317 lines
8.7 KiB
Transact-SQL
317 lines
8.7 KiB
Transact-SQL
-- 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
|
|
|