/** * Return suppliers * * @return array Suppliers */ public static function getSuppliers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_groups = false) { if (!$id_lang) { $id_lang = Configuration::get('PS_LANG_DEFAULT'); } $query = new DbQuery(); $query->select('s.*, sl.`description`'); $query->from('supplier', 's'); $query->leftJoin('supplier_lang', 'sl', 's.`id_supplier` = sl.`id_supplier` AND sl.`id_lang` = ' . (int) $id_lang); $query->join(Shop::addSqlAssociation('supplier', 's')); if ($active) { $query->where('s.`active` = 1'); } $query->orderBy(' s.`name` ASC'); $query->limit($n, ($p - 1) * $n); $query->groupBy('s.id_supplier'); $suppliers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); if ($suppliers === false) { return false; } if ($get_nb_products) { $sql_groups = ''; if (!$all_groups) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'; } foreach ($suppliers as $key => $supplier) { $sql = ' SELECT DISTINCT(ps.`id_product`) FROM `' . _DB_PREFIX_ . 'product_supplier` ps JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`) ' . Shop::addSqlAssociation('product', 'p') . ' WHERE ps.`id_supplier` = ' . (int) $supplier['id_supplier'] . ' AND ps.id_product_attribute = 0' . ($active ? ' AND product_shop.`active` = 1' : '') . ($all_groups ? '' : ' AND ps.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` ' . $sql_groups . ' )'); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); $suppliers[$key]['nb_products'] = count($result); } } $nb_suppliers = count($suppliers); $rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS'); for ($i = 0; $i < $nb_suppliers; $i++) { if ($rewrite_settings) { $suppliers[$i]['link_rewrite'] = Tools::link_rewrite($suppliers[$i]['name'], false); } else { $suppliers[$i]['link_rewrite'] = 0; } } return $suppliers; }
/** * Method called when an ajax request is made * @see AdminController::postProcess() */ public function ajaxProcess() { if (Tools::isSubmit('id')) { $this->lang = false; $lang_id = (int) $this->context->language->id; $id_product = (int) Tools::getValue('id'); $period = Tools::getValue('period') ? (int) Tools::getValue('period') : 7; $warehouse = Tools::getValue('id_warehouse', -1); $where_warehouse = ''; if ($warehouse != -1) { $where_warehouse = ' AND s.id_warehouse = ' . (int) $warehouse; } $query = new DbQuery(); $query->select('pa.id_product_attribute as id, pa.id_product, stock_view.reference, stock_view.ean13, stock_view.upc, stock_view.usable_quantity as stock'); $query->from('product_attribute', 'pa'); $query->join('INNER JOIN ( SELECT SUM(s.usable_quantity) as usable_quantity, s.id_product_attribute, s.reference, s.ean13, s.upc FROM ' . _DB_PREFIX_ . 'stock s WHERE s.id_product = ' . $id_product . $where_warehouse . ' GROUP BY s.id_product_attribute ) stock_view ON (stock_view.id_product_attribute = pa.id_product_attribute)'); $query->where('pa.id_product = ' . $id_product); $query->groupBy('pa.id_product_attribute'); $datas = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($datas as &$data) { $data['name'] = Product::getProductName($data['id_product'], $data['id']); // computes coverage $coverage = StockManagerFactory::getManager()->getProductCoverage($data['id_product'], $data['id'], $period, $this->getCurrentCoverageWarehouse() == -1 ? null : $warehouse); if ($coverage != -1) { if ($coverage < $this->getCurrentWarning()) { // if highlight needed $data['color'] = '#BDE5F8'; } $data['coverage'] = $coverage; } else { // infinity $data['coverage'] = '--'; } // computes quantity sold $qty_sold = $this->getQuantitySold($data['id_product'], $data['id'], $this->getCurrentCoveragePeriod()); if (!$qty_sold) { $data['qty_sold'] = '--'; } else { $data['qty_sold'] = $qty_sold; } } echo Tools::jsonEncode(array('data' => $datas, 'fields_display' => $this->fields_list)); } die; }
public static function getIdsBadgesToValidate() { $ids = array(); $query = new DbQuery(); $query->select('b.`id_badge`'); $query->from('badge', 'b'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'condition_badge` cb ON cb.`id_badge` = b.`id_badge` LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = cb.`id_condition` AND c.`validated` = 1'); $query->where('b.validated = 0'); $query->groupBy('b.`id_badge`'); $query->having('count(*) = SUM(c.validated)'); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($result as $badge) { $ids[] = $badge['id_badge']; } return $ids; }
public static function getIdsAdviceToUnvalidate() { $ids = array(); $query = new DbQuery(); $query->select('a.`id_advice`'); $query->from('advice', 'a'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'condition_advice` ca ON ca.`id_advice` = a.`id_advice` AND ca.`display` = 0 LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = ca.`id_condition` AND c.`validated` = 1'); $query->where('a.validated = 1'); $query->groupBy('a.`id_advice`'); $query->having('count(*) = SUM(c.validated)'); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($result as $advice) { $ids[] = $advice['id_advice']; } return $ids; }
protected function getTaxOrderSummary() { $query = new DbQuery(); $query->select(' SUM(price_with_order_discount_te) as base_te, tax_rate, SUM(tax_value_with_order_discount) as total_tax_value '); $query->from('supply_order_detail'); $query->where('id_supply_order = ' . (int) $this->supply_order->id); $query->groupBy('tax_rate'); $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($results as &$result) { $result['base_te'] = Tools::ps_round($result['base_te'], 2); $result['tax_rate'] = Tools::ps_round($result['tax_rate'], 2); $result['total_tax_value'] = Tools::ps_round($result['total_tax_value'], 2); } unset($result); // remove reference return $results; }
public static function getMovementsByIds($ids) { // Reformatting datas if multiple IDs separated by '|' and protection if (strstr($ids, '|')) { $idsList = explode('|', $ids); array_walk($idsList, 'intval'); $ids = implode(',', $idsList); } else { $ids = pSQL($ids); } $query = new DbQuery(); $query->select(' IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name, IFNULL(pa.reference, s.reference) as reference, sm.physical_quantity, m.name as manufacturer_name '); $query->from('stock_mvt', 'sm'); $query->join(' INNER JOIN `' . _DB_PREFIX_ . 'stock` s ON s.id_stock = sm.id_stock'); $query->join('INNER JOIN ' . _DB_PREFIX_ . 'product p ON s.id_product = p.id_product '); $query->join('LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute pa ON s.id_product_attribute = pa.id_product_attribute '); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( s.id_product = pl.id_product AND pl.id_lang = ' . (int) Context::getContext()->language->id . ' )'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = s.id_product_attribute)'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'attribute` atr ON (atr.id_attribute = pac.id_attribute)'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON ( al.id_attribute = pac.id_attribute AND al.id_lang = ' . (int) Context::getContext()->language->id . ' )'); $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON ( agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) Context::getContext()->language->id . ' )'); $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.id_manufacturer = p.id_manufacturer'); $query->where("sm.id_stock_mvt IN ({$ids})"); $query->groupBy('s.id_product, s.id_product_attribute'); return Db::getInstance()->executeS($query); }
/** * method call when ajax request is made for search product to add to the order * @TODO - Update this method to retreive the reference, ean13, upc corresponding to a product attribute */ public function ajaxProcessSearchProduct() { // Get the search pattern $pattern = pSQL(Tools::getValue('q', false)); if (!$pattern || $pattern == '' || strlen($pattern) < 1) { die; } // get supplier id $id_supplier = (int) Tools::getValue('id_supplier', false); // gets the currency $id_currency = (int) Tools::getValue('id_currency', false); // get lang from context $id_lang = (int) Context::getContext()->language->id; $query = new DbQuery(); $query->select(' CONCAT(p.id_product, \'_\', IFNULL(pa.id_product_attribute, \'0\')) as id, ps.product_supplier_reference as supplier_reference, IFNULL(pa.reference, IFNULL(p.reference, \'\')) as reference, IFNULL(pa.ean13, IFNULL(p.ean13, \'\')) as ean13, IFNULL(pa.upc, IFNULL(p.upc, \'\')) as upc, md5(CONCAT(\'' . _COOKIE_KEY_ . '\', p.id_product, \'_\', IFNULL(pa.id_product_attribute, \'0\'))) as checksum, IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.name, \' - \', al.name order by agl.name SEPARATOR \', \')), pl.name) as name '); $query->from('product', 'p'); $query->innerJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . $id_lang); $query->leftJoin('product_attribute', 'pa', 'pa.id_product = p.id_product'); $query->leftJoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute'); $query->leftJoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute'); $query->leftJoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . $id_lang); $query->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . $id_lang); $query->leftJoin('product_supplier', 'ps', 'ps.id_product = p.id_product AND ps.id_product_attribute = IFNULL(pa.id_product_attribute, 0)'); $query->where('(pl.name LIKE \'%' . $pattern . '%\' OR p.reference LIKE \'%' . $pattern . '%\' OR ps.product_supplier_reference LIKE \'%' . $pattern . '%\')'); $query->where('p.id_product NOT IN (SELECT pd.id_product FROM `' . _DB_PREFIX_ . 'product_download` pd WHERE (pd.id_product = p.id_product))'); $query->where('p.is_virtual = 0 AND p.cache_is_pack = 0'); if ($id_supplier) { $query->where('ps.id_supplier = ' . $id_supplier . ' OR p.id_supplier = ' . $id_supplier); } $query->groupBy('p.id_product, pa.id_product_attribute'); $items = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($items as &$item) { $ids = explode('_', $item['id']); $prices = ProductSupplier::getProductSupplierPrice($ids[0], $ids[1], $id_supplier, true); if (count($prices)) { $item['unit_price_te'] = Tools::convertPriceFull($prices['product_supplier_price_te'], new Currency((int) $prices['id_currency']), new Currency($id_currency)); } } if ($items) { die(Tools::jsonEncode($items)); } die(1); }
public function getOutstanding() { $query = new DbQuery(); $query->select('SUM(oi.total_paid_tax_incl)'); $query->from('order_invoice', 'oi'); $query->leftJoin('orders', 'o', 'oi.id_order = o.id_order'); $query->groupBy('o.id_customer'); $query->where('o.id_customer = ' . (int) $this->id); $total_paid = (double) Db::getInstance()->getValue($query->build()); $query = new DbQuery(); $query->select('SUM(op.amount)'); $query->from('order_payment', 'op'); $query->leftJoin('order_invoice_payment', 'oip', 'op.id_order_payment = oip.id_order_payment'); $query->leftJoin('orders', 'o', 'oip.id_order = o.id_order'); $query->groupBy('o.id_customer'); $query->where('o.id_customer = ' . (int) $this->id); $total_rest = (double) Db::getInstance()->getValue($query->build()); return $total_paid - $total_rest; }
private function renderFreeFilters() { // Multishop: selection // -------------------- $sql = new DbQuery(); $sql->select('shop_group.id_shop_group, shop_group.share_customer as shop_group_share_customer, shop_group.name as shop_group_name, GROUP_CONCAT(DISTINCT shop.id_shop SEPARATOR \'|\') as shop_ids, GROUP_CONCAT(DISTINCT shop.name SEPARATOR \'|\') as shop_names, count(*) as shop_count'); $sql->from('shop', 'shop'); $sql->leftJoin('shop_group', 'shop_group', 'shop_group.id_shop_group = shop.id_shop_group'); $sql->leftJoin('customer', 'customer', 'customer.id_shop = shop.id_shop'); $sql->groupBy('if(shop_group.share_customer = 1, shop.id_shop_group, shop.id_shop)'); $shops_by_sharing_shop_group = db::getInstance()->executeS($sql, true, false); foreach ($shops_by_sharing_shop_group as $key => $shop_group) { $shops_by_sharing_shop_group[$key]['shop_ids'] = explode('|', $shop_group['shop_ids']); $shops_by_sharing_shop_group[$key]['shop_names'] = explode('|', $shop_group['shop_names']); } // Multishop: checked shops and groups // ----------------------------------- $sql = new DbQuery(); $sql->select('shop_group_id, shop_id'); $sql->from('expressmailing_email_shops_groups'); $sql->where('campaign_id = ' . $this->campaign_id); $res = db::getInstance()->executeS($sql, true, false); $checked_groups_shops = array('shop_groups' => array(), 'shops' => array()); foreach ($res as $row) { if (!in_array($row['shop_group_id'], $checked_groups_shops['shop_groups'])) { $checked_groups_shops['shop_groups'][] = $row['shop_group_id']; } if (!in_array($row['shop_id'], $checked_groups_shops['shops'])) { $checked_groups_shops['shops'][] = $row['shop_id']; } } // Include all groups and selected ones // ------------------------------------ $sql = new DbQuery(); $sql->select('CG.id_group, GL.name, XPM.group_id as checked, count(DISTINCT CG.id_customer) AS total'); $sql->from('customer_group', 'CG'); $sql->leftJoin('group_lang', 'GL', 'GL.id_group = CG.id_group'); $sql->leftJoin('expressmailing_email_groups', 'XPM', 'XPM.campaign_id = ' . $this->campaign_id . ' AND XPM.group_id = CG.id_group'); $sql->groupBy('CG.id_group'); $customers_groups = db::getInstance()->executeS($sql, true, false); // We find all the languages and those selected // -------------------------------------------- $sql = new DbQuery(); $sql->select('CS.id_lang, LG.name, XPM.lang_id as checked, count(CS.id_customer) AS total'); $sql->from('customer', 'CS'); $sql->leftJoin('lang', 'LG', 'LG.id_lang = CS.id_lang'); $sql->leftJoin('expressmailing_email_langs', 'XPM', 'XPM.campaign_id = ' . $this->campaign_id . ' AND XPM.lang_id = CS.id_lang'); $sql->groupBy('CS.id_lang'); $customers_langs = db::getInstance()->executeS($sql, true, false); // Count the number of optin subscribers // ------------------------------------- $sql = new DbQuery(); $sql->select('SUM(optin) as total_optin, SUM(newsletter) as total_newsletter, SUM(active) as total_active'); $sql->from('customer'); $customers_suscriptions = db::getInstance()->getRow($sql, false); // Count the number of guest subscribers // ------------------------------------- $sql = new DbQuery(); $sql->select('count(*) as total'); $sql->where('active = 1'); $sql->from('newsletter'); $guest_newsletter = db::getInstance()->getRow($sql, false); $customers_suscriptions['total_guest'] = $guest_newsletter['total']; $this->context->smarty->assign(array('shops_by_sharing_shop_group' => $shops_by_sharing_shop_group, 'customers_groups' => $customers_groups, 'customers_langs' => $customers_langs, 'customers_suscriptions' => $customers_suscriptions, 'checked_groups_shops' => $checked_groups_shops)); $template_path = $this->getTemplatePath() . 'marketinge_step4/filters_free.tpl'; return $this->context->smarty->fetch($template_path); }
public function getProducts($refresh = false, $id_product = false, $id_country = null) { /* * EU-Legal * 1) correct calculation of prices -> Problem with inaccuracy at high number of items * 2) assign standard delivery times to products */ if (!$this->id) { return array(); } // Product cache must be strictly compared to NULL, or else an empty cart will add dozens of queries if ($this->_products !== null && !$refresh) { // Return product row with specified ID if it exists if (is_int($id_product)) { foreach ($this->_products as $product) { if ($product['id_product'] == $id_product) { return array($product); } } return array(); } return $this->_products; } // Build query $sql = new DbQuery(); // Build SELECT $sql->select('cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`delivery_now`, pl.`delivery_later`, product_shop.`id_category_default`, p.`id_supplier`, p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`, product_shop.`available_for_order`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`, stock.`quantity` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`, p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category, CONCAT(LPAD(cp.`id_product`, 10, 0), LPAD(IFNULL(cp.`id_product_attribute`, 0), 10, 0), IFNULL(cp.`id_address_delivery`, 0)) AS unique_id, cp.id_address_delivery, product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference, IFNULL(sp.`reduction_type`, 0) AS reduction_type'); // Build FROM $sql->from('cart_product', 'cp'); // Build JOIN $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`'); $sql->innerJoin('product_shop', 'product_shop', '(product_shop.`id_shop` = cp.`id_shop` AND product_shop.`id_product` = p.`id_product`)'); $sql->leftJoin('product_lang', 'pl', ' p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('pl', 'cp.id_shop')); $sql->leftJoin('category_lang', 'cl', ' product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('cl', 'cp.id_shop')); $sql->leftJoin('product_supplier', 'ps', 'ps.`id_product` = cp.`id_product` AND ps.`id_product_attribute` = cp.`id_product_attribute` AND ps.`id_supplier` = p.`id_supplier`'); $sql->leftJoin('specific_price', 'sp', 'sp.`id_product` = cp.`id_product`'); // AND 'sp.`id_shop` = cp.`id_shop` // @todo test if everything is ok, then refactorise call of this method $sql->join(Product::sqlStock('cp', 'cp')); // Build WHERE clauses $sql->where('cp.`id_cart` = ' . (int) $this->id); if ($id_product) { $sql->where('cp.`id_product` = ' . (int) $id_product); } $sql->where('p.`id_product` IS NOT NULL'); // Build GROUP BY $sql->groupBy('unique_id'); // Build ORDER BY $sql->orderBy('cp.`date_add`, p.`id_product`, cp.`id_product_attribute` ASC'); if (Customization::isFeatureActive()) { $sql->select('cu.`id_customization`, cu.`quantity` AS customization_quantity'); $sql->leftJoin('customization', 'cu', 'p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.`id_product_attribute` AND cu.`id_cart` = ' . (int) $this->id); } else { $sql->select('NULL AS customization_quantity, NULL AS id_customization'); } if (Combination::isFeatureActive()) { $sql->select(' product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr, IF (IFNULL(pa.`reference`, \'\') = \'\', p.`reference`, pa.`reference`) AS reference, (p.`weight`+ pa.`weight`) weight_attribute, IF (IFNULL(pa.`ean13`, \'\') = \'\', p.`ean13`, pa.`ean13`) AS ean13, IF (IFNULL(pa.`upc`, \'\') = \'\', p.`upc`, pa.`upc`) AS upc, pai.`id_image` as pai_id_image, il.`legend` as pai_legend, IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity, IF(product_attribute_shop.wholesale_price > 0, product_attribute_shop.wholesale_price, product_shop.`wholesale_price`) wholesale_price '); $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product_attribute` = cp.`id_product_attribute`'); $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', '(product_attribute_shop.`id_shop` = cp.`id_shop` AND product_attribute_shop.`id_product_attribute` = pa.`id_product_attribute`)'); $sql->leftJoin('product_attribute_image', 'pai', 'pai.`id_product_attribute` = pa.`id_product_attribute`'); $sql->leftJoin('image_lang', 'il', 'il.`id_image` = pai.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang); } else { $sql->select('p.`reference` AS reference, p.`ean13`, p.`upc` AS upc, product_shop.`minimal_quantity` AS minimal_quantity, product_shop.`wholesale_price` wholesale_price'); } $result = Db::getInstance()->executeS($sql); // Reset the cache before the following return, or else an empty cart will add dozens of queries $products_ids = array(); $pa_ids = array(); if ($result) { foreach ($result as $row) { $products_ids[] = $row['id_product']; $pa_ids[] = $row['id_product_attribute']; } } // Thus you can avoid one query per product, because there will be only one query for all the products of the cart Product::cacheProductsFeatures($products_ids); Cart::cacheSomeAttributesLists($pa_ids, $this->id_lang); $this->_products = array(); if (empty($result)) { 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; }
public static function getUserEbooks($user_id, $just_id = false) { if ($user_id == null) { return null; } $user_id = pSQL($user_id); $lang_id = pSQL((int) Context::getContext()->language->id); $shop_id = pSQL((int) Context::getContext()->shop->id); $sql = new DbQuery(); $sql->select('DISTINCT(PRSTEBK.product_id)'); if ($just_id == false) { $sql->select('PRSTEBK.id_prestaebooks'); $sql->select('PRSTEBK.ebook_author'); $sql->select('PRSTEBK.ebook_isbn'); $sql->select('PRDTLG.link_rewrite'); $sql->select('PRDTLG.description'); $sql->select('PRDTLG.name'); $sql->select('PRDIMG.id_image'); } $sql->from('prestaebooks', 'PRSTEBK'); $sql->from('product_lang', 'PRDTLG'); $sql->from('orders', 'ORD'); $sql->from('order_detail', 'ORDDTL'); $sql->from('image', 'PRDIMG'); $sql->where('ORD.valid = 1'); $sql->where('ORDDTL.product_id = PRSTEBK.product_id'); $sql->where('ORDDTL.id_order = ORD.id_order'); $sql->where('PRDTLG.id_product = PRSTEBK.product_id'); $sql->where('PRDTLG.id_lang = "' . $lang_id . '"'); $sql->where('PRDTLG.id_shop = "' . $shop_id . '"'); $sql->where('PRDIMG.id_product = PRSTEBK.product_id'); $sql->where('PRDIMG.cover = 1'); $sql->where('ORD.id_customer = "' . $user_id . '"'); $sql->orderBy('ORD.invoice_date DESC'); $sql->groupBy('PRSTEBK.product_id'); return Db::getInstance()->executeS($sql); }
/** * Exports CSV */ public function renderCSV() { if (count($this->_list) <= 0) { return; } // sets warehouse id and warehouse name $id_warehouse = (int) Tools::getValue('id_warehouse'); $warehouse_name = Warehouse::getWarehouseNameById($id_warehouse); // if quantities requested if (Tools::isSubmit('csv_quantities')) { // filename $filename = $this->l('stock_instant_state_quantities') . '_' . $warehouse_name . '.csv'; // header header('Content-type: text/csv'); header('Cache-Control: no-store, no-cache must-revalidate'); header('Content-disposition: attachment; filename="' . $filename); // puts keys $keys = array('id_product', 'id_product_attribute', 'reference', 'ean13', 'upc', 'name', 'physical_quantity', 'usable_quantity', 'real_quantity'); echo sprintf("%s\n", implode(';', $keys)); // puts rows foreach ($this->_list as $row) { $row_csv = array($row['id_product'], $row['id_product_attribute'], $row['reference'], $row['ean13'], $row['upc'], $row['name'], $row['physical_quantity'], $row['usable_quantity'], $row['real_quantity']); // puts one row echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv))); } } elseif (Tools::isSubmit('csv_prices')) { // sets filename $filename = $this->l('stock_instant_state_prices') . '_' . $warehouse_name . '.csv'; // header header('Content-type: text/csv'); header('Cache-Control: no-store, no-cache must-revalidate'); header('Content-disposition: attachment; filename="' . $filename); // puts keys $keys = array('id_product', 'id_product_attribute', 'reference', 'ean13', 'upc', 'name', 'price_te', 'physical_quantity', 'usable_quantity'); echo sprintf("%s\n", implode(';', $keys)); foreach ($this->_list as $row) { $id_product = (int) $row['id_product']; $id_product_attribute = (int) $row['id_product_attribute']; // gets prices $query = new DbQuery(); $query->select('s.price_te, SUM(s.physical_quantity) as physical_quantity, SUM(s.usable_quantity) as usable_quantity'); $query->from('stock', 's'); $query->leftJoin('warehouse', 'w', 'w.id_warehouse = s.id_warehouse'); $query->where('s.id_product = ' . $id_product . ' AND s.id_product_attribute = ' . $id_product_attribute); $query->where('s.id_warehouse = ' . $id_warehouse); $query->groupBy('s.price_te'); $datas = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); // puts data foreach ($datas as $data) { $row_csv = array($row['id_product'], $row['id_product_attribute'], $row['reference'], $row['ean13'], $row['upc'], $row['name'], $data['price_te'], $data['physical_quantity'], $data['usable_quantity']); // puts one row echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv))); } } } }
public static function getIdsByBadgeGroup($badge_group) { $ids = array(); $sub_query = new DbQuery(); $sub_query->select('id_badge'); $sub_query->from('badge', 'b'); $sub_query->where('b.`id_group` = ' . (int) $badge_group); $sub_query->where('b.`validated` = 0'); $sub_query->groupBy('b.`id_group`'); $query = new DbQuery(); $query->select('c.`id_condition`'); $query->from('condition', 'c'); $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'condition_badge` cb ON cb.`id_condition` = c.`id_condition`'); $query->where('c.`validated` = 0'); $query->where('cb.`id_badge` IN (' . $sub_query . ')'); $query->groupBy('c.`id_condition`'); $result = Db::getInstance()->executeS($query); foreach ($result as $r) { $ids[] = $r['id_condition']; } return $ids; }
/** * Add GROUP BY restriction on query * * @param string $field Field name * @return Collection */ public function groupBy($field) { $this->query->groupBy($this->parseField($field)); return $this; }
private function getAbandonedCartInfo() { if ((int) $this->cart_id < 1) { return false; } $query_limit = 0; $query_offset = 0; $cart_products = array(); if ($this->page !== null && !empty($this->page) && $this->show !== null && !empty($this->show)) { $query_limit = ((int) $this->page - 1) * (int) $this->show; $query_offset = (int) $this->show; } // Get cart information $cart_info_obj = new DbQuery(); $cart_info_obj->select("\r\n\t\t\tc.id_cart,\r\n\t\t\tc.date_add,\r\n\t\t\tc.id_currency,\r\n\t\t\tc.id_customer,\r\n\t\t\tcus.date_add AS account_registered,\r\n\t\t\tcus.email,\r\n\t\t\ta.phone,\r\n\t\t\tCONCAT(cus.firstname, ' ', cus.lastname) AS customer,\r\n\t\t\ts.name AS shop_name,\r\n\t\t\tcar.name AS carrier_name,\r\n\t\t\tSUM((ps.price + pas.price) * cp.quantity) AS cart_total\r\n\t\t"); $cart_info_obj->from('cart', 'c'); $cart_info_obj->innerJoin('cart_product', 'cp', 'cp.id_cart = c.id_cart'); $cart_info_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop'); $cart_info_obj->leftJoin('product_attribute_shop', 'pas', 'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop'); $cart_info_obj->leftJoin('customer', 'cus', 'c.id_customer = cus.id_customer'); $cart_info_obj->leftJoin('address', 'a', 'a.id_customer = cus.id_customer'); $cart_info_obj->leftJoin('shop', 's', 's.id_shop = c.id_shop'); $cart_info_obj->leftJoin('carrier', 'car', 'car.id_carrier = c.id_carrier'); $cart_info_obj->where('c.id_cart = ' . (int) $this->cart_id); $cart_info_obj->groupBy('c.id_cart'); $cart_info_sql = $cart_info_obj->build(); $cart_info = Db::getInstance()->executeS($cart_info_sql); $cart_info = array_shift($cart_info); if (trim($cart_info['customer']) == '') { $cart_info['customer'] = self::GUEST; } // Convert and format price data if ($this->currency_code != $cart_info['id_currency']) { $cart_info['cart_total'] = $this->convertPrice($cart_info['cart_total'], $this->def_currency, $cart_info['id_currency']); } $cart_info['cart_total'] = $this->displayPrice($cart_info['cart_total'], $cart_info['id_currency']); // Get cart products $cart_products_obj = new DbQuery(); $cart_products_obj->select(' cp.id_product, cp.id_product_attribute, cp.quantity AS product_quantity, p.reference AS sku, (ps.price + pas.price) AS product_price, (ps.wholesale_price + pas.wholesale_price) AS wholesale_price, c.id_currency, pl.name AS product_name, pl.link_rewrite '); $cart_products_obj->from('cart_product', 'cp'); $cart_products_obj->leftJoin('product_shop', 'ps', 'ps.id_product = cp.id_product AND ps.id_shop = cp.id_shop'); $cart_products_obj->leftJoin('product_attribute_shop', 'pas', 'pas.id_product_attribute = cp.id_product_attribute AND pas.id_shop = cp.id_shop'); $cart_products_obj->leftJoin('product', 'p', 'p.id_product = cp.id_product'); $cart_products_obj->leftJoin('cart', 'c', 'c.id_cart = cp.id_cart'); $cart_products_obj->leftJoin('product_lang', 'pl', 'pl.id_product = cp.id_product AND pl.id_shop = cp.id_shop AND pl.id_lang = ' . (int) $this->def_lang); $cart_products_obj->where('cp.id_cart = ' . (int) $this->cart_id); $cart_products_obj->limit($query_offset, $query_limit); $cart_products_sql = $cart_products_obj->build(); $cart_products_res = Db::getInstance()->executeS($cart_products_sql); // Get attribute values foreach ($cart_products_res as $product) { $product_attributes_obj = new DbQuery(); $product_attributes_obj->select(' al.name AS attribute_value, agl.public_name AS attribute_name '); $product_attributes_obj->from('product_attribute_combination', 'pac'); $product_attributes_obj->leftJoin('attribute_lang', 'al', 'al.id_attribute = pac.id_attribute AND al.id_lang = ' . (int) $this->def_lang); $product_attributes_obj->leftJoin('attribute', 'a', 'a.id_attribute = pac.id_attribute'); $product_attributes_obj->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $this->def_lang); $product_attributes_obj->where('pac.id_product_attribute = ' . (int) $product['id_product_attribute']); $product_attributes_obj->orderBy('attribute_name'); $product_attributes_sql = $product_attributes_obj->build(); $product_attributes = Db::getInstance()->executeS($product_attributes_sql); $product_attr = array(); foreach ($product_attributes as $product_attribute) { $product_attr[] = $product_attribute['attribute_name'] . ' : ' . $product_attribute['attribute_value']; } $product['combination'] = !empty($product_attr) ? implode(', ', $product_attr) : ''; // Convert and form price data if ($this->currency_code != $this->def_currency) { $product['product_price'] = $this->convertPrice($product['product_price'], $this->def_currency, $product['id_currency']); $product['wholesale_price'] = $this->convertPrice($product['wholesale_price'], $this->def_currency, $product['id_currency']); } $product['product_price'] = $this->displayPrice($product['product_price'], $product['id_currency']); $product['wholesale_price'] = $this->displayPrice($product['wholesale_price'], $product['id_currency']); // Get url of product image $image_url = $this->getProductImageUrl($product['id_product'], $product['link_rewrite']); if ($image_url) { $product['product_image'] = $image_url; } $cart_products[] = $product; } // Get cart product count $cart_product_count_obj = new DbQuery(); $cart_product_count_obj->select(' COUNT(cp.id_product) AS product_count '); $cart_product_count_obj->from('cart_product', 'cp'); $cart_product_count_obj->where('cp.id_cart = ' . (int) $this->cart_id); $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']); }
/** * 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->customer) ? '_' . $context->customer->id : ''); if (!Cache::isStored($cache_id) || $hook_name == 'displayPayment') { $frontend = true; $groups = array(); if (isset($context->employee)) { $shop_list = array((int) $context->shop->id); $frontend = false; } else { // Get shops and groups list $shop_list = Shop::getContextListShopID(); 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'); $sql->innerJoin('hook_module', 'hm', 'hm.`id_module` = m.`id_module`'); $sql->innerJoin('hook', 'h', 'hm.`id_hook` = h.`id_hook`'); $sql->where('(SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'module_shop ms WHERE ms.id_module = m.id_module AND ms.id_shop IN (' . implode(', ', $shop_list) . ')) = ' . count($shop_list)); if ($hook_name != 'displayPayment') { $sql->where('h.name != "displayPayment"'); } elseif ($frontend) { if (Validate::isLoadedObject($context->country)) { $sql->where('(h.name = "displayPayment" AND (SELECT id_country FROM ' . _DB_PREFIX_ . 'module_country mc WHERE mc.id_module = m.id_module AND id_country = ' . (int) $context->country->id . ' LIMIT 1) = ' . (int) $context->country->id . ')'); } if (Validate::isLoadedObject($context->currency)) { $sql->where('(h.name = "displayPayment" 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 . ', -2) LIMIT 1) IN (' . (int) $context->currency->id . ', -2))'); } } if (Validate::isLoadedObject($context->shop)) { $sql->where('hm.id_shop = ' . (int) $context->shop->id); } if ($frontend) { $sql->leftJoin('module_group', 'mg', 'mg.`id_module` = m.`id_module`'); $sql->where('mg.`id_group` IN (' . implode(', ', $groups) . ')'); $sql->groupBy('hm.id_hook, hm.id_module'); } $sql->orderBy('hm.`position`'); // Store results per hook name $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); $list = array(); // Get all available payment module $payment_modules = array(); if (isset($context->shop->id)) { foreach (Module::getPaymentModules() as $module) { $payment_modules[] = $module['name']; } } if ($results) { foreach ($results as $row) { if ($row['hook'] == 'displayPayment' && !in_array($row['module'], $payment_modules)) { continue; } $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') { Cache::store($cache_id, $list); // @todo remove this in 1.6, we keep it in 1.5 for retrocompatibility 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 = Hook::getRetroHookName($hook_name); $hook_name = strtolower($hook_name); $return = array(); if (isset($list[$hook_name])) { $return = $list[$hook_name]; } if (isset($list[$retro_hook_name])) { $return = array_merge($return, $list[$retro_hook_name]); } if (count($return) > 0) { return $return; } return false; } else { return $list; } }
/** * Add GROUP BY restriction on query using real SQL syntax * * @param string $sql * @return Collection */ public function sqlGroupBy($sql) { $this->query->groupBy($this->parseFields($sql)); return $this; }
/** * @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) { 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; }
IFNULL(pa.upc, IFNULL(p.upc, \'\')) as upc, md5(CONCAT(\'' . _COOKIE_KEY_ . '\', p.id_product, \'_\', IFNULL(pa.id_product_attribute, \'0\'))) as checksum, IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.name, \' - \', al.name SEPARATOR \', \')), pl.name) as name '); $query->from('product', 'p'); $query->innerJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $id_lang); $query->leftJoin('product_attribute', 'pa', 'pa.id_product = p.id_product'); $query->leftJoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute'); $query->leftJoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute'); $query->leftJoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . (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->leftJoin('product_supplier', 'ps', 'ps.id_product = p.id_product AND ps.id_product_attribute = IFNULL(pa.id_product_attribute, 0)'); $query->where('p.id_product NOT IN (SELECT pd.id_product FROM `' . _DB_PREFIX_ . 'product_download` pd WHERE (pd.id_product = p.id_product))'); $query->where('p.is_virtual = 0 AND p.cache_is_pack = 0'); $query->where('p.id_product = ' . (int) $id_product . ''); $query->groupBy('p.id_product, pa.id_product_attribute'); $item = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query); $ids = explode('_', $item['id']); if ($item) { die(Tools::jsonEncode($item)); } die(1); //break; /* Update wholesaleprice */ //break; /* Update wholesaleprice */ case 'majWholesalePrice': if (Tools::isSubmit('id_product') && Tools::isSubmit('id_product_attribute') && Tools::isSubmit('wholesale_price') && Tools::isSubmit('id_supplier')) { require_once _PS_MODULE_DIR_ . 'erpillicopresta/classes/ErpProductSupplier.php'; $id_product = Tools::getValue('id_product'); $id_product_attribute = Tools::getValue('id_product_attribute');
/** * AdminController::getList() override * @see AdminController::getList() */ public function getList($id_lang, $order_by = null, $order_way = null, $start = 0, $limit = null, $id_lang_shop = false) { parent::getList($id_lang, $order_by, $order_way, $start, $limit, $id_lang_shop); if ($this->ajax) { foreach ($this->_list as &$list) { if (file_exists(_PS_IMG_DIR_ . $this->fieldImageSettings['dir'] . '/' . (int) $list['id_attribute'] . '.jpg')) { if (!isset($list['color']) || !is_array($list['color'])) { $list['color'] = array(); } $list['color']['texture'] = '../img/' . $this->fieldImageSettings['dir'] . '/' . (int) $list['id_attribute'] . '.jpg'; } } } else { $nb_items = count($this->_list); for ($i = 0; $i < $nb_items; ++$i) { $item =& $this->_list[$i]; $query = new DbQuery(); $query->select('COUNT(a.id_attribute) as count_values'); $query->from('attribute', 'a'); $query->join(Shop::addSqlAssociation('attribute', 'a')); $query->where('a.id_attribute_group =' . (int) $item['id_attribute_group']); $query->groupBy('attribute_shop.id_shop'); $query->orderBy('count_values DESC'); $item['count_values'] = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); unset($query); } } }
public function 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); }
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); }
/** * @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('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); $client_orders_qty = 0; 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 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); }
/** * Admin panel product search * * @param integer $id_lang Language id * @param string $query Search query * @return array Matching products */ public static function searchByName($id_lang, $query, Context $context = null) { if (!$context) { $context = Context::getContext(); } $sql = new DbQuery(); $sql->select('p.`id_product`, pl.`name`, p.`active`, p.`reference`, m.`name` AS manufacturer_name, stock.`quantity`, product_shop.advanced_stock_management, p.`customizable`'); $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) $id_lang . Shop::addSqlRestrictionOnLang('pl')); $sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`'); $where = 'pl.`name` LIKE \'%' . pSQL($query) . '%\' OR p.`reference` LIKE \'%' . pSQL($query) . '%\' OR p.`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); $sql->join(Product::sqlStock('p', 'pa', false, $context->shop)); $result = Db::getInstance()->executeS($sql); if (!$result) { return false; } $results_array = array(); foreach ($result as $row) { $row['price_tax_incl'] = Product::getPriceStatic($row['id_product'], true, null, 2); $row['price_tax_excl'] = Product::getPriceStatic($row['id_product'], false, null, 2); $results_array[] = $row; } return $results_array; }
/** * For a given {product, product attribute} gets warehouse list * * @param int $id_product ID of the product * @param int $id_product_attribute Optional, uses 0 if this product does not have attributes * @param int $id_shop Optional, ID of the shop. Uses the context shop id (@see Context::shop) * @return array Warehouses (ID, reference/name concatenated) */ public static function getProductWarehouseList($id_product, $id_product_attribute = 0, $id_shop = null) { // if it's a pack, returns warehouses if and only if some products use the advanced stock management if (Pack::isPack($id_product)) { $warehouses = Warehouse::getPackWarehouses($id_product); $res = array(); foreach ($warehouses as $warehouse) { $res[]['id_warehouse'] = $warehouse; } return $res; } $share_stock = false; if ($id_shop === null) { if (Shop::getContext() == Shop::CONTEXT_GROUP) { $shop_group = Shop::getContextShopGroup(); } else { $shop_group = Context::getContext()->shop->getGroup(); $id_shop = (int) Context::getContext()->shop->id; } $share_stock = $shop_group->share_stock; } else { $shop_group = Shop::getGroupFromShop($id_shop); $share_stock = $shop_group['share_stock']; } if ($share_stock) { $ids_shop = Shop::getShops(true, (int) $shop_group->id, true); } else { $ids_shop = array((int) $id_shop); } $query = new DbQuery(); $query->select('wpl.id_warehouse, CONCAT(w.reference, " - ", w.name) as name'); $query->from('warehouse_product_location', 'wpl'); $query->innerJoin('warehouse_shop', 'ws', 'ws.id_warehouse = wpl.id_warehouse AND id_shop IN (' . implode(',', array_map('intval', $ids_shop)) . ')'); $query->innerJoin('warehouse', 'w', 'ws.id_warehouse = w.id_warehouse'); $query->where('id_product = ' . (int) $id_product); $query->where('id_product_attribute = ' . (int) $id_product_attribute); $query->where('w.deleted = 0'); $query->groupBy('wpl.id_warehouse'); return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); }
/** * 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; } }
/** * Retrieves the product entries for the current order * * @param int $id_lang Optional Id Lang - Uses Context::language::id by default * @return array */ public function getEntries($id_lang = null) { if ($id_lang == null) { $id_lang = Context::getContext()->language->id; } // build query $query = new DbQuery(); $query->select(' s.*, IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(agl.name, \' - \', al.name SEPARATOR \', \')), pl.name) as name_displayed'); $query->from('supply_order_detail', 's'); $query->innerjoin('product_lang', 'pl', 'pl.id_product = s.id_product AND pl.id_lang = ' . $id_lang); $query->leftjoin('product', 'p', 'p.id_product = s.id_product'); $query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = s.id_product_attribute'); $query->leftjoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute'); $query->leftjoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . $id_lang); $query->leftjoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . $id_lang); $query->where('s.id_supply_order = ' . (int) $this->id); $query->groupBy('s.id_supply_order_detail'); return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); }
/** * Return suppliers * * @return array Suppliers */ public static function getSuppliers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_groups = false) { if (!$id_lang) { $id_lang = Configuration::get('PS_LANG_DEFAULT'); } if (!Group::isFeatureActive()) { $all_groups = true; } $query = new DbQuery(); $query->select('s.*, sl.`description`'); $query->from('supplier', 's'); $query->leftJoin('supplier_lang', 'sl', 's.`id_supplier` = sl.`id_supplier` AND sl.`id_lang` = ' . (int) $id_lang); $query->join(Shop::addSqlAssociation('supplier', 's')); if ($active) { $query->where('s.`active` = 1'); } $query->orderBy(' s.`name` ASC'); $query->limit($n, ($p - 1) * $n); $query->groupBy('s.id_supplier'); $suppliers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($suppliers as &$supplier) { $sup = new Supplier($supplier['id_supplier'], $id_lang); $association_results = $sup->getSupplierForAssociationInformation(); $supplier['association_discount'] = $association_results[0]['discount']; $supplier['association_gain'] = $association_results[0]['gain']; } if ($suppliers === false) { return false; } if ($get_nb_products) { $sql_groups = ''; if (!$all_groups) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'; } $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT ps.`id_supplier`, COUNT(DISTINCT ps.`id_product`) as nb_products FROM `' . _DB_PREFIX_ . 'product_supplier` ps JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`) ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'supplier` as m ON (m.`id_supplier`= p.`id_supplier`) WHERE ps.id_product_attribute = 0' . ($active ? ' AND product_shop.`active` = 1' : '') . ' AND product_shop.`visibility` NOT IN ("none")' . ($all_groups ? '' : ' AND ps.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` ' . $sql_groups . ' )') . ' GROUP BY ps.`id_supplier`'); $counts = array(); foreach ($results as $result) { $counts[(int) $result['id_supplier']] = (int) $result['nb_products']; } if (count($counts) && is_array($suppliers)) { foreach ($suppliers as $key => &$supplier) { if (isset($counts[(int) $supplier['id_supplier']])) { $suppliers[$key]['nb_products'] = $counts[(int) $supplier['id_supplier']]; } else { $suppliers[$key]['nb_products'] = 0; } } } } $nb_suppliers = count($suppliers); $rewrite_settings = (int) Configuration::get('PS_REWRITING_SETTINGS'); for ($i = 0; $i < $nb_suppliers; $i++) { $suppliers[$i]['link_rewrite'] = $rewrite_settings ? Tools::link_rewrite($suppliers[$i]['name']) : 0; } return $suppliers; }