CRM/Report/Form/Grant/Statistics.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  */
  33 class CRM_Report_Form_Grant_Statistics extends CRM_Report_Form {
  34
  35   protected $_customGroupExtends = ['Grant'];
  36
  37   protected $_add2groupSupported = FALSE;
  38
  39   /**
  40    * Class constructor.
  41    */
  42   public function __construct() {
  43     $this->_columns = [
  44       'civicrm_grant' => [
  45         'dao' => 'CRM_Grant_DAO_Grant',
  46         'fields' => [
  47           'summary_statistics' => [
  48             'name' => 'id',
  49             'title' => ts('Summary Statistics'),
  50             'required' => TRUE,
  51           ],
  52           'grant_type_id' => [
  53             'name' => 'grant_type_id',
  54             'title' => ts('By Grant Type'),
  55           ],
  56           'status_id' => [
  57             'no_display' => TRUE,
  58             'required' => TRUE,
  59           ],
  60           'amount_total' => [
  61             'no_display' => TRUE,
  62             'required' => TRUE,
  63           ],
  64           'grant_report_received' => [
  65             'no_display' => TRUE,
  66             'required' => TRUE,
  67           ],
  68           'currency' => [
  69             'no_display' => TRUE,
  70             'required' => TRUE,
  71           ],
  72         ],
  73         'filters' => [
  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,
  79           ],
  80           'decision_date' => [
  81             'name' => 'decision_date',
  82             'title' => ts('Grant Decision'),
  83             'operatorType' => CRM_Report_Form::OP_DATE,
  84             'type' => CRM_Utils_Type::T_DATE,
  85           ],
  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,
  91           ],
  92           'grant_due_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,
  97           ],
  98           'grant_type' => [
  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'),
 103           ],
 104           'status_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'),
 110           ],
 111           'amount_requested' => [
 112             'name' => 'amount_requested',
 113             'title' => ts('Amount Requested'),
 114             'type' => CRM_Utils_Type::T_MONEY,
 115           ],
 116           'amount_granted' => [
 117             'name' => 'amount_granted',
 118             'title' => ts('Amount Granted'),
 119           ],
 120           'grant_report_received' => [
 121             'name' => 'grant_report_received',
 122             'title' => ts('Report Received'),
 123             'operatorType' => CRM_Report_Form::OP_SELECT,
 124             'options' => [
 125               '' => ts('- select -'),
 126               0 => ts('No'),
 127               1 => ts('Yes'),
 128             ],
 129           ],
 130         ],
 131       ],
 132       'civicrm_contact' => [
 133         'dao' => 'CRM_Contact_DAO_Contact',
 134         'fields' => [
 135           'id' => [
 136             'required' => TRUE,
 137             'no_display' => TRUE,
 138           ],
 139           'gender_id' => [
 140             'name' => 'gender_id',
 141             'title' => ts('By Gender'),
 142           ],
 143           'contact_type' => [
 144             'name' => 'contact_type',
 145             'title' => ts('By Contact Type'),
 146           ],
 147         ],
 148         'filters' => [
 149           'gender_id' => [
 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'),
 154           ],
 155           'contact_type' => [
 156             'name' => 'contact_type',
 157             'title' => ts('Contact Type'),
 158             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 159             'options' => CRM_Contact_BAO_ContactType::basicTypePairs(),
 160           ],
 161         ],
 162         'grouping' => 'contact-fields',
 163       ],
 164       'civicrm_worldregion' => [
 165         'dao' => 'CRM_Core_DAO_Worldregion',
 166         'fields' => [
 167           'id' => [
 168             'no_display' => TRUE,
 169           ],
 170           'name' => [
 171             'name' => 'name',
 172             'title' => ts('By World Region'),
 173           ],
 174         ],
 175         'filters' => [
 176           'region_id' => [
 177             'name' => 'id',
 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(),
 182           ],
 183         ],
 184       ],
 185       'civicrm_address' => [
 186         'dao' => 'CRM_Core_DAO_Address',
 187         'fields' => [
 188           'country_id' => [
 189             'name' => 'country_id',
 190             'title' => ts('By Country'),
 191           ],
 192         ],
 193         'filters' => [
 194           'country_id' => [
 195             'title' => ts('Country'),
 196             'type' => CRM_Utils_Type::T_INT,
 197             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 198             'options' => CRM_Core_PseudoConstant::country(),
 199           ],
 200         ],
 201       ],
 202     ];
 203     parent::__construct();
 204   }
 205
 206   public function select() {
 207     $select = [];
 208
 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])
 215           ) {
 216
 217             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 218
 219             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 220             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 221           }
 222         }
 223       }
 224     }
 225     $this->_selectClauses = $select;
 226
 227     $this->_select = "SELECT " . implode(', ', $select) . " ";
 228   }
 229
 230   public function from() {
 231     $this->_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  ) ";
 235
 236     $this->joinAddressFromContact();
 237     $this->joinCountryFromAddress();
 238     if ($this->isTableSelected('civicrm_worldregion')) {
 239       $this->_from .= "
 240                   LEFT JOIN civicrm_worldregion {$this->_aliases['civicrm_worldregion']}
 241                          ON {$this->_aliases['civicrm_country']}.region_id =
 242                             {$this->_aliases['civicrm_worldregion']}.id";
 243     }
 244   }
 245
 246   public function where() {
 247     $whereClause = "
 248 WHERE {$this->_aliases['civicrm_grant']}.amount_total IS NOT NULL
 249   AND {$this->_aliases['civicrm_grant']}.amount_total > 0";
 250     $this->_where = $whereClause;
 251
 252     foreach ($this->_columns as $tableName => $table) {
 253       if (array_key_exists('filters', $table)) {
 254         foreach ($table['filters'] as $fieldName => $field) {
 255
 256           $clause = NULL;
 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);
 261
 262             if ($relative || $from || $to) {
 263               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 264             }
 265           }
 266           else {
 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) ===
 270                 0)
 271             ) {
 272               $op = 'nll';
 273               $this->_params["{$fieldName}_value"] = NULL;
 274             }
 275             if ($op) {
 276               $clause = $this->whereClause($field,
 277                 $op,
 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)
 281               );
 282             }
 283           }
 284           if (!empty($clause)) {
 285             $clauses[] = $clause;
 286           }
 287         }
 288       }
 289     }
 290     if (!empty($clauses)) {
 291       $this->_where = "WHERE " . implode(' AND ', $clauses);
 292       $this->_whereClause = $whereClause . " AND " . implode(' AND ', $clauses);
 293     }
 294   }
 295
 296   public function groupBy() {
 297     $this->_groupBy = '';
 298
 299     if (!empty($this->_params['fields']) &&
 300       is_array($this->_params['fields']) &&
 301       !empty($this->_params['fields'])
 302     ) {
 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'];
 308             }
 309           }
 310         }
 311       }
 312     }
 313     if (!empty($groupBy)) {
 314       $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 315     }
 316   }
 317
 318   public function postProcess() {
 319     // get ready with post process params
 320     $this->beginPostProcess();
 321
 322     // build query, do not apply limit
 323     $sql = $this->buildQuery(FALSE);
 324
 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);
 328
 329     // format result set.
 330     $this->formatDisplay($rows);
 331
 332     // assign variables to templates
 333     $this->doTemplateAssignment($rows);
 334
 335     // do print / pdf / instance stuff if needed
 336     $this->endPostProcess($rows);
 337   }
 338
 339   /**
 340    * Alter display of rows.
 341    *
 342    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 343    * such as rendering contacts as links.
 344    *
 345    * @param array $rows
 346    *   Rows generated by SQL, with an array for each row.
 347    */
 348   public function alterDisplay(&$rows) {
 349     $totalStatistics = $grantStatistics = [];
 350     $totalStatistics = parent::statistics($rows);
 351     $awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
 352     $grantStatistics = [];
 353
 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');
 357
 358     $grantAmountTotal = "
 359 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
 360          SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
 361   {$this->_from} ";
 362
 363     if (!empty($this->_whereClause)) {
 364       $grantAmountTotal .= " {$this->_whereClause}";
 365     }
 366
 367     $result = CRM_Core_DAO::executeQuery($grantAmountTotal);
 368     while ($result->fetch()) {
 369       $grantsReceived = $result->count;
 370       $totalAmount = $result->totalAmount;
 371     }
 372
 373     if (!$grantsReceived) {
 374       return;
 375     }
 376
 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
 381   {$this->_from} ";
 382
 383     if (!empty($this->_where)) {
 384       $grantAmountAwarded .= " {$this->_where}";
 385     }
 386     $values = CRM_Core_DAO::executeQuery($grantAmountAwarded);
 387     while ($values->fetch()) {
 388       $awardedGrants = $values->count;
 389       $awardedGrantsAmount = $values->totalAmount;
 390       $amountGranted = $values->grantedAmount;
 391     }
 392
 393     foreach ($rows as $key => $values) {
 394       if (!empty($values['civicrm_grant_grant_report_received'])) {
 395         $grantReportsReceived++;
 396       }
 397
 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
 403         );
 404       }
 405
 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
 412         );
 413       }
 414
 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
 421         );
 422       }
 423
 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
 429         );
 430       }
 431
 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
 438         );
 439       }
 440
 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]);
 447
 448           $customData = ($customValue) ? FALSE : TRUE;
 449           self::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values,
 450             $awardedGrants, $awardedGrantsAmount, $customData
 451           );
 452         }
 453       }
 454     }
 455
 456     $totalStatistics['total_statistics'] = [
 457       'grants_received' => [
 458         'title' => ts('Grant Requests Received'),
 459         'count' => $grantsReceived,
 460         'amount' => $totalAmount,
 461       ],
 462       'grants_awarded' => [
 463         'title' => ts('Grants Awarded'),
 464         'count' => $awardedGrants,
 465         'amount' => $amountGranted,
 466       ],
 467       'grants_report_received' => [
 468         'title' => ts('Grant Reports Received'),
 469         'count' => $grantReportsReceived,
 470       ],
 471     ];
 472
 473     $this->assign('totalStatistics', $totalStatistics);
 474     $this->assign('grantStatistics', $grantStatistics);
 475
 476     if ($this->_outputMode == 'csv' ||
 477       $this->_outputMode == 'pdf'
 478     ) {
 479       $row = [];
 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')],
 484       ];
 485       foreach ($totalStatistics['total_statistics'] as $title => $value) {
 486         $row[] = [
 487           'civicrm_grant_total_grants' => $value['title'],
 488           'civicrm_grant_count' => $value['count'],
 489           'civicrm_grant_amount' => $value['amount'],
 490         ];
 491       }
 492
 493       if (!empty($grantStatistics)) {
 494         foreach ($grantStatistics as $key => $value) {
 495           $row[] = [
 496             'civicrm_grant_total_grants' => $value['title'],
 497             'civicrm_grant_count' => ts('Number of Grants') . ' (%)',
 498             'civicrm_grant_amount' => ts('Total Amount') . ' (%)',
 499           ];
 500
 501           foreach ($value['value'] as $field => $values) {
 502             foreach ($values['currency'] as $currency => $amount) {
 503               $totalAmount[$currency] = $currency . $amount['value'] .
 504                 "({$values['percentage']}%)";
 505             }
 506             $totalAmt = implode(', ', $totalAmount);
 507             $count = (boolean) CRM_Utils_Array::value('count', $values, 0) ? $values['count'] . " ({$values['percentage']}%)" : '';
 508             $row[] = [
 509               'civicrm_grant_total_grants' => $field,
 510               'civicrm_grant_count' => $count,
 511               'civicrm_grant_amount' => $totalAmt,
 512             ];
 513           }
 514         }
 515       }
 516       $rows = $row;
 517     }
 518   }
 519
 520   /**
 521    * @param $grantStatistics
 522    * @param $fieldValue
 523    * @param $values
 524    * @param $awardedGrants
 525    * @param $awardedGrantsAmount
 526    * @param bool $customData
 527    */
 528   public static function getStatistics(
 529     &$grantStatistics, $fieldValue, $values,
 530     $awardedGrants, $awardedGrantsAmount, $customData = FALSE
 531   ) {
 532     if (!$awardedGrantsAmount) {
 533       return;
 534     }
 535
 536     $currencies = CRM_Core_PseudoConstant::get('CRM_Grant_DAO_Grant', 'currency', ['labelColumn' => 'symbol']);
 537     $currency = $currencies[$values['civicrm_grant_currency']];
 538
 539     if (!$customData) {
 540       if (!isset($grantStatistics['value'][$fieldValue]['currency'][$currency])
 541         ||
 542         !isset($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'])
 543       ) {
 544         $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] = 0;
 545       }
 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;
 551       }
 552       $grantStatistics['value'][$fieldValue]['count']++;
 553       $grantStatistics['value'][$fieldValue]['percentage'] = round(($grantStatistics['value'][$fieldValue]['count'] /
 554           $awardedGrants) * 100);
 555     }
 556     else {
 557       if (!isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency])
 558         ||
 559         !isset($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'])
 560       ) {
 561         $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] = 0;
 562       }
 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);
 569     }
 570   }
 571
 572 }