Billing Transactions

Overview

The Admin Billing Transactions page provides comprehensive oversight of all payment transactions on the platform. Administrators can view transaction history, monitor revenue metrics, filter transactions by various criteria, and track subscription lifecycles.

Key capabilities include:

• View all billing transactions with detailed information
• Monitor revenue statistics (total revenue, refunds, chargebacks)
• Filter transactions by provider, type, status, and date range
• Search transactions by user or external transaction ID
• View linked user accounts and subscription details
• Track transaction statuses (open, closed, cancelled)
• Analyze purchase patterns and rebill rates

 

---

System Requirements

PHP Requirements

Requirement	Minimum	Recommended
PHP Version	7.4+	8.0+
Memory Limit	32M	64M

PHP Extensions Required

• mysqli - Database connectivity
• json - JSON encoding for API responses
• session - Session management for CSRF protection
• mbstring - Multi-byte string handling

PHP Settings

session.autostart = Off
date.timezone = Your/Timezone

---

Installation Requirements

Database Tables

-- Billing Transactions Table
CREATE TABLE IF NOT EXISTS tblBillingTransactions (
    transactionid INT(11) NOT NULL AUTOINCREMENT,
    userid INT(11) DEFAULT NULL COMMENT 'User who made the purchase',
    internalproviderid INT(11) NOT NULL COMMENT 'Payment provider used',
    externalpackageid VARCHAR(100) DEFAULT NULL COMMENT 'Provider package ID',
    typeid TINYINT(2) NOT NULL DEFAULT 1 COMMENT '1=Initial,2=Conversion,3=Rebill,4=Chargeback,5=Refund,6=Void,10=Tokens',
    statusid TINYINT(2) NOT NULL DEFAULT 1 COMMENT '1=Open,2=Closed,3=Cancelled',
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    currencycode VARCHAR(3) DEFAULT 'USD',
    externalid VARCHAR(100) DEFAULT NULL COMMENT 'Provider transaction ID',
    accessstartdate DATETIME DEFAULT NULL,
    accessenddate DATETIME DEFAULT NULL,
    subscriptionid VARCHAR(100) DEFAULT NULL COMMENT 'Recurring subscription ID',
    parenttransactionid INT(11) DEFAULT NULL COMMENT 'For rebills/refunds',
    tokensgranted INT(11) DEFAULT 0 COMMENT 'Tokens added for token purchases',
    ipaddress VARCHAR(45) DEFAULT NULL,
    useragent TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    addeddate DATETIME DEFAULT CURRENTTIMESTAMP,
    updateddate DATETIME DEFAULT NULL ON UPDATE CURRENTTIMESTAMP,
    PRIMARY KEY (transactionid),
    KEY idxuserid (userid),
    KEY idxproviderid (internalproviderid),
    KEY idxtypeid (typeid),
    KEY idxstatusid (statusid),
    KEY idxexternalid (externalid),
    KEY idxaddeddate (addeddate),
    KEY idxsubscriptionid (subscriptionid),
    KEY idxparenttransaction (parenttransactionid),
    CONSTRAINT fktransactionuser FOREIGN KEY (userid)
        REFERENCES tblCMSUsers (id) ON DELETE SET NULL,
    CONSTRAINT fktransactionprovider FOREIGN KEY (internalproviderid)
        REFERENCES tblPaymentProviders (internalproviderid) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4unicodeci;

-- Payment Providers Table (referenced)
CREATE TABLE IF NOT EXISTS tblPaymentProviders (
internalproviderid INT(11) NOT NULL AUTOINCREMENT,
providername VARCHAR(100) NOT NULL,
providertype VARCHAR(50) NOT NULL COMMENT 'Class name for processor',
isactive TINYINT(1) DEFAULT 1,
displayorder INT(11) DEFAULT 0,
configuration JSON DEFAULT NULL COMMENT 'Provider-specific settings',
createdat DATETIME DEFAULT CURRENTTIMESTAMP,
updatedat DATETIME DEFAULT NULL ON UPDATE CURRENTTIMESTAMP,
PRIMARY KEY (internalproviderid),
KEY idxisactive (isactive),
KEY idxdisplayorder (displayorder)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4unicodeci;

-- Access Packages Table (referenced for package details)
CREATE TABLE IF NOT EXISTS tblAccessPackages (
packageid INT(11) NOT NULL AUTOINCREMENT,
providerid INT(11) NOT NULL,
externalid VARCHAR(100) DEFAULT NULL,
title VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
priceinitial DECIMAL(10,2) NOT NULL DEFAULT 0.00,
pricerebill DECIMAL(10,2) DEFAULT NULL,
durationinitial INT(11) DEFAULT NULL COMMENT 'Days',
durationrebill INT(11) DEFAULT NULL COMMENT 'Days',
tokens INT(11) DEFAULT 0,
packagetype ENUM('tokens', 'sitevip', 'creatorsubscription') DEFAULT 'sitevip',
isactive TINYINT(1) DEFAULT 1,
createdat DATETIME DEFAULT CURRENTTIMESTAMP,
PRIMARY KEY (packageid),
KEY idxproviderid (providerid),
KEY idxexternalid (externalid),
KEY idxpackagetype (packagetype),
KEY idxisactive (isactive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4unicodeci;

 

Transaction Type Reference

Type ID	Name	Description
1	Initial Purchase	First-time subscription purchase
2	Conversion	Upgrade or plan change
3	Rebill	Recurring subscription charge
4	Chargeback	Customer disputed charge
5	Refund	Money returned to customer
6	Void	Transaction cancelled before settlement
10	Tokens	One-time token purchase

Transaction Status Reference

Status ID	Name	Description
1	Open	Transaction pending or active
2	Closed	Transaction completed
3	Cancelled	Transaction cancelled

Config Variables

From ct/dat/config.inc.php:

• $dbhost, $dbuser, $dbpasswd, $db - Database connection

 

File Dependencies

ct/includes/sessions.inc.php - Session managementct/dat/config.inc.php - Configurationct/includes/header.php - Admin header

• ct/includes/annotateftr.php - Admin footer
• Font Awesome 5+ - Icon library

---

Features & UI Elements

Page Header

[Screenshot: billing-transactions-header]

The page header displays:

• Title: "Billing Transactions"
• Icon: fa-exchange-alt (exchange icon)
• Breadcrumb: Dashboard / Billing / Transactions

 

Statistics Dashboard

[Screenshot: billing-transactions-stats]

Seven statistics cards showing transaction overview:

Card	Color	Icon	Description
Total Transactions	Green	fa-receipt	All transaction records
Initial Purchases	Blue	fa-shopping-cart	First-time purchases (typeid=1)
Rebills	Cyan	fa-sync	Recurring charges (typeid=3)
Chargebacks	Red	fa-exclamation-triangle	Disputed charges (typeid=4)
Refunds	Orange	fa-undo	Refunded transactions (typeid=5)
Total Revenue	Green	fa-dollar-sign	Sum of successful transactions
Total Refunded	Red	fa-hand-holding-usd	Sum of refunds + chargebacks

Filter Panel

[Screenshot: billing-transactions-filters]

A collapsible filter panel with:

Filter	Type	Description
Search	Text Input	Search by username or external ID
Provider	Dropdown	Filter by payment provider
Type	Dropdown	Filter by transaction type
Status	Dropdown	Filter by transaction status
Date From	Date Picker	Start date for date range
Date To	Date Picker	End date for date range

Action Buttons:

• Apply Filters (Green) - Apply selected filters
• Clear Filters - Reset all filters to default

Transactions Table

[Screenshot: billing-transactions-table]

A data table showing all transactions:

Column	Description
ID	Transaction ID (auto-increment)
User	Username with link to user profile
Provider	Payment provider name
Package	External package ID
Type	Transaction type with color badge
Status	Transaction status with color badge
Amount	Price with currency code
External ID	Provider's transaction reference
Access Period	Start date → End date
Created	Transaction date and time
Actions	View details button

Type Badges:

Type	Badge Color
Initial Purchase	Blue
Conversion	Cyan
Rebill	Green
Chargeback	Red
Refund	Orange
Void	Gray
Tokens	Purple

Status Badges:

Status	Badge Color
Open	Yellow
Closed	Green
Cancelled	Gray

Transaction Detail Modal

[Screenshot: billing-transaction-modal]

When clicking "View Details", a modal displays:

Basic Information:

• Transaction ID
• User (with link to profile)
• Provider name
• External Package ID
• Type and Status
• Amount with currency

 

Subscription Details:

• Access Start Date
• Access End Date
• Subscription ID (for recurring)
• Parent Transaction (for rebills/refunds)

 

Technical Details:

• IP Address
• User Agent
• Notes (if any)
• Created Date
• Updated Date

 

Pagination

• 50 transactions per page
• Previous/Next navigation
• Page number links
• Maintains filter parameters across pages

---

Step-by-Step Usage

Viewing All Transactions

1. Navigate to Admin Panel → Billing → Transactions
2. The page loads with all transactions (newest first)
3. Browse transactions in the table
4. Click on a transaction row for details

Filtering Transactions

By Provider:

1. Expand the filter panel (if collapsed)
2. Select a provider from the Provider dropdown
3. Click Apply Filters
4. Only transactions from that provider are shown

 

By Type:

1. Select a transaction type from the Type dropdown
2. Click Apply Filters
3. Only matching transaction types are shown

 

By Status:

1. Select a status from the Status dropdown
2. Click Apply Filters
3. Only transactions with that status are shown

 

By Date Range:

1. Click the Date From picker and select a start date
2. Click the Date To picker and select an end date
3. Click Apply Filters
4. Only transactions within the range are shown

 

Combining Filters:

1. Select multiple filter criteria
2. Click Apply Filters
3. Results match ALL selected criteria (AND logic)

 

Searching Transactions

1. Enter a search term in the Search field
2. Click Apply Filters
3. Matches against:

• Username
• External transaction ID
• Subscription ID

 

 

Viewing Transaction Details

1. Find the transaction in the table
2. Click the View button (eye icon)
3. Modal opens with full transaction details
4. Review all fields including technical data
5. Click outside modal or press ESC to close

Tracking Subscription Lifecycle

1. Find an initial purchase transaction
2. Note the Subscription ID
3. Search for that subscription ID
4. View all related transactions:

• Initial purchase
• Rebills (monthly charges)
• Any refunds or chargebacks

Investigating Chargebacks

1. Filter by Type: Chargeback
2. Review chargeback transactions
3. Click to view details
4. Note the user and external ID
5. Click username to view user profile
6. Check user's transaction history for patterns

 

 

Exporting Transaction Data

The current implementation displays data on-screen. For exports:

1. Use the filter options to narrow results
2. Use browser print function (Ctrl+P)
3. Or query database directly for CSV exports

 

 

 

---

Best Practices

Revenue Monitoring

 

 

1. Daily Check:

• Review total transactions from last 24 hours
• Check for unusual chargeback spikes
• Verify rebills are processing correctly

1. Weekly Review:

• Compare revenue week-over-week
• Analyze refund rates by provider
• Check conversion rates

1. Monthly Analysis:

• Full revenue reconciliation
• Provider performance comparison
• Customer lifetime value tracking

Chargeback Management

Chargeback Rate	Action
< 0.5%	Normal - monitor monthly
0.5% - 1%	Elevated - review weekly
1% - 2%	High - immediate review
> 2%	Critical - may lose processor

Steps for High Chargebacks:

1. Identify repeat chargeback users
2. Review their transaction history
3. Consider account suspension
4. Document all evidence
5. Respond to provider within deadlines

Transaction Reconciliation

1. Match with Provider Dashboard:

• Export transactions for date range
• Compare counts with provider reports
• Identify any discrepancies

1. Verify Revenue Totals:

• Sum transactions by status
• Exclude cancelled/voided
• Match with bank deposits

---

Troubleshooting

Common Issues

Transactions Not Appearing

Cause: Webhook not received or processed

Solutions:

1. Check billing logs for webhook errors
2. Verify provider webhook URL is correct
3. Check server firewall for blocked requests
4. Verify transaction exists in provider dashboard
5. Manually check tblBillingTransactions table

 

User Not Linked to Transaction

Cause: User not logged in during purchase or webhook timing

Solutions:

1. Check if userid is NULL in database
2. Review webhook payload for user identifier
3. Manually link transaction if user is identified:

 

UPDATE tblBillingTransactions

SET userid = {correctuserid}

WHERE transactionid = {transactionid};

 

Incorrect Transaction Type

Cause: Provider sent wrong transaction classification

Solutions:

1. Review original webhook payload in billing logs
2. Manually update if needed:

 

UPDATE tblBillingTransactions

SET typeid = {correcttypeid}

WHERE transactionid = {transactionid};

 

Missing Rebills

Cause: Subscription cancelled or rebill webhook failed

Solutions:

1. Check provider dashboard for rebill status
2. Verify subscription is still active
3. Check billing logs for failed webhooks
4. Contact provider if discrepancy exists

 

Database Issues

Missing Provider

Cause: Provider deleted but transactions reference it

Solution:

-- Find orphaned transactions

SELECT transactionid, internalproviderid

FROM tblBillingTransactions

WHERE internalproviderid NOT IN (

    SELECT internalproviderid FROM tblPaymentProviders

);

 

-- Re-add provider or update transactions

 

Date Range Issues

Cause: Timezone mismatch between server and database

Solution:

1. Verify PHP timezone setting
2. Check MySQL timezone:

 

SELECT @@global.timezone, @@session.timezone;

 

1. Ensure consistency across all systems

 

---

Security Considerations

Access Control

• Only administrators can view transaction data
• Session validation on page load
• CSRF protection on all form submissions

Sensitive Data

 

 

• Never share external transaction IDs publicly
• IP addresses are logged for fraud prevention
• User agents help identify suspicious patterns
• Limit access to billing section to trusted admins

Audit Trail

All transaction changes should be logged:

• Transaction creation (automatic)
• Status changes
• Manual updates (document in notes field)

 

---

Translatable Strings

{
    "billingtransactionstitle": "Billing Transactions",
    "billingtransactionsbreadcrumb": "Dashboard / Billing / Transactions",
    "billingstattotal": "Total Transactions",
    "billingstatinitial": "Initial Purchases",
    "billingstatrebills": "Rebills",
    "billingstatchargebacks": "Chargebacks",
    "billingstatrefunds": "Refunds",
    "billingstatrevenue": "Total Revenue",
    "billingstatrefunded": "Total Refunded",
    "billingfiltersearch": "Search username or external ID",
    "billingfilterprovider": "All Providers",
    "billingfiltertype": "All Types",
    "billingfilterstatus": "All Statuses",
    "billingfilterdatefrom": "Date From",
    "billingfilterdateto": "Date To",
    "billingfilterapply": "Apply Filters",
    "billingfilterclear": "Clear Filters",
    "billingtableid": "ID",
    "billingtableuser": "User",
    "billingtableprovider": "Provider",
    "billingtablepackage": "Package",
    "billingtabletype": "Type",
    "billingtablestatus": "Status",
    "billingtableamount": "Amount",
    "billingtableexternalid": "External ID",
    "billingtableaccessperiod": "Access Period",
    "billingtablecreated": "Created",
    "billingtableactions": "Actions",
    "billingtypeinitial": "Initial Purchase",
    "billingtypeconversion": "Conversion",
    "billingtyperebill": "Rebill",
    "billingtypechargeback": "Chargeback",
    "billingtyperefund": "Refund",
    "billingtypevoid": "Void",
    "billingtypetokens": "Tokens",
    "billingstatusopen": "Open",
    "billingstatusclosed": "Closed",
    "billingstatuscancelled": "Cancelled",
    "billingmodaltitle": "Transaction Details",
    "billingmodaluser": "User",
    "billingmodalprovider": "Provider",
    "billingmodalpackage": "Package",
    "billingmodaltype": "Type",
    "billingmodalstatus": "Status",
    "billingmodalamount": "Amount",
    "billingmodalexternalid": "External ID",
    "billingmodalsubscriptionid": "Subscription ID",
    "billingmodalparenttransaction": "Parent Transaction",
    "billingmodalaccessstart": "Access Start",
    "billingmodalaccessend": "Access End",
    "billingmodalipaddress": "IP Address",
    "billingmodaluseragent": "User Agent",
    "billingmodalnotes": "Notes",
    "billingmodalcreated": "Created",
    "billingmodalupdated": "Updated",
    "billingmodalclose": "Close",
    "billingnotransactions": "No transactions found",
    "billingnomatching": "No transactions match your filters",
    "billingguestuser": "Guest User",
    "billingunknownprovider": "Unknown Provider",
    "billingviewuser": "View User Profile",
    "billingviewdetails": "View Details"
}

---

Related Documentation

• Payment Providers - Payment provider configuration
• Access Packages - Subscription package management
• Billing Logs - Payment processor logs
• User Management - User account management

---

Version History

Version	Date	Changes
1.0.0	2025-10-20	Initial billing transactions page
1.1.0	2025-11-01	Added filter panel
1.2.0	2025-11-15	Added statistics dashboard
1.3.0	2025-12-01	Added transaction detail modal
1.4.0	2025-12-15	Modern UI with green theme
1.5.0	2025-01-02	Added token purchase support (type 10)