projects / civicrm-core.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Standardize on name DataSource instead of UploadFile CRM-11254
[civicrm-core.git] / CRM / Report / Form.php
CommitLineData
6a488035 1<?php
6a488035
TO		 2/*
3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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 *
30 * @package CRM
31 * @copyright CiviCRM LLC (c) 2004-2013
32 * $Id$
33 *
34 */
35class CRM_Report_Form extends CRM_Core_Form {
36 CONST ROW_COUNT_LIMIT = 50;
37
38 /**
39 * Operator types - used for displaying filter elements
40 */
41 CONST
42 OP_INT = 1,
43 OP_STRING = 2,
44 OP_DATE = 4,
45 OP_DATETIME = 5,
46 OP_FLOAT = 8,
47 OP_SELECT = 64,
48 OP_MULTISELECT = 65,
49 OP_MULTISELECT_SEPARATOR = 66,
50 OP_MONTH = 128;
51
52 /**
53 * The id of the report instance
54 *
55 * @var integer
56 */
57 protected $_id;
58
59 /**
60 * The id of the report template
61 *
62 * @var integer;
63 */
64 protected $_templateID;
65
66 /**
67 * The report title
68 *
69 * @var string
70 */
71 protected $_title;
72 protected $_noFields = FALSE;
73
74 /**
75 * The set of all columns in the report. An associative array
76 * with column name as the key and attribues as the value
77 *
78 * @var array
79 */
80 protected $_columns = array();
81
82 /**
83 * The set of filters in the report
84 *
85 * @var array
86 */
87 protected $_filters = array();
88
89 /**
90 * The set of optional columns in the report
91 *
92 * @var array
93 */
94 protected $_options = array();
95
96 protected $_defaults = array();
97
98 /*
99 * By default most reports hide contact id.
100 * Setting this to true makes it available
101 */
102 protected $_exposeContactID = TRUE;
103
104 /**
105 * Set of statistic fields
106 *
107 * @var array
108 */
109 protected $_statFields = array();
110
111 /**
112 * Set of statistics data
113 *
114 * @var array
115 */
116 protected $_statistics = array();
117
118 /**
119 * List of fields not to be repeated during display
120 *
121 * @var array
122 */
123 protected $_noRepeats = array();
124
125 /**
126 * List of fields not to be displayed
127 *
128 * @var array
129 */
130 protected $_noDisplay = array();
131
132 /**
133 * Object type that a custom group extends
134 *
135 * @var null
136 */
137 protected $_customGroupExtends = NULL;
138 protected $_customGroupFilters = TRUE;
139 protected $_customGroupGroupBy = FALSE;
7a8b61ab 140 protected $_customGroupJoin = 'LEFT JOIN';
6a488035
TO		 141
142 /**
143 * build tags filter
144 *
145 */
146 protected $_tagFilter = FALSE;
147
148 /**
149 * build groups filter
150 *
151 */
152 protected $_groupFilter = FALSE;
153
154 /**
155 * Navigation fields
156 *
157 * @var array
158 */
159 public $_navigation = array();
160
161 public $_drilldownReport = array();
162
163 /**
164 * An attribute for checkbox/radio form field layout
165 *
166 * @var array
167 */
168 protected $_fourColumnAttribute = array(
169 '</td><td width="25%">', '</td><td width="25%">',
170 '</td><td width="25%">', '</tr><tr><td>',
171 );
172
173 protected $_force = 1;
174
175 protected $_params = NULL;
176 protected $_formValues = NULL;
177 protected $_instanceValues = NULL;
178
179 protected $_instanceForm = FALSE;
180 protected $_criteriaForm = FALSE;
181
182 protected $_instanceButtonName = NULL;
183 protected $_createNewButtonName = NULL;
184 protected $_printButtonName = NULL;
185 protected $_pdfButtonName = NULL;
186 protected $_csvButtonName = NULL;
187 protected $_groupButtonName = NULL;
188 protected $_chartButtonName = NULL;
189 protected $_csvSupported = TRUE;
190 protected $_add2groupSupported = TRUE;
191 protected $_groups = NULL;
192 protected $_rowsFound = NULL;
193 protected $_selectAliases = array();
194 protected $_rollup = NULL;
195 protected $_limit = NULL;
196 protected $_sections = NULL;
197 protected $_autoIncludeIndexedFieldsAsOrderBys = 0;
198 protected $_absoluteUrl = FALSE;
7f1ec901 199 protected $_grandFlag = FALSE;
6a488035 200
ae555e90
DS		 201 /**
202 * Flag to indicate if result-set is to be stored in a class variable which could be retrieved using getResultSet() method.
203 *
204 * @var boolean
205 */
206 protected $_storeResultSet = FALSE;
207
208 /**
209 * When _storeResultSet Flag is set use this var to store result set in form of array
210 *
211 * @var boolean
212 */
213 protected $_resultSet = array();
214
6a488035
TO		 215 /**
216 * To what frequency group-by a date column
217 *
218 * @var array
219 */
220 protected $_groupByDateFreq = array(
221 'MONTH' => 'Month',
222 'YEARWEEK' => 'Week',
223 'QUARTER' => 'Quarter',
224 'YEAR' => 'Year',
225 );
226
227 /**
228 * Variables to hold the acl inner join and where clause
229 */
230 protected $_aclFrom = NULL;
231 protected $_aclWhere = NULL;
232
233 /**
234 * Array of DAO tables having columns included in SELECT or ORDER BY clause
235 *
236 * @var array
237 */
238 protected $_selectedTables;
239
240 public $_having = NULL;
241 public $_select = NULL;
242 public $_columnHeaders = array();
243 public $_orderBy = NULL;
244 public $_groupBy = NULL;
adfe2750 245 public $_whereClauses = array();
246 public $_havingClauses = array();
1c4d8c3e 247
7a961f19 248 /**
249 * Variable to hold the currency alias
250 */
251 protected $_currencyColumn = NULL;
6a488035
TO		 252
253 /**
254 *
255 */
256 function __construct() {
257 parent::__construct();
258
259 // build tag filter
260 if ($this->_tagFilter) {
261 $this->buildTagFilter();
262 }
263 if ($this->_exposeContactID) {
264 if (array_key_exists('civicrm_contact', $this->_columns)) {
265 $this->_columns['civicrm_contact']['fields']['exposed_id'] = array(
266 'name' => 'id',
267 'title' => 'Contact ID',
268 'no_repeat' => TRUE,
269 );
270 }
271 }
272
273 if ($this->_groupFilter) {
274 $this->buildGroupFilter();
275 }
276
277 // Get all custom groups
cd43c5e3 278 $allGroups = CRM_Core_PseudoConstant::get('CRM_Core_DAO_CustomField', 'custom_group_id');
6a488035
TO		 279
280 // Get the custom groupIds for which the user have VIEW permission
281 require_once 'CRM/ACL/API.php';
282 $permCustomGroupIds = CRM_ACL_API::group(CRM_Core_Permission::VIEW, NULL, 'civicrm_custom_group', $allGroups, NULL);
283
284 // do not allow custom data for reports if user don't have
285 // permission to access custom data.
286 if (!empty($this->_customGroupExtends) && !CRM_Core_Permission::check('access all custom data') && empty($permCustomGroupIds)) {
287 $this->_customGroupExtends = array();
288 }
289
290 // merge custom data columns to _columns list, if any
291 $this->addCustomDataToColumns(TRUE, $permCustomGroupIds);
292
293 // add / modify display columns, filters ..etc
294 CRM_Utils_Hook::alterReportVar('columns', $this->_columns, $this);
7a961f19 295
296 //assign currencyColumn variable to tpl
297 $this->assign('currencyColumn', $this->_currencyColumn);
6a488035
TO		 298 }
299
300 function preProcessCommon() {
8f1445ea
DL		 301 $this->_force =
302 CRM_Utils_Request::retrieve(
303 'force',
304 'Boolean',
305 CRM_Core_DAO::$_nullObject
306 );
6a488035
TO		 307
308 $this->_section = CRM_Utils_Request::retrieve('section', 'Integer', CRM_Core_DAO::$_nullObject);
309
310 $this->assign('section', $this->_section);
311 CRM_Core_Region::instance('page-header')->add(array(
312 'markup' => sprintf('<!-- Report class: [%s] -->', htmlentities(get_class($this))),
313 ));
314
315 $this->_id = $this->get('instanceId');
316 if (!$this->_id) {
317 $this->_id = CRM_Report_Utils_Report::getInstanceID();
318 if (!$this->_id) {
319 $this->_id = CRM_Report_Utils_Report::getInstanceIDForPath();
320 }
321 }
322
323 // set qfkey so that pager picks it up and use it in the "Next > Last >>" links.
324 // FIXME: Note setting it in $_GET doesn't work, since pager generates link based on QUERY_STRING
325 $_SERVER['QUERY_STRING'] .= "&qfKey={$this->controller->_key}";
326
327 if ($this->_id) {
328 $this->assign('instanceId', $this->_id);
329 $params = array('id' => $this->_id);
330 $this->_instanceValues = array();
331 CRM_Core_DAO::commonRetrieve('CRM_Report_DAO_Instance',
332 $params,
333 $this->_instanceValues
334 );
335 if (empty($this->_instanceValues)) {
336 CRM_Core_Error::fatal("Report could not be loaded.");
337 }
338
339 if (!empty($this->_instanceValues['permission']) &&
340 (!(CRM_Core_Permission::check($this->_instanceValues['permission']) ||
341 CRM_Core_Permission::check('administer Reports')
342 ))
343 ) {
344 CRM_Utils_System::permissionDenied();
345 CRM_Utils_System::civiExit();
346 }
347
348 $formValues = CRM_Utils_Array::value('form_values', $this->_instanceValues);
349 if ($formValues) {
350 $this->_formValues = unserialize($formValues);
351 }
352 else {
353 $this->_formValues = NULL;
354 }
355
356 // lets always do a force if reset is found in the url.
1c4d8c3e 357 if (CRM_Utils_Array::value('reset', $_REQUEST)) {
6a488035
TO		 358 $this->_force = 1;
359 }
360
361 // set the mode
362 $this->assign('mode', 'instance');
363 }
364 else {
365 list($optionValueID, $optionValue) = CRM_Report_Utils_Report::getValueIDFromUrl();
366 $instanceCount = CRM_Report_Utils_Report::getInstanceCount($optionValue);
367 if (($instanceCount > 0) && $optionValueID) {
368 $this->assign('instanceUrl',
369 CRM_Utils_System::url('civicrm/report/list',
370 "reset=1&ovid=$optionValueID"
371 )
372 );
373 }
374 if ($optionValueID) {
375 $this->_description = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', $optionValueID, 'description');
376 }
377
378 // set the mode
379 $this->assign('mode', 'template');
380 }
381
382 // lets display the Report Settings section
383 $this->_instanceForm = $this->_force || $this->_id || (!empty($_POST));
384
385 // Do not display Report Settings section if administer Reports permission is absent OR
386 // if report instance is reserved and administer reserved reports absent
387 if (!CRM_Core_Permission::check('administer Reports') ||
388 ($this->_instanceValues['is_reserved'] && !CRM_Core_Permission::check('administer reserved reports'))) {
389 $this->_instanceForm = FALSE;
390 }
391
392 $this->assign('criteriaForm', FALSE);
393 // Display Report Criteria section if user has access Report Criteria OR administer Reports AND report instance is not reserved
394 if (CRM_Core_Permission::check('administer Reports') || CRM_Core_Permission::check('access Report Criteria')) {
395 if (!$this->_instanceValues['is_reserved'] || CRM_Core_Permission::check('administer reserved reports')) {
396 $this->assign('criteriaForm', TRUE);
397 $this->_criteriaForm = TRUE;
398 }
399 }
400
401 $this->_instanceButtonName = $this->getButtonName('submit', 'save');
402 $this->_createNewButtonName = $this->getButtonName('submit', 'next');
403 $this->_printButtonName = $this->getButtonName('submit', 'print');
404 $this->_pdfButtonName = $this->getButtonName('submit', 'pdf');
405 $this->_csvButtonName = $this->getButtonName('submit', 'csv');
406 $this->_groupButtonName = $this->getButtonName('submit', 'group');
407 $this->_chartButtonName = $this->getButtonName('submit', 'chart');
408 }
409
29fc2b79 410 function addBreadCrumb() {
8f1445ea
DL		 411 $breadCrumbs =
412 array(
413 array(
414 'title' => ts('Report Templates'),
415 'url' => CRM_Utils_System::url('civicrm/admin/report/template/list', 'reset=1'),
416 )
417 );
6a488035
TO		 418
419 CRM_Utils_System::appendBreadCrumb($breadCrumbs);
420 }
421
422 function preProcess() {
8f1445ea 423 $this->preProcessCommon();
6a488035 424
6a488035 425 if (!$this->_id) {
29fc2b79 426 $this->addBreadCrumb();
6a488035
TO		 427 }
428
429 foreach ($this->_columns as $tableName => $table) {
430 // set alias
431 if (!isset($table['alias'])) {
432 $this->_columns[$tableName]['alias'] = substr($tableName, 8) . '_civireport';
433 }
434 else {
435 $this->_columns[$tableName]['alias'] = $table['alias'] . '_civireport';
436 }
437
438 $this->_aliases[$tableName] = $this->_columns[$tableName]['alias'];
439
440 // higher preference to bao object
441 if (array_key_exists('bao', $table)) {
0e6e8724
DL		 442 $baoName = $table['bao'];
443 $expFields = $baoName::exportableFields( );
6a488035 444 }
8edb9849 445 elseif (array_key_exists('dao', $table)){
0e6e8724
DL		 446 $daoName = $table['dao'];
447 $expFields = $daoName::export( );
6a488035 448 }
8edb9849 449 else{
450 $expFields = array();
451 }
6a488035
TO		 452
453 $doNotCopy = array('required');
454
455 $fieldGroups = array('fields', 'filters', 'group_bys', 'order_bys');
456 foreach ($fieldGroups as $fieldGrp) {
457 if (CRM_Utils_Array::value($fieldGrp, $table) && is_array($table[$fieldGrp])) {
458 foreach ($table[$fieldGrp] as $fieldName => $field) {
459 // $name is the field name used to reference the BAO/DAO export fields array
460 $name = isset($field['name']) ? $field['name'] : $fieldName;
461
462 // Sometimes the field name key in the BAO/DAO export fields array is
463 // different from the actual database field name.
464 // Unset $field['name'] so that actual database field name can be obtained
465 // from the BAO/DAO export fields array.
466 unset($field['name']);
467
468 if (array_key_exists($name, $expFields)) {
469 foreach ($doNotCopy as $dnc) {
470 // unset the values we don't want to be copied.
471 unset($expFields[$name][$dnc]);
472 }
473 if (empty($field)) {
474 $this->_columns[$tableName][$fieldGrp][$fieldName] = $expFields[$name];
475 }
476 else {
477 foreach ($expFields[$name] as $property => $val) {
478 if (!array_key_exists($property, $field)) {
479 $this->_columns[$tableName][$fieldGrp][$fieldName][$property] = $val;
480 }
481 }
482 }
483 }
484
485 // fill other vars
486 if (CRM_Utils_Array::value('no_repeat', $field)) {
487 $this->_noRepeats[] = "{$tableName}_{$fieldName}";
488 }
489 if (CRM_Utils_Array::value('no_display', $field)) {
490 $this->_noDisplay[] = "{$tableName}_{$fieldName}";
491 }
492
493 // set alias = table-name, unless already set
494 $alias = isset($field['alias']) ? $field['alias'] : (isset($this->_columns[$tableName]['alias']) ?
495 $this->_columns[$tableName]['alias'] : $tableName
496 );
497 $this->_columns[$tableName][$fieldGrp][$fieldName]['alias'] = $alias;
498
499 // set name = fieldName, unless already set
500 if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['name'])) {
501 $this->_columns[$tableName][$fieldGrp][$fieldName]['name'] = $name;
502 }
503
504 // set dbAlias = alias.name, unless already set
505 if (!isset($this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'])) {
506 $this->_columns[$tableName][$fieldGrp][$fieldName]['dbAlias'] = $alias . '.' . $this->_columns[$tableName][$fieldGrp][$fieldName]['name'];
507 }
508
509 if (CRM_Utils_Array::value('type', $this->_columns[$tableName][$fieldGrp][$fieldName]) &&
510 !isset($this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'])
511 ) {
512 if (in_array($this->_columns[$tableName][$fieldGrp][$fieldName]['type'],
513 array(CRM_Utils_Type::T_MONEY, CRM_Utils_Type::T_FLOAT)
514 )) {
515 $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
516 }
517 elseif (in_array($this->_columns[$tableName][$fieldGrp][$fieldName]['type'],
518 array(CRM_Utils_Type::T_INT)
519 )) {
520 $this->_columns[$tableName][$fieldGrp][$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
521 }
522 }
523 }
524 }
525 }
526
527 // copy filters to a separate handy variable
528 if (array_key_exists('filters', $table)) {
529 $this->_filters[$tableName] = $this->_columns[$tableName]['filters'];
530 }
531
532 if (array_key_exists('group_bys', $table)) {
533 $groupBys[$tableName] = $this->_columns[$tableName]['group_bys'];
534 }
535
536 if (array_key_exists('fields', $table)) {
537 $reportFields[$tableName] = $this->_columns[$tableName]['fields'];
538 }
539 }
540
541 if ($this->_force) {
542 $this->setDefaultValues(FALSE);
543 }
544
8f1445ea
DL		 545 CRM_Report_Utils_Get::processFilter($this->_filters, $this->_defaults);
546 CRM_Report_Utils_Get::processGroupBy($groupBys, $this->_defaults);
547 CRM_Report_Utils_Get::processFields($reportFields, $this->_defaults);
6a488035
TO		 548 CRM_Report_Utils_Get::processChart($this->_defaults);
549
550 if ($this->_force) {
551 $this->_formValues = $this->_defaults;
552 $this->postProcess();
553 }
554 }
555
556 function setDefaultValues($freeze = TRUE) {
557 $freezeGroup = array();
558
559 // FIXME: generalizing form field naming conventions would reduce
560 // lots of lines below.
561 foreach ($this->_columns as $tableName => $table) {
562 if (array_key_exists('fields', $table)) {
563 foreach ($table['fields'] as $fieldName => $field) {
564 if (!array_key_exists('no_display', $field)) {
565 if (isset($field['required'])) {
566 // set default
567 $this->_defaults['fields'][$fieldName] = 1;
568
569 if ($freeze) {
570 // find element object, so that we could use quickform's freeze method
571 // for required elements
8f1445ea 572 $obj = $this->getElementFromGroup("fields", $fieldName);
6a488035
TO		 573 if ($obj) {
574 $freezeGroup[] = $obj;
575 }
576 }
577 }
578 elseif (isset($field['default'])) {
579 $this->_defaults['fields'][$fieldName] = $field['default'];
580 }
581 }
582 }
583 }
584
585 if (array_key_exists('group_bys', $table)) {
586 foreach ($table['group_bys'] as $fieldName => $field) {
587 if (isset($field['default'])) {
588 if (CRM_Utils_Array::value('frequency', $field)) {
589 $this->_defaults['group_bys_freq'][$fieldName] = 'MONTH';
590 }
591 $this->_defaults['group_bys'][$fieldName] = $field['default'];
592 }
593 }
594 }
595 if (array_key_exists('filters', $table)) {
596 foreach ($table['filters'] as $fieldName => $field) {
597 if (isset($field['default'])) {
598 if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
b0e34e7c 599 if(is_array($field['default'])){
600 $this->_defaults["{$fieldName}_from"] = CRM_Utils_Array::value('from', $field['default']);
601 $this->_defaults["{$fieldName}_to"] = CRM_Utils_Array::value('to', $field['default']);
602 $this->_defaults["{$fieldName}_relative"] = 0;
603 }
604 else{
605 $this->_defaults["{$fieldName}_relative"] = $field['default'];
606 }
6a488035
TO		 607 }
608 else {
609 $this->_defaults["{$fieldName}_value"] = $field['default'];
610 }
611 }
612 //assign default value as "in" for multiselect
613 //operator, To freeze the select element
614 if (CRM_Utils_Array::value('operatorType', $field) == CRM_Report_FORM::OP_MULTISELECT) {
615 $this->_defaults["{$fieldName}_op"] = 'in';
616 }
617 elseif (CRM_Utils_Array::value('operatorType', $field) == CRM_Report_FORM::OP_MULTISELECT_SEPARATOR) {
618 $this->_defaults["{$fieldName}_op"] = 'mhas';
619 }
620 elseif ($op = CRM_Utils_Array::value('default_op', $field)) {
621 $this->_defaults["{$fieldName}_op"] = $op;
622 }
623 }
624 }
625
8f1445ea
DL		 626 if (
627 array_key_exists('order_bys', $table) &&
6a488035
TO		 628 is_array($table['order_bys'])
629 ) {
6a488035 630 if (!array_key_exists('order_bys', $this->_defaults)) {
6a488035
TO		 631 $this->_defaults['order_bys'] = array();
632 }
633 foreach ($table['order_bys'] as $fieldName => $field) {
8f1445ea
DL		 634 if (
635 CRM_Utils_Array::value('default', $field) ||
6a488035
TO		 636 CRM_Utils_Array::value('default_order', $field) ||
637 CRM_Utils_Array::value('default_is_section', $field) ||
638 CRM_Utils_Array::value('default_weight', $field)
639 ) {
640 $order_by = array(
641 'column' => $fieldName,
642 'order' => CRM_Utils_Array::value('default_order', $field, 'ASC'),
643 'section' => CRM_Utils_Array::value('default_is_section', $field, 0),
644 );
645
646 if (CRM_Utils_Array::value('default_weight', $field)) {
647 $this->_defaults['order_bys'][(int) $field['default_weight']] = $order_by;
648 }
649 else {
650 array_unshift($this->_defaults['order_bys'], $order_by);
651 }
652 }
653 }
654 }
655
656 foreach ($this->_options as $fieldName => $field) {
657 if (isset($field['default'])) {
658 $this->_defaults['options'][$fieldName] = $field['default'];
659 }
660 }
661 }
662
663 if (!empty($this->_submitValues)) {
664 $this->preProcessOrderBy($this->_submitValues);
665 }
666 else {
667 $this->preProcessOrderBy($this->_defaults);
668 }
669
670 // lets finish freezing task here itself
671 if (!empty($freezeGroup)) {
672 foreach ($freezeGroup as $elem) {
673 $elem->freeze();
674 }
675 }
676
677 if ($this->_formValues) {
678 $this->_defaults = array_merge($this->_defaults, $this->_formValues);
679 }
680
681 if ($this->_instanceValues) {
682 $this->_defaults = array_merge($this->_defaults, $this->_instanceValues);
683 }
684
685 CRM_Report_Form_Instance::setDefaultValues($this, $this->_defaults);
686
687 return $this->_defaults;
688 }
689
690 function getElementFromGroup($group, $grpFieldName) {
691 $eleObj = $this->getElement($group);
692 foreach ($eleObj->_elements as $index => $obj) {
693 if ($grpFieldName == $obj->_attributes['name']) {
694 return $obj;
695 }
696 }
697 return FALSE;
698 }
699
700 function addColumns() {
701 $options = array();
702 $colGroups = NULL;
703 foreach ($this->_columns as $tableName => $table) {
704 if (array_key_exists('fields', $table)) {
705 foreach ($table['fields'] as $fieldName => $field) {
706 if (!array_key_exists('no_display', $field)) {
707 if (isset($field['grouping'])) {
708 $tableName = $field['grouping'];
709 }
710 elseif (isset($table['grouping'])) {
711 $tableName = $table['grouping'];
712 }
713 $colGroups[$tableName]['fields'][$fieldName] = CRM_Utils_Array::value('title', $field);
714
715 if (isset($table['group_title'])) {
716 $colGroups[$tableName]['group_title'] = $table['group_title'];
717 }
718
719 $options[$fieldName] = CRM_Utils_Array::value('title', $field);
720 }
721 }
722 }
723 }
724
725 $this->addCheckBox("fields", ts('Select Columns'), $options, NULL,
726 NULL, NULL, NULL, $this->_fourColumnAttribute, TRUE
727 );
728 $this->assign('colGroups', $colGroups);
729 }
730
731 function addFilters() {
732 $options = $filters = array();
733 $count = 1;
734 foreach ($this->_filters as $table => $attributes) {
735 foreach ($attributes as $fieldName => $field) {
736 // get ready with option value pair
1b36206c 737 // @ 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
738 // would be useful
739 $operations = $this->getOperationPair(
8f1445ea 740 CRM_Utils_Array::value('operatorType', $field),
1b36206c 741 $fieldName);
6a488035
TO		 742
743 $filters[$table][$fieldName] = $field;
744
745 switch (CRM_Utils_Array::value('operatorType', $field)) {
746 case CRM_Report_Form::OP_MONTH:
747 if (!array_key_exists('options', $field) || !is_array($field['options']) || empty($field['options'])) {
748 // If there's no option list for this filter, define one.
749 $field['options'] = array(
750 1 => ts('January'),
751 2 => ts('February'),
752 3 => ts('March'),
753 4 => ts('April'),
754 5 => ts('May'),
755 6 => ts('June'),
756 7 => ts('July'),
757 8 => ts('August'),
758 9 => ts('September'),
759 10 => ts('October'),
760 11 => ts('November'),
761 12 => ts('December'),
762 );
763 // Add this option list to this column _columns. This is
764 // required so that filter statistics show properly.
765 $this->_columns[$table]['filters'][$fieldName]['options'] = $field['options'];
766 }
767 case CRM_Report_FORM::OP_MULTISELECT:
768 case CRM_Report_FORM::OP_MULTISELECT_SEPARATOR:
769 // assume a multi-select field
770 if (!empty($field['options'])) {
771 $element = $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
772 if (count($operations) <= 1) {
773 $element->freeze();
774 }
775 $select = $this->addElement('select', "{$fieldName}_value", NULL,
776 $field['options'], array(
777 'size' => 4,
778 'style' => 'min-width:250px',
779 )
780 );
781 $select->setMultiple(TRUE);
782 }
783 break;
784
785 case CRM_Report_FORM::OP_SELECT:
786 // assume a select field
787 $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations);
788 $this->addElement('select', "{$fieldName}_value", NULL, $field['options']);
789 break;
790
791 case CRM_Report_FORM::OP_DATE:
792 // build datetime fields
bc3f7f04 793 CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from','_to', 'From:', FALSE, $operations);
6a488035
TO		 794 $count++;
795 break;
796
797 case CRM_Report_FORM::OP_DATETIME:
798 // build datetime fields
bc3f7f04 799 CRM_Core_Form_Date::buildDateRange($this, $fieldName, $count, '_from', '_to', 'From:', FALSE, $operations, 'searchDate', true);
6a488035
TO		 800 $count++;
801 break;
802
803 case CRM_Report_FORM::OP_INT:
804 case CRM_Report_FORM::OP_FLOAT:
805 // and a min value input box
806 $this->add('text', "{$fieldName}_min", ts('Min'));
807 // and a max value input box
808 $this->add('text', "{$fieldName}_max", ts('Max'));
809 default:
810 // default type is string
811 $this->addElement('select', "{$fieldName}_op", ts('Operator:'), $operations,
812 array('onchange' => "return showHideMaxMinVal( '$fieldName', this.value );")
813 );
814 // we need text box for value input
815 $this->add('text', "{$fieldName}_value", NULL);
816 break;
817 }
818 }
819 }
820 $this->assign('filters', $filters);
821 }
822
823 function addOptions() {
824 if (!empty($this->_options)) {
825 // FIXME: For now lets build all elements as checkboxes.
826 // Once we clear with the format we can build elements based on type
827
828 $options = array();
829 foreach ($this->_options as $fieldName => $field) {
830 if ($field['type'] == 'select') {
831 $this->addElement('select', "{$fieldName}", $field['title'], $field['options']);
832 }
52634dad 833 else if ($field['type'] == 'checkbox') {
6a488035 834 $options[$field['title']] = $fieldName;
52634dad
DS		 835 $this->addCheckBox($fieldName, NULL,
836 $options, NULL,
837 NULL, NULL, NULL, $this->_fourColumnAttribute
838 );
6a488035
TO		 839 }
840 }
6a488035 841 }
52634dad 842 $this->assign('otherOptions', $this->_options);
6a488035
TO		 843 }
844
845 function addChartOptions() {
846 if (!empty($this->_charts)) {
847 $this->addElement('select', "charts", ts('Chart'), $this->_charts, array('onchange' => 'disablePrintPDFButtons(this.value);'));
848 $this->assign('charts', $this->_charts);
849 $this->addElement('submit', $this->_chartButtonName, ts('View'));
850 }
851 }
852
853 function addGroupBys() {
854 $options = $freqElements = array();
855
856 foreach ($this->_columns as $tableName => $table) {
857 if (array_key_exists('group_bys', $table)) {
858 foreach ($table['group_bys'] as $fieldName => $field) {
859 if (!empty($field)) {
860 $options[$field['title']] = $fieldName;
861 if (CRM_Utils_Array::value('frequency', $field)) {
862 $freqElements[$field['title']] = $fieldName;
863 }
864 }
865 }
866 }
867 }
868 $this->addCheckBox("group_bys", ts('Group by columns'), $options, NULL,
869 NULL, NULL, NULL, $this->_fourColumnAttribute
870 );
871 $this->assign('groupByElements', $options);
872
873 foreach ($freqElements as $name) {
874 $this->addElement('select', "group_bys_freq[$name]",
875 ts('Frequency'), $this->_groupByDateFreq
876 );
877 }
878 }
879
880 function addOrderBys() {
881 $options = array();
882 foreach ($this->_columns as $tableName => $table) {
883
884 // Report developer may define any column to order by; include these as order-by options
885 if (array_key_exists('order_bys', $table)) {
886 foreach ($table['order_bys'] as $fieldName => $field) {
887 if (!empty($field)) {
888 $options[$fieldName] = $field['title'];
889 }
890 }
891 }
892
893 /* Add searchable custom fields as order-by options, if so requested
894 * (These are already indexed, so allowing to order on them is cheap.)
895 */
896
897
898 if ($this->_autoIncludeIndexedFieldsAsOrderBys && array_key_exists('extends', $table) && !empty($table['extends'])) {
899 foreach ($table['fields'] as $fieldName => $field) {
900 if (!array_key_exists('no_display', $field)) {
901 $options[$fieldName] = $field['title'];
902 }
903 }
904 }
905 }
906
907 asort($options);
908
909 $this->assign('orderByOptions', $options);
910
911 if (!empty($options)) {
912 $options = array(
913 '-' => ' - none - ') + $options;
914 for ($i = 1; $i <= 5; $i++) {
915 $this->addElement('select', "order_bys[{$i}][column]", ts('Order by Column'), $options);
916 $this->addElement('select', "order_bys[{$i}][order]", ts('Order by Order'), array('ASC' => 'Ascending', 'DESC' => 'Descending'));
917 $this->addElement('checkbox', "order_bys[{$i}][section]", ts('Order by Section'), FALSE, array('id' => "order_by_section_$i"));
918 }
919 }
920 }
921
922 function buildInstanceAndButtons() {
923 CRM_Report_Form_Instance::buildForm($this);
924
925 $label = $this->_id ? ts('Update Report') : ts('Create Report');
926
927 $this->addElement('submit', $this->_instanceButtonName, $label);
928 $this->addElement('submit', $this->_printButtonName, ts('Print Report'));
929 $this->addElement('submit', $this->_pdfButtonName, ts('PDF'));
930
931 if ($this->_id) {
932 $this->addElement('submit', $this->_createNewButtonName, ts('Save a Copy') . '...');
933 }
934 if ($this->_instanceForm) {
935 $this->assign('instanceForm', TRUE);
936 }
937
938 $label = $this->_id ? ts('Print Report') : ts('Print Preview');
939 $this->addElement('submit', $this->_printButtonName, $label);
940
941 $label = $this->_id ? ts('PDF') : ts('Preview PDF');
942 $this->addElement('submit', $this->_pdfButtonName, $label);
943
944 $label = $this->_id ? ts('Export to CSV') : ts('Preview CSV');
945
946 if ($this->_csvSupported) {
947 $this->addElement('submit', $this->_csvButtonName, $label);
948 }
949
950 if (CRM_Core_Permission::check('administer Reports') && $this->_add2groupSupported) {
951 $this->addElement('select', 'groups', ts('Group'),
952 array('' => ts('- select group -')) + CRM_Core_PseudoConstant::staticGroup()
953 );
954 $this->assign('group', TRUE);
955 }
956
957 $label = ts('Add these Contacts to Group');
958 $this->addElement('submit', $this->_groupButtonName, $label, array('onclick' => 'return checkGroup();'));
959
960 $this->addChartOptions();
961 $this->addButtons(array(
962 array(
963 'type' => 'submit',
964 'name' => ts('Preview Report'),
965 'isDefault' => TRUE,
966 ),
967 )
968 );
969 }
970
971 function buildQuickForm() {
972 $this->addColumns();
973
974 $this->addFilters();
975
976 $this->addOptions();
977
978 $this->addGroupBys();
979
980 $this->addOrderBys();
981
982 $this->buildInstanceAndButtons();
983
984 //add form rule for report
985 if (is_callable(array(
986 $this, 'formRule'))) {
987 $this->addFormRule(array(get_class($this), 'formRule'), $this);
988 }
989 }
990
991 // a formrule function to ensure that fields selected in group_by
992 // (if any) should only be the ones present in display/select fields criteria;
993 // note: works if and only if any custom field selected in group_by.
994 function customDataFormRule($fields, $ignoreFields = array( )) {
995 $errors = array();
996 if (!empty($this->_customGroupExtends) && $this->_customGroupGroupBy && !empty($fields['group_bys'])) {
997 foreach ($this->_columns as $tableName => $table) {
998 if ((substr($tableName, 0, 13) == 'civicrm_value' || substr($tableName, 0, 12) == 'custom_value') && !empty($this->_columns[$tableName]['fields'])) {
999 foreach ($this->_columns[$tableName]['fields'] as $fieldName => $field) {
1000 if (array_key_exists($fieldName, $fields['group_bys']) &&
1001 !array_key_exists($fieldName, $fields['fields'])
1002 ) {
1003 $errors['fields'] = "Please make sure fields selected in 'Group by Columns' section are also selected in 'Display Columns' section.";
1004 }
1005 elseif (array_key_exists($fieldName, $fields['group_bys'])) {
1006 foreach ($fields['fields'] as $fld => $val) {
1007 if (!array_key_exists($fld, $fields['group_bys']) && !in_array($fld, $ignoreFields)) {
1008 $errors['fields'] = "Please ensure that fields selected in 'Display Columns' are also selected in 'Group by Columns' section.";
1009 }
1010 }
1011 }
1012 }
1013 }
1014 }
1015 }
1016 return $errors;
1017 }
1018
1019 // Note: $fieldName param allows inheriting class to build operationPairs
1020 // specific to a field.
1b36206c 1021 function getOperationPair($type = "string", $fieldName = NULL) {
6a488035
TO		 1022 // FIXME: At some point we should move these key-val pairs
1023 // to option_group and option_value table.
6a488035
TO		 1024 switch ($type) {
1025 case CRM_Report_FORM::OP_INT:
1026 case CRM_Report_FORM::OP_FLOAT:
bc3f7f04 1027 return array(
1028 'lte' => ts('Is less than or equal to'),
6a488035
TO		 1029 'gte' => ts('Is greater than or equal to'),
1030 'bw' => ts('Is between'),
1031 'eq' => ts('Is equal to'),
1032 'lt' => ts('Is less than'),
1033 'gt' => ts('Is greater than'),
1034 'neq' => ts('Is not equal to'),
1035 'nbw' => ts('Is not between'),
1036 'nll' => ts('Is empty (Null)'),
1037 'nnll' => ts('Is not empty (Null)'),
1038 );
1039 break;
1040
1041 case CRM_Report_FORM::OP_SELECT:
bc3f7f04 1042 return array(
1043 'eq' => ts('Is equal to'),
1044 );
6a488035
TO		 1045
1046 case CRM_Report_FORM::OP_MONTH:
1047 case CRM_Report_FORM::OP_MULTISELECT:
bc3f7f04 1048 return array(
1049 'in' => ts('Is one of'),
6a488035
TO		 1050 'notin' => ts('Is not one of'),
1051 );
1052 break;
1053
1054 case CRM_Report_FORM::OP_DATE:
bc3f7f04 1055 return array(
1056 'nll' => ts('Is empty (Null)'),
6a488035
TO		 1057 'nnll' => ts('Is not empty (Null)'),
1058 );
1059 break;
1060
1061 case CRM_Report_FORM::OP_MULTISELECT_SEPARATOR:
1062 // use this operator for the values, concatenated with separator. For e.g if
1063 // multiple options for a column is stored as ^A{val1}^A{val2}^A
bc3f7f04 1064 return array(
1065 'mhas' => ts('Is one of'),
1066 );
6a488035
TO		 1067
1068 default:
1069 // type is string
bc3f7f04 1070 return array(
1071 'has' => ts('Contains'),
6a488035
TO		 1072 'sw' => ts('Starts with'),
1073 'ew' => ts('Ends with'),
1074 'nhas' => ts('Does not contain'),
1075 'eq' => ts('Is equal to'),
1076 'neq' => ts('Is not equal to'),
1077 'nll' => ts('Is empty (Null)'),
1078 'nnll' => ts('Is not empty (Null)'),
1079 );
1080 }
1081 }
1082
1083 function buildTagFilter() {
1084 $contactTags = CRM_Core_BAO_Tag::getTags();
1085 if (!empty($contactTags)) {
1086 $this->_columns['civicrm_tag'] = array(
1087 'dao' => 'CRM_Core_DAO_Tag',
1088 'filters' =>
1089 array(
1090 'tagid' =>
1091 array(
1092 'name' => 'tag_id',
1093 'title' => ts('Tag'),
1094 'tag' => TRUE,
1095 'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1096 'options' => $contactTags,
1097 ),
1098 ),
1099 );
1100 }
1101 }
1102
1103 /*
1104 * Adds group filters to _columns (called from _Constuct
1105 */
1106 function buildGroupFilter() {
1107 $this->_columns['civicrm_group']['filters'] = array(
1108 'gid' =>
1109 array(
1110 'name' => 'group_id',
1111 'title' => ts('Group'),
1112 'operatorType' => CRM_Report_Form::OP_MULTISELECT,
1113 'group' => TRUE,
1114 'options' => CRM_Core_PseudoConstant::group(),
1115 ),
1116 );
1117 if (empty($this->_columns['civicrm_group']['dao'])) {
1118 $this->_columns['civicrm_group']['dao'] = 'CRM_Contact_DAO_GroupContact';
1119 }
1120 if (empty($this->_columns['civicrm_group']['alias'])) {
1121 $this->_columns['civicrm_group']['alias'] = 'cgroup';
1122 }
1123 }
1124
29fc2b79 1125 function getSQLOperator($operator = "like") {
6a488035
TO		 1126 switch ($operator) {
1127 case 'eq':
1128 return '=';
1129
1130 case 'lt':
1131 return '<';
1132
1133 case 'lte':
1134 return '<=';
1135
1136 case 'gt':
1137 return '>';
1138
1139 case 'gte':
1140 return '>=';
1141
1142 case 'ne':
1143 case 'neq':
1144 return '!=';
1145
1146 case 'nhas':
1147 return 'NOT LIKE';
1148
1149 case 'in':
1150 return 'IN';
1151
1152 case 'notin':
1153 return 'NOT IN';
1154
1155 case 'nll':
1156 return 'IS NULL';
1157
1158 case 'nnll':
1159 return 'IS NOT NULL';
1160
1161 default:
1162 // type is string
1163 return 'LIKE';
1164 }
1165 }
1166
1167 function whereClause(&$field, $op,
1168 $value, $min, $max
1169 ) {
1170
1171 $type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
1172 $clause = NULL;
1173
1174 switch ($op) {
1175 case 'bw':
1176 case 'nbw':
1177 if (($min !== NULL && strlen($min) > 0) ||
1178 ($max !== NULL && strlen($max) > 0)
1179 ) {
1180 $min = CRM_Utils_Type::escape($min, $type);
1181 $max = CRM_Utils_Type::escape($max, $type);
1182 $clauses = array();
1183 if ($min) {
1184 if ($op == 'bw') {
1185 $clauses[] = "( {$field['dbAlias']} >= $min )";
1186 }
1187 else {
1188 $clauses[] = "( {$field['dbAlias']} < $min )";
1189 }
1190 }
1191 if ($max) {
1192 if ($op == 'bw') {
1193 $clauses[] = "( {$field['dbAlias']} <= $max )";
1194 }
1195 else {
1196 $clauses[] = "( {$field['dbAlias']} > $max )";
1197 }
1198 }
1199
1200 if (!empty($clauses)) {
1201 if ($op == 'bw') {
1202 $clause = implode(' AND ', $clauses);
1203 }
1204 else {
1205 $clause = implode(' OR ', $clauses);
1206 }
1207 }
1208 }
1209 break;
1210
1211 case 'has':
1212 case 'nhas':
1213 if ($value !== NULL && strlen($value) > 0) {
1214 $value = CRM_Utils_Type::escape($value, $type);
1215 if (strpos($value, '%') === FALSE) {
1216 $value = "'%{$value}%'";
1217 }
1218 else {
1219 $value = "'{$value}'";
1220 }
29fc2b79 1221 $sqlOP = $this->getSQLOperator($op);
6a488035
TO		 1222 $clause = "( {$field['dbAlias']} $sqlOP $value )";
1223 }
1224 break;
1225
1226 case 'in':
1227 case 'notin':
1228 if ($value !== NULL && is_array($value) && count($value) > 0) {
29fc2b79 1229 $sqlOP = $this->getSQLOperator($op);
6a488035
TO		 1230 if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
1231 //cycle through selections and esacape values
1232 foreach ($value as $key => $selection) {
1233 $value[$key] = CRM_Utils_Type::escape($selection, $type);
1234 }
1235 $clause = "( {$field['dbAlias']} $sqlOP ( '" . implode("' , '", $value) . "') )";
1236 }
1237 else {
1238 // for numerical values
1239 $clause = "{$field['dbAlias']} $sqlOP (" . implode(', ', $value) . ")";
1240 }
1241 if ($op == 'notin') {
1242 $clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
1243 }
1244 else {
1245 $clause = "( " . $clause . " )";
1246 }
1247 }
1248 break;
1249
1250 case 'mhas':
1251 // mhas == multiple has
1252 if ($value !== NULL && count($value) > 0) {
29fc2b79 1253 $sqlOP = $this->getSQLOperator($op);
6a488035
TO		 1254 $clause = "{$field['dbAlias']} REGEXP '[[:<:]]" . implode('|', $value) . "[[:>:]]'";
1255 }
1256 break;
1257
1258 case 'sw':
1259 case 'ew':
1260 if ($value !== NULL && strlen($value) > 0) {
1261 $value = CRM_Utils_Type::escape($value, $type);
1262 if (strpos($value, '%') === FALSE) {
1263 if ($op == 'sw') {
1264 $value = "'{$value}%'";
1265 }
1266 else {
1267 $value = "'%{$value}'";
1268 }
1269 }
1270 else {
1271 $value = "'{$value}'";
1272 }
29fc2b79 1273 $sqlOP = $this->getSQLOperator($op);
6a488035
TO		 1274 $clause = "( {$field['dbAlias']} $sqlOP $value )";
1275 }
1276 break;
1277
1278 case 'nll':
1279 case 'nnll':
29fc2b79 1280 $sqlOP = $this->getSQLOperator($op);
6a488035
TO		 1281 $clause = "( {$field['dbAlias']} $sqlOP )";
1282 break;
1283
1284 default:
1285 if ($value !== NULL && strlen($value) > 0) {
1286 if (isset($field['clause'])) {
1287 // FIXME: we not doing escape here. Better solution is to use two
1288 // different types - data-type and filter-type
0e6e8724 1289 $clause = $field['clause'];
6a488035
TO		 1290 }
1291 else {
1292 $value = CRM_Utils_Type::escape($value, $type);
29fc2b79 1293 $sqlOP = $this->getSQLOperator($op);
6a488035
TO		 1294 if ($field['type'] == CRM_Utils_Type::T_STRING) {
1295 $value = "'{$value}'";
1296 }
1297 $clause = "( {$field['dbAlias']} $sqlOP $value )";
1298 }
1299 }
1300 break;
1301 }
1302
1303 if (CRM_Utils_Array::value('group', $field) && $clause) {
1304 $clause = $this->whereGroupClause($field, $value, $op);
1305 }
1306 elseif (CRM_Utils_Array::value('tag', $field) && $clause) {
1307 // not using left join in query because if any contact
1308 // belongs to more than one tag, results duplicate
1309 // entries.
1310 $clause = $this->whereTagClause($field, $value, $op);
1311 }
1312
1313 return $clause;
1314 }
1315
1316 function dateClause($fieldName,
1317 $relative, $from, $to, $type = NULL, $fromTime = NULL, $toTime = NULL
1318 ) {
1319 $clauses = array();
1b36206c 1320 if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_FORM::OP_DATE)))) {
29fc2b79 1321 $sqlOP = $this->getSQLOperator($relative);
6a488035
TO		 1322 return "( {$fieldName} {$sqlOP} )";
1323 }
1324
29fc2b79 1325 list($from, $to) = $this->getFromTo($relative, $from, $to, $fromTime, $toTime);
6a488035
TO		 1326
1327 if ($from) {
1328 $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
1329 $clauses[] = "( {$fieldName} >= $from )";
1330 }
1331
1332 if ($to) {
1333 $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
1334 $clauses[] = "( {$fieldName} <= {$to} )";
1335 }
1336
1337 if (!empty($clauses)) {
1338 return implode(' AND ', $clauses);
1339 }
1340
1341 return NULL;
1342 }
29fc2b79 1343 /**
1344 * @todo - could not find any instances where this is called
1345 * @param unknown_type $relative
1346 * @param String $from
1347 * @param String_type $to
1348 * @return string|NULL
1349 */
1350 function dateDisplay($relative, $from, $to) {
1351 list($from, $to) = $this->getFromTo($relative, $from, $to);
6a488035
TO		 1352
1353 if ($from) {
1354 $clauses[] = CRM_Utils_Date::customFormat($from, NULL, array('m', 'M'));
1355 }
1356 else {
1357 $clauses[] = 'Past';
1358 }
1359
1360 if ($to) {
1361 $clauses[] = CRM_Utils_Date::customFormat($to, NULL, array('m', 'M'));
1362 }
1363 else {
1364 $clauses[] = 'Today';
1365 }
1366
1367 if (!empty($clauses)) {
1368 return implode(' - ', $clauses);
1369 }
1370
1371 return NULL;
1372 }
1373
29fc2b79 1374 function getFromTo($relative, $from, $to, $fromtime = NULL, $totime = NULL) {
6a488035
TO		 1375 if (empty($totime)) {
1376 $totime = '235959';
1377 }
1378 //FIX ME not working for relative
1379 if ($relative) {
1380 list($term, $unit) = CRM_Utils_System::explode('.', $relative, 2);
1381 $dateRange = CRM_Utils_Date::relativeToAbsolute($term, $unit);
1382 $from = substr($dateRange['from'], 0, 8);
1383 //Take only Date Part, Sometime Time part is also present in 'to'
1384 $to = substr($dateRange['to'], 0, 8);
1385 }
1386 $from = CRM_Utils_Date::processDate($from, $fromtime);
1387 $to = CRM_Utils_Date::processDate($to, $totime);
1388 return array($from, $to);
1389 }
1390
1391 function alterDisplay(&$rows) {
1392 // custom code to alter rows
1393 }
1394
1395 function alterCustomDataDisplay(&$rows) {
1396 // custom code to alter rows having custom values
1397 if (empty($this->_customGroupExtends)) {
1398 return;
1399 }
1400
1401 $customFieldIds = array();
1402 foreach ($this->_params['fields'] as $fieldAlias => $value) {
1403 if ($fieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
1404 $customFieldIds[$fieldAlias] = $fieldId;
1405 }
1406 }
1407 if (empty($customFieldIds)) {
1408 return;
1409 }
1410
1411 $customFields = $fieldValueMap = array();
1412 $customFieldCols = array('column_name', 'data_type', 'html_type', 'option_group_id', 'id');
1413
1414 // skip for type date and ContactReference since date format is already handled
1415 $query = "
1416SELECT cg.table_name, cf." . implode(", cf.", $customFieldCols) . ", ov.value, ov.label
1417FROM civicrm_custom_field cf
1418INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
1419LEFT JOIN civicrm_option_value ov ON cf.option_group_id = ov.option_group_id
1420WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
1421 cg.is_active = 1 AND
1422 cf.is_active = 1 AND
1423 cf.is_searchable = 1 AND
1424 cf.data_type NOT IN ('ContactReference', 'Date') AND
1425 cf.id IN (" . implode(",", $customFieldIds) . ")";
1426
1427 $dao = CRM_Core_DAO::executeQuery($query);
1428 while ($dao->fetch()) {
1429 foreach ($customFieldCols as $key) {
1430 $customFields[$dao->table_name . '_custom_' . $dao->id][$key] = $dao->$key;
1431 }
1432 if ($dao->option_group_id) {
1433 $fieldValueMap[$dao->option_group_id][$dao->value] = $dao->label;
1434 }
1435 }
1436 $dao->free();
1437
1438 $entryFound = FALSE;
1439 foreach ($rows as $rowNum => $row) {
1440 foreach ($row as $tableCol => $val) {
1441 if (array_key_exists($tableCol, $customFields)) {
1442 $rows[$rowNum][$tableCol] = $this->formatCustomValues($val, $customFields[$tableCol], $fieldValueMap);
1443 $entryFound = TRUE;
1444 }
1445 }
1446
1447 // skip looking further in rows, if first row itself doesn't
1448 // have the column we need
1449 if (!$entryFound) {
1450 break;
1451 }
1452 }
1453 }
1454
1455 function formatCustomValues($value, $customField, $fieldValueMap) {
1456 if (CRM_Utils_System::isNull($value)) {
1457 return;
1458 }
1459
1460 $htmlType = $customField['html_type'];
1461
1462 switch ($customField['data_type']) {
1463 case 'Boolean':
1464 if ($value == '1') {
1465 $retValue = ts('Yes');
1466 }
1467 else {
1468 $retValue = ts('No');
1469 }
1470 break;
1471
1472 case 'Link':
1473 $retValue = CRM_Utils_System::formatWikiURL($value);
1474 break;
1475
1476 case 'File':
1477 $retValue = $value;
1478 break;
1479
1480 case 'Memo':
1481 $retValue = $value;
1482 break;
1483
1484 case 'Float':
1485 if ($htmlType == 'Text') {
1486 $retValue = (float)$value;
1487 break;
1488 }
1489 case 'Money':
1490 if ($htmlType == 'Text') {
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500 $retValue = CRM_Utils_Money::format($value, NULL, '%a');
1501 break;
1502 }
1503 case 'String':
1504 case 'Int':
1505 if (in_array($htmlType, array(
1506 'Text', 'TextArea'))) {
1507 $retValue = $value;
1508 break;
1509 }
1510 case 'StateProvince':
1511 case 'Country':
1512
1513 switch ($htmlType) {
1514 case 'Multi-Select Country':
1515 $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
1516 $customData = array();
1517 foreach ($value as $val) {
1518 if ($val) {
1519 $customData[] = CRM_Core_PseudoConstant::country($val, FALSE);
1520 }
1521 }
1522 $retValue = implode(', ', $customData);
1523 break;
1524
1525 case 'Select Country':
1526 $retValue = CRM_Core_PseudoConstant::country($value, FALSE);
1527 break;
1528
1529 case 'Select State/Province':
1530 $retValue = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
1531 break;
1532
1533 case 'Multi-Select State/Province':
1534 $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
1535 $customData = array();
1536 foreach ($value as $val) {
1537 if ($val) {
1538 $customData[] = CRM_Core_PseudoConstant::stateProvince($val, FALSE);
1539 }
1540 }
1541 $retValue = implode(', ', $customData);
1542 break;
1543
1544 case 'Select':
1545 case 'Radio':
1546 case 'Autocomplete-Select':
1547 $retValue = $fieldValueMap[$customField['option_group_id']][$value];
1548 break;
1549
1550 case 'CheckBox':
1551 case 'AdvMulti-Select':
1552 case 'Multi-Select':
1553 $value = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
1554 $customData = array();
1555 foreach ($value as $val) {
1556 if ($val) {
1557 $customData[] = $fieldValueMap[$customField['option_group_id']][$val];
1558 }
1559 }
1560 $retValue = implode(', ', $customData);
1561 break;
1562
1563 default:
1564 $retValue = $value;
1565 }
1566 break;
1567
1568 default:
1569 $retValue = $value;
1570 }
1571
1572 return $retValue;
1573 }
1574
1575 function removeDuplicates(&$rows) {
1576 if (empty($this->_noRepeats)) {
1577 return;
1578 }
1579 $checkList = array();
1580
1581 foreach ($rows as $key => $list) {
1582 foreach ($list as $colName => $colVal) {
8f1445ea 1583 if (array_key_exists($colName, $checkList) &&
6a488035
TO		 1584 $checkList[$colName] == $colVal) {
1585 $rows[$key][$colName] = "";
1586 }
1587 if (in_array($colName, $this->_noRepeats)) {
1588 $checkList[$colName] = $colVal;
1589 }
1590 }
1591 }
1592 }
1593
1594 function fixSubTotalDisplay(&$row, $fields, $subtotal = TRUE) {
1595 foreach ($row as $colName => $colVal) {
1596 if (in_array($colName, $fields)) {
1597 $row[$colName] = $row[$colName];
1598 }
1599 elseif (isset($this->_columnHeaders[$colName])) {
1600 if ($subtotal) {
1601 $row[$colName] = "Subtotal";
1602 $subtotal = FALSE;
1603 }
1604 else {
1605 unset($row[$colName]);
1606 }
1607 }
1608 }
1609 }
1610
1611 function grandTotal(&$rows) {
1612 if (!$this->_rollup || ($this->_rollup == '') ||
1613 ($this->_limit && count($rows) >= self::ROW_COUNT_LIMIT)
1614 ) {
1615 return FALSE;
1616 }
1617 $lastRow = array_pop($rows);
1618
6a488035
TO		 1619 foreach ($this->_columnHeaders as $fld => $val) {
1620 if (!in_array($fld, $this->_statFields)) {
1621 if (!$this->_grandFlag) {
1622 $lastRow[$fld] = "Grand Total";
1623 $this->_grandFlag = TRUE;
1624 }
1625 else {
1626 $lastRow[$fld] = "";
1627 }
1628 }
1629 }
1630
1631 $this->assign('grandStat', $lastRow);
1632 return TRUE;
1633 }
1634
1635 function formatDisplay(&$rows, $pager = TRUE) {
1636 // set pager based on if any limit was applied in the query.
1637 if ($pager) {
1638 $this->setPager();
1639 }
1640
1641 // allow building charts if any
1642 if (!empty($this->_params['charts']) && !empty($rows)) {
1643 $this->buildChart($rows);
1644 $this->assign('chartEnabled', TRUE);
1645 $this->_chartId = "{$this->_params['charts']}_" . ($this->_id ? $this->_id : substr(get_class($this), 16)) . '_' . session_id();
1646 $this->assign('chartId', $this->_chartId);
1647 }
1648
1649 // unset columns not to be displayed.
1650 foreach ($this->_columnHeaders as $key => $value) {
1651 if (is_array($value) && isset($value['no_display'])) {
1652 unset($this->_columnHeaders[$key]);
1653 }
1654 }
1655
1656 // unset columns not to be displayed.
1657 if (!empty($rows)) {
1658 foreach ($this->_noDisplay as $noDisplayField) {
1659 foreach ($rows as $rowNum => $row) {
1660 unset($this->_columnHeaders[$noDisplayField]);
1661 }
1662 }
1663 }
1664
1665 // build array of section totals
1666 $this->sectionTotals();
1667
1668 // process grand-total row
1669 $this->grandTotal($rows);
1670
1671 // use this method for formatting rows for display purpose.
1672 $this->alterDisplay($rows);
1673 CRM_Utils_Hook::alterReportVar('rows', $rows, $this);
1674
1675 // use this method for formatting custom rows for display purpose.
1676 $this->alterCustomDataDisplay($rows);
1677 }
1678
1679 function buildChart(&$rows) {
1680 // override this method for building charts.
1681 }
1682
1683 // select() method below has been added recently (v3.3), and many of the report templates might
1684 // still be having their own select() method. We should fix them as and when encountered and move
1685 // towards generalizing the select() method below.
1686 function select() {
1687 $select = array();
1688
1689 foreach ($this->_columns as $tableName => $table) {
1690 if (array_key_exists('fields', $table)) {
1691 foreach ($table['fields'] as $fieldName => $field) {
1692 if ($tableName == 'civicrm_address') {
1693 $this->_addressField = TRUE;
1694 }
1695 if ($tableName == 'civicrm_email') {
1696 $this->_emailField = TRUE;
1697 }
1698 if ($tableName == 'civicrm_phone') {
1699 $this->_phoneField = TRUE;
1700 }
1701
1702 if (CRM_Utils_Array::value('required', $field) ||
1703 CRM_Utils_Array::value($fieldName, $this->_params['fields'])
1704 ) {
1705
1706 // 1. In many cases we want select clause to be built in slightly different way
1707 // for a particular field of a particular type.
1708 // 2. This method when used should receive params by reference and modify $this->_columnHeaders
1709 // as needed.
1710 $selectClause = $this->selectClause($tableName, 'fields', $fieldName, $field);
1711 if ($selectClause) {
1712 $select[] = $selectClause;
1713 continue;
1714 }
1715
1716 // include statistics columns only if set
1717 if (CRM_Utils_Array::value('statistics', $field)) {
1718 foreach ($field['statistics'] as $stat => $label) {
1719 $alias = "{$tableName}_{$fieldName}_{$stat}";
1720 switch (strtolower($stat)) {
1721 case 'max':
1722 case 'sum':
1723 $select[] = "$stat({$field['dbAlias']}) as $alias";
1724 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
1725 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
1726 $this->_statFields[] = $alias;
1727 $this->_selectAliases[] = $alias;
1728 break;
1729
1730 case 'count':
1731 $select[] = "COUNT({$field['dbAlias']}) as $alias";
1732 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
1733 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
1734 $this->_statFields[] = $alias;
1735 $this->_selectAliases[] = $alias;
1736 break;
1737
1738 case 'avg':
1739 $select[] = "ROUND(AVG({$field['dbAlias']}),2) as $alias";
1740 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
1741 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
1742 $this->_statFields[] = $alias;
1743 $this->_selectAliases[] = $alias;
1744 break;
1745 }
1746 }
1747 }
1748 else {
1749 $alias = "{$tableName}_{$fieldName}";
1750 $select[] = "{$field['dbAlias']} as $alias";
1751 $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
1752 $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
1753 $this->_selectAliases[] = $alias;
1754 }
1755 }
1756 }
1757 }
1758
1759 // select for group bys
1760 if (array_key_exists('group_bys', $table)) {
1761 foreach ($table['group_bys'] as $fieldName => $field) {
1762
1763 if ($tableName == 'civicrm_address') {
1764 $this->_addressField = TRUE;
1765 }
1766 if ($tableName == 'civicrm_email') {
1767 $this->_emailField = TRUE;
1768 }
1769 if ($tableName == 'civicrm_phone') {
1770 $this->_phoneField = TRUE;
1771 }
1772 // 1. In many cases we want select clause to be built in slightly different way
1773 // for a particular field of a particular type.
1774 // 2. This method when used should receive params by reference and modify $this->_columnHeaders
1775 // as needed.
1776 $selectClause = $this->selectClause($tableName, 'group_bys', $fieldName, $field);
1777 if ($selectClause) {
1778 $select[] = $selectClause;
1779 continue;
1780 }
1781
1782 if (!empty($this->_params['group_bys']) && CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])
1783 && !empty($this->_params['group_bys_freq'])) {
1784 switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
1785 case 'YEARWEEK':
1786 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
1787 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
1788 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
1789 $field['title'] = 'Week';
1790 break;
1791
1792 case 'YEAR':
1793 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
1794 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
1795 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
1796 $field['title'] = 'Year';
1797 break;
1798
1799 case 'MONTH':
1800 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
1801 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
1802 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
1803 $field['title'] = 'Month';
1804 break;
1805
1806 case 'QUARTER':
1807 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
1808 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
1809 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
1810 $field['title'] = 'Quarter';
1811 break;
1812 }
1813 // for graphs and charts -
1814 if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
1815 $this->_interval = $field['title'];
1816 $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
1817 $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
1818 $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
1819
1820 // just to make sure these values are transfered to rows.
1821 // since we 'll need them for calculation purpose,
1822 // e.g making subtotals look nicer or graphs
1823 $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
1824 $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
1825 }
1826 }
1827 }
1828 }
1829 }
1830
1831 $this->_select = "SELECT " . implode(', ', $select) . " ";
1832 }
1833
1834 function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
1835 return FALSE;
1836 }
1837
1838 function where() {
adfe2750 1839 $this->storeWhereHavingClauseArray();
1840
1841 if (empty($this->_whereClauses)) {
1842 $this->_where = "WHERE ( 1 ) ";
1843 $this->_having = "";
1844 }
1845 else {
1846 $this->_where = "WHERE " . implode(' AND ', $this->_whereClauses);
1847 }
1848
1849 if ($this->_aclWhere) {
1850 $this->_where .= " AND {$this->_aclWhere} ";
1851 }
1852
1853 if (!empty($this->_havingClauses)) {
1854 // use this clause to construct group by clause.
1855 $this->_having = "HAVING " . implode(' AND ', $this->_havingClauses);
1856 }
1857 }
1858
1859 /**
1860 * Store Where clauses into an array - breaking out this step makes
1861 * over-riding more flexible as the clauses can be used in constructing a
1862 * temp table that may not be part of the final where clause or added
1863 * in other functions
1864 */
1865 function storeWhereHavingClauseArray(){
6a488035
TO		 1866 foreach ($this->_columns as $tableName => $table) {
1867 if (array_key_exists('filters', $table)) {
1868 foreach ($table['filters'] as $fieldName => $field) {
d12de91c 1869 // respect pseudofield to filter spec so fields can be marked as
1870 // not to be handled here
1871 if(!empty($field['pseudofield'])){
1872 continue;
1873 }
6a488035
TO		 1874 $clause = NULL;
1875 if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
1876 if (CRM_Utils_Array::value('operatorType', $field) == CRM_Report_Form::OP_MONTH) {
1877 $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
1878 $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
1879 if (is_array($value) && !empty($value)) {
1880 $clause = "(month({$field['dbAlias']}) $op (" . implode(', ', $value) . '))';
1881 }
1882 }
1883 else {
1884 $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
1885 $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
1886 $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
1887 $fromTime = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params);
1888 $toTime = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params);
1889 $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type'], $fromTime, $toTime);
1890 }
1891 }
1892 else {
1893 $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
1894 if ($op) {
1895 $clause = $this->whereClause($field,
adfe2750 1896 $op,
1897 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
1898 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
1899 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
6a488035
TO		 1900 );
1901 }
1902 }
1903
1904 if (!empty($clause)) {
1905 if (CRM_Utils_Array::value('having', $field)) {
adfe2750 1906 $this->_havingClauses[] = $clause;
6a488035
TO		 1907 }
1908 else {
adfe2750 1909 $this->_whereClauses[] = $clause;
6a488035
TO		 1910 }
1911 }
1912 }
1913 }
1914 }
1915
6a488035 1916 }
6a488035
TO		 1917 function processReportMode() {
1918 $buttonName = $this->controller->getButtonName();
1919
884605ca
DL		 1920 $output = CRM_Utils_Request::retrieve(
1921 'output',
1922 'String',
1923 CRM_Core_DAO::$_nullObject
6a488035
TO		 1924 );
1925
1926 $this->_sendmail =
884605ca
DL		 1927 CRM_Utils_Request::retrieve(
1928 'sendmail',
1929 'Boolean',
1930 CRM_Core_DAO::$_nullObject
1931 );
6a488035
TO		 1932
1933 $this->_absoluteUrl = FALSE;
1934 $printOnly = FALSE;
1935 $this->assign('printOnly', FALSE);
1936
1937 if ($this->_printButtonName == $buttonName || $output == 'print' || ($this->_sendmail && !$output)) {
1938 $this->assign('printOnly', TRUE);
1939 $printOnly = TRUE;
1940 $this->assign('outputMode', 'print');
1941 $this->_outputMode = 'print';
1942 if ($this->_sendmail) {
1943 $this->_absoluteUrl = TRUE;
1944 }
1945 }
1946 elseif ($this->_pdfButtonName == $buttonName || $output == 'pdf') {
1947 $this->assign('printOnly', TRUE);
1948 $printOnly = TRUE;
1949 $this->assign('outputMode', 'pdf');
1950 $this->_outputMode = 'pdf';
1951 $this->_absoluteUrl = TRUE;
1952 }
1953 elseif ($this->_csvButtonName == $buttonName || $output == 'csv') {
1954 $this->assign('printOnly', TRUE);
1955 $printOnly = TRUE;
1956 $this->assign('outputMode', 'csv');
1957 $this->_outputMode = 'csv';
1958 $this->_absoluteUrl = TRUE;
1959 }
1960 elseif ($this->_groupButtonName == $buttonName || $output == 'group') {
1961 $this->assign('outputMode', 'group');
1962 $this->_outputMode = 'group';
1963 }
1964 elseif ($output == 'create_report' && $this->_criteriaForm) {
1965 $this->assign('outputMode', 'create_report');
1966 $this->_outputMode = 'create_report';
1967 }
1968 else {
1969 $this->assign('outputMode', 'html');
1970 $this->_outputMode = 'html';
1971 }
1972
1973 // Get today's date to include in printed reports
1974 if ($printOnly) {
1975 $reportDate = CRM_Utils_Date::customFormat(date('Y-m-d H:i'));
1976 $this->assign('reportDate', $reportDate);
1977 }
1978 }
1979
1980 function beginPostProcess() {
1981 $this->_params = $this->controller->exportValues($this->_name);
1982
1983 if (empty($this->_params) &&
1984 $this->_force
1985 ) {
1986 $this->_params = $this->_formValues;
1987 }
1988
1989 // hack to fix params when submitted from dashboard, CRM-8532
1990 // fields array is missing because form building etc is skipped
1991 // in dashboard mode for report
1992 if (!CRM_Utils_Array::value('fields', $this->_params) && !$this->_noFields) {
1993 $this->_params = $this->_formValues;
1994 }
1995
1996 $this->_formValues = $this->_params;
1997 if (CRM_Core_Permission::check('administer Reports') &&
1998 isset($this->_id) &&
1999 ($this->_instanceButtonName == $this->controller->getButtonName() . '_save' ||
2000 $this->_chartButtonName == $this->controller->getButtonName()
2001 )
2002 ) {
2003 $this->assign('updateReportButton', TRUE);
2004 }
2005 $this->processReportMode();
2006 }
2007
2008 function buildQuery($applyLimit = TRUE) {
2009 $this->select();
2010 $this->from();
2011 $this->customDataFrom();
2012 $this->where();
2013 $this->groupBy();
2014 $this->orderBy();
2015
2016 // order_by columns not selected for display need to be included in SELECT
2017 $unselectedSectionColumns = $this->unselectedSectionColumns();
2018 foreach ($unselectedSectionColumns as $alias => $section) {
2019 $this->_select .= ", {$section['dbAlias']} as {$alias}";
2020 }
2021
2022 if ($applyLimit && !CRM_Utils_Array::value('charts', $this->_params)) {
2023 $this->limit();
2024 }
2025 CRM_Utils_Hook::alterReportVar('sql', $this, $this);
2026
2027 $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
2028 return $sql;
2029 }
2030
2031 function groupBy() {
2032 $groupBys = array();
2033 if (CRM_Utils_Array::value('group_bys', $this->_params) &&
2034 is_array($this->_params['group_bys']) &&
2035 !empty($this->_params['group_bys'])
2036 ) {
2037 foreach ($this->_columns as $tableName => $table) {
2038 if (array_key_exists('group_bys', $table)) {
2039 foreach ($table['group_bys'] as $fieldName => $field) {
2040 if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
2041 $groupBys[] = $field['dbAlias'];
2042 }
2043 }
2044 }
2045 }
2046 }
2047
2048 if (!empty($groupBys)) {
2049 $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
2050 }
2051 }
2052
2053 function orderBy() {
2054 $this->_orderBy = "";
2055 $this->_sections = array();
2056 $this->storeOrderByArray();
2057 if(!empty($this->_orderByArray) && !$this->_rollup == 'WITH ROLLUP'){
2058 $this->_orderBy = "ORDER BY " . implode(', ', $this->_orderByArray);
2059 }
2060 $this->assign('sections', $this->_sections);
2061 }
2062 /*
2063 * In some cases other functions want to know which fields are selected for ordering by
2064 * Separating this into a separate function allows it to be called separately from constructing
2065 * the order by clause
2066 */
2067 function storeOrderByArray() {
2068 $orderBys = array();
2069
2070 if (CRM_Utils_Array::value('order_bys', $this->_params) &&
2071 is_array($this->_params['order_bys']) &&
2072 !empty($this->_params['order_bys'])
2073 ) {
2074
2075 // Proces order_bys in user-specified order
2076 foreach ($this->_params['order_bys'] as $orderBy) {
2077 $orderByField = array();
2078 foreach ($this->_columns as $tableName => $table) {
2079 if (array_key_exists('order_bys', $table)) {
2080 // For DAO columns defined in $this->_columns
2081 $fields = $table['order_bys'];
2082 }
2083 elseif (array_key_exists('extends', $table)) {
2084 // For custom fields referenced in $this->_customGroupExtends
2085 $fields = $table['fields'];
2086 }
2087 if (!empty($fields) && is_array($fields)) {
2088 foreach ($fields as $fieldName => $field) {
2089 if ($fieldName == $orderBy['column']) {
2090 $orderByField = $field;
2091 $orderByField['tplField'] = "{$tableName}_{$fieldName}";
2092 break 2;
2093 }
2094 }
2095 }
2096 }
2097
2098 if (!empty($orderByField)) {
2099 $orderBys[] = "{$orderByField['dbAlias']} {$orderBy['order']}";
2100
2101 // Record any section headers for assignment to the template
2102 if (CRM_Utils_Array::value('section', $orderBy)) {
2103 $this->_sections[$orderByField['tplField']] = $orderByField;
2104 }
2105 }
2106 }
2107 }
2108
2109 $this->_orderByArray = $orderBys;
2110
2111 $this->assign('sections', $this->_sections);
2112 }
2113
2114 function unselectedSectionColumns() {
2115 $selectColumns = array();
2116 foreach ($this->_columns as $tableName => $table) {
2117 if (array_key_exists('fields', $table)) {
2118 foreach ($table['fields'] as $fieldName => $field) {
2119 if (CRM_Utils_Array::value('required', $field) ||
2120 CRM_Utils_Array::value($fieldName, $this->_params['fields'])
2121 ) {
2122
2123 $selectColumns["{$tableName}_{$fieldName}"] = 1;
2124 }
2125 }
2126 }
2127 }
2128 if (is_array($this->_sections)) {
2129 return array_diff_key($this->_sections, $selectColumns);
2130 }
2131 else {
2132 return array();
2133 }
2134 }
2135
2136 function buildRows($sql, &$rows) {
2137 $dao = CRM_Core_DAO::executeQuery($sql);
2138 if (!is_array($rows)) {
2139 $rows = array();
2140 }
2141
2142 // use this method to modify $this->_columnHeaders
2143 $this->modifyColumnHeaders();
2144
2145 $unselectedSectionColumns = $this->unselectedSectionColumns();
2146
2147 while ($dao->fetch()) {
2148 $row = array();
2149 foreach ($this->_columnHeaders as $key => $value) {
2150 if (property_exists($dao, $key)) {
2151 $row[$key] = $dao->$key;
2152 }
2153 }
2154
2155 // section headers not selected for display need to be added to row
2156 foreach ($unselectedSectionColumns as $key => $values) {
2157 if (property_exists($dao, $key)) {
2158 $row[$key] = $dao->$key;
2159 }
2160 }
2161
2162 $rows[] = $row;
2163 }
2164 }
2165
2166 /**
2167 * When "order by" fields are marked as sections, this assigns to the template
2168 * an array of total counts for each section. This data is used by the Smarty
2169 * plugin {sectionTotal}
2170 */
2171 function sectionTotals() {
2172
2173 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
2174 if (empty($this->_selectAliases)) {
2175 return;
2176 }
2177
2178 if (!empty($this->_sections)) {
2179 // build the query with no LIMIT clause
2180 $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
2181 $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
2182
2183 // pull section aliases out of $this->_sections
2184 $sectionAliases = array_keys($this->_sections);
2185
2186 $ifnulls = array();
2187 foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
2188 $ifnulls[] = "ifnull($alias, '') as $alias";
2189 }
2190
2191 /* Group (un-limited) report by all aliases and get counts. This might
2192 * be done more efficiently when the contents of $sql are known, ie. by
2193 * overriding this method in the report class.
2194 */
2195
2196
2197 $query = "select " . implode(", ", $ifnulls) . ", count(*) as ct from ($sql) as subquery group by " . implode(", ", $sectionAliases);
2198
2199 // initialize array of total counts
2200 $totals = array();
2201 $dao = CRM_Core_DAO::executeQuery($query);
2202 while ($dao->fetch()) {
2203
2204 // let $this->_alterDisplay translate any integer ids to human-readable values.
2205 $rows[0] = $dao->toArray();
2206 $this->alterDisplay($rows);
2207 $row = $rows[0];
2208
2209 // add totals for all permutations of section values
2210 $values = array();
2211 $i = 1;
2212 $aliasCount = count($sectionAliases);
2213 foreach ($sectionAliases as $alias) {
2214 $values[] = $row[$alias];
2215 $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
2216 if ($i == $aliasCount) {
2217 // the last alias is the lowest-level section header; use count as-is
2218 $totals[$key] = $dao->ct;
2219 }
2220 else {
2221 // other aliases are higher level; roll count into their total
2222 $totals[$key] += $dao->ct;
2223 }
2224 }
2225 }
2226 $this->assign('sectionTotals', $totals);
2227 }
2228 }
2229
2230 function modifyColumnHeaders() {
2231 // use this method to modify $this->_columnHeaders
2232 }
2233
2234 function doTemplateAssignment(&$rows) {
2235 $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
2236 $this->assign_by_ref('rows', $rows);
2237 $this->assign('statistics', $this->statistics($rows));
2238 }
2239
2240 // override this method to build your own statistics
2241 function statistics(&$rows) {
2242 $statistics = array();
2243
2244 $count = count($rows);
2245
2246 if ($this->_rollup && ($this->_rollup != '') && $this->_grandFlag) {
2247 $count++;
2248 }
2249
2250 $this->countStat($statistics, $count);
2251
2252 $this->groupByStat($statistics);
2253
2254 $this->filterStat($statistics);
2255
2256 return $statistics;
2257 }
2258
2259 function countStat(&$statistics, $count) {
2260 $statistics['counts']['rowCount'] = array('title' => ts('Row(s) Listed'),
2261 'value' => $count,
2262 );
2263
2264 if ($this->_rowsFound && ($this->_rowsFound > $count)) {
2265 $statistics['counts']['rowsFound'] = array('title' => ts('Total Row(s)'),
2266 'value' => $this->_rowsFound,
2267 );
2268 }
2269 }
2270
2271 function groupByStat(&$statistics) {
2272 if (CRM_Utils_Array::value('group_bys', $this->_params) &&
2273 is_array($this->_params['group_bys']) &&
2274 !empty($this->_params['group_bys'])
2275 ) {
2276 foreach ($this->_columns as $tableName => $table) {
2277 if (array_key_exists('group_bys', $table)) {
2278 foreach ($table['group_bys'] as $fieldName => $field) {
2279 if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
2280 $combinations[] = $field['title'];
2281 }
2282 }
2283 }
2284 }
2285 $statistics['groups'][] = array('title' => ts('Grouping(s)'),
2286 'value' => implode(' & ', $combinations),
2287 );
2288 }
2289 }
2290
2291 function filterStat(&$statistics) {
2292 foreach ($this->_columns as $tableName => $table) {
2293 if (array_key_exists('filters', $table)) {
2294 foreach ($table['filters'] as $fieldName => $field) {
2295 if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE && CRM_Utils_Array::value('operatorType', $field) != CRM_Report_Form::OP_MONTH) {
8f1445ea
DL		 2296 list($from, $to) =
2297 $this->getFromTo(
2298 CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
2299 CRM_Utils_Array::value("{$fieldName}_from", $this->_params),
2300 CRM_Utils_Array::value("{$fieldName}_to", $this->_params),
2301 CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params),
2302 CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params)
2303 );
6a488035
TO		 2304 $from_time_format = CRM_Utils_Array::value("{$fieldName}_from_time", $this->_params) ? 'h' : 'd';
2305 $from = CRM_Utils_Date::customFormat($from, null, array($from_time_format));
2306
2307 $to_time_format = CRM_Utils_Array::value("{$fieldName}_to_time", $this->_params) ? 'h' : 'd';
2308 $to = CRM_Utils_Date::customFormat($to, null, array($to_time_format));
2309
2310 if ($from || $to) {
2311 $statistics['filters'][] = array(
2312 'title' => $field['title'],
10a5be27 2313 'value' => ts("Between %1 and %2", array(1 => $from, 2 => $to)),
6a488035
TO		 2314 );
2315 }
2316 elseif (in_array($rel = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params),
1b36206c 2317 array_keys($this->getOperationPair(CRM_Report_FORM::OP_DATE))
6a488035 2318 )) {
1b36206c 2319 $pair = $this->getOperationPair(CRM_Report_FORM::OP_DATE);
6a488035
TO		 2320 $statistics['filters'][] = array(
2321 'title' => $field['title'],
2322 'value' => $pair[$rel],
2323 );
2324 }
2325 }
2326 else {
2327 $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
2328 $value = NULL;
2329 if ($op) {
1b36206c 2330 $pair = $this->getOperationPair(
8f1445ea
DL		 2331 CRM_Utils_Array::value('operatorType', $field),
2332 $fieldName
6a488035
TO		 2333 );
2334 $min = CRM_Utils_Array::value("{$fieldName}_min", $this->_params);
2335 $max = CRM_Utils_Array::value("{$fieldName}_max", $this->_params);
2336 $val = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
2337 if (in_array($op, array(
2338 'bw', 'nbw')) && ($min || $max)) {
2339 $value = "{$pair[$op]} " . $min . ' and ' . $max;
2340 }
2341 elseif ($op == 'nll' || $op == 'nnll') {
2342 $value = $pair[$op];
2343 }
2344 elseif (is_array($val) && (!empty($val))) {
2345 $options = $field['options'];
2346 foreach ($val as $key => $valIds) {
2347 if (isset($options[$valIds])) {
2348 $val[$key] = $options[$valIds];
2349 }
2350 }
2351 $pair[$op] = (count($val) == 1) ? (($op == 'notin') ? ts('Is Not') : ts('Is')) : $pair[$op];
2352 $val = implode(', ', $val);
2353 $value = "{$pair[$op]} " . $val;
2354 }
2355 elseif (!is_array($val) && (!empty($val) || $val == '0') && isset($field['options']) &&
2356 is_array($field['options']) && !empty($field['options'])
2357 ) {
2358 $value = CRM_Utils_Array::value($op, $pair) . " " . CRM_Utils_Array::value($val, $field['options'], $val);
2359 }
2360 elseif ($val) {
2361 $value = CRM_Utils_Array::value($op, $pair) . " " . $val;
2362 }
2363 }
2364 if ($value) {
2365 $statistics['filters'][] = array('title' => CRM_Utils_Array::value('title', $field),
2366 'value' => $value,
2367 );
2368 }
2369 }
2370 }
2371 }
2372 }
2373 }
2374
2375 function endPostProcess(&$rows = NULL) {
ae555e90
DS		 2376 if ( $this->_storeResultSet ) {
2377 $this->_resultSet = $rows;
2378 }
2379
6a488035
TO		 2380 if ($this->_outputMode == 'print' ||
2381 $this->_outputMode == 'pdf' ||
2382 $this->_sendmail
2383 ) {
2384
2385 $content = $this->compileContent();
2386 $url = CRM_Utils_System::url("civicrm/report/instance/{$this->_id}",
2387 "reset=1", TRUE
2388 );
2389
2390 if ($this->_sendmail) {
2391 $config = CRM_Core_Config::singleton();
2392 $attachments = array();
2393
2394 if ($this->_outputMode == 'csv') {
2395 $content = $this->_formValues['report_header'] . '<p>' . ts('Report URL') . ": {$url}</p>" . '<p>' . ts('The report is attached as a CSV file.') . '</p>' . $this->_formValues['report_footer'];
2396
2397 $csvFullFilename = $config->templateCompileDir . CRM_Utils_File::makeFileName('CiviReport.csv');
2398 $csvContent = CRM_Report_Utils_Report::makeCsv($this, $rows);
2399 file_put_contents($csvFullFilename, $csvContent);
2400 $attachments[] = array(
2401 'fullPath' => $csvFullFilename,
2402 'mime_type' => 'text/csv',
2403 'cleanName' => 'CiviReport.csv',
2404 );
2405 }
2406 if ($this->_outputMode == 'pdf') {
2407 // generate PDF content
2408 $pdfFullFilename = $config->templateCompileDir . CRM_Utils_File::makeFileName('CiviReport.pdf');
2409 file_put_contents($pdfFullFilename,
2410 CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf",
2411 TRUE, array('orientation' => 'landscape')
2412 )
2413 );
2414 // generate Email Content
2415 $content = $this->_formValues['report_header'] . '<p>' . ts('Report URL') . ": {$url}</p>" . '<p>' . ts('The report is attached as a PDF file.') . '</p>' . $this->_formValues['report_footer'];
2416
2417 $attachments[] = array(
2418 'fullPath' => $pdfFullFilename,
2419 'mime_type' => 'application/pdf',
2420 'cleanName' => 'CiviReport.pdf',
2421 );
2422 }
2423
2424 if (CRM_Report_Utils_Report::mailReport($content, $this->_id,
2425 $this->_outputMode, $attachments
2426 )) {
2427 CRM_Core_Session::setStatus(ts("Report mail has been sent."), ts('Sent'), 'success');
2428 }
2429 else {
2430 CRM_Core_Session::setStatus(ts("Report mail could not be sent."), ts('Mail Error'), 'error');
2431 }
2432
1c4d8c3e 2433 CRM_Utils_System::redirect(CRM_Utils_System::url(CRM_Utils_System::currentPath(), 'reset=1'));
6a488035
TO		 2434 }
2435 elseif ($this->_outputMode == 'print') {
2436 echo $content;
2437 }
2438 else {
2439 if ($chartType = CRM_Utils_Array::value('charts', $this->_params)) {
2440 $config = CRM_Core_Config::singleton();
2441 //get chart image name
2442 $chartImg = $this->_chartId . '.png';
2443 //get image url path
2444 $uploadUrl = str_replace('/persist/contribute/', '/persist/', $config->imageUploadURL) . 'openFlashChart/';
2445 $uploadUrl .= $chartImg;
2446 //get image doc path to overwrite
2447 $uploadImg = str_replace('/persist/contribute/', '/persist/', $config->imageUploadDir) . 'openFlashChart/' . $chartImg;
2448 //Load the image
2449 $chart = imagecreatefrompng($uploadUrl);
2450 //convert it into formattd png
2451 header('Content-type: image/png');
2452 //overwrite with same image
2453 imagepng($chart, $uploadImg);
2454 //delete the object
2455 imagedestroy($chart);
2456 }
2457 CRM_Utils_PDF_Utils::html2pdf($content, "CiviReport.pdf", FALSE, array('orientation' => 'landscape'));
2458 }
2459 CRM_Utils_System::civiExit();
2460 }
2461 elseif ($this->_outputMode == 'csv') {
2462 CRM_Report_Utils_Report::export2csv($this, $rows);
2463 }
2464 elseif ($this->_outputMode == 'group') {
2465 $group = $this->_params['groups'];
2466 $this->add2group($group);
2467 }
2468 elseif ($this->_instanceButtonName == $this->controller->getButtonName()) {
2469 CRM_Report_Form_Instance::postProcess($this);
2470 }
2471 elseif ($this->_createNewButtonName == $this->controller->getButtonName() ||
2472 $this->_outputMode == 'create_report' ) {
2473 $this->_createNew = TRUE;
2474 CRM_Report_Form_Instance::postProcess($this);
2475 }
2476 }
8f1445ea 2477
ae555e90
DS		 2478 function storeResultSet() {
2479 $this->_storeResultSet = TRUE;
2480 }
2481
2482 function getResultSet() {
2483 return $this->_resultSet;
2484 }
2485
6a488035
TO		 2486 /*
2487 * Get Template file name - use default form template if a specific one has not been set up for this report
2488 *
2489 */
2490 function getTemplateFileName(){
2491 $defaultTpl = parent::getTemplateFileName();
2492 $template = CRM_Core_Smarty::singleton();
2493 if (!$template->template_exists($defaultTpl)) {
2494 $defaultTpl = 'CRM/Report/Form.tpl';
2495 }
2496 return $defaultTpl;
2497 }
2498
2499 /*
2500 * Compile the report content
2501 *
2502 * Although this function is super-short it is useful to keep separate so it can be over-ridden by report classes.
2503 */
2504 function compileContent(){
2505 $templateFile = $this->getTemplateFileName();
2506 return $this->_formValues['report_header'] . CRM_Core_Form::$_template->fetch($templateFile) . $this->_formValues['report_footer'];
2507 }
2508
2509
2510 function postProcess() {
2511 // get ready with post process params
2512 $this->beginPostProcess();
2513
2514 // build query
2515 $sql = $this->buildQuery();
2516
2517 // build array of result based on column headers. This method also allows
2518 // modifying column headers before using it to build result set i.e $rows.
2519 $rows = array();
2520 $this->buildRows($sql, $rows);
2521
2522 // format result set.
2523 $this->formatDisplay($rows);
2524
2525 // assign variables to templates
2526 $this->doTemplateAssignment($rows);
2527
2528 // do print / pdf / instance stuff if needed
2529 $this->endPostProcess($rows);
2530 }
2531
2532 function limit($rowCount = self::ROW_COUNT_LIMIT) {
2533 // lets do the pager if in html mode
2534 $this->_limit = NULL;
2535 if ($this->_outputMode == 'html' || $this->_outputMode == 'group') {
2536 $this->_select = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
2537
2538 $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
2539
2540 if (!$pageId && !empty($_POST)) {
2541 if (isset($_POST['PagerBottomButton']) && isset($_POST['crmPID_B'])) {
2542 $pageId = max((int)@$_POST['crmPID_B'], 1);
2543 }
2544 elseif (isset($_POST['PagerTopButton']) && isset($_POST['crmPID'])) {
2545 $pageId = max((int)@$_POST['crmPID'], 1);
2546 }
2547 unset($_POST['crmPID_B'], $_POST['crmPID']);
2548 }
2549
2550 $pageId = $pageId ? $pageId : 1;
2551 $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
2552 $offset = ($pageId - 1) * $rowCount;
2553
2554 $this->_limit = " LIMIT $offset, " . $rowCount;
2555 return array($offset, $rowCount);
2556 }
2557 }
2558
2559 function setPager($rowCount = self::ROW_COUNT_LIMIT) {
2560 if ($this->_limit && ($this->_limit != '')) {
2561 $sql = "SELECT FOUND_ROWS();";
2562 $this->_rowsFound = CRM_Core_DAO::singleValueQuery($sql);
2563 $params = array(
2564 'total' => $this->_rowsFound,
2565 'rowCount' => $rowCount,
2566 'status' => ts('Records') . ' %%StatusMessage%%',
2567 'buttonBottom' => 'PagerBottomButton',
2568 'buttonTop' => 'PagerTopButton',
2569 'pageID' => $this->get(CRM_Utils_Pager::PAGE_ID),
2570 );
2571
2572 $pager = new CRM_Utils_Pager($params);
2573 $this->assign_by_ref('pager', $pager);
2574 }
2575 }
2576
2577 function whereGroupClause($field, $value, $op) {
2578
2579 $smartGroupQuery = "";
2580
2581 $group = new CRM_Contact_DAO_Group();
2582 $group->is_active = 1;
2583 $group->find();
2584 $smartGroups = array();
2585 while ($group->fetch()) {
2586 if (in_array($group->id, $this->_params['gid_value']) && $group->saved_search_id) {
2587 $smartGroups[] = $group->id;
2588 }
2589 }
2590
2591 CRM_Contact_BAO_GroupContactCache::check($smartGroups);
2592
2593 $smartGroupQuery = '';
2594 if (!empty($smartGroups)) {
2595 $smartGroups = implode(',', $smartGroups);
2596 $smartGroupQuery = " UNION DISTINCT
2597 SELECT DISTINCT smartgroup_contact.contact_id
2598 FROM civicrm_group_contact_cache smartgroup_contact
2599 WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
2600 }
2601
29fc2b79 2602 $sqlOp = $this->getSQLOperator($op);
6a488035
TO		 2603 if (!is_array($value)) {
2604 $value = array($value);
2605 }
2606 $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
2607
2608 return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
2609 SELECT DISTINCT {$this->_aliases['civicrm_group']}.contact_id
2610 FROM civicrm_group_contact {$this->_aliases['civicrm_group']}
2611 WHERE {$clause} AND {$this->_aliases['civicrm_group']}.status = 'Added'
2612 {$smartGroupQuery} ) ";
2613 }
2614
2615 function whereTagClause($field, $value, $op) {
2616 // not using left join in query because if any contact
2617 // belongs to more than one tag, results duplicate
2618 // entries.
29fc2b79 2619 $sqlOp = $this->getSQLOperator($op);
6a488035
TO		 2620 if (!is_array($value)) {
2621 $value = array($value);
2622 }
2623 $clause = "{$field['dbAlias']} IN (" . implode(', ', $value) . ")";
2624
2625 return " {$this->_aliases['civicrm_contact']}.id {$sqlOp} (
2626 SELECT DISTINCT {$this->_aliases['civicrm_tag']}.entity_id
2627 FROM civicrm_entity_tag {$this->_aliases['civicrm_tag']}
2628 WHERE entity_table = 'civicrm_contact' AND {$clause} ) ";
2629 }
2630
2631 function buildACLClause($tableAlias = 'contact_a') {
2632 list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
2633 }
2634
2635 function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array()) {
2636 if (empty($this->_customGroupExtends)) {
2637 return;
2638 }
2639 if (!is_array($this->_customGroupExtends)) {
2640 $this->_customGroupExtends = array($this->_customGroupExtends);
2641 }
2642 $customGroupWhere = '';
2643 if (!empty($permCustomGroupIds)) {
2644 $customGroupWhere = "cg.id IN (".implode(',' , $permCustomGroupIds).") AND";
2645 }
2646 $sql = "
2647SELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,
2648 cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format
2649FROM civicrm_custom_group cg
2650INNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
2651WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
2652 {$customGroupWhere}
2653 cg.is_active = 1 AND
2654 cf.is_active = 1 AND
2655 cf.is_searchable = 1
2656ORDER BY cg.weight, cf.weight";
2657 $customDAO = CRM_Core_DAO::executeQuery($sql);
2658
2659 $curTable = NULL;
2660 while ($customDAO->fetch()) {
2661 if ($customDAO->table_name != $curTable) {
2662 $curTable = $customDAO->table_name;
2663 $curFields = $curFilters = array();
2664
2665 // dummy dao object
2666 $this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
2667 $this->_columns[$curTable]['extends'] = $customDAO->extends;
2668 $this->_columns[$curTable]['grouping'] = $customDAO->table_name;
2669 $this->_columns[$curTable]['group_title'] = $customDAO->title;
2670
2671 foreach (array(
2672 'fields', 'filters', 'group_bys') as $colKey) {
2673 if (!array_key_exists($colKey, $this->_columns[$curTable])) {
2674 $this->_columns[$curTable][$colKey] = array();
2675 }
2676 }
2677 }
2678 $fieldName = 'custom_' . $customDAO->cf_id;
2679
2680 if ($addFields) {
2681 // this makes aliasing work in favor
2682 $curFields[$fieldName] = array(
2683 'name' => $customDAO->column_name,
2684 'title' => $customDAO->label,
2685 'dataType' => $customDAO->data_type,
2686 'htmlType' => $customDAO->html_type,
2687 );
2688 }
2689 if ($this->_customGroupFilters) {
2690 // this makes aliasing work in favor
2691 $curFilters[$fieldName] = array(
2692 'name' => $customDAO->column_name,
2693 'title' => $customDAO->label,
2694 'dataType' => $customDAO->data_type,
2695 'htmlType' => $customDAO->html_type,
2696 );
2697 }
2698
2699 switch ($customDAO->data_type) {
2700 case 'Date':
2701 // filters
2702 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
2703 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
2704 // CRM-6946, show time part for datetime date fields
2705 if ($customDAO->time_format) {
2706 $curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
2707 }
2708 break;
2709
2710 case 'Boolean':
2711 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
2712 $curFilters[$fieldName]['options'] = array('' => ts('- select -'),
2713 1 => ts('Yes'),
2714 0 => ts('No'),
2715 );
2716 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
2717 break;
2718
2719 case 'Int':
2720 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
2721 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
2722 break;
2723
2724 case 'Money':
2725 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
2726 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
2727 break;
2728
2729 case 'Float':
2730 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
2731 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
2732 break;
2733
2734 case 'String':
2735 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2736
2737 if (!empty($customDAO->option_group_id)) {
2738 if (in_array($customDAO->html_type, array(
2739 'Multi-Select', 'AdvMulti-Select', 'CheckBox'))) {
2740 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
2741 }
2742 else {
2743 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
2744 }
2745 if ($this->_customGroupFilters) {
2746 $curFilters[$fieldName]['options'] = array();
2747 $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(1 => array($customDAO->option_group_id, 'Integer')));
2748 while ($ogDAO->fetch()) {
2749 $curFilters[$fieldName]['options'][$ogDAO->value] = $ogDAO->label;
2750 }
2751 }
2752 }
2753 break;
2754
2755 case 'StateProvince':
2756 if (in_array($customDAO->html_type, array(
2757 'Multi-Select State/Province'))) {
2758 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
2759 }
2760 else {
2761 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
2762 }
2763 $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::stateProvince();
2764 break;
2765
2766 case 'Country':
2767 if (in_array($customDAO->html_type, array(
2768 'Multi-Select Country'))) {
2769 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT_SEPARATOR;
2770 }
2771 else {
2772 $curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_MULTISELECT;
2773 }
2774 $curFilters[$fieldName]['options'] = CRM_Core_PseudoConstant::country();
2775 break;
2776
2777 case 'ContactReference':
2778 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2779 $curFilters[$fieldName]['name'] = 'display_name';
2780 $curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
2781
2782 $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2783 $curFields[$fieldName]['name'] = 'display_name';
2784 $curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
2785 break;
2786
2787 default:
2788 $curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2789 $curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
2790 }
2791
2792 if (!array_key_exists('type', $curFields[$fieldName])) {
fc161185 2793 $curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
6a488035
TO		 2794 }
2795
2796 if ($addFields) {
2797 $this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
2798 }
2799 if ($this->_customGroupFilters) {
2800 $this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
2801 }
2802 if ($this->_customGroupGroupBy) {
2803 $this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
2804 }
2805 }
2806 }
2807
2808 function customDataFrom() {
2809 if (empty($this->_customGroupExtends)) {
2810 return;
2811 }
2812 $mapper = CRM_Core_BAO_CustomQuery::$extendsMap;
2813
2814 foreach ($this->_columns as $table => $prop) {
2815 if (substr($table, 0, 13) == 'civicrm_value' || substr($table, 0, 12) == 'custom_value') {
2816 $extendsTable = $mapper[$prop['extends']];
2817
2818 // check field is in params
2819 if (!$this->isFieldSelected($prop)) {
2820 continue;
2821 }
2822
7a8b61ab 2823 $customJoin = is_array($this->_customGroupJoin) ? $this->_customGroupJoin[$table] : $this->_customGroupJoin;
6a488035 2824 $this->_from .= "
7a8b61ab 2825{$customJoin} {$table} {$this->_aliases[$table]} ON {$this->_aliases[$table]}.entity_id = {$this->_aliases[$extendsTable]}.id";
6a488035
TO		 2826 // handle for ContactReference
2827 if (array_key_exists('fields', $prop)) {
2828 foreach ($prop['fields'] as $fieldName => $field) {
2829 if (CRM_Utils_Array::value('dataType', $field) == 'ContactReference') {
2830 $columnName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', CRM_Core_BAO_CustomField::getKeyID($fieldName), 'column_name');
2831 $this->_from .= "
2832LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_aliases[$table]}.{$columnName} ";
2833 }
2834 }
2835 }
2836 }
2837 }
2838 }
2839
2840 function isFieldSelected($prop) {
2841 if (empty($prop)) {
2842 return FALSE;
2843 }
2844
2845 if (!empty($this->_params['fields'])) {
2846 foreach (array_keys($prop['fields']) as $fieldAlias) {
2847 $customFieldId = CRM_Core_BAO_CustomField::getKeyID($fieldAlias);
2848 if ($customFieldId) {
2849 if (array_key_exists($fieldAlias, $this->_params['fields'])) {
2850 return TRUE;
2851 }
2852
2853 //might be survey response field.
2854 if (CRM_Utils_Array::value('survey_response', $this->_params['fields']) &&
2855 CRM_Utils_Array::value('isSurveyResponseField', $prop['fields'][$fieldAlias])
2856 ) {
2857 return TRUE;
2858 }
2859 }
2860 }
2861 }
2862
2863 if (!empty($this->_params['group_bys']) && $this->_customGroupGroupBy) {
2864 foreach (array_keys($prop['group_bys']) as $fieldAlias) {
2865 if (array_key_exists($fieldAlias, $this->_params['group_bys']) && CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
2866 return TRUE;
2867 }
2868 }
2869 }
2870
2871 if (!empty($this->_params['order_bys'])) {
2872 foreach (array_keys($prop['fields']) as $fieldAlias) {
2873 foreach ($this->_params['order_bys'] as $orderBy) {
2874 if ($fieldAlias == $orderBy['column'] && CRM_Core_BAO_CustomField::getKeyID($fieldAlias)) {
2875 return TRUE;
2876 }
2877 }
2878 }
2879 }
2880
2881 if (!empty($prop['filters']) && $this->_customGroupFilters) {
2882 foreach ($prop['filters'] as $fieldAlias => $val) {
2883 foreach (array(
2884 'value', 'min', 'max', 'relative', 'from', 'to') as $attach) {
2885 if (isset($this->_params[$fieldAlias . '_' . $attach]) &&
dfe4b2f5 2886 (!empty($this->_params[$fieldAlias . '_' . $attach])
2887 || ($attach != 'relative' && $this->_params[$fieldAlias . '_' . $attach] == '0')
2888 )
2889 ){
6a488035
TO		 2890 return TRUE;
2891 }
2892 }
2893 if (CRM_Utils_Array::value($fieldAlias . '_op', $this->_params) &&
2894 in_array($this->_params[$fieldAlias . '_op'], array('nll', 'nnll'))
2895 ) {
2896 return TRUE;
2897 }
2898 }
2899 }
2900
2901 return FALSE;
2902 }
2903
2904 /**
2905 * Check for empty order_by configurations and remove them; also set
2906 * template to hide them.
2907 */
2908 function preProcessOrderBy(&$formValues) {
2909 // Object to show/hide form elements
ae555e90 2910 $_showHide = new CRM_Core_ShowHideBlocks('', '');
6a488035
TO		 2911
2912 $_showHide->addShow('optionField_1');
2913
2914 // Cycle through order_by options; skip any empty ones, and hide them as well
2915 $n = 1;
2916
2917 if (!empty($formValues['order_bys'])) {
2918 foreach ($formValues['order_bys'] as $order_by) {
2919 if ($order_by['column'] && $order_by['column'] != '-') {
2920 $_showHide->addShow('optionField_' . $n);
2921 $orderBys[$n] = $order_by;
2922 $n++;
2923 }
2924 }
2925 }
2926 for ($i = $n; $i <= 5; $i++) {
2927 if ($i > 1) {
2928 $_showHide->addHide('optionField_' . $i);
2929 }
2930 }
2931
2932 // overwrite order_by options with modified values
2933 if (!empty($orderBys)) {
2934 $formValues['order_bys'] = $orderBys;
2935 }
2936 else {
2937 $formValues['order_bys'] = array(1 => array('column' => '-'));
2938 }
2939
2940 // assign show/hide data to template
2941 $_showHide->addToTemplate();
2942 }
2943
2944 /**
2945 * Does table name have columns in SELECT clause?
2946 *
2947 * @param string $tableName Name of table (index of $this->_columns array)
2948 *
2949 * @return bool
2950 */
2951 function isTableSelected($tableName) {
2952 return in_array($tableName, $this->selectedTables());
2953 }
2954
2955 /**
2956 * Fetch array of DAO tables having columns included in SELECT or ORDER BY clause
2957 * (building the array if it's unset)
2958 *
2959 * @return Array $this->_selectedTables
2960 */
2961 function selectedTables() {
2962 if (!$this->_selectedTables) {
2963 $orderByColumns = array();
7a8b61ab 2964 if (array_key_exists('order_bys', $this->_params) && is_array($this->_params['order_bys'])) {
6a488035
TO		 2965 foreach ($this->_params['order_bys'] as $orderBy) {
2966 $orderByColumns[] = $orderBy['column'];
2967 }
2968 }
2969
2970 foreach ($this->_columns as $tableName => $table) {
2971 if (array_key_exists('fields', $table)) {
2972 foreach ($table['fields'] as $fieldName => $field) {
2973 if (CRM_Utils_Array::value('required', $field) ||
2974 CRM_Utils_Array::value($fieldName, $this->_params['fields'])
2975 ) {
2976 $this->_selectedTables[] = $tableName;
2977 break;
2978 }
2979 }
2980 }
2981 if (array_key_exists('order_bys', $table)) {
2982 foreach ($table['order_bys'] as $orderByName => $orderBy) {
2983 if (in_array($orderByName, $orderByColumns)) {
2984 $this->_selectedTables[] = $tableName;
2985 break;
2986 }
2987 }
2988 }
2989 if (array_key_exists('filters', $table)) {
2990 foreach ($table['filters'] as $filterName => $filter) {
2991 if (CRM_Utils_Array::value("{$filterName}_value", $this->_params) ||
2992 CRM_Utils_Array::value("{$filterName}_op", $this->_params) == 'nll' ||
2993 CRM_Utils_Array::value("{$filterName}_op", $this->_params) == 'nnll'
2994 ) {
2995 $this->_selectedTables[] = $tableName;
2996 break;
2997 }
2998 }
2999 }
3000 }
3001 }
3002 return $this->_selectedTables;
3003 }
3004
850e4640
E		 3005 /**
3006 * @deprecated - use getAddressColumns which is a more accurate description
3007 * and also accepts an array of options rather than a long list
3008 *
6a488035
TO		 3009 * function for adding address fields to construct function in reports
3010 * @param bool $groupBy Add GroupBy? Not appropriate for detail report
3011 * @param bool $orderBy Add GroupBy? Not appropriate for detail report
3012 * @return array address fields for construct clause
3013 */
3014 function addAddressFields($groupBy = TRUE, $orderBy = FALSE, $filters = TRUE, $defaults = array(
3015 'country_id' => TRUE)) {
3016 $addressFields = array(
3017 'civicrm_address' =>
3018 array(
3019 'dao' => 'CRM_Core_DAO_Address',
3020 'fields' =>
3021 array(
3022 'name' =>
3023 array('title' => ts('Address Name'),
3024 'default' => CRM_Utils_Array::value('name', $defaults, FALSE),
3025 ),
3026 'street_address' =>
3027 array('title' => ts('Street Address'),
3028 'default' => CRM_Utils_Array::value('street_address', $defaults, FALSE),
3029 ),
3030 'supplemental_address_1' =>
3031 array('title' => ts('Supplementary Address Field 1'),
3032 'default' => CRM_Utils_Array::value('supplemental_address_1', $defaults, FALSE),
3033 ),
3034 'supplemental_address_2' =>
3035 array('title' => ts('Supplementary Address Field 2'),
3036 'default' => CRM_Utils_Array::value('supplemental_address_2', $defaults, FALSE),
3037 ),
3038 'street_number' =>
3039 array(
3040 'name' => 'street_number',
3041 'title' => ts('Street Number'),
3042 'type' => 1,
3043 'default' => CRM_Utils_Array::value('street_number', $defaults, FALSE),
3044 ),
3045 'street_name' =>
3046 array(
3047 'name' => 'street_name',
3048 'title' => ts('Street Name'),
3049 'type' => 1,
3050 'default' => CRM_Utils_Array::value('street_name', $defaults, FALSE),
3051 ),
3052 'street_unit' =>
3053 array(
3054 'name' => 'street_unit',
3055 'title' => ts('Street Unit'),
3056 'type' => 1,
3057 'default' => CRM_Utils_Array::value('street_unit', $defaults, FALSE),
3058 ),
3059 'city' =>
3060 array('title' => ts('City'),
3061 'default' => CRM_Utils_Array::value('city', $defaults, FALSE),
3062 ),
3063 'postal_code' =>
3064 array('title' => ts('Postal Code'),
3065 'default' => CRM_Utils_Array::value('postal_code', $defaults, FALSE),
3066 ),
3067 'county_id' =>
3068 array('title' => ts('County'),
3069 'default' => CRM_Utils_Array::value('county_id', $defaults, FALSE),
3070 ),
3071 'state_province_id' =>
3072 array('title' => ts('State/Province'),
3073 'default' => CRM_Utils_Array::value('state_province_id', $defaults, FALSE),
3074 ),
3075 'country_id' =>
3076 array('title' => ts('Country'),
3077 'default' => CRM_Utils_Array::value('country_id', $defaults, FALSE),
3078 ),
3079 ),
3080 'grouping' => 'location-fields',
3081 ),
3082 );
3083
3084 if ($filters) {
3085 $addressFields['civicrm_address']['filters'] = array(
3086 'street_number' => array('title' => ts('Street Number'),
3087 'type' => 1,
3088 'name' => 'street_number',
3089 ),
3090 'street_name' => array('title' => ts('Street Name'),
3091 'name' => 'street_name',
3092 'operator' => 'like',
3093 ),
3094 'postal_code' => array('title' => ts('Postal Code'),
3095 'type' => 1,
3096 'name' => 'postal_code',
3097 ),
3098 'city' => array('title' => ts('City'),
3099 'operator' => 'like',
3100 'name' => 'city',
3101 ),
3102 'county_id' => array(
3103 'name' => 'county_id',
3104 'title' => ts('County'),
3105 'type' => CRM_Utils_Type::T_INT,
3106 'operatorType' =>
3107 CRM_Report_Form::OP_MULTISELECT,
3108 'options' =>
3109 CRM_Core_PseudoConstant::county(),
3110 ),
3111 'state_province_id' => array(
3112 'name' => 'state_province_id',
3113 'title' => ts('State/Province'),
3114 'type' => CRM_Utils_Type::T_INT,
3115 'operatorType' =>
3116 CRM_Report_Form::OP_MULTISELECT,
3117 'options' =>
3118 CRM_Core_PseudoConstant::stateProvince(),
3119 ),
3120 'country_id' => array(
3121 'name' => 'country_id',
3122 'title' => ts('Country'),
3123 'type' => CRM_Utils_Type::T_INT,
3124 'operatorType' =>
3125 CRM_Report_Form::OP_MULTISELECT,
3126 'options' =>
3127 CRM_Core_PseudoConstant::country(),
3128 ),
3129 );
3130 }
3131
3132 if ($orderBy) {
3133 $addressFields['civicrm_address']['order_bys'] = array('street_name' => array('title' => ts('Street Name')),
3134 'street_number' => array('title' => 'Odd / Even Street Number'),
3135 'street_address' => NULL,
3136 'city' => NULL,
3137 'postal_code' => NULL,
3138 );
3139 }
3140
3141 if ($groupBy) {
3142 $addressFields['civicrm_address']['group_bys'] = array(
3143 'street_address' => NULL,
3144 'city' => NULL,
3145 'postal_code' => NULL,
3146 'state_province_id' =>
3147 array('title' => ts('State/Province'),
3148 ),
3149 'country_id' =>
3150 array('title' => ts('Country'),
3151 ),
3152 'county_id' =>
3153 array('title' => ts('County'),
3154 ),
3155 );
3156 }
3157 return $addressFields;
3158 }
3159
3160 /*
3161 * Do AlterDisplay processing on Address Fields
3162 */
3163 function alterDisplayAddressFields(&$row, &$rows, &$rowNum, $baseUrl, $urltxt) {
3164 $criteriaQueryParams = CRM_Report_Utils_Report::getPreviewCriteriaQueryParams($this->_defaults, $this->_params);
3165 $entryFound = FALSE;
3166 // handle country
3167 if (array_key_exists('civicrm_address_country_id', $row)) {
3168 if ($value = $row['civicrm_address_country_id']) {
3169 $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
3170 $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
3171 "reset=1&force=1&{$criteriaQueryParams}&" .
3172 "country_id_op=in&country_id_value={$value}",
3173 $this->_absoluteUrl, $this->_id
3174 );
3175 $rows[$rowNum]['civicrm_address_country_id_link'] = $url;
3176 $rows[$rowNum]['civicrm_address_country_id_hover'] = ts("%1 for this country.",
3177 array(1 => $urltxt)
3178 );
3179 }
3180
3181 $entryFound = TRUE;
3182 }
3183 if (array_key_exists('civicrm_address_county_id', $row)) {
3184 if ($value = $row['civicrm_address_county_id']) {
3185 $rows[$rowNum]['civicrm_address_county_id'] = CRM_Core_PseudoConstant::county($value, FALSE);
3186 $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
3187 "reset=1&force=1&{$criteriaQueryParams}&" .
3188 "county_id_op=in&county_id_value={$value}",
3189 $this->_absoluteUrl, $this->_id
3190 );
3191 $rows[$rowNum]['civicrm_address_county_id_link'] = $url;
3192 $rows[$rowNum]['civicrm_address_county_id_hover'] = ts("%1 for this county.",
3193 array(1 => $urltxt)
3194 );
3195 }
3196 $entryFound = TRUE;
3197 }
3198 // handle state province
3199 if (array_key_exists('civicrm_address_state_province_id', $row)) {
3200 if ($value = $row['civicrm_address_state_province_id']) {
3201 $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
3202
3203 $url = CRM_Report_Utils_Report::getNextUrl($baseUrl,
3204 "reset=1&force=1&{$criteriaQueryParams}&state_province_id_op=in&state_province_id_value={$value}",
3205 $this->_absoluteUrl, $this->_id
3206 );
3207 $rows[$rowNum]['civicrm_address_state_province_id_link'] = $url;
3208 $rows[$rowNum]['civicrm_address_state_province_id_hover'] = ts("%1 for this state.",
3209 array(1 => $urltxt)
3210 );
3211 }
3212 $entryFound = TRUE;
3213 }
3214
3215 return $entryFound;
3216 }
3217
3218 /*
3219 * Adjusts dates passed in to YEAR() for fiscal year.
3220 */
3221 function fiscalYearOffset($fieldName) {
3222 $config = CRM_Core_Config::singleton();
3223 $fy = $config->fiscalYearStart;
3224 if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar' || ($fy['d'] == 1 && $fy['M'] == 1)) {
3225 return "YEAR( $fieldName )";
3226 }
3227 return "YEAR( $fieldName - INTERVAL " . ($fy['M'] - 1) . " MONTH" . ($fy['d'] > 1 ? (" - INTERVAL " . ($fy['d'] - 1) . " DAY") : '') . " )";
3228 }
3229
3230 /*
3231 * Add Address into From Table if required
3232 */
3233 function addAddressFromClause() {
3234 // include address field if address column is to be included
3235 if ((isset($this->_addressField) &&
3236 $this->_addressField
3237 ) ||
3238 $this->isTableSelected('civicrm_address')
3239 ) {
3240 $this->_from .= "
3241 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
3242 ON ({$this->_aliases['civicrm_contact']}.id =
3243 {$this->_aliases['civicrm_address']}.contact_id) AND
3244 {$this->_aliases['civicrm_address']}.is_primary = 1\n";
3245 }
3246 }
3247
850e4640
E		 3248 /**
3249 * Add Phone into From Table if required
3250 */
3251 function addPhoneFromClause() {
3252 // include address field if address column is to be included
3253 if ($this->isTableSelected('civicrm_phone')
3254 ) {
3255 $this->_from .= "
3256 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
3257 ON ({$this->_aliases['civicrm_contact']}.id =
3258 {$this->_aliases['civicrm_phone']}.contact_id) AND
3259 {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
3260 }
3261 }
3262
3263 /**
3264 * Get phone columns to add to array
3265 * @param array $options
3266 * - prefix Prefix to add to table (in case of more than one instance of the table)
3267 * - prefix_label Label to give columns from this phone table instance
3268 * @return array phone columns definition
3269 */
3270 function getPhoneColumns($options = array()){
3271 $defaultOptions = array(
3272 'prefix' => '',
3273 'prefix_label' => '',
3274 );
3275
3276 $options = array_merge($defaultOptions,$options);
3277
3278 $fields = array(
3279 $options['prefix'] . 'civicrm_phone' => array(
3280 'dao' => 'CRM_Core_DAO_Phone',
3281 'fields' => array(
3282 $options['prefix'] . 'phone' => array(
3283 'title' => ts($options['prefix_label'] . 'Phone'),
3284 'name' => 'phone'
3285 ),
3286 ),
3287 ),
3288 );
3289 return $fields;
3290 }
3291
3292 /**
3293 * Get address columns to add to array
3294 * @param array $options
3295 * - prefix Prefix to add to table (in case of more than one instance of the table)
3296 * - prefix_label Label to give columns from this address table instance
3297 * @return array address columns definition
3298 */
3299 function getAddressColumns($options = array()){
3300 $defaultOptions = array(
3301 'prefix' => '',
3302 'prefix_label' => '',
3303 'group_by' => TRUE,
3304 'order_by' => TRUE,
3305 'filters' => TRUE,
3306 'defaults' => array(
3307 ),
3308 );
3309 $options = array_merge($defaultOptions,$options);
3310 return $this->addAddressFields(
3311 $options['group_by'],
3312 $options['order_by'],
3313 $options['filters'],
3314 $options['defaults']
3315 );
3316
3317 }
3318
6a488035
TO		 3319 function add2group($groupID) {
3320 if (is_numeric($groupID) && isset($this->_aliases['civicrm_contact'])) {
3321 $select = "SELECT DISTINCT {$this->_aliases['civicrm_contact']}.id AS addtogroup_contact_id, ";
3322 $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', $select, $this->_select);
3323
3324 $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
3325 $dao = CRM_Core_DAO::executeQuery($sql);
3326
3327 $contact_ids = array();
3328 // Add resulting contacts to group
3329 while ($dao->fetch()) {
3330 if ($dao->addtogroup_contact_id) {
3331 $contact_ids[$dao->addtogroup_contact_id] = $dao->addtogroup_contact_id;
3332 }
3333 }
3334
3335 if ( !empty($contact_ids) ) {
3336 CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
3337 CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
3338 }
3339 else {
3340 CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
3341 }
3342 }
3343 }
3344}
3345