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