﻿IF DB_NAME() LIKE 'KRETA[_]KLIK[_]%' BEGIN -- csak KK-n kell megfuttatni
  UPDATE T_ALKALMAZOTT
  SET C_KKKEPESITESID = 7244
  WHERE ID IN (
    SELECT C_TANARID FROM T_VEGZETTSEG WHERE TOROLT = 'F'
  )

  INSERT INTO T_KKTANITOVEZGETTSEG (
     C_KKTANIOTOMUVTERTIPUSID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT  
     7352 -- Na
    ,v.C_TANARID
    ,v.C_INTEZMENYID
    ,v.C_TANEVID
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,NULL
    ,NULL
    ,NULL
    ,NULL
  FROM T_VEGZETTSEG v
    INNER JOIN T_DICTIONARYITEMBASE vt ON v.C_INTEZMENYID = vt.C_INTEZMENYID AND v.C_TANEVID = vt.C_TANEVID AND v.C_VEGZETTSEGTIPUSID = vt.ID
    INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID = v.ID
    INNER JOIN T_KEPESITES k ON k.ID = kv.C_KEPESITESID
    INNER JOIN T_DICTIONARYITEMBASE szt ON szt.ID = k.C_KEPESITESTIPUSID AND szt.C_INTEZMENYID = k.C_INTEZMENYID AND szt.C_TANEVID = k.C_TANEVID
  WHERE szt.ID = 7089
    AND k.TOROLT = 'F'
    AND v.TOROLT = 'F'
  ORDER BY v.C_TANARID


  INSERT INTO T_KKGYOGYPEDVEGZETTSEG (
     C_KKGYOGYPEDTERTIPUSID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     7353 -- Na
    ,v.C_TANARID
    ,v.C_INTEZMENYID
    ,v.C_TANEVID
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,NULL
    ,NULL
    ,NULL
    ,NULL
  FROM T_VEGZETTSEG v
    INNER JOIN T_DICTIONARYITEMBASE vt ON v.C_INTEZMENYID = vt.C_INTEZMENYID AND v.C_TANEVID = vt.C_TANEVID AND v.C_VEGZETTSEGTIPUSID = vt.ID
    INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID = v.ID
    INNER JOIN T_KEPESITES k ON k.ID = kv.C_KEPESITESID
    INNER JOIN T_DICTIONARYITEMBASE szt ON szt.ID = k.C_KEPESITESTIPUSID AND szt.C_INTEZMENYID = k.C_INTEZMENYID AND szt.C_TANEVID = k.C_TANEVID
  WHERE szt.ID = 7092
    AND k.TOROLT = 'F'
    AND v.TOROLT = 'F'

  INSERT INTO T_KKTANARVEGZETTSEG (
     C_EGYEB
    ,C_KKTANARVEGZETTSEGID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT DISTINCT
     STUFF((
       SELECT DISTINCT ', ' + subv.C_EGYEB
       FROM T_VEGZETTSEG subv
       WHERE subv.C_TANARID = v.C_TANARID
         AND subv.C_VEGZETTSEGTIPUSID = v.C_VEGZETTSEGTIPUSID
         AND C_EGYEB IS NOT NULL
     FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS C_EGYEB
    ,ISNULL(new.ID, 7229) -- Na
    ,v.C_TANARID
    ,MAX(v.C_INTEZMENYID)
    ,MAX(v.C_TANEVID)
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,NULL
    ,NULL
    ,NULL
    ,NULL
  FROM T_VEGZETTSEG v
    INNER JOIN T_DICTIONARYITEMBASE vt ON v.C_INTEZMENYID = vt.C_INTEZMENYID AND v.C_TANEVID = vt.C_TANEVID AND v.C_VEGZETTSEGTIPUSID = vt.ID
    INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID = v.ID
    INNER JOIN T_KEPESITES k ON k.ID = kv.C_KEPESITESID
    INNER JOIN T_DICTIONARYITEMBASE szt ON szt.ID = k.C_KEPESITESTIPUSID AND szt.C_INTEZMENYID = k.C_INTEZMENYID AND szt.C_TANEVID = k.C_TANEVID
    LEFT JOIN (SELECT DISTINCT ID, C_NAME, C_TYPE FROM T_DICTIONARYITEMBASE d WHERE C_TYPE = 'KKVEGZETTSEGTIPUS') new ON new.C_NAME = vt.C_NAME 
  WHERE szt.ID NOT IN (7092, 7089)
    AND k.TOROLT = 'F'
    AND v.TOROLT = 'F'
  GROUP BY v.C_TANARID, ISNULL(new.ID, 7229), v.C_VEGZETTSEGTIPUSID
  
  CREATE TABLE #KK (old int, new int)
  INSERT INTO #KK (old, new) 
  VALUES
  (6978, 7137),
  (6984, 7137),
  (6994, 7137),
  (6983, 7142),
  (7023, 7166),
  (7027, 7170),
  (7038, 7181),
  (7015, 7158),
  (6985, 7158),
  (6995, 7158),
  (7012, 7155),
  (7088, 7226),
  (6992, 7143),
  (7026, 7169),
  (7093, 7161),
  (7018, 7161),
  (7091, 7228),
  (7017, 7160),
  (7021, 7164),
  (7025, 7168),
  (6986, 7139),
  (6980, 7139),
  (6996, 7139),
  (7019, 7162),
  (7030, 7173),
  (7022, 7165),
  (6993, 7144),
  (6977, 7136),
  (7090, 7227),
  (7024, 7167),
  (6987, 7157),
  (7014, 7157),
  (6997, 7157),
  (6975, 7134),
  (7016, 7159),
  (7029, 7172),
  (7084, 7222),
  (7041, 7133),
  (7042, 7133),
  (7040, 7133),
  (6974, 7133),
  (7039, 7133),
  (7043, 7133),
  (7085, 7223),
  (6988, 7138),
  (6998, 7138),
  (6979, 7138),
  (6976, 7135),
  (6989, 7140),
  (6999, 7140),
  (6981, 7140),
  (6990, 7156),
  (7000, 7156),
  (7013, 7156),
  (7044, 7182),
  (7032, 7175),
  (6991, 7141),
  (7001, 7141),
  (6982, 7141),
  (7034, 7177),
  (7035, 7178),
  (7045, 7183),
  (7061, 7199),
  (7050, 7188),
  (7063, 7201),
  (7053, 7191),
  (7055, 7193),
  (7049, 7187),
  (7051, 7189),
  (7059, 7197),
  (7054, 7192),
  (7002, 7145),
  (7003, 7146),
  (7086, 7224),
  (7057, 7195),
  (7062, 7200),
  (7048, 7186),
  (7056, 7194),
  (7047, 7185),
  (7004, 7147),
  (7046, 7184),
  (7058, 7196),
  (7052, 7190),
  (7060, 7198),
  (7005, 7148),
  (7036, 7179),
  (7064, 7202),
  (7080, 7218),
  (7069, 7207),
  (7082, 7220),
  (7072, 7210),
  (7074, 7212),
  (7068, 7206),
  (7070, 7208),
  (7078, 7216),
  (7073, 7211),
  (7006, 7149),
  (7087, 7225),
  (7007, 7150),
  (7076, 7214),
  (7081, 7219),
  (7067, 7205),
  (7075, 7213),
  (7066, 7204),
  (7008, 7151),
  (7065, 7203),
  (7077, 7215),
  (7071, 7209),
  (7079, 7217),
  (7009, 7152),
  (7033, 7176),
  (7010, 7153),
  (7083, 7221),
  (7020, 7163),
  (7031, 7174),
  (7011, 7154),
  (7028, 7171),
  (7037, 7180)


  INSERT INTO T_KKTANTARGYKATEGORIA (
     C_KKSZAKTIPUSID
    ,C_KKTANARVEGZETTSEGID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT
     kk.new
    ,kktv.ID
    ,kktv.C_INTEZMENYID
    ,kktv.C_TANEVID
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,NULL
    ,NULL
    ,NULL
    ,NULL  
  FROM (
    SELECT DISTINCT
       ISNULL(new.ID, 7229) AS C_KKTANARVEGZETTSEGID -- Na
      ,v.C_TANARID
      ,szt.ID AS C_SZAKTIPUSID
    FROM T_VEGZETTSEG v
      INNER JOIN T_DICTIONARYITEMBASE vt ON v.C_INTEZMENYID = vt.C_INTEZMENYID AND v.C_TANEVID = vt.C_TANEVID AND v.C_VEGZETTSEGTIPUSID = vt.ID
      INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID = v.ID
      INNER JOIN T_KEPESITES k ON k.ID = kv.C_KEPESITESID
      INNER JOIN T_DICTIONARYITEMBASE szt ON szt.ID = k.C_KEPESITESTIPUSID AND szt.C_INTEZMENYID = k.C_INTEZMENYID AND szt.C_TANEVID = k.C_TANEVID
      LEFT JOIN (SELECT DISTINCT ID, C_NAME, C_TYPE FROM T_DICTIONARYITEMBASE d WHERE C_TYPE = 'KKVEGZETTSEGTIPUS') new ON new.C_NAME = vt.C_NAME 
    WHERE szt.ID NOT IN(7092, 7089)
      AND k.TOROLT = 'F'
      AND v.TOROLT = 'F'
  ) x
  INNER JOIN T_KKTANARVEGZETTSEG kktv ON x.C_KKTANARVEGZETTSEGID = kktv.C_KKTANARVEGZETTSEGID AND x.C_TANARID = kktv.C_ALKALMAZOTTID
  INNER JOIN #KK kk ON kk.old = x.C_SZAKTIPUSID
END
GO
