CRM/Report/Form.php

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