187 lines
8.4 KiB
Transact-SQL
187 lines
8.4 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetHazifeladatData
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetHazifeladatData
|
|
@pTanevId int
|
|
,@pOraDatumaTol datetime = NULL
|
|
,@pOraDatumaIg datetime = NULL
|
|
,@pTanarId int = NULL
|
|
,@pTantargyId int = NULL
|
|
,@pOraTema nvarchar(max) = NULL
|
|
,@pOsztalyCsoportIds nvarchar(max) = NULL
|
|
,@pHazifeladatSzoveg nvarchar(max) = NULL
|
|
,@pRogzitesTol datetime = NULL
|
|
,@pRogzitesIg datetime = NULL
|
|
,@pHataridoTol datetime = NULL
|
|
,@pHataridoIg datetime = NULL
|
|
,@pMindegyikHetTipusId int = 1554
|
|
,@pFeladatKategoriaId int = NULL
|
|
,@pFeladatEllatasiHelyId int = NULL
|
|
,@pIsOnlineOra bit = NULL
|
|
,@pFeladatTipusId int = NULL
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
oo.ID AS ID
|
|
,hf.C_DATUM AS OraDatuma
|
|
,oo.C_ORASZAM AS OraSorszama
|
|
,hf.C_BEADASHATARIDO AS HaziFeladatHataridoDatuma
|
|
,hf.C_ROGZITESIDOPONT AS HaziFeladatRogzitesDatuma
|
|
,ISNULL(helyettesf.C_NYOMTATASINEV,fh.C_NYOMTATASINEV) AS Tanar
|
|
,ISNULL(helyettesf.ID,fh.ID) AS TanarId
|
|
,tgy.C_NEV AS Tantargy
|
|
,tgy.ID AS TantargyId
|
|
,NULL AS OraTema
|
|
,hf.C_SZOVEG AS TanarHazifeladat
|
|
,ocs.C_NEV AS OsztalyCsoport
|
|
,ocs.ID AS OsztalyCsoportId
|
|
,hf.ID AS HazifeladatId
|
|
,ISNULL(ootOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) AS IsOnlineOra
|
|
,C_FELADATTIPUSID AS FeladatTipusId
|
|
,''F'' AS IsTanitasiOra
|
|
,oo.TOROLT AS IsToroltOra
|
|
FROM T_DKT_FELADAT_OSSZES hf
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.ID = hf.C_ORARENDIORAGROUPID
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = oo.C_TANTARGYID AND tgy.TOROLT = ''F''
|
|
LEFT JOIN T_HELYETTESITESIIDOSZAK_OSSZES hisz ON hisz.C_HELYETTESITETTORARENDID = oo.ID
|
|
AND hisz.C_HELYETTESITESNAPJA = CAST(hf.C_ROGZITESIDOPONT AS date)
|
|
AND hisz.TOROLT = ''F''
|
|
LEFT JOIN T_FELHASZNALO_OSSZES helyettesf ON helyettesf.ID = hisz.C_HELYETTESTANAROKID AND helyettesf.TOROLT = ''F''
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = oo.C_TANARID AND fh.TOROLT = ''F''
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.TOROLT = ''F''
|
|
INNER JOIN T_ORATULAJDONSAGTIPUS_OSSZES ottOnlineOra ON ottOnlineOra.TOROLT = ''F'' AND ottOnlineOra.C_ALTANEVID = oo.C_TANEVID AND ottOnlineOra.ID = 8615
|
|
LEFT JOIN T_ORARENDIORATULAJDONSAG_OSSZES ootOnlineOra ON ootOnlineOra.C_ORATULAJDONSAGID = ottOnlineOra.ID AND ootOnlineOra.C_TANEVID = ottOnlineOra.C_ALTANEVID AND ootOnlineOra.TOROLT = ''F'' AND ootOnlineOra.C_ORARENDIORAID = oo.ID
|
|
WHERE hf.TOROLT = ''F''
|
|
AND hf.C_TANITASIORAID IS NULL
|
|
AND hf.C_TANEVID = @pTanevId'
|
|
|
|
SET @sql += IIF(@pOraDatumaTol IS NOT NULL, N'
|
|
AND hf.C_DATUM >= @pOraDatumaTol ', '' )
|
|
SET @sql += IIF(@pOraDatumaIg IS NOT NULL, N'
|
|
AND hf.C_DATUM <= @pOraDatumaIg ', '' )
|
|
SET @sql += IIF(@pTantargyId IS NOT NULL, N'
|
|
AND tgy.ID = @pTantargyId', '')
|
|
SET @sql += IIF(@pOsztalyCsoportIds IS NOT NULL, N'
|
|
AND ocs.ID IN (' + @pOsztalyCsoportIds + ')', '')
|
|
SET @sql += IIF(@pFeladatKategoriaId IS NULL, N'', N'
|
|
AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId')
|
|
SET @sql += IIF(@pFeladatEllatasiHelyId IS NULL, N'', N'
|
|
AND ocs.C_FELADATELLATASIHELYID = @pFeladatEllatasiHelyId')
|
|
SET @sql += IIF(@pHazifeladatSzoveg IS NOT NULL, N'
|
|
AND hf.C_SZOVEG LIKE ''%'' + @pHazifeladatSzoveg + ''%''', '')
|
|
SET @sql += IIF(@pRogzitesTol IS NOT NULL, N'
|
|
AND hf.C_ROGZITESIDOPONT >= @pRogzitesTol', '')
|
|
SET @sql += IIF(@pRogzitesIg IS NOT NULL, N'
|
|
AND hf.C_ROGZITESIDOPONT < @pRogzitesIg + 1', '')
|
|
SET @sql += IIF(@pHataridoTol IS NOT NULL, N'
|
|
AND hf.C_BEADASHATARIDO >= @pHataridoTol', '')
|
|
SET @sql += IIF(@pHataridoIg IS NOT NULL, N'
|
|
AND hf.C_BEADASHATARIDO <= @pHataridoIg', '')
|
|
SET @sql += IIF(@pIsOnlineOra IS NOT NULL, N'
|
|
AND ISNULL(totOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) = ' + IIF(@pIsOnlineOra = 1, '''T''', '''F'''), '')
|
|
SET @sql += IIF(@pTanarId IS NOT NULL, N'
|
|
AND (helyettesf.ID = @pTanarId OR fh.ID = @pTanarId)', '')
|
|
SET @sql += IIF(@pFeladatTipusId IS NULL, N'', N'
|
|
AND hf.C_FELADATTIPUSID = @pFeladatTipusId')
|
|
SET @sql += N'
|
|
|
|
UNION ALL
|
|
SELECT
|
|
tn.ID AS ID
|
|
,tn.C_DATUM AS OraDatuma
|
|
,tn.C_ORASZAM AS OraSorszama
|
|
,hf.C_BEADASHATARIDO AS HaziFeladatHataridoDatuma
|
|
,hf.C_ROGZITESIDOPONT AS HaziFeladatRogzitesDatuma
|
|
,ISNULL(helyettesf.C_NYOMTATASINEV, fh.C_NYOMTATASINEV) AS Tanar
|
|
,ISNULL(helyettesf.ID, fh.ID) AS TanarId
|
|
,tgy.C_NEV AS Tantargy
|
|
,tgy.ID AS TantargyId
|
|
,tn.C_TEMA AS OraTema
|
|
,hf.C_SZOVEG AS TanarHazifeladat
|
|
,ocs.C_NEV AS OsztalyCsoport
|
|
,ocs.ID AS OsztalyCsoportId
|
|
,hf.ID AS HazifeladatId
|
|
,ISNULL(totOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) AS IsOnlineOra
|
|
,C_FELADATTIPUSID AS FeladatTipusId
|
|
,''T'' AS IsTanitasiOra
|
|
,tn.TOROLT AS IsToroltOra
|
|
FROM T_DKT_FELADAT_OSSZES hf
|
|
INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = hf.C_TANITASIORAID
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = tn.C_TANTARGYID AND tgy.TOROLT = ''F''
|
|
LEFT JOIN T_FELHASZNALO_OSSZES helyettesf ON helyettesf.ID = tn.C_HELYETTESITOTANARID AND helyettesf.TOROLT = ''F''
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = tn.C_TANARID AND fh.TOROLT = ''F''
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = hf.C_OSZTALYCSOPORTID AND ocs.TOROLT = ''F''
|
|
INNER JOIN T_ORATULAJDONSAGTIPUS_OSSZES ottOnlineOra ON ottOnlineOra.TOROLT = ''F'' AND ottOnlineOra.C_ALTANEVID = tn.C_TANEVID AND ottOnlineOra.ID = 8615
|
|
LEFT JOIN T_TANITASIORATULAJDONSAG_OSSZES totOnlineOra ON totOnlineOra.C_ORATULAJDONSAGID = ottOnlineOra.ID AND totOnlineOra.C_TANEVID = ottOnlineOra.C_ALTANEVID AND totOnlineOra.TOROLT = ''F'' AND totOnlineOra.C_TANITASIORAID = tn.ID
|
|
WHERE hf.TOROLT = ''F''
|
|
AND hf.C_TANEVID = @pTanevId'
|
|
|
|
SET @sql += IIF(@pTantargyId IS NOT NULL, N'
|
|
AND tgy.ID = @pTantargyId', '')
|
|
SET @sql += IIF(@pOsztalyCsoportIds IS NOT NULL, N'
|
|
AND ocs.ID IN (' + @pOsztalyCsoportIds + ')', '')
|
|
SET @sql += IIF(@pFeladatKategoriaId IS NULL, N'', N'
|
|
AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId')
|
|
SET @sql += IIF(@pFeladatEllatasiHelyId IS NULL, N'', N'
|
|
AND ocs.C_FELADATELLATASIHELYID = @pFeladatEllatasiHelyId')
|
|
SET @sql += IIF(@pHazifeladatSzoveg IS NOT NULL, N'
|
|
AND hf.C_SZOVEG LIKE ''%'' + @pHazifeladatSzoveg + ''%''', '')
|
|
SET @sql += IIF(@pRogzitesTol IS NOT NULL, N'
|
|
AND hf.C_ROGZITESIDOPONT >= @pRogzitesTol', '')
|
|
SET @sql += IIF(@pRogzitesIg IS NOT NULL, N'
|
|
AND hf.C_ROGZITESIDOPONT < @pRogzitesIg + 1', '')
|
|
SET @sql += IIF(@pHataridoTol IS NOT NULL, N'
|
|
AND hf.C_BEADASHATARIDO >= @pHataridoTol', '')
|
|
SET @sql += IIF(@pHataridoIg IS NOT NULL, N'
|
|
AND hf.C_BEADASHATARIDO <= @pHataridoIg', '')
|
|
SET @sql += IIF(@pOraDatumaTol IS NOT NULL, N'
|
|
AND tn.C_DATUM >= @pOraDatumaTol', '')
|
|
SET @sql += IIF(@pOraDatumaIg IS NOT NULL, N'
|
|
AND tn.C_DATUM <= @pOraDatumaIg', '')
|
|
SET @sql += IIF(@pOraTema IS NOT NULL, N'
|
|
AND tn.C_TEMA LIKE ''%'' + @pOraTema + ''%''', '')
|
|
SET @sql += IIF(@pIsOnlineOra IS NOT NULL, N'
|
|
AND ISNULL(totOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) = ' + IIF(@pIsOnlineOra = 1, '''T''', '''F'''), '')
|
|
SET @sql += IIF(@pTanarId IS NOT NULL, N'
|
|
AND (helyettesf.ID = @pTanarId OR fh.ID = @pTanarId)', '')
|
|
SET @sql += IIF(@pFeladatTipusId IS NULL, N'', N'
|
|
AND hf.C_FELADATTIPUSID = @pFeladatTipusId')
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pTanevId int
|
|
,@pOraDatumaTol datetime
|
|
,@pOraDatumaIg datetime
|
|
,@pTanarId int
|
|
,@pTantargyId int
|
|
,@pOraTema nvarchar(max)
|
|
,@pHazifeladatSzoveg nvarchar(max)
|
|
,@pRogzitesTol datetime
|
|
,@pRogzitesIg datetime
|
|
,@pHataridoTol datetime
|
|
,@pHataridoIg datetime
|
|
,@pMindegyikHetTipusId int
|
|
,@pFeladatKategoriaId int
|
|
,@pFeladatEllatasiHelyId int
|
|
,@pFeladatTipusId int'
|
|
,@pTanevId = @pTanevId
|
|
,@pOraDatumaTol = @pOraDatumaTol
|
|
,@pOraDatumaIg = @pOraDatumaIg
|
|
,@pTanarId = @pTanarId
|
|
,@pTantargyId = @pTantargyId
|
|
,@pOraTema = @pOraTema
|
|
,@pHazifeladatSzoveg = @pHazifeladatSzoveg
|
|
,@pRogzitesTol = @pRogzitesTol
|
|
,@pRogzitesIg = @pRogzitesIg
|
|
,@pHataridoTol = @pHataridoTol
|
|
,@pHataridoIg = @pHataridoIg
|
|
,@pMindegyikHetTipusId = @pMindegyikHetTipusId
|
|
,@pFeladatKategoriaId = @pFeladatKategoriaId
|
|
,@pFeladatEllatasiHelyId = @pFeladatEllatasiHelyId
|
|
,@pFeladatTipusId = @pFeladatTipusId
|
|
|
|
END
|
|
GO
|