account_invoice_line.py 2.6 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768
  1. # -*- coding: utf-8 -*-
  2. from openerp.http import request as r
  3. def get_account_invoice_line_widget():
  4. user_store = r.env.user.store_id.id
  5. company_currency_rate = r.env.user.company_id.currency_id.rate
  6. query = '''
  7. SELECT
  8. invoice.id AS invoice_id,
  9. line.id AS invoice_line_id,
  10. invoice.number,
  11. invoice.origin,
  12. invoice.date_invoice,
  13. invoice.type,
  14. product.name_template,
  15. line.medic_id,
  16. line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit,
  17. line.quantity AS cant,
  18. line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal,
  19. (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos,
  20. (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost
  21. FROM account_invoice AS invoice
  22. LEFT JOIN account_invoice_line AS line
  23. ON line.invoice_id = invoice.id
  24. LEFT JOIN product_product AS product
  25. ON line.product_id = product.id
  26. LEFT JOIN res_store_journal_rel AS journal
  27. ON journal.journal_id = invoice.journal_id
  28. LEFT JOIN product_price_history AS history
  29. ON history.product_template_id = product.product_tmpl_id
  30. LEFT JOIN res_currency_rate AS rate
  31. ON rate.currency_id = invoice.currency_id
  32. WHERE invoice.state NOT IN ('draft', 'cancel')
  33. AND TO_CHAR(invoice.date_invoice,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
  34. AND journal.store_id = ''' + str(user_store) + '''
  35. GROUP BY
  36. invoice.id,
  37. line.id,
  38. invoice.number,
  39. invoice.origin,
  40. invoice.date_invoice,
  41. line.medic_id,
  42. product.name_template,
  43. line.price_unit,
  44. line.quantity,
  45. line.price_subtotal
  46. '''
  47. r.cr.execute(query,(tuple([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate])))
  48. return [
  49. {
  50. 'invoice_id': j[0],
  51. 'invoice_line_id': j[1],
  52. 'number': j[2],
  53. 'origin': j[3],
  54. 'date': j[4],
  55. 'type': j[5],
  56. 'product_name':j[6],
  57. 'price_unit':j[7],
  58. 'quantity':j[8],
  59. 'subtotal':j[9],
  60. 'tax': j[10],
  61. 'cost': j[11],
  62. 'history_id': [12],
  63. 'medic_id': [13],
  64. } for j in r.cr.fetchall()
  65. ]