DROP PROCEDURE IF EXISTS uspGetTanarokNemNaplozottOrarendiOrakForEmail
GO

CREATE PROCEDURE uspGetTanarokNemNaplozottOrarendiOrakForEmail
AS BEGIN
  SET NOCOUNT ON;

  DECLARE  
     @tanarId int 
    ,@intezmenyId int
    ,@tanevId int
    ,@idoszakKezdete datetime 
    ,@idoszakVege datetime = CONVERT (date, GETDATE())
    ,@csakOrarendiOrak bit = 0
    ,@utolsoTanitasiNap datetime

  CREATE TABLE #NemNaplozottResult (
    NyomtatasiNev nvarchar(255)
   ,Email nvarchar(200)
   ,EmailGuid uniqueidentifier
   ,IntezmenyAzonosito nvarchar(30)
   ,IntezmenyNev nvarchar(255)
   ,IntezmenyEmailCim nvarchar(255)
   ,OsszesNemNaplozottTanorak int
   ,MultHetenNemNaplozottHelyettesitesek int
   ,MultHetenNemNaplozottTanorak int
  )

  DROP TABLE IF EXISTS #OrarendiOrak
  CREATE TABLE #OrarendiOrak (
     Datum datetime
    ,HelyettesitoTanarID int
    ,TanarID int
    ,OraTipus nvarchar (200)
  )

  DECLARE cur CURSOR LOCAL FOR
  SELECT 
     alk.ID
    ,alk.C_ALINTEZMENYID
    ,alk.C_ALTANEVID
    ,tv.C_ELSOTANITASINAP
    ,tv.C_UTOLSOTANITASINAP
  FROM T_ALKALMAZOTT alk
    INNER JOIN T_USERPROFILE up ON up.C_FELHASZNALOID = alk.ID AND up.TOROLT = 'F'
    INNER JOIN T_TANEV tv ON tv.ID = up.C_TANEVID AND tv.TOROLT = 'F'
    INNER JOIN T_MUNKAUGYIADATOK ma ON ma.C_ALKALMAZOTTID = alk.ID AND ma.TOROLT = 'F'
    INNER JOIN T_INTEZMENY i ON i.ID = alk.C_ALINTEZMENYID AND i.TOROLT = 'F'
  WHERE CAST(up.C_PROFILE AS XML).value('data(/UserProfile/NemNaplozottTanorakEmail)[1]', 'NVARCHAR(MAX)') = 'True' 
    AND alk.TOROLT = 'F' 
    AND (ma.C_ALKALMAZASMEGSZUNESE >= GETDATE() OR ma.C_ALKALMAZASMEGSZUNESE IS NULL)
    AND tv.C_AKTIV = 'T'

  OPEN cur
  FETCH NEXT FROM cur INTO @tanarId, @intezmenyId, @tanevId, @idoszakKezdete, @utolsoTanitasiNap
  WHILE @@FETCH_STATUS = 0 BEGIN
    
    IF @idoszakVege <= @utolsoTanitasiNap BEGIN
      
      INSERT INTO #OrarendiOrak (
         Datum
        ,HelyettesitoTanarID
        ,TanarID
        ,OraTipus
      )
      EXEC sp_GetOrarend
         @pIntezmenyId = @intezmenyId
        ,@pTanevId = @tanevId
        ,@pIdoszakKezdete = @idoszakKezdete
        ,@pIdoszakVege = @idoszakVege
        ,@pTanarId = @tanarId
        ,@pCsakOrarendiOrak = @csakOrarendiOrak
        ,@pIsNapirend = null
        ,@pOszlopok = N'Datum,HelyettesitoTanarID,TanarID,OraTipus'

      INSERT INTO #NemNaplozottResult (
         NyomtatasiNev
        ,Email
        ,EmailGuid
        ,IntezmenyAzonosito
        ,IntezmenyNev
        ,IntezmenyEmailCim
        ,OsszesNemNaplozottTanorak
        ,MultHetenNemNaplozottHelyettesitesek
        ,MultHetenNemNaplozottTanorak
      ) SELECT 
         fh.C_NYOMTATASINEV
        ,e.C_EMAILCIM
        ,e.C_GUID
        ,i.C_AZONOSITO
        ,ia.C_NEV
        ,ia.C_EMAILCIM
        ,ISNULL(tbl.OsszesNemNaplozottTanorak, 0)
        ,ISNULL(tbl.MultHetenNemNaplozottHelyettesitesek, 0)
        ,ISNULL(tbl.MultHetenNemNaplozottTanorak, 0)
      FROM T_FELHASZNALO fh 
        INNER JOIN T_EMAIL e ON e.C_FELHASZNALOID = fh.ID AND e.TOROLT = 'F'
        OUTER APPLY (
          SELECT 
             COUNT(1) AS OsszesNemNaplozottTanorak
            ,SUM(IIF(oo.HelyettesitoTanarID = @tanarId AND oo.Datum > CONVERT (date, DATEADD(DAY, -7,GETDATE())), 1, 0)) AS MultHetenNemNaplozottHelyettesitesek
            ,SUM(IIF(oo.Datum > CONVERT (date, DATEADD(DAY, -7, GETDATE())), 1, 0)) AS MultHetenNemNaplozottTanorak
          FROM #OrarendiOrak oo
          WHERE oo.OraTipus = 'OrarendiOra'
           AND (oo.TanarID = fh.ID OR oo.HelyettesitoTanarID = fh.ID)
        ) tbl
        INNER JOIN T_INTEZMENYADATOK ia ON ia.C_TANEVID = fh.C_TANEVID
        INNER JOIN T_INTEZMENY i ON i.ID = ia.C_INTEZMENYID
      WHERE e.C_EMAILCIM IS NOT NULL
        AND e.C_GUID IS NOT NULL 
        AND e.C_ISPUBLIC = 'F' 
        AND e.C_ISHIBASANMEGADVA = 'F' 
        AND e.C_ALAPERTELMEZETT = 'T'
        AND fh.ID = @tanarId 

    END    
    FETCH NEXT FROM cur INTO @tanarId, @intezmenyId, @tanevId, @idoszakKezdete, @utolsoTanitasiNap
  END
  CLOSE cur
  DEALLOCATE cur

  SELECT 
     nnr.NyomtatasiNev
    ,nnr.Email
    ,nnr.EmailGuid
    ,nnr.IntezmenyAzonosito
    ,nnr.IntezmenyEmailCim
    ,nnr.IntezmenyNev
    ,nnr.OsszesNemNaplozottTanorak
    ,nnr.MultHetenNemNaplozottTanorak
    ,nnr.MultHetenNemNaplozottHelyettesitesek
  FROM #NemNaplozottResult nnr
  ORDER BY nnr.NyomtatasiNev
END
GO