CRM/Report/Form.php

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