299 lines
14 KiB
Transact-SQL
299 lines
14 KiB
Transact-SQL
|
|
IF OBJECT_ID('sp_GetDokumentumMulasztasok') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_GetDokumentumMulasztasok
|
|
END
|
|
GO
|
|
|
|
-- ===========================================================================
|
|
-- Description: Dokumentumokban használt mulasztás összesítő osztály alapján
|
|
-- ===========================================================================
|
|
CREATE PROCEDURE [dbo].[sp_GetDokumentumMulasztasok]
|
|
@osztalyId INT
|
|
,@tanevId INT
|
|
,@elmeletGyakorlat BIT = 0
|
|
,@kesesTipus INT = 1499
|
|
,@elmeletiOraPercben INT = 45
|
|
,@gyakorlatiOraPercben INT = 45
|
|
,@ertekelesTipus INT
|
|
,@iskolaErdekuSzamit BIT
|
|
,@isMuveszetOktatasi BIT = 0
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @Tanulok table (
|
|
tanuloId int PRIMARY KEY
|
|
);
|
|
|
|
INSERT INTO @Tanulok
|
|
SELECT TanuloId
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T')
|
|
|
|
DECLARE @Idoszak INT
|
|
DECLARE @IdoszakElsoNap INT
|
|
DECLARE @IdoszakKezdete DATE
|
|
DECLARE @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
|
|
|
|
DECLARE @SzurtCsoportTipusok TABLE (CsoportTipusId INT)
|
|
IF (@isMuveszetOktatasi = 0)
|
|
BEGIN
|
|
INSERT INTO @SzurtCsoportTipusok
|
|
SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@tanevId)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO @SzurtCsoportTipusok
|
|
SELECT Id FROM T_CSOPORTTIPUS_OSSZES
|
|
WHERE
|
|
C_ALTANEVID = @tanevId
|
|
AND TOROLT = 'F'
|
|
AND Id IN
|
|
(
|
|
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
|
|
,m.Igazolt
|
|
,m.Igazolatlan
|
|
,m.ElmeletIgazolt
|
|
,m.ElmeletIgazolatlan
|
|
,m.GyakorlatiIgazolt
|
|
,m.GyakorlatiIgazolatlan
|
|
,m.ElmeletOsszes
|
|
,m.GyakorlatOsszes
|
|
,m.Osszes
|
|
FROM @Tanulok t
|
|
LEFT JOIN (
|
|
SELECT DISTINCT
|
|
m.tanuloId
|
|
,ISNULL(m.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(m.[hianyzasIgazoltTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazoltElmeletiOraban,0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0) Igazolt
|
|
,ISNULL(m.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(m.[hianyzasIgazolatlanTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) Igazolatlan
|
|
,ISNULL(m.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazoltElmeletiOraban,0) ElmeletIgazolt
|
|
,ISNULL(m.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) ElmeletIgazolatlan
|
|
,ISNULL(m.[hianyzasIgazoltTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0) GyakorlatiIgazolt
|
|
,ISNULL(m.[hianyzasIgazolatlanTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) GyakorlatiIgazolatlan
|
|
,ISNULL(m.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazoltElmeletiOraban,0)
|
|
+ ISNULL(m.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) ElmeletOsszes
|
|
,ISNULL(m.[hianyzasIgazoltTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0)
|
|
+ ISNULL(m.[hianyzasIgazolatlanTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) GyakorlatOsszes
|
|
,ISNULL(m.[hianyzasIgazoltTanoraiElmeleti], 0) + ISNULL(m.[hianyzasIgazoltTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazoltElmeletiOraban,0) + ISNULL(kp.kesesIgazoltGyakorlatiOraban,0)
|
|
+ ISNULL(m.[hianyzasIgazolatlanTanoraiElmeleti], 0) + ISNULL(m.[hianyzasIgazolatlanTanoraiGyakorlati], 0) + ISNULL(kp.kesesIgazolatlanElmeletiOraban,0) + ISNULL(kp.kesesIgazolatlanGyakorlatiOraban,0) Osszes
|
|
FROM (
|
|
SELECT
|
|
tanuloId,
|
|
[hianyzasIgazoltTanoraiElmeleti], [hianyzasIgazoltTanoraiGyakorlati],
|
|
[hianyzasIgazolatlanTanoraiElmeleti], [hianyzasIgazolatlanTanoraiGyakorlati],
|
|
[kesesIgazoltTanoraiElmeleti],[kesesIgazoltTanoraiGyakorlati],
|
|
[kesesIgazolatlanTanoraiElmeleti], [kesesIgazolatlanTanoraiGyakorlati]
|
|
FROM (
|
|
SELECT
|
|
x.tanuloId
|
|
,CASE mulasztasTipus WHEN 1500 THEN 'Hianyzas' WHEN 1499 THEN 'Keses' END +
|
|
CASE WHEN isIgazolt IS NULL THEN 'Igazolando' WHEN isIgazolt = 'T' THEN 'Igazolt' WHEN isIgazolt = '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
|
|
m.C_ORATANULOIID AS tanuloId
|
|
,m.C_TIPUS mulasztasTipus
|
|
,m.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 m
|
|
INNER JOIN @Tanulok tanulok ON tanulok.TanuloId = m.C_ORATANULOIID
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
|
|
INNER JOIN T_TANTARGY_OSSZES t on t.id=tn.C_TANTARGYID
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID AND cs.C_TIPUSA NOT IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@tanevId))
|
|
LEFT JOIN T_CSOPORT_OSSZES csmuv ON tn.C_OSZTALYCSOPORTID = csmuv.ID AND csmuv.C_TIPUSA IN (SELECT CsoportTipusId FROM @SzurtCsoportTipusok)
|
|
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 m.C_TANEVID = @tanevId
|
|
AND m.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 m.C_TIPUS IN (1500 ,1499)
|
|
AND
|
|
(
|
|
(@isMuveszetOktatasi = 0)
|
|
OR
|
|
(@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL)
|
|
)
|
|
GROUP BY m.C_ORATANULOIID, m.C_TIPUS, m.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],
|
|
[kesesIgazoltTanoraiElmeleti],[kesesIgazoltTanoraiGyakorlati],
|
|
[kesesIgazolatlanTanoraiElmeleti], [kesesIgazolatlanTanoraiGyakorlati]
|
|
)
|
|
) AS pvt
|
|
) m
|
|
LEFT JOIN (
|
|
SELECT
|
|
tanuloId
|
|
,[kesesIgazolandoTanoraiPerc]
|
|
,[kesesIgazoltTanoraiPerc]
|
|
,[kesesIgazolatlanTanoraiPerc]
|
|
FROM (
|
|
SELECT
|
|
x.tanuloId
|
|
,'Keses' +
|
|
CASE WHEN isIgazolt IS NULL THEN 'Igazolando' WHEN isIgazolt = 'T' THEN 'Igazolt' WHEN isIgazolt = 'F' THEN 'Igazolatlan' END +
|
|
'TanoraiPerc' AS piv
|
|
,kesesPercben
|
|
FROM (
|
|
SELECT
|
|
m.C_ORATANULOIID AS tanuloId
|
|
,m.C_IGAZOLT isIgazolt
|
|
,SUM(m.C_KESESPERCBEN) AS kesesPercben
|
|
FROM T_TANULOMULASZTAS_OSSZES m
|
|
INNER JOIN @Tanulok tanulok ON tanulok.TanuloId = m.C_ORATANULOIID
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID AND cs.C_TIPUSA NOT IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@tanevId))
|
|
LEFT JOIN T_CSOPORT_OSSZES csmuv ON tn.C_OSZTALYCSOPORTID = csmuv.ID AND csmuv.C_TIPUSA IN (SELECT CsoportTipusId FROM @SzurtCsoportTipusok)
|
|
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 m.C_TANEVID = @tanevId
|
|
AND m.Torolt ='F'
|
|
AND tn.Torolt ='F'
|
|
AND
|
|
(
|
|
(@isMuveszetOktatasi = 0 AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT CsoportTipusId FROM @SzurtCsoportTipusok)))
|
|
OR
|
|
(@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL)
|
|
)
|
|
AND m.C_TIPUS = 1499
|
|
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)
|
|
GROUP BY m.C_ORATANULOIID, m.C_IGAZOLT
|
|
) x
|
|
) p
|
|
PIVOT (
|
|
MAX(kesesPercben)
|
|
FOR piv IN
|
|
([kesesIgazolandoTanoraiPerc], [kesesIgazoltTanoraiPerc], [kesesIgazolatlanTanoraiPerc])
|
|
) AS pvt
|
|
) k ON k.tanuloId = m.tanuloId
|
|
LEFT JOIN (
|
|
SELECT
|
|
tanuloId
|
|
,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
|
|
,'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.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
|
|
m.C_ORATANULOIID AS tanuloId
|
|
,m.C_IGAZOLT isIgazolt
|
|
,t.C_GYAKORLATI AS isGyakorlati
|
|
,SUM(m.C_KESESPERCBEN) AS keses
|
|
FROM T_TANULOMULASZTAS_OSSZES m
|
|
INNER JOIN @Tanulok tanulok ON tanulok.TanuloId = m.C_ORATANULOIID
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
|
|
INNER JOIN T_TANTARGY_OSSZES t ON t.ID = tn.C_TANTARGYID
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID AND cs.C_TIPUSA NOT IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@tanevId))
|
|
LEFT JOIN T_CSOPORT_OSSZES csmuv ON tn.C_OSZTALYCSOPORTID = csmuv.ID AND csmuv.C_TIPUSA IN (SELECT CsoportTipusId FROM @SzurtCsoportTipusok)
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = tn.C_OSZTALYCSOPORTID and tanulok.TanuloId = tcs.C_TANULOID AND tcs.TOROLT = 'F'
|
|
WHERE m.C_TANEVID = @tanevId
|
|
AND
|
|
(
|
|
(@isMuveszetOktatasi = 0 AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT CsoportTipusId FROM @SzurtCsoportTipusok)))
|
|
OR
|
|
(@isMuveszetOktatasi = 1 AND csmuv.ID IS NOT NULL)
|
|
)
|
|
AND m.C_TIPUS = 1499
|
|
AND m.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 tn.C_DATUM BETWEEN @IdoszakKezdete AND @IdoszakVege
|
|
GROUP BY m.C_ORATANULOIID, m.C_IGAZOLT, t.C_GYAKORLATI
|
|
) xx
|
|
GROUP BY tanuloId, xx.isIgazolt
|
|
) x
|
|
) p
|
|
PIVOT (
|
|
MAX(kesesOraban)
|
|
FOR piv IN (
|
|
kesesIgazolandoTanoraiPluszOra, kesesIgazoltTanoraiPluszOra, kesesIgazolatlanTanoraiPluszOra
|
|
)
|
|
) AS pvt
|
|
GROUP BY tanuloId
|
|
) kp ON m.tanuloId = kp.tanuloId
|
|
) m on m.tanuloId=t.tanuloId
|
|
END
|
|
GO
|
|
|