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/EcmProducts/CreateXLS.php
Michał Zieliński 82d5804ac4 Add php files
2025-05-12 15:44:39 +00:00

175 lines
7.8 KiB
PHP
Executable File
Raw Permalink Blame History

<?php
set_time_limit(999999);
$mn=explode(",",$_REQUEST['uid']);
//$mn=$_REQUEST['mass'];
if(!$_REQUEST['uid'])$mn=$_REQUEST['mass'];
include_once("modules/EcmProducts/EcmProduct.php");
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(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$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()->getColumnDimension('J')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
$objPHPExcel->getActiveSheet()->SetCellValue('A1','Code');
$objPHPExcel->getActiveSheet()->SetCellValue('B1','Product');
$objPHPExcel->getActiveSheet()->SetCellValue('C1','Category');
$objPHPExcel->getActiveSheet()->SetCellValue('D1','Inv');
$objPHPExcel->getActiveSheet()->SetCellValue('E1','Ord');
$objPHPExcel->getActiveSheet()->SetCellValue('F1','EMS price');
$objPHPExcel->getActiveSheet()->SetCellValue('G1','Inv value');
$objPHPExcel->getActiveSheet()->SetCellValue('H1','Ppc');
$objPHPExcel->getActiveSheet()->SetCellValue('I1','Qty this m');
$objPHPExcel->getActiveSheet()->SetCellValue('J1','Sale this m');
$objPHPExcel->getActiveSheet()->SetCellValue('K1','Margin this m');
$objPHPExcel->getActiveSheet()->SetCellValue('L1','Qty 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('M1','Sale 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('N1','Margin 3 m');
$objPHPExcel->getActiveSheet()->SetCellValue('O1','Inv+ord m');
$objPHPExcel->getActiveSheet()->SetCellValue('P1','Pricebook');
$j=0;
if(count($mn)>0){
foreach($mn as $mm){
$mmm[]="id='".$mm."'";
}
}
if(count($mmm)<=0){
if($_SESSION['EcmProductsForXLSwhere']=="")$_SESSION['EcmProductsForXLSwhere']="1=1";
$where="where (".$_SESSION['EcmProductsForXLSwhere'].") and deleted='0'";
//$_SESSION['EcmProductsForXLSwhere']="";
}
else $where="where (".implode(" or ",$mmm).") and deleted='0'";
//echo $where;die();
$ww=$GLOBALS['db']->query("select * from ecmproducts ".$where);
while($rr=$GLOBALS['db']->fetchByAssoc($ww)){
/*$s0=$ecmp->getSale(array(date("Y-m")));
$s3=$ecmp->getSale(array(date("Y-m",mktime()-1*30*24*3600),date("Y-m",mktime()-2*30*24*3600),date("Y-m",mktime()-3*30*24*3600)));
$GLOBALS['db']->query("update ecmproducts set q0='".$s0['quantity']."',p0='".($s0['sale']-$s0['purchase'])."',s0='".$s0['sale']."',q3='".($s3['quantity']/3)."',p3='".(($s3['sale']-$s3['purchase'])/3)."',s3='".($s3['sale']/3)."',stock_month='".$stock_month."',stock_value='".$stock_value."' where id='".$rr['id']."'");
*/
$objPHPExcel->getActiveSheet()->SetCellValue('A'.($j+2),$rr['code']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.($j+2),$rr['name']);
$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['ems_price']*$rr['ems_qty_in_stock']);
$stock_value_sum+=$rr['ems_price']*$rr['ems_qty_in_stock'];
$objPHPExcel->getActiveSheet()->SetCellValue('H'.($j+2),$rr['pieces_per_carton']);
$pieces_per_carton_sum+=$rr['pieces_per_carton'];
$objPHPExcel->getActiveSheet()->SetCellValue('I'.($j+2),$rr['q0']);
$q0_sum+=$rr['q0'];
$objPHPExcel->getActiveSheet()->SetCellValue('J'.($j+2),$rr['s0']);
$s0_sum+=$rr['s0'];
$objPHPExcel->getActiveSheet()->SetCellValue('K'.($j+2),$rr['p0']);
$p0_sum+=$rr['p0'];
$objPHPExcel->getActiveSheet()->SetCellValue('L'.($j+2),$rr['q3']);
$q3_sum+=$rr['q3'];
$objPHPExcel->getActiveSheet()->SetCellValue('M'.($j+2),$rr['s3']);
$s3_sum+=$rr['s3'];
$objPHPExcel->getActiveSheet()->SetCellValue('N'.($j+2),$rr['p3']);
$p3_sum+=$rr['p3'];
if(round(($rr['s3']))!=0)$stock_month=(($rr['ems_qty_in_stock']+$rr['ordered'])/round(($rr['s3'])));
else $stock_month=0;
$objPHPExcel->getActiveSheet()->SetCellValue('O'.($j+2),$stock_month);
$stock_month_sum+=$rr['stock_month'];
//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('P'.($j+2),implode(",",$dig));
$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),($stock_value_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('H'.($j+2),($pieces_per_carton_sum/$j));
$objPHPExcel->getActiveSheet()->SetCellValue('I'.($j+2),($q0_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('J'.($j+2),($s0_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('K'.($j+2),($p0_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('L'.($j+2),($q3_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('M'.($j+2),($s3_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('N'.($j+2),($p3_sum));
$objPHPExcel->getActiveSheet()->SetCellValue('O'.($j+2),($stock_month_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 View Git Blame Copy Permalink