prs/prs.logikal/LogikalServer.cs

using InABox.Integration.Logikal;
using PRSLogikal.OpenXML;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Ofcas.Lk.Api.Client.Core;
using Ofcas.Lk.Api.Client.Ui;
using Ofcas.Lk.Api.Shared;

namespace PRSLogikal
{

    public class LogikalLogArguments
    {
        public String Message { get; private set; }

        public LogikalLogArguments(string message)
        {
            Message = message;
        }
        
    }

    public delegate void LogikalLogEvent(object sender, LogikalLogArguments args);
    
    public class LogikalServer : IDisposable
    {
        public event LogikalLogEvent Log;
        private void DoLog(String message) => Log?.Invoke(this, new LogikalLogArguments(message));
        
        private IServiceProxyUiResult _proxy;
        private ICoreObjectResult<ILoginScopeUi> _login;

        public IntPtr WindowHandle { get; private set; }

        private static readonly LogikalErrorResponse NOTCONNECTED = new LogikalErrorResponse()
        {
            Status = LogikalStatus.Disconnected,
            Message = $"LogiKal is not connected"
        };
        
        private static readonly LogikalErrorResponse NOTLOGGEDIN = new LogikalErrorResponse()
        {
            Status = LogikalStatus.NotLoggedIn,
            Message = $"Not Logged In"
        };
        
        public LogikalServer(IntPtr windowHandle)
        {
            WindowHandle = windowHandle;
        }

        public LogikalResponse Connect(LogikalConnectRequest request)
        {
            if (_proxy != null)
                return new LogikalConnectResponse();

            // Check that LogiKal is actually running from the folder we have specified
            var _driveLetter = Path.GetPathRoot(request.Path)?.Split(':').FirstOrDefault()?.ToLower() ?? "c";
            var _processes = Process.GetProcessesByName("LogiKal");
            var _running = _processes.Any(x => x.MainModule?.FileName.ToLower().Contains($"{_driveLetter}\\common\\bin\\logikal.exe") == true);
            if (!_running)
            {
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.NotRunning,
                    Message = $"LogiKal is not running at [{request.Path}]"
                };
            }

            var _p = ServiceProxyUiFactory.CreateServiceProxy(request.Path, "ERP");
            var _status = _p.ServiceProxyUi.Start();
            if (_status.OperationCode != OperationCode.Accepted)
            {
               return new LogikalErrorResponse()
               {
                   Status = LogikalStatus.CannotConnect,
                   Message = $"Unable to connect to LogiKal at [{request.Path}]: {_status}"
               };
            }

            _proxy = _p;
            return new LogikalConnectResponse();
        }

        public LogikalResponse Disconnect()
        {
            if (_login != null)
                Logout();

            if (_proxy != null)
            {
                _proxy.ServiceProxyUi.Stop();
                _proxy.Dispose();
            }

            _proxy = null;
            return new LogikalDisconnectResponse();
        }

        private void DoOnDisconnecting()
        {
            
        }

        public LogikalResponse Login(LogikalLoginRequest request)
        {
            Dictionary<string, object> _parameters = new Dictionary<string, object>()
            {
                { WellKnownParameterKey.Login.ProgramMode, "erp" },
                { WellKnownParameterKey.Login.ApplicationHandle, WindowHandle },
                //{ WellKnownParameterKey.Login.UserName, username },
                //{ WellKnownParameterKey.Login.Password, password },
                { WellKnownParameterKey.Login.EnableEventSynchronization, true },

            };

            if (_proxy == null)
                return NOTCONNECTED;

            if (_login != null)
                return new LogikalLoginResponse();

            var _check = _proxy.ServiceProxyUi.CanLogin(_parameters);
            if (!_check.CanExecute)
            {
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.Restricted,
                    Message = $"Login not allowed: {_check}!"
                };
            }

            try
            {
                var _l = _proxy.ServiceProxyUi.Login(_parameters);
                if (_l.OperationCode != OperationCode.Accepted)
                {
                    _login = null;
                    return new LogikalErrorResponse()
                    {
                        Status = LogikalStatus.Failed,
                        Message = $"Login failed: {_l}"
                    };
                }
                else
                {
                    _login = _l;
                    return new LogikalLoginResponse();
                }
            }
            catch (Exception e)
            {
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.Error,
                    Message = $"{e.Message}\n{e.StackTrace}"
                };
            }
            
        }

        public LogikalResponse Logout()
        {
            if (_login != null)
                _login.Dispose();
            _login = null;
            return new LogikalLogoutResponse();
        }

        public bool IsLoggedIn() => _login != null;

        private void GetProjectCentres(ICoreObjectResult<IProjectCenterUi> center, List<LogikalProjectCentre> results)
        {

            var _projectresults = new List<LogikalProject>();
            IList<IBaseProjectInfo> _projects = center.CoreObject.ChildrenInfos;
            foreach (var _project in _projects)
            {
                var _summary = new LogikalProject();
                PopulateProject(_project, _summary);
                _projectresults.Add(_summary);
            }

            var _result = new LogikalProjectCentre()
            {
                ID = center.CoreObject.ProjectCenterContainer.Id,
                Name = center.CoreObject.Parent != null ? center.CoreObject.Info.DirectoryName : "Project Center",
                ParentID = center.CoreObject.Parent != null
                    ? center.CoreObject.Parent.ProjectCenterContainer.Id
                    : Guid.Empty,
                Projects = _projectresults.ToArray()
            };

            results.Add(_result);

            var _children = center.CoreObject.ProjectCenterContainer.GetChildren().CoreObjectResults;
            foreach (var _child in _children)          
                GetProjectCentres(_child, results); 
        }

        private void PopulateProject(IBaseProjectInfo source, ILogikalProject target)
        {
            target.ID = source.Guid;
            target.Title = source.Name;
            target.PersonInCharge = source.PersonInCharge;
            target.Path = source.Path;
            target.LastUpdated = source.LastChangedDateTime;
            target.Created = source.CreatedDateTime;
            target.JobNumber = source.AsProjectInfo().JobNumber;
            target.OfferNumber = source.AsProjectInfo().OfferNumber;
        }

        private List<LogikalProjectCentre> GetProjectCentres()
        {
            var _results = new List<LogikalProjectCentre>();
            var _info = _login.CoreObject.ProjectCenterInfos.FirstOrDefault(x => x.Type.Id == 0);
            if (_info != null)
            {
                var _center = _login.CoreObject.GetProjectCenter(_info);
                GetProjectCentres(_center, _results);
            }
            return _results;
        }

        public LogikalResponse GetProjectCentres(LogikalProjectCentresRequest request)
        {

            if (_proxy == null)
                return NOTCONNECTED;

            if (_login == null)
                return NOTLOGGEDIN;

            var _results = GetProjectCentres();
            return new LogikalProjectCentresResponse<LogikalProjectCentre,LogikalProject>() { ProjectCentres = _results.ToArray() };
        }

        public LogikalResponse GetProjects(LogikalProjectsRequest request)
        {

            if (_proxy == null)
                return NOTCONNECTED;

            if (_login == null)
                return NOTLOGGEDIN;

            List<LogikalProject> _results = new List<LogikalProject>();

            var _centres = GetProjectCentres();
            foreach (var _centre in _centres)
            {
                var _projects = _centre.Projects.Where(x => string.IsNullOrWhiteSpace(request.JobNumber) || string.Equals(x.JobNumber, request.JobNumber));
                _results.AddRange(_projects);
            }           
            return new LogikalProjectsResponse<LogikalProject>() { Projects = _results.ToArray() };
            
        }

        public LogikalResponse GetProject(LogikalProjectRequest request)
        {
            if (_proxy == null)
                return NOTCONNECTED;

            if (_login == null)
                return NOTLOGGEDIN;

            var _project = _login.CoreObject.GetProjectByGuid(request.ProjectID);
            if (_project == null)
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.InvalidProjectID,
                    Message = $"Cannot Load Project {request.ProjectID}"
                };

            var response = new LogikalProjectResponse<LogikalProject>();
            PopulateProject(_project.CoreObject.Info, response.Project);
            return response;
           
        }

        public LogikalResponse GetPhases(LogikalPhasesRequest request)
        {
            if (_proxy == null)
                return NOTCONNECTED;

            if (_login == null)
                return NOTLOGGEDIN;

            var _project = _login.CoreObject.GetProjectByGuid(request.ProjectID);
            if (_project == null)
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.InvalidProjectID,
                    Message = $"Cannot Load Project {request.ProjectID}"
                };

            List<LogikalPhase> _results = new List<LogikalPhase>();
            var _phases = _project.CoreObject.GetChildren().CoreObjectResults;
            foreach (ICoreObjectResult<IPhase> _phase in _phases)
            {
                var _result = new LogikalPhase()
                {
                    ID = _phase.CoreObject.Info.Name,
                    Title = string.IsNullOrWhiteSpace(_phase.CoreObject.Info.Name) ? "Default Phase" : _phase.CoreObject.Info.Name
                };
                _results.Add(_result);
            }
            return new LogikalPhasesResponse<LogikalPhase>() { Phases = _results.ToArray() };
        }

        public LogikalResponse GetElevationSummaries(LogikalElevationSummaryRequest request)
        {
            if (_proxy == null)
                return NOTCONNECTED;

            if (_login == null)
                return NOTLOGGEDIN;

            var _results = new List<LogikalElevationSummary>();

            var _project = _login.CoreObject.GetProjectByGuid(request.ProjectID);
            if (_project == null)
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.InvalidProjectID,
                    Message = $"Cannot Load Project {request.ProjectID}"
                };

            var _phases = _project.CoreObject.GetChildren().CoreObjectResults;
            var _phase = _phases.FirstOrDefault(x => x.CoreObject.Info.Name == request.Phase);
            if (_phase == null)
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.InvalidPhaseID,
                    Message = $"Cannot find phase [{request.Phase}] within project [{request.ProjectID}]"
                };
            
            var _elevations = _phase.CoreObject.GetChildren().CoreObjectResults;
            foreach (var _elevation in _elevations)
            {
                if (!_elevation.CoreObject.Info.IsInRecycleBin)
                {
                    var _result = new LogikalElevationSummary();
                    PopulateElevation(_elevation.CoreObject, _result);
                    _results.Add(_result);
                }
            }
            return new LogikalElevationSummaryResponse<LogikalElevationSummary>() { Elevations = _results.ToArray() };
        }

        private void PopulateElevation(IElevation source, ILogikalElevationSummary target)
        {
            target.ID = source.Info.Guid;
            target.Name = source.Info.Name;
            target.Description = source.Info.SystemDescription;
            target.Size = source.Info.Size;
            using (var ms = new MemoryStream())
            {
                IStreamResult thumbnail =
                   source.GetThumbnail(new Dictionary<string, object>() { });
                thumbnail.Stream.CopyTo(ms);
                target.Thumbnail = ms.GetBuffer();
            }
        }

        public LogikalResponse GetBillOfMaterials(LogikalBOMRequest request)
        {

            if (_proxy == null)
                return NOTCONNECTED;

            if (_login == null)
                return NOTLOGGEDIN;

            var _project = _login.CoreObject.GetProjectByGuid(request.ProjectID);
            if (_project == null)
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.InvalidProjectID,
                    Message = $"Cannot Load Project [{request.ProjectID}]"
                };

            var _elevations = new List<IElevationInfo>();

           
            if (request.ElevationIDs?.Any() == true)
            {
                var _phases = _project.CoreObject.GetChildren();
                foreach (var _phase in _phases.CoreObjectResults)
                    _elevations.AddRange(_phase.CoreObject.ChildrenInfos.Where(x => request.ElevationIDs.Contains(x.Guid)));
            }

            using (IReportItemsResult reportItemsResult = _project.CoreObject.GetReports())
            {
                if (reportItemsResult.OperationCode != OperationCode.Accepted)
                {
                    return new LogikalErrorResponse()
                    {
                        Status = LogikalStatus.Error,
                        Message = $"Cannot Get Reports for Project!"
                    };
                }

                // Filter available reports for the erp export report item
                IReportItem reportItem = reportItemsResult.ReportItems.First(rep =>
                    (rep.Id == WellKnownReports.Delivery.ErpExport) &&
                    (rep.Category.Id == WellKnownReports.Delivery.CategoryId));

                // Create parameters for erp export, export format is required, but always sqlite
                var exportParameters = new Dictionary<string, object>
                {
                    { WellKnownParameterKey.Project.Report.ExportFormat, "SQLite" },
                };


                // Check if report can be exported for the given parameters
                var operationInfo = _project.CoreObject.CanGetReport(reportItem, _elevations, exportParameters);
                if (!operationInfo.CanExecute)
                {
                    return new LogikalErrorResponse()
                    {
                        Status = LogikalStatus.Error,
                        Message = $"Cannot Get Erp Report for Project!"
                    };
                }

                // Run report creation asynchronously - begin method starts the operation in background task
                using (ISynchronizedOperationResult synchronizedOperationResult =
                    _project.CoreObject.BeginGetReport(reportItem, _elevations, exportParameters))
                {

                    var response = new LogikalBOMResponse<LogikalBOM, LogikalFinish, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour>();
                    // End method waits for the background operation to complete in separate task
                    using (IStreamResult streamResult = Task.Run<IStreamResult>(() =>
                        _project.CoreObject.EndGetReport(synchronizedOperationResult.SynchronizedOperation)).Result)
                    {
                        PopulateParts(request, response.BOM, streamResult);
                    }
                    return response;

                }
            }


        }

        public LogikalResponse GetElevationDetails(LogikalElevationDetailRequest detailRequest)
        {
            
            var _project = _login.CoreObject.GetProjectByGuid(detailRequest.ProjectID);
            if (_project == null)
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.InvalidProjectID,
                    Message = $"Cannot Load Project [{detailRequest.ProjectID}]"
                };

            Dictionary<Guid, ICoreObjectResult<IElevationUi>> elevations = new Dictionary<Guid, ICoreObjectResult<IElevationUi>>();
            var _phases = _project.CoreObject.GetChildren().CoreObjectResults;
            foreach (var id in detailRequest.IDs)
            {
                foreach (var _phase in _phases)
                {
                    var ui = _phase.CoreObject.GetChildren().CoreObjectResults
                        .FirstOrDefault(e => e.CoreObject.Info.Guid == id);
                    if (ui != null)
                        elevations[id] = ui;
                }
                if (!elevations.ContainsKey(id))
                {
                    return new LogikalErrorResponse()
                    {
                        Status = LogikalStatus.ElevationNotFound,
                        Message = $"Cannot find Elevation [{id}] within project [{detailRequest.ProjectID}]"
                    };
                }
            }
            
            try
            {

                List<LogikalElevationDetail> results = new List<LogikalElevationDetail>();
                var response = new LogikalElevationDetailResponse<LogikalElevationDetail, LogikalFinish, LogikalProfile, LogikalGasket, LogikalComponent, LogikalGlass, LogikalLabour>();
                foreach (var id in elevations.Keys)
                {
                    var _elevation = elevations[id];

                    LogikalElevationDetail newel = new LogikalElevationDetail();
                    
                    PopulateElevation(_elevation.CoreObject, newel);
                    
                    var dxf = ElevationDetails(detailRequest, _elevation, id, newel, detailRequest.DrawingFormat, detailRequest.DrawingView, detailRequest.DrawingType);
                    if (dxf != null)
                        return dxf;
                    
                    results.Add(newel);
                }
                response.Elevations = results;
                return response;

            }
            catch (Exception e)
            {
                return new LogikalErrorResponse() { Status = LogikalStatus.Error, Message = $"{e.Message}\n\n{e.StackTrace}" };
            }
        }

        private LogikalResponse ElevationDetails(LogikalElevationDetailRequest detailRequest, ICoreObjectResult<IElevationUi> _elevation, Guid id,
            LogikalElevationDetail newel, LogikalDrawingFormat format, LogikalDrawingView view, LogikalDrawingType type)
        {
            // Setup parameters for export of the elevation drawing
            var sectionDrawingParameters = new Dictionary<string, object>
            {
                { WellKnownParameterKey.Elevation.Drawing.Format, format == LogikalDrawingFormat.DXF ? ElevationDrawingFormat.DXF : ElevationDrawingFormat.PNG },
                { WellKnownParameterKey.Elevation.Drawing.View, view == LogikalDrawingView.Exterior ? View.Exterior : View.Interior },
                { WellKnownParameterKey.Elevation.Drawing.Type, type == LogikalDrawingType.Explosion
                    ? ElevationDrawingType.Explosion
                    : type == LogikalDrawingType.Section
                        ? ElevationDrawingType.Section
                        : type == LogikalDrawingType.Elevation
                            ? ElevationDrawingType.Elevation
                            : type == LogikalDrawingType.ElevationWithSectionLines
                                ? ElevationDrawingType.ElevationWithSectionLines
                                : ElevationDrawingType.SectionLine 
                },
                { WellKnownParameterKey.Elevation.Drawing.DxfVersion, DxfVersion.Acad2010 },
                { WellKnownParameterKey.Elevation.Drawing.ShowDescription, true },
                { WellKnownParameterKey.Elevation.Drawing.ShowDimensions, true },
                { WellKnownParameterKey.Elevation.Drawing.Scale, 1.0 },
            };

            // Check if the drawing can be exported for the elevation with the given parameters
            if (!_elevation.CoreObject.CanGetDrawing(sectionDrawingParameters).CanExecute)
            {
                return new LogikalErrorResponse()
                {
                    Status = LogikalStatus.Error,
                    Message = $"GetDrawing() not permitted for Elevation [{id}]"
                };
            }
                    

            // Generate drawing for the elevation with the given parameters
            using (IDrawingResult drawingResult = _elevation.CoreObject.GetDrawing(sectionDrawingParameters))
            {
                Stream exportStream = drawingResult.Stream;
                using (var _ms = new MemoryStream())
                {
                    exportStream.CopyTo(_ms);
                    newel.Drawing = _ms.GetBuffer();
                }
            }

            using (IStreamResult streamResult = _elevation.CoreObject.GetPartsList())
            {
                try
                {
                    PopulateParts(detailRequest, newel, streamResult);
                    //return response;
                }
                catch (Exception e)
                {
                   return new LogikalErrorResponse()
                        { Status = LogikalStatus.Error, Message = $"{e.Message}\n{e.StackTrace}" };
                }
            }

            return null;
        }

        private T CheckValue<T>(object value)
        {
            if (value == null || value is DBNull || value.GetType() != typeof(T))
                return default(T);
            return (T)value;
        }

        private void PopulateParts<TRequest, TBOM>(TRequest request, TBOM bom, IStreamResult stream)
            where TRequest : AbstractLogikalPartsRequest
            where TBOM : ILogikalBOM<LogikalFinish, LogikalProfile, LogikalGasket, LogikalComponent,LogikalGlass,LogikalLabour>
        {
            var _excelData = new byte[] { };
            var sqLiteData = new byte[] { };
            var _finishes = new List<LogikalFinish>(); 
            var _profiles = new List<LogikalProfile>();
            var _gaskets = new List<LogikalGasket>();
            var _components = new List<LogikalComponent>();
            var _glass = new List<LogikalGlass>();
            var _labour = new List<LogikalLabour>();
 
            var file = Path.ChangeExtension(Path.Combine(Path.GetTempPath(), Path.GetTempFileName()), "sqlite3");
            using (var fs = new FileStream(file, FileMode.OpenOrCreate))
                stream.Stream.CopyTo(fs);

            if (request.IncludeSqliteData)
                sqLiteData = File.ReadAllBytes(file);
            
            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 = request.FinishQuery.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            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);
                            }
                        }

                    }
                    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 = request.ProfileQuery.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            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)]);
                                _profiles.Add(_profile);

                            }
                        }

                    }
                    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 = request.GasketQuery.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            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)]);
                                _gaskets.Add(_gasket);

                            }
                        }
                    }
                    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 = request.ComponentQuery.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            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);
                            }
                        }
                    }
                    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 = request.GlassQuery.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            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)]);
                                _glassitem.Width = CheckValue<double>(row[nameof(LogikalGlass.Width)]);
                                _glassitem.Treatment = CheckValue<string>(row[nameof(LogikalGlass.Treatment)]);
                                _glassitem.Location = CheckValue<string>(row[nameof(LogikalGlass.Location)]);
                                _glass.Add(_glassitem);
                            }
                        }
                    }
                    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 = request.LabourQuery.Replace('\n', ' ');
                    try
                    {
                        using (var _reader = _data.ExecuteReader())
                        {
                            DataTable _dt = new DataTable();
                            _dt.Load(_reader);
                            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);
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        throw new Exception($"Error: {e.Message}\nQuery: {_data.CommandText}\nTrace: {e.StackTrace}");
                    }
                        
                }

                if (request.IncludeExcelData)
                {
                    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))
                        _excelData = _buffer.GetBuffer();

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

                bom.Finishes = _finishes.ToArray();
                bom.Profiles = _profiles.ToArray();
                bom.Gaskets = _gaskets.ToArray();
                bom.Components = _components.ToArray();
                bom.Glass = _glass.ToArray();
                bom.Labour = _labour.ToArray();
                bom.ExcelData = _excelData;
                bom.SQLiteData = sqLiteData;

            }
        }

        public void Dispose()
        {
            Disconnect();
        }
    }
}