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   protected $_grandFlag = FALSE;
 165
 166   /**
 167    * An attribute for checkbox/radio form field layout
 168    *
 169    * @var array
 170    */
 171   protected $_fourColumnAttribute = array(
 172     '</td><td width="25%">', '</td><td width="25%">',
 173     '</td><td width="25%">', '</tr><tr><td>',
 174   );
 175
 176   protected $_force = 1;
 177
 178   protected $_params = NULL;
 179   protected $_formValues = NULL;
 180   protected $_instanceValues = NULL;
 181
 182   protected $_instanceForm = FALSE;
 183   protected $_criteriaForm = FALSE;
 184
 185   protected $_instanceButtonName = NULL;
 186   protected $_createNewButtonName = NULL;
 187   protected $_printButtonName = NULL;
 188   protected $_pdfButtonName = NULL;
 189   protected $_csvButtonName = NULL;
 190   protected $_groupButtonName = NULL;
 191   protected $_chartButtonName = NULL;
 192   protected $_csvSupported = TRUE;
 193   protected $_add2groupSupported = TRUE;
 194   protected $_groups = NULL;
 195   protected $_rowsFound = NULL;
 196   protected $_selectAliases = array();
 197   protected $_rollup = NULL;
 198   protected $_limit = NULL;
 199   protected $_sections = NULL;
 200   protected $_autoIncludeIndexedFieldsAsOrderBys = 0;
 201   protected $_absoluteUrl = FALSE;
 202
 203   /**
 204    * To what frequency group-by a date column
 205    *
 206    * @var array
 207    */
 208   protected $_groupByDateFreq = array(
 209     'MONTH' => 'Month',
 210     'YEARWEEK' => 'Week',
 211     'QUARTER' => 'Quarter',
 212     'YEAR' => 'Year',
 213   );
 214
 215   /**
 216    * Variables to hold the acl inner join and where clause
 217    */
 218   protected $_aclFrom = NULL;
 219   protected $_aclWhere = NULL;
 220
 221   /**
 222    * Array of DAO tables having columns included in SELECT or ORDER BY clause
 223    *
 224    * @var array
 225    */
 226   protected $_selectedTables;
 227
 228   public $_having = NULL;
 229   public $_select = NULL;
 230   public $_columnHeaders = array();
 231   public $_orderBy = NULL;
 232   public $_groupBy = NULL;
 233
 234   /**
 235    * Variable to hold the currency alias
 236    */
 237   protected $_currencyColumn = NULL;
 238
 239   /**
 240    *
 241    */
 242   function __construct() {
 243     parent::__construct();
 244
 245     // build tag filter
 246     if ($this->_tagFilter) {
 247       $this->buildTagFilter();
 248     }
 249     if ($this->_exposeContactID) {
 250       if (array_key_exists('civicrm_contact', $this->_columns)) {
 251         $this->_columns['civicrm_contact']['fields']['exposed_id'] = array(
 252           'name' => 'id',
 253           'title' => 'Contact ID',
 254           'no_repeat' => TRUE,
 255         );
 256       }
 257     }
 258
 259     if ($this->_groupFilter) {
 260       $this->buildGroupFilter();
 261     }
 262
 263     // Get all custom groups
 264     $allGroups = CRM_Core_PseudoConstant::customGroup();
 265
 266     // Get the custom groupIds for which the user have VIEW permission
 267     require_once 'CRM/ACL/API.php';
 268     $permCustomGroupIds = CRM_ACL_API::group(CRM_Core_Permission::VIEW, NULL, 'civicrm_custom_group', $allGroups, NULL);
 269
 270     // do not allow custom data for reports if user don't have
 271     // permission to access custom data.
 272     if (!empty($this->_customGroupExtends) && !CRM_Core_Permission::check('access all custom data') && empty($permCustomGroupIds)) {
 273       $this->_customGroupExtends = array();
 274     }
 275
 276     // merge custom data columns to _columns list, if any
 277     $this->addCustomDataToColumns(TRUE, $permCustomGroupIds);
 278
 279     // add / modify display columns, filters ..etc
 280     CRM_Utils_Hook::alterReportVar('columns', $this->_columns, $this);
 281
 282     //assign currencyColumn variable to tpl
 283     $this->assign('currencyColumn', $this->_currencyColumn);
 284   }
 285
 286   function preProcessCommon() {
 287     $this->_force =
 288       CRM_Utils_Request::retrieve(
 289         'force',
 290         'Boolean',
 291         CRM_Core_DAO::$_nullObject
 292       );
 293
 294     $this->_section = CRM_Utils_Request::retrieve('section', 'Integer', CRM_Core_DAO::$_nullObject);
 295
 296     $this->assign('section', $this->_section);
 297     CRM_Core_Region::instance('page-header')->add(array(
 298       'markup' => sprintf('<!-- Report class: [%s] -->', htmlentities(get_class($this))),
 299     ));
 300
 301     $this->_id = $this->get('instanceId');
 302     if (!$this->_id) {
 303       $this->_id = CRM_Report_Utils_Report::getInstanceID();
 304       if (!$this->_id) {
 305         $this->_id = CRM_Report_Utils_Report::getInstanceIDForPath();
 306       }
 307     }
 308
 309     // set qfkey so that pager picks it up and use it in the "Next > Last >>" links.
 310     // FIXME: Note setting it in $_GET doesn't work, since pager generates link based on QUERY_STRING
 311     $_SERVER['QUERY_STRING'] .= "&qfKey={$this->controller->_key}";
 312
 313     if ($this->_id) {
 314       $this->assign('instanceId', $this->_id);
 315       $params = array('id' => $this->_id);
 316       $this->_instanceValues = array();
 317       CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_Instance',
 318         $params,
 319         $this->_instanceValues
 320       );
 321       if (empty($this->_instanceValues)) {
 322         CRM_Core_Error::fatal("Report could not be loaded.");
 323       }
 324
 325       if (!empty($this->_instanceValues['permission']) &&
 326         (!(CRM_Core_Permission::check($this->_instanceValues['permission']) ||
 327           CRM_Core_Permission::check('administer Reports')
 328         ))
 329       ) {
 330         CRM_Utils_System::permissionDenied();
 331         CRM_Utils_System::civiExit();
 332       }
 333
 334       $formValues = CRM_Utils_Array::value('form_values', $this->_instanceValues);
 335       if ($formValues) {
 336         $this->_formValues = unserialize($formValues);
 337       }
 338       else {
 339         $this->_formValues = NULL;
 340       }
 341
 342       // lets always do a force if reset is found in the url.
 343       if (CRM_Utils_Array::value('reset', $_REQUEST)) {
 344         $this->_force = 1;
 345       }
 346
 347       // set the mode
 348       $this->assign('mode', 'instance');
 349     }
 350     else {
 351       list($optionValueID, $optionValue) = CRM_Report_Utils_Report::getValueIDFromUrl();
 352       $instanceCount = CRM_Report_Utils_Report::getInstanceCount($optionValue);
 353       if (($instanceCount > 0) && $optionValueID) {
 354         $this->assign('instanceUrl',
 355           CRM_Utils_System::url('civicrm/report/list',
 356             "reset=1&ovid=$optionValueID"
 357           )
 358         );
 359       }
 360       if ($optionValueID) {
 361         $this->_description = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', $optionValueID, 'description');
 362       }
 363
 364       // set the mode
 365       $this->assign('mode', 'template');
 366     }
 367
 368     // lets display the Report Settings section
 369     $this->_instanceForm = $this->_force || $this->_id || (!empty($_POST));
 370
 371     // Do not display Report Settings section if administer Reports permission is absent OR
 372     // if report instance is reserved and administer reserved reports absent
 373     if (!CRM_Core_Permission::check('administer Reports') ||
 374       ($this->_instanceValues['is_reserved'] && !CRM_Core_Permission::check('administer reserved reports'))) {
 375       $this->_instanceForm = FALSE;
 376     }
 377
 378     $this->assign('criteriaForm', FALSE);
 379     // Display Report Criteria section if user has access Report Criteria OR administer Reports AND report instance is not reserved
 380     if (CRM_Core_Permission::check('administer Reports') || CRM_Core_Permission::check('access Report Criteria')) {
 381       if (!$this->_instanceValues['is_reserved'] || CRM_Core_Permission::check('administer reserved reports')) {
 382         $this->assign('criteriaForm', TRUE);
 383         $this->_criteriaForm = TRUE;
 384       }
 385     }
 386
 387     $this->_instanceButtonName = $this->getButtonName('submit', 'save');
 388     $this->_createNewButtonName = $this->getButtonName('submit', 'next');
 389     $this->_printButtonName = $this->getButtonName('submit', 'print');
 390     $this->_pdfButtonName = $this->getButtonName('submit', 'pdf');
 391     $this->_csvButtonName = $this->getButtonName('submit', 'csv');
 392     $this->_groupButtonName = $this->getButtonName('submit', 'group');
 393     $this->_chartButtonName = $this->getButtonName('submit', 'chart');
 394   }
 395
 396   static function addBreadCrumb() {
 397     $breadCrumbs =
 398       array(
 399         array(
 400           'title' => ts('Report Templates'),
 401           'url' => CRM_Utils_System::url('civicrm/admin/report/template/list', 'reset=1'),
 402         )
 403       );
 404
 405     CRM_Utils_System::appendBreadCrumb($breadCrumbs);
 406   }
 407
 408   function preProcess() {
 409     $this->preProcessCommon();
 410
 411     if (!$this->_id) {
 412       self::addBreadCrumb();
 413     }
 414
 415     foreach ($this->_columns as $tableName => $table) {
 416       // set alias
 417       if (!isset($table['alias'])) {
 418         $this->_columns[$tableName]['alias'] = substr($tableName, 8) . '_civireport';
 419       }
 420       else {
 421         $this->_columns[$tableName]['alias'] = $table['alias'] . '_civireport';
 422       }
 423
 424       $this->_aliases[$tableName] = $this->_columns[$tableName]['alias'];
 425
 426       // higher preference to bao object
 427       if (array_key_exists('bao', $table)) {
 428         require_once str_replace('_', DIRECTORY_SEPARATOR, $table['bao'] . '.php');
 429         eval("\$expFields = {$table['bao']}::exportableFields( );");
 430       }
 431       else {
 432         require_once str_replace('_', DIRECTORY_SEPARATOR, $table['dao'] . '.php');
 433         eval("\$expFields = {$table['dao']}::export( );");
 434       }
 435
 436       $doNotCopy = array('required');
 437
 438       $fieldGroups = array('fields', 'filters', 'group_bys', 'order_bys');
 439       foreach ($fieldGroups as $fieldGrp) {
 440         if (CRM_Utils_Array::value($fieldGrp, $table) && is_array($table[$fieldGrp])) {
 441           foreach ($table[$fieldGrp] as $fieldName => $field) {
 442             // $name is the field name used to reference the BAO/DAO export fields array
 443             $name = isset($field['name']) ? $field['name'] : $fieldName;
 444
 445             // Sometimes the field name key in the BAO/DAO export fields array is
 446             // different from the actual database field name.
 447             // Unset $field['name'] so that actual database field name can be obtained
 448             // from the BAO/DAO export fields array.
 449             unset($field['name']);
 450
 451             if (array_key_exists($name, $expFields)) {
 452               foreach ($doNotCopy as $dnc) {
 453                 // unset the values we don't want to be copied.
 454                 unset($expFields[$name][$dnc]);
 455               }
 456               if (empty($field)) {
 457                 $this->_columns[$tableName][$fieldGrp][$fieldName] = $expFields[$name];
 458               }
 459               else {
 460                 foreach ($expFields[$name] as $property => $val) {
 461                   if (!array_key_exists($property, $field)) {
 462                     $this->_columns[$tableName][$fieldGrp][$fieldName][$property] = $val;
 463                   }
 464                 }
 465               }
 466             }
 467
 468             // fill other vars
 469             if (CRM_Utils_Array::value('no_repeat', $field)) {
 470               $this->_noRepeats[] = "{$tableName}_{$fieldName}";
 471             }
 472             if (CRM_Utils_Array::value('no_display', $field)) {
 473               $this->_noDisplay[] = "{$tableName}_{$fieldName}";
 474             }
 475
 476             // set alias = table-name, unless already set
 477             $alias = isset($field['alias']) ? $field['alias'] : (isset($this->_columns[$tableName]['alias']) ?
 478                      $this->_columns[$tableName]['alias'] : $tableName
 479             );
 480             $this->_columns[$tableName][$fieldGrp][$fieldName]['alias'] = $alias;
 481
 482             // set name = fieldName, unless already set
 483             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['name'])) {
 484               $this->_columns[$tableName][$fieldGrp][$fieldName]['name'] = $name;
 485             }
 486
 487             // set dbAlias = alias.name, unless already set
 488             if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'])) {
 489               $this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'] = $alias . '.' . $this->_columns[$tableName][$fieldGrp][$fieldName]['name'];
 490             }
 491
 492             if (CRM_Utils_Array::value('type', $this->_columns[$tableName][$fieldGrp][$fieldName]) &&
 493               !isset($this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'])
 494             ) {
 495               if (in_array($this->_columns[$tableName][$fieldGrp][$fieldName]['type'],
 496                   array(CRM_Utils_Type::T_MONEY, CRM_Utils_Type::T_FLOAT)
 497                 )) {
 498                 $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
 499               }
 500               elseif (in_array($this->_columns[$tableName][$fieldGrp][$fieldName]['type'],
 501                   array(CRM_Utils_Type::T_INT)
 502                 )) {
 503                 $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
 504               }
 505             }
 506           }
 507         }
 508       }
 509
 510       // copy filters to a separate handy variable
 511       if (array_key_exists('filters', $table)) {
 512         $this->_filters[$tableName] = $this->_columns[$tableName]['filters'];
 513       }
 514
 515       if (array_key_exists('group_bys', $table)) {
 516         $groupBys[$tableName] = $this->_columns[$tableName]['group_bys'];
 517       }
 518
 519       if (array_key_exists('fields', $table)) {
 520         $reportFields[$tableName] = $this->_columns[$tableName]['fields'];
 521       }
 522     }
 523
 524     if ($this->_force) {
 525       $this->setDefaultValues(FALSE);
 526     }
 527
 528     CRM_Report_Utils_Get::processFilter($this->_filters, $this->_defaults);
 529     CRM_Report_Utils_Get::processGroupBy($groupBys, $this->_defaults);
 530     CRM_Report_Utils_Get::processFields($reportFields, $this->_defaults);
 531     CRM_Report_Utils_Get::processChart($this->_defaults);
 532
 533     if ($this->_force) {
 534       $this->_formValues = $this->_defaults;
 535       $this->postProcess();
 536     }
 537   }
 538
 539   function setDefaultValues($freeze = TRUE) {
 540     $freezeGroup = array();
 541
 542     // FIXME: generalizing form field naming conventions would reduce
 543     // lots of lines below.
 544     foreach ($this->_columns as $tableName => $table) {
 545       if (array_key_exists('fields', $table)) {
 546         foreach ($table['fields'] as $fieldName => $field) {
 547           if (!array_key_exists('no_display', $field)) {
 548             if (isset($field['required'])) {
 549               // set default
 550               $this->_defaults['fields'][$fieldName] = 1;
 551
 552               if ($freeze) {
 553                 // find element object, so that we could use quickform's freeze method
 554                 // for required elements
 555                 $obj = $this->getElementFromGroup("fields", $fieldName);
 556                 if ($obj) {
 557                   $freezeGroup[] = $obj;
 558                 }
 559               }
 560             }
 561             elseif (isset($field['default'])) {
 562               $this->_defaults['fields'][$fieldName] = $field['default'];
 563             }
 564           }
 565         }
 566       }
 567
 568       if (array_key_exists('group_bys', $table)) {
 569         foreach ($table['group_bys'] as $fieldName => $field) {
 570           if (isset($field['default'])) {
 571             if (CRM_Utils_Array::value('frequency', $field)) {
 572               $this->_defaults['group_bys_freq'][$fieldName] = 'MONTH';
 573             }
 574             $this->_defaults['group_bys'][$fieldName] = $field['default'];
 575           }
 576         }
 577       }
 578       if (array_key_exists('filters', $table)) {
 579         foreach ($table['filters'] as $fieldName => $field) {
 580           if (isset($field['default'])) {
 581             if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 582               $this->_defaults["{$fieldName}_relative"] = $field['default'];
 583             }
 584             else {
 585               $this->_defaults["{$fieldName}_value"] = $field['default'];
 586             }
 587           }
 588           //assign default value as "in" for multiselect
 589           //operator, To freeze the select element
 590           if (CRM_Utils_Array::value('operatorType', $field) == CRM_Report_FORM::OP_MULTISELECT) {
 591             $this->_defaults["{$fieldName}_op"] = 'in';
 592           }
 593           elseif (CRM_Utils_Array::value('operatorType', $field) == CRM_Report_FORM::OP_MULTISELECT_SEPARATOR) {
 594             $this->_defaults["{$fieldName}_op"] = 'mhas';
 595           }
 596           elseif ($op = CRM_Utils_Array::value('default_op', $field)) {
 597             $this->_defaults["{$fieldName}_op"] = $op;
 598           }
 599         }
 600       }
 601
 602       if (
 603         array_key_exists('order_bys', $table) &&
 604         is_array($table['order_bys'])
 605       ) {
 606         if (!array_key_exists('order_bys', $this->_defaults)) {
 607           $this->_defaults['order_bys'] = array();
 608         }
 609         foreach ($table['order_bys'] as $fieldName => $field) {
 610           if (
 611             CRM_Utils_Array::value('default', $field) ||
 612             CRM_Utils_Array::value('default_order', $field) ||
 613             CRM_Utils_Array::value('default_is_section', $field) ||
 614             CRM_Utils_Array::value('default_weight', $field)
 615           ) {
 616             $order_by = array(
 617               'column' => $fieldName,
 618               'order' => CRM_Utils_Array::value('default_order', $field, 'ASC'),
 619               'section' => CRM_Utils_Array::value('default_is_section', $field, 0),
 620             );
 621
 622             if (CRM_Utils_Array::value('default_weight', $field)) {
 623               $this->_defaults['order_bys'][(int) $field['default_weight']] = $order_by;
 624             }
 625             else {
 626               array_unshift($this->_defaults['order_bys'], $order_by);
 627             }
 628           }
 629         }
 630       }
 631
 632       foreach ($this->_options as $fieldName => $field) {
 633         if (isset($field['default'])) {
 634           $this->_defaults['options'][$fieldName] = $field['default'];
 635         }
 636       }
 637     }
 638
 639     if (!empty($this->_submitValues)) {
 640       $this->preProcessOrderBy($this->_submitValues);
 641     }
 642     else {
 643       $this->preProcessOrderBy($this->_defaults);
 644     }
 645
 646     // lets finish freezing task here itself
 647     if (!empty($freezeGroup)) {
 648       foreach ($freezeGroup as $elem) {
 649         $elem->freeze();
 650       }
 651     }
 652
 653     if ($this->_formValues) {
 654       $this->_defaults = array_merge($this->_defaults, $this->_formValues);
 655     }
 656
 657     if ($this->_instanceValues) {
 658       $this->_defaults = array_merge($this->_defaults, $this->_instanceValues);
 659     }
 660
 661     CRM_Report_Form_Instance::setDefaultValues($this, $this->_defaults);
 662
 663     return $this->_defaults;
 664   }
 665
 666   function getElementFromGroup($group, $grpFieldName) {
 667     $eleObj = $this->getElement($group);
 668     foreach ($eleObj->_elements as $index => $obj) {
 669       if ($grpFieldName == $obj->_attributes['name']) {
 670         return $obj;
 671       }
 672     }
 673     return FALSE;
 674   }
 675
 676   function addColumns() {
 677     $options = array();
 678     $colGroups = NULL;
 679     foreach ($this->_columns as $tableName => $table) {
 680       if (array_key_exists('fields', $table)) {
 681         foreach ($table['fields'] as $fieldName => $field) {
 682           if (!array_key_exists('no_display', $field)) {
 683             if (isset($field['grouping'])) {
 684               $tableName = $field['grouping'];
 685             }
 686             elseif (isset($table['grouping'])) {
 687               $tableName = $table['grouping'];
 688             }
 689             $colGroups[$tableName]['fields'][$fieldName] = CRM_Utils_Array::value('title', $field);
 690
 691             if (isset($table['group_title'])) {
 692               $colGroups[$tableName]['group_title'] = $table['group_title'];
 693             }
 694
 695             $options[$fieldName] = CRM_Utils_Array::value('title', $field);
 696           }
 697         }
 698       }
 699     }
 700
 701     $this->addCheckBox("fields", ts('Select Columns'), $options, NULL,
 702       NULL, NULL, NULL, $this->_fourColumnAttribute, TRUE
 703     );
 704     $this->assign('colGroups', $colGroups);
 705   }
 706
 707   function addFilters() {
 708     $options = $filters = array();
 709     $count = 1;
 710     foreach ($this->_filters as $table => $attributes) {
 711       foreach ($attributes as $fieldName => $field) {
 712         // get ready with option value pair
 713         $operations = self::getOperationPair(
 714           CRM_Utils_Array::value('operatorType', $field),
 715           $fieldName
 716         );
 717
 718         $filters[$table][$fieldName] = $field;
 719
 720         switch (CRM_Utils_Array::value('operatorType', $field)) {
 721           case CRM_Report_Form::OP_MONTH:
 722             if (!array_key_exists('options', $field) || !is_array($field['options']) || empty($field['options'])) {
 723               // If there's no option list for this filter, define one.
 724               $field['options'] = array(
 725                 1 => ts('January'),
 726                 2 => ts('February'),
 727                 3 => ts('March'),
 728                 4 => ts('April'),
 729                 5 => ts('May'),
 730                 6 => ts('June'),
 731                 7 => ts('July'),
 732                 8 => ts('August'),
 733                 9 => ts('September'),
 734                 10 => ts('October'),
 735                 11 => ts('November'),
 736                 12 => ts('December'),
 737               );
 738               // Add this option list to this column _columns. This is
 739               // required so that filter statistics show properly.
 740               $this->_columns[$table]['filters'][$fieldName]['options'] = $field['options'];
 741             }
 742           case CRM_Report_FORM::OP_MULTISELECT:
 743           case CRM_Report_FORM::OP_MULTISELECT_SEPARATOR:
 744             // assume a multi-select field
 745             if (!empty($field['options'])) {
 746               $element = $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
 747               if (count($operations) <= 1) {
 748                 $element->freeze();
 749               }
 750               $select = $this->addElement('select', "{$fieldName}_value", NULL,
 751                         $field['options'], array(
 752                           'size' => 4,
 753                           'style' => 'min-width:250px',
 754                         )
 755               );
 756               $select->setMultiple(TRUE);
 757             }
 758             break;
 759
 760           case CRM_Report_FORM::OP_SELECT:
 761             // assume a select field
 762             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
 763             $this->addElement('select', "{$fieldName}_value", NULL, $field['options']);
 764             break;
 765
 766           case CRM_Report_FORM::OP_DATE:
 767             // build datetime fields
 768             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count);
 769             $count++;
 770             break;
 771
 772           case CRM_Report_FORM::OP_DATETIME:
 773             // build datetime fields
 774             CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', 'From:', FALSE, TRUE, 'searchDate', true);
 775             $count++;
 776             break;
 777
 778           case CRM_Report_FORM::OP_INT:
 779           case CRM_Report_FORM::OP_FLOAT:
 780             // and a min value input box
 781             $this->add('text', "{$fieldName}_min", ts('Min'));
 782             // and a max value input box
 783             $this->add('text', "{$fieldName}_max", ts('Max'));
 784           default:
 785             // default type is string
 786             $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations,
 787               array('onchange' => "return showHideMaxMinVal( '$fieldName', this.value );")
 788             );
 789             // we need text box for value input
 790             $this->add('text', "{$fieldName}_value", NULL);
 791             break;
 792         }
 793       }
 794     }
 795     $this->assign('filters', $filters);
 796   }
 797
 798   function addOptions() {
 799     if (!empty($this->_options)) {
 800       // FIXME: For now lets build all elements as checkboxes.
 801       // Once we clear with the format we can build elements based on type
 802
 803       $options = array();
 804       foreach ($this->_options as $fieldName => $field) {
 805         if ($field['type'] == 'select') {
 806           $this->addElement('select', "{$fieldName}", $field['title'], $field['options']);
 807         }
 808         else {
 809           $options[$field['title']] = $fieldName;
 810         }
 811       }
 812
 813       $this->addCheckBox("options", $field['title'],
 814         $options, NULL,
 815         NULL, NULL, NULL, $this->_fourColumnAttribute
 816       );
 817     }
 818   }
 819
 820   function addChartOptions() {
 821     if (!empty($this->_charts)) {
 822       $this->addElement('select', "charts", ts('Chart'), $this->_charts, array('onchange' => 'disablePrintPDFButtons(this.value);'));
 823       $this->assign('charts', $this->_charts);
 824       $this->addElement('submit', $this->_chartButtonName, ts('View'));
 825     }
 826   }
 827
 828   function addGroupBys() {
 829     $options = $freqElements = array();
 830
 831     foreach ($this->_columns as $tableName => $table) {
 832       if (array_key_exists('group_bys', $table)) {
 833         foreach ($table['group_bys'] as $fieldName => $field) {
 834           if (!empty($field)) {
 835             $options[$field['title']] = $fieldName;
 836             if (CRM_Utils_Array::value('frequency', $field)) {
 837               $freqElements[$field['title']] = $fieldName;
 838             }
 839           }
 840         }
 841       }
 842     }
 843     $this->addCheckBox("group_bys", ts('Group by columns'), $options, NULL,
 844       NULL, NULL, NULL, $this->_fourColumnAttribute
 845     );
 846     $this->assign('groupByElements', $options);
 847
 848     foreach ($freqElements as $name) {
 849       $this->addElement('select', "group_bys_freq[$name]",
 850         ts('Frequency'), $this->_groupByDateFreq
 851       );
 852     }
 853   }
 854
 855   function addOrderBys() {
 856     $options = array();
 857     foreach ($this->_columns as $tableName => $table) {
 858
 859       // Report developer may define any column to order by; include these as order-by options
 860       if (array_key_exists('order_bys', $table)) {
 861         foreach ($table['order_bys'] as $fieldName => $field) {
 862           if (!empty($field)) {
 863             $options[$fieldName] = $field['title'];
 864           }
 865         }
 866       }
 867
 868       /* Add searchable custom fields as order-by options, if so requested
 869        * (These are already indexed, so allowing to order on them is cheap.)
 870        */
 871
 872
 873       if ($this->_autoIncludeIndexedFieldsAsOrderBys && array_key_exists('extends', $table) && !empty($table['extends'])) {
 874         foreach ($table['fields'] as $fieldName => $field) {
 875           if (!array_key_exists('no_display', $field)) {
 876             $options[$fieldName] = $field['title'];
 877           }
 878         }
 879       }
 880     }
 881
 882     asort($options);
 883
 884     $this->assign('orderByOptions', $options);
 885
 886     if (!empty($options)) {
 887       $options = array(
 888         '-' => ' - none - ') + $options;
 889       for ($i = 1; $i <= 5; $i++) {
 890         $this->addElement('select', "order_bys[{$i}][column]", ts('Order by Column'), $options);
 891         $this->addElement('select', "order_bys[{$i}][order]", ts('Order by Order'), array('ASC' => 'Ascending', 'DESC' => 'Descending'));
 892         $this->addElement('checkbox', "order_bys[{$i}][section]", ts('Order by Section'), FALSE, array('id' => "order_by_section_$i"));
 893       }
 894     }
 895   }
 896
 897   function buildInstanceAndButtons() {
 898     CRM_Report_Form_Instance::buildForm($this);
 899
 900     $label = $this->_id ? ts('Update Report') : ts('Create Report');
 901
 902     $this->addElement('submit', $this->_instanceButtonName, $label);
 903     $this->addElement('submit', $this->_printButtonName, ts('Print Report'));
 904     $this->addElement('submit', $this->_pdfButtonName, ts('PDF'));
 905
 906     if ($this->_id) {
 907       $this->addElement('submit', $this->_createNewButtonName, ts('Save a Copy') . '...');
 908     }
 909     if ($this->_instanceForm) {
 910       $this->assign('instanceForm', TRUE);
 911     }
 912
 913     $label = $this->_id ? ts('Print Report') : ts('Print Preview');
 914     $this->addElement('submit', $this->_printButtonName, $label);
 915
 916     $label = $this->_id ? ts('PDF') : ts('Preview PDF');
 917     $this->addElement('submit', $this->_pdfButtonName, $label);
 918
 919     $label = $this->_id ? ts('Export to CSV') : ts('Preview CSV');
 920
 921     if ($this->_csvSupported) {
 922       $this->addElement('submit', $this->_csvButtonName, $label);
 923     }
 924
 925     if (CRM_Core_Permission::check('administer Reports') && $this->_add2groupSupported) {
 926       $this->addElement('select', 'groups', ts('Group'),
 927         array('' => ts('- select group -')) + CRM_Core_PseudoConstant::staticGroup()
 928       );
 929       $this->assign('group', TRUE);
 930     }
 931
 932     $label = ts('Add these Contacts to Group');
 933     $this->addElement('submit', $this->_groupButtonName, $label, array('onclick' => 'return checkGroup();'));
 934
 935     $this->addChartOptions();
 936     $this->addButtons(array(
 937         array(
 938           'type' => 'submit',
 939           'name' => ts('Preview Report'),
 940           'isDefault' => TRUE,
 941         ),
 942       )
 943     );
 944   }
 945
 946   function buildQuickForm() {
 947     $this->addColumns();
 948
 949     $this->addFilters();
 950
 951     $this->addOptions();
 952
 953     $this->addGroupBys();
 954
 955     $this->addOrderBys();
 956
 957     $this->buildInstanceAndButtons();
 958
 959     //add form rule for report
 960     if (is_callable(array(
 961           $this, 'formRule'))) {
 962       $this->addFormRule(array(get_class($this), 'formRule'), $this);
 963     }
 964   }
 965
 966   // a formrule function to ensure that fields selected in group_by
 967   // (if any) should only be the ones present in display/select fields criteria;
 968   // note: works if and only if any custom field selected in group_by.
 969   function customDataFormRule($fields, $ignoreFields = array( )) {
 970     $errors = array();
 971     if (!empty($this->_customGroupExtends) && $this->_customGroupGroupBy && !empty($fields['group_bys'])) {
 972       foreach ($this->_columns as $tableName => $table) {
 973         if ((substr($tableName, 0, 13) == 'civicrm_value' || substr($tableName, 0, 12) == 'custom_value') && !empty($this->_columns[$tableName]['fields'])) {
 974           foreach ($this->_columns[$tableName]['fields'] as $fieldName => $field) {
 975             if (array_key_exists($fieldName, $fields['group_bys']) &&
 976               !array_key_exists($fieldName, $fields['fields'])
 977             ) {
 978               $errors['fields'] = "Please make sure fields selected in 'Group by Columns' section are also selected in 'Display Columns' section.";
 979             }
 980             elseif (array_key_exists($fieldName, $fields['group_bys'])) {
 981               foreach ($fields['fields'] as $fld => $val) {
 982                 if (!array_key_exists($fld, $fields['group_bys']) && !in_array($fld, $ignoreFields)) {
 983                   $errors['fields'] = "Please ensure that fields selected in 'Display Columns' are also selected in 'Group by Columns' section.";
 984                 }
 985               }
 986             }
 987           }
 988         }
 989       }
 990     }
 991     return $errors;
 992   }
 993
 994   // Note: $fieldName param allows inheriting class to build operationPairs
 995   // specific to a field.
 996   static function getOperationPair($type = "string", $fieldName = NULL) {
 997     // FIXME: At some point we should move these key-val pairs
 998     // to option_group and option_value table.
 999
1000     switch ($type) {
1001       case CRM_Report_FORM::OP_INT:
1002       case CRM_Report_FORM::OP_FLOAT:
1003         return array('lte' => ts('Is less than or equal to'),
1004           'gte' => ts('Is greater than or equal to'),
1005           'bw' => ts('Is between'),
1006           'eq' => ts('Is equal to'),
1007           'lt' => ts('Is less than'),
1008           'gt' => ts('Is greater than'),
1009           'neq' => ts('Is not equal to'),
1010           'nbw' => ts('Is not between'),
1011           'nll' => ts('Is empty (Null)'),
1012           'nnll' => ts('Is not empty (Null)'),
1013         );
1014         break;
1015
1016       case CRM_Report_FORM::OP_SELECT:
1017         return array('eq' => ts('Is equal to'));
1018
1019       case CRM_Report_FORM::OP_MONTH:
1020       case CRM_Report_FORM::OP_MULTISELECT:
1021         return array('in' => ts('Is one of'),
1022           'notin' => ts('Is not one of'),
1023         );
1024         break;
1025
1026       case CRM_Report_FORM::OP_DATE:
1027         return array('nll' => ts('Is empty (Null)'),
1028           'nnll' => ts('Is not empty (Null)'),
1029         );
1030         break;
1031
1032       case CRM_Report_FORM::OP_MULTISELECT_SEPARATOR:
1033         // use this operator for the values, concatenated with separator. For e.g if
1034         // multiple options for a column is stored as ^A{val1}^A{val2}^A
1035         return array('mhas' => ts('Is one of'));
1036
1037       default:
1038         // type is string
1039         return array('has' => ts('Contains'),
1040           'sw' => ts('Starts with'),
1041           'ew' => ts('Ends with'),
1042           'nhas' => ts('Does not contain'),
1043           'eq' => ts('Is equal to'),
1044           'neq' => ts('Is not equal to'),
1045           'nll' => ts('Is empty (Null)'),
1046           'nnll' => ts('Is not empty (Null)'),
1047         );
1048     }
1049   }
1050
1051   function buildTagFilter() {
1052     $contactTags = CRM_Core_BAO_Tag::getTags();
1053     if (!empty($contactTags)) {
1054       $this->_columns['civicrm_tag'] = array(
1055         'dao' => 'CRM_Core_DAO_Tag',
1056         'filters' =>
1057         array(
1058           'tagid' =>
1059           array(
1060             'name' => 'tag_id',
1061             'title' => ts('Tag'),
1062             'tag' => TRUE,
1063             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1064             'options' => $contactTags,
1065           ),
1066         ),
1067       );
1068     }
1069   }
1070
1071   /*
1072    * Adds group filters to _columns (called from _Constuct
1073    */
1074   function buildGroupFilter() {
1075     $this->_columns['civicrm_group']['filters'] = array(
1076       'gid' =>
1077       array(
1078         'name' => 'group_id',
1079         'title' => ts('Group'),
1080         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1081         'group' => TRUE,
1082         'options' => CRM_Core_PseudoConstant::group(),
1083       ),
1084     );
1085     if (empty($this->_columns['civicrm_group']['dao'])) {
1086       $this->_columns['civicrm_group']['dao'] = 'CRM_Contact_DAO_GroupContact';
1087     }
1088     if (empty($this->_columns['civicrm_group']['alias'])) {
1089       $this->_columns['civicrm_group']['alias'] = 'cgroup';
1090     }
1091   }
1092
1093   static function getSQLOperator($operator = "like") {
1094     switch ($operator) {
1095       case 'eq':
1096         return '=';
1097
1098       case 'lt':
1099         return '<';
1100
1101       case 'lte':
1102         return '<=';
1103
1104       case 'gt':
1105         return '>';
1106
1107       case 'gte':
1108         return '>=';
1109
1110       case 'ne':
1111       case 'neq':
1112         return '!=';
1113
1114       case 'nhas':
1115         return 'NOT LIKE';
1116
1117       case 'in':
1118         return 'IN';
1119
1120       case 'notin':
1121         return 'NOT IN';
1122
1123       case 'nll':
1124         return 'IS NULL';
1125
1126       case 'nnll':
1127         return 'IS NOT NULL';
1128
1129       default:
1130         // type is string
1131         return 'LIKE';
1132     }
1133   }
1134
1135   function whereClause(&$field, $op,
1136     $value, $min, $max
1137   ) {
1138
1139     $type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
1140     $clause = NULL;
1141
1142     switch ($op) {
1143       case 'bw':
1144       case 'nbw':
1145         if (($min !== NULL && strlen($min) > 0) ||
1146           ($max !== NULL && strlen($max) > 0)
1147         ) {
1148           $min     = CRM_Utils_Type::escape($min, $type);
1149           $max     = CRM_Utils_Type::escape($max, $type);
1150           $clauses = array();
1151           if ($min) {
1152             if ($op == 'bw') {
1153               $clauses[] = "( {$field['dbAlias']} >= $min )";
1154             }
1155             else {
1156               $clauses[] = "( {$field['dbAlias']} < $min )";
1157             }
1158           }
1159           if ($max) {
1160             if ($op == 'bw') {
1161               $clauses[] = "( {$field['dbAlias']} <= $max )";
1162             }
1163             else {
1164               $clauses[] = "( {$field['dbAlias']} > $max )";
1165             }
1166           }
1167
1168           if (!empty($clauses)) {
1169             if ($op == 'bw') {
1170               $clause = implode(' AND ', $clauses);
1171             }
1172             else {
1173               $clause = implode(' OR ', $clauses);
1174             }
1175           }
1176         }
1177         break;
1178
1179       case 'has':
1180       case 'nhas':
1181         if ($value !== NULL && strlen($value) > 0) {
1182           $value = CRM_Utils_Type::escape($value, $type);
1183           if (strpos($value, '%') === FALSE) {
1184             $value = "'%{$value}%'";
1185           }
1186           else {
1187             $value = "'{$value}'";
1188           }
1189           $sqlOP = self::getSQLOperator($op);
1190           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1191         }
1192         break;
1193
1194       case 'in':
1195       case 'notin':
1196         if ($value !== NULL && is_array($value) && count($value) > 0) {
1197           $sqlOP = self::getSQLOperator($op);
1198           if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
1199             //cycle through selections and esacape values
1200             foreach ($value as $key => $selection) {
1201               $value[$key] = CRM_Utils_Type::escape($selection, $type);
1202             }
1203             $clause = "( {$field['dbAlias']} $sqlOP ( '" . implode("' , '", $value) . "') )";
1204           }
1205           else {
1206             // for numerical values
1207             $clause = "{$field['dbAlias']} $sqlOP (" . implode(', ', $value) . ")";
1208           }
1209           if ($op == 'notin') {
1210             $clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
1211           }
1212           else {
1213             $clause = "( " . $clause . " )";
1214           }
1215         }
1216         break;
1217
1218       case 'mhas':
1219         // mhas == multiple has
1220         if ($value !== NULL && count($value) > 0) {
1221           $sqlOP = self::getSQLOperator($op);
1222           $clause = "{$field['dbAlias']} REGEXP '[[:<:]]" . implode('|', $value) . "[[:>:]]'";
1223         }
1224         break;
1225
1226       case 'sw':
1227       case 'ew':
1228         if ($value !== NULL && strlen($value) > 0) {
1229           $value = CRM_Utils_Type::escape($value, $type);
1230           if (strpos($value, '%') === FALSE) {
1231             if ($op == 'sw') {
1232               $value = "'{$value}%'";
1233             }
1234             else {
1235               $value = "'%{$value}'";
1236             }
1237           }
1238           else {
1239             $value = "'{$value}'";
1240           }
1241           $sqlOP = self::getSQLOperator($op);
1242           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1243         }
1244         break;
1245
1246       case 'nll':
1247       case 'nnll':
1248         $sqlOP = self::getSQLOperator($op);
1249         $clause = "( {$field['dbAlias']} $sqlOP )";
1250         break;
1251
1252       default:
1253         if ($value !== NULL && strlen($value) > 0) {
1254           if (isset($field['clause'])) {
1255             // FIXME: we not doing escape here. Better solution is to use two
1256             // different types - data-type and filter-type
1257             eval("\$clause = \"{$field['clause']}\";");
1258           }
1259           else {
1260             $value = CRM_Utils_Type::escape($value, $type);
1261             $sqlOP = self::getSQLOperator($op);
1262             if ($field['type'] == CRM_Utils_Type::T_STRING) {
1263               $value = "'{$value}'";
1264             }
1265             $clause = "( {$field['dbAlias']} $sqlOP $value )";
1266           }
1267         }
1268         break;
1269     }
1270
1271     if (CRM_Utils_Array::value('group', $field) && $clause) {
1272       $clause = $this->whereGroupClause($field, $value, $op);
1273     }
1274     elseif (CRM_Utils_Array::value('tag', $field) && $clause) {
1275       // not using left join in query because if any contact
1276       // belongs to more than one tag, results duplicate
1277       // entries.
1278       $clause = $this->whereTagClause($field, $value, $op);
1279     }
1280
1281     return $clause;
1282   }
1283
1284   function dateClause($fieldName,
1285     $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
1286   ) {
1287     $clauses = array();
1288     if (in_array($relative, array_keys(self::getOperationPair(CRM_Report_FORM::OP_DATE)))) {
1289       $sqlOP = self::getSQLOperator($relative);
1290       return "( {$fieldName} {$sqlOP} )";
1291     }
1292
1293     list($from, $to) = self::getFromTo($relative, $from, $to, $fromTime, $toTime);
1294
1295     if ($from) {
1296       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
1297       $clauses[] = "( {$fieldName} >= $from )";
1298     }
1299
1300     if ($to) {
1301       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
1302       $clauses[] = "( {$fieldName} <= {$to} )";
1303     }
1304
1305     if (!empty($clauses)) {
1306       return implode(' AND ', $clauses);
1307     }
1308
1309     return NULL;
1310   }
1311
1312   static function dateDisplay($relative, $from, $to) {
1313     list($from, $to) = self::getFromTo($relative, $from, $to);
1314
1315     if ($from) {
1316       $clauses[] = CRM_Utils_Date::customFormat($from, NULL, array('m', 'M'));
1317     }
1318     else {
1319       $clauses[] = 'Past';
1320     }
1321
1322     if ($to) {
1323       $clauses[] = CRM_Utils_Date::customFormat($to, NULL, array('m', 'M'));
1324     }
1325     else {
1326       $clauses[] = 'Today';
1327     }
1328
1329     if (!empty($clauses)) {
1330       return implode(' - ', $clauses);
1331     }
1332
1333     return NULL;
1334   }
1335
1336   static function getFromTo($relative, $from, $to, $fromtime = NULL, $totime = NULL) {
1337     if (empty($totime)) {
1338       $totime = '235959';
1339     }
1340     //FIX ME not working for relative
1341     if ($relative) {
1342       list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
1343       $dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
1344       $from = substr($dateRange['from'], 0, 8);
1345       //Take only Date Part, Sometime Time part is also present in 'to'
1346       $to = substr($dateRange['to'], 0, 8);
1347     }
1348     $from = CRM_Utils_Date::processDate($from, $fromtime);
1349     $to = CRM_Utils_Date::processDate($to, $totime);
1350     return array($from, $to);
1351   }
1352
1353   function alterDisplay(&$rows) {
1354     // custom code to alter rows
1355   }
1356
1357   function alterCustomDataDisplay(&$rows) {
1358     // custom code to alter rows having custom values
1359     if (empty($this->_customGroupExtends)) {
1360       return;
1361     }
1362
1363     $customFieldIds = array();
1364     foreach ($this->_params['fields'] as $fieldAlias => $value) {
1365       if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
1366         $customFieldIds[$fieldAlias] = $fieldId;
1367       }
1368     }
1369     if (empty($customFieldIds)) {
1370       return;
1371     }
1372
1373     $customFields = $fieldValueMap = array();
1374     $customFieldCols = array('column_name', 'data_type', 'html_type', 'option_group_id', 'id');
1375
1376     // skip for type date and ContactReference since date format is already handled
1377     $query = "
1378 SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label
1379 FROM  civicrm_custom_field cf
1380 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
1381 LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id
1382 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
1383       cg.is_active = 1 AND
1384       cf.is_active = 1 AND
1385       cf.is_searchable = 1 AND
1386       cf.data_type   NOT IN ('ContactReference', 'Date') AND
1387       cf.id IN (" . implode(",", $customFieldIds) . ")";
1388
1389     $dao = CRM_Core_DAO::executeQuery($query);
1390     while ($dao->fetch()) {
1391       foreach ($customFieldCols as $key) {
1392         $customFields[$dao->table_name . '_custom_' . $dao->id][$key] = $dao->$key;
1393       }
1394       if ($dao->option_group_id) {
1395         $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label;
1396       }
1397     }
1398     $dao->free();
1399
1400     $entryFound = FALSE;
1401     foreach ($rows as $rowNum => $row) {
1402       foreach ($row as $tableCol => $val) {
1403         if (array_key_exists($tableCol, $customFields)) {
1404           $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap);
1405           $entryFound = TRUE;
1406         }
1407       }
1408
1409       // skip looking further in rows, if first row itself doesn't
1410       // have the column we need
1411       if (!$entryFound) {
1412         break;
1413       }
1414     }
1415   }
1416
1417   function formatCustomValues($value, $customField, $fieldValueMap) {
1418     if (CRM_Utils_System::isNull($value)) {
1419       return;
1420     }
1421
1422     $htmlType = $customField['html_type'];
1423
1424     switch ($customField['data_type']) {
1425       case 'Boolean':
1426         if ($value == '1') {
1427           $retValue = ts('Yes');
1428         }
1429         else {
1430           $retValue = ts('No');
1431         }
1432         break;
1433
1434       case 'Link':
1435         $retValue = CRM_Utils_System::formatWikiURL($value);
1436         break;
1437
1438       case 'File':
1439         $retValue = $value;
1440         break;
1441
1442       case 'Memo':
1443         $retValue = $value;
1444         break;
1445
1446       case 'Float':
1447         if ($htmlType == 'Text') {
1448           $retValue = (float)$value;
1449           break;
1450         }
1451       case 'Money':
1452         if ($htmlType == 'Text') {
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462           $retValue = CRM_Utils_Money::format($value, NULL, '%a');
1463           break;
1464         }
1465       case 'String':
1466       case 'Int':
1467         if (in_array($htmlType, array(
1468               'Text', 'TextArea'))) {
1469           $retValue = $value;
1470           break;
1471         }
1472       case 'StateProvince':
1473       case 'Country':
1474
1475         switch ($htmlType) {
1476           case 'Multi-Select Country':
1477             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
1478             $customData = array();
1479             foreach ($value as $val) {
1480               if ($val) {
1481                 $customData[] = CRM_Core_PseudoConstant::country($val, FALSE);
1482               }
1483             }
1484             $retValue = implode(', ', $customData);
1485             break;
1486
1487           case 'Select Country':
1488             $retValue = CRM_Core_PseudoConstant::country($value, FALSE);
1489             break;
1490
1491           case 'Select State/Province':
1492             $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
1493             break;
1494
1495           case 'Multi-Select State/Province':
1496             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
1497             $customData = array();
1498             foreach ($value as $val) {
1499               if ($val) {
1500                 $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE);
1501               }
1502             }
1503             $retValue = implode(', ', $customData);
1504             break;
1505
1506           case 'Select':
1507           case 'Radio':
1508           case 'Autocomplete-Select':
1509             $retValue = $fieldValueMap[$customField['option_group_id']][$value];
1510             break;
1511
1512           case 'CheckBox':
1513           case 'AdvMulti-Select':
1514           case 'Multi-Select':
1515             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
1516             $customData = array();
1517             foreach ($value as $val) {
1518               if ($val) {
1519                 $customData[] = $fieldValueMap[$customField['option_group_id']][$val];
1520               }
1521             }
1522             $retValue = implode(', ', $customData);
1523             break;
1524
1525           default:
1526             $retValue = $value;
1527         }
1528         break;
1529
1530       default:
1531         $retValue = $value;
1532     }
1533
1534     return $retValue;
1535   }
1536
1537   function removeDuplicates(&$rows) {
1538     if (empty($this->_noRepeats)) {
1539       return;
1540     }
1541     $checkList = array();
1542
1543     foreach ($rows as $key => $list) {
1544       foreach ($list as $colName => $colVal) {
1545         if (array_key_exists($colName, $checkList) &&
1546           $checkList[$colName] == $colVal) {
1547           $rows[$key][$colName] = "";
1548         }
1549         if (in_array($colName, $this->_noRepeats)) {
1550           $checkList[$colName] = $colVal;
1551         }
1552       }
1553     }
1554   }
1555
1556   function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
1557     foreach ($row as $colName => $colVal) {
1558       if (in_array($colName, $fields)) {
1559         $row[$colName] = $row[$colName];
1560       }
1561       elseif (isset($this->_columnHeaders[$colName])) {
1562         if ($subtotal) {
1563           $row[$colName] = "Subtotal";
1564           $subtotal = FALSE;
1565         }
1566         else {
1567           unset($row[$colName]);
1568         }
1569       }
1570     }
1571   }
1572
1573   function grandTotal(&$rows) {
1574     if (!$this->_rollup || ($this->_rollup == '') ||
1575       ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
1576     ) {
1577       return FALSE;
1578     }
1579     $lastRow = array_pop($rows);
1580
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(self::getOperationPair(CRM_Report_FORM::OP_DATE))
2266               )) {
2267               $pair = self::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 = self::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       $offset = CRM_Utils_Type::escape($offset, 'Int');
2491       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
2492
2493       $this->_limit = " LIMIT $offset, $rowCount";
2494       return array($offset, $rowCount);
2495     }
2496   }
2497
2498   function setPager($rowCount = self::ROW_COUNT_LIMIT) {
2499     if ($this->_limit && ($this->_limit != '')) {
2500       $sql              = "SELECT FOUND_ROWS();";
2501       $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
2502       $params           = array(
2503         'total' => $this->_rowsFound,
2504         'rowCount' => $rowCount,
2505         'status' => ts('Records') . ' %%StatusMessage%%',
2506         'buttonBottom' => 'PagerBottomButton',
2507         'buttonTop' => 'PagerTopButton',
2508         'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
2509       );
2510
2511       $pager = new CRM_Utils_Pager($params);
2512       $this->assign_by_ref('pager', $pager);
2513     }
2514   }
2515
2516   function whereGroupClause($field, $value, $op) {
2517
2518     $smartGroupQuery = "";
2519
2520     $group = new CRM_Contact_DAO_Group();
2521     $group->is_active = 1;
2522     $group->find();
2523     $smartGroups = array();
2524     while ($group->fetch()) {
2525       if (in_array($group->id, $this->_params['gid_value']) && $group->saved_search_id) {
2526         $smartGroups[] = $group->id;
2527       }
2528     }
2529
2530     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
2531
2532     $smartGroupQuery = '';
2533     if (!empty($smartGroups)) {
2534       $smartGroups = implode(',', $smartGroups);
2535       $smartGroupQuery = " UNION DISTINCT
2536                   SELECT DISTINCT smartgroup_contact.contact_id
2537                   FROM civicrm_group_contact_cache smartgroup_contact
2538                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
2539     }
2540
2541     $sqlOp = self::getSQLOperator($op);
2542     if (!is_array($value)) {
2543       $value = array($value);
2544     }
2545     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
2546
2547     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
2548                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
2549                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
2550                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
2551                           {$smartGroupQuery} ) ";
2552   }
2553
2554   function whereTagClause($field, $value, $op) {
2555     // not using left join in query because if any contact
2556     // belongs to more than one tag, results duplicate
2557     // entries.
2558     $sqlOp = self::getSQLOperator($op);
2559     if (!is_array($value)) {
2560       $value = array($value);
2561     }
2562     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
2563
2564     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
2565                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
2566                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
2567                           WHERE entity_table = 'civicrm_contact' AND {$clause} ) ";
2568   }
2569
2570   function buildACLClause($tableAlias = 'contact_a') {
2571     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
2572   }
2573
2574   function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
2575     if (empty($this->_customGroupExtends)) {
2576       return;
2577     }
2578     if (!is_array($this->_customGroupExtends)) {
2579       $this->_customGroupExtends = array($this->_customGroupExtends);
2580     }
2581     $customGroupWhere = '';
2582     if (!empty($permCustomGroupIds)) {
2583       $customGroupWhere = "cg.id IN (".implode(',' , $permCustomGroupIds).") AND";
2584     }
2585     $sql = "
2586 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
2587        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
2588 FROM   civicrm_custom_group cg
2589 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
2590 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
2591       {$customGroupWhere}
2592       cg.is_active = 1 AND
2593       cf.is_active = 1 AND
2594       cf.is_searchable = 1
2595 ORDER BY cg.weight, cf.weight";
2596     $customDAO = CRM_Core_DAO::executeQuery($sql);
2597
2598     $curTable = NULL;
2599     while ($customDAO->fetch()) {
2600       if ($customDAO->table_name != $curTable) {
2601         $curTable = $customDAO->table_name;
2602         $curFields = $curFilters = array();
2603
2604         // dummy dao object
2605         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
2606         $this->_columns[$curTable]['extends'] = $customDAO->extends;
2607         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
2608         $this->_columns[$curTable]['group_title'] = $customDAO->title;
2609
2610         foreach (array(
2611             'fields', 'filters', 'group_bys') as $colKey) {
2612           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
2613             $this->_columns[$curTable][$colKey] = array();
2614           }
2615         }
2616       }
2617       $fieldName = 'custom_' . $customDAO->cf_id;
2618
2619       if ($addFields) {
2620         // this makes aliasing work in favor
2621         $curFields[$fieldName] = array(
2622           'name' => $customDAO->column_name,
2623           'title' => $customDAO->label,
2624           'dataType' => $customDAO->data_type,
2625           'htmlType' => $customDAO->html_type,
2626         );
2627       }
2628       if ($this->_customGroupFilters) {
2629         // this makes aliasing work in favor
2630         $curFilters[$fieldName] = array(
2631           'name' => $customDAO->column_name,
2632           'title' => $customDAO->label,
2633           'dataType' => $customDAO->data_type,
2634           'htmlType' => $customDAO->html_type,
2635         );
2636       }
2637
2638       switch ($customDAO->data_type) {
2639         case 'Date':
2640           // filters
2641           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
2642           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
2643           // CRM-6946, show time part for datetime date fields
2644           if ($customDAO->time_format) {
2645             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
2646           }
2647           break;
2648
2649         case 'Boolean':
2650           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
2651           $curFilters[$fieldName]['options'] = array('' => ts('- select -'),
2652                                                1 => ts('Yes'),
2653                                                0 => ts('No'),
2654           );
2655           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
2656           break;
2657
2658         case 'Int':
2659           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
2660           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
2661           break;
2662
2663         case 'Money':
2664           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
2665           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
2666           break;
2667
2668         case 'Float':
2669           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
2670           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
2671           break;
2672
2673         case 'String':
2674           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2675
2676           if (!empty($customDAO->option_group_id)) {
2677             if (in_array($customDAO->html_type, array(
2678                   'Multi-Select', 'AdvMulti-Select', 'CheckBox'))) {
2679               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
2680             }
2681             else {
2682               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
2683             }
2684             if ($this->_customGroupFilters) {
2685               $curFilters[$fieldName]['options'] = array();
2686               $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')));
2687               while ($ogDAO->fetch()) {
2688                 $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label;
2689               }
2690             }
2691           }
2692           break;
2693
2694         case 'StateProvince':
2695           if (in_array($customDAO->html_type, array(
2696                 'Multi-Select State/Province'))) {
2697             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
2698           }
2699           else {
2700             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
2701           }
2702           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince();
2703           break;
2704
2705         case 'Country':
2706           if (in_array($customDAO->html_type, array(
2707                 'Multi-Select Country'))) {
2708             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
2709           }
2710           else {
2711             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
2712           }
2713           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country();
2714           break;
2715
2716         case 'ContactReference':
2717           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2718           $curFilters[$fieldName]['name'] = 'display_name';
2719           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
2720
2721           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2722           $curFields[$fieldName]['name'] = 'display_name';
2723           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
2724           break;
2725
2726         default:
2727           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2728           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2729       }
2730
2731       if (!array_key_exists('type', $curFields[$fieldName])) {
2732         $curFields[$fieldName]['type'] = $curFilters[$fieldName]['type'];
2733       }
2734
2735       if ($addFields) {
2736         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
2737       }
2738       if ($this->_customGroupFilters) {
2739         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
2740       }
2741       if ($this->_customGroupGroupBy) {
2742         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
2743       }
2744     }
2745   }
2746
2747   function customDataFrom() {
2748     if (empty($this->_customGroupExtends)) {
2749       return;
2750     }
2751     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
2752
2753     foreach ($this->_columns as $table => $prop) {
2754       if (substr($table, 0, 13) == 'civicrm_value' || substr($table, 0, 12) == 'custom_value') {
2755         $extendsTable = $mapper[$prop['extends']];
2756
2757         // check field is in params
2758         if (!$this->isFieldSelected($prop)) {
2759           continue;
2760         }
2761
2762         $this->_from .= "
2763 LEFT JOIN $table {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$this->_aliases[$extendsTable]}.id";
2764         // handle for ContactReference
2765         if (array_key_exists('fields', $prop)) {
2766           foreach ($prop['fields'] as $fieldName => $field) {
2767             if (CRM_Utils_Array::value('dataType', $field) == 'ContactReference') {
2768               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
2769               $this->_from .= "
2770 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
2771             }
2772           }
2773         }
2774       }
2775     }
2776   }
2777
2778   function isFieldSelected($prop) {
2779     if (empty($prop)) {
2780       return FALSE;
2781     }
2782
2783     if (!empty($this->_params['fields'])) {
2784       foreach (array_keys($prop['fields']) as $fieldAlias) {
2785         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
2786         if ($customFieldId) {
2787           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
2788             return TRUE;
2789           }
2790
2791           //might be survey response field.
2792           if (CRM_Utils_Array::value('survey_response', $this->_params['fields']) &&
2793             CRM_Utils_Array::value('isSurveyResponseField', $prop['fields'][$fieldAlias])
2794           ) {
2795             return TRUE;
2796           }
2797         }
2798       }
2799     }
2800
2801     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
2802       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
2803         if (array_key_exists($fieldAlias, $this->_params['group_bys']) && CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
2804           return TRUE;
2805         }
2806       }
2807     }
2808
2809     if (!empty($this->_params['order_bys'])) {
2810       foreach (array_keys($prop['fields']) as $fieldAlias) {
2811         foreach ($this->_params['order_bys'] as $orderBy) {
2812           if ($fieldAlias == $orderBy['column'] && CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
2813             return TRUE;
2814           }
2815         }
2816       }
2817     }
2818
2819     if (!empty($prop['filters']) && $this->_customGroupFilters) {
2820       foreach ($prop['filters'] as $fieldAlias => $val) {
2821         foreach (array(
2822             'value', 'min', 'max', 'relative', 'from', 'to') as $attach) {
2823           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
2824             (!empty($this->_params[$fieldAlias . '_' . $attach])
2825               || ($attach != 'relative' && $this->_params[$fieldAlias . '_' . $attach] == '0')
2826             )
2827           ){
2828             return TRUE;
2829           }
2830         }
2831         if (CRM_Utils_Array::value($fieldAlias . '_op', $this->_params) &&
2832           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
2833         ) {
2834           return TRUE;
2835         }
2836       }
2837     }
2838
2839     return FALSE;
2840   }
2841
2842   /**
2843    * Check for empty order_by configurations and remove them; also set
2844    * template to hide them.
2845    */
2846   function preProcessOrderBy(&$formValues) {
2847     // Object to show/hide form elements
2848     $_showHide = &new CRM_Core_ShowHideBlocks('', '');
2849
2850     $_showHide->addShow('optionField_1');
2851
2852     // Cycle through order_by options; skip any empty ones, and hide them as well
2853     $n = 1;
2854
2855     if (!empty($formValues['order_bys'])) {
2856       foreach ($formValues['order_bys'] as $order_by) {
2857         if ($order_by['column'] && $order_by['column'] != '-') {
2858           $_showHide->addShow('optionField_' . $n);
2859           $orderBys[$n] = $order_by;
2860           $n++;
2861         }
2862       }
2863     }
2864     for ($i = $n; $i <= 5; $i++) {
2865       if ($i > 1) {
2866         $_showHide->addHide('optionField_' . $i);
2867       }
2868     }
2869
2870     // overwrite order_by options with modified values
2871     if (!empty($orderBys)) {
2872       $formValues['order_bys'] = $orderBys;
2873     }
2874     else {
2875       $formValues['order_bys'] = array(1 => array('column' => '-'));
2876     }
2877
2878     // assign show/hide data to template
2879     $_showHide->addToTemplate();
2880   }
2881
2882   /**
2883    * Does table name have columns in SELECT clause?
2884    *
2885    * @param string $tableName  Name of table (index of $this->_columns array)
2886    *
2887    * @return bool
2888    */
2889   function isTableSelected($tableName) {
2890     return in_array($tableName, $this->selectedTables());
2891   }
2892
2893   /**
2894    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause
2895    * (building the array if it's unset)
2896    *
2897    * @return Array $this->_selectedTables
2898    */
2899   function selectedTables() {
2900     if (!$this->_selectedTables) {
2901       $orderByColumns = array();
2902       if (is_array($this->_params['order_bys'])) {
2903         foreach ($this->_params['order_bys'] as $orderBy) {
2904           $orderByColumns[] = $orderBy['column'];
2905         }
2906       }
2907
2908       foreach ($this->_columns as $tableName => $table) {
2909         if (array_key_exists('fields', $table)) {
2910           foreach ($table['fields'] as $fieldName => $field) {
2911             if (CRM_Utils_Array::value('required', $field) ||
2912               CRM_Utils_Array::value($fieldName, $this->_params['fields'])
2913             ) {
2914               $this->_selectedTables[] = $tableName;
2915               break;
2916             }
2917           }
2918         }
2919         if (array_key_exists('order_bys', $table)) {
2920           foreach ($table['order_bys'] as $orderByName => $orderBy) {
2921             if (in_array($orderByName, $orderByColumns)) {
2922               $this->_selectedTables[] = $tableName;
2923               break;
2924             }
2925           }
2926         }
2927         if (array_key_exists('filters', $table)) {
2928           foreach ($table['filters'] as $filterName => $filter) {
2929             if (CRM_Utils_Array::value("{$filterName}_value", $this->_params) ||
2930               CRM_Utils_Array::value("{$filterName}_op", $this->_params) == 'nll' ||
2931               CRM_Utils_Array::value("{$filterName}_op", $this->_params) == 'nnll'
2932             ) {
2933               $this->_selectedTables[] = $tableName;
2934               break;
2935             }
2936           }
2937         }
2938       }
2939     }
2940     return $this->_selectedTables;
2941   }
2942
2943   /*
2944    * function for adding address fields to construct function in reports
2945    * @param bool $groupBy Add GroupBy? Not appropriate for detail report
2946    * @param bool $orderBy Add GroupBy? Not appropriate for detail report
2947    * @return array address fields for construct clause
2948    */
2949   function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array(
2950       'country_id' => TRUE)) {
2951     $addressFields = array(
2952       'civicrm_address' =>
2953       array(
2954         'dao' => 'CRM_Core_DAO_Address',
2955         'fields' =>
2956         array(
2957           'name' =>
2958           array('title' => ts('Address Name'),
2959             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
2960           ),
2961           'street_address' =>
2962           array('title' => ts('Street Address'),
2963             'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
2964           ),
2965           'supplemental_address_1' =>
2966           array('title' => ts('Supplementary Address Field 1'),
2967             'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
2968           ),
2969           'supplemental_address_2' =>
2970           array('title' => ts('Supplementary Address Field 2'),
2971             'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
2972           ),
2973           'street_number' =>
2974           array(
2975             'name' => 'street_number',
2976             'title' => ts('Street Number'),
2977             'type' => 1,
2978             'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
2979           ),
2980           'street_name' =>
2981           array(
2982             'name' => 'street_name',
2983             'title' => ts('Street Name'),
2984             'type' => 1,
2985             'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
2986           ),
2987           'street_unit' =>
2988           array(
2989             'name' => 'street_unit',
2990             'title' => ts('Street Unit'),
2991             'type' => 1,
2992             'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
2993           ),
2994           'city' =>
2995           array('title' => ts('City'),
2996             'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
2997           ),
2998           'postal_code' =>
2999           array('title' => ts('Postal Code'),
3000             'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
3001           ),
3002           'county_id' =>
3003           array('title' => ts('County'),
3004             'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
3005           ),
3006           'state_province_id' =>
3007           array('title' => ts('State/Province'),
3008             'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
3009           ),
3010           'country_id' =>
3011           array('title' => ts('Country'),
3012             'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
3013           ),
3014         ),
3015         'grouping' => 'location-fields',
3016       ),
3017     );
3018
3019     if ($filters) {
3020       $addressFields['civicrm_address']['filters'] = array(
3021         'street_number' => array('title' => ts('Street Number'),
3022                          'type' => 1,
3023                          'name' => 'street_number',
3024         ),
3025         'street_name' => array('title' => ts('Street Name'),
3026                        'name' => 'street_name',
3027                        'operator' => 'like',
3028         ),
3029         'postal_code' => array('title' => ts('Postal Code'),
3030                        'type' => 1,
3031                        'name' => 'postal_code',
3032         ),
3033         'city' => array('title' => ts('City'),
3034                 'operator' => 'like',
3035                 'name' => 'city',
3036         ),
3037         'county_id' => array(
3038           'name' => 'county_id',
3039           'title' => ts('County'),
3040           'type' => CRM_Utils_Type::T_INT,
3041           'operatorType' =>
3042           CRM_Report_Form::OP_MULTISELECT,
3043           'options' =>
3044           CRM_Core_PseudoConstant::county(),
3045         ),
3046         'state_province_id' => array(
3047           'name' => 'state_province_id',
3048           'title' => ts('State/Province'),
3049           'type' => CRM_Utils_Type::T_INT,
3050           'operatorType' =>
3051           CRM_Report_Form::OP_MULTISELECT,
3052           'options' =>
3053           CRM_Core_PseudoConstant::stateProvince(),
3054         ),
3055         'country_id' => array(
3056           'name' => 'country_id',
3057           'title' => ts('Country'),
3058           'type' => CRM_Utils_Type::T_INT,
3059           'operatorType' =>
3060           CRM_Report_Form::OP_MULTISELECT,
3061           'options' =>
3062           CRM_Core_PseudoConstant::country(),
3063         ),
3064       );
3065     }
3066
3067     if ($orderBy) {
3068       $addressFields['civicrm_address']['order_bys'] = array('street_name' => array('title' => ts('Street Name')),
3069                                                        'street_number' => array('title' => 'Odd / Even Street Number'),
3070                                                        'street_address' => NULL,
3071                                                        'city' => NULL,
3072                                                        'postal_code' => NULL,
3073       );
3074     }
3075
3076     if ($groupBy) {
3077       $addressFields['civicrm_address']['group_bys'] = array(
3078         'street_address' => NULL,
3079         'city' => NULL,
3080         'postal_code' => NULL,
3081         'state_province_id' =>
3082         array('title' => ts('State/Province'),
3083         ),
3084         'country_id' =>
3085         array('title' => ts('Country'),
3086         ),
3087         'county_id' =>
3088         array('title' => ts('County'),
3089         ),
3090       );
3091     }
3092     return $addressFields;
3093   }
3094
3095   /*
3096    * Do AlterDisplay processing on Address Fields
3097    */
3098   function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $urltxt) {
3099     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
3100     $entryFound = FALSE;
3101     // handle country
3102     if (array_key_exists('civicrm_address_country_id', $row)) {
3103       if ($value = $row['civicrm_address_country_id']) {
3104         $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
3105         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
3106                "reset=1&force=1&{$criteriaQueryParams}&" .
3107                "country_id_op=in&country_id_value={$value}",
3108                $this->_absoluteUrl, $this->_id
3109         );
3110         $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
3111         $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
3112                                                              array(1 => $urltxt)
3113         );
3114       }
3115
3116       $entryFound = TRUE;
3117     }
3118     if (array_key_exists('civicrm_address_county_id', $row)) {
3119       if ($value = $row['civicrm_address_county_id']) {
3120         $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
3121         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
3122                "reset=1&force=1&{$criteriaQueryParams}&" .
3123                "county_id_op=in&county_id_value={$value}",
3124                $this->_absoluteUrl, $this->_id
3125         );
3126         $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
3127         $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
3128                                                             array(1 => $urltxt)
3129         );
3130       }
3131       $entryFound = TRUE;
3132     }
3133     // handle state province
3134     if (array_key_exists('civicrm_address_state_province_id', $row)) {
3135       if ($value = $row['civicrm_address_state_province_id']) {
3136         $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
3137
3138         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
3139                "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
3140                $this->_absoluteUrl, $this->_id
3141         );
3142         $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
3143         $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
3144                                                                     array(1 => $urltxt)
3145         );
3146       }
3147       $entryFound = TRUE;
3148     }
3149
3150     return $entryFound;
3151   }
3152
3153   /*
3154    *  Adjusts dates passed in to YEAR() for fiscal year.
3155    */
3156   function fiscalYearOffset($fieldName) {
3157     $config = CRM_Core_Config::singleton();
3158     $fy = $config->fiscalYearStart;
3159     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' || ($fy['d'] == 1 && $fy['M'] == 1)) {
3160       return "YEAR( $fieldName )";
3161     }
3162     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" . ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
3163   }
3164
3165   /*
3166    * Add Address into From Table if required
3167    */
3168   function addAddressFromClause() {
3169     // include address field if address column is to be included
3170     if ((isset($this->_addressField) &&
3171         $this->_addressField
3172       ) ||
3173       $this->isTableSelected('civicrm_address')
3174     ) {
3175       $this->_from .= "
3176                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
3177                            ON ({$this->_aliases['civicrm_contact']}.id =
3178                                {$this->_aliases['civicrm_address']}.contact_id) AND
3179                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
3180     }
3181   }
3182
3183   function add2group($groupID) {
3184     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
3185       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
3186       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
3187
3188       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
3189       $dao = CRM_Core_DAO::executeQuery($sql);
3190
3191       $contact_ids = array();
3192       // Add resulting contacts to group
3193       while ($dao->fetch()) {
3194         if ($dao->addtogroup_contact_id) {
3195           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
3196         }
3197       }
3198
3199       if ( !empty($contact_ids) ) {
3200         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
3201         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
3202       }
3203       else {
3204         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
3205       }
3206     }
3207   }
3208 }
3209