121 lines
4.5 KiB
Transact-SQL
121 lines
4.5 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
DROP PROCEDURE IF EXISTS uspGetAtlag
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetAtlag
|
|
@pOsztalyCsoportId INT
|
|
,@pTantargyId INT
|
|
,@pAtlagMegjelenitesTipus INT
|
|
,@pAtlagbaBeleszamitoOsztalyTipus INT
|
|
,@pErtekelesTipusa INT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
|
|
IF (@pAtlagbaBeleszamitoOsztalyTipus = 1)
|
|
BEGIN
|
|
|
|
IF (@pAtlagMegjelenitesTipus = 1)
|
|
BEGIN
|
|
|
|
/* Összes jegy átlaga minden tanuló */
|
|
SELECT
|
|
te.C_TANTARGYID
|
|
,te.C_OSZTALYCSOPORTID
|
|
,ROUND(SUM(CAST(d.C_VALUE AS FLOAT) * CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100)
|
|
/ SUM(CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100), 2) AS Atlag
|
|
FROM T_TANULOERTEKELES te
|
|
INNER JOIN T_OSZTALYZATTIPUS ot ON te.C_ERTEKELESOSZTALYZATID = ot.ID
|
|
INNER JOIN T_DICTIONARYITEMBASE d ON ot.ID = d.ID
|
|
LEFT JOIN T_TANARIATLAGSULY ta ON te.C_ERTEKELESMODID = ta.C_ERTEKELESMODID
|
|
WHERE te.C_TANTARGYID = @pTantargyId AND te.C_OSZTALYCSOPORTID = @pOsztalyCsoportId AND te.C_TIPUSID = @pErtekelesTipusa
|
|
GROUP BY te.C_TANTARGYID, te.C_OSZTALYCSOPORTID
|
|
|
|
END
|
|
|
|
IF (@pAtlagMegjelenitesTipus = 2)
|
|
BEGIN
|
|
|
|
/* Átlagok átlaga minden tanuló */
|
|
SELECT
|
|
ertekeles.C_TANTARGYID
|
|
,ertekeles.C_OSZTALYCSOPORTID
|
|
,ROUND(AVG(ertekeles.Atlag), 2) AS Atlag
|
|
FROM (
|
|
SELECT
|
|
te.C_TANTARGYID
|
|
,te.C_OSZTALYCSOPORTID
|
|
,te.C_TANULOID
|
|
,ROUND(SUM(CAST(d.C_VALUE AS FLOAT) * CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100)
|
|
/ SUM(CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100), 2) AS Atlag
|
|
FROM T_TANULOERTEKELES te
|
|
INNER JOIN T_OSZTALYZATTIPUS ot ON te.C_ERTEKELESOSZTALYZATID = ot.ID
|
|
INNER JOIN T_DICTIONARYITEMBASE d ON ot.ID = d.ID
|
|
LEFT JOIN T_TANARIATLAGSULY ta ON te.C_ERTEKELESMODID = ta.C_ERTEKELESMODID
|
|
WHERE te.C_TANTARGYID = @pTantargyId AND te.C_OSZTALYCSOPORTID = @pOsztalyCsoportId AND te.C_TIPUSID = @pErtekelesTipusa
|
|
GROUP BY te.C_TANTARGYID, te.C_OSZTALYCSOPORTID,te.C_TANULOID) ertekeles
|
|
GROUP BY ertekeles.C_TANTARGYID, ertekeles.C_OSZTALYCSOPORTID
|
|
|
|
END
|
|
|
|
END
|
|
|
|
IF (@pAtlagbaBeleszamitoOsztalyTipus = 2)
|
|
BEGIN
|
|
DECLARE @CurrentDate DATETIME = GETDATE()
|
|
|
|
IF (@pAtlagMegjelenitesTipus = 1)
|
|
BEGIN
|
|
|
|
/* Összes jegy átlaga minden tanulóra, aki az osztályban jelenleg van */
|
|
SELECT
|
|
te.C_TANTARGYID
|
|
,te.C_OSZTALYCSOPORTID
|
|
,ROUND(SUM(CAST(d.C_VALUE AS FLOAT) * CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100)
|
|
/ SUM(CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100), 2) AS Atlag
|
|
FROM T_TANULOERTEKELES te
|
|
INNER JOIN T_OSZTALYZATTIPUS ot ON te.C_ERTEKELESOSZTALYZATID = ot.ID
|
|
INNER JOIN T_DICTIONARYITEMBASE d ON ot.ID = d.ID
|
|
LEFT JOIN T_TANARIATLAGSULY ta ON te.C_ERTEKELESMODID = ta.C_ERTEKELESMODID
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_OSZTALYCSOPORTID = te.C_OSZTALYCSOPORTID AND tcs.C_TANULOID = te.C_TANULOID
|
|
AND tcs.C_BELEPESDATUM <= @CurrentDate AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @CurrentDate)
|
|
WHERE te.C_TANTARGYID = @pTantargyId AND te.C_OSZTALYCSOPORTID = @pOsztalyCsoportId AND te.C_TIPUSID = @pErtekelesTipusa
|
|
GROUP BY te.C_TANTARGYID, te.C_OSZTALYCSOPORTID
|
|
|
|
END
|
|
|
|
IF (@pAtlagMegjelenitesTipus = 2)
|
|
BEGIN
|
|
|
|
/* Átlagok átlaga minden tanulóra, aki az osztályban jelenleg van */
|
|
SELECT
|
|
ertekeles.C_TANTARGYID
|
|
,ertekeles.C_OSZTALYCSOPORTID
|
|
,ROUND(AVG(ertekeles.Atlag), 2) AS Atlag
|
|
FROM (
|
|
SELECT
|
|
te.C_TANTARGYID
|
|
,te.C_OSZTALYCSOPORTID
|
|
,te.C_TANULOID
|
|
,ROUND(SUM(CAST(d.C_VALUE AS FLOAT) * CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100)
|
|
/ SUM(CAST(ISNULL(ta.C_SULY, 100) AS FLOAT) / 100), 2) AS Atlag
|
|
FROM T_TANULOERTEKELES te
|
|
INNER JOIN T_OSZTALYZATTIPUS ot ON te.C_ERTEKELESOSZTALYZATID = ot.ID
|
|
INNER JOIN T_DICTIONARYITEMBASE d ON ot.ID = d.ID
|
|
LEFT JOIN T_TANARIATLAGSULY ta ON te.C_ERTEKELESMODID = ta.C_ERTEKELESMODID
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_OSZTALYCSOPORTID = te.C_OSZTALYCSOPORTID AND tcs.C_TANULOID = te.C_TANULOID
|
|
AND tcs.C_BELEPESDATUM <= @CurrentDate AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @CurrentDate)
|
|
WHERE te.C_TANTARGYID = @pTantargyId AND te.C_OSZTALYCSOPORTID = @pOsztalyCsoportId AND te.C_TIPUSID = @pErtekelesTipusa
|
|
GROUP BY te.C_TANTARGYID, te.C_OSZTALYCSOPORTID,te.C_TANULOID) ertekeles
|
|
GROUP BY ertekeles.C_TANTARGYID, ertekeles.C_OSZTALYCSOPORTID
|
|
|
|
END
|
|
|
|
END
|
|
|
|
END
|
|
GO
|