<?php 
include("../config.php");
$lr_no 		= $_GET['lr_no'];
$lhc 		= $_GET['lhc_no'];
$bill 		= $_GET['bill_no'];
$mr 		= $_GET['mr_no'];
if($lr_no =='' && $lhc =='' && $bill =='' && $mr ==''){
	$table = '';
}else{
	if($lr_no !=''){
		$lr 	= "SELECT * FROM `lr_mst` WHERE `lr_no` LIKE '%$lr_no%' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
		$lr_res = mysqli_query($conn,$lr);
		$lr_data = array();
		while ($lr_row = mysqli_fetch_array($lr_res)) {
			$lr_data[] = $lr_row;
		}
		
		foreach ($lr_data as $lr) {
			$lhc = "SELECT * FROM `lhc` WHERE `lr_no` = '".$lr['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
		}
		$lhc_res = mysqli_query($conn,$lhc);
		$lhc_data = array();
		while ($lhc_row = mysqli_fetch_assoc($lhc_res)) {
			$lhc_data[] = $lhc_row;
		}
		
		foreach ($lr_data as $lr_details) {
			$bill 		= "SELECT * FROM `billing` WHERE `lr_no` = '".$lr_details['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$bill_res	= mysqli_query($conn,$bill);
		}
		$bill_data = array();
		while ($bill_row = mysqli_fetch_assoc($bill_res)) {
			$bill_data[] = $bill_row;
			$mr = "SELECT * FROM `mr` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$mr_res = mysqli_query($conn,$mr);
		}
		$mr_data = array();
		while ($mr_row = mysqli_fetch_assoc($mr_res)) {
			$mr_data[] = $mr_row;
		}

		$master_accounts       = combine(getAllRecords('vendor_master'), 'id', 'name');
		$table       	= '';
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LR NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">FROM - TO</th>';
			$table .= '<th class="text-center" colspan="2">CONSINEE</th>';
			$table .= '<th class="text-center" colspan="2">CONSINOR</th>';
			$table .= '<th class="text-center">AGENT</th>';
			$table .= '<th class="text-center">WEIGHT</th>';
			$table .= '<th class="text-center">FRIGHT</th>';
			$table .= '</tr>';
			foreach ($lr_data as $key => $lr_datas) {
			$table .= '<tr>';
			$table .= '<td class="text-center">'.$lr_datas['lr_no'].'</td>';
			$table .= '<td class="text-center">'.risFormatDate($lr_datas['l_date']).'</td>';
			$table .= '<td class="text-center">'.$lr_datas['from_place'].' - '.$lr_datas['to_place'].'</td>';
			$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_datas['consignee_name']].'</td>';
			$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_datas['consignor_name']].'</td>';
			$table .= '<td class="text-center">'.$lr_datas['agent'].'</td>';
			$table .= '<td class="text-center">'.$lr_datas['weight'].'</td>';
			$table .= '<td class="text-center">'.$lr_datas['freight'].'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

		/*LHC*/
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LHC DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LHC NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">LORRY HIRE</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">ADVANCE</th>';
			$table .= '<th class="text-center">DISEL</th>';
			$table .= '<th class="text-center">NET</th>';
			$table .= '<th class="text-center">PAID</th>';
			$table .= '<th class="text-center">BALANCE</th>';
			$table .= '</tr>';

			foreach ($lhc_data as $lhc_data) {
			$lhc_add = "SELECT SUM(`amount`) AS addition FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_add_res = mysqli_query($conn,$lhc_add);
			$lhc_add_row = mysqli_fetch_assoc($lhc_add_res);

			$lhc_deduction = "SELECT SUM(`amount`) AS sub FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_deduction_res = mysqli_query($conn,$lhc_deduction);
			$lhc_deduction_row = mysqli_fetch_assoc($lhc_deduction_res);

			$paid = "SELECT SUM(`amount`) AS paid FROM `lhc_payment_status` WHERE `lhc_no`  = '".$lhc_data['lhc_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$paid_res = mysqli_query($conn,$paid);
			$paid_row = mysqli_fetch_assoc($paid_res);

			$table .= '<tr>';
			$table .= '<td class="text-center">'.$lhc_data['lhc_no'].'</td>';
			$table .= '<td class="text-center">'.risFormatDate($lhc_data['lhc_date']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['lorry_hire']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_add_row['addition']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_deduction_row['sub']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['adv_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['disel_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['net_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($paid_row['paid']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['balance']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

		/*BILLING*/

		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">BILLING DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">BILL NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center" colspan="3">PARTY</th>';
			$table .= '<th class="text-center">GROSS AMOUNT</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">NET AMOUNT</th>';
			$table .= '</tr>';

			foreach ($bill_data as $bill) {
				$add ="SELECT SUM(`amount`) AS total_add FROM `billing_details` WHERE `bill_no` = '".$bill['invoice_no']."' AND `type` ='add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$add_res = mysqli_query($conn,$add);
				$add_row = mysqli_fetch_assoc($add_res);

				$sub ="SELECT SUM(`amount`) AS total_sub FROM `billing_details` WHERE `bill_no` = '".$bill['invoice_no']."' AND `type` ='sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$sub_res = mysqli_query($conn,$sub);
				$sub_row = mysqli_fetch_assoc($sub_res);

				$table .= '<tr>';
				$table .= '<td class="text-center" colspan="2">'.$bill['invoice_no'].'</td>';
				$table .= '<td class="text-center">'.risFormatDate($bill['bill_date']).'</td>';
				$table .= '<td class="text-center" colspan="3">'.@$master_accounts[$bill['vendor_id']].'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($bill['gross_amt']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($add_row['total_add']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($sub_row['total_sub']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($bill['net_amt']).'</td>';
				$table .= '</tr>';
			}
		$table .= '</tr>';

		/* MR */
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">MR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">MR NO</th>';
			$table .= '<th class="text-center" colspan="2">DATE</th>';
			$table .= '<th class="text-center" colspan="2">BILL  AMOUNT</th>';
			$table .= '<th class="text-center" colspan="2">TOTAL RECEIVED</th>';
			$table .= '<th class="text-center" colspan="2">DUE AMOUNT</th>';
			$table .= '</tr>';
			foreach ($mr_data as $mr) {
			$table .= '<tr>';
			$table .= '<td class="text-center" colspan="2">'.$mr['mr_no'].'</td>';
			$table .= '<td class="text-center" colspan="2">'.risFormatDate($mr['mr_date']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_amount']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_rec']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_pending']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

	}else if($lhc !=''){
		$lhc_d     = "SELECT * FROM `lhc` WHERE `lhc_no` LIKE '%$lhc%' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
		$lhc_d_res = mysqli_query($conn,$lhc_d);
		$lhc_d_data= array();
		while ($lhc_d_row = mysqli_fetch_assoc($lhc_d_res)) {
			$lhc_d_data[] = $lhc_d_row;
		}
		
		$lhc     = "SELECT * FROM `lhc` WHERE `lhc_no` LIKE '%$lhc%' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' GROUP BY lhc_no";
		$lhc_res = mysqli_query($conn,$lhc);
		$lhc_data= array();
		$lr_data= array();
		while ($lhc_row = mysqli_fetch_assoc($lhc_res)) {
			$lhc_data[] = $lhc_row;
			$lr_data[] = $lhc_row['lr_no'];
		}
		

		$master_accounts       = combine(getAllRecords('vendor_master'), 'id', 'name');
		$table       	= '';
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LR NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">FROM - TO</th>';
			$table .= '<th class="text-center" colspan="2">CONSINEE</th>';
			$table .= '<th class="text-center" colspan="2">CONSINOR</th>';
			$table .= '<th class="text-center">AGENT</th>';
			$table .= '<th class="text-center">WEIGHT</th>';
			$table .= '<th class="text-center">FRIGHT</th>';
			$table .= '</tr>';
			foreach ($lhc_d_data as $fetch_lr) {
				$lr = "SELECT * FROM lr_mst WHERE lr_no = '".$fetch_lr['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$lr_res = mysqli_query($conn,$lr);
				while ($lr_row = mysqli_fetch_assoc($lr_res)) {
				$table .= '<tr>';
				$table .= '<td class="text-center">'.$lr_row['lr_no'].'</td>';
				$table .= '<td class="text-center">'.risFormatDate($lr_row['l_date']).'</td>';
				$table .= '<td class="text-center">'.$lr_row['from_place'].' - '.$lr_row['to_place'].'</td>';
				$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_row['consignee_name']].'</td>';
				$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_row['consignor_name']].'</td>';
				$table .= '<td class="text-center">'.$lr_row['agent'].'</td>';
				$table .= '<td class="text-center">'.$lr_row['weight'].'</td>';
				$table .= '<td class="text-center">'.$lr_row['freight'].'</td>';
				$table .= '</tr>';
				}
			}
		$table .= '</tr>';

		/*LHC*/
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LHC DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LHC NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">LORRY HIRE</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">ADVANCE</th>';
			$table .= '<th class="text-center">DISEL</th>';
			$table .= '<th class="text-center">NET</th>';
			$table .= '<th class="text-center">PAID</th>';
			$table .= '<th class="text-center">BALANCE</th>';
			$table .= '</tr>';

			foreach ($lhc_data as $lhc_data) {
			$lhc_add = "SELECT SUM(`amount`) AS addition FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_add_res = mysqli_query($conn,$lhc_add);
			$lhc_add_row = mysqli_fetch_assoc($lhc_add_res);

			$lhc_deduction = "SELECT SUM(`amount`) AS sub FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_deduction_res = mysqli_query($conn,$lhc_deduction);
			$lhc_deduction_row = mysqli_fetch_assoc($lhc_deduction_res);

			$paid = "SELECT SUM(`amount`) AS paid FROM `lhc_payment_status` WHERE `lhc_no`  = '".$lhc_data['lhc_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$paid_res = mysqli_query($conn,$paid);
			$paid_row = mysqli_fetch_assoc($paid_res);

			$table .= '<tr>';
			$table .= '<td class="text-center">'.$lhc_data['lhc_no'].'</td>';
			$table .= '<td class="text-center">'.risFormatDate($lhc_data['lhc_date']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['lorry_hire']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_add_row['addition']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_deduction_row['sub']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['adv_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['disel_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['net_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($paid_row['paid']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['balance']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

		/*BILLING*/

		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">BILLING DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">BILL NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center" colspan="3">PARTY</th>';
			$table .= '<th class="text-center">GROSS AMOUNT</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">NET AMOUNT</th>';
			$table .= '</tr>';
			foreach ($lr_data as $bill) {
				$bill 		= "SELECT * FROM `billing` WHERE `lr_no` = '".$bill."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			   	$bill_res	= mysqli_query($conn,$bill);
			   	while ($bill_row = mysqli_fetch_assoc($bill_res)) {
					$mr = "SELECT * FROM `mr` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
					$mr_res = mysqli_query($conn,$mr);
					$mr_data = array();
					while ($mr_row = mysqli_fetch_assoc($mr_res)) {
						$mr_data[] = $mr_row;
					}
					$add ="SELECT SUM(`amount`) AS total_add FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` ='add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
					$add_res = mysqli_query($conn,$add);
					$add_row = mysqli_fetch_assoc($add_res);

					$sub ="SELECT SUM(`amount`) AS total_sub FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` ='sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
					$sub_res = mysqli_query($conn,$sub);
					$sub_row = mysqli_fetch_assoc($sub_res);

					$table .= '<tr>';
					$table .= '<td class="text-center" colspan="2">'.$bill_row['invoice_no'].'</td>';
					$table .= '<td class="text-center">'.risFormatDate($bill_row['bill_date']).'</td>';
					$table .= '<td class="text-center" colspan="3">'.@$master_accounts[$bill_row['vendor_id']].'</td>';
					$table .= '<td class="text-center">'.customFormatMoney($bill_row['gross_amt']).'</td>';
					$table .= '<td class="text-center">'.customFormatMoney($add_row['total_add']).'</td>';
					$table .= '<td class="text-center">'.customFormatMoney($sub_row['total_sub']).'</td>';
					$table .= '<td class="text-center">'.customFormatMoney($bill_row['net_amt']).'</td>';
					$table .= '</tr>';
				}
			}
		$table .= '</tr>';

		/* MR */
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">MR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">MR NO</th>';
			$table .= '<th class="text-center" colspan="2">DATE</th>';
			$table .= '<th class="text-center" colspan="2">BILL  AMOUNT</th>';
			$table .= '<th class="text-center" colspan="2">TOTAL RECEIVED</th>';
			$table .= '<th class="text-center" colspan="2">DUE AMOUNT</th>';
			$table .= '</tr>';
			foreach ($mr_data as $mr) {
			$table .= '<tr>';
			$table .= '<td class="text-center" colspan="2">'.$mr['mr_no'].'</td>';
			$table .= '<td class="text-center" colspan="2">'.risFormatDate($mr['mr_date']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_amount']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_rec']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_pending']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

	}else if($bill !=''){
		$billing    = "SELECT * FROM `billing` WHERE `invoice_no` LIKE '%$bill%' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
		$bill_res 	= mysqli_query($conn,$billing);
		$bill_data	= array();
		while ($bill_row = mysqli_fetch_assoc($bill_res)) {
			$bill_data[] = $bill_row;
		}
		
		//LHC DATA
		foreach ($bill_data as $fetch_lhc) {
			$lhc = "SELECT * FROM `lhc` WHERE `lr_no` = '".$fetch_lhc['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' GROUP BY lhc_no";
			$lhc_res = mysqli_query($conn,$lhc);
		}
		$lhc_data = array();
		while ($lhc_row = mysqli_fetch_assoc($lhc_res)) {
			$lhc_data[] = $lhc_row;
		}

		// BILL DATA
		$bill 		= "SELECT * FROM `billing` WHERE `invoice_no` LIKE '%$bill%' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' GROUP BY invoice_no";
		$bill_res	= mysqli_query($conn,$bill);
		$bills_data  = array();
		while ($bill_row = mysqli_fetch_assoc($bill_res)) {
			$bills_data[] = $bill_row;
		}

		$master_accounts       = combine(getAllRecords('vendor_master'), 'id', 'name');
		$table       	= '';
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LR NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">FROM - TO</th>';
			$table .= '<th class="text-center" colspan="2">CONSINEE</th>';
			$table .= '<th class="text-center" colspan="2">CONSINOR</th>';
			$table .= '<th class="text-center">AGENT</th>';
			$table .= '<th class="text-center">WEIGHT</th>';
			$table .= '<th class="text-center">FRIGHT</th>';
			$table .= '</tr>';
			foreach ($bill_data as $fetch_lr) {
				$lr = "SELECT * FROM lr_mst WHERE lr_no = '".$fetch_lr['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$lr_res = mysqli_query($conn,$lr);
				while ($lr_row = mysqli_fetch_assoc($lr_res)) {
				$table .= '<tr>';
				$table .= '<td class="text-center">'.$lr_row['lr_no'].'</td>';
				$table .= '<td class="text-center">'.risFormatDate($lr_row['l_date']).'</td>';
				$table .= '<td class="text-center">'.$lr_row['from_place'].' - '.$lr_row['to_place'].'</td>';
				$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_row['consignee_name']].'</td>';
				$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_row['consignor_name']].'</td>';
				$table .= '<td class="text-center">'.$lr_row['agent'].'</td>';
				$table .= '<td class="text-center">'.$lr_row['weight'].'</td>';
				$table .= '<td class="text-center">'.$lr_row['freight'].'</td>';
				$table .= '</tr>';
				}
			}
		$table .= '</tr>';

		/*LHC*/
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LHC DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LHC NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">LORRY HIRE</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">ADVANCE</th>';
			$table .= '<th class="text-center">DISEL</th>';
			$table .= '<th class="text-center">NET</th>';
			$table .= '<th class="text-center">PAID</th>';
			$table .= '<th class="text-center">BALANCE</th>';
			$table .= '</tr>';

			foreach ($lhc_data as $lhc_data) {
			$lhc_add = "SELECT SUM(`amount`) AS addition FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_add_res = mysqli_query($conn,$lhc_add);
			$lhc_add_row = mysqli_fetch_assoc($lhc_add_res);

			$lhc_deduction = "SELECT SUM(`amount`) AS sub FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_deduction_res = mysqli_query($conn,$lhc_deduction);
			$lhc_deduction_row = mysqli_fetch_assoc($lhc_deduction_res);

			$paid = "SELECT SUM(`amount`) AS paid FROM `lhc_payment_status` WHERE `lhc_no`  = '".$lhc_data['lhc_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$paid_res = mysqli_query($conn,$paid);
			$paid_row = mysqli_fetch_assoc($paid_res);

			$table .= '<tr>';
			$table .= '<td class="text-center">'.$lhc_data['lhc_no'].'</td>';
			$table .= '<td class="text-center">'.risFormatDate($lhc_data['lhc_date']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['lorry_hire']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_add_row['addition']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_deduction_row['sub']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['adv_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['disel_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['net_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($paid_row['paid']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['balance']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

		/*BILLING*/

		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">BILLING DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">BILL NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center" colspan="3">PARTY</th>';
			$table .= '<th class="text-center">GROSS AMOUNT</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">NET AMOUNT</th>';
			$table .= '</tr>';
			foreach ($bills_data as $bill_row) {
				$mr = "SELECT * FROM `mr` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$mr_res = mysqli_query($conn,$mr);
				$mr_data = array();
				while ($mr_row = mysqli_fetch_assoc($mr_res)) {
					$mr_data[] = $mr_row;
				}
				$add ="SELECT SUM(`amount`) AS total_add FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` ='add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$add_res = mysqli_query($conn,$add);
				$add_row = mysqli_fetch_assoc($add_res);

				$sub ="SELECT SUM(`amount`) AS total_sub FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` ='sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$sub_res = mysqli_query($conn,$sub);
				$sub_row = mysqli_fetch_assoc($sub_res);

				$table .= '<tr>';
				$table .= '<td class="text-center" colspan="2">'.$bill_row['invoice_no'].'</td>';
				$table .= '<td class="text-center">'.risFormatDate($bill_row['bill_date']).'</td>';
				$table .= '<td class="text-center" colspan="3">'.@$master_accounts[$bill_row['vendor_id']].'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($bill_row['gross_amt']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($add_row['total_add']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($sub_row['total_sub']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($bill_row['net_amt']).'</td>';
				$table .= '</tr>';
				
			}
		$table .= '</tr>';

		/* MR */
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">MR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">MR NO</th>';
			$table .= '<th class="text-center" colspan="2">DATE</th>';
			$table .= '<th class="text-center" colspan="2">BILL  AMOUNT</th>';
			$table .= '<th class="text-center" colspan="2">TOTAL RECEIVED</th>';
			$table .= '<th class="text-center" colspan="2">DUE AMOUNT</th>';
			$table .= '</tr>';
			foreach ($mr_data as $mr) {
			$table .= '<tr>';
			$table .= '<td class="text-center" colspan="2">'.$mr['mr_no'].'</td>';
			$table .= '<td class="text-center" colspan="2">'.risFormatDate($mr['mr_date']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_amount']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_rec']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_pending']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

	}else{
		$mr 		= "SELECT * FROM `mr` WHERE `mr_no` LIKE '%$mr%' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' GROUP BY mr_no";
		$mr_res 	= mysqli_query($conn,$mr);
		$mr_data	= array();
		while ($mr_row = mysqli_fetch_assoc($mr_res)) {
			$mr_data[] = $mr_row;
			//BILL
			$bill 		= "SELECT * FROM `billing` WHERE `invoice_no` = '".$mr_row['bill_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' GROUP BY invoice_no";
			$bill_res	= mysqli_query($conn,$bill);
		}
		$bill_data = array();
		while ($bill_row = mysqli_fetch_assoc($bill_res)) {
			$bill_data[] = $bill_row;
		}

		foreach ($mr_data as $mr_details) {
			$bills 		= "SELECT * FROM `billing` WHERE `invoice_no` = '".$mr_details['bill_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$bills_res	= mysqli_query($conn,$bills);
		}
		$lr_data = array();
		while ($bills_row = mysqli_fetch_assoc($bills_res)) {
			$lr_data[] = $bills_row;
		}
		//LHC DATA
		foreach ($lr_data as $fetch_lhc) {
			$lhc = "SELECT * FROM `lhc` WHERE `lr_no` = '".$fetch_lhc['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."' GROUP BY lhc_no";
			$lhc_res = mysqli_query($conn,$lhc);
		}
		$lhc_data = array();
		while ($lhc_row = mysqli_fetch_assoc($lhc_res)) {
			$lhc_data[] = $lhc_row;
		}

		$master_accounts  = combine(getAllRecords('vendor_master'), 'id', 'name');
		$table       	  = '';
		
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LR NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">FROM - TO</th>';
			$table .= '<th class="text-center" colspan="2">CONSINEE</th>';
			$table .= '<th class="text-center" colspan="2">CONSINOR</th>';
			$table .= '<th class="text-center">AGENT</th>';
			$table .= '<th class="text-center">WEIGHT</th>';
			$table .= '<th class="text-center">FRIGHT</th>';
			$table .= '</tr>';
			foreach ($lr_data as $fetch_lr) {
				$lr = "SELECT * FROM lr_mst WHERE lr_no = '".$fetch_lr['lr_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$lr_res = mysqli_query($conn,$lr);
				while ($lr_row = mysqli_fetch_assoc($lr_res)) {
					$table .= '<tr>';
					$table .= '<td class="text-center">'.$lr_row['lr_no'].'</td>';
					$table .= '<td class="text-center">'.risFormatDate($lr_row['l_date']).'</td>';
					$table .= '<td class="text-center">'.$lr_row['from_place'].' - '.$lr_row['to_place'].'</td>';
					$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_row['consignee_name']].'</td>';
					$table .= '<td class="text-center" colspan="2">'.@$master_accounts[$lr_row['consignor_name']].'</td>';
					$table .= '<td class="text-center">'.$lr_row['agent'].'</td>';
					$table .= '<td class="text-center">'.$lr_row['weight'].'</td>';
					$table .= '<td class="text-center">'.$lr_row['freight'].'</td>';
					$table .= '</tr>';
				}
			}
			
		$table .= '</tr>';

		/*LHC*/
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">LHC DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center">LHC NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center">LORRY HIRE</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">ADVANCE</th>';
			$table .= '<th class="text-center">DISEL</th>';
			$table .= '<th class="text-center">NET</th>';
			$table .= '<th class="text-center">PAID</th>';
			$table .= '<th class="text-center">BALANCE</th>';
			$table .= '</tr>';

			foreach ($lhc_data as $lhc_data) {
			$lhc_add = "SELECT SUM(`amount`) AS addition FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_add_res = mysqli_query($conn,$lhc_add);
			$lhc_add_row = mysqli_fetch_assoc($lhc_add_res);

			$lhc_deduction = "SELECT SUM(`amount`) AS sub FROM `lhc_details` WHERE `lhc_id` = '".$lhc_data['lhc_no']."' AND `type` = 'sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$lhc_deduction_res = mysqli_query($conn,$lhc_deduction);
			$lhc_deduction_row = mysqli_fetch_assoc($lhc_deduction_res);

			$paid = "SELECT SUM(`amount`) AS paid FROM `lhc_payment_status` WHERE `lhc_no`  = '".$lhc_data['lhc_no']."' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
			$paid_res = mysqli_query($conn,$paid);
			$paid_row = mysqli_fetch_assoc($paid_res);

			$table .= '<tr>';
			$table .= '<td class="text-center">'.$lhc_data['lhc_no'].'</td>';
			$table .= '<td class="text-center">'.risFormatDate($lhc_data['lhc_date']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['lorry_hire']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_add_row['addition']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_deduction_row['sub']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['adv_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['disel_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['net_amnt']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($paid_row['paid']).'</td>';
			$table .= '<td class="text-center">'.customFormatMoney($lhc_data['balance']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';

		/*BILLING*/

		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">BILLING DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">BILL NO</th>';
			$table .= '<th class="text-center">DATE</th>';
			$table .= '<th class="text-center" colspan="3">PARTY</th>';
			$table .= '<th class="text-center">GROSS AMOUNT</th>';
			$table .= '<th class="text-center">TOTAL ADDITION</th>';
			$table .= '<th class="text-center">TOTAL DEDUCTION</th>';
			$table .= '<th class="text-center">NET AMOUNT</th>';
			$table .= '</tr>';
			foreach ($bill_data as $bill_row) {
				
				$add ="SELECT SUM(`amount`) AS total_add FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` ='add' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$add_res = mysqli_query($conn,$add);
				$add_row = mysqli_fetch_assoc($add_res);

				$sub ="SELECT SUM(`amount`) AS total_sub FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND `type` ='sub' AND branch_id = '".$_SESSION['branch_id']."' AND company_id = '".$_SESSION['company_id']."'";
				$sub_res = mysqli_query($conn,$sub);
				$sub_row = mysqli_fetch_assoc($sub_res);

				$table .= '<tr>';
				$table .= '<td class="text-center" colspan="2">'.$bill_row['invoice_no'].'</td>';
				$table .= '<td class="text-center">'.risFormatDate($bill_row['bill_date']).'</td>';
				$table .= '<td class="text-center" colspan="3">'.@$master_accounts[$bill_row['vendor_id']].'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($bill_row['gross_amt']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($add_row['total_add']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($sub_row['total_sub']).'</td>';
				$table .= '<td class="text-center">'.customFormatMoney($bill_row['net_amt']).'</td>';
				$table .= '</tr>';
				
			}
		$table .= '</tr>';

		/* MR */
		$table .= '<tr>';
		$table .= '<th class="text-center" colspan="10">MR DETAILS</th>';
			$table .= '<tr>';
			$table .= '<th class="text-center" colspan="2">MR NO</th>';
			$table .= '<th class="text-center" colspan="2">DATE</th>';
			$table .= '<th class="text-center" colspan="2">BILL  AMOUNT</th>';
			$table .= '<th class="text-center" colspan="2">TOTAL RECEIVED</th>';
			$table .= '<th class="text-center" colspan="2">DUE AMOUNT</th>';
			$table .= '</tr>';
			foreach ($mr_data as $mr) {
			$table .= '<tr>';
			$table .= '<td class="text-center" colspan="2">'.$mr['mr_no'].'</td>';
			$table .= '<td class="text-center" colspan="2">'.risFormatDate($mr['mr_date']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_amount']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_rec']).'</td>';
			$table .= '<td class="text-center" colspan="2">'.customFormatMoney($mr['bill_pending']).'</td>';
			$table .= '</tr>';
			}
		$table .= '</tr>';
	}
}

$data = array('status' => 'success', 'tbody' => $table);
echo json_encode($data);
die();
?>