﻿-- =============================================
-- Description:  Lekérdezzük a részletes hiányzási adatokat egy osztályra nézve.
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS uspGetTanulokMulasztasaiKeseseiReszletes
GO

CREATE PROCEDURE uspGetTanulokMulasztasaiKeseseiReszletes
	@intezmenyId				INT,
	@tanevId					INT,
	@osztalyId					INT,
	@elmeletGyakorlat			BIT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE
	 @elmeletiOraPercben INT = 45
	,@gyakorlatiOraPercben INT = 45
	,@osztalyNev NVARCHAR(200)

	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
  		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
	  WHERE
		o.TOROLT = 'F'
		AND i.TOROLT = 'F'
		AND ofo.TOROLT = 'F'
		AND ocs.ID = @osztalyId
		AND ocs.C_TANEVID = @tanevId

	SELECT
		@osztalyNev = C_NEV
	FROM
		T_OSZTALYCSOPORT_OSSZES ocs
	WHERE
			ID = @osztalyId
		AND ocs.TOROLT = 'F'
		AND ocs.C_TANEVID = @tanevId

	DECLARE @sql NVARCHAR(MAX) = ''

	SET @sql +=
		'SELECT
			  tcs.C_TANULOID          TanuloId
			 ,C_SZULETESINEV          TanuloNev
			 ,C_OKTATASIAZONOSITO     TanuloOktAzon
			 ,@osztalyNev             OsztalyNev
			 ,@osztalyId              OsztalyId'

	IF @elmeletgyakorlat = 1
	BEGIN
		SET @sql +=
			',SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoraiHianyzasIgazolandoGyakorlati
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoraiHianyzasIgazoltGyakorlati
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoraiHianyzasIgazolatlanGyakorlati
			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''F'' AND Gyakorlati = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolandoGyakorlati
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''F'' AND Gyakorlati = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazoltGyakorlati
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''F'' AND Gyakorlati = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolatlanGyakorlati

			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolandoGyakorlati
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazoltGyakorlati
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolatlanGyakorlati

			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)						TanoraiKesesIgazolandoGyakorlatiPerc
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)							TanoraiKesesIgazoltGyakorlatiPerc
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)							TanoraiKesesIgazolatlanGyakorlatiPerc
			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben SzamitottKesesIgazolandoGyakorlati
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben	SzamitottKesesIgazoltGyakorlati
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben	SzamitottKesesIgazolatlanGyakorlati'

		SET @sql +=
			',SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoraiHianyzasIgazolandoElmeleti
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoraiHianyzasIgazoltElmeleti
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoraiHianyzasIgazolatlanElmeleti
			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''F'' AND Gyakorlati = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolandoElmeleti
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''F'' AND Gyakorlati = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazoltElmeleti
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''F'' AND Gyakorlati = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolatlanElmeleti

			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolandoElmeleti
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazoltElmeleti
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolatlanElmeleti

			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)						TanoraiKesesIgazolandoElmeletiPerc
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)							TanoraiKesesIgazoltElmeletiPerc
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)							TanoraiKesesIgazolatlanElmeletiPerc
			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben	SzamitottKesesIgazolandoElmeleti
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben		SzamitottKesesIgazoltElmeleti
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben		SzamitottKesesIgazolatlanElmeleti'
		END
	ELSE
	BEGIN
		SET @sql +=
			',SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END)											TanoraiHianyzasIgazolando
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) + ISNULL(h.[hozottIgazoltHianyzas], 0)		TanoraiHianyzasIgazolt
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND KesesPercben IS NULL THEN 1 ELSE 0 END) + ISNULL(h.[hozottIgazolatlanHianyzas], 0)	TanoraiHianyzasIgazolatlan
			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END)											TanoranKivuliHianyzasIgazolando
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END)												TanoranKivuliHianyzasIgazolt
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''F'' AND KesesPercben IS NULL THEN 1 ELSE 0 END)												TanoranKivuliHianyzasIgazolatlan

			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END)	TanoraiKesesDarabszamIgazolando
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END)		TanoraiKesesDarabszamIgazolt
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END)		TanoraiKesesDarabszamIgazolatlan

			 ,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)						TanoraiKesesIgazolandoPerc
			 ,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)							TanoraiKesesIgazoltPerc
			 ,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)							TanoraiKesesIgazolatlanPerc

			 ,(SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben)
				+ (SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben) SzamitottKesesIgazolando
			 ,(SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben)
				+ (SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben)	SzamitottKesesIgazolt
			 ,(SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben)
				+ SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben		SzamitottKesesIgazolatlan
		 '
	END

	SET @sql +=
			',ISNULL(h.[hozottIgazolatlanHianyzas], 0)						HozottIgazolatlanHianyzas
			 ,ISNULL(h.[hozottIgazolatlanKesesPerc], 0)						HozottIgazolatlanKesesPerc
			 ,ISNULL(h.[hozottIgazoltHianyzas], 0)							HozottIgazoltHianyzas
			 ,ISNULL(h.[hozottIgazoltHianyzasPerc], 0)						HozottIgazoltHianyzasPerc

			 ,SUM(CASE IgazolasTipusa WHEN 1530 THEN 1 ELSE 0 END) OrvosiIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 1531 THEN 1 ELSE 0 END) SzuloiIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 1532 THEN 1 ELSE 0 END) HivatalosTavolletIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 1533 THEN 1 ELSE 0 END) IskolaerdekuTavolletIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 6494 THEN 1 ELSE 0 END) SzolgaltatoiIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 6495 THEN 1 ELSE 0 END) IskolaiEngedelyIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 6967 THEN 1 ELSE 0 END) PalyavalasztasiCeluIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 6834 THEN 1 ELSE 0 END) KikeroIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 6853 THEN 1 ELSE 0 END) TappenzIgazolas
			 ,SUM(CASE IgazolasTipusa WHEN 1529 THEN 1 ELSE 0 END) EgyebIgazolas

		FROM dbo.fnGetDokumentumMulasztasokOsztalyonkentReszletes(@tanevId, @osztalyId, @intezmenyId, DEFAULT, 0, DEFAULT, DEFAULT, 0) TanulokMulasztasai
			INNER JOIN T_TANTARGY_OSSZES Tantargy ON Tantargy.Id = TanulokMulasztasai.TantargyId
			LEFT JOIN (
				SELECT
					C_TANULOID							tanuloId
					,[C_HOZOTTIGAZOLATLANHIANYZAS]		hozottIgazolatlanHianyzas
					,[C_HOZOTTIGAZOLATLANKESESPERCBE]	hozottIgazolatlanKesesPerc
					,[C_HOZOTTIGAZOLTHIANYZAS]			hozottIgazoltHianyzas
					,[C_HOZOTTIGAZOLTKESESPERCBEN]		hozottIgazoltHianyzasPerc
				FROM
					T_TANULOTANUGYIADATOK_OSSZES ta
					INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID = ta.C_TANULOCSOPORTID
				WHERE
					tcs.TOROLT = ''F''
					AND ta.TOROLT = ''F''
					AND ta.C_TANEVID = @tanevId
			) h ON h.tanuloId = TanulokMulasztasai.TanuloId
			RIGHT OUTER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = TanulokMulasztasai.TanuloId
			INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = tcs.C_TANULOID
		WHERE
			tcs.C_TANEVID = @tanevId
			AND tcs.C_OSZTALYCSOPORTID = @osztalyId
			AND Tantargy.TOROLT = ''F''
			AND tcs.TOROLT = ''F''
			AND f.TOROLT = ''F''
		GROUP BY
			TanulokMulasztasai.TanuloId
			,tcs.C_TANULOID
			,C_SZULETESINEV
			,C_OKTATASIAZONOSITO
			,ISNULL(h.[hozottIgazolatlanHianyzas], 0)
			,ISNULL(h.[hozottIgazolatlanKesesPerc], 0)
			,ISNULL(h.[hozottIgazoltHianyzas], 0)
			,ISNULL(h.[hozottIgazoltHianyzasPerc], 0)
		ORDER BY C_SZULETESINEV'

	EXEC sp_executesql @sql, N'
		 @intezmenyId	INT
		,@tanevId		INT
		,@osztalyId		INT
		,@elmeletiOraPercben INT
		,@gyakorlatiOraPercben INT
		,@osztalyNev NVARCHAR(200)'
		,@intezmenyId = @intezmenyId
		,@tanevId = @tanevId
		,@osztalyId = @osztalyId
		,@elmeletiOraPercben = @elmeletiOraPercben
		,@gyakorlatiOraPercben = @gyakorlatiOraPercben
		,@osztalyNev = @osztalyNev

END
GO
