CRM/Report/Form/Contribute/History.php

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