Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

Import order invoice from excel

By
Chris
on 5/31/13, 4:52 AM 3,692 views

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.

0

patrick

--patrick--
1958
| 5 4 7
Baarn, Netherlands
--patrick--
patrick
On 5/31/13, 6:31 AM

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:

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

0 follower(s)

Stats

Asked: 5/31/13, 4:52 AM
Seen: 3692 times
Last updated: 3/16/15, 8:10 AM