declare @strSql nVarchar(200)
declare @sSQL nVarchar(200)
declare @sBegdate As nVarchar(200)
declare @iCou int
--取库存启用日期
Set @sBegdate = N'select @StockStartDate = cast(cValue as Datetime) from ' + @cDBName + '..accinformation ' + ' Where cSysid=''ST'' and cName= ''dSTStartDate'' '
Set @strNewRecord = N'Insert Into ' +@cDBName+'..STStockAgeTempTable(autoid, iqtty, inum)
values(@autoid, @iTempQtty, @iTempNum) '
set @strLeftQty =
' SELECT cWhCode,
cInvCode,
IsNull(cBatch,'''') As cBatch,
IsNull(cFree1,'''') As cFree1,
IsNull(cFree2,'''') As cFree2,
SUM(case when bRdFlag = 1 then iQuantity else -iQuantity end) as iQtty,
SUM(case when bRdFlag = 1 then iNum else - iNum end) AS iNum
From( Select bRdFlag, cWhCode, cInvCode, iQuantity, INum, cBatch, cFree1, cFree2
From '+ @cDBName+'..ST_BegInvAge
Union ALL
Select R.bRdFlag, R.cWhCode, Rs.cInvCode, Rs.iQuantity, Rs.INum, Rs.cBatch, Rs.cFree1, Rs.cFree2
From ' + @cDBName+ '..RdRecord R inner join '+@cDBName+'..RdRecords Rs on R.id = Rs.id
Where R.dDate >= @StartDate And R.dDate <= @CurrentDate And cVouchType <> ''33'' And cVouchType <> ''34''
) X
group by cWhCode, cInvCode, IsNull(cBatch,''''),IsNull(cFree1,''''), IsNull(cFree2,'''') '
--取入库语句
Set @strSQLIn = ' Select AutoId, iQtty, iNum
From (Select dDate, AutoId, abs(iQuantity) As iQtty, abs(iNum) As iNum
From '+@cDBName+'..ST_BegInvAge
Where cWhCode = @cWhCode and cInvCode = @cInvCode and isnull(cBatch,'''') = @cBatch
and isnull(cFree1,'''') = @cFree1 and isnull(cFree2,'''') = @cFree2
Union ALL
Select d.dDate,ds.autoid, Abs(ds.iQuantity) as iQtty, Abs(ds.iNum) as iNum
from '+@cDBName+'..RdRecord d join ' + @cDBName + '..RdRecords ds on (d.id = ds.id)
where ( (d.bRdFlag = 1 and ds.iQuantity > 0) or (d.bRdFlag <> 1 and ds.iQuantity < 0))
And d.dDate >= @StartDate and d.dDate <= @CurrentDate and d.cWhCode = @cWhCode
and ds.cInvCode = @cInvCode and IsNull(ds.cBatch,'''') = @cBatch and IsNull(ds.cFree1,'''') = @cFree1
and IsNull(ds.cFree2,'''') = @cFree2) X
order by dDate Desc,AutoID Desc for read only '
Set @strSQLIn = N' declare curInvIn insensitive cursor for ' + @strSQLIn
set @strLeftQty = N'declare curRd cursor for ' + @strLeftQty
execute sp_executesql @strLeftQty,
N'@CurrentDate Datetime, @StartDate Datetime',
@CurrentDate, @StockStartDate
open curRd
fetch next from curRd into
@cWhCode,@cInvCode,@cBatch,
@cFree1,@cFree2,@iQtty, @iNum
--建立计算结果临时表
Set @sSQL = N'select @Num = count(1) from ' + @cDBName + '..sysobjects ' + ' Where name = ''STStockAgeTempTable'' '
exec sp_executesql @sSQL,N'@Num int output', @iCou output
If @iCou <> 0
Begin
Exec (' Drop Table ' + @cDBName+'..STStockAgeTempTable')
End
open curInvIn
fetch next from curInvIn into @autoId, @iVouchQtty, @iVouchNum
while (round(@iQtty,6) > 0 or round(@iNum,6) > 0) and @@fetch_status =0
begin
if Round(@iQtty, 6) > Round(@iVouchQtty, 6)
begin
set @iTempQtty = @iVouchQtty
set @iQtty = Round(@iQtty, 6) - Round(@iVouchQtty, 6)
end
else begin
set @iTempQtty = @iQtty
set @iQtty = 0.0
end
if Round(@iNum, 6) > Round(@iVouchNum,6)
begin
set @iTempNum = @iVouchNum
set @iNum = Round(@iNum, 6) - Round(@iVouchNum, 6)
end
else begin
set @iTempNum = @iNum
set @iNum = 0.0
end
