3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2017 |
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-2017
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->_autoIncludeIndexedFieldsAsOrderBys
= 1;
46 $this->_deferredFinancialAccount
= CRM_Financial_BAO_FinancialAccount
::getAllDeferredFinancialAccount();
47 $this->_columns
= array(
48 'civicrm_financial_account' => array(
49 'dao' => 'CRM_Financial_DAO_FinancialAccount',
50 'alias' => 'financial_account_deferred',
53 'title' => ts('Deferred Financial Account'),
54 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
55 'options' => $this->_deferredFinancialAccount
,
56 'type' => CRM_Utils_Type
::T_INT
,
61 parent
::__construct();
64 public function preProcess() {
68 public function select() {
70 $this->_select
= ' SELECT
71 financial_account_deferred_civireport.name deferred_account,
72 financial_account_deferred_civireport.id deferred_account_id,
73 financial_account_deferred_civireport.accounting_code deferred_account_code,
74 financial_account_revenue.name revenue_account,
75 financial_account_revenue.id revenue_account_id,
76 financial_account_revenue.accounting_code revenue_account_code,
77 financial_item.status_id,
78 financial_item.id item_id,
79 financial_trxn_contribution_1.status_id,
80 financial_trxn_contribution_1.trxn_date transaction_date,
81 financial_trxn_contribution_1.total_amount,
82 contribution.id contribution_id,
83 contribution.contact_id,
86 GROUP_CONCAT(financial_trxn.total_amount) trxn_amount,
87 GROUP_CONCAT(financial_trxn.trxn_date) trxn_date,
88 financial_item.description,
89 IFNULL(membership.start_date, event.start_date) start_date,
90 IFNULL(membership.end_date, event.end_date) end_date
94 public function from() {
95 $deferredRelationship = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Deferred Revenue Account is' "));
96 $revenueRelationship = key(CRM_Core_PseudoConstant
::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Income Account is' "));
97 $this->_from
= " FROM civicrm_financial_item financial_item
98 INNER JOIN civicrm_entity_financial_account entity_financial_account_deferred
99 ON financial_item.financial_account_id = entity_financial_account_deferred.financial_account_id AND entity_financial_account_deferred.entity_table = 'civicrm_financial_type'
100 AND entity_financial_account_deferred.account_relationship = {$deferredRelationship}
101 INNER JOIN civicrm_financial_account financial_account_deferred_civireport
102 ON entity_financial_account_deferred.financial_account_id = financial_account_deferred_civireport.id
103 INNER JOIN civicrm_entity_financial_account entity_financial_account_revenue
104 ON entity_financial_account_deferred.entity_id = entity_financial_account_revenue.entity_id
105 AND entity_financial_account_deferred.entity_table= entity_financial_account_revenue.entity_table
106 INNER JOIN civicrm_financial_account financial_account_revenue
107 ON entity_financial_account_revenue.financial_account_id = financial_account_revenue.id
108 AND {$revenueRelationship} = entity_financial_account_revenue.account_relationship
109 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_item
110 ON entity_financial_trxn_item.entity_id = financial_item.id AND entity_financial_trxn_item.entity_table = 'civicrm_financial_item'
111 INNER JOIN civicrm_financial_trxn financial_trxn
112 ON financial_trxn.from_financial_account_id = financial_account_deferred_civireport.id AND financial_trxn.id = entity_financial_trxn_item.financial_trxn_id
113 INNER JOIN civicrm_entity_financial_trxn financial_trxn_contribution
114 ON financial_trxn_contribution.financial_trxn_id = financial_trxn.id AND financial_trxn_contribution.entity_table = 'civicrm_contribution'
115 INNER JOIN civicrm_entity_financial_trxn entity_financial_trxn_contribution ON entity_financial_trxn_contribution.entity_id = financial_item.id and entity_financial_trxn_contribution.entity_table = 'civicrm_financial_item'
116 INNER JOIN civicrm_financial_trxn financial_trxn_contribution_1 ON financial_trxn_contribution_1.id = entity_financial_trxn_contribution.financial_trxn_id AND (financial_trxn_contribution_1.from_financial_account_id NOT IN (" . implode(',', array_keys($this->_deferredFinancialAccount
)) . ") OR financial_trxn_contribution_1.from_financial_account_id IS NULL)
117 INNER JOIN civicrm_contribution contribution
118 ON contribution.id = financial_trxn_contribution.entity_id
119 INNER JOIN civicrm_contact contact
120 ON contact.id = contribution.contact_id
121 INNER JOIN civicrm_line_item line_item
122 ON line_item.contribution_id = contribution.id
123 LEFT JOIN civicrm_membership membership
125 WHEN line_item.entity_table = 'civicrm_membership'
126 THEN line_item.entity_id = membership.id
127 ELSE membership.id = 0
129 LEFT JOIN civicrm_participant participant
131 WHEN line_item.entity_table = 'civicrm_participant'
132 THEN line_item.entity_id = participant.id
133 ELSE participant.id = 0
135 LEFT JOIN civicrm_event event ON participant.event_id = event.id
139 public function orderBy() {
143 public function where() {
145 foreach ($this->_columns
as $tableName => $table) {
146 if (array_key_exists('filters', $table)) {
147 foreach ($table['filters'] as $fieldName => $field) {
149 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
150 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
151 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
152 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
154 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
157 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
159 $clause = $this->whereClause($field,
161 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
162 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
163 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
167 if (!empty($clause)) {
168 $clauses[] = $clause;
173 if (!empty($clauses)) {
174 $this->_where
= 'WHERE ' . implode(' AND ', $clauses);
178 public function postProcess() {
179 $this->_noFields
= TRUE;
180 parent
::postProcess();
183 public function groupBy() {
184 $this->_groupBy
= "GROUP BY financial_account_deferred_civireport.id, financial_account_revenue.id, financial_item.id";
193 public function buildRows($sql, &$rows) {
194 $dao = CRM_Core_DAO
::executeQuery($sql);
195 if (!is_array($rows)) {
198 $statuses = CRM_Contribute_PseudoConstant
::contributionStatus(NULL, 'name');
199 $dateColumn = array();
202 'Date of Transaction' => 1,
204 'Contribution ID' => 1,
212 $dateFormat = Civi
::settings()->get('dateformatFinancialBatch');
213 while ($dao->fetch()) {
214 $arraykey = $dao->deferred_account_id
. '_' . $dao->revenue_account_id
;
215 if (empty($rows[$arraykey])) {
216 $rows[$arraykey]['label'] = "Deferred Revenue Account: {$dao->deferred_account} ({$dao->deferred_account_code}), Revenue Account: {$dao->revenue_account} {$dao->revenue_account_code}";
218 $rows[$arraykey]['rows'][$dao->item_id
] = array(
219 'Transaction' => $statuses[$dao->status_id
],
220 'Date of Transaction' => CRM_Utils_Date
::customFormat($dao->transaction_date
, $dateFormat),
221 'Amount' => CRM_Utils_Money
::format($dao->total_amount
),
222 'Contribution ID' => $dao->contribution_id
,
223 'Item' => $dao->description
,
224 'Contact ID' => $dao->contact_id
,
225 'Contact Name' => $dao->display_name
,
226 'Source' => $dao->source
,
227 'Start Date' => CRM_Utils_Date
::customFormat($dao->start_date
, $dateFormat),
228 'End Date' => CRM_Utils_Date
::customFormat($dao->end_date
, $dateFormat),
230 $trxnDate = explode(',', $dao->trxn_date
);
231 $trxnAmount = explode(',', $dao->trxn_amount
);
232 foreach ($trxnDate as $key => $date) {
233 $keyDate = date('M, Y', strtotime($date));
234 $rows[$arraykey]['rows'][$dao->item_id
][$keyDate] = CRM_Utils_Money
::format($trxnAmount[$key]);
235 $dateColumn[date('Ymd', strtotime($date))] = 1;
239 foreach ($dateColumn as $key => $ignore) {
240 $columns[date('M, Y', strtotime($key))] = 1;
242 $this->_columnHeaders
= $columns;
248 public function statistics(&$rows) {}
251 * Alter display of rows.
253 * Iterate through the rows retrieved via SQL and make changes for display purposes,
254 * such as rendering contacts as links.
257 * Rows generated by SQL, with an array for each row.
259 public function alterDisplay(&$rows) {}