CRM/Report/Form.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.6                                                |
   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 $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 to ensure that fields selected in group_by
1422    * (if any) should only be the ones present in display/select fields criteria;
1423    * note: works if and only if any custom field selected in group_by.
1424    *
1425    * @param array $fields
1426    * @param array $ignoreFields
1427    *
1428    * @return array
1429    */
1430   public function customDataFormRule($fields, $ignoreFields = array()) {
1431     $errors = array();
1432     if (!empty($this->_customGroupExtends) && $this->_customGroupGroupBy &&
1433       !empty($fields['group_bys'])
1434     ) {
1435       foreach ($this->_columns as $tableName => $table) {
1436         if ((substr($tableName, 0, 13) == 'civicrm_value' ||
1437             substr($tableName, 0, 12) == 'custom_value') &&
1438           !empty($this->_columns[$tableName]['fields'])
1439         ) {
1440           foreach ($this->_columns[$tableName]['fields'] as $fieldName => $field) {
1441             if (array_key_exists($fieldName, $fields['group_bys']) &&
1442               !array_key_exists($fieldName, $fields['fields'])
1443             ) {
1444               $errors['fields'] = "Please make sure fields selected in 'Group by Columns' section are also selected in 'Display Columns' section.";
1445             }
1446             elseif (array_key_exists($fieldName, $fields['group_bys'])) {
1447               foreach ($fields['fields'] as $fld => $val) {
1448                 if (!array_key_exists($fld, $fields['group_bys']) &&
1449                   !in_array($fld, $ignoreFields)
1450                 ) {
1451                   $errors['fields'] = "Please ensure that fields selected in 'Display Columns' are also selected in 'Group by Columns' section.";
1452                 }
1453               }
1454             }
1455           }
1456         }
1457       }
1458     }
1459     return $errors;
1460   }
1461
1462   /**
1463    * Get operators to display on form.
1464    *
1465    * Note: $fieldName param allows inheriting class to build operationPairs specific to a field.
1466    *
1467    * @param string $type
1468    * @param string $fieldName
1469    *
1470    * @return array
1471    */
1472   public function getOperationPair($type = "string", $fieldName = NULL) {
1473     // FIXME: At some point we should move these key-val pairs
1474     // to option_group and option_value table.
1475     switch ($type) {
1476       case CRM_Report_Form::OP_INT:
1477       case CRM_Report_Form::OP_FLOAT:
1478
1479         $result = array(
1480           'lte' => ts('Is less than or equal to'),
1481           'gte' => ts('Is greater than or equal to'),
1482           'bw' => ts('Is between'),
1483           'eq' => ts('Is equal to'),
1484           'lt' => ts('Is less than'),
1485           'gt' => ts('Is greater than'),
1486           'neq' => ts('Is not equal to'),
1487           'nbw' => ts('Is not between'),
1488           'nll' => ts('Is empty (Null)'),
1489           'nnll' => ts('Is not empty (Null)'),
1490         );
1491         return $result;
1492
1493       case CRM_Report_Form::OP_SELECT:
1494         $result = array(
1495           'eq' => ts('Is equal to'),
1496         );
1497         return $result;
1498
1499       case CRM_Report_Form::OP_MONTH:
1500       case CRM_Report_Form::OP_MULTISELECT:
1501       case CRM_Report_Form::OP_ENTITYREF:
1502
1503         $result = array(
1504           'in' => ts('Is one of'),
1505           'notin' => ts('Is not one of'),
1506         );
1507         return $result;
1508
1509       case CRM_Report_Form::OP_DATE:
1510
1511         $result = array(
1512           'nll' => ts('Is empty (Null)'),
1513           'nnll' => ts('Is not empty (Null)'),
1514         );
1515         return $result;
1516
1517       case CRM_Report_Form::OP_MULTISELECT_SEPARATOR:
1518         // use this operator for the values, concatenated with separator. For e.g if
1519         // multiple options for a column is stored as ^A{val1}^A{val2}^A
1520         $result = array(
1521           'mhas' => ts('Is one of'),
1522           'mnot' => ts('Is not one of'),
1523         );
1524         return $result;
1525
1526       default:
1527         // type is string
1528         $result = array(
1529           'has' => ts('Contains'),
1530           'sw' => ts('Starts with'),
1531           'ew' => ts('Ends with'),
1532           'nhas' => ts('Does not contain'),
1533           'eq' => ts('Is equal to'),
1534           'neq' => ts('Is not equal to'),
1535           'nll' => ts('Is empty (Null)'),
1536           'nnll' => ts('Is not empty (Null)'),
1537         );
1538         return $result;
1539     }
1540   }
1541
1542   /**
1543    * Build the tag filter field to display on the filters tab.
1544    */
1545   public function buildTagFilter() {
1546     $contactTags = CRM_Core_BAO_Tag::getTags($this->_tagFilterTable);
1547     if (!empty($contactTags)) {
1548       $this->_columns['civicrm_tag'] = array(
1549         'dao' => 'CRM_Core_DAO_Tag',
1550         'filters' => array(
1551           'tagid' => array(
1552             'name' => 'tag_id',
1553             'title' => ts('Tag'),
1554             'tag' => TRUE,
1555             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1556             'options' => $contactTags,
1557           ),
1558         ),
1559       );
1560     }
1561   }
1562
1563   /**
1564    * Adds group filters to _columns (called from _Construct).
1565    */
1566   public function buildGroupFilter() {
1567     $this->_columns['civicrm_group']['filters'] = array(
1568       'gid' => array(
1569         'name' => 'group_id',
1570         'title' => ts('Group'),
1571         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1572         'group' => TRUE,
1573         'options' => CRM_Core_PseudoConstant::nestedGroup(),
1574       ),
1575     );
1576     if (empty($this->_columns['civicrm_group']['dao'])) {
1577       $this->_columns['civicrm_group']['dao'] = 'CRM_Contact_DAO_GroupContact';
1578     }
1579     if (empty($this->_columns['civicrm_group']['alias'])) {
1580       $this->_columns['civicrm_group']['alias'] = 'cgroup';
1581     }
1582   }
1583
1584   /**
1585    * Get SQL operator from form text version.
1586    *
1587    * @param string $operator
1588    *
1589    * @return string
1590    */
1591   public function getSQLOperator($operator = "like") {
1592     switch ($operator) {
1593       case 'eq':
1594         return '=';
1595
1596       case 'lt':
1597         return '<';
1598
1599       case 'lte':
1600         return '<=';
1601
1602       case 'gt':
1603         return '>';
1604
1605       case 'gte':
1606         return '>=';
1607
1608       case 'ne':
1609       case 'neq':
1610         return '!=';
1611
1612       case 'nhas':
1613         return 'NOT LIKE';
1614
1615       case 'in':
1616         return 'IN';
1617
1618       case 'notin':
1619         return 'NOT IN';
1620
1621       case 'nll':
1622         return 'IS NULL';
1623
1624       case 'nnll':
1625         return 'IS NOT NULL';
1626
1627       default:
1628         // type is string
1629         return 'LIKE';
1630     }
1631   }
1632
1633   /**
1634    * Generate where clause.
1635    *
1636    * This can be overridden in reports for special treatment of a field
1637    *
1638    * @param array $field Field specifications
1639    * @param string $op Query operator (not an exact match to sql)
1640    * @param mixed $value
1641    * @param float $min
1642    * @param float $max
1643    *
1644    * @return null|string
1645    */
1646   public function whereClause(&$field, $op, $value, $min, $max) {
1647
1648     $type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
1649     $clause = NULL;
1650
1651     switch ($op) {
1652       case 'bw':
1653       case 'nbw':
1654         if (($min !== NULL && strlen($min) > 0) ||
1655           ($max !== NULL && strlen($max) > 0)
1656         ) {
1657           $min = CRM_Utils_Type::escape($min, $type);
1658           $max = CRM_Utils_Type::escape($max, $type);
1659           $clauses = array();
1660           if ($min) {
1661             if ($op == 'bw') {
1662               $clauses[] = "( {$field['dbAlias']} >= $min )";
1663             }
1664             else {
1665               $clauses[] = "( {$field['dbAlias']} < $min )";
1666             }
1667           }
1668           if ($max) {
1669             if ($op == 'bw') {
1670               $clauses[] = "( {$field['dbAlias']} <= $max )";
1671             }
1672             else {
1673               $clauses[] = "( {$field['dbAlias']} > $max )";
1674             }
1675           }
1676
1677           if (!empty($clauses)) {
1678             if ($op == 'bw') {
1679               $clause = implode(' AND ', $clauses);
1680             }
1681             else {
1682               $clause = implode(' OR ', $clauses);
1683             }
1684           }
1685         }
1686         break;
1687
1688       case 'has':
1689       case 'nhas':
1690         if ($value !== NULL && strlen($value) > 0) {
1691           $value = CRM_Utils_Type::escape($value, $type);
1692           if (strpos($value, '%') === FALSE) {
1693             $value = "'%{$value}%'";
1694           }
1695           else {
1696             $value = "'{$value}'";
1697           }
1698           $sqlOP = $this->getSQLOperator($op);
1699           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1700         }
1701         break;
1702
1703       case 'in':
1704       case 'notin':
1705         if (is_string($value) && strlen($value)) {
1706           $value = explode(',', $value);
1707         }
1708         if ($value !== NULL && is_array($value) && count($value) > 0) {
1709           $sqlOP = $this->getSQLOperator($op);
1710           if (CRM_Utils_Array::value('type', $field) ==
1711             CRM_Utils_Type::T_STRING
1712           ) {
1713             //cycle through selections and escape values
1714             foreach ($value as $key => $selection) {
1715               $value[$key] = CRM_Utils_Type::escape($selection, $type);
1716             }
1717             $clause
1718               = "( {$field['dbAlias']} $sqlOP ( '" . implode("' , '", $value) .
1719               "') )";
1720           }
1721           else {
1722             // for numerical values
1723             $clause = "{$field['dbAlias']} $sqlOP (" . implode(', ', $value) .
1724               ")";
1725           }
1726           if ($op == 'notin') {
1727             $clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
1728           }
1729           else {
1730             $clause = "( " . $clause . " )";
1731           }
1732         }
1733         break;
1734
1735       case 'mhas':
1736         // mhas == multiple has
1737         if ($value !== NULL && count($value) > 0) {
1738           $sqlOP = $this->getSQLOperator($op);
1739           $clause
1740             = "{$field['dbAlias']} REGEXP '[[:cntrl:]]" . implode('|', $value) .
1741             "[[:cntrl:]]'";
1742         }
1743         break;
1744
1745       case 'mnot':
1746         // mnot == multiple is not one of
1747         if ($value !== NULL && count($value) > 0) {
1748           $sqlOP = $this->getSQLOperator($op);
1749           $clause
1750             = "( {$field['dbAlias']} NOT REGEXP '[[:cntrl:]]" . implode('|', $value) .
1751             "[[:cntrl:]]' OR {$field['dbAlias']} IS NULL )";
1752         }
1753         break;
1754
1755       case 'sw':
1756       case 'ew':
1757         if ($value !== NULL && strlen($value) > 0) {
1758           $value = CRM_Utils_Type::escape($value, $type);
1759           if (strpos($value, '%') === FALSE) {
1760             if ($op == 'sw') {
1761               $value = "'{$value}%'";
1762             }
1763             else {
1764               $value = "'%{$value}'";
1765             }
1766           }
1767           else {
1768             $value = "'{$value}'";
1769           }
1770           $sqlOP = $this->getSQLOperator($op);
1771           $clause = "( {$field['dbAlias']} $sqlOP $value )";
1772         }
1773         break;
1774
1775       case 'nll':
1776       case 'nnll':
1777         $sqlOP = $this->getSQLOperator($op);
1778         $clause = "( {$field['dbAlias']} $sqlOP )";
1779         break;
1780
1781       default:
1782         if ($value !== NULL && strlen($value) > 0) {
1783           if (isset($field['clause'])) {
1784             // FIXME: we not doing escape here. Better solution is to use two
1785             // different types - data-type and filter-type
1786             $clause = $field['clause'];
1787           }
1788           else {
1789             $value = CRM_Utils_Type::escape($value, $type);
1790             $sqlOP = $this->getSQLOperator($op);
1791             if ($field['type'] == CRM_Utils_Type::T_STRING) {
1792               $value = "'{$value}'";
1793             }
1794             $clause = "( {$field['dbAlias']} $sqlOP $value )";
1795           }
1796         }
1797         break;
1798     }
1799
1800     if (!empty($field['group']) && $clause) {
1801       $clause = $this->whereGroupClause($field, $value, $op);
1802     }
1803     elseif (!empty($field['tag']) && $clause) {
1804       // not using left join in query because if any contact
1805       // belongs to more than one tag, results duplicate
1806       // entries.
1807       $clause = $this->whereTagClause($field, $value, $op);
1808     }
1809     elseif (!empty($field['membership_org']) && $clause) {
1810       $clause = $this->whereMembershipOrgClause($value, $op);
1811     }
1812     elseif (!empty($field['membership_type']) && $clause) {
1813       $clause = $this->whereMembershipTypeClause($value, $op);
1814     }
1815     return $clause;
1816   }
1817
1818   /**
1819    * Get SQL where clause for a date field.
1820    *
1821    * @param string $fieldName
1822    * @param $relative
1823    * @param string $from
1824    * @param string $to
1825    * @param string $type
1826    * @param string $fromTime
1827    * @param string $toTime
1828    *
1829    * @return null|string
1830    */
1831   public function dateClause(
1832     $fieldName,
1833     $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
1834   ) {
1835     $clauses = array();
1836     if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) {
1837       $sqlOP = $this->getSQLOperator($relative);
1838       return "( {$fieldName} {$sqlOP} )";
1839     }
1840
1841     list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime);
1842
1843     if ($from) {
1844       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
1845       $clauses[] = "( {$fieldName} >= $from )";
1846     }
1847
1848     if ($to) {
1849       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
1850       $clauses[] = "( {$fieldName} <= {$to} )";
1851     }
1852
1853     if (!empty($clauses)) {
1854       return implode(' AND ', $clauses);
1855     }
1856
1857     return NULL;
1858   }
1859
1860   /**
1861    * Possibly unused function.
1862    *
1863    * @todo - could not find any instances where this is called
1864    *
1865    * @param bool $relative
1866    * @param string $from
1867    * @param string $to
1868    *
1869    * @return string|NULL
1870    */
1871   public function dateDisplay($relative, $from, $to) {
1872     list($from, $to) = $this->getFromTo($relative, $from, $to);
1873
1874     if ($from) {
1875       $clauses[] = CRM_Utils_Date::customFormat($from, NULL, array('m', 'M'));
1876     }
1877     else {
1878       $clauses[] = 'Past';
1879     }
1880
1881     if ($to) {
1882       $clauses[] = CRM_Utils_Date::customFormat($to, NULL, array('m', 'M'));
1883     }
1884     else {
1885       $clauses[] = 'Today';
1886     }
1887
1888     if (!empty($clauses)) {
1889       return implode(' - ', $clauses);
1890     }
1891
1892     return NULL;
1893   }
1894
1895   /**
1896    * Get values for from and to for date ranges.
1897    *
1898    * @param bool $relative
1899    * @param string $from
1900    * @param string $to
1901    * @param string $fromTime
1902    * @param string $toTime
1903    *
1904    * @return array
1905    */
1906   public function getFromTo($relative, $from, $to, $fromTime = NULL, $toTime = NULL) {
1907     if (empty($toTime)) {
1908       $toTime = '235959';
1909     }
1910     //FIX ME not working for relative
1911     if ($relative) {
1912       list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
1913       $dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
1914       $from = substr($dateRange['from'], 0, 8);
1915       //Take only Date Part, Sometime Time part is also present in 'to'
1916       $to = substr($dateRange['to'], 0, 8);
1917     }
1918     $from = CRM_Utils_Date::processDate($from, $fromTime);
1919     $to = CRM_Utils_Date::processDate($to, $toTime);
1920     return array($from, $to);
1921   }
1922
1923   /**
1924    * Alter display of rows.
1925    *
1926    * Iterate through the rows retrieved via SQL and make changes for display purposes,
1927    * such as rendering contacts as links.
1928    *
1929    * @param array $rows
1930    *   Rows generated by SQL, with an array for each row.
1931    */
1932   public function alterDisplay(&$rows) {
1933   }
1934
1935   /**
1936    * Alter the way in which custom data fields are displayed.
1937    *
1938    * @param array $rows
1939    */
1940   public function alterCustomDataDisplay(&$rows) {
1941     // custom code to alter rows having custom values
1942     if (empty($this->_customGroupExtends)) {
1943       return;
1944     }
1945
1946     $customFieldIds = array();
1947     foreach ($this->_params['fields'] as $fieldAlias => $value) {
1948       if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
1949         $customFieldIds[$fieldAlias] = $fieldId;
1950       }
1951     }
1952     if (empty($customFieldIds)) {
1953       return;
1954     }
1955
1956     $customFields = $fieldValueMap = array();
1957     $customFieldCols = array(
1958       'column_name',
1959       'data_type',
1960       'html_type',
1961       'option_group_id',
1962       'id',
1963     );
1964
1965     // skip for type date and ContactReference since date format is already handled
1966     $query = "
1967 SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label
1968 FROM  civicrm_custom_field cf
1969 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
1970 LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id
1971 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
1972       cg.is_active = 1 AND
1973       cf.is_active = 1 AND
1974       cf.is_searchable = 1 AND
1975       cf.data_type   NOT IN ('ContactReference', 'Date') AND
1976       cf.id IN (" . implode(",", $customFieldIds) . ")";
1977
1978     $dao = CRM_Core_DAO::executeQuery($query);
1979     while ($dao->fetch()) {
1980       foreach ($customFieldCols as $key) {
1981         $customFields[$dao->table_name . '_custom_' .
1982         $dao->id][$key] = $dao->$key;
1983       }
1984       if ($dao->option_group_id) {
1985         $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label;
1986       }
1987     }
1988     $dao->free();
1989
1990     $entryFound = FALSE;
1991     foreach ($rows as $rowNum => $row) {
1992       foreach ($row as $tableCol => $val) {
1993         if (array_key_exists($tableCol, $customFields)) {
1994           $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap);
1995           $entryFound = TRUE;
1996         }
1997       }
1998
1999       // skip looking further in rows, if first row itself doesn't
2000       // have the column we need
2001       if (!$entryFound) {
2002         break;
2003       }
2004     }
2005   }
2006
2007   /**
2008    * @param $value
2009    * @param $customField
2010    * @param $fieldValueMap
2011    *
2012    * @return float|string|void
2013    */
2014   public function formatCustomValues($value, $customField, $fieldValueMap) {
2015     if (CRM_Utils_System::isNull($value)) {
2016       return NULL;
2017     }
2018
2019     $htmlType = $customField['html_type'];
2020
2021     switch ($customField['data_type']) {
2022       case 'Boolean':
2023         if ($value == '1') {
2024           $retValue = ts('Yes');
2025         }
2026         else {
2027           $retValue = ts('No');
2028         }
2029         break;
2030
2031       case 'Link':
2032         $retValue = CRM_Utils_System::formatWikiURL($value);
2033         break;
2034
2035       case 'File':
2036         $retValue = $value;
2037         break;
2038
2039       case 'Memo':
2040         $retValue = $value;
2041         break;
2042
2043       case 'Float':
2044         if ($htmlType == 'Text') {
2045           $retValue = (float) $value;
2046           break;
2047         }
2048       case 'Money':
2049         if ($htmlType == 'Text') {
2050           $retValue = CRM_Utils_Money::format($value, NULL, '%a');
2051           break;
2052         }
2053       case 'String':
2054       case 'Int':
2055         if (in_array($htmlType, array(
2056           'Text',
2057           'TextArea',
2058         ))) {
2059           $retValue = $value;
2060           break;
2061         }
2062       case 'StateProvince':
2063       case 'Country':
2064
2065         switch ($htmlType) {
2066           case 'Multi-Select Country':
2067             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2068             $customData = array();
2069             foreach ($value as $val) {
2070               if ($val) {
2071                 $customData[] = CRM_Core_PseudoConstant::country($val, FALSE);
2072               }
2073             }
2074             $retValue = implode(', ', $customData);
2075             break;
2076
2077           case 'Select Country':
2078             $retValue = CRM_Core_PseudoConstant::country($value, FALSE);
2079             break;
2080
2081           case 'Select State/Province':
2082             $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
2083             break;
2084
2085           case 'Multi-Select State/Province':
2086             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2087             $customData = array();
2088             foreach ($value as $val) {
2089               if ($val) {
2090                 $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE);
2091               }
2092             }
2093             $retValue = implode(', ', $customData);
2094             break;
2095
2096           case 'Select':
2097           case 'Radio':
2098           case 'Autocomplete-Select':
2099             $retValue = $fieldValueMap[$customField['option_group_id']][$value];
2100             break;
2101
2102           case 'CheckBox':
2103           case 'AdvMulti-Select':
2104           case 'Multi-Select':
2105             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2106             $customData = array();
2107             foreach ($value as $val) {
2108               if ($val) {
2109                 $customData[] = $fieldValueMap[$customField['option_group_id']][$val];
2110               }
2111             }
2112             $retValue = implode(', ', $customData);
2113             break;
2114
2115           default:
2116             $retValue = $value;
2117         }
2118         break;
2119
2120       default:
2121         $retValue = $value;
2122     }
2123
2124     return $retValue;
2125   }
2126
2127   /**
2128    * @param $rows
2129    */
2130   public function removeDuplicates(&$rows) {
2131     if (empty($this->_noRepeats)) {
2132       return;
2133     }
2134     $checkList = array();
2135
2136     foreach ($rows as $key => $list) {
2137       foreach ($list as $colName => $colVal) {
2138         if (array_key_exists($colName, $checkList) &&
2139           $checkList[$colName] == $colVal
2140         ) {
2141           $rows[$key][$colName] = "";
2142         }
2143         if (in_array($colName, $this->_noRepeats)) {
2144           $checkList[$colName] = $colVal;
2145         }
2146       }
2147     }
2148   }
2149
2150   /**
2151    * @param $row
2152    * @param $fields
2153    * @param bool $subtotal
2154    */
2155   public function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
2156     foreach ($row as $colName => $colVal) {
2157       if (in_array($colName, $fields)) {
2158       }
2159       elseif (isset($this->_columnHeaders[$colName])) {
2160         if ($subtotal) {
2161           $row[$colName] = "Subtotal";
2162           $subtotal = FALSE;
2163         }
2164         else {
2165           unset($row[$colName]);
2166         }
2167       }
2168     }
2169   }
2170
2171   /**
2172    * @param $rows
2173    *
2174    * @return bool
2175    */
2176   public function grandTotal(&$rows) {
2177     if (!$this->_rollup || ($this->_rollup == '') ||
2178       ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
2179     ) {
2180       return FALSE;
2181     }
2182     $lastRow = array_pop($rows);
2183
2184     foreach ($this->_columnHeaders as $fld => $val) {
2185       if (!in_array($fld, $this->_statFields)) {
2186         if (!$this->_grandFlag) {
2187           $lastRow[$fld] = "Grand Total";
2188           $this->_grandFlag = TRUE;
2189         }
2190         else {
2191           $lastRow[$fld] = "";
2192         }
2193       }
2194     }
2195
2196     $this->assign('grandStat', $lastRow);
2197     return TRUE;
2198   }
2199
2200   /**
2201    * @param $rows
2202    * @param bool $pager
2203    */
2204   public function formatDisplay(&$rows, $pager = TRUE) {
2205     // set pager based on if any limit was applied in the query.
2206     if ($pager) {
2207       $this->setPager();
2208     }
2209
2210     // allow building charts if any
2211     if (!empty($this->_params['charts']) && !empty($rows)) {
2212       $this->buildChart($rows);
2213       $this->assign('chartEnabled', TRUE);
2214       $this->_chartId = "{$this->_params['charts']}_" .
2215         ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' .
2216         session_id();
2217       $this->assign('chartId', $this->_chartId);
2218     }
2219
2220     // unset columns not to be displayed.
2221     foreach ($this->_columnHeaders as $key => $value) {
2222       if (!empty($value['no_display'])) {
2223         unset($this->_columnHeaders[$key]);
2224       }
2225     }
2226
2227     // unset columns not to be displayed.
2228     if (!empty($rows)) {
2229       foreach ($this->_noDisplay as $noDisplayField) {
2230         foreach ($rows as $rowNum => $row) {
2231           unset($this->_columnHeaders[$noDisplayField]);
2232         }
2233       }
2234     }
2235
2236     // build array of section totals
2237     $this->sectionTotals();
2238
2239     // process grand-total row
2240     $this->grandTotal($rows);
2241
2242     // use this method for formatting rows for display purpose.
2243     $this->alterDisplay($rows);
2244     CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
2245
2246     // use this method for formatting custom rows for display purpose.
2247     $this->alterCustomDataDisplay($rows);
2248   }
2249
2250   /**
2251    * @param $rows
2252    */
2253   public function buildChart(&$rows) {
2254     // override this method for building charts.
2255   }
2256
2257   // select() method below has been added recently (v3.3), and many of the report templates might
2258   // still be having their own select() method. We should fix them as and when encountered and move
2259   // towards generalizing the select() method below.
2260
2261   /**
2262    * Generate the SELECT clause and set class variable $_select
2263    */
2264   public function select() {
2265     $select = $this->_selectAliases = array();
2266
2267     foreach ($this->_columns as $tableName => $table) {
2268       if (array_key_exists('fields', $table)) {
2269         foreach ($table['fields'] as $fieldName => $field) {
2270           if ($tableName == 'civicrm_address') {
2271             $this->_addressField = TRUE;
2272           }
2273           if ($tableName == 'civicrm_email') {
2274             $this->_emailField = TRUE;
2275           }
2276           if ($tableName == 'civicrm_phone') {
2277             $this->_phoneField = TRUE;
2278           }
2279
2280           if (!empty($field['required']) ||
2281             !empty($this->_params['fields'][$fieldName])
2282           ) {
2283
2284             // 1. In many cases we want select clause to be built in slightly different way
2285             //    for a particular field of a particular type.
2286             // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2287             //    as needed.
2288             $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
2289             if ($selectClause) {
2290               $select[] = $selectClause;
2291               continue;
2292             }
2293
2294             // include statistics columns only if set
2295             if (!empty($field['statistics'])) {
2296               foreach ($field['statistics'] as $stat => $label) {
2297                 $alias = "{$tableName}_{$fieldName}_{$stat}";
2298                 switch (strtolower($stat)) {
2299                   case 'max':
2300                   case 'sum':
2301                     $select[] = "$stat({$field['dbAlias']}) as $alias";
2302                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2303                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2304                     $this->_statFields[$label] = $alias;
2305                     $this->_selectAliases[] = $alias;
2306                     break;
2307
2308                   case 'count':
2309                     $select[] = "COUNT({$field['dbAlias']}) as $alias";
2310                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2311                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2312                     $this->_statFields[$label] = $alias;
2313                     $this->_selectAliases[] = $alias;
2314                     break;
2315
2316                   case 'count_distinct':
2317                     $select[] = "COUNT(DISTINCT {$field['dbAlias']}) as $alias";
2318                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2319                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2320                     $this->_statFields[$label] = $alias;
2321                     $this->_selectAliases[] = $alias;
2322                     break;
2323
2324                   case 'avg':
2325                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
2326                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2327                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2328                     $this->_statFields[$label] = $alias;
2329                     $this->_selectAliases[] = $alias;
2330                     break;
2331                 }
2332               }
2333             }
2334             else {
2335               $alias = "{$tableName}_{$fieldName}";
2336               $select[] = "{$field['dbAlias']} as $alias";
2337               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
2338               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
2339               $this->_selectAliases[] = $alias;
2340             }
2341           }
2342         }
2343       }
2344
2345       // select for group bys
2346       if (array_key_exists('group_bys', $table)) {
2347         foreach ($table['group_bys'] as $fieldName => $field) {
2348
2349           if ($tableName == 'civicrm_address') {
2350             $this->_addressField = TRUE;
2351           }
2352           if ($tableName == 'civicrm_email') {
2353             $this->_emailField = TRUE;
2354           }
2355           if ($tableName == 'civicrm_phone') {
2356             $this->_phoneField = TRUE;
2357           }
2358           // 1. In many cases we want select clause to be built in slightly different way
2359           //    for a particular field of a particular type.
2360           // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2361           //    as needed.
2362           $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
2363           if ($selectClause) {
2364             $select[] = $selectClause;
2365             continue;
2366           }
2367
2368           if (!empty($this->_params['group_bys']) &&
2369             !empty($this->_params['group_bys'][$fieldName]) &&
2370             !empty($this->_params['group_bys_freq'])
2371           ) {
2372             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
2373               case 'YEARWEEK':
2374                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
2375                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2376                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2377                 $field['title'] = 'Week';
2378                 break;
2379
2380               case 'YEAR':
2381                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
2382                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2383                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2384                 $field['title'] = 'Year';
2385                 break;
2386
2387               case 'MONTH':
2388                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
2389                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2390                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2391                 $field['title'] = 'Month';
2392                 break;
2393
2394               case 'QUARTER':
2395                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
2396                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2397                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2398                 $field['title'] = 'Quarter';
2399                 break;
2400             }
2401             // for graphs and charts -
2402             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
2403               $this->_interval = $field['title'];
2404               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title']
2405                 = $field['title'] . ' Beginning';
2406               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
2407               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
2408
2409               // just to make sure these values are transferred to rows.
2410               // since we 'll need them for calculation purpose,
2411               // e.g making subtotals look nicer or graphs
2412               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
2413               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
2414             }
2415           }
2416         }
2417       }
2418     }
2419
2420     $this->_selectClauses = $select;
2421     $this->_select = "SELECT " . implode(', ', $select) . " ";
2422   }
2423
2424   /**
2425    * @param string $tableName
2426    * @param $tableKey
2427    * @param string $fieldName
2428    * @param $field
2429    *
2430    * @return bool
2431    */
2432   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
2433     return FALSE;
2434   }
2435
2436   public function where() {
2437     $this->storeWhereHavingClauseArray();
2438
2439     if (empty($this->_whereClauses)) {
2440       $this->_where = "WHERE ( 1 ) ";
2441       $this->_having = "";
2442     }
2443     else {
2444       $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
2445     }
2446
2447     if ($this->_aclWhere) {
2448       $this->_where .= " AND {$this->_aclWhere} ";
2449     }
2450
2451     if (!empty($this->_havingClauses)) {
2452       // use this clause to construct group by clause.
2453       $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
2454     }
2455   }
2456
2457   /**
2458    * Store Where clauses into an array - breaking out this step makes
2459    * over-riding more flexible as the clauses can be used in constructing a
2460    * temp table that may not be part of the final where clause or added
2461    * in other functions
2462    */
2463   public function storeWhereHavingClauseArray() {
2464     foreach ($this->_columns as $tableName => $table) {
2465       if (array_key_exists('filters', $table)) {
2466         foreach ($table['filters'] as $fieldName => $field) {
2467           // respect pseudofield to filter spec so fields can be marked as
2468           // not to be handled here
2469           if (!empty($field['pseudofield'])) {
2470             continue;
2471           }
2472           $clause = NULL;
2473           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
2474             if (CRM_Utils_Array::value('operatorType', $field) ==
2475               CRM_Report_Form::OP_MONTH
2476             ) {
2477               $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2478               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2479               if (is_array($value) && !empty($value)) {
2480                 $clause
2481                   = "(month({$field['dbAlias']}) $op (" . implode(', ', $value) .
2482                   '))';
2483               }
2484             }
2485             else {
2486               $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
2487               $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
2488               $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
2489               $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
2490               $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
2491               $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
2492             }
2493           }
2494           else {
2495             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2496             if ($op) {
2497               $clause = $this->whereClause($field,
2498                 $op,
2499                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
2500                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
2501                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
2502               );
2503             }
2504           }
2505
2506           if (!empty($clause)) {
2507             if (!empty($field['having'])) {
2508               $this->_havingClauses[] = $clause;
2509             }
2510             else {
2511               $this->_whereClauses[] = $clause;
2512             }
2513           }
2514         }
2515       }
2516     }
2517
2518   }
2519
2520   public function processReportMode() {
2521     $buttonName = $this->controller->getButtonName();
2522
2523     $output = CRM_Utils_Request::retrieve(
2524       'output',
2525       'String',
2526       CRM_Core_DAO::$_nullObject
2527     );
2528
2529     $this->_sendmail
2530       = CRM_Utils_Request::retrieve(
2531         'sendmail',
2532         'Boolean',
2533         CRM_Core_DAO::$_nullObject
2534       );
2535
2536     $this->_absoluteUrl = FALSE;
2537     $printOnly = FALSE;
2538     $this->assign('printOnly', FALSE);
2539
2540     if ($this->_printButtonName == $buttonName || $output == 'print' ||
2541       ($this->_sendmail && !$output)
2542     ) {
2543       $this->assign('printOnly', TRUE);
2544       $printOnly = TRUE;
2545       $this->assign('outputMode', 'print');
2546       $this->_outputMode = 'print';
2547       if ($this->_sendmail) {
2548         $this->_absoluteUrl = TRUE;
2549       }
2550     }
2551     elseif ($this->_pdfButtonName == $buttonName || $output == 'pdf') {
2552       $this->assign('printOnly', TRUE);
2553       $printOnly = TRUE;
2554       $this->assign('outputMode', 'pdf');
2555       $this->_outputMode = 'pdf';
2556       $this->_absoluteUrl = TRUE;
2557     }
2558     elseif ($this->_csvButtonName == $buttonName || $output == 'csv') {
2559       $this->assign('printOnly', TRUE);
2560       $printOnly = TRUE;
2561       $this->assign('outputMode', 'csv');
2562       $this->_outputMode = 'csv';
2563       $this->_absoluteUrl = TRUE;
2564     }
2565     elseif ($this->_groupButtonName == $buttonName || $output == 'group') {
2566       $this->assign('outputMode', 'group');
2567       $this->_outputMode = 'group';
2568     }
2569     elseif ($output == 'create_report' && $this->_criteriaForm) {
2570       $this->assign('outputMode', 'create_report');
2571       $this->_outputMode = 'create_report';
2572     }
2573     else {
2574       $this->assign('outputMode', 'html');
2575       $this->_outputMode = 'html';
2576     }
2577
2578     // Get today's date to include in printed reports
2579     if ($printOnly) {
2580       $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
2581       $this->assign('reportDate', $reportDate);
2582     }
2583   }
2584
2585   /**
2586    * Post Processing function for Form (postProcessCommon should be used to set other variables from input as the api accesses that function)
2587    */
2588   public function beginPostProcess() {
2589     $this->setParams($this->controller->exportValues($this->_name));
2590
2591     if (empty($this->_params) &&
2592       $this->_force
2593     ) {
2594       $this->setParams($this->_formValues);
2595     }
2596
2597     // hack to fix params when submitted from dashboard, CRM-8532
2598     // fields array is missing because form building etc is skipped
2599     // in dashboard mode for report
2600     //@todo - this could be done in the dashboard no we have a setter
2601     if (empty($this->_params['fields']) && !$this->_noFields) {
2602       $this->setParams($this->_formValues);
2603     }
2604
2605     $this->_formValues = $this->_params;
2606     if (CRM_Core_Permission::check('administer Reports') &&
2607       isset($this->_id) &&
2608       ($this->_instanceButtonName ==
2609         $this->controller->getButtonName() . '_save' ||
2610         $this->_chartButtonName == $this->controller->getButtonName()
2611       )
2612     ) {
2613       $this->assign('updateReportButton', TRUE);
2614     }
2615     $this->processReportMode();
2616     $this->beginPostProcessCommon();
2617   }
2618
2619   /**
2620    * BeginPostProcess function run in both report mode and non-report mode (api)
2621    */
2622   public function beginPostProcessCommon() {
2623
2624   }
2625
2626   /**
2627    * @param bool $applyLimit
2628    *
2629    * @return string
2630    */
2631   public function buildQuery($applyLimit = TRUE) {
2632     $this->select();
2633     $this->from();
2634     $this->customDataFrom();
2635     $this->where();
2636     $this->groupBy();
2637     $this->orderBy();
2638
2639     // order_by columns not selected for display need to be included in SELECT
2640     $unselectedSectionColumns = $this->unselectedSectionColumns();
2641     foreach ($unselectedSectionColumns as $alias => $section) {
2642       $this->_select .= ", {$section['dbAlias']} as {$alias}";
2643     }
2644
2645     if ($applyLimit && empty($this->_params['charts'])) {
2646       $this->limit();
2647     }
2648     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2649
2650     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2651     return $sql;
2652   }
2653
2654   public function groupBy() {
2655     $groupBys = array();
2656     if (!empty($this->_params['group_bys']) &&
2657       is_array($this->_params['group_bys']) &&
2658       !empty($this->_params['group_bys'])
2659     ) {
2660       foreach ($this->_columns as $tableName => $table) {
2661         if (array_key_exists('group_bys', $table)) {
2662           foreach ($table['group_bys'] as $fieldName => $field) {
2663             if (!empty($this->_params['group_bys'][$fieldName])) {
2664               $groupBys[] = $field['dbAlias'];
2665             }
2666           }
2667         }
2668       }
2669     }
2670
2671     if (!empty($groupBys)) {
2672       $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
2673     }
2674   }
2675
2676   public function orderBy() {
2677     $this->_orderBy = "";
2678     $this->_sections = array();
2679     $this->storeOrderByArray();
2680     if (!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP') {
2681       $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2682     }
2683     $this->assign('sections', $this->_sections);
2684   }
2685
2686   /**
2687    * In some cases other functions want to know which fields are selected for ordering by
2688    * Separating this into a separate function allows it to be called separately from constructing
2689    * the order by clause
2690    */
2691   public function storeOrderByArray() {
2692     $orderBys = array();
2693
2694     if (!empty($this->_params['order_bys']) &&
2695       is_array($this->_params['order_bys']) &&
2696       !empty($this->_params['order_bys'])
2697     ) {
2698
2699       // Process order_bys in user-specified order
2700       foreach ($this->_params['order_bys'] as $orderBy) {
2701         $orderByField = array();
2702         foreach ($this->_columns as $tableName => $table) {
2703           if (array_key_exists('order_bys', $table)) {
2704             // For DAO columns defined in $this->_columns
2705             $fields = $table['order_bys'];
2706           }
2707           elseif (array_key_exists('extends', $table)) {
2708             // For custom fields referenced in $this->_customGroupExtends
2709             $fields = CRM_Utils_Array::value('fields', $table, array());
2710           }
2711           else {
2712             continue;
2713           }
2714           if (!empty($fields) && is_array($fields)) {
2715             foreach ($fields as $fieldName => $field) {
2716               if ($fieldName == $orderBy['column']) {
2717                 $orderByField = array_merge($field, $orderBy);
2718                 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2719                 break 2;
2720               }
2721             }
2722           }
2723         }
2724
2725         if (!empty($orderByField)) {
2726           $this->_orderByFields[] = $orderByField;
2727           $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2728
2729           // Record any section headers for assignment to the template
2730           if (!empty($orderBy['section'])) {
2731             $orderByField['pageBreak'] = CRM_Utils_Array::value('pageBreak', $orderBy);
2732             $this->_sections[$orderByField['tplField']] = $orderByField;
2733           }
2734         }
2735       }
2736     }
2737
2738     $this->_orderByArray = $orderBys;
2739
2740     $this->assign('sections', $this->_sections);
2741   }
2742
2743   /**
2744    * @return array
2745    */
2746   public function unselectedSectionColumns() {
2747     $selectColumns = array();
2748     foreach ($this->_columns as $tableName => $table) {
2749       if (array_key_exists('fields', $table)) {
2750         foreach ($table['fields'] as $fieldName => $field) {
2751           if (!empty($field['required']) ||
2752             !empty($this->_params['fields'][$fieldName])
2753           ) {
2754
2755             $selectColumns["{$tableName}_{$fieldName}"] = 1;
2756           }
2757         }
2758       }
2759     }
2760
2761     if (is_array($this->_sections)) {
2762       return array_diff_key($this->_sections, $selectColumns);
2763     }
2764     else {
2765       return array();
2766     }
2767   }
2768
2769   /**
2770    * @param $sql
2771    * @param $rows
2772    */
2773   public function buildRows($sql, &$rows) {
2774     $dao = CRM_Core_DAO::executeQuery($sql);
2775     if (!is_array($rows)) {
2776       $rows = array();
2777     }
2778
2779     // use this method to modify $this->_columnHeaders
2780     $this->modifyColumnHeaders();
2781
2782     $unselectedSectionColumns = $this->unselectedSectionColumns();
2783
2784     while ($dao->fetch()) {
2785       $row = array();
2786       foreach ($this->_columnHeaders as $key => $value) {
2787         if (property_exists($dao, $key)) {
2788           $row[$key] = $dao->$key;
2789         }
2790       }
2791
2792       // section headers not selected for display need to be added to row
2793       foreach ($unselectedSectionColumns as $key => $values) {
2794         if (property_exists($dao, $key)) {
2795           $row[$key] = $dao->$key;
2796         }
2797       }
2798
2799       $rows[] = $row;
2800     }
2801   }
2802
2803   /**
2804    * When "order by" fields are marked as sections, this assigns to the template
2805    * an array of total counts for each section. This data is used by the Smarty
2806    * plugin {sectionTotal}
2807    */
2808   public function sectionTotals() {
2809
2810     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
2811     if (empty($this->_selectAliases)) {
2812       return;
2813     }
2814
2815     if (!empty($this->_sections)) {
2816       // build the query with no LIMIT clause
2817       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
2818       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
2819
2820       // pull section aliases out of $this->_sections
2821       $sectionAliases = array_keys($this->_sections);
2822
2823       $ifnulls = array();
2824       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
2825         $ifnulls[] = "ifnull($alias, '') as $alias";
2826       }
2827
2828       // Group (un-limited) report by all aliases and get counts. This might
2829       // be done more efficiently when the contents of $sql are known, ie. by
2830       // overriding this method in the report class.
2831
2832       $query = "select " . implode(", ", $ifnulls) .
2833         ", count(*) as ct from ($sql) as subquery group by " .
2834         implode(", ", $sectionAliases);
2835
2836       // initialize array of total counts
2837       $totals = array();
2838       $dao = CRM_Core_DAO::executeQuery($query);
2839       while ($dao->fetch()) {
2840
2841         // let $this->_alterDisplay translate any integer ids to human-readable values.
2842         $rows[0] = $dao->toArray();
2843         $this->alterDisplay($rows);
2844         $row = $rows[0];
2845
2846         // add totals for all permutations of section values
2847         $values = array();
2848         $i = 1;
2849         $aliasCount = count($sectionAliases);
2850         foreach ($sectionAliases as $alias) {
2851           $values[] = $row[$alias];
2852           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
2853           if ($i == $aliasCount) {
2854             // the last alias is the lowest-level section header; use count as-is
2855             $totals[$key] = $dao->ct;
2856           }
2857           else {
2858             // other aliases are higher level; roll count into their total
2859             $totals[$key] += $dao->ct;
2860           }
2861         }
2862       }
2863       $this->assign('sectionTotals', $totals);
2864     }
2865   }
2866
2867   public function modifyColumnHeaders() {
2868     // use this method to modify $this->_columnHeaders
2869   }
2870
2871   /**
2872    * @param $rows
2873    */
2874   public function doTemplateAssignment(&$rows) {
2875     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
2876     $this->assign_by_ref('rows', $rows);
2877     $this->assign('statistics', $this->statistics($rows));
2878   }
2879
2880   /**
2881    * override this method to build your own statistics
2882    * @param $rows
2883    *
2884    * @return array
2885    */
2886   public function statistics(&$rows) {
2887     $statistics = array();
2888
2889     $count = count($rows);
2890
2891     if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
2892       $count++;
2893     }
2894
2895     $this->countStat($statistics, $count);
2896
2897     $this->groupByStat($statistics);
2898
2899     $this->filterStat($statistics);
2900
2901     return $statistics;
2902   }
2903
2904   /**
2905    * @param $statistics
2906    * @param $count
2907    */
2908   public function countStat(&$statistics, $count) {
2909     $statistics['counts']['rowCount'] = array(
2910       'title' => ts('Row(s) Listed'),
2911       'value' => $count,
2912     );
2913
2914     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
2915       $statistics['counts']['rowsFound'] = array(
2916         'title' => ts('Total Row(s)'),
2917         'value' => $this->_rowsFound,
2918       );
2919     }
2920   }
2921
2922   /**
2923    * @param $statistics
2924    */
2925   public function groupByStat(&$statistics) {
2926     if (!empty($this->_params['group_bys']) &&
2927       is_array($this->_params['group_bys']) &&
2928       !empty($this->_params['group_bys'])
2929     ) {
2930       foreach ($this->_columns as $tableName => $table) {
2931         if (array_key_exists('group_bys', $table)) {
2932           foreach ($table['group_bys'] as $fieldName => $field) {
2933             if (!empty($this->_params['group_bys'][$fieldName])) {
2934               $combinations[] = $field['title'];
2935             }
2936           }
2937         }
2938       }
2939       $statistics['groups'][] = array(
2940         'title' => ts('Grouping(s)'),
2941         'value' => implode(' & ', $combinations),
2942       );
2943     }
2944   }
2945
2946   /**
2947    * @param $statistics
2948    */
2949   public function filterStat(&$statistics) {
2950     foreach ($this->_columns as $tableName => $table) {
2951       if (array_key_exists('filters', $table)) {
2952         foreach ($table['filters'] as $fieldName => $field) {
2953           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE &&
2954             CRM_Utils_Array::value('operatorType', $field) !=
2955             CRM_Report_Form::OP_MONTH
2956           ) {
2957             list($from, $to)
2958               = $this->getFromTo(
2959                 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
2960                 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
2961                 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
2962                 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
2963                 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
2964               );
2965             $from_time_format = !empty($this->_params["{$fieldName}_from_time"]) ? 'h' : 'd';
2966             $from = CRM_Utils_Date::customFormat($from, NULL, array($from_time_format));
2967
2968             $to_time_format = !empty($this->_params["{$fieldName}_to_time"]) ? 'h' : 'd';
2969             $to = CRM_Utils_Date::customFormat($to, NULL, array($to_time_format));
2970
2971             if ($from || $to) {
2972               $statistics['filters'][] = array(
2973                 'title' => $field['title'],
2974                 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
2975               );
2976             }
2977             elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
2978               array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE))
2979             )) {
2980               $pair = $this->getOperationPair(CRM_Report_Form::OP_DATE);
2981               $statistics['filters'][] = array(
2982                 'title' => $field['title'],
2983                 'value' => $pair[$rel],
2984               );
2985             }
2986           }
2987           else {
2988             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2989             $value = NULL;
2990             if ($op) {
2991               $pair = $this->getOperationPair(
2992                 CRM_Utils_Array::value('operatorType', $field),
2993                 $fieldName
2994               );
2995               $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
2996               $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
2997               $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2998               if (in_array($op, array('bw', 'nbw')) && ($min || $max)) {
2999                 $value = "{$pair[$op]} $min " . ts('and') . " $max";
3000               }
3001               elseif ($val && CRM_Utils_Array::value('operatorType', $field) & self::OP_ENTITYREF) {
3002                 $this->setEntityRefDefaults($field, $tableName);
3003                 $result = civicrm_api3($field['attributes']['entity'], 'getlist',
3004                   array('id' => $val) +
3005                   CRM_Utils_Array::value('api', $field['attributes'], array()));
3006                 $values = array();
3007                 foreach ($result['values'] as $v) {
3008                   $values[] = $v['label'];
3009                 }
3010                 $value = "{$pair[$op]} " . implode(', ', $values);
3011               }
3012               elseif ($op == 'nll' || $op == 'nnll') {
3013                 $value = $pair[$op];
3014               }
3015               elseif (is_array($val) && (!empty($val))) {
3016                 $options = CRM_Utils_Array::value('options', $field, array());
3017                 foreach ($val as $key => $valIds) {
3018                   if (isset($options[$valIds])) {
3019                     $val[$key] = $options[$valIds];
3020                   }
3021                 }
3022                 $pair[$op] = (count($val) == 1) ? (($op == 'notin' || $op ==
3023                   'mnot') ? ts('Is Not') : ts('Is')) : CRM_Utils_Array::value($op, $pair);
3024                 $val = implode(', ', $val);
3025                 $value = "{$pair[$op]} " . $val;
3026               }
3027               elseif (!is_array($val) && (!empty($val) || $val == '0') &&
3028                 isset($field['options']) &&
3029                 is_array($field['options']) && !empty($field['options'])
3030               ) {
3031                 $value = CRM_Utils_Array::value($op, $pair) . " " .
3032                   CRM_Utils_Array::value($val, $field['options'], $val);
3033               }
3034               elseif ($val) {
3035                 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
3036               }
3037             }
3038             if ($value) {
3039               $statistics['filters'][] = array(
3040                 'title' => CRM_Utils_Array::value('title', $field),
3041                 'value' => $value,
3042               );
3043             }
3044           }
3045         }
3046       }
3047     }
3048   }
3049
3050   /**
3051    * End post processing.
3052    *
3053    * @param array|null $rows
3054    */
3055   public function endPostProcess(&$rows = NULL) {
3056     if ($this->_storeResultSet) {
3057       $this->_resultSet = $rows;
3058     }
3059
3060     if ($this->_outputMode == 'print' ||
3061       $this->_outputMode == 'pdf' ||
3062       $this->_sendmail
3063     ) {
3064
3065       $content = $this->compileContent();
3066       $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
3067         "reset=1", TRUE
3068       );
3069
3070       if ($this->_sendmail) {
3071         $config = CRM_Core_Config::singleton();
3072         $attachments = array();
3073
3074         if ($this->_outputMode == 'csv') {
3075           $content
3076             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3077             ": {$url}</p>" . '<p>' .
3078             ts('The report is attached as a CSV file.') . '</p>' .
3079             $this->_formValues['report_footer'];
3080
3081           $csvFullFilename = $config->templateCompileDir .
3082             CRM_Utils_File::makeFileName('CiviReport.csv');
3083           $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
3084           file_put_contents($csvFullFilename, $csvContent);
3085           $attachments[] = array(
3086             'fullPath' => $csvFullFilename,
3087             'mime_type' => 'text/csv',
3088             'cleanName' => 'CiviReport.csv',
3089           );
3090         }
3091         if ($this->_outputMode == 'pdf') {
3092           // generate PDF content
3093           $pdfFullFilename = $config->templateCompileDir .
3094             CRM_Utils_File::makeFileName('CiviReport.pdf');
3095           file_put_contents($pdfFullFilename,
3096             CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
3097               TRUE, array('orientation' => 'landscape')
3098             )
3099           );
3100           // generate Email Content
3101           $content
3102             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3103             ": {$url}</p>" . '<p>' .
3104             ts('The report is attached as a PDF file.') . '</p>' .
3105             $this->_formValues['report_footer'];
3106
3107           $attachments[] = array(
3108             'fullPath' => $pdfFullFilename,
3109             'mime_type' => 'application/pdf',
3110             'cleanName' => 'CiviReport.pdf',
3111           );
3112         }
3113
3114         if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
3115           $this->_outputMode, $attachments
3116         )
3117         ) {
3118           CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
3119         }
3120         else {
3121           CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
3122         }
3123         return TRUE;
3124       }
3125       elseif ($this->_outputMode == 'print') {
3126         echo $content;
3127       }
3128       else {
3129         if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
3130           $config = CRM_Core_Config::singleton();
3131           //get chart image name
3132           $chartImg = $this->_chartId . '.png';
3133           //get image url path
3134           $uploadUrl
3135             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) .
3136             'openFlashChart/';
3137           $uploadUrl .= $chartImg;
3138           //get image doc path to overwrite
3139           $uploadImg
3140             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
3141             'openFlashChart/' . $chartImg;
3142           //Load the image
3143           $chart = imagecreatefrompng($uploadUrl);
3144           //convert it into formatted png
3145           CRM_Utils_System::setHttpHeader('Content-type', 'image/png');
3146           //overwrite with same image
3147           imagepng($chart, $uploadImg);
3148           //delete the object
3149           imagedestroy($chart);
3150         }
3151         CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
3152       }
3153       CRM_Utils_System::civiExit();
3154     }
3155     elseif ($this->_outputMode == 'csv') {
3156       CRM_Report_Utils_Report::export2csv($this, $rows);
3157     }
3158     elseif ($this->_outputMode == 'group') {
3159       $group = $this->_params['groups'];
3160       $this->add2group($group);
3161     }
3162     elseif ($this->_instanceButtonName == $this->controller->getButtonName()) {
3163       CRM_Report_Form_Instance::postProcess($this);
3164     }
3165     elseif ($this->_createNewButtonName == $this->controller->getButtonName() ||
3166       $this->_outputMode == 'create_report'
3167     ) {
3168       $this->_createNew = TRUE;
3169       CRM_Report_Form_Instance::postProcess($this);
3170     }
3171   }
3172
3173   /**
3174    * Set store result set indicator to TRUE.
3175    *
3176    * @todo explain what this does
3177    */
3178   public function storeResultSet() {
3179     $this->_storeResultSet = TRUE;
3180   }
3181
3182   /**
3183    * Get result set.
3184    *
3185    * @return bool
3186    */
3187   public function getResultSet() {
3188     return $this->_resultSet;
3189   }
3190
3191   /**
3192    * Use the form name to create the tpl file name.
3193    *
3194    * @return string
3195    */
3196   public function getTemplateFileName() {
3197     $defaultTpl = parent::getTemplateFileName();
3198     $template = CRM_Core_Smarty::singleton();
3199     if (!$template->template_exists($defaultTpl)) {
3200       $defaultTpl = 'CRM/Report/Form.tpl';
3201     }
3202     return $defaultTpl;
3203   }
3204
3205   /**
3206    * Compile the report content.
3207    * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
3208    *
3209    * @return string
3210    */
3211   public function compileContent() {
3212     $templateFile = $this->getHookedTemplateFileName();
3213     return $this->_formValues['report_header'] .
3214     CRM_Core_Form::$_template->fetch($templateFile) .
3215     $this->_formValues['report_footer'];
3216   }
3217
3218
3219   public function postProcess() {
3220     // get ready with post process params
3221     $this->beginPostProcess();
3222
3223     // build query
3224     $sql = $this->buildQuery();
3225
3226     // build array of result based on column headers. This method also allows
3227     // modifying column headers before using it to build result set i.e $rows.
3228     $rows = array();
3229     $this->buildRows($sql, $rows);
3230
3231     // format result set.
3232     $this->formatDisplay($rows);
3233
3234     // assign variables to templates
3235     $this->doTemplateAssignment($rows);
3236
3237     // do print / pdf / instance stuff if needed
3238     $this->endPostProcess($rows);
3239   }
3240
3241   /**
3242    * @param int $rowCount
3243    * @return array
3244    */
3245   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
3246     // lets do the pager if in html mode
3247     $this->_limit = NULL;
3248
3249     // CRM-14115, over-ride row count if rowCount is specified in URL
3250     if ($this->_dashBoardRowCount) {
3251       $rowCount = $this->_dashBoardRowCount;
3252     }
3253     if ($this->_outputMode == 'html' || $this->_outputMode == 'group') {
3254       $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
3255
3256       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
3257
3258       // @todo all http vars should be extracted in the preProcess
3259       // - not randomly in the class
3260       if (!$pageId && !empty($_POST)) {
3261         if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
3262           $pageId = max((int) $_POST['crmPID_B'], 1);
3263         }
3264         elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
3265           $pageId = max((int) $_POST['crmPID'], 1);
3266         }
3267         unset($_POST['crmPID_B'], $_POST['crmPID']);
3268       }
3269
3270       $pageId = $pageId ? $pageId : 1;
3271       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
3272       $offset = ($pageId - 1) * $rowCount;
3273
3274       $offset = CRM_Utils_Type::escape($offset, 'Int');
3275       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
3276
3277       $this->_limit = " LIMIT $offset, $rowCount";
3278       return array($offset, $rowCount);
3279     }
3280     if ($this->_limitValue) {
3281       if ($this->_offsetValue) {
3282         $this->_limit = " LIMIT {$this->_offsetValue}, {$this->_limitValue} ";
3283       }
3284       else {
3285         $this->_limit = " LIMIT " . $this->_limitValue;
3286       }
3287     }
3288   }
3289
3290   /**
3291    * @param int $rowCount
3292    */
3293   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
3294
3295     // CRM-14115, over-ride row count if rowCount is specified in URL
3296     if ($this->_dashBoardRowCount) {
3297       $rowCount = $this->_dashBoardRowCount;
3298     }
3299
3300     if ($this->_limit && ($this->_limit != '')) {
3301       $sql = "SELECT FOUND_ROWS();";
3302       $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
3303       $params = array(
3304         'total' => $this->_rowsFound,
3305         'rowCount' => $rowCount,
3306         'status' => ts('Records') . ' %%StatusMessage%%',
3307         'buttonBottom' => 'PagerBottomButton',
3308         'buttonTop' => 'PagerTopButton',
3309         'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
3310       );
3311
3312       $pager = new CRM_Utils_Pager($params);
3313       $this->assign_by_ref('pager', $pager);
3314       $this->ajaxResponse['totalRows'] = $this->_rowsFound;
3315     }
3316   }
3317
3318   /**
3319    * @param $field
3320    * @param $value
3321    * @param $op
3322    *
3323    * @return string
3324    */
3325   public function whereGroupClause($field, $value, $op) {
3326
3327     $smartGroupQuery = "";
3328
3329     $group = new CRM_Contact_DAO_Group();
3330     $group->is_active = 1;
3331     $group->find();
3332     $smartGroups = array();
3333     while ($group->fetch()) {
3334       if (in_array($group->id, $this->_params['gid_value']) &&
3335         $group->saved_search_id
3336       ) {
3337         $smartGroups[] = $group->id;
3338       }
3339     }
3340
3341     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3342
3343     $smartGroupQuery = '';
3344     if (!empty($smartGroups)) {
3345       $smartGroups = implode(',', $smartGroups);
3346       $smartGroupQuery = " UNION DISTINCT
3347                   SELECT DISTINCT smartgroup_contact.contact_id
3348                   FROM civicrm_group_contact_cache smartgroup_contact
3349                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3350     }
3351
3352     $sqlOp = $this->getSQLOperator($op);
3353     if (!is_array($value)) {
3354       $value = array($value);
3355     }
3356     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3357
3358     $contactAlias = $this->_aliases['civicrm_contact'];
3359     if (!empty($this->relationType) && $this->relationType == 'b_a') {
3360       $contactAlias = $this->_aliases['civicrm_contact_b'];
3361     }
3362     return " {$contactAlias}.id {$sqlOp} (
3363                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
3364                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
3365                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
3366                           {$smartGroupQuery} ) ";
3367   }
3368
3369   /**
3370    * @param $field
3371    * @param $value
3372    * @param $op
3373    *
3374    * @return string
3375    */
3376   public function whereTagClause($field, $value, $op) {
3377     // not using left join in query because if any contact
3378     // belongs to more than one tag, results duplicate
3379     // entries.
3380     $sqlOp = $this->getSQLOperator($op);
3381     if (!is_array($value)) {
3382       $value = array($value);
3383     }
3384     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3385     $entity_table = $this->_tagFilterTable;
3386     return " {$this->_aliases[$entity_table]}.id {$sqlOp} (
3387                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
3388                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
3389                           WHERE entity_table = '$entity_table' AND {$clause} ) ";
3390   }
3391
3392   /**
3393    * Generate membership organization clause.
3394    *
3395    * @param mixed $value
3396    * @param string $op SQL Operator
3397    *
3398    * @return string
3399    */
3400   public function whereMembershipOrgClause($value, $op) {
3401     $sqlOp = $this->getSQLOperator($op);
3402     if (!is_array($value)) {
3403       $value = array($value);
3404     }
3405
3406     $tmp_membership_org_sql_list = implode(', ', $value);
3407     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3408                           SELECT DISTINCT mem.contact_id
3409                           FROM civicrm_membership mem
3410                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3411                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3412                           WHERE mt.member_of_contact_id IN (" .
3413     $tmp_membership_org_sql_list . ")
3414                           AND mt.is_active = '1'
3415                           AND mem_status.is_current_member = '1'
3416                           AND mem_status.is_active = '1' )  ";
3417   }
3418
3419   /**
3420    * Generate Membership Type SQL Clause.
3421    * @param mixed $value
3422    * @param string $op
3423    *
3424    * @return string
3425    *   SQL query string
3426    */
3427   public function whereMembershipTypeClause($value, $op) {
3428     $sqlOp = $this->getSQLOperator($op);
3429     if (!is_array($value)) {
3430       $value = array($value);
3431     }
3432
3433     $tmp_membership_sql_list = implode(', ', $value);
3434     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3435                           SELECT DISTINCT mem.contact_id
3436                           FROM civicrm_membership mem
3437                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3438                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3439                           WHERE mem.membership_type_id IN (" .
3440     $tmp_membership_sql_list . ")
3441                           AND mt.is_active = '1'
3442                           AND mem_status.is_current_member = '1'
3443                           AND mem_status.is_active = '1' ) ";
3444   }
3445
3446   /**
3447    * @param string $tableAlias
3448    */
3449   public function buildACLClause($tableAlias = 'contact_a') {
3450     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
3451   }
3452
3453   /**
3454    * @param bool $addFields
3455    * @param array $permCustomGroupIds
3456    */
3457   public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
3458     if (empty($this->_customGroupExtends)) {
3459       return;
3460     }
3461     if (!is_array($this->_customGroupExtends)) {
3462       $this->_customGroupExtends = array($this->_customGroupExtends);
3463     }
3464     $customGroupWhere = '';
3465     if (!empty($permCustomGroupIds)) {
3466       $customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) .
3467         ") AND";
3468     }
3469     $sql = "
3470 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
3471        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
3472 FROM   civicrm_custom_group cg
3473 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
3474 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
3475       {$customGroupWhere}
3476       cg.is_active = 1 AND
3477       cf.is_active = 1 AND
3478       cf.is_searchable = 1
3479 ORDER BY cg.weight, cf.weight";
3480     $customDAO = CRM_Core_DAO::executeQuery($sql);
3481
3482     $curTable = NULL;
3483     while ($customDAO->fetch()) {
3484       if ($customDAO->table_name != $curTable) {
3485         $curTable = $customDAO->table_name;
3486         $curFields = $curFilters = array();
3487
3488         // dummy dao object
3489         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
3490         $this->_columns[$curTable]['extends'] = $customDAO->extends;
3491         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
3492         $this->_columns[$curTable]['group_title'] = $customDAO->title;
3493
3494         foreach (array(
3495                    'fields',
3496                    'filters',
3497                    'group_bys',
3498                  ) as $colKey) {
3499           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
3500             $this->_columns[$curTable][$colKey] = array();
3501           }
3502         }
3503       }
3504       $fieldName = 'custom_' . $customDAO->cf_id;
3505
3506       if ($addFields) {
3507         // this makes aliasing work in favor
3508         $curFields[$fieldName] = array(
3509           'name' => $customDAO->column_name,
3510           'title' => $customDAO->label,
3511           'dataType' => $customDAO->data_type,
3512           'htmlType' => $customDAO->html_type,
3513         );
3514       }
3515       if ($this->_customGroupFilters) {
3516         // this makes aliasing work in favor
3517         $curFilters[$fieldName] = array(
3518           'name' => $customDAO->column_name,
3519           'title' => $customDAO->label,
3520           'dataType' => $customDAO->data_type,
3521           'htmlType' => $customDAO->html_type,
3522         );
3523       }
3524
3525       switch ($customDAO->data_type) {
3526         case 'Date':
3527           // filters
3528           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
3529           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
3530           // CRM-6946, show time part for datetime date fields
3531           if ($customDAO->time_format) {
3532             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
3533           }
3534           break;
3535
3536         case 'Boolean':
3537           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
3538           $curFilters[$fieldName]['options'] = array(
3539             '' => ts('- select -'),
3540             1 => ts('Yes'),
3541             0 => ts('No'),
3542           );
3543           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3544           break;
3545
3546         case 'Int':
3547           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
3548           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3549           break;
3550
3551         case 'Money':
3552           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3553           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3554           break;
3555
3556         case 'Float':
3557           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3558           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
3559           break;
3560
3561         case 'String':
3562           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3563
3564           if (!empty($customDAO->option_group_id)) {
3565             if (in_array($customDAO->html_type, array(
3566               'Multi-Select',
3567               'AdvMulti-Select',
3568               'CheckBox',
3569             ))) {
3570               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3571             }
3572             else {
3573               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3574             }
3575             if ($this->_customGroupFilters) {
3576               $curFilters[$fieldName]['options'] = array();
3577               $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(
3578                 1 => array(
3579                   $customDAO->option_group_id,
3580                   'Integer',
3581                 ),
3582               ));
3583               while ($ogDAO->fetch()) {
3584                 $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label;
3585               }
3586               CRM_Utils_Hook::customFieldOptions($customDAO->cf_id, $curFilters[$fieldName]['options'], FALSE);
3587             }
3588           }
3589           break;
3590
3591         case 'StateProvince':
3592           if (in_array($customDAO->html_type, array(
3593             'Multi-Select State/Province',
3594           ))) {
3595             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3596           }
3597           else {
3598             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3599           }
3600           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince();
3601           break;
3602
3603         case 'Country':
3604           if (in_array($customDAO->html_type, array(
3605             'Multi-Select Country',
3606           ))) {
3607             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3608           }
3609           else {
3610             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3611           }
3612           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country();
3613           break;
3614
3615         case 'ContactReference':
3616           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3617           $curFilters[$fieldName]['name'] = 'display_name';
3618           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3619
3620           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3621           $curFields[$fieldName]['name'] = 'display_name';
3622           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3623           break;
3624
3625         default:
3626           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3627           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3628       }
3629
3630       if (!array_key_exists('type', $curFields[$fieldName])) {
3631         $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
3632       }
3633
3634       if ($addFields) {
3635         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
3636       }
3637       if ($this->_customGroupFilters) {
3638         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
3639       }
3640       if ($this->_customGroupGroupBy) {
3641         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
3642       }
3643     }
3644   }
3645
3646   public function customDataFrom() {
3647     if (empty($this->_customGroupExtends)) {
3648       return;
3649     }
3650     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
3651
3652     foreach ($this->_columns as $table => $prop) {
3653       if (substr($table, 0, 13) == 'civicrm_value' ||
3654         substr($table, 0, 12) == 'custom_value'
3655       ) {
3656         $extendsTable = $mapper[$prop['extends']];
3657
3658         // check field is in params
3659         if (!$this->isFieldSelected($prop)) {
3660           continue;
3661         }
3662         $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id");
3663
3664         $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
3665         $this->_from .= "
3666 {$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$baseJoin}";
3667         // handle for ContactReference
3668         if (array_key_exists('fields', $prop)) {
3669           foreach ($prop['fields'] as $fieldName => $field) {
3670             if (CRM_Utils_Array::value('dataType', $field) ==
3671               'ContactReference'
3672             ) {
3673               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
3674               $this->_from .= "
3675 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
3676             }
3677           }
3678         }
3679       }
3680     }
3681   }
3682
3683   /**
3684    * @param $prop
3685    *
3686    * @return bool
3687    */
3688   public function isFieldSelected($prop) {
3689     if (empty($prop)) {
3690       return FALSE;
3691     }
3692
3693     if (!empty($this->_params['fields'])) {
3694       foreach (array_keys($prop['fields']) as $fieldAlias) {
3695         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
3696         if ($customFieldId) {
3697           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
3698             return TRUE;
3699           }
3700
3701           //might be survey response field.
3702           if (!empty($this->_params['fields']['survey_response']) &&
3703             !empty($prop['fields'][$fieldAlias]['isSurveyResponseField'])
3704           ) {
3705             return TRUE;
3706           }
3707         }
3708       }
3709     }
3710
3711     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
3712       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
3713         if (array_key_exists($fieldAlias, $this->_params['group_bys']) &&
3714           CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3715         ) {
3716           return TRUE;
3717         }
3718       }
3719     }
3720
3721     if (!empty($this->_params['order_bys'])) {
3722       foreach (array_keys($prop['fields']) as $fieldAlias) {
3723         foreach ($this->_params['order_bys'] as $orderBy) {
3724           if ($fieldAlias == $orderBy['column'] &&
3725             CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3726           ) {
3727             return TRUE;
3728           }
3729         }
3730       }
3731     }
3732
3733     if (!empty($prop['filters']) && $this->_customGroupFilters) {
3734       foreach ($prop['filters'] as $fieldAlias => $val) {
3735         foreach (array(
3736                    'value',
3737                    'min',
3738                    'max',
3739                    'relative',
3740                    'from',
3741                    'to',
3742                  ) as $attach) {
3743           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
3744             (!empty($this->_params[$fieldAlias . '_' . $attach])
3745               || ($attach != 'relative' &&
3746                 $this->_params[$fieldAlias . '_' . $attach] == '0')
3747             )
3748           ) {
3749             return TRUE;
3750           }
3751         }
3752         if (!empty($this->_params[$fieldAlias . '_op']) &&
3753           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
3754         ) {
3755           return TRUE;
3756         }
3757       }
3758     }
3759
3760     return FALSE;
3761   }
3762
3763   /**
3764    * Check for empty order_by configurations and remove them; also set
3765    * template to hide them.
3766    *
3767    * @param array $formValues
3768    */
3769   public function preProcessOrderBy(&$formValues) {
3770     // Object to show/hide form elements
3771     $_showHide = new CRM_Core_ShowHideBlocks('', '');
3772
3773     $_showHide->addShow('optionField_1');
3774
3775     // Cycle through order_by options; skip any empty ones, and hide them as well
3776     $n = 1;
3777
3778     if (!empty($formValues['order_bys'])) {
3779       foreach ($formValues['order_bys'] as $order_by) {
3780         if ($order_by['column'] && $order_by['column'] != '-') {
3781           $_showHide->addShow('optionField_' . $n);
3782           $orderBys[$n] = $order_by;
3783           $n++;
3784         }
3785       }
3786     }
3787     for ($i = $n; $i <= 5; $i++) {
3788       if ($i > 1) {
3789         $_showHide->addHide('optionField_' . $i);
3790       }
3791     }
3792
3793     // overwrite order_by options with modified values
3794     if (!empty($orderBys)) {
3795       $formValues['order_bys'] = $orderBys;
3796     }
3797     else {
3798       $formValues['order_bys'] = array(1 => array('column' => '-'));
3799     }
3800
3801     // assign show/hide data to template
3802     $_showHide->addToTemplate();
3803   }
3804
3805   /**
3806    * Does table name have columns in SELECT clause?
3807    *
3808    * @param string $tableName
3809    *   Name of table (index of $this->_columns array).
3810    *
3811    * @return bool
3812    */
3813   public function isTableSelected($tableName) {
3814     return in_array($tableName, $this->selectedTables());
3815   }
3816
3817   /**
3818    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause.
3819    * (building the array if it's unset)
3820    *
3821    * @return array
3822    *   selectedTables
3823    */
3824   public function selectedTables() {
3825     if (!$this->_selectedTables) {
3826       $orderByColumns = array();
3827       if (array_key_exists('order_bys', $this->_params) &&
3828         is_array($this->_params['order_bys'])
3829       ) {
3830         foreach ($this->_params['order_bys'] as $orderBy) {
3831           $orderByColumns[] = $orderBy['column'];
3832         }
3833       }
3834
3835       foreach ($this->_columns as $tableName => $table) {
3836         if (array_key_exists('fields', $table)) {
3837           foreach ($table['fields'] as $fieldName => $field) {
3838             if (!empty($field['required']) ||
3839               !empty($this->_params['fields'][$fieldName])
3840             ) {
3841               $this->_selectedTables[] = $tableName;
3842               break;
3843             }
3844           }
3845         }
3846         if (array_key_exists('order_bys', $table)) {
3847           foreach ($table['order_bys'] as $orderByName => $orderBy) {
3848             if (in_array($orderByName, $orderByColumns)) {
3849               $this->_selectedTables[] = $tableName;
3850               break;
3851             }
3852           }
3853         }
3854         if (array_key_exists('filters', $table)) {
3855           foreach ($table['filters'] as $filterName => $filter) {
3856             if (!empty($this->_params["{$filterName}_value"]) ||
3857               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3858               'nll' ||
3859               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
3860               'nnll'
3861             ) {
3862               $this->_selectedTables[] = $tableName;
3863               break;
3864             }
3865           }
3866         }
3867       }
3868     }
3869     return $this->_selectedTables;
3870   }
3871
3872   /**
3873    * @deprecated - use getAddressColumns which is a more accurate description
3874    * and also accepts an array of options rather than a long list
3875    *
3876    * adding address fields to construct function in reports
3877    *
3878    * @param bool $groupBy
3879    *   Add GroupBy? Not appropriate for detail report.
3880    * @param bool $orderBy
3881    *   Add GroupBy? Not appropriate for detail report.
3882    * @param bool $filters
3883    * @param array $defaults
3884    *
3885    * @return array
3886    *   address fields for construct clause
3887    */
3888   public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
3889     $addressFields = array(
3890       'civicrm_address' => array(
3891         'dao' => 'CRM_Core_DAO_Address',
3892         'fields' => array(
3893           'name' => array(
3894             'title' => ts('Address Name'),
3895             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
3896           ),
3897           'street_address' => array(
3898             'title' => ts('Street Address'),
3899             'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
3900           ),
3901           'supplemental_address_1' => array(
3902             'title' => ts('Supplementary Address Field 1'),
3903             'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
3904           ),
3905           'supplemental_address_2' => array(
3906             'title' => ts('Supplementary Address Field 2'),
3907             'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
3908           ),
3909           'street_number' => array(
3910             'name' => 'street_number',
3911             'title' => ts('Street Number'),
3912             'type' => 1,
3913             'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
3914           ),
3915           'street_name' => array(
3916             'name' => 'street_name',
3917             'title' => ts('Street Name'),
3918             'type' => 1,
3919             'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
3920           ),
3921           'street_unit' => array(
3922             'name' => 'street_unit',
3923             'title' => ts('Street Unit'),
3924             'type' => 1,
3925             'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
3926           ),
3927           'city' => array(
3928             'title' => ts('City'),
3929             'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
3930           ),
3931           'postal_code' => array(
3932             'title' => ts('Postal Code'),
3933             'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
3934           ),
3935           'postal_code_suffix' => array(
3936             'title' => ts('Postal Code Suffix'),
3937             'default' => CRM_Utils_Array::value('postal_code_suffix', $defaults, FALSE),
3938           ),
3939           'country_id' => array(
3940             'title' => ts('Country'),
3941             'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
3942           ),
3943           'state_province_id' => array(
3944             'title' => ts('State/Province'),
3945             'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
3946           ),
3947           'county_id' => array(
3948             'title' => ts('County'),
3949             'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
3950           ),
3951         ),
3952         'grouping' => 'location-fields',
3953       ),
3954     );
3955
3956     if ($filters) {
3957       $addressFields['civicrm_address']['filters'] = array(
3958         'street_number' => array(
3959           'title' => ts('Street Number'),
3960           'type' => 1,
3961           'name' => 'street_number',
3962         ),
3963         'street_name' => array(
3964           'title' => ts('Street Name'),
3965           'name' => 'street_name',
3966           'operator' => 'like',
3967         ),
3968         'postal_code' => array(
3969           'title' => ts('Postal Code'),
3970           'type' => 1,
3971           'name' => 'postal_code',
3972         ),
3973         'city' => array(
3974           'title' => ts('City'),
3975           'operator' => 'like',
3976           'name' => 'city',
3977         ),
3978         'country_id' => array(
3979           'name' => 'country_id',
3980           'title' => ts('Country'),
3981           'type' => CRM_Utils_Type::T_INT,
3982           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
3983           'options' => CRM_Core_PseudoConstant::country(),
3984         ),
3985         'state_province_id' => array(
3986           'name' => 'state_province_id',
3987           'title' => ts('State/Province'),
3988           'type' => CRM_Utils_Type::T_INT,
3989           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
3990           'options' => array(),
3991         ),
3992         'county_id' => array(
3993           'name' => 'county_id',
3994           'title' => ts('County'),
3995           'type' => CRM_Utils_Type::T_INT,
3996           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
3997           'options' => array(),
3998         ),
3999       );
4000     }
4001
4002     if ($orderBy) {
4003       $addressFields['civicrm_address']['order_bys'] = array(
4004         'street_name' => array('title' => ts('Street Name')),
4005         'street_number' => array('title' => 'Odd / Even Street Number'),
4006         'street_address' => NULL,
4007         'city' => NULL,
4008         'postal_code' => NULL,
4009       );
4010     }
4011
4012     if ($groupBy) {
4013       $addressFields['civicrm_address']['group_bys'] = array(
4014         'street_address' => NULL,
4015         'city' => NULL,
4016         'postal_code' => NULL,
4017         'state_province_id' => array(
4018           'title' => ts('State/Province'),
4019         ),
4020         'country_id' => array(
4021           'title' => ts('Country'),
4022         ),
4023         'county_id' => array(
4024           'title' => ts('County'),
4025         ),
4026       );
4027     }
4028     return $addressFields;
4029   }
4030
4031   /**
4032    * Do AlterDisplay processing on Address Fields.
4033    *
4034    * @param $row
4035    * @param $rows
4036    * @param $rowNum
4037    * @param $baseUrl
4038    * @param $urltxt
4039    *
4040    * @return bool
4041    */
4042   public function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $urltxt) {
4043     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4044     $entryFound = FALSE;
4045     // handle country
4046     if (array_key_exists('civicrm_address_country_id', $row)) {
4047       if ($value = $row['civicrm_address_country_id']) {
4048         $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
4049         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4050           "reset=1&force=1&{$criteriaQueryParams}&" .
4051           "country_id_op=in&country_id_value={$value}",
4052           $this->_absoluteUrl, $this->_id
4053         );
4054         $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
4055         $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
4056           array(1 => $urltxt)
4057         );
4058       }
4059
4060       $entryFound = TRUE;
4061     }
4062     if (array_key_exists('civicrm_address_county_id', $row)) {
4063       if ($value = $row['civicrm_address_county_id']) {
4064         $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
4065         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4066           "reset=1&force=1&{$criteriaQueryParams}&" .
4067           "county_id_op=in&county_id_value={$value}",
4068           $this->_absoluteUrl, $this->_id
4069         );
4070         $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
4071         $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
4072           array(1 => $urltxt)
4073         );
4074       }
4075       $entryFound = TRUE;
4076     }
4077     // handle state province
4078     if (array_key_exists('civicrm_address_state_province_id', $row)) {
4079       if ($value = $row['civicrm_address_state_province_id']) {
4080         $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
4081
4082         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4083           "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
4084           $this->_absoluteUrl, $this->_id
4085         );
4086         $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
4087         $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
4088           array(1 => $urltxt)
4089         );
4090       }
4091       $entryFound = TRUE;
4092     }
4093
4094     return $entryFound;
4095   }
4096
4097   /**
4098    * Adjusts dates passed in to YEAR() for fiscal year.
4099    *
4100    * @param string $fieldName
4101    *
4102    * @return string
4103    */
4104   public function fiscalYearOffset($fieldName) {
4105     $config = CRM_Core_Config::singleton();
4106     $fy = $config->fiscalYearStart;
4107     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' ||
4108       ($fy['d'] == 1 && $fy['M'] == 1)
4109     ) {
4110       return "YEAR( $fieldName )";
4111     }
4112     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" .
4113     ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
4114   }
4115
4116   /**
4117    * Add Address into From Table if required.
4118    */
4119   public function addAddressFromClause() {
4120     // include address field if address column is to be included
4121     if ((isset($this->_addressField) &&
4122         $this->_addressField
4123       ) ||
4124       $this->isTableSelected('civicrm_address')
4125     ) {
4126       $this->_from .= "
4127                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4128                            ON ({$this->_aliases['civicrm_contact']}.id =
4129                                {$this->_aliases['civicrm_address']}.contact_id) AND
4130                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4131     }
4132   }
4133
4134   /**
4135    * Add Phone into From Table if required.
4136    */
4137   public function addPhoneFromClause() {
4138     // include address field if address column is to be included
4139     if ($this->isTableSelected('civicrm_phone')
4140     ) {
4141       $this->_from .= "
4142       LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
4143       ON ({$this->_aliases['civicrm_contact']}.id =
4144       {$this->_aliases['civicrm_phone']}.contact_id) AND
4145       {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
4146     }
4147   }
4148
4149   /**
4150    * Get phone columns to add to array.
4151    *
4152    * @param array $options
4153    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4154    *   - prefix_label Label to give columns from this phone table instance
4155    *
4156    * @return array
4157    *   phone columns definition
4158    */
4159   public function getPhoneColumns($options = array()) {
4160     $defaultOptions = array(
4161       'prefix' => '',
4162       'prefix_label' => '',
4163     );
4164
4165     $options = array_merge($defaultOptions, $options);
4166
4167     $fields = array(
4168       $options['prefix'] . 'civicrm_phone' => array(
4169         'dao' => 'CRM_Core_DAO_Phone',
4170         'fields' => array(
4171           $options['prefix'] . 'phone' => array(
4172             'title' => ts($options['prefix_label'] . 'Phone'),
4173             'name' => 'phone',
4174           ),
4175         ),
4176       ),
4177     );
4178     return $fields;
4179   }
4180
4181   /**
4182    * Get address columns to add to array.
4183    *
4184    * @param array $options
4185    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4186    *   - prefix_label Label to give columns from this address table instance
4187    *
4188    * @return array
4189    *   address columns definition
4190    */
4191   public function getAddressColumns($options = array()) {
4192     $options += array(
4193       'prefix' => '',
4194       'prefix_label' => '',
4195       'group_by' => TRUE,
4196       'order_by' => TRUE,
4197       'filters' => TRUE,
4198       'defaults' => array(),
4199     );
4200     return $this->addAddressFields(
4201       $options['group_by'],
4202       $options['order_by'],
4203       $options['filters'],
4204       $options['defaults']
4205     );
4206   }
4207
4208   /**
4209    * @param int $groupID
4210    */
4211   public function add2group($groupID) {
4212     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
4213       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
4214       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
4215
4216       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
4217       $sql = str_replace('WITH ROLLUP', '', $sql);
4218       $dao = CRM_Core_DAO::executeQuery($sql);
4219
4220       $contact_ids = array();
4221       // Add resulting contacts to group
4222       while ($dao->fetch()) {
4223         if ($dao->addtogroup_contact_id) {
4224           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
4225         }
4226       }
4227
4228       if (!empty($contact_ids)) {
4229         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
4230         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
4231       }
4232       else {
4233         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
4234       }
4235     }
4236   }
4237
4238   /**
4239    * function used for showing charts on print screen.
4240    */
4241   public static function uploadChartImage() {
4242     // upload strictly for '.png' images
4243     $name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
4244     if (preg_match('/\.png$/', $name)) {
4245       //
4246       // POST data is usually string data, but we are passing a RAW .png
4247       // so PHP is a bit confused and $_POST is empty. But it has saved
4248       // the raw bits into $HTTP_RAW_POST_DATA
4249       //
4250       $httpRawPostData = $GLOBALS['HTTP_RAW_POST_DATA'];
4251
4252       // prepare the directory
4253       $config = CRM_Core_Config::singleton();
4254       $defaultPath
4255         = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
4256         '/openFlashChart/';
4257       if (!file_exists($defaultPath)) {
4258         mkdir($defaultPath, 0777, TRUE);
4259       }
4260
4261       // full path to the saved image including filename
4262       $destination = $defaultPath . $name;
4263
4264       //write and save
4265       $jfh = fopen($destination, 'w') or die("can't open file");
4266       fwrite($jfh, $httpRawPostData);
4267       fclose($jfh);
4268       CRM_Utils_System::civiExit();
4269     }
4270   }
4271
4272   /**
4273    * Apply common settings to entityRef fields.
4274    *
4275    * @param array $field
4276    * @param string $table
4277    */
4278   private function setEntityRefDefaults(&$field, $table) {
4279     $field['attributes'] = $field['attributes'] ? $field['attributes'] : array();
4280     $field['attributes'] += array(
4281       'entity' => CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table)),
4282       'multiple' => TRUE,
4283       'placeholder' => ts('- select -'),
4284     );
4285   }
4286
4287 }