-- random szám generálása DROP VIEW IF EXISTS dev.vGetRand; GO CREATE VIEW dev.vGetRand AS SELECT RAND(CHECKSUM(NEWID())) AS RandomNumber GO 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_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_ENTITYHISTORYBLOB GO CREATE VIEW V_ENTITYHISTORYBLOB 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 ,ev.C_ORIGINALVALUE ,ev.C_CURRENTVALUE ,ev.C_PROPERTYNAME ,COALESCE(f.C_NYOMTATASINEV, u.USERNAME) AS C_FELHASZNALONEV ,e.HOSTNAME AS C_HOSTNAME FROM T_ENTITYHISTORY e WITH(NOLOCK) INNER JOIN T_ENTITYBLOBVALUES ev WITH(NOLOCK) ON ev.C_ENTITYHISTORYID = e.ID LEFT JOIN T_FELHASZNALO f WITH(NOLOCK) ON f.ID = e.C_FELHASZNALOID LEFT JOIN auditlog.Users u WITH(NOLOCK) ON u.USERID = e.C_FELHASZNALOID GO DROP VIEW IF EXISTS V_ENTITYHISTORY GO CREATE VIEW V_ENTITYHISTORY AS -- T_ENTITYBLOBVALUES SELECT e.ID ,e.C_ENTITYID ,e.C_ENTITYNAME ,e.C_ALTERATIONDATE ,e.C_REASON ,e.C_FELHASZNALOID ,e.C_INTEZMENYID ,e.C_TANEVID ,ev.C_ORIGINALVALUE ,ev.C_CURRENTVALUE ,ev.C_PROPERTYNAME ,COALESCE(f.C_NYOMTATASINEV, u.USERNAME) AS C_FELHASZNALONEV ,e.HOSTNAME AS C_HOSTNAME FROM T_ENTITYHISTORY e WITH(NOLOCK) INNER JOIN T_ENTITYBLOBVALUES ev WITH(NOLOCK) ON ev.C_ENTITYHISTORYID = e.ID LEFT JOIN T_FELHASZNALO f WITH(NOLOCK) ON f.ID = e.C_FELHASZNALOID LEFT JOIN auditlog.Users u WITH(NOLOCK) ON u.USERID = e.C_FELHASZNALOID UNION ALL -- T_ENTITYATTRIBUTEHISTORY 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 ,COALESCE(f.C_NYOMTATASINEV, u.USERNAME) AS C_FELHASZNALONEV ,e.HOSTNAME AS C_HOSTNAME FROM dbo.T_ENTITYHISTORY e WITH(NOLOCK) LEFT JOIN dbo.T_ENTITYATTRIBUTEHISTORY ea WITH(NOLOCK) ON ea.C_ENTITYHISTORYID = e.ID LEFT JOIN dbo.T_FELHASZNALO f WITH(NOLOCK) ON f.ID = e.C_FELHASZNALOID LEFT JOIN auditlog.Users u WITH(NOLOCK) ON u.USERID = e.C_FELHASZNALOID WHERE EXISTS(SELECT * FROM dbo.T_ENTITYATTRIBUTEHISTORY h WITH(NOLOCK) WHERE h.C_ENTITYHISTORYID = e.ID) -- performancia miatt 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 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 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 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