﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[sp_GetPedagogusAltalAdottErtekelesek]') IS NOT NULL 
BEGIN
  DROP PROCEDURE [dbo].[sp_GetPedagogusAltalAdottErtekelesek]
END
GO

-- ===================================================================
-- Javított: Badits-Nyáradi József 2017.05.19.
-- ===================================================================
CREATE PROCEDURE [dbo].[sp_GetPedagogusAltalAdottErtekelesek]
	@pTanarId INT,
	@pEvkozi INT,
	@pFelevi INT,
	@pEvvegi INT,
	@pTanevId INT,
    @pFelevVegeNaptipusId INT
AS
BEGIN
	DECLARE @osztalyId  INT = 0;
	DECLARE @tantargyId INT = 0;
	DECLARE @rowId INT = 0;
  DECLARE @maxCharNumErtekelesSzoveg INT = 20;

  SET NOCOUNT ON;

  DECLARE @felevVege DATE = (
    SELECT COALESCE(
      (SELECT C_DATUM FROM T_TANEVRENDJE WHERE C_TANEVID = @pTanevId AND C_NAPTIPUSA = @pFelevVegeNaptipusId),
      GETDATE())
  );

	SELECT DISTINCT	T_FOGLALKOZAS_OSSZES.ID, T_TANTARGY_OSSZES.ID AS TANTARGYID, T_OSZTALYCSOPORT_OSSZES.ID AS OSZTALYID, T_OSZTALYCSOPORT_OSSZES.C_NEV + '_' + T_TANTARGY_OSSZES.C_NEV AS NEV
  INTO #tempFoglalkozasok
	FROM T_FOGLALKOZAS_OSSZES
	INNER JOIN T_OSZTALYCSOPORT_OSSZES ON T_OSZTALYCSOPORT_OSSZES.ID = T_FOGLALKOZAS_OSSZES.C_OSZTALYCSOPORTID
	INNER JOIN T_TANTARGY_OSSZES ON T_TANTARGY_OSSZES.ID = T_FOGLALKOZAS_OSSZES.C_TANTARGYID 
	INNER JOIN T_FOGLALKOZASOK_TANAROK ON T_FOGLALKOZASOK_TANAROK.C_FOGLALKOZASOKID = T_FOGLALKOZAS_OSSZES.ID
	LEFT JOIN T_OSZTALY_OSSZES ON T_OSZTALY_OSSZES.ID = T_OSZTALYCSOPORT_OSSZES.ID AND T_OSZTALY_OSSZES.TOROLT = 'F'
	LEFT JOIN T_CSOPORT_OSSZES ON T_CSOPORT_OSSZES.C_OSZTALYBONTASID = T_OSZTALY_OSSZES.ID AND T_CSOPORT_OSSZES.TOROLT = 'F'
	WHERE T_FOGLALKOZAS_OSSZES.TOROLT = 'F' AND T_TANTARGY_OSSZES.TOROLT = 'F' AND T_OSZTALYCSOPORT_OSSZES.TOROLT = 'F' AND 
    T_FOGLALKOZASOK_TANAROK.C_TANAROKID = @pTanarId AND T_FOGLALKOZAS_OSSZES.C_TANEVID = @pTanevId;

	DECLARE @tempErtekeles TABLE (ID INT,	TANULOID INT, NEV NVARCHAR(255), C_BELEPESDATUM NVARCHAR(15), C_KILEPESDATUM NVARCHAR(15), OSZTID INT, ERTEKELESDATUMA NVARCHAR(255),	ERTEKELESTIPUSA INT, ERTEKELES NVARCHAR(MAX), OSZTALYZAT INT, HONAP INT);

	SELECT * FROM #tempFoglalkozasok 
  WHILE (1 = 1) 
  BEGIN
  	DELETE FROM @tempErtekeles;

	  SELECT TOP 1 @rowId = ID, @tantargyId = TANTARGYID, @osztalyId = OSZTALYID 
    FROM #tempFoglalkozasok
    WHERE ID > @rowId 
    ORDER BY ID;
		
    IF @@ROWCOUNT = 0 
		BEGIN
			BREAK;
		END

		INSERT INTO @tempErtekeles
		SELECT *
		FROM (
		  SELECT DISTINCT	T_TANULOERTEKELES_OSSZES.ID, T_TANULOCSOPORT_OSSZES.C_TANULOID AS TANULOID, T_FELHASZNALO_OSSZES.C_NYOMTATASINEV AS NEV, COALESCE(FORMAT(C_BELEPESDATUM, 'yyyy. MM. dd'), '') AS C_BELEPESDATUM, COALESCE(FORMAT(C_KILEPESDATUM, 'yyyy. MM. dd'), '') AS C_KILEPESDATUM, 
        T_TANULOCSOPORT_OSSZES.C_OSZTALYCSOPORTID AS OSZTID, 
        T_TANULOERTEKELES_OSSZES.C_ERTEKELESDATUM AS ERTEKELESDATUMA, T_TANULOERTEKELES_OSSZES.C_ERTEKELESTIPUSA AS ERTEKELESTIPUSA, 
      COALESCE(CONVERT(NVARCHAR, TDIB_JEGY.C_VALUE), (CASE WHEN C_ERTEKELESSZOVEG IS NOT NULL THEN '[' + CASE WHEN LEN(C_ERTEKELESSZOVEG) >  @maxCharNumErtekelesSzoveg THEN STUFF(C_ERTEKELESSZOVEG, @maxCharNumErtekelesSzoveg, 1000000, '...') ELSE C_ERTEKELESSZOVEG END  + ']'  ELSE NULL END), CONVERT(NVARCHAR, C_SZAZALEK) + '%') AS ERTEKELES, 
      TDIB_JEGY.C_VALUE AS OSZTALYZAT, (CASE WHEN MONTH(C_ERTEKELESDATUM) >= 9 THEN 100 WHEN MONTH(C_ERTEKELESDATUM) > 1 AND MONTH(C_ERTEKELESDATUM) < 9 THEN 200 ELSE (CASE WHEN C_ERTEKELESDATUM > @felevVege THEN 200 ELSE 100 END) END) + MONTH(C_ERTEKELESDATUM) AS HONAP
		  FROM T_TANULOCSOPORT_OSSZES 		
		  INNER JOIN T_FELHASZNALO_OSSZES ON T_FELHASZNALO_OSSZES.ID = T_TANULOCSOPORT_OSSZES.C_TANULOID
		  LEFT JOIN T_TANULOERTEKELES_OSSZES ON T_TANULOERTEKELES_OSSZES.C_TANTARGYID = @tantargyId AND T_TANULOERTEKELES_OSSZES.C_TANULOID = T_TANULOCSOPORT_OSSZES.C_TANULOID AND T_TANULOERTEKELES_OSSZES.TOROLT = 'F' AND T_TANULOERTEKELES_OSSZES.C_TANEVID = @pTanevId AND T_TANULOERTEKELES_OSSZES.C_ERTEKELOID = @pTanarId
		  LEFT JOIN T_OSZTALYZATERTEKELES_OSSZES ON T_OSZTALYZATERTEKELES_OSSZES.ID = T_TANULOERTEKELES_OSSZES.ID AND T_OSZTALYZATERTEKELES_OSSZES.TOROLT = 'F' AND T_OSZTALYZATERTEKELES_OSSZES.c_altanevId = @pTanevId
		  LEFT JOIN T_DICTIONARYITEMBASE_OSSZES AS TDIB_JEGY ON TDIB_JEGY.ID = T_TANULOERTEKELES_OSSZES.C_OSZTALYZAT AND TDIB_JEGY.TOROLT = 'F'		
		  WHERE T_TANULOCSOPORT_OSSZES.C_TANEVID = @pTanevId AND T_TANULOCSOPORT_OSSZES.C_OSZTALYCSOPORTID = @osztalyId
    ) AS tempErtekeles;

		SELECT RESULT.*
		FROM (
		  SELECT PIVOTERTEKEL.TANULOID AS ID, NEV AS "Név", BESOROLAS AS "Csoport tagja", 
        [109] AS Szeptember, [110] AS Október, [111] AS November, [112] AS December, [101] AS "Január / I.", FELEV.JEGY AS [I. félév$@8], [201] AS "Január / II.", [202] AS Február, [203] AS Március, [204] AS Április, [205] AS Május, [206] AS Június, 
        EVVEGE.JEGY AS [II. félév$@9], ATLAG.JEGY AS "Átlag"
	    FROM (
			  SELECT DISTINCT T1.NEV, C_BELEPESDATUM + ' - ' + C_KILEPESDATUM  AS BESOROLAS, STUFF (
					(
            SELECT ', ' + ISNULL(T2.ERTEKELES, '')
					 	FROM @tempErtekeles AS T2
						WHERE T1.TANULOID = T2.TANULOID AND T1.HONAP = T2.HONAP AND T1.ERTEKELESTIPUSA = @pEvkozi AND T2.ERTEKELESTIPUSA = @pEvkozi
						FOR XML PATH ('')
          ), 1, 2, '')  AS ERTEKELESEK,	T1.HONAP,	T1.TANULOID
				FROM @tempErtekeles AS T1
			) AS ERDEMJEGYEK
			PIVOT ( MAX(ERTEKELESEK) FOR HONAP IN ([109], [110], [111], [112], [101], [201], [202], [203], [204], [205], [206])) AS PIVOTERTEKEL
      LEFT JOIN (
			  SELECT TANULOID, AVG(OSZTALYZAT) AS JEGY 
        FROM @tempErtekeles
				WHERE ERTEKELESTIPUSA = @pFelevi
				GROUP BY TANULOID
			) AS FELEV ON PIVOTERTEKEL.TANULOID = FELEV.TANULOID
			LEFT JOIN (
			  SELECT TANULOID, AVG(OSZTALYZAT) AS JEGY				  
			  FROM @tempErtekeles
				WHERE ERTEKELESTIPUSA = @pEvvegi
				GROUP BY TANULOID
			) AS EVVEGE ON PIVOTERTEKEL.TANULOID = EVVEGE.TANULOID
      LEFT JOIN (
			  SELECT TANULOID, ROUND(AVG(CAST(OSZTALYZAT AS FLOAT)), 2) AS JEGY				  
				FROM @tempErtekeles
				WHERE ERTEKELESTIPUSA = @pEvkozi
				GROUP BY TANULOID
			) AS ATLAG ON PIVOTERTEKEL.TANULOID = ATLAG.TANULOID
		) AS RESULT	
    ORDER BY NEV;		

	END

	IF OBJECT_ID('tempdb..#tempErtekeles') IS NOT NULL DROP TABLE #tempErtekeles
	IF OBJECT_ID('tempdb..#tempFoglalkozasok') IS NOT NULL DROP TABLE #tempFoglalkozasok
END

GO