CRM/Report/Form.php

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