$status,'id'=>$id]); } header('Location: subscriptions.php?' . http_build_query($_GET)); exit; } } $filter = $_GET['filter'] ?? 'active'; $q = trim($_GET['q'] ?? ''); $where = ['1=1']; $params = []; if ($filter === 'active') $where[] = "pt.status='active'"; if ($filter === 'cancelled') $where[] = "pt.status='cancelled'"; if ($filter === 'failed') $where[] = "pt.status='failed'"; if ($q !== '') { $where[] = "(m.email LIKE :q1 OR m.business_name LIKE :q2 OR m.first_name LIKE :q3 OR m.last_name LIKE :q4)"; $params['q1'] = '%'.$q.'%'; $params['q2'] = '%'.$q.'%'; $params['q3'] = '%'.$q.'%'; $params['q4'] = '%'.$q.'%'; } if ($filter === 'none') { $subs = db_all( "SELECT m.id AS member_id, m.email, m.first_name, m.last_name, m.business_name, m.tier, m.status AS member_status, m.renewal_date, NULL AS sub_id, NULL AS sub_status, NULL AS token, NULL AS last_charge_at, NULL AS next_charge_at, NULL AS gateway, NULL AS created_at FROM members m LEFT JOIN payment_tokens pt ON pt.member_id=m.id WHERE pt.id IS NULL AND m.status IN ('active','pending') ".($q!=='' ? "AND (m.email LIKE :q1 OR m.business_name LIKE :q2)" : "")." ORDER BY m.created_at DESC LIMIT 200", $q!=='' ? ['q1'=>'%'.$q.'%', 'q2'=>'%'.$q.'%'] : [] ); } else { $subs = db_all( "SELECT m.id AS member_id, m.email, m.first_name, m.last_name, m.business_name, m.tier, m.status AS member_status, m.renewal_date, pt.id AS sub_id, pt.status AS sub_status, pt.token, pt.last_charge_at, pt.next_charge_at, pt.gateway, pt.created_at FROM payment_tokens pt JOIN members m ON m.id=pt.member_id WHERE ".implode(' AND ', $where)." ORDER BY CASE pt.status WHEN 'active' THEN 1 WHEN 'failed' THEN 2 ELSE 3 END, pt.next_charge_at ASC LIMIT 500", $params ); } $counts = [ 'active' => (int)db_value("SELECT COUNT(*) FROM payment_tokens WHERE status='active'"), 'failed' => (int)db_value("SELECT COUNT(*) FROM payment_tokens WHERE status='failed'"), 'cancelled' => (int)db_value("SELECT COUNT(*) FROM payment_tokens WHERE status='cancelled'"), 'none' => (int)db_value( "SELECT COUNT(*) FROM members m LEFT JOIN payment_tokens pt ON pt.member_id=m.id WHERE pt.id IS NULL AND m.status IN ('active','pending')" ), ]; // MRR: active subscriptions only — latest paid invoice per active subscriber $mrr = (int)db_value( "SELECT COALESCE(SUM(i.amount_cents),0) FROM invoices i INNER JOIN ( SELECT member_id, MAX(id) AS max_id FROM invoices WHERE type='membership' AND status='paid' GROUP BY member_id ) latest ON latest.max_id=i.id INNER JOIN payment_tokens pt ON pt.member_id=i.member_id WHERE pt.status='active'" ); $upcoming7 = (int)db_value( "SELECT COUNT(*) FROM payment_tokens WHERE status='active' AND next_charge_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)" ); function cents_to_rand(int $c): string { return 'R '.number_format($c/100,2,'.',' '); } ?>

Subscriptions

+ Add manual

Active subscriptions

PayFast + manual

Monthly recurring

Active only — excl. cancelled

Renewing in 7 days

upcoming charges

No subscription

active members

Active Failed No subscription Cancelled

No subscriptions match this filter.

Business	Member	Tier	Sub status	Member status	Last charged	Next charge	Gateway	Actions
						=0): ?> d —		Edit · Add sub · Member

result.