-- 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.
--

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WebAnnr' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize_Knr_Annr')
CREATE INDEX [Ik_Optimize_Knr_Annr] ON [dbo].[ut_WebAnnr] 
(
   [Knr],[Annr]
)
GO

--- ------------------------------------------------------------ Mitarbeiter fuer Zeiterfassung abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebAnnr_Abruf')
  DROP PROCEDURE [dbo].[itcproc_WebAnnr_Abruf]
GO
CREATE PROCEDURE [dbo].[itcproc_WebAnnr_Abruf]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20) = ''
AS
	IF @Parm_Annr <> '' BEGIN
	  SELECT Annr, [Name] FROM [dbo].[ut_WebAnnr]
	    WHERE Knr = @Parm_Knr AND Annr = @Parm_Annr
	END ELSE BEGIN
	  SELECT Annr, [Name] FROM [dbo].[ut_WebAnnr]
	    WHERE Knr = @Parm_Knr
  	    ORDER BY Annr
	END
GO

---------------------------------------------------------------- Mitarbeiter fuer Zeiterfassung aktualisieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebAnnr_Update')
  DROP PROCEDURE [dbo].[itcproc_WebAnnr_Update]
GO
CREATE PROCEDURE [dbo].[itcproc_WebAnnr_Update]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20)
 ,@Parm_Name varchar(50)
AS
	UPDATE [dbo].[ut_WebAnnr] SET [Name] = @Parm_Name  WHERE Knr = @Parm_Knr AND Annr = @Parm_Annr
GO

---------------------------------------------------------------- Mitarbeiter fuer Zeiterfassung loeschen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebAnnr_Delete')
  DROP PROCEDURE [dbo].[itcproc_WebAnnr_Delete]
GO
CREATE PROCEDURE [dbo].[itcproc_WebAnnr_Delete]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20)
AS
	DELETE FROM [dbo].[ut_WebAnnr] WHERE Knr = @Parm_Knr AND Annr = @Parm_Annr
GO

---------------------------------------------------------------- Mitarbeiter fuer Zeiterfassung anlegen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_WebAnnr_Append')
  DROP PROCEDURE [dbo].[itcproc_WebAnnr_Append]
GO
CREATE PROCEDURE [dbo].[itcproc_WebAnnr_Append]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20)
 ,@Parm_Name varchar(50)
AS
  DECLARE @NeueId    integer
  DECLARE @MaxCount  integer

  IF @Parm_Annr = '' BEGIN
    SELECT 'Das Feld "Annr" darf nicht leer sein.' AS error
    RETURN
  END

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

  SET @MaxCount = ISNULL((SELECT TOP 1 StWertI from ut_Statusfelder WHERE KdNr = @Parm_Knr AND StatId = 'MAX_WEB_AN'),0)
  IF @MaxCount <= (SELECT count(*) FROM [dbo].[ut_WebAnnr] WHERE Knr = @Parm_Knr) BEGIN
    SELECT 'Die maximale Anzahl lizensierter Mitarbeiter ist erreicht.' AS error
    RETURN
  END

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

  INSERT INTO [dbo].[ut_WebAnnr] (RecId,[Knr],[Annr],[Name])
    VALUES(@NeueId,@Parm_Knr,@Parm_Annr,@Parm_Name)
  
  EXECUTE [dbo].[itcproc_WebAnnr_Abruf] @parm_Knr, @Parm_Uid, @Parm_Annr
GO
