DROP PROCEDURE IF EXISTS uspAdattisztitasAlkalmazott
GO

--EXEC uspAdattisztitasAlkalmazott 2, 1, 0

CREATE PROCEDURE uspAdattisztitasAlkalmazott
   @pIntezmenyId int
  ,@pTanevId int
  ,@pIsCount bit
AS BEGIN
  SET NOCOUNT ON;
  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_ADOAZONOSITOJEL 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_ADOAZONOSITOJEL IS NOT NULL  AND (LEN(C_ADOAZONOSITOJEL) <> 10 OR LEFT(C_ADOAZONOSITOJEL, 1) <> ''8'' OR ISNULL(TRY_CAST(SUBSTRING(C_ADOAZONOSITOJEL, 2, 5) AS INT), 0)  <> DATEDIFF(day, ''18670101'', C_SZULETESIDATUM)
      OR 
      IIF (
        ISNUMERIC(C_ADOAZONOSITOJEL) = 0, 
        0, 
          IIF(  
            CAST(SUBSTRING(C_ADOAZONOSITOJEL, 1, 1) * 1 + SUBSTRING(C_ADOAZONOSITOJEL, 2, 1) * 2 + SUBSTRING(C_ADOAZONOSITOJEL, 3, 1) * 3 + SUBSTRING(C_ADOAZONOSITOJEL, 4, 1) * 4 
              + SUBSTRING(C_ADOAZONOSITOJEL, 5, 1) * 5 + SUBSTRING(C_ADOAZONOSITOJEL, 6, 1) * 6 + SUBSTRING(C_ADOAZONOSITOJEL, 7, 1) * 7 + SUBSTRING(C_ADOAZONOSITOJEL, 8, 1) * 8
              + SUBSTRING(C_ADOAZONOSITOJEL, 9, 1) * 9 AS INT) % 11 <> ISNULL(TRY_CAST(SUBSTRING(C_ADOAZONOSITOJEL, 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_KOZTERULETJELLEGENEV  IS NULL OR c.C_KOZTERULETJELLEGENEV = ''Na'')
    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