<?php
session_start();
include("../../../../config/database.php");
$setExcelName	   = "invoicing_rpt".time(); 
include("functions.php");
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');


require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
for($col = 'A'; $col !== 'Q'; $col++) {
    $objPHPExcel->getActiveSheet()
        ->getColumnDimension($col)
        ->setAutoSize(true);}
$styleArray1 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => 'ff1a1a'),
        'size'  => 16,
		'name'  => 'Cambria'
    ));
$styleArray2 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '009900'),
        'size'  => 11,
		'name'  => 'Cambria'
    ));	
$styleArray3 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '3333cc'),
        'size'  => 11,
		'name'  => 'Cambria'
    ));	
$styleArray4 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '000'),
        'size'  => 11,
		'name'  => 'Cambria'
    ));	
$styleArray5 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '3333cc'),
        'size'  => 12,
		'name'  => 'Cambria'
    ));
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
			 ->setLastModifiedBy("Maarten Balliauw")
			 ->setTitle("Office 2007 XLSX Test Document")
			 ->setSubject("Office 2007 XLSX Test Document")
			 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
			 ->setKeywords("office 2007 openxml php")
			 ->setCategory("Test result file");
// Add some data
$emp					= $_SESSION['date_rpt_bill_emp'];
$to						= $_SESSION['date_rpt_bill_to'] ;
$from					= $_SESSION['date_rpt_bill_from'];
$entry					= $_SESSION['date_rpt_status'];
//HEADIGN
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:K1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'BANK MITRA DATE WISE BILL REPORT');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getStyle('A1:K1')->applyFromArray($styleArray1);

$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:K2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', ' From Date : '.date('d-m-Y',strtotime($from)).' To Date : '.date('d-m-Y',strtotime($to)));
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getStyle('A2:K2')->applyFromArray($styleArray2);	
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', 'S No')
            ->setCellValue('B3', 'E.A')
			->setCellValue('C3', 'BANK NAME')
			->setCellValue('D3', 'BRANCH NAME')
            ->setCellValue('E3', 'A/C NAME')
            ->setCellValue('F3', 'A/C NO')
			->setCellValue('G3', 'BILL DATE')
			->setCellValue('H3', 'BILL ENTRY DATE')
            ->setCellValue('I3', 'BILL NO')
			->setCellValue('J3', 'BILL AMOUNT')
			->setCellValue('K3', 'RECEIPT AMOUNT');									
$objPHPExcel->getActiveSheet()->getStyle('A3:K3')->applyFromArray($styleArray3);								
$i	= 4;	
$bill_query		= mysql_query($_SESSION['date_rpt_bill_qry'])or die(mysql_error());
while( $data		= mysql_fetch_assoc($bill_query))
{
		$filed			= 'bill_no';
		if($_SESSION['date_rpt_status']=='Receipt')
		{
			$filed		= 'bill_id';	
		}	
		$rpt_bill_amt	= '0.0';
		$rpt_receipt_amt= '0.0';	
		$qry			= mysql_query("SELECT * FROM `tbl_bill` where `bill_no`='".$data[$filed]."' and `company`='".$data['company']."' ")or die(mysql_error());
		$data1			= mysql_fetch_array($qry);	
		///bill amount	
		$bill_qry		= mysql_query("SELECT sum(amount) FROM `tbl_bill` WHERE `bill_no`='".$data[$filed]."' and `company`='".$data['company']."' " );
		$bill_amt		= mysql_fetch_array($bill_qry);	
		///bill receipt amount	
		$bill_qry2		= mysql_query("SELECT sum(amount) FROM  `tbl_bill_pay` WHERE `bill_id`='".$data[$filed]."'  and `company`='".$data['company']."' ");
		$bill_amt2		= mysql_fetch_array($bill_qry2);	
		// Bill Work
		$bill_qry3		= mysql_query("SELECT * FROM `tbl_bill_work` WHERE `bill_no`='".$data[$filed]."' and `company`='".$data['company']."' " );
		$bill_amt3		= mysql_fetch_array($bill_qry3);
		$bname			= "Multiple Ac Bill";
		$b_no			= "-";
        if($data1['bill_type']!='1')
		{
			$bname		= register1($data1['account_id']);
			$b_no		= register2($data1['account_id']);
		} 
		if($bill_amt['sum(amount)']>0)
		{
			$rpt_bill_amt = $bill_amt['sum(amount)'];
		}
		if($bill_amt2['sum(amount)']>0)
		{
			$rpt_receipt_amt = $bill_amt2['sum(amount)'];
		}
		  	$objPHPExcel->setActiveSheetIndex(0)			
							  ->setCellValue('A'.$i, ($i-3))
							  ->setCellValue('B'.$i, company($data1['company']))
							  ->setCellValue('C'.$i, bank($data1['bank_id']))
							  ->setCellValue('D'.$i, branch($data1['branch_id']))
							  ->setCellValue('E'.$i, $bname)
							  ->setCellValue('F'.$i, $b_no)
							  ->setCellValue('G'.$i, $data1['date'])
							  ->setCellValue('H'.$i, $bill_amt3['datetime'])
							  ->setCellValue('I'.$i, $data1['bill_no'])
							  ->setCellValue('J'.$i, $rpt_bill_amt)
							  ->setCellValue('K'.$i, $rpt_receipt_amt);
		$i++;
}							  							  
mysql_close();
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$setExcelName.".xls");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;