
ALTER TABLE tag_realtime ADD flag int default (0)
GO
UPDATE tag_realtime SET flag=0
GO

--tag_realtime_update_flag
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_tag_realtime_update_flag]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_tag_realtime_update_flag]
GO
CREATE PROCEDURE sp_tag_realtime_update_flag
@in_imei nvarchar(50),
@in_flag int,
@in_servertime datetime
AS
BEGIN
	IF(@in_flag=0)
	BEGIN
		UPDATE [tag_realtime] 
		SET 
		[flag] = @in_flag,
		[last_time]=@in_servertime,
		[servertime]=@in_servertime,
		[interval]=0
		WHERE [imei]=@in_imei
	END
	ELSE IF(@in_flag=2)
	BEGIN
		DECLARE @k INT
		DECLARE @tid INT
		DECLARE @interval INT
		DECLARE @lasttime datetime
		DECLARE @servertime datetime
		DECLARE @tagid varchar(50)
		SET @tid=0
		SELECT @k=COUNT(*) FROM tag_realtime WHERE [imei]=@in_imei
		WHILE @k>0
		BEGIN		
			SELECT TOP 1 @tid=[tid],@tagid=[tagid],@lasttime=[last_time],@servertime=[servertime]  
			FROM [tag_realtime] WHERE tid>@tid AND [imei]=@in_imei order by tid
			SET @interval=DATEDIFF(MINUTE, @lasttime, @in_servertime)
			UPDATE [tag_realtime] SET [last_time]=@servertime,[interval]=@interval,[servertime]=@in_servertime
			WHERE [tagid]=@tagid
			
			SET @k=@k-1
		END
	END
	ELSE
	BEGIN
		UPDATE [tag_realtime] 
		SET 
		[flag] = @in_flag,
		[servertime]=@in_servertime,
		[interval]=0
		WHERE [imei]=@in_imei
	END
END
--exec sp_tag_realtime_update_flag


GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[trigger_tag_realtime_alarm]'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER [trigger_tag_realtime_alarm]
   ON  [tag_realtime]
   AFTER UPDATE
AS 
BEGIN
	DECLARE @in_interval int
	DECLARE @in_tagid nvarchar(50)
	DECLARE @in_imei nvarchar(50)
	DECLARE @in_RTC datetime
	DECLARE @in_last_time datetime
	DECLARE @in_lat decimal(18,6)
	DECLARE @in_lng decimal(18,6)
	DECLARE @in_servertime datetime
	DECLARE @in_flag int
	SELECT DISTINCT
	   @in_interval=[interval]
      ,@in_tagid=[tagid]
      ,@in_imei=[imei]
      ,@in_RTC=[RTC]
      ,@in_last_time=[last_time]
      ,@in_lat=[lat]
      ,@in_lng=[lng]
      ,@in_servertime=[servertime]
      ,@in_flag=[flag]
    FROM INSERTED
	
	IF(@in_flag=0)
	BEGIN
		IF(@in_interval>10 or @in_interval<-10)
		BEGIN
			INSERT INTO [tag_alarm]([tagid],[imei],[RTC],[lat],[lng],[last_time],[interval],[servertime]) 
			VALUES(@in_tagid,@in_imei,@in_RTC,@in_lat,@in_lng,@in_last_time,@in_interval,@in_servertime)
		END
	END
END
'
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp_tag_realtime_update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sp_tag_realtime_update]
@in_tagid nvarchar(max),
@in_imei nvarchar(50),
@in_RTC datetime,
@in_lat decimal(18,6),
@in_lng decimal(18,6),
@seprator  varchar(10),
@in_servertime datetime
AS
BEGIN
	DECLARE @i INT
	DECLARE @interval INT
	DECLARE @lasttime datetime
	DECLARE @servertime datetime
	DECLARE @tagid varchar(50)
	Declare @Table_tagidList table ( tagid Varchar(50))  -- 建立表变量
	DECLARE @TC INT
	
	SET @in_tagid = rtrim(ltrim(@in_tagid))
	SET @i = charindex(@seprator, @in_tagid)
	WHILE @i >= 1
	BEGIN
		SET @tagid=left(@in_tagid, @i - 1)
		SET @in_tagid = substring(@in_tagid, @i + 1, len(@in_tagid) - @i)
		SET @i = charindex(@seprator, @in_tagid)
		SELECT @TC=COUNT(*) FROM  [tag] WHERE [tagid]=@tagid AND [imei]=@in_imei
		IF(@TC<>0)
		BEGIN
			SELECT @lasttime=[last_time],@servertime=[servertime] FROM  [tag_realtime] WHERE [tagid]=@tagid AND [imei]=@in_imei
			SET @interval=DATEDIFF(MINUTE, @lasttime, @in_servertime)
			UPDATE [tag_realtime] SET [imei]=@in_imei,[last_time]=@servertime,[RTC]=@in_RTC,[lat]=@in_lat,[lng]=@in_lng,[interval]=@interval,[servertime]=@in_servertime
			WHERE [tagid]=@tagid
			insert into @Table_tagidList (tagid) values(@tagid)
		END
	END
	IF @in_tagid <> ''/''
	BEGIN
		SELECT @TC=COUNT(*) FROM  [tag] WHERE [tagid]=@in_tagid AND [imei]=@in_imei
		IF(@TC<>0)
		BEGIN
		    SELECT @lasttime=[last_time],@servertime=[servertime] FROM  [tag_realtime] WHERE [tagid]=@in_tagid AND [imei]=@in_imei
			SET @interval=DATEDIFF(MINUTE, @lasttime, @in_servertime)
			UPDATE [tag_realtime] SET [imei]=@in_imei,[last_time]=@servertime,[RTC]=@in_RTC,[lat]=@in_lat,[lng]=@in_lng,[interval]=@interval,[servertime]=@in_servertime
			WHERE [tagid]=@in_tagid
			insert into @Table_tagidList (tagid) values(@in_tagid)
		END
	END

	DECLARE @k INT
	DECLARE @tid INT
	DECLARE @imei varchar(50)
	SET @tid=0

	SELECT @k=COUNT(*) FROM (SELECT * FROM tag WHERE [imei]=@in_imei) AS T
	WHERE tagid NOT IN (select tagid from @Table_tagidList) 
	
	WHILE @k>0
	BEGIN		
		SELECT TOP 1 @tid=[tid],@tagid=[tagid],@imei=[imei] FROM (SELECT * FROM tag WHERE [imei]=@in_imei) AS T
		WHERE tagid NOT IN (select tagid from @Table_tagidList) AND tid>@tid
		
		INSERT INTO [tag_alarm]([tagid],[imei],[RTC],[lat],[lng],[last_time],[interval],[servertime])
		--values(@tagid,@imei,@in_servertime,0,0,@in_servertime,0,@in_servertime)
		SELECT [tagid],[imei],[RTC],[lat],[lng],[last_time],[interval],(select @in_servertime) as servertime FROM tag_realtime WHERE tagid=@tagid
		
		SET @k=@k-1
	END
END
' 
END
GO