276 lines
18 KiB
PHP
276 lines
18 KiB
PHP
<?php
|
||
require_once('/opt/wevads/config/credentials.php');
|
||
error_reporting(E_ALL);
|
||
ini_set('display_errors', 0);
|
||
|
||
try {
|
||
$pdo = get_pdo('adx_system');
|
||
} catch (PDOException $e) { die("Database connection failed"); }
|
||
|
||
try {
|
||
$pdo->exec("CREATE SCHEMA IF NOT EXISTS admin");
|
||
$pdo->exec("CREATE TABLE IF NOT EXISTS admin.leads (
|
||
id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE,
|
||
first_name VARCHAR(100), last_name VARCHAR(100), country VARCHAR(10),
|
||
segment VARCHAR(20) DEFAULT 'cold', isp VARCHAR(50),
|
||
opens_count INTEGER DEFAULT 0, clicks_count INTEGER DEFAULT 0,
|
||
source_file VARCHAR(255), created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW()
|
||
)");
|
||
} catch (PDOException $e) {}
|
||
|
||
$page = max(1, intval($_GET['page'] ?? 1));
|
||
$perPage = 50;
|
||
$offset = ($page - 1) * $perPage;
|
||
$filter = $_GET['filter'] ?? 'all';
|
||
$search = trim($_GET['search'] ?? '');
|
||
|
||
$where = []; $params = [];
|
||
if ($filter === 'hot') { $where[] = "segment = 'hot'"; }
|
||
elseif ($filter === 'warm') { $where[] = "segment = 'warm'"; }
|
||
elseif ($filter === 'cold') { $where[] = "segment = 'cold'"; }
|
||
elseif ($filter === 'openers') { $where[] = "opens_count > 0"; }
|
||
elseif ($filter === 'clickers') { $where[] = "clicks_count > 0"; }
|
||
if ($search) { $where[] = "email ILIKE ?"; $params[] = "%$search%"; }
|
||
$whereSQL = $where ? 'WHERE ' . implode(' AND ', $where) : '';
|
||
|
||
$stats = [
|
||
'total' => $pdo->query("SELECT COUNT(*) FROM admin.leads")->fetchColumn() ?: 0,
|
||
'hot' => $pdo->query("SELECT COUNT(*) FROM admin.leads WHERE segment='hot'")->fetchColumn() ?: 0,
|
||
'warm' => $pdo->query("SELECT COUNT(*) FROM admin.leads WHERE segment='warm'")->fetchColumn() ?: 0,
|
||
'cold' => $pdo->query("SELECT COUNT(*) FROM admin.leads WHERE segment='cold'")->fetchColumn() ?: 0,
|
||
'openers' => $pdo->query("SELECT COUNT(*) FROM admin.leads WHERE opens_count > 0")->fetchColumn() ?: 0,
|
||
'clickers' => $pdo->query("SELECT COUNT(*) FROM admin.leads WHERE clicks_count > 0")->fetchColumn() ?: 0,
|
||
];
|
||
|
||
$ispStats = $pdo->query("SELECT COALESCE(isp, 'Other') as isp, COUNT(*) as cnt FROM admin.leads GROUP BY isp ORDER BY cnt DESC LIMIT 8")->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$countStmt = $pdo->prepare("SELECT COUNT(*) FROM admin.leads $whereSQL");
|
||
$countStmt->execute($params);
|
||
$total = $countStmt->fetchColumn();
|
||
$totalPages = max(1, ceil($total / $perPage));
|
||
|
||
$stmt = $pdo->prepare("SELECT * FROM admin.leads $whereSQL ORDER BY created_at DESC LIMIT $perPage OFFSET $offset");
|
||
$stmt->execute($params);
|
||
$leads = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
|
||
if ($_POST['action'] === 'delete' && isset($_POST['id'])) {
|
||
$pdo->prepare("DELETE FROM admin.leads WHERE id = ?")->execute([$_POST['id']]);
|
||
header("Location: crm.php?filter=$filter&page=$page"); exit;
|
||
}
|
||
if ($_POST['action'] === 'export') {
|
||
header('Content-Type: text/csv');
|
||
header('Content-Disposition: attachment; filename="leads.csv"');
|
||
$out = fopen('php://output', 'w');
|
||
fputcsv($out, ['Email','Name','Country','ISP','Segment','Opens','Clicks']);
|
||
$rows = $pdo->query("SELECT email,first_name,last_name,country,isp,segment,opens_count,clicks_count FROM admin.leads")->fetchAll();
|
||
foreach ($rows as $r) { fputcsv($out, [$r['email'],$r['first_name'].' '.$r['last_name'],$r['country'],$r['isp'],$r['segment'],$r['opens_count'],$r['clicks_count']]); }
|
||
fclose($out); exit;
|
||
}
|
||
}
|
||
?>
|
||
<!DOCTYPE html>
|
||
<html><head>
|
||
<meta charset="UTF-8">
|
||
<title>WEVAL SEND CRM</title>
|
||
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700&display=swap" rel="stylesheet">
|
||
<style>
|
||
:root{--primary:#2563eb;--primary-dark:#1d4ed8;--secondary:#7c3aed;--success:#10b981;--warning:#f59e0b;--danger:#ef4444;--bg-dark:#0f172a;--bg-card:#1e293b;--bg-hover:#334155;--text-primary:#f1f5f9;--text-secondary:#94a3b8;--border:#334155}
|
||
*{box-sizing:border-box;margin:0;padding:0}
|
||
body{font-family:'Inter',sans-serif;background:var(--bg-dark);color:var(--text-primary);min-height:100vh}
|
||
.header{background:linear-gradient(135deg,#2563eb 0%,#7c3aed 100%);padding:0 30px;height:70px;display:flex;align-items:center;justify-content:space-between;box-shadow:0 4px 20px rgba(37,99,235,0.3)}
|
||
.logo{display:flex;align-items:center;gap:12px}
|
||
.logo-icon{width:45px;height:45px;background:rgba(255,255,255,0.2);border-radius:12px;display:flex;align-items:center;justify-content:center;font-size:24px}
|
||
.logo-text{font-size:22px;font-weight:700;color:white}
|
||
.header-nav{display:flex;gap:8px}
|
||
.nav-btn{padding:10px 20px;background:rgba(255,255,255,0.15);border:1px solid rgba(255,255,255,0.2);border-radius:10px;color:white;text-decoration:none;font-weight:500;font-size:14px}
|
||
.nav-btn:hover{background:rgba(255,255,255,0.25)}
|
||
.layout{display:flex;min-height:calc(100vh - 70px)}
|
||
.sidebar{width:280px;background:var(--bg-card);border-right:1px solid var(--border);padding:24px}
|
||
.sidebar-section{background:var(--bg-dark);border-radius:16px;padding:20px;border:1px solid var(--border);margin-bottom:20px}
|
||
.section-title{font-size:11px;font-weight:600;text-transform:uppercase;letter-spacing:1px;color:var(--text-secondary);margin-bottom:16px}
|
||
.segment-list{display:flex;flex-direction:column;gap:8px}
|
||
.segment-card{display:flex;align-items:center;justify-content:space-between;padding:14px 16px;background:var(--bg-card);border-radius:12px;text-decoration:none;color:var(--text-primary);border:2px solid transparent}
|
||
.segment-card:hover{background:var(--bg-hover)}
|
||
.segment-card.active{border-color:var(--primary);background:rgba(37,99,235,0.1)}
|
||
.segment-info{display:flex;align-items:center;gap:10px}
|
||
.segment-icon{width:36px;height:36px;border-radius:10px;display:flex;align-items:center;justify-content:center;font-size:18px}
|
||
.all .segment-icon{background:linear-gradient(135deg,#2563eb,#7c3aed)}
|
||
.hot .segment-icon{background:linear-gradient(135deg,#ef4444,#f97316)}
|
||
.warm .segment-icon{background:linear-gradient(135deg,#f59e0b,#fbbf24)}
|
||
.cold .segment-icon{background:linear-gradient(135deg,#6b7280,#9ca3af)}
|
||
.openers .segment-icon{background:linear-gradient(135deg,#10b981,#34d399)}
|
||
.clickers .segment-icon{background:linear-gradient(135deg,#06b6d4,#22d3ee)}
|
||
.segment-name{font-weight:500;font-size:14px}
|
||
.segment-count{font-size:18px;font-weight:700;color:#3b82f6}
|
||
.hot .segment-count{color:#ef4444}
|
||
.warm .segment-count{color:#f59e0b}
|
||
.cold .segment-count{color:#6b7280}
|
||
.openers .segment-count{color:#10b981}
|
||
.clickers .segment-count{color:#06b6d4}
|
||
.isp-list{display:flex;flex-direction:column;gap:12px}
|
||
.isp-item{display:flex;align-items:center;gap:12px}
|
||
.isp-name{width:70px;font-size:12px;color:var(--text-secondary);font-weight:500}
|
||
.isp-bar{flex:1;height:8px;background:var(--bg-card);border-radius:4px;overflow:hidden}
|
||
.isp-fill{height:100%;border-radius:4px;background:linear-gradient(90deg,var(--primary),var(--secondary))}
|
||
.isp-count{width:45px;text-align:right;font-size:12px;font-weight:600}
|
||
.main{flex:1;padding:24px;overflow-x:auto}
|
||
.stats-bar{display:grid;grid-template-columns:repeat(4,1fr);gap:16px;margin-bottom:24px}
|
||
.stat-card{background:var(--bg-card);border-radius:16px;padding:20px;border:1px solid var(--border);position:relative;overflow:hidden}
|
||
.stat-card::before{content:'';position:absolute;top:0;left:0;right:0;height:4px;background:linear-gradient(90deg,#2563eb,#7c3aed)}
|
||
.stat-value{font-size:32px;font-weight:700;margin-bottom:4px}
|
||
.stat-label{font-size:13px;color:var(--text-secondary)}
|
||
.stat-icon{position:absolute;right:20px;top:50%;transform:translateY(-50%);font-size:40px;opacity:0.2}
|
||
.toolbar{display:flex;gap:12px;margin-bottom:20px;flex-wrap:wrap;align-items:center}
|
||
.search-box{flex:1;min-width:300px;display:flex}
|
||
.search-input{flex:1;padding:14px 20px;background:var(--bg-card);border:2px solid var(--border);border-right:none;border-radius:12px 0 0 12px;color:var(--text-primary);font-size:14px;outline:none}
|
||
.search-input:focus{border-color:var(--primary)}
|
||
.search-input::placeholder{color:var(--text-secondary)}
|
||
.search-btn{padding:14px 24px;background:var(--primary);border:2px solid var(--primary);border-radius:0 12px 12px 0;color:white;font-weight:600;cursor:pointer}
|
||
.btn{padding:14px 24px;border-radius:12px;font-weight:600;font-size:14px;cursor:pointer;display:inline-flex;align-items:center;gap:8px;text-decoration:none;border:none}
|
||
.btn-primary{background:linear-gradient(135deg,#2563eb,#7c3aed);color:white}
|
||
.btn-secondary{background:var(--bg-card);color:var(--text-primary);border:2px solid var(--border)}
|
||
.table-container{background:var(--bg-card);border-radius:16px;border:1px solid var(--border);overflow:hidden}
|
||
.table-header{padding:16px 20px;background:var(--bg-dark);border-bottom:1px solid var(--border)}
|
||
.table-title{font-size:14px;color:var(--text-secondary)}
|
||
.table-title strong{color:var(--text-primary)}
|
||
table{width:100%;border-collapse:collapse}
|
||
th{padding:16px 20px;text-align:left;font-size:11px;font-weight:600;text-transform:uppercase;color:var(--text-secondary);background:var(--bg-dark);border-bottom:1px solid var(--border)}
|
||
td{padding:16px 20px;border-bottom:1px solid var(--border);font-size:14px}
|
||
tr:hover td{background:var(--bg-hover)}
|
||
.email-cell{font-family:monospace;font-size:13px;color:#3b82f6}
|
||
.badge{display:inline-flex;align-items:center;gap:6px;padding:6px 12px;border-radius:20px;font-size:12px;font-weight:600}
|
||
.badge-hot{background:rgba(239,68,68,0.15);color:#f87171;border:1px solid rgba(239,68,68,0.3)}
|
||
.badge-warm{background:rgba(245,158,11,0.15);color:#fbbf24;border:1px solid rgba(245,158,11,0.3)}
|
||
.badge-cold{background:rgba(107,114,128,0.15);color:#9ca3af;border:1px solid rgba(107,114,128,0.3)}
|
||
.badge-isp{background:rgba(37,99,235,0.15);color:#3b82f6;border:1px solid rgba(37,99,235,0.3)}
|
||
.action-btn{width:36px;height:36px;border-radius:10px;border:none;cursor:pointer;display:flex;align-items:center;justify-content:center;background:rgba(239,68,68,0.1);color:#f87171}
|
||
.action-btn:hover{background:#ef4444;color:white}
|
||
.pagination{display:flex;justify-content:center;gap:8px;margin-top:24px}
|
||
.page-btn{padding:10px 16px;background:var(--bg-card);border:1px solid var(--border);border-radius:10px;color:var(--text-primary);text-decoration:none;font-weight:500}
|
||
.page-btn:hover{border-color:var(--primary)}
|
||
.page-btn.active{background:var(--primary);border-color:var(--primary);color:white}
|
||
.empty-state{text-align:center;padding:80px 40px}
|
||
.empty-icon{font-size:64px;margin-bottom:20px;opacity:0.5}
|
||
.empty-title{font-size:20px;font-weight:600;margin-bottom:8px}
|
||
.empty-text{color:var(--text-secondary);margin-bottom:20px}
|
||
</style>
|
||
|
||
<link rel="stylesheet" href="wevads-global.css?v1770777318">
|
||
</head>
|
||
<body>
|
||
<header class="header">
|
||
<div class="logo"><div class="logo-icon">📊</div><div class="logo-text">WEVAL SEND CRM</div></div>
|
||
<nav class="header-nav">
|
||
<a href="world-dashboard.php" class="nav-btn">🌍 Dashboard</a>
|
||
<a href="data-import.php" class="nav-btn">📥 Import</a>
|
||
<a href="index.php" class="nav-btn">🏠 WEVAL</a>
|
||
</nav>
|
||
</header>
|
||
<div class="layout">
|
||
<aside class="sidebar">
|
||
<div class="sidebar-section">
|
||
<div class="section-title">📊 Segments</div>
|
||
<div class="segment-list">
|
||
<a href="crm.php" class="segment-card all <?php echo $filter==='all'?'active':''; ?>">
|
||
<div class="segment-info"><div class="segment-icon">📋</div><span class="segment-name">All Leads</span></div>
|
||
<span class="segment-count"><?php echo number_format($stats['total']); ?></span>
|
||
</a>
|
||
<a href="crm.php?filter=hot" class="segment-card hot <?php echo $filter==='hot'?'active':''; ?>">
|
||
<div class="segment-info"><div class="segment-icon">🔥</div><span class="segment-name">Hot</span></div>
|
||
<span class="segment-count"><?php echo number_format($stats['hot']); ?></span>
|
||
</a>
|
||
<a href="crm.php?filter=warm" class="segment-card warm <?php echo $filter==='warm'?'active':''; ?>">
|
||
<div class="segment-info"><div class="segment-icon">🌡️</div><span class="segment-name">Warm</span></div>
|
||
<span class="segment-count"><?php echo number_format($stats['warm']); ?></span>
|
||
</a>
|
||
<a href="crm.php?filter=cold" class="segment-card cold <?php echo $filter==='cold'?'active':''; ?>">
|
||
<div class="segment-info"><div class="segment-icon">❄️</div><span class="segment-name">Cold</span></div>
|
||
<span class="segment-count"><?php echo number_format($stats['cold']); ?></span>
|
||
</a>
|
||
<a href="crm.php?filter=openers" class="segment-card openers <?php echo $filter==='openers'?'active':''; ?>">
|
||
<div class="segment-info"><div class="segment-icon">📬</div><span class="segment-name">Openers</span></div>
|
||
<span class="segment-count"><?php echo number_format($stats['openers']); ?></span>
|
||
</a>
|
||
<a href="crm.php?filter=clickers" class="segment-card clickers <?php echo $filter==='clickers'?'active':''; ?>">
|
||
<div class="segment-info"><div class="segment-icon">🖱️</div><span class="segment-name">Clickers</span></div>
|
||
<span class="segment-count"><?php echo number_format($stats['clickers']); ?></span>
|
||
</a>
|
||
</div>
|
||
</div>
|
||
<div class="sidebar-section">
|
||
<div class="section-title">📧 By ISP</div>
|
||
<div class="isp-list">
|
||
<?php $maxIsp = !empty($ispStats) ? max(array_column($ispStats, 'cnt')) : 1;
|
||
foreach ($ispStats as $i): $pct = ($i['cnt'] / $maxIsp) * 100; ?>
|
||
<div class="isp-item">
|
||
<span class="isp-name"><?php echo htmlspecialchars($i['isp']); ?></span>
|
||
<div class="isp-bar"><div class="isp-fill" style="width:<?php echo $pct; ?>%"></div></div>
|
||
<span class="isp-count"><?php echo number_format($i['cnt']); ?></span>
|
||
</div>
|
||
<?php endforeach; ?>
|
||
</div>
|
||
</div>
|
||
</aside>
|
||
<main class="main">
|
||
<div class="stats-bar">
|
||
<div class="stat-card"><div class="stat-value"><?php echo number_format($stats['total']); ?></div><div class="stat-label">Total Leads</div><div class="stat-icon">📊</div></div>
|
||
<div class="stat-card"><div class="stat-value"><?php echo number_format($stats['hot'] + $stats['warm']); ?></div><div class="stat-label">Hot + Warm</div><div class="stat-icon">🔥</div></div>
|
||
<div class="stat-card"><div class="stat-value"><?php echo number_format($stats['openers']); ?></div><div class="stat-label">Openers</div><div class="stat-icon">📬</div></div>
|
||
<div class="stat-card"><div class="stat-value"><?php echo $stats['total'] > 0 ? round(($stats['clickers'] / $stats['total']) * 100, 1) : 0; ?>%</div><div class="stat-label">Click Rate</div><div class="stat-icon">📈</div></div>
|
||
</div>
|
||
<div class="toolbar">
|
||
<form method="GET" class="search-box">
|
||
<input type="hidden" name="filter" value="<?php echo htmlspecialchars($filter); ?>">
|
||
<input type="text" name="search" value="<?php echo htmlspecialchars($search); ?>" placeholder="🔍 Search email..." class="search-input">
|
||
<button type="submit" class="search-btn">Search</button>
|
||
</form>
|
||
<form method="POST" style="display:inline"><input type="hidden" name="action" value="export"><button type="submit" class="btn btn-secondary">📤 Export</button></form>
|
||
<a href="data-import.php" class="btn btn-primary">➕ Import</a>
|
||
</div>
|
||
<div class="table-container">
|
||
<div class="table-header"><span class="table-title">Showing <strong><?php echo count($leads); ?></strong> of <strong><?php echo number_format($total); ?></strong> leads</span></div>
|
||
<?php if (empty($leads)): ?>
|
||
<div class="empty-state"><div class="empty-icon">📭</div><h3 class="empty-title">No leads found</h3><p class="empty-text">Import data to get started</p><a href="data-import.php" class="btn btn-primary">📥 Import</a></div>
|
||
<?php else: ?>
|
||
<table>
|
||
<thead><tr><th>Email</th><th>Name</th><th>Country</th><th>ISP</th><th>Segment</th><th>Opens</th><th>Clicks</th><th>Actions</th></tr></thead>
|
||
<tbody>
|
||
<?php foreach ($leads as $lead): ?>
|
||
<tr>
|
||
<td class="email-cell"><?php echo htmlspecialchars($lead['email']); ?></td>
|
||
<td><?php echo htmlspecialchars(trim(($lead['first_name'] ?? '').' '.($lead['last_name'] ?? ''))) ?: '-'; ?></td>
|
||
<td><?php echo $lead['country'] ? '<span class="badge badge-isp">'.htmlspecialchars($lead['country']).'</span>' : '-'; ?></td>
|
||
<td><?php echo $lead['isp'] ? '<span class="badge badge-isp">'.htmlspecialchars($lead['isp']).'</span>' : '-'; ?></td>
|
||
<td><?php $seg=$lead['segment']??'cold';$cls=['hot'=>'badge-hot','warm'=>'badge-warm'][$seg]??'badge-cold';$ico=['hot'=>'🔥','warm'=>'🌡️'][$seg]??'❄️'; ?><span class="badge <?php echo $cls; ?>"><?php echo $ico.' '.ucfirst($seg); ?></span></td>
|
||
<td><?php echo intval($lead['opens_count'] ?? 0); ?></td>
|
||
<td><?php echo intval($lead['clicks_count'] ?? 0); ?></td>
|
||
<td><form method="POST" style="display:inline" onsubmit="return confirm('Delete?')"><input type="hidden" name="action" value="delete"><input type="hidden" name="id" value="<?php echo $lead['id']; ?>"><button type="submit" class="action-btn">🗑️</button></form></td>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
</tbody>
|
||
</table>
|
||
<?php endif; ?>
|
||
</div>
|
||
<?php if ($totalPages > 1): ?>
|
||
<div class="pagination">
|
||
<?php if ($page > 1): ?><a href="?page=<?php echo $page-1; ?>&filter=<?php echo $filter; ?>&search=<?php echo urlencode($search); ?>" class="page-btn">« Prev</a><?php endif; ?>
|
||
<?php for ($i = max(1, $page-2); $i <= min($totalPages, $page+2); $i++): ?>
|
||
<a href="?page=<?php echo $i; ?>&filter=<?php echo $filter; ?>&search=<?php echo urlencode($search); ?>" class="page-btn <?php echo $i===$page?'active':''; ?>"><?php echo $i; ?></a>
|
||
<?php endfor; ?>
|
||
<?php if ($page < $totalPages): ?><a href="?page=<?php echo $page+1; ?>&filter=<?php echo $filter; ?>&search=<?php echo urlencode($search); ?>" class="page-btn">Next »</a><?php endif; ?>
|
||
</div>
|
||
<?php endif; ?>
|
||
</main>
|
||
</div>
|
||
|
||
<script src="arsenal-common.js?v1770778169"></script>
|
||
<div style="position:fixed;bottom:10px;left:50%;transform:translateX(-50%);z-index:9999;display:flex;gap:8px;background:#0c1220;padding:8px 16px;border-radius:8px;border:1px solid #1e293b">
|
||
<a href="/data-lists-new.html" style="color:#22d3ee;text-decoration:none;font-size:12px;padding:4px 12px;background:#111827;border-radius:4px">📋 Data Lists</a>
|
||
<a href="/data-manager.html" style="color:#34d399;text-decoration:none;font-size:12px;padding:4px 12px;background:#111827;border-radius:4px">🗄 Data Manager</a>
|
||
<a href="/deliverads/data-quality.php" style="color:#fbbf24;text-decoration:none;font-size:12px;padding:4px 12px;background:#111827;border-radius:4px">📊 Data Quality</a>
|
||
<a href="/emailing-pipeline.html" style="color:#a78bfa;text-decoration:none;font-size:12px;padding:4px 12px;background:#111827;border-radius:4px">📧 Pipeline</a>
|
||
</div>
|
||
</body>
|
||
</html>
|