108 lines
3.1 KiB
Transact-SQL
108 lines
3.1 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetOrarendExcelExportData
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetOrarendExcelExportData
|
|
@pTanevId int
|
|
,@pErvenyessegKezdete date
|
|
,@pErvenyessegVege date
|
|
,@pIsOraErvenyessegiIdovel bit
|
|
,@pIsIdoszakonKivuliElemekMegjelenitese bit
|
|
,@pIsEgyediNapOrainakKihagyasa bit
|
|
,@pIsNapiredExport bit = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
oo.ID AS Id, '
|
|
IF @pIsOraErvenyessegiIdovel = 1 BEGIN
|
|
SET @sql += N'
|
|
oo.C_ORAERVENYESSEGKEZDETE AS OraErvenyessegKezdete,
|
|
oo.C_ORAERVENYESSEGVEGE AS OraErvenyessegVege, '
|
|
END
|
|
|
|
SET @sql += N'
|
|
hr.C_NAME As Hetirend,
|
|
hn.C_NAME AS Nap, '
|
|
|
|
IF @pIsNapiredExport IS NULL
|
|
BEGIN
|
|
SET @sql += N'
|
|
oo.C_ORAKEZDETE AS FoglalkozasKezdete,
|
|
oo.C_ORAVEGE AS FoglalkozasVege,
|
|
oo.C_ORASZAM AS Ora, '
|
|
END
|
|
ELSE IF @pIsNapiredExport = 1 BEGIN
|
|
SET @sql += N'
|
|
oo.C_ORAKEZDETE AS FoglalkozasKezdete,
|
|
oo.C_ORAVEGE AS FoglalkozasVege, '
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql += N'
|
|
oo.C_ORASZAM AS Ora, '
|
|
END
|
|
|
|
SET @sql += N'
|
|
IIF(o.ID IS NOT NULL,ocs.C_NEV,'''') AS Osztaly,
|
|
IIF(cs.ID IS NOT NULL,ocs.C_NEV,'''') AS Csoport,
|
|
tgy.C_NEV AS Tantargy,
|
|
f.C_NYOMTATASINEV AS Tanar,
|
|
t.C_NEV AS Helyiseg '
|
|
|
|
SET @sql += N'
|
|
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'' '
|
|
IF @pIsIdoszakonKivuliElemekMegjelenitese = 0 BEGIN
|
|
SET @sql += N'
|
|
WHERE 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
|
|
ELSE BEGIN
|
|
SET @sql += N'
|
|
WHERE oo.TOROLT = ''F''
|
|
AND oo.C_TANEVID = @pTanevId
|
|
AND (oo.C_ORAERVENYESSEGVEGE > @pErvenyessegKezdete AND oo.C_ORAERVENYESSEGKEZDETE <= @pErvenyessegVege) '
|
|
|
|
END
|
|
|
|
IF @pIsEgyediNapOrainakKihagyasa = 1 BEGIN
|
|
SET @sql += N'
|
|
AND oo.C_EGYEDINAP = ''F'' '
|
|
END
|
|
|
|
IF @pIsNapiredExport IS NOT NULL
|
|
BEGIN
|
|
|
|
IF @pIsNapiredExport = 0 BEGIN
|
|
SET @sql += N'
|
|
AND oo.C_CSENGETESIRENDID IS NOT NULL '
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql += N'
|
|
AND oo.C_CSENGETESIRENDID IS NULL '
|
|
END
|
|
|
|
END
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pTanevId int
|
|
,@pErvenyessegKezdete date
|
|
,@pErvenyessegVege date'
|
|
,@pTanevId = @pTanevId
|
|
,@pErvenyessegKezdete = @pErvenyessegKezdete
|
|
,@pErvenyessegVege = @pErvenyessegVege
|
|
END
|
|
|
|
GO
|
|
|