3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
33 class CRM_Report_Form_Grant_Statistics
extends CRM_Report_Form
{
35 protected $_customGroupExtends = ['Grant'];
37 protected $_add2groupSupported = FALSE;
42 public function __construct() {
45 'dao' => 'CRM_Grant_DAO_Grant',
47 'summary_statistics' => [
49 'title' => ts('Summary Statistics'),
53 'name' => 'grant_type_id',
54 'title' => ts('By Grant Type'),
64 'grant_report_received' => [
74 'application_received_date' => [
75 'name' => 'application_received_date',
76 'title' => ts('Application Received'),
77 'operatorType' => CRM_Report_Form
::OP_DATE
,
78 'type' => CRM_Utils_Type
::T_DATE
,
81 'name' => 'decision_date',
82 'title' => ts('Grant Decision'),
83 'operatorType' => CRM_Report_Form
::OP_DATE
,
84 'type' => CRM_Utils_Type
::T_DATE
,
86 'money_transfer_date' => [
87 'name' => 'money_transfer_date',
88 'title' => ts('Money Transferred'),
89 'operatorType' => CRM_Report_Form
::OP_DATE
,
90 'type' => CRM_Utils_Type
::T_DATE
,
93 'name' => 'grant_due_date',
94 'title' => ts('Grant Report Due'),
95 'operatorType' => CRM_Report_Form
::OP_DATE
,
96 'type' => CRM_Utils_Type
::T_DATE
,
99 'name' => 'grant_type_id',
100 'title' => ts('Grant Type'),
101 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
102 'options' => CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'grant_type_id'),
105 'name' => 'status_id',
106 'title' => ts('Grant Status'),
107 'type' => CRM_Utils_Type
::T_INT
,
108 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
109 'options' => CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'status_id'),
111 'amount_requested' => [
112 'name' => 'amount_requested',
113 'title' => ts('Amount Requested'),
114 'type' => CRM_Utils_Type
::T_MONEY
,
116 'amount_granted' => [
117 'name' => 'amount_granted',
118 'title' => ts('Amount Granted'),
120 'grant_report_received' => [
121 'name' => 'grant_report_received',
122 'title' => ts('Report Received'),
123 'operatorType' => CRM_Report_Form
::OP_SELECT
,
125 '' => ts('- select -'),
132 'civicrm_contact' => [
133 'dao' => 'CRM_Contact_DAO_Contact',
137 'no_display' => TRUE,
140 'name' => 'gender_id',
141 'title' => ts('By Gender'),
144 'name' => 'contact_type',
145 'title' => ts('By Contact Type'),
150 'name' => 'gender_id',
151 'title' => ts('Gender'),
152 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
153 'options' => CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id'),
156 'name' => 'contact_type',
157 'title' => ts('Contact Type'),
158 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
159 'options' => CRM_Contact_BAO_ContactType
::basicTypePairs(),
162 'grouping' => 'contact-fields',
164 'civicrm_worldregion' => [
165 'dao' => 'CRM_Core_DAO_Worldregion',
168 'no_display' => TRUE,
172 'title' => ts('By World Region'),
178 'title' => ts('World Region'),
179 'type' => CRM_Utils_Type
::T_INT
,
180 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
181 'options' => CRM_Core_PseudoConstant
::worldRegion(),
185 'civicrm_address' => [
186 'dao' => 'CRM_Core_DAO_Address',
189 'name' => 'country_id',
190 'title' => ts('By Country'),
195 'title' => ts('Country'),
196 'type' => CRM_Utils_Type
::T_INT
,
197 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
198 'options' => CRM_Core_PseudoConstant
::country(),
203 parent
::__construct();
206 public function select() {
209 $this->_columnHeaders
= [];
210 foreach ($this->_columns
as $tableName => $table) {
211 if (array_key_exists('fields', $table)) {
212 foreach ($table['fields'] as $fieldName => $field) {
213 if (!empty($field['required']) ||
214 !empty($this->_params
['fields'][$fieldName])
217 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
219 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
220 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
225 $this->_selectClauses
= $select;
227 $this->_select
= "SELECT " . implode(', ', $select) . " ";
230 public function from() {
232 FROM civicrm_grant {$this->_aliases['civicrm_grant']}
233 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
234 ON ({$this->_aliases['civicrm_grant']}.contact_id = {$this->_aliases['civicrm_contact']}.id ) ";
236 $this->joinAddressFromContact();
237 $this->joinCountryFromAddress();
238 if ($this->isTableSelected('civicrm_worldregion')) {
240 LEFT JOIN civicrm_worldregion {$this->_aliases['civicrm_worldregion']}
241 ON {$this->_aliases['civicrm_country']}.region_id =
242 {$this->_aliases['civicrm_worldregion']}.id";
246 public function where() {
248 WHERE {$this->_aliases['civicrm_grant']}.amount_total IS NOT NULL
249 AND {$this->_aliases['civicrm_grant']}.amount_total > 0";
250 $this->_where
= $whereClause;
252 foreach ($this->_columns
as $tableName => $table) {
253 if (array_key_exists('filters', $table)) {
254 foreach ($table['filters'] as $fieldName => $field) {
257 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
258 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
259 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
260 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
262 if ($relative ||
$from ||
$to) {
263 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
267 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
268 if (($fieldName == 'grant_report_received') &&
269 (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ===
273 $this->_params
["{$fieldName}_value"] = NULL;
276 $clause = $this->whereClause($field,
278 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
279 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
280 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
284 if (!empty($clause)) {
285 $clauses[] = $clause;
290 if (!empty($clauses)) {
291 $this->_where
= "WHERE " . implode(' AND ', $clauses);
292 $this->_whereClause
= $whereClause . " AND " . implode(' AND ', $clauses);
296 public function groupBy() {
297 $this->_groupBy
= '';
299 if (!empty($this->_params
['fields']) &&
300 is_array($this->_params
['fields']) &&
301 !empty($this->_params
['fields'])
303 foreach ($this->_columns
as $tableName => $table) {
304 if (array_key_exists('fields', $table)) {
305 foreach ($table['fields'] as $fieldName => $field) {
306 if (!empty($this->_params
['fields'][$fieldName])) {
307 $groupBy[] = $field['dbAlias'];
313 if (!empty($groupBy)) {
314 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, $groupBy);
318 public function postProcess() {
319 // get ready with post process params
320 $this->beginPostProcess();
322 // build query, do not apply limit
323 $sql = $this->buildQuery(FALSE);
325 // build array of result based on column headers. This method also allows
326 // modifying column headers before using it to build result set i.e $rows.
327 $this->buildRows($sql, $rows);
329 // format result set.
330 $this->formatDisplay($rows);
332 // assign variables to templates
333 $this->doTemplateAssignment($rows);
335 // do print / pdf / instance stuff if needed
336 $this->endPostProcess($rows);
340 * Alter display of rows.
342 * Iterate through the rows retrieved via SQL and make changes for display purposes,
343 * such as rendering contacts as links.
346 * Rows generated by SQL, with an array for each row.
348 public function alterDisplay(&$rows) {
349 $totalStatistics = $grantStatistics = [];
350 $totalStatistics = parent
::statistics($rows);
351 $awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
352 $grantStatistics = [];
354 $grantTypes = CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'grant_type_id');
355 $countries = CRM_Core_PseudoConstant
::country();
356 $gender = CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id');
358 $grantAmountTotal = "
359 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
360 SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
363 if (!empty($this->_whereClause
)) {
364 $grantAmountTotal .= " {$this->_whereClause}";
367 $result = CRM_Core_DAO
::executeQuery($grantAmountTotal);
368 while ($result->fetch()) {
369 $grantsReceived = $result->count
;
370 $totalAmount = $result->totalAmount
;
373 if (!$grantsReceived) {
377 $grantAmountAwarded = "
378 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
379 SUM({$this->_aliases['civicrm_grant']}.amount_granted) as grantedAmount,
380 SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
383 if (!empty($this->_where
)) {
384 $grantAmountAwarded .= " {$this->_where}";
386 $values = CRM_Core_DAO
::executeQuery($grantAmountAwarded);
387 while ($values->fetch()) {
388 $awardedGrants = $values->count
;
389 $awardedGrantsAmount = $values->totalAmount
;
390 $amountGranted = $values->grantedAmount
;
393 foreach ($rows as $key => $values) {
394 if (!empty($values['civicrm_grant_grant_report_received'])) {
395 $grantReportsReceived++
;
398 if (!empty($values['civicrm_grant_grant_type_id'])) {
399 $grantType = CRM_Utils_Array
::value($values['civicrm_grant_grant_type_id'], $grantTypes);
400 $grantStatistics['civicrm_grant_grant_type_id']['title'] = ts('By Grant Type');
401 self
::getStatistics($grantStatistics['civicrm_grant_grant_type_id'], $grantType, $values,
402 $awardedGrants, $awardedGrantsAmount
406 if (array_key_exists('civicrm_worldregion_name', $values)) {
407 $region = CRM_Utils_Array
::value('civicrm_worldregion_name', $values);
408 $region = ($region) ?
$region : 'Unassigned';
409 $grantStatistics['civicrm_worldregion_name']['title'] = ts('By Region');
410 self
::getStatistics($grantStatistics['civicrm_worldregion_name'], $region, $values,
411 $awardedGrants, $awardedGrantsAmount
415 if (array_key_exists('civicrm_address_country_id', $values)) {
416 $country = CRM_Utils_Array
::value($values['civicrm_address_country_id'], $countries);
417 $country = ($country) ?
$country : 'Unassigned';
418 $grantStatistics['civicrm_address_country_id']['title'] = ts('By Country');
419 self
::getStatistics($grantStatistics['civicrm_address_country_id'], $country, $values,
420 $awardedGrants, $awardedGrantsAmount
424 if ($type = CRM_Utils_Array
::value('civicrm_contact_contact_type', $values)) {
425 $grantStatistics['civicrm_contact_contact_type']['title'] = ts('By Contact Type');
426 $title = "Total Number of {$type}(s)";
427 self
::getStatistics($grantStatistics['civicrm_contact_contact_type'], $title, $values,
428 $awardedGrants, $awardedGrantsAmount
432 if (array_key_exists('civicrm_contact_gender_id', $values)) {
433 $genderLabel = CRM_Utils_Array
::value($values['civicrm_contact_gender_id'], $gender);
434 $genderLabel = ($genderLabel) ?
$genderLabel : 'Unassigned';
435 $grantStatistics['civicrm_contact_gender_id']['title'] = ts('By Gender');
436 self
::getStatistics($grantStatistics['civicrm_contact_gender_id'], $genderLabel, $values,
437 $awardedGrants, $awardedGrantsAmount
441 foreach ($values as $customField => $customValue) {
442 if (strstr($customField, 'civicrm_value_')) {
443 $customFieldTitle = CRM_Utils_Array
::value('title', $this->_columnHeaders
[$customField]);
444 $customGroupTitle = explode('_custom', strstr($customField, 'civicrm_value_'));
445 $customGroupTitle = $this->_columns
[$customGroupTitle[0]]['group_title'];
446 $grantStatistics[$customGroupTitle]['title'] = ts('By %1', [1 => $customGroupTitle]);
448 $customData = ($customValue) ?
FALSE : TRUE;
449 self
::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values,
450 $awardedGrants, $awardedGrantsAmount, $customData
456 $totalStatistics['total_statistics'] = [
457 'grants_received' => [
458 'title' => ts('Grant Requests Received'),
459 'count' => $grantsReceived,
460 'amount' => $totalAmount,
462 'grants_awarded' => [
463 'title' => ts('Grants Awarded'),
464 'count' => $awardedGrants,
465 'amount' => $amountGranted,
467 'grants_report_received' => [
468 'title' => ts('Grant Reports Received'),
469 'count' => $grantReportsReceived,
473 $this->assign('totalStatistics', $totalStatistics);
474 $this->assign('grantStatistics', $grantStatistics);
476 if ($this->_outputMode
== 'csv' ||
477 $this->_outputMode
== 'pdf'
480 $this->_columnHeaders
= [
481 'civicrm_grant_total_grants' => ['title' => ts('Summary')],
482 'civicrm_grant_count' => ['title' => ts('Count')],
483 'civicrm_grant_amount' => ['title' => ts('Amount')],
485 foreach ($totalStatistics['total_statistics'] as $title => $value) {
487 'civicrm_grant_total_grants' => $value['title'],
488 'civicrm_grant_count' => $value['count'],
489 'civicrm_grant_amount' => $value['amount'],
493 if (!empty($grantStatistics)) {
494 foreach ($grantStatistics as $key => $value) {
496 'civicrm_grant_total_grants' => $value['title'],
497 'civicrm_grant_count' => ts('Number of Grants') . ' (%)',
498 'civicrm_grant_amount' => ts('Total Amount') . ' (%)',
501 foreach ($value['value'] as $field => $values) {
502 foreach ($values['currency'] as $currency => $amount) {
503 $totalAmount[$currency] = $currency . $amount['value'] .
504 "({$values['percentage']}%)";
506 $totalAmt = implode(', ', $totalAmount);
507 $count = (boolean
) CRM_Utils_Array
::value('count', $values, 0) ?
$values['count'] . " ({$values['percentage']}%)" : '';
509 'civicrm_grant_total_grants' => $field,
510 'civicrm_grant_count' => $count,
511 'civicrm_grant_amount' => $totalAmt,
521 * @param $grantStatistics
524 * @param $awardedGrants
525 * @param $awardedGrantsAmount
526 * @param bool $customData
528 public static function getStatistics(
529 &$grantStatistics, $fieldValue, $values,
530 $awardedGrants, $awardedGrantsAmount, $customData = FALSE
532 if (!$awardedGrantsAmount) {
536 $currencies = CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'currency', ['labelColumn' => 'symbol']);
537 $currency = $currencies[$values['civicrm_grant_currency']];
540 if (!isset($grantStatistics['value'][$fieldValue]['currency'][$currency])
542 !isset($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'])
544 $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] = 0;
546 $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] +
= $values['civicrm_grant_amount_total'];
547 $grantStatistics['value'][$fieldValue]['currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] /
548 $awardedGrantsAmount) * 100);
549 if (!isset($grantStatistics['value'][$fieldValue]['count'])) {
550 $grantStatistics['value'][$fieldValue]['count'] = 0;
552 $grantStatistics['value'][$fieldValue]['count']++
;
553 $grantStatistics['value'][$fieldValue]['percentage'] = round(($grantStatistics['value'][$fieldValue]['count'] /
554 $awardedGrants) * 100);
557 if (!isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency])
559 !isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'])
561 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] = 0;
563 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] +
= $values['civicrm_grant_amount_total'];
564 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] /
565 $awardedGrantsAmount) * 100);
566 $grantStatistics['value'][$fieldValue]['unassigned_count']++
;
567 $grantStatistics['value'][$fieldValue]['unassigned_percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_count'] /
568 $awardedGrants) * 100);