CRM/Report/Form/Contribute/History.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  */
  33 class CRM_Report_Form_Contribute_History extends CRM_Report_Form {
  34   /**
  35    * Primary Contacts count limitCONSTROW_COUNT_LIMIT = 10;
  36    */
  37
  38   /**
  39    * @var array
  40    */
  41   protected $_relationshipColumns = [];
  42
  43   protected $_customGroupExtends = [
  44     'Contact',
  45     'Individual',
  46     'Contribution',
  47   ];
  48
  49   protected $_referenceYear = [
  50     'this_year' => '',
  51     'other_year' => '',
  52   ];
  53   protected $_yearStatisticsFrom = '';
  54
  55   protected $_yearStatisticsTo = '';
  56
  57   /**
  58    */
  59   public function __construct() {
  60     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  61     $yearsInPast = 4;
  62     $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, 0);
  63     $count = $date['maxYear'];
  64     $optionYear = ['' => ts('- select -')];
  65
  66     $this->_yearStatisticsFrom = $date['minYear'];
  67     $this->_yearStatisticsTo = $date['maxYear'];
  68
  69     while ($date['minYear'] <= $count) {
  70       $optionYear[$date['minYear']] = $date['minYear'];
  71       $date['minYear']++;
  72     }
  73
  74     $relationTypeOp = [];
  75     $relationshipTypes = CRM_Core_PseudoConstant::relationshipType();
  76     foreach ($relationshipTypes as $rid => $rtype) {
  77       if ($rtype['label_a_b'] != $rtype['label_b_a']) {
  78         $relationTypeOp[$rid] = "{$rtype['label_a_b']}/{$rtype['label_b_a']}";
  79       }
  80       else {
  81         $relationTypeOp[$rid] = $rtype['label_a_b'];
  82       }
  83     }
  84
  85     $this->_columns = [
  86       'civicrm_contact' => [
  87         'dao' => 'CRM_Contact_DAO_Contact',
  88         'fields' => [
  89           'sort_name' => [
  90             'title' => ts('Contact Name'),
  91             'default' => TRUE,
  92             'required' => TRUE,
  93             'no_repeat' => TRUE,
  94           ],
  95           'first_name' => [
  96             'title' => ts('First Name'),
  97           ],
  98           'middle_name' => [
  99             'title' => ts('Middle Name'),
 100           ],
 101           'last_name' => [
 102             'title' => ts('Last Name'),
 103           ],
 104           'id' => [
 105             'no_display' => TRUE,
 106             'default' => TRUE,
 107             'required' => TRUE,
 108           ],
 109           'gender_id' => [
 110             'title' => ts('Gender'),
 111           ],
 112           'birth_date' => [
 113             'title' => ts('Birth Date'),
 114           ],
 115           'age' => [
 116             'title' => ts('Age'),
 117             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
 118           ],
 119           'contact_type' => [
 120             'title' => ts('Contact Type'),
 121           ],
 122           'contact_sub_type' => [
 123             'title' => ts('Contact Subtype'),
 124           ],
 125         ],
 126         'grouping' => 'contact-fields',
 127         'order_bys' => [
 128           'sort_name' => [
 129             'title' => ts('Last Name, First Name'),
 130             'default' => '1',
 131             'default_weight' => '0',
 132             'default_order' => 'ASC',
 133           ],
 134           'first_name' => [
 135             'name' => 'first_name',
 136             'title' => ts('First Name'),
 137           ],
 138           'gender_id' => [
 139             'name' => 'gender_id',
 140             'title' => ts('Gender'),
 141           ],
 142           'birth_date' => [
 143             'name' => 'birth_date',
 144             'title' => ts('Birth Date'),
 145           ],
 146           'contact_type' => [
 147             'title' => ts('Contact Type'),
 148           ],
 149           'contact_sub_type' => [
 150             'title' => ts('Contact Subtype'),
 151           ],
 152         ],
 153         'filters' => [
 154           'sort_name' => ['title' => ts('Contact Name')],
 155           'id' => [
 156             'title' => ts('Contact ID'),
 157             'no_display' => TRUE,
 158           ],
 159           'gender_id' => [
 160             'title' => ts('Gender'),
 161             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 162             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 163           ],
 164           'birth_date' => [
 165             'title' => ts('Birth Date'),
 166             'operatorType' => CRM_Report_Form::OP_DATE,
 167           ],
 168           'contact_type' => [
 169             'title' => ts('Contact Type'),
 170           ],
 171           'contact_sub_type' => [
 172             'title' => ts('Contact Subtype'),
 173           ],
 174         ],
 175       ],
 176       'civicrm_email' => [
 177         'dao' => 'CRM_Core_DAO_Email',
 178         'fields' => [
 179           'email' => [
 180             'title' => ts('Email'),
 181             'no_repeat' => TRUE,
 182           ],
 183         ],
 184         'grouping' => 'contact-fields',
 185       ],
 186       'civicrm_phone' => [
 187         'dao' => 'CRM_Core_DAO_Phone',
 188         'fields' => [
 189           'phone' => [
 190             'title' => ts('Phone'),
 191             'no_repeat' => TRUE,
 192           ],
 193         ],
 194         'grouping' => 'contact-fields',
 195       ],
 196     ] + $this->addAddressFields(FALSE, FALSE, FALSE, []) + [
 197       'civicrm_relationship' => [
 198         'dao' => 'CRM_Contact_DAO_Relationship',
 199         'fields' => [
 200           'relationship_type_id' => [
 201             'title' => ts('Relationship Type'),
 202             'default' => TRUE,
 203           ],
 204           'contact_id_a' => ['no_display' => TRUE],
 205           'contact_id_b' => ['no_display' => TRUE],
 206         ],
 207         'filters' => [
 208           'relationship_type_id' => [
 209             'title' => ts('Relationship Type'),
 210             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 211             'options' => $relationTypeOp,
 212             'type' => CRM_Utils_Type::T_STRING,
 213           ],
 214         ],
 215       ],
 216     ] + [
 217       'civicrm_contribution' => [
 218         'dao' => 'CRM_Contribute_DAO_Contribution',
 219         'fields' => [
 220           'total_amount' => [
 221             'title' => ts('Amount Statistics'),
 222             'default' => TRUE,
 223             'required' => TRUE,
 224             'no_display' => TRUE,
 225             'statistics' => ['sum' => ts('Aggregate Amount')],
 226           ],
 227           'receive_date' => [
 228             'required' => TRUE,
 229             'default' => TRUE,
 230             'no_display' => TRUE,
 231           ],
 232         ],
 233         'grouping' => 'contri-fields',
 234         'filters' => [
 235           'this_year' => [
 236             'title' => ts('This Year'),
 237             'operatorType' => CRM_Report_Form::OP_SELECT,
 238             'options' => $optionYear,
 239             'default' => '',
 240           ],
 241           'other_year' => [
 242             'title' => ts('Other Years'),
 243             'operatorType' => CRM_Report_Form::OP_SELECT,
 244             'options' => $optionYear,
 245             'default' => '',
 246           ],
 247           'receive_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 248           'contribution_status_id' => [
 249             'title' => ts('Contribution Status'),
 250             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 251             'options' => CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'label'),
 252             'default' => [1],
 253           ],
 254           'financial_type_id' => [
 255             'title' => ts('Financial Type'),
 256             'type' => CRM_Utils_Type::T_INT,
 257             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 258             'options' => CRM_Contribute_PseudoConstant::financialType(),
 259           ],
 260           'total_amount' => [
 261             'title' => ts('Contribution Amount'),
 262           ],
 263           'total_sum' => [
 264             'title' => ts('Aggregate Amount'),
 265             'type' => CRM_Report_Form::OP_INT,
 266             'dbAlias' => 'civicrm_contribution_total_amount_sum',
 267             'having' => TRUE,
 268           ],
 269         ],
 270       ],
 271     ];
 272     $this->_columns += [
 273       'civicrm_financial_trxn' => [
 274         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 275         'fields' => [
 276           'card_type_id' => [
 277             'title' => ts('Credit Card Type'),
 278             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 279           ],
 280         ],
 281         'filters' => [
 282           'card_type_id' => [
 283             'title' => ts('Credit Card Type'),
 284             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 285             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 286             'default' => NULL,
 287             'type' => CRM_Utils_Type::T_STRING,
 288           ],
 289         ],
 290       ],
 291     ];
 292
 293     $this->_columns['civicrm_contribution']['fields']['civicrm_upto_' .
 294     $this->_yearStatisticsFrom] = [
 295       'title' => ts('Up To %1 Donation', [1 => $this->_yearStatisticsFrom]),
 296       'default' => TRUE,
 297       'type' => CRM_Utils_Type::T_MONEY,
 298       'is_statistics' => TRUE,
 299     ];
 300
 301     $yearConter = $this->_yearStatisticsFrom;
 302     $yearConter++;
 303     while ($yearConter <= $this->_yearStatisticsTo) {
 304       $this->_columns['civicrm_contribution']['fields'][$yearConter] = [
 305         'title' => ts('%1 Donation', [1 => $yearConter]),
 306         'default' => TRUE,
 307         'type' => CRM_Utils_Type::T_MONEY,
 308         'is_statistics' => TRUE,
 309       ];
 310       $yearConter++;
 311     }
 312
 313     $this->_columns['civicrm_contribution']['fields']['aggregate_amount'] = [
 314       'title' => ts('Aggregate Amount'),
 315       'type' => CRM_Utils_Type::T_MONEY,
 316       'is_statistics' => TRUE,
 317     ];
 318
 319     $this->_groupFilter = TRUE;
 320     $this->_tagFilter = TRUE;
 321     parent::__construct();
 322   }
 323
 324   public function preProcess() {
 325     parent::preProcess();
 326   }
 327
 328   public function select() {
 329     $select = [];
 330     // @todo remove this & use parent (with maybe some override in this or better yet selectWhere fn)
 331     $this->_columnHeaders = [];
 332
 333     foreach ($this->_columns as $tableName => $table) {
 334       if (array_key_exists('fields', $table)) {
 335         foreach ($table['fields'] as $fieldName => $field) {
 336
 337           if (!empty($field['required']) ||
 338             !empty($this->_params['fields'][$fieldName])
 339           ) {
 340             if ($tableName == 'civicrm_relationship') {
 341               $this->_relationshipColumns["{$tableName}_{$fieldName}"] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 342               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 343               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 344               continue;
 345             }
 346
 347             if (!empty($field['is_statistics'])) {
 348               $this->_columnHeaders[$fieldName]['type'] = $field['type'];
 349               $this->_columnHeaders[$fieldName]['title'] = $field['title'];
 350               continue;
 351             }
 352             elseif ($fieldName == 'receive_date') {
 353               if ((CRM_Utils_Array::value('this_year_op', $this->_params) ==
 354                   'fiscal' && !empty($this->_params['this_year_value'])) ||
 355                 (CRM_Utils_Array::value('other_year_op', $this->_params ==
 356                     'fiscal') && !empty($this->_params['other_year_value']))
 357               ) {
 358                 $select[] = self::fiscalYearOffset($field['dbAlias']) .
 359                   " as {$tableName}_{$fieldName}";
 360               }
 361               else {
 362                 $select[] = " YEAR(" . $field['dbAlias'] . ")" .
 363                   " as {$tableName}_{$fieldName}";
 364               }
 365             }
 366             elseif ($fieldName == 'total_amount') {
 367               $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
 368             }
 369             else {
 370               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 371               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 372               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 373             }
 374             if (!empty($field['no_display'])) {
 375               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = TRUE;
 376             }
 377           }
 378         }
 379       }
 380     }
 381     $this->_selectClauses = $select;
 382
 383     $this->_select = "SELECT " . implode(', ', $select) . " ";
 384   }
 385
 386   public function from() {
 387     $this->_from = "
 388         FROM civicrm_contact  {$this->_aliases['civicrm_contact']}
 389              INNER JOIN civicrm_contribution   {$this->_aliases['civicrm_contribution']}
 390                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 391                         {$this->_aliases['civicrm_contribution']}.is_test = 0 ";
 392
 393     $relContacAlias = 'contact_relationship';
 394     $this->_relationshipFrom = " INNER JOIN civicrm_relationship {$this->_aliases['civicrm_relationship']}
 395                      ON (({$this->_aliases['civicrm_relationship']}.contact_id_a = {$relContacAlias}.id OR {$this->_aliases['civicrm_relationship']}.contact_id_b = {$relContacAlias}.id ) AND {$this->_aliases['civicrm_relationship']}.is_active = 1) ";
 396
 397     $this->joinAddressFromContact();
 398     $this->joinPhoneFromContact();
 399     $this->joinEmailFromContact();
 400
 401     // for credit card type
 402     $this->addFinancialTrxnFromClause();
 403   }
 404
 405   public function where() {
 406     $whereClauses = $havingClauses = $relationshipWhere = [];
 407     $this->_relationshipWhere = '';
 408     $this->_statusClause = '';
 409
 410     foreach ($this->_columns as $tableName => $table) {
 411       if (array_key_exists('filters', $table)) {
 412         foreach ($table['filters'] as $fieldName => $field) {
 413           $clause = NULL;
 414           if ($fieldName == 'this_year' || $fieldName == 'other_year') {
 415             continue;
 416           }
 417           elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
 418           ) {
 419             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 420             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 421             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 422
 423             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 424           }
 425           else {
 426             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 427             if ($op) {
 428               $clause = $this->whereClause($field,
 429                 $op,
 430                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 431                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 432                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 433               );
 434             }
 435           }
 436
 437           if (!empty($clause)) {
 438             if ($tableName == 'civicrm_relationship') {
 439               $relationshipWhere[] = $clause;
 440               continue;
 441             }
 442
 443             if ($fieldName == 'contribution_status_id') {
 444               $this->_statusClause = " AND " . $clause;
 445             }
 446
 447             if (!empty($field['having'])) {
 448               $havingClauses[] = $clause;
 449             }
 450             else {
 451               $whereClauses[] = $clause;
 452             }
 453           }
 454         }
 455       }
 456     }
 457
 458     if (empty($whereClauses)) {
 459       $this->_where = "WHERE ( 1 ) ";
 460       $this->_having = "";
 461     }
 462     else {
 463       $this->_where = "WHERE " . implode(' AND ', $whereClauses);
 464     }
 465
 466     if ($this->_aclWhere) {
 467       $this->_where .= " AND {$this->_aclWhere} ";
 468     }
 469
 470     if (!empty($havingClauses)) {
 471       // use this clause to construct group by clause.
 472       $this->_having = "HAVING " . implode(' AND ', $havingClauses);
 473     }
 474
 475     if (!empty($relationshipWhere)) {
 476       $this->_relationshipWhere = ' AND ' .
 477         implode(' AND ', $relationshipWhere);
 478     }
 479   }
 480
 481   public function groupBy() {
 482     $groupBy = [
 483       "{$this->_aliases['civicrm_contribution']}.contact_id",
 484       "YEAR({$this->_aliases['civicrm_contribution']}.receive_date)",
 485     ];
 486     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 487   }
 488
 489   /**
 490    * Override to set limit to 10.
 491    *
 492    * @param int $rowCount
 493    *
 494    * @return array
 495    */
 496   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
 497     return parent::limit($rowCount);
 498   }
 499
 500   /**
 501    * Override to set pager with limit is 10.
 502    *
 503    * @param int $rowCount
 504    */
 505   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
 506     parent::setPager($rowCount);
 507   }
 508
 509   /**
 510    * @param $rows
 511    *
 512    * @return array
 513    */
 514   public function statistics(&$rows) {
 515     $statistics = parent::statistics($rows);
 516     $count = 0;
 517     foreach ($rows as $rownum => $row) {
 518       if (is_numeric($rownum)) {
 519         $count++;
 520       }
 521     }
 522     $statistics['counts']['rowCount'] = [
 523       'title' => ts('Primary Contact(s) Listed'),
 524       'value' => $count,
 525     ];
 526
 527     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
 528       $statistics['counts']['rowsFound'] = [
 529         'title' => ts('Total Primary Contact(s)'),
 530         'value' => $this->_rowsFound,
 531       ];
 532     }
 533
 534     return $statistics;
 535   }
 536
 537   /**
 538    * @param $fields
 539    * @param $files
 540    * @param $self
 541    *
 542    * @return array
 543    */
 544   public static function formRule($fields, $files, $self) {
 545     $errors = [];
 546     if (!empty($fields['this_year_value']) &&
 547       !empty($fields['other_year_value']) &&
 548       ($fields['this_year_value'] == $fields['other_year_value'])
 549     ) {
 550       $errors['other_year_value'] = ts("Value for filters 'This Year' and 'Other Years' can not be same.");
 551     }
 552     return $errors;
 553   }
 554
 555   public function postProcess() {
 556     // get ready with post process params
 557     $this->beginPostProcess();
 558     $this->fixReportParams();
 559
 560     $this->buildACLClause($this->_aliases['civicrm_contact']);
 561     $this->select();
 562     $this->where();
 563     $this->from();
 564     $this->customDataFrom();
 565     $this->groupBy();
 566
 567     $sql = NULL;
 568     $rows = [];
 569
 570     // build array of result based on column headers. This method also allows
 571     // modifying column headers before using it to build result set i.e $rows.
 572     $this->buildRows($sql, $rows);
 573
 574     // format result set.
 575     $this->formatDisplay($rows, FALSE);
 576
 577     // assign variables to templates
 578     $this->doTemplateAssignment($rows);
 579
 580     // do print / pdf / instance stuff if needed
 581     $this->endPostProcess($rows);
 582   }
 583
 584   public function fixReportParams() {
 585     if (!empty($this->_params['this_year_value'])) {
 586       $this->_referenceYear['this_year'] = $this->_params['this_year_value'];
 587     }
 588     if (!empty($this->_params['other_year_value'])) {
 589       $this->_referenceYear['other_year'] = $this->_params['other_year_value'];
 590     }
 591   }
 592
 593   /**
 594    * @param $sql
 595    * @param $rows
 596    */
 597   public function buildRows($sql, &$rows) {
 598     $contactIds = [];
 599
 600     $addWhere = '';
 601
 602     if (!empty($this->_referenceYear['other_year'])) {
 603       (CRM_Utils_Array::value('other_year_op', $this->_params) ==
 604         'calendar') ? $other_receive_date = 'YEAR (contri.receive_date)' : $other_receive_date = self::fiscalYearOffset('contri.receive_date');
 605       $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id NOT IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE  cont.id = contri.contact_id AND {$other_receive_date} = {$this->_referenceYear['other_year']} AND contri.is_test = 0 ) ";
 606     }
 607     if (!empty($this->_referenceYear['this_year'])) {
 608       (CRM_Utils_Array::value('this_year_op', $this->_params) ==
 609         'calendar') ? $receive_date = 'YEAR (contri.receive_date)' : $receive_date = self::fiscalYearOffset('contri.receive_date');
 610       $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$receive_date} = {$this->_referenceYear['this_year']} AND contri.is_test = 0 ) ";
 611     }
 612     $this->limit();
 613     $getContacts = "SELECT {$this->_aliases['civicrm_contact']}.id as cid, SUM({$this->_aliases['civicrm_contribution']}.total_amount) as civicrm_contribution_total_amount_sum {$this->_from} {$this->_where} {$addWhere} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_having} {$this->_limit}";
 614
 615     $dao = CRM_Core_DAO::executeQuery($getContacts);
 616
 617     while ($dao->fetch()) {
 618       $contactIds[] = $dao->cid;
 619     }
 620     $this->setPager();
 621
 622     $relationshipRows = [];
 623     if (empty($contactIds)) {
 624       return;
 625     }
 626
 627     $primaryContributions = $this->buildContributionRows($contactIds);
 628
 629     list($relationshipRows, $relatedContactIds) = $this->buildRelationshipRows($contactIds);
 630
 631     if (empty($relatedContactIds)) {
 632       $rows = $primaryContributions;
 633       return;
 634     }
 635
 636     $relatedContributions = $this->buildContributionRows($relatedContactIds);
 637
 638     $summaryYears = [];
 639     $summaryYears[] = "civicrm_upto_{$this->_yearStatisticsFrom}";
 640     $yearConter = $this->_yearStatisticsFrom;
 641     $yearConter++;
 642     while ($yearConter <= $this->_yearStatisticsTo) {
 643       $summaryYears[] = $yearConter;
 644       $yearConter++;
 645     }
 646     $summaryYears[] = 'aggregate_amount';
 647
 648     foreach ($primaryContributions as $cid => $primaryRow) {
 649       $row = $primaryRow;
 650       if (!isset($relationshipRows[$cid])) {
 651         $rows[$cid] = $row;
 652         continue;
 653       }
 654       $total = [];
 655       $total['civicrm_contact_sort_name'] = ts('Total');
 656       foreach ($summaryYears as $year) {
 657         $total[$year] = CRM_Utils_Array::value($year, $primaryRow, 0);
 658       }
 659
 660       $relatedContact = FALSE;
 661       $rows[$cid] = $row;
 662       foreach ($relationshipRows[$cid] as $relcid => $relRow) {
 663         if (!isset($relatedContributions[$relcid])) {
 664           continue;
 665         }
 666         $relatedContact = TRUE;
 667         $relatedRow = $relatedContributions[$relcid];
 668         foreach ($summaryYears as $year) {
 669           $total[$year] += CRM_Utils_Array::value($year, $relatedRow, 0);
 670         }
 671
 672         foreach (array_keys($this->_relationshipColumns) as $col) {
 673           if (!empty($relRow[$col])) {
 674             $relatedRow[$col] = $relRow[$col];
 675           }
 676         }
 677         $rows["{$cid}_{$relcid}"] = $relatedRow;
 678       }
 679       if ($relatedContact) {
 680         $rows["{$cid}_total"] = $total;
 681         $rows["{$cid}_bank"] = ['civicrm_contact_sort_name' => '&nbsp;'];
 682       }
 683     }
 684   }
 685
 686   /**
 687    * @param $contactIds
 688    *
 689    * @return array
 690    */
 691   public function buildContributionRows($contactIds) {
 692     $rows = [];
 693     if (empty($contactIds)) {
 694       return $rows;
 695     }
 696
 697     $sqlContribution = "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" .
 698       implode(',', $contactIds) .
 699       ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} ";
 700
 701     $dao = CRM_Core_DAO::executeQuery($sqlContribution);
 702     $contributionSum = 0;
 703     $yearcal = [];
 704     while ($dao->fetch()) {
 705       if (!$dao->civicrm_contact_id) {
 706         continue;
 707       }
 708
 709       foreach ($this->_columnHeaders as $key => $value) {
 710         if (property_exists($dao, $key)) {
 711           $rows[$dao->civicrm_contact_id][$key] = $dao->$key;
 712         }
 713       }
 714       if ($dao->civicrm_contribution_receive_date) {
 715         if ($dao->civicrm_contribution_receive_date >
 716           $this->_yearStatisticsFrom
 717         ) {
 718           $rows[$dao->civicrm_contact_id][$dao->civicrm_contribution_receive_date] = $dao->civicrm_contribution_total_amount;
 719         }
 720         else {
 721           if (!isset($rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"])) {
 722             $rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"] = 0;
 723           }
 724
 725           $rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"] += $dao->civicrm_contribution_total_amount;
 726         }
 727       }
 728
 729       if (!isset($rows[$dao->civicrm_contact_id]['aggregate_amount'])) {
 730         $rows[$dao->civicrm_contact_id]['aggregate_amount'] = 0;
 731       }
 732       $rows[$dao->civicrm_contact_id]['aggregate_amount'] += $dao->civicrm_contribution_total_amount;
 733     }
 734     return $rows;
 735   }
 736
 737   /**
 738    * @param $contactIds
 739    *
 740    * @return array
 741    */
 742   public function buildRelationshipRows($contactIds) {
 743     $relationshipRows = $relatedContactIds = [];
 744     if (empty($contactIds)) {
 745       return [$relationshipRows, $relatedContactIds];
 746     }
 747
 748     $relContactAlias = 'contact_relationship';
 749     $addRelSelect = '';
 750     if (!empty($this->_relationshipColumns)) {
 751       $addRelSelect = ', ' . implode(', ', $this->_relationshipColumns);
 752     }
 753     $sqlRelationship = "SELECT {$this->_aliases['civicrm_relationship']}.relationship_type_id as relationship_type_id, {$this->_aliases['civicrm_relationship']}.contact_id_a as contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b as contact_id_b {$addRelSelect} FROM civicrm_contact {$relContactAlias} {$this->_relationshipFrom} WHERE {$relContactAlias}.id IN (" .
 754       implode(',', $contactIds) .
 755       ") AND {$this->_aliases['civicrm_relationship']}.is_active = 1 {$this->_relationshipWhere} GROUP BY {$this->_aliases['civicrm_relationship']}.contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b, {$this->_aliases['civicrm_relationship']}.relationship_type_id";
 756     $relationshipTypes = CRM_Core_PseudoConstant::relationshipType();
 757
 758     $dao = CRM_Core_DAO::executeQuery($sqlRelationship);
 759     while ($dao->fetch()) {
 760       $row = [];
 761       foreach (array_keys($this->_relationshipColumns) as $rel_column) {
 762         $row[$rel_column] = $dao->$rel_column;
 763       }
 764       if (in_array($dao->contact_id_a, $contactIds)) {
 765         $row['civicrm_relationship_relationship_type_id'] = $relationshipTypes[$dao->relationship_type_id]['label_a_b'];
 766         $row['civicrm_relationship_contact_id'] = $dao->contact_id_b;
 767         $relationshipRows[$dao->contact_id_a][$dao->contact_id_b] = $row;
 768         $relatedContactIds[$dao->contact_id_b] = $dao->contact_id_b;
 769       }
 770       if (in_array($dao->contact_id_b, $contactIds)) {
 771         $row['civicrm_relationship_contact_id'] = $dao->contact_id_a;
 772         $row['civicrm_relationship_relationship_type_id'] = $relationshipTypes[$dao->relationship_type_id]['label_b_a'];
 773         $relationshipRows[$dao->contact_id_b][$dao->contact_id_a] = $row;
 774         $relatedContactIds[$dao->contact_id_a] = $dao->contact_id_a;
 775       }
 776     }
 777     return [$relationshipRows, $relatedContactIds];
 778   }
 779
 780   /**
 781    * Override "This Year" $op options
 782    * @param string $type
 783    * @param null $fieldName
 784    *
 785    * @return array
 786    */
 787   public function getOperationPair($type = "string", $fieldName = NULL) {
 788     if ($fieldName == 'this_year' || $fieldName == 'other_year') {
 789       return [
 790         'calendar' => ts('Is Calendar Year'),
 791         'fiscal' => ts('Fiscal Year Starting'),
 792       ];
 793     }
 794     return parent::getOperationPair($type, $fieldName);
 795   }
 796
 797   /**
 798    * Alter display of rows.
 799    *
 800    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 801    * such as rendering contacts as links.
 802    *
 803    * @param array $rows
 804    *   Rows generated by SQL, with an array for each row.
 805    */
 806   public function alterDisplay(&$rows) {
 807     if (empty($rows)) {
 808       return;
 809     }
 810
 811     $last_primary = NULL;
 812     foreach ($rows as $rowNum => $row) {
 813       // Highlight primary contact and amount row
 814       if (is_numeric($rowNum) ||
 815         ($last_primary && ($rowNum == "{$last_primary}_total"))
 816       ) {
 817         if (is_numeric($rowNum)) {
 818           $last_primary = $rowNum;
 819         }
 820         foreach ($row as $key => $value) {
 821           if ($key == 'civicrm_contact_id') {
 822             continue;
 823           }
 824           if (empty($value)) {
 825             $row[$key] = '';
 826             continue;
 827           }
 828
 829           if ($last_primary && ($rowNum == "{$last_primary}_total")) {
 830             $value = CRM_Utils_Money::format($value, ' ');
 831           }
 832           $row[$key] = '<strong>' . $value . '</strong>';
 833         }
 834         $rows[$rowNum] = $row;
 835       }
 836
 837       // Convert Display name into link
 838       if (!empty($row['civicrm_contact_sort_name']) &&
 839         !empty($row['civicrm_contact_id'])
 840       ) {
 841         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 842           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 843           $this->_absoluteUrl, $this->_id
 844         );
 845         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 846         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 847       }
 848
 849       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 850         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 851         $entryFound = TRUE;
 852       }
 853
 854       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 855       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 856
 857     }
 858   }
 859
 860 }