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