200 lines
6.6 KiB
Transact-SQL
200 lines
6.6 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetPedagogusOrarend]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetPedagogusOrarend]
|
|
END
|
|
GO
|
|
-- =============================================
|
|
-- Author: Dőrr Tamás
|
|
-- Create date: 2015.12.16.
|
|
-- Description: sp_GetPedagogusOrarend
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_GetPedagogusOrarend]
|
|
@pPedagogusId INT,
|
|
@pTanevId INT,
|
|
@pIntezmenyId INT,
|
|
@pReszletes BIT = 0
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
/*Intézmény és tanár név*/
|
|
DECLARE @aktivTanevId INT = (SELECT ID FROM T_TANEV_OSSZES WHERE C_AKTIV='T' AND C_INTEZMENYID=@pIntezmenyId)
|
|
|
|
DECLARE @hetEleje DATE
|
|
DECLARE @hetVege DATE
|
|
|
|
IF (@pTanevId = @aktivTanevId)
|
|
BEGIN
|
|
SET @hetEleje = (
|
|
SELECT TOP 1 nh.C_HETKEZDONAPJA FROM T_NAPTARIHET_OSSZES nh
|
|
WHERE
|
|
CONVERT(DATE, GETDATE()) >= CONVERT(DATE, NH.C_HETKEZDONAPJA)
|
|
AND CONVERT(DATE, GETDATE()) <= CONVERT(DATE, nh.C_HETUTOLSONAPJA)
|
|
AND nh.TOROLT = 'F'
|
|
AND nh.C_TANEVID = @pTanevId
|
|
AND nh.C_INTEZMENYID = @pIntezmenyId
|
|
)
|
|
|
|
SET @hetVege = (
|
|
SELECT TOP 1 nh.C_HETUTOLSONAPJA FROM T_NAPTARIHET_OSSZES nh
|
|
WHERE
|
|
CONVERT(DATE, GETDATE()) >= CONVERT(DATE, nh.C_HETKEZDONAPJA)
|
|
AND CONVERT(DATE, GETDATE()) <= CONVERT(DATE, nh.C_HETUTOLSONAPJA)
|
|
AND nh.TOROLT = 'F'
|
|
AND nh.C_TANEVID = @pTanevId
|
|
AND nh.C_INTEZMENYID = @pIntezmenyId
|
|
)
|
|
END
|
|
|
|
ELSE
|
|
|
|
BEGIN
|
|
DECLARE @utolsoNap DATE = (
|
|
SELECT TOP 1 C_NAPDATUMA FROM T_NAPTARINAP_OSSZES
|
|
WHERE
|
|
C_NAPTIPUSA = 1395
|
|
AND C_INTEZMENYID = @pIntezmenyId
|
|
AND C_TANEVID = @pTanevId
|
|
AND TOROLT = 'F'
|
|
)
|
|
|
|
SET @hetEleje = (
|
|
SELECT TOP 1 nh.C_HETKEZDONAPJA FROM T_NAPTARIHET_OSSZES nh
|
|
WHERE
|
|
@utolsoNap >= CONVERT(DATE, nh.C_HETKEZDONAPJA)
|
|
AND @utolsoNap <= CONVERT(DATE, nh.C_HETUTOLSONAPJA)
|
|
AND nh.TOROLT = 'F'
|
|
AND nh.C_TANEVID = @pTanevId
|
|
AND NH.C_INTEZMENYID = @pIntezmenyId
|
|
)
|
|
|
|
SET @hetVege = (
|
|
SELECT TOP 1 nh.C_HETUTOLSONAPJA FROM T_NAPTARIHET_OSSZES nh
|
|
WHERE
|
|
@utolsoNap >= CONVERT(DATE, nh.C_HETKEZDONAPJA)
|
|
AND @utolsoNap <= CONVERT(DATE, nh.C_HETUTOLSONAPJA)
|
|
AND nh.TOROLT = 'F'
|
|
AND nh.C_TANEVID = @pTanevId
|
|
AND NH.C_INTEZMENYID = @pIntezmenyId
|
|
)
|
|
END
|
|
|
|
IF @preszletes = 1
|
|
BEGIN
|
|
SELECT TOP(1)
|
|
f.C_NYOMTATASINEV as TANAR_NEV
|
|
,i.C_NEV as INTEZMENY_NEV
|
|
,i.C_OMKOD as INTEZMENY_OMKOD
|
|
FROM T_ALKALMAZOTT_OSSZES a
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = a.ID AND f.TOROLT = 'F'
|
|
INNER JOIN T_FOGLALKOZASOK_TANAROK ON T_FOGLALKOZASOK_TANAROK.C_TANAROKID = a.ID
|
|
INNER JOIN T_FOGLALKOZAS_OSSZES fo ON fo.ID = T_FOGLALKOZASOK_TANAROK.C_FOGLALKOZASOKID AND fo.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = fo.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES i ON i.C_INTEZMENYID = a.C_ALINTEZMENYID AND i.C_TANEVID = a.C_ALTANEVID AND i.TOROLT = 'F'
|
|
WHERE
|
|
a.ID = @pPedagogusId
|
|
AND a.C_ALTANEVID = @pTanevId
|
|
AND i.C_INTEZMENYID = @pIntezmenyId
|
|
AND a.TOROLT = 'F'
|
|
END
|
|
|
|
DECLARE @tmpTable TABLE (
|
|
TanarId INT
|
|
,Ora INT
|
|
,Hetirend INT
|
|
,TanarNev NVARCHAR(255)
|
|
,[1408] NVARCHAR(255)
|
|
,[1409] NVARCHAR(255)
|
|
,[1410] NVARCHAR(255)
|
|
,[1411] NVARCHAR(255)
|
|
,[1412] NVARCHAR(255)
|
|
,[1413] NVARCHAR(255)
|
|
)
|
|
|
|
DECLARE @ID INT = 0
|
|
WHILE (1 = 1)
|
|
BEGIN
|
|
SELECT TOP 1 @ID = ID FROM T_HETIRENDTIPUS_OSSZES WHERE TOROLT ='F' AND ID != 1554 AND ID > @ID AND C_ALTANEVID = @pTanevId ORDER BY ID
|
|
IF @@ROWCOUNT = 0 BREAK;
|
|
DECLARE @Napok TABLE (NapId INT)
|
|
INSERT INTO @Napok SELECT ID FROM T_DICTIONARYITEMBASE_OSSZES WHERE ID in (1408,1409,1410,1411,1412,1413) AND C_TANEVID = @pTanevId
|
|
|
|
DECLARE @Orak TABLE (OraId INT)
|
|
INSERT INTO @Orak SELECT C_ORASZAM FROM T_CSENGETESIRENDORA WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId AND C_ORASZAM<=10
|
|
|
|
DECLARE @TenylegesOrak TABLE (OraId INT, NapId INT, Leiras NVARCHAR(MAX), HetirendID INT)
|
|
INSERT INTO @TenylegesOrak
|
|
SELECT
|
|
csro.C_ORASZAM as ORASZAM
|
|
,oo.C_HETNAPJA as HETNAP,
|
|
STUFF((select ', ' + bocs.C_NEV + ' - ' + btt.C_NEV + ' - ' + bt.C_NEV
|
|
FROM T_ALKALMAZOTT_OSSZES ba
|
|
INNER JOIN T_ORARENDIORA_OSSZES boo on boo.C_TANARID = ba.ID
|
|
AND boo.TOROLT='F'
|
|
AND ((boo.C_ORAERVENYESSEGKEZDETE<=@hetEleje)
|
|
AND (boo.c_oraervenyessegvege>=@hetVege)
|
|
OR (boo.C_ORAERVENYESSEGKEZDETE>=@hetEleje)
|
|
AND (boo.C_ORAERVENYESSEGKEZDETE<=@hetEleje))
|
|
AND oo.TOROLT='F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES bf on bf.ID = a.ID
|
|
INNER JOIN T_TANTARGY_OSSZES btt on btt.ID = boo.C_TANTARGYID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES bocs on bocs.ID =boo.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_TEREM_OSSZES bt on bt.ID = boo.C_TEREMID
|
|
LEFT JOIN T_CSENGETESIRENDORA_OSSZES bcsro on bcsro.ID = boo.C_CSENGETESIRENDORAID
|
|
WHERE
|
|
a.ID = ba.ID
|
|
and a.C_ALTANEVID = ba.C_ALTANEVID
|
|
and boo.C_HETIREND in (@ID,1554)
|
|
and bcsro.C_ORASZAM=csro.C_ORASZAM
|
|
and boo.C_HETNAPJA=oo.C_HETNAPJA FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') MATRIX_DATA
|
|
,@ID AS HetiRendID
|
|
FROM T_ALKALMAZOTT_OSSZES a
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.C_TANARID = a.ID
|
|
AND ((oo.C_ORAERVENYESSEGKEZDETE<=@hetEleje)
|
|
AND (oo.C_ORAERVENYESSEGVEGE>=@hetVege)
|
|
OR (oo.C_ORAERVENYESSEGKEZDETE>=@hetEleje)
|
|
AND (oo.C_ORAERVENYESSEGKEZDETE<=@hetVege))
|
|
AND oo.TOROLT='F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = a.ID
|
|
INNER JOIN T_TANTARGY_OSSZES tt ON tt.ID = oo.C_TANTARGYID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_TEREM_OSSZES t ON t.ID = oo.C_TEREMID
|
|
LEFT JOIN T_CSENGETESIRENDORA_OSSZES csro ON csro.ID = oo.C_CSENGETESIRENDORAID
|
|
WHERE
|
|
a.ID = @pPedagogusId
|
|
AND a.C_ALTANEVID = @pTanevId
|
|
AND oo.C_HETIREND IN (@ID,1554)
|
|
|
|
INSERT INTO @tmpTable
|
|
SELECT * FROM
|
|
(
|
|
SELECT
|
|
@pPedagogusId as TanarId,
|
|
O.OraId as Ora
|
|
,N.NapId as Nap
|
|
,T.Leiras as Leiras
|
|
,@ID as HetiRend
|
|
,(SELECT TOP 1 f.C_NYOMTATASINEV from T_FELHASZNALO_OSSZES f WHERE f.ID = @pPedagogusId AND f.C_TANEVID = @pTanevId AND f.TOROLT = 'F') as TanarNev
|
|
FROM @Napok N
|
|
CROSS JOIN @Orak O
|
|
LEFT JOIN @TenylegesOrak T on T.NapId = N.NapId AND T.OraId = O.OraId AND T.HetiRendID in (@ID,1554)
|
|
) o1
|
|
PIVOT
|
|
(
|
|
MAX (Leiras)
|
|
FOR [Nap]
|
|
IN ([1408], [1409], [1410], [1411], [1412], [1413])
|
|
) p1
|
|
END
|
|
|
|
SELECT * FROM @tmpTable
|
|
|
|
END
|
|
|
|
GO |