addons/l10n_ch/wizard/bvr_import.py

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