Access DeniedInvalid security key. Add ?key=YOUR_KEY to the URL.
');
}
echo "
Partner Data Sync Utility
";
echo "🔄 Partner Data Sync Utility
";
echo "Synchronizing affiliate/partner data between databases...
";
echo "
";
try {
$shopPdo = getPartnerDBConnection();
$panelPdo = getPartnerPanelDBConnection();
$shopPdo->beginTransaction();
// ========================================
// STEP 1: Check and Update Table Structure
// ========================================
echo "Step 1: Checking Database Structure
";
// Check affiliate_signups table
$stmt = $shopPdo->query("DESCRIBE affiliate_signups");
$columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
$hasCommissionColumn = in_array('commission_earned', $columns);
$hasCommissionPaid = in_array('commission_paid', $columns);
$hasVerifiedAt = in_array('verified_at', $columns);
if (!$hasCommissionColumn) {
echo "⚠ Adding commission_earned column...
";
$shopPdo->exec("ALTER TABLE affiliate_signups ADD COLUMN commission_earned DECIMAL(10,2) DEFAULT 0.00");
} else {
echo "✓ commission_earned column exists
";
}
if (!$hasCommissionPaid) {
echo "⚠ Adding commission_paid column...
";
$shopPdo->exec("ALTER TABLE affiliate_signups ADD COLUMN commission_paid TINYINT(1) DEFAULT 0");
} else {
echo "✓ commission_paid column exists
";
}
if (!$hasVerifiedAt) {
echo "⚠ Adding verified_at timestamp column...
";
$shopPdo->exec("ALTER TABLE affiliate_signups ADD COLUMN verified_at TIMESTAMP NULL DEFAULT NULL");
} else {
echo "✓ verified_at column exists
";
}
// ========================================
// STEP 2: Set Commission Rates
// ========================================
echo "Step 2: Commission Rate Configuration
";
$signupCommission = 5.00; // ₹5 per completed signup
$verifiedCommission = 10.00; // ₹10 per verified signup (total, not additional)
echo "";
echo "Commission Rates:
";
echo "• Per completed signup: ₹" . number_format($signupCommission, 2) . "
";
echo "• Per verified signup: ₹" . number_format($verifiedCommission, 2) . "";
echo "
";
// ========================================
// STEP 3: Update Commission for All Signups
// ========================================
echo "Step 3: Calculating Commissions
";
$stmt = $shopPdo->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 OR commission_earned !=
CASE
WHEN email_verified = 1 THEN ?
WHEN signup_completed = 1 THEN ?
ELSE 0
END
");
$stmt->execute([$verifiedCommission, $signupCommission, $verifiedCommission, $signupCommission]);
$updated = $stmt->rowCount();
echo "✓ Updated commission for $updated signups
";
// ========================================
// STEP 4: Sync Verified Timestamps
// ========================================
echo "Step 4: Syncing Verification Timestamps
";
// Get all verified signups that don't have verified_at timestamp
$stmt = $shopPdo->query("
SELECT asig.id, asig.panel_user_id, asig.email
FROM affiliate_signups asig
WHERE asig.email_verified = 1
AND asig.verified_at IS NULL
AND asig.panel_user_id IS NOT NULL
");
$toSync = $stmt->fetchAll();
$syncedCount = 0;
foreach ($toSync as $signup) {
try {
// Get actual verification time from panel database
$stmt = $panelPdo->prepare("
SELECT ev.created_at as verified_time
FROM email_verifications ev
JOIN users u ON ev.user_id = u.id
WHERE u.id = ? AND u.email_verified = 1
ORDER BY ev.created_at DESC
LIMIT 1
");
$stmt->execute([$signup['panel_user_id']]);
$verificationData = $stmt->fetch();
if ($verificationData) {
$stmt = $shopPdo->prepare("
UPDATE affiliate_signups
SET verified_at = ?
WHERE id = ?
");
$stmt->execute([$verificationData['verified_time'], $signup['id']]);
$syncedCount++;
}
} catch (Exception $e) {
// Skip individual errors
continue;
}
}
echo "✓ Synced verification timestamps for $syncedCount members
";
// ========================================
// STEP 5: Calculate Total Commission Per Affiliate
// ========================================
echo "Step 5: Calculating Affiliate Totals
";
$stmt = $shopPdo->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.signup_completed = 1 THEN 1 ELSE 0 END) as completed_signups,
SUM(CASE WHEN asig.email_verified = 1 THEN 1 ELSE 0 END) as verified_signups,
SUM(CASE WHEN asig.reward_paid = 1 THEN 1 ELSE 0 END) as rewards_paid
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 Clicks | ";
echo "Completed | ";
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['completed_signups']} | ";
echo "{$affiliate['verified_signups']} | ";
echo "₹" . number_format($affiliate['total_earned'], 2) . " | ";
echo "
";
// Update affiliate totals
$updateStmt = $shopPdo->prepare("
UPDATE affiliates
SET
total_commission_earned = ?,
commission_balance = ? - COALESCE(total_commission_redeemed, 0),
total_signups = ?,
total_verified_signups = ?
WHERE id = ?
");
$updateStmt->execute([
$affiliate['total_earned'],
$affiliate['total_earned'],
$affiliate['total_signups'],
$affiliate['verified_signups'],
$affiliate['id']
]);
}
echo "
";
// ========================================
// STEP 6: Verify Affiliate Table Structure
// ========================================
echo "Step 6: Verifying Affiliates Table
";
$stmt = $shopPdo->query("DESCRIBE affiliates");
$columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
$requiredColumns = ['total_commission_earned', 'total_commission_redeemed', 'commission_balance', 'total_signups', 'total_verified_signups'];
$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 "";
echo "ERROR: Missing columns in affiliates table!
";
echo "Please add these columns manually or run the database setup SQL.";
echo "
";
$shopPdo->rollBack();
exit;
}
// ========================================
// STEP 7: Summary Statistics
// ========================================
echo "Step 7: Summary Statistics
";
// Get overall stats
$stmt = $shopPdo->query("
SELECT
COUNT(DISTINCT affiliate_id) as total_affiliates,
COUNT(*) as total_clicks,
SUM(CASE WHEN signup_completed = 1 THEN 1 ELSE 0 END) as total_signups,
SUM(CASE WHEN email_verified = 1 THEN 1 ELSE 0 END) as total_verified,
SUM(commission_earned) as total_commission_earned
FROM affiliate_signups
");
$overallStats = $stmt->fetch();
echo "";
echo "
Overall Statistics:
";
echo "
";
echo "- Total Affiliates: " . number_format($overallStats['total_affiliates']) . "
";
echo "- Total Clicks: " . number_format($overallStats['total_clicks']) . "
";
echo "- Completed Signups: " . number_format($overallStats['total_signups']) . "
";
echo "- Verified Members: " . number_format($overallStats['total_verified']) . "
";
echo "- Total Commission: ₹" . number_format($overallStats['total_commission_earned'], 2) . "
";
echo "
";
echo "
";
echo "
Conversion Rates:
";
echo "
";
echo "- Click to Signup: " . number_format($clickToSignup, 2) . "%
";
echo "- Signup to Verified: " . number_format($signupToVerified, 2) . "%
";
echo "
";
echo "
";
echo "";
echo "
✅ Data Sync Complete!
";
echo "
What was done:
";
echo "
";
echo "- ✓ Verified and updated database structure
";
echo "- ✓ Calculated commission for all signups
";
echo "- ✓ Synced verification timestamps
";
echo "- ✓ Updated affiliate total earnings
";
echo "- ✓ Refreshed commission balances
";
echo "
";
echo "
Next Steps:
";
echo "";
echo "- Verify partner dashboards - Check that all numbers are displaying correctly
";
echo "- DELETE THIS FILE (data-sync.php) for security
";
echo "- Run this sync periodically (e.g., weekly) to ensure data consistency
";
echo "
";
echo "";
echo "⚠️ SECURITY WARNING: Please delete this file (data-sync.php) after use!";
echo "
";
echo "
";
echo "Go to Partner Dashboard
";
} catch (Exception $e) {
if (isset($shopPdo) && $shopPdo->inTransaction()) {
$shopPdo->rollBack();
}
echo "ERROR: " . htmlspecialchars($e->getMessage()) . "
";
echo "Check your database connection and table structure.
";
}
echo "";
?>