GoodERP
Du kan inte välja fler än 25 ämnen Ämnen måste starta med en bokstav eller siffra, kan innehålla bindestreck ('-') och vara max 35 tecken långa.

160 lines
6.3KB

  1. from odoo import fields, models, api
  2. import datetime
  3. class SellSummaryPartner(models.Model):
  4. _name = 'sell.summary.partner'
  5. _inherit = 'report.base'
  6. _description = '销售汇总表(按客户)'
  7. id_lists = fields.Text('移动明细行id列表')
  8. c_category = fields.Char('客户类别')
  9. partner = fields.Char('客户')
  10. goods_code = fields.Char('商品编码')
  11. goods = fields.Char('商品名称')
  12. attribute = fields.Char('属性')
  13. warehouse = fields.Char('仓库')
  14. qty_uos = fields.Float('辅助数量', digits='Quantity')
  15. uos = fields.Char('辅助单位')
  16. qty = fields.Float('基本数量', digits='Quantity')
  17. uom = fields.Char('基本单位')
  18. price = fields.Float('单价', digits='Price')
  19. amount = fields.Float('销售收入', digits='Amount')
  20. tax_amount = fields.Float('税额', digits='Amount')
  21. subtotal = fields.Float('价税合计', digits='Amount')
  22. margin = fields.Float('毛利', digits='Amount')
  23. def select_sql(self, sql_type='out'):
  24. return '''
  25. SELECT MIN(wml.id) as id,
  26. array_agg(wml.id) AS id_lists,
  27. c_categ.name AS c_category,
  28. partner.name AS partner,
  29. goods.code AS goods_code,
  30. goods.name AS goods,
  31. attr.name AS attribute,
  32. wh.name AS warehouse,
  33. SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_uos_qty
  34. ELSE - wml.goods_uos_qty END) AS qty_uos,
  35. uos.name AS uos,
  36. SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
  37. ELSE - wml.goods_qty END) AS qty,
  38. uom.name AS uom,
  39. (CASE WHEN SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
  40. ELSE - wml.goods_qty END) = 0 THEN 0
  41. ELSE
  42. SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.amount
  43. ELSE - wml.amount END)
  44. / SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
  45. ELSE - wml.goods_qty END)
  46. END) AS price,
  47. SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.amount
  48. ELSE - wml.amount END) AS amount,
  49. SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.tax_amount
  50. ELSE - wml.tax_amount END) AS tax_amount,
  51. SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.subtotal
  52. ELSE - wml.subtotal END) AS subtotal,
  53. (SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.amount
  54. ELSE - wml.amount END) - SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
  55. ELSE - wml.goods_qty END) * wml.cost_unit) AS margin
  56. '''
  57. def from_sql(self, sql_type='out'):
  58. return '''
  59. FROM wh_move_line AS wml
  60. LEFT JOIN wh_move wm ON wml.move_id = wm.id
  61. LEFT JOIN partner ON wm.partner_id = partner.id
  62. LEFT JOIN core_category AS c_categ
  63. ON partner.c_category_id = c_categ.id
  64. LEFT JOIN goods ON wml.goods_id = goods.id
  65. LEFT JOIN attribute AS attr ON wml.attribute_id = attr.id
  66. LEFT JOIN warehouse AS wh ON wml.warehouse_id = wh.id
  67. OR wml.warehouse_dest_id = wh.id
  68. LEFT JOIN uom AS uos ON goods.uos_id = uos.id
  69. LEFT JOIN uom ON goods.uom_id = uom.id
  70. '''
  71. def where_sql(self, sql_type='out'):
  72. extra = ''
  73. if self.env.context.get('partner_id'):
  74. extra += 'AND partner.id = {partner_id}'
  75. if self.env.context.get('goods_id'):
  76. extra += 'AND goods.id = {goods_id}'
  77. if self.env.context.get('c_category_id'):
  78. extra += 'AND c_categ.id = {c_category_id}'
  79. if self.env.context.get('warehouse_id'):
  80. extra += 'AND wh.id = {warehouse_id}'
  81. return '''
  82. WHERE wml.state = 'done'
  83. AND wml.date >= '{date_start}'
  84. AND wml.date <= '{date_end}'
  85. AND wm.origin like 'sell.delivery%%'
  86. AND wh.type = 'stock'
  87. %s
  88. ''' % extra
  89. def group_sql(self, sql_type='out'):
  90. return '''
  91. GROUP BY c_category,partner,goods_code,goods,attribute,warehouse,uos,uom,wml.cost_unit
  92. '''
  93. def order_sql(self, sql_type='out'):
  94. return '''
  95. ORDER BY c_category,partner,goods_code,attribute,warehouse
  96. '''
  97. def get_context(self, sql_type='out', context=None):
  98. return {
  99. 'date_start': context.get('date_start') or '',
  100. 'date_end': context.get('date_end'),
  101. 'partner_id': context.get('partner_id') and
  102. context.get('partner_id')[0] or '',
  103. 'goods_id': context.get('goods_id') and
  104. context.get('goods_id')[0] or '',
  105. 'c_category_id': context.get('c_category_id') and
  106. context.get('c_category_id')[0] or '',
  107. 'warehouse_id': context.get('warehouse_id') and
  108. context.get('warehouse_id')[0] or '',
  109. }
  110. def _compute_order(self, result, order):
  111. order = order or 'partner ASC'
  112. return super(SellSummaryPartner, self)._compute_order(result, order)
  113. def collect_data_by_sql(self, sql_type='out'):
  114. collection = self.execute_sql(sql_type='out')
  115. return collection
  116. def view_detail(self):
  117. '''销售汇总表(按客户)查看明细按钮'''
  118. self.ensure_one()
  119. line_ids = []
  120. res = []
  121. move_lines = []
  122. result = self.get_data_from_cache()
  123. for line in result:
  124. if line.get('id') == self.id:
  125. line_ids = line.get('id_lists')
  126. move_lines = self.env['wh.move.line'].search(
  127. [('id', 'in', line_ids)])
  128. for move_line in move_lines:
  129. details = self.env['sell.order.detail'].search(
  130. [('order_name', '=', move_line.move_id.name),
  131. ('goods_id', '=', move_line.goods_id.id)])
  132. for detail in details:
  133. res.append(detail.id)
  134. return {
  135. 'name': '销售明细表',
  136. 'view_mode': 'list',
  137. 'view_id': False,
  138. 'res_model': 'sell.order.detail',
  139. 'type': 'ir.actions.act_window',
  140. 'domain': [('id', 'in', res)],
  141. }
上海开阖软件有限公司 沪ICP备12045867号-1