450 lines
13 KiB
Transact-SQL
450 lines
13 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetTanoranKivuliNaplo
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanoranKivuliNaplo
|
|
@tanevId int
|
|
,@intezmenyId int
|
|
,@csoportId int
|
|
,@iskolaErdekuSzamit bit
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @feladatKategoriaId int = (SELECT C_FELADATKATEGORIAID FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @csoportId AND TOROLT = 'F')
|
|
|
|
DECLARE
|
|
@datum date = GETDATE()
|
|
,@letszam int
|
|
|
|
CREATE TABLE #students (
|
|
ID int
|
|
,Osztaly nvarchar (max)
|
|
,OsztalyId int
|
|
,BelepesDatum datetime
|
|
)
|
|
|
|
CREATE TABLE #OraSorszam (
|
|
TanitasioraId int
|
|
,OraSorszam nvarchar (100)
|
|
)
|
|
|
|
CREATE TABLE #TanuloCimTable (
|
|
FelhasznaloId int
|
|
,Cim nvarchar (max)
|
|
,Cimtipusa nvarchar (max)
|
|
)
|
|
|
|
CREATE TABLE #GondViseloCimTable (
|
|
GondviseloId int
|
|
,Cim nvarchar (max)
|
|
,Cimtipusa nvarchar (max)
|
|
)
|
|
|
|
CREATE TABLE #EmailCimTableTemp (
|
|
FelhasznaloId int
|
|
,EmailCim nvarchar (max)
|
|
,GondviseloId int
|
|
)
|
|
|
|
CREATE TABLE #EmailCimTanuloTable(
|
|
FelhasznaloId int
|
|
,EmailCim nvarchar (max)
|
|
)
|
|
|
|
CREATE TABLE #EmailCimGondviseloTable (
|
|
GondviseloId int
|
|
,EmailCim nvarchar (max)
|
|
)
|
|
|
|
CREATE TABLE #TelefonTableTemp(
|
|
FelhasznaloId int
|
|
,Telefon nvarchar (max)
|
|
,GondviseloId int
|
|
)
|
|
|
|
CREATE TABLE #TanuloTelefonTable(
|
|
FelhasznaloId int
|
|
,Telefon nvarchar (max)
|
|
)
|
|
|
|
CREATE TABLE #GondviseloTelefonTable (
|
|
GondviseloId int
|
|
,Telefon nvarchar (max)
|
|
)
|
|
|
|
SELECT
|
|
@letszam = COUNT(tcs.C_TANULOID)
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
WHERE tcs.C_OSZTALYCSOPORTID = @csoportId
|
|
AND tcs.TOROLT='F'
|
|
|
|
--Fejlecoldal (Fejléc)
|
|
SELECT
|
|
ia.C_NEV AS IntezmenyNev
|
|
,dbo.fnGetDokumentumIntezmenyCime(@tanevId) AS IntezmenyCim
|
|
,ia.C_OMKOD AS OMKOD
|
|
,ia.C_IGAZGATONEVE AS IntezmenyVezeto
|
|
,ocs.C_NEV AS Csoport
|
|
,ocs.ID AS CsoportId
|
|
,ISNULL(f.C_NYOMTATASINEV,'-') AS CsoportVezeto
|
|
,ISNULL(cs.C_CSOPORTNAPLOLEIRAS,'-') AS CsoportNaploLeiras
|
|
,dbo.fnGetDokumentumDatumFormatum(cs.C_CSOPORTNAPLOMEGNYITASA) AS Megnyitas
|
|
,ISNULL(dbo.fnGetDokumentumDatumFormatum(cs.C_CSOPORTNAPLOZARASA),'-') AS Lezaras
|
|
,t.C_NEV AS Tanev
|
|
,cs.C_CSOPORTNAPLOLEIRAS AS Megjegyzes
|
|
,f.ID AS CsoportVezetoId
|
|
,ISNULL(ocs.C_EVFOLYAMTIPUSA, 1296) AS EvfolyamTipusa -- 1296 = Na
|
|
,ocs.C_FELADATELLATASIHELYID AS FeladatEllatasiHelyId
|
|
,ocs.C_KERESZTFELEVES AS OJCSJKeresztfeleves
|
|
,ocs.C_VEGZOSEVFOLYAM AS CSJVegzosEvfolyamu
|
|
,cs.C_TIPUSA AS CsoportTipusa
|
|
FROM T_INTEZMENY_OSSZES i
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON ia.C_INTEZMENYID = i.ID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.C_INTEZMENYID = i.ID
|
|
INNER JOIN T_CSOPORT_OSSZES cs ON cs.ID = ocs.ID
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.ID = cs.C_CSOPORTVEZETOID
|
|
INNER JOIN T_TANEV_OSSZES t ON t.ID = ia.C_TANEVID
|
|
WHERE ia.C_TANEVID = @tanevid
|
|
AND ocs.ID = @csoportId
|
|
|
|
INSERT INTO #students (
|
|
ID
|
|
,Osztaly
|
|
,OsztalyId
|
|
,BelepesDatum
|
|
)
|
|
SELECT
|
|
tcs.C_TANULOID AS TanuloId
|
|
,osztaly.OsztalyNev AS Osztaly
|
|
,osztaly.ID AS OsztalyId
|
|
,CAST(osztaly.BelepesDatuma as DATE) as BelepesDatum
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
CROSS APPLY fnGetTanuloOsztaly(tcs.C_TANULOID, tcs.C_KILEPESDATUM, @feladatKategoriaId, 1, DEFAULT) osztaly
|
|
INNER JOIN T_FELHASZNALO_OSSZES tanuloNeve on tanuloNeve.ID = tcs.C_TANULOID
|
|
WHERE tcs.C_TANEVID = @tanevId
|
|
AND tcs.C_OSZTALYCSOPORTID = @csoportId
|
|
|
|
--Osztaly (Csoport tanulóinak osztályai)
|
|
SELECT
|
|
COUNT(Id) AS Tanulo
|
|
,Osztaly
|
|
,OsztalyId
|
|
FROM #students
|
|
GROUP BY
|
|
Osztaly
|
|
,OsztalyId
|
|
|
|
--Napló
|
|
INSERT INTO #OraSorszam (
|
|
TanitasioraId
|
|
,OraSorszam
|
|
)
|
|
EXEC uspGetOraSorszamByOsztaly
|
|
@osztalyCsoportId = @csoportId
|
|
,@datum = @datum
|
|
,@intezmenyId = @intezmenyId
|
|
,@tanevId = @tanevId
|
|
|
|
SELECT
|
|
dbo.fnGetDokumentumDatumFormatum(tao.C_ORAKEZDETE) + CONVERT(varchar(5), tao.C_ORAKEZDETE, 108) + '-' + CONVERT(varchar(5), tao.C_ORAVEGE, 108) AS Datum
|
|
,IIF(tantargy.C_NEV <> C_NEVNYOMTATVANYBAN AND C_NEVNYOMTATVANYBAN IS NOT NULL, C_NEVNYOMTATVANYBAN
|
|
+ ' (' + tantargy.C_NEV + ')', tantargy.C_NEV) AS TantargyNev
|
|
,tao.C_TANTARGYID AS TantargyId
|
|
,tao.C_MEGTARTOTT AS Megtartott
|
|
,IIF(C_MEGTARTOTT = 'F', 'Elmaradt', tao.C_TEMA) AS Tema
|
|
,@letszam - ISNULL(hianyzo.Hianyzok, 0) AS Letszam
|
|
,IIF(C_MEGTARTOTT = 'F', '-', f.C_NYOMTATASINEV) AS Pedagogus
|
|
,Terem.C_NEV AS TeremNev
|
|
,ISNULL(Oraszam.OraSorszam + '.', '-') AS OraSorszam
|
|
,tao.C_ORAKEZDETE AS OraKezdete
|
|
INTO #megtartottOrak
|
|
FROM T_TANITASIORA_OSSZES tao
|
|
LEFT JOIN (
|
|
SELECT
|
|
tm.C_TANITASIORAKID AS Tanitasiora
|
|
,COUNT(tm.C_ORATANULOIID) AS Hianyzok
|
|
FROM T_TANULOMULASZTAS_OSSZES tm
|
|
WHERE tm.torolt = 'F'
|
|
AND C_TIPUS IN (1499, 1500)
|
|
GROUP BY tm.C_TANITASIORAKID
|
|
) hianyzo ON hianyzo.Tanitasiora = tao.ID
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.id = tao.C_ORATULAJDONOSID
|
|
INNER JOIN T_TANTARGY_OSSZES tantargy ON tantargy.Id = tao.C_TANTARGYID
|
|
LEFT JOIN T_TEREM_OSSZES Terem ON Terem.Id = tao.C_TEREMID
|
|
LEFT JOIN #OraSorszam Oraszam ON Oraszam.TanitasioraId = tao.Id
|
|
WHERE tao.C_OSZTALYCSOPORTID = @csoportId
|
|
AND tao.TOROLT='F'
|
|
SELECT
|
|
Datum
|
|
,TantargyNev
|
|
,TantargyId
|
|
,Megtartott
|
|
,Tema
|
|
,Letszam
|
|
,Pedagogus
|
|
,TeremNev
|
|
,OraSorszam
|
|
,OraKezdete
|
|
FROM #megtartottOrak
|
|
ORDER BY OraKezdete
|
|
|
|
/*TANULÓ adatai*/
|
|
SELECT DISTINCT
|
|
ta.TanuloId
|
|
,ta.OktAzon
|
|
,ta.TajSzam
|
|
,ta.AnyjaNeve
|
|
,ta.SzulHely
|
|
,ta.SzulIdo
|
|
,ta.SocAdat
|
|
,ta.Kepzes
|
|
,ta.TanuloNeve
|
|
,ta.Allampolgarsaga
|
|
,ta.DiakIgazolvanySzam
|
|
,ta.AktualisOsztaly Osztalya
|
|
,ta.BelepesDatum
|
|
FROM fnGetDokumentumTanulokAdatai(@tanevId, @csoportId) ta
|
|
ORDER BY TanuloNeve
|
|
|
|
/*TANULÓ elérhetőségei */
|
|
INSERT INTO #TanuloCimTable (
|
|
FelhasznaloId
|
|
,Cim
|
|
,Cimtipusa
|
|
)
|
|
SELECT DISTINCT
|
|
TanuloId
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
CAST(Btemp.Cim AS nvarchar (max)) + char(13) + char(10)
|
|
FROM fnGetDokumentumTanuloOrGondviseloCim (0, @tanevId, @csoportid) btemp
|
|
WHERE temp.TanuloId = btemp.TanuloId
|
|
AND temp.CimTipusa = btemp.CimTipusa
|
|
FOR XML PATH(''), TYPE
|
|
).value
|
|
('.', 'NVARCHAR(MAX)'), 1, 0, ''
|
|
)
|
|
,cimTipusa.C_NAME
|
|
FROM fnGetDokumentumTanuloOrGondviseloCim (0, @tanevId, @csoportid) temp
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES cimTipusa ON cimTipusa.Id = temp.CimTipusa
|
|
AND cimTipusa.C_TANEVID = @tanevId
|
|
|
|
INSERT INTO #GondViseloCimTable (
|
|
GondviseloId
|
|
,Cim
|
|
,Cimtipusa
|
|
)
|
|
SELECT DISTINCT
|
|
GondviseloId
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
CAST(Btemp.Cim AS nvarchar (max)) + char(13) + char(10)
|
|
FROM fnGetDokumentumTanuloOrGondviseloCim (1, @tanevId, @csoportid) btemp
|
|
WHERE temp.GondviseloId = btemp.GondviseloId
|
|
AND temp.CimTipusa = btemp.CimTipusa
|
|
FOR XML PATH(''), TYPE
|
|
).value
|
|
('.', 'NVARCHAR(MAX)'), 1 ,0, ''
|
|
)
|
|
,cimTipusa.C_NAME
|
|
FROM fnGetDokumentumTanuloOrGondviseloCim (1, @tanevId, @csoportid) temp
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES cimTipusa ON cimTipusa.Id = temp.CimTipusa
|
|
AND cimTipusa.C_TANEVID = @tanevId
|
|
|
|
INSERT INTO #EmailCimTableTemp (
|
|
FelhasznaloId
|
|
,EmailCim
|
|
,GondviseloId
|
|
)
|
|
SELECT
|
|
TanuloId
|
|
,C_EMAILCIM
|
|
,C_GONDVISELOID
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @csoportid, 'T') tanulo
|
|
INNER JOIN T_EMAIL_OSSZES email ON email.C_FELHASZNALOID = TanuloId
|
|
WHERE email.TOROLT = 'F'
|
|
AND email.C_TANEVID = @tanevId
|
|
AND LEN(C_EMAILCIM) > 0
|
|
|
|
INSERT INTO #EmailCimTanuloTable (
|
|
FelhasznaloId
|
|
,EmailCim
|
|
)
|
|
SELECT DISTINCT
|
|
FelhasznaloId
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
CAST(btemp.EmailCim AS nvarchar (max)) + char(13) + char(10)
|
|
FROM #EmailCimTableTemp btemp
|
|
WHERE temp.FelhasznaloId = btemp.FelhasznaloId
|
|
AND btemp.GondviseloId IS NULL
|
|
FOR XML PATH(''), TYPE
|
|
).value
|
|
('.', 'NVARCHAR(MAX)'), 1 ,0, ''
|
|
) Email
|
|
FROM #EmailCimTableTemp temp
|
|
WHERE GondviseloId IS NULL
|
|
|
|
INSERT INTO #EmailCimGondviseloTable (
|
|
GondviseloId
|
|
,EmailCim
|
|
)
|
|
SELECT DISTINCT
|
|
GondviseloId
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
CAST(btemp.EmailCim AS nvarchar (max)) + char(13) + char(10)
|
|
FROM #EmailCimTableTemp btemp
|
|
WHERE temp.GondviseloId = btemp.GondviseloId
|
|
AND btemp.GondviseloId IS NOT NULL
|
|
FOR XML PATH(''), TYPE
|
|
).value
|
|
('.', 'NVARCHAR(MAX)'), 1 ,0, ''
|
|
) Email
|
|
FROM #EmailCimTableTemp temp
|
|
WHERE GondviseloId IS NOT NULL
|
|
|
|
INSERT INTO #TelefonTableTemp (
|
|
FelhasznaloId
|
|
,Telefon
|
|
,GondviseloId
|
|
)
|
|
SELECT
|
|
TanuloId
|
|
,C_TELEFONSZAM
|
|
,C_GONDVISELOID
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @csoportid, 'T') tanulo
|
|
INNER JOIN T_TELEFON_OSSZES telefon ON telefon.C_FELHASZNALOID = TanuloId
|
|
WHERE
|
|
telefon.TOROLT = 'F'
|
|
AND telefon.C_TANEVID = @tanevId
|
|
AND LEN(telefon.C_TELEFONSZAM) > 0
|
|
|
|
INSERT INTO #TanuloTelefonTable (
|
|
FelhasznaloId
|
|
,Telefon
|
|
)
|
|
SELECT DISTINCT
|
|
FelhasznaloId
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
CAST(btemp.Telefon AS nvarchar (max)) + char(13) + char(10)
|
|
FROM #TelefonTableTemp btemp
|
|
WHERE temp.FelhasznaloId = btemp.FelhasznaloId
|
|
AND GondviseloId IS NULL
|
|
FOR XML PATH(''), TYPE
|
|
).value
|
|
('.', 'NVARCHAR(MAX)'), 1 ,0, ''
|
|
)
|
|
FROM #TelefonTableTemp temp
|
|
WHERE GondviseloId IS NULL
|
|
|
|
INSERT INTO #GondviseloTelefonTable (
|
|
GondviseloId
|
|
,Telefon
|
|
)
|
|
SELECT DISTINCT
|
|
GondviseloId
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
CAST(btemp.Telefon AS nvarchar (max)) + char(13) + char(10)
|
|
FROM #TelefonTableTemp btemp
|
|
WHERE temp.GondviseloId = btemp.GondviseloId
|
|
AND temp.FelhasznaloId = btemp.FelhasznaloId
|
|
AND GondviseloId IS NOT NULL
|
|
FOR XML PATH(''), TYPE
|
|
).value
|
|
('.', 'NVARCHAR(MAX)'), 1 ,0, ''
|
|
)
|
|
FROM #TelefonTableTemp temp
|
|
WHERE GondviseloId IS NOT NULL
|
|
|
|
SELECT
|
|
s.Id AS tanuloid
|
|
,cim.Cimtipusa AS cimtipus
|
|
,cim.Cim
|
|
,email.EmailCim AS email
|
|
,telefon.Telefon AS telefonszam
|
|
FROM #students s
|
|
LEFT JOIN #TanuloCimTable cim ON cim.FelhasznaloId = s.Id
|
|
LEFT JOIN #EmailCimTanuloTable email ON email.FelhasznaloId = s.Id
|
|
LEFT JOIN #TanuloTelefonTable telefon ON telefon.FelhasznaloId = s.Id
|
|
ORDER BY Cimtipusa
|
|
|
|
/*gondviselő elérhetőségei */
|
|
SELECT DISTINCT
|
|
Gondviselok.TanuloId AS TanuloId
|
|
,Gondviselok.GondviseloNev + ' (' + RokonsagiFok.C_NAME + ')' AS C_NEV
|
|
,cim.Cimtipusa AS C_CIMTIPUSA
|
|
,cim.cim AS C_OSSZETETTCIM
|
|
,telefon.telefon AS C_TELEFONSZAM
|
|
,email.emailcim AS C_EMAILCIM
|
|
FROM fnGetDokumentumGondviselok (@csoportId, @tanevId, 'F', 'T') Gondviselok
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES RokonsagiFok ON RokonsagiFok.ID = Gondviselok.RokonsagifokId AND RokonsagiFok.C_TANEVID = @tanevId
|
|
LEFT JOIN #EmailCimGondviseloTable email ON email.GondviseloId = Gondviselok.GondviseloId
|
|
LEFT JOIN #GondviseloTelefonTable telefon ON telefon.GondviseloId = Gondviselok.GondviseloId
|
|
LEFT JOIN #GondViseloCimTable cim ON cim.GondviseloId = Gondviselok.GondviseloId
|
|
ORDER BY cim.Cimtipusa
|
|
|
|
--megtartott órák száma
|
|
SELECT
|
|
COUNT(mo.TantargyId) AS TantargyOraszam
|
|
,mo.Tantargynev AS TantargyNev
|
|
INTO #megtartottOrakSzama
|
|
FROM #megtartottOrak mo
|
|
WHERE mo.Megtartott = 'T'
|
|
GROUP BY
|
|
mo.TantargyId
|
|
,mo.TantargyNev
|
|
|
|
SELECT DISTINCT
|
|
SUM(tmp.TantargyOraszam) AS OsszesTantargySzam
|
|
,STUFF(
|
|
(
|
|
SELECT DISTINCT
|
|
', ' + btmp.TantargyNev + '(' + CAST(btmp.TantargyOraszam AS nvarchar (max)) + ')'
|
|
FROM #megtartottOrakSzama btmp
|
|
FOR XML PATH(''), TYPE
|
|
).value('.', 'NVARCHAR(MAX)'), 1 , 2, ''
|
|
) AS Tantargyak
|
|
FROM #megtartottOrakSzama tmp
|
|
|
|
-- Csoport tanárai
|
|
SELECT
|
|
targy.C_NEV AS TargyNev
|
|
,tanar.C_NYOMTATASINEV AS TanarNev
|
|
,targykategoria.C_ORDER
|
|
,tanar.ID TanarId
|
|
,tanar.C_OKTATASIAZONOSITO AS TanarOktAzon
|
|
FROM T_FELHASZNALO_OSSZES tanar
|
|
INNER JOIN T_FOGLALKOZAS_OSSZES f ON f.C_TANARID = tanar.ID
|
|
AND f.TOROLT='F'
|
|
AND f.C_OSZTALYCSOPORTID = @csoportId
|
|
INNER JOIN T_TANTARGY_OSSZES targy ON targy.ID = f.C_TANTARGYID
|
|
AND targy.TOROLT='F'
|
|
INNER JOIN T_TARGYKATEGORIATIPUS_OSSZES ON T_TARGYKATEGORIATIPUS_OSSZES.ID = targy.C_TARGYKATEGORIA
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES targykategoria ON targykategoria.ID = T_TARGYKATEGORIATIPUS_OSSZES.ID
|
|
AND targykategoria.TOROLT = 'F'
|
|
AND targykategoria.C_TANEVID = @tanevId
|
|
WHERE tanar.TOROLT = 'F'
|
|
AND tanar.C_INTEZMENYID = @intezmenyid
|
|
AND tanar.c_tanevId = @tanevId
|
|
GROUP BY
|
|
C_ORDER
|
|
,targy.C_NEV
|
|
,tanar.C_NYOMTATASINEV
|
|
,tanar.ID
|
|
,tanar.C_OKTATASIAZONOSITO
|
|
ORDER BY
|
|
C_ORDER
|
|
,targy.C_NEV
|
|
,tanar.C_NYOMTATASINEV
|
|
|
|
END
|
|
GO
|