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