234 lines
10 KiB
Transact-SQL
234 lines
10 KiB
Transact-SQL
-- ==========================================================================================
|
|
-- Author: Telek Ákos
|
|
-- Create date: 2016.05.31.
|
|
-- Description: Ha az adott tanévhez nincs egy naptári nap sem, akkor létrehoz egy alapot.
|
|
-- Ha van, akkor update-el a tanév rendje alapján.
|
|
-- ==========================================================================================
|
|
IF OBJECT_ID('[sp_GenerateNaptariNapok]') IS NOT NULL BEGIN
|
|
DROP PROCEDURE [sp_GenerateNaptariNapok]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [sp_GenerateNaptariNapok]
|
|
@intezmenyId int,
|
|
@tanevId int,
|
|
@overrideDefault bit = 0 -- felülírja-e az alapadatokat is?
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET DATEFIRST 1; -- Sets the first day of the week to a number from 1 (Monday) through 7 (Sunday).
|
|
|
|
DECLARE
|
|
@startDate datetime
|
|
,@endDate datetime
|
|
,@firstDay datetime
|
|
,@lastDay datetime
|
|
,@hetvegiTanitas int;
|
|
|
|
-- Adott @tanevId alapján kezdő és végdátum lekérdezése
|
|
SELECT
|
|
@startDate = t.C_KEZDONAP
|
|
,@endDate = t.C_UTOLSONAP
|
|
,@firstDay = t.C_ELSOTANITASINAP
|
|
,@lastDay = t.C_UTOLSOTANITASINAP
|
|
FROM T_TANEV t
|
|
WHERE t.ID = @tanevId
|
|
|
|
SET @hetvegiTanitas = dbo.fnGetRendszerbeallitasEnumBool(7683, @intezmenyId, @tanevId)
|
|
-- 1 - H-P
|
|
-- 2 - H-Szo
|
|
-- 3 - H-V
|
|
|
|
IF (SELECT COUNT(1) FROM T_NAPTARINAP_OSSZES WHERE C_TANEVID = @tanevId) = 0 BEGIN
|
|
-- a két dátum közötti napok kigenerálása
|
|
;WITH ctedaterange AS (
|
|
SELECT [Dates] = @startDate, week = 1
|
|
UNION ALL
|
|
SELECT [dates] + 1, week + IIF(DATEPART(dw, dates + 1) = 1, 1, 0)
|
|
FROM ctedaterange
|
|
WHERE [dates] + 1 <= @endDate
|
|
)
|
|
|
|
INSERT INTO T_NAPTARINAP (
|
|
C_HETIREND
|
|
,C_HETNAPJA
|
|
,C_HETSORSZAMA
|
|
,C_NAPDATUMA
|
|
,C_NAPTIPUSA
|
|
,C_ORARENDINAP
|
|
,C_ALAPHETIREND
|
|
,C_ALAPHETNAPJA
|
|
,C_ALAPNAPTIPUSA
|
|
,C_ALAPORARENDINAP
|
|
,C_ALAPCSENGETESIRENDID
|
|
,C_AKTIVCSENGETESIRENDID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
ISNULL(h.C_HETIREND, 1554) AS C_HETIREND -- C_HETIREND - int
|
|
,hn.ID AS C_HETNAPJA -- C_HETNAPJA - int
|
|
,h.C_HETSORSZAMA AS C_HETSORSZAMA -- C_HETSORSZAMA - int
|
|
,s.dates AS C_NAPDATUMA -- C_NAPDATUMA - datetime
|
|
,CASE
|
|
WHEN DATEPART(dw, s.dates) = 6 AND @hetvegiTanitas IN (2, 3)
|
|
THEN 1386 -- 'Munkaszüneti nap'
|
|
WHEN DATEPART(dw, s.dates) = 7 AND @hetvegiTanitas = 3
|
|
THEN 1386 -- 'Munkaszüneti nap'
|
|
ELSE 1385 -- 'Tanítási nap'
|
|
END AS C_NAPTIPUSA -- C_NAPTIPUSA - int
|
|
,CASE
|
|
WHEN DATEPART(dw, s.dates) = 6 AND @hetvegiTanitas IN (2, 3)
|
|
THEN 'F' -- 'Munkaszüneti nap'
|
|
WHEN DATEPART(dw, s.dates) = 7 AND @hetvegiTanitas = 3
|
|
THEN 'F' -- 'Munkaszüneti nap'
|
|
ELSE 'T' -- 'Tanítási nap'
|
|
END AS C_ORARENDINAP -- C_ORARENDINAP - char(1)
|
|
,ISNULL(h.C_HETIREND, 1554) AS C_ALAPHETIREND -- C_ALAPHETIREND - int
|
|
,hn.ID AS C_ALAPHETNAPJA -- C_ALAPHETNAPJA - int
|
|
,CASE
|
|
WHEN DATEPART(dw, s.dates) = 6 AND @hetvegiTanitas IN (2, 3)
|
|
THEN 1386 -- 'Munkaszüneti nap'
|
|
WHEN DATEPART(dw, s.dates) = 7 AND @hetvegiTanitas = 3
|
|
THEN 1386 -- 'Munkaszüneti nap'
|
|
ELSE 1385 -- 'Tanítási nap'
|
|
END AS C_ALAPNAPTIPUSA -- C_ALAPNAPTIPUSA - int
|
|
,CASE
|
|
WHEN DATEPART(dw, s.dates) = 6 AND @hetvegiTanitas IN (2, 3)
|
|
THEN 'F' -- 'Munkaszüneti nap'
|
|
WHEN DATEPART(dw, s.dates) = 7 AND @hetvegiTanitas = 3
|
|
THEN 'F' -- 'Munkaszüneti nap'
|
|
ELSE 'T' -- 'Tanítási nap'
|
|
END AS C_ALAPORARENDINAP -- C_ALAPORARENDINAP - char(1)
|
|
,NULL AS C_ALAPCSENGETESIRENDID -- C_ALAPCSENGETESIRENDID - int
|
|
,NULL AS C_AKTIVCSENGETESIRENDID -- C_AKTIVCSENGETESIRENDID - int
|
|
,@intezmenyId AS C_INTEZMENYID -- C_INTEZMENYID - int
|
|
,@tanevId AS C_TANEVID -- C_TANEVID - int
|
|
,'F' AS TOROLT -- TOROLT - char(1)
|
|
,0 AS SERIAL -- SERIAL - int
|
|
,GETDATE() AS LASTCHANGED -- LASTCHANGED - datetime
|
|
,GETDATE() AS CREATED -- CREATED - datetime
|
|
,NULL AS MODIFIER -- MODIFIER - int
|
|
,NULL AS CREATOR -- CREATOR - int
|
|
FROM ctedaterange s
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES t ON s.dates = t.C_DATUM AND t.C_TANEVID = @tanevId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES hn ON hn.C_VALUE = DATEPART(dw, s.dates) AND hn.C_TYPE = 'HetNapjaTipus' AND hn.C_TANEVID = @tanevId
|
|
LEFT JOIN T_NAPTARIHET_OSSZES h ON s.dates BETWEEN h.C_HETKEZDONAPJA AND h.C_HETUTOLSONAPJA AND h.C_TANEVID = @tanevId
|
|
ORDER BY s.dates
|
|
OPTION (maxrecursion 0) -- hogy ne legyen korlátozva a regurzitás mélysége a CTE-ben /* default 100 */
|
|
END
|
|
ELSE BEGIN
|
|
SELECT @firstDay = ro.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES ro
|
|
WHERE ro.C_TANEVID = @tanevId
|
|
AND ro.C_NAPTIPUSA = 1394
|
|
|
|
SELECT @lastDay = ro.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES ro
|
|
WHERE ro.C_TANEVID = @tanevId
|
|
AND ro.C_NAPTIPUSA = 1395
|
|
|
|
SELECT
|
|
@firstDay = ISNULL(@firstDay, C_KEZDONAP)
|
|
,@lastDay = ISNULL(@lastDay, C_UTOLSONAP)
|
|
FROM T_TANEV_OSSZES
|
|
WHERE ID = @tanevId
|
|
|
|
IF @overrideDefault = 1 BEGIN
|
|
UPDATE T_NAPTARINAP_OSSZES SET -- Nyári szünet UPDATE-je
|
|
C_NAPTIPUSA = IIF(DATEPART(dw, C_NAPDATUMA) BETWEEN 1 AND 5, 1392, C_NAPTIPUSA)
|
|
,C_ALAPNAPTIPUSA = IIF(DATEPART(dw, C_NAPDATUMA) BETWEEN 1 AND 5, 1392, C_ALAPNAPTIPUSA)
|
|
,C_ORARENDINAP = 'F'
|
|
,C_ALAPORARENDINAP = 'F'
|
|
WHERE (C_NAPDATUMA < @firstDay OR C_NAPDATUMA > @lastDay)
|
|
AND C_TANEVID = @tanevId
|
|
END
|
|
|
|
|
|
IF @hetvegiTanitas = 1 BEGIN -- H - P
|
|
UPDATE nn SET
|
|
nn.C_ALAPORARENDINAP = 'F'
|
|
,nn.C_ORARENDINAP = IIF(tr.ID IS NULL, 'F', nn.C_ORARENDINAP)
|
|
,nn.C_NAPTIPUSA = IIF(tr.ID IS NULL, 1386, nn.C_NAPTIPUSA)
|
|
,nn.C_ALAPNAPTIPUSA = 1386
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA AND tr.C_OSSZESCSOPORTRAVONATKOZIK = 'T' AND tr.TOROLT = 'F' AND nn.C_TANEVID = tr.C_TANEVID
|
|
WHERE DATEPART(dw, nn.C_NAPDATUMA) IN (6, 7)
|
|
AND nn.C_TANEVID = @tanevId
|
|
AND nn.C_NAPDATUMA BETWEEN @firstDay AND @lastDay
|
|
END
|
|
|
|
IF @hetvegiTanitas = 2 BEGIN -- H - Szo
|
|
UPDATE nn SET
|
|
nn.C_ALAPORARENDINAP = 'T'
|
|
,nn.C_ORARENDINAP = IIF(tr.ID IS NULL, 'T', nn.C_ORARENDINAP)
|
|
,nn.C_NAPTIPUSA = IIF(tr.ID IS NULL, 1385, nn.C_NAPTIPUSA)
|
|
,nn.C_ALAPNAPTIPUSA = 1385
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA AND tr.C_OSSZESCSOPORTRAVONATKOZIK = 'T' AND tr.TOROLT = 'F' AND nn.C_TANEVID = tr.C_TANEVID
|
|
WHERE DATEPART(dw, nn.C_NAPDATUMA) = 6
|
|
AND nn.C_TANEVID = @tanevId
|
|
AND nn.C_NAPDATUMA BETWEEN @firstDay AND @lastDay
|
|
|
|
UPDATE nn SET
|
|
nn.C_ALAPORARENDINAP = 'F'
|
|
,nn.C_ORARENDINAP = IIF(tr.ID IS NULL, 'F', nn.C_ORARENDINAP)
|
|
,nn.C_NAPTIPUSA = IIF(tr.ID IS NULL, 1386, nn.C_NAPTIPUSA)
|
|
,nn.C_ALAPNAPTIPUSA = 1386
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA AND tr.C_OSSZESCSOPORTRAVONATKOZIK = 'T' AND tr.TOROLT = 'F' AND nn.C_TANEVID = tr.C_TANEVID
|
|
WHERE DATEPART(dw, nn.C_NAPDATUMA) = 7
|
|
AND nn.C_TANEVID = @tanevId
|
|
AND nn.C_NAPDATUMA BETWEEN @firstDay AND @lastDay
|
|
END
|
|
|
|
IF @hetvegiTanitas = 3 BEGIN -- H - V
|
|
UPDATE nn SET
|
|
nn.C_ALAPORARENDINAP = 'T'
|
|
,nn.C_ORARENDINAP = IIF(tr.ID IS NULL, 'T', nn.C_ORARENDINAP)
|
|
,nn.C_NAPTIPUSA = IIF(tr.ID IS NULL, 1385, nn.C_NAPTIPUSA)
|
|
,nn.C_ALAPNAPTIPUSA = 1385
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA AND tr.C_OSSZESCSOPORTRAVONATKOZIK = 'T' AND tr.TOROLT = 'F' AND nn.C_TANEVID = tr.C_TANEVID
|
|
WHERE DATEPART(dw, nn.C_NAPDATUMA) IN (6, 7)
|
|
AND nn.C_TANEVID = @tanevId
|
|
AND nn.C_NAPDATUMA BETWEEN @firstDay AND @lastDay
|
|
END
|
|
|
|
UPDATE n SET
|
|
n.C_HETIREND = IIF(x.TOROLT = 'F', ISNULL(x.C_HETIREND, ISNULL(h.C_HETIREND,n.C_ALAPHETIREND)), ISNULL(h.C_HETIREND,n.C_ALAPHETIREND))
|
|
,n.C_HETNAPJA = IIF(x.TOROLT = 'F', ISNULL(x.C_HETNAPJA, n.C_HETNAPJA), n.C_ALAPHETNAPJA)
|
|
,n.C_NAPTIPUSA = IIF(x.TOROLT = 'F', ISNULL(x.C_NAPTIPUSA, n.C_NAPTIPUSA), n.C_ALAPNAPTIPUSA)
|
|
,n.C_ORARENDINAP = IIF(x.TOROLT = 'F', ISNULL(x.C_ORARENDINAP, n.C_ORARENDINAP), n.C_ALAPORARENDINAP)
|
|
,n.C_AKTIVCSENGETESIRENDID = IIF(x.TOROLT = 'F', ISNULL(x.C_CSENGETESIRENDID, n.C_AKTIVCSENGETESIRENDID), n.C_ALAPCSENGETESIRENDID)
|
|
,n.C_ALAPHETIREND = IIF(@overrideDefault = 0 OR x.TOROLT = 'T', n.C_ALAPHETIREND, h.C_HETIREND)
|
|
,n.C_ALAPHETNAPJA = IIF(@overrideDefault = 0 OR x.TOROLT = 'T', n.C_ALAPHETNAPJA, ISNULL(x.C_HETNAPJA, n.C_ALAPHETNAPJA))
|
|
,n.C_ALAPNAPTIPUSA = IIF(@overrideDefault = 0 OR x.TOROLT = 'T', n.C_ALAPNAPTIPUSA, ISNULL(x.C_NAPTIPUSA, n.C_ALAPNAPTIPUSA))
|
|
,n.C_ALAPORARENDINAP = IIF(@overrideDefault = 0 OR x.TOROLT = 'T', n.C_ALAPORARENDINAP, ISNULL(x.C_ORARENDINAP, n.C_ALAPORARENDINAP))
|
|
,n.C_ALAPCSENGETESIRENDID = IIF(@overrideDefault = 0 OR x.TOROLT = 'T', n.C_AKTIVCSENGETESIRENDID, ISNULL(x.C_CSENGETESIRENDID, n.C_ALAPCSENGETESIRENDID))
|
|
FROM T_NAPTARINAP_OSSZES n
|
|
INNER JOIN T_NAPTARIHET_OSSZES h ON n.C_NAPDATUMA BETWEEN h.C_HETKEZDONAPJA AND h.C_HETUTOLSONAPJA
|
|
LEFT JOIN (
|
|
SELECT ro.C_DATUM, ro.TOROLT, ro.C_HETNAPJA, ro.C_NAPTIPUSA, ro.C_ORARENDINAP, ro.C_CSENGETESIRENDID ,ro.C_HETIREND
|
|
FROM T_TANEVRENDJE_OSSZES ro
|
|
WHERE ro.C_TANEVID = @tanevId
|
|
AND ro.TOROLT = 'F'
|
|
AND NOT EXISTS (SELECT 1 FROM T_OSZTALYCSOPORT_TANEVRENDJE o WHERE o.C_TANEVRENDJEID = ro.ID)
|
|
UNION ALL
|
|
SELECT DISTINCT ro.C_DATUM, ro.TOROLT, NULL, NULL, NULL, NULL,NULL
|
|
FROM T_TANEVRENDJE_OSSZES ro
|
|
WHERE ro.C_TANEVID = @tanevId
|
|
AND ro.TOROLT = 'T'
|
|
AND NOT EXISTS (SELECT 1 FROM T_TANEVRENDJE_OSSZES r WHERE ro.C_DATUM = r.C_DATUM AND C_TANEVID = @tanevId AND TOROLT = 'F')
|
|
AND NOT EXISTS (SELECT 1 FROM T_OSZTALYCSOPORT_TANEVRENDJE o WHERE o.C_TANEVRENDJEID = ro.ID)
|
|
) x ON x.C_DATUM = n.C_NAPDATUMA
|
|
WHERE n.C_TANEVID = @tanevId AND h.C_TANEVID = @tanevId
|
|
END
|
|
END
|
|
GO
|