Commit | Line | Data |
---|---|---|
c1413e28 DG |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
39de6fd5 | 4 | | CiviCRM version 4.6 | |
c1413e28 | 5 | +--------------------------------------------------------------------+ |
e7112fa7 | 6 | | Copyright CiviCRM LLC (c) 2004-2015 | |
c1413e28 DG |
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 | */ |
c1413e28 DG |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
e7112fa7 | 31 | * @copyright CiviCRM LLC (c) 2004-2015 |
c1413e28 DG |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { | |
36 | ||
ced9bfed EM |
37 | /** |
38 | * Class constructor. | |
39 | */ | |
00be9182 | 40 | public function __construct() { |
c1413e28 DG |
41 | $this->_columns = array( |
42 | 'civicrm_contact' => array( | |
43 | 'dao' => 'CRM_Contact_DAO_Contact', | |
44 | 'order_bys' => array( | |
45 | 'sort_name' => array( | |
46 | 'title' => ts("Last name, First name"), | |
47 | ), | |
48 | ), | |
49 | 'fields' => array( | |
50 | 'sort_name' => array( | |
51 | 'title' => ts('Contact Name'), | |
52 | 'no_repeat' => TRUE, | |
53 | 'default' => TRUE, | |
54 | ), | |
55 | 'id' => array( | |
56 | 'no_display' => TRUE, | |
57 | 'required' => TRUE, | |
58 | ), | |
59 | ), | |
60 | ), | |
61 | 'civicrm_email' => array( | |
62 | 'dao' => 'CRM_Core_DAO_Email', | |
63 | 'fields' => array( | |
64 | 'email' => array( | |
65 | 'title' => ts('Email'), | |
66 | 'no_repeat' => TRUE, | |
67 | ), | |
68 | ), | |
69 | 'grouping' => 'contact-fields', | |
70 | ), | |
71 | 'civicrm_phone' => array( | |
72 | 'dao' => 'CRM_Core_DAO_Phone', | |
73 | 'fields' => array( | |
74 | 'phone' => array( | |
75 | 'title' => ts('Phone'), | |
76 | 'no_repeat' => TRUE, | |
77 | ), | |
78 | ), | |
79 | 'grouping' => 'contact-fields', | |
80 | ), | |
81 | 'civicrm_contribution' => array( | |
82 | 'dao' => 'CRM_Contribute_DAO_Contribution', | |
83 | 'fields' => array( | |
84 | 'id' => array( | |
85 | 'no_display' => TRUE, | |
86 | 'required' => TRUE, | |
87 | ), | |
88 | 'total_amount' => array( | |
89 | 'title' => ts('Amount Contributed to Date'), | |
90 | 'statistics' => array( | |
21dfd5f5 | 91 | 'sum' => ts("Total Amount Contributed"), |
c1413e28 DG |
92 | ), |
93 | ), | |
94 | ), | |
95 | ), | |
96 | 'civicrm_contribution_recur' => array( | |
97 | 'dao' => 'CRM_Contribute_DAO_ContributionRecur', | |
98 | 'fields' => array( | |
99 | 'id' => array( | |
100 | 'no_display' => TRUE, | |
101 | 'required' => TRUE, | |
102 | ), | |
103 | 'currency' => array( | |
104 | 'title' => ts("Currency"), | |
105 | 'required' => TRUE, | |
106 | 'no_display' => TRUE, | |
107 | ), | |
c1413e28 | 108 | 'contribution_status_id' => array( |
be205937 | 109 | 'title' => ts('Contribution Status'), |
c1413e28 DG |
110 | ), |
111 | 'frequency_interval' => array( | |
112 | 'title' => ts('Frequency interval'), | |
113 | 'default' => TRUE, | |
114 | ), | |
115 | 'frequency_unit' => array( | |
116 | 'title' => ts('Frequency unit'), | |
117 | 'default' => TRUE, | |
118 | ), | |
2006e4c4 | 119 | 'amount' => array( |
979f1561 | 120 | 'title' => ts('Installment Amount'), |
2006e4c4 | 121 | 'default' => TRUE, |
979f1561 | 122 | ), |
c1413e28 DG |
123 | 'installments' => array( |
124 | 'title' => ts('Installments'), | |
125 | 'default' => TRUE, | |
126 | ), | |
127 | 'start_date' => array( | |
128 | 'title' => ts('Start Date'), | |
129 | ), | |
130 | 'create_date' => array( | |
131 | 'title' => ts('Create Date'), | |
132 | ), | |
133 | 'modified_date' => array( | |
134 | 'title' => ts('Modified Date'), | |
135 | ), | |
136 | 'cancel_date' => array( | |
137 | 'title' => ts('Cancel Date'), | |
138 | ), | |
139 | 'end_date' => array( | |
140 | 'title' => ts('End Date'), | |
141 | ), | |
142 | 'next_sched_contribution_date' => array( | |
143 | 'title' => ts('Next Scheduled Contribution Date'), | |
144 | ), | |
145 | 'failure_count' => array( | |
146 | 'title' => ts('Failure Count'), | |
147 | ), | |
148 | 'failure_retry_date' => array( | |
149 | 'title' => ts('Failure Retry Date'), | |
150 | ), | |
151 | ), | |
152 | 'filters' => array( | |
153 | 'contribution_status_id' => array( | |
be205937 | 154 | 'title' => ts('Contribution Status'), |
c1413e28 DG |
155 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
156 | 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), | |
157 | 'default' => array(5), | |
158 | 'type' => CRM_Utils_Type::T_INT, | |
159 | ), | |
160 | 'currency' => array( | |
161 | 'title' => 'Currency', | |
162 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
163 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
164 | 'default' => NULL, | |
165 | 'type' => CRM_Utils_Type::T_STRING, | |
166 | ), | |
167 | 'financial_type_id' => array( | |
168 | 'title' => ts('Financial Type'), | |
169 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
353ffa53 | 170 | 'options' => CRM_Contribute_PseudoConstant::financialType(), |
c1413e28 DG |
171 | 'type' => CRM_Utils_Type::T_INT, |
172 | ), | |
173 | 'frequency_unit' => array( | |
174 | 'title' => ts('Frequency Unit'), | |
175 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
84178120 | 176 | 'options' => CRM_Core_OptionGroup::values('recur_frequency_units'), |
c1413e28 DG |
177 | 'type' => CRM_Utils_Type::T_INT, |
178 | ), | |
179 | 'frequency_interval' => array( | |
180 | 'title' => ts('Frequency Interval'), | |
181 | 'type' => CRM_Utils_Type::T_INT, | |
182 | ), | |
2006e4c4 | 183 | 'amount' => array( |
184 | 'title' => ts('Installment Amount'), | |
185 | 'type' => CRM_Utils_Type::T_MONEY, | |
186 | ), | |
c1413e28 DG |
187 | 'installments' => array( |
188 | 'title' => ts('Installments'), | |
189 | 'type' => CRM_Utils_Type::T_INT, | |
190 | ), | |
191 | 'start_date' => array( | |
192 | 'title' => ts('Start Date'), | |
193 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
194 | 'type' => CRM_Utils_Type::T_TIME, | |
195 | ), | |
196 | 'next_sched_contribution_date' => array( | |
197 | 'title' => ts('Next Scheduled Contribution Date'), | |
198 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
199 | 'type' => CRM_Utils_Type::T_TIME, | |
200 | ), | |
201 | 'end_date' => array( | |
202 | 'title' => ts('End Date'), | |
203 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
204 | 'type' => CRM_Utils_Type::T_TIME, | |
205 | ), | |
979f1561 | 206 | 'contribution_processed_date' => array( |
207 | 'title' => ts('Last Contribution Processed'), | |
208 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
209 | 'type' => CRM_Utils_Type::T_TIME, | |
210 | ), | |
c1413e28 DG |
211 | 'calculated_end_date' => array( |
212 | 'title' => ts('Calculated end date (either end date or date all installments will be made)'), | |
213 | 'description' => "does this work?", | |
214 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
21dfd5f5 | 215 | 'pseudofield' => TRUE, |
c1413e28 DG |
216 | ), |
217 | ), | |
21dfd5f5 | 218 | ), |
c1413e28 DG |
219 | ); |
220 | $this->_currencyColumn = 'civicrm_contribution_recur_currency'; | |
979f1561 | 221 | $this->_groupFilter = TRUE; |
c1413e28 DG |
222 | parent::__construct(); |
223 | } | |
353ffa53 | 224 | |
ced9bfed EM |
225 | /** |
226 | * Get template file name. | |
227 | * | |
228 | * @return string | |
229 | */ | |
00be9182 | 230 | public function getTemplateName() { |
84178120 | 231 | return 'CRM/Report/Form.tpl'; |
c1413e28 DG |
232 | } |
233 | ||
ced9bfed EM |
234 | /** |
235 | * Generate FROM SQL clause. | |
236 | */ | |
00be9182 | 237 | public function from() { |
c1413e28 DG |
238 | $this->_from = " |
239 | FROM civicrm_contact {$this->_aliases['civicrm_contact']} | |
240 | INNER JOIN civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']} | |
241 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id"; | |
242 | $this->_from .= " | |
243 | LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
244 | ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id"; | |
245 | $this->_from .= " | |
246 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} | |
14e99c8f KG |
247 | ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND |
248 | {$this->_aliases['civicrm_email']}.is_primary = 1)"; | |
c1413e28 DG |
249 | $this->_from .= " |
250 | LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} | |
251 | ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND | |
14e99c8f | 252 | {$this->_aliases['civicrm_phone']}.is_primary = 1)"; |
c1413e28 DG |
253 | } |
254 | ||
00be9182 | 255 | public function groupBy() { |
c1413e28 DG |
256 | $this->_groupBy = "GROUP BY " . $this->_aliases['civicrm_contribution_recur'] . ".id"; |
257 | } | |
258 | ||
00be9182 | 259 | public function where() { |
c1413e28 DG |
260 | parent::where(); |
261 | // Handle calculated end date. This can come from one of two sources: | |
262 | // Either there is a specified end date for the end_date field | |
263 | // Or, we calculate the end date based on the start date + | |
264 | // installments * intervals using the mysql date_add function, along | |
265 | // with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH) | |
266 | $date_suffixes = array('relative', 'from', 'to'); | |
22e263ad | 267 | while (list(, $suffix) = each($date_suffixes)) { |
979f1561 | 268 | $isBreak = FALSE; |
c1413e28 | 269 | // Check to see if the user wants to search by calculated date. |
22e263ad | 270 | if (!empty($this->_params['calculated_end_date_' . $suffix])) { |
c1413e28 DG |
271 | // The calculated date field is in use - spring into action |
272 | // Gather values | |
273 | $relative = CRM_Utils_Array::value("calculated_end_date_relative", $this->_params); | |
274 | $from = CRM_Utils_Array::value("calculated_end_date_from", $this->_params); | |
275 | $to = CRM_Utils_Array::value("calculated_end_date_to", $this->_params); | |
276 | $end_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['dbAlias']; | |
277 | $end_date_type = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['type']; | |
278 | $start_date_type = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['type']; | |
279 | $frequency_unit_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias']; | |
280 | $frequency_interval_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias']; | |
281 | $installments_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['installments']['dbAlias']; | |
282 | $start_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['dbAlias']; | |
283 | ||
284 | // The end date clause is simple to construct | |
353ffa53 | 285 | $end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL); |
c1413e28 DG |
286 | |
287 | // NOTE: For the calculation based on installment, there doesn't | |
288 | // seem to be a way to include the interval unit (e.g. month, | |
289 | // date, etc) as a field name - so we have to build a complex | |
290 | // OR statement instead. | |
291 | ||
979f1561 | 292 | $this->_where .= 'AND (' . |
c1413e28 | 293 | $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)", |
353ffa53 | 294 | $relative, $from, $to, $start_date_type, NULL, NULL); |
979f1561 | 295 | $this->_where .= " AND $frequency_unit_db_alias = 'month' ) OR \n"; |
c1413e28 | 296 | |
979f1561 | 297 | $this->_where .= '(' . |
c1413e28 | 298 | $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)", |
353ffa53 | 299 | $relative, $from, $to, $start_date_type, NULL, NULL); |
979f1561 | 300 | $this->_where .= " AND $frequency_unit_db_alias = 'day' ) OR \n"; |
c1413e28 | 301 | |
979f1561 | 302 | $this->_where .= '(' . |
c1413e28 | 303 | $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)", |
353ffa53 | 304 | $relative, $from, $to, $start_date_type, NULL, NULL); |
cc3360ca | 305 | $this->_where .= " AND $frequency_unit_db_alias = 'week' ) OR \n"; |
c1413e28 | 306 | |
979f1561 | 307 | $this->_where .= '(' . |
c1413e28 | 308 | $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)", |
353ffa53 | 309 | $relative, $from, $to, $start_date_type, NULL, NULL); |
979f1561 | 310 | $this->_where .= " AND $frequency_unit_db_alias = 'year' ) |
311 | AND (($end_date_db_alias IS NOT NULL AND $end_date_clause) | |
312 | OR ($installments_db_alias IS NOT NULL)) | |
313 | "; | |
314 | $isBreak = TRUE; | |
315 | } | |
316 | if (!empty($this->_params['contribution_processed_date_' . $suffix])) { | |
317 | $relative = CRM_Utils_Array::value("contribution_processed_date_relative", $this->_params); | |
318 | $from = CRM_Utils_Array::value("contribution_processed_date_from", $this->_params); | |
319 | $to = CRM_Utils_Array::value("contribution_processed_date_to", $this->_params); | |
320 | $this->_where .= " AND contribution_civireport.id IN (SELECT MAX(cc.id) FROM civicrm_contribution cc WHERE cc.contribution_status_id = 1 AND (" . | |
321 | $this->dateClause("DATE(cc.receive_date)", | |
322 | $relative, $from, $to, CRM_Utils_Type::T_DATE, NULL, NULL) . " ) GROUP BY cc.contribution_recur_id)"; | |
323 | $isBreak = TRUE; | |
324 | } | |
325 | if (!empty($isBreak)) { | |
c1413e28 DG |
326 | break; |
327 | } | |
328 | } | |
329 | } | |
330 | ||
331 | ||
ced9bfed EM |
332 | /** |
333 | * Alter display of rows. | |
334 | * | |
335 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
336 | * such as rendering contacts as links. | |
337 | * | |
338 | * @param array $rows | |
339 | * Rows generated by SQL, with an array for each row. | |
340 | */ | |
00be9182 | 341 | public function alterDisplay(&$rows) { |
c1413e28 DG |
342 | $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(); |
343 | foreach ($rows as $rowNum => $row) { | |
344 | // convert display name to links | |
345 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
346 | CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) && | |
347 | array_key_exists('civicrm_contact_id', $row) | |
348 | ) { | |
349 | $url = CRM_Utils_System::url('civicrm/contact/view', | |
350 | 'reset=1&cid=' . $row['civicrm_contact_id'], | |
351 | $this->_absoluteUrl | |
352 | ); | |
353 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
354 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.'); | |
355 | } | |
356 | ||
357 | // handle contribution status id | |
358 | if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_contribution_status_id', $row)) { | |
359 | $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value]; | |
360 | } | |
84178120 | 361 | |
c1413e28 | 362 | if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_amount', $row)) { |
84178120 | 363 | $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']); |
44fd0292 | 364 | } |
c1413e28 DG |
365 | } |
366 | } | |
96025800 | 367 | |
ef10e0b5 | 368 | } |