good/warehouse/report/stock_receipt_delivery.py

from odoo import models, fields, api
from datetime import datetime, timedelta


class ReportStockReceiptDelivery(models.Model):
    _name = 'report.stock.receipt.delivery'
    _description = '商品收发汇总表'
    _inherit = 'report.base'

    goods_class = fields.Char('商品类别')
    code = fields.Char('商品编号')
    goods = fields.Char('商品名称')
    uom = fields.Char('单位')
    attribute = fields.Char('属性')
    id_lists = fields.Text('库存调拨id列表')
    warehouse = fields.Char('仓库')
    origin = fields.Char('业务类型')
    goods_qty_begin = fields.Float(
        '期初数量', digits='Quantity')
    cost_begin = fields.Float(
        '期初成本', digits='Amount')
    goods_qty_end = fields.Float(
        '期末数量', digits='Quantity')
    cost_end = fields.Float(
        '期末成本', digits='Amount')
    goods_qty_out = fields.Float(
        '出库数量', digits='Quantity')
    cost_out = fields.Float(
        '出库成本', digits='Amount')
    goods_qty_in = fields.Float(
        '入库数量', digits='Quantity')
    cost_in = fields.Float(
        '入库成本', digits='Amount')
    # 入库数据
    goods_buy_receipt = fields.Float(
        '采购入库', digits='Quantity')
    cost_buy_receipt = fields.Float(
        '采购成本', digits='Amount')
    goods_in_others = fields.Float(
        '其他入库', digits='Quantity')
    cost_in_others = fields.Float(
        '其他成本', digits='Amount')
    goods_in_inventory = fields.Float(
        '盘盈入库', digits='Quantity')
    cost_in_inventory = fields.Float(
        '盘盈成本', digits='Amount')
    goods_delivery_return = fields.Float(
        '销售退货', digits='Quantity')
    cost_delivery_return = fields.Float(
        '退货成本', digits='Amount')
    goods_assembly_in = fields.Float(
        '组装入库', digits='Quantity')
    cost_assembly_in = fields.Float(
        '组装入库成本', digits='Amount')
    goods_internal_in = fields.Float(
        '调拨入库', digits='Quantity')
    cost_internal_in = fields.Float(
        '调拨入库成本', digits='Amount')
    # 出库数据
    goods_delivery_sell = fields.Float(
        '销售出库', digits='Quantity')
    cost_delivery_sell = fields.Float(
        '出库成本', digits='Amount')
    goods_out_others = fields.Float(
        '其他出库', digits='Quantity')
    cost_out_others = fields.Float(
        '其他成本', digits='Amount')
    goods_out_inventory = fields.Float(
        '盘亏出库', digits='Quantity')
    cost_out_inventory = fields.Float(
        '盘亏成本', digits='Amount')
    goods_receipt_return = fields.Float(
        '采购退货', digits='Quantity')
    cost_receipt_return = fields.Float(
        '退货成本', digits='Amount')
    goods_assembly_out = fields.Float(
        '组装出库', digits='Quantity')
    cost_assembly_out = fields.Float(
        '组装出库成本', digits='Amount')
    goods_internal_out = fields.Float(
        '调拨出库', digits='Quantity')
    cost_internal_out = fields.Float(
        '调拨出库成本', digits='Amount')

    def select_sql_inherit(self, sql_type='out', sql_origin=''):
        return '''
        SELECT min(line.id) as id,
            goods.name as goods,
            gc.name as goods_class,
            goods.code as code,
            att.name as attribute,
            array_agg(line.id) as id_lists,
            uom.name as uom,
            wh.name as warehouse,
            case 
                when wm.origin = 'buy.receipt.buy' then '采购入库'
                when wm.origin = 'wh.in.others' then '其他入库'                
                when wm.origin = 'wh.in.inventory' then '盘盈'
                when wm.origin = 'sell.delivery.return' then '销售退货'
                
                when wm.origin = 'sell.delivery.sell' then '销售出库'
                when wm.origin = 'wh.out.others' then '其他出库'
                when wm.origin = 'wh.out.inventory' then '盘亏'                
                when wm.origin = 'buy.receipt.return' then '采购退货'                
                
                when wm.origin = 'wh.internal' then '%s'
                when wm.origin = 'wh.assembly' and line.type = 'out' then '组装单子件'
                when wm.origin = 'wh.assembly' and line.type = 'in' then '组装单组合件'
                when wm.origin = 'outsource' and line.type = 'out' then '委外单子件'
                when wm.origin = 'outsource' and line.type = 'in' then '委外单组合件'
            end as origin,
            att.name as attribute,
            array_agg(line.id order by line.id) as id_lists,
            wh.name as warehouse,
            sum(case when
                    line.date < '{date_start}' THEN line.goods_qty ELSE 0 END)
                    as goods_qty_begin,
                sum(case when
                    line.date < '{date_start}' THEN line.cost ELSE 0 END)
                    as cost_begin,
                sum(case when
                    line.date <= '{date_end}' THEN line.goods_qty ELSE 0 END)
                    as goods_qty_end,
                sum(case when
                    line.date <= '{date_end}' THEN line.cost ELSE 0 END)
                    as cost_end,
                sum(case when
                    line.date <= '{date_end}' AND line.date >= '{date_start}'
                  THEN
                    line.goods_qty ELSE 0 END)
                    as goods_qty,
                sum(case when
                    line.date <= '{date_end}' AND line.date >= '{date_start}'
                  THEN
                    line.cost ELSE 0 END)
                    as cost
        ''' % ('调拨出库' if sql_origin == 'internal_in' else '调拨入库')

    def from_sql_inherit(self, sql_type='out', sql_origin=''):
        return '''
        FROM wh_move_line line
        inner join wh_move wm on wm.id = line.move_id
        inner join goods goods ON line.goods_id = goods.id
        left join goods_class gc on gc.id = goods.goods_class_id 
        LEFT JOIN attribute att ON line.attribute_id = att.id
        LEFT JOIN uom uom ON line.uom_id = uom.id
        LEFT JOIN warehouse wh ON line.%s = wh.id
        ''' % ((sql_type == 'out' or (sql_type == 'internal' and sql_origin == 'internal_in')) and 'warehouse_id'
               or 'warehouse_dest_id')

    def where_sql_inherit(self, sql_type='out', sql_origin=''):
        extra = ''
        if self.env.context.get('warehouse_id'):
            extra += 'AND wh.id = {warehouse_id}'
        if self.env.context.get('goods_id'):
            extra += 'AND goods.id = {goods_id}'
        return '''
        WHERE line.state = 'done'
          AND wh.type = 'stock'
          AND wh.active = true
          AND line.date <= '{date_end}'
          %s
        ''' % extra

    def group_sql_inherit(self, sql_type='out', sql_origin=''):
        return '''
        GROUP BY gc.name, goods.id, wm.origin, line.type, goods.code, 
        att.name, uom.name, wh.name, line.warehouse_id, line.type
        '''

    def order_sql_inherit(self, sql_type='out', sql_origin=''):
        return '''
        ORDER BY goods.id, wh.name
        '''

    def get_context_inherit(self, sql_type='out', context=None):
        return {
            'date_start': context.get('date_start') or '',
            'date_end': context.get('date_end'),
            'warehouse_id': context.get('warehouse_id') and context.get('warehouse_id')[0] or '',
            'goods_id': context.get('goods_id') and context.get('goods_id')[0] or '',
        }

    def get_record_key(self, record, sql_type='out'):
        return (
            record.get('goods'),
            record.get('uom'),
            record.get('warehouse'),
            record.get('attribute'),
            record.get('code'),
            record.get('goods_class')
        )

    def unzip_record_key(self, key):
        return {
            'goods': key[0],
            'uom': key[1],
            'warehouse': key[2],
            'attribute': key[3],
            'code': key[4],
            'goods_class': key[5]
        }

    def get_default_value_by_record(self, record, sql_type='out', sql_origin=''):
        return {
            'id': record.get('id'),
        }

    def update_record_value(self, value, record, sql_type='out', sql_origin=''):
        tag = sql_type == 'out' and -1 or 1

        value.update({
            # 调拨入库
            'goods_internal_in': value.get('goods_internal_in', 0) + (
                    sql_type == 'internal' and sql_origin == '调拨入库' and record.get('goods_qty', 0) or 0),
            'cost_internal_in': value.get('cost_internal_in', 0) + (
                    sql_type == 'internal' and sql_origin == '调拨入库' and record.get('cost', 0) or 0),
            # 调拨出库
            'goods_internal_out': value.get('goods_internal_out', 0) + (
                    sql_type == 'internal' and sql_origin == '调拨出库' and record.get('goods_qty', 0) or 0),
            'cost_internal_out': value.get('cost_internal_out', 0) + (
                    sql_type == 'internal' and sql_origin == '调拨出库' and record.get('cost', 0) or 0),

            # =============================期初数量=============================
            'goods_qty_begin': value.get('goods_qty_begin', 0) + (
                    sql_type != 'internal' and tag * record.get('goods_qty_begin', 0)),
            'cost_begin': value.get('cost_begin', 0) + (
                    sql_type != 'internal' and tag * record.get('cost_begin', 0)),
            'goods_qty_end': value.get('goods_qty_end', 0) + (
                    sql_type != 'internal' and tag * record.get('goods_qty_end', 0)),
            'cost_end': value.get('cost_end', 0) + (
                    sql_type != 'internal' and tag * record.get('cost_end', 0)),
            # =============================一批入库数据=============================
            # 采购入库
            'goods_buy_receipt': value.get('goods_buy_receipt', 0) +
                    (sql_type == 'in' and sql_origin == '采购入库' and record.get('goods_qty', 0) or 0),
            'cost_buy_receipt': value.get('cost_buy_receipt', 0) +
                                (sql_type == 'in' and sql_origin == '采购入库' and record.get('cost', 0) or 0),
            # 其他入库
            'goods_in_others': value.get('goods_in_others', 0) +
                               (sql_type == 'in' and sql_origin == '其他入库' and record.get('goods_qty', 0) or 0),
            'cost_in_others': value.get('cost_in_others', 0) +
                              (sql_type == 'in' and sql_origin == '其他入库' and record.get('cost', 0) or 0),
            # 盘盈入库
            'goods_in_inventory': value.get('goods_in_inventory', 0) +
                                  (sql_type == 'in' and sql_origin == '盘盈' and record.get('goods_qty', 0) or 0),
            'cost_in_inventory': value.get('cost_in_inventory', 0) +
                                 (sql_type == 'in' and sql_origin == '盘盈' and record.get('cost', 0) or 0),
            # 销售退货
            'goods_delivery_return': value.get('goods_delivery_return', 0) +
                                  (sql_type == 'in' and sql_origin == '销售退货' and record.get('goods_qty', 0) or 0),
            'cost_delivery_return': value.get('cost_delivery_return', 0) +
                                 (sql_type == 'in' and sql_origin == '销售退货' and record.get('cost', 0) or 0),
            # 组装单组合件(组装入库)
            'goods_assembly_in': value.get('goods_assembly_in', 0) +
                                     (sql_type == 'in' and sql_origin == '组装单组合件' and record.get('goods_qty',
                                                                                                   0) or 0),
            'cost_assembly_in': value.get('cost_assembly_in', 0) +
                                    (sql_type == 'in' and sql_origin == '组装单组合件' and record.get('cost', 0) or 0),
            # =============================一批出库数据=============================
            # 销售出库
            'goods_delivery_sell': value.get('goods_delivery_sell', 0) +
                                 (sql_type == 'out' and sql_origin == '销售出库' and record.get('goods_qty', 0) or 0),
            'cost_delivery_sell': value.get('cost_delivery_sell', 0) +
                                (sql_type == 'out' and sql_origin == '销售出库' and record.get('cost', 0) or 0),
            # 其他出库
            'goods_out_others': value.get('goods_out_others', 0) +
                               (sql_type == 'out' and sql_origin == '其他出库' and record.get('goods_qty', 0) or 0),
            'cost_out_others': value.get('cost_out_others', 0) +
                              (sql_type == 'out' and sql_origin == '其他出库' and record.get('cost', 0) or 0),
            # 盘亏出库
            'goods_out_inventory': value.get('goods_out_inventory', 0) +
                                  (sql_type == 'out' and sql_origin == '盘亏' and record.get('goods_qty', 0) or 0),
            'cost_out_inventory': value.get('cost_out_inventory', 0) +
                                 (sql_type == 'out' and sql_origin == '盘亏' and record.get('cost', 0) or 0),
            # 采购退货
            'goods_receipt_return': value.get('goods_receipt_return', 0) +
                                     (sql_type == 'out' and sql_origin == '采购退货' and record.get('goods_qty',
                                                                                                   0) or 0),
            'cost_receipt_return': value.get('cost_receipt_return', 0) +
                                    (sql_type == 'out' and sql_origin == '采购退货' and record.get('cost', 0) or 0),
            # 组装单子件(组装出库)
            'goods_assembly_out': value.get('goods_assembly_out', 0) +
                                 (sql_type == 'out' and sql_origin == '组装单子件' and record.get('goods_qty',
                                                                                                   0) or 0),
            'cost_assembly_out': value.get('cost_assembly_out', 0) +
                                (sql_type == 'out' and sql_origin == '组装单子件' and record.get('cost', 0) or 0),
            # =============================期末数量=============================
            'goods_qty_in': value.get('goods_qty_in', 0) + (sql_type == 'in' and record.get('goods_qty', 0) or 0),
            'cost_in': value.get('cost_in', 0) + (sql_type == 'in' and record.get('cost', 0) or 0),
            'goods_qty_out': value.get('goods_qty_out', 0) + (sql_type == 'out' and record.get('goods_qty', 0) or 0),
            'cost_out': value.get('cost_out', 0) + (sql_type == 'out' and record.get('cost', 0) or 0),

            'id_lists': value.get('id_lists', []) + record.get('id_lists', []),
        })

    def compute_history_stock_by_collect(self, res, records, sql_type='out', sql_origin=''):
        for record in records:
            record_key = self.get_record_key(record, sql_type=sql_type)
            if not res.get(record_key):
                res[record_key] = self.get_default_value_by_record(
                    record, sql_type=sql_type, sql_origin=record['origin'])

            self.update_record_value(
                res[record_key], record, sql_type=sql_type, sql_origin=record['origin'])

    def collect_data_by_sql(self, sql_type='out', sql_origin=''):
        out_collection = self.execute_sql_inherit(sql_type='out', sql_origin='')
        in_collection = self.execute_sql_inherit(sql_type='in', sql_origin='')
        internal_in_collection = self.execute_sql_inherit(sql_type='internal', sql_origin='internal_in')
        internal_out_collection = self.execute_sql_inherit(sql_type='internal', sql_origin='internal_out')

        res = {}
        self.compute_history_stock_by_collect(res, out_collection, sql_type='out')
        self.compute_history_stock_by_collect(res, in_collection, sql_type='in')
        self.compute_history_stock_by_collect(res, internal_out_collection, sql_type='internal')
        self.compute_history_stock_by_collect(res, internal_in_collection, sql_type='internal')

        result = []
        for key, value in res.items():
            value.update(self.unzip_record_key(key))
            result.append(value)
        return result

    def find_source_move_line(self):
        # 查看库存调拨明细
        move_line_ids = []
        # 获得'report.stock.receipt.delivery'记录集
        move_line_lists = self.get_data_from_cache(sql_type='out')

        date_start = self.env.context.get('date_start')
        date_end = self.env.context.get('date_end')
        for line in move_line_lists:
            if line.get('id') == self.id:
                domain_dict = [('date', '>=', date_start),
                               ('date', '<=', date_end),
                               ('id', 'in', line.get('id_lists'))
                               ]
                move_line_ids = [line.id for line in self.env['wh.move.line'].search(domain_dict)]

        view = self.env.ref('warehouse.wh_move_line_list')
        return {
            'name': ('库存调拨' + str(date_start) +
                      '~' + str(date_end) + 
                      '~' + line['goods']),
            'view_mode': 'list',
            'views': [(view.id, 'list')],
            'res_model': 'wh.move.line',
            'type': 'ir.actions.act_window',
            'domain': [('id', 'in', move_line_ids)]
        }