117 lines
5.3 KiB
Transact-SQL
117 lines
5.3 KiB
Transact-SQL
IF OBJECT_ID(N'fnGetTanuloTantargyMulasztasMegtartottOra') IS NOT NULL BEGIN
|
|
DROP FUNCTION fnGetTanuloTantargyMulasztasMegtartottOra
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION fnGetTanuloTantargyMulasztasMegtartottOra (
|
|
@magantanulo int, /*1 - Nem kell megjelennie, 2 - Csak gyakorlaton, 3 - Minden órán*/
|
|
@iskolaErdekuTavolletSzamit bit,
|
|
@parhuzamosOraSzamit bit,
|
|
@intezmenyId int,
|
|
@tanevId int
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN (
|
|
SELECT xx.C_TANTARGYID, xx.C_OSZTALYCSOPORTID, xx.C_ORATANULOIID, xx.HIANYZAS, xx.KESES, s.MEGTARTOTT AS MEGTARTOTTORA FROM (
|
|
SELECT x.C_TANTARGYID, x.C_OSZTALYCSOPORTID, x.C_ORATANULOIID, x.HIANYZAS, x.KESES,
|
|
CASE
|
|
WHEN TANORANKIVULIFOGLALKOZAS = 'T' THEN 100
|
|
WHEN t.C_GYAKORLATI = 'F' THEN 45
|
|
ELSE 45
|
|
END AS OSZTANI
|
|
FROM (
|
|
SELECT o.C_TANTARGYID, o.C_OSZTALYCSOPORTID, m.C_ORATANULOIID,
|
|
SUM(
|
|
CASE
|
|
WHEN m.C_TIPUS = 1500 AND tm.ID IS NOT NULL AND o.C_ORAKEZDETE BETWEEN tm.C_KEZDETE AND DATEADD(d, 1, tm.C_VEGE) AND tm.C_ORAMENTESITES = 'T' THEN 0
|
|
WHEN m.C_TIPUS = 1500 AND m.C_IGAZOLASTIPUSA = 1533 AND @iskolaErdekuTavolletSzamit = 0 THEN 0
|
|
WHEN m.C_TIPUS = 1500 AND @parhuzamosOraSzamit = 1 THEN IIF(y.RN = 1, 1, 0)
|
|
WHEN m.C_TIPUS = 1500 THEN 1
|
|
ELSE 0
|
|
END
|
|
) AS HIANYZAS,
|
|
SUM(
|
|
IIF(o.C_TANORANKIVULIFOGLALKOZAS = 'T',
|
|
CAST((ISNULL(m.C_KESESPERCBEN, 0) / CAST(DATEDIFF(mi, o.C_ORAKEZDETE, o.C_ORAVEGE) AS float) * 100) AS int),
|
|
ISNULL(m.C_KESESPERCBEN, 0))
|
|
) AS KESES,
|
|
MIN(o.C_TANORANKIVULIFOGLALKOZAS) TANORANKIVULIFOGLALKOZAS
|
|
FROM T_TANULOMULASZTAS m
|
|
INNER JOIN T_TANITASIORA_OSSZES o ON m.C_TANITASIORAKID = o.ID
|
|
LEFT JOIN T_TANULOMENTESSEG_OSSZES tm ON tm.C_TANTARGYID = o.C_TANTARGYID AND tm.C_TANULOID = m.C_ORATANULOIID AND tm.C_TANEVID = 15 /*majd tanév is ide*/
|
|
LEFT JOIN T_FOGLALKOZAS_OSSZES f ON f.id = o.C_FOGLALKOZASID
|
|
LEFT JOIN (
|
|
SELECT sm.ID, ROW_NUMBER() OVER(PARTITION BY sm.C_ORATANULOIID, so.C_DATUM, so.C_ORASZAM ORDER BY so.C_TANORANKIVULIFOGLALKOZAS, so.C_TANTARGYID) RN
|
|
FROM T_TANULOMULASZTAS_OSSZES sm
|
|
INNER JOIN T_TANITASIORA_OSSZES so ON so.ID = sm.C_TANITASIORAKID
|
|
WHERE sm.TOROLT = 'F' AND sm.C_TIPUS = 1500
|
|
) y ON y.ID = m.ID
|
|
INNER JOIN T_TANULO_OSSZES tn ON tn.ID = m.C_ORATANULOIID
|
|
WHERE (f.C_MULASZTASKELL = 'T' OR f.C_MULASZTASKELL IS NULL)
|
|
AND tn.C_MAGANTANULO = 'F'
|
|
AND m.C_INTEZMENYID = @intezmenyId
|
|
AND m.C_TANEVID = @tanevId
|
|
GROUP BY o.C_OSZTALYCSOPORTID, o.C_TANTARGYID, m.C_ORATANULOIID
|
|
) x
|
|
INNER JOIN T_TANTARGY_OSSZES t ON x.C_TANTARGYID = t.ID
|
|
|
|
UNION ALL
|
|
|
|
SELECT x.C_TANTARGYID, x.C_OSZTALYCSOPORTID, x.C_ORATANULOIID, x.HIANYZAS, x.KESES,
|
|
CASE
|
|
WHEN TANORANKIVULIFOGLALKOZAS = 'T' THEN 100
|
|
WHEN x.C_GYAKORLATI = 'F' THEN 45
|
|
ELSE 45
|
|
END AS OSZTANI
|
|
FROM (
|
|
SELECT o.C_TANTARGYID, o.C_OSZTALYCSOPORTID, m.C_ORATANULOIID, MIN(t.C_GYAKORLATI) C_GYAKORLATI,
|
|
SUM(
|
|
CASE
|
|
WHEN m.C_TIPUS = 1500 THEN
|
|
CASE
|
|
WHEN tm.ID IS NOT NULL AND o.C_ORAKEZDETE BETWEEN tm.C_KEZDETE AND DATEADD(d, 1, tm.C_VEGE) AND tm.C_ORAMENTESITES = 'T' THEN 0
|
|
WHEN m.C_IGAZOLASTIPUSA = 1533 AND @iskolaErdekuTavolletSzamit = 0 THEN 0
|
|
WHEN @magantanulo = 1 THEN 0
|
|
WHEN @magantanulo = 2 AND t.C_GYAKORLATI = 'F' THEN 0
|
|
WHEN @parhuzamosOraSzamit = 1 THEN IIF(y.RN = 1, 1, 0)
|
|
ELSE 1
|
|
END
|
|
ELSE 0
|
|
END
|
|
) AS HIANYZAS,
|
|
SUM(
|
|
IIF(o.C_TANORANKIVULIFOGLALKOZAS = 'T',
|
|
CAST((ISNULL(m.C_KESESPERCBEN, 0) / CAST(DATEDIFF(mi, o.C_ORAKEZDETE, o.C_ORAVEGE) AS float) * 100) AS int),
|
|
ISNULL(m.C_KESESPERCBEN, 0))
|
|
) AS KESES,
|
|
MIN(o.C_TANORANKIVULIFOGLALKOZAS) TANORANKIVULIFOGLALKOZAS
|
|
FROM T_TANULOMULASZTAS_OSSZES m
|
|
INNER JOIN T_TANITASIORA_OSSZES o ON m.C_TANITASIORAKID = o.ID
|
|
LEFT JOIN T_TANULOMENTESSEG_OSSZES tm ON tm.C_TANTARGYID = o.C_TANTARGYID AND tm.C_TANULOID = m.C_ORATANULOIID AND tm.C_TANEVID = 15 /*majd tanév is ide*/
|
|
LEFT JOIN T_FOGLALKOZAS_OSSZES f ON f.id = o.C_FOGLALKOZASID
|
|
LEFT JOIN (
|
|
SELECT M.ID, ROW_NUMBER() OVER(PARTITION BY m.C_ORATANULOIID, o.C_DATUM, o.C_ORASZAM ORDER BY o.C_TANORANKIVULIFOGLALKOZAS, o.C_TANTARGYID) RN
|
|
FROM T_TANULOMULASZTAS_OSSZES m
|
|
INNER JOIN T_TANITASIORA_OSSZES o ON o.ID = m.C_TANITASIORAKID
|
|
WHERE m.TOROLT = 'F' AND m.C_TIPUS = 1500
|
|
) y ON y.ID = m.ID
|
|
INNER JOIN T_TANULO_OSSZES tn ON tn.ID = m.C_ORATANULOIID
|
|
INNER JOIN T_TANTARGY_OSSZES t ON o.C_TANTARGYID = t.ID
|
|
WHERE (f.C_MULASZTASKELL = 'T' OR f.C_MULASZTASKELL IS NULL)
|
|
AND tn.C_MAGANTANULO = 'T'
|
|
AND m.C_INTEZMENYID = @intezmenyId
|
|
AND m.C_TANEVID = @tanevId
|
|
|
|
GROUP BY o.C_OSZTALYCSOPORTID, o.C_TANTARGYID, m.C_ORATANULOIID
|
|
) x
|
|
) xx
|
|
LEFT JOIN (
|
|
SELECT C_TANTARGYID, C_OSZTALYCSOPORTID, COUNT(1) AS MEGTARTOTT
|
|
FROM T_TANITASIORA_OSSZES
|
|
WHERE C_SORSZAMOZANDO = 'T'
|
|
GROUP BY C_TANTARGYID, C_OSZTALYCSOPORTID
|
|
) s ON s.C_TANTARGYID = xx.C_TANTARGYID AND s.C_OSZTALYCSOPORTID = xx.C_OSZTALYCSOPORTID
|
|
);
|
|
GO
|