using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Kreta.Core.Logic;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;
using Kreta.Ellenorzo.Dao.VN.Intezmeny.Rendszermodul;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

namespace Kreta.DataAccessManual
{
    internal class IntezmenyDAL : DataAccessBase, IIntezmenyDal
    {
        public IntezmenyDAL(DalHandler handler, GridParameters parameters) : base(handler, parameters)
        {
        }

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

        public int GetIntezmenyId(string intezmenyAzonosito)
        {
            var commandText = @"SELECT ID FROM T_INTEZMENY WHERE C_AZONOSITO = :pAzonosito AND TOROLT = 'F'";
            var paramList = new List<CommandParameter> { new CommandParameter("pAzonosito", intezmenyAzonosito) };

            var ds = GetData(commandText, paramList);

            int id = 0;
            if (ds.Tables[0].Rows.Count > 0)
            {
                id = ds.Tables[0].Rows[0].Field<int>("ID");
            }

            return id;
        }

        public List<string> GetOsszesIntezmeny()
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.CommandText = @"SELECT C_AZONOSITO FROM T_INTEZMENY WHERE TOROLT = 'F'";

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

                    adapter.Fill(dts);

                    var result = dts.Tables[0].AsEnumerable().Select(a => a.Field<string>(0).ToUpper()).ToList();

                    return result;
                }
            }
        }

        public DataSet GetIntIdAndTanevNevByIntezmenyadatokId(int id)
        {
            List<CommandParameter> pList = new List<CommandParameter>();
            pList.Add(new CommandParameter("pId", id));

            var command = @"select
intAdatok.C_INTEZMENYID IntId,
tanev.C_NEV TanevNev
from T_INTEZMENYADATOK intAdatok
inner join T_TANEV tanev on tanev.ID = intAdatok.C_TANEVID
where intAdatok.TOROLT = 'F'
and intAdatok.ID = :pId";

            return this.GetData(command, pList);
        }

        public DataSet GetIntIdAndTanevIdByIntezmenyadatokId(int id)
        {
            List<CommandParameter> pList = new List<CommandParameter>();
            pList.Add(new CommandParameter("pId", id));

            var command = @"select
C_INTEZMENYID IntId,
C_TANEVID TanevId
from T_INTEZMENYADATOK
where TOROLT = 'F'
and ID = :pId";

            return this.GetData(command, pList);
        }

        public DataSet GetIntezmenyek()
        {
            var command = new StringBuilder(@"
SELECT
   Intezmeny.C_AZONOSITO AS Azonosito
  ,Intezmeny.C_FENNTARTOAZONOSITO AS FenntartoAzonosito
  ,'' AS Cim
  ,IntemzenyAdatok.C_IGAZGATONEVE AS IgazgatoNeve
  ,IntemzenyAdatok.C_NEV AS Nev
  ,IntemzenyAdatok.C_OMKOD AS OMKod
  ,IntemzenyAdatok.C_TELEFONSZAM AS Telefonszam
  ,IntemzenyAdatok.C_EMAILCIM AS EmailCim
  ,IntemzenyAdatok.C_IRANYITOSZAM AS Iranyitoszam
  ,IntemzenyAdatok.C_VAROS AS Varos
  ,IntemzenyAdatok.C_ROVIDNEV AS RovidNev
  ,IntemzenyAdatok.C_ADMINEMAILCIM AS AdminEmailCim
  ,IntemzenyAdatok.C_KRETATIPUS AS KretaTipus
  ,Tanev.C_NEV AS AktivTanev
  ,IntemzenyAdatok.TOROLT AS Torolt
  ,Intezmeny.CREATED AS IntezmenyLetrehozasDatuma
  ,(SELECT MAX(C_UTOLSOBELEPES)
    FROM T_FELHASZNALOBELEPES
    WHERE TOROLT = 'F'
      AND C_INTEZMENYID = Intezmeny.ID
      AND C_TANEVID = IntemzenyAdatok.C_TANEVID) AS UtolsoBelepesIdeje
  ,IntezmenyAdatszolgaltatas.C_ELFOGADOTTESL AS ElfogadottESL
  ,IntezmenyAdatszolgaltatas.C_ELFOGADOTTTTF AS ElfogadottTTF
  ,IntezmenyAdatszolgaltatas.C_ELFOGADOTTLETSZAM AS ElfogadottLetszam
  ,IntezmenyAdatszolgaltatas.C_ELFOGADOTTESLDATUMA AS ElfogadottESLDatuma
  ,IntezmenyAdatszolgaltatas.C_ELFOGADOTTTTFDATUMA AS ElfogadottTTFDatuma
  ,IntezmenyAdatszolgaltatas.C_ELFOGADOTTLETSZAMDATUMA AS ElfogadottLetszamDatuma
  ,IntezmenyAdatszolgaltatas.C_VEGLEGESESL AS VeglegesESL
  ,IntezmenyAdatszolgaltatas.C_VEGLEGESTTF AS VeglegesTTF
  ,IntezmenyAdatszolgaltatas.C_VEGLEGESLETSZAM  AS VeglegesLetszam
  ,IntezmenyAdatszolgaltatas.C_VEGLEGESESLDATUMA AS VeglegesESLDatuma
  ,IntezmenyAdatszolgaltatas.C_VEGLEGESTTFDATUMA AS VeglegesTTFDatuma
  ,IntezmenyAdatszolgaltatas.C_VEGLEGESLETSZAMDATUMA AS VeglegesLetszamDatuma
  ,IntezmenyAdatszolgaltatas.C_FENNTARTOESLELUTASITASOKA AS FenntartoESLElutasitasOka
  ,IntezmenyAdatszolgaltatas.C_FENNTARTOTTFELUTASITASOKA AS FenntartoTTFElutasitasOka
FROM T_INTEZMENYADATOK IntemzenyAdatok
  INNER JOIN T_INTEZMENY Intezmeny ON Intezmeny.ID = IntemzenyAdatok.C_INTEZMENYID
    AND Intezmeny.TOROLT = 'F'
  INNER JOIN T_TANEV Tanev ON Tanev.C_INTEZMENYID = IntemzenyAdatok.C_INTEZMENYID
    AND IntemzenyAdatok.C_TANEVID = Tanev.ID
    AND Tanev.C_AKTIV = 'T'
    AND Tanev.TOROLT = 'F'
  LEFT JOIN T_INTEZMENYADATSZOLGALTATAS IntezmenyAdatszolgaltatas ON IntezmenyAdatszolgaltatas.C_INTEZMENYID = Intezmeny.ID
    AND IntezmenyAdatszolgaltatas.C_TANEVID = IntemzenyAdatok.C_TANEVID");

            return GetData(command.ToString());
        }

        public int? GetIntezmenyIdByAzonosito(string azonosito)
        {
            using (var command = new SDACommand
            {
                Connection = UserContext.Instance.SDAConnection,
                Transaction = UserContext.Instance.SDATransaction,
                CommandText = @"
                    SELECT
                        T_INTEZMENY.ID

                    FROM
                        T_INTEZMENY

                    WHERE
                        T_INTEZMENY.C_AZONOSITO = :pAzonosito"
            })
            {
                command.Parameters.Add("pAzonosito", azonosito);

                object id = command.ExecuteScalar();

                return (id != DBNull.Value) ? Convert.ToInt32(id) : new int?();
            }
        }

        public DataSet GetIntezmenyIdAndNevByAzonosito(string azonosito)
        {
            var commandParameters = new List<CommandParameter>()
            {
                new CommandParameter("pAzonosito", azonosito)
            };
            var commandText = @"
                    SELECT
                       i.ID Id
                      ,ia.C_NEV Nev
                    FROM T_INTEZMENY i
                      INNER JOIN T_INTEZMENYADATOK ia ON i.ID = ia.C_INTEZMENYID
                    WHERE
                        i.C_AZONOSITO = :pAzonosito";

            return GetData(commandText, commandParameters);
        }

        public int? GetIntezmenyAdatokIdByAzonosito(string azonosito)
        {
            using (var command = new SDACommand
            {
                Connection = UserContext.Instance.SDAConnection,
                Transaction = UserContext.Instance.SDATransaction,
                CommandText = @"
SELECT
    T_INTEZMENYADATOK.ID

FROM
    T_INTEZMENY
    JOIN T_INTEZMENYADATOK ON T_INTEZMENYADATOK.C_INTEZMENYID = T_INTEZMENY.ID

WHERE
    T_INTEZMENY.C_AZONOSITO = :pAzonosito
    AND EXISTS (SELECT 1 FROM T_TANEV WHERE TOROLT = 'F' AND C_AKTIV = 'T' AND ID = T_INTEZMENYADATOK.C_TANEVID)",

            })
            {
                command.Parameters.Add("pAzonosito", azonosito);

                object id = command.ExecuteScalar();

                return (id != DBNull.Value) ? Convert.ToInt32(id) : new int?();
            }
        }

        public RendszermodulListResponseDao GetRendszermodulDao(int intezmenyAdatokId)
        {
            List<CommandParameter> pList = new List<CommandParameter>
            {
                new CommandParameter("pIntezmenyAdatokId", intezmenyAdatokId, SDADBType.Int)
            };

            var command = @"
SELECT
    C_ALAPKRETA,
    C_KRETAESL,
    C_KRETAEUGYINTEZES

FROM
    T_INTEZMENYADATOK

WHERE T_INTEZMENYADATOK.ID = :pIntezmenyAdatokId";

            return GetData(command, pList).Tables[0].Rows[0].ToDao<RendszermodulListResponseDao>();
        }

        public void Insert(IIntezmenyAdatok dto)
        {
            var entity = dto as IntezmenyAdatok;
            entity.Insert();
            dto.ID = entity.ID;

            FollowUp(entity);
            DalHelper.Commit();
        }

        public void Update(IIntezmenyAdatok dto)
        {
            var entity = dto as IntezmenyAdatok;
            entity.Update();

            FollowUp(entity);
            DalHelper.Commit();
        }

        public void SetTantargyFelosztasElfogadas(int intezmenyId, int tanevId, bool elfogadva, string elutasitas_ok)
        {
            var intezmeny = Get(intezmenyId);
            var intAdatszolgaltatas = intezmeny.IntezmenyAdatszolgaltatas.Where(i => i.TanevId == tanevId).FirstOrDefault();

            intAdatszolgaltatas.ElfogadottTTF = elfogadva;
            if (!elfogadva)
            {
                intAdatszolgaltatas.VeglegesTTF = false;
                intAdatszolgaltatas.FenntartoTTFElutasitasOka = elutasitas_ok;
            }

            //Elfogadott
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "sp_FoglalkozasArchiv";
                command.Parameters.Add("tanevId", tanevId);
                command.Parameters.Add("intezmenyId", intezmenyId);

                command.ExecuteNonQuery();
            }

            DalHelper.IntezmenyAdatszolgaltatasDal().Update(intAdatszolgaltatas);
        }

        public void SetTantargyFelosztasVeglegesites(int intezmenyId, int tanevID, bool veglegesit, bool isKovTanev)
        {
            var intezmeny = Get(intezmenyId);

            var intAdatszolgaltatas = intezmeny.IntezmenyAdatszolgaltatas.Where(i => i.TanevId == tanevID).FirstOrDefault();

            if (isKovTanev)
            {
                intAdatszolgaltatas.VeglegesETTF = veglegesit;
            }
            else
            {
                intAdatszolgaltatas.VeglegesTTF = veglegesit;
            }

            intAdatszolgaltatas.ElfogadottTTF = null;
            intAdatszolgaltatas.FenntartoTTFElutasitasOka = null;

            DalHelper.IntezmenyAdatszolgaltatasDal().Update(intAdatszolgaltatas);
        }

        public string GetNeptunNaploLink(int intezmenyId)
        {
            return Get(intezmenyId).NeptunNaploLink;
        }

        public string GetPoszeidonBejelentkezesiNev(int intezmenyId)
        {
            return Get(intezmenyId).PoszeidonBejelentkezesiNev;
        }

        public string GetIntezmenyNevById(int intezmenyId, int tanevId)
        {
            return Get(intezmenyId).IntezmenyAdatok.First(x => x.IntezmenyId == intezmenyId && x.TanevId == tanevId).Nev;
        }

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

        public void FollowUpIntezmenyAdatok(int intezmenyId, int aktTanevId, int kovetkezoTanevId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = "uspFollowUpIntezmenyAdatok";
                command.Parameters.Add("intezmenyId", intezmenyId);
                command.Parameters.Add("aktTanevId", aktTanevId);
                command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public DataSet GetOrganizationIdentyNameWithOmCode(bool x)
        {
            var command = @"
                SELECT " +
                   (x ? "ia.C_ROVIDNEV" : "ia.C_NEV")
                   + @",ia.C_OMKOD,
                    ISNULL((SELECT IIF(C_DATE < DATEADD(yy,-1,GETDATE()),'T','F') FROM T_LICENCE l WHERE l.C_TANEVID = t.ID AND l.TOROLT = 'F'),'F') as licence
                    ,ia.C_ISSZIRINTEZMENY as IsSzirIntezmeny
                    ,i.C_ISARCHIV as IsArchivIntezmeny
                FROM T_INTEZMENYADATOK ia
                INNER JOIN T_TANEV t ON t.C_INTEZMENYID = ia.C_INTEZMENYID
                  AND ia.C_TANEVID = t.ID
                  AND t.C_AKTIV = 'T'
                INNER JOIN T_INTEZMENY i ON i.ID = ia.C_INTEZMENYID
                WHERE ia.TOROLT = 'F'
                  AND t.TOROLT = 'F'";

            return GetData(command, booleanColumns: "IsSzirIntezmeny,IsArchivIntezmeny");
        }

        public string GetOrganizationSystemSettingsJSON()
        {
            using (var command = new SDACommand
            {
                Connection = UserContext.Instance.SDAConnection,
                Transaction = UserContext.Instance.SDATransaction,
                CommandText = @"
                    SELECT r.C_ERTEK
                    FROM T_RENDSZERBEALLITAS r
                    INNER JOIN T_TANEV t ON t.C_INTEZMENYID = r.C_INTEZMENYID
                      AND r.C_TANEVID = t.ID
                      AND t.C_AKTIV = 'T'
                    WHERE r.C_BEALLITASTIPUS = :pRendszerBeallitasTipus"
            })
            {

                command.Parameters.Add("pRendszerBeallitasTipus", (int)Enums.RendszerBeallitasTipusEnum.Intezmeny_rovid_nevenek_beallitasa);

                object rendszerBeallitasJSON = command.ExecuteScalar();
                return rendszerBeallitasJSON.ToString();
            }
        }

        public DataSet GetAktivIntezmenyNevCim(string intezmenyAzonosito)
        {
            var commandParams = new List<CommandParameter> { new CommandParameter(nameof(intezmenyAzonosito), intezmenyAzonosito) };

            var commandText = $@"
SELECT ia.C_NEV
      ,ia.C_IRANYITOSZAM
      ,ia.C_VAROS
      ,ia.C_AJTO
      ,ia.C_EMELET
      ,ia.C_HAZSZAM
      ,ia.C_KOZTERULETJELLEGENEV
      ,ia.C_KOZTERULETNEV
      ,ia.C_OMKOD
      ,ia.C_EMAILCIM
      ,ia.C_TELEFONSZAM
      ,ia.C_INTEZMENYID
FROM T_INTEZMENYADATOK ia
INNER JOIN T_TANEV t ON t.ID = ia.C_TANEVID AND t.C_INTEZMENYID = ia.C_INTEZMENYID AND t.C_AKTIV = 'T' AND t.TOROLT = 'F'
INNER JOIN T_INTEZMENY i ON i.ID = ia.C_INTEZMENYID AND i.C_AZONOSITO = @{nameof(intezmenyAzonosito)} AND i.TOROLT = 'F'
WHERE ia.TOROLT = 'F'
                                      ";
            return GetData(commandText, commandParams);
        }

        public string GetNexiusId(int intezmenyId)
        {
            return Get(intezmenyId).NexiusId;
        }

        public DataSet GetTelephelyAnyaintezmenyList(int tanevId)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId)
            };

            var command = new StringBuilder(@"
                select
                    ID as ID,
                    ISNULL(m.C_NEV,'') + ' - ' + ISNULL(m.C_MUKODESIHELYAZONOSITO,'') as Nev
                from T_MUKODESIHELY_OSSZES m
                where m.TOROLT = 'F'
                    AND m.C_MUKODESIHELYTIPUSA in (5431, 5432)
                    AND m.C_TANEVID = :pTanevId
            ");

            return this.GetData(command.ToString(), parameters);
        }

        public DataSet GetIntezmenyIdAktivTanevIdByAzonosito(string azonosito)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pIntezmenyAzonosito", azonosito)
            };

            string commandText = @"
                SELECT i.ID AS IntezmenyId, t.ID AS AktivTanevId, t.C_SORSZAM AS TanevSorszam
                FROM T_INTEZMENY i
                  INNER JOIN T_TANEV t ON t.C_INTEZMENYID = i.ID AND t.C_AKTIV = 'T'
                WHERE i.TOROLT = 'F' AND t.TOROLT = 'F'
                    AND i.C_AZONOSITO = :pIntezmenyAzonosito
            ";

            return GetData(commandText, parameters);
        }

        public bool IsSuccessAuthorizedDate()
        {
            using (var command = new SDACommand
            {
                Connection = UserContext.Instance.SDAConnection,
                Transaction = UserContext.Instance.SDATransaction,
                CommandText = @"
                    SELECT
                        IIF(GETDATE() BETWEEN DATEADD(mi, -5, C_ERVENYESSEGKEZDETE) AND DATEADD(mi, 10, C_ERVENYESSEGKEZDETE), 0, 1)
                    FROM T_DASHBOARDUZENET du
                    WHERE
                        du.TOROLT = 'F' and du.C_EGYEDIAZONOSITO = 'LOGINRENDSZERERTESITES'"
            })
            {
                object result = command.ExecuteScalar();
                return result.ToString() != "0";
            }
        }

        public DateTime? GetFrissitesDatum()
        {
            using (var command = new SDACommand
            {
                Connection = UserContext.Instance.SDAConnection,
                Transaction = UserContext.Instance.SDATransaction,
                CommandText = @"
                    SELECT
                        C_ERVENYESSEGKEZDETE
                    FROM T_DASHBOARDUZENET du
                    WHERE
                        du.TOROLT = 'F' and du.C_EGYEDIAZONOSITO = 'LOGINRENDSZERERTESITES'"
            })
            {
                object result = command.ExecuteScalar();
                return !string.IsNullOrWhiteSpace(result.ToString()) ? (DateTime?)DateTime.Parse(result.ToString()) : null;
            }
        }

        public DataSet GetIntezmenyAdatok(int intezmenyId, int tanevId)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pIntezmenyId", intezmenyId),
                new CommandParameter("pTanevId", tanevId)
            };

            string commandText = @"
                SELECT 
                  i.ID IntezmenyId
                  ,i.C_AZONOSITO IntezmenyAzonosito
                  ,i.C_FENNTARTOAZONOSITO FenntartoAzonosito
                  ,i.C_POSZEIDONBEJELENTKEZESINEV PoszeidonBejelentkezesiNev
                  ,i.C_ALTERNATIVAZONOSITO AlternativAzonosito
                  ,i.C_FENNTARTOEMAILCIM FenntartoEmailCim
                  ,i.C_IKTATOSZERVEZETAZONOSITO IktatoSzervezetAzonosito
                  ,ia.ID IntezmenyAdatokId
                  ,ia.C_IGAZGATONEVE IgazgatoNeve
                  ,ia.C_NEV Nev
                  ,ia.C_OMKOD OMKod
                  ,ia.C_TELEFONSZAM Telefonszam
                  ,ia.C_EMAILCIM EmailCim
                  ,ia.C_IRANYITOSZAM IranyitoSzam
                  ,ia.C_VAROS Varos
                  ,ia.C_ROVIDNEV RovidNev
                  ,ia.C_ADMINEMAILCIM AdminEmailCim
                  ,ia.C_ENGEDELYEZETTALLASHELYEK EngedelyezettAllashelyek
                  ,ia.C_AJTO Ajto
                  ,ia.C_EMELET Emelet
                  ,ia.C_HAZSZAM Hazszam
                  ,ia.C_KOZTERULETNEV KozteruletNev
                  ,ia.C_ISCSAKLEPKEZELO IsCsakLepKezelo
                  ,ia.C_KOZTERULETJELLEGENEV KozteruletJellegeNev
                  ,ia.C_ISBAISTATUSZAKTIV IsBaiStatuszAktiv
                  ,ia.C_ISSZIRINTEZMENY IsSzirIntezmeny 
                  ,iasz.C_ELFOGADOTTESL ElfogadottESL
                  ,iasz.C_ELFOGADOTTTTF ElfogadottTTF
                  ,iasz.C_VEGLEGESESL VeglegesESL
                  ,iasz.C_VEGLEGESTTF VeglegesTTF
                  ,iasz.C_VEGLEGESBEIRATKOZAS VeglegesBeiratkozas
                  ,iasz.C_VEGLEGESETTF VeglegesETTF
                  ,l.C_DATE LicenceDatum
                  ,ia.C_ISSZAKKEPZO IsSzakkepzo
                  ,ia.C_JUTTATASHATARNAP JuttatasHatarnap
                  ,i.C_ISARCHIV as IsArchivIntezmeny 
                  ,ia.C_DKTTANARURL as DKTTanarUrl
                  ,ia.C_DKTTANULOURL as DKTDiakUrl
          ,ia.C_ISDKTAKTIV as IsDKTAktiv
                  ,ia.C_ISAKTIVPROJEKTJELENTKEZES as IsProjektAktiv
                  ,i.C_GUID as IntezmenyGuid
                  ,ia.C_ISSZAKKEPZOJUTTATAS as IsSzakkepzoJuttatas
                FROM T_INTEZMENY_OSSZES i
                  INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON ia.C_INTEZMENYID = i.ID AND ia.C_TANEVID = :pTanevId AND ia.TOROLT = 'F'
                  INNER JOIN T_INTEZMENYADATSZOLGALTATAS_OSSZES iasz ON iasz.C_INTEZMENYID = i.ID AND iasz.C_TANEVID = :pTanevId AND iasz.TOROLT = 'F'
                  INNER JOIN T_LICENCE_OSSZES l ON l.C_INTEZMENYID = i.ID AND l.C_TANEVID = :pTanevId AND l.TOROLT = 'F'
                WHERE i.ID = :pIntezmenyId
            ";

            return GetData(commandText, parameters);
        }

        public IEnumerable<string> GetIntezmenyVarosai(int intezmenyId, int tanevId)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pIntezmenyId", intezmenyId),
                new CommandParameter("pTanevId", tanevId)
            };

            var result = new List<string>();

            var commandText = @"
                SELECT C_VAROS
                FROM T_INTEZMENYADATOK_OSSZES
                WHERE TOROLT = 'F'
                    AND C_INTEZMENYID = :pIntezmenyId
                    AND C_TANEVID = :pTanevId";

            var ds = GetData(commandText, parameters);

            result.AddRange(ds.Tables[0].AsEnumerable().Select(r => r.Field<string>("C_VAROS")));

            commandText = @"
                SELECT DISTINCT C_VAROS
                FROM T_MUKODESIHELY_OSSZES
                WHERE TOROLT = 'F'
                    AND C_INTEZMENYID = :pIntezmenyId
                    AND C_TANEVID = :pTanevId
                ORDER BY C_VAROS";

            ds = GetData(commandText, parameters);

            result.AddRange(ds.Tables[0].AsEnumerable().Select(r => r.Field<string>("C_VAROS")));

            return result;
        }

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

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);

                var dataSet = new DataSet();
                using (var sdaDataAdapter = new SDADataAdapter())
                {
                    sdaDataAdapter.SelectCommand = command;
                    sdaDataAdapter.Fill(dataSet);
                }

                return dataSet;
            }
        }

        public string GetEnvironmentName(string intezmenyAzonosito, string key)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.Text;
                command.CommandText = $@"SELECT [kr_{intezmenyAzonosito}_schema].fnGetEnvironmentName({string.Format("N'{0}'", !string.IsNullOrWhiteSpace(key) ? key : "Kornyezet")})";

                return command.ExecuteScalar()?.ToString() ?? string.Empty;
            }
        }
    }
}