DROP PROCEDURE IF EXISTS [dbo].[uspGetNemElerhetoOrarendiElemekData]
GO

CREATE PROCEDURE [dbo].[uspGetNemElerhetoOrarendiElemekData]
   @pTanevId int
AS BEGIN
  SET NOCOUNT ON;
  
SELECT DISTINCT
   oo.ID AS Id
  ,oo.C_ORAERVENYESSEGKEZDETE AS ErvenyessegKezdete
  ,oo.C_ORAERVENYESSEGVEGE AS ErvenyessegVege
  ,f.C_NYOMTATASINEV AS Tanar
  ,f.ID AS TanarId
  ,tgy.C_NEV AS Tantargy
  ,tgy.ID AS TantargyId
  ,IIF(f.C_NEVSORREND = 'T', f.C_UTONEV + ' ' + f.C_VEZETEKNEV, f.C_VEZETEKNEV + ' ' + f.C_UTONEV) AS TanarElotagNelkul
  ,ocs.C_NEV AS OsztalyCsoport
  ,ocs.ID AS OsztalyCsoportId
  ,hr.C_NAME AS Hetirend
  ,hr.ID AS HetirendId
  ,hn.C_NAME AS Nap
  ,hn.ID AS NapId
  ,t.C_NEV AS Terem
  ,t.ID AS TeremId
FROM T_ORARENDIORA_OSSZES oo
  LEFT JOIN T_ORAREND_OSSZES o ON o.C_ORARENDIORAID = oo.ID
  LEFT JOIN T_FELHASZNALO_OSSZES f ON f.ID = oo.C_TANARID AND f.TOROLT = 'F'
  INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = oo.C_TANTARGYID AND tgy.TOROLT = 'F'
  LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
  INNER JOIN T_DICTIONARYITEMBASE_OSSZES hr ON hr.ID = oo.C_HETIREND AND hr.TOROLT = 'F'
  INNER JOIN T_DICTIONARYITEMBASE_OSSZES hn ON hn.ID = oo.C_HETNAPJA AND hn.TOROLT = 'F'
  LEFT JOIN T_TEREM_OSSZES t ON t.ID = oo.C_TEREMID AND t.TOROLT = 'F'
WHERE o.C_ORARENDIORAID IS NULL
  AND oo.TOROLT = 'F'
  AND oo.C_TANEVID = @pTanevId

END