CRM/Report/Form.php

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