328 lines
10 KiB
Transact-SQL
328 lines
10 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
|
|
,dbo.fnGetDokumentumDatumFormatum(cs.C_CSOPORTNAPLOMEGNYITASA) Megnyitas
|
|
,ISNULL(dbo.fnGetDokumentumDatumFormatum(cs.C_CSOPORTNAPLOZARASA),'-') 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
|
|
|
|
CREATE TABLE #students (ID INT PRIMARY KEY, BelepesDatum DATE, KilepesDatum DATE, NaploSorszam INT, Osztaly NVARCHAR(MAX))
|
|
INSERT INTO #students
|
|
SELECT
|
|
TanuloId
|
|
,BelepesDatum
|
|
,KilepesDatum
|
|
,NaploSorszam
|
|
,dbo.fnGetDokumentumTanuloAktualisOsztaly(TanuloId) Osztaly
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @csoportId, 'T')
|
|
|
|
--Osztaly (Csoport tanulóinak osztályai)
|
|
SELECT
|
|
COUNT(Id) Tanulo
|
|
,Osztaly
|
|
FROM #students
|
|
GROUP BY Osztaly
|
|
|
|
--Napló
|
|
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 @letszam INT=(SELECT COUNT(tcs.C_TANULOID) FROM T_TANULOCSOPORT_OSSZES tcs WHERE tcs.C_OSZTALYCSOPORTID=@csoportId AND tcs.TOROLT='F')
|
|
|
|
SELECT
|
|
dbo.fnGetDokumentumDatumFormatum(tao.C_ORAKEZDETE) + CONVERT(VARCHAR(5), tao.C_ORAKEZDETE, 108) + '-' + CONVERT(VARCHAR(5), tao.C_ORAVEGE, 108) Datum
|
|
,IIF(tantargy.C_NEV <> C_NEVNYOMTATVANYBAN AND C_NEVNYOMTATVANYBAN IS NOT NULL, C_NEVNYOMTATVANYBAN --
|
|
+ ' (' + tantargy.C_NEV + ')', tantargy.C_NEV) TantargyNev
|
|
,tao.C_TANTARGYID TantargyId
|
|
,tao.C_MEGTARTOTT Megtartott
|
|
,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
|
|
INTO #megtartottOrak
|
|
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
|
|
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'
|
|
ORDER BY tao.C_ORAKEZDETE
|
|
|
|
SELECT * FROM #megtartottOrak
|
|
|
|
/*TANULÓ adatai*/
|
|
SELECT
|
|
TanuloId
|
|
,Sorszam
|
|
,TorzslapSzam
|
|
,OktAzon
|
|
,TajSzam
|
|
,AnyjaNeve
|
|
,SzulHely
|
|
,SzulIdo
|
|
,SocAdat
|
|
,Kepzes
|
|
,TanuloNeve
|
|
,Allampolgarsaga
|
|
,DiakIgazolvanySzam
|
|
,KilepesDatum
|
|
,BeirasiNaploSorszam
|
|
,AktualisOsztaly Osztalya
|
|
FROM fnGetDokumentumTanulokAdatai(@tanevId, @csoportId)
|
|
ORDER BY TanuloNeve
|
|
|
|
/*TANULÓ elérhetőségei */
|
|
DECLARE @Gondviselok TABLE (TanuloId INT, GondviseloId INT)
|
|
INSERT INTO @Gondviselok
|
|
SELECT
|
|
TanuloId
|
|
,GondviseloId
|
|
FROM fnGetDokumentumGondviselok (@csoportId, @tanevId, 'F', 'T')
|
|
|
|
|
|
DECLARE @TanuloCimTable TABLE (FelhasznaloId INT, Cim NVARCHAR(MAX), Cimtipusa NVARCHAR(MAX))
|
|
INSERT INTO @TanuloCimTable
|
|
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
|
|
|
|
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 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
|
|
|
|
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
|
|
Gondviselok.TanuloId
|
|
,Gondviselok.GondviseloNev + ' (' + RokonsagiFok.C_NAME + ')' C_NEV
|
|
,cim.Cimtipusa C_CIMTIPUSA
|
|
,cim.cim C_OSSZETETTCIM
|
|
,telefon.telefon C_TELEFONSZAM
|
|
,email.emailcim 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
|
|
|
|
--Megtartott órák száma
|
|
SELECT
|
|
COUNT(TantargyId) TantargyOraszam
|
|
,Tantargynev TantargyNev
|
|
INTO #MegtartottOrakSzama
|
|
FROM #megtartottOrak
|
|
WHERE Megtartott = 'T'
|
|
GROUP BY TantargyId, TantargyNev
|
|
|
|
|
|
SELECT DISTINCT
|
|
SUM(tmp.TantargyOraszam) OsszesTantargySzam
|
|
,STUFF((
|
|
SELECT DISTINCT ', ' + btmp.TantargyNev + '(' + CAST(btmp.TantargyOraszam AS NVARCHAR(MAX)) + ')'
|
|
FROM #MegtartottOrakSzama btmp
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,2,'') Tantargyak
|
|
FROM #MegtartottOrakSzama tmp
|
|
|
|
END
|
|
|
|
GO |