-- =============================================
-- 创建：ZS
-- 日期：2005-03-14 22:01 at home
-- 用途：根据配送时间表的定义计划下次配送日期
--调用样式：
--declare @NEXTDAY datetime
--exec GETNEXTDSTDAY '2005-03-19',3645,@NEXTDAY output
--select @NEXTDAY
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'GETNEXTDSTDAY')
DROP PROC GETNEXTDSTDAY
GO
CREATE PROC GETNEXTDSTDAY
@TODAY datetime,
@VSTOCK_ID int,
@NEXTDAY datetime output
AS
BEGIN
set DATEFIRST 1
select top 1 @NEXTDAY = dateadd(day,ZTIME-datepart(weekday,@TODAY),@TODAY)
from DST.STORE_TIME_TABLE
where ZSTOP_YN=0
and ZSTORE_ID=@VSTOCK_ID
and @TODAY<dateadd(day,ZTIME-datepart(weekday,@TODAY),@TODAY)
if @@rowcount=0
select top 1 @NEXTDAY = dateadd(day,7+ZTIME-datepart(weekday,@TODAY),@TODAY)
from DST.STORE_TIME_TABLE
where ZSTOP_YN=0
and ZSTORE_ID=@VSTOCK_ID
and @TODAY<dateadd(day,7+ZTIME-datepart(weekday,@TODAY),@TODAY)
END
GO
下面是配送任务：
/***********中药自动配送（基于规则的决策）启动日期选择**************/
declare @VSTORE_ID smallint
set DATEFIRST 1
declare TIME_CUR scroll cursor for
select ZSTORE_ID
from DST.STORE_TIME_TABLE
where ZSTOP_YN=0
and ZTIME = datepart(weekday,getdate())
order by ZTIME,ZSTORE_ID
open TIME_CUR
fetch first from TIME_CUR into @VSTORE_ID
while (@@fetch_status = 0)
begin
begin tran
declare @TODAY datetime
declare @NEXTDAY datetime
set @TODAY = convert(datetime,convert(varchar,getdate(),112))
exec GETNEXTDSTDAY @TODAY,@VSTORE_ID,@NEXTDAY output
if datediff(day,@TODAY,@NEXTDAY)>2 --如果下次配送时间大于2天，提高配送点的量，防止缺货
exec ZS_PAUTO_DST_CTM @VSTORE_ID,60,15,10
else
exec ZS_PAUTO_DST_CTM @VSTORE_ID,60,15,7
if @@error <>0
rollback tran
else
commit tran
fetch next from TIME_CUR into @VSTORE_ID
end
close TIME_CUR
deallocate TIME_CUR
