CRM/Report/Form/Contribute/History.php

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