Age Open AR on Each Invoice's Own Terms

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.p21server.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string CompanyNo = "ACME";                       // invoice_hdr.company_no
const int Page = 5000;                                 // $top per request
const int TopCustomers = 15;                           // how many rows to print
// ---------------------------------------------------------------------------

const string InvoiceColumns =
    "invoice_no,customer_id,invoice_date,net_due_date,terms_id,terms_desc," +
    "total_amount,amount_paid,paid_in_full_flag,invoice_type,company_no";

string[] buckets = { "not yet due", "1-30", "31-60", "61-90", "90+" };

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(5) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// Open items only. paid_in_full_flag is the flag P21 maintains for exactly this
// question — you cannot ask the service for `total_amount gt amount_paid`, because
// a $filter right-hand side is always a literal.
var invoiceFilter = Uri.EscapeDataString(
    $"paid_in_full_flag eq 'N' and invoice_type eq 'IN' and company_no eq '{CompanyNo}'");
var invoices = await FetchAllAsync(client, "invoice_hdr",
    $"$select={InvoiceColumns}&$filter={invoiceFilter}&$orderby=invoice_no");

// invoice_hdr.customer_id is Edm.String, customer.customer_id is Edm.Decimal —
// the same id, two types. Normalise or every name comes back blank.
var names = new Dictionary<string, string>();
foreach (var c in await FetchAllAsync(client, "customer",
             "$select=customer_id,customer_name&$filter=" +
             Uri.EscapeDataString("delete_flag eq 'N'") + "&$orderby=customer_id"))
{
    if (!c.TryGetProperty("customer_id", out var idEl)) continue;
    var id = idEl.ValueKind == JsonValueKind.Number
        ? ((long)idEl.GetDecimal()).ToString()
        : idEl.GetString() ?? "";
    names[id] = c.TryGetProperty("customer_name", out var n) ? n.GetString() ?? "" : "";
}

var today = DateTime.Today;
var perCustomer = new Dictionary<string, Dictionary<string, decimal>>();
var pastDue = new Dictionary<string, decimal>();
var oldest = new Dictionary<string, int>();
var totals = buckets.ToDictionary(b => b, _ => 0m);
int counted = 0, credits = 0;

foreach (var inv in invoices)
{
    var balance = Num(inv, "total_amount") - Num(inv, "amount_paid");
    if (Math.Abs(balance) < 0.005m) continue;          // applied but not flagged paid

    var due = Text(inv, "net_due_date");
    if (string.IsNullOrEmpty(due)) continue;           // no due date: cannot be aged
    var dpd = (int)(today - DateTimeOffset.Parse(due).Date).TotalDays;

    if (balance < 0) credits++;                        // open credit memo, nets against the total
    var slot = Bucket(dpd);
    var key = Text(inv, "customer_id") ?? "";

    if (!perCustomer.TryGetValue(key, out var row))
        perCustomer[key] = row = buckets.ToDictionary(b => b, _ => 0m);
    row[slot] += balance;
    totals[slot] += balance;

    if (dpd > 0)
    {
        pastDue[key] = pastDue.GetValueOrDefault(key) + balance;
        oldest[key] = Math.Max(oldest.GetValueOrDefault(key), dpd);
    }
    counted++;
}

Console.WriteLine($"{counted} open invoices  ({credits} open credits)  as of {today:yyyy-MM-dd}\n");
Console.WriteLine("company aging".PadRight(28) + string.Concat(buckets.Select(b => b.PadLeft(14))));
Console.WriteLine(new string('-', 28 + 14 * buckets.Length));
Console.WriteLine("".PadRight(28) + string.Concat(buckets.Select(b => totals[b].ToString("N0").PadLeft(14))));

Console.WriteLine($"\ntop {TopCustomers} by past-due balance\n");
Console.WriteLine("customer".PadRight(38) + "past due".PadLeft(13)
    + string.Concat(buckets.Skip(1).Select(b => b.PadLeft(14))) + "oldest".PadLeft(9));
Console.WriteLine(new string('-', 38 + 13 + 14 * 4 + 9));

foreach (var (cid, amount) in pastDue.OrderByDescending(kv => kv.Value).Take(TopCustomers))
{
    if (amount <= 0) break;
    var label = $"{cid} {names.GetValueOrDefault(cid, "")}";
    if (label.Length > 36) label = label[..36];
    Console.WriteLine(label.PadRight(38)
        + amount.ToString("N0").PadLeft(13)
        + string.Concat(buckets.Skip(1).Select(b => perCustomer[cid][b].ToString("N0").PadLeft(14)))
        + oldest.GetValueOrDefault(cid).ToString().PadLeft(9));
}

// --- helpers ---------------------------------------------------------------

static string Bucket(int dpd) => dpd <= 0 ? "not yet due"
    : dpd <= 30 ? "1-30" : dpd <= 60 ? "31-60" : dpd <= 90 ? "61-90" : "90+";

static decimal Num(JsonElement row, string field) =>
    row.TryGetProperty(field, out var v) && v.ValueKind == JsonValueKind.Number ? v.GetDecimal() : 0m;

static string? Text(JsonElement row, string field) =>
    row.TryGetProperty(field, out var v) && v.ValueKind == JsonValueKind.String ? v.GetString() : null;

// Page a table with $top/$skip — P21 sends no @odata.nextLink.
async Task<List<JsonElement>> FetchAllAsync(HttpClient http, string table, string query)
{
    var rows = new List<JsonElement>();
    for (var skip = 0; ; skip += Page)
    {
        var url = $"{BaseUrl}/odataservice/odata/table/{table}?{query}&$top={Page}&$skip={skip}";
        var response = await http.GetAsync(url);
        response.EnsureSuccessStatusCode();
        using var doc = JsonDocument.Parse(await response.Content.ReadAsStringAsync());
        var batch = doc.RootElement.GetProperty("value").EnumerateArray().Select(e => e.Clone()).ToList();
        rows.AddRange(batch);
        if (batch.Count < Page) return rows;
    }
}

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient http)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await http.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    var body = await response.Content.ReadAsStringAsync();
    try
    {
        var value = JsonDocument.Parse(body).RootElement.GetProperty("AccessToken").GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    // Some middleware answers this endpoint in XML even when asked for JSON.
    var match = System.Text.RegularExpressions.Regex.Match(body, "<AccessToken>([^<]+)</AccessToken>");
    if (!match.Success)
        throw new InvalidOperationException($"No AccessToken in response: {body[..Math.Min(200, body.Length)]}");
    return match.Groups[1].Value;
}