kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20180108155437_KRETA_4514_2/sp_GenerateErtekelesekTempTabla.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Description:	<Elõszedi az értékeléseket, havi bontásban tantárgy és osztálycsoport alapján>
-- NOTE: EZT A HÁROM TÁROLT ELJÁRÁST EGYBEN KELL KEZELNI, HA MÓDOSÍTJUK!!!	
--		-	sp_GenerateErtekelesekTempTabla
--		-	sp_GenerateErtekelesekMagatartasSzorgalomTempTabla
--		-	sp_GenerateErtekelesekTempTablaByTanulo
-- =============================================

CREATE PROCEDURE [dbo].[sp_GenerateErtekelesekTempTabla] 
	-- Add the parameters for the stored procedure here
	@pTantargyId		INT,
	@pOsztalyId			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 @TempErtekeles TABLE (
		ID						INT,
		ErtekelesDatuma			NVARCHAR(255),
		ErtekelesTipusa			INT,
		ErtekelesSzoveg			NVARCHAR(MAX),
		SzovegRovidNev			NVARCHAR(3),
		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,
			tanuloErtekeles.C_SZOVEGROVIDNEV			SzovegRovidNev,
			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] = @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_SZOVEGROVIDNEV],
					[C_TANULOID]
				FROM
					[T_TANULOERTEKELES]
				WHERE
					[TOROLT] = 'F' AND 
					[C_TANTARGYID] = @pTantargyId
			) 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 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,
			''							SzovegRovidNev,
			''							Szazalekos,
			@pTantargyId				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
									ISNULL(tempErtekeles2.SzovegRovidNev, '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 DISTINCT
				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_TANULOMENTESSEG tanuloMentesseg ON 
				tanuloMentesseg.C_TANULOID = felhasznalo.ID AND
				tanuloMentesseg.TOROLT = 'F' AND
				tanuloMentesseg.C_TANTARGYID = @pTantargyId AND
			    (tanuloMentesseg.C_KEZDETE IS NULL OR tanuloMentesseg.C_KEZDETE <= @pErtekelesDatum) AND
			    (tanuloMentesseg.C_VEGE IS NULL OR tanuloMentesseg.C_VEGE >= @pErtekelesDatum)
			WHERE 
				tanuloCsoport.TOROLT = 'F' AND
				tanuloCsoport.C_OSZTALYCSOPORTID = @pOsztalyId AND 
				tanuloCsoport.C_BELEPESDATUM <= @pErtekelesDatum AND 
				(tanuloCsoport.C_KILEPESDATUM IS NULL OR tanuloCsoport.C_KILEPESDATUM >= @pErtekelesDatum)
		) AS result2
			ON result2.ID = result.ID
	ORDER BY
		result.Nev
	
	IF OBJECT_ID('tempdb..#TempErtekeles') IS NOT NULL DROP TABLE #TempErtekeles
		
END
GO