pdo, $this->config); $range = $_GET['range'] ?? ($_SESSION['dash_range'] ?? 'this_month'); $_SESSION['dash_range'] = $range; [$fromSql, $toSql] = $this->dateRangeSql($range); if (has_role($u, ['MARKETING'])) { $data = $this->marketingDashboard($fromSql, $toSql); $view = 'dashboard/marketing.php'; } elseif (has_role($u, ['CC_SUP'])) { $data = $this->ccSupervisorDashboard((int)$u['id'], $fromSql, $toSql); $view = 'dashboard/cc_sup.php'; } elseif (has_role($u, ['CC_AGENT'])) { $data = $this->ccAgentDashboard((int)$u['id'], $fromSql, $toSql); $view = 'dashboard/cc_agent.php'; } elseif (has_role($u, ['BR_MGR'])) { $data = $this->branchManagerDashboard((int)($u['branch_id'] ?? 0), $fromSql, $toSql); $view = 'dashboard/br_mgr.php'; } elseif (has_role($u, ['BR_AGENT'])) { $data = $this->branchAgentDashboard((int)$u['id'], $fromSql, $toSql); $view = 'dashboard/br_agent.php'; } elseif (has_role($u, ['SALES_MGR','GM_SALES','ADMIN'])) { $data = $this->salesManagerDashboard($fromSql, $toSql); $view = 'dashboard/sales_mgr.php'; } else { $data = ['kpis'=>[]]; $view = 'dashboard/basic.php'; } $this->render($view, [ 'u' => $u, 'range' => $range, 'data' => $data ]); } // ---------------- Date range helper ---------------- private function dateRangeSql(string $range): array { return match ($range) { 'today' => ["CURDATE()", "DATE_ADD(CURDATE(), INTERVAL 1 DAY)"], 'yesterday' => ["DATE_SUB(CURDATE(), INTERVAL 1 DAY)", "CURDATE()"], 'this_week' => ["DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)", "DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)"], 'prev_month' => ["DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m-01')", "DATE_FORMAT(CURDATE(),'%Y-%m-01')"], 'this_month' => ["DATE_FORMAT(CURDATE(),'%Y-%m-01')", "DATE_ADD(DATE_FORMAT(CURDATE(),'%Y-%m-01'), INTERVAL 1 MONTH)"], default => ["DATE_SUB(NOW(), INTERVAL 30 DAY)", "NOW()"], }; } // ---------------- Common helpers ---------------- private function fetchOne(string $sql, array $params = []): array { $st = $this->pdo->prepare($sql); $st->execute($params); $row = $st->fetch(PDO::FETCH_ASSOC); return $row ?: []; } private function fetchAll(string $sql, array $params = []): array { $st = $this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll(PDO::FETCH_ASSOC) ?: []; } private function safeRate(int $num, int $den): float { if ($den <= 0) return 0.0; return round(($num / $den) * 100, 2); } private function trendLeads(string $from, string $to, string $extraWhere = "1=1", array $params = []): array { return $this->fetchAll(" SELECT DATE(created_at) d, COUNT(*) v FROM leads WHERE created_at >= $from AND created_at < $to AND $extraWhere GROUP BY d ORDER BY d ASC LIMIT 60 ", $params); } private function trendOpps(string $from, string $to, string $extraWhere = "1=1", array $params = []): array { return $this->fetchAll(" SELECT DATE(created_at) d, COUNT(*) v FROM opportunities WHERE created_at >= $from AND created_at < $to AND $extraWhere GROUP BY d ORDER BY d ASC LIMIT 60 ", $params); } // ======================= MARKETING ======================= private function marketingDashboard(string $from, string $to): array { $k = $this->fetchOne(" SELECT (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to) AS leads_total, (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='qualified') AS leads_qualified, (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='disqualified') AS leads_disqualified, (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to) AS opp_total, (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='won') AS opp_won, (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='lost') AS opp_lost "); $k['qualification_rate'] = $this->safeRate((int)$k['leads_qualified'], (int)$k['leads_total']); $k['win_rate'] = $this->safeRate((int)$k['opp_won'], max(1, ((int)$k['opp_won'] + (int)$k['opp_lost']))); $funnel = [ ['label'=>'Leads', 'val'=>(int)$k['leads_total']], ['label'=>'Qualified', 'val'=>(int)$k['leads_qualified']], ['label'=>'Opportunities', 'val'=>(int)$k['opp_total']], ['label'=>'Won', 'val'=>(int)$k['opp_won']], ['label'=>'Lost', 'val'=>(int)$k['opp_lost']], ['label'=>'Disqualified', 'val'=>(int)$k['leads_disqualified']], ]; return [ 'kpis' => $k, // Funnel data (used to draw a funnel-like chart) 'chart_funnel' => [ ['label'=>'Leads', 'val'=>(int)$k['leads_total']], ['label'=>'Qualified', 'val'=>(int)$k['leads_qualified']], ['label'=>'Opportunities', 'val'=>(int)$k['opp_total']], ['label'=>'Won', 'val'=>(int)$k['opp_won']], ['label'=>'Lost', 'val'=>(int)$k['opp_lost']], ['label'=>'Disqualified', 'val'=>(int)$k['leads_disqualified']], ], // ✅ New charts requested 'chart_source' => $this->fetchAll(" SELECT COALESCE(source,'(blank)') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC LIMIT 12 "), 'chart_salary_range' => $this->fetchAll(" SELECT COALESCE(salary_range,'(blank)') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_pref_time' => $this->fetchAll(" SELECT COALESCE(pref_time,'(blank)') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_purchase_time' => $this->fetchAll(" SELECT COALESCE(purchase_time,'(blank)') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_obligation' => $this->fetchAll(" SELECT COALESCE(obligation,'unknown') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_vehicle' => $this->fetchAll(" SELECT COALESCE(vehicle_of_interest,'(blank)') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), // ✅ Table 1: Qualification rate by Source + Won leads by source // won leads by source = opportunities that became won, grouped by leads.source 'table_source_perf' => $this->fetchAll(" SELECT COALESCE(l.source,'(blank)') source, COUNT(*) leads, SUM(CASE WHEN l.qualification='qualified' THEN 1 ELSE 0 END) qualified, ROUND((SUM(CASE WHEN l.qualification='qualified' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0))*100,2) AS q_rate, SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won_opps FROM leads l LEFT JOIN opportunities o ON o.id = l.converted_opportunity_id WHERE l.created_at >= $from AND l.created_at < $to GROUP BY source ORDER BY leads DESC LIMIT 15 "), // ✅ Table 2: Qualification rate by Vehicle of Interest 'table_vehicle_perf' => $this->fetchAll(" SELECT COALESCE(vehicle_of_interest,'(blank)') vehicle, COUNT(*) leads, SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) qualified, ROUND((SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0))*100,2) AS q_rate FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY vehicle ORDER BY leads DESC LIMIT 15 "), ]; } // ======================= CC SUPERVISOR ======================= private function ccSupervisorDashboard(int $supId, string $from, string $to): array { $agentRows = $this->fetchAll("SELECT id, name FROM users WHERE supervisor_user_id=?", [$supId]); $ids = array_map(fn($r)=>(int)$r['id'], $agentRows); if (!$ids) $ids = [-1]; $in = implode(',', array_fill(0, count($ids), '?')); $k = $this->fetchOne(" SELECT COUNT(*) leads_total, SUM(CASE WHEN first_success_contact_at IS NOT NULL THEN 1 ELSE 0 END) contacted_success, SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) qualified, SUM(CASE WHEN qualification='disqualified' THEN 1 ELSE 0 END) disqualified FROM leads WHERE assigned_to_user_id IN ($in) AND created_at >= $from AND created_at < $to ", $ids); $k['contact_rate'] = $this->safeRate((int)$k['contacted_success'], (int)$k['leads_total']); $k['qualification_rate'] = $this->safeRate((int)$k['qualified'], (int)$k['leads_total']); $byAgent = $this->fetchAll(" SELECT u.name label, COUNT(l.id) leads, SUM(CASE WHEN l.first_success_contact_at IS NOT NULL THEN 1 ELSE 0 END) contacted, SUM(CASE WHEN l.qualification='qualified' THEN 1 ELSE 0 END) qualified, SUM(CASE WHEN l.qualification='disqualified' THEN 1 ELSE 0 END) disqualified FROM users u LEFT JOIN leads l ON l.assigned_to_user_id=u.id AND l.created_at >= $from AND l.created_at < $to WHERE u.id IN ($in) GROUP BY u.id ORDER BY leads DESC ", $ids); return [ 'kpis'=>$k, // 6 charts 'chart_by_agent' => $byAgent, 'chart_contact_rate' => array_map(function($r){ $leads=(int)$r['leads']; $contacted=(int)$r['contacted']; return ['label'=>$r['label'], 'val'=> $leads>0 ? round(($contacted/$leads)*100,2) : 0]; }, $byAgent), 'chart_q_rate' => array_map(function($r){ $leads=(int)$r['leads']; $q=(int)$r['qualified']; return ['label'=>$r['label'], 'val'=> $leads>0 ? round(($q/$leads)*100,2) : 0]; }, $byAgent), 'chart_status' => $this->fetchAll(" SELECT COALESCE(status,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id IN ($in) AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", $ids), 'chart_disq' => $this->fetchAll(" SELECT COALESCE(disqualify_reason,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id IN ($in) AND created_at >= $from AND created_at < $to AND qualification='disqualified' GROUP BY label ORDER BY val DESC LIMIT 12 ", $ids), 'chart_vehicle' => $this->fetchAll(" SELECT COALESCE(vehicle_of_interest,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id IN ($in) AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", $ids), // 2 tables 'table_agents' => $byAgent, 'table_recent' => $this->fetchAll(" SELECT l.id, l.status, l.vehicle_of_interest, l.lead_city, l.created_at, u.name agent_name FROM leads l LEFT JOIN users u ON u.id=l.assigned_to_user_id WHERE l.assigned_to_user_id IN ($in) ORDER BY l.id DESC LIMIT 12 ", $ids), ]; } // ======================= CC AGENT ======================= private function ccAgentDashboard(int $uid, string $from, string $to): array { $k = $this->fetchOne(" SELECT COUNT(*) leads_total, SUM(CASE WHEN first_success_contact_at IS NOT NULL THEN 1 ELSE 0 END) contacted_success, SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) qualified, SUM(CASE WHEN qualification='disqualified' THEN 1 ELSE 0 END) disqualified FROM leads WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to ", [$uid]); $k['contact_rate'] = $this->safeRate((int)$k['contacted_success'], (int)$k['leads_total']); $k['qualification_rate'] = $this->safeRate((int)$k['qualified'], (int)$k['leads_total']); $opp = $this->fetchOne(" SELECT COUNT(*) opp_total FROM opportunities o JOIN leads l ON l.id=o.lead_id WHERE l.assigned_to_user_id=? AND o.created_at >= $from AND o.created_at < $to ", [$uid]); $k = array_merge($k, $opp); $outcomes = [ ['label'=>'Qualified', 'val'=>(int)$k['qualified']], ['label'=>'Disqualified', 'val'=>(int)$k['disqualified']], ['label'=>'Pending', 'val'=> max(0, (int)$k['leads_total'] - ((int)$k['qualified'] + (int)$k['disqualified']))], ]; return [ 'kpis'=>$k, // 6 charts 'chart_status' => $this->fetchAll(" SELECT COALESCE(status,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_outcomes' => $outcomes, 'chart_vehicle' => $this->fetchAll(" SELECT COALESCE(vehicle_of_interest,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_pref_time' => $this->fetchAll(" SELECT COALESCE(pref_time,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_purchase_time' => $this->fetchAll(" SELECT COALESCE(purchase_time,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_city' => $this->fetchAll(" SELECT COALESCE(lead_city,'(blank)') label, COUNT(*) val FROM leads WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC LIMIT 12 ", [$uid]), // 2 tables 'table_recent_leads' => $this->fetchAll(" SELECT id, status, vehicle_of_interest, lead_city, created_at FROM leads WHERE assigned_to_user_id=? ORDER BY id DESC LIMIT 12 ", [$uid]), 'table_recent_opps' => $this->fetchAll(" SELECT o.id, o.status, o.stage, o.created_at FROM opportunities o JOIN leads l ON l.id=o.lead_id WHERE l.assigned_to_user_id=? ORDER BY o.id DESC LIMIT 12 ", [$uid]), ]; } // ======================= BRANCH MANAGER ======================= private function branchManagerDashboard(int $branchId, string $from, string $to): array { if ($branchId <= 0) return ['kpis'=>[]]; $k = $this->fetchOne(" SELECT COUNT(*) opp_total, SUM(CASE WHEN status='open' THEN 1 ELSE 0 END) open_cnt, SUM(CASE WHEN status='on_hold' THEN 1 ELSE 0 END) hold_cnt, SUM(CASE WHEN status='won' THEN 1 ELSE 0 END) won_cnt, SUM(CASE WHEN status='lost' THEN 1 ELSE 0 END) lost_cnt FROM opportunities WHERE branch_id=? AND created_at >= $from AND created_at < $to ", [$branchId]); $k['win_rate'] = $this->safeRate((int)$k['won_cnt'], max(1, ((int)$k['won_cnt']+(int)$k['lost_cnt']))); $byAgent = $this->fetchAll(" SELECT u.name label, COUNT(o.id) opps, SUM(CASE WHEN o.status='open' THEN 1 ELSE 0 END) open_cnt, SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won_cnt, SUM(CASE WHEN o.status='lost' THEN 1 ELSE 0 END) lost_cnt, SUM(CASE WHEN o.status='on_hold' THEN 1 ELSE 0 END) hold_cnt FROM users u JOIN roles r ON r.id=u.role_id AND r.code='BR_AGENT' LEFT JOIN opportunities o ON o.assigned_to_user_id=u.id AND o.created_at >= $from AND o.created_at < $to WHERE u.branch_id=? GROUP BY u.id ORDER BY opps DESC ", [$branchId]); return [ 'kpis'=>$k, // 6 charts 'chart_status' => $this->fetchAll(" SELECT COALESCE(status,'(blank)') label, COUNT(*) val FROM opportunities WHERE branch_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$branchId]), 'chart_stage' => $this->fetchAll(" SELECT COALESCE(stage,'(blank)') label, COUNT(*) val FROM opportunities WHERE branch_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$branchId]), 'chart_stage_status' => $this->fetchAll(" SELECT COALESCE(stage_status,'(blank)') label, COUNT(*) val FROM opportunities WHERE branch_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC LIMIT 12 ", [$branchId]), 'chart_by_agent' => $byAgent, 'chart_won_by_agent' => array_map(fn($r)=>['label'=>$r['label'],'val'=>(int)$r['won_cnt']], $byAgent), 'chart_trend' => $this->trendOpps($from, $to, "branch_id=?", [$branchId]), // 2 tables 'table_agents' => $byAgent, 'table_recent' => $this->fetchAll(" SELECT id, status, stage, stage_status, assigned_to_user_id, created_at FROM opportunities WHERE branch_id=? AND created_at >= $from AND created_at < $to ORDER BY id DESC LIMIT 12 ", [$branchId]), ]; } // ======================= BRANCH AGENT ======================= private function branchAgentDashboard(int $uid, string $from, string $to): array { $k = $this->fetchOne(" SELECT COUNT(*) opp_total, SUM(CASE WHEN status='open' THEN 1 ELSE 0 END) open_cnt, SUM(CASE WHEN status='on_hold' THEN 1 ELSE 0 END) hold_cnt, SUM(CASE WHEN status='won' THEN 1 ELSE 0 END) won_cnt, SUM(CASE WHEN status='lost' THEN 1 ELSE 0 END) lost_cnt FROM opportunities WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to ", [$uid]); $k['win_rate'] = $this->safeRate((int)$k['won_cnt'], max(1, ((int)$k['won_cnt']+(int)$k['lost_cnt']))); return [ 'kpis'=>$k, // 6 charts 'chart_status' => $this->fetchAll(" SELECT COALESCE(status,'(blank)') label, COUNT(*) val FROM opportunities WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_stage' => $this->fetchAll(" SELECT COALESCE(stage,'(blank)') label, COUNT(*) val FROM opportunities WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_stage_status' => $this->fetchAll(" SELECT COALESCE(stage_status,'(blank)') label, COUNT(*) val FROM opportunities WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC LIMIT 12 ", [$uid]), 'chart_trend' => $this->trendOpps($from, $to, "assigned_to_user_id=?", [$uid]), 'chart_vehicle' => $this->fetchAll(" SELECT COALESCE(l.vehicle_of_interest,'(blank)') label, COUNT(*) val FROM opportunities o JOIN leads l ON l.id=o.lead_id WHERE o.assigned_to_user_id=? AND o.created_at >= $from AND o.created_at < $to GROUP BY label ORDER BY val DESC ", [$uid]), 'chart_lost_reasons' => $this->fetchAll(" SELECT COALESCE(stage_status,'(blank)') label, COUNT(*) val FROM opportunities WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to AND status='lost' GROUP BY label ORDER BY val DESC LIMIT 12 ", [$uid]), // 2 tables 'table_recent' => $this->fetchAll(" SELECT id, status, stage, stage_status, created_at FROM opportunities WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to ORDER BY id DESC LIMIT 12 ", [$uid]), 'table_recent_contacts' => $this->fetchAll(" SELECT o.id, c.mobile, c.full_name, o.status, o.created_at FROM opportunities o JOIN contacts c ON c.id=o.contact_id WHERE o.assigned_to_user_id=? AND o.created_at >= $from AND o.created_at < $to ORDER BY o.id DESC LIMIT 12 ", [$uid]), ]; } // ======================= SALES MANAGER / ADMIN ======================= private function salesManagerDashboard(string $from, string $to): array { $k = $this->fetchOne(" SELECT (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to) AS leads_total, (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='qualified') AS leads_qualified, (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to) AS opp_total, (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='won') AS opp_won, (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='lost') AS opp_lost, (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='disqualified') AS leads_disqualified "); $k['qualification_rate'] = $this->safeRate((int)$k['leads_qualified'], (int)$k['leads_total']); $k['win_rate'] = $this->safeRate((int)$k['opp_won'], max(1, ((int)$k['opp_won']+(int)$k['opp_lost']))); return [ 'kpis'=>$k, // 6 charts 'chart_funnel' => [ ['label'=>'Leads','val'=>(int)$k['leads_total']], ['label'=>'Qualified','val'=>(int)$k['leads_qualified']], ['label'=>'Opportunities','val'=>(int)$k['opp_total']], ['label'=>'Won','val'=>(int)$k['opp_won']], ['label'=>'Lost','val'=>(int)$k['opp_lost']], ['label'=>'Disqualified','val'=>(int)$k['leads_disqualified']], ], 'chart_opp_status' => $this->fetchAll(" SELECT COALESCE(status,'(blank)') label, COUNT(*) val FROM opportunities WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_opp_stage' => $this->fetchAll(" SELECT COALESCE(stage,'(blank)') label, COUNT(*) val FROM opportunities WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_branch_won' => $this->fetchAll(" SELECT b.name_en label, SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) val FROM opportunities o JOIN branches b ON b.id=o.branch_id WHERE o.created_at >= $from AND o.created_at < $to GROUP BY b.id ORDER BY val DESC LIMIT 12 "), 'chart_vehicle' => $this->fetchAll(" SELECT COALESCE(l.vehicle_of_interest,'(blank)') label, COUNT(*) val FROM leads l WHERE l.created_at >= $from AND l.created_at < $to GROUP BY label ORDER BY val DESC "), 'chart_source' => $this->fetchAll(" SELECT COALESCE(source,'(blank)') label, COUNT(*) val FROM leads WHERE created_at >= $from AND created_at < $to GROUP BY label ORDER BY val DESC LIMIT 12 "), // 2 tables 'table_branch_leaderboard' => $this->fetchAll(" SELECT b.name_en branch, COUNT(o.id) opps, SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won, SUM(CASE WHEN o.status='lost' THEN 1 ELSE 0 END) lost FROM branches b LEFT JOIN opportunities o ON o.branch_id=b.id AND o.created_at >= $from AND o.created_at < $to GROUP BY b.id ORDER BY won DESC, opps DESC LIMIT 12 "), 'table_agent_leaderboard' => $this->fetchAll(" SELECT COALESCE(u.name,'(unassigned)') agent, COUNT(o.id) opps, SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won, SUM(CASE WHEN o.status='lost' THEN 1 ELSE 0 END) lost FROM opportunities o LEFT JOIN users u ON u.id=o.assigned_to_user_id WHERE o.created_at >= $from AND o.created_at < $to GROUP BY o.assigned_to_user_id ORDER BY won DESC, opps DESC LIMIT 12 "), ]; } }