CRM/Report/Form.php

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