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