PHP DbQuery::limit方法代码示例

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

 /**
  * Launch sql query to create collection of objects
  *
  * @param bool $display_query If true, query will be displayed (for debug purpose)
  * @return PrestaShopCollection
  */
 public function getAll($display_query = false)
 {
     if ($this->is_hydrated) {
         return $this;
     }
     $this->is_hydrated = true;
     $alias = $this->generateAlias();
     //$this->query->select($alias.'.*');
     $this->query->from($this->definition['table'], $alias);
     // If multilang, create association to lang table
     if (!empty($this->definition['multilang'])) {
         $this->join(self::LANG_ALIAS);
         if ($this->id_lang) {
             $this->where(self::LANG_ALIAS . '.id_lang', '=', $this->id_lang);
         }
     }
     // Add join clause
     foreach ($this->join_list as $data) {
         $on = '(' . implode(') AND (', $data['on']) . ')';
         switch ($data['type']) {
             case self::LEFT_JOIN:
                 $this->query->leftJoin($data['table'], $data['alias'], $on);
                 break;
             case self::INNER_JOIN:
                 $this->query->innerJoin($data['table'], $data['alias'], $on);
                 break;
             case self::LEFT_OUTER_JOIN:
                 $this->query->leftOuterJoin($data['table'], $data['alias'], $on);
                 break;
         }
     }
     // All limit clause
     if ($this->page_size) {
         $this->query->limit($this->page_size, $this->page_number * $this->page_size);
     }
     // Shall we display query for debug ?
     if ($display_query) {
         echo $this->query . '<br />';
     }
     $this->results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
     if ($this->results && is_array($this->results)) {
         $this->results = ObjectModel::hydrateCollection($this->classname, $this->results, $this->id_lang);
     }
     return $this;
 }

 public function displayAjax()
 {
     // Need a valid mailing-list id
     // ----------------------------
     if (empty($this->campaign_infos['campaign_api_list_id'])) {
         die('Empty campaign_api_list_id !');
     }
     // Get a bloc of 500 customers (formated)
     // --------------------------------------
     $req = new DbQuery();
     $req->select('*');
     $req->from('expressmailing_email_recipients');
     $req->where('campaign_id = ' . $this->campaign_id);
     $req->where('uploaded = \'0\'');
     $req->limit(1500);
     $recipients_list = Db::getInstance()->executeS($req, true, false);
     if (count($recipients_list) == 0) {
         if (Db::getInstance()->update('expressmailing_email', array('campaign_date_update' => date('Y-m-d H:i:s'), 'recipients_modified' => 0), 'campaign_id = ' . $this->campaign_id, 0, false, false)) {
             die('ended');
         }
     }
     $recipients = array();
     $uploaded_id = array();
     foreach ($recipients_list as $customer) {
         array_push($uploaded_id, $customer['id']);
         $recipients[] = array('target' => $customer['target'], 'lastname' => $customer['last_name'], 'firstname' => $customer['first_name'], 'ip_address' => $customer['ip_address'], 'last_connexion_date' => $customer['last_connexion_date']);
     }
     // Upload the bloc
     // ---------------
     $response_array = array();
     $parameters = array('account_id' => $this->session_api->account_id, 'list_id' => $this->campaign_infos['campaign_api_list_id'], 'recipients' => $recipients);
     if ($this->session_api->call('email', 'recipients', 'add', $parameters, $response_array)) {
         // Mark as uploaded the recipients treated
         // ---------------------------------------
         if (Db::getInstance()->update('expressmailing_email_recipients', array('uploaded' => '1'), 'campaign_id = ' . $this->campaign_id . ' AND id IN (' . implode(',', array_map('intval', $uploaded_id)) . ')', 0, false, false)) {
             die('continue');
         } else {
             echo Db::getInstance()->getMsgError();
         }
     }
     // Return the error to the AJAX process
     // ------------------------------------
     die(sprintf($this->module->l('Error during communication with Express-Mailing API : %s', 'adminmarketingestep6'), $this->session_api->getError()));
 }

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

    private function getRecipientsDB()
    {
        // Calcul nombre destinataires total
        // ---------------------------------
        $req = new DbQuery();
        $req->select('SQL_NO_CACHE SQL_CALC_FOUND_ROWS recipient.target, recipient.col_0,
					recipient.col_1, recipient.col_2, recipient.col_3, recipient.col_4, recipient.col_5');
        $req->from('expressmailing_sms_recipients', 'recipient');
        $req->where('recipient.campaign_id = ' . $this->campaign_id);
        $req->limit(20);
        $user_list = Db::getInstance()->executeS($req, true, false);
        $this->list_total = Db::getInstance()->getValue('SELECT FOUND_ROWS()', false);
        return $user_list;
    }

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

 private function getLocalCampaigns($campaign_state = null)
 {
     $req = new DbQuery();
     $req->select('campaign_id, campaign_state, campaign_date_update, campaign_date_send, campaign_name');
     $req->from($this->table);
     if ($campaign_state) {
         $req->where('campaign_state = ' . (string) $campaign_state);
     }
     $req->orderby('campaign_date_update DESC');
     $req->limit(5);
     $user_list = Db::getInstance()->executeS($req, true, false);
     return $user_list;
 }

 public static function getPosts($id_lang, $limit = 10, $id_simpleblog_category = null, $page = null, $active = true, $orderby = false, $orderway = false, $exclude = null, $featured = false, $author = false, $id_shop = null, $filter = false, $selected = array())
 {
     $context = Context::getContext();
     $start = $limit * ($page == 0 ? 0 : $page - 1);
     $sql = new DbQuery();
     $sql->select('*');
     $sql->from('simpleblog_post', 'sbp');
     if ($id_lang) {
         $sql->innerJoin('simpleblog_post_lang', 'l', 'sbp.id_simpleblog_post = l.id_simpleblog_post AND l.id_lang = ' . (int) $id_lang);
     }
     if (!$id_shop) {
         $id_shop = $context->shop->id;
     }
     $sql->innerJoin('simpleblog_post_shop', 'sbps', 'sbp.id_simpleblog_post = sbps.id_simpleblog_post AND sbps.id_shop = ' . (int) $id_shop);
     if ($active) {
         $sql->where('sbp.active = 1');
     }
     if (isset($id_simpleblog_category) && (int) $id_simpleblog_category > 0) {
         $sql->where('sbp.id_simpleblog_category = ' . (int) $id_simpleblog_category);
     }
     if ($exclude) {
         $sql->where('sbp.id_simpleblog_post != ' . (int) $exclude);
     }
     if ($featured) {
         $sql->where('sbp.is_featured = 1');
     }
     if ($author && Configuration::get('PH_BLOG_POST_BY_AUTHOR')) {
         $sql->where('sbp.author = \'' . pSQL($author) . '\'');
     }
     if ($filter) {
         $sql->where('sbp.id_simpleblog_post ' . $filter . ' (' . join(',', $selected) . ')');
     }
     if (isset($context->customer) && !$context->customer->isLogged()) {
         $sql->where('sbp.logged = 0');
     }
     if (!$orderby) {
         $orderby = 'sbp.id_simpleblog_post';
     }
     if (!$orderway) {
         $orderway = 'DESC';
     }
     $sql->orderBy($orderby . ' ' . $orderway);
     $sql->limit($limit, $start);
     $result = Db::getInstance()->executeS($sql);
     if (sizeof($result)) {
         foreach ($result as &$row) {
             $category_rewrite = SimpleBlogCategory::getRewriteByCategory($row['id_simpleblog_category'], $id_lang);
             $category_obj = new SimpleBlogCategory($row['id_simpleblog_category'], $id_lang);
             $category_url = SimpleBlogCategory::getLink($category_obj->link_rewrite, $id_lang);
             if (file_exists(_PS_MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '.' . $row['cover'])) {
                 $row['banner'] = _MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '.' . $row['cover'];
                 $row['banner_thumb'] = _MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '-thumb.' . $row['cover'];
                 $row['banner_wide'] = _MODULE_DIR_ . 'ph_simpleblog/covers/' . $row['id_simpleblog_post'] . '-wide.' . $row['cover'];
             }
             if (file_exists(_PS_MODULE_DIR_ . 'ph_simpleblog/featured/' . $row['id_simpleblog_post'] . '.' . $row['featured'])) {
                 $row['featured'] = _MODULE_DIR_ . 'ph_simpleblog/featured/' . $row['id_simpleblog_post'] . '.' . $row['featured'];
             }
             $row['url'] = self::getLink($row['link_rewrite'], $category_obj->link_rewrite, $id_lang);
             $row['category'] = $category_obj->name;
             $row['category_url'] = $category_url;
             $tags = SimpleBlogTag::getPostTags($row['id_simpleblog_post']);
             $row['tags'] = isset($tags[$id_lang]) && sizeof($tags[$id_lang] > 0) ? $tags[$id_lang] : false;
         }
     } else {
         return;
     }
     return $result;
 }

    private function getRecipientsDB()
    {
        // Count total recipients
        // ----------------------
        $req = new DbQuery();
        $req->select('SQL_NO_CACHE SQL_CALC_FOUND_ROWS	recipient.target,
														recipient.col_0, recipient.col_1, recipient.col_2,
														recipient.col_3, recipient.col_4, recipient.col_5');
        $req->from('expressmailing_fax_recipients', 'recipient');
        $req->where('recipient.campaign_id = ' . $this->campaign_id);
        $req->limit(20);
        $user_list = Db::getInstance()->executeS($req, true, false);
        $this->list_total = Db::getInstance()->getValue('SELECT FOUND_ROWS()', false);
        $this->fields_value['total_recipients'] = (string) $this->list_total;
        $formated_user_list = array();
        foreach ($user_list as $user) {
            $formated_user_list[] = $user;
        }
        return $formated_user_list;
    }

 public function displayAjax()
 {
     // Retrieve the import token
     // -------------------------
     $guid_import = (string) Tools::getValue('guid_import');
     if (empty($guid_import)) {
         die('ended');
     }
     // Get a bloc of 500 customers (formated)
     // --------------------------------------
     $req = new DbQuery();
     $req->select('*');
     $req->from('expressmailing_fax_recipients');
     $req->where('campaign_id = ' . $this->campaign_id);
     $req->where('uploaded = \'0\'');
     $req->limit(1500);
     $recipients_list = Db::getInstance()->executeS($req, true, false);
     if (count($recipients_list) == 0) {
         die($this->finalizeImport($guid_import));
     }
     $uploaded_id = array();
     $formated_recipients = array();
     foreach ($recipients_list as $recipient) {
         $uploaded_id[] = (int) $recipient['id'];
         $data = array('Target' => $recipient['target']);
         if (!empty($recipient['col_0'])) {
             $data['Col_0'] = $recipient['col_0'];
         }
         if (!empty($recipient['col_1'])) {
             $data['Col_1'] = $recipient['col_1'];
         }
         if (!empty($recipient['col_2'])) {
             $data['Col_2'] = $recipient['col_2'];
         }
         if (!empty($recipient['col_3'])) {
             $data['Col_3'] = $recipient['col_3'];
         }
         if (!empty($recipient['col_4'])) {
             $data['Col_4'] = $recipient['col_4'];
         }
         if (!empty($recipient['col_5'])) {
             $data['Col_5'] = $recipient['col_5'];
         }
         if (!empty($recipient['col_6'])) {
             $data['Col_6'] = $recipient['col_6'];
         }
         if (!empty($recipient['col_7'])) {
             $data['Col_7'] = $recipient['col_7'];
         }
         if (!empty($recipient['col_8'])) {
             $data['Col_8'] = $recipient['col_8'];
         }
         if (!empty($recipient['col_9'])) {
             $data['Col_9'] = $recipient['col_9'];
         }
         if (!empty($recipient['col_10'])) {
             $data['Col_10'] = $recipient['col_10'];
         }
         if (!empty($recipient['col_11'])) {
             $data['Col_11'] = $recipient['col_11'];
         }
         if (!empty($recipient['col_12'])) {
             $data['Col_12'] = $recipient['col_12'];
         }
         if (!empty($recipient['col_13'])) {
             $data['Col_13'] = $recipient['col_13'];
         }
         if (!empty($recipient['col_14'])) {
             $data['Col_14'] = $recipient['col_14'];
         }
         if (!empty($recipient['col_15'])) {
             $data['Col_15'] = $recipient['col_15'];
         }
         if (!empty($recipient['col_16'])) {
             $data['Col_16'] = $recipient['col_16'];
         }
         if (!empty($recipient['col_17'])) {
             $data['Col_17'] = $recipient['col_17'];
         }
         if (!empty($recipient['col_18'])) {
             $data['Col_18'] = $recipient['col_18'];
         }
         if (!empty($recipient['col_19'])) {
             $data['Col_19'] = $recipient['col_19'];
         }
         $formated_recipients[] = $data;
     }
     // Upload the bloc
     // ---------------
     $response_array = array();
     $parameters = array('operation' => $guid_import, 'recipients' => $formated_recipients);
     if ($this->session_api->call('fax', 'campaign', 'sendpart_recipients_from_file', $parameters, $response_array)) {
         // Mark as uploaded the recipients treated
         // ---------------------------------------
         if (Db::getInstance()->update('expressmailing_fax_recipients', array('uploaded' => '1'), 'campaign_id = ' . $this->campaign_id . ' AND id IN (' . join(',', $uploaded_id) . ')', 0, false, false)) {
             die('continue');
         }
         echo Db::getInstance()->getMsgError();
     }
     // Return the error to the AJAX process
//.........这里部分代码省略.........

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

 private function getRecipientsDB()
 {
     // Calculating the total number of recipients
     // ------------------------------------------
     $req = new DbQuery();
     $req->select('SQL_CALC_FOUND_ROWS id, lang_iso, target, last_name, first_name, group_name');
     $req->from('expressmailing_email_recipients');
     $req->where('campaign_id = ' . $this->campaign_id);
     $req->limit(10);
     $user_list = Db::getInstance()->executeS($req, true, false);
     $this->list_total = Db::getInstance()->getValue('SELECT FOUND_ROWS()', false);
     if (_PS_MODE_DEV_) {
         echo '<!-- OK getRecipientsDB (' . $this->list_total . ') -->' . "\r\n";
     }
     return $user_list;
 }

    public static function getCustomersSmsRequest($campaign_id, $checked_langs, $checked_groups, $checked_campaign_active, $checked_products, $checked_categories, $limit = 0, &$list_total = null)
    {
        $sql_calc_found = is_null($list_total) ? '' : 'SQL_CALC_FOUND_ROWS ';
        $req = new DbQuery();
        $req->select($sql_calc_found . (int) $campaign_id . ' as campaign_id, address.phone_mobile as target,
				address.phone_mobile as col_0, customer.lastname as col_1, customer.firstname as col_2,address.postcode as col_3,
				address.city as col_4, \'prestashop\' as source');
        $req->from('customer', 'customer');
        $req->leftJoin('customer_group', 'customer_group', 'customer_group.id_customer = customer.id_customer');
        $req->leftJoin('guest', 'guest', 'guest.id_customer = customer.id_customer');
        $req->leftJoin('connections', 'connections', 'connections.id_guest = guest.id_guest');
        $req->innerJoin('address', 'address', 'address.id_customer = customer.id_customer AND address.phone_mobile <> \'\'');
        $req->leftJoin('country', 'country', 'address.id_country = country.id_country');
        $where = array();
        $where[] = 'address.phone_mobile IS NOT NULL AND address.phone_mobile <> \'\'';
        if (!empty($checked_langs)) {
            $where[] = 'customer.id_lang IN(' . implode(', ', array_map('intval', $checked_langs)) . ')';
        }
        if (!empty($checked_groups)) {
            $where[] = 'customer_group.id_group IN(' . implode(', ', array_map('intval', $checked_groups)) . ')';
        }
        if ($checked_campaign_active) {
            $where[] = 'customer.active = 1';
        }
        if (!empty($checked_products) || !empty($checked_categories)) {
            $where_products_categories = array();
            $req->leftJoin('cart', 'cart', 'cart.id_customer = customer.id_customer');
            $req->leftJoin('cart_product', 'cart_product', 'cart_product.id_cart = cart.id_cart');
            if (!empty($checked_products)) {
                $where_products_categories[] = 'cart_product.id_product IN(' . implode(', ', array_map('intval', $checked_products)) . ')';
            }
            if (!empty($checked_categories)) {
                $req->leftJoin('category_product', 'category_product', 'category_product.id_product = cart_product.id_product');
                $where_products_categories[] = 'category_product.id_category IN(' . implode(', ', array_map('intval', $checked_categories)) . ')';
            }
            $where[] = implode(' OR ', $where_products_categories);
        }
        $req->where(implode(' AND ', $where));
        $req->orderby('customer.id_customer');
        $req->groupby('customer.id_customer');
        $limit = (int) $limit;
        if ($limit) {
            $req->limit($limit);
        }
        return $req;
    }

 /**
  * 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');
     }
     if (!Group::isFeatureActive()) {
         $all_groups = true;
     }
     $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);
     foreach ($suppliers as &$supplier) {
         $sup = new Supplier($supplier['id_supplier'], $id_lang);
         $association_results = $sup->getSupplierForAssociationInformation();
         $supplier['association_discount'] = $association_results[0]['discount'];
         $supplier['association_gain'] = $association_results[0]['gain'];
     }
     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';
         }
         $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
                 SELECT  ps.`id_supplier`, COUNT(DISTINCT ps.`id_product`) as nb_products
                 FROM `' . _DB_PREFIX_ . 'product_supplier` ps
                 JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`)
                 ' . Shop::addSqlAssociation('product', 'p') . '
                 LEFT JOIN `' . _DB_PREFIX_ . 'supplier` as m ON (m.`id_supplier`= p.`id_supplier`)
                 WHERE ps.id_product_attribute = 0' . ($active ? ' AND product_shop.`active` = 1' : '') . ' AND product_shop.`visibility` NOT IN ("none")' . ($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 . '
                 )') . '
                 GROUP BY ps.`id_supplier`');
         $counts = array();
         foreach ($results as $result) {
             $counts[(int) $result['id_supplier']] = (int) $result['nb_products'];
         }
         if (count($counts) && is_array($suppliers)) {
             foreach ($suppliers as $key => &$supplier) {
                 if (isset($counts[(int) $supplier['id_supplier']])) {
                     $suppliers[$key]['nb_products'] = $counts[(int) $supplier['id_supplier']];
                 } else {
                     $suppliers[$key]['nb_products'] = 0;
                 }
             }
         }
     }
     $nb_suppliers = count($suppliers);
     $rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS');
     for ($i = 0; $i < $nb_suppliers; $i++) {
         $suppliers[$i]['link_rewrite'] = $rewrite_settings ? Tools::link_rewrite($suppliers[$i]['name']) : 0;
     }
     return $suppliers;
 }

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