WZ_CONNECT_MEMB:
Código:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'WZ_CONNECT_MEMB')
DROP PROCEDURE [dbo].[WZ_CONNECT_MEMB]
GO
CREATE PROCEDURE WZ_CONNECT_MEMB
@uid VARCHAR(20),
@server VARCHAR(20),
@uip VARCHAR(20)
AS BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON
IF EXISTS (SELECT memb___id FROM MEMB_STAT WITH (READUNCOMMITTED) WHERE memb___id = @uid )
BEGIN
UPDATE MEMB_STAT SET ip = @uip , connectstat = 1 , servername = @server , ConnectTM = (getdate()) WHERE memb___id = @uid;
--=================================================--
-- ATUALIZANDO HORÁRIO DE LOGIN NA MEMB_STAT --
--=================================================--
UPDATE MEMB_STAT SET ConnectStartTime = DATEDIFF(s, '19700101', GETDATE()) WHERE memb___id = @uid;
END
ELSE
BEGIN
INSERT INTO MEMB_STAT ( memb___id,ip,connectstat,servername) VALUES (@uid,@uip,1,@server);
--======================================================================--
-- ATUALIZANDO HORÁRIO DE LOGIN NA MEMB_STAT APÓS INSERT DE DADOS --
--======================================================================--
UPDATE MEMB_STAT SET ConnectStartTime = DATEDIFF(s, '19700101', GETDATE()) WHERE memb___id = @uid;
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
WZ_DISCONNECT_MEMB:
Código:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'WZ_DISCONNECT_MEMB')
DROP PROCEDURE [DBO].[WZ_DISCONNECT_MEMB]
GO
CREATE PROCEDURE WZ_DISCONNECT_MEMB
@uid VARCHAR(20)
AS BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @LOGINTIME INT;
DECLARE @LOGOUTTIME INT;
DECLARE @TIMEON INT;
IF EXISTS ( SELECT memb___id FROM MEMB_STAT WITH (READUNCOMMITTED) WHERE memb___id = @uid )
BEGIN
UPDATE MEMB_STAT SET DisConnectTM = (getdate()), connectstat = 0 WHERE memb___id = @uid;
--======================================================--
-- ATUALIZANDO HORÁRIO DE LOGGOUT NA MEMB_STAT --
--======================================================--
UPDATE MEMB_STAT SET ConnectEndTime = DATEDIFF(s, '19700101', GETDATE()) WHERE memb___id = @uid;
--==============================================================--
-- SELECIONANDO HORÁRIO DE LOGIN E DE LOGGOUT NA MEMB_STAT --
--==============================================================--
SET @LOGINTIME = (SELECT DATEDIFF(s, GETDATE(), DATEADD(s, CAST(MEMB_STAT.ConnectStartTime as int), '19700101')) FROM MEMB_STAT WHERE memb___id = @uid);
SET @LOGOUTTIME = (SELECT DATEDIFF(s, GETDATE(), DATEADD(s, CAST(MEMB_STAT.ConnectEndTime as int), '19700101')) FROM MEMB_STAT WHERE memb___id = @uid);
--==============================--
-- EXECUTANDO OPERAÇÃO --
--==============================--
SET @TIMEON = @LOGOUTTIME - @LOGINTIME;
--==============================================--
-- ATUALIZANDO TEMPO ONLINE NA MEMB_STAT --
--==============================================--
UPDATE MEMB_STAT SET TimeON = TimeON + @TIMEON WHERE memb___id = @uid;
END
ELSE
BEGIN
INSERT INTO MEMB_STAT ( memb___id, DisConnectTM, connectstat) VALUES (@uid,(getdate()),0);
END
IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Aguardo feedback.
[]'s
