Export Points Earned vs Spent on an Order

Export Points Associated with Orders

Please replace $ORDER_ID in queries below with order’s ID.

1. Export points spent on an order

SELECT
   SUM(main_table.quantity) AS `points_count`,
   `main_table`.*,
   GROUP_CONCAT(main_table.rewards_transfer_id) AS `transfer_ids`,
   `reference_table`.`rewards_transfer_reference_id`,
   `reference_table`.`reference_type`,
   `reference_table`.`reference_id`,
   `reference_table`.`rewards_transfer_id` AS `transfer_id` 
FROM
   `rewards_transfer` AS `main_table`    
LEFT JOIN
   `rewards_transfer_reference` AS `reference_table` 
      ON main_table.rewards_transfer_id = reference_table.rewards_transfer_id 
      AND main_table.source_reference_id = reference_table.rewards_transfer_reference_id 
WHERE
   (
      reference_id = '$ORDER_ID'
   ) 
   AND (
      reference_type = '1'
   ) 
   AND (
      status != 1
   ) 
   AND (
      quantity < 0
   ) 
GROUP BY
   `main_table`.`currency_id`
<br>

2. Export points earned on an order

SELECT
   SUM(main_table.quantity) AS `points_count`,
   `main_table`.*,
   GROUP_CONCAT(main_table.rewards_transfer_id) AS `transfer_ids`,
   `reference_table`.`rewards_transfer_reference_id`,
   `reference_table`.`reference_type`,
   `reference_table`.`reference_id`,
   `reference_table`.`rewards_transfer_id` AS `transfer_id` 
FROM
   `rewards_transfer` AS `main_table`    
LEFT JOIN
   `rewards_transfer_reference` AS `reference_table` 
      ON main_table.rewards_transfer_id = reference_table.rewards_transfer_id 
      AND main_table.source_reference_id = reference_table.rewards_transfer_reference_id 
WHERE
   (
      reference_id = '$ORDER_ID'
   ) 
   AND (
      reference_type = '1'
   ) 
   AND (
      status != 1
   ) 
   AND (
      quantity > 0
   ) 
GROUP BY
   `main_table`.`currency_id`<br>

NOTE: These queries will return all points spent/earned on the order, regardless that these have been approved or not. This will not include points that have been canceled/revoked.

If you want to get only points approved, that are pending, etc....change WHERE condition for status column using following constants:

STATUS_CANCELLED = 1;

STATUS_PENDING_APPROVAL = 3;

STATUS_PENDING_EVENT = 4;

STATUS_APPROVED = 5;

STATUS_PENDING_TIME = 6;