IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp_tag_realtime_update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sp_tag_realtime_update]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp_timer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sp_timer]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tag]') AND type in (N'U'))
DROP TABLE [tag]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tag_realtime_RTC]') AND type = 'D')
BEGIN
ALTER TABLE [tag_realtime] DROP CONSTRAINT [DF_tag_realtime_RTC]
END
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tag_realtime_last_time]') AND type = 'D')
BEGIN
ALTER TABLE [tag_realtime] DROP CONSTRAINT [DF_tag_realtime_last_time]
END
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tag_realtime_interval]') AND type = 'D')
BEGIN
ALTER TABLE [tag_realtime] DROP CONSTRAINT [DF_tag_realtime_interval]
END
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tag_realtime_lat]') AND type = 'D')
BEGIN
ALTER TABLE [tag_realtime] DROP CONSTRAINT [DF_tag_realtime_lat]
END
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tag_realtime_lng]') AND type = 'D')
BEGIN
ALTER TABLE [tag_realtime] DROP CONSTRAINT [DF_tag_realtime_lng]
END
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tag_realtime]') AND type in (N'U'))
DROP TABLE [tag_realtime]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tag_alarm]') AND type in (N'U'))
DROP TABLE [tag_alarm]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tag_alarm]') AND type in (N'U'))
BEGIN
CREATE TABLE [tag_alarm](
	[tid] [int] IDENTITY(1,1) NOT NULL,
	[tagid] [nvarchar](50) NULL,
	[imei] [nvarchar](50) NULL,
	[RTC] [datetime] NULL,
	[servertime] [datetime] NULL,
	[last_time] [datetime] NULL,
	[lat] [decimal](18, 6) NULL,
	[lng] [decimal](18, 6) NULL,
	[interval] [int] NULL,
 CONSTRAINT [PK_tag_alarm] PRIMARY KEY CLUSTERED 
(
	[tid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tag_realtime]') AND type in (N'U'))
BEGIN
CREATE TABLE [tag_realtime](
	[tid] [int] IDENTITY(1,1) NOT NULL,
	[tagid] [nvarchar](50) NULL,
	[imei] [nvarchar](50) NULL,
	[RTC] [datetime] NULL CONSTRAINT [DF_tag_realtime_RTC]  DEFAULT (getdate()),
	[last_time] [datetime] NULL CONSTRAINT [DF_tag_realtime_last_time]  DEFAULT (getdate()),
	[interval] [int] NULL CONSTRAINT [DF_tag_realtime_interval]  DEFAULT ((0)),
	[lat] [decimal](18, 6) NULL CONSTRAINT [DF_tag_realtime_lat]  DEFAULT ((0)),
	[lng] [decimal](18, 6) NULL CONSTRAINT [DF_tag_realtime_lng]  DEFAULT ((0)),
	[servertime] [datetime] NULL  DEFAULT (getdate()),
 CONSTRAINT [PK_tag_realtime] PRIMARY KEY CLUSTERED 
(
	[tid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tag]') AND type in (N'U'))
BEGIN
CREATE TABLE [tag](
	[tid] [int] IDENTITY(1,1) NOT NULL,
	[tagid] [nvarchar](50) NULL,
	[imei] [nvarchar](50) NULL,
 CONSTRAINT [PK_tag] PRIMARY KEY CLUSTERED 
(
	[tid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp_timer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sp_timer]
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_tid int
	DECLARE @tid int
	DECLARE @in_count int
	DECLARE @next_index INT
	DECLARE @interval int
	DECLARE @sql nvarchar(4000)
	
	select @in_count=COUNT(*) from tag_realtime
	set @tid=0
	set @next_index=0
	while(@next_index<=@in_count)
	begin
		select top 1 @in_tid=tid,@in_RTC=RTC,@in_last_time=last_time,@in_interval=interval
		from tag_realtime
		where tid>@tid
		select @interval=DATEDIFF(MINUTE, @in_RTC, GETUTCDATE())
		if(@interval>5 or @interval<-5)
		begin
			set @in_RTC=GETUTCDATE()
			update tag_realtime set interval=@interval,RTC=@in_RTC where tid=@in_tid
		end
		set @next_index=@next_index+1
		set @tid=@in_tid
	end
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
	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]
    FROM INSERTED
	
	
	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
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[trigger_tag]'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER [trigger_tag]
   ON  [tag]
   AFTER INSERT
AS 
BEGIN
	DECLARE @in_tagid nvarchar(50)
	DECLARE @in_COUNT INT
	SELECT DISTINCT  @in_tagid=tagid FROM INSERTED
	SELECT @in_COUNT=COUNT(*) FROM [tag_realtime] WHERE [tagid]=@in_tagid
    IF(@in_COUNT=0)
    BEGIN
		INSERT tag_realtime([tagid]) VALUES(@in_tagid)
    END 
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 @tagid varchar(50)
	Declare @Table_tagidList table ( tagid Varchar(50))  -- 建立表变量
	
	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 @lasttime=[servertime] FROM  [tag_realtime] WHERE [tagid]=@tagid
		SET @interval=DATEDIFF(MINUTE, @lasttime, @in_servertime)
		UPDATE [tag_realtime] SET [imei]=@in_imei,[last_time]=@lasttime,[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
	IF @in_tagid <> ''/''
	BEGIN
		SELECT @lasttime=[servertime] FROM  [tag_realtime] WHERE [tagid]=@in_tagid
		SET @interval=DATEDIFF(MINUTE, @lasttime, @in_servertime)
		UPDATE [tag_realtime] SET [imei]=@in_imei,[last_time]=@lasttime,[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

	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],[servertime] FROM tag_realtime WHERE tagid=@tagid
		
		SET @k=@k-1
	END
END
' 
END
GO
--gateway product imei数据
if exists (select * from dbo.sysobjects where id = object_id(N'[sp_product_list]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [sp_product_list]
GO
CREATE PROCEDURE [dbo].[sp_product_list]
@in_isType int,
@in_TypeName nvarchar(50)
AS
BEGIN
	IF @in_isType=0
		BEGIN
			SELECT [PID],[TypeName],[IMEI],[TypeID] FROM [TZONEGPS].[dbo].[view_product] 
			WHERE [StopTime]>=GETDATE()
		END
	ELSE
		BEGIN
			SELECT [PID],[TypeName],[IMEI],[TypeID] FROM [TZONEGPS].[dbo].[view_product] 
			WHERE [StopTime]>=GETDATE() AND [TypeName]=@in_TypeName	
		END
END
GO
