Skip to content
← All projects

Effect: ERP sales report & lead identifier

Read-only ASP.NET Core app over a PHC ERP: a sales report with Excel export and a lead identifier with AI company lookup.

Context
Effect
Role
Solo project
Period
Sep 2026 – Oct 2026
Stack
  • C#
  • ASP.NET Core
  • Razor Pages
  • Dapper
  • SQL Server
  • OpenXML
  • AI API
  • Windows Service
Links
Private project. Proprietary code. Architecture and impact are described in the case study.

Context & problem

Effect runs its business on the PHC ERP. Its contacts, the quotes sent to them and the contracts signed with them all live in the ERP's SQL Server database, but there was no single place to see a contact together with its quotes and contracts, filter that list and take it to Excel.

A second need came up once the report was in use. When an email arrived from someone new, the team had no quick way to tell whether the sender or their company was already in the ERP, or to find out who the company was.

This was a paid project, and I owned it end to end. Effect is named with permission, and no real data is shown.

Constraints

  • Read-only. The app reads the ERP's data and never writes to it.
  • Large tables. The ERP's document and document-line tables are large, so nothing could load a whole table into the browser or into memory.
  • Internal only. The app runs on Effect's own server and is only reachable from its local network.
  • Exports people can work with. The Excel file had to be readable on its own, with the filters that produced it and the headline figures.
  • No false matches. A lead lookup that links an email to the wrong company is worse than one that finds nothing.

Architecture

Effect app flow: local-network browsers reach a Windows Service that reads the ERP's SQL Server, exports Excel and looks up companies via an AI API
Architecture diagram. Browsers on Effect's local network reach an ASP.NET Core Razor Pages app running as a Windows Service on Kestrel. A subnet allow-list and rate limiting guard each request; Dapper runs read-only queries against the PHC ERP's SQL Server, and results come back as paged tables or a streamed Excel file, with an AI API for company lookups.
Open SVG in a new tab 

ProblemEffect needed one view of each contact with its quotes and contracts, and a fast way to tell whether an incoming email or domain was already in its PHC ERP.

SolutionA .NET 10 Razor Pages app with read-only SQL access to the ERP: a filterable report with Excel export, and a lead identifier that searches ERP tables and looks up companies with AI.

ImpactThe team filters contacts, quotes and contracts in one place, exports them to Excel, and checks a new lead against the ERP without searching it by hand, with no writes to the ERP.

  • App. A single ASP.NET Core Razor Pages app on .NET 10, with no separate API.
  • Data access. Dapper runs read-only SQL queries against SQL Server.
  • Sales report. Contacts and clients, each with its quotes and contracts, behind a set of filters. Pagination happens on the server, so the browser only receives the page it shows. A second date filter limits results by when each dossier was created, and it has to fall inside the contacts' date range.
  • Export. The Excel file is written with OpenXML in streaming mode. It opens with a block showing the filters and KPIs, followed by grouped rows that expand to show each contact's documents.
  • Lead identifier. Given an email address or a domain, the app searches several ERP tables for a matching contact. It matches the exact domain and its subdomains, and skips lookalike domains that would give false positives. Results are shown per table, with the assigned salesperson highlighted.
  • AI company lookup. When the ERP has no match, an AI API looks up the company's legal name, tax number (NIF), business activity and activity code (CAE). The app checks that the NIF and CAE are valid, labels the result as AI-generated, and lists the sources it used.
  • Copy-ready summary. The results can be copied as plain text, ready to paste into an email or a CRM note.
  • Hosting. The app runs as a Windows Service on Kestrel, on the local network, with a subnet allow-list and rate limiting.

Decisions & trade-offs

  • Dapper instead of a full ORM. The app only reads, so hand-written SQL with a light mapper keeps every query explicit. The trade-off: there are no generated queries, and every query is written and maintained by hand.
  • Server-side pagination. Filtering and paging run in the database, so the page stays light however large the data is. The cost is a round trip to the server for every page.
  • Streaming the Excel export. Rows are written to the file as they're read, instead of building the whole workbook in memory first, so memory use doesn't grow with the size of the export.
  • Strict domain matching. The identifier matches a domain and its subdomains only, never a partial string. It may miss a company that uses an unrelated domain, but it won't link a lead to the wrong one.
  • AI as a fallback, checked and labelled. The ERP is always searched first. AI answers are validated where they can be (NIF and CAE), always carry a warning and their sources, and are never written back to the ERP.
  • A Windows Service on the local network. Running on Kestrel as a Windows Service fits Effect's own server and keeps the tool off the internet. The subnet allow-list and rate limiting add a second layer, so only Effect's network can reach it, and only at a reasonable pace.

Results

Effect's team gets one read-only view of the contacts, quotes and contracts already in the ERP, with an Excel export to share it. They can also check a new lead against the ERP and get a verified company profile when it isn't there yet. No metrics are shown, because the figures belong to the client.

Screenshots

Effect ERP sales report dashboard: date, origin and salesperson filters above five summary cards showing contacts, contacts with quotes, contacts with contracts, and total quote and contract values (demo data).
Demo mode with fictitious data.
An expanded contact row in the Effect sales report listing four quotes (Orçamento Série A/B) and two contracts (Contrato Anual, Contrato Serviços) with date, status, salesperson, value excluding VAT and line count, plus section totals (demo data).
Demo mode with fictitious data.
Effect ERP sales report Excel export in LibreOffice Calc: filters and totals, then outline-grouped contacts, their quotes and contracts, and document lines such as Produto A, Componente X and Serviço de consultoria (demo data).
Demo mode with fictitious data.

Want to talk about this project?

Get in touch