common/Ui/Common.UI/Models/AuftragDbContext.cs

namespace Common.UI.Models
{
    using Common.UI.Infrastructure;
    using Common.Hardware.WaterMeter.eRegister.Models;

    using LaaPackages.SqlClient;

    using System.Collections.Generic;
    using System.IO;
    using System.Xml.Serialization;
    using System;

    internal class AuftragDbContext
    {
        private static readonly string connectionString = Appsettings.GetSetting("ConnectionString");
        private static readonly SqlConnection sqlConnection = SqlConnection.CreateSqlConnection(connectionString, SQLError);

        private static void SQLError(string error)
        {

        }

        internal class eRegister
        {
            internal static void Update(IEnumerable<EregisterState> states)
            {
                foreach (var state in states)
                {
                    sqlConnection
                        .CreateCommand($@"
                                UPDATE eRegister
                                   SET Bemerkung = ISNULL(er.Bemerkung, @{nameof(state.Bemerkung)})
                                     , BUP = ISNULL(@{nameof(state.BUP)}, er.BUP)
                                     , SEMI = ISNULL(@{nameof(state.SEMI)}, er.SEMI)
                                     , DEBUG = ISNULL(@{nameof(state.DEBUG)}, er.DEBUG)
                                     , SemiDebugDatum = ISNULL(@{nameof(state.SemiDebugDatum)}, er.SemiDebugDatum)
                                     , GeschlossenDatum = ISNULL(er.GeschlossenDatum, @{nameof(state.GeschlossenDatum)})
                                     , FunkadresseDatum = ISNULL(er.FunkadresseDatum, @{nameof(state.FunkadresseDatum)})
                                  FROM eRegister AS er
                                  JOIN AuftragPositionSerienNr AS aps
	                                ON er.Seriennummer = aps.SerienNr
                                 WHERE er.Seriennummer = @{nameof(state.Seriennummer)}
                                   AND aps.StatusFertigung = 30")
                        .SetParameter(nameof(state.Bemerkung), state.Bemerkung)
                        .SetParameter(nameof(state.BUP), state.BUP)
                        .SetParameter(nameof(state.SEMI), state.SEMI)
                        .SetParameter(nameof(state.DEBUG), state.DEBUG)
                        .SetParameter(nameof(state.SemiDebugDatum), state.SemiDebugDatum)
                        .SetParameter(nameof(state.GeschlossenDatum), state.GeschlossenDatum)
                        .SetParameter(nameof(state.FunkadresseDatum), state.FunkadresseDatum)
                        .SetParameter(nameof(state.Seriennummer), state.Seriennummer)
                        .ExecuteNonQuery();
                }
            }
        }

        internal class eRegister_History
        {
            internal static void Save(IEnumerable<EregisterHistory> histories)
            {
                foreach (var history in histories)
                {
                    sqlConnection
                        .CreateCommand($@"
                            INSERT INTO eRegister_History
                                 ( SerienNr
                                 , Adresse
                                 , TempAdresse
                                 , Telegrams)
                            VALUES (@{nameof(history.SerienNr)}
                                 ,  @{nameof(history.Adresse)}
                                 ,  @{nameof(history.TempAdresse)}
                                 ,  @{nameof(history.Telegrams)})")
                        .SetParameter(nameof(history.SerienNr), history.SerienNr)
                        .SetParameter(nameof(history.Adresse), history.Adresse)
                        .SetParameter(nameof(history.TempAdresse), history.TempAdresse)
                        .SetParameter(nameof(history.Telegrams), history.Telegrams.ToArray())
                        .ExecuteNonQuery();
                }
            }
        }

        internal class eRegister_Shared
        {
            internal static List<EregisterShared> LoadActions(int sharedId)
                => sqlConnection.LoadActions(sharedId);
        }

        internal class P2000Shared
        {
            internal static int FindSharedId(int psno, string action) 
                => sqlConnection.FindSharedId(psno, action);
        }

        internal static EregisterProgrammingParametersList LoadEregisterProgrammingParametersListForSharedId(int sharedId, bool completed = false)
        {
            var xmlList = sqlConnection
                .CreateCommand($@"
                    SELECT CAST((SELECT DISTINCT *
                                   FROM VieweRegisterProgrammingParameters
                                  WHERE SharedId = @{nameof(sharedId)} AND Completed = 0
                                    FOR XML PATH('{nameof(EregisterProgrammingParameters)}')
                                          , ROOT('{nameof(EregisterProgrammingParametersList)}')) AS XML)")
                .SetParameter(nameof(sharedId), sharedId)
                .FirstOrDefault(x => x.GetValue<string>(0));
            var xmlDeserialized = default(object);

            if (!string.IsNullOrWhiteSpace(xmlList))
            {
                var xmlSerializer = new XmlSerializer(typeof(EregisterProgrammingParametersList));
                var xmlReader = new StringReader(xmlList);

                try
                {
                    xmlDeserialized = xmlSerializer.Deserialize(xmlReader);
                }
                catch (Exception e)
                {
                    AppHost.NLogger.Error(e.ToString());
                }
            }

            return xmlDeserialized as EregisterProgrammingParametersList ?? new EregisterProgrammingParametersList();
        }
    }
}