CRM/Report/Form/Contribute/Lybunt.php

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