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