CRM/Report/Form.php

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