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