8,497
社区成员
发帖
与我相关
我的任务
分享
Create proc pr_StockInOutStat
@stockKindId nvarchar(50),
@wareId nvarchar(50),
@goodsName nvarchar(50),
@pageSize int,
@pageIndex int,
@pageCount int output
AS
declare @lastCheckTime datetime
declare @lastCheckId uniqueidentifier
select top 1 @lastCheckTime=盘点日期,@lastCheckId=编号 from w仓库盘点记录
order by 盘点日期 desc
if object_id('tempdb..#StockList') is not null
drop table #StockList
create table #StockList
(
_id int identity,
_goodsInfo nvarchar(500),
_lastStockAmount int default 0,
_buy int default 0,
_profit int default 0,
_moveIn int default 0,
_outMoveIn int default 0,
_return int default 0,
_sale int default 0,
_loss int default 0,
_lose int default 0,
_moveOut int default 0,
_outMoveOut int default 0,
_curStockAmount int default 0,
_stockId uniqueidentifier,
_stockKind uniqueidentifier,
_warehouseId uniqueidentifier
)
insert into #StockList(_goodsInfo,_lastStockAmount,_stockId,_stockKind,_warehouseId)
select a.品名 + '(' + a.规格 + ' ' + a.生产厂家 + ' ' + a.批号 + ' ' + convert(varchar(10),a.有效期,121) + ' ' + cast(a.成本单价 as varchar(20)) + ')' ,
a.帐面数量,a.物品库存编号, b.库存类别编号,b.库房编号
from w仓库盘点记录明细 a
inner join w仓库盘点记录 b on a.父编号=b.编号
where 父编号=@lastCheckId
declare @inType nvarchar(20)
declare @outType nvarchar(20)
declare @goodsInfo nvarchar(500)
declare @Amount int
declare @stockId uniqueidentifier
declare @stockKind uniqueidentifier
declare @warehouseId uniqueidentifier
--本期入库
declare curAAA cursor
for
select a.库存类别编号,a.仓库编号,a.入库方式,b.物品编号,
b.品名 + '(' + b.规格 + ' ' + b.生产厂家 + ' ' + b.批号 + ' ' + convert(varchar(10),b.有效期,121) + ' ' + cast(b.成本单价 as varchar(20)) + ')' as 物品信息,
sum(b.数量) as 数量 from w入库记录 a inner join w入库记录明细 b
on a.编号=b.父编号 where a.登记时间>@lastCheckTime
and a.入库标志=1
group by a.库存类别编号,a.仓库编号,a.入库方式,b.物品编号,
b.品名,b.规格,b.生产厂家,b.批号,b.有效期,b.成本单价
open curAAA
fetch next from curAAA into @stockKind,@warehouseId,@inType,@stockId,@goodsInfo,@Amount
while @@Fetch_Status=0
begin
if(@inType='采购')
if exists(select _id from #StockList where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId and _goodsInfo=@goodsInfo)
update #StockList set _buy=_buy+@Amount
where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId and _goodsInfo=@goodsInfo
else
insert into #StockList (_goodsInfo,_lastStockAmount,_buy,_stockId,_stockKind,_warehouseId)
values(@goodsInfo,0,@Amount,@stockId,@stockKind,@warehouseId)
else if(@inType='盘盈')
if exists(select _id from #StockList where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId and _goodsInfo=@goodsInfo)
update #StockList set _profit=_profit+@Amount
where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId and _goodsInfo=@goodsInfo
else
insert into #StockList (_goodsInfo,_lastStockAmount,_profit,_stockId,_stockKind,_warehouseId)
values(@goodsInfo,0,@Amount,@stockId,@stockKind,@warehouseId)
fetch next from curAAA into @stockKind,@warehouseId,@inType,@stockId,@goodsInfo,@Amount
end
close CurAAA
deallocate CurAAA
--本期出库
declare curAAB cursor
for
select a.库存类别编号,a.仓库编号,a.出库方式,b.物品库存编号,
sum(b.数量) as 数量 from w出库记录 a inner join w出库记录明细 b
on a.编号=b.父编号 where a.登记时间>@lastCheckTime
and a.出库标志=1
group by a.库存类别编号,a.仓库编号,a.出库方式,b.物品库存编号
open curAAB
fetch next from curAAB into @stockKind,@warehouseId,@outType,@stockId,@Amount
while @@Fetch_Status=0
begin
if(@outType='销售')
if exists(select _id from #StockList where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId)
update #StockList set _sale=_sale+@Amount
where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId
else
insert into #StockList (_goodsInfo,_lastStockAmount,_sale,_stockId,_stockKind,_warehouseId)
values(@goodsInfo,0,@Amount,@stockId,@stockKind,@warehouseId)
else if(@outType='盘亏')
if exists(select _id from #StockList where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId)
update #StockList set _loss=_loss+@Amount
where _stockId=@stockId and _stockKind=@stockKind and _warehouseId=@warehouseId
else
insert into #StockList (_goodsInfo,_lastStockAmount,_loss,_stockId,_stockKind,_warehouseId)
values(@goodsInfo,0,@Amount,@stockId,@stockKind,@warehouseId)
fetch next from curAAB into @stockKind,@warehouseId,@outType,@stockId,@Amount
end
close curAAB
deallocate curAAB
--得到本期库存
update #StockList set _curStockAmount=
isnull((select 数量 from w仓库库存 where 编号=_stockId),0)
if(len(@stockKindId)>0)
delete from #StockList where _stockKind<>@stockKindid
if(len(@wareId)>0)
delete from #StockList where _warehouseId<>@wareId
if(@pageSize is null or @pageSize<=0)
set @pageSize=20
if(@pageIndex is null or @pageIndex<=0)
set @pageIndex=1
select @pageCount=count(*) from #StockList
if(@pageCount is null)
set @pageCount=0
declare @ii int
set @ii=(@pageCount-1)/@pageSize + 1
if(@pageIndex>@ii)
set @pageIndex=@ii
set @goodsName=@goodsName+'%'
select * from
(select ROW_NUMBER() OVER (ORDER BY aa._id) AS [ROW_NUMBER],aa.* from
(select top (@pageIndex * @pageSize) * from #StockList aa
where aa._goodsInfo like @goodsName) bb
where bb.[Row_Number]>((@pageIndex-1) * @pageSize)
drop table #StockList