76 lines
No EOL
2.8 KiB
Transact-SQL
76 lines
No EOL
2.8 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetCovidFertozott
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetCovidFertozott
|
|
@pTanevId int
|
|
,@pTanarId int = NULL
|
|
|
|
AS BEGIN
|
|
|
|
-- Tanárnak csak a saját osztályát vagy ahol TTF szerint tanít
|
|
IF @pTanarId IS NOT NULL
|
|
BEGIN
|
|
SELECT DISTINCT
|
|
x.FelhasznaloNev
|
|
,x.OktatasiAzonosito
|
|
,3 AS 'FelhasznaloTipus'
|
|
,x.TanuloOsztalya
|
|
FROM (
|
|
SELECT
|
|
f.C_NYOMTATASINEV AS 'FelhasznaloNev'
|
|
,f.C_OKTATASIAZONOSITO AS 'OktatasiAzonosito'
|
|
,ocs.C_NEV AS 'TanuloOsztalya'
|
|
FROM T_FOGLALKOZAS fog
|
|
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = fog.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_OSZTALYCSOPORTID = ocs.ID AND tcs.C_BELEPESDATUM < GETDATE() AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= GETDATE())
|
|
INNER JOIN T_TANULO t ON t.ID = tcs.C_TANULOID
|
|
INNER JOIN T_FELHASZNALO f ON f.ID = t.ID
|
|
WHERE fog.TOROLT = 'F'
|
|
AND fog.C_TANEVID = @pTanevId
|
|
AND fog.C_TANARID = @pTanarId
|
|
AND f.C_ISCOVIDFERTOZOTT = 'T'
|
|
UNION ALL
|
|
SELECT
|
|
f.C_NYOMTATASINEV AS 'FelhasznaloNev'
|
|
,f.C_OKTATASIAZONOSITO AS 'OktatasiAzonosito'
|
|
,ocs.C_NEV AS 'TanuloOsztalya'
|
|
FROM T_OSZTALY o
|
|
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = o.ID
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_OSZTALYCSOPORTID = o.ID AND tcs.C_BELEPESDATUM < GETDATE() AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= GETDATE())
|
|
INNER JOIN T_TANULO t ON t.ID = tcs.C_TANULOID
|
|
INNER JOIN T_FELHASZNALO f ON f.ID = t.ID
|
|
WHERE o.TOROLT = 'F'
|
|
AND o.C_ALTANEVID = @pTanevId
|
|
AND o.C_OSZTALYFONOKID = @pTanarId
|
|
AND f.C_ISCOVIDFERTOZOTT = 'T'
|
|
) AS x
|
|
ORDER BY x.FelhasznaloNev,x.OktatasiAzonosito
|
|
END
|
|
ELSE -- Admin dashboardról mindenkit visszaadunk
|
|
BEGIN
|
|
SELECT
|
|
f.ID AS 'FelhasznaloId'
|
|
,f.C_NYOMTATASINEV AS 'FelhasznaloNev'
|
|
,f.C_OKTATASIAZONOSITO AS 'OktatasiAzonosito'
|
|
,CASE WHEN a.ID IS NOT NULL THEN 2
|
|
WHEN t.ID IS NOT NULL THEN 3
|
|
ELSE NULL
|
|
END AS 'FelhasznaloTipus'
|
|
,CASE WHEN t.ID IS NOT NULL THEN ocs.C_NEV
|
|
ELSE NULL
|
|
END AS 'TanuloOsztalya'
|
|
FROM T_FELHASZNALO f
|
|
LEFT JOIN T_ALKALMAZOTT a ON a.ID = f.ID
|
|
LEFT JOIN T_TANULO t
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = t.ID AND tcs.C_BELEPESDATUM < GETDATE() AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= GETDATE())
|
|
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_OSZTALY o ON o.ID = tcs.C_OSZTALYCSOPORTID
|
|
ON t.ID = f.ID
|
|
WHERE f.TOROLT = 'F'
|
|
AND f.C_TANEVID = @pTanevId
|
|
AND f.C_ISCOVIDFERTOZOTT = 'T'
|
|
ORDER BY FelhasznaloNev, OktatasiAzonosito
|
|
END
|
|
|
|
END
|
|
GO |