beeplogic's avatar

Redeemded Gift Card Report

The Gift Card functionality in the Enterprise Edition of Magento is a key selling point of the platform. Unfortunately it appears that some of the technical implementation was either half baked or not thought out completely. In particular, the data structure of the gift card account and relating it to orders is not possible. The data exists, in some fashion, just not in a normalized structure that one would expect.

I had a request to generate a report containing which gift cards have been redeemed and to which orders. This simple report should contain the order number, gift card code, date of use, amount used. Surprisingly, the history table for gift cards does not contain an order ID column. Presumably this is an attempt to keep things “flexible” or to cover some use case where the gift card isn't redeemed within magento (even though the history records probably always comes from Magento…). I would've expected a column for the order entity ID, ideally with a foreign key to the sales_flat_order table. On the order side of things, when using a gift card it is not handled as a payment method and stored as a serialized PHP array in a text column.

The simplest bit of information I found to work with is a text column on the gift card history table, “Additional Information.“. When a gift card is used to pay for an order it contains “Order #1234.” as the value. Since the format would always be the same in this use case I knew I could extract the order increment ID by using a simple pattern. Ideally a regular expression pattern could be used in the column list to transform the value of the Additional Info column: /Order #(\d+)\./

Sadly, MySQL's limitations present a problem here. It does not have support for this type of functionality, its regular expression support is limited to matching (0, 1) results. You can read more about MySQL and regex here. This type of feature might enter that grey area of whether or not too much application logic should be done in your database. I personally think this feature allows for greater flexibility, especially when dealing with use cases like reports or data extraction. My database of choice, PostgreSQL, has support for this functionality.

If the pattern were any more complex I would've probably resorted to create my own order_id column in the history table and either:

  • find an event/model too hook into to place the order entity ID as the value
  • batch process the history and update each record accordingly.

Both approaches are more time consuming and require greater testing. Luckily I was able to use substring methods to return the parts I needed:

  • all characters except the first 7, meaning “Order #” would be removed
  • all characters except the last, taking care of the period “.”

It's important to note here that the value is subject to translation, so the string could appear differently if “Order #%s.” has been translated.

The gift card history table contains records of more than just when gift cards are spent, it contains logs of the creation and adjustment of gift cards. The format used in the Additional Info value was the same when creating and using a gift card so it was simple to filter these based on the value of the action column, 0 or 1. These list of actions are stored as constants in the Gift Card History model. Below is the query I came up with which I turned into a VIEW so that it could be fed into a table report via Kalen's custom-report module.

CREATE VIEW enterprise_giftcard_account_history_order AS
SELECT
   `h`.`action`,
   `h`.`giftcardaccount_id`,
   `h`.`updated_at`,
   `h`.`balance_delta`,
   `h`.`balance_amount`,
    substr(substr(`h`.`additional_info`,1,(char_length(`h`.`additional_info`) - 1)),8) AS `gc_hist_order_id`
FROM `enterprise_giftcardaccount_history` `h` 
INNER JOIN `enterprise_giftcardaccount` `gc` ON `h`.`giftcardaccount_id` = `gc`.`giftcardaccount_id`
WHERE `h`.`action` in (0,1);

It's now possible to join the order table to our view: SELECT * FROM enterprise_giftcard_account_history_order gch INNER JOIN sales_flat_order o ON o.increment_id = gch.gc_hist_order_id.

We can now pretend to have a “normalized” view of the data, even if it is in a hacky and shameful fashion.