<?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 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']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND agent = '".$broker."' AND truck_no = '".$truck_no."' 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 DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND truck_no = '".$truck_no."' GROUP BY lr_no";

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

}else if(!empty($vendor_id) && !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']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') GROUP BY lr_no";

}else if(!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']."' AND lhc_status = 'N' 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']."' AND lhc_status = 'N' 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']."' AND lhc_status = 'N' 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']."' AND lhc_status = 'N' 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']."' AND lhc_status = 'N' 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']."' AND lhc_status = 'N' 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);

		$total_fright += $row['freight'];
		$table .= '<tr class="text-center">';
		$table .= '<td>'.$row["lr_no"].'</td>';
		$table .= '<td>'.$row["l_date"].'</td>';
		$table .= '<td>'.$row["truck_no"].'</td>';
		$table .= '<td>' . @$master_accounts[$row['consignor_name']] . '<br> '.$nor_row['gstin'].'</td>';
		$table .= '<td>' . @$master_accounts[$row['consignee_name']] . '<br> '.$nee_row['gstin'].'</td>';
		$table .= '<td>' . @$master_accounts[$row['billing_to']] . '</td>';
		$table .= '<td>' . $row['from_place'] . ' - ' . $row['to_place'] . '</td>';
		$table .= '<td>' . $row['pcs'] . '</td>';
		$table .= '<td>' . $row['weight'] . '</td>';
		$table .= '<td>' . customFormatMoney($row['freight']) . '</td>';
		$table .= '<td>' . $row['billing_party'] . '</td>';
		$table .= '<td>' . $row['agent'] . '</td>';
		$table .= '</tr>';
	}
	$table .= '<tr>';
	$table .= '<td colspan="9"><b>TOTAL FREIGHT</b></td>';
	$table .= '<td class="text-center">' . customFormatMoney($total_fright) . '</td>';
	$table .= '</tr>';
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>