projects / civicrm-core.git / blob
? search:


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