CRM/Report/Form.php

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