GoodERP
Ви не можете вибрати більше 25 тем Теми мають розпочинатися з літери або цифри, можуть містити дефіси (-) і не повинні перевищувати 35 символів.

211 lines
7.6KB

  1. from odoo import models, fields, api
  2. from datetime import datetime, timedelta
  3. class ReportStockTransceive(models.Model):
  4. _name = 'report.stock.transceive'
  5. _description = '商品收发明细表'
  6. _inherit = 'report.base'
  7. goods = fields.Char('商品')
  8. code = fields.Char('编码')
  9. attribute = fields.Char('属性')
  10. id_lists = fields.Text('库存调拨id列表')
  11. uom = fields.Char('单位')
  12. warehouse = fields.Char('仓库')
  13. goods_qty_begain = fields.Float(
  14. '期初数量', digits='Quantity')
  15. cost_begain = fields.Float(
  16. '期初成本', digits='Amount')
  17. goods_qty_end = fields.Float(
  18. '期末数量', digits='Quantity')
  19. cost_end = fields.Float(
  20. '期末成本', digits='Amount')
  21. goods_qty_out = fields.Float(
  22. '出库数量', digits='Quantity')
  23. cost_out = fields.Float(
  24. '出库成本', digits='Amount')
  25. goods_qty_in = fields.Float(
  26. '入库数量', digits='Quantity')
  27. cost_in = fields.Float(
  28. '入库成本', digits='Amount')
  29. def select_sql(self, sql_type='out'):
  30. return '''
  31. SELECT min(line.id) as id,
  32. goods.name as goods,
  33. goods.code as code,
  34. att.name as attribute,
  35. array_agg(line.id) as id_lists,
  36. uom.name as uom,
  37. wh.name as warehouse,
  38. sum(case when
  39. line.date < '{date_start}' THEN line.goods_qty ELSE 0 END)
  40. as goods_qty_begain,
  41. sum(case when
  42. line.date < '{date_start}' THEN line.cost ELSE 0 END)
  43. as cost_begain,
  44. sum(case when
  45. line.date <= '{date_end}' THEN line.goods_qty ELSE 0 END)
  46. as goods_qty_end,
  47. sum(case when
  48. line.date <= '{date_end}' THEN line.cost ELSE 0 END)
  49. as cost_end,
  50. sum(case when
  51. line.date <= '{date_end}' AND line.date >= '{date_start}'
  52. THEN
  53. line.goods_qty ELSE 0 END)
  54. as goods_qty,
  55. sum(case when
  56. line.date <= '{date_end}' AND line.date >= '{date_start}'
  57. THEN
  58. line.cost ELSE 0 END)
  59. as cost
  60. '''
  61. def from_sql(self, sql_type='out'):
  62. return '''
  63. FROM wh_move_line line
  64. LEFT JOIN goods goods ON line.goods_id = goods.id
  65. LEFT JOIN attribute att ON line.attribute_id = att.id
  66. LEFT JOIN uom uom ON line.uom_id = uom.id
  67. LEFT JOIN warehouse wh ON line.%s = wh.id
  68. ''' % (sql_type == 'out' and 'warehouse_id' or 'warehouse_dest_id')
  69. def where_sql(self, sql_type='out'):
  70. extra = ''
  71. if self.env.context.get('warehouse_id'):
  72. extra += 'AND wh.id = {warehouse_id}'
  73. if self.env.context.get('goods_id'):
  74. extra += 'AND goods.id = {goods_id}'
  75. return '''
  76. WHERE line.state = 'done'
  77. AND wh.type = 'stock'
  78. AND wh.active = true
  79. AND line.date <= '{date_end}'
  80. %s
  81. ''' % extra
  82. def group_sql(self, sql_type='out'):
  83. return '''
  84. GROUP BY goods.id, goods.code, att.name, uom.name, wh.name
  85. '''
  86. def order_sql(self, sql_type='out'):
  87. return '''
  88. ORDER BY goods.id, wh.name
  89. '''
  90. def get_context(self, sql_type='out', context=None):
  91. return {
  92. 'date_start': context.get('date_start') or '',
  93. 'date_end': context.get('date_end'),
  94. 'warehouse_id': context.get('warehouse_id') and context.get('warehouse_id')[0] or '',
  95. 'goods_id': context.get('goods_id') and context.get('goods_id')[0] or '',
  96. }
  97. def get_record_key(self, record, sql_type='out'):
  98. return (
  99. record.get('goods'),
  100. record.get('uom'),
  101. record.get('warehouse'),
  102. record.get('attribute'),
  103. record.get('code')
  104. )
  105. def unzip_record_key(self, key):
  106. return {
  107. 'goods': key[0],
  108. 'uom': key[1],
  109. 'warehouse': key[2],
  110. 'attribute': key[3],
  111. 'code': key[4],
  112. }
  113. def get_default_value_by_record(self, record, sql_type='out'):
  114. return {
  115. 'id': record.get('id'),
  116. }
  117. def update_record_value(self, value, record, sql_type='out'):
  118. tag = sql_type == 'out' and -1 or 1
  119. value.update({
  120. 'goods_qty_begain': value.get('goods_qty_begain', 0) +
  121. (tag * record.get('goods_qty_begain', 0)),
  122. 'cost_begain': value.get('cost_begain', 0) +
  123. (tag * record.get('cost_begain', 0)),
  124. 'goods_qty_end': value.get('goods_qty_end', 0) +
  125. (tag * record.get('goods_qty_end', 0)),
  126. 'cost_end': value.get('cost_end', 0) +
  127. (tag * record.get('cost_end', 0)),
  128. 'goods_qty_out': value.get('goods_qty_out', 0) +
  129. (sql_type == 'out' and record.get('goods_qty', 0) or 0),
  130. 'cost_out': value.get('cost_out', 0) +
  131. (sql_type == 'out' and record.get('cost', 0) or 0),
  132. 'goods_qty_in': value.get('goods_qty_in', 0) +
  133. (sql_type == 'in' and record.get('goods_qty', 0) or 0),
  134. 'cost_in': value.get('cost_in', 0) +
  135. (sql_type == 'in' and record.get('cost', 0) or 0),
  136. 'id_lists': value.get('id_lists', []) + record.get('id_lists', []),
  137. })
  138. def compute_history_stock_by_collect(self, res, records, sql_type='out'):
  139. for record in records:
  140. record_key = self.get_record_key(record, sql_type=sql_type)
  141. if not res.get(record_key):
  142. res[record_key] = self.get_default_value_by_record(
  143. record, sql_type=sql_type)
  144. self.update_record_value(
  145. res[record_key], record, sql_type=sql_type)
  146. def collect_data_by_sql(self, sql_type='out'):
  147. out_collection = self.execute_sql(sql_type='out')
  148. in_collection = self.execute_sql(sql_type='in')
  149. res = {}
  150. self.compute_history_stock_by_collect(
  151. res, in_collection, sql_type='in')
  152. self.compute_history_stock_by_collect(
  153. res, out_collection, sql_type='out')
  154. result = []
  155. for key, value in res.items():
  156. value.update(self.unzip_record_key(key))
  157. result.append(value)
  158. return result
  159. def find_source_move_line(self):
  160. # 查看库存调拨明细
  161. move_line_ids = []
  162. # 获得'report.stock.transceive'记录集
  163. move_line_lists = self.get_data_from_cache(sql_type='out')
  164. date_start = self.env.context.get('date_start')
  165. date_end = self.env.context.get('date_end')
  166. for line in move_line_lists:
  167. if line.get('id') == self.id:
  168. domain_dict = [('date', '>=', date_start),
  169. ('date', '<=', date_end),
  170. ('id', 'in', line.get('id_lists'))
  171. ]
  172. move_line_ids = [line.id for line in self.env['wh.move.line'].search(domain_dict)]
  173. view = self.env.ref('warehouse.wh_move_line_list')
  174. return {
  175. 'name': ('库存调拨' + str(date_start) +
  176. '~' + str(date_end) +
  177. '~' + line['goods'] ),
  178. 'view_mode': 'list',
  179. 'views': [(view.id, 'list')],
  180. 'res_model': 'wh.move.line',
  181. 'type': 'ir.actions.act_window',
  182. 'domain': [('id', 'in', move_line_ids)]
  183. }
上海开阖软件有限公司 沪ICP备12045867号-1