BACT Flex Passes allow a user to see multiple shows at discounted prices with flexibility. A user can redeem purchased Flex Passes for the shows they wish to see, and on the days and times that work best for them. 4-, 5-, 6- and 7-show Passes are available for purchase. A patron may opt to reserve tickets to shows for all the uses available on their Flex Pass once at the beginning of the season, or on a show-by-show basis. Purchasing a Flex Pass entitles the patron to advance ticket sales, reserved seating sections, and other perks.
In the legacy system, Flex Passes (and associated events) were represented in CiviCRM and Fusion Ticket, the latter of which was primarily used to manage seat mapping and assignment.
In the new system, Flex Passes and associated events will be represented in OpenTickets, which is built on top of WooCommerce. Fusion Ticket will go away, and select data will be synced to CiviCRM.
We have identified -- or invented -- the following terms as requiring some explanation and context to ensure understanding:
For the purposes of transforming and migrating Flex Pass data, we are concerned with the following entities/events. Although our level of interest in migrating these entities varies, it is important to draw clear lines between them, especially since the concepts are conflated to some extent in the legacy data model.
A Flex Pass Type is represented across a few CiviCRM tables:
The new system doesn't have a Flex Pass Type entity per se. The new system utilizes a WooCommerce Product (table wp_posts with a post_type of product) to facilitate the purchase of Flex Passes, but this operates more as a template for creating Flex Passes than a separate entity which bestows properties upon (or must be referenced by) a child entity.
We anticipate that the WooCommerce Products will be created in the new system manually. We do not anticipate that the Flex Pass Type entity will need to be migrated from the legacy system except to fill in the blanks for individual Flex Passes, as these have a more robust model in the new system (i.e., we expect the legacy system’s Flex Pass Type and Flex Pass entities will be collapsed in the new system).
A Flex Pass is represented across a few CiviCRM entities:
A significant difference between the data models is that, whereas the legacy system treats the Flex Pass as a ticket bundle which may be redeemed many times, the new system treats it as a ticket type (e.g., child, adult, senior) that may be redeemed many times.
The distinction is made clear with an example. In the legacy system, Flex Pass ID 81800 is good for two uses of a ticket bundle which includes one child and two adult admissions. Suppose Mom redeems Flex Pass 81800 to take Junior to a performance, but Dad can’t make it. In this case, Dad’s ticket is lost, as his seat was part of the bundle redemption even though it wasn’t used.
(Note that in the legacy system, the limiting quantity here is not "performances" but "uses," which refers to the act of redeeming Flex Pass credits for tickets. If, at 9am today, Mom redeems Flex Pass 81800 for 1 adult ticket to Performance A, that's one usage. If she comes back at 10am to get another adult ticket for Performance A, that's her second usage. She can't get any more tickets on this Flex Pass.)
In the new system, Flex Pass 81800 would be represented as three Flex Passes: one for Junior (2 child admissions), one for Mom (2 adult admissions), and one for Dad (2 adult admissions).
A Flex Pass is modeled in WooCommerce as a coupon code, which is comprised of:
A post (table wp_posts) with a post_type of shop_coupon. Fields of note:
Several rows of metadata are associated with that post in the wp_postmeta table. Some items of interest include:
Flex Passes for past seasons need not be migrated.
The new system allows many of the properties that were configured on the Flex Pass Type entity in the legacy system to be set on individual Flex Pass entities. Thus, in order to create the new Flex Passes, the developer will need to reference both the Flex Pass and the Flex Pass Type entities in the legacy system.
Many more Flex Passes will be created in the new system than were represented in the legacy system. The new number of Flex Passes could be calculated with this pseudo-query:
SELECT SUM(qty) FROM civicrm_line_item WHERE entity_table = 'civicrm_participant' AND entity_id IN (/ some query that returns a list of Participant records associated with subscription Events /)
(See the example referencing Flex Pass ID 81800 for context.)
While the legacy system's civiboxoffice_subscription_allowances table associates Flex Passes with Performances, we wish to associate them with Shows in the new system, as it makes administration less onerous (e.g., "this Flex Pass can be used for any performance of these two Shows," rather than "this Flex Pass can be used for any of these 53 Performances"). Unfortunately, the legacy system has no concept of Shows. The Shows will be created manually ahead of the Flex Pass data migration, as will be the WooCommerce Products that provide templates for creating new Flex Passes. These Products will be configured with the allowed Shows. At import time, it may be useful to reference these records (or use a WooCommerce API to create a new Coupon from a Product) to ensure each Flex Pass has the correct Show associations; otherwise the associations can be hardcoded into the migration script. (Note that the max number of uses will be unique in each system; e.g., for a legacy Flex Pass with a subscription_max_uses of 4 there will be only one Product with a matching usage_limit).
The purchase of a Flex Pass is represented as a CiviCRM Contribution. Table civicrm_participant_payment links all Participant records (a superset of the Flex Pass entity) with CiviCRM Contribution records. These appear to be of Contribution Type, though it is likely more important that they be linked to Participant records.
See the Flex Pass Type Entity New Data Model for details regarding the mechanism or entity which facilitates the purchase of a Flex Pass. The purchase of a Flex Pass is modeled in WooCommerce as an order, which is comprised of data stored in four (4) tables:
A post (table wp_posts) with a post_type of shop_order. Fields of note:
Several rows of metadata are associated with that post in the wp_postmeta table:
Each item in the order (including non-Flex Pass Products) is captured in its own row in the wp_woocommerce_order_items table. The Flex Pass Purchase will be represented by as such:
Finally, the table wp_woocommerce_order_itemmeta has several rows of metadata about each individual item in the order, some items of interest:
It is still somewhat unclear as to whether or not we need to migrate the actual purchase of a Flex Pass from the legacy system and represent it in the new data model. For consistency in reporting, it would seem prudent to migrate this purchase information into the new system (OpenTickets/WooCommerce) and have it synced back to CiviCRM. This would ensure that any Flex Passes purchased for the current season in the legacy system are represented using the same data model of Flex Passes purchased in the new system after deployment. Flex Pass Purchases for past seasons definitely need not be migrated.
The purchase of a ticket to see a performance is modeled as a CiviCRM Event Registration, which includes a Participant record and Contribution for that event. Seat assignments are stored in a Fusion Ticket table Seat. The Participant ID is forced into the Seat record's seat_order_id field during checkout.
Here's the Seat schema:
mysql> DESCRIBE `Seat`;
This schema hasn't been investigated thoroughly, but seat_row_nr and seat_zone_id seem likely to be relevant to the migration.
The purchase or acquisition of a ticket is modeled in WooCommerce as an order or purchase of tickets to a performance ("Event Date" in OpenTickets vernacular). This data is stored across seven (7) tables, though only five (5) of those tables have data written to them at the time of acquisition:
The table wp_woocommerce_order_itemmeta has several rows of metadata about each individual item in the order, some items of interest:
The table wp_qsot_event_zone_to_order appears to link orders, events, line items, seating assignments, and more... all in one handy table. The fields are listed below, the only one I am unsure of at the moment is the mille field.
The table wp_qsot_seating_zones contains the human readable name of the seat assignment in the name field, and is referenced by the _zone_id field in the wp_woocommerce_order_itemmeta and the zone_id field in the wp_qsot_event_zone_to_order tables. NB: the entries in this table are not generated when a ticket is purchased. The information stored here is referenced by the data created when a ticket is purchased, and the name field appears to be necessary for transforming and migrating data from the legacy system.
Any tickets that have been acquired for the current season will need to be transformed and migrated to the new system, regardless of whether they were acquired via use of a Flex Pass or simply paid for. This is to ensure people will be able to use tickets they have purchased to performances that haven't occurred yet, and also that their tickets and seat selections won't be resold.
We expect the migration and transformation of ticket acquisitions to be fairly straight-forward, as standard CiviCRM and Word Press/OpenTickets data models are employed and for the most part we can extract the info from the source and shove it into mostly analogous fields in the new system.
Flex Pass Redemption does not really merit its own entity. It's just a variation of Ticket Acquisition. However, because quite a bit of explanation is required to distinguish Flex Pass Redemptions from other event registrations, it is useful to have a separate heading.
Each use of a Flex Pass is modeled as a CiviCRM Participant record. Flex Pass uses are distinguished from other event registrations by the population of the subscription_participant_id column.
The Participant record will be associated with the Contact ID of the purchaser, which need not necessarily be the same Contact who purchased the Flex Pass, as Dad may use Mom's email address during redemption.
Suppose a user registers three participants for a Play with his first redemption of a 4-use Flex Pass:
SELECT COUNT(*) FROM civicrm_participant WHERE subscription_participant_id = X
with the hacked-in subscription_max_uses field on civicrm_event, the system can report that 1 of the 4 allowed uses of the pass have been exercised. (Note that participant record X -- i.e., the Flex Pass -- has a field event_id by which the subscription_max_uses field can be joined.)
The use of a Flex Pass is modeled in WooCommerce as a line item with order_item_type of coupon; it will be associated with an order of tickets to a performance. Essentially, the data model is the same as a Ticket Acquisition, with the addition of a row in the wp_woocommerce_order_items table and other associated metadata in the wp_woocommerce_order_itemmeta table.
Unlike in the legacy system, a Flex Pass may be used multiple times in a single order. Each use is captured in its own row in the wp_woocommerce_order_items table. The use of the Flex Pass to redeem a ticket will be represented as follows:
Our initial observations about WooCommerce’s storage of coupon usage are similar to the legacy system i.e., that it is not explicitly stored but is inferred by inspecting purchases. At first blush it appears that getting a count of coupon line items where info in the wp_woocommerce_order_itemmeta.coupon_data field matches up with data in a post (table wp_posts) with a post_type of shop_coupon when cross-referenced would enable the system to report that X of the Y allowed uses of the pass have been exercised. Making this calculation is unnecessary for migration but may be useful for performing quality assurance.
Evan ain't sure what to put in here, needs Frank's help in teasing out how we transform the legacy data model into the new one. Hopefully we can extract the right kind of information from the legacy system.