--Migration

DROP VIEW IF EXISTS V_INTEZMENYCONFIG
GO

CREATE VIEW V_INTEZMENYCONFIG AS
SELECT 
   i.ID AS C_INTEZMENYID
  ,i.C_AZONOSITO
  ,c.ID AS C_CONFIGID
  ,c.C_MODUL
  ,c.C_CONFIG
  ,ic.C_ERTEK
FROM T_INTEZMENY i
  LEFT JOIN T_INTEZMENYCONFIG ic ON ic.C_INTEZMENYID = i.ID AND ic.TOROLT = 'F'
  LEFT JOIN T_CONFIGTIPUS c ON c.ID = ic.C_CONFIGTIPUSID AND c.TOROLT = 'F'
GO


DROP VIEW IF EXISTS V_INTEZMENYADATOK
GO

CREATE VIEW V_INTEZMENYADATOK AS
SELECT
   i.ID AS C_INTEZMENYID
  ,tv.ID AS C_TANEVID
  ,i.C_AZONOSITO
  ,i.C_ISARCHIV
  ,tv.C_NEV AS C_TANEVNEV
  ,CASE 
    WHEN tv.C_AKTIV = 'T' THEN 'Akt' 
    WHEN tv.C_KOVETKEZO = 'T' THEN 'Köv' 
    WHEN tvAkt.C_SORSZAM < tv.C_SORSZAM THEN 'Új'
    ELSE 'Régi' END AS C_TANEVTIPUS 
  ,tv.C_AKTIVALVA 
  ,i.C_FENNTARTOAZONOSITO
  ,ia.C_NEV
  ,ia.C_IGAZGATONEVE
  ,ia.C_OMKOD
  ,i.C_GUID
  ,tv.C_SORSZAM
  ,tv.C_ISKIVALASZTHATO
  ,ia.C_KRETAESL
  ,ia.C_KRETAEUGYINTEZES
  ,ia.C_ISCSAKLEPKEZELO
  ,ia.C_ISBAISTATUSZAKTIV
  ,ia.C_ISSZIRINTEZMENY
  ,ia.C_ISSZAKKEPZO
  ,ia.C_ISDKTAKTIV
  ,ia.C_ISSZAKKEPZOJUTTATAS
  ,ia.C_ISAKTIVPROJEKTJELENTKEZES
FROM T_INTEZMENY i WITH(NOLOCK)
  LEFT JOIN T_TANEV tvAkt ON tvAkt.C_INTEZMENYID = i.ID AND tvAkt.C_AKTIV = 'T'
  LEFT JOIN T_TANEV tv WITH(NOLOCK) ON i.ID = tv.C_INTEZMENYID
  LEFT JOIN T_INTEZMENYADATOK ia  WITH(NOLOCK) ON ia.C_TANEVID = tv.ID
WHERE tv.TOROLT = 'F'
GO  


DROP VIEW IF EXISTS dbo.V_TANULOMULASZTAS
GO

CREATE VIEW dbo.V_TANULOMULASZTAS AS
SELECT 
	 mu.ID
	,mu.TOROLT
	,mu.C_IGAZOLT
  ,tn.C_DATUM
  ,tgy.C_NEV AS C_TANTARGYNEV
  ,ocs.C_NEV AS C_OSZTALYCSOPORTNEV
	,mu.C_KESESPERCBEN
	,mu.C_TIPUS
	,d1.C_NAME as C_TIPUSNEV
	,mu.C_IGAZOLASTIPUSA
	,d2.C_NAME as C_IGAZOLASTIPUSNEV
	,mu.C_ISERTESITESELKULDVE
	,mu.C_INTEZMENYID
	,mu.C_TANEVID
	,mu.C_ORATANULOIID
	,fh.C_NYOMTATASINEV AS C_ORATANULONEV
	,mu.C_TANITASIORAKID
	,mu.SERIAL
	,mu.LASTCHANGED
	,mu.CREATED
	,mu.MODIFIER
	,mu.CREATOR
	,mu.ELOZOTANEVIREKORDID
FROM T_TANULOMULASZTAS mu  WITH (NOLOCK)
  LEFT JOIN T_DICTIONARYITEMBASE d1  WITH (NOLOCK) on d1.ID = mu.C_TIPUS and d1.C_TANEVID = mu.C_TANEVID and d1.C_TYPE = 'MulasztasTipus' and d1.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d2  WITH (NOLOCK) on d2.ID = mu.C_IGAZOLASTIPUSA and d2.C_TANEVID = mu.C_TANEVID and d2.C_TYPE = 'IgazolasTipus' and d2.TOROLT = 'F'
  LEFT JOIN T_FELHASZNALO fh  WITH (NOLOCK) on fh.ID = mu.C_ORATANULOIID
  LEFT JOIN T_TANITASIORA tn  WITH (NOLOCK) ON tn.ID = mu.C_TANITASIORAKID
  LEFT JOIN T_TANTARGY tgy  WITH (NOLOCK) ON tgy.ID = tn.C_TANTARGYID
  LEFT JOIN T_OSZTALYCSOPORT ocs  WITH (NOLOCK) ON ocs.ID = tn.C_OSZTALYCSOPORTID
GO

DROP VIEW IF EXISTS dbo.V_TANULOERTEKELES
GO

CREATE VIEW dbo.V_TANULOERTEKELES
AS
SELECT
	er.ID
	,er.TOROLT
	,er.C_DATUM
	,er.C_TIPUSID
	,d1.C_NAME as C_TIPUSNEV
	,er.C_ROGZITESDATUM
	,er.C_TANTARGYID
	,tgy.C_NEV as C_TANTARGYNEV
	,er.C_TANULOID
	,dk.C_NYOMTATASINEV as C_TANULONEV
	,er.C_OSZTALYCSOPORTID
	,ocs.C_NEV as C_OSZTALYCSOPORTNEV
	,er.C_ERTEKELESOSZTALYZATID
	,d2.C_NAME as C_ERTEKELESOSZTALYZATNEV
	,er.C_ERTEKELOID
	,fh.C_NYOMTATASINEV as C_ERTEKELONEV
	,er.C_TANORAID
	,er.C_ROGZITESKORITANULOOSZTALYID
	,er.C_INTEZMENYID
	,er.C_TANEVID
	,er.C_ERTEKELESSZOVEG
	,er.C_ERTEKELESSZOVEGROVIDNEV
	,er.C_ERTEKELESMODID
	,d3.C_NAME as C_ERTEKELESMODNEV
	,er.C_ERTEKELESTEMA
	,er.C_ERTEKELESSZAZALEK
	,er.C_ISMAGATARTASSZORGALOM
	,er.C_MAGATARTASERTEKID
	,d4.C_NAME as C_MAGATARTASERTEKNEV
	,er.C_MAGATARTASOSZTALYZATID
    ,d7.C_NAME as C_MAGATARTASOSZTALYZATNEV
	,er.C_MAGATARTASSZOVEG
	,er.C_MAGATARTASSZOVEGROVIDNEV
	,er.C_SZORGALOMERTEKID
	,d5.C_NAME as C_SZORGALOMERTEKNEV
	,er.C_SZORGALOMOSZTALYZATID
	,d6.C_NAME as C_SZORGALOMOSZTALYZATNEV
	,er.C_SZORGALOMSZOVEG
	,er.C_SZORGALOMSZOVEGROVIDNEV
FROM T_TANULOERTEKELES er WITH (NOLOCK)
  LEFT JOIN T_DICTIONARYITEMBASE d1 WITH (NOLOCK) on d1.ID = er.C_TIPUSID and d1.C_TANEVID = er.C_TANEVID and d1.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d2 WITH (NOLOCK) on d2.ID = er.C_ERTEKELESOSZTALYZATID and d2.C_TANEVID = er.C_TANEVID and d2.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d3 WITH (NOLOCK) on d3.ID = er.C_ERTEKELESMODID and d3.C_TANEVID = er.C_TANEVID and d3.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d4 WITH (NOLOCK) on d4.ID = er.C_MAGATARTASERTEKID and d4.C_TANEVID = er.C_TANEVID and d4.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d5 WITH (NOLOCK) on d5.ID = er.C_SZORGALOMERTEKID and d5.C_TANEVID = er.C_TANEVID and d5.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d6 WITH (NOLOCK) on d6.ID = er.C_SZORGALOMOSZTALYZATID and d6.C_TANEVID = er.C_TANEVID and d6.TOROLT = 'F'
  LEFT JOIN T_DICTIONARYITEMBASE d7 WITH (NOLOCK) on d7.ID = er.C_MAGATARTASOSZTALYZATID and d7.C_TANEVID = er.C_TANEVID and d7.TOROLT = 'F'
  LEFT JOIN T_TANTARGY tgy WITH (NOLOCK) on tgy.ID = er.C_TANTARGYID
  LEFT JOIN T_FELHASZNALO dk WITH (NOLOCK) on dk.ID = er.C_TANULOID
  LEFT JOIN T_OSZTALYCSOPORT ocs WITH (NOLOCK) on ocs.ID = er.C_OSZTALYCSOPORTID
  LEFT JOIN T_FELHASZNALO fh WITH (NOLOCK) on fh.ID = er.C_ERTEKELOID
  LEFT JOIN T_TANITASIORA tn WITH (NOLOCK) on tn.ID = er.C_TANORAID
GO