addons/account_coda/wizard/account_coda_import.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
   6 #    $Id$
   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 published by
  10 #    the Free Software Foundation, either version 3 of the License, or
  11 #    (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
  26 from osv import fields, osv
  27 from tools.translate import _
  28
  29 def str2date(date_str):
  30     return time.strftime("%y/%m/%d", time.strptime(date_str, "%d%m%y"))
  31
  32 def str2float(str):
  33     try:
  34         return float(str)
  35     except:
  36         return 0.0
  37
  38 def list2float(lst):
  39     try:
  40         return str2float((lambda s: s[:-3] + '.' + s[-3:])(lst))
  41     except:
  42         return 0.0
  43
  44 class account_coda_import(osv.osv_memory):
  45     _name = 'account.coda.import'
  46     _description = 'Account Coda Import'
  47     _columns = {
  48             'journal_id': fields.many2one('account.journal', 'Bank Journal', required=True),
  49             'def_payable': fields.many2one('account.account', 'Default Payable Account', domain=[('type', '=', 'payable')], required=True, help= 'Set here the payable account that will be used, by default, if the partner is not found'),
  50             'def_receivable': fields.many2one('account.account', 'Default Receivable Account', domain=[('type', '=', 'receivable')], required=True, help= 'Set here the receivable account that will be used, by default, if the partner is not found',),
  51             'awaiting_account': fields.many2one('account.account', 'Default Account for Unrecognized Movement', domain=[('type', '=', 'liquidity')], required=True, help= 'Set here the default account that will be used, if the partner is found but does not have the bank account, or if he is domiciled'),
  52             'coda': fields.binary('Coda File', required=True),
  53             'note':fields.text('Log'),
  54     }
  55
  56     def coda_parsing(self, cr, uid, ids, context=None):
  57
  58         journal_obj=self.pool.get('account.journal')
  59         account_period_obj = self.pool.get('account.period')
  60         partner_bank_obj = self.pool.get('res.partner.bank')
  61         bank_statement_obj = self.pool.get('account.bank.statement')
  62         bank_statement_line_obj = self.pool.get('account.bank.statement.line')
  63         voucher_obj = self.pool.get('account.voucher')
  64         voucher_line_obj = self.pool.get('account.voucher.line')
  65         account_coda_obj = self.pool.get('account.coda')
  66         mod_obj = self.pool.get('ir.model.data')
  67         line_obj = self.pool.get('account.move.line')
  68
  69         if context is None:
  70             context = {}
  71
  72         data = self.read(cr, uid, ids)[0]
  73
  74         codafile = data['coda']
  75         journal_code = journal_obj.browse(cr, uid, data['journal_id'], context=context).code
  76
  77         period = account_period_obj.find(cr, uid, context=context)[0]
  78         def_pay_acc = data['def_payable']
  79         def_rec_acc = data['def_receivable']
  80
  81         err_log = "Errors:\n------\n"
  82         nb_err=0
  83         std_log=''
  84         str_log1 = "Coda File is Imported:  "
  85         str_not=''
  86         str_not1=''
  87
  88         bank_statements = []
  89         bank_statement = {}
  90         recordlist = base64.decodestring(codafile).split('\n')
  91         recordlist.pop()
  92         for line in recordlist:
  93             if line[0] == '0':
  94                 # header data
  95
  96                 bank_statement["bank_statement_line"]={}
  97                 bank_statement_lines = {}
  98                 bank_statement['date'] = str2date(line[5:11])
  99                 bank_statement['journal_id']=data['journal_id']
 100                 period_id = account_period_obj.search(cr, uid, [('date_start', '<=', time.strftime('%Y-%m-%d', time.strptime(bank_statement['date'], "%y/%m/%d"))), ('date_stop', '>=', time.strftime('%Y-%m-%d', time.strptime(bank_statement['date'], "%y/%m/%d")))])
 101                 bank_statement['period_id'] = period_id and period_id[0] or False
 102                 bank_statement['state']='draft'
 103             elif line[0] == '1':
 104                 # old balance data
 105                 bal_start = list2float(line[43:58])
 106                 if line[42] == '1':
 107                     bal_start = - bal_start
 108                 bank_statement["balance_start"]= bal_start
 109                 bank_statement["acc_number"]=line[5:17]
 110                 bank_statement["acc_holder"]=line[64:90]
 111                 bank_statement['name'] = journal_code + ' ' + str(line[2:5])
 112
 113             elif line[0]=='2':
 114                 # movement data record 2
 115                 if line[1]=='1':
 116                     # movement data record 2.1
 117                     if bank_statement_lines.has_key(line[2:6]):
 118                         continue
 119                     st_line = {}
 120                     st_line['extra_note'] = ''
 121                     st_line['statement_id']=0
 122                     st_line['ref'] = line[2:10]
 123                     st_line['date'] = time.strftime('%Y-%m-%d', time.strptime(str2date(line[115:121]), "%y/%m/%d")),
 124                     st_line_amt = list2float(line[32:47])
 125
 126                     if line[61]=='1':
 127                         st_line['toreconcile'] = True
 128                         st_line['name']=line[65:77]
 129                     else:
 130                         st_line['toreconcile'] = False
 131                         st_line['name']=line[62:115]
 132
 133                     st_line['free_comm'] = st_line['name']
 134                     st_line['val_date']=time.strftime('%Y-%m-%d', time.strptime(str2date(line[47:53]), "%y/%m/%d")),
 135                     st_line['entry_date']=time.strftime('%Y-%m-%d', time.strptime(str2date(line[115:121]), "%y/%m/%d")),
 136                     st_line['partner_id']=0
 137                     if line[31] == '1':
 138                         st_line_amt = - st_line_amt
 139                         st_line['account_id'] = def_pay_acc
 140                     else:
 141                         st_line['account_id'] = def_rec_acc
 142                     st_line['amount'] = st_line_amt
 143                     bank_statement_lines[line[2:6]]=st_line
 144                     bank_statement["bank_statement_line"]=bank_statement_lines
 145                 elif line[1] == '2':
 146                     st_line_name = line[2:6]
 147                     bank_statement_lines[st_line_name].update({'account_id': data['awaiting_account']})
 148
 149                 elif line[1] == '3':
 150                     # movement data record 3.1
 151                     st_line_name = line[2:6]
 152                     st_line_partner_acc = str(line[10:47]).strip()
 153                     cntry_number=line[10:47].strip()
 154                     contry_name=line[47:125].strip()
 155                     bank_ids = partner_bank_obj.search(cr, uid, [('acc_number', '=', st_line_partner_acc)])
 156                     bank_statement_lines[st_line_name].update({'cntry_number': cntry_number, 'contry_name': contry_name})
 157                     if bank_ids:
 158                         bank = partner_bank_obj.browse(cr, uid, bank_ids[0], context=context)
 159                         if line and bank.partner_id:
 160                             bank_statement_lines[st_line_name].update({'partner_id': bank.partner_id.id})
 161                             if bank_statement_lines[st_line_name]['amount'] < 0:
 162                                 bank_statement_lines[st_line_name].update({'account_id': bank.partner_id.property_account_payable.id})
 163                             else:
 164                                 bank_statement_lines[st_line_name].update({'account_id': bank.partner_id.property_account_receivable.id})
 165                     else:
 166                         nb_err += 1
 167                         err_log += _('The bank account %s is not defined for the partner %s.\n')%(cntry_number, contry_name)
 168                         bank_statement_lines[st_line_name].update({'account_id': data['awaiting_account']})
 169
 170                     bank_statement["bank_statement_line"]=bank_statement_lines
 171             elif line[0]=='3':
 172                 if line[1] == '1':
 173                     st_line_name = line[2:6]
 174                     bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[40:113]
 175                 elif line[1] == '2':
 176                     st_line_name = line[2:6]
 177                     bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[10:115]
 178                 elif line[1] == '3':
 179                     st_line_name = line[2:6]
 180                     bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[10:100]
 181             elif line[0]=='8':
 182                 # new balance record
 183                 bal_end = list2float(line[42:57])
 184                 if line[41] == '1':
 185                     bal_end = - bal_end
 186                 bank_statement["balance_end_real"]= bal_end
 187
 188             elif line[0]=='9':
 189                 # footer record
 190
 191                 bank_statements.append(bank_statement)
 192         #end for
 193         bkst_list=[]
 194         for statement in bank_statements:
 195             try:
 196                 bk_st_id =bank_statement_obj.create(cr, uid, {
 197                     'journal_id': statement.get('journal_id',False),
 198                     'date': time.strftime('%Y-%m-%d', time.strptime(statement.get('date',time.strftime('%Y-%m-%d')), "%y/%m/%d")),
 199                     'period_id': statement.get('period_id',False) or period,
 200                     'balance_start': statement.get('balance_start',False),
 201                     'balance_end_real': statement.get('balance_end_real',False),
 202                     'state': 'draft',
 203                     'name': statement.get('name',False),
 204                 })
 205                 lines = statement.get('bank_statement_line',False)
 206                 if lines:
 207                     for value in lines:
 208                         journal = journal_obj.browse(cr, uid, statement['journal_id'], context=context)
 209                         line = lines[value]
 210                         if not line['partner_id']:
 211                             line['partner_id'] = journal.company_id.partner_id.id
 212                         voucher_id = False
 213                         rec_id = False
 214                         if line.get('toreconcile',False): # Fix me
 215                             name = line['name'][:3] + '/' + line['name'][3:7] + '/' + line['name'][7:]
 216                             rec_id = self.pool.get('account.move.line').search(cr, uid, [('name', '=', name), ('reconcile_id', '=', False), ('account_id.reconcile', '=', True)])
 217                             if rec_id:
 218                                 result = voucher_obj.onchange_partner_id(cr, uid, [], partner_id=line['partner_id'], journal_id=statement['journal_id'], price=abs(line['amount']), currency_id = journal.company_id.currency_id.id, ttype=(line['amount'] < 0 and 'payment' or 'receipt'), context=context)
 219                                 voucher_res = { 'type':(line['amount'] < 0 and 'payment' or 'receipt'),
 220                                 'name': line['name'],#line.name,
 221                                 'partner_id': line['partner_id'],#line.partner_id.id,
 222                                 'journal_id': statement['journal_id'], #statement.journal_id.id,
 223                                 'account_id': result.get('account_id', journal.default_credit_account_id.id),#line.account_id.id,
 224                                 'company_id': journal.company_id.id,#statement.company_id.id,
 225                                 'currency_id': journal.company_id.currency_id.id,#statement.currency.id,
 226                                 'date': line['date'], #line.date,
 227                                 'amount':abs(line['amount']),
 228                                 'period_id':statement.get('period_id',False) or period,# statement.period_id.id
 229                                 }
 230                                 voucher_id = voucher_obj.create(cr, uid, voucher_res, context=context)
 231                                 context.update({'move_line_ids': rec_id})
 232
 233                                 voucher_line_dict =  False
 234                                 if result['value']['line_ids']:
 235                                     for line_dict in result['value']['line_ids']:
 236                                         move_line = line_obj.browse(cr, uid, line_dict['move_line_id'], context)
 237                                         if line.move_id.id == move_line.move_id.id:
 238                                             voucher_line_dict = line_dict
 239
 240                                 if voucher_line_dict:
 241                                     voucher_line_dict.update({'voucher_id':voucher_id})
 242                                     voucher_line_obj.create(cr, uid, voucher_line_dict, context=context)
 243
 244         #                            reconcile_id = statement_reconcile_obj.create(cr, uid, {
 245         #                                'line_ids': [(6, 0, rec_id)]
 246         #                                }, context=context)
 247         #
 248
 249                                 mv = self.pool.get('account.move.line').browse(cr, uid, rec_id[0], context=context)
 250                                 if mv.partner_id:
 251                                     line['partner_id'] = mv.partner_id.id
 252                                     if line['amount'] < 0:
 253                                         line['account_id'] = mv.partner_id.property_account_payable.id
 254                                     else:
 255                                         line['account_id'] = mv.partner_id.property_account_receivable.id
 256                         str_not1 = ''
 257                         if line.has_key('contry_name') and line.has_key('cntry_number'):
 258                             str_not1="Partner name:%s \n Partner Account Number:%s \n Communication:%s \n Value Date:%s \n Entry Date:%s \n"%(line["contry_name"], line["cntry_number"], line["free_comm"]+line['extra_note'], line["val_date"][0], line["entry_date"][0])
 259                         bank_statement_line_obj.create(cr, uid, {
 260                                    'name':line['name'],
 261                                    'date': line['date'],
 262                                    'amount': line['amount'],
 263                                    'partner_id':line['partner_id'],
 264                                    'account_id':line['account_id'],
 265                                    'statement_id': bk_st_id,
 266                                    'voucher_id': voucher_id,
 267                                    'note':str_not1,
 268                                    'ref':line['ref'],
 269                                    })
 270
 271                 str_not = "\n \n Account Number: %s \n Account Holder Name: %s " %(statement["acc_number"], statement["acc_holder"])
 272                 std_log += "\nStatement: %s, Date: %s, Starting Balance:  %.2f, Ending Balance: %.2f \n"\
 273                           %(statement['name'], statement['date'], float(statement["balance_start"]), float(statement["balance_end_real"]))
 274                 bkst_list.append(bk_st_id)
 275
 276             except osv.except_osv, e:
 277                 cr.rollback()
 278                 nb_err += 1
 279                 err_log += '\n Application Error: ' + str(e)
 280                 raise # REMOVEME
 281
 282             except Exception, e:
 283                 cr.rollback()
 284                 nb_err += 1
 285                 err_log += '\n System Error: '+str(e)
 286                 raise # REMOVEME
 287             except:
 288                 cr.rollback()
 289                 nb_err+=1
 290                 err_log += '\n Unknown Error'
 291                 raise
 292         err_log += '\n\nNumber of statements: '+ str(len(bkst_list))
 293         err_log += '\nNumber of error:'+ str(nb_err) +'\n'
 294
 295         account_coda_obj.create(cr, uid, {
 296             'name': codafile,
 297             'statement_ids': [(6, 0, bkst_list,)],
 298             'note': str_log1+str_not+std_log+err_log,
 299             'journal_id': data['journal_id'],
 300             'date': time.strftime("%Y-%m-%d"),
 301             'user_id': uid,
 302         })
 303         test = ''
 304         test = str_log1 + std_log + err_log
 305         self.write(cr, uid, ids, {'note': test}, context=context)
 306         context.update({ 'statment_ids': bkst_list})
 307         model_data_ids = mod_obj.search(cr, uid, [('model', '=', 'ir.ui.view'), ('name', '=', 'account_coda_note_view')], context=context)
 308         resource_id = mod_obj.read(cr, uid, model_data_ids, fields=['res_id'], context=context)[0]['res_id']
 309
 310         return {
 311             'name': _('Result'),
 312             'res_id': ids[0],
 313             'view_type': 'form',
 314             'view_mode': 'form',
 315             'res_model': 'account.coda.import',
 316             'view_id': False,
 317             'target': 'new',
 318             'views': [(resource_id, 'form')],
 319             'context': context,
 320             'type': 'ir.actions.act_window',
 321         }
 322
 323     def action_open_window(self, cr, uid, data, context=None):
 324         if context is None:
 325             context = {}
 326
 327         return {
 328             'domain':"[('id','in',%s)]"%(context.get('statment_ids', False)),
 329             'name': 'Statement',
 330             'view_type': 'form',
 331             'view_mode': 'tree,form',
 332             'res_model': 'account.bank.statement',
 333             'view_id': False,
 334             'type': 'ir.actions.act_window',
 335     }
 336
 337 account_coda_import()
 338
 339 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: