Designing VAT and PAN Compliant Billing Systems Using Custom PHP and MySQL
Building retail point-of-sale (POS) and corporate billing systems in Nepal requires strict adherence to Inland Revenue Department (IRD) electronic billing regulations. Invoicing software must enforce sequential invoice numbering, validate 9-digit Permanent Account Numbers (PAN), compute 13% Value Added Tax (VAT) with zero floating-point rounding errors, and preserve an immutable audit ledger.
Strategic Executive Summary
- Core Insight: Under Nepal IRD directives, electronic invoices once finalized cannot be updated or deleted. Any corrections or returns must be recorded as distinct Credit Notes referencing the original invoice number.
- Production Quality: Battle-tested engineering techniques designed specifically for Nepal's network infrastructure and business environment.
- Direct Implementation: Copy-paste ready code architectures with security safeguards against race conditions, data corruption, and unauthorized access.
Table of Contents
1. Relational Database Schema for Invoices and Audit Trails
A robust schema separates invoice headers, line items, and transaction logs with explicit precision types (DECIMAL(12,2)) instead of standard floating points:
CREATE TABLE invoices (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fiscal_year VARCHAR(10) NOT NULL, -- e.g. "2082/83"
bill_no VARCHAR(30) NOT NULL UNIQUE, -- Sequential: e.g. "INV-8283-00142"
customer_name VARCHAR(150) NOT NULL,
customer_pan VARCHAR(9) DEFAULT NULL, -- 9-digit PAN or NULL for consumer
subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
taxable_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, -- 13% on taxable_amount
grand_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
payment_mode ENUM('CASH', 'ESEWA', 'KHALTI', 'FONEPAY', 'BANK_TRANSFER') NOT NULL,
is_realtime_synced TINYINT(1) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_bill_fy (fiscal_year, bill_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE invoice_items (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
invoice_id BIGINT UNSIGNED NOT NULL,
item_description VARCHAR(255) NOT NULL,
quantity DECIMAL(10,2) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
item_total DECIMAL(12,2) NOT NULL,
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;2. Precise Financial Calculations in PHP
Floating-point math in PHP (e.g. $a * 0.13) can produce subtle inaccuracies like 14.99999999994. Always calculate monetary values using integer cents or explicit rounding:
<?php
function calculateInvoiceTotals(array $items, float $discount = 0.00): array {
$subtotal = 0.00;
foreach ($items as $item) {
$lineTotal = round($item['quantity'] * $item['unit_price'], 2);
$subtotal += $lineTotal;
}
$discount = round($discount, 2);
$taxable = max(0.00, $subtotal - $discount);
$vatRate = 0.13; // 13% VAT standard rate in Nepal
$vatAmount = round($taxable * $vatRate, 2);
$grandTotal = round($taxable + $vatAmount, 2);
return [
'subtotal' => $subtotal,
'discount' => $discount,
'taxable' => $taxable,
'vat_amount' => $vatAmount,
'grand_total' => $grandTotal
];
}3. Validating 9-Digit Nepali PAN
Under IRD regulations, corporate transactions requiring tax invoices must provide a valid 9-digit PAN:
function validateNepaliPAN(?string $pan): bool {
if (empty($pan)) return true; // Optional for non-registered retail walk-in customers
return (bool) preg_match('/^[0-9]{9}$/', trim($pan));
}4. Thermal POS Receipt Printing (80mm)
For retail billing counters, thermal printer integration can be executed via pure HTML/CSS print stylesheets:
@media print {
body * { visibility: hidden; }
#thermal-receipt, #thermal-receipt * { visibility: visible; }
#thermal-receipt {
position: absolute;
left: 0;
top: 0;
width: 72mm; /* Standard 80mm roll print area */
font-family: 'Courier New', monospace;
font-size: 12px;
line-height: 1.3;
}
}Official Technical Documentation & Reference Sources:
