5 +--------------------------------------------------------------------+
6 | CiviCRM version 4.3 |
7 +--------------------------------------------------------------------+
8 | Copyright CiviCRM LLC (c) 2004-2013 |
9 +--------------------------------------------------------------------+
10 | This file is a part of CiviCRM. |
12 | CiviCRM is free software; you can copy, modify, and distribute it |
13 | under the terms of the GNU Affero General Public License |
14 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
16 | CiviCRM is distributed in the hope that it will be useful, but |
17 | WITHOUT ANY WARRANTY; without even the implied warranty of |
18 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
19 | See the GNU Affero General Public License for more details. |
21 | You should have received a copy of the GNU Affero General Public |
22 | License and the CiviCRM Licensing Exception along |
23 | with this program; if not, contact CiviCRM LLC |
24 | at info[AT]civicrm[DOT]org. If you have questions about the |
25 | GNU Affero General Public License or the licensing of CiviCRM, |
26 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
27 +--------------------------------------------------------------------+
33 * @copyright CiviCRM LLC (c) 2004-2013
37 class CRM_Report_Form_Member_Summary
extends CRM_Report_Form
{
39 protected $_summary = NULL;
40 protected $_interval = NULL;
41 protected $_charts = array(
43 'barChart' => 'Bar Chart',
44 'pieChart' => 'Pie Chart',
46 protected $_add2groupSupported = FALSE;
48 protected $_customGroupExtends = array('Membership');
49 protected $_customGroupGroupBy = FALSE;
50 public $_drilldownReport = array('member/detail' => 'Link to Detail Report');
52 function __construct() {
53 // UI for selecting columns to appear in the report list
54 // array conatining the columns, group_bys and filters build and provided to Form
56 $this->_columns
= array(
57 'civicrm_membership' =>
59 'dao' => 'CRM_Member_DAO_MembershipType',
60 'grouping' => 'member-fields',
63 'membership_type_id' =>
65 'title' => 'Membership Type',
72 array('title' => ts('Member Since'),
73 'type' => CRM_Utils_Type
::T_DATE
,
74 'operatorType' => CRM_Report_Form
::OP_DATE
,
76 'membership_start_date' =>
78 'name' => 'start_date',
79 'title' => ts('Membership Start Date'),
80 'type' => CRM_Utils_Type
::T_DATE
,
81 'operatorType' => CRM_Report_Form
::OP_DATE
,
83 'membership_end_date' =>
86 'title' => ts('Membership End Date'),
87 'type' => CRM_Utils_Type
::T_DATE
,
88 'operatorType' => CRM_Report_Form
::OP_DATE
,
90 'membership_type_id' =>
91 array('title' => ts('Membership Type'),
92 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
93 'options' => CRM_Member_PseudoConstant
::membershipType(),
96 array('title' => ts('Membership Status'),
97 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
98 'options' => CRM_Member_PseudoConstant
::membershipStatus(NULL, NULL, 'label'),
104 array('title' => ts('Member Since'),
110 'membership_type_id' =>
112 'title' => 'Membership Type',
120 'dao' => 'CRM_Contact_DAO_Contact',
125 'no_display' => TRUE,
129 'civicrm_contribution' =>
131 'dao' => 'CRM_Contribute_DAO_Contribution',
135 array('required' => TRUE,
136 'no_display' => TRUE,
139 array('title' => ts('Amount Statistics'),
142 array('sum' => ts('Total Payments Made'),
143 'count' => ts('Contribution Count'),
144 'avg' => ts('Average'),
151 array('title' => 'Currency',
152 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
153 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
154 'type' => CRM_Utils_Type
::T_STRING
,
156 'contribution_status_id' =>
157 array('title' => ts('Contribution Status'),
158 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
159 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
162 'grouping' => 'member-fields',
165 $this->_tagFilter
= TRUE;
166 $this->_groupFilter
= TRUE;
167 $this->_currencyColumn
= 'civicrm_contribution_currency';
168 parent
::__construct();
174 $this->_columnHeaders
= array();
175 $select[] = " COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as civicrm_membership_member_count";
176 $select['joinDate'] = " {$this->_aliases['civicrm_membership']}.join_date as civicrm_membership_member_join_date";
177 $this->_columnHeaders
["civicrm_membership_member_join_date"] = array('title' => ts('Member Since'),
178 'type' => CRM_Utils_Type
::T_DATE
,
180 foreach ($this->_columns
as $tableName => $table) {
181 if (array_key_exists('group_bys', $table)) {
182 foreach ($table['group_bys'] as $fieldName => $field) {
183 if (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys'])) {
185 switch (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
187 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
189 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
190 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
191 $field['title'] = 'Week';
195 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
196 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
197 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
198 $field['title'] = 'Year';
202 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
203 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
204 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
205 $field['title'] = 'Month';
209 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
210 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
211 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
212 $field['title'] = 'Quarter';
215 if (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
216 $this->_interval
= $field['title'];
217 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
218 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
219 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params
['group_bys_freq'][$fieldName];
221 // just to make sure these values are transfered to rows.
222 // since we need that for calculation purpose,
223 // e.g making subtotals look nicer or graphs
224 $this->_columnHeaders
["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
225 $this->_columnHeaders
["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
233 if (array_key_exists('fields', $table)) {
234 foreach ($table['fields'] as $fieldName => $field) {
235 if (CRM_Utils_Array
::value('required', $field) ||
236 CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])
239 // only include statistics columns if set
240 if (CRM_Utils_Array
::value('statistics', $field)) {
241 $this->_statFields
[] = 'civicrm_membership_member_count';
242 foreach ($field['statistics'] as $stat => $label) {
243 switch (strtolower($stat)) {
245 $select[] = "IFNULL(SUM({$field['dbAlias']}), 0) as {$tableName}_{$fieldName}_{$stat}";
246 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
247 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
248 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
252 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
253 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
254 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
255 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
259 $select[] = "IFNULL(ROUND(AVG({$field['dbAlias']}),2), 0) as {$tableName}_{$fieldName}_{$stat}";
260 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
261 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
262 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
267 elseif ($fieldName == 'membership_type_id') {
268 if (!CRM_Utils_Array
::value('membership_type_id', $this->_params
['group_bys']) &&
269 CRM_Utils_Array
::value('join_date', $this->_params
['group_bys'])
271 $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']} ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
274 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
276 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
277 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['operatorType'] = CRM_Utils_Array
::value('operatorType', $field);
280 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
281 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
282 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['operatorType'] = CRM_Utils_Array
::value('operatorType', $field);
287 $this->_columnHeaders
["civicrm_membership_member_count"] = array('title' => ts('Member Count'),
288 'type' => CRM_Utils_Type
::T_INT
,
291 //If grouping is availabled then remove join date from field
293 unset($select['joinDate']);
294 unset($this->_columnHeaders
["civicrm_membership_member_join_date"]);
296 $this->_select
= "SELECT " . implode(', ', $select) . " ";
301 FROM civicrm_membership {$this->_aliases['civicrm_membership']}
303 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON ( {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contact']}.id )
305 LEFT JOIN civicrm_membership_status
306 ON ({$this->_aliases['civicrm_membership']}.status_id = civicrm_membership_status.id )
307 LEFT JOIN civicrm_membership_payment payment
308 ON ( {$this->_aliases['civicrm_membership']}.id = payment.membership_id )
309 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
310 ON payment.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
316 foreach ($this->_columns
as $tableName => $table) {
317 if (array_key_exists('filters', $table)) {
318 foreach ($table['filters'] as $fieldName => $field) {
321 if ($field['operatorType'] & CRM_Utils_Type
::T_DATE
) {
322 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
323 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
324 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
326 if ($relative ||
$from ||
$to) {
327 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
331 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
333 $clause = $this->whereClause($field,
335 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
336 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
337 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
341 if (!empty($clause)) {
342 $clauses[$fieldName] = $clause;
348 if (!empty($clauses)) {
349 $this->_where
= "WHERE {$this->_aliases['civicrm_membership']}.is_test = 0 AND " . implode(' AND ', $clauses);
352 $this->_where
= "WHERE {$this->_aliases['civicrm_membership']}.is_test = 0";
357 $this->_groupBy
= "";
358 if (is_array($this->_params
['group_bys']) &&
359 !empty($this->_params
['group_bys'])
361 foreach ($this->_columns
as $tableName => $table) {
362 if (array_key_exists('group_bys', $table)) {
363 foreach ($table['group_bys'] as $fieldName => $field) {
364 if (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys'])) {
365 if (CRM_Utils_Array
::value('chart', $field)) {
366 $this->assign('chartSupported', TRUE);
368 if (CRM_Utils_Array
::value('frequency', $table['group_bys'][$fieldName]) &&
369 CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])
372 $append = "YEAR({$field['dbAlias']}),";
373 if (in_array(strtolower($this->_params
['group_bys_freq'][$fieldName]),
378 $this->_groupBy
[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
382 $this->_groupBy
[] = $field['dbAlias'];
389 $this->_rollup
= ' WITH ROLLUP';
390 $this->_groupBy
= 'GROUP BY ' . implode(', ', $this->_groupBy
) . " {$this->_rollup} ";
393 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_membership']}.join_date";
397 function statistics(&$rows) {
398 $statistics = parent
::statistics($rows);
400 SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
401 IFNULL(SUM({$this->_aliases['civicrm_contribution']}.total_amount ), 0) as amount,
402 IFNULL(ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2),0) as avg,
403 COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as memberCount,
404 {$this->_aliases['civicrm_contribution']}.currency as currency
407 $sql = "{$select} {$this->_from} {$this->_where}
408 GROUP BY {$this->_aliases['civicrm_contribution']}.currency
411 $dao = CRM_Core_DAO
::executeQuery($sql);
413 $totalAmount = $average = array();
414 $count = $memberCount = 0;
415 while ($dao->fetch()) {
416 $totalAmount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
)."(".$dao->count
.")";
417 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
418 $count +
= $dao->count
;
419 $memberCount +
= $dao->memberCount
;
421 $statistics['counts']['amount'] = array(
422 'title' => ts('Total Amount'),
423 'value' => implode(', ', $totalAmount),
424 'type' => CRM_Utils_Type
::T_STRING
,
426 $statistics['counts']['count'] = array(
427 'title' => ts('Total Donations'),
430 $statistics['counts']['memberCount'] = array(
431 'title' => ts('Total Members'),
432 'value' => $memberCount,
434 $statistics['counts']['avg'] = array(
435 'title' => ts('Average'),
436 'value' => implode(', ', $average),
437 'type' => CRM_Utils_Type
::T_STRING
,
440 if (!(int)$statistics['counts']['amount']['value']) {
441 //if total amount is zero then hide Chart Options
442 $this->assign('chartSupported', FALSE);
448 function postProcess() {
449 parent
::postProcess();
452 function buildChart(&$rows) {
453 $graphRows = array();
455 $membershipTypeValues = CRM_Member_PseudoConstant
::membershipType();
456 $isMembershipType = CRM_Utils_Array
::value('membership_type_id', $this->_params
['group_bys']);
457 $isJoiningDate = CRM_Utils_Array
::value('join_date', $this->_params
['group_bys']);
458 if (CRM_Utils_Array
::value('charts', $this->_params
)) {
459 foreach ($rows as $key => $row) {
460 if (!($row['civicrm_membership_join_date_subtotal'] &&
461 $row['civicrm_membership_membership_type_id']
465 if ($isMembershipType) {
466 $join_date = CRM_Utils_Array
::value('civicrm_membership_join_date_start', $row);
467 $displayInterval = CRM_Utils_Array
::value('civicrm_membership_join_date_interval', $row);
469 list($year, $month) = explode('-', $join_date);
471 if (CRM_Utils_Array
::value('civicrm_membership_join_date_subtotal', $row)) {
473 switch ($this->_interval
) {
475 $displayRange = $displayInterval . ' ' . $year;
479 $displayRange = 'Quarter ' . $displayInterval . ' of ' . $year;
483 $displayRange = 'Week ' . $displayInterval . ' of ' . $year;
487 $displayRange = $year;
490 $membershipType = $displayRange . "-" . $membershipTypeValues[$row['civicrm_membership_membership_type_id']];
494 $membershipType = $membershipTypeValues[$row['civicrm_membership_membership_type_id']];
497 $interval[$membershipType] = $membershipType;
498 $display[$membershipType] = $row['civicrm_contribution_total_amount_sum'];
501 $graphRows['receive_date'][] = CRM_Utils_Array
::value('civicrm_membership_join_date_start', $row);
502 $graphRows[$this->_interval
][] = CRM_Utils_Array
::value('civicrm_membership_join_date_interval', $row);
503 $graphRows['value'][] = $row['civicrm_contribution_total_amount_sum'];
509 if ($isMembershipType) {
510 $graphRows['value'] = $display;
512 'legend' => 'Membership Summary',
513 'xname' => 'Member Since / Member Type',
516 CRM_Utils_OpenFlashChart
::reportChart($graphRows, $this->_params
['charts'], $interval, $chartInfo);
519 CRM_Utils_OpenFlashChart
::chart($graphRows, $this->_params
['charts'], $this->_interval
);
522 $this->assign('chartType', $this->_params
['charts']);
525 function alterDisplay(&$rows) {
526 // custom code to alter rows
528 foreach ($rows as $rowNum => $row) {
529 // make count columns point to detail report
530 if (CRM_Utils_Array
::value('join_date', $this->_params
['group_bys']) &&
531 CRM_Utils_Array
::value('civicrm_membership_join_date_start', $row) &&
532 $row['civicrm_membership_join_date_start'] &&
533 $row['civicrm_membership_join_date_subtotal']
536 $dateStart = CRM_Utils_Date
::customFormat($row['civicrm_membership_join_date_start'], '%Y%m%d');
537 $endDate = new DateTime($dateStart);
540 list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
542 switch (strtolower($this->_params
['group_bys_freq']['join_date'])) {
544 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
1,
545 $dateEnd['d'] - 1, $dateEnd['Y']
550 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
551 $dateEnd['d'] - 1, $dateEnd['Y'] +
1
556 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
557 $dateEnd['d'] +
6, $dateEnd['Y']
562 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
3,
563 $dateEnd['d'] - 1, $dateEnd['Y']
568 if (CRM_Utils_Array
::value('membership_type_id', $this->_params
['group_bys']) &&
569 $typeID = $row['civicrm_membership_membership_type_id']
571 $typeUrl = "&tid_op=in&tid_value={$typeID}";
574 if (!empty($this->_params
['status_id_value'])) {
575 $statusUrl = "&sid_op=in&sid_value=" . implode(",", $this->_params
['status_id_value']);
577 $url = CRM_Report_Utils_Report
::getNextUrl('member/detail',
578 "reset=1&force=1&join_date_from={$dateStart}&join_date_to={$dateEnd}{$typeUrl}{$statusUrl}",
579 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
581 $row['civicrm_membership_join_date_start'] = CRM_Utils_Date
::format($row['civicrm_membership_join_date_start']);
582 $rows[$rowNum]['civicrm_membership_join_date_start_link'] = $url;
583 $rows[$rowNum]['civicrm_membership_join_date_start_hover'] = ts("Lists Summary of Memberships for this date unit.");
588 // handle Membership Types
589 if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
590 if ($value = $row['civicrm_membership_membership_type_id']) {
591 $value = explode(',', $value);
592 foreach ($value as $key => $id) {
593 $value[$key] = CRM_Member_PseudoConstant
::membershipType($id, FALSE);
595 $rows[$rowNum]['civicrm_membership_membership_type_id'] = implode(' , ', $value);
600 // make subtotals look nicer
601 if (array_key_exists('civicrm_membership_join_date_subtotal', $row) &&
602 !$row['civicrm_membership_join_date_subtotal']
604 $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields
);
607 elseif (array_key_exists('civicrm_membership_join_date_subtotal', $row) &&
608 $row['civicrm_membership_join_date_subtotal'] &&
609 !$row['civicrm_membership_membership_type_id']
611 $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields
, FALSE);
612 $rows[$rowNum]['civicrm_membership_membership_type_id'] = '<b>SubTotal</b>';
616 // skip looking further in rows, if first row itself doesn't
617 // have the column we need