CRM/Report/Form.php

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