CRM/Report/Form.php

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