CRM/Report/Form.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 5                                                  |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2019                                |
   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   public $_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    * Has the report been optimised for group filtering.
 157    *
 158    * The functionality for group filtering has been improved but not
 159    * all reports have been adjusted to take care of it.
 160    *
 161    * This property exists to highlight the reports which are still using the
 162    * slow method & allow group filtering to still work for them until they
 163    * can be migrated.
 164    *
 165    * In order to protect extensions we have to default to TRUE - but I have
 166    * separately marked every class with a groupFilter in the hope that will trigger
 167    * people to fix them as they touch them.
 168    *
 169    * CRM-19170
 170    *
 171    * @var bool
 172    */
 173   protected $groupFilterNotOptimised = TRUE;
 174
 175   /**
 176    * Navigation fields
 177    *
 178    * @var array
 179    */
 180   public $_navigation = array();
 181
 182   public $_drilldownReport = array();
 183
 184   /**
 185    * Array of tabs to display on report.
 186    *
 187    * E.g we define the tab title, the tpl and the tab-specific part of the css or  html link.
 188    *
 189    *  $this->tabs['OrderBy'] = array(
 190    *    'title' => ts('Sorting'),
 191    *    'tpl' => 'OrderBy',
 192    *    'div_label' => 'order-by',
 193    *  );
 194    *
 195    * @var array
 196    */
 197   protected $tabs = array();
 198
 199   /**
 200    * Should we add paging.
 201    *
 202    * @var bool
 203    */
 204   protected $addPaging = TRUE;
 205
 206   protected $isForceGroupBy = FALSE;
 207
 208   protected $groupConcatTested = FALSE;
 209
 210   /**
 211    * An attribute for checkbox/radio form field layout
 212    *
 213    * @var array
 214    */
 215   protected $_fourColumnAttribute = array(
 216     '</td><td width="25%">',
 217     '</td><td width="25%">',
 218     '</td><td width="25%">',
 219     '</tr><tr><td>',
 220   );
 221
 222   protected $_force = 1;
 223
 224   protected $_params = NULL;
 225   protected $_formValues = NULL;
 226   protected $_instanceValues = NULL;
 227
 228   protected $_instanceForm = FALSE;
 229   protected $_criteriaForm = FALSE;
 230
 231   protected $_instanceButtonName = NULL;
 232   protected $_createNewButtonName = NULL;
 233   protected $_printButtonName = NULL;
 234   protected $_pdfButtonName = NULL;
 235   protected $_csvButtonName = NULL;
 236   protected $_groupButtonName = NULL;
 237   protected $_chartButtonName = NULL;
 238   protected $_csvSupported = TRUE;
 239   protected $_add2groupSupported = TRUE;
 240   protected $_groups = NULL;
 241   protected $_grandFlag = FALSE;
 242   protected $_rowsFound = NULL;
 243   protected $_selectAliases = array();
 244   protected $_rollup = NULL;
 245
 246   /**
 247    * Table containing list of contact IDs within the group filter.
 248    *
 249    * @var string
 250    */
 251   protected $groupTempTable = '';
 252
 253   /**
 254    * @var array
 255    */
 256   protected $_aliases = array();
 257
 258   /**
 259    * @var string
 260    */
 261   protected $_where;
 262
 263   /**
 264    * @var string
 265    */
 266   protected $_from;
 267
 268   /**
 269    * SQL Limit clause
 270    * @var  string
 271    */
 272   protected $_limit = NULL;
 273
 274   /**
 275    * This can be set to specify a limit to the number of rows
 276    * Since it is currently envisaged as part of the api usage it is only being applied
 277    * when $_output mode is not 'html' or 'group' so as not to have to interpret / mess with that part
 278    * of the code (see limit() fn.
 279    *
 280    * @var integer
 281    */
 282   protected $_limitValue = NULL;
 283
 284   /**
 285    * This can be set to specify row offset
 286    * See notes on _limitValue
 287    * @var integer
 288    */
 289   protected $_offsetValue = NULL;
 290   /**
 291    * @var null
 292    */
 293   protected $_sections = NULL;
 294   protected $_autoIncludeIndexedFieldsAsOrderBys = 0;
 295   protected $_absoluteUrl = FALSE;
 296
 297   /**
 298    * Flag to indicate if result-set is to be stored in a class variable which could be retrieved using getResultSet() method.
 299    *
 300    * @var boolean
 301    */
 302   protected $_storeResultSet = FALSE;
 303
 304   /**
 305    * When _storeResultSet Flag is set use this var to store result set in form of array
 306    *
 307    * @var boolean
 308    */
 309   protected $_resultSet = array();
 310
 311   /**
 312    * To what frequency group-by a date column
 313    *
 314    * @var array
 315    */
 316   protected $_groupByDateFreq = array(
 317     'MONTH' => 'Month',
 318     'YEARWEEK' => 'Week',
 319     'QUARTER' => 'Quarter',
 320     'YEAR' => 'Year',
 321   );
 322
 323   /**
 324    * Variables to hold the acl inner join and where clause
 325    */
 326   protected $_aclFrom = NULL;
 327   protected $_aclWhere = NULL;
 328
 329   /**
 330    * Array of DAO tables having columns included in SELECT or ORDER BY clause.
 331    *
 332    * Where has also been added to this although perhaps the 'includes both' array should have a different name.
 333    *
 334    * @var array
 335    */
 336   protected $_selectedTables = array();
 337
 338   /**
 339    * Array of DAO tables having columns included in WHERE or HAVING clause
 340    *
 341    * @var array
 342    */
 343   protected $filteredTables;
 344
 345   /**
 346    * Output mode e.g 'print', 'csv', 'pdf'.
 347    *
 348    * @var string
 349    */
 350   protected $_outputMode;
 351
 352   /**
 353    * Format of any chart in use.
 354    *
 355    * (it's unclear if this could be merged with outputMode at this stage)
 356    *
 357    * @var
 358    */
 359   protected $_format;
 360
 361   public $_having = NULL;
 362   public $_select = NULL;
 363   public $_selectClauses = array();
 364   public $_columnHeaders = array();
 365   public $_orderBy = NULL;
 366   public $_orderByFields = array();
 367   public $_orderByArray = array();
 368   /**
 369    * Array of clauses to group by.
 370    *
 371    * @var array
 372    */
 373   protected $_groupByArray = array();
 374   public $_groupBy = NULL;
 375   public $_whereClauses = array();
 376   public $_havingClauses = array();
 377
 378   /**
 379    * DashBoardRowCount Dashboard row count
 380    * @var Integer
 381    */
 382   public $_dashBoardRowCount;
 383
 384   /**
 385    * Is this being called without a form controller (ie. the report is being render outside the normal form
 386    * - e.g the api is retrieving the rows
 387    * @var boolean
 388    */
 389   public $noController = FALSE;
 390
 391   /**
 392    * Variable to hold the currency alias
 393    */
 394   protected $_currencyColumn = NULL;
 395
 396   /**
 397    * @var string
 398    */
 399   protected $_interval;
 400
 401   /**
 402    * @var bool
 403    */
 404   protected $_sendmail;
 405
 406   /**
 407    * @var int
 408    */
 409   protected $_chartId;
 410
 411   /**
 412    * @var int
 413    */
 414   public $_section;
 415
 416   /**
 417    * @var string Report description.
 418    */
 419   public $_description;
 420
 421   /**
 422    * @var bool Is an address field selected.
 423    *   This was intended to determine if the address table should be joined in
 424    *   The isTableSelected function is now preferred for this purpose
 425    */
 426   protected $_addressField;
 427
 428   /**
 429    * @var bool Is an email field selected.
 430    *   This was intended to determine if the email table should be joined in
 431    *   The isTableSelected function is now preferred for this purpose
 432    */
 433   protected $_emailField;
 434
 435   /**
 436    * @var bool Is a phone field selected.
 437    *   This was intended to determine if the phone table should be joined in
 438    *   The isTableSelected function is now preferred for this purpose
 439    */
 440   protected $_phoneField;
 441
 442   /**
 443    * @var bool Create new report instance? (or update existing) on save.
 444    */
 445   protected $_createNew;
 446
 447   /**
 448    *  When a grand total row has calculated the status we pop it off to here.
 449    *
 450    * This allows us to access it from the stats function and avoid recalculating.
 451    */
 452   protected $rollupRow = array();
 453
 454   /**
 455    * @var string Database attributes - character set and collation
 456    */
 457   protected $_databaseAttributes = ' DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci';
 458
 459   /**
 460    * SQL being run in this report.
 461    *
 462    * The sql in the report is stored in this variable in order to be displayed on the developer tab.
 463    *
 464    * @var string
 465    */
 466
 467   protected $sql;
 468
 469   /**
 470    * An instruction not to add a Group By.
 471    *
 472    * This is relevant where the group by might be otherwise added after the code that determines the group by array.
 473    *
 474    * e.g. where stat fields are being added but other settings cause it to not be desirable to add a group by
 475    * such as in pivot charts when no row header is set
 476    *
 477    * @var bool
 478    */
 479   protected $noGroupBy = FALSE;
 480
 481   /**
 482    * SQL being run in this report as an array.
 483    *
 484    * The sql in the report is stored in this variable in order to be returned to api & test calls.
 485    *
 486    * @var string
 487    */
 488
 489   protected $sqlArray;
 490
 491   /**
 492    * Tables created for the report that need removal afterwards.
 493    *
 494    * ['civicrm_temp_report_x' => ['temporary' => TRUE, 'name' => 'civicrm_temp_report_x']
 495    * @var array
 496    */
 497   protected $temporaryTables = [];
 498
 499   /**
 500    * Can this report use the sql mode ONLY_FULL_GROUP_BY.
 501    * @var bool
 502    */
 503   public $optimisedForOnlyFullGroupBy = TRUE;
 504   /**
 505    * Class constructor.
 506    */
 507   public function __construct() {
 508     parent::__construct();
 509
 510     $this->addClass('crm-report-form');
 511
 512     if ($this->_tagFilter) {
 513       $this->buildTagFilter();
 514     }
 515     if ($this->_exposeContactID) {
 516       if (array_key_exists('civicrm_contact', $this->_columns)) {
 517         $this->_columns['civicrm_contact']['fields']['exposed_id'] = array(
 518           'name' => 'id',
 519           'title' => ts('Contact ID'),
 520           'no_repeat' => TRUE,
 521         );
 522       }
 523     }
 524
 525     if ($this->_groupFilter) {
 526       $this->buildGroupFilter();
 527     }
 528
 529     // Get all custom groups
 530     $allGroups = CRM_Core_PseudoConstant::get('CRM_Core_DAO_CustomField', 'custom_group_id');
 531
 532     // Get the custom groupIds for which the user has VIEW permission
 533     // If the user has 'access all custom data' permission, we'll leave $permCustomGroupIds empty
 534     // and addCustomDataToColumns() will allow access to all custom groups.
 535     $permCustomGroupIds = array();
 536     if (!CRM_Core_Permission::check('access all custom data')) {
 537       $permCustomGroupIds = CRM_ACL_API::group(CRM_Core_Permission::VIEW, NULL, 'civicrm_custom_group', $allGroups, NULL);
 538       // do not allow custom data for reports if user doesn't have
 539       // permission to access custom data.
 540       if (!empty($this->_customGroupExtends) && empty($permCustomGroupIds)) {
 541         $this->_customGroupExtends = array();
 542       }
 543     }
 544
 545     // merge custom data columns to _columns list, if any
 546     $this->addCustomDataToColumns(TRUE, $permCustomGroupIds);
 547
 548     // add / modify display columns, filters ..etc
 549     CRM_Utils_Hook::alterReportVar('columns', $this->_columns, $this);
 550
 551     //assign currencyColumn variable to tpl
 552     $this->assign('currencyColumn', $this->_currencyColumn);
 553   }
 554
 555   /**
 556    * Shared pre-process function.
 557    *
 558    * If overriding preProcess function this should still be called.
 559    *
 560    * @throws \Exception
 561    */
 562   public function preProcessCommon() {
 563     $this->_force = CRM_Utils_Request::retrieve('force', 'Boolean');
 564
 565     $this->_dashBoardRowCount = CRM_Utils_Request::retrieve('rowCount', 'Integer');
 566
 567     $this->_section = CRM_Utils_Request::retrieve('section', 'Integer');
 568
 569     $this->assign('section', $this->_section);
 570     CRM_Core_Region::instance('page-header')->add(array(
 571       'markup' => sprintf('<!-- Report class: [%s] -->', htmlentities(get_class($this))),
 572     ));
 573     if (!$this->noController) {
 574       $this->setID($this->get('instanceId'));
 575
 576       if (!$this->_id) {
 577         $this->setID(CRM_Report_Utils_Report::getInstanceID());
 578         if (!$this->_id) {
 579           $this->setID(CRM_Report_Utils_Report::getInstanceIDForPath());
 580         }
 581       }
 582
 583       // set qfkey so that pager picks it up and use it in the "Next > Last >>" links.
 584       // FIXME: Note setting it in $_GET doesn't work, since pager generates link based on QUERY_STRING
 585       $_SERVER['QUERY_STRING'] .= "&qfKey={$this->controller->_key}";
 586     }
 587
 588     if ($this->_id) {
 589       $this->assign('instanceId', $this->_id);
 590       $params = array('id' => $this->_id);
 591       $this->_instanceValues = array();
 592       CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 593         $params,
 594         $this->_instanceValues
 595       );
 596       if (empty($this->_instanceValues)) {
 597         CRM_Core_Error::fatal("Report could not be loaded.");
 598       }
 599       $this->_title = $this->_instanceValues['title'];
 600       if (!empty($this->_instanceValues['permission']) &&
 601         (!(CRM_Core_Permission::check($this->_instanceValues['permission']) ||
 602           CRM_Core_Permission::check('administer Reports')
 603         ))
 604       ) {
 605         CRM_Utils_System::permissionDenied();
 606         CRM_Utils_System::civiExit();
 607       }
 608
 609       $formValues = CRM_Utils_Array::value('form_values', $this->_instanceValues);
 610       if ($formValues) {
 611         $this->_formValues = unserialize($formValues);
 612       }
 613       else {
 614         $this->_formValues = NULL;
 615       }
 616
 617       $this->setOutputMode();
 618
 619       if ($this->_outputMode == 'copy') {
 620         $this->_createNew = TRUE;
 621         $this->_params = $this->_formValues;
 622         $this->_params['view_mode'] = 'criteria';
 623         $this->_params['title'] = $this->getTitle() . ts(' (copy created by %1 on %2)', array(
 624           CRM_Core_Session::singleton()->getLoggedInContactDisplayName(),
 625           CRM_Utils_Date::customFormat(date('Y-m-d H:i')),
 626         ));
 627         // Do not pass go. Do not collect another chance to re-run the same query.
 628         CRM_Report_Form_Instance::postProcess($this);
 629       }
 630
 631       // lets always do a force if reset is found in the url.
 632       // Hey why not? see CRM-17225 for more about this. The use of reset to be force is historical for reasons stated
 633       // in the comment line above these 2.
 634       if (!empty($_REQUEST['reset'])
 635           && !in_array(CRM_Utils_Request::retrieve('output', 'String'), array('save', 'criteria'))) {
 636         $this->_force = 1;
 637       }
 638
 639       // set the mode
 640       $this->assign('mode', 'instance');
 641     }
 642     elseif (!$this->noController) {
 643       list($optionValueID, $optionValue) = CRM_Report_Utils_Report::getValueIDFromUrl();
 644       $instanceCount = CRM_Report_Utils_Report::getInstanceCount($optionValue);
 645       if (($instanceCount > 0) && $optionValueID) {
 646         $this->assign('instanceUrl',
 647           CRM_Utils_System::url('civicrm/report/list',
 648             "reset=1&ovid=$optionValueID"
 649           )
 650         );
 651       }
 652       if ($optionValueID) {
 653         $this->_description = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', $optionValueID, 'description');
 654       }
 655
 656       // set the mode
 657       $this->assign('mode', 'template');
 658     }
 659
 660     // lets display the Report Settings section
 661     $this->_instanceForm = $this->_force || $this->_id || (!empty($_POST));
 662
 663     // Do not display Report Settings section if administer Reports permission is absent OR
 664     // if report instance is reserved and administer reserved reports absent
 665     if (!CRM_Core_Permission::check('administer Reports') ||
 666       ($this->_instanceValues['is_reserved'] &&
 667         !CRM_Core_Permission::check('administer reserved reports'))
 668     ) {
 669       $this->_instanceForm = FALSE;
 670     }
 671
 672     $this->assign('criteriaForm', FALSE);
 673     // Display Report Criteria section if user has access Report Criteria OR administer Reports AND report instance is not reserved
 674     if (CRM_Core_Permission::check('administer Reports') ||
 675       CRM_Core_Permission::check('access Report Criteria')
 676     ) {
 677       if (!$this->_instanceValues['is_reserved'] ||
 678         CRM_Core_Permission::check('administer reserved reports')
 679       ) {
 680         $this->assign('criteriaForm', TRUE);
 681         $this->_criteriaForm = TRUE;
 682       }
 683     }
 684
 685     // Special permissions check for private instance if it's not the current contact instance
 686     if ($this->_id &&
 687       (CRM_Report_BAO_ReportInstance::reportIsPrivate($this->_id) &&
 688       !CRM_Report_BAO_ReportInstance::contactIsOwner($this->_id))) {
 689       if (!CRM_Core_Permission::check('access all private reports')) {
 690         $this->_instanceForm = FALSE;
 691         $this->assign('criteriaForm', FALSE);
 692       }
 693     }
 694
 695     $this->_instanceButtonName = $this->getButtonName('submit', 'save');
 696     $this->_createNewButtonName = $this->getButtonName('submit', 'next');
 697     $this->_groupButtonName = $this->getButtonName('submit', 'group');
 698     $this->_chartButtonName = $this->getButtonName('submit', 'chart');
 699   }
 700
 701   /**
 702    * Add bread crumb.
 703    */
 704   public function addBreadCrumb() {
 705     $breadCrumbs
 706       = array(
 707         array(
 708           'title' => ts('Report Templates'),
 709           'url' => CRM_Utils_System::url('civicrm/admin/report/template/list', 'reset=1'),
 710         ),
 711       );
 712
 713     CRM_Utils_System::appendBreadCrumb($breadCrumbs);
 714   }
 715
 716   /**
 717    * Pre process function.
 718    *
 719    * Called prior to build form.
 720    */
 721   public function preProcess() {
 722     $this->preProcessCommon();
 723
 724     if (!$this->_id) {
 725       $this->addBreadCrumb();
 726     }
 727
 728     foreach ($this->_columns as $tableName => $table) {
 729       $this->setTableAlias($table, $tableName);
 730
 731       $expFields = array();
 732       // higher preference to bao object
 733       $daoOrBaoName = CRM_Utils_Array::value('bao', $table, CRM_Utils_Array::value('dao', $table));
 734
 735       if ($daoOrBaoName) {
 736         if (method_exists($daoOrBaoName, 'exportableFields')) {
 737           $expFields = $daoOrBaoName::exportableFields();
 738         }
 739         else {
 740           $expFields = $daoOrBaoName::export();
 741         }
 742       }
 743
 744       $doNotCopy = array('required', 'default');
 745
 746       $fieldGroups = array('fields', 'filters', 'group_bys', 'order_bys');
 747       foreach ($fieldGroups as $fieldGrp) {
 748         if (!empty($table[$fieldGrp]) && is_array($table[$fieldGrp])) {
 749           foreach ($table[$fieldGrp] as $fieldName => $field) {
 750             // $name is the field name used to reference the BAO/DAO export fields array
 751             $name = isset($field['name']) ? $field['name'] : $fieldName;
 752
 753             // Sometimes the field name key in the BAO/DAO export fields array is
 754             // different from the actual database field name.
 755             // Unset $field['name'] so that actual database field name can be obtained
 756             // from the BAO/DAO export fields array.
 757             unset($field['name']);
 758
 759             if (array_key_exists($name, $expFields)) {
 760               foreach ($doNotCopy as $dnc) {
 761                 // unset the values we don't want to be copied.
 762                 unset($expFields[$name][$dnc]);
 763               }
 764               if (empty($field)) {
 765                 $this->_columns[$tableName][$fieldGrp][$fieldName] = $expFields[$name];
 766               }
 767               else {
 768                 foreach ($expFields[$name] as $property => $val) {
 769                   if (!array_key_exists($property, $field)) {
 770                     $this->_columns[$tableName][$fieldGrp][$fieldName][$property] = $val;
 771                   }
 772                 }
 773               }
 774             }
 775
 776             // fill other vars
 777             if (!empty($field['no_repeat'])) {
 778               $this->_noRepeats[] = "{$tableName}_{$fieldName}";
 779             }
 780             if (!empty($field['no_display'])) {
 781               $this->_noDisplay[] = "{$tableName}_{$fieldName}";
 782             }
 783
 784             // set alias = table-name, unless already set
 785             $alias = isset($field['alias']) ? $field['alias'] : (
 786               isset($this->_columns[$tableName]['alias']) ? $this->_columns[$tableName]['alias'] : $tableName
 787             );
 788             $this->_columns[$tableName][$fieldGrp][$fieldName]['alias'] = $alias;
 789
 790             // set name = fieldName, unless already set
 791             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['name'])) {
 792               $this->_columns[$tableName][$fieldGrp][$fieldName]['name'] = $name;
 793             }
 794
 795             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['table_name'])) {
 796               $this->_columns[$tableName][$fieldGrp][$fieldName]['table_name'] = $tableName;
 797             }
 798
 799             // set dbAlias = alias.name, unless already set
 800             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'])) {
 801               $this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias']
 802                 = $alias . '.' .
 803                 $this->_columns[$tableName][$fieldGrp][$fieldName]['name'];
 804             }
 805
 806             // a few auto fills for filters
 807             if ($fieldGrp == 'filters') {
 808               // fill operator types
 809               if (!array_key_exists('operatorType', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 810                 switch (CRM_Utils_Array::value('type', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 811                   case CRM_Utils_Type::T_MONEY:
 812                   case CRM_Utils_Type::T_FLOAT:
 813                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
 814                     break;
 815
 816                   case CRM_Utils_Type::T_INT:
 817                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
 818                     break;
 819
 820                   case CRM_Utils_Type::T_DATE:
 821                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
 822                     break;
 823
 824                   case CRM_Utils_Type::T_BOOLEAN:
 825                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
 826                     if (!array_key_exists('options', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 827                       $this->_columns[$tableName][$fieldGrp][$fieldName]['options']
 828                         = array(
 829                           '' => ts('Any'),
 830                           '0' => ts('No'),
 831                           '1' => ts('Yes'),
 832                         );
 833                     }
 834                     break;
 835
 836                   default:
 837                     if ($daoOrBaoName &&
 838                       array_key_exists('pseudoconstant', $this->_columns[$tableName][$fieldGrp][$fieldName])
 839                     ) {
 840                       // with multiple options operator-type is generally multi-select
 841                       $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
 842                       if (!array_key_exists('options', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 843                         // fill options
 844                         $this->_columns[$tableName][$fieldGrp][$fieldName]['options'] = CRM_Core_PseudoConstant::get($daoOrBaoName, $fieldName);
 845                       }
 846                     }
 847                     break;
 848                 }
 849               }
 850             }
 851             if (!isset($this->_columns[$tableName]['metadata'][$fieldName])) {
 852               $this->_columns[$tableName]['metadata'][$fieldName] = $this->_columns[$tableName][$fieldGrp][$fieldName];
 853             }
 854             else {
 855               $this->_columns[$tableName]['metadata'][$fieldName] = array_merge($this->_columns[$tableName][$fieldGrp][$fieldName], $this->_columns[$tableName]['metadata'][$fieldName]);
 856             }
 857           }
 858         }
 859       }
 860
 861       // copy filters to a separate handy variable
 862       if (array_key_exists('filters', $table)) {
 863         $this->_filters[$tableName] = $this->_columns[$tableName]['filters'];
 864       }
 865
 866       if (array_key_exists('group_bys', $table)) {
 867         $groupBys[$tableName] = $this->_columns[$tableName]['group_bys'];
 868       }
 869
 870       if (array_key_exists('fields', $table)) {
 871         $reportFields[$tableName] = $this->_columns[$tableName]['fields'];
 872       }
 873     }
 874
 875     if ($this->_force) {
 876       $this->setDefaultValues(FALSE);
 877     }
 878
 879     CRM_Report_Utils_Get::processFilter($this->_filters, $this->_defaults);
 880     CRM_Report_Utils_Get::processGroupBy($groupBys, $this->_defaults);
 881     CRM_Report_Utils_Get::processFields($reportFields, $this->_defaults);
 882     CRM_Report_Utils_Get::processChart($this->_defaults);
 883
 884     if ($this->_force) {
 885       $this->_formValues = $this->_defaults;
 886       $this->postProcess();
 887     }
 888   }
 889
 890   /**
 891    * Set default values.
 892    *
 893    * @param bool $freeze
 894    *
 895    * @return array
 896    */
 897   public function setDefaultValues($freeze = TRUE) {
 898     $freezeGroup = array();
 899
 900     // FIXME: generalizing form field naming conventions would reduce
 901     // Lots of lines below.
 902     foreach ($this->_columns as $tableName => $table) {
 903       if (array_key_exists('fields', $table)) {
 904         foreach ($table['fields'] as $fieldName => $field) {
 905           if (empty($field['no_display'])) {
 906             if (!empty($field['required'])) {
 907               // set default
 908               $this->_defaults['fields'][$fieldName] = 1;
 909
 910               if ($freeze) {
 911                 // find element object, so that we could use quickform's freeze method
 912                 // for required elements
 913                 $obj = $this->getElementFromGroup("fields", $fieldName);
 914                 if ($obj) {
 915                   $freezeGroup[] = $obj;
 916                 }
 917               }
 918             }
 919             elseif (isset($field['default'])) {
 920               $this->_defaults['fields'][$fieldName] = $field['default'];
 921             }
 922           }
 923         }
 924       }
 925
 926       if (array_key_exists('group_bys', $table)) {
 927         foreach ($table['group_bys'] as $fieldName => $field) {
 928           if (isset($field['default'])) {
 929             if (!empty($field['frequency'])) {
 930               $this->_defaults['group_bys_freq'][$fieldName] = 'MONTH';
 931             }
 932             $this->_defaults['group_bys'][$fieldName] = $field['default'];
 933           }
 934         }
 935       }
 936       if (array_key_exists('filters', $table)) {
 937         foreach ($table['filters'] as $fieldName => $field) {
 938           if (isset($field['default'])) {
 939             if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
 940             ) {
 941               if (is_array($field['default'])) {
 942                 $this->_defaults["{$fieldName}_from"] = CRM_Utils_Array::value('from', $field['default']);
 943                 $this->_defaults["{$fieldName}_to"] = CRM_Utils_Array::value('to', $field['default']);
 944                 $this->_defaults["{$fieldName}_relative"] = 0;
 945               }
 946               else {
 947                 $this->_defaults["{$fieldName}_relative"] = $field['default'];
 948               }
 949             }
 950             else {
 951               if ((CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_INT) && is_array($field['default'])) {
 952                 $this->_defaults["{$fieldName}_min"] = CRM_Utils_Array::value('min', $field['default']);
 953                 $this->_defaults["{$fieldName}_max"] = CRM_Utils_Array::value('max', $field['default']);
 954               }
 955               $this->_defaults["{$fieldName}_value"] = $field['default'];
 956             }
 957           }
 958           //assign default value as "in" for multiselect
 959           //operator, To freeze the select element
 960           if (CRM_Utils_Array::value('operatorType', $field) ==
 961             CRM_Report_Form::OP_MULTISELECT
 962           ) {
 963             $this->_defaults["{$fieldName}_op"] = 'in';
 964           }
 965           if (CRM_Utils_Array::value('operatorType', $field) ==
 966             CRM_Report_Form::OP_ENTITYREF
 967           ) {
 968             $this->_defaults["{$fieldName}_op"] = 'in';
 969           }
 970           elseif (CRM_Utils_Array::value('operatorType', $field) ==
 971             CRM_Report_Form::OP_MULTISELECT_SEPARATOR
 972           ) {
 973             $this->_defaults["{$fieldName}_op"] = 'mhas';
 974           }
 975           elseif ($op = CRM_Utils_Array::value('default_op', $field)) {
 976             $this->_defaults["{$fieldName}_op"] = $op;
 977           }
 978         }
 979       }
 980
 981       if (
 982         empty($this->_formValues['order_bys']) &&
 983         (array_key_exists('order_bys', $table) &&
 984         is_array($table['order_bys']))
 985       ) {
 986         if (!array_key_exists('order_bys', $this->_defaults)) {
 987           $this->_defaults['order_bys'] = array();
 988         }
 989         foreach ($table['order_bys'] as $fieldName => $field) {
 990           if (!empty($field['default']) || !empty($field['default_order']) ||
 991             CRM_Utils_Array::value('default_is_section', $field) ||
 992             !empty($field['default_weight'])
 993           ) {
 994             $order_by = array(
 995               'column' => $fieldName,
 996               'order' => CRM_Utils_Array::value('default_order', $field, 'ASC'),
 997               'section' => CRM_Utils_Array::value('default_is_section', $field, 0),
 998             );
 999
1000             if (!empty($field['default_weight'])) {
1001               $this->_defaults['order_bys'][(int) $field['default_weight']] = $order_by;
1002             }
1003             else {
1004               array_unshift($this->_defaults['order_bys'], $order_by);
1005             }
1006           }
1007         }
1008       }
1009
1010       foreach ($this->_options as $fieldName => $field) {
1011         if (isset($field['default'])) {
1012           $this->_defaults['options'][$fieldName] = $field['default'];
1013         }
1014       }
1015     }
1016
1017     if (!empty($this->_submitValues)) {
1018       $this->preProcessOrderBy($this->_submitValues);
1019     }
1020     else {
1021       $this->preProcessOrderBy($this->_defaults);
1022     }
1023
1024     // lets finish freezing task here itself
1025     if (!empty($freezeGroup)) {
1026       foreach ($freezeGroup as $elem) {
1027         $elem->freeze();
1028       }
1029     }
1030
1031     if ($this->_formValues) {
1032       $this->_defaults = array_merge($this->_defaults, $this->_formValues);
1033     }
1034
1035     if ($this->_instanceValues) {
1036       $this->_defaults = array_merge($this->_defaults, $this->_instanceValues);
1037     }
1038
1039     CRM_Report_Form_Instance::setDefaultValues($this, $this->_defaults);
1040
1041     return $this->_defaults;
1042   }
1043
1044   /**
1045    * Get element from group.
1046    *
1047    * @param string $group
1048    * @param string $grpFieldName
1049    *
1050    * @return bool
1051    */
1052   public function getElementFromGroup($group, $grpFieldName) {
1053     $eleObj = $this->getElement($group);
1054     foreach ($eleObj->_elements as $index => $obj) {
1055       if ($grpFieldName == $obj->_attributes['name']) {
1056         return $obj;
1057       }
1058     }
1059     return FALSE;
1060   }
1061
1062   /**
1063    * Setter for $_params.
1064    *
1065    * @param array $params
1066    */
1067   public function setParams($params) {
1068     $this->_params = $params;
1069   }
1070
1071   /**
1072    * Getter for $_params.
1073    *
1074    * @return void|array $params
1075    */
1076   public function getParams() {
1077     return $this->_params;
1078   }
1079
1080   /**
1081    * Setter for $_id.
1082    *
1083    * @param int $instanceID
1084    */
1085   public function setID($instanceID) {
1086     $this->_id = $instanceID;
1087   }
1088
1089   /**
1090    * Setter for $_force.
1091    *
1092    * @param bool $isForce
1093    */
1094   public function setForce($isForce) {
1095     $this->_force = $isForce;
1096   }
1097
1098   /**
1099    * Setter for $_limitValue.
1100    *
1101    * @param int $_limitValue
1102    */
1103   public function setLimitValue($_limitValue) {
1104     $this->_limitValue = $_limitValue;
1105   }
1106
1107   /**
1108    * Setter for $_offsetValue.
1109    *
1110    * @param int $_offsetValue
1111    */
1112   public function setOffsetValue($_offsetValue) {
1113     $this->_offsetValue = $_offsetValue;
1114   }
1115
1116   /**
1117    * Setter for $addPaging.
1118    *
1119    * @param bool $value
1120    */
1121   public function setAddPaging($value) {
1122     $this->addPaging = $value;
1123   }
1124
1125   /**
1126    * Getter for $_defaultValues.
1127    *
1128    * @return array
1129    */
1130   public function getDefaultValues() {
1131     return $this->_defaults;
1132   }
1133
1134   /**
1135    * Remove any temporary tables.
1136    */
1137   public function cleanUpTemporaryTables() {
1138     foreach ($this->temporaryTables as $temporaryTable) {
1139       CRM_Core_DAO::executeQuery('DROP ' . ($temporaryTable['temporary'] ? 'TEMPORARY' : '') . ' TABLE IF EXISTS ' . $temporaryTable['name']);
1140     }
1141   }
1142
1143   /**
1144    * Create a temporary table.
1145    *
1146    * This function creates a table AND adds the details to the developer tab & $this->>temporary tables.
1147    *
1148    * @param string $identifier
1149    * @param $sql
1150    *
1151    * @return string
1152    */
1153   public function createTemporaryTable($identifier, $sql) {
1154     $tempTable = CRM_Utils_SQL_TempTable::build()->setUtf8(TRUE)->createWithQuery($sql);
1155     $name = $tempTable->getName();
1156     // Developers may force tables to be durable to assist in debugging so lets check.
1157     $isNotTrueTemporary = $tempTable->isDurable();
1158     // The TempTable build routine adds the next line - we output it to help developers see what has happened.
1159     $sql = 'CREATE ' . ($isNotTrueTemporary ? '' : 'TEMPORARY ') . "TABLE $name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci " . $sql;
1160     $this->addToDeveloperTab($sql);
1161     $this->temporaryTables[$identifier] = ['temporary' => !$isNotTrueTemporary, 'name' => $name];
1162     return $name;
1163   }
1164
1165   /**
1166    * Add columns to report.
1167    */
1168   public function addColumns() {
1169     $options = array();
1170     $colGroups = NULL;
1171     foreach ($this->_columns as $tableName => $table) {
1172       if (array_key_exists('fields', $table)) {
1173         foreach ($table['fields'] as $fieldName => $field) {
1174           $groupTitle = '';
1175           if (empty($field['no_display'])) {
1176             foreach (array('table', 'field') as $var) {
1177               if (!empty(${$var}['grouping'])) {
1178                 if (!is_array(${$var}['grouping'])) {
1179                   $tableName = ${$var}['grouping'];
1180                 }
1181                 else {
1182                   $tableName = array_keys(${$var}['grouping']);
1183                   $tableName = $tableName[0];
1184                   $groupTitle = array_values(${$var}['grouping']);
1185                   $groupTitle = $groupTitle[0];
1186                 }
1187               }
1188             }
1189
1190             if (!$groupTitle && isset($table['group_title'])) {
1191               $groupTitle = $table['group_title'];
1192               // Having a group_title is secret code for being a custom group
1193               // which cryptically translates to needing an accordion.
1194               // here we make that explicit.
1195               $colGroups[$tableName]['use_accordian_for_field_selection'] = TRUE;
1196             }
1197
1198             $colGroups[$tableName]['fields'][$fieldName] = CRM_Utils_Array::value('title', $field);
1199             if ($groupTitle && empty($colGroups[$tableName]['group_title'])) {
1200               $colGroups[$tableName]['group_title'] = $groupTitle;
1201             }
1202             $options[$fieldName] = CRM_Utils_Array::value('title', $field);
1203           }
1204         }
1205       }
1206     }
1207
1208     $this->addCheckBox("fields", ts('Select Columns'), $options, NULL,
1209       NULL, NULL, NULL, $this->_fourColumnAttribute, TRUE
1210     );
1211     if (!empty($colGroups)) {
1212       $this->tabs['FieldSelection'] = array(
1213         'title' => ts('Columns'),
1214         'tpl' => 'FieldSelection',
1215         'div_label' => 'col-groups',
1216       );
1217
1218       // Note this assignment is only really required in buildForm. It is being 'over-called'
1219       // to reduce risk of being missed due to overridden functions.
1220       $this->assign('tabs', $this->tabs);
1221     }
1222
1223     $this->assign('colGroups', $colGroups);
1224   }
1225
1226   /**
1227    * Add filters to report.
1228    */
1229   public function addFilters() {
1230     $filters = $filterGroups = array();
1231     $count = 1;
1232
1233     foreach ($this->_filters as $table => $attributes) {
1234       if (isset($this->_columns[$table]['group_title'])) {
1235         // The presence of 'group_title' is secret code for 'is_a_custom_table'
1236         // which magically means to 'display in an accordian'
1237         // here we make this explicit.
1238         $filterGroups[$table] = array(
1239           'group_title' => $this->_columns[$table]['group_title'],
1240           'use_accordian_for_field_selection' => TRUE,
1241
1242         );
1243       }
1244       foreach ($attributes as $fieldName => $field) {
1245         // get ready with option value pair
1246         // @ 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
1247         // would be useful
1248         $operations = $this->getOperationPair(
1249           CRM_Utils_Array::value('operatorType', $field),
1250           $fieldName);
1251
1252         $filters[$table][$fieldName] = $field;
1253
1254         switch (CRM_Utils_Array::value('operatorType', $field)) {
1255           case CRM_Report_Form::OP_MONTH:
1256             if (!array_key_exists('options', $field) ||
1257               !is_array($field['options']) || empty($field['options'])
1258             ) {
1259               // If there's no option list for this filter, define one.
1260               $field['options'] = array(
1261                 1 => ts('January'),
1262                 2 => ts('February'),
1263                 3 => ts('March'),
1264                 4 => ts('April'),
1265                 5 => ts('May'),
1266                 6 => ts('June'),
1267                 7 => ts('July'),
1268                 8 => ts('August'),
1269                 9 => ts('September'),
1270                 10 => ts('October'),
1271                 11 => ts('November'),
1272                 12 => ts('December'),
1273               );
1274               // Add this option list to this column _columns. This is
1275               // required so that filter statistics show properly.
1276               $this->_columns[$table]['filters'][$fieldName]['options'] = $field['options'];
1277             }
1278           case CRM_Report_Form::OP_MULTISELECT:
1279           case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1280             // assume a multi-select field
1281             if (!empty($field['options']) ||
1282               $fieldName == 'state_province_id' || $fieldName == 'county_id'
1283             ) {
1284               $element = $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1285               if (count($operations) <= 1) {
1286                 $element->freeze();
1287               }
1288               if ($fieldName == 'state_province_id' ||
1289                 $fieldName == 'county_id'
1290               ) {
1291                 $this->addChainSelect($fieldName . '_value', array(
1292                   'multiple' => TRUE,
1293                   'label' => NULL,
1294                   'class' => 'huge',
1295                 ));
1296               }
1297               else {
1298                 $this->addElement('select', "{$fieldName}_value", NULL, $field['options'], array(
1299                   'style' => 'min-width:250px',
1300                   'class' => 'crm-select2 huge',
1301                   'multiple' => TRUE,
1302                   'placeholder' => ts('- select -'),
1303                 ));
1304               }
1305             }
1306             break;
1307
1308           case CRM_Report_Form::OP_SELECT:
1309             // assume a select field
1310             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1311             if (!empty($field['options'])) {
1312               $this->addElement('select', "{$fieldName}_value", NULL, $field['options']);
1313             }
1314             break;
1315
1316           case CRM_Report_Form::OP_ENTITYREF:
1317             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1318             $this->setEntityRefDefaults($field, $table);
1319             $this->addEntityRef("{$fieldName}_value", NULL, $field['attributes']);
1320             break;
1321
1322           case CRM_Report_Form::OP_DATE:
1323             // build datetime fields
1324             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', ts('From:'), FALSE, $operations);
1325             $count++;
1326             break;
1327
1328           case CRM_Report_Form::OP_DATETIME:
1329             // build datetime fields
1330             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', ts('From:'), FALSE, $operations, 'searchDate', TRUE);
1331             $count++;
1332             break;
1333
1334           case CRM_Report_Form::OP_INT:
1335           case CRM_Report_Form::OP_FLOAT:
1336             // and a min value input box
1337             $this->add('text', "{$fieldName}_min", ts('Min'));
1338             // and a max value input box
1339             $this->add('text', "{$fieldName}_max", ts('Max'));
1340           default:
1341             // default type is string
1342             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations,
1343               array('onchange' => "return showHideMaxMinVal( '$fieldName', this.value );")
1344             );
1345             // we need text box for value input
1346             $this->add('text', "{$fieldName}_value", NULL, array('class' => 'huge'));
1347             break;
1348         }
1349       }
1350     }
1351     if (!empty($filters)) {
1352       $this->tabs['Filters'] = array(
1353         'title' => ts('Filters'),
1354         'tpl' => 'Filters',
1355         'div_label' => 'set-filters',
1356       );
1357     }
1358     $this->assign('filters', $filters);
1359     $this->assign('filterGroups', $filterGroups);
1360   }
1361
1362   /**
1363    * Function to assign the tabs to the template in the correct order.
1364    *
1365    * We want the tabs to wind up in this order (if not overridden).
1366    *
1367    *   - Field Selection
1368    *   - Group Bys
1369    *   - Order Bys
1370    *   - Other Options
1371    *   - Filters
1372    */
1373   protected function assignTabs() {
1374     $order = array(
1375       'FieldSelection',
1376       'GroupBy',
1377       'OrderBy',
1378       'ReportOptions',
1379       'Filters',
1380     );
1381     $order = array_intersect_key(array_fill_keys($order, 1), $this->tabs);
1382     $order = array_merge($order, $this->tabs);
1383     $this->assign('tabs', $order);
1384   }
1385
1386   /**
1387    * The intent is to add a tab for developers to view the sql.
1388    *
1389    * Currently using dpm.
1390    *
1391    * @param string $sql
1392    */
1393   public function addToDeveloperTab($sql) {
1394     if (!CRM_Core_Permission::check('view report sql')) {
1395       return;
1396     }
1397     $ignored_output_modes = array('pdf', 'csv', 'print');
1398     if (in_array($this->_outputMode, $ignored_output_modes)) {
1399       return;
1400     }
1401     $this->tabs['Developer'] = array(
1402       'title' => ts('Developer'),
1403       'tpl' => 'Developer',
1404       'div_label' => 'set-developer',
1405     );
1406
1407     $this->assignTabs();
1408     $this->sqlArray[] = $sql;
1409     foreach ($this->sqlArray as $sql) {
1410       foreach (array('LEFT JOIN') as $term) {
1411         $sql = str_replace($term, '<br>  ' . $term, $sql);
1412       }
1413       foreach (array('FROM', 'WHERE', 'GROUP BY', 'ORDER BY', 'LIMIT', ';') as $term) {
1414         $sql = str_replace($term, '<br><br>' . $term, $sql);
1415       }
1416       $this->sqlFormattedArray[] = $sql;
1417       $this->assign('sql', implode(';<br><br><br><br>', $this->sqlFormattedArray));
1418     }
1419     $this->assign('sqlModes', $sqlModes = CRM_Utils_SQL::getSqlModes());
1420
1421   }
1422
1423   /**
1424    * Add options defined in $this->_options to the report.
1425    */
1426   public function addOptions() {
1427     if (!empty($this->_options)) {
1428       // FIXME: For now lets build all elements as checkboxes.
1429       // Once we clear with the format we can build elements based on type
1430
1431       foreach ($this->_options as $fieldName => $field) {
1432         $options = array();
1433
1434         if ($field['type'] == 'select') {
1435           $this->addElement('select', "{$fieldName}", $field['title'], $field['options']);
1436         }
1437         elseif ($field['type'] == 'checkbox') {
1438           $options[$field['title']] = $fieldName;
1439           $this->addCheckBox($fieldName, NULL,
1440             $options, NULL,
1441             NULL, NULL, NULL, $this->_fourColumnAttribute
1442           );
1443         }
1444       }
1445     }
1446     if (!empty($this->_options) &&
1447         (!$this->_id
1448           || ($this->_id && CRM_Report_BAO_ReportInstance::contactCanAdministerReport($this->_id)))
1449     ) {
1450       $this->tabs['ReportOptions'] = array(
1451         'title' => ts('Display Options'),
1452         'tpl' => 'ReportOptions',
1453         'div_label' => 'other-options',
1454       );
1455     }
1456     $this->assign('otherOptions', $this->_options);
1457   }
1458
1459   /**
1460    * Add chart options to the report.
1461    */
1462   public function addChartOptions() {
1463     if (!empty($this->_charts)) {
1464       $this->addElement('select', "charts", ts('Chart'), $this->_charts);
1465       $this->assign('charts', $this->_charts);
1466       $this->addElement('submit', $this->_chartButtonName, ts('View'));
1467     }
1468   }
1469
1470   /**
1471    * Add group by options to the report.
1472    */
1473   public function addGroupBys() {
1474     $options = $freqElements = array();
1475
1476     foreach ($this->_columns as $tableName => $table) {
1477       if (array_key_exists('group_bys', $table)) {
1478         foreach ($table['group_bys'] as $fieldName => $field) {
1479           if (!empty($field) && empty($field['no_display'])) {
1480             $options[$field['title']] = $fieldName;
1481             if (!empty($field['frequency'])) {
1482               $freqElements[$field['title']] = $fieldName;
1483             }
1484           }
1485         }
1486       }
1487     }
1488     $this->addCheckBox("group_bys", ts('Group by columns'), $options, NULL,
1489       NULL, NULL, NULL, $this->_fourColumnAttribute
1490     );
1491     $this->assign('groupByElements', $options);
1492     if (!empty($options)) {
1493       $this->tabs['GroupBy'] = array(
1494         'title' => ts('Grouping'),
1495         'tpl' => 'GroupBy',
1496         'div_label' => 'group-by-elements',
1497       );
1498     }
1499
1500     foreach ($freqElements as $name) {
1501       $this->addElement('select', "group_bys_freq[$name]",
1502         ts('Frequency'), $this->_groupByDateFreq
1503       );
1504     }
1505   }
1506
1507   /**
1508    * Add data for order by tab.
1509    */
1510   public function addOrderBys() {
1511     $options = array();
1512     foreach ($this->_columns as $tableName => $table) {
1513
1514       // Report developer may define any column to order by; include these as order-by options.
1515       if (array_key_exists('order_bys', $table)) {
1516         foreach ($table['order_bys'] as $fieldName => $field) {
1517           if (!empty($field)) {
1518             $options[$fieldName] = $field['title'];
1519           }
1520         }
1521       }
1522
1523       // Add searchable custom fields as order-by options, if so requested
1524       // (These are already indexed, so allowing to order on them is cheap.)
1525
1526       if ($this->_autoIncludeIndexedFieldsAsOrderBys &&
1527         array_key_exists('extends', $table) && !empty($table['extends'])
1528       ) {
1529         foreach ($table['fields'] as $fieldName => $field) {
1530           if (empty($field['no_display'])) {
1531             $options[$fieldName] = $field['title'];
1532           }
1533         }
1534       }
1535     }
1536
1537     asort($options);
1538
1539     $this->assign('orderByOptions', $options);
1540     if (!empty($options)) {
1541       $this->tabs['OrderBy'] = array(
1542         'title' => ts('Sorting'),
1543         'tpl' => 'OrderBy',
1544         'div_label' => 'order-by-elements',
1545       );
1546     }
1547
1548     if (!empty($options)) {
1549       $options = array(
1550         '-' => ' - none - ',
1551       ) + $options;
1552       for ($i = 1; $i <= 5; $i++) {
1553         $this->addElement('select', "order_bys[{$i}][column]", ts('Order by Column'), $options);
1554         $this->addElement('select', "order_bys[{$i}][order]", ts('Order by Order'), array(
1555           'ASC' => ts('Ascending'),
1556           'DESC' => ts('Descending'),
1557         ));
1558         $this->addElement('checkbox', "order_bys[{$i}][section]", ts('Order by Section'), FALSE, array('id' => "order_by_section_$i"));
1559         $this->addElement('checkbox', "order_bys[{$i}][pageBreak]", ts('Page Break'), FALSE, array('id' => "order_by_pagebreak_$i"));
1560       }
1561     }
1562   }
1563
1564   /**
1565    * This adds the tab referred to as Title and Format, rendered through Instance.tpl.
1566    *
1567    * @todo call this tab into the report template in the same way as OrderBy etc, ie
1568    * by adding a description of the tab to $this->tabs, causing the tab to be added in
1569    * Criteria.tpl.
1570    */
1571   public function buildInstanceAndButtons() {
1572     CRM_Report_Form_Instance::buildForm($this);
1573     $this->_actionButtonName = $this->getButtonName('submit');
1574     $this->addTaskMenu($this->getActions($this->_id));
1575
1576     $this->assign('instanceForm', $this->_instanceForm);
1577
1578     // CRM-16274 Determine if user has 'edit all contacts' or equivalent
1579     $permission = CRM_Core_Permission::getPermission();
1580     if ($this->_instanceForm && $permission == CRM_Core_Permission::EDIT &&
1581       $this->_add2groupSupported
1582     ) {
1583       $this->addElement('select', 'groups', ts('Group'),
1584         array('' => ts('Add Contacts to Group')) +
1585         CRM_Core_PseudoConstant::nestedGroup(),
1586         array('class' => 'crm-select2 crm-action-menu fa-plus huge')
1587       );
1588       $this->assign('group', TRUE);
1589     }
1590
1591     $this->addElement('submit', $this->_groupButtonName, '', array('style' => 'display: none;'));
1592
1593     $this->addChartOptions();
1594     $showResultsLabel = $this->getResultsLabel();
1595     $this->addButtons(array(
1596         array(
1597           'type' => 'submit',
1598           'name' => $showResultsLabel,
1599           'isDefault' => TRUE,
1600         ),
1601       )
1602     );
1603   }
1604
1605   /**
1606    * Has this form been submitted already?
1607    *
1608    * @return bool
1609    */
1610   public function resultsDisplayed() {
1611     $buttonName = $this->controller->getButtonName();
1612     return ($buttonName || $this->_outputMode);
1613   }
1614
1615   /**
1616    * Get the actions for this report instance.
1617    *
1618    * @param int $instanceId
1619    *
1620    * @return array
1621    */
1622   protected function getActions($instanceId) {
1623     $actions = CRM_Report_BAO_ReportInstance::getActionMetadata();
1624     if (empty($instanceId)) {
1625       $actions['report_instance.save'] = array(
1626         'title' => ts('Create Report'),
1627         'data' => array(
1628           'is_confirm' => TRUE,
1629           'confirm_title' => ts('Create Report'),
1630           'confirm_refresh_fields' => json_encode(array(
1631             'title' => array('selector' => '.crm-report-instanceForm-form-block-title', 'prepend' => ''),
1632             'description' => array('selector' => '.crm-report-instanceForm-form-block-description', 'prepend' => ''),
1633           )),
1634         ),
1635       );
1636       unset($actions['report_instance.delete']);
1637     }
1638
1639     if (!$this->_csvSupported) {
1640       unset($actions['report_instance.csv']);
1641     }
1642
1643     return $actions;
1644   }
1645
1646   /**
1647    * Main build form function.
1648    */
1649   public function buildQuickForm() {
1650     $this->addColumns();
1651
1652     $this->addFilters();
1653
1654     $this->addOptions();
1655
1656     $this->addGroupBys();
1657
1658     $this->addOrderBys();
1659
1660     $this->buildInstanceAndButtons();
1661
1662     // Add form rule for report.
1663     if (is_callable(array(
1664       $this,
1665       'formRule',
1666     ))) {
1667       $this->addFormRule(array(get_class($this), 'formRule'), $this);
1668     }
1669     $this->assignTabs();
1670   }
1671
1672   /**
1673    * A form rule function for custom data.
1674    *
1675    * The rule ensures that fields selected in group_by if any) should only be the ones
1676    * present in display/select fields criteria;
1677    * note: works if and only if any custom field selected in group_by.
1678    *
1679    * @param array $fields
1680    * @param array $ignoreFields
1681    *
1682    * @return array
1683    */
1684   public function customDataFormRule($fields, $ignoreFields = array()) {
1685     $errors = array();
1686     if (!empty($this->_customGroupExtends) && $this->_customGroupGroupBy &&
1687       !empty($fields['group_bys'])
1688     ) {
1689       foreach ($this->_columns as $tableName => $table) {
1690         if ((substr($tableName, 0, 13) == 'civicrm_value' ||
1691             substr($tableName, 0, 12) == 'custom_value') &&
1692           !empty($this->_columns[$tableName]['fields'])
1693         ) {
1694           foreach ($this->_columns[$tableName]['fields'] as $fieldName => $field) {
1695             if (array_key_exists($fieldName, $fields['group_bys']) &&
1696               !array_key_exists($fieldName, $fields['fields'])
1697             ) {
1698               $errors['fields'] = "Please make sure fields selected in 'Group by Columns' section are also selected in 'Display Columns' section.";
1699             }
1700             elseif (array_key_exists($fieldName, $fields['group_bys'])) {
1701               foreach ($fields['fields'] as $fld => $val) {
1702                 if (!array_key_exists($fld, $fields['group_bys']) &&
1703                   !in_array($fld, $ignoreFields)
1704                 ) {
1705                   $errors['fields'] = "Please ensure that fields selected in 'Display Columns' are also selected in 'Group by Columns' section.";
1706                 }
1707               }
1708             }
1709           }
1710         }
1711       }
1712     }
1713     return $errors;
1714   }
1715
1716   /**
1717    * Get operators to display on form.
1718    *
1719    * Note: $fieldName param allows inheriting class to build operationPairs specific to a field.
1720    *
1721    * @param string $type
1722    * @param string $fieldName
1723    *
1724    * @return array
1725    */
1726   public function getOperationPair($type = "string", $fieldName = NULL) {
1727     // FIXME: At some point we should move these key-val pairs
1728     // to option_group and option_value table.
1729     switch ($type) {
1730       case CRM_Report_Form::OP_INT:
1731       case CRM_Report_Form::OP_FLOAT:
1732
1733         $result = array(
1734           'lte' => ts('Is less than or equal to'),
1735           'gte' => ts('Is greater than or equal to'),
1736           'bw' => ts('Is between'),
1737           'eq' => ts('Is equal to'),
1738           'lt' => ts('Is less than'),
1739           'gt' => ts('Is greater than'),
1740           'neq' => ts('Is not equal to'),
1741           'nbw' => ts('Is not between'),
1742           'nll' => ts('Is empty (Null)'),
1743           'nnll' => ts('Is not empty (Null)'),
1744         );
1745         return $result;
1746
1747       case CRM_Report_Form::OP_SELECT:
1748         $result = array(
1749           'eq' => ts('Is equal to'),
1750         );
1751         return $result;
1752
1753       case CRM_Report_Form::OP_MONTH:
1754       case CRM_Report_Form::OP_MULTISELECT:
1755       case CRM_Report_Form::OP_ENTITYREF:
1756
1757         $result = array(
1758           'in' => ts('Is one of'),
1759           'notin' => ts('Is not one of'),
1760         );
1761         return $result;
1762
1763       case CRM_Report_Form::OP_DATE:
1764
1765         $result = array(
1766           'nll' => ts('Is empty (Null)'),
1767           'nnll' => ts('Is not empty (Null)'),
1768         );
1769         return $result;
1770
1771       case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1772         // use this operator for the values, concatenated with separator. For e.g if
1773         // multiple options for a column is stored as ^A{val1}^A{val2}^A
1774         $result = array(
1775           'mhas' => ts('Is one of'),
1776           'mnot' => ts('Is not one of'),
1777         );
1778         return $result;
1779
1780       default:
1781         // type is string
1782         $result = array(
1783           'has' => ts('Contains'),
1784           'sw' => ts('Starts with'),
1785           'ew' => ts('Ends with'),
1786           'nhas' => ts('Does not contain'),
1787           'eq' => ts('Is equal to'),
1788           'neq' => ts('Is not equal to'),
1789           'nll' => ts('Is empty (Null)'),
1790           'nnll' => ts('Is not empty (Null)'),
1791         );
1792         return $result;
1793     }
1794   }
1795
1796   /**
1797    * Build the tag filter field to display on the filters tab.
1798    */
1799   public function buildTagFilter() {
1800     $contactTags = CRM_Core_BAO_Tag::getTags($this->_tagFilterTable);
1801     if (!empty($contactTags)) {
1802       $this->_columns['civicrm_tag'] = array(
1803         'dao' => 'CRM_Core_DAO_Tag',
1804         'filters' => array(
1805           'tagid' => array(
1806             'name' => 'tag_id',
1807             'title' => ts('Tag'),
1808             'type' => CRM_Utils_Type::T_INT,
1809             'tag' => TRUE,
1810             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1811             'options' => $contactTags,
1812           ),
1813         ),
1814       );
1815     }
1816   }
1817
1818   /**
1819    * Adds group filters to _columns (called from _Construct).
1820    */
1821   public function buildGroupFilter() {
1822     $this->_columns['civicrm_group']['filters'] = array(
1823       'gid' => array(
1824         'name' => 'group_id',
1825         'title' => ts('Group'),
1826         'type' => CRM_Utils_Type::T_INT,
1827         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1828         'group' => TRUE,
1829         'options' => CRM_Core_PseudoConstant::nestedGroup(),
1830       ),
1831     );
1832     if (empty($this->_columns['civicrm_group']['dao'])) {
1833       $this->_columns['civicrm_group']['dao'] = 'CRM_Contact_DAO_GroupContact';
1834     }
1835     if (empty($this->_columns['civicrm_group']['alias'])) {
1836       $this->_columns['civicrm_group']['alias'] = 'cgroup';
1837     }
1838   }
1839
1840   /**
1841    * Get SQL operator from form text version.
1842    *
1843    * @param string $operator
1844    *
1845    * @return string
1846    */
1847   public function getSQLOperator($operator = "like") {
1848     switch ($operator) {
1849       case 'eq':
1850         return '=';
1851
1852       case 'lt':
1853         return '<';
1854
1855       case 'lte':
1856         return '<=';
1857
1858       case 'gt':
1859         return '>';
1860
1861       case 'gte':
1862         return '>=';
1863
1864       case 'ne':
1865       case 'neq':
1866         return '!=';
1867
1868       case 'nhas':
1869         return 'NOT LIKE';
1870
1871       case 'in':
1872         return 'IN';
1873
1874       case 'notin':
1875         return 'NOT IN';
1876
1877       case 'nll':
1878         return 'IS NULL';
1879
1880       case 'nnll':
1881         return 'IS NOT NULL';
1882
1883       default:
1884         // type is string
1885         return 'LIKE';
1886     }
1887   }
1888
1889   /**
1890    * Generate where clause.
1891    *
1892    * This can be overridden in reports for special treatment of a field
1893    *
1894    * @param array $field Field specifications
1895    * @param string $op Query operator (not an exact match to sql)
1896    * @param mixed $value
1897    * @param float $min
1898    * @param float $max
1899    *
1900    * @return null|string
1901    */
1902   public function whereClause(&$field, $op, $value, $min, $max) {
1903
1904     $type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
1905
1906     // CRM-18010: Ensure type of each report filters
1907     if (!$type) {
1908       trigger_error('Type is not defined for field ' . $field['name'], E_USER_WARNING);
1909     }
1910     $clause = NULL;
1911
1912     switch ($op) {
1913       case 'bw':
1914       case 'nbw':
1915         if (($min !== NULL && strlen($min) > 0) ||
1916           ($max !== NULL && strlen($max) > 0)
1917         ) {
1918           $clauses = array();
1919           if ($min) {
1920             $min = CRM_Utils_Type::escape($min, $type);
1921             if ($op == 'bw') {
1922               $clauses[] = "( {$field['dbAlias']} >= $min )";
1923             }
1924             else {
1925               $clauses[] = "( {$field['dbAlias']} < $min OR {$field['dbAlias']} IS NULL )";
1926             }
1927           }
1928           if ($max) {
1929             $max = CRM_Utils_Type::escape($max, $type);
1930             if ($op == 'bw') {
1931               $clauses[] = "( {$field['dbAlias']} <= $max )";
1932             }
1933             else {
1934               $clauses[] = "( {$field['dbAlias']} > $max )";
1935             }
1936           }
1937
1938           if (!empty($clauses)) {
1939             if ($op == 'bw') {
1940               $clause = implode(' AND ', $clauses);
1941             }
1942             else {
1943               $clause = '(' . implode('OR', $clauses) . ')';
1944             }
1945           }
1946         }
1947         break;
1948
1949       case 'has':
1950       case 'nhas':
1951         if ($value !== NULL && strlen($value) > 0) {
1952           $value = CRM_Utils_Type::escape($value, $type);
1953           if (strpos($value, '%') === FALSE) {
1954             $value = "'%{$value}%'";
1955           }
1956           else {
1957             $value = "'{$value}'";
1958           }
1959           $sqlOP = $this->getSQLOperator($op);
1960           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1961         }
1962         break;
1963
1964       case 'in':
1965       case 'notin':
1966         if ((is_string($value) || is_numeric($value)) && strlen($value)) {
1967           $value = explode(',', $value);
1968         }
1969         if ($value !== NULL && is_array($value) && count($value) > 0) {
1970           $sqlOP = $this->getSQLOperator($op);
1971           if (CRM_Utils_Array::value('type', $field) ==
1972             CRM_Utils_Type::T_STRING
1973           ) {
1974             //cycle through selections and escape values
1975             foreach ($value as $key => $selection) {
1976               $value[$key] = CRM_Utils_Type::escape($selection, $type);
1977             }
1978             $clause
1979               = "( {$field['dbAlias']} $sqlOP ( '" . implode("' , '", $value) .
1980               "') )";
1981           }
1982           else {
1983             // for numerical values
1984             $clause = "{$field['dbAlias']} $sqlOP (" . implode(', ', $value) .
1985               ")";
1986           }
1987           if ($op == 'notin') {
1988             $clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
1989           }
1990           else {
1991             $clause = "( " . $clause . " )";
1992           }
1993         }
1994         break;
1995
1996       case 'mhas':
1997       case 'mnot':
1998         // multiple has or multiple not
1999         if ($value !== NULL && count($value) > 0) {
2000           $value = CRM_Utils_Type::escapeAll($value, $type);
2001           $operator = $op == 'mnot' ? 'NOT' : '';
2002           $regexp = "([[:cntrl:]]|^)" . implode('([[:cntrl:]]|$)|([[:cntrl:]]|^)', (array) $value) . "([[:cntrl:]]|$)";
2003           $clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'";
2004         }
2005         break;
2006
2007       case 'sw':
2008       case 'ew':
2009         if ($value !== NULL && strlen($value) > 0) {
2010           $value = CRM_Utils_Type::escape($value, $type);
2011           if (strpos($value, '%') === FALSE) {
2012             if ($op == 'sw') {
2013               $value = "'{$value}%'";
2014             }
2015             else {
2016               $value = "'%{$value}'";
2017             }
2018           }
2019           else {
2020             $value = "'{$value}'";
2021           }
2022           $sqlOP = $this->getSQLOperator($op);
2023           $clause = "( {$field['dbAlias']} $sqlOP $value )";
2024         }
2025         break;
2026
2027       case 'nll':
2028       case 'nnll':
2029         $sqlOP = $this->getSQLOperator($op);
2030         $clause = "( {$field['dbAlias']} $sqlOP )";
2031         break;
2032
2033       case 'eq':
2034       case 'neq':
2035       case 'ne':
2036         //CRM-18457: some custom field passes value in array format against binary operator
2037         if (is_array($value) && count($value)) {
2038           $value = $value[0];
2039         }
2040
2041       default:
2042         if ($value !== NULL && $value !== '') {
2043           if (isset($field['clause'])) {
2044             // FIXME: we not doing escape here. Better solution is to use two
2045             // different types - data-type and filter-type
2046             $clause = $field['clause'];
2047           }
2048           elseif (!is_array($value)) {
2049             $value = CRM_Utils_Type::escape($value, $type);
2050             $sqlOP = $this->getSQLOperator($op);
2051             if ($field['type'] == CRM_Utils_Type::T_STRING) {
2052               $value = "'{$value}'";
2053             }
2054             $clause = "( {$field['dbAlias']} $sqlOP $value )";
2055           }
2056         }
2057         break;
2058     }
2059
2060     if (!empty($field['group']) && $clause) {
2061       $clause = $this->whereGroupClause($field, $value, $op);
2062     }
2063     elseif (!empty($field['tag']) && $clause) {
2064       // not using left join in query because if any contact
2065       // belongs to more than one tag, results duplicate
2066       // entries.
2067       $clause = $this->whereTagClause($field, $value, $op);
2068     }
2069     elseif (!empty($field['membership_org']) && $clause) {
2070       $clause = $this->whereMembershipOrgClause($value, $op);
2071     }
2072     elseif (!empty($field['membership_type']) && $clause) {
2073       $clause = $this->whereMembershipTypeClause($value, $op);
2074     }
2075     return $clause;
2076   }
2077
2078   /**
2079    * Get SQL where clause for a date field.
2080    *
2081    * @param string $fieldName
2082    * @param string $relative
2083    * @param string $from
2084    * @param string $to
2085    * @param string $type
2086    * @param string $fromTime
2087    * @param string $toTime
2088    *
2089    * @return null|string
2090    */
2091   public function dateClause(
2092     $fieldName,
2093     $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
2094   ) {
2095     $clauses = array();
2096     if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) {
2097       $sqlOP = $this->getSQLOperator($relative);
2098       return "( {$fieldName} {$sqlOP} )";
2099     }
2100
2101     list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime);
2102
2103     if ($from) {
2104       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
2105       $clauses[] = "( {$fieldName} >= $from )";
2106     }
2107
2108     if ($to) {
2109       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
2110       $clauses[] = "( {$fieldName} <= {$to} )";
2111     }
2112
2113     if (!empty($clauses)) {
2114       return implode(' AND ', $clauses);
2115     }
2116
2117     return NULL;
2118   }
2119
2120   /**
2121    * Get values for from and to for date ranges.
2122    *
2123    * @deprecated
2124    *
2125    * @param bool $relative
2126    * @param string $from
2127    * @param string $to
2128    * @param string $fromTime
2129    * @param string $toTime
2130    *
2131    * @return array
2132    */
2133   public function getFromTo($relative, $from, $to, $fromTime = NULL, $toTime = NULL) {
2134     if (empty($toTime)) {
2135       // odd legacy behaviour to treat NULL as 'end of the day'
2136       // recommend updating reports to call CRM_Utils_Date::getFromTo
2137       //directly (default on the function is the actual default there).
2138       $toTime = '235959';
2139     }
2140     return CRM_Utils_Date::getFromTo($relative, $from, $to, $fromTime, $toTime);
2141   }
2142
2143   /**
2144    * Alter display of rows.
2145    *
2146    * Iterate through the rows retrieved via SQL and make changes for display purposes,
2147    * such as rendering contacts as links.
2148    *
2149    * @param array $rows
2150    *   Rows generated by SQL, with an array for each row.
2151    */
2152   public function alterDisplay(&$rows) {
2153   }
2154
2155   /**
2156    * Alter the way in which custom data fields are displayed.
2157    *
2158    * @param array $rows
2159    */
2160   public function alterCustomDataDisplay(&$rows) {
2161     // custom code to alter rows having custom values
2162     if (empty($this->_customGroupExtends)) {
2163       return;
2164     }
2165
2166     $customFields = array();
2167     $customFieldIds = array();
2168     foreach ($this->_params['fields'] as $fieldAlias => $value) {
2169       if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
2170         $customFieldIds[$fieldAlias] = $fieldId;
2171       }
2172     }
2173     if (empty($customFieldIds)) {
2174       return;
2175     }
2176
2177     // skip for type date and ContactReference since date format is already handled
2178     $query = "
2179 SELECT cg.table_name, cf.id
2180 FROM  civicrm_custom_field cf
2181 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
2182 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
2183       cg.is_active = 1 AND
2184       cf.is_active = 1 AND
2185       cf.is_searchable = 1 AND
2186       cf.data_type   NOT IN ('ContactReference', 'Date') AND
2187       cf.id IN (" . implode(",", $customFieldIds) . ")";
2188
2189     $dao = CRM_Core_DAO::executeQuery($query);
2190     while ($dao->fetch()) {
2191       $customFields[$dao->table_name . '_custom_' . $dao->id] = $dao->id;
2192     }
2193     $dao->free();
2194
2195     $entryFound = FALSE;
2196     foreach ($rows as $rowNum => $row) {
2197       foreach ($row as $tableCol => $val) {
2198         if (array_key_exists($tableCol, $customFields)) {
2199           $rows[$rowNum][$tableCol] = CRM_Core_BAO_CustomField::displayValue($val, $customFields[$tableCol]);
2200           $entryFound = TRUE;
2201         }
2202       }
2203
2204       // skip looking further in rows, if first row itself doesn't
2205       // have the column we need
2206       if (!$entryFound) {
2207         break;
2208       }
2209     }
2210   }
2211
2212   /**
2213    * Remove duplicate rows.
2214    *
2215    * @param array $rows
2216    */
2217   public function removeDuplicates(&$rows) {
2218     if (empty($this->_noRepeats)) {
2219       return;
2220     }
2221     $checkList = array();
2222
2223     foreach ($rows as $key => $list) {
2224       foreach ($list as $colName => $colVal) {
2225         if (array_key_exists($colName, $checkList) &&
2226           $checkList[$colName] == $colVal
2227         ) {
2228           $rows[$key][$colName] = "";
2229         }
2230         if (in_array($colName, $this->_noRepeats)) {
2231           $checkList[$colName] = $colVal;
2232         }
2233       }
2234     }
2235   }
2236
2237   /**
2238    * Fix subtotal display.
2239    *
2240    * @param array $row
2241    * @param array $fields
2242    * @param bool $subtotal
2243    */
2244   public function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
2245     foreach ($row as $colName => $colVal) {
2246       if (in_array($colName, $fields)) {
2247       }
2248       elseif (isset($this->_columnHeaders[$colName])) {
2249         if ($subtotal) {
2250           $row[$colName] = 'Subtotal';
2251           $subtotal = FALSE;
2252         }
2253         else {
2254           unset($row[$colName]);
2255         }
2256       }
2257     }
2258   }
2259
2260   /**
2261    * Calculate grant total.
2262    *
2263    * @param array $rows
2264    *
2265    * @return bool
2266    */
2267   public function grandTotal(&$rows) {
2268     if (!$this->_rollup  || count($rows) == 1) {
2269       return FALSE;
2270     }
2271
2272     $this->moveSummaryColumnsToTheRightHandSide();
2273
2274     if ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT) {
2275       return FALSE;
2276     }
2277
2278     $this->rollupRow = array_pop($rows);
2279
2280     foreach ($this->_columnHeaders as $fld => $val) {
2281       if (!in_array($fld, $this->_statFields)) {
2282         if (!$this->_grandFlag) {
2283           $this->rollupRow[$fld] = ts('Grand Total');
2284           $this->_grandFlag = TRUE;
2285         }
2286         else {
2287           $this->rollupRow[$fld] = "";
2288         }
2289       }
2290     }
2291
2292     $this->assign('grandStat', $this->rollupRow);
2293     return TRUE;
2294   }
2295
2296   /**
2297    * Format display output.
2298    *
2299    * @param array $rows
2300    * @param bool $pager
2301    */
2302   public function formatDisplay(&$rows, $pager = TRUE) {
2303     // set pager based on if any limit was applied in the query.
2304     if ($pager) {
2305       $this->setPager();
2306     }
2307
2308     // allow building charts if any
2309     if (!empty($this->_params['charts']) && !empty($rows)) {
2310       $this->buildChart($rows);
2311       $this->assign('chartEnabled', TRUE);
2312       $this->_chartId = "{$this->_params['charts']}_" .
2313         ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' .
2314         session_id();
2315       $this->assign('chartId', $this->_chartId);
2316     }
2317
2318     // unset columns not to be displayed.
2319     foreach ($this->_columnHeaders as $key => $value) {
2320       if (!empty($value['no_display'])) {
2321         unset($this->_columnHeaders[$key]);
2322       }
2323     }
2324
2325     // unset columns not to be displayed.
2326     if (!empty($rows)) {
2327       foreach ($this->_noDisplay as $noDisplayField) {
2328         foreach ($rows as $rowNum => $row) {
2329           unset($this->_columnHeaders[$noDisplayField]);
2330         }
2331       }
2332     }
2333
2334     // build array of section totals
2335     $this->sectionTotals();
2336
2337     // process grand-total row
2338     $this->grandTotal($rows);
2339
2340     // Find alter display functions.
2341     $firstRow = reset($rows);
2342     if ($firstRow) {
2343       $selectedFields = array_keys($firstRow);
2344       $alterFunctions = $alterMap = $alterSpecs = array();
2345       foreach ($this->_columns as $tableName => $table) {
2346         if (array_key_exists('metadata', $table)) {
2347           foreach ($table['metadata'] as $field => $specs) {
2348             if (in_array($tableName . '_' . $field, $selectedFields)) {
2349               if (array_key_exists('alter_display', $specs)) {
2350                 $alterFunctions[$tableName . '_' . $field] = $specs['alter_display'];
2351                 $alterMap[$tableName . '_' . $field] = $field;
2352                 $alterSpecs[$tableName . '_' . $field] = NULL;
2353               }
2354               // Add any alters that can be intuited from the field specs.
2355               // So far only boolean but a lot more could be.
2356               if (empty($alterSpecs[$tableName . '_' . $field]) && isset($specs['type']) && $specs['type'] == CRM_Utils_Type::T_BOOLEAN) {
2357                 $alterFunctions[$tableName . '_' . $field] = 'alterBoolean';
2358                 $alterMap[$tableName . '_' . $field] = $field;
2359                 $alterSpecs[$tableName . '_' . $field] = NULL;
2360               }
2361             }
2362           }
2363         }
2364       }
2365
2366       // Run the alter display functions
2367       foreach ($rows as $index => & $row) {
2368         foreach ($row as $selectedField => $value) {
2369           if (array_key_exists($selectedField, $alterFunctions)) {
2370             $rows[$index][$selectedField] = $this->{$alterFunctions[$selectedField]}($value, $row, $selectedField, $alterMap[$selectedField], $alterSpecs[$selectedField]);
2371           }
2372         }
2373       }
2374     }
2375
2376     // use this method for formatting rows for display purpose.
2377     $this->alterDisplay($rows);
2378     CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
2379
2380     // use this method for formatting custom rows for display purpose.
2381     $this->alterCustomDataDisplay($rows);
2382   }
2383
2384   /**
2385    * @param $value
2386    * @param $row
2387    * @param $selectedfield
2388    * @param $criteriaFieldName
2389    *
2390    * @return array
2391    */
2392   protected function alterStateProvinceID($value, &$row, $selectedfield, $criteriaFieldName) {
2393     $url = CRM_Utils_System::url(CRM_Utils_System::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl);
2394     $row[$selectedfield . '_link'] = $url;
2395     $row[$selectedfield . '_hover'] = ts("%1 for this state.", array(
2396       1 => $value,
2397     ));
2398
2399     $states = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
2400     if (!is_array($states)) {
2401       return $states;
2402     }
2403   }
2404
2405   /**
2406    * @param $value
2407    * @param $row
2408    * @param $selectedField
2409    * @param $criteriaFieldName
2410    *
2411    * @return array
2412    */
2413   protected function alterCountryID($value, &$row, $selectedField, $criteriaFieldName) {
2414     $url = CRM_Utils_System::url(CRM_Utils_System::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl);
2415     $row[$selectedField . '_link'] = $url;
2416     $row[$selectedField . '_hover'] = ts("%1 for this country.", array(
2417       1 => $value,
2418     ));
2419     $countries = CRM_Core_PseudoConstant::country($value, FALSE);
2420     if (!is_array($countries)) {
2421       return $countries;
2422     }
2423   }
2424
2425   /**
2426    * @param $value
2427    * @param $row
2428    * @param $selectedfield
2429    * @param $criteriaFieldName
2430    *
2431    * @return array
2432    */
2433   protected function alterCountyID($value, &$row, $selectedfield, $criteriaFieldName) {
2434     $url = CRM_Utils_System::url(CRM_Utils_System::currentPath(), "reset=1&force=1&{$criteriaFieldName}_op=in&{$criteriaFieldName}_value={$value}", $this->_absoluteUrl);
2435     $row[$selectedfield . '_link'] = $url;
2436     $row[$selectedfield . '_hover'] = ts("%1 for this county.", array(
2437       1 => $value,
2438     ));
2439     $counties = CRM_Core_PseudoConstant::county($value, FALSE);
2440     if (!is_array($counties)) {
2441       return $counties;
2442     }
2443   }
2444
2445   /**
2446    * @param $value
2447    * @param $row
2448    * @param $selectedfield
2449    * @param $criteriaFieldName
2450    *
2451    * @return mixed
2452    */
2453   protected function alterLocationTypeID($value, &$row, $selectedfield, $criteriaFieldName) {
2454     return CRM_Core_PseudoConstant::getLabel('CRM_Core_DAO_Address', 'location_type_id', $value);
2455   }
2456
2457   /**
2458    * @param $value
2459    * @param $row
2460    * @param $fieldname
2461    *
2462    * @return mixed
2463    */
2464   protected function alterContactID($value, &$row, $fieldname) {
2465     $nameField = substr($fieldname, 0, -2) . 'name';
2466     static $first = TRUE;
2467     static $viewContactList = FALSE;
2468     if ($first) {
2469       $viewContactList = CRM_Core_Permission::check('access CiviCRM');
2470       $first = FALSE;
2471     }
2472     if (!$viewContactList) {
2473       return $value;
2474     }
2475     if (array_key_exists($nameField, $row)) {
2476       $row[$nameField . '_link'] = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' . $value, $this->_absoluteUrl);
2477     }
2478     else {
2479       $row[$fieldname . '_link'] = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' . $value, $this->_absoluteUrl);
2480     }
2481     return $value;
2482   }
2483
2484   /**
2485    * @param $value
2486    *
2487    * @return mixed
2488    */
2489   protected function alterBoolean($value) {
2490     $options = array(0 => '', 1 => ts('Yes'));
2491     if (isset($options[$value])) {
2492       return $options[$value];
2493     }
2494     return $value;
2495   }
2496
2497   /**
2498    * Build chart.
2499    *
2500    * @param array $rows
2501    */
2502   public function buildChart(&$rows) {
2503     // override this method for building charts.
2504   }
2505
2506   // select() method below has been added recently (v3.3), and many of the report templates might
2507   // still be having their own select() method. We should fix them as and when encountered and move
2508   // towards generalizing the select() method below.
2509
2510   /**
2511    * Generate the SELECT clause and set class variable $_select.
2512    */
2513   public function select() {
2514     $select = $this->_selectAliases = array();
2515     $this->storeGroupByArray();
2516
2517     foreach ($this->_columns as $tableName => $table) {
2518       if (array_key_exists('fields', $table)) {
2519         foreach ($table['fields'] as $fieldName => $field) {
2520           if ($tableName == 'civicrm_address') {
2521             // deprecated, use $this->isTableSelected.
2522             $this->_addressField = TRUE;
2523           }
2524           if ($tableName == 'civicrm_email') {
2525             $this->_emailField = TRUE;
2526           }
2527           if ($tableName == 'civicrm_phone') {
2528             $this->_phoneField = TRUE;
2529           }
2530
2531           if (!empty($field['required']) ||
2532             !empty($this->_params['fields'][$fieldName])
2533           ) {
2534
2535             // 1. In many cases we want select clause to be built in slightly different way
2536             // for a particular field of a particular type.
2537             // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2538             // as needed.
2539             $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
2540             if ($selectClause) {
2541               $select[] = $selectClause;
2542               continue;
2543             }
2544
2545             // include statistics columns only if set
2546             if (!empty($field['statistics']) && !empty($this->_groupByArray)) {
2547               $select = $this->addStatisticsToSelect($field, $tableName, $fieldName, $select);
2548             }
2549             else {
2550
2551               $selectClause = $this->getSelectClauseWithGroupConcatIfNotGroupedBy($tableName, $fieldName, $field);
2552               if ($selectClause) {
2553                 $select[] = $selectClause;
2554               }
2555               else {
2556                 $select = $this->addBasicFieldToSelect($tableName, $fieldName, $field, $select);
2557               }
2558             }
2559           }
2560         }
2561       }
2562
2563       // select for group bys
2564       if (array_key_exists('group_bys', $table)) {
2565         foreach ($table['group_bys'] as $fieldName => $field) {
2566
2567           if ($tableName == 'civicrm_address') {
2568             $this->_addressField = TRUE;
2569           }
2570           if ($tableName == 'civicrm_email') {
2571             $this->_emailField = TRUE;
2572           }
2573           if ($tableName == 'civicrm_phone') {
2574             $this->_phoneField = TRUE;
2575           }
2576           // 1. In many cases we want select clause to be built in slightly different way
2577           // for a particular field of a particular type.
2578           // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2579           // as needed.
2580           $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
2581           if ($selectClause) {
2582             $select[] = $selectClause;
2583             continue;
2584           }
2585
2586           if (!empty($this->_params['group_bys']) &&
2587             !empty($this->_params['group_bys'][$fieldName]) &&
2588             !empty($this->_params['group_bys_freq'])
2589           ) {
2590             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
2591               case 'YEARWEEK':
2592                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
2593                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2594                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2595                 $field['title'] = 'Week';
2596                 break;
2597
2598               case 'YEAR':
2599                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
2600                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2601                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2602                 $field['title'] = 'Year';
2603                 break;
2604
2605               case 'MONTH':
2606                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
2607                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2608                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2609                 $field['title'] = 'Month';
2610                 break;
2611
2612               case 'QUARTER':
2613                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
2614                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2615                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2616                 $field['title'] = 'Quarter';
2617                 break;
2618             }
2619             // for graphs and charts -
2620             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
2621               $this->_interval = $field['title'];
2622               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title']
2623                 = $field['title'] . ' Beginning';
2624               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
2625               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
2626
2627               // just to make sure these values are transferred to rows.
2628               // since we 'll need them for calculation purpose,
2629               // e.g making subtotals look nicer or graphs
2630               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
2631               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
2632             }
2633           }
2634         }
2635       }
2636     }
2637
2638     if (empty($select)) {
2639       // CRM-21412 Do not give fatal error on report when no fields selected
2640       $select = array(1);
2641     }
2642
2643     $this->_selectClauses = $select;
2644     $this->_select = "SELECT " . implode(', ', $select) . " ";
2645   }
2646
2647   /**
2648    * Build select clause for a single field.
2649    *
2650    * @param string $tableName
2651    * @param string $tableKey
2652    * @param string $fieldName
2653    * @param string $field
2654    *
2655    * @return bool
2656    */
2657   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
2658     if (!empty($field['pseudofield'])) {
2659       $alias = "{$tableName}_{$fieldName}";
2660       $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
2661       $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
2662       $this->_columnHeaders["{$tableName}_{$fieldName}"]['dbAlias'] = CRM_Utils_Array::value('dbAlias', $field);
2663       $this->_selectAliases[] = $alias;
2664       return ' 1 as  ' . $alias;
2665     }
2666     return FALSE;
2667   }
2668
2669   /**
2670    * Build where clause.
2671    */
2672   public function where() {
2673     $this->storeWhereHavingClauseArray();
2674
2675     if (empty($this->_whereClauses)) {
2676       $this->_where = "WHERE ( 1 ) ";
2677       $this->_having = "";
2678     }
2679     else {
2680       $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
2681     }
2682
2683     if ($this->_aclWhere) {
2684       $this->_where .= " AND {$this->_aclWhere} ";
2685     }
2686
2687     if (!empty($this->_havingClauses)) {
2688       // use this clause to construct group by clause.
2689       $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
2690     }
2691   }
2692
2693   /**
2694    * Store Where clauses into an array.
2695    *
2696    * Breaking out this step makes over-riding more flexible as the clauses can be used in constructing a
2697    * temp table that may not be part of the final where clause or added
2698    * in other functions
2699    */
2700   public function storeWhereHavingClauseArray() {
2701     foreach ($this->_columns as $tableName => $table) {
2702       if (array_key_exists('filters', $table)) {
2703         foreach ($table['filters'] as $fieldName => $field) {
2704           // respect pseudofield to filter spec so fields can be marked as
2705           // not to be handled here
2706           if (!empty($field['pseudofield'])) {
2707             continue;
2708           }
2709           $clause = $this->generateFilterClause($field, $fieldName);
2710
2711           if (!empty($clause)) {
2712             if (!empty($field['having'])) {
2713               $this->_havingClauses[] = $clause;
2714             }
2715             else {
2716               $this->_whereClauses[] = $clause;
2717             }
2718           }
2719         }
2720       }
2721     }
2722
2723   }
2724
2725   /**
2726    * Set output mode.
2727    */
2728   public function processReportMode() {
2729     $this->setOutputMode();
2730
2731     $this->_sendmail
2732       = CRM_Utils_Request::retrieve(
2733         'sendmail',
2734         'Boolean',
2735         CRM_Core_DAO::$_nullObject
2736       );
2737
2738     $this->_absoluteUrl = FALSE;
2739     $printOnly = FALSE;
2740     $this->assign('printOnly', FALSE);
2741
2742     if ($this->_outputMode == 'print' ||
2743       ($this->_sendmail && !$this->_outputMode)
2744     ) {
2745       $this->assign('printOnly', TRUE);
2746       $printOnly = TRUE;
2747       $this->addPaging = FALSE;
2748       $this->assign('outputMode', 'print');
2749       $this->_outputMode = 'print';
2750       if ($this->_sendmail) {
2751         $this->_absoluteUrl = TRUE;
2752       }
2753     }
2754     elseif ($this->_outputMode == 'pdf') {
2755       $printOnly = TRUE;
2756       $this->addPaging = FALSE;
2757       $this->_absoluteUrl = TRUE;
2758     }
2759     elseif ($this->_outputMode == 'csv') {
2760       $printOnly = TRUE;
2761       $this->_absoluteUrl = TRUE;
2762       $this->addPaging = FALSE;
2763     }
2764     elseif ($this->_outputMode == 'group') {
2765       $this->assign('outputMode', 'group');
2766     }
2767     elseif ($this->_outputMode == 'create_report' && $this->_criteriaForm) {
2768       $this->assign('outputMode', 'create_report');
2769     }
2770     elseif ($this->_outputMode == 'copy' && $this->_criteriaForm) {
2771       $this->_createNew = TRUE;
2772     }
2773
2774     $this->assign('outputMode', $this->_outputMode);
2775     $this->assign('printOnly', $printOnly);
2776     // Get today's date to include in printed reports
2777     if ($printOnly) {
2778       $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
2779       $this->assign('reportDate', $reportDate);
2780     }
2781   }
2782
2783   /**
2784    * Post Processing function for Form.
2785    *
2786    * postProcessCommon should be used to set other variables from input as the api accesses that function.
2787    * This function is not accessed when the api calls the report.
2788    */
2789   public function beginPostProcess() {
2790     $this->setParams($this->controller->exportValues($this->_name));
2791     if (empty($this->_params) &&
2792       $this->_force
2793     ) {
2794       $this->setParams($this->_formValues);
2795     }
2796
2797     // hack to fix params when submitted from dashboard, CRM-8532
2798     // fields array is missing because form building etc is skipped
2799     // in dashboard mode for report
2800     //@todo - this could be done in the dashboard no we have a setter
2801     if (empty($this->_params['fields']) && !$this->_noFields) {
2802       $this->setParams($this->_formValues);
2803     }
2804
2805     $this->processReportMode();
2806
2807     if ($this->_outputMode == 'save' || $this->_outputMode == 'copy') {
2808       $this->_createNew = ($this->_outputMode == 'copy');
2809       CRM_Report_Form_Instance::postProcess($this);
2810     }
2811     if ($this->_outputMode == 'delete') {
2812       CRM_Report_BAO_ReportInstance::doFormDelete($this->_id, 'civicrm/report/list?reset=1', 'civicrm/report/list?reset=1');
2813     }
2814
2815     $this->_formValues = $this->_params;
2816
2817     $this->beginPostProcessCommon();
2818   }
2819
2820   /**
2821    * BeginPostProcess function run in both report mode and non-report mode (api).
2822    */
2823   public function beginPostProcessCommon() {
2824   }
2825
2826   /**
2827    * Build the report query.
2828    *
2829    * @param bool $applyLimit
2830    *
2831    * @return string
2832    */
2833   public function buildQuery($applyLimit = TRUE) {
2834     $this->buildGroupTempTable();
2835     $this->select();
2836     $this->from();
2837     $this->customDataFrom();
2838     $this->buildPermissionClause();
2839     $this->where();
2840     $this->groupBy();
2841     $this->orderBy();
2842
2843     foreach ($this->unselectedOrderByColumns() as $alias => $field) {
2844       $clause = $this->getSelectClauseWithGroupConcatIfNotGroupedBy($field['table_name'], $field['name'], $field);
2845       if (!$clause) {
2846         $clause = "{$field['dbAlias']} as {$alias}";
2847       }
2848       $this->_select .= ", $clause ";
2849     }
2850
2851     if ($applyLimit && empty($this->_params['charts'])) {
2852       $this->limit();
2853     }
2854     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2855
2856     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2857     $this->addToDeveloperTab($sql);
2858     return $sql;
2859   }
2860
2861   /**
2862    * Build group by clause.
2863    */
2864   public function groupBy() {
2865     $this->storeGroupByArray();
2866
2867     if (!empty($this->_groupByArray)) {
2868       if ($this->optimisedForOnlyFullGroupBy) {
2869         // We should probably deprecate this code path. What happens here is that
2870         // the group by is amended to reflect the select columns. This often breaks the
2871         // results. Retrofitting group strict group by onto existing report classes
2872         // went badly.
2873         $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $this->_groupByArray);
2874       }
2875       else {
2876         $this->_groupBy = ' GROUP BY ' . implode($this->_groupByArray);
2877       }
2878     }
2879   }
2880
2881   /**
2882    * Build order by clause.
2883    */
2884   public function orderBy() {
2885     $this->_orderBy = "";
2886     $this->_sections = array();
2887     $this->storeOrderByArray();
2888     if (!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP') {
2889       $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2890     }
2891     $this->assign('sections', $this->_sections);
2892   }
2893
2894   /**
2895    * Extract order by fields and store as an array.
2896    *
2897    * In some cases other functions want to know which fields are selected for ordering by
2898    * Separating this into a separate function allows it to be called separately from constructing
2899    * the order by clause
2900    */
2901   public function storeOrderByArray() {
2902     $orderBys = array();
2903
2904     if (!empty($this->_params['order_bys']) &&
2905       is_array($this->_params['order_bys']) &&
2906       !empty($this->_params['order_bys'])
2907     ) {
2908
2909       // Process order_bys in user-specified order
2910       foreach ($this->_params['order_bys'] as $orderBy) {
2911         $orderByField = array();
2912         foreach ($this->_columns as $tableName => $table) {
2913           if (array_key_exists('order_bys', $table)) {
2914             // For DAO columns defined in $this->_columns
2915             $fields = $table['order_bys'];
2916           }
2917           elseif (array_key_exists('extends', $table)) {
2918             // For custom fields referenced in $this->_customGroupExtends
2919             $fields = CRM_Utils_Array::value('fields', $table, array());
2920           }
2921           else {
2922             continue;
2923           }
2924           if (!empty($fields) && is_array($fields)) {
2925             foreach ($fields as $fieldName => $field) {
2926               if ($fieldName == $orderBy['column']) {
2927                 $orderByField = array_merge($field, $orderBy);
2928                 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2929                 break 2;
2930               }
2931             }
2932           }
2933         }
2934
2935         if (!empty($orderByField)) {
2936           $this->_orderByFields[$orderByField['tplField']] = $orderByField;
2937           if ($this->groupConcatTested) {
2938             $orderBys[$orderByField['tplField']] = "{$orderByField['tplField']} {$orderBy['order']}";
2939           }
2940           else {
2941             // Not sure when this is preferable to using tplField (which has
2942             // definitely been tested to work in cases then this does not.
2943             // in caution not switching unless report has been tested for
2944             // group concat functionality.
2945             $orderBys[$orderByField['tplField']] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2946           }
2947
2948           // Record any section headers for assignment to the template
2949           if (!empty($orderBy['section'])) {
2950             $orderByField['pageBreak'] = CRM_Utils_Array::value('pageBreak', $orderBy);
2951             $this->_sections[$orderByField['tplField']] = $orderByField;
2952           }
2953         }
2954       }
2955     }
2956
2957     $this->_orderByArray = $orderBys;
2958
2959     $this->assign('sections', $this->_sections);
2960   }
2961
2962   /**
2963    * Determine unselected columns.
2964    *
2965    * @return array
2966    */
2967   public function unselectedOrderByColumns() {
2968     return array_diff_key($this->_orderByFields, $this->getSelectColumns());
2969   }
2970
2971   /**
2972    * Determine unselected columns.
2973    *
2974    * @return array
2975    */
2976   public function unselectedSectionColumns() {
2977     if (is_array($this->_sections)) {
2978       return array_diff_key($this->_sections, $this->getSelectColumns());
2979     }
2980     else {
2981       return array();
2982     }
2983   }
2984
2985   /**
2986    * Build output rows.
2987    *
2988    * @param string $sql
2989    * @param array $rows
2990    */
2991   public function buildRows($sql, &$rows) {
2992     if (!$this->optimisedForOnlyFullGroupBy) {
2993       CRM_Core_DAO::disableFullGroupByMode();
2994     }
2995     $dao = CRM_Core_DAO::executeQuery($sql);
2996     if (stristr($this->_select, 'SQL_CALC_FOUND_ROWS')) {
2997       $this->_rowsFound = CRM_Core_DAO::singleValueQuery('SELECT FOUND_ROWS()');
2998     }
2999     CRM_Core_DAO::reenableFullGroupByMode();
3000     if (!is_array($rows)) {
3001       $rows = array();
3002     }
3003
3004     // use this method to modify $this->_columnHeaders
3005     $this->modifyColumnHeaders();
3006
3007     $unselectedSectionColumns = $this->unselectedSectionColumns();
3008
3009     while ($dao->fetch()) {
3010       $row = array();
3011       foreach ($this->_columnHeaders as $key => $value) {
3012         if (property_exists($dao, $key)) {
3013           $row[$key] = $dao->$key;
3014         }
3015       }
3016
3017       // section headers not selected for display need to be added to row
3018       foreach ($unselectedSectionColumns as $key => $values) {
3019         if (property_exists($dao, $key)) {
3020           $row[$key] = $dao->$key;
3021         }
3022       }
3023
3024       $rows[] = $row;
3025     }
3026   }
3027
3028   /**
3029    * Calculate section totals.
3030    *
3031    * When "order by" fields are marked as sections, this assigns to the template
3032    * an array of total counts for each section. This data is used by the Smarty
3033    * plugin {sectionTotal}.
3034    */
3035   public function sectionTotals() {
3036
3037     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
3038     if (empty($this->_selectAliases)) {
3039       return;
3040     }
3041
3042     if (!empty($this->_sections)) {
3043       // build the query with no LIMIT clause
3044       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
3045       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
3046
3047       // pull section aliases out of $this->_sections
3048       $sectionAliases = array_keys($this->_sections);
3049
3050       $ifnulls = array();
3051       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
3052         $ifnulls[] = "ifnull($alias, '') as $alias";
3053       }
3054       $this->_select = "SELECT " . implode(", ", $ifnulls);
3055       $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases);
3056
3057       // Group (un-limited) report by all aliases and get counts. This might
3058       // be done more efficiently when the contents of $sql are known, ie. by
3059       // overriding this method in the report class.
3060
3061       $query = $this->_select .
3062         ", count(*) as ct from ($sql) as subquery group by " .
3063         implode(", ", $sectionAliases);
3064
3065       // initialize array of total counts
3066       $totals = array();
3067       $dao = CRM_Core_DAO::executeQuery($query);
3068       while ($dao->fetch()) {
3069
3070         // let $this->_alterDisplay translate any integer ids to human-readable values.
3071         $rows[0] = $dao->toArray();
3072         $this->alterDisplay($rows);
3073         $row = $rows[0];
3074
3075         // add totals for all permutations of section values
3076         $values = array();
3077         $i = 1;
3078         $aliasCount = count($sectionAliases);
3079         foreach ($sectionAliases as $alias) {
3080           $values[] = $row[$alias];
3081           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
3082           if ($i == $aliasCount) {
3083             // the last alias is the lowest-level section header; use count as-is
3084             $totals[$key] = $dao->ct;
3085           }
3086           else {
3087             // other aliases are higher level; roll count into their total
3088             $totals[$key] += $dao->ct;
3089           }
3090         }
3091       }
3092       $this->assign('sectionTotals', $totals);
3093     }
3094   }
3095
3096   /**
3097    * Modify column headers.
3098    */
3099   public function modifyColumnHeaders() {
3100     // use this method to modify $this->_columnHeaders
3101   }
3102
3103   /**
3104    * Move totals columns to the right edge of the table.
3105    *
3106    * It seems like a more logical layout to have any totals columns on the far right regardless of
3107    * the location of the rest of their table.
3108    */
3109   public function moveSummaryColumnsToTheRightHandSide() {
3110     $statHeaders = (array_intersect_key($this->_columnHeaders, array_flip($this->_statFields)));
3111     $this->_columnHeaders = array_merge(array_diff_key($this->_columnHeaders, $statHeaders), $this->_columnHeaders, $statHeaders);
3112   }
3113
3114   /**
3115    * Assign rows to the template.
3116    *
3117    * @param array $rows
3118    */
3119   public function doTemplateAssignment(&$rows) {
3120     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
3121     $this->assign_by_ref('rows', $rows);
3122     $this->assign('statistics', $this->statistics($rows));
3123   }
3124
3125   /**
3126    * Build report statistics.
3127    *
3128    * Override this method to build your own statistics.
3129    *
3130    * @param array $rows
3131    *
3132    * @return array
3133    */
3134   public function statistics(&$rows) {
3135     $statistics = array();
3136
3137     $count = count($rows);
3138     // Why do we increment the count for rollup seems to artificially inflate the count.
3139     // It seems perhaps intentional to include the summary row in the count of results - although
3140     // this just seems odd.
3141     if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
3142       $count++;
3143     }
3144
3145     $this->countStat($statistics, $count);
3146
3147     $this->groupByStat($statistics);
3148
3149     $this->filterStat($statistics);
3150
3151     return $statistics;
3152   }
3153
3154   /**
3155    * Add count statistics.
3156    *
3157    * @param array $statistics
3158    * @param int $count
3159    */
3160   public function countStat(&$statistics, $count) {
3161     $statistics['counts']['rowCount'] = array(
3162       'title' => ts('Row(s) Listed'),
3163       'value' => $count,
3164     );
3165
3166     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
3167       $statistics['counts']['rowsFound'] = array(
3168         'title' => ts('Total Row(s)'),
3169         'value' => $this->_rowsFound,
3170       );
3171     }
3172   }
3173
3174   /**
3175    * Add group by statistics.
3176    *
3177    * @param array $statistics
3178    */
3179   public function groupByStat(&$statistics) {
3180     if (!empty($this->_params['group_bys']) &&
3181       is_array($this->_params['group_bys']) &&
3182       !empty($this->_params['group_bys'])
3183     ) {
3184       foreach ($this->_columns as $tableName => $table) {
3185         if (array_key_exists('group_bys', $table)) {
3186           foreach ($table['group_bys'] as $fieldName => $field) {
3187             if (!empty($this->_params['group_bys'][$fieldName])) {
3188               $combinations[] = $field['title'];
3189             }
3190           }
3191         }
3192       }
3193       $statistics['groups'][] = array(
3194         'title' => ts('Grouping(s)'),
3195         'value' => implode(' & ', $combinations),
3196       );
3197     }
3198   }
3199
3200   /**
3201    * Filter statistics.
3202    *
3203    * @param array $statistics
3204    */
3205   public function filterStat(&$statistics) {
3206     foreach ($this->_columns as $tableName => $table) {
3207       if (array_key_exists('filters', $table)) {
3208         foreach ($table['filters'] as $fieldName => $field) {
3209           if ((CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE ||
3210               CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_TIME) &&
3211             CRM_Utils_Array::value('operatorType', $field) !=
3212             CRM_Report_Form::OP_MONTH
3213           ) {
3214             list($from, $to)
3215               = $this->getFromTo(
3216                 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3217                 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
3218                 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
3219                 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
3220                 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
3221               );
3222             $from_time_format = !empty($this->_params["{$fieldName}_from_time"]) ? 'h' : 'd';
3223             $from = CRM_Utils_Date::customFormat($from, NULL, array($from_time_format));
3224
3225             $to_time_format = !empty($this->_params["{$fieldName}_to_time"]) ? 'h' : 'd';
3226             $to = CRM_Utils_Date::customFormat($to, NULL, array($to_time_format));
3227
3228             if ($from || $to) {
3229               $statistics['filters'][] = array(
3230                 'title' => $field['title'],
3231                 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
3232               );
3233             }
3234             elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3235               array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE))
3236             )) {
3237               $pair = $this->getOperationPair(CRM_Report_Form::OP_DATE);
3238               $statistics['filters'][] = array(
3239                 'title' => $field['title'],
3240                 'value' => $pair[$rel],
3241               );
3242             }
3243           }
3244           else {
3245             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
3246             $value = NULL;
3247             if ($op) {
3248               $pair = $this->getOperationPair(
3249                 CRM_Utils_Array::value('operatorType', $field),
3250                 $fieldName
3251               );
3252               $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
3253               $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
3254               $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
3255               if (in_array($op, array('bw', 'nbw')) && ($min || $max)) {
3256                 $value = "{$pair[$op]} $min " . ts('and') . " $max";
3257               }
3258               elseif ($val && CRM_Utils_Array::value('operatorType', $field) & self::OP_ENTITYREF) {
3259                 $this->setEntityRefDefaults($field, $tableName);
3260                 $result = civicrm_api3($field['attributes']['entity'], 'getlist',
3261                   array('id' => $val) +
3262                   CRM_Utils_Array::value('api', $field['attributes'], array()));
3263                 $values = array();
3264                 foreach ($result['values'] as $v) {
3265                   $values[] = $v['label'];
3266                 }
3267                 $value = "{$pair[$op]} " . implode(', ', $values);
3268               }
3269               elseif ($op == 'nll' || $op == 'nnll') {
3270                 $value = $pair[$op];
3271               }
3272               elseif (is_array($val) && (!empty($val))) {
3273                 $options = CRM_Utils_Array::value('options', $field, array());
3274                 foreach ($val as $key => $valIds) {
3275                   if (isset($options[$valIds])) {
3276                     $val[$key] = $options[$valIds];
3277                   }
3278                 }
3279                 $pair[$op] = (count($val) == 1) ? (($op == 'notin' || $op ==
3280                   'mnot') ? ts('Is Not') : ts('Is')) : CRM_Utils_Array::value($op, $pair);
3281                 $val = implode(', ', $val);
3282                 $value = "{$pair[$op]} " . $val;
3283               }
3284               elseif (!is_array($val) && (!empty($val) || $val == '0') &&
3285                 isset($field['options']) &&
3286                 is_array($field['options']) && !empty($field['options'])
3287               ) {
3288                 $value = CRM_Utils_Array::value($op, $pair) . " " .
3289                   CRM_Utils_Array::value($val, $field['options'], $val);
3290               }
3291               elseif ($val) {
3292                 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
3293               }
3294             }
3295             if ($value && empty($field['no_display'])) {
3296               $statistics['filters'][] = array(
3297                 'title' => CRM_Utils_Array::value('title', $field),
3298                 'value' => $value,
3299               );
3300             }
3301           }
3302         }
3303       }
3304     }
3305   }
3306
3307   /**
3308    * End post processing.
3309    *
3310    * @param array|null $rows
3311    */
3312   public function endPostProcess(&$rows = NULL) {
3313     $this->assign('report_class', get_class($this));
3314     if ($this->_storeResultSet) {
3315       $this->_resultSet = $rows;
3316     }
3317
3318     if ($this->_outputMode == 'print' ||
3319       $this->_outputMode == 'pdf' ||
3320       $this->_sendmail
3321     ) {
3322
3323       $content = $this->compileContent();
3324       $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
3325         "reset=1", TRUE
3326       );
3327
3328       if ($this->_sendmail) {
3329         $config = CRM_Core_Config::singleton();
3330         $attachments = array();
3331
3332         if ($this->_outputMode == 'csv') {
3333           $content
3334             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3335             ": {$url}</p>" . '<p>' .
3336             ts('The report is attached as a CSV file.') . '</p>' .
3337             $this->_formValues['report_footer'];
3338
3339           $csvFullFilename = $config->templateCompileDir .
3340             CRM_Utils_File::makeFileName('CiviReport.csv');
3341           $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
3342           file_put_contents($csvFullFilename, $csvContent);
3343           $attachments[] = array(
3344             'fullPath' => $csvFullFilename,
3345             'mime_type' => 'text/csv',
3346             'cleanName' => 'CiviReport.csv',
3347           );
3348         }
3349         if ($this->_outputMode == 'pdf') {
3350           // generate PDF content
3351           $pdfFullFilename = $config->templateCompileDir .
3352             CRM_Utils_File::makeFileName('CiviReport.pdf');
3353           file_put_contents($pdfFullFilename,
3354             CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
3355               TRUE, array('orientation' => 'landscape')
3356             )
3357           );
3358           // generate Email Content
3359           $content
3360             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3361             ": {$url}</p>" . '<p>' .
3362             ts('The report is attached as a PDF file.') . '</p>' .
3363             $this->_formValues['report_footer'];
3364
3365           $attachments[] = array(
3366             'fullPath' => $pdfFullFilename,
3367             'mime_type' => 'application/pdf',
3368             'cleanName' => 'CiviReport.pdf',
3369           );
3370         }
3371
3372         if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
3373           $this->_outputMode, $attachments
3374         )
3375         ) {
3376           CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
3377         }
3378         else {
3379           CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
3380         }
3381         return;
3382       }
3383       elseif ($this->_outputMode == 'print') {
3384         echo $content;
3385       }
3386       else {
3387         if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
3388           $config = CRM_Core_Config::singleton();
3389           //get chart image name
3390           $chartImg = $this->_chartId . '.png';
3391           //get image url path
3392           $uploadUrl
3393             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) .
3394             'openFlashChart/';
3395           $uploadUrl .= $chartImg;
3396           //get image doc path to overwrite
3397           $uploadImg
3398             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
3399             'openFlashChart/' . $chartImg;
3400           //Load the image
3401           $chart = imagecreatefrompng($uploadUrl);
3402           //convert it into formatted png
3403           CRM_Utils_System::setHttpHeader('Content-type', 'image/png');
3404           //overwrite with same image
3405           imagepng($chart, $uploadImg);
3406           //delete the object
3407           imagedestroy($chart);
3408         }
3409         CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
3410       }
3411       CRM_Utils_System::civiExit();
3412     }
3413     elseif ($this->_outputMode == 'csv') {
3414       CRM_Report_Utils_Report::export2csv($this, $rows);
3415     }
3416     elseif ($this->_outputMode == 'group') {
3417       $group = $this->_params['groups'];
3418       $this->add2group($group);
3419     }
3420   }
3421
3422   /**
3423    * Set store result set indicator to TRUE.
3424    *
3425    * @todo explain what this does
3426    */
3427   public function storeResultSet() {
3428     $this->_storeResultSet = TRUE;
3429   }
3430
3431   /**
3432    * Get result set.
3433    *
3434    * @return bool
3435    */
3436   public function getResultSet() {
3437     return $this->_resultSet;
3438   }
3439
3440   /**
3441    * Get the sql used to generate the report.
3442    *
3443    * @return string
3444    */
3445   public function getReportSql() {
3446     return $this->sqlArray;
3447   }
3448
3449   /**
3450    * Use the form name to create the tpl file name.
3451    *
3452    * @return string
3453    */
3454   public function getTemplateFileName() {
3455     $defaultTpl = parent::getTemplateFileName();
3456     $template = CRM_Core_Smarty::singleton();
3457     if (!$template->template_exists($defaultTpl)) {
3458       $defaultTpl = 'CRM/Report/Form.tpl';
3459     }
3460     return $defaultTpl;
3461   }
3462
3463   /**
3464    * Compile the report content.
3465    *
3466    * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
3467    *
3468    * @return string
3469    */
3470   public function compileContent() {
3471     $templateFile = $this->getHookedTemplateFileName();
3472     return CRM_Utils_Array::value('report_header', $this->_formValues) .
3473     CRM_Core_Form::$_template->fetch($templateFile) .
3474     CRM_Utils_Array::value('report_footer', $this->_formValues);
3475   }
3476
3477
3478   /**
3479    * Post process function.
3480    */
3481   public function postProcess() {
3482     // get ready with post process params
3483     $this->beginPostProcess();
3484
3485     // build query
3486     $sql = $this->buildQuery();
3487
3488     // build array of result based on column headers. This method also allows
3489     // modifying column headers before using it to build result set i.e $rows.
3490     $rows = array();
3491     $this->buildRows($sql, $rows);
3492
3493     // format result set.
3494     $this->formatDisplay($rows);
3495
3496     // assign variables to templates
3497     $this->doTemplateAssignment($rows);
3498
3499     // do print / pdf / instance stuff if needed
3500     $this->endPostProcess($rows);
3501   }
3502
3503   /**
3504    * Set limit.
3505    *
3506    * @param int $rowCount
3507    *
3508    * @return array
3509    */
3510   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
3511     // lets do the pager if in html mode
3512     $this->_limit = NULL;
3513
3514     // CRM-14115, over-ride row count if rowCount is specified in URL
3515     if ($this->_dashBoardRowCount) {
3516       $rowCount = $this->_dashBoardRowCount;
3517     }
3518     if ($this->addPaging) {
3519       $this->_select = preg_replace('/SELECT(\s+SQL_CALC_FOUND_ROWS)?\s+/i', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
3520
3521       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer');
3522
3523       // @todo all http vars should be extracted in the preProcess
3524       // - not randomly in the class
3525       if (!$pageId && !empty($_POST)) {
3526         if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
3527           $pageId = max((int) $_POST['crmPID_B'], 1);
3528         }
3529         elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
3530           $pageId = max((int) $_POST['crmPID'], 1);
3531         }
3532         unset($_POST['crmPID_B'], $_POST['crmPID']);
3533       }
3534
3535       $pageId = $pageId ? $pageId : 1;
3536       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
3537       $offset = ($pageId - 1) * $rowCount;
3538
3539       $offset = CRM_Utils_Type::escape($offset, 'Int');
3540       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
3541
3542       $this->_limit = " LIMIT $offset, $rowCount";
3543       return array($offset, $rowCount);
3544     }
3545     if ($this->_limitValue) {
3546       if ($this->_offsetValue) {
3547         $this->_limit = " LIMIT {$this->_offsetValue}, {$this->_limitValue} ";
3548       }
3549       else {
3550         $this->_limit = " LIMIT " . $this->_limitValue;
3551       }
3552     }
3553   }
3554
3555   /**
3556    * Set pager.
3557    *
3558    * @param int $rowCount
3559    */
3560   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
3561     // CRM-14115, over-ride row count if rowCount is specified in URL
3562     if ($this->_dashBoardRowCount) {
3563       $rowCount = $this->_dashBoardRowCount;
3564     }
3565
3566     if ($this->_limit && ($this->_limit != '')) {
3567       if (!$this->_rowsFound) {
3568         $sql = "SELECT FOUND_ROWS();";
3569         $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
3570       }
3571       $params = array(
3572         'total' => $this->_rowsFound,
3573         'rowCount' => $rowCount,
3574         'status' => ts('Records') . ' %%StatusMessage%%',
3575         'buttonBottom' => 'PagerBottomButton',
3576         'buttonTop' => 'PagerTopButton',
3577       );
3578       if (!empty($this->controller)) {
3579         // This happens when being called from the api Really we want the api to be able to
3580         // pass paging parameters, but at this stage just preventing test crashes.
3581         $params['pageID'] = $this->get(CRM_Utils_Pager::PAGE_ID);
3582       }
3583
3584       $pager = new CRM_Utils_Pager($params);
3585       $this->assign_by_ref('pager', $pager);
3586       $this->ajaxResponse['totalRows'] = $this->_rowsFound;
3587     }
3588   }
3589
3590   /**
3591    * Build a group filter with contempt for large data sets.
3592    *
3593    * This function has been retained as it takes time to migrate the reports over
3594    * to the new method which will not crash on large datasets.
3595    *
3596    * @deprecated
3597    *
3598    * @param string $field
3599    * @param mixed $value
3600    * @param string $op
3601    *
3602    * @return string
3603    */
3604   public function legacySlowGroupFilterClause($field, $value, $op) {
3605     $smartGroupQuery = "";
3606
3607     $group = new CRM_Contact_DAO_Group();
3608     $group->is_active = 1;
3609     $group->find();
3610     $smartGroups = array();
3611     while ($group->fetch()) {
3612       if (in_array($group->id, (array) $this->_params['gid_value']) &&
3613         $group->saved_search_id
3614       ) {
3615         $smartGroups[] = $group->id;
3616       }
3617     }
3618
3619     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3620
3621     $smartGroupQuery = '';
3622     if (!empty($smartGroups)) {
3623       $smartGroups = implode(',', $smartGroups);
3624       $smartGroupQuery = " UNION DISTINCT
3625                   SELECT DISTINCT smartgroup_contact.contact_id
3626                   FROM civicrm_group_contact_cache smartgroup_contact
3627                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3628     }
3629
3630     $sqlOp = $this->getSQLOperator($op);
3631     if (!is_array($value)) {
3632       $value = array($value);
3633     }
3634     //include child groups if any
3635     $value = array_merge($value, CRM_Contact_BAO_Group::getChildGroupIds($value));
3636
3637     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3638
3639     $contactAlias = $this->_aliases['civicrm_contact'];
3640     if (!empty($this->relationType) && $this->relationType == 'b_a') {
3641       $contactAlias = $this->_aliases['civicrm_contact_b'];
3642     }
3643     return " {$contactAlias}.id {$sqlOp} (
3644                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
3645                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
3646                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
3647                           {$smartGroupQuery} ) ";
3648   }
3649
3650   /**
3651    * Build where clause for groups.
3652    *
3653    * @param string $field
3654    * @param mixed $value
3655    * @param string $op
3656    *
3657    * @return string
3658    */
3659   public function whereGroupClause($field, $value, $op) {
3660     if ($this->groupFilterNotOptimised) {
3661       return $this->legacySlowGroupFilterClause($field, $value, $op);
3662     }
3663     if ($op === 'notin') {
3664       return " group_temp_table.id IS NULL ";
3665     }
3666     // We will have used an inner join instead.
3667     return "1";
3668   }
3669
3670
3671   /**
3672    * Create a table of the contact ids included by the group filter.
3673    *
3674    * This function is called by both the api (tests) and the UI.
3675    */
3676   public function buildGroupTempTable() {
3677     if (!empty($this->groupTempTable) || empty($this->_params['gid_value']) || $this->groupFilterNotOptimised) {
3678       return;
3679     }
3680     $filteredGroups = (array) $this->_params['gid_value'];
3681
3682     $groups = civicrm_api3('Group', 'get', array(
3683       'is_active' => 1,
3684       'id' => array('IN' => $filteredGroups),
3685       'saved_search_id' => array('>' => 0),
3686       'return' => 'id',
3687     ));
3688     $smartGroups = array_keys($groups['values']);
3689
3690     $query = "
3691        SELECT DISTINCT group_contact.contact_id as id
3692        FROM civicrm_group_contact group_contact
3693        WHERE group_contact.group_id IN (" . implode(', ', $filteredGroups) . ")
3694        AND group_contact.status = 'Added' ";
3695
3696     if (!empty($smartGroups)) {
3697       CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3698       $smartGroups = implode(',', $smartGroups);
3699       $query .= "
3700         UNION DISTINCT
3701         SELECT smartgroup_contact.contact_id as id
3702         FROM civicrm_group_contact_cache smartgroup_contact
3703         WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3704     }
3705
3706     $this->groupTempTable = $this->createTemporaryTable('rptgrp', $query);
3707     CRM_Core_DAO::executeQuery("ALTER TABLE $this->groupTempTable ADD INDEX i_id(id)");
3708   }
3709
3710   /**
3711    * Execute query and add it to the developer tab.
3712    *
3713    * @param string $query
3714    * @param array $params
3715    *
3716    * @return \CRM_Core_DAO|object
3717    */
3718   protected function executeReportQuery($query, $params = array()) {
3719     $this->addToDeveloperTab($query);
3720     return CRM_Core_DAO::executeQuery($query, $params);
3721   }
3722
3723   /**
3724    * Build where clause for tags.
3725    *
3726    * @param string $field
3727    * @param mixed $value
3728    * @param string $op
3729    *
3730    * @return string
3731    */
3732   public function whereTagClause($field, $value, $op) {
3733     // not using left join in query because if any contact
3734     // belongs to more than one tag, results duplicate
3735     // entries.
3736     $sqlOp = $this->getSQLOperator($op);
3737     if (!is_array($value)) {
3738       $value = array($value);
3739     }
3740     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3741     $entity_table = $this->_tagFilterTable;
3742     return " {$this->_aliases[$entity_table]}.id {$sqlOp} (
3743                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
3744                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
3745                           WHERE entity_table = '$entity_table' AND {$clause} ) ";
3746   }
3747
3748   /**
3749    * Generate membership organization clause.
3750    *
3751    * @param mixed $value
3752    * @param string $op SQL Operator
3753    *
3754    * @return string
3755    */
3756   public function whereMembershipOrgClause($value, $op) {
3757     $sqlOp = $this->getSQLOperator($op);
3758     if (!is_array($value)) {
3759       $value = array($value);
3760     }
3761
3762     $tmp_membership_org_sql_list = implode(', ', $value);
3763     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3764                           SELECT DISTINCT mem.contact_id
3765                           FROM civicrm_membership mem
3766                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3767                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3768                           WHERE mt.member_of_contact_id IN (" .
3769     $tmp_membership_org_sql_list . ")
3770                           AND mt.is_active = '1'
3771                           AND mem_status.is_current_member = '1'
3772                           AND mem_status.is_active = '1' )  ";
3773   }
3774
3775   /**
3776    * Generate Membership Type SQL Clause.
3777    *
3778    * @param mixed $value
3779    * @param string $op
3780    *
3781    * @return string
3782    *   SQL query string
3783    */
3784   public function whereMembershipTypeClause($value, $op) {
3785     $sqlOp = $this->getSQLOperator($op);
3786     if (!is_array($value)) {
3787       $value = array($value);
3788     }
3789
3790     $tmp_membership_sql_list = implode(', ', $value);
3791     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3792                           SELECT DISTINCT mem.contact_id
3793                           FROM civicrm_membership mem
3794                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3795                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3796                           WHERE mem.membership_type_id IN (" .
3797     $tmp_membership_sql_list . ")
3798                           AND mt.is_active = '1'
3799                           AND mem_status.is_current_member = '1'
3800                           AND mem_status.is_active = '1' ) ";
3801   }
3802
3803   /**
3804    * Buld contact acl clause
3805    * @deprecated in favor of buildPermissionClause
3806    *
3807    * @param string $tableAlias
3808    */
3809   public function buildACLClause($tableAlias = 'contact_a') {
3810     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
3811   }
3812
3813   /**
3814    * Build the permision clause for all entities in this report
3815    */
3816   public function buildPermissionClause() {
3817     $ret = array();
3818     foreach ($this->selectedTables() as $tableName) {
3819       $baoName = str_replace('_DAO_', '_BAO_', CRM_Core_DAO_AllCoreTables::getClassForTable($tableName));
3820       if ($baoName && class_exists($baoName) && !empty($this->_columns[$tableName]['alias'])) {
3821         $tableAlias = $this->_columns[$tableName]['alias'];
3822         $clauses = array_filter($baoName::getSelectWhereClause($tableAlias));
3823         foreach ($clauses as $field => $clause) {
3824           // Skip contact_id field if redundant
3825           if ($field != 'contact_id' || !in_array('civicrm_contact', $this->selectedTables())) {
3826             $ret["$tableName.$field"] = $clause;
3827           }
3828         }
3829       }
3830     }
3831     // Override output from buildACLClause
3832     $this->_aclFrom = NULL;
3833     $this->_aclWhere = implode(' AND ', $ret);
3834   }
3835
3836   /**
3837    * Add custom data to the columns.
3838    *
3839    * @param bool $addFields
3840    * @param array $permCustomGroupIds
3841    */
3842   public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
3843     if (empty($this->_customGroupExtends)) {
3844       return;
3845     }
3846     if (!is_array($this->_customGroupExtends)) {
3847       $this->_customGroupExtends = array($this->_customGroupExtends);
3848     }
3849     $customGroupWhere = '';
3850     if (!empty($permCustomGroupIds)) {
3851       $customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) .
3852         ") AND";
3853     }
3854     $sql = "
3855 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
3856        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
3857 FROM   civicrm_custom_group cg
3858 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
3859 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
3860       {$customGroupWhere}
3861       cg.is_active = 1 AND
3862       cf.is_active = 1 AND
3863       cf.is_searchable = 1
3864 ORDER BY cg.weight, cf.weight";
3865     $customDAO = CRM_Core_DAO::executeQuery($sql);
3866
3867     $curTable = NULL;
3868     while ($customDAO->fetch()) {
3869       if ($customDAO->table_name != $curTable) {
3870         $curTable = $customDAO->table_name;
3871         $curFields = $curFilters = array();
3872
3873         // dummy dao object
3874         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
3875         $this->_columns[$curTable]['extends'] = $customDAO->extends;
3876         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
3877         $this->_columns[$curTable]['group_title'] = $customDAO->title;
3878
3879         foreach (array('fields', 'filters', 'group_bys') as $colKey) {
3880           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
3881             $this->_columns[$curTable][$colKey] = array();
3882           }
3883         }
3884       }
3885       $fieldName = 'custom_' . $customDAO->cf_id;
3886
3887       if ($addFields) {
3888         // this makes aliasing work in favor
3889         $curFields[$fieldName] = array(
3890           'name' => $customDAO->column_name,
3891           'title' => $customDAO->label,
3892           'dataType' => $customDAO->data_type,
3893           'htmlType' => $customDAO->html_type,
3894         );
3895       }
3896       if ($this->_customGroupFilters) {
3897         // this makes aliasing work in favor
3898         $curFilters[$fieldName] = array(
3899           'name' => $customDAO->column_name,
3900           'title' => $customDAO->label,
3901           'dataType' => $customDAO->data_type,
3902           'htmlType' => $customDAO->html_type,
3903         );
3904       }
3905
3906       switch ($customDAO->data_type) {
3907         case 'Date':
3908           // filters
3909           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
3910           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
3911           // CRM-6946, show time part for datetime date fields
3912           if ($customDAO->time_format) {
3913             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
3914           }
3915           break;
3916
3917         case 'Boolean':
3918           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
3919           $curFilters[$fieldName]['options'] = array('' => ts('- select -')) + CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
3920           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3921           break;
3922
3923         case 'Int':
3924           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
3925           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3926           break;
3927
3928         case 'Money':
3929           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3930           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3931           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3932           break;
3933
3934         case 'Float':
3935           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3936           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
3937           break;
3938
3939         case 'String':
3940         case 'StateProvince':
3941         case 'Country':
3942           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3943
3944           $options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
3945           if ((is_array($options) && count($options) != 0) || (!is_array($options) && $options !== FALSE)) {
3946             $curFilters[$fieldName]['operatorType'] = CRM_Core_BAO_CustomField::isSerialized($customDAO) ? CRM_Report_Form::OP_MULTISELECT_SEPARATOR : CRM_Report_Form::OP_MULTISELECT;
3947             $curFilters[$fieldName]['options'] = $options;
3948           }
3949           break;
3950
3951         case 'ContactReference':
3952           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3953           $curFilters[$fieldName]['name'] = 'display_name';
3954           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3955
3956           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3957           $curFields[$fieldName]['name'] = 'display_name';
3958           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3959           break;
3960
3961         default:
3962           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3963           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3964       }
3965
3966       // CRM-19401 fix
3967       if ($customDAO->html_type == 'Select' && !array_key_exists('options', $curFilters[$fieldName])) {
3968         $options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
3969         if ($options !== FALSE) {
3970           $curFilters[$fieldName]['operatorType'] = CRM_Core_BAO_CustomField::isSerialized($customDAO) ? CRM_Report_Form::OP_MULTISELECT_SEPARATOR : CRM_Report_Form::OP_MULTISELECT;
3971           $curFilters[$fieldName]['options'] = $options;
3972         }
3973       }
3974
3975       if (!array_key_exists('type', $curFields[$fieldName])) {
3976         $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
3977       }
3978
3979       if ($addFields) {
3980         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
3981       }
3982       if ($this->_customGroupFilters) {
3983         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
3984       }
3985       if ($this->_customGroupGroupBy) {
3986         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
3987       }
3988     }
3989   }
3990
3991   /**
3992    * Build custom data from clause.
3993    *
3994    * @param bool $joinsForFiltersOnly
3995    *   Only include joins to support filters. This would be used if creating a table of contacts to include first.
3996    */
3997   public function customDataFrom($joinsForFiltersOnly = FALSE) {
3998     if (empty($this->_customGroupExtends)) {
3999       return;
4000     }
4001     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
4002     //CRM-18276 GROUP_CONCAT could be used with singleValueQuery and then exploded,
4003     //but by default that truncates to 1024 characters, which causes errors with installs with lots of custom field sets
4004     $customTables = array();
4005     $customTablesDAO = CRM_Core_DAO::executeQuery("SELECT table_name FROM civicrm_custom_group");
4006     while ($customTablesDAO->fetch()) {
4007       $customTables[] = $customTablesDAO->table_name;
4008     }
4009
4010     foreach ($this->_columns as $table => $prop) {
4011       if (in_array($table, $customTables)) {
4012         $extendsTable = $mapper[$prop['extends']];
4013         // Check field is required for rendering the report.
4014         if ((!$this->isFieldSelected($prop)) || ($joinsForFiltersOnly && !$this->isFieldFiltered($prop))) {
4015           continue;
4016         }
4017         $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id");
4018
4019         $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
4020         $this->_from .= "
4021 {$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$baseJoin}";
4022         // handle for ContactReference
4023         if (array_key_exists('fields', $prop)) {
4024           foreach ($prop['fields'] as $fieldName => $field) {
4025             if (CRM_Utils_Array::value('dataType', $field) ==
4026               'ContactReference'
4027             ) {
4028               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
4029               $this->_from .= "
4030 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
4031             }
4032           }
4033         }
4034       }
4035     }
4036   }
4037
4038   /**
4039    * Check if the field is selected.
4040    *
4041    * @param string $prop
4042    *
4043    * @return bool
4044    */
4045   public function isFieldSelected($prop) {
4046     if (empty($prop)) {
4047       return FALSE;
4048     }
4049
4050     if (!empty($this->_params['fields'])) {
4051       foreach (array_keys($prop['fields']) as $fieldAlias) {
4052         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
4053         if ($customFieldId) {
4054           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
4055             return TRUE;
4056           }
4057
4058           //might be survey response field.
4059           if (!empty($this->_params['fields']['survey_response']) &&
4060             !empty($prop['fields'][$fieldAlias]['isSurveyResponseField'])
4061           ) {
4062             return TRUE;
4063           }
4064         }
4065       }
4066     }
4067
4068     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
4069       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
4070         if (array_key_exists($fieldAlias, $this->_params['group_bys']) &&
4071           CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
4072         ) {
4073           return TRUE;
4074         }
4075       }
4076     }
4077
4078     if (!empty($this->_params['order_bys'])) {
4079       foreach (array_keys($prop['fields']) as $fieldAlias) {
4080         foreach ($this->_params['order_bys'] as $orderBy) {
4081           if ($fieldAlias == $orderBy['column'] &&
4082             CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
4083           ) {
4084             return TRUE;
4085           }
4086         }
4087       }
4088     }
4089     return $this->isFieldFiltered($prop);
4090
4091   }
4092
4093   /**
4094    * Check if the field is used as a filter.
4095    *
4096    * @param string $prop
4097    *
4098    * @return bool
4099    */
4100   protected function isFieldFiltered($prop) {
4101     if (!empty($prop['filters']) && $this->_customGroupFilters) {
4102       foreach ($prop['filters'] as $fieldAlias => $val) {
4103         foreach (array(
4104                    'value',
4105                    'min',
4106                    'max',
4107                    'relative',
4108                    'from',
4109                    'to',
4110                  ) as $attach) {
4111           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
4112             (!empty($this->_params[$fieldAlias . '_' . $attach])
4113               || ($attach != 'relative' &&
4114                 $this->_params[$fieldAlias . '_' . $attach] == '0')
4115             )
4116           ) {
4117             return TRUE;
4118           }
4119         }
4120         if (!empty($this->_params[$fieldAlias . '_op']) &&
4121           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
4122         ) {
4123           return TRUE;
4124         }
4125       }
4126     }
4127
4128     return FALSE;
4129   }
4130
4131   /**
4132    * Check for empty order_by configurations and remove them.
4133    *
4134    * Also set template to hide them.
4135    *
4136    * @param array $formValues
4137    */
4138   public function preProcessOrderBy(&$formValues) {
4139     // Object to show/hide form elements
4140     $_showHide = new CRM_Core_ShowHideBlocks('', '');
4141
4142     $_showHide->addShow('optionField_1');
4143
4144     // Cycle through order_by options; skip any empty ones, and hide them as well
4145     $n = 1;
4146
4147     if (!empty($formValues['order_bys'])) {
4148       foreach ($formValues['order_bys'] as $order_by) {
4149         if ($order_by['column'] && $order_by['column'] != '-') {
4150           $_showHide->addShow('optionField_' . $n);
4151           $orderBys[$n] = $order_by;
4152           $n++;
4153         }
4154       }
4155     }
4156     for ($i = $n; $i <= 5; $i++) {
4157       if ($i > 1) {
4158         $_showHide->addHide('optionField_' . $i);
4159       }
4160     }
4161
4162     // overwrite order_by options with modified values
4163     if (!empty($orderBys)) {
4164       $formValues['order_bys'] = $orderBys;
4165     }
4166     else {
4167       $formValues['order_bys'] = array(1 => array('column' => '-'));
4168     }
4169
4170     // assign show/hide data to template
4171     $_showHide->addToTemplate();
4172   }
4173
4174   /**
4175    * Check if table name has columns in SELECT clause.
4176    *
4177    * @param string $tableName
4178    *   Name of table (index of $this->_columns array).
4179    *
4180    * @return bool
4181    */
4182   public function isTableSelected($tableName) {
4183     return in_array($tableName, $this->selectedTables());
4184   }
4185
4186   /**
4187    * Check if table name has columns in WHERE or HAVING clause.
4188    *
4189    * @param string $tableName
4190    *   Name of table (index of $this->_columns array).
4191    *
4192    * @return bool
4193    */
4194   public function isTableFiltered($tableName) {
4195     // Cause the array to be generated if not previously done.
4196     if (!$this->_selectedTables && !$this->filteredTables) {
4197       $this->selectedTables();
4198     }
4199     return in_array($tableName, $this->filteredTables);
4200   }
4201
4202   /**
4203    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause.
4204    *
4205    * If the array is unset it will be built.
4206    *
4207    * @return array
4208    *   selectedTables
4209    */
4210   public function selectedTables() {
4211     if (!$this->_selectedTables) {
4212       $orderByColumns = array();
4213       if (array_key_exists('order_bys', $this->_params) &&
4214         is_array($this->_params['order_bys'])
4215       ) {
4216         foreach ($this->_params['order_bys'] as $orderBy) {
4217           $orderByColumns[] = $orderBy['column'];
4218         }
4219       }
4220
4221       foreach ($this->_columns as $tableName => $table) {
4222         if (array_key_exists('fields', $table)) {
4223           foreach ($table['fields'] as $fieldName => $field) {
4224             if (!empty($field['required']) ||
4225               !empty($this->_params['fields'][$fieldName])
4226             ) {
4227               $this->_selectedTables[] = $tableName;
4228               break;
4229             }
4230           }
4231         }
4232         if (array_key_exists('order_bys', $table)) {
4233           foreach ($table['order_bys'] as $orderByName => $orderBy) {
4234             if (in_array($orderByName, $orderByColumns)) {
4235               $this->_selectedTables[] = $tableName;
4236               break;
4237             }
4238           }
4239         }
4240         if (array_key_exists('filters', $table)) {
4241           foreach ($table['filters'] as $filterName => $filter) {
4242             if (!empty($this->_params["{$filterName}_value"])
4243               || !empty($this->_params["{$filterName}_relative"])
4244               || CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
4245               'nll'
4246               || CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
4247               'nnll'
4248             ) {
4249               $this->_selectedTables[] = $tableName;
4250               $this->filteredTables[] = $tableName;
4251               break;
4252             }
4253           }
4254         }
4255       }
4256     }
4257     return $this->_selectedTables;
4258   }
4259
4260   /**
4261    * Add campaign fields.
4262    *
4263    * @param bool $groupBy
4264    *   Add GroupBy? Not appropriate for detail report.
4265    * @param bool $orderBy
4266    *   Add OrderBy? Not appropriate for detail report.
4267    * @param bool $filters
4268    *
4269    */
4270   public function addCampaignFields($entityTable = 'civicrm_contribution', $groupBy = FALSE, $orderBy = FALSE, $filters = TRUE) {
4271     // Check if CiviCampaign is a) enabled and b) has active campaigns
4272     $config = CRM_Core_Config::singleton();
4273     $campaignEnabled = in_array('CiviCampaign', $config->enableComponents);
4274     if ($campaignEnabled) {
4275       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, FALSE, FALSE, TRUE);
4276       // If we have a campaign, build out the relevant elements
4277       if (!empty($getCampaigns['campaigns'])) {
4278         $this->campaigns = $getCampaigns['campaigns'];
4279         asort($this->campaigns);
4280         $this->_columns[$entityTable]['fields']['campaign_id'] = array('title' => ts('Campaign'), 'default' => 'false');
4281         if ($filters) {
4282           $this->_columns[$entityTable]['filters']['campaign_id'] = array(
4283             'title' => ts('Campaign'),
4284             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4285             'options' => $this->campaigns,
4286             'type' => CRM_Utils_Type::T_INT,
4287           );
4288         }
4289
4290         if ($groupBy) {
4291           $this->_columns[$entityTable]['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
4292         }
4293
4294         if ($orderBy) {
4295           $this->_columns[$entityTable]['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
4296         }
4297       }
4298     }
4299   }
4300
4301   /**
4302    * Add address fields.
4303    *
4304    * @deprecated - use getAddressColumns which is a more accurate description
4305    * and also accepts an array of options rather than a long list
4306    *
4307    * adding address fields to construct function in reports
4308    *
4309    * @param bool $groupBy
4310    *   Add GroupBy? Not appropriate for detail report.
4311    * @param bool $orderBy
4312    *   Add GroupBy? Not appropriate for detail report.
4313    * @param bool $filters
4314    * @param array $defaults
4315    *
4316    * @return array
4317    *   address fields for construct clause
4318    */
4319   public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
4320     $defaultAddressFields = array(
4321       'street_address' => ts('Street Address'),
4322       'supplemental_address_1' => ts('Supplementary Address Field 1'),
4323       'supplemental_address_2' => ts('Supplementary Address Field 2'),
4324       'supplemental_address_3' => ts('Supplementary Address Field 3'),
4325       'street_number' => ts('Street Number'),
4326       'street_name' => ts('Street Name'),
4327       'street_unit' => ts('Street Unit'),
4328       'city' => ts('City'),
4329       'postal_code' => ts('Postal Code'),
4330       'postal_code_suffix' => ts('Postal Code Suffix'),
4331       'country_id' => ts('Country'),
4332       'state_province_id' => ts('State/Province'),
4333       'county_id' => ts('County'),
4334     );
4335     $addressFields = array(
4336       'civicrm_address' => array(
4337         'dao' => 'CRM_Core_DAO_Address',
4338         'fields' => array(
4339           'address_name' => array(
4340             'title' => ts('Address Name'),
4341             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
4342             'name' => 'name',
4343           ),
4344         ),
4345         'grouping' => 'location-fields',
4346       ),
4347     );
4348     foreach ($defaultAddressFields as $fieldName => $fieldLabel) {
4349       $addressFields['civicrm_address']['fields'][$fieldName] = array(
4350         'title' => $fieldLabel,
4351         'default' => CRM_Utils_Array::value($fieldName, $defaults, FALSE),
4352       );
4353     }
4354
4355     $street_address_filters = $general_address_filters = array();
4356     if ($filters) {
4357       // Address filter depends on whether street address parsing is enabled.
4358       // (CRM-18696)
4359       $addressOptions = CRM_Core_BAO_Setting::valueOptions(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME,
4360         'address_options'
4361       );
4362       if ($addressOptions['street_address_parsing']) {
4363         $street_address_filters = array(
4364           'street_number' => array(
4365             'title' => ts('Street Number'),
4366             'type' => CRM_Utils_Type::T_INT,
4367             'name' => 'street_number',
4368           ),
4369           'street_name' => array(
4370             'title' => ts('Street Name'),
4371             'name' => 'street_name',
4372             'type' => CRM_Utils_Type::T_STRING,
4373           ),
4374         );
4375       }
4376       else {
4377         $street_address_filters = array(
4378           'street_address' => array(
4379             'title' => ts('Street Address'),
4380             'type' => CRM_Utils_Type::T_STRING,
4381             'name' => 'street_address',
4382           ),
4383         );
4384       }
4385       $general_address_filters = array(
4386         'postal_code' => array(
4387           'title' => ts('Postal Code'),
4388           'type' => CRM_Utils_Type::T_STRING,
4389           'name' => 'postal_code',
4390         ),
4391         'city' => array(
4392           'title' => ts('City'),
4393           'type' => CRM_Utils_Type::T_STRING,
4394           'name' => 'city',
4395         ),
4396         'country_id' => array(
4397           'name' => 'country_id',
4398           'title' => ts('Country'),
4399           'type' => CRM_Utils_Type::T_INT,
4400           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4401           'options' => CRM_Core_PseudoConstant::country(),
4402         ),
4403         'state_province_id' => array(
4404           'name' => 'state_province_id',
4405           'title' => ts('State/Province'),
4406           'type' => CRM_Utils_Type::T_INT,
4407           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4408           'options' => array(),
4409         ),
4410         'county_id' => array(
4411           'name' => 'county_id',
4412           'title' => ts('County'),
4413           'type' => CRM_Utils_Type::T_INT,
4414           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4415           'options' => array(),
4416         ),
4417       );
4418     }
4419     $addressFields['civicrm_address']['filters'] = array_merge(
4420         $street_address_filters,
4421         $general_address_filters);
4422
4423     if ($orderBy) {
4424       $addressFields['civicrm_address']['order_bys'] = array(
4425         'street_name' => array('title' => ts('Street Name')),
4426         'street_number' => array('title' => ts('Odd / Even Street Number')),
4427         'street_address' => NULL,
4428         'city' => NULL,
4429         'postal_code' => NULL,
4430       );
4431     }
4432
4433     if ($groupBy) {
4434       $addressFields['civicrm_address']['group_bys'] = array(
4435         'street_address' => NULL,
4436         'city' => NULL,
4437         'postal_code' => NULL,
4438         'state_province_id' => array(
4439           'title' => ts('State/Province'),
4440         ),
4441         'country_id' => array(
4442           'title' => ts('Country'),
4443         ),
4444         'county_id' => array(
4445           'title' => ts('County'),
4446         ),
4447       );
4448     }
4449     return $addressFields;
4450   }
4451
4452   /**
4453    * Do AlterDisplay processing on Address Fields.
4454    *  If there are multiple address field values then
4455    *  on basis of provided separator the code values are translated into respective labels
4456    *
4457    * @param array $row
4458    * @param array $rows
4459    * @param int $rowNum
4460    * @param string $baseUrl
4461    * @param string $linkText
4462    * @param string $separator
4463    *
4464    * @return bool
4465    */
4466   public function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText, $separator = ',') {
4467     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4468     $entryFound = FALSE;
4469     $columnMap = array(
4470       'civicrm_address_country_id' => 'country',
4471       'civicrm_address_county_id' => 'county',
4472       'civicrm_address_state_province_id' => 'stateProvince',
4473     );
4474     foreach ($columnMap as $fieldName => $fnName) {
4475       if (array_key_exists($fieldName, $row)) {
4476         if ($values = $row[$fieldName]) {
4477           $values = (array) explode($separator, $values);
4478           $rows[$rowNum][$fieldName] = [];
4479           $addressField = $fnName == 'stateProvince' ? 'state' : $fnName;
4480           foreach ($values as $value) {
4481             $rows[$rowNum][$fieldName][] = CRM_Core_PseudoConstant::$fnName($value);
4482           }
4483           $rows[$rowNum][$fieldName] = implode($separator, $rows[$rowNum][$fieldName]);
4484           if ($baseUrl) {
4485             $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4486               sprintf("reset=1&force=1&%s&%s_op=in&%s_value=%s",
4487                 $criteriaQueryParams,
4488                 str_replace('civicrm_address_', '', $fieldName),
4489                 str_replace('civicrm_address_', '', $fieldName),
4490                 implode(',', $values)
4491               ), $this->_absoluteUrl, $this->_id
4492             );
4493             $rows[$rowNum]["{$fieldName}_link"] = $url;
4494             $rows[$rowNum]["{$fieldName}_hover"] = ts("%1 for this %2.", array(1 => $linkText, 2 => $addressField));
4495           }
4496           $entryFound = TRUE;
4497         }
4498       }
4499     }
4500
4501     return $entryFound;
4502   }
4503
4504   /**
4505    * Do AlterDisplay processing on Address Fields.
4506    *
4507    * @param array $row
4508    * @param array $rows
4509    * @param int $rowNum
4510    * @param string $baseUrl
4511    * @param string $linkText
4512    *
4513    * @return bool
4514    */
4515   public function alterDisplayContactFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4516     $entryFound = FALSE;
4517     // There is no reason not to add links for all fields but it seems a bit odd to be able to click on
4518     // 'Mrs'. Also, we don't have metadata about the title. So, add selectively to addLinks.
4519     $addLinks = array('gender_id' => 'Gender');
4520     foreach (array('prefix_id', 'suffix_id', 'gender_id', 'contact_sub_type', 'preferred_language') as $fieldName) {
4521       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4522         if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) {
4523           $rowValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
4524           $rowLabels = array();
4525           foreach ($rowValues as $rowValue) {
4526             if ($rowValue) {
4527               $rowLabels[] = CRM_Core_PseudoConstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $rowValue);
4528             }
4529           }
4530           $rows[$rowNum]['civicrm_contact_' . $fieldName] = implode(', ', $rowLabels);
4531           if ($baseUrl && ($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) {
4532             $this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title);
4533           }
4534         }
4535         $entryFound = TRUE;
4536       }
4537     }
4538     $yesNoFields = array(
4539       'do_not_email', 'is_deceased', 'do_not_phone', 'do_not_sms', 'do_not_mail', 'is_opt_out',
4540     );
4541     foreach ($yesNoFields as $fieldName) {
4542       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4543         // Since these are essentially 'negative fields' it feels like it
4544         // makes sense to only highlight the exceptions hence no 'No'.
4545         $rows[$rowNum]['civicrm_contact_' . $fieldName] = !empty($rows[$rowNum]['civicrm_contact_' . $fieldName]) ? ts('Yes') : '';
4546         $entryFound = TRUE;
4547       }
4548     }
4549
4550     // Handle employer id
4551     if (array_key_exists('civicrm_contact_employer_id', $row)) {
4552       $employerId = $row['civicrm_contact_employer_id'];
4553       if ($employerId) {
4554         $rows[$rowNum]['civicrm_contact_employer_id'] = CRM_Contact_BAO_Contact::displayName($employerId);
4555         $rows[$rowNum]['civicrm_contact_employer_id_link'] = CRM_Utils_System::url('civicrm/contact/view', 'reset=1&cid=' . $employerId, $this->_absoluteUrl);
4556         $rows[$rowNum]['civicrm_contact_employer_id_hover'] = ts('View Contact Summary for Employer.');
4557         $entryFound = TRUE;
4558       }
4559     }
4560
4561     return $entryFound;
4562   }
4563
4564   /**
4565    * Adjusts dates passed in to YEAR() for fiscal year.
4566    *
4567    * @param string $fieldName
4568    *
4569    * @return string
4570    */
4571   public function fiscalYearOffset($fieldName) {
4572     $config = CRM_Core_Config::singleton();
4573     $fy = $config->fiscalYearStart;
4574     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' ||
4575       ($fy['d'] == 1 && $fy['M'] == 1)
4576     ) {
4577       return "YEAR( $fieldName )";
4578     }
4579     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" .
4580     ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
4581   }
4582
4583   /**
4584    * Add Address into From Table if required.
4585    *
4586    * @deprecated use joinAddressFromContact
4587    * (left here in case extensions use it).
4588    */
4589   public function addAddressFromClause() {
4590     CRM_Core_Error::deprecatedFunctionWarning('CRM_Report_Form::joinAddressFromContact');
4591     // include address field if address column is to be included
4592     if ((isset($this->_addressField) &&
4593         $this->_addressField
4594       ) ||
4595       $this->isTableSelected('civicrm_address')
4596     ) {
4597       $this->_from .= "
4598                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4599                            ON ({$this->_aliases['civicrm_contact']}.id =
4600                                {$this->_aliases['civicrm_address']}.contact_id) AND
4601                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4602     }
4603   }
4604
4605   /**
4606    * Add Phone into From Table if required.
4607    *
4608    * @deprecated use joinPhoneFromContact
4609    *  (left here in case extensions use it).
4610    */
4611   public function addPhoneFromClause() {
4612     CRM_Core_Error::deprecatedFunctionWarning('CRM_Report_Form::joinPhoneFromContact');
4613     // include address field if address column is to be included
4614     if ($this->isTableSelected('civicrm_phone')) {
4615       $this->_from .= "
4616       LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
4617       ON ({$this->_aliases['civicrm_contact']}.id =
4618       {$this->_aliases['civicrm_phone']}.contact_id) AND
4619       {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
4620     }
4621   }
4622
4623   /**
4624    * Add Address into From Table if required.
4625    *
4626    * Prefix will be added to both tables as
4627    * it is assumed you are using it to get address of a secondary contact.
4628    *
4629    * @param string $prefix
4630    * @param array $extra Additional options.
4631    *      Not currently used in core but may be used in override extensions.
4632    */
4633   protected function joinAddressFromContact($prefix = '', $extra = array()) {
4634     $addressTables = ['civicrm_address', 'civicrm_country', 'civicrm_worldregion', 'civicrm_state_province'];
4635     $isJoinRequired = $this->_addressField;
4636     foreach ($addressTables as $addressTable) {
4637       if ($this->isTableSelected($prefix . $addressTable)) {
4638         $isJoinRequired = TRUE;
4639       }
4640     }
4641     if ($isJoinRequired) {
4642       $this->_from .= "
4643                  LEFT JOIN civicrm_address {$this->_aliases[$prefix . 'civicrm_address']}
4644                            ON ({$this->_aliases[$prefix . 'civicrm_contact']}.id =
4645                                {$this->_aliases[$prefix . 'civicrm_address']}.contact_id) AND
4646                                {$this->_aliases[$prefix . 'civicrm_address']}.is_primary = 1\n";
4647     }
4648   }
4649
4650   /**
4651    * Add Country into From Table if required.
4652    *
4653    * Prefix will be added to both tables as
4654    * it is assumed you are using it to get address of a secondary contact.
4655    *
4656    * @param string $prefix
4657    * @param array $extra Additional options.
4658    *      Not currently used in core but may be used in override extensions.
4659    */
4660   protected function joinCountryFromAddress($prefix = '', $extra = array()) {
4661     // include country field if country column is to be included
4662     if ($this->isTableSelected($prefix . 'civicrm_country') || $this->isTableSelected($prefix . 'civicrm_worldregion')) {
4663       if (empty($this->_aliases[$prefix . 'civicrm_country'])) {
4664         $this->_aliases[$prefix . 'civicrm_country'] = $prefix . '_report_country';
4665       }
4666       $this->_from .= "
4667             LEFT JOIN civicrm_country {$this->_aliases[$prefix . 'civicrm_country']}
4668                    ON {$this->_aliases[$prefix . 'civicrm_address']}.country_id = {$this->_aliases[$prefix . 'civicrm_country']}.id AND
4669                       {$this->_aliases[$prefix . 'civicrm_address']}.is_primary = 1 ";
4670     }
4671   }
4672
4673   /**
4674    * Add Phone into From Table if required.
4675    *
4676    * Prefix will be added to both tables as
4677    * it is assumed you are using it to get address of a secondary contact.
4678    *
4679    * @param string $prefix
4680    * @param array $extra Additional options.
4681    *      Not currently used in core but may be used in override extensions.
4682    */
4683   protected function joinPhoneFromContact($prefix = '', $extra = array()) {
4684     // include phone field if phone column is to be included
4685     if ($this->isTableSelected($prefix . 'civicrm_phone')) {
4686       $this->_from .= "
4687       LEFT JOIN civicrm_phone {$this->_aliases[$prefix . 'civicrm_phone']}
4688              ON {$this->_aliases[$prefix . 'civicrm_contact']}.id = {$this->_aliases[$prefix . 'civicrm_phone']}.contact_id AND
4689                 {$this->_aliases[$prefix . 'civicrm_phone']}.is_primary = 1\n";
4690     }
4691   }
4692
4693   /**
4694    * Add Email into From Table if required.
4695    *
4696    * Prefix will be added to both tables as
4697    * it is assumed you are using it to get address of a secondary contact.
4698    *
4699    * @param string $prefix
4700    * @param array $extra Additional options.
4701    *      Not currently used in core but may be used in override extensions.
4702    */
4703   protected function joinEmailFromContact($prefix = '', $extra = array()) {
4704     // include email field if email column is to be included
4705     if ($this->isTableSelected($prefix . 'civicrm_email')) {
4706       $this->_from .= "
4707             LEFT JOIN  civicrm_email {$this->_aliases[$prefix . 'civicrm_email']}
4708                    ON ({$this->_aliases[$prefix . 'civicrm_contact']}.id = {$this->_aliases[$prefix . 'civicrm_email']}.contact_id AND
4709                        {$this->_aliases[$prefix . 'civicrm_email']}.is_primary = 1) ";
4710     }
4711   }
4712
4713   /**
4714    * Add Financial Transaction into From Table if required.
4715    */
4716   public function addFinancialTrxnFromClause() {
4717     if ($this->isTableSelected('civicrm_financial_trxn')) {
4718       $this->_from .= "
4719          LEFT JOIN civicrm_entity_financial_trxn eftcc
4720            ON ({$this->_aliases['civicrm_contribution']}.id = eftcc.entity_id AND
4721              eftcc.entity_table = 'civicrm_contribution')
4722          LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
4723            ON {$this->_aliases['civicrm_financial_trxn']}.id = eftcc.financial_trxn_id \n";
4724     }
4725   }
4726
4727   /**
4728    * Get phone columns to add to array.
4729    *
4730    * @param array $options
4731    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4732    *   - prefix_label Label to give columns from this phone table instance
4733    *
4734    * @return array
4735    *   phone columns definition
4736    */
4737   public function getPhoneColumns($options = array()) {
4738     $defaultOptions = array(
4739       'prefix' => '',
4740       'prefix_label' => '',
4741     );
4742
4743     $options = array_merge($defaultOptions, $options);
4744
4745     $fields = array(
4746       $options['prefix'] . 'civicrm_phone' => array(
4747         'dao' => 'CRM_Core_DAO_Phone',
4748         'fields' => array(
4749           $options['prefix'] . 'phone' => array(
4750             'title' => $options['prefix_label'] . ts('Phone'),
4751             'name' => 'phone',
4752           ),
4753         ),
4754       ),
4755     );
4756     return $fields;
4757   }
4758
4759   /**
4760    * Get a standard set of contact fields.
4761    *
4762    * @return array
4763    */
4764   public function getBasicContactFields() {
4765     return array(
4766       'sort_name' => array(
4767         'title' => ts('Contact Name'),
4768         'required' => TRUE,
4769         'default' => TRUE,
4770       ),
4771       'id' => array(
4772         'no_display' => TRUE,
4773         'required' => TRUE,
4774       ),
4775       'prefix_id' => array(
4776         'title' => ts('Contact Prefix'),
4777       ),
4778       'first_name' => array(
4779         'title' => ts('First Name'),
4780       ),
4781       'nick_name' => array(
4782         'title' => ts('Nick Name'),
4783       ),
4784       'middle_name' => array(
4785         'title' => ts('Middle Name'),
4786       ),
4787       'last_name' => array(
4788         'title' => ts('Last Name'),
4789       ),
4790       'suffix_id' => array(
4791         'title' => ts('Contact Suffix'),
4792       ),
4793       'postal_greeting_display' => array('title' => ts('Postal Greeting')),
4794       'email_greeting_display' => array('title' => ts('Email Greeting')),
4795       'addressee_display' => array('title' => ts('Addressee')),
4796       'contact_type' => array(
4797         'title' => ts('Contact Type'),
4798       ),
4799       'contact_sub_type' => array(
4800         'title' => ts('Contact Subtype'),
4801       ),
4802       'gender_id' => array(
4803         'title' => ts('Gender'),
4804       ),
4805       'birth_date' => array(
4806         'title' => ts('Birth Date'),
4807       ),
4808       'age' => array(
4809         'title' => ts('Age'),
4810         'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
4811       ),
4812       'job_title' => array(
4813         'title' => ts('Contact Job title'),
4814       ),
4815       'organization_name' => array(
4816         'title' => ts('Organization Name'),
4817       ),
4818       'external_identifier' => array(
4819         'title' => ts('Contact identifier from external system'),
4820       ),
4821       'do_not_email' => array(),
4822       'do_not_phone' => array(),
4823       'do_not_mail' => array(),
4824       'do_not_sms' => array(),
4825       'is_opt_out' => array(),
4826       'is_deceased' => array(),
4827       'preferred_language' => array(),
4828       'employer_id' => array(
4829         'title' => ts('Current Employer'),
4830       ),
4831     );
4832   }
4833
4834   /**
4835    * Get a standard set of contact filters.
4836    *
4837    * @param array $defaults
4838    *
4839    * @return array
4840    */
4841   public function getBasicContactFilters($defaults = array()) {
4842     return array(
4843       'sort_name' => array(
4844         'title' => ts('Contact Name'),
4845       ),
4846       'source' => array(
4847         'title' => ts('Contact Source'),
4848         'type' => CRM_Utils_Type::T_STRING,
4849       ),
4850       'id' => array(
4851         'title' => ts('Contact ID'),
4852         'no_display' => TRUE,
4853       ),
4854       'gender_id' => array(
4855         'title' => ts('Gender'),
4856         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4857         'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'),
4858       ),
4859       'birth_date' => array(
4860         'title' => ts('Birth Date'),
4861         'operatorType' => CRM_Report_Form::OP_DATE,
4862       ),
4863       'contact_type' => array(
4864         'title' => ts('Contact Type'),
4865       ),
4866       'contact_sub_type' => array(
4867         'title' => ts('Contact Subtype'),
4868       ),
4869       'modified_date' => array(
4870         'title' => ts('Contact Modified'),
4871         'operatorType' => CRM_Report_Form::OP_DATE,
4872         'type' => CRM_Utils_Type::T_DATE,
4873       ),
4874       'is_deceased' => array(
4875         'title' => ts('Deceased'),
4876         'type' => CRM_Utils_Type::T_BOOLEAN,
4877         'default' => CRM_Utils_Array::value('deceased', $defaults, 0),
4878       ),
4879       'do_not_email' => array(
4880         'title' => ts('Do not email'),
4881         'type' => CRM_Utils_Type::T_BOOLEAN,
4882       ),
4883       'do_not_phone' => array(
4884         'title' => ts('Do not phone'),
4885         'type' => CRM_Utils_Type::T_BOOLEAN,
4886       ),
4887       'do_not_mail' => array(
4888         'title' => ts('Do not mail'),
4889         'type' => CRM_Utils_Type::T_BOOLEAN,
4890       ),
4891       'do_not_sms' => array(
4892         'title' => ts('Do not SMS'),
4893         'type' => CRM_Utils_Type::T_BOOLEAN,
4894       ),
4895       'is_opt_out' => array(
4896         'title' => ts('Do not bulk email'),
4897         'type' => CRM_Utils_Type::T_BOOLEAN,
4898       ),
4899       'preferred_language' => array(
4900         'title' => ts('Preferred Language'),
4901       ),
4902       'is_deleted' => array(
4903         'no_display' => TRUE,
4904         'default' => 0,
4905         'type' => CRM_Utils_Type::T_BOOLEAN,
4906       ),
4907     );
4908   }
4909
4910   /**
4911    * Add contact to group.
4912    *
4913    * @param int $groupID
4914    */
4915   public function add2group($groupID) {
4916     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
4917       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
4918       $select = preg_replace('/SELECT(\s+SQL_CALC_FOUND_ROWS)?\s+/i', $select, $this->_select);
4919       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
4920       $sql = str_replace('WITH ROLLUP', '', $sql);
4921       $dao = CRM_Core_DAO::executeQuery($sql);
4922
4923       $contact_ids = array();
4924       // Add resulting contacts to group
4925       while ($dao->fetch()) {
4926         if ($dao->addtogroup_contact_id) {
4927           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
4928         }
4929       }
4930
4931       if (!empty($contact_ids)) {
4932         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
4933         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
4934       }
4935       else {
4936         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
4937       }
4938     }
4939   }
4940
4941   /**
4942    * Show charts on print screen.
4943    */
4944   public static function uploadChartImage() {
4945     // upload strictly for '.png' images
4946     $name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
4947     if (preg_match('/\.png$/', $name)) {
4948
4949       // Get the RAW .png from the input.
4950       $httpRawPostData = file_get_contents("php://input");
4951
4952       // prepare the directory
4953       $config = CRM_Core_Config::singleton();
4954       $defaultPath
4955         = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
4956         '/openFlashChart/';
4957       if (!file_exists($defaultPath)) {
4958         mkdir($defaultPath, 0777, TRUE);
4959       }
4960
4961       // full path to the saved image including filename
4962       $destination = $defaultPath . $name;
4963
4964       //write and save
4965       $jfh = fopen($destination, 'w') or die("can't open file");
4966       fwrite($jfh, $httpRawPostData);
4967       fclose($jfh);
4968       CRM_Utils_System::civiExit();
4969     }
4970   }
4971
4972   /**
4973    * Apply common settings to entityRef fields.
4974    *
4975    * @param array $field
4976    * @param string $table
4977    */
4978   public function setEntityRefDefaults(&$field, $table) {
4979     $field['attributes'] = $field['attributes'] ? $field['attributes'] : array();
4980     $field['attributes'] += array(
4981       'entity' => CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table)),
4982       'multiple' => TRUE,
4983       'placeholder' => ts('- select -'),
4984     );
4985   }
4986
4987   /**
4988    * Add link fields to the row.
4989    *
4990    * Function adds the _link & _hover fields to the row.
4991    *
4992    * @param array $row
4993    * @param string $baseUrl
4994    * @param string $linkText
4995    * @param string $value
4996    * @param string $fieldName
4997    * @param string $tablePrefix
4998    * @param string $fieldLabel
4999    *
5000    * @return mixed
5001    */
5002   protected function addLinkToRow(&$row, $baseUrl, $linkText, $value, $fieldName, $tablePrefix, $fieldLabel) {
5003     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
5004     $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
5005       "reset=1&force=1&{$criteriaQueryParams}&" .
5006       $fieldName . "_op=in&{$fieldName}_value={$value}",
5007       $this->_absoluteUrl, $this->_id
5008     );
5009     $row["{$tablePrefix}_{$fieldName}_link"] = $url;
5010     $row["{$tablePrefix}_{$fieldName}_hover"] = ts("%1 for this %2.",
5011       array(1 => $linkText, 2 => $fieldLabel)
5012     );
5013   }
5014
5015   /**
5016    * Get label for show results buttons.
5017    *
5018    * @return string
5019    */
5020   public function getResultsLabel() {
5021     $showResultsLabel = $this->resultsDisplayed() ? ts('Refresh results') : ts('View results');
5022     return $showResultsLabel;
5023   }
5024
5025   /**
5026    * Determine the output mode from the url or input.
5027    *
5028    * Output could be
5029    *   - pdf : Render as pdf
5030    *   - csv : Render as csv
5031    *   - print : Render in print format
5032    *   - save : save the report and display the new report
5033    *   - copy : save the report as a new instance and display that.
5034    *   - group : go to the add to group screen.
5035    *
5036    *  Potentially chart variations could also be included but the complexity
5037    *   is that we might print a bar chart as a pdf.
5038    */
5039   protected function setOutputMode() {
5040     $this->_outputMode = str_replace('report_instance.', '', CRM_Utils_Request::retrieve(
5041       'output',
5042       'String',
5043       CRM_Core_DAO::$_nullObject,
5044       FALSE,
5045       CRM_Utils_Array::value('task', $this->_params)
5046     ));
5047     // if contacts are added to group
5048     if (!empty($this->_params['groups']) && empty($this->_outputMode)) {
5049       $this->_outputMode = 'group';
5050     }
5051     if (isset($this->_params['task'])) {
5052       unset($this->_params['task']);
5053     }
5054   }
5055
5056   /**
5057    * CRM-17793 - Alter DateTime section header to group by date from the datetime field.
5058    *
5059    * @param $tempTable
5060    * @param $columnName
5061    */
5062   public function alterSectionHeaderForDateTime($tempTable, $columnName) {
5063     // add new column with date value for the datetime field
5064     $tempQuery = "ALTER TABLE {$tempTable} ADD COLUMN {$columnName}_date VARCHAR(128)";
5065     CRM_Core_DAO::executeQuery($tempQuery);
5066     $updateQuery = "UPDATE {$tempTable} SET {$columnName}_date = date({$columnName})";
5067     CRM_Core_DAO::executeQuery($updateQuery);
5068     $this->_selectClauses[] = "{$columnName}_date";
5069     $this->_select .= ", {$columnName}_date";
5070     $this->_sections["{$columnName}_date"] = $this->_sections["{$columnName}"];
5071     unset($this->_sections["{$columnName}"]);
5072     $this->assign('sections', $this->_sections);
5073   }
5074
5075   /**
5076    * Get an array of the columns that have been selected for display.
5077    *
5078    * @return array
5079    */
5080   public function getSelectColumns() {
5081     $selectColumns = array();
5082     foreach ($this->_columns as $tableName => $table) {
5083       if (array_key_exists('fields', $table)) {
5084         foreach ($table['fields'] as $fieldName => $field) {
5085           if (!empty($field['required']) ||
5086             !empty($this->_params['fields'][$fieldName])
5087           ) {
5088
5089             $selectColumns["{$tableName}_{$fieldName}"] = 1;
5090           }
5091         }
5092       }
5093     }
5094     return $selectColumns;
5095   }
5096
5097   /**
5098    * Add location tables to the query if they are used for filtering.
5099    *
5100    * This is for when we are running the query separately for filtering and retrieving display fields.
5101    */
5102   public function selectivelyAddLocationTablesJoinsToFilterQuery() {
5103     if ($this->isTableFiltered('civicrm_email')) {
5104       $this->_from .= "
5105           LEFT  JOIN civicrm_email  {$this->_aliases['civicrm_email']}
5106             ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
5107             AND {$this->_aliases['civicrm_email']}.is_primary = 1";
5108     }
5109     if ($this->isTableFiltered('civicrm_phone')) {
5110       $this->_from .= "
5111           LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
5112             ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id
5113             AND {$this->_aliases['civicrm_phone']}.is_primary = 1";
5114     }
5115     if ($this->isTableFiltered('civicrm_address')) {
5116       $this->_from .= "
5117           LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
5118           ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id)
5119           AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
5120     }
5121   }
5122
5123   /**
5124    * Set the base table for the FROM clause.
5125    *
5126    * Sets up the from clause, allowing for the possibility it might be a
5127    * temp table pre-filtered by groups if a group filter is in use.
5128    *
5129    * @param string $baseTable
5130    * @param string $field
5131    * @param null $tableAlias
5132    */
5133   public function setFromBase($baseTable, $field = 'id', $tableAlias = NULL) {
5134     if (!$tableAlias) {
5135       $tableAlias = $this->_aliases[$baseTable];
5136     }
5137     $this->_from = $this->_from = " FROM $baseTable $tableAlias ";
5138     $this->joinGroupTempTable($baseTable, $field, $tableAlias);
5139     $this->_from .= " {$this->_aclFrom} ";
5140   }
5141
5142   /**
5143    * Join the temp table contacting contacts who are members of the filtered groups.
5144    *
5145    * If we are using an IN filter we use an inner join, otherwise a left join.
5146    *
5147    * @param string $baseTable
5148    * @param string $field
5149    * @param string $tableAlias
5150    */
5151   public function joinGroupTempTable($baseTable, $field, $tableAlias) {
5152     if ($this->groupTempTable) {
5153       if ($this->_params['gid_op'] == 'in') {
5154         $this->_from = " FROM $this->groupTempTable group_temp_table INNER JOIN $baseTable $tableAlias
5155         ON group_temp_table.id = $tableAlias.{$field} ";
5156       }
5157       else {
5158         $this->_from .= "
5159           LEFT JOIN $this->groupTempTable group_temp_table
5160           ON $tableAlias.{$field} = group_temp_table.id ";
5161       }
5162     }
5163   }
5164
5165   /**
5166    * Get all labels for fields that are used in a group concat.
5167    *
5168    * @param string $options
5169    *   comma separated option values.
5170    * @param string $baoName
5171    *   The BAO name for the field.
5172    * @param string $fieldName
5173    *   The name of the field for which labels should be retrieved.
5174    *
5175    * return string
5176    */
5177   public function getLabels($options, $baoName, $fieldName) {
5178     $types = explode(',', $options);
5179     $labels = array();
5180     foreach ($types as $value) {
5181       $labels[$value] = CRM_Core_PseudoConstant::getLabel($baoName, $fieldName, $value);
5182     }
5183     return implode(', ', array_filter($labels));
5184   }
5185
5186   /**
5187    * Add statistics columns.
5188    *
5189    * If a group by is in play then add columns for the statistics fields.
5190    *
5191    * This would lead to a new field in the $row such as $fieldName_sum and a new, matching
5192    * column header field.
5193    *
5194    * @param array $field
5195    * @param string $tableName
5196    * @param string $fieldName
5197    * @param array $select
5198    *
5199    * @return array
5200    */
5201   protected function addStatisticsToSelect($field, $tableName, $fieldName, $select) {
5202     foreach ($field['statistics'] as $stat => $label) {
5203       $alias = "{$tableName}_{$fieldName}_{$stat}";
5204       switch (strtolower($stat)) {
5205         case 'max':
5206         case 'sum':
5207           $select[] = "$stat({$field['dbAlias']}) as $alias";
5208           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
5209           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
5210           $this->_statFields[$label] = $alias;
5211           $this->_selectAliases[] = $alias;
5212           break;
5213
5214         case 'count':
5215           $select[] = "COUNT({$field['dbAlias']}) as $alias";
5216           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
5217           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
5218           $this->_statFields[$label] = $alias;
5219           $this->_selectAliases[] = $alias;
5220           break;
5221
5222         case 'count_distinct':
5223           $select[] = "COUNT(DISTINCT {$field['dbAlias']}) as $alias";
5224           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
5225           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
5226           $this->_statFields[$label] = $alias;
5227           $this->_selectAliases[] = $alias;
5228           break;
5229
5230         case 'avg':
5231           $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
5232           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
5233           $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
5234           $this->_statFields[$label] = $alias;
5235           $this->_selectAliases[] = $alias;
5236           break;
5237       }
5238     }
5239     return $select;
5240   }
5241
5242   /**
5243    * Add a basic field to the select clause.
5244    *
5245    * @param string $tableName
5246    * @param string $fieldName
5247    * @param array $field
5248    * @param string $select
5249    * @return array
5250    */
5251   protected function addBasicFieldToSelect($tableName, $fieldName, $field, $select) {
5252     $alias = "{$tableName}_{$fieldName}";
5253     $select[] = "{$field['dbAlias']} as $alias";
5254     $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
5255     $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
5256     $this->_selectAliases[] = $alias;
5257     return $select;
5258   }
5259
5260   /**
5261    * Set table alias.
5262    *
5263    * @param array $table
5264    * @param string $tableName
5265    *
5266    * @return string
5267    *   Alias for table.
5268    */
5269   protected function setTableAlias($table, $tableName) {
5270     if (!isset($table['alias'])) {
5271       $this->_columns[$tableName]['alias'] = substr($tableName, 8) .
5272         '_civireport';
5273     }
5274     else {
5275       $this->_columns[$tableName]['alias'] = $table['alias'] . '_civireport';
5276     }
5277
5278     $this->_aliases[$tableName] = $this->_columns[$tableName]['alias'];
5279     return $this->_aliases[$tableName];
5280   }
5281
5282   /**
5283    * Function to add columns to reports.
5284    *
5285    * This is ported from extended reports, which also adds join filters to the options.
5286    *
5287    * @param string $type
5288    * @param array $options
5289    *  - prefix - A string to prepend to the table name
5290    *  - prefix_label  A string to prepend to the fields
5291    *  - fields (bool) - should the fields for this table be made available
5292    *  - group_by (bool) - should the group bys for this table be made available.
5293    *  - order_by (bool) - should the group bys for this table be made available.
5294    *  - filters (bool) - should the filters for this table by made available.
5295    *  - fields_defaults (array) array of fields that should be displayed by default.
5296    *  - filters_defaults (array) array of fields that should be filtered by default.
5297    *  - join_filters (array) fields available for filtering joins (requires additional custom code).
5298    *  - join_fields (array) fields available from join (requires additional custom code).
5299    *  - group_by_defaults (array) array of group bys that should be applied by default.
5300    *  - order_by_defaults (array) array of order bys that should be applied by default.
5301    *  - custom_fields (array) array of entity types for custom fields (not usually required).
5302    *  - contact_type (string) optional restriction on contact type for some tables.
5303    *  - fields_excluded (array) fields that are in the generic set for the table but not in the report.
5304    *
5305    * @return array
5306    */
5307   protected function getColumns($type, $options = array()) {
5308     $defaultOptions = array(
5309       'prefix' => '',
5310       'prefix_label' => '',
5311       'fields' => TRUE,
5312       'group_bys' => FALSE,
5313       'order_bys' => TRUE,
5314       'filters' => TRUE,
5315       'join_filters' => FALSE,
5316       'fields_defaults' => array(),
5317       'filters_defaults' => array(),
5318       'group_bys_defaults' => array(),
5319       'order_bys_defaults' => array(),
5320     );
5321     $options = array_merge($defaultOptions, $options);
5322
5323     $fn = 'get' . $type . 'Columns';
5324     return $this->$fn($options);
5325   }
5326
5327   /**
5328    * Get columns for contact table.
5329    *
5330    * @param array $options
5331    *
5332    * @return array
5333    */
5334   protected function getContactColumns($options = array()) {
5335     $defaultOptions = array(
5336       'custom_fields' => array('Individual', 'Contact', 'Organization'),
5337       'fields_defaults' => array('display_name', 'id'),
5338       'order_bys_defaults' => array('sort_name ASC'),
5339       'contact_type' => NULL,
5340     );
5341
5342     $options = array_merge($defaultOptions, $options);
5343
5344     $tableAlias = $options['prefix'] . 'contact';
5345
5346     $spec = array(
5347       $options['prefix'] . 'display_name' => array(
5348         'name' => 'display_name',
5349         'title' => $options['prefix_label'] . ts('Contact Name'),
5350         'is_fields' => TRUE,
5351       ),
5352       $options['prefix'] . 'sort_name' => array(
5353         'name' => 'sort_name',
5354         'title' => $options['prefix_label'] . ts('Contact Name (in sort format)'),
5355         'is_fields' => TRUE,
5356         'is_filters' => TRUE,
5357         'is_order_bys' => TRUE,
5358       ),
5359       $options['prefix'] . 'id' => array(
5360         'name' => 'id',
5361         'title' => $options['prefix_label'] . ts('Contact ID'),
5362         'alter_display' => 'alterContactID',
5363         'type' => CRM_Utils_Type::T_INT,
5364         'is_order_bys' => TRUE,
5365         'is_group_bys' => TRUE,
5366         'is_fields' => TRUE,
5367         'is_filters' => TRUE,
5368       ),
5369       $options['prefix'] . 'external_identifier' => array(
5370         'name' => 'external_identifier',
5371         'title' => $options['prefix_label'] . ts('External ID'),
5372         'type' => CRM_Utils_Type::T_INT,
5373         'is_fields' => TRUE,
5374       ),
5375       $options['prefix'] . 'contact_type' => array(
5376         'title' => $options['prefix_label'] . ts('Contact Type'),
5377         'name' => 'contact_type',
5378         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5379         'options' => CRM_Contact_BAO_Contact::buildOptions('contact_type'),
5380         'is_fields' => TRUE,
5381         'is_filters' => TRUE,
5382         'is_group_bys' => TRUE,
5383       ),
5384       $options['prefix'] . 'contact_sub_type' => array(
5385         'title' => $options['prefix_label'] . ts('Contact Sub Type'),
5386         'name' => 'contact_sub_type',
5387         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5388         'options' => CRM_Contact_BAO_Contact::buildOptions('contact_sub_type'),
5389         'is_fields' => TRUE,
5390         'is_filters' => TRUE,
5391         'is_group_bys' => TRUE,
5392       ),
5393       $options['prefix'] . 'is_deleted' => array(
5394         'title' => $options['prefix_label'] . ts('Is deleted'),
5395         'name' => 'is_deleted',
5396         'type' => CRM_Utils_Type::T_BOOLEAN,
5397         'is_fields' => FALSE,
5398         'is_filters' => TRUE,
5399         'is_group_bys' => FALSE,
5400       ),
5401     );
5402     $individualFields = array(
5403       $options['prefix'] . 'first_name' => array(
5404         'name' => 'first_name',
5405         'title' => $options['prefix_label'] . ts('First Name'),
5406         'is_fields' => TRUE,
5407         'is_filters' => TRUE,
5408         'is_order_bys' => TRUE,
5409       ),
5410       $options['prefix'] . 'middle_name' => array(
5411         'name' => 'middle_name',
5412         'title' => $options['prefix_label'] . ts('Middle Name'),
5413         'is_fields' => TRUE,
5414       ),
5415       $options['prefix'] . 'last_name' => array(
5416         'name' => 'last_name',
5417         'title' => $options['prefix_label'] . ts('Last Name'),
5418         'default_order' => 'ASC',
5419         'is_fields' => TRUE,
5420       ),
5421       $options['prefix'] . 'nick_name' => array(
5422         'name' => 'nick_name',
5423         'title' => $options['prefix_label'] . ts('Nick Name'),
5424         'is_fields' => TRUE,
5425       ),
5426       $options['prefix'] . 'prefix_id' => array(
5427         'name' => 'prefix_id',
5428         'title' => $options['prefix_label'] . ts('Prefix'),
5429         'options' => CRM_Contact_BAO_Contact::buildOptions('prefix_id'),
5430         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5431         'is_fields' => TRUE,
5432         'is_filters' => TRUE,
5433       ),
5434       $options['prefix'] . 'suffix_id' => array(
5435         'name' => 'suffix_id',
5436         'title' => $options['prefix_label'] . ts('Suffix'),
5437         'options' => CRM_Contact_BAO_Contact::buildOptions('suffix_id'),
5438         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5439         'is_fields' => TRUE,
5440         'is_filters' => TRUE,
5441       ),
5442       $options['prefix'] . 'gender_id' => array(
5443         'name' => 'gender_id',
5444         'title' => $options['prefix_label'] . ts('Gender'),
5445         'options' => CRM_Contact_BAO_Contact::buildOptions('gender_id'),
5446         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5447         'is_fields' => TRUE,
5448         'is_filters' => TRUE,
5449       ),
5450       'birth_date' => array(
5451         'title' => $options['prefix_label'] . ts('Birth Date'),
5452         'operatorType' => CRM_Report_Form::OP_DATE,
5453         'type' => CRM_Utils_Type::T_DATE,
5454         'is_fields' => TRUE,
5455         'is_filters' => TRUE,
5456       ),
5457       'age' => array(
5458         'title' => $options['prefix_label'] . ts('Age'),
5459         'dbAlias' => 'TIMESTAMPDIFF(YEAR, ' . $tableAlias . '_civireport.birth_date, CURDATE())',
5460         'type' => CRM_Utils_Type::T_INT,
5461         'is_fields' => TRUE,
5462       ),
5463       $options['prefix'] . 'is_deceased' => array(
5464         'title' => $options['prefix_label'] . ts('Is deceased'),
5465         'name' => 'is_deceased',
5466         'type' => CRM_Utils_Type::T_BOOLEAN,
5467         'is_fields' => FALSE,
5468         'is_filters' => TRUE,
5469         'is_group_bys' => FALSE,
5470       ),
5471     );
5472     if (!$options['contact_type'] || $options['contact_type'] === 'Individual') {
5473       $spec = array_merge($spec, $individualFields);
5474     }
5475
5476     if (!empty($options['custom_fields'])) {
5477       $this->_customGroupExtended[$options['prefix'] . 'civicrm_contact'] = array(
5478         'extends' => $options['custom_fields'],
5479         'title' => $options['prefix_label'],
5480         'filters' => $options['filters'],
5481         'prefix' => $options['prefix'],
5482         'prefix_label' => $options['prefix_label'],
5483       );
5484     }
5485
5486     return $this->buildColumns($spec, $options['prefix'] . 'civicrm_contact', 'CRM_Contact_DAO_Contact', $tableAlias, $this->getDefaultsFromOptions($options), $options);
5487   }
5488
5489   /**
5490    * Get address columns to add to array.
5491    *
5492    * @param array $options
5493    *  - prefix Prefix to add to table (in case of more than one instance of the table)
5494    *  - prefix_label Label to give columns from this address table instance
5495    *  - group_bys enable these fields for group by - default false
5496    *  - order_bys enable these fields for order by
5497    *  - filters enable these fields for filtering
5498    *
5499    * @return array address columns definition
5500    */
5501   protected function getAddressColumns($options = array()) {
5502     $defaultOptions = array(
5503       'prefix' => '',
5504       'prefix_label' => '',
5505       'fields' => TRUE,
5506       'group_bys' => FALSE,
5507       'order_bys' => TRUE,
5508       'filters' => TRUE,
5509       'join_filters' => FALSE,
5510       'fields_defaults' => array(),
5511       'filters_defaults' => array(),
5512       'group_bys_defaults' => array(),
5513       'order_bys_defaults' => array(),
5514     );
5515
5516     $options = array_merge($defaultOptions, $options);
5517     $defaults = $this->getDefaultsFromOptions($options);
5518     $tableAlias = $options['prefix'] . 'address';
5519
5520     $spec = array(
5521       $options['prefix'] . 'name' => array(
5522         'title' => ts($options['prefix_label'] . 'Address Name'),
5523         'name' => 'name',
5524         'is_fields' => TRUE,
5525       ),
5526       $options['prefix'] . 'street_number' => array(
5527         'name' => 'street_number',
5528         'title' => ts($options['prefix_label'] . 'Street Number'),
5529         'type' => 1,
5530         'is_fields' => TRUE,
5531       ),
5532       $options['prefix'] . 'odd_street_number' => array(
5533         'title' => ts('Odd / Even Street Number'),
5534         'name' => 'odd_street_number',
5535         'type' => CRM_Utils_Type::T_INT,
5536         'no_display' => TRUE,
5537         'required' => TRUE,
5538         'dbAlias' => '(address_civireport.street_number % 2)',
5539         'is_fields' => TRUE,
5540         'is_order_bys' => TRUE,
5541       ),
5542       $options['prefix'] . 'street_name' => array(
5543         'name' => 'street_name',
5544         'title' => ts($options['prefix_label'] . 'Street Name'),
5545         'type' => 1,
5546         'is_fields' => TRUE,
5547         'is_filters' => TRUE,
5548         'operator' => 'like',
5549         'is_order_bys' => TRUE,
5550       ),
5551       $options['prefix'] . 'street_address' => array(
5552         'title' => ts($options['prefix_label'] . 'Street Address'),
5553         'name' => 'street_address',
5554         'is_fields' => TRUE,
5555         'is_filters' => TRUE,
5556         'is_group_bys' => TRUE,
5557       ),
5558       $options['prefix'] . 'supplemental_address_1' => array(
5559         'title' => ts($options['prefix_label'] . 'Supplementary Address Field 1'),
5560         'name' => 'supplemental_address_1',
5561         'is_fields' => TRUE,
5562       ),
5563       $options['prefix'] . 'supplemental_address_2' => array(
5564         'title' => ts($options['prefix_label'] . 'Supplementary Address Field 2'),
5565         'name' => 'supplemental_address_2',
5566         'is_fields' => TRUE,
5567       ),
5568       $options['prefix'] . 'supplemental_address_3' => array(
5569         'title' => ts($options['prefix_label'] . 'Supplementary Address Field 3'),
5570         'name' => 'supplemental_address_3',
5571         'is_fields' => TRUE,
5572       ),
5573       $options['prefix'] . 'street_number' => array(
5574         'name' => 'street_number',
5575         'title' => ts($options['prefix_label'] . 'Street Number'),
5576         'type' => 1,
5577         'is_order_bys' => TRUE,
5578         'is_filters' => TRUE,
5579         'is_fields' => TRUE,
5580       ),
5581       $options['prefix'] . 'street_unit' => array(
5582         'name' => 'street_unit',
5583         'title' => ts($options['prefix_label'] . 'Street Unit'),
5584         'type' => 1,
5585         'is_fields' => TRUE,
5586       ),
5587       $options['prefix'] . 'city' => array(
5588         'title' => ts($options['prefix_label'] . 'City'),
5589         'name' => 'city',
5590         'operator' => 'like',
5591         'is_fields' => TRUE,
5592         'is_filters' => TRUE,
5593         'is_group_bys' => TRUE,
5594         'is_order_bys' => TRUE,
5595       ),
5596       $options['prefix'] . 'postal_code' => array(
5597         'title' => ts($options['prefix_label'] . 'Postal Code'),
5598         'name' => 'postal_code',
5599         'type' => 1,
5600         'is_fields' => TRUE,
5601         'is_filters' => TRUE,
5602         'is_group_bys' => TRUE,
5603         'is_order_bys' => TRUE,
5604       ),
5605       $options['prefix'] . 'postal_code_suffix' => array(
5606         'title' => ts($options['prefix_label'] . 'Postal Code Suffix'),
5607         'name' => 'postal_code',
5608         'type' => 1,
5609         'is_fields' => TRUE,
5610         'is_filters' => TRUE,
5611         'is_group_bys' => TRUE,
5612         'is_order_bys' => TRUE,
5613       ),
5614       $options['prefix'] . 'county_id' => array(
5615         'title' => ts($options['prefix_label'] . 'County'),
5616         'alter_display' => 'alterCountyID',
5617         'name' => 'county_id',
5618         'type' => CRM_Utils_Type::T_INT,
5619         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5620         'options' => CRM_Core_PseudoConstant::county(),
5621         'is_fields' => TRUE,
5622         'is_filters' => TRUE,
5623         'is_group_bys' => TRUE,
5624       ),
5625       $options['prefix'] . 'state_province_id' => array(
5626         'title' => ts($options['prefix_label'] . 'State/Province'),
5627         'alter_display' => 'alterStateProvinceID',
5628         'name' => 'state_province_id',
5629         'type' => CRM_Utils_Type::T_INT,
5630         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5631         'options' => CRM_Core_PseudoConstant::stateProvince(),
5632         'is_fields' => TRUE,
5633         'is_filters' => TRUE,
5634         'is_group_bys' => TRUE,
5635       ),
5636       $options['prefix'] . 'country_id' => array(
5637         'title' => ts($options['prefix_label'] . 'Country'),
5638         'alter_display' => 'alterCountryID',
5639         'name' => 'country_id',
5640         'is_fields' => TRUE,
5641         'is_filters' => TRUE,
5642         'is_group_bys' => TRUE,
5643         'type' => CRM_Utils_Type::T_INT,
5644         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
5645         'options' => CRM_Core_PseudoConstant::country(),
5646       ),
5647       $options['prefix'] . 'location_type_id' => array(
5648         'name' => 'is_primary',
5649         'title' => ts($options['prefix_label'] . 'Location Type'),
5650         'type' => CRM_Utils_Type::T_INT,
5651         'is_fields' => TRUE,
5652         'alter_display' => 'alterLocationTypeID',
5653       ),
5654       $options['prefix'] . 'id' => array(
5655         'title' => ts($options['prefix_label'] . 'ID'),
5656         'name' => 'id',
5657         'is_fields' => TRUE,
5658       ),
5659       $options['prefix'] . 'is_primary' => array(
5660         'name' => 'is_primary',
5661         'title' => ts($options['prefix_label'] . 'Primary Address?'),
5662         'type' => CRM_Utils_Type::T_BOOLEAN,
5663         'is_fields' => TRUE,
5664       ),
5665     );
5666     return $this->buildColumns($spec, $options['prefix'] . 'civicrm_address', 'CRM_Core_DAO_Address', $tableAlias, $defaults, $options);
5667   }
5668
5669   /**
5670    * Build the columns.
5671    *
5672    * The normal report class needs you to remember to do a few things that are often erratic
5673    *
5674    * 1) use a unique key for any field that might not be unique (e.g. start date, label)
5675    * - this class will prepend an alias to the key & set the 'name' if you don't set it yourself.
5676    *  You can suppress the alias with 'no_field_disambiguation' if transitioning existing reports. This
5677    *  means any saved filters / fields on saved report instances. This will mean that matching names from
5678    *  different tables may be ambigious, but it will smooth any code transition.
5679    * - note that it assumes the value being passed in is the actual table field name
5680    *
5681    * 2) set the field & set it to no display if you don't want the field but you might want to use the field in other
5682    * contexts - the code looks up the fields array for data - so it both defines the field spec & the fields you want to show
5683    *
5684    * 3) this function also sets the 'metadata' array - the extended report class now uses this in place
5685    *  of the fields array to reduce the issues caused when metadata is needed but 'fields' are not defined. Code in
5686    *  the core classes can start to move towards that.
5687    *
5688    * @param array $specs
5689    * @param string $tableName
5690    * @param string $daoName
5691    * @param string $tableAlias
5692    * @param array $defaults
5693    * @param array $options
5694    *
5695    * @return array
5696    */
5697   protected function buildColumns($specs, $tableName, $daoName = NULL, $tableAlias = NULL, $defaults = array(), $options = array()) {
5698     if (!$tableAlias) {
5699       $tableAlias = str_replace('civicrm_', '', $tableName);
5700     }
5701     $types = array('filters', 'group_bys', 'order_bys', 'join_filters');
5702     $columns = array($tableName => array_fill_keys($types, array()));
5703     // The code that uses this no longer cares if it is a DAO or BAO so just call it a DAO.
5704     $columns[$tableName]['dao'] = $daoName;
5705     $columns[$tableName]['alias'] = $tableAlias;
5706
5707     foreach ($specs as $specName => $spec) {
5708       if (empty($spec['name'])) {
5709         $spec['name'] = $specName;
5710       }
5711
5712       $fieldAlias = (empty($options['no_field_disambiguation']) ? $tableAlias . '_' : '') . $specName;
5713       $columns[$tableName]['metadata'][$fieldAlias] = $spec;
5714       $columns[$tableName]['fields'][$fieldAlias] = $spec;
5715       if (isset($defaults['fields_defaults']) && in_array($spec['name'], $defaults['fields_defaults'])) {
5716         $columns[$tableName]['fields'][$fieldAlias]['default'] = TRUE;
5717       }
5718
5719       if (!$spec['is_fields'] || (isset($options['fields_excluded']) && in_array($specName, $options['fields_excluded']))) {
5720         $columns[$tableName]['fields'][$fieldAlias]['no_display'] = TRUE;
5721       }
5722
5723       if (isset($options['fields_required']) && in_array($specName, $options['fields_required'])) {
5724         $columns[$tableName]['fields'][$fieldAlias]['required'] = TRUE;
5725       }
5726
5727       foreach ($types as $type) {
5728         if ($options[$type] && !empty($spec['is_' . $type])) {
5729           $columns[$tableName][$type][$fieldAlias] = $spec;
5730           if (isset($defaults[$type . '_defaults']) && isset($defaults[$type . '_defaults'][$spec['name']])) {
5731             $columns[$tableName][$type][$fieldAlias]['default'] = $defaults[$type . '_defaults'][$spec['name']];
5732           }
5733         }
5734       }
5735     }
5736     return $columns;
5737   }
5738
5739   /**
5740    * Store group bys into array - so we can check elsewhere what is grouped.
5741    */
5742   protected function storeGroupByArray() {
5743
5744     if (!CRM_Utils_Array::value('group_bys', $this->_params)
5745       || !is_array($this->_params['group_bys'])) {
5746       $this->_params['group_bys'] = [];
5747     }
5748
5749     foreach ($this->_columns as $tableName => $table) {
5750       $table = $this->_columns[$tableName];
5751       if (array_key_exists('group_bys', $table)) {
5752         foreach ($table['group_bys'] as $fieldName => $fieldData) {
5753           $field = $this->_columns[$tableName]['metadata'][$fieldName];
5754           if (!empty($this->_params['group_bys'][$fieldName]) || !empty($fieldData['required'])) {
5755             if (!empty($field['chart'])) {
5756               $this->assign('chartSupported', TRUE);
5757             }
5758
5759             if (!empty($table['group_bys'][$fieldName]['frequency']) &&
5760               !empty($this->_params['group_bys_freq'][$fieldName])
5761             ) {
5762
5763               switch ($this->_params['group_bys_freq'][$fieldName]) {
5764                 case 'FISCALYEAR':
5765                   $this->_groupByArray[$tableName . '_' . $fieldName . '_start'] = self::fiscalYearOffset($field['dbAlias']);
5766
5767                 case 'YEAR':
5768                   $this->_groupByArray[$tableName . '_' . $fieldName . '_start'] = " {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
5769
5770                 default:
5771                   $this->_groupByArray[$tableName . '_' . $fieldName . '_start'] = "EXTRACT(YEAR_{$this->_params['group_bys_freq'][$fieldName]} FROM {$field['dbAlias']})";
5772
5773               }
5774             }
5775             else {
5776               if (!in_array($field['dbAlias'], $this->_groupByArray)) {
5777                 $this->_groupByArray[$tableName . '_' . $fieldName] = $field['dbAlias'];
5778               }
5779             }
5780           }
5781         }
5782
5783       }
5784     }
5785   }
5786
5787   /**
5788    * @param $options
5789    *
5790    * @return array
5791    */
5792   protected function getDefaultsFromOptions($options) {
5793     $defaults = array(
5794       'fields_defaults' => $options['fields_defaults'],
5795       'filters_defaults' => $options['filters_defaults'],
5796       'group_bys_defaults' => $options['group_bys_defaults'],
5797       'order_bys_defaults' => $options['order_bys_defaults'],
5798     );
5799     return $defaults;
5800   }
5801
5802   /**
5803    * Get the select clause for a field, wrapping in GROUP_CONCAT if appropriate.
5804    *
5805    * Full group by mode dictates that a field must either be in the group by function or
5806    * wrapped in a aggregate function. Here we wrap the field in GROUP_CONCAT if it is not in the
5807    * group concat.
5808    *
5809    * @param string $tableName
5810    * @param string $fieldName
5811    * @param string $field
5812    * @return string
5813    */
5814   protected function getSelectClauseWithGroupConcatIfNotGroupedBy($tableName, &$fieldName, &$field) {
5815     if ($this->groupConcatTested && (!empty($this->_groupByArray) || $this->isForceGroupBy)) {
5816       if ((empty($field['statistics']) || in_array('GROUP_CONCAT', $field['statistics']))) {
5817         $label = CRM_Utils_Array::value('title', $field);
5818         $alias = "{$tableName}_{$fieldName}";
5819         $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $label;
5820         $this->_selectAliases[] = $alias;
5821         if (empty($this->_groupByArray[$tableName . '_' . $fieldName])) {
5822           return "GROUP_CONCAT(DISTINCT {$field['dbAlias']}) as $alias";
5823         }
5824         return "({$field['dbAlias']}) as $alias";
5825       }
5826     }
5827   }
5828
5829   /**
5830    * Generate clause for the selected filter.
5831    *
5832    * @param array $field
5833    *   Field specification
5834    * @param string $fieldName
5835    *   Field name.
5836    *
5837    * @return string
5838    *   Relevant where clause.
5839    */
5840   protected function generateFilterClause($field, $fieldName) {
5841     if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
5842       if (CRM_Utils_Array::value('operatorType', $field) ==
5843         CRM_Report_Form::OP_MONTH
5844       ) {
5845         $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
5846         $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
5847         if (is_array($value) && !empty($value)) {
5848           return "(month({$field['dbAlias']}) $op (" . implode(', ', $value) .
5849             '))';
5850         }
5851       }
5852       else {
5853         $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
5854         $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
5855         $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
5856         $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
5857         $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
5858         return $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
5859       }
5860     }
5861     else {
5862       $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
5863       if ($op) {
5864         return $this->whereClause($field,
5865           $op,
5866           CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
5867           CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
5868           CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
5869         );
5870       }
5871     }
5872     return '';
5873   }
5874
5875 }