CRM/Report/Form/Contribute/History.php

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