CRM/Report/Form/Contribute/Lybunt.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_Lybunt extends CRM_Report_Form {
  34
  35   protected $_charts = array(
  36     '' => 'Tabular',
  37     'barChart' => 'Bar Chart',
  38     'pieChart' => 'Pie Chart',
  39   );
  40
  41   /**
  42    * This is the report that links will lead to.
  43    *
  44    * It is a bit problematic to use contribute/detail for anything other than a single contact
  45    * as the filtering from this report does not carry over to that report.
  46    *
  47    * @var array
  48    */
  49   public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
  50
  51   protected $lifeTime_from = NULL;
  52   protected $lifeTime_where = NULL;
  53   protected $_customGroupExtends = array(
  54     'Contact',
  55     'Individual',
  56     'Household',
  57     'Organization',
  58   );
  59
  60   /**
  61    * Table containing list of contact IDs.
  62    *
  63    * @var string
  64    */
  65   protected $contactTempTable = '';
  66
  67   /**
  68    * This report has been optimised for group filtering.
  69    *
  70    * CRM-19170
  71    *
  72    * @var bool
  73    */
  74   protected $groupFilterNotOptimised = FALSE;
  75
  76   /**
  77    * Class constructor.
  78    */
  79   public function __construct() {
  80     $this->optimisedForOnlyFullGroupBy = FALSE;
  81     $this->_rollup = 'WITH ROLLUP';
  82     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  83     $yearsInPast = 10;
  84     $yearsInFuture = 1;
  85     $date = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, $yearsInFuture);
  86     $count = $date['maxYear'];
  87     while ($date['minYear'] <= $count) {
  88       $optionYear[$date['minYear']] = $date['minYear'];
  89       $date['minYear']++;
  90     }
  91
  92     // Check if CiviCampaign is a) enabled and b) has active campaigns
  93     $config = CRM_Core_Config::singleton();
  94     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  95     if ($campaignEnabled) {
  96       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  97       $this->activeCampaigns = $getCampaigns['campaigns'];
  98       asort($this->activeCampaigns);
  99     }
 100
 101     $this->_columns = array(
 102       'civicrm_contact' => array(
 103         'dao' => 'CRM_Contact_DAO_Contact',
 104         'grouping' => 'contact-field',
 105         'fields' => $this->getBasicContactFields(),
 106         'order_bys' => array(
 107           'sort_name' => array(
 108             'title' => ts('Last Name, First Name'),
 109             'default' => '0',
 110             'default_order' => 'ASC',
 111           ),
 112           'first_name' => array(
 113             'name' => 'first_name',
 114             'title' => ts('First Name'),
 115           ),
 116           'gender_id' => array(
 117             'name' => 'gender_id',
 118             'title' => ts('Gender'),
 119           ),
 120           'birth_date' => array(
 121             'name' => 'birth_date',
 122             'title' => ts('Birth Date'),
 123           ),
 124           'contact_type' => array(
 125             'title' => ts('Contact Type'),
 126           ),
 127           'contact_sub_type' => array(
 128             'title' => ts('Contact Subtype'),
 129           ),
 130         ),
 131         'filters' => array(
 132           'sort_name' => array(
 133             'title' => ts('Donor Name'),
 134             'operator' => 'like',
 135           ),
 136           'id' => array(
 137             'title' => ts('Contact ID'),
 138             'no_display' => TRUE,
 139           ),
 140           'gender_id' => array(
 141             'title' => ts('Gender'),
 142             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 143             'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
 144           ),
 145           'birth_date' => array(
 146             'title' => ts('Birth Date'),
 147             'operatorType' => CRM_Report_Form::OP_DATE,
 148           ),
 149           'contact_type' => array(
 150             'title' => ts('Contact Type'),
 151           ),
 152           'contact_sub_type' => array(
 153             'title' => ts('Contact Subtype'),
 154           ),
 155           'is_deceased' => array(),
 156           'do_not_phone' => array(),
 157           'do_not_email' => array(),
 158           'do_not_sms' => array(),
 159           'do_not_mail' => array(),
 160           'is_opt_out' => array(),
 161         ),
 162       ),
 163       'civicrm_line_item' => array(
 164         'dao' => 'CRM_Price_DAO_LineItem',
 165       ),
 166       'civicrm_email' => array(
 167         'dao' => 'CRM_Core_DAO_Email',
 168         'grouping' => 'contact-field',
 169         'fields' => array(
 170           'email' => array(
 171             'title' => ts('Email'),
 172             'default' => TRUE,
 173           ),
 174           'on_hold' => array(
 175             'title' => ts('Email on hold'),
 176           ),
 177         ),
 178       ),
 179       'civicrm_phone' => array(
 180         'dao' => 'CRM_Core_DAO_Phone',
 181         'grouping' => 'contact-field',
 182         'fields' => array(
 183           'phone' => array(
 184             'title' => ts('Phone'),
 185             'default' => TRUE,
 186           ),
 187         ),
 188       ),
 189     );
 190     $this->_columns += $this->addAddressFields(FALSE);
 191     $this->_columns += array(
 192       'civicrm_contribution' => array(
 193         'dao' => 'CRM_Contribute_DAO_Contribution',
 194         'fields' => array(
 195           'contact_id' => array(
 196             'title' => ts('contactId'),
 197             'no_display' => TRUE,
 198             'required' => TRUE,
 199             'no_repeat' => TRUE,
 200           ),
 201           'receive_date' => array(
 202             'title' => ts('Year'),
 203             'no_display' => TRUE,
 204             'required' => TRUE,
 205             'no_repeat' => TRUE,
 206           ),
 207           'last_year_total_amount' => array(
 208             'title' => ts('Last Year Total'),
 209             'default' => TRUE,
 210             'type' => CRM_Utils_Type::T_MONEY,
 211             'required' => TRUE,
 212           ),
 213           'civicrm_life_time_total' => array(
 214             'title' => ts('Lifetime Total'),
 215             'default' => TRUE,
 216             'type' => CRM_Utils_Type::T_MONEY,
 217             'statistics' => array('sum' => ts('Lifetime total')),
 218           ),
 219         ),
 220         'filters' => array(
 221           'yid' => array(
 222             'name' => 'receive_date',
 223             'title' => ts('This Year'),
 224             'operatorType' => CRM_Report_Form::OP_SELECT,
 225             'options' => $optionYear,
 226             'default' => date('Y'),
 227             'type' => CRM_Utils_Type::T_INT,
 228           ),
 229           'financial_type_id' => array(
 230             'title' => ts('Financial Type'),
 231             'type' => CRM_Utils_Type::T_INT,
 232             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 233             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 234           ),
 235           'contribution_status_id' => array(
 236             'title' => ts('Contribution Status'),
 237             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 238             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 239             'default' => array('1'),
 240           ),
 241         ),
 242         'order_bys' => array(
 243           'last_year_total_amount' => array(
 244             'title' => ts('Total amount last year'),
 245             'default' => '1',
 246             'default_weight' => '0',
 247             'default_order' => 'DESC',
 248           ),
 249         ),
 250       ),
 251     );
 252     $this->_columns += array(
 253       'civicrm_financial_trxn' => array(
 254         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 255         'fields' => array(
 256           'card_type_id' => array(
 257             'title' => ts('Credit Card Type'),
 258             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 259           ),
 260         ),
 261         'filters' => array(
 262           'card_type_id' => array(
 263             'title' => ts('Credit Card Type'),
 264             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 265             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 266             'default' => NULL,
 267             'type' => CRM_Utils_Type::T_STRING,
 268           ),
 269         ),
 270       ),
 271     );
 272
 273     // If we have a campaign, build out the relevant elements
 274     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 275       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 276         'title' => ts('Campaign'),
 277         'default' => 'false',
 278         'type' => CRM_Utils_Type::T_INT,
 279       );
 280       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 281         'title' => ts('Campaign'),
 282         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 283         'options' => $this->activeCampaigns,
 284         'type' => CRM_Utils_Type::T_INT,
 285       );
 286     }
 287
 288     $this->_groupFilter = TRUE;
 289     $this->_tagFilter = TRUE;
 290     parent::__construct();
 291   }
 292
 293   /**
 294    * Build select clause for a single field.
 295    *
 296    * @param string $tableName
 297    * @param string $tableKey
 298    * @param string $fieldName
 299    * @param string $field
 300    *
 301    * @return string
 302    */
 303   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 304     if ($fieldName == 'last_year_total_amount') {
 305       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
 306       $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $this->getLastYearColumnTitle();
 307       $this->_statFields[$this->getLastYearColumnTitle()] = "{$tableName}_{$fieldName}";
 308       return "SUM(IF(" . $this->whereClauseLastYear('contribution_civireport.receive_date') .  ", contribution_civireport.total_amount, 0)) as {$tableName}_{$fieldName}";
 309     }
 310     if ($fieldName == 'civicrm_life_time_total') {
 311       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
 312       $this->_statFields[$field['title']] = "{$tableName}_{$fieldName}";
 313       return "SUM({$this->_aliases[$tableName]}.total_amount) as {$tableName}_{$fieldName}";
 314     }
 315     if ($fieldName == 'receive_date') {
 316       return self::fiscalYearOffset($field['dbAlias']) .
 317         " as {$tableName}_{$fieldName} ";
 318     }
 319     return FALSE;
 320   }
 321
 322   /**
 323    * Get the title for the last year column.
 324    */
 325   public function getLastYearColumnTitle() {
 326     if ($this->getYearFilterType() == 'calendar') {
 327       return ts('Total for ') . ($this->getCurrentYear() - 1);
 328     }
 329     return ts('Total for Fiscal Year ') . ($this->getCurrentYear() - 1) . '-' . ($this->getCurrentYear());
 330   }
 331
 332   /**
 333    * Construct from clause.
 334    *
 335    * On the first run we are creating a table of contacts to include in the report.
 336    *
 337    * Once contactTempTable is populated we should avoid using any further filters that affect
 338    * the contacts that should be visible.
 339    */
 340   public function from() {
 341     if (!empty($this->contactTempTable)) {
 342       $this->_from = "
 343         FROM  civicrm_contribution {$this->_aliases['civicrm_contribution']}
 344         INNER JOIN $this->contactTempTable restricted_contacts
 345           ON restricted_contacts.cid = {$this->_aliases['civicrm_contribution']}.contact_id
 346           AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 347         INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 348           ON restricted_contacts.cid = {$this->_aliases['civicrm_contact']}.id";
 349
 350       $this->joinAddressFromContact();
 351       $this->joinPhoneFromContact();
 352       $this->joinEmailFromContact();
 353     }
 354     else {
 355       $this->setFromBase('civicrm_contact');
 356
 357       $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ";
 358       if (!$this->groupTempTable) {
 359         // The received_date index is better than the contribution_status_id index (fairly substantially).
 360         // But if we have already pre-filtered down to a group of contacts then we want that to be the
 361         // primary filter and the index hint will block that.
 362         $this->_from .= "USE index (received_date)";
 363       }
 364       $this->_from .= " ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id
 365          AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 366          AND " . $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date") . "
 367        {$this->_aclFrom}
 368        LEFT JOIN civicrm_contribution cont_exclude ON cont_exclude.contact_id = {$this->_aliases['civicrm_contact']}.id
 369          AND " . $this->whereClauseThisYear('cont_exclude.receive_date');
 370       $this->selectivelyAddLocationTablesJoinsToFilterQuery();
 371     }
 372
 373     // for credit card type
 374     $this->addFinancialTrxnFromClause();
 375   }
 376
 377   /**
 378    * Generate where clause.
 379    *
 380    * We are overriding this primarily for 'before-after' handling of the receive_date placeholder field.
 381    *
 382    * We call this twice. The first time we are generating a temp table and we want to do an IS NULL on the
 383    * join that draws in contributions from this year. The second time we are filtering elsewhere (contacts via
 384    * the temp table & contributions via selective addition of contributions in the select function).
 385    *
 386    * If lifetime total is NOT selected we can add a further filter here to possibly improve performance
 387    * but the benefit if unproven as yet.
 388    * $clause = $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date");
 389    *
 390    * @param array $field Field specifications
 391    * @param string $op Query operator (not an exact match to sql)
 392    * @param mixed $value
 393    * @param float $min
 394    * @param float $max
 395    *
 396    * @return null|string
 397    */
 398   public function whereClause(&$field, $op, $value, $min, $max) {
 399     if ($field['name'] == 'receive_date') {
 400       $clause = 1;
 401       if (empty($this->contactTempTable)) {
 402         $this->_whereClauses[] = "cont_exclude.id IS NULL";
 403       }
 404     }
 405     // Group filtering is already done so skip.
 406     elseif (!empty($field['group']) && $this->contactTempTable) {
 407       return 1;
 408     }
 409     else {
 410       $clause = parent::whereClause($field, $op, $value, $min, $max);
 411     }
 412     return $clause;
 413   }
 414
 415   /**
 416    * Generate where clause for last calendar year or fiscal year.
 417    *
 418    * @todo must be possible to re-use relative dates stuff.
 419    *
 420    * @param string $fieldName
 421    *
 422    * @return string
 423    */
 424   public function whereClauseLastYear($fieldName) {
 425     return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND '" . $this->getLastDateOfPriorRange() . "'";
 426   }
 427
 428   /**
 429    * Generate where clause for last calendar year or fiscal year.
 430    *
 431    * @todo must be possible to re-use relative dates stuff.
 432    *
 433    * @param string $fieldName
 434    *
 435    * @param int $current_year
 436    * @return null|string
 437    */
 438   public function whereClauseThisYear($fieldName, $current_year = NULL) {
 439     return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "' AND '" . $this->getLastDateOfCurrentRange() . "'";
 440   }
 441
 442
 443   /**
 444    * Get the year value for the current year.
 445    *
 446    * @return string
 447    */
 448   public function getCurrentYear() {
 449     return $this->_params['yid_value'];
 450   }
 451
 452   /**
 453    * Get the date time of the first date in the 'this year' range.
 454    *
 455    * @return string
 456    */
 457   public function getFirstDateOfCurrentRange() {
 458     $current_year = $this->getCurrentYear();
 459     if ($this->getYearFilterType() == 'calendar') {
 460       return "{$current_year }-01-01";
 461     }
 462     else {
 463       $fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart;
 464       return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
 465     }
 466   }
 467
 468   /**
 469    * Get the year value for the current year.
 470    *
 471    * @return string
 472    */
 473   public function getYearFilterType() {
 474     return CRM_Utils_Array::value('yid_op', $this->_params, 'calendar');
 475   }
 476
 477   /**
 478    * Get the date time of the last date in the 'this year' range.
 479    *
 480    * @return string
 481    */
 482   public function getLastDateOfCurrentRange() {
 483     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfCurrentRange())));
 484   }
 485
 486   /**
 487    * Get the date time of the first date in the 'last year' range.
 488    *
 489    * @return string
 490    */
 491   public function getFirstDateOfPriorRange() {
 492     return date('YmdHis', strtotime('- 1 year', strtotime($this->getFirstDateOfCurrentRange())));
 493   }
 494
 495   /**
 496    * Get the date time of the last date in the 'last year' range.
 497    *
 498    * @return string
 499    */
 500   public function getLastDateOfPriorRange() {
 501     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfPriorRange())));
 502   }
 503
 504
 505   public function groupBy() {
 506     $this->_groupBy = "GROUP BY  {$this->_aliases['civicrm_contribution']}.contact_id ";
 507     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id");
 508     $this->assign('chartSupported', TRUE);
 509   }
 510
 511   /**
 512    * @param $rows
 513    *
 514    * @return array
 515    */
 516   public function statistics(&$rows) {
 517
 518     $statistics = parent::statistics($rows);
 519     // The parent class does something odd where it adds an extra row to the count for the grand total.
 520     // Perhaps that works on some other report? But here it just seems odd.
 521     $this->countStat($statistics, count($rows));
 522     if (!empty($rows)) {
 523       if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_last_year_total_amount'])) {
 524         $statistics['counts']['civicrm_contribution_last_year_total_amount'] = array(
 525           'value' => $this->rollupRow['civicrm_contribution_last_year_total_amount'],
 526           'title' => $this->getLastYearColumnTitle(),
 527           'type' => CRM_Utils_Type::T_MONEY,
 528         );
 529
 530       }
 531       if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_civicrm_life_time_total'])) {
 532         $statistics['counts']['civicrm_contribution_civicrm_life_time_total'] = array(
 533           'value' => $this->rollupRow['civicrm_contribution_civicrm_life_time_total'],
 534           'title' => ts('Total LifeTime'),
 535           'type' => CRM_Utils_Type::T_MONEY,
 536         );
 537       }
 538       else {
 539         $select = "SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount,
 540           SUM(IF( " . $this->whereClauseLastYear('contribution_civireport.receive_date') .  ", contribution_civireport.total_amount, 0)) as last_year
 541          ";
 542         $sql = "{$select} {$this->_from} {$this->_where}";
 543         $dao = CRM_Core_DAO::executeQuery($sql);
 544         if ($dao->fetch()) {
 545           $statistics['counts']['amount'] = array(
 546             'value' => $dao->amount,
 547             'title' => ts('Total LifeTime'),
 548             'type' => CRM_Utils_Type::T_MONEY,
 549           );
 550           $statistics['counts']['last_year'] = array(
 551             'value' => $dao->last_year,
 552             'title' => $this->getLastYearColumnTitle(),
 553             'type' => CRM_Utils_Type::T_MONEY,
 554           );
 555         }
 556       }
 557     }
 558
 559     return $statistics;
 560   }
 561
 562   /**
 563    * This function is called by both the api (tests) and the UI.
 564    */
 565   public function beginPostProcessCommon() {
 566     $this->buildQuery();
 567     // @todo this acl has no test coverage and is very hard to test manually so could be fragile.
 568     $this->resetFormSqlAndWhereHavingClauses();
 569
 570     $this->contactTempTable = 'civicrm_report_temp_lybunt_c_' . date('Ymd_') . uniqid();
 571     $this->limit();
 572     $getContacts = "
 573       CREATE TEMPORARY TABLE $this->contactTempTable {$this->_databaseAttributes}
 574       SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where}
 575       GROUP BY {$this->_aliases['civicrm_contact']}.id";
 576     $this->executeReportQuery($getContacts);
 577     if (empty($this->_params['charts'])) {
 578       $this->setPager();
 579     }
 580
 581     // Reset where clauses to be regenerated in postProcess.
 582     $this->_whereClauses = array();
 583   }
 584
 585   /**
 586    * Build the report query.
 587    *
 588    * The issue we are hitting is that if we want to do group by & then ORDER BY we have to
 589    * wrap the query in an outer query with the order by - otherwise the group by takes precedent.
 590    * This is an issue when we want to group by contact but order by the maximum aggregate donation.
 591    *
 592    * @param bool $applyLimit
 593    *
 594    * @return string
 595    */
 596   public function buildQuery($applyLimit = TRUE) {
 597     $this->buildGroupTempTable();
 598     $this->buildPermissionClause();
 599     // Calling where & select before FROM allows us to build temp tables to use in from.
 600     $this->where();
 601     $this->select();
 602     $this->from();
 603     $this->customDataFrom(empty($this->contactTempTable));
 604
 605     $this->groupBy();
 606     $this->orderBy();
 607     $limitFilter = '';
 608
 609     // order_by columns not selected for display need to be included in SELECT
 610     // This differs from parent in that we are getting those not in order by rather than not in
 611     // sections, as we need to adapt to our contact group by.
 612     $unselectedSectionColumns = array_diff_key($this->_orderByFields, $this->getSelectColumns());
 613     foreach ($unselectedSectionColumns as $alias => $section) {
 614       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 615     }
 616
 617     if ($applyLimit && empty($this->_params['charts'])) {
 618       $this->limit();
 619     }
 620
 621     $sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}";
 622
 623     if (!empty($this->_orderByArray)) {
 624       $this->_orderBy = str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY ISNULL(civicrm_contribution_contact_id), " . implode(', ', $this->_orderByArray));
 625       $this->_orderBy = str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy);
 626       foreach ($this->_orderByFields as $field) {
 627         $this->_orderBy = str_replace($field['dbAlias'], $field['tplField'], $this->_orderBy);
 628       }
 629       $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
 630       $sql = "SELECT SQL_CALC_FOUND_ROWS  * FROM ( $sql ) as inner_query {$this->_orderBy} $this->_limit";
 631     }
 632
 633     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
 634     $this->addToDeveloperTab($sql);
 635
 636     return $sql;
 637   }
 638
 639   /**
 640    * Reset the form sql and where / having clause arrays.
 641    *
 642    * We do an early iteration of the report queries to generate the temp table.
 643    *
 644    * However, that iteration populates the sql for the developer tab,
 645    * the whereClauses & the havingClauses and they are populated again in the normal
 646    * report flow. This is harmless but confusing - ie. the where clause winds up repeating
 647    * the same filters and the dev tab shows the query twice, so we rest them.
 648    */
 649   protected function resetFormSqlAndWhereHavingClauses() {
 650     $this->sql = '';
 651     $this->_havingClauses = array();
 652     $this->_whereClauses = array();
 653     $this->sqlArray = array();
 654   }
 655
 656   /**
 657    * @param $rows
 658    */
 659   public function buildChart(&$rows) {
 660
 661     $graphRows = array();
 662     $count = 0;
 663     $display = array();
 664
 665     $current_year = $this->_params['yid_value'];
 666     $previous_year = $current_year - 1;
 667     $interval[$previous_year] = $previous_year;
 668     $interval['life_time'] = 'Life Time';
 669
 670     foreach ($rows as $key => $row) {
 671       $display['life_time'] = CRM_Utils_Array::value('life_time', $display) +
 672         $row['civicrm_life_time_total'];
 673       $display[$previous_year] = CRM_Utils_Array::value($previous_year, $display) + $row[$previous_year];
 674     }
 675
 676     $config = CRM_Core_Config::Singleton();
 677     $graphRows['value'] = $display;
 678     $chartInfo = array(
 679       'legend' => ts('Lybunt Report'),
 680       'xname' => ts('Year'),
 681       'yname' => ts('Amount (%1)', array(1 => $config->defaultCurrency)),
 682     );
 683     if ($this->_params['charts']) {
 684       // build chart.
 685       CRM_Utils_OpenFlashChart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
 686       $this->assign('chartType', $this->_params['charts']);
 687     }
 688   }
 689
 690   /**
 691    * Alter display of rows.
 692    *
 693    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 694    * such as rendering contacts as links.
 695    *
 696    * @param array $rows
 697    *   Rows generated by SQL, with an array for each row.
 698    */
 699   public function alterDisplay(&$rows) {
 700     $entryFound = FALSE;
 701
 702     foreach ($rows as $rowNum => $row) {
 703       //Convert Display name into link
 704       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 705         array_key_exists('civicrm_contribution_contact_id', $row)
 706       ) {
 707         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 708           'reset=1&force=1&id_op=eq&id_value=' .
 709           $row['civicrm_contribution_contact_id'],
 710           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 711         );
 712         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 713         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 714         $entryFound = TRUE;
 715       }
 716
 717       // convert campaign_id to campaign title
 718       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 719         if ($value = $row['civicrm_contribution_campaign_id']) {
 720           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 721           $entryFound = TRUE;
 722         }
 723       }
 724       // Display 'Yes' if the email is on hold (leave blank for no so it stands out better).
 725       if (array_key_exists('civicrm_email_on_hold', $row)) {
 726         $rows[$rowNum]['civicrm_email_on_hold'] = $row['civicrm_email_on_hold'] ? ts('Yes') : '';
 727         $entryFound = TRUE;
 728       }
 729
 730       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 731       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 732       //handle gender
 733       if (array_key_exists('civicrm_contact_gender_id', $row)) {
 734         if ($value = $row['civicrm_contact_gender_id']) {
 735           $gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
 736           $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
 737         }
 738         $entryFound = TRUE;
 739       }
 740
 741       // display birthday in the configured custom format
 742       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 743         $birthDate = $row['civicrm_contact_birth_date'];
 744         if ($birthDate) {
 745           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 746         }
 747         $entryFound = TRUE;
 748       }
 749
 750       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 751         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 752         $entryFound = TRUE;
 753       }
 754
 755       // skip looking further in rows, if first row itself doesn't
 756       // have the column we need
 757       if (!$entryFound) {
 758         break;
 759       }
 760     }
 761   }
 762
 763   /**
 764    * Override "This Year" $op options
 765    * @param string $type
 766    * @param null $fieldName
 767    *
 768    * @return array
 769    */
 770   public function getOperationPair($type = "string", $fieldName = NULL) {
 771     if ($fieldName == 'yid') {
 772       return array(
 773         'calendar' => ts('Is Calendar Year'),
 774         'fiscal' => ts('Fiscal Year Starting'),
 775       );
 776     }
 777     return parent::getOperationPair($type, $fieldName);
 778   }
 779
 780 }