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/CreateXLS.php

701 lines
29 KiB
PHP
Raw Permalink Normal View History

init
2024-04-27 09:23:34 +02:00
<?php
set_time_limit(999999);
$year=$_GET['year'];
function sum($year,$account_id=""){
if($account_id)$accw=" and parent_id='".$account_id."'";
else $accw="";
$w=$GLOBALS['db']->query("select id,type,register_date,ecminvoiceout_id,currency_value from ecminvoiceouts where register_date like '".$year."%'".$accw." 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=$year."-".$d[1];
$ww=$GLOBALS['db']->query("select price,ecmvat_value,quantity,purchase_price,ecmproduct_id,ecminvoiceoutitem_id from ecminvoiceoutitems where ecminvoiceout_id='".$r['id']."' and deleted='0'");
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
if($r['type']!="correct"){
$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select product_active,product_category_id,carton_volume_meter as cvm,pieces_per_carton as ppc from ecmproducts where id='".$rr['ecmproduct_id']."'"));
$rr['c']=$rrr['product_category_id'];
$rr['ppc']=$rrr['ppc'];
$rr['cvm']=$rrr['cvm'];
$pprice=$rr['purchase_price'];
$total_netto=$currency_value*$rr['price']*$rr['quantity'];
$total_qty=$rr['quantity'];
$total_pur=$pprice*$rr['quantity'];
$sum['all']+=$total_netto;
$sum['all_qty']+=$total_qty;
$sum['all_pur']+=$total_pur;
if($rr['ppc']>0)$sum['all_cbm']+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$dat]['all']+=$total_netto;
$sum[$dat]['all_qty']+=$total_qty;
$sum[$dat]['all_pur']+=$total_pur;
if($rr['ppc']>0)$sum[$dat]['all_cbm']+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$dat]['category_'.$rr['c']]+=$total_netto;
$sum[$dat]['category_qty_'.$rr['c']]+=$total_qty;
$sum[$dat]['category_pur_'.$rr['c']]+=$total_pur;
if($rr['ppc']>0)$sum[$dat]['category_cbm_'.$rr['c']]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$dat]['product_'.$rr['ecmproduct_id']]+=$total_netto;
$sum[$dat]['product_qty_'.$rr['ecmproduct_id']]+=$total_qty;
$sum[$dat]['product_pur_'.$rr['ecmproduct_id']]+=$total_pur;
if($rr['ppc']>0)$sum[$dat]['product_cbm_'.$rr['ecmproduct_id']]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$year]['category_sum_'.$rr['c']]+=$total_netto;
$sum[$year]['category_qty_sum_'.$rr['c']]+=$total_qty;
$sum[$year]['category_pur_sum_'.$rr['c']]+=$total_pur;
if($rr['ppc']>0)$sum[$year]['category_cbm_sum_'.$rr['c']]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$year]['product_sum_'.$rr['ecmproduct_id']]+=$total_netto;
$sum[$year]['product_qty_sum_'.$rr['ecmproduct_id']]+=$total_qty;
$sum[$year]['product_pur_sum_'.$rr['ecmproduct_id']]+=$total_pur;
if($rr['ppc']>0)$sum[$year]['product_cbm_sum_'.$rr['ecmproduct_id']]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
}
else{
$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select product_active,product_category_id,carton_volume_meter as cvm,pieces_per_carton as ppc from ecmproducts where id='".$rr['ecmproduct_id']."'"));
//if($rrr['product_active']!=1)continue;
$rr['c']=$rrr['product_category_id'];
$rr['ppc']=$rrr['ppc'];
$rr['cvm']=$rrr['cvm'];
/*if($r['type']=="correct"){
$rrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select purchase_price from ecminvoiceoutitems where ecmproduct_id='".$rr['ecmproduct_id']."' and ecminvoiceout_id='".$r['ecminvoiceout_id']."' and deleted='0'"));
$pprice=$rrr['purchase_price'];
}
else*/
$rrrr=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select price,quantity,purchase_price from ecminvoiceoutitems where id='".$rr['ecminvoiceoutitem_id']."'"));
$pprice=$rrrr['purchase_price'];
$total_netto=$currency_value*$rr['price']*$rr['quantity']-$currency_value*$rrrr['price']*$rrrr['quantity'];
$total_qty=$rr['quantity']-$rrrr['quantity'];
$total_pur=$pprice*($rr['quantity']-$rrrr['quantity']);
$sum['all']+=$total_netto;
$sum['all_qty']+=$total_qty;
$sum['all_pur']+=$total_pur;
if($rr['ppc']>0)$sum['all_cbm']+=($rr['quantity']-$rrrr['quantity'])*($rr['cvm']/$rr['ppc']);
$sum[$dat]['all']+=$total_netto;
$sum[$dat]['all_qty']+=$total_qty;
$sum[$dat]['all_pur']+=$total_pur;
if($rr['ppc']>0)$sum[$dat]['all_cbm']+=($rr['quantity']-$rrrr['quantity'])*($rr['cvm']/$rr['ppc']);
$sum[$dat]['category_'.$rr['c']]+=$total_netto;
$sum[$dat]['category_qty_'.$rr['c']]+=$total_qty;
$sum[$dat]['category_pur_'.$rr['c']]+=$total_pur;
if($rr['ppc']>0)$sum[$dat]['category_cbm_'.$rr['c']]+=($rr['quantity']-$rrrr['quantity'])*($rr['cvm']/$rr['ppc']);
$sum[$dat]['product_'.$rr['ecmproduct_id']]+=$total_netto;
$sum[$dat]['product_qty_'.$rr['ecmproduct_id']]+=$total_qty;
$sum[$dat]['product_pur_'.$rr['ecmproduct_id']]+=$total_pur;
if($rr['ppc']>0)$sum[$dat]['product_cbm_'.$rr['ecmproduct_id']]+=($rr['quantity']-$rrrr['quantity'])*($rr['cvm']/$rr['ppc']);
$sum[$year]['category_sum_'.$rr['c']]+=$total_netto;
$sum[$year]['category_qty_sum_'.$rr['c']]+=$total_qty;
$sum[$year]['category_pur_sum_'.$rr['c']]+=$total_pur;
if($rr['ppc']>0)$sum[$year]['category_cbm_sum_'.$rr['c']]+=($rr['quantity']-$rrrr['quantity'])*($rr['cvm']/$rr['ppc']);
$sum[$year]['product_sum_'.$rr['ecmproduct_id']]+=$total_netto;
$sum[$year]['product_qty_sum_'.$rr['ecmproduct_id']]+=$total_qty;
$sum[$year]['product_pur_sum_'.$rr['ecmproduct_id']]+=$total_pur;
if($rr['ppc']>0)$sum[$year]['product_cbm_sum_'.$rr['ecmproduct_id']]+=($rr['quantity']-$rrrr['quantity'])*($rr['cvm']/$rr['ppc']);
}
}
}
return $sum;
}
if(!$year)$year=(int)date("Y");
if($year==date(Y)){
$mmm=(int)date("m")-1;
}
else $mmm=12;
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 PRICEBOOK");
$objPHPExcel->getProperties()->setSubject("Office 2007 PRICEBOOK");
$objPHPExcel->getProperties()->setDescription("PRICEBOOK");
$alf1="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
for($i=0;$i<strlen($alf1);$i++)$alf[$i]=$alf1[$i];
$alf[26]="AA";
$alf[27]="AB";
$alf[28]="AC";
$alf[29]="AD";
$alf[30]="AE";
$alf[31]="AF";
$alf[32]="AG";
$alf[33]="AH";
$alf[34]="AI";
$alf[35]="AJ";
$alf[36]="AK";
$alf[37]="AL";
$alf[38]="AM";
$alf[39]="AN";
$alf[40]="AO";
$alf[41]="AP";
$alf[42]="AQ";
$alf[43]="AR";
$alf[44]="AS";
$alf[45]="AT";
$alf[46]="AU";
$alf[47]="AV";
$alf[48]="AW";
$alf[49]="AX";
$alf[50]="AY";
$alf[51]="AZ";
$alf[52]="BA";
$alf[53]="BB";
$alf[54]="BC";
$alf[55]="BD";
$alf[56]="BE";
$alf[]="BF";
$alf[]="BG";
$alf[]="BH";
$alf[]="BI";
$alf[]="BJ";
$alf[]="BK";
$alf[]="BL";
$alf[]="BM";
$alf[]="BN";
$alf[]="BO";
$alf[]="BP";
$alf[]="BQ";
$alf[]="BR";
$alf[]="BS";
$alf[]="BT";
$alf[]="BU";
$alf[]="BV";
$alf[]="BW";
$alf[]="BX";
$alf[]="BY";
$alf[]="BZ";
$alf[]="CA";
$alf[]="CB";
$alf[]="CC";
$alf[]="CD";
$alf[]="CE";
$alf[]="CF";
$alf[]="CG";
$alf[]="CH";
$alf[]="CI";
$alf[]="CJ";
$alf[]="CK";
$alf[]="CL";
$alf[]="CM";
$alf[]="CN";
$alf[]="CO";
$alf[]="CP";
$alf[]="CQ";
$alf[]="CR";
$alf[]="CS";
$alf[]="CT";
$alf[]="CU";
$alf[]="CV";
$alf[]="CW";
$alf[]="CX";
$alf[]="CY";
$alf[]="CZ";
$alf[]="DA";
$alf[]="DB";
$alf[]="DC";
$alf[]="DD";
$alf[]="DE";
$alf[]="DF";
$alf[]="DG";
$alf[]="DH";
$alf[]="DI";
$alf[]="DJ";
$alf[]="DK";
$alf[]="DL";
$alf[]="DM";
$alf[]="DN";
$alf[]="DO";
$alf[]="DP";
$alf[]="DQ";
$alf[]="DR";
$alf[]="DS";
$alf[]="DT";
$alf[]="DU";
$alf[]="DV";
$alf[]="DW";
$alf[]="DX";
$alf[]="DY";
$alf[]="DZ";
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);
for($i=3;$i<=100;$i++){
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+1])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+2])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+3])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+4])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+5])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+6])->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i+7])->setWidth(20);
}
$objPHPExcel->getActiveSheet()->SetCellValue('A1','Name');
$objPHPExcel->getActiveSheet()->SetCellValue('B1','Inventory');
$objPHPExcel->getActiveSheet()->SetCellValue('C1','Inv value');
$objPHPExcel->getActiveSheet()->SetCellValue('D1','CBM');
$months=array("01","02","03","04","05","06","07","08","09","10","11","12");
$months_names=array("01"=>"January","02"=>"February","03"=>"March","04"=>"April","05"=>"May","06"=>"June","07"=>"July","08"=>"August","09"=>"September","10"=>"October","11"=>"November","12"=>"December");
for($i=0;$i<12;$i++){
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+4].'1',$months_names[$months[$i]]." ".$year);
}
$m=2;
for($i=0;$i<=12;$i++){
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+4].'2','Qty');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+5].'2','Value');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+6].'2','Margin');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+7].'2','CBM');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+8].'2','Avg sale');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+9].'2','Avg qty');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+10].'2','Pred. sale');
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+11].'2','Pred. qty');
}
$GLOBALS['db']->query("set names utf8");
/*function sum($year,$c,$all,$acc,$active=""){
if($acc)$ah="ecminvoiceouts.parent_name like '".$acc."' and ";
else $ah="";
if($all)$wh="";
else $wh=" and ecmproducts.product_category_id='".$c."'";
if(!$active)$aa="";
elseif($active=="inactive")$aa=" and ecmproducts.product_active='0'";
else $aa=" and ecmproducts.product_active='1'";
$ww=$GLOBALS['db']->query("select ecmproducts.id as ecmproduct_id,ecmproducts.carton_volume_meter as cvm,ecmproducts.pieces_per_carton as ppc,ecminvoiceouts.register_date as d,ecminvoiceoutitems.purchase_price as purchase_price,ecminvoiceoutitems.price as price,ecminvoiceoutitems.quantity as quantity,ecminvoiceoutitems.ecmproduct_id as ecmproduct_id from ecminvoiceoutitems inner join ecminvoiceouts on ecminvoiceouts.id=ecminvoiceoutitems.ecminvoiceout_id inner join ecmproducts on ecmproducts.id=ecminvoiceoutitems.ecmproduct_id where ".$ah."ecminvoiceouts.register_date like '".$year."%' and ecminvoiceoutitems.deleted='0'".$wh." and ecminvoiceouts.type!='correct'".$aa);
echo mysql_error();
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
$d=explode("-",$rr['d']);
$dat=$year."-".$d[1];
$sum['all']+=$rr['price']*$rr['quantity'];
$_SESSION['EcmReports_sum_all']+=$rr['price']*$rr['quantity'];
$sum['all_qty']+=$rr['quantity'];
$_SESSION['EcmReports_sum_all_qty']+=$rr['quantity'];
$sum['all_pur']+=$rr['purchase_price']*$rr['quantity'];
$_SESSION['EcmReports_sum_all_pur']+=$rr['purchase_price']*$rr['quantity'];
if($rr['ppc']>0){
$sum['all_cbm']+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$_SESSION['EcmReports_sum_all_cbm']+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
}
$sum[$dat]['all']+=$rr['price']*$rr['quantity'];
$sum[$dat]['all_qty']+=$rr['quantity'];
$sum[$dat]['all_pur']+=$rr['purchase_price']*$rr['quantity'];
if($rr['ppc']>0)$sum[$dat]['all_cbm']+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
if(!$all){
$sum[$dat]['category_'.$c]+=$rr['price']*$rr['quantity'];
$sum[$dat]['category_qty_'.$c]+=$rr['quantity'];
$sum[$dat]['category_pur_'.$c]+=$rr['purchase_price']*$rr['quantity'];
if($rr['ppc']>0)$sum[$dat]['category_cbm_'.$c]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$dat]['product_'.$rr['ecmproduct_id']]+=$rr['price']*$rr['quantity'];
$sum[$dat]['product_qty_'.$rr['ecmproduct_id']]+=$rr['quantity'];
$sum[$dat]['product_pur_'.$rr['ecmproduct_id']]+=$rr['purchase_price']*$rr['quantity'];
if($rr['ppc']>0)$sum[$dat]['product_cbm_'.$rr['ecmproduct_id']]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$year]['category_sum_'.$c]+=$rr['price']*$rr['quantity'];
$sum[$year]['category_qty_sum_'.$c]+=$rr['quantity'];
$sum[$year]['category_pur_sum_'.$c]+=$rr['purchase_price']*$rr['quantity'];
if($rr['ppc']>0)$sum[$year]['category_cbm_sum_'.$c]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
$sum[$year]['product_sum_'.$rr['ecmproduct_id']]+=$rr['price']*$rr['quantity'];
$sum[$year]['product_qty_sum_'.$rr['ecmproduct_id']]+=$rr['quantity'];
$sum[$year]['product_pur_sum_'.$rr['ecmproduct_id']]+=$rr['purchase_price']*$rr['quantity'];
if($rr['ppc']>0)$sum[$year]['product_cbm_sum_'.$rr['ecmproduct_id']]+=$rr['quantity']*($rr['cvm']/$rr['ppc']);
}
}
$ww=$GLOBALS['db']->query("select ecminvoiceouts.id as invid,ecminvoiceouts.ecminvoiceout_id as ecminvoiceout_id,ecmproducts.id as ecmproduct_id,ecmproducts.carton_volume_meter as cvm,ecmproducts.pieces_per_carton as ppc,ecminvoiceouts.register_date as d,ecminvoiceoutitems.purchase_price as purchase_price,ecminvoiceoutitems.price as price,ecminvoiceoutitems.quantity as quantity,ecminvoiceoutitems.ecmproduct_id as ecmproduct_id from ecminvoiceoutitems inner join ecminvoiceouts on ecminvoiceouts.id=ecminvoiceoutitems.ecminvoiceout_id inner join ecmproducts on ecmproducts.id=ecminvoiceoutitems.ecmproduct_id where ".$ah."ecminvoiceouts.register_date like '".$year."%' and ecminvoiceoutitems.deleted='0'".$wh." and ecminvoiceouts.type='correct'".$aa);
echo mysql_error();
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
$www=$GLOBALS['db']->query("select id from ecminvoiceoutitems where ecminvoiceout_id='".$rr['ecminvoiceout_id']."' and ecmproduct_id='".$rr['ecmproduct_id']."' and deleted='0' and (price='".$rr['price']."' and quantity='".$rr['quantity']."')");
if(mysql_num_rows($www)>0)continue;
$www=$GLOBALS['db']->query("select quantity,price,purchase_price from ecminvoiceoutitems where ecminvoiceout_id='".$rr['ecminvoiceout_id']."' and ecmproduct_id='".$rr['ecmproduct_id']."' and deleted='0' and (price!='".$rr['price']."' or quantity!='".$rr['quantity']."')");
$rrr=$GLOBALS['db']->fetchByAssoc($www);
$cqty=$rr['quantity']-$rrr['quantity'];
$cprice=$rr['quantity']*$rr['price']-$rrr['quantity']*$rrr['price'];
$cpprice=$cqty*$rr['purchase_price'];
if($cqty==0 && $rr['price']==$rrr['price'])continue;
$d=explode("-",$rr['d']);
$dat=$year."-".$d[1];
$sum['all']+=$cprice;
$_SESSION['EcmReports_sum_all']+=$cprice;
$sum['all_qty']+=$cqty;
$_SESSION['EcmReports_sum_all_qty']+=$cqty;
$sum['all_pur']+=$cpprice;
$_SESSION['EcmReports_sum_all_pur']+=$cpprice;
if($rr['ppc']>0){
$sum['all_cbm']+=$cqty*($rr['cvm']/$rr['ppc']);
$_SESSION['EcmReports_sum_all_cbm']+=$cqty*($rr['cvm']/$rr['ppc']);
}
$sum[$dat]['all']+=$cprice;
$sum[$dat]['all_qty']+=$cqty;
$sum[$dat]['all_pur']+=$cpprice;
if($rr['ppc']>0)$sum[$dat]['all_cbm']+=$cqty*($rr['cvm']/$rr['ppc']);
if(!$all){
$sum[$dat]['category_'.$c]+=$cprice;
$sum[$dat]['category_qty_'.$c]+=$cqty;
$sum[$dat]['category_pur_'.$c]+=$cpprice;
if($rr['ppc']>0)$sum[$dat]['category_cbm_'.$c]+=$cqty*($rr['cvm']/$rr['ppc']);
$sum[$dat]['product_'.$rr['ecmproduct_id']]+=$cprice;
$sum[$dat]['product_qty_'.$rr['ecmproduct_id']]+=$cqty;
$sum[$dat]['product_pur_'.$rr['ecmproduct_id']]+=$cpprice;
if($rr['ppc']>0)$sum[$dat]['product_cbm_'.$rr['ecmproduct_id']]+=$cqty*($rr['cvm']/$rr['ppc']);
$sum[$year]['category_sum_'.$c]+=$cprice;
$sum[$year]['category_qty_sum_'.$c]+=$cqty;
$sum[$year]['category_pur_sum_'.$c]+=$cpprice;
if($rr['ppc']>0)$sum[$year]['category_cbm_sum_'.$c]+=$cqty*($rr['cvm']/$rr['ppc']);
$sum[$year]['product_sum_'.$rr['ecmproduct_id']]+=$cprice;
$sum[$year]['product_qty_sum_'.$rr['ecmproduct_id']]+=$cqty;
$sum[$year]['product_pur_sum_'.$rr['ecmproduct_id']]+=$cpprice;
if($rr['ppc']>0)$sum[$year]['product_cbm_sum_'.$rr['ecmproduct_id']]+=$cqty*($rr['cvm']/$rr['ppc']);
}
}
return $sum;
}*/
if($_GET['category'])$cw=" and id='".$_GET['category']."'";
else $cw="";
$z="select name,id from ecmproductcategories where deleted='0'".$cw." order by name";
$w=$GLOBALS['db']->query($z);
if($_REQUEST['account'])$sum=sum($year,$_REQUEST['account']);
else $sum=unserialize(base64_decode(file_get_contents("modules/EcmReports/cache".$year.$dddd.".dat")));
while($r=$GLOBALS['db']->fetchByAssoc($w)){
$m++;
$ems_qty_in_stock=0;
$stock_value=0;
$cbm=0;
$cat_arr[]=$m;
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$m,$r['name']);
$zz="select ems_qty_in_stock,ems_price,carton_volume_meter as cvm,pieces_per_carton as ppc from ecmproducts where product_category_id='".$r['id']."' and deleted='0'";
$ww=$GLOBALS['db']->query($zz);
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
$ems_qty_in_stock+=$rr['ems_qty_in_stock'];
$stock_value+=$rr['ems_qty_in_stock']*$rr['ems_price'];
$cbm+=$rr['ems_qty_in_stock']*($rr['cvm']/$rr['ppc']);
}
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$m,$ems_qty_in_stock);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$m,$stock_value);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$m,$cbm);
$ems_total+=$ems_qty_in_stock;
$stock_value_total+=$stock_value;
$cbm_total+=$cbm;
$avg_cat=0;
$avg_cat_qty=0;
for($i=0;$i<count($months)*8;$i+=8){
$avg_cat+=$sum[$year."-".$months[($i/8)]]['category_'.$r['id']];
$avg_cat_qty+=$sum[$year."-".$months[($i/8)]]['category_qty_'.$r['id']];
}
for($i=0;$i<count($months)*8;$i+=8){
$cat=$sum[$year."-".$months[($i/8)]]['category_'.$r['id']];
$cat_pur=$sum[$year."-".$months[($i/8)]]['category_pur_'.$r['id']];
$cat_qty=$sum[$year."-".$months[($i/8)]]['category_qty_'.$r['id']];
$cbm=$sum[$year."-".$months[($i/8)]]['category_cbm_'.$r['id']];
if($cat)$margin=100*($cat-$cat_pur)/$cat;
else $margin=0;
if(!$cat)$cat=0;
if(!$cat_qty)$cat_qty=0;
if(!$cbm)$cbm=0;
$rvs=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select value from ecmsalesreports_predictions_cat where account_id='".$_REQUEST['account']."' and year='".$year."' and month='".$i."' and bean_id='".$r['id']."' and type='sale'"));
$rvq=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select value from ecmsalesreports_predictions_cat where account_id='".$_REQUEST['account']."' and year='".$year."' and month='".$i."' and bean_id='".$r['id']."' and type='qty'"));
if($rvs['value']<=0)$rvs['value']=$avg_cat/$mmm;
if($rvq['value']<=0)$rvq['value']=$avg_cat_qty/$mmm;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+4].$m,$cat_qty);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+5].$m,$cat);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+6].$m,$margin);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+7].$m,$cbm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+8].$m,$avg_cat/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+9].$m,$avg_cat_qty/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+10].$m,$rvs['value']);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+11].$m,$rvq['value']);
}
if($sum[$year]['category_sum_'.$r['id']])$margin=100*($sum[$year]['category_sum_'.$r['id']]-$sum[$year]['category_pur_sum_'.$r['id']])/$sum[$year]['category_sum_'.$r['id']];
else $margin=0;
$cbm=$sum[$year]['category_cbm_sum_'.$r['id']];
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+4].$m,($sum[$year]['category_qty_sum_'.$r['id']]/$mmm));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+5].$m,$sum[$year]['category_sum_'.$r['id']]);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+6].$m,$margin);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+7].$m,$cbm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+8].$m,$avg_cat/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+9].$m,$avg_cat_qty/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+10].$m,$avg_cat/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+11].$m,$avg_cat_qty/$mmm);
if(!$_GET['only_cat']){
$zz="select id,name,code,ems_qty_in_stock,ems_price,carton_volume_meter as cvm,pieces_per_carton as ppc from ecmproducts where product_category_id='".$r['id']."' and deleted='0' order by code asc";
$ww=$GLOBALS['db']->query($zz);
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
$m++;
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$m,$rr['code'].' '.$rr['name']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$m,$rr['ems_qty_in_stock']);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$m,($rr['ems_qty_in_stock']*$rr['ems_price']));
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$m,($rr['ems_qty_in_stock']*($rr['cvm']/$rr['ppc'])));
for($i=0;$i<count($months)*8;$i+=8){
$avg_p_qty+=$sum[$year."-".$months[($i/8)]]['product_qty_'.$rr['id']];
$avg_p+=$sum[$year."-".$months[($i/8)]]['product_'.$rr['id']];
}
for($i=0;$i<count($months)*8;$i+=8){
$p_pur=$sum[$year."-".$months[($i/8)]]['product_pur_'.$rr['id']];
$p_qty=$sum[$year."-".$months[($i/8)]]['product_qty_'.$rr['id']];
$p=$sum[$year."-".$months[($i/8)]]['product_'.$rr['id']];
$cbm=$sum[$year."-".$months[($i/8)]]['product_cbm_'.$rr['id']];
if($p)$margin=100*($p-$p_pur)/$p;
else $margin=0;
if(!$p)$p=0;
if(!$cbm)$cbm=0;
if(!$p_qty)$p_qty=0;
$rvs=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select value from ecmsalesreports_predictions_cat where account_id='".$_REQUEST['account']."' and year='".$_REQUEST['year']."' and month='".$i."' and bean_id='".$rr['id']."' and type='sale'"));
$rvq=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select value from ecmsalesreports_predictions_cat where account_id='".$_REQUEST['account']."' and year='".$_REQUEST['year']."' and month='".$i."' and bean_id='".$rr['id']."' and type='qty'"));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+4].$m,$p_qty);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+5].$m,$p);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+6].$m,$margin);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+7].$m,$cbm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+8].$m,$avg_p/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+9].$m,$avg_p_qty/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+10].$m,$rvs['value']);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+11].$m,$rvq['value']);
}
$s_pur=$sum[$year]['product_pur_sum_'.$rr['id']];
$s_qty=$sum[$year]['product_qty_sum_'.$rr['id']];
$s=$sum[$year]['product_sum_'.$rr['id']];
$cbm=$sum[$year]['product_cbm_'.$rr['id']];
if($s)$margin=100*($s-$s_pur)/$s;
else $margin=0;
if(!$cbm)$cbm=0;
if(!$s)$s=0;
if(!$s_qty)$s_qty=0;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+4].$m,($s_qty/$mmm));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+5].$m,$s);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+6].$m,$margin);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+7].$m,$cbm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+8].$m,$avg_p/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+9].$m,$avg_p_qty/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+10].$m,$avg_p/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+11].$m,$avg_p_qty/$mmm);
}
}
}
$m++;
if($_REQUEST['account'])$sum=sum($year,$_REQUEST['account']);
else $sum=unserialize(base64_decode(file_get_contents("modules/EcmReports/cache".$year.$dddd.".dat")));
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$m,$ems_total);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$m,$stock_total);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$m,$cbm_total);
$avg_cat=0;
$avg_cat_qty=0;
function sumCat($year,$month,$account,$type){
$w=$GLOBALS['db']->query("select id from ecmproductcategories where deleted='0'");
while($r=$GLOBALS['db']->fetchByAssoc($w)){
$arr[]="bean_id='".$r['id']."'";
}
$r=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select sum(value) as sum from ecmsalesreports_predictions_cat where deleted='0' and type='".$type."' and account_id='".$account."' and month='".$month."' and year='".$year."' and (".implode(" or ",$arr).")"));
return $r['sum'];
}
for($i=0;$i<count($months)*8;$i+=8){
$avg_cat+=$sum[$year."-".$months[($i/8)]]['all'];
$avg_cat_qty+=$sum[$year."-".$months[($i/8)]]['all_qty'];
}
for($i=0;$i<count($months)*8;$i+=8){
$cat=$sum[$year."-".$months[($i/8)]]['all'];
$cat_pur=$sum[$year."-".$months[($i/8)]]['all_pur'];
$cat_qty=$sum[$year."-".$months[($i/8)]]['all_qty'];
$cbm=$sum[$year."-".$months[($i/8)]]['all_cbm'];
if($cat)$margin=100*($cat-$cat_pur)/$cat;
else $margin=0;
if(!$cbm)$cbm=0;
if(!$cat)$cat=0;
if(!$cat_qty)$cat_qty=0;
$sum_pred_cat+=$pred_cat=sumCat($year,$i,$_REQUEST['account'],"sale");
$sum_pred_cat_qty+=$pred_cat_qty=sumCat($year,$i,$_REQUEST['account'],"qty");
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+4].$m,$cat_qty);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+5].$m,$cat);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+6].$m,$margin);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+7].$m,$cbm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+8].$m,$avg_cat/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+9].$m,$avg_cat_qty/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+10].$m,$pred_cat);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+11].$m,$pred_cat_qty);
}
$m++;
$sa=$sum['all'];
$sa_pur=$sum['all_pur'];
$sa_qty=$sum['all_qty'];
$cbm=$sum['all_cbm'];
if($sa)$margin=100*($sa-$sa_pur)/$sa;
else $margin=0;
if(!$cbm)$cbm=0;
if(!$sa)$sa=0;
if(!$sa_qty)$sa_qty=0;
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+4].($m-1),($sa_qty/$mmm));
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+5].($m-1),$sa);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+6].($m-1),$margin);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+7].($m-1),$cbm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+8].($m-1),$sa/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+9].($m-1),$sum['all_qty']/$mmm);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+10].($m-1),$sum_pred_cat);
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i+11].($m-1),$sum_pred_cat_qty);
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);
if(!$_GET['only_cat']){
foreach($cat_arr as $ca){
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('argb' => 'FF00FF00')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
),
"A".$ca.":DDS".$ca
);
}
}
$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".($m-1).":DD".($m-1)
);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('argb' => 'BBDDFF')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
),
"CW1:DD".($m-1)
);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('argb' => '75BAFF')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
),
"CW".($m-1).":DD".($m-1)
);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('argb' => 'FFCCFFCC')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
),
"A1:DD1"
);
foreach($cat_arr as $ca){
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('argb' => '75BAFF')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
),
"CW".$ca.":DD".$ca
);
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
chmod("cache/upload",0777);
$microtime=str_replace(".","",str_replace(" ","",microtime()));
$name="cache/upload/Report".$microtime.".xlsx";
$objWriter->save($name);
chmod($name,0777);
header("Location: ".$name);
?>
Reference in New Issue Copy Permalink