USE [000000000_0]
GO
/****** Object:  StoredProcedure [dbo].[sis_services_add_picagens_online]    Script Date: 23/01/2024 14:24:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sis_services_add_picagens_online]
	@TERMNA AS VARCHAR(200),
	@SERIA1 AS VARCHAR(200),
	@SERIA2 AS VARCHAR(200),
	@SERIA3 AS VARCHAR(200),
	@SERIA4 AS VARCHAR(200),
	@DHINS AS VARCHAR(200),
	@DHUPD AS VARCHAR(200),
	@IDFUNC AS INTEGER,
	@DATA AS VARCHAR(200),
	@ID_DISP AS INTEGER,
	@STATUS AS VARCHAR(200) out,
	@MESSAG AS VARCHAR(200) out

AS
BEGIN

	SET NOCOUNT ON;
	-- INICIA VARIÁVEIS
	DECLARE @TMP_EXISTE_DB AS INTEGER
	DECLARE @TMP_EXISTE_AC AS INTEGER
	DECLARE @TMP_EXISTE_US AS INTEGER
	DECLARE @TMP_BDNA_NAME AS VARCHAR(200)
	DECLARE @QUERY AS NVARCHAR(2000)
	--VALIDA LICENCIAMENTO E TERMINAL
	SELECT
	@TMP_EXISTE_DB = COUNT(*)
	FROM [000000000_0].[dbo].[sis_tbl_dbs] AS A
	LEFT JOIN [000000000_0].[dbo].[sis_tbl_terminais] AS B ON B.id_dbs = A.id_dbs
	WHERE A.ativo=1 AND A.rem=0
	AND A.serial1 = @SERIA1
	AND A.serial2 = @SERIA2
	AND A.serial3 = @SERIA3
	AND A.serial4 = @SERIA4
	AND B.terminal = @TERMNA
	--VALIDA DATAS DO LICENCIAMENTO
	SELECT
	@TMP_EXISTE_AC = COUNT(*)
	FROM [000000000_0].[dbo].[sis_tbl_dbs] AS A
	LEFT JOIN [000000000_0].[dbo].[sis_tbl_terminais] AS B ON B.id_dbs=A.id_dbs
	WHERE A.ativo=1 AND A.rem=0
	AND A.serial1 = @SERIA1
	AND A.serial2 = @SERIA2
	AND A.serial3 = @SERIA3
	AND A.serial4 = @SERIA4
	AND B.terminal = @TERMNA
	AND CONVERT(DATE, GETDATE()) BETWEEN CONVERT(DATE, validade_ini) AND CONVERT(DATE, validade_fim)

	IF @TMP_EXISTE_DB =0
		BEGIN
			SELECT 'NOK' AS 'STATUS', 'Sem acesso!' AS 'MESSAG'
		END
	ELSE IF @TMP_EXISTE_AC = 0
		BEGIN
			SELECT 'NOK' AS 'STATUS', 'Expirou o licenciamento!' AS 'MESSAG'
		END
	ELSE 
		BEGIN
		SELECT TOP 1
		@TMP_BDNA_NAME = bd
		FROM [000000000_0].[dbo].[sis_tbl_dbs] AS A
		LEFT JOIN [000000000_0].[dbo].[sis_tbl_terminais] AS B ON B.id_dbs=A.id_dbs
		WHERE A.ativo=1 AND A.rem=0
		AND A.serial1 = @SERIA1
		AND A.serial2 = @SERIA2
		AND A.serial3 = @SERIA3
		AND A.serial4 = @SERIA4
		AND B.terminal = @TERMNA
		AND CONVERT(DATE,GETDATE()) BETWEEN CONVERT(DATE, validade_ini) AND CONVERT(DATE, validade_fim)

		SET @QUERY='
		INSERT INTO ['+@TMP_BDNA_NAME+'].[dbo].[tbl_time_registos_picagens] (ativo, sys, rem, id_ins, id_upd, dh_ins, dh_upd, id_time_dispositivos, id_time_funcionarios,dh_registo, verificacao, modo, codigo)
		SELECT
		1 AS "ativo",
		0 AS "sys",
		0 AS "rem",
		1 AS "id_ins",
		1 AS "id_upd",
		'''+CONVERT(varchar, GETDATE(), 121)+''' AS "dh_ins",
		'''+CONVERT(varchar, GETDATE(), 121)+''' AS "dh_upd",
		'+CONVERT(varchar(200), @ID_DISP)+' AS "id_time_dispositivos",
		'+CONVERT(varchar(200), @IDFUNC)+' AS "id_time_funcionarios",
		'''+CONVERT(varchar, GETDATE(), 121)+''' AS "dh_registo",
		'''' AS "verificacao",
		'''' AS "modo",
		'''' AS "codigo"
		WHERE (SELECT COUNT(*) FROM ['+@TMP_BDNA_NAME+'].[dbo].[tbl_time_registos_picagens] WHERE id_time_funcionarios='+CONVERT(varchar(200), @IDFUNC)+' AND dh_registo='''+ CONVERT(varchar, @DATA, 121)+''')<1'
		EXECUTE sp_executesql @QUERY, N'@PerType nchar(2)', @PerType='EM'

		SET @STATUS = 'OK';
		SET @MESSAG = 'Adicionado com sucesso!';
		
	
	END
END