<?php
ini_set('memory_limit', '-1');
set_time_limit (999);
include_once("modules/EcmPaymentStates/helper.php");
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('display_errors', '1');

set_include_path('include/PHPExcel/');

include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("E5 CRM");
$objPHPExcel->getProperties()->setLastModifiedBy("E5 CRM");
$objPHPExcel->getProperties()->setTitle("Office 2007 PAYMENTS");
$objPHPExcel->getProperties()->setSubject("Office 2007 PAYMENTS");
$objPHPExcel->getProperties()->setDescription("PAYMENTS");

$alf="ABCDEFGHIJKLMNOPQRSTUVWXYZ";


$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);


$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->SetCellValue("A1","Kontrahent");
$objPHPExcel->getActiveSheet()->SetCellValue("B1","Bilans");
$objPHPExcel->getActiveSheet()->SetCellValue("C1","Podsumowanie");
$objPHPExcel->getActiveSheet()->SetCellValue("D1","Nie przeterminowane");
$objPHPExcel->getActiveSheet()->SetCellValue("E1","1-30");
$objPHPExcel->getActiveSheet()->SetCellValue("F1","30-60");
$objPHPExcel->getActiveSheet()->SetCellValue("G1","60-90");
$objPHPExcel->getActiveSheet()->SetCellValue("H1","90-180");
$objPHPExcel->getActiveSheet()->SetCellValue("I1","180-...");

$i=2;
$b=2;

$w=$GLOBALS['db']->query("select distinct account_id from ecmpayments_ecminvoiceouts where account_id!='' and account_id is not null");
while($r=$GLOBALS['db']->fetchByAssoc($w))$acc[$r['account_id']]="id='".$r['account_id']."'"; 

$z="select * from accounts where deleted='0' and (".implode(" or ",$acc).") order by name asc";
$w=$GLOBALS['db']->query($z);
$tmp = array();
$objPHPExcel->createSheet(1);
$objPHPExcel->setActiveSheetIndex(1); 
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->setTitle('Faktury');
$objPHPExcel->setActiveSheetIndex(0); 
while($r=$GLOBALS['db']->fetchByAssoc($w)){
$ac=0;
$l_summary=getSummary($r['id']);
	$l_ballance=getBallance($r['id']);
	
	$l_actual_sum=getActualSum($r['id']);
	$l_sum_1_30=getSum($r['id'],1,30);
	$l_sum_30_60=getSum($r['id'],30,60);
	$l_sum_60_90=getSum($r['id'],60,90);
	$l_sum_90_180=getSum($r['id'],90,180);
	$l_sum_180=getSum($r['id'],180);
	if(round($l_summary+$l_actual_sum+$l_sum_1_30+$l_sum_30_60+$l_sum_60_90+$l_sum_90_180+$l_sum_180,2)==0.00)continue; 

	$gz=$GLOBALS['db']->query("SELECT document_no,total,subtotal,register_date,currency_id FROM crm.ecminvoiceouts WHERE parent_id='".$r['id']."' AND paid=0");
	while($em=$GLOBALS['db']->fetchByAssoc($gz)){
		$cu=$GLOBALS['db']->query("SELECT name FROM crm.currencies WHERE id='".$em['currency_id']."'");
		$cur=$GLOBALS['db']->fetchByAssoc($cu);
		$objPHPExcel->setActiveSheetIndex(1); 
		if($ac==0){
		$g=$b-1;$objPHPExcel->getActiveSheet()->SetCellValue("A".$g,$r['name']);
		$objPHPExcel->getActiveSheet()->SetCellValue("A".$b,'Nr faktury');
		$objPHPExcel->getActiveSheet()->SetCellValue("B".$b,'Data wystawienia');
		$objPHPExcel->getActiveSheet()->SetCellValue("C".$b,'Kwota netto');
		$objPHPExcel->getActiveSheet()->SetCellValue("D".$b,'Kwota brutto');
		$objPHPExcel->getActiveSheet()->SetCellValue("E".$b,'Waluta');
		$b++;
		}
		
		$objPHPExcel->getActiveSheet()->SetCellValue("A".$b,$em['document_no']);
		$objPHPExcel->getActiveSheet()->SetCellValue("B".$b,$em['register_date']);
		$objPHPExcel->getActiveSheet()->SetCellValue("C".$b,number_format($em['subtotal'],2,",","."));
		$objPHPExcel->getActiveSheet()->SetCellValue("D".$b,number_format($em['total'],2,",","."));
		if($cur['name']==''){
		$objPHPExcel->getActiveSheet()->SetCellValue("E".$b,$em['currency_id']);
		} else {
		$objPHPExcel->getActiveSheet()->SetCellValue("E".$b,$cur['name']);
		}
		$b++;
		$ac++;
	// echo 'lol';
		$objPHPExcel->setActiveSheetIndex(0); 
	}$b+=2;
	
	$summary+=$l_summary;
	$ballance+=$l_ballance;
	$actual_sum+=$l_actual_sum;
	$sum_1_30+=$l_sum_1_30;
	$sum_30_60+=$l_sum_30_60;
	$sum_60_90+=$l_sum_60_90;
	$sum_90_180+=$l_sum_90_180;
	$sum_180+=$l_sum_180;
	
	$objPHPExcel->getActiveSheet()->SetCellValue("A".$i,$r['name']);
	$objPHPExcel->getActiveSheet()->SetCellValue("B".$i,number_format($l_ballance,2,",","."));
	$objPHPExcel->getActiveSheet()->SetCellValue("C".$i,number_format($l_summary,2,",","."));
	$objPHPExcel->getActiveSheet()->SetCellValue("D".$i,$l_actual_sum);
	$objPHPExcel->getActiveSheet()->SetCellValue("E".$i,$l_sum_1_30);
	$objPHPExcel->getActiveSheet()->SetCellValue("F".$i,$l_sum_30_60);
	$objPHPExcel->getActiveSheet()->SetCellValue("G".$i,$l_sum_60_90);
	$objPHPExcel->getActiveSheet()->SetCellValue("H".$i,$l_sum_90_180);
	$objPHPExcel->getActiveSheet()->SetCellValue("I".$i,$l_sum_180);
	
	$i++;
}
$b=$i;

$objPHPExcel->getActiveSheet()->SetCellValue("B".$i,$ballance);
$objPHPExcel->getActiveSheet()->SetCellValue("C".$i,$summary);
$objPHPExcel->getActiveSheet()->SetCellValue("D".$i,$actual_sum);
$objPHPExcel->getActiveSheet()->SetCellValue("E".$i,$sum_1_30);
$objPHPExcel->getActiveSheet()->SetCellValue("F".$i,$sum_30_60);
$objPHPExcel->getActiveSheet()->SetCellValue("G".$i,$sum_60_90);
$objPHPExcel->getActiveSheet()->SetCellValue("H".$i,$sum_90_180);
$objPHPExcel->getActiveSheet()->SetCellValue("I".$i,$sum_180);

$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'F0F0F0')
		),
		'borders' => array(	
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		)
	),
	"A1:I1"
);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
	array(
		'fill' => array(
			'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
			'color'	=> array('argb' => 'F0F0F0')
		),
		'borders' => array(	
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
		)
	),
	"A".($i).":I".($i)
);
$b+=2;
/*
foreach ($tmp as $v1) {
	
	$a=0;
	$b++;
    foreach ($v1 as $v2) {
		//echo $v2['name'];
		if($a==0){
		$g=$b-1;$objPHPExcel->getActiveSheet()->SetCellValue("A".$g,$v2['cp']);
		$objPHPExcel->getActiveSheet()->SetCellValue("A".$b,'Nr faktury');
		$objPHPExcel->getActiveSheet()->SetCellValue("B".$b,'Data wystawienia');
		$objPHPExcel->getActiveSheet()->SetCellValue("C".$b,'Kwota netto');
		$objPHPExcel->getActiveSheet()->SetCellValue("D".$b,'Kwota brutto');
		$b++;
		}
		$objPHPExcel->getActiveSheet()->SetCellValue("A".$b,$v2['document_no']);
		$objPHPExcel->getActiveSheet()->SetCellValue("B".$b,$v2['register_date']);
		$objPHPExcel->getActiveSheet()->SetCellValue("C".$b,$v2['subtotal']);

		$b++;
		
		$a++;
    }
	$b+=2;
}
*/
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
chmod("cache/upload",0777);
$microtime=str_replace(".","",str_replace(" ","",microtime()));
$name="cache/upload/PaymentsSummary".$microtime.".xlsx";
$objWriter->save($name);
chmod($name,0777);

header("Location: ".$name);
?>