addons/l10n_ch/wizard/bvr_import.py

   1 # -*- coding: utf-8 -*-
   2 #
   3 #  bvr_import.py
   4 #  l10n_ch
   5 #
   6 #  Created by Nicolas Bessi based on Credric Krier contribution
   7 #
   8 #  Copyright (c) 2009 CamptoCamp. All rights reserved.
   9 ##############################################################################
  10 # WARNING: This program as such is intended to be used by professional
  11 # programmers who take the whole responsability of assessing all potential
  12 # consequences resulting from its eventual inadequacies and bugs
  13 # End users who are looking for a ready-to-use solution with commercial
  14 # garantees and support are strongly adviced to contract a Free Software
  15 # Service Company
  16 #
  17 # This program is Free Software; you can redistribute it and/or
  18 # modify it under the terms of the GNU General Public License
  19 # as published by the Free Software Foundation; either version 2
  20 # of the License, or (at your option) any later version.
  21 #
  22 # This program is distributed in the hope that it will be useful,
  23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  25 # GNU General Public License for more details.
  26 #
  27 # You should have received a copy of the GNU General Public License
  28 # along with this program; if not, write to the Free Software
  29 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  30 #
  31 ##############################################################################
  32 import wizard
  33 import pooler
  34 import base64
  35 import time
  36 from tools import mod10r
  37 import re
  38 from tools.translate import _
  39
  40 ASK_FORM = """<?xml version="1.0"?>
  41 <form string="BVR Import">
  42     <field name="file"/>
  43 </form>"""
  44
  45 ASK_FIELDS = {
  46     'file': {
  47         'string': 'BVR file',
  48         'type': 'binary',
  49         'required': True,
  50     },
  51 }
  52
  53 def _reconstruct_invoice_ref(cursor, user, reference, context):
  54     ###
  55     id_invoice = False
  56     # On fait d'abord une recherche sur toutes les factures
  57     # we now searhc for company
  58     user_obj=pooler.get_pool(cursor.dbname).get('res.users')
  59     user_current=user_obj.browse(cursor, user, user)
  60
  61     ##
  62     cursor.execute("SELECT inv.id,inv.number from account_invoice AS inv where inv.company_id = %s" ,(user_current.company_id.id,))
  63     result_invoice = cursor.fetchall()
  64
  65     for inv_id,inv_name in result_invoice:
  66         inv_name =  re.sub('[^0-9]', '0', str(inv_name))
  67         if inv_name == reference:
  68             id_invoice = inv_id
  69             break
  70     if  id_invoice:
  71         cursor.execute('SELECT l.id ' \
  72                     'FROM account_move_line l, account_invoice i ' \
  73                     'WHERE l.move_id = i.move_id AND l.reconcile_id is NULL  ' \
  74                         'AND i.id =ANY(%s)',([id_invoice],))
  75         inv_line = []
  76         for id_line in cursor.fetchall():
  77             inv_line.append(id_line[0])
  78         return inv_line
  79     else:
  80         return []
  81     return True
  82 def _import(obj, cursor, user, data, context):
  83
  84     pool = pooler.get_pool(cursor.dbname)
  85     statement_line_obj = pool.get('account.bank.statement.line')
  86     statement_reconcile_obj = pool.get('account.bank.statement.reconcile')
  87     move_line_obj = pool.get('account.move.line')
  88     property_obj = pool.get('ir.property')
  89     model_fields_obj = pool.get('ir.model.fields')
  90     attachment_obj = pool.get('ir.attachment')
  91     file = data['form']['file']
  92     statement_id = data['id']
  93
  94     records = []
  95     total_amount = 0
  96     total_cost = 0
  97     find_total = False
  98
  99     for lines in base64.decodestring(file).split("\n"):
 100         # Manage files without carriage return
 101         while lines:
 102             (line, lines) = (lines[:128], lines[128:])
 103             record = {}
 104
 105             if line[0:3] in ('999', '995'):
 106                 if find_total:
 107                     raise wizard.except_wizard(_('Error'),
 108                             _('Too much total record found!'))
 109                 find_total = True
 110                 if lines:
 111                     raise wizard.except_wizard(_('Error'),
 112                             _('Record found after total record!'))
 113                 amount = float(line[39:49]) + (float(line[49:51]) / 100)
 114                 cost = float(line[69:76]) + (float(line[76:78]) / 100)
 115                 if line[2] == '5':
 116                     amount *= -1
 117                     cost *= -1
 118
 119                 if round(amount - total_amount, 2) >= 0.01 \
 120                         or round(cost - total_cost, 2) >= 0.01:
 121                     raise wizard.except_wizard(_('Error'),
 122                             _('Total record different from the computed!'))
 123                 if int(line[51:63]) != len(records):
 124                     raise wizard.except_wizard(_('Error'),
 125                             _('Number record different from the computed!'))
 126             else:
 127                 record = {
 128                     'reference': line[12:39],
 129                     'amount': float(line[39:47]) + (float(line[47:49]) / 100),
 130                     'date': time.strftime('%Y-%m-%d',
 131                         time.strptime(line[65:71], '%y%m%d')),
 132                     'cost': float(line[96:98]) + (float(line[98:100]) / 100),
 133                 }
 134
 135                 if record['reference'] != mod10r(record['reference'][:-1]):
 136                     raise wizard.except_wizard(_('Error'),
 137                             _('Recursive mod10 is invalid for reference: %s') % \
 138                                     record['reference'])
 139
 140                 if line[2] == '5':
 141                     record['amount'] *= -1
 142                     record['cost'] *= -1
 143                 total_amount += record['amount']
 144                 total_cost += record['cost']
 145                 records.append(record)
 146
 147     model_fields_ids = model_fields_obj.search(cursor, user, [
 148         ('name', 'in', ['property_account_receivable', 'property_account_payable']),
 149         ('model', '=', 'res.partner'),
 150         ], context=context)
 151     property_ids = property_obj.search(cursor, user, [
 152         ('fields_id', 'in', model_fields_ids),
 153         ('res_id', '=', False),
 154         ], context=context)
 155
 156     account_receivable = False
 157     account_payable = False
 158     for property in property_obj.browse(cursor, user, property_ids, context=context):
 159         if property.fields_id.name == 'property_account_receivable':
 160             account_receivable = int(property.value.split(',')[1])
 161         elif property.fields_id.name == 'property_account_payable':
 162             account_payable = int(property.value.split(',')[1])
 163
 164     for record in records:
 165         # Remove the 11 first char because it can be adherent number
 166         # TODO check if 11 is the right number
 167         reference = record['reference'][11:-1].lstrip('0')
 168         values = {
 169             'name': 'IN '+ reference,
 170             'date': record['date'],
 171             'amount': record['amount'],
 172             'ref': reference,
 173             'type': (record['amount'] >= 0 and 'customer') or 'supplier',
 174             'statement_id': statement_id,
 175         }
 176         line_ids = move_line_obj.search(cursor, user, [
 177             ('ref', 'like', reference),
 178             ('reconcile_id', '=', False),
 179             ('account_id.type', 'in', ['receivable', 'payable']),
 180             ], order='date desc', context=context)
 181         if not line_ids:
 182             line_ids = _reconstruct_invoice_ref(cursor,user,reference,None)
 183
 184         line2reconcile = False
 185         partner_id = False
 186         account_id = False
 187         for line in move_line_obj.browse(cursor, user, line_ids, context=context):
 188             if line.partner_id.id:
 189                 partner_id = line.partner_id.id
 190             if record['amount'] >= 0:
 191                 if round(record['amount'] - line.debit, 2) < 0.01:
 192                     line2reconcile = line.id
 193                     account_id = line.account_id.id
 194                     break
 195             else:
 196                 if round(line.credit + record['amount'], 2) < 0.01:
 197                     line2reconcile = line.id
 198                     account_id = line.account_id.id
 199                     break
 200         if not account_id:
 201             if record['amount'] >= 0:
 202                 account_id = account_receivable
 203             else:
 204                 account_id = account_payable
 205         if not account_id :
 206             raise wizard.except_wizard(_('Error'),
 207                 _('The properties account payable account receivable'))
 208         values['account_id'] = account_id
 209         values['partner_id'] = partner_id
 210
 211         if line2reconcile:
 212             values['reconcile_id'] = statement_reconcile_obj.create(cursor, user, {
 213                 'line_ids': [(6, 0, [line2reconcile])],
 214                 }, context=context)
 215
 216         statement_line_obj.create(cursor, user, values, context=context)
 217     attachment_obj.create(cursor, user, {
 218         'name': 'BVR',
 219         'datas': file,
 220         'datas_fname': 'BVR.txt',
 221         'res_model': 'account.bank.statement',
 222         'res_id': statement_id,
 223         }, context=context)
 224     return {}
 225
 226
 227 class BVRImport(wizard.interface):
 228     states = {
 229         'init': {
 230             'actions': [],
 231             'result': {
 232                 'type': 'form',
 233                 'arch': ASK_FORM,
 234                 'fields': ASK_FIELDS,
 235                 'state': [
 236                     ('end', 'Cancel', 'gtk-cancel'),
 237                     ('import', 'Import', 'gtk-ok', True),
 238                 ],
 239             },
 240         },
 241         'import': {
 242             'actions': [],
 243             'result': {
 244                 'type': 'action',
 245                 'action': _import,
 246                 'state': 'end',
 247             },
 248         },
 249     }
 250
 251 BVRImport('l10n_ch.bvr_import')
 252 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: