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