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