How can I import an order invoice from excel. The excel has id,description,quantity columns and the customer name in a seperate cell. I am using a Python Script to Import product data using XML-RPC, but I don't know the nececery fields for invoices. Thank you.
Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:
- CRM
- e-Commerce
- Accounting
- Inventory
- PoS
- Project management
- MRP
This question has been flagged
1
Reply
7460
Views
An invoice consists of the model account.invoice and account.invoice.line Each invoice should have 1 or more lines.
I made the following script, to import invoices (with the related lines) into the system. I do use an additional module, called openerplib.
# -*- coding: UTF-8 -*-
import openerplib
import datetime
h = "localhost"
db= 'dbName'
u = "admin"
p = 'mySecurePassword'
line = '-' *45 + '\n'
file_dbo_factuur = 'dbo_Factuur.csv'
file_dbo_factuurregel = 'dbo_Factuurregels.csv'
f_result = 'factuur_Result.txt'
f_error = 'factuur_Error.csv'
file_finance = 'factuur_finance.csv'
invoiceAdded = 0
invoiceRejected = 0
handmatig = []
################################################################################
## VARIOUS ROUTINES
################################################################################
def printHeading():
f_res.write(line)
dateX = str(datetime.datetime.now())
f_res.write('-' * 10 + dateX+'\n')
def initConnection(h, db, u, p):
printHeading()
f_res.write('Connecting to %s \nuser: %s\ndatabase: %s\n' %(h, u, db))
return openerplib.get_connection(hostname=h, database=db, login=u, password=p)
def addValsToDB(c, m, vals):
printHeading()
f_res.write('Adding data to %s\n' %(m))
print 'Adding data to', m
model = c.get_model(m)
result = []
i = 0
for v in vals:
result.append(model.create(v))
i += 1
x, y = divmod(i, 100)
if y == 0:
f_res.write('Added already %d records to %s\n' %(x*100, m))
f_res.write('Number of records added: %d\n' %(len(result)))
return result
def writeData(c, m, ids, vals):
printHeading()
f_res.write('Modifying data of model %s with id(s) = %s\n' %(m, ids))
print 'Modifying data of model', m, 'with id =', ids
model = c.get_model(m)
return model.write(ids, vals)
################################################################################
## SCRIPT SPECIFIC ROUTINE(S)
################################################################################
def addInvoice(c, factuur, lines):
global invoiceAdded
global invoiceRejected
#check invoice itself
factuur_split = factuur.split(';')
invoiceID = factuur_obj.search([('uniqueCodeFromOldSystem', '=', factuur_split[0])])
if invoiceID != []:
# print 'Factuur met id %s bestaat reeds' %(factuur_split[0])
f_fout.write('01;Factuur %s (id: %s) bestaat reeds;%s;%s;;;\n' %(factuur_split[2], factuur_split[0], factuur_split[2], factuur_split[0]))
invoiceRejected += 1
return
orderID = order_obj.search([('uniqueCodeFromOldSystem', '=', factuur_split[26])])
# what to do if you cannot find the order?
if orderID == []:
#search in old orders
sjoemel = True
partner = partner_obj.search([('uniqueCodeFromOldSystem', '=', factuur_split[6])])
if partner == []:
f_fout.write('02;Customer (id %s) with invoice %s (id: %s) not found;%s;%s;%s;;\n' %(factuur_split[6], factuur_split[2], factuur_split[0], factuur_split[2], factuur_split[0], factuur_split[26]))
invoiceRejected += 1
return
partner = partner[0]
lines = []
else:
sjoemel = False
orderID = orderID[0]
order = order_obj.read(orderID)
verz_kosten = factuur_split[19]
resFactLines = []
# check lines
for factuur_line in lines:
line_split = factuur_line.split(';')
orderlineID = orderline_obj.search([('hb_id_q', '=', line_split[3])])
if orderlineID == []:
#search in old orderlines.....
orderlineOld = True
orderlineID = old_orderline_obj.search([('orderline_id', '=', line_split[3])])
if orderlineID == []:
f_fout.write('03;Invoiceline id %s with invoice %s ca not find orderline ID %s;%s;%s;%s;%s\n' %(line_split[0], line_split[2], line_split[3], factuur_split[2], factuur_split[0], factuur_split[26], line_split[3]))
continue #next factuurline
else:
orderlineOld = False
line_split[13] = orderlineOld #replace \r\n
resFactLines.append(line_split)
if len(resFactLines) == 0 and not sjoemel:
f_fout.write('04;Factuur %s (id: %s) heeft geen factuurregels;;;;\n' %(factuur_split[2], factuur_split[0]))
invoiceRejected += 1
return
m = 'account.invoice'
vals = []
if sjoemel: #So no actuel order from this year
resOrder = order_obj.onchange_partner_id(0, partner)['value']
origin = 'Factuur %s, van order voor 2013' %(factuur_split[2])
currency = factuur_split[9]
if currency == '1': # Euro
pass
elif currency == '6': # USD
currency = '3'
elif currency == '7': # GBP
currency = '152'
fiscal = resOrder['fiscal_position']
reference = factuur_split[30]
user = 1 #fixed to admin
payment = resOrder['payment_term']
comment = factuur_split[13]
else:
origin = order['name']
currency = order['currency_id'][0]
partner = order['partner_id'][0]
if order['fiscal_position'] == False:
fiscal = False
else:
fiscal = order['fiscal_position'][0]
reference = order['client_order_ref']
user = order['user_id'][0]
payment = order['payment_term'][0]
comment = order['note']
vals.append({
'origin': origin,
'date_due': factuur_split[30],
'check_total': 0.0,
'partner_bank_id': False,
'supplier_invoice_number': False,
'paypal_url': False,
'company_id': 1,
'currency_id': currency,
'partner_id': partner,
'fiscal_position': fiscal,
'user_id': user,
'reference': reference,
'message_is_follower': False,
'payment_term': payment,
'reference_type': 'none',
'journal_id': 11,
'account_id': 166,
'type': 'out_invoice',
'internal_number': 'old invoice number: %s' %(factuur_split[2]),
'reconciled': False,
'move_name': '/',
'section_id': False,
'date_invoice': factuur_split[3],
'name': factuur_split[29], #?
'comment': comment,
'sent': False,
'message_unread': False,
'uniqueCodeFromOldSystem': factuur_split[0],
})
invoiceID = addValsToDB(connection, m, vals)[0]
# add the various lines to the invoice.
vals = []
m = 'account.invoice.line'
invoice_line_tax_id = []
if fiscal == 1: #NL
invoice_line_tax_id = [2]
elif fiscal == 2: # EU
invoice_line_tax_id = [15]
elif fiscal == 3: # Non EU
invoice_line_tax_id = [12]
else: #Default
invoice_line_tax_id = [2]
for line_split in resFactLines:
orderlineOld = line_split[13]
if orderlineOld:
orderlineID = old_orderline_obj.search([('orderline_id', '=', line_split[3])])
orderline = old_orderline_obj.read(orderlineID)[0]
product = prod_obj.read(int(orderline['product_id']))
else:
orderlineID = orderline_obj.search([('hb_id_q', '=', line_split[3])])
orderLine = orderline_obj.read(orderlineID)[0]
product = prod_obj.read(orderLine['product_id'][0])
#Invoice lines contain price AFTER discount
priceArticle = float(line_split[6])
if line_split[4] != '0' and line_split[4] != '100':
priceArticle = (priceArticle / (100 - int(line_split[4]))) * 100.0
vals.append({
'origin': origin,
'uos_id': 1,
'asset_category_id': False,
'account_id': 458,
'name': product['name'],
'sequence': 5,
'invoice_id': invoiceID,
'price_unit': priceArticle,
'company_id': 1,
'invoice_line_tax_id': [[6, 0, invoice_line_tax_id]],
'discount': line_split[4],
'account_analytic_id': False,
'quantity': line_split[5],
'partner_id': partner,
'product_id': product['id'],
'SomeUniqueCodeFromOldSystem': line_split[0],
})
if sjoemel: #Old order
vals.append({
'origin': origin,
'uos_id': 1,
'asset_category_id': False,
'account_id': 458,
'name': origin,
'sequence': 10,
'invoice_id': invoiceID,
'price_unit': float(factuur_split[10]),
'company_id': 1,
'invoice_line_tax_id': [[6, 0, invoice_line_tax_id]], #same as other lines, so use this
'discount': 0,
'account_analytic_id': False,
'quantity': 1,
'partner_id': partner,
'product_id': 1444, #fixed value for product to indicate old order
})
factuurLineIDs = addValsToDB(connection, m, vals)
# Validate invoice
# Give it a date
x = factuur_obj.action_date_assign([invoiceID])
# create the additional needed moves (financial)
x = factuur_obj.action_move_create([invoiceID])
# generate the number
x = factuur_obj.action_number([invoiceID])
# Set the invoice as open
x = factuur_obj.invoice_validate([invoiceID], {})
if not sjoemel:
# Connect invoice to order
m = 'sale.order'
vals = {
'invoice_ids': [[4, invoiceID]] #use [[4, val]] instead of [[6, 0, val]] to ADD instead of REPLACE
}
x = writeData(connection, m, orderID, vals)
# print x
factuur = factuur_obj.read(invoiceID)
verschil = factuur['amount_total'] - float(factuur_split[12])
f_fin.write('%s;%s;%s;%s;%s\n'%(factuur_split[2], factuur_split[12], factuur['number'], str(factuur['amount_total']), verschil))
invoiceAdded += 1
################################################################################
## Start of the script
################################################################################
f_fout = open(f_error, 'a')
f_res = open(f_result, 'a')
startTime = str(datetime.datetime.now())
print startTime
f_fout.write('error code;Omschrijving;factuur;factuurID;order;orderregel\n')
#finance
f_fin = open(file_finance, 'w+')
f_fin.write('invoice Original;Amount original;Invoice openERP;Amount openERP;Difference (original - OpenERP)\n')
connection = openerplib.get_connection(hostname=h, database=db, login=u, password=p)
f_factuur = open(file_dbo_factuur, 'r')
f_factuurline = open(file_dbo_factuurregel, 'r')
# get rid of the headings
header_f = f_factuur.readline()
header_l = f_factuurline.readline()
# get the first entry of the factuurline
line_factuur = f_factuurline.readline()
numberOfLines = 1 #because of line above
factuur_obj = connection.get_model('account.invoice')
factuurline_obj = connection.get_model('account.invoice.line')
order_obj = connection.get_model('sale.order')
orderline_obj = connection.get_model('sale.order.line')
prod_obj = connection.get_model('product.product')
old_order_obj = connection.get_model('hb.order')
old_orderline_obj = connection.get_model('hb.order.line')
partner_obj = connection.get_model('res.partner')
s = ''
numberOfRecords = 0
for factuur in f_factuur:
lines = []
numberOfRecords += 1
factuur_result = factuur.split(';')
if len(factuur_result) != 32:
print 'Invoice with wrong length %s.' %(factuur)
continue
s = factuur
while s:
if line_factuur == '\n': # end of file
if len(lines) > 0:
result = addInvoice(connection, s, lines)
print 'Einde, wegens alleen \\n'
break
if line_factuur == '': # end of file?!
if len(lines) > 0:
result = addInvoice(connection, s, lines)
print 'Einde, wegens lege regel'
break
if line_factuur == '\r\n': # should not happen!
print line_factuur
print line_result
line_result = line_factuur.replace('\n','').split(';')
if factuur_result[0] < line_result[2]: #order lines later than order
if len(lines) > 0:
result = addInvoice(connection, s, lines)
else:
f_fout.write('06;Factuur %s zonder regels\n' %(factuur_result[0]))
break
if factuur_result[0] == line_result[2]: # order line part of order
lines.append(line_factuur)
line_factuur = f_factuurline.readline()
numberOfLines += 1
s = ''
print line
print 'Records:', numberOfRecords
print 'Lines :', numberOfLines
print 'Invoices made in openERP:', invoiceAdded
print 'Invoices rejected by openERP:', invoiceRejected
print line
f_fout.write('01;Invoice exists\n')
f_fout.write('02;Order not found\n')
f_fout.write('03;Orderline not found\n')
f_fout.write('04;invoice without lines\n')
f_res.write(line+'\n')
f_res.write('Invoices read: %d\n' %(numberOfRecords))
f_res.write('Invoicelines read: %d\n' %(numberOfLines))
f_res.write('Invoices made in openERP: %d\n' %(invoiceAdded))
f_res.write('Invoices rejected by openERP: %d\n' %(invoiceRejected))
f_res.write(line+'\n')
eindTime = str(datetime.datetime.now())
f_fout.write(eindTime+'\n')
f_res.write(eindTime+'\n')
f_fin.write(eindTime+'\n')
f_fin.close()
f_fout.close()
f_res.close()
print eindTime
print 'einde'
print line
# vim:expandtab:smartindent:tabstop=2:softtabstop=2:shiftwidth=2: