crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/CRM/Report/Form/Grant/Statistics.php

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