IF OBJECT_ID('fnGetDokumentumTanuloOrGondviseloCim') IS NOT NULL BEGIN
  DROP FUNCTION fnGetDokumentumTanuloOrGondviseloCim
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fnGetDokumentumTanuloOrGondviseloCim (@TanuloOrGondviselo BIT, @pTanevId INT, @pOsztalyId INT)

RETURNS @result TABLE
(
	TanuloId				INT,
	Cim						NVARCHAR(MAX),
	CimTipusa				INT,
	GondviseloId			INT
)
AS
BEGIN
  IF @TanuloOrGondviselo = 0 BEGIN
  INSERT INTO @result(TanuloId, Cim, CimTipusa, GondviseloId)
  SELECT 
      Tanulo.C_TANULOID AS TanuloId		
	  ,cim.C_IRANYITOSZAM + ' ' + cim.C_VAROS + ', ' + cim.C_KOZTERULET + ' ' + kozterulet.C_NAME + ' ' + cim.C_HAZSZAM	AS Cim
	  ,cim.C_CIMTIPUSA AS CimTipusa
	  ,cim.C_GONDVISELOID AS GondviseloId
  FROM T_TANULOCSOPORT_OSSZES Tanulo
	  CROSS APPLY (
            SELECT TOP 1 WITH TIES c.C_IRANYITOSZAM, c.C_VAROS, c.C_KOZTERULET, c.C_HAZSZAM, c.C_KOZTERULETJELLEGE, c.C_CIMTIPUSA, c.C_GONDVISELOID
					  FROM T_CIM_OSSZES c 
						  INNER JOIN T_DICTIONARYITEMBASE_OSSZES dic on dic.ID = c.C_CIMTIPUSA AND dic.C_TANEVID = @pTanevId
					  WHERE c.C_FELHASZNALOID = Tanulo.C_TANULOID 
						  AND c.C_TANEVID = @pTanevId 
						  AND c.TOROLT = 'F' 
						  AND c.C_GONDVISELOID IS NULL 
					  ORDER BY dic.C_NAME ) cim
	  INNER JOIN T_DICTIONARYITEMBASE_OSSZES kozterulet ON kozterulet.Id = cim.C_KOZTERULETJELLEGE AND kozterulet.C_TANEVID = @pTanevId
    WHERE Tanulo.C_TANEVID = @pTanevId
      AND Tanulo.C_OSZTALYCSOPORTID = @pOsztalyId
      AND Tanulo.TOROLT = 'F'
	END
	
  ELSE IF @TanuloOrGondviselo = 1 BEGIN
  INSERT INTO @result(TanuloId, Cim, CimTipusa, GondviseloId)
  SELECT
      Tanulo.C_TANULOID AS TanuloId
		,cim.C_IRANYITOSZAM + ' ' + cim.C_VAROS + ', ' + cim.C_KOZTERULET + ' ' + kozterulet.C_NAME + ' ' + cim.C_HAZSZAM + '.' + ' ' + ISNULL(cim.C_EMELET + '/', ' ') + ISNULL(cim.C_AJTO, ' ') AS Cim
		,cim.C_CIMTIPUSA AS CimTipusa
	  ,cim.C_GONDVISELOID AS GondviseloId
  FROM T_TANULOCSOPORT_OSSZES Tanulo
	  INNER JOIN T_GONDVISELO_OSSZES Gondviselo ON Gondviselo.C_TANULOID = Tanulo.C_TANULOID				
	  CROSS APPLY (
            SELECT TOP 1 WITH TIES c.C_IRANYITOSZAM, c.C_VAROS, c.C_KOZTERULET, c.C_HAZSZAM, c.C_KOZTERULETJELLEGE, c.C_CIMTIPUSA, c.C_GONDVISELOID, c.C_EMELET, c.C_AJTO 
					        FROM T_CIM_OSSZES c 
		          INNER JOIN T_DICTIONARYITEMBASE_OSSZES dic on dic.ID = c.C_CIMTIPUSA AND dic.C_TANEVID = @pTanevId AND dic.TOROLT = 'F'
					        WHERE c.C_GONDVISELOID = Gondviselo.ID 
						        AND c.C_TANEVID = @pTanevId 
						        AND c.TOROLT = 'F' 
						        AND c.C_GONDVISELOID IS NOT NULL 
					        ORDER BY dic.C_NAME ) cim
	  INNER JOIN T_DICTIONARYITEMBASE_OSSZES kozterulet ON kozterulet.Id = cim.C_KOZTERULETJELLEGE AND kozterulet.C_TANEVID = @pTanevId
  WHERE Tanulo.C_TANEVID = @pTanevId
    AND Tanulo.C_OSZTALYCSOPORTID = @pOsztalyId
    AND Tanulo.TOROLT = 'F'
  END

	RETURN
 END