CRM/Report/Form.php

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