CRM/Report/Form.php

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