DROP PROCEDURE IF EXISTS [dev].[uspGenerateUtalasiAllomany]
GO

CREATE PROCEDURE [dev].[uspGenerateUtalasiAllomany]
  @pLekerdezesDatuma datetime = NULL
  ,@pTanevNev nvarchar(10)
  ,@pGeneraltHonap int = NULL
  ,@pOsszevonas nvarchar(100) = NULL
  ,@pVisszaallitasDatuma datetime = NULL
AS
BEGIN

  DECLARE @tanevId nvarchar(100)
  CREATE TABLE #Osszevonasok (Id int primary key)

  INSERT INTO #Osszevonasok (Id)
    SELECT VALUE FROM string_split(@pOsszevonas,',')

    IF @pLekerdezesDatuma IS NOT NULL BEGIN​
    DECLARE cur CURSOR LOCAL FOR
    SELECT ia.C_TANEVID
    FROM T_INTEZMENY i
      INNER JOIN T_INTEZMENYADATOK ia ON i.ID = ia.C_INTEZMENYID
      INNER JOIN T_TANEV tv ON tv.ID = ia.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
    WHERE i.TOROLT = 'F'
      AND ia.C_ISSZAKKEPZOJUTTATAS = 'T'
      AND i.C_ISARCHIV = 'F'
    ​
    OPEN cur
    FETCH NEXT FROM cur INTO @tanevId
    WHILE @@FETCH_STATUS = 0 BEGIN
      EXEC uspUpdateTanuloSzakkepzesiJuttatasok
        @pTanevId = @tanevId
        ,@pMaxIgazolatlanokSzama = 6
        ,@pMinOsztondijAtlag = 2.0
        ,@pOsztondijAlap = 100000
        ,@pLekerdezesDatuma = @pLekerdezesDatuma
        ,@pTanuloId = NULL
        ,@pTanuloIdList = NULL
        ,@pFelhasznaloId = 0
        ,@pGeneraltHonap = @pGeneraltHonap
        ,@pVisszaallitasDatuma = @pVisszaallitasDatuma
        ,@pJuttatasTipusId = 7888

      FETCH NEXT FROM cur INTO @tanevId
    END
    CLOSE cur
    DEALLOCATE cur
    END

DECLARE @sql nvarchar(max) = N'';

SET @sql = N'
    SELECT
        i.C_AZONOSITO as ''KRÉTA Azonosító''
        ,ia.C_NEV as ''Intézmény neve''
        ,ia.C_OMKOD as ''OM kód''
        ,fn.C_AZONOSITO as ''Fenntartó''
        ,ISNULL(fn.C_NEV, ''Ismeretlen fenntartó'') AS ''Fenntartónév''
        ,ISNULL(lmszf.C_NEV, ''Ismeretlen legmagasabb szintű fenntartó'') AS ''Legmagasabb szintű fenntartó''
        ,f.C_OKTATASIAZONOSITO as ''Oktatási azonosító''
        ,f.C_NYOMTATASINEV as ''Diák neve''
        ,f.C_SZULETESIDATUM as ''Születési dátum''
        ,f.C_SZULETESIHELY as ''Születési hely''
        ,f.C_ANYJANEVE as ''Anyja neve''
        ,tel.C_TELEFONSZAM as ''Telefonszám''
        ,em.C_EMAILCIM as ''E-mail cím''
        ,x.C_NEV as ''Gondviselő neve''
        ,x.C_TELEFONSZAM as ''Telefonszám''
        ,x.C_EMAILCIM as ''E-mail cím''
        ,x2.C_NEV as ''Gondviselő neve 2''
        ,x2.C_TELEFONSZAM as ''Telefonszám 2''
        ,x2.C_EMAILCIM as ''E-mail cím 2''
        ,cim.C_CIM AS ''Cím''
        ,CONCAT(IIF(ISDATE(CONCAT(''1900/'',
          CASE WHEN evtip.C_ISOSSZEVONT = ''T''
            THEN IIF(dib3.C_NAME IS NOT NULL, CONCAT(dib3.C_NAME, '' ('', dib.C_NAME, '')''), dib.C_NAME)
            ELSE dib.C_NAME
          END)) = 1, '''''''', ''''),
          CASE WHEN evtip.C_ISOSSZEVONT = ''T''
            THEN IIF(dib3.C_NAME IS NOT NULL, CONCAT(dib3.C_NAME, '' ('', dib.C_NAME, '')''), dib.C_NAME)
            ELSE dib.C_NAME
          END) as ''Évfolyam''
        ,COALESCE(CAST(tt.C_SZAMITOTTATLAG as nvarchar), '''') as ''Tanulmányi átlag (számított)''
        ,COALESCE(CAST(tt.C_KEZIATLAG as nvarchar), '''') as ''Tanulmányi átlag (kézi)''
        ,COALESCE(CAST(tt.C_KEZIATLAG as nvarchar), CAST(tt.C_SZAMITOTTATLAG as nvarchar), '''') as ''Tanulmányi átlag (alkalmazott)''
        ,dibAgazat.C_NAME as ''Ágazat''
        ,dibSzakma.C_NAME as ''Szakma''
        ,CONCAT(mukodHely.C_NEV, '' - '', dibFeladatTipus.C_NAME) as ''Tanuló tanulási helyszíne, feladatellátási hely''
        ,CONCAT(mukodHely.C_IRANYITOSZAM, '' '', mukodHely.C_VAROS, '', '', mukodHely.C_KOZTERULETNEV, '' '', mukodHely.C_KOZTERULETJELLEGENEV, '' '', mukodHely.C_HAZSZAM, '', '' + mukodHely.C_EMELET + ''. emelet'', '' '' + mukodHely.C_AJTO + ''. ajtó'') as ''Működési hely címe''
        ,mukodHely.C_VAROS as ''Működési hely városa''
        ,COALESCE(CONVERT(nvarchar, tta.C_NYILVANTARTASKEZDETE, 23), '''') as ''Jogviszony kezdete dátum''
        ,COALESCE(CONVERT(nvarchar, tta.C_JOGVISZONYVARHATOBEFEJEZESE, 23), CAST(tt.C_JOGVISZONYVARHATOBEFEJEZESE as nvarchar), '''') as ''Jogviszony várható befejezésének dátuma''
        ,COALESCE(CONVERT(nvarchar, tcs.C_KILEPESDATUM, 23), '''') as ''Jogviszony befejezésének dátuma''
        ,IIF(ISNULL(tta.C_ISBESZAMITASOS, tt.C_ISBESZAMITASOS) = ''T'', ''Igen'', ''Nem'') as ''Beszámításos a tanuló''
        ,IIF(ISNULL(tta.C_ISBESZAMITASOSFIXSZAZALEK, tt.C_ISBESZAMITASOSFIXSZAZALEK) = ''T'', ''Igen'', ''Nem'') as ''Beszámításos fix százalék''
        ,IIF(tt.C_ISSIKERESAGAZATIVIZSGA = ''T'', ''Igen'', ''Nem'') as ''Sikeres ágazati alapvizsgát tett''
        ,IIF(tt.C_ISATLAGSZAKKEPZESI = ''T'', ''Igen'', ''Nem'') as ''Átlag szakképzésből''
        ,IIF(ISNULL(tta.C_TANULOSZERZODESES,tt.C_TANULOSZERZODESES) = ''T'', ''Igen'', ''Nem'') as ''Tanulói szerződéses''
        ,IIF(ISNULL(tta.C_EGYUTTMUKODESES,tt.C_EGYUTTMUKODESES) = ''T'', ''Igen'', ''Nem'') as ''Együttműködési megállapodásos''
        ,IIF(ISNULL(tta.C_ISSZAKKEPZESIMUNKASZERZODESS,tt.C_ISSZAKKEPZESIMUNKASZERZODESS) = ''T'', ''Igen'', ''Nem'') as ''Szakképzési munkaszerződéssel''
        ,IIF(ocs.C_VEGZOSEVFOLYAM = ''T'', ''Igen'', ''Nem'') as ''Végzős''
        ,j.C_OSSZEG as ''Ösztöndíj összege''
        ,'''''''' + tt.C_BANKSZAMLASZAM as ''Számlaszáma''
        ,tt.C_BANKSZAMLATULAJNEVE as ''Számla tulajdonosának neve''
        ,dib2.c_name as ''Számla típusa''
    FROM t_intezmeny i
        INNER JOIN T_TANEV t on t.C_INTEZMENYID = i.id and t.C_AKTIV = ''T'' and t.C_NEV = '''+@pTanevNev+''' and t.TOROLT = ''F''
        INNER JOIN T_INTEZMENYADATOK ia on ia.C_TANEVID = t.ID and ia.TOROLT = ''F''';

  IF (@pOsszevonas is not null)
      SET @sql += N'
        INNER JOIN (
          SELECT
             SUM(IIF(bj.C_ISJOGOSULT = ''T'',  bj.C_OSSZEG, 0)) AS C_OSSZEG
            ,bj.C_TANULOID AS C_TANULOID
            ,IIF(AVG(IIF(bj.C_ISJOGOSULT = ''T'',  1.0, 0.0)) = 0, ''F'', ''T'') AS C_ISJOGOSULT
            ,bj.C_TANEVId AS C_TANEVId
          FROM T_JUTTATAS bj
            JOIN #Osszevonasok ov ON ov.Id = bj.C_GENERALTHONAP
          WHERE bj.TOROLT = ''F''
            AND bj.C_JUTTATASTIPUSID = 7888
          GROUP BY bj.C_TANULOID, bj.C_TANEVId
        ) j ON j.C_TANEVId = t.id
    ';
  ELSE
    SET @sql += N'
        INNER JOIN T_JUTTATAS j on j.C_TANEVId = t.id and j.C_ISAKTIV = ''T'' and j.TOROLT = ''F'' and j.C_JUTTATASTIPUSID = 7888
    ';

SET @sql += N'
    INNER JOIN T_FELHASZNALO f on f.id = j.C_TANULOID and f.torolt  =''F''
        LEFT JOIN T_TANULOCSOPORT tcs on tcs.C_TANULOID = f.id and tcs.torolt =''F'' and tcs.C_TANEVID = t.ID
        LEFT JOIN T_FENNTARTO fn ON fn.TOROLT = ''F'' AND fn.ID = i.C_FENNTARTOID
        LEFT JOIN T_LEGMAGASABBSZINTUFENNTARTO lmszf ON lmszf.TOROLT = ''F'' AND fn.C_LEGMAGASABBSZINTUFENNTARTOID = lmszf.Id
        LEFT JOIN (T_TANULOCSOPORT tcs2
          INNER JOIN T_OSZTALYCSOPORT ocs2 on ocs2.id = tcs2.C_OSZTALYCSOPORTID and ocs2.torolt = ''F'' and ocs2.C_FELADATKATEGORIAID = 7553
          INNER JOIN T_OSZTALY o2 ON tcs2.C_OSZTALYCSOPORTID = o2.ID
        ) on tcs2.C_TANULOID = f.ID
            AND (tcs.C_KILEPESDATUM < tcs2.C_KILEPESDATUM OR (tcs.C_KILEPESDATUM IS NOT NULL AND tcs2.C_KILEPESDATUM IS NULL))
            AND tcs2.TOROLT = ''F'' AND tcs2.C_TANEVID = t.ID
        INNER JOIN t_osztaly o on o.id = tcs.C_OSZTALYCSOPORTID and o.torolt = ''F'' and o.C_ALTANEVID = t.ID
        INNER JOIN T_OSZTALYCSOPORT ocs on ocs.id = o.id and ocs.C_TANEVID = t.id and ocs.torolt = ''F''
            AND ocs.C_FELADATKATEGORIAID = 7553
        INNER JOIN T_DICTIONARYITEMBASE dib on dib.id = ocs.C_EVFOLYAMTIPUSA and dib.C_TANEVID = t.id
        INNER JOIN t_tanulo tt on tt.id = f.id and tt.torolt = ''F'' and tt.C_ALTANEVID = t.ID AND tt.C_ISOSZTONDIJBOLKIZARVA = ''F''
        LEFT JOIN T_DICTIONARYITEMBASE dib2 on dib2.id = tt.C_BANKSZAMLATULAJDONOSTIPUSID and dib2.C_TANEVID = t.id
        LEFT JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID and tta.TOROLT = ''F''
        LEFT JOIN T_DICTIONARYITEMBASE dibAgazat on dibAgazat.id = tta.C_AGAZATUJSZKTTIPUSID and dibAgazat.C_TANEVID = t.id and dibAgazat.TOROLT = ''F''
        LEFT JOIN T_DICTIONARYITEMBASE dibSzakma on dibSzakma.id = tta.C_SZAKMATIPUSID and dibSzakma.C_TANEVID = t.id and dibSzakma.TOROLT = ''F''
        INNER JOIN T_FELADATELLATASIHELY felhely on felhely.ID = ocs.C_FELADATELLATASIHELYID and felhely.TOROLT = ''F''
        INNER JOIN T_MUKODESIHELY_OSSZES mukodHely ON mukodHely.ID = felhely.C_MUKODESIHELYID and mukodHely.TOROLT = ''F''
        INNER JOIN T_DICTIONARYITEMBASE dibFeladatTipus on dibFeladatTipus.id = felhely.C_OKTATASINEVELESIFELADATTIPUS and dibFeladatTipus.C_TANEVID = t.id and dibFeladatTipus.TOROLT = ''F''
        LEFT JOIN T_DICTIONARYITEMBASE dib3 on dib3.id = tta.C_TANULOEVFOLYAMTIPUSID and dib3.C_TANEVID = t.id and dib3.TOROLT = ''F''
        INNER JOIN T_EVFOLYAMTIPUS_OSSZES evtip ON evtip.ID = ocs.C_EVFOLYAMTIPUSA AND evtip.TOROLT = ''F'' AND evtip.C_ALTANEVID = ocs.C_TANEVID
        ';

SET @sql += N'
    OUTER APPLY (
            SELECT TOP(1) C_TELEFONSZAM
            FROM T_TELEFON x
            WHERE x.C_FELHASZNALOID = tt.ID
                and x.C_ALAPERTELMEZETT = ''T''
                and x.TOROLT = ''F''
                and x.C_GONDVISELOID is null) tel
        OUTER APPLY (
            SELECT TOP(1) C_EMAILCIM
            FROM T_EMAIL x
            WHERE x.C_FELHASZNALOID = tt.ID
                and x.C_ALAPERTELMEZETT = ''T''
                and x.TOROLT = ''F''
                and x.C_GONDVISELOID is null) em
        OUTER APPLY (
            SELECT TOP(1) * FROM  T_GONDVISELO g
                OUTER APPLY (
                    SELECT TOP(1) C_EMAILCIM
                    FROM T_EMAIL x
                    WHERE x.C_FELHASZNALOID = tt.ID
                        and x.C_ALAPERTELMEZETT = ''T''
                        and x.TOROLT = ''F''
                        and x.C_GONDVISELOID = g.ID
                        and x.C_TANEVID = t.ID) em2
                OUTER APPLY (
                    SELECT TOP(1) C_TELEFONSZAM
                    FROM T_TELEFON x
                    WHERE x.C_FELHASZNALOID = tt.ID
                        and x.C_ALAPERTELMEZETT = ''T''
                        and x.TOROLT = ''F''
                        and x.C_GONDVISELOID = g.ID
                        and x.C_TANEVID = t.ID) tel2
            WHERE g.TOROLT = ''F''
                AND g.C_TANULOID = tt.ID and g.C_TANEVID = t.ID
            ORDER BY g.CREATED DESC  ) x';

  SET @sql += N'
        OUTER APPLY (
            SELECT TOP(1) *FROM  T_GONDVISELO g
                OUTER APPLY (
                    SELECT TOP(1) C_EMAILCIM
                    FROM T_EMAIL x
                    WHERE x.C_FELHASZNALOID = tt.ID
                        and x.C_ALAPERTELMEZETT = ''T''
                        and x.TOROLT = ''F''
                        and x.C_GONDVISELOID = g.ID
                        and x.C_TANEVID = t.ID) em2
                OUTER APPLY (
                    SELECT TOP(1) C_TELEFONSZAM
                    FROM T_TELEFON x
                    WHERE x.C_FELHASZNALOID = tt.ID
                        and x.C_ALAPERTELMEZETT = ''T''
                        and x.TOROLT = ''F''
                        and x.C_GONDVISELOID = g.ID
                        and x.C_TANEVID = t.ID) tel2
            WHERE g.TOROLT = ''F''
                AND g.C_TANULOID = tt.ID and g.C_TANEVID = t.ID
            ORDER BY g.CREATED DESC
        ) x2
    OUTER APPLY (
        SELECT TOP(1) CONCAT(x.C_IRANYITOSZAM, '' '', x.C_VAROS, '', '', x.C_KOZTERULET, '' '', x.C_KOZTERULETJELLEGENEV, '' '', x.C_HAZSZAM, '', '' + x.C_EMELET + ''. emelet'', '' '' + x.C_AJTO + ''. ajtó'') AS C_CIM
        FROM T_CIM x
        WHERE x.C_FELHASZNALOID = tt.ID
            and x.C_ALAPERTELMEZETT = ''T''
            and x.TOROLT = ''F''
            and x.C_GONDVISELOID IS NULL
            and x.C_TANEVID = t.ID
         ORDER BY x.C_ALAPERTELMEZETT DESC) cim
    WHERE i.torolt = ''F''
      AND fn.C_ISTESZTFENNTARTO = ''F''
      AND j.C_ISJOGOSULT = ''T''
      AND tcs2.ID IS NULL
      AND ia.C_ISSZAKKEPZOJUTTATAS = ''T''
      AND i.C_ISARCHIV = ''F''

    ORDER BY i.C_AZONOSITO, f.C_NYOMTATASINEV';

  exec sp_executesql @sql

END
GO