这几天写的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Insert_TSM_Messages]
as
begin
declare @ids int;
set @ids=1;
declare @PlotID nvarchar(50);declare @SendingTime datetime;declare @AlarmID int;declare @state int; declare @RegionName nvarchar(50);
--创建读取数据的游标
declare API_CEODasktop cursor
--读取数据
for select [RegionName] from [dbo].[SysCycle] group by [RegionName]
open API_CEODasktop --打开游标
fetch next from API_CEODasktop into @RegionName
while(@@FETCH_STATUS=0)
begin
if(@ids>6)
set @ids=1;
while @ids<=6
begin
declare @states int;
declare @RegionNames nvarchar(50);
declare @PlotIDs int;
declare @SendingTimes datetime;
declare @RegionID int;--区域ID
declare @BU_RegionID int;--事业部id
declare @事业部count int;
declare @区域count int;
declare @事业部insert int;
set @事业部insert=0;
declare @区域insert int;
set @区域insert=0;
declare @NameClass nvarchar(50);
if(@ids=1)
begin
set @NameClass='个地块计划意向协议时间即将到期';
end
if(@ids=2)
set @NameClass='个地块计划正式协议时间即将到期';
if(@ids=3)
set @NameClass='个地块计划完成确权时间即将到期';
if(@ids=4)
set @NameClass='个地块计划意向协议时间已超时';
if(@ids=5)
set @NameClass='个地块计划正式协议时间已超时';
if(@ids=6)
set @NameClass='个地块计划完成确权时间已超时';
if(@ids>=1 and @ids<=3)
begin
declare select_SysCycle cursor
for select PlotID,SendingTime, [state],[RegionName] from [dbo].[SysCycle] where [state]=@ids and RegionName=@RegionName and SendingTime>GETDATE()
open select_SysCycle
fetch next from select_SysCycle into @PlotIDs,@SendingTimes, @states,@RegionNames
while(@@FETCH_STATUS=0)
begin
--查询出事业部id和区域id
select @RegionID=RegionID, @BU_RegionID=BU_RegionID from RegionList where RegionID=(select RegionID from [dbo].[ProjectList] where PlatFormID=(select [PlatFormID] from [dbo].[Pro_PlotInfo] where [PlotID]=@PlotIDs))
select @事业部count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@BU_RegionID and PresidentType=1
select @区域count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@RegionID and PresidentType=2
if(@事业部count>0)
begin
set @事业部insert=@事业部insert+1;
end
if(@区域count>0)
begin
set @区域insert=@区域insert+1;
end
fetch next from select_SysCycle into @PlotIDs,@SendingTimes, @states,@RegionNames ---循环结束
end
--关闭内层游标
close select_SysCycle
deallocate select_SysCycle
end
if(@ids>=4 and @ids<=6)
begin
declare select_Sys cursor
for select PlotID,SendingTime, [state],[RegionName] from [dbo].[SysCycle] where [state]=@ids and RegionName=@RegionName and SendingTime>GETDATE()
open select_Sys
fetch next from select_Sys into @PlotIDs,@SendingTimes, @states,@RegionNames
while(@@FETCH_STATUS=0)
begin
--查询出事业部id和区域id
select @RegionID=RegionID, @BU_RegionID=BU_RegionID from RegionList where RegionID=(select RegionID from [dbo].[ProjectList] where PlatFormID=(select [PlatFormID] from [dbo].[Pro_PlotInfo] where [PlotID]=@PlotIDs))
select @事业部count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@BU_RegionID and PresidentType=1
select @区域count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@RegionID and PresidentType=2
if(@事业部count>0)
begin
set @事业部insert=@事业部insert+1;
end
if(@区域count>0)
begin
set @区域insert=@区域insert+1;
end
fetch next from select_Sys into @PlotIDs,@SendingTimes, @states,@RegionNames ---循环结束
end
--关闭内层游标
close select_Sys
deallocate select_Sys
end
----------------------------插入数据开始----------------------------------------
if(@事业部insert>0)
begin
declare @LoginName nvarchar(50);
declare insert_TSM_Messs cursor
for select LoginName from [dbo].[AlarmReceivers] where OrgPrimarykey=@BU_RegionID and PresidentType=1
open insert_TSM_Messs
fetch next from insert_TSM_Messs into @LoginName
while(@@FETCH_STATUS=0)
begin
declare @HX_CELL_PHONE nvarchar(50);--手机号码
declare @HX_BUSN_PHONE nvarchar(50);--
----------------------------ftx账号插入-----------------------------
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@LoginName,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,1,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
----------------------------ftx账号插入结束-----------------------------
----------------------手机短信发送开始-----------------------------
select @HX_BUSN_PHONE=HX_BUSN_PHONE, @HX_CELL_PHONE=HX_CELL_PHONE from ds.PERS_ALL where HX_OPRID=@LoginName
if(LEN(@HX_CELL_PHONE)>=11 and LEN(@HX_CELL_PHONE)<12)
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_CELL_PHONE,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
else
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_BUSN_PHONE,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
----------------------手机短信发送结束-----------------------------
fetch next from insert_TSM_Messs into @LoginName
end
close insert_TSM_Messs
deallocate insert_TSM_Messs
end
if(@事业部insert>0)
begin
declare @LoginNames nvarchar(50);
declare insert_TSM cursor
for select LoginName from [dbo].[AlarmReceivers] where OrgPrimarykey=@RegionID and PresidentType=2
open insert_TSM
fetch next from insert_TSM into @LoginNames
while(@@FETCH_STATUS=0)
begin
declare @HX_CELL_PHONEs nvarchar(50);--手机号码
declare @HX_BUSN_PHONEs nvarchar(50);--
----------------------------ftx账号插入-----------------------------
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@LoginName,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,1,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
----------------------------ftx账号插入结束-----------------------------
----------------------手机短信发送开始-----------------------------
select @HX_BUSN_PHONE=HX_BUSN_PHONE, @HX_CELL_PHONE=HX_CELL_PHONE from ds.PERS_ALL where HX_OPRID=@LoginNames
if(LEN(@HX_CELL_PHONEs)>=11 and LEN(@HX_CELL_PHONEs)<12)
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_CELL_PHONEs,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
else
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_BUSN_PHONEs,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
----------------------手机短信发送结束-----------------------------
fetch next from insert_TSM into @LoginNames
end
close insert_TSM
deallocate insert_TSM
end
--------------------------------插入数据结束----------------------------------------------
set @ids=@ids+1;
end--第二层循环结束
--第一层循环结束
fetch next from API_CEODasktop into @RegionName
end
--关闭游标
close API_CEODasktop
--删除游标
deallocate API_CEODasktop
end
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Insert_TSM_Messages]
as
begin
declare @ids int;
set @ids=1;
declare @PlotID nvarchar(50);declare @SendingTime datetime;declare @AlarmID int;declare @state int; declare @RegionName nvarchar(50);
--创建读取数据的游标
declare API_CEODasktop cursor
--读取数据
for select [RegionName] from [dbo].[SysCycle] group by [RegionName]
open API_CEODasktop --打开游标
fetch next from API_CEODasktop into @RegionName
while(@@FETCH_STATUS=0)
begin
if(@ids>6)
set @ids=1;
while @ids<=6
begin
declare @states int;
declare @RegionNames nvarchar(50);
declare @PlotIDs int;
declare @SendingTimes datetime;
declare @RegionID int;--区域ID
declare @BU_RegionID int;--事业部id
declare @事业部count int;
declare @区域count int;
declare @事业部insert int;
set @事业部insert=0;
declare @区域insert int;
set @区域insert=0;
declare @NameClass nvarchar(50);
if(@ids=1)
begin
set @NameClass='个地块计划意向协议时间即将到期';
end
if(@ids=2)
set @NameClass='个地块计划正式协议时间即将到期';
if(@ids=3)
set @NameClass='个地块计划完成确权时间即将到期';
if(@ids=4)
set @NameClass='个地块计划意向协议时间已超时';
if(@ids=5)
set @NameClass='个地块计划正式协议时间已超时';
if(@ids=6)
set @NameClass='个地块计划完成确权时间已超时';
if(@ids>=1 and @ids<=3)
begin
declare select_SysCycle cursor
for select PlotID,SendingTime, [state],[RegionName] from [dbo].[SysCycle] where [state]=@ids and RegionName=@RegionName and SendingTime>GETDATE()
open select_SysCycle
fetch next from select_SysCycle into @PlotIDs,@SendingTimes, @states,@RegionNames
while(@@FETCH_STATUS=0)
begin
--查询出事业部id和区域id
select @RegionID=RegionID, @BU_RegionID=BU_RegionID from RegionList where RegionID=(select RegionID from [dbo].[ProjectList] where PlatFormID=(select [PlatFormID] from [dbo].[Pro_PlotInfo] where [PlotID]=@PlotIDs))
select @事业部count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@BU_RegionID and PresidentType=1
select @区域count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@RegionID and PresidentType=2
if(@事业部count>0)
begin
set @事业部insert=@事业部insert+1;
end
if(@区域count>0)
begin
set @区域insert=@区域insert+1;
end
fetch next from select_SysCycle into @PlotIDs,@SendingTimes, @states,@RegionNames ---循环结束
end
--关闭内层游标
close select_SysCycle
deallocate select_SysCycle
end
if(@ids>=4 and @ids<=6)
begin
declare select_Sys cursor
for select PlotID,SendingTime, [state],[RegionName] from [dbo].[SysCycle] where [state]=@ids and RegionName=@RegionName and SendingTime>GETDATE()
open select_Sys
fetch next from select_Sys into @PlotIDs,@SendingTimes, @states,@RegionNames
while(@@FETCH_STATUS=0)
begin
--查询出事业部id和区域id
select @RegionID=RegionID, @BU_RegionID=BU_RegionID from RegionList where RegionID=(select RegionID from [dbo].[ProjectList] where PlatFormID=(select [PlatFormID] from [dbo].[Pro_PlotInfo] where [PlotID]=@PlotIDs))
select @事业部count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@BU_RegionID and PresidentType=1
select @区域count=count(*) from [dbo].[AlarmReceivers] where OrgPrimarykey=@RegionID and PresidentType=2
if(@事业部count>0)
begin
set @事业部insert=@事业部insert+1;
end
if(@区域count>0)
begin
set @区域insert=@区域insert+1;
end
fetch next from select_Sys into @PlotIDs,@SendingTimes, @states,@RegionNames ---循环结束
end
--关闭内层游标
close select_Sys
deallocate select_Sys
end
----------------------------插入数据开始----------------------------------------
if(@事业部insert>0)
begin
declare @LoginName nvarchar(50);
declare insert_TSM_Messs cursor
for select LoginName from [dbo].[AlarmReceivers] where OrgPrimarykey=@BU_RegionID and PresidentType=1
open insert_TSM_Messs
fetch next from insert_TSM_Messs into @LoginName
while(@@FETCH_STATUS=0)
begin
declare @HX_CELL_PHONE nvarchar(50);--手机号码
declare @HX_BUSN_PHONE nvarchar(50);--
----------------------------ftx账号插入-----------------------------
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@LoginName,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,1,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
----------------------------ftx账号插入结束-----------------------------
----------------------手机短信发送开始-----------------------------
select @HX_BUSN_PHONE=HX_BUSN_PHONE, @HX_CELL_PHONE=HX_CELL_PHONE from ds.PERS_ALL where HX_OPRID=@LoginName
if(LEN(@HX_CELL_PHONE)>=11 and LEN(@HX_CELL_PHONE)<12)
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_CELL_PHONE,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
else
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_BUSN_PHONE,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
----------------------手机短信发送结束-----------------------------
fetch next from insert_TSM_Messs into @LoginName
end
close insert_TSM_Messs
deallocate insert_TSM_Messs
end
if(@事业部insert>0)
begin
declare @LoginNames nvarchar(50);
declare insert_TSM cursor
for select LoginName from [dbo].[AlarmReceivers] where OrgPrimarykey=@RegionID and PresidentType=2
open insert_TSM
fetch next from insert_TSM into @LoginNames
while(@@FETCH_STATUS=0)
begin
declare @HX_CELL_PHONEs nvarchar(50);--手机号码
declare @HX_BUSN_PHONEs nvarchar(50);--
----------------------------ftx账号插入-----------------------------
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@LoginName,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,1,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
----------------------------ftx账号插入结束-----------------------------
----------------------手机短信发送开始-----------------------------
select @HX_BUSN_PHONE=HX_BUSN_PHONE, @HX_CELL_PHONE=HX_CELL_PHONE from ds.PERS_ALL where HX_OPRID=@LoginNames
if(LEN(@HX_CELL_PHONEs)>=11 and LEN(@HX_CELL_PHONEs)<12)
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_CELL_PHONEs,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
else
begin
INSERT INTO dbo.TSM_Messages(MessageID, Target, Title, Content, Priority, MessageType, CreateTime, TargetTime, SendTime, Status, TryTimes, ErrorInfo)
values(NEWID(),
@HX_BUSN_PHONEs,
(@RegionName+'消息提醒'),
((select cast(@RegionName as nvarchar(50)))+(select cast(@事业部insert as nvarchar(5)))+@NameClass),1,2,GETDATE(),(select CONVERT(VARCHAR,GETDATE()+1,23)+' 08:30:00'),getdate(),0,1,'')
end
----------------------手机短信发送结束-----------------------------
fetch next from insert_TSM into @LoginNames
end
close insert_TSM
deallocate insert_TSM
end
--------------------------------插入数据结束----------------------------------------------
set @ids=@ids+1;
end--第二层循环结束
--第一层循环结束
fetch next from API_CEODasktop into @RegionName
end
--关闭游标
close API_CEODasktop
--删除游标
deallocate API_CEODasktop
end
