📑 BillingController.cs Documentation


📋 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Class Hierarchy & UML Diagram
  4. Data Models & View Models
  5. Key Functional Areas
  6. API Endpoints & Action Methods
  7. Helper Methods & Utilities
  8. Error Handling & Return Types
  9. Third‑party Integrations
  10. Sample Usage Examples
  11. Conclusion

1. Overview & Purpose

BillingController sits in the IrbyImaging.UI project and orchestrates all billing‑related web operations:

  • 📈 Dashboard visuals for carrier spend and device counts
  • 📤 Upload & parse CSV billing files (AT&T & Verizon)
  • 🔎 View/Search invoices by carrier, month, job, phone, employee
  • ✏️ Edit/Create/Delete invoice records
  • 📋 Generate TAP/Excel export of job‑level charges
  • 🔄 Synchronize billing data with an ODS via SQL scripts

Use Case: Back‑office staff upload carrier invoices, review spend trends, search by customer/job, and generate files for ERP ingestion.


2. Dependencies & Technologies

Category Library/Package
Web Framework ASP.NET Core MVC
ORM Entity Framework Core
CSV Parsing CsvHelper
Excel Generation IronXL
SQL Script Execution Microsoft.SqlServer.Management.Smo / Common
Configuration Microsoft.Extensions.Configuration
Hosting Microsoft.AspNetCore.Hosting
HTTP Context Microsoft.AspNetCore.Http
Linq / DataTables System.Linq, System.Data

3. Class Hierarchy & UML Diagram

classDiagram
    BaseController <|-- BillingController
    BillingController o-- IrbyContext
    BillingController o-- IHttpContextAccessor
    BillingController o-- IWebHostEnvironment
    BillingController o-- IConfiguration
    BillingController ..> BillingInvoice : uses
    BillingController ..> Billing : uses
    BillingController ..> CarrierType : uses
  • BillingController inherits from BaseController
  • Collaborators:
    • IrbyContext for EF Core data access
    • _httpContextAccessor to obtain current user
    • _hostingEnvironment for file paths
    • _configuration for settings

4. Data Models & View Models

Model / ViewModel Description
BillingInvoice Entity representing a single invoice line item (DB table: BillingInvoice)
Billing UI Model containing search/filter parameters, result list, dropdown data
BillingInfo DTO for dashboard charts (Carrier, YearMonth, Charge, Devices, Date)
CarrierType Entity for available carriers (AT&T, Verizon)
CarrierTypeDetail Lightweight DTO for populating carrier dropdowns

5. Key Functional Areas

5.1 Dashboard Analytics 📊

  • Action: Dashboard()
  • Purpose: Aggregate billing by carrier & month, device counts, job breakdowns for charting.
  • Flow:
    1. Fetch all non‑deleted invoices.
    2. Group by CarrierName & InvoiceYearMonth → total charges.
    3. Populate ViewBag sources for Syncfusion charts (dataSource1…dataSource7).
    4. Return View() (Razor page renders charts).
public IActionResult Dashboard()
{
    // 1. group & sum by carrier/month
    var billing = _context.BillingInvoices
                   .Where(b => !b.IsDeleted)
                   .GroupBy(b => new { b.CarrierName, b.InvoiceYearMonth })
                   .Select(g => new { g.Key.CarrierName, g.Key.InvoiceYearMonth, TotalCharge = g.Sum(x => x.Charge) })
                   .ToList();
    // 2. map to BillingInfo lists for AT&T & Verizon
    …
    // 3. set ViewBag.dataSource*
    ViewBag.dataSource1 = verizonInfoList;
    ViewBag.dataSource2 = attInfoList;
    …
    return View();
}

5.2 Invoice Upload & Parsing 📤

  • Action: Upload(Billing billing, IFormFile file) [HttpPost]
  • Purpose: Ingest CSV invoice from carrier, normalize phone format, lookup asset details, bulk insert into BillingInvoices table.
  • Highlights:
    • Uses CsvHelper to stream through rows.
    • Splits logic by CarrierName (AT&T vs. Verizon).
    • Formats phone (123) 456-7890 → 123-456-7890 via ToPhoneFormat() extension.
    • Resolves mobile device owner/job info and populates JdeNumber, JobNumber, JobName.
    • Adds audit fields: AddedBy, ModifiedBy, AddedDate, ModifiedDate.
using (var csv = new CsvReader(reader, CultureInfo.CurrentCulture))
{
    csv.Read(); csv.ReadHeader();
    while(csv.Read())
    {
        var invoice = new BillingInvoice { … };
        if (carrier.Carrier == "AT&T") { /* parse AT&T columns */ }
        else { /* parse Verizon Number/UserName column */ }
        invoice.Charge = csv.GetField<string>("Total Charges").ToDecimal();
        // Lookup asset & job
        var asset = _context.MobileDevices
                     .Include(md => md.Owner)
                     .FirstOrDefault(md => md.PhoneNumber == phNum);
        // …
        billingRecs.Add(invoice);
    }
}
_context.BillingInvoices.AddRange(billingRecs);
_context.SaveChanges();

5.3 View & Search Invoices 🔎

  • Actions:
    • ViewInvoice(string carrier, string invoiceM, string invoiceY) [GET]
    • ViewInvoice(Billing billing) [POST]
  • Purpose: Filter and display invoice line items in a table based on user input.
  • Filtering Parameters:
    • Carrier (Guid)
    • Invoice month/year
    • Phone number, employee name, JDE number, job number
    • Null‑value checks flags
IQueryable<BillingInvoice> query = _context.BillingInvoices.Where(b => !b.IsDeleted);
if (!string.IsNullOrEmpty(invoiceM)) query = query.Where(x => x.InvoiceYearMonth.Contains(invoiceM));
if (carrierId != Guid.Empty) query = query.Where(x => x.CarrierId == carrierId);
// Other filters...
billing.SearchResults = query.ToList();

5.4 CRUD Operations ✏️

Operation Action Method HTTP Verb
Create CreateInvoice() / Create(Billing) GET / POST
Edit Edit(Guid id) / Edit(Billing) GET / POST
Delete Delete(Guid id) GET
  • Create: Manual entry of a single invoice line.
  • Edit: Update FirstName, LastName, JDENumber, JobNumber.
  • Delete: Soft‑delete (sets IsDeleted = true).

5.5 File Generation & Exports 📁

  • TAP View:
    • GenerateTapFile() → simply renders carrier dropdown for TAP generation.
  • Excel Export:
    • Generate(Billing billing)
      1. Build a new WorkBook via IronXL.
      2. Query aggregated charges by job.
      3. Populate rows (Job#, JobNumber with suffix, Charge).
      4. Return File(content, contentType, fileName) as .xlsx download.
WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("Invoice");
// write headers
xlsSheet["A1"].Value = invoiceYM;
// fill data...
var stream = xlsWorkbook.ToStream();
return File(stream.ToArray(), contentType, fileName);

5.6 Data Synchronization (ODS) 🔄

  • Action: UpdateODS()
  • Purpose: Execute three SQL scripts (ODS_A_JobUpdate.sql, ODS_B_EmployeeUpdate.sql) against the IrbyImaging database to refresh an Operational Data Store (ODS).
  • Flow:
    1. Read .sql files from wwwroot/Scripts.
    2. Use SMO (ServerConnection) to execute scripts.
    3. Capture messages & row counts into ODSUpdatesLog.txt.
var conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);

6. API Endpoints & Action Methods

Route HTTP Verb Description
/Billing/Dashboard GET Renders spend & device charts
/Billing/Index GET Show upload form (select carrier)
/Billing/Upload POST Upload & parse CSV invoice
/Billing/ViewInvoice GET/POST Search / display invoice records
/Billing/Edit/{id} GET Load invoice into edit form
/Billing/Edit POST Save invoice edits
/Billing/CreateInvoice GET Show manual entry form
/Billing/Create POST Save manual invoice line
/Billing/Delete/{id} GET Soft‑delete invoice
/Billing/GenerateTapFile GET Show export form
/Billing/Generate POST Generate & download Excel invoice sheet
/Billing/UpdateODS (internal call) – Run ODS update scripts

7. Helper Methods & Utilities

  • ToPhoneFormat()

    • Extension (string → standardized phone)
    • Example: "1234567890".ToPhoneFormat() → "123-456-7890"
  • OnInfoMessageGenerated

    • Event handler for capturing SMO InfoMessage events.
  • CSV Parsing Logic

    • Splits fields on delimiters, handles parentheses in charges (debits), strips $.

8. Error Handling & Return Types

  • Error Handling:

    • Most actions assume valid data and may throw runtime exceptions (e.g., missing columns, DB errors).
    • ASP.NET Core global exception handling / developer exception page will surface errors.
  • Return Types:

    • IActionResult for all public methods.
    • FileResult on Excel generation.
    • ViewResult for pages (with Billing view‑model or void for UpdateODS).

9. Third‑party Integrations

Integration Purpose
IronXL Dynamic creation of .xlsx files
CsvHelper Robust streaming CSV parsing
SMO (Smo/Common) Programmatic SQL script execution
Syncfusion Charts (Via ViewBag data) for Dashboard

10. Sample Usage Examples

10.1 Uploading a CSV Invoice

<form asp-action="Upload" enctype="multipart/form-data" method="post">
  <select asp-for="CarrierId" asp-items="Model.Carrier"></select>
  <input asp-for="InvoiceMonth" type="number" />
  <input asp-for="InvoiceYear"  type="number" />
  <input type="file" name="file" accept=".csv" />
  <button type="submit">Upload</button>
</form>

10.2 Generating an Excel Report

// Controller POST stub
[HttpPost]
public IActionResult Generate(Billing billing)
{
    // On submit, user gets back a downloadable .xlsx file
}

10.3 Viewing Invoices

// GET: /Billing/ViewInvoice?carrier=...&invoiceM=April&invoiceY=2022
// POST: Use Billing view‑model filters for advanced search

11. Conclusion 🎯

BillingController is a comprehensive ASP.NET Core MVC controller designed to manage every aspect of carrier invoice handling—from uploading and parsing raw CSV files to visualizing spend trends, editing records, synchronizing with an ODS, and exporting aggregated job charges to Excel. Its integration with Entity Framework Core, CsvHelper, IronXL, and SMO showcases a full‑stack approach to back‑office billing automation and reporting.

👉 Next Steps:

  • Implement exception handling & user feedback for upload errors.
  • Secure file parsing logic against malformed input.
  • Extend dashboard with additional KPIs (e.g., average charge per device).

Happy Coding! 🚀