<?php 
include("../config.php");

if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
	$vendor_id = strtoupper($_GET['vendor_id']);
}

if(isset($_GET['group_id']) && !empty($_GET['group_id'])) {
	$group_id = strtoupper($_GET['group_id']);
}


$sd = mysqli_real_escape_string($conn, date('Y-m-d', strtotime($_GET['start_date'])));
$ed = mysqli_real_escape_string($conn, date('Y-m-d', strtotime($_GET['end_date'])));

if(!empty($sd) && !empty($ed) && !empty($vendor_id)){
	$master_vendor       = combine(getAllRecords('vendor_master'), 'id', 'name');
    $vendor_name 	     = @$master_vendor[$vendor_id];
    
    $account = mysqli_query($conn, "SELECT * FROM `act_master` WHERE `ac_name` = '".$vendor_name."'");
   	$account_row = mysqli_fetch_assoc($account);
   
	$trans = "SELECT *
	FROM trans 
	WHERE DATE(voucher_date) >= '".$sd."' AND DATE(voucher_date) <= '".$ed."' AND (`flag` = 'BILLING' OR `flag` = 'GENERAL SALES') AND `branch_id` = '".$_SESSION['branch_id']."' AND `company_id` = '".$_SESSION['company_id']."' AND `balance` > 0  AND `act_code` = '".$account_row['act_code']."' GROUP BY act_code";

}else if(!empty($sd) && !empty($ed) && !empty($group_id)){
	$vendor = mysqli_query($conn, "SELECT * FROM `vendor_master` WHERE `group_id` ='".$group_id."'");
	$vendor_data = array();
	while ($vendor_row = mysqli_fetch_assoc($vendor)) {
		$account = mysqli_query($conn, "SELECT * FROM `act_master` WHERE `ac_name` = '".$vendor_row['name']."'");
		$account_row = mysqli_fetch_assoc($account);

		$vendor_data[] = $account_row['act_code'];
	}
	
	$act_code = '"' . implode('", "', $vendor_data) . '"';

	$trans = "SELECT *
	FROM trans 
	WHERE DATE(voucher_date) >= '".$sd."' AND DATE(voucher_date) <= '".$ed."' AND (`flag` = 'BILLING' OR `flag` = 'GENERAL SALES') AND `branch_id` = '".$_SESSION['branch_id']."' AND `company_id` = '".$_SESSION['company_id']."' AND `balance` > 0  AND `act_code` IN($act_code) GROUP BY act_code";

}else{
	$trans = "SELECT trans.company_id, trans.branch_id, trans.act_code, act_master.act_code, act_master.ac_name, trans.voucher_date, trans.flag
	FROM act_master
	LEFT JOIN trans ON act_master.act_code=trans.act_code WHERE DATE(trans.voucher_date) >= '".$sd."' AND DATE(trans.voucher_date) <= '".$ed."' AND (trans.`flag` = 'BILLING' OR trans.`flag` = 'GENERAL SALES') AND trans.branch_id = '".$_SESSION['branch_id']."' AND trans.company_id = '".$_SESSION['company_id']."' AND trans.balance > 0 GROUP BY trans.act_code ORDER BY ac_name ASC";
}

$result 			= mysqli_query($conn,$trans);
$total       		= mysqli_num_rows($result);
$master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');

$table       	= '';
$total_count    = 0;

$date = $ed;
if(count($total) > 0) {
	$table .= '<tr>';
	$table .= '<th class="text-center">BILL NO</th>';
	$table .= '<th class="text-center">BILL DATE</th>';
	$table .= '<th class="text-center">BILL AMOUNT</th>';
	$table .= '<th class="text-center">RECEIVED</th>';
	$table .= '<th class="text-center">BALANCE</th>';
	$table .= '<th class="text-center">BILL SUB.BY</th>';
	$table .= '<th class="text-center" width="13%">BILL SUB.DATE</th>';
	$table .= '<th class="text-center">DAYS</th>';
	$table .= '</tr>';
	
	$t_bill_amnt = 0;
	$t_received  = 0;
	$t_balance   = 0;
	$bill_amnt = 0;

	while ($row = mysqli_fetch_array($result)) {
		//GET BILL RECORD
		$get_bill = mysqli_query($conn, "SELECT * FROM `trans` WHERE act_code = '".$row['act_code']."' AND (`flag` = 'BILLING' OR `flag` = 'GENERAL SALES') AND balance > 0 AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' and DATE(voucher_date) >= '".$sd."' AND DATE(voucher_date) <= '".$ed."'");

		$master_account = combine(getAllRecords('act_master'), 'act_code', 'ac_name');

		$table .= '<tr class="text-center">';
		$table .= '<td colspan="8" class="report-bg"><b>'.@$master_account[$row['act_code']].'</b> - '.$row['act_code'].'</td>';
		$table .= '</tr>';

		$bill_amnt = 0;
		$received  = 0;
		$balance   = 0;

		while ($get_bill_row 	= mysqli_fetch_assoc($get_bill)) {
			
			//TOTAL COUNT 
			$bill_amnt   += $get_bill_row['amount'];
			$received    += $get_bill_row['adjust'];
			$balance     += $get_bill_row['balance'];

			// GRAND TOTAL
			$t_bill_amnt += $get_bill_row['amount'];
			$t_received  += $get_bill_row['adjust'];
			$t_balance   += $get_bill_row['balance'];

			//GET BILL SUBMIT DATE & DAYS & SYBMITTED BY
			if($get_bill_row['flag'] == 'BILLING'){
				//GET BILL SUBMIT DATE & DAYS & SYBMITTED BY
				$bill_data = mysqli_query($conn, "SELECT * FROM `billing` WHERE `invoice_no` = '".$get_bill_row['voucher_no']."'");
				$bill_data_row = mysqli_fetch_assoc($bill_data);

				if($bill_data_row['bill_submited_date'] != ''){
					$bsd  = risFormatDate($bill_data_row['bill_submited_date']);
					$days = date_diffrence($bill_data_row['bill_submited_date'],$date);
					$bill_submited_by = $bill_data_row['bill_submited_by'];
				}else{
					$bsd = '';
					$days = '';
					$bill_submited_by = '';
				}	
			}else{
				//GET BILL SUBMIT DATE & DAYS & SYBMITTED BY
				$bill_data = mysqli_query($conn, "SELECT * FROM `general_sales` WHERE `bill_no` = '".$get_bill_row['voucher_no']."'");
				$bill_data_row = mysqli_fetch_assoc($bill_data);

				if($bill_data_row['bill_submited_date'] != ''){
					$bsd  = risFormatDate($bill_data_row['bill_submited_date']);
					$days = date_diffrence($bill_data_row['bill_submited_date'],$date);
					$bill_submited_by = $bill_data_row['bill_submited_by'];
				}else{
					$bsd = '';
					$days = '';
					$bill_submited_by = '';
				}
			}


			$table .= '<tr class="text-center">';
			$table .= '<td>'.$get_bill_row['voucher_no'].'</td>';
			$table .= '<td>'.risFormatDate($get_bill_row['voucher_date']).'</td>';
			$table .= '<td>'.customFormatMoney($get_bill_row['amount']).'</td>';
			$table .= '<td>'.customFormatMoney($get_bill_row['adjust']).'</td>';
			$table .= '<td>'.customFormatMoney($get_bill_row['balance']).'</td>';
			$table .= '<td>'.$bill_submited_by.'</td>';
			$table .= '<td>'.$bsd.'</td>';
			$table .= '<td>'.$days.'</td>';
			$table .= '</tr>';
		}
		$table .= '<tr class="text-center">';
		$table .= '<td colspan="2"><b>TOTAL</b></td>';
		$table .= '<td><b>'.customFormatMoney($bill_amnt).'</b></td>';
		$table .= '<td><b>'.customFormatMoney($received).'</b></td>';
		$table .= '<td><b>'.customFormatMoney($balance).'</b></td>';
		$table .= '</tr>';
	}
	$table .= '<tr class="text-center">';
	$table .= '<td colspan="2"><b>GRAND TOTAL</b></td>';
	$table .= '<td><b>'.customFormatMoney($t_bill_amnt).'</b></td>';
	$table .= '<td><b>'.customFormatMoney($t_received).'</b></td>';
	$table .= '<td><b>'.customFormatMoney($t_balance).'</b></td>';
	$table .= '</tr>';
	
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>