Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
39de6fd5 | 4 | | CiviCRM version 4.6 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
e7112fa7 | 6 | | Copyright CiviCRM LLC (c) 2004-2015 | |
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 | |
e7112fa7 | 31 | * @copyright CiviCRM LLC (c) 2004-2015 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { | |
36 | protected $_addressField = FALSE; | |
37 | ||
38 | protected $_emailField = FALSE; | |
39 | ||
40 | protected $_summary = NULL; | |
41 | ||
42 | protected $_customGroupExtends = array( | |
49e1ea9f | 43 | 'Contact', |
44 | 'Individual', | |
45 | 'Contribution', | |
21dfd5f5 | 46 | 'Membership', |
9d72cede | 47 | ); |
2f4c2f5d | 48 | |
74cf4551 | 49 | /** |
74cf4551 | 50 | */ |
00be9182 | 51 | public function __construct() { |
49e1ea9f | 52 | $this->_autoIncludeIndexedFieldsAsOrderBys = 1; |
6a488035 | 53 | $this->_columns = array( |
9d72cede | 54 | 'civicrm_contact' => array( |
6a488035 | 55 | 'dao' => 'CRM_Contact_DAO_Contact', |
9d72cede EM |
56 | 'fields' => array( |
57 | 'sort_name' => array( | |
58 | 'title' => ts('Contact Name'), | |
6a488035 TO |
59 | 'required' => TRUE, |
60 | 'no_repeat' => TRUE, | |
61 | ), | |
49e1ea9f | 62 | 'first_name' => array( |
63 | 'title' => ts('First Name'), | |
64 | ), | |
65 | 'middle_name' => array( | |
66 | 'title' => ts('Middle Name'), | |
67 | ), | |
68 | 'last_name' => array( | |
69 | 'title' => ts('Last Name'), | |
70 | ), | |
9d72cede | 71 | 'id' => array( |
6a488035 TO |
72 | 'no_display' => TRUE, |
73 | 'required' => TRUE, | |
74 | ), | |
49e1ea9f | 75 | 'gender_id' => array( |
76 | 'title' => ts('Gender'), | |
77 | ), | |
78 | 'birth_date' => array( | |
79 | 'title' => ts('Birth Date'), | |
80 | ), | |
81 | 'age' => array( | |
82 | 'title' => ts('Age'), | |
83 | 'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())', | |
84 | ), | |
85 | 'contact_type' => array( | |
86 | 'title' => ts('Contact Type'), | |
87 | ), | |
88 | 'contact_sub_type' => array( | |
89 | 'title' => ts('Contact Subtype'), | |
90 | ), | |
91 | ), | |
92 | 'grouping' => 'contact-fields', | |
93 | 'order_bys' => array( | |
94 | 'sort_name' => array( | |
95 | 'title' => ts('Last Name, First Name'), | |
96 | 'default' => '1', | |
97 | 'default_weight' => '0', | |
98 | 'default_order' => 'ASC', | |
99 | ), | |
100 | 'first_name' => array( | |
101 | 'name' => 'first_name', | |
102 | 'title' => ts('First Name'), | |
103 | ), | |
104 | 'gender_id' => array( | |
105 | 'name' => 'gender_id', | |
106 | 'title' => ts('Gender'), | |
107 | ), | |
108 | 'birth_date' => array( | |
109 | 'name' => 'birth_date', | |
110 | 'title' => ts('Birth Date'), | |
111 | ), | |
9d72cede | 112 | 'contact_type' => array( |
30f85891 RN |
113 | 'title' => ts('Contact Type'), |
114 | ), | |
9d72cede | 115 | 'contact_sub_type' => array( |
b8f96eb8 | 116 | 'title' => ts('Contact Subtype'), |
30f85891 | 117 | ), |
6a488035 | 118 | ), |
9d72cede EM |
119 | 'filters' => array( |
120 | 'sort_name' => array( | |
121 | 'title' => ts('Contact Name'), | |
6a488035 TO |
122 | 'operator' => 'like', |
123 | ), | |
9d72cede EM |
124 | 'id' => array( |
125 | 'title' => ts('Contact ID'), | |
6a488035 TO |
126 | 'no_display' => TRUE, |
127 | ), | |
49e1ea9f | 128 | 'gender_id' => array( |
129 | 'title' => ts('Gender'), | |
130 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
131 | 'options' => CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'), | |
132 | ), | |
133 | 'birth_date' => array( | |
134 | 'title' => ts('Birth Date'), | |
135 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
136 | ), | |
137 | 'contact_type' => array( | |
138 | 'title' => ts('Contact Type'), | |
139 | ), | |
140 | 'contact_sub_type' => array( | |
141 | 'title' => ts('Contact Subtype'), | |
444e4ce8 JL |
142 | ), |
143 | ), | |
6a488035 | 144 | ), |
9d72cede | 145 | 'civicrm_membership' => array( |
6a488035 | 146 | 'dao' => 'CRM_Member_DAO_Membership', |
9d72cede EM |
147 | 'fields' => array( |
148 | 'id' => array( | |
149 | 'title' => ts('Membership #'), | |
6a488035 TO |
150 | 'no_display' => TRUE, |
151 | 'required' => TRUE, | |
152 | ), | |
153 | ), | |
154 | ), | |
086ca649 PN |
155 | 'civicrm_financial_account' => array( |
156 | 'dao' => 'CRM_Financial_DAO_FinancialAccount', | |
157 | 'fields' => array( | |
158 | 'debit_accounting_code' => array( | |
159 | 'title' => ts('Financial Account Code - Debit'), | |
9d72cede | 160 | 'name' => 'accounting_code', |
086ca649 PN |
161 | 'alias' => 'financial_account_civireport_debit', |
162 | 'default' => TRUE, | |
086ca649 PN |
163 | ), |
164 | 'credit_accounting_code' => array( | |
165 | 'title' => ts('Financial Account Code - Credit'), | |
9d72cede | 166 | 'name' => 'accounting_code', |
086ca649 PN |
167 | 'alias' => 'financial_account_civireport_credit', |
168 | 'default' => TRUE, | |
086ca649 PN |
169 | ), |
170 | 'debit_name' => array( | |
171 | 'title' => ts('Financial Account Name - Debit'), | |
9d72cede | 172 | 'name' => 'name', |
086ca649 PN |
173 | 'alias' => 'financial_account_civireport_debit', |
174 | 'default' => TRUE, | |
175 | ), | |
176 | 'credit_name' => array( | |
177 | 'title' => ts('Financial Account Name - Credit'), | |
9d72cede | 178 | 'name' => 'name', |
086ca649 PN |
179 | 'alias' => 'financial_account_civireport_credit', |
180 | 'default' => TRUE, | |
181 | ), | |
182 | ), | |
183 | 'filters' => array( | |
184 | 'debit_accounting_code' => array( | |
185 | 'title' => ts('Financial Account Code - Debit'), | |
186 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
187 | 'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'), | |
9d72cede | 188 | 'name' => 'accounting_code', |
086ca649 PN |
189 | 'alias' => 'financial_account_civireport_debit', |
190 | ), | |
191 | 'credit_accounting_code' => array( | |
2f4c2f5d | 192 | 'title' => ts('Financial Account Code - Credit'), |
086ca649 PN |
193 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
194 | 'options' => CRM_Contribute_PseudoConstant::financialAccount(NULL, NULL, 'accounting_code', 'accounting_code'), | |
195 | ), | |
196 | 'debit_name' => array( | |
197 | 'title' => ts('Financial Account Name - Debit'), | |
198 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
199 | 'options' => CRM_Contribute_PseudoConstant::financialAccount(), | |
9d72cede | 200 | 'name' => 'id', |
086ca649 PN |
201 | 'alias' => 'financial_account_civireport_debit', |
202 | ), | |
203 | 'credit_name' => array( | |
2f4c2f5d | 204 | 'title' => ts('Financial Account Name - Credit'), |
086ca649 PN |
205 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
206 | 'options' => CRM_Contribute_PseudoConstant::financialAccount(), | |
207 | ), | |
208 | ), | |
2f4c2f5d | 209 | ), |
086ca649 PN |
210 | 'civicrm_line_item' => array( |
211 | 'dao' => 'CRM_Price_DAO_LineItem', | |
212 | 'fields' => array( | |
9d72cede EM |
213 | 'financial_type_id' => array( |
214 | 'title' => ts('Financial Type'), | |
086ca649 PN |
215 | 'default' => TRUE, |
216 | ), | |
217 | ), | |
218 | 'filters' => array( | |
2f4c2f5d | 219 | 'financial_type_id' => array( |
220 | 'title' => ts('Financial Type'), | |
086ca649 PN |
221 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
222 | 'options' => CRM_Contribute_PseudoConstant::financialType(), | |
223 | ), | |
224 | ), | |
444e4ce8 JL |
225 | 'order_bys' => array( |
226 | 'financial_type_id' => array('title' => ts('Financial Type')), | |
227 | ), | |
2f4c2f5d | 228 | ), |
9d72cede | 229 | 'civicrm_contribution' => array( |
6a488035 | 230 | 'dao' => 'CRM_Contribute_DAO_Contribution', |
9d72cede | 231 | 'fields' => array( |
6e3c3a7d | 232 | 'receive_date' => array( |
21dfd5f5 | 233 | 'default' => TRUE, |
6a488035 | 234 | ), |
6e3c3a7d PN |
235 | 'invoice_id' => array( |
236 | 'title' => ts('Invoice ID'), | |
6a488035 TO |
237 | 'default' => TRUE, |
238 | ), | |
9d72cede EM |
239 | 'contribution_status_id' => array( |
240 | 'title' => ts('Contribution Status'), | |
6a488035 TO |
241 | 'default' => TRUE, |
242 | ), | |
9d72cede EM |
243 | 'id' => array( |
244 | 'title' => ts('Contribution #'), | |
6a488035 TO |
245 | 'default' => TRUE, |
246 | ), | |
247 | ), | |
9d72cede EM |
248 | 'filters' => array( |
249 | 'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE), | |
250 | 'contribution_status_id' => array( | |
251 | 'title' => ts('Contribution Status'), | |
6a488035 TO |
252 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
253 | 'options' => CRM_Contribute_PseudoConstant::contributionStatus(), | |
254 | 'default' => array(1), | |
255 | ), | |
6a488035 | 256 | ), |
444e4ce8 JL |
257 | 'order_bys' => array( |
258 | 'contribution_id' => array('title' => ts('Contribution #')), | |
259 | 'contribution_status_id' => array('title' => ts('Contribution Status')), | |
260 | ), | |
6a488035 | 261 | 'grouping' => 'contri-fields', |
086ca649 | 262 | ), |
4d9dd529 PN |
263 | 'civicrm_financial_trxn' => array( |
264 | 'dao' => 'CRM_Financial_DAO_FinancialTrxn', | |
265 | 'fields' => array( | |
266 | 'check_number' => array( | |
267 | 'title' => ts('Cheque #'), | |
6a488035 TO |
268 | 'default' => TRUE, |
269 | ), | |
9d72cede EM |
270 | 'payment_instrument_id' => array( |
271 | 'title' => ts('Payment Instrument'), | |
4d9dd529 PN |
272 | 'default' => TRUE, |
273 | ), | |
652a526e | 274 | 'currency' => array( |
9d72cede EM |
275 | 'required' => TRUE, |
276 | 'no_display' => TRUE, | |
2f4c2f5d | 277 | ), |
6e3c3a7d PN |
278 | 'trxn_date' => array( |
279 | 'title' => ts('Transaction Date'), | |
086ca649 PN |
280 | 'default' => TRUE, |
281 | 'type' => CRM_Utils_Type::T_DATE, | |
6e3c3a7d PN |
282 | ), |
283 | 'trxn_id' => array( | |
284 | 'title' => ts('Trans #'), | |
285 | 'default' => TRUE, | |
286 | ), | |
4d9dd529 | 287 | ), |
9d72cede | 288 | 'filters' => array( |
4d9dd529 | 289 | 'payment_instrument_id' => array( |
6e3c3a7d | 290 | 'title' => ts('Payment Instrument'), |
4d9dd529 PN |
291 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
292 | 'options' => CRM_Contribute_PseudoConstant::paymentInstrument(), | |
293 | ), | |
652a526e | 294 | 'currency' => array( |
9d72cede EM |
295 | 'title' => 'Currency', |
296 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
297 | 'options' => CRM_Core_OptionGroup::values('currencies_enabled'), | |
298 | 'default' => NULL, | |
299 | 'type' => CRM_Utils_Type::T_STRING, | |
652a526e | 300 | ), |
2f4c2f5d | 301 | 'trxn_date' => array( |
6e3c3a7d PN |
302 | 'title' => ts('Transaction Date'), |
303 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
304 | 'type' => CRM_Utils_Type::T_DATE, | |
305 | ), | |
4d9dd529 | 306 | ), |
444e4ce8 JL |
307 | 'order_bys' => array( |
308 | 'payment_instrument_id' => array('title' => ts('Payment Instrument')), | |
309 | ), | |
2f4c2f5d | 310 | ), |
6a488035 TO |
311 | 'civicrm_entity_financial_trxn' => array( |
312 | 'dao' => 'CRM_Financial_DAO_EntityFinancialTrxn', | |
313 | 'fields' => array( | |
314 | 'amount' => array( | |
315 | 'title' => ts('Amount'), | |
316 | 'default' => TRUE, | |
652a526e | 317 | 'type' => CRM_Utils_Type::T_STRING, |
6a488035 TO |
318 | ), |
319 | ), | |
9d72cede EM |
320 | 'filters' => array( |
321 | 'amount' => array('title' => ts('Amount')), | |
4d9dd529 | 322 | ), |
2f4c2f5d | 323 | ), |
6a488035 | 324 | ); |
444e4ce8 | 325 | |
16e2e80c | 326 | $this->_groupFilter = TRUE; |
444e4ce8 | 327 | $this->_tagFilter = TRUE; |
6a488035 TO |
328 | parent::__construct(); |
329 | } | |
330 | ||
00be9182 | 331 | public function preProcess() { |
6a488035 TO |
332 | parent::preProcess(); |
333 | } | |
334 | ||
00be9182 | 335 | public function select() { |
6a488035 TO |
336 | $select = array(); |
337 | ||
338 | $this->_columnHeaders = array(); | |
339 | foreach ($this->_columns as $tableName => $table) { | |
340 | if (array_key_exists('fields', $table)) { | |
341 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
342 | if (!empty($field['required']) || |
343 | !empty($this->_params['fields'][$fieldName]) | |
344 | ) { | |
086ca649 | 345 | switch ($fieldName) { |
84178120 | 346 | case 'credit_accounting_code': |
9d72cede | 347 | $select[] = " CASE |
086ca649 PN |
348 | WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL |
349 | THEN {$this->_aliases['civicrm_financial_account']}_credit_1.accounting_code | |
350 | ELSE {$this->_aliases['civicrm_financial_account']}_credit_2.accounting_code | |
351 | END AS civicrm_financial_account_credit_accounting_code "; | |
9d72cede | 352 | break; |
84178120 TO |
353 | |
354 | case 'amount': | |
9d72cede | 355 | $select[] = " CASE |
086ca649 PN |
356 | WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL |
357 | THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount | |
358 | ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount | |
359 | END AS civicrm_entity_financial_trxn_amount "; | |
9d72cede | 360 | break; |
84178120 TO |
361 | |
362 | case 'credit_name': | |
9d72cede | 363 | $select[] = " CASE |
086ca649 PN |
364 | WHEN {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NOT NULL |
365 | THEN {$this->_aliases['civicrm_financial_account']}_credit_1.name | |
366 | ELSE {$this->_aliases['civicrm_financial_account']}_credit_2.name | |
367 | END AS civicrm_financial_account_credit_name "; | |
9d72cede | 368 | break; |
84178120 TO |
369 | |
370 | default: | |
9d72cede EM |
371 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; |
372 | break; | |
6a488035 TO |
373 | } |
374 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title']; | |
375 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field); | |
376 | } | |
377 | } | |
378 | } | |
379 | } | |
380 | ||
652a526e | 381 | $this->_select = 'SELECT ' . implode(', ', $select) . ' '; |
6a488035 TO |
382 | } |
383 | ||
00be9182 | 384 | public function from() { |
6a488035 TO |
385 | $this->_from = NULL; |
386 | ||
387 | $this->_from = "FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} | |
388 | INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} | |
2f4c2f5d | 389 | ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND |
6a488035 TO |
390 | {$this->_aliases['civicrm_contribution']}.is_test = 0 |
391 | LEFT JOIN civicrm_membership_payment payment | |
086ca649 | 392 | ON ( {$this->_aliases['civicrm_contribution']}.id = payment.contribution_id ) |
6a488035 | 393 | LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} |
2f4c2f5d | 394 | ON payment.membership_id = {$this->_aliases['civicrm_membership']}.id |
6a488035 | 395 | LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']} |
2f4c2f5d | 396 | ON ({$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.entity_id AND |
6a488035 | 397 | {$this->_aliases['civicrm_entity_financial_trxn']}.entity_table = 'civicrm_contribution') |
4d9dd529 PN |
398 | LEFT JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']} |
399 | ON {$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}.financial_trxn_id | |
6a488035 | 400 | LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_debit |
4d9dd529 | 401 | ON {$this->_aliases['civicrm_financial_trxn']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}_debit.id |
6a488035 | 402 | LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_1 |
4d9dd529 | 403 | ON {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_1.id |
6a488035 | 404 | LEFT JOIN civicrm_entity_financial_trxn {$this->_aliases['civicrm_entity_financial_trxn']}_item |
2f4c2f5d | 405 | ON ({$this->_aliases['civicrm_financial_trxn']}.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.financial_trxn_id AND |
6a488035 | 406 | {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_table = 'civicrm_financial_item') |
4d9dd529 | 407 | LEFT JOIN civicrm_financial_item fitem |
6a488035 | 408 | ON fitem.id = {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id |
4d9dd529 | 409 | LEFT JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}_credit_2 |
6a488035 | 410 | ON fitem.financial_account_id = {$this->_aliases['civicrm_financial_account']}_credit_2.id |
4d9dd529 | 411 | LEFT JOIN civicrm_line_item {$this->_aliases['civicrm_line_item']} |
6a488035 TO |
412 | ON fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item' "; |
413 | } | |
414 | ||
00be9182 | 415 | public function orderBy() { |
444e4ce8 JL |
416 | parent::orderBy(); |
417 | ||
418 | // please note this will just add the order-by columns to select query, and not display in column-headers. | |
419 | // This is a solution to not throw fatal errors when there is a column in order-by, not present in select/display columns. | |
420 | foreach ($this->_orderByFields as $orderBy) { | |
9d72cede EM |
421 | if (!array_key_exists($orderBy['name'], $this->_params['fields']) && |
422 | empty($orderBy['section']) | |
423 | ) { | |
444e4ce8 JL |
424 | $this->_select .= ", {$orderBy['dbAlias']} as {$orderBy['tplField']}"; |
425 | } | |
426 | } | |
6a488035 TO |
427 | } |
428 | ||
00be9182 | 429 | public function where() { |
086ca649 PN |
430 | foreach ($this->_columns as $tableName => $table) { |
431 | if (array_key_exists('filters', $table)) { | |
432 | foreach ($table['filters'] as $fieldName => $field) { | |
433 | $clause = NULL; | |
434 | if ($fieldName == 'credit_accounting_code') { | |
435 | $field['dbAlias'] = "CASE | |
436 | WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL | |
437 | THEN financial_account_civireport_credit_1.accounting_code | |
2f4c2f5d | 438 | ELSE financial_account_civireport_credit_2.accounting_code |
086ca649 PN |
439 | END"; |
440 | } | |
4c9b6178 | 441 | elseif ($fieldName == 'credit_name') { |
086ca649 PN |
442 | $field['dbAlias'] = "CASE |
443 | WHEN financial_trxn_civireport.from_financial_account_id IS NOT NULL | |
444 | THEN financial_account_civireport_credit_1.id | |
2f4c2f5d | 445 | ELSE financial_account_civireport_credit_2.id |
086ca649 PN |
446 | END"; |
447 | } | |
448 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
449 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); | |
9d72cede EM |
450 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); |
451 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); | |
086ca649 PN |
452 | |
453 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
454 | } | |
455 | else { | |
456 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); | |
457 | if ($op) { | |
458 | $clause = $this->whereClause($field, | |
459 | $op, | |
460 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
461 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
462 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
463 | ); | |
464 | } | |
465 | } | |
466 | if (!empty($clause)) { | |
467 | $clauses[] = $clause; | |
468 | } | |
469 | } | |
470 | } | |
471 | } | |
472 | if (empty($clauses)) { | |
652a526e | 473 | $this->_where = 'WHERE ( 1 )'; |
086ca649 PN |
474 | } |
475 | else { | |
652a526e | 476 | $this->_where = 'WHERE ' . implode(' AND ', $clauses); |
086ca649 PN |
477 | } |
478 | } | |
479 | ||
00be9182 | 480 | public function postProcess() { |
6a488035 TO |
481 | // get the acl clauses built before we assemble the query |
482 | $this->buildACLClause($this->_aliases['civicrm_contact']); | |
483 | parent::postProcess(); | |
484 | } | |
485 | ||
1f557688 SB |
486 | public function groupBy() { |
487 | $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_line_item']}.id "; | |
488 | } | |
489 | ||
74cf4551 EM |
490 | /** |
491 | * @param $rows | |
492 | * | |
493 | * @return array | |
494 | */ | |
00be9182 | 495 | public function statistics(&$rows) { |
6a488035 | 496 | $statistics = parent::statistics($rows); |
1f557688 SB |
497 | $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution'); |
498 | $select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency, | |
499 | CASE | |
500 | WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL | |
501 | THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount | |
502 | ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount | |
503 | END as amount | |
652a526e | 504 | "; |
505 | ||
1f557688 SB |
506 | $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS |
507 | {$select} {$this->_from} {$this->_where} {$this->_groupBy} "; | |
508 | CRM_Core_DAO::executeQuery($tempQuery); | |
6a488035 | 509 | |
1f557688 SB |
510 | $sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency |
511 | FROM {$tempTableName} | |
512 | GROUP BY currency"; | |
6a488035 | 513 | $dao = CRM_Core_DAO::executeQuery($sql); |
49e1ea9f | 514 | $amount = $avg = array(); |
652a526e | 515 | while ($dao->fetch()) { |
516 | $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency); | |
9d72cede | 517 | $avg[] = CRM_Utils_Money::format(round(($dao->amount / |
353ffa53 | 518 | $dao->count), 2), $dao->currency); |
6a488035 TO |
519 | } |
520 | ||
652a526e | 521 | $statistics['counts']['amount'] = array( |
9d72cede EM |
522 | 'value' => implode(', ', $amount), |
523 | 'title' => 'Total Amount', | |
524 | 'type' => CRM_Utils_Type::T_STRING, | |
652a526e | 525 | ); |
526 | $statistics['counts']['avg'] = array( | |
527 | 'value' => implode(', ', $avg), | |
528 | 'title' => 'Average', | |
529 | 'type' => CRM_Utils_Type::T_STRING, | |
530 | ); | |
6a488035 TO |
531 | return $statistics; |
532 | } | |
533 | ||
74cf4551 | 534 | /** |
ced9bfed EM |
535 | * Alter display of rows. |
536 | * | |
537 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
538 | * such as rendering contacts as links. | |
539 | * | |
540 | * @param array $rows | |
541 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 542 | */ |
00be9182 | 543 | public function alterDisplay(&$rows) { |
6a488035 TO |
544 | $contributionTypes = CRM_Contribute_PseudoConstant::financialType(); |
545 | $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument(); | |
4d9dd529 | 546 | $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(); |
6a488035 | 547 | foreach ($rows as $rowNum => $row) { |
6a488035 | 548 | // convert display name to links |
9d72cede EM |
549 | if (array_key_exists('civicrm_contact_sort_name', $row) && |
550 | !empty($rows[$rowNum]['civicrm_contact_sort_name']) && | |
6a488035 TO |
551 | array_key_exists('civicrm_contact_id', $row) |
552 | ) { | |
652a526e | 553 | $url = CRM_Utils_System::url('civicrm/contact/view', |
6a488035 TO |
554 | 'reset=1&cid=' . $row['civicrm_contact_id'], |
555 | $this->_absoluteUrl | |
556 | ); | |
557 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
652a526e | 558 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.'); |
6a488035 TO |
559 | } |
560 | ||
561 | // handle contribution status id | |
4d9dd529 PN |
562 | if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) { |
563 | $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value]; | |
6a488035 TO |
564 | } |
565 | ||
566 | // handle payment instrument id | |
4d9dd529 PN |
567 | if ($value = CRM_Utils_Array::value('civicrm_financial_trxn_payment_instrument_id', $row)) { |
568 | $rows[$rowNum]['civicrm_financial_trxn_payment_instrument_id'] = $paymentInstruments[$value]; | |
6a488035 | 569 | } |
2f4c2f5d | 570 | |
4d9dd529 | 571 | // handle financial type id |
6a488035 TO |
572 | if ($value = CRM_Utils_Array::value('civicrm_line_item_financial_type_id', $row)) { |
573 | $rows[$rowNum]['civicrm_line_item_financial_type_id'] = $contributionTypes[$value]; | |
6a488035 | 574 | } |
652a526e | 575 | if ($value = CRM_Utils_Array::value('civicrm_entity_financial_trxn_amount', $row)) { |
9d72cede | 576 | $rows[$rowNum]['civicrm_entity_financial_trxn_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_entity_financial_trxn_amount'], $rows[$rowNum]['civicrm_financial_trxn_currency']); |
652a526e | 577 | } |
49e1ea9f | 578 | |
579 | //handle gender | |
580 | if (array_key_exists('civicrm_contact_gender_id', $row)) { | |
581 | if ($value = $row['civicrm_contact_gender_id']) { | |
582 | $gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id'); | |
583 | $rows[$rowNum]['civicrm_contact_gender_id'] = $gender[$value]; | |
584 | } | |
585 | $entryFound = TRUE; | |
586 | } | |
587 | ||
588 | // display birthday in the configured custom format | |
589 | if (array_key_exists('civicrm_contact_birth_date', $row)) { | |
590 | $birthDate = $row['civicrm_contact_birth_date']; | |
591 | if ($birthDate) { | |
592 | $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d'); | |
593 | } | |
594 | $entryFound = TRUE; | |
595 | } | |
596 | ||
6a488035 TO |
597 | } |
598 | } | |
96025800 | 599 | |
6a488035 | 600 | } |