Get a CiviEvent Price to display in Views

[geilhufe]

Comments and suggestions welcome. Warning: I do not program, just change stuff in sample code

Basic use case:
Display upcoming events and their price to Drupal users.

General considerations:
I really would prefer to have a node - event relationship where civicrm events (as defined by an existing view) are synced with corresponding drupal nodes. Through CCK, it would be possible to add Drupal fields to CiviCRM events and manipulate them inside Drupal.
<<NOTE: any civicrm field work should probably be done for D7 since fields are now in core>>

Approach:
Since the existing views integration does almost everything I need, I really just want a price field to return in the view.

Option #1:
Create a views custom field that calls the Civi API to get the price. (http://drupal.org/project/views_customfield).

Option #2:
Manually sync a Drupal Event node with the CiviCRM event. The Drupal node would be manually created. The CiviCRM event ID would be manually populated. All the required CiviCRM data would get pumped into the Drupal event via CCK computed fields. I wonder if they would be available to views?

Which option is better? Am I missing an option?

Current views integration is extraordinary - incredibly powerful. Thanks a million to those that built and debugged it.

[xavier]

Hi,

What's the goal of having the price in the view ? Is that to display something specific that you can't from civicrm directly ?

X+

[geilhufe]

The goal is to display to the user a formated list of events that they can register for -- since the other lists of content for the site are already delivered and themed through views, it does not make sense to have to build an entirely new interface for seminars (though that is one alternative approach). CiviCRM's default event lists do not show the event price, so you'd have to hack all that display. Correct?

The views also have human-edited featured content (nodequeue) so it would be nice to continue to have that functionality.

One use case for displaying the price is that events have different prices, so the user should be able to see all the seminars that deal with email, and sign up for the least expensive one if they are budget conscious.

Option #3:
Use FeedAPI to take in the event feed (each new upcoming event becomes a node). Manually add a CCK calculated field to that, and then populate it with the event price via direct SQL query of the DB -- though you have to do some text processing to extract the event ID since it isn't it's own field.
This is starting to look like the best option at the moment-- no duplicate data entry. FeedAPI handles the sync. Events become full fledged Drupal nodes. Downside: change the data on the CiviCRM side (event title), not sure FeedAPI will sync the change.

Note on Option #2: The data would be available to views only if stored in the drupal DB. That means that data is only current as of the last node save.

Note on Option #3: You can't get the price from the API, you have to do it straight from SQL.
http://forum.civicrm.org/index.php/topic,10040.msg43087.html

[geilhufe]

Note on feeds. CiviCRM event RSS feeds do not include the date of the event so the whole FeedAPI sync hack is not workable.

Based on all the various issues related trying to sync events in Drupal and Civi, I think the best option is just going to be having the user manually sync the Drupal event nodes and the CiviCRM events.

1. The user creates a CiviCRM event.
2. We provide a view that shows the CiviCRM events and the Drupal events on a single HTML page... the user can then deduce which CiviCRM events are not yet in Drupal.
3. User creates a new Drupal node, copying and pasting the values from the CiviCRM event (Title, date, price, description)

Any suggestions are appreciated.

[Donald Lobo]

Note on feeds. CiviCRM event RSS feeds do not include the date of the event so the whole FeedAPI sync hack is not workable.

This can be easily fixed if we figure out what element to place it in and a convention. Wanna check the RSS spec and see what makes sense?

http://cyber.law.harvard.edu/rss/rss.html

maybe category? with a domain of startDate and endDate? the fix is simple enough that we can include in 3.0.x (and u can backport to 2.2.x)

lobo

[geilhufe]

I will work up a spec.

The FeedAPI + mapper sync solution is a potentially really easy solution for the problem of syncing Drupal and Civi content. I'm still exploring the dynamics but it seems to update correctly (i.e. I change the event name in Civi, the drupal node value changes), nodes are automatically created after they are created in Civi, it is easy to add CCK fields to Civi entities (like events).

If you dial up the update frequency, you can almost achieve real time sync, but I suspect the performance penalty is pretty high.

[geilhufe]

(1) Super long post.
(2) Following the PFIF model & a google example:
http://zesty.ca/pfif/1.1/
http://zesty.ca/pfif/1.1/pfif-1.1-example.html
http://base.google.com/support/bin/answer.py?hl=en&answer=58085
(3) I do not know how the additional elements will display in the RSS, so we should watch out we don't mess with the base use case.

The current RSS feed includes the following information (as formatted by the feedapi model & obfuscated):

Code: [Select]

All feed items of this feed merged into one. Here you can see which feed item elements are available for mapping. As this view is derived from the actual feed items on this feed, there might be more mappable elements than those listed here.
Array
(
    [title] => Event Title
    [description] => Description
    [options] => Array
        (
            [original_url] => http://XXX/civicrm/event/info?reset=1&id=3
            [timestamp] => 1254201473
            [guid] => CiviCRM_EventID_3_f29f10e5d55e1abe97f51de9173fcf34@iw.XXX.com
            [original_author] =>
            [domains] =>
            [tags] => Array
                (
                    [0] => Online Seminar
                )

            [raw] => Array
                (
                    [title] => Title
                    [link] => /civicrm/event/info?reset=1&id=3
                    [description] =>
There are a number of solid and affordable options to track your volunteers, donors, partners, and other constituents. What should a small organization look for? What tools are available? How ...
                    [category] => Online Seminar
                    [guid] => CiviCRM_EventID_3_f29f10e5d55e1abe97f51de9173fcf34@iw.XXX.com
                )
        )
)

The raw RSS is:

Code: [Select]

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
<channel>
<title>CiviEvent Public Calendar</title>
<link>http://iw.XXX.com/</link>
<description>Listing of current and upcoming public events.</description>
<language>en-us</language>
<generator>CiviCRM</generator>
<docs>http://blogs.law.harvard.edu/tech/rss</docs>

<item>
<title>Choosing a Low-Cost Constituent Database</title>

<link>/civicrm/event/info?reset=1&amp;id=3</link>
<description>
There are a number of solid and affordable options to track your volunteers, donors, partners, and other constituents. What should a small organization look for? What tools are available? How should you choose? We&#039;ll walk through everything you need to know to pick the right database for your organization, and consider the pros and cons of commonly used databases such as GiftWorks, eTapestry, DonorPerfect Online, Salesforce, and Democracy In Action.
&lt;p&gt;&amp;nbsp;There are a number of solid and affordable options to track your volunteers, donors, partners, and other constituents. What should a small organization look for? What tools are available? How should you choose? We&#039;ll walk through everything you need to know to pick the right database for your organization, and consider the pros and cons of commonly used databases such as GiftWorks, eTapestry, DonorPerfect Online, Salesforce, and Democracy In Action.&lt;/p&gt;
&lt;p style=&quot;font-family: Arial, Helvetica, sans-serif; font-size: 12px; margin-top: 0px; margin-right: 0px; margin-bottom: 15px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; &quot;&gt;Please register with an email address that will allow you to recieve the access and dial-in information for the online seminar.&lt;/p&gt;
&lt;hr /&gt;

&lt;p style=&quot;font-family: Arial, Helvetica, sans-serif; font-size: 12px; margin-top: 0px; margin-right: 0px; margin-bottom: 15px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; &quot;&gt;&lt;span style=&quot;font-size: larger; &quot;&gt;&lt;span style=&quot;font-family: &#039;Comic Sans MS&#039;; &quot;&gt;&lt;span style=&quot;color: rgb(153, 51, 102); &quot;&gt;&lt;a href=&quot;/&quot;&gt;PURCHASE THE EVENT RECORDING&lt;/a&gt;&lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;

&lt;hr /&gt;
&lt;p style=&quot;font-family: Arial, Helvetica, sans-serif; font-size: 12px; margin-top: 0px; margin-right: 0px; margin-bottom: 15px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; &quot;&gt;&amp;nbsp;&lt;/p&gt;
&lt;p style=&quot;font-family: Arial, Helvetica, sans-serif; font-size: 12px; margin-top: 0px; margin-right: 0px; margin-bottom: 15px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; &quot;&gt;
When: December 16th, 2009  1:00 PM through 02:30 PM
    </description>

<category>Online Seminar</category>
<guid isPermaLink="false">CiviCRM_EventID_3_f29f10e5d55e1abe97f51de9173fcf34@iw.XXX.com</guid>
</item>
</channel>
</rss>

The data elements that would be ideal (IMHO) for an event are (items in the existing feed are marked with an "x"):

Event ID
X Title
Start date/time
End date/time
X Event Type
Event Summary
X Event Description
X Public (assumed true or event wouldn't be in the feed)
X Active (assumed true or event wouldn't be in the feed)
X Link to view page
Link to registration page
Paid event (T/F)
Default event fee


If we take the PFIF spec (http://zesty.ca/pfif/1.1/  &  http://zesty.ca/pfif/1.1/pfif-1.1-example.html) as a model and assume that we can add any elements we want (A RSS feed may contain elements not described on this page, only if those elements are defined in a namespace.)

No changes to the channel element.

Additions to the item element:
<civicrm_event:ID>
<civicrm_event:startdate> (RFC 822 date format)
<civicrm_event:enddate> (RFC 822 date format)
<civicrm_event:summary>
<civicrm_event:registrationlink>
<civicrm_event:paidevent> (True or False)
<civicrm_event:defaulteventfee>

Just to make things easier so people don't have to deal with CCK date fields:
<civicrm_event:when> (same format as the display page-- December 16th, 2009  1:00 PM through 02:30 PM)

And better yet, make all the element names conform to the database so it is less confusing.

According to this example, you should just be able to throw the additional fields above into the Item element, define the civicrm_event: namespace and you are good to go.

Code: [Select]

<?xml version="1.0"?>
<rss version="2.0"
xmlns:g="http://base.google.com/ns/1.0">
<channel>
<title>Google Jobs</title>
<link>http://www.google.com/support/jobs/</link>
<description>Information about job openings at Google Inc.</description>
<item>
<title>HR Analyst - Mountain View</title>
<link> http://www.google.com/support/jobs/bin/topic.py?dep_id=1077&amp;loc_id=1116</link>
<description>We have an immediate need for an experienced analytical HR professional.
The ideal candidate has a proven record of developing analytical frameworks to make
fact-based decisions.</description>
<g:image_link>http://www.google.com/images/google_sm.gif</g:image_link>
<g:expiration_date>2005-11-15</g:expiration_date>
<g:job_function>Analyst</g:job_function>
<g:location>1600 Amphitheatre Parkway, Mountain View, CA, 94043, USA</g:location>
</item>
</channel>
</rss>

[xavier]

Hi,

What I've done in similar case is creating a drupal module with the menu containing your view that assign the variables (none in your case), and call smarty. Within the template call crmAPI to get the list of events.

In your case, you also need to add a api/v2 method with the sql.

Sounds scarry ? It shoudn't take more than half a day...

[geilhufe]

Scary? I would rather throw myself on the kindness of either friends or strangers 

I think I have more than enough info at this point to get this done. And if Lobo is kind enough to put the event default price in the RSS feed, then it will be a 5 min job. If he's not feeling that kind, it still shouldn't be that big a deal.

X+

What do you think of the FeedAPI solution? Is it a viable method of maintaining sync between CiviCRM entities and Drupal nodes? (aside from being a very resource intensive path)

[geilhufe]

Lobo-
If you do add the stuff for RSS, I'm happy to look up the correct field names so that the rss elements clearly map to the database if that will save you all some time.

[xavier]

As for the feedapi, looks a bit like a hack to me, but should work.

I don't know anything about the drupal node apis, but isn't it already somewhere some kind of code to sync node and event/activity/contact ?

Eg. if there is a function that:
- test if a node exists already with the "external"/CCK {civicrmentity/id}
- does create the node if is doesn't and add he external/CCK

and that you can put in the civicrm hook on create/update ?

X+

[Donald Lobo]

the scope has gotten a bit more than it started off with (no dates in RSS feed)

Considering that we have all / most of the info in the iCal feed, we should figure out how to make it work with that. I'm a bit reluctant to go in and make significant changes to the RSS feed.

I think at some point, doing it the way xavier has proposed makes more sense (IMO). We have some sample code which syncs activities to CCK nodes (done as proof of concept)

lobo

[geilhufe]

Never know unless you ask.

Upon further research FeedAPI has an ICAL parser. So I should be able to solve my problem that way.

[ChrisChinchilla]

Hello…

I'm trying to get an event price (or set of prices) to appear in Drupal Views, I notice there's a handler for it 'civicrm_handler_field_event_price_set.inc', unyet, no field is available.

Any thoughts?

[happysnowmantech]

@ChrisChinchilla: I have a similar requirement for showing price sets in Views.  I noticed the civicrm_handler_field_event_price_set.inc handler file too, but it is outdated and not fully hooked into the Views API, which is why you're not seeing the field.  I submitted a patch to do the Views integration here:

CRM-15398: Enable Drupal Views integration for Price Sets
https://issues.civicrm.org/jira/browse/CRM-15398