/**
* Retrieves the product entries for the current order
*
* @param int $id_lang Optional Id Lang - Uses Context::language::id by default
* @return array
*/
public function getEntries($id_lang = null)
{
if ($id_lang == null) {
$id_lang = Context::getContext()->language->id;
}
// build query
$query = new DbQuery();
$query->select('
s.*,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(agl.name, \' - \', al.name SEPARATOR \', \')), pl.name) as name_displayed');
$query->from('supply_order_detail', 's');
$query->innerjoin('product_lang', 'pl', 'pl.id_product = s.id_product AND pl.id_lang = ' . $id_lang);
$query->leftjoin('product', 'p', 'p.id_product = s.id_product');
$query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = s.id_product_attribute');
$query->leftjoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute');
$query->leftjoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . $id_lang);
$query->leftjoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . $id_lang);
$query->where('s.id_supply_order = ' . (int) $this->id);
$query->groupBy('s.id_supply_order_detail');
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
}
protected function renderCatalog()
{
$stckmgtfr = ERP_STCKMGTFR;
if (Tools::isSubmit('export_catalog')) {
//OUPUT HEADERS
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private', false);
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=catalog_' . date('Y-m-d_His') . '.csv;');
header('Content-Transfer-Encoding: binary');
$id_warehouse = (int) Tools::getValue('id_warehouse');
$area = (int) Tools::getValue('area');
$subarea = (int) Tools::getValue('subarea');
// GET COMBINATIONS
$combination = new DbQuery();
$select_combination = 'pa.id_product,
pa.id_product_attribute,
pa.reference,
pa.ean13,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
p.price as price_product,
pa.price as price_attribute,
p.id_tax_rules_group,
p.id_manufacturer,
cl.name as category,
CASE pa.wholesale_price WHEN 0.000000 THEN p.wholesale_price ELSE pa.wholesale_price END as wholesale_price,
IFNULL( pa.weight, p.weight) as weight,
pl.description,
pl.description_short ';
// get product and product attribute of selected warehouse
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$select_combination .= ', wpl.location, z.name as area, sz.name as subarea';
$combination->innerjoin('warehouse_product_location', 'wpl', 'wpl.id_warehouse = ' . $id_warehouse . ' ' . 'AND pa.id_product = wpl.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0)');
$combination->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$combination->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$combination->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
// filter on area
if ($area != null && $subarea == null) {
$combination->where('z.id_erpip_zone = ' . (int) $area);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
$combination->where('z.id_erpip_zone = ' . (int) $area);
$combination->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$combination->select($select_combination);
$combination->from('product_attribute', 'pa');
$combination->innerjoin('product', 'p', 'pa.id_product = p.id_product');
$combination->innerjoin('product_lang', 'pl', 'pa.id_product = pl.id_product');
$combination->innerjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$combination->innerjoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute');
$combination->innerjoin('attribute_lang', 'al', 'al.id_attribute = pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id);
$combination->innerjoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id);
$combination->innerjoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang =' . (int) $this->context->language->id);
$combination->groupBy('pa.id_product, pa.id_product_attribute');
if ($this->controller_status == STATUS1) {
$combination->limit($stckmgtfr);
$this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $order_free_limit);
}
$combinations = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($combination);
// list of product ids
$ids = array();
foreach ($combinations as $combination) {
$ids[] = (int) $combination['id_product'];
}
$ids = array_unique($ids);
// GET PRODUCT WITHOUT COMBINATIONS
$product = new DbQuery();
// Base query
$select_product = 'p.id_product,
p.reference,
p.ean13,
pl.name as name,
p.weight,
pl.description,
pl.description_short,
p.price as price_product,
p.id_tax_rules_group,
p.id_manufacturer,
cl.name as category,
p.wholesale_price as wholesale_price';
// warehouse query
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$select_product .= ', wpl.location, z.name as area, sz.name as subarea';
$product->innerjoin('warehouse_product_location', 'wpl', 'wpl.id_warehouse = ' . $id_warehouse . ' AND p.id_product = wpl.id_product AND wpl.id_product_attribute = 0');
$product->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$product->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$product->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
// filter on area
if ($area != null && $subarea == null) {
$product->where('z.id_erpip_zone = ' . (int) $area);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
$product->where('z.id_erpip_zone = ' . (int) $area);
$product->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$product->select($select_product);
$product->from('product', 'p');
$product->innerjoin('product_lang', 'pl', 'p.id_product = pl.id_product');
$product->innerjoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang =' . (int) $this->context->language->id);
// if we have attributes we filter for not having a product already listed with attributes
if (count($ids) > 0) {
$product->where('p.id_product NOT IN (' . pSQL(implode(',', array_map('intval', $ids))) . ') ');
}
$product->groupBy('p.id_product');
if ($this->controller_status == STATUS1) {
$product->limit($stckmgtfr);
$this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $order_free_limit);
}
$products = Db::getInstance()->executeS($product);
// merge product with product attribute
$query = array_merge($products, $combinations);
if ($this->controller_status == STATUS1) {
$query = array_splice($query, 0, $stckmgtfr);
}
$nb_items = count($query);
for ($i = 0; $i < $nb_items; ++$i) {
$item =& $query[$i];
// gets stock manager
$manager = StockManagerFactory::getManager();
// id_product_attribute pour un produit sans déclinaisons
if (!isset($item['id_product_attribute'])) {
$item['id_product_attribute'] = 0;
}
// gets quantities and valuation
$stock = new DbQuery();
$stock->select('SUM(physical_quantity) as physical_quantity');
$stock->select('SUM(usable_quantity) as usable_quantity');
$stock->select('SUM(price_te * physical_quantity) as valuation');
$stock->from('stock');
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$stock->where('id_product =' . (int) $item['id_product'] . ' AND id_product_attribute = ' . (int) $item['id_product_attribute'] . ' AND id_warehouse = ' . (int) $id_warehouse);
} else {
$stock->where('id_product =' . (int) $item['id_product'] . ' AND id_product_attribute = ' . (int) $item['id_product_attribute']);
}
$res_stock = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($stock);
$item['physical_quantity'] = $res_stock['physical_quantity'];
// real quantity
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$item['real_quantity'] = $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], $id_warehouse, true);
} else {
$item['real_quantity'] = $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], null, true);
}
// price tax include and tax
$price = new DbQuery();
$price->select('rate');
$price->from('tax', 't');
$price->innerjoin('tax_rule', 'tr', 'tr.id_tax = t.id_tax');
$price->where('t.id_tax = ' . (int) $item['id_tax_rules_group']);
$price->where('tr.id_country = ' . (int) $this->context->country->id);
$res_price = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($price);
$item['rate'] = $res_price['rate'];
// if we are in product
if (!isset($item['price_attribute'])) {
$item['price_ttc'] = $item['price_product'] * ($res_price['rate'] / 100) + $item['price_product'];
} else {
$price = $item['price_product'] + $item['price_attribute'];
$item['price_ttc'] = $price * ($res_price['rate'] / 100) + $price;
}
// get manufacturer
$item['manufacturer'] = Manufacturer::getNameById($item['id_manufacturer']);
// get image product id
$id_image = Product::getCover((int) $item['id_product']);
// there is an image ?
if ($id_image != false) {
$image = new Image($id_image['id_image']);
$item['url_image'] = _PS_BASE_URL_ . _THEME_PROD_DIR_ . $image->getExistingImgPath() . ".jpg";
} else {
$item['url_image'] = $this->l('No image');
}
}
// BASE CSV HEADER
$header = array($this->l('PRODUCT_ID_'), $this->l('PRODUCT_ATTRIBUTE_ID'), $this->l('SKU'), $this->l('EAN13'), $this->l('MANUFACTURER'), $this->l('CATEGORY'), $this->l('PRODUCT_NAME'), $this->l('PRODUCT_WEIGHT'), $this->l('DESCRIPTION'), $this->l('DESCRIPTION_SHORT'), $this->l('URL_IMAGE'), $this->l('PHYSICAL_QTE'), $this->l('REAL_QTY'), $this->l('PURCHASE_PRICE'), $this->l('PRICES_TAX_EXCL'), $this->l('PRICES_TAX_INCL'), $this->l('VAT_RATE'));
// CSV WITH WAREHOUSE LOCATION
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
if ($area != null && $subarea == null) {
array_push($header, $this->l('AREA'));
}
// filter on area and sub area
if ($area != null && $subarea != null) {
array_push($header, $this->l('AREA'), $this->l('SUBAREA'));
}
array_push($header, $this->l('LOCATION'));
}
// Print header
echo implode(';', $header) . "\r\n";
// generate CSV file
foreach ($query as $product) {
if (!isset($product['price_attribute'])) {
$product['price_attribute'] = 0;
}
$content = array($product['id_product'], $product['id_product_attribute'], $product['reference'], $product['ean13'], $product['manufacturer'], self::transformText($product['category']), self::transformText($product['name']), $product['weight'], self::transformText($product['description']), self::transformText($product['description_short']), self::transformText($product['url_image']), $product['physical_quantity'], $product['real_quantity'], round($product['wholesale_price'], 2), round($product['price_product'] + $product['price_attribute'], 2), round($product['price_ttc'], 2), round($product['rate'], 2));
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
if ($area != null && $subarea == null) {
array_push($content, $product['area']);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
array_push($content, $product['area'], $product['subarea']);
}
array_push($content, $product['location']);
}
echo implode(';', $content) . "\r\n";
}
echo sprintf($this->l('You are using the free version of 1-Click ERP which limits the export to %d products'), $stckmgtfr);
die;
}
}