CRM/Report/Form.php

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