3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Report_Form_Contribute_Lybunt
extends CRM_Report_Form
{
20 * This is the report that links will lead to.
22 * It is a bit problematic to use contribute/detail for anything other than a single contact
23 * as the filtering from this report does not carry over to that report.
27 public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report'];
29 protected $lifeTime_from = NULL;
30 protected $lifeTime_where = NULL;
31 protected $_customGroupExtends = [
40 * Table containing list of contact IDs.
44 protected $contactTempTable = '';
47 * This report has been optimised for group filtering.
50 * @see https://issues.civicrm.org/jira/browse/CRM-19170
52 protected $groupFilterNotOptimised = FALSE;
57 public function __construct() {
58 $this->optimisedForOnlyFullGroupBy
= FALSE;
59 $this->_rollup
= 'WITH ROLLUP';
60 $this->_autoIncludeIndexedFieldsAsOrderBys
= 1;
63 $date = CRM_Core_SelectValues
::date('custom', NULL, $yearsInPast, $yearsInFuture);
64 $count = $date['maxYear'];
65 while ($date['minYear'] <= $count) {
66 $optionYear[$date['minYear']] = $date['minYear'];
71 'civicrm_contact' => [
72 'dao' => 'CRM_Contact_DAO_Contact',
73 'grouping' => 'contact-field',
74 'fields' => $this->getBasicContactFields(),
77 'title' => ts('Last Name, First Name'),
79 'default_order' => 'ASC',
82 'name' => 'first_name',
83 'title' => ts('First Name'),
86 'name' => 'gender_id',
87 'title' => ts('Gender'),
90 'name' => 'birth_date',
91 'title' => ts('Birth Date'),
94 'title' => ts('Contact Type'),
96 'contact_sub_type' => [
97 'title' => ts('Contact Subtype'),
102 'title' => ts('Donor Name'),
103 'operator' => 'like',
106 'title' => ts('Contact ID'),
107 'no_display' => TRUE,
110 'title' => ts('Gender'),
111 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
112 'options' => CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id'),
115 'title' => ts('Birth Date'),
116 'operatorType' => CRM_Report_Form
::OP_DATE
,
119 'title' => ts('Contact Type'),
121 'contact_sub_type' => [
122 'title' => ts('Contact Subtype'),
125 'do_not_phone' => [],
126 'do_not_email' => [],
132 'civicrm_line_item' => [
133 'dao' => 'CRM_Price_DAO_LineItem',
136 'dao' => 'CRM_Core_DAO_Email',
137 'grouping' => 'contact-field',
140 'title' => ts('Email'),
144 'title' => ts('Email on hold'),
149 'title' => ts('On Hold'),
150 'type' => CRM_Utils_Type
::T_INT
,
151 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
152 'options' => ['' => ts('Any')] + CRM_Core_PseudoConstant
::emailOnHoldOptions(),
157 'dao' => 'CRM_Core_DAO_Phone',
158 'grouping' => 'contact-field',
161 'title' => ts('Phone'),
167 $this->_columns +
= $this->addAddressFields(FALSE);
169 'civicrm_contribution' => [
170 'dao' => 'CRM_Contribute_DAO_Contribution',
173 'title' => ts('contactId'),
174 'no_display' => TRUE,
179 'title' => ts('Year'),
180 'no_display' => TRUE,
184 'last_year_total_amount' => [
185 'title' => ts('Last Year Total'),
187 'type' => CRM_Utils_Type
::T_MONEY
,
190 'civicrm_life_time_total' => [
191 'title' => ts('Lifetime Total'),
193 'type' => CRM_Utils_Type
::T_MONEY
,
194 'statistics' => ['sum' => ts('Lifetime total')],
200 'name' => 'receive_date',
201 'title' => ts('This Year'),
202 'operatorType' => CRM_Report_Form
::OP_SELECT
,
203 'options' => $optionYear,
204 'default' => date('Y'),
205 'type' => CRM_Utils_Type
::T_INT
,
207 'financial_type_id' => [
208 'title' => ts('Financial Type'),
209 'type' => CRM_Utils_Type
::T_INT
,
210 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
211 'options' => CRM_Contribute_BAO_Contribution
::buildOptions('financial_type_id', 'search'),
213 'contribution_status_id' => [
214 'title' => ts('Contribution Status'),
215 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
216 'options' => CRM_Contribute_BAO_Contribution
::buildOptions('contribution_status_id', 'search'),
221 'last_year_total_amount' => [
222 'title' => ts('Total amount last year'),
224 'default_weight' => '0',
225 'default_order' => 'DESC',
231 'civicrm_financial_trxn' => [
232 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
235 'title' => ts('Credit Card Type'),
236 'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
241 'title' => ts('Credit Card Type'),
242 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
243 'options' => CRM_Financial_DAO_FinancialTrxn
::buildOptions('card_type_id'),
245 'type' => CRM_Utils_Type
::T_STRING
,
251 // If we have a campaign, build out the relevant elements
252 $this->addCampaignFields('civicrm_contribution');
254 // Add charts support
257 'barChart' => ts('Bar Chart'),
258 'pieChart' => ts('Pie Chart'),
261 $this->_groupFilter
= TRUE;
262 $this->_tagFilter
= TRUE;
263 parent
::__construct();
267 * Build select clause for a single field.
269 * @param string $tableName
270 * @param string $tableKey
271 * @param string $fieldName
272 * @param string $field
276 public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
277 if ($fieldName == 'last_year_total_amount') {
278 $this->_columnHeaders
["{$tableName}_{$fieldName}"] = $field;
279 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $this->getLastYearColumnTitle();
280 $this->_statFields
[$this->getLastYearColumnTitle()] = "{$tableName}_{$fieldName}";
281 return "SUM(IF(" . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as {$tableName}_{$fieldName}";
283 if ($fieldName == 'civicrm_life_time_total') {
284 $this->_columnHeaders
["{$tableName}_{$fieldName}"] = $field;
285 $this->_statFields
[$field['title']] = "{$tableName}_{$fieldName}";
286 return "SUM({$this->_aliases[$tableName]}.total_amount) as {$tableName}_{$fieldName}";
288 if ($fieldName == 'receive_date') {
289 return self
::fiscalYearOffset($field['dbAlias']) .
290 " as {$tableName}_{$fieldName} ";
296 * Get the title for the last year column.
298 public function getLastYearColumnTitle() {
299 if ($this->getYearFilterType() == 'calendar') {
300 return ts('Total for ') . ($this->getCurrentYear() - 1);
302 return ts('Total for Fiscal Year ') . ($this->getCurrentYear() - 1) . '-' . ($this->getCurrentYear());
306 * Construct from clause.
308 * On the first run we are creating a table of contacts to include in the report.
310 * Once contactTempTable is populated we should avoid using any further filters that affect
311 * the contacts that should be visible.
313 public function from() {
314 if (!empty($this->contactTempTable
)) {
316 FROM civicrm_contribution {$this->_aliases['civicrm_contribution']}
317 INNER JOIN $this->contactTempTable restricted_contacts
318 ON restricted_contacts.cid = {$this->_aliases['civicrm_contribution']}.contact_id
319 AND {$this->_aliases['civicrm_contribution']}.is_test = 0
320 AND {$this->_aliases['civicrm_contribution']}.is_template = 0
321 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
322 ON restricted_contacts.cid = {$this->_aliases['civicrm_contact']}.id";
324 $this->joinAddressFromContact();
325 $this->joinPhoneFromContact();
326 $this->joinEmailFromContact();
329 $this->setFromBase('civicrm_contact');
331 $this->_from
.= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ";
332 if (!$this->groupTempTable
) {
333 // The received_date index is better than the contribution_status_id index (fairly substantially).
334 // But if we have already pre-filtered down to a group of contacts then we want that to be the
335 // primary filter and the index hint will block that.
336 $this->_from
.= "USE index (received_date)";
338 $this->_from
.= " ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id
339 AND {$this->_aliases['civicrm_contribution']}.is_test = 0
340 AND {$this->_aliases['civicrm_contribution']}.is_template = 0
341 AND " . $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date") . "
343 $this->selectivelyAddLocationTablesJoinsToFilterQuery();
346 // for credit card type
347 $this->addFinancialTrxnFromClause();
351 * Generate where clause.
353 * We are overriding this primarily for 'before-after' handling of the receive_date placeholder field.
355 * We call this twice. The first time we are generating a temp table and we want to do an IS NULL on the
356 * join that draws in contributions from this year. The second time we are filtering elsewhere (contacts via
357 * the temp table & contributions via selective addition of contributions in the select function).
359 * If lifetime total is NOT selected we can add a further filter here to possibly improve performance
360 * but the benefit if unproven as yet.
361 * $clause = $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date");
363 * @param array $field Field specifications
364 * @param string $op Query operator (not an exact match to sql)
365 * @param mixed $value
369 * @return null|string
371 public function whereClause(&$field, $op, $value, $min, $max) {
372 if ($field['name'] == 'receive_date') {
374 if (empty($this->contactTempTable
)) {
375 $clause = "{$this->_aliases['civicrm_contact']}.id NOT IN (
376 SELECT cont_exclude.contact_id
377 FROM civicrm_contribution cont_exclude
378 WHERE " . $this->whereClauseThisYear('cont_exclude.receive_date')
382 // Group filtering is already done so skip.
383 elseif (!empty($field['group']) && $this->contactTempTable
) {
387 $clause = parent
::whereClause($field, $op, $value, $min, $max);
393 * Generate where clause for last calendar year or fiscal year.
395 * @todo must be possible to re-use relative dates stuff.
397 * @param string $fieldName
401 public function whereClauseLastYear($fieldName) {
402 return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND '" . $this->getLastDateOfPriorRange() . "'";
406 * Generate where clause for last calendar year or fiscal year.
408 * @todo must be possible to re-use relative dates stuff.
410 * @param string $fieldName
412 * @param int $current_year
413 * @return null|string
415 public function whereClauseThisYear($fieldName, $current_year = NULL) {
416 return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "' AND '" . $this->getLastDateOfCurrentRange() . "'";
420 * Get the year value for the current year.
424 public function getCurrentYear() {
425 return $this->_params
['yid_value'];
429 * Get the date time of the first date in the 'this year' range.
433 public function getFirstDateOfCurrentRange() {
434 $current_year = $this->getCurrentYear();
435 if ($this->getYearFilterType() == 'calendar') {
436 return "{$current_year }-01-01";
439 $fiscalYear = CRM_Core_Config
::singleton()->fiscalYearStart
;
440 return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
445 * Get the year value for the current year.
449 public function getYearFilterType() {
450 return CRM_Utils_Array
::value('yid_op', $this->_params
, 'calendar');
454 * Get the date time of the last date in the 'this year' range.
458 public function getLastDateOfCurrentRange() {
459 return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfCurrentRange())));
463 * Get the date time of the first date in the 'last year' range.
467 public function getFirstDateOfPriorRange() {
468 return date('YmdHis', strtotime('- 1 year', strtotime($this->getFirstDateOfCurrentRange())));
472 * Get the date time of the last date in the 'last year' range.
476 public function getLastDateOfPriorRange() {
477 return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfPriorRange())));
480 public function groupBy() {
481 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id ";
482 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($this->_selectClauses
, "{$this->_aliases['civicrm_contribution']}.contact_id");
483 $this->assign('chartSupported', TRUE);
491 public function statistics(&$rows) {
493 $statistics = parent
::statistics($rows);
494 // The parent class does something odd where it adds an extra row to the count for the grand total.
495 // Perhaps that works on some other report? But here it just seems odd.
496 $this->countStat($statistics, count($rows));
498 if (!empty($this->rollupRow
) && !empty($this->rollupRow
['civicrm_contribution_last_year_total_amount'])) {
499 $statistics['counts']['civicrm_contribution_last_year_total_amount'] = [
500 'value' => $this->rollupRow
['civicrm_contribution_last_year_total_amount'],
501 'title' => $this->getLastYearColumnTitle(),
502 'type' => CRM_Utils_Type
::T_MONEY
,
506 if (!empty($this->rollupRow
) && !empty($this->rollupRow
['civicrm_contribution_civicrm_life_time_total'])) {
507 $statistics['counts']['civicrm_contribution_civicrm_life_time_total'] = [
508 'value' => $this->rollupRow
['civicrm_contribution_civicrm_life_time_total'],
509 'title' => ts('Total LifeTime'),
510 'type' => CRM_Utils_Type
::T_MONEY
,
514 $select = "SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount,
515 SUM(IF( " . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as last_year
517 $sql = "{$select} {$this->_from} {$this->_where}";
518 $dao = CRM_Core_DAO
::executeQuery($sql);
520 $statistics['counts']['amount'] = [
521 'value' => $dao->amount
,
522 'title' => ts('Total LifeTime'),
523 'type' => CRM_Utils_Type
::T_MONEY
,
525 $statistics['counts']['last_year'] = [
526 'value' => $dao->last_year
,
527 'title' => $this->getLastYearColumnTitle(),
528 'type' => CRM_Utils_Type
::T_MONEY
,
538 * This function is called by both the api (tests) and the UI.
540 public function beginPostProcessCommon() {
542 // @todo this acl has no test coverage and is very hard to test manually so could be fragile.
543 $this->resetFormSqlAndWhereHavingClauses();
545 $this->contactTempTable
= $this->createTemporaryTable('rptlybunt', "
546 SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from}
548 GROUP BY {$this->_aliases['civicrm_contact']}.id"
551 if (empty($this->_params
['charts'])) {
555 // Reset where clauses to be regenerated in postProcess.
556 $this->_whereClauses
= [];
560 * Build the report query.
562 * The issue we are hitting is that if we want to do group by & then ORDER BY we have to
563 * wrap the query in an outer query with the order by - otherwise the group by takes precedent.
564 * This is an issue when we want to group by contact but order by the maximum aggregate donation.
566 * @param bool $applyLimit
570 public function buildQuery($applyLimit = TRUE) {
571 $this->buildGroupTempTable();
572 $this->buildPermissionClause();
573 // Calling where & select before FROM allows us to build temp tables to use in from.
577 $this->customDataFrom(empty($this->contactTempTable
));
583 // order_by columns not selected for display need to be included in SELECT
584 // This differs from parent in that we are getting those not in order by rather than not in
585 // sections, as we need to adapt to our contact group by.
586 $unselectedSectionColumns = array_diff_key($this->_orderByFields
, $this->getSelectColumns());
587 foreach ($unselectedSectionColumns as $alias => $section) {
588 $this->_select
.= ", {$section['dbAlias']} as {$alias}";
591 if ($applyLimit && empty($this->_params
['charts'])) {
595 $sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}";
597 if (!empty($this->_orderByArray
)) {
598 $this->_orderBy
= str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY ISNULL(civicrm_contribution_contact_id), " . implode(', ', $this->_orderByArray
));
599 $this->_orderBy
= str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy
);
600 foreach ($this->_orderByFields
as $field) {
601 $this->_orderBy
= str_replace($field['dbAlias'], $field['tplField'], $this->_orderBy
);
603 $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
604 $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM ( $sql ) as inner_query {$this->_orderBy} $this->_limit";
607 CRM_Utils_Hook
::alterReportVar('sql', $this, $this);
608 $this->addToDeveloperTab($sql);
614 * Reset the form sql and where / having clause arrays.
616 * We do an early iteration of the report queries to generate the temp table.
618 * However, that iteration populates the sql for the developer tab,
619 * the whereClauses & the havingClauses and they are populated again in the normal
620 * report flow. This is harmless but confusing - ie. the where clause winds up repeating
621 * the same filters and the dev tab shows the query twice, so we rest them.
623 protected function resetFormSqlAndWhereHavingClauses() {
625 $this->_havingClauses
= [];
626 $this->_whereClauses
= [];
627 $this->sqlArray
= [];
633 public function buildChart(&$rows) {
639 $current_year = $this->_params
['yid_value'];
640 $previous_year = $current_year - 1;
641 $interval[$previous_year] = $previous_year;
642 $interval['life_time'] = ts('Life Time');
644 foreach ($rows as $key => $row) {
645 // The final row contains the totals so we don't need to include it here.
646 if (!empty($row['civicrm_contribution_contact_id'])) {
647 $display['life_time'] = CRM_Utils_Array
::value('life_time', $display) +
648 $row['civicrm_contribution_civicrm_life_time_total'];
649 $display[$previous_year] = CRM_Utils_Array
::value($previous_year, $display) +
$row['civicrm_contribution_last_year_total_amount'];
653 $config = CRM_Core_Config
::Singleton();
654 $graphRows['value'] = $display;
656 'legend' => ts('Lybunt Report'),
657 'xname' => ts('Year'),
658 'yname' => ts('Amount (%1)', [1 => $config->defaultCurrency
]),
660 if ($this->_params
['charts']) {
662 CRM_Utils_Chart
::reportChart($graphRows, $this->_params
['charts'], $interval, $chartInfo);
663 $this->assign('chartType', $this->_params
['charts']);
668 * Alter display of rows.
670 * Iterate through the rows retrieved via SQL and make changes for display purposes,
671 * such as rendering contacts as links.
674 * Rows generated by SQL, with an array for each row.
676 public function alterDisplay(&$rows) {
679 foreach ($rows as $rowNum => $row) {
680 //Convert Display name into link
681 if (array_key_exists('civicrm_contact_sort_name', $row) &&
682 array_key_exists('civicrm_contribution_contact_id', $row)
684 $url = CRM_Report_Utils_Report
::getNextUrl('contribute/detail',
685 'reset=1&force=1&id_op=eq&id_value=' .
686 $row['civicrm_contribution_contact_id'],
687 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
689 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
690 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
694 // convert campaign_id to campaign title
695 if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
696 if ($value = $row['civicrm_contribution_campaign_id']) {
697 $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns
[$value];
701 // Display 'Yes' if the email is on hold (leave blank for no so it stands out better).
702 if (array_key_exists('civicrm_email_on_hold', $row)) {
703 $rows[$rowNum]['civicrm_email_on_hold'] = $row['civicrm_email_on_hold'] ?
ts('Yes') : '';
707 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ?
TRUE : $entryFound;
708 $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ?
TRUE : $entryFound;
710 if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
711 $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
715 // skip looking further in rows, if first row itself doesn't
716 // have the column we need
724 * Override "This Year" $op options
725 * @param string $type
726 * @param null $fieldName
730 public function getOperationPair($type = "string", $fieldName = NULL) {
731 if ($fieldName == 'yid') {
733 'calendar' => ts('Is Calendar Year'),
734 'fiscal' => ts('Fiscal Year Starting'),
737 return parent
::getOperationPair($type, $fieldName);