Invoice - ASP.NET Core Invoice Management System

A invoice management system built with ASP.NET Core 8.0, featuring dynamic invoice creation, real-time inventory checking, and hybrid database architecture.

🚀 Features

• Dynamic Invoice Creation: Real-time product selection with auto-complete
• Inventory Management: Stock level checking across multiple stores
• Hybrid Database Architecture: Local PostgreSQL + Supabase integration
• JSONB Support: Flexible additional charges storage
• Responsive UI: Modern web interface with JavaScript/jQuery
• Invoice Slip Generation: Printable invoice views

🏗️ Architecture

Technology Stack

• Framework: ASP.NET Core 8.0 (MVC)
• Database: PostgreSQL (local + Supabase)
• ORM: Entity Framework Core with Npgsql
• Frontend: Razor views with JavaScript/jQuery
• JSON Handling: Newtonsoft.Json

Data Model

• InvoiceHeader: Main invoice records with JSONB additional charges
• InvoiceItem: Individual line items with product details
• External Integration: Product catalog and inventory from Supabase

📋 Prerequisites

• .NET 8.0 SDK
• PostgreSQL database (local or cloud)
• Supabase account (for product catalog)

🛠️ Setup Instructions

1. Clone the Repository

git clone <repository-url>
cd InvoicePOC

2. Database Setup

Local PostgreSQL Database

1. Install PostgreSQL on your machine
2. Create a new database for the application
3. Update the connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "postgresql://username:password@localhost:5432/your_database"
  }
}

Supabase Setup

1. Create a Supabase project
2. Run the database seed script:

psql -h db.bbclercdvyoupgrepjvi.supabase.co -U postgres -d postgres -f db_seed_products_stores.sql

3. Entity Framework Migrations

# Install EF Core tools (if not already installed)
dotnet tool install --global dotnet-ef

# Create and apply migrations
dotnet ef migrations add InitialCreate
dotnet ef database update

4. Update Supabase Connection

Update the Supabase connection string in Controllers/InvoiceController.cs:

private string _supabaseConnString = "Host=your-supabase-host;Port=5432;Database=postgres;Username=postgres;Password=your-password;SSL Mode=Require;Trust Server Certificate=true";

5. Run the Application

dotnet restore
dotnet build
dotnet run

The application will be available at https://localhost:5001 or http://localhost:5000.

📊 Database Schema

Local Database (Invoice Storage)

-- InvoiceHeaders table
CREATE TABLE InvoiceHeaders (
    InvoiceId SERIAL PRIMARY KEY,
    CreatedDate TIMESTAMP NOT NULL,
    NetTotal DECIMAL(12,2) NOT NULL,
    GrandTotal DECIMAL(12,2) NOT NULL,
    AdditionalCharges JSONB
);

-- InvoiceItems table
CREATE TABLE InvoiceItems (
    ItemId SERIAL PRIMARY KEY,
    InvoiceId INTEGER REFERENCES InvoiceHeaders(InvoiceId),
    ProductName TEXT NOT NULL,
    Store TEXT NOT NULL,
    Quantity INTEGER NOT NULL,
    Price DECIMAL(12,2) NOT NULL,
    Total DECIMAL(12,2) NOT NULL
);

Supabase Database (Product Catalog)

• Product: Product names and prices
• Store: Store locations
• ProductStock: Inventory levels per product/store

🎯 Usage

Creating an Invoice

1. Navigate to the application homepage
2. Select products from the dropdown (auto-populated from Supabase)
3. Choose store location
4. Enter quantity (stock levels are checked automatically)
5. Add additional charges if needed
6. Save the invoice

Viewing Invoices

• Generated invoices are stored locally
• Access invoice slips via /Invoice/Slip/{id}

🔧 Configuration

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "your-local-postgresql-connection-string"
  }
}

Environment Variables

• ASPNETCORE_ENVIRONMENT: Set to Development for local development
• Database connection strings can be overridden via environment variables

📁 Project Structure

InvoicePOC/
├── Controllers/
│   ├── HomeController.cs
│   └── InvoiceController.cs
├── Models/
│   ├── InvoiceDbContext.cs
│   ├── InvoiceHeaders.cs
│   ├── InvoiceItem.cs
│   └── ErrorViewModel.cs
├── Views/
│   ├── Home/
│   └── Invoice/
│       ├── Index.cshtml
│       └── Slip.cshtml
├── Migrations/
├── wwwroot/
├── Program.cs
├── appsettings.json
└── db_seed_products_stores.sql

🚀 Deployment

Local Development

dotnet run --environment Development

Production

dotnet publish -c Release
dotnet run --environment Production

🔍 API Endpoints

Invoice Management

• GET /Invoice/Index - Invoice creation form
• POST /Invoice/SaveInvoice - Save new invoice
• GET /Invoice/Slip/{id} - View invoice slip

Product Data (Supabase)

• GET /Invoice/GetProducts - Get all products
• GET /Invoice/GetProductPrice - Get product price
• GET /Invoice/GetStores - Get all stores
• GET /Invoice/GetStock - Get stock level

🛡️ Security Considerations

• Update default connection strings
• Use environment variables for sensitive data
• Implement proper authentication/authorization for production
• Enable HTTPS in production
• Validate all user inputs

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

📝 License

This project is a proof of concept for invoice management.

🆘 Support

For issues and questions:

1. Check the existing issues
2. Create a new issue with detailed information
3. Include error messages and steps to reproduce

---

Note: This is a invoice application. For production use, implement proper security measures, error handling, and comprehensive testing.