3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
33 class CRM_Report_Form_Member_Summary
extends CRM_Report_Form
{
35 protected $_summary = NULL;
36 protected $_interval = NULL;
37 protected $_charts = array(
39 'barChart' => 'Bar Chart',
40 'pieChart' => 'Pie Chart',
42 protected $_add2groupSupported = FALSE;
44 protected $_customGroupExtends = array('Membership');
45 protected $_customGroupGroupBy = FALSE;
46 public $_drilldownReport = array('member/detail' => 'Link to Detail Report');
49 * This report has not been optimised for group filtering.
51 * The functionality for group filtering has been improved but not
52 * all reports have been adjusted to take care of it. This report has not
53 * and will run an inefficient query until fixed.
59 protected $groupFilterNotOptimised = TRUE;
64 public function __construct() {
66 // UI for selecting columns to appear in the report list
67 // Array containing the columns, group_bys and filters build and provided to Form
69 // Check if CiviCampaign is a) enabled and b) has active campaigns
70 $config = CRM_Core_Config
::singleton();
71 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
72 if ($campaignEnabled) {
73 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
74 $this->activeCampaigns
= $getCampaigns['campaigns'];
75 asort($this->activeCampaigns
);
78 $this->_columns
= array(
79 'civicrm_membership' => array(
80 'dao' => 'CRM_Member_DAO_MembershipType',
81 'grouping' => 'member-fields',
83 'membership_type_id' => array(
84 'title' => ts('Membership Type'),
90 'title' => ts('Member Since'),
91 'type' => CRM_Utils_Type
::T_DATE
,
92 'operatorType' => CRM_Report_Form
::OP_DATE
,
94 'membership_start_date' => array(
95 'name' => 'start_date',
96 'title' => ts('Membership Start Date'),
97 'type' => CRM_Utils_Type
::T_DATE
,
98 'operatorType' => CRM_Report_Form
::OP_DATE
,
100 'membership_end_date' => array(
101 'name' => 'end_date',
102 'title' => ts('Membership End Date'),
103 'type' => CRM_Utils_Type
::T_DATE
,
104 'operatorType' => CRM_Report_Form
::OP_DATE
,
106 'owner_membership_id' => array(
107 'title' => ts('Membership Owner ID'),
108 'type' => CRM_Utils_Type
::T_INT
,
109 'operatorType' => CRM_Report_Form
::OP_INT
,
111 'membership_type_id' => array(
112 'title' => ts('Membership Type'),
113 'type' => CRM_Utils_Type
::T_INT
,
114 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
115 'options' => CRM_Member_PseudoConstant
::membershipType(),
117 'status_id' => array(
118 'title' => ts('Membership Status'),
119 'type' => CRM_Utils_Type
::T_INT
,
120 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
121 'options' => CRM_Member_PseudoConstant
::membershipStatus(NULL, NULL, 'label'),
124 'group_bys' => array(
125 'join_date' => array(
126 'title' => ts('Member Since'),
132 'membership_type_id' => array(
133 'title' => ts('Membership Type'),
139 'civicrm_contact' => array(
140 'dao' => 'CRM_Contact_DAO_Contact',
142 'contact_id' => array(
143 'no_display' => TRUE,
145 'contact_type' => array(
146 'title' => ts('Contact Type'),
148 'contact_sub_type' => array(
149 'title' => ts('Contact Subtype'),
153 'civicrm_contribution' => array(
154 'dao' => 'CRM_Contribute_DAO_Contribution',
158 'no_display' => TRUE,
160 'total_amount' => array(
161 'title' => ts('Amount Statistics'),
163 'statistics' => array(
164 'sum' => ts('Total Payments Made'),
165 'count' => ts('Contribution Count'),
166 'avg' => ts('Average'),
172 'title' => ts('Currency'),
173 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
174 'options' => CRM_Core_OptionGroup
::values('currencies_enabled'),
176 'type' => CRM_Utils_Type
::T_STRING
,
178 'contribution_status_id' => array(
179 'title' => ts('Contribution Status'),
180 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
181 'options' => CRM_Contribute_PseudoConstant
::contributionStatus(),
184 'grouping' => 'member-fields',
187 $this->_tagFilter
= TRUE;
189 // If we have a campaign, build out the relevant elements
190 if ($campaignEnabled && !empty($this->activeCampaigns
)) {
191 $this->_columns
['civicrm_membership']['fields']['campaign_id'] = array(
192 'title' => ts('Campaign'),
193 'default' => 'false',
195 $this->_columns
['civicrm_membership']['filters']['campaign_id'] = array(
196 'title' => ts('Campaign'),
197 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
198 'options' => $this->activeCampaigns
,
199 'type' => CRM_Utils_Type
::T_INT
,
201 $this->_columns
['civicrm_membership']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
204 $this->_groupFilter
= TRUE;
205 $this->_currencyColumn
= 'civicrm_contribution_currency';
206 parent
::__construct();
209 public function select() {
212 $this->_columnHeaders
= array();
213 $select[] = " COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as civicrm_membership_member_count";
214 $select['joinDate'] = " {$this->_aliases['civicrm_membership']}.join_date as civicrm_membership_member_join_date";
215 $this->_columnHeaders
["civicrm_membership_member_join_date"] = array(
216 'title' => ts('Member Since'),
217 'type' => CRM_Utils_Type
::T_DATE
,
219 foreach ($this->_columns
as $tableName => $table) {
220 if (array_key_exists('group_bys', $table)) {
221 foreach ($table['group_bys'] as $fieldName => $field) {
222 if (!empty($this->_params
['group_bys'][$fieldName])) {
224 switch (CRM_Utils_Array
::value($fieldName, $this->_params
['group_bys_freq'])) {
226 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
228 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
229 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
230 $field['title'] = 'Week';
234 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1) AS {$tableName}_{$fieldName}_start";
235 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
236 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
237 $field['title'] = 'Year';
241 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
242 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
243 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
244 $field['title'] = 'Month';
248 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
249 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
250 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
251 $field['title'] = 'Quarter';
254 if (!empty($this->_params
['group_bys_freq'][$fieldName])) {
255 $this->_interval
= $field['title'];
256 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
257 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
258 $this->_columnHeaders
["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params
['group_bys_freq'][$fieldName];
260 // just to make sure these values are transferred to rows.
261 // since we need that for calculation purpose,
262 // e.g making subtotals look nicer or graphs
263 $this->_columnHeaders
["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
264 $this->_columnHeaders
["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
272 if (array_key_exists('fields', $table)) {
273 foreach ($table['fields'] as $fieldName => $field) {
274 if (!empty($field['required']) ||
275 !empty($this->_params
['fields'][$fieldName])
278 // only include statistics columns if set
279 if (!empty($field['statistics'])) {
280 $this->_statFields
[] = 'civicrm_membership_member_count';
281 foreach ($field['statistics'] as $stat => $label) {
282 switch (strtolower($stat)) {
284 $select[] = "IFNULL(SUM({$field['dbAlias']}), 0) as {$tableName}_{$fieldName}_{$stat}";
285 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
286 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
287 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
291 $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
292 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type
::T_INT
;
293 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
294 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
298 $select[] = "IFNULL(ROUND(AVG({$field['dbAlias']}),2), 0) as {$tableName}_{$fieldName}_{$stat}";
299 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
300 $this->_columnHeaders
["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
301 $this->_statFields
[] = "{$tableName}_{$fieldName}_{$stat}";
306 elseif ($fieldName == 'membership_type_id') {
307 if (empty($this->_params
['group_bys']['membership_type_id']) &&
308 !empty($this->_params
['group_bys']['join_date'])
310 $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']} ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
313 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
315 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
316 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['operatorType'] = CRM_Utils_Array
::value('operatorType', $field);
319 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
320 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $field['title'];
321 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['operatorType'] = CRM_Utils_Array
::value('operatorType', $field);
326 $this->_columnHeaders
["civicrm_membership_member_count"] = array(
327 'title' => ts('Member Count'),
328 'type' => CRM_Utils_Type
::T_INT
,
331 //If grouping is availabled then remove join date from field
333 unset($select['joinDate']);
334 unset($this->_columnHeaders
["civicrm_membership_member_join_date"]);
336 $this->_selectClauses
= $select;
337 $this->_select
= "SELECT " . implode(', ', $select) . " ";
340 public function from() {
342 FROM civicrm_membership {$this->_aliases['civicrm_membership']}
344 LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON ( {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contact']}.id )
346 LEFT JOIN civicrm_membership_status
347 ON ({$this->_aliases['civicrm_membership']}.status_id = civicrm_membership_status.id )
348 LEFT JOIN civicrm_membership_payment payment
349 ON ( {$this->_aliases['civicrm_membership']}.id = payment.membership_id )
350 LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
351 ON payment.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
354 public function where() {
355 $this->_whereClauses
[] = "{$this->_aliases['civicrm_membership']}.is_test = 0 AND
356 {$this->_aliases['civicrm_contact']}.is_deleted = 0";
360 public function groupBy() {
361 $this->_groupBy
= "";
362 if (is_array($this->_params
['group_bys']) &&
363 !empty($this->_params
['group_bys'])
365 foreach ($this->_columns
as $table) {
366 if (array_key_exists('group_bys', $table)) {
367 foreach ($table['group_bys'] as $fieldName => $field) {
368 if (!empty($this->_params
['group_bys'][$fieldName])) {
369 if (!empty($field['chart'])) {
370 $this->assign('chartSupported', TRUE);
372 if (!empty($table['group_bys'][$fieldName]['frequency']) &&
373 !empty($this->_params
['group_bys_freq'][$fieldName])
376 $append = "YEAR({$field['dbAlias']})";
377 if (in_array(strtolower($this->_params
['group_bys_freq'][$fieldName]),
382 $this->_groupByArray
[] = $append;
383 $this->_groupByArray
[] = "{$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
387 $this->_groupByArray
[] = $field['dbAlias'];
394 $this->_rollup
= ' WITH ROLLUP';
395 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($this->_selectClauses
, array_filter($this->_groupByArray
));
396 $this->_groupBy
= 'GROUP BY ' . implode(', ', array_filter($this->_groupByArray
)) .
397 " {$this->_rollup} ";
400 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_membership']}.join_date");
409 public function statistics(&$rows) {
410 $statistics = parent
::statistics($rows);
412 SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
413 IFNULL(SUM({$this->_aliases['civicrm_contribution']}.total_amount ), 0) as amount,
414 IFNULL(ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2),0) as avg,
415 COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as memberCount,
416 {$this->_aliases['civicrm_contribution']}.currency as currency
419 $sql = "{$select} {$this->_from} {$this->_where}
420 GROUP BY {$this->_aliases['civicrm_contribution']}.currency
423 $dao = CRM_Core_DAO
::executeQuery($sql);
425 $totalAmount = $average = array();
426 $count = $memberCount = 0;
427 while ($dao->fetch()) {
428 $totalAmount[] = CRM_Utils_Money
::format($dao->amount
, $dao->currency
) . "(" . $dao->count
. ")";
429 $average[] = CRM_Utils_Money
::format($dao->avg
, $dao->currency
);
430 $count +
= $dao->count
;
431 $memberCount +
= $dao->memberCount
;
433 $statistics['counts']['amount'] = array(
434 'title' => ts('Total Amount'),
435 'value' => implode(', ', $totalAmount),
436 'type' => CRM_Utils_Type
::T_STRING
,
438 $statistics['counts']['count'] = array(
439 'title' => ts('Total Contributions'),
442 $statistics['counts']['memberCount'] = array(
443 'title' => ts('Total Members'),
444 'value' => $memberCount,
446 $statistics['counts']['avg'] = array(
447 'title' => ts('Average'),
448 'value' => implode(', ', $average),
449 'type' => CRM_Utils_Type
::T_STRING
,
452 if (!(int) $statistics['counts']['amount']['value']) {
453 //if total amount is zero then hide Chart Options
454 $this->assign('chartSupported', FALSE);
460 public function postProcess() {
461 parent
::postProcess();
467 public function buildChart(&$rows) {
468 $graphRows = array();
470 $membershipTypeValues = CRM_Member_PseudoConstant
::membershipType();
471 $isMembershipType = CRM_Utils_Array
::value('membership_type_id', $this->_params
['group_bys']);
472 $isJoiningDate = CRM_Utils_Array
::value('join_date', $this->_params
['group_bys']);
473 if (!empty($this->_params
['charts'])) {
474 foreach ($rows as $key => $row) {
475 if (!($row['civicrm_membership_join_date_subtotal'] &&
476 $row['civicrm_membership_membership_type_id']
481 if ($isMembershipType) {
482 $join_date = CRM_Utils_Array
::value('civicrm_membership_join_date_start', $row);
483 $displayInterval = CRM_Utils_Array
::value('civicrm_membership_join_date_interval', $row);
485 list($year, $month) = explode('-', $join_date);
487 if (!empty($row['civicrm_membership_join_date_subtotal'])) {
489 switch ($this->_interval
) {
491 $displayRange = $displayInterval . ' ' . $year;
495 $displayRange = 'Quarter ' . $displayInterval . ' of ' . $year;
499 $displayRange = 'Week ' . $displayInterval . ' of ' . $year;
503 $displayRange = $year;
506 $membershipType = $displayRange . "-" .
507 $membershipTypeValues[$row['civicrm_membership_membership_type_id']];
511 $membershipType = $membershipTypeValues[$row['civicrm_membership_membership_type_id']];
514 $interval[$membershipType] = $membershipType;
515 $display[$membershipType] = $row['civicrm_contribution_total_amount_sum'];
518 $graphRows['receive_date'][] = CRM_Utils_Array
::value('civicrm_membership_join_date_start', $row);
519 $graphRows[$this->_interval
][] = CRM_Utils_Array
::value('civicrm_membership_join_date_interval', $row);
520 $graphRows['value'][] = $row['civicrm_contribution_total_amount_sum'];
526 if ($isMembershipType) {
527 $graphRows['value'] = $display;
529 'legend' => ts('Membership Summary'),
530 'xname' => ts('Member Since / Member Type'),
531 'yname' => ts('Fees'),
533 CRM_Utils_OpenFlashChart
::reportChart($graphRows, $this->_params
['charts'], $interval, $chartInfo);
536 CRM_Utils_OpenFlashChart
::chart($graphRows, $this->_params
['charts'], $this->_interval
);
539 $this->assign('chartType', $this->_params
['charts']);
543 * Alter display of rows.
545 * Iterate through the rows retrieved via SQL and make changes for display purposes,
546 * such as rendering contacts as links.
549 * Rows generated by SQL, with an array for each row.
551 public function alterDisplay(&$rows) {
553 foreach ($rows as $rowNum => $row) {
554 // make count columns point to detail report
555 if (!empty($this->_params
['group_bys']['join_date']) &&
556 !empty($row['civicrm_membership_join_date_start']) &&
557 $row['civicrm_membership_join_date_start'] &&
558 $row['civicrm_membership_join_date_subtotal']
561 $dateStart = CRM_Utils_Date
::customFormat($row['civicrm_membership_join_date_start'], '%Y%m%d');
562 $endDate = new DateTime($dateStart);
565 list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
567 switch (strtolower($this->_params
['group_bys_freq']['join_date'])) {
569 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
1,
570 $dateEnd['d'] - 1, $dateEnd['Y']
575 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
576 $dateEnd['d'] - 1, $dateEnd['Y'] +
1
581 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
582 $dateEnd['d'] +
6, $dateEnd['Y']
587 $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] +
3,
588 $dateEnd['d'] - 1, $dateEnd['Y']
593 if (!empty($this->_params
['group_bys']['membership_type_id']) &&
594 $typeID = $row['civicrm_membership_membership_type_id']
596 $typeUrl = "&tid_op=in&tid_value={$typeID}";
599 if (!empty($this->_params
['status_id_value'])) {
600 $statusUrl = "&sid_op=in&sid_value=" .
601 implode(",", $this->_params
['status_id_value']);
603 $url = CRM_Report_Utils_Report
::getNextUrl('member/detail',
604 "reset=1&force=1&join_date_from={$dateStart}&join_date_to={$dateEnd}{$typeUrl}{$statusUrl}",
605 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
607 $row['civicrm_membership_join_date_start'] = CRM_Utils_Date
::format($row['civicrm_membership_join_date_start']);
608 $rows[$rowNum]['civicrm_membership_join_date_start_link'] = $url;
609 $rows[$rowNum]['civicrm_membership_join_date_start_hover'] = ts("Lists Summary of Memberships for this date unit.");
614 // handle Membership Types
615 if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
616 if ($value = $row['civicrm_membership_membership_type_id']) {
617 $value = explode(',', $value);
618 foreach ($value as $key => $id) {
619 $value[$key] = CRM_Member_PseudoConstant
::membershipType($id, FALSE);
621 $rows[$rowNum]['civicrm_membership_membership_type_id'] = implode(' , ', $value);
626 // make subtotals look nicer
627 if (array_key_exists('civicrm_membership_join_date_subtotal', $row) &&
628 !$row['civicrm_membership_join_date_subtotal']
630 $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields
);
633 elseif (array_key_exists('civicrm_membership_join_date_subtotal', $row) &&
634 $row['civicrm_membership_join_date_subtotal'] &&
635 !$row['civicrm_membership_membership_type_id']
637 $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields
, FALSE);
638 $rows[$rowNum]['civicrm_membership_membership_type_id'] = '<b>' . ts('Subtotal') . '</b>';
642 // If using campaigns, convert campaign_id to campaign title
643 if (array_key_exists('civicrm_membership_campaign_id', $row)) {
644 if ($value = $row['civicrm_membership_campaign_id']) {
645 $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->activeCampaigns
[$value];
650 // skip looking further in rows, if first row itself doesn't
651 // have the column we need