product_product.py 4.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152
  1. # -*- coding: utf-8 -*-
  2. from openerp.http import request as r
  3. def get_product_product():
  4. user_store = r.env.user.store_id.id
  5. company_currency_rate = r.env.user.company_id.currency_id.rate
  6. validate_brand = '''
  7. SELECT EXISTS(
  8. SELECT table_name
  9. FROM information_schema.columns
  10. WHERE table_schema='public'
  11. AND table_name='product_brand')
  12. '''
  13. query_with_brand = '''
  14. SELECT
  15. product.id,
  16. template.categ_id,
  17. CASE
  18. WHEN product.default_code IS NOT NULL
  19. THEN ('[' || product.default_code || '] ' || product.name_template)
  20. ELSE product.name_template
  21. END AS display_name,
  22. (array_agg(attr_rel.att_id)) AS attr_rel,
  23. (array_agg(attr_value.name)) AS attr_value,
  24. (array_agg(attr.id)) AS attr,
  25. template.product_brand_id,
  26. brand.name,
  27. product.product_tmpl_id,
  28. template.list_price,
  29. product.default_code,
  30. product.active,
  31. template.sale_ok,
  32. template.company_id,
  33. template.store_id,
  34. template.type,
  35. product.ean13
  36. FROM product_product AS product
  37. LEFT JOIN product_template AS template
  38. ON template.id = product.product_tmpl_id
  39. LEFT JOIN product_attribute_value_product_product_rel AS attr_rel
  40. ON attr_rel.prod_id = product.id
  41. LEFT JOIN product_attribute_value AS attr_value
  42. ON attr_value.id = attr_rel.att_id
  43. LEFT JOIN product_attribute AS attr
  44. ON attr.id = attr_value.attribute_id
  45. LEFT JOIN product_brand AS brand
  46. ON brand.id = template.product_brand_id
  47. GROUP BY
  48. product.id,
  49. template.categ_id,
  50. product.default_code,
  51. template.product_brand_id,
  52. brand.name,
  53. template.list_price,
  54. template.sale_ok,
  55. template.company_id,
  56. template.store_id,
  57. template.type
  58. '''
  59. query_without_brand = '''
  60. SELECT
  61. product.id,
  62. template.categ_id,
  63. CASE
  64. WHEN product.default_code IS NOT NULL
  65. THEN ('[' || product.default_code || '] ' || product.name_template)
  66. ELSE product.name_template
  67. END AS display_name,
  68. (array_agg(attr_rel.att_id)) AS attr_rel,
  69. (array_agg(attr_value.name)) AS attr_value,
  70. (array_agg(attr.id)) AS attr,
  71. product.product_tmpl_id,
  72. template.list_price,
  73. product.default_code,
  74. product.active,
  75. template.sale_ok,
  76. template.company_id,
  77. template.store_id,
  78. template.type,
  79. product.ean13
  80. FROM product_product AS product
  81. LEFT JOIN product_template AS template
  82. ON template.id = product.product_tmpl_id
  83. LEFT JOIN product_attribute_value_product_product_rel AS attr_rel
  84. ON attr_rel.prod_id = product.id
  85. LEFT JOIN product_attribute_value AS attr_value
  86. ON attr_value.id = attr_rel.att_id
  87. LEFT JOIN product_attribute AS attr
  88. ON attr.id = attr_value.attribute_id
  89. GROUP BY
  90. product.id,
  91. template.categ_id,
  92. product.default_code,
  93. template.list_price,
  94. template.sale_ok,
  95. template.company_id,
  96. template.store_id,
  97. template.type
  98. '''
  99. r.cr.execute(validate_brand)
  100. for j in r.cr.fetchall():
  101. brand = j[0]
  102. if brand == True:
  103. r.cr.execute(query_with_brand,([company_currency_rate]))
  104. return [
  105. {
  106. 'product_id': j[0],
  107. 'categ_id': j[1],
  108. 'product_name': j[2],
  109. 'attribute_value_ids':j[3],
  110. 'attribute_values':j[4],
  111. 'attribute':j[5],
  112. 'product_brand_id':j[6],
  113. 'brand_name': j[7],
  114. 'product_tmpl_id': j[8],
  115. 'list_price': j[9],
  116. 'default_code': j[10],
  117. 'active': j[11],
  118. 'sale_ok': j[12],
  119. 'company_id': j[13],
  120. 'store_id': j[14],
  121. 'type': j[15],
  122. 'ean13': j[16],
  123. } for j in r.cr.fetchall()
  124. ]
  125. else:
  126. r.cr.execute(query_without_brand,([company_currency_rate]))
  127. return [
  128. {
  129. 'product_id': j[0],
  130. 'categ_id': j[1],
  131. 'product_name': j[2],
  132. 'attribute_value_ids':j[3],
  133. 'attribute_values':j[4],
  134. 'attribute':j[5],
  135. 'product_tmpl_id': j[6],
  136. 'list_price': j[7],
  137. 'default_code': j[8],
  138. 'active': j[9],
  139. 'sale_ok': j[10],
  140. 'company_id': j[11],
  141. 'store_id': j[12],
  142. 'type': j[13],
  143. 'ean13': j[14],
  144. } for j in r.cr.fetchall()
  145. ]