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   /**
  20    * This is the report that links will lead to.
  21    *
  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.
  24    *
  25    * @var array
  26    */
  27   public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report'];
  28
  29   protected $lifeTime_from = NULL;
  30   protected $lifeTime_where = NULL;
  31   protected $_customGroupExtends = [
  32     'Contribution',
  33     'Contact',
  34     'Individual',
  35     'Household',
  36     'Organization',
  37   ];
  38
  39   /**
  40    * Table containing list of contact IDs.
  41    *
  42    * @var string
  43    */
  44   protected $contactTempTable = '';
  45
  46   /**
  47    * This report has been optimised for group filtering.
  48    *
  49    * @var bool
  50    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  51    */
  52   protected $groupFilterNotOptimised = FALSE;
  53
  54   /**
  55    * Class constructor.
  56    */
  57   public function __construct() {
  58     $this->optimisedForOnlyFullGroupBy = FALSE;
  59     $this->_rollup = 'WITH ROLLUP';
  60     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  61     $yearsInPast = 10;
  62     $yearsInFuture = 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'];
  67       $date['minYear']++;
  68     }
  69
  70     $this->_columns = [
  71       'civicrm_contact' => [
  72         'dao' => 'CRM_Contact_DAO_Contact',
  73         'grouping' => 'contact-field',
  74         'fields' => $this->getBasicContactFields(),
  75         'order_bys' => [
  76           'sort_name' => [
  77             'title' => ts('Last Name, First Name'),
  78             'default' => '0',
  79             'default_order' => 'ASC',
  80           ],
  81           'first_name' => [
  82             'name' => 'first_name',
  83             'title' => ts('First Name'),
  84           ],
  85           'gender_id' => [
  86             'name' => 'gender_id',
  87             'title' => ts('Gender'),
  88           ],
  89           'birth_date' => [
  90             'name' => 'birth_date',
  91             'title' => ts('Birth Date'),
  92           ],
  93           'contact_type' => [
  94             'title' => ts('Contact Type'),
  95           ],
  96           'contact_sub_type' => [
  97             'title' => ts('Contact Subtype'),
  98           ],
  99         ],
 100         'filters' => [
 101           'sort_name' => [
 102             'title' => ts('Donor Name'),
 103             'operator' => 'like',
 104           ],
 105           'id' => [
 106             'title' => ts('Contact ID'),
 107             'no_display' => TRUE,
 108           ],
 109           'gender_id' => [
 110             'title' => ts('Gender'),
 111             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 112             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 113           ],
 114           'birth_date' => [
 115             'title' => ts('Birth Date'),
 116             'operatorType' => CRM_Report_Form::OP_DATE,
 117           ],
 118           'contact_type' => [
 119             'title' => ts('Contact Type'),
 120           ],
 121           'contact_sub_type' => [
 122             'title' => ts('Contact Subtype'),
 123           ],
 124           'is_deceased' => [],
 125           'do_not_phone' => [],
 126           'do_not_email' => [],
 127           'do_not_sms' => [],
 128           'do_not_mail' => [],
 129           'is_opt_out' => [],
 130         ],
 131       ],
 132       'civicrm_line_item' => [
 133         'dao' => 'CRM_Price_DAO_LineItem',
 134       ],
 135       'civicrm_email' => [
 136         'dao' => 'CRM_Core_DAO_Email',
 137         'grouping' => 'contact-field',
 138         'fields' => [
 139           'email' => [
 140             'title' => ts('Email'),
 141             'default' => TRUE,
 142           ],
 143           'on_hold' => [
 144             'title' => ts('Email on hold'),
 145           ],
 146         ],
 147         'filters' => [
 148           '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(),
 153           ],
 154         ],
 155       ],
 156       'civicrm_phone' => [
 157         'dao' => 'CRM_Core_DAO_Phone',
 158         'grouping' => 'contact-field',
 159         'fields' => [
 160           'phone' => [
 161             'title' => ts('Phone'),
 162             'default' => TRUE,
 163           ],
 164         ],
 165       ],
 166     ];
 167     $this->_columns += $this->addAddressFields(FALSE);
 168     $this->_columns += [
 169       'civicrm_contribution' => [
 170         'dao' => 'CRM_Contribute_DAO_Contribution',
 171         'fields' => [
 172           'contact_id' => [
 173             'title' => ts('contactId'),
 174             'no_display' => TRUE,
 175             'required' => TRUE,
 176             'no_repeat' => TRUE,
 177           ],
 178           'receive_date' => [
 179             'title' => ts('Year'),
 180             'no_display' => TRUE,
 181             'required' => TRUE,
 182             'no_repeat' => TRUE,
 183           ],
 184           'last_year_total_amount' => [
 185             'title' => ts('Last Year Total'),
 186             'default' => TRUE,
 187             'type' => CRM_Utils_Type::T_MONEY,
 188             'required' => TRUE,
 189           ],
 190           'civicrm_life_time_total' => [
 191             'title' => ts('Lifetime Total'),
 192             'default' => TRUE,
 193             'type' => CRM_Utils_Type::T_MONEY,
 194             'statistics' => ['sum' => ts('Lifetime total')],
 195             'required' => TRUE,
 196           ],
 197         ],
 198         'filters' => [
 199           'yid' => [
 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,
 206           ],
 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'),
 212           ],
 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'),
 217             'default' => ['1'],
 218           ],
 219         ],
 220         'order_bys' => [
 221           'last_year_total_amount' => [
 222             'title' => ts('Total amount last year'),
 223             'default' => '1',
 224             'default_weight' => '0',
 225             'default_order' => 'DESC',
 226           ],
 227         ],
 228       ],
 229     ];
 230     $this->_columns += [
 231       'civicrm_financial_trxn' => [
 232         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 233         'fields' => [
 234           'card_type_id' => [
 235             'title' => ts('Credit Card Type'),
 236             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 237           ],
 238         ],
 239         'filters' => [
 240           'card_type_id' => [
 241             'title' => ts('Credit Card Type'),
 242             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 243             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 244             'default' => NULL,
 245             'type' => CRM_Utils_Type::T_STRING,
 246           ],
 247         ],
 248       ],
 249     ];
 250
 251     // If we have a campaign, build out the relevant elements
 252     $this->addCampaignFields('civicrm_contribution');
 253
 254     // Add charts support
 255     $this->_charts = [
 256       '' => ts('Tabular'),
 257       'barChart' => ts('Bar Chart'),
 258       'pieChart' => ts('Pie Chart'),
 259     ];
 260
 261     $this->_groupFilter = TRUE;
 262     $this->_tagFilter = TRUE;
 263     parent::__construct();
 264   }
 265
 266   /**
 267    * Build select clause for a single field.
 268    *
 269    * @param string $tableName
 270    * @param string $tableKey
 271    * @param string $fieldName
 272    * @param string $field
 273    *
 274    * @return string
 275    */
 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}";
 282     }
 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}";
 287     }
 288     if ($fieldName == 'receive_date') {
 289       return self::fiscalYearOffset($field['dbAlias']) .
 290         " as {$tableName}_{$fieldName} ";
 291     }
 292     return FALSE;
 293   }
 294
 295   /**
 296    * Get the title for the last year column.
 297    */
 298   public function getLastYearColumnTitle() {
 299     if ($this->getYearFilterType() == 'calendar') {
 300       return ts('Total for ') . ($this->getCurrentYear() - 1);
 301     }
 302     return ts('Total for Fiscal Year ') . ($this->getCurrentYear() - 1) . '-' . ($this->getCurrentYear());
 303   }
 304
 305   /**
 306    * Construct from clause.
 307    *
 308    * On the first run we are creating a table of contacts to include in the report.
 309    *
 310    * Once contactTempTable is populated we should avoid using any further filters that affect
 311    * the contacts that should be visible.
 312    */
 313   public function from() {
 314     if (!empty($this->contactTempTable)) {
 315       $this->_from = "
 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";
 323
 324       $this->joinAddressFromContact();
 325       $this->joinPhoneFromContact();
 326       $this->joinEmailFromContact();
 327     }
 328     else {
 329       $this->setFromBase('civicrm_contact');
 330
 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)";
 337       }
 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") . "
 342        {$this->_aclFrom} ";
 343       $this->selectivelyAddLocationTablesJoinsToFilterQuery();
 344     }
 345
 346     // for credit card type
 347     $this->addFinancialTrxnFromClause();
 348   }
 349
 350   /**
 351    * Generate where clause.
 352    *
 353    * We are overriding this primarily for 'before-after' handling of the receive_date placeholder field.
 354    *
 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).
 358    *
 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");
 362    *
 363    * @param array $field Field specifications
 364    * @param string $op Query operator (not an exact match to sql)
 365    * @param mixed $value
 366    * @param float $min
 367    * @param float $max
 368    *
 369    * @return null|string
 370    */
 371   public function whereClause(&$field, $op, $value, $min, $max) {
 372     if ($field['name'] == 'receive_date') {
 373       $clause = 1;
 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')
 379         . ")";
 380       }
 381     }
 382     // Group filtering is already done so skip.
 383     elseif (!empty($field['group']) && $this->contactTempTable) {
 384       return 1;
 385     }
 386     else {
 387       $clause = parent::whereClause($field, $op, $value, $min, $max);
 388     }
 389     return $clause;
 390   }
 391
 392   /**
 393    * Generate where clause for last calendar year or fiscal year.
 394    *
 395    * @todo must be possible to re-use relative dates stuff.
 396    *
 397    * @param string $fieldName
 398    *
 399    * @return string
 400    */
 401   public function whereClauseLastYear($fieldName) {
 402     return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND '" . $this->getLastDateOfPriorRange() . "'";
 403   }
 404
 405   /**
 406    * Generate where clause for last calendar year or fiscal year.
 407    *
 408    * @todo must be possible to re-use relative dates stuff.
 409    *
 410    * @param string $fieldName
 411    *
 412    * @param int $current_year
 413    * @return null|string
 414    */
 415   public function whereClauseThisYear($fieldName, $current_year = NULL) {
 416     return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "' AND '" . $this->getLastDateOfCurrentRange() . "'";
 417   }
 418
 419   /**
 420    * Get the year value for the current year.
 421    *
 422    * @return string
 423    */
 424   public function getCurrentYear() {
 425     return $this->_params['yid_value'];
 426   }
 427
 428   /**
 429    * Get the date time of the first date in the 'this year' range.
 430    *
 431    * @return string
 432    */
 433   public function getFirstDateOfCurrentRange() {
 434     $current_year = $this->getCurrentYear();
 435     if ($this->getYearFilterType() == 'calendar') {
 436       return "{$current_year }-01-01";
 437     }
 438     else {
 439       $fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart;
 440       return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
 441     }
 442   }
 443
 444   /**
 445    * Get the year value for the current year.
 446    *
 447    * @return string
 448    */
 449   public function getYearFilterType() {
 450     return CRM_Utils_Array::value('yid_op', $this->_params, 'calendar');
 451   }
 452
 453   /**
 454    * Get the date time of the last date in the 'this year' range.
 455    *
 456    * @return string
 457    */
 458   public function getLastDateOfCurrentRange() {
 459     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfCurrentRange())));
 460   }
 461
 462   /**
 463    * Get the date time of the first date in the 'last year' range.
 464    *
 465    * @return string
 466    */
 467   public function getFirstDateOfPriorRange() {
 468     return date('YmdHis', strtotime('- 1 year', strtotime($this->getFirstDateOfCurrentRange())));
 469   }
 470
 471   /**
 472    * Get the date time of the last date in the 'last year' range.
 473    *
 474    * @return string
 475    */
 476   public function getLastDateOfPriorRange() {
 477     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfPriorRange())));
 478   }
 479
 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);
 484   }
 485
 486   /**
 487    * @param array $rows
 488    *
 489    * @return array
 490    */
 491   public function statistics(&$rows) {
 492
 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));
 497     if (!empty($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,
 503         ];
 504
 505       }
 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,
 511         ];
 512       }
 513       else {
 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
 516          ";
 517         $sql = "{$select} {$this->_from} {$this->_where}";
 518         $dao = CRM_Core_DAO::executeQuery($sql);
 519         if ($dao->fetch()) {
 520           $statistics['counts']['amount'] = [
 521             'value' => $dao->amount,
 522             'title' => ts('Total LifeTime'),
 523             'type' => CRM_Utils_Type::T_MONEY,
 524           ];
 525           $statistics['counts']['last_year'] = [
 526             'value' => $dao->last_year,
 527             'title' => $this->getLastYearColumnTitle(),
 528             'type' => CRM_Utils_Type::T_MONEY,
 529           ];
 530         }
 531       }
 532     }
 533
 534     return $statistics;
 535   }
 536
 537   /**
 538    * This function is called by both the api (tests) and the UI.
 539    */
 540   public function beginPostProcessCommon() {
 541     $this->buildQuery();
 542     // @todo this acl has no test coverage and is very hard to test manually so could be fragile.
 543     $this->resetFormSqlAndWhereHavingClauses();
 544
 545     $this->contactTempTable = $this->createTemporaryTable('rptlybunt', "
 546       SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from}
 547       {$this->_where}
 548       GROUP BY {$this->_aliases['civicrm_contact']}.id"
 549     );
 550     $this->limit();
 551     if (empty($this->_params['charts'])) {
 552       $this->setPager();
 553     }
 554
 555     // Reset where clauses to be regenerated in postProcess.
 556     $this->_whereClauses = [];
 557   }
 558
 559   /**
 560    * Build the report query.
 561    *
 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.
 565    *
 566    * @param bool $applyLimit
 567    *
 568    * @return string
 569    */
 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.
 574     $this->where();
 575     $this->select();
 576     $this->from();
 577     $this->customDataFrom(empty($this->contactTempTable));
 578
 579     $this->groupBy();
 580     $this->orderBy();
 581     $limitFilter = '';
 582
 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}";
 589     }
 590
 591     if ($applyLimit && empty($this->_params['charts'])) {
 592       $this->limit();
 593     }
 594
 595     $sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}";
 596
 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);
 602       }
 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";
 605     }
 606
 607     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
 608     $this->addToDeveloperTab($sql);
 609
 610     return $sql;
 611   }
 612
 613   /**
 614    * Reset the form sql and where / having clause arrays.
 615    *
 616    * We do an early iteration of the report queries to generate the temp table.
 617    *
 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.
 622    */
 623   protected function resetFormSqlAndWhereHavingClauses() {
 624     $this->sql = '';
 625     $this->_havingClauses = [];
 626     $this->_whereClauses = [];
 627     $this->sqlArray = [];
 628   }
 629
 630   /**
 631    * @param $rows
 632    */
 633   public function buildChart(&$rows) {
 634
 635     $graphRows = [];
 636     $count = 0;
 637     $display = [];
 638
 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');
 643
 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'];
 650       }
 651     }
 652
 653     $config = CRM_Core_Config::Singleton();
 654     $graphRows['value'] = $display;
 655     $chartInfo = [
 656       'legend' => ts('Lybunt Report'),
 657       'xname' => ts('Year'),
 658       'yname' => ts('Amount (%1)', [1 => $config->defaultCurrency]),
 659     ];
 660     if ($this->_params['charts']) {
 661       // build chart.
 662       CRM_Utils_Chart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
 663       $this->assign('chartType', $this->_params['charts']);
 664     }
 665   }
 666
 667   /**
 668    * Alter display of rows.
 669    *
 670    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 671    * such as rendering contacts as links.
 672    *
 673    * @param array $rows
 674    *   Rows generated by SQL, with an array for each row.
 675    */
 676   public function alterDisplay(&$rows) {
 677     $entryFound = FALSE;
 678
 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)
 683       ) {
 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
 688         );
 689         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 690         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 691         $entryFound = TRUE;
 692       }
 693
 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];
 698           $entryFound = TRUE;
 699         }
 700       }
 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') : '';
 704         $entryFound = TRUE;
 705       }
 706
 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;
 709
 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');
 712         $entryFound = TRUE;
 713       }
 714
 715       // skip looking further in rows, if first row itself doesn't
 716       // have the column we need
 717       if (!$entryFound) {
 718         break;
 719       }
 720     }
 721   }
 722
 723   /**
 724    * Override "This Year" $op options
 725    * @param string $type
 726    * @param null $fieldName
 727    *
 728    * @return array
 729    */
 730   public function getOperationPair($type = "string", $fieldName = NULL) {
 731     if ($fieldName == 'yid') {
 732       return [
 733         'calendar' => ts('Is Calendar Year'),
 734         'fiscal' => ts('Fiscal Year Starting'),
 735       ];
 736     }
 737     return parent::getOperationPair($type, $fieldName);
 738   }
 739
 740 }