prs/prs.desktop/Integrations/Logikal/LogikalClient.cs

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using System.Windows.Forms;
using Comal.Classes;
using H.Formatters;
using H.Pipes;
using InABox.Clients;
using InABox.Configuration;
using InABox.Core;
using InABox.Integration.Awg;
using InABox.Integration.Logikal;
using InABox.Wpf;
using InABox.WPF;
using Newtonsoft.Json;
using PRSDesktop.Integrations.Logikal;
using Exception = System.Exception;
using Process = System.Diagnostics.Process;

namespace PRSDesktop;

public class LogikalClient : IDisposable
{
    public static LogikalClient Instance { get; } = new LogikalClient();
    
    private readonly PipeClient<LogikalMessage> _client;
    
    private ConcurrentDictionary<Guid, ManualResetEventSlim> Events = new();
    private ConcurrentDictionary<Guid, LogikalMessage> Responses = new();

    private const int DefaultRequestTimeout = 5 * 60 * 1000; // 5 minutes
    
    public LogikalSettings Settings { get; private set; }
    
    private string? _lastDriveAccessed = null;

    public bool Ready { get; private set; } = false;

    private LogikalErrorResponse NOTLOGGEDIN = new LogikalErrorResponse()
    {
        Status = LogikalStatus.NotLoggedIn,
        Message = "Not Logged In"
    };
    
    private LogikalClient()
    {
        var checkfile = System.IO.Path.Combine(CoreUtils.GetPath(),"simulator.logikal");

        Settings = new GlobalConfiguration<LogikalSettings>().Load();

        if (!File.Exists(checkfile))
        {
            var _basedirectory =
                System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) ?? "";

            var _logikalapp = System.IO.Path.Combine(_basedirectory, "PRSLogikal", "PRSLogikal.exe");
            if (!File.Exists(_logikalapp))
                _logikalapp = @"C:\development\prs\prs.logikal\bin\Debug\prslogikal.exe";
            if (!File.Exists(_logikalapp))
            {
                MessageBox.Show("Unable to locate PRS/Logikal interface!");
                return;
            }

            var _info = new ProcessStartInfo(_logikalapp);
            _info.WindowStyle = ProcessWindowStyle.Minimized;
            Process.Start(_info);
        }

        _client = new PipeClient<LogikalMessage>("$logikal", formatter: new NewtonsoftJsonFormatter());
        
        _client.Connected += Client_Connected;
        _client.Disconnected += Client_Disconnected;
        _client.MessageReceived += Client_MessageReceived;
        _client.ExceptionOccurred += Client_ExceptionOccurred;

        _client.ConnectAsync();
    }
    
    public void Dispose()
    {
        _client.DisposeAsync().AsTask().Wait();
    }
    
    private void Client_Connected(object? sender, H.Pipes.Args.ConnectionEventArgs<LogikalMessage> e)
    {
        Logger.Send(LogType.Information, "", $"Connected to Pipe: {e.Connection.PipeName}");
        //Disconnected = false;
        // Here we will register a Licence "Hit" for the Logikal interface
    }

    private void Client_Disconnected(object? sender, H.Pipes.Args.ConnectionEventArgs<LogikalMessage> e)
    {
        Logger.Send(LogType.Information, "", $"Disconnected from Pipe: {e.Connection.PipeName}");
        foreach (var ev in Events)
        {
            Responses.TryAdd(ev.Key, LogikalMessage.Error("Disconnected"));
            ev.Value.Set();
        }
        //Disconnected = true;
    }
    
    private void Client_ExceptionOccurred(object? sender, H.Pipes.Args.ExceptionEventArgs e)
    {
        Logger.Send(LogType.Error, "", $"Exception occured: {e.Exception.Message}");
    }

    private static Dictionary<LogikalMethod, Type> _methods = new Dictionary<LogikalMethod, Type>()
        {
            { LogikalMethod.Connect, typeof(LogikalConnectResponse) },
            { LogikalMethod.Login, typeof(LogikalLoginResponse) },
            { LogikalMethod.Logout, typeof(LogikalLogoutResponse) },
            { LogikalMethod.Disconnect, typeof(LogikalDisconnectResponse) },
            { LogikalMethod.ProjectCentres, typeof(LogikalProjectCentresResponse<LogikalProjectCentre, LogikalProject>) },
            { LogikalMethod.Projects, typeof(LogikalProjectsResponse<LogikalProject>) },
            { LogikalMethod.Phases, typeof(LogikalPhasesResponse<LogikalPhase>) },
            { LogikalMethod.ElevationSummary, typeof(LogikalElevationSummaryResponse<LogikalElevationSummary>) },
            { LogikalMethod.ElevationDetail, typeof(LogikalElevationDetailResponse<LogikalElevationDetail,LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile,LogikalGasket,LogikalComponent, LogikalGlass, LogikalLabour>) },
            { LogikalMethod.BOM, typeof(LogikalBOMResponse<LogikalBOM, LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile,LogikalGasket,LogikalComponent, LogikalGlass, LogikalLabour>) },
            { LogikalMethod.Error, typeof(LogikalErrorResponse) },
        };


    private LogikalResponse FromMessage(LogikalMessage message)
    {
        try
        {
            LogikalResponse _result = null;
            if (_methods.TryGetValue(message.Method, out var _type))
            {
                _result = JsonConvert.DeserializeObject(message.Payload, _type) as LogikalResponse;
                if (_result != null)
                    return _result;
                return new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = $"Deserialize Failure: {message.Method}: {message.Payload}" };
            }
            else
                return new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = $"Invalid Message Method: {message.Method}: {message.Payload}" };
        }
        catch (Exception e)
        {
            return new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = $"Exception Deserializing Response: {e.Message}\n{e.StackTrace}" };
        }

    }
    
    public LogikalResponse Send(LogikalRequest request, int timeout = DefaultRequestTimeout)
    {
        var checkfile = System.IO.Path.Combine(CoreUtils.GetPath(),"simulator.logikal");
        var filename = System.IO.Path.Combine(CoreUtils.GetPath(), $"{request.Method()}.logikal");
        if (File.Exists(checkfile) && File.Exists(filename))
        {
            var dec = Serialization.Deserialize<LogikalMessage>(File.ReadAllText(filename));
            if (dec != null)
                return FromMessage(dec);
        }

        var message = new LogikalMessage()
        {
            ID = Guid.NewGuid(),
            Method = request.Method(),
            Payload = Serialization.Serialize(request),
        };
        var ev = Queue(message.ID);
        _client.WriteAsync(message);
        var result = GetResult(message.ID, ev, timeout);

        if (File.Exists(checkfile))
        {
            var enc = Serialization.Serialize(result);
            File.WriteAllText(filename, enc);
        }
        return FromMessage(result);
    }

    public ManualResetEventSlim Queue(Guid id)
    {
        var ev = new ManualResetEventSlim();
        Events[id] = ev;
        return ev;
    }

    public LogikalMessage GetResult(Guid id, ManualResetEventSlim ev, int timeout)
    {
        if (Responses.TryRemove(id, out var result))
        {
            Events.TryRemove(id, out ev);
            return result;
        }

        try
        {
            if (!ev.Wait(timeout))
            {
                return LogikalMessage.Error("Timeout");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
            
        Responses.TryRemove(id, out result);
        Events.TryRemove(id, out ev);
        return result ?? LogikalMessage.Error("Unknown");
    }

    private void Client_MessageReceived(object? sender, H.Pipes.Args.ConnectionMessageEventArgs<LogikalMessage?> e)
    {
        if (Events.TryGetValue(e.Message.ID, out var ev))
        {
            Responses[e.Message.ID] = e.Message;
            ev.Set();
        }
    }
    
    ~LogikalClient()
    {
        Dispose();
    }

    public LogikalResponse Initialize()
    {
        Settings = new GlobalConfiguration<LogikalSettings>().Load();
        
        var checkfile = System.IO.Path.Combine(CoreUtils.GetPath(),"simulator.logikal");
        if (File.Exists(checkfile))
            return new LogikalInitializeResponse() { Path = $@"(simulator)" };
        
        string? result = _lastDriveAccessed;
        
        var _processes = Process.GetProcesses()
            .Where(x=>x.ProcessName.ToLower().Equals("logikal")
                //&& Regex.IsMatch(x.MainModule?.FileName ?? "", @"^ofcas\\[A-Za-z]\\common\\bin\\logikal\.exe$", RegexOptions.IgnoreCase)
            ).ToArray();

        if (_processes.Length == 0)
            return new LogikalErrorResponse() { Status = LogikalStatus.NotRunning, Message = "Logikal is not running"};

        var drives = _processes
            .Select(x => x.MainModule?.FileName.ToLower()
                .Split([@"ofcas\"], StringSplitOptions.None)
                .LastOrDefault()?
                .Replace(@"\common\bin\logikal.exe", "")
                .ToUpper() ?? "C")
            .Distinct()
            .OrderBy(x=>x)
            .ToArray();

        if (drives.Length == 1)
            result = drives[0];
        else
        {
            Dictionary<string, string> instances = new Dictionary<string, string>();
            foreach (var drive in drives)
                instances.Add(drive, $@"{drive} Drive");

            DictionaryRadioEdit.Execute<string>(instances, "Select Database", null, ref result);
        }
        
        if (string.IsNullOrEmpty(result))
            return new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Cancelled by User"};

        if (!Equals(result, _lastDriveAccessed))
        {
            if (Ready)
            {
                Logout();
                Disconnect();
            }
            Ready = false;
        }

        _lastDriveAccessed = result;
        
        return new LogikalInitializeResponse() { Path = $@"{result}:\logikalstarter.exe" };
        
    }

    public LogikalResponse Connect(string path)
    {
        if (Ready)
            return new LogikalConnectResponse();
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Connecting To Logikal...", progress =>
        {
            Client.Save(new LogikalUsage(),"");
            try
            {
                _result = Send(new LogikalConnectRequest(path));
            }
            catch (Exception e)
            {
                _result =  new LogikalErrorResponse() { Status = LogikalStatus.CannotConnect, Message = e.Message };
            }
        });
        return _result;
        
        
    }

    public LogikalResponse Disconnect()
    {
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Disconnecting From Logikal...", progress =>
        {
            try
            {
                _result = Send(new LogikalDisconnectRequest())
                    .Success<LogikalLogoutResponse>(r => { Ready = false; });
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
        });
        return _result;
    }

    public LogikalResponse Login()
    {

        if (Ready)
            return new LogikalLoginResponse();
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Logging In...", progress =>
        {
            try
            {
                _result = Send(new LogikalLoginRequest())
                    .Success<LogikalLoginResponse>(r => { Ready = true; });
            }
            catch (Exception e)
            {
               _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
        
        });
        return _result;
    }

    public LogikalResponse Logout()
    {
        if (!Ready)
            return new LogikalLogoutResponse();
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Logging Out...", progress =>
        {   
            try
            {
                _result = Send(new LogikalLogoutRequest())
                    .Success<LogikalLogoutResponse>(r => { Ready = false; });
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
        });
        return _result;
    }
    
    public LogikalResponse GetProjectCentres()
    {

        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Project Centres...", progress =>
        { 
            try
            {
                _result = Send(new LogikalProjectCentresRequest());
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
        });
        return _result;

    }
    
    public LogikalResponse GetProjects(string jobnumber)
    {
        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Projects...", progress =>
        { 
            try
            {
                _result = Send(new LogikalProjectsRequest(jobnumber));
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
        });
        return _result;
    }

    public LogikalResponse GetProject(Guid projectid)
    {
       

        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Project...", progress =>
        { 
            try
            {
                _result = Send(new LogikalProjectRequest(projectid));
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
            
        });
        return _result;
    }

    public LogikalResponse GetPhases(Guid projectid)
    {
        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Project...", progress =>
        { 
            try
            {
                _result = Send(new LogikalPhasesRequest(projectid));
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
        });
        return _result;
    }
    
    public LogikalResponse GetElevationSummaries(Guid projectid, string phase)
    {
        
        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Elevations...", progress =>
        { 
            
            try
            {
                _result = Send(new LogikalElevationSummaryRequest(projectid, phase));
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
            
        });
        return _result;
        
    }
    
    public LogikalResponse GetBillOfMaterials(Guid projectid, Guid[] elevationids, bool excel, bool sqlite)
    {
    
        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Elevations...", progress =>
        { 
            
            try
            {
                var _request = new LogikalBOMRequest(projectid, elevationids);

                _result = Send(_request)
                    .Always(response => { })
                    .Error(response => { })
                    .Success<LogikalBOMResponse<LogikalBOM,LogikalGroup,LogikalStyle,LogikalSupplier,LogikalProfile,LogikalGasket,LogikalComponent,LogikalGlass,LogikalLabour>>(response =>
                    {
                        ExtractBOMData([response.BOM], true, excel);
                    });
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
            
        });
        return _result;

    }

    public LogikalResponse GetElevationDetails(Guid projectid, Guid[] elevationids, bool excel, bool sqlite)
    {
    
        if (!Ready)
            return NOTLOGGEDIN;
        
        LogikalResponse _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = "Unknown Error" };
        Progress.ShowModal("Retrieving Elevations...", progress =>
        { 
            try
            {
                var _request = new LogikalElevationDetailRequest(
                    projectid, 
                    elevationids,
                    Settings.DrawingFormat == Comal.Classes.LogikalDrawingFormat.DXF 
                        ? InABox.Integration.Logikal.LogikalDrawingFormat.DXF 
                        : InABox.Integration.Logikal.LogikalDrawingFormat.PNG,
                
                    Settings.DrawingView == Comal.Classes.LogikalDrawingView.Exterior
                        ? InABox.Integration.Logikal.LogikalDrawingView.Exterior
                        : InABox.Integration.Logikal.LogikalDrawingView.Interior,
                
                    Settings.DrawingType == Comal.Classes.LogikalDrawingType.Explosion
                        ? InABox.Integration.Logikal.LogikalDrawingType.Explosion
                        : Settings.DrawingType == Comal.Classes.LogikalDrawingType.Section
                            ? InABox.Integration.Logikal.LogikalDrawingType.Section
                            : Settings.DrawingType == Comal.Classes.LogikalDrawingType.Elevation
                                ? InABox.Integration.Logikal.LogikalDrawingType.Elevation
                                : Settings.DrawingType == Comal.Classes.LogikalDrawingType.ElevationWithSectionLines
                                    ? InABox.Integration.Logikal.LogikalDrawingType.ElevationWithSectionLines
                                    : InABox.Integration.Logikal.LogikalDrawingType.SectionLine
                        
                );

                _result = Send(_request)
                    .Success<LogikalElevationDetailResponse<
                        LogikalElevationDetail,
                        LogikalGroup,
                        LogikalStyle,
                        LogikalSupplier,
                        LogikalProfile,
                        LogikalGasket,
                        LogikalComponent,
                        LogikalGlass,
                        LogikalLabour>>(response =>
                    {
                        ExtractBOMData(response.Elevations, false, excel);
                    });
            }
            catch (Exception e)
            {
                _result = new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = e.Message };
            }
            
        });
        return _result;
    }
    
    private T? CheckValue<T>(object? value)
    {
        //var converter = TypeDescriptor.GetConverter(typeof(T));
        try
        {
            return (T?)Convert.ChangeType(value, typeof(T));
        }
        catch (Exception e)
        {
            return default(T);
        }
    }

    
    
    private double GetScale(string scale, double value)
    {
        var _model = new LogikalMeasurementModel() { Value = value };
        var _expression = new CoreExpression<LogikalMeasurementModel, double>(scale); 
        if(_expression.Evaluate(_model).Get(out var eval, out var e))
            return eval;
        Logger.Send(LogType.Information, "", $"Error in Formula: [{scale}] ({e.Message})");
        return 0F;
    }

    private void ExtractBOMData(IEnumerable<ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile,LogikalGasket,LogikalComponent,LogikalGlass,LogikalLabour>> elevations, bool optimised, bool includeexcel)
    {
        foreach (var elevation in elevations)
        {
            var file = Path.ChangeExtension(Path.Combine(Path.GetTempPath(), Path.GetTempFileName()), "sqlite3");
            File.WriteAllBytes(file,elevation.SQLiteData);
            
            var sb = new SQLiteConnectionStringBuilder();
            sb.DataSource = file;
            using (var _connection = new SQLiteConnection(sb.ToString()))
            {

                _connection.Open();

                
                // Get Finishes
                ExtractStyles(_connection, elevation);
                
                ExtractGroups(_connection, elevation);
                
                ExtractSuppliers(_connection, elevation);

                ExtractProfiles(optimised, _connection, elevation);

                ExtractGaskets(_connection, elevation);

                ExtractComponents(_connection, elevation);

                ExtractGlass(_connection, elevation);

                ExtractLabour(_connection, elevation);

                if (includeexcel)
                {
                    List<string> _tables = new List<string>();
                    using (var _master = new SQLiteCommand(_connection))
                    {
                        _master.CommandText = "select * from sqlite_master where type='table'";
                        using (var _reader = _master.ExecuteReader())
                        {
                            if (_reader.HasRows)
                            {
                                while (_reader.Read())
                                    _tables.Add(_reader.GetString(1));
                            }
                        }
                    }

                    DataSet _ds = new DataSet();
                    foreach (var _table in _tables)
                    {
                        using (var _data = new SQLiteCommand(_connection))
                        {
                            _data.CommandText = $"select * from {_table}";
                            using (var _reader = _data.ExecuteReader())
                            {
                                DataTable _dt = new DataTable(_table);
                                _ds.Tables.Add(_dt);
                                _dt.Load(_reader);
                            }
                        }
                    }

                    var excelApp = OfficeOpenXML.GetInstance();
                    using (var _buffer = excelApp.GetExcelStream(_ds, false))
                        elevation.ExcelData = _buffer.GetBuffer();

                    _connection.Close();
                    File.Delete(file);
                }
            }

            File.Delete(file);
        }
    }

    private void ExtractLabour(SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        // Get Labour
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = Settings.LabourSQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalLabour> labour = new List<LogikalLabour>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _labouritem = new LogikalLabour();

                        _labouritem.Code = CheckValue<string>(row[nameof(LogikalLabour.Code)])?.ToUpper() ?? "";
                        _labouritem.Description = CheckValue<string>(row[nameof(LogikalLabour.Description)]);
                        _labouritem.Quantity = CheckValue<double>(row[nameof(LogikalLabour.Quantity)]);
                        _labouritem.Cost = CheckValue<double>(row[nameof(LogikalLabour.Cost)]);
                        labour.Add(_labouritem);
                    }

                    elevation.Labour = labour;
                }
            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }

        }
    }

    private void ExtractGlass(SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        // Get Glass
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = Settings.GlassSQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalGlass> glass = new List<LogikalGlass>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _glassitem = new LogikalGlass();
                        _glassitem.Code = CheckValue<string>(row[nameof(LogikalGlass.Code)])?.ToUpper() ?? "";
                        _glassitem.Description = CheckValue<string>(row[nameof(LogikalGlass.Description)]);
                        _glassitem.Quantity = CheckValue<Int64>(row[nameof(LogikalGlass.Quantity)]);
                        _glassitem.Cost = CheckValue<double>(row[nameof(LogikalGlass.Cost)]);
                        _glassitem.Height =  GetScale(Settings.GlassMeasurement,CheckValue<double>(row[nameof(LogikalGlass.Height)]));
                        _glassitem.Width =  GetScale(Settings.GlassMeasurement,CheckValue<double>(row[nameof(LogikalGlass.Width)]));
                        _glassitem.Treatment = (CheckValue<string>(row[nameof(LogikalGlass.Treatment)]) ?? "").ToUpper();
                        _glassitem.Location = CheckValue<string>(row[nameof(LogikalGlass.Location)]) ?? "";
                        _glassitem.Group = (CheckValue<string>(row[nameof(LogikalGlass.Group)]) ?? "").ToUpper();
                        _glassitem.Supplier = (CheckValue<string>(row[nameof(LogikalGlass.Supplier)]) ?? "").ToUpper();
                        glass.Add(_glassitem);
                    }

                    elevation.Glass = glass;
                }
            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }

        }
    }

    private void ExtractComponents(SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        // Get Components
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = Settings.ComponentSQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalComponent> components = new List<LogikalComponent>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _component = new LogikalComponent();
                        _component.Code = CheckValue<string>(row[nameof(LogikalComponent.Code)])?.ToUpper() ?? "";
                        _component.Description = CheckValue<string>(row[nameof(LogikalComponent.Description)]);
                        _component.Quantity = CheckValue<double>(row[nameof(LogikalComponent.Quantity)]);
                        _component.Cost = CheckValue<double>(row[nameof(LogikalComponent.Cost)]);
                        _component.PackSize = CheckValue<double>(row[nameof(LogikalComponent.PackSize)]);
                        _component.Group = (CheckValue<string>(row[nameof(LogikalComponent.Group)]) ?? "").ToUpper();
                        _component.Supplier = (CheckValue<string>(row[nameof(LogikalComponent.Supplier)]) ?? "").ToUpper();
                        components.Add(_component);
                    }

                    elevation.Components = components;
                }
            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }

        }
    }

    private void ExtractGaskets(SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        // Get Gaskets
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = Settings.GasketSQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalGasket> gaskets = new List<LogikalGasket>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _gasket = new LogikalGasket();
                        _gasket.Code = CheckValue<string>(row[nameof(LogikalGasket.Code)])?.ToUpper() ?? "";
                        _gasket.Description = CheckValue<string>(row[nameof(LogikalGasket.Description)]);
                        _gasket.Quantity = CheckValue<double>(row[nameof(LogikalGasket.Quantity)]);
                        _gasket.Cost = CheckValue<double>(row[nameof(LogikalGasket.Cost)]);
                        _gasket.Length =  GetScale(Settings.GasketMeasurement, CheckValue<double>(row[nameof(LogikalGasket.Length)]));
                        _gasket.Group = (CheckValue<string>(row[nameof(LogikalGasket.Group)]) ?? "").ToUpper();
                        _gasket.Supplier = (CheckValue<string>(row[nameof(LogikalGasket.Supplier)]) ?? "").ToUpper();
                        gaskets.Add(_gasket);
                    }

                    elevation.Gaskets = gaskets;
                }
            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }
        }
    }

    private void ExtractProfiles(bool optimised, SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        // Get Profiles
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = optimised
                ? Settings.BillOfMaterialsProfileSQL.Replace('\n', ' ')
                : Settings.DesignProfileSQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalProfile> profiles = new List<LogikalProfile>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _profile = new LogikalProfile();
                        _profile.Code = CheckValue<string>(row[nameof(LogikalProfile.Code)])?.ToUpper() ?? "";
                        _profile.Description = CheckValue<string>(row[nameof(LogikalProfile.Description)]);
                        _profile.Quantity = CheckValue<Int64>(row[nameof(LogikalProfile.Quantity)]);
                        _profile.Cost = CheckValue<double>(row[nameof(LogikalProfile.Cost)]);
                        _profile.Finish = (CheckValue<string>(row[nameof(LogikalProfile.Finish)]) ?? "").ToUpper();
                        _profile.Length =  GetScale(Settings.ProfileMeasurement, CheckValue<double>(row[nameof(LogikalProfile.Length)]));
                        _profile.Group = (CheckValue<string>(row[nameof(LogikalProfile.Group)]) ?? "").ToUpper();
                        _profile.Supplier = (CheckValue<string>(row[nameof(LogikalProfile.Supplier)]) ?? "").ToUpper();
                        _profile.MillCost = CheckValue<double>(row[nameof(LogikalProfile.MillCost)]);
                        _profile.AnodizePerimeter = CheckValue<double>(row[nameof(LogikalProfile.AnodizePerimeter)]);
                        _profile.PaintPerimeter = CheckValue<double>(row[nameof(LogikalProfile.PaintPerimeter)]);
                        profiles.Add(_profile);
                    }

                    elevation.Profiles = profiles;
                }

            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }
        }
    }
    
    private void ExtractSuppliers(SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = LogikalSupplier.SQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalSupplier> _suppliers = new List<LogikalSupplier>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _supplier = new LogikalSupplier();
                        _supplier.Code = CheckValue<string>(row[nameof(LogikalSupplier.Code)])?.ToUpper() ?? "";
                        _supplier.Description = CheckValue<string>(row[nameof(LogikalSupplier.Description)]) ?? "";
                        _suppliers.Add(_supplier);
                    }

                    elevation.Suppliers = _suppliers;
                }

            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }
        }
    }

    private void ExtractGroups(SQLiteConnection _connection, ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour> elevation)
    {
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = Settings.GroupSQL.Replace('\n', ' ');
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalGroup> _groups = new List<LogikalGroup>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var _group = new LogikalGroup();
                        _group.Code = CheckValue<string>(row[nameof(LogikalGroup.Code)])?.ToUpper() ?? "";
                        _group.Description = CheckValue<string>(row[nameof(LogikalGroup.Description)]) ?? "";
                        _group.Parent = CheckValue<string>(row[nameof(LogikalGroup.Parent)])?.ToUpper() ?? "";
                        _groups.Add(_group);
                    }

                    elevation.Groups = _groups;
                }

            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }
        }
    }
    
    private void ExtractStyles(SQLiteConnection _connection,
            ILogikalBOM<LogikalGroup, LogikalStyle, LogikalSupplier, LogikalProfile, LogikalGasket, LogikalComponent,
                LogikalGlass, LogikalLabour> elevation)
    {
        using (var _data = new SQLiteCommand(_connection))
        {
            _data.CommandText = LogikalStyle.SQL;
            try
            {
                using (var _reader = _data.ExecuteReader())
                {
                    DataTable _dt = new DataTable();
                    _dt.Load(_reader);
                    List<LogikalStyle> finishes = new List<LogikalStyle>();
                    foreach (DataRow row in _dt.Rows)
                    {
                        var style = new LogikalStyle();
                        style.Code = CheckValue<string>(row[nameof(LogikalStyle.Code)])?.ToUpper() ?? "";
                        style.Description = CheckValue<string>(row[nameof(LogikalStyle.Description)]) ?? "";
                        style.Cost = CheckValue<double>(row[nameof(LogikalStyle.Cost)]);
                        style.StyleType = (AwgStyleType)CheckValue<int>(row[nameof(LogikalStyle.StyleType)]);
                        finishes.Add(style);
                    }

                    elevation.Styles = finishes;
                }

            }
            catch (Exception e)
            {
                throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
            }
        }
        
    }
    
}