93 lines
4.1 KiB
Transact-SQL
93 lines
4.1 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('dbo.uspIktatottDokumentumLetezik') IS NOT NULL BEGIN
|
|
DROP PROCEDURE dbo.uspIktatottDokumentumLetezik
|
|
END
|
|
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
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
declare @cnt int = 0
|
|
set @cnt = 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
|
|
|
|
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(@cnt > 0, 'dksz.C_DOKUMENTUMKULCSSZOTIPUS, ', ''), N'COUNT(iktd.C_INTEZMENYID) AS darab FROM T_IKTATOTTDOKUMENTUM iktd', IIF(@cnt > 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, CONCAT(N'
|
|
AND iktd.C_TANEVID = ', @tanevId), ''), N'
|
|
AND iktd.C_DOKUMENTUMKATEGORIA = ', @dokumentumKategoria, N'
|
|
AND iktd.C_DOKUMENTUMTIPUS = ', @dokumentumTipus, N'
|
|
AND iktd.C_DOKUMENTUMELEMISZINT = ', @elemiSzint
|
|
)
|
|
IF @cnt > 0 BEGIN
|
|
|
|
IF @osztalyId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition='', N' AND (',''), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7399 AND dksze.C_ERTEK = ''', @osztalyId, ''')')
|
|
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 = ''', @csoportId, ''')')
|
|
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 = ''', @tanarId, ''')')
|
|
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 = ''', @evfolyamId, ''')')
|
|
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 ''%', LOWER(@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 ''%', LOWER(@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 ''%', LOWER(@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 ''%', LOWER(@alkalmazottEgyediAzon), '%'')')
|
|
END
|
|
SET @whereCondition += IIF(@whereCondition != '', ')', '')
|
|
SET @sql += @whereCondition
|
|
END
|
|
SET @sql += CONCAT(N'
|
|
GROUP BY iktd.ID', IIF(@cnt > 0, N', dksz.C_DOKUMENTUMKULCSSZOTIPUS', ''), N'
|
|
) AS kulcsszotipusok
|
|
GROUP BY kulcsszotipusok.iktdID
|
|
) AS doksik
|
|
WHERE doksik.darab = ', IIF(@cnt > 0, @cnt, '1'), ' ORDER BY ID ASC')
|
|
|
|
EXEC sp_executesql @sql;
|
|
END
|
|
|
|
GO |