CRM/Report/Form/Grant/Statistics.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Grant_Statistics extends CRM_Report_Form {
  18
  19   protected $_customGroupExtends = ['Grant'];
  20
  21   protected $_add2groupSupported = FALSE;
  22
  23   /**
  24    * Class constructor.
  25    */
  26   public function __construct() {
  27     $this->_columns = [
  28       'civicrm_grant' => [
  29         'dao' => 'CRM_Grant_DAO_Grant',
  30         'fields' => [
  31           'summary_statistics' => [
  32             'name' => 'id',
  33             'title' => ts('Summary Statistics'),
  34             'required' => TRUE,
  35           ],
  36           'grant_type_id' => [
  37             'name' => 'grant_type_id',
  38             'title' => ts('By Grant Type'),
  39           ],
  40           'status_id' => [
  41             'no_display' => TRUE,
  42             'required' => TRUE,
  43           ],
  44           'amount_total' => [
  45             'no_display' => TRUE,
  46             'required' => TRUE,
  47           ],
  48           'grant_report_received' => [
  49             'no_display' => TRUE,
  50             'required' => TRUE,
  51           ],
  52           'currency' => [
  53             'no_display' => TRUE,
  54             'required' => TRUE,
  55           ],
  56         ],
  57         'filters' => [
  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,
  63           ],
  64           'decision_date' => [
  65             'name' => 'decision_date',
  66             'title' => ts('Grant Decision'),
  67             'operatorType' => CRM_Report_Form::OP_DATE,
  68             'type' => CRM_Utils_Type::T_DATE,
  69           ],
  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,
  75           ],
  76           'grant_due_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,
  81           ],
  82           'grant_type' => [
  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'),
  87           ],
  88           'status_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'),
  94           ],
  95           'amount_requested' => [
  96             'name' => 'amount_requested',
  97             'title' => ts('Amount Requested'),
  98             'type' => CRM_Utils_Type::T_MONEY,
  99           ],
 100           'amount_granted' => [
 101             'name' => 'amount_granted',
 102             'title' => ts('Amount Granted'),
 103           ],
 104           'grant_report_received' => [
 105             'name' => 'grant_report_received',
 106             'title' => ts('Report Received'),
 107             'operatorType' => CRM_Report_Form::OP_SELECT,
 108             'options' => [
 109               '' => ts('- select -'),
 110               0 => ts('No'),
 111               1 => ts('Yes'),
 112             ],
 113           ],
 114         ],
 115       ],
 116       'civicrm_contact' => [
 117         'dao' => 'CRM_Contact_DAO_Contact',
 118         'fields' => [
 119           'id' => [
 120             'required' => TRUE,
 121             'no_display' => TRUE,
 122           ],
 123           'gender_id' => [
 124             'name' => 'gender_id',
 125             'title' => ts('By Gender'),
 126           ],
 127           'contact_type' => [
 128             'name' => 'contact_type',
 129             'title' => ts('By Contact Type'),
 130           ],
 131         ],
 132         'filters' => [
 133           'gender_id' => [
 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'),
 138           ],
 139           'contact_type' => [
 140             'name' => 'contact_type',
 141             'title' => ts('Contact Type'),
 142             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 143             'options' => CRM_Contact_BAO_ContactType::basicTypePairs(),
 144           ],
 145         ],
 146         'grouping' => 'contact-fields',
 147       ],
 148       'civicrm_worldregion' => [
 149         'dao' => 'CRM_Core_DAO_Worldregion',
 150         'fields' => [
 151           'id' => [
 152             'no_display' => TRUE,
 153           ],
 154           'name' => [
 155             'name' => 'name',
 156             'title' => ts('By World Region'),
 157           ],
 158         ],
 159         'filters' => [
 160           'region_id' => [
 161             'name' => 'id',
 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(),
 166           ],
 167         ],
 168       ],
 169       'civicrm_address' => [
 170         'dao' => 'CRM_Core_DAO_Address',
 171         'fields' => [
 172           'country_id' => [
 173             'name' => 'country_id',
 174             'title' => ts('By Country'),
 175           ],
 176         ],
 177         'filters' => [
 178           'country_id' => [
 179             'title' => ts('Country'),
 180             'type' => CRM_Utils_Type::T_INT,
 181             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 182             'options' => CRM_Core_PseudoConstant::country(),
 183           ],
 184         ],
 185       ],
 186     ];
 187     parent::__construct();
 188   }
 189
 190   public function select() {
 191     $select = [];
 192
 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])
 199           ) {
 200
 201             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 202
 203             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 204             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 205           }
 206         }
 207       }
 208     }
 209     $this->_selectClauses = $select;
 210
 211     $this->_select = "SELECT " . implode(', ', $select) . " ";
 212   }
 213
 214   public function from() {
 215     $this->_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  ) ";
 219
 220     $this->joinAddressFromContact();
 221     $this->joinCountryFromAddress();
 222     if ($this->isTableSelected('civicrm_worldregion')) {
 223       $this->_from .= "
 224                   LEFT JOIN civicrm_worldregion {$this->_aliases['civicrm_worldregion']}
 225                          ON {$this->_aliases['civicrm_country']}.region_id =
 226                             {$this->_aliases['civicrm_worldregion']}.id";
 227     }
 228   }
 229
 230   public function where() {
 231     $whereClause = "
 232 WHERE {$this->_aliases['civicrm_grant']}.amount_total IS NOT NULL
 233   AND {$this->_aliases['civicrm_grant']}.amount_total > 0";
 234     $this->_where = $whereClause;
 235
 236     foreach ($this->_columns as $tableName => $table) {
 237       if (array_key_exists('filters', $table)) {
 238         foreach ($table['filters'] as $fieldName => $field) {
 239
 240           $clause = NULL;
 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;
 245
 246             if ($relative || $from || $to) {
 247               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 248             }
 249           }
 250           else {
 251             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 252             if (($fieldName == 'grant_report_received') &&
 253               (CRM_Utils_Array::value("{$fieldName}_value", $this->_params) ===
 254                 0)
 255             ) {
 256               $op = 'nll';
 257               $this->_params["{$fieldName}_value"] = NULL;
 258             }
 259             if ($op) {
 260               $clause = $this->whereClause($field,
 261                 $op,
 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)
 265               );
 266             }
 267           }
 268           if (!empty($clause)) {
 269             $clauses[] = $clause;
 270           }
 271         }
 272       }
 273     }
 274     if (!empty($clauses)) {
 275       $this->_where = "WHERE " . implode(' AND ', $clauses);
 276       $this->_whereClause = $whereClause . " AND " . implode(' AND ', $clauses);
 277     }
 278   }
 279
 280   public function groupBy() {
 281     $this->_groupBy = '';
 282
 283     if (!empty($this->_params['fields']) &&
 284       is_array($this->_params['fields']) &&
 285       !empty($this->_params['fields'])
 286     ) {
 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'];
 292             }
 293           }
 294         }
 295       }
 296     }
 297     if (!empty($groupBy)) {
 298       $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 299     }
 300   }
 301
 302   public function postProcess() {
 303     // get ready with post process params
 304     $this->beginPostProcess();
 305
 306     // build query, do not apply limit
 307     $sql = $this->buildQuery(FALSE);
 308
 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);
 312
 313     // format result set.
 314     $this->formatDisplay($rows);
 315
 316     // assign variables to templates
 317     $this->doTemplateAssignment($rows);
 318
 319     // do print / pdf / instance stuff if needed
 320     $this->endPostProcess($rows);
 321   }
 322
 323   /**
 324    * Alter display of rows.
 325    *
 326    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 327    * such as rendering contacts as links.
 328    *
 329    * @param array $rows
 330    *   Rows generated by SQL, with an array for each row.
 331    */
 332   public function alterDisplay(&$rows) {
 333     $totalStatistics = $grantStatistics = [];
 334     $totalStatistics = parent::statistics($rows);
 335     $awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
 336     $grantStatistics = [];
 337
 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');
 341
 342     $grantAmountTotal = "
 343 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
 344          SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
 345   {$this->_from} ";
 346
 347     if (!empty($this->_whereClause)) {
 348       $grantAmountTotal .= " {$this->_whereClause}";
 349     }
 350
 351     $result = CRM_Core_DAO::executeQuery($grantAmountTotal);
 352     while ($result->fetch()) {
 353       $grantsReceived = $result->count;
 354       $totalAmount = $result->totalAmount;
 355     }
 356
 357     if (!$grantsReceived) {
 358       return;
 359     }
 360
 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
 365   {$this->_from} ";
 366
 367     if (!empty($this->_where)) {
 368       $grantAmountAwarded .= " {$this->_where}";
 369     }
 370     $values = CRM_Core_DAO::executeQuery($grantAmountAwarded);
 371     while ($values->fetch()) {
 372       $awardedGrants = $values->count;
 373       $awardedGrantsAmount = $values->totalAmount;
 374       $amountGranted = $values->grantedAmount;
 375     }
 376
 377     foreach ($rows as $key => $values) {
 378       if (!empty($values['civicrm_grant_grant_report_received'])) {
 379         $grantReportsReceived++;
 380       }
 381
 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
 387         );
 388       }
 389
 390       if (array_key_exists('civicrm_worldregion_name', $values)) {
 391         $region = $values['civicrm_worldregion_name'] ?: 'Unassigned';
 392         $grantStatistics['civicrm_worldregion_name']['title'] = ts('By Region');
 393         self::getStatistics($grantStatistics['civicrm_worldregion_name'], $region, $values,
 394           $awardedGrants, $awardedGrantsAmount
 395         );
 396       }
 397
 398       if (array_key_exists('civicrm_address_country_id', $values)) {
 399         $country = $countries[$values['civicrm_address_country_id']] ?? 'Unassigned';
 400         $grantStatistics['civicrm_address_country_id']['title'] = ts('By Country');
 401         self::getStatistics($grantStatistics['civicrm_address_country_id'], $country, $values,
 402           $awardedGrants, $awardedGrantsAmount
 403         );
 404       }
 405
 406       if ($type = CRM_Utils_Array::value('civicrm_contact_contact_type', $values)) {
 407         $grantStatistics['civicrm_contact_contact_type']['title'] = ts('By Contact Type');
 408         $title = "Total Number of {$type}(s)";
 409         self::getStatistics($grantStatistics['civicrm_contact_contact_type'], $title, $values,
 410           $awardedGrants, $awardedGrantsAmount
 411         );
 412       }
 413
 414       if (array_key_exists('civicrm_contact_gender_id', $values)) {
 415         $genderLabel = $gender[$values['civicrm_contact_gender_id']] ?? 'Unassigned';
 416         $grantStatistics['civicrm_contact_gender_id']['title'] = ts('By Gender');
 417         self::getStatistics($grantStatistics['civicrm_contact_gender_id'], $genderLabel, $values,
 418           $awardedGrants, $awardedGrantsAmount
 419         );
 420       }
 421
 422       foreach ($values as $customField => $customValue) {
 423         if (strstr($customField, 'civicrm_value_')) {
 424           $customFieldTitle = $this->_columnHeaders[$customField]['title'] ?? NULL;
 425           $customGroupTitle = explode('_custom', strstr($customField, 'civicrm_value_'));
 426           $customGroupTitle = $this->_columns[$customGroupTitle[0]]['group_title'];
 427           $grantStatistics[$customGroupTitle]['title'] = ts('By %1', [1 => $customGroupTitle]);
 428
 429           self::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values,
 430             $awardedGrants, $awardedGrantsAmount, !$customValue
 431           );
 432         }
 433       }
 434     }
 435
 436     $totalStatistics['total_statistics'] = [
 437       'grants_received' => [
 438         'title' => ts('Grant Requests Received'),
 439         'count' => $grantsReceived,
 440         'amount' => $totalAmount,
 441       ],
 442       'grants_awarded' => [
 443         'title' => ts('Grants Awarded'),
 444         'count' => $awardedGrants,
 445         'amount' => $amountGranted,
 446       ],
 447       'grants_report_received' => [
 448         'title' => ts('Grant Reports Received'),
 449         'count' => $grantReportsReceived,
 450       ],
 451     ];
 452
 453     $this->assign('totalStatistics', $totalStatistics);
 454     $this->assign('grantStatistics', $grantStatistics);
 455
 456     if ($this->_outputMode == 'csv' ||
 457       $this->_outputMode == 'pdf'
 458     ) {
 459       $row = [];
 460       $this->_columnHeaders = [
 461         'civicrm_grant_total_grants' => ['title' => ts('Summary')],
 462         'civicrm_grant_count' => ['title' => ts('Count')],
 463         'civicrm_grant_amount' => ['title' => ts('Amount')],
 464       ];
 465       foreach ($totalStatistics['total_statistics'] as $title => $value) {
 466         $row[] = [
 467           'civicrm_grant_total_grants' => $value['title'],
 468           'civicrm_grant_count' => $value['count'],
 469           'civicrm_grant_amount' => $value['amount'],
 470         ];
 471       }
 472
 473       if (!empty($grantStatistics)) {
 474         foreach ($grantStatistics as $key => $value) {
 475           $row[] = [
 476             'civicrm_grant_total_grants' => $value['title'],
 477             'civicrm_grant_count' => ts('Number of Grants') . ' (%)',
 478             'civicrm_grant_amount' => ts('Total Amount') . ' (%)',
 479           ];
 480
 481           foreach ($value['value'] as $field => $values) {
 482             foreach ($values['currency'] as $currency => $amount) {
 483               $totalAmount[$currency] = $currency . $amount['value'] .
 484                 "({$values['percentage']}%)";
 485             }
 486             $totalAmt = implode(', ', $totalAmount);
 487             $count = empty($values['count']) ? '' : "{$values['count']} ({$values['percentage']}%)";
 488             $row[] = [
 489               'civicrm_grant_total_grants' => $field,
 490               'civicrm_grant_count' => $count,
 491               'civicrm_grant_amount' => $totalAmt,
 492             ];
 493           }
 494         }
 495       }
 496       $rows = $row;
 497     }
 498   }
 499
 500   /**
 501    * @param $grantStatistics
 502    * @param $fieldValue
 503    * @param $values
 504    * @param $awardedGrants
 505    * @param $awardedGrantsAmount
 506    * @param bool $customData
 507    */
 508   public static function getStatistics(
 509     &$grantStatistics, $fieldValue, $values,
 510     $awardedGrants, $awardedGrantsAmount, $customData = FALSE
 511   ) {
 512     if (!$awardedGrantsAmount) {
 513       return;
 514     }
 515
 516     $currencies = CRM_Core_PseudoConstant::get('CRM_Grant_DAO_Grant', 'currency', ['labelColumn' => 'name']);
 517     $currency = $currencies[$values['civicrm_grant_currency']];
 518
 519     if (!$customData) {
 520       if (!isset($grantStatistics['value'][$fieldValue]['currency'][$currency])
 521         ||
 522         !isset($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'])
 523       ) {
 524         $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] = 0;
 525       }
 526       $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] += $values['civicrm_grant_amount_total'];
 527       $grantStatistics['value'][$fieldValue]['currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] /
 528           $awardedGrantsAmount) * 100);
 529       if (!isset($grantStatistics['value'][$fieldValue]['count'])) {
 530         $grantStatistics['value'][$fieldValue]['count'] = 0;
 531       }
 532       $grantStatistics['value'][$fieldValue]['count']++;
 533       $grantStatistics['value'][$fieldValue]['percentage'] = round(($grantStatistics['value'][$fieldValue]['count'] /
 534           $awardedGrants) * 100);
 535     }
 536     else {
 537       if (!isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency])
 538         ||
 539         !isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'])
 540       ) {
 541         $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] = 0;
 542       }
 543       $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] += $values['civicrm_grant_amount_total'];
 544       $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] /
 545           $awardedGrantsAmount) * 100);
 546       $grantStatistics['value'][$fieldValue]['unassigned_count']++;
 547       $grantStatistics['value'][$fieldValue]['unassigned_percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_count'] /
 548           $awardedGrants) * 100);
 549     }
 550   }
 551
 552 }