CRM/Report/Form.php

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