kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20170428103502_KRETA_1027/sp_GenerateErtekelesekMagatartasSzorgalomTempTabla.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Description:	<Elõszedi a magatartas szorgalom értékeléseket, havi bontásban osztálycsoport alapján>
-- NOTE: EZT A NÉGY TÁROLT ELJÁRÁST EGYBEN KELL KEZELNI, HA MÓDOSÍTJUK!!!	
--		-	sp_GenerateErtekelesekTempTabla
--		-	sp_GenerateErtekelesekTempTablaByFoglalkozas
--		-	sp_GenerateErtekelesekMagatartasSzorgalomTempTabla
--		-	sp_GenerateErtekelesekTempTablaByTanulo
-- =============================================

CREATE PROCEDURE [dbo].[sp_GenerateErtekelesekMagatartasSzorgalomTempTabla] 
	-- Add the parameters for the stored procedure here
	@pOsztalyId INT,
	@pFelevVege DATE,
	@pEvkozi INT,
	@pFelevi INT,
	@pEvvegi INT
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @TempErtekeles TABLE (
		ID				INT,
		ErtekelesDatuma NVARCHAR(255),
		ErtekelesTipusa INT,
		ErtekelesSzoveg NVARCHAR(MAX),
		Honap			NVARCHAR(2),
		TanuloId		INT,
		Nev				NVARCHAR(255),
		Osztalyzat		INT,
		Magatartas		INT,
		Szorgalom		INT
	)

	INSERT INTO @TempErtekeles
	SELECT DISTINCT *
    FROM (
		SELECT
    		tanuloErtekeles.ID					ID,
			tanuloErtekeles.C_ERTEKELESDATUM	ErtekelesDatuma,
			tanuloErtekeles.C_ERTEKELESTIPUSA	ErtekelesTipusa,
			tanuloErtekeles.C_ERTEKELESSZOVEG	ErtekelesSzoveg,
			CASE WHEN tanuloErtekeles.C_ERTEKELESTIPUSA = @pFelevi 
					THEN 'I'
				WHEN tanuloErtekeles.C_ERTEKELESTIPUSA = @pEvvegi 
					THEN 'II'
				ELSE
					CASE 
						WHEN CAST(tanuloErtekeles.C_ERTEKELESDATUM AS DATE) > @pFelevVege
							THEN CAST(MONTH(tanuloErtekeles.C_ERTEKELESDATUM) + 1 AS NVARCHAR(2))
							ELSE CAST(MONTH(tanuloErtekeles.C_ERTEKELESDATUM) AS NVARCHAR(2))
					END 
			END									Honap,
			tanuloCsoport.C_TANULOID			TanuloId,
			felhasznalo.C_NYOMTATASINEV			Nev,
			osztalyzat.C_VALUE					Osztalyzat,
			magatartas.C_Value					Magatartas,
			szorgalom.C_Value					Szorgalom
		FROM (
			SELECT
				[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
			) AS tanuloCsoport
			INNER JOIN (
				SELECT 
					[ID],
					[C_NYOMTATASINEV]
				FROM 
					[T_FELHASZNALO] 
				WHERE 
					[TOROLT] = 'F' 
			) AS felhasznalo
				ON felhasznalo.ID = tanuloCsoport.C_TANULOID
			LEFT JOIN (
				SELECT
					[ID],
					[C_ERTEKELESDATUM],
					[C_ERTEKELESTIPUSA],
					[C_OSZTALYZAT],
					[C_ERTEKELESSZOVEG],
					[C_TANULOID]
				FROM
					[T_TANULOERTEKELES]
				WHERE
					[TOROLT] = 'F' AND 
					[C_TANTARGYID] IS NULL
			) AS tanuloErtekeles
				ON tanuloErtekeles.C_TANULOID = tanuloCsoport.C_TANULOID
			LEFT JOIN (
				SELECT
					[ID],
    				[C_MAGATARTAS],
    				[C_SZORGALOM]
				FROM 
					[T_OSZTALYFONOKIERTEKELES] 
				WHERE 
					[TOROLT] = 'F'
			) AS osztalyfonokiErtekeles 
				ON osztalyfonokiErtekeles.ID = tanuloErtekeles.ID
			LEFT JOIN (
				SELECT 
					[ID],
					[C_VALUE]
				FROM
					[T_DICTIONARYITEMBASE] 
				WHERE 
					[TOROLT] = 'F'
			) AS osztalyzat
				ON osztalyzat.ID = tanuloErtekeles.C_OSZTALYZAT
			LEFT JOIN (
				SELECT 
					[ID],
					[C_VALUE]
				FROM
					[T_DICTIONARYITEMBASE]
				WHERE
					[TOROLT] = 'F' 
			) AS magatartas
				ON magatartas.ID = osztalyfonokiErtekeles.C_MAGATARTAS
			LEFT JOIN (
				SELECT 
					[ID],
					[C_VALUE]
				FROM
					[T_DICTIONARYITEMBASE]
				WHERE
					[TOROLT] = 'F' 
			) AS szorgalom
				ON szorgalom.ID = osztalyfonokiErtekeles.C_SZORGALOM
    ) AS temp

	SELECT *
    FROM (
		SELECT 
			pivotErtekelesek.TanuloId	ID,
			Nev							Nev,
			[9]							[09],
			[10]						[10],
			[11]						[11],
			[12]						[12],
			[1]							[01I],
			[2]							[01II],
			[3]							[02],
			[4]							[03],
			[5]							[04],
			[6]							[05], 
			[7]							[06],
			[I]							[I],
			[II]						[II],
			''							Ertekeles,
			''							ErtekelesSzoveg,
			''							Magatartas,
			''							Szorgalom
		FROM (
			SELECT DISTINCT
				tempErtekeles1.Nev,
				STUFF (
					(SELECT 
						',' +
						ISNULL(CAST(tempErtekeles2.Osztalyzat AS VARCHAR(1)), '') +
						ISNULL(CAST(tempErtekeles2.Magatartas AS varchar(15)), '') + ',' +  ISNULL(CAST(tempErtekeles2.Szorgalom AS varchar(15)), '') + 
						ISNULL(CAST(
							CASE
								WHEN tempErtekeles2.ErtekelesSzoveg IS NULL OR LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(tempErtekeles2.ErtekelesSzoveg, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) = ''
									THEN NULL 
								ELSE 
									'sz'
							END AS VARCHAR(2)), '')
					FROM 
						@TempErtekeles tempErtekeles2
					WHERE 
						tempErtekeles1.TanuloId = tempErtekeles2.TanuloId AND 
						tempErtekeles1.Honap = tempErtekeles2.Honap
					FOR XML PATH ('')
					), 1, 1, ''
				) Ertekelesek,
				tempErtekeles1.Honap,
				tempErtekeles1.TanuloId
			FROM 
				@TempErtekeles tempErtekeles1
		) AS erdemjegyek
		PIVOT (
			MAX(Ertekelesek) 
			FOR Honap 
			IN ([I], [II], [9] ,[10] ,[11] ,[12] ,[1] ,[2] ,[3] ,[4] ,[5] ,[6], [7])
		) AS pivotErtekelesek
	) AS result

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