55 lines
2.0 KiB
Transact-SQL
55 lines
2.0 KiB
Transact-SQL
-- ===================================================================================================
|
|
-- KIRSTAT - Felhasználó - Termek
|
|
-- ===================================================================================================
|
|
-- 5. MUNKALAP FELSŐ - A02T13 - OSZTÁLYTERMEK/ÓVODAI CSOPORTSZOBÁK/FÉRŐHELYEK NYITÓÁLLOMÁNYA
|
|
DROP PROCEDURE IF EXISTS uspKIRstat_FelhasznaloTerem
|
|
GO
|
|
|
|
CREATE PROCEDURE uspKIRstat_FelhasznaloTerem
|
|
@pFeladatellatasiHelyId int
|
|
,@pTanevId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@oktoberEgy datetime
|
|
|
|
SET @oktoberEgy = (SELECT LEFT(C_NEV, 4) FROM T_TANEV WHERE ID = @pTanevId) + '1001'
|
|
|
|
|
|
SELECT
|
|
tm.ID AS TeremId
|
|
,tm.C_NEV AS TeremNev
|
|
,CASE
|
|
WHEN tm.C_JELLEG IN (181, 182) THEN 'Osztályterem/csoportszoba'
|
|
WHEN tm.C_JELLEG = 2927 THEN 'Iskolapszichológusi szoba'
|
|
ELSE d.C_NAME
|
|
END AS TeremTipus
|
|
,ISNULL(tm.C_KAPACITAS, 0) AS Kapacitas
|
|
FROM T_TEREM tm
|
|
INNER JOIN T_DICTIONARYITEMBASE d ON tm.C_JELLEG = d.ID AND d.C_TANEVID = tm.C_TANEVID
|
|
CROSS APPLY (
|
|
SELECT TOP(1) C_FELADATELLATASIHELYID, A, B
|
|
FROM (
|
|
SELECT C_FELADATELLATASIHELYID, COUNT(DISTINCT oo.ID) AS A, COUNT(1) AS B
|
|
FROM T_ORARENDIORA oo
|
|
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_OSZTALYCSOPORTID = oo.C_OSZTALYCSOPORTID
|
|
AND tcs.C_BELEPESDATUM <= @oktoberEgy
|
|
AND (tcs.C_KILEPESDATUM > @oktoberEgy OR tcs.C_KILEPESDATUM IS NULL)
|
|
AND tcs.TOROLT = 'F'
|
|
WHERE tm.ID = oo.C_TEREMID
|
|
AND oo.C_ORAERVENYESSEGKEZDETE <= @oktoberEgy
|
|
AND oo.C_ORAERVENYESSEGVEGE > @oktoberEgy
|
|
AND oo.TOROLT = 'F'
|
|
GROUP BY C_FELADATELLATASIHELYID
|
|
) x
|
|
ORDER BY A DESC, B DESC
|
|
) oo
|
|
WHERE d.ID IN (181, 182, 184, 188, 190, 191, 2926, 2927, 2956)
|
|
AND tm.TOROLT = 'F'
|
|
AND d.C_TANEVID = @pTanevId
|
|
AND oo.C_FELADATELLATASIHELYID = @pFeladatellatasiHelyId
|
|
END
|
|
GO
|