crm.e5.pl/modules/EcmPrices/CreateXLS.php

<?php
set_time_limit(99999999);
ini_set('display_errors',1);
require_once("modules/EcmPrices/ListHelper.php");
$r=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select name,exchange_rate_id,ecmlanguage,currency_value from ecmprices where id='".$_COOKIE['pricebook_id']."'"));
$name=$r['name'];
$exchange_rate_id=$r['exchange_rate_id'];
$lang=$_REQUEST['langs2'];
$er_value=$r['currency_value'];
if(!$er_value){
require_once('modules/Currencies/Currency.php');
$currency  = new Currency();
$currency->retrieve($exchange_rate_id);
$er_value=$currency->conversion_rate;
}
set_include_path('include/PHPExcel/');
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
//print "t";
$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");
//print "t"; 

$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:Z1"
);

//print "t";
$result = $GLOBALS['db']->query("select * from ecmprices_customview where id='".$_COOKIE['customview_id']."'");
$row=$GLOBALS['db']->fetchByAssoc($result);
$c=explode("||",$row['columns']);
$t=explode("||",$row['titles']);

$where="";
if(count($_SESSION['pricebook_check'][$_SESSION['pricebook_id']])>0)
{
	foreach($_SESSION['pricebook_check'][$_SESSION['pricebook_id']] as $key=>$value){
		if($_SESSION['pricebook_check'][$_SESSION['pricebook_id']][$key]!="true" && $_SESSION['pricebook_check'][$_SESSION['pricebook_id']]['all']=="true"){
			$where_clauses[]="ecmprices_ecmproducts.id!='".$key."'";
			$orand=' and ';
		}
		elseif($_SESSION['pricebook_check'][$_SESSION['pricebook_id']][$key]=="true" && $_SESSION['pricebook_check'][$_SESSION['pricebook_id']]['all']!="true"){
			$where_clauses[]="ecmprices_ecmproducts.id='".$key."'";
			$orand=' or ';
		}
	}
	if(count($where_clauses)>0)$where="(".implode($orand,$where_clauses).") and ";
	elseif($_SESSION['pricebook_check'][$_SESSION['pricebook_id']]['all']!="true" && count($where_clauses)==0)$where="ecmprices_ecmproducts.id='9999999999999999' and ";
}
else $where="ecmprices_ecmproducts.id='9999999999999999' and ";
//print $where;
$z="select 
ecmprices_ecmproducts.price as price,
ecmprices_ecmproducts.recipient_code as recipient_code,
ecmprices_ecmproducts.id as idp,
ecmprices_ecmproducts.remarks_pl as remarks_pl,
ecmprices_ecmproducts.remarks_en as remarks_en,
ecmprices_ecmproducts.remarks_de as remarks_de,


ecmproducts.*,
if(ecmprices_ecmproducts.price>0,(100-(100*ecmproducts.purchase_price/ecmprices_ecmproducts.price)),0) as margin_rate,

ecmproduct_language_pl_view.ean as ean_pl, 
ecmproduct_language_pl_view.short_description as short_description_pl, 
ecmproduct_language_pl_view.long_description as long_description_pl, 
ecmproduct_language_pl_view.remarks as default_remarks_pl, 

ecmproduct_language_en_view.ean as ean_en, 
ecmproduct_language_en_view.short_description as short_description_en, 
ecmproduct_language_en_view.long_description as long_description_en, 
ecmproduct_language_en_view.remarks as default_remarks_en, 

ecmproduct_language_de_view.ean as ean_de, 
ecmproduct_language_de_view.short_description as short_description_de, 
ecmproduct_language_de_view.long_description as long_description_de, 
ecmproduct_language_de_view.remarks as default_remarks_de 

from ecmprices_ecmproducts

left join ecmproduct_language_pl_view on ecmproduct_language_pl_view.ecmproduct_id=ecmprices_ecmproducts.ecmproduct_id
left join ecmproduct_language_en_view on ecmproduct_language_en_view.ecmproduct_id=ecmprices_ecmproducts.ecmproduct_id
left join ecmproduct_language_de_view on ecmproduct_language_de_view.ecmproduct_id=ecmprices_ecmproducts.ecmproduct_id

left join ecmproducts on ecmproducts.id=ecmprices_ecmproducts.ecmproduct_id 

where ".$where." ecmprices_ecmproducts.ecmprice_id='".$_SESSION['pricebook_id']."' and ecmprices_ecmproducts.deleted='0'";
if($_REQUEST['order_by']){
	$order=str_replace($_REQUEST['order_by']." desc",$_REQUEST['order_by']." ".$_REQUEST['sorder'],$order);
	$order=str_replace($_REQUEST['order_by']." asc",$_REQUEST['order_by']." ".$_REQUEST['sorder'],$order);
}
//$z.=" order by ".$_REQUEST['order_by'];

if($_SESSION['pricebook_order'])$z.=" order by ".$_SESSION['pricebook_order'];

//print $z;

$result = $GLOBALS['db']->query($z);
$alf="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
for($i=0;$i<count($t);$i++){
	if($t[$i]){
		if($c[$i]=="name")$width="50";
		else $width="30";
		$objPHPExcel->getActiveSheet()->getColumnDimension($alf[$i])->setWidth($width);
		$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].'1',$t[$i]);
		
	}
}
$j=2;
while($row=$GLOBALS['db']->fetchByAssoc($result)){	
	$stop[$row['code']]=false;
	$stop[$row['code']]=false;
	$sale30="";
	$qty30="";
	$sale90="";
	$qty90="";
	$sale180="";
	$qty180="";
	for($i=0;$i<count($t);$i++){
		if($t[$i]){	  
	  		if(($c[$i]=="sale_qty30" || $c[$i]=="sale_qty90" || $c[$i]=="sale_qty180" || $c[$i]=="sale_avg_price30" || $c[$i]=="sale_avg_price90" || $c[$i]=="sale_avg_price180") && $stop[$row['code']]==false){
				$w=$GLOBALS['db']->query("select ecmproducts_fk_list.price as price,ecmproducts_fk_list.quantity as quantity,ecmproducts_fk_list.date as date from ecmproducts_fk_list where ecmproducts_fk_list.code like '".$row['code']."'");
	  			//echo mysql_error();die();
	  			while($r=$GLOBALS['db']->fetchByAssoc($w)){
					$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 || $dd==$date1){
	  					$sale30+=$r['price']*$r['quantity'];
						$qty30+=$r['quantity'];
						$aq30[$r['no']]+=$r['quantity'];
					}
					if($dd==$date || $dd==$date1 || $dd==$date2 || $dd==$date3){
	  					$sale90+=$r['price']*$r['quantity'];
						$qty90+=$r['quantity'];
						$aq390[$r['no']]+=$r['quantity'];
					}
					if($dd==$date || $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'];
					}
				}
	  			if($qty>0)$sq=round($sale/$qty,2);
	  			if($qty30>0)$sq30=round($sale30/$qty30,2);
	  			else $sq30="";
	  			if($qty90>0)$sq90=round($sale90/$qty90,2);
	  			else $sq90="";
	  			if($qty180>0)$sq180=round($sale180/$qty180,2);
	  			else $sq180="";
				
				$stop[$row['code']]=true;
	  		}
		
			if($c[$i]=="ordered")$html.=$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['ordered']);
			elseif($c[$i]=="sale_qty30")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$qty30);
			elseif($c[$i]=="sale_qty90")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$qty90);
			elseif($c[$i]=="sale_qty180")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$qty180);
			elseif($c[$i]=="sale_avg_price30")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$sq30);
			elseif($c[$i]=="sale_avg_price90")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$sq90);
			elseif($c[$i]=="sale_avg_price180")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$sq180);
			elseif($c[$i]=="ems_qty_in_stock")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['ems_qty_in_stock']);
			elseif($c[$i]=="list_price")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,addExchangeRateValue($row['price'],$c[$i],$er_value,true));
			elseif($c[$i]=="margin_rate")$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['margin_rate']);
			elseif($c[$i]=="name"){
				if($lang=="en_us"){
					//$html.='<td valign="top" height="'.$height.'" width="'.$product_name_width.'">'.iconv("UTF-8","ISO-8859-2",$row['short_description_en']).'</td>';
					$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['short_description_en']);
				}
				elseif($lang=="ge_ge"){
					//$html.='<td valign="top" height="'.$height.'" width="'.$product_name_width.'">'.iconv("UTF-8","ISO-8859-2",$row['short_description_en']).'</td>';
					$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['short_description_de']);
				}
				else {
					//$html.='<td valign="top" height="'.$height.'" width="'.$product_name_width.'">'.iconv("UTF-8","ISO-8859-2",$row['name']).'</td>';
					$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['name']);
				}
			}
			elseif($c[$i]=="product_category_name"){
				if($lang=="en_us"){
					$rrp=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select name_en from ecmproductcategories where id='".$row['product_category_id']."'"));
					//$html.=$tds.iconv("UTF-8","ISO-8859-2",$rrp['name_en']).$tde;
					$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$rrp['name_en']);
				}
				elseif($lang=="ge_ge"){
					$rrp=$GLOBALS['db']->fetchByAssoc($GLOBALS['db']->query("select name_de from ecmproductcategories where id='".$row['product_category_id']."'"));
					//$html.=$tds.iconv("UTF-8","ISO-8859-2",$rrp['name_en']).$tde;
					$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$rrp['name_de']);
				}
				else {
					//$html.=$tds.iconv("UTF-8","ISO-8859-2",$row['product_category_name']).$tde;
					$objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,$row['product_category_name']);
				}
			}
			else $objPHPExcel->getActiveSheet()->SetCellValue($alf[$i].$j,addExchangeRateValue($row[$c[$i]],$c[$i],$er_value,true));
		}
	}
	$j++;
}

$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);

//print "t";
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//print "t";
$microtime=str_replace(".","",str_replace(" ","",microtime()));
$name="cache/upload/Products".$microtime.".xlsx";
$objWriter->save($name);
//print "tr";
chmod($name,0777);
//print $name;
if($_REQUEST['disposition']=="D")header("Location: ".$name);
?>