<?php
include 'config.php';

// Check if the user is logged in

if ((!isSet($_SESSION['loginname'])) || ($loggin <> '1'))
{
header("Location: login.php");
exit;
}
require("configure.php"); 


@set_time_limit(0);
//ini_set('max_execution_time', '999'); 
ini_set('post_max_size', '10M');
ini_set('memory_limit','1024M');		
	
require('Classes/PHPExcel.php');	
 
$objPHPexcel = PHPExcel_IOFactory::load('Classes/template/Report.xls');	

$objWorksheet = $objPHPexcel->getActiveSheet();

$year = (int)$_GET["year"];
$date_form = htmlspecialchars($_POST["year"],ENT_QUOTES)."-01-01";
$date_to = htmlspecialchars($_POST["year"],ENT_QUOTES)."-12-31";
$index = $_POST['index'];
$companyid = $_POST['companyid'];
$sql = "SELECT * FROM file_company WHERE companyid=:companyid";
$sth = Db::getDbh()->prepare($sql);
$sth->execute(array(":companyid" => $companyid));
if( $error = $sth->getError(array(":companyid" => $companyid)) ){
	var_dump($error);
}
$row = $sth->fetch(PDO::FETCH_ASSOC);

$excel_row=1;
$objWorksheet->setCellValueByColumnAndRow(0, $excel_row, $row{'title'});
$excel_row=5;

$sql = "SELECT * FROM report_content WHERE companyid=:companyid AND date>=:date_form AND date<=:date_to AND status = '1'";
$sth = Db::getDbh()->prepare($sql);
$sth->execute(array(":companyid" => $companyid, ":date_form" => $date_form, ":date_to" => $date_to));
if( $error = $sth->getError(array(":companyid" => $companyid, ":date_form" => $date_form, ":date_to" => $date_to)) ){
	var_dump($error);
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)){
	$field_a = $row{'field_a'};
	$total_a[] = $field_a;
	$field_b = $row{'field_b'};
	$total_b[] = $field_b;
	$field_c = $row{'field_c'};
	$total_c[] = $field_c;
	$field_d = $row{'field_d'};
	$total_d[] = $field_d;
	$field_e = $row{'field_a'} + $row{'field_c'};
	$total_e[] = $field_e;
	$field_f = $row{'field_b'} + $row{'field_d'};
	$total_f[] = $field_f;
	$field_g = round(($field_e / $field_f * 100), 2);
	$objWorksheet->insertNewRowBefore($excel_row+1, 1);
	$objWorksheet->setCellValueByColumnAndRow(0, $excel_row, date("Y-m-d",strtotime($row{'date'})));
	$objWorksheet->setCellValueByColumnAndRow(1, $excel_row, $field_a);
	$objWorksheet->setCellValueByColumnAndRow(2, $excel_row, $field_b);
	$objWorksheet->setCellValueByColumnAndRow(3, $excel_row, $field_c);
	$objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $field_d);
	$objWorksheet->setCellValueByColumnAndRow(5, $excel_row, $field_e);
	$objWorksheet->setCellValueByColumnAndRow(6, $excel_row, $field_f);
	$objWorksheet->setCellValueByColumnAndRow(7, $excel_row, $field_g);
	$excel_row++;
}

if(is_array($total_a)){
	$display_a = array_sum($total_a);
}	else	{
	$display_a = 0;
}
if(is_array($total_b)){
	$display_b = array_sum($total_b);
}	else	{
	$display_b = 0;
}
if(is_array($total_c)){
	$display_c = array_sum($total_c);
}	else	{
	$display_c = 0;
}
if(is_array($total_d)){
	$display_d = array_sum($total_d);
}	else	{
	$display_d = 0;
}
if(is_array($total_e)){
	$display_e = array_sum($total_e);
}	else	{
	$display_e = 0;
}
if(is_array($total_f)){
	$display_f = array_sum($total_f);
}	else	{
	$display_f = 0;
}
if(is_array($total_e) && is_array($total_f)){
	$display_g = round((array_sum($total_e) / array_sum($total_f) * 100), 2);
}	else	{
	$display_g = 0;
}


$objWorksheet->insertNewRowBefore($excel_row+1, 1);
$objWorksheet->setCellValueByColumnAndRow(0, $excel_row, "TOTAL");
$objWorksheet->setCellValueByColumnAndRow(1, $excel_row, $display_a);
$objWorksheet->setCellValueByColumnAndRow(2, $excel_row, $display_b);
$objWorksheet->setCellValueByColumnAndRow(3, $excel_row, $display_c);
$objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $display_d);
$objWorksheet->setCellValueByColumnAndRow(5, $excel_row, $display_e);
$objWorksheet->setCellValueByColumnAndRow(6, $excel_row, $display_f);
$objWorksheet->setCellValueByColumnAndRow(7, $excel_row, $display_g);

$objWorksheet->getStyle("A".$excel_row.":H".$excel_row)->getFont()->setBold(true);
$objWorksheet->getStyle("A5:H".$excel_row)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Report_'.$date_form.'~'.$date_to.'.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
 
flush();

?>