-- dbo.ep_Vyroba_AdvPlan_ZapisDavkyPrikazu
CREATE PROCEDURE dbo.ep_Vyroba_AdvPlan_ZapisDavkyPrikazu
   @datumStart DATETIME=NULL,
   @casOd TINYINT=NULL
AS

SET NOCOUNT ON

-- HD Consulting Strakonice, T. Buzin
-- !! proceduru neupravujte, bude stejne pregenerovana pri startu pluginu pro planovani

IF OBJECT_ID('tempdb..#TabPrikazyProKapacPlan', 'U') IS NULL
 RETURN

DECLARE
 @errMsg NVARCHAR(500),
 @tranPred INT,
 @majorSqlVer TINYINT,
 @jeSql2022 BIT=0,
 @cnt INT,
 @canCont BIT=0,
 @sql NVARCHAR(max),
 @debugXml XML
DECLARE
 @idSadaDavek INT,
 @idVPr INT,
 @pocetVlastOper INT,
 @dokl INT,
 @alt NCHAR(1),
 @dilec INT,
 @mjEvid NVARCHAR(10),
 @kodZakazModif NVARCHAR(20),
 @idZakazModif INT,
 @idStroj INT=NULL,
 @idKalendarStroje INT,
 @cntOpProZapis INT,
 @doklProZapis INT,
 @altProZapis NCHAR(1),
 @ksZaHod NUMERIC(19,6),
 @kaZaHod NUMERIC(19,6),
 @kusyZad NUMERIC(19,6),
 @mnozKs NUMERIC(19,6),
 @mnozKa NUMERIC(19,6),
 @mnozKsVKA NUMERIC(19,6),
 @mnozKAnaPal NUMERIC(19,6),
 @mnoz NUMERIC(19,6),
 @pocetDavek INT,
 @cisloDavky INT,
 @locId INT,
 @planStartPrikaz DATETIME,
 @planKonecPrikaz DATETIME,
 @secCnt INT,
 @planStart DATETIME,
 @prepoctiPlanStart BIT,
 @planStartEnd DATETIME,
 @planKonec DATETIME,
 @ksZbytek NUMERIC(19,6),
 @cas DATETIME,
 @jeTesto BIT=0
DECLARE
 @idAdvKPl INT,
 @idDavky INT,
 @idVypoctu INT,
 @idPrac INT,
 @podm NVARCHAR(80),
 @idDavkyOld INT,
 @startOdOld DATETIME,
 @poradCislo INT
DECLARE
 @jeUdrzba BIT,
 @pocetHodUdrzba NUMERIC(19,6),
 @idPrPUdrzba INT  -- TabPrPostup.ID z prikazu udrzby

DECLARE @davky TABLE (ID INT NOT NULL, CasOd DATETIME, CasDo DATETIME, Zamek BIT DEFAULT 0, PoradoveCislo INT)



BEGIN TRY
SET @tranPred = @@TRANCOUNT
IF (@tranPred=0)
 BEGIN TRAN


-- cisteni SQL seedu
SET @cnt = ISNULL( (SELECT MAX(ID) FROM dbo.TabAdvKPVypocty), 1)
DBCC CHECKIDENT(TabAdvKPVypocty, RESEED, @cnt)
SET @cnt = ISNULL( (SELECT MAX(ID) FROM dbo.TabAdvKPDavky), 1)
DBCC CHECKIDENT(TabAdvKPDavky, RESEED, @cnt)



SET @majorSqlVer = CONVERT(tinyint, SERVERPROPERTY('ProductMajorVersion')) 
SET @jeSql2022 = CONVERT(bit, IIF(@majorSqlVer>=16, 1, 0))


SELECT @idAdvKPl=ID FROM dbo.TabAdvKapacPlan WHERE Kod=N'99'
SET @idVypoctu=(SELECT TOP(1) ID FROM dbo.TabAdvKPVypocty WHERE IDAdvKapacPlan=@idAdvKPl ORDER BY CisloVypoctu DESC)
IF (@idVypoctu IS NULL)
 BEGIN
  INSERT dbo.TabAdvKPVypocty (IDAdvKapacPlan, CisloVypoctu, Akce, IDLastMzdy) VALUES (@idAdvKPl, 1, 3, 0)
  SET @idVypoctu = SCOPE_IDENTITY()
 END

SET @casOd = ISNULL(@casOd, 0)



SET @idSadaDavek = 1 + ISNULL( (SELECT MAX(_VazbaPlgPlan) FROM dbo.TabAdvKPDavky_EXT WHERE _VazbaPlgPlan IS NOT NULL), 0)


DECLARE c CURSOR LOCAL FOR
 SELECT ID, JeUdrzba, PocetHodin, IDPrPostup FROM #TabPrikazyProKapacPlan
OPEN c
WHILE (1=1)
 BEGIN
  FETCH NEXT FROM c INTO @idVPr, @jeUdrzba, @pocetHodUdrzba, @idPrPUdrzba
  IF (@@FETCH_STATUS<>0) BREAK

  IF (@jeUdrzba=1)
    BEGIN
     SET @idPrPUdrzba = ISNULL(@idPrPUdrzba, 0)
     SET @pocetHodUdrzba = ISNULL(@pocetHodUdrzba, 0)
     IF (@idPrPUdrzba>0) AND (@pocetHodUdrzba>0)
      BEGIN
       IF (@datumStart IS NOT NULL)
         SET @planStart = DATEADD(hour, @casOd, dbo.hf_TruncDate(@datumStart))
        ELSE
         SET @planStart = DATEADD(hour, @casOd, dbo.hf_TruncDate(GETDATE()))         
      
       SELECT @idVPr=IDPrikaz, @doklProZapis=Doklad, @altProZapis=Alt, @idPrac=pracoviste, @idStroj=IDStroje
         FROM dbo.TabPrPostup WHERE ID=@idPrPUdrzba AND IDOdchylkyDo IS NULL
       SELECT @dilec=IdTabKmen, @idZakazModif=IdZakazModif, @kusyZad=Kusy_zad
         FROM dbo.TabPrikaz WHERE ID=@idVPr
       SET @idKalendarStroje = (SELECT IDPlanKalend FROM dbo.TabCisStroju WHERE ID=@idStroj)

       SET @pocetDavek = @pocetHodUdrzba
       SET @cisloDavky = 1

 -- pokud neni nektera davka daneho prikazu uzamcena, smaz je vsechny
 -- jinak nastav aby se planovani toho prikazu pri privnim pruchodu WHILE ukoncilo
/*
       IF NOT EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDPrikaz=@idVPr AND IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1)
         DELETE FROM dbo.TabAdvKPDavky WHERE IDPrikaz=@idVPr AND IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj
        ELSE
         SET @cisloDavky=@pocetDavek
*/
       WHILE (1=1)
        BEGIN
         IF (@cisloDavky>@pocetDavek) BREAK

         DELETE FROM @davky
         IF EXISTS (SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND CasOd>=@planStart AND Zamek=0)
          BEGIN
          ;WITH d AS (
            SELECT ROW_NUMBER() OVER (ORDER BY CasOd) AS AutoInc,
                   ID, CasOd, CasDo, Zamek
             FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND CasOd>=@planStart AND Zamek=0
          )
           INSERT @davky (ID, CasOd, CasDo, Zamek, PoradoveCislo)
            SELECT ID, CasOd, CasDo, Zamek, AutoInc FROM d;
          END
         SET @debugXml = (SELECT * FROM @davky FOR XML AUTO)

     -- begin: test data/casu zahajeni na kalendar stroje
         IF (@idKalendarStroje IS NULL) -- pokud kalendar nemam, per to tam
           BEGIN
            SET @cnt = 0
            WHILE EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @planStart BETWEEN CasOd AND CasDo)
                  AND (@cnt<=3000) -- kvuli zamrznuti
             BEGIN
              SET @planStart = DATEADD(hour, 1, @planStart)
              SET @cnt = @cnt + 1
             END
            SET @planStartEnd = DATEADD(second, -1, DATEADD(hour, 1, @planStart))
           END
          ELSE
           BEGIN -- pokud kalendar mam, zkontroluj jestli neni na dany datum/cas vyjimka
            SET @cnt = 0
            WHILE (EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @planStart BETWEEN CasOd AND CasDo)
                   OR EXISTS(SELECT 1 FROM dbo.TabPlanKalendPol WHERE IDStroje=@idStroj AND @planStart BETWEEN CasOd AND CasDo)
                  )
                  AND (@cnt<=3000) -- kvuli zamrznuti
             BEGIN
              SET @planStart = DATEADD(hour, 1, @planStart)
              SET @cnt = @cnt + 1
             END
            SET @planStartEnd = DATEADD(second, -1, DATEADD(hour, 1, @planStart))
           END
     -- end: test data/casu zahajeni na kalendar stroje


         IF OBJECT_ID('dbo.ep_Vyroba_AdvPlan_ArchivujDavky', 'P') IS NOT NULL
          EXEC dbo.ep_Vyroba_AdvPlan_ArchivujDavky @IDAdvKapacPlan=@idAdvKPl

         SET @podm = N'IdAdvKapacPlan=' + CONVERT(nvarchar, @idAdvKPl)
         EXEC @locId=dbo.hp_NajdiPrvniVolny @Tabulka=N'TabAdvKPDavky', @Atribut=N'LocalID', @Where=@podm, @Odkud=1, @Kam=999999999, @Returnem=1
             
         SET @mnoz = 0
         INSERT dbo.TabAdvKPDavky (IDAdvKapacPlan, LocalID, IDVypoctu, ZpusobVznikuDavkyKP, DatumPripadu, IDPrikaz, Dilec, DokladPrPostup, AltPrPostup,
                                   IDPrac, IDStroje, CasOd, CasDo, Mnozstvi, PrepravniCas, MeziOperCas, DeadTime, BlokaceZdroje, ProcSplneni, Mnozstvi_Zive)
           SELECT @idAdvKPl, @locId, @idVypoctu, 0, @planStart, @idVPr, @dilec, @doklProZapis, @altProZapis, @idPrac, @idStroj, @planStart, @planStartEnd,
                  @mnoz, 0, 0, 0, 1, 0, @mnoz
         SET @idDavky = SCOPE_IDENTITY()
         IF (@idDavky IS NOT NULL)
          BEGIN
           IF NOT EXISTS (SELECT 1 FROM dbo.TabAdvKPDavky_EXT  WHERE ID=@idDavky)
            INSERT dbo.TabAdvKPDavky_EXT (ID) VALUES (@idDavky)
           UPDATE dbo.TabAdvKPDavky_EXT SET _VazbaPlgPlan=@idSadaDavek WHERE ID=@idDavky
          END


  -- begin: posun nasledujicich davek
         DECLARE dav CURSOR LOCAL FOR
          SELECT ID, CasOd, PoradoveCislo
           FROM @davky
         OPEN dav
         WHILE (1=1)
          BEGIN
           FETCH NEXT FROM dav INTO @idDavkyOld, @startOdOld, @poradCislo
           IF (@@FETCH_STATUS<>0) BREAK

           IF (@poradCislo=1)
             SET @startOdOld = DATEADD(hour, 1, @planStart)
            ELSE
             SET @startOdOld = DATEADD(hour, 1, (SELECT CasOd FROM @davky WHERE PoradoveCislo=@poradCislo-1))

           IF (@idKalendarStroje IS NULL)
             BEGIN
              SET @cnt = 0
              WHILE EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @startOdOld BETWEEN CasOd AND CasDo)
                    AND (@cnt<=3000) -- kvuli zamrznuti
               BEGIN
                SET @startOdOld = DATEADD(hour, 1, @startOdOld)
                SET @cnt = @cnt + 1
               END
             END
            ELSE
             BEGIN
              SET @cnt = 0
              WHILE (EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @startOdOld BETWEEN CasOd AND CasDo)
                     OR EXISTS(SELECT 1 FROM dbo.TabPlanKalendPol WHERE IDStroje=@idStroj AND @planStart BETWEEN CasOd AND CasDo)
                    )
                    AND (@cnt<=3000) -- kvuli zamrznuti
               BEGIN
                SET @startOdOld = DATEADD(hour, 1, @startOdOld)
                SET @cnt = @cnt + 1
               END
             END
           UPDATE @davky SET CasOd=@startOdOld, CasDo=DATEADD(second, -1, DATEADD(hour, 1, @startOdOld)) WHERE PoradoveCislo=@poradCislo
           UPDATE dbo.TabAdvKPDavky SET CasOd=@startOdOld, CasDo=DATEADD(second, -1, DATEADD(hour, 1, @startOdOld)) WHERE ID=@idDavkyOld
          END
         CLOSE dav
         DEALLOCATE dav
  -- end: posun nasledujicich davek
  
         SET @cisloDavky = @cisloDavky + 1
        END
        
      END -- @idPrPUdrzba>0
    END
   ELSE
    BEGIN -- @jeUdrzba=0
     SET @pocetVlastOper = ISNULL( (SELECT COUNT(ID) FROM dbo.TabPrPostup WHERE IDPrikaz=@idVPr AND IDOdchylkyDo IS NULL AND priorita=0 AND typ<2), 0)
     SELECT @dilec=IdTabKmen, @idZakazModif=IdZakazModif, @kusyZad=Kusy_zad, @planStart=Plan_zadani, @planKonec=Plan_Ukonceni
        FROM dbo.TabPrikaz WHERE ID=@idVPr
     SET @planStartPrikaz = @planStart
     SET @planKonecPrikaz = @planKonec 

     IF (@planStartPrikaz<GETDATE())
      BEGIN
       SET @secCnt = DATEDIFF(second, @planStart, @planKonec)
       IF (@jeSql2022=1)
         BEGIN
          SET @sql = 'SET @planStartPrikaz = DATEADD(hour, 0, DATETRUNC(hour, GETDATE()))'
          EXEC sp_executesql @sql, N'@planStartPrikaz DATETIME OUT', @planStartPrikaz=@planStartPrikaz OUT
         END
        ELSE
         SET @planStartPrikaz = DATEADD(hour, DATEDIFF(hour, 0, DATEADD(minute, 30, GETDATE())), 0)
       SET @planKonecPrikaz = DATEADD(second, @secCnt, @planStartPrikaz)
      END


     SET @jeTesto=0
     IF EXISTS (SELECT 1 FROM dbo.TabKmenZbozi WHERE ID=@dilec AND SkupZbo=N'701')
      SET @jeTesto=1
     IF (@jeTesto=1)
      CONTINUE

     SELECT @dokl=Doklad, @alt=Alt, @idPrac=pracoviste, @idStroj=IDStroje FROM dbo.TabPrPostup WHERE IDPrikaz=@idVPr AND Odvadeci=1 AND IDOdchylkyDo IS NULL
     SET @idKalendarStroje = (SELECT IDPlanKalend FROM dbo.TabCisStroju WHERE ID=@idStroj)
     SELECT @mjEvid=MJEvidence FROM dbo.TabKmenZbozi WHERE ID=@dilec


     SET @mnoz = 0
     SET @mnozKs = 0
     SET @mnozKa = 0
     SET @mnozKsVKA = 0
     SET @mnozKAnaPal = 0

     SET @mjEvid=LOWER(@mjEvid)
     IF (@mjEvid=N'ka')
      BEGIN
       SET @mnozKsVKA = ISNULL( (SELECT PocetOdvozene FROM dbo.TabMJZbozi WHERE IDKmenZbozi=@dilec AND LOWER(KodMJ1)=N'KA' AND LOWER(KodMJ2)=N'ks'), 1)
       SET @mnozKAnaPal = ISNULL( (SELECT PocetHlavni FROM dbo.TabMJZbozi WHERE IDKmenZbozi=@dilec AND LOWER(KodMJ1)=N'ka' AND LOWER(KodMJ2)=N'pal'), 0)
       SET @mnozKa = @kusyZad
       SET @mnozKs = @kusyZad * @mnozKsVKA
      END
     IF (@mjEvid=N'ks')
      BEGIN
       SET @mnozKs = @kusyZad
      END


     SET @ksZaHod=0
     IF (@idZakazModif IS NOT NULL)
      BEGIN
       SET @kodZakazModif = (SELECT Kod FROM dbo.TabZakazModif WHERE ID=@idZakazModif)
       IF (@kodZakazModif IS NOT NULL)
        BEGIN
         SET @idStroj = (SELECT ID FROM dbo.TabCisStroju WHERE Kod=LEFT(@kodZakazModif,10) AND Blokovano=0)
         IF (@idStroj IS NOT NULL)
          SET @ksZaHod = ISNULL( (SELECT _KapacitaKsZaHod FROM dbo.TabCisStroju_EXT WHERE ID=@idStroj), 0)
        END
      END -- @idZakazModif IS NOT NULL


     SET @doklProZapis=NULL
     SET @altProZapis=NULL
     SET @cntOpProZapis = (SELECT COUNT(ID) FROM dbo.TabPrPostup WHERE IDPrikaz=@idVPr AND IDOdchylkyDo IS NULL AND priorita=0)

     IF (@cntOpProZapis=1)
       BEGIN
        SELECT TOP(1) @doklProZapis=pp.Doklad, @altProZapis=pp.Alt, @ksZaHod=ISNULL(ppe._RychlostOperKsHod,0)
         FROM dbo.TabPrPostup pp LEFT JOIN dbo.TabPrPostup_EXT ppe ON (ppe.ID=pp.ID) 
          WHERE pp.IDPrikaz=@idVPr AND pp.IDOdchylkyDo IS NULL AND pp.priorita=0
       END
      ELSE
       BEGIN
   -- peceni - pozor, diakritika dela obcas problem, proto pres LIKE
        IF EXISTS(SELECT 1 FROM dbo.TabPrPostup WHERE Nazev LIKE N'Pe_ení' AND IDPrikaz=@idVPr AND IDOdchylkyDo IS NULL AND priorita=0)
         SELECT TOP(1) @doklProZapis=pp.Doklad, @altProZapis=pp.Alt, @ksZaHod=ISNULL(ppe._RychlostOperKsHod,0)
          FROM dbo.TabPrPostup pp LEFT JOIN dbo.TabPrPostup_EXT ppe ON (ppe.ID=pp.ID)
           WHERE pp.Nazev LIKE N'Pe_ení' AND pp.IDPrikaz=@idVPr AND pp.IDOdchylkyDo IS NULL AND pp.priorita=0
   -- maceni, diakritika dela obcas problem, proto pres LIKE
        IF EXISTS(SELECT 1 FROM dbo.TabPrPostup WHERE Nazev LIKE N'Má_ení' AND IDPrikaz=@idVPr AND IDOdchylkyDo IS NULL AND priorita=0)
         SELECT TOP(1) @doklProZapis=pp.Doklad, @altProZapis=pp.Alt, @ksZaHod=ISNULL(ppe._RychlostOperKsHod,0)
          FROM dbo.TabPrPostup pp LEFT JOIN dbo.TabPrPostup_EXT ppe ON (ppe.ID=pp.ID)
           WHERE pp.Nazev LIKE N'Má_ení' AND pp.IDPrikaz=@idVPr AND pp.IDOdchylkyDo IS NULL AND pp.priorita=0
   -- maceni, diakritika dela obcas problem, proto pres LIKE
        IF EXISTS(SELECT 1 FROM dbo.TabPrPostup WHERE Nazev LIKE N'Polévání' AND IDPrikaz=@idVPr AND IDOdchylkyDo IS NULL AND priorita=0)
         SELECT TOP(1) @doklProZapis=pp.Doklad, @altProZapis=pp.Alt, @ksZaHod=ISNULL(ppe._RychlostOperKsHod,0)
          FROM dbo.TabPrPostup pp LEFT JOIN dbo.TabPrPostup_EXT ppe ON (ppe.ID=pp.ID)
           WHERE pp.Nazev LIKE N'Polévání' AND pp.IDPrikaz=@idVPr AND pp.IDOdchylkyDo IS NULL AND pp.priorita=0
       END
       


     IF (@ksZaHod<>0) AND (@doklProZapis IS NOT NULL)
      BEGIN
       SET @kaZaHod=0
       IF (@mjEvid=N'ka')
        SET @kaZaHod = ROUND(@ksZaHod/@mnozKsVKA, 2)


   --    SET @planStart=NULL
       IF (COL_LENGTH('dbo.TabPrikaz_EXT', '_PlanZahajeni') IS NOT NULL)
        SET @planStart=(SELECT _PlanZahajeni FROM dbo.TabPrikaz_EXT WHERE ID=@idVPr)    
       IF (@planStart IS NULL)
        BEGIN
   /*
         IF (@jeSql2022=1)
           SET @planStart=DATEADD(hour, 1, DATETRUNC(hour,GETDATE()))
          ELSE
   */
           SET @planStart=DATEADD(hour, 1, CONVERT(datetime, FORMAT(@planStartPrikaz, 'dd.MM.yyyy HH', 'de-de') + N':00:00', 104))
         SET @prepoctiPlanStart=1
        END

       SET @prepoctiPlanStart=0

       IF (@datumStart IS NOT NULL)
        SET @planStart = DATEADD(hour, @casOd, dbo.hf_TruncDate(@datumStart))

   --    SET @planKonec=NULL
       IF (COL_LENGTH('dbo.TabPrikaz_EXT', '_PlanUkonceni') IS NOT NULL)
        SET @planKonec=(SELECT _PlanUkonceni FROM dbo.TabPrikaz_EXT WHERE ID=@idVPr)
       IF (@planKonec IS NULL)
        SET @planKonec=DATEADD(minute, 60.0 * ROUND(@mnozKs/@ksZaHod,2), @planStart)

       IF (@prepoctiPlanStart=1)
   /*
        BEGIN
         IF (@jeSql2022=1)
           SET @planKonec=DATEADD(second, -1, DATEADD(hour, 1, DATETRUNC(hour,@planKonec)))
          ELSE
   */
   --       SET @planStart=DATEADD(hour, 1, CEILING(DATEDIFF(hour, 0, @planKonec)/60.0)*60)
          SET @planStart=DATEADD(hour, 1, CONVERT(datetime, FORMAT(@planKonec, 'dd.MM.yyyy HH', 'de-de') + N':00:00', 104))
   --     END

         
       SET @pocetDavek = CEILING(@mnozKs/@ksZaHod)
       IF (@pocetDavek>0)
        BEGIN
         SET @cisloDavky = 1

   -- pokud neni nektera davka daneho prikazu uzamcena, smaz je vsechny
   -- jinak nastav aby se planovani toho prikazu pri privnim pruchodu WHILE ukoncilo
         IF NOT EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDPrikaz=@idVPr AND IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1)
           DELETE FROM dbo.TabAdvKPDavky WHERE IDPrikaz=@idVPr AND IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj
          ELSE
           SET @cisloDavky=@pocetDavek

         WHILE (1=1)
          BEGIN
           IF (@cisloDavky>@pocetDavek) BREAK

           DELETE FROM @davky
           IF EXISTS (SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND CasOd>=@planStart AND Zamek=0)
            BEGIN
            ;WITH d AS (
              SELECT ROW_NUMBER() OVER (ORDER BY CasOd) AS AutoInc,
                     ID, CasOd, CasDo, Zamek
               FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND CasOd>=@planStart AND Zamek=0
            )
             INSERT @davky (ID, CasOd, CasDo, Zamek, PoradoveCislo)
              SELECT ID, CasOd, CasDo, Zamek, AutoInc FROM d;
            END
           SET @debugXml = (SELECT * FROM @davky FOR XML AUTO)

   -- begin: test data/casu zahajeni na kalendar stroje
           IF (@idKalendarStroje IS NULL) -- pokud kalendar nemam, per to tam
             BEGIN
              SET @cnt = 0
              WHILE EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @planStart BETWEEN CasOd AND CasDo)
                    AND (@cnt<=3000) -- kvuli zamrznuti
               BEGIN
                SET @planStart = DATEADD(hour, 1, @planStart)
                SET @cnt = @cnt + 1
               END
              SET @planStartEnd = DATEADD(second, -1, DATEADD(hour, 1, @planStart))
             END
            ELSE
             BEGIN -- pokud kalendar mam, zkontroluj jestli neni na dany datum/cas vyjimka
              SET @cnt = 0
              WHILE (EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @planStart BETWEEN CasOd AND CasDo)
                     OR EXISTS(SELECT 1 FROM dbo.TabPlanKalendPol WHERE IDStroje=@idStroj AND @planStart BETWEEN CasOd AND CasDo)
                    )
                    AND (@cnt<=3000) -- kvuli zamrznuti
               BEGIN
                SET @planStart = DATEADD(hour, 1, @planStart)
                SET @cnt = @cnt + 1
               END
              SET @planStartEnd = DATEADD(second, -1, DATEADD(hour, 1, @planStart))
             END
   -- end: test data/casu zahajeni na kalendar stroje


           IF (@cisloDavky=@pocetDavek)
            BEGIN
             SET @ksZaHod = @mnozKs - ( @ksZaHod * (@cisloDavky-1))
             SET @kaZaHod = ROUND(@ksZaHod/@mnozKsVKA, 2)
            END
    
           SET @mnoz = CASE @mjEvid WHEN N'ka' THEN @kaZaHod WHEN N'ks' THEN @ksZaHod END

           IF OBJECT_ID('dbo.ep_Vyroba_AdvPlan_ArchivujDavky', 'P') IS NOT NULL
            EXEC dbo.ep_Vyroba_AdvPlan_ArchivujDavky @IDAdvKapacPlan=@idAdvKPl

           SET @podm = N'IdAdvKapacPlan=' + CONVERT(nvarchar, @idAdvKPl)
           EXEC @locId=dbo.hp_NajdiPrvniVolny @Tabulka=N'TabAdvKPDavky', @Atribut=N'LocalID', @Where=@podm, @Odkud=1, @Kam=999999999, @Returnem=1
           
           INSERT dbo.TabAdvKPDavky (IDAdvKapacPlan, LocalID, IDVypoctu, ZpusobVznikuDavkyKP, DatumPripadu, IDPrikaz, Dilec, DokladPrPostup, AltPrPostup,
                                     IDPrac, IDStroje, CasOd, CasDo, Mnozstvi, PrepravniCas, MeziOperCas, DeadTime, BlokaceZdroje, ProcSplneni, Mnozstvi_Zive)
             SELECT @idAdvKPl, @locId, @idVypoctu, 0, @planStart, @idVPr, @dilec, @doklProZapis, @altProZapis, @idPrac, @idStroj, @planStart, @planStartEnd,
                    @mnoz, 0, 0, 0, 1, 0, @mnoz
           SET @idDavky = SCOPE_IDENTITY()
           IF (@idDavky IS NOT NULL)
            BEGIN
             IF NOT EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky_EXT  WHERE ID=@idDavky)
              INSERT dbo.TabAdvKPDavky_EXT (ID) VALUES (@idDavky)
             UPDATE dbo.TabAdvKPDavky_EXT SET _VazbaPlgPlan=@idSadaDavek WHERE ID=@idDavky
            END

           IF NOT EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky_EXT WHERE ID=@idDavky)
            INSERT dbo.TabAdvKPDavky_EXT (ID) VALUES (@idDavky)
           UPDATE dbo.TabAdvKPDavky_EXT SET _MnozstviKs=CASE @mjEvid WHEN N'ka' THEN @ksZaHod WHEN N'ks' THEN @ksZaHod ELSE NULL END WHERE ID=@idDavky

   -- begin: posun nasledujicich davek
           DECLARE dav CURSOR LOCAL FOR
            SELECT ID, CasOd, PoradoveCislo
             FROM @davky
           OPEN dav
           WHILE (1=1)
            BEGIN
             FETCH NEXT FROM dav INTO @idDavkyOld, @startOdOld, @poradCislo
             IF (@@FETCH_STATUS<>0) BREAK

             IF (@poradCislo=1)
               SET @startOdOld = DATEADD(hour, 1, @planStart)
              ELSE
               SET @startOdOld = DATEADD(hour, 1, (SELECT CasOd FROM @davky WHERE PoradoveCislo=@poradCislo-1))

             IF (@idKalendarStroje IS NULL)
               BEGIN
                SET @cnt = 0
                WHILE EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @startOdOld BETWEEN CasOd AND CasDo)
                      AND (@cnt<=3000) -- kvuli zamrznuti
                 BEGIN
                  SET @startOdOld = DATEADD(hour, 1, @startOdOld)
                  SET @cnt = @cnt + 1
                 END
               END
              ELSE
               BEGIN
                SET @cnt = 0
                WHILE (EXISTS(SELECT 1 FROM dbo.TabAdvKPDavky WHERE IDAdvKapacPlan=@idAdvKPl AND IDStroje=@idStroj AND Zamek=1 AND @startOdOld BETWEEN CasOd AND CasDo)
                       OR EXISTS(SELECT 1 FROM dbo.TabPlanKalendPol WHERE IDStroje=@idStroj AND @planStart BETWEEN CasOd AND CasDo)
                      )
                      AND (@cnt<=3000) -- kvuli zamrznuti
                 BEGIN
                  SET @startOdOld = DATEADD(hour, 1, @startOdOld)
                  SET @cnt = @cnt + 1
                 END
               END
             UPDATE @davky SET CasOd=@startOdOld, CasDo=DATEADD(second, -1, DATEADD(hour, 1, @startOdOld)) WHERE PoradoveCislo=@poradCislo
             UPDATE dbo.TabAdvKPDavky SET CasOd=@startOdOld, CasDo=DATEADD(second, -1, DATEADD(hour, 1, @startOdOld)) WHERE ID=@idDavkyOld
            END
           CLOSE dav
           DEALLOCATE dav
   -- end: posun nasledujicich davek


           SET @planStart = DATEADD(hour, 1, @planStart)
           SET @cisloDavky = @cisloDavky + 1
          END
        END
      END   -- @ksZaHod<>0
    END -- @jeUdrzba=0
    
 END
CLOSE c
DEALLOCATE c



IF (@tranPred=0) AND (@@TRANCOUNT>0)
 COMMIT TRAN

END TRY
BEGIN CATCH
 IF (@tranPred=0) AND (@@TRANCOUNT>0)
  ROLLBACK TRAN
 SET @errMsg = ERROR_MESSAGE()
END CATCH