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