PrestaShop 1.6 get product discounted price using sql

[Zohaib-fk]

Hi,

Greetings of the day.

I want to get product discounted price using sql. I am able to get regular price.

For example below is the discounted price.

17,83 € tax incl.

-10%

19,81 € tax incl.

 

SELECT CONCAT('PS', p.id_product) AS 'sku',
    p.active AS 'Active (0/1)',
    pl.name AS 'post_title',
    GROUP_CONCAT(DISTINCT REPLACE(pc.name, ' / ', '>')  SEPARATOR '|') AS 'tax:product_cat',
    p.price AS 'Price tax excl.',
    ROUND(p.price * (COALESCE(ptx.rate, 0) / 100 + 1), 2) AS 'regular_price',
    COALESCE(ptx.rate, 0) AS 'Tax',
    p.wholesale_price AS 'Wholesale price',
    p.on_sale AS 'On sale (0/1)',
    p.reference AS 'Reference #',
    p.supplier_reference AS 'Supplier reference #',
    p.ean13 AS 'EAN13',
    p.upc AS 'UPC',
    p.ecotax AS 'Ecotax',
    p.weight AS 'weight',
    p.quantity AS 'stock',
    "yes" AS 'manage_stock',
    pl.description_short AS 'post_excerpt',
    pl.description AS 'post_content',
    pl.meta_title AS 'Meta-title',
    pl.meta_keywords AS 'Meta-keywords',
    pl.meta_description AS 'Meta-description',
    pl.link_rewrite AS 'URL rewritten',
    pl.available_now AS 'Text when in stock',
    pl.available_later AS 'Text when backorder allowed',
    p.available_for_order AS 'Available for order',
    p.date_add AS 'post_date',
    p.show_price AS 'Show price',
    p.online_only AS 'Available online only',
    p.`condition` AS 'Condition',
    concat( 'http://svartrecords.com/shoppe/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS 'images'
FROM ps_product p LEFT JOIN
ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1 JOIN
ps_tax_rule ptxgrp ON ptxgrp.id_tax_rules_group = p.id_tax_rules_group JOIN
ps_tax ptx ON ptx.id_tax = ptxgrp.id_tax  JOIN
ps_image im ON p.id_product = im.id_product JOIN
ps_category_product pcp ON pcp.id_product = p.id_product JOIN
ps_category_lang pc ON pcp.id_category = pc.id_category AND pc.id_lang = 1
WHERE p.active = 1
GROUP BY p.id_product


SELECT DISTINCT product_id, product_reference AS kood,product_name AS nimetus,product_quantity AS nr, pl.link_rewrite,psa.quantity as kogukogus,group_concat(ps.product_supplier_reference) as supp_ref, p.location AS asukoht,
         CONCAT(c.link_rewrite,'/',p.id_product,'-',pl.link_rewrite,'.html') link
        FROM ps_order_detail o
        JOIN ps_product_lang pl on o.product_id = pl.id_product
        JOIN ps_product p on p.id_product = pl.id_product
        JOIN ps_stock_available psa on p.id_product = psa.id_product
        JOIN ps_category_lang c on c.id_category = p.id_category_default
        JOIN ps_product_supplier ps on p.id_product = ps.id_product
        LEFT JOIN ps_product_attribute pa on p.id_product=pa.id_product
                   AND psa.id_product_attribute = pa.id_product_attribute

        WHERE pl.id_lang=2 AND c.id_lang=2


SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product    

 

Any idea?   

Have a nice day.

 

[pant13]

did you find a solution? i'm also looking for which tables to join in order to get the discount

[musicmaster]

A rather impossible thing to ask. Discount can depend on customer group, country, quantity, currency and date. Of course most times only quantity and date are relevant. But even that can result in quite a complicated query.