Get Products by Coupon

Question

I want to get products by coupon code filtered whether by SKU or Mark so I can get theses details:

,order date price before discount , price after discount, name /surname of the client, telephone , country and product name

For order date ,price before discount and price after discount I got them named 'created_at','subtotal' and 'grand_total' from the table sales_flate_order . But I can not find the other items

I have done an essay , may you check it

SELECT
    /*   sales_flat_order.coupon_code AS coupon,*/
    catalog_product_entity_text.value As nomproduit,
    sales_flat_order.created_at   AS datedecommande,
    sales_flat_order.subtotal   AS prixavantremise,
    sales_flat_order.grand_total AS prixapresremise,
    sales_flat_order_address.firstname  AS prenom,
    sales_flat_order_address.lastname As nom,
     sales_flat_order_address.telephone AS telephone,
sales_flat_order_address.country_id AS pays
FROM catalog_product_entity_text
         LEFT JOIN sales_flat_order
                   ON (sales_flat_order.entity_id = catalog_product_entity_text.entity_id)
         LEFT JOIN sales_flat_order_address
                   ON (sales_flat_order.entity_id = sales_flat_order_address.entity_id)
        LEFT JOIN catalog_product_entity
                   ON (catalog_product_entity.entity_id=sales_flat_order_address.entity_id)
  where sales_flat_order.coupon_code = 'wxxxx'
GROUP BY catalog_product_entity.sku;

Thanks in advance

Answer 1

below code use to get all the order related to coupon code 496672 , you can use your own coupon code

<?php
require 'app/Mage.php';
Mage::app();
$order_collection = Mage::getModel('sales/order')
    ->getCollection()
    ->addFieldToFilter('coupon_code','496672')
    ->setPageSize(50)
    ->setCurPage($p)
    ->setOrder('entity_id', 'DESC');
foreach ($order_collection as $order) {
    echo $order->getIncrementId().'<br>';
}
?>

to get product you need to fetch order item