2
# -*- coding: utf-8 -*-
3
##############################################################################
5
# OpenERP, Open Source Management Solution
6
# Copyright (C) 2011 TeMPO Consulting, MSF. All Rights Reserved
7
# Developer: Olivier DOSSMANN
9
# This program is free software: you can redistribute it and/or modify
10
# it under the terms of the GNU Affero General Public License as
11
# published by the Free Software Foundation, either version 3 of the
12
# License, or (at your option) any later version.
14
# This program is distributed in the hope that it will be useful,
15
# but WITHOUT ANY WARRANTY; without even the implied warranty of
16
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17
# GNU Affero General Public License for more details.
19
# You should have received a copy of the GNU Affero General Public License
20
# along with this program. If not, see <http://www.gnu.org/licenses/>.
22
##############################################################################
25
from osv import fields
26
from tools.translate import _
27
from time import strftime
28
from tempfile import NamedTemporaryFile
29
from base64 import decodestring
30
from spreadsheet_xml.spreadsheet_xml import SpreadsheetXML
31
from csv import DictReader
35
class msf_doc_import_accounting(osv.osv_memory):
36
_name = 'msf.doc.import.accounting'
39
'date': fields.date(string="Migration date", required=True),
40
'file': fields.binary(string="File", filters='*.xml, *.xls', required=True),
41
'filename': fields.char(string="Imported filename", size=256),
42
'progression': fields.float(string="Progression", readonly=True),
43
'message': fields.char(string="Message", size=256, readonly=True),
44
'state': fields.selection([('draft', 'Created'), ('inprogress', 'In Progress'), ('error', 'Error'), ('done', 'Done')], string="State", readonly=True, required=True),
45
'error_ids': fields.one2many('msf.doc.import.accounting.errors', 'wizard_id', "Errors", readonly=True),
49
'date': lambda *a: strftime('%Y-%m-%d'),
50
'progression': lambda *a: 0.0,
51
'state': lambda *a: 'draft',
52
'message': lambda *a: _('Initialisation…'),
55
def create_entries(self, cr, uid, ids, context=None):
64
journal_ids = self.pool.get('account.journal').search(cr, uid, [('type', '=', 'migration')])
66
raise osv.except_osv(_('Warning'), _('No migration journal found!'))
67
journal_id = journal_ids[0]
68
# Fetch default funding pool: MSF Private Fund
70
msf_fp_id = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'analytic_distribution', 'analytic_account_msf_private_funds')[1]
74
for w in self.browse(cr, uid, ids):
76
entries = self.pool.get('msf.doc.import.accounting.lines').search(cr, uid, [('wizard_id', '=', w.id)])
79
self.write(cr, uid, [w.id], {'message': _('No lines…'), 'progression': 100.0})
82
b_entries = self.pool.get('msf.doc.import.accounting.lines').browse(cr, uid, entries)
84
self.write(cr, uid, [w.id], {'message': _('Grouping by currencies…'), 'progression': 10.0})
85
# Search all currencies (to create moves)
86
available_currencies = []
87
for entry in b_entries:
88
if (entry.currency_id.id, entry.period_id.id) not in available_currencies:
89
available_currencies.append((entry.currency_id.id, entry.period_id.id))
91
if available_currencies and len(available_currencies) > 1:
92
available_currencies = list(set(available_currencies))
94
self.write(cr, uid, ids, {'message': _('Writing a move for each currency…'), 'progression': 20.0})
96
nb_currencies = float(len(available_currencies))
97
current_percent = 20.0
98
remaining_percent = 80.0
99
step = float(remaining_percent / nb_currencies)
100
for c_id, p_id in available_currencies:
104
'manual_currency_id': c_id,
105
'journal_id': journal_id,
106
'document_date': w.date,
111
move_id = self.pool.get('account.move').create(cr, uid, move_vals, context)
112
for l_num, l in enumerate(b_entries):
114
progression = 20.0 + ((float(l_num) / float(len(b_entries))) * step) + (float(num - 1) * step)
115
self.write(cr, uid, [w.id], {'progression': progression})
116
if l.currency_id.id == c_id and l.period_id.id == p_id:
118
# Create analytic distribution
119
if l.account_id.user_type_code == 'expense':
120
distrib_id = self.pool.get('analytic.distribution').create(cr, uid, {}, context)
122
'distribution_id': distrib_id,
126
'source_date': l.date,
127
'destination_id': l.destination_id.id,
129
common_vals.update({'analytic_id': l.cost_center_id.id,})
130
cc_res = self.pool.get('cost.center.distribution.line').create(cr, uid, common_vals)
131
common_vals.update({'analytic_id': msf_fp_id, 'cost_center_id': l.cost_center_id.id,})
132
fp_res = self.pool.get('funding.pool.distribution.line').create(cr, uid, common_vals)
136
'name': l.description,
138
'account_id': l.account_id.id,
140
'document_date': l.document_date,
142
'journal_id': journal_id,
143
'debit_currency': l.debit,
144
'credit_currency': l.credit,
146
'analytic_distribution_id': distrib_id,
147
'partner_id': l.partner_id and l.partner_id.id or False,
148
'employee_id': l.employee_id and l.employee_id.id or False,
150
self.pool.get('account.move.line').create(cr, uid, move_line_vals, context, check=False)
152
progression = 20.0 + (float(num) * step)
153
self.write(cr, uid, [w.id], {'progression': progression})
157
def _import(self, dbname, uid, ids, context=None):
159
Do treatment before validation:
160
- check data from wizard
161
- check that file exists and that data are inside
162
- check integrity of data in files
167
# Prepare some values
168
cr = pooler.get_db(dbname).cursor()
174
self.write(cr, uid, ids, {'message': _('Cleaning up old imports…'), 'progression': 1.00})
175
# Clean up old temporary imported lines
176
old_lines_ids = self.pool.get('msf.doc.import.accounting.lines').search(cr, uid, [])
177
self.pool.get('msf.doc.import.accounting.lines').unlink(cr, uid, old_lines_ids)
180
for wiz in self.browse(cr, uid, ids):
182
self.write(cr, uid, [wiz.id], {'message': _('Checking file…'), 'progression': 2.00})
184
# Check that a file was given
186
raise osv.except_osv(_('Error'), _('Nothing to import.'))
188
self.write(cr, uid, [wiz.id], {'message': _('Copying file…'), 'progression': 3.00})
189
fileobj = NamedTemporaryFile('w+b', delete=False)
190
fileobj.write(decodestring(wiz.file))
192
content = SpreadsheetXML(xmlfile=fileobj.name)
194
raise osv.except_osv(_('Warning'), _('No content.'))
196
self.write(cr, uid, [wiz.id], {'message': _('Processing line number…'), 'progression': 4.00})
197
rows = content.getRows()
198
nb_rows = len([x for x in content.getRows()])
200
self.write(cr, uid, [wiz.id], {'message': _('Reading headers…'), 'progression': 5.00})
201
# Use the first row to find which column to use
203
col_names = ['Description', 'Reference', 'Document Date', 'Posting Date', 'G/L Account', 'Third party', 'Destination', 'Cost Centre', 'Booking Debit', 'Booking Credit', 'Booking Currency']
204
for num, r in enumerate(rows):
205
header = [x and x.data for x in r.iter_cells()]
208
cols[el] = header.index(el)
210
# Number of line to bypass in line's count
214
raise osv.except_osv(_('Error'), _("'%s' column not found in file.") % (el,))
218
self.write(cr, uid, [wiz.id], {'message': _('Reading lines…'), 'progression': 6.00})
219
# Check file's content
220
for num, r in enumerate(rows):
222
percent = (float(num+1) / float(nb_rows+1)) * 100.0
223
progression = ((float(num+1) * 94) / float(nb_rows)) + 6
224
self.write(cr, uid, [wiz.id], {'message': _('Checking file…'), 'progression': progression})
225
# Prepare some values
231
r_destination = False
233
r_document_date = False
236
current_line_num = num + base_num
237
# Fetch all XML row values
238
line = self.pool.get('import.cell.data').get_line_values(cr, uid, ids, r)
239
# Bypass this line if NO debit AND NO credit
240
if not line[cols['Booking Debit']] and not line[cols['Booking Credit']]:
243
# Check that currency is active
244
if not line[cols['Booking Currency']]:
245
errors.append(_('Line %s. No currency specified!') % (current_line_num,))
247
curr_ids = self.pool.get('res.currency').search(cr, uid, [('name', '=', line[cols['Booking Currency']])])
249
errors.append(_('Line %s. Currency not found: %s') % (current_line_num, line[cols['Booking Currency']],))
251
for c in self.pool.get('res.currency').browse(cr, uid, curr_ids):
253
errors.append(_('Line %s. Currency is not active: %s') % (current_line_num, line[cols['Booking Currency']],))
255
r_currency = curr_ids[0]
256
if not line[cols['Booking Currency']] in money:
257
money[line[cols['Booking Currency']]] = {}
258
if not 'debit' in money[line[cols['Booking Currency']]]:
259
money[line[cols['Booking Currency']]]['debit'] = 0
260
if not 'credit' in money[line[cols['Booking Currency']]]:
261
money[line[cols['Booking Currency']]]['credit'] = 0
262
if not 'name' in money[line[cols['Booking Currency']]]:
263
money[line[cols['Booking Currency']]]['name'] = line[cols['Booking Currency']]
264
# Increment global debit/credit
265
if line[cols['Booking Debit']]:
266
money[line[cols['Booking Currency']]]['debit'] += line[cols['Booking Debit']]
267
r_debit = line[cols['Booking Debit']]
268
if line[cols['Booking Credit']]:
269
money[line[cols['Booking Currency']]]['credit'] += line[cols['Booking Credit']]
270
r_credit = line[cols['Booking Credit']]
271
# Check document/posting dates
272
if not line[cols['Document Date']]:
273
errors.append(_('Line %s. No document date specified!') % (current_line_num,))
275
if not line[cols['Posting Date']]:
276
errors.append(_('Line %s. No posting date specified!') % (current_line_num,))
278
if line[cols['Document Date']] > line[cols['Posting Date']]:
279
errors.append(_("Line %s. Document date '%s' should be inferior or equal to Posting date '%s'.") % (current_line_num, line[cols['Document Date']], line[cols['Posting Date']],))
281
# Fetch document date and posting date
282
r_document_date = line[cols['Document Date']].strftime('%Y-%m-%d')
283
r_date = line[cols['Posting Date']].strftime('%Y-%m-%d')
284
# Check that a period exist and is open
285
period_ids = self.pool.get('account.period').get_period_from_date(cr, uid, r_date, context)
287
errors.append(_('Line %s. No period found for given date: %s') % (current_line_num, r_date))
289
r_period = period_ids[0]
291
if not line[cols['G/L Account']]:
292
errors.append(_('Line %s. No G/L account specified!') % (current_line_num,))
294
account_ids = self.pool.get('account.account').search(cr, uid, [('code', '=', line[cols['G/L Account']])])
296
errors.append(_('Line %s. G/L account %s not found!') % (current_line_num, line[cols['G/L Account']],))
298
r_account = account_ids[0]
299
account = self.pool.get('account.account').browse(cr, uid, r_account)
300
# Check that Third party exists (if not empty)
302
if line[cols['Third party']]:
303
if account.type_for_register == 'advance':
304
tp_ids = self.pool.get('hr.employee').search(cr, uid, [('name', '=', line[cols['Third party']])])
305
tp_label = 'Employee'
307
tp_ids = self.pool.get('res.partner').search(cr, uid, [('name', '=', line[cols['Third party']])])
309
errors.append(_('Line %s. %s not found: %s') % (current_line_num, tp_label, line[cols['Third party']],))
311
r_partner = tp_ids[0]
312
# Check analytic axis only if G/L account is an expense account
313
if account.user_type_code == 'expense':
315
if not line[cols['Destination']]:
316
errors.append(_('Line %s. No destination specified!') % (current_line_num,))
318
destination_ids = self.pool.get('account.analytic.account').search(cr, uid, [('category', '=', 'DEST'), '|', ('name', '=', line[cols['Destination']]), ('code', '=', line[cols['Destination']])])
319
if not destination_ids:
320
errors.append(_('Line %s. Destination %s not found!') % (current_line_num, line[cols['Destination']],))
322
r_destination = destination_ids[0]
324
if not line[cols['Cost Centre']]:
325
errors.append(_('Line %s. No cost center specified:') % (current_line_num,))
327
cc_ids = self.pool.get('account.analytic.account').search(cr, uid, [('category', '=', 'OC'), '|', ('name', '=', line[cols['Cost Centre']]), ('code', '=', line[cols['Cost Centre']])])
329
errors.append(_('Line %s. Cost Center %s not found!') % (current_line_num, line[cols['Cost Centre']]))
332
# NOTE: There is no need to check G/L account, Cost Center and Destination regarding document/posting date because this check is already done at Journal Entries validation.
334
# Registering data regarding these "keys":
341
'description': line[cols['Description']] or '',
342
'ref': line[cols['Reference']] or '',
343
'account_id': r_account or False,
344
'debit': r_debit or 0.0,
345
'credit': r_credit or 0.0,
346
'cost_center_id': r_cc or False,
347
'destination_id': r_destination or False,
348
'document_date': r_document_date or False,
349
'date': r_date or False,
350
'currency_id': r_currency or False,
352
'period_id': r_period or False,
354
if account.type_for_register == 'advance':
355
vals.update({'employee_id': r_partner,})
357
vals.update({'partner_id': r_partner,})
358
line_res = self.pool.get('msf.doc.import.accounting.lines').create(cr, uid, vals, context)
360
errors.append(_('Line %s. A problem occured for line registration. Please contact an Administrator.') % (current_line_num,))
363
# Check if all is ok for the file
364
## The lines should be balanced for each currency
366
if (money[c]['debit'] - money[c]['credit']) >= 10**-2:
367
raise osv.except_osv(_('Error'), _('Currency %s is not balanced: %s' ) % (money[c]['name'], (money[c]['debit'] - money[c]['credit']),))
370
self.write(cr, uid, ids, {'message': _('Check complete. Reading potential errors or write needed changes.'), 'progression': 100.0})
373
# If errors, cancel probable modifications
377
message = 'Import FAILED.'
379
error_ids = self.pool.get('msf.doc.import.accounting.errors').search(cr, uid, [], context)
381
self.pool.get('msf.doc.import.accounting.errors').unlink(cr, uid, error_ids ,context)
382
# create errors lines
384
self.pool.get('msf.doc.import.accounting.errors').create(cr, uid, {'wizard_id': wiz.id, 'name': e}, context)
388
self.write(cr, uid, ids, {'message': _('Writing changes…'), 'progression': 0.0})
389
# Create all journal entries
390
self.create_entries(cr, uid, ids, context)
391
message = 'Import successful.'
394
self.write(cr, uid, ids, {'message': message, 'state': wiz_state, 'progression': 100.0})
401
def button_validate(self, cr, uid, ids, context=None):
403
Launch process in a thread and return a wizard
409
thread = threading.Thread(target=self._import, args=(cr.dbname, uid, ids, context))
412
return self.write(cr, uid, ids, {'state': 'inprogress'}, context)
414
def button_update(self, cr, uid, ids, context=None):
420
msf_doc_import_accounting()
422
class msf_doc_import_accounting_lines(osv.osv):
423
_name = 'msf.doc.import.accounting.lines'
426
'description': fields.text("Description", required=False, readonly=True),
427
'ref': fields.text("Reference", required=False, readonly=True),
428
'document_date': fields.date("Document date", required=True, readonly=True),
429
'date': fields.date("Posting date", required=True, readonly=True),
430
'account_id': fields.many2one('account.account', "G/L Account", required=True, readonly=True),
431
'destination_id': fields.many2one('account.analytic.account', "Destination", required=False, readonly=True),
432
'cost_center_id': fields.many2one('account.analytic.account', "Cost Center", required=False, readonly=True),
433
'debit': fields.float("Debit", required=False, readonly=True),
434
'credit': fields.float("Credit", required=False, readonly=True),
435
'currency_id': fields.many2one('res.currency', "Currency", required=True, readonly=True),
436
'partner_id': fields.many2one('res.partner', "Partner", required=False, readonly=True),
437
'employee_id': fields.many2one('hr.employee', "Employee", required=False, readonly=True),
438
'period_id': fields.many2one('account.period', "Period", required=True, readonly=True),
439
'wizard_id': fields.integer("Wizard", required=True, readonly=True),
443
'description': lambda *a: '',
444
'ref': lambda *a: '',
445
'document_date': lambda *a: strftime('%Y-%m-%d'),
446
'date': lambda *a: strftime('%Y-%m-%d'),
447
'debit': lambda *a: 0.0,
448
'credit': lambda *a: 0.0,
451
msf_doc_import_accounting_lines()
453
class msf_doc_import_accounting_errors(osv.osv_memory):
454
_name = 'msf.doc.import.accounting.errors'
457
'name': fields.text("Description", readonly=True, required=True),
458
'wizard_id': fields.many2one('msf.doc.import.accounting', "Wizard", required=True, readonly=True),
461
msf_doc_import_accounting_errors()
462
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
added
removed
msf_doc_import/account.py
