220 lines
No EOL
11 KiB
Transact-SQL
220 lines
No EOL
11 KiB
Transact-SQL
DROP FUNCTION IF EXISTS fnGetFollowUpColumnMapping
|
|
GO
|
|
|
|
CREATE FUNCTION fnGetFollowUpColumnMapping (@ExludeColumns nvarchar(max) = NULL,@entityName nvarchar(128))
|
|
RETURNS @ColumnMapping TABLE (
|
|
insertColName nvarchar(max) COLLATE DATABASE_DEFAULT
|
|
,selectColName nvarchar(max)
|
|
,updateColName nvarchar(max)
|
|
) AS
|
|
BEGIN
|
|
SET @ExludeColumns = ISNULL(@ExludeColumns+',','')+'ID,CORE_ID'
|
|
DECLARE @excludedColumns TABLE(colName nvarchar(128) COLLATE DATABASE_DEFAULT)
|
|
INSERT INTO @excludedColumns
|
|
SELECT
|
|
value
|
|
FROM string_split(@ExludeColumns,',')
|
|
|
|
INSERT INTO @ColumnMapping (insertColName, selectColName) VALUES
|
|
('TOROLT','''F''')
|
|
,('SERIAL','0')
|
|
,('CREATOR','s.MODIFIER')
|
|
,('CREATED','s.LASTCHANGED')
|
|
,('ELOZOTANEVIREKORDID','s.ID')
|
|
|
|
INSERT INTO @ColumnMapping (insertColName,selectColName)
|
|
SELECT
|
|
c.name,'s.'+c.name
|
|
FROM sys.columns c
|
|
INNER JOIN sys.tables t ON t.object_id = c.object_id AND t.name = @entityName
|
|
WHERE c.NAME NOT IN (SELECT colName from @excludedColumns)
|
|
AND NOT EXISTS (SELECT 1 FROM @ColumnMapping WHERE insertColName = c.name)
|
|
|
|
DELETE FROM @ColumnMapping
|
|
WHERE NOT EXISTS (
|
|
SELECT
|
|
1
|
|
FROM sys.columns c
|
|
INNER JOIN sys.tables t ON t.object_id = c.object_id AND t.name = @entityName
|
|
WHERE c.NAME = insertColName)
|
|
|
|
DELETE @ColumnMapping WHERE insertColName IN (select colName FROM @excludedColumns)
|
|
|
|
UPDATE @ColumnMapping SET selectColName = '@kovetkezoTanevId' WHERE insertColName IN('C_TANEVID','C_ALTANEVID')
|
|
UPDATE @ColumnMapping SET selectColName = '@intezmenyId' WHERE insertColName IN('C_INTEZMENYID','C_ALINTEZMENYID')
|
|
|
|
IF @entityName = 'T_TANULO' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = '''F''' WHERE insertColName IN ('C_EVISMETLO', 'C_ISBESZAMITASOS', 'C_ISOSZTONDIJBOLKIZARVA', 'C_ISMULASZTASMIATTKIZART')
|
|
UPDATE @ColumnMapping SET selectColName = '''T''' WHERE insertColName IN ('C_ISATLAGSZAKKEPZESI', 'C_ISBESZAMITASOSFIXSZAZALEK')
|
|
END
|
|
ELSE IF @entityName IN('T_CIM','T_EMAIL','T_TELEFON') BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = '@kovFelhasznaloId' WHERE insertColName = 'C_FELHASZNALOID'
|
|
END
|
|
ELSE IF @entityName IN('T_MUNKAUGYIADATOK','T_TOVABBIMUNKAUGYIADATOK') BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = '@ujAlkalmazottId' WHERE insertColName = 'C_ALKALMAZOTTID'
|
|
UPDATE @ColumnMapping SET selectColName = 'f.ID' WHERE insertColName = 'C_FELADATELLATASIHELYID'
|
|
END
|
|
ELSE IF @entityName = 'T_ALKALMAZOTT' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 's.ID' WHERE insertColName = 'ELOZOTANEVIREKORDIDA'
|
|
UPDATE @ColumnMapping SET selectColName = 'IIF((select ic.C_ERTEK from T_INTEZMENYCONFIG ic where ic.C_CONFIGTIPUSID = 23 and ic.TOROLT = ''F'' and ic.C_INTEZMENYID = @intezmenyId) = ''true'',s.C_KKKEPESITESID,NULL)' WHERE insertColName IN('C_KKKEPESITESID')
|
|
END
|
|
ELSE IF @entityName IN('T_KKTANITOVEZGETTSEG','T_KKGYOGYPEDVEGZETTSEG','T_KKTANARVEGZETTSEG','T_KKAMIVEGZETTSEG') BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = '@kovAlkalmazottId' WHERE insertColName = 'C_ALKALMAZOTTID'
|
|
END
|
|
ELSE IF @entityName IN('T_KKELEKTROAKUZENE','T_KKKLASSZIKUSZENE','T_KKJAZZZENE','T_KKNEPZENE','T_KKTERULET') BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'av.ID' WHERE insertColName = 'C_KKAMIVEGZETTSEGID'
|
|
END
|
|
ELSE IF @entityName = 'T_KKTANTARGYKATEGORIA' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'av.ID' WHERE insertColName = 'C_KKTANARVEGZETTSEGID'
|
|
END
|
|
ELSE IF @entityName IN ('T_PEDAGOGUSELETPALYAMODELL','T_VEGZETTSEG') BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'fh.ID' WHERE insertColName = 'C_TANARID'
|
|
END
|
|
ELSE IF @entityName = 'T_KEPESITES_VEGZETTSEG' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'k.ID' WHERE insertColName = 'C_KEPESITESID'
|
|
UPDATE @ColumnMapping SET selectColName = 'vKov.ID' WHERE insertColName = 'C_VEGZETTSEGID'
|
|
END
|
|
ELSE IF @entityName = 'T_TANULOMENTESSEG' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'tgy.ID' WHERE insertColName = 'C_TANTARGYID'
|
|
UPDATE @ColumnMapping SET selectColName = 'tKov.ID' WHERE insertColName = 'C_TANULOID'
|
|
END
|
|
ELSE IF @entityName = 'T_TANULOSNI' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'tKov.ID' WHERE insertColName = 'C_TANULOID'
|
|
END
|
|
ELSE IF @entityName = 'T_GONDVISELO' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'fhNew.ID' WHERE insertColName = 'C_TANULOID'
|
|
END
|
|
ELSE IF @entityName = 'T_ORATERVTARGY' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'tgy.ID' WHERE insertColName = 'C_TANTARGYID'
|
|
UPDATE @ColumnMapping SET selectColName = 'otKov.ID' WHERE insertColName = 'C_ORATERVID'
|
|
END
|
|
ELSE IF @entityName = 'T_ORATERV' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'e.ID' WHERE insertColName = 'C_EVFOLYAM'
|
|
UPDATE @ColumnMapping SET selectColName = 'tr.ID' WHERE insertColName = 'C_TANTERVID'
|
|
END
|
|
ELSE IF @entityName = 'T_CSENGETESIREND' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'mh.ID' WHERE insertColName = 'C_MUKODESIHELYID'
|
|
END
|
|
ELSE IF @entityName = 'T_CSENGETESIRENDORA' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'csr.ID' WHERE insertColName = 'C_CSENGETESIRENDID'
|
|
END
|
|
ELSE IF @entityName = 'T_TEREM' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'mh.ID' WHERE insertColName = 'C_MUKODESIHELYID'
|
|
UPDATE @ColumnMapping SET selectColName = '@felelosId' WHERE insertColName = 'C_TEREMFELELOSID'
|
|
END
|
|
ELSE IF @entityName = 'T_FELADATELLATASIHELY' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'mh.ID' WHERE insertColName = 'C_MUKODESIHELYID'
|
|
UPDATE @ColumnMapping SET selectColName = 'okt.ID' WHERE insertColName = 'C_OKTATASINEVELESIFELADATTIPUS'
|
|
END
|
|
ELSE IF @entityName = 'T_TANTARGY' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'tkt.ID' WHERE insertColName = 'C_TARGYKATEGORIA'
|
|
UPDATE @ColumnMapping SET selectColName = 'tszt.ID' WHERE insertColName = 'C_TANSZAKTIPUSID'
|
|
UPDATE @ColumnMapping SET selectColName = 'ftgy.ID' WHERE insertColName = 'C_FOTARGYID'
|
|
END
|
|
ELSE IF @entityName = 'T_MUKODESIHELY' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'ia.ID' WHERE insertColName = 'C_INTEZMENYADATOKID'
|
|
END
|
|
ELSE IF @entityName = 'T_ESZKOZ' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'te.ID' WHERE insertColName = 'C_TEREMID'
|
|
UPDATE @ColumnMapping SET selectColName = 'fh.ID' WHERE insertColName = 'C_FELELOSID'
|
|
UPDATE @ColumnMapping SET selectColName = '@aktTipusId' WHERE insertColName = 'C_TIPUS'
|
|
END
|
|
ELSE IF @entityName = 'T_TANTARGYNYELV' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = 'tgy.ID' WHERE insertColName = 'C_TANTARGYID'
|
|
UPDATE @ColumnMapping SET selectColName = 'dib.ID' WHERE insertColName = 'C_NYELVID'
|
|
END
|
|
ELSE IF @entityName = 'T_TBJOGVISZONY' BEGIN
|
|
UPDATE @ColumnMapping SET selectColName = '@KovFelhasznaloId' WHERE insertColName = 'C_TANULOID'
|
|
END
|
|
|
|
UPDATE @ColumnMapping SET
|
|
updateColName = 's.'+insertColName
|
|
WHERE insertColName NOT IN (
|
|
'CREATED'
|
|
,'CREATOR'
|
|
,'ELOZOTANEVIREKORDID'
|
|
,'ELOZOTANEVIREKORDIDA'
|
|
,'C_INTEZMENYID'
|
|
,'C_TANEVID'
|
|
,'C_ALINTEZMENYID'
|
|
,'C_ALTANEVID'
|
|
,'C_ALKALMAZOTTID'
|
|
,'TOROLT'
|
|
,'C_KKAMIVEGZETTSEGID'
|
|
)
|
|
|
|
IF @entityName = 'T_TANULO' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = '''F''' WHERE insertColName IN ('C_EVISMETLO', 'C_ISBESZAMITASOS', 'C_ISOSZTONDIJBOLKIZARVA', 'C_ISMULASZTASMIATTKIZART')
|
|
UPDATE @ColumnMapping SET updateColName = '''T''' WHERE insertColName IN ('C_ISATLAGSZAKKEPZESI', 'C_ISBESZAMITASOSFIXSZAZALEK')
|
|
END
|
|
ELSE IF @entityName = 'T_KKTANTARGYKATEGORIA' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_KKTANARVEGZETTSEGID'
|
|
END
|
|
ELSE IF @entityName IN('T_MUNKAUGYIADATOK','T_TOVABBIMUNKAUGYIADATOK') BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = 'f.ID' WHERE insertColName = 'C_FELADATELLATASIHELYID'
|
|
END
|
|
ELSE IF @entityName IN ('T_VEGZETTSEG','T_PEDAGOGUSELETPALYAMODELL') BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_TANARID'
|
|
END
|
|
ELSE IF @entityName = 'T_TANULOMENTESSEG' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = 'tgy.ID' WHERE insertColName = 'C_TANTARGYID'
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_TANULOID'
|
|
END
|
|
ELSE IF @entityName = 'T_TANULOSNI' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_TANULOID'
|
|
END
|
|
ELSE IF @entityName = 'T_GONDVISELO' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName IN ('C_TANULOID','C_EGYEDIAZONOSITO')
|
|
END
|
|
ELSE IF @entityName IN('T_CIM','T_TELEFON','T_EMAIL') BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName IN ('C_FELHASZNALOID','C_GONDVISELOID')
|
|
END
|
|
ELSE IF @entityName = 'T_ORATERVTARGY' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_ORATERVID'
|
|
UPDATE @ColumnMapping SET updateColname = 'tgy.ID' WHERE insertColName = 'C_TANTARGYID'
|
|
END
|
|
ELSE IF @entityName = 'T_ORATERV' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = 'e.ID' WHERE insertColName = 'C_EVFOLYAM'
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_TANTERVID'
|
|
END
|
|
ELSE IF @entityName = 'T_CSENGETESIREND' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_MUKODESIHELYID'
|
|
END
|
|
ELSE IF @entityName = 'T_CSENGETESIRENDORA' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_CSENGETESIRENDID'
|
|
END
|
|
ELSE IF @entityName = 'T_TEREM' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = 'mh.ID' WHERE insertColName = 'C_MUKODESIHELYID'
|
|
UPDATE @ColumnMapping SET updateColName = '@felelosId' WHERE insertColName = 'C_TEREMFELELOSID'
|
|
END
|
|
ELSE IF @entityName = 'T_FELADATELLATASIHELY' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_MUKODESIHELYID'
|
|
UPDATE @ColumnMapping SET updateColName = 'okt.ID' WHERE insertColName = 'C_OKTATASINEVELESIFELADATTIPUS'
|
|
END
|
|
ELSE IF @entityName = 'T_TANTARGY' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = 'tkt.ID' WHERE insertColName = 'C_TARGYKATEGORIA'
|
|
UPDATE @ColumnMapping SET updateColName = 'tszt.ID' WHERE insertColName = 'C_TANSZAKTIPUSID'
|
|
UPDATE @ColumnMapping SET updateColName = 'ftgy.ID' WHERE insertColName = 'C_FOTARGYID'
|
|
END
|
|
ELSE IF @entityName = 'T_MUKODESIHELY' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName = 'C_INTEZMENYADATOKID'
|
|
END
|
|
ELSE IF @entityName = 'T_ESZKOZ' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = 'te.ID' WHERE insertColName = 'C_TEREMID'
|
|
UPDATE @ColumnMapping SET updateColName = 'fh.ID' WHERE insertColName = 'C_FELELOSID'
|
|
UPDATE @ColumnMapping SET updateColName = '@aktTipusId' WHERE insertColName = 'C_TIPUS'
|
|
END
|
|
ELSE IF @entityName = 'T_TANTARGYNYELV' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName IN ('C_TANTARGYID','C_NYELVID')
|
|
END
|
|
ELSE IF @entityName = 'T_RENDSZERBEALLITAS' BEGIN
|
|
UPDATE @ColumnMapping SET updateColName = NULL WHERE insertColName IN ('C_ALAPERTEK','C_BEALLITASTIPUS','C_ERTEKTIPUS')
|
|
END
|
|
|
|
UPDATE @ColumnMapping SET
|
|
updateColName = 't.SERIAL + 1'
|
|
WHERE insertColName = 'SERIAL'
|
|
|
|
RETURN
|
|
END
|
|
GO |