305 lines
12 KiB
Transact-SQL
305 lines
12 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGenerateOrarend
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGenerateOrarend
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pIdoszakKezdete datetime = NULL
|
|
,@pIdoszakVege datetime = NULL
|
|
,@pOrarendiOraId int = NULL
|
|
,@pOsztalycsoportId int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@sql nvarchar(max) = N''
|
|
,@aktivCsengetesirend int;
|
|
SELECT
|
|
@pIdoszakKezdete = ISNULL(@pIdoszakKezdete, C_KEZDONAP)
|
|
,@pIdoszakVege = ISNULL(@pIdoszakVege, C_UTOLSONAP)
|
|
FROM T_TANEV
|
|
WHERE ID = @pTanevId
|
|
|
|
SELECT @aktivCsengetesirend = ID
|
|
FROM T_CSENGETESIREND
|
|
WHERE C_TANEVID = @pTanevId
|
|
AND TOROLT = 'F'
|
|
AND C_AKTIV = 'T'
|
|
|
|
IF @pOrarendiOraId IS NOT NULL BEGIN
|
|
IF (SELECT TOROLT FROM T_ORARENDIORA_OSSZES WHERE ID = @pOrarendiOraId) = 'T' BEGIN
|
|
DELETE orr
|
|
FROM T_ORAREND orr
|
|
WHERE orr.C_ORARENDIORAID = @pOrarendiOraId
|
|
END
|
|
ELSE BEGIN
|
|
DELETE orr
|
|
FROM T_ORAREND orr
|
|
WHERE orr.C_ORARENDIORAID = @pOrarendiOraId
|
|
AND (C_DATUM < @pIdoszakKezdete OR C_DATUM >= @pIdoszakVege)
|
|
END
|
|
END
|
|
ELSE BEGIN
|
|
DELETE orr
|
|
FROM T_ORAREND orr
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.ID = orr.C_ORARENDIORAID
|
|
WHERE oo.TOROLT = 'T' AND oo.C_TANEVID = @pTanevId
|
|
END
|
|
|
|
SET @sql += N'
|
|
|
|
CREATE TABLE #Osztalycsoport (
|
|
Id int
|
|
,IdoszakVege datetime
|
|
,TanevId int
|
|
)
|
|
|
|
DECLARE @VegzosUtolsoTanitasiNap datetime;
|
|
SELECT @VegzosUtolsoTanitasiNap = MIN(C_DATUM)
|
|
FROM T_TANEVRENDJE_OSSZES
|
|
WHERE TOROLT = ''F''
|
|
AND C_TANEVID = @pTanevId
|
|
AND C_NAPTIPUSA IN (1402, 7600, 7601, 7602, 7603, 1395)
|
|
AND C_OSSZESCSOPORTRAVONATKOZIK = ''T''
|
|
|
|
--PRINT ''Osztálycsoport insert''
|
|
|
|
INSERT INTO #Osztalycsoport (Id, IdoszakVege, TanevId)
|
|
SELECT ocs.ID, IIF(C_VEGZOSEVFOLYAM = ''T'', @VegzosUtolsoTanitasiNap, @pIdoszakVege), ocs.C_TANEVID
|
|
FROM T_OSZTALYCSOPORT_OSSZES ocs
|
|
' + IIF(@pOrarendiOraId IS NULL, '', 'INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.C_OSZTALYCSOPORTID = ocs.ID AND oo.TOROLT = ''F'' AND oo.ID = @pOrarendiOraId') + '
|
|
WHERE ocs.TOROLT = ''F'' ' + IIF(@pOsztalycsoportId IS NULL, '', 'AND ocs.ID = @pOsztalycsoportId') + '
|
|
|
|
--PRINT ''Osztálycsoport update''
|
|
|
|
UPDATE ocs SET
|
|
ocs.IdoszakVege = NewVegeDatum
|
|
FROM #OsztalyCsoport ocs
|
|
INNER JOIN (
|
|
SELECT MIN(tr.C_DATUM) AS NewVegeDatum, ocstr.C_OSZTALYCSOPORTID
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON ocstr.C_TANEVRENDJEID = tr.ID
|
|
WHERE tr.C_OSSZESCSOPORTRAVONATKOZIK = ''F''
|
|
AND tr.TOROLT = ''F''
|
|
AND tr.C_NAPTIPUSA IN (1402, 7600, 7601, 7602, 7603)
|
|
AND tr.C_TANEVID = @pTanevId
|
|
GROUP BY ocstr.C_OSZTALYCSOPORTID
|
|
) x ON x.C_OSZTALYCSOPORTID = ocs.Id'
|
|
|
|
IF @pOsztalycsoportId IS NOT NULL OR @pOrarendiOraId IS NOT NULL BEGIN
|
|
SET @sql += N'
|
|
CREATE TABLE #OrarendiOra (
|
|
Id int primary key
|
|
);
|
|
|
|
--PRINT ''OrarendiOraTemp insert''
|
|
INSERT INTO #OrarendiOra (id)
|
|
SELECT ID
|
|
FROM T_ORARENDIORA_OSSZES
|
|
WHERE TOROLT = ''F''
|
|
' + IIF(@pOsztalycsoportId IS NULL, '', 'AND C_OSZTALYCSOPORTID = @pOsztalycsoportId')
|
|
+ IIF(@pOrarendiOraId IS NULL, '', 'AND ID = @pOrarendiOraId') + '
|
|
'
|
|
END
|
|
SET @sql += N'
|
|
CREATE TABLE #NaptariNap (
|
|
C_TANEVID int
|
|
,C_INTEZMENYID int
|
|
,C_NAPDATUMA datetime
|
|
,C_HETNAPJA int
|
|
,C_HETIREND int
|
|
,C_EGYEDINAP char(1) COLLATE Hungarian_CI_AI
|
|
,C_CSENGETESIRENDID int
|
|
,INDEX IX_001 CLUSTERED (C_TANEVID, C_HETNAPJA, C_INTEZMENYID, C_NAPDATUMA)
|
|
)
|
|
|
|
INSERT INTO #NaptariNap (
|
|
C_TANEVID
|
|
,C_INTEZMENYID
|
|
,C_NAPDATUMA
|
|
,C_HETNAPJA
|
|
,C_HETIREND
|
|
,C_EGYEDINAP
|
|
,C_CSENGETESIRENDID
|
|
) SELECT
|
|
nn.C_TANEVID
|
|
,nn.C_INTEZMENYID
|
|
,nn.C_NAPDATUMA
|
|
,nn.C_HETNAPJA
|
|
,nn.C_HETIREND
|
|
,ISNULL(tr.C_EGYEDINAP, ''F'')
|
|
,ISNULL(nn.C_AKTIVCSENGETESIRENDID, @aktivCsengetesirend)
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA AND tr.C_TANEVID = nn.C_TANEVID AND tr.C_OSSZESCSOPORTRAVONATKOZIK = ''T'' AND tr.TOROLT = ''F''
|
|
WHERE nn.C_TANEVID = @pTanevId AND nn.TOROLT = ''F''
|
|
AND nn.C_ORARENDINAP = ''T''
|
|
AND nn.C_NAPDATUMA BETWEEN @pIdoszakKezdete AND @pIdoszakVege
|
|
' + IIF(@pOrarendiOraId IS NULL, '', 'AND nn.C_HETNAPJA = (SELECT C_HETNAPJA FROM T_ORARENDIORA_OSSZES WHERE ID = @pOrarendiOraId)') + '
|
|
|
|
CREATE TABLE #NaptariNapOsztalycsoport (
|
|
C_TANEVID int
|
|
,C_INTEZMENYID int
|
|
,C_NAPDATUMA datetime
|
|
,C_HETNAPJA int
|
|
,C_HETIREND int
|
|
,C_EGYEDINAP char(1) COLLATE Hungarian_CI_AI
|
|
,C_OSZTALYCSOPORTID int
|
|
,C_CSENGETESIRENDID int
|
|
,INDEX IX_001 CLUSTERED (C_TANEVID, C_HETNAPJA, C_INTEZMENYID, C_NAPDATUMA, C_OSZTALYCSOPORTID)
|
|
)
|
|
INSERT INTO #NaptariNapOsztalycsoport (
|
|
C_TANEVID
|
|
,C_INTEZMENYID
|
|
,C_NAPDATUMA
|
|
,C_HETNAPJA
|
|
,C_HETIREND
|
|
,C_EGYEDINAP
|
|
,C_OSZTALYCSOPORTID
|
|
,C_CSENGETESIRENDID
|
|
) SELECT
|
|
tr.C_TANEVID
|
|
,tr.C_INTEZMENYID
|
|
,tr.C_DATUM
|
|
,tr.C_HETNAPJA
|
|
,tr.C_HETIREND
|
|
,tr.C_EGYEDINAP
|
|
,ocstr.C_OSZTALYCSOPORTID
|
|
,ISNULL(tr.C_CSENGETESIRENDID, @aktivCsengetesirend)
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON ocstr.C_TANEVRENDJEID = tr.ID
|
|
WHERE tr.C_TANEVID = @pTanevId
|
|
AND tr.TOROLT = ''F''
|
|
AND tr.C_ORARENDINAP = ''T''
|
|
AND tr.C_OSSZESCSOPORTRAVONATKOZIK = ''F''
|
|
AND tr.C_DATUM BETWEEN @pIdoszakKezdete AND @pIdoszakVege
|
|
' + IIF(@pOsztalycsoportId IS NULL, '', 'AND ocstr.C_OSZTALYCSOPORTID = @pOsztalycsoportId' )
|
|
|
|
SET @sql += N'
|
|
CREATE TABLE #Orarend (
|
|
Datum datetime
|
|
,OrarendiOraId int
|
|
,PRIMARY KEY CLUSTERED (Datum, OrarendioraId)
|
|
);
|
|
|
|
--PRINT ''NaptárinapTemp insert''
|
|
INSERT INTO #Orarend (
|
|
Datum
|
|
,OrarendiOraId
|
|
)'
|
|
SET @sql += N'
|
|
SELECT nn.C_NAPDATUMA, oo.ID
|
|
FROM #NaptariNap nn
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.C_HETNAPJA = nn.C_HETNAPJA
|
|
AND (oo.C_HETIREND = 1554 OR oo.C_HETIREND = nn.C_HETIREND)
|
|
AND nn.C_EGYEDINAP = oo.C_EGYEDINAP
|
|
AND oo.TOROLT=''F''
|
|
AND oo.C_TANEVID = nn.C_TANEVID
|
|
AND oo.C_INTEZMENYID = nn.C_INTEZMENYID
|
|
AND oo.C_ORAERVENYESSEGKEZDETE <= nn.C_NAPDATUMA AND oo.C_ORAERVENYESSEGVEGE > nn.C_NAPDATUMA
|
|
INNER JOIN #Osztalycsoport ocs ON ocs.Id = oo.C_OSZTALYCSOPORTID AND ocs.IdoszakVege >= nn.C_NAPDATUMA
|
|
'+IIF(@pOrarendiOraId IS NULL AND @pOsztalyCsoportId IS NULL, '', 'INNER JOIN #OrarendiOra oo2 ON oo2.Id = oo.ID') + '
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM T_TANEVRENDJE_OSSZES tr2
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON tr2.ID = ocstr.C_TANEVRENDJEID
|
|
WHERE tr2.C_OSSZESCSOPORTRAVONATKOZIK = ''F'' AND tr2.TOROLT = ''F''
|
|
AND ocs.Id = ocstr.C_OSZTALYCSOPORTID AND nn.C_NAPDATUMA = tr2.C_DATUM)
|
|
AND (EXISTS (SELECT 1 FROM T_CSENGETESIRENDORA_OSSZES csro WHERE csro.C_CSENGETESIRENDID = nn.C_CSENGETESIRENDID AND oo.C_ORASZAM = csro.C_ORASZAM AND csro.TOROLT = ''F'') OR oo.C_CSENGETESIRENDID IS NULL)
|
|
|
|
UNION ALL
|
|
|
|
SELECT nn.C_NAPDATUMA, oo.ID
|
|
FROM #NaptariNap nn
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.C_HETNAPJA = nn.C_HETNAPJA
|
|
AND (oo.C_HETIREND = 1554 OR oo.C_HETIREND = nn.C_HETIREND)
|
|
AND nn.C_EGYEDINAP = oo.C_EGYEDINAP
|
|
AND oo.TOROLT=''F''
|
|
AND oo.C_TANEVID = nn.C_TANEVID
|
|
AND oo.C_INTEZMENYID = nn.C_INTEZMENYID
|
|
AND oo.C_ORAERVENYESSEGKEZDETE = nn.C_NAPDATUMA AND oo.C_ORAERVENYESSEGVEGE = nn.C_NAPDATUMA
|
|
INNER JOIN #Osztalycsoport ocs ON ocs.Id = oo.C_OSZTALYCSOPORTID AND ocs.IdoszakVege >= nn.C_NAPDATUMA
|
|
'+IIF(@pOrarendiOraId IS NULL AND @pOsztalyCsoportId IS NULL, '', 'INNER JOIN #OrarendiOra oo2 ON oo2.Id = oo.ID') + '
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM T_TANEVRENDJE_OSSZES tr2
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON tr2.ID = ocstr.C_TANEVRENDJEID
|
|
WHERE tr2.C_OSSZESCSOPORTRAVONATKOZIK = ''F'' AND tr2.TOROLT = ''F''
|
|
AND ocs.Id = ocstr.C_OSZTALYCSOPORTID AND nn.C_NAPDATUMA = tr2.C_DATUM)
|
|
AND (EXISTS (SELECT 1 FROM T_CSENGETESIRENDORA_OSSZES csro WHERE csro.C_CSENGETESIRENDID = nn.C_CSENGETESIRENDID AND oo.C_ORASZAM = csro.C_ORASZAM AND csro.TOROLT = ''F'') OR oo.C_CSENGETESIRENDID IS NULL)
|
|
|
|
UNION ALL'
|
|
|
|
SET @sql += N'
|
|
SELECT nn.C_NAPDATUMA, oo.ID
|
|
FROM #NaptariNapOsztalycsoport nn
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.C_HETNAPJA = nn.C_HETNAPJA
|
|
AND (oo.C_HETIREND = 1554 OR oo.C_HETIREND = nn.C_HETIREND)
|
|
AND nn.C_EGYEDINAP = oo.C_EGYEDINAP
|
|
AND oo.TOROLT=''F''
|
|
AND oo.C_TANEVID = nn.C_TANEVID
|
|
AND oo.C_INTEZMENYID = nn.C_INTEZMENYID
|
|
AND oo.C_ORAERVENYESSEGKEZDETE <= nn.C_NAPDATUMA AND oo.C_ORAERVENYESSEGVEGE > nn.C_NAPDATUMA
|
|
AND oo.C_OSZTALYCSOPORTID = nn.C_OSZTALYCSOPORTID
|
|
INNER JOIN #Osztalycsoport ocs ON ocs.Id = nn.C_OSZTALYCSOPORTID AND ocs.IdoszakVege >= nn.C_NAPDATUMA
|
|
'+IIF(@pOrarendiOraId IS NULL AND @pOsztalyCsoportId IS NULL, '', 'INNER JOIN #OrarendiOra oo2 ON oo2.Id = oo.ID') + '
|
|
WHERE (EXISTS (SELECT 1 FROM T_CSENGETESIRENDORA_OSSZES csro WHERE csro.C_CSENGETESIRENDID = nn.C_CSENGETESIRENDID AND oo.C_ORASZAM = csro.C_ORASZAM AND csro.TOROLT = ''F'') OR oo.C_CSENGETESIRENDID IS NULL)
|
|
|
|
UNION ALL
|
|
|
|
SELECT nn.C_NAPDATUMA, oo.ID
|
|
FROM #NaptariNapOsztalycsoport nn
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.C_HETNAPJA = nn.C_HETNAPJA
|
|
AND (oo.C_HETIREND = 1554 OR oo.C_HETIREND = nn.C_HETIREND)
|
|
AND nn.C_EGYEDINAP = oo.C_EGYEDINAP
|
|
AND oo.TOROLT=''F''
|
|
AND oo.C_TANEVID = nn.C_TANEVID
|
|
AND oo.C_INTEZMENYID = nn.C_INTEZMENYID
|
|
AND oo.C_ORAERVENYESSEGKEZDETE = nn.C_NAPDATUMA AND oo.C_ORAERVENYESSEGVEGE = nn.C_NAPDATUMA
|
|
AND oo.C_OSZTALYCSOPORTID = nn.C_OSZTALYCSOPORTID
|
|
INNER JOIN #Osztalycsoport ocs ON ocs.Id = nn.C_OSZTALYCSOPORTID AND ocs.IdoszakVege >= nn.C_NAPDATUMA
|
|
'+IIF(@pOrarendiOraId IS NULL AND @pOsztalyCsoportId IS NULL, '', 'INNER JOIN #OrarendiOra oo2 ON oo2.Id = oo.ID') + '
|
|
WHERE (EXISTS (SELECT 1 FROM T_CSENGETESIRENDORA_OSSZES csro WHERE csro.C_CSENGETESIRENDID = nn.C_CSENGETESIRENDID AND oo.C_ORASZAM = csro.C_ORASZAM AND csro.TOROLT = ''F'') OR oo.C_CSENGETESIRENDID IS NULL)
|
|
'
|
|
|
|
SET @sql += N'
|
|
--PRINT ''MERGE''
|
|
--select * from #Orarend order by OrarendioraId desc
|
|
MERGE T_ORAREND_OSSZES trg
|
|
USING #Orarend src ON
|
|
trg.C_ORARENDIORAID = src.OrarendioraId AND src.Datum = trg.C_DATUM
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT (
|
|
C_ORARENDIORAID
|
|
,C_DATUM
|
|
,C_TANEVID
|
|
,C_INTEZMENYID
|
|
) VALUES (
|
|
src.OrarendiOraId
|
|
,src.Datum
|
|
,@pTanevId
|
|
,@pIntezmenyId
|
|
)
|
|
WHEN NOT MATCHED BY SOURCE AND trg.C_TANEVID = @pTanevId AND trg.C_DATUM BETWEEN @pIdoszakKezdete AND @pIdoszakVege
|
|
' + IIF(@pOrarendiOraId IS NULL AND @pOsztalyCsoportId IS NULL, '', 'AND trg.C_ORARENDIORAID IN (SELECT Id FROM #OrarendiOra)') + '
|
|
THEN
|
|
DELETE
|
|
--OUTPUT $action, DELETED.C_DATUM, DELETED.C_ORARENDIORAID, INSERTED.C_DATUM, INSERTED.C_ORARENDIORAID
|
|
;'
|
|
--select @sql
|
|
EXEC sp_executesql @sql,N'
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pIdoszakKezdete datetime
|
|
,@pIdoszakVege datetime
|
|
,@pOrarendiOraId int
|
|
,@pOsztalycsoportId int
|
|
,@aktivCsengetesirend int'
|
|
,@pIntezmenyId = @pIntezmenyId
|
|
,@pTanevId = @pTanevId
|
|
,@pIdoszakKezdete = @pIdoszakKezdete
|
|
,@pIdoszakVege = @pIdoszakVege
|
|
,@pOrarendiOraId = @pOrarendiOraId
|
|
,@pOsztalycsoportId = @pOsztalycsoportId
|
|
,@aktivCsengetesirend = @aktivCsengetesirend
|
|
END
|
|
GO
|
|
|