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