mz/crm.e5.pl
1
0
Fork 0
Code Issues 12 Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
crm.e5.pl/modules/EcmPaymentStates/CreateXLSsummary.php
Michał Zieliński 11e713ca6f init
2024-04-27 09:23:34 +02:00

202 lines
7.8 KiB
PHP
Raw Permalink Blame History

<?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);
?>
Reference in New Issue View Git Blame Copy Permalink