存货核算中的后进先出

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   proc CaculateProfit_LIFO


as


/*
    drop table #tbl
    drop table #tbl2
    drop table #tblInput

*/



--后进先出型
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
    


select IODate,inputNum,inputUnitPrice
        into #tblInput
            from #tbl
            where inputNum is not null
                and inputUnitPrice is not null
            order by IODate asc
    


/*
select * from #tbl
select * from #tblInput
*/




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


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


declare @出库剩余量 int;
    set @出库剩余量=0


    /* 找出第一个被减项 ,后进先出的第一个后进项*/
declare @第一个出库日期 datetime
    select     top 1 
        @第一个出库日期=IODate,
        @出库剩余量 = outputNum 
            from dbo.InvertoryAccouter
            where outputNum is not null
                and SalesPrice is not null
                order by IODate asc
    


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


declare @总利润 decimal(12,4)
    set @总利润=0



DECLARE LIFO_cursor cursor
FOR  select IODate,InputNum,
        InputUnitPrice,OutputNum,
        CostUnitPrice,SalesUnitPrice,Profit 
            from #tbl
        
    OPEN LIFO_cursor
    FETCH NEXT FROM LIFO_cursor
    INTO @日期,@入库数量,
            @入库单价,@出库数量,
            @成本单价,@销售单价,@利润
    WHILE @@FETCH_STATUS=0
        BEGIN
            -- @当前累加成本 重新清零
            set @当前累加成本=0

            if @出库数量>0 
                Begin
                    declare @日期零点 datetime
                    --取时间零整点
                    select     @日期零点 = (convert(nvarchar(10),datepart(year,@日期))+'-'
                            +convert(nvarchar(10),datepart(month,@日期))+'-'
                            +convert(nvarchar(10),datepart(day,@日期)))    
                    
                    select top 1 
                        @当前被减项日期 = IODate,
                        @当前被减项数量=inputNum,
                        @当前被减项单价 = inputUnitPrice
                        from #tblInput
                        where InputNum is not null
                            and inputUnitPrice is not null
                            and IODate<@日期零点
                            order by IODate desc


                    SELECT  @出库剩余量 = @出库数量
 
                    --如果出库数量大于0则循环递减
                    While(@出库剩余量>0)
                        Begin
                            IF(@当前被减项数量<@出库剩余量)
                                BEGIN
                                    --减少当前的库存 删除临时表中的库存相应纪录
                                    select @出库剩余量 = @出库剩余量-@当前被减项数量
                                    
                                    select @当前累加成本 = @当前累加成本
                                                +@当前被减项数量*@当前被减项单价
                            
                                    delete from #tblInput  
                                        where IODate = @当前被减项日期
                                            
                                    select top 1 
                                        @当前被减项日期 = IODate,
                                        @当前被减项数量=inputNum,            
                                        @当前被减项单价 = inputUnitPrice
                                        from #tblInput
                                        where InputNum is not null
                                            and inputUnitPrice is not null
                                            and IODate<@当前被减项日期
                                            order by IODate desc
                                    
                                    
                                        
                                    
                                END
                            ELSE
                                BEGIN
                                    update #tblInput 
                                        set inputNum = (inputNum - @出库剩余量)
                                        where IODate = @当前被减项日期
                                    
                                    /*输出当前虚拟表的工作情况    
                                    select * from #tblInput
                                    */
                                    
                                    
                                    select @当前累加成本 = @当前累加成本
                                                +@出库剩余量*@当前被减项单价    
            
                                                
                                    select top 1 
                                        @当前被减项日期 = IODate,
                                        @当前被减项数量=inputNum,            
                                        @当前被减项单价 = inputUnitPrice
                                        from #tblInput
                                        where InputNum is not null
                                            and inputUnitPrice is not null
                                            and IODate<@当前被减项日期

                                        
                                    
                                        
                                    select @出库剩余量=0
                                        
                                    
                                END
                            
                            
                        End
                    
                    --print @当前累加成本/@出库数量    
                    select @成本单价=@当前累加成本/@出库数量
                    select @总利润 = @总利润
                            +(@销售单价-@成本单价)*@出库数量
                End
                    
                    
                    
                    
                        
                
                
            FETCH NEXT FROM LIFO_cursor
            INTO @日期,@入库数量,
                    @入库单价,@出库数量,
                    @成本单价,@销售单价,@利润
        END 
    CLOSE LIFO_cursor
DEALLOCATE LIFO_cursor


select @总利润


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO