Need sql query for prestashop to generate csv file
€8-30 EUR
Betalades vid leverans
Hello,
I need a sql query that generate a csv from a prestashop database, value required values in the csv are the following:
id_order -> order number
id_order_invoice -> invoice number
total_products -> total paid of the order vat excluded and without shipping
total_shipping_tax_excl -> shipping paid vat excluded
state -> order state
date -> order purchase date
prod_ref -> prod reference
uds -> number of units
product_price -> unit product price vat excluded
Name -> customer first name + last name
ai_country -> invoice address prestashop country number
ai_address1 -> invoice address 1 field
ai_address2 -> invoice address 2 field
ai_postcode -> invoice post code field
ai_city-> invoice city field
ai_other-> invoice other field
ai_phone-> invoice phone field
ai_phone_mobile-> invoice mobile phone field
email -> invoice mobile phone field
I have a query that is almost working except that invoice number that should be extracted from ps_orders table and here is not the correct one (don't know why)
So you can fix it or create a new one
SELECT d.id_order as order_number,
id_order_invoice as invoice_number,
ROUND(total_products, 2) as total_product_tax_excl,
ROUND(total_shipping_tax_excl, 2) total_shipping_tax_excl,
[login to view URL] AS state,
o.date_upd AS date,
d.product_reference AS prod_ref,
d.product_quantity AS uds,
ROUND(d.product_price, 2) product_price,
CONCAT_WS( ' ', [login to view URL], [login to view URL] ) AS Name,
ad.id_country AS ad_country, [login to view URL] AS ad_address1, [login to view URL] AS ad_address2, [login to view URL] AS ad_postcode, [login to view URL] AS ad_city, [login to view URL] AS ad_other, [login to view URL] AS ad_phone, ad.phone_mobile AS ad_phone_mobile,
ai.id_country AS ai_country, [login to view URL] AS ai_address1, [login to view URL] AS ai_address2, [login to view URL] AS ai_postcode, [login to view URL] AS ai_city, [login to view URL] AS ai_other, [login to view URL] AS ai_phone, ai.phone_mobile AS ai_phone_mobile,
[login to view URL] FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order )
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer )
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group )
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state )
WHERE 1 GROUP BY d.id_order, d.product_name ORDER BY d.id_order DESC LIMIT 40
Projekt-id: #18146131
About the project
Tilldelades:
hi, i would like to rewrite the attached query to get the correct invoice number from the ps_orders table.
4 frilansare har lagt bud på i genomsnitt €16 för det här jobbet
Hi How do you do! As I am an experienced web developer, I have much experience with php, mysql and database management. I am sure I can handle your project successfully in short time. I am available to start immedi Mer