<?php
include('../config.php');
require_once '../PHPExcel/Classes/PHPExcel.php';
$objPHPExcel   = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

if($_GET['module'] == 'BR'){
    $rowcount       = 3;
    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 * FROM `lr_mst` WHERE agent = '".$broker."' AND truck_no = '".$truck_no."' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."')AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lr_mst` WHERE truck_no = '".$truck_no."' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."')AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }elseif(!empty($vendor_id) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lr_mst` WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }else if(!empty($vendor_id)){
        $lr = "SELECT * FROM `lr_mst` WHERE consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";

    }else if(!empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lr_mst` WHERE agent = '".$broker."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lr_mst` WHERE truck_no = '".$truck_no."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }else if(!empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lr_mst` WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }else{
        $lr = "SELECT * FROM `lr_mst` WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lr_no";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    while($row = mysqli_fetch_array($resultset)){
        $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 = '';
        }
        
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:O1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . ' BOOKING REGISTER REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','LR NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','LR DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','CONSIGNOR');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','CONSIGNEE');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','BILL TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','FROM');
        $objPHPExcel->getActiveSheet()->SetCellValue('J2','TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('K2','PCS');
        $objPHPExcel->getActiveSheet()->SetCellValue('L2','WEIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('M2','FREIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('N2','PAID BY');
        $objPHPExcel->getActiveSheet()->SetCellValue('O2','BROKER');
        $objPHPExcel->getActiveSheet()->SetCellValue('P2','EWAY BILL');
        $objPHPExcel->getActiveSheet()->SetCellValue('Q2','EWAY BILL DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('R2','EWAY BILL E.DATE');

        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['lr_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['l_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['consignor_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $nor_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, @$master_accounts[$row['consignee_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $nee_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, @$master_accounts[$row['billing_to']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['from_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['to_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['pcs']);
        $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $row['weight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowcount, $row['freight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowcount, $row['billing_party']);
        $objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowcount, $row['agent']);
        $objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowcount, $row['eway_bill_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('Q'.$rowcount, $edate);
        $objPHPExcel->getActiveSheet()->SetCellValue('R'.$rowcount, $exdate);
        $rowcount++; 
    } 
    $filename = " BOOKING REGISTER REPORT " .".xls";
}
if($_GET['module'] == 'CR'){
    $rowcount       = 3;
    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 * FROM `contract` WHERE agent = '".$broker."' AND truck_no = '".$truck_no."' AND (vendor_id = '".$vendor_id."')AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
    $lr = "SELECT * FROM `contract` WHERE truck_no = '".$truck_no."' AND (vendor_id = '".$vendor_id."')AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }elseif(!empty($vendor_id) && !empty($sd) && !empty($ed)){
    $lr = "SELECT * FROM `contract` WHERE (vendor_id = '".$vendor_id."') AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }else if(!empty($vendor_id)){
    $lr = "SELECT * FROM `contract` WHERE vendor_id = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }else if(!empty($broker) && !empty($sd) && !empty($ed)){
    $lr = "SELECT * FROM `contract` WHERE agent = '".$broker."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
    $lr = "SELECT * FROM `contract` WHERE truck_no = '".$truck_no."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }else if(!empty($sd) && !empty($ed)){
    $lr = "SELECT * FROM `contract` WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }else{
    $lr = "SELECT * FROM `contract` WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY code";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    while($row = mysqli_fetch_array($resultset)){
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:O1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . ' CONTRACT REGISTER REPORT ');
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
    $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
    $objPHPExcel->getActiveSheet()->SetCellValue('B2','CODE');
    $objPHPExcel->getActiveSheet()->SetCellValue('C2','DATE');
    $objPHPExcel->getActiveSheet()->SetCellValue('D2','TRUCK NO');
    $objPHPExcel->getActiveSheet()->SetCellValue('E2','VENDOR');
    $objPHPExcel->getActiveSheet()->SetCellValue('F2','FROM');
    $objPHPExcel->getActiveSheet()->SetCellValue('G2','TO');
    $objPHPExcel->getActiveSheet()->SetCellValue('H2','WEIGHT');
    $objPHPExcel->getActiveSheet()->SetCellValue('I2','KM');
    $objPHPExcel->getActiveSheet()->SetCellValue('J2','FREIGHT');
    $objPHPExcel->getActiveSheet()->SetCellValue('K2','BROKER');
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['code']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['c_date'])));
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['truck_no']);
    $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, @$master_accounts[$row['vendor_id']]);
    $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['from_place']);
    $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['to_place']);
    $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $row['weight']);
    $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['km']);
    $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['amount']);
    $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['agent']);
    $rowcount++; 
    } 
    $filename = " CONTRACT REGISTER REPORT " .".xls";
}
if($_GET['module'] == 'PCFCL'){
    $rowcount       = 3;
    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 * FROM contract WHERE truck_no = '".$truck_no."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (vendor_id = '".$vendor_id."') AND agent = '".$broker."' AND truck_no = '".$truck_no."' GROUP BY code";
    
    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE truck_no = '".$truck_no."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (vendor_id = '".$vendor_id."') AND truck_no = '".$truck_no."' GROUP BY code";

    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE truck_no = '".$truck_no."' AND agent = '".$broker."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (vendor_id = '".$vendor_id."') AND truck_no = '".$truck_no."' GROUP BY code";

    }else if(!empty($vendor_id) && !empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE agent = '".$broker."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (vendor_id = '".$vendor_id."') GROUP BY code";

    }else if(!empty($vendor_id) && !empty($sd) && !empty($ed)){
        
        $lr = "SELECT * FROM contract WHERE (vendor_id = '".$vendor_id."') AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY code";

    }else if(!empty($vendor_id)){
        $lr = "SELECT * FROM contract WHERE (vendor_id = '".$vendor_id."') AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY code";

    }else if(!empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE agent = '".$broker."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY code";

    }else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE truck_no = '".$truck_no."' AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY code";


    }else if(!empty($sd) && !empty($ed)){
         $lr = "SELECT * FROM contract WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY code";
    }else{
         $lr = "SELECT * FROM contract WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY code";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    while($row = mysqli_fetch_array($resultset)){
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:O1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'PENDING CONTRACT FOR CONTRACT LHC ');
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
    $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
    $objPHPExcel->getActiveSheet()->SetCellValue('B2','CODE');
    $objPHPExcel->getActiveSheet()->SetCellValue('C2','DATE');
    $objPHPExcel->getActiveSheet()->SetCellValue('D2','TRUCK NO');
    $objPHPExcel->getActiveSheet()->SetCellValue('E2','VENDOR');
    $objPHPExcel->getActiveSheet()->SetCellValue('F2','FROM');
    $objPHPExcel->getActiveSheet()->SetCellValue('G2','TO');
    $objPHPExcel->getActiveSheet()->SetCellValue('H2','WEIGHT');
    $objPHPExcel->getActiveSheet()->SetCellValue('I2','KM');
    $objPHPExcel->getActiveSheet()->SetCellValue('J2','FREIGHT');
    $objPHPExcel->getActiveSheet()->SetCellValue('K2','BROKER');
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['code']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['c_date'])));
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['truck_no']);
    $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, @$master_accounts[$row['vendor_id']]);
    $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['from_place']);
    $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['to_place']);
    $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $row['weight']);
    $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['km']);
    $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['amount']);
    $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['agent']);
    $rowcount++; 
    } 
    $filename = " PENDING CONTRACT FOR CONTRACT LHC  " .".xls";
}
if($_GET['module'] == 'PCFB'){
    $rowcount       = 3;
    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 * FROM contract WHERE (vendor_id = '".$vendor_id."') AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND agent = '".$broker."' AND truck_no = '".$truck_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";
    
    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE (vendor_id = '".$vendor_id."') AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND truck_no = '".$truck_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";
    }else if(!empty($vendor_id) && !empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE (vendor_id = '".$vendor_id."') AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND agent = '".$broker."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";

    }else if(!empty($vendor_id) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE (vendor_id = '".$vendor_id."') AND DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";

    }else if(!empty($vendor_id)){
        $lr = "SELECT * FROM contract WHERE (vendor_id = '".$vendor_id."') AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";

    }else if(!empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND agent = '".$broker."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";

    }else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND truck_no = '".$truck_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";

    }else if(!empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM contract WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";
    }else{
        $lr = "SELECT * FROM contract WHERE DATE(c_date) >= '".$sd."' AND DATE(c_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY code";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    while($row = mysqli_fetch_array($resultset)){
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:O1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'PENDING CONTRACT FOR BILLING');
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
    $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
    $objPHPExcel->getActiveSheet()->SetCellValue('B2','CODE');
    $objPHPExcel->getActiveSheet()->SetCellValue('C2','DATE');
    $objPHPExcel->getActiveSheet()->SetCellValue('D2','TRUCK NO');
    $objPHPExcel->getActiveSheet()->SetCellValue('E2','VENDOR');
    $objPHPExcel->getActiveSheet()->SetCellValue('F2','FROM');
    $objPHPExcel->getActiveSheet()->SetCellValue('G2','TO');
    $objPHPExcel->getActiveSheet()->SetCellValue('H2','WEIGHT');
    $objPHPExcel->getActiveSheet()->SetCellValue('I2','KM');
    $objPHPExcel->getActiveSheet()->SetCellValue('J2','FREIGHT');
    $objPHPExcel->getActiveSheet()->SetCellValue('K2','BROKER');
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['code']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['c_date'])));
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['truck_no']);
    $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, @$master_accounts[$row['vendor_id']]);
    $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['from_place']);
    $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['to_place']);
    $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $row['weight']);
    $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['km']);
    $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['amount']);
    $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['agent']);
    $rowcount++; 
    } 
    $filename = " PENDING CONTRACT FOR BILLING  " .".xls";
}
if($_GET['module'] == 'PLFL'){
    $rowcount       = 3;
    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 * FROM lr_mst WHERE truck_no = '".$truck_no."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND agent = '".$broker."' AND truck_no = '".$truck_no."' GROUP BY lr_no";
    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE truck_no = '".$truck_no."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND truck_no = '".$truck_no."' GROUP BY lr_no";

    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE truck_no = '".$truck_no."' AND agent = '".$broker."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND truck_no = '".$truck_no."' GROUP BY lr_no";

    }else if(!empty($vendor_id) && !empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE agent = '".$broker."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' AND (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') GROUP BY lr_no";

    }else if(!empty($vendor_id) && !empty($sd) && !empty($ed)){
        
        $lr = "SELECT * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY lr_no";

    }else if(!empty($vendor_id)){
        $lr = "SELECT * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY lr_no";

    }else if(!empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE agent = '".$broker."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY lr_no";

    }else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE truck_no = '".$truck_no."' AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY lr_no";


    }else if(!empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY lr_no";
    }else{
        $lr = "SELECT * FROM lr_mst WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND lhc_status = 'N' GROUP BY lr_no";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    while($row = mysqli_fetch_array($resultset)){
        $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);

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:O1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$_SESSION['company_id']] . ' - ' . ' PENDING LR FOR LHC REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','LR NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','LR DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','CONSIGNOR');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','CONSIGNEE');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','BILL TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','FROM');
        $objPHPExcel->getActiveSheet()->SetCellValue('J2','TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('K2','PCS');
        $objPHPExcel->getActiveSheet()->SetCellValue('L2','WEIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('M2','FREIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('N2','PAID BY');
        $objPHPExcel->getActiveSheet()->SetCellValue('O2','BROKER');
        

        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['lr_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['l_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['consignor_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $nor_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, @$master_accounts[$row['consignee_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $nee_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, @$master_accounts[$row['billing_to']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['from_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['to_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['pcs']);
        $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $row['weight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowcount, $row['freight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowcount, $row['billing_party']);
        $objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowcount, $row['agent']);
        
        $rowcount++; 
    } 
    $filename = "PENDING LR FOR LHC" .".xls";
}

if($_GET['module'] == 'PLFB'){

    $rowcount       = 3;
    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 * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND agent = '".$broker."' AND truck_no = '".$truck_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";
    }else if(!empty($vendor_id) && !empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND truck_no = '".$truck_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";
    }else if(!empty($vendor_id) && !empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND agent = '".$broker."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";

    }else if(!empty($vendor_id) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";

    }else if(!empty($vendor_id)){
        $lr = "SELECT * FROM lr_mst WHERE (consignor_name = '".$vendor_id."' OR consignee_name = '".$vendor_id."') AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";

    }else if(!empty($broker) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND agent = '".$broker."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";

    }else if(!empty($truck_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND truck_no = '".$truck_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";

    }else if(!empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM lr_mst WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";
    }else{
        $lr = "SELECT * FROM lr_mst WHERE DATE(l_date) >= '".$sd."' AND DATE(l_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND `billing_status` = 'N' GROUP BY lr_no";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    while($row = mysqli_fetch_array($resultset)){
        $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);

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:O1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$_SESSION['company_id']] . ' - ' . ' PENDING LR FOR BILLING REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','LR NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','LR DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','CONSIGNOR');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','CONSIGNEE');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','BILL TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','FROM');
        $objPHPExcel->getActiveSheet()->SetCellValue('J2','TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('K2','PCS');
        $objPHPExcel->getActiveSheet()->SetCellValue('L2','WEIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('M2','FREIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('N2','PAID BY');
        $objPHPExcel->getActiveSheet()->SetCellValue('O2','BROKER');
        

        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['lr_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['l_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['consignor_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $nor_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, @$master_accounts[$row['consignee_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $nee_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, @$master_accounts[$row['billing_to']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['from_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['to_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['pcs']);
        $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $row['weight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowcount, $row['freight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowcount, $row['billing_party']);
        $objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowcount, $row['agent']);
        
        $rowcount++; 
    } 
    $filename = "PENDING LR FOR BILLING" .".xls";
}

if($_GET['module'] == 'BREG'){
    $rowcount       = 3;
    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
        $vendor_id = strtoupper($_GET['vendor_id']);
    }
    if(isset($_GET['bill_no']) && !empty($_GET['bill_no'])) {
        $bill_no = strtoupper($_GET['bill_no']);
    }

    $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($sd) && !empty($ed)){
        $lr = "SELECT * FROM `billing` WHERE vendor_id = '".$vendor_id."' AND DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }else if(!empty($vendor_id)){
        $lr = "SELECT * FROM `billing` WHERE vendor_id = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";

    }else if(!empty($sd) && !empty($ed) && !empty($bill_no)){
        $lr = "SELECT * FROM `billing` WHERE DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND invoice_no = '".$bill_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }else{
        $lr = "SELECT * FROM `billing` WHERE DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    $total_add = 0;
    $total_sub = 0;
    while($row = mysqli_fetch_array($resultset)){
        $nor     = "SELECT * FROM `vendor_master` WHERE `id` = '".$row['vendor_id']."'";
        $nor_res = mysqli_query($conn,$nor);
        $nor_row = mysqli_fetch_assoc($nor_res);
        
        $add      = "SELECT SUM(amount) AS total_add FROM billing_details WHERE type = 'add' AND bill_no = '".$data['invoice_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY bill_no";
        $add_res  = mysqli_query($conn,$add);
        $add_row  = mysqli_fetch_assoc($add_res);
        
        $sub      = "SELECT SUM(amount) AS total_sub FROM billing_details WHERE type = 'sub' AND bill_no = '".$data['invoice_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY bill_no";
        $sub_res  = mysqli_query($conn,$sub);
        $sub_row  = mysqli_fetch_assoc($sub_res);

        $total_add += $add_row['total_add'];
        $total_sub += $sub_row['total_sub'];

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:N1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'BILLING REGISTER REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','BILL NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','BILL DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','PARTY');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','LR NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','WEIGHT');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','RATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','AMOUNT');
        $objPHPExcel->getActiveSheet()->SetCellValue('J2','OTHER CHANRGES');
        $objPHPExcel->getActiveSheet()->SetCellValue('K2','TOTAL ADDITION');
        $objPHPExcel->getActiveSheet()->SetCellValue('L2','TOTAL DELETION');
        $objPHPExcel->getActiveSheet()->SetCellValue('M2','GROSS AMOUNT');
        $objPHPExcel->getActiveSheet()->SetCellValue('N2','NET AMOUNT');
        
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['invoice_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['bill_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['vendor_id']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $nor_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['lr_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['lr_weight']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $row['lr_rate']);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['lr_amount']);
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['other_charges']);
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $add_row['total_add']);
        $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $sub_row['total_sub']);
        $objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowcount, $row['gross_amt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowcount, $row['net_amt']);
        
        
        $rowcount++; 
    } 
    $filename = " BILLING REGISTER REPORT " .".xls";
}

if($_GET['module'] == 'BREGSUM'){
    $rowcount       = 3;
    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
        $vendor_id = strtoupper($_GET['vendor_id']);
    }

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

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

    }else if(!empty($bill_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `billing` WHERE DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND invoice_no = '".$bill_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no";
    }else{
        $lr = "SELECT * FROM `billing` WHERE DATE(bill_date) >= '".$sd."' AND DATE(bill_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY invoice_no";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    $total_add = 0;
    $total_sub = 0;
    while($row = mysqli_fetch_array($resultset)){
        $nor     = "SELECT * FROM `vendor_master` WHERE `id` = '".$row['vendor_id']."'";
        $nor_res = mysqli_query($conn,$nor);
        $nor_row = mysqli_fetch_assoc($nor_res);
        
        $add      = "SELECT SUM(amount) AS total_add FROM billing_details WHERE type = 'add' AND bill_no = '".$data['invoice_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY bill_no";
        $add_res  = mysqli_query($conn,$add);
        $add_row  = mysqli_fetch_assoc($add_res);
        
        $sub      = "SELECT SUM(amount) AS total_sub FROM billing_details WHERE type = 'sub' AND bill_no = '".$data['invoice_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY bill_no";
        $sub_res  = mysqli_query($conn,$sub);
        $sub_row  = mysqli_fetch_assoc($sub_res);

        $total_add += $add_row['total_add'];
        $total_sub += $sub_row['total_sub'];

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:J1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'BILLING SUMMERY REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','BILL NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','BILL DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','PARTY');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','GST');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','GROSS AMOUNT');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','TOTAL ADDITION');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','TOTAL DEDUCTION');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','NET AMOUNT');
        
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['invoice_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['bill_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['vendor_id']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $nor_row['gstin']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['gross_amt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $add_row['total_add']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $sub_row['total_sub']);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['net_amt']);
        
        
        $rowcount++; 
    } 
    $filename = " BILLING SUMMERY REPORT " .".xls";
}

if($_GET['module'] == 'LHCREG'){
    $rowcount       = 3;
    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['lhc_no']) && !empty($_GET['lhc_no'])) {
        $lhc_no = strtoupper($_GET['lhc_no']);
    }


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

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

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

    }else if(!empty($lhc_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lhc` WHERE lhc_no = '".$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 lhc_no";
    }else if(!empty($lhc_no)){
        $lr = "SELECT * FROM `lhc` WHERE lhc_no = '".$lhc_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
    }else if(!empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `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 lhc_no";
    }else{
        $lr = "SELECT * FROM `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 lhc_no";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $master_truck       = combine(getAllRecords('truck_mst'), 'id', 'o_name');
    $cou = 1;
    $total_add = 0;
    $total_sub = 0;
    while($row = mysqli_fetch_array($resultset)){
        $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);

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

        $agent          = "SELECT * FROM agent WHERE name = '".$row['agent']."'";
        $agent_res      = mysqli_query($conn,$agent);
        $agent_row      = mysqli_fetch_assoc($agent_res);

        $total_add += $add_row['total_add'];
        $total_sub += $sub_row['total_sub'];

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:S1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'LHC REGISTER REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','LHC NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','LHC DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','TRUCK NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','FROM');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','OWNER PANCARD');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','OWNER');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','AGENT PANCARD');
        $objPHPExcel->getActiveSheet()->SetCellValue('J2','AGENT');
        $objPHPExcel->getActiveSheet()->SetCellValue('K2','LORRY HIRE CHARGES');
        $objPHPExcel->getActiveSheet()->SetCellValue('L2','TOTAL ADDITION');
        $objPHPExcel->getActiveSheet()->SetCellValue('M2','DISEL');
        $objPHPExcel->getActiveSheet()->SetCellValue('N2','TOTAL DEDUCTION');
        $objPHPExcel->getActiveSheet()->SetCellValue('O2','ADVANCE');
        $objPHPExcel->getActiveSheet()->SetCellValue('P2','PENDING');
        $objPHPExcel->getActiveSheet()->SetCellValue('Q2','BANK');
        $objPHPExcel->getActiveSheet()->SetCellValue('R2','CASH');
        $objPHPExcel->getActiveSheet()->SetCellValue('S2','PAYMENT');
        
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['lhc_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['lhc_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['truck_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $row['from_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['to_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $truck_row['pan_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, @$master_truck[$row['owner_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $agent_row['pan_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $row['agent']);
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['lorry_hire']);
        $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $add_row['total_add']);
        $objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowcount, $row['disel_amnt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowcount, $sub_row['total_sub']);
        $objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowcount, $row['adv_amnt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowcount, $row['balance']);
        $objPHPExcel->getActiveSheet()->SetCellValue('Q'.$rowcount, $row['pay_mode_amnt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('R'.$rowcount, $row['cash']);
        $objPHPExcel->getActiveSheet()->SetCellValue('S'.$rowcount, $row['payment_to']);
        $rowcount++; 
    } 
    $filename = "LHC REGISTER REPORT " .".xls";
}
if($_GET['module'] == 'TDSPBL'){
    $rowcount       = 3;
    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['lhc_no']) && !empty($_GET['lhc_no'])) {
        $lhc_no = strtoupper($_GET['lhc_no']);
    }


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

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

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

    }else if(!empty($lhc_no) && !empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `lhc` WHERE lhc_no = '".$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 lhc_no";
    }else if(!empty($lhc_no)){
        $lr = "SELECT * FROM `lhc` WHERE lhc_no = '".$lhc_no."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY lhc_no";
    }else if(!empty($sd) && !empty($ed)){
        $lr = "SELECT * FROM `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 lhc_no";
    }else{
        $lr = "SELECT * FROM `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 lhc_no";
    }
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    $master_accounts    = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $master_truck       = combine(getAllRecords('truck_mst'), 'id', 'o_name');
    $cou = 1;
    $total_add = 0;
    $total_sub = 0;
    while($row = mysqli_fetch_array($resultset)){
        $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);

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

        $agent          = "SELECT * FROM agent WHERE name = '".$row['agent']."'";
        $agent_res      = mysqli_query($conn,$agent);
        $agent_row      = mysqli_fetch_assoc($agent_res);

        $total_add += $add_row['total_add'];
        $total_sub += $sub_row['total_sub'];

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:S1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'TDS PAYABLE REPORT ');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','LHC NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','LHC DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','AGENT');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','AGENT PANCARD');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','LORRY HIRE CHARGES');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','TDS AMOUNT');

        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['lhc_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['lhc_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['agent']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $agent_row['pan_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['lorry_hire']);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $sub_row['total_sub']);
        $rowcount++; 
    } 
    $filename = "TDS PAYABLE REPORT".".xls";
}

if($_GET['module'] == 'LHCPENREG'){

    $rowcount       = 3;
    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 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";
    }
    $master_truck       = combine(getAllRecords('truck_mst'), 'id', 'o_name');
    $resultset          = mysqli_query($conn, $lr) or die("database error:". mysqli_error($conn));
    //$master_accounts    = combine(getAllRecords('truck_mst'), 'id', 'name');
    $master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
    $cou = 1;
    $total_add = 0;
    $total_sub = 0;
    while($row = mysqli_fetch_array($resultset)){
        
        $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);

        $total_add += $add_row['total_add'];
        $total_sub += $sub_row['total_sub'];

        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'PENDING LHC REGISTER');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
        $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('B2','LHC NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('C2','LHC DATE');
        $objPHPExcel->getActiveSheet()->SetCellValue('D2','TRUCK NO');
        $objPHPExcel->getActiveSheet()->SetCellValue('E2','FROM');
        $objPHPExcel->getActiveSheet()->SetCellValue('F2','TO');
        $objPHPExcel->getActiveSheet()->SetCellValue('G2','OWNER');
        $objPHPExcel->getActiveSheet()->SetCellValue('H2','AGENT');
        $objPHPExcel->getActiveSheet()->SetCellValue('I2','LORRY HIRE CHARGES');
        $objPHPExcel->getActiveSheet()->SetCellValue('J2','TOTAL ADDITION');
        $objPHPExcel->getActiveSheet()->SetCellValue('K2','DISEL');
        $objPHPExcel->getActiveSheet()->SetCellValue('L2','TOTAL DEDUCTION');
        $objPHPExcel->getActiveSheet()->SetCellValue('M2','ADVANCE');
        $objPHPExcel->getActiveSheet()->SetCellValue('N2','BANK');
        $objPHPExcel->getActiveSheet()->SetCellValue('O2','CASH');
        $objPHPExcel->getActiveSheet()->SetCellValue('P2','PENDING');
        
        
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['lhc_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['lhc_date'])));
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['truck_no']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $row['from_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['to_place']);
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, @$master_truck[$row['owner_name']]);
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $row['agent']);
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $row['lorry_hire']);
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, $add_row['total_add']);
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['disel_amnt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $sub_row['total_sub']);
        $objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowcount, $row['adv_amnt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowcount, $row['pay_mode_amnt']);
        $objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowcount, $row['cash']);
        $objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowcount, $row['balance']);
        
        $rowcount++; 
    } 
    $filename = "PENDING LHC REGISTER REPORT " .".xls";
}

if($_GET['module'] == 'MRREG'){
    $rowcount       = 3;
    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
        $vendor_id = strtoupper($_GET['vendor_id']);
    }

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

    if($sd =='' || $ed =='' || $_GET['vendor_id'] ==''){
        $lr = "SELECT * FROM `mr` AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    if( !empty($sd) && !empty($ed)) {
        $sd = date('Y-m-d', strtotime($sd));
        $ed = date('Y-m-d', strtotime($ed));
        $lr = "SELECT * FROM `mr` WHERE DATE(mr_date) >= '".$sd."' AND DATE(mr_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    if( !empty($vendor_id) ) {
        $lr = "SELECT * FROM `mr` WHERE vendor_id = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    $result                = mysqli_query($conn,$lr);
    $total                 = mysqli_num_rows($result);
    $master_accounts       = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_company        = combine(getAllRecords('company_mst'), 'id', 'name');
    $table          = '';
    $total_count    = 0;
    if(count($total) > 0) {
        $total_bank   = 0;
        $total_add    = 0;
        $total_sub    = 0;
        $total_cash   = 0;
        $cou          = 1;
        while ($row = mysqli_fetch_array($result)) {
            $tota_rec_amnt = "SELECT SUM(bill_rec) AS bill_rec_amnt FROM mr WHERE mr_no = '".$row['mr_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $total_rec_amnt_res = mysqli_query($conn,$tota_rec_amnt);
            $total_rec_amnt_row = mysqli_fetch_assoc($total_rec_amnt_res);

            $add      = "SELECT SUM(amount) AS total_add FROM mr_details WHERE type = 'add' AND mr_no = '".$row['mr_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $add_res  = mysqli_query($conn,$add);
            $add_row  = mysqli_fetch_assoc($add_res);
            
            $sub      = "SELECT SUM(amount) AS total_sub FROM mr_details WHERE type = 'sub' AND mr_no = '".$row['mr_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $sub_res  = mysqli_query($conn,$sub);
            $sub_row  = mysqli_fetch_assoc($sub_res);

            $bank     = "SELECT * FROM `mr_payment_status` WHERE `mr_no` = '".$row['mr_no']."' AND  payment_type = 'BANK' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $bank_res = mysqli_query($conn,$bank);
            $bank_row = mysqli_fetch_assoc($bank_res);

            $cash     = "SELECT * FROM `mr_payment_status` WHERE `mr_no` = '".$row['mr_no']."' AND  payment_type = 'CASH' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $cash_res = mysqli_query($conn,$cash);
            $cash_row = mysqli_fetch_assoc($cash_res);

            $total_bank  += $bank_row['amount'];
            $total_cash  += $cash_row['amount'];
            $total_add   += $add_row['total_add'];
            $total_sub   += $sub_row['total_sub'];

            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:L1');
            $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'MR REGISTER');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
            $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('B2','MR NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('C2','MR DATE');
            $objPHPExcel->getActiveSheet()->SetCellValue('D2','PARTY');
            $objPHPExcel->getActiveSheet()->SetCellValue('E2','INV.NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('F2','INV.AMOUNT');
            $objPHPExcel->getActiveSheet()->SetCellValue('G2','RECEIVED');
            $objPHPExcel->getActiveSheet()->SetCellValue('H2','T.ADDITION');
            $objPHPExcel->getActiveSheet()->SetCellValue('I2','T.DEDUCTION');
            $objPHPExcel->getActiveSheet()->SetCellValue('J2','NET RECEIVED');
            $objPHPExcel->getActiveSheet()->SetCellValue('K2','BANK');
            $objPHPExcel->getActiveSheet()->SetCellValue('L2','CASH');
            
            
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['mr_no']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['mr_date'])));
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['vendor_id']]);
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $row['bill_no']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['bill_amount']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['bill_rec']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, $add_row['total_add']);
            $objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowcount, $sub_row['total_sub']);
            $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowcount, customFormatMoney($total_rec_amnt_row['bill_rec_amnt'] - $sub_row['total_sub']));
            $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowcount, $row['pay_mode_amnt']);
            $objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowcount, $row['cash']);
            
            $rowcount++; 
        }
    } 
    $filename = "MR REGISTER REPORT" .".xls";
}

if($_GET['module'] == 'TDSREC'){
    $rowcount       = 3;
    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
        $vendor_id = strtoupper($_GET['vendor_id']);
    }

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

    if($sd =='' || $ed =='' || $_GET['vendor_id'] ==''){
        $lr = "SELECT * FROM `mr` AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    if( !empty($sd) && !empty($ed)) {
        $sd = date('Y-m-d', strtotime($sd));
        $ed = date('Y-m-d', strtotime($ed));
        $lr = "SELECT * FROM `mr` WHERE DATE(mr_date) >= '".$sd."' AND DATE(mr_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    if( !empty($vendor_id) ) {
        $lr = "SELECT * FROM `mr` WHERE vendor_id = '".$vendor_id."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    $result                = mysqli_query($conn,$lr);
    $total                 = mysqli_num_rows($result);
    $master_accounts       = combine(getAllRecords('vendor_master'), 'id', 'name');
    $master_accounts_pan       = combine(getAllRecords('vendor_master'), 'id', 'pan');
    $master_company        = combine(getAllRecords('company_mst'), 'id', 'name');
    $table          = '';
    $total_count    = 0;
    if(count($total) > 0) {
        $total_bank   = 0;
        $total_add    = 0;
        $total_sub    = 0;
        $total_cash   = 0;
        $cou          = 1;
        while ($row = mysqli_fetch_array($result)) {
            $tota_rec_amnt = "SELECT SUM(bill_rec) AS bill_rec_amnt FROM mr WHERE mr_no = '".$row['mr_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $total_rec_amnt_res = mysqli_query($conn,$tota_rec_amnt);
            $total_rec_amnt_row = mysqli_fetch_assoc($total_rec_amnt_res);

            $add      = "SELECT SUM(amount) AS total_add FROM mr_details WHERE type = 'add' AND mr_no = '".$row['mr_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $add_res  = mysqli_query($conn,$add);
            $add_row  = mysqli_fetch_assoc($add_res);
            
            $sub      = "SELECT SUM(amount) AS total_sub FROM mr_details WHERE type = 'sub' AND mr_no = '".$row['mr_no']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $sub_res  = mysqli_query($conn,$sub);
            $sub_row  = mysqli_fetch_assoc($sub_res);

            $bank     = "SELECT * FROM `mr_payment_status` WHERE `mr_no` = '".$row['mr_no']."' AND  payment_type = 'BANK' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $bank_res = mysqli_query($conn,$bank);
            $bank_row = mysqli_fetch_assoc($bank_res);

            $cash     = "SELECT * FROM `mr_payment_status` WHERE `mr_no` = '".$row['mr_no']."' AND  payment_type = 'CASH' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' GROUP BY mr_no";
            $cash_res = mysqli_query($conn,$cash);
            $cash_row = mysqli_fetch_assoc($cash_res);

            $total_bank  += $bank_row['amount'];
            $total_cash  += $cash_row['amount'];
            $total_add   += $add_row['total_add'];
            $total_sub   += $sub_row['total_sub'];

            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:L1');
            $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$row['company_id']] . ' - ' . 'TDS RECEIVABLE');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
            $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('B2','INV.NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('C2','INVOICE DATE');
            $objPHPExcel->getActiveSheet()->SetCellValue('D2','PARTY');
            $objPHPExcel->getActiveSheet()->SetCellValue('E2','PANCARD');
            $objPHPExcel->getActiveSheet()->SetCellValue('F2','INV.AMOUNT');
            $objPHPExcel->getActiveSheet()->SetCellValue('G2','TDS');

            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['bill_no']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, date("d-m-Y", strtotime($row['bill_date'])));
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, @$master_accounts[$row['vendor_id']]);
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, @$master_accounts_pan[$row['vendor_id']]);
            $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['bill_amount']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $sub_row['total_sub']);

            $rowcount++; 
        }
    } 
    $filename = "MR REGISTER REPORT" .".xls";
}

if($_GET['module'] == 'OUTRPRT'){
    $rowcount       = 3;
    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
        $vendor_id = strtoupper($_GET['vendor_id']);
    }

    $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($sd =='' || $ed =='' || $_GET['vendor_id'] ==''){
        $lr     = "SELECT * FROM `trans`";
    }

    if( !empty($sd) && !empty($ed)) {
        $sd = date('Y-m-d', strtotime($sd));
        $ed = date('Y-m-d', strtotime($ed));
        $lr = "SELECT * FROM `trans` WHERE DATE(voucher_date) >= '".$sd."' AND DATE(voucher_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    if(!empty($sd) && !empty($ed)){
        $date = $ed;
    }else{
        $date = date('Y-m-d');
    }
    if( !empty($vendor_id) ) {
        $master_vendor       = combine(getAllRecords('vendor_master'), 'id', 'name');
        $vendor_name         = @$master_vendor[$vendor_id];
        $account                 = "SELECT * FROM `act_master` WHERE `ac_name` = '".$vendor_name."'";
        $account_res             = mysqli_query($conn,$account);
        $account_row             = mysqli_fetch_assoc($account_res);
        $lr = "SELECT * FROM `trans` WHERE act_code = '".$account_row['act_code']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    $result                = mysqli_query($conn,$lr);
    $total                 = mysqli_num_rows($result);
    $master_accounts       = combine(getAllRecords('act_master'), 'act_code', 'ac_name');
    $master_company        = combine(getAllRecords('company_mst'), 'id', 'name');
    $table          = '';
    $total_count    = 0;
    if(count($total) > 0) {
        $cou          = 1;
        $data = array();
        while ($row = mysqli_fetch_array($result)) {
            $data[] = $row['act_code'];
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
            $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$_SESSION['company_id']] . ' - ' . 'OUTSTANDING REPORT');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
            $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('B2','PARTY');
            $objPHPExcel->getActiveSheet()->SetCellValue('C2','BILL NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('D2','DATE');
            $objPHPExcel->getActiveSheet()->SetCellValue('E2','BILL AMOUNT');
            $objPHPExcel->getActiveSheet()->SetCellValue('F2','RECEIVED');
            $objPHPExcel->getActiveSheet()->SetCellValue('G2','BALANCE');
            $objPHPExcel->getActiveSheet()->SetCellValue('H2','DAYS');
            
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, @$master_accounts[$row['act_code']]);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, $row['voucher_no']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, risFormatDate($row['voucher_date']));
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $row['amount']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['adjust']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['balance']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, date_diffrence($row['voucher_date'],$date));
            $rowcount++;  
    }
    
    }
    
    $filename = "OUTSTANDING REPORT" .".xls";
}

if($_GET['module'] == 'OUTSTDPRT'){
    $rowcount       = 3;
    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
        $vendor_id = strtoupper($_GET['vendor_id']);
    }

    $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($sd =='' || $ed =='' || $_GET['vendor_id'] ==''){
        $lr     = "SELECT * FROM `trans`";
    }

    if( !empty($sd) && !empty($ed)) {
        $sd = date('Y-m-d', strtotime($sd));
        $ed = date('Y-m-d', strtotime($ed));
        $lr = "SELECT * FROM `trans` WHERE DATE(voucher_date) >= '".$sd."' AND DATE(voucher_date) <= '".$ed."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    if(!empty($sd) && !empty($ed)){
        $date = $ed;
    }else{
        $date = date('Y-m-d');
    }
    if( !empty($vendor_id) ) {
        $master_vendor       = combine(getAllRecords('vendor_master'), 'id', 'name');
        $vendor_name         = @$master_vendor[$vendor_id];
        $account                 = "SELECT * FROM `act_master` WHERE `ac_name` = '".$vendor_name."'";
        $account_res             = mysqli_query($conn,$account);
        $account_row             = mysqli_fetch_assoc($account_res);
        $lr = "SELECT * FROM `trans` WHERE act_code = '".$account_row['act_code']."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
    }

    $result                = mysqli_query($conn,$lr);
    $total                 = mysqli_num_rows($result);
    $master_accounts       = combine(getAllRecords('act_master'), 'act_code', 'ac_name');
    $master_company        = combine(getAllRecords('company_mst'), 'id', 'name');
    $table          = '';
    $total_count    = 0;
    if(count($total) > 0) {
        $cou          = 1;
        $data = array();
        while ($row = mysqli_fetch_array($result)) {
            $data[] = $row['act_code'];
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
            $objPHPExcel->getActiveSheet()->setCellValue('A1',@$master_company[$_SESSION['company_id']] . ' - ' . 'OUTSTANDING REPORT');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold( true );
            $objPHPExcel->getActiveSheet()->SetCellValue('A2','SR.NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('B2','BILL NO');
            $objPHPExcel->getActiveSheet()->SetCellValue('C2','BILL DATE');
            $objPHPExcel->getActiveSheet()->SetCellValue('D2','BILL AMOUNT');
            $objPHPExcel->getActiveSheet()->SetCellValue('E2','RECEIVED');
            $objPHPExcel->getActiveSheet()->SetCellValue('F2','BALANCE');
            $objPHPExcel->getActiveSheet()->SetCellValue('G2','BILL SUB.BY');
            $objPHPExcel->getActiveSheet()->SetCellValue('H2','BILL SUB.DATE');
            $objPHPExcel->getActiveSheet()->SetCellValue('I2','DAYS');
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $cou++);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, @$master_accounts[$row['voucher_no']]);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, risFormatDate($row['voucher_date']));
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['amount']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowcount, $row['adjust']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowcount, $row['balance']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowcount, $row['bill_submited_by']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, risFormatDate($row['bill_submited_date']));
            $objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowcount, date_diffrence($row['voucher_date'],$date));
            $rowcount++;  
    }
    
    }
    
    $filename = "OUTSTANDING REPORT" .".xls";
}
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=" . $filename);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

$objWriter->save('php://output');

?>