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