-- Autor: Steffen Anders
-- Stand: 2022-06-28

-------------------------------------------------------------------------------------------------------
-- aktuelle Sitzung registrieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_RegisterSession')
  DROP PROCEDURE [dbo].[itcproc_RegisterSession]
GO
CREATE PROCEDURE [dbo].[itcproc_RegisterSession] @Param_ProductId char(23)
AS
  DECLARE @m_NeueId    integer

  IF 0 = (SELECT count(*) FROM [dbo].[ut_UserSession] WHERE [ProductId] = @Param_ProductId)
  BEGIN
    SET @m_NeueId = (SELECT MAX(RecId) FROM [dbo].[ut_UserSession])
	IF @m_NeueId IS NULL SET @m_NeueId = 0
	SET @m_NeueId = @m_NeueId + 1
    INSERT INTO [dbo].[ut_UserSession] (RecId, [ProductId]) VALUES (@m_NeueId, @Param_ProductId)
  END

  UPDATE [dbo].[ut_UserSession] SET [Activity] = CONVERT(char(19),SYSDATETIME(),120) WHERE [ProductId] = @Param_ProductId
GO

-------------------------------------------------------------------------------------------------------
-- aktive Sessions
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V'   and [name] = 'itcview_SessionActivity')
  DROP VIEW [dbo].[itcview_SessionActivity]
GO
CREATE VIEW [dbo].[itcview_SessionActivity]
AS
  SELECT [ProductId], DATEDIFF(second, CONVERT(datetime,[Activity],120), SYSDATETIME()) AS [InactiveSince] FROM [dbo].[ut_UserSession]
GO

-------------------------------------------------------------------------------------------------------
-- konkurierende Sessions
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_CompetingSessions')
  DROP PROCEDURE [dbo].[itcproc_CompetingSessions]
GO
CREATE PROCEDURE [dbo].[itcproc_CompetingSessions] @Param_ProductId char(23)
AS
  SELECT count(*) AS Result FROM [dbo].[itcview_SessionActivity] WHERE ([InactiveSince] < 30) AND ([ProductId] <> @Param_ProductId)
GO

--EXECUTE [dbo].[itcproc_RegisterSession] '00342-50658-15075-AAOEN'
--EXECUTE [dbo].[itcproc_RegisterSession] '00342-50658-15075-AAOEM'
--EXECUTE [dbo].[itcproc_CompetingSessions] '00342-50658-15075-AAOEM'
