3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
19 class CRM_Report_Form_Contribute_DeferredRevenue
extends CRM_Report_Form
{
22 * Holds Deferred Financial Account
25 protected $_deferredFinancialAccount = [];
29 public function __construct() {
30 $this->_exposeContactID
= FALSE;
31 $this->_deferredFinancialAccount
= CRM_Financial_BAO_FinancialAccount
::getAllDeferredFinancialAccount();
33 'civicrm_financial_trxn' => [
34 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
37 'title' => ts('Transaction'),
40 'title' => ts('Transaction Date'),
41 'type' => CRM_Utils_Type
::T_DATE
,
45 'title' => ts('Transaction Amount'),
46 'type' => CRM_Utils_Type
::T_MONEY
,
48 'dbAlias' => 'SUM(financial_trxn_1_civireport.total_amount )',
53 'title' => ts('Transaction Date'),
54 'operatorType' => CRM_Report_Form
::OP_DATE
,
55 'type' => CRM_Utils_Type
::T_DATE
,
59 'civicrm_contribution' => [
60 'dao' => 'CRM_Contribute_DAO_Contribution',
63 'title' => ts('Contribution ID'),
65 'contribution_id' => [
66 'title' => ts('Contribution ID'),
69 'dbAlias' => 'contribution_civireport.id',
72 'title' => ts('Contact ID'),
75 'title' => ts('Source'),
78 'title' => ts('Receive Date'),
79 'type' => CRM_Utils_Type
::T_DATE
,
82 'name' => 'contribution_cancel_date',
83 'title' => ts('Cancel Date'),
84 'type' => CRM_Utils_Type
::T_DATE
,
86 'revenue_recognition_date' => [
87 'title' => ts('Revenue Recognition Date'),
88 'type' => CRM_Utils_Type
::T_DATE
,
93 'title' => ts('Receive Date'),
94 'operatorType' => CRM_Report_Form
::OP_DATE
,
95 'type' => CRM_Utils_Type
::T_DATE
,
98 'title' => ts('Cancel Date'),
99 'operatorType' => CRM_Report_Form
::OP_DATE
,
100 'type' => CRM_Utils_Type
::T_DATE
,
101 'name' => 'contribution_cancel_date',
103 'revenue_recognition_date' => [
104 'title' => ts('Revenue Recognition Date'),
105 'operatorType' => CRM_Report_Form
::OP_DATE
,
106 'type' => CRM_Utils_Type
::T_DATE
,
108 'revenue_recognition_date_toggle' => [
109 'title' => ts("Current month's revenue?"),
110 'type' => CRM_Utils_Type
::T_BOOLEAN
,
112 'pseudofield' => TRUE,
116 'civicrm_financial_account' => [
117 'dao' => 'CRM_Financial_DAO_FinancialAccount',
120 'title' => ts('Deferred Account'),
122 'no_display' => TRUE,
125 'title' => ts('Deferred Account ID'),
127 'no_display' => TRUE,
129 'accounting_code' => [
130 'title' => ts('Deferred Accounting Code'),
132 'no_display' => TRUE,
137 'title' => ts('Deferred Financial Account'),
138 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
139 'options' => $this->_deferredFinancialAccount
,
140 'type' => CRM_Utils_Type
::T_INT
,
144 'civicrm_financial_account_1' => [
145 'dao' => 'CRM_Financial_DAO_FinancialAccount',
148 'title' => ts('Revenue Account'),
150 'no_display' => TRUE,
153 'title' => ts('Revenue Account ID'),
155 'no_display' => TRUE,
157 'accounting_code' => [
158 'title' => ts('Revenue Accounting code'),
159 'no_display' => TRUE,
164 'civicrm_financial_item' => [
165 'dao' => 'CRM_Financial_DAO_FinancialItem',
168 'title' => ts('Status'),
170 'no_display' => TRUE,
173 'title' => ts('Financial Item ID'),
175 'no_display' => TRUE,
178 'title' => ts('Item'),
182 'civicrm_financial_trxn_1' => [
183 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
186 'title' => ts('Deferred Transaction Amount'),
187 'type' => CRM_Utils_Type
::T_MONEY
,
189 'no_display' => TRUE,
190 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.total_amount)',
193 'title' => ts('Deferred Transaction Date'),
195 'no_display' => TRUE,
196 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.trxn_date)',
197 'type' => CRM_Utils_Type
::T_DATE
,
201 'civicrm_contact' => [
202 'dao' => 'CRM_Contact_DAO_Contact',
205 'title' => ts('Contact Name'),
208 'title' => ts('Contact ID'),
210 'no_display' => TRUE,
214 'civicrm_membership' => [
215 'dao' => 'CRM_Member_DAO_Membership',
218 'title' => ts('Start Date'),
219 'dbAlias' => 'IFNULL(membership_civireport.start_date, event_civireport.start_date)',
220 'type' => CRM_Utils_Type
::T_DATE
,
223 'title' => ts('End Date'),
224 'dbdbAlias' => 'IFNULL(membership_civireport.end_date, event_civireport.end_date)',
225 'type' => CRM_Utils_Type
::T_DATE
,
230 'dao' => 'CRM_Event_DAO_Event',
232 'civicrm_participant' => [
233 'dao' => 'CRM_Event_DAO_Participant',
236 'dao' => 'CRM_Batch_DAO_EntityBatch',
237 'grouping' => 'contri-fields',
240 'title' => ts('Batch Title'),
241 'dbAlias' => "GROUP_CONCAT(DISTINCT batch_civireport.batch_id
242 ORDER BY batch_civireport.batch_id SEPARATOR ',')",
248 'title' => ts('Batch Title'),
249 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
250 'options' => CRM_Batch_BAO_Batch
::getBatches(),
251 'type' => CRM_Utils_Type
::T_INT
,
256 parent
::__construct();
260 * Pre process function.
262 * Called prior to build form.
264 public function preProcess() {
265 parent
::preProcess();
271 public function from() {
272 $deferredRelationship = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
273 $revenueRelationship = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
275 FROM civicrm_financial_item {$this->_aliases['civicrm_financial_item']}
276 INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred
277 ON {$this->_aliases['civicrm_financial_item']}.financial_account_id = entity_financial_account_deferred.financial_account_id
278 AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type'
279 AND entity_financial_account_deferred.account_relationship = {$deferredRelationship}
280 INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}
281 ON entity_financial_account_deferred.financial_account_id = {$this->_aliases['civicrm_financial_account']}.id
282 INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue
283 ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id
284 AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table
285 INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account_1']}
286 ON entity_financial_account_revenue.financial_account_id = {$this->_aliases['civicrm_financial_account_1']}.id
287 AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship
288 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item
289 ON entity_financial_trxn_item.entity_id = {$this->_aliases['civicrm_financial_item']}.id
290 AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item'
291 INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn_1']}
292 ON {$this->_aliases['civicrm_financial_trxn_1']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}.id
293 AND {$this->_aliases['civicrm_financial_trxn_1']}.id = entity_financial_trxn_item.financial_trxn_id
294 INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution
295 ON financial_trxn_contribution.financial_trxn_id = {$this->_aliases['civicrm_financial_trxn_1']}.id
296 AND financial_trxn_contribution.entity_table = 'civicrm_contribution'
297 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution
298 ON entity_financial_trxn_contribution.entity_id = {$this->_aliases['civicrm_financial_item']}.id
299 AND entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item'
300 INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
301 ON {$this->_aliases['civicrm_financial_trxn']}.id = entity_financial_trxn_contribution.financial_trxn_id
302 AND ({$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount
)) . ")
303 OR {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NULL)
304 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
305 ON {$this->_aliases['civicrm_contribution']}.id = financial_trxn_contribution.entity_id
306 INNER JOIN civicrm_line_item line_item
307 ON line_item.contribution_id = {$this->_aliases['civicrm_contribution']}.id
308 AND line_item.financial_type_id = entity_financial_account_deferred.entity_id
309 LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
311 WHEN line_item.entity_table = 'civicrm_participant'
312 THEN line_item.entity_id = {$this->_aliases['civicrm_participant']}.id
313 ELSE {$this->_aliases['civicrm_participant']}.id = 0
315 LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
316 ON {$this->_aliases['civicrm_participant']}.event_id = {$this->_aliases['civicrm_event']}.id";
318 if ($this->isTableSelected('civicrm_contact')) {
320 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
321 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id";
324 if ($this->isTableSelected('civicrm_membership')) {
326 LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
328 WHEN line_item.entity_table = 'civicrm_membership'
329 THEN line_item.entity_id = {$this->_aliases['civicrm_membership']}.id
330 ELSE {$this->_aliases['civicrm_membership']}.id = 0
334 if ($this->isTableSelected('civicrm_batch')) {
336 LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
337 ON {$this->_aliases['civicrm_batch']}.entity_id = {$this->_aliases['civicrm_financial_trxn']}.id
338 AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'";
343 * Post process function.
345 public function postProcess() {
346 $this->_noFields
= TRUE;
347 parent
::postProcess();
353 * @param int $rowCount
355 public function limit($rowCount = self
::ROW_COUNT_LIMIT
) {
356 $this->_limit
= NULL;
360 * Build where clause.
362 public function 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}'";
370 * Build group by clause.
372 public function groupBy() {
373 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_financial_account']}.id, {$this->_aliases['civicrm_financial_account_1']}.id, {$this->_aliases['civicrm_financial_item']}.id";
374 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect(
375 $this->_selectClauses
,
377 "{$this->_aliases['civicrm_financial_account_1']}.id",
378 "{$this->_aliases['civicrm_financial_item']}.id",
384 * Modify column headers.
386 public function modifyColumnHeaders() {
387 // Re-order the columns in a custom order defined below.
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',
401 // Only re-order selected columns.
402 $sortArray = array_flip(array_intersect_key(array_flip($sortArray), $this->_columnHeaders
));
405 $this->_columnHeaders
= array_merge(array_flip($sortArray), $this->_columnHeaders
);
407 // Add months to the columns.
408 if ($this->_params
['revenue_recognition_date_toggle_value']) {
409 $this->_columnHeaders
[date('M, Y', strtotime(date('Y-m-d')))] = [
410 'title' => date('M, Y', strtotime(date('Y-m-d'))),
411 'type' => CRM_Utils_Type
::T_DATE
,
415 for ($i = 0; $i < 12; $i++
) {
416 $this->_columnHeaders
[date('M, Y', strtotime(date('Y-m-d') . "+{$i} month"))] = [
417 'title' => date('M, Y', strtotime(date('Y-m-d') . "+{$i} month")),
418 'type' => CRM_Utils_Type
::T_DATE
,
430 public function buildRows($sql, &$rows) {
431 $dao = CRM_Core_DAO
::executeQuery($sql);
433 // use this method to modify $this->_columnHeaders
434 $this->modifyColumnHeaders();
436 // Get custom date format.
437 $dateFormat = Civi
::settings()->get('dateformatFinancialBatch');
439 if (!is_array($rows)) {
443 while ($dao->fetch()) {
445 foreach ($this->_columnHeaders
as $key => $value) {
446 $arraykey = $dao->civicrm_financial_account_id
. '_' . $dao->civicrm_financial_account_1_id
;
448 if (property_exists($dao, $key)) {
449 if (CRM_Utils_Array
::value('type', $value) & CRM_Utils_Type
::T_DATE
) {
450 $row[$key] = CRM_Utils_Date
::customFormat($dao->$key, $dateFormat);
452 elseif (CRM_Utils_Array
::value('type', $value) & CRM_Utils_Type
::T_MONEY
) {
453 $row[$key] = CRM_Utils_Money
::format($dao->$key);
456 $row[$key] = $dao->$key;
460 $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id
] = $row;
461 $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}";
462 $trxnDate = explode(',', $dao->civicrm_financial_trxn_1_trxn_date
);
463 $trxnAmount = explode(',', $dao->civicrm_financial_trxn_1_total_amount
);
464 foreach ($trxnDate as $key => $date) {
465 $keyDate = date('M, Y', strtotime($date));
466 $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id
][$keyDate] = CRM_Utils_Money
::format($trxnAmount[$key]);
473 * Alter display of rows.
475 * Iterate through the rows retrieved via SQL and make changes for display purposes,
476 * such as rendering contacts as links.
479 * Rows generated by SQL, with an array for each row.
481 public function alterDisplay(&$rows) {
483 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
485 foreach ($rows as &$entry) {
486 foreach ($entry['rows'] as $rowNum => &$row) {
488 // convert transaction status id to status name
489 if ($status = CRM_Utils_Array
::value('civicrm_financial_trxn_status_id', $row)) {
490 $row['civicrm_financial_trxn_status_id'] = CRM_Core_PseudoConstant
::getLabel('CRM_Core_BAO_FinancialTrxn', 'status_id', $status);
494 // convert batch id to batch title
495 if ($batchId = CRM_Utils_Array
::value('civicrm_batch_batch_id', $row)) {
496 $row['civicrm_batch_batch_id'] = $this->getLabels($batchId, 'CRM_Batch_BAO_EntityBatch', 'batch_id');
500 // add hotlink for contribution
501 if ($amount = CRM_Utils_Array
::value('civicrm_financial_trxn_total_amount', $row)) {
502 $contributionUrl = CRM_Utils_System
::url("civicrm/contact/view/contribution",
503 'reset=1&action=view&cid=' . $row['civicrm_contact_id'] . '&id=' . $row['civicrm_contribution_contribution_id'],
506 $row['civicrm_financial_trxn_total_amount'] = "<a href={$contributionUrl}>{$amount}</a>";
507 if ($contributionId = CRM_Utils_Array
::value('civicrm_contribution_id', $row)) {
508 $row['civicrm_contribution_id'] = "<a href={$contributionUrl}>{$contributionId}</a>";
513 // add hotlink for contact
514 if ($contactName = CRM_Utils_Array
::value('civicrm_contact_display_name', $row)) {
515 $contactUrl = CRM_Utils_System
::url("civicrm/contact/view",
516 'reset=1&cid=' . $row['civicrm_contact_id'],
519 $row['civicrm_contact_display_name'] = "<a href={$contactUrl}>{$contactName}</a>";
523 if ($contactId = CRM_Utils_Array
::value('civicrm_contribution_contact_id', $row)) {
524 $contactUrl = CRM_Utils_System
::url("civicrm/contact/view",
525 'reset=1&cid=' . $row['civicrm_contact_id'],
528 $row['civicrm_contribution_contact_id'] = "<a href={$contactUrl}>{$contactId}</a>";