0, 'completed_signups' => 0, 'verified_signups' => 0, 'mobile_verified_signups' => 0];
$recentSignups = [];
$memberDetails = [];
$commissionLog = [];
$recentRedemptions = [];
$emailCommission = 0;
$mobileCommission = 0;
$surveyCommission = 0;
$surveyCompletedCount = 0;
$emailRewardAmount = 5.00; // Fixed: Rs. 5 per email verification
$mobileRewardAmount = 0;
$surveyReward = floatval($partner['survey_reward'] ?? 0);
$referredMembers = [];
try {
$shopPdo = getPartnerDBConnection();
$panelPdo = getPartnerPanelDBConnection();
// --- Detect if mobile_verified column exists ---
$hasMobileVerified = false;
try {
$cols = $shopPdo->query("SHOW COLUMNS FROM affiliate_signups")->fetchAll(PDO::FETCH_COLUMN);
$hasMobileVerified = in_array('mobile_verified', $cols);
} catch (Exception $e) { /* ignore */ }
// --- STEP 1: Fetch signup stats ---
$mobileSelect = $hasMobileVerified ? "SUM(CASE WHEN mobile_verified = 1 THEN 1 ELSE 0 END) as mobile_verified_signups" : "0 as mobile_verified_signups";
$stmt = $shopPdo->prepare("
SELECT
COUNT(*) as total_signups,
SUM(CASE WHEN signup_completed = 1 THEN 1 ELSE 0 END) as completed_signups,
SUM(CASE WHEN email_verified = 1 THEN 1 ELSE 0 END) as verified_signups,
$mobileSelect
FROM affiliate_signups WHERE affiliate_id = ?
");
$stmt->execute([$partner['id']]);
$signupStats = $stmt->fetch(PDO::FETCH_ASSOC);
// --- STEP 2: Calculate referral commission ---
// Email verification reward = Rs. 5 fixed
// Mobile verification reward = signup_reward - 5 (the rest)
$signupRewardTotal = floatval($partner['signup_reward'] ?? 10);
$mobileRewardAmount = max(0, $signupRewardTotal - $emailRewardAmount);
$emailCommission = intval($signupStats['verified_signups']) * $emailRewardAmount;
$mobileCommission = intval($signupStats['mobile_verified_signups'] ?? 0) * $mobileRewardAmount;
$referralTotal = $emailCommission + $mobileCommission;
// --- STEP 3: Update per-signup commission_earned (referral portion only) ---
if ($hasMobileVerified) {
$stmt = $shopPdo->prepare("
UPDATE affiliate_signups
SET commission_earned = CASE
WHEN mobile_verified = 1 THEN ?
WHEN email_verified = 1 THEN ?
WHEN signup_completed = 1 THEN 0
ELSE 0
END
WHERE affiliate_id = ?
");
$stmt->execute([$signupRewardTotal, $emailRewardAmount, $partner['id']]);
} else {
$stmt = $shopPdo->prepare("
UPDATE affiliate_signups
SET commission_earned = CASE
WHEN email_verified = 1 THEN ?
WHEN signup_completed = 1 THEN 0
ELSE 0
END
WHERE affiliate_id = ?
");
$stmt->execute([$emailRewardAmount, $partner['id']]);
}
// --- STEP 4: Get survey commission from partner_commission_log ---
$surveyCommission = 0;
$surveyCompletedCount = 0;
try {
$stmt = $shopPdo->prepare("
SELECT COUNT(*) as cnt, COALESCE(SUM(amount), 0) as total
FROM partner_commission_log
WHERE affiliate_id = ? AND type = 'survey_revenue_share'
");
$stmt->execute([$partner['id']]);
$surveyRow = $stmt->fetch(PDO::FETCH_ASSOC);
$surveyCommission = floatval($surveyRow['total'] ?? 0);
$surveyCompletedCount = intval($surveyRow['cnt'] ?? 0);
} catch (Exception $e) { /* table may not exist */ }
// --- STEP 5: Update affiliate totals (referral + survey) ---
// IMPORTANT: Don't overwrite survey commission — add it to referral total
$grandTotal = $referralTotal + $surveyCommission;
$stmt = $shopPdo->prepare("
UPDATE affiliates
SET
total_commission_earned = ?,
commission_balance = ? - COALESCE(total_commission_redeemed, 0),
total_signups = ?,
total_verified_signups = ?
WHERE id = ?
");
$stmt->execute([
$grandTotal,
$grandTotal,
$signupStats['total_signups'],
$signupStats['verified_signups'],
$partner['id']
]);
// Refresh partner data
$stmt = $shopPdo->prepare("SELECT * FROM affiliates WHERE id = ?");
$stmt->execute([$partner['id']]);
$partner = $stmt->fetch(PDO::FETCH_ASSOC);
// Survey reward amount from affiliates table
$surveyReward = floatval($partner['survey_reward'] ?? $partner['revenue_share_per_survey'] ?? 0);
// --- STEP 6: Recent signups ---
$stmt = $shopPdo->prepare("
SELECT * FROM affiliate_signups
WHERE affiliate_id = ?
ORDER BY clicked_at DESC LIMIT 10
");
$stmt->execute([$partner['id']]);
$recentSignups = $stmt->fetchAll(PDO::FETCH_ASSOC);
// --- STEP 7: Member details from panel DB ---
$memberDetails = [];
foreach ($recentSignups as $signup) {
if (!empty($signup['panel_user_id'])) {
$referredMembers[] = intval($signup['panel_user_id']);
try {
$stmt = $panelPdo->prepare("
SELECT u.id, u.email, u.status, up.points, up.total_earned, up.total_redeemed
FROM users u LEFT JOIN user_points up ON u.id = up.user_id
WHERE u.id = ?
");
$stmt->execute([$signup['panel_user_id']]);
$member = $stmt->fetch(PDO::FETCH_ASSOC);
if ($member) $memberDetails[$signup['panel_user_id']] = $member;
} catch (Exception $e) { /* ignore */ }
}
}
// --- STEP 8: Build unified commission log ---
$commissionLog = [];
// 8a: Email verification rewards
$emailQuery = "SELECT id, email, panel_user_id, email_verified, verified_at, clicked_at"
. ($hasMobileVerified ? ", mobile_verified, mobile_verified_at" : "")
. " FROM affiliate_signups WHERE affiliate_id = ? AND email_verified = 1 ORDER BY verified_at DESC";
$stmt = $shopPdo->prepare($emailQuery);
$stmt->execute([$partner['id']]);
$verifiedSignups = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($verifiedSignups as $vs) {
$maskedEmail = maskEmail($vs['email'] ?? 'Member');
// Email verification entry
$commissionLog[] = [
'type' => 'email_verified',
'amount' => $emailRewardAmount,
'description' => "Email verification reward - $maskedEmail",
'created_at' => $vs['verified_at'] ?? $vs['clicked_at'],
'panel_user_id' => $vs['panel_user_id']
];
// Mobile verification entry (only if column exists and is verified)
if ($hasMobileVerified && !empty($vs['mobile_verified']) && $mobileRewardAmount > 0) {
$commissionLog[] = [
'type' => 'mobile_verified',
'amount' => $mobileRewardAmount,
'description' => "Mobile verification reward - $maskedEmail",
'created_at' => $vs['mobile_verified_at'] ?? $vs['verified_at'] ?? $vs['clicked_at'],
'panel_user_id' => $vs['panel_user_id']
];
}
}
// 8b: Survey revenue share from partner_commission_log
try {
$stmt = $shopPdo->prepare("
SELECT * FROM partner_commission_log
WHERE affiliate_id = ? AND type = 'survey_revenue_share'
ORDER BY created_at DESC
");
$stmt->execute([$partner['id']]);
$surveyLogs = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($surveyLogs as $sl) {
$commissionLog[] = [
'type' => 'survey_revenue_share',
'amount' => floatval($sl['amount']),
'description' => $sl['description'] ?? 'Survey revenue share',
'created_at' => $sl['created_at'],
'panel_user_id' => $sl['panel_user_id'] ?? null
];
}
} catch (Exception $e) { /* table may not exist */ }
// Sort all entries by date descending
usort($commissionLog, function($a, $b) {
return strtotime($b['created_at'] ?? '2000-01-01') - strtotime($a['created_at'] ?? '2000-01-01');
});
// --- STEP 9: Recent redemptions ---
try {
$stmt = $shopPdo->prepare("SELECT * FROM partner_redemptions WHERE affiliate_id = ? ORDER BY created_at DESC LIMIT 10");
$stmt->execute([$partner['id']]);
$recentRedemptions = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) { /* table may not exist */ }
} catch (Exception $e) {
error_log("Partner dashboard error for partner " . ($partner['id'] ?? '?') . ": " . $e->getMessage());
if (function_exists('logPartnerActivity')) {
logPartnerActivity('Dashboard error: ' . $e->getMessage(), ['partner_id' => $partner['id']]);
}
$signupStats = ['total_signups' => 0, 'completed_signups' => 0, 'verified_signups' => 0, 'mobile_verified_signups' => 0];
$recentSignups = [];
$memberDetails = [];
$commissionLog = [];
$recentRedemptions = [];
$emailCommission = 0;
$mobileCommission = 0;
$surveyCommission = 0;
$surveyCompletedCount = 0;
$emailRewardAmount = 5;
$mobileRewardAmount = 0;
$surveyReward = floatval($partner['survey_reward'] ?? 5.00);
if (!isset($referredMembers)) $referredMembers = [];
}
$minRedemption = defined('PARTNER_MIN_REDEMPTION') ? PARTNER_MIN_REDEMPTION : 2000;
$canRedeem = floatval($partner['commission_balance'] ?? 0) >= $minRedemption;
// Handle UPI update
$upiSuccess = '';
$upiError = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'update_upi' && !$isInactive) {
$newUpi = trim($_POST['upi_id'] ?? '');
if (!empty($newUpi) && preg_match('/^[\w\.\-]+@[\w\.\-]+$/', $newUpi)) {
try {
$stmt = $shopPdo->prepare("UPDATE affiliates SET upi_id = ? WHERE id = ?");
$stmt->execute([$newUpi, $partner['id']]);
$partner['upi_id'] = $newUpi;
$upiSuccess = 'UPI ID updated successfully!';
} catch (Exception $e) {
$upiError = 'Error updating UPI ID.';
}
} else {
$upiError = 'Please enter a valid UPI ID (e.g. name@upi).';
}
}
?>
Partner Dashboard -
Your account is currently inactive. Some features are limited. Contact support for assistance.
Welcome back, !
Affiliate Code:
Referral Link: /signup.php?ref=
View Referrals
Track your signups and their status
Rewards & Earnings
View all earnings and redeem
Need Help?
Contact our support team
Contact Support
My Referrals
No referrals yet
Share your referral link to start earning commissions!
| Date |
Email |
Status |
Verified |
Commission |
|
|
Signed Up
Clicked Only
|
Verified
Pending
|
₹
|
Rewards & Earnings
Commission Breakdown
₹ ( x ₹)
₹ ( x ₹)
₹ ( surveys)
Redeem Earnings
₹ is eligible for redemption!
Request Redemption
You need ₹ more to request a redemption. (Minimum: ₹)
Earnings History
No earnings yet
Refer members and earn commissions when they verify and complete surveys!
| Date |
Type |
Description |
Amount |
|
Email Verified';
} elseif ($type === 'mobile_verified') {
echo 'Mobile Verified';
} elseif ($type === 'survey_revenue_share') {
echo 'Survey';
} else {
echo 'Referral';
}
?>
|
|
₹
|
Account Settings
Partner Information
Your Reward Structure
₹
₹
₹ per survey