I am desperate, can't find a solution. How to export used voucher codes with order information?

[BabsD]

I am running crazy. I tried to use the SQL Manager, but it is too complicated for me to use, I don't have the proper knowledge. Is anyone out there who could help me with that?  If you live in the U.S. I even can send some Chocolate as a thank you!

Ho can I run an export of this information:
All orders which were using a specific Voucher code including the following information

Voucher Code | Order date | Order status (shipped or delivered) | customer name | order total excl. tax | shipping fees excl tax

I use PS 1.6.1 
 

[idnovate.com]

Try this:

SELECT ocr.`name`, cr.`code`, o.`id_order`, o.`date_add`, o.`current_state`, osl.`name`, o.`id_customer`, CONCAT(c.`firstname`, ' ', c.`lastname`),  o.`total_paid_tax_excl`, o.`total_shipping_tax_excl`
FROM ps_order_cart_rule ocr
LEFT JOIN ps_cart_rule cr ON (cr.`id_cart_rule` = ocr.`id_cart_rule`)
LEFT JOIN ps_orders o ON (o.`id_order` = ocr.`id_order`)
LEFT JOIN ps_order_state_lang osl ON (osl.`id_order_state` = o.`current_state` AND osl.`id_lang` = 1)
LEFT JOIN ps_customer c ON (c.`id_customer` = o.`id_customer`)

 

[BabsD]

21 minutes ago, idnovate.com said:

Try this:

SELECT ocr.`name`, cr.`code`, o.`id_order`, o.`date_add`, o.`current_state`, osl.`name`, o.`id_customer`, CONCAT(c.`firstname`, ' ', c.`lastname`),  o.`total_paid_tax_excl`, o.`total_shipping_tax_excl`
FROM ps_order_cart_rule ocr
LEFT JOIN ps_cart_rule cr ON (cr.`id_cart_rule` = ocr.`id_cart_rule`)
LEFT JOIN ps_orders o ON (o.`id_order` = ocr.`id_order`)
LEFT JOIN ps_order_state_lang osl ON (osl.`id_order_state` = o.`current_state` AND osl.`id_lang` = 1)
LEFT JOIN ps_customer c ON (c.`id_customer` = o.`id_customer`)

 

OMG, you are my hero!! this is a life saver - thank you, thank you!

One more question, can I do this query also by asking for a specific voucher code? This would give me the opportunity to run a report only on a specific one instead of getting all? 

[idnovate.com]

Just add:

WHERE cr.`code` = 'CODE';

 

[BabsD]

Thank you!! You helped me a lot!

8 hours ago, idnovate.com said:

Just add:

WHERE cr.`code` = 'CODE';

 

 

[BabsD]

On 5/22/2020 at 11:32 AM, idnovate.com said:

Just add:

WHERE cr.`code` = 'CODE';

 

May I ask one more thing? I would like to find another query which gives me the following information:

Selecting from all orders which have the status delivered

want to set a specific date, e.g. all orders after 5/25/2020

showing First name, Last Name (of the person who had ordered, usually the name in the billing address), e-mail address 

[idnovate.com]

What have you done so far? 😊

[BabsD]

8 hours ago, idnovate.com said:

What have you done so far? 😊

I tried to reuse what you did for me but I am not able to understand these queries and I am just not able to make it work

this is what I did

SELECT ocr.`name`, o.`id_order`, o.`date_add`, osl.`name`, o.`id_customer`, CONCAT(c.`firstname`,
' ', c.`lastname`),  o.`total_paid_tax_excl`, o.`total_shipping_tax_excl`
FROM ps_orders osl
LEFT JOIN ps_orders o ON (o.`id_order` = ocr.`id_order`)
LEFT JOIN ps_customer c ON (c.`id_customer` = o.`id_customer`)
WHERE o.`date_add` > '2020-5-25';

 

[idnovate.com]

Try this:

SELECT o.`id_order`, o.`date_add`, o.`id_customer`, CONCAT(c.`firstname`, ' ', c.`lastname`) as customer_name,  c.email
FROM ps_orders o
LEFT JOIN ps_customer c ON (c.`id_customer` = o.`id_customer`)
WHERE o.`date_add` > '2020-5-25'
AND o.current_state = 1; //Set the ID for Delivered order state

 

[BabsD]

4 hours ago, idnovate.com said:

Try this:

SELECT o.`id_order`, o.`date_add`, o.`id_customer`, CONCAT(c.`firstname`, ' ', c.`lastname`) as customer_name,  c.email
FROM ps_orders o
LEFT JOIN ps_customer c ON (c.`id_customer` = o.`id_customer`)
WHERE o.`date_add` > '2020-5-25'
AND o.current_state = 1; //Set the ID for Delivered order state

 

thank you! but it came back with no results

any ideas why?

[Divyesh Prajapati]

On 5/22/2020 at 6:31 PM, BabsD said:

I am running crazy. I tried to use the SQL Manager, but it is too complicated for me to use, I don't have the proper knowledge. Is anyone out there who could help me with that?  If you live in the U.S. I even can send some Chocolate as a thank you!

Ho can I run an export of this information:
All orders which were using a specific Voucher code including the following information

Voucher Code | Order date | Order status (shipped or delivered) | customer name | order total excl. tax | shipping fees excl tax

I use PS 1.6.1 
 

I think this will work for you... If in order you are having multiple cart rules (voucher) then it will give vouchers as comma separated values

SELECT GROUP_CONCAT(ocr.`name`) AS vouchers, GROUP_CONCAT(cr.`code`) AS voucher_codes, o.`reference` as order_reference, osl.`name` as order_status, o.`current_state`, CONCAT(c.`firstname`, ' ', c.`lastname`) as customer_name, o.`date_add` as order_date, o.`total_paid_tax_excl`, o.`total_shipping_tax_excl` FROM ps_orders o
LEFT JOIN ps_customer as c ON c.id_customer = o.id_customer
LEFT JOIN ps_order_state_lang as osl ON osl.id_order_state = o.current_state AND osl.id_lang = o.id_lang
LEFT JOIN ps_order_cart_rule as ocr ON ocr.id_order = o.id_order
LEFT JOIN ps_cart_rule as cr ON cr.id_cart_rule = ocr.id_cart_rule
GROUP BY o.id_order

And if you want only order with specific voucher code then below will work; replace {CODE_HERE} with your code

SELECT GROUP_CONCAT(ocr.`name`) AS vouchers, GROUP_CONCAT(cr.`code`) AS voucher_codes, o.`reference` as order_reference, osl.`name` as order_status, o.`current_state`, CONCAT(c.`firstname`, ' ', c.`lastname`) as customer_name, o.`date_add` as order_date, o.`total_paid_tax_excl`, o.`total_shipping_tax_excl` FROM ps_orders o
LEFT JOIN ps_customer as c ON c.id_customer = o.id_customer
LEFT JOIN ps_order_state_lang as osl ON osl.id_order_state = o.current_state AND osl.id_lang = o.id_lang
LEFT JOIN ps_order_cart_rule as ocr ON ocr.id_order = o.id_order
LEFT JOIN ps_cart_rule as cr ON cr.id_cart_rule = ocr.id_cart_rule
WHERE cr.`code` = '{CODE_HERE}'
GROUP BY o.id_order

 

Edited June 6, 2020 by Divyesh Prajapati (see edit history)

[idnovate.com]

1 hour ago, BabsD said:

thank you! but it came back with no results

any ideas why?

Have you read this?

AND o.current_state = 1; //Set the ID for Delivered order state

 

[BabsD]

1 hour ago, idnovate.com said:

Have you read this?

AND o.current_state = 1; //Set the ID for Delivered order state

 

how do I find out the ID?

[BabsD]

1 hour ago, idnovate.com said:

Have you read this?

AND o.current_state = 1; //Set the ID for Delivered order state

 

found it! It works now! Thank you for your help!

[idnovate.com]