GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_OsztalyNaploErtekelesek] 
	-- Add the parameters for the stored procedure here
	@pOsztalyId int,
	@pFelevVege Date,
	@pFelevi int,
	@pEvvegi int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--IF 1=0 BEGIN
 --      SET FMTONLY OFF
 --    END

	IF OBJECT_ID('tempdb..#TEMPOSZTALYZAT') IS NOT NULL drop table #TEMPOSZTALYZAT
	IF OBJECT_ID('tempdb..#TEMPERTEKELESEK') IS NOT NULL drop table #TEMPERTEKELESEK
	IF OBJECT_ID('tempdb..#TEMPIDOSZAK') IS NOT NULL drop table #TEMPIDOSZAK
	IF OBJECT_ID('tempdb..#tempVaz') IS NOT NULL drop table #tempVaz

	SELECT ID, C_VALUE ERTEK INTO #TEMPOSZTALYZAT FROM T_DICTIONARYITEMBASE
			WHERE C_TYPE IN ('OSZTALYZATTIPUS','MAGATARTASERTEKELESTIPUS','SZORGALOMERTEKELESTIPUS')
	
	create table #TEMPIDOSZAK
	(
	    IDOSZAK int,
	    NEV varchar(20),
		SORREND int
	)INSERT INTO #TEMPIDOSZAK
	  (IDOSZAK, NEV, SORREND)
	VALUES
	  (9, '09.',1),(10, '10.', 2),(11, '11.', 3),(12, '12.', 4),(1, '01(I).', 5)
	  ,(100, 'félév', 6),(2, '01(II).', 7),(3, '02.', 8),(4, '03.', 9),(5, '04.', 10)
	  ,(6, '05.', 11),(7, '06.', 12),(101, 'év vége', 13)
	
	SELECT * INTO #tempVaz FROM (
	select te.TanuloId
		   ,te.TantargyId
		   ,te.TantargyNev
		  ,ti.IDOSZAK
		  ,ti.SORREND
	from (Select foglalkozas.C_TANTARGYID TantargyId
		  	  ,tanulo.C_TANULOID TanuloId
		  	  ,Tantargy.C_NEV TantargyNev
		  From(
		  SELECT [ID]
		  FROM [T_OSZTALYCSOPORT] where [TOROLT] = 'F' And ID = @pOsztalyId
		  Union
		  SELECT [ID]
		  FROM [T_CSOPORT]
		  INNER JOIN T_CSOPORTOK_OSZTALYOK ON T_CSOPORT.ID = T_CSOPORTOK_OSZTALYOK.C_CSOPORTOKID
		   where [TOROLT] ='F' And T_CSOPORTOK_OSZTALYOK.C_OSZTALYOKID = @pOsztalyId) ocs
		  inner join ( SELECT
		        [C_TANTARGYID]
		        ,[C_OSZTALYCSOPORTID]
		  FROM [T_FOGLALKOZAS] where [TOROLT] = 'F') foglalkozas
		  on foglalkozas.C_OSZTALYCSOPORTID = ocs.ID
		  inner join (select
		  			  ID 
		  			,C_NEV				 
		  from T_TANTARGY TARGY) Tantargy ON Tantargy.ID = foglalkozas.C_TANTARGYID	
		  cross join (SELECT distinct 	   
		        [C_TANULOID]
		  FROM [T_TANULOCSOPORT] where [TOROLT] = 'F'
		  and [C_BELEPESDATUM] < GETDATE()
		  and ([C_KILEPESDATUM] is null or [C_KILEPESDATUM] >= GETDATE())
		  and C_OSZTALYCSOPORTID = @pOsztalyId) tanulo )te
		  cross join #TEMPIDOSZAK ti
		  ) tempVaz
	
	SELECT TANULO.ID TANULOID, 
			TANULO.TANULONEV, 
			TANULO.OKTATASIAZONOSITO, 
			YEAR(ERTEKELES.C_ERTEKELESDATUM) EV, 
			CASE WHEN ERTEKELES.C_ERTEKELESTIPUSA = @pFelevi
				THEN 100
				ELSE CASE WHEN ERTEKELES.C_ERTEKELESTIPUSA = @pEvvegi
				THEN 101
				ELSE
				CASE WHEN ERTEKELES.C_ERTEKELESDATUM > @pFelevVege
				   THEN MONTH(ERTEKELES.C_ERTEKELESDATUM) + 1
				   ELSE MONTH(ERTEKELES.C_ERTEKELESDATUM)
				   END END END as IDOSZAK, 
			TARGY.C_NEV TARGYNEV,
			TARGY.ID TARGYID,
			OSZTALYZATTIPUSOK.ERTEK OSZTALYZAT, 
			MAGATARTAS.ERTEK MAGATARTAS, 
			SZORGALOM.ERTEK SZORGALOM, 
			ERTEKELES.C_ERTEKELESTIPUSA ERTEKELESTIPUS
			INTO #TEMPERTEKELESEK
		FROM T_TANULOERTEKELES ERTEKELES
			LEFT JOIN #TEMPOSZTALYZAT OSZTALYZATTIPUSOK ON OSZTALYZATTIPUSOK.ID = ERTEKELES.C_OSZTALYZAT
			LEFT JOIN T_OSZTALYFONOKIERTEKELES OSZTALYFONOKI ON OSZTALYFONOKI.ID = ERTEKELES.ID
			LEFT JOIN #TEMPOSZTALYZAT MAGATARTAS ON MAGATARTAS.ID = OSZTALYFONOKI.C_MAGATARTAS
			LEFT JOIN #TEMPOSZTALYZAT SZORGALOM ON SZORGALOM.ID = OSZTALYFONOKI.C_SZORGALOM		
			JOIN T_TANTARGY TARGY ON TARGY.ID = ERTEKELES.C_TANTARGYID	
			JOIN ( SELECT ID, C_NYOMTATASINEV TANULONEV, C_OKTATASIAZONOSITO OKTATASIAZONOSITO FROM T_FELHASZNALO WHERE TOROLT='F') TANULO ON TANULO.ID = ERTEKELES.C_TANULOID
		WHERE EXISTS 
		( 
		SELECT * FROM T_TANULOCSOPORT WHERE 
		T_TANULOCSOPORT.C_TANULOID = ERTEKELES.C_TANULOID 
		AND T_TANULOCSOPORT.C_OSZTALYCSOPORTID = @pOsztalyId 
		AND T_TANULOCSOPORT.TOROLT='F'
		AND T_TANULOCSOPORT.C_BELEPESDATUM < GETDATE()
		AND (T_TANULOCSOPORT.C_KILEPESDATUM is null or T_TANULOCSOPORT.C_KILEPESDATUM >= GETDATE())
		)
			AND ERTEKELES.TOROLT='F'
			AND (ERTEKELES.C_OSZTALYZAT IS NOT NULL OR OSZTALYFONOKI.C_MAGATARTAS IS NOT NULL OR OSZTALYFONOKI.C_SZORGALOM IS NOT NULL)
		ORDER BY TANULO.TANULONEV, TANULO.OKTATASIAZONOSITO , ERTEKELES.C_TANULOID, YEAR(ERTEKELES.C_ERTEKELESDATUM), MONTH(ERTEKELES.C_ERTEKELESDATUM),TARGY.C_NEV 
		
	SELECT  distinct		
		 tempIdoszak.TanuloId
		,tempIdoszak.TantargyNev
		,tempIdoszak.Sorrend as Idoszak
		,RESULTS.Osztalyzatok
	FROM (select 
			CASE WHEN  t1.Osztalyzat is null
				THEN 
					CASE WHEN t1.Magatartas is not null OR t1.Szorgalom is not null
	                        THEN ISNULL( CAST(Avg(t1.Magatartas)  AS varchar(1)),'0')+ '/' + ISNULL( CAST(Avg(t1.Szorgalom) AS varchar(1)),'0')
	                        ELSE null
					End
				ELSE STUFF(
	             (SELECT ', ' + CAST(t2.Osztalyzat AS varchar(1))
	              FROM #TEMPERTEKELESEK t2
	              where t1.TanuloId = t2.TanuloId And t1.Idoszak = t2.Idoszak And t1.ErtekelesTipus = 1518 And t2.ErtekelesTipus = 1518 And t1.TargyNev = t2.TargyNev
	              FOR XML PATH (''))
	              , 1, 1, '')
			END  AS Osztalyzatok,
	      		t1.Idoszak,      		
				t1.TargyNev,
				t1.TARGYID,
				t1.TANULOID
	      from #TEMPERTEKELESEK t1
		  Group by t1.Idoszak, t1.TargyNev,t1.TARGYID, t1.TANULOID ,t1.ErtekelesTipus, t1.OSZTALYZAT ,t1.MAGATARTAS, t1.SZORGALOM
		  ) RESULTS
	right join #tempVaz tempIdoszak
	on tempIdoszak.TanuloId = RESULTS.TANULOID And tempIdoszak.IDOSZAK = RESULTS.IDOSZAK And tempIdoszak.TantargyId = RESULTS.TARGYID
	order by tempIdoszak.TanuloID, tempIdoszak.TantargyNev , tempIdoszak.Sorrend
	
	IF OBJECT_ID('tempdb..#TEMPOSZTALYZAT') IS NOT NULL drop table #TEMPOSZTALYZAT
	IF OBJECT_ID('tempdb..#TEMPERTEKELESEK') IS NOT NULL drop table #TEMPERTEKELESEK
	IF OBJECT_ID('tempdb..#TEMPIDOSZAK') IS NOT NULL drop table #TEMPIDOSZAK
	IF OBJECT_ID('tempdb..#tempVaz') IS NOT NULL drop table #tempVaz
END