513 lines
20 KiB
Transact-SQL
513 lines
20 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspAdattisztitasAlkalmazott
|
|
GO
|
|
|
|
--EXEC uspAdattisztitasAlkalmazott 2, 1, 0
|
|
|
|
CREATE PROCEDURE uspAdattisztitasAlkalmazott
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pIsCount bit
|
|
AS BEGIN
|
|
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
IF @pIsCount = 1 BEGIN
|
|
SET @sql = N'
|
|
SELECT
|
|
COUNT(DISTINCT x.AlkalmazottId) AS HibasAdatkorokSzama,
|
|
COUNT(1) AS HibakSzama'
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql = N'
|
|
SELECT
|
|
x.AlkalmazottId
|
|
,fh.C_OKTATASIAZONOSITO AS OktatasiAzonosito
|
|
,fh.C_NYOMTATASINEV AS AlkalmazottNev
|
|
,x.HibasErtek
|
|
,x.HibaKod
|
|
,ati.C_MEZO AS HibasAdat
|
|
,ati.C_LEIRAS AS Leiras'
|
|
END
|
|
|
|
SET @sql += N'
|
|
FROM (
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A001'' AS HibaKod
|
|
,d.C_NAME AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_DICTIONARYITEMBASE d WITH(NOLOCK) ON d.ID = fh.C_NEME AND fh.C_TANEVID = d.C_TANEVID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND C_NEME NOT IN (4,5) AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A002'' AS HibaKod
|
|
,C_VEZETEKNEV AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(LTRIM(RTRIM(C_VEZETEKNEV))) < 2
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A003'' AS HibaKod
|
|
,C_UTONEV AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(LTRIM(RTRIM(C_UTONEV))) < 2
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A004'' AS HibaKod
|
|
,C_SZULETESINEV AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(LTRIM(RTRIM(LEFT(C_SZULETESINEV, C_SZULETESINEVELVALASZTO)))) < 2 AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A005'' AS HibaKod
|
|
,C_SZULETESINEV AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(LTRIM(RTRIM(SUBSTRING(C_SZULETESINEV, C_SZULETESINEVELVALASZTO, 100)))) < 2 AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A006'' AS HibaKod
|
|
,C_ANYJANEVE AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(LTRIM(RTRIM(LEFT(C_ANYJANEVE, C_ANYJANEVEELVALASZTO)))) < 2 AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A007'' AS HibaKod
|
|
,C_ANYJANEVE AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(LTRIM(RTRIM(LEFT(C_ANYJANEVE, C_ANYJANEVEELVALASZTO)))) < 2 AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A008'' AS HibaKod
|
|
,C_SZULETESIHELY AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (LEN(LTRIM(RTRIM(C_SZULETESIHELY))) = 0 OR C_SZULETESIHELY IS NULL) AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A009'' AS HibaKod
|
|
,FORMAT(C_SZULETESIDATUM, ''yyyy.MM.dd.'') AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND C_SZULETESIDATUM NOT BETWEEN ''19400101'' AND ''20200312'' AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
/*
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A010'' AS HibaKod
|
|
,d.C_NAME + ISNULL(C_IGAZOLVANYSZAM, '' üres'') AS HibasErtek
|
|
FROM T_FELHASZNALO fh
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_DICTIONARYITEMBASE d WITH(NOLOCK) ON d.ID = fh.C_IGAZOLVANYTIPUSA AND d.C_TANEVID = fh.C_TANEVID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (C_IGAZOLVANYTIPUSA <> 543 OR C_IGAZOLVANYTIPUSA IS NULL) AND C_IGAZOLVANYSZAM IS NULL
|
|
|
|
UNION ALL
|
|
*/
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A011'' AS HibaKod
|
|
,CAST(C_ADOSZAM AS nvarchar(50)) AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND C_ADOSZAM IS NOT NULL AND (LEN(C_ADOSZAM) <> 10 OR LEFT(C_ADOSZAM, 1) <> ''8'' OR ISNULL(TRY_CAST(SUBSTRING(C_ADOSZAM, 2, 5) AS INT), 0) <> DATEDIFF(day, ''18670101'', C_SZULETESIDATUM)
|
|
OR
|
|
IIF (
|
|
ISNUMERIC(C_ADOSZAM) = 0,
|
|
0,
|
|
IIF(
|
|
CAST(SUBSTRING(C_ADOSZAM, 1, 1) * 1 + SUBSTRING(C_ADOSZAM, 2, 1) * 2 + SUBSTRING(C_ADOSZAM, 3, 1) * 3 + SUBSTRING(C_ADOSZAM, 4, 1) * 4
|
|
+ SUBSTRING(C_ADOSZAM, 5, 1) * 5 + SUBSTRING(C_ADOSZAM, 6, 1) * 6 + SUBSTRING(C_ADOSZAM, 7, 1) * 7 + SUBSTRING(C_ADOSZAM, 8, 1) * 8
|
|
+ SUBSTRING(C_ADOSZAM, 9, 1) * 9 AS INT) % 11 <> ISNULL(TRY_CAST(SUBSTRING(C_ADOSZAM, 10, 1) AS INT
|
|
), 12), 0, 1)) = 0)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A012'' AS HibaKod
|
|
,d.C_NAME AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
INNER JOIN T_DICTIONARYITEMBASE d WITH(NOLOCK) ON d.ID = c.C_CIMTIPUSA AND fh.C_TANEVID = d.C_TANEVID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (c.C_CIMTIPUSA = 906 OR c.C_CIMTIPUSA IS NULL)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A013'' AS HibaKod
|
|
,''Üres'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (LTRIM(RTRIM(c.C_IRANYITOSZAM)) = '''' OR c.C_IRANYITOSZAM IS NULL)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A014'' AS HibaKod
|
|
,c.C_IRANYITOSZAM AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LTRIM(RTRIM(c.C_IRANYITOSZAM)) <> ''''
|
|
AND C_ORSZAG = 765 AND NOT EXISTS (SELECT 1 FROM T_TELEPULES x WHERE c.C_IRANYITOSZAM = x.C_IRANYITOSZAM)
|
|
|
|
/*UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A015'' AS HibaKod
|
|
,''IranyitoszamTelepules'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
INNER JOIN T_TANEV tv WITH(NOLOCK) ON tv.ID = fh.C_TANEVID AND tv.C_NEV = ''2019/2020'' AND tv.TOROLT = ''F''
|
|
INNER JOIN T_INTEZMENY i WITH(NOLOCK) ON i.ID = fh.C_INTEZMENYID AND i.C_FENNTARTOAZONOSITO < 900 AND i.TOROLT = ''F''
|
|
INNER JOIN T_TELEPULES t WITH(NOLOCK) ON t.C_IRANYITOSZAM = c.C_IRANYITOSZAM AND c.C_VAROS <> t.C_TELEPULESNEV
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LTRIM(RTRIM(c.C_IRANYITOSZAM)) <> ''''
|
|
AND c.C_ORSZAG = 765 */
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A016'' AS HibaKod
|
|
,c.C_VAROS AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (LTRIM(RTRIM(c.C_VAROS)) = '''' OR c.C_VAROS IS NULL)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
/*
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A017'' AS HibaKod
|
|
,c.C_VAROS AS HibasErtek--, w.C_TELEPULESNEV
|
|
FROM T_FELHASZNALO fh
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
-- LEFT JOIN T_TELEPULES w WITH(NOLOCK) ON w.C_IRANYITOSZAM = c.C_IRANYITOSZAM AND c.C_VAROS <> w.C_TELEPULESNEV
|
|
-- INNER JOIN T_TANEV tv WITH(NOLOCK) ON tv.ID = fh.C_TANEVID AND tv.C_AKTIV = ''T''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LTRIM(RTRIM(c.C_IRANYITOSZAM)) <> ''''
|
|
AND c.C_ORSZAG = 765 AND NOT EXISTS (SELECT 1 FROM T_TELEPULES x WHERE c.C_VAROS = x.C_TELEPULESNEV)
|
|
*/
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A018'' AS HibaKod
|
|
,''Üres'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (LTRIM(RTRIM(c.C_KOZTERULET)) = '''' OR c.C_KOZTERULET IS NULL)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A019'' AS HibaKod
|
|
,''Üres'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (c.C_KOZTERULETJELLEGE = 911 OR c.C_KOZTERULETJELLEGE IS NULL)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A020'' AS HibaKod
|
|
,''Üres'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_CIM c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (LTRIM(RTRIM(c.C_HAZSZAM)) = '''' OR c.C_HAZSZAM IS NULL)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A021'' AS HibaKod
|
|
,c.C_TELEFONSZAM AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_TELEFON c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND LEN(REPLACE(REPLACE(REPLACE(c.C_TELEFONSZAM, ''/'', ''''), ''-'', ''''), '' '', '''')) < 10
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A022'' AS HibaKod
|
|
,c.C_EMAILCIM AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_EMAIL c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (c.C_EMAILCIM NOT LIKE ''%@%'' OR c.C_EMAILCIM NOT LIKE ''%.%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A023'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_EMAIL c WITH(NOLOCK) ON c.C_FELHASZNALOID = fh.ID AND c.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (c.C_EMAILTIPUSA IS NULL OR c.C_EMAILTIPUSA = 1024)
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A026'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (ma.C_MUNKAKORTIPUSA IS NULL OR ma.C_MUNKAKORTIPUSA = 548)
|
|
AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A028'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND (ma.C_FOGLALKOZTATASTIPUS IS NULL OR ma.C_FOGLALKOZTATASTIPUS = 1338)
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A029'' AS HibaKod
|
|
,CAST(C_RESZMUNKAIDOSZAZALEK AS nvarchar(10)) AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_FOGLALKOZTATASTIPUS = 6466 AND C_RESZMUNKAIDOSZAZALEK NOT BETWEEN 1 AND 99
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A030'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_ALKALMAZASKEZDETE IS NULL
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A031'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_ALKALMAZASKEZDETE IS NOT NULL
|
|
AND (ma.C_MUNKAKORTIPUSA = 6710 OR C_ELOTAG IN (''KA'', ''BTA'', ''KGK''))
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A032'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_ALKALMAZASMEGSZUNESE IS NULL
|
|
AND ma.C_MUNKAKORTIPUSA <> 6710 AND (C_ELOTAG NOT LIKE ''%KA%'' OR C_ELOTAG NOT LIKE ''%BTA%'' OR C_ELOTAG NOT LIKE ''%KGK%'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A033'' AS HibaKod
|
|
,''Üres / NA'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_ALKALMAZASMEGSZUNESE IS NOT NULL
|
|
AND (ma.C_MUNKAKORTIPUSA = 6710 OR C_ELOTAG IN (''KA'', ''BTA'', ''KGK''))
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A035'' AS HibaKod
|
|
,IIF((ma.C_BETOLTETLENALLASHELY = ''T'' AND C_ELOTAG NOT LIKE ''%BTA%''), ''Hibás előtag'', ''Hibás betöltetlen álláshelynek jelölés'') AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ((ma.C_BETOLTETLENALLASHELY = ''T'' AND C_ELOTAG NOT LIKE ''%BTA%'')
|
|
OR (ma.C_BETOLTETLENALLASHELY = ''F'' AND C_ELOTAG LIKE ''%BTA%''))
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A040'' AS HibaKod
|
|
,''Na'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_ALKALMAZOTT a WITH(NOLOCK) ON a.ID = fh.ID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND a.C_SZAKERTOIVIZSGAELNOKI = ''T'' AND ma.C_BESOROLASIFOKOZAT = 3040
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A042'' AS HibaKod
|
|
,''Na'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_ALKALMAZOTT a WITH(NOLOCK) ON a.ID = fh.ID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND a.C_TOVABBKEPZES = ''T'' AND ma.C_BESOROLASIFOKOZAT = 3040
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A044'' AS HibaKod
|
|
,''Na'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_ALKALMAZOTT a WITH(NOLOCK) ON a.ID = fh.ID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND a.C_SZAKVIZSGA = ''T'' AND ma.C_BESOROLASIFOKOZAT = 3040
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A046'' AS HibaKod
|
|
,''Na'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_ALKALMAZOTT a WITH(NOLOCK) ON a.ID = fh.ID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_ATTANITO = ''T'' AND ma.C_BESOROLASIFOKOZAT = 3040
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A048'' AS HibaKod
|
|
,''Na'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_ALKALMAZOTT a WITH(NOLOCK) ON a.ID = fh.ID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND ma.C_ATTANITOMASINTEZMENYNEL = ''T'' AND ma.C_BESOROLASIFOKOZAT = 3040
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
fh.ID AS AlkalmazottId
|
|
,''A049'' AS HibaKod
|
|
,''Üres'' AS HibasErtek
|
|
FROM T_FELHASZNALO fh WITH(NOLOCK)
|
|
INNER JOIN T_MUNKAUGYIADATOK ma WITH(NOLOCK) ON ma.C_ALKALMAZOTTID = fh.ID AND ma.TOROLT = ''F''
|
|
INNER JOIN T_ALKALMAZOTT a WITH(NOLOCK) ON a.ID = fh.ID
|
|
WHERE fh.TOROLT = ''F'' AND fh.C_TANEVID = @pTanevId
|
|
AND a.C_SZTSZKOD IS NULL
|
|
|
|
) x'
|
|
|
|
IF @pIsCount = 1 BEGIN
|
|
SET @sql += N'
|
|
GROUP BY x.AlkalmazottId'
|
|
END
|
|
ELSE BEGIN
|
|
SET @sql += N'
|
|
INNER JOIN T_FELHASZNALO fh WITH(NOLOCK) ON fh.ID = x.AlkalmazottId AND fh.C_INTEZMENYID = @pIntezmenyId AND fh.C_TANEVID = @pTanevId
|
|
INNER JOIN T_ADATTISZTITASINDIKATOR ati ON ati.C_KOD = x.HibaKod'
|
|
END
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pIsCount bit'
|
|
,@pIntezmenyId = @pIntezmenyId
|
|
,@pTanevId = @pTanevId
|
|
,@pIsCount = @pIsCount
|
|
|
|
END
|
|
GO
|