/* ==================================================================================== */
/* Description: <Dinamikus sql az emailek (T_EMAIL) lekérdezésére (és/vagy exportjára)> */
/* ==================================================================================== */
DROP PROCEDURE IF EXISTS dbo.uspGetEmail
GO

CREATE PROCEDURE dbo.uspGetEmail
   @pTanevId int
  ,@pFelhasznaloId int = NULL
  ,@pFelhasznaloTipus int = NULL
  ,@pGondviseloId int = NULL
  ,@pIsAlapertelmezett char(1) = NULL
  ,@pIsPublic char(1) = NULL
  ,@pEmailTipusa int = NULL
  ,@pIsHibasanMegadva char(1) = NULL

AS
BEGIN  
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N''

  /* SELECT part */
  SET @sql = N'
  SELECT DISTINCT tmp.* FROM (
    SELECT
       e.ID
      ,e.C_EMAILCIM AS EmailCim
      ,ISNULL(g.C_NEV, f.C_NYOMTATASINEV) AS FelhasznaloNev
      ,CASE WHEN g.ID IS NULL THEN f.C_OKTATASIAZONOSITO ELSE NULL END AS OktatasiAzonosito
      ,CASE WHEN g.ID IS NOT NULL THEN f.C_NYOMTATASINEV ELSE NULL END AS TanuloNev
      ,CASE WHEN g.ID IS NOT NULL THEN f.C_OKTATASIAZONOSITO ELSE NULL END AS TanuloOktatasiAzonosito
      ,CASE
        WHEN g.ID IS NOT NULL THEN 4
        WHEN t.ID IS NOT NULL THEN 3
        WHEN a.ID IS NOT NULL THEN 2 ELSE NULL END AS FelhasznaloTipus
      ,e.C_EMAILTIPUSA AS EmailTipusa
      ,e.C_ALAPERTELMEZETT AS IsAlapertelmezett
      ,e.C_ISPUBLIC AS IsPublikus
      ,e.C_ISHIBASANMEGADVA AS IsHibasanMegadva
    FROM T_EMAIL_OSSZES e
      INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = e.C_FELHASZNALOID AND f.TOROLT = ''F''
      LEFT JOIN T_GONDVISELO_OSSZES g ON g.ID = e.C_GONDVISELOID AND g.C_TANULOID = f.ID AND g.TOROLT = ''F''
      LEFT JOIN T_TANULO_OSSZES t ON t.ID = f.ID AND e.C_GONDVISELOID IS NULL AND t.TOROLT = ''F''
      LEFT JOIN T_ALKALMAZOTT_OSSZES a ON a.ID = f.ID AND e.C_GONDVISELOID IS NULL AND a.TOROLT = ''F''
  '

  /* Inner WHERE part */
  SET @sql += N'
    WHERE e.TOROLT = ''F''
      AND e.C_TANEVID = @pTanevId
      ' + IIF(@pFelhasznaloId IS NULL, N'', N' AND f.ID = @pFelhasznaloId') + N'
      ' + IIF(@pGondviseloId IS NULL, N'', N' AND g.ID = @pGondviseloId AND e.C_ALAPERTELMEZETT = ''T''') + N'
      ' + IIF(@pIsAlapertelmezett IS NULL, N'', N' AND e.C_ALAPERTELMEZETT = @pIsAlapertelmezett') + N'
      ' + IIF(@pIsPublic IS NULL, N'', N' AND e.C_ISPUBLIC = @pIsPublic') + N'
      ' + IIF(@pEmailTipusa IS NULL, N'', N' AND e.C_EMAILTIPUSA = @pEmailTipusa') + N'
      ' + IIF(@pIsHibasanMegadva IS NULL, N'', N' AND e.C_ISHIBASANMEGADVA = @pIsHibasanMegadva') + N'
  ) AS tmp
  '

  /* Outer WHERE part */
  IF (@pFelhasznaloTipus IS NOT NULL)
  SET @sql += N' WHERE tmp.FelhasznaloTipus = @pFelhasznaloTipus'

  /* SORTING & PAGING part */
  SET @sql += N'
  ORDER BY tmp.FelhasznaloTipus, tmp.FelhasznaloNev
  '

  EXEC sp_executesql @sql, N'
     @pTanevId int
    ,@pFelhasznaloId int
    ,@pFelhasznaloTipus int
    ,@pGondviseloId int
    ,@pIsAlapertelmezett char(1)
    ,@pIsPublic char(1)
    ,@pEmailTipusa int
    ,@pIsHibasanMegadva char(1)'
    ,@pTanevId = @pTanevId
    ,@pFelhasznaloId = @pFelhasznaloId
    ,@pFelhasznaloTipus = @pFelhasznaloTipus
    ,@pGondviseloId = @pGondviseloId
    ,@pIsAlapertelmezett = @pIsAlapertelmezett
    ,@pIsPublic = @pIsPublic
    ,@pEmailTipusa = @pEmailTipusa
    ,@pIsHibasanMegadva = @pIsHibasanMegadva

END 
GO