CRM/Report/Form.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  * Class CRM_Report_Form
  30  */
  31 class CRM_Report_Form extends CRM_Core_Form {
  32   const ROW_COUNT_LIMIT = 50;
  33
  34   /**
  35    * Operator types - used for displaying filter elements
  36    */
  37   const
  38     OP_INT = 1,
  39     OP_STRING = 2,
  40     OP_DATE = 4,
  41     OP_DATETIME = 5,
  42     OP_FLOAT = 8,
  43     OP_SELECT = 64,
  44     OP_MULTISELECT = 65,
  45     OP_MULTISELECT_SEPARATOR = 66,
  46     OP_MONTH = 128,
  47     OP_ENTITYREF = 256;
  48
  49   /**
  50    * The id of the report instance
  51    *
  52    * @var integer
  53    */
  54   protected $_id;
  55
  56   /**
  57    * The id of the report template
  58    *
  59    * @var integer;
  60    */
  61   protected $_templateID;
  62
  63   /**
  64    * The report title
  65    *
  66    * @var string
  67    */
  68   protected $_title;
  69   protected $_noFields = FALSE;
  70
  71   /**
  72    * The set of all columns in the report. An associative array
  73    * with column name as the key and attributes as the value
  74    *
  75    * @var array
  76    */
  77   protected $_columns = array();
  78
  79   /**
  80    * The set of filters in the report
  81    *
  82    * @var array
  83    */
  84   protected $_filters = array();
  85
  86   /**
  87    * The set of optional columns in the report
  88    *
  89    * @var array
  90    */
  91   protected $_options = array();
  92
  93   /**
  94    * By default most reports hide contact id.
  95    * Setting this to true makes it available
  96    */
  97   protected $_exposeContactID = TRUE;
  98
  99   /**
 100    * Set of statistic fields
 101    *
 102    * @var array
 103    */
 104   protected $_statFields = array();
 105
 106   /**
 107    * Set of statistics data
 108    *
 109    * @var array
 110    */
 111   protected $_statistics = array();
 112
 113   /**
 114    * List of fields not to be repeated during display
 115    *
 116    * @var array
 117    */
 118   protected $_noRepeats = array();
 119
 120   /**
 121    * List of fields not to be displayed
 122    *
 123    * @var array
 124    */
 125   protected $_noDisplay = array();
 126
 127   /**
 128    * Object type that a custom group extends
 129    *
 130    * @var null
 131    */
 132   protected $_customGroupExtends = NULL;
 133   protected $_customGroupExtendsJoin = array();
 134   protected $_customGroupFilters = TRUE;
 135   protected $_customGroupGroupBy = FALSE;
 136   protected $_customGroupJoin = 'LEFT JOIN';
 137
 138   /**
 139    * Build tags filter
 140    */
 141   protected $_tagFilter = FALSE;
 142
 143   /**
 144    * specify entity table for tags filter
 145    */
 146   protected $_tagFilterTable = 'civicrm_contact';
 147
 148   /**
 149    * Build groups filter.
 150    *
 151    * @var bool
 152    */
 153   protected $_groupFilter = FALSE;
 154
 155   /**
 156    * Navigation fields
 157    *
 158    * @var array
 159    */
 160   public $_navigation = array();
 161
 162   public $_drilldownReport = array();
 163
 164   /**
 165    * Array of tabs to display on report.
 166    *
 167    * E.g we define the tab title, the tpl and the tab-specific part of the css or  html link.
 168    *
 169    *  $this->tabs['OrderBy'] = array(
 170    *    'title' => ts('Sorting'),
 171    *    'tpl' => 'OrderBy',
 172    *    'div_label' => 'order-by',
 173    *  );
 174    *
 175    * @var array
 176    */
 177   protected $tabs = array();
 178
 179   /**
 180    * Should we add paging.
 181    *
 182    * @var bool
 183    */
 184   protected $addPaging = TRUE;
 185
 186   /**
 187    * An attribute for checkbox/radio form field layout
 188    *
 189    * @var array
 190    */
 191   protected $_fourColumnAttribute = array(
 192     '</td><td width="25%">',
 193     '</td><td width="25%">',
 194     '</td><td width="25%">',
 195     '</tr><tr><td>',
 196   );
 197
 198   protected $_force = 1;
 199
 200   protected $_params = NULL;
 201   protected $_formValues = NULL;
 202   protected $_instanceValues = NULL;
 203
 204   protected $_instanceForm = FALSE;
 205   protected $_criteriaForm = FALSE;
 206
 207   protected $_instanceButtonName = NULL;
 208   protected $_createNewButtonName = NULL;
 209   protected $_printButtonName = NULL;
 210   protected $_pdfButtonName = NULL;
 211   protected $_csvButtonName = NULL;
 212   protected $_groupButtonName = NULL;
 213   protected $_chartButtonName = NULL;
 214   protected $_csvSupported = TRUE;
 215   protected $_add2groupSupported = TRUE;
 216   protected $_groups = NULL;
 217   protected $_grandFlag = FALSE;
 218   protected $_rowsFound = NULL;
 219   protected $_selectAliases = array();
 220   protected $_rollup = NULL;
 221
 222   /**
 223    * @var array
 224    */
 225   protected $_aliases = array();
 226
 227   /**
 228    * @var string
 229    */
 230   protected $_where;
 231
 232   /**
 233    * @var string
 234    */
 235   protected $_from;
 236
 237   /**
 238    * SQL Limit clause
 239    * @var  string
 240    */
 241   protected $_limit = NULL;
 242
 243   /**
 244    * This can be set to specify a limit to the number of rows
 245    * Since it is currently envisaged as part of the api usage it is only being applied
 246    * when $_output mode is not 'html' or 'group' so as not to have to interpret / mess with that part
 247    * of the code (see limit() fn.
 248    *
 249    * @var integer
 250    */
 251   protected $_limitValue = NULL;
 252
 253   /**
 254    * This can be set to specify row offset
 255    * See notes on _limitValue
 256    * @var integer
 257    */
 258   protected $_offsetValue = NULL;
 259   /**
 260    * @var null
 261    */
 262   protected $_sections = NULL;
 263   protected $_autoIncludeIndexedFieldsAsOrderBys = 0;
 264   protected $_absoluteUrl = FALSE;
 265
 266   /**
 267    * Flag to indicate if result-set is to be stored in a class variable which could be retrieved using getResultSet() method.
 268    *
 269    * @var boolean
 270    */
 271   protected $_storeResultSet = FALSE;
 272
 273   /**
 274    * When _storeResultSet Flag is set use this var to store result set in form of array
 275    *
 276    * @var boolean
 277    */
 278   protected $_resultSet = array();
 279
 280   /**
 281    * To what frequency group-by a date column
 282    *
 283    * @var array
 284    */
 285   protected $_groupByDateFreq = array(
 286     'MONTH' => 'Month',
 287     'YEARWEEK' => 'Week',
 288     'QUARTER' => 'Quarter',
 289     'YEAR' => 'Year',
 290   );
 291
 292   /**
 293    * Variables to hold the acl inner join and where clause
 294    */
 295   protected $_aclFrom = NULL;
 296   protected $_aclWhere = NULL;
 297
 298   /**
 299    * Array of DAO tables having columns included in SELECT or ORDER BY clause.
 300    *
 301    * Where has also been added to this although perhaps the 'includes both' array should have a different name.
 302    *
 303    * @var array
 304    */
 305   protected $_selectedTables;
 306
 307   /**
 308    * Array of DAO tables having columns included in WHERE or HAVING clause
 309    *
 310    * @var array
 311    */
 312   protected $filteredTables;
 313
 314   /**
 315    * Output mode e.g 'print', 'csv', 'pdf'.
 316    *
 317    * @var string
 318    */
 319   protected $_outputMode;
 320
 321   /**
 322    * Format of any chart in use.
 323    *
 324    * (it's unclear if this could be merged with outputMode at this stage)
 325    *
 326    * @var
 327    */
 328   protected $_format;
 329
 330   public $_having = NULL;
 331   public $_select = NULL;
 332   public $_selectClauses = array();
 333   public $_columnHeaders = array();
 334   public $_orderBy = NULL;
 335   public $_orderByFields = array();
 336   public $_orderByArray = array();
 337   public $_groupBy = NULL;
 338   public $_whereClauses = array();
 339   public $_havingClauses = array();
 340
 341   /**
 342    * DashBoardRowCount Dashboard row count
 343    * @var Integer
 344    */
 345   public $_dashBoardRowCount;
 346
 347   /**
 348    * Is this being called without a form controller (ie. the report is being render outside the normal form
 349    * - e.g the api is retrieving the rows
 350    * @var boolean
 351    */
 352   public $noController = FALSE;
 353
 354   /**
 355    * Variable to hold the currency alias
 356    */
 357   protected $_currencyColumn = NULL;
 358
 359   /**
 360    * @var string
 361    */
 362   protected $_interval;
 363
 364   /**
 365    * @var bool
 366    */
 367   protected $_sendmail;
 368
 369   /**
 370    * @var int
 371    */
 372   protected $_chartId;
 373
 374   /**
 375    * @var int
 376    */
 377   public $_section;
 378
 379   /**
 380    * @var string Report description.
 381    */
 382   public $_description;
 383
 384   /**
 385    * @var bool Is an address field selected.
 386    *   This was intended to determine if the address table should be joined in
 387    *   The isTableSelected function is now preferred for this purpose
 388    */
 389   protected $_addressField;
 390
 391   /**
 392    * @var bool Is an email field selected.
 393    *   This was intended to determine if the email table should be joined in
 394    *   The isTableSelected function is now preferred for this purpose
 395    */
 396   protected $_emailField;
 397
 398   /**
 399    * @var bool Is a phone field selected.
 400    *   This was intended to determine if the phone table should be joined in
 401    *   The isTableSelected function is now preferred for this purpose
 402    */
 403   protected $_phoneField;
 404
 405   /**
 406    * @var bool Create new report instance? (or update existing) on save.
 407    */
 408   protected $_createNew;
 409
 410   /**
 411    *  When a grand total row has calculated the status we pop it off to here.
 412    *
 413    * This allows us to access it from the stats function and avoid recalculating.
 414    */
 415   protected $rollupRow = array();
 416
 417   /**
 418    * SQL being run in this report.
 419    *
 420    * The sql in the report is stored in this variable in order to be displayed on the developer tab.
 421    *
 422    * @var string
 423    */
 424
 425   protected $sql;
 426
 427   /**
 428    * SQL being run in this report as an array.
 429    *
 430    * The sql in the report is stored in this variable in order to be returned to api & test calls.
 431    *
 432    * @var string
 433    */
 434
 435   protected $sqlArray;
 436   /**
 437    * Class constructor.
 438    */
 439   public function __construct() {
 440     parent::__construct();
 441
 442     $this->addClass('crm-report-form');
 443
 444     if ($this->_tagFilter) {
 445       $this->buildTagFilter();
 446     }
 447     if ($this->_exposeContactID) {
 448       if (array_key_exists('civicrm_contact', $this->_columns)) {
 449         $this->_columns['civicrm_contact']['fields']['exposed_id'] = array(
 450           'name' => 'id',
 451           'title' => 'Contact ID',
 452           'no_repeat' => TRUE,
 453         );
 454       }
 455     }
 456
 457     if ($this->_groupFilter) {
 458       $this->buildGroupFilter();
 459     }
 460
 461     // Get all custom groups
 462     $allGroups = CRM_Core_PseudoConstant::get('CRM_Core_DAO_CustomField', 'custom_group_id');
 463
 464     // Get the custom groupIds for which the user has VIEW permission
 465     // If the user has 'access all custom data' permission, we'll leave $permCustomGroupIds empty
 466     // and addCustomDataToColumns() will allow access to all custom groups.
 467     $permCustomGroupIds = array();
 468     if (!CRM_Core_Permission::check('access all custom data')) {
 469       $permCustomGroupIds = CRM_ACL_API::group(CRM_Core_Permission::VIEW, NULL, 'civicrm_custom_group', $allGroups, NULL);
 470       // do not allow custom data for reports if user doesn't have
 471       // permission to access custom data.
 472       if (!empty($this->_customGroupExtends) && empty($permCustomGroupIds)) {
 473         $this->_customGroupExtends = array();
 474       }
 475     }
 476
 477     // merge custom data columns to _columns list, if any
 478     $this->addCustomDataToColumns(TRUE, $permCustomGroupIds);
 479
 480     // add / modify display columns, filters ..etc
 481     CRM_Utils_Hook::alterReportVar('columns', $this->_columns, $this);
 482
 483     //assign currencyColumn variable to tpl
 484     $this->assign('currencyColumn', $this->_currencyColumn);
 485   }
 486
 487   /**
 488    * Shared pre-process function.
 489    *
 490    * If overriding preProcess function this should still be called.
 491    *
 492    * @throws \Exception
 493    */
 494   public function preProcessCommon() {
 495     $this->_force
 496       = CRM_Utils_Request::retrieve(
 497       'force',
 498       'Boolean',
 499       CRM_Core_DAO::$_nullObject
 500       );
 501
 502     $this->_dashBoardRowCount
 503       = CRM_Utils_Request::retrieve(
 504       'rowCount',
 505       'Integer',
 506       CRM_Core_DAO::$_nullObject
 507       );
 508
 509     $this->_section = CRM_Utils_Request::retrieve('section', 'Integer', CRM_Core_DAO::$_nullObject);
 510
 511     $this->assign('section', $this->_section);
 512     CRM_Core_Region::instance('page-header')->add(array(
 513       'markup' => sprintf('<!-- Report class: [%s] -->', htmlentities(get_class($this))),
 514     ));
 515     if (!$this->noController) {
 516       $this->setID($this->get('instanceId'));
 517
 518       if (!$this->_id) {
 519         $this->setID(CRM_Report_Utils_Report::getInstanceID());
 520         if (!$this->_id) {
 521           $this->setID(CRM_Report_Utils_Report::getInstanceIDForPath());
 522         }
 523       }
 524
 525       // set qfkey so that pager picks it up and use it in the "Next > Last >>" links.
 526       // FIXME: Note setting it in $_GET doesn't work, since pager generates link based on QUERY_STRING
 527       $_SERVER['QUERY_STRING'] .= "&qfKey={$this->controller->_key}";
 528     }
 529
 530     if ($this->_id) {
 531       $this->assign('instanceId', $this->_id);
 532       $params = array('id' => $this->_id);
 533       $this->_instanceValues = array();
 534       CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 535         $params,
 536         $this->_instanceValues
 537       );
 538       if (empty($this->_instanceValues)) {
 539         CRM_Core_Error::fatal("Report could not be loaded.");
 540       }
 541       $this->_title = $this->_instanceValues['title'];
 542       if (!empty($this->_instanceValues['permission']) &&
 543         (!(CRM_Core_Permission::check($this->_instanceValues['permission']) ||
 544           CRM_Core_Permission::check('administer Reports')
 545         ))
 546       ) {
 547         CRM_Utils_System::permissionDenied();
 548         CRM_Utils_System::civiExit();
 549       }
 550
 551       $formValues = CRM_Utils_Array::value('form_values', $this->_instanceValues);
 552       if ($formValues) {
 553         $this->_formValues = unserialize($formValues);
 554       }
 555       else {
 556         $this->_formValues = NULL;
 557       }
 558
 559       $this->setOutputMode();
 560
 561       if ($this->_outputMode == 'copy') {
 562         $this->_createNew = TRUE;
 563         $this->_params = $this->_formValues;
 564         $this->_params['view_mode'] = 'criteria';
 565         $this->_params['title'] = ts('(copy)') . $this->getTitle();
 566         // Do not pass go. Do not collect another chance to re-run the same query.
 567         CRM_Report_Form_Instance::postProcess($this);
 568       }
 569
 570       // lets always do a force if reset is found in the url.
 571       // Hey why not? see CRM-17225 for more about this. The use of reset to be force is historical for reasons stated
 572       // in the comment line above these 2.
 573       if (!empty($_REQUEST['reset'])
 574           && !in_array(CRM_Utils_Request::retrieve('output', 'String'), array('save', 'criteria'))) {
 575         $this->_force = 1;
 576       }
 577
 578       // set the mode
 579       $this->assign('mode', 'instance');
 580     }
 581     elseif (!$this->noController) {
 582       list($optionValueID, $optionValue) = CRM_Report_Utils_Report::getValueIDFromUrl();
 583       $instanceCount = CRM_Report_Utils_Report::getInstanceCount($optionValue);
 584       if (($instanceCount > 0) && $optionValueID) {
 585         $this->assign('instanceUrl',
 586           CRM_Utils_System::url('civicrm/report/list',
 587             "reset=1&ovid=$optionValueID"
 588           )
 589         );
 590       }
 591       if ($optionValueID) {
 592         $this->_description = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', $optionValueID, 'description');
 593       }
 594
 595       // set the mode
 596       $this->assign('mode', 'template');
 597     }
 598
 599     // lets display the Report Settings section
 600     $this->_instanceForm = $this->_force || $this->_id || (!empty($_POST));
 601
 602     // Do not display Report Settings section if administer Reports permission is absent OR
 603     // if report instance is reserved and administer reserved reports absent
 604     if (!CRM_Core_Permission::check('administer Reports') ||
 605       ($this->_instanceValues['is_reserved'] &&
 606         !CRM_Core_Permission::check('administer reserved reports'))
 607     ) {
 608       $this->_instanceForm = FALSE;
 609     }
 610
 611     $this->assign('criteriaForm', FALSE);
 612     // Display Report Criteria section if user has access Report Criteria OR administer Reports AND report instance is not reserved
 613     if (CRM_Core_Permission::check('administer Reports') ||
 614       CRM_Core_Permission::check('access Report Criteria')
 615     ) {
 616       if (!$this->_instanceValues['is_reserved'] ||
 617         CRM_Core_Permission::check('administer reserved reports')
 618       ) {
 619         $this->assign('criteriaForm', TRUE);
 620         $this->_criteriaForm = TRUE;
 621       }
 622     }
 623
 624     $this->_instanceButtonName = $this->getButtonName('submit', 'save');
 625     $this->_createNewButtonName = $this->getButtonName('submit', 'next');
 626     $this->_printButtonName = $this->getButtonName('submit', 'print');
 627     $this->_pdfButtonName = $this->getButtonName('submit', 'pdf');
 628     $this->_csvButtonName = $this->getButtonName('submit', 'csv');
 629     $this->_groupButtonName = $this->getButtonName('submit', 'group');
 630     $this->_chartButtonName = $this->getButtonName('submit', 'chart');
 631   }
 632
 633   /**
 634    * Add bread crumb.
 635    */
 636   public function addBreadCrumb() {
 637     $breadCrumbs
 638       = array(
 639         array(
 640           'title' => ts('Report Templates'),
 641           'url' => CRM_Utils_System::url('civicrm/admin/report/template/list', 'reset=1'),
 642         ),
 643       );
 644
 645     CRM_Utils_System::appendBreadCrumb($breadCrumbs);
 646   }
 647
 648   /**
 649    * Pre process function.
 650    *
 651    * Called prior to build form.
 652    */
 653   public function preProcess() {
 654     $this->preProcessCommon();
 655
 656     if (!$this->_id) {
 657       $this->addBreadCrumb();
 658     }
 659
 660     foreach ($this->_columns as $tableName => $table) {
 661       // set alias
 662       if (!isset($table['alias'])) {
 663         $this->_columns[$tableName]['alias'] = substr($tableName, 8) .
 664           '_civireport';
 665       }
 666       else {
 667         $this->_columns[$tableName]['alias'] = $table['alias'] . '_civireport';
 668       }
 669
 670       $this->_aliases[$tableName] = $this->_columns[$tableName]['alias'];
 671
 672       $daoOrBaoName = NULL;
 673       // higher preference to bao object
 674       if (array_key_exists('bao', $table)) {
 675         $daoOrBaoName = $table['bao'];
 676         $expFields = $daoOrBaoName::exportableFields();
 677       }
 678       elseif (array_key_exists('dao', $table)) {
 679         $daoOrBaoName = $table['dao'];
 680         $expFields = $daoOrBaoName::export();
 681       }
 682       else {
 683         $expFields = array();
 684       }
 685
 686       $doNotCopy = array('required');
 687
 688       $fieldGroups = array('fields', 'filters', 'group_bys', 'order_bys');
 689       foreach ($fieldGroups as $fieldGrp) {
 690         if (!empty($table[$fieldGrp]) && is_array($table[$fieldGrp])) {
 691           foreach ($table[$fieldGrp] as $fieldName => $field) {
 692             // $name is the field name used to reference the BAO/DAO export fields array
 693             $name = isset($field['name']) ? $field['name'] : $fieldName;
 694
 695             // Sometimes the field name key in the BAO/DAO export fields array is
 696             // different from the actual database field name.
 697             // Unset $field['name'] so that actual database field name can be obtained
 698             // from the BAO/DAO export fields array.
 699             unset($field['name']);
 700
 701             if (array_key_exists($name, $expFields)) {
 702               foreach ($doNotCopy as $dnc) {
 703                 // unset the values we don't want to be copied.
 704                 unset($expFields[$name][$dnc]);
 705               }
 706               if (empty($field)) {
 707                 $this->_columns[$tableName][$fieldGrp][$fieldName] = $expFields[$name];
 708               }
 709               else {
 710                 foreach ($expFields[$name] as $property => $val) {
 711                   if (!array_key_exists($property, $field)) {
 712                     $this->_columns[$tableName][$fieldGrp][$fieldName][$property] = $val;
 713                   }
 714                 }
 715               }
 716             }
 717
 718             // fill other vars
 719             if (!empty($field['no_repeat'])) {
 720               $this->_noRepeats[] = "{$tableName}_{$fieldName}";
 721             }
 722             if (!empty($field['no_display'])) {
 723               $this->_noDisplay[] = "{$tableName}_{$fieldName}";
 724             }
 725
 726             // set alias = table-name, unless already set
 727             $alias = isset($field['alias']) ? $field['alias'] : (
 728               isset($this->_columns[$tableName]['alias']) ? $this->_columns[$tableName]['alias'] : $tableName
 729             );
 730             $this->_columns[$tableName][$fieldGrp][$fieldName]['alias'] = $alias;
 731
 732             // set name = fieldName, unless already set
 733             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['name'])) {
 734               $this->_columns[$tableName][$fieldGrp][$fieldName]['name'] = $name;
 735             }
 736
 737             // set dbAlias = alias.name, unless already set
 738             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'])) {
 739               $this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias']
 740                 = $alias . '.' .
 741                 $this->_columns[$tableName][$fieldGrp][$fieldName]['name'];
 742             }
 743
 744             // a few auto fills for filters
 745             if ($fieldGrp == 'filters') {
 746               // fill operator types
 747               if (!array_key_exists('operatorType', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 748                 switch (CRM_Utils_Array::value('type', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 749                   case CRM_Utils_Type::T_MONEY:
 750                   case CRM_Utils_Type::T_FLOAT:
 751                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
 752                     break;
 753
 754                   case CRM_Utils_Type::T_INT:
 755                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
 756                     break;
 757
 758                   case CRM_Utils_Type::T_DATE:
 759                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
 760                     break;
 761
 762                   case CRM_Utils_Type::T_BOOLEAN:
 763                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
 764                     if (!array_key_exists('options', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 765                       $this->_columns[$tableName][$fieldGrp][$fieldName]['options']
 766                         = array(
 767                           '' => ts('Any'),
 768                           '0' => ts('No'),
 769                           '1' => ts('Yes'),
 770                         );
 771                     }
 772                     break;
 773
 774                   default:
 775                     if ($daoOrBaoName &&
 776                       array_key_exists('pseudoconstant', $this->_columns[$tableName][$fieldGrp][$fieldName])
 777                     ) {
 778                       // with multiple options operator-type is generally multi-select
 779                       $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
 780                       if (!array_key_exists('options', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 781                         // fill options
 782                         $this->_columns[$tableName][$fieldGrp][$fieldName]['options'] = CRM_Core_PseudoConstant::get($daoOrBaoName, $fieldName);
 783                       }
 784                     }
 785                     break;
 786                 }
 787               }
 788             }
 789           }
 790         }
 791       }
 792
 793       // copy filters to a separate handy variable
 794       if (array_key_exists('filters', $table)) {
 795         $this->_filters[$tableName] = $this->_columns[$tableName]['filters'];
 796       }
 797
 798       if (array_key_exists('group_bys', $table)) {
 799         $groupBys[$tableName] = $this->_columns[$tableName]['group_bys'];
 800       }
 801
 802       if (array_key_exists('fields', $table)) {
 803         $reportFields[$tableName] = $this->_columns[$tableName]['fields'];
 804       }
 805     }
 806
 807     if ($this->_force) {
 808       $this->setDefaultValues(FALSE);
 809     }
 810
 811     CRM_Report_Utils_Get::processFilter($this->_filters, $this->_defaults);
 812     CRM_Report_Utils_Get::processGroupBy($groupBys, $this->_defaults);
 813     CRM_Report_Utils_Get::processFields($reportFields, $this->_defaults);
 814     CRM_Report_Utils_Get::processChart($this->_defaults);
 815
 816     if ($this->_force) {
 817       $this->_formValues = $this->_defaults;
 818       $this->postProcess();
 819     }
 820   }
 821
 822   /**
 823    * Set default values.
 824    *
 825    * @param bool $freeze
 826    *
 827    * @return array
 828    */
 829   public function setDefaultValues($freeze = TRUE) {
 830     $freezeGroup = array();
 831
 832     // FIXME: generalizing form field naming conventions would reduce
 833     // Lots of lines below.
 834     foreach ($this->_columns as $tableName => $table) {
 835       if (array_key_exists('fields', $table)) {
 836         foreach ($table['fields'] as $fieldName => $field) {
 837           if (empty($field['no_display'])) {
 838             if (isset($field['required'])) {
 839               // set default
 840               $this->_defaults['fields'][$fieldName] = 1;
 841
 842               if ($freeze) {
 843                 // find element object, so that we could use quickform's freeze method
 844                 // for required elements
 845                 $obj = $this->getElementFromGroup("fields", $fieldName);
 846                 if ($obj) {
 847                   $freezeGroup[] = $obj;
 848                 }
 849               }
 850             }
 851             elseif (isset($field['default'])) {
 852               $this->_defaults['fields'][$fieldName] = $field['default'];
 853             }
 854           }
 855         }
 856       }
 857
 858       if (array_key_exists('group_bys', $table)) {
 859         foreach ($table['group_bys'] as $fieldName => $field) {
 860           if (isset($field['default'])) {
 861             if (!empty($field['frequency'])) {
 862               $this->_defaults['group_bys_freq'][$fieldName] = 'MONTH';
 863             }
 864             $this->_defaults['group_bys'][$fieldName] = $field['default'];
 865           }
 866         }
 867       }
 868       if (array_key_exists('filters', $table)) {
 869         foreach ($table['filters'] as $fieldName => $field) {
 870           if (isset($field['default'])) {
 871             if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
 872             ) {
 873               if (is_array($field['default'])) {
 874                 $this->_defaults["{$fieldName}_from"] = CRM_Utils_Array::value('from', $field['default']);
 875                 $this->_defaults["{$fieldName}_to"] = CRM_Utils_Array::value('to', $field['default']);
 876                 $this->_defaults["{$fieldName}_relative"] = 0;
 877               }
 878               else {
 879                 $this->_defaults["{$fieldName}_relative"] = $field['default'];
 880               }
 881             }
 882             else {
 883               if ((CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_INT) && is_array($field['default'])) {
 884                 $this->_defaults["{$fieldName}_min"] = CRM_Utils_Array::value('min', $field['default']);
 885                 $this->_defaults["{$fieldName}_max"] = CRM_Utils_Array::value('max', $field['default']);
 886               }
 887               $this->_defaults["{$fieldName}_value"] = $field['default'];
 888             }
 889           }
 890           //assign default value as "in" for multiselect
 891           //operator, To freeze the select element
 892           if (CRM_Utils_Array::value('operatorType', $field) ==
 893             CRM_Report_Form::OP_MULTISELECT
 894           ) {
 895             $this->_defaults["{$fieldName}_op"] = 'in';
 896           }
 897           if (CRM_Utils_Array::value('operatorType', $field) ==
 898             CRM_Report_Form::OP_ENTITYREF
 899           ) {
 900             $this->_defaults["{$fieldName}_op"] = 'in';
 901           }
 902           elseif (CRM_Utils_Array::value('operatorType', $field) ==
 903             CRM_Report_Form::OP_MULTISELECT_SEPARATOR
 904           ) {
 905             $this->_defaults["{$fieldName}_op"] = 'mhas';
 906           }
 907           elseif ($op = CRM_Utils_Array::value('default_op', $field)) {
 908             $this->_defaults["{$fieldName}_op"] = $op;
 909           }
 910         }
 911       }
 912
 913       if (
 914         empty($this->_formValues['order_bys']) &&
 915         (array_key_exists('order_bys', $table) &&
 916         is_array($table['order_bys']))
 917       ) {
 918         if (!array_key_exists('order_bys', $this->_defaults)) {
 919           $this->_defaults['order_bys'] = array();
 920         }
 921         foreach ($table['order_bys'] as $fieldName => $field) {
 922           if (!empty($field['default']) || !empty($field['default_order']) ||
 923             CRM_Utils_Array::value('default_is_section', $field) ||
 924             !empty($field['default_weight'])
 925           ) {
 926             $order_by = array(
 927               'column' => $fieldName,
 928               'order' => CRM_Utils_Array::value('default_order', $field, 'ASC'),
 929               'section' => CRM_Utils_Array::value('default_is_section', $field, 0),
 930             );
 931
 932             if (!empty($field['default_weight'])) {
 933               $this->_defaults['order_bys'][(int) $field['default_weight']] = $order_by;
 934             }
 935             else {
 936               array_unshift($this->_defaults['order_bys'], $order_by);
 937             }
 938           }
 939         }
 940       }
 941
 942       foreach ($this->_options as $fieldName => $field) {
 943         if (isset($field['default'])) {
 944           $this->_defaults['options'][$fieldName] = $field['default'];
 945         }
 946       }
 947     }
 948
 949     if (!empty($this->_submitValues)) {
 950       $this->preProcessOrderBy($this->_submitValues);
 951     }
 952     else {
 953       $this->preProcessOrderBy($this->_defaults);
 954     }
 955
 956     // lets finish freezing task here itself
 957     if (!empty($freezeGroup)) {
 958       foreach ($freezeGroup as $elem) {
 959         $elem->freeze();
 960       }
 961     }
 962
 963     if ($this->_formValues) {
 964       $this->_defaults = array_merge($this->_defaults, $this->_formValues);
 965     }
 966
 967     if ($this->_instanceValues) {
 968       $this->_defaults = array_merge($this->_defaults, $this->_instanceValues);
 969     }
 970
 971     CRM_Report_Form_Instance::setDefaultValues($this, $this->_defaults);
 972
 973     return $this->_defaults;
 974   }
 975
 976   /**
 977    * Get element from group.
 978    *
 979    * @param string $group
 980    * @param string $grpFieldName
 981    *
 982    * @return bool
 983    */
 984   public function getElementFromGroup($group, $grpFieldName) {
 985     $eleObj = $this->getElement($group);
 986     foreach ($eleObj->_elements as $index => $obj) {
 987       if ($grpFieldName == $obj->_attributes['name']) {
 988         return $obj;
 989       }
 990     }
 991     return FALSE;
 992   }
 993
 994   /**
 995    * Setter for $_params.
 996    *
 997    * @param array $params
 998    */
 999   public function setParams($params) {
1000     $this->_params = $params;
1001   }
1002
1003   /**
1004    * Setter for $_id.
1005    *
1006    * @param int $instanceID
1007    */
1008   public function setID($instanceID) {
1009     $this->_id = $instanceID;
1010   }
1011
1012   /**
1013    * Setter for $_force.
1014    *
1015    * @param bool $isForce
1016    */
1017   public function setForce($isForce) {
1018     $this->_force = $isForce;
1019   }
1020
1021   /**
1022    * Setter for $_limitValue.
1023    *
1024    * @param int $_limitValue
1025    */
1026   public function setLimitValue($_limitValue) {
1027     $this->_limitValue = $_limitValue;
1028   }
1029
1030   /**
1031    * Setter for $_offsetValue.
1032    *
1033    * @param int $_offsetValue
1034    */
1035   public function setOffsetValue($_offsetValue) {
1036     $this->_offsetValue = $_offsetValue;
1037   }
1038
1039   /**
1040    * Setter for $addPaging.
1041    *
1042    * @param bool $value
1043    */
1044   public function setAddPaging($value) {
1045     $this->addPaging = $value;
1046   }
1047
1048   /**
1049    * Getter for $_defaultValues.
1050    *
1051    * @return array
1052    */
1053   public function getDefaultValues() {
1054     return $this->_defaults;
1055   }
1056
1057   /**
1058    * Add columns to report.
1059    */
1060   public function addColumns() {
1061     $options = array();
1062     $colGroups = NULL;
1063     foreach ($this->_columns as $tableName => $table) {
1064       if (array_key_exists('fields', $table)) {
1065         foreach ($table['fields'] as $fieldName => $field) {
1066           $groupTitle = '';
1067           if (empty($field['no_display'])) {
1068             foreach (array('table', 'field') as $var) {
1069               if (!empty(${$var}['grouping'])) {
1070                 if (!is_array(${$var}['grouping'])) {
1071                   $tableName = ${$var}['grouping'];
1072                 }
1073                 else {
1074                   $tableName = array_keys(${$var}['grouping']);
1075                   $tableName = $tableName[0];
1076                   $groupTitle = array_values(${$var}['grouping']);
1077                   $groupTitle = $groupTitle[0];
1078                 }
1079               }
1080             }
1081
1082             if (!$groupTitle && isset($table['group_title'])) {
1083               $groupTitle = $table['group_title'];
1084               // Having a group_title is secret code for being a custom group
1085               // which cryptically translates to needing an accordion.
1086               // here we make that explicit.
1087               $colGroups[$tableName]['use_accordian_for_field_selection'] = TRUE;
1088             }
1089
1090             $colGroups[$tableName]['fields'][$fieldName] = CRM_Utils_Array::value('title', $field);
1091             if ($groupTitle && empty($colGroups[$tableName]['group_title'])) {
1092               $colGroups[$tableName]['group_title'] = $groupTitle;
1093             }
1094             $options[$fieldName] = CRM_Utils_Array::value('title', $field);
1095           }
1096         }
1097       }
1098     }
1099
1100     $this->addCheckBox("fields", ts('Select Columns'), $options, NULL,
1101       NULL, NULL, NULL, $this->_fourColumnAttribute, TRUE
1102     );
1103     if (!empty($colGroups)) {
1104       $this->tabs['FieldSelection'] = array(
1105         'title' => ts('Columns'),
1106         'tpl' => 'FieldSelection',
1107         'div_label' => 'col-groups',
1108       );
1109
1110       // Note this assignment is only really required in buildForm. It is being 'over-called'
1111       // to reduce risk of being missed due to overridden functions.
1112       $this->assign('tabs', $this->tabs);
1113     }
1114
1115     $this->assign('colGroups', $colGroups);
1116   }
1117
1118   /**
1119    * Add filters to report.
1120    */
1121   public function addFilters() {
1122     $filters = $filterGroups = array();
1123     $count = 1;
1124
1125     foreach ($this->_filters as $table => $attributes) {
1126       if (isset($this->_columns[$table]['group_title'])) {
1127         // The presence of 'group_title' is secret code for 'is_a_custom_table'
1128         // which magically means to 'display in an accordian'
1129         // here we make this explicit.
1130         $filterGroups[$table] = array(
1131           'group_title' => $this->_columns[$table]['group_title'],
1132           'use_accordian_for_field_selection' => TRUE,
1133
1134         );
1135       }
1136       foreach ($attributes as $fieldName => $field) {
1137         // get ready with option value pair
1138         // @ todo being able to specific options for a field (e.g a date field) in the field spec as an array rather than an override
1139         // would be useful
1140         $operations = $this->getOperationPair(
1141           CRM_Utils_Array::value('operatorType', $field),
1142           $fieldName);
1143
1144         $filters[$table][$fieldName] = $field;
1145
1146         switch (CRM_Utils_Array::value('operatorType', $field)) {
1147           case CRM_Report_Form::OP_MONTH:
1148             if (!array_key_exists('options', $field) ||
1149               !is_array($field['options']) || empty($field['options'])
1150             ) {
1151               // If there's no option list for this filter, define one.
1152               $field['options'] = array(
1153                 1 => ts('January'),
1154                 2 => ts('February'),
1155                 3 => ts('March'),
1156                 4 => ts('April'),
1157                 5 => ts('May'),
1158                 6 => ts('June'),
1159                 7 => ts('July'),
1160                 8 => ts('August'),
1161                 9 => ts('September'),
1162                 10 => ts('October'),
1163                 11 => ts('November'),
1164                 12 => ts('December'),
1165               );
1166               // Add this option list to this column _columns. This is
1167               // required so that filter statistics show properly.
1168               $this->_columns[$table]['filters'][$fieldName]['options'] = $field['options'];
1169             }
1170           case CRM_Report_Form::OP_MULTISELECT:
1171           case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1172             // assume a multi-select field
1173             if (!empty($field['options']) ||
1174               $fieldName == 'state_province_id' || $fieldName == 'county_id'
1175             ) {
1176               $element = $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1177               if (count($operations) <= 1) {
1178                 $element->freeze();
1179               }
1180               if ($fieldName == 'state_province_id' ||
1181                 $fieldName == 'county_id'
1182               ) {
1183                 $this->addChainSelect($fieldName . '_value', array(
1184                   'multiple' => TRUE,
1185                   'label' => NULL,
1186                   'class' => 'huge',
1187                 ));
1188               }
1189               else {
1190                 $this->addElement('select', "{$fieldName}_value", NULL, $field['options'], array(
1191                   'style' => 'min-width:250px',
1192                   'class' => 'crm-select2 huge',
1193                   'multiple' => TRUE,
1194                   'placeholder' => ts('- select -'),
1195                 ));
1196               }
1197             }
1198             break;
1199
1200           case CRM_Report_Form::OP_SELECT:
1201             // assume a select field
1202             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1203             if (!empty($field['options'])) {
1204               $this->addElement('select', "{$fieldName}_value", NULL, $field['options']);
1205             }
1206             break;
1207
1208           case CRM_Report_Form::OP_ENTITYREF:
1209             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1210             $this->setEntityRefDefaults($field, $table);
1211             $this->addEntityRef("{$fieldName}_value", NULL, $field['attributes']);
1212             break;
1213
1214           case CRM_Report_Form::OP_DATE:
1215             // build datetime fields
1216             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', 'From:', FALSE, $operations);
1217             $count++;
1218             break;
1219
1220           case CRM_Report_Form::OP_DATETIME:
1221             // build datetime fields
1222             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', 'From:', FALSE, $operations, 'searchDate', TRUE);
1223             $count++;
1224             break;
1225
1226           case CRM_Report_Form::OP_INT:
1227           case CRM_Report_Form::OP_FLOAT:
1228             // and a min value input box
1229             $this->add('text', "{$fieldName}_min", ts('Min'));
1230             // and a max value input box
1231             $this->add('text', "{$fieldName}_max", ts('Max'));
1232           default:
1233             // default type is string
1234             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations,
1235               array('onchange' => "return showHideMaxMinVal( '$fieldName', this.value );")
1236             );
1237             // we need text box for value input
1238             $this->add('text', "{$fieldName}_value", NULL, array('class' => 'huge'));
1239             break;
1240         }
1241       }
1242     }
1243     if (!empty($filters)) {
1244       $this->tabs['Filters'] = array(
1245         'title' => ts('Filters'),
1246         'tpl' => 'Filters',
1247         'div_label' => 'set-filters',
1248       );
1249     }
1250     $this->assign('filters', $filters);
1251     $this->assign('filterGroups', $filterGroups);
1252   }
1253
1254   /**
1255    * Function to assign the tabs to the template in the correct order.
1256    *
1257    * We want the tabs to wind up in this order (if not overridden).
1258    *
1259    *   - Field Selection
1260    *   - Group Bys
1261    *   - Order Bys
1262    *   - Other Options
1263    *   - Filters
1264    */
1265   protected function assignTabs() {
1266     $order = array(
1267       'FieldSelection',
1268       'GroupBy',
1269       'OrderBy',
1270       'ReportOptions',
1271       'Filters',
1272     );
1273     $order = array_intersect_key(array_fill_keys($order, 1), $this->tabs);
1274     $order = array_merge($order, $this->tabs);
1275     $this->assign('tabs', $order);
1276   }
1277
1278   /**
1279    * The intent is to add a tab for developers to view the sql.
1280    *
1281    * Currently using dpm.
1282    *
1283    * @param string $sql
1284    */
1285   protected function addToDeveloperTab($sql) {
1286     if (!CRM_Core_Permission::check('view report sql')) {
1287       return;
1288     }
1289     $this->tabs['Developer'] = array(
1290       'title' => ts('Developer'),
1291       'tpl' => 'Developer',
1292       'div_label' => 'set-developer',
1293     );
1294
1295     $this->assignTabs();
1296     $this->sqlArray[] = $sql;
1297     foreach (array('LEFT JOIN') as $term) {
1298       $sql = str_replace($term, '<br>&nbsp&nbsp' . $term, $sql);
1299     }
1300     foreach (array('FROM', 'WHERE', 'GROUP BY', 'ORDER BY', 'LIMIT', ';') as $term) {
1301       $sql = str_replace($term, '<br><br>' . $term, $sql);
1302     }
1303     $this->sql .= $sql . "<br>";
1304
1305     $this->assign('sql', $this->sql);
1306   }
1307
1308   /**
1309    * Add options defined in $this->_options to the report.
1310    */
1311   public function addOptions() {
1312     if (!empty($this->_options)) {
1313       // FIXME: For now lets build all elements as checkboxes.
1314       // Once we clear with the format we can build elements based on type
1315
1316       foreach ($this->_options as $fieldName => $field) {
1317         $options = array();
1318
1319         if ($field['type'] == 'select') {
1320           $this->addElement('select', "{$fieldName}", $field['title'], $field['options']);
1321         }
1322         elseif ($field['type'] == 'checkbox') {
1323           $options[$field['title']] = $fieldName;
1324           $this->addCheckBox($fieldName, NULL,
1325             $options, NULL,
1326             NULL, NULL, NULL, $this->_fourColumnAttribute
1327           );
1328         }
1329       }
1330     }
1331     if (!empty($this->_options)) {
1332       $this->tabs['ReportOptions'] = array(
1333         'title' => ts('Display Options'),
1334         'tpl' => 'ReportOptions',
1335         'div_label' => 'other-options',
1336       );
1337     }
1338     $this->assign('otherOptions', $this->_options);
1339   }
1340
1341   /**
1342    * Add chart options to the report.
1343    */
1344   public function addChartOptions() {
1345     if (!empty($this->_charts)) {
1346       $this->addElement('select', "charts", ts('Chart'), $this->_charts);
1347       $this->assign('charts', $this->_charts);
1348       $this->addElement('submit', $this->_chartButtonName, ts('View'));
1349     }
1350   }
1351
1352   /**
1353    * Add group by options to the report.
1354    */
1355   public function addGroupBys() {
1356     $options = $freqElements = array();
1357
1358     foreach ($this->_columns as $tableName => $table) {
1359       if (array_key_exists('group_bys', $table)) {
1360         foreach ($table['group_bys'] as $fieldName => $field) {
1361           if (!empty($field)) {
1362             $options[$field['title']] = $fieldName;
1363             if (!empty($field['frequency'])) {
1364               $freqElements[$field['title']] = $fieldName;
1365             }
1366           }
1367         }
1368       }
1369     }
1370     $this->addCheckBox("group_bys", ts('Group by columns'), $options, NULL,
1371       NULL, NULL, NULL, $this->_fourColumnAttribute
1372     );
1373     $this->assign('groupByElements', $options);
1374     if (!empty($options)) {
1375       $this->tabs['GroupBy'] = array(
1376         'title' => ts('Grouping'),
1377         'tpl' => 'GroupBy',
1378         'div_label' => 'group-by-elements',
1379       );
1380     }
1381
1382     foreach ($freqElements as $name) {
1383       $this->addElement('select', "group_bys_freq[$name]",
1384         ts('Frequency'), $this->_groupByDateFreq
1385       );
1386     }
1387   }
1388
1389   /**
1390    * Add data for order by tab.
1391    */
1392   public function addOrderBys() {
1393     $options = array();
1394     foreach ($this->_columns as $tableName => $table) {
1395
1396       // Report developer may define any column to order by; include these as order-by options.
1397       if (array_key_exists('order_bys', $table)) {
1398         foreach ($table['order_bys'] as $fieldName => $field) {
1399           if (!empty($field)) {
1400             $options[$fieldName] = $field['title'];
1401           }
1402         }
1403       }
1404
1405       // Add searchable custom fields as order-by options, if so requested
1406       // (These are already indexed, so allowing to order on them is cheap.)
1407
1408       if ($this->_autoIncludeIndexedFieldsAsOrderBys &&
1409         array_key_exists('extends', $table) && !empty($table['extends'])
1410       ) {
1411         foreach ($table['fields'] as $fieldName => $field) {
1412           if (empty($field['no_display'])) {
1413             $options[$fieldName] = $field['title'];
1414           }
1415         }
1416       }
1417     }
1418
1419     asort($options);
1420
1421     $this->assign('orderByOptions', $options);
1422     if (!empty($options)) {
1423       $this->tabs['OrderBy'] = array(
1424         'title' => ts('Sorting'),
1425         'tpl' => 'OrderBy',
1426         'div_label' => 'order-by-elements',
1427       );
1428     }
1429
1430     if (!empty($options)) {
1431       $options = array(
1432         '-' => ' - none - ',
1433       ) + $options;
1434       for ($i = 1; $i <= 5; $i++) {
1435         $this->addElement('select', "order_bys[{$i}][column]", ts('Order by Column'), $options);
1436         $this->addElement('select', "order_bys[{$i}][order]", ts('Order by Order'), array(
1437           'ASC' => 'Ascending',
1438           'DESC' => 'Descending',
1439         ));
1440         $this->addElement('checkbox', "order_bys[{$i}][section]", ts('Order by Section'), FALSE, array('id' => "order_by_section_$i"));
1441         $this->addElement('checkbox', "order_bys[{$i}][pageBreak]", ts('Page Break'), FALSE, array('id' => "order_by_pagebreak_$i"));
1442       }
1443     }
1444   }
1445
1446   /**
1447    * This adds the tab referred to as Title and Format, rendered through Instance.tpl.
1448    *
1449    * @todo call this tab into the report template in the same way as OrderBy etc, ie
1450    * by adding a description of the tab to $this->tabs, causing the tab to be added in
1451    * Criteria.tpl.
1452    */
1453   public function buildInstanceAndButtons() {
1454     CRM_Report_Form_Instance::buildForm($this);
1455
1456     $label = $this->_id ? ts('Update Report') : ts('Create Report');
1457
1458     $this->addElement('submit', $this->_instanceButtonName, $label);
1459     $this->addElement('submit', $this->_printButtonName, ts('Print Report'));
1460     $this->addElement('submit', $this->_pdfButtonName, ts('PDF'));
1461
1462     if ($this->_id) {
1463       $this->addElement('submit', $this->_createNewButtonName,
1464         ts('Save a Copy') . '...');
1465     }
1466     $this->assign('instanceForm', $this->_instanceForm);
1467
1468     $label = $this->_id ? ts('Print Report') : ts('Print Preview');
1469     $this->addElement('submit', $this->_printButtonName, $label);
1470
1471     $label = $this->_id ? ts('PDF') : ts('Preview PDF');
1472     $this->addElement('submit', $this->_pdfButtonName, $label);
1473
1474     $label = $this->_id ? ts('Export to CSV') : ts('Preview CSV');
1475
1476     if ($this->_csvSupported) {
1477       $this->addElement('submit', $this->_csvButtonName, $label);
1478     }
1479
1480     // CRM-16274 Determine if user has 'edit all contacts' or equivalent
1481     $permission = CRM_Core_Permission::getPermission();
1482     if ($permission == CRM_Core_Permission::EDIT &&
1483       $this->_add2groupSupported
1484     ) {
1485       $this->addElement('select', 'groups', ts('Group'),
1486         array('' => ts('Add Contacts to Group')) +
1487         CRM_Core_PseudoConstant::nestedGroup(),
1488         array('class' => 'crm-select2 crm-action-menu fa-plus huge')
1489       );
1490       $this->assign('group', TRUE);
1491     }
1492
1493     $this->addElement('submit', $this->_groupButtonName, '', array('style' => 'display: none;'));
1494
1495     $this->addChartOptions();
1496     $this->addButtons(array(
1497         array(
1498           'type' => 'submit',
1499           'name' => ts('Preview Report'),
1500           'isDefault' => TRUE,
1501         ),
1502       )
1503     );
1504   }
1505
1506   /**
1507    * Main build form function.
1508    */
1509   public function buildQuickForm() {
1510     $this->addColumns();
1511
1512     $this->addFilters();
1513
1514     $this->addOptions();
1515
1516     $this->addGroupBys();
1517
1518     $this->addOrderBys();
1519
1520     $this->buildInstanceAndButtons();
1521
1522     // Add form rule for report.
1523     if (is_callable(array(
1524       $this,
1525       'formRule',
1526     ))) {
1527       $this->addFormRule(array(get_class($this), 'formRule'), $this);
1528     }
1529     $this->assignTabs();
1530   }
1531
1532   /**
1533    * A form rule function for custom data.
1534    *
1535    * The rule ensures that fields selected in group_by if any) should only be the ones
1536    * present in display/select fields criteria;
1537    * note: works if and only if any custom field selected in group_by.
1538    *
1539    * @param array $fields
1540    * @param array $ignoreFields
1541    *
1542    * @return array
1543    */
1544   public function customDataFormRule($fields, $ignoreFields = array()) {
1545     $errors = array();
1546     if (!empty($this->_customGroupExtends) && $this->_customGroupGroupBy &&
1547       !empty($fields['group_bys'])
1548     ) {
1549       foreach ($this->_columns as $tableName => $table) {
1550         if ((substr($tableName, 0, 13) == 'civicrm_value' ||
1551             substr($tableName, 0, 12) == 'custom_value') &&
1552           !empty($this->_columns[$tableName]['fields'])
1553         ) {
1554           foreach ($this->_columns[$tableName]['fields'] as $fieldName => $field) {
1555             if (array_key_exists($fieldName, $fields['group_bys']) &&
1556               !array_key_exists($fieldName, $fields['fields'])
1557             ) {
1558               $errors['fields'] = "Please make sure fields selected in 'Group by Columns' section are also selected in 'Display Columns' section.";
1559             }
1560             elseif (array_key_exists($fieldName, $fields['group_bys'])) {
1561               foreach ($fields['fields'] as $fld => $val) {
1562                 if (!array_key_exists($fld, $fields['group_bys']) &&
1563                   !in_array($fld, $ignoreFields)
1564                 ) {
1565                   $errors['fields'] = "Please ensure that fields selected in 'Display Columns' are also selected in 'Group by Columns' section.";
1566                 }
1567               }
1568             }
1569           }
1570         }
1571       }
1572     }
1573     return $errors;
1574   }
1575
1576   /**
1577    * Get operators to display on form.
1578    *
1579    * Note: $fieldName param allows inheriting class to build operationPairs specific to a field.
1580    *
1581    * @param string $type
1582    * @param string $fieldName
1583    *
1584    * @return array
1585    */
1586   public function getOperationPair($type = "string", $fieldName = NULL) {
1587     // FIXME: At some point we should move these key-val pairs
1588     // to option_group and option_value table.
1589     switch ($type) {
1590       case CRM_Report_Form::OP_INT:
1591       case CRM_Report_Form::OP_FLOAT:
1592
1593         $result = array(
1594           'lte' => ts('Is less than or equal to'),
1595           'gte' => ts('Is greater than or equal to'),
1596           'bw' => ts('Is between'),
1597           'eq' => ts('Is equal to'),
1598           'lt' => ts('Is less than'),
1599           'gt' => ts('Is greater than'),
1600           'neq' => ts('Is not equal to'),
1601           'nbw' => ts('Is not between'),
1602           'nll' => ts('Is empty (Null)'),
1603           'nnll' => ts('Is not empty (Null)'),
1604         );
1605         return $result;
1606
1607       case CRM_Report_Form::OP_SELECT:
1608         $result = array(
1609           'eq' => ts('Is equal to'),
1610         );
1611         return $result;
1612
1613       case CRM_Report_Form::OP_MONTH:
1614       case CRM_Report_Form::OP_MULTISELECT:
1615       case CRM_Report_Form::OP_ENTITYREF:
1616
1617         $result = array(
1618           'in' => ts('Is one of'),
1619           'notin' => ts('Is not one of'),
1620         );
1621         return $result;
1622
1623       case CRM_Report_Form::OP_DATE:
1624
1625         $result = array(
1626           'nll' => ts('Is empty (Null)'),
1627           'nnll' => ts('Is not empty (Null)'),
1628         );
1629         return $result;
1630
1631       case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1632         // use this operator for the values, concatenated with separator. For e.g if
1633         // multiple options for a column is stored as ^A{val1}^A{val2}^A
1634         $result = array(
1635           'mhas' => ts('Is one of'),
1636           'mnot' => ts('Is not one of'),
1637         );
1638         return $result;
1639
1640       default:
1641         // type is string
1642         $result = array(
1643           'has' => ts('Contains'),
1644           'sw' => ts('Starts with'),
1645           'ew' => ts('Ends with'),
1646           'nhas' => ts('Does not contain'),
1647           'eq' => ts('Is equal to'),
1648           'neq' => ts('Is not equal to'),
1649           'nll' => ts('Is empty (Null)'),
1650           'nnll' => ts('Is not empty (Null)'),
1651         );
1652         return $result;
1653     }
1654   }
1655
1656   /**
1657    * Build the tag filter field to display on the filters tab.
1658    */
1659   public function buildTagFilter() {
1660     $contactTags = CRM_Core_BAO_Tag::getTags($this->_tagFilterTable);
1661     if (!empty($contactTags)) {
1662       $this->_columns['civicrm_tag'] = array(
1663         'dao' => 'CRM_Core_DAO_Tag',
1664         'filters' => array(
1665           'tagid' => array(
1666             'name' => 'tag_id',
1667             'title' => ts('Tag'),
1668             'tag' => TRUE,
1669             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1670             'options' => $contactTags,
1671           ),
1672         ),
1673       );
1674     }
1675   }
1676
1677   /**
1678    * Adds group filters to _columns (called from _Construct).
1679    */
1680   public function buildGroupFilter() {
1681     $this->_columns['civicrm_group']['filters'] = array(
1682       'gid' => array(
1683         'name' => 'group_id',
1684         'title' => ts('Group'),
1685         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1686         'group' => TRUE,
1687         'options' => CRM_Core_PseudoConstant::nestedGroup(),
1688       ),
1689     );
1690     if (empty($this->_columns['civicrm_group']['dao'])) {
1691       $this->_columns['civicrm_group']['dao'] = 'CRM_Contact_DAO_GroupContact';
1692     }
1693     if (empty($this->_columns['civicrm_group']['alias'])) {
1694       $this->_columns['civicrm_group']['alias'] = 'cgroup';
1695     }
1696   }
1697
1698   /**
1699    * Get SQL operator from form text version.
1700    *
1701    * @param string $operator
1702    *
1703    * @return string
1704    */
1705   public function getSQLOperator($operator = "like") {
1706     switch ($operator) {
1707       case 'eq':
1708         return '=';
1709
1710       case 'lt':
1711         return '<';
1712
1713       case 'lte':
1714         return '<=';
1715
1716       case 'gt':
1717         return '>';
1718
1719       case 'gte':
1720         return '>=';
1721
1722       case 'ne':
1723       case 'neq':
1724         return '!=';
1725
1726       case 'nhas':
1727         return 'NOT LIKE';
1728
1729       case 'in':
1730         return 'IN';
1731
1732       case 'notin':
1733         return 'NOT IN';
1734
1735       case 'nll':
1736         return 'IS NULL';
1737
1738       case 'nnll':
1739         return 'IS NOT NULL';
1740
1741       default:
1742         // type is string
1743         return 'LIKE';
1744     }
1745   }
1746
1747   /**
1748    * Generate where clause.
1749    *
1750    * This can be overridden in reports for special treatment of a field
1751    *
1752    * @param array $field Field specifications
1753    * @param string $op Query operator (not an exact match to sql)
1754    * @param mixed $value
1755    * @param float $min
1756    * @param float $max
1757    *
1758    * @return null|string
1759    */
1760   public function whereClause(&$field, $op, $value, $min, $max) {
1761
1762     $type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
1763     $clause = NULL;
1764
1765     switch ($op) {
1766       case 'bw':
1767       case 'nbw':
1768         if (($min !== NULL && strlen($min) > 0) ||
1769           ($max !== NULL && strlen($max) > 0)
1770         ) {
1771           $min = CRM_Utils_Type::escape($min, $type);
1772           $max = CRM_Utils_Type::escape($max, $type);
1773           $clauses = array();
1774           if ($min) {
1775             if ($op == 'bw') {
1776               $clauses[] = "( {$field['dbAlias']} >= $min )";
1777             }
1778             else {
1779               $clauses[] = "( {$field['dbAlias']} < $min )";
1780             }
1781           }
1782           if ($max) {
1783             if ($op == 'bw') {
1784               $clauses[] = "( {$field['dbAlias']} <= $max )";
1785             }
1786             else {
1787               $clauses[] = "( {$field['dbAlias']} > $max )";
1788             }
1789           }
1790
1791           if (!empty($clauses)) {
1792             if ($op == 'bw') {
1793               $clause = implode(' AND ', $clauses);
1794             }
1795             else {
1796               $clause = implode(' OR ', $clauses);
1797             }
1798           }
1799         }
1800         break;
1801
1802       case 'has':
1803       case 'nhas':
1804         if ($value !== NULL && strlen($value) > 0) {
1805           $value = CRM_Utils_Type::escape($value, $type);
1806           if (strpos($value, '%') === FALSE) {
1807             $value = "'%{$value}%'";
1808           }
1809           else {
1810             $value = "'{$value}'";
1811           }
1812           $sqlOP = $this->getSQLOperator($op);
1813           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1814         }
1815         break;
1816
1817       case 'in':
1818       case 'notin':
1819         if (is_string($value) && strlen($value)) {
1820           $value = explode(',', $value);
1821         }
1822         if ($value !== NULL && is_array($value) && count($value) > 0) {
1823           $sqlOP = $this->getSQLOperator($op);
1824           if (CRM_Utils_Array::value('type', $field) ==
1825             CRM_Utils_Type::T_STRING
1826           ) {
1827             //cycle through selections and escape values
1828             foreach ($value as $key => $selection) {
1829               $value[$key] = CRM_Utils_Type::escape($selection, $type);
1830             }
1831             $clause
1832               = "( {$field['dbAlias']} $sqlOP ( '" . implode("' , '", $value) .
1833               "') )";
1834           }
1835           else {
1836             // for numerical values
1837             $clause = "{$field['dbAlias']} $sqlOP (" . implode(', ', $value) .
1838               ")";
1839           }
1840           if ($op == 'notin') {
1841             $clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
1842           }
1843           else {
1844             $clause = "( " . $clause . " )";
1845           }
1846         }
1847         break;
1848
1849       case 'mhas':
1850       case 'mnot':
1851         // multiple has or multiple not
1852         if ($value !== NULL && count($value) > 0) {
1853           $value = CRM_Utils_Type::escapeAll($value, $type);
1854           $operator = $op == 'mnot' ? 'NOT' : '';
1855           $regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', (array) $value) . "[[:cntrl:]]*";
1856           $clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'";
1857         }
1858         break;
1859
1860       case 'sw':
1861       case 'ew':
1862         if ($value !== NULL && strlen($value) > 0) {
1863           $value = CRM_Utils_Type::escape($value, $type);
1864           if (strpos($value, '%') === FALSE) {
1865             if ($op == 'sw') {
1866               $value = "'{$value}%'";
1867             }
1868             else {
1869               $value = "'%{$value}'";
1870             }
1871           }
1872           else {
1873             $value = "'{$value}'";
1874           }
1875           $sqlOP = $this->getSQLOperator($op);
1876           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1877         }
1878         break;
1879
1880       case 'nll':
1881       case 'nnll':
1882         $sqlOP = $this->getSQLOperator($op);
1883         $clause = "( {$field['dbAlias']} $sqlOP )";
1884         break;
1885
1886       default:
1887         if ($value !== NULL && strlen($value) > 0) {
1888           if (isset($field['clause'])) {
1889             // FIXME: we not doing escape here. Better solution is to use two
1890             // different types - data-type and filter-type
1891             $clause = $field['clause'];
1892           }
1893           else {
1894             $value = CRM_Utils_Type::escape($value, $type);
1895             $sqlOP = $this->getSQLOperator($op);
1896             if ($field['type'] == CRM_Utils_Type::T_STRING) {
1897               $value = "'{$value}'";
1898             }
1899             $clause = "( {$field['dbAlias']} $sqlOP $value )";
1900           }
1901         }
1902         break;
1903     }
1904
1905     if (!empty($field['group']) && $clause) {
1906       $clause = $this->whereGroupClause($field, $value, $op);
1907     }
1908     elseif (!empty($field['tag']) && $clause) {
1909       // not using left join in query because if any contact
1910       // belongs to more than one tag, results duplicate
1911       // entries.
1912       $clause = $this->whereTagClause($field, $value, $op);
1913     }
1914     elseif (!empty($field['membership_org']) && $clause) {
1915       $clause = $this->whereMembershipOrgClause($value, $op);
1916     }
1917     elseif (!empty($field['membership_type']) && $clause) {
1918       $clause = $this->whereMembershipTypeClause($value, $op);
1919     }
1920     return $clause;
1921   }
1922
1923   /**
1924    * Get SQL where clause for a date field.
1925    *
1926    * @param string $fieldName
1927    * @param string $relative
1928    * @param string $from
1929    * @param string $to
1930    * @param string $type
1931    * @param string $fromTime
1932    * @param string $toTime
1933    *
1934    * @return null|string
1935    */
1936   public function dateClause(
1937     $fieldName,
1938     $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
1939   ) {
1940     $clauses = array();
1941     if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) {
1942       $sqlOP = $this->getSQLOperator($relative);
1943       return "( {$fieldName} {$sqlOP} )";
1944     }
1945
1946     list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime);
1947
1948     if ($from) {
1949       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
1950       $clauses[] = "( {$fieldName} >= $from )";
1951     }
1952
1953     if ($to) {
1954       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
1955       $clauses[] = "( {$fieldName} <= {$to} )";
1956     }
1957
1958     if (!empty($clauses)) {
1959       return implode(' AND ', $clauses);
1960     }
1961
1962     return NULL;
1963   }
1964
1965   /**
1966    * Possibly unused function.
1967    *
1968    * @todo - could not find any instances where this is called
1969    *
1970    * @param bool $relative
1971    * @param string $from
1972    * @param string $to
1973    *
1974    * @return string|NULL
1975    */
1976   public function dateDisplay($relative, $from, $to) {
1977     list($from, $to) = $this->getFromTo($relative, $from, $to);
1978
1979     if ($from) {
1980       $clauses[] = CRM_Utils_Date::customFormat($from, NULL, array('m', 'M'));
1981     }
1982     else {
1983       $clauses[] = 'Past';
1984     }
1985
1986     if ($to) {
1987       $clauses[] = CRM_Utils_Date::customFormat($to, NULL, array('m', 'M'));
1988     }
1989     else {
1990       $clauses[] = 'Today';
1991     }
1992
1993     if (!empty($clauses)) {
1994       return implode(' - ', $clauses);
1995     }
1996
1997     return NULL;
1998   }
1999
2000   /**
2001    * Get values for from and to for date ranges.
2002    *
2003    * @param bool $relative
2004    * @param string $from
2005    * @param string $to
2006    * @param string $fromTime
2007    * @param string $toTime
2008    *
2009    * @return array
2010    */
2011   public function getFromTo($relative, $from, $to, $fromTime = NULL, $toTime = NULL) {
2012     if (empty($toTime)) {
2013       $toTime = '235959';
2014     }
2015     //FIX ME not working for relative
2016     if ($relative) {
2017       list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
2018       $dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
2019       $from = substr($dateRange['from'], 0, 8);
2020       //Take only Date Part, Sometime Time part is also present in 'to'
2021       $to = substr($dateRange['to'], 0, 8);
2022     }
2023     $from = CRM_Utils_Date::processDate($from, $fromTime);
2024     $to = CRM_Utils_Date::processDate($to, $toTime);
2025     return array($from, $to);
2026   }
2027
2028   /**
2029    * Alter display of rows.
2030    *
2031    * Iterate through the rows retrieved via SQL and make changes for display purposes,
2032    * such as rendering contacts as links.
2033    *
2034    * @param array $rows
2035    *   Rows generated by SQL, with an array for each row.
2036    */
2037   public function alterDisplay(&$rows) {
2038   }
2039
2040   /**
2041    * Alter the way in which custom data fields are displayed.
2042    *
2043    * @param array $rows
2044    */
2045   public function alterCustomDataDisplay(&$rows) {
2046     // custom code to alter rows having custom values
2047     if (empty($this->_customGroupExtends)) {
2048       return;
2049     }
2050
2051     $customFieldIds = array();
2052     foreach ($this->_params['fields'] as $fieldAlias => $value) {
2053       if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
2054         $customFieldIds[$fieldAlias] = $fieldId;
2055       }
2056     }
2057     if (empty($customFieldIds)) {
2058       return;
2059     }
2060
2061     // skip for type date and ContactReference since date format is already handled
2062     $query = "
2063 SELECT cg.table_name, cf.id
2064 FROM  civicrm_custom_field cf
2065 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
2066 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
2067       cg.is_active = 1 AND
2068       cf.is_active = 1 AND
2069       cf.is_searchable = 1 AND
2070       cf.data_type   NOT IN ('ContactReference', 'Date') AND
2071       cf.id IN (" . implode(",", $customFieldIds) . ")";
2072
2073     $dao = CRM_Core_DAO::executeQuery($query);
2074     while ($dao->fetch()) {
2075       $customFields[$dao->table_name . '_custom_' . $dao->id] = $dao->id;
2076     }
2077     $dao->free();
2078
2079     $entryFound = FALSE;
2080     foreach ($rows as $rowNum => $row) {
2081       foreach ($row as $tableCol => $val) {
2082         if (array_key_exists($tableCol, $customFields)) {
2083           $rows[$rowNum][$tableCol] = CRM_Core_BAO_CustomField::displayValue($val, $customFields[$tableCol]);
2084           $entryFound = TRUE;
2085         }
2086       }
2087
2088       // skip looking further in rows, if first row itself doesn't
2089       // have the column we need
2090       if (!$entryFound) {
2091         break;
2092       }
2093     }
2094   }
2095
2096   /**
2097    * Remove duplicate rows.
2098    *
2099    * @param array $rows
2100    */
2101   public function removeDuplicates(&$rows) {
2102     if (empty($this->_noRepeats)) {
2103       return;
2104     }
2105     $checkList = array();
2106
2107     foreach ($rows as $key => $list) {
2108       foreach ($list as $colName => $colVal) {
2109         if (array_key_exists($colName, $checkList) &&
2110           $checkList[$colName] == $colVal
2111         ) {
2112           $rows[$key][$colName] = "";
2113         }
2114         if (in_array($colName, $this->_noRepeats)) {
2115           $checkList[$colName] = $colVal;
2116         }
2117       }
2118     }
2119   }
2120
2121   /**
2122    * Fix subtotal display.
2123    *
2124    * @param array $row
2125    * @param array $fields
2126    * @param bool $subtotal
2127    */
2128   public function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
2129     foreach ($row as $colName => $colVal) {
2130       if (in_array($colName, $fields)) {
2131       }
2132       elseif (isset($this->_columnHeaders[$colName])) {
2133         if ($subtotal) {
2134           $row[$colName] = "Subtotal";
2135           $subtotal = FALSE;
2136         }
2137         else {
2138           unset($row[$colName]);
2139         }
2140       }
2141     }
2142   }
2143
2144   /**
2145    * Calculate grant total.
2146    *
2147    * @param array $rows
2148    *
2149    * @return bool
2150    */
2151   public function grandTotal(&$rows) {
2152     if (!$this->_rollup  || count($rows) == 1) {
2153       return FALSE;
2154     }
2155
2156     $this->moveSummaryColumnsToTheRightHandSide();
2157
2158     if ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT) {
2159       return FALSE;
2160     }
2161
2162     $this->rollupRow = array_pop($rows);
2163
2164     foreach ($this->_columnHeaders as $fld => $val) {
2165       if (!in_array($fld, $this->_statFields)) {
2166         if (!$this->_grandFlag) {
2167           $this->rollupRow[$fld] = ts('Grand Total');
2168           $this->_grandFlag = TRUE;
2169         }
2170         else {
2171           $this->rollupRow[$fld] = "";
2172         }
2173       }
2174     }
2175
2176     $this->assign('grandStat', $this->rollupRow);
2177     return TRUE;
2178   }
2179
2180   /**
2181    * Format display output.
2182    *
2183    * @param array $rows
2184    * @param bool $pager
2185    */
2186   public function formatDisplay(&$rows, $pager = TRUE) {
2187     // set pager based on if any limit was applied in the query.
2188     if ($pager) {
2189       $this->setPager();
2190     }
2191
2192     // allow building charts if any
2193     if (!empty($this->_params['charts']) && !empty($rows)) {
2194       $this->buildChart($rows);
2195       $this->assign('chartEnabled', TRUE);
2196       $this->_chartId = "{$this->_params['charts']}_" .
2197         ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' .
2198         session_id();
2199       $this->assign('chartId', $this->_chartId);
2200     }
2201
2202     // unset columns not to be displayed.
2203     foreach ($this->_columnHeaders as $key => $value) {
2204       if (!empty($value['no_display'])) {
2205         unset($this->_columnHeaders[$key]);
2206       }
2207     }
2208
2209     // unset columns not to be displayed.
2210     if (!empty($rows)) {
2211       foreach ($this->_noDisplay as $noDisplayField) {
2212         foreach ($rows as $rowNum => $row) {
2213           unset($this->_columnHeaders[$noDisplayField]);
2214         }
2215       }
2216     }
2217
2218     // build array of section totals
2219     $this->sectionTotals();
2220
2221     // process grand-total row
2222     $this->grandTotal($rows);
2223
2224     // use this method for formatting rows for display purpose.
2225     $this->alterDisplay($rows);
2226     CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
2227
2228     // use this method for formatting custom rows for display purpose.
2229     $this->alterCustomDataDisplay($rows);
2230   }
2231
2232   /**
2233    * Build chart.
2234    *
2235    * @param array $rows
2236    */
2237   public function buildChart(&$rows) {
2238     // override this method for building charts.
2239   }
2240
2241   // select() method below has been added recently (v3.3), and many of the report templates might
2242   // still be having their own select() method. We should fix them as and when encountered and move
2243   // towards generalizing the select() method below.
2244
2245   /**
2246    * Generate the SELECT clause and set class variable $_select.
2247    */
2248   public function select() {
2249     $select = $this->_selectAliases = array();
2250
2251     foreach ($this->_columns as $tableName => $table) {
2252       if (array_key_exists('fields', $table)) {
2253         foreach ($table['fields'] as $fieldName => $field) {
2254           if ($tableName == 'civicrm_address') {
2255             $this->_addressField = TRUE;
2256           }
2257           if ($tableName == 'civicrm_email') {
2258             $this->_emailField = TRUE;
2259           }
2260           if ($tableName == 'civicrm_phone') {
2261             $this->_phoneField = TRUE;
2262           }
2263
2264           if (!empty($field['required']) ||
2265             !empty($this->_params['fields'][$fieldName])
2266           ) {
2267
2268             // 1. In many cases we want select clause to be built in slightly different way
2269             // for a particular field of a particular type.
2270             // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2271             // as needed.
2272             $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
2273             if ($selectClause) {
2274               $select[] = $selectClause;
2275               continue;
2276             }
2277
2278             // include statistics columns only if set
2279             if (!empty($field['statistics'])) {
2280               foreach ($field['statistics'] as $stat => $label) {
2281                 $alias = "{$tableName}_{$fieldName}_{$stat}";
2282                 switch (strtolower($stat)) {
2283                   case 'max':
2284                   case 'sum':
2285                     $select[] = "$stat({$field['dbAlias']}) as $alias";
2286                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2287                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2288                     $this->_statFields[$label] = $alias;
2289                     $this->_selectAliases[] = $alias;
2290                     break;
2291
2292                   case 'count':
2293                     $select[] = "COUNT({$field['dbAlias']}) as $alias";
2294                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2295                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2296                     $this->_statFields[$label] = $alias;
2297                     $this->_selectAliases[] = $alias;
2298                     break;
2299
2300                   case 'count_distinct':
2301                     $select[] = "COUNT(DISTINCT {$field['dbAlias']}) as $alias";
2302                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2303                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2304                     $this->_statFields[$label] = $alias;
2305                     $this->_selectAliases[] = $alias;
2306                     break;
2307
2308                   case 'avg':
2309                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
2310                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2311                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2312                     $this->_statFields[$label] = $alias;
2313                     $this->_selectAliases[] = $alias;
2314                     break;
2315                 }
2316               }
2317             }
2318             else {
2319               $alias = "{$tableName}_{$fieldName}";
2320               $select[] = "{$field['dbAlias']} as $alias";
2321               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
2322               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
2323               $this->_selectAliases[] = $alias;
2324             }
2325           }
2326         }
2327       }
2328
2329       // select for group bys
2330       if (array_key_exists('group_bys', $table)) {
2331         foreach ($table['group_bys'] as $fieldName => $field) {
2332
2333           if ($tableName == 'civicrm_address') {
2334             $this->_addressField = TRUE;
2335           }
2336           if ($tableName == 'civicrm_email') {
2337             $this->_emailField = TRUE;
2338           }
2339           if ($tableName == 'civicrm_phone') {
2340             $this->_phoneField = TRUE;
2341           }
2342           // 1. In many cases we want select clause to be built in slightly different way
2343           // for a particular field of a particular type.
2344           // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2345           // as needed.
2346           $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
2347           if ($selectClause) {
2348             $select[] = $selectClause;
2349             continue;
2350           }
2351
2352           if (!empty($this->_params['group_bys']) &&
2353             !empty($this->_params['group_bys'][$fieldName]) &&
2354             !empty($this->_params['group_bys_freq'])
2355           ) {
2356             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
2357               case 'YEARWEEK':
2358                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
2359                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2360                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2361                 $field['title'] = 'Week';
2362                 break;
2363
2364               case 'YEAR':
2365                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
2366                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2367                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2368                 $field['title'] = 'Year';
2369                 break;
2370
2371               case 'MONTH':
2372                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
2373                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2374                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2375                 $field['title'] = 'Month';
2376                 break;
2377
2378               case 'QUARTER':
2379                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
2380                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2381                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2382                 $field['title'] = 'Quarter';
2383                 break;
2384             }
2385             // for graphs and charts -
2386             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
2387               $this->_interval = $field['title'];
2388               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title']
2389                 = $field['title'] . ' Beginning';
2390               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
2391               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
2392
2393               // just to make sure these values are transferred to rows.
2394               // since we 'll need them for calculation purpose,
2395               // e.g making subtotals look nicer or graphs
2396               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
2397               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
2398             }
2399           }
2400         }
2401       }
2402     }
2403
2404     $this->_selectClauses = $select;
2405     $this->_select = "SELECT " . implode(', ', $select) . " ";
2406   }
2407
2408   /**
2409    * Build select clause for a single field.
2410    *
2411    * @param string $tableName
2412    * @param string $tableKey
2413    * @param string $fieldName
2414    * @param string $field
2415    *
2416    * @return bool
2417    */
2418   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
2419     return FALSE;
2420   }
2421
2422   /**
2423    * Build where clause.
2424    */
2425   public function where() {
2426     $this->storeWhereHavingClauseArray();
2427
2428     if (empty($this->_whereClauses)) {
2429       $this->_where = "WHERE ( 1 ) ";
2430       $this->_having = "";
2431     }
2432     else {
2433       $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
2434     }
2435
2436     if ($this->_aclWhere) {
2437       $this->_where .= " AND {$this->_aclWhere} ";
2438     }
2439
2440     if (!empty($this->_havingClauses)) {
2441       // use this clause to construct group by clause.
2442       $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
2443     }
2444   }
2445
2446   /**
2447    * Store Where clauses into an array.
2448    *
2449    * Breaking out this step makes over-riding more flexible as the clauses can be used in constructing a
2450    * temp table that may not be part of the final where clause or added
2451    * in other functions
2452    */
2453   public function storeWhereHavingClauseArray() {
2454     foreach ($this->_columns as $tableName => $table) {
2455       if (array_key_exists('filters', $table)) {
2456         foreach ($table['filters'] as $fieldName => $field) {
2457           // respect pseudofield to filter spec so fields can be marked as
2458           // not to be handled here
2459           if (!empty($field['pseudofield'])) {
2460             continue;
2461           }
2462           $clause = NULL;
2463           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
2464             if (CRM_Utils_Array::value('operatorType', $field) ==
2465               CRM_Report_Form::OP_MONTH
2466             ) {
2467               $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2468               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2469               if (is_array($value) && !empty($value)) {
2470                 $clause
2471                   = "(month({$field['dbAlias']}) $op (" . implode(', ', $value) .
2472                   '))';
2473               }
2474             }
2475             else {
2476               $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
2477               $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
2478               $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
2479               $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
2480               $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
2481               $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
2482             }
2483           }
2484           else {
2485             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2486             if ($op) {
2487               $clause = $this->whereClause($field,
2488                 $op,
2489                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
2490                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
2491                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
2492               );
2493             }
2494           }
2495
2496           if (!empty($clause)) {
2497             if (!empty($field['having'])) {
2498               $this->_havingClauses[] = $clause;
2499             }
2500             else {
2501               $this->_whereClauses[] = $clause;
2502             }
2503           }
2504         }
2505       }
2506     }
2507
2508   }
2509
2510   /**
2511    * Set output mode.
2512    */
2513   public function processReportMode() {
2514     $this->setOutputMode();
2515
2516     $this->_sendmail
2517       = CRM_Utils_Request::retrieve(
2518         'sendmail',
2519         'Boolean',
2520         CRM_Core_DAO::$_nullObject
2521       );
2522
2523     $this->_absoluteUrl = FALSE;
2524     $printOnly = FALSE;
2525     $this->assign('printOnly', FALSE);
2526
2527     if ($this->_outputMode == 'print' ||
2528       ($this->_sendmail && !$this->_outputMode)
2529     ) {
2530       $this->assign('printOnly', TRUE);
2531       $printOnly = TRUE;
2532       $this->addPaging = FALSE;
2533       $this->assign('outputMode', 'print');
2534       $this->_outputMode = 'print';
2535       if ($this->_sendmail) {
2536         $this->_absoluteUrl = TRUE;
2537       }
2538     }
2539     elseif ($this->_outputMode == 'pdf') {
2540       $printOnly = TRUE;
2541       $this->addPaging = FALSE;
2542       $this->_absoluteUrl = TRUE;
2543     }
2544     elseif ($this->_outputMode == 'csv') {
2545       $printOnly = TRUE;
2546       $this->_absoluteUrl = TRUE;
2547       $this->addPaging = FALSE;
2548     }
2549     elseif ($this->_outputMode == 'group') {
2550       $this->assign('outputMode', 'group');
2551     }
2552     elseif ($this->_outputMode == 'create_report' && $this->_criteriaForm) {
2553       $this->assign('outputMode', 'create_report');
2554     }
2555     elseif ($this->_outputMode == 'copy' && $this->_criteriaForm) {
2556       $this->_createNew = TRUE;
2557     }
2558
2559     $this->assign('outputMode', $this->_outputMode);
2560     $this->assign('printOnly', $printOnly);
2561     // Get today's date to include in printed reports
2562     if ($printOnly) {
2563       $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
2564       $this->assign('reportDate', $reportDate);
2565     }
2566   }
2567
2568   /**
2569    * Post Processing function for Form.
2570    *
2571    * postProcessCommon should be used to set other variables from input as the api accesses that function.
2572    * This function is not accessed when the api calls the report.
2573    */
2574   public function beginPostProcess() {
2575     $this->setParams($this->controller->exportValues($this->_name));
2576     if (empty($this->_params) &&
2577       $this->_force
2578     ) {
2579       $this->setParams($this->_formValues);
2580     }
2581
2582     // hack to fix params when submitted from dashboard, CRM-8532
2583     // fields array is missing because form building etc is skipped
2584     // in dashboard mode for report
2585     //@todo - this could be done in the dashboard no we have a setter
2586     if (empty($this->_params['fields']) && !$this->_noFields) {
2587       $this->setParams($this->_formValues);
2588     }
2589
2590     $this->_formValues = $this->_params;
2591     if (CRM_Core_Permission::check('administer Reports') &&
2592       isset($this->_id) &&
2593       ($this->_instanceButtonName ==
2594         $this->controller->getButtonName() . '_save' ||
2595         $this->_chartButtonName == $this->controller->getButtonName()
2596       )
2597     ) {
2598       $this->assign('updateReportButton', TRUE);
2599     }
2600
2601     $this->processReportMode();
2602
2603     if ($this->_outputMode == 'save' || $this->_outputMode == 'copy') {
2604       $this->_createNew = ($this->_outputMode == 'copy');
2605       // Do not pass go. Do not collect another chance to re-run the same query.
2606       // This will be called from the button - there is an earlier response to the url
2607       // perhaps they can still be consolidated more.
2608       CRM_Report_Form_Instance::postProcess($this);
2609     }
2610     $this->beginPostProcessCommon();
2611   }
2612
2613   /**
2614    * BeginPostProcess function run in both report mode and non-report mode (api).
2615    */
2616   public function beginPostProcessCommon() {}
2617
2618   /**
2619    * Build the report query.
2620    *
2621    * @param bool $applyLimit
2622    *
2623    * @return string
2624    */
2625   public function buildQuery($applyLimit = TRUE) {
2626     $this->select();
2627     $this->from();
2628     $this->customDataFrom();
2629     $this->where();
2630     $this->groupBy();
2631     $this->orderBy();
2632
2633     // order_by columns not selected for display need to be included in SELECT
2634     $unselectedSectionColumns = $this->unselectedSectionColumns();
2635     foreach ($unselectedSectionColumns as $alias => $section) {
2636       $this->_select .= ", {$section['dbAlias']} as {$alias}";
2637     }
2638
2639     if ($applyLimit && empty($this->_params['charts'])) {
2640       $this->limit();
2641     }
2642     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2643
2644     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2645     $this->addToDeveloperTab($sql);
2646     return $sql;
2647   }
2648
2649   /**
2650    * Build group by clause.
2651    */
2652   public function groupBy() {
2653     $groupBys = array();
2654     if (!empty($this->_params['group_bys']) &&
2655       is_array($this->_params['group_bys'])
2656     ) {
2657       foreach ($this->_columns as $tableName => $table) {
2658         if (array_key_exists('group_bys', $table)) {
2659           foreach ($table['group_bys'] as $fieldName => $field) {
2660             if (!empty($this->_params['group_bys'][$fieldName])) {
2661               $groupBys[] = $field['dbAlias'];
2662             }
2663           }
2664         }
2665       }
2666     }
2667
2668     if (!empty($groupBys)) {
2669       $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
2670     }
2671   }
2672
2673   /**
2674    * Build order by clause.
2675    */
2676   public function orderBy() {
2677     $this->_orderBy = "";
2678     $this->_sections = array();
2679     $this->storeOrderByArray();
2680     if (!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP') {
2681       $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2682     }
2683     $this->assign('sections', $this->_sections);
2684   }
2685
2686   /**
2687    * Extract order by fields and store as an array.
2688    *
2689    * In some cases other functions want to know which fields are selected for ordering by
2690    * Separating this into a separate function allows it to be called separately from constructing
2691    * the order by clause
2692    */
2693   public function storeOrderByArray() {
2694     $orderBys = array();
2695
2696     if (!empty($this->_params['order_bys']) &&
2697       is_array($this->_params['order_bys']) &&
2698       !empty($this->_params['order_bys'])
2699     ) {
2700
2701       // Process order_bys in user-specified order
2702       foreach ($this->_params['order_bys'] as $orderBy) {
2703         $orderByField = array();
2704         foreach ($this->_columns as $tableName => $table) {
2705           if (array_key_exists('order_bys', $table)) {
2706             // For DAO columns defined in $this->_columns
2707             $fields = $table['order_bys'];
2708           }
2709           elseif (array_key_exists('extends', $table)) {
2710             // For custom fields referenced in $this->_customGroupExtends
2711             $fields = CRM_Utils_Array::value('fields', $table, array());
2712           }
2713           else {
2714             continue;
2715           }
2716           if (!empty($fields) && is_array($fields)) {
2717             foreach ($fields as $fieldName => $field) {
2718               if ($fieldName == $orderBy['column']) {
2719                 $orderByField = array_merge($field, $orderBy);
2720                 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2721                 break 2;
2722               }
2723             }
2724           }
2725         }
2726
2727         if (!empty($orderByField)) {
2728           $this->_orderByFields[$orderByField['tplField']] = $orderByField;
2729           $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2730
2731           // Record any section headers for assignment to the template
2732           if (!empty($orderBy['section'])) {
2733             $orderByField['pageBreak'] = CRM_Utils_Array::value('pageBreak', $orderBy);
2734             $this->_sections[$orderByField['tplField']] = $orderByField;
2735           }
2736         }
2737       }
2738     }
2739
2740     $this->_orderByArray = $orderBys;
2741
2742     $this->assign('sections', $this->_sections);
2743   }
2744
2745   /**
2746    * Determine unselected columns.
2747    *
2748    * @return array
2749    */
2750   public function unselectedSectionColumns() {
2751     if (is_array($this->_sections)) {
2752       return array_diff_key($this->_sections, $this->getSelectColumns());
2753     }
2754     else {
2755       return array();
2756     }
2757   }
2758
2759   /**
2760    * Build output rows.
2761    *
2762    * @param string $sql
2763    * @param array $rows
2764    */
2765   public function buildRows($sql, &$rows) {
2766     $dao = CRM_Core_DAO::executeQuery($sql);
2767     if (!is_array($rows)) {
2768       $rows = array();
2769     }
2770
2771     // use this method to modify $this->_columnHeaders
2772     $this->modifyColumnHeaders();
2773
2774     $unselectedSectionColumns = $this->unselectedSectionColumns();
2775
2776     while ($dao->fetch()) {
2777       $row = array();
2778       foreach ($this->_columnHeaders as $key => $value) {
2779         if (property_exists($dao, $key)) {
2780           $row[$key] = $dao->$key;
2781         }
2782       }
2783
2784       // section headers not selected for display need to be added to row
2785       foreach ($unselectedSectionColumns as $key => $values) {
2786         if (property_exists($dao, $key)) {
2787           $row[$key] = $dao->$key;
2788         }
2789       }
2790
2791       $rows[] = $row;
2792     }
2793   }
2794
2795   /**
2796    * Calculate section totals.
2797    *
2798    * When "order by" fields are marked as sections, this assigns to the template
2799    * an array of total counts for each section. This data is used by the Smarty
2800    * plugin {sectionTotal}.
2801    */
2802   public function sectionTotals() {
2803
2804     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
2805     if (empty($this->_selectAliases)) {
2806       return;
2807     }
2808
2809     if (!empty($this->_sections)) {
2810       // build the query with no LIMIT clause
2811       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
2812       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
2813
2814       // pull section aliases out of $this->_sections
2815       $sectionAliases = array_keys($this->_sections);
2816
2817       $ifnulls = array();
2818       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
2819         $ifnulls[] = "ifnull($alias, '') as $alias";
2820       }
2821
2822       // Group (un-limited) report by all aliases and get counts. This might
2823       // be done more efficiently when the contents of $sql are known, ie. by
2824       // overriding this method in the report class.
2825
2826       $query = "select " . implode(", ", $ifnulls) .
2827         ", count(*) as ct from ($sql) as subquery group by " .
2828         implode(", ", $sectionAliases);
2829
2830       // initialize array of total counts
2831       $totals = array();
2832       $dao = CRM_Core_DAO::executeQuery($query);
2833       while ($dao->fetch()) {
2834
2835         // let $this->_alterDisplay translate any integer ids to human-readable values.
2836         $rows[0] = $dao->toArray();
2837         $this->alterDisplay($rows);
2838         $row = $rows[0];
2839
2840         // add totals for all permutations of section values
2841         $values = array();
2842         $i = 1;
2843         $aliasCount = count($sectionAliases);
2844         foreach ($sectionAliases as $alias) {
2845           $values[] = $row[$alias];
2846           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
2847           if ($i == $aliasCount) {
2848             // the last alias is the lowest-level section header; use count as-is
2849             $totals[$key] = $dao->ct;
2850           }
2851           else {
2852             // other aliases are higher level; roll count into their total
2853             $totals[$key] += $dao->ct;
2854           }
2855         }
2856       }
2857       $this->assign('sectionTotals', $totals);
2858     }
2859   }
2860
2861   /**
2862    * Modify column headers.
2863    */
2864   public function modifyColumnHeaders() {
2865     // use this method to modify $this->_columnHeaders
2866   }
2867
2868   /**
2869    * Move totals columns to the right edge of the table.
2870    *
2871    * It seems like a more logical layout to have any totals columns on the far right regardless of
2872    * the location of the rest of their table.
2873    */
2874   public function moveSummaryColumnsToTheRightHandSide() {
2875     $statHeaders = (array_intersect_key($this->_columnHeaders, array_flip($this->_statFields)));
2876     $this->_columnHeaders = array_merge(array_diff_key($this->_columnHeaders, $statHeaders), $this->_columnHeaders, $statHeaders);
2877   }
2878
2879   /**
2880    * Assign rows to the template.
2881    *
2882    * @param array $rows
2883    */
2884   public function doTemplateAssignment(&$rows) {
2885     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
2886     $this->assign_by_ref('rows', $rows);
2887     $this->assign('statistics', $this->statistics($rows));
2888   }
2889
2890   /**
2891    * Build report statistics.
2892    *
2893    * Override this method to build your own statistics.
2894    *
2895    * @param array $rows
2896    *
2897    * @return array
2898    */
2899   public function statistics(&$rows) {
2900     $statistics = array();
2901
2902     $count = count($rows);
2903     // Why do we increment the count for rollup seems to artificially inflate the count.
2904     // It seems perhaps intentional to include the summary row in the count of results - although
2905     // this just seems odd.
2906     if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
2907       $count++;
2908     }
2909
2910     $this->countStat($statistics, $count);
2911
2912     $this->groupByStat($statistics);
2913
2914     $this->filterStat($statistics);
2915
2916     return $statistics;
2917   }
2918
2919   /**
2920    * Add count statistics.
2921    *
2922    * @param array $statistics
2923    * @param int $count
2924    */
2925   public function countStat(&$statistics, $count) {
2926     $statistics['counts']['rowCount'] = array(
2927       'title' => ts('Row(s) Listed'),
2928       'value' => $count,
2929     );
2930
2931     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
2932       $statistics['counts']['rowsFound'] = array(
2933         'title' => ts('Total Row(s)'),
2934         'value' => $this->_rowsFound,
2935       );
2936     }
2937   }
2938
2939   /**
2940    * Add group by statistics.
2941    *
2942    * @param array $statistics
2943    */
2944   public function groupByStat(&$statistics) {
2945     if (!empty($this->_params['group_bys']) &&
2946       is_array($this->_params['group_bys']) &&
2947       !empty($this->_params['group_bys'])
2948     ) {
2949       foreach ($this->_columns as $tableName => $table) {
2950         if (array_key_exists('group_bys', $table)) {
2951           foreach ($table['group_bys'] as $fieldName => $field) {
2952             if (!empty($this->_params['group_bys'][$fieldName])) {
2953               $combinations[] = $field['title'];
2954             }
2955           }
2956         }
2957       }
2958       $statistics['groups'][] = array(
2959         'title' => ts('Grouping(s)'),
2960         'value' => implode(' & ', $combinations),
2961       );
2962     }
2963   }
2964
2965   /**
2966    * Filter statistics.
2967    *
2968    * @param array $statistics
2969    */
2970   public function filterStat(&$statistics) {
2971     foreach ($this->_columns as $tableName => $table) {
2972       if (array_key_exists('filters', $table)) {
2973         foreach ($table['filters'] as $fieldName => $field) {
2974           if ((CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE ||
2975               CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_TIME) &&
2976             CRM_Utils_Array::value('operatorType', $field) !=
2977             CRM_Report_Form::OP_MONTH
2978           ) {
2979             list($from, $to)
2980               = $this->getFromTo(
2981                 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
2982                 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
2983                 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
2984                 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
2985                 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
2986               );
2987             $from_time_format = !empty($this->_params["{$fieldName}_from_time"]) ? 'h' : 'd';
2988             $from = CRM_Utils_Date::customFormat($from, NULL, array($from_time_format));
2989
2990             $to_time_format = !empty($this->_params["{$fieldName}_to_time"]) ? 'h' : 'd';
2991             $to = CRM_Utils_Date::customFormat($to, NULL, array($to_time_format));
2992
2993             if ($from || $to) {
2994               $statistics['filters'][] = array(
2995                 'title' => $field['title'],
2996                 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
2997               );
2998             }
2999             elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3000               array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE))
3001             )) {
3002               $pair = $this->getOperationPair(CRM_Report_Form::OP_DATE);
3003               $statistics['filters'][] = array(
3004                 'title' => $field['title'],
3005                 'value' => $pair[$rel],
3006               );
3007             }
3008           }
3009           else {
3010             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
3011             $value = NULL;
3012             if ($op) {
3013               $pair = $this->getOperationPair(
3014                 CRM_Utils_Array::value('operatorType', $field),
3015                 $fieldName
3016               );
3017               $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
3018               $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
3019               $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
3020               if (in_array($op, array('bw', 'nbw')) && ($min || $max)) {
3021                 $value = "{$pair[$op]} $min " . ts('and') . " $max";
3022               }
3023               elseif ($val && CRM_Utils_Array::value('operatorType', $field) & self::OP_ENTITYREF) {
3024                 $this->setEntityRefDefaults($field, $tableName);
3025                 $result = civicrm_api3($field['attributes']['entity'], 'getlist',
3026                   array('id' => $val) +
3027                   CRM_Utils_Array::value('api', $field['attributes'], array()));
3028                 $values = array();
3029                 foreach ($result['values'] as $v) {
3030                   $values[] = $v['label'];
3031                 }
3032                 $value = "{$pair[$op]} " . implode(', ', $values);
3033               }
3034               elseif ($op == 'nll' || $op == 'nnll') {
3035                 $value = $pair[$op];
3036               }
3037               elseif (is_array($val) && (!empty($val))) {
3038                 $options = CRM_Utils_Array::value('options', $field, array());
3039                 foreach ($val as $key => $valIds) {
3040                   if (isset($options[$valIds])) {
3041                     $val[$key] = $options[$valIds];
3042                   }
3043                 }
3044                 $pair[$op] = (count($val) == 1) ? (($op == 'notin' || $op ==
3045                   'mnot') ? ts('Is Not') : ts('Is')) : CRM_Utils_Array::value($op, $pair);
3046                 $val = implode(', ', $val);
3047                 $value = "{$pair[$op]} " . $val;
3048               }
3049               elseif (!is_array($val) && (!empty($val) || $val == '0') &&
3050                 isset($field['options']) &&
3051                 is_array($field['options']) && !empty($field['options'])
3052               ) {
3053                 $value = CRM_Utils_Array::value($op, $pair) . " " .
3054                   CRM_Utils_Array::value($val, $field['options'], $val);
3055               }
3056               elseif ($val) {
3057                 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
3058               }
3059             }
3060             if ($value) {
3061               $statistics['filters'][] = array(
3062                 'title' => CRM_Utils_Array::value('title', $field),
3063                 'value' => $value,
3064               );
3065             }
3066           }
3067         }
3068       }
3069     }
3070   }
3071
3072   /**
3073    * End post processing.
3074    *
3075    * @param array|null $rows
3076    */
3077   public function endPostProcess(&$rows = NULL) {
3078     if ($this->_storeResultSet) {
3079       $this->_resultSet = $rows;
3080     }
3081
3082     if ($this->_outputMode == 'print' ||
3083       $this->_outputMode == 'pdf' ||
3084       $this->_sendmail
3085     ) {
3086
3087       $content = $this->compileContent();
3088       $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
3089         "reset=1", TRUE
3090       );
3091
3092       if ($this->_sendmail) {
3093         $config = CRM_Core_Config::singleton();
3094         $attachments = array();
3095
3096         if ($this->_outputMode == 'csv') {
3097           $content
3098             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3099             ": {$url}</p>" . '<p>' .
3100             ts('The report is attached as a CSV file.') . '</p>' .
3101             $this->_formValues['report_footer'];
3102
3103           $csvFullFilename = $config->templateCompileDir .
3104             CRM_Utils_File::makeFileName('CiviReport.csv');
3105           $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
3106           file_put_contents($csvFullFilename, $csvContent);
3107           $attachments[] = array(
3108             'fullPath' => $csvFullFilename,
3109             'mime_type' => 'text/csv',
3110             'cleanName' => 'CiviReport.csv',
3111           );
3112         }
3113         if ($this->_outputMode == 'pdf') {
3114           // generate PDF content
3115           $pdfFullFilename = $config->templateCompileDir .
3116             CRM_Utils_File::makeFileName('CiviReport.pdf');
3117           file_put_contents($pdfFullFilename,
3118             CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
3119               TRUE, array('orientation' => 'landscape')
3120             )
3121           );
3122           // generate Email Content
3123           $content
3124             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3125             ": {$url}</p>" . '<p>' .
3126             ts('The report is attached as a PDF file.') . '</p>' .
3127             $this->_formValues['report_footer'];
3128
3129           $attachments[] = array(
3130             'fullPath' => $pdfFullFilename,
3131             'mime_type' => 'application/pdf',
3132             'cleanName' => 'CiviReport.pdf',
3133           );
3134         }
3135
3136         if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
3137           $this->_outputMode, $attachments
3138         )
3139         ) {
3140           CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
3141         }
3142         else {
3143           CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
3144         }
3145         return TRUE;
3146       }
3147       elseif ($this->_outputMode == 'print') {
3148         echo $content;
3149       }
3150       else {
3151         if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
3152           $config = CRM_Core_Config::singleton();
3153           //get chart image name
3154           $chartImg = $this->_chartId . '.png';
3155           //get image url path
3156           $uploadUrl
3157             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) .
3158             'openFlashChart/';
3159           $uploadUrl .= $chartImg;
3160           //get image doc path to overwrite
3161           $uploadImg
3162             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
3163             'openFlashChart/' . $chartImg;
3164           //Load the image
3165           $chart = imagecreatefrompng($uploadUrl);
3166           //convert it into formatted png
3167           CRM_Utils_System::setHttpHeader('Content-type', 'image/png');
3168           //overwrite with same image
3169           imagepng($chart, $uploadImg);
3170           //delete the object
3171           imagedestroy($chart);
3172         }
3173         CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
3174       }
3175       CRM_Utils_System::civiExit();
3176     }
3177     elseif ($this->_outputMode == 'csv') {
3178       CRM_Report_Utils_Report::export2csv($this, $rows);
3179     }
3180     elseif ($this->_outputMode == 'group') {
3181       $group = $this->_params['groups'];
3182       $this->add2group($group);
3183     }
3184   }
3185
3186   /**
3187    * Set store result set indicator to TRUE.
3188    *
3189    * @todo explain what this does
3190    */
3191   public function storeResultSet() {
3192     $this->_storeResultSet = TRUE;
3193   }
3194
3195   /**
3196    * Get result set.
3197    *
3198    * @return bool
3199    */
3200   public function getResultSet() {
3201     return $this->_resultSet;
3202   }
3203
3204   /**
3205    * Get the sql used to generate the report.
3206    *
3207    * @return string
3208    */
3209   public function getReportSql() {
3210     return $this->sqlArray;
3211   }
3212
3213   /**
3214    * Use the form name to create the tpl file name.
3215    *
3216    * @return string
3217    */
3218   public function getTemplateFileName() {
3219     $defaultTpl = parent::getTemplateFileName();
3220     $template = CRM_Core_Smarty::singleton();
3221     if (!$template->template_exists($defaultTpl)) {
3222       $defaultTpl = 'CRM/Report/Form.tpl';
3223     }
3224     return $defaultTpl;
3225   }
3226
3227   /**
3228    * Compile the report content.
3229    *
3230    * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
3231    *
3232    * @return string
3233    */
3234   public function compileContent() {
3235     $templateFile = $this->getHookedTemplateFileName();
3236     return $this->_formValues['report_header'] .
3237     CRM_Core_Form::$_template->fetch($templateFile) .
3238     $this->_formValues['report_footer'];
3239   }
3240
3241
3242   /**
3243    * Post process function.
3244    */
3245   public function postProcess() {
3246     // get ready with post process params
3247     $this->beginPostProcess();
3248
3249     // build query
3250     $sql = $this->buildQuery();
3251
3252     // build array of result based on column headers. This method also allows
3253     // modifying column headers before using it to build result set i.e $rows.
3254     $rows = array();
3255     $this->buildRows($sql, $rows);
3256
3257     // format result set.
3258     $this->formatDisplay($rows);
3259
3260     // assign variables to templates
3261     $this->doTemplateAssignment($rows);
3262
3263     // do print / pdf / instance stuff if needed
3264     $this->endPostProcess($rows);
3265   }
3266
3267   /**
3268    * Set limit.
3269    *
3270    * @param int $rowCount
3271    *
3272    * @return array
3273    */
3274   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
3275     // lets do the pager if in html mode
3276     $this->_limit = NULL;
3277
3278     // CRM-14115, over-ride row count if rowCount is specified in URL
3279     if ($this->_dashBoardRowCount) {
3280       $rowCount = $this->_dashBoardRowCount;
3281     }
3282     if ($this->addPaging) {
3283       $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
3284
3285       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
3286
3287       // @todo all http vars should be extracted in the preProcess
3288       // - not randomly in the class
3289       if (!$pageId && !empty($_POST)) {
3290         if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
3291           $pageId = max((int) $_POST['crmPID_B'], 1);
3292         }
3293         elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
3294           $pageId = max((int) $_POST['crmPID'], 1);
3295         }
3296         unset($_POST['crmPID_B'], $_POST['crmPID']);
3297       }
3298
3299       $pageId = $pageId ? $pageId : 1;
3300       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
3301       $offset = ($pageId - 1) * $rowCount;
3302
3303       $offset = CRM_Utils_Type::escape($offset, 'Int');
3304       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
3305
3306       $this->_limit = " LIMIT $offset, $rowCount";
3307       return array($offset, $rowCount);
3308     }
3309     if ($this->_limitValue) {
3310       if ($this->_offsetValue) {
3311         $this->_limit = " LIMIT {$this->_offsetValue}, {$this->_limitValue} ";
3312       }
3313       else {
3314         $this->_limit = " LIMIT " . $this->_limitValue;
3315       }
3316     }
3317   }
3318
3319   /**
3320    * Set pager.
3321    *
3322    * @param int $rowCount
3323    */
3324   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
3325     // CRM-14115, over-ride row count if rowCount is specified in URL
3326     if ($this->_dashBoardRowCount) {
3327       $rowCount = $this->_dashBoardRowCount;
3328     }
3329
3330     if ($this->_limit && ($this->_limit != '')) {
3331       if (!$this->_rowsFound) {
3332         $sql = "SELECT FOUND_ROWS();";
3333         $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
3334       }
3335       $params = array(
3336         'total' => $this->_rowsFound,
3337         'rowCount' => $rowCount,
3338         'status' => ts('Records') . ' %%StatusMessage%%',
3339         'buttonBottom' => 'PagerBottomButton',
3340         'buttonTop' => 'PagerTopButton',
3341       );
3342       if (!empty($this->controller)) {
3343         // This happens when being called from the api Really we want the api to be able to
3344         // pass paging parameters, but at this stage just preventing test crashes.
3345         $params['pageID'] = $this->get(CRM_Utils_Pager::PAGE_ID);
3346       }
3347
3348       $pager = new CRM_Utils_Pager($params);
3349       $this->assign_by_ref('pager', $pager);
3350       $this->ajaxResponse['totalRows'] = $this->_rowsFound;
3351     }
3352   }
3353
3354   /**
3355    * Build where clause for groups.
3356    *
3357    * @param string $field
3358    * @param mixed $value
3359    * @param string $op
3360    *
3361    * @return string
3362    */
3363   public function whereGroupClause($field, $value, $op) {
3364
3365     $smartGroupQuery = "";
3366
3367     $group = new CRM_Contact_DAO_Group();
3368     $group->is_active = 1;
3369     $group->find();
3370     $smartGroups = array();
3371     while ($group->fetch()) {
3372       if (in_array($group->id, $this->_params['gid_value']) &&
3373         $group->saved_search_id
3374       ) {
3375         $smartGroups[] = $group->id;
3376       }
3377     }
3378
3379     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3380
3381     $smartGroupQuery = '';
3382     if (!empty($smartGroups)) {
3383       $smartGroups = implode(',', $smartGroups);
3384       $smartGroupQuery = " UNION DISTINCT
3385                   SELECT DISTINCT smartgroup_contact.contact_id
3386                   FROM civicrm_group_contact_cache smartgroup_contact
3387                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3388     }
3389
3390     $sqlOp = $this->getSQLOperator($op);
3391     if (!is_array($value)) {
3392       $value = array($value);
3393     }
3394     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3395
3396     $contactAlias = $this->_aliases['civicrm_contact'];
3397     if (!empty($this->relationType) && $this->relationType == 'b_a') {
3398       $contactAlias = $this->_aliases['civicrm_contact_b'];
3399     }
3400     return " {$contactAlias}.id {$sqlOp} (
3401                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
3402                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
3403                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
3404                           {$smartGroupQuery} ) ";
3405   }
3406
3407   /**
3408    * Build where clause for tags.
3409    *
3410    * @param string $field
3411    * @param mixed $value
3412    * @param string $op
3413    *
3414    * @return string
3415    */
3416   public function whereTagClause($field, $value, $op) {
3417     // not using left join in query because if any contact
3418     // belongs to more than one tag, results duplicate
3419     // entries.
3420     $sqlOp = $this->getSQLOperator($op);
3421     if (!is_array($value)) {
3422       $value = array($value);
3423     }
3424     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3425     $entity_table = $this->_tagFilterTable;
3426     return " {$this->_aliases[$entity_table]}.id {$sqlOp} (
3427                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
3428                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
3429                           WHERE entity_table = '$entity_table' AND {$clause} ) ";
3430   }
3431
3432   /**
3433    * Generate membership organization clause.
3434    *
3435    * @param mixed $value
3436    * @param string $op SQL Operator
3437    *
3438    * @return string
3439    */
3440   public function whereMembershipOrgClause($value, $op) {
3441     $sqlOp = $this->getSQLOperator($op);
3442     if (!is_array($value)) {
3443       $value = array($value);
3444     }
3445
3446     $tmp_membership_org_sql_list = implode(', ', $value);
3447     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3448                           SELECT DISTINCT mem.contact_id
3449                           FROM civicrm_membership mem
3450                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3451                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3452                           WHERE mt.member_of_contact_id IN (" .
3453     $tmp_membership_org_sql_list . ")
3454                           AND mt.is_active = '1'
3455                           AND mem_status.is_current_member = '1'
3456                           AND mem_status.is_active = '1' )  ";
3457   }
3458
3459   /**
3460    * Generate Membership Type SQL Clause.
3461    *
3462    * @param mixed $value
3463    * @param string $op
3464    *
3465    * @return string
3466    *   SQL query string
3467    */
3468   public function whereMembershipTypeClause($value, $op) {
3469     $sqlOp = $this->getSQLOperator($op);
3470     if (!is_array($value)) {
3471       $value = array($value);
3472     }
3473
3474     $tmp_membership_sql_list = implode(', ', $value);
3475     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3476                           SELECT DISTINCT mem.contact_id
3477                           FROM civicrm_membership mem
3478                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3479                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3480                           WHERE mem.membership_type_id IN (" .
3481     $tmp_membership_sql_list . ")
3482                           AND mt.is_active = '1'
3483                           AND mem_status.is_current_member = '1'
3484                           AND mem_status.is_active = '1' ) ";
3485   }
3486
3487   /**
3488    * Build acl clauses.
3489    *
3490    * @param string $tableAlias
3491    */
3492   public function buildACLClause($tableAlias = 'contact_a') {
3493     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
3494   }
3495
3496   /**
3497    * Add custom data to the columns.
3498    *
3499    * @param bool $addFields
3500    * @param array $permCustomGroupIds
3501    */
3502   public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
3503     if (empty($this->_customGroupExtends)) {
3504       return;
3505     }
3506     if (!is_array($this->_customGroupExtends)) {
3507       $this->_customGroupExtends = array($this->_customGroupExtends);
3508     }
3509     $customGroupWhere = '';
3510     if (!empty($permCustomGroupIds)) {
3511       $customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) .
3512         ") AND";
3513     }
3514     $sql = "
3515 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
3516        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
3517 FROM   civicrm_custom_group cg
3518 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
3519 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
3520       {$customGroupWhere}
3521       cg.is_active = 1 AND
3522       cf.is_active = 1 AND
3523       cf.is_searchable = 1
3524 ORDER BY cg.weight, cf.weight";
3525     $customDAO = CRM_Core_DAO::executeQuery($sql);
3526
3527     $curTable = NULL;
3528     while ($customDAO->fetch()) {
3529       if ($customDAO->table_name != $curTable) {
3530         $curTable = $customDAO->table_name;
3531         $curFields = $curFilters = array();
3532
3533         // dummy dao object
3534         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
3535         $this->_columns[$curTable]['extends'] = $customDAO->extends;
3536         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
3537         $this->_columns[$curTable]['group_title'] = $customDAO->title;
3538
3539         foreach (array('fields', 'filters', 'group_bys') as $colKey) {
3540           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
3541             $this->_columns[$curTable][$colKey] = array();
3542           }
3543         }
3544       }
3545       $fieldName = 'custom_' . $customDAO->cf_id;
3546
3547       if ($addFields) {
3548         // this makes aliasing work in favor
3549         $curFields[$fieldName] = array(
3550           'name' => $customDAO->column_name,
3551           'title' => $customDAO->label,
3552           'dataType' => $customDAO->data_type,
3553           'htmlType' => $customDAO->html_type,
3554         );
3555       }
3556       if ($this->_customGroupFilters) {
3557         // this makes aliasing work in favor
3558         $curFilters[$fieldName] = array(
3559           'name' => $customDAO->column_name,
3560           'title' => $customDAO->label,
3561           'dataType' => $customDAO->data_type,
3562           'htmlType' => $customDAO->html_type,
3563         );
3564       }
3565
3566       switch ($customDAO->data_type) {
3567         case 'Date':
3568           // filters
3569           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
3570           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
3571           // CRM-6946, show time part for datetime date fields
3572           if ($customDAO->time_format) {
3573             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
3574           }
3575           break;
3576
3577         case 'Boolean':
3578           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
3579           $curFilters[$fieldName]['options'] = array('' => ts('- select -'))
3580             + CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
3581           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3582           break;
3583
3584         case 'Int':
3585           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
3586           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3587           break;
3588
3589         case 'Money':
3590           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3591           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3592           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3593           break;
3594
3595         case 'Float':
3596           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3597           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
3598           break;
3599
3600         case 'String':
3601         case 'StateProvince':
3602         case 'Country':
3603           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3604
3605           $options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
3606           if ($options !== FALSE) {
3607             $curFilters[$fieldName]['operatorType'] = CRM_Core_BAO_CustomField::isSerialized($customDAO) ? CRM_Report_Form::OP_MULTISELECT_SEPARATOR : CRM_Report_Form::OP_MULTISELECT;
3608             $curFilters[$fieldName]['options'] = $options;
3609           }
3610           break;
3611
3612         case 'ContactReference':
3613           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3614           $curFilters[$fieldName]['name'] = 'display_name';
3615           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3616
3617           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3618           $curFields[$fieldName]['name'] = 'display_name';
3619           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3620           break;
3621
3622         default:
3623           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3624           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3625       }
3626
3627       if (!array_key_exists('type', $curFields[$fieldName])) {
3628         $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
3629       }
3630
3631       if ($addFields) {
3632         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
3633       }
3634       if ($this->_customGroupFilters) {
3635         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
3636       }
3637       if ($this->_customGroupGroupBy) {
3638         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
3639       }
3640     }
3641   }
3642
3643   /**
3644    * Build custom data from clause.
3645    *
3646    * @param bool $joinsForFiltersOnly
3647    *   Only include joins to support filters. This would be used if creating a table of contacts to include first.
3648    */
3649   public function customDataFrom($joinsForFiltersOnly = FALSE) {
3650     if (empty($this->_customGroupExtends)) {
3651       return;
3652     }
3653     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
3654     $customTables = explode(',', CRM_Core_DAO::singleValueQuery("SELECT GROUP_CONCAT(table_name) FROM civicrm_custom_group"));
3655
3656     foreach ($this->_columns as $table => $prop) {
3657       if (in_array($table, $customTables)) {
3658         $extendsTable = $mapper[$prop['extends']];
3659         // Check field is required for rendering the report.
3660         if ((!$this->isFieldSelected($prop)) || ($joinsForFiltersOnly && !$this->isFieldFiltered($prop))) {
3661           continue;
3662         }
3663         $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id");
3664
3665         $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
3666         $this->_from .= "
3667 {$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$baseJoin}";
3668         // handle for ContactReference
3669         if (array_key_exists('fields', $prop)) {
3670           foreach ($prop['fields'] as $fieldName => $field) {
3671             if (CRM_Utils_Array::value('dataType', $field) ==
3672               'ContactReference'
3673             ) {
3674               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
3675               $this->_from .= "
3676 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
3677             }
3678           }
3679         }
3680       }
3681     }
3682   }
3683
3684   /**
3685    * Check if the field is selected.
3686    *
3687    * @param string $prop
3688    *
3689    * @return bool
3690    */
3691   public function isFieldSelected($prop) {
3692     if (empty($prop)) {
3693       return FALSE;
3694     }
3695
3696     if (!empty($this->_params['fields'])) {
3697       foreach (array_keys($prop['fields']) as $fieldAlias) {
3698         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
3699         if ($customFieldId) {
3700           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
3701             return TRUE;
3702           }
3703
3704           //might be survey response field.
3705           if (!empty($this->_params['fields']['survey_response']) &&
3706             !empty($prop['fields'][$fieldAlias]['isSurveyResponseField'])
3707           ) {
3708             return TRUE;
3709           }
3710         }
3711       }
3712     }
3713
3714     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
3715       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
3716         if (array_key_exists($fieldAlias, $this->_params['group_bys']) &&
3717           CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3718         ) {
3719           return TRUE;
3720         }
3721       }
3722     }
3723
3724     if (!empty($this->_params['order_bys'])) {
3725       foreach (array_keys($prop['fields']) as $fieldAlias) {
3726         foreach ($this->_params['order_bys'] as $orderBy) {
3727           if ($fieldAlias == $orderBy['column'] &&
3728             CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3729           ) {
3730             return TRUE;
3731           }
3732         }
3733       }
3734     }
3735     return $this->isFieldFiltered($prop);
3736
3737   }
3738
3739   /**
3740    * Check if the field is used as a filter.
3741    *
3742    * @param string $prop
3743    *
3744    * @return bool
3745    */
3746   protected function isFieldFiltered($prop) {
3747     if (!empty($prop['filters']) && $this->_customGroupFilters) {
3748       foreach ($prop['filters'] as $fieldAlias => $val) {
3749         foreach (array(
3750                    'value',
3751                    'min',
3752                    'max',
3753                    'relative',
3754                    'from',
3755                    'to',
3756                  ) as $attach) {
3757           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
3758             (!empty($this->_params[$fieldAlias . '_' . $attach])
3759               || ($attach != 'relative' &&
3760                 $this->_params[$fieldAlias . '_' . $attach] == '0')
3761             )
3762           ) {
3763             return TRUE;
3764           }
3765         }
3766         if (!empty($this->_params[$fieldAlias . '_op']) &&
3767           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
3768         ) {
3769           return TRUE;
3770         }
3771       }
3772     }
3773
3774     return FALSE;
3775   }
3776
3777   /**
3778    * Check for empty order_by configurations and remove them.
3779    *
3780    * Also set template to hide them.
3781    *
3782    * @param array $formValues
3783    */
3784   public function preProcessOrderBy(&$formValues) {
3785     // Object to show/hide form elements
3786     $_showHide = new CRM_Core_ShowHideBlocks('', '');
3787
3788     $_showHide->addShow('optionField_1');
3789
3790     // Cycle through order_by options; skip any empty ones, and hide them as well
3791     $n = 1;
3792
3793     if (!empty($formValues['order_bys'])) {
3794       foreach ($formValues['order_bys'] as $order_by) {
3795         if ($order_by['column'] && $order_by['column'] != '-') {
3796           $_showHide->addShow('optionField_' . $n);
3797           $orderBys[$n] = $order_by;
3798           $n++;
3799         }
3800       }
3801     }
3802     for ($i = $n; $i <= 5; $i++) {
3803       if ($i > 1) {
3804         $_showHide->addHide('optionField_' . $i);
3805       }
3806     }
3807
3808     // overwrite order_by options with modified values
3809     if (!empty($orderBys)) {
3810       $formValues['order_bys'] = $orderBys;
3811     }
3812     else {
3813       $formValues['order_bys'] = array(1 => array('column' => '-'));
3814     }
3815
3816     // assign show/hide data to template
3817     $_showHide->addToTemplate();
3818   }
3819
3820   /**
3821    * Check if table name has columns in SELECT clause.
3822    *
3823    * @param string $tableName
3824    *   Name of table (index of $this->_columns array).
3825    *
3826    * @return bool
3827    */
3828   public function isTableSelected($tableName) {
3829     return in_array($tableName, $this->selectedTables());
3830   }
3831
3832   /**
3833    * Check if table name has columns in WHERE or HAVING clause.
3834    *
3835    * @param string $tableName
3836    *   Name of table (index of $this->_columns array).
3837    *
3838    * @return bool
3839    */
3840   public function isTableFiltered($tableName) {
3841     // Cause the array to be generated if not previously done.
3842     if (!$this->_selectedTables && !$this->filteredTables) {
3843       $this->selectedTables();
3844     }
3845     return in_array($tableName, $this->filteredTables);
3846   }
3847
3848   /**
3849    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause.
3850    *
3851    * If the array is unset it will be built.
3852    *
3853    * @return array
3854    *   selectedTables
3855    */
3856   public function selectedTables() {
3857     if (!$this->_selectedTables) {
3858       $orderByColumns = array();
3859       if (array_key_exists('order_bys', $this->_params) &&
3860         is_array($this->_params['order_bys'])
3861       ) {
3862         foreach ($this->_params['order_bys'] as $orderBy) {
3863           $orderByColumns[] = $orderBy['column'];
3864         }
3865       }
3866
3867       foreach ($this->_columns as $tableName => $table) {
3868         if (array_key_exists('fields', $table)) {
3869           foreach ($table['fields'] as $fieldName => $field) {
3870             if (!empty($field['required']) ||
3871               !empty($this->_params['fields'][$fieldName])
3872             ) {
3873               $this->_selectedTables[] = $tableName;
3874               break;
3875             }
3876           }
3877         }
3878         if (array_key_exists('order_bys', $table)) {
3879           foreach ($table['order_bys'] as $orderByName => $orderBy) {
3880             if (in_array($orderByName, $orderByColumns)) {
3881               $this->_selectedTables[] = $tableName;
3882               break;
3883             }
3884           }
3885         }
3886         if (array_key_exists('filters', $table)) {
3887           foreach ($table['filters'] as $filterName => $filter) {
3888             if (!empty($this->_params["{$filterName}_value"]) ||
3889               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3890               'nll' ||
3891               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3892               'nnll'
3893             ) {
3894               $this->_selectedTables[] = $tableName;
3895               $this->filteredTables[] = $tableName;
3896               break;
3897             }
3898           }
3899         }
3900       }
3901     }
3902     return $this->_selectedTables;
3903   }
3904
3905   /**
3906    * Add address fields.
3907    *
3908    * @deprecated - use getAddressColumns which is a more accurate description
3909    * and also accepts an array of options rather than a long list
3910    *
3911    * adding address fields to construct function in reports
3912    *
3913    * @param bool $groupBy
3914    *   Add GroupBy? Not appropriate for detail report.
3915    * @param bool $orderBy
3916    *   Add GroupBy? Not appropriate for detail report.
3917    * @param bool $filters
3918    * @param array $defaults
3919    *
3920    * @return array
3921    *   address fields for construct clause
3922    */
3923   public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
3924     $addressFields = array(
3925       'civicrm_address' => array(
3926         'dao' => 'CRM_Core_DAO_Address',
3927         'fields' => array(
3928           'address_name' => array(
3929             'title' => ts('Address Name'),
3930             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
3931             'name' => 'name',
3932           ),
3933           'street_address' => array(
3934             'title' => ts('Street Address'),
3935             'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
3936           ),
3937           'supplemental_address_1' => array(
3938             'title' => ts('Supplementary Address Field 1'),
3939             'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
3940           ),
3941           'supplemental_address_2' => array(
3942             'title' => ts('Supplementary Address Field 2'),
3943             'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
3944           ),
3945           'street_number' => array(
3946             'name' => 'street_number',
3947             'title' => ts('Street Number'),
3948             'type' => 1,
3949             'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
3950           ),
3951           'street_name' => array(
3952             'name' => 'street_name',
3953             'title' => ts('Street Name'),
3954             'type' => 1,
3955             'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
3956           ),
3957           'street_unit' => array(
3958             'name' => 'street_unit',
3959             'title' => ts('Street Unit'),
3960             'type' => 1,
3961             'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
3962           ),
3963           'city' => array(
3964             'title' => ts('City'),
3965             'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
3966           ),
3967           'postal_code' => array(
3968             'title' => ts('Postal Code'),
3969             'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
3970           ),
3971           'postal_code_suffix' => array(
3972             'title' => ts('Postal Code Suffix'),
3973             'default' => CRM_Utils_Array::value('postal_code_suffix', $defaults, FALSE),
3974           ),
3975           'country_id' => array(
3976             'title' => ts('Country'),
3977             'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
3978           ),
3979           'state_province_id' => array(
3980             'title' => ts('State/Province'),
3981             'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
3982           ),
3983           'county_id' => array(
3984             'title' => ts('County'),
3985             'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
3986           ),
3987         ),
3988         'grouping' => 'location-fields',
3989       ),
3990     );
3991
3992     if ($filters) {
3993       $addressFields['civicrm_address']['filters'] = array(
3994         'street_number' => array(
3995           'title' => ts('Street Number'),
3996           'type' => 1,
3997           'name' => 'street_number',
3998         ),
3999         'street_name' => array(
4000           'title' => ts('Street Name'),
4001           'name' => 'street_name',
4002           'operator' => 'like',
4003         ),
4004         'postal_code' => array(
4005           'title' => ts('Postal Code'),
4006           'type' => 1,
4007           'name' => 'postal_code',
4008         ),
4009         'city' => array(
4010           'title' => ts('City'),
4011           'operator' => 'like',
4012           'name' => 'city',
4013         ),
4014         'country_id' => array(
4015           'name' => 'country_id',
4016           'title' => ts('Country'),
4017           'type' => CRM_Utils_Type::T_INT,
4018           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4019           'options' => CRM_Core_PseudoConstant::country(),
4020         ),
4021         'state_province_id' => array(
4022           'name' => 'state_province_id',
4023           'title' => ts('State/Province'),
4024           'type' => CRM_Utils_Type::T_INT,
4025           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4026           'options' => array(),
4027         ),
4028         'county_id' => array(
4029           'name' => 'county_id',
4030           'title' => ts('County'),
4031           'type' => CRM_Utils_Type::T_INT,
4032           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4033           'options' => array(),
4034         ),
4035       );
4036     }
4037
4038     if ($orderBy) {
4039       $addressFields['civicrm_address']['order_bys'] = array(
4040         'street_name' => array('title' => ts('Street Name')),
4041         'street_number' => array('title' => 'Odd / Even Street Number'),
4042         'street_address' => NULL,
4043         'city' => NULL,
4044         'postal_code' => NULL,
4045       );
4046     }
4047
4048     if ($groupBy) {
4049       $addressFields['civicrm_address']['group_bys'] = array(
4050         'street_address' => NULL,
4051         'city' => NULL,
4052         'postal_code' => NULL,
4053         'state_province_id' => array(
4054           'title' => ts('State/Province'),
4055         ),
4056         'country_id' => array(
4057           'title' => ts('Country'),
4058         ),
4059         'county_id' => array(
4060           'title' => ts('County'),
4061         ),
4062       );
4063     }
4064     return $addressFields;
4065   }
4066
4067   /**
4068    * Do AlterDisplay processing on Address Fields.
4069    *
4070    * @param array $row
4071    * @param array $rows
4072    * @param int $rowNum
4073    * @param string $baseUrl
4074    * @param string $linkText
4075    *
4076    * @return bool
4077    */
4078   public function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4079     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4080     $entryFound = FALSE;
4081     // handle country
4082     if (array_key_exists('civicrm_address_country_id', $row)) {
4083       if ($value = $row['civicrm_address_country_id']) {
4084         $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
4085         if ($baseUrl) {
4086           $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4087             "reset=1&force=1&{$criteriaQueryParams}&" .
4088             "country_id_op=in&country_id_value={$value}",
4089             $this->_absoluteUrl, $this->_id
4090           );
4091           $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
4092           $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
4093             array(1 => $linkText)
4094           );
4095         }
4096       }
4097
4098       $entryFound = TRUE;
4099     }
4100     if (array_key_exists('civicrm_address_county_id', $row)) {
4101       if ($value = $row['civicrm_address_county_id']) {
4102         $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
4103         if ($baseUrl) {
4104           $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4105             "reset=1&force=1&{$criteriaQueryParams}&" .
4106             "county_id_op=in&county_id_value={$value}",
4107             $this->_absoluteUrl, $this->_id
4108           );
4109           $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
4110           $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
4111             array(1 => $linkText)
4112           );
4113         }
4114       }
4115       $entryFound = TRUE;
4116     }
4117     // handle state province
4118     if (array_key_exists('civicrm_address_state_province_id', $row)) {
4119       if ($value = $row['civicrm_address_state_province_id']) {
4120         $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
4121         if ($baseUrl) {
4122           $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4123             "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
4124             $this->_absoluteUrl, $this->_id
4125           );
4126           $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
4127           $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
4128             array(1 => $linkText)
4129           );
4130         }
4131       }
4132       $entryFound = TRUE;
4133     }
4134
4135     return $entryFound;
4136   }
4137
4138   /**
4139    * Do AlterDisplay processing on Address Fields.
4140    *
4141    * @param array $row
4142    * @param array $rows
4143    * @param int $rowNum
4144    * @param string $baseUrl
4145    * @param string $linkText
4146    *
4147    * @return bool
4148    */
4149   public function alterDisplayContactFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4150     $entryFound = FALSE;
4151     // There is no reason not to add links for all fields but it seems a bit odd to be able to click on
4152     // 'Mrs'. Also, we don't have metadata about the title. So, add selectively to addLinks.
4153     $addLinks = array('gender_id' => 'Gender');
4154     foreach (array('prefix_id', 'suffix_id', 'gender_id') as $fieldName) {
4155       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4156         if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) {
4157           $rows[$rowNum]['civicrm_contact_' . $fieldName] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $value);
4158           if ($baseUrl && ($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) {
4159             $this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title);
4160           }
4161         }
4162         $entryFound = TRUE;
4163       }
4164     }
4165     $yesNoFields = array(
4166       'do_not_email', 'is_deceased', 'do_not_phone', 'do_not_sms', 'do_not_mail', 'is_opt_out',
4167     );
4168     foreach ($yesNoFields as $fieldName) {
4169       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4170         // Since these are essentially 'negative fields' it feels like it
4171         // makes sense to only highlight the exceptions hence no 'No'.
4172         $rows[$rowNum]['civicrm_contact_' . $fieldName] = !empty($rows[$rowNum]['civicrm_contact_' . $fieldName]) ? ts('Yes') : '';
4173         $entryFound = TRUE;
4174       }
4175     }
4176     return $entryFound;
4177   }
4178
4179   /**
4180    * Adjusts dates passed in to YEAR() for fiscal year.
4181    *
4182    * @param string $fieldName
4183    *
4184    * @return string
4185    */
4186   public function fiscalYearOffset($fieldName) {
4187     $config = CRM_Core_Config::singleton();
4188     $fy = $config->fiscalYearStart;
4189     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' ||
4190       ($fy['d'] == 1 && $fy['M'] == 1)
4191     ) {
4192       return "YEAR( $fieldName )";
4193     }
4194     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" .
4195     ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
4196   }
4197
4198   /**
4199    * Add Address into From Table if required.
4200    */
4201   public function addAddressFromClause() {
4202     // include address field if address column is to be included
4203     if ((isset($this->_addressField) &&
4204         $this->_addressField
4205       ) ||
4206       $this->isTableSelected('civicrm_address')
4207     ) {
4208       $this->_from .= "
4209                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4210                            ON ({$this->_aliases['civicrm_contact']}.id =
4211                                {$this->_aliases['civicrm_address']}.contact_id) AND
4212                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4213     }
4214   }
4215
4216   /**
4217    * Add Phone into From Table if required.
4218    */
4219   public function addPhoneFromClause() {
4220     // include address field if address column is to be included
4221     if ($this->isTableSelected('civicrm_phone')
4222     ) {
4223       $this->_from .= "
4224       LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
4225       ON ({$this->_aliases['civicrm_contact']}.id =
4226       {$this->_aliases['civicrm_phone']}.contact_id) AND
4227       {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
4228     }
4229   }
4230
4231   /**
4232    * Get phone columns to add to array.
4233    *
4234    * @param array $options
4235    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4236    *   - prefix_label Label to give columns from this phone table instance
4237    *
4238    * @return array
4239    *   phone columns definition
4240    */
4241   public function getPhoneColumns($options = array()) {
4242     $defaultOptions = array(
4243       'prefix' => '',
4244       'prefix_label' => '',
4245     );
4246
4247     $options = array_merge($defaultOptions, $options);
4248
4249     $fields = array(
4250       $options['prefix'] . 'civicrm_phone' => array(
4251         'dao' => 'CRM_Core_DAO_Phone',
4252         'fields' => array(
4253           $options['prefix'] . 'phone' => array(
4254             'title' => ts($options['prefix_label'] . 'Phone'),
4255             'name' => 'phone',
4256           ),
4257         ),
4258       ),
4259     );
4260     return $fields;
4261   }
4262
4263   /**
4264    * Get address columns to add to array.
4265    *
4266    * @param array $options
4267    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4268    *   - prefix_label Label to give columns from this address table instance
4269    *
4270    * @return array
4271    *   address columns definition
4272    */
4273   public function getAddressColumns($options = array()) {
4274     $options += array(
4275       'prefix' => '',
4276       'prefix_label' => '',
4277       'group_by' => TRUE,
4278       'order_by' => TRUE,
4279       'filters' => TRUE,
4280       'defaults' => array(),
4281     );
4282     return $this->addAddressFields(
4283       $options['group_by'],
4284       $options['order_by'],
4285       $options['filters'],
4286       $options['defaults']
4287     );
4288   }
4289
4290   /**
4291    * Get a standard set of contact fields.
4292    *
4293    * @return array
4294    */
4295   public function getBasicContactFields() {
4296     return array(
4297       'sort_name' => array(
4298         'title' => ts('Contact Name'),
4299         'required' => TRUE,
4300         'default' => TRUE,
4301       ),
4302       'id' => array(
4303         'no_display' => TRUE,
4304         'required' => TRUE,
4305       ),
4306       'prefix_id' => array(
4307         'title' => ts('Contact Prefix'),
4308       ),
4309       'first_name' => array(
4310         'title' => ts('First Name'),
4311       ),
4312       'nick_name' => array(
4313         'title' => ts('Nick Name'),
4314       ),
4315       'middle_name' => array(
4316         'title' => ts('Middle Name'),
4317       ),
4318       'last_name' => array(
4319         'title' => ts('Last Name'),
4320       ),
4321       'suffix_id' => array(
4322         'title' => ts('Contact Suffix'),
4323       ),
4324       'postal_greeting_display' => array('title' => ts('Postal Greeting')),
4325       'email_greeting_display' => array('title' => ts('Email Greeting')),
4326       'addressee_display' => array('title' => ts('Address Greeting')),
4327       'contact_type' => array(
4328         'title' => ts('Contact Type'),
4329       ),
4330       'contact_sub_type' => array(
4331         'title' => ts('Contact Subtype'),
4332       ),
4333       'gender_id' => array(
4334         'title' => ts('Gender'),
4335       ),
4336       'birth_date' => array(
4337         'title' => ts('Birth Date'),
4338       ),
4339       'age' => array(
4340         'title' => ts('Age'),
4341         'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
4342       ),
4343       'job_title' => array(
4344         'title' => ts('Contact Job title'),
4345       ),
4346       'organization_name' => array(
4347         'title' => ts('Organization Name'),
4348       ),
4349       'external_identifier' => array(
4350         'title' => ts('Contact identifier from external system'),
4351       ),
4352       'do_not_email' => array(),
4353       'do_not_phone' => array(),
4354       'do_not_mail' => array(),
4355       'do_not_sms' => array(),
4356       'is_opt_out' => array(),
4357       'is_deceased' => array(),
4358     );
4359   }
4360
4361   /**
4362    * Add contact to group.
4363    *
4364    * @param int $groupID
4365    */
4366   public function add2group($groupID) {
4367     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
4368       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
4369
4370       // here are we are prepending / adding  contact id field that could be used for adding group
4371       // so first check for "SELECT SQL_CALC_FOUND_ROWS" and if does not exist replace "SELECT"
4372       if (preg_match('/^SELECT SQL_CALC_FOUND_ROWS/', $this->_select)) {
4373         $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
4374       }
4375       else {
4376         $select = str_ireplace('SELECT ', $select, $this->_select);
4377       }
4378
4379       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
4380       $sql = str_replace('WITH ROLLUP', '', $sql);
4381       $dao = CRM_Core_DAO::executeQuery($sql);
4382
4383       $contact_ids = array();
4384       // Add resulting contacts to group
4385       while ($dao->fetch()) {
4386         if ($dao->addtogroup_contact_id) {
4387           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
4388         }
4389       }
4390
4391       if (!empty($contact_ids)) {
4392         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
4393         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
4394       }
4395       else {
4396         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
4397       }
4398     }
4399   }
4400
4401   /**
4402    * Show charts on print screen.
4403    */
4404   public static function uploadChartImage() {
4405     // upload strictly for '.png' images
4406     $name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
4407     if (preg_match('/\.png$/', $name)) {
4408       //
4409       // POST data is usually string data, but we are passing a RAW .png
4410       // so PHP is a bit confused and $_POST is empty. But it has saved
4411       // the raw bits into $HTTP_RAW_POST_DATA
4412       //
4413       $httpRawPostData = $GLOBALS['HTTP_RAW_POST_DATA'];
4414
4415       // prepare the directory
4416       $config = CRM_Core_Config::singleton();
4417       $defaultPath
4418         = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
4419         '/openFlashChart/';
4420       if (!file_exists($defaultPath)) {
4421         mkdir($defaultPath, 0777, TRUE);
4422       }
4423
4424       // full path to the saved image including filename
4425       $destination = $defaultPath . $name;
4426
4427       //write and save
4428       $jfh = fopen($destination, 'w') or die("can't open file");
4429       fwrite($jfh, $httpRawPostData);
4430       fclose($jfh);
4431       CRM_Utils_System::civiExit();
4432     }
4433   }
4434
4435   /**
4436    * Apply common settings to entityRef fields.
4437    *
4438    * @param array $field
4439    * @param string $table
4440    */
4441   private function setEntityRefDefaults(&$field, $table) {
4442     $field['attributes'] = $field['attributes'] ? $field['attributes'] : array();
4443     $field['attributes'] += array(
4444       'entity' => CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table)),
4445       'multiple' => TRUE,
4446       'placeholder' => ts('- select -'),
4447     );
4448   }
4449
4450   /**
4451    * Add link fields to the row.
4452    *
4453    * Function adds the _link & _hover fields to the row.
4454    *
4455    * @param array $row
4456    * @param string $baseUrl
4457    * @param string $linkText
4458    * @param string $value
4459    * @param string $fieldName
4460    * @param string $tablePrefix
4461    * @param string $fieldLabel
4462    *
4463    * @return mixed
4464    */
4465   protected function addLinkToRow(&$row, $baseUrl, $linkText, $value, $fieldName, $tablePrefix, $fieldLabel) {
4466     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4467     $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4468       "reset=1&force=1&{$criteriaQueryParams}&" .
4469       $fieldName . "_op=in&{$fieldName}_value={$value}",
4470       $this->_absoluteUrl, $this->_id
4471     );
4472     $row["{$tablePrefix}_{$fieldName}_link"] = $url;
4473     $row["{$tablePrefix}_{$fieldName}_hover"] = ts("%1 for this %2.",
4474       array(1 => $linkText, 2 => $fieldLabel)
4475     );
4476   }
4477
4478   /**
4479    * Generate temporary table to hold all contributions with permissioned FTs.
4480    *
4481    * @param object $query
4482    * @param string $alias
4483    * @param bool $return
4484    */
4485   public function getPermissionedFTQuery(&$query, $alias = NULL, $return = FALSE) {
4486     if (!CRM_Financial_BAO_FinancialType::isACLFinancialTypeStatus()) {
4487       return FALSE;
4488     }
4489     $financialTypes = NULL;
4490     CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes);
4491     if (empty($financialTypes)) {
4492       $contFTs = "0";
4493       $liFTs = implode(',', array_keys(CRM_Contribute_Pseudoconstant::financialType()));
4494     }
4495     else {
4496       $contFTs = $liFTs = implode(',', array_keys($financialTypes));
4497     }
4498     if ($alias) {
4499       $temp = CRM_Utils_Array::value('civicrm_line_item', $query->_aliases);
4500       $query->_aliases['civicrm_line_item'] = $alias;
4501     }
4502     if (empty($query->_where)) {
4503       $query->_where = "WHERE {$query->_aliases['civicrm_contribution']}.id IS NOT NULL ";
4504     }
4505     CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS civicrm_contribution_temp");
4506     $sql = "CREATE TEMPORARY TABLE civicrm_contribution_temp AS SELECT {$query->_aliases['civicrm_contribution']}.id {$query->_from}
4507               LEFT JOIN civicrm_line_item   {$query->_aliases['civicrm_line_item']}
4508                       ON {$query->_aliases['civicrm_contribution']}.id = {$query->_aliases['civicrm_line_item']}.contribution_id AND
4509                          {$query->_aliases['civicrm_line_item']}.entity_table = 'civicrm_contribution'
4510                       AND {$query->_aliases['civicrm_line_item']}.financial_type_id NOT IN (" . $liFTs . ")
4511               {$query->_where}
4512                       AND {$query->_aliases['civicrm_contribution']}.financial_type_id IN (" . $contFTs . ")
4513                       AND {$query->_aliases['civicrm_line_item']}.id IS NULL
4514               GROUP BY {$query->_aliases['civicrm_contribution']}.id";
4515     CRM_Core_DAO::executeQuery($sql);
4516     if (isset($temp)) {
4517       $query->_aliases['civicrm_line_item'] = $temp;
4518     }
4519     $from = " INNER JOIN civicrm_contribution_temp temp ON {$query->_aliases['civicrm_contribution']}.id = temp.id ";
4520     if ($return) {
4521       return $from;
4522     }
4523     $query->_from .= $from;
4524   }
4525
4526   /**
4527    * Get label for show results buttons.
4528    *
4529    * @return string
4530    */
4531   public function getResultsLabel() {
4532     $showResultsLabel = $this->resultsDisplayed() ? ts('Refresh results') : ts('View results');
4533     return $showResultsLabel;
4534   }
4535
4536   /**
4537    * Determine the output mode from the url or input.
4538    *
4539    * Output could be
4540    *   - pdf : Render as pdf
4541    *   - csv : Render as csv
4542    *   - print : Render in print format
4543    *   - save : save the report and display the new report
4544    *   - copy : save the report as a new instance and display that.
4545    *   - group : go to the add to group screen.
4546    *
4547    *  Potentially chart variations could also be included but the complexity
4548    *   is that we might print a bar chart as a pdf.
4549    */
4550   protected function setOutputMode() {
4551     $buttonName = $this->controller->getButtonName();
4552     $this->_outputMode = CRM_Utils_Request::retrieve(
4553       'output',
4554       'String',
4555       CRM_Core_DAO::$_nullObject,
4556       FALSE,
4557       CRM_Utils_Array::value('task', $this->_params)
4558     );
4559
4560     if ($buttonName) {
4561       if ($buttonName == $this->_instanceButtonName) {
4562         $this->_outputMode = 'save';
4563       }
4564       if ($buttonName == $this->_printButtonName) {
4565         $this->_outputMode = 'print';
4566       }
4567       if ($buttonName == $this->_pdfButtonName) {
4568         $this->_outputMode = 'pdf';
4569       }
4570       if ($this->_csvButtonName == $buttonName) {
4571         $this->_outputMode = 'csv';
4572       }
4573       if ($this->_groupButtonName == $buttonName) {
4574         $this->_outputMode = 'group';
4575       }
4576       if ($buttonName == $this->_createNewButtonName) {
4577         $this->_outputMode = 'copy';
4578       }
4579     }
4580   }
4581
4582   /**
4583    * CRM-17793 - Alter DateTime section header to group by date from the datetime field.
4584    * @param $tempTable
4585    * @param $columnName
4586    */
4587   public function alterSectionHeaderForDateTime($tempTable, $columnName) {
4588     // add new column with date value for the datetime field
4589     $tempQuery = "ALTER TABLE {$tempTable} ADD COLUMN {$columnName}_date VARCHAR(128)";
4590     CRM_Core_DAO::executeQuery($tempQuery);
4591     $updateQuery = "UPDATE {$tempTable} SET {$columnName}_date = date({$columnName})";
4592     CRM_Core_DAO::executeQuery($updateQuery);
4593     $this->_select .= ", {$columnName}_date";
4594     $this->_sections["{$columnName}_date"] = $this->_sections["{$columnName}"];
4595     unset($this->_sections["{$columnName}"]);
4596     $this->assign('sections', $this->_sections);
4597   }
4598
4599   /**
4600    * Get an array of the columns that have been selected for display.
4601    *
4602    * @return array
4603    */
4604   public function getSelectColumns() {
4605     $selectColumns = array();
4606     foreach ($this->_columns as $tableName => $table) {
4607       if (array_key_exists('fields', $table)) {
4608         foreach ($table['fields'] as $fieldName => $field) {
4609           if (!empty($field['required']) ||
4610             !empty($this->_params['fields'][$fieldName])
4611           ) {
4612
4613             $selectColumns["{$tableName}_{$fieldName}"] = 1;
4614           }
4615         }
4616       }
4617     }
4618     return $selectColumns;
4619   }
4620
4621   /**
4622    * Add location tables to the query if they are used for filtering.
4623    *
4624    * This is for when we are running the query separately for filtering and retrieving display fields.
4625    */
4626   public function selectivelyAddLocationTablesJoinsToFilterQuery() {
4627     if ($this->isTableFiltered('civicrm_email')) {
4628       $this->_from .= "
4629           LEFT  JOIN civicrm_email  {$this->_aliases['civicrm_email']}
4630             ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
4631             AND {$this->_aliases['civicrm_email']}.is_primary = 1";
4632     }
4633     if ($this->isTableFiltered('civicrm_phone')) {
4634       $this->_from .= "
4635           LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
4636             ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id
4637             AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
4638     }
4639     if ($this->isTableFiltered('civicrm_address')) {
4640       $this->_from .= "
4641           LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4642           ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id)
4643           AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4644     }
4645   }
4646
4647 }