Is this query possible? Please help

[mehere]

Hello,

could someone please give me some help on how to set a DB query in SQL Manager from BO?

I am in need of something like:

IF Voucher field is not empty THEN for Orders placed from (date) to (date) whose status is delivered (possibly), get Order ID,Voucher id,voucher name,Customer name,Total products (tax incl.),Total products (tax excl.),total discounts,shipping,total paid.

Any help/hint would be much appreciated,

thank you very much!

Michele

[Guest]

Here is a script that shows you all the columns.

Change c.current_state = 5 OR e.id_order_state = 5 to your delivered id state !!!

SELECT a.*, b.*, c.*, d.*, e.* FROM ps_order_cart_rule a
LEFT JOIN  ps_cart_rule b ON (a.id_cart_rule = b.id_cart_rule)
LEFT JOIN  ps_orders c ON (a.id_order = c.id_order)
LEFT JOIN  ps_customer d ON (c.id_customer = d.id_customer)
LEFT JOIN ps_order_history e ON (c.id_order = e.id_order)
WHERE c.current_state = 5 OR e.id_order_state = 5
GROUP BY a.id_order, c.id_order
ORDER BY a.id_order 

 

Edited June 11, 2020 by Guest (see edit history)

[mehere]

Thank you Tengler! Will try first to understand and then to implement

Lot of love!

M

[Guest]

AND (c.date_add BETWEEN '2020-06-06' AND '2020-06-11')

Filter date order add between two date, add to WHERE clausule.

etc...

 

Edited June 11, 2020 by Guest (see edit history)

[mehere]

I am starting to get something, not what I need yet but something

Thank you again Tengler!

[mehere]

Ok for other dummies like me, here's something to get all info starting from a specific cart rule ID for the whole year 2020 (invoiced orders):

SELECT * FROM ps_order_cart_rule a
LEFT JOIN  ps_cart_rule b ON (a.id_cart_rule = b.id_cart_rule)
LEFT JOIN  ps_orders c ON (a.id_order = c.id_order)
LEFT JOIN  ps_customer d ON (c.id_customer = d.id_customer)
LEFT JOIN ps_order_history e ON (c.id_order = e.id_order)
WHERE a.id_cart_rule = 810 AND (c.invoice_date BETWEEN '2020-01-01' AND '2020-12-31') AND (c.current_state = 5 OR e.id_order_state = 5)
GROUP BY a.id_order, c.id_order
ORDER BY a.id_order

More love to Tengler!