200 lines
14 KiB
Transact-SQL
200 lines
14 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 as IntezmenyNev, convert(nvarchar(5), ia.c_iranyitoszam)+', '+ia.c_varos+', '+ia.c_cime as IntezmenyCim, ia.c_omkod as OMKOD, ia.c_igazgatoneve as IntezmenyVezeto, ocs.c_nev Csoport,
|
|
isnull(f.c_nyomtatasinev,'-') as CsoportVezeto, isnull(cs.c_csoportnaploleiras,'-') as CsoportNaploLeiras, FORMAT(cs.c_csoportnaplomegnyitasa, 'yyyy. MMMM dd.', 'hu-hu') as Megnyitas,
|
|
isnull(FORMAT(cs.c_csoportnaplozarasa, 'yyyy. MMMM dd.', 'hu-hu'),'-') as Lezaras, t.c_nev as Tanev, cs.c_csoportnaploleiras as 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 i.id=@intezmenyid and ia.c_tanevid=@tanevid and ocs.id=@csoportId
|
|
|
|
--Osztaly (Csoport tanulóinak osztályai)
|
|
select Osztaly.Osztaly Osztaly, count(c_tanuloid) Tanulo from t_tanulocsoport tcs
|
|
inner join (select tcs.c_tanuloid Tanulo, ocs.c_nev as Osztaly, tcs.c_kilepesdatum Kilepes from T_TANULOCSOPORT_OSSZES tcs
|
|
inner join T_OSZTALY_OSSZES o on o.id=tcs.c_osztalycsoportid and o.torolt='F'
|
|
inner join T_OSZTALYCSOPORT_OSSZES ocs on o.id=ocs.id
|
|
where tcs.TOROLT='F') osztaly on osztaly.Tanulo=tcs.c_tanuloid
|
|
where tcs.c_osztalycsoportid=@csoportId and tcs.TOROLT='F' and tcs.c_kilepesdatum is null
|
|
group by osztaly.Osztaly
|
|
|
|
--Mulasztasok
|
|
declare @datum date=getdate()
|
|
create table #OraSorszam (TanitasioraId int, OraSorszam int)
|
|
insert into #OraSorszam
|
|
EXEC [sp_GetOraSorszamByOsztaly]
|
|
@osztalyCsoportId = @csoportId,
|
|
@datum = @datum,
|
|
@intezmenyId = @intezmenyId,
|
|
@tanevId = @tanevId
|
|
|
|
DECLARE @tanulo TABLE (Tanulo int, Osztaly nvarchar(max))
|
|
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')
|
|
|
|
insert into @tanulo
|
|
select f.id as Tanulo, Osztaly.Osztaly Osztaly from T_TANULOCSOPORT_OSSZES tcs
|
|
inner join (select tcs.c_tanuloid Tanulo, ocs.c_nev Osztaly from T_TANULOCSOPORT_OSSZES tcs
|
|
inner join T_OSZTALY_OSSZES o on o.id=tcs.c_osztalycsoportid and o.torolt='F'
|
|
inner join T_OSZTALYCSOPORT_OSSZES ocs on ocs.id=tcs.c_osztalycsoportid --and ocs.c_intezmenyid=@intezmenyid and ocs.c_tanevid=@tanevid
|
|
where tcs.c_kilepesdatum is null and tcs.c_tanevid=@tanevid) Osztaly on Osztaly.Tanulo=tcs.c_tanuloid
|
|
inner join t_felhasznalo f on f.id=tcs.c_tanuloid
|
|
where tcs.c_osztalycsoportid=@csoportId and tcs.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_intezmenyid=@intezmenyid
|
|
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 @tanulo 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.Tanulo
|
|
|
|
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.Tanulo
|
|
|
|
inner join T_FELHASZNALO_OSSZES f on f.id=tanulo.Tanulo
|
|
left join (select Tanulo, count(Csoport) as Osszes from @mulasztas m where Felev=1 group by Tanulo) osszesM on osszesM.Tanulo=tanulo.Tanulo
|
|
left join (select Tanulo, count(Csoport) as Osszes from @mulasztas m where Felev=2 group by Tanulo) osszesM2 on osszesM2.Tanulo=tanulo.Tanulo
|
|
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' and tcs.c_kilepesdatum is null)
|
|
|
|
select convert(nvarchar(max),datepart(year,tao.c_orakezdete))+'. '+convert(nvarchar(max),datepart(month,tao.c_orakezdete))+'. '+convert(nvarchar(max),datepart(day,tao.c_orakezdete))+'.' as Datum,
|
|
tao.c_tema as Tema, @letszam-isnull(hianyzo.Hianyzok,0) as Letszam, f.c_nyomtatasinev as Pedagogus from T_TANITASIORA_OSSZES tao
|
|
inner join T_FOGLALKOZAS_OSSZES fog on fog.id=tao.c_foglalkozasid
|
|
inner join T_FOGLALKOZASOK_TANAROK ft on ft.c_foglalkozasokid=fog.id
|
|
left join (select tm.c_tanitasiorakid as Tanitasiora, count(tm.c_oratanuloiid) Hianyzok from T_TANULOMULASZTAS_OSSZES tm where tm.torolt='F' group by tm.c_tanitasiorakid ) hianyzo on hianyzo.Tanitasiora=tao.id
|
|
inner join T_FELHASZNALO_OSSZES f on f.id=iif(tao.c_helyettesitotanarid is not null,tao.c_helyettesitotanarid, ft.c_tanarokid)
|
|
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, Osztaly, datepart(month,tao.c_datum) as Honap, tm.c_igazolt as IgazoltE from @tanulo tanulo
|
|
left join T_TANULOMULASZTAS_OSSZES tm on tm.c_oratanuloiid=tanulo.Tanulo and tm.torolt='F' and tm.c_tanevid=@tanevid and tm.c_intezmenyid=@intezmenyid
|
|
left join T_TANITASIORA_OSSZES tao on tao.id=tm.c_tanitasiorakid and tao.c_tanevid=@tanevid and tao.c_intezmenyid=@intezmenyid
|
|
where
|
|
tao.c_osztalycsoportid=@csoportId
|
|
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 @tanulo 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.Tanulo=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.Tanulo=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.Tanulo
|
|
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.Tanulo
|
|
inner join t_felhasznalo f on f.id=tanulo.Tanulo
|
|
|
|
--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) as Honap, tm.c_igazolt as IgazoltE from @tanulo tanulo
|
|
left join T_TANULOMULASZTAS_OSSZES tm on tm.c_oratanuloiid=tanulo.Tanulo and tm.torolt='F' and tm.c_tanevid=@tanevid --and tm.c_intezmenyid=@intezmenyid
|
|
left join T_TANITASIORA_OSSZES tao on tao.id=tm.c_tanitasiorakid and tao.c_tanevid=@tanevid --and tao.c_intezmenyid=@intezmenyid
|
|
where
|
|
tao.c_osztalycsoportid=@csoportId
|
|
and tao.torolt='F'
|
|
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 as Id, count(Tanulo) as 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 as 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 as Id, count(IgazoltE) Osszes from @hianyzasOssz where IgazoltE='F') nioo on nioo.Id=ioo.Csoport
|
|
|
|
END
|
|
|
|
GO |