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

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

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

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

$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($broker) && !empty($truck_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `lr_mst` WHERE agent = '".$broker."' AND truck_no = '".$truck_no."' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."')AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `lr_mst` WHERE truck_no = '".$truck_no."' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."')AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}elseif(!empty($vendor_id) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `lr_mst` WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}else if(!empty($vendor_id)){
	$lr = "SELECT * FROM `lr_mst` WHERE consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";

}else if(!empty($broker) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `lr_mst` WHERE agent = '".$broker."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `lr_mst` WHERE truck_no = '".$truck_no."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}else if(!empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `lr_mst` WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}else{
	$lr = "SELECT * FROM `lr_mst` WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
}


$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;
	while ($row = mysqli_fetch_array($result)) {
		$nor     = "SELECT * FROM `vendor_master` WHERE `id` = '".$row['consignor_name']."'";
		$nor_res = mysqli_query($conn,$nor);
		$nor_row = mysqli_fetch_assoc($nor_res);

		$nee     = "SELECT * FROM `vendor_master` WHERE `id` = '".$row['consignee_name']."'";
		$nee_res = mysqli_query($conn,$nee);
		$nee_row = mysqli_fetch_assoc($nee_res);
		
		$truck 		= "SELECT * FROM truck_mst WHERE truck_no = '".$row['truck_no']."'";
		$truck_res 	= mysqli_query($conn,$truck);
		$truck_row 	= mysqli_fetch_array($truck_res);
		// LHC DATA
		$lhc     = "SELECT * FROM `lhc` WHERE `lr_no` = '".$row['lr_no']."'";
		$lhc_res = mysqli_query($conn,$lhc);
		$lhc_row = mysqli_fetch_assoc($lhc_res);
		if($lhc_row['lhc_date'] != ''){
			$lhc_date = risFormatDate($lhc_row['lhc_date']);
		}else{
			$lhc_date = '';
		}
		
		$add      = "SELECT SUM(amount) AS total_add FROM lhc_details WHERE type = 'add' AND lhc_id = '".$lhc_row['lhc_no']."' GROUP BY lhc_id";
		$add_res  = mysqli_query($conn,$add);
		$add_row  = mysqli_fetch_assoc($add_res);
		
		$sub      = "SELECT SUM(amount) AS total_sub FROM lhc_details WHERE type = 'sub' AND lhc_id = '".$lhc_row['lhc_no']."' GROUP BY lhc_id";
		$sub_res  = mysqli_query($conn,$sub);
		$sub_row  = mysqli_fetch_assoc($sub_res);

		$bill     = "SELECT * FROM `billing` WHERE `lr_no` = '".$row['lr_no']."'";
		$bill_res = mysqli_query($conn,$bill);
		$bill_row = mysqli_fetch_assoc($bill_res);

		$bill_add 	  = "SELECT SUM(`amount`) AS total_add FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` = 'add'";
		$bill_add_res = mysqli_query($conn,$bill_add);
		$bill_add_row = mysqli_fetch_assoc($bill_add_res);

		$bill_sub 	  = "SELECT SUM(`amount`) AS total_sub FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` = 'sub'";
		$bill_sub_res = mysqli_query($conn,$bill_sub);
		$bill_sub_row = mysqli_fetch_assoc($bill_sub_res);

		$mr 			 = "SELECT * FROM `mr` WHERE `bill_no` = '".$bill_row['invoice_no']."' GROUP BY mr_no";
		$mr_res 		 = mysqli_query($conn,$mr);
		$mr_data_rowspan = mysqli_num_rows($mr_res);

		$total_fright += $row['freight'];
		//$lastColumnPrinted 	= false;
		foreach ($mr_data as $key => $mr_datas) {
			$table .= '<tr class="text-center">';
				$table .= '<td class="text-center mr-report-vertical">'.$row["lr_no"].'</td>';
				$table .= '<td class="text-center mr-report-vertical">'.$row["l_date"].'</td>';
				$table .= '<td class="text-center mr-report-vertical">'.$row["truck_no"].'</td>';
				$table .= '<td class="text-center mr-report-vertical">'.$row["truck_driver_no"].'</td>';
				$table .= '<td class="text-center mr-report-vertical">'.$truck_row["o_name"].'</td>';
				$table .= '<td class="text-center mr-report-vertical">' . $row['from_place'] . ' - ' . $row['to_place'] . '</td>';
				$table .= '<td class="text-center mr-report-vertical">' . @$master_accounts[$row['consignor_name']] .'</td>';
				$table .= '<td class="text-center mr-report-vertical">' . @$master_accounts[$row['consignee_name']] .'</td>';
				$table .= '<td class="text-center mr-report-vertical">' . $row['agent'] . '</td>';
				$table .= '<td class="text-center mr-report-vertical">' . $row['billing_party'] .'</td>';
				$table .= '<td class="text-center mr-report-vertical">' . customFormatMoney($row['freight']) . '</td>';
				$table .= '<td class="text-center mr-report-vertical">' . risFormatDate($row['act_rec_date']) . '</td>';
			$table .= '</tr>';
		}
		$lastColumnPrinted 	= false;
		while ($mr_row = mysqli_fetch_assoc($mr_res)) {
		$table .= '<tr class="text-center">';
			if($lastColumnPrinted == false): $lastColumnPrinted = true;
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">'.$row["lr_no"].'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">'.$row["l_date"].'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">'.$row["truck_no"].'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">'.$row["truck_driver_no"].'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">'.$truck_row["o_name"].'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . $row['from_place'] . ' - ' . $row['to_place'] . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . @$master_accounts[$row['consignor_name']] .'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . @$master_accounts[$row['consignee_name']] .'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . $row['agent'] . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . $row['billing_party'] .'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($row['freight']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . risFormatDate($row['act_rec_date']) . '</td>';

			// LHC 
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . $lhc_row['lhc_no'] . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . $lhc_date . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($lhc_row['lorry_hire']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($add_row['total_add']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($sub_row['total_sub']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($lhc_row['adv_amnt']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($lhc_row['payment_mode']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($lhc_row['balance']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($lhc_row['net_amnt'] - $lhc_row['balance']) . '</td>';

			//BILL

			$table .= '<td class="text-center mr-report-vertical"  rowspan="'.$mr_data_rowspan.'">' . $bill_row['invoice_no'] . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . risFormatDate($bill_row['bill_date']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . @$master_accounts[$bill_row['vendor_id']] .'</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($bill_add_row['total_add']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($bill_sub_row['total_sub']) . '</td>';
			$table .= '<td class="text-center mr-report-vertical" rowspan="'.$mr_data_rowspan.'">' . customFormatMoney($bill_row['net_amt']) . '</td>';
			endif;
				
			$table .= '<td class="text-center">' . $mr_row['mr_no'] . '</td>';
			$table .= '<td class="text-center">' . risFormatDate($mr_row['mr_date']) . '</td>';
			$table .= '<td class="text-center">' . customFormatMoney($mr_row['bill_rec']) . '</td>';
			$table .= '<td class="text-center">' . customFormatMoney($mr_row['bill_pending']) . '</td>';
				
		$table .= '</tr>';
	}}
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>