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; }
public function getProducts($refresh = false, $id_product = false, $id_country = null) { 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`, 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.`wholesale_price`, product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference'); // 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`'); // @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('p.`id_product`, cp.`id_product_attribute`, cp.`date_add` 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 '); $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'); } $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)) { return array(); } $cart_shop_context = Context::getContext()->cloneContext(); foreach ($result as &$row) { // $quantityDiscount = SpecificPrice::getQuantityDiscount((int)$row['id_product'], $row['id_shop'], // (int)$cart->id_currency, (int)$this->vat_address->id_country, // (int)$this->customer->id_default_group, (int)$row['cart_quantity'], false, null, null, $null, true, true, $this->context); // // echo '<pre>'; // print_r($quantityDiscount); // echo '</pre>'; if (isset($row['ecotax_attr']) && $row['ecotax_attr'] > 0) { $row['ecotax'] = (double) $row['ecotax_attr']; } $row['stock_quantity'] = (int) $row['quantity']; // for compatibility with 1.2 themes $row['quantity'] = (int) $row['cart_quantity']; if (isset($row['id_product_attribute']) && (int) $row['id_product_attribute'] && isset($row['weight_attribute'])) { $row['weight'] = (double) $row['weight_attribute']; } if (Configuration::get('PS_TAX_ADDRESS_TYPE') == 'id_address_invoice') { $address_id = (int) $this->id_address_invoice; } else { $address_id = (int) $row['id_address_delivery']; } if (!Address::addressExists($address_id)) { $address_id = null; } if ($cart_shop_context->shop->id != $row['id_shop']) { $cart_shop_context->shop = new Shop((int) $row['id_shop']); } if ($this->_taxCalculationMethod == PS_TAX_EXC) { $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 2, null, false, true, (int) $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $specific_price_output, true, true, $cart_shop_context); // Here taxes are computed only once the quantity has been applied to the product price $row['price_wt'] = Product::getPriceStatic((int) $row['id_product'], true, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 2, null, false, true, (int) $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $null, true, true, $cart_shop_context); $tax_rate = Tax::getProductTaxRate((int) $row['id_product'], (int) $address_id); $row['total_wt'] = Tools::ps_round($row['price'] * (double) $row['cart_quantity'] * (1 + (double) $tax_rate / 100), 2); $row['total'] = $row['price'] * (int) $row['cart_quantity']; } else { $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, (int) $row['id_product_attribute'], 2, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $specific_price_output, true, true, $cart_shop_context); $row['price_wt'] = Product::getPriceStatic((int) $row['id_product'], true, (int) $row['id_product_attribute'], 2, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $null, true, true, $cart_shop_context); // In case when you use QuantityDiscount, getPriceStatic() can be return more of 2 decimals $row['price_wt'] = Tools::ps_round($row['price_wt'], 2); $row['total_wt'] = $row['price_wt'] * (int) $row['cart_quantity']; $row['total'] = Tools::ps_round($row['price'] * (int) $row['cart_quantity'], 2); $row['description_short'] = Tools::nl2br($row['description_short']); } if (!isset($row['pai_id_image']) || $row['pai_id_image'] == 0) { $cache_id = 'Cart::getProducts_' . '-pai_id_image-' . (int) $row['id_product'] . '-' . (int) $this->id_lang . '-' . (int) $row['id_shop']; if (!Cache::isStored($cache_id)) { $row2 = Db::getInstance()->getRow(' SELECT image_shop.`id_image` id_image, il.`legend` FROM `' . _DB_PREFIX_ . 'image` i JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (i.id_image = image_shop.id_image AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $row['id_shop'] . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang . ') WHERE i.`id_product` = ' . (int) $row['id_product'] . ' AND image_shop.`cover` = 1'); Cache::store($cache_id, $row2); } $row2 = Cache::retrieve($cache_id); if (!$row2) { $row2 = array('id_image' => false, 'legend' => false); } else { $row = array_merge($row, $row2); } } else { $row['id_image'] = $row['pai_id_image']; $row['legend'] = $row['pai_legend']; } $row['reduction_applies'] = $specific_price_output && (double) $specific_price_output['reduction']; $row['quantity_discount_applies'] = $specific_price_output && $row['cart_quantity'] >= (int) $specific_price_output['from_quantity']; $row['id_image'] = Product::defineProductImage($row, $this->id_lang); $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']); $row['features'] = Product::getFeaturesStatic((int) $row['id_product']); if (array_key_exists($row['id_product_attribute'] . '-' . $this->id_lang, self::$_attributesLists)) { $row = array_merge($row, self::$_attributesLists[$row['id_product_attribute'] . '-' . $this->id_lang]); } if (Context::getContext()->language->id != 1) { $row['name'] = Tools::rus2translit($row['name']); } $row = Product::getTaxesInformations($row, $cart_shop_context); $this->_products[] = $row; } return $this->_products; }
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) { $products_ids[] = $row['id_product']; } // Thus you can avoid one query per product, because there will be only one query for all the products of the cart Product::cacheFrontFeatures($products_ids, $id_lang); return Product::getProductsProperties((int) $id_lang, $result); }
/** * 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; }
/** * Get list of modules we can execute per hook * * @since 1.5.0 * @param string $hook_name Get list of modules for this hook if given * @return array */ public static function getHookModuleExecList($hook_name = null) { $context = Context::getContext(); $cache_id = 'hook_module_exec_list_' . (isset($context->shop->id) ? '_' . $context->shop->id : '') . (isset($context->customer) ? '_' . $context->customer->id : ''); if (!Cache::isStored($cache_id) || $hook_name == 'displayPayment' || $hook_name == 'displayPaymentEU' || $hook_name == 'displayBackOfficeHeader') { $frontend = true; $groups = array(); $use_groups = Group::isFeatureActive(); if (isset($context->employee)) { $frontend = false; } else { // Get groups list if ($use_groups) { if (isset($context->customer) && $context->customer->isLogged()) { $groups = $context->customer->getGroups(); } elseif (isset($context->customer) && $context->customer->isLogged(true)) { $groups = array((int) Configuration::get('PS_GUEST_GROUP')); } else { $groups = array((int) Configuration::get('PS_UNIDENTIFIED_GROUP')); } } } // SQL Request $sql = new DbQuery(); $sql->select('h.`name` as hook, m.`id_module`, h.`id_hook`, m.`name` as module, h.`live_edit`'); $sql->from('module', 'm'); if ($hook_name != 'displayBackOfficeHeader') { $sql->join(Shop::addSqlAssociation('module', 'm', true, 'module_shop.enable_device & ' . (int) Context::getContext()->getDevice())); $sql->innerJoin('module_shop', 'ms', 'ms.`id_module` = m.`id_module`'); } $sql->innerJoin('hook_module', 'hm', 'hm.`id_module` = m.`id_module`'); $sql->innerJoin('hook', 'h', 'hm.`id_hook` = h.`id_hook`'); if ($hook_name != 'displayPayment' && $hook_name != 'displayPaymentEU') { $sql->where('h.`name` != "displayPayment" AND h.`name` != "displayPaymentEU"'); } elseif ($frontend) { if (Validate::isLoadedObject($context->country)) { $sql->where('((h.`name` = "displayPayment" OR h.`name` = "displayPaymentEU") AND (SELECT `id_country` FROM `' . _DB_PREFIX_ . 'module_country` mc WHERE mc.`id_module` = m.`id_module` AND `id_country` = ' . (int) $context->country->id . ' AND `id_shop` = ' . (int) $context->shop->id . ' LIMIT 1) = ' . (int) $context->country->id . ')'); } if (Validate::isLoadedObject($context->currency)) { $sql->where('((h.`name` = "displayPayment" OR h.`name` = "displayPaymentEU") AND (SELECT `id_currency` FROM `' . _DB_PREFIX_ . 'module_currency` mcr WHERE mcr.`id_module` = m.`id_module` AND `id_currency` IN (' . (int) $context->currency->id . ', -1, -2) LIMIT 1) IN (' . (int) $context->currency->id . ', -1, -2))'); } } if (Validate::isLoadedObject($context->shop)) { $sql->where('hm.`id_shop` = ' . (int) $context->shop->id); } if ($frontend) { if ($use_groups) { $sql->leftJoin('module_group', 'mg', 'mg.`id_module` = m.`id_module`'); if (Validate::isLoadedObject($context->shop)) { $sql->where('mg.`id_shop` = ' . (int) $context->shop->id . (count($groups) ? ' AND mg.`id_group` IN (' . implode(', ', $groups) . ')' : '')); } elseif (count($groups)) { $sql->where('mg.`id_group` IN (' . implode(', ', $groups) . ')'); } } } $sql->groupBy('hm.id_hook, hm.id_module'); $sql->orderBy('hm.`position`'); $list = array(); if ($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql)) { foreach ($result as $row) { $row['hook'] = strtolower($row['hook']); if (!isset($list[$row['hook']])) { $list[$row['hook']] = array(); } $list[$row['hook']][] = array('id_hook' => $row['id_hook'], 'module' => $row['module'], 'id_module' => $row['id_module'], 'live_edit' => $row['live_edit']); } } if ($hook_name != 'displayPayment' && $hook_name != 'displayPaymentEU' && $hook_name != 'displayBackOfficeHeader') { Cache::store($cache_id, $list); // @todo remove this in 1.6, we keep it in 1.5 for backward compatibility self::$_hook_modules_cache_exec = $list; } } else { $list = Cache::retrieve($cache_id); } // If hook_name is given, just get list of modules for this hook if ($hook_name) { $retro_hook_name = strtolower(Hook::getRetroHookName($hook_name)); $hook_name = strtolower($hook_name); $return = array(); $inserted_modules = array(); if (isset($list[$hook_name])) { $return = $list[$hook_name]; } foreach ($return as $module) { $inserted_modules[] = $module['id_module']; } if (isset($list[$retro_hook_name])) { foreach ($list[$retro_hook_name] as $retro_module_call) { if (!in_array($retro_module_call['id_module'], $inserted_modules)) { $return[] = $retro_module_call; } } } return count($return) > 0 ? $return : false; } else { return $list; } }
/** * Returns all menu items for a given shop * * @param int $id_lang * @param int $id_shop * * @return array */ public static function getMenuItems($id_lang, $id_shop) { $sql = new DbQuery(); $sql->select('i.*, il.url, il.title, il.name'); $sql->from('ct_top_menu_item', 'i'); $sql->leftJoin('ct_top_menu_item_lang', 'il', 'i.id_ct_top_menu_item = il.id_ct_top_menu_item AND il.id_lang = ' . (int) $id_lang); $sql->innerJoin('ct_top_menu_item_shop', 'ishop', 'i.id_ct_top_menu_item = ishop.id_ct_top_menu_item AND ishop.id_shop = ' . (int) $id_shop); $sql->orderBy('ishop.position ASC'); return (array) Db::getInstance()->executeS($sql); }
/** * Get list of parent categories * * @since 1.5.0 * @param int $id_lang * @return array */ public function getParentCategories($id_lang = null) { if (!$id_lang) { $id_lang = Context::getContext()->language->id; } $interval = Category::getInterval($this->id_category_default); $sql = new DbQuery(); $sql->from('category', 'c'); $sql->leftJoin('category_lang', 'cl', 'c.id_category = cl.id_category AND id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl')); $sql->where('c.nleft <= ' . (int) $interval['nleft'] . ' AND c.nright >= ' . (int) $interval['nright']); $sql->orderBy('c.nleft'); return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); }
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); $cart_product_count_sql = $cart_product_count_obj->build(); $cart_product_count_res = Db::getInstance()->executeS($cart_product_count_sql); $cart_product_count_res = array_shift($cart_product_count_res); return array('cart_info' => $cart_info, 'cart_products' => $cart_products, 'cart_products_count' => $cart_product_count_res['product_count']); }
public function getAffectedProducts($products = false) { $conditions_group = $this->getConditions(); $query = new DbQuery(); $query->select('p.id_product'); $query->from('product', 'p'); $query->join(Shop::addSqlAssociation('product', 'p')); $query->groupBy('p.id_product'); $attributes = false; $categories = false; $features = false; $suppliers = false; $where = '1'; if ($conditions_group) { $where .= ' AND ('; foreach ($conditions_group as $id_condition_group => $condition_group) { $fields = array('category' => array('name' => 'cp.id_category', 'values' => array()), 'manufacturer' => array('name' => 'p.id_manufacturer', 'values' => array()), 'supplier' => array('name' => 'pss.id_supplier', 'values' => array()), 'feature' => array('name' => 'fp.id_feature_value', 'values' => array()), 'attribute' => array('name' => 'pac.id_attribute', 'values' => array())); foreach ($condition_group as $condition) { if ($condition['type'] == 'category') { $categories = true; } elseif ($condition['type'] == 'feature') { $features = true; } elseif ($condition['type'] == 'attribute') { $attributes = true; } elseif ($condition['type'] == 'supplier') { $suppliers = true; } $fields[$condition['type']]['values'][] = $condition['value']; } foreach ($fields as $field) { if (!($n_conditions = count($field['values']))) { continue; } $where .= $field['name'] . ' IN (' . implode(',', array_map('intval', $field['values'])) . ') AND '; if ($n_conditions > 1) { $query->having('COUNT(' . bqSQL($field['name']) . ') >=' . (int) $n_conditions); } } $where = rtrim($where, ' AND ') . ') OR ('; } $where = rtrim($where, 'OR ('); } if ($products && count($products)) { $where .= ' AND p.id_product IN (' . implode(', ', array_map('intval', $products)) . ')'; } if ($attributes) { $query->select('pa.id_product_attribute'); $query->leftJoin('product_attribute', 'pa', 'p.id_product = pa.id_product'); $query->join(Shop::addSqlAssociation('product_attribute', 'pa', false)); $query->leftJoin('product_attribute_combination', 'pac', 'pa.id_product_attribute = pac.id_product_attribute'); $query->groupBy('pa.id_product_attribute'); } else { $query->select('NULL as id_product_attribute'); } if ($features) { $query->leftJoin('feature_product', 'fp', 'p.id_product = fp.id_product'); } if ($categories) { $query->leftJoin('category_product', 'cp', 'p.id_product = cp.id_product'); } if ($suppliers) { $query->leftJoin('product_supplier', 'pss', 'p.id_product = pss.id_product'); } if ($where) { $query->where($where); } return Db::getInstance()->executeS($query); }
/** * Webservice : gets the ids shops associated to this warehouse * @return array */ public function getWsShops() { $query = new DbQuery(); $query->select('ws.id_shop as id, s.name'); $query->from('warehouse_shop', 'ws'); $query->leftJoin('shop', 's', 's.id_shop = ws.id_shop'); $query->where($this->def['primary'] . ' = ' . (int) $this->id); $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); return $res; }
/** * ONLY FOR DEVELOPMENT PURPOSE */ public function getEntityContents($entity) { $xml = $this->loadEntity($entity); $primary = isset($xml->fields['primary']) && $xml->fields['primary'] ? (string) $xml->fields['primary'] : 'id_' . $entity; $is_multilang = $this->isMultilang($entity); // Check if current table is an association table (if multiple primary keys) $is_association = false; if (strpos($primary, ',') !== false) { $is_association = true; $primary = array_map('trim', explode(',', $primary)); } // Build query $sql = new DbQuery(); $sql->select('a.*'); $sql->from($entity, 'a'); if ($is_multilang) { $sql->select('b.*'); $sql->leftJoin($entity . '_lang', 'b', 'a.' . $primary . ' = b.' . $primary); } if (isset($xml->fields['sql']) && $xml->fields['sql']) { $sql->where((string) $xml->fields['sql']); } if (!$is_association) { $sql->select('a.' . $primary); if (!isset($xml->fields['ordersql']) || !$xml->fields['ordersql']) { $sql->orderBy('a.' . $primary); } } if ($is_multilang && (!isset($xml->fields['ordersql']) || !$xml->fields['ordersql'])) { $sql->orderBy('b.id_lang'); } if (isset($xml->fields['ordersql']) && $xml->fields['ordersql']) { $sql->orderBy((string) $xml->fields['ordersql']); } // Get multilang columns $alias_multilang = array(); if ($is_multilang) { $columns = $this->getColumns($entity); $multilang_columns = $this->getColumns($entity, true); // If some columns from _lang table have same name than original table, rename them (E.g. value in configuration) foreach ($multilang_columns as $c => $is_text) { if (isset($columns[$c])) { $alias = $c . '_alias'; $alias_multilang[$c] = $alias; $sql->select('a.' . $c . ' as ' . $c . ', b.' . $c . ' as ' . $alias); } } } // Get all results $nodes = $nodes_lang = array(); $results = Db::getInstance()->executeS($sql); if (Db::getInstance()->getNumberError()) { $this->setError($this->language->l('SQL error on query <i>%s</i>', $sql)); } else { foreach ($results as $row) { // Store common columns if ($is_association) { $id = $entity; foreach ($primary as $key) { $id .= '_' . $row[$key]; } } else { $id = $this->generateId($entity, $row[$primary], $row, isset($xml->fields['id']) && $xml->fields['id'] ? (string) $xml->fields['id'] : null); } if (!isset($nodes[$id])) { $node = array(); foreach ($xml->fields->field as $field) { $column = (string) $field['name']; if (isset($field['relation'])) { $sql = 'SELECT `id_' . bqSQL($field['relation']) . '` FROM `' . bqSQL(_DB_PREFIX_ . $field['relation']) . '` WHERE `id_' . bqSQL($field['relation']) . '` = ' . (int) $row[$column]; $node[$column] = $this->generateId((string) $field['relation'], Db::getInstance()->getValue($sql)); // A little trick to allow storage of some hard values, like '-1' for tab.id_parent if (!$node[$column] && $row[$column]) { $node[$column] = $row[$column]; } } else { $node[$column] = $row[$column]; } } $nodes[$id] = $node; } // Store multilang columns if ($is_multilang && $row['id_lang']) { $node = array(); foreach ($multilang_columns as $column => $is_text) { $node[$column] = $row[isset($alias_multilang[$column]) ? $alias_multilang[$column] : $column]; } $nodes_lang[$row['id_lang']][$id] = $node; } } } return array('nodes' => $nodes, 'nodes_lang' => $nodes_lang); }
/** * @static * @param int $id * @return array */ public static function getCategories($id = 0, $only_id = true) { // build query $query = new DbQuery(); if ($only_id) { $query->select('cs.`id_category`'); } else { $query->select('DISTINCT cs.`id_category`, cl.`name`, cl.`link_rewrite`'); } $query->from('category_shop', 'cs'); $query->leftJoin('category_lang', 'cl', 'cl.`id_category` = cs.`id_category` AND cl.`id_lang` = ' . (int) Context::getContext()->language->id); $query->where('cs.`id_shop` = ' . (int) $id); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); if ($only_id) { $array = array(); foreach ($result as $row) { $array[] = $row['id_category']; } $array = array_unique($array); } else { return $result; } return $array; }
/** * Return total to paid of sibling invoices * * @param int $mod TAX_EXCL, TAX_INCL, DETAIL * * @since 1.5.0.14 */ public function getSiblingTotal($mod = OrderInvoice::TAX_INCL) { $query = new DbQuery(); $query->select('SUM(oi.total_paid_tax_incl) as total_paid_tax_incl, SUM(oi.total_paid_tax_excl) as total_paid_tax_excl'); $query->from('order_invoice_payment', 'oip1'); $query->innerJoin('order_invoice_payment', 'oip2', 'oip2.id_order_payment = oip1.id_order_payment AND oip2.id_order_invoice <> oip1.id_order_invoice'); $query->leftJoin('order_invoice', 'oi', 'oi.id_order_invoice = oip2.id_order_invoice'); $query->where('oip1.id_order_invoice = ' . $this->id); $row = Db::getInstance()->getRow($query); switch ($mod) { case OrderInvoice::TAX_EXCL: return $row['total_paid_tax_excl']; case OrderInvoice::TAX_INCL: return $row['total_paid_tax_incl']; default: return $row; } }
/** * @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); } $client_orders_qty = 0; // check if product is present in a pack if (!Pack::isPack($id_product) && ($in_pack = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT id_product_pack, quantity FROM ' . _DB_PREFIX_ . 'pack WHERE id_product_item = ' . (int) $id_product . ' AND id_product_attribute_item = ' . ($id_product_attribute ? (int) $id_product_attribute : '0')))) { foreach ($in_pack as $value) { if (Validate::isLoadedObject($product = new Product((int) $value['id_product_pack'])) && ($product->pack_stock_type == 1 || $product->pack_stock_type == 2 || $product->pack_stock_type == 3 && Configuration::get('PS_PACK_STOCK_TYPE') > 0)) { $query = new DbQuery(); $query->select('od.product_quantity, od.product_quantity_refunded, pk.quantity'); $query->from('order_detail', 'od'); $query->leftjoin('orders', 'o', 'o.id_order = od.id_order'); $query->where('od.product_id = ' . (int) $value['id_product_pack']); $query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.id_order_state = o.current_state'); $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state'); $query->leftJoin('pack', 'pk', 'pk.id_product_item = ' . (int) $id_product . ' AND pk.id_product_attribute_item = ' . ($id_product_attribute ? (int) $id_product_attribute : '0') . ' AND id_product_pack = od.product_id'); $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') . ')'); $query->groupBy('od.id_order_detail'); if (count($ids_warehouse)) { $query->where('od.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')'); } $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); if (count($res)) { foreach ($res as $row) { $client_orders_qty += ($row['product_quantity'] - $row['product_quantity_refunded']) * $row['quantity']; } } } } } // skip if product is a pack without if (!Pack::isPack($id_product) || (Pack::isPack($id_product) && Validate::isLoadedObject($product = new Product((int) $id_product)) && $product->pack_stock_type == 0 || $product->pack_stock_type == 2 || $product->pack_stock_type == 3 && (Configuration::get('PS_PACK_STOCK_TYPE') == 0 || Configuration::get('PS_PACK_STOCK_TYPE') == 2))) { // Gets client_orders_qty $query = new DbQuery(); $query->select('od.product_quantity, 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.id_order_state = o.current_state'); $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') . ')'); $query->groupBy('od.id_order_detail'); if (count($ids_warehouse)) { $query->where('od.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')'); } $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); if (count($res)) { foreach ($res as $row) { $client_orders_qty += $row['product_quantity'] - $row['product_quantity_refunded']; } } } // 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; }
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)) { return array(); } $cart_shop_context = Context::getContext()->cloneContext(); foreach ($result as &$row) { if (isset($row['ecotax_attr']) && $row['ecotax_attr'] > 0) { $row['ecotax'] = (double) $row['ecotax_attr']; } $row['stock_quantity'] = (int) $row['quantity']; // for compatibility with 1.2 themes $row['quantity'] = (int) $row['cart_quantity']; if (isset($row['id_product_attribute']) && (int) $row['id_product_attribute'] && isset($row['weight_attribute'])) { $row['weight'] = (double) $row['weight_attribute']; } if (Configuration::get('PS_TAX_ADDRESS_TYPE') == 'id_address_invoice') { $address_id = (int) $this->id_address_invoice; } else { $address_id = (int) $row['id_address_delivery']; } if (!Address::addressExists($address_id)) { $address_id = null; } if ($cart_shop_context->shop->id != $row['id_shop']) { $cart_shop_context->shop = new Shop((int) $row['id_shop']); } $address = Address::initialize($address_id, true); $id_tax_rules_group = Product::getIdTaxRulesGroupByIdProduct((int) $row['id_product'], $cart_shop_context); $tax_calculator = TaxManagerFactory::getManager($address, $id_tax_rules_group)->getTaxCalculator(); $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 6, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, $address_id, $specific_price_output, false, true, $cart_shop_context); switch (Configuration::get('PS_ROUND_TYPE')) { case Order::ROUND_TOTAL: $row['total'] = $row['price'] * (int) $row['cart_quantity']; $row['total_wt'] = $tax_calculator->addTaxes($row['price']) * (int) $row['cart_quantity']; break; case Order::ROUND_LINE: $row['total'] = Tools::ps_round($row['price'] * (int) $row['cart_quantity'], _PS_PRICE_COMPUTE_PRECISION_); $row['total_wt'] = Tools::ps_round($tax_calculator->addTaxes($row['price']) * (int) $row['cart_quantity'], _PS_PRICE_COMPUTE_PRECISION_); break; case Order::ROUND_ITEM: default: $row['total'] = Tools::ps_round($row['price'], _PS_PRICE_COMPUTE_PRECISION_) * (int) $row['cart_quantity']; $row['total_wt'] = Tools::ps_round($tax_calculator->addTaxes($row['price']), _PS_PRICE_COMPUTE_PRECISION_) * (int) $row['cart_quantity']; break; } $row['price_wt'] = $tax_calculator->addTaxes($row['price']); $row['description_short'] = Tools::nl2br($row['description_short']); if (!isset($row['pai_id_image']) || $row['pai_id_image'] == 0) { $cache_id = 'Cart::getProducts_' . '-pai_id_image-' . (int) $row['id_product'] . '-' . (int) $this->id_lang . '-' . (int) $row['id_shop']; if (!Cache::isStored($cache_id)) { $row2 = Db::getInstance()->getRow(' SELECT image_shop.`id_image` id_image, il.`legend` FROM `' . _DB_PREFIX_ . 'image` i JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (i.id_image = image_shop.id_image AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $row['id_shop'] . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang . ') WHERE i.`id_product` = ' . (int) $row['id_product'] . ' AND image_shop.`cover` = 1'); Cache::store($cache_id, $row2); } $row2 = Cache::retrieve($cache_id); if (!$row2) { $row2 = array('id_image' => false, 'legend' => false); } else { $row = array_merge($row, $row2); } } else { $row['id_image'] = $row['pai_id_image']; $row['legend'] = $row['pai_legend']; } $row['reduction_applies'] = $specific_price_output && (double) $specific_price_output['reduction']; $row['quantity_discount_applies'] = $specific_price_output && $row['cart_quantity'] >= (int) $specific_price_output['from_quantity']; $row['id_image'] = Product::defineProductImage($row, $this->id_lang); $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']); $row['features'] = Product::getFeaturesStatic((int) $row['id_product']); if (array_key_exists($row['id_product_attribute'] . '-' . $this->id_lang, self::$_attributesLists)) { $row = array_merge($row, self::$_attributesLists[$row['id_product_attribute'] . '-' . $this->id_lang]); } /* * EU-Legal * assign standard delivery times to products */ $row['delivery_now'] = !empty($row['delivery_now']) ? $row['delivery_now'] : Configuration::get('LEGAL_DELIVERY_NOW', $this->id_lang); $row['delivery_later'] = !empty($row['delivery_later']) ? $row['delivery_later'] : Configuration::get('LEGAL_DELIVERY_LATER', $this->id_lang); $row = Product::getTaxesInformations($row, $cart_shop_context); $this->_products[] = $row; } return $this->_products; }
/** * Get all available taxes * * @return array Taxes */ public static function getTaxes($id_lang = false, $active_only = true) { $sql = new DbQuery(); $sql->select('t.id_tax, t.rate'); $sql->from('tax', 't'); $sql->where('t.`deleted` != 1'); if ($id_lang) { $sql->select('tl.name, tl.id_lang'); $sql->leftJoin('tax_lang', 'tl', 't.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $id_lang); $sql->orderBy('`name` ASC'); } if ($active_only) { $sql->where('t.`active` = 1'); } return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); }
/** * For a given $id_supplier, tells if it has pending supply orders * * @param int $id_supplier Id Supplier * @return bool */ public static function supplierHasPendingOrders($id_supplier) { if (!$id_supplier) { return false; } $query = new DbQuery(); $query->select('COUNT(so.id_supply_order) as supply_orders'); $query->from('supply_order', 'so'); $query->leftJoin('supply_order_state', 'sos', 'so.id_supply_order_state = sos.id_supply_order_state'); $query->where('sos.enclosed != 1'); $query->where('so.id_supplier = ' . (int) $id_supplier); $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); return $res > 0; }
/** * @static * @param int $id_shop * @return DbQuery */ public static function getIdByQuery($id_shop = 0) { $query = new DbQuery(); $query->select('c.id_currency'); $query->from('currency', 'c'); $query->where('deleted = 0'); if (Shop::isFeatureActive() && $id_shop > 0) { $query->leftJoin('currency_shop', 'cs', 'cs.id_currency = c.id_currency'); $query->where('id_shop = ' . (int) $id_shop); } return $query; }