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