CRM/Report/Form.php

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