262 lines
11 KiB
Transact-SQL
262 lines
11 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 NVARCHAR(24) = NULL,
|
|
@pIktatasVege NVARCHAR(24) = 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
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @whereCondition NVARCHAR(MAX) = ''
|
|
DECLARE @sql NVARCHAR(MAX);
|
|
SET @sql = CONCAT(N'DECLARE @tabla TABLE (
|
|
ID INT NOT NULL
|
|
,Kategoria INT NOT NULL
|
|
,DokumentumTipus INT NOT NULL
|
|
,DokumentumNeve NVARCHAR(MAX) NOT NULL
|
|
,FeladatellatasihelyId INT NULL
|
|
,FeladatellatasihelyNeve NVARCHAR(MAX) NULL
|
|
,Iktatoszam NVARCHAR(MAX) NULL
|
|
,IktatasDatuma DATETIME NULL
|
|
,IktatoSzemelye NVARCHAR(MAX) NOT NULL
|
|
,Statusz INT NOT NULL
|
|
,TanevNev NVARCHAR(MAX) NOT NULL
|
|
,ElektronikusPeldanyId INT NULL
|
|
,FoszamosDokumentumId INT NULL
|
|
,RowNumber INT
|
|
)
|
|
|
|
SELECT * INTO #tmpIktDok
|
|
FROM (
|
|
SELECT * FROM fnGetIktatottDokumentumok(', @pTanevId, ',', @pIntezmenyId, ')')
|
|
|
|
IF @pKategoriaId IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'Kategoria = ', @pKategoriaId)
|
|
END
|
|
IF @pDokumentumTipus IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'DokumentumTipus = ', @pDokumentumTipus)
|
|
END
|
|
IF @pDokumentumNeve IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'LOWER(DokumentumNeve) LIKE ''%', @pDokumentumNeve ,'%''')
|
|
END
|
|
IF @pFeladatEllatasiHely IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'FeladatellatasihelyId = ', @pFeladatEllatasiHely)
|
|
END
|
|
IF @pIktatoSzam IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'LOWER(Iktatoszam) LIKE ''%', @pIktatoszam, '%''')
|
|
END
|
|
IF @pIktatasKezdete IS NOT NULL AND @pIktatasVege IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'IktatasDatuma BETWEEN ''', CONVERT(NVARCHAR, @pIktatasKezdete, 102), ''' AND ''', CONVERT(NVARCHAR, @pIktatasVege, 102), '''')
|
|
END
|
|
IF @pIktatoSzemely IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'LOWER(IktatoSzemelye) LIKE ''%', @pIktatoSzemely, '%''')
|
|
END
|
|
IF @pDokumentumStatusz IS NOT NULL BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', N' WHERE ', N' AND '), N'Statusz = ', @pDokumentumStatusz)
|
|
END
|
|
|
|
SET @sql += CONCAT(@whereCondition, ') data;')
|
|
|
|
--SET @sql = CONCAT(@sql, N'
|
|
--insert into #tmpIktDok
|
|
--SELECT * FROM fnGetIktatottDokumentumok(', @pTanevId, ',', @pIntezmenyId, ') AS aaa
|
|
--where aaa.ID in (select distinct FoszamosDokumentumId from #tmpIktDok)
|
|
-- and aaa.ID not in (select distinct ID from #tmpIktDok)')
|
|
|
|
SET @whereCondition = ''
|
|
|
|
declare @cnt int = 0
|
|
set @cnt = 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 @cnt > 0 BEGIN
|
|
SET @sql += ' left join (
|
|
select count(datadarab.C_IKTATOTTDOKUMENTUMID) darab, datadarab.C_IKTATOTTDOKUMENTUMID from (
|
|
select dksz.C_DOKUMENTUMKULCSSZOTIPUS, dksz.C_IKTATOTTDOKUMENTUMID from T_DOKUMENTUMKULCSSZOERTEK_OSSZES dksze
|
|
inner join T_DOKUMENTUMKULCSSZO_OSSZES 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 = ''', @pOsztalyId, ''')')
|
|
END
|
|
IF (@pEvfolyamId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7400 AND dksze.C_ERTEK = ''', @pEvfolyamId, ''')')
|
|
END
|
|
IF (@pKepzesTipusId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7402 AND dksze.C_ERTEK = ''', @pKepzesTipusId, ''')')
|
|
END
|
|
IF (@pOsztalyFonokId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7403 AND dksze.C_ERTEK = ''', @pOsztalyFonokId, ''')')
|
|
END
|
|
IF (@pTantervId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7404 AND dksze.C_ERTEK = ''', @pTantervId, ''')')
|
|
END
|
|
IF (@pOsztalyjellemzoId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7405 AND dksze.C_ERTEK = ''', @pOsztalyjellemzoId, ''')')
|
|
END
|
|
IF (@pSzakcsoportId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7406 AND dksze.C_ERTEK = ''', @pSzakcsoportId, ''')')
|
|
END
|
|
IF (@pAgazatId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7407 AND dksze.C_ERTEK = ''', @pAgazatId, ''')')
|
|
END
|
|
IF (@pSzakkepesitesId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7408 AND dksze.C_ERTEK = ''', @pSzakkepesitesId, ''')')
|
|
END
|
|
IF (@pReszszakkepesitesId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7409 AND dksze.C_ERTEK = ''', @pReszszakkepesitesId, ''')')
|
|
END
|
|
IF (@pCsoportnevId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7410 AND dksze.C_ERTEK = ''', @pCsoportnevId, ''')')
|
|
END
|
|
IF (@pCsoporttipusId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7411 AND dksze.C_ERTEK = ''', @pCsoporttipusId, ''')')
|
|
END
|
|
IF (@pCsoportjellemzoId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7413 AND dksze.C_ERTEK = ''', @pCsoportjellemzoId, ''')')
|
|
END
|
|
IF (@pTanuloNeve IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7414 AND dksze.C_ERTEK like ''%', @pTanuloNeve, '%'')')
|
|
END
|
|
IF (@pAlkalmazottId IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7415 AND dksze.C_ERTEK = ', @pAlkalmazottId, ')')
|
|
END
|
|
IF (@pTanuloOktAzon IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7417 AND dksze.C_ERTEK like ''%', @pTanuloOktAzon, '%'')')
|
|
END
|
|
IF (@pAlkalmazottOktAzon IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7418 AND dksze.C_ERTEK like ''%', @pAlkalmazottOktAzon, '%'')')
|
|
END
|
|
IF (@pGondviseloNeve IS NOT NULL)
|
|
BEGIN
|
|
SET @whereCondition += CONCAT(IIF(@whereCondition = '', '', N' OR '), N'(dksz.C_DOKUMENTUMKULCSSZOTIPUS = 7416 AND dksze.C_ERTEK like ''%', @pGondviseloNeve, '%'')')
|
|
END
|
|
SET @sql += @whereCondition
|
|
SET @sql += CONCAT(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'
|
|
DECLARE kurzor CURSOR FOR
|
|
SELECT ID FROM #selIktDok
|
|
DECLARE @IktatottDokumentumId INT
|
|
OPEN kurzor
|
|
FETCH NEXT FROM kurzor INTO @IktatottDokumentumId
|
|
WHILE @@FETCH_STATUS = 0 BEGIN'
|
|
END
|
|
SET @sql += CONCAT(N'
|
|
INSERT INTO @tabla
|
|
SELECT * FROM (
|
|
SELECT *, ROW_NUMBER() OVER (ORDER BY IktatasDatuma DESC) AS RowNumber FROM (
|
|
SELECT * FROM #selIktDok WHERE ID = ', IIF(@pFoszamosDokumentumId IS NULL, '@IktatottDokumentumId', CAST(@pFoszamosDokumentumId as nvarchar)), N'
|
|
OR
|
|
-- ha alszámos, akkor a főszámost
|
|
ID = (SELECT FoszamosDokumentumId FROM #selIktDok WHERE ID = ', IIF(@pFoszamosDokumentumId IS NULL, '@IktatottDokumentumId', CAST(@pFoszamosDokumentumId as nvarchar)), N')
|
|
OR
|
|
-- többi alszámos
|
|
FoszamosDokumentumId =
|
|
(SELECT FoszamosDokumentumId FROM #selIktDok WHERE ID = ', IIF(@pFoszamosDokumentumId IS NULL, '@IktatottDokumentumId', CAST(@pFoszamosDokumentumId as nvarchar)), N')
|
|
OR
|
|
-- főszámoshoz az alszámosak
|
|
FoszamosDokumentumId = ', IIF(@pFoszamosDokumentumId IS NULL, '@IktatottDokumentumId', CAST(@pFoszamosDokumentumId as nvarchar)), N'
|
|
) AS a
|
|
) AS b
|
|
WHERE RowNumber', IIF(@pFoszamosDokumentumId IS NULL, ' = ', ' > '), '1')
|
|
IF @pFoszamosDokumentumId IS NULL BEGIN
|
|
SET @sql += N'
|
|
FETCH NEXT FROM kurzor INTO @IktatottDokumentumId
|
|
END
|
|
CLOSE kurzor
|
|
DEALLOCATE kurzor'
|
|
END
|
|
SET @sql += CONCAT(N'
|
|
select distinct * from @tabla',
|
|
IIF(@pOrderBy IS NOT NULL, CONCAT(N' ORDER BY ', @pOrderby), ''), N';
|
|
drop table #selIktDok;
|
|
drop table #tmpIktDok;')
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
GO |