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

-- dbo.itcproc_web_Append_ZeitTerminalPosten
IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_ZeitTerminalPosten' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize_LfdNr')
CREATE INDEX [Ik_Optimize_LfdNr] ON [dbo].[ut_ZeitTerminalPosten] 
(
   [LfdNr]
)
GO

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

-- [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren]
IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_ZeitTerminalPosten' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize_Knr_Annr_Vkz')
CREATE INDEX [Ik_Optimize_Knr_Annr_Vkz] ON [dbo].[ut_ZeitTerminalPosten] 
(
   [Knr],[Annr],[Vkz],[Datum],[Uhrzeit],[LfdNr]
)
GO

-- [dbo].[itcproc_ZeitTerminalPosten_Abrufen]
IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_ZeitTerminalPosten' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize_Knr_TicketGuid')
CREATE INDEX [Ik_Optimize_Knr_TicketGuid] ON [dbo].[ut_ZeitTerminalPosten] 
(
   [Knr],[TicketGuid],[Annr],[Datum],[Uhrzeit],[LfdNr]
)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_web_Append_ZeitTerminalPosten')
  DROP PROCEDURE [dbo].[itcproc_web_Append_ZeitTerminalPosten]
GO
CREATE PROCEDURE [dbo].[itcproc_web_Append_ZeitTerminalPosten]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Datum varchar(10)
 ,@Parm_Uhrzeit varchar(5)
 ,@Parm_Annr varchar(20)
 ,@Parm_Belegnr varchar(20)
 ,@Parm_StatusNum varchar(10)
 ,@Parm_StatusText varchar(10)
 ,@Parm_Comment varchar(50)
 ,@Parm_PostenGuid varchar(40)
 ,@Parm_Latitude varchar(10) = null
 ,@Parm_Longitude varchar(10) = null
AS
  DECLARE @mDateTime120 char(16)
  DECLARE @NeueId    integer
  DECLARE @NeueLfdNr    integer

  IF ISNULL(@Parm_PostenGuid,'') = '' BEGIN
    SET @Parm_PostenGuid = '{' + convert(varchar(40),NEWID()) + '}'
  END
  SET @Parm_PostenGuid = UPPER(@Parm_PostenGuid)

  IF 0 = (SELECT count(*) FROM [dbo].[ut_ZeitTerminalPosten] WHERE PostenGuid = UPPER(@Parm_PostenGuid)) BEGIN
    SET     @mDateTime120 = convert(char(16),GETDATE(),120) 

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

    SET     @NeueLfdNr = (SELECT MAX(LfdNr) FROM [dbo].[ut_ZeitTerminalPosten])
    IF @NeueLfdNr IS NULL SET @NeueLfdNr = 0
    SET @NeueLfdNr = @NeueLfdNr + 1

    INSERT INTO [dbo].[ut_ZeitTerminalPosten] (
      RecId
     ,[LfdNr]
     ,[Knr]
     ,[Uid]
     ,[Datum]
     ,[Uhrzeit]
     ,[Annr]
     ,[Belegnr]
     ,[StatusNum]
     ,[StatusText]
     ,[Comment]
     ,[PostenGuid]
     ,[InsertDate]
     ,[InsertTime]
       ) VALUES (
      @NeueId
     ,@NeueLfdNr
     ,UPPER(RTRIM(@Parm_Knr))
     ,RTRIM(@Parm_Uid)
     ,convert(datetime,@Parm_Datum,120)
     ,RTRIM(@Parm_Uhrzeit)
     ,UPPER(RTRIM(@Parm_Annr))
     ,UPPER(RTRIM(@Parm_Belegnr))
     ,convert(integer,@Parm_StatusNum)
     ,RTRIM(@Parm_StatusText)
     ,RTRIM(@Parm_Comment)
     ,UPPER(RTRIM(@Parm_PostenGuid))
     ,convert(datetime,Left(@mDateTime120,10),120)
     ,Right(@mDateTime120,5)
         )
  END
GO

--------------------------------------------------------------------------------------------------------
-- Posten fuer eine Annr kalkulieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_ZeitTerminalPosten_Kalkulieren')
  DROP PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20)
 ,@Parm_TicketGuid varchar(40)
AS
  DECLARE  Cposten CURSOR KEYSET FOR 
    SELECT RecId, convert(datetime, convert(char(10),Datum,120) + ' ' + Uhrzeit,120) as FullDateTime FROM [dbo].[ut_ZeitTerminalPosten] 
	WHERE [Knr] = @Parm_Knr AND [Annr] = @Parm_Annr AND [Vkz] = 0 /* nicht verarbeitet */
    ORDER BY [Knr], [Annr] , [Datum], [Uhrzeit], [LfdNr]

  IF ISNULL(@Parm_TicketGuid,'') = '' BEGIN
    SET @Parm_TicketGuid = '{' + convert(varchar(40),NEWID()) + '}'
  END
  SET @Parm_TicketGuid = UPPER(@Parm_TicketGuid)

  DECLARE @mRecId     integer
  DECLARE @mStartTime datetime
  DECLARE @mRecIdNext integer
  DECLARE @mKnrNext varchar(20)
  DECLARE @mAnnrNext varchar(4)
  DECLARE @mGuidNext varchar(40)
  DECLARE @mVkzNext tinyint
  DECLARE @mEndTime datetime
  DECLARE @mErrorCode integer
  DECLARE @mDuration integer

  OPEN Cposten
  FETCH NEXT FROM Cposten INTO @mRecId, @mStartTime
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    -- RecId des naechsten Postens ermitteln	
	SET @mRecIdNext = isnull((SELECT TOP 1 DataRecId FROM [dbo].[ut_ZeitTerminalPosten_Keys] 
			WHERE Key03_Value > (SELECT TOP 1 Key03_Value FROM [dbo].[ut_ZeitTerminalPosten_Keys] where DataRecId = @mRecId)),0)

	IF @mRecIdNext > 0 BEGIN
	  SELECT TOP 1 @mKnrNext = RTRIM([Knr]), @mAnnrNext = RTRIM([Annr]), @mVkzNext = [Vkz], @mGuidNext = [PostenGuid],
	               @mEndTime = convert(datetime, convert(char(10),Datum,120) + ' ' + Uhrzeit,120) 
	               FROM [dbo].[ut_ZeitTerminalPosten] WHERE RecId = @mRecIdNext
	  IF @mKnrNext = @Parm_Knr AND @mAnnrNext = @Parm_Annr BEGIN
	    IF @mVkzNext = 0 BEGIN
		  SET @mErrorCode = 0
		END ELSE BEGIN
		  SET @mErrorCode = 1
		END
        SET @mDuration = DATEDIFF(minute, @mStartTime, @mEndTime)
	    UPDATE [dbo].[ut_ZeitTerminalPosten] SET Vkz = 1, Closed = 1, ClosedBy = @mGuidNext, Minuten = @mDuration, ErrorCode = @mErrorCode, TicketGuid = @Parm_TicketGuid WHERE RecId = @mRecId
	  END
	END

    FETCH NEXT FROM Cposten INTO @mRecId, @mStartTime
  END
  CLOSE Cposten
  DEALLOCATE Cposten
GO 

--------------------------------------------------------------------------------------------------------
-- Posten fuer alle Annr kalkulieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_ZeitTerminalPosten_Kalkulieren2')
  DROP PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren2]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren2]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20)
 ,@Parm_TicketGuid varchar(40)
AS
  IF ISNULL(@Parm_TicketGuid,'') = '' BEGIN
    SET @Parm_TicketGuid = '{' + convert(varchar(40),NEWID()) + '}'
  END
  SET @Parm_TicketGuid = UPPER(@Parm_TicketGuid)

  -- Ticket registrieren
  DECLARE @mDateTime120 char(16)
  DECLARE @mDate1 datetime
  DECLARE @mTime1 char(5)
  SET @mDateTime120 = convert(char(16),GETDATE(),120) 
  SET @mDate1 = convert(datetime,Left(@mDateTime120,10),120)
  SET @mTime1 = Right(@mDateTime120,5)

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

  INSERT INTO [dbo].[ut_ProcessingTicket] (RecId,[Knr],[Uid],[StartDate],[StartTime],[TicketGuid],[Comment])
    VALUES (@NeueId, @Parm_Knr,@Parm_Uid,@mDate1,@mTime1,@Parm_TicketGuid,'dbo.itcproc_ZeitTerminalPosten_Kalkulieren2')

  IF ISNULL(@Parm_Annr,'') = '' BEGIN
    -- alle Annr
    DECLARE Cannr CURSOR FOR SELECT DISTINCT [Annr] FROM [dbo].[ut_ZeitTerminalPosten] WHERE [Knr] = @Parm_Knr AND [Vkz] = 0
    DECLARE @mAnnr varchar(20)
    OPEN Cannr
    FETCH NEXT FROM Cannr INTO @mAnnr
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXECUTE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren] @Parm_Knr, @Parm_Uid, @mAnnr, @Parm_TicketGuid
      FETCH NEXT FROM Cannr INTO @mAnnr
    END
    CLOSE Cannr
    DEALLOCATE Cannr
  END ELSE BEGIN
    -- nur eine Annr
    EXECUTE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren] @Parm_Knr, @Parm_Uid, @Parm_Annr, @Parm_TicketGuid
  END

  SET @mDateTime120 = convert(char(16),GETDATE(),120) 
  SET @mDate1 = convert(datetime,Left(@mDateTime120,10),120)
  SET @mTime1 = Right(@mDateTime120,5)
  UPDATE ut_ProcessingTicket SET EndDate = @mDate1, EndTime = @mTime1, Success = 1 WHERE TicketGuid = @Parm_TicketGuid
GO 

--------------------------------------------------------------------------------------------------------
-- Posten abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_ZeitTerminalPosten_Abruf')
  DROP PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Abruf]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Abruf]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_VonDatum varchar(10)
 ,@Parm_BisDatum varchar(10)
 ,@Parm_Annr varchar(20)
 ,@Parm_Vkz integer
 ,@Parm_TicketGuid varchar(40)
AS
	IF ISNULL(@Parm_TicketGuid,'') <> '' BEGIN
	  SET @Parm_VonDatum = '2022-01-01'
	  SET @Parm_BisDatum = '2200-01-01'
	  SET @Parm_Annr = ''
	  SET @Parm_Vkz = 1
	END
	IF ISNULL(@Parm_VonDatum,'') = '' BEGIN
	  SET @Parm_VonDatum = '2022-01-01'
	END
	IF ISNULL(@Parm_BisDatum,'') = '' BEGIN
	  SET @Parm_BisDatum = convert(char(10),GETDATE(),120)
	END
    SET @Parm_TicketGuid = UPPER(@Parm_TicketGuid)

	select   zp.LfdNr
			,convert(char(10),zp.Datum,120) as Datum
			,zp.Uhrzeit
	        ,zp.Annr
			,zp.Belegnr
			,zp.StatusNum
			,zp.StatusText
			,zp.Comment
			,zp.PostenGuid
			,zp.TicketGuid
			,zp.Vkz
			,zp.Closed
			,zp.ClosedBy
			,convert(char(10),zp.InsertDate,120) as InsertDate
			,zp.InsertTime
			,zp.Minuten
			,zp.ErrorCode
			,convert(varchar(20),ROUND(convert(float,zp.Minuten)/60,2)) as Stunden
			,la.Lohnart
			,la.Comment as LohnartText
	from dbo.ut_ZeitTerminalPosten zp
		right join ut_WebAnnr an on an.Knr = zp.Knr and an.Annr = zp.Annr
		right join ut_WebLohnart la on la.Knr = zp.Knr and la.StatusNum = zp.StatusNum
	where zp.[Knr] = @Parm_Knr and zp.[Datum] >= convert(datetime,@Parm_VonDatum,120) and zp.[Datum] <= convert(datetime,@Parm_BisDatum + ' 23:59',120)
		and zp.[Vkz] = @Parm_Vkz and (@Parm_Annr = '' or zp.[Annr] = @Parm_Annr) and (@Parm_TicketGuid = '' or zp.[TicketGuid] = @Parm_TicketGuid)
	order by zp.Annr, Datum, Uhrzeit, LfdNr
GO

--------------------------------------------------------------------------------------------------------
-- Posten fuer alle Annr kalkulieren mit neuem Ticket und Abruf
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_ZeitTerminalPosten_Kalkulieren3')
  DROP PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren3]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren3]
  @Parm_Knr varchar(20)
 ,@Parm_Uid varchar(30)
 ,@Parm_Annr varchar(20) = ''
AS
  DECLARE @mTicket varchar(40)
  SET @mTicket = UPPER('{' + convert(varchar(40),NEWID()) + '}')
  EXECUTE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren2] @Parm_Knr,@Parm_Uid,@Parm_Annr,@mTicket
  EXECUTE [dbo].[itcproc_ZeitTerminalPosten_Abruf] @Parm_Knr,@Parm_Uid,'','',@Parm_Annr,1,@mTicket
GO

-- EXECUTE [dbo].[itcproc_ZeitTerminalPosten_Kalkulieren3] '59998','Master',''

