3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Report_Form_Grant_Statistics
extends CRM_Report_Form
{
19 protected $_customGroupExtends = ['Grant'];
21 protected $_add2groupSupported = FALSE;
26 public function __construct() {
29 'dao' => 'CRM_Grant_DAO_Grant',
31 'summary_statistics' => [
33 'title' => ts('Summary Statistics'),
37 'name' => 'grant_type_id',
38 'title' => ts('By Grant Type'),
48 'grant_report_received' => [
58 'application_received_date' => [
59 'name' => 'application_received_date',
60 'title' => ts('Application Received'),
61 'operatorType' => CRM_Report_Form
::OP_DATE
,
62 'type' => CRM_Utils_Type
::T_DATE
,
65 'name' => 'decision_date',
66 'title' => ts('Grant Decision'),
67 'operatorType' => CRM_Report_Form
::OP_DATE
,
68 'type' => CRM_Utils_Type
::T_DATE
,
70 'money_transfer_date' => [
71 'name' => 'money_transfer_date',
72 'title' => ts('Money Transferred'),
73 'operatorType' => CRM_Report_Form
::OP_DATE
,
74 'type' => CRM_Utils_Type
::T_DATE
,
77 'name' => 'grant_due_date',
78 'title' => ts('Grant Report Due'),
79 'operatorType' => CRM_Report_Form
::OP_DATE
,
80 'type' => CRM_Utils_Type
::T_DATE
,
83 'name' => 'grant_type_id',
84 'title' => ts('Grant Type'),
85 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
86 'options' => CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'grant_type_id'),
89 'name' => 'status_id',
90 'title' => ts('Grant Status'),
91 'type' => CRM_Utils_Type
::T_INT
,
92 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
93 'options' => CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'status_id'),
95 'amount_requested' => [
96 'name' => 'amount_requested',
97 'title' => ts('Amount Requested'),
98 'type' => CRM_Utils_Type
::T_MONEY
,
100 'amount_granted' => [
101 'name' => 'amount_granted',
102 'title' => ts('Amount Granted'),
104 'grant_report_received' => [
105 'name' => 'grant_report_received',
106 'title' => ts('Report Received'),
107 'operatorType' => CRM_Report_Form
::OP_SELECT
,
109 '' => ts('- select -'),
116 'civicrm_contact' => [
117 'dao' => 'CRM_Contact_DAO_Contact',
121 'no_display' => TRUE,
124 'name' => 'gender_id',
125 'title' => ts('By Gender'),
128 'name' => 'contact_type',
129 'title' => ts('By Contact Type'),
134 'name' => 'gender_id',
135 'title' => ts('Gender'),
136 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
137 'options' => CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id'),
140 'name' => 'contact_type',
141 'title' => ts('Contact Type'),
142 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
143 'options' => CRM_Contact_BAO_ContactType
::basicTypePairs(),
146 'grouping' => 'contact-fields',
148 'civicrm_worldregion' => [
149 'dao' => 'CRM_Core_DAO_Worldregion',
152 'no_display' => TRUE,
156 'title' => ts('By World Region'),
162 'title' => ts('World Region'),
163 'type' => CRM_Utils_Type
::T_INT
,
164 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
165 'options' => CRM_Core_PseudoConstant
::worldRegion(),
169 'civicrm_address' => [
170 'dao' => 'CRM_Core_DAO_Address',
173 'name' => 'country_id',
174 'title' => ts('By Country'),
179 'title' => ts('Country'),
180 'type' => CRM_Utils_Type
::T_INT
,
181 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
182 'options' => CRM_Core_PseudoConstant
::country(),
187 parent
::__construct();
190 public function select() {
193 $this->_columnHeaders
= [];
194 foreach ($this->_columns
as $tableName => $table) {
195 if (array_key_exists('fields', $table)) {
196 foreach ($table['fields'] as $fieldName => $field) {
197 if (!empty($field['required']) ||
198 !empty($this->_params
['fields'][$fieldName])
201 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
203 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'] ??
NULL;
204 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = $field['type'] ??
NULL;
209 $this->_selectClauses
= $select;
211 $this->_select
= "SELECT " . implode(', ', $select) . " ";
214 public function from() {
216 FROM civicrm_grant {$this->_aliases['civicrm_grant']}
217 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
218 ON ({$this->_aliases['civicrm_grant']}.contact_id = {$this->_aliases['civicrm_contact']}.id ) ";
220 $this->joinAddressFromContact();
221 $this->joinCountryFromAddress();
222 if ($this->isTableSelected('civicrm_worldregion')) {
224 LEFT JOIN civicrm_worldregion {$this->_aliases['civicrm_worldregion']}
225 ON {$this->_aliases['civicrm_country']}.region_id =
226 {$this->_aliases['civicrm_worldregion']}.id";
230 public function where() {
232 WHERE {$this->_aliases['civicrm_grant']}.amount_total IS NOT NULL
233 AND {$this->_aliases['civicrm_grant']}.amount_total > 0";
234 $this->_where
= $whereClause;
236 foreach ($this->_columns
as $tableName => $table) {
237 if (array_key_exists('filters', $table)) {
238 foreach ($table['filters'] as $fieldName => $field) {
241 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
242 $relative = $this->_params
["{$fieldName}_relative"] ??
NULL;
243 $from = $this->_params
["{$fieldName}_from"] ??
NULL;
244 $to = $this->_params
["{$fieldName}_to"] ??
NULL;
246 if ($relative ||
$from ||
$to) {
247 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
251 $op = $this->_params
["{$fieldName}_op"] ??
NULL;
252 if (($fieldName == 'grant_report_received') &&
253 (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ===
257 $this->_params
["{$fieldName}_value"] = NULL;
260 $clause = $this->whereClause($field,
262 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
263 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
264 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
268 if (!empty($clause)) {
269 $clauses[] = $clause;
274 if (!empty($clauses)) {
275 $this->_where
= "WHERE " . implode(' AND ', $clauses);
276 $this->_whereClause
= $whereClause . " AND " . implode(' AND ', $clauses);
280 public function groupBy() {
281 $this->_groupBy
= '';
283 if (!empty($this->_params
['fields']) &&
284 is_array($this->_params
['fields']) &&
285 !empty($this->_params
['fields'])
287 foreach ($this->_columns
as $tableName => $table) {
288 if (array_key_exists('fields', $table)) {
289 foreach ($table['fields'] as $fieldName => $field) {
290 if (!empty($this->_params
['fields'][$fieldName])) {
291 $groupBy[] = $field['dbAlias'];
297 if (!empty($groupBy)) {
298 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, $groupBy);
302 public function postProcess() {
303 // get ready with post process params
304 $this->beginPostProcess();
306 // build query, do not apply limit
307 $sql = $this->buildQuery(FALSE);
309 // build array of result based on column headers. This method also allows
310 // modifying column headers before using it to build result set i.e $rows.
311 $this->buildRows($sql, $rows);
313 // format result set.
314 $this->formatDisplay($rows);
316 // assign variables to templates
317 $this->doTemplateAssignment($rows);
319 // do print / pdf / instance stuff if needed
320 $this->endPostProcess($rows);
324 * Alter display of rows.
326 * Iterate through the rows retrieved via SQL and make changes for display purposes,
327 * such as rendering contacts as links.
330 * Rows generated by SQL, with an array for each row.
332 public function alterDisplay(&$rows) {
333 $totalStatistics = $grantStatistics = [];
334 $totalStatistics = parent
::statistics($rows);
335 $awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
336 $grantStatistics = [];
338 $grantTypes = CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'grant_type_id');
339 $countries = CRM_Core_PseudoConstant
::country();
340 $gender = CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id');
342 $grantAmountTotal = "
343 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
344 SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
347 if (!empty($this->_whereClause
)) {
348 $grantAmountTotal .= " {$this->_whereClause}";
351 $result = CRM_Core_DAO
::executeQuery($grantAmountTotal);
352 while ($result->fetch()) {
353 $grantsReceived = $result->count
;
354 $totalAmount = $result->totalAmount
;
357 if (!$grantsReceived) {
361 $grantAmountAwarded = "
362 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
363 SUM({$this->_aliases['civicrm_grant']}.amount_granted) as grantedAmount,
364 SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
367 if (!empty($this->_where
)) {
368 $grantAmountAwarded .= " {$this->_where}";
370 $values = CRM_Core_DAO
::executeQuery($grantAmountAwarded);
371 while ($values->fetch()) {
372 $awardedGrants = $values->count
;
373 $awardedGrantsAmount = $values->totalAmount
;
374 $amountGranted = $values->grantedAmount
;
377 foreach ($rows as $key => $values) {
378 if (!empty($values['civicrm_grant_grant_report_received'])) {
379 $grantReportsReceived++
;
382 if (!empty($values['civicrm_grant_grant_type_id'])) {
383 $grantType = $grantTypes[$values['civicrm_grant_grant_type_id']] ??
NULL;
384 $grantStatistics['civicrm_grant_grant_type_id']['title'] = ts('By Grant Type');
385 self
::getStatistics($grantStatistics['civicrm_grant_grant_type_id'], $grantType, $values,
386 $awardedGrants, $awardedGrantsAmount
390 if (array_key_exists('civicrm_worldregion_name', $values)) {
391 $region = $values['civicrm_worldregion_name'] ??
NULL;
392 $region = ($region) ?
$region : 'Unassigned';
393 $grantStatistics['civicrm_worldregion_name']['title'] = ts('By Region');
394 self
::getStatistics($grantStatistics['civicrm_worldregion_name'], $region, $values,
395 $awardedGrants, $awardedGrantsAmount
399 if (array_key_exists('civicrm_address_country_id', $values)) {
400 $country = $countries[$values['civicrm_address_country_id']] ??
NULL;
401 $country = ($country) ?
$country : 'Unassigned';
402 $grantStatistics['civicrm_address_country_id']['title'] = ts('By Country');
403 self
::getStatistics($grantStatistics['civicrm_address_country_id'], $country, $values,
404 $awardedGrants, $awardedGrantsAmount
408 if ($type = CRM_Utils_Array
::value('civicrm_contact_contact_type', $values)) {
409 $grantStatistics['civicrm_contact_contact_type']['title'] = ts('By Contact Type');
410 $title = "Total Number of {$type}(s)";
411 self
::getStatistics($grantStatistics['civicrm_contact_contact_type'], $title, $values,
412 $awardedGrants, $awardedGrantsAmount
416 if (array_key_exists('civicrm_contact_gender_id', $values)) {
417 $genderLabel = $gender[$values['civicrm_contact_gender_id']] ??
NULL;
418 $genderLabel = ($genderLabel) ?
$genderLabel : 'Unassigned';
419 $grantStatistics['civicrm_contact_gender_id']['title'] = ts('By Gender');
420 self
::getStatistics($grantStatistics['civicrm_contact_gender_id'], $genderLabel, $values,
421 $awardedGrants, $awardedGrantsAmount
425 foreach ($values as $customField => $customValue) {
426 if (strstr($customField, 'civicrm_value_')) {
427 $customFieldTitle = $this->_columnHeaders
[$customField]['title'] ??
NULL;
428 $customGroupTitle = explode('_custom', strstr($customField, 'civicrm_value_'));
429 $customGroupTitle = $this->_columns
[$customGroupTitle[0]]['group_title'];
430 $grantStatistics[$customGroupTitle]['title'] = ts('By %1', [1 => $customGroupTitle]);
432 self
::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values,
433 $awardedGrants, $awardedGrantsAmount, !$customValue
439 $totalStatistics['total_statistics'] = [
440 'grants_received' => [
441 'title' => ts('Grant Requests Received'),
442 'count' => $grantsReceived,
443 'amount' => $totalAmount,
445 'grants_awarded' => [
446 'title' => ts('Grants Awarded'),
447 'count' => $awardedGrants,
448 'amount' => $amountGranted,
450 'grants_report_received' => [
451 'title' => ts('Grant Reports Received'),
452 'count' => $grantReportsReceived,
456 $this->assign('totalStatistics', $totalStatistics);
457 $this->assign('grantStatistics', $grantStatistics);
459 if ($this->_outputMode
== 'csv' ||
460 $this->_outputMode
== 'pdf'
463 $this->_columnHeaders
= [
464 'civicrm_grant_total_grants' => ['title' => ts('Summary')],
465 'civicrm_grant_count' => ['title' => ts('Count')],
466 'civicrm_grant_amount' => ['title' => ts('Amount')],
468 foreach ($totalStatistics['total_statistics'] as $title => $value) {
470 'civicrm_grant_total_grants' => $value['title'],
471 'civicrm_grant_count' => $value['count'],
472 'civicrm_grant_amount' => $value['amount'],
476 if (!empty($grantStatistics)) {
477 foreach ($grantStatistics as $key => $value) {
479 'civicrm_grant_total_grants' => $value['title'],
480 'civicrm_grant_count' => ts('Number of Grants') . ' (%)',
481 'civicrm_grant_amount' => ts('Total Amount') . ' (%)',
484 foreach ($value['value'] as $field => $values) {
485 foreach ($values['currency'] as $currency => $amount) {
486 $totalAmount[$currency] = $currency . $amount['value'] .
487 "({$values['percentage']}%)";
489 $totalAmt = implode(', ', $totalAmount);
490 $count = (boolean
) CRM_Utils_Array
::value('count', $values, 0) ?
$values['count'] . " ({$values['percentage']}%)" : '';
492 'civicrm_grant_total_grants' => $field,
493 'civicrm_grant_count' => $count,
494 'civicrm_grant_amount' => $totalAmt,
504 * @param $grantStatistics
507 * @param $awardedGrants
508 * @param $awardedGrantsAmount
509 * @param bool $customData
511 public static function getStatistics(
512 &$grantStatistics, $fieldValue, $values,
513 $awardedGrants, $awardedGrantsAmount, $customData = FALSE
515 if (!$awardedGrantsAmount) {
519 $currencies = CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'currency', ['labelColumn' => 'symbol']);
520 $currency = $currencies[$values['civicrm_grant_currency']];
523 if (!isset($grantStatistics['value'][$fieldValue]['currency'][$currency])
525 !isset($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'])
527 $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] = 0;
529 $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] +
= $values['civicrm_grant_amount_total'];
530 $grantStatistics['value'][$fieldValue]['currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] /
531 $awardedGrantsAmount) * 100);
532 if (!isset($grantStatistics['value'][$fieldValue]['count'])) {
533 $grantStatistics['value'][$fieldValue]['count'] = 0;
535 $grantStatistics['value'][$fieldValue]['count']++
;
536 $grantStatistics['value'][$fieldValue]['percentage'] = round(($grantStatistics['value'][$fieldValue]['count'] /
537 $awardedGrants) * 100);
540 if (!isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency])
542 !isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'])
544 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] = 0;
546 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] +
= $values['civicrm_grant_amount_total'];
547 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] /
548 $awardedGrantsAmount) * 100);
549 $grantStatistics['value'][$fieldValue]['unassigned_count']++
;
550 $grantStatistics['value'][$fieldValue]['unassigned_percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_count'] /
551 $awardedGrants) * 100);