PHP DbQuery::leftJoin方法代码示例

function getDevices()
{
    $devices_obj = new DbQuery();
    $devices_obj->select('
		mpn.`id`,
		mpn.`new_order`,
		mpn.`new_customer`,
		mpn.`order_statuses`,
		mpn.`id_shop`,
		mpn.`app_connection_id`,
		mpn.`status`,
		mpn.`device_unique_id`,
		md.`account_email`,
		md.`device_name`,
		md.`last_activity`,
		c.`iso_code` AS currency_iso
	');
    $devices_obj->from('mobassistantconnector_push_notifications', 'mpn');
    $devices_obj->leftJoin('mobassistantconnector_devices', 'md', 'md.`device_unique_id` = mpn.`device_unique_id`');
    $devices_obj->leftJoin('currency', 'c', 'c.`id_currency` = mpn.`currency_code`');
    $devices_sql = $devices_obj->build();
    $devices = Db::getInstance()->executeS($devices_sql);
    if (!$devices) {
        $devices = array();
    }
    $devices = replaceNull($devices);
    $statuses_db = OrderState::getOrderStates(Configuration::get('PS_LANG_DEFAULT'));
    $count_statuses = count($statuses_db);
    $statuses = array();
    for ($i = 0; $i < $count_statuses; $i++) {
        $statuses[$statuses_db[$i]['id_order_state']] = $statuses_db[$i]['name'];
    }
    $devices = formDevices($devices, $statuses);
    return Tools::jsonEncode($devices);
}

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

    /**
     * Load ObjectModel
     * @param $id
     * @param $id_lang
     * @param $entity ObjectModel
     * @param $entity_defs
     * @param $id_shop
     * @param $should_cache_objects
     * @throws PrestaShopDatabaseException
     */
    public function load($id, $id_lang, $entity, $entity_defs, $id_shop, $should_cache_objects)
    {
        // Load object from database if object id is present
        $cache_id = 'objectmodel_' . $entity_defs['classname'] . '_' . (int) $id . '_' . (int) $id_shop . '_' . (int) $id_lang;
        if (!$should_cache_objects || !Cache::isStored($cache_id)) {
            $sql = new DbQuery();
            $sql->from($entity_defs['table'], 'a');
            $sql->where('a.`' . bqSQL($entity_defs['primary']) . '` = ' . (int) $id);
            // Get lang informations
            if ($id_lang && isset($entity_defs['multilang']) && $entity_defs['multilang']) {
                $sql->leftJoin($entity_defs['table'] . '_lang', 'b', 'a.`' . bqSQL($entity_defs['primary']) . '` = b.`' . bqSQL($entity_defs['primary']) . '` AND b.`id_lang` = ' . (int) $id_lang);
                if ($id_shop && !empty($entity_defs['multilang_shop'])) {
                    $sql->where('b.`id_shop` = ' . (int) $id_shop);
                }
            }
            // Get shop informations
            if (Shop::isTableAssociated($entity_defs['table'])) {
                $sql->leftJoin($entity_defs['table'] . '_shop', 'c', 'a.`' . bqSQL($entity_defs['primary']) . '` = c.`' . bqSQL($entity_defs['primary']) . '` AND c.`id_shop` = ' . (int) $id_shop);
            }
            if ($object_datas = Db::getInstance()->getRow($sql)) {
                if (!$id_lang && isset($entity_defs['multilang']) && $entity_defs['multilang']) {
                    $sql = 'SELECT *
							FROM `' . bqSQL(_DB_PREFIX_ . $entity_defs['table']) . '_lang`
							WHERE `' . bqSQL($entity_defs['primary']) . '` = ' . (int) $id . ($id_shop && $entity->isLangMultishop() ? ' AND `id_shop` = ' . (int) $id_shop : '');
                    if ($object_datas_lang = Db::getInstance()->executeS($sql)) {
                        foreach ($object_datas_lang as $row) {
                            foreach ($row as $key => $value) {
                                if ($key != $entity_defs['primary'] && array_key_exists($key, $entity)) {
                                    if (!isset($object_datas[$key]) || !is_array($object_datas[$key])) {
                                        $object_datas[$key] = array();
                                    }
                                    $object_datas[$key][$row['id_lang']] = $value;
                                }
                            }
                        }
                    }
                }
                $entity->id = (int) $id;
                foreach ($object_datas as $key => $value) {
                    if (array_key_exists($key, $entity)) {
                        $entity->{$key} = $value;
                    } else {
                        unset($object_datas[$key]);
                    }
                }
                if ($should_cache_objects) {
                    Cache::store($cache_id, $object_datas);
                }
            }
        } else {
            $object_datas = Cache::retrieve($cache_id);
            if ($object_datas) {
                $entity->id = (int) $id;
                foreach ($object_datas as $key => $value) {
                    $entity->{$key} = $value;
                }
            }
        }
    }

 public static function getCompleteLocation($id_product, $id_product_attribute, $id_warehouse)
 {
     // build query
     $query = new DbQuery();
     $query->select("CONCAT(area.name, ';', IFNULL(sub_area.name, '--'), ';', IF(location='', '--', location)) as CompleteArea");
     $query->from('warehouse_product_location', 'wpl');
     $query->leftJoin('erpip_warehouse_product_location', 'ewpl', 'ewpl.id_warehouse_product_location = wpl.id_warehouse_product_location');
     $query->leftJoin('erpip_zone', 'area', 'area.id_erpip_zone = ewpl.id_zone_parent');
     $query->leftJoin('erpip_zone', 'sub_area', 'sub_area.id_erpip_zone = ewpl.id_zone');
     $query->where('id_product = ' . (int) $id_product . ' AND id_product_attribute = ' . (int) $id_product_attribute . ' 
                         AND wpl.id_warehouse = ' . (int) $id_warehouse);
     return Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query);
 }

 protected function getQuantitySold($id_product, $id_product_attribute, $coverage)
 {
     $query = new DbQuery();
     $query->select('SUM(' . PP::sqlQty('product_quantity', 'od') . ')');
     $query->from('order_detail', 'od');
     $query->leftJoin('orders', 'o', 'od.id_order = o.id_order');
     $query->leftJoin('order_history', 'oh', 'o.date_upd = oh.date_add');
     $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
     $query->where('od.product_id = ' . (int) $id_product);
     $query->where('od.product_attribute_id = ' . (int) $id_product_attribute);
     $query->where('TO_DAYS(NOW()) - TO_DAYS(oh.date_add) <= ' . (int) $coverage);
     $query->where('o.valid = 1');
     $query->where('os.logable = 1 AND os.delivery = 1 AND os.shipped = 1');
     $quantity = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query);
     return $quantity;
 }

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

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

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

    protected function supplyOrdersDetailsImportOne($info, &$products, &$reset, $force_ids, $current_line, $validateOnly = false)
    {
        // sets default values if needed
        AdminImportController::setDefaultValues($info);
        // gets the supply order
        if (array_key_exists('supply_order_reference', $info) && pSQL($info['supply_order_reference']) && SupplyOrder::exists(pSQL($info['supply_order_reference']))) {
            $supply_order = SupplyOrder::getSupplyOrderByReference(pSQL($info['supply_order_reference']));
        } else {
            $this->errors[] = sprintf($this->l('Supply Order (%s) could not be loaded (at line %d).'), $info['supply_order_reference'], $current_line + 1);
        }
        if (empty($this->errors)) {
            // sets parameters
            $id_product = (int) $info['id_product'];
            if (!$info['id_product_attribute']) {
                $info['id_product_attribute'] = 0;
            }
            $id_product_attribute = (int) $info['id_product_attribute'];
            $unit_price_te = (double) $info['unit_price_te'];
            $quantity_expected = (int) $info['quantity_expected'];
            $discount_rate = (double) $info['discount_rate'];
            $tax_rate = (double) $info['tax_rate'];
            // checks if one product/attribute is there only once
            if (isset($products[$id_product][$id_product_attribute])) {
                $this->errors[] = sprintf($this->l('Product/Attribute (%d/%d) cannot be added twice (at line %d).'), $id_product, $id_product_attribute, $current_line + 1);
            } else {
                $products[$id_product][$id_product_attribute] = $quantity_expected;
            }
            // checks parameters
            if (false === ($supplier_reference = ProductSupplier::getProductSupplierReference($id_product, $id_product_attribute, $supply_order->id_supplier))) {
                $this->errors[] = sprintf($this->l('Product (%d/%d) is not available for this order (at line %d).'), $id_product, $id_product_attribute, $current_line + 1);
            }
            if ($unit_price_te < 0) {
                $this->errors[] = sprintf($this->l('Unit Price (tax excl.) (%d) is not valid (at line %d).'), $unit_price_te, $current_line + 1);
            }
            if ($quantity_expected < 0) {
                $this->errors[] = sprintf($this->l('Quantity Expected (%d) is not valid (at line %d).'), $quantity_expected, $current_line + 1);
            }
            if ($discount_rate < 0 || $discount_rate > 100) {
                $this->errors[] = sprintf($this->l('Discount rate (%d) is not valid (at line %d). %s.'), $discount_rate, $current_line + 1, $this->l('Format: Between 0 and 100'));
            }
            if ($tax_rate < 0 || $tax_rate > 100) {
                $this->errors[] = sprintf($this->l('Quantity Expected (%d) is not valid (at line %d).'), $tax_rate, $current_line + 1, $this->l('Format: Between 0 and 100'));
            }
            // if no errors, sets supply order details
            if (empty($this->errors)) {
                // resets order if needed
                if (!$validateOnly && $reset) {
                    $supply_order->resetProducts();
                    $reset = false;
                }
                // creates new product
                $supply_order_detail = new SupplyOrderDetail();
                AdminImportController::arrayWalk($info, array('AdminImportController', 'fillInfo'), $supply_order_detail);
                // sets parameters
                $supply_order_detail->id_supply_order = $supply_order->id;
                $currency = new Currency($supply_order->id_ref_currency);
                $supply_order_detail->id_currency = $currency->id;
                $supply_order_detail->exchange_rate = $currency->conversion_rate;
                $supply_order_detail->supplier_reference = $supplier_reference;
                $supply_order_detail->name = Product::getProductName($id_product, $id_product_attribute, $supply_order->id_lang);
                // gets ean13 / ref / upc
                $query = new DbQuery();
                $query->select('
					IFNULL(pa.reference, IFNULL(p.reference, \'\')) as reference,
					IFNULL(pa.ean13, IFNULL(p.ean13, \'\')) as ean13,
					IFNULL(pa.upc, IFNULL(p.upc, \'\')) as upc
				');
                $query->from('product', 'p');
                $query->leftJoin('product_attribute', 'pa', 'pa.id_product = p.id_product AND id_product_attribute = ' . (int) $id_product_attribute);
                $query->where('p.id_product = ' . (int) $id_product);
                $query->where('p.is_virtual = 0 AND p.cache_is_pack = 0');
                $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
                $product_infos = $res['0'];
                $supply_order_detail->reference = $product_infos['reference'];
                $supply_order_detail->ean13 = $product_infos['ean13'];
                $supply_order_detail->upc = $product_infos['upc'];
                $supply_order_detail->force_id = (bool) $force_ids;
                if (!$validateOnly) {
                    $supply_order_detail->add();
                    $supply_order->update();
                }
                unset($supply_order_detail);
            }
        }
    }

    public function searchProducts($query)
    {
        if (version_compare(_PS_VERSION_, '1.5', '<')) {
            $sql = '
				SELECT p.`id_product`, pl.`name`, p.`weight`
				FROM `' . _DB_PREFIX_ . 'category_product` cp
				LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (p.`id_product` = cp.`id_product`)
				LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = "' . (int) $this->context->language->id . '")
				WHERE pl.`name` LIKE \'%' . pSQL($query) . '%\'
					OR p.`ean13` LIKE \'%' . pSQL($query) . '%\'
					OR p.`upc` LIKE \'%' . pSQL($query) . '%\'
					OR p.`reference` LIKE \'%' . pSQL($query) . '%\'
					OR p.`supplier_reference` LIKE \'%' . pSQL($query) . '%\'
				GROUP BY `id_product`
				ORDER BY pl.`name` ASC
			';
        } else {
            $sql = new DbQuery();
            $sql->select('p.`id_product`, pl.`name`, p.`weight`');
            $sql->from('category_product', 'cp');
            $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`');
            $sql->join(Shop::addSqlAssociation('product', 'p'));
            $sql->leftJoin('product_lang', 'pl', '
				p.`id_product` = pl.`id_product`
				AND pl.`id_lang` = ' . (int) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl'));
            $where = 'pl.`name` LIKE \'%' . pSQL($query) . '%\'
			OR p.`ean13` LIKE \'%' . pSQL($query) . '%\'
			OR p.`upc` LIKE \'%' . pSQL($query) . '%\'
			OR p.`reference` LIKE \'%' . pSQL($query) . '%\'
			OR p.`supplier_reference` LIKE \'%' . pSQL($query) . '%\'
			OR  p.`id_product` IN (SELECT id_product FROM ' . _DB_PREFIX_ . 'product_supplier sp WHERE `product_supplier_reference` LIKE \'%' . pSQL($query) . '%\')';
            $sql->groupBy('`id_product`');
            $sql->orderBy('pl.`name` ASC');
            if (Combination::isFeatureActive()) {
                $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product` = p.`id_product`');
                $sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false));
                $where .= ' OR pa.`reference` LIKE \'%' . pSQL($query) . '%\'';
            }
            $sql->where($where);
        }
        $result = Db::getInstance()->executeS($sql);
        if (!$result) {
            return array('found' => false, 'notfound' => $this->l('No product has been found.'));
        }
        foreach ($result as &$product) {
            $product['id_product_attribute'] = Product::getDefaultAttribute($product['id_product']);
            $product['weight_numeric'] = $product['weight'];
            $product['weight'] = sprintf('%.3f', $product['weight']) . ' ' . _DPDPOLAND_DEFAULT_WEIGHT_UNIT_;
        }
        return array('products' => $result, 'found' => true);
    }

 public function getSubscribers()
 {
     $dbquery = new DbQuery();
     $dbquery->select('c.`id_customer` AS `id`, s.`name` AS `shop_name`, gl.`name` AS `gender`, c.`lastname`, c.`firstname`, c.`email`, c.`newsletter` AS `subscribed`, c.`newsletter_date_add`');
     $dbquery->from('customer', 'c');
     $dbquery->leftJoin('shop', 's', 's.id_shop = c.id_shop');
     $dbquery->leftJoin('gender', 'g', 'g.id_gender = c.id_gender');
     $dbquery->leftJoin('gender_lang', 'gl', 'g.id_gender = gl.id_gender AND gl.id_lang = ' . $this->context->employee->id_lang);
     $dbquery->where('c.`newsletter` = 1');
     if ($this->_searched_email) {
         $dbquery->where('c.`email` LIKE \'%' . bqSQL($this->_searched_email) . '%\' ');
     }
     $customers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($dbquery->build());
     $dbquery = new DbQuery();
     $dbquery->select('CONCAT(\'N\', n.`id`) AS `id`, s.`name` AS `shop_name`, NULL AS `gender`, NULL AS `lastname`, NULL AS `firstname`, n.`email`, n.`active` AS `subscribed`, n.`newsletter_date_add`');
     $dbquery->from('newsletter', 'n');
     $dbquery->leftJoin('shop', 's', 's.id_shop = n.id_shop');
     $dbquery->where('n.`active` = 1');
     if ($this->_searched_email) {
         $dbquery->where('n.`email` LIKE \'%' . bqSQL($this->_searched_email) . '%\' ');
     }
     $non_customers = Db::getInstance()->executeS($dbquery->build());
     $subscribers = array_merge($customers, $non_customers);
     return $subscribers;
 }

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

    /**
     * Loads products which quantity (hysical quantity) is equal or less than $threshold
     * @param int $threshold
     */
    protected function loadProducts($threshold)
    {
        // if there is already an order
        if (Tools::getValue('id_supply_order')) {
            $supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
        } else {
            // else, we just created a new order
            $supply_order = $this->object;
        }
        // if order is not valid, return;
        if (!Validate::isLoadedObject($supply_order)) {
            return;
        }
        // resets products if needed
        if (Tools::getValue('id_supply_order')) {
            $supply_order->resetProducts();
        }
        // gets products
        $query = new DbQuery();
        $query->select('
			ps.id_product,
			ps.id_product_attribute,
			ps.product_supplier_reference as supplier_reference,
			ps.product_supplier_price_te as unit_price_te,
			ps.id_currency,
			IFNULL(pa.reference, IFNULL(p.reference, \'\')) as reference,
			IFNULL(pa.ean13, IFNULL(p.ean13, \'\')) as ean13,
			IFNULL(pa.upc, IFNULL(p.upc, \'\')) as upc');
        $query->from('product_supplier', 'ps');
        $query->leftJoin('stock', 's', '
			s.id_product = ps.id_product
			AND s.id_product_attribute = ps.id_product_attribute
			AND s.id_warehouse = ' . (int) $supply_order->id_warehouse);
        $query->innerJoin('warehouse_product_location', 'wpl', '
			wpl.id_product = ps.id_product
			AND wpl.id_product_attribute = ps.id_product_attribute
			AND wpl.id_warehouse = ' . (int) $supply_order->id_warehouse . '
		');
        $query->leftJoin('product', 'p', 'p.id_product = ps.id_product');
        $query->leftJoin('product_attribute', 'pa', '
			pa.id_product_attribute = ps.id_product_attribute
			AND p.id_product = ps.id_product
		');
        $query->where('ps.id_supplier = ' . (int) $supply_order->id_supplier);
        // gets items
        $items = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
        // loads order currency
        $order_currency = new Currency($supply_order->id_currency);
        if (!Validate::isLoadedObject($order_currency)) {
            return;
        }
        $manager = StockManagerFactory::getManager();
        foreach ($items as $item) {
            $diff = (int) $threshold;
            if ($supply_order->is_template != 1) {
                $real_quantity = (int) $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], $supply_order->id_warehouse, true);
                $diff = (int) $threshold - (int) $real_quantity;
            }
            if ($diff >= 0) {
                // sets supply_order_detail
                $supply_order_detail = new SupplyOrderDetail();
                $supply_order_detail->id_supply_order = $supply_order->id;
                $supply_order_detail->id_currency = $order_currency->id;
                $supply_order_detail->id_product = $item['id_product'];
                $supply_order_detail->id_product_attribute = $item['id_product_attribute'];
                $supply_order_detail->reference = $item['reference'];
                $supply_order_detail->supplier_reference = $item['supplier_reference'];
                $supply_order_detail->name = Product::getProductName($item['id_product'], $item['id_product_attribute'], $supply_order->id_lang);
                $supply_order_detail->ean13 = $item['ean13'];
                $supply_order_detail->upc = $item['upc'];
                $supply_order_detail->quantity_expected = (int) $diff == 0 ? 1 : (int) $diff;
                $supply_order_detail->exchange_rate = $order_currency->conversion_rate;
                $product_currency = new Currency($item['id_currency']);
                if (Validate::isLoadedObject($product_currency)) {
                    $supply_order_detail->unit_price_te = Tools::convertPriceFull($item['unit_price_te'], $product_currency, $order_currency);
                } else {
                    $supply_order_detail->unit_price_te = 0;
                }
                $supply_order_detail->save();
                unset($product_currency);
            }
        }
        // updates supply order
        $supply_order->update();
    }

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