<?php
include("dbconnect.php");
$filename = "class_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");
$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='';
$classid=@$_GET['form'];
$streamsid=@$_GET['stream'];
if($classid=='no' || $streamsid=='no'){
	if($classid!='no'){
	$ssasddll=mysqli_query($con,"SELECT * FROM forms WHERE id='$classid'");
	$rrooowsa=mysqli_fetch_array($ssasddll);
	$formname=$rrooowsa['form'];
	$tittlesss=$type.' '.$formname.' CLASS LIST BALANCES';
	}
	if($streamsid!='no'){
	$ssasddll=mysqli_query($con,"SELECT * FROM streams WHERE id='$streamsid'");
	$rrooowsa=mysqli_fetch_array($ssasddll);
	$streamname=$rrooowsa['stream'];
	$tittlesss=$streamname.' Stream All CLASS LIST BALANCES';
	}
}
else{

	$ssasddll=mysqli_query($con,"SELECT * FROM forms WHERE id='$classid'");
	$rrooowsa=mysqli_fetch_array($ssasddll);
	$formname=$rrooowsa['form'];
	$tittlesss=$type.' '.$formname.' CLASS LIST BALANCES';
	
	$ssasddlltt=mysqli_query($con,"SELECT * FROM streams WHERE id='$streamsid'");
	$rrooowsatt=mysqli_fetch_array($ssasddlltt);
	$streamname=$rrooowsatt['stream'];
	$tittlesss=$type.' '.$formname.' '.$streamname.' Stream CLASS LIST BALANCES';
	
}

$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $email)
->setCellValue('A4', $tittlesss);
$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:L2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:L3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:L4');
$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->setActiveSheetIndex(0)
->setCellValue('A5', 'S/N')
->setCellValue('B5', 'ADM NO')
->setCellValue('C5', 'NAME')
->setCellValue('D5', 'CLASS')
->setCellValue('E5', 'STREAM')
->setCellValue('F5', 'ACADEMIC YEAR')
->setCellValue('G5', 'PERIOD/TERM')
->setCellValue('H5', 'TERM FEE')
->setCellValue('I5', 'YEAR FEE')
->setCellValue('J5', 'PAID')
->setCellValue('K5', 'TERM BALANCE')
->setCellValue('L5', 'YEAR BALANCE');
$objPHPExcel->getActiveSheet() ->getStyle('A1:L5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:L5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#CCFFCC');
$sqlquery="";
if($classid=='no' || $streamsid=='no'){
	if($classid!='no'){
	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` WHERE form_id='$classid' order by adm_no ASC";
	}
	if($streamsid!='no'){
	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` WHERE stream_id='$streamsid' order by adm_no ASC";
	}
}
else{

	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` WHERE form_id='$classid' and stream_id='$streamsid' order by adm_no ASC";
	
}
$ssql=mysqli_query($con,$sqlquery); 
   $rrr=1; 
while($ssrowssql=mysqli_fetch_array($ssql)){
      $adm_no=$ssrowssql['adm_no'];
      $studentname=$ssrowssql['studentname'];
      $form=$ssrowssql['form'];
      $stream=$ssrowssql['stream'];
      $academicyear=$ssrowssql['academicy'];
      $academicperiod='';
      $acyear=$ssrowssql['academicyear'];
      $acperiod='';
		$qqq45=mysqli_query($con,"SELECT *,(SELECT periods FROM academicyearsperiods WHERE academicyearsperiods.id=`activeperiod`.academicperiod) AS periods FROM `activeperiod` WHERE academicyear='$acyear'");
		$val42=mysqli_fetch_array($qqq45);
		$academicperiod=$val42['periods'];
		$acperiod=$val42['academicperiod'];
		  
        $orderss=0;
        $qqqwwgyy=mysqli_query($con,"SELECT * FROM `academicyearsperiods` WHERE academicyear='$acyear' AND id='$acperiod'");
        $vals1yy=mysqli_fetch_array($qqqwwgyy);
          $orderss=$vals1yy['orders'];
      // studentsfee
      $fd=1;
      $fp=2;
      $feedue=0;
      $paid=0;
      $balance=0;
	  $feedueyear=0;
        $ssqlfee=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fd' AND adm_no='$adm_no' AND academicyear='$acyear' AND academicperiod IN (SELECT id FROM `academicyearsperiods` WHERE academicyear='$acyear' AND orders<='$orderss')"); 
		while($sqfee=mysqli_fetch_array($ssqlfee)){
		$feedue=$feedue+$sqfee['amount'];	
		}
		$ssqlfeeyear=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fd' AND adm_no='$adm_no' AND academicyear='$acyear'"); 
		while($sqfeeyear=mysqli_fetch_array($ssqlfeeyear)){
		$feedueyear=$feedueyear+$sqfeeyear['amount'];	
		}
        /*$ssqlfee5=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fp' AND adm_no='$adm_no' AND academicyear='$acyear' AND academicperiod IN (SELECT id FROM `academicyearsperiods` WHERE academicyear=? AND orders<=?)",[$fp,$adm_no,$acyear,$acyear,$orderss]);
		while($sqfee1=mysqli_fetch_array($ssqlfee5)){
		$paid=$paid+$sqfee1['amount'];	
		}
		*/
		$ssqlfee5=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fp' AND adm_no='$adm_no' AND academicyear='$acyear'");
		while($sqfee1=mysqli_fetch_array($ssqlfee5)){
		$paid=$paid+$sqfee1['amount'];	
		}
        $opaid=0;
        $ssqlfeeo=mysqli_query($con,"SELECT * FROM `overpayments` WHERE adm_no='$adm_no' AND academicyear='$acyear'"); 
        while($sqfeeo=mysqli_fetch_array($ssqlfeeo)){
          $opaid=$opaid+$sqfeeo['amount'];
        }
        if($feedue==null || $feedue==''){
          $feedue=0;
        }
		if($feedueyear==null || $feedueyear==''){
          $feedueyear=0;
        }
        if($paid==null || $paid==''){
          $paid=0;
        }
        if($opaid==null || $opaid==''){
          $opaid=0;
        }
        $paid1=$paid+$opaid;
        $tbalance=$feedue-$paid1;
		if($tbalance<0){
			$tbalance=0;
		}
		$ytbalance=$feedueyear-$paid1;
		
	  $objPHPExcel->setActiveSheetIndex(0)
		->setCellValue('A'.(5+$rrr), $rrr)
		->setCellValue('B'.(5+$rrr), $adm_no)
		->setCellValue('C'.(5+$rrr), $studentname)
		->setCellValue('D'.(5+$rrr), $type.' '.$form)
		->setCellValue('E'.(5+$rrr), $stream)
		->setCellValue('F'.(5+$rrr), $academicyear)
		->setCellValue('G'.(5+$rrr), $academicperiod)
		->setCellValue('H'.(5+$rrr), $feedue)
		->setCellValue('I'.(5+$rrr), $feedueyear)
		->setCellValue('J'.(5+$rrr), $paid1)
		->setCellValue('K'.(5+$rrr), $tbalance)
		->setCellValue('L'.(5+$rrr), $ytbalance);
		$rrr++;
    }
	
 $objPHPExcel->setActiveSheetIndex(0)
		->setCellValue('A'.(5+$rrr), 'Totals')
		->setCellValue('H'.(5+$rrr), "=SUM(H6:H".(4+$rrr).")")
		->setCellValue('I'.(5+$rrr), "=SUM(I6:I".(4+$rrr).")")
		->setCellValue('J'.(5+$rrr), "=SUM(J6:J".(4+$rrr).")")
		->setCellValue('K'.(5+$rrr), "=SUM(K6:K".(4+$rrr).")")
		->setCellValue('L'.(5+$rrr), "=SUM(L6:L".(4+$rrr).")");
		$objPHPExcel->getActiveSheet()->mergeCells('A'.(5+$rrr).':G'.(5+$rrr));
		$objPHPExcel->getActiveSheet()->getStyle('H6:L'.(5+$rrr))
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
		$rrr++;
$objPHPExcel->getActiveSheet()->getStyle('A'.(4+$rrr).':L'.(4+$rrr))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1:L'.(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;

?>