 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
// Purchases & Sales Management Tab
// Comprehensive view of all purchases, sales, and revenue statistics
// Check if payment_method column exists
$has_payment_method = false;
try {
$check_cols = $pdo->query("SHOW COLUMNS FROM track_purchases LIKE 'payment_method'");
$has_payment_method = $check_cols->rowCount() > 0;
} catch (Exception $e) {
error_log("Error checking payment_method column: " . $e->getMessage());
}
// Get date range filters - default to last 90 days to show more purchases
// Allow "all" to show all purchases
$show_all = isset($_GET['show_all']) && $_GET['show_all'] === '1';
$date_from = $_GET['date_from'] ?? ($show_all ? '2000-01-01' : date('Y-m-d', strtotime('-90 days')));
$date_to = $_GET['date_to'] ?? date('Y-m-d'); // Today
$user_filter = $_GET['user_id'] ?? null;
$track_filter = $_GET['track_id'] ?? null;
$payment_method_filter = $_GET['payment_method'] ?? 'all';
// Build WHERE clause
$where_conditions = [];
$params = [];
// Only add date filters if not showing all
if (!$show_all) {
$where_conditions[] = "tp.purchase_date >= ?";
$where_conditions[] = "tp.purchase_date <= ?";
$params[] = $date_from . ' 00:00:00';
$params[] = $date_to . ' 23:59:59';
}
if ($user_filter) {
$where_conditions[] = "tp.user_id = ?";
$params[] = $user_filter;
}
if ($track_filter) {
$where_conditions[] = "tp.track_id = ?";
$params[] = $track_filter;
}
if ($payment_method_filter !== 'all' && $has_payment_method) {
$where_conditions[] = "tp.payment_method = ?";
$params[] = $payment_method_filter;
}
$where_clause = !empty($where_conditions) ? implode(' AND ', $where_conditions) : '1=1';
// Get purchase statistics
$stats_query = "
SELECT
COUNT(*) as total_purchases,
COUNT(DISTINCT tp.user_id) as unique_buyers,
COUNT(DISTINCT tp.track_id) as unique_tracks_sold,
SUM(tp.price_paid) as total_revenue,
AVG(tp.price_paid) as avg_purchase_price,
SUM(tp.credits_used) as total_credits_used" .
($has_payment_method ? ",
COUNT(CASE WHEN tp.payment_method = 'stripe' THEN 1 END) as stripe_purchases,
COUNT(CASE WHEN tp.payment_method = 'credits' THEN 1 END) as credit_purchases,
COUNT(CASE WHEN tp.payment_method = 'free' THEN 1 END) as free_purchases" : ",
0 as stripe_purchases,
0 as credit_purchases,
0 as free_purchases") . "
FROM track_purchases tp
WHERE $where_clause
";
$stats_stmt = $pdo->prepare($stats_query);
$stats_stmt->execute($params);
$purchase_stats = $stats_stmt->fetch(PDO::FETCH_ASSOC);
// Get revenue breakdown by artist/platform
if ($show_all) {
$revenue_query = "
SELECT
s.revenue_recipient,
COUNT(*) as sale_count,
SUM(s.amount) as total_revenue,
COUNT(DISTINCT s.artist_id) as unique_artists
FROM sales s
GROUP BY s.revenue_recipient
";
$revenue_stmt = $pdo->prepare($revenue_query);
$revenue_stmt->execute();
} else {
$revenue_query = "
SELECT
s.revenue_recipient,
COUNT(*) as sale_count,
SUM(s.amount) as total_revenue,
COUNT(DISTINCT s.artist_id) as unique_artists
FROM sales s
WHERE s.created_at >= ? AND s.created_at <= ?
GROUP BY s.revenue_recipient
";
$revenue_stmt = $pdo->prepare($revenue_query);
$revenue_stmt->execute([$date_from . ' 00:00:00', $date_to . ' 23:59:59']);
}
$revenue_breakdown = $revenue_stmt->fetchAll(PDO::FETCH_ASSOC);
// Get all purchases with full details
$purchases_query = "
SELECT
tp.id as purchase_id,
tp.user_id,
tp.track_id,
tp.price_paid,
tp.credits_used" .
($has_payment_method ? ", tp.payment_method, tp.stripe_payment_intent_id" : ", 'credits' as payment_method, NULL as stripe_payment_intent_id") . ",
tp.purchase_date,
tp.download_count,
tp.last_downloaded,
u.name as buyer_name,
u.email as buyer_email,
mt.title as track_title,
mt.price as track_price,
mt.duration,
artist.name as artist_name,
artist.id as artist_id,
s.revenue_recipient,
s.amount as sale_amount
FROM track_purchases tp
JOIN users u ON tp.user_id = u.id
JOIN music_tracks mt ON tp.track_id = mt.id
JOIN users artist ON mt.user_id = artist.id
LEFT JOIN sales s ON tp.track_id = s.track_id AND tp.user_id = s.buyer_id AND DATE(s.created_at) = DATE(tp.purchase_date)
WHERE $where_clause
ORDER BY tp.purchase_date DESC
LIMIT 500
";
$purchases_stmt = $pdo->prepare($purchases_query);
$purchases_stmt->execute($params);
$purchases = $purchases_stmt->fetchAll(PDO::FETCH_ASSOC);
// Get top selling tracks
if ($show_all) {
$top_tracks_query = "
SELECT
mt.id,
mt.title,
artist.name as artist_name,
COUNT(tp.id) as purchase_count,
SUM(tp.price_paid) as total_revenue,
AVG(tp.price_paid) as avg_price
FROM music_tracks mt
JOIN users artist ON mt.user_id = artist.id
JOIN track_purchases tp ON mt.id = tp.track_id
GROUP BY mt.id, mt.title, artist.name
ORDER BY purchase_count DESC
LIMIT 20
";
$top_tracks_stmt = $pdo->prepare($top_tracks_query);
$top_tracks_stmt->execute();
} else {
$top_tracks_query = "
SELECT
mt.id,
mt.title,
artist.name as artist_name,
COUNT(tp.id) as purchase_count,
SUM(tp.price_paid) as total_revenue,
AVG(tp.price_paid) as avg_price
FROM music_tracks mt
JOIN users artist ON mt.user_id = artist.id
JOIN track_purchases tp ON mt.id = tp.track_id
WHERE tp.purchase_date >= ? AND tp.purchase_date <= ?
GROUP BY mt.id, mt.title, artist.name
ORDER BY purchase_count DESC
LIMIT 20
";
$top_tracks_stmt = $pdo->prepare($top_tracks_query);
$top_tracks_stmt->execute([$date_from . ' 00:00:00', $date_to . ' 23:59:59']);
}
$top_tracks = $top_tracks_stmt->fetchAll(PDO::FETCH_ASSOC);
// Get top buyers
if ($show_all) {
$top_buyers_query = "
SELECT
u.id,
u.name,
u.email,
COUNT(tp.id) as purchase_count,
SUM(tp.price_paid) as total_spent,
AVG(tp.price_paid) as avg_purchase
FROM users u
JOIN track_purchases tp ON u.id = tp.user_id
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 20
";
$top_buyers_stmt = $pdo->prepare($top_buyers_query);
$top_buyers_stmt->execute();
} else {
$top_buyers_query = "
SELECT
u.id,
u.name,
u.email,
COUNT(tp.id) as purchase_count,
SUM(tp.price_paid) as total_spent,
AVG(tp.price_paid) as avg_purchase
FROM users u
JOIN track_purchases tp ON u.id = tp.user_id
WHERE tp.purchase_date >= ? AND tp.purchase_date <= ?
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 20
";
$top_buyers_stmt = $pdo->prepare($top_buyers_query);
$top_buyers_stmt->execute([$date_from . ' 00:00:00', $date_to . ' 23:59:59']);
}
$top_buyers = $top_buyers_stmt->fetchAll(PDO::FETCH_ASSOC);
// Get daily sales for chart
if ($show_all) {
$daily_sales_query = "
SELECT
DATE(tp.purchase_date) as sale_date,
COUNT(*) as purchase_count,
SUM(tp.price_paid) as daily_revenue
FROM track_purchases tp
GROUP BY DATE(tp.purchase_date)
ORDER BY sale_date DESC
LIMIT 30
";
$daily_sales_stmt = $pdo->prepare($daily_sales_query);
$daily_sales_stmt->execute();
} else {
$daily_sales_query = "
SELECT
DATE(tp.purchase_date) as sale_date,
COUNT(*) as purchase_count,
SUM(tp.price_paid) as daily_revenue
FROM track_purchases tp
WHERE tp.purchase_date >= ? AND tp.purchase_date <= ?
GROUP BY DATE(tp.purchase_date)
ORDER BY sale_date DESC
LIMIT 30
";
$daily_sales_stmt = $pdo->prepare($daily_sales_query);
$daily_sales_stmt->execute([$date_from . ' 00:00:00', $date_to . ' 23:59:59']);
}
$daily_sales = $daily_sales_stmt->fetchAll(PDO::FETCH_ASSOC);
// Get all users for filter dropdown
$users_stmt = $pdo->query("SELECT id, name, email FROM users ORDER BY name");
$all_users = $users_stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!-- Purchases & Sales Management -->
<div class="section-header">
<h2><i class="fas fa-shopping-cart"></i> Purchases & Sales Management</h2>
<p>View all track purchases, sales statistics, and revenue details</p>
</div>
<!-- Filters -->
<div style="background: rgba(255, 255, 255, 0.05); border-radius: 16px; padding: 2rem; margin-bottom: 2rem;">
<form method="GET" action="" style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 1rem; align-items: end;">
<input type="hidden" name="tab" value="purchases">
<div>
<label style="display: block; color: #a0aec0; margin-bottom: 0.5rem; font-weight: 600;">Date From</label>
<input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" style="width: 100%; padding: 0.8rem; border: 1px solid rgba(255, 255, 255, 0.2); border-radius: 8px; background: rgba(255, 255, 255, 0.05); color: white;">
</div>
<div>
<label style="display: block; color: #a0aec0; margin-bottom: 0.5rem; font-weight: 600;">Date To</label>
<input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" style="width: 100%; padding: 0.8rem; border: 1px solid rgba(255, 255, 255, 0.2); border-radius: 8px; background: rgba(255, 255, 255, 0.05); color: white;">
</div>
<div>
<label style="display: block; color: #a0aec0; margin-bottom: 0.5rem; font-weight: 600;">Payment Method</label>
<select name="payment_method" style="width: 100%; padding: 0.8rem; border: 1px solid rgba(255, 255, 255, 0.2); border-radius: 8px; background: rgba(255, 255, 255, 0.05); color: white;">
<option value="all" <?= $payment_method_filter === 'all' ? 'selected' : '' ?>>All Methods</option>
<option value="stripe" <?= $payment_method_filter === 'stripe' ? 'selected' : '' ?>>Stripe</option>
<option value="credits" <?= $payment_method_filter === 'credits' ? 'selected' : '' ?>>Credits</option>
<option value="free" <?= $payment_method_filter === 'free' ? 'selected' : '' ?>>Free</option>
</select>
</div>
<div>
<label style="display: block; color: #a0aec0; margin-bottom: 0.5rem; font-weight: 600;">User</label>
<select name="user_id" style="width: 100%; padding: 0.8rem; border: 1px solid rgba(255, 255, 255, 0.2); border-radius: 8px; background: rgba(255, 255, 255, 0.05); color: white;">
<option value="">All Users</option>
<?php foreach ($all_users as $user): ?>
<option value="<?= $user['id'] ?>" <?= $user_filter == $user['id'] ? 'selected' : '' ?>>
<?= htmlspecialchars($user['name']) ?> (<?= htmlspecialchars($user['email']) ?>)
</option>
<?php endforeach; ?>
</select>
</div>
<div>
<button type="submit" class="btn btn-primary" style="width: 100%;">
<i class="fas fa-filter"></i> Apply Filters
</button>
</div>
<div>
<a href="?tab=purchases" class="btn btn-secondary" style="width: 100%; text-decoration: none; display: block; text-align: center;">
<i class="fas fa-redo"></i> Reset
</a>
</div>
<div>
<a href="?tab=purchases&show_all=1" class="btn btn-primary" style="width: 100%; text-decoration: none; display: block; text-align: center; background: #48bb78;">
<i class="fas fa-list"></i> Show All Purchases
</a>
</div>
</form>
</div>
<!-- Debug Info (only show if no purchases found) -->
<?php if ($purchase_stats['total_purchases'] == 0): ?>
<div style="background: rgba(255, 193, 7, 0.1); border: 2px solid #ffc107; border-radius: 16px; padding: 2rem; margin-bottom: 2rem;">
<h3 style="color: #ffc107; margin: 0 0 1rem 0;"><i class="fas fa-exclamation-triangle"></i> No Purchases Found</h3>
<p style="color: #a0aec0; margin: 0 0 1rem 0;">No purchases found for the selected filters. Here are some things to check:</p>
<ul style="color: #a0aec0; margin: 0; padding-left: 2rem;">
<li>Try clicking "Show All Purchases" to see purchases from any date</li>
<li>Check if purchases exist in the database: <a href="?tab=purchases&show_all=1" style="color: #667eea;">View All</a></li>
<li>Verify the purchase was recorded: Check the <code>track_purchases</code> table directly</li>
<li>Date range: Currently showing from <?= htmlspecialchars($date_from) ?> to <?= htmlspecialchars($date_to) ?></li>
</ul>
<?php
// Quick check: Count all purchases regardless of date
$total_check = $pdo->query("SELECT COUNT(*) as total FROM track_purchases")->fetch(PDO::FETCH_ASSOC);
?>
<p style="color: #a0aec0; margin: 1rem 0 0 0;">
<strong>Total purchases in database:</strong> <?= number_format($total_check['total']) ?>
<?php if ($total_check['total'] > 0): ?>
<br><span style="color: #48bb78;">✓ Purchases exist! Try <a href="?tab=purchases&show_all=1" style="color: #667eea;">showing all purchases</a> to see them.</span>
<?php else: ?>
<br><span style="color: #e53e3e;">⚠ No purchases found in database. The purchase may not have been recorded.</span>
<?php endif; ?>
</p>
</div>
<?php endif; ?>
<!-- Statistics Cards -->
<div class="stats-grid" style="margin-bottom: 3rem;">
<div class="stat-card">
<div class="stat-number"><?= number_format($purchase_stats['total_purchases']) ?></div>
<div class="stat-label">Total Purchases</div>
</div>
<div class="stat-card">
<div class="stat-number">$<?= number_format($purchase_stats['total_revenue'], 2) ?></div>
<div class="stat-label">Total Revenue</div>
</div>
<div class="stat-card">
<div class="stat-number"><?= number_format($purchase_stats['unique_buyers']) ?></div>
<div class="stat-label">Unique Buyers</div>
</div>
<div class="stat-card">
<div class="stat-number"><?= number_format($purchase_stats['unique_tracks_sold']) ?></div>
<div class="stat-label">Tracks Sold</div>
</div>
<div class="stat-card">
<div class="stat-number">$<?= number_format($purchase_stats['avg_purchase_price'], 2) ?></div>
<div class="stat-label">Avg Purchase Price</div>
</div>
<div class="stat-card">
<div class="stat-number"><?= number_format($purchase_stats['stripe_purchases']) ?></div>
<div class="stat-label">Stripe Purchases</div>
</div>
<div class="stat-card">
<div class="stat-number"><?= number_format($purchase_stats['credit_purchases']) ?></div>
<div class="stat-label">Credit Purchases</div>
</div>
<div class="stat-card">
<div class="stat-number"><?= number_format($purchase_stats['total_credits_used']) ?></div>
<div class="stat-label">Credits Used</div>
</div>
</div>
<!-- Revenue Breakdown -->
<div class="section-header">
<h3><i class="fas fa-chart-pie"></i> Revenue Breakdown</h3>
</div>
<div class="stats-grid" style="margin-bottom: 3rem;">
<?php foreach ($revenue_breakdown as $breakdown): ?>
<div class="stat-card">
<div class="stat-number">$<?= number_format($breakdown['total_revenue'], 2) ?></div>
<div class="stat-label"><?= ucfirst($breakdown['revenue_recipient']) ?> Revenue</div>
<div style="font-size: 1.2rem; color: #a0aec0; margin-top: 0.5rem;">
<?= number_format($breakdown['sale_count']) ?> sales
<?php if ($breakdown['revenue_recipient'] === 'artist'): ?>
(<?= number_format($breakdown['unique_artists']) ?> artists)
<?php endif; ?>
</div>
</div>
<?php endforeach; ?>
</div>
<!-- Top Selling Tracks -->
<div class="section-header">
<h3><i class="fas fa-fire"></i> Top Selling Tracks</h3>
</div>
<table class="data-table" style="margin-bottom: 3rem;">
<thead>
<tr>
<th>Rank</th>
<th>Track</th>
<th>Artist</th>
<th>Purchases</th>
<th>Total Revenue</th>
<th>Avg Price</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php if (empty($top_tracks)): ?>
<tr>
<td colspan="7" style="text-align: center; color: #a0aec0; padding: 2rem;">No sales in this period</td>
</tr>
<?php else: ?>
<?php foreach ($top_tracks as $index => $track): ?>
<tr>
<td><strong>#<?= $index + 1 ?></strong></td>
<td>
<a href="/track.php?id=<?= $track['id'] ?>" target="_blank" style="color: #667eea; text-decoration: none;">
<?= htmlspecialchars($track['title']) ?>
</a>
</td>
<td><?= htmlspecialchars($track['artist_name']) ?></td>
<td><?= number_format($track['purchase_count']) ?></td>
<td style="color: #48bb78; font-weight: 600;">$<?= number_format($track['total_revenue'], 2) ?></td>
<td>$<?= number_format($track['avg_price'], 2) ?></td>
<td>
<a href="?tab=purchases&track_id=<?= $track['id'] ?>&date_from=<?= $date_from ?>&date_to=<?= $date_to ?>" class="btn btn-sm btn-secondary" style="text-decoration: none;">
<i class="fas fa-filter"></i> Filter
</a>
</td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
<!-- Top Buyers -->
<div class="section-header">
<h3><i class="fas fa-users"></i> Top Buyers</h3>
</div>
<table class="data-table" style="margin-bottom: 3rem;">
<thead>
<tr>
<th>Rank</th>
<th>Buyer</th>
<th>Email</th>
<th>Purchases</th>
<th>Total Spent</th>
<th>Avg Purchase</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php if (empty($top_buyers)): ?>
<tr>
<td colspan="7" style="text-align: center; color: #a0aec0; padding: 2rem;">No buyers in this period</td>
</tr>
<?php else: ?>
<?php foreach ($top_buyers as $index => $buyer): ?>
<tr>
<td><strong>#<?= $index + 1 ?></strong></td>
<td><?= htmlspecialchars($buyer['name']) ?></td>
<td><?= htmlspecialchars($buyer['email']) ?></td>
<td><?= number_format($buyer['purchase_count']) ?></td>
<td style="color: #48bb78; font-weight: 600;">$<?= number_format($buyer['total_spent'], 2) ?></td>
<td>$<?= number_format($buyer['avg_purchase'], 2) ?></td>
<td>
<a href="?tab=purchases&user_id=<?= $buyer['id'] ?>&date_from=<?= $date_from ?>&date_to=<?= $date_to ?>" class="btn btn-sm btn-secondary" style="text-decoration: none;">
<i class="fas fa-filter"></i> View Purchases
</a>
</td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
<!-- All Purchases Table -->
<div class="section-header">
<h3><i class="fas fa-list"></i> All Purchases (<?= count($purchases) ?> shown)</h3>
<div style="margin-top: 1rem;">
<button onclick="exportPurchases()" class="btn btn-primary">
<i class="fas fa-download"></i> Export to CSV
</button>
</div>
</div>
<div style="background: rgba(255, 255, 255, 0.05); border-radius: 16px; padding: 2rem; overflow-x: auto;">
<table class="data-table">
<thead>
<tr>
<th>ID</th>
<th>Date</th>
<th>Buyer</th>
<th>Track</th>
<th>Artist</th>
<th>Price</th>
<th>Credits Used</th>
<th>Payment Method</th>
<th>Transaction ID</th>
<th>Revenue To</th>
<th>Downloads</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php if (empty($purchases)): ?>
<tr>
<td colspan="12" style="text-align: center; color: #a0aec0; padding: 2rem;">No purchases found for the selected filters</td>
</tr>
<?php else: ?>
<?php foreach ($purchases as $purchase): ?>
<tr>
<td><?= $purchase['purchase_id'] ?></td>
<td><?= date('M j, Y g:i A', strtotime($purchase['purchase_date'])) ?></td>
<td>
<div style="font-weight: 600; color: white;"><?= htmlspecialchars($purchase['buyer_name']) ?></div>
<div style="font-size: 1.2rem; color: #a0aec0;"><?= htmlspecialchars($purchase['buyer_email']) ?></div>
</td>
<td>
<a href="/track.php?id=<?= $purchase['track_id'] ?>" target="_blank" style="color: #667eea; text-decoration: none;">
<?= htmlspecialchars($purchase['track_title']) ?>
</a>
</td>
<td>
<a href="/artist_profile.php?id=<?= $purchase['artist_id'] ?>" target="_blank" style="color: #667eea; text-decoration: none;">
<?= htmlspecialchars($purchase['artist_name']) ?>
</a>
</td>
<td style="color: #48bb78; font-weight: 600;">$<?= number_format($purchase['price_paid'], 2) ?></td>
<td><?= number_format($purchase['credits_used']) ?></td>
<td>
<span class="status-badge" style="background: <?= $purchase['payment_method'] === 'stripe' ? '#667eea' : ($purchase['payment_method'] === 'credits' ? '#48bb78' : '#a0aec0') ?>;">
<?= ucfirst($purchase['payment_method']) ?>
</span>
</td>
<td style="font-family: monospace; font-size: 1.1rem;">
<?= $purchase['stripe_payment_intent_id'] ? htmlspecialchars(substr($purchase['stripe_payment_intent_id'], 0, 20)) . '...' : 'N/A' ?>
</td>
<td>
<span class="status-badge" style="background: <?= $purchase['revenue_recipient'] === 'platform' ? '#e53e3e' : '#48bb78' ?>;">
<?= ucfirst($purchase['revenue_recipient']) ?>
</span>
</td>
<td><?= number_format($purchase['download_count']) ?></td>
<td>
<a href="/track.php?id=<?= $purchase['track_id'] ?>" target="_blank" class="btn btn-sm btn-secondary" style="text-decoration: none;">
<i class="fas fa-external-link-alt"></i>
</a>
</td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
</div>
<script>
function exportPurchases() {
// Get current filter parameters
const params = new URLSearchParams(window.location.search);
params.set('export', 'csv');
// Open export URL
window.location.href = 'admin_api.php?action=export_purchases&' + params.toString();
}
</script>