<?php
include("dbconnect.php");
$filename = "student_statement.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='';
$admno=@$_GET['admno'];
$sqlSTUDENT=mysqli_query($con,"SELECT * FROM `students` WHERE `adm_no`='$admno'");
$studetails=@mysqli_fetch_array($sqlSTUDENT);
$tittlesss=$admno.' :: '.$studetails['fname'].' '.$studetails['sname'].' '.$studetails['tname'].' Fee Statement';
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $email)
->setCellValue('A4', $tittlesss);
$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:H2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:H3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:H4');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth('18');
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth('18');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A5', 'Transaction')
->setCellValue('B5', 'Academic Year')
->setCellValue('C5', 'Academic Period')
->setCellValue('D5', 'CLASS')
->setCellValue('E5', 'STREAM')
->setCellValue('F5', 'AMOUNT')
->setCellValue('G5', 'BALANCE')
->setCellValue('H5', 'REFERENCE/RECEIPTS');
$objPHPExcel->getActiveSheet() ->getStyle('A1:H5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:H5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#CCFFCC');

$balance=0;
$rrr=1;
$balancecal=0;
$rrrs=0;
$ssqql=mysqli_query($con,"SELECT * FROM `academicyears` ORDER BY id ASC");
while($sd=mysqli_fetch_array($ssqql)){
$academy=$sd['id'];
$academicyear=$sd['academicyear'];

$sqlfeedue=mysqli_query($con,"SELECT *,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) AS form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) AS stream FROM feesdue WHERE 	adm_no='$admno' AND academicyear='$academy'");
while($key=mysqli_fetch_array($sqlfeedue)){
$formmmm=$key['form'];
$Streammm=$key['stream'];
$rr2=0;
$sqlllperiods=mysqli_query($con,"SELECT * FROM `academicyearsperiods` WHERE `academicyear`='$academy' ORDER BY `orders` ASC");
while($sssq=mysqli_fetch_array($sqlllperiods)){
$acperiod=$sssq['id'];
$periods=$sssq['periods'];
$rr2++;
$transa=1;
$dueamount=0;
$sqlfeedue=mysqli_query($con,"SELECT SUM(amount) AS amt FROM `studentsfee` WHERE `adm_no`='$admno' AND `academicyear`='$academy' AND `academicperiod`='$acperiod' AND `transaction_type`='$transa'");
$sdue=@mysqli_fetch_array($sqlfeedue);
$dueamount=$sdue['amt'];
if($rr2==1){
if($balancecal>0){
$dueamount=$dueamount-$balancecal;
}
}
$balance=$balance+$dueamount;
if($dueamount>0){
  $objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('A'.(5+$rrr), 'Term Fees')
	->setCellValue('B'.(5+$rrr), $academicyear)
	->setCellValue('C'.(5+$rrr), $periods)
	->setCellValue('D'.(5+$rrr), $type.' '.$formmmm)
	->setCellValue('E'.(5+$rrr), $Streammm)
	->setCellValue('F'.(5+$rrr), $dueamount )
	->setCellValue('G'.(5+$rrr),$balance)
	->setCellValue('H'.(5+$rrr),'');
	$rrr++;
}
$dadafeepay=mysqli_query($con,"SELECT * FROM `feepayments` WHERE `adm_no`='$admno' AND `years`='$academy' AND `periods`='$acperiod'");
while($sdfee=@mysqli_fetch_array($dadafeepay)){
$amountp=$sdfee['amount_paid'];
$modepp=$sdfee['modeofpayment'];
if($rrrs>0){
if($modepp!=4){
	$balance=$balance-$amountp;
$receiptno=$sdfee['receiptno'];
$amountdeducted=$amountp*(-1);
$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('A'.(5+$rrr), 'Fee Payment')
	->setCellValue('B'.(5+$rrr), $academicyear)
	->setCellValue('C'.(5+$rrr), $periods)
	->setCellValue('D'.(5+$rrr), $type.' '.$formmmm)
	->setCellValue('E'.(5+$rrr), $Streammm)
	->setCellValue('F'.(5+$rrr), $amountdeducted )
	->setCellValue('G'.(5+$rrr),$balance)
	->setCellValue('H'.(5+$rrr),$receiptno);
	$rrr++;
}
}
else{
$balance=$balance-$amountp;
$receiptno=$sdfee['receiptno'];
$amountdeducted=$amountp*(-1);
$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('A'.(5+$rrr), 'Fee Payment')
	->setCellValue('B'.(5+$rrr), $academicyear)
	->setCellValue('C'.(5+$rrr), $periods)
	->setCellValue('D'.(5+$rrr), $type.' '.$formmmm)
	->setCellValue('E'.(5+$rrr), $Streammm)
	->setCellValue('F'.(5+$rrr), $amountdeducted )
	->setCellValue('G'.(5+$rrr),$balance)
	->setCellValue('H'.(5+$rrr),$receiptno);
	$rrr++;
}
}

}
}
$balancecal=$balance;
$rrrs++;
}

$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$rrr), 'Balance')
->setCellValue('G'.(5+$rrr), $balance);
$objPHPExcel->getActiveSheet()->mergeCells('A'.(5+$rrr).':F'.(5+$rrr));
$objPHPExcel->getActiveSheet()->getStyle('F6:G'.(5+$rrr))
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$rrr++;
$objPHPExcel->getActiveSheet()->getStyle('A'.(4+$rrr).':H'.(4+$rrr))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1:H'.(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;
?>