IF OBJECT_ID('sp_GetRiportTanariAdatok') IS NOT NULL BEGIN
  DROP PROCEDURE sp_GetRiportTanariAdatok
END
GO

CREATE PROCEDURE sp_GetRiportTanariAdatok 
  @fenntartoId int,
  @intezmenyId int,
  @tanevNev nvarchar(20)  
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @intezmenyTable table (
    id int primary key,
    tanevId int
  )

  IF @fenntartoId IS NOT NULL AND @intezmenyId IS NOT NULL BEGIN
    INSERT INTO @intezmenyTable (id)
    SELECT i.ID FROM T_INTEZMENY i
    WHERE i.C_FENNTARTOAZONOSITO = @fenntartoId 
      AND i.ID = @intezmenyId
      AND i.TOROLT = 'F'
  END

  IF @fenntartoId IS NOT NULL AND @intezmenyId IS NULL BEGIN
    INSERT INTO @intezmenyTable (id)
    SELECT i.ID FROM T_INTEZMENY i
    WHERE i.C_FENNTARTOAZONOSITO = @fenntartoId
      AND i.TOROLT = 'F'
  END

  IF @fenntartoId IS NULL AND @intezmenyId IS NOT NULL BEGIN
    INSERT INTO @intezmenyTable (id)
    SELECT i.ID FROM T_INTEZMENY i 
    WHERE i.ID = @intezmenyId
      AND i.TOROLT = 'F'
  END

  IF @fenntartoId IS NULL AND @intezmenyId IS NULL BEGIN
    INSERT INTO @intezmenyTable (id)
    SELECT i.ID FROM T_INTEZMENY i 
    WHERE i.TOROLT = 'F'
  END

  IF NOT EXISTS (SELECT 1 FROM @intezmenyTable) BEGIN
    RETURN
  END  

  UPDATE i
    SET i.tanevId = t.ID
  FROM @intezmenyTable i
  INNER JOIN T_TANEV t ON t.C_INTEZMENYID = i.id
  WHERE t.C_NEV = @tanevNev
    AND t.TOROLT = 'F'
  
  SELECT 'NemRiport' AS ReportName, 
    CASE C_NEME 
      WHEN 4 THEN 'Férfi' 
      WHEN 5 THEN 'Nő' 
      ELSE 'Nincs adat' 
    END AS ReportKey1, NULL AS ReportKey2, COUNT(1) AS ReportValue 
  FROM T_FELHASZNALO f
    INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id  AND f.C_TANEVID = i.tanevId
  WHERE EXISTS (SELECT 1 FROM T_MUNKAUGYIADATOK m WHERE m.C_ALKALMAZOTTID = f.ID AND m.C_BETOLTETLENALLASHELY = 'F')
    AND f.TOROLT = 'F'
  GROUP BY C_NEME
  
  UNION ALL

  SELECT 'MunkaviszonyRiport' AS ReportName, d.C_NAME AS ReportKey1, NULL AS ReportKey2, COUNT(1) AS ReportValue 
  FROM T_FELHASZNALO f
    INNER JOIN T_MUNKAUGYIADATOK m ON f.ID = m.C_ALKALMAZOTTID
    INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
    LEFT JOIN T_DICTIONARYITEMBASE d ON d.C_INTEZMENYID = m.C_INTEZMENYID AND m.C_TANEVID = d.C_TANEVID AND d.ID = m.C_MUNKAVISZONYTIPUSA  
  WHERE m.C_BETOLTETLENALLASHELY = 'F'
    AND f.TOROLT = 'F'
  GROUP BY d.C_NAME
  
  UNION ALL

  SELECT 'ÖtévRiport',
    CASE f.C_NEME 
      WHEN 4 THEN 'Férfi' 
      WHEN 5 THEN 'Nő' 
      ELSE 'Nincs adat' 
    END, p.nev, COUNT(1)
  FROM T_FELHASZNALO f
    LEFT JOIN (VALUES 
      ('11-15', 11, 15), ('16-20', 16, 20), ('21-25', 21, 25), ('26-30', 26, 30), ('31-35', 31, 35), ('36-40', 36, 40), ('41-45', 41, 45),
      ('46-50', 46, 50), ('51-55', 51, 55), ('56-60', 56, 60), ('61-65', 61, 65), ('66-70', 66, 70), ('71-75', 71, 75),
      ('76-80', 76, 80), ('81-85', 81, 85), ('86-90', 86, 90), ('81-95', 81, 95), ('96-100', 96, 100), ('101-105', 101, 105)
    ) p (nev, also, felso) ON 
        CASE 
          WHEN DATEADD(YY, DATEDIFF(yy, f.C_SZULETESIDATUM, GETDATE()), f.C_SZULETESIDATUM) < GETDATE() THEN DATEDIFF(yy, f.C_SZULETESIDATUM, GETDATE())
          ELSE DATEDIFF(yy, f.C_SZULETESIDATUM,GETDATE()) - 1 
        END BETWEEN p.also AND p.felso
    INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
  WHERE EXISTS (SELECT 1 FROM T_MUNKAUGYIADATOK m WHERE m.C_ALKALMAZOTTID = f.ID AND m.C_BETOLTETLENALLASHELY = 'F')
    AND f.TOROLT = 'F'
  GROUP BY f.C_NEME, p.nev 
  
  UNION ALL

  SELECT 'TantargyRiport' AS ReportName, d.C_NAME AS ReportKey1, NULL AS ReportKey2, COUNT(DISTINCT f.C_TANARID) AS ReportValue 
  FROM T_FOGLALKOZAS f
    INNER JOIN T_TANTARGY t ON f.C_TANTARGYID = t.ID
    INNER JOIN T_DICTIONARYITEMBASE d ON d.C_INTEZMENYID = t.C_INTEZMENYID AND t.C_TANEVID = d.C_TANEVID AND d.ID = t.C_TARGYKATEGORIA
    INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id  AND f.C_TANEVID = i.tanevId
  WHERE f.TOROLT = 'F'
  GROUP BY d.C_NAME

END