CRM/Report/Form.php

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