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