Using SQL feature to export orders with UPC

[Adria Laxson]

I need to be able to have a UPC in my order export to send to warehouse. I bought a new order export module but it doesn't have this function that I need. I'm trying to pull the UPC code from the product file because it's not in the order file. I'm doing this via SQL query has a last ditch effort to get these orders exported.

I try this and get an error that I haven't done the subquery correctly (The Table "DEPENDENT-SUBQUERY" doesn't exis). Here is my code...

 

select F.id_order,

           F.InvoiceNumber,

           F.DeliveryNumber,

           F.InvoiceDate,

           F.DeliveryDate,

           F.ShipToCompany,

           F.ShipToFirstName,

           F.ShipToLastName,

           F.ShipToAddress1,

           F.ShipToAddress2,

           F.ShipToCity,

           F.ShipToState,

           F.ShipToPostal,

           F.ShipToCountryCode,

           F.BillToCompany,

           F.BillToFirstName,

           F.BillToLastName,

           F.BillToAddress1,

           F.BillToAddress2,

           F.BillToCity,

           F.BillToState,

           F.BillToPostal,

           F.BillToCountryCode,

           F.payment,

           F.shipping_number,

           F.email,

           F.lastname,

           F.firstname,

           F.date_add,

           F.product_name,

           F.product_reference,

           F.product_quantity,

           F.product_price,

           F.DiscountTotal,

           F.PaidTotal,

           F.PaidTotalActual,

           F.MerchandiseTotal,

           F.ShippingTotal,

           F.WrappingTotal,

           F.product_ean13,

           F.product_supplier_reference,

/*

This sub-select is used to make one final attempt to go get the UPC from the product record in the event the UPC is not contained on the order,

and this uses 2 additional techniques where PrestaShop appears to place product info.

*/

           IF(F.product_upc is null or F.product_upc = '',

           (

IF((

               SELECT distinct upc

               FROM ps_product_attribute

               WHERE reference = F.product_reference

               AND id_product_attribute = F.product_attribute_id

) is null, (SELECT upc FROM ps_product WHERE id_product = F.product_id), (

               SELECT distinct upc

               FROM ps_product_attribute

               WHERE reference = F.product_reference

               AND id_product_attribute = F.product_attribute_id

))

           )

           ,F.product_upc) as product_upc

from

(

SELECT     O.id_order,

           O.invoice_number as InvoiceNumber,

           O.delivery_number as DeliveryNumber,

           O.invoice_date as InvoiceDate,

           O.delivery_date as DeliveryDate,

           AD.company as ShipToCompany,

           AD.firstname as ShipToFirstName,

           AD.lastname as ShipToLastName,

           AD.address1 as ShipToAddress1,

           AD.address2 as ShipToAddress2,

           AD.city as ShipToCity,

           ADS.name ShipToState,

           AD.postcode as ShipToPostal,

           ADC.iso_code ShipToCountryCode,

           AI.company as BillToCompany,

           AI.firstname as BillToFirstName,

           AI.lastname as BillToLastName,

           AI.address1 as BillToAddress1,

           AI.address2 as BillToAddress2,

           AI.city as BillToCity,

           AIS.name BillToState,

           AI.postcode as BillToPostal,

           AIC.iso_code BillToCountryCode,

           O.payment,

           O.shipping_number,

           C.email,

           C.lastname,

           C.firstname,

           O.date_add,

           replace(replace(OD.product_name, '\n', ''), '\r', '') as product_name,

           OD.product_reference,

           OD.product_quantity,

           OD.product_price,

           O.total_discounts as DiscountTotal,

           O.total_paid as PaidTotal,

           O.total_paid_real as PaidTotalActual,

           O.total_products as MerchandiseTotal,

           O.total_shipping as ShippingTotal,

           O.total_wrapping as WrappingTotal,

           OD.product_ean13,

           OD.product_supplier_reference,

 

/*This sub-select is used to go get the UPC from the product record in the event the UPC is not contained on the order.*/

 

           IF(OD.product_upc is null or OD.product_upc = '',

(

select     IFNULL(P.UPC_PA,P.UPC_P) as UPC

from

(

/*Products with combinations.*/

select     p.reference as REF_P,

           p.id_product,

           pl.name AS NAME_PL,

           agl.name AS NAME_G,

           al.name AS NAME_A,

           pa.reference as REF_A,

           IFNULL(pa.upc,'') as UPC_PA,

           IFNULL(p.upc,'') as UPC_P,

           pa.id_product as id_product_PA,

           ag.id_attribute_group ,

           a.id_attribute ,

           pa.id_product_attribute

FROM       ps_product_attribute pa

LEFT JOIN  ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute

LEFT JOIN  ps_attribute a ON a.id_attribute = pac.id_attribute

LEFT JOIN  ps_attribute_group ag ON ag.id_attribute_group = a.id_attribute_group

LEFT JOIN  ps_attribute_lang al ON a.id_attribute = al.id_attribute

LEFT JOIN  ps_attribute_group_lang agl ON ag.id_attribute_group = agl.id_attribute_group

LEFT JOIN  ps_product p ON pa.id_product = p.id_product

LEFT JOIN  ps_product_lang pl ON p.id_product = pl.id_product

WHERE      al.id_lang =1

AND        agl.id_lang =1

and        pl.id_lang =1

union

/*Products without combinations.*/

select     p.reference as REF_P,

           p.id_product,

           pl.name AS NAME_PL,

           null AS NAME_G,

           null AS NAME_A,

           pa.reference as REF_A,

           IFNULL(pa.upc,'') as UPC_PA,

           IFNULL(p.upc,'') as UPC_P,

           pa.id_product as id_product_PA,

           null as id_attribute_group,

           null as id_attribute,

           null as id_product_attribute

FROM       ps_product p

LEFT JOIN  ps_product_lang pl ON p.id_product = pl.id_product

LEFT JOIN  ps_product_attribute pa ON pa.id_product = p.id_product

WHERE      pl.id_lang =1

and        p.id_product not in (

select     p.id_product

FROM       ps_product_attribute pa

LEFT JOIN  ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute

LEFT JOIN  ps_attribute a ON a.id_attribute = pac.id_attribute

LEFT JOIN  ps_attribute_group ag ON ag.id_attribute_group = a.id_attribute_group

LEFT JOIN  ps_attribute_lang al ON a.id_attribute = al.id_attribute

LEFT JOIN  ps_attribute_group_lang agl ON ag.id_attribute_group = agl.id_attribute_group

LEFT JOIN  ps_product p ON pa.id_product = p.id_product

LEFT JOIN  ps_product_lang pl ON p.id_product = pl.id_product

WHERE      al.id_lang =1

AND        agl.id_lang =1

and        pl.id_lang =1

)

)          P

where      P.id_product = OD.product_id

and        P.id_product_attribute = OD.product_attribute_id

 

),

           OD.product_upc) as product_upc,

           OD.product_attribute_id,

           OD.product_id

 

FROM       ps_orders AS O,

           ps_order_detail AS OD,

           ps_customer AS C,

           ps_address AS AD,

           ps_address AS AI,

           ps_state AS ADS,

           ps_state AS AIS,

           ps_country AS ADC,

           ps_country AS AIC

WHERE      AI.id_country = AIC.id_country

and        AD.id_country = ADC.id_country

and        AI.id_state = AIS.id_state

and        AD.id_state = ADS.id_state

and        AI.id_address=id_address_invoice

AND        AD.id_address=id_address_delivery

AND        C.id_customer=O.id_customer

AND        O.id_order = OD.id_order