public function renderList() { $this->toolbar_title = $this->l('Inventory reports'); if (Tools::isSubmit('id_container') && Tools::getValue('id_container') > 0) { self::$currentIndex .= '&id_container=' . (int) Tools::getValue('id_container'); } // Get id container. if noone selected, take the first one if (($id_container = $this->getCurrentValue('id_container')) == false) { $id_container = (int) ErpInventory::getFirstId(); $this->tpl_list_vars['id_container'] = $id_container; } // get total stock gap of inventory $total_stock_gap = InventoryProduct::getTotalStockGap($id_container); $this->tpl_list_vars['total_gap'] = Tools::displayPrice($total_stock_gap); // Query $this->_select = 'p.id_product, IF(pa.id_product_attribute, pa.reference, p.reference) as reference, IFNULL(pa.id_product_attribute, 0) as id_product_attribute, IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name, p.id_product, IFNULL(pa.id_product_attribute, 0) as id_product_attribute, w.name as warehouse, a.qte_before, a.qte_after, smrl.name as reason, (qte_after - qte_before) as gap, ( SELECT ps.product_supplier_reference FROM ' . _DB_PREFIX_ . 'product_supplier ps WHERE ps.id_product = a.id_product AND ps.id_product_attribute = a.id_product_attribute LIMIT 1 )as first_supplier_ref'; $this->_join = 'INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (a.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->context->language->id . ') INNER JOIN ' . _DB_PREFIX_ . 'product p ON a.id_product = p.id_product INNER JOIN ' . _DB_PREFIX_ . 'stock_mvt_reason_lang smrl ON (a.id_mvt_reason = smrl.id_stock_mvt_reason AND smrl.id_lang = ' . (int) $this->context->language->id . ') INNER JOIN ' . _DB_PREFIX_ . 'stock_mvt_reason smr ON a.id_mvt_reason = smr.id_stock_mvt_reason INNER JOIN ' . _DB_PREFIX_ . 'warehouse w ON w.id_warehouse = a.id_warehouse INNER JOIN ' . _DB_PREFIX_ . 'product_attribute pa ON a.id_product_attribute= pa.id_product_attribute INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute INNER JOIN ' . _DB_PREFIX_ . 'attribute atr ON atr.id_attribute= pac.id_attribute INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON (al.id_attribute= pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id . ') INNER JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON (agl.id_attribute_group= atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id . ') INNER JOIN ' . _DB_PREFIX_ . 'erpip_inventory i ON a.id_erpip_inventory = i.id_erpip_inventory'; $this->_where = 'AND i.id_erpip_inventory = ' . $id_container; $this->_order = 'a.id_erpip_inventory_product DESC LIMIT 10'; $this->_group = 'GROUP BY a.id_product_attribute'; // Send values to view $this->tpl_list_vars['containers'] = ErpInventory::getContainers(); $list = parent::renderList(); return $list; }
public function renderList() { $this->toolbar_title = $this->l('Products list'); if (Tools::isSubmit('id_display')) { self::$currentIndex .= '&id_display=' . (int) Tools::getValue('id_display'); } if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != '-1') { self::$currentIndex .= '&id_warehouse=' . (int) Tools::getValue('id_warehouse'); } if (Tools::isSubmit('areaFilter')) { self::$currentIndex .= '&areaFilter=' . Tools::getValue('areaFilter'); } if (Tools::isSubmit('subareaFilter')) { self::$currentIndex .= '&subareaFilter=' . Tools::getValue('subareaFilter'); } if (Tools::isSubmit('id_category') && Tools::getValue('id_category') != '-1') { self::$currentIndex .= '&id_category=' . (int) Tools::getValue('id_category'); } if (Tools::isSubmit('id_supplier') && Tools::getValue('id_supplier') != '-1') { self::$currentIndex .= '&id_supplier=' . (int) Tools::getValue('id_supplier'); } if (Tools::isSubmit('id_manufacturer') && Tools::getValue('id_manufacturer') != '-1') { self::$currentIndex .= '&id_manufacturer=' . (int) Tools::getValue('id_manufacturer'); } // Get display type $id_display = $this->getCurrentValue('id_display'); // qubquery : return the first provider reference for principal product $this->_select = ' cl.name as category_name, i.id_image, a.id_product as mvt_reason, area.name as area_name, sub_area.name as sub_area_name, wpl.location as location, a.id_product as new_quantity, ( SELECT ps.product_supplier_reference FROM ' . _DB_PREFIX_ . 'product_supplier ps WHERE ps.id_product = a.id_product AND ps.id_product_attribute = 0 LIMIT 1 )as first_supplier_ref, ( EXISTS(SELECT pa.id_product FROM ' . _DB_PREFIX_ . 'product_attribute pa WHERE pa.id_product = a.id_product LIMIT 1) ) as have_attribute, '; $this->_join = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (pa.id_product = a.id_product)' . 'INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (a.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->context->language->id . ') INNER JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (a.id_category_default = cl.id_category AND cl.id_lang = ' . (int) $this->context->language->id . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON a.id_product = i.id_product '; $this->_join .= ' LEFT JOIN ' . _DB_PREFIX_ . 'warehouse_product_location wpl ON (wpl.id_product = a.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0))'; $this->_join .= ' LEFT JOIN ' . _DB_PREFIX_ . 'erpip_warehouse_product_location ewpl ON wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location '; $this->_join .= ' LEFT JOIN ' . _DB_PREFIX_ . 'erpip_zone area ON area.id_erpip_zone = ewpl.id_zone_parent '; $this->_join .= ' LEFT JOIN ' . _DB_PREFIX_ . 'erpip_zone sub_area ON sub_area.id_erpip_zone = ewpl.id_zone '; // Render 1 : mix products and declination to sort by area if ($id_display == 1) { $this->_select .= 'IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as product_name, IFNULL((CONCAT(a.id_product, ";", pa.id_product_attribute)), a.id_product) as id_product,'; $this->_join .= ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = pa.id_product_attribute) LEFT JOIN `' . _DB_PREFIX_ . 'attribute` atr ON (atr.id_attribute = pac.id_attribute) LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (al.id_attribute = pac.id_attribute AND al.id_lang = ' . (int) $this->context->language->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) $this->context->language->id . ') '; } else { $this->_select .= 'pl.name as product_name,'; } $this->tpl_list_vars['advanced_stock_token'] = $this->advanced_stock_token; // FILTERS // Init filters variables $this->tpl_list_vars['id_category'] = -1; $this->tpl_list_vars['id_supplier'] = -1; $this->tpl_list_vars['id_manufacturer'] = -1; $this->tpl_list_vars['id_warehouse'] = -1; $this->tpl_list_vars['areaFilter'] = -1; $this->tpl_list_vars['subareaFilter'] = -1; $this->tpl_list_vars['id_display'] = 0; // Add additional filters $this->tpl_list_vars['warehouses'] = Warehouse::getWarehouses(); $this->tpl_list_vars['categories'] = Category::getSimpleCategories((int) $this->context->language->id); $this->tpl_list_vars['suppliers'] = Supplier::getSuppliers(); $this->tpl_list_vars['manufacturers'] = Manufacturer::getManufacturers(); $this->tpl_list_vars['controller_status'] = $this->controller_status; // Get inventory containers $this->tpl_list_vars['containers'] = ErpInventory::getContainers(); // get conf stock gap $this->tpl_list_vars['gap_stock'] = Configuration::getGlobalValue('ERP_GAP_STOCK'); require_once _PS_MODULE_DIR_ . 'erpillicopresta/models/ErpFeature.php'; $this->tpl_list_vars['erp_feature'] = ErpFeature::getFeaturesWithToken($this->context->language->iso_code); $this->tpl_list_vars['template_path'] = $this->template_path; // get default inventory reasons if ($this->context->language->iso_code == 'fr') { $this->tpl_list_vars['reason_increase'] = ErpStockMvtReason::existsByName('Augmentation d\'inventaire'); $this->tpl_list_vars['reason_decrease'] = ErpStockMvtReason::existsByName('Diminution d\'inventaire'); } else { $this->tpl_list_vars['reason_increase'] = ErpStockMvtReason::existsByName('Increase of inventory'); $this->tpl_list_vars['reason_decrease'] = ErpStockMvtReason::existsByName('Decrease of inventory'); } // specify advanced stock manager or not if ($this->advanced_stock_management) { // Get the selected warehouse, if there's not, get the first one if (($id_warehouse = $this->getCurrentValue('id_warehouse')) == false) { $id_warehouse = $this->getCookie('id_warehouse'); $this->tpl_list_vars['id_warehouse'] = $id_warehouse; } // filters warehouse location $area = $this->getCurrentValue('areaFilter'); $subarea = $this->getCurrentValue('subareaFilter'); $this->tpl_list_vars['areas'] = ErpZone::getZonesName($id_warehouse); $this->tpl_list_vars['sub_areas'] = $area ? ErpZone::getZonesName($id_warehouse, 'sub_area', $area) : array(); // if an area AND an under area are specified, we filter the area and the under area for the specified area if ($area != false && $subarea != false) { $this->_where .= ' AND wpl.id_warehouse = ' . (int) $id_warehouse . ' AND area.id_erpip_zone = "' . (int) $area . '" AND sub_area.id_erpip_zone = ' . (int) $subarea; $this->_group = 'GROUP BY a.id_product'; } elseif ($area != false) { $this->_where .= ' AND wpl.id_warehouse=' . (int) $id_warehouse . ' AND area.id_erpip_zone = ' . (int) $area; //$this->_where .= ' AND area.id_erpip_zone = '.(int)$area; if ($id_display == 1) { $this->_group = 'GROUP BY a.id_product, pa.id_product_attribute'; } else { $this->_group = 'GROUP BY a.id_product'; } } else { $this->_where .= ' AND wpl.id_warehouse = ' . (int) $id_warehouse; if ($id_display == 1) { $this->_group = 'GROUP BY a.id_product, pa.id_product_attribute'; } else { $this->_group = 'GROUP BY a.id_product'; } } } else { if ($id_display == 1) { $this->_group = 'GROUP BY a.id_product, pa.id_product_attribute'; } else { $this->_group = 'GROUP BY a.id_product'; } } // filter the query with applied filters //category filter if (($id_category = $this->getCurrentValue('id_category')) != false) { $this->_where .= ' AND a.id_product IN ( SELECT cp.id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE cp.id_category = ' . (int) $id_category . ' )'; } // provider filter if (($id_supplier = $this->getCurrentValue('id_supplier')) != false) { $this->_where .= ' AND a.id_product IN ( SELECT ps.id_product FROM ' . _DB_PREFIX_ . 'product_supplier ps WHERE ps.id_supplier = ' . (int) $id_supplier . ' )'; } // Brand filter if (($id_manufacturer = $this->getCurrentValue('id_manufacturer')) != false) { $this->_where .= ' AND a.id_manufacturer = ' . (int) $id_manufacturer; } $this->displayInformation($this->l('Be careful, if you are using advanced [respectively simple] stock management, only products using advanced [respectively simple] stock management will be exported.')); $this->displayInformation($this->l('In advanced sotck managment, products that are not stocked in a warehouse will not appear.')); // Show information or confirm message / error at the end of the inventory /*switch(Tools::getValue('submitFilterproduct')) { case 0: $this->displayInformation($this->l('New inventory')); break; case 1: $this->confirmations[] = $this->l('Inventory completed'); break; case 2: $this->errors[] = Tools::displayError('There has been a problem while handling products'); break; default: $this->displayInformation($this->l('New inventory')); break; }*/ // Add plugin simple tooltip $this->addJqueryPlugin('cluetip', _MODULE_DIR_ . 'erpillicopresta/js/cluetip/'); // add jquery dialog $this->addJqueryUI('ui.dialog'); // add plugin validity $this->addJqueryPlugin('validity.min', _MODULE_DIR_ . 'erpillicopresta/js/validity/'); // Load JS $this->addJS(_MODULE_DIR_ . 'erpillicopresta/js/inventory_tools.js'); $this->addJS(_MODULE_DIR_ . 'erpillicopresta/js/inventory.js'); // Load CSS $this->addCSS(_MODULE_DIR_ . 'erpillicopresta/css/jquery.validity.css'); $this->addCSS(_MODULE_DIR_ . 'erpillicopresta/css/jquery.cluetip.css'); $list = parent::renderList(); return $list; }