CRM/Report/Form.php

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