CRM/Report/Form/Contribute/Lybunt.php

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