存货核算中的先进先出

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   proc CaculateProfit_FIFO



as
    
/*

drop table #tbl
drop table #tbl2

*/
    

declare     @期初时间 datetime,
        @期初数量 int,
        @期初单价 decimal(12,4)
        
        set @期初时间='2001-01-31 23:59:59'
        set @期初数量=20
        set @期初单价=200.00


create table #tbl(IODate  datetime default(getdate()),
            InputNum int,
            InputUnitPrice decimal(12,4),
            
            OutputNum int,
            CostUnitPrice decimal(12,4),
            SalesUnitPrice decimal(12,4),
            Profit decimal(12,4))


select IODate,
    inputNum,
    inputPrice,
    outputNum,
    SalesPrice
    into #tbl2
        from dbo.InvertoryAccouter
        order by IODate asc




insert into #tbl
    (IODate,InputNum,InputUnitPrice,
    OutputNum,SalesUnitPrice)

select  @期初时间,
    @期初数量,
    @期初单价,
    null,
    null
    
    union all
select * 
    from #tbl2
    

    


declare @日期 datetime,
    @入库数量 int,
    @入库单价 decimal(12,4),
    @出库数量 int,
    @成本单价 decimal(12,4),
    @销售单价 decimal(12,4),
    @利润    decimal(12,4)


declare @当前被减项日期 datetime,
    @当前被减项数量 int,
    @当前被减项剩余数量 int,
    @当前被减项单价 decimal(12,4)

    set @当前被减项日期=@期初时间
    set @当前被减项数量=@期初数量
    set @当前被减项剩余数量=@期初数量
    set @当前被减项单价= @期初单价

declare @当前累加成本 decimal(12,4)


declare @总利润 decimal(12,4)
    set @总利润=0
    
DECLARE FIFO_cursor cursor
For select IODate,InputNum,
        InputUnitPrice,OutputNum,
        CostUnitPrice,SalesUnitPrice,Profit 
            from #tbl

    OPEN FIFO_cursor
    FETCH NEXT from FIFO_cursor
        INTO @日期,@入库数量,
            @入库单价,@出库数量,
            @成本单价,@销售单价,@利润

        WHILE @@FETCH_STATUS=0
            Begin
                        
                -- @当前累加成本 重新清零
                set @当前累加成本=0
    
                --    成本单价 = 成本/数量
                if @出库数量>0 
                    Begin
                        if @当前被减项剩余数量<@出库数量
                            Begin
                                while(@当前被减项剩余数量<@出库数量)
                                    begin
                                        --先统计当前项的累加成本准备和下个选项减出来的成本相加
                                        select @当前累加成本=@当前被减项剩余数量*@当前被减项单价
                                        
                                        --移动到下一条入库纪录
                                        select top 1 
                                         @当前被减项日期 = IODate,
                                         @当前被减项数量=inputNum,
                                         @当前被减项剩余数量=@当前被减项剩余数量+inputNum,
                                         @当前被减项单价 = inputPrice
                                            from dbo.InvertoryAccouter
                                                where IODate>@当前被减项日期
                                                    and inputNum is not null
                                                    and inputPrice is not null
                                                order by IODate asc
                                        --select @当前被减项剩余数量= @当前被减项剩余数量-@出库数量
                                                            
                                    end
                                --一直循环累加直至发现现在的 @当前被减项剩余数量 已经大于 @出库数量
                                select @当前被减项剩余数量=@当前被减项剩余数量-@出库数量
            
                                select @当前累加成本 = @当前累加成本+
                                            (@当前被减项数量-@当前被减项剩余数量)*@当前被减项单价
                                
                                --计算出 出库的 成本单价
                                select @成本单价 = @当前累加成本/@出库数量
                                
                                --print (@销售单价-@成本单价)*@出库数量
                                
                                select @总利润 = @总利润 + (@销售单价-@成本单价)*@出库数量
                                --print @总利润
                            End
                        
                        
                        else
                            --如果 @出库数量<@当前被减项剩余数量
                            Begin
                                
                                select @当前被减项剩余数量=@当前被减项剩余数量-@出库数量
                                /*
                                select @当前累加成本 = @当前被减项单价*@出库数量
                                --计算出 出库的 成本单价
                                select @成本单价 = @当前累加成本/@出库数量
                                */
                                select @成本单价 = @当前被减项单价
        
                                --print (@销售单价-@成本单价)*@出库数量    
                                
                                select @总利润 = @总利润 + (@销售单价-@成本单价)*@出库数量    
                                --print @总利润        
                            End
                        
                        
                    End 
                    
                
                --print @日期
                
                
                FETCH NEXT from FIFO_cursor
                    INTO @日期,@入库数量,
                        @入库单价,@出库数量,
                        @成本单价,@销售单价,@利润
            End
            
    
    CLOSE FIFO_cursor
DEALLOCATE FIFO_cursor        

select  @总利润



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO