File size: 4,841 Bytes
0dff816 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
<?php
// api/agent-functions.php
// Database connection
function getDBConnection() {
static $conn;
if (!$conn) {
$host = '127.0.0.1';
$dbname = 'jmdb';
$username = 'root';
$password = 'YourStrongPassword123';
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
}
return $conn;
}
// Get pending agents for a sponsor
function getPendingAgents($sponsorId) {
$conn = getDBConnection();
$stmt = $conn->prepare("
SELECT aa.*, u.username, u.email
FROM agent_applications aa
JOIN users u ON aa.user_id = u.id
WHERE aa.sponsor_id = ? AND aa.status = 'pending'
ORDER BY aa.applied_at DESC
");
$stmt->execute([$sponsorId]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get agent statistics for a sponsor
function getAgentStats($sponsorId) {
$conn = getDBConnection();
// Initialize default stats
$stats = [
'pending' => 0,
'approved' => 0,
'rejected' => 0,
'documents_needed' => 0,
'total_applications' => 0,
'commission_this_month' => 0.00,
'total_commission' => 0.00
];
try {
// Count applications by status
$stmt = $conn->prepare("
SELECT
status,
COUNT(*) as count
FROM agent_applications
WHERE sponsor_id = ?
GROUP BY status
");
$stmt->execute([$sponsorId]);
$statusCounts = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($statusCounts as $row) {
$stats[$row['status']] = $row['count'];
$stats['total_applications'] += $row['count'];
}
// Get commission data
$stmt = $conn->prepare("
SELECT
COALESCE(SUM(CASE WHEN MONTH(created_at) = MONTH(CURRENT_DATE()) THEN commission_amount ELSE 0 END), 0) as commission_this_month,
COALESCE(SUM(commission_amount), 0) as total_commission
FROM commissions
WHERE sponsor_id = ? AND status = 'paid'
");
$stmt->execute([$sponsorId]);
$commissionData = $stmt->fetch(PDO::FETCH_ASSOC);
if ($commissionData) {
$stats['commission_this_month'] = $commissionData['commission_this_month'];
$stats['total_commission'] = $commissionData['total_commission'];
}
} catch (Exception $e) {
error_log("Error getting agent stats: " . $e->getMessage());
}
return $stats;
}
// Review agent application (admin function)
function reviewAgentApplication($agentId, $adminId, $status, $notes = '') {
$conn = getDBConnection();
try {
$conn->beginTransaction();
// Update agent application
$stmt = $conn->prepare("
UPDATE agent_applications
SET status = ?, reviewed_by = ?, reviewed_at = NOW(), review_notes = ?
WHERE id = ?
");
$stmt->execute([$status, $adminId, $notes, $agentId]);
// If approved, update user type to agent
if ($status === 'approved') {
$stmt = $conn->prepare("
UPDATE users u
JOIN agent_applications aa ON u.id = aa.user_id
SET u.user_type = 'agent'
WHERE aa.id = ?
");
$stmt->execute([$agentId]);
}
$conn->commit();
return true;
} catch (Exception $e) {
$conn->rollBack();
error_log("Error reviewing agent: " . $e->getMessage());
return false;
}
}
// Search agents
function searchAgents($sponsorId, $searchTerm) {
$conn = getDBConnection();
$searchTerm = "%$searchTerm%";
$stmt = $conn->prepare("
SELECT aa.*, u.username, u.email
FROM agent_applications aa
JOIN users u ON aa.user_id = u.id
WHERE aa.sponsor_id = ?
AND (aa.full_name LIKE ? OR aa.phone LIKE ? OR u.username LIKE ? OR aa.id LIKE ?)
ORDER BY aa.applied_at DESC
");
$stmt->execute([$sponsorId, $searchTerm, $searchTerm, $searchTerm, $searchTerm]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get agent details for review
function getAgentDetails($agentId) {
$conn = getDBConnection();
$stmt = $conn->prepare("
SELECT aa.*, u.username, u.email, u.created_at as user_joined
FROM agent_applications aa
JOIN users u ON aa.user_id = u.id
WHERE aa.id = ?
");
$stmt->execute([$agentId]);
return $stmt->fetch(PDO::FETCH_ASSOC);
}
?> |