Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
7e9e8871 | 4 | | CiviCRM version 4.7 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
fa938177 | 6 | | Copyright CiviCRM LLC (c) 2004-2016 | |
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 | 27 | |
850e4640 | 28 | |
9d72cede EM |
29 | /* |
30 | * !!!!!!!!!!!!!!!!!!!! | |
31 | * NB: this is named detail but behaves like a summary report. | |
32 | * It is also accessed through the Pledge Summary link in the UI | |
33 | * This should presumably be changed. | |
34 | * ~ Doten | |
35 | * !!!!!!!!!!!!!!!!!!!! | |
36 | * | |
37 | */ | |
2ebf49a4 | 38 | |
6a488035 TO |
39 | /** |
40 | * | |
41 | * @package CRM | |
fa938177 | 42 | * @copyright CiviCRM LLC (c) 2004-2016 |
6a488035 TO |
43 | */ |
44 | class CRM_Report_Form_Pledge_Detail extends CRM_Report_Form { | |
45 | ||
46 | protected $_summary = NULL; | |
47 | protected $_totalPaid = FALSE; | |
edcb3801 | 48 | protected $_pledgeStatuses = array(); |
6a488035 TO |
49 | protected $_customGroupExtends = array( |
50 | 'Pledge', | |
21dfd5f5 | 51 | 'Individual', |
6a488035 | 52 | ); |
74cf4551 | 53 | |
1728e9a0 | 54 | /** |
55 | * This report has not been optimised for group filtering. | |
56 | * | |
57 | * The functionality for group filtering has been improved but not | |
58 | * all reports have been adjusted to take care of it. This report has not | |
59 | * and will run an inefficient query until fixed. | |
60 | * | |
61 | * CRM-19170 | |
62 | * | |
63 | * @var bool | |
64 | */ | |
65 | protected $groupFilterNotOptimised = TRUE; | |
66 | ||
74cf4551 | 67 | /** |
36aff7b7 | 68 | * Class constructor. |
74cf4551 | 69 | */ |
00be9182 | 70 | public function __construct() { |
9d72cede EM |
71 | $this->_pledgeStatuses = CRM_Contribute_PseudoConstant::contributionStatus(); |
72 | // Check if CiviCampaign is a) enabled and b) has active campaigns | |
73 | $config = CRM_Core_Config::singleton(); | |
2ebf49a4 CD |
74 | $campaignEnabled = in_array("CiviCampaign", $config->enableComponents); |
75 | if ($campaignEnabled) { | |
76 | $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE); | |
77 | $this->activeCampaigns = $getCampaigns['campaigns']; | |
78 | asort($this->activeCampaigns); | |
79 | } | |
80 | ||
6a488035 | 81 | $this->_columns = array( |
a130e045 DG |
82 | 'civicrm_contact' => array( |
83 | 'dao' => 'CRM_Contact_DAO_Contact', | |
84 | 'fields' => array( | |
85 | 'sort_name' => array( | |
86 | 'title' => ts('Contact Name'), | |
87 | 'required' => TRUE, | |
88 | 'no_repeat' => TRUE, | |
6a488035 | 89 | ), |
9d72cede | 90 | ), |
a130e045 DG |
91 | 'filters' => array( |
92 | 'sort_name' => array('title' => ts('Contact Name')), | |
93 | 'id' => array('no_display' => TRUE), | |
94 | ), | |
95 | 'grouping' => 'contact-fields', | |
96 | ), | |
97 | 'civicrm_email' => array( | |
98 | 'dao' => 'CRM_Core_DAO_Email', | |
99 | 'fields' => array( | |
100 | 'email' => array('no_repeat' => TRUE), | |
101 | ), | |
102 | 'grouping' => 'contact-fields', | |
103 | ), | |
104 | 'civicrm_pledge' => array( | |
105 | 'dao' => 'CRM_Pledge_DAO_Pledge', | |
106 | 'fields' => array( | |
107 | 'id' => array( | |
108 | 'no_display' => TRUE, | |
109 | 'required' => TRUE, | |
110 | ), | |
111 | 'contact_id' => array( | |
112 | 'no_display' => TRUE, | |
113 | 'required' => TRUE, | |
114 | ), | |
9e335308 | 115 | 'financial_type_id' => array( |
116 | 'title' => ts('Financial Type'), | |
117 | ), | |
a130e045 DG |
118 | 'amount' => array( |
119 | 'title' => ts('Pledge Amount'), | |
120 | 'required' => TRUE, | |
121 | 'type' => CRM_Utils_Type::T_MONEY, | |
122 | ), | |
123 | 'currency' => array( | |
124 | 'required' => TRUE, | |
125 | 'no_display' => TRUE, | |
126 | ), | |
127 | 'frequency_unit' => array( | |
128 | 'title' => ts('Frequency Unit'), | |
129 | ), | |
130 | 'installments' => array( | |
131 | 'title' => ts('Installments'), | |
132 | ), | |
133 | 'pledge_create_date' => array( | |
134 | 'title' => ts('Pledge Made Date'), | |
135 | ), | |
136 | 'start_date' => array( | |
137 | 'title' => ts('Pledge Start Date'), | |
138 | 'type' => CRM_Utils_Type::T_DATE, | |
139 | ), | |
140 | 'end_date' => array( | |
141 | 'title' => ts('Pledge End Date'), | |
142 | 'type' => CRM_Utils_Type::T_DATE, | |
143 | ), | |
144 | 'status_id' => array( | |
145 | 'title' => ts('Pledge Status'), | |
146 | 'required' => TRUE, | |
9d72cede | 147 | ), |
6a488035 | 148 | ), |
a130e045 DG |
149 | 'filters' => array( |
150 | 'pledge_create_date' => array( | |
ccc29f8e | 151 | 'title' => ts('Pledge Made Date'), |
a130e045 DG |
152 | 'operatorType' => CRM_Report_Form::OP_DATE, |
153 | ), | |
154 | 'pledge_amount' => array( | |
155 | 'title' => ts('Pledged Amount'), | |
156 | 'operatorType' => CRM_Report_Form::OP_INT, | |
157 | ), | |
158 | 'currency' => array( | |
ccc29f8e | 159 | 'title' => ts('Currency'), |
a130e045 DG |
160 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
161 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
162 | 'default' => NULL, | |
163 | 'type' => CRM_Utils_Type::T_STRING, | |
164 | ), | |
165 | 'sid' => array( | |
166 | 'name' => 'status_id', | |
167 | 'title' => ts('Pledge Status'), | |
8ee006e7 | 168 | 'type' => CRM_Utils_Type::T_INT, |
a130e045 DG |
169 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
170 | 'options' => CRM_Core_OptionGroup::values('contribution_status'), | |
171 | ), | |
9e335308 | 172 | 'financial_type_id' => array( |
173 | 'title' => ts('Financial Type'), | |
8ee006e7 | 174 | 'type' => CRM_Utils_Type::T_INT, |
9e335308 | 175 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
176 | 'options' => CRM_Contribute_PseudoConstant::financialType(), | |
177 | ), | |
850e4640 | 178 | |
a130e045 DG |
179 | ), |
180 | ), | |
181 | 'civicrm_pledge_payment' => array( | |
182 | 'dao' => 'CRM_Pledge_DAO_PledgePayment', | |
183 | 'fields' => array( | |
184 | 'total_paid' => array( | |
185 | 'title' => ts('Total Amount Paid'), | |
186 | 'type' => CRM_Utils_Type::T_MONEY, | |
187 | ), | |
188 | 'balance_due' => array( | |
189 | 'title' => ts('Balance Due'), | |
190 | 'default' => TRUE, | |
191 | 'type' => CRM_Utils_Type::T_MONEY, | |
9d72cede | 192 | ), |
6a488035 | 193 | ), |
a130e045 DG |
194 | ), |
195 | ); | |
196 | ||
197 | $this->_columns += $this->getAddressColumns(array('group_by' => FALSE)) + $this->getPhoneColumns(); | |
198 | ||
2ebf49a4 CD |
199 | // If we have a campaign, build out the relevant elements |
200 | $this->_tagFilter = TRUE; | |
201 | if ($campaignEnabled && !empty($this->activeCampaigns)) { | |
9d72cede | 202 | $this->_columns['civicrm_pledge']['fields']['campaign_id'] = array( |
ccc29f8e | 203 | 'title' => ts('Campaign'), |
9d72cede EM |
204 | 'default' => 'false', |
205 | ); | |
206 | $this->_columns['civicrm_pledge']['filters']['campaign_id'] = array( | |
207 | 'title' => ts('Campaign'), | |
208 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
209 | 'options' => $this->activeCampaigns, | |
942b1d01 | 210 | 'type' => CRM_Utils_Type::T_INT, |
9d72cede EM |
211 | ); |
212 | $this->_columns['civicrm_pledge']['group_bys']['campaign_id'] = array('title' => ts('Campaign')); | |
2ebf49a4 CD |
213 | |
214 | } | |
6a488035 | 215 | |
16e2e80c | 216 | $this->_groupFilter = TRUE; |
6a488035 | 217 | $this->_tagFilter = TRUE; |
9bf1940a | 218 | $this->_currencyColumn = 'civicrm_pledge_currency'; |
6a488035 TO |
219 | parent::__construct(); |
220 | } | |
221 | ||
00be9182 | 222 | public function preProcess() { |
6a488035 TO |
223 | parent::preProcess(); |
224 | } | |
225 | ||
00be9182 | 226 | public function select() { |
6a488035 TO |
227 | parent::select(); |
228 | } | |
d424ffde | 229 | |
74cf4551 | 230 | /** |
fe482240 | 231 | * If we are retrieving total paid we need to define the inclusion of pledge_payment. |
d424ffde | 232 | * |
100fef9d | 233 | * @param string $tableName |
74cf4551 | 234 | * @param $tableKey |
100fef9d | 235 | * @param string $fieldName |
74cf4551 EM |
236 | * @param $field |
237 | * | |
238 | * @return bool|string | |
239 | */ | |
00be9182 | 240 | public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) { |
9d72cede | 241 | if ($fieldName == 'total_paid') { |
6a488035 TO |
242 | $this->_totalPaid = TRUE; // add pledge_payment join |
243 | $this->_columnHeaders["{$tableName}_{$fieldName}"] = array( | |
244 | 'title' => $field['title'], | |
21dfd5f5 | 245 | 'type' => $field['type'], |
6a488035 | 246 | ); |
edcb3801 | 247 | return "COALESCE(sum({$this->_aliases[$tableName]}.actual_amount), 0) as {$tableName}_{$fieldName}"; |
6a488035 | 248 | } |
9d72cede | 249 | if ($fieldName == 'balance_due') { |
edcb3801 | 250 | $cancelledStatus = array_search('Cancelled', $this->_pledgeStatuses); |
251 | $completedStatus = array_search('Completed', $this->_pledgeStatuses); | |
6a488035 TO |
252 | $this->_totalPaid = TRUE; // add pledge_payment join |
253 | $this->_columnHeaders["{$tableName}_{$fieldName}"] = $field['title']; | |
254 | $this->_columnHeaders["{$tableName}_{$fieldName}"] = array( | |
255 | 'title' => $field['title'], | |
21dfd5f5 | 256 | 'type' => $field['type'], |
6a488035 | 257 | ); |
9d72cede | 258 | return "IF({$this->_aliases['civicrm_pledge']}.status_id IN({$cancelledStatus}, $completedStatus), 0, COALESCE({$this->_aliases['civicrm_pledge']}.amount, 0) - COALESCE(sum({$this->_aliases[$tableName]}.actual_amount),0)) as {$tableName}_{$fieldName}"; |
6a488035 TO |
259 | } |
260 | return FALSE; | |
261 | } | |
262 | ||
00be9182 | 263 | public function groupBy() { |
6a488035 TO |
264 | parent::groupBy(); |
265 | if (empty($this->_groupBy) && $this->_totalPaid) { | |
d1641c51 | 266 | $groupBy = array("{$this->_aliases['civicrm_pledge']}.id", "{$this->_aliases['civicrm_pledge']}.currency"); |
b708c08d | 267 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); |
6a488035 TO |
268 | } |
269 | } | |
9d72cede | 270 | |
00be9182 | 271 | public function from() { |
6a488035 TO |
272 | $this->_from = " |
273 | FROM civicrm_pledge {$this->_aliases['civicrm_pledge']} | |
274 | LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} | |
275 | ON ({$this->_aliases['civicrm_contact']}.id = | |
276 | {$this->_aliases['civicrm_pledge']}.contact_id ) | |
277 | {$this->_aclFrom} "; | |
278 | ||
9d72cede | 279 | if ($this->_totalPaid) { |
6a488035 TO |
280 | $this->_from .= " |
281 | LEFT JOIN civicrm_pledge_payment {$this->_aliases['civicrm_pledge_payment']} ON | |
282 | {$this->_aliases['civicrm_pledge']}.id = {$this->_aliases['civicrm_pledge_payment']}.pledge_id | |
283 | AND {$this->_aliases['civicrm_pledge_payment']}.status_id = 1 | |
284 | "; | |
285 | } | |
286 | ||
850e4640 E |
287 | $this->addPhoneFromClause(); |
288 | $this->addAddressFromClause(); | |
6a488035 TO |
289 | // include email field if email column is to be included |
290 | if ($this->_emailField) { | |
291 | $this->_from .= " | |
292 | LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} | |
293 | ON ({$this->_aliases['civicrm_contact']}.id = | |
294 | {$this->_aliases['civicrm_email']}.contact_id) AND | |
295 | {$this->_aliases['civicrm_email']}.is_primary = 1\n"; | |
296 | } | |
297 | } | |
298 | ||
74cf4551 EM |
299 | /** |
300 | * @param $rows | |
301 | * | |
302 | * @return array | |
303 | */ | |
00be9182 | 304 | public function statistics(&$rows) { |
6a488035 | 305 | $statistics = parent::statistics($rows); |
9d72cede | 306 | //regenerate the from field without extra left join on pledge payments |
bcb4b43e | 307 | $totalPaid = $this->_totalPaid; |
2d97372d | 308 | $this->_totalPaid = FALSE; |
309 | $this->from(); | |
310 | $this->customDataFrom(); | |
6a488035 | 311 | if (!$this->_having) { |
7a961f19 | 312 | $totalAmount = $average = array(); |
313 | $count = 0; | |
6a488035 | 314 | $select = " |
edcb3801 | 315 | SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount ) as count, |
316 | SUM({$this->_aliases['civicrm_pledge']}.amount ) as amount, | |
317 | ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg, | |
318 | {$this->_aliases['civicrm_pledge']}.currency as currency | |
319 | "; | |
6a488035 | 320 | |
edcb3801 | 321 | $group = "GROUP BY {$this->_aliases['civicrm_pledge']}.currency"; |
7a961f19 | 322 | $sql = "{$select} {$this->_from} {$this->_where} {$group}"; |
6a488035 | 323 | $dao = CRM_Core_DAO::executeQuery($sql); |
f0472f17 | 324 | $count = $index = $totalCount = 0; |
edcb3801 | 325 | // this will run once per currency |
9d72cede | 326 | while ($dao->fetch()) { |
edcb3801 | 327 | $totalAmount = CRM_Utils_Money::format($dao->amount, $dao->currency); |
9d72cede | 328 | $average = CRM_Utils_Money::format($dao->avg, $dao->currency); |
edcb3801 | 329 | $count = $dao->count; |
f0472f17 | 330 | $totalCount .= $count; |
edcb3801 | 331 | $statistics['counts']['amount' . $index] = array( |
474f6ca2 | 332 | 'title' => ts('Total Pledged') . ' (' . $dao->currency . ')', |
edcb3801 | 333 | 'value' => $totalAmount, |
334 | 'type' => CRM_Utils_Type::T_STRING, | |
335 | ); | |
336 | $statistics['counts']['avg' . $index] = array( | |
474f6ca2 | 337 | 'title' => ts('Average') . ' (' . $dao->currency . ')', |
edcb3801 | 338 | 'value' => $average, |
339 | 'type' => CRM_Utils_Type::T_STRING, | |
340 | ); | |
f0472f17 | 341 | $statistics['counts']['count' . $index] = array( |
474f6ca2 | 342 | 'title' => ts('Total No Pledges') . ' (' . $dao->currency . ')', |
f0472f17 | 343 | 'value' => $count, |
344 | 'type' => CRM_Utils_Type::T_INT, | |
345 | ); | |
9d72cede | 346 | $index++; |
6a488035 | 347 | } |
9d72cede | 348 | if ($totalCount > $count) { |
f0472f17 | 349 | $statistics['counts']['count' . $index] = array( |
350 | 'title' => ts('Total No Pledges'), | |
351 | 'value' => $totalCount, | |
352 | 'type' => CRM_Utils_Type::T_INT, | |
353 | ); | |
354 | } | |
6a488035 | 355 | } |
bcb4b43e | 356 | // reset from clause |
357 | if ($totalPaid) { | |
358 | $this->_totalPaid = TRUE; | |
359 | $this->from(); | |
360 | } | |
6a488035 TO |
361 | return $statistics; |
362 | } | |
363 | ||
00be9182 | 364 | public function orderBy() { |
6a488035 TO |
365 | $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id"; |
366 | } | |
367 | ||
00be9182 | 368 | public function where() { |
6a488035 TO |
369 | $clauses = array(); |
370 | foreach ($this->_columns as $tableName => $table) { | |
371 | if (array_key_exists('filters', $table)) { | |
372 | foreach ($table['filters'] as $fieldName => $field) { | |
373 | $clause = NULL; | |
374 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
375 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); | |
9d72cede EM |
376 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); |
377 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); | |
6a488035 TO |
378 | |
379 | if ($relative || $from || $to) { | |
380 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
381 | } | |
382 | } | |
383 | else { | |
384 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); | |
385 | if ($op) { | |
386 | $clause = $this->whereClause($field, | |
387 | $op, | |
388 | CRM_Utils_Array::value("{$fieldName}_value", | |
389 | $this->_params | |
390 | ), | |
391 | CRM_Utils_Array::value("{$fieldName}_min", | |
392 | $this->_params | |
393 | ), | |
394 | CRM_Utils_Array::value("{$fieldName}_max", | |
395 | $this->_params | |
396 | ) | |
397 | ); | |
398 | } | |
399 | } | |
400 | ||
401 | if (!empty($clause)) { | |
402 | $clauses[] = $clause; | |
403 | } | |
404 | } | |
405 | } | |
406 | } | |
407 | if (empty($clauses)) { | |
408 | $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) "; | |
409 | } | |
410 | else { | |
411 | $this->_where = "WHERE ({$this->_aliases['civicrm_pledge']}.is_test=0 ) AND | |
edcb3801 | 412 | " . implode(' AND ', $clauses); |
6a488035 TO |
413 | } |
414 | ||
415 | if ($this->_aclWhere) { | |
416 | $this->_where .= " AND {$this->_aclWhere} "; | |
417 | } | |
418 | } | |
419 | ||
00be9182 | 420 | public function postProcess() { |
6a488035 TO |
421 | |
422 | $this->beginPostProcess(); | |
423 | ||
424 | // get the acl clauses built before we assemble the query | |
425 | $this->buildACLClause($this->_aliases['civicrm_contact']); | |
9d72cede EM |
426 | $sql = $this->buildQuery(); |
427 | $rows = $payment = array(); | |
6a488035 TO |
428 | |
429 | $dao = CRM_Core_DAO::executeQuery($sql); | |
430 | ||
431 | // Set pager for the Main Query only which displays basic information | |
432 | $this->setPager(); | |
433 | $this->assign('columnHeaders', $this->_columnHeaders); | |
434 | ||
435 | while ($dao->fetch()) { | |
436 | $pledgeID = $dao->civicrm_pledge_id; | |
437 | foreach ($this->_columnHeaders as $columnHeadersKey => $columnHeadersValue) { | |
438 | $row = array(); | |
439 | if (property_exists($dao, $columnHeadersKey)) { | |
440 | $display[$pledgeID][$columnHeadersKey] = $dao->$columnHeadersKey; | |
441 | } | |
442 | } | |
443 | $pledgeIDArray[] = $pledgeID; | |
444 | } | |
445 | ||
446 | // Add Special headers | |
447 | $this->_columnHeaders['scheduled_date'] = array( | |
448 | 'type' => CRM_Utils_Type::T_DATE, | |
ccc29f8e | 449 | 'title' => ts('Next Payment Due'), |
9d72cede EM |
450 | ); |
451 | $this->_columnHeaders['scheduled_amount'] = array( | |
452 | 'type' => CRM_Utils_Type::T_MONEY, | |
ccc29f8e | 453 | 'title' => ts('Next Payment Amount'), |
9d72cede EM |
454 | ); |
455 | $this->_columnHeaders['status_id'] = NULL; | |
456 | ||
457 | /* | |
458 | * this is purely about ordering the total paid & balance due fields off to the end | |
459 | * of the table in case custom or address fields cause them to fall in the middle | |
460 | * (arguably the pledge amount should be moved to after these fields too) | |
461 | * | |
462 | */ | |
463 | $tableHeaders = array( | |
464 | 'civicrm_pledge_payment_total_paid', | |
21dfd5f5 | 465 | 'civicrm_pledge_payment_balance_due', |
9d72cede | 466 | ); |
6a488035 TO |
467 | |
468 | foreach ($tableHeaders as $header) { | |
469 | //per above, unset & reset them so they move to the end | |
9d72cede | 470 | if (isset($this->_columnHeaders[$header])) { |
6a488035 TO |
471 | $headervalue = $this->_columnHeaders[$header]; |
472 | unset($this->_columnHeaders[$header]); | |
473 | $this->_columnHeaders[$header] = $headervalue; | |
474 | } | |
475 | } | |
476 | ||
477 | // To Display Payment Details of pledged amount | |
478 | // for pledge payments In Progress | |
479 | if (!empty($display)) { | |
a18fb275 SB |
480 | $statusId = array_keys(CRM_Core_PseudoConstant::accountOptionValues("contribution_status", NULL, " AND v.name IN ('Pending', 'Overdue')")); |
481 | $statusId = implode(',', $statusId); | |
d1641c51 | 482 | $select = "payment.pledge_id, payment.scheduled_amount, pledge.contact_id"; |
6a488035 TO |
483 | $sqlPayment = " |
484 | SELECT min(payment.scheduled_date) as scheduled_date, | |
d1641c51 | 485 | {$select} |
6a488035 TO |
486 | |
487 | FROM civicrm_pledge_payment payment | |
488 | LEFT JOIN civicrm_pledge pledge | |
489 | ON pledge.id = payment.pledge_id | |
490 | ||
a18fb275 | 491 | WHERE payment.status_id IN ({$statusId}) |
6a488035 | 492 | |
d1641c51 | 493 | GROUP BY {$select}"; |
6a488035 TO |
494 | |
495 | $daoPayment = CRM_Core_DAO::executeQuery($sqlPayment); | |
496 | ||
497 | while ($daoPayment->fetch()) { | |
498 | foreach ($pledgeIDArray as $key => $val) { | |
499 | if ($val == $daoPayment->pledge_id) { | |
500 | ||
501 | $display[$daoPayment->pledge_id]['scheduled_date'] = $daoPayment->scheduled_date; | |
502 | ||
503 | $display[$daoPayment->pledge_id]['scheduled_amount'] = $daoPayment->scheduled_amount; | |
504 | } | |
505 | } | |
506 | } | |
6a488035 TO |
507 | } |
508 | ||
509 | // Displaying entire data on the form | |
510 | if (!empty($display)) { | |
511 | foreach ($display as $key => $value) { | |
512 | $row = array(); | |
513 | foreach ($this->_columnHeaders as $columnKey => $columnValue) { | |
514 | if (array_key_exists($columnKey, $value)) { | |
0d8afee2 | 515 | $row[$columnKey] = !empty($value[$columnKey]) ? $value[$columnKey] : ''; |
6a488035 TO |
516 | } |
517 | } | |
518 | $rows[] = $row; | |
519 | } | |
520 | } | |
521 | ||
522 | unset($this->_columnHeaders['status_id']); | |
523 | unset($this->_columnHeaders['civicrm_pledge_id']); | |
524 | unset($this->_columnHeaders['civicrm_pledge_contact_id']); | |
525 | ||
526 | $this->formatDisplay($rows, FALSE); | |
527 | $this->doTemplateAssignment($rows); | |
528 | $this->endPostProcess($rows); | |
529 | } | |
530 | ||
74cf4551 | 531 | /** |
ced9bfed EM |
532 | * Alter display of rows. |
533 | * | |
534 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
535 | * such as rendering contacts as links. | |
536 | * | |
537 | * @param array $rows | |
538 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 539 | */ |
00be9182 | 540 | public function alterDisplay(&$rows) { |
9d72cede EM |
541 | $entryFound = FALSE; |
542 | $checkList = array(); | |
6a488035 TO |
543 | $display_flag = $prev_cid = $cid = 0; |
544 | ||
545 | foreach ($rows as $rowNum => $row) { | |
546 | if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') { | |
547 | // don't repeat contact details if its same as the previous row | |
548 | if (array_key_exists('civicrm_pledge_contact_id', $row)) { | |
549 | if ($cid = $row['civicrm_pledge_contact_id']) { | |
550 | if ($rowNum == 0) { | |
551 | $prev_cid = $cid; | |
552 | } | |
553 | else { | |
554 | if ($prev_cid == $cid) { | |
555 | $display_flag = 1; | |
556 | $prev_cid = $cid; | |
557 | } | |
558 | else { | |
559 | $display_flag = 0; | |
560 | $prev_cid = $cid; | |
561 | } | |
562 | } | |
563 | ||
564 | if ($display_flag) { | |
565 | foreach ($row as $colName => $colVal) { | |
566 | if (in_array($colName, $this->_noRepeats)) { | |
567 | unset($rows[$rowNum][$colName]); | |
568 | } | |
569 | } | |
570 | } | |
571 | $entryFound = TRUE; | |
572 | } | |
573 | } | |
574 | } | |
575 | ||
576 | // convert display name to links | |
577 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
578 | array_key_exists('civicrm_pledge_contact_id', $row) | |
579 | ) { | |
580 | $url = CRM_Utils_System::url("civicrm/contact/view", | |
581 | 'reset=1&cid=' . $row['civicrm_pledge_contact_id'], | |
582 | $this->_absoluteUrl | |
583 | ); | |
584 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
585 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact."); | |
586 | $entryFound = TRUE; | |
587 | } | |
588 | ||
9e335308 | 589 | if (array_key_exists('civicrm_pledge_financial_type_id', $row)) { |
590 | if ($value = $row['civicrm_pledge_financial_type_id']) { | |
591 | $rows[$rowNum]['civicrm_pledge_financial_type_id'] = CRM_Contribute_PseudoConstant::financialType($value, FALSE); | |
592 | } | |
593 | $entryFound = TRUE; | |
594 | } | |
595 | ||
6a488035 TO |
596 | //handle status id |
597 | if (array_key_exists('civicrm_pledge_status_id', $row)) { | |
598 | if ($value = $row['civicrm_pledge_status_id']) { | |
599 | $rows[$rowNum]['civicrm_pledge_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value); | |
600 | } | |
601 | $entryFound = TRUE; | |
602 | } | |
603 | ||
2ebf49a4 CD |
604 | // If using campaigns, convert campaign_id to campaign title |
605 | if (array_key_exists('civicrm_pledge_campaign_id', $row)) { | |
606 | if ($value = $row['civicrm_pledge_campaign_id']) { | |
607 | $rows[$rowNum]['civicrm_pledge_campaign_id'] = $this->activeCampaigns[$value]; | |
608 | } | |
609 | $entryFound = TRUE; | |
610 | } | |
611 | ||
6a488035 TO |
612 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'pledge/detail', 'List all pledge(s) for this ') ? TRUE : $entryFound; |
613 | ||
614 | // skip looking further in rows, if first row itself doesn't | |
615 | // have the column we need | |
616 | if (!$entryFound) { | |
617 | break; | |
618 | } | |
619 | } | |
620 | } | |
96025800 | 621 | |
6a488035 | 622 | } |