424 lines
17 KiB
PHP
424 lines
17 KiB
PHP
|
<?php
|
||
|
|
include_once("modules/EcmReports/cache.php");
|
||
|
|
include_once("modules/EcmProductReports/vtigerConnector.php");
|
||
|
|
|
||
|
|
$vc=new vtigerConnector();
|
||
|
|
//$wc[]="purchaseorder.tracking_no!=''";
|
||
|
|
$vc->pp=99999;
|
||
|
|
$vc->where="(purchaseorder.postatus='Created' or purchaseorder.postatus='Approved' or purchaseorder.postatus='Delivered')";
|
||
|
|
|
||
|
|
$vc->getData();
|
||
|
|
$cnt=$vc->getCount();
|
||
|
|
$data=$vc->data;
|
||
|
|
|
||
|
|
$vd=$data;
|
||
|
|
function getValueByCode($data,$code,$status=""){
|
||
|
|
$r=mysql_fetch_array(mysql_query("select conversion_rate from currencies where name like 'USD'"));
|
||
|
|
for($i=0;$i<count($data);$i++){
|
||
|
|
if($data[$i]['productcode']==$code || $data[$i]['productcode']==$code."_S"){
|
||
|
|
if($status){
|
||
|
|
if($status==$data[$i]['postatus'])$value+=$r['conversion_rate']*$data[$i]['quantity']*$data[$i]['listprice']/0.8;
|
||
|
|
}
|
||
|
|
else $value+=$r['conversion_rate']*$data[$i]['quantity']*$data[$i]['listprice']/0.8;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return $value;
|
||
|
|
}
|
||
|
|
function getSumLast($account_id,$product_active){
|
||
|
|
$wh[]="e.register_date>'".date("Y-m-d",mktime()-12*30*24*3600)."'";
|
||
|
|
$wh[]="e.deleted='0'";
|
||
|
|
$wh[]="i.deleted='0'";
|
||
|
|
$wh[]="p.deleted='0'";
|
||
|
|
$wh[]="e.status='accepted'";
|
||
|
|
if($account_id)$wh[]="(e.parent_id='".$account_id."' or w.parent_id='".$account_id."')";
|
||
|
|
if($product_active && $product_active=="active")$wh[]="p.product_active='1'";
|
||
|
|
elseif($product_active && $product_active=="inactive")$wh[]="p.product_active='0'";
|
||
|
|
elseif($product_active && $product_active=="inactive")$wh=" and p.product_active='0'";
|
||
|
|
|
||
|
|
$w=mysql_query("select i.purchase_price,i.quantity,i.ecmproduct_id,p.product_category_id from ecminvoiceoutitems as i inner join ecminvoiceouts as e on e.id=i.ecminvoiceout_id inner join ecmproducts as p on i.ecmproduct_id=p.id where ".implode(" and ",$wh));
|
||
|
|
while($r=mysql_fetch_array($w)){
|
||
|
|
$arr[$r['product_category_id']]+=$r['purchase_price']*$r['quantity'];
|
||
|
|
$arr[$r['ecmproduct_id']]+=$r['purchase_price']*$r['quantity'];
|
||
|
|
}
|
||
|
|
return $arr;
|
||
|
|
}
|
||
|
|
set_time_limit(999999);
|
||
|
|
$year=$_GET['year'];
|
||
|
|
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[]=$alf1[$i];
|
||
|
|
}
|
||
|
|
for($i=0;$i<strlen($alf1);$i++){
|
||
|
|
for($j=0;$j<strlen($alf1);$j++){
|
||
|
|
$alf[]=$alf1[$i].$alf1[$j];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);
|
||
|
|
|
||
|
|
for($i=1;$i<=160;$i++){
|
||
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i])->setWidth(30);
|
||
|
|
}
|
||
|
|
//ilosc poczatkowych kolumn
|
||
|
|
$pc=11;
|
||
|
|
|
||
|
|
//kolumny glowne
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('A1','Name');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('B1','Inv qty');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('C1','Inv value');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('D1','Order value');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('E1','Order created value');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('F1','Order approved value');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('G1','Order delivered value');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('H1','Pur 12m');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('I1','Stock m');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('J1','Stock m + ord');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue('K1','CBM');
|
||
|
|
|
||
|
|
//miesiace
|
||
|
|
$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");
|
||
|
|
|
||
|
|
//drukowanie kolumn miesiecy
|
||
|
|
for($i=0;$i<12;$i++){
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc].'1',$months_names[$months[$i]]." ".$year);
|
||
|
|
}
|
||
|
|
//drukowanie kolumn z wartosciami dla miesiecy
|
||
|
|
$m=2;
|
||
|
|
for($i=0;$i<=12;$i++){
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc].'2','Qty');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+1].'2','Avg qty');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+2].'2','Sales');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+3].'2','Avg sales');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+4].'2','Purchase');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+5].'2','Avg purchase');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+6].'2','Margin %');
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+7].'2','CBM');
|
||
|
|
//$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+8].'2','Pred. sales');
|
||
|
|
//$objPHPExcel->getActiveSheet()->SetCellValue($alf[8*$i+$pc+9].'2','Pred. qty');
|
||
|
|
}
|
||
|
|
$m++;
|
||
|
|
|
||
|
|
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);
|
||
|
|
|
||
|
|
$GLOBALS['db']->query("set names utf8");
|
||
|
|
|
||
|
|
$as=sum($year,$_REQUEST['account'],"","",$_REQUEST['active']);
|
||
|
|
$last=getSumLast($_REQUEST['account'],$_REQUEST['active']);
|
||
|
|
|
||
|
|
while($r=$GLOBALS['db']->fetchByAssoc($w)){
|
||
|
|
|
||
|
|
//nr kolumny
|
||
|
|
$col=0;
|
||
|
|
|
||
|
|
//zerowanie wartosci
|
||
|
|
$ems_qty_in_stock=0;
|
||
|
|
$stock_value=0;
|
||
|
|
$order_value=0;
|
||
|
|
$order_created_value=0;
|
||
|
|
$order_approved_value=0;
|
||
|
|
$order_delivered_value=0;
|
||
|
|
$cbm=0;
|
||
|
|
|
||
|
|
//wartosci do kategorii
|
||
|
|
$act="";
|
||
|
|
if($_REQUEST['active']=="active")$act=" and product_active='1'";
|
||
|
|
elseif($_REQUEST['active']=="active")$act=" and product_active='0'";
|
||
|
|
$zz="select ems_qty_in_stock,ems_price,carton_volume_meter as cvm,pieces_per_carton as ppc,code from ecmproducts where product_category_id='".$r['id']."' and deleted='0'".$act;
|
||
|
|
$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']);
|
||
|
|
$order_value+=getValueByCode($vd,$rr['code']);
|
||
|
|
$order_created_value+=getValueByCode($vd,$rr['code'],"Created");
|
||
|
|
$order_approved_value+=getValueByCode($vd,$rr['code'],"Approved");
|
||
|
|
$order_delivered_value+=getValueByCode($vd,$rr['code'],"Delivered");
|
||
|
|
}
|
||
|
|
|
||
|
|
//sumowanie
|
||
|
|
$cbm_total+=$cbm;
|
||
|
|
$ems_qty_in_stock_total+=$ems_qty_in_stock;
|
||
|
|
$stock_value_total+=$stock_value;
|
||
|
|
$order_value_total+=$order_value;
|
||
|
|
$order_created_value_total+=$order_created_value;
|
||
|
|
$order_approved_value_total+=$order_approved_value;
|
||
|
|
$order_delivered_value_total+=$order_delivered_value;
|
||
|
|
|
||
|
|
//wartosci sprzedazy i zakupu w grupach
|
||
|
|
$sumy=getSum($as,$year,"",$r['id']);
|
||
|
|
|
||
|
|
//sumowanie
|
||
|
|
$tot_pur+=$sumy['total_purchase'];
|
||
|
|
$stock_m=0;
|
||
|
|
$stock_m_order=0;
|
||
|
|
if($sumy['total_purchase'])$stock_m=@$stock_value/($last[$r['id']]/12);
|
||
|
|
if($sumy['total_purchase'])$stock_m_order=@($stock_value+$order_value)/($last[$r['id']]/12);
|
||
|
|
$stock_m_total+=$stock_m;
|
||
|
|
$stock_m_order_total+=$stock_m_order;
|
||
|
|
$pur12m+=$last[$r['id']];
|
||
|
|
|
||
|
|
//tworzenie kolumn
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$r['name']);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$ems_qty_in_stock);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$stock_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$order_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$order_created_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$order_approved_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$order_delivered_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$last[$r['id']]/12);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$stock_m);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$stock_m_order);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cbm);
|
||
|
|
|
||
|
|
$avg_cat=0;
|
||
|
|
$avg_cat_qty=0;
|
||
|
|
$avg_cat_pur=0;
|
||
|
|
$sumy=getSum($as,$year,"",$r['id']);
|
||
|
|
|
||
|
|
$avg_cat+=$sumy["total"];
|
||
|
|
$summary_avg_cat+=$sumy['total'];
|
||
|
|
$avg_cat_pur+=$sumy["total_purchase"];
|
||
|
|
$summary_avg_cat_pur+=$sumy['total_purchase'];
|
||
|
|
$avg_cat_qty+=$sumy["qty"];
|
||
|
|
$summary_avg_cat_qty+=$sumy['qty'];
|
||
|
|
|
||
|
|
//tworzenie kolumn w miesiacach
|
||
|
|
for($i=0;$i<count($months);$i++){
|
||
|
|
$sum=getSum($as,$year."-".$months[$i],"",$r['id']);
|
||
|
|
$cat=$sum["total"];
|
||
|
|
$cat_pur=$sum["total_purchase"];
|
||
|
|
$cat_qty=$sum["qty"];
|
||
|
|
$cbm=$sum["cbm"];
|
||
|
|
|
||
|
|
if($cat)$margin=100*($cat-$cat_pur)/$cat;
|
||
|
|
else $margin=0;
|
||
|
|
|
||
|
|
if(!$cat)$cat=number_format(0,2,"",".");
|
||
|
|
|
||
|
|
$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;
|
||
|
|
|
||
|
|
$ajax_cat=ajaxWindow($r['id'],$i,$rvs['value'],$_REQUEST['account'],"sale",$year);
|
||
|
|
@$ajax_cat_qty=ajaxWindow($r['id'],$i,$rvq['value'],$_REQUEST['account'],"qty",$year,($avg_cat/$avg_cat_qty));
|
||
|
|
|
||
|
|
$summary['total'][$year."-".$months[$i]]+=$cat;
|
||
|
|
$summary['total_purchase'][$year."-".$months[$i]]+=$cat_pur;
|
||
|
|
$summary['total_qty'][$year."-".$months[$i]]+=$cat_qty;
|
||
|
|
$summary['total_cbm'][$year."-".$months[$i]]+=$cbm;
|
||
|
|
|
||
|
|
$summary['total'][$year]+=$cat;
|
||
|
|
$summary['total_purchase'][$year]+=$cat_pur;
|
||
|
|
$summary['total_qty'][$year]+=$cat_qty;
|
||
|
|
$summary['total_cbm'][$year]+=$cbm;
|
||
|
|
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cat_qty);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat_qty/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cat);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cat_pur);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat_pur/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$margin);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cbm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rvs['value']);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rvq['value']);
|
||
|
|
}
|
||
|
|
|
||
|
|
//sumowanie wiersza
|
||
|
|
if($sumy["total_purchase"])$margin=100*($sumy["total"]-$sumy["total_purchase"])/$sumy["total"];
|
||
|
|
else $margin=0;
|
||
|
|
$cbm=$sumy["cbm"];
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,($sumy["qty"]/$mmm));
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat_qty/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$sumy["total"]);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$sumy["total_purchase"]);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat_pur/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$margin);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cbm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_cat_qty/$mmm);
|
||
|
|
|
||
|
|
$m++;
|
||
|
|
|
||
|
|
if($_REQUEST['category']){
|
||
|
|
$col=0;
|
||
|
|
$as=sum($year,$_REQUEST['account'],"",$_REQUEST['category'],$_REQUEST['active']);
|
||
|
|
|
||
|
|
$act="";
|
||
|
|
if($_REQUEST['active']=="active")$act=" and product_active='1'";
|
||
|
|
elseif($_REQUEST['active']=="active")$act=" and product_active='0'";
|
||
|
|
$zz="select * from ecmproducts where product_category_id='".$_REQUEST['category']."' and deleted='0'".$act." order by code asc";
|
||
|
|
$ww=$GLOBALS['db']->query($zz);
|
||
|
|
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
|
||
|
|
$col=0;
|
||
|
|
$avg_p=0;
|
||
|
|
$avg_p_qty=0;
|
||
|
|
$avg_p_pur=0;
|
||
|
|
|
||
|
|
$sumy=getSum($as,$year,$rr['id'],$_REQUEST['category']);
|
||
|
|
for($i=0;$i<count($months);$i++){
|
||
|
|
$avg_p_qty+=$sumy["qty"];
|
||
|
|
$avg_p+=$sumy["total"];
|
||
|
|
$avg_p_pur+=$sumy["total_purchase"];
|
||
|
|
}
|
||
|
|
|
||
|
|
//$sumy=getSum($as,$year,$rr['id'],"");
|
||
|
|
$stock_m=0;
|
||
|
|
$stock_m_order=0;
|
||
|
|
$o_value=0;
|
||
|
|
$o_value+=getValueByCode($vd,$rr['code']);
|
||
|
|
$o_c_value=0;
|
||
|
|
$o_c_value+=getValueByCode($vd,$rr['code'],"Created");
|
||
|
|
$o_a_value=0;
|
||
|
|
$o_a_value+=getValueByCode($vd,$rr['code'],"Approved");
|
||
|
|
$o_d_value=0;
|
||
|
|
$o_d_value+=getValueByCode($vd,$rr['code'],"Delivered");
|
||
|
|
if($sumy['total_purchase'])$stock_m=@($rr['ems_qty_in_stock']*$rr['ems_price'])/($last[$rr['id']]/12);
|
||
|
|
if($sumy['total_purchase'])$stock_m_order=@(($rr['ems_qty_in_stock']*$rr['ems_price'])+$o_value)/($last[$rr['id']]/12);
|
||
|
|
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rr['name']);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rr['ems_qty_in_stock']);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rr['ems_qty_in_stock']*$rr['ems_price']);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$o_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$o_created_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$o_approved_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$o_delivered_value);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$last[$rr['id']]/12);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$stock_m);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$stock_m_order);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,($rr['ems_qty_in_stock']*($rr['cvm']/$rr['ppc'])));
|
||
|
|
|
||
|
|
for($i=0;$i<count($months);$i++){
|
||
|
|
$sum=getSum($as,$year."-".$months[$i],$rr['id'],$_REQUEST['category']);
|
||
|
|
$p_pur=$sum["total_purchase"];
|
||
|
|
$p_qty=$sum["qty"];
|
||
|
|
$p=$sum["total"];
|
||
|
|
$cbm=$sum["cbm"];
|
||
|
|
|
||
|
|
|
||
|
|
if($p)$margin=100*($p-$p_pur)/$p;
|
||
|
|
else $margin=0;
|
||
|
|
|
||
|
|
if(!$p)$p=number_format(0,2,",",".");
|
||
|
|
|
||
|
|
|
||
|
|
$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='".$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='".$year."' and month='".$i."' and bean_id='".$rr['id']."' and type='qty'"));
|
||
|
|
|
||
|
|
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$p_qty);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_p_qty/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$p);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_p/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$p_pur);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$avg_p_pur/$mmm);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$margin);
|
||
|
|
$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$cbm);
|
||
|
|
//$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rvs['value']);
|
||
|
|
//$objPHPExcel->getActiveSheet()->SetCellValue($alf[$col++].$m,$rvq['value']);
|
||
|
|
}
|
||
|
|
|
||
|
|
$m++;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
$objPHPExcel->getActiveSheet()->setTitle('Simple');
|
||
|
|
$objPHPExcel->setActiveSheetIndex(0);
|
||
|
|
|
||
|
|
$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).":EH".($m)
|
||
|
|
);
|
||
|
|
|
||
|
|
$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)
|
||
|
|
)
|
||
|
|
),
|
||
|
|
"DY1:EH".($m)
|
||
|
|
);
|
||
|
|
|
||
|
|
$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)
|
||
|
|
)
|
||
|
|
),
|
||
|
|
"DY".($m).":EH".($m)
|
||
|
|
);
|
||
|
|
$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:EH1"
|
||
|
|
);
|
||
|
|
|
||
|
|
$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);
|
||
|
|
?>
|