CRM/Report/Form.php

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