<?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 lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                  WHERE lr_mst.`agent` = '".$broker."' AND lr_mst.`truck_no` = '".$truck_no."' AND (lr_mst.`consignor_name` = '".$vendor_id."' OR lr_mst.`consignee_name` = '".$vendor_id."')AND DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND lr_mst.`company_id` = '".$_SESSION['company_id']."' AND lr_mst.`branch_id` = '".$_SESSION['branch_id']."'";


}else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
           WHERE lr_mst.`truck_no` = '".$truck_no."' AND (lr_mst.`consignor_name` = '".$vendor_id."' OR lr_mst.`consignee_name` = '".$vendor_id."')AND DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND lr_mst.`company_id` = '".$_SESSION['company_id']."' AND lr_mst.`branch_id` = '".$_SESSION['branch_id']."'";
}elseif(!empty($vendor_id) && !empty($sd) && !empty($ed)){
	$lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                   WHERE (lr_mst.`consignor_name` = '".$vendor_id."' OR lr_mst.`consignee_name` = '".$vendor_id."') AND DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND lr_mst.`company_id` = '".$_SESSION['company_id']."' AND lr_mst.`branch_id` = '".$_SESSION['branch_id']."'";
}else if(!empty($vendor_id)){
	$lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                  WHERE lr_mst.`consignor_name` = '".$vendor_id."' OR lr_mst.`consignee_name` = '".$vendor_id."' AND lr_mst.`company_id` = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";

}else if(!empty($broker) && !empty($sd) && !empty($ed)){
	$lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                   WHERE lr_mst.`agent` = '".$broker."' AND DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND lr_mst.`company_id` = '".$_SESSION['company_id']."' AND lr_mst.`branch_id` = '".$_SESSION['branch_id']."'";
}else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
	$lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                   WHERE lr_mst.`truck_no` = '".$truck_no."' AND DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
}else if(!empty($sd) && !empty($ed)){
	       $lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                 WHERE DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND 
                 lr_mst.`company_id` = '".$_SESSION ['company_id']."' AND lr_mst.`branch_id` = '".$_SESSION['branch_id']."'";
}else{
	$lr = "SELECT lr_mst.`lr_no`, lr_mst.`l_date`, vendor_master.`name` AS consignor_name, vendor_master_1.`name` AS
                 consignee_name, lr_mst.`from_place`, lr_mst.`to_place`, lr_mst.`truck_no`, truck_mst.`o_name`, truck_mst.`o_pan`,
                 lr_mst.`agent`, lhc.`lhc_no`, lhc.`lhc_date`, lhc.`lorry_hire`, lhc.`adv_amnt`, 
                 vw_lhc_balance_payment.`adj_amnt` AS balance_payment, billing.`invoice_no`, billing.`bill_date`,
                 vendor_master_2.`name` AS bill_party, billing.`lr_amount` AS bill_amount, vw_mr.`bill_rec` AS bill_rec, 
                 vw_mr.`tds` AS tds, vw_mr.`deduction` AS decution, lr_mst.`rate_on`, lr_mst.`company_id`,
                 lr_mst.`branch_id`
                 FROM billing LEFT OUTER JOIN vw_mr ON billing.`invoice_no` = vw_mr.`bill_no` LEFT OUTER JOIN
                 vendor_master AS vendor_master_2 ON billing.`vendor_id` = vendor_master_2.`id` RIGHT OUTER JOIN
                 lr_mst ON billing.`lr_no` = lr_mst.`lr_no` LEFT OUTER JOIN vw_lhc_balance_payment RIGHT OUTER JOIN
                 lhc ON vw_lhc_balance_payment.`lhc_no` = lhc.`lhc_no` ON lr_mst.`lr_no` = lhc.`lr_no` LEFT OUTER JOIN
                 truck_mst ON lr_mst.`truck_no` = truck_mst.`truck_no` LEFT OUTER JOIN vendor_master AS vendor_master_1 ON lr_mst.
                 `consignee_name` = vendor_master_1.`id` LEFT OUTER JOIN vendor_master ON `lr_mst`.consignor_name = vendor_master.
                 `id`
                   WHERE DATE(lr_mst.`l_date`) >= '".$sd."' AND DATE(lr_mst.`l_date`) <= '".$ed."' AND lr_mst.`company_id` = '".$_SESSION['company_id']."' AND lr_mst.`branch_id` = '".$_SESSION['branch_id']."'";
}
$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);

		if($row['eway_date'] != ''){
			$edate = risFormatDate($row['eway_date']);
		}else{
			$edate = '';
		}

		if($row['eway_ex_date'] != ''){
			$exdate = risFormatDate($row['eway_ex_date']);
		}else{
			$exdate = '';
		}

		$total_fright += $row['freight'];
		$table .= '<tr class="text-center">';
		$table .= '<td>'.$row["lr_no"].'</td>';
		$table .= '<td>'.risFormatDate($row["l_date"]).'</td>';
        $table .= '<td>'.$row["consignor_name"].'</td>';
        $table .= '<td>'.$row["consignee_name"].'</td>';
        $table .= '<td>'.$row["from_place"].'</td>';
        $table .= '<td>'.$row["to_place"].'</td>';
		$table .= '<td>'.$row["truck_no"].'</td>';
        $table .= '<td>'.$row["o_name"].'</td>';
        $table .= '<td>'.$row["o_pan"].'</td>';
        $table .= '<td>'.$row["agent"].'</td>';
        $table .= '<td>'.$row["lhc_no"].'</td>';
        $table .= '<td>'.risFormatDate($row["lhc_date"]).'</td>';
        $table .= '<td>'.$row["lorry_hire"].'</td>';
        $table .= '<td>'.$row["adv_amnt"].'</td>';
        $table .= '<td>'.$row["balance_payment"].'</td>';
        $table .= '<td>'.$row["invoice_no"].'</td>';
        $table .= '<td>'.risFormatDate($row["bill_date"]).'</td>';
        $table .= '<td>'.$row["bill_party"].'</td>';
        $table .= '<td>' . customFormatMoney($row['bill_amount']) . '</td>';
        $table .= '<td>' . customFormatMoney($row['bill_rec']) . '</td>';
        $table .= '<td>' . customFormatMoney($row['tds']) . '</td>';
        $table .= '<td>' . customFormatMoney($row['decution']) . '</td>';
        $table .= '<td>'.$row["rate_on"].'</td>';
		$table .= '</tr>';
	}
	$table .= '<tr>';
	$table .= '</tr>';
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>