using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Kreta.Core;
using Kreta.Core.Logic;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.ParameterClasses;
using Kreta.DataAccessManual.Util;
using Kreta.Enums.ManualEnums;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

namespace Kreta.DataAccessManual
{
    internal class FogadooraDAL : DataAccessBase, IFogadooraDal
    {

        public FogadooraDAL(DalHandler handler, GridParameters parameters)
            : base(handler, parameters)
        {
        }

        public FogadooraDAL(DalHandler handler) : base(handler)
        {
        }

        /// INFO @DevKornel: Mobil használja
        public DataSet GetFogadoora(int intezemenyId, int tanevId, FogadooraSearchPco searchPco)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pIntezmenyId", intezemenyId),
                new CommandParameter("pTodayDate", DateTime.Now) //.AddDays(4)) //!!!
            };

            var commandText = new StringBuilder(@"
                 SELECT DISTINCT
                    fogadoora.ID AS ID
                   ,STUFF(
                      (SELECT
                        ', ' + C_NEV
                        FROM T_FOGADOORA_OSZTALYCSOPORT focs
                        INNER JOIN T_OSZTALYCSOPORT ocs on ocs.ID = focs.C_OSZTALYCSOPORTID
                            AND ocs.TOROLT = 'F'
                        WHERE
                        focs.C_FOGADOORAID = fogadoora.ID
                        FOR XML PATH ('')), 1, 1, '') AS OsztalyCsoport
                   ,terem.C_NEV AS Terem
                   ,terem.ID AS TeremId
                   ,FORMAT(fogadoora.C_FOGADOORAKEZDETE, 'yyyy.MM.dd HH:mm') AS FogadooraKezdete
                   ,FORMAT(fogadoora.C_FOGADOORAVEGE, 'yyyy.MM.dd HH:mm') AS FogadooraVege
                   ,fogadoora.C_RENDSZERESSEG AS RendszeressegId
                   ,fogadoora.C_FOGADOORATIPUSID AS JelentkezesekKezeleseId
                   ,felhasznalo.C_NYOMTATASINEV AS Pedagogus
                   ,felhasznalo.ID AS PedagogusId
                   ,IIF(fogadoora.C_FOGADOORAKEZDETE > :pTodayDate, 'T', 'F' ) AS isTorolhetoModosithato
                   ,JelentkezesekSzama.Count AS  JelentkezesekSzama
                 FROM T_FOGADOORA fogadoora
                   INNER JOIN T_TEREM terem on terem.ID = fogadoora.C_TEREMID
                     AND terem.TOROLT = 'F'
                   INNER JOIN T_FELHASZNALO felhasznalo ON felhasznalo.ID = fogadoora.C_TANARID
                     AND felhasznalo.TOROLT = 'F'
                   INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoport ON fogadooraOsztalyCsoport.C_FOGADOORAID = fogadoora.ID
                   INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = fogadooraOsztalyCsoport.C_OSZTALYCSOPORTID
                   OUTER APPLY (
                     SELECT 
                       IIF(f.C_FOGADOORATIPUSID = 0, '-', CAST(COUNT(fig.ID) AS nvarchar(3))) AS Count
                     FROM T_FOGADOORA_OSSZES f
                       LEFT JOIN T_FOGADOORAIDOPONT_OSSZES fi ON fi.C_FOGADOORAID = f.ID AND fi.TOROLT = 'F'
                       LEFT JOIN T_FOGADOORAIDOPONT_GONDVISELO_OSSZES fig ON fig.C_FOGADOORAIDOPONTID = fi.ID AND fig.TOROLT = 'F'
                     WHERE f.ID = fogadoora.ID
                     GROUP BY f.C_FOGADOORATIPUSID
                   ) AS JelentkezesekSzama
                 WHERE fogadoora.TOROLT = 'F'
                   AND fogadoora.C_INTEZMENYID =:pIntezmenyId
                   AND fogadoora.C_TANEVID = :pTanevId");

            if (searchPco.FeladatKategoriaId.IsEntityId())
            {
                paramlist.Add(new CommandParameter("pFeladatKategoriaId", searchPco.FeladatKategoriaId));
                commandText.Append($@" AND ocs.C_FELADATKATEGORIAID = :pFeladatKategoriaId ");
            }

            if (searchPco.FeladatEllatasiHelyId.HasValue)
            {
                paramlist.Add(new CommandParameter("pFeladatEllatasiHelyId", searchPco.FeladatEllatasiHelyId));
                commandText.Append($@" AND ocs.C_FELADATELLATASIHELYID = :pFeladatEllatasiHelyId ");
            }

            if (searchPco.OsztalyCsoportIdsSrc != null)
            {
                commandText.Append($@" AND fogadooraOsztalyCsoport.C_OSZTALYCSOPORTID IN ({SqlLogic.ParseListToParameter(searchPco.OsztalyCsoportIdsSrc)})");
            }

            if (searchPco.TanarIdSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pTanarId", searchPco.TanarIdSrc));
                commandText.Append(@" AND fogadoora.C_TANARID = :pTanarId");
            }

            if (searchPco.TeremIdSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pTeremId", searchPco.TeremIdSrc));
                commandText.Append(@" AND fogadoora.C_TEREMID = :pTeremId");
            }

            if (searchPco.IdopontSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pIdopont", searchPco.IdopontSrc));
                commandText.Append(@" AND CAST(:pIdopont AS DATE) BETWEEN CAST(fogadoora.C_FOGADOORAKEZDETE AS DATE) AND CAST(fogadoora.C_FOGADOORAVEGE AS DATE)");
            }

            if (searchPco.RendszeressegIdSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pRendszeresseg", searchPco.RendszeressegIdSrc));
                commandText.Append(@" AND fogadoora.C_RENDSZERESSEG = :pRendszeresseg");
            }

            if (searchPco.TipusIdSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pTipus", searchPco.TipusIdSrc));
                commandText.Append(@" AND fogadoora.C_FOGADOORATIPUSID = :pTipus");
            }

            if (searchPco.IdopontTolSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pIdopontTol", searchPco.IdopontTolSrc.Value));
                commandText.Append(@" AND CAST(fogadoora.C_FOGADOORAKEZDETE AS DATE) >= CAST(:pIdopontTol AS DATE)");
            }

            if (searchPco.IdopontIgSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pIdopontIg", searchPco.IdopontIgSrc.Value));
                commandText.Append(@" AND CAST(fogadoora.C_FOGADOORAVEGE AS DATE) <= CAST(:pIdopontIg AS DATE)");
            }

            if (searchPco.IdointervallumTolSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pStartTime", searchPco.IdointervallumTolSrc.Value.TimeOfDay));
                commandText.Append(@" AND CAST(fogadoora.C_FOGADOORAKEZDETE AS TIME) >= :pStartTime");
            }

            if (searchPco.IdointervallumIgSrc.HasValue)
            {
                paramlist.Add(new CommandParameter("pEndTime", searchPco.IdointervallumIgSrc.Value.TimeOfDay));
                commandText.Append(" AND CAST(fogadoora.C_FOGADOORAVEGE AS TIME) <= :pEndTime");
            }

            var ds = this.GetData(commandText.ToString(), paramlist);

            SetBoolFields(ds.Tables[0], "isTorolhetoModosithato");
            return ds;
        }

        public DataSet GetFogadooraById(int fogadooraId, int gondviseloId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraId", fogadooraId),
                new CommandParameter("pGondviseloId", gondviseloId)
            };

            var commandText = new StringBuilder(@"
                  SELECT f.ID AS FogadooraId
                        ,f.C_FOGADOORATIPUSID AS FogadooraTipusId
                        ,f.C_FOGADOORAKEZDETE AS FogadooraKezdete
                        ,f.C_FOGADOORAVEGE AS FogadooraVege
                        ,fio.ID AS IdopontId
                        ,fio.C_KEZDETE AS FogadooraIdopontKezdete
                        ,fio.C_VEGE AS FogadooraIdopontVege
                        ,t.ID AS TeremId
                        ,t.C_NEV AS TeremNev
                        ,CASE WHEN foigi.C_GONDVISELOID = :pGondviseloId THEN 1 ELSE 0 END AS IsJelentkeztem
                        ,foigi.C_GONDVISELOID AS FogadooraIdopontGondviseloId
                    FROM T_FOGADOORA f
                        INNER JOIN T_FOGADOORAIDOPONT fio ON fio.C_FOGADOORAID = f.ID
                        INNER JOIN T_TEREM t ON t.ID = f.C_TEREMID
                        LEFT JOIN T_FOGADOORAIDOPONT_GONDVISELO foigi ON foigi.C_FOGADOORAIDOPONTID = fio.ID
                    WHERE f.ID = :pFogadooraId
            ");

            return GetData(commandText.ToString(), paramlist);
        }

        /// INFO: Mobil használja
        public DataSet GetTanarFogadoorakByGondviseloId(int gondviseloId, int? fogadoOraId = null, DateTime? datumTol = null, DateTime? datumIg = null)
        {
            var commandText = new StringBuilder($@"
                SELECT DISTINCT fo.ID FogadooraId
                    ,t.ID TeremId
                    ,t.C_NEV TeremNev
                    ,fo.C_FOGADOORATIPUSID FogadooraTipusId
                    ,fo.C_FOGADOORAKEZDETE FogadooraKezdete
                    ,fo.C_FOGADOORAVEGE FogadooraVege
                    ,fo.C_JELENTKEZESHATARIDO AS JelentkezesHatarido 
                    ,f.C_NYOMTATASINEV TanarNev
                    ,f.ID TanarId
                    ,foi.ID FogadooraIdopontId
                    ,foi.C_KEZDETE FogadooraIdopontKezdete
                    ,foi.C_VEGE FogadooraIdopontVege
                    ,CASE WHEN foig.C_GONDVISELOID IS NULL THEN 'T' ELSE 'F' END IsSzabadIdopont
                    ,CASE WHEN foig.C_GONDVISELOID = g.ID THEN 'T' ELSE 'F' END IsJelentkeztem
                FROM T_GONDVISELO g
                    INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = g.C_TANULOID
                    INNER JOIN T_FOGADOORA_OSZTALYCSOPORT foocs ON foocs.C_OSZTALYCSOPORTID = tcs.C_OSZTALYCSOPORTID
                    INNER JOIN T_FOGADOORA fo ON fo.ID = foocs.C_FOGADOORAID
                    LEFT JOIN T_FOGADOORAIDOPONT foi ON foi.C_FOGADOORAID = fo.ID
                    INNER JOIN T_TEREM t ON t.ID = fo.C_TEREMID
                    LEFT JOIN T_FOGADOORAIDOPONT_GONDVISELO foig ON foig.C_FOGADOORAIDOPONTID = foi.ID
                        AND ((fo.C_FOGADOORATIPUSID = {(int)FogadooraTipusEnum.Jelentkezheto} AND foig.C_GONDVISELOID = g.ID) OR (fo.C_FOGADOORATIPUSID = {(int)FogadooraTipusEnum.JelentkezhetoDarabolt}))
                    LEFT JOIN T_FELHASZNALO f ON f.ID = fo.C_TANARID
                WHERE g.ID = {gondviseloId}
                    AND (fo.C_FOGADOORAKEZDETE >= tcs.C_BELEPESDATUM
                        AND (tcs.C_KILEPESDATUM IS NULL OR fo.C_FOGADOORAKEZDETE <= tcs.C_KILEPESDATUM))");

            if (fogadoOraId.HasValue)
            {
                commandText.Append($" AND fo.ID = {fogadoOraId}");
            }
            if (datumTol.HasValue)
            {
                commandText.Append($" AND (CAST(fo.C_FOGADOORAKEZDETE AS DATETIME) >= CAST('{datumTol.Value:yyyy-MM-ddTHH:mm:ss}' AS DATETIME))" +
                    $" OR (CAST(fo.C_FOGADOORAKEZDETE AS DATETIME) <= CAST('{datumTol.Value:yyyy-MM-ddTHH:mm:ss}' AS DATETIME) AND CAST(fo.C_FOGADOORAVEGE AS DATETIME) >= CAST('{datumTol.Value:yyyy-MM-ddTHH:mm:ss}' AS DATETIME))");
            }
            if (datumIg.HasValue)
            {
                commandText.Append($" AND CAST(fo.C_FOGADOORAVEGE AS DATE) <= CAST('{datumIg.Value:yyyy-MM-dd}' AS DATE)");
            }

            return GetData(commandText.ToString());
        }

        public DataSet GetFogadooraCalendar(int intezemenyId, int tanevId, DateTime? start, DateTime end, int? tanarId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pIntezmenyId", intezemenyId),
                new CommandParameter("pStart", start),
                new CommandParameter("pEnd", end),
                new CommandParameter("pTanarId", tanarId)
            };

            var commandText = new StringBuilder(@"
                 SELECT DISTINCT
                     fogadoora.ID                        AS ID
                    ,STUFF(
                        (SELECT ', ' + C_NEV FROM T_FOGADOORA_OSZTALYCSOPORT focs
                            INNER JOIN T_OSZTALYCSOPORT ocs on ocs.ID = focs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
                        WHERE
                                focs.C_FOGADOORAID = fogadoora.ID
                        FOR XML PATH ('')), 1, 1, ''
                    )                              AS OsztalyCsoport
                    ,terem.C_NEV                        AS Terem
                    ,FORMAT(fogadoora.C_FOGADOORAKEZDETE, 'yyyy.MM.dd HH:mm')  AS FogadooraKezdete
                    ,FORMAT(fogadoora.C_FOGADOORAVEGE, 'yyyy.MM.dd HH:mm')    AS FogadooraVege
                    ,fogadoora.C_RENDSZERESSEG                  AS RendszeressegId
                    ,fogadoora.C_FOGADOORATIPUSID                AS JelentkezesekKezeleseId
                    ,felhasznalo.C_NYOMTATASINEV                AS Pedagogus
                FROM T_FOGADOORA fogadoora
                    INNER JOIN T_TEREM terem on terem.ID = fogadoora.C_TEREMID AND terem.TOROLT = 'F'
                    INNER JOIN T_FELHASZNALO felhasznalo ON felhasznalo.ID = fogadoora.C_TANARID AND felhasznalo.TOROLT = 'F'
                    INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoport ON fogadooraOsztalyCsoport.C_FOGADOORAID = fogadoora.ID
                WHERE
                    fogadoora.TOROLT = 'F' AND fogadoora.C_INTEZMENYID =:pIntezmenyId AND fogadoora.C_TANEVID = :pTanevId
                    AND fogadoora.C_TANARID = :pTanarId
                    AND CAST(fogadoora.C_FOGADOORAKEZDETE AS DATE) BETWEEN CAST(:pStart as DATE) AND CAST(:pEnd as DATE)
                    AND CAST(fogadoora.C_FOGADOORAVEGE AS DATE) BETWEEN CAST(:pStart as DATE) AND CAST(:pEnd as DATE)
            ");

            var ds = this.GetData(commandText.ToString(), paramlist);
            return ds;
        }

        public DataSet GetFogadooraForGondviseloCalendar(int intezemenyId, int tanevId, DateTime? start, DateTime end, int? tanuloId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pStart", start),
                new CommandParameter("pEnd", end),
                new CommandParameter("pTanuloId", tanuloId)
            };

            var commandText = new StringBuilder(@"
            SELECT DISTINCT
                fogadoora.ID                    AS Id
                ,STUFF(
                (SELECT ', ' + C_NEV FROM T_FOGADOORA_OSZTALYCSOPORT focs
                    INNER JOIN T_OSZTALYCSOPORT ocs on ocs.ID = focs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
                WHERE
                        focs.C_FOGADOORAID = fogadoora.ID
                FOR XML PATH ('')), 1, 1, ''
                )                          AS OsztalyCsoport
                ,fogadoora.C_FOGADOORAKEZDETE            AS FogadooraKezdete
                ,fogadoora.C_FOGADOORAVEGE              AS FogadooraVege
                ,felhasznalo.C_NYOMTATASINEV                        AS TanarNev
            FROM T_TANULOCSOPORT tanulocsoport
                INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalycsoport ON fogadooraOsztalycsoport.C_OSZTALYCSOPORTID = tanulocsoport.C_OSZTALYCSOPORTID AND fogadooraOsztalycsoport.TOROLT = 'F'
                INNER JOIN T_FOGADOORA fogadoora ON fogadoora.ID = fogadooraOsztalycsoport.C_FOGADOORAID AND fogadoora.TOROLT = 'F'
                INNER JOIN T_FELHASZNALO felhasznalo ON felhasznalo.Id = fogadoora.C_TANARID
            WHERE
                tanulocsoport.C_TANULOID = :pTanuloId
                AND fogadooraOsztalycsoport.TOROLT = 'F'
                AND tanulocsoport.TOROLT = 'F'
                AND (fogadoora.C_FOGADOORAKEZDETE >= tanulocsoport.C_BELEPESDATUM
                                                AND (tanulocsoport.C_KILEPESDATUM IS NULL OR fogadoora.C_FOGADOORAKEZDETE <= tanulocsoport.C_KILEPESDATUM))
                AND CAST(fogadoora.C_FOGADOORAKEZDETE AS DATE) BETWEEN CAST(:pStart as DATE) AND CAST(:pEnd as DATE)
                AND CAST(fogadoora.C_FOGADOORAVEGE AS DATE) BETWEEN CAST(:pStart as DATE) AND CAST(:pEnd as DATE)
            ");

            var ds = this.GetData(commandText.ToString(), paramlist);
            return ds;
        }

        public DataSet GetFogadooraOsztalyCsoportok(int tanevId, int tanarId, bool isSzuperOsztalyfonok)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pTanarId", tanarId)
            };

            var commandText = new StringBuilder(@"
                    SELECT DISTINCT foglalkozas.C_OSZTALYCSOPORTID OsztalyCsoportId, osztalycsoport.C_NEV OsztalyCsoportNev FROM T_FOGLALKOZAS foglalkozas
                        INNER JOIN T_OSZTALYCSOPORT osztalycsoport ON osztalycsoport.Id = foglalkozas.C_OSZTALYCSOPORTID
                    WHERE foglalkozas.C_TANARID = :pTanarId AND foglalkozas.TOROLT = 'F'
                    UNION
                    SELECT DISTINCT osztaly.Id OsztalyCsoportId, osztalycsoport.C_NEV OsztalyCsoportNev FROM T_OSZTALY osztaly
                           INNER JOIN T_OSZTALYCSOPORT osztalycsoport ON osztaly.Id = osztalycsoport.Id
                    WHERE (C_OSZTALYFONOKID = :pTanarId OR C_OFOHELYETTESID = :pTanarId) AND osztaly.TOROLT = 'F'
                    UNION
                    SELECT DISTINCT csoport.Id OsztalyCsoportId, osztalycsoport.C_NEV OsztalyCsoportNev FROM T_CSOPORT csoport
                        INNER JOIN T_OSZTALYCSOPORT osztalycsoport ON osztalycsoport.Id = csoport.Id
                    WHERE C_CSOPORTVEZETOID = :pTanarId AND csoport.TOROLT = 'F'");

            var commandTextForSzuperOsztalyfonok = new StringBuilder(@"
                    SELECT DISTINCT osztalycsoport.Id OsztalyCsoportId, osztalycsoport.C_NEV OsztalyCsoportNev FROM T_OSZTALYCSOPORT osztalycsoport
                    WHERE C_TANEVID = :pTanevId AND TOROLT = 'F'");

            var result = this.GetData(isSzuperOsztalyfonok ? commandTextForSzuperOsztalyfonok.ToString() : commandText.ToString(), paramlist);
            return result;
        }

        public void DeleteFogadoora(int id)
        {
            var oFogadoora = Get(id) as Fogadoora;

            oFogadoora.FogadooraIdopont.ToList().ForEach(f => { f.FogadooraIdopont_Gondviselo.DeleteAll(); });
            oFogadoora.FogadooraIdopont.DeleteAll();
            oFogadoora.Fogadoora_OsztalyCsoport.DeleteAll();
            if (oFogadoora.NemKotottMunkaidoId.IsEntityId())
            {
                var nkm = DalHelper.NemKotottMunkaido().GetNemKotottMunkaidoById(oFogadoora.NemKotottMunkaidoId);
                oFogadoora.NemKotottMunkaido = null;
                DalHelper.NemKotottMunkaido().Delete(nkm.ID);
            }

            oFogadoora.Delete();
            DalHelper.Commit();
        }

        public DataSet GetFogadooraNotifications()
        {
            var commandText = new StringBuilder(@"
                select
                    fog.ID as Id
                    ,f.ID as TanarId
                    ,f.C_NYOMTATASINEV as Tanar
                    ,t.C_NEV as Terem
                    ,C_FOGADOORAKEZDETE as Idopont
                    ,C_FOGADOORAVEGE as IdopontVege
                    ,DATEDIFF(DAY, CAST(GETDATE() as DATE), CAST(C_FOGADOORAKEZDETE as DATE)) as Nap
                    ,fog.C_TANEVID as TanevId
                from T_FOGADOORA_OSSZES fog
                join T_FELHASZNALO_OSSZES f on f.ID = fog.C_TANARID and f.TOROLT = 'F'
                join T_TEREM_OSSZES t on t.ID = fog.C_TEREMID and t.TOROLT = 'F'
                where
                    (DATEDIFF(DAY, CAST(GETDATE() as DATE), CAST(C_FOGADOORAKEZDETE as DATE)) = 1)
                    OR
                    (DATEDIFF(DAY, CAST(GETDATE() as DATE), CAST(C_FOGADOORAKEZDETE as DATE)) = 3)
                    AND fog.TOROLT = 'F'
            ");

            var ds = this.GetData(commandText.ToString());
            return ds;
        }

        public DataSet GetGondviselokByFogadooraId(int fogadooraId)
        {
            return GetGondviselokByFogadooraIdAndOsztalyCsoportId(fogadooraId, null);
        }

        public DataSet GetGondviselokByFogadooraIdAndOsztalyCsoportId(int fogadooraId, int? osztalyCsoportId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraId", fogadooraId),
            };

            var commandText = new StringBuilder(@"
             SELECT DISTINCT
                fogadoora.C_FOGADOORATIPUSID as FogadooraJelentkezesTipusa
                ,gondviselo.ID AS GondviseloId
                ,gondviselo.C_NEV AS GondviseloNev
                ,ISNULL(email.C_EMAILCIM, '') as GondviseloEmailCim
                ,email.C_GUID as GondviseloEmailGuid
                ,gondviselo.C_TANULOID AS TanuloId
                ,tanulo.C_NYOMTATASINEV TanuloNev
                ,osztalyCsop.C_NEV      OsztalyNev
                ,CAST(C_PROFILE AS XML).value('(/UserProfile/FogadooraEmailUj)[1]','bit') as FogadooraEmailUj
            FROM T_FOGADOORA fogadoora
                INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoort ON fogadooraOsztalyCsoort.C_FOGADOORAID = fogadoora.ID
                INNER JOIN T_TANULOCSOPORT tanuloCsoport ON tanuloCsoport.C_OSZTALYCSOPORTID = fogadooraOsztalyCsoort.C_OSZTALYCSOPORTID
                            AND (fogadoora.C_FOGADOORAKEZDETE >= tanulocsoport.C_BELEPESDATUM
                                    AND (tanulocsoport.C_KILEPESDATUM IS NULL OR fogadoora.C_FOGADOORAKEZDETE <= tanulocsoport.C_KILEPESDATUM))
                INNER JOIN T_OSZTALYCSOPORT osztalyCsop on tanuloCsoport.C_OSZTALYCSOPORTID = osztalyCsop.ID
                LEFT JOIN T_GONDVISELO gondviselo ON gondviselo.C_TANULOID = TanuloCsoport.C_TANULOID AND gondviselo.C_ISCSOKKENTETTGONDVISELO = 'F'
                LEFT JOIN T_FELHASZNALO tanulo ON gondviselo.C_TANULOID = tanulo.ID
                LEFT JOIN T_EMAIL email ON email.C_GONDVISELOID = gondviselo.ID AND email.TOROLT = 'F' AND email.C_ISHIBASANMEGADVA = 'F'
          LEFT JOIN T_USERPROFILE up on up.C_FELHASZNALOID = gondviselo.C_TANULOID AND up.TOROLT = 'F'
            WHERE
                fogadoora.ID = :pFogadooraId
                AND fogadooraOsztalyCsoort.TOROLT = 'F'
                AND tanuloCsoport.TOROLT = 'F'
                AND osztalyCsop.TOROLT = 'F'
                AND gondviselo.TOROLT = 'F'
                AND tanulo.TOROLT = 'F'
                AND fogadoora.TOROLT = 'F'");

            if (osztalyCsoportId.HasValue)
            {
                paramlist.Add(new CommandParameter("pOsztalyCsoportId", osztalyCsoportId));
                commandText.AppendLine(@"
                    AND fogadooraOsztalyCsoort.C_OSZTALYCSOPORTID = :pOsztalyCsoportId");
            }

            var result = this.GetData(commandText.ToString(), paramlist);
            return result;
        }

        public DataSet GetJelentkezettGondviselokByFogadooraId(int fogadooraId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraId", fogadooraId),
            };

            var commandText = new StringBuilder(@"
            SELECT DISTINCT
                 gondviselo.ID      GondviseloId
                ,gondviselo.C_NEV    GondviseloNev
                ,email.C_EMAILCIM    GondviseloEmailCim
            FROM T_FOGADOORA fogadoora
                INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoort ON fogadooraOsztalyCsoort.C_FOGADOORAID = fogadoora.ID
                INNER JOIN T_FOGADOORAIDOPONT fogadooraIopont ON fogadoora.ID = fogadooraIopont.C_FOGADOORAID
                INNER JOIN T_FOGADOORAIDOPONT_GONDVISELO fogadooraGondviselo ON fogadooraGondviselo.C_FOGADOORAIDOPONTID = fogadooraIopont.ID
                INNER JOIN T_GONDVISELO gondviselo ON gondviselo.ID = fogadooraGondviselo.C_GONDVISELOID
                INNER JOIN T_EMAIL email ON email.C_GONDVISELOID = gondviselo.ID AND email.C_ISHIBASANMEGADVA = 'F'
            WHERE
                fogadoora.ID = :pFogadooraId
                AND fogadooraOsztalyCsoort.TOROLT = 'F'
                AND fogadooraIopont.TOROLT = 'F'
                AND fogadooraGondviselo.TOROLT = 'F'
                AND gondviselo.TOROLT = 'F'
                AND email.TOROLT = 'F'
                AND fogadoora.TOROLT = 'F'");

            var result = this.GetData(commandText.ToString(), paramlist);
            return result;
        }

        public DataSet GetJelentkezettGondviseloCntByFogadooraId(int fogadooraId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraId", fogadooraId),
            };

            var commandText = new StringBuilder(@"
            SELECT
                fogadoora.ID
            FROM T_FOGADOORA fogadoora
                INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoort ON fogadooraOsztalyCsoort.C_FOGADOORAID = fogadoora.ID
                INNER JOIN T_FOGADOORAIDOPONT fogadooraIopont ON fogadoora.ID = fogadooraIopont.C_FOGADOORAID
                INNER JOIN T_FOGADOORAIDOPONT_GONDVISELO fogadooraGondviselo ON fogadooraGondviselo.C_FOGADOORAIDOPONTID = fogadooraIopont.ID
                INNER JOIN T_GONDVISELO gondviselo ON gondviselo.ID = fogadooraGondviselo.C_GONDVISELOID
            WHERE
                fogadoora.ID = :pFogadooraId
                AND fogadooraOsztalyCsoort.TOROLT = 'F'
                AND fogadooraIopont.TOROLT = 'F'
                AND fogadooraGondviselo.TOROLT = 'F'
                AND gondviselo.TOROLT = 'F'
                AND fogadoora.TOROLT = 'F'");

            var result = this.GetData(commandText.ToString(), paramlist);
            return result;
        }

        public DataSet GetGondviseloKozelgoFogadoora(int tanevId, int nap)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pNap", nap),
            };

            var commandText = new StringBuilder(@"
            SELECT DISTINCT
                 gondviselo.ID      GondviseloId
                ,gondviselo.C_NEV    GondviseloNev
                ,Email.C_EMAILCIM    GondviseloEmailCim
            FROM T_FOGADOORA fogadoora
                INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoort ON fogadooraOsztalyCsoort.C_FOGADOORAID = fogadoora.ID
                INNER JOIN T_FOGADOORAIDOPONT fogadooraIopont ON fogadoora.ID = fogadooraIopont.C_FOGADOORAID
                INNER JOIN T_FOGADOORAIDOPONT_GONDVISELO fogadooraGondviselo ON fogadooraGondviselo.C_FOGADOORAIDOPONTID = fogadooraIopont.ID
                INNER JOIN T_GONDVISELO gondviselo ON gondviselo.ID = fogadooraGondviselo.C_GONDVISELOID
                INNER JOIN T_EMAIL email ON email.C_GONDVISELOID = gondviselo.ID
            WHERE
                fogadooraOsztalyCsoort.TOROLT = 'F'
                AND fogadooraIopont.TOROLT = 'F'
                AND fogadooraGondviselo.TOROLT = 'F'
                AND gondviselo.TOROLT = 'F'
                AND email.TOROLT = 'F'
                AND fogadoora.TOROLT = 'F'
                AND fogadoora.C_TANEVID = :pTanevId
                AND DATEDIFF(DAY, GETDATE(), fogadoora.C_FOGADOORAKEZDETE) = :pNap");

            var result = this.GetData(commandText.ToString(), paramlist);
            return result;
        }

        /// INFO @DevKornel: Mobil használja
        public DataSet GetFogadooraIdopontok(List<int> fogadooraIds, int gondviseloId, FogadooraTipusEnum? tipus = null)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pGondviseloId", gondviseloId)
            };
            if (tipus != null)
            {
                paramlist.Add(new CommandParameter("pJelentkezhetoId", (int)tipus));
            }

            var commandText = new StringBuilder($@"
            SELECT DISTINCT
               fogadooraIdopont.ID AS Value
              ,FORMAT(fogadooraIdopont.C_KEZDETE, 'HH:mm') + ' - ' + FORMAT(fogadooraIdopont.C_VEGE, 'HH:mm') AS Text
              ,fogadooraIdopont.C_KEZDETE AS KezdoIdopont
              ,fogadooraIdopont.C_VEGE AS VegIdopont
              ,CASE WHEN fogadooraGondviselo.ID IS NULL THEN 'F' ELSE 'T' END AS IsJelentkeztem
              ,fogadooraIdopont.C_FOGADOORAID AS FogadooraId
            FROM T_GONDVISELO gondviselo
              INNER JOIN T_TANULOCSOPORT tanulocsoport ON tanulocsoport.C_TANULOID = gondviselo.C_TANULOID
              INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoport ON fogadooraOsztalyCsoport.C_OSZTALYCSOPORTID = tanulocsoport.C_OSZTALYCSOPORTID
              INNER JOIN T_FOGADOORA fogadoora ON fogadoora.ID = fogadooraOsztalyCsoport.C_FOGADOORAID
              INNER JOIN T_FOGADOORAIDOPONT fogadooraIdopont ON fogadooraIdopont.C_FOGADOORAID = fogadoora.ID
              LEFT JOIN T_FOGADOORAIDOPONT_GONDVISELO fogadooraGondviselo ON fogadooraGondviselo.C_FOGADOORAIDOPONTID = fogadooraIdopont.ID
                AND fogadooraGondviselo.TOROLT = 'F'
            WHERE gondviselo.ID = :pGondviseloId
              AND fogadoora.ID IN ({SqlLogic.ParseListToParameter(fogadooraIds)})
              " + (tipus != null ? @" AND (fogadooraGondviselo.ID IS NULL OR fogadoora.C_FOGADOORATIPUSID = :pJelentkezhetoId)" : "") + @"
              AND tanulocsoport.TOROLT = 'F'
              AND fogadooraOsztalyCsoport.TOROLT = 'F'
              AND fogadooraIdopont.TOROLT = 'F'
              AND (fogadoora.C_FOGADOORAKEZDETE >= tanulocsoport.C_BELEPESDATUM
                AND (tanulocsoport.C_KILEPESDATUM IS NULL OR fogadoora.C_FOGADOORAKEZDETE <= tanulocsoport.C_KILEPESDATUM))");

            var result = this.GetData(commandText.ToString(), paramlist);
            return result;
        }

        public int Insert(IFogadoora dto)
        {
            var entity = dto as Fogadoora;
            entity.Insert();
            DalHelper.Commit();
            return entity.ID;
        }

        public int Insert(IFogadoora_OsztalyCsoport dto)
        {
            var entity = dto as Fogadoora_OsztalyCsoport;
            entity.Insert();
            DalHelper.Commit();
            return entity.ID;
        }

        public int Insert(IFogadooraIdopont dto)
        {
            var entity = dto as FogadooraIdopont;
            entity.Insert();
            DalHelper.Commit();
            return entity.ID;
        }

        public void Insert(IFogadooraIdopont_Gondviselo dto)
        {
            var entity = dto as FogadooraIdopont_Gondviselo;
            entity.Insert();
            DalHelper.Commit();
        }

        public void DeleteFogadooraIdopontGondviselo(int id)
        {
            var entity = GetFogadooraIdopontGondviselo(id) as FogadooraIdopont_Gondviselo;
            entity.Delete();
            DalHelper.Commit();
        }

        public DataSet GetFogadooraIdopontokByRendszeressegTipusId(int tanevId, int rendszeressegTipusId, DateTime datum, DateTime tanevUtolsoNapja)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "uspGetFogadooraIdopont";
                command.Parameters.Add(nameof(tanevId), tanevId);
                command.Parameters.Add(nameof(rendszeressegTipusId), rendszeressegTipusId);
                command.Parameters.Add(nameof(datum), datum);
                command.Parameters.Add(nameof(tanevUtolsoNapja), tanevUtolsoNapja);

                var dts = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(dts);
                }

                return dts;
            }
        }

        public IFogadoora Get(int id)
        {
            var entity = Fogadoora.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public IFogadoora Get()
        {
            var entity = Fogadoora.GiveAnInstance();
            return entity;
        }

        public void FullUpdate(IFogadoora dto)
        {
            var entity = dto as Fogadoora;
            entity.FullUpdate();
            DalHelper.Commit();
        }

        public void FullUpdate(IFogadooraIdopont dto)
        {
            var entity = dto as FogadooraIdopont;
            entity.FullUpdate();
            DalHelper.Commit();
        }

        public void DeleteFogadooraOsztalyCsoportByFogadooraId(int fogadooraId)
        {
            var entity = Get(fogadooraId) as Fogadoora;
            entity.Fogadoora_OsztalyCsoport.DeleteAll();

            DalHelper.Commit();
        }

        public void DeleteFogadooraOsztalyCsoportByFogadooraIdOsztalyCsoportId(int fogadooraId, int osztalyCsoportId)
        {
            var entity = Get(fogadooraId) as Fogadoora;
            var fogadoOra_osztalyCsoport = entity.Fogadoora_OsztalyCsoport.FirstOrDefault(x => osztalyCsoportId == x.OsztalyCsoportId && !x.Torolt);
            var fogadoOra_idopontok = entity.FogadooraIdopont.Where(fi => fi.FogadooraId == fogadooraId && !fi.Torolt);
            var fogadoOraIdopont_gondviselok = fogadoOra_idopontok.Select(fi => fi.FogadooraIdopont_Gondviselo.FirstOrDefault(fig => !fig.Torolt));
            if (fogadoOra_osztalyCsoport != null)
            {
                var ocs = fogadoOra_osztalyCsoport.OsztalyCsoport;
                entity.Fogadoora_OsztalyCsoport.Delete(fogadoOra_osztalyCsoport);

                var ocsGondviselok = ocs.Tanulo.SelectMany(tcs => tcs.Tanulo.Gondviselo.Where(g => !g.Torolt));
                if (ocsGondviselok != null)
                {
                    fogadoOraIdopont_gondviselok.Where(fig => ocsGondviselok.Any(g => g.ID == fig?.GondviseloId)).ToList().ForEach(x =>
                    {
                        x.Delete();
                    });
                }

                entity.TanuloEsemeny?.OsztalyCsoport?.Remove(fogadoOra_osztalyCsoport.OsztalyCsoport);
            }

            FullUpdate(entity);

            DalHelper.Commit();
        }

        public void DeleteOsztalyCsoportTanuloesemenyByFogadooraId(int fogadooraId, int osztalyCsoportId)
        {
            var fogadoora = Get(fogadooraId) as Fogadoora;
            var osztalyCsoport = DalHelper.OsztalyCsoport().Get(osztalyCsoportId) as OsztalyCsoport;

            if (fogadoora.TanuloEsemeny.OsztalyCsoport.Any(ocs => ocs.ID == osztalyCsoportId))
            {
                fogadoora.TanuloEsemeny.RemoveFromOsztalyCsoport(osztalyCsoport);
            }

            DalHelper.Commit();
        }

        public void InsertOsztalyCsoportTanuloesemenyByFogadooraId(int fogadooraId, int osztalyCsoportId)
        {
            var fogadoora = Get(fogadooraId) as Fogadoora;
            var osztalyCsoport = DalHelper.OsztalyCsoport().Get(osztalyCsoportId) as OsztalyCsoport;

            if (!fogadoora.TanuloEsemeny.OsztalyCsoport.Any(ocs => ocs.ID == osztalyCsoportId))
            {
                fogadoora.TanuloEsemeny.AddToOsztalyCsoport(osztalyCsoport);
            }

            DalHelper.Commit();
        }

        public DataSet DeleteFogadooraIdopontGondviseloByFogadooraIdOsztalyCsoportId(int fogadooraId, int osztalyCsoportId)
        {
            var ertesitendoGondviselok = new List<(int id, string email, string nev)>();
            var fogadoora = Get(fogadooraId);
            var fogadooraIdopontok = fogadoora.FogadooraIdopont.Where(x => x.FogadooraId == fogadooraId && !x.Torolt).ToArray();
            foreach (var fogadooraIdopont in fogadooraIdopontok)
            {
                var idopontGondviselo = fogadooraIdopont.FogadooraIdopont_Gondviselo.FirstOrDefault(g => !g.Torolt);
                if (idopontGondviselo != null)
                {
                    var gondviselo = idopontGondviselo.Gondviselo;
                    var tanulo = gondviselo.Tanulo;
                    var tanuloCsoport = tanulo.OsztalyCsoport.FirstOrDefault(x => x.OsztalyCsoportId == osztalyCsoportId && !x.Torolt && (x.KilepesDatum == null));
                    if (tanuloCsoport != null)
                    {
                        DeleteFogadooraIdopontGondviselo(idopontGondviselo.ID);
                        var gondviseloEmail = gondviselo.Email.SingleOrDefault(x => x.Alapertelmezett && !x.Torolt && !x.IsHibasanMegadva)?.EmailCim;

                        if (gondviseloEmail != null)
                        {
                            ertesitendoGondviselok.Add((id: idopontGondviselo.GondviseloId, email: gondviseloEmail, nev: gondviselo.Nev));
                        }
                        FullUpdate(fogadooraIdopont);
                    }
                }
            }

            DalHelper.Commit();

            DataSet gondviselokDS = new DataSet();
            DataTable table = new DataTable();
            table.Columns.Add("GondviseloId", typeof(int));
            table.Columns.Add("GondviseloEmailCim", typeof(string));
            table.Columns.Add("GondviseloNev", typeof(string));
            table.Columns.Add("TanuloId", typeof(int));
            gondviselokDS.Tables.Add(table);
            foreach (var (id, email, nev) in ertesitendoGondviselok)
            {
                table.Rows.Add(new object[] { id, email, nev });
            }
            return gondviselokDS;
        }

        public DataSet GetFogadooraGondviselo(int intezemenyId, int tanevId, int gondviseloId, int? tanarId = null, int? teremId = null, DateTime? start = null, DateTime? end = null, DateTime? startTime = null, DateTime? endTime = null, bool? isJelentkezheto = null)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pIntezmenyId", intezemenyId),
                new CommandParameter("pGondviseloId", gondviseloId)
            };

            var where = new StringBuilder();
            if (tanarId.HasValue)
            {
                paramlist.Add(new CommandParameter("pTanarId", tanarId));
                where.Append(" AND felhasznalo.Id = :pTanarId");
            }

            if (teremId.HasValue)
            {
                paramlist.Add(new CommandParameter("pTeremId", teremId));
                where.Append(" AND terem.Id = :pTeremId");
            }

            if (start.HasValue)
            {
                paramlist.Add(new CommandParameter("pStart", start));
                where.Append(" AND CAST(C_FOGADOORAKEZDETE AS DATE) >= :pStart");
            }

            if (end.HasValue)
            {
                paramlist.Add(new CommandParameter("pEnd", end));
                where.Append(" AND CAST(C_FOGADOORAVEGE AS DATE) <= :pEnd");
            }

            if (startTime.HasValue)
            {
                paramlist.Add(new CommandParameter("pStartTime", startTime.Value.TimeOfDay));
                where.Append(" AND CAST(C_FOGADOORAKEZDETE AS TIME) >= :pStartTime");
            }

            if (endTime.HasValue)
            {
                paramlist.Add(new CommandParameter("pEndTime", endTime.Value.TimeOfDay));
                where.Append(" AND CAST(C_FOGADOORAVEGE AS TIME) <= :pEndTime");
            }

            if (isJelentkezheto == true)
            {
                where.Append(" AND fogadoora.C_FOGADOORATIPUSID <> 0 AND fogadoora.C_FOGADOORAKEZDETE >= GETDATE()");
            }

            var commandText = new StringBuilder(
                $@"      SELECT DISTINCT
                     fogadoora.ID                        AS ID
                    ,terem.C_NEV                        AS Terem
                    ,terem.Id                            AS TeremId
                    ,fogadoora.C_FOGADOORAKEZDETE                              AS FogadooraKezdete
                    ,fogadoora.C_FOGADOORAVEGE                                AS FogadooraVege
                    ,fogadoora.C_FOGADOORATIPUSID                AS JelentkezesekKezeleseId
                    ,felhasznalo.C_NYOMTATASINEV                AS Pedagogus
                    ,felhasznalo.Id                                AS PedagogusId
                    ,IIF(fogadoora.C_FOGADOORATIPUSID = 0, 0, IIF(COUNT(fogadooraGondviselo.Id) = 0, 1, 2)) AS JelentkezesAllapot --0 ha nem lehet jelentkezni, 1 ha nem jelentkezett, 2 ha jelentkezett
                    ,fogadooraGondviselo.ID                    AS IdopontGondviseloId
                    ,IIF(fogadooraIdopont.ID iS NULL, NULL, IIF(fogadooraGondviselo.Id IS NULL, NULL, FORMAT(ISNULL(C_KEZDETE, ''),'HH:mm') + ' - ' + FORMAT(ISNULL(C_VEGE, ''),'HH:mm'))) AS IdopontStr
                    ,SzabadIdopontokSzama
                    ,fogadoora.C_JELENTKEZESHATARIDO                            AS JelentkezesiHatarido
            FROM T_GONDVISELO gondviselo
                INNER JOIN T_TANULOCSOPORT tanulocsoport ON tanulocsoport.C_TANULOID = gondviselo.C_TANULOID
                INNER JOIN T_FOGADOORA_OSZTALYCSOPORT fogadooraOsztalyCsoport ON fogadooraOsztalyCsoport.C_OSZTALYCSOPORTID = tanulocsoport.C_OSZTALYCSOPORTID
                INNER JOIN T_FOGADOORA fogadoora ON fogadoora.ID = fogadooraOsztalyCsoport.C_FOGADOORAID
                CROSS APPLY (
                                SELECT COUNT(fi.ID) SzabadIdopontokSzama
                                FROM T_FOGADOORAIDOPONT fi
                                    LEFT JOIN T_FOGADOORAIDOPONT_GONDVISELO fg ON fi.ID = fg.C_FOGADOORAIDOPONTID AND fg.TOROLT = 'F'
                                WHERE C_FOGADOORAID = fogadoora.ID AND fg.ID IS NULL
                                ) szabad(SzabadIdopontokSzama)
                INNER JOIN T_FELHASZNALO felhasznalo ON felhasznalo.ID = fogadoora.C_TANARID
                INNER JOIN T_TEREM terem ON terem.ID = fogadoora.C_TEREMID
                LEFT JOIN T_FOGADOORAIDOPONT fogadooraIdopont on fogadooraIdopont.C_FOGADOORAID = fogadoora.ID AND fogadooraIdopont.TOROLT = 'F'
                LEFT JOIN T_FOGADOORAIDOPONT_GONDVISELO fogadooraGondviselo on fogadooraGondviselo.C_FOGADOORAIDOPONTID = fogadooraIdopont.ID AND fogadooraGondviselo.C_GONDVISELOID = gondviselo.id
            WHERE
                gondviselo.ID = :pGondviseloId
                AND fogadoora.TOROLT = 'F'
                AND fogadoora.C_INTEZMENYID = :pIntezmenyId
                AND fogadoora.C_TANEVID = :pTanevId
                AND tanulocsoport.TOROLT = 'F'
                AND fogadooraOsztalyCsoport.TOROLT = 'F'
                AND (fogadoora.C_FOGADOORAKEZDETE >= tanulocsoport.C_BELEPESDATUM
                        AND (tanulocsoport.C_KILEPESDATUM IS NULL OR fogadoora.C_FOGADOORAKEZDETE <= tanulocsoport.C_KILEPESDATUM)) {where}
            GROUP BY fogadoora.ID, terem.C_NEV, terem.Id, fogadoora.C_FOGADOORAKEZDETE, fogadoora.C_FOGADOORAVEGE, fogadoora.C_FOGADOORATIPUSID,felhasznalo.C_NYOMTATASINEV,felhasznalo.Id,fogadooraGondviselo.ID, C_KEZDETE, C_VEGE,fogadooraIdopont.ID, SzabadIdopontokSzama, fogadoora.C_JELENTKEZESHATARIDO");

            var ds = this.GetData(commandText.ToString(), paramlist);
            return ds;
        }

        public bool IsSzabadIdopontByFogadooraIdopontId(int fogadooraIdopontId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraIdopontId", fogadooraIdopontId),
                new CommandParameter("pJelentkezhetoId", (int)FogadooraTipusEnum.Jelentkezheto)
            };

            var commandText = new StringBuilder(@"
                        SELECT
                            fogadooraGondviselo.ID GondviseloId
                        FROM T_FOGADOORAIDOPONT_GONDVISELO fogadooraGondviselo
                            INNER JOIN T_FOGADOORAIDOPONT fogadooraIdopont ON fogadooraIdopont.ID = fogadooraGondviselo.C_FOGADOORAIDOPONTID
                            INNER JOIN T_FOGADOORA fogadoora ON fogadoora.ID = fogadooraIdopont.C_FOGADOORAID
                        WHERE
                            fogadooraGondviselo.C_FOGADOORAIDOPONTID = :pFogadooraIdopontId
                            AND fogadoora.C_FOGADOORATIPUSID <> :pJelentkezhetoId
                            AND fogadooraGondviselo.TOROLT = 'F'
                            AND fogadooraIdopont.TOROLT = 'F'
                            AND fogadoora.TOROLT = 'F'");

            var ds = this.GetData(commandText.ToString(), paramlist);

            return ds.Tables[0].Rows.Count == 0;
        }

        public List<(int Id, DateTime Idopont)> GetFogadooraIdByGroupIdList(int tanevId, int fogadooraGroupId, DateTime start, DateTime end)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pFogadooraGroupId", fogadooraGroupId),
                new CommandParameter("pIdoszakKezdete", start),
                new CommandParameter("pIdoszakVege", end)
            };

            var commandText = new StringBuilder(@"
                        SELECT
                             ID
                            ,C_FOGADOORAKEZDETE FogadooraKezdete
                        FROM T_FOGADOORA
                        WHERE
                            C_TANEVID = :pTanevId
                            AND C_GROUPID = :pFogadooraGroupId
                            AND C_FOGADOORAKEZDETE BETWEEN :pIdoszakKezdete AND :pIdoszakVege
                            AND TOROLT = 'F'");

            var ds = this.GetData(commandText.ToString(), paramlist);

            var list = ds.Tables[0].AsEnumerable()
                .Select(r => (Id: r.Field<int>("ID"), Idopont: r.Field<DateTime>("FogadooraKezdete")))
                .ToList();

            return list;
        }

        public void UpdateFogadooraGroupId(int fogadooraId, int groupId)
        {
            var fogadoora = Get(fogadooraId);
            fogadoora.GroupId = groupId;
            FullUpdate(fogadoora);

            DalHelper.Commit();
        }

        public string GetProfileData(int felhasznaloId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFelhasznaloId", felhasznaloId)
            };

            var commandText = new StringBuilder(@"
                SELECT
                    ISNULL(C_PROFILE,'') AS ProfileXml
                FROM T_USERPROFILE
                WHERE
                    C_FELHASZNALOID = :pFelhasznaloId
            ");

            var ds = this.GetData(commandText.ToString(), paramlist);

            return ds.Tables[0].Rows.Count == 0 ? string.Empty : ds.Tables[0].Rows[0].Field<string>("ProfileXml");
        }

        public DataSet GetFogadooraJelentkezettGondviselokByGroupId(int groupId, DateTime kedzete, DateTime vege)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pGroupId", groupId),
                new CommandParameter("pKezdete", kedzete),
                new CommandParameter("pVege", vege)
            };

            var commandText = new StringBuilder(@"
                 SELECT
                     g.Id                AS GondviseloId
                FROM T_FOGADOORAIDOPONT_GONDVISELO_OSSZES fogadooraIdopontGondviselo
                    INNER JOIN T_FOGADOORAIDOPONT_OSSZES fogadooraIdopont ON fogadooraIdopont.ID = fogadooraIdopontGondviselo.C_FOGADOORAIDOPONTID
                    INNER JOIN T_FOGADOORA_OSSZES fogadoora ON fogadoora.ID = fogadooraIdopont.C_FOGADOORAID
                    INNER JOIN T_GONDVISELO_OSSZES g ON g.ID = fogadooraIdopontGondviselo.C_GONDVISELOID
                    INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = g.C_TANULOID
                 WHERE
                    fogadoora.C_GROUPID = :pGroupId
                    AND C_FOGADOORAKEZDETE >= :pKezdete
                    AND C_FOGADOORAKEZDETE <= :pVege
                    AND fogadooraIdopontGondviselo.TOROLT = 'F'
                    AND fogadooraIdopont.TOROLT = 'F'
                    AND fogadoora.TOROLT = 'F'
            ");

            var ds = this.GetData(commandText.ToString(), paramlist);

            return ds;
        }

        public DataSet GetFogadooraJelentkezettGondviselokDataSet(int fogadooraId, List<int> fogadooraOsztalyCsoportIdList)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraId", fogadooraId)
            };

            var commandText = new StringBuilder($@"
                SELECT
                     g.C_NEV                AS GondviseloNev
                    ,f.C_NYOMTATASINEV      AS TanuloNev
                    ,tanulo.osztaly         AS OsztalyCsoportNev
                    ,FORMAT(fogadooraIdopont.C_KEZDETE, 'HH:mm') + ' - ' + FORMAT(fogadooraIdopont.C_VEGE, 'HH:mm') AS Idointervallum
                    ,fogadooraIdopont.C_KEZDETE AS Kezdete
                    ,tanulo.osztalyId AS OsztalyCsoportId
                FROM T_FOGADOORAIDOPONT_GONDVISELO_OSSZES fogadooraIdopontGondviselo
                    INNER JOIN T_FOGADOORAIDOPONT_OSSZES fogadooraIdopont ON fogadooraIdopont.ID = fogadooraIdopontGondviselo.C_FOGADOORAIDOPONTID
                    INNER JOIN T_FOGADOORA_OSSZES fogadoora ON fogadoora.ID = fogadooraIdopont.C_FOGADOORAID
                    INNER JOIN T_GONDVISELO_OSSZES g ON g.ID = fogadooraIdopontGondviselo.C_GONDVISELOID
                    INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = g.C_TANULOID
                    CROSS APPLY
                        (
                            SELECT
                                C_NEV
                                ,ocs.ID
                            FROM T_TANULOCSOPORT tcs
                                INNER JOIN T_OSZTALYCSOPORT ocs on ocs.ID = tcs.C_OSZTALYCSOPORTID
                            WHERE
                                C_TANULOID = g.C_TANULOID
                                AND (fogadoora.C_FOGADOORAKEZDETE >= tcs.C_BELEPESDATUM
                                AND (tcs.C_KILEPESDATUM IS NULL OR fogadoora.C_FOGADOORAKEZDETE <= tcs.C_KILEPESDATUM))
                                AND tcs.TOROLT = 'f'
                        ) tanulo(osztaly, osztalyId)
                 WHERE
                    fogadoora.ID = :pFogadooraId
                    AND tanulo.osztalyId IN ({SqlLogic.ParseListToParameter(fogadooraOsztalyCsoportIdList)})
                    AND fogadooraIdopontGondviselo.TOROLT = 'F'
                    AND fogadooraIdopont.TOROLT = 'F'
            ");

            var ds = this.GetData(commandText.ToString(), paramlist);

            return ds;
        }

        public DataSet GeTeremOrarendDataSet(int intezmenyId, int tanevId, DateTime start, DateTime end, int teremId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "sp_GetOrarend";

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pIdoszakKezdete", start.Date);
                command.Parameters.Add("pIdoszakVege", end.Date);
                command.Parameters.Add("pTanarId", DBNull.Value);
                command.Parameters.Add("pOsztalyCsoportId", DBNull.Value);
                command.Parameters.Add("pTanuloId", DBNull.Value);
                command.Parameters.Add("pCsakOrarendiOrak", false);
                command.Parameters.Add("pTantargyId", DBNull.Value);
                command.Parameters.Add("pTeremId", teremId);
                command.Parameters.Add("pIsHelyettesitesNelkul", false);
                command.Parameters.Add("pIsNapirend", DBNull.Value);

                var dts = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(dts);
                }

                return dts;
            }
        }

        public DataSet GetPedagogusOrarendDataSet(int intezmenyId, int tanevId, DateTime start, DateTime end, int tanarId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "sp_GetOrarend";

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pIdoszakKezdete", start.Date);
                command.Parameters.Add("pIdoszakVege", end.Date);
                command.Parameters.Add("pTanarId", tanarId);
                command.Parameters.Add("pOsztalyCsoportId", DBNull.Value);
                command.Parameters.Add("pTanuloId", DBNull.Value);
                command.Parameters.Add("pCsakOrarendiOrak", false);
                command.Parameters.Add("pTantargyId", DBNull.Value);
                command.Parameters.Add("pTeremId", DBNull.Value);
                command.Parameters.Add("pIsHelyettesitesNelkul", false);
                command.Parameters.Add("pIsNapirend", DBNull.Value);

                var dts = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(dts);
                }

                return dts;
            }
        }

        public DataSet GetPedagogusTeremFogadooraDataSet(int tanevId, DateTime start, DateTime end, int? tanarId = null, int? teremId = null)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetPedagogusTeremFogadoora";

                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pIdoszakKezdete", start.Date);
                command.Parameters.Add("pIdoszakVege", end.Date);
                command.Parameters.Add("pTanarId", tanarId);
                command.Parameters.Add("pTeremId", teremId);

                var dts = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(dts);
                }

                return dts;
            }
        }

        public DataTable GetFogadooraOsztalyCsoportMaradekElemek(int fogadooraId)
        {
            var paramlist = new List<CommandParameter>
            {
                new CommandParameter("pFogadooraGroupId", fogadooraId)
            };

            var commandText = new StringBuilder(@"
              SELECT
                focs.C_OSZTALYCSOPORTID AS OsztalyCsoportId
                ,COUNT(focs.ID) AS Darab
              FROM T_FOGADOORA_OSZTALYCSOPORT_OSSZES focs
              INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = focs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
              INNER JOIN T_FOGADOORA_OSSZES f ON f.ID = focs.C_FOGADOORAID AND f.TOROLT = 'F'
              WHERE f.C_GROUPID = :pFogadooraGroupId
                AND focs.TOROLT = 'F'
              GROUP BY focs.C_OSZTALYCSOPORTID
            ");

            var ds = this.GetData(commandText.ToString(), paramlist);

            return ds.Tables[0];
        }

        public IFogadoora_OsztalyCsoport GetFogadooraOsztalyCsoport()
        {
            var entity = Fogadoora_OsztalyCsoport.GiveAnInstance();
            return entity;
        }

        public IFogadooraIdopont GetFogadooraIdopont()
        {
            var entity = FogadooraIdopont.GiveAnInstance();
            return entity;
        }

        public IFogadooraIdopont GetFogadooraIdopont(int id)
        {
            var entity = FogadooraIdopont.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public IFogadooraIdopont_Gondviselo GetFogadooraIdopontGondviselo()
        {
            var entity = FogadooraIdopont_Gondviselo.GiveAnInstance();
            return entity;
        }

        public IFogadooraIdopont_Gondviselo GetFogadooraIdopontGondviselo(int id)
        {
            var entity = FogadooraIdopont_Gondviselo.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public DataSet GetKozelgoFogadoorak()
        {
            var commandText = @"
SELECT 
     Fogadoora.ID AS FogadooraId
    ,Fogadoora.C_FOGADOORAKEZDETE AS FogadooraKezdete
    ,Fogadoora.C_FOGADOORAVEGE AS FogadooraVege
    ,Felhasznalo.ID as UserId    
    ,Felhasznalo.C_NYOMTATASINEV AS NyomtatasiNev
    ,Email.C_EMAILCIM AS EmailCim
    ,Email.C_GUID as EmailGuid
    ,Terem.C_NEV AS TeremNev
    ,i.C_AZONOSITO as IntezmenyAzon
    ,ia.C_NEV as IntezmenyNev
    ,ia.C_IRANYITOSZAM + ' ' + ia.C_VAROS + ' ' + ia.C_KOZTERULETNEV + ' ' + ia.C_KOZTERULETJELLEGENEV + ' ' + ia.C_HAZSZAM as IntezmenyCim
    ,ia.C_EMAILCIM as IntezmenyFenntartoEmail
    ,ocs.C_NEV as OsztalyCsoportNev
FROM T_FOGADOORA Fogadoora
INNER JOIN T_FOGADOORA_OSZTALYCSOPORT focs on focs.C_FOGADOORAID = Fogadoora.ID AND focs.TOROLT = 'F'
INNER JOIN T_OSZTALYCSOPORT ocs on ocs.ID = focs.C_OSZTALYCSOPORTID
INNER JOIN T_FELHASZNALO Felhasznalo ON Felhasznalo.ID = Fogadoora.C_TANARID AND Fogadoora.TOROLT = 'F'
INNER JOIN T_EMAIL Email ON Email.C_FELHASZNALOID = Felhasznalo.ID AND Email.TOROLT = 'F'
INNER JOIN T_TEREM Terem ON Terem.ID = Fogadoora.C_TEREMID AND Terem.TOROLT = 'F'
INNER JOIN T_INTEZMENY i on i.ID = Fogadoora.C_INTEZMENYID
INNER JOIN T_INTEZMENYADATOK ia on ia.C_INTEZMENYID = Fogadoora.C_INTEZMENYID and ia.C_TANEVID = Fogadoora.C_TANEVID
WHERE
    CONVERT(DATE, Fogadoora.C_FOGADOORAKEZDETE, 120) = CONVERT(DATE, DATEADD(DAY, 3, GETDATE()), 120)
    AND Fogadoora.TOROLT = 'F'
    AND Email.C_ALAPERTELMEZETT = 'T'
    AND Email.C_EMAILCIM IS NOT NULL
    AND Email.C_EMAILCIM != ''
";

            var ds = GetData(commandText);
            return ds;
        }

        public DataSet GetFogadooraDokumentumAdatok(int fogadooraId, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetFogadooraDokumentumAdatok";

                command.Parameters.Add("pFogadooraId", fogadooraId);
                command.Parameters.Add("pTanevId", tanevId);

                var ds = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(ds);
                }

                return ds;
            }
        }

        public bool IsFogadooraJelentkezesFeatureEnabled(int fogadooraIdopontId)
        {
            var commandParameterList = new List<CommandParameter>
            {
                new CommandParameter(nameof(fogadooraIdopontId), fogadooraIdopontId)
            };

            var commandText = $@"
                        SELECT
                            f.ID
                        FROM T_FOGADOORA f
                            INNER JOIN T_FOGADOORAIDOPONT foi ON foi.C_FOGADOORAID = f.ID AND f.C_FOGADOORATIPUSID IN (1, 2) AND foi.TOROLT = 'F' 
                        WHERE foi.ID = @{nameof(fogadooraIdopontId)}
                            AND f.TOROLT = 'F'";

            var ds = GetData(
                commandText: commandText,
                parameters: commandParameterList);

            return ds.Tables[0].Rows.Count > 0;
        }
    }
}