-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2023, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--

-------------------------------------------------------------------------------------------------- 
--  WebMandant braucht Schluessl Knr, Locked
-- view beruecksichtigt gesperrte Mandanten und gesperrte Permissions
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V'   and [name] = 'itcview_WebMandantPermission')
  DROP VIEW [dbo].[itcview_WebMandantPermission]
GO
CREATE VIEW dbo.itcview_WebMandantPermission
AS
  select mr.Knr, wp.Roule, wp.Permission, wp.Granted from ut_WebRoulePermission wp 
     inner join ut_WebMandantRoule mr on wp.Roule = mr.Roule and wp.Granted = 1
	 inner join ut_WebMandant      wm on wm.Knr   = mr.Knr  and wm.Locked = 0
GO

-------------------------------------------------------------------------------------------------- 
--  WebUser braucht Schluessl Knr, Uid, Locked
-- view beruecksichtigt gesperrte Nutzer
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V'   and [name] = 'itcview_WebMandantUser')
  DROP VIEW [dbo].[itcview_WebMandantUser]
GO
CREATE VIEW dbo.itcview_WebMandantUser
AS
  select wr.Knr, wr.[Uid], wr.Roule from ut_WebUserRoule wr
     inner join ut_WebUser wu on wr.Knr = wu.Knr and wr.[Uid] = wu.[Uid] and wu.Locked = 0
GO

-------------------------------------------------------------------------------------------------- 
--
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V'   and [name] = 'itcview_WebUserPermission')
  DROP VIEW [dbo].[itcview_WebUserPermission]
GO
CREATE VIEW dbo.itcview_WebUserPermission
AS
  select u.Knr, u.[Uid], u.Roule, p.Permission, p.Granted from dbo.itcview_WebMandantPermission p
     inner join dbo.itcview_WebMandantUser u on p.Knr = u.Knr and p.[Roule] = u.[Roule]
GO

---------------------------------------------------------------------- Permission mit Kundenummer und Benutzernamen ueberpruefen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_web_Check_Permission')
  DROP PROCEDURE [dbo].[itcproc_web_Check_Permission]
GO
CREATE PROCEDURE [dbo].[itcproc_web_Check_Permission]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Pwd varchar(30)
 ,@Parm_Permission varchar(50)
AS
  DECLARE @mPwHash integer
  SET @mPwHash = dbo.itcfunc_StringHash(@Parm_Pwd)

  EXECUTE [dbo].[itcproc_WebLog_write] @Parm_Knr, @Parm_Uid, @Parm_Permission, '', '', 0

  IF 1 <> (SELECT count(*) FROM [dbo].[ut_WebUser]  WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid AND [PwHash] = @mPwHash) BEGIN
    SELECT 0 AS Granted, @Parm_Knr AS Knr, @Parm_Uid AS [Uid] 
    RETURN
  END

  SELECT [Granted], [Knr], [Uid] FROM dbo.itcview_WebUserPermission
	WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid AND [Permission] = @Parm_Permission
GO

------------------------------------------------------------------------ Permission mit Session-ID und IP-Adresse ueberpruefen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_web_Check_Permission2')
  DROP PROCEDURE [dbo].[itcproc_web_Check_Permission2]
GO
CREATE PROCEDURE [dbo].[itcproc_web_Check_Permission2]
  @Parm_SessionId varchar(40)
 ,@Parm_IPAddress varchar(20)
 ,@Parm_Permission varchar(50)
AS
  DECLARE @mKnr varchar(20)
  DECLARE @mUid varchar(30)
  SELECT TOP 1 @mKnr = [Knr], @mUid = [Uid] from [dbo].[ut_WebUserSession] WHERE [SessionId] = @Parm_SessionId AND [IPAddress] = @Parm_IPAddress

  EXECUTE [dbo].[itcproc_WebLog_write] @mKnr, @mUid, @Parm_Permission, @Parm_SessionId, @Parm_IPAddress, 0

  SELECT [Granted], [Knr], [Uid] FROM dbo.itcview_WebUserPermission
	WHERE [Knr] = @mKnr AND [Uid] = @mUid AND [Permission] = @Parm_Permission
GO

------------------------------------------------------------------------ Permissionliste mit Session-ID liefern
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_GetMyPermissions')
  DROP PROCEDURE [dbo].[itcproc_GetMyPermissions]
GO
CREATE PROCEDURE [dbo].[itcproc_GetMyPermissions]
  @Parm_SessionId varchar(40)
AS
  DECLARE @mKnr varchar(20)
  DECLARE @mUid varchar(30)
  SELECT TOP 1 @mKnr = [Knr], @mUid = [Uid] from [dbo].[ut_WebUserSession] WHERE [SessionId] = @Parm_SessionId

  EXECUTE [dbo].[itcproc_WebLog_write] @mKnr, @mUid, 'dbo.itcproc_GetMyPermissions', @Parm_SessionId, '', 0

  SELECT [Permission] FROM dbo.itcview_WebUserPermission
	WHERE [Knr] = @mKnr AND [Uid] = @mUid
GO

------------------------------------------------------------------------ Permissionliste mit Mandant und Benutzer
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_GetMyPermissions2')
  DROP PROCEDURE [dbo].[itcproc_GetMyPermissions2]
GO
CREATE PROCEDURE [dbo].[itcproc_GetMyPermissions2]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Dummy varchar(30)
AS
  EXECUTE [dbo].[itcproc_WebLog_write] @Parm_Knr, @Parm_Uid, 'dbo.itcproc_GetMyPermissions2', '', '', 0
  SELECT [Permission] FROM dbo.itcview_WebUserPermission WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid
GO

------------------------------------------------------------------------ Einzelne Permission abfragen
IF OBJECT_ID (N'itcfunc_GetMyPermission', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_GetMyPermission
GO
CREATE FUNCTION dbo.itcfunc_GetMyPermission(@Param_Knr varchar(20), @Param_Uid varchar(30), @Param_Permission varchar(50)) RETURNS integer
AS
BEGIN
  DECLARE @m_Granted integer
  SELECT @m_Granted = Granted FROM dbo.itcview_WebUserPermission WHERE [Knr] = @Param_Knr AND [Uid] = @Param_Uid AND [Permission] = @Param_Permission
  IF @m_Granted IS NULL
    SET @m_Granted = 0
  RETURN @m_Granted
END
GO

---------------------------------------------------------------------- Permission fur Hybrid-App ueberpruefen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_hybrid_Check_Permission')
  DROP PROCEDURE [dbo].[itcproc_hybrid_Check_Permission]
GO
CREATE PROCEDURE [dbo].[itcproc_hybrid_Check_Permission]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Pwd varchar(30)
 ,@Parm_Permission varchar(50)
AS
  IF 1 <> (SELECT count(*) FROM [dbo].[ut_WebUser]  WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid) BEGIN
    SELECT 0 AS Granted, @Parm_Knr AS Knr, @Parm_Uid AS [Uid] 
    RETURN
  END

  EXECUTE [dbo].[itcproc_WebLog_write] @Parm_Knr, @Parm_Uid, @Parm_Permission, '', '', 0

  SELECT [Granted], [Knr], [Uid] FROM dbo.itcview_WebUserPermission
	WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid AND [Permission] = @Parm_Permission
GO
