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

-- ==========================================================================================
-- Description:	<Az osztoly/csoport tanulóinak vagy egy adott tanulónak a mulasztásai>
-- ==========================================================================================
CREATE PROCEDURE [dbo].[sp_GetTanulokMulasztasaiKesesei]
	 @pIntezmenyId		INT
	,@pTanevId			INT
	,@pOsztalyCsoportId INT = NULL
	,@pTanuloId			INT = NULL
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE 
		 @elmeletiOraPercben INT = 45
		,@gyakorlatiOraPercben INT = 60
		,@osztalyCsoportNev NVARCHAR(200)
	
	SELECT 
		@osztalyCsoportNev = C_NEV
	FROM 
		T_OSZTALYCSOPORT
	WHERE 
			ID = @pOsztalyCsoportId
		AND T_OSZTALYCSOPORT.TOROLT = 'F'

  
	DECLARE @Tanulok TABLE (Id INT PRIMARY KEY)
  
	IF @pTanuloId IS NOT NULL
	BEGIN
		INSERT INTO @Tanulok (Id) VALUES (@pTanuloId)
	END 
	ELSE BEGIN
		INSERT INTO 
			@Tanulok (Id)
		SELECT 
			C_TANULOID 
		FROM 
			T_TANULOCSOPORT_OSSZES tcs 
		WHERE 
				tcs.C_OSZTALYCSOPORTID = @pOsztalyCsoportId
			AND tcs.C_BELEPESDATUM <= GETDATE() 
			AND (tcs.C_KILEPESDATUM > GETDATE() OR tcs.C_KILEPESDATUM IS NULL)
            AND tcs.TOROLT = 'F'

	END  
  
	SELECT
		 fh.ID												TanuloId
		,fh.C_NYOMTATASINEV									TanuloNev
		,fh.C_SZULETESIDATUM								SzuletesiIdo
		,fh.C_ANYJANEVE 									AnyjaNeve
		,@osztalyCsoportNev									OsztCsopNev
		,ISNULL(m.[hianyzasIgazolandoTanorai], 0) 			TanoraiHianyzasIgazolando
		,ISNULL(m.[hianyzasIgazolandoTanoranKivuli], 0)		TanoranKivuliHianyzasIgazolando
		,ISNULL(m.[hianyzasIgazoltTanorai], 0)				TanoraiHianyzasIgazolt
		,ISNULL(m.[hianyzasIgazoltTanoranKivuli], 0)		TanoranKivuliHianyzasIgazolt
		,ISNULL(m.[hianyzasIgazolatlanTanorai], 0)			TanoraiHianyzasIgazolatlan
		,ISNULL(m.[hianyzasIgazolatlanTanoranKivuli], 0) 	TanoranKivuliHianyzasIgazolatlan
		,ISNULL(m.[kesesIgazolandoTanorai], 0) 				TanoraiKesesDarabszamIgazolando
		,ISNULL(m.[kesesIgazoltTanorai], 0)					TanoraiKesesDarabszamIgazolt
		,ISNULL(m.[kesesIgazolatlanTanorai], 0)				TanoraiKesesDarabszamIgazolatlan
		,ISNULL(k.[kesesIgazolandoTanoraiPerc], 0)			TanoraiKesesIgazolandoPerc
		,ISNULL(k.[kesesIgazoltTanoraiPerc], 0)				TanoraiKesesIgazoltPerc
		,ISNULL(k.[kesesIgazolatlanTanoraiPerc], 0)			TanoraiKesesIgazolatlanPerc
		,ISNULL(kp.[kesesIgazolandoTanoraiPluszOra], 0)		SzamitottKesesIgazolando
		,ISNULL(kp.[kesesIgazoltTanoraiPluszOra], 0)		SzamitottKesesIgazolt
		,ISNULL(kp.[kesesIgazolatlanTanoraiPluszOra], 0) 	SzamitottKesesIgazolatlan
	FROM (
		SELECT 
			 ID
			,C_NYOMTATASINEV
			,C_SZULETESIDATUM
			,C_ANYJANEVE
		FROM 
			T_FELHASZNALO_OSSZES fh
		WHERE 
			EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = fh.ID) 
	) fh
	LEFT JOIN (
		SELECT 
			 tanuloId 
			,[hianyzasIgazolandoTanorai] 
			,[hianyzasIgazolandoTanoranKivuli] 
			,[hianyzasIgazoltTanorai]
			,[hianyzasIgazoltTanoranKivuli]
			,[hianyzasIgazolatlanTanorai]
			,[hianyzasIgazolatlanTanoranKivuli]
			,[kesesIgazolandoTanorai]
			,[kesesIgazoltTanorai]
			,[kesesIgazolatlanTanorai]
		FROM (
			SELECT 
				 x.tanuloId
				,CASE mulasztasTipus WHEN 1500 THEN 'Hianyzas' WHEN 1499 THEN 'Keses' END +
				 CASE WHEN isIgazolt IS NULL THEN 'Igazolando' WHEN isIgazolt = 'T' THEN 'Igazolt' WHEN isIgazolt = 'F' THEN 'Igazolatlan' END +
				 CASE isTanorai WHEN 'T' THEN 'Tanorai' WHEN 'F' THEN 'TanoranKivuli' END AS piv
				,cnt
			FROM (
  			SELECT 
  				 m.C_ORATANULOIID AS tanuloId
  				,m.C_TIPUS mulasztasTipus
  				,m.C_IGAZOLT isIgazolt
  				,IIF(cs.ID IS NULL, 'T', 'F') AS isTanorai
  				,COUNT(1) AS cnt 
  			FROM T_TANULOMULASZTAS_OSSZES m  
    			INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
    			LEFT JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID AND cs.C_TIPUSA NOT IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@pTanevId))
          INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = tn.C_OSZTALYCSOPORTID AND m.C_ORATANULOIID = tcs.C_TANULOID AND tcs.TOROLT = 'F'
  			WHERE m.C_INTEZMENYID = @pIntezmenyId 
  				AND m.C_TANEVID = @pTanevId 
  				AND m.Torolt ='F'
  				AND EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = m.C_ORATANULOIID)
          AND tcs.C_BELEPESDATUM <= tn.C_DATUM 
          AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > tn.C_DATUM)
  			GROUP BY m.C_ORATANULOIID, m.C_TIPUS, m.C_IGAZOLT, IIF(cs.ID IS NULL, 'T', 'F')
			) x
		) p
		PIVOT (
			MAX(cnt)
			FOR piv IN ([hianyzasIgazolandoTanorai], [hianyzasIgazolandoTanoranKivuli], [hianyzasIgazoltTanorai], [hianyzasIgazoltTanoranKivuli], [hianyzasIgazolatlanTanorai], [hianyzasIgazolatlanTanoranKivuli], [kesesIgazolandoTanorai], [kesesIgazoltTanorai], [kesesIgazolatlanTanorai])
		) AS pvt
	) m ON fh.ID = m.tanuloId
	LEFT JOIN (
		SELECT 
			 tanuloId
			,[kesesIgazolandoTanoraiPerc]
			,[kesesIgazoltTanoraiPerc]
			,[kesesIgazolatlanTanoraiPerc] 
		FROM (
			SELECT 
				 x.tanuloId
				,'Keses' +
				 CASE WHEN isIgazolt IS NULL THEN 'Igazolando' WHEN isIgazolt = 'T' THEN 'Igazolt' WHEN isIgazolt = 'F' THEN 'Igazolatlan' END +
				 'TanoraiPerc' AS piv
				,kesesPercben
			FROM (
  			SELECT 
  				 m.C_ORATANULOIID AS tanuloId 
  				,m.C_IGAZOLT isIgazolt
  				,SUM(m.C_KESESPERCBEN) AS kesesPercben 
  			FROM T_TANULOMULASZTAS_OSSZES m  
    			INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
    			LEFT  JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID
          INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = tn.C_OSZTALYCSOPORTID AND m.C_ORATANULOIID = tcs.C_TANULOID AND tcs.TOROLT = 'F'
  			WHERE m.C_INTEZMENYID = @pIntezmenyId 
  				AND m.C_TANEVID = @pTanevId 
  				AND m.Torolt ='F'
  				AND EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = m.C_ORATANULOIID)
  				AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@pTanevId)))
          AND tcs.C_BELEPESDATUM <= tn.C_DATUM 
          AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > tn.C_DATUM)
  				AND m.C_TIPUS = 1499
  			GROUP BY m.C_ORATANULOIID, m.C_IGAZOLT
			) x
		) p
		PIVOT (
			MAX(kesesPercben)
			FOR piv IN ([kesesIgazolandoTanoraiPerc], [kesesIgazoltTanoraiPerc], [kesesIgazolatlanTanoraiPerc])
		) AS pvt 
	) k ON k.tanuloId = m.tanuloId
	LEFT JOIN (
		SELECT 
			 tanuloId
			,[kesesIgazolandoTanoraiPluszOra]
			,[kesesIgazoltTanoraiPluszOra]
			,[kesesIgazolatlanTanoraiPluszOra]
		FROM (
			SELECT 
				 x.tanuloId
				,'Keses' +
				 CASE WHEN isIgazolt IS NULL THEN 'Igazolando' WHEN isIgazolt = 'T' THEN 'Igazolt' WHEN isIgazolt = 'F' THEN 'Igazolatlan' END +
				 'TanoraiPluszOra' AS piv
				,kesesOraban
			FROM (
			SELECT
				 xx.tanuloId 
				,xx.isIgazolt
				,SUM(xx.keses / (CASE WHEN isGyakorlati = 'F' THEN @elmeletiOraPercben ELSE @gyakorlatiOraPercben END)) AS kesesOraban
			FROM (
				SELECT 
					 m.C_ORATANULOIID AS tanuloId 
					,m.C_IGAZOLT isIgazolt
					,t.C_GYAKORLATI AS isGyakorlati
					,SUM(m.C_KESESPERCBEN) AS keses
				FROM T_TANULOMULASZTAS_OSSZES m  
  				INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = m.C_TANITASIORAKID
  				INNER JOIN T_TANTARGY_OSSZES t ON t.ID = tn.C_TANTARGYID
  				LEFT  JOIN T_CSOPORT_OSSZES cs ON tn.C_OSZTALYCSOPORTID = cs.ID
          INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = tn.C_OSZTALYCSOPORTID AND m.C_ORATANULOIID = tcs.C_TANULOID AND tcs.TOROLT = 'F'
				WHERE m.C_INTEZMENYID = @pIntezmenyId
					AND m.C_TANEVID = @pTanevId
					AND EXISTS (SELECT 1 FROM @Tanulok tk WHERE tk.Id = m.C_ORATANULOIID)
					AND (cs.ID IS NULL OR cs.C_TIPUSA IN (SELECT ID FROM fnGetTanoraiCeluCsoportTipusok(@pTanevId)))
          AND tcs.C_BELEPESDATUM <= tn.C_DATUM 
          AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > tn.C_DATUM)
					AND m.C_TIPUS = 1499
					AND m.TOROLT = 'F'
				GROUP BY 
					 m.C_ORATANULOIID
					,m.C_IGAZOLT
					,t.C_GYAKORLATI
			) xx
			GROUP BY tanuloId, xx.isIgazolt
			) x
		) p
		PIVOT (
			MAX(kesesOraban)
			FOR piv IN ([kesesIgazolandoTanoraiPluszOra], [kesesIgazoltTanoraiPluszOra], [kesesIgazolatlanTanoraiPluszOra])
		) AS pvt
	) kp ON m.tanuloId = kp.tanuloId
END
GO


