存货核算中的后进先出

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