CRM/Report/Form.php

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