223 lines
9.7 KiB
Transact-SQL
223 lines
9.7 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'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 #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
|
|
IF @pFoszamosDokumentumId IS NULL BEGIN
|
|
--SET @sql += CONCAT(IIF(@cnt > 0, N' AND ', N' WHERE '), N'CHARINDEX(''-'', Iktatoszam) = 0')
|
|
SET @sql += CONCAT(IIF(@cnt > 0, N' AND ', N' WHERE '), N'FoszamosDokumentumId IS NULL')
|
|
END
|
|
ELSE IF @pFoszamosDokumentumId > 0 BEGIN
|
|
-- SET @sql += CONCAT(IIF(@cnt > 0, N' AND ', N' WHERE '), N'CHARINDEX((select
|
|
--SUBSTRING(C_IKTATOSZAM,
|
|
-- CHARINDEX(''/'',C_IKTATOSZAM) + 1,
|
|
-- CHARINDEX(''/'', C_IKTATOSZAM, CHARINDEX(''/'', C_IKTATOSZAM) + 2) - CHARINDEX(''/'', C_IKTATOSZAM) - 1
|
|
-- ) + ''-''
|
|
-- from T_IKTATOTTDOKUMENTUM_OSSZES where id = ', @pFoszamosDokumentumId, N'), Iktatoszam) > 0')
|
|
SET @sql += CONCAT(IIF(@cnt > 0, N' AND ', N' WHERE '), N'FoszamosDokumentumId = ', @pFoszamosDokumentumId)
|
|
END
|
|
IF @pOrderBy IS NOT NULL BEGIN
|
|
SET @sql += CONCAT(N' ORDER BY ', @pOrderby)
|
|
END
|
|
SET @sql += N';
|
|
drop table #tmpIktDok;'
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql @sql
|
|
|
|
END
|
|
|
|
GO |