528 lines
22 KiB
Transact-SQL
528 lines
22 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetTanoranKivuliNaplo]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTanoranKivuliNaplo]
|
|
END
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetTanoranKivuliNaplo]
|
|
@tanevId INT,
|
|
@intezmenyId INT,
|
|
@csoportId INT,
|
|
@iskolaErdekuSzamit BIT
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
--Fejlecoldal (Fejléc)
|
|
SELECT
|
|
ia.C_NEV IntezmenyNev
|
|
,dbo.fnGetDokumentumIntezmenyCime(@tanevId) as IntezmenyCim
|
|
,ia.C_OMKOD OMKOD
|
|
,ia.C_IGAZGATONEVE IntezmenyVezeto
|
|
,ocs.C_NEV Csoport
|
|
,ISNULL(f.C_NYOMTATASINEV,'-') CsoportVezeto
|
|
,ISNULL(cs.C_CSOPORTNAPLOLEIRAS,'-') CsoportNaploLeiras
|
|
,FORMAT(cs.C_CSOPORTNAPLOMEGNYITASA, 'yyyy. MMMM dd.', 'hu-hu') Megnyitas
|
|
,ISNULL(FORMAT(cs.C_CSOPORTNAPLOZARASA, 'yyyy. MMMM dd.', 'hu-hu'),'-') Lezaras
|
|
,t.C_NEV as Tanev
|
|
,cs.C_CSOPORTNAPLOLEIRAS Megjegyzes
|
|
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
|
|
|
|
DECLARE @NaploSorszam TABLE (
|
|
TanuloId INT
|
|
,Sorszam INT
|
|
)
|
|
INSERT INTO @NaploSorszam
|
|
EXEC [sp_GetDokumentumTanuloNaploSorszam]
|
|
@osztalyId = @csoportId
|
|
|
|
CREATE TABLE #students (ID INT PRIMARY KEY, BelepesDatum DATE, KilepesDatum DATE, NaploSorszam INT, Osztaly NVARCHAR(MAX))
|
|
INSERT INTO #students
|
|
SELECT
|
|
t.TanuloId
|
|
,BelepesDatum
|
|
,ISNULL(KilepesDatum, GETDATE())
|
|
,nsz.Sorszam
|
|
,dbo.fnGetDokumentumTanuloAktualisOsztaly(t.TanuloId) Osztaly
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @csoportId, 'T') t
|
|
INNER JOIN @NaploSorszam nsz ON nsz.TanuloId = t.TanuloId
|
|
|
|
--Osztaly (Csoport tanulóinak osztályai)
|
|
SELECT
|
|
COUNT(Id) Tanulo
|
|
,Osztaly
|
|
FROM #students
|
|
GROUP BY Osztaly
|
|
|
|
--Mulasztasok
|
|
DECLARE @datum DATE = GETDATE()
|
|
CREATE TABLE #OraSorszam (TanitasioraId INT, OraSorszam NVARCHAR(100))
|
|
INSERT INTO #OraSorszam
|
|
EXEC sp_GetOraSorszamByOsztaly
|
|
@osztalyCsoportId = @csoportId,
|
|
@datum = @datum,
|
|
@intezmenyId = @intezmenyId,
|
|
@tanevId = @tanevId
|
|
|
|
DECLARE @FelevVege DATE = (SELECT TOP 1 C_DATUM FROM T_TANEVRENDJE_OSSZES te WHERE te.C_NAPTIPUSA=1400 AND te.C_INTEZMENYID=@intezmenyid AND te.C_TANEVID=@tanevid AND te.TOROLT='F')
|
|
|
|
DECLARE @mulasztas TABLE (Csoport NVARCHAR(1), Tanulo INT, Oraszam INT, Felev INT)
|
|
INSERT INTO @mulasztas
|
|
SELECT
|
|
'x' Csoport
|
|
,tm.C_ORATANULOIID Tanulo
|
|
,oes.OraSorszam OraSzam
|
|
,iif(tao.C_DATUM < @FelevVege,1,2)
|
|
FROM T_TANULOMULASZTAS_OSSZES tm
|
|
INNER JOIN T_TANITASIORA_OSSZES tao ON tm.C_TANITASIORAKID=tao.ID
|
|
LEFT JOIN #OraSorszam oes ON oes.TanitasioraId=tao.ID
|
|
WHERE
|
|
tao.C_OSZTALYCSOPORTID=@csoportId
|
|
AND tao.TOROLT='F'
|
|
and tm.TOROLT='F'
|
|
AND tm.C_TANEVID=@tanevid
|
|
AND tm.c_TIPUS IN (1499,1500)
|
|
AND (@iskolaErdekuSzamit = 1 OR (C_IGAZOLASTIPUSA <> 1533 OR C_IGAZOLASTIPUSA IS NULL))
|
|
|
|
SELECT f.C_NYOMTATASINEV Tanulo, tanulo.Osztaly,
|
|
m.[1], m.[2], m.[3], m.[4], m.[5], m.[6], m.[7], m.[8], m.[9], m.[10],
|
|
m.[11], m.[12], m.[13], m.[14], m.[15], m.[16], m.[17], m.[18],m.[19],m.[20],
|
|
m.[21], m.[22], m.[23], m.[24], m.[25], m.[26], m.[27], m.[28],m.[29],m.[30],
|
|
m.[31], m.[32], m.[33], m.[34], m.[35], m.[36], m.[37], m.[38],m.[39],m.[40],
|
|
m.[41], m.[42], m.[43], m.[44], m.[45], m.[46], m.[47], m.[48],m.[49],m.[50],
|
|
osszesM.Osszes, /*1. félév*/
|
|
m2.[1] m1, m2.[2] m2, m2.[3] m3, m2.[4] m4, m2.[5] m5, m2.[6] m6, m2.[7] m7, m2.[8] m8, m2.[9] m9, m2.[10] m10,
|
|
m2.[11] m11, m2.[12] m12, m2.[13] m13, m2.[14] m14, m2.[15] m15, m2.[16] m16, m2.[17] m17, m2.[18] m18, m2.[19] m19, m2.[20] m20,
|
|
m2.[21] m21, m2.[22] m22, m2.[23] m23, m2.[24] m24, m2.[25] m25, m2.[26] m26, m2.[27] m27, m2.[28] m28, m2.[29] m29, m2.[30] m30,
|
|
m2.[31] m31, m2.[32] m32, m2.[33] m33, m2.[34] m34, m2.[35] m35, m2.[36] m36, m2.[37] m37, m2.[38] m38, m2.[39] m39, m2.[40] m40,
|
|
m2.[41] m41, m2.[42] m42, m2.[43] m43, m2.[44] m44, m2.[45] m45, m2.[46] m46, m2.[47] m47, m2.[48] m48, m2.[49] m49, m2.[50] m50,
|
|
osszesM2.Osszes OsszesM/*2. félév*/
|
|
FROM #students tanulo
|
|
|
|
/* 1. félév*/
|
|
LEFT JOIN (SELECT * FROM @mulasztas mulasztas PIVOT (MAX(Csoport) FOR Oraszam IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
|
|
[11], [12], [13], [14], [15], [16], [17], [18],[19],[20],
|
|
[21], [22], [23], [24], [25], [26], [27], [28],[29],[30],
|
|
[31], [32], [33], [34], [35], [36], [37], [38],[39],[40],
|
|
[41], [42], [43], [44], [45], [46], [47], [48],[49],[50])) pv WHERE pv.Felev=1)m ON m.Tanulo=tanulo.Id
|
|
|
|
LEFT JOIN (SELECT * FROM @mulasztas mulasztas PIVOT (MAX(Csoport) FOR Oraszam IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
|
|
[11], [12], [13], [14], [15], [16], [17], [18],[19],[20],
|
|
[21], [22], [23], [24], [25], [26], [27], [28],[29],[30],
|
|
[31], [32], [33], [34], [35], [36], [37], [38],[39],[40],
|
|
[41], [42], [43], [44], [45], [46], [47], [48],[49],[50])) pv WHERE pv.Felev=2)m2 ON m2.Tanulo=tanulo.Id
|
|
|
|
INNER JOIN T_FELHASZNALO_OSSZES f on f.id=tanulo.Id
|
|
LEFT JOIN(SELECT Tanulo, COUNT(Csoport) Osszes FROM @mulasztas m WHERE Felev=1 GROUP BY Tanulo) osszesM ON osszesM.Tanulo=tanulo.Id
|
|
LEFT JOIN (SELECT Tanulo, COUNT(Csoport) Osszes FROM @mulasztas m WHERE Felev=2 GROUP BY Tanulo) osszesM2 ON osszesM2.Tanulo=tanulo.Id
|
|
ORDER BY f.C_NYOMTATASINEV
|
|
|
|
--Napló
|
|
DECLARE @letszam INT=(SELECT COUNT(tcs.C_TANULOID) FROM T_TANULOCSOPORT_OSSZES tcs WHERE tcs.C_OSZTALYCSOPORTID=@csoportId AND tcs.TOROLT='F')
|
|
|
|
SELECT
|
|
FORMAT(tao.C_ORAKEZDETE, 'yyyy.MM.dd. hh:mm') Datum
|
|
,IIF(C_MEGTARTOTT = 'F', 'Elmaradt', tao.C_TEMA) Tema
|
|
,@letszam-isnull(hianyzo.Hianyzok,0) Letszam
|
|
,IIF(C_MEGTARTOTT = 'F', '-', f.C_NYOMTATASINEV) Pedagogus
|
|
,Terem.C_NEV TeremNev
|
|
,isnull(Oraszam.OraSorszam + '.'
|
|
, '-') OraSorszam
|
|
FROM T_TANITASIORA_OSSZES tao
|
|
LEFT JOIN (SELECT tm.C_TANITASIORAKID Tanitasiora, COUNT(tm.C_ORATANULOIID) 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
|
|
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'
|
|
ORDER BY tao.C_ORAKEZDETE
|
|
|
|
--Hiányzások
|
|
DECLARE @hianyzas TABLE (Tanulo INT, Osztaly NVARCHAR(MAX), Honap INT, IgazoltE NVARCHAR(1))
|
|
INSERT INTO @hianyzas
|
|
SELECT
|
|
tanulo.Id
|
|
,Osztaly
|
|
,DATEPART(MONTH,tao.C_DATUM) Honap
|
|
,tm.C_IGAZOLT IgazoltE
|
|
FROM #students tanulo
|
|
LEFT JOIN T_TANULOMULASZTAS_OSSZES tm ON tm.C_ORATANULOIID=tanulo.Id AND tm.TOROLT='F' AND tm.C_TANEVID=@tanevid
|
|
LEFT JOIN T_TANITASIORA_OSSZES tao ON tao.id=tm.C_TANITASIORAKID AND tao.C_TANEVID=@tanevid
|
|
WHERE
|
|
tao.C_OSZTALYCSOPORTID=@csoportId
|
|
AND C_TIPUS IN (1499,1500)
|
|
AND (@iskolaErdekuSzamit = 1 OR (C_IGAZOLASTIPUSA <> 1533 OR C_IGAZOLASTIPUSA IS NULL))
|
|
AND tao.torolt='F'
|
|
|
|
SELECT f.C_NYOMTATASINEV as Tanulo, tanulo.Osztaly,
|
|
--igazolt
|
|
iif(i.[1]=0,null, i.[1]) as [1], iif(i.[2]=0,null, i.[2]) as [2], iif(i.[3]=0,null, i.[3]) as [3], iif(i.[4]=0,null, i.[4]) as [4], iif(i.[5]=0,null, i.[5]) as [5],iif(i.[6]=0,null, i.[6]) as [6],
|
|
iif(i.[7]=0,null, i.[7]) as [7], iif(i.[8]=0,null, i.[8]) as [8], iif(i.[9]=0,null, i.[9]) as [9], iif(i.[10]=0,null, i.[10]) as [10], iif(i.[11]=0,null, i.[11]) as [11], iif(i.[12]=0,null, i.[12]) as [12],
|
|
--igazolatlan
|
|
iif(ni.[1]=0,null, ni.[1]) as n1, iif(ni.[2]=0,null, ni.[2]) as n2, iif(ni.[3]=0,null, ni.[3]) as n3, iif(ni.[4]=0,null, ni.[4]) as n4, iif(ni.[5]=0,null, ni.[5]) as n5,iif(ni.[6]=0,null, ni.[6]) as n6,
|
|
iif(ni.[7]=0,null, ni.[7]) as n7, iif(ni.[8]=0,null, ni.[8]) as n8, iif(ni.[9]=0,null, ni.[9]) as n9, iif(ni.[10]=0,null, ni.[10]) as n10, iif(ni.[11]=0,null, ni.[11]) as n11, iif(ni.[12]=0,null, ni.[12]) as n12,
|
|
igazolt.Igazolt, igazolatlan.Igazolatlan
|
|
FROM #students tanulo
|
|
LEFT JOIN (SELECT pv.* from @hianyzas hianyzas PIVOT (COUNT(Honap) FOR Honap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))pv WHERE pv.IgazoltE='T')i ON tanulo.Id=i.Tanulo
|
|
LEFT JOIN (SELECT pv.* from @hianyzas hianyzas PIVOT (COUNT(Honap) FOR Honap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))pv WHERE pv.IgazoltE='F')ni ON tanulo.Id=ni.Tanulo
|
|
LEFT JOIN (SELECT hianyzas.Tanulo Tanulo, COUNT(IgazoltE) as Igazolt from @hianyzas hianyzas WHERE hianyzas.IgazoltE='T' GROUP BY hianyzas.Tanulo) igazolt ON igazolt.Tanulo=tanulo.Id
|
|
LEFT JOIN (SELECT hianyzas.Tanulo Tanulo, COUNT(IgazoltE) as Igazolatlan from @hianyzas hianyzas WHERE hianyzas.IgazoltE='F' GROUP BY hianyzas.Tanulo) igazolatlan ON igazolatlan.Tanulo=tanulo.Id
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.id=tanulo.Id
|
|
ORDER BY C_NYOMTATASINEV
|
|
|
|
--Hiányzások (Összesítő)
|
|
DECLARE @hianyzasOssz TABLE (Osztaly INT, Honap INT, IgazoltE NVARCHAR(1))
|
|
INSERT INTO @hianyzasOssz
|
|
SELECT
|
|
@csoportId
|
|
,DATEPART(MONTH,tao.C_DATUM) Honap
|
|
,tm.C_IGAZOLT IgazoltE
|
|
FROM #students tanulo
|
|
LEFT JOIN T_TANULOMULASZTAS_OSSZES tm ON tm.C_ORATANULOIID=tanulo.Id AND tm.TOROLT='F' AND tm.C_TANEVID=@tanevid
|
|
LEFT JOIN T_TANITASIORA_OSSZES tao ON tao.id=tm.C_TANITASIORAKID AND tao.C_TANEVID=@tanevid
|
|
WHERE
|
|
tao.C_OSZTALYCSOPORTID=@csoportId
|
|
AND tao.TOROLT='F'
|
|
AND C_TIPUS IN (1499,1500)
|
|
AND (@iskolaErdekuSzamit = 1 OR (C_IGAZOLASTIPUSA <> 1533 OR C_IGAZOLASTIPUSA IS NULL))
|
|
|
|
DECLARE @igazoltH TABLE (Csoport INT, [1] INT, [2] INT, [3] INT, [4] INT, [5] INT, [6] INT, [7] INT, [8] INT, [9] INT, [10] INT,
|
|
[11] INT, [12] INT)
|
|
INSERT INTO @igazoltH
|
|
SELECT @csoportId, iif(i.[1]=0,null, i.[1]) as [1], iif(i.[2]=0,null, i.[2]) as [2], iif(i.[3]=0,null, i.[3]) as [3], iif(i.[4]=0,null, i.[4]) as [4], iif(i.[5]=0,null, i.[5]) as [5],iif(i.[6]=0,null, i.[6]) as [6],
|
|
iif(i.[7]=0,null, i.[7]) as [7], iif(i.[8]=0,null, i.[8]) as [8], iif(i.[9]=0,null, i.[9]) as [9], iif(i.[10]=0,null, i.[10]) as [10], iif(i.[11]=0,null, i.[11]) as [11], iif(i.[12]=0,null, i.[12]) as [12]
|
|
from @hianyzasOssz ho pivot (count(Honap) for Honap in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))i where i.IgazoltE='T'
|
|
|
|
DECLARE @igazolatlanH TABLE (Csoport INT, [1] INT, [2] INT, [3] INT, [4] INT, [5] INT, [6] INT, [7] INT, [8] INT, [9] INT, [10] INT,
|
|
[11] INT, [12] INT)
|
|
INSERT INTO @igazolatlanH
|
|
SELECT @csoportId, iif(ni.[1]=0,null, ni.[1]) as n1, iif(ni.[2]=0,null, ni.[2]) as n2, iif(ni.[3]=0,null, ni.[3]) as n3, iif(ni.[4]=0,null, ni.[4]) as n4, iif(ni.[5]=0,null, ni.[5]) as n5,iif(ni.[6]=0,null, ni.[6]) as n6,
|
|
iif(ni.[7]=0,null, ni.[7]) as n7, iif(ni.[8]=0,null, ni.[8]) as n8, iif(ni.[9]=0,null, ni.[9]) as n9, iif(ni.[10]=0,null, ni.[10]) as n10, iif(ni.[11]=0,null, ni.[11]) as n11, iif(ni.[12]=0,null, ni.[12]) as n12
|
|
from @hianyzasOssz ho pivot (count(Honap) for Honap in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))ni where ni.IgazoltE='F'
|
|
|
|
--Hiányzás (Mulasztott órák összesen)
|
|
SELECT iif(i.[1]=0,null, i.[1]) as [1], iif(i.[2]=0,null, i.[2]) as [2], iif(i.[3]=0,null, i.[3]) as [3], iif(i.[4]=0,null, i.[4]) as [4], iif(i.[5]=0,null, i.[5]) as [5],iif(i.[6]=0,null, i.[6]) as [6],
|
|
iif(i.[7]=0,null, i.[7]) as [7], iif(i.[8]=0,null, i.[8]) as [8], iif(i.[9]=0,null, i.[9]) as [9], iif(i.[10]=0,null, i.[10]) as [10], iif(i.[11]=0,null, i.[11]) as [11], iif(i.[12]=0,null, i.[12]) as [12],
|
|
iif(ni.[1]=0,null, ni.[1]) as n1, iif(ni.[2]=0,null, ni.[2]) as n2, iif(ni.[3]=0,null, ni.[3]) as n3, iif(ni.[4]=0,null, ni.[4]) as n4, iif(ni.[5]=0,null, ni.[5]) as n5,iif(ni.[6]=0,null, ni.[6]) as n6,
|
|
iif(ni.[7]=0,null, ni.[7]) as n7, iif(ni.[8]=0,null, ni.[8]) as n8, iif(ni.[9]=0,null, ni.[9]) as n9, iif(ni.[10]=0,null, ni.[10]) as n10, iif(ni.[11]=0,null, ni.[11]) as n11, iif(ni.[12]=0,null, ni.[12]) as n12
|
|
from @igazolth i
|
|
left join @igazolatlanH ni on ni.Csoport=i.csoport
|
|
|
|
--Hiányzás (Mulasztó tanulók száma)
|
|
DECLARE @Igazolt TABLE (Csoport INT, Osszes INT)
|
|
INSERT INTO @Igazolt
|
|
SELECT @csoportId Id, COUNT(Tanulo) IgazoltOsszes FROM (SELECT DISTINCT tanulo FROM @hianyzas WHERE IgazoltE='T')m
|
|
|
|
SELECT i.Osszes IgazoltOsszes, ni.Osszes IgazolatlanOsszes FROM @Igazolt i
|
|
LEFT JOIN (SELECT @csoportId Id, COUNT(Tanulo) as Osszes FROM (SELECT DISTINCT tanulo FROM @hianyzas WHERE IgazoltE='F')m) ni ON ni.Id=i.Csoport
|
|
|
|
--Hiányzás (Mulasztott órák Összesen-Összesen)
|
|
DECLARE @IgazoltOsszesOsszes TABLE (Csoport INT, Osszes INT)
|
|
INSERT INTO @IgazoltOsszesOsszes
|
|
SELECT @csoportId, COUNT(IgazoltE) FROM @hianyzasOssz WHERE IgazoltE='T'
|
|
|
|
SELECT ioo.Osszes IgazoltOsszesOsszes, nioo.Osszes as IgazolatlanOsszesOsszes FROM @IgazoltOsszesOsszes ioo
|
|
LEFT JOIN (SELECT @csoportId Id, count(IgazoltE) Osszes FROM @hianyzasOssz WHERE IgazoltE='F') nioo ON nioo.Id=ioo.Csoport
|
|
|
|
/*TANULÓ adatai*/
|
|
SELECT DISTINCT
|
|
tanuloID
|
|
,sorszam
|
|
,torzslapSzam
|
|
,oktAzon
|
|
,tajSzam
|
|
,anyjaNeve
|
|
,szulHely
|
|
,FORMAT(szulIdo, 'yyyy.MM.dd.') szulIdo
|
|
,IIF(socAdat = '', '', LEFT(socAdat, len(socAdat)- 1)) socAdat
|
|
,kepzes
|
|
,tanuloNeve
|
|
,allampolgarsaga
|
|
,diakIgazolvanySzam
|
|
,FORMAT(kilepesDatum, 'yyyy.MM.dd.') kilepesDatum
|
|
,beirasiNaploSorszam
|
|
,dbo.fnGetDokumentumTanuloAktualisOsztaly(tanuloID) Osztalya
|
|
INTO #TanuloAlapAdatok
|
|
FROM (
|
|
SELECT
|
|
t.ID AS tanuloID
|
|
,naploSorszam.Sorszam AS sorszam
|
|
,f.C_NYILVANTARTASISZAM AS torzslapSzam
|
|
,f.C_OKTATASIAZONOSITO AS oktAzon
|
|
,f.C_TAJSZAM AS tajSzam
|
|
,f.C_ANYJANEVE AS anyjaNeve
|
|
,f.C_SZULETESIHELY AS szulHely
|
|
,f.C_SZULETESIDATUM AS szulIdo
|
|
,IIF(t.C_MAGANTANULO = 'T', 'Magántanuló (eü. ok), ', '') +
|
|
IIF(t.C_JOGVISZONYATSZUNETELTETO = 'T', 'Jogviszony szüneteltetve, ', '') +
|
|
IIF(t.C_SZAKMAIGYAKORLATON = 'T', 'Szakmai gyakorlat, ', '') +
|
|
IIF(t.C_SZOCIALISTAMOGATAS = 'T', 'Szociális támogatás, ', '') +
|
|
IIF(t.C_SAJATOSNEVELESU = 'T', 'Sajátos nevelési igényű, ', '') +
|
|
IIF(t.C_BEJARO = 'T', 'Bejáró, ', '')
|
|
AS socAdat
|
|
,tt.C_NEV AS kepzes
|
|
,f.C_NYOMTATASINEV AS tanuloNeve
|
|
,d.C_NAME AS allampolgarsaga
|
|
,t.C_DIAKIGAZOLVANYSZAM AS diakIgazolvanySzam
|
|
,MAX(cs.C_KILEPESDATUM) AS kilepesDatum
|
|
,t.C_BEIRASINAPLOSORSZAM AS beirasiNaploSorszam
|
|
FROM T_TANULO_OSSZES t
|
|
INNER JOIN #students s ON s.ID = t.ID
|
|
INNER JOIN @NaploSorszam naploSorszam on naploSorszam.TanuloId=s.ID
|
|
INNER JOIN T_TANTERV_OSSZES tt ON tt.ID = t.C_TANTERVID
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = t.ID
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES cs ON s.ID = cs.C_TANULOID AND cs.C_OSZTALYCSOPORTID = @csoportId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.id=f.C_ALLAMPOLGARSAGA AND d.C_TANEVID = f.C_TANEVID AND d.C_INTEZMENYID = f.C_INTEZMENYID
|
|
GROUP BY
|
|
s.ID, t.ID, naploSorszam.Sorszam, f.C_NYILVANTARTASISZAM, f.C_OKTATASIAZONOSITO, f.C_TAJSZAM, f.C_ANYJANEVE,
|
|
f.C_SZULETESIHELY, f.C_SZULETESIDATUM, t.C_MAGANTANULO, t.C_JOGVISZONYATSZUNETELTETO, t.C_SZAKMAIGYAKORLATON, C_SAJATOSNEVELESU, t.C_BEJARO,
|
|
t.C_SZOCIALISTAMOGATAS, tt.C_NEV, f.C_NYOMTATASINEV, d.C_NAME, t.C_DIAKIGAZOLVANYSZAM, t.C_BEIRASINAPLOSORSZAM
|
|
) tbl
|
|
|
|
|
|
SELECT * FROM #TanuloAlapAdatok ORDER BY tanuloNeve
|
|
|
|
/*TANULÓ elérhetőségei */
|
|
DECLARE @Gondviselok TABLE (TanuloId INT, GondviseloId INT)
|
|
INSERT INTO @Gondviselok
|
|
SELECT
|
|
s.Id
|
|
,gondviselo.Id
|
|
FROM T_GONDVISELO_OSSZES gondviselo
|
|
INNER JOIN #students s on s.id= gondviselo.C_TANULOID AND gondviselo.TOROLT = 'F'
|
|
|
|
DECLARE @CimTableTemp TABLE (FelhasznaloId INT, Cim NVARCHAR(MAX), CimTipus INT, GondviseloId INT)
|
|
INSERT INTO @CimTableTemp
|
|
SELECT
|
|
TanuloId
|
|
,C_IRANYITOSZAM + ' ' + C_VAROS + ', ' + C_KOZTERULET + ' ' + kozterulet.C_NAME + ' ' + C_HAZSZAM
|
|
,C_CIMTIPUSA
|
|
,C_GONDVISELOID
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @csoportId, 'T') tanulo
|
|
INNER JOIN T_CIM_OSSZES cim ON cim.C_FELHASZNALOID = TanuloId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES kozterulet ON kozterulet.Id = C_KOZTERULETJELLEGE AND kozterulet.C_TANEVID = @TanevId
|
|
WHERE
|
|
cim.TOROLT = 'F'
|
|
AND cim.C_TANEVID = @tanevId
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
TanuloId
|
|
,C_IRANYITOSZAM + ' ' + C_VAROS + ', ' + C_KOZTERULET + ' ' + kozterulet.C_NAME + ' ' + C_HAZSZAM
|
|
,C_CIMTIPUSA
|
|
,GondviseloId
|
|
FROM @Gondviselok gonviselo
|
|
INNER JOIN T_CIM_OSSZES cim ON cim.C_GONDVISELOID = GondviseloId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES kozterulet ON kozterulet.Id = C_KOZTERULETJELLEGE AND kozterulet.C_TANEVID = @TanevId
|
|
WHERE
|
|
cim.TOROLT = 'F'
|
|
AND cim.C_TANEVID = @tanevId
|
|
|
|
DECLARE @TanuloCimTable TABLE (FelhasznaloId INT, Cim NVARCHAR(MAX), Cimtipusa NVARCHAR(MAX))
|
|
INSERT INTO @TanuloCimTable
|
|
SELECT DISTINCT
|
|
FelhasznaloId
|
|
,STUFF((
|
|
SELECT DISTINCT CAST(Btemp.Cim AS NVARCHAR(MAX)) + CHAR(13) + CHAR(10)
|
|
FROM @CimTableTemp btemp
|
|
WHERE
|
|
temp.FelhasznaloId = btemp.FelhasznaloId
|
|
AND temp.CimTipus = btemp.CimTipus
|
|
AND GondviseloId IS NULL
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,0,'')
|
|
,C_NAME
|
|
FROM @CimTableTemp temp
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES cimTipusa ON cimTipusa.Id = Cimtipus
|
|
WHERE
|
|
GondviseloId IS NULL
|
|
|
|
DECLARE @GondViseloCimTable TABLE (GondviseloId INT, Cim NVARCHAR(MAX), Cimtipusa NVARCHAR(MAX))
|
|
INSERT INTO @GondViseloCimTable
|
|
SELECT DISTINCT
|
|
GondviseloId
|
|
,STUFF((
|
|
SELECT DISTINCT CAST(btemp.Cim AS NVARCHAR(MAX)) + CHAR(13) + CHAR(10)
|
|
FROM @CimTableTemp btemp
|
|
WHERE
|
|
temp.GondviseloId = btemp.GondviseloId
|
|
AND temp.CimTipus = btemp.CimTipus
|
|
AND GondviseloId IS NOT NULL
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,0,'')
|
|
,C_NAME
|
|
FROM @CimTableTemp temp
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES cimTipusa ON cimTipusa.Id = Cimtipus
|
|
WHERE
|
|
GondviseloId IS NOT NULL
|
|
|
|
DECLARE @EmailCimTableTemp TABLE (FelhasznaloId INT, EmailCim NVARCHAR(MAX), GondviseloId INT)
|
|
INSERT INTO @EmailCimTableTemp
|
|
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
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
TanuloId
|
|
,C_EMAILCIM
|
|
,GondviseloId
|
|
FROM @Gondviselok
|
|
INNER JOIN T_EMAIL_OSSZES email ON email.C_GONDVISELOID = GondviseloId
|
|
WHERE
|
|
email.TOROLT = 'F'
|
|
AND email.C_TANEVID = @tanevId
|
|
AND LEN(C_EMAILCIM) > 0
|
|
|
|
DECLARE @EmailCimTanuloTable TABLE (FelhasznaloId INT, EmailCim NVARCHAR(MAX))
|
|
INSERT INTO @EmailCimTanuloTable
|
|
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
|
|
|
|
DECLARE @EmailCimGondviseloTable TABLE (GondviseloId INT, EmailCim NVARCHAR(MAX))
|
|
INSERT INTO @EmailCimGondviseloTable
|
|
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
|
|
|
|
DECLARE @TelefonTableTemp TABLE (FelhasznaloId INT, Telefon NVARCHAR(MAX), GondviseloId INT)
|
|
INSERT INTO @TelefonTableTemp
|
|
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
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
TanuloId
|
|
,C_TELEFONSZAM
|
|
,GondviseloId
|
|
FROM @Gondviselok gondviselo
|
|
INNER JOIN T_TELEFON_OSSZES telefon ON telefon.C_GONDVISELOID = GondviseloId
|
|
WHERE
|
|
telefon.TOROLT = 'F'
|
|
AND telefon.C_TANEVID = @tanevId
|
|
AND LEN(telefon.C_TELEFONSZAM) > 0
|
|
|
|
DECLARE @TanuloTelefonTable TABLE (FelhasznaloId INT, Telefon NVARCHAR(MAX))
|
|
INSERT INTO @TanuloTelefonTable
|
|
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
|
|
|
|
DECLARE @GondviseloTelefonTable TABLE (GondviseloId INT, Telefon NVARCHAR(MAX))
|
|
INSERT INTO @GondviseloTelefonTable
|
|
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 tanuloid
|
|
,cim.Cimtipusa cimtipus
|
|
,cim.Cim
|
|
,email.EmailCim email
|
|
,telefon.Telefon 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
|
|
s.ID
|
|
,g.C_NEV + ' (' + RokonsagiFok.C_NAME + ')' C_NEV
|
|
,cim.Cimtipusa C_CIMTIPUSA
|
|
,cim.cim C_OSSZETETTCIM
|
|
,telefon.telefon C_TELEFONSZAM
|
|
,email.emailcim C_EMAILCIM
|
|
FROM #students s
|
|
INNER JOIN T_GONDVISELO_OSSZES g ON g.C_TANULOID = s.ID
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES RokonsagiFok ON RokonsagiFok.ID = g.C_ROKONSAGFOKA AND RokonsagiFok.C_TANEVID = @tanevId
|
|
LEFT JOIN @EmailCimGondviseloTable email ON email.GondviseloId = g.ID
|
|
LEFT JOIN @GondviseloTelefonTable telefon ON telefon.GondviseloId = g.ID
|
|
LEFT JOIN @GondViseloCimTable cim ON cim.GondviseloId = g.ID
|
|
WHERE
|
|
g.Torolt = 'F'
|
|
ORDER BY Cimtipusa
|
|
|
|
|
|
END
|
|
|
|
GO |