63 lines
4.2 KiB
Transact-SQL
63 lines
4.2 KiB
Transact-SQL
DECLARE @TEMP_RENDSZERBEALLITAS TABLE (
|
|
C_BEALLITASTIPUS INT not null,
|
|
C_ERTEKTIPUS INT not null,
|
|
C_ALAPERTEK NVARCHAR(MAX) null,
|
|
C_ERTEK NVARCHAR(MAX) not null,
|
|
SERIAL INT null,
|
|
CREATOR INT null,
|
|
C_INTEZMENYID INT not null,
|
|
C_TANEVID INT not null
|
|
)
|
|
|
|
DECLARE intezmenytanevekCursor CURSOR LOCAL FOR
|
|
SELECT i.C_INTEZMENYID, i.C_TANEVID
|
|
FROM T_INTEZMENYADATOK i
|
|
INNER JOIN T_TANEV_OSSZES t ON t.ID = i.C_TANEVID
|
|
AND t.C_NEV = '2017/2018'
|
|
|
|
DECLARE @intezmenyId int, @tanevId int
|
|
|
|
OPEN intezmenytanevekCursor
|
|
FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @tanevId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
PRINT N'INTÉZMÉNY ID: ' + CAST(@intezmenyId AS NVARCHAR(10)) + N', TANÉV ID: ' + CAST(@tanevId AS NVARCHAR(10))
|
|
|
|
DECLARE @felevVegeDatum nvarchar(50) = (SELECT FORMAT(C_DATUM, 'yyyy-MM-dd') FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA = 1400 AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId)
|
|
DECLARE @evVegeDatum nvarchar(50) = (SELECT FORMAT(C_DATUM, 'yyyy-MM-dd') FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA = 1395 AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId)
|
|
DECLARE @evElsoTanitasiNapja nvarchar(50) = (SELECT FORMAT(C_DATUM, 'yyyy-MM-dd') FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA = 1394 AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId)
|
|
|
|
SET @felevVegeDatum = ISNULL(@felevVegeDatum, '2000-01-01')
|
|
SET @evVegeDatum = ISNULL(@evVegeDatum, '2000-01-01')
|
|
SET @evElsoTanitasiNapja = ISNULL(@evElsoTanitasiNapja, '2000-09-01')
|
|
|
|
INSERT INTO @TEMP_RENDSZERBEALLITAS (C_BEALLITASTIPUS, C_ERTEKTIPUS, C_ALAPERTEK, C_ERTEK, SERIAL, CREATOR, C_INTEZMENYID, C_TANEVID)
|
|
VALUES
|
|
(3076, 1696, '{"Id":"Ertekeles_datumanak_minimuma","Date":"' + @evElsoTanitasiNapja + '"}', '{"Id":"Ertekeles_datumanak_minimuma","Date":"' + @evElsoTanitasiNapja + '"}', 0, 1, @intezmenyId, @tanevId),
|
|
(3082, 1696, '{"Id":"Naplozaras_eves_datuma","Date":"' + @evElsoTanitasiNapja + '"}', '{"Id":"Naplozaras_eves_datuma","Date":"' + @evElsoTanitasiNapja + '"}', 0, 1, @intezmenyId, @tanevId),
|
|
(5438, 1696, '{"Id":"Felevi_jegyek_megjelenesenek_datuma","Date":"' + @felevVegeDatum + '"}', '{"Id":"Felevi_jegyek_megjelenesenek_datuma","Date":"' + @felevVegeDatum + '"}', 0, 1, @intezmenyId, @tanevId),
|
|
(5439, 1696, '{"Id":"Ev_vegi_jegyek_megjelenesenek_datuma","Date":"' + @evVegeDatum + '"}', '{"Id":"Ev_vegi_jegyek_megjelenesenek_datuma","Date":"' + @evVegeDatum + '"}', 0, 1, @intezmenyId, @tanevId),
|
|
(5440, 1696, '{"Id":"Ev_vegi_jegyek_megjelenesenek_datuma_vegzos","Date":"' + @evVegeDatum + '"}', '{"Id":"Ev_vegi_jegyek_megjelenesenek_datuma_vegzos","Date":"' + @evVegeDatum + '"}', 0, 1, @intezmenyId, @tanevId),
|
|
|
|
/*Értesítések*/
|
|
(1690, 1696, '{"Id":"Ertesites_bukasra_allo_tanulokrol","Date":"' + LEFT(@evVegeDatum, 4) + '-05-02"}', '{"Id":"Ertesites_bukasra_allo_tanulokrol","Date":"' + LEFT(@evVegeDatum, 4) + '-05-02"}', 0, 1, @intezmenyId, @tanevId),
|
|
(1691, 1696, '{"Id":"Ertesites_bukasra_allo_vegzos_tanulokrol","Date":"' + LEFT(@evVegeDatum, 4) + '-04-02"}', '{"Id":"Ertesites_bukasra_allo_vegzos_tanulokrol","Date":"' + LEFT(@evVegeDatum, 4) + '-04-02"}', 0, 1, @intezmenyId, @tanevId),
|
|
(1687, 1696, '{"Id":"Ertesites_Tanev_rendi_valtoztatasrol_idopont","Date":"' + @evElsoTanitasiNapja + '"}', '{"Id":"Ertesites_Tanev_rendi_valtoztatasrol_idopont","Date":"' + @evElsoTanitasiNapja + '"}', 0, 1, @intezmenyId, @tanevId)
|
|
|
|
UPDATE rb
|
|
SET
|
|
rb.C_ALAPERTEK = tmp.C_ALAPERTEK
|
|
,rb.C_ERTEK = tmp.C_ERTEK
|
|
FROM dbo.T_RENDSZERBEALLITAS_OSSZES rb
|
|
INNER JOIN @TEMP_RENDSZERBEALLITAS tmp ON tmp.C_BEALLITASTIPUS = rb.C_BEALLITASTIPUS
|
|
AND tmp.C_ERTEKTIPUS = rb.C_ERTEKTIPUS
|
|
|
|
FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @tanevId
|
|
END
|
|
|
|
CLOSE intezmenytanevekCursor
|
|
DEALLOCATE intezmenytanevekCursor
|
|
|
|
GO
|