Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
8c9251b3 | 6 | | Copyright CiviCRM LLC (c) 2004-2018 | |
6a488035 TO |
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 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
8c9251b3 | 31 | * @copyright CiviCRM LLC (c) 2004-2018 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Report_Form_Member_Lapse extends CRM_Report_Form { | |
36 | ||
37 | protected $_summary = NULL; | |
6a488035 TO |
38 | protected $_charts = array('' => 'Tabular'); |
39 | protected $_customGroupExtends = array( | |
21dfd5f5 | 40 | 'Membership', |
9d72cede | 41 | ); |
6a488035 | 42 | public $_drilldownReport = array('member/detail' => 'Link to Detail Report'); |
f813f78e | 43 | |
74cf4551 | 44 | /** |
1728e9a0 | 45 | * This report has not been optimised for group filtering. |
46 | * | |
47 | * The functionality for group filtering has been improved but not | |
48 | * all reports have been adjusted to take care of it. This report has not | |
49 | * and will run an inefficient query until fixed. | |
50 | * | |
51 | * CRM-19170 | |
52 | * | |
53 | * @var bool | |
74cf4551 | 54 | */ |
1728e9a0 | 55 | protected $groupFilterNotOptimised = TRUE; |
56 | ||
74cf4551 | 57 | /** |
73b448bf | 58 | * Class constructor. |
74cf4551 | 59 | */ |
00be9182 | 60 | public function __construct() { |
d62fab33 RN |
61 | |
62 | // Check if CiviCampaign is a) enabled and b) has active campaigns | |
63 | $config = CRM_Core_Config::singleton(); | |
64 | $campaignEnabled = in_array("CiviCampaign", $config->enableComponents); | |
65 | if ($campaignEnabled) { | |
66 | $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE); | |
67 | $this->activeCampaigns = $getCampaigns['campaigns']; | |
68 | asort($this->activeCampaigns); | |
f813f78e | 69 | } |
d62fab33 | 70 | |
6a488035 | 71 | // UI for selecting columns to appear in the report list |
9d72cede | 72 | // array containing the columns, group_bys and filters build and provided to Form |
6a488035 | 73 | $this->_columns = array( |
9d72cede | 74 | 'civicrm_contact' => array( |
6a488035 | 75 | 'dao' => 'CRM_Contact_DAO_Contact', |
9d72cede EM |
76 | 'fields' => array( |
77 | 'sort_name' => array( | |
78 | 'title' => ts('Member Name'), | |
6a488035 TO |
79 | 'no_repeat' => TRUE, |
80 | 'required' => TRUE, | |
81 | ), | |
9d72cede | 82 | 'id' => array( |
6a488035 TO |
83 | 'no_display' => TRUE, |
84 | 'required' => TRUE, | |
85 | ), | |
9d72cede EM |
86 | 'first_name' => array( |
87 | 'title' => ts('First Name'), | |
6a488035 TO |
88 | 'no_repeat' => TRUE, |
89 | ), | |
9d72cede EM |
90 | 'last_name' => array( |
91 | 'title' => ts('Last Name'), | |
6a488035 TO |
92 | 'no_repeat' => TRUE, |
93 | ), | |
9d72cede | 94 | 'contact_type' => array( |
30f85891 RN |
95 | 'title' => ts('Contact Type'), |
96 | ), | |
9d72cede | 97 | 'contact_sub_type' => array( |
b8f96eb8 | 98 | 'title' => ts('Contact Subtype'), |
30f85891 | 99 | ), |
6a488035 TO |
100 | ), |
101 | 'grouping' => 'contact-fields', | |
102 | ), | |
9d72cede | 103 | 'civicrm_membership_type' => array( |
6a488035 TO |
104 | 'dao' => 'CRM_Member_DAO_MembershipType', |
105 | 'grouping' => 'member-fields', | |
9d72cede EM |
106 | 'filters' => array( |
107 | 'tid' => array( | |
6a488035 TO |
108 | 'name' => 'id', |
109 | 'title' => ts('Membership Types'), | |
8ee006e7 | 110 | 'type' => CRM_Utils_Type::T_INT, |
6a488035 TO |
111 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
112 | 'options' => CRM_Member_PseudoConstant::membershipType(), | |
113 | ), | |
114 | ), | |
115 | ), | |
9d72cede | 116 | 'civicrm_membership' => array( |
6a488035 TO |
117 | 'dao' => 'CRM_Member_DAO_Membership', |
118 | 'grouping' => 'member-fields', | |
9d72cede EM |
119 | 'fields' => array( |
120 | 'membership_type_id' => array( | |
fd6a6828 | 121 | 'title' => ts('Membership Type'), |
6a488035 TO |
122 | 'required' => TRUE, |
123 | 'type' => CRM_Utils_Type::T_STRING, | |
124 | ), | |
9d72cede EM |
125 | 'membership_start_date' => array( |
126 | 'title' => ts('Current Cycle Start Date'), | |
6a488035 | 127 | ), |
9d72cede EM |
128 | 'membership_end_date' => array( |
129 | 'title' => ts('Membership Lapse Date'), | |
6a488035 TO |
130 | 'required' => TRUE, |
131 | ), | |
132 | ), | |
9d72cede EM |
133 | 'filters' => array( |
134 | 'membership_end_date' => array( | |
fd6a6828 | 135 | 'title' => ts('Lapsed Memberships'), |
6a488035 TO |
136 | 'operatorType' => CRM_Report_Form::OP_DATE, |
137 | ), | |
138 | ), | |
139 | ), | |
9d72cede | 140 | 'civicrm_membership_status' => array( |
6a488035 TO |
141 | 'dao' => 'CRM_Member_DAO_MembershipStatus', |
142 | 'alias' => 'mem_status', | |
9d72cede EM |
143 | 'fields' => array( |
144 | 'name' => array( | |
145 | 'title' => ts('Current Status'), | |
6a488035 TO |
146 | 'required' => TRUE, |
147 | ), | |
148 | ), | |
149 | 'grouping' => 'member-fields', | |
150 | ), | |
9d72cede | 151 | 'civicrm_address' => array( |
6a488035 | 152 | 'dao' => 'CRM_Core_DAO_Address', |
9d72cede | 153 | 'fields' => array( |
6a488035 TO |
154 | 'street_address' => NULL, |
155 | 'city' => NULL, | |
156 | 'postal_code' => NULL, | |
9d72cede EM |
157 | 'state_province_id' => array( |
158 | 'title' => ts('State/Province'), | |
6a488035 | 159 | ), |
9d72cede EM |
160 | 'country_id' => array( |
161 | 'title' => ts('Country'), | |
6a488035 TO |
162 | 'default' => TRUE, |
163 | ), | |
164 | ), | |
165 | 'grouping' => 'contact-fields', | |
166 | ), | |
9d72cede | 167 | 'civicrm_phone' => array( |
6a488035 TO |
168 | 'dao' => 'CRM_Core_DAO_Phone', |
169 | 'alias' => 'phone', | |
9d72cede | 170 | 'fields' => array('phone' => NULL), |
6a488035 TO |
171 | 'grouping' => 'contact-fields', |
172 | ), | |
9d72cede | 173 | 'civicrm_email' => array( |
6a488035 | 174 | 'dao' => 'CRM_Core_DAO_Email', |
9d72cede | 175 | 'fields' => array('email' => NULL), |
6a488035 TO |
176 | 'grouping' => 'contact-fields', |
177 | ), | |
6a488035 TO |
178 | ); |
179 | ||
d62fab33 RN |
180 | // If we have a campaign, build out the relevant elements |
181 | if ($campaignEnabled && !empty($this->activeCampaigns)) { | |
182 | $this->_columns['civicrm_membership']['fields']['campaign_id'] = array( | |
fd6a6828 | 183 | 'title' => ts('Campaign'), |
d62fab33 RN |
184 | 'default' => 'false', |
185 | ); | |
9d72cede EM |
186 | $this->_columns['civicrm_membership']['filters']['campaign_id'] = array( |
187 | 'title' => ts('Campaign'), | |
d62fab33 RN |
188 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
189 | 'options' => $this->activeCampaigns, | |
d5c051d3 | 190 | 'type' => CRM_Utils_Type::T_INT, |
d62fab33 | 191 | ); |
f813f78e | 192 | } |
d62fab33 | 193 | |
16e2e80c | 194 | $this->_groupFilter = TRUE; |
6a488035 TO |
195 | $this->_tagFilter = TRUE; |
196 | parent::__construct(); | |
197 | } | |
198 | ||
00be9182 | 199 | public function preProcess() { |
6a488035 TO |
200 | parent::preProcess(); |
201 | } | |
202 | ||
00be9182 | 203 | public function select() { |
6a488035 TO |
204 | $select = array(); |
205 | $this->_columnHeaders = array(); | |
206 | foreach ($this->_columns as $tableName => $table) { | |
207 | if (array_key_exists('fields', $table)) { | |
208 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
209 | if (!empty($field['required']) || |
210 | !empty($this->_params['fields'][$fieldName]) | |
211 | ) { | |
6a488035 TO |
212 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; |
213 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field); | |
214 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title']; | |
215 | } | |
216 | } | |
217 | } | |
218 | } | |
219 | $this->_select = "SELECT " . implode(', ', $select) . " "; | |
220 | } | |
221 | ||
74cf4551 EM |
222 | /** |
223 | * @param $fields | |
224 | * @param $files | |
225 | * @param $self | |
226 | * | |
227 | * @return array | |
228 | */ | |
00be9182 | 229 | public static function formRule($fields, $files, $self) { |
6a488035 TO |
230 | $errors = $grouping = array(); |
231 | //check for searching combination of dispaly columns and | |
232 | //grouping criteria | |
233 | ||
234 | return $errors; | |
235 | } | |
236 | ||
00be9182 | 237 | public function from() { |
6a488035 TO |
238 | $this->_from = " |
239 | FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} | |
f813f78e | 240 | INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']} |
241 | ON {$this->_aliases['civicrm_contact']}.id = | |
6a488035 TO |
242 | {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0 |
243 | LEFT JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']} | |
f813f78e | 244 | ON {$this->_aliases['civicrm_membership_status']}.id = |
6a488035 | 245 | {$this->_aliases['civicrm_membership']}.status_id |
f813f78e | 246 | LEFT JOIN civicrm_membership_type {$this->_aliases['civicrm_membership_type']} |
6a488035 TO |
247 | ON {$this->_aliases['civicrm_membership']}.membership_type_id = |
248 | {$this->_aliases['civicrm_membership_type']}.id"; | |
249 | ||
183af103 | 250 | $this->joinAddressFromContact(); |
251 | $this->joinPhoneFromContact(); | |
252 | $this->joinEmailFromContact(); | |
6a488035 TO |
253 | } |
254 | ||
00be9182 | 255 | public function where() { |
6a488035 TO |
256 | $clauses = array(); |
257 | foreach ($this->_columns as $tableName => $table) { | |
258 | if (array_key_exists('filters', $table)) { | |
259 | foreach ($table['filters'] as $fieldName => $field) { | |
260 | $clause = NULL; | |
261 | ||
262 | if ($field['operatorType'] & CRM_Utils_Type::T_DATE) { | |
263 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); | |
9d72cede EM |
264 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); |
265 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); | |
6a488035 TO |
266 | |
267 | if ($relative || $from || $to) { | |
268 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
269 | } | |
270 | } | |
271 | else { | |
272 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); | |
273 | if ($op) { | |
274 | $clause = $this->whereClause($field, | |
275 | $op, | |
276 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
277 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
278 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
279 | ); | |
280 | } | |
281 | } | |
282 | if (!empty($clause)) { | |
283 | $clauses[$fieldName] = $clause; | |
284 | } | |
285 | } | |
286 | } | |
287 | } | |
288 | ||
289 | if (empty($clauses)) { | |
9d72cede EM |
290 | $this->_where = "WHERE end_date < '" . date('Y-m-d') . |
291 | "' AND {$this->_aliases['civicrm_membership_status']}.name = 'Expired'"; | |
6a488035 TO |
292 | } |
293 | else { | |
294 | if (!array_key_exists('end_date', $clauses)) { | |
9d72cede EM |
295 | $this->_where = "WHERE end_date < '" . date('Y-m-d') . "' AND " . |
296 | implode(' AND ', $clauses); | |
6a488035 TO |
297 | } |
298 | else { | |
299 | $this->_where = "WHERE " . implode(' AND ', $clauses); | |
300 | } | |
301 | } | |
302 | ||
303 | if ($this->_aclWhere) { | |
304 | $this->_where .= " AND {$this->_aclWhere} "; | |
305 | } | |
306 | } | |
307 | ||
00be9182 | 308 | public function orderBy() { |
6a488035 TO |
309 | $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id"; |
310 | } | |
311 | ||
00be9182 | 312 | public function postProcess() { |
6a488035 TO |
313 | $this->beginPostProcess(); |
314 | ||
315 | // get the acl clauses built before we assemble the query | |
316 | $this->buildACLClause($this->_aliases['civicrm_contact']); | |
317 | $sql = $this->buildQuery(TRUE); | |
318 | ||
9d72cede EM |
319 | $dao = CRM_Core_DAO::executeQuery($sql); |
320 | $rows = $graphRows = array(); | |
6a488035 TO |
321 | $count = 0; |
322 | while ($dao->fetch()) { | |
323 | $row = array(); | |
324 | foreach ($this->_columnHeaders as $key => $value) { | |
325 | $row[$key] = $dao->$key; | |
326 | } | |
327 | ||
328 | $rows[] = $row; | |
329 | } | |
330 | $this->formatDisplay($rows); | |
331 | ||
332 | // assign variables to templates | |
333 | $this->doTemplateAssignment($rows); | |
334 | ||
335 | $this->endPostProcess($rows); | |
336 | } | |
337 | ||
74cf4551 | 338 | /** |
ced9bfed EM |
339 | * Alter display of rows. |
340 | * | |
341 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
342 | * such as rendering contacts as links. | |
343 | * | |
344 | * @param array $rows | |
345 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 346 | */ |
00be9182 | 347 | public function alterDisplay(&$rows) { |
6a488035 TO |
348 | $entryFound = FALSE; |
349 | $checkList = array(); | |
350 | ||
351 | foreach ($rows as $rowNum => $row) { | |
352 | ||
353 | if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') { | |
354 | // not repeat contact display names if it matches with the one | |
355 | // in previous row | |
356 | ||
357 | $repeatFound = FALSE; | |
358 | foreach ($row as $colName => $colVal) { | |
a7488080 | 359 | if (!empty($checkList[$colName]) && |
6a488035 TO |
360 | is_array($checkList[$colName]) && |
361 | in_array($colVal, $checkList[$colName]) | |
362 | ) { | |
363 | $rows[$rowNum][$colName] = ""; | |
364 | $repeatFound = TRUE; | |
365 | } | |
366 | if (in_array($colName, $this->_noRepeats)) { | |
367 | $checkList[$colName][] = $colVal; | |
368 | } | |
369 | } | |
370 | } | |
371 | ||
372 | //handle the Membership Type Ids | |
373 | if (array_key_exists('civicrm_membership_membership_type_id', $row)) { | |
374 | if ($value = $row['civicrm_membership_membership_type_id']) { | |
375 | $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE); | |
376 | } | |
377 | $entryFound = TRUE; | |
378 | } | |
379 | ||
380 | // handle state province | |
381 | if (array_key_exists('civicrm_address_state_province_id', $row)) { | |
382 | if ($value = $row['civicrm_address_state_province_id']) { | |
383 | $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE); | |
384 | } | |
385 | $entryFound = TRUE; | |
386 | } | |
387 | ||
388 | // handle country | |
389 | if (array_key_exists('civicrm_address_country_id', $row)) { | |
390 | if ($value = $row['civicrm_address_country_id']) { | |
391 | $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE); | |
392 | } | |
393 | $entryFound = TRUE; | |
394 | } | |
395 | ||
396 | // convert display name to links | |
397 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
398 | array_key_exists('civicrm_contact_id', $row) | |
399 | ) { | |
400 | $url = CRM_Report_Utils_Report::getNextUrl('member/detail', | |
401 | 'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'], | |
402 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
403 | ); | |
404 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
405 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Membership Detail for this Contact."); | |
406 | } | |
407 | ||
d62fab33 RN |
408 | // If using campaigns, convert campaign_id to campaign title |
409 | if (array_key_exists('civicrm_membership_campaign_id', $row)) { | |
410 | if ($value = $row['civicrm_membership_campaign_id']) { | |
411 | $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->activeCampaigns[$value]; | |
412 | } | |
413 | $entryFound = TRUE; | |
414 | } | |
415 | ||
6a488035 TO |
416 | // skip looking further in rows, if first row itself doesn't |
417 | // have the column we need | |
418 | if (!$entryFound) { | |
419 | break; | |
420 | } | |
421 | } | |
422 | } | |
96025800 | 423 | |
6a488035 | 424 | } |