721 lines
22 KiB
Transact-SQL
721 lines
22 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetNapkozisNaplo
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetNapkozisNaplo
|
|
@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
|
|
@elsonap date
|
|
,@utolsonap date
|
|
,@FelevVege date
|
|
,@datum date = GETDATE()
|
|
|
|
CREATE TABLE #OraSorszam (
|
|
TanitasioraId int PRIMARY KEY
|
|
,OraSorszam int
|
|
)
|
|
|
|
--Tanulók adatai, elérhetőségei
|
|
CREATE TABLE #tanulok (
|
|
Id int
|
|
,TanuloCsoportId int
|
|
,Nev nvarchar (max)
|
|
,Osztaly nvarchar (max)
|
|
,OsztalyId int
|
|
,Sorszam nvarchar (max)
|
|
,beirasiSorszam nvarchar (max)
|
|
,Torzslapszam nvarchar (max)
|
|
,Allampolgarsaga nvarchar (max)
|
|
,DiakIg nvarchar (max)
|
|
,SzulHely nvarchar (max)
|
|
,SzulIdo nvarchar (max)
|
|
,Anyja nvarchar (max)
|
|
,Taj nvarchar (max)
|
|
,OktAzon nvarchar (max)
|
|
,Kilepes nvarchar (max)
|
|
,Kepzes nvarchar (max)
|
|
,TanuloCimTipus nvarchar (max)
|
|
,TanuloCim nvarchar (max)
|
|
,TanuloEmail nvarchar (max)
|
|
,szoc nvarchar (max)
|
|
,BelepesDatum datetime
|
|
)
|
|
|
|
DECLARE @Mulasztasok TABLE (
|
|
IgazoltE nvarchar (1)
|
|
,Tanulo int
|
|
,Tipus int
|
|
)
|
|
|
|
DECLARE @GondViseloCimTable TABLE (
|
|
GondviseloId int
|
|
,Cim nvarchar (max)
|
|
,Cimtipusa nvarchar (max)
|
|
)
|
|
|
|
DECLARE @EmailCimTableTemp TABLE (
|
|
FelhasznaloId int
|
|
,EmailCim nvarchar (max)
|
|
,GondviseloId int
|
|
)
|
|
|
|
DECLARE @EmailCimGondviseloTable TABLE (
|
|
GondviseloId int
|
|
,EmailCim nvarchar (max)
|
|
)
|
|
|
|
DECLARE @TelefonTableTemp TABLE (
|
|
FelhasznaloId int
|
|
,Telefon nvarchar (max)
|
|
,GondviseloId int
|
|
)
|
|
|
|
DECLARE @GondviseloTelefonTable TABLE (
|
|
GondviseloId int
|
|
,Telefon nvarchar (max)
|
|
)
|
|
|
|
SELECT TOP 1
|
|
@FelevVege = tr.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
WHERE tr.C_NAPTIPUSA = 1400
|
|
AND tr.C_TANEVID = @tanevId
|
|
AND tr.TOROLT = 'F'
|
|
|
|
INSERT INTO #tanulok (
|
|
Id
|
|
,TanuloCsoportId
|
|
,Nev
|
|
,Osztaly
|
|
,OsztalyId
|
|
,Sorszam
|
|
,beirasiSorszam
|
|
,Torzslapszam
|
|
,Allampolgarsaga
|
|
,DiakIg
|
|
,SzulHely
|
|
,SzulIdo
|
|
,Anyja
|
|
,Taj
|
|
,OktAzon
|
|
,Kilepes
|
|
,Kepzes
|
|
,TanuloCimTipus
|
|
,TanuloCim
|
|
,TanuloEmail
|
|
,szoc
|
|
,BelepesDatum
|
|
)
|
|
SELECT
|
|
tcs.C_TANULOID AS Id
|
|
,tcs.ID AS TanuloCsoportId
|
|
,IIF(tcs.C_KILEPESDATUM IS NOT NULL, f.C_NYOMTATASINEV + ' (kilépett)', f.C_NYOMTATASINEV) AS Nev
|
|
,osztaly.OsztalyNev Osztaly
|
|
,osztaly.ID AS OsztalyId
|
|
,tta.C_NAPLOSORSZAM AS NaploSorszam
|
|
,tta.C_BEIRASINAPLOSORSZAM AS beirasiSorszam
|
|
,tta.C_TORZSLAPSZAM AS TorzsLapSzam
|
|
,Allapolgarasaga.C_NAME AS Allampolgarsag
|
|
,t.C_DIAKIGAZOLVANYSZAM AS DiakIg
|
|
,f.C_SZULETESIHELY AS SzulHely
|
|
,FORMAT(f.C_SZULETESIDATUM, 'yyyy.MM.dd.') AS SzulIdo
|
|
,f.C_ANYJANEVE AS Anyja
|
|
,f.C_TAJSZAM AS Taj
|
|
,f.C_OKTATASIAZONOSITO AS OktAzon
|
|
,FORMAT(tcs.C_KILEPESDATUM, 'yyyy.MM.dd.') AS Kilepes
|
|
,tt.c_nev AS Kepzes
|
|
,cim.Tipus
|
|
,cim.cim
|
|
,email.c_emailcim AS TanuloEmail
|
|
,STUFF(
|
|
IIF(tta.C_MAGANTANULO = 'T', ', Magántanuló (' + ISNULL(MagantanulosagOka.C_NAME, '-') + ')', '')
|
|
+ IIF(tta.C_JOGVISZONYATSZUNETELTETO = 'T', ', Jogviszony szüneteltetve', '')
|
|
+ IIF(tta.C_SZAKMAIGYAKORLATON = 'T', ', Szakmai gyakorlat', '')
|
|
+ IIF(t.C_SZOCIALISTAMOGATAS = 'T', ', Szociális támogatás', ''), 1, 2, ''
|
|
) AS szoc
|
|
,tcs.C_BELEPESDATUM 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 f ON f.ID = tcs.C_TANULOID
|
|
INNER JOIN T_TANULO_OSSZES t ON t.ID = tcs.C_TANULOID
|
|
INNER JOIN fnGetOsztalyCsoportTanuloinakTanugyiAdatai(@csoportId, DEFAULT) tta ON tta.TanuloId = t.ID
|
|
INNER JOIN T_TANTERV_OSSZES tt ON tt.ID = tta.C_TANTERVID
|
|
AND tt.TOROLT = 'F'
|
|
AND tt.C_TANEVID = @tanevId
|
|
LEFT JOIN (
|
|
SELECT DISTINCT
|
|
TanuloId AS TanuloId
|
|
,cimTipusa.C_NAME AS Tipus
|
|
,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, ''
|
|
) AS Cim
|
|
FROM fnGetDokumentumTanuloOrGondviseloCim(0, @tanevId, @csoportid) temp
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES AS cimTipusa ON cimTipusa.Id = temp.CimTipusa
|
|
AND cimTipusa.C_TANEVID = @tanevId
|
|
AND cimTipusa.TOROLT = 'F'
|
|
WHERE temp.CimTipusa = 907
|
|
) AS cim ON cim.TanuloId = t.ID
|
|
LEFT JOIN T_TELEFON_OSSZES tel ON tel.c_felhasznaloid = t.ID
|
|
AND tel.c_gondviseloid IS NULL
|
|
AND tel.torolt = 'F'
|
|
AND tel.c_alapertelmezett = 'T'
|
|
LEFT JOIN T_EMAIL_OSSZES email ON email.c_felhasznaloid = t.ID
|
|
AND email.c_gondviseloid IS NULL
|
|
AND email.torolt = 'F'
|
|
AND email.c_alapertelmezett = 'T'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES AS MagantanulosagOka ON MagantanulosagOka.ID = tta.C_MAGANTANULOSAGANAKOKAID
|
|
AND MagantanulosagOka.C_TANEVID = tt.C_TANEVID
|
|
AND MagantanulosagOka.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES Allapolgarasaga ON Allapolgarasaga.ID = f.C_ALLAMPOLGARSAGA
|
|
AND Allapolgarasaga.C_TANEVID = tt.C_TANEVID
|
|
AND Allapolgarasaga.TOROLT = 'F'
|
|
WHERE tcs.C_TANEVID = @tanevId
|
|
AND tcs.C_OSZTALYCSOPORTID = @csoportId
|
|
|
|
--Kezdőoldal
|
|
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 NapkozisCsoport
|
|
,IIF(LEN(ocs.c_nev) > 17, SUBSTRING(ocs.c_nev, 1, 17) + '...', ocs.c_nev) AS RovidNev
|
|
,ISNULL(f.c_nyomtatasinev, '-') AS CsoportVezeto
|
|
,ISNULL(cs.c_csoportnaploleiras, '-') AS CsoportNaploLeiras
|
|
,FORMAT(cs.c_csoportnaplomegnyitasa, 'yyyy.MM.dd.') AS Megnyitas
|
|
,ISNULL(FORMAT(cs.c_csoportnaplozarasa, 'yyyy.MM.dd.'), '-') AS Lezaras
|
|
,ocs.ID AS NapkozisCsoportId
|
|
,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
|
|
AND ocs.c_tanevid = @tanevid
|
|
AND ocs.id = @csoportId
|
|
INNER JOIN T_CSOPORT_OSSZES cs ON cs.id = ocs.id
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.id = cs.c_csoportvezetoid
|
|
WHERE i.id = @intezmenyid
|
|
AND ia.c_tanevid = @tanevid
|
|
|
|
--Csoportot vezette
|
|
SELECT
|
|
ISNULL(f.c_nyomtatasinev, '-') AS CsoportVezeto
|
|
FROM T_CSOPORT_OSSZES cs
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.id = cs.c_csoportvezetoid
|
|
WHERE cs.id = @csoportId
|
|
AND cs.c_altanevid = @tanevid
|
|
AND cs.c_alintezmenyid = @intezmenyid
|
|
|
|
--Tanulók
|
|
SELECT DISTINCT
|
|
Nev
|
|
,Osztaly
|
|
,OktAzon
|
|
,OsztalyId
|
|
,BelepesDatum
|
|
FROM #tanulok
|
|
ORDER BY
|
|
Nev
|
|
|
|
--Naplóhetek
|
|
|
|
SELECT TOP 1
|
|
@elsonap = t.C_KEZDONAP
|
|
FROM T_TANEV_OSSZES t
|
|
WHERE t.ID = @tanevId
|
|
AND t.TOROLT = 'F'
|
|
|
|
IF (
|
|
SELECT
|
|
c_osztalycsoportid
|
|
FROM T_OSZTALYCSOPORT_TANEVRENDJE otr
|
|
INNER JOIN t_tanevrendje_osszes tr ON tr.id = otr.c_tanevrendjeid
|
|
AND tr.c_naptipusa = 1395
|
|
WHERE c_osztalycsoportid = @csoportid
|
|
) IS NULL
|
|
|
|
SELECT TOP 1
|
|
@utolsonap = tr.c_datum
|
|
FROM t_tanevrendje_osszes tr
|
|
WHERE c_naptipusa = 1395
|
|
AND c_tanevid = @tanevId
|
|
AND c_intezmenyid = @intezmenyId
|
|
AND torolt = 'F'
|
|
AND c_osszescsoportravonatkozik = 'T'
|
|
|
|
ELSE
|
|
|
|
SELECT TOP 1
|
|
@utolsonap = tr.c_datum
|
|
FROM T_OSZTALYCSOPORT_TANEVRENDJE otr
|
|
INNER JOIN t_tanevrendje_osszes tr ON tr.id = otr.c_tanevrendjeid
|
|
AND tr.c_naptipusa = 1395
|
|
WHERE c_osztalycsoportid = @csoportid
|
|
|
|
CREATE TABLE #NapSorszamTemp (
|
|
Datum DATE
|
|
)
|
|
INSERT INTO #NapSorszamTemp
|
|
EXEC sp_GetDokumentumTanitasiNapokSorszama
|
|
@tanevId = @tanevId
|
|
,@osztalyId = @csoportId
|
|
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY ret.Het) AS TanitasiHetSorszam
|
|
,ret.Het
|
|
,dbo.fnGetDokumentumDatumFormatum(ret.HetEleje) AS HetEleje
|
|
,dbo.fnGetDokumentumDatumFormatum(ret.HetVege) AS HetVege
|
|
FROM (
|
|
SELECT DISTINCT
|
|
ha.*
|
|
FROM (
|
|
SELECT
|
|
nn.C_HETSORSZAMA AS Het
|
|
,MIN(nn.C_NAPDATUMA) AS HetEleje
|
|
,MAX(nn.C_NAPDATUMA) AS HetVege
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
WHERE nn.C_NAPDATUMA BETWEEN @elsonap AND @utolsonap
|
|
AND nn.TOROLT='F'
|
|
AND nn.c_tanevId = @tanevId
|
|
GROUP BY nn.C_HETSORSZAMA
|
|
) ha
|
|
INNER JOIN #NapSorszamTemp ns ON ns.Datum >= ha.HetEleje and ns.Datum <= ha.HetVege
|
|
) ret
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
NULL AS TanitasiHetSorszam
|
|
,ret.Het
|
|
,dbo.fnGetDokumentumDatumFormatum(ret.HetEleje) AS HetEleje
|
|
,dbo.fnGetDokumentumDatumFormatum(ret.HetVege) AS HetVege
|
|
FROM (
|
|
SELECT DISTINCT
|
|
ha.*
|
|
FROM (
|
|
SELECT
|
|
nn.C_HETSORSZAMA AS Het
|
|
,MIN(nn.C_NAPDATUMA) AS HetEleje
|
|
,MAX(nn.C_NAPDATUMA) AS HetVege
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
WHERE nn.C_NAPDATUMA BETWEEN @elsonap AND @utolsonap
|
|
AND nn.TOROLT='F'
|
|
AND nn.c_tanevId = @tanevId
|
|
GROUP BY nn.C_HETSORSZAMA
|
|
) ha
|
|
LEFT JOIN #NapSorszamTemp ns ON ns.Datum >= ha.HetEleje and ns.Datum <= ha.HetVege
|
|
WHERE ns.Datum IS NULL
|
|
) ret
|
|
ORDER BY ret.Het
|
|
|
|
--Tanuló adatok
|
|
SELECT
|
|
*
|
|
FROM #tanulok
|
|
ORDER BY
|
|
Nev
|
|
|
|
SELECT
|
|
DATEPART(MONTH, Datum) AS Honap
|
|
,TantargyId AS Id
|
|
,Igazolt AS IgazoltE
|
|
,TanuloId AS TanuloId
|
|
,TanuloCsoportId AS TanuloCsoportId
|
|
,IgazolasTipusa AS IgazolasTipus
|
|
,Datum AS Datum
|
|
,Tipusa AS Tipus
|
|
,KesesPercben AS KesesPercben
|
|
,HetSorszama AS HetSorszama
|
|
,Oraszam AS Oraszam
|
|
,CAST(DATEPART(MONTH, Datum) AS nvarchar (10)) + IIF(C_GYAKORLATI = 'T', 'G', 'E') AS ElmeletGyakorlat
|
|
,C_GYAKORLATI AS isGyakorlat
|
|
INTO #mulasztasTmp
|
|
FROM fnGetDokumentumMulasztasokOsztalyonkentReszletes(@tanevId, @csoportId, @iskolaErdekuSzamit, 1, 0, DEFAULT, DEFAULT, 0) TanulokMulasztasai
|
|
INNER JOIN T_TANTARGY_OSSZES AS Tantargy ON Tantargy.Id = TanulokMulasztasai.TantargyId
|
|
|
|
SELECT
|
|
ta.TanuloId AS Id
|
|
,ta.TanuloCsoportId
|
|
,ta.Sorszam
|
|
,ta.TorzslapSzam
|
|
,ta.OktAzon
|
|
,ta.TajSzam
|
|
,ta.AnyjaNeve
|
|
,ta.SzulHely
|
|
,ta.SzulIdo
|
|
,ta.SocAdat
|
|
,ta.Kepzes
|
|
,ta.TanuloNeve
|
|
,ta.Allampolgarsaga
|
|
,ta.DiakIgazolvanySzam
|
|
,ta.KilepesDatum
|
|
,ta.BeirasiNaploSorszam
|
|
,ta.AdottOsztaly AS Osztaly
|
|
,ta.AdottOsztalyId AS OsztalyId
|
|
,CONVERT(DATE, LEFT(ta.BelepesDatum, LEN(ta.BelepesDatum) - 1), 102) AS BelepesDatum
|
|
INTO #studentsWithTanuloCsoport
|
|
FROM fnGetDokumentumTanulokAdatai(@tanevId, @csoportId) ta
|
|
|
|
EXEC sp_GetDokumentumMulasztasokIdoszakonkent
|
|
@tanevId = @tanevId
|
|
,@osztalyCsoportId = @csoportId
|
|
,@iskolaErdekuSzamit = @iskolaErdekuSzamit
|
|
,@isReszletesMulasztasok = 0
|
|
|
|
--Napló
|
|
SELECT DISTINCT
|
|
nn.c_hetsorszama AS Het
|
|
,d.c_name AS Nap
|
|
,FORMAT(nn.c_napdatuma, 'yyyy. MMMM dd.', 'hu-hu') AS Datum
|
|
FROM T_NAPTARINAP_OSSZES nn
|
|
LEFT JOIN T_TANITASIORA_OSSZES tao ON tao.c_datum = nn.c_napdatuma
|
|
AND tao.c_osztalycsoportid = @csoportid
|
|
AND tao.torolt = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.id = nn.c_hetnapja
|
|
AND d.C_TANEVID = @tanevId
|
|
AND d.TOROLT = 'F'
|
|
LEFT JOIN T_FOGLALKOZAS_OSSZES fog ON fog.id = tao.c_foglalkozasid
|
|
AND fog.c_tanevid = @tanevid
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.id = fog.c_tanarid
|
|
AND f.torolt = 'F'
|
|
WHERE nn.c_napdatuma >= @elsonap
|
|
AND nn.c_napdatuma <= @utolsonap
|
|
ORDER BY
|
|
nn.c_hetsorszama
|
|
,FORMAT(nn.c_napdatuma, 'yyyy. MMMM dd.', 'hu-hu')
|
|
|
|
--Napló adatok
|
|
INSERT INTO #OraSorszam (
|
|
TanitasioraId
|
|
,OraSorszam
|
|
)
|
|
EXEC uspGetOraSorszamByOsztaly
|
|
@osztalyCsoportId = @csoportId
|
|
,@datum = @datum
|
|
,@intezmenyId = @intezmenyId
|
|
,@tanevId = @tanevId
|
|
|
|
SELECT DISTINCT
|
|
tao.C_ORASZAM AS Oraszam
|
|
,tao.C_HETSORSZAMA AS Het
|
|
,FORMAT(tao.C_DATUM, 'yyyy. MMMM dd.', 'hu-hu') AS Datum
|
|
,CONVERT(varchar(5), tao.C_ORAKEZDETE, 108) + '-' + CONVERT(varchar(5), tao.C_ORAVEGE, 108) AS DatumKezdettel
|
|
,ISNULL('Helyettesítő: ' + helyettesito.C_NYOMTATASINEV, f.C_NYOMTATASINEV) AS Tanar
|
|
,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
|
|
,tao.C_TEMA AS Tema
|
|
,OraSorszam
|
|
,C_ORAKEZDETE
|
|
INTO #megtartottOrak
|
|
FROM T_TANITASIORA_OSSZES tao
|
|
LEFT JOIN T_FOGLALKOZAS_OSSZES fog ON fog.id = tao.C_FOGLALKOZASID
|
|
AND fog.TOROLT = 'F'
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.id = tao.C_TANARID
|
|
AND f.TOROLT = 'F'
|
|
LEFT JOIN T_FELHASZNALO_OSSZES helyettesito ON helyettesito.ID = tao.C_HELYETTESITOTANARID
|
|
INNER JOIN T_TANTARGY_OSSZES tantargy ON tantargy.Id = tao.C_TANTARGYID
|
|
LEFT JOIN (
|
|
SELECT DISTINCT
|
|
OraSorszam
|
|
,TanitasioraId
|
|
FROM #OraSorszam
|
|
) AS oraSorszam ON oraSorszam.TanitasioraId = tao.Id
|
|
WHERE tao.C_OSZTALYCSOPORTID = @csoportid
|
|
AND tao.TOROLT = 'F'
|
|
ORDER BY
|
|
tao.C_ORAKEZDETE
|
|
,tao.C_ORASZAM
|
|
|
|
SELECT
|
|
*
|
|
FROM #megtartottOrak
|
|
|
|
--Napló mulasztás
|
|
SELECT
|
|
C_HETSORSZAMA AS Het
|
|
,C_NYOMTATASINEV AS Tanulo
|
|
,MAX([1]) AS Hetfo
|
|
,MAX([2]) AS Kedd
|
|
,MAX([3]) AS Szerda
|
|
,MAX([4]) AS Csutortok
|
|
,MAX([5]) AS Pentek
|
|
,MAX([6]) AS Szombat
|
|
FROM (
|
|
SELECT
|
|
C_HETSORSZAMA
|
|
,C_NYOMTATASINEV
|
|
,[0]
|
|
,[1]
|
|
,[2]
|
|
,[3]
|
|
,[4]
|
|
,[5]
|
|
,[6]
|
|
FROM (
|
|
SELECT DISTINCT
|
|
n.C_NAPTARINAPID
|
|
,DATEPART(dw, n.C_DATUM) AS NAP
|
|
,t.C_NYOMTATASINEV
|
|
,n.C_HETSORSZAMA
|
|
,'[' + STUFF(
|
|
(
|
|
SELECT
|
|
', ' + CAST(OraSorszam AS varchar(3)) + IIF(tm.C_TIPUS = 1499, '(k)', '')
|
|
FROM T_TANULOMULASZTAS_OSSZES tm
|
|
INNER JOIN T_TANITASIORA_OSSZES o ON o.ID = tm.C_TANITASIORAKID
|
|
LEFT JOIN (
|
|
SELECT DISTINCT
|
|
OraSorszam
|
|
,TanitasioraId
|
|
FROM #OraSorszam
|
|
) AS oraSorszam ON oraSorszam.TanitasioraId = o.Id
|
|
WHERE n.C_NAPTARINAPID = o.C_NAPTARINAPID
|
|
AND m.C_ORATANULOIID = tm.C_ORATANULOIID
|
|
AND tm.C_TANEVID = @tanevId
|
|
AND tm.torolt = 'F'
|
|
AND tm.c_intezmenyId = @intezmenyId
|
|
AND o.c_tanevId = @tanevId
|
|
AND o.C_OSZTALYCSOPORTID = @csoportId
|
|
AND o.TOROLT = 'F'
|
|
ORDER BY
|
|
o.C_NAPTARINAPID
|
|
,o.C_ORASZAM
|
|
FOR XML PATH(''), TYPE
|
|
) .value
|
|
('.', 'varchar(max)'), 1, 2, ''
|
|
) + '] ' + CAST(SUM(IIF(m.C_IGAZOLT = 'T', 1, 0)) AS varchar(2)) + '/' + CAST(SUM(IIF(m.C_IGAZOLT = 'F', 1, 0)) AS varchar(2)) AS hianyzas
|
|
FROM T_TANITASIORA_OSSZES n
|
|
INNER JOIN T_TANULOMULASZTAS_OSSZES m ON m.C_TANITASIORAKID = n.ID
|
|
AND m.torolt = 'F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES t ON t.ID = m.C_ORATANULOIID
|
|
WHERE n.C_TANEVID = @tanevId
|
|
AND m.C_TANEVID = @tanevId
|
|
AND (@iskolaErdekuSzamit = 1 OR C_IGAZOLASTIPUSA <> 1533)
|
|
AND n.C_OSZTALYCSOPORTID = @csoportId
|
|
AND n.TOROLT = 'F'
|
|
GROUP BY
|
|
n.C_NAPTARINAPID
|
|
,C_ORATANULOIID
|
|
,C_NYOMTATASINEV
|
|
,n.C_HETSORSZAMA
|
|
,n.C_DATUM
|
|
) AS SUB PIVOT(MAX(HIANYZAS) FOR NAP IN(
|
|
[0]
|
|
,[1]
|
|
,[2]
|
|
,[3]
|
|
,[4]
|
|
,[5]
|
|
,[6])) AS PivotTable
|
|
) AS ASD
|
|
GROUP BY
|
|
C_HETSORSZAMA
|
|
,C_NYOMTATASINEV
|
|
ORDER BY
|
|
C_HETSORSZAMA
|
|
|
|
--Tanuló feljegyzései
|
|
SELECT
|
|
tte.C_TANULOID AS Tanulo
|
|
,FORMAT(tao.c_datum, 'yyyy.MM.dd.') AS Idopont
|
|
,d.C_NAME AS Tipus
|
|
,te.C_TARTALOM AS Feljegyzes
|
|
,f.C_NYOMTATASINEV AS Feljegyzo
|
|
FROM T_TANULO_TANULOESEMENY tte
|
|
INNER JOIN T_TANULOESEMENY_OSSZES te ON te.Id = tte.C_TANULOESEMENYID
|
|
INNER JOIN T_TANITASIORA_OSSZES tao ON tao.Id = te.C_TANITASIORAID
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.Id = te.C_FELJEGYZOID
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.Id = te.C_TIPUS
|
|
AND d.C_TANEVID = @tanevId
|
|
AND d.TOROLT = 'F'
|
|
WHERE C_OSZTALYCSOPORTID = @csoportId
|
|
AND te.TOROLT = 'F'
|
|
AND C_TIPUS <> 1534
|
|
|
|
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 @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 @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
|
|
|
|
/*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
|