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