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 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 General Public License for more details.
  17 #
  18 #    You should have received a copy of the GNU General Public License
  19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20 #
  21 ##############################################################################
  22
  23 import netsvc
  24 from osv import osv,fields
  25 from tools.translate import _
  26 from mx import DateTime
  27 import time
  28 import base64
  29
  30 def str2date(date_str):
  31     return time.strftime("%y/%m/%d",time.strptime(date_str,"%d%m%y"))
  32
  33 def str2float(str):
  34     try:
  35         return float(str)
  36     except:
  37         return 0.0
  38
  39 def list2float(lst):
  40     try:
  41         return str2float((lambda s : s[:-3] + '.' + s[-3:])(lst))
  42     except:
  43         return 0.0
  44
  45 class account_coda_import(osv.osv_memory):
  46     _name = 'account.coda.import'
  47     _description = 'Account Coda Import'
  48     _columns = {
  49             'journal_id': fields.many2one('account.journal', 'Bank Journal', required=True),
  50             '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'),
  51             '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',),
  52             'awaiting_account': fields.many2one('account.account', 'Default Account for Unrecognized Movement',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',),
  53             'coda': fields.binary('Coda File', required=True),
  54             'note':fields.text('Log'),
  55     }
  56     def coda_parsing(self, cr, uid, ids, context):
  57
  58
  59         journal_obj=self.pool.get('account.journal')
  60         account_period_obj = self.pool.get('account.period')
  61         partner_bank_obj=self.pool.get('res.partner.bank')
  62         bank_statement_obj=self.pool.get('account.bank.statement')
  63         move_line_obj=self.pool.get('account.move.line')
  64         bank_statement_line_obj=self.pool.get('account.bank.statement.line')
  65         statement_reconcile_obj=self.pool.get('account.bank.statement.reconcile')
  66         account_coda_obj=self.pool.get('account.coda')
  67         mod_obj = self.pool.get('ir.model.data')
  68
  69         data = self.read(cr, uid, ids)[0]
  70
  71
  72         codafile = data['coda']
  73         journal_code = journal_obj.browse(cr, uid, data['journal_id'], context).code
  74
  75         period = account_period_obj.find(cr, uid, context=context)[0]
  76         def_pay_acc = data['def_payable']
  77         def_rec_acc = data['def_receivable']
  78
  79         str_log = ""
  80         err_log = "Errors:\n------\n"
  81         nb_err=0
  82         std_log=''
  83         str_log1 = "Coda File is Imported  :  "
  84         str_not=''
  85         str_not1=''
  86
  87         bank_statements=[]
  88         bank_statement = {}
  89         recordlist = base64.decodestring(codafile).split('\n')
  90         recordlist.pop()
  91         for line in recordlist:
  92             if line[0] == '0':
  93                 # header data
  94
  95                 bank_statement["bank_statement_line"]={}
  96                 bank_statement_lines = {}
  97                 bank_statement['date'] = str2date(line[5:11])
  98                 bank_statement['journal_id']=data['journal_id']
  99                 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")))])
 100                 bank_statement['period_id'] = period_id and period_id[0] or False
 101                 bank_statement['state']='draft'
 102             elif line[0] == '1':
 103                 # old balance data
 104                 bal_start = list2float(line[43:58])
 105                 if line[42] == '1':
 106                     bal_start = - bal_start
 107                 bank_statement["balance_start"]= bal_start
 108                 bank_statement["acc_number"]=line[5:17]
 109                 bank_statement["acc_holder"]=line[64:90]
 110                 bank_statement['name'] = journal_code + ' ' + str(line[2:5])
 111
 112             elif line[0]=='2':
 113                 # movement data record 2
 114                 if line[1]=='1':
 115                     # movement data record 2.1
 116                     if bank_statement_lines.has_key(line[2:6]):
 117                         continue
 118                     st_line = {}
 119                     st_line['extra_note'] = ''
 120                     st_line['statement_id']=0
 121                     st_line['ref'] = line[2:10]
 122                     st_line['date'] = time.strftime('%Y-%m-%d',time.strptime(str2date(line[115:121]),"%y/%m/%d")),
 123                     st_line_amt = list2float(line[32:47])
 124
 125                     if line[61]=='1':
 126                         st_line['toreconcile'] = True
 127                         st_line['name']=line[65:77]
 128                     else:
 129                         st_line['toreconcile'] = False
 130                         st_line['name']=line[62:115]
 131
 132                     st_line['free_comm'] = st_line['name']
 133                     st_line['val_date']=time.strftime('%Y-%m-%d',time.strptime(str2date(line[47:53]),"%y/%m/%d")),
 134                     st_line['entry_date']=time.strftime('%Y-%m-%d',time.strptime(str2date(line[115:121]),"%y/%m/%d")),
 135                     st_line['partner_id']=0
 136                     if line[31] == '1':
 137                         st_line_amt = - st_line_amt
 138                         st_line['account_id'] = def_pay_acc
 139                     else:
 140                         st_line['account_id'] = def_rec_acc
 141                     st_line['amount'] = st_line_amt
 142                     bank_statement_lines[line[2:6]]=st_line
 143                     bank_statement["bank_statement_line"]=bank_statement_lines
 144                 elif line[1] == '2':
 145                     st_line_name = line[2:6]
 146                     bank_statement_lines[st_line_name].update({'account_id': data['awaiting_account']})
 147
 148                 elif line[1] == '3':
 149                     # movement data record 3.1
 150                     st_line_name = line[2:6]
 151                     st_line_partner_acc = str(line[10:47]).strip()
 152                     cntry_number=line[10:47].strip()
 153                     contry_name=line[47:125].strip()
 154                     bank_ids = partner_bank_obj.search(cr, uid, [('acc_number','=',st_line_partner_acc)])
 155                     bank_statement_lines[st_line_name].update({'cntry_number': cntry_number, 'contry_name': contry_name})
 156                     if bank_ids:
 157                         bank = partner_bank_obj.browse(cr, uid, bank_ids[0], context)
 158                         if line and bank.partner_id:
 159                             bank_statement_lines[st_line_name].update({'partner_id': bank.partner_id.id})
 160                             if bank_statement_lines[st_line_name]['amount'] < 0 :
 161                                 bank_statement_lines[st_line_name].update({'account_id': bank.partner_id.property_account_payable.id})
 162                             else :
 163                                 bank_statement_lines[st_line_name].update({'account_id': bank.partner_id.property_account_receivable.id})
 164                     else:
 165                         nb_err += 1
 166                         err_log += _('The bank account %s is not defined for the partner %s.\n')%(cntry_number,contry_name)
 167                         bank_statement_lines[st_line_name].update({'account_id': data['awaiting_account']})
 168
 169                     bank_statement["bank_statement_line"]=bank_statement_lines
 170             elif line[0]=='3':
 171                 if line[1] == '1':
 172                     st_line_name = line[2:6]
 173                     bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[40:113]
 174                 elif line[1] == '2':
 175                     st_line_name = line[2:6]
 176                     bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[10:115]
 177                 elif line[1] == '3':
 178                     st_line_name = line[2:6]
 179                     bank_statement_lines[st_line_name]['extra_note'] += '\n' + line[10:100]
 180             elif line[0]=='8':
 181                 # new balance record
 182                 bal_end = list2float(line[42:57])
 183                 if line[41] == '1':
 184                     bal_end = - bal_end
 185                 bank_statement["balance_end_real"]= bal_end
 186
 187             elif line[0]=='9':
 188                 # footer record
 189
 190                 bank_statements.append(bank_statement)
 191         #end for
 192         bkst_list=[]
 193         for statement in bank_statements:
 194             try:
 195                 bk_st_id =bank_statement_obj.create(cr,uid,{
 196                     'journal_id': statement['journal_id'],
 197                     'date':time.strftime('%Y-%m-%d',time.strptime(statement['date'],"%y/%m/%d")),
 198                     'period_id':statement['period_id'] or period,
 199                     'balance_start': statement["balance_start"],
 200                     'balance_end_real': statement["balance_end_real"],
 201                     'state': 'draft',
 202                     'name': statement['name'],
 203                 })
 204                 lines=statement["bank_statement_line"]
 205                 for value in lines:
 206                     line=lines[value]
 207                     reconcile_id = False
 208                     if line['toreconcile']:
 209                         rec_id = move_line_obj.search(cr, uid, [('name','=',line['name']),('reconcile_id','=',False),('account_id.reconcile','=',True)])
 210                         if rec_id:
 211                             reconcile_id = statement_reconcile_obj.create(cr, uid, {
 212                                 'line_ids': [(6, 0, rec_id)]
 213                                 }, context=context)
 214                     str_not1 = ''
 215                     if line.has_key('contry_name') and line.has_key('cntry_number'):
 216                         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])
 217                     id=bank_statement_line_obj.create(cr,uid,{
 218                                'name':line['name'],
 219                                'date': line['date'],
 220                                'amount': line['amount'],
 221                                'partner_id':line['partner_id'] or 0,
 222                                'account_id':line['account_id'],
 223                                'statement_id': bk_st_id,
 224                                'reconcile_id': reconcile_id,
 225                                'note':str_not1,
 226                                'ref':line['ref'],
 227                                })
 228
 229                 str_not= "\n \n Account Number: %s \n Account Holder Name: %s " %(statement["acc_number"],statement["acc_holder"])
 230                 std_log += "\nStatement : %s , Date  : %s, Starting Balance :  %.2f , Ending Balance : %.2f \n"\
 231                           %(statement['name'], statement['date'], float(statement["balance_start"]), float(statement["balance_end_real"]))
 232                 bkst_list.append(bk_st_id)
 233
 234             except osv.except_osv, e:
 235                 cr.rollback()
 236                 nb_err+=1
 237                 err_log += '\n Application Error : ' + str(e)
 238                 raise # REMOVEME
 239
 240             except Exception, e:
 241                 cr.rollback()
 242                 nb_err+=1
 243                 err_log += '\n System Error : '+str(e)
 244                 raise # REMOVEME
 245             except :
 246                 cr.rollback()
 247                 nb_err+=1
 248                 err_log += '\n Unknown Error'
 249                 raise
 250         err_log += '\n\nNumber of statements : '+ str(len(bkst_list))
 251         err_log += '\nNumber of error :'+ str(nb_err) +'\n'
 252
 253         account_coda_obj.create(cr, uid, {
 254             'name':codafile,
 255             'statement_ids': [(6, 0, bkst_list,)],
 256             'note':str_log1+str_not+std_log+err_log,
 257             'journal_id':data['journal_id'],
 258             'date':time.strftime("%Y-%m-%d"),
 259             'user_id':uid,
 260             })
 261         test=''
 262         test=str_log1 + std_log + err_log
 263         self.write(cr, uid,ids, {'note':test}, context=context)
 264         extraction= { 'statment_ids':bkst_list}
 265         context.update({ 'statment_ids':bkst_list})
 266         model_data_ids = mod_obj.search(cr, uid, [('model', '=', 'ir.ui.view'),('name', '=', 'account_coda_note_view')], context=context)
 267         resource_id = mod_obj.read(cr, uid, model_data_ids, fields=['res_id'], context=context)[0]['res_id']
 268         return {
 269                 'name': _('Result'),
 270                 'res_id': ids[0],
 271                 'view_type': 'form',
 272                 'view_mode': 'form',
 273                 'res_model': 'account.coda.import',
 274                 'view_id': False,
 275                 'target':'new',
 276                 'views': [(resource_id,'form')],
 277                 'context': context,
 278                 'type': 'ir.actions.act_window',
 279         }
 280     def action_open_window(self, cr, uid,data,  context):
 281         return {
 282             'domain':"[('id','in',%s)]"%(context.get('statment_ids',False)),
 283             'name': 'Statement',
 284             'view_type': 'form',
 285             'view_mode': 'tree,form',
 286             'res_model': 'account.bank.statement',
 287             'view_id': False,
 288             'type': 'ir.actions.act_window',
 289         }
 290
 291 account_coda_import()