103 lines
3.4 KiB
Transact-SQL
103 lines
3.4 KiB
Transact-SQL
IF OBJECT_ID('sp_GetOraSorszamByOsztaly') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_GetOraSorszamByOsztaly
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetOraSorszamByOsztaly
|
|
@osztalyCsoportId INT,
|
|
@datum DATE,
|
|
@intezmenyId INT,
|
|
@tanevId INT
|
|
AS
|
|
BEGIN
|
|
DECLARE @tanarSzamit bit,
|
|
@osztalybontasEgybe bit,
|
|
@osztalybontasCsoport bit
|
|
|
|
SET @tanarSzamit = dbo.fnGetRendszerbeallitasEnumBool(3075, @intezmenyId, @tanevId)
|
|
SET @osztalybontasEgybe = dbo.fnGetRendszerbeallitasEnumBool(3078, @intezmenyId, @tanevId)
|
|
SET @osztalybontasCsoport = 0
|
|
|
|
|
|
|
|
IF EXISTS (SELECT 1 FROM T_CSOPORT_OSSZES WHERE (ID = @osztalyCsoportId AND C_OSZTALYBONTASID IS NOT NULL) OR C_OSZTALYBONTASID = @osztalyCsoportId) BEGIN
|
|
SET @osztalybontasCsoport = 1
|
|
END
|
|
|
|
|
|
IF @osztalybontasEgybe = 0 OR @osztalybontasCsoport = 0 BEGIN
|
|
IF @tanarSzamit = 1 BEGIN
|
|
SELECT
|
|
t.ID AS TanitasiOraId
|
|
,ROW_NUMBER() OVER (PARTITION BY t.C_OSZTALYCSOPORTID, t.C_TANARID, t.C_TANTARGYID ORDER BY t.C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
|
|
FROM T_TANITASIORA_OSSZES t
|
|
WHERE t.C_MEGTARTOTT = 'T'
|
|
AND t.C_SORSZAMOZANDO = 'T'
|
|
AND CAST(t.C_ORAKEZDETE AS DATE) <= @datum
|
|
AND t.TOROLT = 'F'
|
|
AND t.C_INTEZMENYID = @intezmenyid
|
|
AND t.C_TANEVID = @tanevId
|
|
AND t.C_OSZTALYCSOPORTID = @osztalyCsoportId
|
|
END
|
|
ELSE BEGIN
|
|
SELECT
|
|
t.ID AS TanitasiOraId
|
|
,ROW_NUMBER() OVER (PARTITION BY t.C_OSZTALYCSOPORTID, t.C_TANTARGYID ORDER BY C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
|
|
FROM T_TANITASIORA_OSSZES t
|
|
WHERE t.C_MEGTARTOTT = 'T'
|
|
AND t.C_SORSZAMOZANDO = 'T'
|
|
AND t.C_OSZTALYCSOPORTID = @osztalyCsoportId
|
|
AND CAST(t.C_ORAKEZDETE AS DATE) <= @datum
|
|
AND t.TOROLT = 'F'
|
|
AND t.C_INTEZMENYID = @intezmenyid
|
|
AND t.C_TANEVID = @tanevId
|
|
END
|
|
END
|
|
ELSE BEGIN
|
|
|
|
DECLARE @osztalyId int, @csoportId int
|
|
|
|
IF EXISTS (SELECT 1 FROM T_CSOPORT_OSSZES WHERE ID = @osztalyCsoportId) BEGIN
|
|
SET @csoportId = @osztalyCsoportId
|
|
SELECT @osztalyId = C_OSZTALYBONTASID FROM T_CSOPORT_OSSZES WHERE ID = @osztalyCsoportId
|
|
END
|
|
ELSE BEGIN
|
|
SET @osztalyId = @osztalyCsoportId
|
|
SELECT TOP(1) @csoportId = csoport
|
|
FROM (
|
|
SELECT
|
|
t.C_OSZTALYCSOPORTID as csoport
|
|
,COUNT(1) AS cnt
|
|
FROM T_TANITASIORA_OSSZES t
|
|
WHERE t.C_OSZTALYCSOPORTID IN (SELECT ID FROM T_CSOPORT_OSSZES WHERE C_OSZTALYBONTASID = @osztalyCsoportId)
|
|
AND CAST(t.C_ORAKEZDETE AS DATE) <= @datum
|
|
AND t.C_SORSZAMOZANDO = 'T'
|
|
AND t.C_MEGTARTOTT = 'T'
|
|
AND t.TOROLT = 'F'
|
|
AND t.C_INTEZMENYID = @intezmenyid
|
|
AND t.C_TANEVID = @tanevId
|
|
GROUP BY t.C_OSZTALYCSOPORTID
|
|
) x
|
|
ORDER BY cnt DESC
|
|
END
|
|
|
|
SELECT TanitasiOraId, OraSorszam
|
|
FROM (
|
|
SELECT
|
|
t.ID AS TanitasiOraId
|
|
,ROW_NUMBER() OVER (PARTITION BY t.C_TANTARGYID ORDER BY C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
|
|
,t.C_OSZTALYCSOPORTID AS ocsId
|
|
FROM T_TANITASIORA_OSSZES t
|
|
WHERE t.C_OSZTALYCSOPORTID IN (@osztalyId, @csoportId)
|
|
AND CAST(t.C_ORAKEZDETE AS DATE) <= @datum
|
|
AND t.C_SORSZAMOZANDO = 'T'
|
|
AND t.C_MEGTARTOTT = 'T'
|
|
AND t.TOROLT = 'F'
|
|
AND t.C_INTEZMENYID = @intezmenyid
|
|
AND t.C_TANEVID = @tanevId
|
|
) x
|
|
WHERE x.ocsId = @osztalyCsoportId
|
|
END
|
|
END
|
|
GO
|