CRM/Report/Form.php

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