SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_GetOsztalyElsoNegyedeviTantargyiStatisztika]
  @tanevId int,
  @intezmenyId int

AS
BEGIN
  SET NOCOUNT ON;

declare @kezdodatum as date=(select distinct tr.c_datum from t_tanevrendje tr where c_naptipusa=1394 and tr.torolt='F' and tr.c_intezmenyid=@intezmenyid and tr.c_tanevid=@tanevid);
declare @vegdatum as date=(select distinct tr.c_datum from t_tanevrendje tr where c_naptipusa=1403 and tr.torolt='F' and tr.c_intezmenyid=@intezmenyid and tr.c_tanevid=@tanevid);

--Osztályok
create table #osztaly (Id int, Nev nvarchar(max), Letszam int, rendez nvarchar(max))
insert into #osztaly 
	select o.id as Id, ocs.c_nev as Nev, count(tcs.c_tanuloid) as Letszam, ocs.c_nev as rendez from t_osztaly o
		inner join t_osztalycsoport ocs on o.id=ocs.id and ocs.c_intezmenyid=@intezmenyId and ocs.c_tanevid=@tanevId and ocs.torolt='F' 
		left join t_tanulocsoport tcs on tcs.c_osztalycsoportid=o.id and tcs.c_intezmenyid=@intezmenyId and tcs.c_tanevid=@tanevId and tcs.torolt='F' 
	where o.c_alintezmenyid=@intezmenyId and o.c_altanevid=@tanevId and o.torolt='F' 
	group by o.id, ocs.c_nev

--Osztályok, összesen
insert into #osztaly
	select Id, Nev, sum(Letszam) as Letszam, rendez from(
		select -1 as Id, 'Összesen' as Nev, count(tcs.c_tanuloid) as Letszam, 'zzz' as rendez from t_osztaly o
			inner join t_osztalycsoport ocs on o.id=ocs.id and ocs.c_intezmenyid=@intezmenyId and ocs.c_tanevid=@tanevId and ocs.torolt='F' 
			left join t_tanulocsoport tcs on tcs.c_osztalycsoportid=o.id and tcs.c_intezmenyid=@intezmenyId and tcs.c_tanevid=@tanevId and tcs.torolt='F' 
		where o.c_alintezmenyid=@intezmenyId and o.c_altanevid=@tanevId and o.torolt='F' 
		group by o.id
	)a
	group by Id, Nev, rendez

--Értékelések
create table #ertekeles(Osztaly int, Tantargy int, Atlag float)
insert into #ertekeles
			select te.c_osztalycsoportid as Osztaly, te.c_tantargyid as Tantargy, round(avg(convert(float,d.c_value)),2) as Atlag from t_tanuloertekeles te
				inner join t_osztaly o on o.id=te.c_osztalycsoportid and o.c_alintezmenyid=@intezmenyId and o.c_altanevid=@tanevId and o.torolt='F' 
				inner join (select d.id, d.c_value from t_dictionaryitembase d) d on d.id=te.c_osztalyzat
			where te.torolt='F' and te.c_tanevid=@tanevid and te.c_intezmenyid=@intezmenyid
					and convert(date,te.c_ertekelesdatum)>@kezdodatum and convert(date,te.c_ertekelesdatum)<@vegdatum and te.c_tantargyid is not null
			group by te.c_osztalycsoportid, te.c_tantargyid

--Értékelés, összegzés
insert into #ertekeles	
			select Osztaly, Tantargy, round(avg(convert(float,Atlag)),2) from (
				select -1 as Osztaly, te.c_tantargyid as Tantargy, round(avg(convert(float,d.c_value)),2) as Atlag from t_tanuloertekeles te
					inner join t_osztaly o on o.id=te.c_osztalycsoportid and o.c_alintezmenyid=@intezmenyId and o.c_altanevid=@tanevId and o.torolt='F' 
					inner join (select d.id, d.c_value from t_dictionaryitembase d) d on d.id=te.c_osztalyzat
				where te.c_tantargyid is not null and te.torolt='F' and te.c_tanevid=@tanevid and te.c_intezmenyid=@intezmenyid
					and convert(date,te.c_ertekelesdatum)>@kezdodatum and convert(date,te.c_ertekelesdatum)<@vegdatum
				group by te.c_osztalycsoportid, te.c_tantargyid)a
			group by Osztaly, tantargy

--Osztályátlag
create table #osztalyavg(Osztaly int, Atlag float)
insert into #osztalyavg
			select te.c_osztalycsoportid as Osztaly, round(avg(convert(float,d.c_value)),2) as Atlag from t_tanuloertekeles te
				inner join t_osztaly o on o.id=te.c_osztalycsoportid and o.c_alintezmenyid=@intezmenyId and o.c_altanevid=@tanevId and o.torolt='F'  
				inner join (select d.id, d.c_value from t_dictionaryitembase d) d on d.id=te.c_osztalyzat
			where te.c_tantargyid is not null and te.torolt='F' and te.c_tanevid=@tanevid and te.c_intezmenyid=@intezmenyid
					and convert(date,te.c_ertekelesdatum)>@kezdodatum and convert(date,te.c_ertekelesdatum)<@vegdatum
			group by te.c_osztalycsoportid

--Mulasztás
declare @mulasztas table(Osztaly int, MulasztasTipus int, IgazoltE nvarchar(1), Keses int)
insert into @mulasztas
	select Id, mulasztas.MulasztasTipus, mulasztas.IgazoltE, mulasztas.Keses from #osztaly o
		left join (select o.id as Osztaly, tm.c_tipus as MulasztasTipus, tm.c_igazolt as IgazoltE, tm.c_kesespercben as Keses from t_tanulomulasztas tm
						inner join t_tanulocsoport tcs on tcs.c_tanuloid=tm.c_oratanuloiid and tcs.c_intezmenyid=@intezmenyId and tcs.c_tanevid=@tanevId and tcs.torolt='F' 
						inner join t_osztaly o on o.id=tcs.c_osztalycsoportid and o.c_alintezmenyid=@intezmenyId and o.c_altanevid=@tanevId and o.torolt='F' 
						inner join t_tanitasiora tao on tao.id=tm.c_tanitasiorakid and tao.torolt='F' and tao.torolt='F' and tao.c_tanevid=@tanevid and tao.c_intezmenyid=@intezmenyid and convert(date,tao.c_datum)>@kezdodatum and convert(date,tao.c_datum)<@vegdatum 
					where tm.c_igazolt in ('T', 'F') 
					) mulasztas on mulasztas.Osztaly=o.id

create table #mulasztastmp(Osztaly int, ih float, nih float, ik float, nik float)
insert into #mulasztastmp
	select distinct o.Id, round((convert(float,ih.ih)/convert(float, o.letszam)),2) as ih, round((convert(float,nih.nih)/convert(float, o.letszam)),2) as nih, round((convert(float,ik.ik)/convert(float, o.letszam)),2) as ik, round((convert(float,nik.nik)/convert(float, o.letszam)),2) as nik from #osztaly o
		left join (select m.Osztaly, count(m.Mulasztastipus) as ih from @mulasztas m where m.IgazoltE='T' and m.MulasztasTipus=1500 group by m.Osztaly) ih on ih.osztaly=o.id
		left join (select m.Osztaly, count(m.Mulasztastipus) as nih from @mulasztas m where m.IgazoltE='F' and m.MulasztasTipus=1500 group by m.Osztaly) nih on nih.osztaly=o.id
		left join (select m.Osztaly, count(m.Mulasztastipus) as ik from @mulasztas m where m.IgazoltE='T' and m.MulasztasTipus=1499 group by m.Osztaly) ik on ik.osztaly=o.id
		left join (select m.Osztaly, count(m.Mulasztastipus) as nik from @mulasztas m where m.IgazoltE='F' and m.MulasztasTipus=1499 group by m.Osztaly) nik on ik.osztaly=o.id

declare @cols as nvarchar(max),
		@colsIn as nvarchar(max),
		@query as nvarchar(4000);

select @cols= isnull(@cols + ',','') +quotename(Tantargy) + ' as '+quotename(nev),
	   @colsIn= isnull(@colsIn + ',','') +quotename(Tantargy)
	from (select distinct t.id as Tantargy, t.c_nev as nev from t_foglalkozas f 
		  inner join t_tantargy t on f.c_tantargyid=t.id) Tantargy

set @query='
			select  Nev AS COLUMN5286, Letszam AS COLUMN3847, '+@cols+', oavg.Atlag as COLUMN1817, 
					mtmp.ih AS COLUMN5291, mtmp.nih AS COLUMN5292, mtmp.ik AS COLUMN5293, mtmp.nik AS COLUMN5294 
			from #osztaly o
				left join (select * from #ertekeles pivot (max(Atlag) for Tantargy in ('+@colsIn+'))pv) ertekeles on ertekeles.Osztaly=o.Id
				left join #osztalyavg oavg on oavg.Osztaly=o.id
				left join #mulasztastmp mtmp on mtmp.Osztaly=o.id
			order by rendez
			'
EXEC sp_executesql @query

drop table #mulasztastmp
drop table #osztalyavg
drop table #ertekeles
drop table #osztaly

End


GO