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
1744             = "{$field['dbAlias']} REGEXP '[[:cntrl:]]" . implode('|', $value) .
1745             "[[:cntrl:]]'";
1746         }
1747         break;
1748
1749       case 'mnot':
1750         // mnot == multiple is not one of
1751         if ($value !== NULL && count($value) > 0) {
1752           $sqlOP = $this->getSQLOperator($op);
1753           $clause
1754             = "( {$field['dbAlias']} NOT REGEXP '[[:cntrl:]]" . implode('|', $value) .
1755             "[[:cntrl:]]' OR {$field['dbAlias']} IS NULL )";
1756         }
1757         break;
1758
1759       case 'sw':
1760       case 'ew':
1761         if ($value !== NULL && strlen($value) > 0) {
1762           $value = CRM_Utils_Type::escape($value, $type);
1763           if (strpos($value, '%') === FALSE) {
1764             if ($op == 'sw') {
1765               $value = "'{$value}%'";
1766             }
1767             else {
1768               $value = "'%{$value}'";
1769             }
1770           }
1771           else {
1772             $value = "'{$value}'";
1773           }
1774           $sqlOP = $this->getSQLOperator($op);
1775           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1776         }
1777         break;
1778
1779       case 'nll':
1780       case 'nnll':
1781         $sqlOP = $this->getSQLOperator($op);
1782         $clause = "( {$field['dbAlias']} $sqlOP )";
1783         break;
1784
1785       default:
1786         if ($value !== NULL && strlen($value) > 0) {
1787           if (isset($field['clause'])) {
1788             // FIXME: we not doing escape here. Better solution is to use two
1789             // different types - data-type and filter-type
1790             $clause = $field['clause'];
1791           }
1792           else {
1793             $value = CRM_Utils_Type::escape($value, $type);
1794             $sqlOP = $this->getSQLOperator($op);
1795             if ($field['type'] == CRM_Utils_Type::T_STRING) {
1796               $value = "'{$value}'";
1797             }
1798             $clause = "( {$field['dbAlias']} $sqlOP $value )";
1799           }
1800         }
1801         break;
1802     }
1803
1804     if (!empty($field['group']) && $clause) {
1805       $clause = $this->whereGroupClause($field, $value, $op);
1806     }
1807     elseif (!empty($field['tag']) && $clause) {
1808       // not using left join in query because if any contact
1809       // belongs to more than one tag, results duplicate
1810       // entries.
1811       $clause = $this->whereTagClause($field, $value, $op);
1812     }
1813     elseif (!empty($field['membership_org']) && $clause) {
1814       $clause = $this->whereMembershipOrgClause($value, $op);
1815     }
1816     elseif (!empty($field['membership_type']) && $clause) {
1817       $clause = $this->whereMembershipTypeClause($value, $op);
1818     }
1819     return $clause;
1820   }
1821
1822   /**
1823    * Get SQL where clause for a date field.
1824    *
1825    * @param string $fieldName
1826    * @param string $relative
1827    * @param string $from
1828    * @param string $to
1829    * @param string $type
1830    * @param string $fromTime
1831    * @param string $toTime
1832    *
1833    * @return null|string
1834    */
1835   public function dateClause(
1836     $fieldName,
1837     $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
1838   ) {
1839     $clauses = array();
1840     if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) {
1841       $sqlOP = $this->getSQLOperator($relative);
1842       return "( {$fieldName} {$sqlOP} )";
1843     }
1844
1845     list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime);
1846
1847     if ($from) {
1848       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
1849       $clauses[] = "( {$fieldName} >= $from )";
1850     }
1851
1852     if ($to) {
1853       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
1854       $clauses[] = "( {$fieldName} <= {$to} )";
1855     }
1856
1857     if (!empty($clauses)) {
1858       return implode(' AND ', $clauses);
1859     }
1860
1861     return NULL;
1862   }
1863
1864   /**
1865    * Possibly unused function.
1866    *
1867    * @todo - could not find any instances where this is called
1868    *
1869    * @param bool $relative
1870    * @param string $from
1871    * @param string $to
1872    *
1873    * @return string|NULL
1874    */
1875   public function dateDisplay($relative, $from, $to) {
1876     list($from, $to) = $this->getFromTo($relative, $from, $to);
1877
1878     if ($from) {
1879       $clauses[] = CRM_Utils_Date::customFormat($from, NULL, array('m', 'M'));
1880     }
1881     else {
1882       $clauses[] = 'Past';
1883     }
1884
1885     if ($to) {
1886       $clauses[] = CRM_Utils_Date::customFormat($to, NULL, array('m', 'M'));
1887     }
1888     else {
1889       $clauses[] = 'Today';
1890     }
1891
1892     if (!empty($clauses)) {
1893       return implode(' - ', $clauses);
1894     }
1895
1896     return NULL;
1897   }
1898
1899   /**
1900    * Get values for from and to for date ranges.
1901    *
1902    * @param bool $relative
1903    * @param string $from
1904    * @param string $to
1905    * @param string $fromTime
1906    * @param string $toTime
1907    *
1908    * @return array
1909    */
1910   public function getFromTo($relative, $from, $to, $fromTime = NULL, $toTime = NULL) {
1911     if (empty($toTime)) {
1912       $toTime = '235959';
1913     }
1914     //FIX ME not working for relative
1915     if ($relative) {
1916       list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
1917       $dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
1918       $from = substr($dateRange['from'], 0, 8);
1919       //Take only Date Part, Sometime Time part is also present in 'to'
1920       $to = substr($dateRange['to'], 0, 8);
1921     }
1922     $from = CRM_Utils_Date::processDate($from, $fromTime);
1923     $to = CRM_Utils_Date::processDate($to, $toTime);
1924     return array($from, $to);
1925   }
1926
1927   /**
1928    * Alter display of rows.
1929    *
1930    * Iterate through the rows retrieved via SQL and make changes for display purposes,
1931    * such as rendering contacts as links.
1932    *
1933    * @param array $rows
1934    *   Rows generated by SQL, with an array for each row.
1935    */
1936   public function alterDisplay(&$rows) {
1937   }
1938
1939   /**
1940    * Alter the way in which custom data fields are displayed.
1941    *
1942    * @param array $rows
1943    */
1944   public function alterCustomDataDisplay(&$rows) {
1945     // custom code to alter rows having custom values
1946     if (empty($this->_customGroupExtends)) {
1947       return;
1948     }
1949
1950     $customFieldIds = array();
1951     foreach ($this->_params['fields'] as $fieldAlias => $value) {
1952       if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
1953         $customFieldIds[$fieldAlias] = $fieldId;
1954       }
1955     }
1956     if (empty($customFieldIds)) {
1957       return;
1958     }
1959
1960     $customFields = $fieldValueMap = array();
1961     $customFieldCols = array(
1962       'column_name',
1963       'data_type',
1964       'html_type',
1965       'option_group_id',
1966       'id',
1967     );
1968
1969     // skip for type date and ContactReference since date format is already handled
1970     $query = "
1971 SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label
1972 FROM  civicrm_custom_field cf
1973 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
1974 LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id
1975 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
1976       cg.is_active = 1 AND
1977       cf.is_active = 1 AND
1978       cf.is_searchable = 1 AND
1979       cf.data_type   NOT IN ('ContactReference', 'Date') AND
1980       cf.id IN (" . implode(",", $customFieldIds) . ")";
1981
1982     $dao = CRM_Core_DAO::executeQuery($query);
1983     while ($dao->fetch()) {
1984       foreach ($customFieldCols as $key) {
1985         $customFields[$dao->table_name . '_custom_' .
1986         $dao->id][$key] = $dao->$key;
1987       }
1988       if ($dao->option_group_id) {
1989         $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label;
1990       }
1991     }
1992     $dao->free();
1993
1994     $entryFound = FALSE;
1995     foreach ($rows as $rowNum => $row) {
1996       foreach ($row as $tableCol => $val) {
1997         if (array_key_exists($tableCol, $customFields)) {
1998           $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap);
1999           $entryFound = TRUE;
2000         }
2001       }
2002
2003       // skip looking further in rows, if first row itself doesn't
2004       // have the column we need
2005       if (!$entryFound) {
2006         break;
2007       }
2008     }
2009   }
2010
2011   /**
2012    * Format custom values.
2013    *
2014    * @param mixed $value
2015    * @param array $customField
2016    * @param array $fieldValueMap
2017    *
2018    * @return float|string|void
2019    */
2020   public function formatCustomValues($value, $customField, $fieldValueMap) {
2021     if (CRM_Utils_System::isNull($value)) {
2022       return NULL;
2023     }
2024
2025     $htmlType = $customField['html_type'];
2026
2027     switch ($customField['data_type']) {
2028       case 'Boolean':
2029         if ($value == '1') {
2030           $retValue = ts('Yes');
2031         }
2032         else {
2033           $retValue = ts('No');
2034         }
2035         break;
2036
2037       case 'Link':
2038         $retValue = CRM_Utils_System::formatWikiURL($value);
2039         break;
2040
2041       case 'File':
2042         $retValue = $value;
2043         break;
2044
2045       case 'Memo':
2046         $retValue = $value;
2047         break;
2048
2049       case 'Float':
2050         if ($htmlType == 'Text') {
2051           $retValue = (float) $value;
2052           break;
2053         }
2054       case 'Money':
2055         if ($htmlType == 'Text') {
2056           $retValue = CRM_Utils_Money::format($value, NULL, '%a');
2057           break;
2058         }
2059       case 'String':
2060       case 'Int':
2061         if (in_array($htmlType, array(
2062           'Text',
2063           'TextArea',
2064         ))) {
2065           $retValue = $value;
2066           break;
2067         }
2068       case 'StateProvince':
2069       case 'Country':
2070
2071         switch ($htmlType) {
2072           case 'Multi-Select Country':
2073             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2074             $customData = array();
2075             foreach ($value as $val) {
2076               if ($val) {
2077                 $customData[] = CRM_Core_PseudoConstant::country($val, FALSE);
2078               }
2079             }
2080             $retValue = implode(', ', $customData);
2081             break;
2082
2083           case 'Select Country':
2084             $retValue = CRM_Core_PseudoConstant::country($value, FALSE);
2085             break;
2086
2087           case 'Select State/Province':
2088             $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
2089             break;
2090
2091           case 'Multi-Select State/Province':
2092             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2093             $customData = array();
2094             foreach ($value as $val) {
2095               if ($val) {
2096                 $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE);
2097               }
2098             }
2099             $retValue = implode(', ', $customData);
2100             break;
2101
2102           case 'Select':
2103           case 'Radio':
2104           case 'Autocomplete-Select':
2105             $retValue = $fieldValueMap[$customField['option_group_id']][$value];
2106             break;
2107
2108           case 'CheckBox':
2109           case 'AdvMulti-Select':
2110           case 'Multi-Select':
2111             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2112             $customData = array();
2113             foreach ($value as $val) {
2114               if ($val) {
2115                 $customData[] = $fieldValueMap[$customField['option_group_id']][$val];
2116               }
2117             }
2118             $retValue = implode(', ', $customData);
2119             break;
2120
2121           default:
2122             $retValue = $value;
2123         }
2124         break;
2125
2126       default:
2127         $retValue = $value;
2128     }
2129
2130     return $retValue;
2131   }
2132
2133   /**
2134    * Remove duplicate rows.
2135    *
2136    * @param array $rows
2137    */
2138   public function removeDuplicates(&$rows) {
2139     if (empty($this->_noRepeats)) {
2140       return;
2141     }
2142     $checkList = array();
2143
2144     foreach ($rows as $key => $list) {
2145       foreach ($list as $colName => $colVal) {
2146         if (array_key_exists($colName, $checkList) &&
2147           $checkList[$colName] == $colVal
2148         ) {
2149           $rows[$key][$colName] = "";
2150         }
2151         if (in_array($colName, $this->_noRepeats)) {
2152           $checkList[$colName] = $colVal;
2153         }
2154       }
2155     }
2156   }
2157
2158   /**
2159    * Fix subtotal display.
2160    *
2161    * @param array $row
2162    * @param array $fields
2163    * @param bool $subtotal
2164    */
2165   public function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
2166     foreach ($row as $colName => $colVal) {
2167       if (in_array($colName, $fields)) {
2168       }
2169       elseif (isset($this->_columnHeaders[$colName])) {
2170         if ($subtotal) {
2171           $row[$colName] = "Subtotal";
2172           $subtotal = FALSE;
2173         }
2174         else {
2175           unset($row[$colName]);
2176         }
2177       }
2178     }
2179   }
2180
2181   /**
2182    * Calculate grant total.
2183    *
2184    * @param array $rows
2185    *
2186    * @return bool
2187    */
2188   public function grandTotal(&$rows) {
2189     if (!$this->_rollup || ($this->_rollup == '') ||
2190       ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
2191     ) {
2192       return FALSE;
2193     }
2194     $lastRow = array_pop($rows);
2195
2196     foreach ($this->_columnHeaders as $fld => $val) {
2197       if (!in_array($fld, $this->_statFields)) {
2198         if (!$this->_grandFlag) {
2199           $lastRow[$fld] = "Grand Total";
2200           $this->_grandFlag = TRUE;
2201         }
2202         else {
2203           $lastRow[$fld] = "";
2204         }
2205       }
2206     }
2207
2208     $this->assign('grandStat', $lastRow);
2209     return TRUE;
2210   }
2211
2212   /**
2213    * Format display output.
2214    *
2215    * @param array $rows
2216    * @param bool $pager
2217    */
2218   public function formatDisplay(&$rows, $pager = TRUE) {
2219     // set pager based on if any limit was applied in the query.
2220     if ($pager) {
2221       $this->setPager();
2222     }
2223
2224     // allow building charts if any
2225     if (!empty($this->_params['charts']) && !empty($rows)) {
2226       $this->buildChart($rows);
2227       $this->assign('chartEnabled', TRUE);
2228       $this->_chartId = "{$this->_params['charts']}_" .
2229         ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' .
2230         session_id();
2231       $this->assign('chartId', $this->_chartId);
2232     }
2233
2234     // unset columns not to be displayed.
2235     foreach ($this->_columnHeaders as $key => $value) {
2236       if (!empty($value['no_display'])) {
2237         unset($this->_columnHeaders[$key]);
2238       }
2239     }
2240
2241     // unset columns not to be displayed.
2242     if (!empty($rows)) {
2243       foreach ($this->_noDisplay as $noDisplayField) {
2244         foreach ($rows as $rowNum => $row) {
2245           unset($this->_columnHeaders[$noDisplayField]);
2246         }
2247       }
2248     }
2249
2250     // build array of section totals
2251     $this->sectionTotals();
2252
2253     // process grand-total row
2254     $this->grandTotal($rows);
2255
2256     // use this method for formatting rows for display purpose.
2257     $this->alterDisplay($rows);
2258     CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
2259
2260     // use this method for formatting custom rows for display purpose.
2261     $this->alterCustomDataDisplay($rows);
2262   }
2263
2264   /**
2265    * Build chart.
2266    *
2267    * @param array $rows
2268    */
2269   public function buildChart(&$rows) {
2270     // override this method for building charts.
2271   }
2272
2273   // select() method below has been added recently (v3.3), and many of the report templates might
2274   // still be having their own select() method. We should fix them as and when encountered and move
2275   // towards generalizing the select() method below.
2276
2277   /**
2278    * Generate the SELECT clause and set class variable $_select.
2279    */
2280   public function select() {
2281     $select = $this->_selectAliases = array();
2282
2283     foreach ($this->_columns as $tableName => $table) {
2284       if (array_key_exists('fields', $table)) {
2285         foreach ($table['fields'] as $fieldName => $field) {
2286           if ($tableName == 'civicrm_address') {
2287             $this->_addressField = TRUE;
2288           }
2289           if ($tableName == 'civicrm_email') {
2290             $this->_emailField = TRUE;
2291           }
2292           if ($tableName == 'civicrm_phone') {
2293             $this->_phoneField = TRUE;
2294           }
2295
2296           if (!empty($field['required']) ||
2297             !empty($this->_params['fields'][$fieldName])
2298           ) {
2299
2300             // 1. In many cases we want select clause to be built in slightly different way
2301             // for a particular field of a particular type.
2302             // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2303             // as needed.
2304             $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
2305             if ($selectClause) {
2306               $select[] = $selectClause;
2307               continue;
2308             }
2309
2310             // include statistics columns only if set
2311             if (!empty($field['statistics'])) {
2312               foreach ($field['statistics'] as $stat => $label) {
2313                 $alias = "{$tableName}_{$fieldName}_{$stat}";
2314                 switch (strtolower($stat)) {
2315                   case 'max':
2316                   case 'sum':
2317                     $select[] = "$stat({$field['dbAlias']}) as $alias";
2318                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2319                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2320                     $this->_statFields[$label] = $alias;
2321                     $this->_selectAliases[] = $alias;
2322                     break;
2323
2324                   case 'count':
2325                     $select[] = "COUNT({$field['dbAlias']}) as $alias";
2326                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2327                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2328                     $this->_statFields[$label] = $alias;
2329                     $this->_selectAliases[] = $alias;
2330                     break;
2331
2332                   case 'count_distinct':
2333                     $select[] = "COUNT(DISTINCT {$field['dbAlias']}) as $alias";
2334                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2335                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2336                     $this->_statFields[$label] = $alias;
2337                     $this->_selectAliases[] = $alias;
2338                     break;
2339
2340                   case 'avg':
2341                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
2342                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2343                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2344                     $this->_statFields[$label] = $alias;
2345                     $this->_selectAliases[] = $alias;
2346                     break;
2347                 }
2348               }
2349             }
2350             else {
2351               $alias = "{$tableName}_{$fieldName}";
2352               $select[] = "{$field['dbAlias']} as $alias";
2353               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
2354               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
2355               $this->_selectAliases[] = $alias;
2356             }
2357           }
2358         }
2359       }
2360
2361       // select for group bys
2362       if (array_key_exists('group_bys', $table)) {
2363         foreach ($table['group_bys'] as $fieldName => $field) {
2364
2365           if ($tableName == 'civicrm_address') {
2366             $this->_addressField = TRUE;
2367           }
2368           if ($tableName == 'civicrm_email') {
2369             $this->_emailField = TRUE;
2370           }
2371           if ($tableName == 'civicrm_phone') {
2372             $this->_phoneField = TRUE;
2373           }
2374           // 1. In many cases we want select clause to be built in slightly different way
2375           // for a particular field of a particular type.
2376           // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2377           // as needed.
2378           $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
2379           if ($selectClause) {
2380             $select[] = $selectClause;
2381             continue;
2382           }
2383
2384           if (!empty($this->_params['group_bys']) &&
2385             !empty($this->_params['group_bys'][$fieldName]) &&
2386             !empty($this->_params['group_bys_freq'])
2387           ) {
2388             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
2389               case 'YEARWEEK':
2390                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
2391                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2392                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2393                 $field['title'] = 'Week';
2394                 break;
2395
2396               case 'YEAR':
2397                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
2398                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2399                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2400                 $field['title'] = 'Year';
2401                 break;
2402
2403               case 'MONTH':
2404                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
2405                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2406                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2407                 $field['title'] = 'Month';
2408                 break;
2409
2410               case 'QUARTER':
2411                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
2412                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2413                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2414                 $field['title'] = 'Quarter';
2415                 break;
2416             }
2417             // for graphs and charts -
2418             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
2419               $this->_interval = $field['title'];
2420               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title']
2421                 = $field['title'] . ' Beginning';
2422               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
2423               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
2424
2425               // just to make sure these values are transferred to rows.
2426               // since we 'll need them for calculation purpose,
2427               // e.g making subtotals look nicer or graphs
2428               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
2429               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
2430             }
2431           }
2432         }
2433       }
2434     }
2435
2436     $this->_selectClauses = $select;
2437     $this->_select = "SELECT " . implode(', ', $select) . " ";
2438   }
2439
2440   /**
2441    * Build select clause for a single field.
2442    *
2443    * @param string $tableName
2444    * @param string $tableKey
2445    * @param string $fieldName
2446    * @param string $field
2447    *
2448    * @return bool
2449    */
2450   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
2451     return FALSE;
2452   }
2453
2454   /**
2455    * Build where clause.
2456    */
2457   public function where() {
2458     $this->storeWhereHavingClauseArray();
2459
2460     if (empty($this->_whereClauses)) {
2461       $this->_where = "WHERE ( 1 ) ";
2462       $this->_having = "";
2463     }
2464     else {
2465       $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
2466     }
2467
2468     if ($this->_aclWhere) {
2469       $this->_where .= " AND {$this->_aclWhere} ";
2470     }
2471
2472     if (!empty($this->_havingClauses)) {
2473       // use this clause to construct group by clause.
2474       $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
2475     }
2476   }
2477
2478   /**
2479    * Store Where clauses into an array.
2480    *
2481    * Breaking out this step makes over-riding more flexible as the clauses can be used in constructing a
2482    * temp table that may not be part of the final where clause or added
2483    * in other functions
2484    */
2485   public function storeWhereHavingClauseArray() {
2486     foreach ($this->_columns as $tableName => $table) {
2487       if (array_key_exists('filters', $table)) {
2488         foreach ($table['filters'] as $fieldName => $field) {
2489           // respect pseudofield to filter spec so fields can be marked as
2490           // not to be handled here
2491           if (!empty($field['pseudofield'])) {
2492             continue;
2493           }
2494           $clause = NULL;
2495           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
2496             if (CRM_Utils_Array::value('operatorType', $field) ==
2497               CRM_Report_Form::OP_MONTH
2498             ) {
2499               $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2500               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2501               if (is_array($value) && !empty($value)) {
2502                 $clause
2503                   = "(month({$field['dbAlias']}) $op (" . implode(', ', $value) .
2504                   '))';
2505               }
2506             }
2507             else {
2508               $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
2509               $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
2510               $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
2511               $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
2512               $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
2513               $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
2514             }
2515           }
2516           else {
2517             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2518             if ($op) {
2519               $clause = $this->whereClause($field,
2520                 $op,
2521                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
2522                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
2523                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
2524               );
2525             }
2526           }
2527
2528           if (!empty($clause)) {
2529             if (!empty($field['having'])) {
2530               $this->_havingClauses[] = $clause;
2531             }
2532             else {
2533               $this->_whereClauses[] = $clause;
2534             }
2535           }
2536         }
2537       }
2538     }
2539
2540   }
2541
2542   /**
2543    * Set output mode.
2544    */
2545   public function processReportMode() {
2546     $buttonName = $this->controller->getButtonName();
2547
2548     $output = CRM_Utils_Request::retrieve(
2549       'output',
2550       'String',
2551       CRM_Core_DAO::$_nullObject
2552     );
2553
2554     $this->_sendmail
2555       = CRM_Utils_Request::retrieve(
2556         'sendmail',
2557         'Boolean',
2558         CRM_Core_DAO::$_nullObject
2559       );
2560
2561     $this->_absoluteUrl = FALSE;
2562     $printOnly = FALSE;
2563     $this->assign('printOnly', FALSE);
2564
2565     if ($this->_printButtonName == $buttonName || $output == 'print' ||
2566       ($this->_sendmail && !$output)
2567     ) {
2568       $this->assign('printOnly', TRUE);
2569       $printOnly = TRUE;
2570       $this->assign('outputMode', 'print');
2571       $this->_outputMode = 'print';
2572       if ($this->_sendmail) {
2573         $this->_absoluteUrl = TRUE;
2574       }
2575     }
2576     elseif ($this->_pdfButtonName == $buttonName || $output == 'pdf') {
2577       $this->assign('printOnly', TRUE);
2578       $printOnly = TRUE;
2579       $this->assign('outputMode', 'pdf');
2580       $this->_outputMode = 'pdf';
2581       $this->_absoluteUrl = TRUE;
2582     }
2583     elseif ($this->_csvButtonName == $buttonName || $output == 'csv') {
2584       $this->assign('printOnly', TRUE);
2585       $printOnly = TRUE;
2586       $this->assign('outputMode', 'csv');
2587       $this->_outputMode = 'csv';
2588       $this->_absoluteUrl = TRUE;
2589     }
2590     elseif ($this->_groupButtonName == $buttonName || $output == 'group') {
2591       $this->assign('outputMode', 'group');
2592       $this->_outputMode = 'group';
2593     }
2594     elseif ($output == 'create_report' && $this->_criteriaForm) {
2595       $this->assign('outputMode', 'create_report');
2596       $this->_outputMode = 'create_report';
2597     }
2598     else {
2599       $this->assign('outputMode', 'html');
2600       $this->_outputMode = 'html';
2601     }
2602
2603     // Get today's date to include in printed reports
2604     if ($printOnly) {
2605       $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
2606       $this->assign('reportDate', $reportDate);
2607     }
2608   }
2609
2610   /**
2611    * Post Processing function for Form.
2612    *
2613    * postProcessCommon should be used to set other variables from input as the api accesses that function.
2614    */
2615   public function beginPostProcess() {
2616     $this->setParams($this->controller->exportValues($this->_name));
2617
2618     if (empty($this->_params) &&
2619       $this->_force
2620     ) {
2621       $this->setParams($this->_formValues);
2622     }
2623
2624     // hack to fix params when submitted from dashboard, CRM-8532
2625     // fields array is missing because form building etc is skipped
2626     // in dashboard mode for report
2627     //@todo - this could be done in the dashboard no we have a setter
2628     if (empty($this->_params['fields']) && !$this->_noFields) {
2629       $this->setParams($this->_formValues);
2630     }
2631
2632     $this->_formValues = $this->_params;
2633     if (CRM_Core_Permission::check('administer Reports') &&
2634       isset($this->_id) &&
2635       ($this->_instanceButtonName ==
2636         $this->controller->getButtonName() . '_save' ||
2637         $this->_chartButtonName == $this->controller->getButtonName()
2638       )
2639     ) {
2640       $this->assign('updateReportButton', TRUE);
2641     }
2642     $this->processReportMode();
2643     $this->beginPostProcessCommon();
2644   }
2645
2646   /**
2647    * BeginPostProcess function run in both report mode and non-report mode (api).
2648    */
2649   public function beginPostProcessCommon() {}
2650
2651   /**
2652    * Build the report query.
2653    *
2654    * @param bool $applyLimit
2655    *
2656    * @return string
2657    */
2658   public function buildQuery($applyLimit = TRUE) {
2659     $this->select();
2660     $this->from();
2661     $this->customDataFrom();
2662     $this->where();
2663     $this->groupBy();
2664     $this->orderBy();
2665
2666     // order_by columns not selected for display need to be included in SELECT
2667     $unselectedSectionColumns = $this->unselectedSectionColumns();
2668     foreach ($unselectedSectionColumns as $alias => $section) {
2669       $this->_select .= ", {$section['dbAlias']} as {$alias}";
2670     }
2671
2672     if ($applyLimit && empty($this->_params['charts'])) {
2673       $this->limit();
2674     }
2675     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2676
2677     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2678     return $sql;
2679   }
2680
2681   /**
2682    * Build group by clause.
2683    */
2684   public function groupBy() {
2685     $groupBys = array();
2686     if (!empty($this->_params['group_bys']) &&
2687       is_array($this->_params['group_bys']) &&
2688       !empty($this->_params['group_bys'])
2689     ) {
2690       foreach ($this->_columns as $tableName => $table) {
2691         if (array_key_exists('group_bys', $table)) {
2692           foreach ($table['group_bys'] as $fieldName => $field) {
2693             if (!empty($this->_params['group_bys'][$fieldName])) {
2694               $groupBys[] = $field['dbAlias'];
2695             }
2696           }
2697         }
2698       }
2699     }
2700
2701     if (!empty($groupBys)) {
2702       $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
2703     }
2704   }
2705
2706   /**
2707    * Build order by clause.
2708    */
2709   public function orderBy() {
2710     $this->_orderBy = "";
2711     $this->_sections = array();
2712     $this->storeOrderByArray();
2713     if (!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP') {
2714       $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2715     }
2716     $this->assign('sections', $this->_sections);
2717   }
2718
2719   /**
2720    * Extract order by fields and store as an array.
2721    *
2722    * In some cases other functions want to know which fields are selected for ordering by
2723    * Separating this into a separate function allows it to be called separately from constructing
2724    * the order by clause
2725    */
2726   public function storeOrderByArray() {
2727     $orderBys = array();
2728
2729     if (!empty($this->_params['order_bys']) &&
2730       is_array($this->_params['order_bys']) &&
2731       !empty($this->_params['order_bys'])
2732     ) {
2733
2734       // Process order_bys in user-specified order
2735       foreach ($this->_params['order_bys'] as $orderBy) {
2736         $orderByField = array();
2737         foreach ($this->_columns as $tableName => $table) {
2738           if (array_key_exists('order_bys', $table)) {
2739             // For DAO columns defined in $this->_columns
2740             $fields = $table['order_bys'];
2741           }
2742           elseif (array_key_exists('extends', $table)) {
2743             // For custom fields referenced in $this->_customGroupExtends
2744             $fields = CRM_Utils_Array::value('fields', $table, array());
2745           }
2746           else {
2747             continue;
2748           }
2749           if (!empty($fields) && is_array($fields)) {
2750             foreach ($fields as $fieldName => $field) {
2751               if ($fieldName == $orderBy['column']) {
2752                 $orderByField = array_merge($field, $orderBy);
2753                 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2754                 break 2;
2755               }
2756             }
2757           }
2758         }
2759
2760         if (!empty($orderByField)) {
2761           $this->_orderByFields[] = $orderByField;
2762           $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2763
2764           // Record any section headers for assignment to the template
2765           if (!empty($orderBy['section'])) {
2766             $orderByField['pageBreak'] = CRM_Utils_Array::value('pageBreak', $orderBy);
2767             $this->_sections[$orderByField['tplField']] = $orderByField;
2768           }
2769         }
2770       }
2771     }
2772
2773     $this->_orderByArray = $orderBys;
2774
2775     $this->assign('sections', $this->_sections);
2776   }
2777
2778   /**
2779    * Determine unselected columns.
2780    *
2781    * @return array
2782    */
2783   public function unselectedSectionColumns() {
2784     $selectColumns = array();
2785     foreach ($this->_columns as $tableName => $table) {
2786       if (array_key_exists('fields', $table)) {
2787         foreach ($table['fields'] as $fieldName => $field) {
2788           if (!empty($field['required']) ||
2789             !empty($this->_params['fields'][$fieldName])
2790           ) {
2791
2792             $selectColumns["{$tableName}_{$fieldName}"] = 1;
2793           }
2794         }
2795       }
2796     }
2797
2798     if (is_array($this->_sections)) {
2799       return array_diff_key($this->_sections, $selectColumns);
2800     }
2801     else {
2802       return array();
2803     }
2804   }
2805
2806   /**
2807    * Build output rows.
2808    *
2809    * @param string $sql
2810    * @param array $rows
2811    */
2812   public function buildRows($sql, &$rows) {
2813     $dao = CRM_Core_DAO::executeQuery($sql);
2814     if (!is_array($rows)) {
2815       $rows = array();
2816     }
2817
2818     // use this method to modify $this->_columnHeaders
2819     $this->modifyColumnHeaders();
2820
2821     $unselectedSectionColumns = $this->unselectedSectionColumns();
2822
2823     while ($dao->fetch()) {
2824       $row = array();
2825       foreach ($this->_columnHeaders as $key => $value) {
2826         if (property_exists($dao, $key)) {
2827           $row[$key] = $dao->$key;
2828         }
2829       }
2830
2831       // section headers not selected for display need to be added to row
2832       foreach ($unselectedSectionColumns as $key => $values) {
2833         if (property_exists($dao, $key)) {
2834           $row[$key] = $dao->$key;
2835         }
2836       }
2837
2838       $rows[] = $row;
2839     }
2840   }
2841
2842   /**
2843    * Calculate section totals.
2844    *
2845    * When "order by" fields are marked as sections, this assigns to the template
2846    * an array of total counts for each section. This data is used by the Smarty
2847    * plugin {sectionTotal}.
2848    */
2849   public function sectionTotals() {
2850
2851     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
2852     if (empty($this->_selectAliases)) {
2853       return;
2854     }
2855
2856     if (!empty($this->_sections)) {
2857       // build the query with no LIMIT clause
2858       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
2859       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
2860
2861       // pull section aliases out of $this->_sections
2862       $sectionAliases = array_keys($this->_sections);
2863
2864       $ifnulls = array();
2865       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
2866         $ifnulls[] = "ifnull($alias, '') as $alias";
2867       }
2868
2869       // Group (un-limited) report by all aliases and get counts. This might
2870       // be done more efficiently when the contents of $sql are known, ie. by
2871       // overriding this method in the report class.
2872
2873       $query = "select " . implode(", ", $ifnulls) .
2874         ", count(*) as ct from ($sql) as subquery group by " .
2875         implode(", ", $sectionAliases);
2876
2877       // initialize array of total counts
2878       $totals = array();
2879       $dao = CRM_Core_DAO::executeQuery($query);
2880       while ($dao->fetch()) {
2881
2882         // let $this->_alterDisplay translate any integer ids to human-readable values.
2883         $rows[0] = $dao->toArray();
2884         $this->alterDisplay($rows);
2885         $row = $rows[0];
2886
2887         // add totals for all permutations of section values
2888         $values = array();
2889         $i = 1;
2890         $aliasCount = count($sectionAliases);
2891         foreach ($sectionAliases as $alias) {
2892           $values[] = $row[$alias];
2893           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
2894           if ($i == $aliasCount) {
2895             // the last alias is the lowest-level section header; use count as-is
2896             $totals[$key] = $dao->ct;
2897           }
2898           else {
2899             // other aliases are higher level; roll count into their total
2900             $totals[$key] += $dao->ct;
2901           }
2902         }
2903       }
2904       $this->assign('sectionTotals', $totals);
2905     }
2906   }
2907
2908   /**
2909    * Modify column headers.
2910    */
2911   public function modifyColumnHeaders() {
2912     // use this method to modify $this->_columnHeaders
2913   }
2914
2915   /**
2916    * Assign rows to the template.
2917    *
2918    * @param array $rows
2919    */
2920   public function doTemplateAssignment(&$rows) {
2921     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
2922     $this->assign_by_ref('rows', $rows);
2923     $this->assign('statistics', $this->statistics($rows));
2924   }
2925
2926   /**
2927    * Build report statistics.
2928    *
2929    * Override this method to build your own statistics.
2930    *
2931    * @param array $rows
2932    *
2933    * @return array
2934    */
2935   public function statistics(&$rows) {
2936     $statistics = array();
2937
2938     $count = count($rows);
2939
2940     if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
2941       $count++;
2942     }
2943
2944     $this->countStat($statistics, $count);
2945
2946     $this->groupByStat($statistics);
2947
2948     $this->filterStat($statistics);
2949
2950     return $statistics;
2951   }
2952
2953   /**
2954    * Add count statistics.
2955    *
2956    * @param array $statistics
2957    * @param int $count
2958    */
2959   public function countStat(&$statistics, $count) {
2960     $statistics['counts']['rowCount'] = array(
2961       'title' => ts('Row(s) Listed'),
2962       'value' => $count,
2963     );
2964
2965     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
2966       $statistics['counts']['rowsFound'] = array(
2967         'title' => ts('Total Row(s)'),
2968         'value' => $this->_rowsFound,
2969       );
2970     }
2971   }
2972
2973   /**
2974    * Add group by statistics.
2975    *
2976    * @param array $statistics
2977    */
2978   public function groupByStat(&$statistics) {
2979     if (!empty($this->_params['group_bys']) &&
2980       is_array($this->_params['group_bys']) &&
2981       !empty($this->_params['group_bys'])
2982     ) {
2983       foreach ($this->_columns as $tableName => $table) {
2984         if (array_key_exists('group_bys', $table)) {
2985           foreach ($table['group_bys'] as $fieldName => $field) {
2986             if (!empty($this->_params['group_bys'][$fieldName])) {
2987               $combinations[] = $field['title'];
2988             }
2989           }
2990         }
2991       }
2992       $statistics['groups'][] = array(
2993         'title' => ts('Grouping(s)'),
2994         'value' => implode(' & ', $combinations),
2995       );
2996     }
2997   }
2998
2999   /**
3000    * Filter statistics.
3001    *
3002    * @param array $statistics
3003    */
3004   public function filterStat(&$statistics) {
3005     foreach ($this->_columns as $tableName => $table) {
3006       if (array_key_exists('filters', $table)) {
3007         foreach ($table['filters'] as $fieldName => $field) {
3008           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE &&
3009             CRM_Utils_Array::value('operatorType', $field) !=
3010             CRM_Report_Form::OP_MONTH
3011           ) {
3012             list($from, $to)
3013               = $this->getFromTo(
3014                 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3015                 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
3016                 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
3017                 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
3018                 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
3019               );
3020             $from_time_format = !empty($this->_params["{$fieldName}_from_time"]) ? 'h' : 'd';
3021             $from = CRM_Utils_Date::customFormat($from, NULL, array($from_time_format));
3022
3023             $to_time_format = !empty($this->_params["{$fieldName}_to_time"]) ? 'h' : 'd';
3024             $to = CRM_Utils_Date::customFormat($to, NULL, array($to_time_format));
3025
3026             if ($from || $to) {
3027               $statistics['filters'][] = array(
3028                 'title' => $field['title'],
3029                 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
3030               );
3031             }
3032             elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3033               array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE))
3034             )) {
3035               $pair = $this->getOperationPair(CRM_Report_Form::OP_DATE);
3036               $statistics['filters'][] = array(
3037                 'title' => $field['title'],
3038                 'value' => $pair[$rel],
3039               );
3040             }
3041           }
3042           else {
3043             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
3044             $value = NULL;
3045             if ($op) {
3046               $pair = $this->getOperationPair(
3047                 CRM_Utils_Array::value('operatorType', $field),
3048                 $fieldName
3049               );
3050               $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
3051               $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
3052               $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
3053               if (in_array($op, array('bw', 'nbw')) && ($min || $max)) {
3054                 $value = "{$pair[$op]} $min " . ts('and') . " $max";
3055               }
3056               elseif ($val && CRM_Utils_Array::value('operatorType', $field) & self::OP_ENTITYREF) {
3057                 $this->setEntityRefDefaults($field, $tableName);
3058                 $result = civicrm_api3($field['attributes']['entity'], 'getlist',
3059                   array('id' => $val) +
3060                   CRM_Utils_Array::value('api', $field['attributes'], array()));
3061                 $values = array();
3062                 foreach ($result['values'] as $v) {
3063                   $values[] = $v['label'];
3064                 }
3065                 $value = "{$pair[$op]} " . implode(', ', $values);
3066               }
3067               elseif ($op == 'nll' || $op == 'nnll') {
3068                 $value = $pair[$op];
3069               }
3070               elseif (is_array($val) && (!empty($val))) {
3071                 $options = CRM_Utils_Array::value('options', $field, array());
3072                 foreach ($val as $key => $valIds) {
3073                   if (isset($options[$valIds])) {
3074                     $val[$key] = $options[$valIds];
3075                   }
3076                 }
3077                 $pair[$op] = (count($val) == 1) ? (($op == 'notin' || $op ==
3078                   'mnot') ? ts('Is Not') : ts('Is')) : CRM_Utils_Array::value($op, $pair);
3079                 $val = implode(', ', $val);
3080                 $value = "{$pair[$op]} " . $val;
3081               }
3082               elseif (!is_array($val) && (!empty($val) || $val == '0') &&
3083                 isset($field['options']) &&
3084                 is_array($field['options']) && !empty($field['options'])
3085               ) {
3086                 $value = CRM_Utils_Array::value($op, $pair) . " " .
3087                   CRM_Utils_Array::value($val, $field['options'], $val);
3088               }
3089               elseif ($val) {
3090                 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
3091               }
3092             }
3093             if ($value) {
3094               $statistics['filters'][] = array(
3095                 'title' => CRM_Utils_Array::value('title', $field),
3096                 'value' => $value,
3097               );
3098             }
3099           }
3100         }
3101       }
3102     }
3103   }
3104
3105   /**
3106    * End post processing.
3107    *
3108    * @param array|null $rows
3109    */
3110   public function endPostProcess(&$rows = NULL) {
3111     if ($this->_storeResultSet) {
3112       $this->_resultSet = $rows;
3113     }
3114
3115     if ($this->_outputMode == 'print' ||
3116       $this->_outputMode == 'pdf' ||
3117       $this->_sendmail
3118     ) {
3119
3120       $content = $this->compileContent();
3121       $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
3122         "reset=1", TRUE
3123       );
3124
3125       if ($this->_sendmail) {
3126         $config = CRM_Core_Config::singleton();
3127         $attachments = array();
3128
3129         if ($this->_outputMode == 'csv') {
3130           $content
3131             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3132             ": {$url}</p>" . '<p>' .
3133             ts('The report is attached as a CSV file.') . '</p>' .
3134             $this->_formValues['report_footer'];
3135
3136           $csvFullFilename = $config->templateCompileDir .
3137             CRM_Utils_File::makeFileName('CiviReport.csv');
3138           $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
3139           file_put_contents($csvFullFilename, $csvContent);
3140           $attachments[] = array(
3141             'fullPath' => $csvFullFilename,
3142             'mime_type' => 'text/csv',
3143             'cleanName' => 'CiviReport.csv',
3144           );
3145         }
3146         if ($this->_outputMode == 'pdf') {
3147           // generate PDF content
3148           $pdfFullFilename = $config->templateCompileDir .
3149             CRM_Utils_File::makeFileName('CiviReport.pdf');
3150           file_put_contents($pdfFullFilename,
3151             CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
3152               TRUE, array('orientation' => 'landscape')
3153             )
3154           );
3155           // generate Email Content
3156           $content
3157             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3158             ": {$url}</p>" . '<p>' .
3159             ts('The report is attached as a PDF file.') . '</p>' .
3160             $this->_formValues['report_footer'];
3161
3162           $attachments[] = array(
3163             'fullPath' => $pdfFullFilename,
3164             'mime_type' => 'application/pdf',
3165             'cleanName' => 'CiviReport.pdf',
3166           );
3167         }
3168
3169         if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
3170           $this->_outputMode, $attachments
3171         )
3172         ) {
3173           CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
3174         }
3175         else {
3176           CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
3177         }
3178         return TRUE;
3179       }
3180       elseif ($this->_outputMode == 'print') {
3181         echo $content;
3182       }
3183       else {
3184         if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
3185           $config = CRM_Core_Config::singleton();
3186           //get chart image name
3187           $chartImg = $this->_chartId . '.png';
3188           //get image url path
3189           $uploadUrl
3190             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) .
3191             'openFlashChart/';
3192           $uploadUrl .= $chartImg;
3193           //get image doc path to overwrite
3194           $uploadImg
3195             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
3196             'openFlashChart/' . $chartImg;
3197           //Load the image
3198           $chart = imagecreatefrompng($uploadUrl);
3199           //convert it into formatted png
3200           header('Content-type: image/png');
3201           //overwrite with same image
3202           imagepng($chart, $uploadImg);
3203           //delete the object
3204           imagedestroy($chart);
3205         }
3206         CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
3207       }
3208       CRM_Utils_System::civiExit();
3209     }
3210     elseif ($this->_outputMode == 'csv') {
3211       CRM_Report_Utils_Report::export2csv($this, $rows);
3212     }
3213     elseif ($this->_outputMode == 'group') {
3214       $group = $this->_params['groups'];
3215       $this->add2group($group);
3216     }
3217     elseif ($this->_instanceButtonName == $this->controller->getButtonName()) {
3218       CRM_Report_Form_Instance::postProcess($this);
3219     }
3220     elseif ($this->_createNewButtonName == $this->controller->getButtonName() ||
3221       $this->_outputMode == 'create_report'
3222     ) {
3223       $this->_createNew = TRUE;
3224       CRM_Report_Form_Instance::postProcess($this);
3225     }
3226   }
3227
3228   /**
3229    * Set store result set indicator to TRUE.
3230    *
3231    * @todo explain what this does
3232    */
3233   public function storeResultSet() {
3234     $this->_storeResultSet = TRUE;
3235   }
3236
3237   /**
3238    * Get result set.
3239    *
3240    * @return bool
3241    */
3242   public function getResultSet() {
3243     return $this->_resultSet;
3244   }
3245
3246   /**
3247    * Use the form name to create the tpl file name.
3248    *
3249    * @return string
3250    */
3251   public function getTemplateFileName() {
3252     $defaultTpl = parent::getTemplateFileName();
3253     $template = CRM_Core_Smarty::singleton();
3254     if (!$template->template_exists($defaultTpl)) {
3255       $defaultTpl = 'CRM/Report/Form.tpl';
3256     }
3257     return $defaultTpl;
3258   }
3259
3260   /**
3261    * Compile the report content.
3262    *
3263    * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
3264    *
3265    * @return string
3266    */
3267   public function compileContent() {
3268     $templateFile = $this->getHookedTemplateFileName();
3269     return $this->_formValues['report_header'] .
3270     CRM_Core_Form::$_template->fetch($templateFile) .
3271     $this->_formValues['report_footer'];
3272   }
3273
3274
3275   /**
3276    * Post process function.
3277    */
3278   public function postProcess() {
3279     // get ready with post process params
3280     $this->beginPostProcess();
3281
3282     // build query
3283     $sql = $this->buildQuery();
3284
3285     // build array of result based on column headers. This method also allows
3286     // modifying column headers before using it to build result set i.e $rows.
3287     $rows = array();
3288     $this->buildRows($sql, $rows);
3289
3290     // format result set.
3291     $this->formatDisplay($rows);
3292
3293     // assign variables to templates
3294     $this->doTemplateAssignment($rows);
3295
3296     // do print / pdf / instance stuff if needed
3297     $this->endPostProcess($rows);
3298   }
3299
3300   /**
3301    * Set limit.
3302    *
3303    * @param int $rowCount
3304    *
3305    * @return array
3306    */
3307   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
3308     // lets do the pager if in html mode
3309     $this->_limit = NULL;
3310
3311     // CRM-14115, over-ride row count if rowCount is specified in URL
3312     if ($this->_dashBoardRowCount) {
3313       $rowCount = $this->_dashBoardRowCount;
3314     }
3315     if ($this->_outputMode == 'html' || $this->_outputMode == 'group') {
3316       $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
3317
3318       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
3319
3320       // @todo all http vars should be extracted in the preProcess
3321       // - not randomly in the class
3322       if (!$pageId && !empty($_POST)) {
3323         if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
3324           $pageId = max((int) $_POST['crmPID_B'], 1);
3325         }
3326         elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
3327           $pageId = max((int) $_POST['crmPID'], 1);
3328         }
3329         unset($_POST['crmPID_B'], $_POST['crmPID']);
3330       }
3331
3332       $pageId = $pageId ? $pageId : 1;
3333       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
3334       $offset = ($pageId - 1) * $rowCount;
3335
3336       $offset = CRM_Utils_Type::escape($offset, 'Int');
3337       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
3338
3339       $this->_limit = " LIMIT $offset, $rowCount";
3340       return array($offset, $rowCount);
3341     }
3342     if ($this->_limitValue) {
3343       if ($this->_offsetValue) {
3344         $this->_limit = " LIMIT {$this->_offsetValue}, {$this->_limitValue} ";
3345       }
3346       else {
3347         $this->_limit = " LIMIT " . $this->_limitValue;
3348       }
3349     }
3350   }
3351
3352   /**
3353    * Set pager.
3354    *
3355    * @param int $rowCount
3356    */
3357   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
3358
3359     // CRM-14115, over-ride row count if rowCount is specified in URL
3360     if ($this->_dashBoardRowCount) {
3361       $rowCount = $this->_dashBoardRowCount;
3362     }
3363
3364     if ($this->_limit && ($this->_limit != '')) {
3365       $sql = "SELECT FOUND_ROWS();";
3366       $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
3367       $params = array(
3368         'total' => $this->_rowsFound,
3369         'rowCount' => $rowCount,
3370         'status' => ts('Records') . ' %%StatusMessage%%',
3371         'buttonBottom' => 'PagerBottomButton',
3372         'buttonTop' => 'PagerTopButton',
3373         'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
3374       );
3375
3376       $pager = new CRM_Utils_Pager($params);
3377       $this->assign_by_ref('pager', $pager);
3378       $this->ajaxResponse['totalRows'] = $this->_rowsFound;
3379     }
3380   }
3381
3382   /**
3383    * Build where clause for groups.
3384    *
3385    * @param string $field
3386    * @param mixed $value
3387    * @param string $op
3388    *
3389    * @return string
3390    */
3391   public function whereGroupClause($field, $value, $op) {
3392
3393     $smartGroupQuery = "";
3394
3395     $group = new CRM_Contact_DAO_Group();
3396     $group->is_active = 1;
3397     $group->find();
3398     $smartGroups = array();
3399     while ($group->fetch()) {
3400       if (in_array($group->id, $this->_params['gid_value']) &&
3401         $group->saved_search_id
3402       ) {
3403         $smartGroups[] = $group->id;
3404       }
3405     }
3406
3407     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3408
3409     $smartGroupQuery = '';
3410     if (!empty($smartGroups)) {
3411       $smartGroups = implode(',', $smartGroups);
3412       $smartGroupQuery = " UNION DISTINCT
3413                   SELECT DISTINCT smartgroup_contact.contact_id
3414                   FROM civicrm_group_contact_cache smartgroup_contact
3415                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3416     }
3417
3418     $sqlOp = $this->getSQLOperator($op);
3419     if (!is_array($value)) {
3420       $value = array($value);
3421     }
3422     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3423
3424     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3425                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
3426                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
3427                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
3428                           {$smartGroupQuery} ) ";
3429   }
3430
3431   /**
3432    * Build where clause for tags.
3433    *
3434    * @param string $field
3435    * @param mixed $value
3436    * @param string $op
3437    *
3438    * @return string
3439    */
3440   public function whereTagClause($field, $value, $op) {
3441     // not using left join in query because if any contact
3442     // belongs to more than one tag, results duplicate
3443     // entries.
3444     $sqlOp = $this->getSQLOperator($op);
3445     if (!is_array($value)) {
3446       $value = array($value);
3447     }
3448     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3449     $entity_table = $this->_tagFilterTable;
3450     return " {$this->_aliases[$entity_table]}.id {$sqlOp} (
3451                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
3452                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
3453                           WHERE entity_table = '$entity_table' AND {$clause} ) ";
3454   }
3455
3456   /**
3457    * Generate membership organization clause.
3458    *
3459    * @param mixed $value
3460    * @param string $op SQL Operator
3461    *
3462    * @return string
3463    */
3464   public function whereMembershipOrgClause($value, $op) {
3465     $sqlOp = $this->getSQLOperator($op);
3466     if (!is_array($value)) {
3467       $value = array($value);
3468     }
3469
3470     $tmp_membership_org_sql_list = implode(', ', $value);
3471     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3472                           SELECT DISTINCT mem.contact_id
3473                           FROM civicrm_membership mem
3474                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3475                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3476                           WHERE mt.member_of_contact_id IN (" .
3477     $tmp_membership_org_sql_list . ")
3478                           AND mt.is_active = '1'
3479                           AND mem_status.is_current_member = '1'
3480                           AND mem_status.is_active = '1' )  ";
3481   }
3482
3483   /**
3484    * Generate Membership Type SQL Clause.
3485    *
3486    * @param mixed $value
3487    * @param string $op
3488    *
3489    * @return string
3490    *   SQL query string
3491    */
3492   public function whereMembershipTypeClause($value, $op) {
3493     $sqlOp = $this->getSQLOperator($op);
3494     if (!is_array($value)) {
3495       $value = array($value);
3496     }
3497
3498     $tmp_membership_sql_list = implode(', ', $value);
3499     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3500                           SELECT DISTINCT mem.contact_id
3501                           FROM civicrm_membership mem
3502                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3503                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3504                           WHERE mem.membership_type_id IN (" .
3505     $tmp_membership_sql_list . ")
3506                           AND mt.is_active = '1'
3507                           AND mem_status.is_current_member = '1'
3508                           AND mem_status.is_active = '1' ) ";
3509   }
3510
3511   /**
3512    * Build acl clauses.
3513    *
3514    * @param string $tableAlias
3515    */
3516   public function buildACLClause($tableAlias = 'contact_a') {
3517     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
3518   }
3519
3520   /**
3521    * Add custom data to the columns.
3522    *
3523    * @param bool $addFields
3524    * @param array $permCustomGroupIds
3525    */
3526   public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
3527     if (empty($this->_customGroupExtends)) {
3528       return;
3529     }
3530     if (!is_array($this->_customGroupExtends)) {
3531       $this->_customGroupExtends = array($this->_customGroupExtends);
3532     }
3533     $customGroupWhere = '';
3534     if (!empty($permCustomGroupIds)) {
3535       $customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) .
3536         ") AND";
3537     }
3538     $sql = "
3539 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
3540        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
3541 FROM   civicrm_custom_group cg
3542 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
3543 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
3544       {$customGroupWhere}
3545       cg.is_active = 1 AND
3546       cf.is_active = 1 AND
3547       cf.is_searchable = 1
3548 ORDER BY cg.weight, cf.weight";
3549     $customDAO = CRM_Core_DAO::executeQuery($sql);
3550
3551     $curTable = NULL;
3552     while ($customDAO->fetch()) {
3553       if ($customDAO->table_name != $curTable) {
3554         $curTable = $customDAO->table_name;
3555         $curFields = $curFilters = array();
3556
3557         // dummy dao object
3558         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
3559         $this->_columns[$curTable]['extends'] = $customDAO->extends;
3560         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
3561         $this->_columns[$curTable]['group_title'] = $customDAO->title;
3562
3563         foreach (array(
3564                    'fields',
3565                    'filters',
3566                    'group_bys',
3567                  ) as $colKey) {
3568           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
3569             $this->_columns[$curTable][$colKey] = array();
3570           }
3571         }
3572       }
3573       $fieldName = 'custom_' . $customDAO->cf_id;
3574
3575       if ($addFields) {
3576         // this makes aliasing work in favor
3577         $curFields[$fieldName] = array(
3578           'name' => $customDAO->column_name,
3579           'title' => $customDAO->label,
3580           'dataType' => $customDAO->data_type,
3581           'htmlType' => $customDAO->html_type,
3582         );
3583       }
3584       if ($this->_customGroupFilters) {
3585         // this makes aliasing work in favor
3586         $curFilters[$fieldName] = array(
3587           'name' => $customDAO->column_name,
3588           'title' => $customDAO->label,
3589           'dataType' => $customDAO->data_type,
3590           'htmlType' => $customDAO->html_type,
3591         );
3592       }
3593
3594       switch ($customDAO->data_type) {
3595         case 'Date':
3596           // filters
3597           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
3598           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
3599           // CRM-6946, show time part for datetime date fields
3600           if ($customDAO->time_format) {
3601             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
3602           }
3603           break;
3604
3605         case 'Boolean':
3606           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
3607           $curFilters[$fieldName]['options'] = array(
3608             '' => ts('- select -'),
3609             1 => ts('Yes'),
3610             0 => ts('No'),
3611           );
3612           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3613           break;
3614
3615         case 'Int':
3616           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
3617           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3618           break;
3619
3620         case 'Money':
3621           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3622           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3623           break;
3624
3625         case 'Float':
3626           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3627           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
3628           break;
3629
3630         case 'String':
3631           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3632
3633           if (!empty($customDAO->option_group_id)) {
3634             if (in_array($customDAO->html_type, array(
3635               'Multi-Select',
3636               'AdvMulti-Select',
3637               'CheckBox',
3638             ))) {
3639               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3640             }
3641             else {
3642               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3643             }
3644             if ($this->_customGroupFilters) {
3645               $curFilters[$fieldName]['options'] = array();
3646               $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(
3647                 1 => array(
3648                   $customDAO->option_group_id,
3649                   'Integer',
3650                 ),
3651               ));
3652               while ($ogDAO->fetch()) {
3653                 $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label;
3654               }
3655               CRM_Utils_Hook::customFieldOptions($customDAO->cf_id, $curFilters[$fieldName]['options'], FALSE);
3656             }
3657           }
3658           break;
3659
3660         case 'StateProvince':
3661           if (in_array($customDAO->html_type, array(
3662             'Multi-Select State/Province',
3663           ))) {
3664             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3665           }
3666           else {
3667             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3668           }
3669           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince();
3670           break;
3671
3672         case 'Country':
3673           if (in_array($customDAO->html_type, array(
3674             'Multi-Select Country',
3675           ))) {
3676             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3677           }
3678           else {
3679             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3680           }
3681           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country();
3682           break;
3683
3684         case 'ContactReference':
3685           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3686           $curFilters[$fieldName]['name'] = 'display_name';
3687           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3688
3689           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3690           $curFields[$fieldName]['name'] = 'display_name';
3691           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3692           break;
3693
3694         default:
3695           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3696           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3697       }
3698
3699       if (!array_key_exists('type', $curFields[$fieldName])) {
3700         $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
3701       }
3702
3703       if ($addFields) {
3704         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
3705       }
3706       if ($this->_customGroupFilters) {
3707         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
3708       }
3709       if ($this->_customGroupGroupBy) {
3710         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
3711       }
3712     }
3713   }
3714
3715   /**
3716    * Build custom data from clause.
3717    */
3718   public function customDataFrom() {
3719     if (empty($this->_customGroupExtends)) {
3720       return;
3721     }
3722     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
3723
3724     foreach ($this->_columns as $table => $prop) {
3725       if (substr($table, 0, 13) == 'civicrm_value' ||
3726         substr($table, 0, 12) == 'custom_value'
3727       ) {
3728         $extendsTable = $mapper[$prop['extends']];
3729
3730         // check field is in params
3731         if (!$this->isFieldSelected($prop)) {
3732           continue;
3733         }
3734         $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id");
3735
3736         $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
3737         $this->_from .= "
3738 {$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$baseJoin}";
3739         // handle for ContactReference
3740         if (array_key_exists('fields', $prop)) {
3741           foreach ($prop['fields'] as $fieldName => $field) {
3742             if (CRM_Utils_Array::value('dataType', $field) ==
3743               'ContactReference'
3744             ) {
3745               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
3746               $this->_from .= "
3747 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
3748             }
3749           }
3750         }
3751       }
3752     }
3753   }
3754
3755   /**
3756    * Check if the field is selected.
3757    *
3758    * @param string $prop
3759    *
3760    * @return bool
3761    */
3762   public function isFieldSelected($prop) {
3763     if (empty($prop)) {
3764       return FALSE;
3765     }
3766
3767     if (!empty($this->_params['fields'])) {
3768       foreach (array_keys($prop['fields']) as $fieldAlias) {
3769         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
3770         if ($customFieldId) {
3771           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
3772             return TRUE;
3773           }
3774
3775           //might be survey response field.
3776           if (!empty($this->_params['fields']['survey_response']) &&
3777             !empty($prop['fields'][$fieldAlias]['isSurveyResponseField'])
3778           ) {
3779             return TRUE;
3780           }
3781         }
3782       }
3783     }
3784
3785     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
3786       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
3787         if (array_key_exists($fieldAlias, $this->_params['group_bys']) &&
3788           CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3789         ) {
3790           return TRUE;
3791         }
3792       }
3793     }
3794
3795     if (!empty($this->_params['order_bys'])) {
3796       foreach (array_keys($prop['fields']) as $fieldAlias) {
3797         foreach ($this->_params['order_bys'] as $orderBy) {
3798           if ($fieldAlias == $orderBy['column'] &&
3799             CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3800           ) {
3801             return TRUE;
3802           }
3803         }
3804       }
3805     }
3806
3807     if (!empty($prop['filters']) && $this->_customGroupFilters) {
3808       foreach ($prop['filters'] as $fieldAlias => $val) {
3809         foreach (array(
3810                    'value',
3811                    'min',
3812                    'max',
3813                    'relative',
3814                    'from',
3815                    'to',
3816                  ) as $attach) {
3817           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
3818             (!empty($this->_params[$fieldAlias . '_' . $attach])
3819               || ($attach != 'relative' &&
3820                 $this->_params[$fieldAlias . '_' . $attach] == '0')
3821             )
3822           ) {
3823             return TRUE;
3824           }
3825         }
3826         if (!empty($this->_params[$fieldAlias . '_op']) &&
3827           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
3828         ) {
3829           return TRUE;
3830         }
3831       }
3832     }
3833
3834     return FALSE;
3835   }
3836
3837   /**
3838    * Check for empty order_by configurations and remove them.
3839    *
3840    * Also set template to hide them.
3841    *
3842    * @param array $formValues
3843    */
3844   public function preProcessOrderBy(&$formValues) {
3845     // Object to show/hide form elements
3846     $_showHide = new CRM_Core_ShowHideBlocks('', '');
3847
3848     $_showHide->addShow('optionField_1');
3849
3850     // Cycle through order_by options; skip any empty ones, and hide them as well
3851     $n = 1;
3852
3853     if (!empty($formValues['order_bys'])) {
3854       foreach ($formValues['order_bys'] as $order_by) {
3855         if ($order_by['column'] && $order_by['column'] != '-') {
3856           $_showHide->addShow('optionField_' . $n);
3857           $orderBys[$n] = $order_by;
3858           $n++;
3859         }
3860       }
3861     }
3862     for ($i = $n; $i <= 5; $i++) {
3863       if ($i > 1) {
3864         $_showHide->addHide('optionField_' . $i);
3865       }
3866     }
3867
3868     // overwrite order_by options with modified values
3869     if (!empty($orderBys)) {
3870       $formValues['order_bys'] = $orderBys;
3871     }
3872     else {
3873       $formValues['order_bys'] = array(1 => array('column' => '-'));
3874     }
3875
3876     // assign show/hide data to template
3877     $_showHide->addToTemplate();
3878   }
3879
3880   /**
3881    * Check if table name has columns in SELECT clause.
3882    *
3883    * @param string $tableName
3884    *   Name of table (index of $this->_columns array).
3885    *
3886    * @return bool
3887    */
3888   public function isTableSelected($tableName) {
3889     return in_array($tableName, $this->selectedTables());
3890   }
3891
3892   /**
3893    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause.
3894    *
3895    * If the array is unset it will be built.
3896    *
3897    * @return array
3898    *   selectedTables
3899    */
3900   public function selectedTables() {
3901     if (!$this->_selectedTables) {
3902       $orderByColumns = array();
3903       if (array_key_exists('order_bys', $this->_params) &&
3904         is_array($this->_params['order_bys'])
3905       ) {
3906         foreach ($this->_params['order_bys'] as $orderBy) {
3907           $orderByColumns[] = $orderBy['column'];
3908         }
3909       }
3910
3911       foreach ($this->_columns as $tableName => $table) {
3912         if (array_key_exists('fields', $table)) {
3913           foreach ($table['fields'] as $fieldName => $field) {
3914             if (!empty($field['required']) ||
3915               !empty($this->_params['fields'][$fieldName])
3916             ) {
3917               $this->_selectedTables[] = $tableName;
3918               break;
3919             }
3920           }
3921         }
3922         if (array_key_exists('order_bys', $table)) {
3923           foreach ($table['order_bys'] as $orderByName => $orderBy) {
3924             if (in_array($orderByName, $orderByColumns)) {
3925               $this->_selectedTables[] = $tableName;
3926               break;
3927             }
3928           }
3929         }
3930         if (array_key_exists('filters', $table)) {
3931           foreach ($table['filters'] as $filterName => $filter) {
3932             if (!empty($this->_params["{$filterName}_value"]) ||
3933               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3934               'nll' ||
3935               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3936               'nnll'
3937             ) {
3938               $this->_selectedTables[] = $tableName;
3939               break;
3940             }
3941           }
3942         }
3943       }
3944     }
3945     return $this->_selectedTables;
3946   }
3947
3948   /**
3949    * Add address fields.
3950    *
3951    * @deprecated - use getAddressColumns which is a more accurate description
3952    * and also accepts an array of options rather than a long list
3953    *
3954    * adding address fields to construct function in reports
3955    *
3956    * @param bool $groupBy
3957    *   Add GroupBy? Not appropriate for detail report.
3958    * @param bool $orderBy
3959    *   Add GroupBy? Not appropriate for detail report.
3960    * @param bool $filters
3961    * @param array $defaults
3962    *
3963    * @return array
3964    *   address fields for construct clause
3965    */
3966   public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
3967     $addressFields = array(
3968       'civicrm_address' => array(
3969         'dao' => 'CRM_Core_DAO_Address',
3970         'fields' => array(
3971           'address_name' => array(
3972             'title' => ts('Address Name'),
3973             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
3974             'name' => 'name',
3975           ),
3976           'street_address' => array(
3977             'title' => ts('Street Address'),
3978             'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
3979           ),
3980           'supplemental_address_1' => array(
3981             'title' => ts('Supplementary Address Field 1'),
3982             'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
3983           ),
3984           'supplemental_address_2' => array(
3985             'title' => ts('Supplementary Address Field 2'),
3986             'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
3987           ),
3988           'street_number' => array(
3989             'name' => 'street_number',
3990             'title' => ts('Street Number'),
3991             'type' => 1,
3992             'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
3993           ),
3994           'street_name' => array(
3995             'name' => 'street_name',
3996             'title' => ts('Street Name'),
3997             'type' => 1,
3998             'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
3999           ),
4000           'street_unit' => array(
4001             'name' => 'street_unit',
4002             'title' => ts('Street Unit'),
4003             'type' => 1,
4004             'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
4005           ),
4006           'city' => array(
4007             'title' => ts('City'),
4008             'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
4009           ),
4010           'postal_code' => array(
4011             'title' => ts('Postal Code'),
4012             'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
4013           ),
4014           'postal_code_suffix' => array(
4015             'title' => ts('Postal Code Suffix'),
4016             'default' => CRM_Utils_Array::value('postal_code_suffix', $defaults, FALSE),
4017           ),
4018           'country_id' => array(
4019             'title' => ts('Country'),
4020             'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
4021           ),
4022           'state_province_id' => array(
4023             'title' => ts('State/Province'),
4024             'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
4025           ),
4026           'county_id' => array(
4027             'title' => ts('County'),
4028             'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
4029           ),
4030         ),
4031         'grouping' => 'location-fields',
4032       ),
4033     );
4034
4035     if ($filters) {
4036       $addressFields['civicrm_address']['filters'] = array(
4037         'street_number' => array(
4038           'title' => ts('Street Number'),
4039           'type' => 1,
4040           'name' => 'street_number',
4041         ),
4042         'street_name' => array(
4043           'title' => ts('Street Name'),
4044           'name' => 'street_name',
4045           'operator' => 'like',
4046         ),
4047         'postal_code' => array(
4048           'title' => ts('Postal Code'),
4049           'type' => 1,
4050           'name' => 'postal_code',
4051         ),
4052         'city' => array(
4053           'title' => ts('City'),
4054           'operator' => 'like',
4055           'name' => 'city',
4056         ),
4057         'country_id' => array(
4058           'name' => 'country_id',
4059           'title' => ts('Country'),
4060           'type' => CRM_Utils_Type::T_INT,
4061           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4062           'options' => CRM_Core_PseudoConstant::country(),
4063         ),
4064         'state_province_id' => array(
4065           'name' => 'state_province_id',
4066           'title' => ts('State/Province'),
4067           'type' => CRM_Utils_Type::T_INT,
4068           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4069           'options' => array(),
4070         ),
4071         'county_id' => array(
4072           'name' => 'county_id',
4073           'title' => ts('County'),
4074           'type' => CRM_Utils_Type::T_INT,
4075           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4076           'options' => array(),
4077         ),
4078       );
4079     }
4080
4081     if ($orderBy) {
4082       $addressFields['civicrm_address']['order_bys'] = array(
4083         'street_name' => array('title' => ts('Street Name')),
4084         'street_number' => array('title' => 'Odd / Even Street Number'),
4085         'street_address' => NULL,
4086         'city' => NULL,
4087         'postal_code' => NULL,
4088       );
4089     }
4090
4091     if ($groupBy) {
4092       $addressFields['civicrm_address']['group_bys'] = array(
4093         'street_address' => NULL,
4094         'city' => NULL,
4095         'postal_code' => NULL,
4096         'state_province_id' => array(
4097           'title' => ts('State/Province'),
4098         ),
4099         'country_id' => array(
4100           'title' => ts('Country'),
4101         ),
4102         'county_id' => array(
4103           'title' => ts('County'),
4104         ),
4105       );
4106     }
4107     return $addressFields;
4108   }
4109
4110   /**
4111    * Do AlterDisplay processing on Address Fields.
4112    *
4113    * @param array $row
4114    * @param array $rows
4115    * @param int $rowNum
4116    * @param string $baseUrl
4117    * @param string $linkText
4118    *
4119    * @return bool
4120    */
4121   public function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4122     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4123     $entryFound = FALSE;
4124     // handle country
4125     if (array_key_exists('civicrm_address_country_id', $row)) {
4126       if ($value = $row['civicrm_address_country_id']) {
4127         $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
4128         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4129           "reset=1&force=1&{$criteriaQueryParams}&" .
4130           "country_id_op=in&country_id_value={$value}",
4131           $this->_absoluteUrl, $this->_id
4132         );
4133         $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
4134         $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
4135           array(1 => $linkText)
4136         );
4137       }
4138
4139       $entryFound = TRUE;
4140     }
4141     if (array_key_exists('civicrm_address_county_id', $row)) {
4142       if ($value = $row['civicrm_address_county_id']) {
4143         $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
4144         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4145           "reset=1&force=1&{$criteriaQueryParams}&" .
4146           "county_id_op=in&county_id_value={$value}",
4147           $this->_absoluteUrl, $this->_id
4148         );
4149         $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
4150         $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
4151           array(1 => $linkText)
4152         );
4153       }
4154       $entryFound = TRUE;
4155     }
4156     // handle state province
4157     if (array_key_exists('civicrm_address_state_province_id', $row)) {
4158       if ($value = $row['civicrm_address_state_province_id']) {
4159         $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
4160
4161         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4162           "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
4163           $this->_absoluteUrl, $this->_id
4164         );
4165         $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
4166         $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
4167           array(1 => $linkText)
4168         );
4169       }
4170       $entryFound = TRUE;
4171     }
4172
4173     return $entryFound;
4174   }
4175
4176   /**
4177    * Do AlterDisplay processing on Address Fields.
4178    *
4179    * @param array $row
4180    * @param array $rows
4181    * @param int $rowNum
4182    * @param string $baseUrl
4183    * @param string $linkText
4184    *
4185    * @return bool
4186    */
4187   public function alterDisplayContactFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4188     $entryFound = FALSE;
4189     // There is no reason not to add links for all fields but it seems a bit odd to be able to click on
4190     // 'Mrs'. Also, we don't have metadata about the title. So, add selectively to addLinks.
4191     $addLinks = array('gender_id' => 'Gender');
4192     foreach (array('prefix_id', 'suffix_id', 'gender_id') as $fieldName) {
4193       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4194         if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) {
4195           $rows[$rowNum]['civicrm_contact_' . $fieldName] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $value);
4196           if (($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) {
4197             $this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title);
4198           }
4199         }
4200         $entryFound = TRUE;
4201       }
4202     }
4203     return $entryFound;
4204   }
4205
4206   /**
4207    * Adjusts dates passed in to YEAR() for fiscal year.
4208    *
4209    * @param string $fieldName
4210    *
4211    * @return string
4212    */
4213   public function fiscalYearOffset($fieldName) {
4214     $config = CRM_Core_Config::singleton();
4215     $fy = $config->fiscalYearStart;
4216     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' ||
4217       ($fy['d'] == 1 && $fy['M'] == 1)
4218     ) {
4219       return "YEAR( $fieldName )";
4220     }
4221     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" .
4222     ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
4223   }
4224
4225   /**
4226    * Add Address into From Table if required.
4227    */
4228   public function addAddressFromClause() {
4229     // include address field if address column is to be included
4230     if ((isset($this->_addressField) &&
4231         $this->_addressField
4232       ) ||
4233       $this->isTableSelected('civicrm_address')
4234     ) {
4235       $this->_from .= "
4236                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4237                            ON ({$this->_aliases['civicrm_contact']}.id =
4238                                {$this->_aliases['civicrm_address']}.contact_id) AND
4239                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4240     }
4241   }
4242
4243   /**
4244    * Add Phone into From Table if required.
4245    */
4246   public function addPhoneFromClause() {
4247     // include address field if address column is to be included
4248     if ($this->isTableSelected('civicrm_phone')
4249     ) {
4250       $this->_from .= "
4251       LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
4252       ON ({$this->_aliases['civicrm_contact']}.id =
4253       {$this->_aliases['civicrm_phone']}.contact_id) AND
4254       {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
4255     }
4256   }
4257
4258   /**
4259    * Get phone columns to add to array.
4260    *
4261    * @param array $options
4262    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4263    *   - prefix_label Label to give columns from this phone table instance
4264    *
4265    * @return array
4266    *   phone columns definition
4267    */
4268   public function getPhoneColumns($options = array()) {
4269     $defaultOptions = array(
4270       'prefix' => '',
4271       'prefix_label' => '',
4272     );
4273
4274     $options = array_merge($defaultOptions, $options);
4275
4276     $fields = array(
4277       $options['prefix'] . 'civicrm_phone' => array(
4278         'dao' => 'CRM_Core_DAO_Phone',
4279         'fields' => array(
4280           $options['prefix'] . 'phone' => array(
4281             'title' => ts($options['prefix_label'] . 'Phone'),
4282             'name' => 'phone',
4283           ),
4284         ),
4285       ),
4286     );
4287     return $fields;
4288   }
4289
4290   /**
4291    * Get address columns to add to array.
4292    *
4293    * @param array $options
4294    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4295    *   - prefix_label Label to give columns from this address table instance
4296    *
4297    * @return array
4298    *   address columns definition
4299    */
4300   public function getAddressColumns($options = array()) {
4301     $options += array(
4302       'prefix' => '',
4303       'prefix_label' => '',
4304       'group_by' => TRUE,
4305       'order_by' => TRUE,
4306       'filters' => TRUE,
4307       'defaults' => array(),
4308     );
4309     return $this->addAddressFields(
4310       $options['group_by'],
4311       $options['order_by'],
4312       $options['filters'],
4313       $options['defaults']
4314     );
4315   }
4316
4317   /**
4318    * Get a standard set of contact fields.
4319    *
4320    * @return array
4321    */
4322   public function getBasicContactFields() {
4323     return array(
4324       'sort_name' => array(
4325         'title' => ts('Contact Name'),
4326         'required' => TRUE,
4327         'default' => TRUE,
4328       ),
4329       'id' => array(
4330         'no_display' => TRUE,
4331         'required' => TRUE,
4332       ),
4333       'prefix_id' => array(
4334         'title' => ts('Contact Prefix'),
4335       ),
4336       'first_name' => array(
4337         'title' => ts('First Name'),
4338       ),
4339       'nick_name' => array(
4340         'title' => ts('Nick Name'),
4341       ),
4342       'middle_name' => array(
4343         'title' => ts('Middle Name'),
4344       ),
4345       'last_name' => array(
4346         'title' => ts('Last Name'),
4347       ),
4348       'suffix_id' => array(
4349         'title' => ts('Contact Suffix'),
4350       ),
4351       'postal_greeting_display' => array('title' => ts('Postal Greeting')),
4352       'email_greeting_display' => array('title' => ts('Email Greeting')),
4353       'addressee_display' => array('title' => ts('Address Greeting')),
4354       'contact_type' => array(
4355         'title' => ts('Contact Type'),
4356       ),
4357       'contact_sub_type' => array(
4358         'title' => ts('Contact Subtype'),
4359       ),
4360       'gender_id' => array(
4361         'title' => ts('Gender'),
4362       ),
4363       'birth_date' => array(
4364         'title' => ts('Birth Date'),
4365       ),
4366       'age' => array(
4367         'title' => ts('Age'),
4368         'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
4369       ),
4370       'job_title' => array(
4371         'title' => ts('Contact Job title'),
4372       ),
4373       'organization_name' => array(
4374         'title' => ts('Organization Name'),
4375       ),
4376       'external_identifier' => array(
4377         'title' => ts('Contact identifier from external system'),
4378       ),
4379     );
4380   }
4381
4382   /**
4383    * Add contact to group.
4384    *
4385    * @param int $groupID
4386    */
4387   public function add2group($groupID) {
4388     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
4389       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
4390       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
4391
4392       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
4393       $sql = str_replace('WITH ROLLUP', '', $sql);
4394       $dao = CRM_Core_DAO::executeQuery($sql);
4395
4396       $contact_ids = array();
4397       // Add resulting contacts to group
4398       while ($dao->fetch()) {
4399         if ($dao->addtogroup_contact_id) {
4400           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
4401         }
4402       }
4403
4404       if (!empty($contact_ids)) {
4405         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
4406         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
4407       }
4408       else {
4409         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
4410       }
4411     }
4412   }
4413
4414   /**
4415    * Show charts on print screen.
4416    */
4417   public static function uploadChartImage() {
4418     // upload strictly for '.png' images
4419     $name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
4420     if (preg_match('/\.png$/', $name)) {
4421       //
4422       // POST data is usually string data, but we are passing a RAW .png
4423       // so PHP is a bit confused and $_POST is empty. But it has saved
4424       // the raw bits into $HTTP_RAW_POST_DATA
4425       //
4426       $httpRawPostData = $GLOBALS['HTTP_RAW_POST_DATA'];
4427
4428       // prepare the directory
4429       $config = CRM_Core_Config::singleton();
4430       $defaultPath
4431         = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
4432         '/openFlashChart/';
4433       if (!file_exists($defaultPath)) {
4434         mkdir($defaultPath, 0777, TRUE);
4435       }
4436
4437       // full path to the saved image including filename
4438       $destination = $defaultPath . $name;
4439
4440       //write and save
4441       $jfh = fopen($destination, 'w') or die("can't open file");
4442       fwrite($jfh, $httpRawPostData);
4443       fclose($jfh);
4444       CRM_Utils_System::civiExit();
4445     }
4446   }
4447
4448   /**
4449    * Apply common settings to entityRef fields.
4450    *
4451    * @param array $field
4452    * @param string $table
4453    */
4454   private function setEntityRefDefaults(&$field, $table) {
4455     $field['attributes'] = $field['attributes'] ? $field['attributes'] : array();
4456     $field['attributes'] += array(
4457       'entity' => CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table)),
4458       'multiple' => TRUE,
4459       'placeholder' => ts('- select -'),
4460     );
4461   }
4462
4463   /**
4464    * Add link fields to the row.
4465    *
4466    * Function adds the _link & _hover fields to the row.
4467    *
4468    * @param array $row
4469    * @param string $baseUrl
4470    * @param string $linkText
4471    * @param string $value
4472    * @param string $fieldName
4473    * @param string $tablePrefix
4474    * @param string $fieldLabel
4475    *
4476    * @return mixed
4477    */
4478   protected function addLinkToRow(&$row, $baseUrl, $linkText, $value, $fieldName, $tablePrefix, $fieldLabel) {
4479     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4480     $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4481       "reset=1&force=1&{$criteriaQueryParams}&" .
4482       $fieldName . "_op=in&{$fieldName}_value={$value}",
4483       $this->_absoluteUrl, $this->_id
4484     );
4485     $row["{$tablePrefix}_{$fieldName}_link"] = $url;
4486     $row["{$tablePrefix}_{$fieldName}_hover"] = ts("%1 for this %2.",
4487       array(1 => $linkText, 2 => $fieldLabel)
4488     );
4489   }
4490
4491 }