266 lines
14 KiB
Transact-SQL
266 lines
14 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS sp_GetDokumentumMulasztasok
|
|
GO
|
|
|
|
-- ===========================================================================
|
|
-- Description: Dokumentumokban használt mulasztás összesítő osztály alapján
|
|
-- ===========================================================================
|
|
CREATE PROCEDURE sp_GetDokumentumMulasztasok
|
|
@osztalyId int
|
|
,@tanevId int
|
|
,@elmeletiOraPercben int = 45
|
|
,@gyakorlatiOraPercben int = 45
|
|
,@ertekelesTipus int
|
|
,@iskolaErdekuSzamit bit
|
|
,@isMuveszetOktatasi bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
CREATE TABLE #Tanulo (
|
|
TanuloId int
|
|
,TanuloCsoportId int
|
|
,BelepesDatum date
|
|
,KilepesDatum date
|
|
,PRIMARY KEY (TanuloId, BelepesDatum)
|
|
)
|
|
|
|
INSERT INTO #Tanulo
|
|
SELECT
|
|
TanuloId
|
|
,TanuloCsoportId
|
|
,BelepesDatum
|
|
,KilepesDatum
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T')
|
|
|
|
DECLARE
|
|
@Idoszak int
|
|
,@IdoszakElsoNap int
|
|
,@IdoszakKezdete date
|
|
,@IdoszakVege date
|
|
|
|
SET @Idoszak =
|
|
CASE @ertekelesTipus
|
|
WHEN 1519 THEN 1400 --félévi
|
|
WHEN 1520 THEN 1395 --év végi
|
|
WHEN 1522 THEN 1403 --I. negyedévi
|
|
WHEN 1524 THEN 1400 --II. negyedévi
|
|
WHEN 1523 THEN 1404 --III. negyedévi
|
|
END
|
|
|
|
SET @IdoszakElsoNap =
|
|
CASE @ertekelesTipus
|
|
WHEN 1519 THEN 1394 --félévi
|
|
WHEN 1520 THEN 1394 --év végi
|
|
WHEN 1522 THEN 1394 --I. negyedévi
|
|
WHEN 1524 THEN 1403 --II. negyedévi
|
|
WHEN 1523 THEN 1400 --III. negyedévi
|
|
END
|
|
|
|
CREATE TABLE #SzurtCsoportTipus (
|
|
CsoportTipusId int PRIMARY KEY
|
|
)
|
|
|
|
IF @isMuveszetOktatasi = 0 BEGIN
|
|
INSERT INTO #SzurtCsoportTipus
|
|
SELECT ID
|
|
FROM fnGetTanoraiCeluCsoportTipusok(@tanevId)
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO #SzurtCsoportTipus (CsoportTipusId) VALUES
|
|
(1067) -- Alapf. müv. okt. zeneművészeti csoport - egyéni főtanszak
|
|
,(1068) -- Alapf. müv. okt. zeneművészeti csoport - csoportos főtanszak
|
|
,(1069) -- Alapf. müv. okt. képző- és iparművészeti csoport
|
|
,(1070) -- Alapf. müv. okt. táncművészeti csoport
|
|
,(1071) -- Alapf. müv. okt. szín- és bábművészeti csoport
|
|
,(6756) -- Alapf. műv. okt. zeneművészeti csoport - csoportos kötelezően választható
|
|
,(6757) -- Alapf. műv. okt. zeneművészeti csoport - egyéni kötelezően választható
|
|
,(6758) -- Alapf. műv. okt. zeneművészeti csoport - csoportos kötelező
|
|
,(6759) -- Alapf. műv. okt. zeneművészeti csoport - egyéni kötelező
|
|
,(6760) -- Alapf. műv. okt. zeneművészeti csoport - csoportos választható
|
|
,(6761) -- Alapf. műv. okt. zeneművészeti csoport - egyéni választható
|
|
END
|
|
|
|
SET @IdoszakKezdete = (SELECT TOP 1 C_DATUM FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA = @IdoszakElsoNap AND C_TANEVID = @tanevId AND TOROLT='F')
|
|
SET @IdoszakVege = (SELECT TOP 1 C_DATUM FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA = @Idoszak AND C_TANEVID = @tanevId AND TOROLT='F')
|
|
|
|
SELECT
|
|
t.TANULOID tanuloid
|
|
,t.TanuloCsoportId tanulocsoportid
|
|
,ISNULL(mu.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(mu.[hianyzasIgazoltTanoraiGyakorlati], 0)
|
|
+ ISNULL(kp.kesesIgazoltElmeletiOraban,0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0) AS Igazolt
|
|
,ISNULL(mu.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(mu.[hianyzasIgazolatlanTanoraiGyakorlati], 0)
|
|
+ ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) AS Igazolatlan
|
|
,ISNULL(mu.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazoltElmeletiOraban,0) AS ElmeletIgazolt
|
|
,ISNULL(mu.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) AS ElmeletIgazolatlan
|
|
,ISNULL(mu.[hianyzasIgazoltTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0) AS GyakorlatiIgazolt
|
|
,ISNULL(mu.[hianyzasIgazolatlanTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) AS GyakorlatiIgazolatlan
|
|
,ISNULL(mu.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazoltElmeletiOraban,0)
|
|
+ ISNULL(mu.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) AS ElmeletOsszes
|
|
,ISNULL(mu.[hianyzasIgazoltTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0)
|
|
+ ISNULL(mu.[hianyzasIgazolatlanTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) AS GyakorlatOsszes
|
|
,ISNULL(mu.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(mu.[hianyzasIgazoltTanoraiGyakorlati], 0)
|
|
+ ISNULL(kp.kesesIgazoltElmeletiOraban,0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0)
|
|
+ ISNULL(mu.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(mu.[hianyzasIgazolatlanTanoraiGyakorlati], 0)
|
|
+ ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) AS Osszes
|
|
FROM #Tanulo t
|
|
LEFT JOIN (
|
|
SELECT
|
|
tanuloId
|
|
,tanulocsoportid
|
|
,[hianyzasIgazoltTanoraiElmeleti]
|
|
,[hianyzasIgazoltTanoraiGyakorlati]
|
|
,[hianyzasIgazolatlanTanoraiElmeleti]
|
|
,[hianyzasIgazolatlanTanoraiGyakorlati]
|
|
FROM (
|
|
SELECT
|
|
x.tanuloId, x.tanulocsoportid
|
|
,'Hianyzas' +
|
|
CASE isIgazolt WHEN 'T' THEN 'Igazolt' WHEN 'F' THEN 'Igazolatlan' END +
|
|
CASE isTanorai WHEN 'T' THEN 'Tanorai' WHEN 'F' THEN 'TanoranKivuli' END +
|
|
CASE isGyakorlati WHEN 'T' THEN 'Gyakorlati' WHEN 'F' THEN 'Elmeleti' END
|
|
AS piv
|
|
,cnt
|
|
FROM (
|
|
SELECT
|
|
mu.C_ORATANULOIID AS tanuloId
|
|
,tanulok.TanuloCsoportId
|
|
,mu.C_TIPUS mulasztasTipus
|
|
,mu.C_IGAZOLT isIgazolt
|
|
,IIF((@isMuveszetOktatasi = 0 AND cs.ID IS NULL), 'T', IIF((@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL),'T','F')) AS isTanorai
|
|
,IIF(t.C_GYAKORLATI='T', 'T', 'F') as isGyakorlati
|
|
,COUNT(1) AS cnt
|
|
FROM T_TANULOMULASZTAS_OSSZES mu
|
|
INNER JOIN #Tanulo tanulok ON tanulok.TanuloId = mu.C_ORATANULOIID
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = mu.C_TANITASIORAKID
|
|
INNER JOIN T_TANTARGY_OSSZES t on t.id=tn.C_TANTARGYID
|
|
LEFT JOIN (
|
|
SELECT cs.ID
|
|
FROM T_CSOPORT_OSSZES cs
|
|
INNER JOIN T_CSOPORTTIPUS_OSSZES cst On cst.ID = cs.C_TIPUSA AND cst.C_ALTANEVID = cs.C_ALTANEVID AND cst.C_ISTANORAICELU = 'F'
|
|
WHERE cs.C_ALTANEVID = @tanevId
|
|
) cs ON tn.C_OSZTALYCSOPORTID = cs.ID
|
|
LEFT JOIN (
|
|
SELECT cs.ID
|
|
FROM T_CSOPORT_OSSZES cs
|
|
INNER JOIN #SzurtCsoportTipus cst On cst.CsoportTipusId = cs.C_TIPUSA
|
|
WHERE cs.C_ALTANEVID = @tanevId
|
|
) csmuv ON tn.C_OSZTALYCSOPORTID = csmuv.ID
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = tn.C_OSZTALYCSOPORTID AND tanulok.TanuloId = tcs.C_TANULOID AND tcs.TOROLT = 'F'
|
|
WHERE tn.C_DATUM BETWEEN @IdoszakKezdete AND @IdoszakVege
|
|
AND mu.C_TANEVID = @tanevId
|
|
AND mu.Torolt ='F'
|
|
AND tn.Torolt ='F'
|
|
AND mu.C_IGAZOLT IS NOT NULL
|
|
AND tn.C_TANEVID = @tanevId
|
|
AND (@iskolaErdekuSzamit = 1 OR (C_IGAZOLASTIPUSA <> 1533 OR C_IGAZOLASTIPUSA IS NULL))
|
|
AND tcs.C_BELEPESDATUM <= tn.C_DATUM
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > tn.C_DATUM)
|
|
AND tanulok.BelepesDatum <= tn.C_DATUM
|
|
AND (tanulok.KilepesDatum IS NULL OR tanulok.KilepesDatum > tn.C_DATUM)
|
|
AND mu.C_TIPUS = 1500
|
|
AND (
|
|
(@isMuveszetOktatasi = 0)
|
|
OR
|
|
(@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL)
|
|
)
|
|
GROUP BY mu.C_ORATANULOIID, tanulok.TanuloCsoportId, mu.C_TIPUS, mu.C_IGAZOLT, IIF((@isMuveszetOktatasi = 0 AND cs.ID IS NULL), 'T', IIF((@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL),'T','F')), t.C_GYAKORLATI
|
|
) x
|
|
) p
|
|
PIVOT (
|
|
MAX(cnt)
|
|
FOR piv IN (
|
|
[hianyzasIgazoltTanoraiElmeleti], [hianyzasIgazoltTanoraiGyakorlati],
|
|
[hianyzasIgazolatlanTanoraiElmeleti], [hianyzasIgazolatlanTanoraiGyakorlati]
|
|
)
|
|
) AS pvt
|
|
) mu on mu.tanuloId = t.tanuloId and mu.TanuloCsoportId = t.TanuloCsoportId
|
|
LEFT JOIN (
|
|
SELECT
|
|
tanuloId
|
|
,tanulocsoportid
|
|
,SUM(kesesIgazolandoTanoraiPluszOra) AS kesesIgazolandoTanoraiPluszOra
|
|
,SUM(kesesIgazoltTanoraiPluszOra) AS kesesIgazoltTanoraiPluszOra
|
|
,SUM(kesesIgazolatlanTanoraiPluszOra) AS kesesIgazolatlanTanoraiPluszOra
|
|
,SUM(kesesIgazoltElmeletiOraban) AS kesesIgazoltElmeletiOraban
|
|
,SUM(kesesIgazolatlanElmeletiOraban) AS kesesIgazolatlanElmeletiOraban
|
|
,SUM(kesesIgazoltGyakorlatiOraban) AS kesesIgazoltGyakorlatiOraban
|
|
,SUM(kesesIgazolatlanGyakorlatiOraban) AS kesesIgazolatlanGyakorlatiOraban
|
|
FROM (
|
|
SELECT
|
|
x.tanuloId
|
|
,x.tanulocsoportid
|
|
,'Keses' +
|
|
CASE
|
|
WHEN isIgazolt IS NULL THEN 'Igazolando'
|
|
WHEN isIgazolt = 'T' THEN 'Igazolt'
|
|
WHEN isIgazolt = 'F' THEN 'Igazolatlan'
|
|
END + 'TanoraiPluszOra' AS piv
|
|
,kesesOraban
|
|
,kesesIgazoltElmeletiOraban
|
|
,kesesIgazolatlanElmeletiOraban
|
|
,kesesIgazoltGyakorlatiOraban
|
|
,kesesIgazolatlanGyakorlatiOraban
|
|
FROM (
|
|
SELECT
|
|
xx.tanuloId
|
|
,xx.tanulocsoportid
|
|
,xx.isIgazolt
|
|
,SUM(xx.keses / (CASE WHEN isGyakorlati = 'F' THEN @elmeletiOraPercben ELSE @gyakorlatiOraPercben END)) AS kesesOraban
|
|
,SUM(CASE WHEN isGyakorlati = 'F' AND isIgazolt='T' THEN xx.keses / @elmeletiOraPercben ELSE 0 END) AS kesesIgazoltElmeletiOraban
|
|
,SUM(CASE WHEN isGyakorlati = 'F' AND isIgazolt='F' THEN xx.keses / @elmeletiOraPercben ELSE 0 END) AS kesesIgazolatlanElmeletiOraban
|
|
,SUM(CASE WHEN isGyakorlati = 'T' AND isIgazolt='T' THEN xx.keses / @gyakorlatiOraPercben ELSE 0 END) AS kesesIgazoltGyakorlatiOraban
|
|
,SUM(CASE WHEN isGyakorlati = 'T' AND isIgazolt='F' THEN xx.keses / @gyakorlatiOraPercben ELSE 0 END) AS kesesIgazolatlanGyakorlatiOraban
|
|
FROM (
|
|
SELECT
|
|
mu.C_ORATANULOIID AS tanuloId
|
|
,tanulok.tanulocsoportid
|
|
,mu.C_IGAZOLT isIgazolt
|
|
,t.C_GYAKORLATI AS isGyakorlati
|
|
,SUM(mu.C_KESESPERCBEN) AS keses
|
|
FROM T_TANULOMULASZTAS_OSSZES mu
|
|
INNER JOIN #Tanulo tanulok ON tanulok.TanuloId = mu.C_ORATANULOIID
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = mu.C_TANITASIORAKID
|
|
INNER JOIN T_TANTARGY_OSSZES t ON t.ID = tn.C_TANTARGYID
|
|
LEFT JOIN (
|
|
SELECT cs.ID, cs.C_TIPUSA
|
|
FROM T_CSOPORT_OSSZES cs
|
|
INNER JOIN T_CSOPORTTIPUS_OSSZES cst On cst.ID = cs.C_TIPUSA AND cst.C_ALTANEVID = cs.C_ALTANEVID AND cst.C_ISTANORAICELU = 'F'
|
|
) cs ON tn.C_OSZTALYCSOPORTID = cs.ID
|
|
LEFT JOIN (
|
|
SELECT cs.ID
|
|
FROM T_CSOPORT_OSSZES cs
|
|
INNER JOIN #SzurtCsoportTipus cst On cst.CsoportTipusId = cs.C_TIPUSA
|
|
) csmuv ON tn.C_OSZTALYCSOPORTID = csmuv.ID
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = tn.C_OSZTALYCSOPORTID and tanulok.TanuloId = tcs.C_TANULOID AND tcs.TOROLT = 'F'
|
|
WHERE mu.C_TANEVID = @tanevId
|
|
AND (
|
|
(@isMuveszetOktatasi = 0 AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT CsoportTipusId FROM #SzurtCsoportTipus)))
|
|
OR
|
|
(@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL)
|
|
)
|
|
AND mu.C_TIPUS = 1499
|
|
AND mu.TOROLT = 'F'
|
|
AND tn.Torolt ='F'
|
|
AND (@iskolaErdekuSzamit = 1 OR (C_IGAZOLASTIPUSA <> 1533 OR C_IGAZOLASTIPUSA IS NULL))
|
|
AND tcs.C_BELEPESDATUM <= tn.C_DATUM
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > tn.C_DATUM)
|
|
AND tanulok.BelepesDatum <= tn.C_DATUM
|
|
AND (tanulok.KilepesDatum IS NULL OR tanulok.KilepesDatum > tn.C_DATUM)
|
|
AND tn.C_DATUM BETWEEN @IdoszakKezdete AND @IdoszakVege
|
|
GROUP BY mu.C_ORATANULOIID, tanulok.TanuloCsoportId, mu.C_IGAZOLT, t.C_GYAKORLATI
|
|
) xx
|
|
GROUP BY tanuloId, TanuloCsoportId, xx.isIgazolt
|
|
) x
|
|
) p
|
|
PIVOT (
|
|
MAX(kesesOraban)
|
|
FOR piv IN (
|
|
kesesIgazolandoTanoraiPluszOra, kesesIgazoltTanoraiPluszOra, kesesIgazolatlanTanoraiPluszOra
|
|
)
|
|
) AS pvt
|
|
GROUP BY tanuloId, TanuloCsoportId
|
|
) kp ON t.tanuloId = kp.tanuloId AND kp.TanuloCsoportId = t.TanuloCsoportId
|
|
END
|
|
GO
|
|
|