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

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


if( !empty($sd) && !empty($ed) && $agent_id != '') {
	$lr = "SELECT * FROM `lhc` WHERE DATE(lhc_date) >= '".$sd."' AND DATE(lhc_date) <= '".$ed."' AND balance > 0 AND agent = '".$agent_id."'  AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
}else{
	$lr = "SELECT * FROM `lhc` WHERE DATE(lhc_date) >= '".$sd."' AND DATE(lhc_date) <= '".$ed."' AND balance > 0 AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
}

$result 		= mysqli_query($conn,$lr);
$total       	= mysqli_num_rows($result);
$master_vendor  = combine(getAllRecords('vendor_master'), 'id', 'name');
$table       	= '';
$total_count    = 0;
if(count($total) > 0) {
	$total_amount  = 0;
	$total_advance = 0;
	$total_balance = 0;
	while ($row = mysqli_fetch_array($result)) {
		$sub      = "SELECT SUM(amount) AS total_sub FROM lhc_details WHERE type = 'sub' AND lhc_id = '".$row['lhc_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_id";
		$sub_res  = mysqli_query($conn,$sub);
		$sub_row  = mysqli_fetch_assoc($sub_res);

		$add      = "SELECT SUM(amount) AS total_add FROM lhc_details WHERE type = 'add' AND lhc_id = '".$row['lhc_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_id";
		$add_res  = mysqli_query($conn,$add);
		$add_row  = mysqli_fetch_assoc($add_res);

		$remove_lhc_alies = substr($row['lhc_no'], 4);
		$new_lhc_no       = ltrim($remove_lhc_alies, "0"); 
		
		$remove_lr_alies = substr($row['lr_no'], 4);
		$new_lr_no       = ltrim($remove_lr_alies, "0");

		$lr     = mysqli_query($conn,"SELECT * FROM `lr_mst` WHERE `lr_no` = '".$row['lr_no']."'");
		$lr_row = mysqli_fetch_assoc($lr);

		$total_amount  += $row['net_amnt'];
		$total_advance += ($row['adv_amnt']);
		$total_sub += ($sub_row['total_sub']);
		$total_add += ($add_row['total_add']);
		$total_balance += $row['balance'];

		$table .= '<tr class="text-center">';
		$table .= '<td>'.$new_lhc_no.'</td>';
		$table .= '<td>'.risFormatDate($row["lhc_date"]).'</td>';
		$table .= '<td>' . $new_lr_no . '</td>';
		$table .= '<td>' . $row['truck_no'] . '</td>';
		$table .= '<td>' . $row['from_place'] . '-'. $row['to_place'] .'</td>';
		$table .= '<td>' . @$master_vendor[$lr_row['consignor_name']] . '</td>';
		$table .= '<td>' . @$master_vendor[$lr_row['consignee_name']] . '</td>';
		$table .= '<td>' . $row['agent'] . '</td>';
		$table .= '<td class="text-right">' . customFormatMoney($row['net_amnt']) . '</td>';
		$table .= '<td class="text-right">' . customFormatMoney($row['adv_amnt']) . '</td>';
		$table .= '<td>' . customFormatMoney($add_row['total_add']) . '</td>';
		$table .= '<td>' . customFormatMoney($sub_row['total_sub']) . '</td>';
		$table .= '<td class="text-right">' . customFormatMoney($row['balance']) . '</td>';
		$table .= '</tr>';
	}
	
	$table .= '<tr>';
	$table .= '<td colspan="8" class="text-right"><b>TOTAL</b></td>';
	$table .= '<td class="text-right">' . customFormatMoney($total_amount) . '</td>';
	$table .= '<td class="text-right">' . customFormatMoney($total_advance) . '</td>';
	$table .= '<td class="text-right">' . customFormatMoney($total_add) . '</td>';
	$table .= '<td class="text-right">' . customFormatMoney($total_sub) . '</td>';
	$table .= '<td class="text-right">' . customFormatMoney($total_balance) . '</td>';
	$table .= '</tr>';
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>