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