95 lines
2.6 KiB
Transact-SQL
95 lines
2.6 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetOrarendErvenyesseggelExcelExportData]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetOrarendErvenyesseggelExcelExportData]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetOrarendErvenyesseggelExcelExportData]
|
|
@pTanevId INT,
|
|
@pErvenyessegKezdete DATE,
|
|
@pErvenyessegVege DATE
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
CAST(
|
|
(
|
|
CAST(
|
|
YEAR(oo.C_ORAERVENYESSEGKEZDETE)
|
|
AS NVARCHAR(4)
|
|
)
|
|
+ '.' +
|
|
IIF (MONTH(oo.C_ORAERVENYESSEGKEZDETE) < 10,'0','') +
|
|
CAST(
|
|
MONTH(oo.C_ORAERVENYESSEGKEZDETE)
|
|
AS NVARCHAR(4)
|
|
)
|
|
+ '.' +
|
|
IIF (DAY(oo.C_ORAERVENYESSEGKEZDETE) < 10,'0','') +
|
|
CAST(
|
|
DAY(oo.C_ORAERVENYESSEGKEZDETE)
|
|
AS NVARCHAR(4)
|
|
)
|
|
)
|
|
AS nvarchar(12)
|
|
) AS 'Óra érvényességének kezdete',
|
|
IIF(oo.C_ORAERVENYESSEGVEGE IS NULL,
|
|
'',
|
|
CAST(
|
|
(
|
|
CAST(
|
|
YEAR(oo.C_ORAERVENYESSEGVEGE)
|
|
AS NVARCHAR(4)
|
|
)
|
|
+ '.' +
|
|
IIF (MONTH(oo.C_ORAERVENYESSEGVEGE) < 10,'0','') +
|
|
CAST(
|
|
MONTH(oo.C_ORAERVENYESSEGVEGE)
|
|
AS NVARCHAR(4)
|
|
)
|
|
+ '.' +
|
|
IIF (DAY(oo.C_ORAERVENYESSEGVEGE) < 10,'0','') +
|
|
CAST(
|
|
DAY(oo.C_ORAERVENYESSEGVEGE)
|
|
AS NVARCHAR(4)
|
|
)
|
|
)
|
|
AS nvarchar(12)
|
|
)
|
|
) AS 'Óra érvényességének vége (nem kötelezõ megadni;abban az esetben 1 napra érvényes bejegyzés készül)',
|
|
hr.C_NAME As 'Hetirend',
|
|
hn.C_NAME AS 'Nap',
|
|
oo.C_ORASZAM AS 'Óra (adott napon belül)',
|
|
IIF(o.ID IS NOT NULL,ocs.C_NEV,'') AS 'Osztály',
|
|
IIF(cs.ID IS NOT NULL,ocs.C_NEV,'') AS 'Csoport',
|
|
tgy.C_NEV as 'Tantárgy',
|
|
f.C_NYOMTATASINEV as 'Tanár',
|
|
t.C_NEV AS 'Helyiség'
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_TEREM_OSSZES t ON t.ID = oo.C_TEREMID AND t.TOROLT = 'F'
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = oo.C_TANTARGYID AND tgy.TOROLT = 'F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = oo.C_TANARID AND f.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES hr ON hr.ID = oo.C_HETIREND AND hr.TOROLT = 'F' AND hr.C_TANEVID = @pTanevId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES hn ON hn.ID = oo.C_HETNAPJA AND hn.TOROLT = 'F' AND hn.C_TANEVID = @pTanevId
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
|
|
LEFT JOIN T_OSZTALY_OSSZES o ON ocs.ID = o.ID AND o.TOROLT = 'F'
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON ocs.ID = cs.ID AND cs.TOROLT = 'F'
|
|
WHERE
|
|
oo.C_ORASZAM IS NOT NULL
|
|
AND oo.TOROLT = 'F'
|
|
AND oo.C_ORAERVENYESSEGKEZDETE >= @pErvenyessegKezdete
|
|
AND (oo.C_ORAERVENYESSEGVEGE <= @pErvenyessegVege OR oo.C_ORAERVENYESSEGVEGE IS NULL)
|
|
AND oo.C_TANEVID = @pTanevId
|
|
|
|
END
|
|
GO
|
|
|