<?php
include("dbconnect.php");
$filename = "PAYMENTLISTS.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);
$classid=@$_GET['forms'];
$streamsid=@$_GET['streams'];
$modeid=@$_GET['mode'];
$bankid=@$_GET['banks'];
$filters=@$_GET['filters'];
$datefrom=@$_GET['dfrom'];
$dateto=@$_GET['dto'];
$stateme='';
if($filters==2){
	$stateme=' From '.$datefrom. ' To '.$dateto;
}
$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'];
$ssasddll=mysqli_query($con,"SELECT * FROM forms WHERE id='$classid'");
$rrooowsa=mysqli_fetch_array($ssasddll);
$formname=$rrooowsa['form'];
$ssasddllstream=mysqli_query($con,"SELECT * FROM streams WHERE id='$streamsid'");
$rrooowsastrea=mysqli_fetch_array($ssasddllstream);
$streamname=$rrooowsastrea['stream'];
$ssasddllsmode=mysqli_query($con,"SELECT * FROM pmodes WHERE id='$modeid'");
$rrooowsamode=mysqli_fetch_array($ssasddllsmode);
$modemname=$rrooowsamode['mode'];
$ssasddllsbank=mysqli_query($con,"SELECT * FROM banks WHERE id='$bankid'");
$rrooowsabank=mysqli_fetch_array($ssasddllsbank);
$bankmname=$rrooowsabank['bankname'];
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $email)
->setCellValue('A4', 'Stream '.$streamname.' Class '.$type.' '.$formname.' Payment Lists. Mode: '.$modemname.' Bank :'.$stateme);
$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:P3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:P4');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('8');
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('12');
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth('15');
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth('15');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A5', 'S/N')
->setCellValue('B5', 'REC#')
->setCellValue('C5', 'ADM NO')
->setCellValue('D5', 'NAME')
->setCellValue('E5', 'CLASS')
->setCellValue('F5', 'STREAM')
->setCellValue('G5', 'YEAR')
->setCellValue('H5', 'TERM')
->setCellValue('I5', 'AMOUNT')
->setCellValue('J5', 'Mode')
->setCellValue('K5', 'Bank')
->setCellValue('L5', 'slip')
->setCellValue('M5', 'Deposited')
->setCellValue('N5', 'Paid By')
->setCellValue('O5', 'Served By')
->setCellValue('P5', 'Date Paid');
$objPHPExcel->getActiveSheet() ->getStyle('A1:P5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$rrr=1;
$SQLLL="SELECT *,
    (SELECT periods FROM academicyearsperiods WHERE academicyearsperiods.id=feepayments.periods) AS academicp,
    (SELECT academicyear FROM academicyears WHERE academicyears.id=feepayments.years) AS academicy,
    (SELECT form FROM forms WHERE forms.id=feepayments.form_id) AS form,
    (SELECT stream FROM streams WHERE streams.id=feepayments.stream_id) AS stream,
    (SELECT mode FROM pmodes WHERE pmodes.id=feepayments.modeofpayment) AS modes,
    (SELECT bankname FROM banks WHERE banks.id=feepayments.bank) AS bankname,
    (SELECT fullname FROM finance_users WHERE finance_users.id=feepayments.user_id) AS users,
    (SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feepayments.adm_no) AS studentname
     FROM `feepayments` WHERE status='1'";

if(@$_GET['filters']!=''){
$datetype=$_GET['filters'];
if($datetype=="1"){
}
elseif($datetype=="2"){
if(@$_GET['dfrom']!='' && @$_GET['dto']!=''){
	$ddfrom=@$_GET['dfrom'];
	$ddto=@$_GET['dto'];
	$SQLLL=$SQLLL." AND DATE(created_at)>='$ddfrom' AND DATE(created_at)<='$ddto'";
}	
}
}
if(@$_GET['streams']!=""){
	$streammid=@$_GET['streams'];
if(@$_GET['streams']=="all"){
}
else{
$SQLLL=$SQLLL." AND stream_id='$streammid'";	
}
}
if(@$_GET['forms']!=""){
	$formsid=@$_GET['forms'];
if(@$_GET['forms']=="all"){
}
else{
$SQLLL=$SQLLL." AND form_id='$formsid'";	
}
}
if(@$_GET['banks']!=""){
	$banksid=@$_GET['banks'];
if(@$_GET['banks']=="all"){
}
else{
$SQLLL=$SQLLL." AND bank='$banksid'";	
}
}
if(@$_GET['mode']!=""){
	$modeid=@$_GET['mode'];
if(@$_GET['mode']=="all"){
}
else{
$SQLLL=$SQLLL." AND modeofpayment='$modeid'";	
}
}
	 
$sqll=mysqli_query($con,$SQLLL);
while($rrowsasmy=mysqli_fetch_array($sqll)){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), $rrr)
->setCellValue('B'.(5+$rrr), $rrowsasmy['receiptno'])
->setCellValue('C'.(5+$rrr), $rrowsasmy['adm_no'])
->setCellValue('D'.(5+$rrr), $rrowsasmy['studentname'])
->setCellValue('E'.(5+$rrr), $type.' '.$rrowsasmy['form'])
->setCellValue('F'.(5+$rrr),$rrowsasmy['stream'])
->setCellValue('G'.(5+$rrr), $rrowsasmy['academicy'])
->setCellValue('H'.(5+$rrr), $rrowsasmy['academicp'])
->setCellValue('I'.(5+$rrr), $rrowsasmy['amount_paid'])
->setCellValue('J'.(5+$rrr), $rrowsasmy['modes'])
->setCellValue('K'.(5+$rrr), $rrowsasmy['bankname'])
->setCellValue('L'.(5+$rrr), $rrowsasmy['bankslipno'])
->setCellValue('M'.(5+$rrr), $rrowsasmy['datedeposited'])
->setCellValue('N'.(5+$rrr), $rrowsasmy['paidby'])
->setCellValue('O'.(5+$rrr), $rrowsasmy['users'])
->setCellValue('P'.(5+$rrr), date("Y-m-d",strtotime($rrowsasmy['created_at'])));

	$rrr++;
}

$objPHPExcel->getActiveSheet()->getStyle('A1:P'.(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;
?>