kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20171025123838_KRETA_3855/sp_GetUzenofuzetErtekelolap.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

CREATE PROCEDURE [sp_GetUzenofuzetErtekelolap]
	@osztalyId INT,
	@tanevId   INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Intézmény, tanév
	SELECT T_INTEZMENYADATOK_OSSZES.C_NEV				IntezmenyNeve,
		   T_INTEZMENYADATOK_OSSZES.C_VAROS				IntezmenyVaros,
		   dbo.fnGetDokumentumIntezmenyCime(@tanevId)	IntezmenyCime,
		   T_INTEZMENYADATOK_OSSZES.C_TELEFONSZAM		TELEFON,
		   T_INTEZMENYADATOK_OSSZES.C_EMAILCIM			EMAILCIM,
		   T_INTEZMENYADATOK_OSSZES.C_OMKOD				IntezmenyOMKod,
		   T_INTEZMENYADATOK_OSSZES.C_IGAZGATONEVE		Intezmenyvezeto, 
		   (SELECT C_OSZTALYFONOKID FROM T_OSZTALY_OSSZES WHERE T_OSZTALY_OSSZES.ID = @osztalyId AND T_OSZTALY_OSSZES.C_ALTANEVID = @tanevId AND T_OSZTALY_OSSZES.TOROLT = 'F') PartnerID,
		   T_TANEV_OSSZES.C_NEV							TANEV
	FROM T_INTEZMENYADATOK_OSSZES
		INNER JOIN T_TANEV_OSSZES on T_TANEV_OSSZES.ID = T_INTEZMENYADATOK_OSSZES.C_TANEVID AND T_TANEV_OSSZES.C_INTEZMENYID = T_INTEZMENYADATOK_OSSZES.C_INTEZMENYID AND T_TANEV_OSSZES.TOROLT = 'F'
	WHERE 
		T_INTEZMENYADATOK_OSSZES.TOROLT = 'F'
		AND T_TANEV_OSSZES.ID = @tanevId

	--Osztály, évfolyam
	SELECT T_OSZTALYCSOPORT_OSSZES.C_NEV,
		   T_OSZTALYCSOPORT_OSSZES.C_EVFOLYAMTIPUSA,
		   T_FELHASZNALO_OSSZES.C_NYOMTATASINEV Osztalyfonok
	FROM T_OSZTALYCSOPORT_OSSZES
		INNER JOIN T_OSZTALY_OSSZES ON T_OSZTALYCSOPORT_OSSZES.id = T_OSZTALY_OSSZES.ID
		INNER JOIN T_FELHASZNALO_OSSZES ON T_FELHASZNALO_OSSZES.id = T_OSZTALY_OSSZES.c_osztalyfonokid
	WHERE T_OSZTALYCSOPORT_OSSZES.TOROLT='F' 
		AND T_OSZTALYCSOPORT_OSSZES.ID = @osztalyId
		AND T_OSZTALYCSOPORT_OSSZES.C_TANEVID = @tanevId

	
	--Tanuló adatai
	SELECT 
		T_FELHASZNALO_OSSZES.ID						TANULOID,
		T_FELHASZNALO_OSSZES.C_NYOMTATASINEV		TANULONEV,
		T_FELHASZNALO_OSSZES.C_OKTATASIAZONOSITO	OKTATASIAZONOSITO
	FROM T_FELHASZNALO_OSSZES
		INNER JOIN fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T') Tanulok ON Tanulok.TanuloId = T_FELHASZNALO_OSSZES.Id
	ORDER BY T_FELHASZNALO_OSSZES.C_NYOMTATASINEV
	
	--Jegyek
		DECLARE @FelevVegeHonap INT,
				@FelevVegeNap	INT
		SELECT 
			 @FelevVegeHonap = DATEPART(mm, C_DATUM)
			,@FelevVegeNap = DATEPART(dd, C_DATUM)
		FROM T_TANEVRENDJE_OSSZES 
		WHERE 
			C_NAPTIPUSA = 1400 
			AND TOROLT = 'F' 
			AND C_TANEVID = @tanevid

		SELECT * INTO #magatartasSzorgalomTMP FROM
		(SELECT 
			'Magatartás' Tipus
			,IIF(DATEPART(mm, Datum) = @FelevVegeHonap, IIF(DATEPART(mm, Datum) = @FelevVegeHonap AND DATEPART(DD, Datum) <= @FelevVegeNap, 13, 14), DATEPART(mm, Datum))  Honap
			,TanuloId
			,Magatartas	Ertekeles
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Magatartas IS NOT NULL
			AND Tipusa = 1518

		UNION ALL

		SELECT 
			'Szorgalom'
			,IIF(DATEPART(mm, Datum) = @FelevVegeHonap, IIF(DATEPART(mm, Datum) = @FelevVegeHonap AND DATEPART(DD, Datum) <= @FelevVegeNap, 13, 14), DATEPART(mm, Datum))  Honap
			,TanuloId
			,Szorgalom 
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Szorgalom IS NOT NULL
			AND Tipusa = 1518

		UNION ALL

		SELECT 
			'Szorgalom'
			,15  Honap
			,TanuloId
			,Szorgalom 
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Szorgalom IS NOT NULL
			AND Tipusa = 1519

		UNION ALL

		SELECT 
			'Magatartás'
			,15  Honap
			,TanuloId
			,Magatartas 
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Magatartas IS NOT NULL
			AND Tipusa = 1519

		UNION ALL

		SELECT 
			'Szorgalom'
			,16  Honap
			,TanuloId
			,Szorgalom 
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Szorgalom IS NOT NULL
			AND Tipusa = 1520

		UNION ALL

		SELECT 
			'Magatartás'
			,16  Honap
			,TanuloId
			,Magatartas 
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Magatartas IS NOT NULL
			AND Tipusa = 1520

		UNION ALL

		SELECT 
			'Magatartás' Tipus
			,IIF(DATEPART(mm, Datum) = @FelevVegeHonap, IIF(DATEPART(mm, Datum) = @FelevVegeHonap AND DATEPART(DD, Datum) <= @FelevVegeNap, 13, 14), DATEPART(mm, Datum))  Honap
			,TanuloId
			,CONVERT(NVARCHAR(MAX),Osztalyzat)
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Magatartas IS NULL
			AND TantargyId IS NULL
			AND Tipusa = 1518

		UNION ALL

		SELECT 
			'Szorgalom' Tipus
			,IIF(DATEPART(mm, Datum) = @FelevVegeHonap, IIF(DATEPART(mm, Datum) = @FelevVegeHonap AND DATEPART(DD, Datum) <= @FelevVegeNap, 13, 14), DATEPART(mm, Datum))  Honap
			,TanuloId
			,CONVERT(NVARCHAR(MAX),Osztalyzat)
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Szorgalom IS NULL
			AND TantargyId IS NULL
			AND Tipusa = 1518

		UNION ALL

		SELECT 
			'Magatartás' Tipus
			,15  Honap
			,TanuloId
			,CONVERT(NVARCHAR(MAX),Osztalyzat)
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Magatartas IS NULL
			AND TantargyId IS NULL
			AND Tipusa = 1519

		UNION ALL

		SELECT 
			'Szorgalom' Tipus
			,15  Honap
			,TanuloId
			,CONVERT(NVARCHAR(MAX),Osztalyzat)
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Szorgalom IS NULL
			AND TantargyId IS NULL
			AND Tipusa = 1519

		UNION ALL

		SELECT 
			'Magatartás' Tipus
			,16  Honap
			,TanuloId
			,CONVERT(NVARCHAR(MAX),Osztalyzat)
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Magatartas IS NULL
			AND TantargyId IS NULL
			AND Tipusa = 1520

		UNION ALL

		SELECT 
			'Szorgalom' Tipus
			,16  Honap
			,TanuloId
			,CONVERT(NVARCHAR(MAX),Osztalyzat)
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			Szorgalom IS NULL
			AND TantargyId IS NULL
			AND Tipusa = 1520
		)magszorg

		SELECT DISTINCT
			Honap
			,TanuloId
			,Tipus
			,STUFF((
				SELECT ', ' + CAST(Ertekeles AS nvarchar(max))
				FROM #magatartasSzorgalomTMP bmsz 
				WHERE 
					msz.TanuloId = bmsz.TanuloId
					AND msz.Tipus = bmsz.Tipus
					AND msz.Honap = bmsz.Honap
				FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2,'') Ertekeles 	
			,IIF(Tipus = 'Magatartás', 0, 1) Rendez
			,
			CASE 
				WHEN Honap = 2 THEN 8
				WHEN Honap = 3 THEN 9
				WHEN Honap = 4 THEN 10
				WHEN Honap = 5 THEN 11
				WHEN Honap = 6 THEN 12
				WHEN Honap = 7 THEN 13
				WHEN Honap = 8 THEN 14
				WHEN Honap = 9 THEN  1
				WHEN Honap = 10 THEN 2
				WHEN Honap = 11 THEN 3
				WHEN Honap = 12 THEN 4
				WHEN Honap = 13 THEN 5
				WHEN Honap = 14 THEN 7
				WHEN Honap = 15 THEN 6
				WHEN Honap = 16 THEN 15
			END AS RendezHonap
		INTO #magatartasSzorgalom 
		FROM #magatartasSzorgalomTMP msz

		SELECT * INTO #jegyekTMP FROM
		(SELECT 
			IIF(DATEPART(mm, Datum) = @FelevVegeHonap, IIF(DATEPART(mm, Datum) = @FelevVegeHonap AND DATEPART(DD, Datum) <= @FelevVegeNap, 13, 14), DATEPART(mm, Datum))  Honap
			,TanuloId
			,TantargyId
			,ISNULL(CONVERT(NVARCHAR(MAX),Osztalyzat), Szazalek) Ertekeles
			,TargyKategoriaId Rendez
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			TantargyId IS NOT NULL
			AND SzovegesErtekeles IS NULL
			AND Tipusa = 1518

		UNION ALL

		SELECT 
			15  Honap
			,TanuloId
			,TantargyId
			,ISNULL(CONVERT(NVARCHAR(MAX),Osztalyzat), Szazalek) Ertekeles
			,TargyKategoriaId
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			TantargyId IS NOT NULL
			AND SzovegesErtekeles IS NULL
			AND Tipusa = 1519

		UNION ALL

		SELECT 
			16  Honap
			,TanuloId
			,TantargyId
			,ISNULL(CONVERT(NVARCHAR(MAX),Osztalyzat), Szazalek) Ertekeles
			,TargyKategoriaId
		FROM fnGetDokumentumErtekelesekOsztalyonkent(@tanevid, @osztalyid)
		WHERE
			TantargyId IS NOT NULL
			AND SzovegesErtekeles IS NULL
			AND Tipusa = 1520
		)jegyek

		SELECT DISTINCT
			Honap
			,TanuloId
			,TantargyId
			,STUFF((
				SELECT ', ' + CAST(Ertekeles AS nvarchar(max))
				FROM #jegyekTMP bj 
				WHERE 
					j.TanuloId = bj.TanuloId
					AND j.TantargyId = bj.TantargyId
					AND j.Honap = bj.Honap
				FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2,'') Ertekeles 	
			,Rendez
			,
				CASE 
				WHEN Honap = 2 THEN 8
				WHEN Honap = 3 THEN 9
				WHEN Honap = 4 THEN 10
				WHEN Honap = 5 THEN 11
				WHEN Honap = 6 THEN 12
				WHEN Honap = 7 THEN 13
				WHEN Honap = 8 THEN 14
				WHEN Honap = 9 THEN  1
				WHEN Honap = 10 THEN 2
				WHEN Honap = 11 THEN 3
				WHEN Honap = 12 THEN 4
				WHEN Honap = 13 THEN 5
				WHEN Honap = 14 THEN 7
				WHEN Honap = 15 THEN 6
				WHEN Honap = 16 THEN 15
				END AS RendezHonap
		INTO #jegyek 
		FROM #jegyekTMP j

			SELECT * FROM(
		SELECT 
			TanuloId
			, Tipus
			, Rendez
			,[9],[10],[11],[12],[2],[3],[4],[5],[6],[13],[14],[15],[16]	
		FROM (SELECT TanuloId, Ertekeles, Honap, Tipus, IIF(Tipus = 'Magatartás', 0, 1) TantargyId, Rendez
				FROM #magatartasSzorgalom)a
		PIVOT (MAX(Ertekeles) FOR Honap IN ([9],[10],[11],[12],[2],[3],[4],[5],[6],[13],[14],[15],[16])) piv
		
		UNION ALL

		SELECT
			TanuloId		TanuloId
			,C_NEV			TantargyNev
			,Rendez
			,[9],[10],[11],[12],[2],[3],[4],[5],[6],[13],[14],[15],[16]	
		FROM (SELECT TanuloId, Ertekeles, Honap, TantargyId, Rendez FROM #jegyek) j
			INNER JOIN (SELECT Id, C_NEV FROM T_TANTARGY_OSSZES) t ON t.Id = TantargyId
		PIVOT (MAX(Ertekeles) FOR Honap IN ([9],[10],[11],[12],[2],[3],[4],[5],[6],[13],[14],[15],[16])) piv
		)vegleges
		ORDER BY TanuloId, Rendez, Tipus
END

GO