Merge lp:~therp-nl/banking-addons/6.1-add_camt_import into lp:banking-addons/6.1
- 6.1-add_camt_import
- Merge into 6.1
Proposed by
Stefan Rijnhart (Opener)
Status: | Merged |
---|---|
Merged at revision: | 187 |
Proposed branch: | lp:~therp-nl/banking-addons/6.1-add_camt_import |
Merge into: | lp:banking-addons/6.1 |
Diff against target: |
382 lines (+341/-1) 4 files modified
account_banking/parsers/models.py (+29/-1) account_banking_camt/__init__.py (+1/-0) account_banking_camt/__openerp__.py (+33/-0) account_banking_camt/camt.py (+278/-0) |
To merge this branch: | bzr merge lp:~therp-nl/banking-addons/6.1-add_camt_import |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Holger Brunn (Therp) | code review | Approve | |
Review via email: mp+190641@code.launchpad.net |
Commit message
[ADD] Support for SEPA CAMT.053 bank statements
Description of the change
To post a comment you must log in.
Revision history for this message
Holger Brunn (Therp) (hbrunn) wrote : | # |
review:
Approve
(code review)
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'account_banking/parsers/models.py' | |||
2 | --- account_banking/parsers/models.py 2012-01-17 08:48:10 +0000 | |||
3 | +++ account_banking/parsers/models.py 2013-10-11 12:39:01 +0000 | |||
4 | @@ -19,6 +19,7 @@ | |||
5 | 19 | # | 19 | # |
6 | 20 | ############################################################################## | 20 | ############################################################################## |
7 | 21 | 21 | ||
8 | 22 | import re | ||
9 | 22 | from tools.translate import _ | 23 | from tools.translate import _ |
10 | 23 | 24 | ||
11 | 24 | class mem_bank_statement(object): | 25 | class mem_bank_statement(object): |
12 | @@ -34,7 +35,7 @@ | |||
13 | 34 | # Lock attributes to enable parsers to trigger non-conformity faults | 35 | # Lock attributes to enable parsers to trigger non-conformity faults |
14 | 35 | __slots__ = [ | 36 | __slots__ = [ |
15 | 36 | 'start_balance','end_balance', 'date', 'local_account', | 37 | 'start_balance','end_balance', 'date', 'local_account', |
17 | 37 | 'local_currency', 'id', 'statements' | 38 | 'local_currency', 'id', 'transactions' |
18 | 38 | ] | 39 | ] |
19 | 39 | def __init__(self, *args, **kwargs): | 40 | def __init__(self, *args, **kwargs): |
20 | 40 | super(mem_bank_statement, self).__init__(*args, **kwargs) | 41 | super(mem_bank_statement, self).__init__(*args, **kwargs) |
21 | @@ -353,6 +354,33 @@ | |||
22 | 353 | name = "%s-%d" % (base, suffix) | 354 | name = "%s-%d" % (base, suffix) |
23 | 354 | return name | 355 | return name |
24 | 355 | 356 | ||
25 | 357 | def get_unique_account_identifier(self, cr, account): | ||
26 | 358 | """ | ||
27 | 359 | Get an identifier for a local bank account, based on the last | ||
28 | 360 | characters of the account number with minimum length 3. | ||
29 | 361 | The identifier should be unique amongst the company accounts | ||
30 | 362 | |||
31 | 363 | Presumably, the bank account is one of the company accounts | ||
32 | 364 | itself but importing bank statements for non-company accounts | ||
33 | 365 | is not prevented anywhere else in the system so the 'account' | ||
34 | 366 | param being a company account is not enforced here either. | ||
35 | 367 | """ | ||
36 | 368 | def normalize(account_no): | ||
37 | 369 | return re.sub('\s', '', account_no) | ||
38 | 370 | |||
39 | 371 | account = normalize(account) | ||
40 | 372 | cr.execute( | ||
41 | 373 | """SELECT acc_number FROM res_partner_bank | ||
42 | 374 | WHERE company_id IS NOT NULL""") | ||
43 | 375 | accounts = [normalize(row[0]) for row in cr.fetchall()] | ||
44 | 376 | tail_length = 3 | ||
45 | 377 | while tail_length <= len(account): | ||
46 | 378 | tail = account[-tail_length:] | ||
47 | 379 | if len([acc for acc in accounts if acc.endswith(tail)]) < 2: | ||
48 | 380 | return tail | ||
49 | 381 | tail_length += 1 | ||
50 | 382 | return account | ||
51 | 383 | |||
52 | 356 | def parse(self, cr, data): | 384 | def parse(self, cr, data): |
53 | 357 | ''' | 385 | ''' |
54 | 358 | Parse data. | 386 | Parse data. |
55 | 359 | 387 | ||
56 | === added directory 'account_banking_camt' | |||
57 | === added file 'account_banking_camt/__init__.py' | |||
58 | --- account_banking_camt/__init__.py 1970-01-01 00:00:00 +0000 | |||
59 | +++ account_banking_camt/__init__.py 2013-10-11 12:39:01 +0000 | |||
60 | @@ -0,0 +1,1 @@ | |||
61 | 1 | import camt | ||
62 | 0 | 2 | ||
63 | === added file 'account_banking_camt/__openerp__.py' | |||
64 | --- account_banking_camt/__openerp__.py 1970-01-01 00:00:00 +0000 | |||
65 | +++ account_banking_camt/__openerp__.py 2013-10-11 12:39:01 +0000 | |||
66 | @@ -0,0 +1,33 @@ | |||
67 | 1 | ############################################################################## | ||
68 | 2 | # | ||
69 | 3 | # Copyright (C) 2013 Therp BV (<http://therp.nl>) | ||
70 | 4 | # All Rights Reserved | ||
71 | 5 | # | ||
72 | 6 | # This program is free software: you can redistribute it and/or modify | ||
73 | 7 | # it under the terms of the GNU Affero General Public License as published by | ||
74 | 8 | # the Free Software Foundation, either version 3 of the License, or | ||
75 | 9 | # (at your option) any later version. | ||
76 | 10 | # | ||
77 | 11 | # This program is distributed in the hope that it will be useful, | ||
78 | 12 | # but WITHOUT ANY WARRANTY; without even the implied warranty of | ||
79 | 13 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | ||
80 | 14 | # GNU Affero General Public License for more details. | ||
81 | 15 | # | ||
82 | 16 | # You should have received a copy of the GNU Affero General Public License | ||
83 | 17 | # along with this program. If not, see <http://www.gnu.org/licenses/>. | ||
84 | 18 | # | ||
85 | 19 | ############################################################################## | ||
86 | 20 | { | ||
87 | 21 | 'name': 'CAMT Format Bank Statements Import', | ||
88 | 22 | 'version': '0.1', | ||
89 | 23 | 'license': 'AGPL-3', | ||
90 | 24 | 'author': 'Therp BV', | ||
91 | 25 | 'website': 'https://launchpad.net/banking-addons', | ||
92 | 26 | 'category': 'Banking addons', | ||
93 | 27 | 'depends': ['account_banking'], | ||
94 | 28 | 'description': ''' | ||
95 | 29 | Module to import SEPA CAMT.053 Format bank statement files. Based | ||
96 | 30 | on the Banking addons framework. | ||
97 | 31 | ''', | ||
98 | 32 | 'installable': True, | ||
99 | 33 | } | ||
100 | 0 | 34 | ||
101 | === added file 'account_banking_camt/camt.py' | |||
102 | --- account_banking_camt/camt.py 1970-01-01 00:00:00 +0000 | |||
103 | +++ account_banking_camt/camt.py 2013-10-11 12:39:01 +0000 | |||
104 | @@ -0,0 +1,278 @@ | |||
105 | 1 | # -*- coding: utf-8 -*- | ||
106 | 2 | ############################################################################## | ||
107 | 3 | # | ||
108 | 4 | # Copyright (C) 2013 Therp BV (<http://therp.nl>) | ||
109 | 5 | # All Rights Reserved | ||
110 | 6 | # | ||
111 | 7 | # This program is free software: you can redistribute it and/or modify | ||
112 | 8 | # it under the terms of the GNU Affero General Public License as published by | ||
113 | 9 | # the Free Software Foundation, either version 3 of the License, or | ||
114 | 10 | # (at your option) any later version. | ||
115 | 11 | # | ||
116 | 12 | # This program is distributed in the hope that it will be useful, | ||
117 | 13 | # but WITHOUT ANY WARRANTY; without even the implied warranty of | ||
118 | 14 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | ||
119 | 15 | # GNU Affero General Public License for more details. | ||
120 | 16 | # | ||
121 | 17 | # You should have received a copy of the GNU Affero General Public License | ||
122 | 18 | # along with this program. If not, see <http://www.gnu.org/licenses/>. | ||
123 | 19 | # | ||
124 | 20 | ############################################################################## | ||
125 | 21 | |||
126 | 22 | from lxml import etree | ||
127 | 23 | from openerp.osv.orm import except_orm | ||
128 | 24 | from account_banking.parsers import models | ||
129 | 25 | from account_banking.parsers.convert import str2date | ||
130 | 26 | |||
131 | 27 | bt = models.mem_bank_transaction | ||
132 | 28 | |||
133 | 29 | class transaction(models.mem_bank_transaction): | ||
134 | 30 | |||
135 | 31 | def __init__(self, values, *args, **kwargs): | ||
136 | 32 | super(transaction, self).__init__(*args, **kwargs) | ||
137 | 33 | for attr in values: | ||
138 | 34 | setattr(self, attr, values[attr]) | ||
139 | 35 | |||
140 | 36 | def is_valid(self): | ||
141 | 37 | return not self.error_message | ||
142 | 38 | |||
143 | 39 | class parser(models.parser): | ||
144 | 40 | code = 'CAMT' | ||
145 | 41 | country_code = 'NL' | ||
146 | 42 | name = 'Generic CAMT Format' | ||
147 | 43 | doc = '''\ | ||
148 | 44 | CAMT Format parser | ||
149 | 45 | ''' | ||
150 | 46 | |||
151 | 47 | def tag(self, node): | ||
152 | 48 | """ | ||
153 | 49 | Return the tag of a node, stripped from its namespace | ||
154 | 50 | """ | ||
155 | 51 | return node.tag[len(self.ns):] | ||
156 | 52 | |||
157 | 53 | def assert_tag(self, node, expected): | ||
158 | 54 | """ | ||
159 | 55 | Get node's stripped tag and compare with expected | ||
160 | 56 | """ | ||
161 | 57 | assert self.tag(node) == expected, ( | ||
162 | 58 | "Expected tag '%s', got '%s' instead" % | ||
163 | 59 | (self.tag(node), expected)) | ||
164 | 60 | |||
165 | 61 | def xpath(self, node, expr): | ||
166 | 62 | """ | ||
167 | 63 | Wrap namespaces argument into call to Element.xpath(): | ||
168 | 64 | |||
169 | 65 | self.xpath(node, './ns:Acct/ns:Id') | ||
170 | 66 | """ | ||
171 | 67 | return node.xpath(expr, namespaces={'ns': self.ns[1:-1]}) | ||
172 | 68 | |||
173 | 69 | def find(self, node, expr): | ||
174 | 70 | """ | ||
175 | 71 | Like xpath(), but return first result if any or else False | ||
176 | 72 | |||
177 | 73 | Return None to test nodes for being truesy | ||
178 | 74 | """ | ||
179 | 75 | result = node.xpath(expr, namespaces={'ns': self.ns[1:-1]}) | ||
180 | 76 | if result: | ||
181 | 77 | return result[0] | ||
182 | 78 | return None | ||
183 | 79 | |||
184 | 80 | def get_balance_type_node(self, node, balance_type): | ||
185 | 81 | """ | ||
186 | 82 | :param node: BkToCstmrStmt/Stmt/Bal node | ||
187 | 83 | :param balance type: one of 'OPBD', 'PRCD', 'ITBD', 'CLBD' | ||
188 | 84 | """ | ||
189 | 85 | code_expr = './ns:Bal/ns:Tp/ns:CdOrPrtry/ns:Cd[text()="%s"]/../../..' % balance_type | ||
190 | 86 | return self.xpath(node, code_expr) | ||
191 | 87 | |||
192 | 88 | def parse_amount(self, node): | ||
193 | 89 | """ | ||
194 | 90 | Parse an element that contains both Amount and CreditDebitIndicator | ||
195 | 91 | |||
196 | 92 | :return: signed amount | ||
197 | 93 | :returntype: float | ||
198 | 94 | """ | ||
199 | 95 | sign = -1 if node.find(self.ns + 'CdtDbtInd').text == 'DBIT' else 1 | ||
200 | 96 | return sign * float(node.find(self.ns + 'Amt').text) | ||
201 | 97 | |||
202 | 98 | def get_start_balance(self, node): | ||
203 | 99 | """ | ||
204 | 100 | Find the (only) balance node with code OpeningBalance, or | ||
205 | 101 | the only one with code 'PreviousClosingBalance' | ||
206 | 102 | or the first balance node with code InterimBalance in | ||
207 | 103 | the case of preceeding pagination. | ||
208 | 104 | |||
209 | 105 | :param node: BkToCstmrStmt/Stmt/Bal node | ||
210 | 106 | """ | ||
211 | 107 | nodes = ( | ||
212 | 108 | self.get_balance_type_node(node, 'OPBD') or | ||
213 | 109 | self.get_balance_type_node(node, 'PRCD') or | ||
214 | 110 | self.get_balance_type_node(node, 'ITBD')) | ||
215 | 111 | return self.parse_amount(nodes[0]) | ||
216 | 112 | |||
217 | 113 | def get_end_balance(self, node): | ||
218 | 114 | """ | ||
219 | 115 | Find the (only) balance node with code ClosingBalance, or | ||
220 | 116 | the second (and last) balance node with code InterimBalance in | ||
221 | 117 | the case of continued pagination. | ||
222 | 118 | |||
223 | 119 | :param node: BkToCstmrStmt/Stmt/Bal node | ||
224 | 120 | """ | ||
225 | 121 | nodes = ( | ||
226 | 122 | self.get_balance_type_node(node, 'CLBD') or | ||
227 | 123 | self.get_balance_type_node(node, 'ITBD')) | ||
228 | 124 | return self.parse_amount(nodes[-1]) | ||
229 | 125 | |||
230 | 126 | def parse_Stmt(self, cr, node): | ||
231 | 127 | """ | ||
232 | 128 | Parse a single Stmt node. | ||
233 | 129 | |||
234 | 130 | Be sure to craft a unique, but short enough statement identifier, | ||
235 | 131 | as it is used as the basis of the generated move lines' names | ||
236 | 132 | which overflow when using the full IBAN and CAMT statement id. | ||
237 | 133 | """ | ||
238 | 134 | statement = models.mem_bank_statement() | ||
239 | 135 | statement.local_account = ( | ||
240 | 136 | self.xpath(node, './ns:Acct/ns:Id/ns:IBAN')[0].text | ||
241 | 137 | if self.xpath(node, './ns:Acct/ns:Id/ns:IBAN') | ||
242 | 138 | else self.xpath(node, './ns:Acct/ns:Id/ns:Othr/ns:Id')[0].text) | ||
243 | 139 | |||
244 | 140 | identifier = node.find(self.ns + 'Id').text | ||
245 | 141 | if identifier.upper().startswith('CAMT053'): | ||
246 | 142 | identifier = identifier[7:] | ||
247 | 143 | statement.id = self.get_unique_statement_id( | ||
248 | 144 | cr, "%s-%s" % ( | ||
249 | 145 | self.get_unique_account_identifier( | ||
250 | 146 | cr, statement.local_account), | ||
251 | 147 | identifier) | ||
252 | 148 | ) | ||
253 | 149 | |||
254 | 150 | statement.local_currency = self.xpath(node, './ns:Acct/ns:Ccy')[0].text | ||
255 | 151 | statement.start_balance = self.get_start_balance(node) | ||
256 | 152 | statement.end_balance = self.get_end_balance(node) | ||
257 | 153 | number = 0 | ||
258 | 154 | for Ntry in self.xpath(node, './ns:Ntry'): | ||
259 | 155 | transaction_detail = self.parse_Ntry(Ntry) | ||
260 | 156 | if number == 0: | ||
261 | 157 | # Take the statement date from the first transaction | ||
262 | 158 | statement.date = str2date( | ||
263 | 159 | transaction_detail['execution_date'], "%Y-%m-%d") | ||
264 | 160 | number += 1 | ||
265 | 161 | transaction_detail['id'] = str(number).zfill(4) | ||
266 | 162 | statement.transactions.append( | ||
267 | 163 | transaction(transaction_detail)) | ||
268 | 164 | return statement | ||
269 | 165 | |||
270 | 166 | def get_transfer_type(self, node): | ||
271 | 167 | """ | ||
272 | 168 | Map entry descriptions to transfer types. To extend with | ||
273 | 169 | proper mapping from BkTxCd/Domn/Cd/Fmly/Cd to transfer types | ||
274 | 170 | if we can get our hands on real life samples. | ||
275 | 171 | |||
276 | 172 | For now, leave as a hook for bank specific overrides to map | ||
277 | 173 | properietary codes from BkTxCd/Prtry/Cd. | ||
278 | 174 | |||
279 | 175 | :param node: Ntry node | ||
280 | 176 | """ | ||
281 | 177 | return bt.ORDER | ||
282 | 178 | |||
283 | 179 | def parse_Ntry(self, node): | ||
284 | 180 | """ | ||
285 | 181 | :param node: Ntry node | ||
286 | 182 | """ | ||
287 | 183 | entry_details = { | ||
288 | 184 | 'execution_date': self.xpath(node, './ns:BookgDt/ns:Dt')[0].text, | ||
289 | 185 | 'effective_date': self.xpath(node, './ns:ValDt/ns:Dt')[0].text, | ||
290 | 186 | 'transfer_type': self.get_transfer_type(node), | ||
291 | 187 | 'transferred_amount': self.parse_amount(node) | ||
292 | 188 | } | ||
293 | 189 | TxDtls = self.xpath(node, './ns:NtryDtls/ns:TxDtls') | ||
294 | 190 | if len(TxDtls) == 1: | ||
295 | 191 | vals = self.parse_TxDtls(TxDtls[0], entry_details) | ||
296 | 192 | else: | ||
297 | 193 | vals = entry_details | ||
298 | 194 | return vals | ||
299 | 195 | |||
300 | 196 | def get_party_values(self, TxDtls): | ||
301 | 197 | """ | ||
302 | 198 | Determine to get either the debtor or creditor party node | ||
303 | 199 | and extract the available data from it | ||
304 | 200 | """ | ||
305 | 201 | vals = {} | ||
306 | 202 | party_type = self.find( | ||
307 | 203 | TxDtls, '../../ns:CdtDbtInd').text == 'CRDT' and 'Dbtr' or 'Cdtr' | ||
308 | 204 | party_node = self.find(TxDtls, './ns:RltdPties/ns:%s' % party_type) | ||
309 | 205 | account_node = self.find( | ||
310 | 206 | TxDtls, './ns:RltdPties/ns:%sAcct/ns:Id' % party_type) | ||
311 | 207 | bic_node = self.find( | ||
312 | 208 | TxDtls, | ||
313 | 209 | './ns:RltdAgts/ns:%sAgt/ns:FinInstnId/ns:BIC' % party_type) | ||
314 | 210 | if party_node is not None: | ||
315 | 211 | name_node = self.find(party_node, './ns:Nm') | ||
316 | 212 | vals['remote_owner'] = ( | ||
317 | 213 | name_node.text if name_node is not None else False) | ||
318 | 214 | country_node = self.find(party_node, './ns:PstlAdr/ns:Ctry') | ||
319 | 215 | vals['remote_owner_country'] = ( | ||
320 | 216 | country_node.text if country_node is not None else False) | ||
321 | 217 | address_node = self.find(party_node, './ns:PstlAdr/ns:AdrLine') | ||
322 | 218 | if address_node is not None: | ||
323 | 219 | vals['remote_owner_address'] = [address_node.text] | ||
324 | 220 | if account_node is not None: | ||
325 | 221 | iban_node = self.find(account_node, './ns:IBAN') | ||
326 | 222 | if iban_node is not None: | ||
327 | 223 | vals['remote_account'] = iban_node.text | ||
328 | 224 | if bic_node is not None: | ||
329 | 225 | vals['remote_bank_bic'] = bic_node.text | ||
330 | 226 | else: | ||
331 | 227 | domestic_node = self.find(account_node, './ns:Othr/ns:Id') | ||
332 | 228 | vals['remote_account'] = ( | ||
333 | 229 | domestic_node.text if domestic_node is not None else False) | ||
334 | 230 | return vals | ||
335 | 231 | |||
336 | 232 | def parse_TxDtls(self, TxDtls, entry_values): | ||
337 | 233 | """ | ||
338 | 234 | Parse a single TxDtls node | ||
339 | 235 | """ | ||
340 | 236 | vals = dict(entry_values) | ||
341 | 237 | unstructured = self.xpath(TxDtls, './ns:RmtInf/ns:Ustrd') | ||
342 | 238 | if unstructured: | ||
343 | 239 | vals['message'] = ' '.join([x.text for x in unstructured]) | ||
344 | 240 | structured = self.find( | ||
345 | 241 | TxDtls, './ns:RmtInf/ns:Strd/ns:CdtrRefInf/ns:Ref') | ||
346 | 242 | if structured is None or not structured.text: | ||
347 | 243 | structured = self.find(TxDtls, './ns:Refs/ns:EndToEndId') | ||
348 | 244 | if structured is not None: | ||
349 | 245 | vals['reference'] = structured.text | ||
350 | 246 | else: | ||
351 | 247 | if vals.get('message'): | ||
352 | 248 | vals['reference'] = vals['message'] | ||
353 | 249 | vals.update(self.get_party_values(TxDtls)) | ||
354 | 250 | return vals | ||
355 | 251 | |||
356 | 252 | def check_version(self): | ||
357 | 253 | """ | ||
358 | 254 | Sanity check the document's namespace | ||
359 | 255 | """ | ||
360 | 256 | if not self.ns.startswith('{urn:iso:std:iso:20022:tech:xsd:camt.'): | ||
361 | 257 | raise except_orm( | ||
362 | 258 | "Error", | ||
363 | 259 | "This does not seem to be a CAMT format bank statement.") | ||
364 | 260 | |||
365 | 261 | if not self.ns.startswith('{urn:iso:std:iso:20022:tech:xsd:camt.053.'): | ||
366 | 262 | raise except_orm( | ||
367 | 263 | "Error", | ||
368 | 264 | "Only CAMT.053 is supported at the moment.") | ||
369 | 265 | return True | ||
370 | 266 | |||
371 | 267 | def parse(self, cr, data): | ||
372 | 268 | """ | ||
373 | 269 | Parse a CAMT053 XML file | ||
374 | 270 | """ | ||
375 | 271 | root = etree.fromstring(data) | ||
376 | 272 | self.ns = root.tag[:root.tag.index("}") + 1] | ||
377 | 273 | self.check_version() | ||
378 | 274 | self.assert_tag(root[0][0], 'GrpHdr') | ||
379 | 275 | statements = [] | ||
380 | 276 | for node in root[0][1:]: | ||
381 | 277 | statements.append(self.parse_Stmt(cr, node)) | ||
382 | 278 | return statements |
see https:/ /code.launchpad .net/~therp- nl/banking- addons/ 7.0-add_ camt_import/ +merge/ 190638/ comments/ 438449