PHP DbQuery::leftjoin方法代码示例

 /**
  * Same as StockMvtReason::getStockMvtReasons(), ignoring a specific lists of ids
  *
  * @since 1.5.0
  * @param int $id_lang
  * @param array $ids_ignore
  * @param int $sign optional
  */
 public static function getStockMvtReasonsWithFilter($id_lang, $ids_ignore, $sign = null)
 {
     $query = new DbQuery();
     $query->select('smrl.name, smr.id_stock_mvt_reason, smr.sign');
     $query->from('stock_mvt_reason', 'smr');
     $query->leftjoin('stock_mvt_reason_lang', 'smrl', 'smr.id_stock_mvt_reason = smrl.id_stock_mvt_reason AND smrl.id_lang=' . (int) $id_lang);
     $query->where('smr.deleted = 0');
     if ($sign != null) {
         $query->where('smr.sign = ' . (int) $sign);
     }
     if (count($ids_ignore)) {
         $ids_ignore = array_map('intval', $ids_ignore);
         $query->where('smr.id_stock_mvt_reason NOT IN(' . implode(', ', $ids_ignore) . ')');
     }
     return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
 }

    /**
     * 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);
    }

    /**
     * @see StockManagerInterface::getProductRealQuantities()
     */
    public function getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false)
    {
        if (!is_null($ids_warehouse)) {
            // in case $ids_warehouse is not an array
            if (!is_array($ids_warehouse)) {
                $ids_warehouse = array($ids_warehouse);
            }
            // casts for security reason
            $ids_warehouse = array_map('intval', $ids_warehouse);
        }
        // Gets client_orders_qty
        $query = new DbQuery();
        $query->select('SUM(od.product_quantity) + SUM(od.product_quantity_refunded)');
        $query->from('order_detail', 'od');
        $query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
        $query->where('od.product_id = ' . (int) $id_product);
        if (0 != $id_product_attribute) {
            $query->where('od.product_attribute_id = ' . (int) $id_product_attribute);
        }
        $query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.date_add = o.date_upd');
        $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
        $query->where('os.shipped != 1');
        $query->where('o.valid = 1 OR (os.id_order_state != ' . (int) Configuration::get('PS_OS_ERROR') . '
					   AND os.id_order_state != ' . (int) Configuration::get('PS_OS_CANCELED') . ')');
        //if (count($ids_warehouse))
        //$query->where('od.id_warehouse IN('.implode(', ', $ids_warehouse).')');
        $client_orders_qty = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query);
        // Gets supply_orders_qty
        $query = new DbQuery();
        $query->select('sod.quantity_expected, sod.quantity_received');
        $query->from('supply_order', 'so');
        $query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order = so.id_supply_order');
        $query->leftjoin('supply_order_state', 'sos', 'sos.id_supply_order_state = so.id_supply_order_state');
        $query->where('sos.pending_receipt = 1');
        $query->where('sod.id_product = ' . (int) $id_product . ' AND sod.id_product_attribute = ' . (int) $id_product_attribute);
        if (!is_null($ids_warehouse) && count($ids_warehouse)) {
            $query->where('so.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
        }
        $supply_orders_qties = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        $supply_orders_qty = 0;
        foreach ($supply_orders_qties as $qty) {
            if ($qty['quantity_expected'] > $qty['quantity_received']) {
                $supply_orders_qty += $qty['quantity_expected'] - $qty['quantity_received'];
            }
        }
        // Gets {physical OR usable}_qty
        $qty = $this->getProductPhysicalQuantities($id_product, $id_product_attribute, $ids_warehouse, $usable);
        //real qty = actual qty in stock - current client orders + current supply orders
        return $qty - $client_orders_qty + $supply_orders_qty;
    }

 /**
  * Gets the list of supply order statuses
  *
  * @param array $ids Optional Do not include these ids in the result
  * @param int $id_lang Optional
  * @return array
  */
 public static function getStates($ids = null, $id_lang = null)
 {
     if ($id_lang == null) {
         $id_lang = Context::getContext()->language->id;
     }
     if ($ids && !is_array($ids)) {
         $ids = array();
     }
     $query = new DbQuery();
     $query->select('sl.name, s.id_supply_order_state');
     $query->from('supply_order_state', 's');
     $query->leftjoin('supply_order_state_lang', 'sl', 's.id_supply_order_state = sl.id_supply_order_state AND sl.id_lang=' . (int) $id_lang);
     if ($ids) {
         $query->where('s.id_supply_order_state NOT IN(' . implode(',', array_map('intval', $ids)) . ')');
     }
     $query->orderBy('sl.name ASC');
     return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
 }

 public function renderCSV()
 {
     if (Tools::isSubmit('export_csv')) {
         // header
         header('Content-type: text/csv; charset=utf-8');
         header('Cache-Control: no-store, no-cache');
         header('Content-disposition: attachment; filename="suppliers.csv"');
         // write headers column
         $keys = array('Name', 'Email', 'Company', 'Firstname', 'Lastname', 'Address 1', 'Address 2', 'Post code', 'City', 'Phone', 'GSM', 'Fax', 'Franco amount', 'Discount amount', 'Escompte', 'Delivery time', 'Account number accounting', 'Adding date', 'Updating date', 'activate');
         echo sprintf("%s\n", implode(';', $keys));
         $query = null;
         $query = new DbQuery();
         $query->select('s.*, erpips.*, a.company, a.firstname, a.lastname, a.address1, a.address2, a.postcode, a.city, a.phone, a.phone_mobile');
         $query->from('supplier', 's');
         $query->leftjoin('erpip_supplier', 'erpips', 'erpips.id_supplier = s.id_supplier');
         $query->leftjoin('address', 'a', 'a.id_supplier = s.id_supplier');
         // Execute query
         $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         // write datas
         foreach ($res as $supplier) {
             $content_csv = array(self::transformText($supplier['name']), $supplier['email'], self::transformText($supplier['company']), self::transformText($supplier['firstname']), self::transformText($supplier['lastname']), self::transformText($supplier['address1']), self::transformText($supplier['address2']), $supplier['postcode'], $supplier['city'], $supplier['phone'], $supplier['phone_mobile'], $supplier['fax'], $supplier['franco_amount'], $supplier['discount_amount'], $supplier['escompte'], $supplier['delivery_time'], $supplier['account_number_accounting'], $supplier['date_add'], $supplier['date_upd'], $supplier['active'], PHP_EOL);
             echo implode(';', $content_csv);
         }
         die;
     }
 }

    /**
     * @see StockManagerInterface::getProductRealQuantities()
     */
    public function getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false)
    {
        if (!is_null($ids_warehouse)) {
            // in case $ids_warehouse is not an array
            if (!is_array($ids_warehouse)) {
                $ids_warehouse = array($ids_warehouse);
            }
            // casts for security reason
            $ids_warehouse = array_map('intval', $ids_warehouse);
        }
        $client_orders_qty = 0;
        // check if product is present in a pack
        if (!Pack::isPack($id_product) && ($in_pack = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT id_product_pack, quantity FROM ' . _DB_PREFIX_ . 'pack
			WHERE id_product_item = ' . (int) $id_product . '
			AND id_product_attribute_item = ' . ($id_product_attribute ? (int) $id_product_attribute : '0')))) {
            foreach ($in_pack as $value) {
                if (Validate::isLoadedObject($product = new Product((int) $value['id_product_pack'])) && ($product->pack_stock_type == 1 || $product->pack_stock_type == 2 || $product->pack_stock_type == 3 && Configuration::get('PS_PACK_STOCK_TYPE') > 0)) {
                    $query = new DbQuery();
                    $query->select('od.product_quantity, od.product_quantity_refunded, pk.quantity');
                    $query->from('order_detail', 'od');
                    $query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
                    $query->where('od.product_id = ' . (int) $value['id_product_pack']);
                    $query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.id_order_state = o.current_state');
                    $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
                    $query->leftJoin('pack', 'pk', 'pk.id_product_item = ' . (int) $id_product . ' AND pk.id_product_attribute_item = ' . ($id_product_attribute ? (int) $id_product_attribute : '0') . ' AND id_product_pack = od.product_id');
                    $query->where('os.shipped != 1');
                    $query->where('o.valid = 1 OR (os.id_order_state != ' . (int) Configuration::get('PS_OS_ERROR') . '
								   AND os.id_order_state != ' . (int) Configuration::get('PS_OS_CANCELED') . ')');
                    $query->groupBy('od.id_order_detail');
                    if (count($ids_warehouse)) {
                        $query->where('od.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
                    }
                    $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
                    if (count($res)) {
                        foreach ($res as $row) {
                            $client_orders_qty += ($row['product_quantity'] - $row['product_quantity_refunded']) * $row['quantity'];
                        }
                    }
                }
            }
        }
        // skip if product is a pack without
        if (!Pack::isPack($id_product) || (Pack::isPack($id_product) && Validate::isLoadedObject($product = new Product((int) $id_product)) && $product->pack_stock_type == 0 || $product->pack_stock_type == 2 || $product->pack_stock_type == 3 && (Configuration::get('PS_PACK_STOCK_TYPE') == 0 || Configuration::get('PS_PACK_STOCK_TYPE') == 2))) {
            // Gets client_orders_qty
            $query = new DbQuery();
            $query->select('od.product_quantity, od.product_quantity_refunded');
            $query->from('order_detail', 'od');
            $query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
            $query->where('od.product_id = ' . (int) $id_product);
            if (0 != $id_product_attribute) {
                $query->where('od.product_attribute_id = ' . (int) $id_product_attribute);
            }
            $query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.id_order_state = o.current_state');
            $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
            $query->where('os.shipped != 1');
            $query->where('o.valid = 1 OR (os.id_order_state != ' . (int) Configuration::get('PS_OS_ERROR') . '
						   AND os.id_order_state != ' . (int) Configuration::get('PS_OS_CANCELED') . ')');
            $query->groupBy('od.id_order_detail');
            if (count($ids_warehouse)) {
                $query->where('od.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
            }
            $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
            if (count($res)) {
                foreach ($res as $row) {
                    $client_orders_qty += $row['product_quantity'] - $row['product_quantity_refunded'];
                }
            }
        }
        // Gets supply_orders_qty
        $query = new DbQuery();
        $query->select('sod.quantity_expected, sod.quantity_received');
        $query->from('supply_order', 'so');
        $query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order = so.id_supply_order');
        $query->leftjoin('supply_order_state', 'sos', 'sos.id_supply_order_state = so.id_supply_order_state');
        $query->where('sos.pending_receipt = 1');
        $query->where('sod.id_product = ' . (int) $id_product . ' AND sod.id_product_attribute = ' . (int) $id_product_attribute);
        if (!is_null($ids_warehouse) && count($ids_warehouse)) {
            $query->where('so.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
        }
        $supply_orders_qties = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        $supply_orders_qty = 0;
        foreach ($supply_orders_qties as $qty) {
            if ($qty['quantity_expected'] > $qty['quantity_received']) {
                $supply_orders_qty += $qty['quantity_expected'] - $qty['quantity_received'];
            }
        }
        // Gets {physical OR usable}_qty
        $qty = $this->getProductPhysicalQuantities($id_product, $id_product_attribute, $ids_warehouse, $usable);
        //real qty = actual qty in stock - current client orders + current supply orders
        return $qty - $client_orders_qty + $supply_orders_qty;
    }

 /**
  * Exports CSV
  */
 protected function renderCSV()
 {
     // exports orders
     if (Tools::isSubmit('csv_orders')) {
         // header
         header('Content-type: text/csv; charset=utf-8');
         header('Cache-Control: no-store, no-cache');
         header('Content-disposition: attachment; filename="supply_orders.csv"');
         // write headers column
         $keys = array('id_supplier', 'supplier_name', 'id_lang', 'id_warehouse', 'id_supply_order_state', 'id_currency', 'reference', 'date_add', 'date_upd', 'date_delivery_expected', 'total_te', 'total_with_discount_te', 'total_ti', 'total_tax', 'discount_rate', 'discount_value_te', 'is_template', 'escompte', 'invoice_number', 'date_to_invoice', 'global_discount_amount', 'global_discount_type', 'shipping_amount', 'description');
         echo sprintf("%s\n", implode(';', $keys));
         $query = null;
         $query = new DbQuery();
         $query->select('so.*, ipso.*');
         $query->from('supply_order', 'so');
         $query->leftjoin('erpip_supply_order', 'ipso', 'ipso.id_supply_order = so.id_supply_order');
         if ($this->controller_status == STATUS1) {
             $query->limit(ERP_STCKMGTFR);
         }
         // FILTERS SUPPLIER & WAREHOUSE
         $id_warehouse = $this->getCurrentWarehouse();
         if ($id_warehouse != -1) {
             $query->where('so.id_warehouse = ' . (int) $id_warehouse);
         }
         $id_supplier = $this->getCurrentSupplier();
         if ($id_supplier != -1) {
             $query->where('so.id_supplier = ' . (int) $id_supplier);
         }
         // Execute query
         $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         // write datas
         foreach ($res as $order) {
             $content_csv = array($order['id_supplier'], self::transformText($order['supplier_name']), $order['id_lang'], $order['id_warehouse'], $order['id_supply_order_state'], $order['id_currency'], $order['reference'], $order['date_add'], $order['date_upd'], $order['date_delivery_expected'], $order['total_te'], $order['total_with_discount_te'], $order['total_ti'], $order['total_tax'], $order['discount_rate'], $order['discount_value_te'], $order['is_template'], $order['escompte'], $order['invoice_number'], $order['date_to_invoice'] == '0000-00-00' ? '' : $order['date_to_invoice'], $order['global_discount_amount'], $order['global_discount_type'], $order['shipping_amount'], self::transformText($order['description']), PHP_EOL);
             echo implode(';', $content_csv);
         }
         if ($this->controller_status == STATUS1) {
             echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
         }
         die;
     } else {
         if (Tools::isSubmit('csv_orders_details')) {
             // header
             header('Content-type: text/csv');
             header('Content-Type: application/force-download; charset=UTF-8');
             header('Cache-Control: no-store, no-cache');
             header('Content-disposition: attachment; filename="' . $this->l('supply_orders_details') . '.csv"');
             // echoes details
             $ids = array();
             foreach ($this->_list as $entry) {
                 $ids[] = $entry['id_supply_order'];
             }
             if ($this->controller_status == STATUS1) {
                 $ids = array_splice($ids, 0, ERP_STCKMGTFR);
             }
             if (count($ids) <= 0) {
                 return;
             }
             // for each supply order
             $keys = array('id_product', 'id_product_attribute', 'reference', 'supplier_reference', 'ean13', 'upc', 'name', 'unit_price_te', 'quantity_expected', 'quantity_received', 'price_te', 'discount_rate', 'discount_value_te', 'price_with_discount_te', 'tax_rate', 'tax_value', 'price_ti', 'tax_value_with_order_discount', 'price_with_order_discount_te', 'id_supply_order', 'comment');
             echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $keys)));
             // overrides keys (in order to add FORMAT calls)
             $keys = array('sod.id_product', 'sod.id_product_attribute', 'sod.reference', 'sod.supplier_reference', 'sod.ean13', 'sod.upc', 'sod.name', 'FORMAT(sod.unit_price_te, 2)', 'sod.quantity_expected', 'sod.quantity_received', 'FORMAT(sod.price_te, 2)', 'FORMAT(sod.discount_rate, 2)', 'FORMAT(sod.discount_value_te, 2)', 'FORMAT(sod.price_with_discount_te, 2)', 'FORMAT(sod.tax_rate, 2)', 'FORMAT(sod.tax_value, 2)', 'FORMAT(sod.price_ti, 2)', 'FORMAT(sod.tax_value_with_order_discount, 2)', 'FORMAT(sod.price_with_order_discount_te, 2)', 'sod.id_supply_order', 'ipsod.comment');
             foreach ($ids as $id) {
                 $query = new DbQuery();
                 $query->select(implode(', ', $keys));
                 $query->from('supply_order_detail', 'sod');
                 $query->leftJoin('supply_order', 'so', 'so.id_supply_order = sod.id_supply_order');
                 $query->leftJoin('erpip_supply_order_detail', 'ipsod', 'ipsod.id_supply_order_detail = sod.id_supply_order_detail');
                 // FILTERS SUPPLIER & WAREHOUSE
                 $id_warehouse = $this->getCurrentWarehouse();
                 if ($id_warehouse != -1) {
                     $query->where('so.id_warehouse = ' . (int) $id_warehouse);
                 }
                 $id_supplier = $this->getCurrentSupplier();
                 if ($id_supplier != -1) {
                     $query->where('so.id_supplier = ' . (int) $id_supplier);
                 }
                 $query->where('sod.id_supply_order = ' . (int) $id);
                 $query->orderBy('sod.id_supply_order_detail DESC');
                 $resource = Db::getInstance()->query($query);
                 // gets details
                 while ($row = Db::getInstance()->nextRow($resource)) {
                     $row = array_map(array('CSVCore', 'wrap'), $row);
                     $row['name'] = self::transformText($row['name']);
                     $row['reference'] = self::transformText($row['reference']);
                     $row['supplier_reference'] = self::transformText($row['supplier_reference']);
                     echo sprintf("%s\n", implode(';', $row));
                 }
             }
             if ($this->controller_status == STATUS1) {
                 echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
             }
         } else {
             if (Tools::isSubmit('csv_order_details') && Tools::getValue('id_supply_order')) {
                 $supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
                 if (Validate::isLoadedObject($supply_order)) {
                     $details = $supply_order->getEntriesCollection();
//.........这里部分代码省略.........

 public function renderCSV()
 {
     if (Tools::isSubmit('export_csv')) {
         // header
         header('Content-type: text/csv; charset=utf-8');
         header('Cache-Control: no-store, no-cache');
         header('Content-disposition: attachment; filename="areas.csv"');
         // write headers column
         $keys = array('area_name', 'parent_name', 'warehouse', 'active');
         echo sprintf("%s\n", implode(';', $keys));
         $query = null;
         $query = new DbQuery();
         $query->select('area.name as area_name, parent.name as parent_name, area.active, w.name as warehouse');
         $query->from('erpip_zone', 'area');
         $query->leftjoin('erpip_zone', 'parent', 'parent.id_erpip_zone = area.id_parent');
         $query->leftjoin('warehouse', 'w', 'w.id_warehouse = area.id_warehouse');
         // Execute query
         $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         // write datas
         foreach ($res as $area) {
             $content_csv = array(self::transformText($area['area_name']), self::transformText($area['parent_name']), $area['warehouse'], $area['active'], PHP_EOL);
             echo implode(';', $content_csv);
         }
         die;
     }
 }

 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);
             }
//.........这里部分代码省略.........

    protected function renderCSV()
    {
        if (Tools::isSubmit('export_csv')) {
            /* GENERATION CSV */
            // header
            header('Content-type: text/csv; charset=utf-8');
            header('Cache-Control: no-store, no-cache');
            header('Content-disposition: attachment; filename="inventory_report.csv"');
            // write headers column
            $keys = array('warehouse', 'id_product', 'id_product_attribute', 'SKU', 'supplier_reference', 'product_name', 'quantity_before', 'quantity_after', 'movement_reason', 'stock_gap');
            echo sprintf("%s\n", implode(';', $keys));
            $query = null;
            $query = new DbQuery();
            $query->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, ip.qte_before, ip.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 = ip.id_product
						AND ps.id_product_attribute = ip.id_product_attribute
						LIMIT 1
			)as first_supplier_ref');
            $query->from('erpip_inventory_product', 'ip');
            $query->leftjoin('product', 'p', 'ip.id_product= p.id_product');
            $query->leftjoin('product_attribute', 'pa', 'ip.id_product_attribute= pa.id_product_attribute');
            $query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
            $query->leftjoin('attribute', 'atr', 'atr.id_attribute= pac.id_attribute');
            $query->leftjoin('attribute_lang', 'al', '(al.id_attribute= pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id . ')');
            $query->leftjoin('attribute_group_lang', 'agl', '(agl.id_attribute_group= atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id . ')');
            $query->leftjoin('product_lang', 'pl', '(p.id_product = pl.id_product AND pl.id_lang =' . (int) $this->context->language->id . ')');
            $query->leftjoin('warehouse', 'w', 'w.id_warehouse = ip.id_warehouse');
            $query->leftjoin('stock_mvt_reason_lang', 'smrl', '(smrl.id_stock_mvt_reason = ip.id_mvt_reason AND pl.id_lang =' . (int) $this->context->language->id . ')');
            $query->where('id_erpip_inventory=' . (int) Tools::getValue('id_container'));
            $query->groupBy('ip.id_product_attribute');
            if ($this->controller_status == STATUS1) {
                $query->limit(ERP_STCKMGTFR);
            }
            // Execute query
            $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
            // write datas
            foreach ($res as $product) {
                $content_csv = array($product['warehouse'], $product['id_product'], $product['id_product_attribute'], $product['reference'], $product['first_supplier_ref'], self::transformText($product['name']), $product['qte_before'], $product['qte_after'], $product['reason'], $product['gap'], PHP_EOL);
                echo implode(';', $content_csv);
            }
            if ($this->controller_status == STATUS1) {
                echo $this->l('You are using the free version of 1-Click ERP, which limits the display to 10 products. In order to remove the limit, switch to a higher version.');
            }
            die;
        }
    }

    protected function renderCSV()
    {
        if (Tools::isSubmit('export_csv')) {
            /* FILTRES */
            // Category filter
            $id_category = Tools::isSubmit('id_category') ? intval(Tools::getValue('id_category')) : -1;
            $query = new DbQuery();
            $query->select('id_product');
            $query->from('category_product');
            $query->where("id_category = {$id_category}");
            $categories = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
            $i = 0;
            foreach ($categories as $category) {
                $categories[$i] = $category['id_product'];
                $i++;
            }
            $categories = implode(',', $categories);
            $query = null;
            // Provider filter
            $id_supplier = Tools::isSubmit('id_supplier') ? Tools::getValue('id_supplier') : -1;
            // Brand filter
            $id_manufacturer = Tools::isSubmit('id_manufacturer') ? Tools::getValue('id_manufacturer') : -1;
            // Location filter
            //$area = (Tools::isSubmit('area')) ? Tools::getValue('area') : -1;
            //$subarea = (Tools::isSubmit('subarea')) ? Tools::getValue('subarea') : -1;
            /* GENERATION CSV */
            // header
            header('Content-type: text/csv; charset=utf-8');
            header('Cache-Control: no-store, no-cache');
            header('Content-disposition: attachment; filename="inventory_grid.csv"');
            // Get product list
            $query = null;
            $query = new DbQuery();
            $query->select('p.id_product,
						IF(pa.id_product_attribute, pa.reference, p.reference) as reference,
						p.ean13,
						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');
            $query->from('product', 'p');
            $query->leftjoin('product_attribute', 'pa', 'p.id_product= pa.id_product');
            $query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
            $query->leftjoin('attribute', 'atr', 'atr.id_attribute= pac.id_attribute');
            $query->leftjoin('attribute_lang', 'al', '(al.id_attribute= pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id . ')');
            $query->leftjoin('attribute_group_lang', 'agl', '(agl.id_attribute_group= atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id . ')');
            $query->leftjoin('product_lang', 'pl', '(p.id_product = pl.id_product AND pl.id_lang =' . (int) $this->context->language->id . ')');
            // Apply filters
            if ($id_category != -1) {
                $query->where('p.id_product IN(' . pSQL($categories) . ')');
            }
            if ($id_supplier != -1) {
                $query->where('p.id_supplier = ' . (int) $id_supplier);
            }
            if ($id_manufacturer != -1) {
                $query->where('p.id_manufacturer = ' . (int) $id_manufacturer);
            }
            $id_warehouse = $this->getCookie('id_warehouse');
            // Apply warehouse filter, area, sub area only in active stock manager
            if ($this->advanced_stock_management && $id_warehouse != -1) {
                $query->select('wpl.location, wpl.id_warehouse, z.name as area, sz.name as subarea');
                $query->leftjoin('warehouse_product_location', 'wpl', '(p.id_product = wpl.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0))');
                $query->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
                $query->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
                $query->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
                $area = Tools::getValue('area') == null ? -1 : Tools::getValue('area');
                $subarea = Tools::getValue('subarea') == null ? -1 : Tools::getValue('subarea');
                // Warehouse filter
                $query->where('wpl.id_warehouse = ' . (int) $id_warehouse);
                // Area filter
                if ($area != -1 && $subarea == -1) {
                    $query->where('z.id_erpip_zone = ' . (int) $area);
                }
                // area and sub area filter
                if ($area != -1 && $subarea != -1) {
                    $query->where('z.id_erpip_zone = ' . (int) $area);
                    $query->where('sz.id_erpip_zone = ' . (int) $subarea);
                }
            }
            $query->groupBy('pa.id_product_attribute, p.id_product');
            $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
            // Get the physical quantity
            $nb_items = count($products);
            for ($i = 0; $i < $nb_items; ++$i) {
                $item =& $products;
                if ($this->advanced_stock_management) {
                    $query = new DbQuery();
                    $query->select('physical_quantity');
                    $query->from('stock');
                    $query->where('id_product = ' . (int) $item[$i]['id_product'] . ' AND id_product_attribute = ' . (int) $item[$i]['id_product_attribute'] . ' AND id_warehouse =' . (int) $item[$i]['id_warehouse']);
                    // Execute query
                    $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query);
                } else {
                    $res['physical_quantity'] = (int) Product::getQuantity($item[$i]['id_product'], (int) $item[$i]['id_product_attribute']);
                }
                // add column to array
                $item[$i]['physical_quantity'] = $res['physical_quantity'];
            }
            // write headers column
            $keys = array('id_product', 'id_product_attribute', 'reference', 'ean', 'name', 'area', 'subarea', 'location', 'physical_quantity', 'found_quantity');
            echo sprintf("%s\n", implode(';', $keys));
//.........这里部分代码省略.........