addons/account_coda/wizard/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>).
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21
  22 import pooler
  23 import time
  24 import datetime
  25 import wizard
  26 import netsvc
  27 import base64
  28 from osv import osv
  29 from tools.translate import _
  30
  31 codawiz_form = """<?xml version="1.0"?>
  32 <form string="Import Coda Statement">
  33 <separator colspan="4" string="Select your bank journal :" />
  34     <field name="journal_id" colspan="1" domain="[('type','=','cash')]" />
  35     <newline />
  36     <field name="def_payable" />    <field name="def_receivable" />
  37     <newline />
  38     <field name="awaiting_account" />
  39     <separator string="Clic on 'New' to select your file :" colspan="4"/>
  40     <field name="coda"/>
  41 </form>
  42 """
  43
  44 codawiz_fields = {
  45     'journal_id' : {
  46         'string':'Bank Journal',
  47         'type':'many2one',
  48         'relation':'account.journal',
  49         'required':True,
  50
  51     },
  52     'coda' : {
  53         'string':'Coda File',
  54         'type':'binary',
  55         'required':True,
  56     },
  57     'def_payable' : {
  58         'string' : 'Default Payable Account',
  59         'type' : 'many2one',
  60         'relation': 'account.account',
  61         'required':True,
  62         'domain':[('type','=','payable')],
  63         'help': 'Set here the payable account that will be used, by default, if the partner is not found',
  64     },
  65     'def_receivable' : {
  66         'string' : 'Default receivable Account',
  67         'type' : 'many2one',
  68         'relation': 'account.account',
  69         'required':True,
  70         'domain':[('type','=','receivable')],
  71         'help': 'Set here the receivable account that will be used, by default, if the partner is not found',
  72     },
  73     'awaiting_account' : {
  74         'string' : 'Default Account for Unrecognized Movement',
  75         'type' : 'many2one',
  76         'relation': 'account.account',
  77         'required':True,
  78         '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',
  79
  80     }
  81
  82 }
  83
  84 result_form = """<?xml version="1.0"?>
  85 <form string="Import Coda Statement">
  86 <separator colspan="4" string="Results :" />
  87     <field name="note" colspan="4" nolabel="1" width="500"/>
  88 </form>
  89 """
  90
  91 result_fields = {
  92
  93     'note' : {'string':'Log','type':'text'}
  94
  95 }
  96
  97
  98 def _coda_parsing(self, cr, uid, data, context):
  99     pool = pooler.get_pool(cr.dbname)
 100     codafile = data['form']['coda']
 101     journal_code = pool.get('account.journal').browse(cr, uid, data['form']['journal_id'], context).code
 102     def_pay_acc = data['form']['def_payable']
 103     def_rec_acc = data['form']['def_receivable']
 104
 105     str_log = ""
 106     err_log = "Errors:\n------\n"
 107     nb_err=0
 108     std_log=''
 109     str_log1 = "Coda File is Imported  :  "
 110     str_not=''
 111     str_not1=''
 112
 113     bank_statements=[]
 114     recordlist = base64.decodestring(codafile).split('\n')
 115     recordlist.pop()
 116     for line in recordlist:
 117         if line[0] == '0':
 118             # header data
 119             bank_statement = {}
 120             bank_statement["bank_statement_line"]={}
 121             bank_statement_lines = {}
 122             bank_statement['date'] = str2date(line[5:11])
 123             bank_statement['journal_id']=data['form']['journal_id']
 124             period_id = pool.get('account.period').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")))])
 125             bank_statement['period_id'] = period_id[0]
 126             bank_statement['state']='draft'
 127         elif line[0] == '1':
 128             # old balance data
 129             bal_start = list2float(line[43:58])
 130             if line[42] == '1':
 131                 bal_start = - bal_start
 132             bank_statement["balance_start"]= bal_start
 133             bank_statement["acc_number"]=line[5:17]
 134             bank_statement["acc_holder"]=line[64:90]
 135             bank_statement['name'] = journal_code + ' ' + str(line[2:5])
 136
 137         elif line[0]=='2':
 138             # movement data record 2
 139             if line[1]=='1':
 140                 # movement data record 2.1
 141                 if bank_statement_lines.has_key(line[2:6]):
 142                     continue
 143                 st_line = {}
 144                 st_line['extra_note'] = ''
 145                 st_line['statement_id']=0
 146                 st_line['ref'] = line[2:10]
 147                 st_line['date'] = time.strftime('%Y-%m-%d',time.strptime(str2date(line[115:121]),"%y/%m/%d")),
 148                 st_line_amt = list2float(line[32:47])
 149
 150                 if line[61]=='1':
 151                     st_line['toreconcile'] = True
 152                     st_line['name']=line[65:77]
 153                 else:
 154                     st_line['toreconcile'] = False
 155                     st_line['name']=line[62:115]
 156
 157                 st_line['free_comm'] = st_line['name']
 158                 st_line['val_date']=time.strftime('%Y-%m-%d',time.strptime(str2date(line[47:53]),"%y/%m/%d")),
 159                 st_line['entry_date']=time.strftime('%Y-%m-%d',time.strptime(str2date(line[115:121]),"%y/%m/%d")),
 160                 st_line['partner_id']=0
 161                 if line[31] == '1':
 162                     st_line_amt = - st_line_amt
 163                     st_line['account_id'] = def_pay_acc
 164                 else:
 165                     st_line['account_id'] = def_rec_acc
 166                 st_line['amount'] = st_line_amt
 167                 bank_statement_lines[line[2:6]]=st_line
 168                 bank_statement["bank_statement_line"]=bank_statement_lines
 169             elif line[1] == '2':
 170                 st_line_name = line[2:6]
 171                 bank_statement_lines[st_line_name].update({'account_id': data['form']['awaiting_account']})
 172
 173             elif line[1] == '3':
 174                 # movement data record 3.1
 175                 st_line_name = line[2:6]
 176                 st_line_partner_acc = str(line[10:47]).strip()
 177                 cntry_number=line[10:47].strip()
 178                 contry_name=line[47:125].strip()
 179                 bank_ids = pool.get('res.partner.bank').search(cr,uid,[('acc_number','=',st_line_partner_acc)])
 180                 bank_statement_lines[st_line_name].update({'cntry_number': cntry_number, 'contry_name': contry_name})
 181                 if bank_ids:
 182                     bank = pool.get('res.partner.bank').browse(cr,uid,bank_ids[0],context)
 183                     if line and bank.partner_id:
 184                         bank_statement_lines[st_line_name].update({'partner_id': bank.partner_id.id})
 185                         if bank_statement_lines[st_line_name]['amount'] < 0 :
 186                             bank_statement_lines[st_line_name].update({'account_id': bank.partner_id.property_account_payable.id})
 187                         else :
 188                             bank_statement_lines[st_line_name].update({'account_id': bank.partner_id.property_account_receivable.id})
 189                 else:
 190                     nb_err += 1
 191                     err_log += _('The bank account %s is not defined for the partner %s.\n')%(cntry_number,contry_name)
 192                     bank_statement_lines[st_line_name].update({'account_id': data['form']['awaiting_account']})
 193
 194                 bank_statement["bank_statement_line"]=bank_statement_lines
 195         elif line[0]=='3':
 196             if line[1] == '1':
 197                 st_line_name = line[2:6]
 198                 bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[40:113]
 199             elif line[1] == '2':
 200                 st_line_name = line[2:6]
 201                 bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[10:115]
 202             elif line[1] == '3':
 203                 st_line_name = line[2:6]
 204                 bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[10:100]
 205         elif line[0]=='8':
 206             # new balance record
 207             bal_end = list2float(line[42:57])
 208             if line[41] == '1':
 209                 bal_end = - bal_end
 210             bank_statement["balance_end_real"]= bal_end
 211
 212         elif line[0]=='9':
 213             # footer record
 214             bank_statements.append(bank_statement)
 215     #end for
 216     bkst_list=[]
 217     for statement in bank_statements:
 218         try:
 219             bk_st_id = pool.get('account.bank.statement').create(cr,uid,{
 220                 'journal_id': statement['journal_id'],
 221                 'date':time.strftime('%Y-%m-%d',time.strptime(statement['date'],"%y/%m/%d")),
 222                 'period_id':statement['period_id'],
 223                 'balance_start': statement["balance_start"],
 224                 'balance_end_real': statement["balance_end_real"],
 225                 'state': 'draft',
 226                 'name': statement['name'],
 227             })
 228             lines=statement["bank_statement_line"]
 229             for value in lines:
 230                 line=lines[value]
 231                 reconcile_id = False
 232                 if line['toreconcile']:
 233                     rec_id = pool.get('account.move.line').search(cr, uid, [('name','=',line['name']),('reconcile_id','=',False),('account_id.reconcile','=',True)])
 234                     if rec_id:
 235                         reconcile_id = pool.get('account.bank.statement.reconcile').create(cr, uid, {
 236                             'line_ids': [(6, 0, rec_id)]
 237                             }, context=context)
 238                 str_not1 = ''
 239                 if line.has_key('contry_name') and line.has_key('cntry_number'):
 240                     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])
 241                 id=pool.get('account.bank.statement.line').create(cr,uid,{
 242                            'name':line['name'],
 243                            'date': line['date'],
 244                            'amount': line['amount'],
 245                            'partner_id':line['partner_id'] or 0,
 246                            'account_id':line['account_id'],
 247                            'statement_id': bk_st_id,
 248                            'reconcile_id': reconcile_id,
 249                            'note':str_not1,
 250                            'ref':line['ref'],
 251                            })
 252
 253             str_not= "\n \n Account Number: %s \n Account Holder Name: %s " %(statement["acc_number"],statement["acc_holder"])
 254             std_log += "\nStatement : %s , Date  : %s, Starting Balance :  %.2f , Ending Balance : %.2f \n"\
 255                       %(statement['name'], statement['date'], float(statement["balance_start"]), float(statement["balance_end_real"]))
 256             bkst_list.append(bk_st_id)
 257
 258         except osv.except_osv, e:
 259             cr.rollback()
 260             nb_err+=1
 261             err_log += '\n Application Error : ' + str(e)
 262             raise # REMOVEME
 263
 264         except Exception, e:
 265             cr.rollback()
 266             nb_err+=1
 267             err_log += '\n System Error : '+str(e)
 268             raise # REMOVEME
 269         except :
 270             cr.rollback()
 271             nb_err+=1
 272             err_log += '\n Unknown Error'
 273             raise
 274     err_log += '\n\nNumber of statements : '+ str(len(bkst_list))
 275     err_log += '\nNumber of error :'+ str(nb_err) +'\n'
 276
 277     pool.get('account.coda').create(cr, uid,{
 278         'name':codafile,
 279         'statement_ids': [(6, 0, bkst_list,)],
 280         'note':str_log1+str_not+std_log+err_log,
 281         'journal_id':data['form']['journal_id'],
 282         'date':time.strftime("%Y-%m-%d"),
 283         'user_id':uid,
 284         })
 285
 286     return {'note':str_log1 + std_log + err_log ,'journal_id': data['form']['journal_id'], 'coda': data['form']['coda'],'statment_ids':bkst_list}
 287
 288
 289 def str2date(date_str):
 290     return time.strftime("%y/%m/%d",time.strptime(date_str,"%d%m%y"))
 291
 292 def str2float(str):
 293     try:
 294         return float(str)
 295     except:
 296         return 0.0
 297
 298 def list2float(lst):
 299     try:
 300         return str2float((lambda s : s[:-3] + '.' + s[-3:])(lst))
 301     except:
 302         return 0.0
 303
 304 class coda_import(wizard.interface):
 305     def _action_open_window(self, cr, uid, data, context):
 306         form=data['form']
 307         return {
 308             'domain':"[('id','in',%s)]"%(form['statment_ids']),
 309             'name': 'Statement',
 310             'view_type': 'form',
 311             'view_mode': 'tree,form',
 312             'res_model': 'account.bank.statement',
 313             'view_id': False,
 314             'type': 'ir.actions.act_window',
 315         }
 316     states = {
 317         'init' : {
 318             'actions' : [],
 319             'result' : {'type' : 'form',
 320                     'arch' : codawiz_form,
 321                     'fields' : codawiz_fields,
 322                     'state' : [('end', '_Close'),('extraction', '_Ok') ]}
 323         },
 324         'extraction' : {
 325             'actions' : [_coda_parsing],
 326             'result' : {'type' : 'form',
 327                     'arch' : result_form,
 328                     'fields' : result_fields,
 329                     'state' : [('end', '_Close'),('open', '_Open Statement')]}
 330         },
 331         'open': {
 332             'actions': [],
 333             'result': {'type': 'action', 'action': _action_open_window, 'state': 'end'}
 334
 335             },
 336
 337     }
 338 coda_import("account.coda_import")
 339 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: