Jump to content
BabsD

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

Recommended Posts

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 
 

Share this post


Link to post
Share on other sites

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`)

 

  • Like 1

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites

Thank you!! You helped me a lot!

8 hours ago, idnovate.com said:

Just add:


WHERE cr.`code` = 'CODE';

 

 

Share this post


Link to post
Share on other sites
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 

Share this post


Link to post
Share on other sites
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';

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More