kreta/Kreta.DataAccessManual/CsengetesiRendDAL.cs

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

namespace Kreta.DataAccessManual
{
    internal class CsengetesiRendDal : DataAccessBase, ICsengetesiRendDal
    {
        public CsengetesiRendDal(DalHandler handler, GridParameters parameters)
            : base(handler, parameters)
        {

        }

        public CsengetesiRendDal(DalHandler handler) : base(handler)
        {

        }

        public DataSet GetCsengetesiRendekByMukodesiHely(int tanevId, int? mukodesiHelyId = null, bool csakAktiv = false)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetCsengetesiRendekByMukodesiHely";

                sdaCommand.Parameters.Add("pTanevId", tanevId);
                sdaCommand.Parameters.Add("pMukodesiHelyId", mukodesiHelyId);
                sdaCommand.Parameters.Add("pCsakAktiv", csakAktiv);

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

                SetBoolFields(dataSet.Tables[0], "Aktiv");

                return dataSet;
            }
        }

        public void SetCsengetesirend(int csengetesiRendId, bool aktiv, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = "sp_SetAktivCsengetesiRend";
                command.Parameters.Add("CsengrendID", csengetesiRendId);
                command.Parameters.Add("Aktival", aktiv);
                command.Parameters.Add("TanevId", tanevId);
                command.CommandType = CommandType.StoredProcedure;

                command.ExecuteNonQuery();

                DalHelper.Commit();
            }
        }

        public DataSet GetCsengetesiRendek(DateTime kezdoDatum, DateTime vegDatum, int? osztalycsoportszuro, 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 = "uspGetCsengetesiRendek";
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pIntezmenyId", intezmenyId);

                if (osztalycsoportszuro.HasValue)
                {
                    command.Parameters.Add("pOsztalyCsoportId", osztalycsoportszuro.Value);
                }
                else
                {
                    command.Parameters.Add("pOsztalyCsoportId", DBNull.Value);
                }

                command.Parameters.Add("pKezdoDatum", kezdoDatum);
                command.Parameters.Add("pVegDatum", vegDatum);

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

                return dataSet;
            }
        }

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

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

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

                return dataSet;
            }
        }

        public ICsengetesiRend Get()
        {
            return CsengetesiRend.GiveAnInstance();
        }

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

        public void FullUpdate(ICsengetesiRend dto)
        {
            var entity = dto as CsengetesiRend;
            entity.FullUpdate();
            FollowUp(entity);
            DalHelper.Commit();
        }

        public void Update(ICsengetesiRend dto)
        {
            var entity = dto as CsengetesiRend;
            entity.Update();
            FollowUp(entity);
            DalHelper.Commit();
        }

        public void Insert(ICsengetesiRend dto)
        {
            var entity = dto as CsengetesiRend;
            entity.Insert();
            FollowUp(entity);
            dto.ID = entity.ID;
            DalHelper.Commit();
        }

        public void Delete(int id)
        {
            var entity = CsengetesiRend.GiveAnInstance();
            entity.LoadByID(id);

            var torlendok = new List<int>();
            foreach (var ora in entity.CsengetesiRendOra)
            {
                torlendok.Add(ora.ID);
            }

            this.DalHelper.CsengetesiRendOrak(GridParameters).CsengetesiRendOrakTorles(torlendok, entity.TanevId);

            entity.Delete();

            DalHelper.Commit();
        }

        public (int csengetesiRendId, int oraSzam) GetCsengetesiRendAndOraszam(int csengetesirendId, 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 = "uspGetCsengetesiRendAndOraszam";

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

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

                var csengetesiRendId = Convert.ToInt32(dataSet.Tables[0].Rows[0]["CsengetesiRendId"].ToString());
                var oraSzam = Convert.ToInt32(dataSet.Tables[0].Rows[0]["OraSzam"].ToString());

                return (csengetesiRendId, oraSzam);
            }
        }

        public DataSet GetCsengetesiRendEsOrak(string csengetesiRendId, int oraszamId, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetCsengetesiRendEsOrak";

                command.Parameters.Add("pCsengetesirendId", csengetesiRendId);
                command.Parameters.Add("pOraszamId", oraszamId);
                command.Parameters.Add("pTanevId", tanevId);

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

                return dataSet;
            }
        }

        /// INFO @DevKornel: Mobil használja
        public int GetAktivCsengetesiRendId(int tanevId, int intezmenyId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetAktivCsengetesiRendId";

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

                return (int.Parse(command.ExecuteScalar().ToString()));
            }
        }

        public bool IsCsengetesiRendNevExists(int id, string nev, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspIsCsengetesiRendNevExists";

                command.Parameters.Add("pId", id);
                command.Parameters.Add("pNev", nev);
                command.Parameters.Add("pTanevId", tanevId);

                var result = command.ExecuteScalar();
                if (result != null && result != System.DBNull.Value)
                    return true;

                return false;
            }
        }

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

        public (int minOraszam, int maxOraszam) GetMinMaxOraszam(int tanevId, int csengetesirendId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetMinMaxOraszam";

                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pCsengetesirendId", csengetesirendId);

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

                var minOraszam = dataSet.Tables[0].Rows[0].Field<int>("MinOraszam");
                var maxOraszam = dataSet.Tables[0].Rows[0].Field<int>("MaxOraszam");

                return (minOraszam, maxOraszam);
            }
        }
    }
}