﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

CREATE PROCEDURE [dbo].[sp_GetStatPedagogusTanuloinakTantargyiMulasztasaiMegtartott]
	@tanarId	INT,
	@tanevId	INT,
	@iskolaErekeduTavolletId INT
AS
BEGIN
	SET NOCOUNT ON;

--Tanár
DECLARE @osztalyCsoportTantargy TABLE (osztalyCsoport INT, Tantargy INT)
INSERT INTO @osztalyCsoportTantargy
SELECT DISTINCT 
	C_OSZTALYCSOPORTID 
	,C_TANTARGYID
FROM T_ORARENDIORA_OSSZES 
WHERE 
	C_TANARID=@tanarId
	AND TOROLT = 'F'

--Tanulók
CREATE TABLE #students (TanuloId INT)
INSERT INTO #students
SELECT DISTINCT 
	C_TANULOID
FROM T_TANULOCSOPORT_OSSZES 
WHERE C_OSZTALYCSOPORTID IN (SELECT osztalyCsoport FROM @osztalyCsoportTantargy)
		AND TOROLT = 'F'

--Taulók mulasztásai
CREATE TABLE #mulasztas (Ossz INT, Tanulo INT, Tantargy INT, OsztalyCsoport INT)
INSERT INTO #mulasztas
SELECT 
	COUNT(tm.ID)
	, tm.C_ORATANULOIID
	, t.ID
	, tao.C_OSZTALYCSOPORTID 
FROM T_TANULOMULASZTAS_OSSZES tm
	INNER JOIN T_TANITASIORA_OSSZES tao on tao.ID = tm.C_TANITASIORAKID	
	INNER JOIN T_TANTARGY_OSSZES t on t.ID = tao.C_TANTARGYID
	INNER JOIN #students s on s.TanuloID = tm.C_ORATANULOIID
	INNER JOIN @osztalyCsoportTantargy ocst on ocst.osztalyCsoport=tao.C_OSZTALYCSOPORTID and ocst.Tantargy=tao.C_TANTARGYID
WHERE 
		tm.Torolt = 'F' 
		AND tm.C_TIPUS = 1500
		AND ((tao.C_OSZTALYCSOPORTID IN (SELECT ID FROM T_OSZTALY_OSSZES)) OR (tao.C_OSZTALYCSOPORTID IN (SELECT ID FROM T_CSOPORT_OSSZES WHERE C_TIPUSA=1034)))
		AND tm.C_IGAZOLASTIPUSA <> @iskolaErekeduTavolletId
GROUP BY tm.C_ORATANULOIID, t.ID, tao.C_OSZTALYCSOPORTID

--Naplózott óra tanulónként
CREATE TABLE #naplozott (Tanulo INT, Tantargy INT, OsztalyCsoport INT, Ossz INT)
INSERT INTO #naplozott 
SELECT 
	m.Tanulo
	, m.Tantargy
	, m.OsztalyCsoport
	, COUNT(DISTINCT tao.ID) 
FROM #mulasztas m
	INNER JOIN T_TANITASIORA_OSSZES tao ON tao.C_OSZTALYCSOPORTID=m.OsztalyCsoport AND tao.C_TANTARGYID=m.Tantargy AND tao.C_MEGTARTOTT='T'
	INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID=m.Tanulo AND tcs.C_OSZTALYCSOPORTID=m.OsztalyCsoport
	AND ((tao.C_OSZTALYCSOPORTID IN (SELECT ID FROM T_OSZTALY_OSSZES)) OR (tao.C_OSZTALYCSOPORTID IN (SELECT ID FROM T_CSOPORT_OSSZES WHERE C_TIPUSA=1034)))
	INNER JOIN @osztalyCsoportTantargy ocst on ocst.osztalyCsoport=tao.C_OSZTALYCSOPORTID and ocst.Tantargy=tao.C_TANTARGYID
WHERE 
	tao.TOROLT='F' 
	AND tao.C_DATUM >= tcs.C_BELEPESDATUM
GROUP BY m.Tanulo, m.Tantargy, m.OsztalyCsoport

DECLARE @cols AS NVARCHAR(MAX),
		@query AS NVARCHAR(MAX), 
		@colsIn AS NVARCHAR(MAX)

SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Tantargy) FROM (SELECT DISTINCT m.Tantargy Tantargy FROM #mulasztas m) Tantargy
SELECT @colsIn= ISNULL(@colsIn + ',','') +'CONVERT(NVARCHAR(MAX),'+QUOTENAME(Tantargy) + ')+''%'' as ' + QUOTENAME(nev) FROM (SELECT DISTINCT m.Tantargy AS Tantargy, t.c_nev nev FROM #mulasztas m
																			INNER JOIN T_TANTARGY_OSSZES t ON t.id=m.Tantargy) TantargyCols

set @query='
			SELECT 
				fel.C_NYOMTATASINEV Tanulo
				, '+@colsIn+' 
			FROM (SELECT 
					mulasztas.Tanulo
					, ROUND(CONVERT(FLOAT, mulasztas.Ossz)/CONVERT(FLOAT, naplozott.Ossz)*100, 0) Osszes
					, mulasztas.Tantargy 
				FROM #students s				
				INNER JOIN #mulasztas mulasztas on mulasztas.Tanulo=s.TanuloId
				INNER JOIN #naplozott naplozott on naplozott.Tantargy=mulasztas.Tantargy and naplozott.OsztalyCsoport=mulasztas.OsztalyCsoport and naplozott.Tanulo=mulasztas.Tanulo
				)a
			PIVOT (MAX(Osszes) FOR Tantargy IN ('+@cols+'))pv
				INNER JOIN T_FELHASZNALO_OSSZES fel on fel.ID=Tanulo
			ORDER BY fel.C_NYOMTATASINEV
			'
EXEC sp_executesql @query

DROP TABLE #students
DROP TABLE #mulasztas
DROP TABLE #naplozott

END