Price Sets

April 10, 2007 - This functionality is currently in development as a contributed feature by Marshall Newrock. It will be officially incorporated in the 1.8 release, but a 1.7 compatible version will be available prior to that for those who are interested.

Overview

An event like a conference may have multiple items that may be charged for, more than can be conveniently handled with the fee levels. For example, you can have:

• base registration cost (supporter, member, executive)
• optional dinner for attendee
• and dinner for attendee's guest

With three registration levels which have 0, 1, or 2 dinners, that's already nine prices.

Proposal

With price sets, a group of fields which will be added up for a total cost. This would work similar to Custom Data. The fields may be integer, drop-down, radio, or checkbox. In the case of checkbox fields, the cost will include the sum of all checked boxes. This is useful, eg, for selecting which days you will attend. The total cost of an integer field is the value entered times its price.

Validation rules would be useful, to prevent nonsensical input. If you're only attending on Saturday, it seems unlikely you'd want to register for the Friday lunch. Integer fields should have minimum and maximum values. Negative values would be prohibited, 0 would be treated as if it were empty. There would have to be a good way to refer to other fields and options.

A fieldset should be able to have another field as the parent. Example, if you select "entire conference" you shouldn't be able to select which days you're going to attend.

The parent field differs from a validation rule in that a validation rule is checked on submit, whereas the parent field would actually disable a form input. Validation rules would be implemented before parent fields.

A little bit of Javascript to display a running total would be nice, but that's the last step after everything else is working.

End-User Procedure

A user will first create a Price Set. To the Price Set, Price Fields can be added. Price Fields will have options. This method, as opposed to having a pool of price fields, will prevent problems as people change values of in-use fields.

It will be prohibited, or at least very difficult, to change Price Sets which are in use.

Once a price set has been created, an event form may then use it. Event forms may only have a single price set. The price set will appear between the two custom profiles, if used.

For backwards compatibility and simplicity where needed, a form may use either the current method of fee levels, or the new method of price sets.

Details

Price Sets will use the existing custom_option table. custom_field and custom_group will be copied and modified as necessary. This allows for the addition of price-specific features later.

Fields may be date-limited, but options may not. Date-limited fields allow for situations such as early registration, where the price is more after a certain date.

If a field is not text, is required, and only has a single value, it will instead be displayed as a hidden field. This allows for "base values".

Negative values should not be disallowed. When combined with ACL's, a single-valued field may be a discount. Currently, there are no plans to support percentages, as the application is limited and almost certainly addressed by ACL's anyway.

There will be no support for collapsed or tabbed display. This will only invite confusion.

Price sets which are not active will still be displayed on forms which use them. They will not be available for selection in new forms.

Storing Participant Selected Fee Options

When an event form with a price set is submitted, all information must be stored, as fields may be changed while the form is live. This is analagous to shopping cart systems which store complete line-item info for orders.

Phase 1 - complete

• Implement text, radio, checkbox, and select form fields, and group/field editing.
• Allow selecting of a price set with an event form.
• Functions for storing and retrieving participant price-set data.
• Lock fee groups which belong to a form which is live or which has any registrants.
  • This is not implemented as a lock, as such. The key limitation is that it is not possible to delete or disable a price set which is being used by any form. If you try, it refuses and you are given a list of forms which use the price set so you can change them.
  • Changes to the fields are not prevented. This was deemed not necessary, as when a form with a price set is submitted, all of the selected fields are saved. If the price set is changed or deleted, this information remains.
• select-type fields with associated quantity input

Phase 2+

This is everything after phase 1. When phase 2 begins, some items will be selected and others shunted to phase 3+

• Allow date-limited fields
• ACL's
• Discounting mechanism (either permissioned fees via ACL's and/or ability to setup Discount Codes that the user can input and can be passed via GET params - i.e. from a mailing to members)
• Copy field from another fee group
• Include unlock option for the above. It will at least keep the user from accidentally messing things up.
• Javascript based validation.
• formRule() based validation
  • This should not be actual PHP code or function names. Instead, look for a "validation language" of some type which will be easily parsable, and can easily have a graphical frontend for creating rules.
• Inventory tracking (of more than just available participant seats)
• quantity discounts
  • example: 1-99 cost $25 each, 100-499 cost $20 each, 500+ cost $15 each

DB Implementation

civicrm_price_set

A set of price fields which can be embedded in an event registration form (and eventually in other forms). During form processing, the price_fields (and options) for the set are totalled.

• id int unsigned primary key (Price Set)
• domain_id int unsigned not null references civicrm_domain (Which domain owns this fee group)
• name varchar(255) unique (Variable name/programmatic handle for this set)
• title varchar(255) unique (Friendly name)
• help_pre text (Description and/or help text to display before fields in form)
• help_post text (Description and/or help text to display after fields in form)
• is_active bool default 1 (Is this set active)
• javascript text (Optional Javascript script function(s) included on the form with this price_set. Can be used for conditional )

civicrm_price_set_entity

Join table links a price_set to an entity. Initially, the entities will always be Event Pages.

• id int unsigned primary key
• entity_table varchar(64)
• entity_id int(10)
• price_set_id int(10)

Indexing and FK:

• entity_table+entity_id
• entity_table+entity_id+price_set_id
• price_set_id FK civicrm_price_set.id

civicrm_price_field

• id int unsigned primary key
• price_set_id int unsigned not null references civicrm_price_set (FK to civicrm_price_set)
• name varchar(255) unique (Variable name/programmatic handle for this field)
• label varchar(255) unique (Text for form field label (also friendly name for administering this field))
• html_type enum(Text, Select, Radio, Checkbox)
  • NOTE: Checkboxes and Multi-select 'Select' fields are automatically handled as 'accumulators' in processing logic. They carry an implied "quantity" of ONE. Text fields are used for cases when the end-user can enter a variable quantity: [ 3 ] Orchestra Tickets @ $25.00 each
• is_enter_qty bool default 0 (Enter a quantity along with a selection)
• help_pre text (Description and/or help text to display before fields in form)
• help_post text (Description and/or help text to display after fields in form)
• weight int (Order in which the fields should appear)
• is_display_amounts tinyint default 1 (Should we automatically display the option value next to the label for each price option? If false, it is up to admin to display option pricing in the label or other text on the form.)
• options_per_line int unsigned (number of options per line for checkbox and radio)
• is_active bool default 1 (Is this fee field active)
• is_required bool default 0 (Is this fee field required)
• active_on datetime default '0000-00-00 00:00:00' (If non-zero, do not show this field before the date specified)
• expire_on datetime default '0000-00-00 00:00:00' (If non-zero, do not show this field after the date specified)
• javascript varchar(255) (Optional scripting attributes for field - e.g. onclick, onchange etc.)

NOTE: text fields will be stored with a single option which contains the unit price.

civicrm_custom_option

Use this existing class to store option label and value (amount). Assuming we want to treat price_fields of type text in the same way as all the others, then we would store a single custom_option record for that field and clone the price_field label as the custom_option.label.

civicrm_line_item

This table stores the "selected items" in the end-user's "order". The order TOTAL is stored in the parent entity record - civicrm_contribution.total_amount in the case of Event Registrations. We store foreign keys to the price_field and custom_option to faciliate search and aggregation of totals (e.g "210 people signed up for the Special Luncheon"). We store the option label, qty and unit price in effect at "registration time" to insulate this order record from changes in the pricing tables.

• id int unsigned primary key
• entity_table varchar(64) ('civicrm_contribution' for current cases)
• entity_id int(10) (FK contribution.id)
• price_field_id int not null (FK to price_field.id)
• custom_option_id int not null (FK to custom_option.id)
• label varchar(255) (descriptive label for item - from custom_option.label)
• qty int (how many items ordered)
• unit_price money
• line_total money (qty * unit_price. Storing this will make SUM queries a bit easier)

Issue: This structure has several layers of indirection which we may want to rethink. Working backwards to the registering participant from this table requires the following joins:
price_value.entity_id -> contribution.id
contribution.id -> participant_payment.payment_entity_id
participant_payment.payment_entity_id -> participant_id -> participant.id
participant.contact_id -> contact.id

civicrm_acl

This existing class can be used to control access to/display of price_field records.

civicrm_form_rule

work in progress. do not implement

• id int unsigned primary key
• entity_table varchar(64) not null (Name of table where item being referenced is stored.)
• entity_id int unsigned not null (Foreign key to the referenced item.)
• rule_name varchar(64) (Short descriptive name)
• form_rule text (The validation rule)
• rule_message varchar(255) (Text displayed when the rule fails)

civicrm_inventory

work in progress. do not implement

• id int unsigned primary key
• entity_table varchar(64) not null (Name of table where item being referenced is stored.)
• entity_id int unsigned not null (Foreign key to the referenced item.)
• qty_available

Additional implementation notes

Handling complex parent/child price field use cases

This is probably 'phase 2' functionality. Implementation might be adding a parent_field_id to price_field and embedding show-hide display rules in the fields accordingly. However, we could allow folks to do some of this in Phase 1 if we structure the template such that each field is in a block (div?) with a unique field-specific ID. Folks could then configure set-level and field-level javascript to do the show/hide tasks. For simple cases, we already include a showHideByValue() function in Common.js which can be invoked at the field level.