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;