CRM/Report/Form.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.6                                                |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2015                                |
   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   protected $_options = array();
  92
  93   protected $_defaults = array();
  94
  95   /**
  96    * By default most reports hide contact id.
  97    * Setting this to true makes it available
  98    */
  99   protected $_exposeContactID = TRUE;
 100
 101   /**
 102    * Set of statistic fields
 103    *
 104    * @var array
 105    */
 106   protected $_statFields = array();
 107
 108   /**
 109    * Set of statistics data
 110    *
 111    * @var array
 112    */
 113   protected $_statistics = array();
 114
 115   /**
 116    * List of fields not to be repeated during display
 117    *
 118    * @var array
 119    */
 120   protected $_noRepeats = array();
 121
 122   /**
 123    * List of fields not to be displayed
 124    *
 125    * @var array
 126    */
 127   protected $_noDisplay = array();
 128
 129   /**
 130    * Object type that a custom group extends
 131    *
 132    * @var null
 133    */
 134   protected $_customGroupExtends = NULL;
 135   protected $_customGroupExtendsJoin = array();
 136   protected $_customGroupFilters = TRUE;
 137   protected $_customGroupGroupBy = FALSE;
 138   protected $_customGroupJoin = 'LEFT JOIN';
 139
 140   /**
 141    * Build tags filter
 142    */
 143   protected $_tagFilter = FALSE;
 144
 145   /**
 146    * specify entity table for tags filter
 147    */
 148   protected $_tagFilterTable = 'civicrm_contact';
 149
 150   /**
 151    * Build groups filter.
 152    *
 153    * @var bool
 154    */
 155   protected $_groupFilter = FALSE;
 156
 157   /**
 158    * Navigation fields
 159    *
 160    * @var array
 161    */
 162   public $_navigation = array();
 163
 164   public $_drilldownReport = array();
 165
 166   /**
 167    * Array of tabs to display on report.
 168    *
 169    * E.g we define the tab title, the tpl and the tab-specific part of the css or  html link.
 170    *
 171    *  $this->tabs['OrderBy'] = array(
 172    *    'title' => ts('Sorting'),
 173    *    'tpl' => 'OrderBy',
 174    *    'div_label' => 'order-by',
 175    *  );
 176    *
 177    * @var array
 178    */
 179   protected $tabs = array();
 180
 181   /**
 182    * An attribute for checkbox/radio form field layout
 183    *
 184    * @var array
 185    */
 186   protected $_fourColumnAttribute = array(
 187     '</td><td width="25%">',
 188     '</td><td width="25%">',
 189     '</td><td width="25%">',
 190     '</tr><tr><td>',
 191   );
 192
 193   protected $_force = 1;
 194
 195   protected $_params = NULL;
 196   protected $_formValues = NULL;
 197   protected $_instanceValues = NULL;
 198
 199   protected $_instanceForm = FALSE;
 200   protected $_criteriaForm = FALSE;
 201
 202   protected $_instanceButtonName = NULL;
 203   protected $_createNewButtonName = NULL;
 204   protected $_printButtonName = NULL;
 205   protected $_pdfButtonName = NULL;
 206   protected $_csvButtonName = NULL;
 207   protected $_groupButtonName = NULL;
 208   protected $_chartButtonName = NULL;
 209   protected $_csvSupported = TRUE;
 210   protected $_add2groupSupported = TRUE;
 211   protected $_groups = NULL;
 212   protected $_grandFlag = FALSE;
 213   protected $_rowsFound = NULL;
 214   protected $_selectAliases = array();
 215   protected $_rollup = NULL;
 216
 217   /**
 218    * @var array
 219    */
 220   protected $_aliases = array();
 221
 222   /**
 223    * @var string
 224    */
 225   protected $_where;
 226
 227   /**
 228    * @var string
 229    */
 230   protected $_from;
 231
 232   /**
 233    * SQL Limit clause
 234    * @var  string
 235    */
 236   protected $_limit = NULL;
 237
 238   /**
 239    * This can be set to specify a limit to the number of rows
 240    * Since it is currently envisaged as part of the api usage it is only being applied
 241    * when $_output mode is not 'html' or 'group' so as not to have to interpret / mess with that part
 242    * of the code (see limit() fn
 243    * @var integer
 244    */
 245   protected $_limitValue = NULL;
 246
 247   /**
 248    * This can be set to specify row offset
 249    * See notes on _limitValue
 250    * @var integer
 251    */
 252   protected $_offsetValue = NULL;
 253   /**
 254    * @var null
 255    */
 256   protected $_sections = NULL;
 257   protected $_autoIncludeIndexedFieldsAsOrderBys = 0;
 258   protected $_absoluteUrl = FALSE;
 259
 260   /**
 261    * Flag to indicate if result-set is to be stored in a class variable which could be retrieved using getResultSet() method.
 262    *
 263    * @var boolean
 264    */
 265   protected $_storeResultSet = FALSE;
 266
 267   /**
 268    * When _storeResultSet Flag is set use this var to store result set in form of array
 269    *
 270    * @var boolean
 271    */
 272   protected $_resultSet = array();
 273
 274   /**
 275    * To what frequency group-by a date column
 276    *
 277    * @var array
 278    */
 279   protected $_groupByDateFreq = array(
 280     'MONTH' => 'Month',
 281     'YEARWEEK' => 'Week',
 282     'QUARTER' => 'Quarter',
 283     'YEAR' => 'Year',
 284   );
 285
 286   /**
 287    * Variables to hold the acl inner join and where clause
 288    */
 289   protected $_aclFrom = NULL;
 290   protected $_aclWhere = NULL;
 291
 292   /**
 293    * Array of DAO tables having columns included in SELECT or ORDER BY clause
 294    *
 295    * @var array
 296    */
 297   protected $_selectedTables;
 298
 299   /**
 300    * Output mode e.g 'print', 'csv', 'pdf'
 301    * @var string
 302    */
 303   protected $_outputMode;
 304
 305   public $_having = NULL;
 306   public $_select = NULL;
 307   public $_selectClauses = array();
 308   public $_columnHeaders = array();
 309   public $_orderBy = NULL;
 310   public $_orderByFields = array();
 311   public $_orderByArray = array();
 312   public $_groupBy = NULL;
 313   public $_whereClauses = array();
 314   public $_havingClauses = array();
 315
 316   /**
 317    * DashBoardRowCount Dashboard row count
 318    * @var Integer
 319    */
 320   public $_dashBoardRowCount;
 321
 322   /**
 323    * Is this being called without a form controller (ie. the report is being render outside the normal form
 324    * - e.g the api is retrieving the rows
 325    * @var boolean
 326    */
 327   public $noController = FALSE;
 328
 329   /**
 330    * Variable to hold the currency alias
 331    */
 332   protected $_currencyColumn = NULL;
 333
 334   /**
 335    * @var string
 336    */
 337   protected $_interval;
 338
 339   /**
 340    * @var bool
 341    */
 342   protected $_sendmail;
 343
 344   /**
 345    * @var int
 346    */
 347   protected $_chartId;
 348
 349   /**
 350    * @var int
 351    */
 352   public $_section;
 353
 354   /**
 355    * @var string Report description.
 356    */
 357   public $_description;
 358
 359   /**
 360    * @var bool Is an address field selected.
 361    *   This was intended to determine if the address table should be joined in
 362    *   The isTableSelected function is now preferred for this purpose
 363    */
 364   protected $_addressField;
 365
 366   /**
 367    * @var bool Is an email field selected.
 368    *   This was intended to determine if the email table should be joined in
 369    *   The isTableSelected function is now preferred for this purpose
 370    */
 371   protected $_emailField;
 372
 373   /**
 374    * @var bool Is a phone field selected.
 375    *   This was intended to determine if the phone table should be joined in
 376    *   The isTableSelected function is now preferred for this purpose
 377    */
 378   protected $_phoneField;
 379
 380   /**
 381    * @var bool Create new report instance? (or update existing) on save.
 382    */
 383   protected $_createNew;
 384
 385   /**
 386    * Class constructor.
 387    */
 388   public function __construct() {
 389     parent::__construct();
 390
 391     $this->addClass('crm-report-form');
 392
 393     if ($this->_tagFilter) {
 394       $this->buildTagFilter();
 395     }
 396     if ($this->_exposeContactID) {
 397       if (array_key_exists('civicrm_contact', $this->_columns)) {
 398         $this->_columns['civicrm_contact']['fields']['exposed_id'] = array(
 399           'name' => 'id',
 400           'title' => 'Contact ID',
 401           'no_repeat' => TRUE,
 402         );
 403       }
 404     }
 405
 406     if ($this->_groupFilter) {
 407       $this->buildGroupFilter();
 408     }
 409
 410     // Get all custom groups
 411     $allGroups = CRM_Core_PseudoConstant::get('CRM_Core_DAO_CustomField', 'custom_group_id');
 412
 413     // Get the custom groupIds for which the user has VIEW permission
 414     // If the user has 'access all custom data' permission, we'll leave $permCustomGroupIds empty
 415     // and addCustomDataToColumns() will allow access to all custom groups.
 416     $permCustomGroupIds = array();
 417     if (!CRM_Core_Permission::check('access all custom data')) {
 418       $permCustomGroupIds = CRM_ACL_API::group(CRM_Core_Permission::VIEW, NULL, 'civicrm_custom_group', $allGroups, NULL);
 419       // do not allow custom data for reports if user doesn't have
 420       // permission to access custom data.
 421       if (!empty($this->_customGroupExtends) && empty($permCustomGroupIds)) {
 422         $this->_customGroupExtends = array();
 423       }
 424     }
 425
 426     // merge custom data columns to _columns list, if any
 427     $this->addCustomDataToColumns(TRUE, $permCustomGroupIds);
 428
 429     // add / modify display columns, filters ..etc
 430     CRM_Utils_Hook::alterReportVar('columns', $this->_columns, $this);
 431
 432     //assign currencyColumn variable to tpl
 433     $this->assign('currencyColumn', $this->_currencyColumn);
 434   }
 435
 436   /**
 437    * Shared pre-process function.
 438    *
 439    * If overriding preProcess function this should still be called.
 440    *
 441    * @throws \Exception
 442    */
 443   public function preProcessCommon() {
 444     $this->_force
 445       = CRM_Utils_Request::retrieve(
 446       'force',
 447       'Boolean',
 448       CRM_Core_DAO::$_nullObject
 449       );
 450
 451     $this->_dashBoardRowCount
 452       = CRM_Utils_Request::retrieve(
 453       'rowCount',
 454       'Integer',
 455       CRM_Core_DAO::$_nullObject
 456       );
 457
 458     $this->_section = CRM_Utils_Request::retrieve('section', 'Integer', CRM_Core_DAO::$_nullObject);
 459
 460     $this->assign('section', $this->_section);
 461     CRM_Core_Region::instance('page-header')->add(array(
 462       'markup' => sprintf('<!-- Report class: [%s] -->', htmlentities(get_class($this))),
 463     ));
 464     if (!$this->noController) {
 465       $this->setID($this->get('instanceId'));
 466
 467       if (!$this->_id) {
 468         $this->setID(CRM_Report_Utils_Report::getInstanceID());
 469         if (!$this->_id) {
 470           $this->setID(CRM_Report_Utils_Report::getInstanceIDForPath());
 471         }
 472       }
 473
 474       // set qfkey so that pager picks it up and use it in the "Next > Last >>" links.
 475       // FIXME: Note setting it in $_GET doesn't work, since pager generates link based on QUERY_STRING
 476       $_SERVER['QUERY_STRING'] .= "&qfKey={$this->controller->_key}";
 477     }
 478
 479     if ($this->_id) {
 480       $this->assign('instanceId', $this->_id);
 481       $params = array('id' => $this->_id);
 482       $this->_instanceValues = array();
 483       CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_ReportInstance',
 484         $params,
 485         $this->_instanceValues
 486       );
 487       if (empty($this->_instanceValues)) {
 488         CRM_Core_Error::fatal("Report could not be loaded.");
 489       }
 490       $this->_title = $this->_instanceValues['title'];
 491       if (!empty($this->_instanceValues['permission']) &&
 492         (!(CRM_Core_Permission::check($this->_instanceValues['permission']) ||
 493           CRM_Core_Permission::check('administer Reports')
 494         ))
 495       ) {
 496         CRM_Utils_System::permissionDenied();
 497         CRM_Utils_System::civiExit();
 498       }
 499
 500       $formValues = CRM_Utils_Array::value('form_values', $this->_instanceValues);
 501       if ($formValues) {
 502         $this->_formValues = unserialize($formValues);
 503       }
 504       else {
 505         $this->_formValues = NULL;
 506       }
 507
 508       // lets always do a force if reset is found in the url.
 509       if (!empty($_REQUEST['reset'])) {
 510         $this->_force = 1;
 511       }
 512
 513       // set the mode
 514       $this->assign('mode', 'instance');
 515     }
 516     elseif (!$this->noController) {
 517       list($optionValueID, $optionValue) = CRM_Report_Utils_Report::getValueIDFromUrl();
 518       $instanceCount = CRM_Report_Utils_Report::getInstanceCount($optionValue);
 519       if (($instanceCount > 0) && $optionValueID) {
 520         $this->assign('instanceUrl',
 521           CRM_Utils_System::url('civicrm/report/list',
 522             "reset=1&ovid=$optionValueID"
 523           )
 524         );
 525       }
 526       if ($optionValueID) {
 527         $this->_description = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', $optionValueID, 'description');
 528       }
 529
 530       // set the mode
 531       $this->assign('mode', 'template');
 532     }
 533
 534     // lets display the Report Settings section
 535     $this->_instanceForm = $this->_force || $this->_id || (!empty($_POST));
 536
 537     // Do not display Report Settings section if administer Reports permission is absent OR
 538     // if report instance is reserved and administer reserved reports absent
 539     if (!CRM_Core_Permission::check('administer Reports') ||
 540       ($this->_instanceValues['is_reserved'] &&
 541         !CRM_Core_Permission::check('administer reserved reports'))
 542     ) {
 543       $this->_instanceForm = FALSE;
 544     }
 545
 546     $this->assign('criteriaForm', FALSE);
 547     // Display Report Criteria section if user has access Report Criteria OR administer Reports AND report instance is not reserved
 548     if (CRM_Core_Permission::check('administer Reports') ||
 549       CRM_Core_Permission::check('access Report Criteria')
 550     ) {
 551       if (!$this->_instanceValues['is_reserved'] ||
 552         CRM_Core_Permission::check('administer reserved reports')
 553       ) {
 554         $this->assign('criteriaForm', TRUE);
 555         $this->_criteriaForm = TRUE;
 556       }
 557     }
 558
 559     $this->_instanceButtonName = $this->getButtonName('submit', 'save');
 560     $this->_createNewButtonName = $this->getButtonName('submit', 'next');
 561     $this->_printButtonName = $this->getButtonName('submit', 'print');
 562     $this->_pdfButtonName = $this->getButtonName('submit', 'pdf');
 563     $this->_csvButtonName = $this->getButtonName('submit', 'csv');
 564     $this->_groupButtonName = $this->getButtonName('submit', 'group');
 565     $this->_chartButtonName = $this->getButtonName('submit', 'chart');
 566   }
 567
 568   /**
 569    * Add bread crumb.
 570    */
 571   public function addBreadCrumb() {
 572     $breadCrumbs
 573       = array(
 574         array(
 575           'title' => ts('Report Templates'),
 576           'url' => CRM_Utils_System::url('civicrm/admin/report/template/list', 'reset=1'),
 577         ),
 578       );
 579
 580     CRM_Utils_System::appendBreadCrumb($breadCrumbs);
 581   }
 582
 583   /**
 584    * Pre process function.
 585    *
 586    * Called prior to build form.
 587    */
 588   public function preProcess() {
 589     $this->preProcessCommon();
 590
 591     if (!$this->_id) {
 592       $this->addBreadCrumb();
 593     }
 594
 595     foreach ($this->_columns as $tableName => $table) {
 596       // set alias
 597       if (!isset($table['alias'])) {
 598         $this->_columns[$tableName]['alias'] = substr($tableName, 8) .
 599           '_civireport';
 600       }
 601       else {
 602         $this->_columns[$tableName]['alias'] = $table['alias'] . '_civireport';
 603       }
 604
 605       $this->_aliases[$tableName] = $this->_columns[$tableName]['alias'];
 606
 607       $daoOrBaoName = NULL;
 608       // higher preference to bao object
 609       if (array_key_exists('bao', $table)) {
 610         $daoOrBaoName = $table['bao'];
 611         $expFields = $daoOrBaoName::exportableFields();
 612       }
 613       elseif (array_key_exists('dao', $table)) {
 614         $daoOrBaoName = $table['dao'];
 615         $expFields = $daoOrBaoName::export();
 616       }
 617       else {
 618         $expFields = array();
 619       }
 620
 621       $doNotCopy = array('required');
 622
 623       $fieldGroups = array('fields', 'filters', 'group_bys', 'order_bys');
 624       foreach ($fieldGroups as $fieldGrp) {
 625         if (!empty($table[$fieldGrp]) && is_array($table[$fieldGrp])) {
 626           foreach ($table[$fieldGrp] as $fieldName => $field) {
 627             // $name is the field name used to reference the BAO/DAO export fields array
 628             $name = isset($field['name']) ? $field['name'] : $fieldName;
 629
 630             // Sometimes the field name key in the BAO/DAO export fields array is
 631             // different from the actual database field name.
 632             // Unset $field['name'] so that actual database field name can be obtained
 633             // from the BAO/DAO export fields array.
 634             unset($field['name']);
 635
 636             if (array_key_exists($name, $expFields)) {
 637               foreach ($doNotCopy as $dnc) {
 638                 // unset the values we don't want to be copied.
 639                 unset($expFields[$name][$dnc]);
 640               }
 641               if (empty($field)) {
 642                 $this->_columns[$tableName][$fieldGrp][$fieldName] = $expFields[$name];
 643               }
 644               else {
 645                 foreach ($expFields[$name] as $property => $val) {
 646                   if (!array_key_exists($property, $field)) {
 647                     $this->_columns[$tableName][$fieldGrp][$fieldName][$property] = $val;
 648                   }
 649                 }
 650               }
 651             }
 652
 653             // fill other vars
 654             if (!empty($field['no_repeat'])) {
 655               $this->_noRepeats[] = "{$tableName}_{$fieldName}";
 656             }
 657             if (!empty($field['no_display'])) {
 658               $this->_noDisplay[] = "{$tableName}_{$fieldName}";
 659             }
 660
 661             // set alias = table-name, unless already set
 662             $alias = isset($field['alias']) ? $field['alias'] : (
 663               isset($this->_columns[$tableName]['alias']) ? $this->_columns[$tableName]['alias'] : $tableName
 664             );
 665             $this->_columns[$tableName][$fieldGrp][$fieldName]['alias'] = $alias;
 666
 667             // set name = fieldName, unless already set
 668             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['name'])) {
 669               $this->_columns[$tableName][$fieldGrp][$fieldName]['name'] = $name;
 670             }
 671
 672             // set dbAlias = alias.name, unless already set
 673             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'])) {
 674               $this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias']
 675                 = $alias . '.' .
 676                 $this->_columns[$tableName][$fieldGrp][$fieldName]['name'];
 677             }
 678
 679             // a few auto fills for filters
 680             if ($fieldGrp == 'filters') {
 681               // fill operator types
 682               if (!array_key_exists('operatorType', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 683                 switch (CRM_Utils_Array::value('type', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 684                   case CRM_Utils_Type::T_MONEY:
 685                   case CRM_Utils_Type::T_FLOAT:
 686                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
 687                     break;
 688
 689                   case CRM_Utils_Type::T_INT:
 690                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
 691                     break;
 692
 693                   case CRM_Utils_Type::T_DATE:
 694                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
 695                     break;
 696
 697                   case CRM_Utils_Type::T_BOOLEAN:
 698                     $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
 699                     if (!array_key_exists('options', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 700                       $this->_columns[$tableName][$fieldGrp][$fieldName]['options']
 701                         = array(
 702                           '' => ts('Any'),
 703                           '0' => ts('No'),
 704                           '1' => ts('Yes'),
 705                         );
 706                     }
 707                     break;
 708
 709                   default:
 710                     if ($daoOrBaoName &&
 711                       array_key_exists('pseudoconstant', $this->_columns[$tableName][$fieldGrp][$fieldName])
 712                     ) {
 713                       // with multiple options operator-type is generally multi-select
 714                       $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
 715                       if (!array_key_exists('options', $this->_columns[$tableName][$fieldGrp][$fieldName])) {
 716                         // fill options
 717                         $this->_columns[$tableName][$fieldGrp][$fieldName]['options'] = CRM_Core_PseudoConstant::get($daoOrBaoName, $fieldName);
 718                       }
 719                     }
 720                     break;
 721                 }
 722               }
 723             }
 724           }
 725         }
 726       }
 727
 728       // copy filters to a separate handy variable
 729       if (array_key_exists('filters', $table)) {
 730         $this->_filters[$tableName] = $this->_columns[$tableName]['filters'];
 731       }
 732
 733       if (array_key_exists('group_bys', $table)) {
 734         $groupBys[$tableName] = $this->_columns[$tableName]['group_bys'];
 735       }
 736
 737       if (array_key_exists('fields', $table)) {
 738         $reportFields[$tableName] = $this->_columns[$tableName]['fields'];
 739       }
 740     }
 741
 742     if ($this->_force) {
 743       $this->setDefaultValues(FALSE);
 744     }
 745
 746     CRM_Report_Utils_Get::processFilter($this->_filters, $this->_defaults);
 747     CRM_Report_Utils_Get::processGroupBy($groupBys, $this->_defaults);
 748     CRM_Report_Utils_Get::processFields($reportFields, $this->_defaults);
 749     CRM_Report_Utils_Get::processChart($this->_defaults);
 750
 751     if ($this->_force) {
 752       $this->_formValues = $this->_defaults;
 753       $this->postProcess();
 754     }
 755   }
 756
 757   /**
 758    * Set default values.
 759    *
 760    * @param bool $freeze
 761    *
 762    * @return array
 763    */
 764   public function setDefaultValues($freeze = TRUE) {
 765     $freezeGroup = array();
 766
 767     // FIXME: generalizing form field naming conventions would reduce
 768     // Lots of lines below.
 769     foreach ($this->_columns as $tableName => $table) {
 770       if (array_key_exists('fields', $table)) {
 771         foreach ($table['fields'] as $fieldName => $field) {
 772           if (empty($field['no_display'])) {
 773             if (isset($field['required'])) {
 774               // set default
 775               $this->_defaults['fields'][$fieldName] = 1;
 776
 777               if ($freeze) {
 778                 // find element object, so that we could use quickform's freeze method
 779                 // for required elements
 780                 $obj = $this->getElementFromGroup("fields", $fieldName);
 781                 if ($obj) {
 782                   $freezeGroup[] = $obj;
 783                 }
 784               }
 785             }
 786             elseif (isset($field['default'])) {
 787               $this->_defaults['fields'][$fieldName] = $field['default'];
 788             }
 789           }
 790         }
 791       }
 792
 793       if (array_key_exists('group_bys', $table)) {
 794         foreach ($table['group_bys'] as $fieldName => $field) {
 795           if (isset($field['default'])) {
 796             if (!empty($field['frequency'])) {
 797               $this->_defaults['group_bys_freq'][$fieldName] = 'MONTH';
 798             }
 799             $this->_defaults['group_bys'][$fieldName] = $field['default'];
 800           }
 801         }
 802       }
 803       if (array_key_exists('filters', $table)) {
 804         foreach ($table['filters'] as $fieldName => $field) {
 805           if (isset($field['default'])) {
 806             if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE
 807             ) {
 808               if (is_array($field['default'])) {
 809                 $this->_defaults["{$fieldName}_from"] = CRM_Utils_Array::value('from', $field['default']);
 810                 $this->_defaults["{$fieldName}_to"] = CRM_Utils_Array::value('to', $field['default']);
 811                 $this->_defaults["{$fieldName}_relative"] = 0;
 812               }
 813               else {
 814                 $this->_defaults["{$fieldName}_relative"] = $field['default'];
 815               }
 816             }
 817             else {
 818               $this->_defaults["{$fieldName}_value"] = $field['default'];
 819             }
 820           }
 821           //assign default value as "in" for multiselect
 822           //operator, To freeze the select element
 823           if (CRM_Utils_Array::value('operatorType', $field) ==
 824             CRM_Report_Form::OP_MULTISELECT
 825           ) {
 826             $this->_defaults["{$fieldName}_op"] = 'in';
 827           }
 828           if (CRM_Utils_Array::value('operatorType', $field) ==
 829             CRM_Report_Form::OP_ENTITYREF
 830           ) {
 831             $this->_defaults["{$fieldName}_op"] = 'in';
 832           }
 833           elseif (CRM_Utils_Array::value('operatorType', $field) ==
 834             CRM_Report_Form::OP_MULTISELECT_SEPARATOR
 835           ) {
 836             $this->_defaults["{$fieldName}_op"] = 'mhas';
 837           }
 838           elseif ($op = CRM_Utils_Array::value('default_op', $field)) {
 839             $this->_defaults["{$fieldName}_op"] = $op;
 840           }
 841         }
 842       }
 843
 844       if (
 845         empty($this->_formValues['order_bys']) &&
 846         (array_key_exists('order_bys', $table) &&
 847         is_array($table['order_bys']))
 848       ) {
 849         if (!array_key_exists('order_bys', $this->_defaults)) {
 850           $this->_defaults['order_bys'] = array();
 851         }
 852         foreach ($table['order_bys'] as $fieldName => $field) {
 853           if (!empty($field['default']) || !empty($field['default_order']) ||
 854             CRM_Utils_Array::value('default_is_section', $field) ||
 855             !empty($field['default_weight'])
 856           ) {
 857             $order_by = array(
 858               'column' => $fieldName,
 859               'order' => CRM_Utils_Array::value('default_order', $field, 'ASC'),
 860               'section' => CRM_Utils_Array::value('default_is_section', $field, 0),
 861             );
 862
 863             if (!empty($field['default_weight'])) {
 864               $this->_defaults['order_bys'][(int) $field['default_weight']] = $order_by;
 865             }
 866             else {
 867               array_unshift($this->_defaults['order_bys'], $order_by);
 868             }
 869           }
 870         }
 871       }
 872
 873       foreach ($this->_options as $fieldName => $field) {
 874         if (isset($field['default'])) {
 875           $this->_defaults['options'][$fieldName] = $field['default'];
 876         }
 877       }
 878     }
 879
 880     if (!empty($this->_submitValues)) {
 881       $this->preProcessOrderBy($this->_submitValues);
 882     }
 883     else {
 884       $this->preProcessOrderBy($this->_defaults);
 885     }
 886
 887     // lets finish freezing task here itself
 888     if (!empty($freezeGroup)) {
 889       foreach ($freezeGroup as $elem) {
 890         $elem->freeze();
 891       }
 892     }
 893
 894     if ($this->_formValues) {
 895       $this->_defaults = array_merge($this->_defaults, $this->_formValues);
 896     }
 897
 898     if ($this->_instanceValues) {
 899       $this->_defaults = array_merge($this->_defaults, $this->_instanceValues);
 900     }
 901
 902     CRM_Report_Form_Instance::setDefaultValues($this, $this->_defaults);
 903
 904     return $this->_defaults;
 905   }
 906
 907   /**
 908    * Get element from group.
 909    *
 910    * @param string $group
 911    * @param string $grpFieldName
 912    *
 913    * @return bool
 914    */
 915   public function getElementFromGroup($group, $grpFieldName) {
 916     $eleObj = $this->getElement($group);
 917     foreach ($eleObj->_elements as $index => $obj) {
 918       if ($grpFieldName == $obj->_attributes['name']) {
 919         return $obj;
 920       }
 921     }
 922     return FALSE;
 923   }
 924
 925   /**
 926    * Setter for $_params.
 927    *
 928    * @param array $params
 929    */
 930   public function setParams($params) {
 931     $this->_params = $params;
 932   }
 933
 934   /**
 935    * Setter for $_id.
 936    *
 937    * @param int $instanceID
 938    */
 939   public function setID($instanceID) {
 940     $this->_id = $instanceID;
 941   }
 942
 943   /**
 944    * Setter for $_force.
 945    *
 946    * @param bool $isForce
 947    */
 948   public function setForce($isForce) {
 949     $this->_force = $isForce;
 950   }
 951
 952   /**
 953    * Setter for $_limitValue.
 954    *
 955    * @param int $_limitValue
 956    */
 957   public function setLimitValue($_limitValue) {
 958     $this->_limitValue = $_limitValue;
 959   }
 960
 961   /**
 962    * Setter for $_offsetValue.
 963    *
 964    * @param int $_offsetValue
 965    */
 966   public function setOffsetValue($_offsetValue) {
 967     $this->_offsetValue = $_offsetValue;
 968   }
 969
 970   /**
 971    * Getter for $_defaultValues.
 972    *
 973    * @return array
 974    */
 975   public function getDefaultValues() {
 976     return $this->_defaults;
 977   }
 978
 979   /**
 980    * Add columns to report.
 981    */
 982   public function addColumns() {
 983     $options = array();
 984     $colGroups = NULL;
 985     foreach ($this->_columns as $tableName => $table) {
 986       if (array_key_exists('fields', $table)) {
 987         foreach ($table['fields'] as $fieldName => $field) {
 988           $groupTitle = '';
 989           if (empty($field['no_display'])) {
 990             foreach (array('table', 'field') as $var) {
 991               if (!empty(${$var}['grouping'])) {
 992                 if (!is_array(${$var}['grouping'])) {
 993                   $tableName = ${$var}['grouping'];
 994                 }
 995                 else {
 996                   $tableName = array_keys(${$var}['grouping']);
 997                   $tableName = $tableName[0];
 998                   $groupTitle = array_values(${$var}['grouping']);
 999                   $groupTitle = $groupTitle[0];
1000                 }
1001               }
1002             }
1003
1004             if (!$groupTitle && isset($table['group_title'])) {
1005               $groupTitle = $table['group_title'];
1006               // Having a group_title is secret code for being a custom group
1007               // which cryptically translates to needing an accordian.
1008               // here we make that explicit.
1009               $colGroups[$tableName]['use_accordian_for_field_selection'] = TRUE;
1010             }
1011
1012             $colGroups[$tableName]['fields'][$fieldName] = CRM_Utils_Array::value('title', $field);
1013             if ($groupTitle && empty($colGroups[$tableName]['group_title'])) {
1014               $colGroups[$tableName]['group_title'] = $groupTitle;
1015             }
1016             $options[$fieldName] = CRM_Utils_Array::value('title', $field);
1017           }
1018         }
1019       }
1020     }
1021
1022     $this->addCheckBox("fields", ts('Select Columns'), $options, NULL,
1023       NULL, NULL, NULL, $this->_fourColumnAttribute, TRUE
1024     );
1025     if (!empty($colGroups)) {
1026       $this->tabs['FieldSelection'] = array(
1027         'title' => ts('Columns'),
1028         'tpl' => 'FieldSelection',
1029         'div_label' => 'col-groups',
1030       );
1031
1032       // Note this assignment is only really required in buildForm. It is being 'over-called'
1033       // to reduce risk of being missed due to overridden functions.
1034       $this->assign('tabs', $this->tabs);
1035     }
1036
1037     $this->assign('colGroups', $colGroups);
1038   }
1039
1040   /**
1041    * Add filters to report.
1042    */
1043   public function addFilters() {
1044     $filters = $filterGroups = array();
1045     $count = 1;
1046
1047     foreach ($this->_filters as $table => $attributes) {
1048       if (isset($this->_columns[$table]['group_title'])) {
1049         // The presence of 'group_title' is secret code for 'is_a_custom_table'
1050         // which magically means to 'display in an accordian'
1051         // here we make this explicit.
1052         $filterGroups[$table] = array(
1053           'group_title' => $this->_columns[$table]['group_title'],
1054           'use_accordian_for_field_selection' => TRUE,
1055
1056         );
1057       }
1058       foreach ($attributes as $fieldName => $field) {
1059         // get ready with option value pair
1060         // @ 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
1061         // would be useful
1062         $operations = $this->getOperationPair(
1063           CRM_Utils_Array::value('operatorType', $field),
1064           $fieldName);
1065
1066         $filters[$table][$fieldName] = $field;
1067
1068         switch (CRM_Utils_Array::value('operatorType', $field)) {
1069           case CRM_Report_Form::OP_MONTH:
1070             if (!array_key_exists('options', $field) ||
1071               !is_array($field['options']) || empty($field['options'])
1072             ) {
1073               // If there's no option list for this filter, define one.
1074               $field['options'] = array(
1075                 1 => ts('January'),
1076                 2 => ts('February'),
1077                 3 => ts('March'),
1078                 4 => ts('April'),
1079                 5 => ts('May'),
1080                 6 => ts('June'),
1081                 7 => ts('July'),
1082                 8 => ts('August'),
1083                 9 => ts('September'),
1084                 10 => ts('October'),
1085                 11 => ts('November'),
1086                 12 => ts('December'),
1087               );
1088               // Add this option list to this column _columns. This is
1089               // required so that filter statistics show properly.
1090               $this->_columns[$table]['filters'][$fieldName]['options'] = $field['options'];
1091             }
1092           case CRM_Report_Form::OP_MULTISELECT:
1093           case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1094             // assume a multi-select field
1095             if (!empty($field['options']) ||
1096               $fieldName == 'state_province_id' || $fieldName == 'county_id'
1097             ) {
1098               $element = $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1099               if (count($operations) <= 1) {
1100                 $element->freeze();
1101               }
1102               if ($fieldName == 'state_province_id' ||
1103                 $fieldName == 'county_id'
1104               ) {
1105                 $this->addChainSelect($fieldName . '_value', array(
1106                   'multiple' => TRUE,
1107                   'label' => NULL,
1108                   'class' => 'huge',
1109                 ));
1110               }
1111               else {
1112                 $this->addElement('select', "{$fieldName}_value", NULL, $field['options'], array(
1113                   'style' => 'min-width:250px',
1114                   'class' => 'crm-select2 huge',
1115                   'multiple' => TRUE,
1116                   'placeholder' => ts('- select -'),
1117                 ));
1118               }
1119             }
1120             break;
1121
1122           case CRM_Report_Form::OP_SELECT:
1123             // assume a select field
1124             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1125             if (!empty($field['options'])) {
1126               $this->addElement('select', "{$fieldName}_value", NULL, $field['options']);
1127             }
1128             break;
1129
1130           case CRM_Report_Form::OP_ENTITYREF:
1131             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
1132             $this->setEntityRefDefaults($field, $table);
1133             $this->addEntityRef("{$fieldName}_value", NULL, $field['attributes']);
1134             break;
1135
1136           case CRM_Report_Form::OP_DATE:
1137             // build datetime fields
1138             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', 'From:', FALSE, $operations);
1139             $count++;
1140             break;
1141
1142           case CRM_Report_Form::OP_DATETIME:
1143             // build datetime fields
1144             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', 'From:', FALSE, $operations, 'searchDate', TRUE);
1145             $count++;
1146             break;
1147
1148           case CRM_Report_Form::OP_INT:
1149           case CRM_Report_Form::OP_FLOAT:
1150             // and a min value input box
1151             $this->add('text', "{$fieldName}_min", ts('Min'));
1152             // and a max value input box
1153             $this->add('text', "{$fieldName}_max", ts('Max'));
1154           default:
1155             // default type is string
1156             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations,
1157               array('onchange' => "return showHideMaxMinVal( '$fieldName', this.value );")
1158             );
1159             // we need text box for value input
1160             $this->add('text', "{$fieldName}_value", NULL, array('class' => 'huge'));
1161             break;
1162         }
1163       }
1164     }
1165     if (!empty($filters)) {
1166       $this->tabs['Filters'] = array(
1167         'title' => ts('Filters'),
1168         'tpl' => 'Filters',
1169         'div_label' => 'set-filters',
1170       );
1171     }
1172     $this->assign('filters', $filters);
1173     $this->assign('filterGroups', $filterGroups);
1174   }
1175
1176   /**
1177    * Function to assign the tabs to the template in the correct order.
1178    *
1179    * We want the tabs to wind up in this order (if not overridden).
1180    *
1181    *   - Field Selection
1182    *   - Group Bys
1183    *   - Order Bys
1184    *   - Other Options
1185    *   - Filters
1186    */
1187   protected function assignTabs() {
1188     $order = array(
1189       'FieldSelection',
1190       'GroupBy',
1191       'OrderBy',
1192       'ReportOptions',
1193       'Filters',
1194     );
1195     $order = array_intersect_key(array_fill_keys($order, 1), $this->tabs);
1196     $order = array_merge($order, $this->tabs);
1197     $this->assign('tabs', $order);
1198   }
1199
1200   /**
1201    * Add options defined in $this->_options to the report.
1202    */
1203   public function addOptions() {
1204     if (!empty($this->_options)) {
1205       // FIXME: For now lets build all elements as checkboxes.
1206       // Once we clear with the format we can build elements based on type
1207
1208       foreach ($this->_options as $fieldName => $field) {
1209         $options = array();
1210
1211         if ($field['type'] == 'select') {
1212           $this->addElement('select', "{$fieldName}", $field['title'], $field['options']);
1213         }
1214         elseif ($field['type'] == 'checkbox') {
1215           $options[$field['title']] = $fieldName;
1216           $this->addCheckBox($fieldName, NULL,
1217             $options, NULL,
1218             NULL, NULL, NULL, $this->_fourColumnAttribute
1219           );
1220         }
1221       }
1222     }
1223     if (!empty($this->_options)) {
1224       $this->tabs['ReportOptions'] = array(
1225         'title' => ts('Display Options'),
1226         'tpl' => 'ReportOptions',
1227         'div_label' => 'other-options',
1228       );
1229     }
1230     $this->assign('otherOptions', $this->_options);
1231   }
1232
1233   /**
1234    * Add chart options to the report.
1235    */
1236   public function addChartOptions() {
1237     if (!empty($this->_charts)) {
1238       $this->addElement('select', "charts", ts('Chart'), $this->_charts);
1239       $this->assign('charts', $this->_charts);
1240       $this->addElement('submit', $this->_chartButtonName, ts('View'));
1241     }
1242   }
1243
1244   /**
1245    * Add group by options to the report.
1246    */
1247   public function addGroupBys() {
1248     $options = $freqElements = array();
1249
1250     foreach ($this->_columns as $tableName => $table) {
1251       if (array_key_exists('group_bys', $table)) {
1252         foreach ($table['group_bys'] as $fieldName => $field) {
1253           if (!empty($field)) {
1254             $options[$field['title']] = $fieldName;
1255             if (!empty($field['frequency'])) {
1256               $freqElements[$field['title']] = $fieldName;
1257             }
1258           }
1259         }
1260       }
1261     }
1262     $this->addCheckBox("group_bys", ts('Group by columns'), $options, NULL,
1263       NULL, NULL, NULL, $this->_fourColumnAttribute
1264     );
1265     $this->assign('groupByElements', $options);
1266     if (!empty($options)) {
1267       $this->tabs['GroupBy'] = array(
1268         'title' => ts('Grouping'),
1269         'tpl' => 'GroupBy',
1270         'div_label' => 'group-by-elements',
1271       );
1272     }
1273
1274     foreach ($freqElements as $name) {
1275       $this->addElement('select', "group_bys_freq[$name]",
1276         ts('Frequency'), $this->_groupByDateFreq
1277       );
1278     }
1279   }
1280
1281   /**
1282    * Add data for order by tab.
1283    */
1284   public function addOrderBys() {
1285     $options = array();
1286     foreach ($this->_columns as $tableName => $table) {
1287
1288       // Report developer may define any column to order by; include these as order-by options.
1289       if (array_key_exists('order_bys', $table)) {
1290         foreach ($table['order_bys'] as $fieldName => $field) {
1291           if (!empty($field)) {
1292             $options[$fieldName] = $field['title'];
1293           }
1294         }
1295       }
1296
1297       // Add searchable custom fields as order-by options, if so requested
1298       // (These are already indexed, so allowing to order on them is cheap.)
1299
1300       if ($this->_autoIncludeIndexedFieldsAsOrderBys &&
1301         array_key_exists('extends', $table) && !empty($table['extends'])
1302       ) {
1303         foreach ($table['fields'] as $fieldName => $field) {
1304           if (empty($field['no_display'])) {
1305             $options[$fieldName] = $field['title'];
1306           }
1307         }
1308       }
1309     }
1310
1311     asort($options);
1312
1313     $this->assign('orderByOptions', $options);
1314     if (!empty($options)) {
1315       $this->tabs['OrderBy'] = array(
1316         'title' => ts('Sorting'),
1317         'tpl' => 'OrderBy',
1318         'div_label' => 'order-by-elements',
1319       );
1320     }
1321
1322     if (!empty($options)) {
1323       $options = array(
1324         '-' => ' - none - ',
1325       ) + $options;
1326       for ($i = 1; $i <= 5; $i++) {
1327         $this->addElement('select', "order_bys[{$i}][column]", ts('Order by Column'), $options);
1328         $this->addElement('select', "order_bys[{$i}][order]", ts('Order by Order'), array(
1329           'ASC' => 'Ascending',
1330           'DESC' => 'Descending',
1331         ));
1332         $this->addElement('checkbox', "order_bys[{$i}][section]", ts('Order by Section'), FALSE, array('id' => "order_by_section_$i"));
1333         $this->addElement('checkbox', "order_bys[{$i}][pageBreak]", ts('Page Break'), FALSE, array('id' => "order_by_pagebreak_$i"));
1334       }
1335     }
1336   }
1337
1338   /**
1339    * This adds the tab referred to as Title and Format, rendered through Instance.tpl.
1340    *
1341    * @todo call this tab into the report template in the same way as OrderBy etc, ie
1342    * by adding a description of the tab to $this->tabs, causing the tab to be added in
1343    * Criteria.tpl.
1344    */
1345   public function buildInstanceAndButtons() {
1346     CRM_Report_Form_Instance::buildForm($this);
1347
1348     $label = $this->_id ? ts('Update Report') : ts('Create Report');
1349
1350     $this->addElement('submit', $this->_instanceButtonName, $label);
1351     $this->addElement('submit', $this->_printButtonName, ts('Print Report'));
1352     $this->addElement('submit', $this->_pdfButtonName, ts('PDF'));
1353
1354     if ($this->_id) {
1355       $this->addElement('submit', $this->_createNewButtonName,
1356         ts('Save a Copy') . '...');
1357     }
1358     $this->assign('instanceForm', $this->_instanceForm);
1359
1360     $label = $this->_id ? ts('Print Report') : ts('Print Preview');
1361     $this->addElement('submit', $this->_printButtonName, $label);
1362
1363     $label = $this->_id ? ts('PDF') : ts('Preview PDF');
1364     $this->addElement('submit', $this->_pdfButtonName, $label);
1365
1366     $label = $this->_id ? ts('Export to CSV') : ts('Preview CSV');
1367
1368     if ($this->_csvSupported) {
1369       $this->addElement('submit', $this->_csvButtonName, $label);
1370     }
1371
1372     if (CRM_Core_Permission::check('administer Reports') &&
1373       $this->_add2groupSupported
1374     ) {
1375       $this->addElement('select', 'groups', ts('Group'),
1376         array('' => ts('Add Contacts to Group')) +
1377         CRM_Core_PseudoConstant::nestedGroup(),
1378         array('class' => 'crm-select2 crm-action-menu action-icon-plus huge')
1379       );
1380       $this->assign('group', TRUE);
1381     }
1382
1383     $this->addElement('submit', $this->_groupButtonName, '', array('style' => 'display: none;'));
1384
1385     $this->addChartOptions();
1386     $this->addButtons(array(
1387         array(
1388           'type' => 'submit',
1389           'name' => ts('Preview Report'),
1390           'isDefault' => TRUE,
1391         ),
1392       )
1393     );
1394   }
1395
1396   /**
1397    * Main build form function.
1398    */
1399   public function buildQuickForm() {
1400     $this->addColumns();
1401
1402     $this->addFilters();
1403
1404     $this->addOptions();
1405
1406     $this->addGroupBys();
1407
1408     $this->addOrderBys();
1409
1410     $this->buildInstanceAndButtons();
1411
1412     // Add form rule for report.
1413     if (is_callable(array(
1414       $this,
1415       'formRule',
1416     ))) {
1417       $this->addFormRule(array(get_class($this), 'formRule'), $this);
1418     }
1419     $this->assignTabs();
1420   }
1421
1422   /**
1423    * A form rule function for custom data.
1424    *
1425    * The rule ensures that fields selected in group_by if any) should only be the ones
1426    * present in display/select fields criteria;
1427    * note: works if and only if any custom field selected in group_by.
1428    *
1429    * @param array $fields
1430    * @param array $ignoreFields
1431    *
1432    * @return array
1433    */
1434   public function customDataFormRule($fields, $ignoreFields = array()) {
1435     $errors = array();
1436     if (!empty($this->_customGroupExtends) && $this->_customGroupGroupBy &&
1437       !empty($fields['group_bys'])
1438     ) {
1439       foreach ($this->_columns as $tableName => $table) {
1440         if ((substr($tableName, 0, 13) == 'civicrm_value' ||
1441             substr($tableName, 0, 12) == 'custom_value') &&
1442           !empty($this->_columns[$tableName]['fields'])
1443         ) {
1444           foreach ($this->_columns[$tableName]['fields'] as $fieldName => $field) {
1445             if (array_key_exists($fieldName, $fields['group_bys']) &&
1446               !array_key_exists($fieldName, $fields['fields'])
1447             ) {
1448               $errors['fields'] = "Please make sure fields selected in 'Group by Columns' section are also selected in 'Display Columns' section.";
1449             }
1450             elseif (array_key_exists($fieldName, $fields['group_bys'])) {
1451               foreach ($fields['fields'] as $fld => $val) {
1452                 if (!array_key_exists($fld, $fields['group_bys']) &&
1453                   !in_array($fld, $ignoreFields)
1454                 ) {
1455                   $errors['fields'] = "Please ensure that fields selected in 'Display Columns' are also selected in 'Group by Columns' section.";
1456                 }
1457               }
1458             }
1459           }
1460         }
1461       }
1462     }
1463     return $errors;
1464   }
1465
1466   /**
1467    * Get operators to display on form.
1468    *
1469    * Note: $fieldName param allows inheriting class to build operationPairs specific to a field.
1470    *
1471    * @param string $type
1472    * @param string $fieldName
1473    *
1474    * @return array
1475    */
1476   public function getOperationPair($type = "string", $fieldName = NULL) {
1477     // FIXME: At some point we should move these key-val pairs
1478     // to option_group and option_value table.
1479     switch ($type) {
1480       case CRM_Report_Form::OP_INT:
1481       case CRM_Report_Form::OP_FLOAT:
1482
1483         $result = array(
1484           'lte' => ts('Is less than or equal to'),
1485           'gte' => ts('Is greater than or equal to'),
1486           'bw' => ts('Is between'),
1487           'eq' => ts('Is equal to'),
1488           'lt' => ts('Is less than'),
1489           'gt' => ts('Is greater than'),
1490           'neq' => ts('Is not equal to'),
1491           'nbw' => ts('Is not between'),
1492           'nll' => ts('Is empty (Null)'),
1493           'nnll' => ts('Is not empty (Null)'),
1494         );
1495         return $result;
1496
1497       case CRM_Report_Form::OP_SELECT:
1498         $result = array(
1499           'eq' => ts('Is equal to'),
1500         );
1501         return $result;
1502
1503       case CRM_Report_Form::OP_MONTH:
1504       case CRM_Report_Form::OP_MULTISELECT:
1505       case CRM_Report_Form::OP_ENTITYREF:
1506
1507         $result = array(
1508           'in' => ts('Is one of'),
1509           'notin' => ts('Is not one of'),
1510         );
1511         return $result;
1512
1513       case CRM_Report_Form::OP_DATE:
1514
1515         $result = array(
1516           'nll' => ts('Is empty (Null)'),
1517           'nnll' => ts('Is not empty (Null)'),
1518         );
1519         return $result;
1520
1521       case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1522         // use this operator for the values, concatenated with separator. For e.g if
1523         // multiple options for a column is stored as ^A{val1}^A{val2}^A
1524         $result = array(
1525           'mhas' => ts('Is one of'),
1526           'mnot' => ts('Is not one of'),
1527         );
1528         return $result;
1529
1530       default:
1531         // type is string
1532         $result = array(
1533           'has' => ts('Contains'),
1534           'sw' => ts('Starts with'),
1535           'ew' => ts('Ends with'),
1536           'nhas' => ts('Does not contain'),
1537           'eq' => ts('Is equal to'),
1538           'neq' => ts('Is not equal to'),
1539           'nll' => ts('Is empty (Null)'),
1540           'nnll' => ts('Is not empty (Null)'),
1541         );
1542         return $result;
1543     }
1544   }
1545
1546   /**
1547    * Build the tag filter field to display on the filters tab.
1548    */
1549   public function buildTagFilter() {
1550     $contactTags = CRM_Core_BAO_Tag::getTags($this->_tagFilterTable);
1551     if (!empty($contactTags)) {
1552       $this->_columns['civicrm_tag'] = array(
1553         'dao' => 'CRM_Core_DAO_Tag',
1554         'filters' => array(
1555           'tagid' => array(
1556             'name' => 'tag_id',
1557             'title' => ts('Tag'),
1558             'tag' => TRUE,
1559             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1560             'options' => $contactTags,
1561           ),
1562         ),
1563       );
1564     }
1565   }
1566
1567   /**
1568    * Adds group filters to _columns (called from _Construct).
1569    */
1570   public function buildGroupFilter() {
1571     $this->_columns['civicrm_group']['filters'] = array(
1572       'gid' => array(
1573         'name' => 'group_id',
1574         'title' => ts('Group'),
1575         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1576         'group' => TRUE,
1577         'options' => CRM_Core_PseudoConstant::nestedGroup(),
1578       ),
1579     );
1580     if (empty($this->_columns['civicrm_group']['dao'])) {
1581       $this->_columns['civicrm_group']['dao'] = 'CRM_Contact_DAO_GroupContact';
1582     }
1583     if (empty($this->_columns['civicrm_group']['alias'])) {
1584       $this->_columns['civicrm_group']['alias'] = 'cgroup';
1585     }
1586   }
1587
1588   /**
1589    * Get SQL operator from form text version.
1590    *
1591    * @param string $operator
1592    *
1593    * @return string
1594    */
1595   public function getSQLOperator($operator = "like") {
1596     switch ($operator) {
1597       case 'eq':
1598         return '=';
1599
1600       case 'lt':
1601         return '<';
1602
1603       case 'lte':
1604         return '<=';
1605
1606       case 'gt':
1607         return '>';
1608
1609       case 'gte':
1610         return '>=';
1611
1612       case 'ne':
1613       case 'neq':
1614         return '!=';
1615
1616       case 'nhas':
1617         return 'NOT LIKE';
1618
1619       case 'in':
1620         return 'IN';
1621
1622       case 'notin':
1623         return 'NOT IN';
1624
1625       case 'nll':
1626         return 'IS NULL';
1627
1628       case 'nnll':
1629         return 'IS NOT NULL';
1630
1631       default:
1632         // type is string
1633         return 'LIKE';
1634     }
1635   }
1636
1637   /**
1638    * Generate where clause.
1639    *
1640    * This can be overridden in reports for special treatment of a field
1641    *
1642    * @param array $field Field specifications
1643    * @param string $op Query operator (not an exact match to sql)
1644    * @param mixed $value
1645    * @param float $min
1646    * @param float $max
1647    *
1648    * @return null|string
1649    */
1650   public function whereClause(&$field, $op, $value, $min, $max) {
1651
1652     $type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
1653     $clause = NULL;
1654
1655     switch ($op) {
1656       case 'bw':
1657       case 'nbw':
1658         if (($min !== NULL && strlen($min) > 0) ||
1659           ($max !== NULL && strlen($max) > 0)
1660         ) {
1661           $min = CRM_Utils_Type::escape($min, $type);
1662           $max = CRM_Utils_Type::escape($max, $type);
1663           $clauses = array();
1664           if ($min) {
1665             if ($op == 'bw') {
1666               $clauses[] = "( {$field['dbAlias']} >= $min )";
1667             }
1668             else {
1669               $clauses[] = "( {$field['dbAlias']} < $min )";
1670             }
1671           }
1672           if ($max) {
1673             if ($op == 'bw') {
1674               $clauses[] = "( {$field['dbAlias']} <= $max )";
1675             }
1676             else {
1677               $clauses[] = "( {$field['dbAlias']} > $max )";
1678             }
1679           }
1680
1681           if (!empty($clauses)) {
1682             if ($op == 'bw') {
1683               $clause = implode(' AND ', $clauses);
1684             }
1685             else {
1686               $clause = implode(' OR ', $clauses);
1687             }
1688           }
1689         }
1690         break;
1691
1692       case 'has':
1693       case 'nhas':
1694         if ($value !== NULL && strlen($value) > 0) {
1695           $value = CRM_Utils_Type::escape($value, $type);
1696           if (strpos($value, '%') === FALSE) {
1697             $value = "'%{$value}%'";
1698           }
1699           else {
1700             $value = "'{$value}'";
1701           }
1702           $sqlOP = $this->getSQLOperator($op);
1703           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1704         }
1705         break;
1706
1707       case 'in':
1708       case 'notin':
1709         if (is_string($value) && strlen($value)) {
1710           $value = explode(',', $value);
1711         }
1712         if ($value !== NULL && is_array($value) && count($value) > 0) {
1713           $sqlOP = $this->getSQLOperator($op);
1714           if (CRM_Utils_Array::value('type', $field) ==
1715             CRM_Utils_Type::T_STRING
1716           ) {
1717             //cycle through selections and escape values
1718             foreach ($value as $key => $selection) {
1719               $value[$key] = CRM_Utils_Type::escape($selection, $type);
1720             }
1721             $clause
1722               = "( {$field['dbAlias']} $sqlOP ( '" . implode("' , '", $value) .
1723               "') )";
1724           }
1725           else {
1726             // for numerical values
1727             $clause = "{$field['dbAlias']} $sqlOP (" . implode(', ', $value) .
1728               ")";
1729           }
1730           if ($op == 'notin') {
1731             $clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
1732           }
1733           else {
1734             $clause = "( " . $clause . " )";
1735           }
1736         }
1737         break;
1738
1739       case 'mhas':
1740         // mhas == multiple has
1741         if ($value !== NULL && count($value) > 0) {
1742           $sqlOP = $this->getSQLOperator($op);
1743           $clause = "( {$field['dbAlias']} REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', $value) . "[[:>:]]' )";
1744         }
1745         break;
1746
1747       case 'mnot':
1748         // mnot == multiple is not one of
1749         if ($value !== NULL && count($value) > 0) {
1750           $sqlOP = $this->getSQLOperator($op);
1751           $clause = "( {$field['dbAlias']} NOT REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', $value) . "[[:>:]]' )";
1752         }
1753         break;
1754
1755       case 'sw':
1756       case 'ew':
1757         if ($value !== NULL && strlen($value) > 0) {
1758           $value = CRM_Utils_Type::escape($value, $type);
1759           if (strpos($value, '%') === FALSE) {
1760             if ($op == 'sw') {
1761               $value = "'{$value}%'";
1762             }
1763             else {
1764               $value = "'%{$value}'";
1765             }
1766           }
1767           else {
1768             $value = "'{$value}'";
1769           }
1770           $sqlOP = $this->getSQLOperator($op);
1771           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1772         }
1773         break;
1774
1775       case 'nll':
1776       case 'nnll':
1777         $sqlOP = $this->getSQLOperator($op);
1778         $clause = "( {$field['dbAlias']} $sqlOP )";
1779         break;
1780
1781       default:
1782         if ($value !== NULL && strlen($value) > 0) {
1783           if (isset($field['clause'])) {
1784             // FIXME: we not doing escape here. Better solution is to use two
1785             // different types - data-type and filter-type
1786             $clause = $field['clause'];
1787           }
1788           else {
1789             $value = CRM_Utils_Type::escape($value, $type);
1790             $sqlOP = $this->getSQLOperator($op);
1791             if ($field['type'] == CRM_Utils_Type::T_STRING) {
1792               $value = "'{$value}'";
1793             }
1794             $clause = "( {$field['dbAlias']} $sqlOP $value )";
1795           }
1796         }
1797         break;
1798     }
1799
1800     if (!empty($field['group']) && $clause) {
1801       $clause = $this->whereGroupClause($field, $value, $op);
1802     }
1803     elseif (!empty($field['tag']) && $clause) {
1804       // not using left join in query because if any contact
1805       // belongs to more than one tag, results duplicate
1806       // entries.
1807       $clause = $this->whereTagClause($field, $value, $op);
1808     }
1809     elseif (!empty($field['membership_org']) && $clause) {
1810       $clause = $this->whereMembershipOrgClause($value, $op);
1811     }
1812     elseif (!empty($field['membership_type']) && $clause) {
1813       $clause = $this->whereMembershipTypeClause($value, $op);
1814     }
1815     return $clause;
1816   }
1817
1818   /**
1819    * Get SQL where clause for a date field.
1820    *
1821    * @param string $fieldName
1822    * @param string $relative
1823    * @param string $from
1824    * @param string $to
1825    * @param string $type
1826    * @param string $fromTime
1827    * @param string $toTime
1828    *
1829    * @return null|string
1830    */
1831   public function dateClause(
1832     $fieldName,
1833     $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
1834   ) {
1835     $clauses = array();
1836     if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) {
1837       $sqlOP = $this->getSQLOperator($relative);
1838       return "( {$fieldName} {$sqlOP} )";
1839     }
1840
1841     list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime);
1842
1843     if ($from) {
1844       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
1845       $clauses[] = "( {$fieldName} >= $from )";
1846     }
1847
1848     if ($to) {
1849       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
1850       $clauses[] = "( {$fieldName} <= {$to} )";
1851     }
1852
1853     if (!empty($clauses)) {
1854       return implode(' AND ', $clauses);
1855     }
1856
1857     return NULL;
1858   }
1859
1860   /**
1861    * Possibly unused function.
1862    *
1863    * @todo - could not find any instances where this is called
1864    *
1865    * @param bool $relative
1866    * @param string $from
1867    * @param string $to
1868    *
1869    * @return string|NULL
1870    */
1871   public function dateDisplay($relative, $from, $to) {
1872     list($from, $to) = $this->getFromTo($relative, $from, $to);
1873
1874     if ($from) {
1875       $clauses[] = CRM_Utils_Date::customFormat($from, NULL, array('m', 'M'));
1876     }
1877     else {
1878       $clauses[] = 'Past';
1879     }
1880
1881     if ($to) {
1882       $clauses[] = CRM_Utils_Date::customFormat($to, NULL, array('m', 'M'));
1883     }
1884     else {
1885       $clauses[] = 'Today';
1886     }
1887
1888     if (!empty($clauses)) {
1889       return implode(' - ', $clauses);
1890     }
1891
1892     return NULL;
1893   }
1894
1895   /**
1896    * Get values for from and to for date ranges.
1897    *
1898    * @param bool $relative
1899    * @param string $from
1900    * @param string $to
1901    * @param string $fromTime
1902    * @param string $toTime
1903    *
1904    * @return array
1905    */
1906   public function getFromTo($relative, $from, $to, $fromTime = NULL, $toTime = NULL) {
1907     if (empty($toTime)) {
1908       $toTime = '235959';
1909     }
1910     //FIX ME not working for relative
1911     if ($relative) {
1912       list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
1913       $dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
1914       $from = substr($dateRange['from'], 0, 8);
1915       //Take only Date Part, Sometime Time part is also present in 'to'
1916       $to = substr($dateRange['to'], 0, 8);
1917     }
1918     $from = CRM_Utils_Date::processDate($from, $fromTime);
1919     $to = CRM_Utils_Date::processDate($to, $toTime);
1920     return array($from, $to);
1921   }
1922
1923   /**
1924    * Alter display of rows.
1925    *
1926    * Iterate through the rows retrieved via SQL and make changes for display purposes,
1927    * such as rendering contacts as links.
1928    *
1929    * @param array $rows
1930    *   Rows generated by SQL, with an array for each row.
1931    */
1932   public function alterDisplay(&$rows) {
1933   }
1934
1935   /**
1936    * Alter the way in which custom data fields are displayed.
1937    *
1938    * @param array $rows
1939    */
1940   public function alterCustomDataDisplay(&$rows) {
1941     // custom code to alter rows having custom values
1942     if (empty($this->_customGroupExtends)) {
1943       return;
1944     }
1945
1946     $customFieldIds = array();
1947     foreach ($this->_params['fields'] as $fieldAlias => $value) {
1948       if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
1949         $customFieldIds[$fieldAlias] = $fieldId;
1950       }
1951     }
1952     if (empty($customFieldIds)) {
1953       return;
1954     }
1955
1956     $customFields = $fieldValueMap = array();
1957     $customFieldCols = array(
1958       'column_name',
1959       'data_type',
1960       'html_type',
1961       'option_group_id',
1962       'id',
1963     );
1964
1965     // skip for type date and ContactReference since date format is already handled
1966     $query = "
1967 SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label
1968 FROM  civicrm_custom_field cf
1969 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
1970 LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id
1971 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
1972       cg.is_active = 1 AND
1973       cf.is_active = 1 AND
1974       cf.is_searchable = 1 AND
1975       cf.data_type   NOT IN ('ContactReference', 'Date') AND
1976       cf.id IN (" . implode(",", $customFieldIds) . ")";
1977
1978     $dao = CRM_Core_DAO::executeQuery($query);
1979     while ($dao->fetch()) {
1980       foreach ($customFieldCols as $key) {
1981         $customFields[$dao->table_name . '_custom_' .
1982         $dao->id][$key] = $dao->$key;
1983       }
1984       if ($dao->option_group_id) {
1985         $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label;
1986       }
1987     }
1988     $dao->free();
1989
1990     $entryFound = FALSE;
1991     foreach ($rows as $rowNum => $row) {
1992       foreach ($row as $tableCol => $val) {
1993         if (array_key_exists($tableCol, $customFields)) {
1994           $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap);
1995           $entryFound = TRUE;
1996         }
1997       }
1998
1999       // skip looking further in rows, if first row itself doesn't
2000       // have the column we need
2001       if (!$entryFound) {
2002         break;
2003       }
2004     }
2005   }
2006
2007   /**
2008    * Format custom values.
2009    *
2010    * @param mixed $value
2011    * @param array $customField
2012    * @param array $fieldValueMap
2013    *
2014    * @return float|string|void
2015    */
2016   public function formatCustomValues($value, $customField, $fieldValueMap) {
2017     if (CRM_Utils_System::isNull($value)) {
2018       return NULL;
2019     }
2020
2021     $htmlType = $customField['html_type'];
2022
2023     switch ($customField['data_type']) {
2024       case 'Boolean':
2025         if ($value == '1') {
2026           $retValue = ts('Yes');
2027         }
2028         else {
2029           $retValue = ts('No');
2030         }
2031         break;
2032
2033       case 'Link':
2034         $retValue = CRM_Utils_System::formatWikiURL($value);
2035         break;
2036
2037       case 'File':
2038         $retValue = $value;
2039         break;
2040
2041       case 'Memo':
2042         $retValue = $value;
2043         break;
2044
2045       case 'Float':
2046         if ($htmlType == 'Text') {
2047           $retValue = (float) $value;
2048           break;
2049         }
2050       case 'Money':
2051         if ($htmlType == 'Text') {
2052           $retValue = CRM_Utils_Money::format($value, NULL, '%a');
2053           break;
2054         }
2055       case 'String':
2056       case 'Int':
2057         if (in_array($htmlType, array(
2058           'Text',
2059           'TextArea',
2060         ))) {
2061           $retValue = $value;
2062           break;
2063         }
2064       case 'StateProvince':
2065       case 'Country':
2066
2067         switch ($htmlType) {
2068           case 'Multi-Select Country':
2069             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2070             $customData = array();
2071             foreach ($value as $val) {
2072               if ($val) {
2073                 $customData[] = CRM_Core_PseudoConstant::country($val, FALSE);
2074               }
2075             }
2076             $retValue = implode(', ', $customData);
2077             break;
2078
2079           case 'Select Country':
2080             $retValue = CRM_Core_PseudoConstant::country($value, FALSE);
2081             break;
2082
2083           case 'Select State/Province':
2084             $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
2085             break;
2086
2087           case 'Multi-Select State/Province':
2088             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2089             $customData = array();
2090             foreach ($value as $val) {
2091               if ($val) {
2092                 $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE);
2093               }
2094             }
2095             $retValue = implode(', ', $customData);
2096             break;
2097
2098           case 'Select':
2099           case 'Radio':
2100           case 'Autocomplete-Select':
2101             $retValue = $fieldValueMap[$customField['option_group_id']][$value];
2102             break;
2103
2104           case 'CheckBox':
2105           case 'AdvMulti-Select':
2106           case 'Multi-Select':
2107             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2108             $customData = array();
2109             foreach ($value as $val) {
2110               if ($val) {
2111                 $customData[] = $fieldValueMap[$customField['option_group_id']][$val];
2112               }
2113             }
2114             $retValue = implode(', ', $customData);
2115             break;
2116
2117           default:
2118             $retValue = $value;
2119         }
2120         break;
2121
2122       default:
2123         $retValue = $value;
2124     }
2125
2126     return $retValue;
2127   }
2128
2129   /**
2130    * Remove duplicate rows.
2131    *
2132    * @param array $rows
2133    */
2134   public function removeDuplicates(&$rows) {
2135     if (empty($this->_noRepeats)) {
2136       return;
2137     }
2138     $checkList = array();
2139
2140     foreach ($rows as $key => $list) {
2141       foreach ($list as $colName => $colVal) {
2142         if (array_key_exists($colName, $checkList) &&
2143           $checkList[$colName] == $colVal
2144         ) {
2145           $rows[$key][$colName] = "";
2146         }
2147         if (in_array($colName, $this->_noRepeats)) {
2148           $checkList[$colName] = $colVal;
2149         }
2150       }
2151     }
2152   }
2153
2154   /**
2155    * Fix subtotal display.
2156    *
2157    * @param array $row
2158    * @param array $fields
2159    * @param bool $subtotal
2160    */
2161   public function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
2162     foreach ($row as $colName => $colVal) {
2163       if (in_array($colName, $fields)) {
2164       }
2165       elseif (isset($this->_columnHeaders[$colName])) {
2166         if ($subtotal) {
2167           $row[$colName] = "Subtotal";
2168           $subtotal = FALSE;
2169         }
2170         else {
2171           unset($row[$colName]);
2172         }
2173       }
2174     }
2175   }
2176
2177   /**
2178    * Calculate grant total.
2179    *
2180    * @param array $rows
2181    *
2182    * @return bool
2183    */
2184   public function grandTotal(&$rows) {
2185     if (!$this->_rollup || ($this->_rollup == '') ||
2186       ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
2187     ) {
2188       return FALSE;
2189     }
2190     $lastRow = array_pop($rows);
2191
2192     foreach ($this->_columnHeaders as $fld => $val) {
2193       if (!in_array($fld, $this->_statFields)) {
2194         if (!$this->_grandFlag) {
2195           $lastRow[$fld] = "Grand Total";
2196           $this->_grandFlag = TRUE;
2197         }
2198         else {
2199           $lastRow[$fld] = "";
2200         }
2201       }
2202     }
2203
2204     $this->assign('grandStat', $lastRow);
2205     return TRUE;
2206   }
2207
2208   /**
2209    * Format display output.
2210    *
2211    * @param array $rows
2212    * @param bool $pager
2213    */
2214   public function formatDisplay(&$rows, $pager = TRUE) {
2215     // set pager based on if any limit was applied in the query.
2216     if ($pager) {
2217       $this->setPager();
2218     }
2219
2220     // allow building charts if any
2221     if (!empty($this->_params['charts']) && !empty($rows)) {
2222       $this->buildChart($rows);
2223       $this->assign('chartEnabled', TRUE);
2224       $this->_chartId = "{$this->_params['charts']}_" .
2225         ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' .
2226         session_id();
2227       $this->assign('chartId', $this->_chartId);
2228     }
2229
2230     // unset columns not to be displayed.
2231     foreach ($this->_columnHeaders as $key => $value) {
2232       if (!empty($value['no_display'])) {
2233         unset($this->_columnHeaders[$key]);
2234       }
2235     }
2236
2237     // unset columns not to be displayed.
2238     if (!empty($rows)) {
2239       foreach ($this->_noDisplay as $noDisplayField) {
2240         foreach ($rows as $rowNum => $row) {
2241           unset($this->_columnHeaders[$noDisplayField]);
2242         }
2243       }
2244     }
2245
2246     // build array of section totals
2247     $this->sectionTotals();
2248
2249     // process grand-total row
2250     $this->grandTotal($rows);
2251
2252     // use this method for formatting rows for display purpose.
2253     $this->alterDisplay($rows);
2254     CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
2255
2256     // use this method for formatting custom rows for display purpose.
2257     $this->alterCustomDataDisplay($rows);
2258   }
2259
2260   /**
2261    * Build chart.
2262    *
2263    * @param array $rows
2264    */
2265   public function buildChart(&$rows) {
2266     // override this method for building charts.
2267   }
2268
2269   // select() method below has been added recently (v3.3), and many of the report templates might
2270   // still be having their own select() method. We should fix them as and when encountered and move
2271   // towards generalizing the select() method below.
2272
2273   /**
2274    * Generate the SELECT clause and set class variable $_select.
2275    */
2276   public function select() {
2277     $select = $this->_selectAliases = array();
2278
2279     foreach ($this->_columns as $tableName => $table) {
2280       if (array_key_exists('fields', $table)) {
2281         foreach ($table['fields'] as $fieldName => $field) {
2282           if ($tableName == 'civicrm_address') {
2283             $this->_addressField = TRUE;
2284           }
2285           if ($tableName == 'civicrm_email') {
2286             $this->_emailField = TRUE;
2287           }
2288           if ($tableName == 'civicrm_phone') {
2289             $this->_phoneField = TRUE;
2290           }
2291
2292           if (!empty($field['required']) ||
2293             !empty($this->_params['fields'][$fieldName])
2294           ) {
2295
2296             // 1. In many cases we want select clause to be built in slightly different way
2297             // for a particular field of a particular type.
2298             // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2299             // as needed.
2300             $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
2301             if ($selectClause) {
2302               $select[] = $selectClause;
2303               continue;
2304             }
2305
2306             // include statistics columns only if set
2307             if (!empty($field['statistics'])) {
2308               foreach ($field['statistics'] as $stat => $label) {
2309                 $alias = "{$tableName}_{$fieldName}_{$stat}";
2310                 switch (strtolower($stat)) {
2311                   case 'max':
2312                   case 'sum':
2313                     $select[] = "$stat({$field['dbAlias']}) as $alias";
2314                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2315                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2316                     $this->_statFields[$label] = $alias;
2317                     $this->_selectAliases[] = $alias;
2318                     break;
2319
2320                   case 'count':
2321                     $select[] = "COUNT({$field['dbAlias']}) as $alias";
2322                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2323                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2324                     $this->_statFields[$label] = $alias;
2325                     $this->_selectAliases[] = $alias;
2326                     break;
2327
2328                   case 'count_distinct':
2329                     $select[] = "COUNT(DISTINCT {$field['dbAlias']}) as $alias";
2330                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2331                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2332                     $this->_statFields[$label] = $alias;
2333                     $this->_selectAliases[] = $alias;
2334                     break;
2335
2336                   case 'avg':
2337                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
2338                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2339                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2340                     $this->_statFields[$label] = $alias;
2341                     $this->_selectAliases[] = $alias;
2342                     break;
2343                 }
2344               }
2345             }
2346             else {
2347               $alias = "{$tableName}_{$fieldName}";
2348               $select[] = "{$field['dbAlias']} as $alias";
2349               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
2350               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
2351               $this->_selectAliases[] = $alias;
2352             }
2353           }
2354         }
2355       }
2356
2357       // select for group bys
2358       if (array_key_exists('group_bys', $table)) {
2359         foreach ($table['group_bys'] as $fieldName => $field) {
2360
2361           if ($tableName == 'civicrm_address') {
2362             $this->_addressField = TRUE;
2363           }
2364           if ($tableName == 'civicrm_email') {
2365             $this->_emailField = TRUE;
2366           }
2367           if ($tableName == 'civicrm_phone') {
2368             $this->_phoneField = TRUE;
2369           }
2370           // 1. In many cases we want select clause to be built in slightly different way
2371           // for a particular field of a particular type.
2372           // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2373           // as needed.
2374           $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
2375           if ($selectClause) {
2376             $select[] = $selectClause;
2377             continue;
2378           }
2379
2380           if (!empty($this->_params['group_bys']) &&
2381             !empty($this->_params['group_bys'][$fieldName]) &&
2382             !empty($this->_params['group_bys_freq'])
2383           ) {
2384             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
2385               case 'YEARWEEK':
2386                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
2387                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2388                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2389                 $field['title'] = 'Week';
2390                 break;
2391
2392               case 'YEAR':
2393                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
2394                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2395                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2396                 $field['title'] = 'Year';
2397                 break;
2398
2399               case 'MONTH':
2400                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
2401                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2402                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2403                 $field['title'] = 'Month';
2404                 break;
2405
2406               case 'QUARTER':
2407                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
2408                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2409                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2410                 $field['title'] = 'Quarter';
2411                 break;
2412             }
2413             // for graphs and charts -
2414             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
2415               $this->_interval = $field['title'];
2416               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title']
2417                 = $field['title'] . ' Beginning';
2418               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
2419               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
2420
2421               // just to make sure these values are transferred to rows.
2422               // since we 'll need them for calculation purpose,
2423               // e.g making subtotals look nicer or graphs
2424               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
2425               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
2426             }
2427           }
2428         }
2429       }
2430     }
2431
2432     $this->_selectClauses = $select;
2433     $this->_select = "SELECT " . implode(', ', $select) . " ";
2434   }
2435
2436   /**
2437    * Build select clause for a single field.
2438    *
2439    * @param string $tableName
2440    * @param string $tableKey
2441    * @param string $fieldName
2442    * @param string $field
2443    *
2444    * @return bool
2445    */
2446   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
2447     return FALSE;
2448   }
2449
2450   /**
2451    * Build where clause.
2452    */
2453   public function where() {
2454     $this->storeWhereHavingClauseArray();
2455
2456     if (empty($this->_whereClauses)) {
2457       $this->_where = "WHERE ( 1 ) ";
2458       $this->_having = "";
2459     }
2460     else {
2461       $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
2462     }
2463
2464     if ($this->_aclWhere) {
2465       $this->_where .= " AND {$this->_aclWhere} ";
2466     }
2467
2468     if (!empty($this->_havingClauses)) {
2469       // use this clause to construct group by clause.
2470       $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
2471     }
2472   }
2473
2474   /**
2475    * Store Where clauses into an array.
2476    *
2477    * Breaking out this step makes over-riding more flexible as the clauses can be used in constructing a
2478    * temp table that may not be part of the final where clause or added
2479    * in other functions
2480    */
2481   public function storeWhereHavingClauseArray() {
2482     foreach ($this->_columns as $tableName => $table) {
2483       if (array_key_exists('filters', $table)) {
2484         foreach ($table['filters'] as $fieldName => $field) {
2485           // respect pseudofield to filter spec so fields can be marked as
2486           // not to be handled here
2487           if (!empty($field['pseudofield'])) {
2488             continue;
2489           }
2490           $clause = NULL;
2491           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
2492             if (CRM_Utils_Array::value('operatorType', $field) ==
2493               CRM_Report_Form::OP_MONTH
2494             ) {
2495               $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2496               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2497               if (is_array($value) && !empty($value)) {
2498                 $clause
2499                   = "(month({$field['dbAlias']}) $op (" . implode(', ', $value) .
2500                   '))';
2501               }
2502             }
2503             else {
2504               $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
2505               $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
2506               $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
2507               $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
2508               $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
2509               $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
2510             }
2511           }
2512           else {
2513             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2514             if ($op) {
2515               $clause = $this->whereClause($field,
2516                 $op,
2517                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
2518                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
2519                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
2520               );
2521             }
2522           }
2523
2524           if (!empty($clause)) {
2525             if (!empty($field['having'])) {
2526               $this->_havingClauses[] = $clause;
2527             }
2528             else {
2529               $this->_whereClauses[] = $clause;
2530             }
2531           }
2532         }
2533       }
2534     }
2535
2536   }
2537
2538   /**
2539    * Set output mode.
2540    */
2541   public function processReportMode() {
2542     $buttonName = $this->controller->getButtonName();
2543
2544     $output = CRM_Utils_Request::retrieve(
2545       'output',
2546       'String',
2547       CRM_Core_DAO::$_nullObject
2548     );
2549
2550     $this->_sendmail
2551       = CRM_Utils_Request::retrieve(
2552         'sendmail',
2553         'Boolean',
2554         CRM_Core_DAO::$_nullObject
2555       );
2556
2557     $this->_absoluteUrl = FALSE;
2558     $printOnly = FALSE;
2559     $this->assign('printOnly', FALSE);
2560
2561     if ($this->_printButtonName == $buttonName || $output == 'print' ||
2562       ($this->_sendmail && !$output)
2563     ) {
2564       $this->assign('printOnly', TRUE);
2565       $printOnly = TRUE;
2566       $this->assign('outputMode', 'print');
2567       $this->_outputMode = 'print';
2568       if ($this->_sendmail) {
2569         $this->_absoluteUrl = TRUE;
2570       }
2571     }
2572     elseif ($this->_pdfButtonName == $buttonName || $output == 'pdf') {
2573       $this->assign('printOnly', TRUE);
2574       $printOnly = TRUE;
2575       $this->assign('outputMode', 'pdf');
2576       $this->_outputMode = 'pdf';
2577       $this->_absoluteUrl = TRUE;
2578     }
2579     elseif ($this->_csvButtonName == $buttonName || $output == 'csv') {
2580       $this->assign('printOnly', TRUE);
2581       $printOnly = TRUE;
2582       $this->assign('outputMode', 'csv');
2583       $this->_outputMode = 'csv';
2584       $this->_absoluteUrl = TRUE;
2585     }
2586     elseif ($this->_groupButtonName == $buttonName || $output == 'group') {
2587       $this->assign('outputMode', 'group');
2588       $this->_outputMode = 'group';
2589     }
2590     elseif ($output == 'create_report' && $this->_criteriaForm) {
2591       $this->assign('outputMode', 'create_report');
2592       $this->_outputMode = 'create_report';
2593     }
2594     else {
2595       $this->assign('outputMode', 'html');
2596       $this->_outputMode = 'html';
2597     }
2598
2599     // Get today's date to include in printed reports
2600     if ($printOnly) {
2601       $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
2602       $this->assign('reportDate', $reportDate);
2603     }
2604   }
2605
2606   /**
2607    * Post Processing function for Form.
2608    *
2609    * postProcessCommon should be used to set other variables from input as the api accesses that function.
2610    */
2611   public function beginPostProcess() {
2612     $this->setParams($this->controller->exportValues($this->_name));
2613
2614     if (empty($this->_params) &&
2615       $this->_force
2616     ) {
2617       $this->setParams($this->_formValues);
2618     }
2619
2620     // hack to fix params when submitted from dashboard, CRM-8532
2621     // fields array is missing because form building etc is skipped
2622     // in dashboard mode for report
2623     //@todo - this could be done in the dashboard no we have a setter
2624     if (empty($this->_params['fields']) && !$this->_noFields) {
2625       $this->setParams($this->_formValues);
2626     }
2627
2628     $this->_formValues = $this->_params;
2629     if (CRM_Core_Permission::check('administer Reports') &&
2630       isset($this->_id) &&
2631       ($this->_instanceButtonName ==
2632         $this->controller->getButtonName() . '_save' ||
2633         $this->_chartButtonName == $this->controller->getButtonName()
2634       )
2635     ) {
2636       $this->assign('updateReportButton', TRUE);
2637     }
2638     $this->processReportMode();
2639     $this->beginPostProcessCommon();
2640   }
2641
2642   /**
2643    * BeginPostProcess function run in both report mode and non-report mode (api).
2644    */
2645   public function beginPostProcessCommon() {}
2646
2647   /**
2648    * Build the report query.
2649    *
2650    * @param bool $applyLimit
2651    *
2652    * @return string
2653    */
2654   public function buildQuery($applyLimit = TRUE) {
2655     $this->select();
2656     $this->from();
2657     $this->customDataFrom();
2658     $this->where();
2659     $this->groupBy();
2660     $this->orderBy();
2661
2662     // order_by columns not selected for display need to be included in SELECT
2663     $unselectedSectionColumns = $this->unselectedSectionColumns();
2664     foreach ($unselectedSectionColumns as $alias => $section) {
2665       $this->_select .= ", {$section['dbAlias']} as {$alias}";
2666     }
2667
2668     if ($applyLimit && empty($this->_params['charts'])) {
2669       $this->limit();
2670     }
2671     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2672
2673     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2674     return $sql;
2675   }
2676
2677   /**
2678    * Build group by clause.
2679    */
2680   public function groupBy() {
2681     $groupBys = array();
2682     if (!empty($this->_params['group_bys']) &&
2683       is_array($this->_params['group_bys'])
2684     ) {
2685       foreach ($this->_columns as $tableName => $table) {
2686         if (array_key_exists('group_bys', $table)) {
2687           foreach ($table['group_bys'] as $fieldName => $field) {
2688             if (!empty($this->_params['group_bys'][$fieldName])) {
2689               $groupBys[] = $field['dbAlias'];
2690             }
2691           }
2692         }
2693       }
2694     }
2695
2696     if (!empty($groupBys)) {
2697       $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
2698     }
2699   }
2700
2701   /**
2702    * Build order by clause.
2703    */
2704   public function orderBy() {
2705     $this->_orderBy = "";
2706     $this->_sections = array();
2707     $this->storeOrderByArray();
2708     if (!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP') {
2709       $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2710     }
2711     $this->assign('sections', $this->_sections);
2712   }
2713
2714   /**
2715    * Extract order by fields and store as an array.
2716    *
2717    * In some cases other functions want to know which fields are selected for ordering by
2718    * Separating this into a separate function allows it to be called separately from constructing
2719    * the order by clause
2720    */
2721   public function storeOrderByArray() {
2722     $orderBys = array();
2723
2724     if (!empty($this->_params['order_bys']) &&
2725       is_array($this->_params['order_bys']) &&
2726       !empty($this->_params['order_bys'])
2727     ) {
2728
2729       // Process order_bys in user-specified order
2730       foreach ($this->_params['order_bys'] as $orderBy) {
2731         $orderByField = array();
2732         foreach ($this->_columns as $tableName => $table) {
2733           if (array_key_exists('order_bys', $table)) {
2734             // For DAO columns defined in $this->_columns
2735             $fields = $table['order_bys'];
2736           }
2737           elseif (array_key_exists('extends', $table)) {
2738             // For custom fields referenced in $this->_customGroupExtends
2739             $fields = CRM_Utils_Array::value('fields', $table, array());
2740           }
2741           else {
2742             continue;
2743           }
2744           if (!empty($fields) && is_array($fields)) {
2745             foreach ($fields as $fieldName => $field) {
2746               if ($fieldName == $orderBy['column']) {
2747                 $orderByField = array_merge($field, $orderBy);
2748                 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2749                 break 2;
2750               }
2751             }
2752           }
2753         }
2754
2755         if (!empty($orderByField)) {
2756           $this->_orderByFields[] = $orderByField;
2757           $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2758
2759           // Record any section headers for assignment to the template
2760           if (!empty($orderBy['section'])) {
2761             $orderByField['pageBreak'] = CRM_Utils_Array::value('pageBreak', $orderBy);
2762             $this->_sections[$orderByField['tplField']] = $orderByField;
2763           }
2764         }
2765       }
2766     }
2767
2768     $this->_orderByArray = $orderBys;
2769
2770     $this->assign('sections', $this->_sections);
2771   }
2772
2773   /**
2774    * Determine unselected columns.
2775    *
2776    * @return array
2777    */
2778   public function unselectedSectionColumns() {
2779     $selectColumns = array();
2780     foreach ($this->_columns as $tableName => $table) {
2781       if (array_key_exists('fields', $table)) {
2782         foreach ($table['fields'] as $fieldName => $field) {
2783           if (!empty($field['required']) ||
2784             !empty($this->_params['fields'][$fieldName])
2785           ) {
2786
2787             $selectColumns["{$tableName}_{$fieldName}"] = 1;
2788           }
2789         }
2790       }
2791     }
2792
2793     if (is_array($this->_sections)) {
2794       return array_diff_key($this->_sections, $selectColumns);
2795     }
2796     else {
2797       return array();
2798     }
2799   }
2800
2801   /**
2802    * Build output rows.
2803    *
2804    * @param string $sql
2805    * @param array $rows
2806    */
2807   public function buildRows($sql, &$rows) {
2808     $dao = CRM_Core_DAO::executeQuery($sql);
2809     if (!is_array($rows)) {
2810       $rows = array();
2811     }
2812
2813     // use this method to modify $this->_columnHeaders
2814     $this->modifyColumnHeaders();
2815
2816     $unselectedSectionColumns = $this->unselectedSectionColumns();
2817
2818     while ($dao->fetch()) {
2819       $row = array();
2820       foreach ($this->_columnHeaders as $key => $value) {
2821         if (property_exists($dao, $key)) {
2822           $row[$key] = $dao->$key;
2823         }
2824       }
2825
2826       // section headers not selected for display need to be added to row
2827       foreach ($unselectedSectionColumns as $key => $values) {
2828         if (property_exists($dao, $key)) {
2829           $row[$key] = $dao->$key;
2830         }
2831       }
2832
2833       $rows[] = $row;
2834     }
2835   }
2836
2837   /**
2838    * Calculate section totals.
2839    *
2840    * When "order by" fields are marked as sections, this assigns to the template
2841    * an array of total counts for each section. This data is used by the Smarty
2842    * plugin {sectionTotal}.
2843    */
2844   public function sectionTotals() {
2845
2846     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
2847     if (empty($this->_selectAliases)) {
2848       return;
2849     }
2850
2851     if (!empty($this->_sections)) {
2852       // build the query with no LIMIT clause
2853       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
2854       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
2855
2856       // pull section aliases out of $this->_sections
2857       $sectionAliases = array_keys($this->_sections);
2858
2859       $ifnulls = array();
2860       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
2861         $ifnulls[] = "ifnull($alias, '') as $alias";
2862       }
2863
2864       // Group (un-limited) report by all aliases and get counts. This might
2865       // be done more efficiently when the contents of $sql are known, ie. by
2866       // overriding this method in the report class.
2867
2868       $query = "select " . implode(", ", $ifnulls) .
2869         ", count(*) as ct from ($sql) as subquery group by " .
2870         implode(", ", $sectionAliases);
2871
2872       // initialize array of total counts
2873       $totals = array();
2874       $dao = CRM_Core_DAO::executeQuery($query);
2875       while ($dao->fetch()) {
2876
2877         // let $this->_alterDisplay translate any integer ids to human-readable values.
2878         $rows[0] = $dao->toArray();
2879         $this->alterDisplay($rows);
2880         $row = $rows[0];
2881
2882         // add totals for all permutations of section values
2883         $values = array();
2884         $i = 1;
2885         $aliasCount = count($sectionAliases);
2886         foreach ($sectionAliases as $alias) {
2887           $values[] = $row[$alias];
2888           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
2889           if ($i == $aliasCount) {
2890             // the last alias is the lowest-level section header; use count as-is
2891             $totals[$key] = $dao->ct;
2892           }
2893           else {
2894             // other aliases are higher level; roll count into their total
2895             $totals[$key] += $dao->ct;
2896           }
2897         }
2898       }
2899       $this->assign('sectionTotals', $totals);
2900     }
2901   }
2902
2903   /**
2904    * Modify column headers.
2905    */
2906   public function modifyColumnHeaders() {
2907     // use this method to modify $this->_columnHeaders
2908   }
2909
2910   /**
2911    * Assign rows to the template.
2912    *
2913    * @param array $rows
2914    */
2915   public function doTemplateAssignment(&$rows) {
2916     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
2917     $this->assign_by_ref('rows', $rows);
2918     $this->assign('statistics', $this->statistics($rows));
2919   }
2920
2921   /**
2922    * Build report statistics.
2923    *
2924    * Override this method to build your own statistics.
2925    *
2926    * @param array $rows
2927    *
2928    * @return array
2929    */
2930   public function statistics(&$rows) {
2931     $statistics = array();
2932
2933     $count = count($rows);
2934
2935     if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
2936       $count++;
2937     }
2938
2939     $this->countStat($statistics, $count);
2940
2941     $this->groupByStat($statistics);
2942
2943     $this->filterStat($statistics);
2944
2945     return $statistics;
2946   }
2947
2948   /**
2949    * Add count statistics.
2950    *
2951    * @param array $statistics
2952    * @param int $count
2953    */
2954   public function countStat(&$statistics, $count) {
2955     $statistics['counts']['rowCount'] = array(
2956       'title' => ts('Row(s) Listed'),
2957       'value' => $count,
2958     );
2959
2960     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
2961       $statistics['counts']['rowsFound'] = array(
2962         'title' => ts('Total Row(s)'),
2963         'value' => $this->_rowsFound,
2964       );
2965     }
2966   }
2967
2968   /**
2969    * Add group by statistics.
2970    *
2971    * @param array $statistics
2972    */
2973   public function groupByStat(&$statistics) {
2974     if (!empty($this->_params['group_bys']) &&
2975       is_array($this->_params['group_bys']) &&
2976       !empty($this->_params['group_bys'])
2977     ) {
2978       foreach ($this->_columns as $tableName => $table) {
2979         if (array_key_exists('group_bys', $table)) {
2980           foreach ($table['group_bys'] as $fieldName => $field) {
2981             if (!empty($this->_params['group_bys'][$fieldName])) {
2982               $combinations[] = $field['title'];
2983             }
2984           }
2985         }
2986       }
2987       $statistics['groups'][] = array(
2988         'title' => ts('Grouping(s)'),
2989         'value' => implode(' & ', $combinations),
2990       );
2991     }
2992   }
2993
2994   /**
2995    * Filter statistics.
2996    *
2997    * @param array $statistics
2998    */
2999   public function filterStat(&$statistics) {
3000     foreach ($this->_columns as $tableName => $table) {
3001       if (array_key_exists('filters', $table)) {
3002         foreach ($table['filters'] as $fieldName => $field) {
3003           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE &&
3004             CRM_Utils_Array::value('operatorType', $field) !=
3005             CRM_Report_Form::OP_MONTH
3006           ) {
3007             list($from, $to)
3008               = $this->getFromTo(
3009                 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3010                 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
3011                 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
3012                 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
3013                 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
3014               );
3015             $from_time_format = !empty($this->_params["{$fieldName}_from_time"]) ? 'h' : 'd';
3016             $from = CRM_Utils_Date::customFormat($from, NULL, array($from_time_format));
3017
3018             $to_time_format = !empty($this->_params["{$fieldName}_to_time"]) ? 'h' : 'd';
3019             $to = CRM_Utils_Date::customFormat($to, NULL, array($to_time_format));
3020
3021             if ($from || $to) {
3022               $statistics['filters'][] = array(
3023                 'title' => $field['title'],
3024                 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
3025               );
3026             }
3027             elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3028               array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE))
3029             )) {
3030               $pair = $this->getOperationPair(CRM_Report_Form::OP_DATE);
3031               $statistics['filters'][] = array(
3032                 'title' => $field['title'],
3033                 'value' => $pair[$rel],
3034               );
3035             }
3036           }
3037           else {
3038             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
3039             $value = NULL;
3040             if ($op) {
3041               $pair = $this->getOperationPair(
3042                 CRM_Utils_Array::value('operatorType', $field),
3043                 $fieldName
3044               );
3045               $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
3046               $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
3047               $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
3048               if (in_array($op, array('bw', 'nbw')) && ($min || $max)) {
3049                 $value = "{$pair[$op]} $min " . ts('and') . " $max";
3050               }
3051               elseif ($val && CRM_Utils_Array::value('operatorType', $field) & self::OP_ENTITYREF) {
3052                 $this->setEntityRefDefaults($field, $tableName);
3053                 $result = civicrm_api3($field['attributes']['entity'], 'getlist',
3054                   array('id' => $val) +
3055                   CRM_Utils_Array::value('api', $field['attributes'], array()));
3056                 $values = array();
3057                 foreach ($result['values'] as $v) {
3058                   $values[] = $v['label'];
3059                 }
3060                 $value = "{$pair[$op]} " . implode(', ', $values);
3061               }
3062               elseif ($op == 'nll' || $op == 'nnll') {
3063                 $value = $pair[$op];
3064               }
3065               elseif (is_array($val) && (!empty($val))) {
3066                 $options = CRM_Utils_Array::value('options', $field, array());
3067                 foreach ($val as $key => $valIds) {
3068                   if (isset($options[$valIds])) {
3069                     $val[$key] = $options[$valIds];
3070                   }
3071                 }
3072                 $pair[$op] = (count($val) == 1) ? (($op == 'notin' || $op ==
3073                   'mnot') ? ts('Is Not') : ts('Is')) : CRM_Utils_Array::value($op, $pair);
3074                 $val = implode(', ', $val);
3075                 $value = "{$pair[$op]} " . $val;
3076               }
3077               elseif (!is_array($val) && (!empty($val) || $val == '0') &&
3078                 isset($field['options']) &&
3079                 is_array($field['options']) && !empty($field['options'])
3080               ) {
3081                 $value = CRM_Utils_Array::value($op, $pair) . " " .
3082                   CRM_Utils_Array::value($val, $field['options'], $val);
3083               }
3084               elseif ($val) {
3085                 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
3086               }
3087             }
3088             if ($value) {
3089               $statistics['filters'][] = array(
3090                 'title' => CRM_Utils_Array::value('title', $field),
3091                 'value' => $value,
3092               );
3093             }
3094           }
3095         }
3096       }
3097     }
3098   }
3099
3100   /**
3101    * End post processing.
3102    *
3103    * @param array|null $rows
3104    */
3105   public function endPostProcess(&$rows = NULL) {
3106     if ($this->_storeResultSet) {
3107       $this->_resultSet = $rows;
3108     }
3109
3110     if ($this->_outputMode == 'print' ||
3111       $this->_outputMode == 'pdf' ||
3112       $this->_sendmail
3113     ) {
3114
3115       $content = $this->compileContent();
3116       $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
3117         "reset=1", TRUE
3118       );
3119
3120       if ($this->_sendmail) {
3121         $config = CRM_Core_Config::singleton();
3122         $attachments = array();
3123
3124         if ($this->_outputMode == 'csv') {
3125           $content
3126             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3127             ": {$url}</p>" . '<p>' .
3128             ts('The report is attached as a CSV file.') . '</p>' .
3129             $this->_formValues['report_footer'];
3130
3131           $csvFullFilename = $config->templateCompileDir .
3132             CRM_Utils_File::makeFileName('CiviReport.csv');
3133           $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
3134           file_put_contents($csvFullFilename, $csvContent);
3135           $attachments[] = array(
3136             'fullPath' => $csvFullFilename,
3137             'mime_type' => 'text/csv',
3138             'cleanName' => 'CiviReport.csv',
3139           );
3140         }
3141         if ($this->_outputMode == 'pdf') {
3142           // generate PDF content
3143           $pdfFullFilename = $config->templateCompileDir .
3144             CRM_Utils_File::makeFileName('CiviReport.pdf');
3145           file_put_contents($pdfFullFilename,
3146             CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
3147               TRUE, array('orientation' => 'landscape')
3148             )
3149           );
3150           // generate Email Content
3151           $content
3152             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3153             ": {$url}</p>" . '<p>' .
3154             ts('The report is attached as a PDF file.') . '</p>' .
3155             $this->_formValues['report_footer'];
3156
3157           $attachments[] = array(
3158             'fullPath' => $pdfFullFilename,
3159             'mime_type' => 'application/pdf',
3160             'cleanName' => 'CiviReport.pdf',
3161           );
3162         }
3163
3164         if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
3165           $this->_outputMode, $attachments
3166         )
3167         ) {
3168           CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
3169         }
3170         else {
3171           CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
3172         }
3173         return TRUE;
3174       }
3175       elseif ($this->_outputMode == 'print') {
3176         echo $content;
3177       }
3178       else {
3179         if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
3180           $config = CRM_Core_Config::singleton();
3181           //get chart image name
3182           $chartImg = $this->_chartId . '.png';
3183           //get image url path
3184           $uploadUrl
3185             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) .
3186             'openFlashChart/';
3187           $uploadUrl .= $chartImg;
3188           //get image doc path to overwrite
3189           $uploadImg
3190             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
3191             'openFlashChart/' . $chartImg;
3192           //Load the image
3193           $chart = imagecreatefrompng($uploadUrl);
3194           //convert it into formatted png
3195           header('Content-type: image/png');
3196           //overwrite with same image
3197           imagepng($chart, $uploadImg);
3198           //delete the object
3199           imagedestroy($chart);
3200         }
3201         CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
3202       }
3203       CRM_Utils_System::civiExit();
3204     }
3205     elseif ($this->_outputMode == 'csv') {
3206       CRM_Report_Utils_Report::export2csv($this, $rows);
3207     }
3208     elseif ($this->_outputMode == 'group') {
3209       $group = $this->_params['groups'];
3210       $this->add2group($group);
3211     }
3212     elseif ($this->_instanceButtonName == $this->controller->getButtonName()) {
3213       CRM_Report_Form_Instance::postProcess($this);
3214     }
3215     elseif ($this->_createNewButtonName == $this->controller->getButtonName() ||
3216       $this->_outputMode == 'create_report'
3217     ) {
3218       $this->_createNew = TRUE;
3219       CRM_Report_Form_Instance::postProcess($this);
3220     }
3221   }
3222
3223   /**
3224    * Set store result set indicator to TRUE.
3225    *
3226    * @todo explain what this does
3227    */
3228   public function storeResultSet() {
3229     $this->_storeResultSet = TRUE;
3230   }
3231
3232   /**
3233    * Get result set.
3234    *
3235    * @return bool
3236    */
3237   public function getResultSet() {
3238     return $this->_resultSet;
3239   }
3240
3241   /**
3242    * Use the form name to create the tpl file name.
3243    *
3244    * @return string
3245    */
3246   public function getTemplateFileName() {
3247     $defaultTpl = parent::getTemplateFileName();
3248     $template = CRM_Core_Smarty::singleton();
3249     if (!$template->template_exists($defaultTpl)) {
3250       $defaultTpl = 'CRM/Report/Form.tpl';
3251     }
3252     return $defaultTpl;
3253   }
3254
3255   /**
3256    * Compile the report content.
3257    *
3258    * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
3259    *
3260    * @return string
3261    */
3262   public function compileContent() {
3263     $templateFile = $this->getHookedTemplateFileName();
3264     return $this->_formValues['report_header'] .
3265     CRM_Core_Form::$_template->fetch($templateFile) .
3266     $this->_formValues['report_footer'];
3267   }
3268
3269
3270   /**
3271    * Post process function.
3272    */
3273   public function postProcess() {
3274     // get ready with post process params
3275     $this->beginPostProcess();
3276
3277     // build query
3278     $sql = $this->buildQuery();
3279
3280     // build array of result based on column headers. This method also allows
3281     // modifying column headers before using it to build result set i.e $rows.
3282     $rows = array();
3283     $this->buildRows($sql, $rows);
3284
3285     // format result set.
3286     $this->formatDisplay($rows);
3287
3288     // assign variables to templates
3289     $this->doTemplateAssignment($rows);
3290
3291     // do print / pdf / instance stuff if needed
3292     $this->endPostProcess($rows);
3293   }
3294
3295   /**
3296    * Set limit.
3297    *
3298    * @param int $rowCount
3299    *
3300    * @return array
3301    */
3302   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
3303     // lets do the pager if in html mode
3304     $this->_limit = NULL;
3305
3306     // CRM-14115, over-ride row count if rowCount is specified in URL
3307     if ($this->_dashBoardRowCount) {
3308       $rowCount = $this->_dashBoardRowCount;
3309     }
3310     if ($this->_outputMode == 'html' || $this->_outputMode == 'group') {
3311       $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
3312
3313       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
3314
3315       // @todo all http vars should be extracted in the preProcess
3316       // - not randomly in the class
3317       if (!$pageId && !empty($_POST)) {
3318         if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
3319           $pageId = max((int) $_POST['crmPID_B'], 1);
3320         }
3321         elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
3322           $pageId = max((int) $_POST['crmPID'], 1);
3323         }
3324         unset($_POST['crmPID_B'], $_POST['crmPID']);
3325       }
3326
3327       $pageId = $pageId ? $pageId : 1;
3328       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
3329       $offset = ($pageId - 1) * $rowCount;
3330
3331       $offset = CRM_Utils_Type::escape($offset, 'Int');
3332       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
3333
3334       $this->_limit = " LIMIT $offset, $rowCount";
3335       return array($offset, $rowCount);
3336     }
3337     if ($this->_limitValue) {
3338       if ($this->_offsetValue) {
3339         $this->_limit = " LIMIT {$this->_offsetValue}, {$this->_limitValue} ";
3340       }
3341       else {
3342         $this->_limit = " LIMIT " . $this->_limitValue;
3343       }
3344     }
3345   }
3346
3347   /**
3348    * Set pager.
3349    *
3350    * @param int $rowCount
3351    */
3352   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
3353
3354     // CRM-14115, over-ride row count if rowCount is specified in URL
3355     if ($this->_dashBoardRowCount) {
3356       $rowCount = $this->_dashBoardRowCount;
3357     }
3358
3359     if ($this->_limit && ($this->_limit != '')) {
3360       $sql = "SELECT FOUND_ROWS();";
3361       $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
3362       $params = array(
3363         'total' => $this->_rowsFound,
3364         'rowCount' => $rowCount,
3365         'status' => ts('Records') . ' %%StatusMessage%%',
3366         'buttonBottom' => 'PagerBottomButton',
3367         'buttonTop' => 'PagerTopButton',
3368         'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
3369       );
3370
3371       $pager = new CRM_Utils_Pager($params);
3372       $this->assign_by_ref('pager', $pager);
3373       $this->ajaxResponse['totalRows'] = $this->_rowsFound;
3374     }
3375   }
3376
3377   /**
3378    * Build where clause for groups.
3379    *
3380    * @param string $field
3381    * @param mixed $value
3382    * @param string $op
3383    *
3384    * @return string
3385    */
3386   public function whereGroupClause($field, $value, $op) {
3387
3388     $smartGroupQuery = "";
3389
3390     $group = new CRM_Contact_DAO_Group();
3391     $group->is_active = 1;
3392     $group->find();
3393     $smartGroups = array();
3394     while ($group->fetch()) {
3395       if (in_array($group->id, $this->_params['gid_value']) &&
3396         $group->saved_search_id
3397       ) {
3398         $smartGroups[] = $group->id;
3399       }
3400     }
3401
3402     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3403
3404     $smartGroupQuery = '';
3405     if (!empty($smartGroups)) {
3406       $smartGroups = implode(',', $smartGroups);
3407       $smartGroupQuery = " UNION DISTINCT
3408                   SELECT DISTINCT smartgroup_contact.contact_id
3409                   FROM civicrm_group_contact_cache smartgroup_contact
3410                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3411     }
3412
3413     $sqlOp = $this->getSQLOperator($op);
3414     if (!is_array($value)) {
3415       $value = array($value);
3416     }
3417     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3418
3419     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3420                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
3421                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
3422                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
3423                           {$smartGroupQuery} ) ";
3424   }
3425
3426   /**
3427    * Build where clause for tags.
3428    *
3429    * @param string $field
3430    * @param mixed $value
3431    * @param string $op
3432    *
3433    * @return string
3434    */
3435   public function whereTagClause($field, $value, $op) {
3436     // not using left join in query because if any contact
3437     // belongs to more than one tag, results duplicate
3438     // entries.
3439     $sqlOp = $this->getSQLOperator($op);
3440     if (!is_array($value)) {
3441       $value = array($value);
3442     }
3443     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3444     $entity_table = $this->_tagFilterTable;
3445     return " {$this->_aliases[$entity_table]}.id {$sqlOp} (
3446                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
3447                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
3448                           WHERE entity_table = '$entity_table' AND {$clause} ) ";
3449   }
3450
3451   /**
3452    * Generate membership organization clause.
3453    *
3454    * @param mixed $value
3455    * @param string $op SQL Operator
3456    *
3457    * @return string
3458    */
3459   public function whereMembershipOrgClause($value, $op) {
3460     $sqlOp = $this->getSQLOperator($op);
3461     if (!is_array($value)) {
3462       $value = array($value);
3463     }
3464
3465     $tmp_membership_org_sql_list = implode(', ', $value);
3466     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3467                           SELECT DISTINCT mem.contact_id
3468                           FROM civicrm_membership mem
3469                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3470                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3471                           WHERE mt.member_of_contact_id IN (" .
3472     $tmp_membership_org_sql_list . ")
3473                           AND mt.is_active = '1'
3474                           AND mem_status.is_current_member = '1'
3475                           AND mem_status.is_active = '1' )  ";
3476   }
3477
3478   /**
3479    * Generate Membership Type SQL Clause.
3480    *
3481    * @param mixed $value
3482    * @param string $op
3483    *
3484    * @return string
3485    *   SQL query string
3486    */
3487   public function whereMembershipTypeClause($value, $op) {
3488     $sqlOp = $this->getSQLOperator($op);
3489     if (!is_array($value)) {
3490       $value = array($value);
3491     }
3492
3493     $tmp_membership_sql_list = implode(', ', $value);
3494     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3495                           SELECT DISTINCT mem.contact_id
3496                           FROM civicrm_membership mem
3497                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3498                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3499                           WHERE mem.membership_type_id IN (" .
3500     $tmp_membership_sql_list . ")
3501                           AND mt.is_active = '1'
3502                           AND mem_status.is_current_member = '1'
3503                           AND mem_status.is_active = '1' ) ";
3504   }
3505
3506   /**
3507    * Build acl clauses.
3508    *
3509    * @param string $tableAlias
3510    */
3511   public function buildACLClause($tableAlias = 'contact_a') {
3512     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
3513   }
3514
3515   /**
3516    * Add custom data to the columns.
3517    *
3518    * @param bool $addFields
3519    * @param array $permCustomGroupIds
3520    */
3521   public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
3522     if (empty($this->_customGroupExtends)) {
3523       return;
3524     }
3525     if (!is_array($this->_customGroupExtends)) {
3526       $this->_customGroupExtends = array($this->_customGroupExtends);
3527     }
3528     $customGroupWhere = '';
3529     if (!empty($permCustomGroupIds)) {
3530       $customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) .
3531         ") AND";
3532     }
3533     $sql = "
3534 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
3535        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
3536 FROM   civicrm_custom_group cg
3537 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
3538 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
3539       {$customGroupWhere}
3540       cg.is_active = 1 AND
3541       cf.is_active = 1 AND
3542       cf.is_searchable = 1
3543 ORDER BY cg.weight, cf.weight";
3544     $customDAO = CRM_Core_DAO::executeQuery($sql);
3545
3546     $curTable = NULL;
3547     while ($customDAO->fetch()) {
3548       if ($customDAO->table_name != $curTable) {
3549         $curTable = $customDAO->table_name;
3550         $curFields = $curFilters = array();
3551
3552         // dummy dao object
3553         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
3554         $this->_columns[$curTable]['extends'] = $customDAO->extends;
3555         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
3556         $this->_columns[$curTable]['group_title'] = $customDAO->title;
3557
3558         foreach (array(
3559                    'fields',
3560                    'filters',
3561                    'group_bys',
3562                  ) as $colKey) {
3563           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
3564             $this->_columns[$curTable][$colKey] = array();
3565           }
3566         }
3567       }
3568       $fieldName = 'custom_' . $customDAO->cf_id;
3569
3570       if ($addFields) {
3571         // this makes aliasing work in favor
3572         $curFields[$fieldName] = array(
3573           'name' => $customDAO->column_name,
3574           'title' => $customDAO->label,
3575           'dataType' => $customDAO->data_type,
3576           'htmlType' => $customDAO->html_type,
3577         );
3578       }
3579       if ($this->_customGroupFilters) {
3580         // this makes aliasing work in favor
3581         $curFilters[$fieldName] = array(
3582           'name' => $customDAO->column_name,
3583           'title' => $customDAO->label,
3584           'dataType' => $customDAO->data_type,
3585           'htmlType' => $customDAO->html_type,
3586         );
3587       }
3588
3589       switch ($customDAO->data_type) {
3590         case 'Date':
3591           // filters
3592           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
3593           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
3594           // CRM-6946, show time part for datetime date fields
3595           if ($customDAO->time_format) {
3596             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
3597           }
3598           break;
3599
3600         case 'Boolean':
3601           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
3602           $curFilters[$fieldName]['options'] = array(
3603             '' => ts('- select -'),
3604             1 => ts('Yes'),
3605             0 => ts('No'),
3606           );
3607           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3608           break;
3609
3610         case 'Int':
3611           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
3612           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3613           break;
3614
3615         case 'Money':
3616           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3617           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3618           break;
3619
3620         case 'Float':
3621           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3622           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
3623           break;
3624
3625         case 'String':
3626           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3627
3628           if (!empty($customDAO->option_group_id)) {
3629             if (in_array($customDAO->html_type, array(
3630               'Multi-Select',
3631               'AdvMulti-Select',
3632               'CheckBox',
3633             ))) {
3634               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3635             }
3636             else {
3637               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3638             }
3639             if ($this->_customGroupFilters) {
3640               $curFilters[$fieldName]['options'] = array();
3641               $ogDAO = CRM_Core_DAO::executeQuery("SELECT ov.value, ov.label FROM civicrm_option_value ov WHERE ov.option_group_id = %1 ORDER BY ov.weight", array(
3642                 1 => array(
3643                   $customDAO->option_group_id,
3644                   'Integer',
3645                 ),
3646               ));
3647               while ($ogDAO->fetch()) {
3648                 $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label;
3649               }
3650               CRM_Utils_Hook::customFieldOptions($customDAO->cf_id, $curFilters[$fieldName]['options'], FALSE);
3651             }
3652           }
3653           break;
3654
3655         case 'StateProvince':
3656           if (in_array($customDAO->html_type, array(
3657             'Multi-Select State/Province',
3658           ))) {
3659             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3660           }
3661           else {
3662             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3663           }
3664           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince();
3665           break;
3666
3667         case 'Country':
3668           if (in_array($customDAO->html_type, array(
3669             'Multi-Select Country',
3670           ))) {
3671             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3672           }
3673           else {
3674             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3675           }
3676           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country();
3677           break;
3678
3679         case 'ContactReference':
3680           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3681           $curFilters[$fieldName]['name'] = 'display_name';
3682           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3683
3684           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3685           $curFields[$fieldName]['name'] = 'display_name';
3686           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3687           break;
3688
3689         default:
3690           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3691           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3692       }
3693
3694       if (!array_key_exists('type', $curFields[$fieldName])) {
3695         $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
3696       }
3697
3698       if ($addFields) {
3699         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
3700       }
3701       if ($this->_customGroupFilters) {
3702         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
3703       }
3704       if ($this->_customGroupGroupBy) {
3705         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
3706       }
3707     }
3708   }
3709
3710   /**
3711    * Build custom data from clause.
3712    */
3713   public function customDataFrom() {
3714     if (empty($this->_customGroupExtends)) {
3715       return;
3716     }
3717     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
3718
3719     foreach ($this->_columns as $table => $prop) {
3720       if (substr($table, 0, 13) == 'civicrm_value' ||
3721         substr($table, 0, 12) == 'custom_value'
3722       ) {
3723         $extendsTable = $mapper[$prop['extends']];
3724
3725         // check field is in params
3726         if (!$this->isFieldSelected($prop)) {
3727           continue;
3728         }
3729         $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id");
3730
3731         $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
3732         $this->_from .= "
3733 {$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$baseJoin}";
3734         // handle for ContactReference
3735         if (array_key_exists('fields', $prop)) {
3736           foreach ($prop['fields'] as $fieldName => $field) {
3737             if (CRM_Utils_Array::value('dataType', $field) ==
3738               'ContactReference'
3739             ) {
3740               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
3741               $this->_from .= "
3742 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
3743             }
3744           }
3745         }
3746       }
3747     }
3748   }
3749
3750   /**
3751    * Check if the field is selected.
3752    *
3753    * @param string $prop
3754    *
3755    * @return bool
3756    */
3757   public function isFieldSelected($prop) {
3758     if (empty($prop)) {
3759       return FALSE;
3760     }
3761
3762     if (!empty($this->_params['fields'])) {
3763       foreach (array_keys($prop['fields']) as $fieldAlias) {
3764         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
3765         if ($customFieldId) {
3766           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
3767             return TRUE;
3768           }
3769
3770           //might be survey response field.
3771           if (!empty($this->_params['fields']['survey_response']) &&
3772             !empty($prop['fields'][$fieldAlias]['isSurveyResponseField'])
3773           ) {
3774             return TRUE;
3775           }
3776         }
3777       }
3778     }
3779
3780     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
3781       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
3782         if (array_key_exists($fieldAlias, $this->_params['group_bys']) &&
3783           CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3784         ) {
3785           return TRUE;
3786         }
3787       }
3788     }
3789
3790     if (!empty($this->_params['order_bys'])) {
3791       foreach (array_keys($prop['fields']) as $fieldAlias) {
3792         foreach ($this->_params['order_bys'] as $orderBy) {
3793           if ($fieldAlias == $orderBy['column'] &&
3794             CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3795           ) {
3796             return TRUE;
3797           }
3798         }
3799       }
3800     }
3801
3802     if (!empty($prop['filters']) && $this->_customGroupFilters) {
3803       foreach ($prop['filters'] as $fieldAlias => $val) {
3804         foreach (array(
3805                    'value',
3806                    'min',
3807                    'max',
3808                    'relative',
3809                    'from',
3810                    'to',
3811                  ) as $attach) {
3812           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
3813             (!empty($this->_params[$fieldAlias . '_' . $attach])
3814               || ($attach != 'relative' &&
3815                 $this->_params[$fieldAlias . '_' . $attach] == '0')
3816             )
3817           ) {
3818             return TRUE;
3819           }
3820         }
3821         if (!empty($this->_params[$fieldAlias . '_op']) &&
3822           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
3823         ) {
3824           return TRUE;
3825         }
3826       }
3827     }
3828
3829     return FALSE;
3830   }
3831
3832   /**
3833    * Check for empty order_by configurations and remove them.
3834    *
3835    * Also set template to hide them.
3836    *
3837    * @param array $formValues
3838    */
3839   public function preProcessOrderBy(&$formValues) {
3840     // Object to show/hide form elements
3841     $_showHide = new CRM_Core_ShowHideBlocks('', '');
3842
3843     $_showHide->addShow('optionField_1');
3844
3845     // Cycle through order_by options; skip any empty ones, and hide them as well
3846     $n = 1;
3847
3848     if (!empty($formValues['order_bys'])) {
3849       foreach ($formValues['order_bys'] as $order_by) {
3850         if ($order_by['column'] && $order_by['column'] != '-') {
3851           $_showHide->addShow('optionField_' . $n);
3852           $orderBys[$n] = $order_by;
3853           $n++;
3854         }
3855       }
3856     }
3857     for ($i = $n; $i <= 5; $i++) {
3858       if ($i > 1) {
3859         $_showHide->addHide('optionField_' . $i);
3860       }
3861     }
3862
3863     // overwrite order_by options with modified values
3864     if (!empty($orderBys)) {
3865       $formValues['order_bys'] = $orderBys;
3866     }
3867     else {
3868       $formValues['order_bys'] = array(1 => array('column' => '-'));
3869     }
3870
3871     // assign show/hide data to template
3872     $_showHide->addToTemplate();
3873   }
3874
3875   /**
3876    * Check if table name has columns in SELECT clause.
3877    *
3878    * @param string $tableName
3879    *   Name of table (index of $this->_columns array).
3880    *
3881    * @return bool
3882    */
3883   public function isTableSelected($tableName) {
3884     return in_array($tableName, $this->selectedTables());
3885   }
3886
3887   /**
3888    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause.
3889    *
3890    * If the array is unset it will be built.
3891    *
3892    * @return array
3893    *   selectedTables
3894    */
3895   public function selectedTables() {
3896     if (!$this->_selectedTables) {
3897       $orderByColumns = array();
3898       if (array_key_exists('order_bys', $this->_params) &&
3899         is_array($this->_params['order_bys'])
3900       ) {
3901         foreach ($this->_params['order_bys'] as $orderBy) {
3902           $orderByColumns[] = $orderBy['column'];
3903         }
3904       }
3905
3906       foreach ($this->_columns as $tableName => $table) {
3907         if (array_key_exists('fields', $table)) {
3908           foreach ($table['fields'] as $fieldName => $field) {
3909             if (!empty($field['required']) ||
3910               !empty($this->_params['fields'][$fieldName])
3911             ) {
3912               $this->_selectedTables[] = $tableName;
3913               break;
3914             }
3915           }
3916         }
3917         if (array_key_exists('order_bys', $table)) {
3918           foreach ($table['order_bys'] as $orderByName => $orderBy) {
3919             if (in_array($orderByName, $orderByColumns)) {
3920               $this->_selectedTables[] = $tableName;
3921               break;
3922             }
3923           }
3924         }
3925         if (array_key_exists('filters', $table)) {
3926           foreach ($table['filters'] as $filterName => $filter) {
3927             if (!empty($this->_params["{$filterName}_value"]) ||
3928               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3929               'nll' ||
3930               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3931               'nnll'
3932             ) {
3933               $this->_selectedTables[] = $tableName;
3934               break;
3935             }
3936           }
3937         }
3938       }
3939     }
3940     return $this->_selectedTables;
3941   }
3942
3943   /**
3944    * Add address fields.
3945    *
3946    * @deprecated - use getAddressColumns which is a more accurate description
3947    * and also accepts an array of options rather than a long list
3948    *
3949    * adding address fields to construct function in reports
3950    *
3951    * @param bool $groupBy
3952    *   Add GroupBy? Not appropriate for detail report.
3953    * @param bool $orderBy
3954    *   Add GroupBy? Not appropriate for detail report.
3955    * @param bool $filters
3956    * @param array $defaults
3957    *
3958    * @return array
3959    *   address fields for construct clause
3960    */
3961   public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
3962     $addressFields = array(
3963       'civicrm_address' => array(
3964         'dao' => 'CRM_Core_DAO_Address',
3965         'fields' => array(
3966           'address_name' => array(
3967             'title' => ts('Address Name'),
3968             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
3969             'name' => 'name',
3970           ),
3971           'street_address' => array(
3972             'title' => ts('Street Address'),
3973             'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
3974           ),
3975           'supplemental_address_1' => array(
3976             'title' => ts('Supplementary Address Field 1'),
3977             'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
3978           ),
3979           'supplemental_address_2' => array(
3980             'title' => ts('Supplementary Address Field 2'),
3981             'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
3982           ),
3983           'street_number' => array(
3984             'name' => 'street_number',
3985             'title' => ts('Street Number'),
3986             'type' => 1,
3987             'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
3988           ),
3989           'street_name' => array(
3990             'name' => 'street_name',
3991             'title' => ts('Street Name'),
3992             'type' => 1,
3993             'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
3994           ),
3995           'street_unit' => array(
3996             'name' => 'street_unit',
3997             'title' => ts('Street Unit'),
3998             'type' => 1,
3999             'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
4000           ),
4001           'city' => array(
4002             'title' => ts('City'),
4003             'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
4004           ),
4005           'postal_code' => array(
4006             'title' => ts('Postal Code'),
4007             'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
4008           ),
4009           'postal_code_suffix' => array(
4010             'title' => ts('Postal Code Suffix'),
4011             'default' => CRM_Utils_Array::value('postal_code_suffix', $defaults, FALSE),
4012           ),
4013           'country_id' => array(
4014             'title' => ts('Country'),
4015             'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
4016           ),
4017           'state_province_id' => array(
4018             'title' => ts('State/Province'),
4019             'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
4020           ),
4021           'county_id' => array(
4022             'title' => ts('County'),
4023             'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
4024           ),
4025         ),
4026         'grouping' => 'location-fields',
4027       ),
4028     );
4029
4030     if ($filters) {
4031       $addressFields['civicrm_address']['filters'] = array(
4032         'street_number' => array(
4033           'title' => ts('Street Number'),
4034           'type' => 1,
4035           'name' => 'street_number',
4036         ),
4037         'street_name' => array(
4038           'title' => ts('Street Name'),
4039           'name' => 'street_name',
4040           'operator' => 'like',
4041         ),
4042         'postal_code' => array(
4043           'title' => ts('Postal Code'),
4044           'type' => 1,
4045           'name' => 'postal_code',
4046         ),
4047         'city' => array(
4048           'title' => ts('City'),
4049           'operator' => 'like',
4050           'name' => 'city',
4051         ),
4052         'country_id' => array(
4053           'name' => 'country_id',
4054           'title' => ts('Country'),
4055           'type' => CRM_Utils_Type::T_INT,
4056           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4057           'options' => CRM_Core_PseudoConstant::country(),
4058         ),
4059         'state_province_id' => array(
4060           'name' => 'state_province_id',
4061           'title' => ts('State/Province'),
4062           'type' => CRM_Utils_Type::T_INT,
4063           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4064           'options' => array(),
4065         ),
4066         'county_id' => array(
4067           'name' => 'county_id',
4068           'title' => ts('County'),
4069           'type' => CRM_Utils_Type::T_INT,
4070           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4071           'options' => array(),
4072         ),
4073       );
4074     }
4075
4076     if ($orderBy) {
4077       $addressFields['civicrm_address']['order_bys'] = array(
4078         'street_name' => array('title' => ts('Street Name')),
4079         'street_number' => array('title' => 'Odd / Even Street Number'),
4080         'street_address' => NULL,
4081         'city' => NULL,
4082         'postal_code' => NULL,
4083       );
4084     }
4085
4086     if ($groupBy) {
4087       $addressFields['civicrm_address']['group_bys'] = array(
4088         'street_address' => NULL,
4089         'city' => NULL,
4090         'postal_code' => NULL,
4091         'state_province_id' => array(
4092           'title' => ts('State/Province'),
4093         ),
4094         'country_id' => array(
4095           'title' => ts('Country'),
4096         ),
4097         'county_id' => array(
4098           'title' => ts('County'),
4099         ),
4100       );
4101     }
4102     return $addressFields;
4103   }
4104
4105   /**
4106    * Do AlterDisplay processing on Address Fields.
4107    *
4108    * @param array $row
4109    * @param array $rows
4110    * @param int $rowNum
4111    * @param string $baseUrl
4112    * @param string $linkText
4113    *
4114    * @return bool
4115    */
4116   public function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4117     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4118     $entryFound = FALSE;
4119     // handle country
4120     if (array_key_exists('civicrm_address_country_id', $row)) {
4121       if ($value = $row['civicrm_address_country_id']) {
4122         $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
4123         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4124           "reset=1&force=1&{$criteriaQueryParams}&" .
4125           "country_id_op=in&country_id_value={$value}",
4126           $this->_absoluteUrl, $this->_id
4127         );
4128         $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
4129         $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
4130           array(1 => $linkText)
4131         );
4132       }
4133
4134       $entryFound = TRUE;
4135     }
4136     if (array_key_exists('civicrm_address_county_id', $row)) {
4137       if ($value = $row['civicrm_address_county_id']) {
4138         $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
4139         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4140           "reset=1&force=1&{$criteriaQueryParams}&" .
4141           "county_id_op=in&county_id_value={$value}",
4142           $this->_absoluteUrl, $this->_id
4143         );
4144         $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
4145         $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
4146           array(1 => $linkText)
4147         );
4148       }
4149       $entryFound = TRUE;
4150     }
4151     // handle state province
4152     if (array_key_exists('civicrm_address_state_province_id', $row)) {
4153       if ($value = $row['civicrm_address_state_province_id']) {
4154         $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
4155
4156         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4157           "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
4158           $this->_absoluteUrl, $this->_id
4159         );
4160         $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
4161         $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
4162           array(1 => $linkText)
4163         );
4164       }
4165       $entryFound = TRUE;
4166     }
4167
4168     return $entryFound;
4169   }
4170
4171   /**
4172    * Do AlterDisplay processing on Address Fields.
4173    *
4174    * @param array $row
4175    * @param array $rows
4176    * @param int $rowNum
4177    * @param string $baseUrl
4178    * @param string $linkText
4179    *
4180    * @return bool
4181    */
4182   public function alterDisplayContactFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4183     $entryFound = FALSE;
4184     // There is no reason not to add links for all fields but it seems a bit odd to be able to click on
4185     // 'Mrs'. Also, we don't have metadata about the title. So, add selectively to addLinks.
4186     $addLinks = array('gender_id' => 'Gender');
4187     foreach (array('prefix_id', 'suffix_id', 'gender_id') as $fieldName) {
4188       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4189         if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) {
4190           $rows[$rowNum]['civicrm_contact_' . $fieldName] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $value);
4191           if (($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) {
4192             $this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title);
4193           }
4194         }
4195         $entryFound = TRUE;
4196       }
4197     }
4198     return $entryFound;
4199   }
4200
4201   /**
4202    * Adjusts dates passed in to YEAR() for fiscal year.
4203    *
4204    * @param string $fieldName
4205    *
4206    * @return string
4207    */
4208   public function fiscalYearOffset($fieldName) {
4209     $config = CRM_Core_Config::singleton();
4210     $fy = $config->fiscalYearStart;
4211     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' ||
4212       ($fy['d'] == 1 && $fy['M'] == 1)
4213     ) {
4214       return "YEAR( $fieldName )";
4215     }
4216     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" .
4217     ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
4218   }
4219
4220   /**
4221    * Add Address into From Table if required.
4222    */
4223   public function addAddressFromClause() {
4224     // include address field if address column is to be included
4225     if ((isset($this->_addressField) &&
4226         $this->_addressField
4227       ) ||
4228       $this->isTableSelected('civicrm_address')
4229     ) {
4230       $this->_from .= "
4231                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4232                            ON ({$this->_aliases['civicrm_contact']}.id =
4233                                {$this->_aliases['civicrm_address']}.contact_id) AND
4234                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4235     }
4236   }
4237
4238   /**
4239    * Add Phone into From Table if required.
4240    */
4241   public function addPhoneFromClause() {
4242     // include address field if address column is to be included
4243     if ($this->isTableSelected('civicrm_phone')
4244     ) {
4245       $this->_from .= "
4246       LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
4247       ON ({$this->_aliases['civicrm_contact']}.id =
4248       {$this->_aliases['civicrm_phone']}.contact_id) AND
4249       {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
4250     }
4251   }
4252
4253   /**
4254    * Get phone columns to add to array.
4255    *
4256    * @param array $options
4257    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4258    *   - prefix_label Label to give columns from this phone table instance
4259    *
4260    * @return array
4261    *   phone columns definition
4262    */
4263   public function getPhoneColumns($options = array()) {
4264     $defaultOptions = array(
4265       'prefix' => '',
4266       'prefix_label' => '',
4267     );
4268
4269     $options = array_merge($defaultOptions, $options);
4270
4271     $fields = array(
4272       $options['prefix'] . 'civicrm_phone' => array(
4273         'dao' => 'CRM_Core_DAO_Phone',
4274         'fields' => array(
4275           $options['prefix'] . 'phone' => array(
4276             'title' => ts($options['prefix_label'] . 'Phone'),
4277             'name' => 'phone',
4278           ),
4279         ),
4280       ),
4281     );
4282     return $fields;
4283   }
4284
4285   /**
4286    * Get address columns to add to array.
4287    *
4288    * @param array $options
4289    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4290    *   - prefix_label Label to give columns from this address table instance
4291    *
4292    * @return array
4293    *   address columns definition
4294    */
4295   public function getAddressColumns($options = array()) {
4296     $options += array(
4297       'prefix' => '',
4298       'prefix_label' => '',
4299       'group_by' => TRUE,
4300       'order_by' => TRUE,
4301       'filters' => TRUE,
4302       'defaults' => array(),
4303     );
4304     return $this->addAddressFields(
4305       $options['group_by'],
4306       $options['order_by'],
4307       $options['filters'],
4308       $options['defaults']
4309     );
4310   }
4311
4312   /**
4313    * Get a standard set of contact fields.
4314    *
4315    * @return array
4316    */
4317   public function getBasicContactFields() {
4318     return array(
4319       'sort_name' => array(
4320         'title' => ts('Contact Name'),
4321         'required' => TRUE,
4322         'default' => TRUE,
4323       ),
4324       'id' => array(
4325         'no_display' => TRUE,
4326         'required' => TRUE,
4327       ),
4328       'prefix_id' => array(
4329         'title' => ts('Contact Prefix'),
4330       ),
4331       'first_name' => array(
4332         'title' => ts('First Name'),
4333       ),
4334       'nick_name' => array(
4335         'title' => ts('Nick Name'),
4336       ),
4337       'middle_name' => array(
4338         'title' => ts('Middle Name'),
4339       ),
4340       'last_name' => array(
4341         'title' => ts('Last Name'),
4342       ),
4343       'suffix_id' => array(
4344         'title' => ts('Contact Suffix'),
4345       ),
4346       'postal_greeting_display' => array('title' => ts('Postal Greeting')),
4347       'email_greeting_display' => array('title' => ts('Email Greeting')),
4348       'addressee_display' => array('title' => ts('Address Greeting')),
4349       'contact_type' => array(
4350         'title' => ts('Contact Type'),
4351       ),
4352       'contact_sub_type' => array(
4353         'title' => ts('Contact Subtype'),
4354       ),
4355       'gender_id' => array(
4356         'title' => ts('Gender'),
4357       ),
4358       'birth_date' => array(
4359         'title' => ts('Birth Date'),
4360       ),
4361       'age' => array(
4362         'title' => ts('Age'),
4363         'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
4364       ),
4365       'job_title' => array(
4366         'title' => ts('Contact Job title'),
4367       ),
4368       'organization_name' => array(
4369         'title' => ts('Organization Name'),
4370       ),
4371       'external_identifier' => array(
4372         'title' => ts('Contact identifier from external system'),
4373       ),
4374     );
4375   }
4376
4377   /**
4378    * Add contact to group.
4379    *
4380    * @param int $groupID
4381    */
4382   public function add2group($groupID) {
4383     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
4384       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
4385       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
4386
4387       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
4388       $sql = str_replace('WITH ROLLUP', '', $sql);
4389       $dao = CRM_Core_DAO::executeQuery($sql);
4390
4391       $contact_ids = array();
4392       // Add resulting contacts to group
4393       while ($dao->fetch()) {
4394         if ($dao->addtogroup_contact_id) {
4395           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
4396         }
4397       }
4398
4399       if (!empty($contact_ids)) {
4400         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
4401         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
4402       }
4403       else {
4404         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
4405       }
4406     }
4407   }
4408
4409   /**
4410    * Show charts on print screen.
4411    */
4412   public static function uploadChartImage() {
4413     // upload strictly for '.png' images
4414     $name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
4415     if (preg_match('/\.png$/', $name)) {
4416       //
4417       // POST data is usually string data, but we are passing a RAW .png
4418       // so PHP is a bit confused and $_POST is empty. But it has saved
4419       // the raw bits into $HTTP_RAW_POST_DATA
4420       //
4421       $httpRawPostData = $GLOBALS['HTTP_RAW_POST_DATA'];
4422
4423       // prepare the directory
4424       $config = CRM_Core_Config::singleton();
4425       $defaultPath
4426         = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
4427         '/openFlashChart/';
4428       if (!file_exists($defaultPath)) {
4429         mkdir($defaultPath, 0777, TRUE);
4430       }
4431
4432       // full path to the saved image including filename
4433       $destination = $defaultPath . $name;
4434
4435       //write and save
4436       $jfh = fopen($destination, 'w') or die("can't open file");
4437       fwrite($jfh, $httpRawPostData);
4438       fclose($jfh);
4439       CRM_Utils_System::civiExit();
4440     }
4441   }
4442
4443   /**
4444    * Apply common settings to entityRef fields.
4445    *
4446    * @param array $field
4447    * @param string $table
4448    */
4449   private function setEntityRefDefaults(&$field, $table) {
4450     $field['attributes'] = $field['attributes'] ? $field['attributes'] : array();
4451     $field['attributes'] += array(
4452       'entity' => CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table)),
4453       'multiple' => TRUE,
4454       'placeholder' => ts('- select -'),
4455     );
4456   }
4457
4458   /**
4459    * Add link fields to the row.
4460    *
4461    * Function adds the _link & _hover fields to the row.
4462    *
4463    * @param array $row
4464    * @param string $baseUrl
4465    * @param string $linkText
4466    * @param string $value
4467    * @param string $fieldName
4468    * @param string $tablePrefix
4469    * @param string $fieldLabel
4470    *
4471    * @return mixed
4472    */
4473   protected function addLinkToRow(&$row, $baseUrl, $linkText, $value, $fieldName, $tablePrefix, $fieldLabel) {
4474     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4475     $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4476       "reset=1&force=1&{$criteriaQueryParams}&" .
4477       $fieldName . "_op=in&{$fieldName}_value={$value}",
4478       $this->_absoluteUrl, $this->_id
4479     );
4480     $row["{$tablePrefix}_{$fieldName}_link"] = $url;
4481     $row["{$tablePrefix}_{$fieldName}_hover"] = ts("%1 for this %2.",
4482       array(1 => $linkText, 2 => $fieldLabel)
4483     );
4484   }
4485
4486 }