prs/prs.shared/Posters/Timberline/BillTimberlinePoster.cs

using Comal.Classes;
using ControlzEx.Standard;
using CsvHelper;
using CsvHelper.Configuration;
using CsvHelper.Configuration.Attributes;
using FastReport.Utils;
using InABox.Core;
using InABox.Core.Postable;
using InABox.Poster.Timberline;
using InABox.Scripting;
using Microsoft.Win32;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace PRS.Shared
{
    public class BillTimberlineHeader
    {
        [Ignore]
        public List<BillTimberlineDistribution> Distributions { get; set; } = new();

        [Index(0)]
        public string RecordID { get; set; } = "APIF";

        [Index(1)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string Vendor { get; set; } = "";

        [Index(2)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 15)]
        public string Invoice { get; set; } = "";

        [Index(3)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 30)]
        public string Description { get; set; } = "";

        [Index(4)]
        public double Amount { get; set; }

        [Index(5)]
        public double Tax { get; set; }

        [Index(6)]
        public double DiscountOffered { get; set; }

        [Index(7)]
        public double MiscDeduction { get; set; }

        [Index(8)]
        [TypeConverter(typeof(TimberlinePosterDateConverter))]
        public DateTime InvoiceDate { get; set; }

        [Index(9)]
        [TypeConverter(typeof(TimberlinePosterDateConverter))]
        public DateTime DateReceived { get; set; }

        [Index(10)]
        [TypeConverter(typeof(TimberlinePosterDateConverter))]
        public DateTime DiscountDate { get; set; }

        [Index(11)]
        [TypeConverter(typeof(TimberlinePosterDateConverter))]
        public DateTime PaymentDate { get; set; }

        [Index(12)]
        [TypeConverter(typeof(TimberlinePosterDateConverter))]
        public DateTime AccountingDate { get; set; }

        [Index(13)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string InvoiceCode1 { get; set; } = "";

        [Index(14)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string InvoiceCode2 { get; set; } = "";

        [Index(15)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 30)]
        public string SmryPayeeName { get; set; } = "";

        [Index(16)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 33)]
        public string SmryPayeeAddress1 { get; set; } = "";

        [Index(17)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 33)]
        public string SmryPayeeAddress2 { get; set; } = "";

        [Index(18)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 15)]
        public string SmryPayeeCity { get; set; } = "";

        [Index(19)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 4)]
        public string SmryPayeeState { get; set; } = "";

        [Index(20)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string SmryPayeeZip { get; set; } = "";
    }

    public class BillTimberlineDistribution
    {
        [Index(0)]
        public string RecordID { get; set; } = "APDF";

        [Index(1)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 12)]
        public string Commitment { get; set; } = "";

        [Index(2)]
        public int CommitmentLineItem { get; set; }

        [Index(3)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 7)]
        public string Equipment { get; set; } = "";

        [Index(4)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 6)]
        public string EQCostCode { get; set; } = "";

        [Index(5)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 6)]
        public string Job { get; set; } = "";

        [Index(6)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string Extra { get; set; } = "";

        [Index(7)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 7)]
        public string CostCode { get; set; } = "";

        [Index(8)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 3)]
        public string Category { get; set; } = "";

        [Index(9)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string BLStdItem { get; set; } = "";

        [Index(10)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 8)]
        public string Reserved { get; set; } = "";

        [Index(11)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), int.MaxValue)] // int.MaxValue because it was chopping the accounts in PRS
        public string ExpenseAccount { get; set; } = "";

        [Index(12)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), int.MaxValue)] // int.MaxValue because it was chopping the accounts in PRS
        public string APAccount { get; set; } = "";

        [Index(13)]
        public double TaxablePayments { get; set; }

        [Index(14)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 6)]
        public string TaxGroup { get; set; } = "";

        [Index(15)]
        public double Units { get; set; }

        [Index(16)]
        public double UnitCost { get; set; }

        [Index(17)]
        public double Amount { get; set; }

        [Index(18)]
        public double Tax { get; set; }

        [Index(19)]
        public double TaxLiability { get; set; }

        [Index(20)]
        public double DiscountOffered { get; set; }

        [Index(21)]
        public double Retainage { get; set; }

        [Index(22)]
        public double MiscDeduction { get; set; }

        [Index(23)]
        [BooleanTrueValues("t", "T", "1", "Y", "y")]
        [BooleanFalseValues("f", "F", "0", "N", "n")]
        public bool TaxablePaymentsExempt { get; set; }

        [Index(24)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string DistCode { get; set; } = "";

        [Index(25)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string MiscEntry1 { get; set; } = "";

        [Index(26)]
        public double MiscEntryUnits1 { get; set; }

        [Index(27)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string MiscEntry2 { get; set; } = "";

        [Index(28)]
        public double MiscEntryUnits2 { get; set; }

        [Index(29)]
        public double MeterOdometer { get; set; }

        [Index(30)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 30)]
        public string Description { get; set; } = "";

        [Index(31)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 10)]
        public string Authorization { get; set; } = "";

        [Index(32)]
        [TypeConverter(typeof(TimberlinePosterStringConverter), 30)]
        public string JointPayee { get; set; } = "";
    }

    public class BillTimberlineSettings : TimberlinePosterSettings<Bill>
    {
        protected override string DefaultScript()
        {
            return @"
using PRS.Shared;
using InABox.Core;
using System.Collections.Generic;

public class Module
{
    public void BeforePost(IDataModel<Bill> model)
    {
        // Perform pre-processing
    }

    public bool ProcessHeader(IDataModel<Bill> model, Bill bill, BillTimberlineHeader header)
    {
        // Do extra processing for a header line; return false to fail this header
        return true;
    }

    public bool ProcessLine(IDataModel<Bill> model, BillLine bill, BillTimberlineDistribution distribution)
    {
        // Do extra processing for a distribution line; return false to fail this header
        return true;
    }

    public void AfterPost(IDataModel<Bill> model)
    {
        // Perform post-processing;
    }
}";
        }
    }

    public class BillTimberlineResult : TimberlinePostResult<BillTimberlineHeader, Bill>
    {
    }

    public class BillTimberlinePoster : ITimberlinePoster<Bill, BillTimberlineSettings>
    {
        public ScriptDocument? Script { get; set; }
        public BillTimberlineSettings Settings { get; set; }

        public bool BeforePost(IDataModel<Bill> model)
        {
            model.SetIsDefault<Document>(false, alias: "CompanyLogo");
            model.SetIsDefault<CoreTable>(false, alias: "CompanyInformation");
            model.SetIsDefault<Employee>(false);

            model.SetColumns(new Columns<Bill>(x => x.ID)
                .Add(x => x.SupplierLink.Code)
                .Add(x => x.Description)
                .Add(x => x.Number)
                .Add(x => x.IncTax)
                .Add(x => x.Tax)
                .Add(x => x.BillDate)
                .Add(x => x.AccountingDate)
                .Add(x => x.PaymentDate)
                .Add(x => x.Approved));

            model.SetColumns(new Columns<BillLine>(x => x.ID)
                .Add(x => x.BillLink.ID)
                .Add(x => x.TaxCode.Code)
                .Add(x => x.IncTax)
                .Add(x => x.Tax)
                .Add(x => x.Description)
                .Add(x => x.PurchaseGL.Code)
                .Add(x => x.OrderItem.ID),
                alias: "Bill_BillLine");

            model.SetIsDefault<BillLine>(true, "Bill_BillLine");
            model.AddChildTable<BillLine, PurchaseOrderItem>(x => x.OrderItem.ID, x => x.ID, isdefault: true,
                parentalias: "Bill_BillLine", childalias: "POItem",
                columns: new Columns<PurchaseOrderItem>(x => x.ID)
                    .Add(x => x.PurchaseOrderLink.PONumber)
                    .Add(x => x.Job.JobNumber)
                    .Add(x => x.Qty)
                    .Add(x => x.Cost)
                    .Add(x => x.PostedReference)
                    );

            Script?.Execute(methodname: "BeforePost", parameters: new object[] { model });
            return true;
        }

        private bool ProcessHeader(IDataModel<Bill> model, Bill bill, BillTimberlineHeader header)
        {
            return Script?.Execute(methodname: "ProcessHeader", parameters: new object[] { model, bill, header }) != false;
        }
        private bool ProcessLine(IDataModel<Bill> model, BillLine bill, BillTimberlineDistribution distribution)
        {
            return Script?.Execute(methodname: "ProcessLine", parameters: new object[] { model, bill, distribution }) != false;
        }

        private BillTimberlineResult DoProcess(IDataModel<Bill> model)
        {
            var result = new BillTimberlineResult();

            var lines = model.GetTable<BillLine>("Bill_BillLine").ToObjects<BillLine>()
                .GroupBy(x => x.BillLink.ID).ToDictionary(x => x.Key, x => x.ToList());
            var purchaseOrderItems = model.GetTable<PurchaseOrderItem>("POItem").ToObjects<PurchaseOrderItem>()
                .ToDictionary(x => x.ID, x => x);

            var bills = model.GetTable<Bill>().ToObjects<Bill>();
            if(bills.Any(x => x.Approved.IsEmpty()))
            {
                throw new PostFailedMessageException("We can't process unapproved bills; please approve all bills before processing.");
            }

            foreach (var bill in bills)
            {
                var apif = new BillTimberlineHeader
                {
                    Vendor = bill.SupplierLink.Code,
                    Invoice = bill.Number,
                    Description = bill.Description,
                    Amount = bill.IncTax,
                    Tax = bill.Tax,
                    // DiscountOffered
                    // Misc. Deduction
                    InvoiceDate = bill.BillDate,
                    // DateReceived doesn't exist
                    DiscountDate = bill.BillDate,
                    PaymentDate = bill.PaymentDate,
                    AccountingDate = bill.AccountingDate,
                    // InvoiceCode1
                    // InvoiceCode2
                    // SmryPayeeName
                    // SmryPayeeAddress1
                    // SmryPayeeAddress2
                    // SmryPayeeCity
                    // SmryPayeeState
                    // SmryPayeeZip
                };
                if (!ProcessHeader(model, bill, apif))
                {
                    result.AddFailed(bill, "Failed by script.");
                }
                else
                {
                    var success = true;
                    var billLines = lines.GetValueOrDefault(bill.ID) ?? new List<BillLine>();
                    foreach (var billLine in billLines)
                    {
                        var apdf = new BillTimberlineDistribution
                        {
                            // Equipment
                            // EQ Cost Code
                            // Extra
                            // Cost Code
                            // Category
                            /// BL STd Item
                            // Reserved
                            ExpenseAccount = billLine.PurchaseGL.Code,
                            // AP Account
                            // Taxable Payments
                            TaxGroup = billLine.TaxCode.Code,
                            Amount = billLine.IncTax,
                            Tax = billLine.Tax,
                            // Tax Liability
                            // Discount OFfered
                            // Retainage
                            // MIsc Deduction
                            // Tax Payments Exempt
                            // Dist Code
                            // Misc Entry 1
                            // Misc Units 1
                            // Misc Entry 2
                            // Misc Units 2
                            // Meter
                            Description = billLine.Description,
                            // Authorization
                            // Joint Payee
                        };
                        if (purchaseOrderItems.TryGetValue(billLine.OrderItem.ID, out var poItem))
                        {
                            apdf.Commitment = poItem.PurchaseOrderLink.PONumber;
                            apdf.Job = poItem.Job.JobNumber;
                            if (int.TryParse(poItem.PostedReference, out var itemNumber))
                            {
                                apdf.CommitmentLineItem = itemNumber;
                                billLine.PostedReference = poItem.PostedReference;
                            }
                            apdf.Units = poItem.Qty;
                            apdf.UnitCost = poItem.Cost;
                        }

                        if (!ProcessLine(model, billLine, apdf))
                        {
                            success = false;
                            break;
                        }
                        apif.Distributions.Add(apdf);
                    }
                    if (success)
                    {
                        foreach(var billLine in billLines)
                        {
                            result.AddFragment(billLine);
                        }
                        result.AddSuccess(bill, apif);
                    }
                    else
                    {
                        result.AddFailed(bill, "Failed by script.");
                    }
                }
            }
            return result;
        }

        public IPostResult<Bill> Process(IDataModel<Bill> model)
        {
            var result = DoProcess(model);

            var dlg = new SaveFileDialog()
            {
                Filter = "CSV Files (*.csv)|*.csv"
            };

            if (dlg.ShowDialog() == true)
            {
                using (var writer = new StreamWriter(dlg.FileName))
                {
                    using var csv = new CsvWriter(writer, CultureInfo.InvariantCulture);
                    foreach (var apif in result.Exports)
                    {
                        csv.WriteRecord(apif);
                        csv.NextRecord();
                        foreach (var apdf in apif.Distributions)
                        {
                            csv.WriteRecord(apdf);
                            csv.NextRecord();
                        }
                    }
                }

                while (true)
                {
                    var logDlg = new OpenFileDialog
                    {
                        InitialDirectory = Path.GetDirectoryName(dlg.FileName),
                        FileName = "APREJECT.txt",
                        Filter = "All Files (*.*) | *.*",
                        Title = "Please select APREJECT.txt"
                    };
                    if (logDlg.ShowDialog() == true)
                    {
                        var rejectedHeaders = new List<BillTimberlineHeader?>();
                        using (var reader = new StreamReader(logDlg.FileName))
                        {
                            using var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)
                            {
                                HasHeaderRecord = false
                            });

                            var i = 1;
                            while (csv.Read())
                            {
                                var id = csv.GetField(0);
                                if (id == "APIF")
                                {
                                    var header = csv.GetRecord<BillTimberlineHeader>();
                                    if (header is not null)
                                    {
                                        var entry = result.Items.FirstOrDefault(x => x.Item2?.Invoice.Equals(header.Invoice) == true);
                                        if (entry is not null)
                                        {
                                            (entry.Item1 as IPostable).FailPost("");
                                        }
                                    }
                                    else
                                    {
                                        Logger.Send(LogType.Error, "", "Bill Timberline export: Unable to parse header from CSV line in rejection file.");
                                        MessageBox.Show($"Invalid line {i} in file; skipping.");
                                    }
                                }
                                else if (id == "APDF")
                                {
                                    // Ignoring these because the reject file contains the header as well, and we don't need to fail BillLines, because
                                    // they aren't postable.

                                    /*var line = csv.GetRecord<BillTimberlineDistribution>();
                                    if (line is not null)
                                    {
                                        var entry = result.Items.FirstOrDefault(x => x.Item2?.Invoice.Equals(line.Invoice) == true);
                                        if (entry is not null)
                                        {
                                            (entry.Item1 as IPostable).FailPost("");
                                        }
                                    }
                                    else
                                    {
                                        Logger.Send(LogType.Error, "", "Bill Timberline export: Unable to parse line from CSV line in rejection file.");
                                        MessageBox.Show("Invalid line in file; skipping.");
                                    }*/
                                }
                                ++i;
                            }
                        }
                        return result;
                    }
                    else
                    {
                        if (MessageBox.Show("Do you wish to cancel the export?", "Cancel Export?", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
                        {
                            throw new PostCancelledException();
                        }
                        else if (MessageBox.Show("Did everything post successfully?", "Successful?", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
                        {
                            return result;
                        }
                    }
                }
            }
            else
            {
                throw new PostCancelledException();
            }
        }
        public void AfterPost(IDataModel<Bill> model, IPostResult<Bill> result)
        {
            Script?.Execute(methodname: "AfterPost", parameters: new object[] { model });
        }
    }

    public class BillTimberlinePosterEngine<T> : TimberlinePosterEngine<Bill, BillTimberlineSettings>
    {
    }
}