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 System.Text.RegularExpressions;
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? jobnumber, string? reference)
    {
        V6Project? project = null;

        int number = 0;
        if (Settings.UseV6QuoteNumber)
        {
            if (string.IsNullOrWhiteSpace(Settings.ProjectPrefix))
                number = int.TryParse(jobnumber, out int jn) ? jn : 0;
            else if (jobnumber?.StartsWith(Settings.ProjectPrefix) == true)
                number = int.TryParse(jobnumber.Substring(Settings.ProjectPrefix.Length), out int jn) ? jn : 0;
        }
        else
            number = V6Project.ParseReference(reference, out int rn) ? rn : 0;
        
        if (number > 0)
        {
            var _query = CheckQuoteQuery(V6Project.SQL, V6Project.SQL, number, "");
            var _table = Query(_query,"quote");
            return _table.Rows.Count > 0
                ? DataRowToProject(_table.Rows[0])
                : null;
        }
        return project;
    }
    
    public IEnumerable<V6Project> GetProjects()
    {
        
        List<V6Project> _projects = new();
        try
        {
            var _quotes = Query(V6Project.SQL,"quotes");
            if (!IsConnected)
                return _projects;
            
            foreach (DataRow _row in _quotes.Rows)
                _projects.Add(DataRowToProject(_row));
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",$"{e.Message}n{e.StackTrace}");
        }
        return _projects;

    }

    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)),
            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 CheckQuoteQuery(string query, string fallback, int number, string variation)
    {
        string _basefilter = $"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) ";
        var result = string.IsNullOrWhiteSpace(query)
            ? fallback
            : query;
        result = Regex.Replace(result, @"1\s*=\s*1", _basefilter);
        return result;
    }
    
    private static string CheckVariationQuery(string query, string fallback, int number)
    {
        string _basefilter = $"q.quote_num = '{number}' and coalesce(q.quote_num_suff,'') <> '' and q.quote_vers = (select max(quote_vers) from quote where quote_id = q.quote_id) ";
        var result = string.IsNullOrWhiteSpace(query)
            ? fallback
            : query;
        result = Regex.Replace(result, @"1\s*=\s*1", _basefilter);
        return result;
    }
    
    private static string CheckItemQuery(string query, string fallback, IEnumerable<int> quoteitems)
    {
        string _basefilter = $"qi.quote_item_id in ({string.Join(",",quoteitems)}) ";
        var result = string.IsNullOrWhiteSpace(query)
            ? fallback
            : query;
        result = Regex.Replace(result, @"1\s*=\s*1", _basefilter);
        return result;
        //result = result.Replace("\n"," ").Replace("\r"," ").Replace("1=1", $"{_basefilter}", StringComparison.CurrentCultureIgnoreCase);
        //while (result.Contains("  "))
        //    result = result.Replace("  ", " ");
        //return result;
    }

    public List<V6Variation> GetVariations(V6Project project)
    {
        List<V6Variation> _result = new()
            { new V6Variation() { ID = "", Description = "Main Project", SellPrice = project.SellPrice } };
        var _query = CheckVariationQuery(V6Variation.SQL, V6Variation.SQL, project.Number);
        try
        {
            var _table = Query(_query, "items");
            _result.AddRange(_table.Rows.OfType<DataRow>().Select(DataRowToVariation));
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",$"{e.Message}\n{_query}");
        }
        return _result;
    }
    
    private V6Variation DataRowToVariation(DataRow row)
    {
        var _result =  new V6Variation()
        {
            ID = GetString(row, nameof(V6Variation.ID)),
            Description = GetString(row,nameof(V6Variation.Description)),
            SellPrice = GetDouble(row,nameof(V6Variation.SellPrice))
        };
        return _result;
    }
    
    public List<V6Elevation> GetElevations(V6Project project, string variationid)
    {
        List<V6Elevation> _result = new();
        var _query = CheckQuoteQuery(V6Elevation.SQL, V6Elevation.SQL, project.Number, variationid);
        try
        {
            
            var _table = Query(_query, "items");
            _result.AddRange(_table.Rows.OfType<DataRow>().Select(DataRowToElevation));
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",$"{e.Message}\n{_query}");
        }
        return _result;

    }

    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)),
            Drawings = GetInteger(row,nameof(V6Elevation.Drawings))
        };
        return _result;
    }
    
    public V6Drawings? GetDrawingSet(V6Project project, int itemnumber)
    {
        var _query = CheckItemQuery(V6Drawings.SQL, V6Drawings.SQL, [itemnumber]);
        try
        {
            var _table = Query(_query, "drawings");
            return _table.Rows.Count > 0
                ? DataRowToDrawings(_table.Rows[0])
                : null;
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",$"{e.Message}\n{_query}");
        }
        return new V6Drawings();

    }

    private V6Drawings DataRowToDrawings(DataRow row)
    {
        return new V6Drawings()
        {
            BinaryData = GetBinary(row, nameof(V6Drawings.BinaryData))
        };
    }

    public V6BOM GetBOM(V6Project project, int[] quoteitems)
    {
        var result = new V6BOM();
        result.Finishes = GetFinishes(project, (q,f) => CheckItemQuery(q, f, quoteitems));
        result.Profiles = GetProfiles(project, quoteitems);
        result.Gaskets = GetGaskets(project, (q,f) => CheckItemQuery(q, f, quoteitems));
        result.Components = GetComponents(project, (q,f) => CheckItemQuery(q, f, quoteitems));
        result.Glass =  GetGlass(project, (q,f) => CheckItemQuery(q, f, quoteitems));
        result.Labour = GetLabour(project, (q,f) => CheckItemQuery(q, f, quoteitems));
        return result;
    }
    
    public V6BOM GetBOM(V6Project project, string variation)
    {
       var result = new V6BOM();
       result.Finishes = GetFinishes(project, (q,f) => CheckQuoteQuery(q,f,project.Number, variation));
       result.Profiles = GetProfiles(project, variation);
       result.Gaskets = GetGaskets(project, (q,f) => CheckQuoteQuery(q,f,project.Number, variation));
       result.Components = GetComponents(project, (q,f) => CheckQuoteQuery(q,f,project.Number, variation));
       result.Glass =  GetGlass(project, (q,f) => CheckQuoteQuery(q,f,project.Number, variation));
       result.Labour = GetLabour(project, (q,f) => CheckQuoteQuery(q,f,project.Number, variation));
       return result;
    }
    
    
    
    public List<V6Labour> GetLabour(V6Project project, Func<string,string,string> getSql)
    {
        var _result = new List<V6Labour>();
        string _query = getSql(Settings.LabourSQL, V6Labour.SQL);
        try
        {
            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}\n{_query}");
        }            
        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, Func<string,string,string> getSql)
    {
        var _result = new List<V6Finish>();
        string _query = getSql(V6Finish.SQL, V6Finish.SQL);
        try
        {
            
            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}\n{_query}");
        }
        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, string variation)
    {
        var _result = new List<V6Profile>();
        string _query = CheckQuoteQuery(Settings.BOMProfilesSQL, V6Profile.BillOfMaterialsSQL, project.Number, variation);
        try
        {
            
            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}\n{_query}");
        }
        return _result;
    }
    
    public List<V6Profile> GetProfiles(V6Project project, int[] quoteitems)
    {
        var _result = new List<V6Profile>();
        string _query = CheckItemQuery(Settings.DesignProfilesSQL, V6Profile.DesignSQL, quoteitems);
        try
        {
            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}\n{_query}");
        }
        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)) * GetScale(Settings.ProfileMeasurement);
        _result.Quantity = Math.Ceiling(GetDouble(row,nameof(V6Profile.Quantity)));
        _result.Cost = GetDouble(row, nameof(V6Profile.Cost));
        _result.Finish = GetString(row, nameof(V6Profile.Finish));
        return _result;
    }
    
    public List<V6Gasket> GetGaskets(V6Project project, Func<string,string,string> getSql)
    {
        var _result = new List<V6Gasket>();
        string _query = getSql(Settings.GasketSQL, V6Gasket.SQL);
        try
        {
            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}\n{_query}");
        }
        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.Quantity = GetDouble(row, nameof(V6Gasket.Quantity)) * GetScale(Settings.GasketMeasurement);
        _result.Cost = GetDouble(row, nameof(V6Profile.Cost)) / GetScale(Settings.GasketMeasurement);
        return _result;
    }
    
    public List<V6Component> GetComponents(V6Project project, Func<string,string,string> getSql)
    {
        var _result = new List<V6Component>();
        string _query = getSql(Settings.ComponentSQL, V6Component.SQL);
        try
        {
            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}\n{_query}");
        }

        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, Func<string,string,string> getSql)
    {
        var _result = new List<V6Glass>();
        string _query = getSql(Settings.GlassSQL, V6Glass.SQL);
        try
        {
            var _table = Query(_query,"glass");
            foreach (DataRow _row in _table.Rows)
                _result.Add(DataRowToGlass(_row));
            
        }
        catch (Exception e)
        {
            Logger.Send(LogType.Error,"",$"{e.Message}\n{_query}");
        }
        return _result;

    }

    private V6Glass DataRowToGlass(DataRow row)
    {
        var result = new V6Glass();
        result.Code = GetString(row, nameof(V6Glass.Code));
        result.Description = GetString(row, nameof(V6Glass.Description));
        result.Treatment = GetString(row, nameof(V6Glass.Treatment));
        result.Height = GetDouble(row, nameof(V6Glass.Height)) * GetScale(Settings.ProfileMeasurement);
        result.Width = GetDouble(row, nameof(V6Glass.Width)) * GetScale(Settings.GlassMeasurement);
        result.Location = GetString(row, nameof(V6Glass.Location));
        result.Quantity = GetDouble(row, nameof(V6Glass.Quantity));
        result.Cost = GetDouble(row, nameof(V6Glass.Cost));
        return result;
    }

    private double GetScale(V6Measurement scale)
    {
        return scale == V6Measurement.Metres
            ? 0.0254
            : Settings.ProfileMeasurement == V6Measurement.Millimetres
                ? 25.4
                : 1.0;
    }
}