Event Price Sets and some explorations with custom search

CiviEvent is one of the more popular components in CiviCRM (followed by CiviContribute, CiviMember and CiviMail). We have improved CiviEvent significantly in the past few releases and more improvements are slated for CiviEvent in v2.1. With this increased usage, we've seen more folks using Price Sets. Briefly, price sets gives admins the option to break an event into smaller pieces and charge for each selected piece. Price Sets was one of the first significant of code contributions to CiviCRM (thanx to Ideal Solutions and Marshal Newrock). We were planning to restructure this code in 2.2, but there were two missing features which came up a few times with some of our 'regular' users. The first missing feature was that two options in a price set item could not have the same amount. In order to include meals on multiple days with the same price, you would have to separate them into different items. Price Sets did not allow an amount of 0 either in an option. Fixing this was relatively easy, we just shuffled around a few fields that we used in the civicrm_option_value table to fix the above problems. There is no export price set data feature in an easy manner for offline analysis / fulfillment. Admins would have to export a field with the options selected joined together as a text string. However export deals with information at the participant level. Different participants could be registered for different events. Events could be using different price sets. The price set information for each participant is stored in a "thin" table (civicrm_line_item, one record for every price set item selected). To deal with this, export would need to either create a temp table OR have an 'n' way LEFT JOIN with the line_item table. Also the number of columns would be the total number of choices of all the price sets. Since export uses the same code as search, we've avoided adding this level of complexity to search. This seemed to be a good match for custom search, so i built a custom search to export price set information for all participants of any event that used price set. The custom search is slightly different than our previous custom searches. While writing one query to get this information is probably doable, i took the easy way out and created a temporary table. The temp table has a couple of id columns (id, contact_id, participant_id), The other columns are based on the number of option value(s) present in the price set. There is a column for each option value with the default value of 0. We populate this table from the civicrm_participant table. We then collect all the line_items selected by the 'participant' and populate the temp table. All the queries now are simple queries on this temp table. You can check the code for this custom search here In other custom search news, our ace developer Sunil is creating a custom search to find all the contacts who are part of group A, B or C and not in group X and Y and Z. To keep this simple, we also use a couple of temp tables, one for the include group of contacts and the other for the excluded group of contacts. You can find his code here. This is still a work in progress. There is also a new custom search listing page. You can see it on our 2.1 sandbox. Note that 2.1 is still in active development, so please do not file any issues for 2.1.

Comments

Is it possible to backport it to 2.0 ?

Could you point me to the svn version or update sql to run ? (didn't find it)

X+

http://issues.civicrm.org/jira/browse/CRM-3209?page=com.atlassian.jira.ext.fisheye:fisheye-issuepanel

I'll try to generate patch and apply.