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

$rowcount       = 3;
$type 			= $_GET['type'];
if($type == 'OWNER'){
	$gl = "SELECT * FROM `lhc` WHERE agent = '".$_POST['id']."' AND payment_to = 'OWNER' AND balance > 0";
	$lhc      = "SELECT * FROM `lhc` WHERE agent = '".$_POST['employee_id']."' AND payment_to = 'OWNER' AND balance > 0";
	$lhc_res  = mysqli_query($conn,$lhc);
	$lhc_row  = mysqli_fetch_assoc($lhc_res);
	$name     = $lhc_row['agent'];
}else{
	$gl = "SELECT * FROM `lhc` WHERE owner_name = '".$_POST['id']."' AND payment_to = 'AGENT' AND balance > 0";
	$lhc      = "SELECT * FROM `lhc` WHERE owner_name = '".$_POST['id']."' AND payment_to = 'AGENT' AND balance > 0";
	$lhc_res  = mysqli_query($conn,$lhc);
	$lhc_row  = mysqli_fetch_assoc($lhc_res);
	$name     = $lhc_row['agent'];
}

$master_truck       = combine(getAllRecords('truck_mst'), 'id', 'o_name');
$resultset          = mysqli_query($conn, $gl) 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']."' 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']."' 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++; 
} 
echo '<pre>';
print_r($objWriter);
echo '</pre>';
die();
$filename = "PENDING LHC REGISTER 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');

?>