kreta/Kreta.DataAccessManual/AdatszolgaltatasokDAL.cs

using System;
using System.Data;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;
using Kreta.Enums;
using Kreta.Framework;
using Kreta.Framework.Util;
using Kreta.Resources;
using SDA.DataProvider;

namespace Kreta.DataAccessManual
{
    internal class AdatszolgaltatasokDAL : DataAccessBase, IAdatszolgaltatasokDal
    {
        private readonly string sp_GetESL_A03T01_1 = nameof(sp_GetESL_A03T01_1),
                sp_GetESL_A03T02_1 = nameof(sp_GetESL_A03T02_1),
                sp_GetESL_A03T03_1 = nameof(sp_GetESL_A03T03_1),
                sp_ExportEsl_A03T01_1 = nameof(sp_ExportEsl_A03T01_1);

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

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

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

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

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

                SetDNAME(dataSet.Tables[0], "CsoportTipus,Evfolyam");

                return dataSet;
            }
        }

        public int? GetEnaploAdatszolgaltatasId(int intezmenyId, int tanevId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetEnaploAdatszolgaltatasId";

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


                var id = command.ExecuteScalar();

                if (int.TryParse(id?.ToString(), out int result))
                {
                    return result;
                }

                return null;
            }

        }

        public int? GetPedagogusIKTAdatszolgaltatasId(int intezmenyId, int tanevId, int tanarId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetPedagogusIKTAdatszolgaltatasId";

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

                var id = command.ExecuteScalar();

                if (int.TryParse(id?.ToString(), out int result))
                {
                    return result;
                }

                return null;
            }
        }

        public DataSet GetIKTAdatok(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 = "uspGetIKTAdatok";

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pEszkozTipusProjektor", (int)EszkozKategoriaEnum.PROJEKTOR_mobil);

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

                return dataSet;
            }
        }

        public DataSet GetLemorzsolodasEslRiport(int tanevId, int? feladatEllatasiHelyId, bool isFelevi)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = sp_GetESL_A03T01_1;

                command.Parameters.Add(nameof(tanevId), tanevId);

                if (feladatEllatasiHelyId.HasValue)
                {
                    command.Parameters.Add(nameof(feladatEllatasiHelyId), feladatEllatasiHelyId);
                }
                else
                {
                    command.Parameters.Add(nameof(feladatEllatasiHelyId), DBNull.Value);
                }

                command.Parameters.Add(nameof(isFelevi), isFelevi ? 1 : 0);
                command.Parameters.Add("osztalyzatTipusTypeID", (int)GeneratedAdatszotarTipusEnum.OsztalyzatTipus);

                var ds = new DataSet();

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

                SetTableRowsFromResourceFile(typeof(AdatszolgaltatasokResource), ds.Tables[0], ds.Tables[0].Columns[0].ColumnName);

                return ds;
            }
        }

        public DataSet GetLemorzsolodasEslRiportExport(int intezmenyId, int tanevId, int? feladatEllatasiHelyId, bool isFelevi)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = sp_ExportEsl_A03T01_1;

                command.Parameters.Add(nameof(tanevId), tanevId);

                if (feladatEllatasiHelyId.HasValue)
                {
                    command.Parameters.Add(nameof(feladatEllatasiHelyId), feladatEllatasiHelyId);
                }
                else
                {
                    command.Parameters.Add(nameof(feladatEllatasiHelyId), DBNull.Value);
                }

                command.Parameters.Add(nameof(isFelevi), isFelevi ? 1 : 0);
                command.Parameters.Add("osztalyzatTipusTypeID", (int)GeneratedAdatszotarTipusEnum.OsztalyzatTipus);

                var ds = new DataSet();

                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(ds);
                }
                if (ds.Tables.Count > 0)
                {
                    SetTableRowsFromResourceFile(typeof(AdatszolgaltatasokResource), ds.Tables[0], ds.Tables[0].Columns[0].ColumnName);
                    ds.Tables[0].Columns["LeirasValue"].SetOrdinal(0);
                    ds.Tables[0].Columns["LeirasValue"].ColumnName = "Leírás";
                }
                return ds;
            }
        }

        public DataSet GetElegtelenEslRiport(int tanevId, int? feladatEllatasiHelyId, bool isFelevi)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = sp_GetESL_A03T02_1;

                command.Parameters.Add(nameof(tanevId), tanevId);

                if (feladatEllatasiHelyId.HasValue)
                {
                    command.Parameters.Add(nameof(feladatEllatasiHelyId), feladatEllatasiHelyId);
                }
                else
                {

                    command.Parameters.Add(nameof(feladatEllatasiHelyId), DBNull.Value);
                }
                command.Parameters.Add(nameof(isFelevi), isFelevi ? 1 : 0);
                command.Parameters.Add("ESLTantargykategoriaTypeId", (int)GeneratedAdatszotarTipusEnum.ESLTantargykategoria);
                command.Parameters.Add("osztalyzatTipusTypeID", (int)GeneratedAdatszotarTipusEnum.OsztalyzatTipus);

                var ds = new DataSet();

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

                return ds;
            }
        }

        public DataSet GetHittanOraszamList(int intezmenyId, int tanevId, int csoportTipus, DateTime hittanAdatszolgaltatasLetszamDatuma, DateTime startDate, DateTime endDate)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetHittanOraszamList";

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pCsoportTipus", csoportTipus);
                command.Parameters.Add("pHittanAdatszolgaltatasLetszamDatum", hittanAdatszolgaltatasLetszamDatuma);
                command.Parameters.Add("pStartDate", startDate);
                command.Parameters.Add("pEndDate", endDate);
                command.Parameters.Add("pOktNevKatTipus", (int)OktNevelesiKategoriaEnum.NevelesOktatas);

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

                return dataSet;
            }
        }

        public void SaveOrUpdateHittan(int intezmenyId, int tanevId, int felhasznaloId, int csoportId, int hittanTipusId, int darabszam, int letszam, DateTime startDate, DateTime endDate, bool forJanuar)
        {
            using (SDACommand command = UserContext.Instance.SDAConnection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pUserId", felhasznaloId);
                command.Parameters.Add("pCsoportId", csoportId);
                command.Parameters.Add("pHittanTipusId", hittanTipusId);
                command.Parameters.Add("pDarabszam", darabszam);
                command.Parameters.Add("pLetszam", letszam);
                command.Parameters.Add("pStartDate", startDate);
                command.Parameters.Add("pEndDate", endDate);
                command.Parameters.Add("pForJanuar", forJanuar);

                command.CommandText = "uspSaveOrUpdateHittan";
                command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public DataSet GetHasznaltEvfolyamokForEsl(int? feladatellatasiHelyId, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetHasznaltEvfolyamokForEsl";

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

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

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

                return dataSet;
            }
        }

        public DataSet GetHittanOraszam(int intezmenyId, int tanevId, int csoportTipus, DateTime letszamDatum, DateTime startDate, DateTime endDate)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetHittanOraszam";

                sdaCommand.Parameters.Add("pIntezmenyId", intezmenyId);
                sdaCommand.Parameters.Add("pTanevId", tanevId);
                sdaCommand.Parameters.Add("pCsoportTipus", csoportTipus);
                sdaCommand.Parameters.Add("pLetszamDatum", letszamDatum);
                sdaCommand.Parameters.Add("pStartDate", startDate);
                sdaCommand.Parameters.Add("pEndDate", endDate);

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

                SetDNAME(dataSet.Tables[0], "HittanTipus");

                return dataSet;
            }
        }

        public DataSet GetOkosmeroEnergetikaiAdat(int intezmenyId, int tanevId, int? feladatellatasiHely)
        {
            //TODO: DT: Majd a végleges DB kialakítás után figyelni hogy melyik paraméter kötlező és melyik nem
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetOkosmeroEnergetikaiAdat";

                sdaCommand.Parameters.Add("pIntezmenyId", intezmenyId);
                sdaCommand.Parameters.Add("pTanevId", tanevId);
                sdaCommand.Parameters.Add("pFeladatellatasiHely", feladatellatasiHely);

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

                return dataSet;
            }
        }
    }
}