90 lines
2.9 KiB
Transact-SQL
90 lines
2.9 KiB
Transact-SQL
-- ==========================================================================================
|
|
-- Author: Telek Ákos
|
|
-- Create date: 2016.06.08.
|
|
-- Description: Naptári hetek generálása TANEVRENDJE alapján
|
|
-- ==========================================================================================
|
|
DROP PROCEDURE IF EXISTS sp_GenerateNaptariHetek
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GenerateNaptariHetek
|
|
@intezmenyId int
|
|
,@tanevId int
|
|
,@ABHet bit = 1
|
|
,@hetiRendTipusTypeId int = 49
|
|
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,
|
|
@cnt int;
|
|
|
|
DELETE FROM T_NAPTARIHET_OSSZES
|
|
WHERE C_INTEZMENYID = @intezmenyId
|
|
AND C_TANEVID = @tanevId
|
|
|
|
/* megnézzük, hány nem 1554-es hetirend van */
|
|
SELECT @cnt = COUNT(1)
|
|
FROM T_HETIRENDTIPUS_OSSZES
|
|
WHERE ID <> 1554 AND C_ALTANEVID = @tanevId
|
|
AND TOROLT='F'
|
|
|
|
SELECT @endDate = C_UTOLSONAP, @startDate = C_KEZDONAP
|
|
FROM T_TANEV_OSSZES
|
|
WHERE ID = @tanevId
|
|
AND TOROLT='F';
|
|
|
|
/* CTE a napok legenerálásához */
|
|
;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
|
|
)
|
|
|
|
/* Maga az inzert */
|
|
INSERT INTO T_NAPTARIHET_OSSZES (
|
|
C_HETIREND
|
|
,C_HETKEZDONAPJA
|
|
,C_HETSORSZAMA
|
|
,C_HETUTOLSONAPJA
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
IIF(@ABHet=1,ISNULL(x.ID, 1554),1554) AS C_HETIREND -- C_HETIREND - int
|
|
,mindate AS C_HETKEZDONAPJA -- C_HETKEZDONAPJA - datetime
|
|
,week AS C_HETSORSZAMA -- C_HETSORSZAMA - int
|
|
,maxdate AS C_HETUTOLSONAPJA -- C_HETUTOLSONAPJA - datetime
|
|
,@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 (
|
|
SELECT
|
|
MIN(dates) AS mindate -- C_HETKEZDONAPJA - datetime
|
|
,week AS week -- C_HETSORSZAMA - int
|
|
,MAX(dates) AS maxdate -- C_HETUTOLSONAPJA - datetime
|
|
FROM ctedaterange
|
|
GROUP BY week
|
|
) c
|
|
LEFT JOIN (
|
|
SELECT ROW_NUMBER() OVER (ORDER BY C_VALUE) AS RN, ID
|
|
FROM T_DICTIONARYITEMBASE_OSSZES d
|
|
WHERE d.C_DICTIONARYTYPEID = @hetiRendTipusTypeId AND ID <> 1554
|
|
AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId AND TOROLT='F'
|
|
) x ON x.RN % NULLIF(@cnt, 0) = c.week % NULLIF(@cnt, 0)
|
|
OPTION (maxrecursion 0)
|
|
END
|
|
GO
|