kreta/Kreta.DataAccessManual/JelszoModositasLinkDal.cs

using System.Data;
using Kreta.Core;
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 JelszoModositasLinkDal : DataAccessBase, IJelszoModositasLinkDal
    {
        public JelszoModositasLinkDal(DalHandler handler, GridParameters parameters)
            : base(handler, parameters)
        {

        }

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

        }

        public IJelszoModositasLink Get()
        {
            return JelszoModositasLink.GiveAnInstance();
        }

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

        public int? GetId(string guid)
        {
            if (!string.IsNullOrWhiteSpace(guid))
            {
                using (var command = new SDACommand())
                {
                    command.Connection = UserContext.Instance.SDAConnection;
                    command.Transaction = UserContext.Instance.SDATransaction;
                    command.Parameters.Add(nameof(guid), guid);

                    command.CommandText = $@"
SELECT link.ID
FROM T_JELSZOMODOSITASLINK link
INNER JOIN T_TANEV tanev ON tanev.ID = link.C_TANEVID AND tanev.C_INTEZMENYID = link.C_INTEZMENYID AND tanev.C_AKTIV = 'T' AND tanev.TOROLT = 'F'
WHERE link.C_AZONOSITO = @{nameof(guid)}
  AND link.TOROLT = 'F'
";

                    var result = command.ExecuteScalar();
                    if (int.TryParse(result?.ToString(), out int res))
                    {
                        return res;
                    }
                }
            }

            return null;
        }

        public void FullUpdate(IJelszoModositasLink dto)
        {
            var entity = dto as JelszoModositasLink;
            entity.FullUpdate(true);
            DalHelper.Commit();
        }

        public void Update(IJelszoModositasLink dto)
        {
            var entity = dto as JelszoModositasLink;
            entity.FullUpdate();
            DalHelper.Commit();
        }

        public void Insert(IJelszoModositasLink dto)
        {
            var entity = dto as JelszoModositasLink;
            entity.Insert(true);

            dto.ID = entity.ID;
            DalHelper.Commit();
        }

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

            Delete(entity);
        }

        public void Delete(IJelszoModositasLink dto)
        {
            var entity = dto as JelszoModositasLink;

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

        public string GetGuidByGondviseloId(int gondviseloId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

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

                command.CommandText = $@"
SELECT link.C_AZONOSITO
FROM T_JELSZOMODOSITASLINK link
INNER JOIN T_TANEV tanev ON tanev.ID = link.C_TANEVID AND tanev.C_INTEZMENYID = link.C_INTEZMENYID AND tanev.C_AKTIV = 'T' AND tanev.TOROLT = 'F'
WHERE link.C_GONDVISELOID = @{nameof(gondviseloId)}
  AND link.TOROLT = 'F'
";

                var result = command.ExecuteScalar();

                return result?.ToString() ?? null;
            }
        }

        public void DeleteIfExists(int felhasznaloId, int? gondviseloId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.Parameters.Add(nameof(felhasznaloId), felhasznaloId);
                var gondviseloSzures = string.Empty;
                if (gondviseloId.IsEntityId())
                {
                    command.Parameters.Add(nameof(gondviseloId), gondviseloId);
                    gondviseloSzures = $" AND link.C_GONDVISELOID = @{nameof(gondviseloId)} ";
                }
                else
                {
                    gondviseloSzures = $" AND link.C_GONDVISELOID IS NULL ";
                }

                command.CommandText = $@"
UPDATE link
SET link.TOROLT = 'T'
FROM T_JELSZOMODOSITASLINK link
INNER JOIN T_TANEV tanev ON tanev.ID = link.C_TANEVID AND tanev.C_INTEZMENYID = link.C_INTEZMENYID AND tanev.C_AKTIV = 'T' AND tanev.TOROLT = 'F'
WHERE link.C_FELHASZNALOID = @{nameof(felhasznaloId)}
  AND link.TOROLT = 'F'
{gondviseloSzures}
";

                int numberOfUpdatedRows = command.ExecuteNonQuery();
            }
        }

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

                command.CommandText = "uspDeleteInvalidLinks";

                command.ExecuteNonQuery();

                DalHelper.Commit();
            }
        }
    }
}