89 lines
No EOL
3.3 KiB
Transact-SQL
89 lines
No EOL
3.3 KiB
Transact-SQL
/* ==================================================================================== */
|
|
/* 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 |