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

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


$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($owner) && !empty($sd) && !empty($ed)){
	$lr = "SELECT * FROM `con_lhc` WHERE owner_name = '".$owner."' AND DATE(lhc_date) >= '".$sd."' AND DATE(lhc_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY contract_code";
}else if(!empty($owner_name)){
	$lr = "SELECT * FROM `con_lhc` WHERE owner_name = '".$owner."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY contract_code";

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


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

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


$table       	= '';
$total_count    = 0;
if(count($total) > 0) {
	$total_add 		= 0;
	$total_sub 		= 0;
	$lorry_hire     = 0;
	$disel    		= 0;
	$adv     		= 0;
	$balance     	= 0;
	$bank     		= 0;
	$cash    	   	= 0;
	while ($row = mysqli_fetch_array($result)) {
		$add      = "SELECT SUM(amount) AS total_add FROM  con_lhc_details WHERE type = 'add' AND lhc_id = '".$row['contract_code']."' 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);
		
		$sub      = "SELECT SUM(amount) AS total_sub FROM  con_lhc_details WHERE type = 'sub' AND lhc_id = '".$row['contract_code']."' 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);

		$truck 			= "SELECT * FROM truck_mst WHERE id = '".$row['owner_name']."'";
		$truck_res 		= mysqli_query($conn,$truck);
		$truck_row 		= mysqli_fetch_assoc($truck_res);

		$remove_alies = substr($row["contract_code"], 6);
		$new_bill_no   = ltrim($remove_alies, "0");

		$total_add 		+= $add_row['total_add'];
		$total_sub 		+= $sub_row['total_sub'];
		$lorry_hire     += $row['lorry_hire'];
		$adv     		+= $row['adv_amnt'];
		$balance     	+= $row['balance'];
		$bank     		+= $row['pay_mode_amnt'];
		$cash    	   	+= $row['cash'];
		
		
		$table .= '<tr class="text-center">';
		$table .= '<td>'.$new_bill_no.'</td>';
		$table .= '<td>'.$row["lhc_date"].'</td>';
		$table .= '<td>' . $row['truck_no'] . '</td>';
		$table .= '<td>' . $row['from_place'] . '-'. $row['to_place'] .'</td>';
		$table .= '<td><span style="color:red">'.$truck_row['pan_no'].'</span>&nbsp;'. @$master_accounts[$row['owner_name']] . '</td>';

		$table .= '<td>' . customFormatMoney($row['lorry_hire']) . '</td>';
		$table .= '<td>' . customFormatMoney($add_row['total_add']) . '</td>';
		$table .= '<td>' . customFormatMoney($sub_row['total_sub']) . '</td>';
		$table .= '<td>' . customFormatMoney($row['adv_amnt']) . '</td>';
		$table .= '<td>' . customFormatMoney($row['balance']) . '</td>';
		$table .= '<td>' . customFormatMoney($row['pay_mode_amnt']) . '</td>';
		$table .= '<td>' . customFormatMoney($row['cash']) . '</td>';
		$table .= '</tr>';
	}
	$table .= '<tr>';
	$table .= '<td colspan="5" class="text-right"><b>TOTAL</b></td>';
	$table .= '<td class="text-center">' . customFormatMoney($lorry_hire) . '</td>';
	$table .= '<td class="text-center">' . customFormatMoney($total_add) . '</td>';
	$table .= '<td class="text-center">' . customFormatMoney($total_sub) . '</td>';
	$table .= '<td class="text-center">' . customFormatMoney($adv) . '</td>';
	$table .= '<td class="text-center">' . customFormatMoney($balance) . '</td>';
	$table .= '<td class="text-center">' . customFormatMoney($bank) . '</td>';
	$table .= '<td class="text-center">' . customFormatMoney($cash) . '</td>';
	$table .= '</tr>';
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>