CiviCRM Community Forums (archive)

Welcome.
In case you don't get a more fulsome response to this - I would be looking down the Views route (Drupal module) but can't confirm the 'open' v 'full' or the colour being possible without at least some theming work.
Good luck

This can certainly be done with Views and I have done similar tasks, but it will require a bit of custom theming in PHP. Specifically, the Views template(s) will need to be customized to mark 'open' & 'full' events.

I can not estimate your coding time based on "a little bit of Perl about 10 years ago." But I can say that the Views module itself is quite complicated and takes time to learn. Once you are in it, there is a section for Theming. There it provides you with the default theme templates and also explains what variables are available in that template.

You would need to first create your basic View, then look through those template files to find the relevant one (correct level of specificity basically), then use print_r() to examine the variables to find the particular datum you need, and then use that datum to add a class to some HTML element, which can then be styled via CSS.

This process could easily take a few days if you are new to CiviCRM, Views and PHP, without question.

and in the grand perl tradition of there is more than one way, I'd pull the smarty template

{crmAPI var="results" entity="events" action="get" param1="ddd"}
{foreach results ...}
<some html magic>{$event.somedata}
{/foreach}

The civi_view module allows you to create a wunderschon.tpl and access it through civiview/wunderschon

X+

In case you don't get a more fulsome response to this - I would be looking down the Views route (Drupal module) but can't confirm the 'open' v 'full' or the colour being possible without at least some theming work.
Good luck

OK, from what i can tell CiviCRM uses multiple participant status types (civicrm_participant.status_id) to determine if the participant is to be counted towards the civicrm_event.max_participant value.  I wanted to use Views to calculate this and then alter the date link in year view, and/or change the title displayed in the month, week, day view. 

I couldn't see an obvious way to do this in Views, although you would think it was possible.  So, what i ended up doing was creating a trigger in the civicrm database on the civicrm_participant table that would check the count of specific civicrm_participant.status_id values for all entries in the table with the civicrm_participant.event_id = to the NEW.event_id of the inserted record and then compare that to the civicrm_event.max_participants of the event with the id = NEW.event_id.  if the count is >= max_participants then it would add "[FULL]" to the title text.  of course this does not address any updated/deleted records but triggers could be added for each of those.

Now, i am not a big fan of messing with the database, and you have to be running MySQL 5.1.6 or above to be able to use triggers,  but since i am more familiar with SQL than i am with Drupal/Views i decided to go this route at least temporarily, besides once tested it actually seemed like the easiest and cleanest way to get it done.

below is the trigger and 2 separate SQL statements i used for testing - they are formatted to paste into phpmyadmin, you will need to reformat if you use the mysql cli
... if someone could offer a better way within Views (or with SQL) i would certainly appreciate your feedback.


# ==================================
# create Trigger to update records as users register and event becomes full

CREATE TRIGGER CheckFull AFTER INSERT ON civicrm_participant
FOR EACH ROW
UPDATE civicrm_event SET civicrm_event.title = CONCAT_WS('',`civicrm_event`.`title`, " [FULL]")
WHERE civicrm_event.id = NEW.event_id
AND (SELECt COUNT(*)
FROM civicrm_participant
WHERE civicrm_participant.event_id = NEW.event_id
AND (civicrm_participant.status_id = '1'
OR civicrm_participant.status_id = '2'
OR civicrm_participant.status_id = '5'
OR civicrm_participant.status_id = '8'
OR civicrm_participant.status_id = '9'
OR civicrm_participant.status_id = '10'
)) >= civicrm_event.max_participants

# ====================================
# Run this to verify you will get the correct records
# remove the - WHERE title NOT LIKE '%[FULL]' - to get all records

SELECT a.id, a.title, a.max, b.ev_id, b.eCnt
FROM (SELECT id, title, max_participants as max FROM civicrm_event WHERE title NOT LIKE '%[FULL]') as a, (
SELECT event_id as ev_id, COUNT(*) AS eCnt
FROM civicrm_participant
WHERE status_id = '1'
OR status_id = '2'
OR status_id = '5'
OR status_id = '8'
OR status_id = '9'
OR status_id = '10'
GROUP BY event_id
) as b
WHERE a.id = b.ev_id AND b.eCnt >= a.max

# =================================
# Run to 'manually' update all current records that are full

UPDATE civicrm_event, civicrm_participant SET civicrm_event.title = CONCAT_WS('',`civicrm_event`.`title`, " [FULL]")
WHERE civicrm_event.title NOT LIKE '%[FULL]'
AND (
SELECT COUNT(*) AS eCnt
FROM civicrm_participant
WHERE civicrm_participant.event_id = civicrm_event.id
AND ( status_id = '1'
OR status_id = '2'
OR status_id = '5'
OR status_id = '8'
OR status_id = '9'
OR status_id = '10')
) >= civicrm_event.max_participants