 Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory : /home/gositeme/domains/soundstudiopro.com/private_html/admin_includes/ |
<?php
/**
* Subscription Management Admin Panel
* View and manage user subscriptions
*/
$pdo = getDBConnection();
// Get filter parameters
$status_filter = $_GET['status'] ?? 'all';
$plan_filter = $_GET['plan'] ?? 'all';
$search = $_GET['search'] ?? '';
// Build query
$where_conditions = [];
$params = [];
if ($status_filter !== 'all') {
$where_conditions[] = "us.status = ?";
$params[] = $status_filter;
}
if ($plan_filter !== 'all') {
$where_conditions[] = "us.plan_name = ?";
$params[] = $plan_filter;
}
if (!empty($search)) {
$where_conditions[] = "(u.name LIKE ? OR u.email LIKE ? OR us.stripe_subscription_id LIKE ?)";
$search_param = "%{$search}%";
$params[] = $search_param;
$params[] = $search_param;
$params[] = $search_param;
}
$where_clause = !empty($where_conditions) ? "WHERE " . implode(" AND ", $where_conditions) : "";
// Get subscriptions
$stmt = $pdo->prepare("
SELECT
us.*,
u.name as user_name,
u.email as user_email,
u.id as user_id,
(SELECT tracks_created FROM monthly_track_usage
WHERE user_id = us.user_id
AND year_month = DATE_FORMAT(NOW(), '%Y-%m')
LIMIT 1) as current_month_usage,
(SELECT track_limit FROM monthly_track_usage
WHERE user_id = us.user_id
AND year_month = DATE_FORMAT(NOW(), '%Y-%m')
LIMIT 1) as current_month_limit
FROM user_subscriptions us
JOIN users u ON us.user_id = u.id
{$where_clause}
ORDER BY us.created_at DESC
LIMIT 100
");
$stmt->execute($params);
$subscriptions = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Get statistics
$stats_stmt = $pdo->query("
SELECT
COUNT(*) as total,
COUNT(CASE WHEN status = 'active' THEN 1 END) as active,
COUNT(CASE WHEN status = 'canceled' THEN 1 END) as canceled,
COUNT(CASE WHEN plan_name = 'essential' THEN 1 END) as essential
FROM user_subscriptions
");
$stats = $stats_stmt->fetch(PDO::FETCH_ASSOC);
?>
<div class="admin-section">
<h2>📊 Subscription Management</h2>
<!-- Statistics -->
<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin-bottom: 30px;">
<div style="background: #2a2a2a; padding: 20px; border-radius: 8px;">
<div style="color: #a0aec0; font-size: 0.9rem; margin-bottom: 5px;">Total Subscriptions</div>
<div style="color: white; font-size: 2rem; font-weight: bold;"><?= $stats['total'] ?></div>
</div>
<div style="background: #2a2a2a; padding: 20px; border-radius: 8px;">
<div style="color: #a0aec0; font-size: 0.9rem; margin-bottom: 5px;">Active</div>
<div style="color: #48bb78; font-size: 2rem; font-weight: bold;"><?= $stats['active'] ?></div>
</div>
<div style="background: #2a2a2a; padding: 20px; border-radius: 8px;">
<div style="color: #a0aec0; font-size: 0.9rem; margin-bottom: 5px;">Canceled</div>
<div style="color: #e53e3e; font-size: 2rem; font-weight: bold;"><?= $stats['canceled'] ?></div>
</div>
<div style="background: #2a2a2a; padding: 20px; border-radius: 8px;">
<div style="color: #a0aec0; font-size: 0.9rem; margin-bottom: 5px;">Essential Plan</div>
<div style="color: #667eea; font-size: 2rem; font-weight: bold;"><?= $stats['essential'] ?></div>
</div>
</div>
<!-- Filters -->
<div style="background: #2a2a2a; padding: 20px; border-radius: 8px; margin-bottom: 20px;">
<form method="GET" style="display: flex; gap: 15px; flex-wrap: wrap; align-items: end;">
<input type="hidden" name="tab" value="subscriptions">
<div style="flex: 1; min-width: 200px;">
<label style="color: #a0aec0; display: block; margin-bottom: 5px;">Search</label>
<input type="text" name="search" value="<?= htmlspecialchars($search) ?>" placeholder="Name, email, or subscription ID" style="width: 100%; padding: 8px; background: #1a1a1a; border: 1px solid #444; border-radius: 5px; color: white;">
</div>
<div>
<label style="color: #a0aec0; display: block; margin-bottom: 5px;">Status</label>
<select name="status" style="padding: 8px; background: #1a1a1a; border: 1px solid #444; border-radius: 5px; color: white;">
<option value="all" <?= $status_filter === 'all' ? 'selected' : '' ?>>All</option>
<option value="active" <?= $status_filter === 'active' ? 'selected' : '' ?>>Active</option>
<option value="canceled" <?= $status_filter === 'canceled' ? 'selected' : '' ?>>Canceled</option>
<option value="past_due" <?= $status_filter === 'past_due' ? 'selected' : '' ?>>Past Due</option>
</select>
</div>
<div>
<label style="color: #a0aec0; display: block; margin-bottom: 5px;">Plan</label>
<select name="plan" style="padding: 8px; background: #1a1a1a; border: 1px solid #444; border-radius: 5px; color: white;">
<option value="all" <?= $plan_filter === 'all' ? 'selected' : '' ?>>All</option>
<option value="essential" <?= $plan_filter === 'essential' ? 'selected' : '' ?>>Essential</option>
</select>
</div>
<div>
<button type="submit" style="padding: 8px 20px; background: #667eea; color: white; border: none; border-radius: 5px; cursor: pointer;">Filter</button>
<a href="?tab=subscriptions" style="padding: 8px 20px; background: #2a2a2a; color: white; text-decoration: none; border-radius: 5px; display: inline-block; margin-left: 10px;">Reset</a>
</div>
</form>
</div>
<!-- Subscriptions Table -->
<div style="background: #2a2a2a; border-radius: 8px; overflow: hidden;">
<table style="width: 100%; border-collapse: collapse;">
<thead>
<tr style="background: #1a1a1a;">
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">User</th>
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">Plan</th>
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">Status</th>
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">Usage</th>
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">Period End</th>
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">Subscription ID</th>
<th style="padding: 15px; text-align: left; color: white; border-bottom: 1px solid #333;">Actions</th>
</tr>
</thead>
<tbody>
<?php if (empty($subscriptions)): ?>
<tr>
<td colspan="7" style="padding: 30px; text-align: center; color: #a0aec0;">No subscriptions found</td>
</tr>
<?php else: ?>
<?php foreach ($subscriptions as $sub): ?>
<tr style="border-bottom: 1px solid #333;">
<td style="padding: 15px; color: white;">
<div><strong><?= htmlspecialchars($sub['user_name']) ?></strong></div>
<div style="color: #a0aec0; font-size: 0.9rem;"><?= htmlspecialchars($sub['user_email']) ?></div>
</td>
<td style="padding: 15px; color: white;"><?= ucfirst($sub['plan_name']) ?></td>
<td style="padding: 15px;">
<span style="padding: 5px 10px; border-radius: 5px; font-size: 0.9rem; background: <?= $sub['status'] === 'active' ? '#2d5016' : ($sub['status'] === 'canceled' ? '#5a1a1a' : '#5a3a1a'); ?>; color: <?= $sub['status'] === 'active' ? '#48bb78' : ($sub['status'] === 'canceled' ? '#e53e3e' : '#ffc107'); ?>;">
<?= ucfirst($sub['status']) ?>
</span>
</td>
<td style="padding: 15px; color: white;">
<?php if ($sub['plan_name'] === 'essential'): ?>
<?= $sub['current_month_usage'] ?? 0 ?> / <?= $sub['current_month_limit'] ?? 5 ?>
<?php else: ?>
N/A
<?php endif; ?>
</td>
<td style="padding: 15px; color: white;">
<?= $sub['current_period_end'] ? date('M j, Y', strtotime($sub['current_period_end'])) : 'N/A' ?>
</td>
<td style="padding: 15px; color: #a0aec0; font-size: 0.9rem; font-family: monospace;">
<?= htmlspecialchars(substr($sub['stripe_subscription_id'], 0, 20)) ?>...
</td>
<td style="padding: 15px;">
<a href="/admin.php?tab=users&user_id=<?= $sub['user_id'] ?>" style="color: #667eea; text-decoration: none; margin-right: 10px;">View User</a>
</td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
</div>
<!-- Quick Actions -->
<div style="margin-top: 30px; padding: 20px; background: #2a2a2a; border-radius: 8px;">
<h3 style="color: white; margin-bottom: 15px;">Quick Actions</h3>
<div style="display: flex; gap: 15px; flex-wrap: wrap;">
<a href="/admin_sync_subscriptions.php" style="padding: 10px 20px; background: linear-gradient(135deg, #4299e1, #3182ce); color: white; text-decoration: none; border-radius: 5px; font-weight: 600; display: inline-flex; align-items: center; gap: 8px;">
<i class="fas fa-sync-alt"></i> Sync Subscriptions from Stripe
</a>
<a href="/create_subscription_tables.php" style="padding: 10px 20px; background: #667eea; color: white; text-decoration: none; border-radius: 5px;">Setup Database Tables</a>
<a href="/cron/reset_monthly_limits.php?cron_key=YOUR_SECRET_CRON_KEY" style="padding: 10px 20px; background: #48bb78; color: white; text-decoration: none; border-radius: 5px;">Run Monthly Reset (Manual)</a>
</div>
<div style="margin-top: 15px; padding: 15px; background: rgba(66, 153, 225, 0.1); border-radius: 8px; border: 1px solid rgba(66, 153, 225, 0.3);">
<p style="color: #a0aec0; margin: 0; font-size: 0.9rem;">
<strong style="color: #4299e1;">💡 Sync Tool:</strong> Use the sync tool to fix subscription mismatches, sync after refunds, or when webhooks fail.
Search by user ID, email, or filter by issues to find users that need syncing.
</p>
</div>
</div>
</div>