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

----------------------------------------------------------------------------------------------
-- Adressen in Ressourcenplanung
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_ResplanMember')
  DROP VIEW [dbo].[itcview_ResplanMember]
GO
CREATE VIEW [dbo].[itcview_ResplanMember] AS
  SELECT a.* FROM dbo.ut_Adressen a WITH(INDEX = Ik_Key6)
    INNER JOIN dbo.ut_Statusfelder s ON s.PrjNr = a.DESTTYP + a.Annr AND s.StatId = 'RESSPLANNR'
GO
----------------------------------------------------------------------------------------------
-- Termine im Zeitraum auflisten
-- klassischer Klient Ressourcenplanungsdialog
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Resplan_list')
  DROP PROCEDURE [dbo].[itcproc_Resplan_list]
GO
CREATE PROCEDURE [dbo].[itcproc_Resplan_list]
  @Parm_VonDatum datetime  
 ,@Parm_BisDatum datetime  
 ,@Parm_ResTyp char(2) = ''
 ,@Parm_ResNr char(4) = ''
 ,@Parm_BaGruppe char(2) = ''
 ,@Parm_ForExport tinyint = 0
AS
  DECLARE @m_VonJMT10 char(10) = convert(char(10), @Parm_VonDatum, 120)
  DECLARE @m_BisJMT10 char(10) = convert(char(10), @Parm_BisDatum, 120)
  DECLARE @m_VonJMT char(8) = SUBSTRING(@m_VonJMT10,1,4) + SUBSTRING(@m_VonJMT10,6,2) + SUBSTRING(@m_VonJMT10,9,2)
  DECLARE @m_BisJMT char(8) = SUBSTRING(@m_BisJMT10,1,4) + SUBSTRING(@m_BisJMT10,6,2) + SUBSTRING(@m_BisJMT10,9,2)

  IF @Parm_ForExport <> 0 BEGIN
    -- assign missed guids
    UPDATE r SET ExpGuid = LOWER(NEWID())
    FROM ut_ResPlandaten r WITH(INDEX(Ik_Key3)) INNER JOIN dbo.itcview_ResplanMember a ON a.DESTTYP = r.ResTyp AND a.Annr = r.ResNr
    WHERE (Jahr + Monat + Tag) >= @m_VonJMT AND (Jahr + Monat + Tag) <= @m_BisJMT AND ExpGuid = ''
	  AND (@Parm_ResTyp = '' OR ResTyp = @Parm_ResTyp) AND (@Parm_ResNr = '' OR ResNr = @Parm_ResNr) AND (@Parm_BaGruppe = '' OR BaGruppe = @Parm_BaGruppe)

    -- update stamp
    DECLARE @m_ExpStamp varchar(30) = CONVERT(varchar(30), CURRENT_TIMESTAMP, 120)
    SET @m_ExpStamp = SUBSTRING(@m_ExpStamp,1,10) + 'T' + SUBSTRING(@m_ExpStamp,12,8) + 'Z'
    SET @m_ExpStamp = REPLACE(@m_ExpStamp,'-','')
    SET @m_ExpStamp = REPLACE(@m_ExpStamp,':','')
    UPDATE r SET ExpStamp = @m_ExpStamp
    FROM ut_ResPlandaten r WITH(INDEX(Ik_Key3)) INNER JOIN dbo.itcview_ResplanMember a ON a.DESTTYP = r.ResTyp AND a.Annr = r.ResNr
    WHERE (Jahr + Monat + Tag) >= @m_VonJMT AND (Jahr + Monat + Tag) <= @m_BisJMT
	  AND (@Parm_ResTyp = '' OR ResTyp = @Parm_ResTyp) AND (@Parm_ResNr = '' OR ResNr = @Parm_ResNr) AND (@Parm_BaGruppe = '' OR BaGruppe = @Parm_BaGruppe)
  END

  SELECT r.*
     , a.DESTDEBNR
	 , a.Name1
	 , a.BaGruppe
     , DATEDIFF(day, @Parm_VonDatum, convert(datetime, Jahr + '-' + Monat + '-' + Tag, 120)) + 1 AS IndexOfDay
	 , dbo.itcfunc_Duration_Minutes(r.SS, r.SM, r.ES, r.EM) AS Duration
  --SELECT r.*, a.DESTDEBNR, a.Name1, a.BaGruppe, Jahr + '-' + Monat + '-' + Tag 
    FROM ut_ResPlandaten r WITH(INDEX(Ik_Key3)) INNER JOIN dbo.itcview_ResplanMember a ON a.DESTTYP = r.ResTyp AND a.Annr = r.ResNr
    WHERE (Jahr + Monat + Tag) >= @m_VonJMT AND (Jahr + Monat + Tag) <= @m_BisJMT
	  AND (@Parm_ResTyp = '' OR ResTyp = @Parm_ResTyp) AND (@Parm_ResNr = '' OR ResNr = @Parm_ResNr) AND (@Parm_BaGruppe = '' OR BaGruppe = @Parm_BaGruppe)
    ORDER BY ResTyp, ResNr, Jahr, Monat, Tag, SS, SM, LfdNr
GO
----------------------------------------------------------------------------------------------

-- 20240508T120939Z

--EXECUTE [dbo].[itcproc_Resplan_list] '20240416', '20240422',' 4','','',1
--EXECUTE [dbo].[itcproc_Resplan_list] '20240416', '20240422',' 4','1001'


-- SELECT DATEDIFF(day, '20240401', '20240431')

-- EXECUTE [dbo].[itcproc_Resplan_list] '20240401', '20240505'

-- EXECUTE [dbo].[itcproc_Resplan_list] '20240401', '20240430', ' ', ''
-- SELECT DESTTYP, Annr, Name1, BaGruppe FROM [dbo].[itcview_ResplanMember]
-- ResPlandaten
