<?php
include("dbconnect.php");
$filename = "feeregistersummary.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['class'];
$streamsid=@$_GET['stream'];
$filters=@$_GET['cats'];
$ddfrom=@$_GET['from'];
$ddto=@$_GET['to'];
$groupby=@$_GET['groups'];
$aaccid=8;
$stateme='';
if($filters==1){
	$stateme=' Date From '.$ddfrom. ' To '.$ddto;
}
else{
	$stateme=' Receipts From '.$ddfrom. ' To '.$ddto;
}
$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'];
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $email)
->setCellValue('A4', 'Stream '.$streamname.' Class '.$type.' '.$formname.' Fee Register. '.$stateme);

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('8');
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->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', 'AMOUNT');
$rrrr=4;
$lastalpha='';
$select=mysqli_query($con,"SELECT * FROM  voteheads WHERE account_id='$aaccid'");
while($roows=mysqli_fetch_array($select)){
	
	$selectsas=mysqli_query($con,"SELECT * FROM  alphabets WHERE id='$rrrr'");
	$roows11=mysqli_fetch_array($selectsas);
	$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue($roows11['nam'].'5', $roows['votehead']);
	$objPHPExcel->getActiveSheet()->getColumnDimension($roows11['nam'])->setWidth('20');
	$lastalpha=$roows11['nam'];
$rrrr++;	
}
$selectsas=mysqli_query($con,"SELECT * FROM  alphabets WHERE id='$rrrr'");
	$roows11c=mysqli_fetch_array($selectsas);
	$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue($roows11c['nam'].'5', 'Prepayment');
	$objPHPExcel->getActiveSheet()->getColumnDimension($roows11c['nam'])->setWidth('20');
	$lastalpha=$roows11c['nam'];
$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$lastalpha.'1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:'.$lastalpha.'2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:'.$lastalpha.'3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:'.$lastalpha.'4');
$objPHPExcel->getActiveSheet() ->getStyle('A1:'.$lastalpha.'5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:'.$lastalpha.'5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#CCFFCC');
$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'";*/
$SQLLL="SELECT receiptno FROM `feepayments` WHERE status='1'";

if(@$_GET['cats']!=''){
$datetype=$_GET['cats'];
if($datetype=="1"){
	if(@$_GET['from']!='' && @$_GET['to']!=''){
	$ddfrom=@$_GET['from'];
	$ddto=@$_GET['to'];
	$SQLLL=$SQLLL." AND DATE(created_at)>='$ddfrom' AND DATE(created_at)<='$ddto'";
}
}
elseif($datetype=="2"){
if(@$_GET['from']!='' && @$_GET['to']!=''){
	$ddfrom=@$_GET['from'];
	$ddto=@$_GET['to'];
	$SQLLL=$SQLLL." AND receiptno>='$ddfrom' AND receiptno<='$ddto'";
}	
}
}
if(@$_GET['stream']!=""){
	$streammid=@$_GET['stream'];
if(@$_GET['stream']=="all"){
}
else{
$SQLLL=$SQLLL." AND stream_id='$streammid'";	
}
}
if(@$_GET['class']!=""){
	$formsid=@$_GET['class'];
if(@$_GET['class']=="all"){
}
else{
$SQLLL=$SQLLL." AND form_id='$formsid'";	
}
}

$rrtt=1;

$recnooos1 = (int) filter_var($ddfrom, FILTER_SANITIZE_NUMBER_INT);
$recnooos2 = (int) filter_var($ddto, FILTER_SANITIZE_NUMBER_INT);
//$sqll=mysqli_query($con,$SQLLL);
//while($rrowsasmy=mysqli_fetch_array($sqll)){
while($recnooos1<=$recnooos2){
	$toot=$recnooos1+$groupby-1;
	if($toot>$recnooos2){
	$toot=$recnooos2;	
	}
	$leadeing1='';
	if($recnooos1<10){
	$leadeing1='000';
	}
	elseif($recnooos1<100){
	$leadeing1='00';
	}
	elseif($recnooos1<1000){
	$leadeing1='0';
	}
	$recsumfrom='REC'.$leadeing1.$recnooos1;
	
	$leadeing='';
	if($toot<10){
	$leadeing='000';
	}
	elseif($toot<100){
	$leadeing='00';
	}
	elseif($toot<1000){
	$leadeing='0';
	}
	$recsumto='REC'.$leadeing.$toot;
	
	
	
$sqllIOOO=mysqli_query($con,"SELECT SUM(amount_paid) AS ammmt FROM `feepayments` WHERE receiptno>='$recsumfrom' AND receiptno<='$recsumto' AND receiptno IN (".$SQLLL.")");
$rrorrr=mysqli_fetch_array($sqllIOOO);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), $rrr)
->setCellValue('B'.(5+$rrr), $recsumfrom.' - '.$recsumto)
->setCellValue('C'.(5+$rrr), $rrorrr['ammmt']);
//$rec=$rrowsasmy['receiptno'];
$TTTTT=4;
$selectty=mysqli_query($con,"SELECT * FROM  voteheads WHERE account_id='$aaccid'");
while($roows5=mysqli_fetch_array($selectty)){
	$vidd=$roows5['id'];
	$selectsas=mysqli_query($con,"SELECT * FROM  alphabets WHERE id='$TTTTT'");
	$roows11=mysqli_fetch_array($selectsas);
	$values=0;
	$selectsasr=mysqli_query($con,"SELECT sum(amount) AS amtt FROM  studentsfee WHERE votehead='$vidd' AND transaction>='$recsumfrom' AND transaction<='$recsumto' AND transaction IN (".$SQLLL.")");
	if(mysqli_num_rows($selectsasr)>0){
		$roows11f=mysqli_fetch_array($selectsasr);
		$values=$roows11f['amtt'];
	}
	$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue($roows11['nam'].(5+$rrr), $values);
	//$lastalpha=$roows11['nam'];
$TTTTT++;	
}
$values=0;
$selectsasr=mysqli_query($con,"SELECT SUM(amount) AS toopay FROM  overpayments WHERE receipt_no>='$recsumfrom' AND receipt_no<='$recsumto' AND receipt_no IN (".$SQLLL.")");
if(mysqli_num_rows($selectsasr)>0){
$roows11f=mysqli_fetch_array($selectsasr);
$values=$roows11f['toopay'];
}
$selectsas=mysqli_query($con,"SELECT * FROM  alphabets WHERE id='$rrrr'");
$roows11c=mysqli_fetch_array($selectsas);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($roows11c['nam'].(5+$rrr), $values);

	$rrr++;
	$rrtt=0;
//}
	$recnooos1=$recnooos1+$groupby;
}
if($rrr>1){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), 'TOTALS')
->setCellValue('C'.(5+$rrr), "=SUM(C6:C".(4+$rrr).")");
$allo=4;
$selectty=mysqli_query($con,"SELECT * FROM  voteheads WHERE account_id='$aaccid'");
while($roows5=mysqli_fetch_array($selectty)){
	$vidd=$roows5['id'];
	$selectsas=mysqli_query($con,"SELECT * FROM  alphabets WHERE id='$allo'");
	$roows11=mysqli_fetch_array($selectsas);
	$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue($roows11['nam'].(5+$rrr), "=SUM(".$roows11['nam']."6:".$roows11['nam'].(4+$rrr).")");
	//$lastalpha=$roows11['nam'];
$allo++;	
}
$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue($lastalpha.(5+$rrr), "=SUM(".$lastalpha."6:".$lastalpha.(4+$rrr).")");
$objPHPExcel->getActiveSheet()->mergeCells('A'.(5+$rrr).':B'.(5+$rrr));
		$objPHPExcel->getActiveSheet()->getStyle('C6:'.$lastalpha.(5+$rrr))
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
}
		$rrr++;

$objPHPExcel->getActiveSheet()->getStyle('A'.(4+$rrr).':'.$lastalpha.(4+$rrr))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1:'.$lastalpha.(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;
?>