kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20170515225718_KRETA_149/sp_GenerateErtekelesekTempTablaByFoglalkozas.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Description:	<Előszedi az értékeléseket, havi bontásban foglalkozás 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_GenerateErtekelesekTempTablaByFoglalkozas] 
	-- Add the parameters for the stored procedure here
	@pFoglalkozasId INT,
	@pFelevVege DATE,
	@pEvkozi INT,
	@pFelevi INT,
	@pEvvegi INT,
	@pErtekelesDatum DATE = NULL
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @pErtekelesDatum IS NULL
		SET @pErtekelesDatum = GETDATE()

	DECLARE @OsztalyId INT;
	DECLARE @TantargyId INT;

	SELECT
		@OsztalyId = foglalkozas.C_OSZTALYCSOPORTID,
		@TantargyId = foglalkozas.C_TANTARGYID
	FROM
		T_FOGLALKOZAS foglalkozas
	WHERE 
		foglalkozas.ID = @pFoglalkozasId
	
	DECLARE @TempErtekeles TABLE (
		ID				INT,
		ErtekelesDatuma NVARCHAR(255),
		ErtekelesTipusa INT,
		ErtekelesSzoveg NVARCHAR(MAX),
		Honap			NVARCHAR(2),
		TanuloId		INT,
		Nev				NVARCHAR(255),
		Osztalyzat		INT,
		Szazalek		INT,
		Suly			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,
			osztalyzatErtekeles.C_SZAZALEK		Szazalek,
			tanariAtlagSuly.C_SULY				Suly
		FROM (
			SELECT
				[C_TANULOID]
			FROM
				[T_TANULOCSOPORT]
			WHERE
				[TOROLT] = 'F' AND
				[C_BELEPESDATUM] < @pErtekelesDatum AND
				([C_KILEPESDATUM] IS NULL OR [C_KILEPESDATUM] >= @pErtekelesDatum)	AND
				[C_OSZTALYCSOPORTID] = @OsztalyId
			) 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] = @TantargyId
			) AS tanuloErtekeles
				ON tanuloErtekeles.C_TANULOID = tanuloCsoport.C_TANULOID
			LEFT JOIN (
				SELECT
					[ID],
					[C_SZAZALEK],
					[C_ERTEKELESMODJA]
				FROM 
					[T_OSZTALYZATERTEKELES] 
				WHERE 
					[TOROLT] = 'F'
			) AS osztalyzatErtekeles 
				ON osztalyzatErtekeles.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 T_TANARIATLAGSULY_OSSZES tanariAtlagSuly
				ON	tanariAtlagSuly.C_ERTEKELESMODJA = osztalyzatErtekeles.C_ERTEKELESMODJA AND 
					tanuloErtekeles.C_OSZTALYZAT IS NOT NULL AND 
					tanariAtlagSuly.TOROLT = 'F'
    ) AS temp

	SELECT
		result.*
		,result2.ErtkelesMentesseg
		,result2.SzovegesenErtekelheto
    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],
			atlag.Jegy					Atlag,
			''							Ertekeles,
			''							ErtekelesSzoveg,
			''							Szazalekos,
			@TantargyId					TantargyId
		FROM (
			SELECT DISTINCT
				tempErtekeles1.Nev,
				STUFF (
					(SELECT 
						',' +
						ISNULL(CAST(tempErtekeles2.Osztalyzat AS VARCHAR(1)), '') +
						ISNULL(CAST(tempErtekeles2.Szazalek AS VARCHAR(2)), '') +
						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                             
			LEFT JOIN (
				SELECT 
					TanuloId																TanuloId,
					ROUND(
						SUM(CAST(Osztalyzat AS FLOAT) * CAST(ISNULL(Suly, 100) AS FLOAT) / 100) / SUM(CAST(ISNULL(Suly, 100) AS FLOAT) / 100)
					, 2)																	Jegy
				FROM
					@TempErtekeles
				WHERE
					ErtekelesTipusa = @pEvkozi AND
					Osztalyzat IS NOT NULL
				GROUP BY 
					TanuloId
			) AS atlag
				ON pivotErtekelesek.TanuloId = atlag.TanuloId
	) AS result
		JOIN (
			SELECT 
				felhasznalo.ID							ID,
				tanuloMentesseg.C_ERTEKELESMENTESITES	ErtkelesMentesseg,
				tanuloMentesseg.C_SZOVEGESENERTEKELHETO	SzovegesenErtekelheto
			FROM 
				T_TANULOCSOPORT AS tanuloCsoport
			INNER JOIN T_FELHASZNALO felhasznalo ON 
				felhasznalo.ID = tanuloCsoport.C_TANULOID	
			LEFT JOIN T_TARGYTANULOATLAG targyTanuloAtlag ON 
				targyTanuloAtlag.C_TANTARGYID = @TantargyId AND 
				targyTanuloAtlag.C_TANULOID = felhasznalo.ID
			LEFT JOIN T_TANULOMENTESSEG tanuloMentesseg ON 
				tanuloMentesseg.C_TANULOID = felhasznalo.ID AND
				tanuloMentesseg.TOROLT = 'F' AND
				tanuloMentesseg.C_TANTARGYID = @TantargyId AND
				tanuloMentesseg.C_KEZDETE IS NULL AND
				tanuloMentesseg.C_VEGE IS NULL
			WHERE 
				tanuloCsoport.TOROLT = 'F' AND
				tanuloCsoport.C_OSZTALYCSOPORTID = @OsztalyId AND 
				tanuloCsoport.C_BELEPESDATUM < @pErtekelesDatum AND 
				(tanuloCsoport.C_KILEPESDATUM IS NULL OR tanuloCsoport.C_KILEPESDATUM >= @pErtekelesDatum)
		) AS result2
			ON result2.ID = result.ID
	
	IF OBJECT_ID('tempdb..#TempErtekeles') IS NOT NULL DROP TABLE #TempErtekeles
		
END
GO