kreta/Kreta.DataAccessManual/SapTavolletDal.cs

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

namespace Kreta.DataAccessManual
{
    internal class SapTavolletDal : DataAccessBase, ISapTavolletDal
    {
        private static readonly string SqlDateFormat = "yyyyMMdd 00:00:00";
        public SapTavolletDal(DalHandler handler) : base(handler)
        {

        }

        #region BaseCRUD

        public void Delete(ISAPTavollet dto)
        {
            var entity = dto as SAPTavollet;
            entity.Delete(false);
            DalHelper.Commit();
        }

        public void Delete(int id)
        {
            var entity = SAPTavollet.GiveAnInstance();
            entity.LoadByID(id);
            entity.Delete();
            DalHelper.Commit();
        }

        public ISAPTavollet Get()
        {
            return SAPTavollet.GiveAnInstance();
        }

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

        public void Insert(ISAPTavollet dto)
        {
            var entity = dto as SAPTavollet;
            entity.Insert();
            dto.ID = entity.ID;
            DalHelper.Commit();
        }

        public void Update(ISAPTavollet dto)
        {
            var entity = dto as SAPTavollet;
            entity.Update();
            DalHelper.Commit();
        }

        #endregion

        public DataSet GetSAPTavolletDataSet(int? alkalmazottId, TavolletSearchPco pco)
        {
            var command = new StringBuilder();
            var parameters = new List<CommandParameter>()
            {
                new CommandParameter("pAlkalmazottId", (object)alkalmazottId ?? DBNull.Value),
                new CommandParameter("pTanevId", pco.TanevId),
                new CommandParameter("pIntezmenyId", pco.IntezmenyId),
            };

            for (int i = 0; i < pco.NaptariEv.Length; i++)
            {
                parameters.Add(new CommandParameter($"pKezdete{i}", new DateTime(pco.NaptariEv[i], 1, 1).ToString(SqlDateFormat)));
                parameters.Add(new CommandParameter($"pKezdeteVege{i}", new DateTime(pco.NaptariEv[i], 1, 1).AddYears(1).ToString(SqlDateFormat))); //az éven átnyúlás miatt a kezdet a lényeg

                if (i > 0)
                {
                    command.AppendLine(@"
                    UNION ALL");
                }

                command.Append($@"
                SELECT
                   t.C_ALKALMAZOTTID as AlkalmazottId
                  ,a.C_SZTSZKOD as SzTSzKod
                  ,t.C_INTEZMENYID as IntezmenyId
                  ,t.C_TAVOLLETKEZDETE as TavolletKezdete
                  ,t.C_TAVOLLETVEGE as TavolletVege
                  ,t.C_TAVOLLETIDOTARTAMANAP as TavolletIdotartamNap
                  ,t.C_TAVOLLETIDOTARTAMAORA as TavolletIdotartamOra
                  ,t.C_TAVOLLETTIPUSID as TavolletTipusId
                FROM T_SAPTAVOLLET_OSSZES t
                  INNER JOIN T_ALKALMAZOTT_OSSZES a ON (a.ID = t.C_ALKALMAZOTTID OR a.ELOZOTANEVIREKORDID = t.C_ALKALMAZOTTID) AND a.TOROLT = 'F'
                WHERE t.TOROLT = 'F'
                  AND (a.ID = :pAlkalmazottId OR :pAlkalmazottId IS NULL) AND a.C_ALTANEVID = :pTanevId
                  AND t.C_INTEZMENYID = :pIntezmenyId
                  AND t.C_TAVOLLETKEZDETE >= :pKezdete{i} AND t.C_TAVOLLETKEZDETE < :pKezdeteVege{i}
                ");
            }
            return GetData(command.ToString(), parameters);
        }

        public void SyncSapTavollet(Dictionary<string, List<SAPTavolletPco>> tavolletListDict, TavolletSearchPco pco)
        {
            //régi rekordok fizikai törlése
            //TODO: pKezdete és pVege pontosítása, ha szükséges?!
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                var alkalmazottList = new List<int>();
                var elozoTaneviList = new List<int>();
                foreach (var item in tavolletListDict)
                {
                    if (item.Value.Count > 0)
                    {
                        alkalmazottList.Add(item.Value[0].AlkalmazottId);
                        if (item.Value[0].ElozoTaneviAlkalmazottId.HasValue)
                            elozoTaneviList.Add(item.Value[0].ElozoTaneviAlkalmazottId.Value);
                    }
                }
                command.Parameters.Add("pAlkalmazottIds", SqlLogic.ParseListToParameter(alkalmazottList));
                command.Parameters.Add("pElozoTaneviAlkalmazottIds", (elozoTaneviList.Count > 0 ? SqlLogic.ParseListToParameter(elozoTaneviList) : DBNull.Value));

                command.Parameters.Add("pIntezmenyId", pco.IntezmenyId);
                var commandText = new StringBuilder();
                commandText.Append(@"
                    DELETE FROM T_SAPTAVOLLET_OSSZES
                    WHERE
                        (C_ALKALMAZOTTID IN (SELECT value FROM string_split(:pAlkalmazottIds, ','))
                            OR (:pElozoTaneviAlkalmazottIds IS NOT NULL AND C_ALKALMAZOTTID IN (SELECT value FROM string_split(:pElozoTaneviAlkalmazottIds, ','))))
                        AND C_INTEZMENYID = :pIntezmenyId
                        AND (
                ");
                for (int i = 0; i < pco.NaptariEv.Length; i++)
                {
                    if (i > 0)
                    {
                        commandText.AppendLine(@"
                            OR ");
                    }
                    command.Parameters.Add($"pKezdete{i}", new DateTime(pco.NaptariEv[i], 1, 1).ToString(SqlDateFormat));
                    command.Parameters.Add($"pVege{i}", new DateTime(pco.NaptariEv[i], 1, 1).AddYears(1).ToString(SqlDateFormat));
                    commandText.AppendLine($@"
                        (C_TAVOLLETKEZDETE >= :pKezdete{i}
                        AND C_TAVOLLETVEGE < :pVege{i})");
                }
                commandText.Append(")");
                command.CommandText = commandText.ToString();
                command.ExecuteNonQuery();
            }

            //friss rekordok beszúrása
            foreach (var alkalmazott in tavolletListDict)
            {
                ISAPTavollet tavollet;
                foreach (var item in alkalmazott.Value)
                {
                    tavollet = SAPTavollet.GiveAnInstance();
                    tavollet.AlkalmazottId = item.AlkalmazottId;
                    tavollet.TanevId = item.TanevId;
                    tavollet.IntezmenyId = item.IntezmenyId;
                    tavollet.TavolletKezdete = item.TavolletKezdete;
                    tavollet.TavolletVege = item.TavolletVege;
                    tavollet.TavolletIdotartamaNap = item.TavolletIdotartamNap;
                    tavollet.TavolletIdotartamaOra = item.TavolletIdotartamOra;
                    tavollet.TavolletTipusId = item.TavolletTipusId;

                    Insert(tavollet);
                }
            }
        }

        public DataSet GetTavolletTipusDataSet(int tanevId, int intezmenyId)
        {
            var parameters = new List<CommandParameter>()
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pIntezmenyId", intezmenyId)
            };

            var commandText = new StringBuilder($@"
                SELECT DISTINCT
                  tt.ID         AS 'ID'
                  ,tt.C_SAPKOD  AS 'SapCode'
                  ,d.C_NAME     AS 'Megnevezes'
                FROM T_TAVOLLETTIPUS_OSSZES tt
                  INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.ID=tt.ID
                WHERE
                  tt.TOROLT = 'F'
                  AND tt.C_ALTANEVID = :pTanevId
                  AND tt.C_ALINTEZMENYID = :pIntezmenyId
            ");

            return GetData(commandText.ToString(), parameters);
        }
    }
}