<?php
session_start();
include("dbconnect.php");
$academicyear=@$_GET['academicyear'];
$periods=@$_GET['periods'];
$yearr=@$_GET['year'];
$class=@$_GET['class'];
$stream=@$_GET['stream'];
$naclas='';
if($class==''){
$dads=mysqli_query($conn,"select form from forms where id='$class'");
$rowss=mysqli_fetch_array($dads);
$naclas=$rowss['form'];
}
$steamname='';
if($stream!='' || $stream!='all'){
$dadsttt=mysqli_query($conn,"select stream from streams where id='$stream'");
$rowssrerer=mysqli_fetch_array($dadsttt);
$steamname=$rowssrerer['stream'];
}
$query="SELECT * FROM school_info";
$result=mysqli_query($conn,$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'];

$dadsttty=mysqli_query($conn,"select * from academicyears where id='$academicyear'");
$rowssrows=mysqli_fetch_array($dadsttty);
$acdemicyname=$rowssrows['academicyear'];
$dadstttyy=mysqli_query($conn,"select * from academicyearsperiods where id='$periods'");
$rowssrererrows=mysqli_fetch_array($dadstttyy);
$periodsname=$rowssrererrows['periods'];

$filename = $naclas."".$steamname."_Arrears_list.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");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $naclas.' Votehead Arrears List for the year '.$yearr);
$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:D3');
$objPHPExcel->getActiveSheet() ->getStyle('A1:A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A4', 'Votehead')
->setCellValue('B4', 'Expected Fee')
->setCellValue('C4', 'Paid Fee')
->setCellValue('D4', 'Fee Balance');
$border_style= array('borders' => array('right' => array('style' => 
PHPExcel_Style_Border::BORDER_THICK,'color' => array('argb' => '766f6e'),)));
$objPHPExcel->getActiveSheet();
$objPHPExcel->getActiveSheet()->getStyle("A1:D4")->applyFromArray($border_style);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getStyle('A1:D4')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#9999FF');
$i=0;
$str="SELECT * FROM  arrearsnow";
$result = mysqli_query($conn,$str);
$sum=0;$sum1=0;$sum2=0;$sum3=0;
While($row = mysqli_fetch_array($result)) {
	$tt=$row['votehead'];
	$tt1=$row['expected'];
	$tt2=$row['paid'];
	$tt3=$row['balance'];
	$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$i), $tt)
->setCellValue('B'.(5+$i), $tt1)
->setCellValue('C'.(5+$i), $tt2)
->setCellValue('D'.(5+$i), $tt3);
$i++;	
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.(5+$i),'Totals')
->setCellValue('B'.(5+$i), '=sum(B5:B'.(5+$i-1).')')
->setCellValue('C'.(5+$i), '=sum(C5:C'.(5+$i-1).')')
->setCellValue('D'.(5+$i), '=sum(D5:D'.(5+$i-1).')');
$objPHPExcel->getActiveSheet()->getStyle('A1:D'.(5+$i))->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '#000000'
)
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('B5:D'.(5+$i))
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

// 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="'.$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;