3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
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-2015
37 * @link http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation
39 class CRM_Financial_BAO_ExportFormat_CSV
extends CRM_Financial_BAO_ExportFormat
{
41 // For this phase, we always output these records too so that there isn't data referenced in the journal entries that isn't defined anywhere.
42 // Possibly in the future this could be selected by the user.
43 public static $complementaryTables = array(
51 public function __construct() {
52 parent
::__construct();
56 * @param array $exportParams
58 public function export($exportParams) {
59 $export = parent
::export($exportParams);
61 // Save the file in the public directory
62 $fileName = self
::putFile($export);
64 foreach (self
::$complementaryTables as $rct) {
65 $func = "export{$rct}";
69 // now do general journal entries
72 $this->output($fileName);
80 public function generateExportQuery($batchId) {
82 ft.id as financial_trxn_id,
84 fa_to.accounting_code AS to_account_code,
85 fa_to.name AS to_account_name,
86 fa_to.account_type_code AS to_account_type_code,
87 ft.total_amount AS debit_total_amount,
88 ft.trxn_id AS trxn_id,
89 cov.label AS payment_instrument,
92 c.id AS contribution_id,
93 c.contact_id AS contact_id,
94 eb.batch_id AS batch_id,
95 ft.currency AS currency,
96 cov_status.label AS status,
98 WHEN efti.entity_id IS NOT NULL
102 fa_from.account_type_code AS credit_account_type_code,
103 fa_from.accounting_code AS credit_account,
104 fa_from.name AS credit_account_name,
105 fac.account_type_code AS from_credit_account_type_code,
106 fac.accounting_code AS from_credit_account,
107 fac.name AS from_credit_account_name,
108 fi.description AS item_description
109 FROM civicrm_entity_batch eb
110 LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
111 LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
112 LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
113 LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
114 LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
115 LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id = ft.id AND eftc.entity_table = 'civicrm_contribution')
116 LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id
117 LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'
118 LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)
119 LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id = ft.id AND efti.entity_table = 'civicrm_financial_item')
120 LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
121 LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id
122 LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
123 WHERE eb.batch_id = ( %1 )";
125 CRM_Utils_Hook
::batchQuery($sql);
127 $params = array(1 => array($batchId, 'String'));
128 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
138 public function putFile($export) {
139 $config = CRM_Core_Config
::singleton();
140 $fileName = $config->uploadDir
. 'Financial_Transactions_' . $this->_batchIds
. '_' . date('YmdHis') . '.' . $this->getFileExtension();
141 $this->_downloadFile
[] = $config->customFileUploadDir
. CRM_Utils_File
::cleanFileName(basename($fileName));
142 $out = fopen($fileName, 'w');
143 fputcsv($out, $export['headers']);
144 unset($export['headers']);
145 if (!empty($export)) {
146 foreach ($export as $fields) {
147 fputcsv($out, $fields);
155 * Format table headers.
157 * @param array $values
160 public function formatHeaders($values) {
161 $arrayKeys = array_keys($values);
163 if (!empty($arrayKeys)) {
164 foreach ($values[$arrayKeys[0]] as $title => $value) {
172 * Generate CSV array for export.
174 * @param array $export
176 public function makeCSV($export) {
177 // getting data from admin page
178 $prefixValue = CRM_Core_BAO_Setting
::getItem(CRM_Core_BAO_Setting
::CONTRIBUTE_PREFERENCES_NAME
, 'contribution_invoice_settings');
180 foreach ($export as $batchId => $dao) {
181 $financialItems = array();
182 $this->_batchIds
= $batchId;
184 $batchItems = array();
185 $queryResults = array();
187 while ($dao->fetch()) {
188 $creditAccountName = $creditAccountType = $creditAccount = NULL;
189 if ($dao->credit_account
) {
190 $creditAccountName = $dao->credit_account_name
;
191 $creditAccountType = $dao->credit_account_type_code
;
192 $creditAccount = $dao->credit_account
;
195 $creditAccountName = $dao->from_credit_account_name
;
196 $creditAccountType = $dao->from_credit_account_type_code
;
197 $creditAccount = $dao->from_credit_account
;
200 $invoiceNo = CRM_Utils_Array
::value('invoice_prefix', $prefixValue) . "" . $dao->contribution_id
;
202 $financialItems[] = array(
203 'Batch ID' => $dao->batch_id
,
204 'Invoice No' => $invoiceNo,
205 'Contact ID' => $dao->contact_id
,
206 'Financial Trxn ID/Internal ID' => $dao->financial_trxn_id
,
207 'Transaction Date' => $dao->trxn_date
,
208 'Debit Account' => $dao->to_account_code
,
209 'Debit Account Name' => $dao->to_account_name
,
210 'Debit Account Type' => $dao->to_account_type_code
,
211 'Debit Account Amount (Unsplit)' => $dao->debit_total_amount
,
212 'Transaction ID (Unsplit)' => $dao->trxn_id
,
213 'Debit amount (Split)' => $dao->amount
,
214 'Payment Instrument' => $dao->payment_instrument
,
215 'Check Number' => $dao->check_number
,
216 'Source' => $dao->source
,
217 'Currency' => $dao->currency
,
218 'Transaction Status' => $dao->status
,
219 'Amount' => $dao->amount
,
220 'Credit Account' => $creditAccount,
221 'Credit Account Name' => $creditAccountName,
222 'Credit Account Type' => $creditAccountType,
223 'Item Description' => $dao->item_description
,
226 end($financialItems);
227 $batchItems[] = &$financialItems[key($financialItems)];
228 $queryResults[] = get_object_vars($dao);
231 CRM_Utils_Hook
::batchItems($queryResults, $batchItems);
233 $financialItems['headers'] = self
::formatHeaders($financialItems);
234 self
::export($financialItems);
236 parent
::initiateDownload();
242 public function getFileExtension() {
246 public function exportACCNT() {
249 public function exportCUST() {
252 public function exportTRANS() {