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


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

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


$sd = $_GET['start_date'];
$ed = $_GET['end_date'];

if($sd =='' || $ed =='' || $_GET['type'] =='' || $_GET['agent'] =='' || $_GET['owner'] ==''){
	$lr = "SELECT * FROM `lhc` WHERE balance > 0 AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
}

if( !empty($sd) && !empty($ed)) {
	$sd = date('Y-m-d', strtotime($sd));
	$ed = date('Y-m-d', strtotime($ed));
	$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";
}

if( !empty($agent) ) {
	$lr = "SELECT * FROM `lhc` WHERE agent = '".$agent."' AND payment_to = 'AGENT' AND balance > 0 AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
}

if( !empty($owner) ) {
	$lr = "SELECT * FROM `lhc` WHERE owner_name = '".$owner."' AND payment_to = 'OWNER' AND balance > 0 AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
}

if( !empty($type) ) {
	$lr = "SELECT * FROM `lhc` WHERE payment_to = '".$type."' 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_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;
	$total_add 		= 0;
	$total_sub 		= 0;

	while ($row = mysqli_fetch_array($result)) {
		$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);
		
		$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);

		$hit_lhc 		= "SELECT * FROM lhc WHERE lhc_no = '".$row['lhc_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
		$hit_lhc_res 	= mysqli_query($conn,$hit_lhc);
		$hit_lhc_row 	= mysqli_fetch_assoc($hit_lhc_res);
		

		if($hit_lhc_row['payment_to'] == 'AGENT'){
			$style  = "color:red";
			$button = "<a href='#' id='".$row["agent"]."' onclick='viewData(this);'>STATEMENT</a>";
		}else{
			$style = "";
			$button = '';
		}

		if($hit_lhc_row['payment_to'] == 'OWNER'){
			$styles = "color:green";
			$buttons = "<a href='#' id='".$row["owner_name"]."' onclick='viewDatas(this);'>STATEMENT</a>";
		}else{
			$styles = "";
			$buttons = '';
		}

		$total_add 		+= $add_row['total_add'];
		$total_sub 		+= $sub_row['total_sub'];
		$lorry_hire     += $row['lorry_hire'];
		$disel    		+= $row['disel_amnt'];
		$adv     		+= $row['adv_amnt'];
		$balance     	+= $row['balance'];
		$bank     		+= $row['pay_mode_amnt'];
		$cash    	   	+= $row['cash'];
		
		
		$table .= '<tr class="text-center">';
		$table .= '<td>'.$row["lhc_no"].'</td>';
		$table .= '<td>'.risFormatDate($row["lhc_date"]).'</td>';
		$table .= '<td>' . $row['truck_no'] . '</td>';
		$table .= '<td>' . $row['from_place'] . '-'. $row['to_place'] .'</td>';
		$table .= '<td style='.$styles.'>' . @$master_accounts[$row['owner_name']] . '   '.$buttons.'</td>';
		$table .= '<td style='.$style.'>' . $row['agent'] . ' '.$button.'</td>';
		$table .= '<td>' . $row['lorry_hire'] . '</td>';
		$table .= '<td>' . $add_row['total_add'] . '</td>';
		$table .= '<td>' . $row['disel_amnt'] . '</td>';
		$table .= '<td>' . $sub_row['total_sub'] . '</td>';
		$table .= '<td>' . $row['adv_amnt'] . '</td>';
		$table .= '<td>' . $row['pay_mode_amnt'] . '</td>';
		$table .= '<td>' . $row['cash'] . '</td>';
		$table .= '<td>' . $row['balance'] . '</td>';
		
		$table .= '</tr>';
	}
	
	$table .= '<tr>';
	$table .= '<td colspan="6" class="text-right"><b>TOTAL</b></td>';
	$table .= '<td>' . customFormatMoney($lorry_hire) . '</td>';
	$table .= '<td>' . customFormatMoney($total_add) . '</td>';
	$table .= '<td>' . customFormatMoney($disel) . '</td>';
	$table .= '<td>' . customFormatMoney($total_sub) . '</td>';
	$table .= '<td>' . customFormatMoney($adv) . '</td>';
	$table .= '<td>' . customFormatMoney($bank) . '</td>';
	$table .= '<td>' . customFormatMoney($cash) . '</td>';
	$table .= '<td>' . customFormatMoney($balance) . '</td>';
	$table .= '</tr>';
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>