145 lines
4.7 KiB
Transact-SQL
145 lines
4.7 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
DROP PROCEDURE IF EXISTS uspGetNyomtatvanyokOrarendOsszes
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetNyomtatvanyokOrarendOsszes
|
|
@intezmenyId int
|
|
,@tanevID int
|
|
,@pIdoszakKezdete datetime
|
|
,@pIdoszakVege datetime
|
|
,@pTanarId int = NULL
|
|
,@pIsHelyettesitesNelkul bit = 0
|
|
,@pOsztalyCsoportId int = NULL
|
|
,@pTanuloId int = NULL
|
|
,@pTantargyId int = NULL
|
|
,@pTeremId int = NULL
|
|
,@pCsakOrarendiOrak bit = 0
|
|
,@pIsNapirend bit /*0 csak orarendi ora, 1 csak napirend, NULL mindkett?*/
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
CREATE TABLE #OrarendTable (
|
|
Id int
|
|
,ORARENDIID int
|
|
,Datum datetime
|
|
,Bontott char(1)
|
|
,Hetirend int
|
|
,HetNapja int
|
|
,HetSorszam int
|
|
,ErvenyessegKezdete datetime
|
|
,ErvenyessegVege datetime
|
|
,OraKezdete datetime
|
|
,OraVege datetime
|
|
,Oraszam int
|
|
,TargyNev nvarchar(300)
|
|
,TargyNevForMobile nvarchar(300)
|
|
,TargykategoriaID int
|
|
,Megtartott char(1)
|
|
,CsengetesiRendOraID int
|
|
,CsengetesiRendID int
|
|
,MaxNapiOraszam int
|
|
,OsztalyNev nvarchar(255)
|
|
,TanevRendOsztalyCsoportId int
|
|
,MegjelenesOka nvarchar(300)
|
|
,TanarNev nvarchar(255)
|
|
,TeremNev nvarchar(255)
|
|
,TeremId int
|
|
,OraTipus nvarchar(200)
|
|
,Hianyzas char(1)
|
|
,Keses char(1)
|
|
,Ures char(1)
|
|
,HelyettesitoTanarID int
|
|
,HelyettesitoTanarNev nvarchar(255)
|
|
,HelyettesitesId int
|
|
,TanarID int
|
|
,AdminAltalKiirt char(1)
|
|
,GroupId int
|
|
,Tema nvarchar(max)
|
|
,TantargyId int
|
|
,OsztCsopId int
|
|
);
|
|
|
|
INSERT INTO #OrarendTable
|
|
EXEC sp_GetOrarend
|
|
@pIntezmenyId = @intezmenyId
|
|
,@pTanevId = @tanevID
|
|
,@pIdoszakKezdete = @pIdoszakKezdete
|
|
,@pIdoszakVege = @pIdoszakVege
|
|
,@pTanarId = @pTanarId
|
|
,@pIsHelyettesitesNelkul = @pIsHelyettesitesNelkul
|
|
,@pOsztalyCsoportId = @pOsztalyCsoportId
|
|
,@pTanuloId = @pTanuloId
|
|
,@pTantargyId = @pTantargyId
|
|
,@pTeremId = @pTeremId
|
|
,@pCsakOrarendiOrak = @pCsakOrarendiOrak
|
|
,@pIsNapirend = @pIsNapirend
|
|
|
|
declare @intezmenyNev NVARCHAR(510) = (SELECT C_NEV FROM T_INTEZMENYADATOK_OSSZES WHERE C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevID and TOROLT = 'F')
|
|
|
|
CREATE TABLE #Oraszamok (Oraszam int, HetirendId int, PRIMARY KEY(Oraszam, HetirendId))
|
|
INSERT INTO #Oraszamok
|
|
SELECT DISTINCT
|
|
C_ORASZAM
|
|
,C_HETIREND
|
|
FROM T_ORARENDIORA_OSSZES
|
|
WHERE C_INTEZMENYID = @intezmenyId
|
|
AND C_TANEVID = @tanevID
|
|
AND C_ORASZAM IS NOT NULL
|
|
|
|
SELECT DISTINCT
|
|
COALESCE(@pTanarId, @pTeremId, @pOsztalyCsoportId) as Id
|
|
,STUFF((SELECT DISTINCT
|
|
', ' + IIF(@pTanarId IS NOT NULL, bo.TanarNev, IIF(@pTeremId IS NOT NULL, bo.TeremNev, IIF(@pOsztalyCsoportId IS NOT NULL, bo.OsztalyNev,'')))
|
|
FROM #OrarendTable bo
|
|
WHERE bo.Hetirend = o.Hetirend
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,2,'') as Nev
|
|
,@intezmenyNev as IntezmenyNev
|
|
,dic.C_NAME as Hetirend
|
|
FROM #OrarendTable o
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES dic on o.Hetirend = dic.ID and dic.C_TANEVID = @tanevID
|
|
|
|
SELECT
|
|
COALESCE(@pTanarId, @pTeremId, @pOsztalyCsoportId) as Id
|
|
,IIF(@pIsNapirend = 0, CAST(osz.Oraszam as NVARCHAR(3)), ISNULL(t.OraKezdete + ' - ' + t.OraVege, '')) as Ora
|
|
,IIF(@pIsNapirend = 0, osz.HetirendId, t.Hetirend) as HetirendId
|
|
,[1408] AS Hetfo
|
|
,[1409] AS Kedd
|
|
,[1410] AS Szerda
|
|
,[1411] AS Csutortok
|
|
,[1412] AS Pentek
|
|
,[1413] AS Szombat
|
|
,dic.C_NAME as HetirendNev
|
|
FROM ( SELECT *
|
|
FROM
|
|
( SELECT
|
|
STUFF(( SELECT
|
|
CONVERT(NVARCHAR(MAX),bo.TargyNev + NCHAR(13) + NCHAR(10) + bo.OsztalyNev + NCHAR(13) + NCHAR(10) + bo.TeremNev + NCHAR(10) + NCHAR(10))
|
|
FROM #OrarendTable bo
|
|
WHERE bo.OraKezdete = o.OraKezdete
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,0,'') as TargyNev
|
|
,HetNapja
|
|
,Oraszam
|
|
,CONVERT(CHAR(5), OraKezdete, 108) OraKezdete
|
|
,CONVERT(CHAR(5), OraVege, 108) OraVege
|
|
,Hetirend
|
|
FROM #OrarendTable o
|
|
) as ot
|
|
PIVOT (MAX(ot.TargyNev) FOR ot.HetNapja in([1408],[1409],[1410],[1411],[1412],[1413])) as pv
|
|
) as t
|
|
FULL JOIN #Oraszamok osz ON osz.Oraszam = t.Oraszam and t.Hetirend = osz.HetirendId
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dic ON dic.C_TANEVID = @tanevID and dic.TOROLT = 'F'
|
|
WHERE (@pIsNapirend = 0 AND osz.HetirendId = dic.ID) OR t.Hetirend = dic.ID
|
|
ORDER BY IIF(@pIsNapirend = 0, osz.HetirendId, t.Hetirend)
|
|
,CASE WHEN @pIsNapirend = 0 then osz.Oraszam else CAST(t.OraKezdete AS DATETIME) END
|
|
|
|
END
|
|
|
|
GO |