GoodERP
Вы не можете выбрать более 25 тем Темы должны начинаться с буквы или цифры, могут содержать дефисы(-) и должны содержать не более 35 символов.

76 lines
2.4KB

  1. from odoo import fields, models, api
  2. import datetime
  3. class SellTopTen(models.Model):
  4. _name = 'sell.top.ten'
  5. _inherit = 'report.base'
  6. _description = '销量前十商品'
  7. goods = fields.Char('商品名称')
  8. warehouse = fields.Char('仓库')
  9. qty = fields.Float('基本数量', digits='Quantity')
  10. amount = fields.Float('销售收入', digits='Amount')
  11. def select_sql(self, sql_type='out'):
  12. return '''
  13. SELECT MIN(wml.id) as id,
  14. goods.name AS goods,
  15. wh.name AS warehouse,
  16. (SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
  17. ELSE 0 END) -
  18. SUM(CASE WHEN wm.origin = 'sell.delivery.return' THEN wml.goods_qty
  19. ELSE 0 END)) AS qty,
  20. (SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.amount
  21. ELSE 0 END) -
  22. SUM(CASE WHEN wm.origin = 'sell.delivery.return' THEN wml.amount
  23. ELSE 0 END)) AS amount
  24. '''
  25. def from_sql(self, sql_type='out'):
  26. return '''
  27. FROM wh_move_line AS wml
  28. LEFT JOIN wh_move wm ON wml.move_id = wm.id
  29. LEFT JOIN goods ON wml.goods_id = goods.id
  30. LEFT JOIN warehouse AS wh ON wml.warehouse_id = wh.id
  31. '''
  32. def where_sql(self, sql_type='out'):
  33. extra = ''
  34. if self.env.context.get('warehouse_id'):
  35. extra += 'AND wh.id = {warehouse_id}'
  36. return '''
  37. WHERE wml.state = 'done'
  38. AND wml.date >= '{date_start}'
  39. AND wml.date <= '{date_end}'
  40. AND wm.origin like 'sell.delivery%%'
  41. AND wh.type = 'stock'
  42. %s
  43. ''' % extra
  44. def group_sql(self, sql_type='out'):
  45. return '''
  46. GROUP BY goods, warehouse
  47. '''
  48. def order_sql(self, sql_type='out'):
  49. return '''
  50. ORDER BY qty DESC
  51. fetch first 10 rows only
  52. '''
  53. def get_context(self, sql_type='out', context=None):
  54. return {
  55. 'date_start': context.get('date_start') or '',
  56. 'date_end': context.get('date_end'),
  57. 'warehouse_id': context.get('warehouse_id') and
  58. context.get('warehouse_id')[0] or '',
  59. }
  60. def collect_data_by_sql(self, sql_type='out'):
  61. collection = self.execute_sql(sql_type='out')
  62. return collection
上海开阖软件有限公司 沪ICP备12045867号-1