account_voucher.py 4.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154
  1. # -*- coding: utf-8 -*-
  2. from openerp.http import request as r
  3. def get_account_voucher_all():
  4. company_currency_rate = r.env.user.company_id.currency_id.rate
  5. query = '''
  6. SELECT voucher.id,
  7. voucher.number,
  8. voucher.type,
  9. voucher.date,
  10. journal.currency,
  11. voucher.amount,
  12. CASE
  13. WHEN journal.currency IS NULL
  14. THEN voucher.amount
  15. ELSE voucher.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
  16. END AS amount_currency,
  17. voucher.journal_id,
  18. voucher.reference
  19. FROM account_voucher AS voucher
  20. LEFT JOIN res_store_journal_rel AS journal_rel
  21. ON voucher.journal_id = journal_rel.journal_id
  22. LEFT JOIN account_journal AS journal
  23. ON journal.id = voucher.journal_id
  24. LEFT JOIN res_currency_rate AS rate
  25. ON rate.currency_id = journal.currency
  26. WHERE voucher.state = 'posted'
  27. GROUP BY
  28. voucher.id,
  29. voucher.number,
  30. voucher.type,
  31. voucher.date,
  32. voucher.amount,
  33. journal.name,
  34. journal.currency
  35. '''
  36. r.cr.execute(query,(tuple([company_currency_rate])))
  37. return [
  38. {
  39. 'id': j[0],
  40. 'number': j[1],
  41. 'type': j[2],
  42. 'date': j[3],
  43. 'currency_id': j[4],
  44. 'amount': j[5],
  45. 'amount_currency': j[6],
  46. 'journal_id': j[7],
  47. 'reference': j[8],
  48. } for j in r.cr.fetchall()
  49. ]
  50. def get_account_voucher_customer():
  51. company_currency_rate = r.env.user.company_id.currency_id.rate
  52. query = '''
  53. SELECT voucher.id,
  54. voucher.number,
  55. voucher.type,
  56. voucher.date,
  57. journal.currency,
  58. voucher.amount,
  59. CASE
  60. WHEN journal.currency IS NULL
  61. THEN voucher.amount
  62. ELSE voucher.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
  63. END AS amount_currency,
  64. voucher.journal_id,
  65. voucher.reference
  66. FROM account_voucher AS voucher
  67. LEFT JOIN res_store_journal_rel AS journal_rel
  68. ON voucher.journal_id = journal_rel.journal_id
  69. LEFT JOIN account_journal AS journal
  70. ON journal.id = voucher.journal_id
  71. LEFT JOIN res_currency_rate AS rate
  72. ON rate.currency_id = journal.currency
  73. WHERE voucher.state = 'posted'
  74. AND voucher.type = 'receipt'
  75. GROUP BY
  76. voucher.id,
  77. voucher.number,
  78. voucher.type,
  79. voucher.date,
  80. voucher.amount,
  81. journal.name,
  82. journal.currency
  83. '''
  84. r.cr.execute(query,(tuple([company_currency_rate])))
  85. return [
  86. {
  87. 'id': j[0],
  88. 'number': j[1],
  89. 'type': j[2],
  90. 'date': j[3],
  91. 'currency_id': j[4],
  92. 'amount': j[5],
  93. 'amount_currency': j[6],
  94. 'journal_id': j[7],
  95. 'reference': j[8],
  96. } for j in r.cr.fetchall()
  97. ]
  98. def get_account_voucher_supplier():
  99. company_currency_rate = r.env.user.company_id.currency_id.rate
  100. query = '''
  101. SELECT voucher.id,
  102. voucher.number,
  103. voucher.type,
  104. voucher.date,
  105. journal.currency,
  106. voucher.amount,
  107. CASE
  108. WHEN journal.currency IS NULL
  109. THEN voucher.amount
  110. ELSE voucher.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
  111. END AS amount_currency,
  112. voucher.journal_id,
  113. voucher.reference
  114. FROM account_voucher AS voucher
  115. LEFT JOIN res_store_journal_rel AS journal_rel
  116. ON voucher.journal_id = journal_rel.journal_id
  117. LEFT JOIN account_journal AS journal
  118. ON journal.id = voucher.journal_id
  119. LEFT JOIN res_currency_rate AS rate
  120. ON rate.currency_id = journal.currency
  121. WHERE voucher.state = 'posted'
  122. AND voucher.type = 'payment'
  123. GROUP BY
  124. voucher.id,
  125. voucher.number,
  126. voucher.type,
  127. voucher.date,
  128. voucher.amount,
  129. journal.name,
  130. journal.currency
  131. '''
  132. r.cr.execute(query,(tuple([company_currency_rate])))
  133. return [
  134. {
  135. 'id': j[0],
  136. 'number': j[1],
  137. 'type': j[2],
  138. 'date': j[3],
  139. 'currency_id': j[4],
  140. 'amount': j[5],
  141. 'amount_currency': j[6],
  142. 'journal_id': j[7],
  143. 'reference': j[8],
  144. } for j in r.cr.fetchall()
  145. ]