Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | // $Id$ | |
3 | ||
4 | /* | |
5 | +--------------------------------------------------------------------+ | |
6 | | CiviCRM version 4.3 | | |
7 | +--------------------------------------------------------------------+ | |
8 | | Copyright CiviCRM LLC (c) 2004-2013 | | |
9 | +--------------------------------------------------------------------+ | |
10 | | This file is a part of CiviCRM. | | |
11 | | | | |
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. | | |
15 | | | | |
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. | | |
20 | | | | |
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 | +--------------------------------------------------------------------+ | |
28 | */ | |
29 | ||
30 | /** | |
31 | * | |
32 | * @package CRM | |
33 | * @copyright CiviCRM LLC (c) 2004-2013 | |
34 | * $Id$ | |
35 | * | |
36 | */ | |
37 | class CRM_Report_Form_Pledge_Summary extends CRM_Report_Form { | |
38 | ||
39 | protected $_summary = NULL; | |
40 | protected $_totalPaid = FALSE; | |
41 | protected $_customGroupExtends = array('Pledge', 'Individual'); | |
42 | protected $_customGroupGroupBy = TRUE; | |
43 | protected $_addressField = FALSE; | |
44 | protected $_emailField = FALSE; | |
45 | ||
46 | function __construct() { | |
47 | $this->_columns = array( | |
48 | 'civicrm_contact' => | |
49 | array( | |
50 | 'dao' => 'CRM_Contact_DAO_Contact', | |
51 | 'fields' => | |
52 | array( | |
53 | 'sort_name' => | |
54 | array('title' => ts('Contact Name'), | |
55 | 'no_repeat' => TRUE, | |
56 | ), | |
57 | 'postal_greeting_display' => | |
58 | array('title' => ts('Postal Greeting')), | |
59 | 'id' => | |
60 | array( | |
61 | 'no_display' => TRUE, | |
62 | 'required' => TRUE, | |
63 | ), | |
64 | ), | |
65 | 'grouping' => 'contact-fields', | |
66 | 'group_bys' => | |
67 | array( | |
68 | 'id' => | |
69 | array('title' => ts('Contact ID')), | |
70 | 'sort_name' => | |
71 | array('title' => ts('Contact Name'), | |
72 | ), | |
73 | ), | |
74 | ), | |
75 | 'civicrm_email' => | |
76 | array( | |
77 | 'dao' => 'CRM_Core_DAO_Email', | |
78 | 'fields' => | |
79 | array( | |
80 | 'email' => | |
81 | array( | |
82 | 'no_repeat' => TRUE, | |
83 | 'title' => ts('email'), | |
84 | ), | |
85 | ), | |
86 | 'grouping' => 'contact-fields', | |
87 | ), | |
88 | 'civicrm_pledge' => | |
89 | array( | |
90 | 'dao' => 'CRM_Pledge_DAO_Pledge', | |
91 | 'fields' => | |
92 | array( | |
93 | 'id' => | |
94 | array( | |
95 | 'no_display' => TRUE, | |
96 | 'required' => FALSE, | |
97 | ), | |
9bf1940a | 98 | 'currency' => array( |
99 | 'required' => TRUE, | |
100 | 'no_display' => TRUE, | |
101 | ), | |
6a488035 TO |
102 | 'amount' => |
103 | array('title' => ts('Pledge Amount'), | |
104 | 'required' => TRUE, | |
105 | 'type' => CRM_Utils_Type::T_MONEY, | |
106 | 'statistics' => | |
107 | array('sum' => ts('Aggregate Amount Pledged'), | |
108 | 'count' => ts('Pledges'), | |
109 | 'avg' => ts('Average'), | |
110 | ), | |
111 | ), | |
112 | 'frequency_unit' => | |
113 | array('title' => ts('Frequency Unit'), | |
114 | ), | |
115 | 'installments' => | |
116 | array('title' => ts('Installments'), | |
117 | ), | |
118 | 'pledge_create_date' => | |
119 | array('title' => ts('Pledge Made Date'), | |
120 | ), | |
121 | 'start_date' => | |
122 | array('title' => ts('Pledge Start Date'), | |
123 | 'type' => CRM_Utils_Type::T_DATE, | |
124 | ), | |
125 | 'end_date' => | |
126 | array('title' => ts('Pledge End Date'), | |
127 | 'type' => CRM_Utils_Type::T_DATE, | |
128 | ), | |
129 | 'status_id' => | |
130 | array('title' => ts('Pledge Status'), | |
131 | ), | |
132 | ), | |
133 | 'filters' => | |
134 | array( | |
135 | 'pledge_create_date' => | |
136 | array( | |
137 | 'title' => 'Pledge Made Date', | |
138 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
139 | ), | |
140 | 'pledge_amount' => | |
141 | array('title' => ts('Pledged Amount'), | |
142 | 'operatorType' => CRM_Report_Form::OP_INT, | |
143 | ), | |
9bf1940a | 144 | 'currency' => |
145 | array('title' => 'Currency', | |
146 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
147 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
d1b0931b | 148 | 'default' => NULL, |
9bf1940a | 149 | 'type' => CRM_Utils_Type::T_STRING, |
150 | ), | |
6a488035 TO |
151 | 'sid' => |
152 | array( | |
153 | 'name' => 'status_id', | |
154 | 'title' => ts('Pledge Status'), | |
155 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
156 | 'options' => CRM_Core_OptionGroup::values('contribution_status'), | |
157 | ), | |
158 | ), | |
159 | 'group_bys' => | |
160 | array( | |
161 | 'pledge_create_date' => | |
162 | array( | |
163 | 'frequency' => TRUE, | |
164 | 'default' => TRUE, | |
165 | 'chart' => TRUE, | |
166 | ), | |
167 | 'frequency_unit' => | |
168 | array('title' => ts('Frequency Unit'), | |
169 | ), | |
170 | 'status_id' => | |
171 | array('title' => ts('Pledge Status'), | |
172 | ), | |
173 | ), | |
174 | ), | |
175 | 'civicrm_pledge_payment' => | |
176 | array( | |
177 | 'dao' => 'CRM_Pledge_DAO_PledgePayment', | |
178 | 'fields' => | |
179 | array( | |
180 | 'total_paid' => | |
181 | array( | |
182 | 'title' => ts('Total Amount Paid'), | |
9bf1940a | 183 | 'type' => CRM_Utils_Type::T_STRING, |
6a488035 TO |
184 | 'dbAlias' => 'sum(pledge_payment_civireport.actual_amount)', |
185 | ), | |
186 | ), | |
187 | ), | |
188 | 'civicrm_group' => | |
189 | array( | |
190 | 'dao' => 'CRM_Contact_DAO_Group', | |
191 | 'alias' => 'cgroup', | |
192 | 'filters' => | |
193 | array( | |
194 | 'gid' => | |
195 | array( | |
196 | 'name' => 'group_id', | |
197 | 'title' => ts(' Group'), | |
198 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
199 | 'group' => TRUE, | |
200 | 'options' => CRM_Core_PseudoConstant::group(), | |
201 | ), | |
202 | ), | |
203 | ), | |
204 | ) + $this->addAddressFields(); | |
205 | ||
206 | $this->_tagFilter = TRUE; | |
9bf1940a | 207 | $this->_currencyColumn = 'civicrm_pledge_currency'; |
6a488035 TO |
208 | parent::__construct(); |
209 | } | |
210 | ||
211 | function preProcess() { | |
212 | parent::preProcess(); | |
213 | } | |
214 | ||
215 | function select() { | |
216 | parent::select(); | |
217 | } | |
218 | ||
219 | function from() { | |
220 | $this->_from = " | |
221 | FROM civicrm_pledge {$this->_aliases['civicrm_pledge']} | |
222 | LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} | |
223 | ON ({$this->_aliases['civicrm_contact']}.id = | |
224 | {$this->_aliases['civicrm_pledge']}.contact_id ) | |
225 | {$this->_aclFrom} "; | |
226 | ||
227 | // include address field if address column is to be included | |
228 | if ($this->_addressField) { | |
229 | $this->_from .= " | |
230 | LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} | |
231 | ON ({$this->_aliases['civicrm_contact']}.id = | |
232 | {$this->_aliases['civicrm_address']}.contact_id) AND | |
233 | {$this->_aliases['civicrm_address']}.is_primary = 1\n"; | |
234 | } | |
235 | ||
236 | // include email field if email column is to be included | |
237 | if ($this->_emailField) { | |
238 | $this->_from .= " | |
239 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} | |
240 | ON ({$this->_aliases['civicrm_contact']}.id = | |
241 | {$this->_aliases['civicrm_email']}.contact_id) AND | |
242 | {$this->_aliases['civicrm_email']}.is_primary = 1\n"; | |
243 | } | |
244 | ||
245 | if(CRM_Utils_Array::value('total_paid', $this->_params['fields'])){ | |
246 | $this->_from .= " | |
247 | LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON | |
248 | {$this->_aliases['civicrm_pledge']}.id = {$this->_aliases['civicrm_pledge_payment']}.pledge_id | |
249 | AND {$this->_aliases['civicrm_pledge_payment']}.status_id = 1 | |
250 | "; | |
251 | } | |
252 | } | |
253 | ||
254 | function groupBy() { | |
255 | $this->_groupBy = ""; | |
256 | $append = FALSE; | |
257 | ||
258 | if (is_array($this->_params['group_bys']) && | |
259 | !empty($this->_params['group_bys']) | |
260 | ) { | |
261 | foreach ($this->_columns as $tableName => $table) { | |
262 | if (array_key_exists('group_bys', $table)) { | |
263 | foreach ($table['group_bys'] as $fieldName => $field) { | |
264 | if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) { | |
265 | if (CRM_Utils_Array::value('chart', $field)) { | |
266 | $this->assign('chartSupported', TRUE); | |
267 | } | |
268 | ||
269 | if (CRM_Utils_Array::value('frequency', $table['group_bys'][$fieldName]) && | |
270 | CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq']) | |
271 | ) { | |
272 | ||
273 | $append = "YEAR({$field['dbAlias']}),"; | |
274 | if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]), | |
275 | array('year') | |
276 | )) { | |
277 | $append = ''; | |
278 | } | |
279 | $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})"; | |
280 | $append = TRUE; | |
281 | } | |
282 | else { | |
283 | $this->_groupBy[] = $field['dbAlias']; | |
284 | } | |
285 | } | |
286 | } | |
287 | } | |
288 | } | |
289 | ||
290 | if (!empty($this->_statFields) && | |
291 | (($append && count($this->_groupBy) <= 1) || (!$append)) && !$this->_having | |
292 | ) { | |
293 | $this->_rollup = " WITH ROLLUP"; | |
294 | } | |
295 | $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) . " {$this->_rollup} "; | |
296 | } | |
297 | else { | |
298 | $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id"; | |
299 | } | |
300 | } | |
301 | ||
302 | function statistics(&$rows) { | |
303 | $statistics = parent::statistics($rows); | |
304 | ||
305 | if (!$this->_having) { | |
306 | $select = " | |
307 | SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount ) as count, | |
308 | SUM({$this->_aliases['civicrm_pledge']}.amount ) as amount, | |
309 | ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg | |
310 | "; | |
311 | ||
312 | $sql = "{$select} {$this->_from} {$this->_where}"; | |
9bf1940a | 313 | |
6a488035 TO |
314 | $dao = CRM_Core_DAO::executeQuery($sql); |
315 | ||
316 | if ($dao->fetch()) { | |
317 | $statistics['count']['amount'] = array( | |
318 | 'value' => $dao->amount, | |
319 | 'title' => 'Total Pledged', | |
320 | 'type' => CRM_Utils_Type::T_MONEY, | |
321 | ); | |
322 | $statistics['count']['count '] = array( | |
323 | 'value' => $dao->count, | |
324 | 'title' => 'Total No Pledges', | |
325 | ); | |
326 | $statistics['count']['avg '] = array( | |
327 | 'value' => $dao->avg, | |
328 | 'title' => 'Average', | |
329 | 'type' => CRM_Utils_Type::T_MONEY, | |
330 | ); | |
331 | } | |
332 | } | |
333 | return $statistics; | |
334 | } | |
335 | ||
336 | function where() { | |
337 | $clauses = array(); | |
338 | foreach ($this->_columns as $tableName => $table) { | |
339 | if (array_key_exists('filters', $table)) { | |
340 | foreach ($table['filters'] as $fieldName => $field) { | |
341 | $clause = NULL; | |
342 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
343 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); | |
344 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); | |
345 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); | |
346 | ||
347 | if ($relative || $from || $to) { | |
348 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
349 | } | |
350 | } | |
351 | else { | |
352 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); | |
353 | if ($op) { | |
354 | $clause = $this->whereClause($field, | |
355 | $op, | |
356 | CRM_Utils_Array::value("{$fieldName}_value", | |
357 | $this->_params | |
358 | ), | |
359 | CRM_Utils_Array::value("{$fieldName}_min", | |
360 | $this->_params | |
361 | ), | |
362 | CRM_Utils_Array::value("{$fieldName}_max", | |
363 | $this->_params | |
364 | ) | |
365 | ); | |
366 | } | |
367 | } | |
368 | ||
369 | if (!empty($clause)) { | |
370 | $clauses[] = $clause; | |
371 | } | |
372 | } | |
373 | } | |
374 | } | |
375 | if (empty($clauses)) { | |
376 | $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) "; | |
377 | } | |
378 | else { | |
379 | $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) AND | |
380 | " . implode(' AND ', $clauses); | |
381 | } | |
382 | ||
383 | if ($this->_aclWhere) { | |
384 | $this->_where .= " AND {$this->_aclWhere} "; | |
385 | } | |
386 | } | |
387 | ||
388 | function postProcess() { | |
389 | parent::postProcess(); | |
390 | } | |
391 | ||
392 | function alterDisplay(&$rows) { | |
393 | // custom code to alter rows | |
394 | $entryFound = FALSE; | |
395 | $checkList = array(); | |
396 | $display_flag = $prev_cid = $cid = 0; | |
9bf1940a | 397 | crm_Core_error::Debug('$rows', $rows); |
6a488035 TO |
398 | foreach ($rows as $rowNum => $row) { |
399 | ||
400 | // convert display name to links | |
401 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
402 | array_key_exists('civicrm_contact_id', $row) | |
403 | ) { | |
404 | $url = CRM_Utils_System::url("civicrm/contact/view", | |
405 | 'reset=1&cid=' . $row['civicrm_contact_id'], | |
406 | $this->_absoluteUrl | |
407 | ); | |
408 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
409 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact."); | |
410 | $entryFound = TRUE; | |
411 | } | |
412 | ||
413 | //handle status id | |
414 | if (array_key_exists('civicrm_pledge_status_id', $row)) { | |
415 | if ($value = $row['civicrm_pledge_status_id']) { | |
416 | $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value); | |
417 | } | |
418 | $entryFound = TRUE; | |
419 | } | |
420 | ||
421 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'pledge/summary', 'List all pledge(s) for this ') ? TRUE : $entryFound; | |
422 | ||
423 | // skip looking further in rows, if first row itself doesn't | |
424 | // have the column we need | |
425 | if (!$entryFound) { | |
426 | break; | |
427 | } | |
428 | } | |
429 | } | |
430 | } | |
431 |