kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20170503114150_KRETA_1103/sp_GetMunkaidoElszamoloLap.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

CREATE PROCEDURE [dbo].[sp_GetMunkaidoElszamoloLap] 
	@tanarid INT,
	@TanevId INT,
	@honapId INT

AS 
  BEGIN 
	 SET NOCOUNT ON;

	DECLARE @Honap INT = (
		SELECT DISTINCT 
			dictionaryItemBase.C_VALUE 
		FROM 
			T_DICTIONARYITEMBASE_OSSZES dictionaryItemBase
		WHERE 
			dictionaryItemBase.ID = @honapId
	)

	SELECT TOP 1 
		DATEPART(YEAR, naptariNap.C_NAPDATUMA)		Ev, 
		DATEPART(MONTH, naptariNap.C_NAPDATUMA)		Honap 
	FROM 
		T_NAPTARINAP_OSSZES naptariNap 
	WHERE 
		DATEPART(MONTH,naptariNap.C_NAPDATUMA) = @Honap AND 
		naptariNap.C_TANEVID = @TanevId AND 
		naptariNap.TOROLT = 'F'

--===================================
--FEJLÉC
--===================================
	SELECT 
		IIF (T_INTEZMENYADATOK_OSSZES.C_ROVIDNEV IS NULL ,T_INTEZMENYADATOK_OSSZES.C_NEV, T_INTEZMENYADATOK_OSSZES.C_ROVIDNEV ) IntezmenyNeve
		,T_INTEZMENYADATOK_OSSZES.C_IRANYITOSZAM	IntezmenyIranyitoszam
		,T_INTEZMENYADATOK_OSSZES.C_VAROS			IntezmenyVaros
		,T_INTEZMENYADATOK_OSSZES.C_CIME			IntezmenyCime
		,T_INTEZMENYADATOK_OSSZES.C_TELEFONSZAM		TELEFON
		,T_INTEZMENYADATOK_OSSZES.C_EMAILCIM		EMAILCIM
		,T_INTEZMENYADATOK_OSSZES.C_OMKOD			IntezmenyOMKod
		,T_TANEV_OSSZES.C_NEV						TANEV
		,(SELECT C_NYOMTATASINEV FROM T_FELHASZNALO_OSSZES WHERE T_FELHASZNALO_OSSZES.ID = @tanarid AND T_FELHASZNALO_OSSZES.C_TANEVID = @TanevId AND T_FELHASZNALO_OSSZES.TOROLT='F') TanarNev
		,@tanarid									PartnerID
	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

--===================================
--MEGTARTOTT TANÓRÁK, HELYETTESÍTÉSEK
--===================================
	SELECT 
		OsztalyCsoportID
		,OsztalyCsoportNev
		,Honap
		,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],

		 [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+
		 [11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+
		 [21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31] Ossz

	FROM
	(
	SELECT 
		DATEPART(DAY, T_TANITASIORA_OSSZES.C_DATUM)		Nap
		,DATEPART(Month, T_TANITASIORA_OSSZES.C_DATUM)	Honap
		,T_OSZTALYCSOPORT_OSSZES.ID						OsztalyCsoportID
		,T_OSZTALYCSOPORT_OSSZES.ID						ocsId
		,T_OSZTALYCSOPORT_OSSZES.c_nev					OsztalyCsoportNev
	FROM T_TANITASIORA_OSSZES
		INNER JOIN T_FOGLALKOZAS_OSSZES on T_TANITASIORA_OSSZES.C_FOGLALKOZASID = T_FOGLALKOZAS_OSSZES.ID
		INNER JOIN T_FOGLALKOZASOK_TANAROK on T_FOGLALKOZAS_OSSZES.ID=T_FOGLALKOZASOK_TANAROK.C_FOGLALKOZASOKID
		INNER JOIN T_OSZTALYCSOPORT_OSSZES on T_FOGLALKOZAS_OSSZES.C_OSZTALYCSOPORTID = T_OSZTALYCSOPORT_OSSZES.ID
	WHERE 
		T_FOGLALKOZASOK_TANAROK.C_TANAROKID = @tanarid
		AND T_TANITASIORA_OSSZES.C_TANEVID = @TanevId
		AND DATEPART(MONTH, T_TANITASIORA_OSSZES.C_DATUM) = @Honap
		AND T_TANITASIORA_OSSZES.TOROLT = 'F' 
		AND T_FOGLALKOZAS_OSSZES.TOROLT = 'F' 
		AND T_OSZTALYCSOPORT_OSSZES.TOROLT = 'F'
		AND T_TANITASIORA_OSSZES.C_MEGTARTOTT = 'T'
		AND T_TANITASIORA_OSSZES.C_HELYETTESITOTANARID IS NULL
	)a
	PIVOT
	(
	COUNT(ocsId)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) 
	) OsszTanora 

--szakszerű helyettesítés
	SELECT 
		OsztalyCsoportID
		,OsztalyCsoportNev
		,Honap
		,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],

		 [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+
		 [11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+
		 [21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31] Ossz
	FROM
	(
	SELECT 
		DATEPART(DAY, tao.C_DATUM)		Nap
		,DATEPART(MONTH,tao.C_DATUM)	Honap
		,ocs.C_NEV						OsztalyCsoportNev
		,tao.C_OSZTALYCSOPORTID			oraid
		,tao.C_OSZTALYCSOPORTID			OsztalyCsoportID
	FROM T_TANITASIORA_OSSZES tao
		INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.id=tao.C_OSZTALYCSOPORTID
	WHERE 
		tao.C_HELYETTESITOTANARID = @tanarid 
		AND DATEPART(MONTH, tao.C_DATUM) = @Honap 
		AND tao.TOROLT = 'F' 
		AND tao.C_HELYETTESITESTIPUSA = 1494 
		AND tao.C_TANEVID = @TanevId
	)a
	PIVOT
	(
	COUNT(oraID)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
 
	) SzakszeruHelyettesites 

--nem szakszerű helyettesítés
	SELECT 
		OsztalyCsoportID
		,OsztalyCsoportNev
		,Honap
		,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],

		 [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+
		 [11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+
		 [21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31] Ossz
	FROM
	(
	SELECT 
			DATEPART(DAY, tao.C_DATUM)		Nap
			,DATEPART(MONTH,tao.C_DATUM)	Honap
			,ocs.C_NEV						OsztalyCsoportNev
			,tao.C_OSZTALYCSOPORTID			oraid
			,tao.C_OSZTALYCSOPORTID			OsztalyCsoportID
	FROM T_TANITASIORA_OSSZES tao
		INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.id=tao.C_OSZTALYCSOPORTID
	WHERE 
		tao.C_HELYETTESITOTANARID = @tanarid 
		AND DATEPART(MONTH, tao.C_DATUM) = @Honap 
		AND tao.TOROLT='F' 
		AND tao.C_HELYETTESITESTIPUSA = 1495 
		AND tao.C_TANEVID = @TanevId
	)a
	PIVOT
	(
	COUNT(oraID)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
 
	) NemSzakszeruHelyettesites 

--óraösszevonás
	SELECT 
		OsztalyCsoportID
		,OsztalyCsoportNev
		,Honap
		,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],

		 [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+
		 [11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+
		 [21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31] Ossz
	FROM
	(
	SELECT 
		DATEPART(DAY, tao.C_DATUM)	Nap
		,DATEPART(MONTH,tao.C_DATUM) Honap
		,ocs.C_NEV					OsztalyCsoportNev
		,tao.C_OSZTALYCSOPORTID		oraid
		,tao.C_OSZTALYCSOPORTID		OsztalyCsoportID
	FROM T_TANITASIORA_OSSZES tao
		INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID=tao.C_OSZTALYCSOPORTID
	WHERE tao.C_HELYETTESITOTANARID = @tanarid 
		AND DATEPART(MONTH, tao.C_DATUM) = @Honap 
		AND tao.TOROLT = 'F' 
		AND tao.C_HELYETTESITESTIPUSA = 1496 
		AND tao.C_TANEVID = @TanevId
	)a
	PIVOT
	(
	COUNT(oraID)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
 
	) Oraosszevonas 

--tanóránkívüli
	SELECT 
		OsztalyCsoportID
		,OsztalyCsoportNev
		,Honap
		,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],

		 [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+
		 [11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+
		 [21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31] Ossz
	FROM
	(
	SELECT 
		DATEPART(DAY, tao.C_DATUM)		Nap
		,DATEPART(MONTH,tao.C_DATUM)	Honap
		,ocs.C_NEV						OsztalyCsoportNev
		,tao.C_OSZTALYCSOPORTID			oraid
		,tao.C_OSZTALYCSOPORTID			OsztalyCsoportID
	FROM T_TANITASIORA_OSSZES tao
		INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.id=tao.C_OSZTALYCSOPORTID
		INNER JOIN T_FOGLALKOZAS_OSSZES fog on fog.id=tao.C_FOGLALKOZASID AND fog.C_FOGLALKOZASTIPUSA=1371
		INNER JOIN T_FOGLALKOZASOK_TANAROK ft on ft.C_FOGLALKOZASOKID=fog.ID AND ft.c_tanarokid=@tanarId
	WHERE 
		DATEPART(MONTH, tao.C_DATUM) = @Honap 
		AND tao.TOROLT = 'F' 
		AND tao.C_TANEVID = @TanevId 
	)a
	PIVOT
	(
	COUNT(oraID)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
 
	) TanoranKivuli 

--egyéni foglalkozas
	SELECT 
		OsztalyCsoportID
		,OsztalyCsoportNev
		,Honap
		,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],

		 [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+
		 [11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+
		 [21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31] Ossz
	FROM
	(
	SELECT 
		DATEPART(DAY, tao.C_DATUM)	Nap,
		DATEPART(MONTH,tao.C_DATUM) Honap,
		ocs.C_NEV					OsztalyCsoportNev,
		tao.C_OSZTALYCSOPORTID		oraid,
		tao.C_OSZTALYCSOPORTID		OsztalyCsoportID
	FROM T_TANITASIORA_OSSZES tao
		INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.id=tao.C_OSZTALYCSOPORTID
		INNER JOIN T_FOGLALKOZAS_OSSZES fog on fog.id=tao.C_FOGLALKOZASID AND fog.C_FOGLALKOZASTIPUSA=1371
		INNER JOIN T_FOGLALKOZASOK_TANAROK ft on ft.C_FOGLALKOZASOKID=fog.ID AND ft.C_TANAROKID=@tanarId
	WHERE DATEPART(MONTH, tao.C_DATUM) = @Honap 
			AND tao.TOROLT = 'F' 
			AND tao.C_TANEVID = @TanevId
	)a
	PIVOT
	(
	COUNT(oraID)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
 
	) Oraosszevonas 

--============================================
--NEVELÉSSEL-OKTATÁSSAL LE NEM KÖTÖTT MUNKAIDŐ
--============================================
--ÖSSZESEN
	SELECT  
		 Honap
		 ,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
		 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
		 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],
		 ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+
		 ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0)+
		 ISNULL([11],0)+ISNULL([12],0)+ISNULL([13],0)+ISNULL([14],0)+ISNULL([15],0)+
		 ISNULL([16],0)+ISNULL([17],0)+ISNULL([18],0)+ISNULL([19],0)+ISNULL([20],0)+
		 ISNULL([21],0)+ISNULL([22],0)+ISNULL([23],0)+ISNULL([24],0)+ISNULL([25],0)+
		 ISNULL([26],0)+ISNULL([27],0)+ISNULL([28],0)+ISNULL([29],0)+ISNULL([30],0)+ISNULL([31],0) Ossz
	FROM
	(
	SELECT 
		DATEPART(DAY, C_KEZDETE)	Nap
		,DATEPART(MONTH, C_KEZDETE)	Honap
		,ROUND(DATEPART(HOUR,C_VEGE-C_KEZDETE)+CONVERT(FLOAT,DATEPART(MINUTE,C_VEGE-C_KEZDETE))/60,1) Oraszam
	FROM T_NEMKOTOTTMUNKAIDO_OSSZES 
	WHERE 
		C_MEGTARTOTT = 'T'
		AND C_TANARID = @tanarId
		AND TOROLT = 'F'
		AND DATEPART(MONTH, C_KEZDETE)	= @Honap 
		AND DATEPART(MONTH, C_VEGE)		= @Honap
	) Orak
	PIVOT
	(
	SUM(Oraszam)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
	) Tevekenyseg

--a hónapban előforduló típusok
	SELECT DISTINCT 
		C_TEVEKENYSEGTIPUSA TipusId
		,d.C_NAME			Tipus
	FROM T_NEMKOTOTTMUNKAIDO_OSSZES 
	INNER JOIN (SELECT DISTINCT ID, C_NAME FROM T_DICTIONARYITEMBASE) d on d.ID=C_TEVEKENYSEGTIPUSA
	WHERE 
		C_MEGTARTOTT = 'T'
		AND C_TANARID = @tanarId
		AND TOROLT = 'F'
		AND DATEPART(MONTH, C_KEZDETE)	= @Honap 
		AND DATEPART(MONTH, C_VEGE)		= @Honap

--sorokra (tipusokra) lebontva
	SELECT  
	 Honap
	 ,Tipus TipusId
	 ,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
 	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31],
	 ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+
	 ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0)+
	 ISNULL([11],0)+ISNULL([12],0)+ISNULL([13],0)+ISNULL([14],0)+ISNULL([15],0)+
	 ISNULL([16],0)+ISNULL([17],0)+ISNULL([18],0)+ISNULL([19],0)+ISNULL([20],0)+
	 ISNULL([21],0)+ISNULL([22],0)+ISNULL([23],0)+ISNULL([24],0)+ISNULL([25],0)+
	 ISNULL([26],0)+ISNULL([27],0)+ISNULL([28],0)+ISNULL([29],0)+ISNULL([30],0)+ISNULL([31],0) Ossz
	FROM
	(
	SELECT 
		C_TEVEKENYSEGTIPUSA			Tipus
		,DATEPART(DAY, C_KEZDETE)	Nap
		,DATEPART(MONTH, C_KEZDETE)	Honap
		,ROUND(DATEPART(hour,C_VEGE-C_KEZDETE)+CONVERT(FLOAT,DATEPART(MINUTE,C_VEGE-C_KEZDETE))/60,1) Oraszam
	FROM T_NEMKOTOTTMUNKAIDO_OSSZES 
	WHERE 
		C_MEGTARTOTT='T'
		AND C_TANARID = @tanarId
		AND TOROLT = 'F'
		AND DATEPART(MONTH, C_KEZDETE)	= @Honap 
		AND DATEPART(MONTH, C_VEGE)		= @Honap
	) Orak
	PIVOT
	(
	SUM(Oraszam)
	FOR Nap IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
	 [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
	 [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
	) Soronkent

END

GO