128 lines
5.3 KiB
Transact-SQL
128 lines
5.3 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
DROP PROCEDURE IF EXISTS dbo.uspIktatottDokumentumLetezik
|
|
GO
|
|
|
|
CREATE PROCEDURE dbo.uspIktatottDokumentumLetezik
|
|
@intezmenyId INT,
|
|
@tanevId INT = NULL,
|
|
@dokumentumKategoria INT,
|
|
@dokumentumTipus INT,
|
|
@elemiSzint INT,
|
|
@osztalyId INT = NULL,
|
|
@csoportId INT = NULL,
|
|
@tanarId INT = NULL,
|
|
@evfolyamId INT = NULL,
|
|
@gondviseloNeve NVARCHAR(50) = NULL,
|
|
@tanuloNeve NVARCHAR(50) = NULL,
|
|
@tanuloEgyediAzon NVARCHAR(15) = NULL,
|
|
@alkalmazottEgyediAzon NVARCHAR(15) = NULL,
|
|
@teremId INT = NULL
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @paramDefinition nvarchar(MAX)
|
|
declare @nct int = 0
|
|
set @nct = case when @osztalyId IS NOT NULL then 1 else 0 END+
|
|
case when @csoportId IS NOT NULL then 1 else 0 END+
|
|
case when @tanarId IS NOT NULL then 1 else 0 END+
|
|
case when @evfolyamId IS NOT NULL then 1 else 0 END+
|
|
case when @gondviseloNeve IS NOT NULL then 1 else 0 END+
|
|
case when @tanuloNeve IS NOT NULL then 1 else 0 END+
|
|
case when @tanuloEgyediAzon IS NOT NULL then 1 else 0 END+
|
|
case when @alkalmazottEgyediAzon IS NOT NULL then 1 else 0 END+
|
|
case when @teremId IS NOT NULL then 1 else 0 END
|
|
|
|
DECLARE @whereCondition NVARCHAR(MAX) = ''
|
|
declare @sql NVARCHAR(MAX) = CONCAT(N'SELECT ID FROM (
|
|
SELECT kulcsszotipusok.iktdID ID, COUNT(kulcsszotipusok.darab) AS darab FROM (
|
|
SELECT iktd.ID iktdID, ', IIF(@nct > 0, 'dksz.C_DOKUMENTUMKULCSSZOTIPUS, ', ''), N'COUNT(iktd.C_INTEZMENYID) AS darab FROM T_IKTATOTTDOKUMENTUM iktd', IIF(@nct > 0, N'
|
|
LEFT JOIN T_DOKUMENTUMKULCSSZO_OSSZES dksz ON dksz.C_IKTATOTTDOKUMENTUMID = iktd.ID
|
|
LEFT JOIN T_DOKUMENTUMKULCSSZOERTEK_OSSZES dksze ON dksze.ID = dksz.C_DOKUMENTUMKULCSSZOERTEKID', ''), N'
|
|
WHERE iktd.TOROLT = ''F''
|
|
AND iktd.C_INTEZMENYID = @intezmenyId', IIF(@tanevId IS NOT NULL, N'
|
|
AND iktd.C_TANEVID = @tanevId', ''), N'
|
|
AND iktd.C_DOKUMENTUMKATEGORIA = @dokumentumKategoria
|
|
AND iktd.C_DOKUMENTUMTIPUS = @dokumentumTipus
|
|
AND iktd.C_DOKUMENTUMELEMISZINT = @elemiSzint'
|
|
)
|
|
IF @nct > 0 BEGIN
|
|
|
|
IF @osztalyId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (',''), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7399 AND dksze.C_ERTEK = CAST(@osztalyId as nvarchar))')
|
|
END
|
|
IF @csoportId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7410 AND dksze.C_ERTEK = CAST(@csoportId as nvarchar))')
|
|
END
|
|
IF @tanarId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7415 AND dksze.C_ERTEK = CAST(@tanarId as nvarchar))')
|
|
END
|
|
IF @evfolyamId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7400 AND dksze.C_ERTEK = CAST(@evfolyamId as nvarchar))')
|
|
END
|
|
IF @tanuloNeve IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7414 AND LOWER(dksze.C_ERTEK) LIKE ''%'' + @tanuloNeve + ''%'')')
|
|
END
|
|
IF @gondviseloNeve IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7416 AND LOWER(dksze.C_ERTEK) LIKE ''%'' + @gondviseloNeve + ''%'')')
|
|
END
|
|
IF @tanuloEgyediAzon IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7561 AND LOWER(dksze.C_ERTEK) LIKE ''%'' + @tanuloEgyediAzon + ''%'')')
|
|
END
|
|
IF @alkalmazottEgyediAzon IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7562 AND LOWER(dksze.C_ERTEK) LIKE ''%'' + @alkalmazottEgyediAzon + ''%'')')
|
|
END
|
|
IF @teremId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7674 AND dksze.C_ERTEK = CAST(@teremId as nvarchar))')
|
|
END
|
|
SET @whereCondition += IIF(@whereCondition != '', ')', '')
|
|
SET @sql += @whereCondition
|
|
END
|
|
SET @sql += CONCAT(N'
|
|
GROUP BY iktd.ID', IIF(@nct > 0, N', dksz.C_DOKUMENTUMKULCSSZOTIPUS', ''), N'
|
|
) AS kulcsszotipusok
|
|
GROUP BY kulcsszotipusok.iktdID
|
|
) AS doksik
|
|
WHERE doksik.darab = ', IIF(@nct > 0, '@cnt', '1'), ' ORDER BY ID ASC')
|
|
|
|
SET @paramDefinition = N'@intezmenyId int,
|
|
@tanevId int,
|
|
@dokumentumKategoria int,
|
|
@dokumentumTipus int,
|
|
@elemiSzint int,
|
|
@osztalyId int,
|
|
@csoportId int,
|
|
@tanarId int,
|
|
@evfolyamId int,
|
|
@tanuloNeve nvarchar,
|
|
@gondviseloNeve nvarchar,
|
|
@tanuloEgyediAzon nvarchar,
|
|
@alkalmazottEgyediAzon nvarchar,
|
|
@teremId int,
|
|
@cnt int
|
|
'
|
|
EXEC sp_executesql @sql, @paramDefinition,
|
|
@intezmenyId = @intezmenyId,
|
|
@tanevId = @tanevId,
|
|
@dokumentumKategoria = @dokumentumKategoria,
|
|
@dokumentumTipus = @dokumentumTipus,
|
|
@elemiSzint = @elemiSzint,
|
|
@osztalyId = @osztalyId,
|
|
@csoportId = @csoportId,
|
|
@tanarId = @tanarId,
|
|
@evfolyamId = @evfolyamId,
|
|
@tanuloNeve = @tanuloNeve,
|
|
@gondviseloNeve = @gondviseloNeve,
|
|
@tanuloEgyediAzon = @tanuloEgyediAzon,
|
|
@alkalmazottEgyediAzon = @alkalmazottEgyediAzon,
|
|
@teremId = @teremId,
|
|
@cnt = @nct
|
|
END
|
|
|
|
GO |