From 30d6c827a2567114a82320d3900d90afd0e14cd0 Mon Sep 17 00:00:00 2001 From: Eileen McNaughton Date: Fri, 20 May 2022 11:07:30 +1200 Subject: [PATCH] Cleanup sql query On digging into this query I found that is uses protected words like value & count so I have fixed the query to no longer use those & re-formatted to add comments. I suspect the actual fix might need to sit around pending community input so I think it is good to get this tidy up merged to make it all clearer --- CRM/Event/BAO/Event.php | 32 ++++++++++++++++++-------------- 1 file changed, 18 insertions(+), 14 deletions(-) diff --git a/CRM/Event/BAO/Event.php b/CRM/Event/BAO/Event.php index 559e651d67..b25413edba 100644 --- a/CRM/Event/BAO/Event.php +++ b/CRM/Event/BAO/Event.php @@ -2258,20 +2258,24 @@ WHERE ce.loc_block_id = $locBlockId"; //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']]); -- 2.25.1