PHP DbQuery::innerjoin方法代码示例

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