using System.Collections.Generic;
using InABox.Core;

namespace Comal.Classes
{
    public class V6Quote : V6Object
    {

        [NullEditor]
        public int ID { get; set; }
    
        [NullEditor]
        public int Revision { get; set; }
    
        [IntegerEditor(Width=80)]
        [Caption("Quote")]
        [EditorSequence(1)]
        public int Number { get; set; }
    
        [TextBoxEditor(Width=60)]
        [EditorSequence(2)]
        public string Variation { get; set; }
    
        [TextBoxEditor(Width = 100)]
        [EditorSequence(3)]
        public string ClientID { get; set; }
        
        [TextBoxEditor(Visible = Visible.Hidden)]
        [EditorSequence(4)]
        public string ClientName { get; set; }
        
        [TextBoxEditor]
        [EditorSequence(5)]
        public string Title { get; set; }
        
        [MemoEditor(Visible = Visible.Hidden)]
        [EditorSequence(6)]
        public string Street { get; set; }
        
        [TextBoxEditor]
        [EditorSequence(7)]
        public string City { get; set; }
                
        [TextBoxEditor]
        [EditorSequence(8)]
        public string State { get; set; }
                
        [TextBoxEditor]
        [EditorSequence(9)]
        public string PostCode { get; set; }
  
        [CurrencyEditor]
        [EditorSequence(10)]
        public double SellPrice {get; set; }

        public override void ValidateQuery(string sql, List<string> errors)
        {
            ValidateField(sql, nameof(ID), errors);
            ValidateField(sql, nameof(Revision), errors);
            ValidateField(sql, nameof(Number), errors);
            ValidateField(sql, nameof(Variation), errors);
            ValidateField(sql, nameof(ClientID), errors);
            ValidateField(sql, nameof(ClientName), errors);
            ValidateField(sql, nameof(Title), errors);
            ValidateField(sql, nameof(Street), errors);
            ValidateField(sql, nameof(City), errors);
            ValidateField(sql, nameof(State), errors);
            ValidateField(sql, nameof(PostCode), errors);
            ValidateField(sql, nameof(SellPrice), errors);
        }
        
        public static string SQL = 
            "select distinct \n" 
            + $"  q.quote_id as {nameof(ID)}, \n " 
            + $"  q.quote_vers as {nameof(Revision)}, \n" 
            + $"  q.quote_num as {nameof(Number)}, \n" 
            + $"  q.quote_num_suff as {nameof(Variation)}, \n" 
            + $"  c.cust_code as {nameof(ClientID)}, \n" 
            + $"  c.cust_name as {nameof(ClientName)}, \n" 
            + $"  coalesce(a.addr_1,'') + coalesce(char(13)+char(10)+a.addr_2,'') as {nameof(Street)}, \n"
            + $"  a.addr_3 as {nameof(City)}, \n"
            + $"  a.addr_4 as {nameof(State)}, \n"
            + $"  a.addr_5 as {nameof(PostCode)}, \n"
            + $"  q.quote_title as {nameof(Title)}, \n"
            + $"  q.nett_sell_price + q.nett_sell_prc_lab as {nameof(SellPrice)} \n" 
            + "from quote q \n" 
            + "  left outer join customer c on q.cust_id = c.cust_id \n" 
            + "  left outer join addr a on q.del_addr_id = a.addr_id \n"
            + "where  \n"
            + "  q.quote_vers = (select max(quote_vers) from quote where quote_id = q.quote_id) \n"
            + "order by \n"
            + "  q.quote_num, \n"
            + "  q.quote_num_suff";
    }
}