PHP DbQuery::groupBy方法代码示例

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

    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;
    }

    protected function getTaxOrderSummary()
    {
        $query = new DbQuery();
        $query->select('
			SUM(price_with_order_discount_te) as base_te,
			tax_rate,
			SUM(tax_value_with_order_discount) as total_tax_value
		');
        $query->from('supply_order_detail');
        $query->where('id_supply_order = ' . (int) $this->supply_order->id);
        $query->groupBy('tax_rate');
        $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        foreach ($results as &$result) {
            $result['base_te'] = Tools::ps_round($result['base_te'], 2);
            $result['tax_rate'] = Tools::ps_round($result['tax_rate'], 2);
            $result['total_tax_value'] = Tools::ps_round($result['total_tax_value'], 2);
        }
        unset($result);
        // remove reference
        return $results;
    }

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

    /**
     * method call when ajax request is made for search product to add to the order
     * @TODO - Update this method to retreive the reference, ean13, upc corresponding to a product attribute
     */
    public function ajaxProcessSearchProduct()
    {
        // Get the search pattern
        $pattern = pSQL(Tools::getValue('q', false));
        if (!$pattern || $pattern == '' || strlen($pattern) < 1) {
            die;
        }
        // get supplier id
        $id_supplier = (int) Tools::getValue('id_supplier', false);
        // gets the currency
        $id_currency = (int) Tools::getValue('id_currency', false);
        // get lang from context
        $id_lang = (int) Context::getContext()->language->id;
        $query = new DbQuery();
        $query->select('
			CONCAT(p.id_product, \'_\', IFNULL(pa.id_product_attribute, \'0\')) as id,
			ps.product_supplier_reference as supplier_reference,
			IFNULL(pa.reference, IFNULL(p.reference, \'\')) as reference,
			IFNULL(pa.ean13, IFNULL(p.ean13, \'\')) as ean13,
			IFNULL(pa.upc, IFNULL(p.upc, \'\')) as upc,
			md5(CONCAT(\'' . _COOKIE_KEY_ . '\', p.id_product, \'_\', IFNULL(pa.id_product_attribute, \'0\'))) as checksum,
			IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.name, \' - \', al.name order by agl.name SEPARATOR \', \')), pl.name) as name
		');
        $query->from('product', 'p');
        $query->innerJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . $id_lang);
        $query->leftJoin('product_attribute', 'pa', 'pa.id_product = p.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 = 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->leftJoin('product_supplier', 'ps', 'ps.id_product = p.id_product AND ps.id_product_attribute = IFNULL(pa.id_product_attribute, 0)');
        $query->where('(pl.name LIKE \'%' . $pattern . '%\' OR p.reference LIKE \'%' . $pattern . '%\' OR ps.product_supplier_reference LIKE \'%' . $pattern . '%\')');
        $query->where('p.id_product NOT IN (SELECT pd.id_product FROM `' . _DB_PREFIX_ . 'product_download` pd WHERE (pd.id_product = p.id_product))');
        $query->where('p.is_virtual = 0 AND p.cache_is_pack = 0');
        if ($id_supplier) {
            $query->where('ps.id_supplier = ' . $id_supplier . ' OR p.id_supplier = ' . $id_supplier);
        }
        $query->groupBy('p.id_product, pa.id_product_attribute');
        $items = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        foreach ($items as &$item) {
            $ids = explode('_', $item['id']);
            $prices = ProductSupplier::getProductSupplierPrice($ids[0], $ids[1], $id_supplier, true);
            if (count($prices)) {
                $item['unit_price_te'] = Tools::convertPriceFull($prices['product_supplier_price_te'], new Currency((int) $prices['id_currency']), new Currency($id_currency));
            }
        }
        if ($items) {
            die(Tools::jsonEncode($items));
        }
        die(1);
    }

 public function getOutstanding()
 {
     $query = new DbQuery();
     $query->select('SUM(oi.total_paid_tax_incl)');
     $query->from('order_invoice', 'oi');
     $query->leftJoin('orders', 'o', 'oi.id_order = o.id_order');
     $query->groupBy('o.id_customer');
     $query->where('o.id_customer = ' . (int) $this->id);
     $total_paid = (double) Db::getInstance()->getValue($query->build());
     $query = new DbQuery();
     $query->select('SUM(op.amount)');
     $query->from('order_payment', 'op');
     $query->leftJoin('order_invoice_payment', 'oip', 'op.id_order_payment = oip.id_order_payment');
     $query->leftJoin('orders', 'o', 'oip.id_order = o.id_order');
     $query->groupBy('o.id_customer');
     $query->where('o.id_customer = ' . (int) $this->id);
     $total_rest = (double) Db::getInstance()->getValue($query->build());
     return $total_paid - $total_rest;
 }

    private function renderFreeFilters()
    {
        // Multishop: selection
        // --------------------
        $sql = new DbQuery();
        $sql->select('shop_group.id_shop_group, shop_group.share_customer as shop_group_share_customer, shop_group.name as shop_group_name, 
			GROUP_CONCAT(DISTINCT shop.id_shop SEPARATOR \'|\') as shop_ids, GROUP_CONCAT(DISTINCT shop.name SEPARATOR \'|\') as shop_names,
			count(*) as shop_count');
        $sql->from('shop', 'shop');
        $sql->leftJoin('shop_group', 'shop_group', 'shop_group.id_shop_group = shop.id_shop_group');
        $sql->leftJoin('customer', 'customer', 'customer.id_shop = shop.id_shop');
        $sql->groupBy('if(shop_group.share_customer = 1, shop.id_shop_group, shop.id_shop)');
        $shops_by_sharing_shop_group = db::getInstance()->executeS($sql, true, false);
        foreach ($shops_by_sharing_shop_group as $key => $shop_group) {
            $shops_by_sharing_shop_group[$key]['shop_ids'] = explode('|', $shop_group['shop_ids']);
            $shops_by_sharing_shop_group[$key]['shop_names'] = explode('|', $shop_group['shop_names']);
        }
        // Multishop: checked shops and groups
        // -----------------------------------
        $sql = new DbQuery();
        $sql->select('shop_group_id, shop_id');
        $sql->from('expressmailing_email_shops_groups');
        $sql->where('campaign_id = ' . $this->campaign_id);
        $res = db::getInstance()->executeS($sql, true, false);
        $checked_groups_shops = array('shop_groups' => array(), 'shops' => array());
        foreach ($res as $row) {
            if (!in_array($row['shop_group_id'], $checked_groups_shops['shop_groups'])) {
                $checked_groups_shops['shop_groups'][] = $row['shop_group_id'];
            }
            if (!in_array($row['shop_id'], $checked_groups_shops['shops'])) {
                $checked_groups_shops['shops'][] = $row['shop_id'];
            }
        }
        // Include all groups and selected ones
        // ------------------------------------
        $sql = new DbQuery();
        $sql->select('CG.id_group, GL.name, XPM.group_id as checked, count(DISTINCT CG.id_customer) AS total');
        $sql->from('customer_group', 'CG');
        $sql->leftJoin('group_lang', 'GL', 'GL.id_group = CG.id_group');
        $sql->leftJoin('expressmailing_email_groups', 'XPM', 'XPM.campaign_id = ' . $this->campaign_id . ' AND XPM.group_id = CG.id_group');
        $sql->groupBy('CG.id_group');
        $customers_groups = db::getInstance()->executeS($sql, true, false);
        // We find all the languages and those selected
        // --------------------------------------------
        $sql = new DbQuery();
        $sql->select('CS.id_lang, LG.name, XPM.lang_id as checked, count(CS.id_customer) AS total');
        $sql->from('customer', 'CS');
        $sql->leftJoin('lang', 'LG', 'LG.id_lang = CS.id_lang');
        $sql->leftJoin('expressmailing_email_langs', 'XPM', 'XPM.campaign_id = ' . $this->campaign_id . ' AND XPM.lang_id = CS.id_lang');
        $sql->groupBy('CS.id_lang');
        $customers_langs = db::getInstance()->executeS($sql, true, false);
        // Count the number of optin subscribers
        // -------------------------------------
        $sql = new DbQuery();
        $sql->select('SUM(optin) as total_optin, SUM(newsletter) as total_newsletter, SUM(active) as total_active');
        $sql->from('customer');
        $customers_suscriptions = db::getInstance()->getRow($sql, false);
        // Count the number of guest subscribers
        // -------------------------------------
        $sql = new DbQuery();
        $sql->select('count(*) as total');
        $sql->where('active = 1');
        $sql->from('newsletter');
        $guest_newsletter = db::getInstance()->getRow($sql, false);
        $customers_suscriptions['total_guest'] = $guest_newsletter['total'];
        $this->context->smarty->assign(array('shops_by_sharing_shop_group' => $shops_by_sharing_shop_group, 'customers_groups' => $customers_groups, 'customers_langs' => $customers_langs, 'customers_suscriptions' => $customers_suscriptions, 'checked_groups_shops' => $checked_groups_shops));
        $template_path = $this->getTemplatePath() . 'marketinge_step4/filters_free.tpl';
        return $this->context->smarty->fetch($template_path);
    }

    public function getProducts($refresh = false, $id_product = false, $id_country = null)
    {
        /* 
         * EU-Legal
         * 1) correct calculation of prices -> Problem with inaccuracy at high number of items 
         * 2) assign standard delivery times to products
         */
        if (!$this->id) {
            return array();
        }
        // Product cache must be strictly compared to NULL, or else an empty cart will add dozens of queries
        if ($this->_products !== null && !$refresh) {
            // Return product row with specified ID if it exists
            if (is_int($id_product)) {
                foreach ($this->_products as $product) {
                    if ($product['id_product'] == $id_product) {
                        return array($product);
                    }
                }
                return array();
            }
            return $this->_products;
        }
        // Build query
        $sql = new DbQuery();
        // Build SELECT
        $sql->select('cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`,
						pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`delivery_now`, pl.`delivery_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` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`,
						p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category,
						CONCAT(LPAD(cp.`id_product`, 10, 0), LPAD(IFNULL(cp.`id_product_attribute`, 0), 10, 0), IFNULL(cp.`id_address_delivery`, 0)) 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');
        // Build FROM
        $sql->from('cart_product', 'cp');
        // Build JOIN
        $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`');
        // AND 'sp.`id_shop` = cp.`id_shop`
        // @todo test if everything is ok, then refactorise call of this method
        $sql->join(Product::sqlStock('cp', 'cp'));
        // Build WHERE clauses
        $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');
        // Build GROUP BY
        $sql->groupBy('unique_id');
        // Build ORDER BY
        $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');
            $sql->leftJoin('customization', 'cu', 'p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.`id_product_attribute` AND cu.`id_cart` = ' . (int) $this->id);
        } else {
            $sql->select('NULL AS customization_quantity, 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,
				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);
        // Reset the cache before the following return, or else an empty cart will add dozens of queries
        $products_ids = array();
        $pa_ids = array();
        if ($result) {
            foreach ($result as $row) {
                $products_ids[] = $row['id_product'];
                $pa_ids[] = $row['id_product_attribute'];
            }
        }
        // Thus you can avoid one query per product, because there will be only one query for all the products of the cart
        Product::cacheProductsFeatures($products_ids);
        Cart::cacheSomeAttributesLists($pa_ids, $this->id_lang);
        $this->_products = array();
        if (empty($result)) {
//.........这里部分代码省略.........

 public static function getUserEbooks($user_id, $just_id = false)
 {
     if ($user_id == null) {
         return null;
     }
     $user_id = pSQL($user_id);
     $lang_id = pSQL((int) Context::getContext()->language->id);
     $shop_id = pSQL((int) Context::getContext()->shop->id);
     $sql = new DbQuery();
     $sql->select('DISTINCT(PRSTEBK.product_id)');
     if ($just_id == false) {
         $sql->select('PRSTEBK.id_prestaebooks');
         $sql->select('PRSTEBK.ebook_author');
         $sql->select('PRSTEBK.ebook_isbn');
         $sql->select('PRDTLG.link_rewrite');
         $sql->select('PRDTLG.description');
         $sql->select('PRDTLG.name');
         $sql->select('PRDIMG.id_image');
     }
     $sql->from('prestaebooks', 'PRSTEBK');
     $sql->from('product_lang', 'PRDTLG');
     $sql->from('orders', 'ORD');
     $sql->from('order_detail', 'ORDDTL');
     $sql->from('image', 'PRDIMG');
     $sql->where('ORD.valid = 1');
     $sql->where('ORDDTL.product_id = PRSTEBK.product_id');
     $sql->where('ORDDTL.id_order = ORD.id_order');
     $sql->where('PRDTLG.id_product = PRSTEBK.product_id');
     $sql->where('PRDTLG.id_lang = "' . $lang_id . '"');
     $sql->where('PRDTLG.id_shop = "' . $shop_id . '"');
     $sql->where('PRDIMG.id_product = PRSTEBK.product_id');
     $sql->where('PRDIMG.cover = 1');
     $sql->where('ORD.id_customer = "' . $user_id . '"');
     $sql->orderBy('ORD.invoice_date DESC');
     $sql->groupBy('PRSTEBK.product_id');
     return Db::getInstance()->executeS($sql);
 }

 /**
  * Exports CSV
  */
 public function renderCSV()
 {
     if (count($this->_list) <= 0) {
         return;
     }
     // sets warehouse id and warehouse name
     $id_warehouse = (int) Tools::getValue('id_warehouse');
     $warehouse_name = Warehouse::getWarehouseNameById($id_warehouse);
     // if quantities requested
     if (Tools::isSubmit('csv_quantities')) {
         // filename
         $filename = $this->l('stock_instant_state_quantities') . '_' . $warehouse_name . '.csv';
         // header
         header('Content-type: text/csv');
         header('Cache-Control: no-store, no-cache must-revalidate');
         header('Content-disposition: attachment; filename="' . $filename);
         // puts keys
         $keys = array('id_product', 'id_product_attribute', 'reference', 'ean13', 'upc', 'name', 'physical_quantity', 'usable_quantity', 'real_quantity');
         echo sprintf("%s\n", implode(';', $keys));
         // puts rows
         foreach ($this->_list as $row) {
             $row_csv = array($row['id_product'], $row['id_product_attribute'], $row['reference'], $row['ean13'], $row['upc'], $row['name'], $row['physical_quantity'], $row['usable_quantity'], $row['real_quantity']);
             // puts one row
             echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv)));
         }
     } elseif (Tools::isSubmit('csv_prices')) {
         // sets filename
         $filename = $this->l('stock_instant_state_prices') . '_' . $warehouse_name . '.csv';
         // header
         header('Content-type: text/csv');
         header('Cache-Control: no-store, no-cache must-revalidate');
         header('Content-disposition: attachment; filename="' . $filename);
         // puts keys
         $keys = array('id_product', 'id_product_attribute', 'reference', 'ean13', 'upc', 'name', 'price_te', 'physical_quantity', 'usable_quantity');
         echo sprintf("%s\n", implode(';', $keys));
         foreach ($this->_list as $row) {
             $id_product = (int) $row['id_product'];
             $id_product_attribute = (int) $row['id_product_attribute'];
             // gets prices
             $query = new DbQuery();
             $query->select('s.price_te, SUM(s.physical_quantity) as physical_quantity, SUM(s.usable_quantity) as usable_quantity');
             $query->from('stock', 's');
             $query->leftJoin('warehouse', 'w', 'w.id_warehouse = s.id_warehouse');
             $query->where('s.id_product = ' . $id_product . ' AND s.id_product_attribute = ' . $id_product_attribute);
             $query->where('s.id_warehouse = ' . $id_warehouse);
             $query->groupBy('s.price_te');
             $datas = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
             // puts data
             foreach ($datas as $data) {
                 $row_csv = array($row['id_product'], $row['id_product_attribute'], $row['reference'], $row['ean13'], $row['upc'], $row['name'], $data['price_te'], $data['physical_quantity'], $data['usable_quantity']);
                 // puts one row
                 echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv)));
             }
         }
     }
 }

 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;
 }

 /**
  * Add GROUP BY restriction on query
  *
  * @param string $field Field name
  * @return Collection
  */
 public function groupBy($field)
 {
     $this->query->groupBy($this->parseField($field));
     return $this;
 }

    private function getAbandonedCartInfo()
    {
        if ((int) $this->cart_id < 1) {
            return false;
        }
        $query_limit = 0;
        $query_offset = 0;
        $cart_products = array();
        if ($this->page !== null && !empty($this->page) && $this->show !== null && !empty($this->show)) {
            $query_limit = ((int) $this->page - 1) * (int) $this->show;
            $query_offset = (int) $this->show;
        }
        // Get cart information
        $cart_info_obj = new DbQuery();
        $cart_info_obj->select("\r\n\t\t\tc.id_cart,\r\n\t\t\tc.date_add,\r\n\t\t\tc.id_currency,\r\n\t\t\tc.id_customer,\r\n\t\t\tcus.date_add AS account_registered,\r\n\t\t\tcus.email,\r\n\t\t\ta.phone,\r\n\t\t\tCONCAT(cus.firstname, ' ', cus.lastname) AS customer,\r\n\t\t\ts.name AS shop_name,\r\n\t\t\tcar.name AS carrier_name,\r\n\t\t\tSUM((ps.price + pas.price) * cp.quantity) AS cart_total\r\n\t\t");
        $cart_info_obj->from('cart', 'c');
        $cart_info_obj->innerJoin('cart_product', 'cp', 'cp.id_cart = c.id_cart');
        $cart_info_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
        $cart_info_obj->leftJoin('product_attribute_shop', 'pas', 'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop');
        $cart_info_obj->leftJoin('customer', 'cus', 'c.id_customer = cus.id_customer');
        $cart_info_obj->leftJoin('address', 'a', 'a.id_customer = cus.id_customer');
        $cart_info_obj->leftJoin('shop', 's', 's.id_shop = c.id_shop');
        $cart_info_obj->leftJoin('carrier', 'car', 'car.id_carrier = c.id_carrier');
        $cart_info_obj->where('c.id_cart = ' . (int) $this->cart_id);
        $cart_info_obj->groupBy('c.id_cart');
        $cart_info_sql = $cart_info_obj->build();
        $cart_info = Db::getInstance()->executeS($cart_info_sql);
        $cart_info = array_shift($cart_info);
        if (trim($cart_info['customer']) == '') {
            $cart_info['customer'] = self::GUEST;
        }
        // Convert and format price data
        if ($this->currency_code != $cart_info['id_currency']) {
            $cart_info['cart_total'] = $this->convertPrice($cart_info['cart_total'], $this->def_currency, $cart_info['id_currency']);
        }
        $cart_info['cart_total'] = $this->displayPrice($cart_info['cart_total'], $cart_info['id_currency']);
        // Get cart products
        $cart_products_obj = new DbQuery();
        $cart_products_obj->select('
			cp.id_product,
			cp.id_product_attribute,
			cp.quantity AS product_quantity,
			p.reference AS sku,
			(ps.price + pas.price) AS product_price,
			(ps.wholesale_price + pas.wholesale_price) AS wholesale_price,
			c.id_currency,
			pl.name AS product_name,
			pl.link_rewrite
		');
        $cart_products_obj->from('cart_product', 'cp');
        $cart_products_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop');
        $cart_products_obj->leftJoin('product_attribute_shop', 'pas', 'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop');
        $cart_products_obj->leftJoin('product', 'p', 'p.id_product = cp.id_product');
        $cart_products_obj->leftJoin('cart', 'c', 'c.id_cart = cp.id_cart');
        $cart_products_obj->leftJoin('product_lang', 'pl', 'pl.id_product = cp.id_product AND pl.id_shop = cp.id_shop AND pl.id_lang = ' . (int) $this->def_lang);
        $cart_products_obj->where('cp.id_cart = ' . (int) $this->cart_id);
        $cart_products_obj->limit($query_offset, $query_limit);
        $cart_products_sql = $cart_products_obj->build();
        $cart_products_res = Db::getInstance()->executeS($cart_products_sql);
        // Get attribute values
        foreach ($cart_products_res as $product) {
            $product_attributes_obj = new DbQuery();
            $product_attributes_obj->select('
				al.name AS attribute_value,
				agl.public_name AS attribute_name
			');
            $product_attributes_obj->from('product_attribute_combination', 'pac');
            $product_attributes_obj->leftJoin('attribute_lang', 'al', 'al.id_attribute = pac.id_attribute AND al.id_lang = ' . (int) $this->def_lang);
            $product_attributes_obj->leftJoin('attribute', 'a', 'a.id_attribute = pac.id_attribute');
            $product_attributes_obj->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $this->def_lang);
            $product_attributes_obj->where('pac.id_product_attribute = ' . (int) $product['id_product_attribute']);
            $product_attributes_obj->orderBy('attribute_name');
            $product_attributes_sql = $product_attributes_obj->build();
            $product_attributes = Db::getInstance()->executeS($product_attributes_sql);
            $product_attr = array();
            foreach ($product_attributes as $product_attribute) {
                $product_attr[] = $product_attribute['attribute_name'] . ' : ' . $product_attribute['attribute_value'];
            }
            $product['combination'] = !empty($product_attr) ? implode(', ', $product_attr) : '';
            // Convert and form price data
            if ($this->currency_code != $this->def_currency) {
                $product['product_price'] = $this->convertPrice($product['product_price'], $this->def_currency, $product['id_currency']);
                $product['wholesale_price'] = $this->convertPrice($product['wholesale_price'], $this->def_currency, $product['id_currency']);
            }
            $product['product_price'] = $this->displayPrice($product['product_price'], $product['id_currency']);
            $product['wholesale_price'] = $this->displayPrice($product['wholesale_price'], $product['id_currency']);
            // Get url of product image
            $image_url = $this->getProductImageUrl($product['id_product'], $product['link_rewrite']);
            if ($image_url) {
                $product['product_image'] = $image_url;
            }
            $cart_products[] = $product;
        }
        // Get cart product count
        $cart_product_count_obj = new DbQuery();
        $cart_product_count_obj->select('
			COUNT(cp.id_product) AS product_count
		');
        $cart_product_count_obj->from('cart_product', 'cp');
        $cart_product_count_obj->where('cp.id_cart = ' . (int) $this->cart_id);
//.........这里部分代码省略.........