prs/prs.desktop/Integrations/V6/V6Client.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using Comal.Classes;
using InABox.Clients;
using InABox.Configuration;
using InABox.Core;
using InABox.Dxf;
using PRSDesktop.Integrations.V6;

namespace PRSDesktop;

public class V6Client : MicrosoftSQLClient
{
    public V6Settings Settings { get; private set; }

    public V6Client()
    {
        Client.Save(new V6Usage(),"");
        Settings = new GlobalConfiguration<V6Settings>().Load();
    }

    protected override string GetConnectionString() => Settings.AsConnectionString();

    public V6Project? GetProject(string? reference)
    {
        V6Project? project = null;
        if (V6Project.ParseReference(reference, out int number, out string variation))
        {
            var _query = CheckQuery(Settings.QuoteSQL, V6Project.SQL, number, variation);
            var _table = Query(_query,"quote");
            return _table.Rows.Count > 0
                ? DataRowToProject(_table.Rows[0])
                : null;
        }
        return project;
    }
    
    public IEnumerable<V6Project> GetProjects()
    {
        try
        {
            List<V6Project> _projects = new();
            if (!IsConnected)
                return _projects;
        
            var _quotes = Query(Settings.QuoteSQL,"quotes");
        
            foreach (DataRow _row in _quotes.Rows)
                _projects.Add(DataRowToProject(_row));
        
            return _projects;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

    private V6Project DataRowToProject(DataRow row)
    {
        var _quote = new V6Project()
        {
            ID = GetInteger(row,nameof(V6Project.ID)),
            Revision = GetInteger(row, nameof(V6Project.Revision)),
            Number = GetInteger(row, nameof(V6Project.Number)),
            Variation = GetString(row,nameof(V6Project.Variation)),
            ClientID = GetString(row,nameof(V6Project.ClientID)),
            ClientName = GetString(row,nameof(V6Project.ClientName)),
            Title = GetString(row,nameof(V6Project.Title)),
            SellPrice = GetDouble(row,nameof(V6Project.SellPrice)),
            Street = GetString(row,nameof(V6Project.Street)),
            City = GetString(row,nameof(V6Project.City)),
            State = GetString(row,nameof(V6Project.State)),
            PostCode = GetString(row,nameof(V6Project.PostCode)),
        };

        return _quote;
    }
    
    private static string CheckQuery(string query, string fallback, int number, string variation, IEnumerable<int>? quoteitems = null)
    {
        string _basefilter = quoteitems == null
            ? $"q.quote_num = '{number}' and q.quote_num_suff = '{variation}' and q.quote_vers = (select max(quote_vers) from quote where quote_id = q.quote_id) "
            : $"qi.quote_item_id in ({string.Join(",",quoteitems)}) ";
        var result = string.IsNullOrWhiteSpace(query)
            ? fallback
            : query;
        return result.Replace("where ", $"where {_basefilter} and ", StringComparison.CurrentCultureIgnoreCase);
    }
    
    public List<V6Elevation> GetElevations(V6Project project)
    {
        List<V6Elevation> _result = new();
        try
        {
            var _query = CheckQuery(Settings.ElevationSQL, V6Elevation.SQL, project.Number, project.Variation);
            var _table = Query(_query, "items");
            _result.AddRange(_table.Rows.OfType<DataRow>().Select(DataRowToElevation));
            return _result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

    private V6Elevation DataRowToElevation(DataRow row)
    {
        var _result =  new V6Elevation()
        {
            ID = GetInteger(row, nameof(V6Elevation.ID)),
            Description = GetString(row,nameof(V6Elevation.Description)),
            Quantity = (int)GetDouble(row,nameof(V6Elevation.Quantity))
        };
        return _result;
    }
    
    public V6Drawings? GetDrawingSet(V6Project project, int itemnumber)
    {
        try
        {
            var _query = CheckQuery(Settings.DrawingsSQL, V6Drawings.SQL, project.Number, project.Variation, [itemnumber]);
            var _table = Query(_query, "drawings");
            return _table.Rows.Count > 0
                ? DataRowToDrawings(_table.Rows[0])
                : null;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

    private V6Drawings DataRowToDrawings(DataRow row)
    {
        return new V6Drawings()
        {
            BinaryData = GetBinary(row, nameof(V6Drawings.BinaryData))
        };
    }
    
    public V6BOM GetBOM(V6Project project, IEnumerable<int>? quoteitems)
    {
       var result = new V6BOM();
       var itemnumbers = quoteitems?.ToArray();
       result.Finishes = GetFinishes(project, itemnumbers);
       result.Profiles = GetProfiles(project, itemnumbers);
       result.Gaskets = GetGaskets(project, itemnumbers);
       result.Components = GetComponents(project, itemnumbers);
       result.Glass =  GetGlass(project, itemnumbers);
       result.Labour = GetLabour(project, itemnumbers);
       return result;
    }
    
    public List<V6Labour> GetLabour(V6Project project, IEnumerable<int>? quoteitems = null)
    {
        var _result = new List<V6Labour>();
        try
        {
            string _query = CheckQuery(Settings.LabourSQL, V6Labour.SQL, project.Number, project.Variation, quoteitems);
            var _table = Query(_query,"labour");
            foreach (DataRow _row in _table.Rows)
            {
                var _labour = DataRowToLabour(_row);
                _result.Add(_labour);
            }
            return _result;
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",e.Message);
        }            
        return _result;


    }

    private V6Labour DataRowToLabour(DataRow row)
    {
        var _labour = new V6Labour();
        _labour.Code = GetString(row, nameof(V6Labour.Code));
        _labour.Description = GetString(row, nameof(V6Labour.Description));
        _labour.Quantity = GetDouble(row, nameof(V6Labour.Quantity));
        _labour.Cost = GetDouble(row, nameof(V6Labour.Cost));
        return _labour;
    }

    public List<V6Finish> GetFinishes(V6Project project, IEnumerable<int>? quoteitems = null)
    {
        var _result = new List<V6Finish>();
        try
        {
            string _query = CheckQuery(Settings.FinishSQL, V6Finish.SQL, project.Number, project.Variation, quoteitems);
            var _table = Query(_query,"finish");
        
            foreach (DataRow _row in _table.Rows)
            {
                var _finish = DataRowToFinish(_row);
                _result.Add(_finish);
            }
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",e.Message);
        }
        return _result;
        
    }
    
    private V6Finish DataRowToFinish(DataRow row)
    {
        var _result = new V6Finish();

        _result.Code = GetString(row, nameof(V6Profile.Code));
        _result.Description = GetString(row, nameof(V6Profile.Description));
        return _result;
    }

    public List<V6Profile> GetProfiles(V6Project project, IEnumerable<int>? quoteitems = null)
    {
        var _result = new List<V6Profile>();
        try
        {
            string _query = CheckQuery(Settings.ProfileSQL, V6Profile.SQL, project.Number, project.Variation, quoteitems);
            var _table = Query(_query,"profile");
        
            foreach (DataRow _row in _table.Rows)
            {
                var _profile = DataRowToProfile(_row);

                _result.Add(_profile);
            }
            return _result;
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",e.Message);
        }
        return _result;
    }
    
    private V6Profile DataRowToProfile(DataRow row)
    {
        var _result = new V6Profile();

        _result.Code = GetString(row, nameof(V6Profile.Code));
        _result.Description = GetString(row, nameof(V6Profile.Description));
        _result.Length = GetDouble(row, nameof(V6Profile.Length));
        _result.Quantity = Math.Ceiling(GetDouble(row,nameof(V6Profile.Quantity)) / (_result.Length.IsEffectivelyEqual(0.0) ? 1.0 : _result.Length));
        _result.Cost = GetDouble(row, nameof(V6Profile.Cost));
        _result.Finish = GetString(row, nameof(V6Profile.Finish));
        return _result;
    }
    
    public List<V6Gasket> GetGaskets(V6Project project, IEnumerable<int>? quoteitems = null)
    {
        var _result = new List<V6Gasket>();
        try
        {
            string _query = CheckQuery(Settings.GasketSQL, V6Gasket.SQL, project.Number, project.Variation, quoteitems);
            var _table = Query(_query,"gasket");
        
            foreach (DataRow _row in _table.Rows)
            {
                var _gasket = DataRowToGasket(_row);
                _result.Add(_gasket);
            }
            return _result;
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",e.Message);
        }
        return _result;
    }
    
    private V6Gasket DataRowToGasket(DataRow row)
    {
        var _result = new V6Gasket();

        _result.Code = GetString(row, nameof(V6Gasket.Code));
        _result.Description = GetString(row, nameof(V6Gasket.Description));
        _result.Length = GetDouble(row, nameof(V6Gasket.Length));
        _result.Quantity = Math.Ceiling(GetDouble(row,nameof(V6Profile.Quantity)) / (_result.Length.IsEffectivelyEqual(0.0) ? 1.0 : _result.Length));
        _result.Cost = GetDouble(row, nameof(V6Profile.Cost));
        return _result;
    }
    
    public List<V6Component> GetComponents(V6Project project, IEnumerable<int>? quoteitems = null)
    {
        var _result = new List<V6Component>();
        try
        {


            string _query = CheckQuery(Settings.ComponentSQL, V6Component.SQL, project.Number, project.Variation, quoteitems);
            var _table = Query(_query, "sundries");
            foreach (DataRow _row in _table.Rows)
            {
                var _sundry = DataRowToComponent(_row);

                _result.Add(_sundry);
            }

        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error, "", e.Message);
        }

        return _result;

    }

    private V6Component DataRowToComponent(DataRow row)
    {
        var _result = new V6Component();
        _result.Code = GetString(row, nameof(V6Component.Code));
        _result.Description = GetString(row, nameof(V6Component.Description));
        _result.PackSize = GetDouble(row, nameof(V6Component.PackSize));
        _result.Quantity = GetDouble(row, nameof(V6Component.Quantity));
        _result.Cost = GetDouble(row, nameof(V6Component.Cost));
        return _result;
    }
    
    public List<V6Glass> GetGlass(V6Project project, IEnumerable<int>? quoteitems = null)
    {
        var _result = new List<V6Glass>();
        try
        {
            string _query = CheckQuery(Settings.GlassSQL, V6Glass.SQL, project.Number, project.Variation, quoteitems);
            var _table = Query(_query,"glass");
        
            foreach (DataRow _row in _table.Rows)
            {
                _result.Add(DataRowToGlass(_row));
            }

        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error, "", e.Message);
        }
        return _result;

    }

    private V6Glass DataRowToGlass(DataRow row)
    {
        return new V6Glass()
        {
            Code = GetString(row, nameof(V6Glass.Code)),
            Description = GetString(row, nameof(V6Glass.Description)),
            Treatment = GetString(row, nameof(V6Glass.Treatment)),
            Height = GetDouble(row, nameof(V6Glass.Height)),
            Width = GetDouble(row, nameof(V6Glass.Width)),    
            Location = GetString(row, nameof(V6Glass.Location)),
            Quantity = GetDouble(row, nameof(V6Glass.Quantity)),
            Cost = GetDouble(row, nameof(V6Glass.Cost)),
        };
    }
}