3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
35 class CRM_Report_Form_Contribute_DeferredRevenue
extends CRM_Report_Form
{
38 * Holds Deferred Financial Account
40 protected $_deferredFinancialAccount = array();
44 public function __construct() {
45 $this->_exposeContactID
= FALSE;
46 $this->_deferredFinancialAccount
= CRM_Financial_BAO_FinancialAccount
::getAllDeferredFinancialAccount();
47 $this->_columns
= array(
48 'civicrm_financial_trxn' => array(
49 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
52 'title' => ts('Transaction'),
55 'title' => ts('Transaction Date'),
56 'type' => CRM_Utils_Type
::T_DATE
,
59 'total_amount' => array(
60 'title' => ts('Transaction Amount'),
61 'type' => CRM_Utils_Type
::T_MONEY
,
63 'dbAlias' => 'SUM(financial_trxn_1_civireport.total_amount )',
68 'title' => ts('Transaction Date'),
69 'operatorType' => CRM_Report_Form
::OP_DATE
,
70 'type' => CRM_Utils_Type
::T_DATE
,
74 'civicrm_contribution' => array(
75 'dao' => 'CRM_Contribute_DAO_Contribution',
78 'title' => ts('Contribution ID'),
80 'contribution_id' => array(
81 'title' => ts('Contribution ID'),
84 'dbAlias' => 'contribution_civireport.id',
86 'contact_id' => array(
87 'title' => ts('Contact ID'),
90 'title' => ts('Source'),
92 'receive_date' => array(
93 'title' => ts('Receive Date'),
94 'type' => CRM_Utils_Type
::T_DATE
,
96 'cancel_date' => array(
97 'title' => ts('Cancel Date'),
98 'type' => CRM_Utils_Type
::T_DATE
,
100 'revenue_recognition_date' => array(
101 'title' => ts('Revenue Recognition Date'),
102 'type' => CRM_Utils_Type
::T_DATE
,
106 'receive_date' => array(
107 'title' => ts('Receive Date'),
108 'operatorType' => CRM_Report_Form
::OP_DATE
,
109 'type' => CRM_Utils_Type
::T_DATE
,
111 'cancel_date' => array(
112 'title' => ts('Cancel Date'),
113 'operatorType' => CRM_Report_Form
::OP_DATE
,
114 'type' => CRM_Utils_Type
::T_DATE
,
116 'revenue_recognition_date' => array(
117 'title' => ts('Revenue Recognition Date'),
118 'operatorType' => CRM_Report_Form
::OP_DATE
,
119 'type' => CRM_Utils_Type
::T_DATE
,
121 'revenue_recognition_date_toggle' => array(
122 'title' => ts("Current month's revenue?"),
123 'type' => CRM_Utils_Type
::T_BOOLEAN
,
125 'pseudofield' => TRUE,
129 'civicrm_financial_account' => array(
130 'dao' => 'CRM_Financial_DAO_FinancialAccount',
133 'title' => ts('Deferred Account'),
135 'no_display' => TRUE,
138 'title' => ts('Deferred Account ID'),
140 'no_display' => TRUE,
142 'accounting_code' => array(
143 'title' => ts('Deferred Accounting Code'),
145 'no_display' => TRUE,
150 'title' => ts('Deferred Financial Account'),
151 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
152 'options' => $this->_deferredFinancialAccount
,
153 'type' => CRM_Utils_Type
::T_INT
,
157 'civicrm_financial_account_1' => array(
158 'dao' => 'CRM_Financial_DAO_FinancialAccount',
161 'title' => ts('Revenue Account'),
163 'no_display' => TRUE,
166 'title' => ts('Revenue Account ID'),
168 'no_display' => TRUE,
170 'accounting_code' => array(
171 'title' => ts('Revenue Accounting code'),
172 'no_display' => TRUE,
177 'civicrm_financial_item' => array(
178 'dao' => 'CRM_Financial_DAO_FinancialItem',
180 'status_id' => array(
181 'title' => ts('Status'),
183 'no_display' => TRUE,
186 'title' => ts('Financial Item ID'),
188 'no_display' => TRUE,
190 'description' => array(
191 'title' => ts('Item'),
195 'civicrm_financial_trxn_1' => array(
196 'dao' => 'CRM_Financial_DAO_FinancialTrxn',
198 'total_amount' => array(
199 'title' => ts('Deferred Transaction Amount'),
200 'type' => CRM_Utils_Type
::T_MONEY
,
202 'no_display' => TRUE,
203 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.total_amount)',
205 'trxn_date' => array(
206 'title' => ts('Deferred Transaction Date'),
208 'no_display' => TRUE,
209 'dbAlias' => 'GROUP_CONCAT(financial_trxn_1_civireport.trxn_date)',
210 'type' => CRM_Utils_Type
::T_DATE
,
214 'civicrm_contact' => array(
215 'dao' => 'CRM_Contact_DAO_Contact',
217 'display_name' => array(
218 'title' => ts('Contact Name'),
221 'title' => ts('Contact ID'),
223 'no_display' => TRUE,
227 'civicrm_membership' => array(
228 'dao' => 'CRM_Member_DAO_Membership',
230 'start_date' => array(
231 'title' => ts('Start Date'),
232 'dbAlias' => 'IFNULL(membership_civireport.start_date, event_civireport.start_date)',
233 'type' => CRM_Utils_Type
::T_DATE
,
236 'title' => ts('End Date'),
237 'dbdbAlias' => 'IFNULL(membership_civireport.end_date, event_civireport.end_date)',
238 'type' => CRM_Utils_Type
::T_DATE
,
242 'civicrm_event' => array(
243 'dao' => 'CRM_Event_DAO_Event',
245 'civicrm_participant' => array(
246 'dao' => 'CRM_Event_DAO_Participant',
248 'civicrm_batch' => array(
249 'dao' => 'CRM_Batch_DAO_EntityBatch',
250 'grouping' => 'contri-fields',
253 'title' => ts('Batch Title'),
254 'dbAlias' => "GROUP_CONCAT(DISTINCT batch_civireport.batch_id
255 ORDER BY batch_civireport.batch_id SEPARATOR ',')",
261 'title' => ts('Batch Title'),
262 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
263 'options' => CRM_Batch_BAO_Batch
::getBatches(),
264 'type' => CRM_Utils_Type
::T_INT
,
269 parent
::__construct();
273 * Pre process function.
275 * Called prior to build form.
277 public function preProcess() {
278 parent
::preProcess();
284 public function from() {
285 $deferredRelationship = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
286 $revenueRelationship = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
288 FROM civicrm_financial_item {$this->_aliases['civicrm_financial_item']}
289 INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred
290 ON {$this->_aliases['civicrm_financial_item']}.financial_account_id = entity_financial_account_deferred.financial_account_id
291 AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type'
292 AND entity_financial_account_deferred.account_relationship = {$deferredRelationship}
293 INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account']}
294 ON entity_financial_account_deferred.financial_account_id = {$this->_aliases['civicrm_financial_account']}.id
295 INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue
296 ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id
297 AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table
298 INNER JOIN civicrm_financial_account {$this->_aliases['civicrm_financial_account_1']}
299 ON entity_financial_account_revenue.financial_account_id = {$this->_aliases['civicrm_financial_account_1']}.id
300 AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship
301 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item
302 ON entity_financial_trxn_item.entity_id = {$this->_aliases['civicrm_financial_item']}.id
303 AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item'
304 INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn_1']}
305 ON {$this->_aliases['civicrm_financial_trxn_1']}.to_financial_account_id = {$this->_aliases['civicrm_financial_account']}.id
306 AND {$this->_aliases['civicrm_financial_trxn_1']}.id = entity_financial_trxn_item.financial_trxn_id
307 INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution
308 ON financial_trxn_contribution.financial_trxn_id = {$this->_aliases['civicrm_financial_trxn_1']}.id
309 AND financial_trxn_contribution.entity_table = 'civicrm_contribution'
310 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution
311 ON entity_financial_trxn_contribution.entity_id = {$this->_aliases['civicrm_financial_item']}.id
312 AND entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item'
313 INNER JOIN civicrm_financial_trxn {$this->_aliases['civicrm_financial_trxn']}
314 ON {$this->_aliases['civicrm_financial_trxn']}.id = entity_financial_trxn_contribution.financial_trxn_id
315 AND ({$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount
)) . ")
316 OR {$this->_aliases['civicrm_financial_trxn']}.from_financial_account_id IS NULL)
317 INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
318 ON {$this->_aliases['civicrm_contribution']}.id = financial_trxn_contribution.entity_id
319 INNER JOIN civicrm_line_item line_item
320 ON line_item.contribution_id = {$this->_aliases['civicrm_contribution']}.id
321 AND line_item.financial_type_id = entity_financial_account_deferred.entity_id
322 LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
324 WHEN line_item.entity_table = 'civicrm_participant'
325 THEN line_item.entity_id = {$this->_aliases['civicrm_participant']}.id
326 ELSE {$this->_aliases['civicrm_participant']}.id = 0
328 LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
329 ON {$this->_aliases['civicrm_participant']}.event_id = {$this->_aliases['civicrm_event']}.id";
331 if ($this->isTableSelected('civicrm_contact')) {
333 INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
334 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id";
337 if ($this->isTableSelected('civicrm_membership')) {
339 LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
341 WHEN line_item.entity_table = 'civicrm_membership'
342 THEN line_item.entity_id = {$this->_aliases['civicrm_membership']}.id
343 ELSE {$this->_aliases['civicrm_membership']}.id = 0
347 if ($this->isTableSelected('civicrm_batch')) {
349 LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
350 ON {$this->_aliases['civicrm_batch']}.entity_id = {$this->_aliases['civicrm_financial_trxn']}.id
351 AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'";
356 * Post process function.
358 public function postProcess() {
359 $this->_noFields
= TRUE;
360 parent
::postProcess();
366 * @param int $rowCount
368 public function limit($rowCount = self
::ROW_COUNT_LIMIT
) {
369 $this->_limit
= NULL;
373 * Build where clause.
375 public function where() {
377 $startDate = date('Y-m-01');
378 $endDate = date('Y-m-t', strtotime(date('ymd') . '+11 month'));
379 $this->_where
.= " AND {$this->_aliases['civicrm_financial_trxn_1']}.trxn_date BETWEEN '{$startDate}' AND '{$endDate}'";
383 * Build group by clause.
385 public function groupBy() {
386 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_financial_account']}.id, {$this->_aliases['civicrm_financial_account_1']}.id, {$this->_aliases['civicrm_financial_item']}.id";
387 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect(
388 $this->_selectClauses
,
390 "{$this->_aliases['civicrm_financial_account_1']}.id",
391 "{$this->_aliases['civicrm_financial_item']}.id",
397 * Modify column headers.
399 public function modifyColumnHeaders() {
400 // Re-order the columns in a custom order defined below.
402 'civicrm_batch_batch_id',
403 'civicrm_financial_trxn_status_id',
404 'civicrm_financial_trxn_trxn_date',
405 'civicrm_contribution_receive_date',
406 'civicrm_contribution_cancel_date',
407 'civicrm_contribution_revenue_recognition_date',
408 'civicrm_financial_trxn_total_amount',
409 'civicrm_financial_item_description',
410 'civicrm_contribution_contact_id',
411 'civicrm_contact_display_name',
412 'civicrm_contribution_source',
414 // Only re-order selected columns.
415 $sortArray = array_flip(array_intersect_key(array_flip($sortArray), $this->_columnHeaders
));
418 $this->_columnHeaders
= array_merge(array_flip($sortArray), $this->_columnHeaders
);
420 // Add months to the columns.
421 if ($this->_params
['revenue_recognition_date_toggle_value']) {
422 $this->_columnHeaders
[date('M, Y', strtotime(date('Y-m-d')))] = array(
423 'title' => date('M, Y', strtotime(date('Y-m-d'))),
424 'type' => CRM_Utils_Type
::T_DATE
,
428 for ($i = 0; $i < 12; $i++
) {
429 $this->_columnHeaders
[date('M, Y', strtotime(date('Y-m-d') . "+{$i} month"))] = array(
430 'title' => date('M, Y', strtotime(date('Y-m-d') . "+{$i} month")),
431 'type' => CRM_Utils_Type
::T_DATE
,
443 public function buildRows($sql, &$rows) {
444 $dao = CRM_Core_DAO
::executeQuery($sql);
446 // use this method to modify $this->_columnHeaders
447 $this->modifyColumnHeaders();
449 // Get custom date format.
450 $dateFormat = Civi
::settings()->get('dateformatFinancialBatch');
452 if (!is_array($rows)) {
456 while ($dao->fetch()) {
458 foreach ($this->_columnHeaders
as $key => $value) {
459 $arraykey = $dao->civicrm_financial_account_id
. '_' . $dao->civicrm_financial_account_1_id
;
461 if (property_exists($dao, $key)) {
462 if (CRM_Utils_Array
::value('type', $value) & CRM_Utils_Type
::T_DATE
) {
463 $row[$key] = CRM_Utils_Date
::customFormat($dao->$key, $dateFormat);
465 elseif (CRM_Utils_Array
::value('type', $value) & CRM_Utils_Type
::T_MONEY
) {
466 $row[$key] = CRM_Utils_Money
::format($dao->$key);
469 $row[$key] = $dao->$key;
473 $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id
] = $row;
474 $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}";
475 $trxnDate = explode(',', $dao->civicrm_financial_trxn_1_trxn_date
);
476 $trxnAmount = explode(',', $dao->civicrm_financial_trxn_1_total_amount
);
477 foreach ($trxnDate as $key => $date) {
478 $keyDate = date('M, Y', strtotime($date));
479 $rows[$arraykey]['rows'][$dao->civicrm_financial_item_id
][$keyDate] = CRM_Utils_Money
::format($trxnAmount[$key]);
486 * Alter display of rows.
488 * Iterate through the rows retrieved via SQL and make changes for display purposes,
489 * such as rendering contacts as links.
492 * Rows generated by SQL, with an array for each row.
494 public function alterDisplay(&$rows) {
496 $contributionStatus = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
498 foreach ($rows as &$entry) {
499 foreach ($entry['rows'] as $rowNum => &$row) {
501 // convert transaction status id to status name
502 if ($status = CRM_Utils_Array
::value('civicrm_financial_trxn_status_id', $row)) {
503 $row['civicrm_financial_trxn_status_id'] = CRM_Core_PseudoConstant
::getLabel('CRM_Core_BAO_FinancialTrxn', 'status_id', $status);
507 // convert batch id to batch title
508 if ($batchId = CRM_Utils_Array
::value('civicrm_batch_batch_id', $row)) {
509 $row['civicrm_batch_batch_id'] = $this->getLabels($batchId, 'CRM_Batch_BAO_EntityBatch', 'batch_id');
513 // add hotlink for contribution
514 if ($amount = CRM_Utils_Array
::value('civicrm_financial_trxn_total_amount', $row)) {
515 $contributionUrl = CRM_Utils_System
::url("civicrm/contact/view/contribution",
516 'reset=1&action=view&cid=' . $row['civicrm_contact_id'] . '&id=' . $row['civicrm_contribution_contribution_id'],
519 $row['civicrm_financial_trxn_total_amount'] = "<a href={$contributionUrl}>{$amount}</a>";
520 if ($contributionId = CRM_Utils_Array
::value('civicrm_contribution_id', $row)) {
521 $row['civicrm_contribution_id'] = "<a href={$contributionUrl}>{$contributionId}</a>";
526 // add hotlink for contact
527 if ($contactName = CRM_Utils_Array
::value('civicrm_contact_display_name', $row)) {
528 $contactUrl = CRM_Utils_System
::url("civicrm/contact/view",
529 'reset=1&cid=' . $row['civicrm_contact_id'],
532 $row['civicrm_contact_display_name'] = "<a href={$contactUrl}>{$contactName}</a>";
536 if ($contactId = CRM_Utils_Array
::value('civicrm_contribution_contact_id', $row)) {
537 $contactUrl = CRM_Utils_System
::url("civicrm/contact/view",
538 'reset=1&cid=' . $row['civicrm_contact_id'],
541 $row['civicrm_contribution_contact_id'] = "<a href={$contactUrl}>{$contactId}</a>";