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