<?php
include("dbconnect.php");
$filename = "customerstatement.xls";
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');

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


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("RMS System")
->setLastModifiedBy("cleophas-mutai")
->setTitle("Office 2007 XLSX Document")
->setSubject("Office 2007 XLSX Document")
->setDescription("Document for Office 2007 XLSX, generated by Rms.")
->setKeywords("class_list Excel 2007")
->setCategory("class_list file");
$query="SELECT * FROM school_info";
$result=@mysqli_query($con,$query);
$resultArr = @mysqli_fetch_array($result);

$school_name =$resultArr['school_name'];
$logo = $resultArr['logo'];
$email = $resultArr['email'];
$address= 'P.O Box '.$resultArr['address'].'-'. $resultArr['code'].' '. $city = $resultArr['city'];
$city = $resultArr['city'];
$phone = $resultArr['phone'];
$type = $resultArr['type'];
$tittlesss='';
$customer=@$_GET['customer'];
$ssasddll=mysqli_query($con,"SELECT * FROM `customers` WHERE cusno='$customer'");
$rrooowsa=mysqli_fetch_array($ssasddll);
$tittlesss=$customer.' '.$rrooowsa['fullname'].' STATEMENT';
$balance=$rrooowsa['balance'];
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $email)
->setCellValue('A4', $tittlesss);
$objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:E2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:E3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:E4');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('8');
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('20');
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('15');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A5', 'S/N')
->setCellValue('B5', 'Transactions')
->setCellValue('C5', 'Date')
->setCellValue('D5', 'Amount')
->setCellValue('E5', 'Balance');
$objPHPExcel->getActiveSheet() ->getStyle('A1:E5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:E5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#CCFFCC');
$rrr=1;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), '')
->setCellValue('B'.(5+$rrr), 'Opening Balance')
->setCellValue('E'.(5+$rrr), $balance);
$objPHPExcel->getActiveSheet()->mergeCells('B'.(5+$rrr).':D'.(5+$rrr));
$rrr++;
$sasaqw=mysqli_query($con,"SELECT * FROM `cus_trans`  WHERE `customerrno`='$customer'");
while($roows=mysqli_fetch_array($sasaqw)){
$trans=$roows['transtype'];
$transactionvv='';
$amount1=$roows['amount'];
$dddat=$roows['created_at'];
$datecreated=date("Y-m-d",strtotime($dddat));
$amount=0;
if($trans==5){
$transactionvv='Customer Debt'; 
$amount=$amount1;  
}
else{
$transactionvv='Customer Payment';
$amount=$amount1*(-1);  
}
$balance=$balance+$amount;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), $rrr)
->setCellValue('B'.(5+$rrr), $transactionvv)
->setCellValue('C'.(5+$rrr), $datecreated)
->setCellValue('D'.(5+$rrr), $amount)
->setCellValue('E'.(5+$rrr), "=E".(5+$rrr-1)."+D".(5+$rrr));
$rrr++;
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), '')
->setCellValue('B'.(5+$rrr), 'Balance')
->setCellValue('E'.(5+$rrr), "=E".(5+$rrr-1));
$objPHPExcel->getActiveSheet()->mergeCells('B'.(5+$rrr).':D'.(5+$rrr));
$objPHPExcel->getActiveSheet()->getStyle('D6:E'.(5+$rrr))
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$rrr++;
$objPHPExcel->getActiveSheet()->getStyle('A'.(4+$rrr).':E'.(4+$rrr))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1:E'.(4+$rrr))->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '#000000'
)
)
)
);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
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;

?>