Need sql query for prestashop to generate csv file

Slutfört Publicerat 5 år sedan Betalades vid leverans
Slutfört 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

MySQL PHP Prestashop SQL

Projekt-id: #18146131

About the project

4 offerter Distansprojekt Aktivt 5 år sedan

Tilldelades:

softcarve

hi, i would like to rewrite the attached query to get the correct invoice number from the ps_orders table.

€13 EUR inom 0 dagar
(128 omdömen)
6.3

4 frilansare har lagt bud på i genomsnitt €16 för det här jobbet

jonyahmed105

Hello I will do it for you sir. Thanks

€23 EUR inom 1 dag
(153 omdömen)
6.4
webgenius0322

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

€14 EUR inom 1 dag
(51 omdömen)
5.6
joyia19

i am database expert professional database developer and designer will solve your problem quickly i will not disappoint you the task is not lengthy but tricky it is solvable task and require some focus that is known as Mer

€14 EUR inom 0 dagar
(0 omdömen)
0.0