249 lines
10 KiB
Transact-SQL
249 lines
10 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetPedagogusAltalAdottErtekelesek
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetPedagogusAltalAdottErtekelesek
|
|
@pTanarId int
|
|
,@pEvkozi int
|
|
,@pFelevi int
|
|
,@pEvvegi int
|
|
,@pTanevId int
|
|
,@pFelevVegeNaptipusId int
|
|
,@pErtekelesmodSuly bit = 0
|
|
,@pIntezmenyId int
|
|
,@pFeladatKategoriaId int = NULL
|
|
AS BEGIN
|
|
SET NOCOUNT ON
|
|
DECLARE @osztalyId int = 0
|
|
,@tantargyId int = 0
|
|
,@rowId int = 0
|
|
,@maxCharNumErtekelesSzoveg int = 20
|
|
,@felevVege date
|
|
,@isVegzos char
|
|
,@VegzosUtolsoNap datetime
|
|
,@UtolsoNap datetime
|
|
,@VizsgaltNap datetime
|
|
,@UtolsoNapTanev datetime
|
|
,@UtolsoNapTanevRendje datetime
|
|
|
|
SELECT @felevVege = ISNULL(C_DATUM,GETDATE()) FROM T_TANEVRENDJE WHERE C_TANEVID = @pTanevId AND C_NAPTIPUSA = @pFelevVegeNaptipusId
|
|
SELECT TOP 1 @UtolsoNapTanevRendje = C_DATUM FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA = 1395 AND C_TANEVID = @pTanevId AND TOROLT = 'F'
|
|
SELECT TOP 1 @UtolsoNapTanev = C_UTOLSOTANITASINAP FROM T_TANEV_OSSZES WHERE ID = @pTanevId AND TOROLT = 'F'
|
|
--súlyok
|
|
SELECT
|
|
tas.C_ERTEKELESMODID AS ID
|
|
,dib.C_NAME
|
|
,C_SULY
|
|
,CAST(C_SULY AS nvarchar) + '%' AS C_SULYSZAZALEK, ' (' + NCHAR(97 + ROW_NUMBER() OVER(ORDER BY dib.C_ORDER ASC) - 1) + ')' AS Row#
|
|
INTO #tempTanariAtlagsuly
|
|
FROM T_TANARIATLAGSULY_OSSZES tas
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES dib ON dib.ID = tas.C_ERTEKELESMODID AND dib.C_TANEVID=tas.C_TANEVID AND dib.TOROLT='F'
|
|
WHERE tas.TOROLT='F' AND tas.C_TANEVID=@pTanevId
|
|
|
|
--megjelenítendő tgy-ocs-evg hármasok
|
|
SELECT
|
|
ROW_NUMBER () OVER (ORDER BY TARGYKATEGSORREND, EVFOLYAMSORREND, OSZTALYCSOPORTNEV, TARGYNEV) AS SORSZAM
|
|
,TANTARGYID
|
|
,OSZTALYID
|
|
,CAST(ROW_NUMBER ( ) OVER (ORDER BY TARGYKATEGSORREND, EVFOLYAMSORREND, OSZTALYCSOPORTNEV, TARGYNEV) AS nvarchar(10)) + '. ' + NEV AS NEV
|
|
INTO #tempFoglalkozasok
|
|
FROM (
|
|
SELECT
|
|
tgy.ID AS TANTARGYID
|
|
,ocs.ID AS OSZTALYID
|
|
,dibKateg.C_ORDER AS TARGYKATEGSORREND
|
|
,dibEvf.C_ORDER AS EVFOLYAMSORREND
|
|
,ocs.C_NEV AS OSZTALYCSOPORTNEV
|
|
,tgy.C_NEV AS TARGYNEV
|
|
,ocs.C_NEV + '-' + tgy.C_NEV AS NEV
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = f.C_OSZTALYCSOPORTID AND (ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL) AND ocs.TOROLT = 'F'
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = f.C_TANTARGYID AND tgy.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES dibKateg ON dibKateg.ID = tgy.C_TARGYKATEGORIA AND dibKateg.C_TANEVID=f.C_TANEVID AND dibKateg.TOROLT='F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES AS dibEvf ON dibEvf.ID = ocs.C_EVFOLYAMTIPUSA AND dibEvf.C_TANEVID=f.C_TANEVID AND dibEvf.TOROLT='F'
|
|
WHERE f.TOROLT = 'F' AND f.C_TANARID = @pTanarId AND f.C_TANEVID=@pTanevId
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
tgy.ID AS TANTARGYID
|
|
,ocs.ID AS OSZTALYID
|
|
,dibKateg.C_ORDER AS TARGYKATEGSORREND
|
|
,dibEvf.C_ORDER AS EVFOLYAMSORREND
|
|
,ocs.C_NEV AS OSZTALYCSOPORTNEV
|
|
,tgy.C_NEV AS TARGYNEV
|
|
,ocs.C_NEV + '-' + tgy.C_NEV AS NEV
|
|
FROM T_TANULOERTEKELES_OSSZES te
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = te.C_OSZTALYCSOPORTID AND (ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL) AND ocs.TOROLT = 'F'
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = te.C_TANTARGYID AND tgy.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES AS dibKateg ON dibKateg.ID = tgy.C_TARGYKATEGORIA AND dibKateg.C_TANEVID = tgy.C_TANEVID AND dibKateg.TOROLT='F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES AS dibEvf ON dibEvf.ID = ocs.C_EVFOLYAMTIPUSA AND dibEvf.C_TANEVID = ocs.C_TANEVID AND dibEvf.TOROLT='F'
|
|
WHERE te.TOROLT = 'F' AND te.C_ERTEKELOID = @pTanarId AND te.C_TANEVID = @pTanevId
|
|
) AS FOGLALKOZASOK
|
|
|
|
CREATE TABLE #tempErtekeles (
|
|
ID int
|
|
,TANULOID int
|
|
,NEV nvarchar(255)
|
|
,OKTAZON nvarchar(255)
|
|
,C_BELEPESDATUM nvarchar(15)
|
|
,C_KILEPESDATUM nvarchar(15)
|
|
,OSZTID int
|
|
,ERTEKELESDATUMA nvarchar(255)
|
|
,ERTEKELESTIPUSA int
|
|
,ERTEKELESMODJA int
|
|
,ERTEKELES nvarchar(MAX)
|
|
,OSZTALYZAT int
|
|
,HONAP int
|
|
)
|
|
SELECT * FROM #tempFoglalkozasok
|
|
ORDER BY SORSZAM;
|
|
|
|
DECLARE foglCur CURSOR FOR
|
|
SELECT TANTARGYID,OSZTALYID FROM #tempFoglalkozasok
|
|
|
|
OPEN foglCur
|
|
FETCH NEXT FROM foglCur INTO @tantargyId,@osztalyId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
DELETE FROM #tempErtekeles
|
|
SET @isVegzos = (SELECT TOP 1 C_VEGZOSEVFOLYAM FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @osztalyId)
|
|
SET @VegzosUtolsoNap = (SELECT dbo.fnGetOsztalyVegzosTanitasiNap(@osztalyId, @pIntezmenyId, @pTanevId))
|
|
SET @UtolsoNap = IIF(@isVegzos = 'T', COALESCE(@VegzosUtolsoNap, @UtolsoNapTanevRendje, @UtolsoNapTanev), ISNULL(@UtolsoNapTanevRendje, @UtolsoNapTanev))
|
|
SET @VizsgaltNap = (SELECT IIF(@UtolsoNap > GETDATE(), GETDATE(), @UtolsoNap))
|
|
|
|
|
|
|
|
INSERT INTO #tempErtekeles
|
|
SELECT DISTINCT
|
|
te.ID
|
|
,tcs.C_TANULOID AS TANULOID
|
|
,fh.C_NYOMTATASINEV AS NEV
|
|
,fh.C_OKTATASIAZONOSITO AS OKTAZON
|
|
,COALESCE(FORMAT(tcs.C_BELEPESDATUM, 'yyyy. MM. dd'), '') AS C_BELEPESDATUM
|
|
,COALESCE(FORMAT(tcs.C_KILEPESDATUM, 'yyyy. MM. dd'), '') AS C_KILEPESDATUM
|
|
,tcs.C_OSZTALYCSOPORTID AS OSZTID
|
|
,te.C_DATUM AS ERTEKELESDATUMA
|
|
,te.C_TIPUSID AS ERTEKELESTIPUSA
|
|
,te.C_ERTEKELESMODID AS ERTEKELESMODJA
|
|
,(IIF(C_TIPUSID NOT IN (@pEvkozi, @pFelevi, @pEvvegi),'{','') +
|
|
CASE WHEN dibJegy.C_VALUE IS NOT NULL
|
|
THEN CONVERT(nvarchar, dibJegy.C_VALUE)
|
|
WHEN C_ERTEKELESSZOVEG IS NOT NULL
|
|
THEN '['+ IIF (LEN(C_ERTEKELESSZOVEG) > @maxCharNumErtekelesSzoveg ,STUFF(C_ERTEKELESSZOVEG, @maxCharNumErtekelesSzoveg, 1000000, '...'),C_ERTEKELESSZOVEG)+']'
|
|
ELSE CONVERT(nvarchar, C_ERTEKELESSZAZALEK) + '%' END +
|
|
IIF(C_TIPUSID NOT IN (@pEvkozi, @pFelevi, @pEvvegi),'}' ,'' ) +
|
|
IIF(@pErtekelesmodSuly = 1 AND C_TIPUSID = @pEvkozi,TANARIATLAGSULY.Row#,'')
|
|
) AS ERTEKELES
|
|
,dibJegy.C_VALUE AS OSZTALYZAT
|
|
,CASE
|
|
WHEN MONTH(C_DATUM) >= 9 THEN 100
|
|
WHEN MONTH(C_DATUM) > 1 AND MONTH(C_DATUM) < 9 THEN 200
|
|
WHEN C_DATUM > @felevVege THEN 200
|
|
ELSE 100 END + MONTH(C_DATUM) AS HONAP
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = tcs.C_TANULOID
|
|
LEFT JOIN T_TANULOERTEKELES_OSSZES te ON te.C_TANTARGYID = @tantargyId AND te.C_TANULOID = tcs.C_TANULOID AND te.TOROLT = 'F' AND te.C_TANEVID = @pTanevId AND te.C_ERTEKELOID = @pTanarId
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES AS dibJegy ON dibJegy.ID = te.C_ERTEKELESOSZTALYZATID AND dibJegy.TOROLT = 'F' AND dibJegy.C_TANEVID = @pTanevId
|
|
LEFT JOIN #tempTanariAtlagsuly AS TANARIATLAGSULY on TANARIATLAGSULY.ID = te.C_ERTEKELESMODID
|
|
WHERE tcs.C_TANEVID = @pTanevId
|
|
AND tcs.C_OSZTALYCSOPORTID = @osztalyId
|
|
AND tcs.TOROLT = 'F'
|
|
|
|
SELECT
|
|
CAST(ROW_NUMBER () OVER (ORDER BY NEV) AS nvarchar(10)) + '.' AS '#'
|
|
,RESULT.*
|
|
FROM (
|
|
SELECT
|
|
PIVOTERTEKEL.TANULOID AS ID
|
|
,NEV AS 'Név'
|
|
,OKTAZON AS 'Okt. azonosító'
|
|
,BESOROLAS AS 'Csoport tagja'
|
|
,[109] AS Szeptember
|
|
,[110] AS Október
|
|
,[111] AS November
|
|
,[112] AS December
|
|
,[101] AS 'Január / I.'
|
|
,FELEV.JEGY AS [I. félév$@8]
|
|
,[201] AS 'Január / II.'
|
|
,[202] AS Február
|
|
,[203] AS Március
|
|
,[204] AS Április
|
|
,[205] AS Május
|
|
,[206] AS Június
|
|
,EVVEGE.JEGY AS [II. félév$@9]
|
|
,ATLAG.SULYOZOTTATLAG AS 'Átlag'
|
|
FROM (
|
|
SELECT DISTINCT
|
|
T1.NEV
|
|
,T1.OKTAZON
|
|
,T1.C_BELEPESDATUM + ' - ' + T1.C_KILEPESDATUM AS BESOROLAS
|
|
,STUFF ((
|
|
SELECT ', ' + ISNULL(T2.ERTEKELES, '')
|
|
FROM #tempErtekeles AS T2
|
|
WHERE T1.TANULOID = T2.TANULOID AND T1.HONAP = T2.HONAP
|
|
AND T1.ERTEKELESTIPUSA NOT IN (@pFelevi, @pEvvegi)
|
|
AND T2.ERTEKELESTIPUSA NOT IN (@pFelevi, @pEvvegi)
|
|
ORDER BY ERTEKELESDATUMA
|
|
FOR XML PATH ('')
|
|
), 1, 2, '') AS ERTEKELESEK
|
|
,T1.HONAP
|
|
,T1.TANULOID
|
|
FROM #tempErtekeles AS T1
|
|
) AS ERDEMJEGYEK
|
|
PIVOT (MAX(ERTEKELESEK) FOR HONAP IN ([109], [110], [111], [112], [101], [201], [202], [203], [204], [205], [206])) AS PIVOTERTEKEL
|
|
LEFT JOIN (
|
|
SELECT
|
|
TANULOID
|
|
,AVG(OSZTALYZAT) AS JEGY
|
|
FROM #tempErtekeles
|
|
WHERE ERTEKELESTIPUSA = @pFelevi
|
|
GROUP BY TANULOID
|
|
) AS FELEV ON PIVOTERTEKEL.TANULOID = FELEV.TANULOID
|
|
LEFT JOIN (
|
|
SELECT
|
|
TANULOID
|
|
,AVG(OSZTALYZAT) AS JEGY
|
|
FROM #tempErtekeles
|
|
WHERE ERTEKELESTIPUSA = @pEvvegi
|
|
GROUP BY TANULOID
|
|
) AS EVVEGE ON PIVOTERTEKEL.TANULOID = EVVEGE.TANULOID
|
|
LEFT JOIN (
|
|
SELECT
|
|
TANULOID
|
|
,ROUND(AVG(CAST(OSZTALYZAT AS FLOAT)), 2) AS ATLAG
|
|
,ROUND(SUM(OSZTALYZAT * ISNULL(T_TANARIATLAGSULY.C_SULY, 100) / 100.0) / SUM(ISNULL(T_TANARIATLAGSULY.C_SULY, 100) / 100.0), 2) AS SULYOZOTTATLAG
|
|
FROM #tempErtekeles AS tempErtekeles
|
|
LEFT JOIN T_TANARIATLAGSULY on T_TANARIATLAGSULY.C_ERTEKELESMODID = tempErtekeles.ERTEKELESMODJA AND T_TANARIATLAGSULY.TOROLT = 'F' AND T_TANARIATLAGSULY.C_TANEVID = @pTanevId
|
|
WHERE ERTEKELESTIPUSA = @pEvkozi AND OSZTALYZAT IS NOT NULL
|
|
GROUP BY TANULOID
|
|
) AS ATLAG ON PIVOTERTEKEL.TANULOID = ATLAG.TANULOID
|
|
) AS RESULT
|
|
ORDER BY NEV;
|
|
|
|
FETCH NEXT FROM foglCur INTO @tantargyId,@osztalyId
|
|
END
|
|
|
|
CLOSE foglCur
|
|
DEALLOCATE foglCur
|
|
|
|
SELECT
|
|
ID
|
|
,Row# AS 'Jelmagyarázat'
|
|
,C_NAME AS 'Értékelésmód'
|
|
,C_SULYSZAZALEK AS 'Értékelés súlyozása'
|
|
FROM #tempTanariAtlagsuly
|
|
ORDER BY Row#;
|
|
|
|
-- Iktatás adatok
|
|
SELECT
|
|
f.ID AS PedagogusId
|
|
,f.C_OKTATASIAZONOSITO AS PedagogusOktAzon
|
|
,mua.C_FELADATELLATASIHELYID AS FeladatEllatasiHelyId
|
|
FROM T_FELHASZNALO_OSSZES AS f
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES AS mua ON mua.C_ALKALMAZOTTID = f.ID AND mua.TOROLT='F'
|
|
WHERE f.ID = @pTanarId AND f.C_TANEVID = @pTanevId AND f.TOROLT='F'
|
|
END
|
|
GO
|