Commit | Line | Data |
---|---|---|
3c6d6452 PN |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
3c6d6452 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
3c6d6452 PN |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
3c6d6452 PN |
16 | */ |
17 | class CRM_Report_Form_Contribute_DeferredRevenue extends CRM_Report_Form { | |
18 | ||
19 | /** | |
20 | * Holds Deferred Financial Account | |
c86d4e7c | 21 | * @var array |
3c6d6452 | 22 | */ |
be2fb01f | 23 | protected $_deferredFinancialAccount = []; |
3c6d6452 PN |
24 | |
25 | /** | |
26 | */ | |
27 | public function __construct() { | |
a3bba030 | 28 | $this->_exposeContactID = FALSE; |
3c6d6452 | 29 | $this->_deferredFinancialAccount = CRM_Financial_BAO_FinancialAccount::getAllDeferredFinancialAccount(); |
be2fb01f CW |
30 | $this->_columns = [ |
31 | 'civicrm_financial_trxn' => [ | |
da345655 | 32 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', |
be2fb01f CW |
33 | 'fields' => [ |
34 | 'status_id' => [ | |
da345655 | 35 | 'title' => ts('Transaction'), |
be2fb01f CW |
36 | ], |
37 | 'trxn_date' => [ | |
da345655 E |
38 | 'title' => ts('Transaction Date'), |
39 | 'type' => CRM_Utils_Type::T_DATE, | |
40 | 'required' => TRUE, | |
be2fb01f CW |
41 | ], |
42 | 'total_amount' => [ | |
da345655 E |
43 | 'title' => ts('Transaction Amount'), |
44 | 'type' => CRM_Utils_Type::T_MONEY, | |
45 | 'required' => TRUE, | |
e29d24ad | 46 | 'dbAlias' => 'SUM(financial_trxn_1_civireport.total_amount )', |
be2fb01f CW |
47 | ], |
48 | ], | |
49 | 'filters' => [ | |
50 | 'trxn_date' => [ | |
da345655 E |
51 | 'title' => ts('Transaction Date'), |
52 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
53 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
54 | ], |
55 | ], | |
56 | ], | |
57 | 'civicrm_contribution' => [ | |
da345655 | 58 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
be2fb01f CW |
59 | 'fields' => [ |
60 | 'id' => [ | |
da345655 | 61 | 'title' => ts('Contribution ID'), |
be2fb01f CW |
62 | ], |
63 | 'contribution_id' => [ | |
da345655 E |
64 | 'title' => ts('Contribution ID'), |
65 | 'required' => TRUE, | |
66 | 'no_display' => TRUE, | |
67 | 'dbAlias' => 'contribution_civireport.id', | |
be2fb01f CW |
68 | ], |
69 | 'contact_id' => [ | |
da345655 | 70 | 'title' => ts('Contact ID'), |
be2fb01f CW |
71 | ], |
72 | 'source' => [ | |
da345655 | 73 | 'title' => ts('Source'), |
be2fb01f CW |
74 | ], |
75 | 'receive_date' => [ | |
da345655 E |
76 | 'title' => ts('Receive Date'), |
77 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
78 | ], |
79 | 'cancel_date' => [ | |
6e7cc0f5 | 80 | 'name' => 'contribution_cancel_date', |
da345655 E |
81 | 'title' => ts('Cancel Date'), |
82 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
83 | ], |
84 | 'revenue_recognition_date' => [ | |
da345655 E |
85 | 'title' => ts('Revenue Recognition Date'), |
86 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
87 | ], |
88 | ], | |
89 | 'filters' => [ | |
90 | 'receive_date' => [ | |
da345655 E |
91 | 'title' => ts('Receive Date'), |
92 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
93 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f | 94 | ], |
f85b4a88 | 95 | 'receipt_date' => ['operatorType' => CRM_Report_Form::OP_DATE], |
be2fb01f | 96 | 'cancel_date' => [ |
da345655 E |
97 | 'title' => ts('Cancel Date'), |
98 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
99 | 'type' => CRM_Utils_Type::T_DATE, | |
6e7cc0f5 | 100 | 'name' => 'contribution_cancel_date', |
be2fb01f CW |
101 | ], |
102 | 'revenue_recognition_date' => [ | |
da345655 E |
103 | 'title' => ts('Revenue Recognition Date'), |
104 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
105 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
106 | ], |
107 | 'revenue_recognition_date_toggle' => [ | |
da345655 E |
108 | 'title' => ts("Current month's revenue?"), |
109 | 'type' => CRM_Utils_Type::T_BOOLEAN, | |
110 | 'default' => 0, | |
111 | 'pseudofield' => TRUE, | |
be2fb01f CW |
112 | ], |
113 | ], | |
114 | ], | |
115 | 'civicrm_financial_account' => [ | |
3c6d6452 | 116 | 'dao' => 'CRM_Financial_DAO_FinancialAccount', |
be2fb01f CW |
117 | 'fields' => [ |
118 | 'name' => [ | |
a3bba030 PN |
119 | 'title' => ts('Deferred Account'), |
120 | 'required' => TRUE, | |
121 | 'no_display' => TRUE, | |
be2fb01f CW |
122 | ], |
123 | 'id' => [ | |
a3bba030 PN |
124 | 'title' => ts('Deferred Account ID'), |
125 | 'required' => TRUE, | |
126 | 'no_display' => TRUE, | |
be2fb01f CW |
127 | ], |
128 | 'accounting_code' => [ | |
a3bba030 PN |
129 | 'title' => ts('Deferred Accounting Code'), |
130 | 'required' => TRUE, | |
131 | 'no_display' => TRUE, | |
be2fb01f CW |
132 | ], |
133 | ], | |
134 | 'filters' => [ | |
135 | 'id' => [ | |
3c6d6452 PN |
136 | 'title' => ts('Deferred Financial Account'), |
137 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
138 | 'options' => $this->_deferredFinancialAccount, | |
139 | 'type' => CRM_Utils_Type::T_INT, | |
be2fb01f CW |
140 | ], |
141 | ], | |
142 | ], | |
143 | 'civicrm_financial_account_1' => [ | |
a3bba030 | 144 | 'dao' => 'CRM_Financial_DAO_FinancialAccount', |
be2fb01f CW |
145 | 'fields' => [ |
146 | 'name' => [ | |
a3bba030 PN |
147 | 'title' => ts('Revenue Account'), |
148 | 'required' => TRUE, | |
149 | 'no_display' => TRUE, | |
be2fb01f CW |
150 | ], |
151 | 'id' => [ | |
a3bba030 PN |
152 | 'title' => ts('Revenue Account ID'), |
153 | 'required' => TRUE, | |
154 | 'no_display' => TRUE, | |
be2fb01f CW |
155 | ], |
156 | 'accounting_code' => [ | |
a3bba030 PN |
157 | 'title' => ts('Revenue Accounting code'), |
158 | 'no_display' => TRUE, | |
159 | 'required' => TRUE, | |
be2fb01f CW |
160 | ], |
161 | ], | |
162 | ], | |
163 | 'civicrm_financial_item' => [ | |
a3bba030 | 164 | 'dao' => 'CRM_Financial_DAO_FinancialItem', |
be2fb01f CW |
165 | 'fields' => [ |
166 | 'status_id' => [ | |
a3bba030 PN |
167 | 'title' => ts('Status'), |
168 | 'required' => TRUE, | |
169 | 'no_display' => TRUE, | |
be2fb01f CW |
170 | ], |
171 | 'id' => [ | |
468477f3 | 172 | 'title' => ts('Financial Item ID'), |
a3bba030 PN |
173 | 'required' => TRUE, |
174 | 'no_display' => TRUE, | |
be2fb01f CW |
175 | ], |
176 | 'description' => [ | |
da345655 | 177 | 'title' => ts('Item'), |
be2fb01f CW |
178 | ], |
179 | ], | |
180 | ], | |
181 | 'civicrm_financial_trxn_1' => [ | |
a3bba030 | 182 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', |
be2fb01f CW |
183 | 'fields' => [ |
184 | 'total_amount' => [ | |
a3bba030 | 185 | 'title' => ts('Deferred Transaction Amount'), |
da345655 | 186 | 'type' => CRM_Utils_Type::T_MONEY, |
a3bba030 PN |
187 | 'required' => TRUE, |
188 | 'no_display' => TRUE, | |
189 | 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.total_amount)', | |
be2fb01f CW |
190 | ], |
191 | 'trxn_date' => [ | |
a3bba030 PN |
192 | 'title' => ts('Deferred Transaction Date'), |
193 | 'required' => TRUE, | |
194 | 'no_display' => TRUE, | |
195 | 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.trxn_date)', | |
da345655 | 196 | 'type' => CRM_Utils_Type::T_DATE, |
be2fb01f CW |
197 | ], |
198 | ], | |
199 | ], | |
200 | 'civicrm_contact' => [ | |
a3bba030 | 201 | 'dao' => 'CRM_Contact_DAO_Contact', |
be2fb01f CW |
202 | 'fields' => [ |
203 | 'display_name' => [ | |
da345655 | 204 | 'title' => ts('Contact Name'), |
be2fb01f CW |
205 | ], |
206 | 'id' => [ | |
da345655 | 207 | 'title' => ts('Contact ID'), |
a3bba030 PN |
208 | 'required' => TRUE, |
209 | 'no_display' => TRUE, | |
be2fb01f CW |
210 | ], |
211 | ], | |
212 | ], | |
213 | 'civicrm_membership' => [ | |
a3bba030 | 214 | 'dao' => 'CRM_Member_DAO_Membership', |
be2fb01f CW |
215 | 'fields' => [ |
216 | 'start_date' => [ | |
a3bba030 | 217 | 'title' => ts('Start Date'), |
a3bba030 | 218 | 'dbAlias' => 'IFNULL(membership_civireport.start_date, event_civireport.start_date)', |
da345655 | 219 | 'type' => CRM_Utils_Type::T_DATE, |
be2fb01f CW |
220 | ], |
221 | 'end_date' => [ | |
a3bba030 | 222 | 'title' => ts('End Date'), |
a3bba030 | 223 | 'dbdbAlias' => 'IFNULL(membership_civireport.end_date, event_civireport.end_date)', |
da345655 | 224 | 'type' => CRM_Utils_Type::T_DATE, |
be2fb01f CW |
225 | ], |
226 | ], | |
227 | ], | |
228 | 'civicrm_event' => [ | |
a3bba030 | 229 | 'dao' => 'CRM_Event_DAO_Event', |
be2fb01f CW |
230 | ], |
231 | 'civicrm_participant' => [ | |
a3bba030 | 232 | 'dao' => 'CRM_Event_DAO_Participant', |
be2fb01f CW |
233 | ], |
234 | 'civicrm_batch' => [ | |
5f2d82f6 PN |
235 | 'dao' => 'CRM_Batch_DAO_EntityBatch', |
236 | 'grouping' => 'contri-fields', | |
be2fb01f CW |
237 | 'fields' => [ |
238 | 'batch_id' => [ | |
da345655 | 239 | 'title' => ts('Batch Title'), |
0860078d E |
240 | 'dbAlias' => "GROUP_CONCAT(DISTINCT batch_civireport.batch_id |
241 | ORDER BY batch_civireport.batch_id SEPARATOR ',')", | |
242 | ||
be2fb01f CW |
243 | ], |
244 | ], | |
245 | 'filters' => [ | |
246 | 'batch_id' => [ | |
5f2d82f6 PN |
247 | 'title' => ts('Batch Title'), |
248 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
249 | 'options' => CRM_Batch_BAO_Batch::getBatches(), | |
250 | 'type' => CRM_Utils_Type::T_INT, | |
be2fb01f CW |
251 | ], |
252 | ], | |
253 | ], | |
254 | ]; | |
3c6d6452 PN |
255 | parent::__construct(); |
256 | } | |
257 | ||
f5aedad3 PN |
258 | /** |
259 | * Pre process function. | |
260 | * | |
261 | * Called prior to build form. | |
262 | */ | |
3c6d6452 PN |
263 | public function preProcess() { |
264 | parent::preProcess(); | |
265 | } | |
266 | ||
f5aedad3 PN |
267 | /** |
268 | * Build from clause. | |
269 | */ | |
3c6d6452 PN |
270 | public function from() { |
271 | $deferredRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' ")); | |
272 | $revenueRelationship = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' ")); | |
da345655 E |
273 | $this->_from = " |
274 | FROM civicrm_financial_item {$this->_aliases['civicrm_financial_item']} | |
275 | INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred | |
276 | ON {$this->_aliases['civicrm_financial_item']}.financial_account_id = entity_financial_account_deferred.financial_account_id | |
277 | AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type' | |
278 | AND entity_financial_account_deferred.account_relationship = {$deferredRelationship} | |
279 | INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']} | |
280 | ON entity_financial_account_deferred.financial_account_id = {$this->_aliases['civicrm_financial_account']}.id | |
281 | INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue | |
282 | ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id | |
283 | AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table | |
284 | INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account_1']} | |
285 | ON entity_financial_account_revenue.financial_account_id = {$this->_aliases['civicrm_financial_account_1']}.id | |
286 | AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship | |
287 | INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item | |
288 | ON entity_financial_trxn_item.entity_id = {$this->_aliases['civicrm_financial_item']}.id | |
289 | AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item' | |
290 | INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn_1']} | |
bb40e744 | 291 | ON {$this->_aliases['civicrm_financial_trxn_1']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}.id |
f452d72c | 292 | AND {$this->_aliases['civicrm_financial_trxn_1']}.id = entity_financial_trxn_item.financial_trxn_id |
da345655 E |
293 | INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution |
294 | ON financial_trxn_contribution.financial_trxn_id = {$this->_aliases['civicrm_financial_trxn_1']}.id | |
295 | AND financial_trxn_contribution.entity_table = 'civicrm_contribution' | |
296 | INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution | |
297 | ON entity_financial_trxn_contribution.entity_id = {$this->_aliases['civicrm_financial_item']}.id | |
f452d72c | 298 | AND entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item' |
da345655 E |
299 | INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']} |
300 | ON {$this->_aliases['civicrm_financial_trxn']}.id = entity_financial_trxn_contribution.financial_trxn_id | |
301 | AND ({$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount)) . ") | |
302 | OR {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NULL) | |
303 | INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
304 | ON {$this->_aliases['civicrm_contribution']}.id = financial_trxn_contribution.entity_id | |
f452d72c | 305 | INNER JOIN civicrm_line_item line_item |
da345655 | 306 | ON line_item.contribution_id = {$this->_aliases['civicrm_contribution']}.id |
e29d24ad | 307 | AND line_item.financial_type_id = entity_financial_account_deferred.entity_id |
da345655 E |
308 | LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']} |
309 | ON CASE | |
310 | WHEN line_item.entity_table = 'civicrm_participant' | |
311 | THEN line_item.entity_id = {$this->_aliases['civicrm_participant']}.id | |
312 | ELSE {$this->_aliases['civicrm_participant']}.id = 0 | |
313 | END | |
314 | LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']} | |
315 | ON {$this->_aliases['civicrm_participant']}.event_id = {$this->_aliases['civicrm_event']}.id"; | |
316 | ||
317 | if ($this->isTableSelected('civicrm_contact')) { | |
318 | $this->_from .= " | |
319 | INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} | |
320 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id"; | |
321 | } | |
322 | ||
323 | if ($this->isTableSelected('civicrm_membership')) { | |
324 | $this->_from .= " | |
325 | LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} | |
326 | ON CASE | |
327 | WHEN line_item.entity_table = 'civicrm_membership' | |
328 | THEN line_item.entity_id = {$this->_aliases['civicrm_membership']}.id | |
329 | ELSE {$this->_aliases['civicrm_membership']}.id = 0 | |
330 | END"; | |
331 | } | |
5f2d82f6 | 332 | |
da345655 | 333 | if ($this->isTableSelected('civicrm_batch')) { |
5f2d82f6 PN |
334 | $this->_from .= " |
335 | LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']} | |
0860078d | 336 | ON {$this->_aliases['civicrm_batch']}.entity_id = {$this->_aliases['civicrm_financial_trxn']}.id |
da345655 | 337 | AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'"; |
5f2d82f6 | 338 | } |
3c6d6452 PN |
339 | } |
340 | ||
f5aedad3 PN |
341 | /** |
342 | * Post process function. | |
343 | */ | |
a3bba030 | 344 | public function postProcess() { |
86f01e6c | 345 | $this->_noFields = TRUE; |
f5aedad3 PN |
346 | parent::postProcess(); |
347 | } | |
a3bba030 | 348 | |
f5aedad3 PN |
349 | /** |
350 | * Set limit. | |
351 | * | |
11630f6a | 352 | * @param int|null $rowCount |
f5aedad3 | 353 | */ |
11630f6a SL |
354 | public function limit($rowCount = NULL) { |
355 | $rowCount = $rowCount ?? $this->getRowCount(); | |
f5aedad3 | 356 | $this->_limit = NULL; |
3c6d6452 PN |
357 | } |
358 | ||
86f01e6c PN |
359 | /** |
360 | * Build where clause. | |
361 | */ | |
362 | public function where() { | |
363 | parent::where(); | |
364 | $startDate = date('Y-m-01'); | |
365 | $endDate = date('Y-m-t', strtotime(date('ymd') . '+11 month')); | |
366 | $this->_where .= " AND {$this->_aliases['civicrm_financial_trxn_1']}.trxn_date BETWEEN '{$startDate}' AND '{$endDate}'"; | |
367 | } | |
368 | ||
f5aedad3 PN |
369 | /** |
370 | * Build group by clause. | |
371 | */ | |
3c6d6452 | 372 | public function groupBy() { |
a3bba030 | 373 | $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_financial_account']}.id, {$this->_aliases['civicrm_financial_account_1']}.id, {$this->_aliases['civicrm_financial_item']}.id"; |
68aa5bd7 PN |
374 | $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect( |
375 | $this->_selectClauses, | |
be2fb01f | 376 | [ |
68aa5bd7 PN |
377 | "{$this->_aliases['civicrm_financial_account_1']}.id", |
378 | "{$this->_aliases['civicrm_financial_item']}.id", | |
be2fb01f | 379 | ] |
68aa5bd7 | 380 | ); |
3c6d6452 PN |
381 | } |
382 | ||
da345655 E |
383 | /** |
384 | * Modify column headers. | |
385 | */ | |
386 | public function modifyColumnHeaders() { | |
387 | // Re-order the columns in a custom order defined below. | |
be2fb01f | 388 | $sortArray = [ |
da345655 E |
389 | 'civicrm_batch_batch_id', |
390 | 'civicrm_financial_trxn_status_id', | |
391 | 'civicrm_financial_trxn_trxn_date', | |
392 | 'civicrm_contribution_receive_date', | |
393 | 'civicrm_contribution_cancel_date', | |
394 | 'civicrm_contribution_revenue_recognition_date', | |
395 | 'civicrm_financial_trxn_total_amount', | |
396 | 'civicrm_financial_item_description', | |
397 | 'civicrm_contribution_contact_id', | |
398 | 'civicrm_contact_display_name', | |
399 | 'civicrm_contribution_source', | |
be2fb01f | 400 | ]; |
da345655 E |
401 | // Only re-order selected columns. |
402 | $sortArray = array_flip(array_intersect_key(array_flip($sortArray), $this->_columnHeaders)); | |
403 | ||
404 | // Re-ordering. | |
405 | $this->_columnHeaders = array_merge(array_flip($sortArray), $this->_columnHeaders); | |
406 | ||
407 | // Add months to the columns. | |
408 | if ($this->_params['revenue_recognition_date_toggle_value']) { | |
be2fb01f | 409 | $this->_columnHeaders[date('M, Y', strtotime(date('Y-m-d')))] = [ |
da345655 E |
410 | 'title' => date('M, Y', strtotime(date('Y-m-d'))), |
411 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f | 412 | ]; |
da345655 E |
413 | } |
414 | else { | |
415 | for ($i = 0; $i < 12; $i++) { | |
be2fb01f | 416 | $this->_columnHeaders[date('M, Y', strtotime(date('Y-m-d') . "+{$i} month"))] = [ |
da345655 E |
417 | 'title' => date('M, Y', strtotime(date('Y-m-d') . "+{$i} month")), |
418 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f | 419 | ]; |
da345655 E |
420 | } |
421 | } | |
422 | } | |
423 | ||
3c6d6452 PN |
424 | /** |
425 | * Build output rows. | |
426 | * | |
427 | * @param string $sql | |
428 | * @param array $rows | |
9d1de479 | 429 | * |
430 | * @throws \CRM_Core_Exception | |
3c6d6452 PN |
431 | */ |
432 | public function buildRows($sql, &$rows) { | |
433 | $dao = CRM_Core_DAO::executeQuery($sql); | |
da345655 E |
434 | |
435 | // use this method to modify $this->_columnHeaders | |
436 | $this->modifyColumnHeaders(); | |
437 | ||
438 | // Get custom date format. | |
439 | $dateFormat = Civi::settings()->get('dateformatFinancialBatch'); | |
440 | ||
3c6d6452 | 441 | if (!is_array($rows)) { |
be2fb01f | 442 | $rows = []; |
3c6d6452 | 443 | } |
da345655 | 444 | |
3c6d6452 | 445 | while ($dao->fetch()) { |
be2fb01f | 446 | $row = []; |
da345655 E |
447 | foreach ($this->_columnHeaders as $key => $value) { |
448 | $arraykey = $dao->civicrm_financial_account_id . '_' . $dao->civicrm_financial_account_1_id; | |
449 | ||
450 | if (property_exists($dao, $key)) { | |
451 | if (CRM_Utils_Array::value('type', $value) & CRM_Utils_Type::T_DATE) { | |
452 | $row[$key] = CRM_Utils_Date::customFormat($dao->$key, $dateFormat); | |
453 | } | |
454 | elseif (CRM_Utils_Array::value('type', $value) & CRM_Utils_Type::T_MONEY) { | |
9d1de479 | 455 | $values = []; |
456 | foreach (explode(',', $dao->$key) as $moneyValue) { | |
457 | $values[] = CRM_Utils_Money::format($moneyValue); | |
458 | } | |
459 | $row[$key] = implode(',', $values); | |
da345655 E |
460 | } |
461 | else { | |
462 | $row[$key] = $dao->$key; | |
463 | } | |
464 | } | |
465 | ||
466 | $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id] = $row; | |
f422493a | 467 | $rows[$arraykey]['label'] = "Deferred Revenue Account: {$dao->civicrm_financial_account_name} ({$dao->civicrm_financial_account_accounting_code}), Revenue Account: {$dao->civicrm_financial_account_1_name} {$dao->civicrm_financial_account_1_accounting_code}"; |
da345655 E |
468 | $trxnDate = explode(',', $dao->civicrm_financial_trxn_1_trxn_date); |
469 | $trxnAmount = explode(',', $dao->civicrm_financial_trxn_1_total_amount); | |
9d1de479 | 470 | foreach ($trxnDate as $trxnKey => $date) { |
da345655 | 471 | $keyDate = date('M, Y', strtotime($date)); |
9d1de479 | 472 | $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id][$keyDate] = CRM_Utils_Money::format($trxnAmount[$trxnKey]); |
da345655 | 473 | } |
3c6d6452 | 474 | } |
da345655 E |
475 | } |
476 | } | |
477 | ||
478 | /** | |
479 | * Alter display of rows. | |
480 | * | |
481 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
482 | * such as rendering contacts as links. | |
483 | * | |
484 | * @param array $rows | |
485 | * Rows generated by SQL, with an array for each row. | |
486 | */ | |
487 | public function alterDisplay(&$rows) { | |
488 | $entryFound = FALSE; | |
489 | $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name'); | |
490 | ||
491 | foreach ($rows as &$entry) { | |
492 | foreach ($entry['rows'] as $rowNum => &$row) { | |
493 | ||
494 | // convert transaction status id to status name | |
495 | if ($status = CRM_Utils_Array::value('civicrm_financial_trxn_status_id', $row)) { | |
496 | $row['civicrm_financial_trxn_status_id'] = CRM_Core_PseudoConstant::getLabel('CRM_Core_BAO_FinancialTrxn', 'status_id', $status); | |
497 | $entryFound = TRUE; | |
498 | } | |
499 | ||
500 | // convert batch id to batch title | |
501 | if ($batchId = CRM_Utils_Array::value('civicrm_batch_batch_id', $row)) { | |
502 | $row['civicrm_batch_batch_id'] = $this->getLabels($batchId, 'CRM_Batch_BAO_EntityBatch', 'batch_id'); | |
503 | $entryFound = TRUE; | |
504 | } | |
505 | ||
506 | // add hotlink for contribution | |
507 | if ($amount = CRM_Utils_Array::value('civicrm_financial_trxn_total_amount', $row)) { | |
508 | $contributionUrl = CRM_Utils_System::url("civicrm/contact/view/contribution", | |
509 | 'reset=1&action=view&cid=' . $row['civicrm_contact_id'] . '&id=' . $row['civicrm_contribution_contribution_id'], | |
510 | $this->_absoluteUrl | |
511 | ); | |
512 | $row['civicrm_financial_trxn_total_amount'] = "<a href={$contributionUrl}>{$amount}</a>"; | |
513 | if ($contributionId = CRM_Utils_Array::value('civicrm_contribution_id', $row)) { | |
514 | $row['civicrm_contribution_id'] = "<a href={$contributionUrl}>{$contributionId}</a>"; | |
515 | } | |
516 | $entryFound = TRUE; | |
517 | } | |
518 | ||
519 | // add hotlink for contact | |
520 | if ($contactName = CRM_Utils_Array::value('civicrm_contact_display_name', $row)) { | |
521 | $contactUrl = CRM_Utils_System::url("civicrm/contact/view", | |
522 | 'reset=1&cid=' . $row['civicrm_contact_id'], | |
523 | $this->_absoluteUrl | |
524 | ); | |
525 | $row['civicrm_contact_display_name'] = "<a href={$contactUrl}>{$contactName}</a>"; | |
526 | $entryFound = TRUE; | |
527 | } | |
528 | ||
529 | if ($contactId = CRM_Utils_Array::value('civicrm_contribution_contact_id', $row)) { | |
530 | $contactUrl = CRM_Utils_System::url("civicrm/contact/view", | |
531 | 'reset=1&cid=' . $row['civicrm_contact_id'], | |
532 | $this->_absoluteUrl | |
533 | ); | |
534 | $row['civicrm_contribution_contact_id'] = "<a href={$contactUrl}>{$contactId}</a>"; | |
535 | $entryFound = TRUE; | |
86f01e6c | 536 | } |
3c6d6452 PN |
537 | } |
538 | } | |
3c6d6452 | 539 | } |
3c6d6452 PN |
540 | |
541 | } |