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     $customFields = $fieldValueMap = array();
2035     $customFieldCols = array(
2036       'column_name',
2037       'data_type',
2038       'html_type',
2039       'option_group_id',
2040       'id',
2041     );
2042
2043     // skip for type date and ContactReference since date format is already handled
2044     $query = "
2045 SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label
2046 FROM  civicrm_custom_field cf
2047 INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
2048 LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id
2049 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
2050       cg.is_active = 1 AND
2051       cf.is_active = 1 AND
2052       cf.is_searchable = 1 AND
2053       cf.data_type   NOT IN ('ContactReference', 'Date') AND
2054       cf.id IN (" . implode(",", $customFieldIds) . ")";
2055
2056     $dao = CRM_Core_DAO::executeQuery($query);
2057     while ($dao->fetch()) {
2058       foreach ($customFieldCols as $key) {
2059         $customFields[$dao->table_name . '_custom_' .
2060         $dao->id][$key] = $dao->$key;
2061       }
2062       if ($dao->option_group_id) {
2063         $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label;
2064       }
2065     }
2066     $dao->free();
2067
2068     $entryFound = FALSE;
2069     foreach ($rows as $rowNum => $row) {
2070       foreach ($row as $tableCol => $val) {
2071         if (array_key_exists($tableCol, $customFields)) {
2072           $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap);
2073           $entryFound = TRUE;
2074         }
2075       }
2076
2077       // skip looking further in rows, if first row itself doesn't
2078       // have the column we need
2079       if (!$entryFound) {
2080         break;
2081       }
2082     }
2083   }
2084
2085   /**
2086    * Format custom values.
2087    *
2088    * @param mixed $value
2089    * @param array $customField
2090    * @param array $fieldValueMap
2091    *
2092    * @return float|string|void
2093    */
2094   public function formatCustomValues($value, $customField, $fieldValueMap) {
2095     if (CRM_Utils_System::isNull($value)) {
2096       return NULL;
2097     }
2098
2099     $htmlType = $customField['html_type'];
2100
2101     switch ($customField['data_type']) {
2102       case 'Boolean':
2103         if ($value == '1') {
2104           $retValue = ts('Yes');
2105         }
2106         else {
2107           $retValue = ts('No');
2108         }
2109         break;
2110
2111       case 'Link':
2112         $retValue = CRM_Utils_System::formatWikiURL($value);
2113         break;
2114
2115       case 'File':
2116         $retValue = $value;
2117         break;
2118
2119       case 'Memo':
2120         $retValue = $value;
2121         break;
2122
2123       case 'Float':
2124         if ($htmlType == 'Text') {
2125           $retValue = (float) $value;
2126           break;
2127         }
2128       case 'Money':
2129         if ($htmlType == 'Text') {
2130           $retValue = CRM_Utils_Money::format($value, NULL, '%a');
2131           break;
2132         }
2133       case 'String':
2134       case 'Int':
2135         if (in_array($htmlType, array(
2136           'Text',
2137           'TextArea',
2138         ))) {
2139           $retValue = $value;
2140           break;
2141         }
2142       case 'StateProvince':
2143       case 'Country':
2144
2145         switch ($htmlType) {
2146           case 'Multi-Select Country':
2147             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2148             $customData = array();
2149             foreach ($value as $val) {
2150               if ($val) {
2151                 $customData[] = CRM_Core_PseudoConstant::country($val, FALSE);
2152               }
2153             }
2154             $retValue = implode(', ', $customData);
2155             break;
2156
2157           case 'Select Country':
2158             $retValue = CRM_Core_PseudoConstant::country($value, FALSE);
2159             break;
2160
2161           case 'Select State/Province':
2162             $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
2163             break;
2164
2165           case 'Multi-Select State/Province':
2166             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2167             $customData = array();
2168             foreach ($value as $val) {
2169               if ($val) {
2170                 $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE);
2171               }
2172             }
2173             $retValue = implode(', ', $customData);
2174             break;
2175
2176           case 'Select':
2177           case 'Radio':
2178           case 'Autocomplete-Select':
2179             $retValue = $fieldValueMap[$customField['option_group_id']][$value];
2180             break;
2181
2182           case 'CheckBox':
2183           case 'AdvMulti-Select':
2184           case 'Multi-Select':
2185             $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
2186             $customData = array();
2187             foreach ($value as $val) {
2188               if ($val) {
2189                 $customData[] = $fieldValueMap[$customField['option_group_id']][$val];
2190               }
2191             }
2192             $retValue = implode(', ', $customData);
2193             break;
2194
2195           default:
2196             $retValue = $value;
2197         }
2198         break;
2199
2200       default:
2201         $retValue = $value;
2202     }
2203
2204     return $retValue;
2205   }
2206
2207   /**
2208    * Remove duplicate rows.
2209    *
2210    * @param array $rows
2211    */
2212   public function removeDuplicates(&$rows) {
2213     if (empty($this->_noRepeats)) {
2214       return;
2215     }
2216     $checkList = array();
2217
2218     foreach ($rows as $key => $list) {
2219       foreach ($list as $colName => $colVal) {
2220         if (array_key_exists($colName, $checkList) &&
2221           $checkList[$colName] == $colVal
2222         ) {
2223           $rows[$key][$colName] = "";
2224         }
2225         if (in_array($colName, $this->_noRepeats)) {
2226           $checkList[$colName] = $colVal;
2227         }
2228       }
2229     }
2230   }
2231
2232   /**
2233    * Fix subtotal display.
2234    *
2235    * @param array $row
2236    * @param array $fields
2237    * @param bool $subtotal
2238    */
2239   public function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
2240     foreach ($row as $colName => $colVal) {
2241       if (in_array($colName, $fields)) {
2242       }
2243       elseif (isset($this->_columnHeaders[$colName])) {
2244         if ($subtotal) {
2245           $row[$colName] = "Subtotal";
2246           $subtotal = FALSE;
2247         }
2248         else {
2249           unset($row[$colName]);
2250         }
2251       }
2252     }
2253   }
2254
2255   /**
2256    * Calculate grant total.
2257    *
2258    * @param array $rows
2259    *
2260    * @return bool
2261    */
2262   public function grandTotal(&$rows) {
2263     if (!$this->_rollup || ($this->_rollup == '') ||
2264       ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
2265     ) {
2266       return FALSE;
2267     }
2268     $lastRow = array_pop($rows);
2269
2270     foreach ($this->_columnHeaders as $fld => $val) {
2271       if (!in_array($fld, $this->_statFields)) {
2272         if (!$this->_grandFlag) {
2273           $lastRow[$fld] = "Grand Total";
2274           $this->_grandFlag = TRUE;
2275         }
2276         else {
2277           $lastRow[$fld] = "";
2278         }
2279       }
2280     }
2281
2282     $this->assign('grandStat', $lastRow);
2283     return TRUE;
2284   }
2285
2286   /**
2287    * Format display output.
2288    *
2289    * @param array $rows
2290    * @param bool $pager
2291    */
2292   public function formatDisplay(&$rows, $pager = TRUE) {
2293     // set pager based on if any limit was applied in the query.
2294     if ($pager) {
2295       $this->setPager();
2296     }
2297
2298     // allow building charts if any
2299     if (!empty($this->_params['charts']) && !empty($rows)) {
2300       $this->buildChart($rows);
2301       $this->assign('chartEnabled', TRUE);
2302       $this->_chartId = "{$this->_params['charts']}_" .
2303         ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' .
2304         session_id();
2305       $this->assign('chartId', $this->_chartId);
2306     }
2307
2308     // unset columns not to be displayed.
2309     foreach ($this->_columnHeaders as $key => $value) {
2310       if (!empty($value['no_display'])) {
2311         unset($this->_columnHeaders[$key]);
2312       }
2313     }
2314
2315     // unset columns not to be displayed.
2316     if (!empty($rows)) {
2317       foreach ($this->_noDisplay as $noDisplayField) {
2318         foreach ($rows as $rowNum => $row) {
2319           unset($this->_columnHeaders[$noDisplayField]);
2320         }
2321       }
2322     }
2323
2324     // build array of section totals
2325     $this->sectionTotals();
2326
2327     // process grand-total row
2328     $this->grandTotal($rows);
2329
2330     // use this method for formatting rows for display purpose.
2331     $this->alterDisplay($rows);
2332     CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
2333
2334     // use this method for formatting custom rows for display purpose.
2335     $this->alterCustomDataDisplay($rows);
2336   }
2337
2338   /**
2339    * Build chart.
2340    *
2341    * @param array $rows
2342    */
2343   public function buildChart(&$rows) {
2344     // override this method for building charts.
2345   }
2346
2347   // select() method below has been added recently (v3.3), and many of the report templates might
2348   // still be having their own select() method. We should fix them as and when encountered and move
2349   // towards generalizing the select() method below.
2350
2351   /**
2352    * Generate the SELECT clause and set class variable $_select.
2353    */
2354   public function select() {
2355     $select = $this->_selectAliases = array();
2356
2357     foreach ($this->_columns as $tableName => $table) {
2358       if (array_key_exists('fields', $table)) {
2359         foreach ($table['fields'] as $fieldName => $field) {
2360           if ($tableName == 'civicrm_address') {
2361             $this->_addressField = TRUE;
2362           }
2363           if ($tableName == 'civicrm_email') {
2364             $this->_emailField = TRUE;
2365           }
2366           if ($tableName == 'civicrm_phone') {
2367             $this->_phoneField = TRUE;
2368           }
2369
2370           if (!empty($field['required']) ||
2371             !empty($this->_params['fields'][$fieldName])
2372           ) {
2373
2374             // 1. In many cases we want select clause to be built in slightly different way
2375             // for a particular field of a particular type.
2376             // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2377             // as needed.
2378             $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
2379             if ($selectClause) {
2380               $select[] = $selectClause;
2381               continue;
2382             }
2383
2384             // include statistics columns only if set
2385             if (!empty($field['statistics'])) {
2386               foreach ($field['statistics'] as $stat => $label) {
2387                 $alias = "{$tableName}_{$fieldName}_{$stat}";
2388                 switch (strtolower($stat)) {
2389                   case 'max':
2390                   case 'sum':
2391                     $select[] = "$stat({$field['dbAlias']}) as $alias";
2392                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2393                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2394                     $this->_statFields[$label] = $alias;
2395                     $this->_selectAliases[] = $alias;
2396                     break;
2397
2398                   case 'count':
2399                     $select[] = "COUNT({$field['dbAlias']}) as $alias";
2400                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2401                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2402                     $this->_statFields[$label] = $alias;
2403                     $this->_selectAliases[] = $alias;
2404                     break;
2405
2406                   case 'count_distinct':
2407                     $select[] = "COUNT(DISTINCT {$field['dbAlias']}) as $alias";
2408                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2409                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
2410                     $this->_statFields[$label] = $alias;
2411                     $this->_selectAliases[] = $alias;
2412                     break;
2413
2414                   case 'avg':
2415                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
2416                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
2417                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
2418                     $this->_statFields[$label] = $alias;
2419                     $this->_selectAliases[] = $alias;
2420                     break;
2421                 }
2422               }
2423             }
2424             else {
2425               $alias = "{$tableName}_{$fieldName}";
2426               $select[] = "{$field['dbAlias']} as $alias";
2427               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
2428               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
2429               $this->_selectAliases[] = $alias;
2430             }
2431           }
2432         }
2433       }
2434
2435       // select for group bys
2436       if (array_key_exists('group_bys', $table)) {
2437         foreach ($table['group_bys'] as $fieldName => $field) {
2438
2439           if ($tableName == 'civicrm_address') {
2440             $this->_addressField = TRUE;
2441           }
2442           if ($tableName == 'civicrm_email') {
2443             $this->_emailField = TRUE;
2444           }
2445           if ($tableName == 'civicrm_phone') {
2446             $this->_phoneField = TRUE;
2447           }
2448           // 1. In many cases we want select clause to be built in slightly different way
2449           // for a particular field of a particular type.
2450           // 2. This method when used should receive params by reference and modify $this->_columnHeaders
2451           // as needed.
2452           $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
2453           if ($selectClause) {
2454             $select[] = $selectClause;
2455             continue;
2456           }
2457
2458           if (!empty($this->_params['group_bys']) &&
2459             !empty($this->_params['group_bys'][$fieldName]) &&
2460             !empty($this->_params['group_bys_freq'])
2461           ) {
2462             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
2463               case 'YEARWEEK':
2464                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
2465                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2466                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2467                 $field['title'] = 'Week';
2468                 break;
2469
2470               case 'YEAR':
2471                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
2472                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2473                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2474                 $field['title'] = 'Year';
2475                 break;
2476
2477               case 'MONTH':
2478                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
2479                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2480                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2481                 $field['title'] = 'Month';
2482                 break;
2483
2484               case 'QUARTER':
2485                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
2486                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
2487                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
2488                 $field['title'] = 'Quarter';
2489                 break;
2490             }
2491             // for graphs and charts -
2492             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
2493               $this->_interval = $field['title'];
2494               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title']
2495                 = $field['title'] . ' Beginning';
2496               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
2497               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
2498
2499               // just to make sure these values are transferred to rows.
2500               // since we 'll need them for calculation purpose,
2501               // e.g making subtotals look nicer or graphs
2502               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
2503               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
2504             }
2505           }
2506         }
2507       }
2508     }
2509
2510     $this->_selectClauses = $select;
2511     $this->_select = "SELECT " . implode(', ', $select) . " ";
2512   }
2513
2514   /**
2515    * Build select clause for a single field.
2516    *
2517    * @param string $tableName
2518    * @param string $tableKey
2519    * @param string $fieldName
2520    * @param string $field
2521    *
2522    * @return bool
2523    */
2524   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
2525     return FALSE;
2526   }
2527
2528   /**
2529    * Build where clause.
2530    */
2531   public function where() {
2532     $this->storeWhereHavingClauseArray();
2533
2534     if (empty($this->_whereClauses)) {
2535       $this->_where = "WHERE ( 1 ) ";
2536       $this->_having = "";
2537     }
2538     else {
2539       $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
2540     }
2541
2542     if ($this->_aclWhere) {
2543       $this->_where .= " AND {$this->_aclWhere} ";
2544     }
2545
2546     if (!empty($this->_havingClauses)) {
2547       // use this clause to construct group by clause.
2548       $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
2549     }
2550   }
2551
2552   /**
2553    * Store Where clauses into an array.
2554    *
2555    * Breaking out this step makes over-riding more flexible as the clauses can be used in constructing a
2556    * temp table that may not be part of the final where clause or added
2557    * in other functions
2558    */
2559   public function storeWhereHavingClauseArray() {
2560     foreach ($this->_columns as $tableName => $table) {
2561       if (array_key_exists('filters', $table)) {
2562         foreach ($table['filters'] as $fieldName => $field) {
2563           // respect pseudofield to filter spec so fields can be marked as
2564           // not to be handled here
2565           if (!empty($field['pseudofield'])) {
2566             continue;
2567           }
2568           $clause = NULL;
2569           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
2570             if (CRM_Utils_Array::value('operatorType', $field) ==
2571               CRM_Report_Form::OP_MONTH
2572             ) {
2573               $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2574               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2575               if (is_array($value) && !empty($value)) {
2576                 $clause
2577                   = "(month({$field['dbAlias']}) $op (" . implode(', ', $value) .
2578                   '))';
2579               }
2580             }
2581             else {
2582               $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
2583               $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
2584               $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
2585               $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
2586               $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
2587               $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
2588             }
2589           }
2590           else {
2591             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2592             if ($op) {
2593               $clause = $this->whereClause($field,
2594                 $op,
2595                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
2596                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
2597                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
2598               );
2599             }
2600           }
2601
2602           if (!empty($clause)) {
2603             if (!empty($field['having'])) {
2604               $this->_havingClauses[] = $clause;
2605             }
2606             else {
2607               $this->_whereClauses[] = $clause;
2608             }
2609           }
2610         }
2611       }
2612     }
2613
2614   }
2615
2616   /**
2617    * Set output mode.
2618    */
2619   public function processReportMode() {
2620     $this->setOutputMode();
2621
2622     $this->_sendmail
2623       = CRM_Utils_Request::retrieve(
2624         'sendmail',
2625         'Boolean',
2626         CRM_Core_DAO::$_nullObject
2627       );
2628
2629     $this->_absoluteUrl = FALSE;
2630     $printOnly = FALSE;
2631     $this->assign('printOnly', FALSE);
2632
2633     if ($this->_outputMode == 'print' ||
2634       ($this->_sendmail && !$this->_outputMode)
2635     ) {
2636       $this->assign('printOnly', TRUE);
2637       $printOnly = TRUE;
2638       $this->addPaging = FALSE;
2639       $this->assign('outputMode', 'print');
2640       $this->_outputMode = 'print';
2641       if ($this->_sendmail) {
2642         $this->_absoluteUrl = TRUE;
2643       }
2644     }
2645     elseif ($this->_outputMode == 'pdf') {
2646       $printOnly = TRUE;
2647       $this->addPaging = FALSE;
2648       $this->_absoluteUrl = TRUE;
2649     }
2650     elseif ($this->_outputMode == 'csv') {
2651       $printOnly = TRUE;
2652       $this->_absoluteUrl = TRUE;
2653       $this->addPaging = FALSE;
2654     }
2655     elseif ($this->_outputMode == 'group') {
2656       $this->assign('outputMode', 'group');
2657     }
2658     elseif ($this->_outputMode == 'create_report' && $this->_criteriaForm) {
2659       $this->assign('outputMode', 'create_report');
2660     }
2661     elseif ($this->_outputMode == 'copy' && $this->_criteriaForm) {
2662       $this->_createNew = TRUE;
2663     }
2664
2665     $this->assign('outputMode', $this->_outputMode);
2666     $this->assign('printOnly', $printOnly);
2667     // Get today's date to include in printed reports
2668     if ($printOnly) {
2669       $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
2670       $this->assign('reportDate', $reportDate);
2671     }
2672   }
2673
2674   /**
2675    * Post Processing function for Form.
2676    *
2677    * postProcessCommon should be used to set other variables from input as the api accesses that function.
2678    * This function is not accessed when the api calls the report.
2679    */
2680   public function beginPostProcess() {
2681     $this->setParams($this->controller->exportValues($this->_name));
2682     if (empty($this->_params) &&
2683       $this->_force
2684     ) {
2685       $this->setParams($this->_formValues);
2686     }
2687
2688     // hack to fix params when submitted from dashboard, CRM-8532
2689     // fields array is missing because form building etc is skipped
2690     // in dashboard mode for report
2691     //@todo - this could be done in the dashboard no we have a setter
2692     if (empty($this->_params['fields']) && !$this->_noFields) {
2693       $this->setParams($this->_formValues);
2694     }
2695
2696     $this->_formValues = $this->_params;
2697     if (CRM_Core_Permission::check('administer Reports') &&
2698       isset($this->_id) &&
2699       ($this->_instanceButtonName ==
2700         $this->controller->getButtonName() . '_save' ||
2701         $this->_chartButtonName == $this->controller->getButtonName()
2702       )
2703     ) {
2704       $this->assign('updateReportButton', TRUE);
2705     }
2706
2707     $this->processReportMode();
2708
2709     if ($this->_outputMode == 'save' || $this->_outputMode == 'copy') {
2710       $this->_createNew = ($this->_outputMode == 'copy');
2711       // Do not pass go. Do not collect another chance to re-run the same query.
2712       // This will be called from the button - there is an earlier response to the url
2713       // perhaps they can still be consolidated more.
2714       CRM_Report_Form_Instance::postProcess($this);
2715     }
2716     $this->beginPostProcessCommon();
2717   }
2718
2719   /**
2720    * BeginPostProcess function run in both report mode and non-report mode (api).
2721    */
2722   public function beginPostProcessCommon() {}
2723
2724   /**
2725    * Build the report query.
2726    *
2727    * @param bool $applyLimit
2728    *
2729    * @return string
2730    */
2731   public function buildQuery($applyLimit = TRUE) {
2732     $this->select();
2733     $this->from();
2734     $this->customDataFrom();
2735     $this->where();
2736     $this->groupBy();
2737     $this->orderBy();
2738
2739     // order_by columns not selected for display need to be included in SELECT
2740     $unselectedSectionColumns = $this->unselectedSectionColumns();
2741     foreach ($unselectedSectionColumns as $alias => $section) {
2742       $this->_select .= ", {$section['dbAlias']} as {$alias}";
2743     }
2744
2745     if ($applyLimit && empty($this->_params['charts'])) {
2746       $this->limit();
2747     }
2748     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2749
2750     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2751     $this->addToDeveloperTab($sql);
2752     return $sql;
2753   }
2754
2755   /**
2756    * Build group by clause.
2757    */
2758   public function groupBy() {
2759     $groupBys = array();
2760     if (!empty($this->_params['group_bys']) &&
2761       is_array($this->_params['group_bys'])
2762     ) {
2763       foreach ($this->_columns as $tableName => $table) {
2764         if (array_key_exists('group_bys', $table)) {
2765           foreach ($table['group_bys'] as $fieldName => $field) {
2766             if (!empty($this->_params['group_bys'][$fieldName])) {
2767               $groupBys[] = $field['dbAlias'];
2768             }
2769           }
2770         }
2771       }
2772     }
2773
2774     if (!empty($groupBys)) {
2775       $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
2776     }
2777   }
2778
2779   /**
2780    * Build order by clause.
2781    */
2782   public function orderBy() {
2783     $this->_orderBy = "";
2784     $this->_sections = array();
2785     $this->storeOrderByArray();
2786     if (!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP') {
2787       $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2788     }
2789     $this->assign('sections', $this->_sections);
2790   }
2791
2792   /**
2793    * Extract order by fields and store as an array.
2794    *
2795    * In some cases other functions want to know which fields are selected for ordering by
2796    * Separating this into a separate function allows it to be called separately from constructing
2797    * the order by clause
2798    */
2799   public function storeOrderByArray() {
2800     $orderBys = array();
2801
2802     if (!empty($this->_params['order_bys']) &&
2803       is_array($this->_params['order_bys']) &&
2804       !empty($this->_params['order_bys'])
2805     ) {
2806
2807       // Process order_bys in user-specified order
2808       foreach ($this->_params['order_bys'] as $orderBy) {
2809         $orderByField = array();
2810         foreach ($this->_columns as $tableName => $table) {
2811           if (array_key_exists('order_bys', $table)) {
2812             // For DAO columns defined in $this->_columns
2813             $fields = $table['order_bys'];
2814           }
2815           elseif (array_key_exists('extends', $table)) {
2816             // For custom fields referenced in $this->_customGroupExtends
2817             $fields = CRM_Utils_Array::value('fields', $table, array());
2818           }
2819           else {
2820             continue;
2821           }
2822           if (!empty($fields) && is_array($fields)) {
2823             foreach ($fields as $fieldName => $field) {
2824               if ($fieldName == $orderBy['column']) {
2825                 $orderByField = array_merge($field, $orderBy);
2826                 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2827                 break 2;
2828               }
2829             }
2830           }
2831         }
2832
2833         if (!empty($orderByField)) {
2834           $this->_orderByFields[] = $orderByField;
2835           $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2836
2837           // Record any section headers for assignment to the template
2838           if (!empty($orderBy['section'])) {
2839             $orderByField['pageBreak'] = CRM_Utils_Array::value('pageBreak', $orderBy);
2840             $this->_sections[$orderByField['tplField']] = $orderByField;
2841           }
2842         }
2843       }
2844     }
2845
2846     $this->_orderByArray = $orderBys;
2847
2848     $this->assign('sections', $this->_sections);
2849   }
2850
2851   /**
2852    * Determine unselected columns.
2853    *
2854    * @return array
2855    */
2856   public function unselectedSectionColumns() {
2857     $selectColumns = array();
2858     foreach ($this->_columns as $tableName => $table) {
2859       if (array_key_exists('fields', $table)) {
2860         foreach ($table['fields'] as $fieldName => $field) {
2861           if (!empty($field['required']) ||
2862             !empty($this->_params['fields'][$fieldName])
2863           ) {
2864
2865             $selectColumns["{$tableName}_{$fieldName}"] = 1;
2866           }
2867         }
2868       }
2869     }
2870
2871     if (is_array($this->_sections)) {
2872       return array_diff_key($this->_sections, $selectColumns);
2873     }
2874     else {
2875       return array();
2876     }
2877   }
2878
2879   /**
2880    * Build output rows.
2881    *
2882    * @param string $sql
2883    * @param array $rows
2884    */
2885   public function buildRows($sql, &$rows) {
2886     $dao = CRM_Core_DAO::executeQuery($sql);
2887     if (!is_array($rows)) {
2888       $rows = array();
2889     }
2890
2891     // use this method to modify $this->_columnHeaders
2892     $this->modifyColumnHeaders();
2893
2894     $unselectedSectionColumns = $this->unselectedSectionColumns();
2895
2896     while ($dao->fetch()) {
2897       $row = array();
2898       foreach ($this->_columnHeaders as $key => $value) {
2899         if (property_exists($dao, $key)) {
2900           $row[$key] = $dao->$key;
2901         }
2902       }
2903
2904       // section headers not selected for display need to be added to row
2905       foreach ($unselectedSectionColumns as $key => $values) {
2906         if (property_exists($dao, $key)) {
2907           $row[$key] = $dao->$key;
2908         }
2909       }
2910
2911       $rows[] = $row;
2912     }
2913   }
2914
2915   /**
2916    * Calculate section totals.
2917    *
2918    * When "order by" fields are marked as sections, this assigns to the template
2919    * an array of total counts for each section. This data is used by the Smarty
2920    * plugin {sectionTotal}.
2921    */
2922   public function sectionTotals() {
2923
2924     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
2925     if (empty($this->_selectAliases)) {
2926       return;
2927     }
2928
2929     if (!empty($this->_sections)) {
2930       // build the query with no LIMIT clause
2931       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
2932       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
2933
2934       // pull section aliases out of $this->_sections
2935       $sectionAliases = array_keys($this->_sections);
2936
2937       $ifnulls = array();
2938       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
2939         $ifnulls[] = "ifnull($alias, '') as $alias";
2940       }
2941
2942       // Group (un-limited) report by all aliases and get counts. This might
2943       // be done more efficiently when the contents of $sql are known, ie. by
2944       // overriding this method in the report class.
2945
2946       $query = "select " . implode(", ", $ifnulls) .
2947         ", count(*) as ct from ($sql) as subquery group by " .
2948         implode(", ", $sectionAliases);
2949
2950       // initialize array of total counts
2951       $totals = array();
2952       $dao = CRM_Core_DAO::executeQuery($query);
2953       while ($dao->fetch()) {
2954
2955         // let $this->_alterDisplay translate any integer ids to human-readable values.
2956         $rows[0] = $dao->toArray();
2957         $this->alterDisplay($rows);
2958         $row = $rows[0];
2959
2960         // add totals for all permutations of section values
2961         $values = array();
2962         $i = 1;
2963         $aliasCount = count($sectionAliases);
2964         foreach ($sectionAliases as $alias) {
2965           $values[] = $row[$alias];
2966           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
2967           if ($i == $aliasCount) {
2968             // the last alias is the lowest-level section header; use count as-is
2969             $totals[$key] = $dao->ct;
2970           }
2971           else {
2972             // other aliases are higher level; roll count into their total
2973             $totals[$key] += $dao->ct;
2974           }
2975         }
2976       }
2977       $this->assign('sectionTotals', $totals);
2978     }
2979   }
2980
2981   /**
2982    * Modify column headers.
2983    */
2984   public function modifyColumnHeaders() {
2985     // use this method to modify $this->_columnHeaders
2986   }
2987
2988   /**
2989    * Assign rows to the template.
2990    *
2991    * @param array $rows
2992    */
2993   public function doTemplateAssignment(&$rows) {
2994     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
2995     $this->assign_by_ref('rows', $rows);
2996     $this->assign('statistics', $this->statistics($rows));
2997   }
2998
2999   /**
3000    * Build report statistics.
3001    *
3002    * Override this method to build your own statistics.
3003    *
3004    * @param array $rows
3005    *
3006    * @return array
3007    */
3008   public function statistics(&$rows) {
3009     $statistics = array();
3010
3011     $count = count($rows);
3012
3013     if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
3014       $count++;
3015     }
3016
3017     $this->countStat($statistics, $count);
3018
3019     $this->groupByStat($statistics);
3020
3021     $this->filterStat($statistics);
3022
3023     return $statistics;
3024   }
3025
3026   /**
3027    * Add count statistics.
3028    *
3029    * @param array $statistics
3030    * @param int $count
3031    */
3032   public function countStat(&$statistics, $count) {
3033     $statistics['counts']['rowCount'] = array(
3034       'title' => ts('Row(s) Listed'),
3035       'value' => $count,
3036     );
3037
3038     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
3039       $statistics['counts']['rowsFound'] = array(
3040         'title' => ts('Total Row(s)'),
3041         'value' => $this->_rowsFound,
3042       );
3043     }
3044   }
3045
3046   /**
3047    * Add group by statistics.
3048    *
3049    * @param array $statistics
3050    */
3051   public function groupByStat(&$statistics) {
3052     if (!empty($this->_params['group_bys']) &&
3053       is_array($this->_params['group_bys']) &&
3054       !empty($this->_params['group_bys'])
3055     ) {
3056       foreach ($this->_columns as $tableName => $table) {
3057         if (array_key_exists('group_bys', $table)) {
3058           foreach ($table['group_bys'] as $fieldName => $field) {
3059             if (!empty($this->_params['group_bys'][$fieldName])) {
3060               $combinations[] = $field['title'];
3061             }
3062           }
3063         }
3064       }
3065       $statistics['groups'][] = array(
3066         'title' => ts('Grouping(s)'),
3067         'value' => implode(' & ', $combinations),
3068       );
3069     }
3070   }
3071
3072   /**
3073    * Filter statistics.
3074    *
3075    * @param array $statistics
3076    */
3077   public function filterStat(&$statistics) {
3078     foreach ($this->_columns as $tableName => $table) {
3079       if (array_key_exists('filters', $table)) {
3080         foreach ($table['filters'] as $fieldName => $field) {
3081           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE &&
3082             CRM_Utils_Array::value('operatorType', $field) !=
3083             CRM_Report_Form::OP_MONTH
3084           ) {
3085             list($from, $to)
3086               = $this->getFromTo(
3087                 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3088                 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
3089                 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
3090                 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
3091                 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
3092               );
3093             $from_time_format = !empty($this->_params["{$fieldName}_from_time"]) ? 'h' : 'd';
3094             $from = CRM_Utils_Date::customFormat($from, NULL, array($from_time_format));
3095
3096             $to_time_format = !empty($this->_params["{$fieldName}_to_time"]) ? 'h' : 'd';
3097             $to = CRM_Utils_Date::customFormat($to, NULL, array($to_time_format));
3098
3099             if ($from || $to) {
3100               $statistics['filters'][] = array(
3101                 'title' => $field['title'],
3102                 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
3103               );
3104             }
3105             elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
3106               array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE))
3107             )) {
3108               $pair = $this->getOperationPair(CRM_Report_Form::OP_DATE);
3109               $statistics['filters'][] = array(
3110                 'title' => $field['title'],
3111                 'value' => $pair[$rel],
3112               );
3113             }
3114           }
3115           else {
3116             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
3117             $value = NULL;
3118             if ($op) {
3119               $pair = $this->getOperationPair(
3120                 CRM_Utils_Array::value('operatorType', $field),
3121                 $fieldName
3122               );
3123               $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
3124               $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
3125               $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
3126               if (in_array($op, array('bw', 'nbw')) && ($min || $max)) {
3127                 $value = "{$pair[$op]} $min " . ts('and') . " $max";
3128               }
3129               elseif ($val && CRM_Utils_Array::value('operatorType', $field) & self::OP_ENTITYREF) {
3130                 $this->setEntityRefDefaults($field, $tableName);
3131                 $result = civicrm_api3($field['attributes']['entity'], 'getlist',
3132                   array('id' => $val) +
3133                   CRM_Utils_Array::value('api', $field['attributes'], array()));
3134                 $values = array();
3135                 foreach ($result['values'] as $v) {
3136                   $values[] = $v['label'];
3137                 }
3138                 $value = "{$pair[$op]} " . implode(', ', $values);
3139               }
3140               elseif ($op == 'nll' || $op == 'nnll') {
3141                 $value = $pair[$op];
3142               }
3143               elseif (is_array($val) && (!empty($val))) {
3144                 $options = CRM_Utils_Array::value('options', $field, array());
3145                 foreach ($val as $key => $valIds) {
3146                   if (isset($options[$valIds])) {
3147                     $val[$key] = $options[$valIds];
3148                   }
3149                 }
3150                 $pair[$op] = (count($val) == 1) ? (($op == 'notin' || $op ==
3151                   'mnot') ? ts('Is Not') : ts('Is')) : CRM_Utils_Array::value($op, $pair);
3152                 $val = implode(', ', $val);
3153                 $value = "{$pair[$op]} " . $val;
3154               }
3155               elseif (!is_array($val) && (!empty($val) || $val == '0') &&
3156                 isset($field['options']) &&
3157                 is_array($field['options']) && !empty($field['options'])
3158               ) {
3159                 $value = CRM_Utils_Array::value($op, $pair) . " " .
3160                   CRM_Utils_Array::value($val, $field['options'], $val);
3161               }
3162               elseif ($val) {
3163                 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
3164               }
3165             }
3166             if ($value) {
3167               $statistics['filters'][] = array(
3168                 'title' => CRM_Utils_Array::value('title', $field),
3169                 'value' => $value,
3170               );
3171             }
3172           }
3173         }
3174       }
3175     }
3176   }
3177
3178   /**
3179    * End post processing.
3180    *
3181    * @param array|null $rows
3182    */
3183   public function endPostProcess(&$rows = NULL) {
3184     if ($this->_storeResultSet) {
3185       $this->_resultSet = $rows;
3186     }
3187
3188     if ($this->_outputMode == 'print' ||
3189       $this->_outputMode == 'pdf' ||
3190       $this->_sendmail
3191     ) {
3192
3193       $content = $this->compileContent();
3194       $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
3195         "reset=1", TRUE
3196       );
3197
3198       if ($this->_sendmail) {
3199         $config = CRM_Core_Config::singleton();
3200         $attachments = array();
3201
3202         if ($this->_outputMode == 'csv') {
3203           $content
3204             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3205             ": {$url}</p>" . '<p>' .
3206             ts('The report is attached as a CSV file.') . '</p>' .
3207             $this->_formValues['report_footer'];
3208
3209           $csvFullFilename = $config->templateCompileDir .
3210             CRM_Utils_File::makeFileName('CiviReport.csv');
3211           $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
3212           file_put_contents($csvFullFilename, $csvContent);
3213           $attachments[] = array(
3214             'fullPath' => $csvFullFilename,
3215             'mime_type' => 'text/csv',
3216             'cleanName' => 'CiviReport.csv',
3217           );
3218         }
3219         if ($this->_outputMode == 'pdf') {
3220           // generate PDF content
3221           $pdfFullFilename = $config->templateCompileDir .
3222             CRM_Utils_File::makeFileName('CiviReport.pdf');
3223           file_put_contents($pdfFullFilename,
3224             CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
3225               TRUE, array('orientation' => 'landscape')
3226             )
3227           );
3228           // generate Email Content
3229           $content
3230             = $this->_formValues['report_header'] . '<p>' . ts('Report URL') .
3231             ": {$url}</p>" . '<p>' .
3232             ts('The report is attached as a PDF file.') . '</p>' .
3233             $this->_formValues['report_footer'];
3234
3235           $attachments[] = array(
3236             'fullPath' => $pdfFullFilename,
3237             'mime_type' => 'application/pdf',
3238             'cleanName' => 'CiviReport.pdf',
3239           );
3240         }
3241
3242         if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
3243           $this->_outputMode, $attachments
3244         )
3245         ) {
3246           CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
3247         }
3248         else {
3249           CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
3250         }
3251         return TRUE;
3252       }
3253       elseif ($this->_outputMode == 'print') {
3254         echo $content;
3255       }
3256       else {
3257         if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
3258           $config = CRM_Core_Config::singleton();
3259           //get chart image name
3260           $chartImg = $this->_chartId . '.png';
3261           //get image url path
3262           $uploadUrl
3263             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) .
3264             'openFlashChart/';
3265           $uploadUrl .= $chartImg;
3266           //get image doc path to overwrite
3267           $uploadImg
3268             = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
3269             'openFlashChart/' . $chartImg;
3270           //Load the image
3271           $chart = imagecreatefrompng($uploadUrl);
3272           //convert it into formatted png
3273           CRM_Utils_System::setHttpHeader('Content-type', 'image/png');
3274           //overwrite with same image
3275           imagepng($chart, $uploadImg);
3276           //delete the object
3277           imagedestroy($chart);
3278         }
3279         CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
3280       }
3281       CRM_Utils_System::civiExit();
3282     }
3283     elseif ($this->_outputMode == 'csv') {
3284       CRM_Report_Utils_Report::export2csv($this, $rows);
3285     }
3286     elseif ($this->_outputMode == 'group') {
3287       $group = $this->_params['groups'];
3288       $this->add2group($group);
3289     }
3290   }
3291
3292   /**
3293    * Set store result set indicator to TRUE.
3294    *
3295    * @todo explain what this does
3296    */
3297   public function storeResultSet() {
3298     $this->_storeResultSet = TRUE;
3299   }
3300
3301   /**
3302    * Get result set.
3303    *
3304    * @return bool
3305    */
3306   public function getResultSet() {
3307     return $this->_resultSet;
3308   }
3309
3310   /**
3311    * Use the form name to create the tpl file name.
3312    *
3313    * @return string
3314    */
3315   public function getTemplateFileName() {
3316     $defaultTpl = parent::getTemplateFileName();
3317     $template = CRM_Core_Smarty::singleton();
3318     if (!$template->template_exists($defaultTpl)) {
3319       $defaultTpl = 'CRM/Report/Form.tpl';
3320     }
3321     return $defaultTpl;
3322   }
3323
3324   /**
3325    * Compile the report content.
3326    *
3327    * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
3328    *
3329    * @return string
3330    */
3331   public function compileContent() {
3332     $templateFile = $this->getHookedTemplateFileName();
3333     return $this->_formValues['report_header'] .
3334     CRM_Core_Form::$_template->fetch($templateFile) .
3335     $this->_formValues['report_footer'];
3336   }
3337
3338
3339   /**
3340    * Post process function.
3341    */
3342   public function postProcess() {
3343     // get ready with post process params
3344     $this->beginPostProcess();
3345
3346     // build query
3347     $sql = $this->buildQuery();
3348
3349     // build array of result based on column headers. This method also allows
3350     // modifying column headers before using it to build result set i.e $rows.
3351     $rows = array();
3352     $this->buildRows($sql, $rows);
3353
3354     // format result set.
3355     $this->formatDisplay($rows);
3356
3357     // assign variables to templates
3358     $this->doTemplateAssignment($rows);
3359
3360     // do print / pdf / instance stuff if needed
3361     $this->endPostProcess($rows);
3362   }
3363
3364   /**
3365    * Set limit.
3366    *
3367    * @param int $rowCount
3368    *
3369    * @return array
3370    */
3371   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
3372     // lets do the pager if in html mode
3373     $this->_limit = NULL;
3374
3375     // CRM-14115, over-ride row count if rowCount is specified in URL
3376     if ($this->_dashBoardRowCount) {
3377       $rowCount = $this->_dashBoardRowCount;
3378     }
3379     if ($this->addPaging) {
3380       $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
3381
3382       $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
3383
3384       // @todo all http vars should be extracted in the preProcess
3385       // - not randomly in the class
3386       if (!$pageId && !empty($_POST)) {
3387         if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
3388           $pageId = max((int) $_POST['crmPID_B'], 1);
3389         }
3390         elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
3391           $pageId = max((int) $_POST['crmPID'], 1);
3392         }
3393         unset($_POST['crmPID_B'], $_POST['crmPID']);
3394       }
3395
3396       $pageId = $pageId ? $pageId : 1;
3397       $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
3398       $offset = ($pageId - 1) * $rowCount;
3399
3400       $offset = CRM_Utils_Type::escape($offset, 'Int');
3401       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
3402
3403       $this->_limit = " LIMIT $offset, $rowCount";
3404       return array($offset, $rowCount);
3405     }
3406     if ($this->_limitValue) {
3407       if ($this->_offsetValue) {
3408         $this->_limit = " LIMIT {$this->_offsetValue}, {$this->_limitValue} ";
3409       }
3410       else {
3411         $this->_limit = " LIMIT " . $this->_limitValue;
3412       }
3413     }
3414   }
3415
3416   /**
3417    * Set pager.
3418    *
3419    * @param int $rowCount
3420    */
3421   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
3422
3423     // CRM-14115, over-ride row count if rowCount is specified in URL
3424     if ($this->_dashBoardRowCount) {
3425       $rowCount = $this->_dashBoardRowCount;
3426     }
3427
3428     if ($this->_limit && ($this->_limit != '')) {
3429       $sql = "SELECT FOUND_ROWS();";
3430       $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
3431       $params = array(
3432         'total' => $this->_rowsFound,
3433         'rowCount' => $rowCount,
3434         'status' => ts('Records') . ' %%StatusMessage%%',
3435         'buttonBottom' => 'PagerBottomButton',
3436         'buttonTop' => 'PagerTopButton',
3437         'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
3438       );
3439
3440       $pager = new CRM_Utils_Pager($params);
3441       $this->assign_by_ref('pager', $pager);
3442       $this->ajaxResponse['totalRows'] = $this->_rowsFound;
3443     }
3444   }
3445
3446   /**
3447    * Build where clause for groups.
3448    *
3449    * @param string $field
3450    * @param mixed $value
3451    * @param string $op
3452    *
3453    * @return string
3454    */
3455   public function whereGroupClause($field, $value, $op) {
3456
3457     $smartGroupQuery = "";
3458
3459     $group = new CRM_Contact_DAO_Group();
3460     $group->is_active = 1;
3461     $group->find();
3462     $smartGroups = array();
3463     while ($group->fetch()) {
3464       if (in_array($group->id, $this->_params['gid_value']) &&
3465         $group->saved_search_id
3466       ) {
3467         $smartGroups[] = $group->id;
3468       }
3469     }
3470
3471     CRM_Contact_BAO_GroupContactCache::check($smartGroups);
3472
3473     $smartGroupQuery = '';
3474     if (!empty($smartGroups)) {
3475       $smartGroups = implode(',', $smartGroups);
3476       $smartGroupQuery = " UNION DISTINCT
3477                   SELECT DISTINCT smartgroup_contact.contact_id
3478                   FROM civicrm_group_contact_cache smartgroup_contact
3479                   WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
3480     }
3481
3482     $sqlOp = $this->getSQLOperator($op);
3483     if (!is_array($value)) {
3484       $value = array($value);
3485     }
3486     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3487
3488     $contactAlias = $this->_aliases['civicrm_contact'];
3489     if (!empty($this->relationType) && $this->relationType == 'b_a') {
3490       $contactAlias = $this->_aliases['civicrm_contact_b'];
3491     }
3492     return " {$contactAlias}.id {$sqlOp} (
3493                           SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
3494                           FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
3495                           WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
3496                           {$smartGroupQuery} ) ";
3497   }
3498
3499   /**
3500    * Build where clause for tags.
3501    *
3502    * @param string $field
3503    * @param mixed $value
3504    * @param string $op
3505    *
3506    * @return string
3507    */
3508   public function whereTagClause($field, $value, $op) {
3509     // not using left join in query because if any contact
3510     // belongs to more than one tag, results duplicate
3511     // entries.
3512     $sqlOp = $this->getSQLOperator($op);
3513     if (!is_array($value)) {
3514       $value = array($value);
3515     }
3516     $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
3517     $entity_table = $this->_tagFilterTable;
3518     return " {$this->_aliases[$entity_table]}.id {$sqlOp} (
3519                           SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
3520                           FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
3521                           WHERE entity_table = '$entity_table' AND {$clause} ) ";
3522   }
3523
3524   /**
3525    * Generate membership organization clause.
3526    *
3527    * @param mixed $value
3528    * @param string $op SQL Operator
3529    *
3530    * @return string
3531    */
3532   public function whereMembershipOrgClause($value, $op) {
3533     $sqlOp = $this->getSQLOperator($op);
3534     if (!is_array($value)) {
3535       $value = array($value);
3536     }
3537
3538     $tmp_membership_org_sql_list = implode(', ', $value);
3539     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3540                           SELECT DISTINCT mem.contact_id
3541                           FROM civicrm_membership mem
3542                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3543                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3544                           WHERE mt.member_of_contact_id IN (" .
3545     $tmp_membership_org_sql_list . ")
3546                           AND mt.is_active = '1'
3547                           AND mem_status.is_current_member = '1'
3548                           AND mem_status.is_active = '1' )  ";
3549   }
3550
3551   /**
3552    * Generate Membership Type SQL Clause.
3553    *
3554    * @param mixed $value
3555    * @param string $op
3556    *
3557    * @return string
3558    *   SQL query string
3559    */
3560   public function whereMembershipTypeClause($value, $op) {
3561     $sqlOp = $this->getSQLOperator($op);
3562     if (!is_array($value)) {
3563       $value = array($value);
3564     }
3565
3566     $tmp_membership_sql_list = implode(', ', $value);
3567     return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
3568                           SELECT DISTINCT mem.contact_id
3569                           FROM civicrm_membership mem
3570                           LEFT JOIN civicrm_membership_status mem_status ON mem.status_id = mem_status.id
3571                           LEFT JOIN civicrm_membership_type mt ON mem.membership_type_id = mt.id
3572                           WHERE mem.membership_type_id IN (" .
3573     $tmp_membership_sql_list . ")
3574                           AND mt.is_active = '1'
3575                           AND mem_status.is_current_member = '1'
3576                           AND mem_status.is_active = '1' ) ";
3577   }
3578
3579   /**
3580    * Build acl clauses.
3581    *
3582    * @param string $tableAlias
3583    */
3584   public function buildACLClause($tableAlias = 'contact_a') {
3585     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
3586   }
3587
3588   /**
3589    * Add custom data to the columns.
3590    *
3591    * @param bool $addFields
3592    * @param array $permCustomGroupIds
3593    */
3594   public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
3595     if (empty($this->_customGroupExtends)) {
3596       return;
3597     }
3598     if (!is_array($this->_customGroupExtends)) {
3599       $this->_customGroupExtends = array($this->_customGroupExtends);
3600     }
3601     $customGroupWhere = '';
3602     if (!empty($permCustomGroupIds)) {
3603       $customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) .
3604         ") AND";
3605     }
3606     $sql = "
3607 SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
3608        cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
3609 FROM   civicrm_custom_group cg
3610 INNER  JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
3611 WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
3612       {$customGroupWhere}
3613       cg.is_active = 1 AND
3614       cf.is_active = 1 AND
3615       cf.is_searchable = 1
3616 ORDER BY cg.weight, cf.weight";
3617     $customDAO = CRM_Core_DAO::executeQuery($sql);
3618
3619     $curTable = NULL;
3620     while ($customDAO->fetch()) {
3621       if ($customDAO->table_name != $curTable) {
3622         $curTable = $customDAO->table_name;
3623         $curFields = $curFilters = array();
3624
3625         // dummy dao object
3626         $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
3627         $this->_columns[$curTable]['extends'] = $customDAO->extends;
3628         $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
3629         $this->_columns[$curTable]['group_title'] = $customDAO->title;
3630
3631         foreach (array(
3632                    'fields',
3633                    'filters',
3634                    'group_bys',
3635                  ) as $colKey) {
3636           if (!array_key_exists($colKey, $this->_columns[$curTable])) {
3637             $this->_columns[$curTable][$colKey] = array();
3638           }
3639         }
3640       }
3641       $fieldName = 'custom_' . $customDAO->cf_id;
3642
3643       if ($addFields) {
3644         // this makes aliasing work in favor
3645         $curFields[$fieldName] = array(
3646           'name' => $customDAO->column_name,
3647           'title' => $customDAO->label,
3648           'dataType' => $customDAO->data_type,
3649           'htmlType' => $customDAO->html_type,
3650         );
3651       }
3652       if ($this->_customGroupFilters) {
3653         // this makes aliasing work in favor
3654         $curFilters[$fieldName] = array(
3655           'name' => $customDAO->column_name,
3656           'title' => $customDAO->label,
3657           'dataType' => $customDAO->data_type,
3658           'htmlType' => $customDAO->html_type,
3659         );
3660       }
3661
3662       switch ($customDAO->data_type) {
3663         case 'Date':
3664           // filters
3665           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
3666           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
3667           // CRM-6946, show time part for datetime date fields
3668           if ($customDAO->time_format) {
3669             $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
3670           }
3671           break;
3672
3673         case 'Boolean':
3674           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
3675           $curFilters[$fieldName]['options'] = array(
3676             '' => ts('- select -'),
3677             1 => ts('Yes'),
3678             0 => ts('No'),
3679           );
3680           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3681           break;
3682
3683         case 'Int':
3684           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
3685           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
3686           break;
3687
3688         case 'Money':
3689           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3690           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
3691           break;
3692
3693         case 'Float':
3694           $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
3695           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
3696           break;
3697
3698         case 'String':
3699           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3700
3701           if (!empty($customDAO->option_group_id)) {
3702             if (in_array($customDAO->html_type, array(
3703               'Multi-Select',
3704               'AdvMulti-Select',
3705               'CheckBox',
3706             ))) {
3707               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3708             }
3709             else {
3710               $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3711             }
3712             if ($this->_customGroupFilters) {
3713               $curFilters[$fieldName]['options'] = array();
3714               $ogDAO = CRM_Core_DAO::executeQuery("SELECT ov.value, ov.label FROM civicrm_option_value ov WHERE ov.option_group_id = %1 ORDER BY ov.weight", array(
3715                 1 => array(
3716                   $customDAO->option_group_id,
3717                   'Integer',
3718                 ),
3719               ));
3720               while ($ogDAO->fetch()) {
3721                 $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label;
3722               }
3723               CRM_Utils_Hook::customFieldOptions($customDAO->cf_id, $curFilters[$fieldName]['options'], FALSE);
3724             }
3725           }
3726           break;
3727
3728         case 'StateProvince':
3729           if (in_array($customDAO->html_type, array(
3730             'Multi-Select State/Province',
3731           ))) {
3732             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3733           }
3734           else {
3735             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3736           }
3737           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince();
3738           break;
3739
3740         case 'Country':
3741           if (in_array($customDAO->html_type, array(
3742             'Multi-Select Country',
3743           ))) {
3744             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
3745           }
3746           else {
3747             $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
3748           }
3749           $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country();
3750           break;
3751
3752         case 'ContactReference':
3753           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3754           $curFilters[$fieldName]['name'] = 'display_name';
3755           $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3756
3757           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3758           $curFields[$fieldName]['name'] = 'display_name';
3759           $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
3760           break;
3761
3762         default:
3763           $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3764           $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
3765       }
3766
3767       if (!array_key_exists('type', $curFields[$fieldName])) {
3768         $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
3769       }
3770
3771       if ($addFields) {
3772         $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
3773       }
3774       if ($this->_customGroupFilters) {
3775         $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
3776       }
3777       if ($this->_customGroupGroupBy) {
3778         $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
3779       }
3780     }
3781   }
3782
3783   /**
3784    * Build custom data from clause.
3785    */
3786   public function customDataFrom() {
3787     if (empty($this->_customGroupExtends)) {
3788       return;
3789     }
3790     $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
3791
3792     foreach ($this->_columns as $table => $prop) {
3793       if (substr($table, 0, 13) == 'civicrm_value' ||
3794         substr($table, 0, 12) == 'custom_value'
3795       ) {
3796         $extendsTable = $mapper[$prop['extends']];
3797
3798         // check field is in params
3799         if (!$this->isFieldSelected($prop)) {
3800           continue;
3801         }
3802         $baseJoin = CRM_Utils_Array::value($prop['extends'], $this->_customGroupExtendsJoin, "{$this->_aliases[$extendsTable]}.id");
3803
3804         $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
3805         $this->_from .= "
3806 {$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$baseJoin}";
3807         // handle for ContactReference
3808         if (array_key_exists('fields', $prop)) {
3809           foreach ($prop['fields'] as $fieldName => $field) {
3810             if (CRM_Utils_Array::value('dataType', $field) ==
3811               'ContactReference'
3812             ) {
3813               $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
3814               $this->_from .= "
3815 LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
3816             }
3817           }
3818         }
3819       }
3820     }
3821   }
3822
3823   /**
3824    * Check if the field is selected.
3825    *
3826    * @param string $prop
3827    *
3828    * @return bool
3829    */
3830   public function isFieldSelected($prop) {
3831     if (empty($prop)) {
3832       return FALSE;
3833     }
3834
3835     if (!empty($this->_params['fields'])) {
3836       foreach (array_keys($prop['fields']) as $fieldAlias) {
3837         $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
3838         if ($customFieldId) {
3839           if (array_key_exists($fieldAlias, $this->_params['fields'])) {
3840             return TRUE;
3841           }
3842
3843           //might be survey response field.
3844           if (!empty($this->_params['fields']['survey_response']) &&
3845             !empty($prop['fields'][$fieldAlias]['isSurveyResponseField'])
3846           ) {
3847             return TRUE;
3848           }
3849         }
3850       }
3851     }
3852
3853     if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
3854       foreach (array_keys($prop['group_bys']) as $fieldAlias) {
3855         if (array_key_exists($fieldAlias, $this->_params['group_bys']) &&
3856           CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3857         ) {
3858           return TRUE;
3859         }
3860       }
3861     }
3862
3863     if (!empty($this->_params['order_bys'])) {
3864       foreach (array_keys($prop['fields']) as $fieldAlias) {
3865         foreach ($this->_params['order_bys'] as $orderBy) {
3866           if ($fieldAlias == $orderBy['column'] &&
3867             CRM_Core_BAO_CustomField::getKeyID($fieldAlias)
3868           ) {
3869             return TRUE;
3870           }
3871         }
3872       }
3873     }
3874
3875     if (!empty($prop['filters']) && $this->_customGroupFilters) {
3876       foreach ($prop['filters'] as $fieldAlias => $val) {
3877         foreach (array(
3878                    'value',
3879                    'min',
3880                    'max',
3881                    'relative',
3882                    'from',
3883                    'to',
3884                  ) as $attach) {
3885           if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
3886             (!empty($this->_params[$fieldAlias . '_' . $attach])
3887               || ($attach != 'relative' &&
3888                 $this->_params[$fieldAlias . '_' . $attach] == '0')
3889             )
3890           ) {
3891             return TRUE;
3892           }
3893         }
3894         if (!empty($this->_params[$fieldAlias . '_op']) &&
3895           in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
3896         ) {
3897           return TRUE;
3898         }
3899       }
3900     }
3901
3902     return FALSE;
3903   }
3904
3905   /**
3906    * Check for empty order_by configurations and remove them.
3907    *
3908    * Also set template to hide them.
3909    *
3910    * @param array $formValues
3911    */
3912   public function preProcessOrderBy(&$formValues) {
3913     // Object to show/hide form elements
3914     $_showHide = new CRM_Core_ShowHideBlocks('', '');
3915
3916     $_showHide->addShow('optionField_1');
3917
3918     // Cycle through order_by options; skip any empty ones, and hide them as well
3919     $n = 1;
3920
3921     if (!empty($formValues['order_bys'])) {
3922       foreach ($formValues['order_bys'] as $order_by) {
3923         if ($order_by['column'] && $order_by['column'] != '-') {
3924           $_showHide->addShow('optionField_' . $n);
3925           $orderBys[$n] = $order_by;
3926           $n++;
3927         }
3928       }
3929     }
3930     for ($i = $n; $i <= 5; $i++) {
3931       if ($i > 1) {
3932         $_showHide->addHide('optionField_' . $i);
3933       }
3934     }
3935
3936     // overwrite order_by options with modified values
3937     if (!empty($orderBys)) {
3938       $formValues['order_bys'] = $orderBys;
3939     }
3940     else {
3941       $formValues['order_bys'] = array(1 => array('column' => '-'));
3942     }
3943
3944     // assign show/hide data to template
3945     $_showHide->addToTemplate();
3946   }
3947
3948   /**
3949    * Check if table name has columns in SELECT clause.
3950    *
3951    * @param string $tableName
3952    *   Name of table (index of $this->_columns array).
3953    *
3954    * @return bool
3955    */
3956   public function isTableSelected($tableName) {
3957     return in_array($tableName, $this->selectedTables());
3958   }
3959
3960   /**
3961    * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause.
3962    *
3963    * If the array is unset it will be built.
3964    *
3965    * @return array
3966    *   selectedTables
3967    */
3968   public function selectedTables() {
3969     if (!$this->_selectedTables) {
3970       $orderByColumns = array();
3971       if (array_key_exists('order_bys', $this->_params) &&
3972         is_array($this->_params['order_bys'])
3973       ) {
3974         foreach ($this->_params['order_bys'] as $orderBy) {
3975           $orderByColumns[] = $orderBy['column'];
3976         }
3977       }
3978
3979       foreach ($this->_columns as $tableName => $table) {
3980         if (array_key_exists('fields', $table)) {
3981           foreach ($table['fields'] as $fieldName => $field) {
3982             if (!empty($field['required']) ||
3983               !empty($this->_params['fields'][$fieldName])
3984             ) {
3985               $this->_selectedTables[] = $tableName;
3986               break;
3987             }
3988           }
3989         }
3990         if (array_key_exists('order_bys', $table)) {
3991           foreach ($table['order_bys'] as $orderByName => $orderBy) {
3992             if (in_array($orderByName, $orderByColumns)) {
3993               $this->_selectedTables[] = $tableName;
3994               break;
3995             }
3996           }
3997         }
3998         if (array_key_exists('filters', $table)) {
3999           foreach ($table['filters'] as $filterName => $filter) {
4000             if (!empty($this->_params["{$filterName}_value"]) ||
4001               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
4002               'nll' ||
4003               CRM_Utils_Array::value("{$filterName}_op", $this->_params) ==
4004               'nnll'
4005             ) {
4006               $this->_selectedTables[] = $tableName;
4007               break;
4008             }
4009           }
4010         }
4011       }
4012     }
4013     return $this->_selectedTables;
4014   }
4015
4016   /**
4017    * Add address fields.
4018    *
4019    * @deprecated - use getAddressColumns which is a more accurate description
4020    * and also accepts an array of options rather than a long list
4021    *
4022    * adding address fields to construct function in reports
4023    *
4024    * @param bool $groupBy
4025    *   Add GroupBy? Not appropriate for detail report.
4026    * @param bool $orderBy
4027    *   Add GroupBy? Not appropriate for detail report.
4028    * @param bool $filters
4029    * @param array $defaults
4030    *
4031    * @return array
4032    *   address fields for construct clause
4033    */
4034   public function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array('country_id' => TRUE)) {
4035     $addressFields = array(
4036       'civicrm_address' => array(
4037         'dao' => 'CRM_Core_DAO_Address',
4038         'fields' => array(
4039           'address_name' => array(
4040             'title' => ts('Address Name'),
4041             'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
4042             'name' => 'name',
4043           ),
4044           'street_address' => array(
4045             'title' => ts('Street Address'),
4046             'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
4047           ),
4048           'supplemental_address_1' => array(
4049             'title' => ts('Supplementary Address Field 1'),
4050             'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
4051           ),
4052           'supplemental_address_2' => array(
4053             'title' => ts('Supplementary Address Field 2'),
4054             'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
4055           ),
4056           'street_number' => array(
4057             'name' => 'street_number',
4058             'title' => ts('Street Number'),
4059             'type' => 1,
4060             'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
4061           ),
4062           'street_name' => array(
4063             'name' => 'street_name',
4064             'title' => ts('Street Name'),
4065             'type' => 1,
4066             'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
4067           ),
4068           'street_unit' => array(
4069             'name' => 'street_unit',
4070             'title' => ts('Street Unit'),
4071             'type' => 1,
4072             'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
4073           ),
4074           'city' => array(
4075             'title' => ts('City'),
4076             'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
4077           ),
4078           'postal_code' => array(
4079             'title' => ts('Postal Code'),
4080             'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
4081           ),
4082           'postal_code_suffix' => array(
4083             'title' => ts('Postal Code Suffix'),
4084             'default' => CRM_Utils_Array::value('postal_code_suffix', $defaults, FALSE),
4085           ),
4086           'country_id' => array(
4087             'title' => ts('Country'),
4088             'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
4089           ),
4090           'state_province_id' => array(
4091             'title' => ts('State/Province'),
4092             'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
4093           ),
4094           'county_id' => array(
4095             'title' => ts('County'),
4096             'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
4097           ),
4098         ),
4099         'grouping' => 'location-fields',
4100       ),
4101     );
4102
4103     if ($filters) {
4104       $addressFields['civicrm_address']['filters'] = array(
4105         'street_number' => array(
4106           'title' => ts('Street Number'),
4107           'type' => 1,
4108           'name' => 'street_number',
4109         ),
4110         'street_name' => array(
4111           'title' => ts('Street Name'),
4112           'name' => 'street_name',
4113           'operator' => 'like',
4114         ),
4115         'postal_code' => array(
4116           'title' => ts('Postal Code'),
4117           'type' => 1,
4118           'name' => 'postal_code',
4119         ),
4120         'city' => array(
4121           'title' => ts('City'),
4122           'operator' => 'like',
4123           'name' => 'city',
4124         ),
4125         'country_id' => array(
4126           'name' => 'country_id',
4127           'title' => ts('Country'),
4128           'type' => CRM_Utils_Type::T_INT,
4129           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4130           'options' => CRM_Core_PseudoConstant::country(),
4131         ),
4132         'state_province_id' => array(
4133           'name' => 'state_province_id',
4134           'title' => ts('State/Province'),
4135           'type' => CRM_Utils_Type::T_INT,
4136           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4137           'options' => array(),
4138         ),
4139         'county_id' => array(
4140           'name' => 'county_id',
4141           'title' => ts('County'),
4142           'type' => CRM_Utils_Type::T_INT,
4143           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
4144           'options' => array(),
4145         ),
4146       );
4147     }
4148
4149     if ($orderBy) {
4150       $addressFields['civicrm_address']['order_bys'] = array(
4151         'street_name' => array('title' => ts('Street Name')),
4152         'street_number' => array('title' => 'Odd / Even Street Number'),
4153         'street_address' => NULL,
4154         'city' => NULL,
4155         'postal_code' => NULL,
4156       );
4157     }
4158
4159     if ($groupBy) {
4160       $addressFields['civicrm_address']['group_bys'] = array(
4161         'street_address' => NULL,
4162         'city' => NULL,
4163         'postal_code' => NULL,
4164         'state_province_id' => array(
4165           'title' => ts('State/Province'),
4166         ),
4167         'country_id' => array(
4168           'title' => ts('Country'),
4169         ),
4170         'county_id' => array(
4171           'title' => ts('County'),
4172         ),
4173       );
4174     }
4175     return $addressFields;
4176   }
4177
4178   /**
4179    * Do AlterDisplay processing on Address Fields.
4180    *
4181    * @param array $row
4182    * @param array $rows
4183    * @param int $rowNum
4184    * @param string $baseUrl
4185    * @param string $linkText
4186    *
4187    * @return bool
4188    */
4189   public function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4190     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4191     $entryFound = FALSE;
4192     // handle country
4193     if (array_key_exists('civicrm_address_country_id', $row)) {
4194       if ($value = $row['civicrm_address_country_id']) {
4195         $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
4196         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4197           "reset=1&force=1&{$criteriaQueryParams}&" .
4198           "country_id_op=in&country_id_value={$value}",
4199           $this->_absoluteUrl, $this->_id
4200         );
4201         $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
4202         $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
4203           array(1 => $linkText)
4204         );
4205       }
4206
4207       $entryFound = TRUE;
4208     }
4209     if (array_key_exists('civicrm_address_county_id', $row)) {
4210       if ($value = $row['civicrm_address_county_id']) {
4211         $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
4212         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4213           "reset=1&force=1&{$criteriaQueryParams}&" .
4214           "county_id_op=in&county_id_value={$value}",
4215           $this->_absoluteUrl, $this->_id
4216         );
4217         $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
4218         $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
4219           array(1 => $linkText)
4220         );
4221       }
4222       $entryFound = TRUE;
4223     }
4224     // handle state province
4225     if (array_key_exists('civicrm_address_state_province_id', $row)) {
4226       if ($value = $row['civicrm_address_state_province_id']) {
4227         $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
4228
4229         $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4230           "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
4231           $this->_absoluteUrl, $this->_id
4232         );
4233         $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
4234         $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
4235           array(1 => $linkText)
4236         );
4237       }
4238       $entryFound = TRUE;
4239     }
4240
4241     return $entryFound;
4242   }
4243
4244   /**
4245    * Do AlterDisplay processing on Address Fields.
4246    *
4247    * @param array $row
4248    * @param array $rows
4249    * @param int $rowNum
4250    * @param string $baseUrl
4251    * @param string $linkText
4252    *
4253    * @return bool
4254    */
4255   public function alterDisplayContactFields(&$row, &$rows, &$rowNum, $baseUrl, $linkText) {
4256     $entryFound = FALSE;
4257     // There is no reason not to add links for all fields but it seems a bit odd to be able to click on
4258     // 'Mrs'. Also, we don't have metadata about the title. So, add selectively to addLinks.
4259     $addLinks = array('gender_id' => 'Gender');
4260     foreach (array('prefix_id', 'suffix_id', 'gender_id') as $fieldName) {
4261       if (array_key_exists('civicrm_contact_' . $fieldName, $row)) {
4262         if (($value = $row['civicrm_contact_' . $fieldName]) != FALSE) {
4263           $rows[$rowNum]['civicrm_contact_' . $fieldName] = CRM_Core_Pseudoconstant::getLabel('CRM_Contact_BAO_Contact', $fieldName, $value);
4264           if (($title = CRM_Utils_Array::value($fieldName, $addLinks)) != FALSE) {
4265             $this->addLinkToRow($rows[$rowNum], $baseUrl, $linkText, $value, $fieldName, 'civicrm_contact', $title);
4266           }
4267         }
4268         $entryFound = TRUE;
4269       }
4270     }
4271     return $entryFound;
4272   }
4273
4274   /**
4275    * Adjusts dates passed in to YEAR() for fiscal year.
4276    *
4277    * @param string $fieldName
4278    *
4279    * @return string
4280    */
4281   public function fiscalYearOffset($fieldName) {
4282     $config = CRM_Core_Config::singleton();
4283     $fy = $config->fiscalYearStart;
4284     if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' ||
4285       ($fy['d'] == 1 && $fy['M'] == 1)
4286     ) {
4287       return "YEAR( $fieldName )";
4288     }
4289     return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" .
4290     ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
4291   }
4292
4293   /**
4294    * Add Address into From Table if required.
4295    */
4296   public function addAddressFromClause() {
4297     // include address field if address column is to be included
4298     if ((isset($this->_addressField) &&
4299         $this->_addressField
4300       ) ||
4301       $this->isTableSelected('civicrm_address')
4302     ) {
4303       $this->_from .= "
4304                  LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
4305                            ON ({$this->_aliases['civicrm_contact']}.id =
4306                                {$this->_aliases['civicrm_address']}.contact_id) AND
4307                                {$this->_aliases['civicrm_address']}.is_primary = 1\n";
4308     }
4309   }
4310
4311   /**
4312    * Add Phone into From Table if required.
4313    */
4314   public function addPhoneFromClause() {
4315     // include address field if address column is to be included
4316     if ($this->isTableSelected('civicrm_phone')
4317     ) {
4318       $this->_from .= "
4319       LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
4320       ON ({$this->_aliases['civicrm_contact']}.id =
4321       {$this->_aliases['civicrm_phone']}.contact_id) AND
4322       {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
4323     }
4324   }
4325
4326   /**
4327    * Get phone columns to add to array.
4328    *
4329    * @param array $options
4330    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4331    *   - prefix_label Label to give columns from this phone table instance
4332    *
4333    * @return array
4334    *   phone columns definition
4335    */
4336   public function getPhoneColumns($options = array()) {
4337     $defaultOptions = array(
4338       'prefix' => '',
4339       'prefix_label' => '',
4340     );
4341
4342     $options = array_merge($defaultOptions, $options);
4343
4344     $fields = array(
4345       $options['prefix'] . 'civicrm_phone' => array(
4346         'dao' => 'CRM_Core_DAO_Phone',
4347         'fields' => array(
4348           $options['prefix'] . 'phone' => array(
4349             'title' => ts($options['prefix_label'] . 'Phone'),
4350             'name' => 'phone',
4351           ),
4352         ),
4353       ),
4354     );
4355     return $fields;
4356   }
4357
4358   /**
4359    * Get address columns to add to array.
4360    *
4361    * @param array $options
4362    *   - prefix Prefix to add to table (in case of more than one instance of the table)
4363    *   - prefix_label Label to give columns from this address table instance
4364    *
4365    * @return array
4366    *   address columns definition
4367    */
4368   public function getAddressColumns($options = array()) {
4369     $options += array(
4370       'prefix' => '',
4371       'prefix_label' => '',
4372       'group_by' => TRUE,
4373       'order_by' => TRUE,
4374       'filters' => TRUE,
4375       'defaults' => array(),
4376     );
4377     return $this->addAddressFields(
4378       $options['group_by'],
4379       $options['order_by'],
4380       $options['filters'],
4381       $options['defaults']
4382     );
4383   }
4384
4385   /**
4386    * Get a standard set of contact fields.
4387    *
4388    * @return array
4389    */
4390   public function getBasicContactFields() {
4391     return array(
4392       'sort_name' => array(
4393         'title' => ts('Contact Name'),
4394         'required' => TRUE,
4395         'default' => TRUE,
4396       ),
4397       'id' => array(
4398         'no_display' => TRUE,
4399         'required' => TRUE,
4400       ),
4401       'prefix_id' => array(
4402         'title' => ts('Contact Prefix'),
4403       ),
4404       'first_name' => array(
4405         'title' => ts('First Name'),
4406       ),
4407       'nick_name' => array(
4408         'title' => ts('Nick Name'),
4409       ),
4410       'middle_name' => array(
4411         'title' => ts('Middle Name'),
4412       ),
4413       'last_name' => array(
4414         'title' => ts('Last Name'),
4415       ),
4416       'suffix_id' => array(
4417         'title' => ts('Contact Suffix'),
4418       ),
4419       'postal_greeting_display' => array('title' => ts('Postal Greeting')),
4420       'email_greeting_display' => array('title' => ts('Email Greeting')),
4421       'addressee_display' => array('title' => ts('Address Greeting')),
4422       'contact_type' => array(
4423         'title' => ts('Contact Type'),
4424       ),
4425       'contact_sub_type' => array(
4426         'title' => ts('Contact Subtype'),
4427       ),
4428       'gender_id' => array(
4429         'title' => ts('Gender'),
4430       ),
4431       'birth_date' => array(
4432         'title' => ts('Birth Date'),
4433       ),
4434       'age' => array(
4435         'title' => ts('Age'),
4436         'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
4437       ),
4438       'job_title' => array(
4439         'title' => ts('Contact Job title'),
4440       ),
4441       'organization_name' => array(
4442         'title' => ts('Organization Name'),
4443       ),
4444       'external_identifier' => array(
4445         'title' => ts('Contact identifier from external system'),
4446       ),
4447     );
4448   }
4449
4450   /**
4451    * Add contact to group.
4452    *
4453    * @param int $groupID
4454    */
4455   public function add2group($groupID) {
4456     if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
4457       $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
4458
4459       // here are we are prepending / adding  contact id field that could be used for adding group
4460       // so first check for "SELECT SQL_CALC_FOUND_ROWS" and if does not exist replace "SELECT"
4461       if (preg_match('/^SELECT SQL_CALC_FOUND_ROWS/', $this->_select)) {
4462         $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
4463       }
4464       else {
4465         $select = str_ireplace('SELECT ', $select, $this->_select);
4466       }
4467
4468       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
4469       $sql = str_replace('WITH ROLLUP', '', $sql);
4470       $dao = CRM_Core_DAO::executeQuery($sql);
4471
4472       $contact_ids = array();
4473       // Add resulting contacts to group
4474       while ($dao->fetch()) {
4475         if ($dao->addtogroup_contact_id) {
4476           $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
4477         }
4478       }
4479
4480       if (!empty($contact_ids)) {
4481         CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
4482         CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
4483       }
4484       else {
4485         CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
4486       }
4487     }
4488   }
4489
4490   /**
4491    * Show charts on print screen.
4492    */
4493   public static function uploadChartImage() {
4494     // upload strictly for '.png' images
4495     $name = trim(basename(CRM_Utils_Request::retrieve('name', 'String', CRM_Core_DAO::$_nullObject, FALSE, NULL, 'GET')));
4496     if (preg_match('/\.png$/', $name)) {
4497       //
4498       // POST data is usually string data, but we are passing a RAW .png
4499       // so PHP is a bit confused and $_POST is empty. But it has saved
4500       // the raw bits into $HTTP_RAW_POST_DATA
4501       //
4502       $httpRawPostData = $GLOBALS['HTTP_RAW_POST_DATA'];
4503
4504       // prepare the directory
4505       $config = CRM_Core_Config::singleton();
4506       $defaultPath
4507         = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) .
4508         '/openFlashChart/';
4509       if (!file_exists($defaultPath)) {
4510         mkdir($defaultPath, 0777, TRUE);
4511       }
4512
4513       // full path to the saved image including filename
4514       $destination = $defaultPath . $name;
4515
4516       //write and save
4517       $jfh = fopen($destination, 'w') or die("can't open file");
4518       fwrite($jfh, $httpRawPostData);
4519       fclose($jfh);
4520       CRM_Utils_System::civiExit();
4521     }
4522   }
4523
4524   /**
4525    * Apply common settings to entityRef fields.
4526    *
4527    * @param array $field
4528    * @param string $table
4529    */
4530   private function setEntityRefDefaults(&$field, $table) {
4531     $field['attributes'] = $field['attributes'] ? $field['attributes'] : array();
4532     $field['attributes'] += array(
4533       'entity' => CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table)),
4534       'multiple' => TRUE,
4535       'placeholder' => ts('- select -'),
4536     );
4537   }
4538
4539   /**
4540    * Add link fields to the row.
4541    *
4542    * Function adds the _link & _hover fields to the row.
4543    *
4544    * @param array $row
4545    * @param string $baseUrl
4546    * @param string $linkText
4547    * @param string $value
4548    * @param string $fieldName
4549    * @param string $tablePrefix
4550    * @param string $fieldLabel
4551    *
4552    * @return mixed
4553    */
4554   protected function addLinkToRow(&$row, $baseUrl, $linkText, $value, $fieldName, $tablePrefix, $fieldLabel) {
4555     $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
4556     $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
4557       "reset=1&force=1&{$criteriaQueryParams}&" .
4558       $fieldName . "_op=in&{$fieldName}_value={$value}",
4559       $this->_absoluteUrl, $this->_id
4560     );
4561     $row["{$tablePrefix}_{$fieldName}_link"] = $url;
4562     $row["{$tablePrefix}_{$fieldName}_hover"] = ts("%1 for this %2.",
4563       array(1 => $linkText, 2 => $fieldLabel)
4564     );
4565   }
4566
4567   /**
4568    * Generate temporary table to hold all contributions with permissioned FTs.
4569    *
4570    * @param object $query
4571    * @param string $alias
4572    * @param bool $return
4573    */
4574   public function getPermissionedFTQuery(&$query, $alias = NULL, $return = FALSE) {
4575     if (!CRM_Financial_BAO_FinancialType::isACLFinancialTypeStatus()) {
4576       return FALSE;
4577     }
4578     $financialTypes = NULL;
4579     CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes);
4580     if (empty($financialTypes)) {
4581       $contFTs = "0";
4582       $liFTs = implode(',', array_keys(CRM_Contribute_Pseudoconstant::financialType()));
4583     }
4584     else {
4585       $contFTs = $liFTs = implode(',', array_keys($financialTypes));
4586     }
4587     if ($alias) {
4588       $temp = CRM_Utils_Array::value('civicrm_line_item', $query->_aliases);
4589       $query->_aliases['civicrm_line_item'] = $alias;
4590     }
4591     if (empty($query->_where)) {
4592       $query->_where = "WHERE {$query->_aliases['civicrm_contribution']}.id IS NOT NULL ";
4593     }
4594     CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS civicrm_contribution_temp");
4595     $sql = "CREATE TEMPORARY TABLE civicrm_contribution_temp AS SELECT {$query->_aliases['civicrm_contribution']}.id {$query->_from}
4596               LEFT JOIN civicrm_line_item   {$query->_aliases['civicrm_line_item']}
4597                       ON {$query->_aliases['civicrm_contribution']}.id = {$query->_aliases['civicrm_line_item']}.contribution_id AND
4598                          {$query->_aliases['civicrm_line_item']}.entity_table = 'civicrm_contribution'
4599                       AND {$query->_aliases['civicrm_line_item']}.financial_type_id NOT IN (" . $liFTs . ")
4600               {$query->_where}
4601                       AND {$query->_aliases['civicrm_contribution']}.financial_type_id IN (" . $contFTs . ")
4602                       AND {$query->_aliases['civicrm_line_item']}.id IS NULL
4603               GROUP BY {$query->_aliases['civicrm_contribution']}.id";
4604     CRM_Core_DAO::executeQuery($sql);
4605     if (isset($temp)) {
4606       $query->_aliases['civicrm_line_item'] = $temp;
4607     }
4608     $from = " INNER JOIN civicrm_contribution_temp temp ON {$query->_aliases['civicrm_contribution']}.id = temp.id ";
4609     if ($return) {
4610       return $from;
4611     }
4612     $query->_from .= $from;
4613   }
4614
4615   /**
4616    * Get label for show results buttons.
4617    *
4618    * @return string
4619    */
4620   public function getResultsLabel() {
4621     $showResultsLabel = $this->resultsDisplayed() ? ts('Refresh results') : ts('View results');
4622     return $showResultsLabel;
4623   }
4624
4625   /**
4626    * Determine the output mode from the url or input.
4627    *
4628    * Output could be
4629    *   - pdf : Render as pdf
4630    *   - csv : Render as csv
4631    *   - print : Render in print format
4632    *   - save : save the report and display the new report
4633    *   - copy : save the report as a new instance and display that.
4634    *   - group : go to the add to group screen.
4635    *
4636    *  Potentially chart variations could also be included but the complexity
4637    *   is that we might print a bar chart as a pdf.
4638    */
4639   protected function setOutputMode() {
4640     $buttonName = $this->controller->getButtonName();
4641     $this->_outputMode = CRM_Utils_Request::retrieve(
4642       'output',
4643       'String',
4644       CRM_Core_DAO::$_nullObject,
4645       FALSE,
4646       CRM_Utils_Array::value('task', $this->_params)
4647     );
4648
4649     if ($buttonName) {
4650       if ($buttonName == $this->_instanceButtonName) {
4651         $this->_outputMode = 'save';
4652       }
4653       if ($buttonName == $this->_printButtonName) {
4654         $this->_outputMode = 'print';
4655       }
4656       if ($buttonName == $this->_pdfButtonName) {
4657         $this->_outputMode = 'pdf';
4658       }
4659       if ($this->_csvButtonName == $buttonName) {
4660         $this->_outputMode = 'csv';
4661       }
4662       if ($this->_groupButtonName == $buttonName) {
4663         $this->_outputMode = 'group';
4664       }
4665       if ($buttonName == $this->_createNewButtonName) {
4666         $this->_outputMode = 'copy';
4667       }
4668     }
4669   }
4670
4671 }