DROP PROCEDURE IF EXISTS uspGetPedagogusIgazolas
GO

CREATE PROCEDURE dbo.uspGetPedagogusIgazolas
   @pTanevId int
  ,@pAlkalmazottId int
AS
BEGIN
  
  ;WITH cimCte AS (
    SELECT 
       c.C_IRANYITOSZAM + ' ' + c.C_VAROS + ', ' + c.C_KOZTERULET + ' ' + c.C_KOZTERULETJELLEGENEV + ' ' + c.C_HAZSZAM + ISNULL(', ' + c.C_EMELET + '. emelet', '') + ISNULL(', ' + c.C_AJTO + '. ajtó', '') AS Lakcim
      ,1 AS CimOrder
    FROM T_CIM_OSSZES c
    WHERE c.TOROLT = 'F' AND c.C_FELHASZNALOID = @pAlkalmazottId AND c.C_ALAPERTELMEZETT = 'T'
    UNION
    SELECT 
       c.C_IRANYITOSZAM + ' ' + c.C_VAROS + ', ' + c.C_KOZTERULET + ' ' + c.C_KOZTERULETJELLEGENEV + ' ' + c.C_HAZSZAM + ISNULL(', ' + c.C_EMELET + '. emelet', '') + ISNULL(', ' + c.C_AJTO + '. ajtó', '') AS Lakcim
      ,2 AS CimOrder
    FROM T_CIM_OSSZES c
    WHERE c.TOROLT = 'F' AND c.C_FELHASZNALOID = @pAlkalmazottId AND c.C_CIMTIPUSA = 907
    UNION
    SELECT TOP 1
       c.C_IRANYITOSZAM + ' ' + c.C_VAROS + ', ' + c.C_KOZTERULET + ' ' + c.C_KOZTERULETJELLEGENEV + ' ' + c.C_HAZSZAM + ISNULL(', ' + c.C_EMELET + '. emelet', '') + ISNULL(', ' + c.C_AJTO + '. ajtó', '') AS Lakcim
      ,3 AS CimOrder
    FROM T_CIM_OSSZES c
    WHERE c.TOROLT = 'F' AND c.C_FELHASZNALOID = @pAlkalmazottId
    ORDER BY c.CREATED DESC
  )

  SELECT
     f.ID AS AlkalmazottId
    ,f.C_NYOMTATASINEV AS AlkalmazottNev
    ,f.C_OKTATASIAZONOSITO AS OktatasiAzonosito
    ,f.C_SZULETESIHELY AS SzuletesiHely
    ,FORMAT(f.C_SZULETESIDATUM, 'yyyy. MM. dd.') AS SzuletesiDatum
    ,c.Lakcim
    ,i.C_NEV AS IntezmenyNev
    ,mh.C_IRANYITOSZAM + ' ' + mh.C_VAROS + ', ' + mh.C_KOZTERULETNEV + ' ' + ISNULL(mh.C_KOZTERULETJELLEGENEV + ' ', '') + mh.C_HAZSZAM + ISNULL(', ' + mh.C_EMELET + '. emelet', '') + ISNULL(', ' + mh.C_AJTO + '. ajtó', '') AS FeladatellatasiHelyCime
    ,fh.ID AS FeladatEllatasiHelyId
    ,ma.C_MUNKAKORTIPUSA AS MunkakorTipusa
    ,ma.C_FOGLALKOZTATASTIPUS AS FoglalkoztatasTipus
  FROM T_FELHASZNALO_OSSZES f
  CROSS APPLY (SELECT TOP 1 Lakcim FROM cimCte ORDER BY CimOrder) c
  INNER JOIN T_INTEZMENYADATOK_OSSZES i ON i.TOROLT = 'F' AND i.C_TANEVID = f.C_TANEVID AND i.C_INTEZMENYID = f.C_INTEZMENYID
  INNER JOIN T_MUNKAUGYIADATOK_OSSZES ma ON ma.TOROLT = 'F' AND ma.C_ALKALMAZOTTID = f.ID
  INNER JOIN T_FELADATELLATASIHELY_OSSZES fh ON fh.TOROLT = 'F' AND fh.ID = ma.C_FELADATELLATASIHELYID
  INNER JOIN T_MUKODESIHELY_OSSZES mh ON mh.TOROLT = 'F' AND mh.ID = fh.C_MUKODESIHELYID
  WHERE f.TOROLT = 'F'
    AND f.C_TANEVID = @pTanevId
    AND f.ID = @pAlkalmazottId

END
GO