query(" SELECT TRIM(code) AS code, name FROM countries WHERE COALESCE(enabled, 0) = 1 AND TRIM(code) <> '' ORDER BY name ")->fetchAll(); $countriesHome = $pdo->query(" SELECT TRIM(code) AS code, name, COALESCE(enabled, 0) AS enabled FROM countries WHERE COALESCE(enabled, 0) = 1 AND COALESCE(show_on_home, 1) = 1 AND TRIM(code) <> '' ORDER BY name ")->fetchAll(); // aggregates per country $agg = []; foreach ($countriesEnabled as $c) { $code = $c['code']; $st = $pdo->prepare("SELECT COUNT(*) AS total_leads, SUM(confirmed_at IS NOT NULL) AS confirmed_cnt, SUM(delivered_at IS NOT NULL OR current_status='delivered') AS delivered_cnt, SUM(returned_at IS NOT NULL OR current_status='returned') AS returned_cnt, SUM(rejected_at IS NOT NULL OR current_status='rejected' OR ever_rejected=1) AS rejected_cnt, SUM(current_status='in_delivery') AS in_delivery_cnt, SUM(current_status='incident') AS incident_cnt, SUM(COALESCE(qty_cards,0)) AS cards_qty_all, SUM(CASE WHEN confirmed_at IS NOT NULL THEN COALESCE(qty_cards,0) ELSE 0 END) AS cards_qty_confirmed FROM leads WHERE country=?"); $st->execute([$code]); $row = $st->fetch() ?: []; $st2 = $pdo->prepare("SELECT SUM(COALESCE(last_stock_qty,0)) AS stock_qty FROM cards WHERE active=1 AND country=?"); $st2->execute([$code]); $stock = $st2->fetch(); $row['stock_qty'] = (int)($stock['stock_qty'] ?? 0); $agg[$code] = $row; } // latest status changes (optional filters) $latestWhere = []; $latestArgs = []; if ($lscCountry !== '') { $latestWhere[] = 'l.country = ?'; $latestArgs[] = strtoupper($lscCountry); } if ($lscStatus !== '') { $latestWhere[] = '(h.status_norm = ? OR l.current_status = ?)'; $latestArgs[] = $lscStatus; $latestArgs[] = $lscStatus; } if ($q !== '') { $like = '%' . $q . '%'; $latestWhere[] = '(h.lead_number LIKE ? OR l.customer_name LIKE ? OR l.email LIKE ? OR l.phone LIKE ? OR l.tracking_number LIKE ?)'; array_push($latestArgs, $like, $like, $like, $like, $like); } $latestSql = "SELECT h.lead_number,h.status_norm,h.status_raw,h.status_at,l.country,l.customer_name,l.email,l.phone,l.current_status,l.current_status_at,l.tracking_number,l.order_id_remote,l.courier_code,l.qty_cards,l.details_json FROM lead_history h JOIN leads l ON l.lead_number=h.lead_number"; if (count($latestWhere) > 0) { $latestSql .= ' WHERE ' . implode(' AND ', $latestWhere); } $latestSql .= " ORDER BY COALESCE(h.status_at,'1970-01-01') DESC, h.id DESC LIMIT 50"; $stLatest = $pdo->prepare($latestSql); $stLatest->execute($latestArgs); $latest = $stLatest->fetchAll(); // home countries (respect settings toggle) $pinnedCountries = $countriesHome; require_once __DIR__ . '/_inc/layout_top.php'; ?>
0,'confirmed'=>0,'delivered'=>0,'returned'=>0,'rejected'=>0,'in_delivery'=>0,'incident'=>0,'cards'=>0,'stock'=>0 ]; foreach($countriesEnabled as $c){ $code = $c['code']; $a = $agg[$code] ?? []; $overall['total'] += (int)($a['total_leads'] ?? 0); $overall['confirmed'] += (int)($a['confirmed_cnt'] ?? 0); $overall['delivered'] += (int)($a['delivered_cnt'] ?? 0); $overall['returned'] += (int)($a['returned_cnt'] ?? 0); $overall['rejected'] += (int)($a['rejected_cnt'] ?? 0); $overall['in_delivery'] += (int)($a['in_delivery_cnt'] ?? 0); $overall['incident'] += (int)($a['incident_cnt'] ?? 0); $overall['cards'] += (int)($a['cards_qty_confirmed'] ?? 0); $overall['stock'] += (int)($a['stock_qty'] ?? 0); } $confRate = $overall['total']>0 ? round($overall['confirmed']*100/$overall['total'],1) : 0; $delRate = $overall['confirmed']>0 ? round($overall['delivered']*100/$overall['confirmed'],1) : 0; $retRate = $overall['confirmed']>0 ? round($overall['returned']*100/$overall['confirmed'],1) : 0; $rejRate = $overall['confirmed']>0 ? round($overall['rejected']*100/$overall['confirmed'],1) : 0; $inDelRate = $overall['total']>0 ? round($overall['in_delivery']*100/$overall['total'],1) : 0; $incRate = $overall['total']>0 ? round($overall['incident']*100/$overall['total'],1) : 0; ?>
Overview Enabled countries
Window: days
TOTAL LEADS
Across enabled countries
CONFIRMED
% of total
DELIVERED
% of confirmed
RETURNED
% of confirmed
REJECTED
% of confirmed
IN DELIVERY
% of total
INCIDENT
% of total
CARDS / STOCK
/
Confirmed cards vs active stock
Trend Created / Confirmed / Delivered / Returned / Rejected
Created
Confirmed
Delivered
Returned
Rejected
0 ? round($conf*100/$total,1) : 0; $delPct = $total>0 ? ($del*100/$total) : 0; $retPct = $total>0 ? ($ret*100/$total) : 0; $rejPct = $total>0 ? ($rej*100/$total) : 0; ?>
Total leads • Confirmed: (%)
Open Enable
Cards + Status distribution
DELIVERED
0?round($del*100/$total,1):0 ?>%
RETURNED
0?round($ret*100/$total,1):0 ?>%
REJECTED
0?round($rej*100/$total,1):0 ?>%
CONFIRMED
%
CARDS QTY
Confirmed
STOCK
Active
Latest status changes
Reset
LeadCountryCustomerMailPhone StatusWhenTrackingCards