kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20171010205924_KRETA_3340/sp_GetTanulokJegyeiReszletezes.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetTanulokJegyeiReszletezes]
	@OsztalyId		int,
	@TanevId		int,
	@isNemet		bit = 0
AS
BEGIN
	SET NOCOUNT ON;

SELECT DISTINCT 
	@osztalyId					OsztalyId
	,ocs.C_NEV					OsztalyNev
	,o.C_OSZTALYNAPLOLEIRASA	OsztalyMegjegyzes
	,o.C_OSZTALYFONOKID			PartnerID
	,ofo.C_NYOMTATASINEV		Osztalyfonok
	,i.C_IGAZGATONEVE			IntezmenyVezeto
	,i.C_NEV					IntezmenyNeve
	,i.C_IRANYITOSZAM			IntezmenyIranyitoszam
	,i.C_VAROS					IntezmenyVaros
	,dbo.fnGetDokumentumIntezmenyCime(@tanevId)	IntezmenyCime
	,i.C_OMKOD					IntezmenyOMKod
  FROM T_OSZTALYCSOPORT_OSSZES ocs 
  	INNER JOIN T_OSZTALY_OSSZES o ON ocs.ID = o.ID AND o.torolt='F'
  	LEFT  JOIN T_FELHASZNALO_OSSZES ofo ON ofo.ID = o.C_OSZTALYFONOKID 
  	INNER JOIN T_INTEZMENYADATOK_OSSZES i on i.C_INTEZMENYID=ocs.C_INTEZMENYID AND i.C_TANEVID = ocs.C_TANEVID AND i.Torolt = 'F'
  WHERE ocs.ID = @osztalyId
	
--Osztályhoz tartozó tanulók
	DECLARE @Tanulok TABLE (TanuloId INT PRIMARY KEY, KilepesDatum DATE)
	INSERT INTO @Tanulok
	SELECT TanuloId, KilepesDatum FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T') 

--Értékelések	
	DECLARE @ertekelesek TABLE (ErtekelesId INT, TargyNev NVARCHAR(MAX), Ertekeles NVARCHAR(MAX))
	INSERT INTO @ertekelesek
	SELECT 
		ERTEKELES.Id	
		, IIF(@isNemet = 0, 'Magatartás', 'Betragen')			TARGY
		, ISNULL(CONVERT(NVARCHAR(MAX), IIF(@isNemet = 0, ERT.C_NAME, dbo.fnGetDokumentumNemet(OSZTALYZAT.C_MAGATARTAS))), ISNULL(ERTEKELES.C_ERTEKELESSZOVEG, IIF(@isNemet = 0, JEGY.C_NAME, dbo.fnGetDokumentumNemet(ERTEKELES.C_OSZTALYZAT)))) Ertekeles
	FROM T_TANULOERTEKELES_OSSZES ERTEKELES
		INNER JOIN @Tanulok TANCSOPORT ON TANCSOPORT.TanuloId = ERTEKELES.C_TANULOID 
		INNER JOIN T_OSZTALYFONOKIERTEKELES_OSSZES OSZTALYZAT ON OSZTALYZAT.ID = ERTEKELES.ID 
		LEFT JOIN T_DICTIONARYITEMBASE_OSSZES ERT ON ERT.ID=OSZTALYZAT.C_MAGATARTAS AND ERT.C_TANEVID = @tanevId
		LEFT JOIN T_DICTIONARYITEMBASE_OSSZES JEGY ON JEGY.ID=ERTEKELES.C_OSZTALYZAT AND JEGY.C_TANEVID = @tanevId
		LEFT JOIN T_CSOPORT_OSSZES cs ON cs.Id = ERTEKELES.C_OSZTALYCSOPORTID
	WHERE 
		ERTEKELES.C_TANEVID = @tanevId
		AND ERTEKELES.TOROLT='F'
		AND OSZTALYZAT.TOROLT = 'F'
		AND C_ERTEKELESSZOVEG IS NULL
		AND (KilepesDatum IS NULL OR KilepesDatum >= C_ERTEKELESDATUM)
		AND (cs.Id IS NULL OR cs.C_TIPUSA = 1034)

	UNION

	SELECT 
		ERTEKELES.Id	
		, IIF(@isNemet = 0, 'Szorgalom', 'Fleiss')			TARGY
		, ISNULL(CONVERT(NVARCHAR(MAX), IIF(@isNemet = 0, ERT.C_NAME, dbo.fnGetDokumentumNemet(OSZTALYZAT.C_SZORGALOM))), ISNULL(ERTEKELES.C_ERTEKELESSZOVEG, IIF(@isNemet = 0, JEGY.C_NAME, dbo.fnGetDokumentumNemet(ERTEKELES.C_OSZTALYZAT)))) Ertekeles
	FROM T_TANULOERTEKELES_OSSZES ERTEKELES
		INNER JOIN @Tanulok TANCSOPORT ON TANCSOPORT.TanuloId = ERTEKELES.C_TANULOID 
		INNER JOIN T_OSZTALYFONOKIERTEKELES_OSSZES OSZTALYZAT ON OSZTALYZAT.ID = ERTEKELES.ID 
		LEFT JOIN T_DICTIONARYITEMBASE_OSSZES ERT ON ERT.ID=OSZTALYZAT.C_SZORGALOM AND ERT.C_TANEVID = @tanevId
		LEFT JOIN T_DICTIONARYITEMBASE_OSSZES JEGY ON JEGY.ID=ERTEKELES.C_OSZTALYZAT AND JEGY.C_TANEVID = @tanevId
		LEFT JOIN T_CSOPORT_OSSZES cs ON cs.Id = ERTEKELES.C_OSZTALYCSOPORTID
	WHERE 
		ERTEKELES.C_TANEVID = @TanevId
		AND ERTEKELES.TOROLT='F'
		AND OSZTALYZAT.TOROLT = 'F'
		AND C_ERTEKELESSZOVEG IS NULL
		AND (KilepesDatum IS NULL OR KilepesDatum >= C_ERTEKELESDATUM)
		AND (cs.Id IS NULL OR cs.C_TIPUSA = 1034)

	UNION

	SELECT 
		ERTEKELES.Id	
		, IIF(@isNemet = 0, 'Magatartás', 'Betragen')			TARGY
		, SUBSTRING(ERTEKELES.C_ERTEKELESSZOVEG, 1, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',') - 1)		Ertekeles
	FROM T_TANULOERTEKELES_OSSZES ERTEKELES
		INNER JOIN T_OSZTALYFONOKIERTEKELES_OSSZES OSZTALYZAT ON OSZTALYZAT.ID = ERTEKELES.ID 
		INNER JOIN @Tanulok TANCSOPORT ON TANCSOPORT.TanuloId = ERTEKELES.C_TANULOID 
	WHERE 
		ERTEKELES.C_TANEVID = @TanevId
		AND (KilepesDatum IS NULL OR KilepesDatum >= C_ERTEKELESDATUM)
		AND ERTEKELES.TOROLT='F'
		AND OSZTALYZAT.TOROLT = 'F'
		AND C_ERTEKELESSZOVEG IS NOT NULL
		AND SUBSTRING(ERTEKELES.C_ERTEKELESSZOVEG, 1, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',') - 1) IN ('rossz', 'változó', 'jó', 'példás', 'Vorbildlich', 'Gut', 'Wechselnd', 'Schlecht')
		AND SUBSTRING (ERTEKELES.C_ERTEKELESSZOVEG, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',')+ 1, LEN(ERTEKELES.C_ERTEKELESSZOVEG)) IN ('hanyag', 'változó', 'jó', 'példás', 'Vorbildlich', 'Gut', 'Wechselnd', 'Schlecht')

	UNION

	SELECT 
		ERTEKELES.Id	
		, IIF(@isNemet = 0, 'Szorgalom', 'Fleiss')	TARGY
		, SUBSTRING (ERTEKELES.C_ERTEKELESSZOVEG, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',')+ 1, LEN(ERTEKELES.C_ERTEKELESSZOVEG))		Ertekeles
	FROM T_TANULOERTEKELES_OSSZES ERTEKELES
		INNER JOIN T_OSZTALYFONOKIERTEKELES_OSSZES OSZTALYZAT ON OSZTALYZAT.ID = ERTEKELES.ID 
		INNER JOIN @Tanulok TANCSOPORT ON TANCSOPORT.TanuloId = ERTEKELES.C_TANULOID 
	WHERE 
		ERTEKELES.C_TANEVID = @TanevId
		AND (KilepesDatum IS NULL OR KilepesDatum >= C_ERTEKELESDATUM)
		AND ERTEKELES.TOROLT='F'
		AND OSZTALYZAT.TOROLT = 'F'
		AND C_ERTEKELESSZOVEG IS NOT NULL
		AND SUBSTRING(ERTEKELES.C_ERTEKELESSZOVEG, 1, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',') - 1) IN ('rossz', 'változó', 'jó', 'példás', 'Vorbildlich', 'Gut', 'Wechselnd', 'Schlecht')
		AND SUBSTRING (ERTEKELES.C_ERTEKELESSZOVEG, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',')+ 1, LEN(ERTEKELES.C_ERTEKELESSZOVEG)) IN ('hanyag', 'változó', 'jó', 'példás', 'Vorbildlich', 'Gut', 'Wechselnd', 'Schlecht')

	UNION 

	SELECT 
		ERTEKELES.Id				
		, IIF(@isNemet = 0, 'Magatartás / Szorgalom', 'Betragen / Fleiss')	TARGY
		, ERTEKELES.C_ERTEKELESSZOVEG Ertekeles
	FROM T_TANULOERTEKELES_OSSZES ERTEKELES
		INNER JOIN @Tanulok TANCSOPORT ON TANCSOPORT.TanuloId = ERTEKELES.C_TANULOID 
		INNER JOIN T_OSZTALYFONOKIERTEKELES_OSSZES OSZTALYZAT ON OSZTALYZAT.ID = ERTEKELES.ID 
	WHERE 
		ERTEKELES.C_TANEVID = @TanevId
		AND ERTEKELES.TOROLT='F'
		AND OSZTALYZAT.TOROLT = 'F'
		AND C_ERTEKELESSZOVEG IS NOT NULL
		AND (KilepesDatum IS NULL OR KilepesDatum >= C_ERTEKELESDATUM)
		AND SUBSTRING(ERTEKELES.C_ERTEKELESSZOVEG, 1, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',') - 1) NOT IN ('rossz', 'változó', 'jó', 'példás')
		AND SUBSTRING (ERTEKELES.C_ERTEKELESSZOVEG, CHARINDEX(',', ERTEKELES.C_ERTEKELESSZOVEG + ',')+ 1, LEN(ERTEKELES.C_ERTEKELESSZOVEG)) NOT IN ('hanyag', 'változó', 'jó', 'példás')

	UNION

	SELECT 
		ERTEKELES.Id									
		, ISNULL(TARGY.C_NEVNYOMTATVANYBAN, TARGY.TARGYNEV)	TARGY
		, ISNULL(CONVERT(NVARCHAR(MAX), IIF(@isNemet = 0, ERT.C_NAME, dbo.fnGetDokumentumNemet(ERTEKELES.C_OSZTALYZAT))), ISNULL(ERTEKELES.C_ERTEKELESSZOVEG, CONVERT(NVARCHAR(MAX),ertekelesMegjegyzes.C_SZAZALEK)+' % '))	Ertekeles	   
	FROM T_TANULOERTEKELES_OSSZES ERTEKELES
		INNER JOIN @Tanulok TANCSOPORT ON TANCSOPORT.TanuloId = ERTEKELES.C_TANULOID 
		INNER JOIN ( SELECT ID,C_NEV TARGYNEV,C_TARGYKATEGORIA KAT, C_ALTANTARGYKENTNYOMTATVANYBAN, C_FOTARGYE, C_NEVNYOMTATVANYBAN, C_TARGYKATEGORIA FROM T_TANTARGY_OSSZES WHERE TOROLT = 'F') TARGY 
													ON TARGY.ID = ERTEKELES.C_TANTARGYID
		LEFT JOIN T_DICTIONARYITEMBASE_OSSZES ERT ON ERT.ID=ERTEKELES.C_OSZTALYZAT AND ERT.C_TANEVID = @tanevId
		LEFT JOIN T_OSZTALYZATERTEKELES_OSSZES ertekelesMegjegyzes on ertekelesMegjegyzes.ID=ertekeles.Id
		LEFT JOIN T_CSOPORT_OSSZES cs ON cs.Id = ERTEKELES.C_OSZTALYCSOPORTID
	WHERE 	
		ERTEKELES.C_TANEVID = @tanevId
		AND ERTEKELES.TOROLT='F'
		AND (KilepesDatum IS NULL OR KilepesDatum >= C_ERTEKELESDATUM)
		AND (cs.Id IS NULL OR cs.C_TIPUSA = 1034)
		AND TARGY.C_TARGYKATEGORIA <> 1248

SELECT 
	FORMAT(te.C_ERTEKELESDATUM, 'yyyy.MM.dd.') Datum
	,ertekeles.TargyNev			Tantargy
	,ertTipusa.C_NAME			ErtTip
	,ertModja.C_NAME			ErtMod
	,oe.c_tema					Tema
	,ertekelo.C_NYOMTATASINEV	ErtekeloNev
	,ertekeles.Ertekeles		Osztalyzat
	,tanulo.C_NYOMTATASINEV		TanuloNev
	,tanulo.C_NYOMTATASINEV + CAST(tanulo.Id AS NVARCHAR(MAX)) GroupParameter
FROM @Tanulok t
	INNER JOIN T_TANULOERTEKELES_OSSZES te ON te.c_tanuloid = t.TanuloId
	INNER JOIN @ertekelesek ertekeles on ertekeles.ErtekelesId = te.Id
	LEFT JOIN T_OSZTALYZATERTEKELES_OSSZES oe ON oe.Id = te.id
	INNER JOIN T_DICTIONARYITEMBASE_OSSZES ertTipusa ON ertTipusa.Id = te.C_ERTEKELESTIPUSA AND ertTipusa.C_TANEVID = @tanevid
	LEFT JOIN T_DICTIONARYITEMBASE_OSSZES ertModja ON ertModja.Id = oe.C_ERTEKELESMODJA AND ertModja.C_TANEVID = @tanevid
	INNER JOIN T_FELHASZNALO_OSSZES	ertekelo ON ertekelo.Id = C_ERTEKELOID
	INNER JOIN T_FELHASZNALO_OSSZES tanulo ON tanulo.Id = te.C_TANULOID
WHERE
	EXISTS (SELECT TOP 1 C_TANULOID FROM @Tanulok WHERE TanuloId = C_TANULOID)
ORDER BY tanulo.C_NYOMTATASINEV, te.C_ERTEKELESDATUM
	
END

GO