CRM/Report/Form.php

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