1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
| $this->_select .= 'a.`id_product` as id, two.competitor_price as competitor_price, a.`reference` as reference,
two.competitor as competitor, sa.wholesale_price as wholesale_price, sa.last_pa_price as last_pa_price ,
sa.free_delivery as free_delivery ,
IF(sa.`price` < two.`competitor_price`, 1, 0) AS `flag`, a.`id_manufacturer` as `manufacturer`,
(SELECT COUNT(*) FROM `'._DB_PREFIX_.'product_attribute` WHERE `id_product`=a.`id_product`) as attribute ,';
$this->_select .= ' i.id_image as id_image, cl.name `name_category`, mg.`margin` as `margin`,
mg.`margin_max` as `margin_max`, sa.`price`,
IF(t.`rate` AND trg.`active` AND t.`active` AND NOT t.`deleted` AND '.(int)Configuration::get('PS_TAX').',
ROUND(sa.`price`+(t.`rate`/100*sa.`price`), 6), sa.`price`) AS `price_final`,
IF(t.`rate` AND trg.`active` AND t.`active` AND NOT t.`deleted` AND '.(int)Configuration::get('PS_TAX').',
t.`rate`, 0) as rate, stock.quantity as quantity_total ,IFNULL(lp.`id_product`, 0) as `id_lengow_product`,f.reduction as discount_amt ';
$this->_join .= 'LEFT JOIN `'._DB_PREFIX_.'product_shop` sa ON a.`id_product` = sa.`id_product`
AND sa.`id_shop` = '.$id_shop.'
LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON sa.`id_category_default` = cl.`id_category`
AND cl.id_shop = '.$id_shop.'
LEFT JOIN '._DB_PREFIX_.'tax_rules_group trg ON trg.`id_tax_rules_group` = a.`id_tax_rules_group`
LEFT JOIN '._DB_PREFIX_.'tax t ON t.`id_tax` = a.`id_tax_rules_group`
LEFT JOIN `'._DB_PREFIX_.'image` i ON i.`id_product` = a.`id_product` AND i.`cover`=1
LEFT JOIN '._DB_PREFIX_.'twovnbm two ON two.`id_product` = a.`id_product`
LEFT JOIN '._DB_PREFIX_.'lengow_product lp ON lp.`id_product` = sa.`id_product`
LEFT JOIN `'._DB_PREFIX_.'stock_available` stock ON (stock.`id_product` = a.`id_product`) AND stock.id_product_attribute =0
LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` f ON (f.`id_product` = sa.`id_product`) AND f.id_product_attribute = 0
LEFT JOIN (SELECT sa.`id_product`, IF(t.`rate` AND trg.`active` AND t.`active` AND NOT t.`deleted`
AND '.(int)Configuration::get('PS_TAX').',
IF(sa.last_pa_price=0,ROUND((two.competitor_price-sa.wholesale_price)*100/two.competitor_price,2),ROUND((two.competitor_price-sa.last_pa_price)*100/two.competitor_price,2) )
,
two.competitor_price) AS `margin`,
IF(t.`rate` AND trg.`active` AND t.`active` AND NOT t.`deleted` AND '.(int)Configuration::get('PS_TAX').',
IF(sa.last_pa_price=0,ROUND((two.competitor_price-sa.wholesale_price)*100/two.competitor_price,2),ROUND((two.competitor_price-sa.last_pa_price)*100/two.competitor_price,2) )
,
two.competitor_price) AS `margin_max`
FROM `'._DB_PREFIX_.'product` as a
LEFT JOIN `'._DB_PREFIX_.'product_shop` sa ON a.`id_product` = sa.`id_product`
AND sa.`id_shop` = '.$id_shop.'
LEFT JOIN '._DB_PREFIX_.'tax t ON t.`id_tax` = a.`id_tax_rules_group`
LEFT JOIN '._DB_PREFIX_.'tax_rules_group trg ON trg.`id_tax_rules_group` = a.`id_tax_rules_group`
LEFT JOIN '._DB_PREFIX_.'twovnbm two ON two.`id_product` = a.`id_product`
LEFT JOIN '._DB_PREFIX_.'lengow_product lp ON lp.`id_product` = sa.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` f ON (f.`id_product` = sa.`id_product`) AND f.id_product_attribute = 0
GROUP BY a.id_product ) mg ON mg.`id_product` = a.`id_product`
';
if (Tools::getValue('refilters')) {
$this->processResetFilters();
$this->_default_pagination = (int)Tools::getValue('how_many_show');
$this->_pagination = array(20, 50, 100, 300);
}
$this->_where .= ' AND b.`id_lang` = '.(int)$this->context->language->id.' AND a.`id_product`=two.`id_product`
AND b.`id_lang` = cl.`id_lang`
AND a.`id_product`=b.`id_product` AND a.`id_product`=sa.`id_product` |
Partager