using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
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()
    {

        Settings = new GlobalConfiguration<LogikalSettings>().Load();
        
        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,LogikalFinish,LogikalProfile,LogikalGasket,LogikalComponent, LogikalGlass, LogikalLabour>) },
            { LogikalMethod.BOM, typeof(LogikalBOMResponse<LogikalBOM,LogikalFinish,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()
    {
        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)
                    .Success<LogikalBOMResponse<
                        LogikalElevationDetail,
                        LogikalFinish,
                        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,
                        LogikalFinish,
                        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)
    {
        if (value == null || value is DBNull || value.GetType() != typeof(T))
            return default(T);
        return (T)value;
    }
    
    private double GetScale(LogikalMeasurement scale)
    {
        return scale == LogikalMeasurement.Metres
            ? 1.0 / 1000.0
            : 1.0;
    }

    private void ExtractBOMData(IEnumerable<LogikalElevationDetail> 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
                using (var _data = new SQLiteCommand(_connection))
                {
                    _data.CommandText = Settings.FinishSQL.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            List<LogikalFinish> finishes = new List<LogikalFinish>();
                            foreach (DataRow row in _dt.Rows)
                            {
                                var _finish = new LogikalFinish();
                                _finish.Code = CheckValue<string>(row[nameof(LogikalFinish.Code)]);
                                _finish.Description = CheckValue<string>(row[nameof(LogikalFinish.Description)]);
                                finishes.Add(_finish);
                            }

                            elevation.Finishes = finishes;
                        }

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

                // 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)]);
                                _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)]);
                                _profile.Length = CheckValue<double>(row[nameof(LogikalProfile.Length)]) *
                                                  GetScale(Settings.ProfileMeasurement);
                                profiles.Add(_profile);
                            }

                            elevation.Profiles = profiles;
                        }

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

                // 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)]);
                                _gasket.Description = CheckValue<string>(row[nameof(LogikalGasket.Description)]);
                                _gasket.Quantity = CheckValue<Int64>(row[nameof(LogikalGasket.Quantity)]);
                                _gasket.Cost = CheckValue<double>(row[nameof(LogikalGasket.Cost)]);
                                _gasket.Length = CheckValue<double>(row[nameof(LogikalGasket.Length)]) *
                                                 GetScale(Settings.GasketMeasurement);
                                gaskets.Add(_gasket);
                            }

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

                // 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)]);
                                _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)]);
                                components.Add(_component);
                            }

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

                }

                // 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)]);
                                _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 = CheckValue<double>(row[nameof(LogikalGlass.Height)]) *
                                                    GetScale(Settings.GlassMeasurement);
                                _glassitem.Width = CheckValue<double>(row[nameof(LogikalGlass.Width)]) *
                                                   GetScale(Settings.GlassMeasurement);
                                _glassitem.Treatment = CheckValue<string>(row[nameof(LogikalGlass.Treatment)]);
                                _glassitem.Location = CheckValue<string>(row[nameof(LogikalGlass.Location)]);
                                glass.Add(_glassitem);
                            }

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

                }

                // 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)]);
                                _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}");
                    }

                }
                
                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);
        }
    }


}