kreta/Kreta.DataAccessManual/GondviseloDAL.cs

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

namespace Kreta.DataAccessManual
{
    internal class GondviseloDal : DataAccessBase, IGondviseloDal
    {
        public void Delete(int id)
        {
            var entity = Gondviselo.GiveAnInstance();
            entity.LoadByID(id);

            Delete(entity);
        }

        public void Delete(IGondviselo dto)
        {
            var entity = dto as Gondviselo;

            entity.DashboardUzenetFelhasznalo.DeleteAll();
            entity.Email.DeleteAll();
            entity.Telefon.DeleteAll();
            entity.FelhasznaloBelepes.DeleteAll();
            entity.Cim.DeleteAll();
            entity.OldalLatogatottsag.DeleteAll();
            entity.FelhasznaloBelepesTortenet.DeleteAll();
            entity.JelszoModositasLink.DeleteAll();
            entity.EszkozIgenyles.DeleteAll();
            entity.Delete();

            DalHelper.Commit();
        }

        public void FullUpdate(IGondviselo dto)
        {
            var entity = dto as Gondviselo;
            entity.FullUpdate();

            DalHelper.Commit();
        }

        public void Update(IGondviselo dto)
        {
            var entity = dto as Gondviselo;
            entity.Update();

            DalHelper.Commit();
        }

        public IGondviselo Get()
        {
            return Gondviselo.GiveAnInstance();
        }

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

        public IGondviselo GetSpecific(string condition, int tanevId)
        {
            var entity = Gondviselo.LoadWithFilter(condition + $" AND C_TANEVID = {tanevId}").FirstOrDefault();
            return entity;
        }

        public void Insert(IGondviselo dto)
        {
            var entity = dto as Gondviselo;
            entity.Insert();
            DalHelper.Felhasznalo().UpdateAllEgyediAzonosito();
            dto.ID = entity.ID;
            DalHelper.Commit();
        }

        public void FollowUpGondviselo(int intezmenyId, int tanevId, int kovTanevId, int gondviseloId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "uspFollowUpGondviselo";

                command.Parameters.Add("intezmenyId", intezmenyId);
                command.Parameters.Add("tanevId", tanevId);
                command.Parameters.Add("kovetkezoTanevId", kovTanevId);
                command.Parameters.Add("gondviseloId", gondviseloId);

                command.ExecuteNonQuery();

                DalHelper.Commit();
            }
        }

        public void FollowUpGondviseloCim(int intezmenyId, int tanevId, int kovTanevId, int gondviseloId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "uspFollowUpGondviseloCim";

                command.Parameters.Add("intezmenyId", intezmenyId);
                command.Parameters.Add("tanevId", tanevId);
                command.Parameters.Add("kovetkezoTanevId", kovTanevId);
                command.Parameters.Add("gondviseloId", gondviseloId);

                command.ExecuteNonQuery();

                DalHelper.Commit();
            }
        }

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

        public GondviseloDal(DalHandler handler, GridParameters gridParameters) : base(handler, gridParameters)
        {
        }

        public DataSet GetTanuloGondviseloExportData(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 = "sp_GetTanuloGondviseloExportData";

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

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

                return ds;
            }
        }

        public void UpdateTorvenyesKepviselo(int tanuloId)
        {
            var commandText = $@"
                UPDATE
                    T_GONDVISELO
                SET
                    C_ISTORVENYESKEPVISELO = 'F'
                WHERE
                    TOROLT = 'F' AND C_TANULOID = :pTanuloId
            ";

            using (SDACommand command = DAUtil.CreateCommand(commandText))
            {
                command.Parameters.Add("pTanuloId", tanuloId);
                command.ExecuteNonQuery();
            }
            DalHelper.Commit();
        }

        public DataSet GetGondviseloDataSet(int tanevId)
        {
            using (var sdaCommand = new SDACommand())
            {
                string commandText = @"
                    SELECT
                       g.ID AS Id
                      ,g.C_NEV AS Nev
                      ,g.C_ROKONSAGFOKA AS RokonsagiFokId
                      ,g.C_ISTORVENYESKEPVISELO AS TorvenyesKepviselo
                      ,g.C_IMPORTALT AS Importalt

                      ,fh.ID AS FelhasznaloId
                      ,fh.C_NEVSORREND AS FelhasznaloNevsorrend
                      ,fh.C_ELOTAG AS FelhasznaloElotag
                      ,fh.C_VEZETEKNEV AS FelhasznaloVezeteknev
                      ,fh.C_UTONEV AS FelhasznaloKeresztnev
                      ,fh.C_SZULETESIHELY AS FelhasznaloSzuletesiHely
                      ,fh.C_SZULETESIDATUM AS FelhasznaloSzuletesiIdo
                      ,fh.C_OKTATASIAZONOSITO AS FelhasznaloOktatasiAzonosito

                      ,c.ID AS CimId
                      ,c.C_ALAPERTELMEZETT AS Alapertelmezett
                      ,c.C_ORSZAG AS OrszagId
                      ,c.C_CIMTIPUSA AS CimTipusId
                      ,c.C_IRANYITOSZAM AS CimIranyitoszam
                      ,c.C_VAROS AS Helyseg
                      ,c.C_KOZTERULET AS Kozterulet
                      ,c.C_KOZTERULETJELLEGENEV AS KozteruletJelleg
                      ,c.C_HAZSZAM AS Hazszam
                      ,c.C_EMELET AS Emelet
                      ,c.C_AJTO AS Ajto

                      ,e.ID AS EmailId
                      ,e.C_EMAILTIPUSA AS EmailTipusId
                      ,e.C_EMAILCIM AS EmailEmailCim
                      ,e.C_ALAPERTELMEZETT AS EmailAlapertelmezett

                      ,tf.ID AS TelefonId
                      ,tf.C_TELEFONTIPUSA AS TelefonTipusId
                      ,tf.C_TELEFONSZAM AS TelefonTelefonszam
                      ,tf.C_ALAPERTELMEZETT AS TelefonAlapertelmezett
                    FROM T_GONDVISELO_OSSZES g
                      INNER JOIN T_TANULO_OSSZES dk ON dk.ID = g.C_TANULOID AND dk.C_ALTANEVID = g.C_TANEVID AND dk.TOROLT = 'F'
                      INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = dk.ID AND fh.C_TANEVID = g.C_TANEVID AND fh.TOROLT = 'F'
                      LEFT JOIN T_TELEFON_OSSZES tf ON tf.C_GONDVISELOID = g.ID AND tf.C_TANEVID = g.C_TANEVID AND tf.TOROLT = 'F' AND tf.C_ALAPERTELMEZETT = 'T'
                      LEFT JOIN T_EMAIL_OSSZES e ON e.C_GONDVISELOID = g.ID AND e.C_TANEVID = g.C_TANEVID AND e.TOROLT = 'F' AND e.C_ALAPERTELMEZETT = 'T'
                      LEFT JOIN T_CIM_OSSZES c ON c.C_GONDVISELOID = g.ID AND c.C_TANEVID = g.C_TANEVID AND c.TOROLT = 'F'
                    WHERE g.C_TANEVID = :pTanevId
                      AND g.TOROLT = 'F'
                    ORDER BY g.C_NEV
                ";

                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, "RokonsagiFokId,TelefonTipusId,EmailTipusId,OrszagId,CimTipusId");
                DataTable result = SortingAndPaging(dataTable, GridParameters);

                return result.AsDataSet();
            }
        }

        public void UpdateCOVIDFlag()
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "uspUpdateCOVIDFlag";

                command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public DataSet GetCovidBejelentettekDataSet(int tanevId)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;

                sdaCommand.CommandText = "uspGetCovidBejelentett";

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

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

                return dataSet;
            }
        }

        public bool IsGondviseloTanuloinakEvfolyamTipusaLetezik(int gondviseloId, int intezmenyId, int tanevId, IEnumerable<int> evfolyamTipusIdList)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;

                sdaCommand.CommandText = "uspGondviseloTanuloinakEvfolyamTipusaLetezik";

                sdaCommand.Parameters.Add("pIntezmenyId", intezmenyId);
                sdaCommand.Parameters.Add("pTanevId", tanevId);
                sdaCommand.Parameters.Add("pGondviseloId", gondviseloId);
                sdaCommand.Parameters.Add("pEvfolyamtipusIdList", SqlLogic.ParseListToParameter(evfolyamTipusIdList.ToList()));

                int cnt = Convert.ToInt32(sdaCommand.ExecuteScalar());
                return cnt > 0;
            }
        }
    }
}