216 lines
7.2 KiB
Transact-SQL
216 lines
7.2 KiB
Transact-SQL
IF OBJECT_ID('[dbo].[sp_GetTanulokMulasztasaiKesesei]') IS NOT NULL BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTanulokMulasztasaiKesesei]
|
|
END
|
|
GO
|
|
|
|
-- ==========================================================================================
|
|
-- Description: <Az osztoly/csoport tanulóinak vagy egy adott tanulónak a mulasztásai>
|
|
-- ==========================================================================================
|
|
CREATE PROCEDURE [dbo].[sp_GetTanulokMulasztasaiKesesei]
|
|
@pIntezmenyId INT
|
|
,@pTanevId INT
|
|
,@pOsztalyCsoportId INT = NULL
|
|
,@pTanuloId INT = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@elmeletiOraPercben INT = 45
|
|
,@gyakorlatiOraPercben INT = 60
|
|
,@osztalyCsoportNev NVARCHAR(200)
|
|
|
|
SELECT
|
|
@osztalyCsoportNev = C_NEV
|
|
FROM
|
|
T_OSZTALYCSOPORT
|
|
WHERE
|
|
ID = @pOsztalyCsoportId
|
|
AND T_OSZTALYCSOPORT.TOROLT = 'F'
|
|
|
|
|
|
DECLARE @Tanulok TABLE (Id INT PRIMARY KEY)
|
|
|
|
IF @pTanuloId IS NOT NULL
|
|
BEGIN
|
|
INSERT INTO @Tanulok (Id) VALUES (@pTanuloId)
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO
|
|
@Tanulok (Id)
|
|
SELECT
|
|
C_TANULOID
|
|
FROM
|
|
T_TANULOCSOPORT_OSSZES tcs
|
|
WHERE
|
|
tcs.C_OSZTALYCSOPORTID = @pOsztalyCsoportId
|
|
AND tcs.C_BELEPESDATUM <= GETDATE()
|
|
AND (tcs.C_KILEPESDATUM > GETDATE() OR tcs.C_KILEPESDATUM IS NULL)
|
|
AND tcs.TOROLT = 'F'
|
|
|
|
END
|
|
|
|
SELECT
|
|
fh.ID TanuloId
|
|
,fh.C_NYOMTATASINEV TanuloNev
|
|
,fh.C_SZULETESIDATUM SzuletesiIdo
|
|
,fh.C_ANYJANEVE AnyjaNeve
|
|
,@osztalyCsoportNev OsztCsopNev
|
|
,ISNULL(m.[hianyzasIgazolandoTanorai], 0) TanoraiHianyzasIgazolando
|
|
,ISNULL(m.[hianyzasIgazolandoTanoranKivuli], 0) TanoranKivuliHianyzasIgazolando
|
|
,ISNULL(m.[hianyzasIgazoltTanorai], 0) TanoraiHianyzasIgazolt
|
|
,ISNULL(m.[hianyzasIgazoltTanoranKivuli], 0) TanoranKivuliHianyzasIgazolt
|
|
,ISNULL(m.[hianyzasIgazolatlanTanorai], 0) TanoraiHianyzasIgazolatlan
|
|
,ISNULL(m.[hianyzasIgazolatlanTanoranKivuli], 0) TanoranKivuliHianyzasIgazolatlan
|
|
,ISNULL(m.[kesesIgazolandoTanorai], 0) TanoraiKesesDarabszamIgazolando
|
|
,ISNULL(m.[kesesIgazoltTanorai], 0) TanoraiKesesDarabszamIgazolt
|
|
,ISNULL(m.[kesesIgazolatlanTanorai], 0) TanoraiKesesDarabszamIgazolatlan
|
|
,ISNULL(k.[kesesIgazolandoTanoraiPerc], 0) TanoraiKesesIgazolandoPerc
|
|
,ISNULL(k.[kesesIgazoltTanoraiPerc], 0) TanoraiKesesIgazoltPerc
|
|
,ISNULL(k.[kesesIgazolatlanTanoraiPerc], 0) TanoraiKesesIgazolatlanPerc
|
|
,ISNULL(kp.[kesesIgazolandoTanoraiPluszOra], 0) SzamitottKesesIgazolando
|
|
,ISNULL(kp.[kesesIgazoltTanoraiPluszOra], 0) SzamitottKesesIgazolt
|
|
,ISNULL(kp.[kesesIgazolatlanTanoraiPluszOra], 0) SzamitottKesesIgazolatlan
|
|
FROM (
|
|
SELECT
|
|
ID
|
|
,C_NYOMTATASINEV
|
|
,C_SZULETESIDATUM
|
|
,C_ANYJANEVE
|
|
FROM
|
|
T_FELHASZNALO_OSSZES fh
|
|
WHERE
|
|
EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = fh.ID)
|
|
) fh
|
|
LEFT JOIN (
|
|
SELECT
|
|
tanuloId
|
|
,[hianyzasIgazolandoTanorai]
|
|
,[hianyzasIgazolandoTanoranKivuli]
|
|
,[hianyzasIgazoltTanorai]
|
|
,[hianyzasIgazoltTanoranKivuli]
|
|
,[hianyzasIgazolatlanTanorai]
|
|
,[hianyzasIgazolatlanTanoranKivuli]
|
|
,[kesesIgazolandoTanorai]
|
|
,[kesesIgazoltTanorai]
|
|
,[kesesIgazolatlanTanorai]
|
|
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 AS piv
|
|
,cnt
|
|
FROM (
|
|
SELECT
|
|
m.C_ORATANULOIID AS tanuloId
|
|
,m.C_TIPUS mulasztasTipus
|
|
,m.C_IGAZOLT isIgazolt
|
|
,IIF(cs.ID IS NULL, 'T', 'F') AS isTanorai
|
|
,COUNT(1) AS cnt
|
|
FROM T_TANULOMULASZTAS_OSSZES m
|
|
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(@pTanevId))
|
|
WHERE
|
|
m.C_INTEZMENYID = @pIntezmenyId
|
|
AND m.C_TANEVID = @pTanevId
|
|
AND m.Torolt ='F'
|
|
AND EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = m.C_ORATANULOIID)
|
|
GROUP BY m.C_ORATANULOIID, m.C_TIPUS, m.C_IGAZOLT, IIF(cs.ID IS NULL, 'T', 'F')
|
|
) x
|
|
) p
|
|
PIVOT (
|
|
MAX(cnt)
|
|
FOR piv IN ([hianyzasIgazolandoTanorai], [hianyzasIgazolandoTanoranKivuli], [hianyzasIgazoltTanorai], [hianyzasIgazoltTanoranKivuli], [hianyzasIgazolatlanTanorai], [hianyzasIgazolatlanTanoranKivuli], [kesesIgazolandoTanorai], [kesesIgazoltTanorai], [kesesIgazolatlanTanorai])
|
|
) AS pvt
|
|
) m ON fh.ID = m.tanuloId
|
|
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 T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID
|
|
WHERE m.C_INTEZMENYID = @pIntezmenyId
|
|
AND m.C_TANEVID = @pTanevId
|
|
AND m.Torolt ='F'
|
|
AND EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = m.C_ORATANULOIID)
|
|
AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@pTanevId)))
|
|
AND m.C_TIPUS = 1499
|
|
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
|
|
,[kesesIgazolandoTanoraiPluszOra]
|
|
,[kesesIgazoltTanoraiPluszOra]
|
|
,[kesesIgazolatlanTanoraiPluszOra]
|
|
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
|
|
FROM (
|
|
SELECT
|
|
xx.tanuloId
|
|
,xx.isIgazolt
|
|
,SUM(xx.keses / (CASE WHEN isGyakorlati = 'F' THEN @elmeletiOraPercben ELSE @gyakorlatiOraPercben END)) AS kesesOraban
|
|
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 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
|
|
WHERE
|
|
m.C_INTEZMENYID = @pIntezmenyId
|
|
AND m.C_TANEVID = @pTanevId
|
|
AND EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = m.C_ORATANULOIID)
|
|
AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@pTanevId)))
|
|
AND m.C_TIPUS = 1499
|
|
AND m.TOROLT = 'F'
|
|
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
|
|
) kp ON m.tanuloId = kp.tanuloId
|
|
|
|
END
|
|
|
|
|
|
GO
|
|
|