CRM/Report/Form.php

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