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

--- ------------------------------------------------------------ Benutzer fuer Zeiterfassung abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUser_Abruf')
  DROP PROCEDURE [dbo].[itcproc_WebUser_Abruf]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUser_Abruf]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30) = ''  -- Nutzer, um den es geht
AS
  SELECT [Uid], [Email], [Locked], [Annr], [AlleAnnr], [Hybrid] FROM [dbo].[ut_WebUser]
    WHERE Knr = @Parm_Knr AND (@Parm_UidFilter = '' OR [Uid] = @Parm_UidFilter)
	ORDER BY [Uid]
GO

---------------------------------------------------------------- Benutzer fuer Zeiterfassung aktualisieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUser_Update')
  DROP PROCEDURE [dbo].[itcproc_WebUser_Update]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUser_Update]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30) -- Nutzer, um den es geht
 ,@Parm_Email varchar(250)
 ,@Parm_Locked varchar(30)
 ,@Parm_Annr varchar(30)  
 ,@Parm_AlleAnnr varchar(30)  
 ,@Parm_Hybrid varchar(30)  -- wird ignoriert
 ,@Parm_Dummy varchar(250) = ''
AS
  UPDATE [dbo].[ut_WebUser] SET 
    [Email] = @Parm_Email,  
	[Locked] = dbo.itcfunc_integer(@Parm_Locked),
	[Annr] = @Parm_Annr,
	[AlleAnnr] = dbo.itcfunc_integer(@Parm_AlleAnnr)
  WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_UidFilter
GO

---------------------------------------------------------------- Passwort fuer Benutzer aktualisieren (Admin/Master)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUser_UpdatePwd')
  DROP PROCEDURE [dbo].[itcproc_WebUser_UpdatePwd]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUser_UpdatePwd]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30) -- Nutzer, um den es geht
 ,@Parm_Pwd varchar(30)
AS
  DECLARE @PwHash integer
  SET @PwHash = dbo.itcfunc_StringHash(@Parm_Pwd)

  UPDATE [dbo].[ut_WebUser] SET [PwHash] = @PwHash WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_UidFilter
GO

---------------------------------------------------------------- eigenes Passwort aendern
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUser_ChangePwd')
  DROP PROCEDURE [dbo].[itcproc_WebUser_ChangePwd]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUser_ChangePwd]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_PwdOld varchar(30)
 ,@Parm_PwdNew varchar(30)
AS
  DECLARE @PwHashOld integer
  SET @PwHashOld = dbo.itcfunc_StringHash(@Parm_PwdOld)
  DECLARE @PwHashNew integer
  SET @PwHashNew = dbo.itcfunc_StringHash(@Parm_PwdNew)

  IF 1 = (SELECT count(*) FROM [dbo].[ut_WebUser] WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid AND [PwHash] = @PwHashOld) BEGIN
    UPDATE [dbo].[ut_WebUser] SET [PwHash] = @PwHashNew WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_Uid AND [PwHash] = @PwHashOld
	SELECT 1 AS success
    RETURN
  END
 
  SELECT 'Das Passwort wurde nicht aktualisiert.' AS error
GO

---------------------------------------------------------------- Benutzer fuer Zeiterfassung loeschen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUser_Delete')
  DROP PROCEDURE [dbo].[itcproc_WebUser_Delete]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUser_Delete]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30) -- Nutzer, um den es geht
AS
	DELETE FROM [dbo].[ut_WebUser] WHERE Knr = @Parm_Knr AND [Uid] = @Parm_UidFilter
GO

----------------------------------------------------------------------------------------------------------
-- Achtung die Parameter weichen von Update ab
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUser_Append')
  DROP PROCEDURE [dbo].[itcproc_WebUser_Append]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUser_Append]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30) -- Nutzer, um den es geht
 ,@Parm_Pwd varchar(30)
AS
  IF rtrim(@Parm_Knr) = '' OR rtrim(@Parm_Uid) = '' OR rtrim(@Parm_Pwd) = ''
  BEGIN
    SELECT 'Mindestens eines der Felder "Uid" oder "Pwd" ist nicht korrekt.' AS error
	RETURN
  END

  IF 0 < (SELECT count(*) FROM [dbo].[ut_WebUser] WHERE [Knr] = @Parm_Knr AND [Uid] = @Parm_UidFilter)
  BEGIN
    SELECT 'Dieser Datensatz existiert bereits.' AS error
    RETURN
  END

  DECLARE @PwHash integer
  SET @PwHash = dbo.itcfunc_StringHash(@Parm_Pwd)

  DECLARE @NeueId integer
  SET @NeueId = (SELECT MAX(RecId) FROM [dbo].[ut_WebUser])
  IF @NeueId IS NULL SET @NeueId = 0
  SET @NeueId = @NeueId + 1

  INSERT [dbo].[ut_WebUser] ([RecId],[Knr],[Uid],[PwHash])
			values(@NeueId,@Parm_Knr,@Parm_UidFilter,@PwHash)

  EXECUTE [dbo].[itcproc_WebUser_Abruf] @parm_Knr, @Parm_Uid, @Parm_UidFilter
GO

--- ------------------------------------------------------------ Benutzer fuer Zeiterfassung abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUserRoule_get_page')
  DROP PROCEDURE [dbo].[itcproc_WebUserRoule_get_page]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUserRoule_get_page]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)  -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30) = ''  -- Nutzer, um den es geht
AS
  select @Parm_UidFilter as [UidFilter], mr.Roule, wr.Descr, 
			(select count(*) from ut_WebUserRoule where Knr = mr.Knr and Roule = mr.Roule and [Uid] = @Parm_UidFilter) as Assigned
    from ut_WebMandantRoule mr inner join ut_WebRoule wr on wr.Roule = mr.Roule
    where mr.Knr = @Parm_Knr
	order by mr.Roule
GO

--- ------------------------------------------------------------ Benutzer fuer Zeiterfassung abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebUserRoule_put')
  DROP PROCEDURE [dbo].[itcproc_WebUserRoule_put]
GO
CREATE PROCEDURE [dbo].[itcproc_WebUserRoule_put]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)        -- Nutzer, der den Aufruf gestarted hat
 ,@Parm_UidFilter varchar(30)  -- Nutzer, um den es geht
 ,@Parm_Roule varchar(30)
 ,@Parm_Descr varchar(50)
 ,@Parm_Assigned varchar(10)
AS
  IF @Parm_Roule = '' OR @Parm_UidFilter = '' BEGIN
    SELECT 'Benutzer und Rolle duerfen nicht leer sein' as error
	RETURN
  END

  IF dbo.itcfunc_integer(@Parm_Assigned) = 0 BEGIN
	DELETE FROM dbo.ut_WebUserRoule WHERE Knr = @Parm_Knr AND [Uid] = @Parm_UidFilter AND Roule = @Parm_Roule
	RETURN
  END

  IF 0 = (SELECT count(*) FROM dbo.ut_WebUserRoule WHERE Knr = @Parm_Knr AND [Uid] = @Parm_UidFilter AND Roule = @Parm_Roule) BEGIN
    DECLARE @NeueId integer
    SET @NeueId = dbo.itcfunc_NextRecId_WebUserRoule()
    INSERT [dbo].[ut_WebUserRoule] (RecId,Knr,[Uid],Roule,UidKey)
			values(@NeueId,@Parm_Knr,@Parm_UidFilter,@Parm_Roule,UPPER(@Parm_UidFilter))
  END
GO

-- select * from ut_WebUser
