Partner Commission Sync Script";
echo "
";
try {
$pdo = getPartnerDBConnection();
$pdo->beginTransaction();
echo "Step 1: Checking affiliate_signups table structure
";
// Check if commission_earned column exists in affiliate_signups
$stmt = $pdo->query("DESCRIBE affiliate_signups");
$columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
$hasCommissionColumn = in_array('commission_earned', $columns);
if ($hasCommissionColumn) {
echo "✓ commission_earned column exists in affiliate_signups
";
} else {
echo "⚠ commission_earned column missing - will add it
";
$pdo->exec("ALTER TABLE affiliate_signups ADD COLUMN commission_earned DECIMAL(10,2) DEFAULT 0.00");
$pdo->exec("ALTER TABLE affiliate_signups ADD COLUMN commission_paid TINYINT(1) DEFAULT 0");
echo "✓ Added commission columns to affiliate_signups
";
}
echo "Step 2: Setting default commission amounts
";
// Set commission rate (you can change these)
$signupCommission = 5.00; // ₹5 per signup
$verifiedCommission = 10.00; // ₹10 per verified signup (total, not additional)
echo "Commission rates:
";
echo "";
echo "- Per signup: ₹" . number_format($signupCommission, 2) . "
";
echo "- Per verified signup: ₹" . number_format($verifiedCommission, 2) . "
";
echo "
";
echo "Step 3: Calculating commissions for all signups
";
// Update commission_earned for all signups based on verification status
$stmt = $pdo->prepare("
UPDATE affiliate_signups
SET commission_earned = CASE
WHEN email_verified = 1 THEN ?
WHEN signup_completed = 1 THEN ?
ELSE 0
END
WHERE commission_earned = 0 OR commission_earned IS NULL
");
$stmt->execute([$verifiedCommission, $signupCommission]);
$updated = $stmt->rowCount();
echo "✓ Updated commission for $updated signups
";
echo "Step 4: Calculating total commission per affiliate
";
// Get all affiliates with their total commissions
$stmt = $pdo->query("
SELECT
a.id,
a.affiliate_code,
a.company_name,
COALESCE(SUM(asig.commission_earned), 0) as total_earned,
COUNT(asig.id) as total_signups,
SUM(CASE WHEN asig.email_verified = 1 THEN 1 ELSE 0 END) as verified_signups
FROM affiliates a
LEFT JOIN affiliate_signups asig ON a.id = asig.affiliate_id
GROUP BY a.id
");
$affiliates = $stmt->fetchAll();
echo "";
echo "";
echo "| Affiliate Code | ";
echo "Company | ";
echo "Total Signups | ";
echo "Verified | ";
echo "Commission Earned | ";
echo "
";
foreach ($affiliates as $affiliate) {
echo "";
echo "| {$affiliate['affiliate_code']} | ";
echo "{$affiliate['company_name']} | ";
echo "{$affiliate['total_signups']} | ";
echo "{$affiliate['verified_signups']} | ";
echo "₹" . number_format($affiliate['total_earned'], 2) . " | ";
echo "
";
// Update affiliate totals
$updateStmt = $pdo->prepare("
UPDATE affiliates
SET
total_commission_earned = ?,
commission_balance = ?,
total_commission_redeemed = 0
WHERE id = ?
");
$updateStmt->execute([
$affiliate['total_earned'],
$affiliate['total_earned'],
$affiliate['id']
]);
}
echo "
";
echo "Step 5: Verifying affiliates table structure
";
// Check affiliates table has commission columns
$stmt = $pdo->query("DESCRIBE affiliates");
$columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
$requiredColumns = ['total_commission_earned', 'total_commission_redeemed', 'commission_balance'];
$missingColumns = [];
foreach ($requiredColumns as $col) {
if (in_array($col, $columns)) {
echo "✓ Column '$col' exists
";
} else {
echo "✗ Column '$col' missing
";
$missingColumns[] = $col;
}
}
if (!empty($missingColumns)) {
echo "ERROR: Missing columns in affiliates table!
";
echo "Please run the database setup SQL again.
";
$pdo->rollBack();
exit;
}
$pdo->commit();
echo "
";
echo "✓ Commission Sync Complete!
";
echo "What happened:
";
echo "";
echo "- Calculated commission for all signups
";
echo "- Updated affiliate total earnings
";
echo "- Set commission balances
";
echo "
";
echo "Next Steps:
";
echo "";
echo "- Refresh the partner dashboard - You should now see the correct numbers
";
echo "- DELETE THIS FILE (sync-commissions.php) for security
";
echo "- Commission rates can be adjusted in this script if needed
";
echo "
";
echo "
";
echo "Go to Partner Dashboard
";
} catch (Exception $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
echo "Error: " . $e->getMessage() . "
";
echo "Check your database connection and table structure.
";
}
?>