addons/account_coda/wizard/account_coda_import.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #
   6 #    Copyright (c) 2011 Noviat nv/sa (www.noviat.be). All rights reserved.
   7 #
   8 #    This program is free software: you can redistribute it and/or modify
   9 #    it under the terms of the GNU Affero General Public License as
  10 #    published by the Free Software Foundation, either version 3 of the
  11 #    License, or (at your option) any later version.
  12 #
  13 #    This program is distributed in the hope that it will be useful,
  14 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  15 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16 #    GNU Affero General Public License for more details.
  17 #
  18 #    You should have received a copy of the GNU Affero General Public License
  19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20 #
  21 ##############################################################################
  22
  23 import time
  24 import base64
  25 from osv import fields,osv
  26 from tools.translate import _
  27 import netsvc
  28 import re
  29 from traceback import format_exception
  30 from sys import exc_info
  31 logger=netsvc.Logger()
  32
  33 class account_coda_import(osv.osv_memory):
  34     _name = 'account.coda.import'
  35     _description = 'Import CODA File'
  36     _columns = {
  37         'coda_data': fields.binary('CODA File', required=True),
  38         'coda_fname': fields.char('CODA Filename', size=128, required=True),
  39         'note':fields.text('Log'),
  40     }
  41     _defaults = {
  42         'coda_fname': lambda *a: '',
  43     }
  44
  45     def coda_parsing(self, cr, uid, ids, context=None, batch=False, codafile=None, codafilename=None):
  46         if context is None:
  47             context = {}
  48         if batch:
  49             codafile = str(codafile)
  50             codafilename = codafilename
  51         else:
  52             data=self.browse(cr,uid,ids)[0]
  53             try:
  54                 codafile = data.coda_data
  55                 codafilename = data.coda_fname
  56             except:
  57                 raise osv.except_osv(_('Error!'), _('Wizard in incorrect state. Please hit the Cancel button!'))
  58                 return {}
  59
  60         currency_obj = self.pool.get('res.currency')
  61         coda_bank_account_obj = self.pool.get('coda.bank.account')
  62         trans_type_obj = self.pool.get('account.coda.trans.type')
  63         trans_code_obj = self.pool.get('account.coda.trans.code')
  64         trans_category_obj = self.pool.get('account.coda.trans.category')
  65         comm_type_obj = self.pool.get('account.coda.comm.type')
  66         journal_obj = self.pool.get('account.journal')
  67         period_obj = self.pool.get('account.period')
  68         partner_bank_obj = self.pool.get('res.partner.bank')
  69         coda_obj = self.pool.get('account.coda')
  70         coda_st_obj = self.pool.get('coda.bank.statement')
  71         coda_st_line_obj = self.pool.get('coda.bank.statement.line')
  72         bank_st_obj = self.pool.get('account.bank.statement')
  73         bank_st_line_obj = self.pool.get('account.bank.statement.line')
  74         glob_obj = self.pool.get('account.bank.statement.line.global')
  75         inv_obj = self.pool.get('account.invoice')
  76         move_obj = self.pool.get('account.move')
  77         move_line_obj = self.pool.get('account.move.line')
  78         voucher_obj = self.pool.get('account.voucher')
  79         voucher_line_obj = self.pool.get('account.voucher.line')
  80         seq_obj = self.pool.get('ir.sequence')
  81         mod_obj = self.pool.get('ir.model.data')
  82
  83         coda_bank_table = coda_bank_account_obj.read(cr, uid, coda_bank_account_obj.search(cr, uid, []), context=context)
  84         for coda_bank in coda_bank_table:
  85             coda_bank.update({'journal_code': coda_bank['journal'] and journal_obj.browse(cr, uid, coda_bank['journal'][0], context=context).code or ''})
  86             coda_bank.update({'iban': partner_bank_obj.browse(cr, uid, coda_bank['bank_id'][0], context=context).iban})
  87             coda_bank.update({'acc_number': partner_bank_obj.browse(cr, uid, coda_bank['bank_id'][0], context=context).acc_number})
  88             coda_bank.update({'currency_name': currency_obj.browse(cr, uid, coda_bank['currency'][0], context=context).name})
  89         trans_type_table = trans_type_obj.read(cr, uid, trans_type_obj.search(cr, uid, []), context=context)
  90         trans_code_table = trans_code_obj.read(cr, uid, trans_code_obj.search(cr, uid, []), context=context)
  91         trans_category_table = trans_category_obj.read(cr, uid, trans_category_obj.search(cr, uid, []), context=context)
  92         comm_type_table = comm_type_obj.read(cr, uid, comm_type_obj.search(cr, uid, []), context=context)
  93
  94         err_string = ''
  95         err_code = None
  96         err_log = ''
  97         coda_statements = []
  98         recordlist = unicode(base64.decodestring(codafile), 'windows-1252', 'strict').split('\n')
  99
 100         for line in recordlist:
 101
 102             if not line:
 103                 pass
 104             elif line[0] == '0':
 105                 # start of a new statement within the CODA file
 106                 coda_statement = {}
 107                 coda_parsing_note = ''
 108                 coda_statement_lines = {}
 109                 st_line_seq = 0
 110                 glob_lvl_stack = [0]
 111                 # header data
 112                 coda_statement['currency'] = 'EUR'   # default currency
 113                 coda_statement['version'] = line[127]
 114                 coda_version = line[127]
 115                 if coda_version not in ['1','2']:
 116                     err_string = _('\nCODA V%s statements are not supported, please contact your bank!') % coda_version
 117                     err_code = 'R0001'
 118                     if batch:
 119                         return (err_code, err_string)
 120                     raise osv.except_osv(_('Data Error!'), err_string)
 121                 coda_statement['coda_statement_lines'] = {}
 122                 coda_statement['date'] = str2date(line[5:11])
 123                 period_id = period_obj.search(cr , uid, [('date_start' ,'<=', coda_statement['date']), ('date_stop','>=',coda_statement['date'])])
 124                 if not period_id:
 125                     err_string = _("\nThe CODA creation date doesn't fall within a defined Accounting Period!" \
 126                           "\nPlease create the Accounting Period for date %s.") % coda_statement['date']
 127                     err_code = 'R0002'
 128                     if batch:
 129                         return (err_code, err_string)
 130                     raise osv.except_osv(_('Data Error!'), err_string)
 131                 coda_statement['period_id'] = period_id[0]
 132                 coda_statement['state'] = 'draft'
 133
 134                 coda_id = coda_obj.search(cr, uid,[
 135                     ('name', '=', codafilename),
 136                     ('coda_creation_date', '=', coda_statement['date']),
 137                     ])
 138                 if coda_id:
 139                     err_string = _("\nCODA File with Filename '%s' and Creation Date '%s' has already been imported !") \
 140                         % (codafilename, coda_statement['date'])
 141                     err_code = 'W0001'
 142                     if batch:
 143                         return (err_code, err_string)
 144                     raise osv.except_osv(_('Warning !'), err_string)
 145
 146             elif line[0] == '1':
 147                 if coda_version == '1':
 148                     coda_statement['acc_number'] = line[5:17]
 149                     if line[18:21].strip():
 150                         coda_statement['currency'] = line[18:21]
 151                 elif line[1] == '0':                                # Belgian bank account BBAN structure
 152                     coda_statement['acc_number'] = line[5:17]
 153                     coda_statement['currency'] = line[18:21]
 154                 elif line[1] == '1':    # foreign bank account BBAN structure
 155                     err_string = _('\nForeign bank accounts with BBAN structure are not supported !')
 156                     err_code = 'R1001'
 157                     if batch:
 158                         return (err_code, err_string)
 159                     raise osv.except_osv(_('Data Error!'), err_string)
 160                 elif line[1] == '2':    # Belgian bank account IBAN structure
 161                     coda_statement['acc_number']=line[5:21]
 162                     coda_statement['currency'] = line[39:42]
 163                 elif line[1] == '3':    # foreign bank account IBAN structure
 164                     err_string = _('\nForeign bank accounts with IBAN structure are not supported !')
 165                     err_code = 'R1002'
 166                     if batch:
 167                         return (err_code, err_string)
 168                     raise osv.except_osv(_('Data Error!'), err_string)
 169                 else:
 170                     err_string = _('\nUnsupported bank account structure !')
 171                     err_code = 'R1003'
 172                     if batch:
 173                         return (err_code, err_string)
 174                     raise osv.except_osv(_('Data Error!'), err_string)
 175                 coda_statement['description'] = line[90:125].strip()
 176                 cba_filter = lambda x: ((coda_statement['acc_number'] in (x['iban'] or '')) or (coda_statement['acc_number'] == x['acc_number'])) \
 177                     and (coda_statement['currency'] == x['currency_name']) and (coda_statement['description'] == (x['description1'] or x['description2'] or ''))
 178                 coda_bank =  filter(cba_filter, coda_bank_table)
 179                 if coda_bank:
 180                     coda_bank = coda_bank[0]
 181                     coda_statement['type'] = coda_bank['state']
 182                     coda_statement['journal_id'] = coda_bank['journal'] and coda_bank['journal'][0]
 183                     coda_statement['currency_id'] = coda_bank['currency'][0]
 184                     coda_statement['coda_bank_account_id'] = coda_bank['id']
 185                     def_pay_acc = coda_bank['def_payable'][0]
 186                     def_rec_acc = coda_bank['def_receivable'][0]
 187                     awaiting_acc = coda_bank['awaiting_account'][0]
 188                     transfer_acc = coda_bank['transfer_account'][0]
 189                     find_bbacom = coda_bank['find_bbacom']
 190                     find_partner = coda_bank['find_partner']
 191                 else:
 192                     err_string = _("\nNo matching CODA Bank Account Configuration record found !") + \
 193                         _("\nPlease check if the 'Bank Account Number', 'Currency' and 'Account Description' fields of your configuration record match with '%s', '%s' and '%s' !") \
 194                         % (coda_statement['acc_number'], coda_statement['currency'], coda_statement['description'])
 195                     err_code = 'R1004'
 196                     if batch:
 197                         return (err_code, err_string)
 198                     raise osv.except_osv(_('Data Error!'), err_string)
 199                 bal_start = list2float(line[43:58])             # old balance data
 200                 if line[42] == '1':    # 1= Debit
 201                     bal_start = - bal_start
 202                 coda_statement['balance_start'] = bal_start
 203                 coda_statement['acc_holder'] = line[64:90]
 204                 coda_statement['paper_seq_number'] = line[2:5]
 205                 coda_statement['coda_seq_number'] = line[125:128]
 206                 if coda_bank['coda_st_naming']:
 207                     coda_statement['name'] = coda_bank['coda_st_naming'] % {
 208                        'code': coda_bank['journal_code'] or '',
 209                        'year': time.strftime('%Y'),
 210                        'y': time.strftime('%y'),
 211                        'coda': line[125:128],
 212                        'paper': line[2:5],
 213                     }
 214                 else:
 215                     coda_statement['name'] = '/'
 216
 217             elif line[0] == '2':
 218                 # movement data record 2
 219                 if line[1] == '1':
 220                     # movement data record 2.1
 221                     st_line = {}
 222                     st_line_seq = st_line_seq + 1
 223                     st_line['sequence'] = st_line_seq
 224                     st_line['type'] = 'general'
 225                     st_line['reconcile'] = False
 226                     st_line['struct_comm_type'] = ''
 227                     st_line['struct_comm_type_desc'] = ''
 228                     st_line['struct_comm_101'] = ''
 229                     st_line['communication'] = ''
 230                     st_line['partner_id'] = 0
 231                     st_line['account_id'] = 0
 232                     st_line['counterparty_name'] = ''
 233                     st_line['counterparty_bic'] = ''
 234                     st_line['counterparty_number'] = ''
 235                     st_line['counterparty_currency'] = ''
 236                     st_line['glob_lvl_flag'] = False
 237                     st_line['globalisation_id'] = 0
 238                     st_line['globalisation_code'] = ''
 239                     st_line['globalisation_amount'] = False
 240                     st_line['amount'] = False
 241
 242                     st_line['ref'] = line[2:10]
 243                     st_line['trans_ref'] = line[10:31]
 244                     st_line_amt = list2float(line[32:47])
 245                     if line[31] == '1':    # 1=debit
 246                         st_line_amt = - st_line_amt
 247                     # processing of amount depending on globalisation code
 248                     glob_lvl_flag = int(line[124])
 249                     if glob_lvl_flag > 0:
 250                         if glob_lvl_stack[-1] == glob_lvl_flag:
 251                             st_line['glob_lvl_flag'] = glob_lvl_flag
 252                             st_line['amount'] = st_line_amt
 253                             glob_lvl_stack.pop()
 254                         else:
 255                             glob_lvl_stack.append(glob_lvl_flag)
 256                             st_line['type'] = 'globalisation'
 257                             st_line['glob_lvl_flag'] = glob_lvl_flag
 258                             st_line['globalisation_amount'] = st_line_amt
 259                             st_line['account_id'] = None
 260                     else:
 261                         st_line['amount'] = st_line_amt
 262                     # positions 48-53 : Value date or 000000 if not known (DDMMYY)
 263                     st_line['val_date'] = str2date(line[47:53])
 264                     # positions 54-61 : transaction code
 265                     st_line['trans_type'] = line[53]
 266                     trans_type =  filter(lambda x: st_line['trans_type'] == x['type'], trans_type_table)
 267                     if not trans_type:
 268                         err_string = _('\nThe File contains an invalid CODA Transaction Type : %s!') % st_line['trans_type']
 269                         err_code = 'R2001'
 270                         if batch:
 271                             return (err_code, err_string)
 272                         raise osv.except_osv(_('Data Error!'), err_string)
 273                     st_line['trans_type_desc'] = trans_type[0]['description']
 274                     st_line['trans_family'] = line[54:56]
 275                     trans_family =  filter(lambda x: (x['type'] == 'family') and (st_line['trans_family'] == x['code']), trans_code_table)
 276                     if not trans_family:
 277                         err_string = _('\nThe File contains an invalid CODA Transaction Family : %s!') % st_line['trans_family']
 278                         err_code = 'R2002'
 279                         if batch:
 280                             return (err_code, err_string)
 281                         raise osv.except_osv(_('Data Error!'), err_string)
 282                     st_line['trans_family_desc'] = trans_family[0]['description']
 283                     st_line['trans_code'] = line[56:58]
 284                     trans_code =  filter(lambda x: (x['type'] == 'code') and (st_line['trans_code'] == x['code']) and (trans_family[0]['id'] == x['parent_id'][0]),
 285                         trans_code_table)
 286                     if trans_code:
 287                         st_line['trans_code_desc'] = trans_code[0]['description']
 288                     else:
 289                         st_line['trans_code_desc'] = _('Transaction Code unknown, please consult your bank.')
 290                     st_line['trans_category'] = line[58:61]
 291                     trans_category =  filter(lambda x: st_line['trans_category'] == x['category'], trans_category_table)
 292                     if trans_category:
 293                         st_line['trans_category_desc'] = trans_category[0]['description']
 294                     else:
 295                         st_line['trans_category_desc'] = _('Transaction Category unknown, please consult your bank.')
 296                     # positions 61-115 : communication
 297                     if line[61] == '1':
 298                         st_line['struct_comm_type'] = line[62:65]
 299                         comm_type =  filter(lambda x: st_line['struct_comm_type'] == x['code'], comm_type_table)
 300                         if not comm_type:
 301                             err_string = _('\nThe File contains an invalid Structured Communication Type : %s!') % st_line['struct_comm_type']
 302                             err_code = 'R2003'
 303                             if batch:
 304                                 return (err_code, err_string)
 305                             raise osv.except_osv(_('Data Error!'), err_string)
 306                         st_line['struct_comm_type_desc'] = comm_type[0]['description']
 307                         st_line['communication'] = st_line['name'] = line[65:115]
 308                         if st_line['struct_comm_type'] == '101':
 309                             bbacomm = line[65:77]
 310                             st_line['struct_comm_101'] = st_line['name'] = '+++' + bbacomm[0:3] + '/' + bbacomm[3:7] + '/' + bbacomm[7:] + '+++'
 311                     else:
 312                         st_line['communication'] = st_line['name'] = line[62:115]
 313                     st_line['entry_date'] = str2date(line[115:121])
 314                     # positions 122-124 not processed
 315                     coda_statement_lines[st_line_seq] = st_line
 316                     coda_statement['coda_statement_lines'] = coda_statement_lines
 317                 elif line[1] == '2':
 318                     # movement data record 2.2
 319                     if coda_statement['coda_statement_lines'][st_line_seq]['ref'] != line[2:10]:
 320                         err_string = _('\nCODA parsing error on movement data record 2.2, seq nr %s!'    \
 321                             '\nPlease report this issue via your OpenERP support channel.') % line[2:10]
 322                         err_code = 'R2004'
 323                         if batch:
 324                             return (err_code, err_string)
 325                         raise osv.except_osv(_('Error!'), err_string)
 326                     coda_statement['coda_statement_lines'][st_line_seq]['name'] += line[10:63]
 327                     coda_statement['coda_statement_lines'][st_line_seq]['communication'] += line[10:63]
 328                     coda_statement['coda_statement_lines'][st_line_seq]['counterparty_bic'] = line[98:109].strip()
 329                 elif line[1] == '3':
 330                     # movement data record 2.3
 331                     if coda_statement['coda_statement_lines'][st_line_seq]['ref'] != line[2:10]:
 332                         err_string = _('\nCODA parsing error on movement data record 2.3, seq nr %s!'    \
 333                             '\nPlease report this issue via your OpenERP support channel.') % line[2:10]
 334                         err_code = 'R2005'
 335                         if batch:
 336                             return (err_code, err_string)
 337                         raise osv.except_osv(_('Error!'), err_string)
 338                     st_line = coda_statement_lines[st_line_seq]
 339                     if coda_version == '1':
 340                         counterparty_number = line[10:22]
 341                         counterparty_name = line[47:125].strip()
 342                         counterparty_currency = ''
 343                     else:
 344                         if line[22] == ' ':
 345                             counterparty_number = line[10:22]
 346                             counterparty_currency = line[23:26].strip()
 347                         else:
 348                             counterparty_number = line[10:44].strip()
 349                             counterparty_currency = line[44:47].strip()
 350                         counterparty_name = line[47:82].strip()
 351                         st_line['name'] += line[82:125]
 352                         st_line['communication'] += line[82:125]
 353                     st_line['counterparty_number'] = counterparty_number
 354                     st_line['counterparty_currency'] = counterparty_currency
 355                     st_line['counterparty_name'] = counterparty_name
 356                     if counterparty_currency not in [coda_bank['currency_name'], '']:
 357                         err_string = _('\nCODA parsing error on movement data record 2.3, seq nr %s!'    \
 358                             '\nPlease report this issue via your OpenERP support channel.') % line[2:10]
 359                         err_code = 'R2006'
 360                         if batch:
 361                             return (err_code, err_string)
 362                         raise osv.except_osv(_('Error!'), err_string)
 363
 364                     # partner matching and reconciliation
 365                     if st_line['type'] == 'general':
 366                         match = False
 367                         bank_ids = False
 368                         # prepare reconciliation for bba scor
 369                         reference = st_line['struct_comm_101']
 370                         if reference and find_bbacom:
 371                             inv_ids = inv_obj.search(cr , uid, [('reference' ,'=', reference), ('reference_type' ,'=', 'bba')])
 372                             if inv_ids:
 373                                 invoice = inv_obj.browse(cr, uid, inv_ids[0])
 374                                 partner = invoice.partner_id
 375                                 st_line['partner_id'] = partner.id
 376                                 if invoice.type in ['in_invoice', 'in_refund']:
 377                                     st_line['account_id'] = partner.property_account_payable.id or def_pay_acc
 378                                     st_line['type'] = 'supplier'
 379                                 else:
 380                                     st_line['account_id'] = partner.property_account_receivable.id or def_rec_acc
 381                                     st_line['type'] = 'customer'
 382                                 if invoice.type in ['in_invoice', 'out_invoice']:
 383                                     iml_ids = move_line_obj.search(cr, uid, [('move_id', '=', invoice.move_id.id), ('reconcile_id', '=', False), ('account_id.reconcile', '=', True)])
 384                                 if iml_ids:
 385                                     st_line['reconcile'] = iml_ids[0]
 386                                 match = True
 387                             else:
 388                                 coda_parsing_note += _("\n    Bank Statement '%s' line '%s':" \
 389                                     "\n        There is no invoice matching the Structured Communication '%s'!" \
 390                                     "\n        Please verify and adjust the invoice and perform the import again or otherwise change the corresponding entry manually in the generated Bank Statement.") \
 391                                     % (coda_statement['name'], st_line['ref'], reference)
 392                         # lookup partner via counterparty_number
 393                         if not match and counterparty_number:
 394                             cba_filter = lambda x: ((counterparty_number in (x['iban'] or '')) or (counterparty_number == x['acc_number'])) \
 395                                 and (x['state'] == 'normal')
 396                             transfer_account =  filter(cba_filter, coda_bank_table)
 397                             if transfer_account:
 398                                 st_line['account_id'] = transfer_acc
 399                                 match = True
 400                             elif find_partner:
 401                                 bank_ids = partner_bank_obj.search(cr,uid,[('acc_number','=', counterparty_number)])
 402                         if not match and find_partner and bank_ids:
 403                             if len(bank_ids) > 1:
 404                                 coda_parsing_note += _("\n    Bank Statement '%s' line '%s':" \
 405                                     "\n        No partner record assigned: There are multiple partners with the same Bank Account Number '%s'!" \
 406                                     "\n        Please correct the configuration and perform the import again or otherwise change the corresponding entry manually in the generated Bank Statement.") \
 407                                     % (coda_statement['name'], st_line['ref'], counterparty_number)
 408                             else:
 409                                 bank = partner_bank_obj.browse(cr, uid, bank_ids[0], context)
 410                                 st_line['partner_id'] = bank.partner_id.id
 411                                 match = True
 412                                 if st_line['amount'] < 0:
 413                                     st_line['account_id'] = bank.partner_id.property_account_payable.id or def_pay_acc
 414                                     st_line['type'] = 'supplier'
 415                                 else:
 416                                     st_line['account_id'] = bank.partner_id.property_account_receivable.id or def_rec_acc
 417                                     st_line['type'] = 'customer'
 418                         elif not match and find_partner:
 419                             if counterparty_number:
 420                                 coda_parsing_note += _("\n    Bank Statement '%s' line '%s':" \
 421                                     "\n        The bank account '%s' is not defined for the partner '%s'!" \
 422                                     "\n        Please correct the configuration and perform the import again or otherwise change the corresponding entry manually in the generated Bank Statement.") \
 423                                     % (coda_statement['name'], st_line['ref'],
 424                                     counterparty_number, counterparty_name)
 425                             else:
 426                                 coda_parsing_note += _("\n    Bank Statement '%s' line '%s':" \
 427                                     "\n        No matching partner record found!" \
 428                                     "\n        Please adjust the corresponding entry manually in the generated Bank Statement.") \
 429                                     % (coda_statement['name'], st_line['ref'])
 430                             st_line['account_id'] = awaiting_acc
 431                     # end of partner record lookup
 432                     coda_statement_lines[st_line_seq] = st_line
 433                     coda_statement['coda_statement_lines'] = coda_statement_lines
 434                 else:
 435                     # movement data record 2.x (x <> 1,2,3)
 436                     err_string = _('\nMovement data records of type 2.%s are not supported !') % line[1]
 437                     err_code = 'R2007'
 438                     if batch:
 439                         return (err_code, err_string)
 440                     raise osv.except_osv(_('Data Error!'), err_string)
 441
 442             elif line[0] == '3':
 443                 # information data record 3
 444                 if line[1] == '1':
 445                     # information data record 3.1
 446                     info_line = {}
 447                     info_line['entry_date'] = st_line['entry_date']
 448                     info_line['type'] = 'information'
 449                     st_line_seq = st_line_seq + 1
 450                     info_line['sequence'] = st_line_seq
 451                     info_line['struct_comm_type'] = ''
 452                     info_line['struct_comm_type_desc'] = ''
 453                     info_line['communication'] = ''
 454                     info_line['ref'] = line[2:10]
 455                     info_line['trans_ref'] = line[10:31]
 456                     # positions 32-38 : transaction code
 457                     info_line['trans_type'] = line[31]
 458                     trans_type =  filter(lambda x: info_line['trans_type'] == x['type'], trans_type_table)
 459                     if not trans_type:
 460                         err_string = _('\nThe File contains an invalid CODA Transaction Type : %s!') % st_line['trans_type']
 461                         err_code = 'R3001'
 462                         if batch:
 463                             return (err_code, err_string)
 464                         raise osv.except_osv(_('Data Error!'), err_string)
 465                     info_line['trans_type_desc'] = trans_type[0]['description']
 466                     info_line['trans_family'] = line[32:34]
 467                     trans_family =  filter(lambda x: (x['type'] == 'family') and (info_line['trans_family'] == x['code']), trans_code_table)
 468                     if not trans_family:
 469                         err_string = _('\nThe File contains an invalid CODA Transaction Family : %s!') % st_line['trans_family']
 470                         err_code = 'R3002'
 471                         if batch:
 472                             return (err_code, err_string)
 473                         raise osv.except_osv(_('Data Error!'), err_string)
 474                     info_line['trans_family_desc'] = trans_family[0]['description']
 475                     info_line['trans_code'] = line[34:36]
 476                     trans_code =  filter(lambda x: (x['type'] == 'code') and (info_line['trans_code'] == x['code']) and (trans_family[0]['id'] == x['parent_id']),
 477                         trans_code_table)
 478                     if trans_code:
 479                         info_line['trans_code_desc'] = trans_code[0]['description']
 480                     else:
 481                         info_line['trans_code_desc'] = _('Transaction Code unknown, please consult your bank.')
 482                     info_line['trans_category'] = line[36:39]
 483                     trans_category =  filter(lambda x: info_line['trans_category'] == x['category'], trans_category_table)
 484                     if trans_category:
 485                         info_line['trans_category_desc'] = trans_category[0]['description']
 486                     else:
 487                         info_line['trans_category_desc'] = _('Transaction Category unknown, please consult your bank.')
 488                     # positions 40-113 : communication
 489                     if line[39] == '1':
 490                         info_line['struct_comm_type'] = line[40:43]
 491                         comm_type = filter(lambda x: info_line['struct_comm_type'] == x['code'], comm_type_table)
 492                         if not comm_type:
 493                             err_string = _('\nThe File contains an invalid Structured Communication Type : %s!') % info_line['struct_comm_type']
 494                             err_code = 'R3003'
 495                             if batch:
 496                                 return (err_code, err_string)
 497                             raise osv.except_osv(_('Data Error!'), err_string)
 498                         info_line['struct_comm_type_desc'] = comm_type[0]['description']
 499                         info_line['communication'] = info_line['name'] = line[43:113]
 500                     else:
 501                         info_line['communication'] = info_line['name'] = line[40:113]
 502                     # positions 114-128 not processed
 503                     coda_statement_lines[st_line_seq] = info_line
 504                     coda_statement['coda_statement_lines'] = coda_statement_lines
 505                 elif line[1] == '2':
 506                     # information data record 3.2
 507                     if coda_statement['coda_statement_lines'][st_line_seq]['ref'] != line[2:10]:
 508                         err_string = _('\nCODA parsing error on information data record 3.2, seq nr %s!'    \
 509                             '\nPlease report this issue via your OpenERP support channel.') % line[2:10]
 510                         err_code = 'R3004'
 511                         if batch:
 512                             return (err_code, err_string)
 513                         raise osv.except_osv(_('Error!'), err_string)
 514                     coda_statement['coda_statement_lines'][st_line_seq]['name'] += line[10:115]
 515                     coda_statement['coda_statement_lines'][st_line_seq]['communication'] += line[10:115]
 516                 elif line[1] == '3':
 517                     # information data record 3.3
 518                     if coda_statement['coda_statement_lines'][st_line_seq]['ref'] != line[2:10]:
 519                         err_string = _('\nCODA parsing error on information data record 3.3, seq nr %s!'    \
 520                             '\nPlease report this issue via your OpenERP support channel.') % line[2:10]
 521                         err_code = 'R3005'
 522                         if batch:
 523                             return (err_code, err_string)
 524                         raise osv.except_osv(_('Error!'), err_string)
 525                     coda_statement['coda_statement_lines'][st_line_seq]['name'] += line[10:100]
 526                     coda_statement['coda_statement_lines'][st_line_seq]['communication'] += line[10:100]
 527
 528             elif line[0] == '4':
 529                 # free communication data record 4
 530                 comm_line = {}
 531                 comm_line['type'] = 'communication'
 532                 st_line_seq = st_line_seq + 1
 533                 comm_line['sequence'] = st_line_seq
 534                 comm_line['ref'] = line[2:10]
 535                 comm_line['communication'] = comm_line['name'] = line[32:112]
 536                 coda_statement_lines[st_line_seq] = comm_line
 537                 coda_statement['coda_statement_lines'] = coda_statement_lines
 538
 539             elif line[0] == '8':
 540                 # new balance record
 541                 bal_end = list2float(line[42:57])
 542                 if line[41] == '1':    # 1=Debit
 543                     bal_end = - bal_end
 544                 coda_statement['balance_end_real'] = bal_end
 545
 546             elif line[0] == '9':
 547                 # footer record
 548                 coda_statement['balance_min'] = list2float(line[22:37])
 549                 coda_statement['balance_plus'] = list2float(line[37:52])
 550                 if not bal_end:
 551                     coda_statement['balance_end_real'] = coda_statement['balance_start'] + coda_statement['balance_plus'] - coda_statement['balance_min']
 552                 if coda_parsing_note:
 553                     coda_statement['coda_parsing_note'] = '\nStatement Line matching results:' + coda_parsing_note
 554                 else:
 555                     coda_statement['coda_parsing_note'] = ''
 556                 coda_statements.append(coda_statement)
 557         #end for
 558
 559         err_string = ''
 560         err_code = ''
 561         coda_id = 0
 562         coda_note = ''
 563         line_note = ''
 564
 565         try:
 566             coda_id = coda_obj.create(cr, uid,{
 567                 'name' : codafilename,
 568                 'coda_data': codafile,
 569                 'coda_creation_date' : coda_statement['date'],
 570                 'date': fields.date.context_today(self, cr, uid, context=context),
 571                 'user_id': uid,
 572                 })
 573             context.update({'coda_id': coda_id})
 574
 575         except osv.except_osv, e:
 576             cr.rollback()
 577             err_string = _('\nApplication Error : ') + str(e)
 578         except Exception, e:
 579             cr.rollback()
 580             err_string = _('\nSystem Error : ') + str(e)
 581         except :
 582             cr.rollback()
 583             err_string = _('\nUnknown Error : ') + str(e)
 584         if err_string:
 585             err_code = 'G0001'
 586             if batch:
 587                 return (err_code, err_string)
 588             raise osv.except_osv(_('CODA Import failed !'), err_string)
 589
 590         nb_err = 0
 591         err_string = ''
 592         coda_st_ids = []
 593         bk_st_ids = []
 594
 595         for statement in coda_statements:
 596
 597             # The CODA Statement info is written to two objects: 'coda.bank.statement' and 'account.bank.statement'
 598
 599             try:
 600
 601                 coda_st_id = coda_st_obj.create(cr, uid, {
 602                     'name': statement['name'],
 603                     'type': statement['type'],
 604                     'coda_bank_account_id': statement['coda_bank_account_id'],
 605                     'currency': statement['currency_id'],
 606                     'journal_id': statement['journal_id'],
 607                     'coda_id': coda_id,
 608                     'date': statement['date'],
 609                     'period_id': statement['period_id'],
 610                     'balance_start': statement['balance_start'],
 611                     'balance_end_real': statement['balance_end_real'],
 612                     'state':'draft',
 613                 })
 614                 coda_st_ids.append(coda_st_id)
 615
 616                 if statement['type'] == 'normal':
 617                     context.update({'ebanking_import': 1})
 618                     journal = journal_obj.browse(cr, uid, statement['journal_id'], context=context)
 619                     cr.execute('SELECT balance_end_real \
 620                         FROM account_bank_statement \
 621                         WHERE journal_id = %s and date < %s \
 622                         ORDER BY date DESC,id DESC LIMIT 1', (statement['journal_id'], statement['date']))
 623                     res = cr.fetchone()
 624                     balance_start_check = res and res[0]
 625                     if balance_start_check == None:
 626                         if journal.default_debit_account_id and (journal.default_credit_account_id == journal.default_debit_account_id):
 627                             balance_start_check = journal.default_debit_account_id.balance
 628                         else:
 629                             nb_err += 1
 630                             err_string += _('\nConfiguration Error in journal %s!'    \
 631                                 '\nPlease verify the Default Debit and Credit Account settings.') % journal.name
 632                             break
 633                     if balance_start_check <> statement['balance_start']:
 634                             nb_err += 1
 635                             err_string += _('\nThe CODA Statement %s Starting Balance (%.2f) does not correspond with the previous Closing Balance (%.2f) in journal %s!')  \
 636                                 % (statement['name'], statement['balance_start'], balance_start_check, journal.name)
 637                             break
 638
 639                     bk_st_id = bank_st_obj.create(cr, uid, {
 640                         'name': statement['name'],
 641                         'journal_id': statement['journal_id'],
 642                         'coda_statement_id': coda_st_id,
 643                         'date': statement['date'],
 644                         'period_id': statement['period_id'],
 645                         'balance_start': statement['balance_start'],
 646                         'balance_end_real': statement['balance_end_real'],
 647                         'state': 'draft',
 648                     })
 649                     bk_st_ids.append(bk_st_id)
 650                     coda_st_obj.write(cr, uid, [coda_st_id], {'statement_id': bk_st_id}, context=context)
 651
 652                 glob_id_stack = [(0, '', 0, '')]          # stack with tuples (glob_lvl_flag, glob_code, glob_id, glob_name)
 653                 lines = statement['coda_statement_lines']
 654                 st_line_seq = 0
 655
 656                 for x in lines:
 657                     line = lines[x]
 658
 659                     # handling non-transactional records : line['type'] in ['information', 'communication']
 660
 661                     if line['type'] == 'information':
 662
 663                         line['globalisation_id'] = glob_id_stack[-1][2]
 664                         line_note = _('Transaction Type' ': %s - %s'                \
 665                             '\nTransaction Family: %s - %s'                         \
 666                             '\nTransaction Code: %s - %s'                           \
 667                             '\nTransaction Category: %s - %s'                       \
 668                             '\nStructured Communication Type: %s - %s'              \
 669                             '\nCommunication: %s')                                  \
 670                             %(line['trans_type'], line['trans_type_desc'],
 671                               line['trans_family'], line['trans_family_desc'],
 672                               line['trans_code'], line['trans_code_desc'],
 673                               line['trans_category'], line['trans_category_desc'],
 674                               line['struct_comm_type'], line['struct_comm_type_desc'],
 675                               line['communication'])
 676
 677                         coda_st_line_id = coda_st_line_obj.create(cr, uid, {
 678                                    'sequence': line['sequence'],
 679                                    'ref': line['ref'],
 680                                    'name': line['name'].strip() or '/',
 681                                    'type' : 'information',
 682                                    'date': line['entry_date'],
 683                                    'statement_id': coda_st_id,
 684                                    'note': line_note,
 685                                    })
 686
 687                     elif line['type'] == 'communication':
 688
 689                         line_note = _('Free Communication:\n %s')                  \
 690                             %(line['communication'])
 691
 692                         coda_st_line_id = coda_st_line_obj.create(cr, uid, {
 693                                    'sequence': line['sequence'],
 694                                    'ref': line['ref'],
 695                                    'name': line['name'].strip() or '/',
 696                                    'type' : 'communication',
 697                                    'date': statement['date'],
 698                                    'statement_id': coda_st_id,
 699                                    'note': line_note,
 700                                    })
 701
 702                     # handling transactional records, # line['type'] in ['globalisation', 'general', 'supplier', 'customer']
 703
 704                     else:
 705
 706                         glob_lvl_flag = line['glob_lvl_flag']
 707                         if glob_lvl_flag:
 708                             if glob_id_stack[-1][0] == glob_lvl_flag:
 709                                 line['globalisation_id'] = glob_id_stack[-1][2]
 710                                 glob_id_stack.pop()
 711                             else:
 712                                 glob_name = line['name'].strip() or '/'
 713                                 glob_code = seq_obj.get(cr, uid, 'statement.line.global')
 714                                 glob_id = glob_obj.create(cr, uid, {
 715                                     'code': glob_code,
 716                                     'name': glob_name,
 717                                     'type': 'coda',
 718                                     'parent_id': glob_id_stack[-1][2],
 719                                     'amount': line['globalisation_amount'],
 720                                 })
 721                                 line['globalisation_id'] = glob_id
 722                                 glob_id_stack.append((glob_lvl_flag, glob_code, glob_id, glob_name))
 723
 724                         line_note = _('Partner name: %s \nPartner Account Number: %s' \
 725                             '\nTransaction Type: %s - %s'                             \
 726                             '\nTransaction Family: %s - %s'                           \
 727                             '\nTransaction Code: %s - %s'                             \
 728                             '\nTransaction Category: %s - %s'                         \
 729                             '\nStructured Communication Type: %s - %s'                \
 730                             '\nCommunication: %s')                                    \
 731                             %(line['counterparty_name'], line['counterparty_number'],
 732                               line['trans_type'], line['trans_type_desc'],
 733                               line['trans_family'], line['trans_family_desc'],
 734                               line['trans_code'], line['trans_code_desc'],
 735                               line['trans_category'], line['trans_category_desc'],
 736                               line['struct_comm_type'], line['struct_comm_type_desc'],
 737                               line['communication'])
 738
 739                         if line['type'] == 'globalisation':
 740
 741                             coda_st_line_id = coda_st_line_obj.create(cr, uid, {
 742                                    'sequence': line['sequence'],
 743                                    'ref': line['ref'],
 744                                    'name': line['name'].strip() or '/',
 745                                    'type' : 'globalisation',
 746                                    'val_date' : line['val_date'],
 747                                    'date': line['entry_date'],
 748                                    'globalisation_level': line['glob_lvl_flag'],
 749                                    'globalisation_amount': line['globalisation_amount'],
 750                                    'globalisation_id': line['globalisation_id'],
 751                                    'partner_id': line['partner_id'] or 0,
 752                                    'account_id': line['account_id'],
 753                                    'statement_id': coda_st_id,
 754                                    'note': line_note,
 755                                    })
 756
 757                         else:       # line['type'] in ['general', 'supplier', 'customer']
 758
 759                             if glob_lvl_flag == 0:
 760                                 line['globalisation_id'] = glob_id_stack[-1][2]
 761                             if not line['account_id']:
 762                                     line['account_id'] = awaiting_acc
 763
 764                             coda_st_line_id = coda_st_line_obj.create(cr, uid, {
 765                                    'sequence': line['sequence'],
 766                                    'ref': line['ref'],
 767                                    'name': line['name'] or '/',
 768                                    'type' : line['type'],
 769                                    'val_date' : line['val_date'],
 770                                    'date': line['entry_date'],
 771                                    'amount': line['amount'],
 772                                    'partner_id': line['partner_id'] or 0,
 773                                    'counterparty_name': line['counterparty_name'],
 774                                    'counterparty_bic': line['counterparty_bic'],
 775                                    'counterparty_number': line['counterparty_number'],
 776                                    'counterparty_currency': line['counterparty_currency'],
 777                                    'account_id': line['account_id'],
 778                                    'globalisation_level': line['glob_lvl_flag'],
 779                                    'globalisation_id': line['globalisation_id'],
 780                                    'statement_id': coda_st_id,
 781                                    'note': line_note,
 782                                    })
 783
 784                             if statement['type'] == 'normal':
 785
 786                                 st_line_seq += 1
 787                                 voucher_id = False
 788                                 line_name = line['name'].strip()
 789                                 if not line_name:
 790                                     if line['globalisation_id']:
 791                                         line_name = glob_id_stack[-1][3]
 792                                     else:
 793                                         line_name = '/'
 794
 795                                 if line['reconcile']:
 796                                     voucher_vals = {
 797                                         'type': line['type'] == 'supplier' and 'payment' or 'receipt',
 798                                         'name': line_name,
 799                                         'partner_id': line['partner_id'],
 800                                         'journal_id': statement['journal_id'],
 801                                         'account_id': journal.default_credit_account_id.id,
 802                                         'company_id': journal.company_id.id,
 803                                         'currency_id': journal.company_id.currency_id.id,
 804                                         'date': line['entry_date'],
 805                                         'amount': abs(line['amount']),
 806                                         'period_id': statement['period_id'],
 807                                     }
 808                                     voucher_id = voucher_obj.create(cr, uid, voucher_vals, context=context)
 809
 810                                     move_line = move_line_obj.browse(cr, uid, line['reconcile'], context=context)
 811                                     voucher_dict = voucher_obj.onchange_partner_id(cr, uid, [],
 812                                         partner_id = line['partner_id'],
 813                                         journal_id = statement['journal_id'],
 814                                         price = abs(line['amount']),
 815                                         currency_id = journal.company_id.currency_id.id,
 816                                         ttype = line['type'] == 'supplier' and 'payment' or 'receipt',
 817                                         date = line['val_date'],
 818                                         context = context)
 819                                     #logger.notifyChannel('addons.'+self._name, netsvc.LOG_WARNING, 'voucher_dict = %s' % voucher_dict)
 820                                     voucher_line_vals = False
 821                                     if voucher_dict['value']['line_ids']:
 822                                         for line_dict in voucher_dict['value']['line_ids']:
 823                                             if line_dict['move_line_id'] == move_line.id:
 824                                                 voucher_line_vals = line_dict
 825                                     if voucher_line_vals:
 826                                         voucher_line_vals.update({
 827                                             'voucher_id': voucher_id,
 828                                             'amount': abs(line['amount']),
 829                                         })
 830                                         voucher_line_obj.create(cr, uid, voucher_line_vals, context=context)
 831
 832                                 bank_st_line_id = bank_st_line_obj.create(cr, uid, {
 833                                        'sequence': st_line_seq,
 834                                        'ref': line['ref'],
 835                                        'name': line_name,
 836                                        'type' : line['type'],
 837                                        'val_date' : line['val_date'],
 838                                        'date': line['entry_date'],
 839                                        'amount': line['amount'],
 840                                        'partner_id': line['partner_id'] or 0,
 841                                        'counterparty_name': line['counterparty_name'],
 842                                        'counterparty_bic': line['counterparty_bic'],
 843                                        'counterparty_number': line['counterparty_number'],
 844                                        'counterparty_currency': line['counterparty_currency'],
 845                                        'account_id': line['account_id'],
 846                                        'globalisation_id': line['globalisation_id'],
 847                                        'statement_id': bk_st_id,
 848                                        'voucher_id': voucher_id,
 849                                        'note': line_note,
 850                                         })
 851                 # end 'for x in lines'
 852
 853                 coda_st_obj.write(cr, uid, [coda_st_id], {}, context=context)           # calculate balance
 854                 st_balance = coda_st_obj.read(cr, uid, coda_st_id, ['balance_end', 'balance_end_real'], context=context)
 855                 if st_balance['balance_end'] <> st_balance['balance_end_real']:
 856                     err_string += _('\nIncorrect ending Balance in CODA Statement %s for Bank Account %s!')  \
 857                         % (statement['coda_seq_number'], (statement['acc_number'] + ' (' + statement['currency'] + ') - ' + statement['description']))
 858                     if statement['type'] == 'normal':
 859                         nb_err += 1
 860                         break
 861                     else:
 862                         statement['coda_parsing_note'] += '\n' + err_string
 863
 864                 if statement['type'] == 'normal':
 865                     bank_st_obj.button_dummy(cr, uid, [bk_st_id], context=context)      # calculate balance
 866                     journal_name = journal.name
 867                 else:
 868                     journal_name = _('None')
 869                 coda_note = coda_note +                                                 \
 870                     _('\n\nBank Journal: %s'                                            \
 871                     '\nCODA Version: %s'                                                \
 872                     '\nCODA Sequence Number: %s'                                        \
 873                     '\nPaper Statement Sequence Number: %s'                             \
 874                     '\nBank Account: %s'                                                \
 875                     '\nAccount Holder Name: %s'                                         \
 876                     '\nDate: %s, Starting Balance:  %.2f, Ending Balance: %.2f'         \
 877                     '%s')                                                               \
 878                     %(journal_name,
 879                       coda_version,
 880                       statement['coda_seq_number'],
 881                       statement['paper_seq_number'],
 882                       (statement['acc_number'] + ' (' + statement['currency'] + ') - ' + statement['description']),
 883                       statement['acc_holder'],
 884                       statement['date'], float(statement['balance_start']), float(statement['balance_end_real']),
 885                       statement['coda_parsing_note'])
 886
 887             except osv.except_osv, e:
 888                 cr.rollback()
 889                 nb_err += 1
 890                 err_string += _('\nError ! ') + str(e)
 891                 tb = ''.join(format_exception(*exc_info()))
 892                 logger.notifyChannel('addons.'+self._name, netsvc.LOG_ERROR,
 893                     'Application Error while processing Statement %s\n%s' % (statement.get('name', '/'),tb))
 894             except Exception, e:
 895                 cr.rollback()
 896                 nb_err += 1
 897                 err_string += _('\nSystem Error : ') + str(e)
 898                 tb = ''.join(format_exception(*exc_info()))
 899                 logger.notifyChannel('addons.'+self._name, netsvc.LOG_ERROR,
 900                     'System Error while processing Statement %s\n%s' % (statement.get('name', '/'),tb))
 901             except :
 902                 cr.rollback()
 903                 nb_err += 1
 904                 err_string = _('\nUnknown Error : ') + str(e)
 905                 tb = ''.join(format_exception(*exc_info()))
 906                 logger.notifyChannel('addons.'+self._name, netsvc.LOG_ERROR,
 907                     'Unknown Error while processing Statement %s\n%s' % (statement.get('name', '/'),tb))
 908
 909         # end 'for statement in coda_statements'
 910
 911         coda_note_header = _('CODA File is Imported  :')
 912         coda_note_footer = _('\n\nNumber of statements : ') + str(len(coda_st_ids))
 913         err_log = err_log + _('\nNumber of errors : ') + str(nb_err) + '\n'
 914
 915         if not nb_err:
 916             note = coda_note_header + coda_note + coda_note_footer
 917             coda_obj.write(cr, uid,[coda_id],{'note': note })
 918             cr.commit()
 919             if batch:
 920                 return None
 921         else:
 922             cr.rollback()
 923             if batch:
 924                 err_code = 'G0002'
 925                 return (err_code, err_string)
 926             raise osv.except_osv(_('CODA Import failed !'), err_string)
 927
 928         context.update({ 'bk_st_ids': bk_st_ids})
 929         model_data_ids = mod_obj.search(cr, uid, [('model', '=', 'ir.ui.view'), ('name', '=', 'account_coda_import_result_view')], context=context)
 930         resource_id = mod_obj.read(cr, uid, model_data_ids, fields=['res_id'], context=context)[0]['res_id']
 931         self.write(cr, uid, ids, {'note': note}, context=context)
 932
 933         return {
 934             'name': _('Import CODA File result'),
 935             'res_id': ids[0],
 936             'view_type': 'form',
 937             'view_mode': 'form',
 938             'res_model': 'account.coda.import',
 939             'view_id': False,
 940             'target': 'new',
 941             'views': [(resource_id, 'form')],
 942             'context': context,
 943             'type': 'ir.actions.act_window',
 944         }
 945
 946     def action_open_coda_statements(self, cr, uid, ids, context=None):
 947         if context is None:
 948             context = {}
 949         module, xml_id = 'account_coda', 'action_coda_bank_statements'
 950         res_model, res_id = self.pool.get('ir.model.data').get_object_reference(cr, uid, module, xml_id)
 951         action = self.pool.get('ir.actions.act_window').read(cr, uid, res_id, context=context)
 952         domain = eval(action.get('domain') or '[]')
 953         domain += [('coda_id', '=', context.get('coda_id', False))]
 954         action.update({'domain': domain})
 955         return action
 956
 957     def action_open_bank_statements(self, cr, uid, ids, context=None):
 958         if context is None:
 959             context = {}
 960         module, xml_id = 'account', 'action_bank_statement_tree'
 961         res_model, res_id = self.pool.get('ir.model.data').get_object_reference(cr, uid, module, xml_id)
 962         action = self.pool.get('ir.actions.act_window').read(cr, uid, res_id, context=context)
 963         domain = eval(action.get('domain') or '[]')
 964         domain += [('id','in', context.get('bk_st_ids', False))]
 965         action.update({'domain': domain})
 966         return action
 967
 968 account_coda_import()
 969
 970 def str2date(date_str):
 971     return time.strftime('%Y-%m-%d', time.strptime(date_str,'%d%m%y'))
 972
 973 def str2float(str):
 974     try:
 975         return float(str)
 976     except:
 977         return 0.0
 978
 979 def list2float(lst):
 980             try:
 981                 return str2float((lambda s : s[:-3] + '.' + s[-3:])(lst))
 982             except:
 983                 return 0.0
 984
 985 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: