<?php 
include("../config.php");
if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
	$vendor_id = strtoupper($_GET['vendor_id']);
}

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

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

$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($vendor_id) && !empty($bill_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `billing` WHERE vendor_id = '".$vendor_id."' AND DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND invoice_no = '".$bill_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no ORDER BY invoice_no DESC";

}else if(!empty($vendor_id) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `billing` WHERE vendor_id = '".$vendor_id."' AND DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no ORDER BY invoice_no DESC";

}else if(!empty($vendor_id)){
	$lr = "SELECT * FROM `billing` WHERE vendor_id = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no ORDER BY invoice_no DESC";

}else if(!empty($bill_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `billing` WHERE DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND invoice_no = '".$bill_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no ORDER BY invoice_no DESC";

}else if(!empty($paid_by) && !empty($sd) && !empty($ed)){
	
	$lr = "SELECT billing.*, lr_mst.lr_no, lr_mst.gst_paid_by FROM billing 
	 		JOIN lr_mst ON lr_mst.lr_no = billing.lr_no
	 		WHERE DATE(billing.bill_date) >= '".$sd."' AND DATE(billing.bill_date) <= '".$ed."' AND lr_mst.gst_paid_by = '".$paid_by."' AND billing.company_id = '".$_SESSION['company_id']."' AND billing.branch_id = '".$_SESSION['branch_id']."' GROUP BY billing.invoice_no ORDER BY billing.invoice_no DESC
	 		";
}else{
	$lr = "SELECT * FROM `billing` WHERE DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no ORDER BY invoice_no DESC";
}

$result 		= mysqli_query($conn,$lr);
$total       	= mysqli_num_rows($result);

$master_accounts       = combine(getAllRecords('vendor_master'), 'id', 'name');


$table       	= '';
$total_count    = 0;
if(count($total) > 0) {
	$total_fright = 0;
	$rate 	= 0;
	$amount = 0;
	$other  = 0;
	$other  = 0;
	$other  = 0;
	$ga    	= 0;
	$na     = 0;
	$total_add = 0;
	$total_sub = 0;
	$total_gst = 0;
	while ($row = mysqli_fetch_array($result)) {
		$lr 	= mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE `lr_no` = '".$row['lr_no']."'");
		$lr_row = mysqli_fetch_assoc($lr);
		
		$nor     = "SELECT * FROM `vendor_master` WHERE `id` = '".$row['vendor_id']."'";
		$nor_res = mysqli_query($conn,$nor);
		$nor_row = mysqli_fetch_assoc($nor_res);
		
		$add      = "SELECT SUM(amount) AS total_add FROM billing_details WHERE type = 'add' AND bill_no = '".$data['invoice_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY bill_no";
		$add_res  = mysqli_query($conn,$add);
		$add_row  = mysqli_fetch_assoc($add_res);
		
		$sub      = "SELECT SUM(amount) AS total_sub FROM billing_details WHERE type = 'sub' AND bill_no = '".$data['invoice_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY bill_no";
		$sub_res  = mysqli_query($conn,$sub);
		$sub_row  = mysqli_fetch_assoc($sub_res);


		$total_add += $add_row['total_add'];
		$total_sub += $sub_row['total_sub'];
		$ga    	   += $row['gross_amt'];
		$na        += $row['net_amt'];
		$gst 		= ($row['sgst_amount'] + $row['cgst_amount']) + $row['igst_amount'];
		$total_gst  += ($row['sgst_amount'] + $row['cgst_amount']) + $row['igst_amount'];
		$table .= '<tr>';
		$table .= '<td class="text-center">'.$row["invoice_no"].'</td>';
		$table .= '<td class="text-center">'.risFormatDate($row["bill_date"]).'</td>';
		$table .= '<td class="text-left">' . @$master_accounts[$row['vendor_id']] .'</td>';
		$table .= '<td class="text-center">' . $nor_row['gstin'] . '</td>';
		$table .= '<td class="text-center">' . customFormatMoney($row['gross_amt']) . '</td>';
		$table .= '<td class="text-center">' . customFormatMoney($add_row['total_add']) . '</td>';
		$table .= '<td class="text-center">' . customFormatMoney($sub_row['total_sub']) . '</td>';
		$table .= '<td class="text-center">' . customFormatMoney($row['net_amt']) . '</td>';
		$table .= '<td class="text-center">' . customFormatMoney($gst) . '</td>';
		$table .= '<td class="text-center">' . $lr_row['gst_paid_by'] . '</td>';

		$table .= '</tr>';
	}
	$table .= '<tr class="text-center">';
	$table .= '<td colspan="4"><b>TOTAL</b></td>';
	$table .= '<td>' . customFormatMoney($ga) . '</td>';
	$table .= '<td>' . customFormatMoney($total_add) . '</td>';
	$table .= '<td>' . customFormatMoney($total_sub) . '</td>';
	$table .= '<td>' . customFormatMoney($na) . '</td>';
	$table .= '<td>' . customFormatMoney($total_gst) . '</td>';
	$table .= '</tr>';
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>