CRM/Report/Form.php

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