SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('sp_GetPedagogusStatuszRiport', 'P') IS NOT NULL BEGIN
  DROP PROCEDURE sp_GetPedagogusStatuszRiport
END
GO

CREATE PROCEDURE [dbo].[sp_GetPedagogusStatuszRiport]
	@fenntartoId nvarchar(max) = null,
	@intezmenyId int = null,
	@tanevId int = null,
	@statuszSzam int = null,
	@tanarId int = null
AS
BEGIN
	SET NOCOUNT ON;
	select
	FE.ID,
	I.ID IntId,
	F.C_TANEVID TanevId,
	FE.C_OKTATASIAZONOSITO OktAzon,
	FE.C_NYOMTATASINEV TanarNev,
	STATUSZ.KotelezoOraszam KotelezoOraszam,
	HETI.HetiOraszam HetiOraszam,
	HETI.MunkaidoKedvezmeny MunkaidoKedvezmeny,
	SUM(F.C_ORASZAM) OsszesOraszam,
	cast(STATUSZ.StatuszSzam as decimal(10,2)) StatuszSzam,
	cast(TANORAI.Ossz as decimal(10,2)) TanoraiOraszam,
	cast(SUM(F.C_ORASZAM) - TANORAI.Ossz as decimal(10,2)) NemTanoraiOraszam
	from T_FOGLALKOZAS F
	inner join T_TANTARGY TT on TT.ID = F.C_TANTARGYID and TT.TOROLT = 'F'
	inner join T_FELHASZNALO FE on FE.ID = F.C_TANARID and FE.TOROLT = 'F'
	inner join T_INTEZMENYADATOK IA on IA.C_TANEVID = F.C_TANEVID and IA.C_INTEZMENYID = F.C_INTEZMENYID and IA.TOROLT = 'F'
	inner join T_INTEZMENY I on I.ID = IA.C_INTEZMENYID and I.TOROLT = 'F'
	inner join 
	(
	select distinct
	MU.C_ALKALMAZOTTID ID,
	isnull(MU.C_MUNKAIDOKEDVEZMENYORASZAM, 0) MunkaidoKedvezmeny,
	MU.C_KOTELEZOORASZAM - isnull(MU.C_MUNKAIDOKEDVEZMENYORASZAM, 0) HetiOraszam
	from T_MUNKAUGYIADATOK MU
	where MU.TOROLT = 'F'
	) HETI
	on HETI.ID = FE.ID
	inner join
	(select
	T_FOGLALKOZAS.C_INTEZMENYID IntId,
	T_FOGLALKOZAS.C_TANEVID TanevId,
	T_FOGLALKOZAS.C_TANARID TanarId,
	MunkaU.Kotelezo KotelezoOraszam,
	IIF(SUM(T_FOGLALKOZAS.C_ORASZAM) < Kotelezo - Kedvezmeny,
	 CAST(SUM(T_FOGLALKOZAS.C_ORASZAM) AS DECIMAL) / CAST((Kotelezo - Kedvezmeny) AS DECIMAL),	 
	 1 + (cast((SUM(T_FOGLALKOZAS.C_ORASZAM) - (Kotelezo - Kedvezmeny)) as decimal) / @statuszSzam))  StatuszSzam
	from T_FOGLALKOZAS
    INNER JOIN T_OSZTALYCSOPORT_OSSZES ON T_FOGLALKOZAS.C_OSZTALYCSOPORTID = T_OSZTALYCSOPORT_OSSZES.ID
	inner join T_ALKALMAZOTT on T_ALKALMAZOTT.ID = T_FOGLALKOZAS.C_TANARID and T_ALKALMAZOTT.TOROLT = 'F'
	inner join T_MUNKAUGYIADATOK on T_MUNKAUGYIADATOK.C_ALKALMAZOTTID = T_ALKALMAZOTT.ID and T_MUNKAUGYIADATOK.TOROLT ='F'
	INNER JOIN (SELECT C_ALKALMAZOTTID, C_KOTELEZOORASZAM Kotelezo, IIF(C_MUNKAIDOKEDVEZMENYORASZAM IS NULL, 0, C_MUNKAIDOKEDVEZMENYORASZAM) Kedvezmeny FROM T_MUNKAUGYIADATOK
	WHERE TOROLT = 'F' and C_KOTELEZOORASZAM > 0 /*and C_KOTELEZOORASZAM - isnull(C_MUNKAIDOKEDVEZMENYORASZAM, 0) > 0*/	
	) MunkaU  ON 
		MunkaU.C_ALKALMAZOTTID = T_ALKALMAZOTT.ID
	where T_FOGLALKOZAS.TOROLT = 'F'
    AND T_OSZTALYCSOPORT_OSSZES.TOROLT='F'
	and T_MUNKAUGYIADATOK.C_KOTELEZOORASZAM is not null
	group by T_FOGLALKOZAS.C_INTEZMENYID, T_FOGLALKOZAS.C_TANEVID,T_FOGLALKOZAS.C_TANARID, MunkaU.Kotelezo,MunkaU.Kedvezmeny
	) STATUSZ
	on STATUSZ.IntId = I.ID and STATUSZ.TanevId = F.C_TANEVID and STATUSZ.TanarId = FE.ID
	inner join
	(
	select
	F.C_INTEZMENYID IntId,
	F.C_TANEVID TanevId,
	F.C_TANARID TanarId,
	isnull(OSZTCSOP.ORASZAM, 0) Ossz
	from T_FOGLALKOZAS F
	left join
	(
	select
	F.C_TANARID TanarId,
	SUM(F.C_ORASZAM) OraSzam
	from T_FOGLALKOZAS F
	inner join T_OSZTALY O on O.ID = F.C_OSZTALYCSOPORTID and O.TOROLT = 'F'
	where F.TOROLT = 'F'
	group by F.C_TANARID
	union all
	select
	F.C_TANARID TanarId,
	SUM(F.C_ORASZAM) OraSzam
	from T_FOGLALKOZAS F
	inner join T_CSOPORT CS on CS.ID = F.C_OSZTALYCSOPORTID and CS.TOROLT = 'F'
	where F.TOROLT = 'F'
	and CS.C_TIPUSA = 1031
	group by F.C_TANARID
	) OSZTCSOP
	on OSZTCSOP.TanarId = F.C_TANARID
	where F.TOROLT = 'F'
	group by F.C_INTEZMENYID, F.C_TANEVID,F.C_TANARID, OSZTCSOP.ORASZAM
	)TANORAI
	on TANORAI.IntId = I.ID and TANORAI.TanevId = F.C_TANEVID and TANORAI.TanarId = FE.ID
	where F.TOROLT = 'F' 
	and I.C_FENNTARTOAZONOSITO =  @fenntartoId
	and (I.ID = @intezmenyId or @intezmenyId is null)
	and (F.C_TANEVID = @tanevId or @tanevId is null)
	and (FE.ID = @tanarId or @tanarId is null)
	group by FE.ID, I.ID, F.C_TANEVID, FE.C_OKTATASIAZONOSITO, FE.C_NYOMTATASINEV, STATUSZ.KotelezoOraszam, HETI.HetiOraszam, HETI.MunkaidoKedvezmeny, STATUSZ.StatuszSzam, TANORAI.Ossz
END