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