antadis/bebeboutik-scripts
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
master
bebeboutik-scripts/cron_queue_bdc.php
Michael RICOIS f63b399633 Script form server
2017-12-18 17:14:31 +01:00

1310 lines
66 KiB
PHP
Raw Permalink Blame History

<?php
ini_set('memory_limit', '5G');
ini_set('max_execution_time', 0);
$_SERVER['HTTP_HOST'] = 'www.bebeboutik.com';
$_SERVER['SERVER_PORT'] = 80;
include dirname(__FILE__).'/www/config/config.inc.php';
include dirname(__FILE__).'/www/init.php';
include dirname(__FILE__).'/www/tools/PHPExcel/Classes/PHPExcel.php';
include dirname(__FILE__).'/www/tools/PHPExcel/Classes/PHPExcel/IOFactory.php';
include dirname(__FILE__).'/www/modules/privatesales/Sale.php';
include dirname(__FILE__).'/www/modules/privatesales_logistique/classes/MakeStats.php';
include dirname(__FILE__).'/www/modules/privatesales_logistique/classes/OrderForm.php';
include dirname(__FILE__).'/www/modules/privatesales_logistique/classes/SaleStats.php';
include dirname(__FILE__).'/www/modules/privatesales_logistique/classes/DateManager.php';
// ONLY FOR ETAT DES VENTES FOR NOW (type 3)
// CREATE TABLE `ps_ant_queue_logistic` (
// `id_category` int(11) NOT NULL,
// `date_from` DATETIME,
// `date_to` DATETIME,
// `file_type` VARCHAR(255),
// `status` TINYINT(4) DEFAULT 0,
// `date_add` DATETIME
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
$on_process = (int) Db::getInstance()->getValue('
SELECT COUNT(`id_queue`) FROM `'._DB_PREFIX_.'ant_queue_logistic` WHERE `status`=1
');
if($on_process!=0){
exit;
}
$queue = Db::getInstance()->getRow('
SELECT * FROM `'._DB_PREFIX_.'ant_queue_logistic`
ORDER BY `id_queue` ASC
');
if($queue){
Db::getInstance()->execute('
UPDATE `'._DB_PREFIX_.'ant_queue_logistic` SET `status`=1 WHERE `id_queue`='.(int)$queue['id_queue'].'
');
$lang_id = $queue['version'];
$tax = new Tax(1);
$TVA = number_format($tax->rate, 2);
$type = (int)$queue['file_type'];
$id_category = (int)$queue['id_category'];
$date_from = new DateManager("10/11/2017 07:00:00");
$date_to = new DateManager("17/11/2017 01:59:59");
$date_of_extract = new DateManager(date('Y-m-d H:i:s'));
$date_of_extract->setLangue(strtoupper($lang_id));
$date_from->setLangue(strtoupper($lang_id));
$date_to->setLangue(strtoupper($lang_id));
$array_lang = array(
'en' => array(
'Product ID' => 'Product ID',
'EAN' => 'EAN',
'Reference BB' => 'BB Boutik ID',
'Product Name' => 'Product Name',
'Attribute' => 'Size',
'Quantity' => 'Quantities sold',
'Total quantity' => 'Booked quantities',
'Remaining quantity' => 'Remaining quantities',
'Unit Price' => 'Unit Price',
'Total Amount' => 'Total Amount',
'Purchase order' => 'Purchase order',
'Purchase order logistic' => 'Purchase order logistic',
'State order' => 'Sales report',
'Missing' => 'Missing',
'Colisage' => 'Packing',
'To Sent' => 'To sent',
'To Stock' => 'To stock',
'Shipped Product' => 'Shipped products',
'The number below must be present on each document regarding this order, delivery note, invoice :' => 'The number below must be present on each document regarding this order, delivery note, invoice :',
'PURCHASE ORDER NUMBER' => 'PURCHASE ORDER NUMBER',
'Delivery adress' => 'Delivery address',
'PURCHASE ORDER DATE' => 'PURCHASE ORDER DATE',
'DELIVERY DUTY PAID' => 'DELIVERY DUTY PAID',
'YES' => 'YES',
),
'fr' => array(
'Product ID' => 'Référence',
'EAN' => 'EAN',
'Reference BB' => 'Référence BBB',
'Product Name' => 'Nom du produit',
'Attribute' => 'Déclinaison',
'Quantity' => 'Quantités vendues',
'Total quantity' => 'Stock initial',
'Remaining quantity' => 'Stock restant',
'Unit Price' => 'PU HT',
'Total Amount' => 'Prix Total HT',
'Purchase order' => 'Bon de commande',
'Purchase order logistic' => 'Bon de commande logistique',
'State order' => 'Etat des ventes',
'YES' => 'OUI',
'Missing' => 'Manquant',
'Colisage' => 'Colisage/Ferme',
'To Sent' => 'À envoyer',
'To Stock' => 'À mettre en stock',
'Shipped Product' => 'Produits livrés',
'PURCHASE ORDER NUMBER' => 'NUMERO DE BON DE COMMANDE',
'Delivery adress' => 'Adresse de livraison',
'PURCHASE ORDER DATE' => 'DATE DU BON DE COMMANDE',
'DELIVERY DUTY PAID' => 'FRANCO DE PORT',
'The number below must be present on each document regarding this order, delivery note, invoice :' => 'Le numéro suivant doit apparaître sur toute la correspondance annexe, les papiers de livraison et les factures :',
)
);
$lang = $array_lang[$lang_id];
$sale = Sale::getSaleFromCategory($id_category);
$category = new Category($id_category, 2);
$products = MakeStats::getProductsByCat($id_category);
$lines = array();
$array_product = array();
$arrayProduct = array();
foreach($products as $product){
$array_product[$product['id_product']] = $arrayProduct[$product['id_product']] = new Product($product['id_product']);
}
$order_states = explode(',', Configuration::get('PS_IT_OF_ORDER_STATES'));
if ($type == 2 || $type == 4) {
$lines = MakeStats::getDetailsByIdsProduct(array_keys($arrayProduct), $order_states, $date_from->getDate('SQL'), $date_to->getDate('SQL'));
} else {
$lines = MakeStats::getOrdersByIdsProduct(array_keys($arrayProduct), $order_states, $date_from->getDate('SQL'), $date_to->getDate('SQL'));
}
// for stats
$lines_tmp = $lines;
if ($type == 4) {
$lines = MakeStats::getArrayLines($lines, false, $sale->id);
} else {
$lines = MakeStats::getArrayLines($lines);
}
if ($type == 3) {
$stats = new SaleStats($sale, $category);
$stats->loadData();
$total_sale_product = array();
$total_bdc_ht = 0;
foreach ($lines_tmp as $key => $order_line) {
$id_product = $order_line['product_id'];
$id_product_attribute = $order_line['product_attribute_id'];
$current_product = $array_product[$id_product];
if($id_product_attribute != 0) {
$combination = new Combination($id_product_attribute);
if($combination->wholesale_price == 0) {
$wholesale_price = $current_product->wholesale_price;
} else {
$wholesale_price = $combination->wholesale_price;
}
} else {
$wholesale_price = $current_product->wholesale_price;
}
$quantity_to_cmd = $order_line['product_quantity'] - $order_line['product_quantity_reinjected'];
$total_sale_ht += $quantity_to_cmd * $wholesale_price;
if (!is_array($total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute]) ){
$total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute] = array();
$total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute]['total_ht'] = 0;
$total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute]['quantiy'] = 0;
$total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute]['name'] = $current_product->name[2];
}
$price_ht = $current_product->getPrice(false, $id_product_attribute, 2);
$price_ttc = $current_product->getPrice(true, $id_product_attribute, 2);
$total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute]['total_ht'] += $price_ht;
$total_sale_product[(int)$id_product.'-'.(int) $id_product_attribute]['quantiy'] += $quantity_to_cmd;
}
$stats->addToStats('total_bdc_ht', $total_sale_ht);
$bestsales = MakeStats::orderMultiArray($total_sale_product, 'total_ht');
$bestsales_extract = array_slice($bestsales, 0, 3);
$i = 1;
foreach ($bestsales_extract as $key => $bestsalesProduct) {
$stats->addToStats('bestsale_'.$i.'_product', $bestsalesProduct['name']);
$stats->addToStats('bestsale_'.$i.'_quantity', $bestsalesProduct['quantiy']);
$stats->addToStats('bestsale_'.$i.'_ca', $bestsalesProduct['total_ht']);
$i++;
}
$stats->loadDataForSubCategory();
$fp = fopen('extracts/sales_logistique/Stats_vente_'. $stats->sale->title[2].'.csv', 'w+');
foreach ($stats->data_stats as $key => $stat) {
$text = $stats->lang[$key] . ';' . $stat ."\r\n";
fwrite($fp, $text);
}
foreach ($stats->data_subcategories as $key => $subcategories) {
fwrite($fp, 'Sous categorie '. ($key+1)."\r\n");
foreach ($subcategories as $key_2 => $subcategory) {
$text = $stats->lang[$key_2] . ';' . $subcategory ."\r\n";
fwrite($fp, $text);
}
}
fclose($fp);
die;
}
$arrayTmp = array();
foreach($lines as $id_product => $arrayProduct)
{
foreach($arrayProduct as $id_product_attribute => $values)
{
//echo "<pre>";var_dump($values);echo "</pre>";
$arrayTmp[$values['line']['product_id']][$values['line']['product_attribute_id']] = $values;
}
}
// associate position
$arrayTmp2 = array();
foreach ($products as $key => $product_order) {
if (isset($arrayTmp[$product_order['id_product']])) {
$arrayTmp2[] = $arrayTmp[$product_order['id_product']];
}
}
// $lines2 = $arrayTmp;
$lines2 = $arrayTmp2;
// ksort($lines2);
$have_declinaison = false;
$array = array();
foreach($lines2 as $key => $lines_attributes)
{
foreach($lines_attributes as $values)
{
$array[$key][] = $values;
if($values['line']['product_attribute_name_base'] != '') {
if (!$have_declinaison) {
$have_declinaison = true;
}
}
}
}
$lines2 = array_replace($lines2, $array);
switch($type)
{
case '1':
$title = $lang['Purchase order'].' '. $category->name .' - Bébé Boutik '. $date_from->getNomMois() .' '.$date_from->getAnnee();
break;
case '2':
$extrafields = Category::getSalesInfos(array((int)$category->id_category));
$title = $lang['State order'] .' '. $category->name . ' - ' . $extrafields[(int)$category->id_category]['sales'][1];
break;
case '4':
$title = $lang['Purchase order logistic'].' '. $category->name .' - Bébé Boutik '. $date_from->getNomMois() .' '.$date_from->getAnnee();
break;
}
$firstColumn = 'A';
if($have_declinaison === true)
{
if ($type == 4) {
$lastColumn = 'M';
$letter_product_ean = 'B';
$letter_product_ref_interne = 'C';
$letter_product_name = 'D';
$letter_product_attribute_name = 'E';
$letter_product_ht = 'F';
//$letter_product_M1_M2 = 'F';
$letter_product_M1 = 'G';
$letter_product_multi = 'H';
$letter_product_stock3btk = 'I';
$letter_product_missing = 'J';
$letter_product_to_sent = 'K'; // (M1 + Multi - Missing)
$letter_product_to_stock = 'L';
$letter_product_shipped = 'M'; // (M1 + Multi - stock3BTK - Missing + to_stock)
} else {
if ($type == 1) {
$lastColumn = 'H';
} else {
$lastColumn = 'J';
}
$letter_bdc_title = 'C';
$letter_end_address = 'B';
$letter_date = 'C';
$letter_franco = 'D';
$letter_condition = 'E';
$letter_product_ean = 'B';
$letter_product_ref_interne = 'C';
$letter_product_name = 'D';
$letter_product_attribute_name = 'E';
/*$letter_product_qty = 'F';*/
if ($type == 1) {
$letter_product_qty = 'F';
$letter_product_ht = 'G';
$letter_product_total = 'H';
} else {
$letter_product_total_qty = 'F';
$letter_product_qty = 'G';
$letter_product_remaining_qty = 'H';
$letter_product_ht = 'I';
$letter_product_total = 'J';
}
}
}
else
{
if ($type == 4) {
$lastColumn = 'L';
$letter_product_ean = 'B';
$letter_product_ref_interne = 'C';
$letter_product_name = 'D';
//$letter_product_attribute_name = 'E';
$letter_product_ht = 'E';
//$letter_product_M1_M2 = 'F';
$letter_product_M1 = 'F';
$letter_product_multi = 'G';
$letter_product_stock3btk = 'H';
$letter_product_missing = 'I';
$letter_product_to_sent = 'J'; // (M1 + Multi - Missing)
$letter_product_to_stock = 'K';
$letter_product_shipped = 'L'; // (M1 + Multi - stock3BTK - Missing + to_stock)
} else {
if ($type == 1) {
$lastColumn = 'G';
} else {
$lastColumn = 'I';
}
$letter_bdc_title = 'C';
$letter_end_address = 'B';
$letter_date = 'C';
$letter_franco = 'D';
$letter_condition = 'E';
$letter_product_ean = 'B';
$letter_product_ref_interne = 'C';
$letter_product_name = 'D';
// $letter_product_attribute_name = 'C';
/* $letter_product_qty = 'E';*/
if ($type == 1) {
$letter_product_qty = 'E';
$letter_product_ht = 'F';
$letter_product_total = 'G';
} else {
$letter_product_total_qty = 'E';
$letter_product_qty = 'F';
$letter_product_remaining_qty = 'G';
$letter_product_ht = 'H';
$letter_product_total = 'I';
}
}
}
// if($loaded === true)
// {
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
// echo file_get_contents($OrderForm->getFile());
// exit;
// }
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Antadis");
$objPHPExcel->getProperties()->setTitle( $title );
$objPHPExcel->getProperties()->setSubject( $title );
$objPHPExcel->getProperties()->setDescription( $title );
$objPHPExcel->getActiveSheet()->getStyle($firstColumn.'1:'.$lastColumn.'300')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle($firstColumn.'1:'.$lastColumn.'300')->getFont()->setName('calibri');
// $objPHPExcel->getFont()->setSize(10);
// $objPHPExcel->getFont()->setName('Colibri');
$arrayBorder = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
$arrayBorderAutour = array(
'borders' => array(
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
),
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
),
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
),
'top' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
)
)
);
$arrayConfig = array(
'PS_IT_OF_ORGANISATION_'.$lang_id,
'PS_IT_OF_ADDRESS_'.$lang_id,
'PS_IT_OF_ADDRESS_2_'.$lang_id,
'PS_IT_OF_CP_CITY_'.$lang_id,
'PS_IT_OF_NUMBER_PHONE_'.$lang_id
);
if(!Configuration::get('PS_IT_OF_ADDRESS_2_'.$lang_id))
unset($arrayConfig[array_search('PS_IT_OF_ADDRESS_2_'.$lang_id, $arrayConfig)]);
$activeSheet = $objPHPExcel->getActiveSheet();
$i = 1;
if($type == 1)
{
//LOGO
// $activeSheet->getRowDimension($i)->setRowHeight(80);
// $objDrawing = new PHPExcel_Worksheet_Drawing();
// $objDrawing->setWorksheet($activeSheet);
// $objDrawing->setName("name");
// $objDrawing->setDescription("Description");
// $objDrawing->setPath(_PS_IMG_DIR_.'logo.jpg');
// $objDrawing->setCoordinates($firstColumn.$i);
// $objDrawing->setOffsetX(1);
// $objDrawing->setOffsetY(5);
//ADRESSE DE LIVRAISON
// $activeSheet->SetCellValue($firstColumn.$i, 'Adresse de livraison : ');
// $activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
// $activeSheet->getStyle($firstColumn.$i)->getFont()->setBold(true);
// $activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(12);
// $activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
// $styleArray = array(
// 'font' => array(
// 'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE
// )
// );
// $activeSheet->getStyle($firstColumn.$i)->applyFromArray($styleArray);
// $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
$iOri = $i;
foreach($arrayConfig as $k => $cfg)
{
$activeSheet->SetCellValue($firstColumn.$i, Configuration::get($cfg) );
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(10);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->mergeCells($firstColumn.$i.':'.$letter_end_address.$i );
if($k == 0)
{
$activeSheet->getStyle($firstColumn.$i)->getFont()->setBold(true);
$i++;
$activeSheet->SetCellValue($firstColumn.$i, Configuration::get('PS_IT_OF_SLOGAN_'.$lang_id) );
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(10);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->mergeCells($firstColumn.$i.':'.$letter_end_address.$i );
$i++;
}
$i++;
}
$activeSheet->SetCellValue($firstColumn.$i, Configuration::get('PS_IT_OF_EMAIL_'.$lang_id) );
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(10);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->mergeCells($firstColumn.$i.':'.$letter_end_address.$i );
$i++;
$activeSheet->SetCellValue($firstColumn.$i, Configuration::get('PS_IT_OF_TVA_'.$lang_id) );
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(10);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->mergeCells($firstColumn.$i.':'.$letter_end_address.$i );
$i++;
//BON DE COMMANDE
$activeSheet->getCell($letter_bdc_title.$iOri)->setValue($lang['Purchase order'].' '.chr(13).' '.$category->name);
$activeSheet->getStyle($letter_bdc_title.$iOri)->getAlignment()->setWrapText(true);
$activeSheet->getStyle($letter_bdc_title.$iOri)->getFont()->setBold(true);
$activeSheet->getStyle($letter_bdc_title.$iOri)->getFont()->setSize(18);
$activeSheet->getStyle($letter_bdc_title.$iOri)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_bdc_title.$iOri)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->mergeCells($letter_bdc_title.$iOri.':'.$lastColumn.($i-1));
// $activeSheet->getStyle($firstColumn.($iOri).':'.$lastColumn.($i-1))->applyFromArray($arrayBorderAutour);
$i++;
//TEXTE
$activeSheet->SetCellValue($firstColumn.$i, $lang['The number below must be present on each document regarding this order, delivery note, invoice :']);
$activeSheet->mergeCells($firstColumn.$i.':'.$letter_end_address.$i );
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$activeSheet->getRowDimension($i)->setRowHeight(30);
$i++;
$activeSheet->SetCellValue($firstColumn.$i, $lang['PURCHASE ORDER NUMBER'] . ' : '. $OrderForm->id);
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(10);
// $activeSheet->getStyle($firstColumn.$i)->getFont()->getColor()->setRGB('FF0000');
$activeSheet->getStyle($firstColumn.$i)->getFont()->setBold(true);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$activeSheet->mergeCells($firstColumn.$i.':'.$letter_end_address.$i );
$i++;
//ADRESSE DE LIVRAISON BIS
$letter = 'D';
$activeSheet->SetCellValue($letter.$i, $lang['Delivery adress'].' :');
$activeSheet->getStyle($letter.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->getStyle($letter.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter.$i)->getFont()->setSize(12);
$activeSheet->getStyle($letter.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
// $styleArray = array(
// 'font' => array(
// 'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE
// )
// );
// $activeSheet->getStyle($letter.$i)->applyFromArray($styleArray);
$activeSheet->mergeCells($letter.$i.':'.$lastColumn.$i );
$i++;
$iOri = $i;
foreach($arrayConfig as $cfg)
{
$activeSheet->SetCellValue($letter.$i, Configuration::get($cfg) );
$activeSheet->getStyle($letter.$i)->getFont()->setSize(10);
$activeSheet->mergeCells($letter.$i.':'.$lastColumn.$i );
$activeSheet->getStyle($letter.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$i++;
}
$i++;
$activeSheet->SetCellValue($letter_date.$i, $lang['PURCHASE ORDER DATE'] );
$activeSheet->SetCellValue($letter_franco.$i, $lang['DELIVERY DUTY PAID'] );
$activeSheet->SetCellValue($letter_condition.$i, 'CONDITIONS' );
$activeSheet->getStyle($letter_date.$i.':'.$letter_condition.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_date.$i.':'.$letter_condition.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$activeSheet->getStyle($letter_date.$i.':'.$letter_condition.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i ++;
$activeSheet->SetCellValue($letter_date.$i, date('d/m/Y'));
$activeSheet->SetCellValue($letter_franco.$i, $lang['YES'] );
$activeSheet->SetCellValue($letter_condition.$i, 'N/A' );
$activeSheet->getStyle($letter_date.($i-1).':'.$letter_condition.$i)->applyFromArray($arrayBorderAutour);
$activeSheet->getStyle($letter_date.$i.':'.$letter_condition.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i ++;
if(isset(Sale::$otherVar))
{
$iOriSaleVar = $i;
$value = '';
/*$activeSheet->SetCellValue($firstColumn.$i, 'Fournisseur : ');
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->getStyle($firstColumn.$i)->getFont()->setBold(true);
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(13);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);*/
$p = 0;
foreach(Sale::$otherVar as $var)
if(!empty($sale->$var))
{
$value = $sale->$var;
$activeSheet->getCell($firstColumn.$i)->setValue($value);
if($var == 'supplier_name')
{
$activeSheet->getStyle($firstColumn.$i)->getFont()->setSize(14);
$activeSheet->getStyle($firstColumn.$i)->getFont()->setBold(true);
$activeSheet->getRowDimension($i)->setRowHeight(20);
}
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setWrapText(true);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->mergeCells($firstColumn.$i.':F'.$i);
$i++;
$p++;
}
if($p == 0)
{
$activeSheet->getCell($firstColumn.$i)->setValue('Aucune information fournisseur');
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setWrapText(true);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->mergeCells($firstColumn.$i.':F'.$i);
$i++;
}
$activeSheet->getStyle($firstColumn.($i-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($firstColumn.($i-1).':'.$lastColumn.($i-1))->applyFromArray($arrayBorderAutour);
if($iOriSaleVar != $i)
$activeSheet->mergeCells($firstColumn.$iOriSaleVar.':'.$firstColumn.($i-1) );
// if(!empty($value))
// {
// $activeSheet->getCell('C'.$i)->setValue($value);
// $activeSheet->getStyle('C'.$i)->getFont()->setSize(13);
// $activeSheet->getStyle('C'.$i)->getAlignment()->setWrapText(true);
// $activeSheet->getStyle('C'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
// $activeSheet->mergeCells('C'.$i.':F'.$i);
// $activeSheet->getRowDimension($i)->setRowHeight(90);
// $i++;
// }
}
$i ++;
}
if ($type == 4) {
$activeSheet->SetCellValue($firstColumn.$i, $title);
$i++;$i++;
$borders_style = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$activeSheet->SetCellValue($firstColumn.$i, $lang['Product ID']);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($firstColumn.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ean.$i, $lang['EAN']);
$activeSheet->getStyle($letter_product_ean.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_ean.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ref_interne.$i, $lang['Reference BB']);
$activeSheet->getStyle($letter_product_ref_interne.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_ref_interne.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_name.$i, $lang['Product Name']);
$activeSheet->getStyle($letter_product_name.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_name.$i)->applyFromArray($borders_style);
if($have_declinaison === true) {
$activeSheet->SetCellValue($letter_product_attribute_name.$i, $lang['Attribute']);
$activeSheet->getStyle($letter_product_attribute_name.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_attribute_name.$i)->applyFromArray($borders_style);
}
$activeSheet->setCellValue($letter_product_ht.$i, $lang['Unit Price']);
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_ht.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_M1.$i, 'M1');
$activeSheet->getStyle($letter_product_M1.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_M1.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_M1.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_multi.$i, 'Multi');
$activeSheet->getStyle($letter_product_multi.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_multi.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_multi.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_stock3btk.$i, 'Stock 3BTK');
$activeSheet->getStyle($letter_product_stock3btk.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_stock3btk.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_stock3btk.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_missing.$i, $lang['Missing']);
$activeSheet->getStyle($letter_product_missing.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_missing.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_missing.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_to_sent.$i, $lang['To Sent']);
$activeSheet->getStyle($letter_product_to_sent.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_to_sent.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_to_sent.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_to_stock.$i, $lang['To Stock']);
$activeSheet->getStyle($letter_product_to_stock.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_to_stock.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_to_stock.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_shipped.$i, $lang['Shipped Product']);
$activeSheet->getStyle($letter_product_shipped.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_shipped.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_shipped.$i)->applyFromArray($borders_style);
} else {
if ($type == 2) {
$activeSheet->SetCellValue($firstColumn.$i, $date_of_extract->getJour().' '.$date_of_extract->getNomMois().' '.$date_of_extract->getAnnee());
$i++;$i++;
}
$activeSheet->SetCellValue($firstColumn.$i, $lang['Product ID']);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->SetCellValue($letter_product_ean.$i, $lang['EAN']);
$activeSheet->getStyle($letter_product_ean.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->SetCellValue($letter_product_ref_interne.$i, $lang['Reference BB']);
$activeSheet->getStyle($letter_product_ref_interne.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//$activeSheet->getStyle('E'.$i)->getBorders()->setBold(true);
$activeSheet->SetCellValue($letter_product_name.$i, $lang['Product Name']);
$activeSheet->getStyle($letter_product_name.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
if($have_declinaison === true) {
$activeSheet->SetCellValue($letter_product_attribute_name.$i, $lang['Attribute']);
$activeSheet->getStyle($letter_product_attribute_name.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
$activeSheet->SetCellValue($letter_product_qty.$i, $lang['Quantity']);
$activeSheet->getStyle($letter_product_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_qty.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getStyle($letter_product_qty.$i)->getAlignment()->setWrapText(true);
if ($type == 2) {
$activeSheet->getStyle($letter_product_qty.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'rotation' => 0,
'startcolor' => array(
'rgb' => 'D9D9D9'
),
'endcolor' => array(
'argb' => 'D9D9D9D9'
)
)
);
$activeSheet->SetCellValue($letter_product_total_qty.$i, $lang['Total quantity']);
$activeSheet->getStyle($letter_product_total_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_total_qty.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->SetCellValue($letter_product_remaining_qty.$i, $lang['Remaining quantity']);
$activeSheet->getStyle($letter_product_remaining_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_remaining_qty.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
$activeSheet->setCellValue($letter_product_ht.$i, $lang['Unit Price']);
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->setCellValue($letter_product_total.$i, $lang['Total Amount']);
$activeSheet->getStyle($letter_product_total.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_total.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
$i++;
$iOri = $i;
//p(count($lines));
$count = 0;
if($type == 1 || $type == 2)
foreach($lines as $line)
foreach($line as $l)
$count += $l['total'];
switch($type)
{
case '4':
foreach($lines2 as $k => $lines_attributes)
{
$ref = array();
$decli = array();
foreach ($lines_attributes as $key => $row) {
// si le produit n'apparait pas dans une commande, on affiche pas la ligne
if (!isset($row['total_m']) || $row['total_m'] == 0) {
unset($lines_attributes[$key]);
continue;
}
$ref[$key] = $row['line']['product_reference'];
$decli[$key] = $row['line']['product_attribute_name_base'];
}
array_multisort($ref, SORT_ASC, $decli, SORT_ASC, $lines_attributes);
foreach($lines_attributes as $key => $values)
{
if (is_array($lines_attributes[$key+1])
|| $lines2[$k+1][0]['line']['product_reference'] == $values['line']['product_reference']) {
$borders_style = array(
'borders' => array(
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
} else {
$borders_style = array(
'borders' => array(
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
}
$id_product_attribute = $values['line']['product_attribute_id'];
$id_product = $values['line']['product_id'];
$current_product = $array_product[$id_product];
if($id_product_attribute != 0)
{
$combination = new Combination($id_product_attribute);
if($combination->wholesale_price == 0)
$wholesale_price = $current_product->wholesale_price;
else
$wholesale_price = $combination->wholesale_price;
}
else
{
$wholesale_price = $current_product->wholesale_price;
}
$activeSheet->SetCellValue($firstColumn.$i, $values['line']['product_supplier_reference']);
$activeSheet->getStyle($firstColumn.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
$activeSheet->getStyle($firstColumn.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ean.$i, $values['line']['product_ean13']);
$activeSheet->getStyle($letter_product_ean.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_NUMBER );
$activeSheet->getStyle($letter_product_ean.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ref_interne.$i, $values['line']['product_reference']);
$activeSheet->getStyle($letter_product_ref_interne.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_name.$i, $values['line']['product_name_base']);
$activeSheet->getStyle($letter_product_name.$i)->applyFromArray($borders_style);
if($have_declinaison === true) {
$activeSheet->SetCellValue($letter_product_attribute_name.$i, $values['line']['product_attribute_name_base']);
$activeSheet->getStyle($letter_product_attribute_name.$i)->applyFromArray($borders_style);
}
$activeSheet->setCellValue($letter_product_ht.$i, (float)$wholesale_price );
$activeSheet->getStyle($letter_product_ht.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$activeSheet->getStyle($letter_product_ht.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_multi.$i, (isset($values['multi']))?$values['multi']:0);
$activeSheet->getStyle($letter_product_multi.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_M1.$i, (isset($values['m1']))?$values['m1']:0);
$activeSheet->getStyle($letter_product_M1.$i)->applyFromArray($borders_style);
// colonnes vide - a remplir par 3BTK
$activeSheet->getStyle($letter_product_stock3btk.$i)->applyFromArray($borders_style);
$activeSheet->getStyle($letter_product_missing.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_to_sent.$i, '='.$letter_product_M1.$i.'+'.$letter_product_multi.$i.'-'.$letter_product_missing.$i);
$activeSheet->getStyle($letter_product_to_sent.$i)->applyFromArray($borders_style);
// colonnes vide - a remplir par 3BTK
$activeSheet->getStyle($letter_product_to_stock.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_shipped.$i, '='.$letter_product_M1.$i.'+'.$letter_product_multi.$i.'-'.$letter_product_stock3btk.$i.'-'.$letter_product_missing.$i.'+'.$letter_product_to_stock.$i);
$activeSheet->getStyle($letter_product_shipped.$i)->applyFromArray($borders_style);
$i++;
}
}
break;
case '2':
foreach($lines2 as $k => $lines_attributes)
{
foreach($lines_attributes as $key => $values)
{
if (
(isset($lines_attributes[$key+1]) && is_array($lines_attributes[$key+1]))
|| (isset($lines2[$k+1]) && $lines2[$k+1][0]['line']['product_reference'] == $values['line']['product_reference'])
) {
$borders_style = array(
'borders' => array(
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
} else {
$borders_style = array(
'borders' => array(
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
}
$id_product_attribute = $values['line']['product_attribute_id'];
$id_product = $values['line']['product_id'];
$current_product = $array_product[$id_product];
if($id_product_attribute != 0)
{
$combination = new Combination($id_product_attribute);
if($combination->wholesale_price == 0)
$wholesale_price = $current_product->wholesale_price;
else
$wholesale_price = $combination->wholesale_price;
}
else
{
$wholesale_price = $current_product->wholesale_price;
}
$activeSheet->SetCellValue($firstColumn.$i, $values['line']['product_supplier_reference']);
$activeSheet->getStyle($firstColumn.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
$activeSheet->getStyle($firstColumn.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ean.$i, $values['line']['product_ean13']);
$activeSheet->getStyle($letter_product_ean)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$activeSheet->getStyle($letter_product_ean.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$activeSheet->getStyle($letter_product_ean.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle($letter_product_ean.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ref_interne.$i, $values['line']['product_reference']);
$activeSheet->getStyle($letter_product_ref_interne.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_name.$i, $values['line']['product_name_base']);
$activeSheet->getStyle($letter_product_name.$i)->applyFromArray($borders_style);
if($have_declinaison === true) {
$activeSheet->SetCellValue($letter_product_attribute_name.$i, $values['line']['product_attribute_name_base']);
$activeSheet->getStyle($letter_product_attribute_name.$i)->applyFromArray($borders_style);
}
$activeSheet->SetCellValue($letter_product_qty.$i, $values['total']);
$activeSheet->getStyle($letter_product_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_qty.$i)->applyFromArray($borders_style);
$activeSheet->getStyle($letter_product_qty.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'rotation' => 0,
'startcolor' => array(
'rgb' => 'D9D9D9'
),
'endcolor' => array(
'argb' => 'D9D9D9D9'
)
)
);
$activeSheet->SetCellValue($letter_product_total_qty.$i, ($values['stock']+$values['total']));
$activeSheet->getStyle($letter_product_total_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_total_qty.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_remaining_qty.$i, $values['stock']);
$activeSheet->getStyle($letter_product_remaining_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_remaining_qty.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_ht.$i, (float)$wholesale_price );
$activeSheet->setCellValue($letter_product_total.$i, '='.$letter_product_qty.$i.'*'.$letter_product_ht.$i);
$activeSheet->getStyle($letter_product_ht.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$activeSheet->getStyle($letter_product_ht.$i)->applyFromArray($borders_style);
$activeSheet->getStyle($letter_product_total.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$activeSheet->getStyle($letter_product_total.$i)->applyFromArray($borders_style);
$i++;
}
}
break;
case '1':
//echo "<pre>";var_dump($lines2);echo "</pre>";die();
foreach($lines2 as $k => $lines_attributes)
{
foreach($lines_attributes as $key => $values)
{
if (is_array($lines_attributes[$key+1])
|| $lines2[$k+1][0]['line']['product_reference'] == $values['line']['product_reference']) {
$borders_style = array(
'borders' => array(
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
} else {
$borders_style = array(
'borders' => array(
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
}
$id_product_attribute = $values['line']['product_attribute_id'];
$id_product = $values['line']['product_id'];
$current_product = $array_product[$id_product];
if($id_product_attribute != 0)
{
$combination = new Combination($id_product_attribute);
if($combination->wholesale_price == 0)
$wholesale_price = $current_product->wholesale_price;
else
$wholesale_price = $combination->wholesale_price;
}
else
{
$wholesale_price = $current_product->wholesale_price;
}
$activeSheet->SetCellValue($firstColumn.$i, $values['line']['product_supplier_reference']);
$activeSheet->getStyle($firstColumn.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
$activeSheet->getStyle($firstColumn.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ean.$i, $values['line']['product_ean13']);
$activeSheet->getStyle($letter_product_ean)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$activeSheet->getStyle($letter_product_ean.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$activeSheet->getStyle($letter_product_ean.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle($letter_product_ean.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_ref_interne.$i, $values['line']['product_reference']);
$activeSheet->getStyle($letter_product_ref_interne.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_name.$i, $values['line']['product_name_base']);
$activeSheet->getStyle($letter_product_name.$i)->applyFromArray($borders_style);
if($have_declinaison === true) {
$activeSheet->SetCellValue($letter_product_attribute_name.$i, $values['line']['product_attribute_name_base']);
$activeSheet->getStyle($letter_product_attribute_name.$i)->applyFromArray($borders_style);
}
$activeSheet->SetCellValue($letter_product_qty.$i, $values['total']);
$activeSheet->getStyle($letter_product_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_qty.$i)->applyFromArray($borders_style);
$activeSheet->setCellValue($letter_product_ht.$i, (float)$wholesale_price );
$activeSheet->setCellValue($letter_product_total.$i, '='.$letter_product_qty.$i.'*'.$letter_product_ht.$i);
$activeSheet->getStyle($letter_product_ht.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$activeSheet->getStyle($letter_product_ht.$i)->applyFromArray($borders_style);
$activeSheet->getStyle($letter_product_total.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$activeSheet->getStyle($letter_product_total.$i)->applyFromArray($borders_style);
$i++;
}
}
break;
}
if ($type == 4) {
$borders_style = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
if($have_declinaison === true){
$activeSheet->SetCellValue($letter_product_attribute_name.$i, 'Total');
$activeSheet->getStyle($letter_product_attribute_name.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_attribute_name.$i)->applyFromArray($borders_style);
} else {
$activeSheet->SetCellValue($letter_product_name.$i, 'Total');
$activeSheet->getStyle($letter_product_name.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_name.$i)->applyFromArray($borders_style);
}
//$activeSheet->SetCellValue($letter_product_ht.$i, '=SUM('.$letter_product_ht.($iOri).':'.$letter_product_ht.($i-1).')');
//$activeSheet->getStyle($letter_product_ht.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
//$activeSheet->getStyle($letter_product_ht.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_ht.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_multi.$i, '=SUM('.$letter_product_multi.($iOri).':'.$letter_product_multi.($i-1).')');
$activeSheet->getStyle($letter_product_multi.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_multi.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_M1.$i, '=SUM('.$letter_product_M1.($iOri).':'.$letter_product_M1.($i-1).')');
$activeSheet->getStyle($letter_product_M1.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_M1.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_stock3btk.$i, '=SUM('.$letter_product_stock3btk.($iOri).':'.$letter_product_stock3btk.($i-1).')');
$activeSheet->getStyle($letter_product_stock3btk.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_stock3btk.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_missing.$i, '=SUM('.$letter_product_missing.($iOri).':'.$letter_product_missing.($i-1).')');
$activeSheet->getStyle($letter_product_missing.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_missing.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_to_sent.$i, '=SUM('.$letter_product_to_sent.($iOri).':'.$letter_product_to_sent.($i-1).')');
$activeSheet->getStyle($letter_product_to_sent.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_to_sent.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_to_stock.$i, '=SUM('.$letter_product_to_stock.($iOri).':'.$letter_product_to_stock.($i-1).')');
$activeSheet->getStyle($letter_product_to_stock.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_to_stock.$i)->applyFromArray($borders_style);
$activeSheet->SetCellValue($letter_product_shipped.$i, '=SUM('.$letter_product_shipped.($iOri).':'.$letter_product_shipped.($i-1).')');
$activeSheet->getStyle($letter_product_shipped.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_shipped.$i)->applyFromArray($borders_style);
$activeSheet->getColumnDimension($firstColumn)->setWidth(25);
$activeSheet->getColumnDimension($letter_product_ean)->setWidth(25);
$activeSheet->getColumnDimension($letter_product_ref_interne)->setWidth(25);
$activeSheet->getColumnDimension($letter_product_name)->setWidth(40);
if ($have_declinaison === true) {
$activeSheet->getColumnDimension($letter_product_attribute_name)->setWidth(20);
}
$activeSheet->getColumnDimension($letter_product_ht)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_multi)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_M1)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_stock3btk)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_missing)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_to_sent)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_to_stock)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_shipped)->setWidth(13);
$i++;
$activeSheet->setCellValue($firstColumn.$i, '');
} else {
$activeSheet->SetCellValue($firstColumn.$i, 'Total');
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle($firstColumn.$i)->getFont()->setBold(true);
$activeSheet->SetCellValue($letter_product_qty.$i, '=SUM('.$letter_product_qty.($iOri).':'.$letter_product_qty.($i-1).')');
$activeSheet->getStyle($letter_product_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_qty.$i)->getFont()->setBold(true);
if ($type == 2) {
$activeSheet->getStyle($letter_product_qty.$i)->getFill()->applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'rotation' => 0,
'startcolor' => array(
'rgb' => 'D9D9D9'
),
'endcolor' => array(
'argb' => 'D9D9D9D9'
)
)
);
$activeSheet->SetCellValue($letter_product_total_qty.$i, '=SUM('.$letter_product_total_qty.($iOri).':'.$letter_product_total_qty.($i-1).')');
$activeSheet->getStyle($letter_product_total_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_total_qty.$i)->getFont()->setBold(true);
$activeSheet->SetCellValue($letter_product_remaining_qty.$i, '=SUM('.$letter_product_remaining_qty.($iOri).':'.$letter_product_remaining_qty.($i-1).')');
$activeSheet->getStyle($letter_product_remaining_qty.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle($letter_product_remaining_qty.$i)->getFont()->setBold(true);
}
$activeSheet->getStyle($firstColumn.($iOri-1).':'.$lastColumn.($iOri-1))->applyFromArray($arrayBorder);
$activeSheet->getStyle($firstColumn.$i.':'.$lastColumn.$i)->applyFromArray($arrayBorder);
$i++;
$activeSheet->setCellValue($letter_product_ht.$i, $lang['Total Amount']);
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if($type == 1)
$activeSheet->getStyle($letter_product_ht.$i)->getFont()->setBold(true);
$activeSheet->setCellValue($letter_product_total.$i, '=SUM('.$letter_product_total.($iOri).':'.$letter_product_total.($i-2).')');
$activeSheet->getStyle($letter_product_total.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
if($lang_id != 'en')
{
$i++;
$activeSheet->setCellValue($letter_product_ht.$i, 'TVA '.$TVA);
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if($type == 1)
$activeSheet->getStyle($letter_product_ht.$i)->getFont()->setBold(true);
$activeSheet->setCellValue($letter_product_total.$i, '='.$letter_product_total.($i-1).'*'.($TVA/100) );
$activeSheet->getStyle($letter_product_total.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$i++;
$activeSheet->setCellValue($letter_product_ht.$i, 'TOTAL TTC');
$activeSheet->getStyle($letter_product_ht.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$activeSheet->getStyle($letter_product_ht.$i)->getFont()->setBold(true);
$activeSheet->setCellValue($letter_product_total.$i, '='.$letter_product_total.($i-1).'+'.$letter_product_total.($i-2) );
$activeSheet->getStyle($letter_product_total.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );
$activeSheet->getStyle($letter_product_total.$i)->getFont()->setBold(true);
$activeSheet->getStyle($letter_product_ht.($i-3).':'.$lastColumn.$i)->applyFromArray($arrayBorder);
}
else
{
$activeSheet->getStyle($letter_product_ht.($i-1).':'.$lastColumn.$i)->applyFromArray($arrayBorder);
}
if($type == 1)
{
$value = Configuration::get('PS_IT_OF_BAS_PAGE_'.$lang_id);
if($value != '')
{
$i++;
$value = str_replace('<br />', ' ' , nl2br($value));
$activeSheet->setCellValue($firstColumn.$i, $value);
$activeSheet->mergeCells($firstColumn.$i.':'.$lastColumn.$i );
$activeSheet->getRowDimension($i)->setRowHeight(80);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->getStyle($firstColumn.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setWrapText(true);
}
$value = Configuration::get('PS_IT_OF_BAS_PAGE_BIS_'.$lang_id);
if($value != '')
{
$i++;
$value = str_replace('<br />', ' ' , nl2br($value));
$activeSheet->setCellValue($firstColumn.$i, $value);
$activeSheet->mergeCells($firstColumn.$i.':'.$lastColumn.$i );
$activeSheet->getRowDimension($i)->setRowHeight(80);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->getStyle($firstColumn.$i)->getAlignment()->setWrapText(true);
$activeSheet->getStyle($firstColumn.$i)->getFont()->getColor()->setRGB('FF0000');
$activeSheet->getStyle($firstColumn.$i)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
}
}
$activeSheet->getColumnDimension($firstColumn)->setWidth(25);
$activeSheet->getColumnDimension($letter_product_ean)->setWidth(25);
$activeSheet->getColumnDimension($letter_product_ref_interne)->setWidth(25);
$activeSheet->getColumnDimension($letter_product_qty)->setWidth(18);
$activeSheet->getColumnDimension($letter_product_name)->setWidth(40);
if($have_declinaison === true)
$activeSheet->getColumnDimension($letter_product_attribute_name)->setWidth(20);
$activeSheet->getColumnDimension($letter_product_qty)->setWidth(13);
if ($type == 2) {
$activeSheet->getColumnDimension($letter_product_total_qty)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_remaining_qty)->setWidth(13);
}
$activeSheet->getColumnDimension($letter_product_ht)->setWidth(13);
$activeSheet->getColumnDimension($letter_product_total)->setWidth(13);
$i++;
$activeSheet->setCellValue($firstColumn.$i, '');
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
if($type == 1 && Validate::isLoadedObject($OrderForm))
{
$objWriter->save($OrderForm->getFile());
}
else
{
$objWriter->save(dirname(__FILE__).'/extracts/sales_logistique/'.$title.'.xlsx');
}
exit;
}
Reference in New Issue View Git Blame Copy Permalink