CRM/Report/Form.php

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