executaveis/Gprs/scripts/1.SR_GPRS - DB+Procedures+Functions.sql

create database srgprs
go
use srgprs
GO
/****** Object:  Table [dbo].[tmp_sync]    Script Date: 08/26/2010 00:41:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_sync]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tmp_sync](
	[veiculo] [int] NULL,
	[datetime] [datetime] NULL
) ON [PRIMARY]
END
GO
/****** Object:  StoredProcedure [dbo].[ProcP3]    Script Date: 08/26/2010 00:40:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcP3]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[ProcP3] 
	-- Add the parameters for the stored procedure here
	@provaID varchar(10),
	@cnnStr nvarchar(200)
AS
BEGIN
	SET NOCOUNT ON;
	declare @delCmd varchar (max);

	declare @str varchar(max);
	select @str = ''SELECT destino, comando FROM Pendente where destino not like ''''''''+%'''''''' and provaid='' +  @provaID ; 
	select @delCmd = ''delete FROM Pendente where destino not like ''''''''+%'''''''' and provaid='' +  @provaID ; 
	exec (''INSERT into pendente Select * from OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''','''''' +  @str + '''''') AS a;'')

	exec (''delete from OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''','''''' +  @str + '''''');'')

END
' 
END
GO
/****** Object:  Table [dbo].[ParticipacaoProva]    Script Date: 08/26/2010 00:40:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParticipacaoProva]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParticipacaoProva](
	[PilotoID] [int] NULL,
	[EquipaID] [int] NULL,
	[SafetyBoxID] [int] NULL,
	[VeiculoID] [int] NOT NULL,
	[ProvaID] [int] NOT NULL,
	[estadoParticipacaoID] [int] NOT NULL,
	[unitid] [varchar](20) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tmp_sync_rotagps]    Script Date: 08/26/2010 00:41:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_sync_rotagps]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tmp_sync_rotagps](
	[veiculo] [int] NULL,
	[datetime] [datetime] NULL
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Pendente]    Script Date: 08/26/2010 00:40:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pendente]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Pendente](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[unitid] [varchar](50) NOT NULL,
	[command] [varchar](300) NULL,
 CONSTRAINT [PK_Pendente] PRIMARY KEY CLUSTERED 
(
	[id] 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_PADDING OFF
GO
/****** Object:  Table [dbo].[RotaHistorico]    Script Date: 08/26/2010 00:41:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RotaHistorico]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RotaHistorico](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UnitID] [bigint] NOT NULL,
	[data] [datetime] NOT NULL,
	[latitude] [float] NOT NULL,
	[longitude] [float] NOT NULL,
	[velocidade] [int] NOT NULL,
	[rumo] [int] NOT NULL,
	[altitude] [float] NOT NULL,
	[satelites] [int] NOT NULL,
	[inputs] [int] NULL,
	[outputs] [int] NULL,
	[customInput_1] [int] NULL,
	[customInput_2] [int] NULL,
	[razaoSMSID] [int] NOT NULL,
 CONSTRAINT [PK_RotaHistorico] PRIMARY KEY CLUSTERED 
(
	[ID] 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
/****** Object:  Table [dbo].[Rota]    Script Date: 08/26/2010 00:40:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rota]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Rota](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UnitID] [bigint] NOT NULL,
	[data] [datetime] NOT NULL,
	[latitude] [float] NOT NULL,
	[longitude] [float] NOT NULL,
	[velocidade] [int] NOT NULL,
	[rumo] [int] NOT NULL,
	[altitude] [float] NOT NULL,
	[satelites] [int] NOT NULL,
	[inputs] [int] NULL,
	[outputs] [int] NULL,
	[customInput_1] [int] NULL,
	[customInput_2] [int] NULL,
	[razaoSMSID] [int] NOT NULL,
 CONSTRAINT [PK_Rota] PRIMARY KEY CLUSTERED 
(
	[ID] 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
/****** Object:  StoredProcedure [dbo].[ProcP2]    Script Date: 08/26/2010 00:40:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcP2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[ProcP2]
	-- Add the parameters for the stored procedure here
	@provaID varchar(10),
	@etapaID varchar(10),
	@cnnStr nvarchar(200)
AS
BEGIN
	SET NOCOUNT OFF;
	-- temporary data
	delete from tmp_sync
	declare @str varchar(200)
	select @str = ''select veiculoid, convert( varchar(20), max(data), 120 ) from rotaSMS where provaid = '' + @ProvaID + '' and etapaid = '' + @etapaid + '' group by veiculoid'' 	
	exec (''insert into tmp_sync Select * from OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''','''''' +  @str + '''''') AS a;'')


	declare @str1 varchar(max);
	select @str1 = ''SELECT [VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude]''+
      '',[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[razaoSMSID] FROM FuncTable ( '' + @provaID + '', '' + @etapaID + '') ''
	
--[VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude],[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[razaoSMSID]

	exec (''insert into OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''', ''''select [VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude],[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[razaoSMSID] from rotasms'''') '' + @str1 + '' '' )
	
print @str1
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[FuncTable]    Script Date: 08/26/2010 00:41:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[FuncTable](@ProvaID int, @etapaID int)
RETURNS @retFindReports TABLE 
(
    -- columns returned by the function
    [VeiculoID] [int] NOT NULL,
	[ProvaID] [int] NOT NULL,
	[SafetyBoxID] [int] NOT NULL,
	[EtapaID] [int] NOT NULL,
	[data] [datetime] NOT NULL,
	[latitude] [float] NOT NULL,
	[longitude] [float] NOT NULL,
	[velocidade] [int] NOT NULL,
	[rumo] [int] NOT NULL,
	[altitude] [float] NOT NULL,
	[satelites] [int] NOT NULL,
	[inputs] [int] NULL,
	[outputs] [int] NULL,
	[customInput_1] [int] NULL,
	[customInput_2] [int] NULL,
	[razaoSMSID] [int] NOT NULL
)
AS
-- body of the function
BEGIN
   
  DECLARE @veiculoID varchar(20)
DECLARE @datetime varchar(20)

DECLARE db_cursor CURSOR FOR 
select veiculoid, convert( varchar(20), max(data), 120 )
from rota R left join participacaoprova P on R.unitid = P.unitid
group by veiculoid

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @veiculoID,  @datetime 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       declare @dtOld datetime
		-- insert the new positions only i.e. the ones that aren''t in the safetyrally original DB	
		if  not exists (select 1 from  tmp_sync where veiculo = @veiculoID)
		begin
			INSERT @retFindReports 
			select distinct veiculoid, @provaID , safetyboxid, @etapaID 
			, data, latitude, longitude, velocidade, rumo, altitude, satelites, inputs, outputs, CustomInput_1, CustomInput_2, razaoSMSID 
			from Rota R left join ParticipacaoProva P on R.UnitID = P.unitid 
			where P.unitid is not null and veiculoid =  @veiculoID  and data = @datetime
		end

		--insert the updated positions only if most recent	
		set @dtOld = (
			select convert( varchar(20), max(datetime), 120 )
			from tmp_sync
			where veiculo = @veiculoID
			group by veiculo 
		)

		if (@datetime > @dtOld)
		begin
			INSERT @retFindReports 
			select distinct veiculoid, @provaID , safetyboxid, @etapaID 
			, data, latitude, longitude, velocidade, rumo, altitude, satelites, inputs, outputs, CustomInput_1, CustomInput_2, razaoSMSID 
			from Rota R left join ParticipacaoProva P on R.UnitID = P.unitid 
			where P.unitid is not null and veiculoid =  @veiculoID  and data = @datetime
		end
		
	   FETCH NEXT FROM db_cursor INTO @veiculoID, @datetime   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
   
RETURN
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[ProcSyncParticipacao]    Script Date: 08/26/2010 00:40:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcSyncParticipacao]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[ProcSyncParticipacao] 
	-- Add the parameters for the stored procedure here
	@provaID varchar(10),
	@cnnStr nvarchar(200)
AS
BEGIN
	SET NOCOUNT ON;
	
	delete from participacaoProva

	declare @str varchar(max);
	select @str = ''select P.*, S.unitid from participacaoprova P, safetybox S where P.SafetyBoxId = S.dispositivoid and provaid='' +  @provaID ; 
	
	exec (''INSERT into participacaoprova Select * from OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''','''''' +  @str + '''''') AS a;'')
	
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[FuncTable_rotagps]    Script Date: 08/26/2010 00:41:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncTable_rotagps]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[FuncTable_rotagps](@ProvaID int, @etapaID int)
RETURNS @retFindReports TABLE 
(
    -- columns returned by the function
    [VeiculoID] [int] NOT NULL,
	[ProvaID] [int] NOT NULL,
	[SafetyBoxID] [int] NOT NULL,
	[EtapaID] [int] NOT NULL,
	[data] [datetime] NOT NULL,
	[latitude] [float] NOT NULL,
	[longitude] [float] NOT NULL,
	[velocidade] [int] NOT NULL,
	[rumo] [int] NOT NULL,
	[altitude] [float] NOT NULL,
	[satelites] [int] NOT NULL,
	[inputs] [int] NULL,
	[outputs] [int] NULL,
	[customInput_1] [int] NULL,
	[customInput_2] [int] NULL,
	[logComunicacaoID] [int] NULL,
	sensores int not null,
	cobertura bit not null
)
AS
-- body of the function
BEGIN
   
  DECLARE @veiculoID varchar(20)
DECLARE @datetime varchar(20)

DECLARE db_cursor CURSOR FOR 
select veiculoid, convert( varchar(20), max(data), 120 )
from rotahistorico R left join participacaoprova P on R.unitid = P.unitid
group by veiculoid

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @veiculoID,  @datetime 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       declare @dtOld datetime
		-- insert the new positions only i.e. the ones that aren''t in the safetyrally original DB	
		if  not exists (select 1 from  tmp_sync_rotagps where veiculo = @veiculoID)
		begin
			INSERT @retFindReports 
			select distinct veiculoid, @provaID , safetyboxid, @etapaID 
			, data, latitude, longitude, velocidade, rumo, altitude, satelites, inputs, outputs, CustomInput_1, CustomInput_2, NULL, 0, 0  
			from Rotahistorico R left join ParticipacaoProva P on R.UnitID = P.unitid 
			where P.unitid is not null and veiculoid =  @veiculoID  and data <= @datetime
		end

		--insert the updated positions only if most recent	
		set @dtOld = (
			select convert( varchar(20), max(datetime), 120 )
			from tmp_sync_rotagps
			where veiculo = @veiculoID
			group by veiculo 
		)

		if (@datetime > @dtOld)
		begin
			INSERT @retFindReports 
			select distinct veiculoid, @provaID , safetyboxid, @etapaID 
			, data, latitude, longitude, velocidade, rumo, altitude, satelites, inputs, outputs, CustomInput_1, CustomInput_2, NULL, 0, 0  
			from RotaHistorico R left join ParticipacaoProva P on R.UnitID = P.unitid 
			where P.unitid is not null and veiculoid =  @veiculoID  and data <= @datetime and data > @dtOld
		end
		
	   FETCH NEXT FROM db_cursor INTO @veiculoID, @datetime   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
   
RETURN
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[ProcP4]    Script Date: 08/26/2010 00:40:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcP4]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ProcP4]
	-- Add the parameters for the stored procedure here
	@provaID varchar(10),
	@etapaID varchar(10),
	@cnnStr nvarchar(200)
AS
BEGIN
	SET NOCOUNT OFF;
	-- temporary data
	delete from tmp_sync_rotagps
	declare @str varchar(200)
	select @str = ''select veiculoid, convert( varchar(20), max(data), 120 ) from rotaGPS where provaid = '' + @ProvaID + '' and etapaid = '' + @etapaid + '' group by veiculoid'' 	
	exec (''insert into tmp_sync_rotagps Select * from OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''','''''' +  @str + '''''') AS a;'')


	declare @str1 varchar(max);
	select @str1 = ''SELECT [VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude]''+
      '',[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[logComunicacaoID],[sensores],[cobertura] FROM FuncTable_rotagps ( '' + @provaID + '', '' + @etapaID + '') ''
	
	exec (''insert into OPENROWSET(''''SQLNCLI'''','''''' + @cnnStr + '''''', ''''select [VeiculoID],[ProvaID],[SafetyBoxID],[EtapaID],[data],[latitude],[longitude],[velocidade],[rumo],[altitude],[satelites],[inputs],[outputs],[customInput_1],[customInput_2],[logComunicacaoID],[sensores],[cobertura] from rotagps'''') '' + @str1 + '' '' )
	
print @str1
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[ProcP1]    Script Date: 08/26/2010 00:40:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcP1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ProcP1] 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
begin transaction

	DECLARE @unitID bigint
DECLARE @datetime datetime

DECLARE db_cursor CURSOR FOR  
	select unitid, max(data)
	from Rota
	group by unitid


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @unitID,  @datetime 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       
	insert into rotaHistorico	
	  SELECT [UnitID]
      ,[data]
      ,[latitude]
      ,[longitude]
      ,[velocidade]
      ,[rumo]
      ,[altitude]
      ,[satelites]
      ,[inputs]
      ,[outputs]
      ,[customInput_1]
      ,[customInput_2]
      ,[razaoSMSID]
  FROM [srgprs].[dbo].[Rota]
	where UnitID = @unitID and data < @datetime

	delete from Rota where UnitID = @unitID and data < @datetime

       FETCH NEXT FROM db_cursor INTO @unitID, @datetime   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

commit transaction
    
END
' 
END
GO