CRM/Report/Form.php

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