getting ordered products which have been delivered and not returned

[kerrywales]

Using PS 1.6.1.0 on Prestashop Cloud

 

I am working out how to get a list of customers who have purchased a particular product. As there is no "stat" for that I have been looking into the Advanced Parameter->SQL Manager to get me the list.

 

Looking through the list of tables in the list I have come up with the SQL statement below. It may be inefficient but it appears to give me the right results 'ish.

 

I need to make sure that the "order" has been paid for, delivered and not returned (at the time of producing the list).

 

Can anyone give me guidance to the best way to ensure that select ONLY products that have been paid for and not returned?

 

Regards

 

SELECT c.company, c.firstname, c.lastname, c.email, p.reference as product_reference, o.reference as order_reference 
FROM  ps_product AS p, ps_orders as o, ps_order_detail as od, ps_customer as c 
WHERE 
od.product_id=p.id_product
AND o.id_order=od.id_order
AND o.id_customer=c.id_customer
AND p.reference="XXXXX"