PHP DbQuery::join方法代码示例

 /**
  * Gets the name of a given product, in the given lang
  * HAI : override method to record product name with sort
  * 
  * @since 1.5.0
  * @param int $id_product
  * @param int $id_product_attribute Optional
  * @param int $id_lang Optional
  * @return string
  */
 public static function getProductName($id_product, $id_product_attribute = null, $id_lang = null)
 {
     // use the lang in the context if $id_lang is not defined
     if (!$id_lang) {
         $id_lang = (int) Context::getContext()->language->id;
     }
     // creates the query object
     $query = new DbQuery();
     // selects different names, if it is a combination
     if ($id_product_attribute) {
         $query->select('IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name ORDER BY agl.`name`, \' - \', al.name ASC SEPARATOR \', \')),pl.name) as name');
     } else {
         $query->select('DISTINCT pl.name as name');
     }
     // adds joins & where clauses for combinations
     if ($id_product_attribute) {
         $query->from('product_attribute', 'pa');
         $query->join(Shop::addSqlAssociation('product_attribute', 'pa'));
         $query->innerJoin('product_lang', 'pl', 'pl.id_product = pa.id_product AND pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
         $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 = atr.id_attribute AND al.id_lang = ' . (int) $id_lang);
         $query->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) $id_lang);
         $query->where('pa.id_product = ' . (int) $id_product . ' AND pa.id_product_attribute = ' . (int) $id_product_attribute);
     } else {
         $query->from('product_lang', 'pl');
         $query->where('pl.id_product = ' . (int) $id_product);
         $query->where('pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
     }
     return Db::getInstance()->getValue($query);
 }

    public function renderView()
    {
        $badges_feature = new Collection('badge', $this->context->language->id);
        $badges_feature->where('type', '=', 'feature');
        $badges_feature->orderBy('id_group');
        $badges_feature->orderBy('group_position');
        $badges_achievement = new Collection('badge', $this->context->language->id);
        $badges_achievement->where('type', '=', 'achievement');
        $badges_achievement->orderBy('id_group');
        $badges_achievement->orderBy('group_position');
        $badges_international = new Collection('badge', $this->context->language->id);
        $badges_international->where('type', '=', 'international');
        $badges_international->orderBy('id_group');
        $badges_international->orderBy('group_position');
        $groups = array();
        $query = new DbQuery();
        $query->select('DISTINCT(b.`id_group`), bl.group_name, b.type');
        $query->from('badge', 'b');
        $query->join('
			LEFT JOIN `' . _DB_PREFIX_ . 'badge_lang` bl ON bl.`id_badge` = b.`id_badge`');
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        foreach ($result as $res) {
            $groups['badges_' . $res['type']][$res['id_group']] = $res['group_name'];
        }
        $badges_type = array('badges_feature' => array('name' => $this->l('Features'), 'badges' => $badges_feature), 'badges_achievement' => array('name' => $this->l('Achievements'), 'badges' => $badges_achievement), 'badges_international' => array('name' => $this->l('International'), 'badges' => $badges_international));
        $levels = array(1 => $this->l('1. Beginner'), 2 => $this->l('2. Pro'), 3 => $this->l('3. Expert'), 4 => $this->l('4. Wizard'), 5 => $this->l('5. Guru'), 6 => $this->l('6. Legend'));
        $this->tpl_view_vars = array('badges_type' => $badges_type, 'current_level_percent' => (int) Configuration::get('GF_CURRENT_LEVEL_PERCENT'), 'current_level' => (int) Configuration::get('GF_CURRENT_LEVEL'), 'groups' => $groups, 'levels' => $levels);
        return parent::renderView();
    }

    /**
     * Return suppliers
     *
     * @return array Suppliers
     */
    public static function getSuppliers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_groups = false)
    {
        if (!$id_lang) {
            $id_lang = Configuration::get('PS_LANG_DEFAULT');
        }
        $query = new DbQuery();
        $query->select('s.*, sl.`description`');
        $query->from('supplier', 's');
        $query->leftJoin('supplier_lang', 'sl', 's.`id_supplier` = sl.`id_supplier` AND sl.`id_lang` = ' . (int) $id_lang);
        $query->join(Shop::addSqlAssociation('supplier', 's'));
        if ($active) {
            $query->where('s.`active` = 1');
        }
        $query->orderBy(' s.`name` ASC');
        $query->limit($n, ($p - 1) * $n);
        $query->groupBy('s.id_supplier');
        $suppliers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        if ($suppliers === false) {
            return false;
        }
        if ($get_nb_products) {
            $sql_groups = '';
            if (!$all_groups) {
                $groups = FrontController::getCurrentCustomerGroups();
                $sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
            }
            foreach ($suppliers as $key => $supplier) {
                $sql = '
					SELECT DISTINCT(ps.`id_product`)
					FROM `' . _DB_PREFIX_ . 'product_supplier` ps
					JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`)
					' . Shop::addSqlAssociation('product', 'p') . '
					WHERE ps.`id_supplier` = ' . (int) $supplier['id_supplier'] . '
					AND ps.id_product_attribute = 0' . ($active ? ' AND product_shop.`active` = 1' : '') . ($all_groups ? '' : '
					AND ps.`id_product` IN (
						SELECT cp.`id_product`
						FROM `' . _DB_PREFIX_ . 'category_group` cg
						LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
						WHERE cg.`id_group` ' . $sql_groups . '
					)');
                $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
                $suppliers[$key]['nb_products'] = count($result);
            }
        }
        $nb_suppliers = count($suppliers);
        $rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS');
        for ($i = 0; $i < $nb_suppliers; $i++) {
            if ($rewrite_settings) {
                $suppliers[$i]['link_rewrite'] = Tools::link_rewrite($suppliers[$i]['name'], false);
            } else {
                $suppliers[$i]['link_rewrite'] = 0;
            }
        }
        return $suppliers;
    }

    /**
     * Method called when an ajax request is made
     * @see AdminController::postProcess()
     */
    public function ajaxProcess()
    {
        if (Tools::isSubmit('id')) {
            $this->lang = false;
            $lang_id = (int) $this->context->language->id;
            $id_product = (int) Tools::getValue('id');
            $period = Tools::getValue('period') ? (int) Tools::getValue('period') : 7;
            $warehouse = Tools::getValue('id_warehouse', -1);
            $where_warehouse = '';
            if ($warehouse != -1) {
                $where_warehouse = ' AND s.id_warehouse = ' . (int) $warehouse;
            }
            $query = new DbQuery();
            $query->select('pa.id_product_attribute as id, pa.id_product, stock_view.reference, stock_view.ean13,
							stock_view.upc, stock_view.usable_quantity as stock');
            $query->from('product_attribute', 'pa');
            $query->join('INNER JOIN
						  (
						  	SELECT SUM(s.usable_quantity) as usable_quantity, s.id_product_attribute, s.reference, s.ean13, s.upc
						   	FROM ' . _DB_PREFIX_ . 'stock s
						   	WHERE s.id_product = ' . $id_product . $where_warehouse . '
						   	GROUP BY s.id_product_attribute
						   )
						   stock_view ON (stock_view.id_product_attribute = pa.id_product_attribute)');
            $query->where('pa.id_product = ' . $id_product);
            $query->groupBy('pa.id_product_attribute');
            $datas = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
            foreach ($datas as &$data) {
                $data['name'] = Product::getProductName($data['id_product'], $data['id']);
                // computes coverage
                $coverage = StockManagerFactory::getManager()->getProductCoverage($data['id_product'], $data['id'], $period, $this->getCurrentCoverageWarehouse() == -1 ? null : $warehouse);
                if ($coverage != -1) {
                    if ($coverage < $this->getCurrentWarning()) {
                        // if highlight needed
                        $data['color'] = '#BDE5F8';
                    }
                    $data['coverage'] = $coverage;
                } else {
                    // infinity
                    $data['coverage'] = '--';
                }
                // computes quantity sold
                $qty_sold = $this->getQuantitySold($data['id_product'], $data['id'], $this->getCurrentCoveragePeriod());
                if (!$qty_sold) {
                    $data['qty_sold'] = '--';
                } else {
                    $data['qty_sold'] = $qty_sold;
                }
            }
            echo Tools::jsonEncode(array('data' => $datas, 'fields_display' => $this->fields_list));
        }
        die;
    }

    public static function getIdsAdviceToUnvalidate()
    {
        $ids = array();
        $query = new DbQuery();
        $query->select('a.`id_advice`');
        $query->from('advice', 'a');
        $query->join('
			LEFT JOIN `' . _DB_PREFIX_ . 'condition_advice` ca ON ca.`id_advice` = a.`id_advice` AND ca.`display` = 0 
			LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = ca.`id_condition` AND c.`validated` = 1');
        $query->where('a.validated = 1');
        $query->groupBy('a.`id_advice`');
        $query->having('count(*) = SUM(c.validated)');
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        foreach ($result as $advice) {
            $ids[] = $advice['id_advice'];
        }
        return $ids;
    }

    public static function getMovementsByIds($ids)
    {
        // Reformatting datas if multiple IDs separated by '|' and protection
        if (strstr($ids, '|')) {
            $idsList = explode('|', $ids);
            array_walk($idsList, 'intval');
            $ids = implode(',', $idsList);
        } else {
            $ids = pSQL($ids);
        }
        $query = new DbQuery();
        $query->select('
						IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
						IFNULL(pa.reference, s.reference) as reference, sm.physical_quantity, m.name as manufacturer_name ');
        $query->from('stock_mvt', 'sm');
        $query->join(' INNER JOIN `' . _DB_PREFIX_ . 'stock` s ON s.id_stock = sm.id_stock');
        $query->join('INNER JOIN ' . _DB_PREFIX_ . 'product p ON s.id_product = p.id_product ');
        $query->join('LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute pa ON s.id_product_attribute = pa.id_product_attribute ');
        $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (
				s.id_product = pl.id_product
				AND pl.id_lang = ' . (int) Context::getContext()->language->id . '
		)');
        $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = s.id_product_attribute)');
        $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'attribute` atr ON (atr.id_attribute = pac.id_attribute)');
        $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (
				al.id_attribute = pac.id_attribute
				AND al.id_lang = ' . (int) Context::getContext()->language->id . '
		)');
        $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (
				agl.id_attribute_group = atr.id_attribute_group
				AND agl.id_lang = ' . (int) Context::getContext()->language->id . '
		)');
        $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.id_manufacturer = p.id_manufacturer');
        $query->where("sm.id_stock_mvt IN ({$ids})");
        $query->groupBy('s.id_product, s.id_product_attribute');
        return Db::getInstance()->executeS($query);
    }

    public static function getIdsBadgesToValidate()
    {
        $ids = array();
        $query = new DbQuery();
        $query->select('b.`id_badge`');
        $query->from('badge', 'b');
        $query->join('
			LEFT JOIN `' . _DB_PREFIX_ . 'condition_badge` cb ON cb.`id_badge` = b.`id_badge` 
			LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = cb.`id_condition` AND c.`validated` = 1');
        $query->where('b.validated = 0');
        $query->groupBy('b.`id_badge`');
        $query->having('count(*) = SUM(c.validated)');
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        foreach ($result as $badge) {
            $ids[] = $badge['id_badge'];
        }
        return $ids;
    }

    /**
     * AdminController::initContent() override
     * @see AdminController::initContent()
     */
    public function initContent()
    {
        if (!Configuration::get('PS_ADVANCED_STOCK_MANAGEMENT')) {
            $this->warnings[md5('PS_ADVANCED_STOCK_MANAGEMENT')] = $this->l('You need to activate the Advanced Stock Management feature prior to using this feature.');
            return false;
        }
        // Manage the add stock form
        if ($this->display == 'addstock' || $this->display == 'removestock' || $this->display == 'transferstock') {
            if (Tools::isSubmit('id_product') || Tools::isSubmit('id_product_attribute')) {
                // get id product and product attribute if possible
                $id_product = (int) Tools::getValue('id_product', 0);
                $id_product_attribute = (int) Tools::getValue('id_product_attribute', 0);
                $product_is_valid = false;
                $is_pack = false;
                $is_virtual = false;
                $lang_id = $this->context->language->id;
                $default_wholesale_price = 0;
                // try to load product attribute first
                if ($id_product_attribute > 0) {
                    // try to load product attribute
                    $combination = new Combination($id_product_attribute);
                    if (Validate::isLoadedObject($combination)) {
                        $product_is_valid = true;
                        $id_product = $combination->id_product;
                        $reference = $combination->reference;
                        $ean13 = $combination->ean13;
                        $upc = $combination->upc;
                        $manufacturer_reference = $combination->supplier_reference;
                        // get the full name for this combination
                        $query = new DbQuery();
                        $query->select('IFNULL(CONCAT(pl.`name`, \' : \', GROUP_CONCAT(agl.`name`, \' - \', al.`name` SEPARATOR \', \')),pl.`name`) as name');
                        $query->from('product_attribute', 'a');
                        $query->join('INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.`id_product` = a.`id_product` AND pl.`id_lang` = ' . (int) $lang_id . ')
							LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac ON (pac.`id_product_attribute` = a.`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` = atr.`id_attribute` AND al.`id_lang` = ' . (int) $lang_id . ')
							LEFT JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON (agl.`id_attribute_group` = atr.`id_attribute_group` AND agl.`id_lang` = ' . (int) $lang_id . ')');
                        $query->where('a.`id_product_attribute` = ' . $id_product_attribute);
                        $name = Db::getInstance()->getValue($query);
                        $p = new Product($id_product, false, $lang_id);
                        $default_wholesale_price = $combination->wholesale_price > 0 ? $combination->wholesale_price : $p->wholesale_price;
                    }
                } else {
                    $product = new Product($id_product, false, $lang_id);
                    if (is_int($product->id)) {
                        $product_is_valid = true;
                        $reference = $product->reference;
                        $ean13 = $product->ean13;
                        $upc = $product->upc;
                        $name = $product->name;
                        $manufacturer_reference = $product->supplier_reference;
                        $is_pack = $product->cache_is_pack;
                        $is_virtual = $product->is_virtual;
                        $default_wholesale_price = $product->wholesale_price;
                    }
                }
                if ($product_is_valid === true && $is_virtual == false) {
                    // init form
                    $this->renderForm();
                    $this->getlanguages();
                    $helper = new HelperForm();
                    $this->initPageHeaderToolbar();
                    // Check if form template has been overriden
                    if (file_exists($this->context->smarty->getTemplateDir(0) . '/' . $this->tpl_folder . 'form.tpl')) {
                        $helper->tpl = $this->tpl_folder . 'form.tpl';
                    }
                    $this->setHelperDisplay($helper);
                    $helper->submit_action = $this->display;
                    $helper->id = null;
                    // no display standard hidden field in the form
                    $helper->languages = $this->_languages;
                    $helper->default_form_language = $this->default_form_language;
                    $helper->allow_employee_form_lang = $this->allow_employee_form_lang;
                    $helper->show_cancel_button = true;
                    $helper->back_url = $this->context->link->getAdminLink('AdminStockManagement');
                    $helper->fields_value = array('id_product' => $id_product, 'id_product_attribute' => $id_product_attribute, 'reference' => $reference, 'manufacturer_reference' => $manufacturer_reference, 'name' => $name, 'ean13' => $ean13, 'upc' => $upc, 'check' => md5(_COOKIE_KEY_ . $id_product . $id_product_attribute), 'quantity' => Tools::getValue('quantity', ''), 'id_warehouse' => Tools::getValue('id_warehouse', ''), 'usable' => $this->fields_value['usable'] ? $this->fields_value['usable'] : Tools::getValue('usable', 1), 'price' => Tools::getValue('price', (double) Tools::convertPrice($default_wholesale_price, null)), 'id_currency' => Tools::getValue('id_currency', ''), 'id_stock_mvt_reason' => Tools::getValue('id_stock_mvt_reason', ''), 'is_post' => 1);
                    if ($this->display == 'addstock') {
                        $_POST['id_product'] = (int) $id_product;
                    }
                    if ($this->display == 'transferstock') {
                        $helper->fields_value['id_warehouse_from'] = Tools::getValue('id_warehouse_from', '');
                        $helper->fields_value['id_warehouse_to'] = Tools::getValue('id_warehouse_to', '');
                        $helper->fields_value['usable_from'] = Tools::getValue('usable_from', '1');
                        $helper->fields_value['usable_to'] = Tools::getValue('usable_to', '1');
                    }
                    $this->content .= $helper->generateForm($this->fields_form);
                    $this->context->smarty->assign(array('content' => $this->content, 'show_page_header_toolbar' => $this->show_page_header_toolbar, 'page_header_toolbar_title' => $this->page_header_toolbar_title, 'page_header_toolbar_btn' => $this->page_header_toolbar_btn));
                } else {
                    $this->errors[] = Tools::displayError('The specified product is not valid.');
                }
            }
        } else {
            parent::initContent();
        }
    }

    public function getNewProducts($where, $id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }
        $front = true;
        if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
            $front = false;
        }
        if ($page_number < 0) {
            $page_number = 0;
        }
        if ($nb_products < 1) {
            $nb_products = 10;
        }
        if (empty($order_by) || $order_by == 'position') {
            $order_by = 'date_add';
        }
        if (empty($order_way)) {
            $order_way = 'DESC';
        }
        if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd') {
            $order_by_prefix = 'p';
        } else {
            if ($order_by == 'name') {
                $order_by_prefix = 'pl';
            }
        }
        if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
            die(Tools::displayError());
        }
        $sql_groups = '';
        if (Group::isFeatureActive()) {
            $groups = FrontController::getCurrentCustomerGroups();
            $sql_groups = 'AND p.`id_product` IN (
					SELECT cp.`id_product`
					FROM `' . _DB_PREFIX_ . 'category_group` cg
					LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
					WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
				)';
        }
        if (strpos($order_by, '.') > 0) {
            $order_by = explode('.', $order_by);
            $order_by_prefix = $order_by[0];
            $order_by = $order_by[1];
        }
        if ($count) {
            $sql = 'SELECT COUNT(p.`id_product`) AS nb
						FROM `' . _DB_PREFIX_ . 'product` p
						' . Shop::addSqlAssociation('product', 'p') . '
						WHERE product_shop.`active` = 1
						AND product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"
						' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
						' . $sql_groups;
            return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
        }
        $sql = new DbQuery();
        $sql->select('p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
				pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
				product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new');
        $sql->from('product', 'p');
        $sql->join(Shop::addSqlAssociation('product', 'p'));
        $sql->leftJoin('product_lang', 'pl', '
				p.`id_product` = pl.`id_product`
				AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
        $sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`');
        $sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1'));
        $sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang);
        $sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');
        $sql->where('product_shop.`active` = 1');
        if ($front) {
            $sql->where('product_shop.`visibility` IN ("both", "catalog")');
        }
        $sql->where('product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"');
        if (Group::isFeatureActive()) {
            $sql->where('p.`id_product` IN (
					SELECT cp.`id_product`
					FROM `' . _DB_PREFIX_ . 'category_group` cg
					LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
					WHERE ' . $where . ' cg.`id_group` ' . $sql_groups . '
				)');
        }
        $sql->groupBy('product_shop.id_product');
        $sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way));
        $sql->limit($nb_products, $page_number * $nb_products);
        if (Combination::isFeatureActive()) {
            $sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute');
            $sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`');
            $sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1'));
        }
        $sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0));
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        if ($order_by == 'price') {
            Tools::orderbyPrice($result, $order_way);
        }
        if (!$result) {
            return false;
        }
        $products_ids = array();
        foreach ($result as $row) {
//.........这里部分代码省略.........

 public function getAffectedProducts($products = false)
 {
     $conditions_group = $this->getConditions();
     $query = new DbQuery();
     $query->select('p.id_product');
     $query->from('product', 'p');
     $query->join(Shop::addSqlAssociation('product', 'p'));
     $query->groupBy('p.id_product');
     $attributes = false;
     $categories = false;
     $features = false;
     $suppliers = false;
     $where = '1';
     if ($conditions_group) {
         $where .= ' AND (';
         foreach ($conditions_group as $id_condition_group => $condition_group) {
             $fields = array('category' => array('name' => 'cp.id_category', 'values' => array()), 'manufacturer' => array('name' => 'p.id_manufacturer', 'values' => array()), 'supplier' => array('name' => 'pss.id_supplier', 'values' => array()), 'feature' => array('name' => 'fp.id_feature_value', 'values' => array()), 'attribute' => array('name' => 'pac.id_attribute', 'values' => array()));
             foreach ($condition_group as $condition) {
                 if ($condition['type'] == 'category') {
                     $categories = true;
                 } elseif ($condition['type'] == 'feature') {
                     $features = true;
                 } elseif ($condition['type'] == 'attribute') {
                     $attributes = true;
                 } elseif ($condition['type'] == 'supplier') {
                     $suppliers = true;
                 }
                 $fields[$condition['type']]['values'][] = $condition['value'];
             }
             foreach ($fields as $field) {
                 if (!($n_conditions = count($field['values']))) {
                     continue;
                 }
                 $where .= $field['name'] . ' IN (' . implode(',', array_map('intval', $field['values'])) . ') AND ';
                 if ($n_conditions > 1) {
                     $query->having('COUNT(' . bqSQL($field['name']) . ') >=' . (int) $n_conditions);
                 }
             }
             $where = rtrim($where, ' AND ') . ') OR (';
         }
         $where = rtrim($where, 'OR (');
     }
     if ($products && count($products)) {
         $where .= ' AND p.id_product IN (' . implode(', ', array_map('intval', $products)) . ')';
     }
     if ($attributes) {
         $query->select('pa.id_product_attribute');
         $query->leftJoin('product_attribute', 'pa', 'p.id_product = pa.id_product');
         $query->join(Shop::addSqlAssociation('product_attribute', 'pa', false));
         $query->leftJoin('product_attribute_combination', 'pac', 'pa.id_product_attribute = pac.id_product_attribute');
         $query->groupBy('pa.id_product_attribute');
     } else {
         $query->select('NULL as id_product_attribute');
     }
     if ($features) {
         $query->leftJoin('feature_product', 'fp', 'p.id_product = fp.id_product');
     }
     if ($categories) {
         $query->leftJoin('category_product', 'cp', 'p.id_product = cp.id_product');
     }
     if ($suppliers) {
         $query->leftJoin('product_supplier', 'pss', 'p.id_product = pss.id_product');
     }
     if ($where) {
         $query->where($where);
     }
     return Db::getInstance()->executeS($query);
 }

    public static function swapProduct($area, $subarea, $location)
    {
        // build query
        if ($location == '') {
            $location = '0';
        }
        $query = new DbQuery();
        $query->select('IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
							wpl.id_product, wpl.id_product_attribute');
        $query->from('warehouse_product_location', 'wpl');
        $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.id_product = wpl.id_product');
        $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (wpl.id_product = pl.id_product AND pl.id_lang = ' . (int) Context::getContext()->language->id . ')
							LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = wpl.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) Context::getContext()->language->id . ')
							LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) Context::getContext()->language->id . ')');
        $query->where("wpl.zone = '" . pSQL($area) . "' AND wpl.sous_zone = '" . pSQL($subarea) . "' AND wpl.location=" . pSQL($location));
        $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query);
        if ($row['id_product'] == null) {
            echo 'false';
        } else {
            echo Tools::jsonEncode(Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query));
        }
    }

 public static function getIdsByBadgeGroup($badge_group)
 {
     $ids = array();
     $sub_query = new DbQuery();
     $sub_query->select('id_badge');
     $sub_query->from('badge', 'b');
     $sub_query->where('b.`id_group` = ' . (int) $badge_group);
     $sub_query->where('b.`validated` = 0');
     $sub_query->groupBy('b.`id_group`');
     $query = new DbQuery();
     $query->select('c.`id_condition`');
     $query->from('condition', 'c');
     $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'condition_badge` cb ON cb.`id_condition` = c.`id_condition`');
     $query->where('c.`validated` = 0');
     $query->where('cb.`id_badge` IN (' . $sub_query . ')');
     $query->groupBy('c.`id_condition`');
     $result = Db::getInstance()->executeS($query);
     foreach ($result as $r) {
         $ids[] = $r['id_condition'];
     }
     return $ids;
 }

 /**
  * AdminController::getList() override
  * @see AdminController::getList()
  */
 public function getList($id_lang, $order_by = null, $order_way = null, $start = 0, $limit = null, $id_lang_shop = false)
 {
     parent::getList($id_lang, $order_by, $order_way, $start, $limit, $id_lang_shop);
     if ($this->ajax) {
         foreach ($this->_list as &$list) {
             if (file_exists(_PS_IMG_DIR_ . $this->fieldImageSettings['dir'] . '/' . (int) $list['id_attribute'] . '.jpg')) {
                 if (!isset($list['color']) || !is_array($list['color'])) {
                     $list['color'] = array();
                 }
                 $list['color']['texture'] = '../img/' . $this->fieldImageSettings['dir'] . '/' . (int) $list['id_attribute'] . '.jpg';
             }
         }
     } else {
         $nb_items = count($this->_list);
         for ($i = 0; $i < $nb_items; ++$i) {
             $item =& $this->_list[$i];
             $query = new DbQuery();
             $query->select('COUNT(a.id_attribute) as count_values');
             $query->from('attribute', 'a');
             $query->join(Shop::addSqlAssociation('attribute', 'a'));
             $query->where('a.id_attribute_group =' . (int) $item['id_attribute_group']);
             $query->groupBy('attribute_shop.id_shop');
             $query->orderBy('count_values DESC');
             $item['count_values'] = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query);
             unset($query);
         }
     }
 }

    public function getProducts($refresh = false, $id_product = false, $id_country = null)
    {
        if (!$this->id) {
            return array();
        }
        if ($this->_products !== null && !$refresh) {
            if (is_int($id_product)) {
                foreach ($this->_products as $product) {
                    if ($product['id_product'] == $id_product) {
                        return array($product);
                    }
                }
                return array();
            }
            return $this->_products;
        }
        $sql = new DbQuery();
        $sql->select('cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`,
						cp.`id_cart_product`, cp.`quantity_fractional` AS cart_quantity_fractional, p.id_pp_template,
						pl.`description_short`, pl.`available_now`, pl.`available_later`, product_shop.`id_category_default`, p.`id_supplier`,
						p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`,
						product_shop.`available_for_order`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`,
						stock.`quantity` + stock.`quantity_remainder` AS quantity_available, p.`unit_price_ratio`, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`,
						p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) + IFNULL(stock.quantity_remainder, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category,
						cp.`id_cart_product` AS unique_id, cp.id_address_delivery,
						product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference, IFNULL(sp.`reduction_type`, 0) AS reduction_type');
        $sql->from('cart_product', 'cp');
        $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`');
        $sql->innerJoin('product_shop', 'product_shop', '(product_shop.`id_shop` = cp.`id_shop` AND product_shop.`id_product` = p.`id_product`)');
        $sql->leftJoin('product_lang', 'pl', '
			p.`id_product` = pl.`id_product`
			AND pl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('pl', 'cp.id_shop'));
        $sql->leftJoin('category_lang', 'cl', '
			product_shop.`id_category_default` = cl.`id_category`
			AND cl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('cl', 'cp.id_shop'));
        $sql->leftJoin('product_supplier', 'ps', 'ps.`id_product` = cp.`id_product` AND ps.`id_product_attribute` = cp.`id_product_attribute` AND ps.`id_supplier` = p.`id_supplier`');
        $sql->leftJoin('specific_price', 'sp', 'sp.`id_product` = cp.`id_product`');
        $sql->join(Product::sqlStock('cp', 'cp'));
        $sql->where('cp.`id_cart` = ' . (int) $this->id);
        if ($id_product) {
            $sql->where('cp.`id_product` = ' . (int) $id_product);
        }
        $sql->where('p.`id_product` IS NOT NULL');
        $sql->groupBy('unique_id');
        $sql->orderBy('cp.`date_add`, p.`id_product`, cp.`id_product_attribute` ASC');
        if (Customization::isFeatureActive()) {
            $sql->select('cu.`id_customization`, cu.`quantity` AS customization_quantity, cu.`quantity_fractional` AS customization_quantity_fractional');
            $sql->leftJoin('customization', 'cu', 'cp.`id_cart_product` = cu.`id_cart_product`');
        } else {
            $sql->select('NULL AS customization_quantity, NULL AS customization_quantity_fractional, NULL AS id_customization');
        }
        if (Combination::isFeatureActive()) {
            $sql->select('
				product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr,
				IF (IFNULL(pa.`reference`, \'\') = \'\', p.`reference`, pa.`reference`) AS reference,
				(p.`weight`+ pa.`weight`) weight_attribute,
				IF (IFNULL(pa.`ean13`, \'\') = \'\', p.`ean13`, pa.`ean13`) AS ean13,
				IF (IFNULL(pa.`upc`, \'\') = \'\', p.`upc`, pa.`upc`) AS upc,
				pai.`id_image` as pai_id_image, il.`legend` as pai_legend,
				IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity,
				IFNULL(product_attribute_shop.`minimal_quantity_fractional`, product_shop.`minimal_quantity_fractional`) as minimal_quantity_fractional,
				IF(product_attribute_shop.wholesale_price > 0,  product_attribute_shop.wholesale_price, product_shop.`wholesale_price`) wholesale_price
			');
            $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product_attribute` = cp.`id_product_attribute`');
            $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', '(product_attribute_shop.`id_shop` = cp.`id_shop` AND product_attribute_shop.`id_product_attribute` = pa.`id_product_attribute`)');
            $sql->leftJoin('product_attribute_image', 'pai', 'pai.`id_product_attribute` = pa.`id_product_attribute`');
            $sql->leftJoin('image_lang', 'il', 'il.`id_image` = pai.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang);
        } else {
            $sql->select('p.`reference` AS reference, p.`ean13`,
				p.`upc` AS upc, product_shop.`minimal_quantity` AS minimal_quantity, product_shop.`wholesale_price` wholesale_price');
        }
        $result = Db::getInstance()->executeS($sql);
        $products_ids = array();
        $pa_ids = array();
        if ($result) {
            foreach ($result as $row) {
                $products_ids[] = $row['id_product'];
                $pa_ids[] = $row['id_product_attribute'];
            }
        }
        Product::cacheProductsFeatures($products_ids);
        Cart::cacheSomeAttributesLists($pa_ids, $this->id_lang);
        $this->_products = array();
        if (empty($result)) {
            return array();
        }
        $cart_shop_context = Context::getContext()->cloneContext();
        foreach ($result as &$row) {
            if (isset($row['ecotax_attr']) && $row['ecotax_attr'] > 0) {
                $row['ecotax'] = (double) $row['ecotax_attr'];
            }
            $row['stock_quantity'] = (double) $row['quantity'];
            $row['quantity'] = (int) $row['cart_quantity'];
            if (isset($row['id_product_attribute']) && (int) $row['id_product_attribute'] && isset($row['weight_attribute'])) {
                $row['weight'] = (double) $row['weight_attribute'];
            }
            if (Configuration::get('PS_TAX_ADDRESS_TYPE') == 'id_address_invoice') {
                $address_id = (int) $this->id_address_invoice;
            } else {
                $address_id = (int) $row['id_address_delivery'];
//.........这里部分代码省略.........

 public function getWsTaxes()
 {
     $query = new DbQuery();
     $query->select('id_tax as id');
     $query->from('order_detail_tax', 'tax');
     $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON (tax.`id_order_detail` = od.`id_order_detail`)');
     $query->where('od.`id_order_detail` = ' . (int) $this->id_order_detail);
     return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
 }