367 lines
12 KiB
Transact-SQL
367 lines
12 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspGenerateNemKivalaszthatoTanev
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspGenerateNemKivalaszthatoTanev
|
|
@tanevNev nvarchar(10)
|
|
,@elsoTanitasiNap nvarchar(4) = '0901'
|
|
,@utolsoTanitasiNap nvarchar(4) = '0615'
|
|
AS BEGIN
|
|
|
|
-- @tanevNev nvarchar(10) = '2020/2021'
|
|
--,@elsoTanitasiNap nvarchar(4) = '0901'
|
|
--,@utolsoTanitasiNap nvarchar(4) = '0615'
|
|
DECLARE
|
|
@tanevElsoFele nvarchar(4) = SUBSTRING(@tanevNev,1,4)
|
|
,@tanevMasodikFele nvarchar(4) = SUBSTRING(@tanevNev,6,4)
|
|
|
|
SET NOCOUNT ON;
|
|
UPDATE T_TANEV SET
|
|
TOROLT='T'
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = 0
|
|
WHERE C_ISKIVALASZTHATO = 'F' AND TOROLT ='F' AND C_KOVETKEZO = 'F'
|
|
|
|
INSERT INTO T_TANEV (
|
|
C_AKTIV
|
|
,C_ELSOTANITASINAP
|
|
,C_KEZDONAP
|
|
,C_KOVETKEZO
|
|
,C_NEV
|
|
,C_SORSZAM
|
|
,C_UTOLSONAP
|
|
,C_UTOLSOTANITASINAP
|
|
,C_AKTIVALVA
|
|
,C_INTEZMENYID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
,NNID
|
|
,C_ISKIVALASZTHATO
|
|
) SELECT
|
|
'F' AS C_AKTIV
|
|
,@tanevElsoFele+@elsoTanitasiNap AS C_ELSOTANITASINAP
|
|
,@tanevElsoFele+'0901' AS C_KEZDONAP
|
|
,'F' AS C_KOVETKEZO
|
|
,@tanevNev AS C_NEV
|
|
,SUBSTRING(@tanevNev,3,2)*2 AS C_SORSZAM
|
|
,@tanevMasodikFele+'0831' AS C_UTOLSONAP
|
|
,@tanevMasodikFele+@utolsoTanitasiNap AS C_UTOLSOTANITASINAP
|
|
,NULL AS C_AKTIVALVA
|
|
,i.ID AS C_INTEZMENYID
|
|
,'F' AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,NULL AS MODIFIER
|
|
,NULL AS CREATOR
|
|
,NULL AS ELOZOTANEVIREKORDID
|
|
,NULL AS NNID
|
|
,'F' AS C_ISKIVALASZTHATO
|
|
FROM T_INTEZMENY i
|
|
WHERE i.TOROLT= 'F'
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM T_TANEV tv
|
|
WHERE tv.C_NEV = @tanevNev AND tv.C_INTEZMENYID = i.ID AND tv.TOROLT='F')
|
|
|
|
;DISABLE TRIGGER ALL ON T_DICTIONARYITEMBASE
|
|
SET IDENTITY_INSERT T_DICTIONARYTYPE ON
|
|
INSERT INTO T_DICTIONARYTYPE (
|
|
ID
|
|
,C_CODENAME
|
|
,C_DESCRIPTION
|
|
,C_ISCOLOREDITABLE
|
|
,C_ISCUSTOMITEMALLOWED
|
|
,C_NAME
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
dt.ID
|
|
,dt.C_CODENAME AS C_CODENAME -- C_CODENAME - nvarchar(30)
|
|
,dt.C_DESCRIPTION AS C_DESCRIPTION -- C_DESCRIPTION - nvarchar(255)
|
|
,dt.C_ISCOLOREDITABLE AS C_ISCOLOREDITABLE -- C_ISCOLOREDITABLE - char(1)
|
|
,dt.C_ISCUSTOMITEMALLOWED AS C_ISCUSTOMITEMALLOWED -- C_ISCUSTOMITEMALLOWED - char(1)
|
|
,dt.C_NAME AS C_NAME -- C_NAME - nvarchar(50)
|
|
,dt.C_INTEZMENYID AS C_INTEZMENYID -- C_INTEZMENYID - int
|
|
,tnHidden.ID AS C_TANEVID -- C_TANEVID - int
|
|
,dt.TOROLT 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 T_DICTIONARYTYPE dt
|
|
INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=dt.C_INTEZMENYID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
|
|
INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=dt.C_INTEZMENYID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
|
|
AND tnHidden.C_NEV = @tanevNev
|
|
WHERE dt.C_TANEVID = tn.ID
|
|
SET IDENTITY_INSERT T_DICTIONARYTYPE OFF
|
|
|
|
SET IDENTITY_INSERT T_DICTIONARYITEMBASE ON
|
|
INSERT INTO T_DICTIONARYITEMBASE (
|
|
ID
|
|
,C_VALUE
|
|
,C_NAME
|
|
,C_NAME_1
|
|
,C_NAME_2
|
|
,C_NAME_3
|
|
,C_NAME_4
|
|
,C_VISIBLE
|
|
,C_CODE
|
|
,C_TYPE
|
|
,C_PROTECTED
|
|
,C_COLOR
|
|
,C_ORDER
|
|
,C_BGCOLOR
|
|
,C_DESCRIPTION
|
|
,C_ISACTIVE
|
|
,C_SHORTNAME
|
|
,C_DICTIONARYTYPEID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
dib.ID
|
|
,C_VALUE AS C_VALUE -- C_VALUE - int
|
|
,C_NAME AS C_NAME -- C_NAME - nvarchar(250)
|
|
,C_NAME_1 AS C_NAME_1 -- C_NAME_1 - nvarchar(250)
|
|
,C_NAME_2 AS C_NAME_2 -- C_NAME_2 - nvarchar(250)
|
|
,C_NAME_3 AS C_NAME_3 -- C_NAME_3 - nvarchar(250)
|
|
,C_NAME_4 AS C_NAME_4 -- C_NAME_4 - nvarchar(250)
|
|
,C_VISIBLE AS C_VISIBLE -- C_VISIBLE - char(1)
|
|
,C_CODE AS C_CODE -- C_CODE - nvarchar(20)
|
|
,C_TYPE AS C_TYPE -- C_TYPE - nvarchar(32)
|
|
,C_PROTECTED AS C_PROTECTED -- C_PROTECTED - char(1)
|
|
,C_COLOR AS C_COLOR -- C_COLOR - nvarchar(6)
|
|
,C_ORDER AS C_ORDER -- C_ORDER - int
|
|
,C_BGCOLOR AS C_BGCOLOR -- C_BGCOLOR - nvarchar(6)
|
|
,C_DESCRIPTION AS C_DESCRIPTION -- C_DESCRIPTION - nvarchar(255)
|
|
,C_ISACTIVE AS C_ISACTIVE -- C_ISACTIVE - char(1)
|
|
,C_SHORTNAME AS C_SHORTNAME -- C_SHORTNAME - nvarchar(30)
|
|
,C_DICTIONARYTYPEID AS C_DICTIONARYTYPEID -- C_DICTIONARYTYPEID - int
|
|
,dib.C_INTEZMENYID AS C_INTEZMENYID -- C_INTEZMENYID - int
|
|
,tnHidden.ID AS C_TANEVID -- C_TANEVID - int
|
|
,dib.TOROLT 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
|
|
,dib.ID AS ELOZOTANEVIREKORDID -- ELOZOTANEVIREKORDID - int
|
|
FROM T_DICTIONARYITEMBASE dib
|
|
INNER JOIN T_INTEZMENY i ON i.ID=dib.C_INTEZMENYID AND i.TOROLT='F' AND i.C_FENNTARTOAZONOSITO<900
|
|
INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
|
|
INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
|
|
AND tnHidden.C_NEV = @tanevNev
|
|
WHERE dib.C_TANEVID = tn.ID AND dib.ID<100000
|
|
|
|
SET IDENTITY_INSERT T_DICTIONARYITEMBASE OFF
|
|
-- print 'Szótárelemek létrehozása sikeres!';
|
|
|
|
INSERT INTO T_OKTNEVELESIKATEGORIA (
|
|
ID
|
|
,C_FELADATCSOPORTTANULOOSZTALYK
|
|
,C_ALINTEZMENYID
|
|
,C_ALTANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
,ELOZOTANEVIREKORDIDA
|
|
,NNID)
|
|
SELECT
|
|
o.ID
|
|
,C_FELADATCSOPORTTANULOOSZTALYK
|
|
,i.ID
|
|
,tnHidden.ID
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,0
|
|
,0
|
|
,o.ID
|
|
,o.ID
|
|
,o.NNID
|
|
FROM T_OKTNEVELESIKATEGORIA o
|
|
INNER JOIN T_INTEZMENY i ON i.ID=o.C_ALINTEZMENYID AND i.TOROLT='F' AND i.C_FENNTARTOAZONOSITO<900
|
|
INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
|
|
INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
|
|
AND tnHidden.C_NEV = @tanevNev
|
|
WHERE C_ALTANEVID=tn.ID AND o.TOROLT='F' AND o.ID<100000
|
|
|
|
INSERT INTO T_DICTIONARYITEMBASENYELV(
|
|
C_NAME
|
|
,C_NAME_1
|
|
,C_NAME_2
|
|
,C_NAME_3
|
|
,C_NAME_4
|
|
,C_NYELVID
|
|
,C_SHORTNAME
|
|
,C_DICTIONARYITEMBASEID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
dibNy.C_NAME
|
|
,dibNy.C_NAME_1
|
|
,dibNy.C_NAME_2
|
|
,dibNy.C_NAME_3
|
|
,dibNy.C_NAME_4
|
|
,dibNyelv.ID
|
|
,dibNy.C_SHORTNAME
|
|
,dibNew.ID
|
|
,tnKov.C_INTEZMENYID
|
|
,tn.ID
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,NULL
|
|
,NULL
|
|
,dibNy.ID
|
|
FROM T_DICTIONARYITEMBASENYELV dibNy
|
|
INNER JOIN T_TANEV tn ON dibNy.C_TANEVID = tn.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
|
|
INNER JOIN T_TANEV tnKov ON tnKov.C_INTEZMENYID = tn.C_INTEZMENYID AND tnKov.C_ISKIVALASZTHATO = 'F' AND tnKov.TOROLT='F'
|
|
AND tnKov.C_NEV = @tanevNev
|
|
INNER JOIN T_DICTIONARYITEMBASE dibNew ON dibNew.ELOZOTANEVIREKORDID=dibNy.C_DICTIONARYITEMBASEID
|
|
AND dibNew.C_TANEVID = tnKov.ID AND dibNew.TOROLT='F'
|
|
INNER JOIN T_DICTIONARYITEMBASE dibNyelv ON dibNyelv.ELOZOTANEVIREKORDID = dibNy.C_NYELVID AND dibNyelv.TOROLT='F'
|
|
WHERE dibNy.C_TANEVID = tn.ID AND dibNy.TOROLT='F'
|
|
AND NOT EXISTS (SELECT 1 FROM T_DICTIONARYITEMBASENYELV WHERE TOROLT='F' AND C_DICTIONARYITEMBASEID = dibNew.ID AND C_TANEVID = tnKov.ID)
|
|
|
|
DECLARE tableCursor CURSOR FOR
|
|
SELECT DISTINCT OBJECT_NAME(parent_object_id)
|
|
FROM sys.foreign_key_columns
|
|
WHERE referenced_object_id = OBJECT_ID('T_DICTIONARYITEMBASE') AND OBJECT_NAME(parent_object_id) NOT IN ('T_OKTNEVELESIKATEGORIA','T_DICTIONARYITEMBASENYELV')
|
|
|
|
DECLARE @tableName nvarchar(80)
|
|
,@sql nvarchar(max)
|
|
,@columnList nvarchar(max)
|
|
,@insertList nvarchar(max)
|
|
|
|
OPEN tableCursor
|
|
FETCH NEXT FROM tableCursor into @tableName
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
SELECT @columnList = ISNULL(@columnList + ', ', '') + c.[name]
|
|
FROM sys.tables t
|
|
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
|
|
WHERE t.[name] = @tableName
|
|
ORDER BY c.column_id
|
|
|
|
SET @insertList = @columnList
|
|
SET @insertList = REPLACE(@insertList, 'C_ALTANEVID', 'tnHidden.ID')
|
|
SET @insertList = REPLACE(@insertList, 'SERIAL', '0')
|
|
SET @insertList = REPLACE(@insertList, 'LASTCHANGED', 'GETDATE()')
|
|
SET @insertList = REPLACE(@insertList, 'CREATED', 'GETDATE()')
|
|
SET @insertList = REPLACE(@insertList, 'MODIFIER', 'NULL')
|
|
SET @insertList = REPLACE(@insertList, 'CREATOR', 'NULL')
|
|
SET @insertList = REPLACE(@insertList, 'TOROLT', 'x.TOROLT')
|
|
SET @insertList = REPLACE(@insertList, 'NNID', 'x.NNID')
|
|
SET @insertList = REPLACE(@insertList, 'ELOZOTANEVIREKORDIDA', 'x.ID')
|
|
SET @insertList = REPLACE(@insertList, 'ELOZOTANEVIREKORDID', 'x.ID')
|
|
|
|
|
|
SET @sql = N'
|
|
DISABLE TRIGGER ALL ON ' + @tableName +'
|
|
INSERT INTO ' + @tableName + ' (
|
|
' + @columnList + '
|
|
) SELECT ' + @insertList + '
|
|
FROM ' + @tableName + ' x
|
|
INNER JOIN T_INTEZMENY i ON i.ID=x.C_ALINTEZMENYID AND i.TOROLT=''F'' AND i.C_FENNTARTOAZONOSITO<900
|
|
INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV=''T'' AND tn.TOROLT=''F''
|
|
INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO=''F'' AND tnHidden.C_ISKIVALASZTHATO=''F'' AND tnHidden.TOROLT=''F''
|
|
AND tnHidden.C_NEV = @tanevNev
|
|
WHERE x.C_ALTANEVID = tn.ID
|
|
AND x.C_ALINTEZMENYID = i.ID
|
|
AND x.TOROLT = ''F''
|
|
AND x.ID<100000;
|
|
ENABLE TRIGGER ALL ON ' + @tableName +'
|
|
'
|
|
SET @sql = REPLACE(@sql, 'SELECT ID','SELECT x.ID')
|
|
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@tanevNev nvarchar(10)'
|
|
,@tanevNev = @tanevNev
|
|
|
|
SET @columnList = NULL
|
|
FETCH NEXT FROM tableCursor into @tableName
|
|
END
|
|
|
|
CLOSE tableCursor
|
|
DEALLOCATE tableCursor
|
|
|
|
INSERT INTO T_TANTERV (
|
|
C_CSOPORTTIPUSA
|
|
,C_EVFOLYAMTOL
|
|
,C_EVFOLYAMIG
|
|
,C_KERETTANTERVREEPULO
|
|
,C_NEV
|
|
,C_MEGJEGYZES
|
|
,C_VEDETT
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
t.C_CSOPORTTIPUSA AS C_CSOPORTTIPUSA
|
|
,t.C_EVFOLYAMTOL AS C_EVFOLYAMTOL
|
|
,t.C_EVFOLYAMIG AS C_EVFOLYAMIG
|
|
,C_KERETTANTERVREEPULO AS C_KERETTANTERVREEPULO
|
|
,t.C_NEV AS C_NEV
|
|
,C_MEGJEGYZES AS C_MEGJEGYZES
|
|
,t.C_VEDETT
|
|
,i.ID AS C_INTEZMENYID
|
|
,tnHidden.ID AS C_TANEVID
|
|
,'F' AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,NULL AS MODIFIER
|
|
,NULL AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_TANTERV t
|
|
INNER JOIN T_INTEZMENY i ON i.ID=t.C_INTEZMENYID AND i.TOROLT='F' AND i.C_FENNTARTOAZONOSITO<900
|
|
INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
|
|
INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
|
|
AND tnHidden.C_NEV = @tanevNev
|
|
WHERE t.C_TANEVID = tn.ID
|
|
AND t.TOROLT = 'F' AND t.C_CSOPORTTIPUSA<100000;
|
|
|
|
ENABLE TRIGGER ALL ON T_DICTIONARYITEMBASE;
|
|
|
|
END
|
|
GO |