This is a summary of ideas from this forum topic, http://forum.civicrm.org/index.php?topic=15983 , and discussion should continue here.
I'm working on a CiviCRM/Drupal installation for an organization that puts on workshops and houses and feeds people for the duration of the workshops. CiviCRM's built-in way of handling price sets as flat lists of options and prices, doesn't quite do what we want.
We need events to have many signup options, including length, since we sometimes give the option to attend partial workshops, and fee level, since we have different pricings for students and the unemployed. Many other options' prices, in turn, need to depend on the length field and the fee level field - for example, housing option prices need to be adjusted based on length of attendance selected, since a private room for a weekend would not cost nearly as much as a private room for a full week, and workshop fee prices would depend on the feel level selection. We also need it to be general enough so a field's pricing can have multiple dependencies; that is, a field's price could depend on both length and fee level rather than just on one of the two. Participants signing up would first select their workshop length and fee level, and the rest of the options' prices would adjust accordingly.
Also, CiviCRM's way of handling price sets - as available for all events - isn't quite right for this organization's needs - they'd need to have price sets perhaps as starting templates, but allow the specifics, like the costs of various options, and perhaps even the options themselves, to vary event by event. It doesn't make sense for us to accumulate as many global price sets as we have events.
We also need to give participants the option of paying the deposit up front and then the balance upon arrival instead of paying the full fee up front. That also means that we need to keep track of when people pay, how much they pay, and how much they owe. I know that Accouting and Finance, when it's finished, will be able to track payments and balances but not necessarily allow for deposits upon signup. Anyway, for time being, I'm thinking of writing a module to bridge event signups into Ubercart invoices, and then completely paid invoices back to Civi as completed signups, so that we can track partial payment.
Detailed information about the problem and proposed implementation:
Spec
We will preliminarily call this system "Advanced Price Sets."
There are some requirements:
1) Multiple fields with signup options and corresponding pricing:
- "Independent fields," the selections of which change "dependent fields'" pricing.
- "Dependent" fields whose pricings depend upon designated indepentend fields' selections.
For example, a choice in the "Length" independent field (weekend, Thurs-Sun, whole week) would affect the pricing of the "Dorm" dependent field's choices, since the pricing of shared dorm rooms versus private rooms would vary with the length of stay. There could be a situation with two independent fields affecting one dependent field. For example, selections in both I.F. "Length" and I.F. "Fee level" (employed professional, student, unemployed) would affect the "Tuition" D.F.'s pricing.
4) A set of "attributes" for each event with names like "Monday lunch," "Monday dinner," "Monday night shared dorm," "Monday night private room," etc, a default selection of which would be stored with each participant's registration depending on the selections of the I.F.'s and D.F.'s. For example, a selection of Weekend only and Shared dorm would cause the default selections to include Friday night shared dorm and Saturday night shared dorm. The selection of attributes stored with each participant's record needs to be overridable by an admin to account for a need to make note of, for example, someone not being able to make it to a specific meal. All these attributes need to be tallied for reports so, for example, the chef knows counts for each meal and housing people know counts for each night.
5) Improved price set display and editing workflow. Because every workshop will have slightly different options and pricing, we need to display and edit the price sets along with the individual events themselves instead of on a global price set editing page. It doesn't make sense for us to accumulate as many global price sets as we have individual events.
6) The option to pay either the full fee up front or a fixed deposit and then the balance upon arrival. All these records need to be updated in the system.
7) Overriding. Selected values for fields, pricings, and attributes need to be stored with each registration as they are at the time of registration, but also need to be overrideable case by case.
Implementation ideas
Many options were discussed, including creating an entirely new architecture for event pricing and signup with new database tables and multi-dimensional arrays to store all possible pricings. That schema is attached to the forum post. However, it seems like the most sane and natural solution so far is the following, based on Dave Greenberg's suggestion, which piggy-backs on the existing Price Set functionality:
- Create filters which can be defined at the Event level. For my example, one filter would have values 'Full Week', 'Thursday-Sunday', 'Weekend Only' and the other would have 'employed professional,' 'student,' unemployed'
- When filter values are defined, admin can select a filter for any Price Set field. For my example, admin would create 9 Price Set fields for the Tuition - with the corresponding prices for each combination of Length and Registration Level.
- At registration, user / staff selects a filter value (or it's passed in via GET param) first. Price set fields are included in the registration form based on the filter (price set fields which don't have filter value would always be included).
Then, on top of this basic structure, build the functionality for attributes and saving/overriding.
We're waiting on the Finance and Accounting project ( http://wiki.civicrm.org/confluence/display/CRM/Finance+and+Accounting ) for proper handling of partial payments and adjustments to invoices. I suggested using Ubercart plus a new Ubercart/Civi integration module to route all CiviCRM payments through Ubercart as Ubercart orders, and then record the payment information back in CiviCRM once it's paid (http://forum.civicrm.org/index.php/topic,15983.msg70647.html#msg70647). This would take care of the problem but require extra work migrating the data stored in Ubercart back to CiviCRM once Finance and Accounting is available.
Comments
There are some new features for pricesets in v3.3 that you should become familiar with, including the ability to define a maximum number of participants on a field-option level (not just a field level). But more pertinent to your items -- the new changes involved moving fields and options to their own table structure. Previously they used the option group/value tables, which was limited in terms of extendability. With their own structure it will be easier to build additional features into the schema.
Bulding on Brian's comment (lcdweb) - could you stick with the existing data structure and customize the UI a bit?
* Each fee option stored as a civicrm_price_field_value row (at the lowest attribute level). EXAMPLE: "Monday shared room at full week student price".
* Each selected fee option stored as a civicrm_line_item row. EXAMPLE: When Susan Student registers and selects shared room for the full week, you create 7 line item rows with the daily price.
Rules for generating these get stored somewhere else (custom DB table or xml file or .....), and some hooks would need to be used to hide some of the details stored in the price_field_value records from the register flow and then create the granular line items.
The benefit here is that once the participant is registered, the existing UI for viewing and reporting and exporting etc all work as is. At this point, not sure if this is a hack or a good idea :-)
Still have to get my head around this one a little more - but could it be made to account for the fact that the relationship between number of days and price isn't necessarily linear? Every price is going to be an exception and we really need to spell everything out.
Oh! You're saying to literally spell everything out like this:
Monday night shared student price full week
Tuesday breakfast student price full week
Tuesday morning workshop A student price full week
(...)
Monday night shared professional price full week
Tuesday breakfast professional price full week
(...)
This is the first time I've said that something would be too much granularity... but this is too much granularity. It doesn't really make sense logically from the organization's standpont to attach specific prices to individual workshop sessions at various rates when that's not necessarily literally how the event's pricing is determined. The attributes are more to get counts for everything than to break down pricings. I'll run it by them, though. Thanks!
Confirmed that they don't want the prices attached to granularity - it wouldn't make sense since they think about the signup options' prices as packages rather than broken down. Attributes are for keeping track of who's doing each specific part of an event.