1

Running civiCRM 5.14.1 ... and Joomla 3.9.8

When I receive my scheduled emailed Contribution report, the email contains the SQL used to generate the report... How do I avoid this?

The report is the stock Contribution Details one.

This is what I get in the report:

    FROM civicrm_tmp_e_dflt_0e14e8f146b15ff80cc20f028e1f66b2);
    SELECT GROUP_CONCAT(DISTINCT contact_civireport.sort_name) as civicrm_contact_sort_name, GROUP_CONCAT(DISTINCT contact_civireport.id) as civicrm_contact_id, GROUP_CONCAT(DISTINCT email_civireport.email) as civicrm_email_email,
GROUP_CONCAT(DISTINCT phone_civireport.phone) as civicrm_phone_phone, (contribution_civireport.id) as civicrm_contribution_contribution_id, GROUP_CONCAT(DISTINCT contribution_civireport.financial_type_id) as civicrm_contribution_financial_type_id, GROUP_CONCAT(DISTINCT contribution_civireport.currency) as civicrm_contribution_currency, GROUP_CONCAT(DISTINCT contribution_civireport.receive_date) as civicrm_contribution_receive_date, GROUP_CONCAT(DISTINCT contribution_civireport.total_amount) as civicrm_contribution_total_amount, GROUP_CONCAT(DISTINCT 'Contribution') as civicrm_contribution_contribution_or_soft, GROUP_CONCAT(DISTINCT address_civireport.country_id) as civicrm_address_country_id   

FROM civicrm_contact
contact_civireport   
      INNER JOIN civicrm_contribution contribution_civireport
        ON contact_civireport.id = contribution_civireport.contact_id
        AND contribution_civireport.is_test = 0

  LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1


  LEFT JOIN civicrm_address address_civireport
                           ON (contact_civireport.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1


  LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1)  

WHERE ( contact_civireport.is_deleted = 0 ) AND (1) AND ( contribution_civireport.contribution_status_id IN (1) )  

GROUP BY contribution_civireport.id  

ORDER BY civicrm_contact_sort_name ASC ;



CREATE TEMPORARY TABLE `civicrm_tmp_e_dflt_0e14e8f146b15ff80cc20f028e1f66b2` ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT GROUP_CONCAT(DISTINCT contact_civireport.sort_name) as civicrm_contact_sort_name, GROUP_CONCAT(DISTINCT contact_civireport.id) as civicrm_contact_id, GROUP_CONCAT(DISTINCT email_civireport.email) as civicrm_email_email, GROUP_CONCAT(DISTINCT phone_civireport.phone) as civicrm_phone_phone,
(contribution_civireport.id) as civicrm_contribution_contribution_id, GROUP_CONCAT(DISTINCT contribution_civireport.financial_type_id) as civicrm_contribution_financial_type_id, GROUP_CONCAT(DISTINCT contribution_civireport.currency) as civicrm_contribution_currency, GROUP_CONCAT(DISTINCT contribution_civireport.receive_date) as civicrm_contribution_receive_date, GROUP_CONCAT(DISTINCT contribution_civireport.total_amount) as civicrm_contribution_total_amount, GROUP_CONCAT(DISTINCT 'Contribution') as civicrm_contribution_contribution_or_soft, GROUP_CONCAT(DISTINCT address_civireport.country_id) as civicrm_address_country_id   

FROM civicrm_contact contact_civireport   
      INNER JOIN civicrm_contribution contribution_civireport
        ON contact_civireport.id = contribution_civireport.contact_id
        AND contribution_civireport.is_test = 0

  LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1


  LEFT JOIN civicrm_address address_civireport
                           ON (contact_civireport.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1


  LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1)  

WHERE ( contact_civireport.is_deleted = 0 ) AND (1) AND ( contribution_civireport.contribution_status_id IN (1) )  

GROUP BY contribution_civireport.id  

ORDER BY civicrm_contact_sort_name ASC ;



CREATE TEMPORARY TABLE `civicrm_tmp_e_dflt_39b3cf36a448ecf18b624c69b96f53b0` ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT GROUP_CONCAT(DISTINCT contact_civireport.sort_name) as civicrm_contact_sort_name, GROUP_CONCAT(DISTINCT contact_civireport.id) as civicrm_contact_id, GROUP_CONCAT(DISTINCT email_civireport.email) as civicrm_email_email, GROUP_CONCAT(DISTINCT phone_civireport.phone) as civicrm_phone_phone,
GROUP_CONCAT(DISTINCT contribution_civireport.id) as civicrm_contribution_contribution_id, GROUP_CONCAT(DISTINCT contribution_civireport.financial_type_id) as civicrm_contribution_financial_type_id, GROUP_CONCAT(DISTINCT contribution_civireport.currency) as civicrm_contribution_currency, GROUP_CONCAT(DISTINCT contribution_civireport.receive_date) as civicrm_contribution_receive_date, GROUP_CONCAT(DISTINCT contribution_soft_civireport.amount) as civicrm_contribution_total_amount, GROUP_CONCAT(DISTINCT 'Contribution') as civicrm_contribution_contribution_or_soft, GROUP_CONCAT(DISTINCT address_civireport.country_id) as civicrm_address_country_id  


FROM  civicrm_tmp_e_dflt_0e14e8f146b15ff80cc20f028e1f66b2 temp1_civireport
      INNER JOIN civicrm_contribution contribution_civireport
        ON temp1_civireport.civicrm_contribution_contribution_id = contribution_civireport.id
      INNER JOIN civicrm_contribution_soft contribution_soft_civireport
        ON contribution_soft_civireport.contribution_id = contribution_civireport.id
      INNER JOIN civicrm_contact      contact_civireport
        ON contact_civireport.id = contribution_soft_civireport.contact_id



  LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1


  LEFT JOIN civicrm_address address_civireport
                           ON (contact_civireport.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1


  LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1)  

WHERE ( contact_civireport.is_deleted = 0 ) AND (1) AND ( contribution_civireport.contribution_status_id IN (1) )  

GROUP BY contribution_civireport.id;



CREATE TEMPORARY TABLE `civicrm_tmp_e_dflt_7eac86d772ca2f9a3668c3d1ad376967` ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS (SELECT * 

FROM civicrm_tmp_e_dflt_0e14e8f146b15ff80cc20f028e1f66b2);




        SELECT COUNT(contribution_civireport.total_amount ) as count,
               SUM( contribution_civireport.total_amount ) as amount,
               ROUND(AVG(contribution_civireport.total_amount), 2) as avg,
               contribution_civireport.currency as currency,
               SUM( contribution_civireport.fee_amount ) as fees,
               SUM( contribution_civireport.net_amount ) as net


FROM civicrm_contact contact_civireport   
      INNER JOIN civicrm_contribution contribution_civireport
        ON contact_civireport.id = contribution_civireport.contact_id
        AND contribution_civireport.is_test = 0

  LEFT JOIN civicrm_phone phone_civireport
             ON contact_civireport.id = phone_civireport.contact_id AND
                phone_civireport.is_primary = 1


  LEFT JOIN civicrm_address address_civireport
                           ON (contact_civireport.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1


  LEFT JOIN  civicrm_email email_civireport
                   ON (contact_civireport.id = email_civireport.contact_id AND
                       email_civireport.is_primary = 1)  

WHERE ( contact_civireport.is_deleted = 0 ) AND (1) AND ( contribution_civireport.contribution_status_id IN (1) ) 


GROUP BY contribution_civireport.currency

function showHideMaxMinVal( field, val ) { var fldVal = field + "_value_cell"; var fldMinMax = field + "_min_max_cell"; if ( val == "bw" || val == "nbw" ) { cj('#' + fldVal ).hide(); cj('#' + fldMinMax ).show(); } else if (val =="nll" || val == "nnll") { cj('#' + fldVal).hide() ; cj('#' + field + '_value').val(''); cj('#' + fldMinMax ).hide(); } else { cj('#' + fldVal ).show(); cj('#' + fldMinMax ).hide(); } } CRM.$(function($) { $('.crm-report-criteria-groupby input:checkbox').click(function() { $('#fields_' + this.id.substr(10)).prop('checked', this.checked); }); });
CRM.$(function($) { var tabSettings = { collapsible: true, active: false }; // If a tab contains an error, open it if ($('.civireport-criteria .crm-error', '#mainTabContainer').length) { tabSettings.active = $('.civireport-criteria').index($('.civireport-criteria:has(".crm-error")')[0]); } $("#mainTabContainer").tabs(tabSettings); });


CRM.$(function($) { // Disable print/pdf output of charts $('select[name=charts]', 'form.crm-report-form').change(function() { var viewType = $(this).val(), flashChartType = ''; $('#_qf_Summary_submit_pdf, #_qf_Summary_submit_print').prop('disabled', (viewType && flashChartType != viewType)); }); });
Grouping(s)     Contribution
Is deleted  Is equal to No
Contribution OR Soft Credit?    Is equal to Contributions Only
Contribution Status     Is Completed
Donor Name  Donor Email     Donor Phone     Financial Type  Date Received   Amount  Country
B, A    alex.block@example.com  613-###-####    Donation    2019-06-19 13:48:06     $ 20.00     Canada

Row(s) Listed   1
Total Amount (Contributions)    $ 20.00 (1)
Total Contributions     1
Fees    $ 0.00
Net     $ 20.00
Average     $ 20.00 
  • 1
    Can you provide the some more details? Like which contribution report and the sql you get in the email csv file. – Pradeep Nayak Jun 25 at 17:18
  • I updated my post with what is contained in the email. – A. Block Jun 26 at 15:05
  • Also can you add the schedule job parameters set or parameters used in cron job to send email? – Pradeep Nayak Jun 26 at 15:54
  • instanceId=43 format=html – A. Block Jun 27 at 13:57
  • Just wondering if anyone has any insight into why this would happen? – A. Block Jul 23 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.