316 lines
12 KiB
Transact-SQL
316 lines
12 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('uspGetIktatottDokumentum') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE uspGetIktatottDokumentum
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE dbo.uspGetIktatottDokumentum
|
|
@pTanevId INT,
|
|
@pIntezmenyId INT,
|
|
@pKategoriaId INT = NULL,
|
|
@pDokumentumTipus INT = NULL,
|
|
@pDokumentumNeve NVARCHAR(256) = NULL,
|
|
@pFeladatEllatasiHely INT = NULL,
|
|
@pIktatoSzam NVARCHAR(50) = NULL,
|
|
@pIktatasKezdete DATETIME = NULL,
|
|
@pIktatasVege DATETIME = NULL,
|
|
@pOsztalyId INT = NULL,
|
|
@pEvfolyamId INT = NULL,
|
|
@pKepzesTipusId INT = NULL,
|
|
@pOsztalyFonokId INT = NULL ,
|
|
@pTantervId INT = NULL,
|
|
@pOsztalyjellemzoId INT = NULL,
|
|
@pSzakcsoportId INT = NULL,
|
|
@pAgazatId INT = NULL,
|
|
@pSzakkepesitesId INT = NULL,
|
|
@pReszszakkepesitesId INT = NULL,
|
|
@pCsoportnevId INT = NULL,
|
|
@pCsoporttipusId INT = NULL,
|
|
@pCsoportjellemzoId INT = NULL,
|
|
@pTanuloNeve NVARCHAR(50) = NULL,
|
|
@pAlkalmazottId INT = NULL,
|
|
@pTanuloOktAzon NVARCHAR(20) = NULL,
|
|
@pAlkalmazottOktAzon NVARCHAR(20) = NULL,
|
|
@pGondviseloNeve NVARCHAR(50) = NULL,
|
|
@pIktatoSzemely NVARCHAR(50) = NULL,
|
|
@pDokumentumStatusz INT = NULL,
|
|
@pFoszamosDokumentumId INT = NULL,
|
|
@pOrderBy NVARCHAR(256) = NULL,
|
|
@pFirstRow INT = NULL,
|
|
@pLastRow INT = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @whereCondition NVARCHAR(MAX) = ''
|
|
DECLARE @sql NVARCHAR(MAX);
|
|
DECLARE @paramDefinition NVARCHAR(MAX)
|
|
|
|
SET @sql = N'SELECT * INTO #tmpIktDok
|
|
FROM (
|
|
SELECT * FROM fnGetIktatottDokumentumok(@tanevId,@intezmenyId)'
|
|
|
|
IF @pKategoriaId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'Kategoria = @kategoriaId')
|
|
END
|
|
IF @pDokumentumTipus IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'DokumentumTipus = @dokumentumTipus')
|
|
END
|
|
IF @pDokumentumNeve IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'DokumentumNeve LIKE ''%'' + @dokumentumNeve + ''%''')
|
|
END
|
|
IF @pFeladatEllatasiHely IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'FeladatellatasihelyId = @feladatEllatasiHely')
|
|
END
|
|
IF @pIktatoSzam IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'Iktatoszam LIKE ''%'' + @iktatoSzam + ''%''')
|
|
END
|
|
IF @pIktatasKezdete IS NOT NULL AND @pIktatasVege IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'IktatasDatuma BETWEEN @iktatasKezdete AND @iktatasVege')
|
|
END
|
|
IF @pIktatoSzemely IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'IktatoSzemelye LIKE ''%'' + @iktatoSzemely + ''%''')
|
|
END
|
|
IF @pDokumentumStatusz IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'Statusz = @dokumentumStatusz')
|
|
END
|
|
|
|
SET @sql += CONCAT(@whereCondition, ') data;')
|
|
|
|
SET @whereCondition = ''
|
|
|
|
declare @nct int = 0
|
|
set @nct = case when @pOsztalyId IS NOT NULL then 1 else 0 END+
|
|
case when @pEvfolyamId IS NOT NULL then 1 else 0 END+
|
|
case when @pKepzesTipusId IS NOT NULL then 1 else 0 END+
|
|
case when @pOsztalyFonokId IS NOT NULL then 1 else 0 END+
|
|
case when @pTantervId IS NOT NULL then 1 else 0 END+
|
|
case when @pOsztalyjellemzoId IS NOT NULL then 1 else 0 END+
|
|
case when @pSzakcsoportId IS NOT NULL then 1 else 0 END+
|
|
case when @pAgazatId IS NOT NULL then 1 else 0 END+
|
|
case when @pSzakkepesitesId IS NOT NULL then 1 else 0 END+
|
|
case when @pReszszakkepesitesId IS NOT NULL then 1 else 0 END+
|
|
case when @pCsoportnevId IS NOT NULL then 1 else 0 END+
|
|
case when @pCsoporttipusId IS NOT NULL then 1 else 0 END+
|
|
case when @pCsoportjellemzoId IS NOT NULL then 1 else 0 END+
|
|
case when @pTanuloNeve IS NOT NULL then 1 else 0 END+
|
|
case when @pAlkalmazottId IS NOT NULL then 1 else 0 END+
|
|
case when @pTanuloOktAzon IS NOT NULL then 1 else 0 END+
|
|
case when @pAlkalmazottOktAzon IS NOT NULL then 1 else 0 END+
|
|
case when @pGondviseloNeve IS NOT NULL then 1 else 0 END
|
|
|
|
SET @sql += N'
|
|
SELECT * INTO #selIktDok FROM (
|
|
SELECT #tmpIktDok.* FROM #tmpIktDok'
|
|
IF @nct > 0 BEGIN
|
|
SET @sql += ' LEFT JOIN (
|
|
SELECT COUNT(datadarab.C_IKTATOTTDOKUMENTUMID) AS darab, datadarab.C_IKTATOTTDOKUMENTUMID FROM (
|
|
SELECT dksz.C_DOKUMENTUMKULCSSZOTIPUS, dksz.C_IKTATOTTDOKUMENTUMID FROM T_DOKUMENTUMKULCSSZOERTEK_OSSZES AS dksze
|
|
INNER JOIN T_DOKUMENTUMKULCSSZO_OSSZES AS dksz ON dksz.C_DOKUMENTUMKULCSSZOERTEKID = dksze.ID
|
|
WHERE dksz.C_IKTATOTTDOKUMENTUMID IN (SELECT ID FROM #tmpIktDok)
|
|
AND
|
|
('
|
|
IF (@pOsztalyId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7399 AND dksze.C_ERTEK = CAST(@osztalyId as nvarchar))')
|
|
END
|
|
IF (@pEvfolyamId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7400 AND dksze.C_ERTEK = CAST(@evfolyamId as nvarchar))')
|
|
END
|
|
IF (@pKepzesTipusId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7402 AND dksze.C_ERTEK = CAST(@kepzesTipusId as nvarchar))')
|
|
END
|
|
IF (@pOsztalyFonokId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7403 AND dksze.C_ERTEK = CAST(@osztalyFonokId as nvarchar))')
|
|
END
|
|
IF (@pTantervId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7404 AND dksze.C_ERTEK = CAST(@tantervId as nvarchar))')
|
|
END
|
|
IF (@pOsztalyjellemzoId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7405 AND dksze.C_ERTEK = CAST(@osztalyjellemzoId as nvarchar))')
|
|
END
|
|
IF (@pSzakcsoportId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7406 AND dksze.C_ERTEK = CAST(@szakcsoportId as nvarchar))')
|
|
END
|
|
IF (@pAgazatId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7407 AND dksze.C_ERTEK = CAST(@agazatId as nvarchar))')
|
|
END
|
|
IF (@pSzakkepesitesId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7408 AND dksze.C_ERTEK = CAST(@szakkepesitesId as nvarchar))')
|
|
END
|
|
IF (@pReszszakkepesitesId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7409 AND dksze.C_ERTEK = CAST(@reszszakkepesitesId as nvarchar))')
|
|
END
|
|
IF (@pCsoportnevId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7410 AND dksze.C_ERTEK = CAST(@csoportnevId as nvarchar))')
|
|
END
|
|
IF (@pCsoporttipusId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7411 AND dksze.C_ERTEK = CAST(@csoporttipusId as nvarchar))')
|
|
END
|
|
IF (@pCsoportjellemzoId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7413 AND dksze.C_ERTEK = CAST(@csoportjellemzoId as nvarchar))')
|
|
END
|
|
IF (@pTanuloNeve IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7414 AND dksze.C_ERTEK LIKE ''%'' + @tanuloNeve + ''%'')')
|
|
END
|
|
IF (@pAlkalmazottId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7415 AND dksze.C_ERTEK = CAST(@alkalmazottId as nvarchar))')
|
|
END
|
|
IF (@pTanuloOktAzon IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7417 AND dksze.C_ERTEK LIKE ''%'' + @tanuloOktAzon + ''%'')')
|
|
END
|
|
IF (@pAlkalmazottOktAzon IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7418 AND dksze.C_ERTEK LIKE ''%'' + @alkalmazottOktAzon + ''%'')')
|
|
END
|
|
IF (@pGondviseloNeve IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7416 AND dksze.C_ERTEK LIKE ''%'' + @gondviseloNeve + ''%'')')
|
|
END
|
|
SET @sql += @whereCondition
|
|
SET @sql += N')
|
|
GROUP BY dksz.C_IKTATOTTDOKUMENTUMID, dksz.C_DOKUMENTUMKULCSSZOTIPUS
|
|
) datadarab
|
|
GROUP BY datadarab.C_IKTATOTTDOKUMENTUMID
|
|
) darabok ON #tmpIktDok.ID = darabok.C_IKTATOTTDOKUMENTUMID
|
|
WHERE darabok.darab = @cnt'
|
|
END
|
|
SET @sql += ') AS sel';
|
|
IF @pFoszamosDokumentumId IS NULL BEGIN
|
|
SET @sql += N'
|
|
SELECT a.* INTO #alszamos FROM #selIktDok AS a
|
|
INNER JOIN (
|
|
SELECT FoszamosDokumentumId, MAX(IktatasDatuma) AS datum
|
|
FROM #selIktDok
|
|
GROUP BY FoszamosDokumentumId
|
|
) b ON a.FoszamosDokumentumId = b.FoszamosDokumentumId AND a.IktatasDatuma = b.datum
|
|
WHERE a.FoszamosDokumentumId IS NOT NULL
|
|
|
|
SELECT a.* INTO #resultIktDok FROM #selIktDok AS a
|
|
WHERE a.FoszamosDokumentumId IS NULL AND a.ID NOT IN (SELECT FoszamosDokumentumId FROM #alszamos)
|
|
|
|
INSERT INTO #resultIktDok
|
|
SELECT * FROM #alszamos
|
|
DROP TABLE #alszamos;'
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql += N'
|
|
SELECT * INTO #resultIktDok FROM #selIktDok
|
|
WHERE ID != @foszamosDokumentumId
|
|
AND FoszamosDokumentumId = (SELECT FoszamosDokumentumId FROM #selIktDok WHERE ID = @foszamosDokumentumId)
|
|
INSERT INTO #resultIktDok SELECT * FROM #selIktDok
|
|
WHERE ID = (SELECT FoszamosDokumentumId FROM #selIktDok WHERE ID = @foszamosDokumentumId)'
|
|
|
|
END
|
|
|
|
SET @sql += CONCAT(N'
|
|
SELECT COUNT(#resultIktDok.ID) FROM #resultIktDok;',
|
|
IIF(@pFirstRow IS NULL AND @pLastRow IS NULL,
|
|
CONCAT(N'
|
|
SELECT * FROM #resultIktDok', IIF(@pOrderBy IS NOT NULL, CONCAT(' ORDER BY ', @pOrderBy), '')
|
|
),
|
|
CONCAT(N'
|
|
SELECT * FROM
|
|
(
|
|
SELECT ROW_NUMBER() OVER(ORDER BY ', IIF(@pOrderBy IS NULL, '(SELECT 0)', @pOrderBy) ,') AS rnum, #resultIktDok.* FROM #resultIktDok
|
|
) qqq
|
|
WHERE qqq.rnum BETWEEN @firstRow AND @lastRow')), N'
|
|
DROP TABLE #resultIktDok;
|
|
DROP TABLE #tmpIktDok;
|
|
DROP TABLE #selIktDok;');
|
|
|
|
--PRINT @sql
|
|
SET @paramDefinition = N'@tanevId INT,
|
|
@intezmenyId INT,
|
|
@kategoriaId INT,
|
|
@dokumentumTipus INT,
|
|
@dokumentumNeve NVARCHAR(256),
|
|
@feladatEllatasiHely INT,
|
|
@iktatoSzam NVARCHAR(50),
|
|
@iktatasKezdete DATETIME,
|
|
@iktatasVege DATETIME,
|
|
@osztalyId INT,
|
|
@evfolyamId INT,
|
|
@kepzesTipusId INT,
|
|
@osztalyFonokId INT ,
|
|
@tantervId INT,
|
|
@osztalyjellemzoId INT,
|
|
@szakcsoportId INT,
|
|
@agazatId INT,
|
|
@szakkepesitesId INT,
|
|
@reszszakkepesitesId INT,
|
|
@csoportnevId INT,
|
|
@csoporttipusId INT,
|
|
@csoportjellemzoId INT,
|
|
@tanuloNeve NVARCHAR(50),
|
|
@alkalmazottId INT,
|
|
@tanuloOktAzon NVARCHAR(20),
|
|
@alkalmazottOktAzon NVARCHAR(20),
|
|
@gondviseloNeve NVARCHAR(50),
|
|
@iktatoSzemely NVARCHAR(50),
|
|
@dokumentumStatusz INT,
|
|
@foszamosDokumentumId INT,
|
|
@orderBy NVARCHAR(256),
|
|
@firstRow INT,
|
|
@lastRow INT,
|
|
@cnt INT'
|
|
|
|
EXEC sp_executesql @sql, @paramDefinition,
|
|
@tanevId = @pTanevId,
|
|
@intezmenyId = @pIntezmenyId,
|
|
@kategoriaId = @pKategoriaId,
|
|
@dokumentumTipus = @pDokumentumTipus,
|
|
@dokumentumNeve = @pDokumentumNeve,
|
|
@feladatEllatasiHely = @pFeladatEllatasiHely,
|
|
@iktatoSzam = @pIktatoSzam,
|
|
@iktatasKezdete = @pIktatasKezdete,
|
|
@iktatasVege = @pIktatasVege,
|
|
@osztalyId = @pOsztalyId,
|
|
@evfolyamId = @pEvfolyamId,
|
|
@kepzesTipusId = @pKepzesTipusId,
|
|
@osztalyFonokId = @pOsztalyFonokId,
|
|
@tantervId = @pTantervId,
|
|
@osztalyjellemzoId = @pOsztalyjellemzoId,
|
|
@szakcsoportId = @pSzakcsoportId,
|
|
@agazatId = @pAgazatId,
|
|
@szakkepesitesId = @pSzakkepesitesId,
|
|
@reszszakkepesitesId = @pReszszakkepesitesId,
|
|
@csoportnevId = @pCsoportnevId,
|
|
@csoporttipusId = @pCsoporttipusId,
|
|
@csoportjellemzoId = @pCsoportjellemzoId,
|
|
@tanuloNeve = @pTanuloNeve,
|
|
@alkalmazottId = @pAlkalmazottId,
|
|
@tanuloOktAzon = @pTanuloOktAzon,
|
|
@alkalmazottOktAzon = @pAlkalmazottOktAzon,
|
|
@gondviseloNeve = @pGondviseloNeve,
|
|
@iktatoSzemely = @pIktatoSzemely,
|
|
@dokumentumStatusz = @pDokumentumStatusz,
|
|
@foszamosDokumentumId = @pFoszamosDokumentumId,
|
|
@orderBy = @pOrderBy,
|
|
@firstRow = @pFirstRow,
|
|
@lastRow = @pLastRow,
|
|
@cnt = @nct
|
|
|
|
END
|
|
|
|
GO |