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/EcmReports/CreateXLSrph.php

274 lines
11 KiB
PHP
Raw Permalink Normal View History

init
2024-04-27 09:23:34 +02:00
<?php
$date_from=$_GET['date_from'];
$date_to=$_GET['date_to'];
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 RPH");
$objPHPExcel->getProperties()->setSubject("Office 2007 RPH");
$objPHPExcel->getProperties()->setDescription("RPH");
$alf1="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
for($i=0;$i<strlen($alf1);$i++)$alf[$i]=$alf1[$i];
$alf[]="AA";
$alf[]="AB";
$alf[]="AC";
$alf[]="AD";
$alf[]="AE";
$alf[]="AF";
$alf[]="AG";
$alf[]="AH";
$alf[]="AI";
$alf[]="AJ";
$alf[]="AK";
$alf[]="AL";
$alf[]="AM";
$alf[]="AN";
$alf[]="AO";
$alf[]="AP";
$alf[]="AQ";
$alf[]="AR";
$alf[]="AS";
$alf[]="AT";
$alf[]="AU";
$alf[]="AV";
$alf[]="AW";
$alf[]="AX";
$alf[]="AY";
$alf[]="AZ";
$alf[]="BA";
$alf[]="BB";
$alf[]="BC";
$alf[]="BD";
$alf[]="BE";
$alf[]="BF";
$alf[]="BG";
$alf[]="BH";
$alf[]="BI";
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AY')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('AZ')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('BA')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('BB')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('BC')->setWidth(30);
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue("A1","Lp.");
$objPHPExcel->getActiveSheet()->SetCellValue("B1","Name");
$objPHPExcel->getActiveSheet()->SetCellValue("C1","January");
$objPHPExcel->getActiveSheet()->SetCellValue("G1","February");
$objPHPExcel->getActiveSheet()->SetCellValue("K1","March");
$objPHPExcel->getActiveSheet()->SetCellValue("O1","April");
$objPHPExcel->getActiveSheet()->SetCellValue("S1","May");
$objPHPExcel->getActiveSheet()->SetCellValue("W1","June");
$objPHPExcel->getActiveSheet()->SetCellValue("AA1","July");
$objPHPExcel->getActiveSheet()->SetCellValue("AE1","August");
$objPHPExcel->getActiveSheet()->SetCellValue("AI1","September");
$objPHPExcel->getActiveSheet()->SetCellValue("AM1","October");
$objPHPExcel->getActiveSheet()->SetCellValue("AQ1","November");
$objPHPExcel->getActiveSheet()->SetCellValue("AU1","Dezember");
$objPHPExcel->getActiveSheet()->SetCellValue("AY1",$_REQUEST['year']." total");
$objPHPExcel->getActiveSheet()->SetCellValue("AZ1",$_REQUEST['year']." avg");
$objPHPExcel->getActiveSheet()->SetCellValue("BA1",($_REQUEST['year']-1)." total");
$objPHPExcel->getActiveSheet()->SetCellValue("BB1",($_REQUEST['year']-1)." avg");
$objPHPExcel->getActiveSheet()->SetCellValue("BC1","User");
$i=2;
$wh[]="deleted='0'";
if($_REQUEST['user_id'])$wh[]="assigned_user_id='".$_REQUEST['user_id']."'";
if($_REQUEST['account_id']){
$wh[]="parent_id='".$_REQUEST['account_id']."'";
$wh[]="parent_id is not null";
}
if(count($wh)>0)$where=" and ".implode(" and ",$wh);
else $where="";
$z="select * from accounts where 1=1".$where." order by name asc";
$w=mysql_query($z);
function getSumWz($year){
$w=$GLOBALS['db']->query("select id,type,register_date,currency_value,wz_id,parent_id,ecminvoiceout_id from ecminvoiceouts where register_date like '".$year."%' and deleted='0'");
while($r=$GLOBALS['db']->fetchByAssoc($w)){
if(!$r['currency_value'])
$currency_value=1;
else $currency_value=$r['currency_value'];
$d=explode("-",$r['register_date']);
$dat=$d[0]."-".$d[1];
if($r['type']!="correct"){
$rr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select sum(price*quantity) as s from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'"));
$total_netto=$currency_value*$rr['s'];
}
else{
$ww=$GLOBALS['db']->query("select price,quantity,ecminvoiceoutitem_id from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'");
$total_netto=0;
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
$rrrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select price,quantity from ecminvoiceoutitems where id='".$rr['ecminvoiceoutitem_id']."'"));
$total_netto+=$currency_value*$rr['price']*$rr['quantity']-$currency_value*$rrrr['price']*$rrrr['quantity'];
}
}
if($r['wz_id']){
$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select parent_id from ecmstockdocouts where id='".$r['wz_id']."'"));
$parent_id=$rrr['parent_id'];
}
else {
if($r['type']!="correct")$parent_id=$r['parent_id'];
else{
$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select wz_id from ecminvoiceouts where id='".$r['ecminvoiceout_id']."'"));
$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select parent_id from ecmstockdocouts where id='".$rrr['wz_id']."'"));
$parent_id=$rrr['parent_id'];
}
$parent_id=$r['parent_id'];
}
$sum[$dat][$parent_id]['tn']+=$total_netto;
}
return $sum;
}
function getMinSec($v){
$min=floor($v/60);
$sec=$v-$min*60;
if($min>0 || $sec>0)$str=$min."m ".$sec."s";
return $str;
}
$swz1=getSumWz($_REQUEST['year']);
$swz1m=getSumWz($_REQUEST['year']-1);
if($_REQUEST['account_id'] && $_REQUEST['user_id']){
$time=$_SESSION['EcmSalesReport_time'];
$ll=4;
}
else $ll=4;
while($r=mysql_fetch_array($w)){
$objPHPExcel->getActiveSheet()->SetCellValue("A".$i,($i-1));
$objPHPExcel->getActiveSheet()->SetCellValue("B".$i,$r['name']);
for($k=1;$k<=12;$k++){
if($k<10)$n="0".$k;
else $n=$k;
$swz=$swz1[$_REQUEST['year']."-".$n][$r['id']];
$total_netto=$swz['tn'];
$rval=mysql_fetch_array(mysql_query("select value from ecmsalesreports_predictions where account_id='".$r['id']."' and year='".$_REQUEST['year']."' and month='".$n."' and deleted='0'"));
$value=$rval['value'];
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+2].$i,$total_netto);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+3].$i,$value);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+4].$i,getMinSec($time[$r['id']][$_REQUEST['year']."-".$n]['duration']));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+5].$i,$time[$r['id']][$_REQUEST['year']."-".$n]['l']);
$sum_total_netto[$_REQUEST['year']."-".$n]+=$total_netto;
$sum_total_netto[$_REQUEST['year']]+=$total_netto;
$account_total[$r['id']]+=$total_netto;
}
for($k=1;$k<=12;$k++){
if($k<10)$n="0".$k;
else $n=$k;
$swzm=$swz1m[($_REQUEST['year']-1)."-".$n][$r['id']];
$total_netto_m=$swzm['tn'];
$sum_total_netto_m[($_REQUEST['year']-1)."-".$n]+=$total_netto_m;
$sum_total_netto_m[($_REQUEST['year']-1)]+=$total_netto_m;
$account_total_m[$r['id']]+=$total_netto_m;
}
$objPHPExcel->getActiveSheet()->SetCellValue("AY".$i,$account_total[$r['id']]);
$objPHPExcel->getActiveSheet()->SetCellValue("AZ".$i,($account_total[$r['id']]/((int)date("m"))));
$objPHPExcel->getActiveSheet()->SetCellValue("BA".$i,$account_total_m[$r['id']]);
$objPHPExcel->getActiveSheet()->SetCellValue("BB".$i,($account_total_m[$r['id']]/12));
$ruser=mysql_fetch_array(mysql_query("select * from users where id='".$r['assigned_user_id']."'"));
$objPHPExcel->getActiveSheet()->SetCellValue("BC".$i,$ruser['first_name'].' '.$ruser['last_name']);
$i++;
}
for($k=1;$k<=12;$k++){
if($k<10)$n="0".$k;
else $n=$k;
$allvalue=0;
$wacc=mysql_query("select * from accounts where 1=1".$where." order by name asc");
while($racc=mysql_fetch_array($wacc)){
$wval=mysql_query("select value from ecmsalesreports_predictions where account_id='".$racc['id']."' and year='".$_REQUEST['year']."' and month='".$n."' and deleted='0'");
while($rval=mysql_fetch_array($wval)){
$allvalue+=$rval['value'];
}
}
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+2].$i,($sum_total_netto[$_REQUEST['year'].'-'.$n]));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+3].$i,$allvalue);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+4].$i,getMinSec($time[$_REQUEST['year']."-".$n]['duration']));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$ll*($k-1)+5].$i,$time[$_REQUEST['year']."-".$n]['l']);
}
$objPHPExcel->getActiveSheet()->SetCellValue("AY".$i,$sum_total_netto[$_REQUEST['year']]);
$objPHPExcel->getActiveSheet()->SetCellValue("AZ".$i,($sum_total_netto[$_REQUEST['year']]/((int)date("m"))));
$objPHPExcel->getActiveSheet()->SetCellValue("BA".$i,$sum_total_netto_m[$_REQUEST['year']-1]);
$objPHPExcel->getActiveSheet()->SetCellValue("BB".$i,($sum_total_netto_m[$_REQUEST['year']-1]/12));
$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:BA1"
);
$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).":BA".($i)
);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
chmod("cache/upload",0777);
$microtime=str_replace(".","",str_replace(" ","",microtime()));
$name="cache/upload/RPHReport".$microtime.".xlsx";
$objWriter->save($name);
chmod($name,0777);
header("Location: ".$name);
?>
Reference in New Issue Copy Permalink