Table of Contents

Flex Pass and Related Entity Mapping

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.

Glossary

We have identified -- or invented -- the following terms as requiring some explanation and context to ensure understanding:

  • Show: This word is used to refer to a play, musical, or other entity as it exists conceptually. It is the script, the music, the props, etc. It is not the actual event that a person would view. One cannot purchase a ticket to a Show; one purchases a ticket to a Performance of a Show. In the OpenTickets GUI this is called an "Event." A Show (or "Event") is modeled in the new system as a WordPress post (table wp_posts with a post_type of qsot-event) and associated metadata stored in the wp_postmeta table. A Show does not have an analogous entity in the legacy system, though use of CiviCampaign has been discussed as a syncing mechanism in the new system.
  • Performance: The performance of a Show, which can be viewed in real life. This is the entity for which patrons buy tickets, e.g. to see the Dec 11 Performance of the "Cat in the Hat" Show. In the OpenTickets GUI this is called an "Event Date". A Performance is represented by a CiviEvent in the legacy system and modeled in the new system as a post (table wp_posts with a post_type of qsot-event) with a post_parent of the related Show post. This post will have different metadata stored in the wp_postmeta table than a Show post.
  • Ticket Bundle: A collection of ticket types associated with a particular Flex Pass. In the legacy system, each Flex Pass contains a description of what the holder receives with each redemption. For example, a Flex Pass may be configured for 3 redemptions of 1 child ticket and 2 adult tickets, for a total of up to 9 tickets. Note that the total is for up to 9 tickets, because if a member of the party is not present for the redemption, the ticket is forfeited.

Concepts

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.

  • Flex Pass Type: The entity which represents a type of pass and identifies the number of allowed uses, the redemption method (e.g., a discount code), etc.
  • Flex Pass: The entity which represents a specific pass and identifies its owner.
  • Flex Pass Purchase: A record of an event in which a contact exchanges money for a Flex Pass.
  • Ticket Acquisition: A record of an event in a which a user acquires a ticket to a performance (this record includes seating assignments), whether by payment or Flex Pass Redemption.
  • Flex Pass Redemption: A record of an event in which a Flex Pass is “used” to acquire a ticket.

The Flex Pass Type Entity

Legacy Data Model

A Flex Pass Type is represented across a few CiviCRM tables:

  • A CiviCRM Event record (must be of type Subscription, i.e., event_type_id = 8) provides a field subscription_max_uses which indicates how many ticket bundles may be redeemed for this Flex Pass Type. Note that this field was added directly to core table civicrm_event.
  • Custom table civiboxoffice_subscription_allowances determines which Performances (also modeled as CiviCRM Events) and Flex Pass Types can be used together.

New Data Model

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.

Migration Notes

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).

The Flex Pass Entity

Legacy Data Model

A Flex Pass is represented across a few CiviCRM entities:

  • The CiviCRM Participant record (associated with a Subscription Event) identifies the owner (contact_id) and the Flex Pass Type (event_id).
  • Table civicrm_line_item contains records which represent the ticket bundle to which each use of the Flex Pass entitles its owner. A record exists for each type of ticket (e.g., child, adult, senior) in the bundle. For example, there are two records with entity_table civicrm_participant and entity_id 81800. The first shows that the Flex Pass may be used to redeem 1 child ticket. The second shows that the Flex Pass may be used to redeem 2 Adult tickets.
  • The number of remaining uses is not stored explicitly. It must be calculated. See the legacy data model for the Flex Pass Redemption Entity.

New Data Model

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:

    • post_content: A comment that explains how the coupon was created (e.g., Created by purchase of product #48).
    • post_date: The date the Flex Pass was created/purchased, in the local time zone.
    • post_date_gmt: The date the Flex Pass was created/purchased, in GMT.
    • post_name: Same as post_title.
    • post_title: Contains a code that can be entered into checkout screens to access the discount (e.g., 2018fp4adult-b47gk3).
  • Several rows of metadata are associated with that post in the wp_postmeta table. Some items of interest include:

    • discount_type: should always be set to percentage
    • coupon_amount: should always be set to 100
    • individual_use: should always be set to no
    • product_ids: this will be set to the ticket type e.g. Adult, Child, Senior
    • usage_limit: same as legacy system’s civicrm_event.subscription_max_uses field, e.g. this will be set to 4 for a 4-show Flex Pass
    • usage_limit_per_user: NULL
    • limit_usage_to_x_items: this appears to need to be set to 1
    • expiry_date: NULL
    • free_shipping: should always be set to no
    • exclude_sale_items: should always be set to no
    • customer_email: should be populated with the primary email address of the contact associated with the Flex Pass Participant record
    • event_ids: a comma-separated list of wp_posts.id integers, each of which can represent a show or a performance -- we wish to use only show IDs

Migration Notes

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 Flex Pass Purchase Entity

Legacy Data Model

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.

New Data Model

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:

    • post_content: NULL
    • post_date: The date the Flex Pass was created/purchased, in the local time zone.
    • post_date_gmt: The date the Flex Pass was created/purchased, in GMT.
    • post_name: A URL-safe version of post_title (e.g., order-aug-21-2018-0640-pm).
    • post_title: Contains the word Order and the date and time the purchase was made (e.g., Order – August 21, 2018 @ 11:40 AM).
  • Several rows of metadata are associated with that post in the wp_postmeta table:

    • Includes information about the customer (e.g. name, email, and physical address)
    • The order total
    • Coupon usages (serialized array)
    • Payment information
    • And more!
  • 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:

    • order_item_id: This is a unique identifier for this table, and is used in the wp_woocommerce_order_itemmeta table (details below) to associate metadata to each item in the order
    • order_item_name: populated with the title of the WooCommerce Product e.g. 4-Show Flex Pass - Adult
    • order_item_type: line_item
    • order_id: references wp_posts.ID
    • Any other items purchased in the same order will have their own row in this table
  • Finally, the table wp_woocommerce_order_itemmeta has several rows of metadata about each individual item in the order, some items of interest:

    • _product_id: The ID of the WooCommerce Product (table wp_posts with a post_type of product) purchased
    • _qty: Quantity
    • _line_subtotal: The amount of the item before coupons/discounts
    • _line_total: The amount of the item after coupons/discounts have been applied
    • _line_tax_data: a:2:{s:5:"total";a:0:{}s:8:"subtotal";a:0:{}}
    • _coupons: a serialized array of any coupons that were created by the purchase of this item

Migration Notes

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.

Ticket Acquisition Entity

Legacy Data Model

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`;
Field Type Null Key Default
seat_id int(11) unsigned NO PRI NULL
seat_created timestamp NO CURRENT_TIMESTAMP
seat_event_id int(11) NO MUL 0
seat_category_id int(11) NO MUL 0
seat_user_id int(11) YES NULL
seat_order_id int(11) YES MUL NULL
seat_row_nr varchar(5) YES NULL
seat_zone_id int(11) YES NULL
seat_pmp_id int(11) YES NULL
seat_nr int(11) NO 0
seat_ts int(11) YES MUL NULL
seat_sid varchar(32) YES NULL
seat_price decimal(10,2) YES NULL
seat_discount decimal(10,2) YES 0.00
seat_discount_id int(11) YES NULL
seat_code varchar(16) YES NULL
seat_status varchar(5) NO MUL free
seat_sales_id int(11) YES NULL
seat_checked_by int(11) YES NULL
seat_checked_date timestamp NO 0000-00-00 00:00:00
seat_old_order_id int(11) YES NULL
seat_old_status varchar(5) YES NULL

This schema hasn't been investigated thoroughly, but seat_row_nr and seat_zone_id seem likely to be relevant to the migration.

New Data Model

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:

  • A post (table wp_posts) with a post_type of shop_order. This is the entire order; if multiple tickets (or other products) are purchased in the same transaction they will be detailed in the wp_woocommerce_order_items table. Fields of note in this table:
    • post_content: NULL
    • post_date: The date the ticket was purchased, in the local time zone.
    • post_date_gmt: The date the ticket was purchased, in GMT.
    • post_name: A URL-safe version of post_title (e.g., order-aug-21-2018-0730-pm).
    • post_title: Contains the word Order and the date and time the purchase was made (e.g., Order – August 21, 2018 @ 7:30 PM).
    • post_status: wc-completed (this is slightly different than the standard published)
  • Several rows of metadata are associated with that post in the wp_postmeta table:
    • Includes information about the customer (e.g. name, email, and physical address)
    • The order total
    • Coupon usages (serialized array)
    • Payment information
    • And more!
  • Each item (ticket) in the order is captured in its own row in the wp_woocommerce_order_items table. Ticket purchases will be represented as follows:
    • order_item_id: This is a unique identifier for this table, and is used in the wp_woocommerce_order_itemmeta table (details below) to associate metadata to each item in the order
    • order_item_name: populated with the title of the WooCommerce product that was purchased e.g. Child Ticket, Adult Ticket, or Senior Ticket
    • order_item_type: line_item
    • order_id: references wp_posts.ID
    • Any other items purchased in the same order will have their own row in this table
  • The table wp_woocommerce_order_itemmeta has several rows of metadata about each individual item in the order, some items of interest:

    • _product_id: The ID of the WooCommerce Product (table wp_posts with a post_type of product) purchased. This will be a Ticket type e.g. Child Ticket for ticket purchases.
    • _qty: Quantity
    • _line_subtotal: The amount of the item before coupons/discounts
    • _line_total: The amount of the item after coupons/discounts (such as a Flex Pass) have been applied i.e. this will be 0 if a Flex Pass was used to acquire the ticket
    • _zone_id: The ID of the seat assignment in table wp_qsot_seating_zones
    • _event_id: The ID of the OpenTickets Event Date post (table wp_posts with a post_type of qsot-event) the ticket is associated with
  • 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.

    • event_id:

    • order_id:
    • quantity:
    • state:
    • since:
    • mille:
    • session_customer_id:
    • ticket_type_id:
    • order_item_id:
    • zone_id:
  • 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.

  • Additional information about the seating zone (type, color, size, ordering, position, etc.) is contained in the wp_qsot_seating_zonemeta table, though again none of this data is created during a ticket purchase. I also don't think

Migration Notes

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.

The Flex Pass Redemption Entity

Legacy Data Model

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:

  • One participant record, associated with the redeemer, is created in table civicrm_participant. The hacked-in subscription_participant_id column is populated with the participant ID of the Flex Pass that was redeemed.
  • Line items associated with the redemption Participant record will be created in civicrm_lineitems, one per ticket type. The qty field for each row indicates how many tickets of the type in question were issued. Note that this is inconsequential for the purposes of migration, since we are only concerned with number of times redeemed, not what the patron did with those redemptions.
  • By cross referencing a query like

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.)

  • One of the CiviBoxOffice extension authors reports that tickets redeemed with a Flex Pass will show as zero dollar transactions, though we have not validated this. Joining to the contribution or transaction details of a Redemption seems unlikely to be useful for the purposes of data migration; we should have sufficient information to create records of Redemption in the new system from the Participant records alone.

New Data Model

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:

    • order_item_id: This is a unique identifier for this table, and is used in the wp_woocommerce_order_itemmeta table (details below) to associate metadata to each coupon in the order
    • order_item_name: populated with the coupon code e.g. 2018fp4adult-pgbcrm
    • order_item_type: coupon
    • order_id: references wp_posts.ID
  • Finally, the table wp_woocommerce_order_itemmeta contains the following rows of metadata about each individual coupon used on the order:
    • discount_amount: The total amount in dollars that is being applied to the order. NB: depending on configuration a single coupon code or Flex Pass can be applied to multiple items in the transaction.
    • discount_amount_tax: Shows as 0 in all examples we have so far
    • coupon_data: Serialized array of coupon data

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.

Migration Notes

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.