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
  33 import base64
  34 import time
  35 import re
  36
  37 from tools.translate import _
  38 from osv import osv, fields
  39 from tools import mod10r
  40 import pooler
  41
  42 def _reconstruct_invoice_ref(cursor, user, reference, context=None):
  43     ###
  44     id_invoice = False
  45     # On fait d'abord une recherche sur toutes les factures
  46     # we now searhc for company
  47     user_obj = pooler.get_pool(cursor.dbname).get('res.users')
  48     user_current=user_obj.browse(cursor, user, user)
  49
  50     ##
  51     cursor.execute("SELECT inv.id,inv.number from account_invoice AS inv where inv.company_id = %s" ,(user_current.company_id.id,))
  52     result_invoice = cursor.fetchall()
  53
  54     for inv_id,inv_name in result_invoice:
  55         inv_name =  re.sub('[^0-9]', '0', str(inv_name))
  56         if inv_name == reference:
  57             id_invoice = inv_id
  58             break
  59     if  id_invoice:
  60         cursor.execute('SELECT l.id ' \
  61                     'FROM account_move_line l, account_invoice i ' \
  62                     'WHERE l.move_id = i.move_id AND l.reconcile_id is NULL  ' \
  63                         'AND i.id IN %s',(tuple([id_invoice]),))
  64         inv_line = []
  65         for id_line in cursor.fetchall():
  66             inv_line.append(id_line[0])
  67         return inv_line
  68     else:
  69         return []
  70     return True
  71
  72 def _import(self, cursor, user, data, context=None):
  73
  74     statement_line_obj = self.pool.get('account.bank.statement.line')
  75     statement_obj = self.pool.get('account.bank.statement')
  76     voucher_obj = self.pool.get('account.voucher')
  77     voucher_line_obj = self.pool.get('account.voucher.line')
  78     move_line_obj = self.pool.get('account.move.line')
  79     attachment_obj = self.pool.get('ir.attachment')
  80     file = data['form']['file']
  81     statement_id = data['id']
  82     records = []
  83     total_amount = 0
  84     total_cost = 0
  85     find_total = False
  86
  87     if context is None:
  88         context = {}
  89     for lines in base64.decodestring(file).split("\n"):
  90         # Manage files without carriage return
  91         while lines:
  92             (line, lines) = (lines[:128], lines[128:])
  93             record = {}
  94
  95             if line[0:3] in ('999', '995'):
  96                 if find_total:
  97                     raise osv.except_osv(_('Error'),
  98                             _('Too much total record found!'))
  99                 find_total = True
 100                 if lines:
 101                     raise osv.except_osv(_('Error'),
 102                             _('Record found after total record!'))
 103                 amount = float(line[39:49]) + (float(line[49:51]) / 100)
 104                 cost = float(line[69:76]) + (float(line[76:78]) / 100)
 105                 if line[2] == '5':
 106                     amount *= -1
 107                     cost *= -1
 108
 109                 if round(amount - total_amount, 2) >= 0.01 \
 110                         or round(cost - total_cost, 2) >= 0.01:
 111                     raise osv.except_osv(_('Error'),
 112                             _('Total record different from the computed!'))
 113                 if int(line[51:63]) != len(records):
 114                     raise osv.except_osv(_('Error'),
 115                             _('Number record different from the computed!'))
 116             else:
 117                 record = {
 118                     'reference': line[12:39],
 119                     'amount': float(line[39:47]) + (float(line[47:49]) / 100),
 120                     'date': time.strftime('%Y-%m-%d',
 121                         time.strptime(line[65:71], '%y%m%d')),
 122                     'cost': float(line[96:98]) + (float(line[98:100]) / 100),
 123                 }
 124
 125                 if record['reference'] != mod10r(record['reference'][:-1]):
 126                     raise osv.except_osv(_('Error'),
 127                             _('Recursive mod10 is invalid for reference: %s') % \
 128                                     record['reference'])
 129
 130                 if line[2] == '5':
 131                     record['amount'] *= -1
 132                     record['cost'] *= -1
 133                 total_amount += record['amount']
 134                 total_cost += record['cost']
 135                 records.append(record)
 136
 137 #    model_fields_ids = model_fields_obj.search(cursor, user, [
 138 #        ('name', 'in', ['property_account_receivable', 'property_account_payable']),
 139 #        ('model', '=', 'res.partner'),
 140 #        ], context=context)
 141 #    property_ids = property_obj.search(cursor, user, [
 142 #        ('fields_id', 'in', model_fields_ids),
 143 #        ('res_id', '=', False),
 144 #        ], context=context)
 145     account_receivable = False
 146     account_payable = False
 147     statement = statement_obj.browse(cursor, user, statement_id, context=context)
 148
 149     for record in records:
 150         # Remove the 11 first char because it can be adherent number
 151         # TODO check if 11 is the right number
 152         reference = record['reference'][11:-1].lstrip('0')
 153         values = {
 154             'name': 'IN '+ reference,
 155             'date': record['date'],
 156             'amount': record['amount'],
 157             'ref': reference,
 158             'type': (record['amount'] >= 0 and 'customer') or 'supplier',
 159             'statement_id': statement_id,
 160         }
 161
 162         line_ids = move_line_obj.search(cursor, user, [
 163             ('ref', 'like', reference),
 164             ('reconcile_id', '=', False),
 165             ('account_id.type', 'in', ['receivable', 'payable']),
 166             ], order='date desc', context=context)
 167         if not line_ids:
 168             line_ids = _reconstruct_invoice_ref(cursor, user, reference, None)
 169
 170         partner_id = False
 171         account_id = False
 172         for line in move_line_obj.browse(cursor, user, line_ids, context=context):
 173             account_receivable = line.partner_id.property_account_receivable.id
 174             account_payable = line.partner_id.property_account_payable.id
 175             partner_id = line.partner_id.id
 176             move_id = line.move_id.id
 177             if record['amount'] >= 0:
 178                 if round(record['amount'] - line.debit, 2) < 0.01:
 179 #                    line2reconcile = line.id
 180                     account_id = line.account_id.id
 181                     break
 182             else:
 183                 if round(line.credit + record['amount'], 2) < 0.01:
 184 #                    line2reconcile = line.id
 185                     account_id = line.account_id.id
 186                     break
 187         result = voucher_obj.onchange_partner_id(cursor, user, [], partner_id=partner_id, journal_id=statement.journal_id.id, price=abs(record['amount']), currency_id= statement.currency.id, ttype='payment', context=context)
 188         voucher_res = { 'type': 'payment' ,
 189
 190              'name': values['name'],
 191              'partner_id': partner_id,
 192              'journal_id': statement.journal_id.id,
 193              'account_id': result.get('account_id', statement.journal_id.default_credit_account_id.id),
 194              'company_id': statement.company_id.id,
 195              'currency_id': statement.currency.id,
 196              'date': record['date'] or time.strftime('%Y-%m-%d'),
 197              'amount': abs(record['amount']),
 198             'period_id': statement.period_id.id
 199              }
 200         voucher_id = voucher_obj.create(cursor, user, voucher_res, context=context)
 201         context.update({'move_line_ids': line_ids})
 202         values['voucher_id'] = voucher_id
 203         voucher_line_dict =  False
 204         if result['value']['line_ids']:
 205             for line_dict in result['value']['line_ids']:
 206                 move_line = move_line_obj.browse(cursor, user, line_dict['move_line_id'], context)
 207                 if move_id == move_line.move_id.id:
 208                     voucher_line_dict = line_dict
 209         if voucher_line_dict:
 210             voucher_line_dict.update({'voucher_id':voucher_id})
 211             voucher_line_obj.create(cursor, user, voucher_line_dict, context=context)
 212
 213         if not account_id:
 214             if record['amount'] >= 0:
 215                 account_id = account_receivable
 216             else:
 217                 account_id = account_payable
 218         if not account_id :
 219             raise osv.except_osv(_('Error'),
 220                 _('The properties account payable account receivable'))
 221         values['account_id'] = account_id
 222         values['partner_id'] = partner_id
 223
 224 #            values['reconcile_id'] = statement_reconcile_obj.create(cursor, user, {
 225 #                'line_ids': [(6, 0, [line2reconcile])],
 226 #                }, context=context)
 227
 228         statement_line_obj.create(cursor, user, values, context=context)
 229     attachment_obj.create(cursor, user, {
 230         'name': 'BVR',
 231         'datas': file,
 232         'datas_fname': 'BVR.txt',
 233         'res_model': 'account.bank.statement',
 234         'res_id': statement_id,
 235         }, context=context)
 236
 237     return {}
 238
 239 class bvr_import_wizard(osv.osv_memory):
 240     _name = 'bvr.import.wizard'
 241     _columns = {
 242         'file':fields.binary('BVR File', required=True)
 243     }
 244
 245     def import_bvr(self, cr, uid, ids, context=None):
 246         data = {}
 247         if context is None:
 248             context = {}
 249         active_ids = context.get('active_ids', [])
 250         active_id = context.get('active_id', False)
 251         data['form'] = {}
 252         data['ids'] = active_ids
 253         data['id'] = active_id
 254         data['form']['file'] = ''
 255         res = self.read(cr, uid, ids[0], ['file'])
 256         if res:
 257             data['form']['file'] = res['file']
 258         _import(self, cr, uid, data, context=context)
 259         return {'type': 'ir.actions.act_window_close'}
 260
 261 bvr_import_wizard()
 262
 263 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: