192 lines
5.1 KiB
Transact-SQL
192 lines
5.1 KiB
Transact-SQL
--Migration
|
|
DROP VIEW IF EXISTS V_ORARENDIORA
|
|
GO
|
|
|
|
CREATE VIEW V_ORARENDIORA AS
|
|
SELECT
|
|
oo.ID
|
|
,oo.C_ORARENDIORAGROUPID
|
|
,oo.TOROLT
|
|
,oo.C_TANTARGYID
|
|
,tgy.C_NEV AS C_TANTARGYNEV
|
|
,oo.C_OSZTALYCSOPORTID
|
|
,ocs.C_NEV AS C_OSZTALYCSOPORTNEV
|
|
,oo.C_TANARID
|
|
,pg.C_NYOMTATASINEV AS C_TANARNEV
|
|
,oo.C_ORAERVENYESSEGKEZDETE
|
|
,oo.C_ORAERVENYESSEGVEGE
|
|
,oo.C_ORASZAM
|
|
,oo.C_ORAKEZDETE
|
|
,oo.C_ORAVEGE
|
|
,oo.C_CSENGETESIRENDID
|
|
,oo.C_CSENGETESIRENDORAID
|
|
,oo.C_HETIREND
|
|
,hrt.C_NAME AS C_HETIRENDNEV
|
|
,oo.C_HETNAPJA
|
|
,hnt.C_NAME AS C_HETNAPJANEV
|
|
,oo.C_TEREMID
|
|
,tr.C_NEV AS C_TEREMNEV
|
|
,oo.C_TANEVID
|
|
,oo.C_INTEZMENYID
|
|
,oo.LASTCHANGED
|
|
,oo.CREATED
|
|
,oo.MODIFIER
|
|
,oo.CREATOR
|
|
FROM T_ORARENDIORA oo WITH(NOLOCK)
|
|
LEFT JOIN T_OSZTALYCSOPORT ocs WITH(NOLOCK) ON ocs.ID = oo.C_OSZTALYCSOPORTID
|
|
LEFT JOIN T_TANTARGY tgy WITH(NOLOCK) ON tgy.ID = oo.C_TANTARGYID
|
|
LEFT JOIN T_FELHASZNALO pg WITH(NOLOCK) ON pg.ID = oo.C_TANARID
|
|
LEFT JOIN T_DICTIONARYITEMBASE hrt WITH(NOLOCK) ON hrt.ID = oo.C_HETIREND AND hrt.C_TANEVID = oo.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE hnt WITH(NOLOCK) ON hnt.ID = oo.C_HETNAPJA AND hnt.C_TANEVID = oo.C_TANEVID
|
|
LEFT JOIN T_TEREM tr WITH(NOLOCK) ON tr.ID = oo.C_TEREMID
|
|
GO
|
|
|
|
DROP VIEW IF EXISTS V_TANITASIORA
|
|
GO
|
|
|
|
CREATE VIEW V_TANITASIORA AS
|
|
SELECT
|
|
tn.ID
|
|
,tn.C_ORARENDIORAGROUPID
|
|
,tn.C_DATUM
|
|
,tn.TOROLT
|
|
,tn.C_TANTARGYID
|
|
,tgy.C_NEV AS C_TANTARGYNEV
|
|
,tn.C_OSZTALYCSOPORTID
|
|
,ocs.C_NEV AS C_OSZTALYCSOPORTNEV
|
|
,tn.C_TANARID
|
|
,pg.C_NYOMTATASINEV AS C_TANARNEV
|
|
,tn.C_HELYETTESITOTANARID
|
|
,pgh.C_NYOMTATASINEV AS C_HELYETTESITOTANARNEV
|
|
,tn.C_ORASZAM
|
|
,tn.C_TEMA
|
|
,tn.C_ORAKEZDETE
|
|
,tn.C_ORAVEGE
|
|
,tn.C_CSENGETESIRENDID
|
|
,tn.C_CSENGETESIRENDORAID
|
|
,tn.C_HETNAPJA
|
|
,hnt.C_NAME AS C_HETNAPJANEV
|
|
,tn.C_TEREMID
|
|
,tr.C_NEV AS C_TEREMNEV
|
|
,tn.C_TANEVID
|
|
,tn.C_INTEZMENYID
|
|
,tn.LASTCHANGED
|
|
,tn.CREATED
|
|
,tn.MODIFIER
|
|
,tn.CREATOR
|
|
FROM T_TANITASIORA tn WITH(NOLOCK)
|
|
LEFT JOIN T_OSZTALYCSOPORT ocs WITH(NOLOCK) ON ocs.ID = tn.C_OSZTALYCSOPORTID
|
|
LEFT JOIN T_TANTARGY tgy WITH(NOLOCK) ON tgy.ID = tn.C_TANTARGYID
|
|
LEFT JOIN T_FELHASZNALO pg WITH(NOLOCK) ON pg.ID = tn.C_TANARID
|
|
LEFT JOIN T_FELHASZNALO pgh WITH(NOLOCK) ON pgh.ID = tn.C_HELYETTESITOTANARID
|
|
LEFT JOIN T_DICTIONARYITEMBASE hnt WITH(NOLOCK) ON hnt.ID = tn.C_HETNAPJA AND hnt.C_TANEVID = tn.C_TANEVID
|
|
LEFT JOIN T_TEREM tr WITH(NOLOCK) ON tr.ID = tn.C_TEREMID
|
|
GO
|
|
|
|
DROP VIEW IF EXISTS V_TANULOCSOPORT
|
|
GO
|
|
|
|
CREATE VIEW V_TANULOCSOPORT AS
|
|
SELECT
|
|
tcs.ID
|
|
,tcs.TOROLT
|
|
,tcs.C_TANULOID
|
|
,dk.C_NYOMTATASINEV AS C_TANULONEV
|
|
,tcs.C_OSZTALYCSOPORTID
|
|
,ocs.C_NEV AS C_OSZTALYCSOPORTNEV
|
|
,IIF(tta.ID IS NULL, 'CSOP', 'OSZT') AS C_OSZTALYCSOPORTTIPUS
|
|
,ocs.C_FELADATKATEGORIAID
|
|
,fk.C_NAME AS C_FELADATKATEGORIANEV
|
|
,tcs.C_BELEPESDATUM
|
|
,tcs.C_KILEPESDATUM
|
|
,tcs.C_ZARADEK
|
|
,tcs.C_VEGZARADEK
|
|
,tcs.C_BEJEGYZESIDOPONTJA
|
|
,tcs.C_KILEPESROGZITESDATUM
|
|
,tcs.C_INTEZMENYID
|
|
,tcs.C_TANEVID
|
|
,tcs.LASTCHANGED
|
|
,tcs.CREATED
|
|
,tcs.MODIFIER
|
|
,tcs.CREATOR
|
|
FROM T_TANULOCSOPORT tcs WITH(NOLOCK)
|
|
LEFT JOIN T_OSZTALYCSOPORT ocs WITH(NOLOCK) ON ocs.ID = tcs.C_OSZTALYCSOPORTID
|
|
LEFT JOIN T_FELHASZNALO dk WITH(NOLOCK) ON dk.ID = tcs.C_TANULOID
|
|
LEFT JOIN T_TANULOTANUGYIADATOK tta WITH(NOLOCK) ON tta.C_TANULOCSOPORTID = tcs.ID
|
|
LEFT JOIN T_DICTIONARYITEMBASE fk WITH(NOLOCK) ON fk.ID = ocs.C_FELADATKATEGORIAID AND fk.C_TANEVID = ocs.C_TANEVID
|
|
GO
|
|
|
|
DROP VIEW IF EXISTS V_ENTITYHISTORY
|
|
GO
|
|
|
|
CREATE VIEW V_ENTITYHISTORY AS
|
|
SELECT
|
|
e.ID
|
|
,e.C_ENTITYID
|
|
,e.C_ENTITYNAME
|
|
,e.C_ALTERATIONDATE
|
|
,e.C_REASON
|
|
,e.C_FELHASZNALOID
|
|
,e.C_INTEZMENYID
|
|
,e.C_TANEVID
|
|
,ea.C_ORIGINALVALUE
|
|
,ea.C_CURRENTVALUE
|
|
,ea.C_PROPERTYNAME
|
|
FROM T_ENTITYHISTORY e WITH(NOLOCK)
|
|
LEFT JOIN T_ENTITYATTRIBUTEHISTORY ea WITH(NOLOCK) ON e.ID = ea.C_ENTITYHISTORYID
|
|
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
|
|
,tv.C_NEV AS C_TANEVNEV
|
|
,CASE WHEN tv.C_AKTIV = 'T' THEN 'Akt' WHEN tv.C_KOVETKEZO = 'T' THEN 'Köv' ELSE 'Régi' END AS C_TANEVTIPUS
|
|
,tv.C_AKTIVALVA
|
|
,i.C_FENNTARTOAZONOSITO
|
|
,ia.C_NEV
|
|
,ia.C_IGAZGATONEVE
|
|
,ia.C_ISSZAKKEPZO
|
|
,ia.C_ISDKTAKTIV
|
|
,ia.C_OMKOD
|
|
,i.C_GUID
|
|
FROM T_INTEZMENY i WITH(NOLOCK)
|
|
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 V_TANEVRENDJE
|
|
GO
|
|
|
|
CREATE VIEW V_TANEVRENDJE AS
|
|
SELECT
|
|
tr.ID
|
|
,tr.TOROLT
|
|
,tr.C_DATUM
|
|
,tr.C_HETIREND
|
|
,hr.C_NAME AS C_HETIRENDNEV
|
|
,tr.C_HETNAPJA
|
|
,hn.C_NAME AS C_HETNAPJANEV
|
|
,tr.C_NAPTIPUSA
|
|
,nt.C_NAME AS C_NAPTIPUSANEV
|
|
,tr.C_ORARENDINAP
|
|
,tr.C_OSSZESCSOPORTRAVONATKOZIK
|
|
,tr.C_EGYEDINAP
|
|
,tr.C_MEGJEGYZES
|
|
,tr.C_INTEZMENYID
|
|
,tr.C_TANEVID
|
|
,tr.LASTCHANGED
|
|
,tr.CREATED
|
|
,tr.MODIFIER
|
|
,tr.CREATOR
|
|
FROM T_TANEVRENDJE tr WITH(NOLOCK)
|
|
LEFT JOIN T_DICTIONARYITEMBASE hr WITH(NOLOCK) ON hr.ID = tr.C_HETIREND AND hr.C_TANEVID = tr.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE hn WITH(NOLOCK) ON hn.ID = tr.C_HETNAPJA AND hn.C_TANEVID = tr.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE nt WITH(NOLOCK) ON nt.ID = tr.C_NAPTIPUSA AND nt.C_TANEVID = tr.C_TANEVID
|
|
GO
|