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     $this->_columns += array(
 252       'civicrm_financial_trxn' => array(
 253         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 254         'fields' => array(
 255           'card_type' => array(
 256             'title' => ts('Credit Card Type'),
 257             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type SEPARATOR ",")',
 258           ),
 259         ),
 260         'filters' => array(
 261           'card_type' => array(
 262             'title' => ts('Credit Card Type'),
 263             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 264             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type'),
 265             'default' => NULL,
 266             'type' => CRM_Utils_Type::T_STRING,
 267           ),
 268         ),
 269       ),
 270     );
 271
 272     // If we have a campaign, build out the relevant elements
 273     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 274       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 275         'title' => ts('Campaign'),
 276         'default' => 'false',
 277         'type' => CRM_Utils_Type::T_INT,
 278       );
 279       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 280         'title' => ts('Campaign'),
 281         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 282         'options' => $this->activeCampaigns,
 283         'type' => CRM_Utils_Type::T_INT,
 284       );
 285     }
 286
 287     $this->_groupFilter = TRUE;
 288     $this->_tagFilter = TRUE;
 289     parent::__construct();
 290   }
 291
 292   /**
 293    * Build select clause for a single field.
 294    *
 295    * @param string $tableName
 296    * @param string $tableKey
 297    * @param string $fieldName
 298    * @param string $field
 299    *
 300    * @return string
 301    */
 302   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 303     if ($fieldName == 'last_year_total_amount') {
 304       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
 305       $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $this->getLastYearColumnTitle();
 306       $this->_statFields[$this->getLastYearColumnTitle()] = "{$tableName}_{$fieldName}";
 307       return "SUM(IF(" . $this->whereClauseLastYear('contribution_civireport.receive_date') .  ", contribution_civireport.total_amount, 0)) as {$tableName}_{$fieldName}";
 308     }
 309     if ($fieldName == 'civicrm_life_time_total') {
 310       $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field;
 311       $this->_statFields[$field['title']] = "{$tableName}_{$fieldName}";
 312       return "SUM({$this->_aliases[$tableName]}.total_amount) as {$tableName}_{$fieldName}";
 313     }
 314     if ($fieldName == 'receive_date') {
 315       return self::fiscalYearOffset($field['dbAlias']) .
 316         " as {$tableName}_{$fieldName} ";
 317     }
 318     return FALSE;
 319   }
 320
 321   /**
 322    * Get the title for the last year column.
 323    */
 324   public function getLastYearColumnTitle() {
 325     if ($this->getYearFilterType() == 'calendar') {
 326       return ts('Total for ') . ($this->getCurrentYear() - 1);
 327     }
 328     return ts('Total for Fiscal Year ') . ($this->getCurrentYear() - 1) . '-' . ($this->getCurrentYear());
 329   }
 330
 331   /**
 332    * Construct from clause.
 333    *
 334    * On the first run we are creating a table of contacts to include in the report.
 335    *
 336    * Once contactTempTable is populated we should avoid using any further filters that affect
 337    * the contacts that should be visible.
 338    */
 339   public function from() {
 340     if (!empty($this->contactTempTable)) {
 341       $this->_from = "
 342         FROM  civicrm_contribution {$this->_aliases['civicrm_contribution']}
 343         INNER JOIN $this->contactTempTable restricted_contacts
 344           ON restricted_contacts.cid = {$this->_aliases['civicrm_contribution']}.contact_id
 345           AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 346         INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 347           ON restricted_contacts.cid = {$this->_aliases['civicrm_contact']}.id";
 348       if ($this->isTableSelected('civicrm_email')) {
 349         $this->_from .= "
 350           LEFT  JOIN civicrm_email  {$this->_aliases['civicrm_email']}
 351             ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
 352             AND {$this->_aliases['civicrm_email']}.is_primary = 1";
 353       }
 354       if ($this->isTableSelected('civicrm_phone')) {
 355         $this->_from .= "
 356           LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 357             ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id
 358             AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
 359       }
 360       $this->addAddressFromClause();
 361     }
 362     else {
 363       $this->setFromBase('civicrm_contact');
 364
 365       $this->_from .= " INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ";
 366       if (!$this->groupTempTable) {
 367         // The received_date index is better than the contribution_status_id index (fairly substantially).
 368         // But if we have already pre-filtered down to a group of contacts then we want that to be the
 369         // primary filter and the index hint will block that.
 370         $this->_from .= "USE index (received_date)";
 371       }
 372       $this->_from .= " ON {$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_contact']}.id
 373          AND {$this->_aliases['civicrm_contribution']}.is_test = 0
 374          AND " . $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date") . "
 375        {$this->_aclFrom}
 376        LEFT JOIN civicrm_contribution cont_exclude ON cont_exclude.contact_id = {$this->_aliases['civicrm_contact']}.id
 377          AND " . $this->whereClauseThisYear('cont_exclude.receive_date');
 378       $this->selectivelyAddLocationTablesJoinsToFilterQuery();
 379     }
 380
 381     // for credit card type
 382     $this->addFinancialTrxnFromClause();
 383   }
 384
 385   /**
 386    * Generate where clause.
 387    *
 388    * We are overriding this primarily for 'before-after' handling of the receive_date placeholder field.
 389    *
 390    * We call this twice. The first time we are generating a temp table and we want to do an IS NULL on the
 391    * join that draws in contributions from this year. The second time we are filtering elsewhere (contacts via
 392    * the temp table & contributions via selective addition of contributions in the select function).
 393    *
 394    * If lifetime total is NOT selected we can add a further filter here to possibly improve performance
 395    * but the benefit if unproven as yet.
 396    * $clause = $this->whereClauseLastYear("{$this->_aliases['civicrm_contribution']}.receive_date");
 397    *
 398    * @param array $field Field specifications
 399    * @param string $op Query operator (not an exact match to sql)
 400    * @param mixed $value
 401    * @param float $min
 402    * @param float $max
 403    *
 404    * @return null|string
 405    */
 406   public function whereClause(&$field, $op, $value, $min, $max) {
 407     if ($field['name'] == 'receive_date') {
 408       $clause = 1;
 409       if (empty($this->contactTempTable)) {
 410         $this->_whereClauses[] = "cont_exclude.id IS NULL";
 411       }
 412     }
 413     // Group filtering is already done so skip.
 414     elseif (!empty($field['group']) && $this->contactTempTable) {
 415       return 1;
 416     }
 417     else {
 418       $clause = parent::whereClause($field, $op, $value, $min, $max);
 419     }
 420     return $clause;
 421   }
 422
 423   /**
 424    * Generate where clause for last calendar year or fiscal year.
 425    *
 426    * @todo must be possible to re-use relative dates stuff.
 427    *
 428    * @param string $fieldName
 429    *
 430    * @return string
 431    */
 432   public function whereClauseLastYear($fieldName) {
 433     return "$fieldName BETWEEN '" . $this->getFirstDateOfPriorRange() . "' AND '" . $this->getLastDateOfPriorRange() . "'";
 434   }
 435
 436   /**
 437    * Generate where clause for last calendar year or fiscal year.
 438    *
 439    * @todo must be possible to re-use relative dates stuff.
 440    *
 441    * @param string $fieldName
 442    *
 443    * @param int $current_year
 444    * @return null|string
 445    */
 446   public function whereClauseThisYear($fieldName, $current_year = NULL) {
 447     return "$fieldName BETWEEN '" . $this->getFirstDateOfCurrentRange() . "' AND '" . $this->getLastDateOfCurrentRange() . "'";
 448   }
 449
 450
 451   /**
 452    * Get the year value for the current year.
 453    *
 454    * @return string
 455    */
 456   public function getCurrentYear() {
 457     return $this->_params['yid_value'];
 458   }
 459
 460   /**
 461    * Get the date time of the first date in the 'this year' range.
 462    *
 463    * @return string
 464    */
 465   public function getFirstDateOfCurrentRange() {
 466     $current_year = $this->getCurrentYear();
 467     if ($this->getYearFilterType() == 'calendar') {
 468       return "{$current_year }-01-01";
 469     }
 470     else {
 471       $fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart;
 472       return "{$current_year}-{$fiscalYear['M']}-{$fiscalYear['d']}";
 473     }
 474   }
 475
 476   /**
 477    * Get the year value for the current year.
 478    *
 479    * @return string
 480    */
 481   public function getYearFilterType() {
 482     return CRM_Utils_Array::value('yid_op', $this->_params, 'calendar');
 483   }
 484
 485   /**
 486    * Get the date time of the last date in the 'this year' range.
 487    *
 488    * @return string
 489    */
 490   public function getLastDateOfCurrentRange() {
 491     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfCurrentRange())));
 492   }
 493
 494   /**
 495    * Get the date time of the first date in the 'last year' range.
 496    *
 497    * @return string
 498    */
 499   public function getFirstDateOfPriorRange() {
 500     return date('YmdHis', strtotime('- 1 year', strtotime($this->getFirstDateOfCurrentRange())));
 501   }
 502
 503   /**
 504    * Get the date time of the last date in the 'last year' range.
 505    *
 506    * @return string
 507    */
 508   public function getLastDateOfPriorRange() {
 509     return date('YmdHis', strtotime('+ 1 year - 1 second', strtotime($this->getFirstDateOfPriorRange())));
 510   }
 511
 512
 513   public function groupBy() {
 514     $this->_groupBy = "GROUP BY  {$this->_aliases['civicrm_contribution']}.contact_id ";
 515     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id");
 516     $this->assign('chartSupported', TRUE);
 517   }
 518
 519   /**
 520    * @param $rows
 521    *
 522    * @return array
 523    */
 524   public function statistics(&$rows) {
 525
 526     $statistics = parent::statistics($rows);
 527     // The parent class does something odd where it adds an extra row to the count for the grand total.
 528     // Perhaps that works on some other report? But here it just seems odd.
 529     $this->countStat($statistics, count($rows));
 530     if (!empty($rows)) {
 531       if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_last_year_total_amount'])) {
 532         $statistics['counts']['civicrm_contribution_last_year_total_amount'] = array(
 533           'value' => $this->rollupRow['civicrm_contribution_last_year_total_amount'],
 534           'title' => $this->getLastYearColumnTitle(),
 535           'type' => CRM_Utils_Type::T_MONEY,
 536         );
 537
 538       }
 539       if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_civicrm_life_time_total'])) {
 540         $statistics['counts']['civicrm_contribution_civicrm_life_time_total'] = array(
 541           'value' => $this->rollupRow['civicrm_contribution_civicrm_life_time_total'],
 542           'title' => ts('Total LifeTime'),
 543           'type' => CRM_Utils_Type::T_MONEY,
 544         );
 545       }
 546       else {
 547         $select = "SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount,
 548           SUM(IF( " . $this->whereClauseLastYear('contribution_civireport.receive_date') .  ", contribution_civireport.total_amount, 0)) as last_year
 549          ";
 550         $sql = "{$select} {$this->_from} {$this->_where}";
 551         $dao = CRM_Core_DAO::executeQuery($sql);
 552         if ($dao->fetch()) {
 553           $statistics['counts']['amount'] = array(
 554             'value' => $dao->amount,
 555             'title' => ts('Total LifeTime'),
 556             'type' => CRM_Utils_Type::T_MONEY,
 557           );
 558           $statistics['counts']['last_year'] = array(
 559             'value' => $dao->last_year,
 560             'title' => $this->getLastYearColumnTitle(),
 561             'type' => CRM_Utils_Type::T_MONEY,
 562           );
 563         }
 564       }
 565     }
 566
 567     return $statistics;
 568   }
 569
 570   /**
 571    * This function is called by both the api (tests) and the UI.
 572    */
 573   public function beginPostProcessCommon() {
 574     $this->buildQuery();
 575     // @todo this acl has no test coverage and is very hard to test manually so could be fragile.
 576     $this->resetFormSqlAndWhereHavingClauses();
 577
 578     $this->contactTempTable = 'civicrm_report_temp_lybunt_c_' . date('Ymd_') . uniqid();
 579     $this->limit();
 580     $getContacts = "
 581       CREATE TEMPORARY TABLE $this->contactTempTable {$this->_databaseAttributes}
 582       SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where}
 583       GROUP BY {$this->_aliases['civicrm_contact']}.id";
 584     $this->executeReportQuery($getContacts);
 585     if (empty($this->_params['charts'])) {
 586       $this->setPager();
 587     }
 588
 589     // Reset where clauses to be regenerated in postProcess.
 590     $this->_whereClauses = array();
 591   }
 592
 593   /**
 594    * Build the report query.
 595    *
 596    * The issue we are hitting is that if we want to do group by & then ORDER BY we have to
 597    * wrap the query in an outer query with the order by - otherwise the group by takes precedent.
 598    * This is an issue when we want to group by contact but order by the maximum aggregate donation.
 599    *
 600    * @param bool $applyLimit
 601    *
 602    * @return string
 603    */
 604   public function buildQuery($applyLimit = TRUE) {
 605     $this->buildGroupTempTable();
 606     $this->buildPermissionClause();
 607     // Calling where & select before FROM allows us to build temp tables to use in from.
 608     $this->where();
 609     $this->select();
 610     $this->from();
 611     $this->customDataFrom(empty($this->contactTempTable));
 612
 613     $this->groupBy();
 614     $this->orderBy();
 615     $this->getPermissionedFTQuery($this);
 616     $limitFilter = '';
 617
 618     // order_by columns not selected for display need to be included in SELECT
 619     // This differs from parent in that we are getting those not in order by rather than not in
 620     // sections, as we need to adapt to our contact group by.
 621     $unselectedSectionColumns = array_diff_key($this->_orderByFields, $this->getSelectColumns());
 622     foreach ($unselectedSectionColumns as $alias => $section) {
 623       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 624     }
 625
 626     if ($applyLimit && empty($this->_params['charts'])) {
 627       $this->limit();
 628     }
 629
 630     $sql = "{$this->_select} {$this->_from} {$this->_where} {$limitFilter} {$this->_groupBy} {$this->_having} {$this->_rollup}";
 631
 632     if (!empty($this->_orderByArray)) {
 633       $this->_orderBy = str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY ISNULL(civicrm_contribution_contact_id), " . implode(', ', $this->_orderByArray));
 634       $this->_orderBy = str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy);
 635       foreach ($this->_orderByFields as $field) {
 636         $this->_orderBy = str_replace($field['dbAlias'], $field['tplField'], $this->_orderBy);
 637       }
 638       $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
 639       $sql = "SELECT SQL_CALC_FOUND_ROWS  * FROM ( $sql ) as inner_query {$this->_orderBy} $this->_limit";
 640     }
 641
 642     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
 643     $this->addToDeveloperTab($sql);
 644
 645     return $sql;
 646   }
 647
 648   /**
 649    * Reset the form sql and where / having clause arrays.
 650    *
 651    * We do an early iteration of the report queries to generate the temp table.
 652    *
 653    * However, that iteration populates the sql for the developer tab,
 654    * the whereClauses & the havingClauses and they are populated again in the normal
 655    * report flow. This is harmless but confusing - ie. the where clause winds up repeating
 656    * the same filters and the dev tab shows the query twice, so we rest them.
 657    */
 658   protected function resetFormSqlAndWhereHavingClauses() {
 659     $this->sql = '';
 660     $this->_havingClauses = array();
 661     $this->_whereClauses = array();
 662     $this->sqlArray = array();
 663   }
 664
 665   /**
 666    * @param $rows
 667    */
 668   public function buildChart(&$rows) {
 669
 670     $graphRows = array();
 671     $count = 0;
 672     $display = array();
 673
 674     $current_year = $this->_params['yid_value'];
 675     $previous_year = $current_year - 1;
 676     $interval[$previous_year] = $previous_year;
 677     $interval['life_time'] = 'Life Time';
 678
 679     foreach ($rows as $key => $row) {
 680       $display['life_time'] = CRM_Utils_Array::value('life_time', $display) +
 681         $row['civicrm_life_time_total'];
 682       $display[$previous_year] = CRM_Utils_Array::value($previous_year, $display) + $row[$previous_year];
 683     }
 684
 685     $config = CRM_Core_Config::Singleton();
 686     $graphRows['value'] = $display;
 687     $chartInfo = array(
 688       'legend' => ts('Lybunt Report'),
 689       'xname' => ts('Year'),
 690       'yname' => ts('Amount (%1)', array(1 => $config->defaultCurrency)),
 691     );
 692     if ($this->_params['charts']) {
 693       // build chart.
 694       CRM_Utils_OpenFlashChart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
 695       $this->assign('chartType', $this->_params['charts']);
 696     }
 697   }
 698
 699   /**
 700    * Alter display of rows.
 701    *
 702    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 703    * such as rendering contacts as links.
 704    *
 705    * @param array $rows
 706    *   Rows generated by SQL, with an array for each row.
 707    */
 708   public function alterDisplay(&$rows) {
 709     $entryFound = FALSE;
 710
 711     foreach ($rows as $rowNum => $row) {
 712       //Convert Display name into link
 713       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 714         array_key_exists('civicrm_contribution_contact_id', $row)
 715       ) {
 716         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 717           'reset=1&force=1&id_op=eq&id_value=' .
 718           $row['civicrm_contribution_contact_id'],
 719           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 720         );
 721         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 722         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 723         $entryFound = TRUE;
 724       }
 725
 726       // convert campaign_id to campaign title
 727       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 728         if ($value = $row['civicrm_contribution_campaign_id']) {
 729           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 730           $entryFound = TRUE;
 731         }
 732       }
 733       // Display 'Yes' if the email is on hold (leave blank for no so it stands out better).
 734       if (array_key_exists('civicrm_email_on_hold', $row)) {
 735         $rows[$rowNum]['civicrm_email_on_hold'] = $row['civicrm_email_on_hold'] ? ts('Yes') : '';
 736         $entryFound = TRUE;
 737       }
 738
 739       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 740       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, 'List all contribution(s)') ? TRUE : $entryFound;
 741       //handle gender
 742       if (array_key_exists('civicrm_contact_gender_id', $row)) {
 743         if ($value = $row['civicrm_contact_gender_id']) {
 744           $gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
 745           $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value];
 746         }
 747         $entryFound = TRUE;
 748       }
 749
 750       // display birthday in the configured custom format
 751       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 752         $birthDate = $row['civicrm_contact_birth_date'];
 753         if ($birthDate) {
 754           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 755         }
 756         $entryFound = TRUE;
 757       }
 758
 759       if (!empty($row['civicrm_financial_trxn_card_type'])) {
 760         $rows[$rowNum]['civicrm_financial_trxn_card_type'] = $this->getLabels($row['civicrm_financial_trxn_card_type'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type');
 761         $entryFound = TRUE;
 762       }
 763
 764       // skip looking further in rows, if first row itself doesn't
 765       // have the column we need
 766       if (!$entryFound) {
 767         break;
 768       }
 769     }
 770   }
 771
 772   /**
 773    * Override "This Year" $op options
 774    * @param string $type
 775    * @param null $fieldName
 776    *
 777    * @return array
 778    */
 779   public function getOperationPair($type = "string", $fieldName = NULL) {
 780     if ($fieldName == 'yid') {
 781       return array(
 782         'calendar' => ts('Is Calendar Year'),
 783         'fiscal' => ts('Fiscal Year Starting'),
 784       );
 785     }
 786     return parent::getOperationPair($type, $fieldName);
 787   }
 788
 789 }