mz/crm.twinpol.com
1
0
Fork 0
Code Issues 1 Pull Requests 1 Actions Packages Projects Releases Wiki Activity
Files
main
crm.twinpol.com/modules/EcmProductB2Bs/CreateXLS.back.php

287 lines
14 KiB
PHP
Raw Permalink Normal View History

Add php files
2025-05-12 15:44:39 +00:00
<?php
set_time_limit(999999);
$mn=explode(",",$_REQUEST['uid']);
//$mn=$_REQUEST['mass'];
if(!$_REQUEST['uid'])$mn=$_REQUEST['mass'];
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");
$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:AG1"
);
$alf="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$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('R')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('AG')->setWidth(20);
$objPHPExcel->getActiveSheet()->SetCellValue('A1','Product');
$objPHPExcel->getActiveSheet()->SetCellValue('B1','Code');
$objPHPExcel->getActiveSheet()->SetCellValue('C1','Category');
$objPHPExcel->getActiveSheet()->SetCellValue('D1','Inventory');
$objPHPExcel->getActiveSheet()->SetCellValue('E1','Ordered');
$objPHPExcel->getActiveSheet()->SetCellValue('F1','EMS price');
$objPHPExcel->getActiveSheet()->SetCellValue('G1','Unit price');
$objPHPExcel->getActiveSheet()->SetCellValue('H1','Inv value');
$objPHPExcel->getActiveSheet()->SetCellValue('I1','Ppc');
$objPHPExcel->getActiveSheet()->SetCellValue('J1','CBM stock');
$objPHPExcel->getActiveSheet()->SetCellValue('K1','CBM ordered');
$objPHPExcel->getActiveSheet()->SetCellValue('L1','Qty 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('M1','Avg price 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('N1','Qty this m');
$objPHPExcel->getActiveSheet()->SetCellValue('O1','Avg price this m');
$objPHPExcel->getActiveSheet()->SetCellValue('P1','Stock m');
$objPHPExcel->getActiveSheet()->SetCellValue('Q1','Pricebook');
$objPHPExcel->getActiveSheet()->SetCellValue('R1','MOQ');
$objPHPExcel->getActiveSheet()->SetCellValue('S1','Pieces per carton');
$objPHPExcel->getActiveSheet()->SetCellValue('T1','Carton dimensions (m)');
$objPHPExcel->getActiveSheet()->SetCellValue('U1','Carton netto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('V1','Carton brutto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('W1','Product netto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('X1','Product brutto weight');
$objPHPExcel->getActiveSheet()->SetCellValue('Y1','Packing dimensions');
$objPHPExcel->getActiveSheet()->SetCellValue('Z1','Carton volume');
$objPHPExcel->getActiveSheet()->SetCellValue('AA1','EAN');
$objPHPExcel->getActiveSheet()->SetCellValue('AB1','Short description');
$objPHPExcel->getActiveSheet()->SetCellValue('AC1','Long description');
$objPHPExcel->getActiveSheet()->SetCellValue('AD1','FOB price');
$objPHPExcel->getActiveSheet()->SetCellValue('AE1','SRP price');
$objPHPExcel->getActiveSheet()->SetCellValue('AF1','Custom duty rate');
$objPHPExcel->getActiveSheet()->SetCellValue('AG1','Commission rate');
/*
$objPHPExcel->getActiveSheet()->SetCellValue('O1','Pieces per carton');
$objPHPExcel->getActiveSheet()->SetCellValue('P1','Carton dimensions');
$objPHPExcel->getActiveSheet()->SetCellValue('Q1','Packing dimensions');
$objPHPExcel->getActiveSheet()->SetCellValue('R1','Carton volume');*/
$j=0;
if(count($mn)>0){
foreach($mn as $mm){
$mmm[]="id='".$mm."'";
}
}
if(count($mmm)<=0){
if($_SESSION['EcmProductB2BsForXLSwhere']=="")$_SESSION['EcmProductB2BsForXLSwhere']="1=1";
$where="where (".$_SESSION['EcmProductB2BsForXLSwhere'].") and deleted='0'";
//$_SESSION['EcmProductB2BsForXLSwhere']="";
}
else $where="where (".implode(" or ",$mmm).") and deleted='0'";
$ww=$GLOBALS['db']->query("select * from ecmproducts ".$where);
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
$objPHPExcel->getActiveSheet()->SetCellValue('A'.($j+2),$rr['name']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.($j+2),$rr['code']);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.($j+2),$rr['product_category_name']);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.($j+2),$rr['ems_qty_in_stock']);
$ems_qty_in_stock_sum+=$rr['ems_qty_in_stock'];
$objPHPExcel->getActiveSheet()->SetCellValue('E'.($j+2),$rr['ordered']);
$ordered_sum+=$rr['ordered'];
$objPHPExcel->getActiveSheet()->SetCellValue('F'.($j+2),$rr['ems_price']);
$ems_price_sum+=$rr['ems_price'];
$objPHPExcel->getActiveSheet()->SetCellValue('G'.($j+2),$rr['purchase_price']);
$purchase_price_sum+=$rr['purchase_price'];
$SALE_AVG_PRICE90=0;
$sale0=0;
$sale30=0;
$sale90=0;
$sale180=0;
$qty0=0;
$qty30=0;
$qty90=0;
$qty180=0;
$ddate=date("Y-m-d",mktime()-200*24*3600);
$w=$GLOBALS['db']->query("select ecminvoiceoutitems.price as price,ecminvoiceoutitems.quantity as quantity,ecminvoiceouts.register_date as date from ecminvoiceoutitems inner join ecminvoiceouts on ecminvoiceoutitems.ecminvoiceout_id=ecminvoiceouts.id where ecminvoiceoutitems.ecmproduct_id='".$rr['id']."' and ecminvoiceouts.type!='correct' and ecminvoiceouts.register_date>'".$ddate."'");
while($r=$GLOBALS['db']->fetchByAssoc($w)){
$sale+=$r['price']*$r['quantity'];
$qty+=$r['quantity'];
$aq[$r['no']]+=$r['quantity'];
$date=date("Y-m");
$m=(int)date("m");
$date1=date("Y-m",mktime(0,0,0,($m-1),1,date("Y")));
$date2=date("Y-m",mktime(0,0,0,($m-2),1,date("Y")));
$date3=date("Y-m",mktime(0,0,0,($m-3),1,date("Y")));
$date4=date("Y-m",mktime(0,0,0,($m-4),1,date("Y")));
$date5=date("Y-m",mktime(0,0,0,($m-5),1,date("Y")));
$date6=date("Y-m",mktime(0,0,0,($m-6),1,date("Y")));
$d=explode("-",$r['date']);
$dd=$d[0]."-".$d[1];
if($dd==$date){
$sale0+=$r['price']*$r['quantity'];
$qty0+=$r['quantity'];
$aq0[$r['no']]+=$r['quantity'];
}
if($dd==$date1){
$sale30+=$r['price']*$r['quantity'];
$qty30+=$r['quantity'];
$aq30[$r['no']]+=$r['quantity'];
}
if($dd==$date1 || $dd==$date2 || $dd==$date3){
$sale90+=$r['price']*$r['quantity'];
$qty90+=$r['quantity'];
$aq90[$r['no']]+=$r['quantity'];
}
if($dd==$date1 || $dd==$date2 || $dd==$date3 || $dd==$date4 || $dd==$date5 || $dd==$date6){
$sale180+=$r['price']*$r['quantity'];
$qty180+=$r['quantity'];
$aq180[$r['no']]+=$r['quantity'];
}
}
$SALE_QTY=$qty;
$SALE_QTY0=$qty0;
$SALE_QTY30=$qty30;
$SALE_QTY90=($qty90/3);
$SALE_QTY180=$qty180;
$SALE_SALE=$sale;
$SALE_SALE0=$sale0;
$SALE_SALE30=$sale30;
$SALE_SALE90=$sale90;
$SALE_SALE80=$sale180;
if($qty>0)$SALE_AVG_PRICE=round($sale/$qty,2);
if($qty0>0)$SALE_AVG_PRICE0=round($sale0/$qty0,2);
if($qty30>0)$SALE_AVG_PRICE30=round($sale30/$qty30,2);
if($qty90>0)$SALE_AVG_PRICE90=round($sale90/$qty90,2);
if($qty180>0)$SALE_AVG_PRICE180=round($sale180/$qty180,2);
if($rr['pieces_per_carton'])$cbm=(($rr['ems_qty_in_stock']*$rr['carton_volume_meter'])/$rr['pieces_per_carton']);
if($rr['pieces_per_carton'])$cbm_ordered=(($rr['ordered']*$rr['carton_volume_meter'])/$rr['pieces_per_carton']);
$objPHPExcel->getActiveSheet()->SetCellValue('H'.($j+2),($rr['ems_price']*$rr['ems_qty_in_stock']));
$stock_value_sum+=($rr['ems_price']*$rr['ems_qty_in_stock']);
$objPHPExcel->getActiveSheet()->SetCellValue('I'.($j+2),$rr['pieces_per_carton']);
$pieces_per_carton_sum+=$rr['pieces_per_carton'];
$objPHPExcel->getActiveSheet()->SetCellValue('J'.($j+2),$cbm);
$cbm_sum+=$cbm;
$objPHPExcel->getActiveSheet()->SetCellValue('K'.($j+2),$cbm_ordered);
$cbm_ordered_sum+=$cbm_ordered;
$objPHPExcel->getActiveSheet()->SetCellValue('L'.($j+2),$SALE_QTY90);
$sale_qty90_sum+=$SALE_QTY90;
$objPHPExcel->getActiveSheet()->SetCellValue('M'.($j+2),$SALE_AVG_PRICE90);
$sale_avg_price90_sum+=$SALE_AVG_PRICE90;
$objPHPExcel->getActiveSheet()->SetCellValue('N'.($j+2),$SALE_QTY0);
$sale_qty0_sum+=$SALE_QTY0;
$objPHPExcel->getActiveSheet()->SetCellValue('O'.($j+2),$SALE_AVG_PRICE0);
$sale_avg_price0_sum+=$SALE_AVG_PRICE0;
$objPHPExcel->getActiveSheet()->SetCellValue('P'.($j+2),($rr['ems_qty_in_stock']/($SALE_QTY90)));
//pricebook
$accounts=array("Auchan","Carrefour","Euro","Real");
$dig=array();
foreach($accounts as $account){
$www=$GLOBALS['db']->query("select p.id from ecmpricebooks_ecmproducts as p inner join ecmpricebooks as e on e.id=p.ecmpricebook_id where p.ecmproduct_id='".$rr['id']."' and e.name='".$account."' and e.deleted='0' and p.deleted='0'");
if(mysql_num_rows($www)>0)$dig[]=$account[0];
}
$objPHPExcel->getActiveSheet()->SetCellValue('Q'.($j+2),implode(",",$dig));
$objPHPExcel->getActiveSheet()->SetCellValue('R'.($j+2),$rr['moq']);
$objPHPExcel->getActiveSheet()->SetCellValue('S'.($j+2),$rr['pieces_per_carton']);
$objPHPExcel->getActiveSheet()->SetCellValue('T'.($j+2),$rr['carton_dimensions_1']." x ".$rr['carton_dimensions_2']." x ".$rr['carton_dimensions_3']);
$objPHPExcel->getActiveSheet()->SetCellValue('U'.($j+2),$rr['carton_netto_weight']);
$objPHPExcel->getActiveSheet()->SetCellValue('V'.($j+2),$rr['carton_brutto_weight']);
$objPHPExcel->getActiveSheet()->SetCellValue('W'.($j+2),$rr['product_netto_weight']);
$objPHPExcel->getActiveSheet()->SetCellValue('X'.($j+2),$rr['product_brutto_weight']);
$objPHPExcel->getActiveSheet()->SetCellValue('Y'.($j+2),$rr['packing_dimensions_1']." x ".$rr['packing_dimensions_2']." x ".$rr['packing_dimensions_3']);
$objPHPExcel->getActiveSheet()->SetCellValue('Z'.($j+2),$rr['carton_volume_meter']);
$rl=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select * from ecmproduct_language where ecmproduct_id='".$rr['id']."' and language='pl'"));
$objPHPExcel->getActiveSheet()->SetCellValue('AA'.($j+2),$rl['ean']);
if($rl['short_description'])$sd="yes";
else $sd="no";
if($rl['long_description'])$ld="yes";
else $ld="no";
$objPHPExcel->getActiveSheet()->SetCellValue('AB'.($j+2),$sd);
$objPHPExcel->getActiveSheet()->SetCellValue('AC'.($j+2),$ld);
$objPHPExcel->getActiveSheet()->SetCellValue('AD'.($j+2),$rr['fob_price']);
$objPHPExcel->getActiveSheet()->SetCellValue('AE'.($j+2),$rr['srp_price']);
$objPHPExcel->getActiveSheet()->SetCellValue('AF'.($j+2),$rr['custom_duty_rate']);
$objPHPExcel->getActiveSheet()->SetCellValue('AG'.($j+2),$rr['commission_rate']);
$j++;
}
$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)
)
),
"A".($j+2).":AG".($j+2)
);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.($j+2),$ems_qty_in_stock_sum);
$objPHPExcel->getActiveSheet()->SetCellValue('E'.($j+2),$ordered_sum);
$objPHPExcel->getActiveSheet()->SetCellValue('F'.($j+2),($ems_price_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('G'.($j+2),($purchase_price_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('H'.($j+2),($stock_value_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('I'.($j+2),($pieces_per_carton_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('J'.($j+2),$cbm_sum);
$objPHPExcel->getActiveSheet()->SetCellValue('K'.($j+2),$cbm_ordered_sum);
$objPHPExcel->getActiveSheet()->SetCellValue('L'.($j+2),($sale_qty90_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('M'.($j+2),($sale_avg_price90_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('N'.($j+2),($sale_qty0_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('O'.($j+2),($sale_avg_price0_sum/$j));
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
chmod("cache/upload",0777);
$microtime=str_replace(".","",str_replace(" ","",microtime()));
$name="cache/upload/Products".$microtime.".xlsx";
$objWriter->save($name);
chmod($name,0777);
header("Location: ".$name);
?>
Reference in New Issue Copy Permalink