4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.3 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2013 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License and the CiviCRM Licensing Exception along |
22 | with this program; if not, contact CiviCRM LLC |
23 | at info[AT]civicrm[DOT]org. If you have questions about the |
24 | GNU Affero General Public License or the licensing of CiviCRM, |
25 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
26 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2013
36 class CRM_Report_Form_Grant_Statistics
extends CRM_Report_Form
{
38 protected $_addressField = FALSE;
40 protected $_customGroupExtends = array('Grant');
42 protected $_add2groupSupported = FALSE;
43 function __construct() {
44 $this->_columns
= array(
47 'dao' => 'CRM_Grant_DAO_Grant',
50 'summary_statistics' =>
53 'title' => ts('Summary Statistics'),
58 'name' => 'grant_type_id',
59 'title' => ts('By Grant Type'),
71 'grant_report_received' =>
84 'application_received_date' =>
86 'name' => 'application_received_date',
87 'title' => ts('Application Received'),
88 'operatorType' => CRM_Report_Form
::OP_DATE
,
89 'type' => CRM_Utils_Type
::T_DATE
,
93 'name' => 'decision_date',
94 'title' => ts('Grant Decision'),
95 'operatorType' => CRM_Report_Form
::OP_DATE
,
96 'type' => CRM_Utils_Type
::T_DATE
,
98 'money_transfer_date' =>
100 'name' => 'money_transfer_date',
101 'title' => ts('Money Transferred'),
102 'operatorType' => CRM_Report_Form
::OP_DATE
,
103 'type' => CRM_Utils_Type
::T_DATE
,
107 'name' => 'grant_due_date',
108 'title' => ts('Grant Report Due'),
109 'operatorType' => CRM_Report_Form
::OP_DATE
,
110 'type' => CRM_Utils_Type
::T_DATE
,
114 'name' => 'grant_type_id',
115 'title' => ts('Grant Type'),
116 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
117 'options' => CRM_Grant_PseudoConstant
::grantType(),
121 'name' => 'status_id',
122 'title' => ts('Grant Status'),
123 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
124 'options' => CRM_Grant_PseudoConstant
::grantStatus(),
126 'amount_requested' =>
128 'name' => 'amount_requested',
129 'title' => ts('Amount Requested'),
130 'type' => CRM_Utils_Type
::T_MONEY
,
134 'name' => 'amount_granted',
135 'title' => ts('Amount Granted'),
137 'grant_report_received' =>
139 'name' => 'grant_report_received',
140 'title' => ts('Report Received'),
141 'operatorType' => CRM_Report_Form
::OP_SELECT
,
142 'options' => array('' => ts('- select -'),
151 'dao' => 'CRM_Contact_DAO_Contact',
157 'no_display' => TRUE,
161 'name' => 'gender_id',
162 'title' => ts('By Gender'),
166 'name' => 'contact_type',
167 'title' => ts('By Contact Type'),
174 'name' => 'gender_id',
175 'title' => ts('Gender'),
176 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
177 'options' => CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id'),
181 'name' => 'contact_type',
182 'title' => ts('Contact Type'),
183 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
184 'options' => CRM_Contact_BAO_ContactType
::basicTypePairs(),
187 'grouping' => 'contact-fields',
189 'civicrm_world_region' =>
191 'dao' => 'CRM_Core_DAO_Worldregion',
196 'no_display' => TRUE,
201 'title' => ts('By World Region'),
209 'title' => ts('World Region'),
210 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
211 'options' => CRM_Core_PseudoConstant
::worldRegion(),
217 'dao' => 'CRM_Core_DAO_Address',
222 'name' => 'country_id',
223 'title' => ts('By Country'),
229 array('title' => ts('Country'),
230 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
231 'options' => CRM_Core_PseudoConstant
::country(),
236 parent
::__construct();
242 $this->_columnHeaders
= array();
243 foreach ($this->_columns
as $tableName => $table) {
244 if (in_array($tableName, array(
245 'civicrm_address', 'civicrm_world_region'))) {
246 $this->_addressField
= TRUE;
249 if (array_key_exists('fields', $table)) {
250 foreach ($table['fields'] as $fieldName => $field) {
251 if (CRM_Utils_Array
::value('required', $field) ||
252 CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])
255 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
257 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
258 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
264 $this->_select
= "SELECT " . implode(', ', $select) . " ";
269 FROM civicrm_grant {$this->_aliases['civicrm_grant']}
270 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
271 ON ({$this->_aliases['civicrm_grant']}.contact_id = {$this->_aliases['civicrm_contact']}.id ) ";
272 if ($this->_addressField
) {
274 LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
275 ON {$this->_aliases['civicrm_contact']}.id =
276 {$this->_aliases['civicrm_address']}.contact_id AND
277 {$this->_aliases['civicrm_address']}.is_primary = 1\n
278 LEFT JOIN civicrm_country country
279 ON {$this->_aliases['civicrm_address']}.country_id =
281 LEFT JOIN civicrm_worldregion {$this->_aliases['civicrm_world_region']}
282 ON country.region_id =
283 {$this->_aliases['civicrm_world_region']}.id";
288 $approved = array_search( 'Approved', CRM_Grant_PseudoConstant
::grantStatus( ) );
290 WHERE {$this->_aliases['civicrm_grant']}.amount_total IS NOT NULL
291 AND {$this->_aliases['civicrm_grant']}.amount_total > 0";
292 $this->_where
= $whereClause . " AND {$this->_aliases['civicrm_grant']}.status_id = {$approved} ";
294 foreach ($this->_columns
as $tableName => $table) {
295 if (array_key_exists('filters', $table)) {
296 foreach ($table['filters'] as $fieldName => $field) {
299 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
300 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
301 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
302 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
304 if ($relative ||
$from ||
$to) {
305 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
309 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
310 if (($fieldName == 'grant_report_received') &&
311 (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) === 0)
314 $this->_params
["{$fieldName}_value"] = NULL;
317 $clause = $this->whereClause($field,
319 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
320 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
321 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
325 if (!empty($clause)) {
326 $clauses[] = $clause;
327 $this->_where
.= " AND " . implode(' AND ', $clauses);
328 $this->_whereClause
= $whereClause . " AND " . implode(' AND ', $clauses);
336 $this->_groupBy
= '';
338 if (CRM_Utils_Array
::value('fields', $this->_params
) &&
339 is_array($this->_params
['fields']) &&
340 !empty($this->_params
['fields'])
342 foreach ($this->_columns
as $tableName => $table) {
343 if (array_key_exists('fields', $table)) {
344 foreach ($table['fields'] as $fieldName => $field) {
345 if (CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])) {
346 $this->_groupBy
[] = $field['dbAlias'];
352 if (!empty($this->_groupBy
)) {
353 $this->_groupBy
= " GROUP BY " . implode(', ', $this->_groupBy
);
357 function postProcess() {
358 // get ready with post process params
359 $this->beginPostProcess();
361 // build query, do not apply limit
362 $sql = $this->buildQuery(FALSE);
364 // build array of result based on column headers. This method also allows
365 // modifying column headers before using it to build result set i.e $rows.
366 $this->buildRows($sql, $rows);
368 // format result set.
369 $this->formatDisplay($rows);
371 // assign variables to templates
372 $this->doTemplateAssignment($rows);
374 // do print / pdf / instance stuff if needed
375 $this->endPostProcess($rows);
378 function alterDisplay(&$rows) {
379 $totalStatistics = $grantStatistics = array();
380 $totalStatistics = parent
::statistics($rows);
381 $awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
382 $grantStatistics = array();
384 $grantTypes = CRM_Grant_PseudoConstant
::grantType();
385 $countries = CRM_Core_PseudoConstant
::country();
386 $gender = CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id');
388 $grantAmountTotal = "
389 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
390 SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
393 if (!empty($this->_whereClause
)) {
394 $grantAmountTotal .= " {$this->_whereClause}";
397 $result = CRM_Core_DAO
::executeQuery($grantAmountTotal);
398 while ($result->fetch()) {
399 $grantsReceived = $result->count
;
400 $totalAmount = $result->totalAmount
;
403 if (!$grantsReceived) {
407 $grantAmountAwarded = "
408 SELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,
409 SUM({$this->_aliases['civicrm_grant']}.amount_granted) as grantedAmount,
410 SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount
413 if (!empty($this->_where
)) {
414 $grantAmountAwarded .= " {$this->_where}";
416 $values = CRM_Core_DAO
::executeQuery($grantAmountAwarded);
417 while ($values->fetch()) {
418 $awardedGrants = $values->count
;
419 $awardedGrantsAmount = $values->totalAmount
;
420 $amountGranted = $values->grantedAmount
;
423 foreach ($rows as $key => $values) {
424 if (CRM_Utils_Array
::value('civicrm_grant_grant_report_received', $values)) {
425 $grantReportsReceived++
;
428 if (CRM_Utils_Array
::value('civicrm_grant_grant_type_id', $values)) {
429 $grantType = CRM_Utils_Array
::value($values['civicrm_grant_grant_type_id'], $grantTypes);
430 $grantStatistics['civicrm_grant_grant_type_id']['title'] = ts('By Grant Type');
431 self
::getStatistics($grantStatistics['civicrm_grant_grant_type_id'], $grantType, $values,
432 $awardedGrants, $awardedGrantsAmount
436 if (array_key_exists('civicrm_world_region_name', $values)) {
437 $region = CRM_Utils_Array
::value('civicrm_world_region_name', $values);
438 $region = ($region) ?
$region : 'Unassigned';
439 $grantStatistics['civicrm_world_region_name']['title'] = ts('By Region');
440 self
::getStatistics($grantStatistics['civicrm_world_region_name'], $region, $values,
441 $awardedGrants, $awardedGrantsAmount
445 if (array_key_exists('civicrm_address_country_id', $values)) {
446 $country = CRM_Utils_Array
::value($values['civicrm_address_country_id'], $countries);
447 $country = ($country) ?
$country : 'Unassigned';
448 $grantStatistics['civicrm_address_country_id']['title'] = ts('By Country');
449 self
::getStatistics($grantStatistics['civicrm_address_country_id'], $country, $values,
450 $awardedGrants, $awardedGrantsAmount
454 if ($type = CRM_Utils_Array
::value('civicrm_contact_contact_type', $values)) {
455 $grantStatistics['civicrm_contact_contact_type']['title'] = ts('By Contact Type');
456 $title = "Total Number of {$type}(s)";
457 self
::getStatistics($grantStatistics['civicrm_contact_contact_type'], $title, $values,
458 $awardedGrants, $awardedGrantsAmount
462 if (array_key_exists('civicrm_contact_gender_id', $values)) {
463 $genderLabel = CRM_Utils_Array
::value($values['civicrm_contact_gender_id'], $gender);
464 $genderLabel = ($genderLabel) ?
$genderLabel : 'Unassigned';
465 $grantStatistics['civicrm_contact_gender_id']['title'] = ts('By Gender');
466 self
::getStatistics($grantStatistics['civicrm_contact_gender_id'], $genderLabel, $values,
467 $awardedGrants, $awardedGrantsAmount
471 foreach ($values as $customField => $customValue) {
472 if (strstr($customField, 'civicrm_value_')) {
473 $customFieldTitle = CRM_Utils_Array
::value('title', $this->_columnHeaders
[$customField]);
474 $customGroupTitle = explode('_custom', strstr($customField, 'civicrm_value_'));
475 $customGroupTitle = $this->_columns
[$customGroupTitle[0]]['group_title'];
476 $grantStatistics[$customGroupTitle]['title'] = ts('By %1', array(1 => $customGroupTitle));
478 $customData = ($customValue) ?
FALSE : TRUE;
479 self
::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values,
480 $awardedGrants, $awardedGrantsAmount, $customData
486 $totalStatistics['total_statistics'] = array('grants_received' => array('title' => ts('Grant Requests Received'),
487 'count' => $grantsReceived,
488 'amount' => $totalAmount,
490 'grants_awarded' => array('title' => ts('Grants Awarded'),
491 'count' => $awardedGrants,
492 'amount' => $amountGranted,
494 'grants_report_received' => array('title' => ts('Grant Reports Received'),
495 'count' => $grantReportsReceived,
499 $this->assign('totalStatistics', $totalStatistics);
500 $this->assign('grantStatistics', $grantStatistics);
502 if ($this->_outputMode
== 'csv' ||
503 $this->_outputMode
== 'pdf'
506 $this->_columnHeaders
= array('civicrm_grant_total_grants' => array('title' => ts('Summary')),
507 'civicrm_grant_count' => array('title' => ts('Count')),
508 'civicrm_grant_amount' => array('title' => ts('Amount')),
510 foreach ($totalStatistics['total_statistics'] as $title => $value) {
512 'civicrm_grant_total_grants' => $value['title'],
513 'civicrm_grant_count' => $value['count'],
514 'civicrm_grant_amount' => $value['amount'],
518 if (!empty($grantStatistics)) {
519 foreach ($grantStatistics as $key => $value) {
521 'civicrm_grant_total_grants' => $value['title'],
522 'civicrm_grant_count' => ts('Number of Grants') . ' (%)',
523 'civicrm_grant_amount' => ts('Total Amount') . ' (%)',
526 foreach ($value['value'] as $field => $values) {
527 foreach ($values['currency'] as $currency => $amount) {
528 $totalAmount[$currency] = $currency . $amount['value'] . "({$values['percentage']}%)";
530 $totalAmt = implode(', ', $totalAmount);
531 $count = (boolean
)CRM_Utils_Array
::value('count', $values, 0) ?
$values['count'] . " ({$values['percentage']}%)" : '';
533 'civicrm_grant_total_grants' => $field,
534 'civicrm_grant_count' => $count,
535 'civicrm_grant_amount' => $totalAmt,
544 static function getStatistics(&$grantStatistics, $fieldValue, $values,
545 $awardedGrants, $awardedGrantsAmount, $customData = FALSE
547 if (!$awardedGrantsAmount) {
551 $currencies = CRM_Core_PseudoConstant
::get('CRM_Grant_DAO_Grant', 'currency');
552 $currency = $currencies[$values['civicrm_grant_currency']];
555 $grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] +
= $values['civicrm_grant_amount_total'];
556 $grantStatistics['value'][$fieldValue]['currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['currency'][$currency]['value'] / $awardedGrantsAmount) * 100);
557 $grantStatistics['value'][$fieldValue]['count']++
;
558 $grantStatistics['value'][$fieldValue]['percentage'] = round(($grantStatistics['value'][$fieldValue]['count'] / $awardedGrants) * 100);
561 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] +
= $values['civicrm_grant_amount_total'];
562 $grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_currency'][$currency]['value'] / $awardedGrantsAmount) * 100);
563 $grantStatistics['value'][$fieldValue]['unassigned_count']++
;
564 $grantStatistics['value'][$fieldValue]['unassigned_percentage'] = round(($grantStatistics['value'][$fieldValue]['unassigned_count'] / $awardedGrants) * 100);