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

using Comal.Classes;
using CsvHelper;
using CsvHelper.Configuration;
using CsvHelper.Configuration.Attributes;
using FastReport.Utils;
using InABox.Clients;
using InABox.Core;
using InABox.Core.Postable;
using InABox.Poster.Timberline;
using InABox.Scripting;
using Microsoft.Win32;
using NPOI.SS.Formula.Functions;
using PRSDimensionUtils;
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;
using Columns = InABox.Core.Columns;

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; } = "";

    [Ignore]
    private string _invoice = "";

    [Index(2)]
    [TypeConverter(typeof(TimberlinePosterStringConverter), 15)]
    public string Invoice
    {
        get => _invoice;
        set => _invoice = TimberlinePosterStringConverter.ConvertString(value, 15);
    }

    [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 Draw { get; set; } = "";

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

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

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

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

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

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

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

    [Index(33)]
    [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, PurchaseOrderItem? item, PurchaseOrderItemAllocation? allocation, BillTimberlineDistribution distribution)
    {
        // Do extra processing for a distribution line; return false to fail this header. The BillLine will be split based on the allocations on the purchase order,
        // so that information is given here to with 'item' and 'allocation'; these are both 'null' if the bill isn't linked to a PurchaseOrderItem,
        // and 'allocation' is 'null' if the line corresponds to the primary allocation, based on the job number of the PurchaseOrderItem.
        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)
    {
        foreach(var (name, table) in model.ModelTables)
        {
            table.IsDefault = false;
        }
        model.SetIsDefault<Bill>(true);
        model.SetIsDefault<BillLine>(true, "Bill_BillLine");

        model.SetColumns(Columns.None<Bill>().Add(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.IsApproved)
            .Add(x=>x.ApprovalStatus)
        );

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

        model.AddChildTable<BillLine, PurchaseOrderItem>(x => x.OrderItem.ID, x => x.ID, isdefault: true,
            parentalias: "Bill_BillLine", childalias: "POItem",
            columns: Columns.None<PurchaseOrderItem>().Add(x => x.ID)
                .Add(x => x.PurchaseOrderLink.PONumber)
                .Add(x => x.Qty)
                .Add(x => x.Description)
                .Add(x => x.Cost)
                .Add(x => x.CostCentre.Code)
                .Add(x => x.PurchaseGL.Code)
                .Add(x => x.PostedReference)
                .Add(x => x.Job.ID)
                .Add(x => x.Job.JobNumber)
                .AddDimensionsColumns(x => x.Dimensions, Dimensions.ColumnsType.Local));
        model.AddChildTable<PurchaseOrderItem, PurchaseOrderItemAllocation>(x => x.ID, x => x.Item.ID, isdefault: true,
            parentalias: "POItem", childalias: "Allocations",
            columns: Columns.None<PurchaseOrderItemAllocation>()
                .Add(x => x.Item.ID)
                .Add(x => x.Quantity)
                .Add(x => x.PostedReference)
                .Add(x => x.Job.ID)
                .Add(x => x.Job.JobNumber));

        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, PurchaseOrderItem? item, PurchaseOrderItemAllocation? allocation, BillTimberlineDistribution distribution)
    {
        return Script?.Execute(methodname: "ProcessLine", parameters: new object?[] { model, bill, item, allocation, distribution }) != false;
    }

    private class LineData(IJob job, double poCost, double poQty, IPostableFragment<PurchaseOrder> item, double qty)
    {
        public IJob Job { get; set; } = job;

        public double POCost { get; set; } = poCost;

        public double POQty { get; set; } = poQty;

        public IPostableFragment<PurchaseOrder> Item { get; set; } = item;

        public double Qty { get; set; } = qty;
    }

    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 allocations = model.GetTable<PurchaseOrderItemAllocation>("Allocations").ToObjects<PurchaseOrderItemAllocation>()
            .GroupBy(x => x.Item.ID)
            .ToDictionary(x => x.Key, x => x.ToList());

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

        foreach (var bill in bills)
        {
            if(bill.Number.Length > 15)
            {
                result.AddFailed(bill, "Bill Number cannot be more than 15 digits in length.");
                continue;
            }

            var apif = new BillTimberlineHeader
            {
                Vendor = bill.SupplierLink.Code,
                Invoice = bill.Number,
                Description = bill.Description,
                Amount = Math.Round(bill.IncTax, 4),
                Tax = Math.Round(bill.Tax, 4),
                // 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.");
                continue;
            }

            var success = true;
            var billLines = lines.GetValueOrDefault(bill.ID) ?? new List<BillLine>();
            foreach (var billLine in billLines)
            {
                BillTimberlineDistribution CreateLine()
                {
                    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,
                        // 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
                    };
                    return apdf;
                }

                var lineData = new List<LineData>();
                if (purchaseOrderItems.TryGetValue(billLine.OrderItem.ID, out var poItem))
                {
                    var dimensions = poItem.Dimensions.Copy();
                    var poCost = poItem.Cost;
                    var poQty = poItem.Qty;
                    
                    DimensionUtils.ConvertDimensions(
                        dimensions, 
                        ref poQty, 
                        ref poCost,
                        Client<ProductDimensionUnit>.Provider
                    );
                    
                    if(allocations.TryGetValue(billLine.OrderItem.ID, out var poias))
                    {
                        var remQty = poQty - poias.Sum(x => x.Quantity);
                        if(remQty != 0.0)
                        {
                            lineData.Add(new(poItem.Job, poCost, poQty, poItem, remQty));
                        }
                        lineData.AddRange(poias.Select(x => new LineData(x.Job, poCost, poQty, x, x.Quantity)));
                    }
                    else
                    {
                        lineData.Add(new(poItem.Job, poCost, poQty, poItem, poQty));
                    }

                    foreach(var line in lineData)
                    {
                        var apdf = CreateLine();

                        apdf.Commitment = poItem.PurchaseOrderLink.PONumber;
                        apdf.ExpenseAccount = poItem.PurchaseGL.Code;
                        if(line.Job.ID != Guid.Empty)
                        {
                            apdf.Job = line.Job.JobNumber;
                            apdf.CostCode = poItem.CostCentre.Code;
                        }
                        if (int.TryParse(line.Item.PostedReference, out var itemNumber))
                        {
                            apdf.CommitmentLineItem = itemNumber;
                            billLine.PostedReference = line.Item.PostedReference;
                        }
                        apdf.Units = Math.Round(line.Qty, 4);
                        apdf.UnitCost = Math.Round(line.POCost, 4);
                        apdf.Description = poItem.Description.NotWhiteSpaceOr(apdf.Description);

                        apdf.Tax = Math.Round(billLine.Tax * line.Qty / line.POQty, 4);
                        apdf.Amount = Math.Round(billLine.IncTax * line.Qty / line.POQty, 4);

                        if (!ProcessLine(model, billLine, poItem, line.Item as PurchaseOrderItemAllocation, apdf))
                        {
                            success = false;
                            break;
                        }
                        apif.Distributions.Add(apdf);
                    }
                }
                else
                {
                    var apdf = CreateLine();
                    apdf.Job = billLine.Job.JobNumber;
                    apdf.CostCode = billLine.CostCentre.Code;
                    apdf.Amount = Math.Round(billLine.IncTax, 4);
                    apdf.Tax = Math.Round(billLine.Tax, 4);

                    if (!ProcessLine(model, billLine, null, null, 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>
{
}