exec("SET time_zone = '+05:30'"); } catch (Exception $e) { error_log("Projects page DB error: ".$e->getMessage()); } // ===== Snapshot KPIs ===== $kpi = [ 'total' => 0, 'live' => 0, 'closed' => 0, 'on_hold' => 0, 'created' => 0, 'targeted' => 0, 'sample_target' => 0, 'completes_total' => 0, 'completes_valid' => 0, 'avg_eloi' => 0, 'avg_actual_loi_min' => 0, 'revenue_inr' => 0, 'active_clients' => 0, 'urls_sent_total' => 0, 'urls_screenout_total' => 0, ]; $forexRates = ['INR' => 1.0, 'USD' => 86.50, 'EUR' => 93.00]; if ($pdo) { // Live forex (best-effort) try { $ctx = stream_context_create(['http' => ['timeout' => 3]]); $rateJson = @file_get_contents('https://api.exchangerate-api.com/v4/latest/USD', false, $ctx); if ($rateJson) { $rateData = json_decode($rateJson, true); if (!empty($rateData['rates']['INR'])) { $usdToInr = (float)$rateData['rates']['INR']; $forexRates['USD'] = $usdToInr; if (!empty($rateData['rates']['EUR'])) { $forexRates['EUR'] = $usdToInr / (float)$rateData['rates']['EUR']; } } } } catch (Exception $e) { /* fallback rates */ } try { $stmt = $pdo->query("SELECT COUNT(*) as total, SUM(CASE WHEN status='Live' THEN 1 ELSE 0 END) as live_cnt, SUM(CASE WHEN status='Closed' THEN 1 ELSE 0 END) as closed_cnt, SUM(CASE WHEN status='On hold' THEN 1 ELSE 0 END) as hold_cnt, SUM(CASE WHEN status='Created' THEN 1 ELSE 0 END) as created_cnt, SUM(CASE WHEN status='Targeted' THEN 1 ELSE 0 END) as targeted_cnt, COALESCE(SUM(sample_size),0) as sample_target, AVG(eloi) as avg_eloi, COUNT(DISTINCT client_id) as active_clients FROM projects"); $row = $stmt->fetch(); if ($row) { $kpi['total'] = (int)$row['total']; $kpi['live'] = (int)$row['live_cnt']; $kpi['closed'] = (int)$row['closed_cnt']; $kpi['on_hold'] = (int)$row['hold_cnt']; $kpi['created'] = (int)$row['created_cnt']; $kpi['targeted'] = (int)$row['targeted_cnt']; $kpi['sample_target'] = (int)$row['sample_target']; $kpi['avg_eloi'] = round((float)($row['avg_eloi'] ?? 0), 1); $kpi['active_clients'] = (int)$row['active_clients']; } } catch (Exception $e) { error_log("Projects KPI agg error: ".$e->getMessage()); } try { $stmt = $pdo->query("SELECT SUM(CASE WHEN status='complete' THEN 1 ELSE 0 END) as completes_total, SUM(CASE WHEN status='complete' AND quality_flag='valid' THEN 1 ELSE 0 END) as completes_valid, SUM(CASE WHEN status='sent' THEN 1 ELSE 0 END) as sent_cnt, SUM(CASE WHEN status IN ('earlyscreenout','latescreenout') THEN 1 ELSE 0 END) as so_cnt, AVG(CASE WHEN status='complete' AND quality_flag='valid' AND actual_loi_seconds IS NOT NULL THEN actual_loi_seconds END) as avg_loi_sec FROM survey_urls"); $row = $stmt->fetch(); if ($row) { $kpi['completes_total'] = (int)$row['completes_total']; $kpi['completes_valid'] = (int)$row['completes_valid']; $kpi['urls_sent_total'] = (int)$row['sent_cnt']; $kpi['urls_screenout_total'] = (int)$row['so_cnt']; $kpi['avg_actual_loi_min'] = round((float)($row['avg_loi_sec'] ?? 0) / 60, 1); } } catch (Exception $e) { /* */ } try { $stmt = $pdo->query("SELECT currency, SUM(total_amount) as amt FROM invoices GROUP BY currency"); foreach ($stmt->fetchAll() as $r) { $rate = $forexRates[$r['currency'] ?? 'INR'] ?? 1.0; $kpi['revenue_inr'] += (float)$r['amt'] * $rate; } } catch (Exception $e) { /* */ } } // ===== Build WHERE for filtered list ===== $where = ['1=1']; $params = []; if ($f_status !== '') { $where[] = "p.status = ?"; $params[] = $f_status; } if ($f_client > 0) { $where[] = "p.client_id = ?"; $params[] = $f_client; } if ($f_search !== '') { $where[] = "(p.project_id LIKE ? OR p.project_name LIKE ? OR p.client_reference LIKE ?)"; $params[] = '%'.$f_search.'%'; $params[] = '%'.$f_search.'%'; $params[] = '%'.$f_search.'%'; } switch ($f_sort) { case 'newest': $orderBy = 'p.created_at DESC'; break; case 'oldest': $orderBy = 'p.created_at ASC'; break; case 'sample': $orderBy = 'p.sample_size DESC'; break; case 'completes': $orderBy = 'urls_valid DESC'; break; case 'name': $orderBy = 'p.project_name ASC'; break; case 'client': $orderBy = 'c.company_name ASC, p.created_at DESC'; break; default: $orderBy = 'p.updated_at DESC'; // 'recent' } // ===== Fetch project list ===== $projects = []; if ($pdo) { try { $sql = " SELECT p.*, c.company_name as client_name, c.country as client_country, c.currency as client_currency, (SELECT COUNT(*) FROM project_selections WHERE project_id = p.id) as selections_count, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id) as urls_total, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id AND status='available') as urls_available, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id AND status='sent') as urls_sent, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id AND status='complete') as urls_complete, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id AND status='complete' AND quality_flag='valid') as urls_valid, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id AND status IN ('earlyscreenout','latescreenout')) as urls_screenout, (SELECT COUNT(*) FROM survey_urls WHERE project_id = p.project_id AND status='quotafull') as urls_quotafull, (SELECT AVG(actual_loi_seconds) FROM survey_urls WHERE project_id = p.project_id AND status='complete' AND quality_flag='valid' AND actual_loi_seconds IS NOT NULL) as avg_loi_seconds, (SELECT SUM(total_amount) FROM invoices WHERE project_id = p.id) as invoice_total, (SELECT currency FROM invoices WHERE project_id = p.id LIMIT 1) as invoice_currency, (SELECT status FROM invoices WHERE project_id = p.id LIMIT 1) as invoice_status FROM projects p LEFT JOIN clients c ON p.client_id = c.id WHERE ".implode(' AND ', $where)." ORDER BY $orderBy "; $stmt = $pdo->prepare($sql); $stmt->execute($params); $projects = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (Exception $e) { error_log("Projects fetch error: ".$e->getMessage()); } } // Client dropdown $clients_dd = []; if ($pdo) { try { $stmt = $pdo->query("SELECT id, company_name FROM clients ORDER BY company_name ASC"); $clients_dd = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (Exception $e) { /* */ } } // Helpers if (!function_exists('fmtIST')) { function fmtIST($dt, $fmt = 'd M Y, h:i A') { if (!$dt || $dt === '0000-00-00 00:00:00') return '—'; try { $date = new DateTime($dt, new DateTimeZone('Asia/Kolkata')); return $date->format($fmt); } catch (Exception $e) { return date($fmt, strtotime($dt)); } } } if (!function_exists('fmtINR')) { function fmtINR($v) { return '₹' . number_format((float)$v, 2); } } if (!function_exists('fmtNum')) { function fmtNum($v) { return number_format((int)$v); } } include 'includes/header.php'; ?>

Projects

All projects across all clients · live + closed + draft

clients · projects · USD: ₹ · EUR: ₹
Total Projects
across client
Live
in fieldwork now
Sample Target
total committed completes
Valid Completes
of total
Avg ELOI
min
estimated
Avg Actual LOI
min
from valid completes
URLs Sent
screen-outs
Total Revenue
all invoices · INR equiv
📝 Created
🎯 Targeted
🔴 Live
⏸️ On Hold
✅ Closed
Clear Showing of projects
📋
No projects found
Try adjusting your filters or search query.
0 ? min(round($valid / $sampleTarget * 100, 1), 999.9) : 0; $progColor = $completePct >= 100 ? '#059669' : ($completePct >= 70 ? '#10b981' : ($completePct >= 30 ? '#d97706' : '#94a3b8')); $stClass = ['Live'=>'green','Closed'=>'gray','On hold'=>'amber','Created'=>'blue','Targeted'=>'purple'][$p['status']] ?? 'gray'; $actualLoiMin = $p['avg_loi_seconds'] ? round($p['avg_loi_seconds']/60, 1) : null; $invSym = '₹'; if (($p['invoice_currency'] ?? '') === 'USD') $invSym = '$'; elseif (($p['invoice_currency'] ?? '') === 'EUR') $invSym = '€'; $invStatusClass = ['paid'=>'green','due'=>'amber','overdue'=>'red','critical'=>'red','banned'=>'red','invoiced'=>'blue'][$p['invoice_status'] ?? ''] ?? 'gray'; ?>
Project Client Industry Country Status ELOI Actual LOI Sample Progress Sels URLs Funnel Invoice Created Made Live Updated Action
Ref:
min min —
/
%
Sent: · Cmp: ( valid)
SO: · QF: · Total:
No invoice
—'; ?>
Closed:
View →
Client