using System.Collections.Generic;
using System.Data;
using System.Text;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.ParameterClasses;
using Kreta.DataAccessManual.Util;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

namespace Kreta.DataAccessManual
{
    internal class OratervDAL : DataAccessBase, IOratervDal
    {
        public OratervDAL(DalHandler handler) : base(handler)
        { }

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

        public DataSet GetOraterv(OratervPCO pco)
        {
            var parameters = new List<CommandParameter>();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(@"SELECT ID as ID
                                                    ,ID as OratervId
                                                    ,ot.C_EVFOLYAM as EvFolyam
                                                    ,ot.C_NEV as Nev
                                                    ,ot.C_TANTERVID as TantervId
                                              FROM T_ORATERV_OSSZES ot
                                              WHERE
                                                  ot.TOROLT = 'F'
                                                    AND ot.C_TANEVID = :pTanevId");

            if (pco.TantervId.HasValue && pco.TantervId.Value > 0)
            {
                commandText.Append($" AND ot.C_TANTERVID = @{nameof(pco.TantervId)} ");
                parameters.Add(new CommandParameter(nameof(pco.TantervId), pco.TantervId.Value));
            }

            if (pco.EvfolyamID.HasValue && pco.EvfolyamID.Value > 0)
            {
                commandText.Append($" AND ot.C_EVFOLYAM = @{nameof(pco.EvfolyamID)} ");
                parameters.Add(new CommandParameter(nameof(pco.EvfolyamID), pco.EvfolyamID.Value));
            }

            if (!string.IsNullOrWhiteSpace(pco.Nev))
            {
                if (pco.IsValidacio)
                {
                    commandText.Append($" AND ot.C_NEV = @{nameof(pco.Nev)} ");
                }
                else
                {
                    commandText.Append($" AND ot.C_NEV LIKE '%' + @{nameof(pco.Nev)} + '%' ");
                }
                parameters.Add(new CommandParameter(nameof(pco.Nev), pco.Nev));
            }

            parameters.Add(new CommandParameter("pTanevId", pco.TanevId));

            return GetData(commandText.ToString(), parameters: parameters, dictionaryItemColumns: "EvFolyam");
        }

        public DataSet GetOratervTantargy(int ID, int tanevId)
        {
            var parameters = new List<CommandParameter>();
            parameters.Add(new CommandParameter("pTanevId", tanevId));

            string commandText = string.Format(@"
                                                 SELECT
                                                    ott.ID as ID
                                                    ,ott.ID as OratervTargyID
                                                    ,ott.C_EVESORASZAM as EvesSorszam
                                                    ,tt.C_NEV as Tantargy
                                                    ,ott.C_ORATERVID as OraTervId
                                                FROM T_ORATERVTARGY_OSSZES ott
                                                JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
                                                WHERE
                                                    ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND ott.C_TANEVID = :pTanevId
                                            ", ID);

            return GetData(commandText, parameters: parameters);
        }

        public DataSet GetOratervTantargyakById(int oratervID, int tanevId)
        {
            var parameters = new List<CommandParameter>();
            string commandText = string.Format(@"
                                                 SELECT
                                                    ott.ID as ID
                                                FROM T_ORATERVTARGY_OSSZES ott
                                                WHERE
                                                    ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND ott.C_TANEVID = {1}
                                            ", oratervID, tanevId);

            return GetData(commandText, parameters: parameters);
        }

        public DataSet GetTantargyak(int ID, int tanevId)
        {
            var parameters = new List<CommandParameter>();

            string commandText = string.Format(@"
                                                SELECT
                                                    tt.ID
                                                    ,tt.C_NEV
                                                FROM T_TANTARGY_OSSZES tt WHERE tt.TOROLT = 'F' AND tt.C_TANEVID = {1}
                                            EXCEPT
                                              SELECT
                                                tt.ID
                                                ,tt.C_NEV
                                              FROM T_ORATERVTARGY_OSSZES ott
                                              JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
                                              WHERE ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND tt.C_TANEVID = {1}
                                            ", ID, tanevId);

            return GetData(commandText, parameters: parameters);
        }

        public DataSet GetTantargyakModOraTervTantargy(int OraTervID, int OraTervTantargyID, int tanevId)
        {
            var parameters = new List<CommandParameter>();
            parameters.Add(new CommandParameter("pTanevId", tanevId));

            string commandText = string.Format(@"
                                                SELECT
                                                    tt.ID
                                                    ,tt.C_NEV
                                                FROM T_TANTARGY_OSSZES tt WHERE tt.TOROLT = 'F' AND tt.C_TANEVID = :pTanevId
                                            EXCEPT
                                              SELECT
                                                tt.ID
                                                ,tt.C_NEV
                                              FROM T_ORATERVTARGY_OSSZES ott
                                              JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
                                              WHERE ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND tt.C_TANEVID = :pTanevId
                                            UNION
                                              SELECT
                                                tt.ID
                                                ,tt.C_NEV
                                              FROM T_ORATERVTARGY_OSSZES ott
                                              JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
                                              WHERE ott.TOROLT = 'F' AND ott.ID = {1} AND tt.C_TANEVID = :pTanevId
                                            ", OraTervID, OraTervTantargyID);

            return GetData(commandText, parameters: parameters);
        }

        public int IfModifyOrDeleteOraTerv(int ID)
        {
            var parameters = new List<CommandParameter>();
            string commandText = string.Format(@"
                                              SELECT
                                                COUNT(*) as CNT
                                              FROM T_ORATERVTARGY ott
                                              WHERE ott.TOROLT = 'F' AND ott.C_ORATERVID = {0}
                                            ", ID);

            var CommandText = commandText;
            DataSet ds = GetData(CommandText, parameters: parameters);
            return int.Parse(ds.Tables[0].Rows[0]["CNT"].ToString());
        }

        public void Insert(IOraTervTargy entity)
        {
            ((OraTervTargy)entity).Insert();
            FollowUp(entity);
            DalHelper.Commit();
        }

        public void FullUpdate(IOraTervTargy entity)
        {
            ((OraTervTargy)entity).FullUpdate();
            FollowUp(entity);
            DalHelper.Commit();
        }

        public void Delete(IOraTervTargy entity)
        {
            ((OraTervTargy)entity).Delete();
            DalHelper.Commit();
        }

        public void Insert(IOraTerv entity)
        {
            ((OraTerv)entity).Insert();
            FollowUp(entity);
            DalHelper.Commit();
        }

        public void FullUpdate(IOraTerv entity)
        {
            ((OraTerv)entity).FullUpdate();
            FollowUp(entity);
            DalHelper.Commit();
        }

        public void Delete(IOraTerv entity)
        {
            ((OraTerv)entity).Delete();
            DalHelper.Commit();
        }
        public IOraTerv Get() => OraTerv.GiveAnInstance();
        public IOraTervTargy GetOraTervTargy() => OraTervTargy.GiveAnInstance();
        public IOraTerv Get(int id)
        {
            var entity = OraTerv.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }
        public IOraTervTargy GetOraTervTargy(int id)
        {
            var entity = OraTervTargy.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public void FollowUpOraterv(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int oratervId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = "uspFollowUpOraterv";
                command.Parameters.Add("intezmenyId", intezmenyId);
                command.Parameters.Add("aktTanevId", aktTanevId);
                command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
                command.Parameters.Add("oratervId", oratervId);

                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();
                DalHelper.Commit();

            }
        }

        public void FollowUpOratervTargy(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int oratervTargyId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = "uspFollowUpOratervTargy";
                command.Parameters.Add("intezmenyId", intezmenyId);
                command.Parameters.Add("aktTanevId", aktTanevId);
                command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
                command.Parameters.Add("oratervTargyId", oratervTargyId);

                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public DataSet GetOratervDataSet(int tanevId)
        {
            using (var sdaCommand = new SDACommand())
            {
                string commandText = @"
                        SELECT
                           tt.ID AS TantervId
                          ,tt.C_NEV AS TantervNev
                          ,ot.ID AS Id
                          ,ot.C_NEV AS OratervNev
                          ,ot.C_EVFOLYAM AS EvfolyamId
                        FROM T_ORATERV_OSSZES ot
                          INNER JOIN T_TANTERV_OSSZES tt ON tt.ID = ot.C_TANTERVID AND tt.C_TANEVID = ot.C_TANEVID AND tt.TOROLT = 'F' 
                        WHERE ot.C_TANEVID=:pTanevId
                          AND ot.TOROLT = 'F'
                    ";

                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.Text;
                sdaCommand.CommandText = commandText;

                sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;

                var dataSet = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = sdaCommand;
                    adapter.Fill(dataSet);
                }

                DataTable dataTable = dataSet.Tables[0];
                SetDNAME(dataTable, "EvfolyamId");

                return dataSet;
            }
        }

        public DataSet GetOratervTantargyDataSet(int tanevId)
        {
            using (var sdaCommand = new SDACommand())
            {
                string commandText = @"
                        SELECT
                           ott.ID AS Id
                          ,ott.C_ORATERVID AS OratervId
                          ,ott.C_TANTARGYID AS TantargyId
                          ,ott.C_EVESORASZAM AS EvesOraszam
                          ,ot.C_NEV AS OratervNev
                          ,t.C_NEV AS TantargyNev
                        FROM T_ORATERVTARGY ott
                          INNER JOIN T_ORATERV_OSSZES ot ON ot.ID = ott.C_ORATERVID AND ot.C_TANEVID = ott.C_TANEVID AND ot.TOROLT = 'F' 
                          INNER JOIN T_TANTARGY_OSSZES t ON t.ID = ott.C_TANTARGYID AND t.C_TANEVID = ott.C_TANEVID AND t.TOROLT = 'F' 
                        WHERE ott.C_TANEVID=:pTanevId
                          AND ott.TOROLT = 'F'
                    ";

                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.Text;
                sdaCommand.CommandText = commandText;

                sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;

                var dataSet = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = sdaCommand;
                    adapter.Fill(dataSet);
                }

                return dataSet;
            }
        }
    }
}