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_IIF
extends CRM_Financial_BAO_ExportFormat
{
41 // Tab character. Some people's editors replace tabs with spaces so I'm scared to use actual tabs.
42 // Can't set it here using chr() because static. Same thing if a const. So it's set in constructor.
45 // 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.
46 // Possibly in the future this could be selected by the user.
47 public static $complementaryTables = array(
55 public function __construct() {
56 parent
::__construct();
57 self
::$SEPARATOR = chr(9);
61 * @param array $exportParams
63 public function export($exportParams) {
64 parent
::export($exportParams);
66 foreach (self
::$complementaryTables as $rct) {
67 $func = "export{$rct}";
71 // now do general journal entries
82 public function putFile($out) {
83 $config = CRM_Core_Config
::singleton();
84 $fileName = $config->uploadDir
. 'Financial_Transactions_' . $this->_batchIds
. '_' . date('YmdHis') . '.' . $this->getFileExtension();
85 $this->_downloadFile
[] = $config->customFileUploadDir
. CRM_Utils_File
::cleanFileName(basename($fileName));
86 $buffer = fopen($fileName, 'w');
87 fwrite($buffer, $out);
97 public function generateExportQuery($batchId) {
100 ft.id as financial_trxn_id,
102 ft.total_amount AS debit_total_amount,
103 ft.currency AS currency,
104 ft.trxn_id AS trxn_id,
105 cov.label AS payment_instrument,
107 fa_from.id AS from_account_id,
108 fa_from.name AS from_account_name,
109 fa_from.accounting_code AS from_account_code,
110 fa_from.financial_account_type_id AS from_account_type_id,
111 fa_from.description AS from_account_description,
112 fa_from.account_type_code AS from_account_type_code,
113 fa_to.id AS to_account_id,
114 fa_to.name AS to_account_name,
115 fa_to.accounting_code AS to_account_code,
116 fa_to.financial_account_type_id AS to_account_type_id,
117 fa_to.account_type_code AS to_account_type_code,
118 fa_to.description AS to_account_description,
119 fi.description AS item_description,
120 contact_from.id AS contact_from_id,
121 contact_from.display_name AS contact_from_name,
122 contact_from.first_name AS contact_from_first_name,
123 contact_from.last_name AS contact_from_last_name,
124 contact_to.id AS contact_to_id,
125 contact_to.display_name AS contact_to_name,
126 contact_to.first_name AS contact_to_first_name,
127 contact_to.last_name AS contact_to_last_name
128 FROM civicrm_entity_batch eb
129 LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
130 LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
131 LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
132 LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
133 LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
134 LEFT JOIN civicrm_contact contact_from ON contact_from.id = fa_from.contact_id
135 LEFT JOIN civicrm_contact contact_to ON contact_to.id = fa_to.contact_id
136 LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id = ft.id AND efti.entity_table = 'civicrm_financial_item')
137 LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
138 WHERE eb.batch_id = ( %1 )";
140 $params = array(1 => array($batchId, 'String'));
141 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
149 public function makeIIF($export) {
150 // Keep running list of accounts and contacts used in this batch, since we need to
151 // include those in the output. Only want to include ones used in the batch, not everything in the db,
152 // since would increase the chance of messing up user's existing Quickbooks entries.
153 foreach ($export as $batchId => $dao) {
154 $accounts = $contacts = $journalEntries = $exportParams = array();
155 $this->_batchIds
= $batchId;
156 while ($dao->fetch()) {
157 // add to running list of accounts
158 if (!empty($dao->from_account_id
) && !isset($accounts[$dao->from_account_id
])) {
159 $accounts[$dao->from_account_id
] = array(
160 'name' => $this->format($dao->from_account_name
),
161 'account_code' => $this->format($dao->from_account_code
),
162 'description' => $this->format($dao->from_account_description
),
163 'type' => $this->format($dao->from_account_type_code
),
166 if (!empty($dao->to_account_id
) && !isset($accounts[$dao->to_account_id
])) {
167 $accounts[$dao->to_account_id
] = array(
168 'name' => $this->format($dao->to_account_name
),
169 'account_code' => $this->format($dao->to_account_code
),
170 'description' => $this->format($dao->to_account_description
),
171 'type' => $this->format($dao->to_account_type_code
),
175 // add to running list of contacts
176 if (!empty($dao->contact_from_id
) && !isset($contacts[$dao->contact_from_id
])) {
177 $contacts[$dao->contact_from_id
] = array(
178 'name' => $this->format($dao->contact_from_name
),
179 'first_name' => $this->format($dao->contact_from_first_name
),
180 'last_name' => $this->format($dao->contact_from_last_name
),
184 if (!empty($dao->contact_to_id
) && !isset($contacts[$dao->contact_to_id
])) {
185 $contacts[$dao->contact_to_id
] = array(
186 'name' => $this->format($dao->contact_to_name
),
187 'first_name' => $this->format($dao->contact_to_first_name
),
188 'last_name' => $this->format($dao->contact_to_last_name
),
192 // set up the journal entries for this financial trxn
193 $journalEntries[$dao->financial_trxn_id
] = array(
194 'to_account' => array(
195 'trxn_date' => $this->format($dao->trxn_date
, 'date'),
196 'trxn_id' => $this->format($dao->trxn_id
),
197 'account_name' => $this->format($dao->to_account_name
),
198 'amount' => $this->format($dao->debit_total_amount
, 'money'),
199 'contact_name' => $this->format($dao->contact_to_name
),
200 'payment_instrument' => $this->format($dao->payment_instrument
),
201 'check_number' => $this->format($dao->check_number
),
207 * splits has two possibilities depending on FROM account
209 if (empty($dao->from_account_id
)) {
210 // In this case, split records need to use the individual financial_item account for each item in the trxn
213 fa.name AS account_name,
214 fa.accounting_code AS account_code,
215 fa.description AS account_description,
216 fi.description AS description,
217 fi.id AS financial_item_id,
218 fi.currency AS currency,
219 cov.label AS payment_instrument,
220 ft.check_number AS check_number,
221 fi.transaction_date AS transaction_date,
223 fa.account_type_code AS account_type_code,
224 contact.id AS contact_id,
225 contact.display_name AS contact_name,
226 contact.first_name AS contact_first_name,
227 contact.last_name AS contact_last_name
228 FROM civicrm_entity_financial_trxn eft
229 LEFT JOIN civicrm_financial_item fi ON eft.entity_id = fi.id
230 LEFT JOIN civicrm_financial_trxn ft ON ft.id = eft.financial_trxn_id
231 LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
232 LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
233 LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
234 LEFT JOIN civicrm_contact contact ON contact.id = fi.contact_id
235 WHERE eft.entity_table = 'civicrm_financial_item'
236 AND eft.financial_trxn_id = %1";
238 $itemParams = array(1 => array($dao->financial_trxn_id
, 'Integer'));
240 $itemDAO = CRM_Core_DAO
::executeQuery($item_sql, $itemParams);
241 while ($itemDAO->fetch()) {
242 // add to running list of accounts
243 if (!empty($itemDAO->account_id
) && !isset($accounts[$itemDAO->account_id
])) {
244 $accounts[$itemDAO->account_id
] = array(
245 'name' => $this->format($itemDAO->account_name
),
246 'account_code' => $this->format($itemDAO->account_code
),
247 'description' => $this->format($itemDAO->account_description
),
248 'type' => $this->format($itemDAO->account_type_code
),
252 if (!empty($itemDAO->contact_id
) && !isset($contacts[$itemDAO->contact_id
])) {
253 $contacts[$itemDAO->contact_id
] = array(
254 'name' => $this->format($itemDAO->contact_name
),
255 'first_name' => $this->format($itemDAO->contact_first_name
),
256 'last_name' => $this->format($itemDAO->contact_last_name
),
260 // add split line for this item
261 $journalEntries[$dao->financial_trxn_id
]['splits'][$itemDAO->financial_item_id
] = array(
262 'trxn_date' => $this->format($itemDAO->transaction_date
, 'date'),
263 'spl_id' => $this->format($itemDAO->financial_item_id
),
264 'account_name' => $this->format($itemDAO->account_name
),
265 'amount' => '-' . $this->format($itemDAO->amount
, 'money'),
266 'contact_name' => $this->format($itemDAO->contact_name
),
267 'payment_instrument' => $this->format($itemDAO->payment_instrument
),
268 'description' => $this->format($itemDAO->description
),
269 'check_number' => $this->format($itemDAO->check_number
),
270 'currency' => $this->format($itemDAO->currency
),
276 // In this case, split record just uses the FROM account from the trxn, and there's only one record here
277 $journalEntries[$dao->financial_trxn_id
]['splits'][] = array(
278 'trxn_date' => $this->format($dao->trxn_date
, 'date'),
279 'spl_id' => $this->format($dao->financial_trxn_id
),
280 'account_name' => $this->format($dao->from_account_name
),
281 'amount' => '-' . $this->format($dao->debit_total_amount
, 'money'),
282 'contact_name' => $this->format($dao->contact_from_name
),
283 'description' => $this->format($dao->item_description
),
284 'payment_instrument' => $this->format($dao->payment_instrument
),
285 'check_number' => $this->format($dao->check_number
),
286 'currency' => $this->format($dao->currency
),
290 $exportParams = array(
291 'accounts' => $accounts,
292 'contacts' => $contacts,
293 'journalEntries' => $journalEntries,
295 self
::export($exportParams);
297 parent
::initiateDownload();
300 public function exportACCNT() {
301 self
::assign('accounts', $this->_exportParams
['accounts']);
304 public function exportCUST() {
305 self
::assign('contacts', $this->_exportParams
['contacts']);
308 public function exportTRANS() {
309 self
::assign('journalEntries', $this->_exportParams
['journalEntries']);
315 public function getMimeType() {
316 return 'application/octet-stream';
322 public function getFileExtension() {
329 public function getHookedTemplateFileName() {
330 return 'CRM/Financial/ExportFormat/IIF.tpl';
336 * @param string $type
337 * type can be string, date, or notepad
339 * @return bool|mixed|string
341 public static function format($s, $type = 'string') {
342 // If I remember right there's a couple things:
343 // NOTEPAD field needs to be surrounded by quotes and then get rid of double quotes inside, also newlines should be literal \n, and ditch any ascii 0x0d's.
344 // Date handling has changed over the years. It used to only understand mm/dd/yy but I think now it might depend on your OS settings. Sometimes mm/dd/yyyy works but sometimes it wants yyyy/mm/dd, at least where I had used it.
345 // In all cases need to do something with tabs in the input.
347 $s1 = str_replace(self
::$SEPARATOR, '\t', $s);
350 $sout = date('Y/m/d', strtotime($s1));
354 $sout = CRM_Utils_Money
::format($s, NULL, NULL, TRUE);
359 $s2 = str_replace("\n", '\n', $s1);
360 $s3 = str_replace("\r", '', $s2);
361 $s4 = str_replace('"', "'", $s3);
362 if ($type == 'notepad') {
363 $sout = '"' . $s4 . '"';