122 lines
5.5 KiB
Transact-SQL
122 lines
5.5 KiB
Transact-SQL
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
IF OBJECT_ID('[dbo].[sp_GetTeremOrarend]') IS NOT NULL
|
||
BEGIN
|
||
DROP PROCEDURE [dbo].[sp_GetTeremOrarend]
|
||
END
|
||
GO
|
||
|
||
CREATE PROCEDURE [dbo].[sp_GetTeremOrarend]
|
||
@TeremId int,
|
||
@TanevId int
|
||
AS
|
||
BEGIN
|
||
-- SET NOCOUNT ON added to prevent extra result sets from
|
||
-- interfering with SELECT statements.
|
||
-- Insert statements for procedure here
|
||
-- <20>rarend
|
||
|
||
IF OBJECT_ID('tempdb..#tempszorzat') IS NOT NULL drop table #tempszorzat
|
||
IF OBJECT_ID('tempdb..#orarend') IS NOT NULL drop table #orarend
|
||
|
||
SELECT
|
||
T_INTEZMENYADATOK_OSSZES.C_NEV IntezmenyNeve
|
||
,T_INTEZMENYADATOK_OSSZES.C_OMKOD IntezmenyOMKod
|
||
FROM T_INTEZMENYADATOK_OSSZES
|
||
WHERE
|
||
T_INTEZMENYADATOK_OSSZES.TOROLT = 'F'
|
||
AND T_INTEZMENYADATOK_OSSZES.C_TANEVID = @TanevId
|
||
|
||
DECLARE @intezmenyid int = (SELECT C_INTEZMENYID FROM T_TEREM_OSSZES WHERE id = @TeremId)
|
||
DECLARE @AktivtanevId INT = (SELECT ID FROM T_TANEV_OSSZES TANEV WHERE C_AKTIV='T' AND C_INTEZMENYID=@IntezmenyId)
|
||
|
||
DECLARE @UtolsoNap DATE = (SELECT TOP 1 C_NAPDATUMA FROM T_NAPTARINAP_OSSZES WHERE C_NAPTIPUSA=1395 AND C_INTEZMENYID=@intezmenyId AND C_TANEVID=@tanevid AND TOROLT='F')
|
||
|
||
DECLARE @HetEleje DATE
|
||
DECLARE @HetVege DATE
|
||
|
||
IF (@tanevId = @AktivtanevId AND GETDATE() <= @UtolsoNap)
|
||
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=@tanevid and nh.C_INTEZMENYID=@intezmenyId)
|
||
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=@tanevid and nh.C_INTEZMENYID=@intezmenyId)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
|
||
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=@tanevId AND NH.C_INTEZMENYID=@intezmenyId)
|
||
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=@tanevId AND NH.C_INTEZMENYID=@intezmenyId)
|
||
END
|
||
|
||
DECLARE @tmpTable TABLE (oraszam INT,hetirend INT, [1408] NVARCHAR(MAX), [1409] NVARCHAR(MAX), [1410] NVARCHAR(MAX), [1411] NVARCHAR(MAX), [1412] NVARCHAR(MAX), [1413] NVARCHAR(MAX))
|
||
SELECT * INTO #tempszorzat FROM (SELECT ID AS Hetnapja FROM T_DICTIONARYITEMBASE_OSSZES WHERE id BETWEEN 1408 AND 1413 AND torolt = 'F' AND C_TANEVID = @TanevId) napok,(SELECT C_oraszam AS Oraszam FROM T_ORARENDIORA_OSSZES WHERE TOROLT = 'F' AND C_TANEVID = @TanevId AND C_INTEZMENYID=@intezmenyid) as oraszam
|
||
|
||
|
||
DECLARE @ID INT = 0
|
||
WHILE (1 = 1)
|
||
BEGIN
|
||
IF OBJECT_ID('tempdb..#orarend') IS NOT NULL drop table #orarend
|
||
SELECT TOP 1 @ID = ID FROM T_HETIRENDTIPUS_OSSZES WHERE TOROLT ='F' and ID != 1554 and ID > @ID and C_ALTANEVID = @TanevId ORDER BY ID
|
||
IF @@ROWCOUNT = 0 BREAK;
|
||
|
||
|
||
SELECT #tempszorzat.Hetnapja,#tempszorzat.Oraszam,
|
||
(case
|
||
WHEN orak.HetiRend is null
|
||
THEN @ID
|
||
ELSE orak.HetiRend
|
||
END) as Hetirend
|
||
,orak.ADAT Into #orarend from #tempszorzat
|
||
left join (
|
||
SELECT ora.C_HETIREND HetiRend,
|
||
ora.C_HETNAPJA HetNapja,
|
||
ora.C_ORASZAM Oraszam,
|
||
STUFF((select ', '+t.c_nev+'- '+f.c_nev
|
||
from T_ORARENDIORA_OSSZES bo
|
||
inner join T_FOGLALKOZAS_OSSZES f on f.id=bo.C_FOGLALKOZASID
|
||
inner join T_TEREM_OSSZES t on t.id=bo.c_teremid
|
||
where
|
||
bo.C_HETNAPJA=ora.C_HETNAPJA and
|
||
bo.C_ORASZAM=ora.C_ORASZAM and
|
||
bo.C_HETIREND=ora.C_HETIREND AND
|
||
((bo.C_ORAERVENYESSEGKEZDETE<=@HetEleje) and (bo.C_ORAERVENYESSEGVEGE>=@HetVege)or
|
||
(bo.C_ORAERVENYESSEGKEZDETE>=@HetEleje) and (bo.C_ORAERVENYESSEGKEZDETE<=@HetVege))
|
||
AND bo.C_TEREMID=@teremId
|
||
AND bo.torolt='F'
|
||
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Adat
|
||
FROM T_ORARENDIORA_OSSZES ora
|
||
JOIN T_FOGLALKOZAS_OSSZES ON T_FOGLALKOZAS_OSSZES.ID = ora.C_FOGLALKOZASID
|
||
JOIN T_TANTARGY_OSSZES targy ON targy.ID = T_FOGLALKOZAS_OSSZES.C_TANTARGYID
|
||
JOIN T_FOGLALKOZASOK_TANAROK ON T_FOGLALKOZAS_OSSZES.ID = T_FOGLALKOZASOK_TANAROK.C_FOGLALKOZASOKID
|
||
JOIN T_FELHASZNALO_OSSZES tanar ON T_FOGLALKOZASOK_TANAROK.C_TANAROKID = tanar.ID
|
||
JOIN T_OSZTALYCSOPORT_OSSZES OSZTCSOP ON OSZTCSOP.ID = T_FOGLALKOZAS_OSSZES.C_OSZTALYCSOPORTID
|
||
WHERE ora.TOROLT = 'F'
|
||
AND ora.C_TEREMID = @TeremId
|
||
AND ora.C_TANEVID = @TanevId
|
||
GROUP BY ora.C_HETIREND, ora.C_HETNAPJA, ora.C_ORASZAM,OSZTCSOP.C_NEV, targy.C_NEV, tanar.C_NYOMTATASINEV,T_FOGLALKOZAS_OSSZES.ID) orak on #tempszorzat.Oraszam = orak.Oraszam and (#tempszorzat.HetNapja = orak.HetNapja or orak.HetNapja = 1554)
|
||
|
||
INSERT INTO @tmpTable
|
||
SELECT * FROM
|
||
(
|
||
SELECT oraszam,hetnapja,adat, @ID AS Hetirend
|
||
FROM #orarend WHERE Hetirend = @ID OR Hetirend = 1554
|
||
|
||
) o1
|
||
PIVOT
|
||
(
|
||
MAX (adat)
|
||
FOR hetnapja
|
||
IN ([1408], [1409], [1410], [1411], [1412], [1413])
|
||
) p1
|
||
|
||
END
|
||
|
||
SELECT * FROM @tmpTable
|
||
IF OBJECT_ID('tempdb..#tempszorzat') IS NOT NULL DROP TABLE #tempszorzat
|
||
IF OBJECT_ID('tempdb..#orarend') IS NOT NULL DROP TABLE #orarend
|
||
|
||
END
|
||
|
||
GO |