<?php
include("dbconnect.php");	
session_start();

$filters=@$_GET['cats'];
$datefrom=@$_GET['from'];
$dateto=@$_GET['to'];
$account=@$_GET['account'];
$academicyear=@$_GET['academicyear'];
$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'];
$dat=date('d-m-Y');
//$data6='Receipt Cash Book Between '.$frm.' and '.$to.'';
$preinted='Printed Date :'.strtoupper(date("Y-m-d h:i:sa", time()));
$filename ='ledgers.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("cleophas Developer school finance")
->setLastModifiedBy("cleophas-Developer")
->setTitle("Office 2007 XLSX Document")
->setSubject("Office 2007 XLSX Document")
->setDescription("Document for Office 2007 XLSX, generated by school finance.")
->setKeywords("ledgers List Excel 2007")
->setCategory("ledgers List");
$rrrssss=0;
$resultRRR = mysqli_query($con,"select * from voteheads WHERE account_id='$account' AND academicyear='$academicyear' ORDER by priority");
While($rowRRR = mysqli_fetch_array($resultRRR)) {
$iid=$rowRRR['id'];
$sheetname=$rowRRR['votehead'];

$objPHPExcel->createSheet($rrrssss);
$objPHPExcel->setActiveSheetIndex($rrrssss)
->setCellValue('A1', $school_name)
->setCellValue('A2', $address)
->setCellValue('A3', $email)
->setCellValue('A4', $rowRRR['votehead']." A/C");
$objPHPExcel->setActiveSheetIndex($rrrssss)
->setCellValue('A5', 'Date')
->setCellValue('B5', 'Particulars')
->setCellValue('C5', 'DR')
->setCellValue('D5', '')
->setCellValue('E5', 'CR')
->setCellValue('F5', '');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:F3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:F4');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:F5');
$objPHPExcel->getActiveSheet() ->getStyle('A1:F5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:F5')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:F5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#B2DDD');
$objPHPExcel->getActiveSheet()
->setCellValue('A6', '')
->setCellValue('B6', 'Bal B/F')
->setCellValue('C6', 0)
->setCellValue('D6', '')
->setCellValue('E6', 0)
->setCellValue('F6', '');
$nnow=$datefrom;
$noww=$dateto;
$rr=1;
while($nnow<=$noww){
$resultRRRs = mysqli_query($con,"select SUM(amount) AS totalss FROM studentsfee WHERE `votehead`='$iid' and `academicyear`='$academicyear' and transaction_type='2' AND transaction IN (SELECT receiptno FROM feepayments WHERE datepaid='$nnow')");
$rowRRRs = @mysqli_fetch_array($resultRRRs);
$credit=$rowRRRs['totalss'];
$resultRRRsess = mysqli_query($con,"select SUM(amount) as amt from ccustomerslists where `votehead`='$iid' and trans_id IN (select id from customerstrans where transtype='6' and `academicyear`='$academicyear' and datepaid='$nnow')");
$rowRRRss = @mysqli_fetch_array($resultRRRsess);
$credita=$rowRRRss['amt'];
$creditf=$credit+$credita;

$resultRRRsessp = mysqli_query($con,"select SUM(amount) as amt from ssupplierslists where `votehead`='$iid' and vourchorno IN (SELECT vourcherno FROM suppliersinvoice WHERE trans_id='4' AND `academicyear`='$academicyear' and datepaid='$nnow')");
$rowRRRssp = @mysqli_fetch_array($resultRRRsessp);
$creditap=$rowRRRssp['amt'];

$creditfp=$creditap;

if($creditfp>0 || $creditf>0){
$objPHPExcel->getActiveSheet()
->setCellValue('A'.($rr+6), $nnow)
->setCellValue('B'.($rr+6), 'Cash Book')
->setCellValue('C'.($rr+6), $creditfp)
->setCellValue('D'.($rr+6), '=C'.($rr+6).'+D'.($rr+5))
->setCellValue('E'.($rr+6), $creditf)
->setCellValue('F'.($rr+6), '=E'.($rr+6).'+F'.($rr+5));
$rr++;
}
$date2=strtotime($nnow);
		$day=date("d",$date2);$paymonaa=date("m",$date2);$yyyearrr=date("Y",$date2);
		if($paymonaa=="01"||$paymonaa=="03"||$paymonaa=="05" || $paymonaa=="07" || $paymonaa=="08" || $paymonaa=="10" || $paymonaa=="12"){
		if($day==31){
			if($paymonaa=="12"){
			$dayy="01";$paymonaa1="01";$yyyearrr1=$yyyearrr+1;
			$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;
			}
			else{
			$dayy="01";$paymonaa1=$paymonaa+1;
			if($paymonaa1>9){$paymonaa1=$paymonaa1;}else{$paymonaa1='0'.$paymonaa1;}
			$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;
			}
		}
		else{
			$dayy=$day+1;
			if($dayy>9){$dayy=$dayy;}else{$dayy='0'.$dayy;}
			$paymonaa1=$paymonaa;$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;	
		}
		}
		elseif($paymonaa=="02"){
			if($yyyearrr%4==0){
				if($day==29){
				$dayy="01";	$paymonaa1=$paymonaa+1;
				if($paymonaa1>9){$paymonaa1=$paymonaa1;}else{$paymonaa1='0'.$paymonaa1;}
				$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;	
				}
				else{
				$dayy=$day+1;
				if($dayy>9){$dayy=$dayy;}else{$dayy='0'.$dayy;}
				$paymonaa1=$paymonaa;$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;
				}
			}
			else{
				if($day==28){
				$dayy="01";$paymonaa1=$paymonaa+1;
				if($paymonaa1>9){$paymonaa1=$paymonaa1;}else{$paymonaa1='0'.$paymonaa1;}
				$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;	
				}
				else{
				$dayy=$day+1;
				if($dayy>9){$dayy=$dayy;}else{$dayy='0'.$dayy;}
				$paymonaa1=$paymonaa;$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;
				}
			}
		}
		else{
			if($day==30){
			$dayy="01";	$paymonaa1=$paymonaa+1;
			if($paymonaa1>9){$paymonaa1=$paymonaa1;}else{$paymonaa1='0'.$paymonaa1;}
			$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;
		}
		else{
			$dayy=$day+1;
			if($dayy>9){$dayy=$dayy;}else{$dayy='0'.$dayy;}
			$paymonaa1=$paymonaa;$yyyearrr1=$yyyearrr;$nnow=$yyyearrr1.'-'.$paymonaa1.'-'.$dayy;	
		}
		}
//$rr++;		
}

$objPHPExcel->getActiveSheet()
->setCellValue('A'.($rr+6), '')
->setCellValue('B'.($rr+6), 'Bal C/F')
->setCellValue('C'.($rr+6), '')
->setCellValue('D'.($rr+6), '=IF((SUM(C6:C'.($rr+5).')-SUM(E6:E'.($rr+5).'))>0,SUM(C6:C'.($rr+5).')-SUM(E6:E'.($rr+5).'),"")')
->setCellValue('E'.($rr+6), '')
->setCellValue('F'.($rr+6), '=IF((SUM(E6:E'.($rr+5).')-SUM(C6:C'.($rr+5).'))>0,SUM(E6:E'.($rr+5).')-SUM(C6:C'.($rr+5).'),"")');

$objPHPExcel->getActiveSheet()->getStyle('A'.($rr+6).':F'.($rr+6))->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.(6+$rr).':F'.(6+$rr))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#CFFFCC');
$objPHPExcel->getActiveSheet()->getStyle('D6:D'.(6+$rr))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle('F6:F'.(6+$rr))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle('C6:F'.($rr+6))
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->getStyle('A1:F'.($rr+6))->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '#000000'
)
)
)
);
$rrrssss++;
$objPHPExcel->getActiveSheet()->setTitle("ledger-".$rrrssss);
}
// 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;
?>