CRM/Report/Form/Contribute/Lybunt.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form {
  18
  19   protected $_charts = [
  20     '' => 'Tabular',
  21     'barChart' => 'Bar Chart',
  22     'pieChart' => 'Pie Chart',
  23   ];
  24
  25   /**
  26    * This is the report that links will lead to.
  27    *
  28    * It is a bit problematic to use contribute/detail for anything other than a single contact
  29    * as the filtering from this report does not carry over to that report.
  30    *
  31    * @var array
  32    */
  33   public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report'];
  34
  35   protected $lifeTime_from = NULL;
  36   protected $lifeTime_where = NULL;
  37   protected $_customGroupExtends = [
  38     'Contact',
  39     'Individual',
  40     'Household',
  41     'Organization',
  42   ];
  43
  44   /**
  45    * Table containing list of contact IDs.
  46    *
  47    * @var string
  48    */
  49   protected $contactTempTable = '';
  50
  51   /**
  52    * This report has been optimised for group filtering.
  53    *
  54    * CRM-19170
  55    *
  56    * @var bool
  57    */
  58   protected $groupFilterNotOptimised = FALSE;
  59
  60   /**
  61    * Class constructor.
  62    */
  63   public function __construct() {
  64     $this->optimisedForOnlyFullGroupBy = FALSE;
  65     $this->_rollup = 'WITH ROLLUP';
  66     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  67     $yearsInPast = 10;
  68     $yearsInFuture = 1;
  69     $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, $yearsInFuture);
  70     $count = $date['maxYear'];
  71     while ($date['minYear'] <= $count) {
  72       $optionYear[$date['minYear']] = $date['minYear'];
  73       $date['minYear']++;
  74     }
  75
  76     $this->_columns = [
  77       'civicrm_contact' => [
  78         'dao' => 'CRM_Contact_DAO_Contact',
  79         'grouping' => 'contact-field',
  80         'fields' => $this->getBasicContactFields(),
  81         'order_bys' => [
  82           'sort_name' => [
  83             'title' => ts('Last Name, First Name'),
  84             'default' => '0',
  85             'default_order' => 'ASC',
  86           ],
  87           'first_name' => [
  88             'name' => 'first_name',
  89             'title' => ts('First Name'),
  90           ],
  91           'gender_id' => [
  92             'name' => 'gender_id',
  93             'title' => ts('Gender'),
  94           ],
  95           'birth_date' => [
  96             'name' => 'birth_date',
  97             'title' => ts('Birth Date'),
  98           ],
  99           'contact_type' => [
 100             'title' => ts('Contact Type'),
 101           ],
 102           'contact_sub_type' => [
 103             'title' => ts('Contact Subtype'),
 104           ],
 105         ],
 106         'filters' => [
 107           'sort_name' => [
 108             'title' => ts('Donor Name'),
 109             'operator' => 'like',
 110           ],
 111           'id' => [
 112             'title' => ts('Contact ID'),
 113             'no_display' => TRUE,
 114           ],
 115           'gender_id' => [
 116             'title' => ts('Gender'),
 117             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 118             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 119           ],
 120           'birth_date' => [
 121             'title' => ts('Birth Date'),
 122             'operatorType' => CRM_Report_Form::OP_DATE,
 123           ],
 124           'contact_type' => [
 125             'title' => ts('Contact Type'),
 126           ],
 127           'contact_sub_type' => [
 128             'title' => ts('Contact Subtype'),
 129           ],
 130           'is_deceased' => [],
 131           'do_not_phone' => [],
 132           'do_not_email' => [],
 133           'do_not_sms' => [],
 134           'do_not_mail' => [],
 135           'is_opt_out' => [],
 136         ],
 137       ],
 138       'civicrm_line_item' => [
 139         'dao' => 'CRM_Price_DAO_LineItem',
 140       ],
 141       'civicrm_email' => [
 142         'dao' => 'CRM_Core_DAO_Email',
 143         'grouping' => 'contact-field',
 144         'fields' => [
 145           'email' => [
 146             'title' => ts('Email'),
 147             'default' => TRUE,
 148           ],
 149           'on_hold' => [
 150             'title' => ts('Email on hold'),
 151           ],
 152         ],
 153       ],
 154       'civicrm_phone' => [
 155         'dao' => 'CRM_Core_DAO_Phone',
 156         'grouping' => 'contact-field',
 157         'fields' => [
 158           'phone' => [
 159             'title' => ts('Phone'),
 160             'default' => TRUE,
 161           ],
 162         ],
 163       ],
 164     ];
 165     $this->_columns += $this->addAddressFields(FALSE);
 166     $this->_columns += [
 167       'civicrm_contribution' => [
 168         'dao' => 'CRM_Contribute_DAO_Contribution',
 169         'fields' => [
 170           'contact_id' => [
 171             'title' => ts('contactId'),
 172             'no_display' => TRUE,
 173             'required' => TRUE,
 174             'no_repeat' => TRUE,
 175           ],
 176           'receive_date' => [
 177             'title' => ts('Year'),
 178             'no_display' => TRUE,
 179             'required' => TRUE,
 180             'no_repeat' => TRUE,
 181           ],
 182           'last_year_total_amount' => [
 183             'title' => ts('Last Year Total'),
 184             'default' => TRUE,
 185             'type' => CRM_Utils_Type::T_MONEY,
 186             'required' => TRUE,
 187           ],
 188           'civicrm_life_time_total' => [
 189             'title' => ts('Lifetime Total'),
 190             'default' => TRUE,
 191             'type' => CRM_Utils_Type::T_MONEY,
 192             'statistics' => ['sum' => ts('Lifetime total')],
 193             'required' => TRUE,
 194           ],
 195         ],
 196         'filters' => [
 197           'yid' => [
 198             'name' => 'receive_date',
 199             'title' => ts('This Year'),
 200             'operatorType' => CRM_Report_Form::OP_SELECT,
 201             'options' => $optionYear,
 202             'default' => date('Y'),
 203             'type' => CRM_Utils_Type::T_INT,
 204           ],
 205           'financial_type_id' => [
 206             'title' => ts('Financial Type'),
 207             'type' => CRM_Utils_Type::T_INT,
 208             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 209             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 210           ],
 211           'contribution_status_id' => [
 212             'title' => ts('Contribution Status'),
 213             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 214             'options' => CRM_Contribute_BAO_Contribution::buildOptions('contribution_status_id', 'search'),
 215             'default' => ['1'],
 216           ],
 217         ],
 218         'order_bys' => [
 219           'last_year_total_amount' => [
 220             'title' => ts('Total amount last year'),
 221             'default' => '1',
 222             'default_weight' => '0',
 223             'default_order' => 'DESC',
 224           ],
 225         ],
 226       ],
 227     ];
 228     $this->_columns += [
 229       'civicrm_financial_trxn' => [
 230         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 231         'fields' => [
 232           'card_type_id' => [
 233             'title' => ts('Credit Card Type'),
 234             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 235           ],
 236         ],
 237         'filters' => [
 238           'card_type_id' => [
 239             'title' => ts('Credit Card Type'),
 240             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 241             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 242             'default' => NULL,
 243             'type' => CRM_Utils_Type::T_STRING,
 244           ],
 245         ],
 246       ],
 247     ];
 248
 249     // If we have a campaign, build out the relevant elements
 250     $this->addCampaignFields('civicrm_contribution');
 251
 252     $this->_groupFilter = TRUE;
 253     $this->_tagFilter = TRUE;
 254     parent::__construct();
 255   }
 256
 257   /**
 258    * Build select clause for a single field.
 259    *
 260    * @param string $tableName
 261    * @param string $tableKey
 262    * @param string $fieldName
 263    * @param string $field
 264    *
 265    * @return string
 266    */
 267   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 268     if ($fieldName == 'last_year_total_amount') {
 269       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
 270       $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $this->getLastYearColumnTitle();
 271       $this->_statFields[$this->getLastYearColumnTitle()] = "{$tableName}_{$fieldName}";
 272       return "SUM(IF(" . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as {$tableName}_{$fieldName}";
 273     }
 274     if ($fieldName == 'civicrm_life_time_total') {
 275       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
 276       $this->_statFields[$field['title']] = "{$tableName}_{$fieldName}";
 277       return "SUM({$this->_aliases[$tableName]}.total_amount) as {$tableName}_{$fieldName}";
 278     }
 279     if ($fieldName == 'receive_date') {
 280       return self::fiscalYearOffset($field['dbAlias']) .
 281         " as {$tableName}_{$fieldName} ";
 282     }
 283     return FALSE;
 284   }
 285
 286   /**
 287    * Get the title for the last year column.
 288    */
 289   public function getLastYearColumnTitle() {
 290     if ($this->getYearFilterType() == 'calendar') {
 291       return ts('Total for ') . ($this->getCurrentYear() - 1);
 292     }
 293     return ts('Total for Fiscal Year ') . ($this->getCurrentYear() - 1) . '-' . ($this->getCurrentYear());
 294   }
 295
 296   /**
 297    * Construct from clause.
 298    *
 299    * On the first run we are creating a table of contacts to include in the report.
 300    *
 301    * Once contactTempTable is populated we should avoid using any further filters that affect
 302    * the contacts that should be visible.
 303    */
 304   public function from() {
 305     if (!empty($this->contactTempTable)) {
 306       $this->_from = "
 307         FROM  civicrm_contribution {$this->_aliases['civicrm_contribution']}
 308         INNER JOIN $this->contactTempTable restricted_contacts
 309           ON restricted_contacts.cid = {$this->_aliases['civicrm_contribution']}.contact_id
 310           AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 311         INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 312           ON restricted_contacts.cid = {$this->_aliases['civicrm_contact']}.id";
 313
 314       $this->joinAddressFromContact();
 315       $this->joinPhoneFromContact();
 316       $this->joinEmailFromContact();
 317     }
 318     else {
 319       $this->setFromBase('civicrm_contact');
 320
 321       $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ";
 322       if (!$this->groupTempTable) {
 323         // The received_date index is better than the contribution_status_id index (fairly substantially).
 324         // But if we have already pre-filtered down to a group of contacts then we want that to be the
 325         // primary filter and the index hint will block that.
 326         $this->_from .= "USE index (received_date)";
 327       }
 328       $this->_from .= " ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id
 329          AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 330          AND " . $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date") . "
 331        {$this->_aclFrom} ";
 332       $this->selectivelyAddLocationTablesJoinsToFilterQuery();
 333     }
 334
 335     // for credit card type
 336     $this->addFinancialTrxnFromClause();
 337   }
 338
 339   /**
 340    * Generate where clause.
 341    *
 342    * We are overriding this primarily for 'before-after' handling of the receive_date placeholder field.
 343    *
 344    * We call this twice. The first time we are generating a temp table and we want to do an IS NULL on the
 345    * join that draws in contributions from this year. The second time we are filtering elsewhere (contacts via
 346    * the temp table & contributions via selective addition of contributions in the select function).
 347    *
 348    * If lifetime total is NOT selected we can add a further filter here to possibly improve performance
 349    * but the benefit if unproven as yet.
 350    * $clause = $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date");
 351    *
 352    * @param array $field Field specifications
 353    * @param string $op Query operator (not an exact match to sql)
 354    * @param mixed $value
 355    * @param float $min
 356    * @param float $max
 357    *
 358    * @return null|string
 359    */
 360   public function whereClause(&$field, $op, $value, $min, $max) {
 361     if ($field['name'] == 'receive_date') {
 362       $clause = 1;
 363       if (empty($this->contactTempTable)) {
 364         $clause = "{$this->_aliases['civicrm_contact']}.id NOT IN (
 365           SELECT cont_exclude.contact_id
 366           FROM civicrm_contribution cont_exclude
 367           WHERE " . $this->whereClauseThisYear('cont_exclude.receive_date')
 368         . ")";
 369       }
 370     }
 371     // Group filtering is already done so skip.
 372     elseif (!empty($field['group']) && $this->contactTempTable) {
 373       return 1;
 374     }
 375     else {
 376       $clause = parent::whereClause($field, $op, $value, $min, $max);
 377     }
 378     return $clause;
 379   }
 380
 381   /**
 382    * Generate where clause for last calendar year or fiscal year.
 383    *
 384    * @todo must be possible to re-use relative dates stuff.
 385    *
 386    * @param string $fieldName
 387    *
 388    * @return string
 389    */
 390   public function whereClauseLastYear($fieldName) {
 391     return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND '" . $this->getLastDateOfPriorRange() . "'";
 392   }
 393
 394   /**
 395    * Generate where clause for last calendar year or fiscal year.
 396    *
 397    * @todo must be possible to re-use relative dates stuff.
 398    *
 399    * @param string $fieldName
 400    *
 401    * @param int $current_year
 402    * @return null|string
 403    */
 404   public function whereClauseThisYear($fieldName, $current_year = NULL) {
 405     return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "' AND '" . $this->getLastDateOfCurrentRange() . "'";
 406   }
 407
 408   /**
 409    * Get the year value for the current year.
 410    *
 411    * @return string
 412    */
 413   public function getCurrentYear() {
 414     return $this->_params['yid_value'];
 415   }
 416
 417   /**
 418    * Get the date time of the first date in the 'this year' range.
 419    *
 420    * @return string
 421    */
 422   public function getFirstDateOfCurrentRange() {
 423     $current_year = $this->getCurrentYear();
 424     if ($this->getYearFilterType() == 'calendar') {
 425       return "{$current_year }-01-01";
 426     }
 427     else {
 428       $fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart;
 429       return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
 430     }
 431   }
 432
 433   /**
 434    * Get the year value for the current year.
 435    *
 436    * @return string
 437    */
 438   public function getYearFilterType() {
 439     return CRM_Utils_Array::value('yid_op', $this->_params, 'calendar');
 440   }
 441
 442   /**
 443    * Get the date time of the last date in the 'this year' range.
 444    *
 445    * @return string
 446    */
 447   public function getLastDateOfCurrentRange() {
 448     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfCurrentRange())));
 449   }
 450
 451   /**
 452    * Get the date time of the first date in the 'last year' range.
 453    *
 454    * @return string
 455    */
 456   public function getFirstDateOfPriorRange() {
 457     return date('YmdHis', strtotime('- 1 year', strtotime($this->getFirstDateOfCurrentRange())));
 458   }
 459
 460   /**
 461    * Get the date time of the last date in the 'last year' range.
 462    *
 463    * @return string
 464    */
 465   public function getLastDateOfPriorRange() {
 466     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfPriorRange())));
 467   }
 468
 469   public function groupBy() {
 470     $this->_groupBy = "GROUP BY  {$this->_aliases['civicrm_contribution']}.contact_id ";
 471     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id");
 472     $this->assign('chartSupported', TRUE);
 473   }
 474
 475   /**
 476    * @param $rows
 477    *
 478    * @return array
 479    */
 480   public function statistics(&$rows) {
 481
 482     $statistics = parent::statistics($rows);
 483     // The parent class does something odd where it adds an extra row to the count for the grand total.
 484     // Perhaps that works on some other report? But here it just seems odd.
 485     $this->countStat($statistics, count($rows));
 486     if (!empty($rows)) {
 487       if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_last_year_total_amount'])) {
 488         $statistics['counts']['civicrm_contribution_last_year_total_amount'] = [
 489           'value' => $this->rollupRow['civicrm_contribution_last_year_total_amount'],
 490           'title' => $this->getLastYearColumnTitle(),
 491           'type' => CRM_Utils_Type::T_MONEY,
 492         ];
 493
 494       }
 495       if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_civicrm_life_time_total'])) {
 496         $statistics['counts']['civicrm_contribution_civicrm_life_time_total'] = [
 497           'value' => $this->rollupRow['civicrm_contribution_civicrm_life_time_total'],
 498           'title' => ts('Total LifeTime'),
 499           'type' => CRM_Utils_Type::T_MONEY,
 500         ];
 501       }
 502       else {
 503         $select = "SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount,
 504           SUM(IF( " . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as last_year
 505          ";
 506         $sql = "{$select} {$this->_from} {$this->_where}";
 507         $dao = CRM_Core_DAO::executeQuery($sql);
 508         if ($dao->fetch()) {
 509           $statistics['counts']['amount'] = [
 510             'value' => $dao->amount,
 511             'title' => ts('Total LifeTime'),
 512             'type' => CRM_Utils_Type::T_MONEY,
 513           ];
 514           $statistics['counts']['last_year'] = [
 515             'value' => $dao->last_year,
 516             'title' => $this->getLastYearColumnTitle(),
 517             'type' => CRM_Utils_Type::T_MONEY,
 518           ];
 519         }
 520       }
 521     }
 522
 523     return $statistics;
 524   }
 525
 526   /**
 527    * This function is called by both the api (tests) and the UI.
 528    */
 529   public function beginPostProcessCommon() {
 530     $this->buildQuery();
 531     // @todo this acl has no test coverage and is very hard to test manually so could be fragile.
 532     $this->resetFormSqlAndWhereHavingClauses();
 533
 534     $this->contactTempTable = $this->createTemporaryTable('rptlybunt', "
 535       SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from}
 536       {$this->_where}
 537       GROUP BY {$this->_aliases['civicrm_contact']}.id"
 538     );
 539     $this->limit();
 540     if (empty($this->_params['charts'])) {
 541       $this->setPager();
 542     }
 543
 544     // Reset where clauses to be regenerated in postProcess.
 545     $this->_whereClauses = [];
 546   }
 547
 548   /**
 549    * Build the report query.
 550    *
 551    * The issue we are hitting is that if we want to do group by & then ORDER BY we have to
 552    * wrap the query in an outer query with the order by - otherwise the group by takes precedent.
 553    * This is an issue when we want to group by contact but order by the maximum aggregate donation.
 554    *
 555    * @param bool $applyLimit
 556    *
 557    * @return string
 558    */
 559   public function buildQuery($applyLimit = TRUE) {
 560     $this->buildGroupTempTable();
 561     $this->buildPermissionClause();
 562     // Calling where & select before FROM allows us to build temp tables to use in from.
 563     $this->where();
 564     $this->select();
 565     $this->from();
 566     $this->customDataFrom(empty($this->contactTempTable));
 567
 568     $this->groupBy();
 569     $this->orderBy();
 570     $limitFilter = '';
 571
 572     // order_by columns not selected for display need to be included in SELECT
 573     // This differs from parent in that we are getting those not in order by rather than not in
 574     // sections, as we need to adapt to our contact group by.
 575     $unselectedSectionColumns = array_diff_key($this->_orderByFields, $this->getSelectColumns());
 576     foreach ($unselectedSectionColumns as $alias => $section) {
 577       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 578     }
 579
 580     if ($applyLimit && empty($this->_params['charts'])) {
 581       $this->limit();
 582     }
 583
 584     $sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}";
 585
 586     if (!empty($this->_orderByArray)) {
 587       $this->_orderBy = str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY ISNULL(civicrm_contribution_contact_id), " . implode(', ', $this->_orderByArray));
 588       $this->_orderBy = str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy);
 589       foreach ($this->_orderByFields as $field) {
 590         $this->_orderBy = str_replace($field['dbAlias'], $field['tplField'], $this->_orderBy);
 591       }
 592       $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
 593       $sql = "SELECT SQL_CALC_FOUND_ROWS  * FROM ( $sql ) as inner_query {$this->_orderBy} $this->_limit";
 594     }
 595
 596     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
 597     $this->addToDeveloperTab($sql);
 598
 599     return $sql;
 600   }
 601
 602   /**
 603    * Reset the form sql and where / having clause arrays.
 604    *
 605    * We do an early iteration of the report queries to generate the temp table.
 606    *
 607    * However, that iteration populates the sql for the developer tab,
 608    * the whereClauses & the havingClauses and they are populated again in the normal
 609    * report flow. This is harmless but confusing - ie. the where clause winds up repeating
 610    * the same filters and the dev tab shows the query twice, so we rest them.
 611    */
 612   protected function resetFormSqlAndWhereHavingClauses() {
 613     $this->sql = '';
 614     $this->_havingClauses = [];
 615     $this->_whereClauses = [];
 616     $this->sqlArray = [];
 617   }
 618
 619   /**
 620    * @param $rows
 621    */
 622   public function buildChart(&$rows) {
 623
 624     $graphRows = [];
 625     $count = 0;
 626     $display = [];
 627
 628     $current_year = $this->_params['yid_value'];
 629     $previous_year = $current_year - 1;
 630     $interval[$previous_year] = $previous_year;
 631     $interval['life_time'] = 'Life Time';
 632
 633     foreach ($rows as $key => $row) {
 634       // The final row contains the totals so we don't need to include it here.
 635       if (!empty($row['civicrm_contribution_contact_id'])) {
 636         $display['life_time'] = CRM_Utils_Array::value('life_time', $display) +
 637           $row['civicrm_contribution_civicrm_life_time_total'];
 638         $display[$previous_year] = CRM_Utils_Array::value($previous_year, $display) + $row['civicrm_contribution_last_year_total_amount'];
 639       }
 640     }
 641
 642     $config = CRM_Core_Config::Singleton();
 643     $graphRows['value'] = $display;
 644     $chartInfo = [
 645       'legend' => ts('Lybunt Report'),
 646       'xname' => ts('Year'),
 647       'yname' => ts('Amount (%1)', [1 => $config->defaultCurrency]),
 648     ];
 649     if ($this->_params['charts']) {
 650       // build chart.
 651       CRM_Utils_Chart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
 652       $this->assign('chartType', $this->_params['charts']);
 653     }
 654   }
 655
 656   /**
 657    * Alter display of rows.
 658    *
 659    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 660    * such as rendering contacts as links.
 661    *
 662    * @param array $rows
 663    *   Rows generated by SQL, with an array for each row.
 664    */
 665   public function alterDisplay(&$rows) {
 666     $entryFound = FALSE;
 667
 668     foreach ($rows as $rowNum => $row) {
 669       //Convert Display name into link
 670       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 671         array_key_exists('civicrm_contribution_contact_id', $row)
 672       ) {
 673         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 674           'reset=1&force=1&id_op=eq&id_value=' .
 675           $row['civicrm_contribution_contact_id'],
 676           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 677         );
 678         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 679         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 680         $entryFound = TRUE;
 681       }
 682
 683       // convert campaign_id to campaign title
 684       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 685         if ($value = $row['civicrm_contribution_campaign_id']) {
 686           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns[$value];
 687           $entryFound = TRUE;
 688         }
 689       }
 690       // Display 'Yes' if the email is on hold (leave blank for no so it stands out better).
 691       if (array_key_exists('civicrm_email_on_hold', $row)) {
 692         $rows[$rowNum]['civicrm_email_on_hold'] = $row['civicrm_email_on_hold'] ? ts('Yes') : '';
 693         $entryFound = TRUE;
 694       }
 695
 696       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 697       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 698
 699       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 700         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 701         $entryFound = TRUE;
 702       }
 703
 704       // skip looking further in rows, if first row itself doesn't
 705       // have the column we need
 706       if (!$entryFound) {
 707         break;
 708       }
 709     }
 710   }
 711
 712   /**
 713    * Override "This Year" $op options
 714    * @param string $type
 715    * @param null $fieldName
 716    *
 717    * @return array
 718    */
 719   public function getOperationPair($type = "string", $fieldName = NULL) {
 720     if ($fieldName == 'yid') {
 721       return [
 722         'calendar' => ts('Is Calendar Year'),
 723         'fiscal' => ts('Fiscal Year Starting'),
 724       ];
 725     }
 726     return parent::getOperationPair($type, $fieldName);
 727   }
 728
 729 }