Advanced Price Sets, Event Attributes, deposit payment, and payment/invoice tracking

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.