prs/prs.desktop/Utils/V6Utils/V6Client.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using Comal.Classes;
using InABox.Clients;
using InABox.Core;
using InABox.Dxf;
using Syncfusion.Windows.Tools.Controls;

namespace PRSDesktop;

public class V6Client : IDisposable
{

    private SqlConnection? connection;

    public V6Settings Settings { get; private set; }

    public V6Client()
    {
        Settings = Client.Query(
                new Filter<V6Settings>().All(),
                Columns.All<V6Settings>(),
                null,
                CoreRange.Database(1)
            )
            .ToObjects<V6Settings>()
            .FirstOrDefault()
        ?? new V6Settings();
        Settings.CheckSQL();
    }
    
    private DataTable QueryV6(string sql, string tablename, SqlParameter[]? parameters = null)
    {
        DataTable _result = new DataTable(tablename);
        if (connection != null)
        {
            using (var _command = new SqlCommand(sql.Replace("\r\n"," ").Replace("\n"," ").Replace("\r"," "), connection))
            {
                if (parameters?.Any() == true)
                {
                    foreach (var _parameter in parameters)
                        _command.Parameters.Add(_parameter);
                }

                using (var _adapter = new SqlDataAdapter(_command))
                    _adapter.Fill(_result);
            }
            connection.Close();
        }

        return _result;
    }

    public int GetInteger(DataRow row, string field, int defaultvalue = 0)
    {
        return row[field] == DBNull.Value ? 0 : Convert.ToInt32(row[field]);
    }
    
    public double GetDouble(DataRow row, string field, double defaultvalue = 0.0)
    {
        return row[field] == DBNull.Value
            ? 0.0
            : Convert.ToDouble(row[field]);
    }

    public string GetString(DataRow row, string field, string defaultvalue = "")
    {
        return row[field] == DBNull.Value ? "" : row[field] as string ?? defaultvalue;
    }
    
    public byte[] GetBinary(DataRow row, string field)
    {
        return row[field] == DBNull.Value ? [] : (byte[])row[field];
    }

    public bool Connect()
    {
        connection = new SqlConnection(Settings.AsConnectionString());
        try
        {
            connection.Open();
        }
        catch
        {
            connection = null;
        }
        return IsConnected;
    }

    public bool IsConnected => connection != null;

    public void Disconnect()
    {
        if (connection != null)
        {
            connection.Dispose();
            connection = null;
        }
    }
    
    public IEnumerable<V6Quote> GetQuotes()
    {
        try
        {
            List<V6Quote> _projects = new();
            if (connection == null)
                return _projects;
        
            var _quotes = QueryV6(Settings.QuoteSQL,"quotes");
        
            foreach (DataRow _row in _quotes.Rows)
                _projects.Add(DataRowToQuote(_row));
        
            return _projects;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

    private V6Quote DataRowToQuote(DataRow row)
    {
        var _quote = new V6Quote()
        {
            ID = GetInteger(row,nameof(V6Quote.ID)),
            Revision = GetInteger(row, nameof(V6Quote.Revision)),
            Number = GetInteger(row, nameof(V6Quote.Number)),
            Variation = GetString(row,nameof(V6Quote.Variation)),
            ClientID = GetString(row,nameof(V6Quote.ClientID)),
            ClientName = GetString(row,nameof(V6Quote.ClientName)),
            Title = GetString(row,nameof(V6Quote.Title)),
            SellPrice = GetDouble(row,nameof(V6Quote.SellPrice)),
        };

        return _quote;
    }
    
    private string CheckQuery(string query, int number, string variation, int? quoteitem = null)
    {
        string _basefilter = quoteitem == 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 = {quoteitem} ";
        return query.Replace("where ", $"where {_basefilter} and ", StringComparison.CurrentCultureIgnoreCase);
    }

    public V6Quote? GetQuote(int number, string variation)
    {
        try
        {
            var _query = CheckQuery(Settings.QuoteSQL, number, variation);
            var _table = QueryV6(_query,"quote");
            return _table.Rows.Count > 0
                ? DataRowToQuote(_table.Rows[0])
                : null;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }
    
    public List<V6Elevation> GetItems(int number, string variation)
    {
        try
        {
            List<V6Elevation> _result = new();
            var _quote = GetQuote(number, variation);
            if (_quote == null)
                return _result;
            var _query = CheckQuery(Settings.ElevationSQL, number, variation);
            var _table = QueryV6(_query, "items");
            _result.AddRange(_table.Rows.OfType<DataRow>().Select(DataRowToItem));
        
            return _result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

    private V6Elevation DataRowToItem(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 GetDrawings(int itemnumber)
    {
        try
        {
            var _query = CheckQuery(Settings.DrawingsSQL, 0, "", itemnumber);
            var _table = QueryV6(_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()
        {
            Drawings = GetBinary(row, nameof(V6Drawings.Drawings))
        };
    }
    
    public List<V6Drawing> DecodeDrawings(byte[] _binary, string[] filetypes)
    {
        string StreamToString(Stream stream)
        {
            stream.Position = 0;
            using (StreamReader _reader = new StreamReader(stream, Encoding.UTF8))
                return _reader.ReadToEnd();
        }

        byte[] StreamToByteArray(Stream stream)
        {
            using(var _memoryStream = new MemoryStream())
            {
                stream.Position = 0;
                stream.CopyTo(_memoryStream);
                return _memoryStream.ToArray();
            }
        }

        List<V6Drawing> _result = new();
        if (_binary.Length != 0)
        {
            Syncfusion.Compression.Zip.ZipArchive _zip = new Syncfusion.Compression.Zip.ZipArchive();
            using (var _ms = new MemoryStream(_binary))
            {
                _zip.Open(_ms,false);
                var _descriptor = _zip.Items.FirstOrDefault(x =>
                    string.Equals(x.ItemName, "descriptor.json", StringComparison.CurrentCultureIgnoreCase));
                if (_descriptor != null)
                {
                    var _json = StreamToString(_descriptor.DataStream);
                    var _drawings = Serialization.Deserialize<V6DrawingIndex>(_json) ?? new V6DrawingIndex();
                    foreach (var _file in _drawings.Files)
                    {
                        if (filetypes == null || filetypes.Any(x =>
                                String.Equals(x, _file.FileType, StringComparison.CurrentCultureIgnoreCase)))
                        {
                            var _frame = _zip.Items.FirstOrDefault(x =>
                                string.Equals(x.ItemName, _file.FileName, StringComparison.CurrentCultureIgnoreCase));
                            if (_frame != null)
                            {
                                var _bmp = DxfUtils.ProcessImage(_frame.DataStream);
                                using (var _bmpStream = new MemoryStream())
                                {
                                    _bmp.Save(_bmpStream, ImageFormat.Png);
                                    var _drawing = new V6Drawing();
                                    _drawing.Data = StreamToByteArray(_bmpStream);
                                    _drawing.FileName = Path.ChangeExtension(_file.FileName, ".png");
                                    _result.Add(_drawing);
                                    //File.WriteAllBytes(System.IO.Path.Combine(@"c:\development\ecoview",_file.FileName),_file.Data);
                                }
                            }
                        }
                    }
                }
            }
        }

        return _result;
    }




    public List<V6Labour> GetLabour(int number, string variation, int? quoteitem = null)
    {
        try
        {
            var _result = new List<V6Labour>();
            var _quote = GetQuote(number, variation);
            if (_quote == null)
                return _result;
            string _query = CheckQuery(Settings.LabourSQL, number, variation, quoteitem);
            var _table = QueryV6(_query,"labour");
            foreach (DataRow _row in _table.Rows)
            {
                var _labour = DataRowToLabour(_row);
                _result.Add(_labour);
            }
            return _result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

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

    public List<V6Profile> GetProfiles(int number, string variation, int? quoteitem = null)
    {
        try
        {
            var _result = new List<V6Profile>();
            var _quote = GetQuote(number, variation);
            if (_quote == null)
                return _result;
            string _query = CheckQuery(Settings.ProfileSQL, number, variation, quoteitem);
            var _table = QueryV6(_query,"profile");
        
            foreach (DataRow _row in _table.Rows)
            {
                var _profile = DataRowToProfile(_row);

                _result.Add(_profile);
            }
            return _result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

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

    
    public List<V6Component> GetComponents(int number, string variation, int? quoteitem = null)
    {
        try
        {
            var _result = new List<V6Component>();
            var _quote = GetQuote(number, variation);
            if (_quote == null)
                return _result;
            string _query = CheckQuery(Settings.ComponentSQL, number, variation, quoteitem);
            var _table = QueryV6(_query,"sundries");
            foreach (DataRow _row in _table.Rows)
            {
                var _sundry = DataRowToComponent(_row);

                _result.Add(_sundry);
            }
            return _result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

    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));
        return _result;
    }
    
    
    public List<V6Glass> GetGlass(int number, string variation, int? quoteitem = null)
    {
        try
        {
            var _result = new List<V6Glass>();
            var _quote = GetQuote(number, variation);
            if (_quote == null)
                return _result;
            string _query = CheckQuery(Settings.GlassSQL, number, variation, quoteitem);
            var _table = QueryV6(_query,"glass");
        
            foreach (DataRow _row in _table.Rows)
            {
                _result.Add(DataRowToGlass(_row));
            }
            return _result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

    }

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

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