DROP PROCEDURE IF EXISTS uspTanuloKereses
GO

CREATE PROCEDURE [dbo].[uspTanuloKereses]
   @pTorolt char(1)
  ,@pDatum datetime = NULL
  ,@pTanuloBelepesNelkul bit = 0
  ,@pNincsAllampolgarsag int = NULL
  ,@pNincsAnyanyelv int = NULL
  ,@pFelvetelEve int = NULL
  ,@pTanevId int
  ,@pFeladatKategoriaId int = NULL
  ,@pTanuloNeve nvarchar(255) = NULL
  ,@pNeme int = NULL
  ,@pSzuletesiIdoKezdet datetime = NULL
  ,@pSzuletesiIdoVeg datetime = NULL
  ,@pSzuletesiHely nvarchar(50) = NULL
  ,@pOktatasiAzonosito nvarchar(20) = NULL
  ,@pOsztalyCsoportId int = NULL
  ,@pVegzosEvfolyam char(1) = NULL
  ,@pSNI char(1) = NULL
  ,@pBTM char(1) = NULL
  ,@pFeladatellatasiHelyId int = NULL
  ,@pHatranyosHelyzetId int = NULL
  ,@pEvfolyamTipusaId int = NULL
  ,@pOsztalybaSoroltTantervNelkul bit = NULL
  ,@pAktiv bit = NULL
  ,@pMuveszetiAgId int = NULL
  ,@pNincsOsztondijAlapAdat bit = 0
  ,@pNincsOsztondijUtalasAdat bit = 0
  ,@pAnyjaNeve nvarchar(64) = NULL
  ,@pVanKettosOsztalybesorolas bit = 0
  ,@pTanterviJellegId int = NULL
AS BEGIN
SET NOCOUNT ON
  DECLARE @sql nvarchar(max);

IF (@pOsztalyCsoportId IS NOT NULL) BEGIN

  CREATE TABLE #TanugyiAdatok(
     OsztalyId int
    ,TantervNev nvarchar(255)
    ,Osztaly nvarchar(255)
    ,C_TANKOTELEZETT char(1)
    ,C_MUVESZETIAGID int
    ,TanuloId int PRIMARY KEY CLUSTERED
    ,C_VEGZOSEVFOLYAM char(1)
    ,C_FELADATKATEGORIAID int
    ,C_FELADATELLATASIHELYID int
    ,C_EVFOLYAMTIPUSA int
    ,C_TANTERVIJELLEGID int
  )
    
  INSERT INTO #TanugyiAdatok(
     OsztalyId
    ,TantervNev
    ,Osztaly
    ,C_TANKOTELEZETT
    ,C_MUVESZETIAGID
    ,TanuloId 
    ,C_VEGZOSEVFOLYAM
    ,C_FELADATKATEGORIAID
    ,C_FELADATELLATASIHELYID 
    ,C_EVFOLYAMTIPUSA
    ,C_TANTERVIJELLEGID
  )
  SELECT
     ocs.ID
    ,tr.C_NEV
    ,ISNULL(ocs.C_NEV,(SELECT OsztalyNev FROM dbo.fnGetTanuloOsztalyString(tta.TanuloId,@pDatum,ISNULL(ocs.C_FELADATKATEGORIAID,ocso.C_FELADATKATEGORIAID),@pAktiv,default, NULL)))
    ,tta.C_TANKOTELEZETT
    ,ocs.C_MUVESZETIAGID
    ,tta.TanuloId
    ,ocs.C_VEGZOSEVFOLYAM
    ,ISNULL(ocs.C_FELADATKATEGORIAID,ocso.C_FELADATKATEGORIAID)
    ,ISNULL(ocs.C_FELADATELLATASIHELYID,ocso.C_FELADATELLATASIHELYID)
    ,ISNULL(tta.C_TANULOEVFOLYAMTIPUSID,ocs.C_EVFOLYAMTIPUSA)
    ,tta.C_TANTERVIJELLEGID
  FROM fnGetOsztalyCsoportTanuloinakTanugyiAdatai(@pOsztalyCsoportId ,@pDatum) tta
    LEFT JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID = tta.C_TANULOCSOPORTID AND tcs.TOROLT='F'
    LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT='F'
    LEFT JOIN T_TANTERV_OSSZES tr ON tr.ID = tta.C_TANTERVID AND tr.TOROLT='F'
    LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = @pOsztalyCsoportId AND ocso.TOROLT='F'
END

IF (@pNincsOsztondijUtalasAdat = 1) BEGIN
  CREATE TABLE #JuttatasAdatok(
    TanuloId int
    ,Bankszamlaszam nvarchar(255)
    ,BankszamlaTulajdonosa int
    ,BankszamlaTulajdonosNeve nvarchar(255)
    ,TajSzam nvarchar(255)
    ,OsztondijOsszege int
    ,IsJogosult char(1)
  )

  INSERT INTO #JuttatasAdatok(
    TanuloId
    ,Bankszamlaszam
    ,BankszamlaTulajdonosa
    ,BankszamlaTulajdonosNeve
    ,TajSzam
    ,OsztondijOsszege
    ,IsJogosult
  )
  SELECT
    f.ID AS TanuloId
    ,t.C_BANKSZAMLASZAM AS Bankszamlaszam
    ,t.C_BANKSZAMLATULAJDONOSTIPUSID AS BankszamlaTulajdonosa
    ,t.C_BANKSZAMLATULAJNEVE AS BankszamlaTulajdonosNeve
    ,f.C_TAJSZAM AS TajSzam
    ,j.C_OSSZEG AS OsztondijOsszege
    ,j.C_ISJOGOSULT AS IsJogosult
  FROM T_TANULO_OSSZES t
    INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = t.ID AND f.TOROLT = 'F'
    INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = t.ID AND tcs.TOROLT = 'F'
    INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID AND o.TOROLT = 'F'
    INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = o.ID AND ocs.TOROLT = 'F'
    LEFT JOIN T_TANULOTANUGYIADATOK_OSSZES tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
    LEFT JOIN T_JUTTATAS_OSSZES j ON j.C_TANULOID = t.ID AND j.TOROLT = 'F' AND j.C_ISAKTIV = 'T' AND j.C_JUTTATASTIPUSID = 7888
  WHERE
    t.C_ALTANEVID = @pTanevId
    AND t.TOROLT = 'F'
END

IF (@pVanKettosOsztalybesorolas = 1) BEGIN
  CREATE TABLE #KettosOsztalybesorolasuak(
     TanuloId int
  )

  INSERT INTO #KettosOsztalybesorolasuak(TanuloId)
  SELECT
    x.TanuloId
  FROM (
    SELECT
       f.ID AS TanuloId
      ,tcs.C_BELEPESDATUM
      ,tcs.C_KILEPESDATUM
      ,COUNT(1) OVER (PARTITION BY f.ID) AS CNT
    FROM T_TANULO_OSSZES t
      INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = t.ID AND f.TOROLT = 'F'
      INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = t.ID AND tcs.TOROLT = 'F'
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs
        INNER JOIN T_OSZTALY_OSSZES o ON o.ID = ocs.ID AND o.TOROLT = 'F'
      ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
    WHERE t.TOROLT = 'F'
      AND ocs.C_FELADATKATEGORIAID = 7553
      AND tcs.C_BELEPESDATUM <= CAST(GETDATE() AS DATE)  
      AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > CAST(GETDATE() AS DATE) )
  ) x
  WHERE x.CNT > 1

END

SET @sql = N'
SELECT NEWID() AS ID, * FROM (
SELECT DISTINCT  
  T_FELHASZNALO_OSSZES.ID AS TanuloId
  ,tanugyiAdatok.OsztalyId AS OsztalyCsoportId
  ,T_FELHASZNALO_OSSZES.C_NYOMTATASINEV AS TanuloNeve
  ,IIF(T_FELHASZNALO_OSSZES.C_NEVSORREND = ''T'',
    T_FELHASZNALO_OSSZES.C_UTONEV + '' '' + T_FELHASZNALO_OSSZES.C_VEZETEKNEV,
    T_FELHASZNALO_OSSZES.C_VEZETEKNEV + '' '' + T_FELHASZNALO_OSSZES.C_UTONEV) AS TanuloNeveElotagNelkul
  ,T_FELHASZNALO_OSSZES.c_anyjaneve AS AnyjaNeve
  ,T_FELHASZNALO_OSSZES.C_OKTATASIAZONOSITO AS OktatasiAzonosito
  ,tanugyiAdatok.TantervNev AS Tanterv
  ,tanugyiAdatok.Osztaly AS Osztaly
  ,T_FELHASZNALO_OSSZES.C_SZULETESIHELY AS SzuletesiHely
  ,T_FELHASZNALO_OSSZES.C_SZULETESIDATUM AS SzuletesiIdo
  ,ISNULL(tanugyiAdatok.C_TANKOTELEZETT, T_TANULO_OSSZES.C_TANKOTELEZETT) AS Tankotelezett
  ,tanugyiAdatok.C_MUVESZETIAGID as MuveszetiAgId
  ,tanugyiAdatok.C_EVFOLYAMTIPUSA AS EvfolyamTipusId
  ,T_TANULO_OSSZES.TOROLT AS IsArchivalt
  ,T_FELHASZNALO_OSSZES.C_TAJSZAM AS TajSzam
  ,T_FELHASZNALO_OSSZES.C_ADOAZONOSITOJEL AS AdoazonositoJel
FROM T_FELHASZNALO_OSSZES' 
+ IIF(@pOsztalyCsoportId IS NOT NULL, '
  INNER JOIN #TanugyiAdatok tanugyiAdatok ON tanugyiAdatok.TanuloId = T_FELHASZNALO_OSSZES.ID ', '
  LEFT JOIN
    (SELECT 
       tanulo.ID TanuloId
      ,tta.C_TANKOTELEZETT
      ,tcso.C_OSZTALYCSOPORTID OsztalyId
      ,ocs.C_NEV Osztaly
      ,ocs.C_MUVESZETIAGID
      ,tanterv.C_NEV TantervNev
      ,ocs.C_VEGZOSEVFOLYAM
      ,ocs.C_FELADATELLATASIHELYID
      ,ocs.C_FELADATKATEGORIAID
      ,IIF(ocs.C_EVFOLYAMTIPUSA = 7366, tta.C_TANULOEVFOLYAMTIPUSID, ocs.C_EVFOLYAMTIPUSA) AS C_EVFOLYAMTIPUSA
      ,tta.C_TANTERVIJELLEGID
    FROM T_TANULO_OSSZES tanulo
      INNER JOIN T_TANULOCSOPORT_OSSZES tcso ON tcso.C_TANULOID = tanulo.ID 
        AND tcso.TOROLT=''F'' 
        AND tcso.C_BELEPESDATUM <= @pDatum  
        AND (tcso.C_KILEPESDATUM IS NULL OR tcso.C_KILEPESDATUM > @pDatum )
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tcso.C_OSZTALYCSOPORTID AND ocs.TOROLT = ''F''
      INNER JOIN T_TANULOTANUGYIADATOK_OSSZES tta ON tta.C_TANULOCSOPORTID = tcso.ID AND tta.TOROLT=''F''
      INNER JOIN T_TANTERV_OSSZES tanterv on tanterv.ID = tta.C_TANTERVID AND tanterv.TOROLT = ''F''
    WHERE tanulo.C_ALTANEVID =  @pTanevId
    ) as tanugyiAdatok ON tanugyiAdatok.TanuloId = T_FELHASZNALO_OSSZES.ID ') + '
  INNER JOIN T_TANULO_OSSZES ON T_FELHASZNALO_OSSZES.ID = T_TANULO_OSSZES.ID
  INNER JOIN T_TANEV_OSSZES ON T_TANEV_OSSZES.ID = T_TANULO_OSSZES.C_FELVETELTANEVEID '
 + IIF(@pAktiv = 1, '  INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = tanugyiAdatok.TanuloId
        AND tcs.C_OSZTALYCSOPORTID = tanugyiAdatok.OsztalyId
        AND tcs.TOROLT=''F'' 
        AND tcs.C_BELEPESDATUM <= CAST(GETDATE() AS DATE)  
        AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > CAST(GETDATE() AS DATE) ) ', '')
+ IIF(@pNincsOsztondijAlapAdat = 1, ' INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = T_FELHASZNALO_OSSZES.ID AND tcs.TOROLT = ''F'' ',' ')
+ IIF(@pNincsOsztondijUtalasAdat = 1, ' INNER JOIN #JuttatasAdatok jut ON jut.TanuloId = T_FELHASZNALO_OSSZES.ID ', ' ')
+ IIF(@pVanKettosOsztalybesorolas = 1, ' INNER JOIN #KettosOsztalybesorolasuak ko ON ko.TanuloId = T_FELHASZNALO_OSSZES.ID ', ' ')

SET @sql += N' 
WHERE T_FELHASZNALO_OSSZES.TOROLT= @pTorolt
  AND T_TANULO_OSSZES.TOROLT= @pTorolt
  AND T_FELHASZNALO_OSSZES.C_TANEVID =  @pTanevId '
  + IIF(@pTanuloNeve <> '', ' AND T_FELHASZNALO_OSSZES.C_NYOMTATASINEV LIKE ''%'' + @pTanuloNeve + ''%'' ', '') + 
  + IIF(@pNeme IS NOT NULL, ' AND T_FELHASZNALO_OSSZES.C_NEME = @pNeme ', '') + 
  + IIF(@pSzuletesiIdoKezdet IS NOT NULL, ' AND T_FELHASZNALO_OSSZES.C_SZULETESIDATUM >= @pSzuletesiIdoKezdet ', '') + 
  + IIF(@pSzuletesiIdoVeg IS NOT NULL, ' AND T_FELHASZNALO_OSSZES.C_SZULETESIDATUM <= @pSzuletesiIdoVeg ', '') + 
  + IIF(@pAnyjaNeve IS NOT NULL, ' AND T_FELHASZNALO_OSSZES.C_ANYJANEVE LIKE ''%'' + @pAnyjaNeve + ''%'' ', '') + 
  + IIF(@pSzuletesiHely IS NOT NULL, ' AND T_FELHASZNALO_OSSZES.C_SZULETESIHELY LIKE ''%'' + @pSzuletesiHely + ''%'' ', '') + 
  + IIF(@pOktatasiAzonosito IS NOT NULL AND @pOktatasiAzonosito <> '',' AND T_FELHASZNALO_OSSZES.C_OKTATASIAZONOSITO LIKE ''%'' + @pOktatasiAzonosito + ''%'' ', '') + 
  + IIF(@pVegzosEvfolyam IS NOT NULL AND @pVegzosEvfolyam <> '', ' AND tanugyiAdatok.C_VEGZOSEVFOLYAM = @pVegzosEvfolyam ', '') + 
  + IIF(@pSNI <> '', ' AND T_TANULO_OSSZES.C_SAJATOSNEVELESU = @pSNI ', '') + 
  + IIF(@pBTM <> '', ' AND T_TANULO_OSSZES.C_BTMPROBLEMAS = @pBTM ', '') + 
  + IIF(@pFeladatellatasiHelyId IS NOT NULL, ' AND tanugyiAdatok.C_FELADATELLATASIHELYID = @pFeladatellatasiHelyId ', '') + 
  + IIF(@pHatranyosHelyzetId IS NOT NULL, ' AND T_TANULO_OSSZES.C_HATRANYOSHELYZETU = @pHatranyosHelyzetId ', '') + 
  + IIF(@pEvfolyamTipusaId IS NOT NULL, ' AND tanugyiAdatok.C_EVFOLYAMTIPUSA = @pEvfolyamTipusaId ', '') + 
  + IIF(@pOsztalybaSoroltTantervNelkul IS NOT NULL AND @pOsztalybaSoroltTantervNelkul = 1, '  AND T_TANULO_OSSZES.C_TANTERVID IS NULL AND tanugyiAdatok.OsztalyId IS NOT NULL ', '') + 
  + IIF(@pFeladatKategoriaId IS NOT NULL, ' AND tanugyiAdatok.C_FELADATKATEGORIAID = @pFeladatKategoriaId ', '') + 
  + IIF(@pMuveszetiAgId IS NOT NULL, ' AND tanugyiAdatok.C_MUVESZETIAGID = @pMuveszetiAgId ', '') +
  + IIF(@pTanterviJellegId IS NOT NULL, ' AND tanugyiAdatok.C_TANTERVIJELLEGID = @pTanterviJellegId ', '') + 
  + IIF(@pNincsAllampolgarsag IS NOT NULL, ' AND T_FELHASZNALO_OSSZES.C_ALLAMPOLGARSAGA = @pNincsAllampolgarsag ', '') + 
  + IIF(@pNincsAnyanyelv IS NOT NULL, ' AND (T_FELHASZNALO_OSSZES.C_ANYANYELVE = @pNincsAnyanyelv OR T_FELHASZNALO_OSSZES.C_ANYANYELVE IS NULL) ', '') + 
  + IIF(@pTanuloBelepesNelkul = 1, ' AND NOT EXISTS(SELECT C_FELHASZNALOID FROM T_FELHASZNALOBELEPES_OSSZES fb WHERE fb.C_FELHASZNALOID = T_FELHASZNALO_OSSZES.ID AND fb.TOROLT = ''F'' AND fb.C_GONDVISELOID IS NULL) ', '') + 
  + IIF(@pFelvetelEve IS NOT NULL, ' AND T_TANEV_OSSZES.ID = @pFelvetelEve ', '') + 
  + IIF(@pNincsOsztondijAlapAdat = 1, ' AND (T_FELHASZNALO_OSSZES.C_NYOMTATASINEV IS NULL OR T_FELHASZNALO_OSSZES.C_ANYJANEVE = '''' OR T_FELHASZNALO_OSSZES.C_ANYJANEVE IS NULL OR T_FELHASZNALO_OSSZES.C_SZULETESIHELY = '''' OR T_FELHASZNALO_OSSZES.C_SZULETESIHELY IS NULL OR T_FELHASZNALO_OSSZES.C_SZULETESIDATUM IS NULL OR T_FELHASZNALO_OSSZES.C_OKTATASIAZONOSITO = '''' OR T_FELHASZNALO_OSSZES.C_OKTATASIAZONOSITO IS NULL OR tanugyiAdatok.Osztaly IS NULL OR tanugyiAdatok.C_EVFOLYAMTIPUSA IS NULL ) ', '') + 
  + IIF(@pNincsOsztondijUtalasAdat = 1, ' AND ((ISNULL(jut.Bankszamlaszam, '''') = '''' OR jut.BankszamlaTulajdonosa IS NULL OR ISNULL(jut.BankszamlaTulajdonosNeve, '''') = '''' OR ISNULL(jut.TajSzam, '''') = '''' OR ISNULL(jut.OsztondijOsszege, 0) <= 0)  AND jut.IsJogosult = ''T'' ) ', '') + 
  ' AND T_TANULO_OSSZES.C_ALTANEVID =  @pTanevId ) as x'

EXEC sp_executesql @sql, N'
     @pTorolt char(1)
    ,@pDatum datetime = NULL
    ,@pTanuloBelepesNelkul bit = 0
    ,@pNincsAllampolgarsag int = NULL
    ,@pNincsAnyanyelv int = NULL
    ,@pFelvetelEve int = NULL
    ,@pTanevId int
    ,@pFeladatKategoriaId int = NULL
    ,@pTanuloNeve nvarchar(255) = NULL
    ,@pNeme int = NULL
    ,@pSzuletesiIdoKezdet datetime = NULL
    ,@pSzuletesiIdoVeg datetime = NULL
    ,@pSzuletesiHely nvarchar(50) = NULL
    ,@pOktatasiAzonosito nvarchar(20) = NULL
    ,@pOsztalyCsoportId int = NULL
    ,@pVegzosEvfolyam char(1) = NULL
    ,@pSNI char(1) = NULL
    ,@pBTM char(1) = NULL
    ,@pFeladatellatasiHelyId int = NULL
    ,@pHatranyosHelyzetId int = NULL
    ,@pEvfolyamTipusaId int = NULL
    ,@pOsztalybaSoroltTantervNelkul bit = NULL
    ,@pAktiv bit = NULL
    ,@pMuveszetiAgId int = NULL 
    ,@pNincsOsztondijAlapAdat bit = NULL
    ,@pNincsOsztondijUtalasAdat bit = NULL
    ,@pAnyjaNeve nvarchar(64) = NULL
    ,@pTanterviJellegId int = NULL'
    ,@pTorolt = @pTorolt
    ,@pDatum = @pDatum
    ,@pTanuloBelepesNelkul = @pTanuloBelepesNelkul
    ,@pNincsAllampolgarsag = @pNincsAllampolgarsag
    ,@pNincsAnyanyelv = @pNincsAnyanyelv
    ,@pFelvetelEve = @pFelvetelEve
    ,@pTanevId = @pTanevId
    ,@pFeladatKategoriaId = @pFeladatKategoriaId
    ,@pTanuloNeve = @pTanuloNeve
    ,@pNeme = @pNeme
    ,@pSzuletesiIdoKezdet = @pSzuletesiIdoKezdet
    ,@pSzuletesiIdoVeg = @pSzuletesiIdoVeg
    ,@pSzuletesiHely = @pSzuletesiHely
    ,@pOktatasiAzonosito = @pOktatasiAzonosito
    ,@pOsztalyCsoportId  = @pOsztalyCsoportId
    ,@pVegzosEvfolyam = @pVegzosEvfolyam
    ,@pSNI = @pSNI
    ,@pBTM = @pBTM
    ,@pFeladatellatasiHelyId = @pFeladatellatasiHelyId
    ,@pHatranyosHelyzetId = @pHatranyosHelyzetId
    ,@pEvfolyamTipusaId = @pEvfolyamTipusaId
    ,@pOsztalybaSoroltTantervNelkul = @pOsztalybaSoroltTantervNelkul
    ,@pAktiv = @pAktiv
    ,@pMuveszetiAgId = @pMuveszetiAgId
    ,@pNincsOsztondijAlapAdat = @pNincsOsztondijAlapAdat
    ,@pNincsOsztondijUtalasAdat = @pNincsOsztondijUtalasAdat
    ,@pAnyjaNeve = @pAnyjaNeve
    ,@pTanterviJellegId = @pTanterviJellegId

END
GO