CRM/Report/Form.php

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