//3. consider event seat as a sum of all seats from line items in case price field value carries count.
$query = "
- SELECT IF ( SUM( value.count*lineItem.qty ),
- SUM( value.count*lineItem.qty ) +
- COUNT( DISTINCT participant.id ) -
- COUNT( DISTINCT IF ( value.count, participant.id, NULL ) ),
- COUNT( DISTINCT participant.id ) )
- FROM civicrm_participant participant
-INNER JOIN civicrm_contact contact ON ( contact.id = participant.contact_id AND contact.is_deleted = 0 )
-INNER JOIN civicrm_event event ON ( event.id = participant.event_id )
-LEFT JOIN civicrm_line_item lineItem ON ( lineItem.entity_id = participant.id
- AND lineItem.entity_table = 'civicrm_participant' )
-LEFT JOIN civicrm_price_field_value value ON ( value.id = lineItem.price_field_value_id AND value.count )
- WHERE ( participant.event_id = %1 )
- AND participant.is_test = 0
- {$extraWhereClause}
+ SELECT
+ IF
+ -- If the line item count * the line item quantity is not 0
+ (SUM(price_field_value.`count` * lineItem.qty),
+ -- then use the count * the quantity, ensuring each
+ -- actual participant record gets a result
+ SUM(price_field_value.`count` * lineItem.qty)
+ + COUNT(DISTINCT participant.id )
+ - COUNT(DISTINCT IF (price_field_value.`count`, participant.id, NULL)),
+ -- if the line item count is NULL or 0 then count the participants
+ COUNT(DISTINCT participant.id))
+ FROM civicrm_participant participant
+ INNER JOIN civicrm_contact contact ON (contact.id = participant.contact_id AND contact.is_deleted = 0)
+ INNER JOIN civicrm_event event ON ( event.id = participant.event_id )
+ LEFT JOIN civicrm_line_item lineItem ON ( lineItem.entity_id = participant.id AND lineItem.entity_table = 'civicrm_participant' )
+ LEFT JOIN civicrm_price_field_value price_field_value ON (price_field_value.id = lineItem.price_field_value_id AND price_field_value.`count`)
+ WHERE (participant.event_id = %1) AND participant.is_test = 0
+ {$extraWhereClause}
GROUP BY participant.event_id";
return (int) CRM_Core_DAO::singleValueQuery($query, [1 => [$eventId, 'Positive']]);
* All of the icons to show.
*/
public static function getICalLinks($eventId = NULL) {
- $return = $eventId ? [] : [
- [
- 'url' => CRM_Utils_System::url('civicrm/event/ical', 'reset=1&list=1&html=1', TRUE, NULL, TRUE),
- 'text' => ts('HTML listing of current and future public events.'),
- 'icon' => 'fa-th-list',
- ],
- [
- 'url' => CRM_Utils_System::url('civicrm/event/ical', 'reset=1&list=1&rss=1', TRUE, NULL, TRUE),
- 'text' => ts('Get RSS 2.0 feed for current and future public events.'),
- 'icon' => 'fa-rss',
- ],
- ];
+ $return = [];
$query = [
'reset' => 1,
];
'text' => $eventId ? ts('Download iCalendar entry for this event.') : ts('Download iCalendar entry for current and future public events.'),
'icon' => 'fa-download',
];
- $query['list'] = 1;
- $return[] = [
- 'url' => CRM_Utils_System::url('civicrm/event/ical', $query, TRUE, NULL, TRUE),
- 'text' => $eventId ? ts('iCalendar feed for this event.') : ts('iCalendar feed for current and future public events.'),
- 'icon' => 'fa-link',
- ];
+ if ($eventId) {
+ $return[] = [
+ 'url' => CRM_Utils_System::url('civicrm/event/ical', ['gCalendar' => 1] + $query, TRUE, NULL, TRUE),
+ 'text' => ts('Add event to Google Calendar'),
+ 'icon' => 'fa-share',
+ ];
+ }
+ else {
+ $return[] = [
+ 'url' => CRM_Utils_System::url('civicrm/event/ical', $query, TRUE, NULL, TRUE),
+ 'text' => ts('iCalendar feed for current and future public events'),
+ 'icon' => 'fa-link',
+ ];
+ }
return $return;
}