Community Portal System Documentation

Project: COS20031 Community Portal
Version: 1.2
Last Updated: November 12, 2025

System Overview

The Community Portal is a web-based apartment management system that facilitates:

Apartment contract management for residents
Service subscriptions (cable, internet, cleaning, etc.)
Utility billing based on meter readings
Invoice management and online payments
Vendor services coordination
Administrative oversight and reporting

Key Features

Role-based access control (Administrator, Customer, Vendor)
Persistent sessions (30-day cookie lifetime)
VNPay payment gateway integration
CSV/Excel invoice export with comprehensive filtering
Real-time invoice calculations
Batch payment processing
Apartment deletion with automatic contract cancellation
Service deletion with automatic subscription termination
Yearly subscription billing (charges at 12-month intervals)
Unified invoice viewing pages for all user roles
PDO-based database access for security and performance

Recent Updates (November 2025)

Reporting & Analytics (November 12, 2025)

Monthly Revenue Tracking: Changed all revenue statistics from all-time to current month only
- admin_reports.php: Updated invoice counts and revenue to filter by DATE_TRUNC('month', "DateIssued")
- vendor_dashboard.php: Changed invoice queries to show only current month data
- Updated all labels to reflect monthly scope: "Invoices (This Month)", "Monthly Revenue"
- 12-month trend charts remain unchanged for historical analysis
Vendor Reports Page (vendor_reports.php): New comprehensive analytics dashboard for vendors
- Summary Cards:
  - Active Customers (distinct customers with paid invoices)
  - Active Contracts (all service contracts in system)
  - Active Services (available services count)
  - Monthly invoice statistics (Pending, Overdue, Paid)
  - Monthly Revenue with Vietnamese Dong formatting
- Interactive Charts (Chart.js 4.4.0):
  - Monthly Revenue Bar Chart: Last 12 months of paid invoices
  - Number of Contracts Line Chart: New vs cumulative contracts over time
  - Number of Customers Line Chart: Customer acquisition tracking
- Accessible via "View Reports & Analytics" button in vendor dashboard
- Fully responsive design matching admin reports style
- Schema-aware queries (vendors linked via Invoice.VendorID, not Service.VendorID)
Server-Side Pagination: Converted from client-side to server-side pagination
- Implemented on admin_view_invoices.php, customer_view_invoices.php, vendor_view_invoices.php
- Changed from LIMIT 500 to LIMIT 20 with OFFSET calculation
- Added page navigation UI with Previous/Next and numbered page links
- Statistics calculated from all matching invoices, not just current page
- Maintains filter/search state across page navigation using http_build_query()
- Removed deprecated scripts/invoice_pagination.js
- Marked scripts/common.js filterTable() as deprecated
Chart.js Integration (admin_reports.php): Added three interactive data visualizations
- Monthly Revenue Bar Chart: Vietnamese Dong formatting with compact notation
- User Growth Line Chart: Dual-axis showing new users and cumulative total
- Apartment Occupancy Rate Trend: Percentage-based line chart with contract activity tracking
- All charts use last 12 months of data with proper date aggregation
- Fixed revenue data source from Payment table to Invoice table with Status='paid' filter

Invoice System Overhaul

Customer Invoice Viewing: Recreated customer_view_invoices.php using admin template for consistent UI/UX
Vendor Invoice Viewing: Recreated vendor_view_invoices.php with proper SQL schema alignment
Export Integration: Moved export functionality into view pages for streamlined workflow
Database Schema Alignment: Fixed all SQL column/table name mismatches:
- Corrected column references: BillNumber (not InvoiceNumber), DateIssued (not InvoiceDate), TotalPrice (not TotalAmount)
- Fixed table references: InvoiceDetail (not InvoiceLineItem)
- Fixed contract status: ApartmentContract.IsActive (boolean, not Status enum)
- Corrected customer name retrieval: Customer.FullName (not User.FullName)
Status Filter Fix: Standardized all status values to lowercase ('paid', 'pending', 'overdue') to match database ENUMs
PDO Migration: Converted customer_export_invoices.php from pg_* functions to PDO for consistency and security

Subscription Billing Enhancement

Yearly Subscription Logic: Updated scripts/generate_monthly_invoices.php to properly handle yearly contracts
- Now charges full contract price only at 12-month intervals from StartDate
- Prevents incorrect monthly division (previously charged ContractPrice/12 monthly)
- Uses DateTime difference calculation to determine months since contract start
- Checks monthsSinceStart % 12 === 0 before charging yearly subscriptions

Service Contract Display Fix

Vendor Dashboard: Fixed service contract query to show ALL active contracts
- Removed vendor filter that was preventing new contracts from displaying
- Vendors can now see all customer subscriptions to their services immediately upon creation

Administrative Deletion Features

Apartment Deletion (admin_manage_apartments.php):
- Added delete functionality with automatic contract cancellation
- Transaction-based deletion ensures data integrity
- Cancels all active ApartmentContract records (sets IsActive = false, EndDate = NOW())
- Preserves historical data while removing apartment from active listings
- Includes confirmation dialog to prevent accidental deletions
Service Deletion (services_manage.php):
- Vendors can delete their own services
- Automatically terminates all active ServiceContract records (sets EndDate = NOW())
- Transaction-based with rollback on errors
- Confirmation dialog warns about contract cancellations
- Maintains referential integrity between services and customer subscriptions

Code Quality Improvements

Common Helper Functions: Created common_helpers.php with reusable utilities:
- formatCurrency(): Consistent Vietnamese Dong formatting
- formatDate(): Standardized date/time display
- h(): HTML escaping for XSS prevention
Error Handling: Added comprehensive try-catch blocks with transaction rollback
SQL Security: Ensured all queries use PDO prepared statements with named parameters

User Roles

Administrator: Full system access, user management, invoice generation
Customer: View apartments, pay invoices, manage subscriptions
Vendor: Create invoices, manage service contracts

Technology Stack

Backend

Language: PHP 7+/8 with strict types
Database: PostgreSQL 13+ (schema: COS20031)
Session Management: PHP sessions with custom cookie configuration
Authentication: HMAC-SHA256 signed cookies for persistent login

Frontend

HTML5 with semantic markup
CSS3 with animations and responsive design
JavaScript (ES6+) for dynamic interactions
No frameworks - vanilla JS for lightweight performance

Payment Gateway

VNPay Sandbox (TmnCode: 0P5OS3KZ)
Hash Algorithm: HMAC-SHA512 for payment signatures

Development Tools

Error Reporting: Enabled for debugging
PDO: Prepared statements for SQL injection prevention
CSRF Protection: Tokens for all forms

Architecture

File Structure

implementation for COS20031/
├── index.php                      # Login page
├── logout.php                     # Logout handler
├── change_password.php            # Password change form
├── creds.php                      # Environment configuration
├── session_config.php             # Session settings
├── auth_helper.php                # Authentication functions
├── schema.sql                     # Database schema
│
├── admin_*.php                    # Administrator pages
│   ├── admin_dashboard.php
│   ├── admin_manage_users.php
│   ├── admin_manage_apartments.php   # Now includes apartment deletion
│   ├── admin_manage_vendors.php
│   ├── admin_generate_invoices.php
│   ├── admin_utility_meters.php
│   ├── admin_reports.php
│   └── admin_manage_requests.php
│
├── customer_*.php                 # Customer pages
│   ├── customer_dashboard.php
│   ├── customer_view_invoices.php    # NEW: Recreated with admin template
│   ├── customer_request_service.php
│   └── customer_export_invoices.php  # Updated to PDO
│
├── vendor_*.php                   # Vendor pages
│   ├── vendor_dashboard.php          # Fixed service contract display
│   ├── vendor_view_invoices.php      # NEW: Recreated with admin template
│   ├── vendor_export_invoices.php    # NEW: CSV export for vendors
│   ├── vendor_create_invoice.php
│   └── vendor_update_profile.php
│
├── batch_payment.php              # Payment selection
├── start_vnpay_batch.php          # VNPay redirect
├── vnpay_batch_return.php         # VNPay callback
├── services_manage.php            # Service management with deletion
├── common_helpers.php             # NEW: Shared helper functions
│
├── styles/
│   ├── dashboard.css              # Main stylesheet (600+ lines)
│   └── login.css                  # Login page styles
│
└── scripts/
    ├── common.js                  # Shared utilities (300+ lines)
    ├── batch_payment.js           # Payment page logic (200+ lines)
    └── generate_monthly_invoices.php  # Updated yearly billing logic

Request Flow

User Request
    ↓
session_config.php (load session settings)
    ↓
session_start() (30-day persistent cookie)
    ↓
auth_helper.php (verify authentication)
    ↓
requireRole() / requireAuth() (check permissions)
    ↓
Database Connection (PDO with COS20031 schema)
    ↓
Business Logic (queries, calculations)
    ↓
HTML Response (with CSRF tokens)

Database Schema

Schema: COS20031

The database uses PostgreSQL with a dedicated schema COS20031 containing all application tables.

Core Entities

1. User Table

Primary authentication table for all system users.

CREATE TABLE "User" (
  "UserID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "HashedPassword" TEXT NOT NULL,
  "PasswordSalt" TEXT NOT NULL, 
  "Username" VARCHAR(255) UNIQUE NOT NULL,
  "PhoneNumber" VARCHAR(20),
  "Email" TEXT,
  "Role" UserRole NOT NULL,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "RequirePasswordChange" BOOLEAN DEFAULT false
);

Indexes:

Primary key on UserID
Unique index on Username

Relationships:

One-to-One with Customer (via UserID)
One-to-One with Vendor (via UserID)

2. Customer Table

Extended information for customer users.

CREATE TABLE "Customer" (
    "CustomerID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "UserID" UUID UNIQUE NOT NULL REFERENCES "User"("UserID") ON DELETE CASCADE,
    "FullName" VARCHAR(100) NOT NULL,
    "DateOfBirth" DATE,
    "PhoneNumber" VARCHAR(15),
    "Email" VARCHAR(100),
    "EmergencyContact" VARCHAR(100),
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on CustomerID
Foreign key index on UserID

Relationships:

Many-to-Many with Apartment (through ApartmentContract)
One-to-Many with Invoice
One-to-Many with ServiceContract

3. Vendor Table

Extended information for vendor users.

CREATE TABLE "Vendor" (
    "VendorID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "UserID" UUID UNIQUE NOT NULL REFERENCES "User"("UserID") ON DELETE CASCADE,
    "CompanyName" VARCHAR(100) NOT NULL,
    "ContactPerson" VARCHAR(100),
    "PhoneNumber" VARCHAR(15),
    "Email" VARCHAR(100),
    "Address" TEXT,
    "ServiceType" VARCHAR(50),
    "ApprovalStatus" VARCHAR(20) DEFAULT 'pending' 
        CHECK ("ApprovalStatus" IN ('pending', 'approved', 'rejected')),
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on VendorID
Foreign key index on UserID
Index on ApprovalStatus

Relationships:

One-to-Many with Invoice (invoices created by vendor)
One-to-Many with Service (services offered)

4. Apartment Table

Physical apartment units in the building.

CREATE TABLE "Apartment" (
    "ApartmentID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ApartmentNumber" VARCHAR(10) UNIQUE NOT NULL,
    "Building" INTEGER,
    "Floor" INTEGER,
    "Bedrooms" INTEGER,
    "Bathrooms" INTEGER,
    "Area" DECIMAL(10, 2),
    "Occupied" BOOLEAN DEFAULT false,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on ApartmentID
Unique index on ApartmentNumber

Relationships:

Many-to-Many with Customer (through ApartmentContract)
One-to-Many with UtilityMeter

5. ApartmentContract Table

Links customers to apartments with role (host/board).

CREATE TABLE "ApartmentContract" (
    "ContractID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ApartmentID" UUID NOT NULL REFERENCES "Apartment"("ApartmentID") ON DELETE CASCADE,
    "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID") ON DELETE CASCADE,
    "StartDate" DATE NOT NULL,
    "EndDate" DATE,
    "Role" VARCHAR(10) CHECK ("Role" IN ('host', 'board')),
    "IsActive" BOOLEAN DEFAULT true,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on ContractID
Foreign key index on ApartmentID
Foreign key index on CustomerID
Composite index on (CustomerID, IsActive)

Business Rules:

Role = 'host': Primary resident (pays rent)
Role = 'board': Secondary resident (pays utilities)
EndDate = NULL: Ongoing contract
IsActive = true: Currently valid contract

6. Service Table

Available community services (cable, internet, cleaning, etc.).

CREATE TABLE "Service" (
    "ServiceID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ServiceName" VARCHAR(100) NOT NULL,
    "ServiceType" VARCHAR(50),
    "Description" TEXT,
    "VendorID" UUID REFERENCES "Vendor"("VendorID") ON DELETE SET NULL,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on ServiceID
Foreign key index on VendorID

Relationships:

Many-to-One with Vendor (service provider)
One-to-Many with ServiceContract (customer subscriptions)

7. ServiceContract Table

Customer subscriptions to services.

CREATE TABLE "ServiceContract" (
    "ServiceContractID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ServiceID" UUID NOT NULL REFERENCES "Service"("ServiceID") ON DELETE CASCADE,
    "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID") ON DELETE CASCADE,
    "Subscription" VARCHAR(20) CHECK ("Subscription" IN ('monthly', 'quarterly', 'annually')),
    "ContractPrice" DECIMAL(10, 2) NOT NULL,
    "StartDate" DATE NOT NULL,
    "EndDate" DATE,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on ServiceContractID
Foreign key index on ServiceID
Foreign key index on CustomerID

Business Rules:

Subscription: Billing frequency
ContractPrice: Price in Vietnamese đồng (no decimals in display)
Used to generate recurring service invoices

8. Invoice Table

Bills for services and utilities.

CREATE TABLE "Invoice" (
    "InvoiceID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID") ON DELETE CASCADE,
    "VendorID" UUID REFERENCES "Vendor"("VendorID") ON DELETE SET NULL,
    "BillNumber" VARCHAR(50) UNIQUE NOT NULL,
    "InvoiceType" VARCHAR(20) CHECK ("InvoiceType" IN ('service', 'utility')),
    "DateIssued" DATE NOT NULL DEFAULT CURRENT_DATE,
    "DueDate" DATE NOT NULL,
    "Status" VARCHAR(20) DEFAULT 'pending' 
        CHECK ("Status" IN ('pending', 'paid', 'overdue', 'cancelled')),
    "TotalPrice" DECIMAL(10, 2) NOT NULL,
    "MeterReadingID" UUID REFERENCES "UtilityMeterReading"("ReadingID") ON DELETE SET NULL,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on InvoiceID
Unique index on BillNumber
Foreign key index on CustomerID
Foreign key index on VendorID
Composite index on (CustomerID, Status)

Business Rules:

InvoiceType = 'service': Service subscription invoice
InvoiceType = 'utility': Utility consumption invoice
VendorID: Links to vendor who created the invoice (service invoices only)
Status = 'pending': Awaiting payment
Status = 'paid': Fully paid
Status = 'overdue': Past due date

9. InvoiceDetail Table

Line items for each invoice (links to service contracts).

CREATE TABLE "InvoiceDetail" (
    "InvoiceDetailID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "InvoiceID" UUID NOT NULL REFERENCES "Invoice"("InvoiceID") ON DELETE CASCADE,
    "ServiceContractID" UUID REFERENCES "ServiceContract"("ServiceContractID") ON DELETE SET NULL,
    "Description" TEXT,
    "Quantity" INTEGER DEFAULT 1,
    "UnitPrice" DECIMAL(10, 2) NOT NULL,
    "Amount" DECIMAL(10, 2) NOT NULL,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on InvoiceDetailID
Foreign key index on InvoiceID
Foreign key index on ServiceContractID

Business Rules:

Amount = Quantity × UnitPrice
Sum of all Amount values = Invoice.TotalPrice

10. Payment Table

Payment records for invoices.

CREATE TABLE "Payment" (
    "PaymentID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "InvoiceID" UUID NOT NULL REFERENCES "Invoice"("InvoiceID") ON DELETE CASCADE,
    "PaymentMethod" VARCHAR(20) CHECK ("PaymentMethod" IN ('vnpay', 'cash', 'bank_transfer')),
    "AmountPaid" DECIMAL(10, 2) NOT NULL,
    "PaymentDate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "BatchPaymentID" UUID REFERENCES "BatchPayment"("BatchPaymentID") ON DELETE SET NULL,
    "TransactionReference" VARCHAR(100)
);

Indexes:

Primary key on PaymentID
Foreign key index on InvoiceID
Foreign key index on BatchPaymentID

Business Rules:

PaymentMethod = 'vnpay': Online payment via VNPay
BatchPaymentID: Groups multiple payments in one transaction
TransactionReference: External payment ID (e.g., VNPay transaction number)

11. BatchPayment Table

Groups multiple invoice payments in one VNPay transaction.

CREATE TABLE "BatchPayment" (
    "BatchPaymentID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID") ON DELETE CASCADE,
    "TotalAmount" DECIMAL(10, 2) NOT NULL,
    "PaymentMethod" VARCHAR(20) DEFAULT 'vnpay',
    "Status" VARCHAR(20) DEFAULT 'pending' 
        CHECK ("Status" IN ('pending', 'completed', 'failed')),
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on BatchPaymentID
Foreign key index on CustomerID

Business Rules:

Created when customer selects multiple invoices to pay
TotalAmount: Sum of all selected invoice amounts
Status = 'completed': VNPay payment successful
Links to multiple Payment records

12. VNPayTransaction Table

Tracks VNPay payment gateway transactions.

CREATE TABLE "VNPayTransaction" (
    "TxnRef" VARCHAR(100) PRIMARY KEY,
    "InvoiceID" UUID REFERENCES "Invoice"("InvoiceID") ON DELETE SET NULL,
    "BatchPaymentID" UUID REFERENCES "BatchPayment"("BatchPaymentID") ON DELETE SET NULL,
    "Amount" DECIMAL(10, 2) NOT NULL,
    "OrderInfo" TEXT,
    "ResponseCode" VARCHAR(10),
    "TransactionNo" VARCHAR(100),
    "BankCode" VARCHAR(20),
    "PayDate" VARCHAR(14),
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on TxnRef (unique VNPay transaction reference)
Foreign key index on BatchPaymentID

Business Rules:

TxnRef: Generated unique ID (timestamp-based)
ResponseCode = '00': Payment successful
PayDate: Format YYYYMMDDHHmmss
Links to BatchPayment for multi-invoice payments

13. UtilityMeter Table

Utility meters for apartments (electricity, water, gas).

CREATE TABLE "UtilityMeter" (
    "MeterID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "ApartmentID" UUID NOT NULL REFERENCES "Apartment"("ApartmentID") ON DELETE CASCADE,
    "MeterType" VARCHAR(20) CHECK ("MeterType" IN ('electricity', 'water', 'gas')),
    "MeterNumber" VARCHAR(50) UNIQUE NOT NULL,
    "InstallationDate" DATE,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on MeterID
Unique index on MeterNumber
Foreign key index on ApartmentID

14. UtilityMeterReading Table

Monthly meter readings for billing.

CREATE TABLE "UtilityMeterReading" (
    "ReadingID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "MeterID" UUID NOT NULL REFERENCES "UtilityMeter"("MeterID") ON DELETE CASCADE,
    "ReadingDate" DATE NOT NULL,
    "PreviousReading" DECIMAL(10, 2) DEFAULT 0,
    "CurrentReading" DECIMAL(10, 2) NOT NULL,
    "Consumption" DECIMAL(10, 2) GENERATED ALWAYS AS ("CurrentReading" - "PreviousReading") STORED,
    "UnitPrice" DECIMAL(10, 2) NOT NULL,
    "TotalCost" DECIMAL(10, 2) GENERATED ALWAYS AS (("CurrentReading" - "PreviousReading") * "UnitPrice") STORED,
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

Primary key on ReadingID
Foreign key index on MeterID

Business Rules:

Consumption: Automatically calculated (current - previous)
TotalCost: Automatically calculated (consumption × unitPrice)
Used to generate utility invoices

Authentication & Session Management

Session Configuration

File: session_config.php

// 30-day persistent session cookies
ini_set('session.cookie_lifetime', (string)(30 * 24 * 3600)); // 2,592,000 seconds
ini_set('session.gc_maxlifetime', (string)(30 * 24 * 3600));
ini_set('session.cookie_httponly', '1');
ini_set('session.cookie_samesite', 'Strict');
ini_set('session.use_strict_mode', '1');
ini_set('session.name', 'COMMUNITY_PORTAL_SESSION');

Configuration Details:

Cookie Lifetime: 30 days (survives browser close)
HttpOnly: Prevents JavaScript access (XSS protection)
SameSite Strict: Prevents CSRF attacks
Custom Session Name: COMMUNITY_PORTAL_SESSION

Use Case 1: User Login

Page: index.php

Flow Diagram

User submits login form
    ↓
Validate CSRF token
    ↓
Check username/password in database
    ↓
Hash password with salt
    ↓
Compare hashed password
    ↓
Create session variables
    ↓
Create signed authentication cookie
    ↓
Redirect to role-specific dashboard

Queries Executed

1. Check if administrator exists (on page load)

SELECT COUNT(*) as count
FROM "User"
WHERE "Role" = 'administrator';

2. Fetch user by username

SELECT 
    "UserID",
    "Username",
    "HashedPassword",
    "PasswordSalt",
    "Role",
    "RequirePasswordChange"
FROM "User"
WHERE "Username" = :username
LIMIT 1;

Parameters:

:username - User-submitted username (string)

3. Password Verification (PHP)

$computedHash = hash("sha256", $user["PasswordSalt"] . $passwordInput);
if (hash_equals($user["HashedPassword"], $computedHash)) {
    // Password correct
}

Security:

SHA-256 hashing with unique salt per user
Constant-time comparison with hash_equals()

Session Creation

Session Variables:

$_SESSION["auth_user_id"] = $user["UserID"];      // UUID
$_SESSION["auth_username"] = $user["Username"];    // String
$_SESSION["auth_role"] = $user["Role"];            // 'administrator'|'customer'|'vendor'
$_SESSION["csrf_token"] = bin2hex(random_bytes(16)); // 32-char hex

Persistent Cookie Creation

Cookie Data Structure:

$cookieData = json_encode([
    'user_id' => $user["UserID"],
    'username' => $user["Username"],
    'role' => $user["Role"],
    'timestamp' => time()
]);

HMAC Signature:

$secretKey = getenv("COOKIE_SECRET");
$signature = hash_hmac('sha256', $cookieData, $secretKey);
$signedCookieValue = base64_encode($cookieData . '|' . $signature);

Set Cookie:

setcookie('user_auth', $signedCookieValue, [
    'expires' => time() + (30 * 24 * 3600),
    'path' => '/',
    'secure' => false,  // Set true for HTTPS
    'httponly' => true,
    'samesite' => 'Strict'
]);

Redirect Logic

if ($role === 'administrator') {
    header("Location: admin_dashboard.php");
} elseif ($role === 'customer') {
    header("Location: customer_dashboard.php");
} elseif ($role === 'vendor') {
    header("Location: vendor_dashboard.php");
}

Use Case 2: Session Restoration (Auto-Login)

Triggered: On any page load when session is empty but cookie exists

Flow Diagram

User visits page with expired session
    ↓
Check for 'user_auth' cookie
    ↓
Base64 decode cookie value
    ↓
Split into data and signature
    ↓
Verify HMAC signature
    ↓
Check timestamp (< 30 days)
    ↓
Restore session variables
    ↓
Redirect to dashboard

Cookie Verification (auth_helper.php)

function verifyAuthCookie(): ?array {
    if (!isset($_COOKIE['user_auth'])) return null;
    
    $signedCookieValue = $_COOKIE['user_auth'];
    $decodedValue = base64_decode($signedCookieValue, true);
    [$cookieData, $signature] = explode('|', $decodedValue, 2);
    
    $secretKey = getenv("COOKIE_SECRET");
    $expectedSignature = hash_hmac('sha256', $cookieData, $secretKey);
    
    if (!hash_equals($expectedSignature, $signature)) {
        return null; // Tampered cookie
    }
    
    $userData = json_decode($cookieData, true);
    
    // Check expiration
    if ((time() - $userData['timestamp']) > (30 * 24 * 3600)) {
        return null; // Expired
    }
    
    return $userData;
}

Security Measures:

HMAC-SHA256 prevents cookie tampering
Constant-time signature comparison
Timestamp validation
Automatic expiration after 30 days

Use Case 3: Password Change

Page: change_password.php

Queries Executed

1. Check if password change is required

SELECT "RequirePasswordChange" 
FROM "User" 
WHERE "UserID" = :userId;

2. Fetch current user data

SELECT "UserID", "HashedPassword", "PasswordSalt"
FROM "User"
WHERE "UserID" = :userId
LIMIT 1;

3. Update password

UPDATE "User"
SET "HashedPassword" = :hashedPassword,
    "PasswordSalt" = :passwordSalt,
    "RequirePasswordChange" = false
WHERE "UserID" = :userId;

Parameters:

:hashedPassword - SHA-256 hash of (newSalt + newPassword)
:passwordSalt - New random 32-character hex salt
:userId - Current user's UUID

Validation Rules

// Current password required
if ($currentPassword === "") {
    $errors[] = "Current password is required.";
}

// New password minimum length
if (strlen($newPassword) < 8) {
    $errors[] = "New password must be at least 8 characters long.";
}

// Passwords must match
if ($newPassword !== $confirmPassword) {
    $errors[] = "New password and confirmation do not match.";
}

// Must be different from current
if ($newPassword === $currentPassword) {
    $errors[] = "New password must be different from current password.";
}

Password Update Process

// Generate new salt (32 random bytes)
$newSalt = bin2hex(random_bytes(16));

// Hash new password with new salt
$newHashedPassword = hash("sha256", $newSalt . $newPassword);

// Update database
$updateStatement->execute([
    ':hashedPassword' => $newHashedPassword,
    ':passwordSalt' => $newSalt,
    ':userId' => $userId
]);

Redirect After Success:

header("refresh:2;url=$dashboardUrl");

Use Case 4: Logout

Page: logout.php

Flow Diagram

User clicks Logout button (POST request)
    ↓
Load session configuration
    ↓
Start session (with correct name)
    ↓
Clear all session variables
    ↓
Destroy session cookie
    ↓
Destroy authentication cookie
    ↓
Destroy session data
    ↓
Redirect to index.php

Logout Process (auth_helper.php)

function logout(): void {
    // Clear all session variables
    $_SESSION = [];
    
    // Destroy the session cookie
    if (ini_get("session.use_cookies")) {
        $params = session_get_cookie_params();
        setcookie(session_name(), '', [
            'expires' => time() - 42000,
            'path' => $params["path"],
            'domain' => $params["domain"],
            'secure' => $params["secure"],
            'httponly' => $params["httponly"],
            'samesite' => $params["samesite"] ?? 'Strict'
        ]);
    }
    
    // Destroy session data on server
    session_destroy();
    
    // Clear the authentication cookie
    if (isset($_COOKIE['user_auth'])) {
        setcookie('user_auth', '', [
            'expires' => time() - 3600,
            'path' => '/',
            'secure' => false,
            'httponly' => true,
            'samesite' => 'Strict'
        ]);
    }
}

Critical: logout.php must use session_config.php to ensure it destroys the correct session (with custom name COMMUNITY_PORTAL_SESSION).

Use Case 5: Authentication Helpers

File: auth_helper.php

Helper Functions

1. Get User ID

function getUserId(): ?string {
    // Check session first
    if (isset($_SESSION['auth_user_id'])) {
        return $_SESSION['auth_user_id'];
    }
    
    // Fallback to cookie
    $cookieData = verifyAuthCookie();
    if ($cookieData !== null && isset($cookieData['user_id'])) {
        return $cookieData['user_id'];
    }
    
    return null;
}

2. Get Username

function getUsername(): ?string {
    if (isset($_SESSION['auth_username'])) {
        return $_SESSION['auth_username'];
    }
    
    $cookieData = verifyAuthCookie();
    return $cookieData['username'] ?? null;
}

3. Get User Role

function getUserRole(): ?string {
    if (isset($_SESSION['auth_role'])) {
        return $_SESSION['auth_role'];
    }
    
    $cookieData = verifyAuthCookie();
    return $cookieData['role'] ?? null;
}

4. Check Authentication

function isAuthenticated(): bool {
    return getUserId() !== null;
}

5. Check Specific Role

function hasRole(string $role): bool {
    $userRole = getUserRole();
    return $userRole !== null && $userRole === $role;
}

6. Require Authentication

function requireAuth(string $redirectUrl = 'index.php'): void {
    if (!isAuthenticated()) {
        header("Location: $redirectUrl");
        exit();
    }
}

7. Require Specific Role

function requireRole(string $role, string $redirectUrl = 'index.php'): void {
    if (!hasRole($role)) {
        header("Location: $redirectUrl");
        exit();
    }
}

8. Check Password Change Required

function requiresPasswordChange(): bool {
    if (!isAuthenticated()) return false;
    
    $userId = getUserId();
    
    $query = 'SELECT "RequirePasswordChange" FROM "User" WHERE "UserID" = :userId';
    $statement = $pdo->prepare($query);
    $statement->execute([':userId' => $userId]);
    $result = $statement->fetch();
    
    return $result !== false && 
           isset($result['RequirePasswordChange']) && 
           $result['RequirePasswordChange'] === true;
}

CSRF Protection

All forms include CSRF token validation.

Token Generation (on page load):

if (empty($_SESSION["csrf_token"])) {
    $_SESSION["csrf_token"] = bin2hex(random_bytes(16));
}
$csrfToken = $_SESSION["csrf_token"];

HTML Form:

<input type="hidden" name="csrf_token" value="<?= htmlspecialchars($csrfToken) ?>">

Validation (on form submit):

$submittedToken = (string) ($_POST["csrf_token"] ?? "");

if (!hash_equals($csrfToken, $submittedToken)) {
    $errors[] = "Your session has expired. Please try again.";
}

Security:

Constant-time comparison with hash_equals()
Token regenerated per session
32-character random hex string

Administrator Use Cases

Admin Dashboard Overview

Page: admin_dashboard.php

Access Control:

requireRole('administrator', 'index.php');
if (requiresPasswordChange()) {
    header("Location: change_password.php");
    exit();
}

Queries Executed on Dashboard Load

1. Get Admin User Info

SELECT COUNT(*) as count
FROM "User"
WHERE "Role" = 'administrator';

2. Count Total Users

SELECT COUNT(*) as total_users
FROM "User";

3. Count Customers

SELECT COUNT(*) as total_customers
FROM "User"
WHERE "Role" = 'customer';

4. Count Apartments

SELECT COUNT(*) as total_apartments
FROM "Apartment";

5. Count Pending Vendor Approvals

SELECT COUNT(*) as pending_vendors
FROM "Vendor"
WHERE "ApprovalStatus" = 'pending';

Use Case 1: User Management

Page: admin_manage_users.php

View All Users

Query:

SELECT 
    u."UserID",
    u."Username",
    u."Role",
    u."RequirePasswordChange",
    u."CreatedAt",
    c."FullName" as "CustomerName",
    c."Email" as "CustomerEmail",
    v."CompanyName" as "VendorName",
    v."Email" as "VendorEmail"
FROM "User" u
LEFT JOIN "Customer" c ON u."UserID" = c."UserID"
LEFT JOIN "Vendor" v ON u."UserID" = v."UserID"
ORDER BY u."CreatedAt" DESC;

Result Columns:

UserID (UUID)
Username
Role (administrator/customer/vendor)
RequirePasswordChange (boolean)
CreatedAt (timestamp)
CustomerName or VendorName (depending on role)
Email

Create New User

Transaction Flow:

BEGIN TRANSACTION
    ↓
Insert into User table
    ↓
Insert into Customer or Vendor table (based on role)
    ↓
COMMIT

1. Insert User

INSERT INTO "User" (
    "Username",
    "HashedPassword",
    "PasswordSalt",
    "Role",
    "RequirePasswordChange"
) VALUES (
    :username,
    :hashedPassword,
    :passwordSalt,
    :role,
    true
) RETURNING "UserID";

Parameters:

:username - Unique username (string)
:hashedPassword - SHA-256 hash (64 chars)
:passwordSalt - Random salt (32 chars hex)
:role - 'administrator', 'customer', or 'vendor'

2a. If Customer, Insert Customer Data

INSERT INTO "Customer" (
    "UserID",
    "FullName",
    "DateOfBirth",
    "PhoneNumber",
    "Email",
    "EmergencyContact"
) VALUES (
    :userId,
    :fullName,
    :dateOfBirth,
    :phoneNumber,
    :email,
    :emergencyContact
);

2b. If Vendor, Insert Vendor Data

INSERT INTO "Vendor" (
    "UserID",
    "CompanyName",
    "ContactPerson",
    "PhoneNumber",
    "Email",
    "Address",
    "ServiceType",
    "ApprovalStatus"
) VALUES (
    :userId,
    :companyName,
    :contactPerson,
    :phoneNumber,
    :email,
    :address,
    :serviceType,
    'pending'
);

Delete User

Transaction Flow:

BEGIN TRANSACTION
    ↓
Delete from Customer/Vendor (CASCADE)
    ↓
Delete from User
    ↓
COMMIT

Query:

DELETE FROM "User"
WHERE "UserID" = :userId;

Cascade Effects:

Customer: Deletes ApartmentContracts, ServiceContracts, Invoices
Vendor: Sets VendorID to NULL in related Services and Invoices

Force Password Change

Query:

UPDATE "User"
SET "RequirePasswordChange" = true
WHERE "UserID" = :userId;

Use Case 2: Apartment Management

Page: admin_manage_apartments.php

View All Apartments

Query:

SELECT 
    a."ApartmentID",
    a."ApartmentNumber",
    a."Building",
    a."Floor",
    a."Bedrooms",
    a."Bathrooms",
    a."Area",
    a."Occupied",
    COUNT(DISTINCT ac."ContractID") as "ContractCount",
    STRING_AGG(DISTINCT c."FullName", ', ') as "CurrentResidents"
FROM "Apartment" a
LEFT JOIN "ApartmentContract" ac ON a."ApartmentID" = ac."ApartmentID" 
    AND ac."IsActive" = true
LEFT JOIN "Customer" c ON ac."CustomerID" = c."CustomerID"
GROUP BY a."ApartmentID"
ORDER BY a."Building", a."Floor", a."ApartmentNumber";

Result Columns:

ApartmentID (UUID)
ApartmentNumber (e.g., "101", "202")
Building, Floor, Bedrooms, Bathrooms, Area
Occupied (boolean)
ContractCount (number of active contracts)
CurrentResidents (comma-separated names)

Create New Apartment

Query:

INSERT INTO "Apartment" (
    "ApartmentNumber",
    "Building",
    "Floor",
    "Bedrooms",
    "Bathrooms",
    "Area",
    "Occupied"
) VALUES (
    :apartmentNumber,
    :building,
    :floor,
    :bedrooms,
    :bathrooms,
    :area,
    false
) RETURNING "ApartmentID";

Parameters:

:apartmentNumber - Unique identifier (string, e.g., "301")
:building - Building number (integer)
:floor - Floor number (integer)
:bedrooms - Number of bedrooms (integer)
:bathrooms - Number of bathrooms (integer)
:area - Area in square meters (decimal)

Assign Customer to Apartment

Transaction Flow:

BEGIN TRANSACTION
    ↓
Insert ApartmentContract
    ↓
Update Apartment.Occupied = true
    ↓
COMMIT

1. Create Contract

INSERT INTO "ApartmentContract" (
    "ApartmentID",
    "CustomerID",
    "StartDate",
    "EndDate",
    "Role",
    "IsActive"
) VALUES (
    :apartmentId,
    :customerId,
    :startDate,
    :endDate,
    :role,
    true
) RETURNING "ContractID";

Parameters:

:apartmentId - UUID of apartment
:customerId - UUID of customer
:startDate - Contract start date
:endDate - Contract end date (NULL for ongoing)
:role - 'host' (primary) or 'board' (secondary)

2. Update Apartment Status

UPDATE "Apartment"
SET "Occupied" = true
WHERE "ApartmentID" = :apartmentId;

Terminate Contract

Transaction Flow:

BEGIN TRANSACTION
    ↓
Update ApartmentContract.IsActive = false
    ↓
Set EndDate = CURRENT_DATE
    ↓
Check if any active contracts remain
    ↓
If none, set Apartment.Occupied = false
    ↓
COMMIT

1. Deactivate Contract

UPDATE "ApartmentContract"
SET "IsActive" = false,
    "EndDate" = CURRENT_DATE
WHERE "ContractID" = :contractId;

2. Check Remaining Contracts

SELECT COUNT(*) as active_contracts
FROM "ApartmentContract"
WHERE "ApartmentID" = :apartmentId
  AND "IsActive" = true;

3. Update Apartment If No Active Contracts

UPDATE "Apartment"
SET "Occupied" = false
WHERE "ApartmentID" = :apartmentId
  AND NOT EXISTS (
      SELECT 1 FROM "ApartmentContract"
      WHERE "ApartmentID" = :apartmentId
        AND "IsActive" = true
  );

Delete Apartment (NEW)

Transaction Flow:

BEGIN TRANSACTION
    ↓
Cancel all active ApartmentContracts
    ↓
Delete Apartment record
    ↓
COMMIT (or ROLLBACK on error)

1. Cancel Active Contracts

UPDATE "ApartmentContract"
SET "IsActive" = false,
    "EndDate" = NOW()
WHERE "ApartmentID" = :apartment_id
  AND "IsActive" = true;

2. Delete Apartment

DELETE FROM "Apartment"
WHERE "ApartmentID" = :apartment_id;

Features:

Automatically terminates all tenant contracts before deletion
Preserves historical contract data (IsActive = false, EndDate set)
Transaction-based for data integrity
JavaScript confirmation dialog prevents accidental deletions
Rollback on any error to maintain referential integrity

Use Case:

Remove apartments that are no longer available (demolished, converted to other use)
Ensure all tenants are properly unassigned before removal
Maintain audit trail through contract history

Use Case 3: Vendor Approval

Page: admin_manage_vendors.php

View Pending Vendors

Query:

SELECT 
    v."VendorID",
    v."CompanyName",
    v."ContactPerson",
    v."PhoneNumber",
    v."Email",
    v."Address",
    v."ServiceType",
    v."ApprovalStatus",
    v."CreatedAt",
    u."Username"
FROM "Vendor" v
JOIN "User" u ON v."UserID" = u."UserID"
WHERE v."ApprovalStatus" = 'pending'
ORDER BY v."CreatedAt" ASC;

Approve Vendor

Query:

UPDATE "Vendor"
SET "ApprovalStatus" = 'approved'
WHERE "VendorID" = :vendorId;

Effects:

Vendor can now access vendor_dashboard.php
Vendor can create invoices
Vendor services become visible to customers

Reject Vendor

Query:

UPDATE "Vendor"
SET "ApprovalStatus" = 'rejected'
WHERE "VendorID" = :vendorId;

Effects:

Vendor account blocked from vendor functions
Can still login but sees rejection message

Use Case 4: Invoice Generation (Utilities)

Page: admin_generate_invoices.php

This is one of the most complex operations, generating utility invoices from meter readings.

Flow Diagram

Select apartment
    ↓
View utility meters
    ↓
Enter meter reading
    ↓
Calculate consumption
    ↓
Calculate total cost
    ↓
Create meter reading record
    ↓
Generate invoice
    ↓
Create invoice details

View Apartments with Meters

Query:

SELECT 
    a."ApartmentID",
    a."ApartmentNumber",
    a."Building",
    a."Floor",
    COUNT(DISTINCT um."MeterID") as "MeterCount",
    STRING_AGG(DISTINCT um."MeterType", ', ') as "MeterTypes"
FROM "Apartment" a
LEFT JOIN "UtilityMeter" um ON a."ApartmentID" = um."ApartmentID"
WHERE a."Occupied" = true
GROUP BY a."ApartmentID"
ORDER BY a."Building", a."Floor", a."ApartmentNumber";

View Meters for Apartment

Query:

SELECT 
    um."MeterID",
    um."MeterType",
    um."MeterNumber",
    um."InstallationDate",
    umr."CurrentReading" as "LastReading",
    umr."ReadingDate" as "LastReadingDate"
FROM "UtilityMeter" um
LEFT JOIN LATERAL (
    SELECT "CurrentReading", "ReadingDate"
    FROM "UtilityMeterReading"
    WHERE "MeterID" = um."MeterID"
    ORDER BY "ReadingDate" DESC
    LIMIT 1
) umr ON true
WHERE um."ApartmentID" = :apartmentId
ORDER BY um."MeterType";

Parameters:

:apartmentId - UUID of apartment

Result Columns:

MeterID (UUID)
MeterType ('electricity', 'water', 'gas')
MeterNumber (string)
LastReading (decimal, or NULL if first reading)
LastReadingDate (date)

Create Meter Reading and Invoice

Transaction Flow:

BEGIN TRANSACTION
    ↓
1. Insert UtilityMeterReading
    ↓
2. Get Apartment's Customer (host role)
    ↓
3. Generate Bill Number
    ↓
4. Create Invoice
    ↓
5. Create InvoiceDetail
    ↓
COMMIT

1. Insert Meter Reading

INSERT INTO "UtilityMeterReading" (
    "MeterID",
    "ReadingDate",
    "PreviousReading",
    "CurrentReading",
    "UnitPrice"
) VALUES (
    :meterId,
    CURRENT_DATE,
    :previousReading,
    :currentReading,
    :unitPrice
) RETURNING "ReadingID", "Consumption", "TotalCost";

Parameters:

:meterId - UUID of meter
:previousReading - Last reading value (0 if first)
:currentReading - New reading value
:unitPrice - Price per unit (đồng)

Computed Columns:

Consumption = CurrentReading - PreviousReading
TotalCost = Consumption × UnitPrice

2. Get Customer for Apartment

SELECT c."CustomerID"
FROM "ApartmentContract" ac
JOIN "Customer" c ON ac."CustomerID" = c."CustomerID"
WHERE ac."ApartmentID" = :apartmentId
  AND ac."Role" = 'host'
  AND ac."IsActive" = true
LIMIT 1;

Business Rule: Host role pays utility bills.

3. Generate Bill Number

$billNumber = 'UTL-' . date('Ym') . '-' . str_pad($sequence, 4, '0', STR_PAD_LEFT);
// Example: UTL-202411-0001

4. Create Invoice

INSERT INTO "Invoice" (
    "CustomerID",
    "BillNumber",
    "InvoiceType",
    "DateIssued",
    "DueDate",
    "Status",
    "TotalPrice",
    "MeterReadingID"
) VALUES (
    :customerId,
    :billNumber,
    'utility',
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '30 days',
    'pending',
    :totalCost,
    :readingId
) RETURNING "InvoiceID";

5. Create Invoice Detail

INSERT INTO "InvoiceDetail" (
    "InvoiceID",
    "Description",
    "Quantity",
    "UnitPrice",
    "Amount"
) VALUES (
    :invoiceId,
    :description,  -- e.g., 'Electricity - November 2025'
    :consumption,
    :unitPrice,
    :totalCost
);

Parameters:

:description - "{MeterType} - {Month Year}"
:consumption - Usage quantity
:unitPrice - Price per unit
:totalCost - Consumption × UnitPrice

Use Case 5: Utility Meter Management

Page: admin_utility_meters.php

View All Meters

Query:

SELECT 
    um."MeterID",
    um."MeterType",
    um."MeterNumber",
    um."InstallationDate",
    a."ApartmentNumber",
    a."Building",
    a."Floor",
    COUNT(umr."ReadingID") as "ReadingCount",
    MAX(umr."ReadingDate") as "LastReadingDate"
FROM "UtilityMeter" um
JOIN "Apartment" a ON um."ApartmentID" = a."ApartmentID"
LEFT JOIN "UtilityMeterReading" umr ON um."MeterID" = umr."MeterID"
GROUP BY um."MeterID", a."ApartmentNumber", a."Building", a."Floor"
ORDER BY a."Building", a."Floor", a."ApartmentNumber", um."MeterType";

Install New Meter

Query:

INSERT INTO "UtilityMeter" (
    "ApartmentID",
    "MeterType",
    "MeterNumber",
    "InstallationDate"
) VALUES (
    :apartmentId,
    :meterType,
    :meterNumber,
    :installationDate
) RETURNING "MeterID";

Parameters:

:apartmentId - UUID of apartment
:meterType - 'electricity', 'water', or 'gas'
:meterNumber - Unique meter identifier (string)
:installationDate - Installation date

Validation:

MeterNumber must be unique across all meters
One meter per type per apartment

View Meter Reading History

Query:

SELECT 
    "ReadingID",
    "ReadingDate",
    "PreviousReading",
    "CurrentReading",
    "Consumption",
    "UnitPrice",
    "TotalCost"
FROM "UtilityMeterReading"
WHERE "MeterID" = :meterId
ORDER BY "ReadingDate" DESC
LIMIT 12;  -- Last 12 months

Use Case 6: Service Management

Page: services_manage.php

Note: This page is accessible to administrators only for managing the service catalog.

View All Services

Query:

SELECT 
    s."ServiceID",
    s."ServiceName",
    s."ServiceType",
    s."Description",
    v."CompanyName" as "VendorName",
    v."VendorID",
    COUNT(DISTINCT sc."ServiceContractID") as "SubscriberCount"
FROM "Service" s
LEFT JOIN "Vendor" v ON s."VendorID" = v."VendorID"
LEFT JOIN "ServiceContract" sc ON s."ServiceID" = sc."ServiceID"
GROUP BY s."ServiceID", v."CompanyName", v."VendorID"
ORDER BY s."ServiceType", s."ServiceName";

Result Columns:

ServiceID (UUID)
ServiceName (e.g., "Cable TV Premium")
ServiceType (e.g., "Entertainment")
Description
VendorName (company providing service)
SubscriberCount (active subscriptions)

Create New Service

Query:

INSERT INTO "Service" (
    "ServiceName",
    "ServiceType",
    "Description",
    "VendorID"
) VALUES (
    :serviceName,
    :serviceType,
    :description,
    :vendorId
) RETURNING "ServiceID";

Parameters:

:serviceName - Display name
:serviceType - Category (e.g., "Internet", "Cleaning")
:description - Detailed description
:vendorId - UUID of vendor (can be NULL for admin-created services)

Update Service

Query:

UPDATE "Service"
SET "ServiceName" = :serviceName,
    "ServiceType" = :serviceType,
    "Description" = :description,
    "VendorID" = :vendorId
WHERE "ServiceID" = :serviceId;

Delete Service (UPDATED)

Transaction Flow:

BEGIN TRANSACTION
    ↓
Cancel all active ServiceContracts
    ↓
Delete Service record
    ↓
COMMIT (or ROLLBACK on error)

1. End Active Contracts

UPDATE "ServiceContract"
SET "EndDate" = NOW()
WHERE "ServiceID" = :sid
  AND ("EndDate" IS NULL OR "EndDate" > NOW());

2. Delete Service

DELETE FROM "Service"
WHERE "ServiceID" = :sid;

Features:

Automatically terminates all customer subscriptions before deletion
Transaction-based for data integrity
Accessible to vendors (for their own services) and administrators
JavaScript confirmation dialog warns about contract cancellations
Rollback on any error to maintain referential integrity

Use Case:

Remove discontinued services from catalog
Vendor can clean up obsolete offerings
Ensures all customer subscriptions are properly ended
Maintains audit trail through contract history (EndDate set, not deleted)

Use Case 7: Reports

Page: admin_reports.php

Revenue Report (Monthly)

Query:

SELECT 
    DATE_TRUNC('month', p."PaymentDate") as "Month",
    COUNT(DISTINCT p."PaymentID") as "PaymentCount",
    COUNT(DISTINCT i."InvoiceID") as "InvoiceCount",
    SUM(p."AmountPaid") as "TotalRevenue",
    SUM(CASE WHEN i."InvoiceType" = 'utility' THEN p."AmountPaid" ELSE 0 END) as "UtilityRevenue",
    SUM(CASE WHEN i."InvoiceType" = 'service' THEN p."AmountPaid" ELSE 0 END) as "ServiceRevenue"
FROM "Payment" p
JOIN "Invoice" i ON p."InvoiceID" = i."InvoiceID"
WHERE p."PaymentDate" >= :startDate
  AND p."PaymentDate" <= :endDate
GROUP BY DATE_TRUNC('month', p."PaymentDate")
ORDER BY "Month" DESC;

Parameters:

:startDate - Report period start
:endDate - Report period end

Outstanding Invoices Report

Query:

SELECT 
    i."BillNumber",
    i."InvoiceType",
    i."DateIssued",
    i."DueDate",
    i."TotalPrice",
    i."Status",
    c."FullName" as "CustomerName",
    c."Email" as "CustomerEmail",
    c."PhoneNumber" as "CustomerPhone",
    CASE 
        WHEN i."DueDate" < CURRENT_DATE THEN 'Overdue'
        WHEN i."DueDate" < CURRENT_DATE + INTERVAL '7 days' THEN 'Due Soon'
        ELSE 'Pending'
    END as "Urgency",
    CURRENT_DATE - i."DueDate" as "DaysOverdue"
FROM "Invoice" i
JOIN "Customer" c ON i."CustomerID" = c."CustomerID"
WHERE i."Status" IN ('pending', 'overdue')
ORDER BY i."DueDate" ASC;

Service Subscription Report

Query:

SELECT 
    s."ServiceName",
    s."ServiceType",
    v."CompanyName" as "VendorName",
    COUNT(DISTINCT sc."ServiceContractID") as "ActiveSubscriptions",
    SUM(sc."ContractPrice") as "MonthlyRevenue",
    AVG(sc."ContractPrice") as "AveragePrice"
FROM "Service" s
LEFT JOIN "Vendor" v ON s."VendorID" = v."VendorID"
LEFT JOIN "ServiceContract" sc ON s."ServiceID" = sc."ServiceID"
WHERE sc."EndDate" IS NULL OR sc."EndDate" > CURRENT_DATE
GROUP BY s."ServiceID", s."ServiceName", s."ServiceType", v."CompanyName"
ORDER BY "ActiveSubscriptions" DESC;

Customer Use Cases

Customer Dashboard Overview

Page: customer_dashboard.php

Access Control:

requireRole('customer', 'index.php');
if (requiresPasswordChange()) {
    header("Location: change_password.php");
    exit();
}

Queries Executed on Dashboard Load

1. Get Customer Data

SELECT "CustomerID", "FullName", "DateOfBirth"
FROM "Customer"
WHERE "UserID" = :userId;

Parameters:

:userId - Current user's UUID from session

2. Get Customer Apartments

SELECT 
    a."ApartmentID",
    a."ApartmentNumber",
    a."Building",
    a."Floor",
    a."Occupied",
    ac."StartDate",
    ac."EndDate",
    ac."IsActive",
    ac."Role" as "ContractRole"
FROM "ApartmentContract" ac
JOIN "Apartment" a ON ac."ApartmentID" = a."ApartmentID"
WHERE ac."CustomerID" = :customerId
ORDER BY ac."StartDate" DESC;

Parameters:

:customerId - Customer's UUID

Result Processing:

foreach ($apartments as $apt) {
    if ($apt['IsActive']) {
        $activeApartments++;
    }
}

3. Get Customer Invoices (Pending & Paid)

SELECT 
    "InvoiceID",
    "BillNumber",
    "DateIssued",
    "DueDate",
    "Status",
    "TotalPrice",
    "InvoiceType",
    "MeterReadingID"
FROM "Invoice"
WHERE "CustomerID" = :customerId
ORDER BY "DateIssued" DESC
LIMIT 10;

Result Separation:

foreach ($allInvoices as $invoice) {
    if ($invoice['Status'] === 'paid') {
        $paidInvoices[] = $invoice;
    } else {
        $invoices[] = $invoice;  // Pending/overdue
        if ($invoice['Status'] === 'pending' || $invoice['Status'] === 'overdue') {
            $stats['pending_invoices']++;
            $stats['total_amount_due'] += floatval($invoice['TotalPrice']);
        }
    }
}

4. Get Service Subscriptions

SELECT 
    sc."ServiceContractID",
    s."ServiceName",
    s."ServiceType",
    sc."Subscription",
    sc."ContractPrice",
    sc."StartDate",
    sc."EndDate"
FROM "ServiceContract" sc
JOIN "Service" s ON sc."ServiceID" = s."ServiceID"
WHERE sc."CustomerID" = :customerId
ORDER BY sc."StartDate" DESC;

Dashboard Stats Calculated:

Total apartments
Active apartments
Pending invoices count
Active subscriptions count
Total amount due (sum of pending invoice amounts)

Use Case 1: View Apartment Details

Display Format: Card view and table view

Card View Rendering:

foreach ($apartments as $apt) {
    $roleDisplay = ($apt['ContractRole'] === 'host') ? '🏠 Host' : '🔑 Board';
    $statusColor = $apt['IsActive'] ? '#10b981' : '#d1d5db';
    $statusLabel = $apt['IsActive'] ? 'ACTIVE' : 'INACTIVE';
    
    // Display apartment number, building, floor
    // Display contract role, start date, end date
    // Highlight active status
}

Business Rules:

Host Role: Primary resident, responsible for rent
Board Role: Secondary resident, typically pays utilities
Active Contract: IsActive = true and (EndDate IS NULL OR EndDate > CURRENT_DATE)
Ongoing Contract: EndDate IS NULL

Use Case 2: View and Pay Invoices

View Pending Invoices

Invoices are already loaded on dashboard. Display shows:

Table Columns:

Bill Number (e.g., "UTL-202411-0001", "SVC-202411-0015")
Type (Utility / Service)
Date Issued
Due Date
Amount (formatted as Vietnamese đồng)
Status (Pending / Overdue)

Formatting:

function formatCurrency($amount): string {
    return number_format(floatval($amount), 0, ',', '.') . 'đ';
}

function formatDate($dateString): string {
    $date = new DateTime($dateString);
    return $date->format('M d, Y');
}

Status Display:

if ($invoice['Status'] === 'pending') {
    echo '<span style="color: #f59e0b;">⏳ Pending</span>';
} else {
    echo '<span style="color: #ef4444;">⚠ Overdue</span>';
}

Type Display:

$type = $invoice['InvoiceType'] ?? 'service';
$typeLabel = $type === 'utility' ? 'Utility' : 'Service';
$typeColor = $type === 'utility' ? '#0ea5e9' : '#8b5cf6';

Use Case 3: Batch Payment Selection

Page: batch_payment.php

Access Control:

requireRole('customer', 'index.php');

Query: Get Unpaid Invoices

SELECT 
    i."InvoiceID",
    i."BillNumber",
    i."InvoiceType",
    i."DateIssued",
    i."DueDate",
    i."TotalPrice",
    i."Status"
FROM "Invoice" i
WHERE i."CustomerID" = :customerId
  AND i."Status" IN ('pending', 'overdue')
ORDER BY i."DueDate" ASC;

Parameters:

:customerId - Customer's UUID

Result Display:

Checkbox for each invoice
Bill number, type, due date, amount
"Select All" / "Deselect All" buttons
Real-time total calculation (JavaScript)

JavaScript: Real-Time Total Calculation

File: scripts/batch_payment.js

function updateTotals() {
    const checkboxes = document.querySelectorAll('input[name="invoice_ids[]"]:checked');
    let total = 0;
    let count = 0;
    
    checkboxes.forEach(checkbox => {
        const amount = parseFloat(checkbox.dataset.amount || 0);
        total += amount;
        count++;
    });
    
    document.getElementById('selected-count').textContent = count;
    document.getElementById('total-amount').textContent = formatCurrency(total);
}

// Format number as Vietnamese currency
function formatCurrency(amount) {
    return new Intl.NumberFormat('vi-VN').format(amount) + 'đ';
}

Event Listeners:

document.querySelectorAll('input[name="invoice_ids[]"]').forEach(checkbox => {
    checkbox.addEventListener('change', updateTotals);
});

document.getElementById('select-all').addEventListener('click', () => {
    document.querySelectorAll('input[name="invoice_ids[]"]').forEach(cb => {
        cb.checked = true;
    });
    updateTotals();
});

Form Submission

Validation:

if (empty($_POST['invoice_ids'])) {
    $errors[] = "Please select at least one invoice to pay.";
}

if (empty($_POST['payment_method'])) {
    $errors[] = "Please select a payment method.";
}

Redirect to Payment Gateway:

if ($_POST['payment_method'] === 'vnpay') {
    header("Location: start_vnpay_batch.php");
    exit();
}

Use Case 4: View All Invoices (NEW)

Page: customer_view_invoices.php

Access Control:

requireRole('customer', 'index.php');

Features:

Comprehensive invoice listing with filtering and pagination
Integrated CSV export functionality
Real-time filtering by status, type, and date range
Consistent UI matching admin interface
Uses common helper functions for formatting

Query: Get Customer Invoices with Details

SELECT 
    i."InvoiceID",
    i."BillNumber",
    i."DateIssued",
    i."DueDate",
    i."TotalPrice",
    i."Status",
    i."InvoiceType",
    STRING_AGG(DISTINCT s."ServiceName", ', ') AS "Services",
    p."PaymentDate"
FROM "Invoice" i
LEFT JOIN "InvoiceDetail" id ON i."InvoiceID" = id."InvoiceID"
LEFT JOIN "ServiceContract" sc ON id."ServiceContractID" = sc."ServiceContractID"
LEFT JOIN "Service" s ON sc."ServiceID" = s."ServiceID"
LEFT JOIN "Payment" p ON i."InvoiceID" = p."InvoiceID"
WHERE i."CustomerID" = :customerId
  AND (:statusFilter = '' OR i."Status" = :statusFilter)
  AND (:typeFilter = '' OR i."InvoiceType" = :typeFilter)
  AND (:startDate = '' OR i."DateIssued" >= :startDate)
  AND (:endDate = '' OR i."DateIssued" <= :endDate)
GROUP BY i."InvoiceID", p."PaymentDate"
ORDER BY i."DateIssued" DESC
LIMIT :limit OFFSET :offset;

Parameters:

:customerId - Customer's UUID (filtered automatically)
:statusFilter - 'paid', 'pending', 'overdue', or '' for all
:typeFilter - 'utility', 'service', or '' for all
:startDate - Date range start (optional)
:endDate - Date range end (optional)
:limit - Results per page (default: 20)
:offset - Pagination offset

Display Features:

Color-coded status badges (green=paid, yellow=pending, red=overdue)
Type badges (blue=utility, purple=service)
Service names aggregation for service invoices
Currency formatting using formatCurrency()
Date formatting using formatDate()
Export button redirects to customer_export_invoices.php with same filters

Updated Features:

Fixed SQL column names: BillNumber, DateIssued, TotalPrice
Fixed table references: InvoiceDetail (not InvoiceLineItem)
Lowercase status values matching database ENUMs
PDO prepared statements throughout
Added common_helpers.php include for utility functions

Use Case 5: Export Invoices to CSV (UPDATED)

Page: customer_export_invoices.php

Access Control:

requireRole('customer', 'index.php');

Query: Get All Invoices with Service Details

SELECT 
    i."BillNumber",
    i."InvoiceType",
    i."DateIssued",
    i."DueDate",
    i."TotalPrice",
    i."Status",
    STRING_AGG(DISTINCT s."ServiceName", ', ') as "ServiceNames",
    STRING_AGG(DISTINCT s."ServiceType", ', ') as "ServiceTypes",
    p."PaymentDate",
    p."PaymentMethod",
    p."TransactionReference"
FROM "Invoice" i
LEFT JOIN "InvoiceDetail" id ON i."InvoiceID" = id."InvoiceID"
LEFT JOIN "ServiceContract" sc ON id."ServiceContractID" = sc."ServiceContractID"
LEFT JOIN "Service" s ON sc."ServiceID" = s."ServiceID"
LEFT JOIN "Payment" p ON i."InvoiceID" = p."InvoiceID"
WHERE i."CustomerID" = :customerId
GROUP BY i."InvoiceID", p."PaymentDate", p."PaymentMethod", p."TransactionReference"
ORDER BY i."DateIssued" DESC;

Parameters:

:customerId - Customer's UUID

Result Columns:

BillNumber
InvoiceType
DateIssued, DueDate
TotalPrice
Status
ServiceNames (comma-separated, for service invoices)
ServiceTypes (comma-separated)
PaymentDate, PaymentMethod, TransactionReference (for paid invoices)

CSV Generation

Headers:

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="invoices_export_' . date('Y-m-d') . '.csv"');

// UTF-8 BOM for Excel compatibility
echo "\xEF\xBB\xBF";

Column Headers:

$columns = [
    'Bill Number',
    'Type',
    'Service Name(s)',
    'Service Type(s)',
    'Date Issued',
    'Due Date',
    'Amount (đ)',
    'Status',
    'Payment Date',
    'Payment Method',
    'Transaction Reference'
];

fputcsv($output, $columns);

Data Rows:

foreach ($invoices as $invoice) {
    $row = [
        $invoice['BillNumber'],
        $invoice['InvoiceType'] === 'utility' ? 'Utility' : 'Service',
        $invoice['ServiceNames'] ?? 'N/A',
        $invoice['ServiceTypes'] ?? 'N/A',
        formatDate($invoice['DateIssued']),
        formatDate($invoice['DueDate']),
        formatCurrency($invoice['TotalPrice']),
        ucfirst($invoice['Status']),
        $invoice['PaymentDate'] ? formatDate($invoice['PaymentDate']) : 'N/A',
        $invoice['PaymentMethod'] ?? 'N/A',
        $invoice['TransactionReference'] ?? 'N/A'
    ];
    
    fputcsv($output, $row);
}

Currency Formatting:

function formatCurrency($amount): string {
    return number_format(floatval($amount), 0, ',', '.');  // No 'đ' in CSV
}

Use Case 5: Request Service Subscription

Page: customer_request_service.php

Access Control:

requireRole('customer', 'index.php');

Query: Get Available Services

SELECT 
    s."ServiceID",
    s."ServiceName",
    s."ServiceType",
    s."Description",
    v."CompanyName" as "VendorName",
    v."PhoneNumber" as "VendorPhone"
FROM "Service" s
LEFT JOIN "Vendor" v ON s."VendorID" = v."VendorID"
WHERE v."ApprovalStatus" = 'approved' OR s."VendorID" IS NULL
ORDER BY s."ServiceType", s."ServiceName";

Display:

Service name and type
Description
Vendor information
"Subscribe" button

Create Service Subscription

Transaction Flow:

BEGIN TRANSACTION
    ↓
1. Validate customer hasn't already subscribed
    ↓
2. Insert ServiceContract
    ↓
3. Create initial invoice (optional)
    ↓
COMMIT

1. Check Existing Subscription

SELECT COUNT(*) as subscription_count
FROM "ServiceContract"
WHERE "ServiceID" = :serviceId
  AND "CustomerID" = :customerId
  AND ("EndDate" IS NULL OR "EndDate" > CURRENT_DATE);

If count > 0: Show error "You are already subscribed to this service."

2. Insert Service Contract

INSERT INTO "ServiceContract" (
    "ServiceID",
    "CustomerID",
    "Subscription",
    "ContractPrice",
    "StartDate",
    "EndDate"
) VALUES (
    :serviceId,
    :customerId,
    :subscription,  -- 'monthly', 'quarterly', 'annually'
    :contractPrice,
    CURRENT_DATE,
    NULL  -- Ongoing subscription
) RETURNING "ServiceContractID";

Parameters:

:serviceId - Selected service UUID
:customerId - Customer UUID
:subscription - Billing frequency (from form)
:contractPrice - Price based on subscription type

3. Create Initial Invoice (Optional)

Some implementations create an invoice immediately upon subscription.

INSERT INTO "Invoice" (
    "CustomerID",
    "BillNumber",
    "InvoiceType",
    "DateIssued",
    "DueDate",
    "Status",
    "TotalPrice"
) VALUES (
    :customerId,
    :billNumber,
    'service',
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '30 days',
    'pending',
    :contractPrice
) RETURNING "InvoiceID";

4. Create Invoice Detail

INSERT INTO "InvoiceDetail" (
    "InvoiceID",
    "ServiceContractID",
    "Description",
    "Quantity",
    "UnitPrice",
    "Amount"
) VALUES (
    :invoiceId,
    :serviceContractId,
    :description,  -- e.g., "Cable TV Premium - November 2025"
    1,
    :contractPrice,
    :contractPrice
);

Use Case 6: View Payment History

Paid invoices are displayed on the customer dashboard in a separate section.

Table Display:

Bill Number
Type (Utility / Service)
Date Issued
Due Date
Amount
Status (✓ Paid)
Slightly faded appearance (opacity: 0.8)

No Additional Queries: Payment history uses the same invoice query from dashboard load, filtered by Status = 'paid'.

Use Case 7: View Service Subscriptions

Active subscriptions are displayed on the customer dashboard.

Table Columns:

Service Name
Type
Subscription Frequency (Monthly / Quarterly / Annually)
Price
Start Date
Actions (Cancel button)

Query:

SELECT 
    sc."ServiceContractID",
    s."ServiceName",
    s."ServiceType",
    sc."Subscription",
    sc."ContractPrice",
    sc."StartDate",
    sc."EndDate"
FROM "ServiceContract" sc
JOIN "Service" s ON sc."ServiceID" = s."ServiceID"
WHERE sc."CustomerID" = :customerId
ORDER BY sc."StartDate" DESC;

Parameters:

:customerId - Current customer's UUID

Use Case 8: Cancel Service Subscription

Page: customer_dashboard.php

Trigger: Customer clicks "Cancel" button next to a subscription

Security:

CSRF token validation
Ownership verification (CustomerID match)

Flow:

1. Verify CSRF Token

$csrfToken = $_SESSION['csrf_token'] ?? '';
$submittedToken = $_POST['csrf_token'] ?? '';

if (!hash_equals($csrfToken, $submittedToken)) {
    throw new Exception("Invalid security token.");
}

2. Get Customer ID

SELECT "CustomerID" 
FROM "Customer" 
WHERE "UserID" = :userId;

3. Delete Service Contract

DELETE FROM "ServiceContract"
WHERE "ServiceContractID" = :contractId
AND "CustomerID" = :customerId
RETURNING "ServiceContractID";

Parameters:

:contractId - UUID of subscription to cancel
:customerId - Current customer's UUID

Security Note: The AND "CustomerID" = :customerId clause ensures customers can only cancel their own subscriptions.

Business Rules:

Immediate cancellation (no refunds)
Subscription is permanently deleted from database
Related invoices remain for historical records
Customer is redirected back to dashboard with success/error message

Confirmation: JavaScript confirmation dialog before submission:

onsubmit="return confirm('Are you sure you want to cancel this subscription? This action cannot be undone.');"

Success Message: "Subscription cancelled successfully."

Error Scenarios:

Invalid CSRF token → "Invalid security token. Please try again."
Subscription not found → "Subscription not found or you don't have permission to cancel it."
Database error → Error logged, generic error message shown

Vendor Use Cases

Vendor Dashboard Overview

Page: vendor_dashboard.php

Access Control:

requireRole('vendor', 'index.php');
if (requiresPasswordChange()) {
    header("Location: change_password.php");
    exit();
}

Queries Executed on Dashboard Load

1. Get Vendor Data

SELECT 
    "VendorID",
    "CompanyName",
    "ApprovalStatus",
    "ServiceType"
FROM "Vendor"
WHERE "UserID" = :userId;

Parameters:

:userId - Current user's UUID

2. Get Vendor's Service Contracts

SELECT 
    sc."ServiceContractID",
    s."ServiceName",
    s."ServiceType",
    sc."Subscription",
    sc."ContractPrice",
    sc."StartDate",
    c."FullName" as "CustomerName"
FROM "ServiceContract" sc
JOIN "Service" s ON sc."ServiceID" = s."ServiceID"
JOIN "Customer" c ON sc."CustomerID" = c."CustomerID"
WHERE s."VendorID" = :vendorId
ORDER BY sc."StartDate" DESC;

3. Get Vendor's Invoices (Filtered by VendorID)

SELECT 
    i."InvoiceID",
    i."BillNumber",
    i."DateIssued",
    i."DueDate",
    i."Status",
    i."TotalPrice",
    c."FullName" as "CustomerName"
FROM "Invoice" i
JOIN "Customer" c ON i."CustomerID" = c."CustomerID"
WHERE i."VendorID" = :vendorId
ORDER BY i."DateIssued" DESC
LIMIT 20;

Parameters:

:vendorId - Vendor's UUID

Security Note: This query ensures vendors only see invoices they created (where Invoice.VendorID matches their ID).

Use Case 1: Create Invoice

Page: vendor_create_invoice.php

Access Control:

requireRole('vendor', 'index.php');

// Check if vendor is approved
if ($vendorData['ApprovalStatus'] !== 'approved') {
    die("Your vendor account is pending approval.");
}

Flow Diagram

Select customer
    ↓
Select service(s)
    ↓
Enter quantities and prices
    ↓
Calculate total
    ↓
Generate bill number
    ↓
Create invoice
    ↓
Create invoice details

Query: Get Customers

SELECT 
    c."CustomerID",
    c."FullName",
    c."Email",
    c."PhoneNumber"
FROM "Customer" c
ORDER BY c."FullName";

Query: Get Vendor's Services

SELECT 
    "ServiceID",
    "ServiceName",
    "ServiceType",
    "Description"
FROM "Service"
WHERE "VendorID" = :vendorId
ORDER BY "ServiceType", "ServiceName";

Parameters:

:vendorId - Vendor's UUID

Create Invoice Transaction

Transaction Flow:

BEGIN TRANSACTION
    ↓
1. Get VendorID from session
    ↓
2. Generate bill number
    ↓
3. Calculate total from line items
    ↓
4. Insert Invoice with VendorID
    ↓
5. Insert InvoiceDetail rows
    ↓
COMMIT

1. Get Vendor ID

SELECT "VendorID"
FROM "Vendor"
WHERE "UserID" = :userId;

2. Generate Bill Number

$billNumber = 'SVC-' . date('Ym') . '-' . str_pad($sequence, 4, '0', STR_PAD_LEFT);
// Example: SVC-202411-0015

3. Calculate Total

$totalPrice = 0;
foreach ($invoiceItems as $item) {
    $amount = $item['quantity'] * $item['unitPrice'];
    $totalPrice += $amount;
}

4. Insert Invoice (WITH VendorID)

INSERT INTO "Invoice" (
    "CustomerID",
    "VendorID",  -- CRITICAL: Links invoice to vendor
    "BillNumber",
    "InvoiceType",
    "DateIssued",
    "DueDate",
    "Status",
    "TotalPrice"
) VALUES (
    :customerId,
    :vendorId,  -- From session
    :billNumber,
    'service',
    CURRENT_DATE,
    :dueDate,
    'pending',
    :totalPrice
) RETURNING "InvoiceID";

Parameters:

:customerId - Selected customer UUID
:vendorId - Vendor's UUID from session (ensures invoice ownership)
:billNumber - Generated bill number
:dueDate - Calculated due date (e.g., +30 days)
:totalPrice - Sum of all line items

5. Insert Invoice Details

INSERT INTO "InvoiceDetail" (
    "InvoiceID",
    "ServiceContractID",
    "Description",
    "Quantity",
    "UnitPrice",
    "Amount"
) VALUES (
    :invoiceId,
    :serviceContractId,  -- Can be NULL for one-time services
    :description,
    :quantity,
    :unitPrice,
    :amount
);

Repeat for each line item:

Loop through $_POST['services'] array
Insert one InvoiceDetail row per service

Validation:

// Ensure vendor owns the service being invoiced
$serviceCheck = $pdo->prepare('
    SELECT COUNT(*) as count
    FROM "Service"
    WHERE "ServiceID" = :serviceId AND "VendorID" = :vendorId
');
$serviceCheck->execute([':serviceId' => $serviceId, ':vendorId' => $vendorId]);
$result = $serviceCheck->fetch();

if ($result['count'] == 0) {
    throw new Exception("You can only invoice for your own services.");
}

Use Case 2: View Invoice Status (UPDATED)

Page: vendor_view_invoices.php (NEW)

Comprehensive invoice viewing page for vendors with filtering and export capabilities.

Access Control:

requireRole('vendor', 'index.php');

Features:

View all invoices created by the vendor
Filter by status, type, and date range
Pagination (20 invoices per page)
Integrated CSV export
Consistent UI matching admin and customer interfaces

Query: Get Vendor Invoices with Details

SELECT 
    i."InvoiceID",
    i."BillNumber",
    i."DateIssued",
    i."DueDate",
    i."TotalPrice",
    i."Status",
    i."InvoiceType",
    c."FullName" AS "CustomerName",
    STRING_AGG(DISTINCT s."ServiceName", ', ') AS "Services",
    p."PaymentDate"
FROM "Invoice" i
LEFT JOIN "Customer" c ON i."CustomerID" = c."CustomerID"
LEFT JOIN "InvoiceDetail" id ON i."InvoiceID" = id."InvoiceID"
LEFT JOIN "ServiceContract" sc ON id."ServiceContractID" = sc."ServiceContractID"
LEFT JOIN "Service" s ON sc."ServiceID" = s."ServiceID"
LEFT JOIN "Payment" p ON i."InvoiceID" = p."InvoiceID"
WHERE i."VendorID" = :vendorId
  AND (:statusFilter = '' OR i."Status" = :statusFilter)
  AND (:typeFilter = '' OR i."InvoiceType" = :typeFilter)
  AND (:startDate = '' OR i."DateIssued" >= :startDate)
  AND (:endDate = '' OR i."DateIssued" <= :endDate)
GROUP BY i."InvoiceID", c."FullName", p."PaymentDate"
ORDER BY i."DateIssued" DESC
LIMIT :limit OFFSET :offset;

Parameters:

:vendorId - Vendor's UUID (filtered automatically from session)
:statusFilter - 'paid', 'pending', 'overdue', or '' for all
:typeFilter - 'utility', 'service', or '' for all
:startDate, :endDate - Optional date range
:limit, :offset - Pagination

Display Features:

Customer name from Customer.FullName (not User table)
Service names aggregated for service invoices
Color-coded status badges
Currency and date formatting using common helpers

Schema Fixes Applied:

Corrected column names: BillNumber, DateIssued, TotalPrice
Corrected table: InvoiceDetail (not InvoiceLineItem)
Fixed customer name: c."FullName" from Customer table
Lowercase status values matching ENUMs

Export Functionality

Page: vendor_export_invoices.php (NEW)

Exports vendor invoices to CSV with same filtering as view page.

Query: Similar to view page but returns all matching invoices (no pagination)

CSV Features:

UTF-8 BOM for Excel compatibility
Headers: Bill Number, Customer, Type, Services, Date Issued, Due Date, Amount, Status, Payment Date
Filename: vendor_invoices_export_YYYY-MM-DD.csv

Status Display:

Pending: Yellow ⏳
Paid: Green ✓
Overdue: Red ⚠

No Edit/Delete: Once created, invoices cannot be modified by vendors (business rule).

Use Case 3: Update Vendor Profile

Page: vendor_update_profile.php

Query: Get Current Profile

SELECT 
    "CompanyName",
    "ContactPerson",
    "PhoneNumber",
    "Email",
    "Address",
    "ServiceType"
FROM "Vendor"
WHERE "UserID" = :userId;

Update Profile

UPDATE "Vendor"
SET "CompanyName" = :companyName,
    "ContactPerson" = :contactPerson,
    "PhoneNumber" = :phoneNumber,
    "Email" = :email,
    "Address" = :address,
    "ServiceType" = :serviceType
WHERE "UserID" = :userId;

Parameters:

All form fields (validated and sanitized)
:userId - Current user's UUID

Validation:

if (empty($companyName)) {
    $errors[] = "Company name is required.";
}

if (!empty($email) && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
    $errors[] = "Invalid email format.";
}

if (!empty($phoneNumber) && !preg_match('/^[0-9\-\+\s\(\)]+$/', $phoneNumber)) {
    $errors[] = "Invalid phone number format.";
}

VNPay Payment Integration

Overview

VNPay is Vietnam's leading payment gateway. The integration allows customers to pay multiple invoices in one transaction.

Payment Flow:

Customer selects invoices
    ↓
Creates BatchPayment record
    ↓
Generates VNPayTransaction reference
    ↓
Redirects to VNPay sandbox
    ↓
Customer completes payment
    ↓
VNPay redirects back with signature
    ↓
System verifies signature
    ↓
Matches invoices by amount
    ↓
Creates Payment records
    ↓
Updates invoice statuses
    ↓
Shows success message
    ↓
Auto-redirects to dashboard after 5s

Configuration

File: creds.php

putenv("VNP_TMN_CODE=0P5OS3KZ");
putenv("VNP_HASH_SECRET=your_secret_key_here");
putenv("VNP_URL=https://sandbox.vnpayment.vn/paymentv2/vpcpay.html");
putenv("VNP_RETURN_URL=https://cos20031.ascensionfm.works/vnpay_batch_return.php");

Important: Use HTTPS for production deployment.

Use Case 1: Initiate Batch Payment

Page: start_vnpay_batch.php

Access Control:

requireRole('customer', 'index.php');

Transaction Flow

BEGIN TRANSACTION
    ↓
1. Validate invoice selection
    ↓
2. Calculate total amount
    ↓
3. Create BatchPayment record
    ↓
4. Create VNPayTransaction record
    ↓
5. Generate VNPay parameters
    ↓
6. Sign parameters with HMAC-SHA512
    ↓
7. Redirect to VNPay URL
    ↓
COMMIT

Queries Executed

1. Get Customer ID

SELECT "CustomerID"
FROM "Customer"
WHERE "UserID" = :userId;

2. Validate and Calculate Invoice Total

SELECT 
    "InvoiceID",
    "TotalPrice"
FROM "Invoice"
WHERE "InvoiceID" = ANY(:invoiceIds)
  AND "CustomerID" = :customerId
  AND "Status" IN ('pending', 'overdue');

Parameters:

:invoiceIds - Array of selected invoice UUIDs
:customerId - Customer's UUID

Security: Ensures customer can only pay their own invoices.

3. Create BatchPayment

INSERT INTO "BatchPayment" (
    "CustomerID",
    "TotalAmount",
    "PaymentMethod",
    "Status"
) VALUES (
    :customerId,
    :totalAmount,
    'vnpay',
    'pending'
) RETURNING "BatchPaymentID";

Parameters:

:customerId - Customer UUID
:totalAmount - Sum of selected invoice amounts

4. Ensure VNPayTransaction Table Exists

CREATE TABLE IF NOT EXISTS "COS20031"."VNPayTransaction" (
    "TxnRef" VARCHAR(100) PRIMARY KEY,
    "InvoiceID" UUID REFERENCES "COS20031"."Invoice"("InvoiceID") ON DELETE SET NULL,
    "BatchPaymentID" UUID REFERENCES "COS20031"."BatchPayment"("BatchPaymentID") ON DELETE SET NULL,
    "Amount" DECIMAL(10, 2) NOT NULL,
    "OrderInfo" TEXT,
    "ResponseCode" VARCHAR(10),
    "TransactionNo" VARCHAR(100),
    "BankCode" VARCHAR(20),
    "PayDate" VARCHAR(14),
    "CreatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5. Generate Transaction Reference

$vnp_TxnRef = date('YmdHis');  // Example: 20251111143022

6. Create VNPayTransaction Record

INSERT INTO "VNPayTransaction" (
    "TxnRef",
    "BatchPaymentID",
    "Amount",
    "OrderInfo"
) VALUES (
    :txnRef,
    :batchPaymentId,
    :totalAmount,
    :orderInfo
);

Parameters:

:txnRef - Generated timestamp-based ID
:batchPaymentId - UUID from BatchPayment
:totalAmount - Total payment amount (in đồng)
:orderInfo - Description (e.g., "Payment for 3 invoices")

VNPay Parameter Construction

Required Parameters:

$vnp_Params = [
    'vnp_Version' => '2.1.0',
    'vnp_Command' => 'pay',
    'vnp_TmnCode' => getenv('VNP_TMN_CODE'),
    'vnp_Amount' => $totalAmount * 100,  // Convert đồng to VNPay format (multiply by 100)
    'vnp_CurrCode' => 'VND',
    'vnp_TxnRef' => $vnp_TxnRef,
    'vnp_OrderInfo' => $orderInfo,
    'vnp_OrderType' => 'billpayment',
    'vnp_Locale' => 'vn',
    'vnp_ReturnUrl' => getenv('VNP_RETURN_URL'),
    'vnp_IpAddr' => $_SERVER['REMOTE_ADDR'],
    'vnp_CreateDate' => date('YmdHis')
];

Critical: vnp_Amount must be multiplied by 100 (VNPay requirement).

HMAC-SHA512 Signature

1. Sort Parameters Alphabetically

ksort($vnp_Params);

2. Build Query String

$query = "";
foreach ($vnp_Params as $key => $value) {
    if (strlen($value) > 0) {
        $query .= urlencode($key) . "=" . urlencode($value) . "&";
    }
}
$query = rtrim($query, '&');

3. Generate Signature

$vnp_HashSecret = getenv('VNP_HASH_SECRET');
$vnpSecureHash = hash_hmac('sha512', $query, $vnp_HashSecret);

4. Append Signature

$vnp_Url = getenv('VNP_URL') . '?' . $query . '&vnp_SecureHash=' . $vnpSecureHash;

Redirect to VNPay

header("Location: $vnp_Url");
exit();

Use Case 2: Process VNPay Callback

Page: vnpay_batch_return.php

No Authentication Required: VNPay callback may arrive after session expires. Page shows conditional UI based on login status.

Flow Diagram

VNPay redirects back with parameters
    ↓
Extract GET parameters
    ↓
Verify HMAC signature
    ↓
Check response code (00 = success)
    ↓
Find BatchPayment by TxnRef
    ↓
Select invoices matching batch amount
    ↓
BEGIN TRANSACTION
    ↓
Create Payment records for each invoice
    ↓
Update invoice statuses to 'paid'
    ↓
Update BatchPayment status to 'completed'
    ↓
Update VNPayTransaction with response data
    ↓
COMMIT
    ↓
Show success/failure message
    ↓
Auto-redirect after 5 seconds

Extract and Verify Parameters

GET Parameters from VNPay:

vnp_TxnRef - Transaction reference
vnp_Amount - Amount (multiplied by 100)
vnp_ResponseCode - '00' for success
vnp_TransactionNo - VNPay transaction number
vnp_BankCode - Bank code used
vnp_PayDate - Payment timestamp (YYYYMMDDHHmmss)
vnp_SecureHash - HMAC signature

Signature Verification:

$vnp_SecureHash = $_GET['vnp_SecureHash'];
$inputData = $_GET;
unset($inputData['vnp_SecureHash']);

ksort($inputData);
$query = "";
foreach ($inputData as $key => $value) {
    if (strlen($value) > 0) {
        $query .= urlencode($key) . "=" . urlencode($value) . "&";
    }
}
$query = rtrim($query, '&');

$vnp_HashSecret = getenv('VNP_HASH_SECRET');
$secureHash = hash_hmac('sha512', $query, $vnp_HashSecret);

if (!hash_equals($secureHash, $vnp_SecureHash)) {
    die("Invalid signature - possible tampering");
}

Security: Constant-time comparison with hash_equals().

Find BatchPayment

Query:

SELECT 
    bp."BatchPaymentID",
    bp."CustomerID",
    bp."TotalAmount",
    bp."Status"
FROM "BatchPayment" bp
JOIN "VNPayTransaction" vt ON bp."BatchPaymentID" = vt."BatchPaymentID"
WHERE vt."TxnRef" = :txnRef;

Parameters:

:txnRef - From $_GET['vnp_TxnRef']

Select Invoices to Pay (Amount Matching)

Query:

SELECT 
    "InvoiceID",
    "TotalPrice"
FROM "Invoice"
WHERE "CustomerID" = :customerId
  AND "Status" IN ('pending', 'overdue')
ORDER BY "DueDate" ASC;

Amount Matching Logic:

$targetAmount = $batchPayment['TotalAmount'];
$runningTotal = 0;
$selectedInvoices = [];

foreach ($allInvoices as $invoice) {
    if ($runningTotal >= $targetAmount) {
        break;
    }
    
    $selectedInvoices[] = $invoice;
    $runningTotal += floatval($invoice['TotalPrice']);
}

// Verify exact match
if (abs($runningTotal - $targetAmount) > 0.01) {
    throw new Exception("Invoice amount mismatch");
}

Why Amount Matching? The callback doesn't receive invoice IDs (they're not in session), so we select invoices by due date until we match the batch total.

Create Payments and Update Statuses

Transaction:

BEGIN;

-- For each selected invoice:
INSERT INTO "Payment" (
    "InvoiceID",
    "PaymentMethod",
    "AmountPaid",
    "BatchPaymentID",
    "TransactionReference"
) VALUES (
    :invoiceId,
    'vnpay',
    :amountPaid,
    :batchPaymentId,
    :transactionNo
);

UPDATE "Invoice"
SET "Status" = 'paid'
WHERE "InvoiceID" = :invoiceId;

-- After all invoices:
UPDATE "BatchPayment"
SET "Status" = 'completed'
WHERE "BatchPaymentID" = :batchPaymentId;

UPDATE "VNPayTransaction"
SET "ResponseCode" = :responseCode,
    "TransactionNo" = :transactionNo,
    "BankCode" = :bankCode,
    "PayDate" = :payDate
WHERE "TxnRef" = :txnRef;

COMMIT;

Parameters:

:invoiceId - UUID of each invoice
:amountPaid - Invoice total price
:batchPaymentId - UUID from batch payment
:transactionNo - From $_GET['vnp_TransactionNo']
:responseCode - From $_GET['vnp_ResponseCode']
:bankCode - From $_GET['vnp_BankCode']
:payDate - From $_GET['vnp_PayDate']
:txnRef - Transaction reference

Display Result

Success Message (responseCode = '00'):

<div class="payment-success">
    <h2>✓ Payment Successful!</h2>
    <p>Transaction: <?= htmlspecialchars($transactionNo) ?></p>
    <p>Amount: <?= formatCurrency($amountPaid) ?></p>
    <p>Invoices Paid: <?= $invoiceCount ?></p>
</div>

Failure Message (responseCode != '00'):

<div class="payment-failure">
    <h2>⚠ Payment Failed</h2>
    <p>Response Code: <?= htmlspecialchars($responseCode) ?></p>
    <p>Please try again or contact support.</p>
</div>

Auto-Redirect Script

JavaScript:

// Auto-redirect after 5 seconds
let countdown = 5;
const countdownElement = document.getElementById('countdown');
const redirectUrl = <?= isLoggedIn() ? '"customer_dashboard.php#invoices"' : '"index.php"' ?>;

const timer = setInterval(() => {
    countdown--;
    countdownElement.textContent = countdown;
    
    if (countdown <= 0) {
        clearInterval(timer);
        window.location.href = redirectUrl;
    }
}, 1000);

// Cancel redirect if user clicks anywhere
document.addEventListener('click', () => {
    clearInterval(timer);
    document.getElementById('redirect-message').style.display = 'none';
}, { once: true });

VNPay Response Codes

Code	Meaning
00	Success
07	Transaction suspected of fraud
09	Customer's card not registered for Internet Banking
10	Customer failed authentication more than 3 times
11	Payment timeout
12	Customer's account locked
13	Incorrect OTP
24	Customer cancelled transaction
51	Insufficient balance
65	Customer exceeded daily transaction limit
75	Payment bank under maintenance
79	Payment amount exceeds limit
99	Unknown error

Testing VNPay Sandbox

Test Card Numbers:

Bank: NCB (National Citizen Bank)
Card Number: 9704198526191432198
Card Holder: NGUYEN VAN A
Expiry: 07/15
OTP: 123456

Test Scenarios:

Successful payment (use above card)
Cancelled payment (close VNPay window)
Timeout (wait 15 minutes without paying)
Invalid signature (tamper with return URL parameters)

Helper Functions & Utilities

Common Helper Functions

File: common_helpers.php (NEW)

A centralized collection of utility functions used across the application for consistent formatting and security.

formatCurrency($amount)

Formats a numeric value as Vietnamese Dong currency.

function formatCurrency($amount): string {
    return number_format((float)$amount, 0, ',', '.') . 'đ';
}

Parameters:

$amount (numeric): The amount to format

Returns: string - Formatted currency string (e.g., "1.000.000đ")

Usage: All invoice pages, payment displays, service pricing

formatDate($dateString)

Formats a date/time string for consistent display.

function formatDate($dateString): string {
    if (!$dateString) return 'N/A';
    try {
        return (new DateTime($dateString))->format('M d, Y H:i');
    } catch (Exception $e) {
        return 'Invalid Date';
    }
}

Parameters:

$dateString (string): Date string in any PHP-parseable format

Returns: string - Formatted date (e.g., "Nov 12, 2025 14:30") or "N/A"/"Invalid Date"

Usage: Invoice dates, contract dates, timestamp displays

h($string)

HTML escapes a string to prevent XSS attacks.

function h($string): string {
    return htmlspecialchars((string)$string, ENT_QUOTES, 'UTF-8');
}

Parameters:

$string (mixed): Value to escape (converted to string)

Returns: string - HTML-safe string

Security: Prevents XSS by escaping special HTML characters

Usage: All user-generated content display, form values

Database Connection

File: Multiple files (duplicated pattern)

function createDatabaseConnection(): PDO {
    $host = getenv("DB_HOST");
    $port = getenv("DB_PORT");
    $dbName = getenv("DB_NAME");
    $user = getenv("DB_USER");
    $password = getenv("DB_PASSWORD");

    if ($host === false || $port === false || $dbName === false || 
        $user === false || $password === false) {
        throw new RuntimeException("Database credentials not properly configured");
    }

    $dsn = sprintf("pgsql:host=%s;port=%s;dbname=%s", $host, $port, $dbName);
    
    $pdo = new PDO($dsn, $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    // Set the search path to COS20031 schema
    $pdo->exec('SET search_path TO "COS20031", public');

    return $pdo;
}

Configuration:

Uses environment variables from creds.php
Sets PostgreSQL schema to COS20031
Enables exception mode for error handling
Returns associative arrays by default

Authentication Helpers

File: auth_helper.php

verifyAuthCookie()

Verifies and decodes the signed authentication cookie.

Returns: array|null - User data if valid, null otherwise

Security Features:

Base64 decoding
HMAC-SHA256 signature verification
Timestamp expiration check (30 days)
Constant-time comparison

getUserId()

Get the current user's UUID.

Returns: string|null

Fallback Order:

Session variable $_SESSION['auth_user_id']
Verified auth cookie user_auth
NULL if not authenticated

getUsername()

Get the current user's username.

Returns: string|null

Fallback Order:

Session variable $_SESSION['auth_username']
Verified auth cookie
NULL

getUserRole()

Get the current user's role.

Returns: string|null - 'administrator', 'customer', 'vendor', or NULL

Fallback Order:

Session variable $_SESSION['auth_role']
Verified auth cookie
NULL

isAuthenticated()

Check if user is logged in.

Returns: bool

Implementation:

function isAuthenticated(): bool {
    return getUserId() !== null;
}

hasRole(string $role)

Check if user has a specific role.

Parameters:

$role - Role to check ('administrator', 'customer', 'vendor')

Returns: bool

Implementation:

function hasRole(string $role): bool {
    $userRole = getUserRole();
    return $userRole !== null && $userRole === $role;
}

requireAuth(string $redirectUrl = 'index.php')

Require authentication or redirect.

Parameters:

$redirectUrl - Where to redirect if not authenticated

Returns: void

Side Effects: May terminate script with exit()

requireRole(string $role, string $redirectUrl = 'index.php')

Require a specific role or redirect.

Parameters:

$role - Required role
$redirectUrl - Where to redirect if unauthorized

Returns: void

Side Effects: May terminate script with exit()

requiresPasswordChange()

Check if user must change password.

Returns: bool

Query:

SELECT "RequirePasswordChange" 
FROM "User" 
WHERE "UserID" = :userId

Use Case: Force new users to set their own password on first login.

logout()

Clear authentication session and cookies.

Returns: void

Actions:

Clear all session variables
Destroy session cookie
Destroy session data on server
Clear user_auth cookie

getEnvOrFail(string $key)

Get environment variable or throw exception.

Parameters:

$key - Environment variable name

Returns: string

Throws: RuntimeException if not set or empty

Use Case: Fail fast if critical configuration is missing.

Formatting Functions

formatCurrency(mixed $amount)

Format amount as Vietnamese đồng.

Parameters:

$amount - Numeric amount

Returns: string

Implementation:

function formatCurrency($amount): string {
    return number_format(floatval($amount), 0, ',', '.') . 'đ';
}

Examples:

formatCurrency(1000000) → "1.000.000đ"
formatCurrency(50000.50) → "50.001đ" (rounded)

Note: Vietnamese đồng has no decimals, amounts are rounded to integers.

formatDate(string $dateString)

Format date in readable format.

Parameters:

$dateString - ISO date string (from PostgreSQL)

Returns: string

Implementation:

function formatDate($dateString): string {
    if (!$dateString) return 'N/A';
    try {
        $date = new DateTime($dateString);
        return $date->format('M d, Y');
    } catch (Exception $e) {
        return 'N/A';
    }
}

Examples:

formatDate('2025-11-11') → "Nov 11, 2025"
formatDate(null) → "N/A"

escape(string $value)

Escape output for safe HTML rendering.

Parameters:

$value - String to escape

Returns: string

Implementation:

function escape(string $value): string {
    return htmlspecialchars($value, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
}

Alias: Some files use h() as shorthand.

Use Case: Prevent XSS attacks in HTML output.

JavaScript Utilities

File: scripts/common.js

Modal Management

openModal(modalId) Opens a modal dialog.

closeModal(modalId) Closes a modal dialog.

Implementation:

function openModal(modalId) {
    const modal = document.getElementById(modalId);
    if (modal) {
        modal.style.display = 'flex';
        document.body.style.overflow = 'hidden';
    }
}

function closeModal(modalId) {
    const modal = document.getElementById(modalId);
    if (modal) {
        modal.style.display = 'none';
        document.body.style.overflow = 'auto';
    }
}

Auto-init: Adds click-outside-to-close functionality.

Validation

confirmAction(message) Show confirmation dialog.

Returns: boolean

function confirmAction(message) {
    return confirm(message || 'Are you sure?');
}

confirmDelete(itemName) Confirm deletion with item name.

function confirmDelete(itemName) {
    return confirm(`Are you sure you want to delete ${itemName}? This cannot be undone.`);
}

Currency Formatting

formatCurrency(amount) Format number as Vietnamese đồng (client-side).

function formatCurrency(amount) {
    return new Intl.NumberFormat('vi-VN').format(amount) + 'đ';
}

Examples:

formatCurrency(1000000) → "1.000.000đ"
formatCurrency(50000) → "50.000đ"

Date Formatting

formatDate(dateString) Format date string.

function formatDate(dateString) {
    if (!dateString) return 'N/A';
    const date = new Date(dateString);
    return date.toLocaleDateString('en-US', {
        year: 'numeric',
        month: 'short',
        day: 'numeric'
    });
}

formatDateTime(dateString) Format date and time.

function formatDateTime(dateString) {
    if (!dateString) return 'N/A';
    const date = new Date(dateString);
    return date.toLocaleDateString('en-US', {
        year: 'numeric',
        month: 'short',
        day: 'numeric',
        hour: '2-digit',
        minute: '2-digit'
    });
}

Alerts

showAlert(message, type) Display temporary alert message.

Parameters:

message - Alert text
type - 'success', 'error', 'warning', 'info'

function showAlert(message, type = 'info') {
    const alert = document.createElement('div');
    alert.className = `alert alert-${type}`;
    alert.textContent = message;
    alert.style.animation = 'slideUp 0.3s ease-out';
    
    document.body.appendChild(alert);
    
    setTimeout(() => {
        alert.style.animation = 'fadeOut 0.3s ease-out';
        setTimeout(() => alert.remove(), 300);
    }, 3000);
}

Auto-remove: Alerts disappear after 3 seconds.

Table Filtering

filterTable(inputId, tableId) Client-side table filtering.

function filterTable(inputId, tableId) {
    const input = document.getElementById(inputId);
    const table = document.getElementById(tableId);
    const filter = input.value.toUpperCase();
    const rows = table.getElementsByTagName('tr');
    
    for (let i = 1; i < rows.length; i++) {  // Skip header
        const cells = rows[i].getElementsByTagName('td');
        let found = false;
        
        for (let j = 0; j < cells.length; j++) {
            if (cells[j].textContent.toUpperCase().indexOf(filter) > -1) {
                found = true;
                break;
            }
        }
        
        rows[i].style.display = found ? '' : 'none';
    }
}

Use Case: Real-time search in data tables.

Debounce

debounce(func, wait) Debounce function calls.

function debounce(func, wait) {
    let timeout;
    return function executedFunction(...args) {
        const later = () => {
            clearTimeout(timeout);
            func(...args);
        };
        clearTimeout(timeout);
        timeout = setTimeout(later, wait);
    };
}

Use Case: Optimize search input event handlers.

Batch Payment JavaScript

File: scripts/batch_payment.js

updateTotals()

Recalculate selected invoice totals.

function updateTotals() {
    const checkboxes = document.querySelectorAll('input[name="invoice_ids[]"]:checked');
    let total = 0;
    let count = 0;
    
    checkboxes.forEach(checkbox => {
        const amount = parseFloat(checkbox.dataset.amount || 0);
        total += amount;
        count++;
    });
    
    document.getElementById('selected-count').textContent = count;
    document.getElementById('total-amount').textContent = formatCurrency(total);
}

Triggers: Checkbox change events

setupCheckboxListeners()

Attach event listeners to invoice checkboxes.

function setupCheckboxListeners() {
    document.querySelectorAll('input[name="invoice_ids[]"]').forEach(checkbox => {
        checkbox.addEventListener('change', updateTotals);
    });
}

setupSelectAll()

Implement select all/deselect all functionality.

document.getElementById('select-all').addEventListener('click', () => {
    document.querySelectorAll('input[name="invoice_ids[]"]').forEach(cb => {
        cb.checked = true;
    });
    updateTotals();
});

document.getElementById('deselect-all').addEventListener('click', () => {
    document.querySelectorAll('input[name="invoice_ids[]"]').forEach(cb => {
        cb.checked = false;
    });
    updateTotals();
});

setupPaymentMethodValidation()

Validate payment method selection before submit.

document.querySelector('form').addEventListener('submit', (e) => {
    const method = document.querySelector('input[name="payment_method"]:checked');
    const selected = document.querySelectorAll('input[name="invoice_ids[]"]:checked');
    
    if (selected.length === 0) {
        e.preventDefault();
        showAlert('Please select at least one invoice to pay', 'error');
        return false;
    }
    
    if (!method) {
        e.preventDefault();
        showAlert('Please select a payment method', 'error');
        return false;
    }
});

API Reference

Environment Variables

File: creds.php

Variable	Description	Example
`DB_HOST`	PostgreSQL host	`localhost`
`DB_PORT`	PostgreSQL port	`5432`
`DB_NAME`	Database name	`s105261618`
`DB_USER`	Database user	`s105261618`
`DB_PASSWORD`	Database password	`xxxxxxxx`
`COOKIE_SECRET`	HMAC secret key for cookies	Random 64-char string
`VNP_TMN_CODE`	VNPay merchant code	`0P5OS3KZ` (sandbox)
`VNP_HASH_SECRET`	VNPay hash secret	Provided by VNPay
`VNP_URL`	VNPay payment URL	`https://sandbox.vnpayment.vn/paymentv2/vpcpay.html`
`VNP_RETURN_URL`	VNPay callback URL	`https://cos20031.ascensionfm.works/vnpay_batch_return.php`

Database Connection String

pgsql:host={DB_HOST};port={DB_PORT};dbname={DB_NAME}

Schema Search Path:

SET search_path TO "COS20031", public;

Executed on every connection to ensure table references use the correct schema.

VNPay API Endpoints

Payment URL

GET https://sandbox.vnpayment.vn/paymentv2/vpcpay.html

Required Parameters:

vnp_Version=2.1.0
vnp_Command=pay
vnp_TmnCode - Merchant code
vnp_Amount - Amount in smallest currency unit (đồng × 100)
vnp_CurrCode=VND
vnp_TxnRef - Unique transaction reference
vnp_OrderInfo - Order description
vnp_OrderType=billpayment
vnp_Locale=vn or en
vnp_ReturnUrl - Callback URL
vnp_IpAddr - Customer IP
vnp_CreateDate - Format: YYYYMMDDHHmmss
vnp_SecureHash - HMAC-SHA512 of all parameters

Signature Algorithm:

Sort all parameters alphabetically (except vnp_SecureHash)
Build query string: key1=value1&key2=value2&...
Hash: hash_hmac('sha512', $queryString, $secret)

Return URL (Callback)

GET Parameters Returned:

vnp_TxnRef - Original transaction reference
vnp_Amount - Amount (× 100)
vnp_OrderInfo - Order description
vnp_ResponseCode - Status code ('00' = success)
vnp_TransactionNo - VNPay transaction ID
vnp_BankCode - Bank used for payment
vnp_PayDate - Payment timestamp (YYYYMMDDHHmmss)
vnp_SecureHash - HMAC signature to verify

Verification: Must verify signature using same algorithm as request.

HTTP Status Codes

Code	Usage
200	Success
302	Redirect (authentication, post-login)
400	Bad request (validation errors)
401	Unauthorized (not logged in)
403	Forbidden (wrong role)
404	Not found
500	Server error (database, PHP exceptions)

CSRF Token Flow

Generation (Server):

if (empty($_SESSION["csrf_token"])) {
    $_SESSION["csrf_token"] = bin2hex(random_bytes(16));
}

HTML Form:

<input type="hidden" name="csrf_token" value="<?= htmlspecialchars($csrfToken) ?>">

Validation (Server):

$submittedToken = (string) ($_POST["csrf_token"] ?? "");

if (!hash_equals($csrfToken, $submittedToken)) {
    $errors[] = "Your session has expired. Please try again.";
}

Constant-time comparison prevents timing attacks.

Session Variables

Variable	Type	Description
`$_SESSION['auth_user_id']`	UUID string	Current user's ID
`$_SESSION['auth_username']`	string	Username
`$_SESSION['auth_role']`	string	User role (administrator/customer/vendor)
`$_SESSION['csrf_token']`	32-char hex	CSRF protection token

Cookie Structure

Name: user_auth

Value: Base64-encoded string containing:

{JSON_DATA}|{HMAC_SIGNATURE}

JSON Data:

{
    "user_id": "UUID",
    "username": "string",
    "role": "administrator|customer|vendor",
    "timestamp": 1699999999
}

Signature: HMAC-SHA256 of JSON data

Expiration: 30 days from creation

Flags:

HttpOnly - Prevents JavaScript access
SameSite=Strict - CSRF protection
Secure=false - Set true for HTTPS

Response Formats

Success JSON (if API exists)

{
    "success": true,
    "data": { ... },
    "message": "Operation completed successfully"
}

Error JSON

{
    "success": false,
    "errors": ["Error message 1", "Error message 2"],
    "message": "Operation failed"
}

Note: Current implementation uses HTML responses, not JSON APIs. This structure is for reference if converting to REST API.

SQL Query Patterns

Parameterized Query (Secure)

$stmt = $pdo->prepare('SELECT * FROM "User" WHERE "UserID" = :userId');
$stmt->execute([':userId' => $userId]);
$result = $stmt->fetch();

Insert with RETURNING

$stmt = $pdo->prepare('
    INSERT INTO "User" (...)
    VALUES (...)
    RETURNING "UserID"
');
$stmt->execute([...]);
$newId = $stmt->fetchColumn();

Transaction

$pdo->beginTransaction();
try {
    // Multiple queries
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

Performance Considerations

Database Indexes

Frequently Queried Columns:

User.Username (UNIQUE index)
Invoice.CustomerID + Invoice.Status (composite index)
ApartmentContract.CustomerID + ApartmentContract.IsActive
VNPayTransaction.TxnRef (PRIMARY KEY)

Recommendations:

Add index on Invoice.DueDate for overdue queries
Consider partial index: WHERE Status IN ('pending', 'overdue')

Caching Opportunities

Static Data (rarely changes):

Service catalog
Apartment list
Vendor list

Implementation Idea:

// Cache for 5 minutes
$cacheKey = 'services_list';
$services = apcu_fetch($cacheKey);

if ($services === false) {
    $services = $pdo->query('SELECT * FROM "Service"')->fetchAll();
    apcu_store($cacheKey, $services, 300);  // 5 minutes
}

Note: Not currently implemented.

Query Optimization

Avoid N+1 Queries:

❌ Bad:

$invoices = $pdo->query('SELECT * FROM "Invoice"')->fetchAll();
foreach ($invoices as $invoice) {
    $customer = $pdo->prepare('SELECT * FROM "Customer" WHERE "CustomerID" = ?');
    $customer->execute([$invoice['CustomerID']]);
    // ...
}

✅ Good:

$invoices = $pdo->query('
    SELECT i.*, c."FullName"
    FROM "Invoice" i
    JOIN "Customer" c ON i."CustomerID" = c."CustomerID"
')->fetchAll();

Frontend Performance

CSS:

Single dashboard.css file (600+ lines)
Minification recommended for production

JavaScript:

Deferred loading: <script src="..." defer></script>
Event delegation for dynamic elements
Debouncing for search inputs

Images:

No images currently used (CSS-only UI)
If added, use modern formats (WebP)

Security Checklist

✅ Implemented

⚠️ Recommendations

Rate limiting on login attempts
Password strength requirements (enforce in UI)
HTTPS enforcement (currently HTTP)
Content Security Policy headers
Input length validation
File upload validation (if added)
Audit logging (track admin actions)
Two-factor authentication (future)

Deployment Guide

Requirements

PHP: 7.4+ (8.0+ recommended)
PostgreSQL: 13+
Extensions: PDO, pdo_pgsql, mbstring, openssl
Web Server: Apache or Nginx with PHP-FPM

Installation Steps

1. Clone Repository

cd /path/to/webroot
# Copy files to web directory

2. Configure Environment

Edit creds.php:

putenv("DB_HOST=localhost");
putenv("DB_PORT=5432");
putenv("DB_NAME=your_database");
putenv("DB_USER=your_user");
putenv("DB_PASSWORD=your_password");
putenv("COOKIE_SECRET=" . bin2hex(random_bytes(32)));  // Generate unique secret

3. Create Database Schema

psql -U your_user -d your_database -f schema.sql

4. Set Permissions

chmod 755 -R .
chmod 600 creds.php  # Protect credentials

5. Configure Web Server

Apache (.htaccess):

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

Nginx:

location / {
    try_files $uri $uri/ /index.php?$args;
}

location ~ \.php$ {
    fastcgi_pass unix:/var/run/php/php8.0-fpm.sock;
    fastcgi_index index.php;
    include fastcgi_params;
}

6. Create Default Administrator

First login will auto-create administrator if none exists (see index.php:ensureAdministratorExists()).

Default Credentials:

Username: admin
Password: admin123

⚠️ CHANGE IMMEDIATELY AFTER FIRST LOGIN

Production Checklist

Troubleshooting

Common Issues

1. HTTP 500 Error

Check PHP error log: /var/log/apache2/error.log or /var/log/nginx/error.log
Verify database credentials in creds.php
Ensure PostgreSQL is running: systemctl status postgresql

2. Session Not Persisting

Check session.cookie_lifetime in session_config.php
Verify session directory is writable: /var/lib/php/sessions
Confirm session_config.php is included before session_start()

3. VNPay Redirect Issues

Verify VNP_RETURN_URL is correctly set to production domain (https://cos20031.ascensionfm.works/vnpay_batch_return.php)
Verify signature secret matches VNPay portal
Check VNPay sandbox test card numbers

4. Database Connection Failed

Test connection: psql -U username -d database -h localhost
Check pg_hba.conf for authentication settings
Verify schema exists: \dn in psql

5. Logout Button Not Working

Ensure logout.php uses session_config.php
Check session name matches across all pages
Verify cookies are being cleared (browser dev tools)

6. Change Password Button Does Nothing

Check for HTML syntax errors (e.g., misplaced quotes)
Verify change_password.php uses session_config.php
Check browser console for JavaScript errors

Future Enhancements

Planned Features

Email Notifications
- Invoice due reminders
- Payment confirmations
- Service subscription renewals
Reports Dashboard
- Revenue charts (Chart.js)
- Payment trends
- Service popularity
- Export to PDF
Mobile App
- React Native or Flutter
- Push notifications
- Mobile payment options
Advanced Search
- Full-text search on invoices
- Filter by date ranges
- Multi-column sorting
Audit Trail
- Log all admin actions
- Track invoice modifications
- User activity monitoring
Service Request System
- Customers request maintenance
- Vendors respond with quotes
- Track request status
Automated Billing (UPDATED)
- Cron job for monthly service invoices (scripts/generate_monthly_invoices.php)
- Yearly subscription handling: Charges full price at 12-month intervals
- Auto-generate utility invoices from meter readings
- Recurring payment reminders

Yearly Subscription Logic:

// Calculate months since contract start
$startDate = new DateTime($contract['StartDate']);
$now = new DateTime();
$interval = $startDate->diff($now);
$monthsSinceStart = ($interval->y * 12) + $interval->m;

// Charge only at 12-month intervals
if ($contract['SubscriptionType'] === 'yearly') {
    if ($monthsSinceStart % 12 === 0) {
        // Charge full contract price
        $totalPrice = $contract['ContractPrice'];
    } else {
        // Skip this month
        continue;
    }
}

Multi-language Support
- Vietnamese (VN)
- English (EN)
- i18n implementation

Conclusion

This documentation provides a comprehensive guide to the Community Portal system, covering:

System architecture and technology stack
Complete database schema with all tables and relationships
Authentication flows with session and cookie management
All use cases for administrators, customers, and vendors
VNPay payment integration with detailed API documentation
Helper functions and utilities
Security best practices and recommendations
Deployment guide and troubleshooting tips

Key Takeaways

Security First: CSRF protection, prepared statements, HMAC-signed cookies, XSS prevention
User Experience: 30-day persistent sessions, auto-redirect, real-time totals, unified interface
Data Integrity: Transactions for multi-step operations, foreign key constraints, contract cancellation
Scalability: Indexed queries, PDO prepared statements, prepared for caching
Maintainability: Consistent patterns, helper functions, clear documentation
Business Logic: Yearly billing cycles, automatic contract termination, audit trails

Recent Improvements (November 2025)

✅ Unified invoice viewing interface across all user roles
✅ Fixed all database schema mismatches and SQL errors
✅ Implemented PDO throughout for security and consistency
✅ Added administrative deletion features with contract management
✅ Corrected yearly subscription billing logic
✅ Centralized helper functions for code reuse
✅ Enhanced error handling with transaction rollback

Support

For questions or issues, contact the development team or refer to the inline comments in the source code.

Version: 1.1
Last Updated: November 12, 2025
Maintained By: COS20031 Development Team

Admin Invoice Viewing & Excel Export Feature

Overview

Added comprehensive invoice viewing and Excel export functionality for administrators.

New Files Created

1. `admin_view_invoices.php`

Main invoice viewing page with:

Comprehensive filtering by status, customer, vendor, date range, and bill number
Statistics dashboard showing total invoices, amounts, paid/pending/overdue breakdowns
Sortable table view of all invoices
Excel export button to download filtered results

2. `admin_export_invoices.php`

Excel export handler that:

Exports invoices to CSV format (opens in Excel)
Maintains applied filters from viewing page
Includes comprehensive data: invoice details, customer info, vendor info, apartment data
UTF-8 BOM for proper Excel compatibility
Timestamped filenames

Features

Invoice Viewing

View up to 500 most recent invoices
Filter by:
- Status: All, Pending, Paid, Overdue
- Customer: Dropdown of all customers
- Vendor: Dropdown of all vendors
- Bill Number: Search by bill number
- Date Range: From/To date filtering

Statistics Dashboard

Real-time statistics showing:

Total number of invoices
Total amount across all invoices
Amount paid (green)
Amount pending (yellow)
Amount overdue (red)

Excel Export

Click "📊 Export to Excel" button
Downloads CSV file that opens in Excel
Includes all filtered results
Columns exported:
- Invoice ID
- Bill Number
- Customer Name & Email
- Vendor Name & Email
- Apartment Number & Address
- Date Issued
- Due Date
- Amount (VND)
- Status

Usage

Accessing the Feature

Log in as Administrator
From Admin Dashboard, click "📋 View All Invoices"

Filtering Invoices

Select desired filters from dropdown menus
Click "Apply Filters"
Click "Clear Filters" to reset

Exporting to Excel

Apply any desired filters
Click "📊 Export to Excel" button
File downloads automatically as invoices_export_YYYY-MM-DD_HHMMSS.csv
Open in Excel, Google Sheets, or any spreadsheet application

File Locations

/admin_view_invoices.php    - Main viewing page
/admin_export_invoices.php  - Export handler
/admin_dashboard.php        - Updated with new link

Database Queries

Queries the following tables:

Invoice - Main invoice data
Customer - Customer information
Vendor - Vendor information
Apartment - Apartment details

Security

✅ Administrator role required
✅ Session validation
✅ Password change enforcement
✅ SQL injection protection (parameterized queries)
✅ XSS protection (htmlspecialchars)

Benefits

Comprehensive Overview - See all invoices in one place
Powerful Filtering - Find specific invoices quickly
Financial Insights - Real-time statistics
Data Export - Easy reporting and analysis in Excel
Audit Trail - Track all invoice transactions

Example Use Cases

1. Monthly Financial Report

Filter by date range (e.g., last month)
Export to Excel
Create pivot tables and charts

2. Customer Invoice History

Filter by specific customer
View all their invoices
Export for customer records

3. Vendor Payment Tracking

Filter by vendor
See all invoices for that vendor
Export for payment reconciliation

4. Overdue Invoice Follow-up

Filter by "Overdue" status
Export list for collections
Contact customers with overdue payments

Technical Details

CSV Format

UTF-8 encoding with BOM for Excel compatibility
Comma-separated values
Headers included
Numbers formatted for Excel (no thousands separator in file)

Performance

Limited to 500 most recent invoices for page load speed
Export has no limit (exports all filtered results)
Indexed database queries for fast filtering

Future Enhancements (Optional)

PDF export option
Invoice detail view modal
Bulk status updates
Payment recording
Email invoice reminders
Charts and graphs
Pagination for larger datasets

End of Documentation

Name		Name	Last commit message	Last commit date
Latest commit History 14 Commits
scripts		scripts
styles		styles
README.md		README.md
admin_dashboard.php		admin_dashboard.php
admin_export_invoices.php		admin_export_invoices.php
admin_generate_invoices.php		admin_generate_invoices.php
admin_manage_apartments.php		admin_manage_apartments.php
admin_manage_requests.php		admin_manage_requests.php
admin_manage_users.php		admin_manage_users.php
admin_manage_vendors.php		admin_manage_vendors.php
admin_reports.php		admin_reports.php
admin_utility_meters.php		admin_utility_meters.php
admin_view_invoices.php		admin_view_invoices.php
auth_helper.php		auth_helper.php
batch_payment.php		batch_payment.php
change_password.php		change_password.php
common_helpers.php		common_helpers.php
creds.php		creds.php
customer_dashboard.php		customer_dashboard.php
customer_export_invoices.php		customer_export_invoices.php
customer_request_service.php		customer_request_service.php
customer_view_invoices.php		customer_view_invoices.php
index.php		index.php
logout.php		logout.php
schema.sql		schema.sql
services_manage.php		services_manage.php
session_config.php		session_config.php
start_vnpay_batch.php		start_vnpay_batch.php
vendor_create_invoice.php		vendor_create_invoice.php
vendor_dashboard.php		vendor_dashboard.php
vendor_export_invoices.php		vendor_export_invoices.php
vendor_reports.php		vendor_reports.php
vendor_update_profile.php		vendor_update_profile.php
vendor_view_contracts.php		vendor_view_contracts.php
vendor_view_invoices.php		vendor_view_invoices.php
vnpay_batch_return.php		vnpay_batch_return.php

Folders and files

Latest commit

History

Repository files navigation

Community Portal System Documentation

Table of Contents

System Overview

Key Features

Recent Updates (November 2025)

Reporting & Analytics (November 12, 2025)

Invoice System Overhaul

Subscription Billing Enhancement

Service Contract Display Fix

Administrative Deletion Features

Code Quality Improvements

User Roles

Technology Stack

Backend

Frontend

Payment Gateway

Development Tools

Architecture

File Structure

Request Flow

Database Schema

Schema: COS20031

Core Entities

1. User Table

2. Customer Table

3. Vendor Table

4. Apartment Table

5. ApartmentContract Table

6. Service Table

7. ServiceContract Table

8. Invoice Table

9. InvoiceDetail Table

10. Payment Table

11. BatchPayment Table

12. VNPayTransaction Table

13. UtilityMeter Table

14. UtilityMeterReading Table

Authentication & Session Management

Session Configuration

Use Case 1: User Login

Flow Diagram

Queries Executed

Session Creation

Persistent Cookie Creation

Redirect Logic

Use Case 2: Session Restoration (Auto-Login)

Flow Diagram

Cookie Verification (auth_helper.php)

Use Case 3: Password Change

Queries Executed

Validation Rules

Password Update Process

Use Case 4: Logout

Flow Diagram

Logout Process (auth_helper.php)

Use Case 5: Authentication Helpers

Helper Functions

CSRF Protection

Administrator Use Cases

Admin Dashboard Overview

Queries Executed on Dashboard Load

Use Case 1: User Management

View All Users

Create New User

Delete User

Force Password Change

Use Case 2: Apartment Management

View All Apartments

Create New Apartment

Assign Customer to Apartment

Terminate Contract

Delete Apartment (NEW)

Use Case 3: Vendor Approval

View Pending Vendors

Approve Vendor

Reject Vendor