100 lines
4.8 KiB
Transact-SQL
100 lines
4.8 KiB
Transact-SQL
DROP FUNCTION IF EXISTS fnGetDokumentumTanulokGyorsFeljegyzeseiHavibontasban
|
|
GO
|
|
|
|
CREATE FUNCTION fnGetDokumentumTanulokGyorsFeljegyzeseiHavibontasban (
|
|
@tanevId int
|
|
,@osztalyId int
|
|
,@tipusID int
|
|
,@CsakOravalRendelkezo bit
|
|
,@FelevVege date
|
|
,@BelepesDatum datetime
|
|
,@KilepesDatum datetime)
|
|
RETURNS @result TABLE (
|
|
TanuloId int
|
|
,Tantargy nvarchar (255)
|
|
,Szeptember int
|
|
,Oktober int
|
|
,November int
|
|
,December int
|
|
,Januar int
|
|
,Februar int
|
|
,Marcius int
|
|
,Aprilis int
|
|
,Majus int
|
|
,Junius int
|
|
,Felev bit
|
|
,Ossz int
|
|
)
|
|
AS BEGIN
|
|
|
|
DECLARE @kategoriaId int = (SELECT C_FELADATKATEGORIAID FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @osztalyId)
|
|
|
|
INSERT INTO @result
|
|
|
|
SELECT
|
|
tte.C_TANULOID AS TanuloId
|
|
,t.C_NEVNYOMTATVANYBAN AS Tantargy
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 9 THEN 1 ELSE 0 END) AS Szeptember
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 10 THEN 1 ELSE 0 END) AS Oktober
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 11 THEN 1 ELSE 0 END) AS November
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 12 THEN 1 ELSE 0 END) AS December
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 1 THEN 1 ELSE 0 END) AS Januar
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 2 THEN 1 ELSE 0 END) AS Februar
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 3 THEN 1 ELSE 0 END) AS Marcius
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 4 THEN 1 ELSE 0 END) AS Aprilis
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 5 THEN 1 ELSE 0 END) AS Majus
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 6 THEN 1 ELSE 0 END) AS Junius
|
|
,SUM(CASE WHEN te.C_ROGZITESDATUMA <= @FelevVege THEN 1 ELSE 0 END) AS Felev
|
|
,SUM(1) AS Ossz
|
|
FROM T_TANULO_TANULOESEMENY tte
|
|
INNER JOIN T_TANULOESEMENY_OSSZES te ON te.ID = tte.C_TANULOESEMENYID AND te.TOROLT = 'F'
|
|
INNER JOIN T_TANTARGY_OSSZES t ON t.ID = te.C_TANTARGYID AND t.TOROLT = 'F'
|
|
LEFT JOIN T_TANITASIORA_OSSZES tOra ON tOra.ID = te.C_TANITASIORAID AND tOra.Torolt = 'F'
|
|
LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tOra.C_OSZTALYCSOPORTID AND ocs.C_FELADATKATEGORIAID = @kategoriaId AND ocs.Torolt = 'F'
|
|
WHERE
|
|
tte.C_TANULOID IN (SELECT TanuloId FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T'))
|
|
AND te.TOROLT = 'F'
|
|
AND te.C_TIPUS = @tipusID
|
|
AND ((@CsakOravalRendelkezo = 1 AND (te.C_TANITASIORAID IS NOT NULL AND tOra.ID IS NOT NULL AND ocs.ID IS NOT NULL))
|
|
OR (@CsakOravalRendelkezo = 0 AND (te.C_TANITASIORAID IS NOT NULL AND tOra.ID IS NOT NULL AND ocs.ID IS NOT NULL) OR te.C_TANITASIORAID IS NULL)
|
|
)
|
|
AND @BelepesDatum <= te.C_ROGZITESDATUMA
|
|
AND (@KilepesDatum IS NULL OR @KilepesDatum >= te.C_ROGZITESDATUMA)
|
|
GROUP BY tte.C_TANULOID, t.C_NEVNYOMTATVANYBAN
|
|
|
|
UNION ALL
|
|
SELECT
|
|
tte.C_TANULOID AS TanuloId
|
|
,N'Összesen' AS Tantargy
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 9 THEN 1 ELSE 0 END) AS Szeptember
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 10 THEN 1 ELSE 0 END) AS Oktober
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 11 THEN 1 ELSE 0 END) AS November
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 12 THEN 1 ELSE 0 END) AS December
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 1 THEN 1 ELSE 0 END) AS Januar
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 2 THEN 1 ELSE 0 END) AS Februar
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 3 THEN 1 ELSE 0 END) AS Marcius
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 4 THEN 1 ELSE 0 END) AS Aprilis
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 5 THEN 1 ELSE 0 END) AS Majus
|
|
,SUM(CASE WHEN DATEPART(MONTH, te.C_ROGZITESDATUMA) = 6 THEN 1 ELSE 0 END) AS Junius
|
|
,SUM(CASE WHEN te.C_ROGZITESDATUMA <= @FelevVege THEN 1 ELSE 0 END) AS Felev
|
|
,SUM(1) AS Ossz
|
|
FROM T_TANULO_TANULOESEMENY tte
|
|
INNER JOIN T_TANULOESEMENY_OSSZES te ON te.ID = tte.C_TANULOESEMENYID AND te.TOROLT = 'F'
|
|
INNER JOIN T_TANTARGY_OSSZES t ON t.ID = te.C_TANTARGYID AND t.TOROLT = 'F'
|
|
LEFT JOIN T_TANITASIORA_OSSZES tOra ON tOra.ID = te.C_TANITASIORAID AND tOra.Torolt = 'F'
|
|
LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tOra.C_OSZTALYCSOPORTID AND ocs.C_FELADATKATEGORIAID = @kategoriaId AND ocs.Torolt = 'F'
|
|
WHERE
|
|
tte.C_TANULOID IN (SELECT TanuloId FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T'))
|
|
AND te.TOROLT = 'F'
|
|
AND te.C_TIPUS = @tipusID
|
|
AND ((@CsakOravalRendelkezo = 1 AND (te.C_TANITASIORAID IS NOT NULL AND tOra.ID IS NOT NULL AND ocs.ID IS NOT NULL))
|
|
OR (@CsakOravalRendelkezo = 0 AND (te.C_TANITASIORAID IS NOT NULL AND tOra.ID IS NOT NULL AND ocs.ID IS NOT NULL) OR te.C_TANITASIORAID IS NULL)
|
|
)
|
|
AND @BelepesDatum <= te.C_ROGZITESDATUMA
|
|
AND (@KilepesDatum IS NULL OR @KilepesDatum >= te.C_ROGZITESDATUMA)
|
|
GROUP BY tte.C_TANULOID
|
|
|
|
RETURN
|
|
END
|
|
GO
|