­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ pdo, $this->config); // branches for filter (already used in your datatable filters) $branches = []; if (has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) { $branches = $this->pdo->query("SELECT id, name_en, name_ar, city FROM branches WHERE is_active=1 ORDER BY id")->fetchAll(); } elseif (!empty($u['branch_id'])) { $st = $this->pdo->prepare("SELECT id, name_en, name_ar, city FROM branches WHERE is_active=1 AND id=?"); $st->execute([(int)$u['branch_id']]); $b = $st->fetch(); if ($b) $branches = [$b]; } // branch agents for bulk assignment dropdown $agents = []; if (has_role($u, ['ADMIN','BR_MGR'])) { $branchId = (int)($u['branch_id'] ?? 0); // For Admin: default to NO agents until a branch is selected (safer) // We'll still show dropdown but it will be empty unless BR_MGR (or you add AJAX later) if (has_role($u, ['BR_MGR']) && $branchId > 0) { $st = $this->pdo->prepare(" SELECT u.id, u.name, u.mobile FROM users u JOIN roles r ON r.id=u.role_id WHERE r.code='BR_AGENT' AND u.is_active=1 AND u.branch_id=? ORDER BY u.name "); $st->execute([$branchId]); $agents = $st->fetchAll(); } } $this->render('opps/index.php', ['u'=>$u, 'branches'=>$branches, 'agents'=>$agents]); } public function agents(): void { $u = require_login($this->pdo, $this->config); // Only Admin/Branch Manager can bulk assign if (!has_role($u, ['ADMIN','BR_MGR'])) { http_response_code(403); echo "Forbidden"; exit; } $branchId = (int)($_GET['branch_id'] ?? 0); // BR_MGR can only request their branch if (has_role($u, ['BR_MGR'])) { $branchId = (int)($u['branch_id'] ?? 0); } if ($branchId <= 0) { header('Content-Type: application/json; charset=utf-8'); echo json_encode(['agents' => []], JSON_UNESCAPED_UNICODE); exit; } $st = $this->pdo->prepare(" SELECT u.id, u.name, u.mobile FROM users u JOIN roles r ON r.id=u.role_id WHERE r.code='BR_AGENT' AND u.is_active=1 AND u.branch_id=? ORDER BY u.name "); $st->execute([$branchId]); $agents = $st->fetchAll(PDO::FETCH_ASSOC); header('Content-Type: application/json; charset=utf-8'); echo json_encode(['agents' => $agents], JSON_UNESCAPED_UNICODE); exit; } private function stageMap(): array { return [ 'open_opportunity' => ['Open'], 'test_drive' => ['Booked', 'Completed', 'No Show', 'Rescheduled'], 'quotation_offer' => ['Sent', 'Revised', 'Expired'], 'finance_processing' => ['Submitted', 'Approved', 'Rejected', 'Pending Documents'], 'negotiation' => ['In Negotiation', 'Waiting Approval', 'Offer Improved'], 'booking' => ['Booked', 'Payment Pending', 'Cancelled'], 'delivery_sale' => ['Closed – Won', 'Delivered'], 'closed_lost' => ['Price', 'Competitor', 'Finance Rejected', 'No Stock', 'Delay', 'No Response'], 'on_hold' => ['Follow-up', 'On Hold', 'Future Purchase'], ]; } private function stageLabel(string $key): string { return match ($key) { 'open_opportunity' => 'Open Opportunity', 'test_drive' => 'Test Drive', 'quotation_offer' => 'Quotation / Offer', 'finance_processing' => 'Finance Processing', 'negotiation' => 'Negotiation', 'booking' => 'Booking', 'delivery_sale' => 'Delivery / Sale', 'closed_lost' => 'Closed – Lost', 'on_hold' => 'On Hold / Nurturing', default => $key, }; } private function deriveOverallStatus(string $stage, ?string $stageStatus): string { // Overall opportunity status if ($stage === 'closed_lost') return 'lost'; if ($stage === 'on_hold') return 'on_hold'; if ($stage === 'delivery_sale') return 'won'; // Everything else stays open return 'open'; } public function view(): void { $u = require_login($this->pdo, $this->config); $id = (int)($_GET['id'] ?? 0); $st = $this->pdo->prepare("SELECT o.*, c.country_code, c.mobile, c.full_name, c.city, b.name_en AS branch_name_en, b.name_ar AS branch_name_ar, b.location_url, l.vehicle_of_interest, l.model_year, l.salary, l.salary_range, l.obligation, l.obligation_amount, l.pref_time, l.purchase_time, l.qualified_at, l.qualify_note, qb.name_en AS qualified_branch_name_en, qb.name_ar AS qualified_branch_name_ar, qb.city AS qualified_branch_city, lo.name AS lead_owner_name, qbuser.name AS qualified_by_name FROM opportunities o JOIN contacts c ON c.id=o.contact_id JOIN branches b ON b.id=o.branch_id JOIN leads l ON l.id=o.lead_id LEFT JOIN branches qb ON qb.id = l.qualified_branch_id LEFT JOIN users lo ON lo.id = l.assigned_to_user_id LEFT JOIN users qbuser ON qbuser.id = l.qualified_by_user_id WHERE o.id=?"); $st->execute([$id]); $opp = $st->fetch(); if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; } if (!can_view_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; } // capture first view for assigned branch agent if (has_role($u, ['BR_AGENT']) && (int)$opp['assigned_to_user_id'] === (int)$u['id'] && empty($opp['first_view_at'])) { $st = $this->pdo->prepare("UPDATE opportunities SET first_view_at=?, status=IF(status='assigned','in_progress',status), updated_at=? WHERE id=? AND first_view_at IS NULL"); $st->execute([now(), now(), $id]); $this->log('opportunity', $id, 'VIEW', $u['id'], ['first_view'=>true]); // refresh $st = $this->pdo->prepare("SELECT o.*, -- contact info c.country_code, c.mobile, c.full_name, c.city, -- opportunity branch info b.name_en AS branch_name_en, b.name_ar AS branch_name_ar, b.location_url, -- lead info (same fields you show in lead view) l.source AS lead_source, l.campaign AS lead_campaign, l.vehicle_of_interest, l.model_year, l.salary, l.salary_range, l.obligation, l.obligation_amount, l.pref_time, l.purchase_time, -- qualification info l.qualified_at, l.qualify_note, l.qualified_branch_id, -- qualified branch name (the branch selected by call center) qb.name_en AS qualified_branch_name_en, qb.name_ar AS qualified_branch_name_ar, qb.city AS qualified_branch_city FROM opportunities o JOIN contacts c ON c.id = o.contact_id JOIN branches b ON b.id = o.branch_id JOIN leads l ON l.id = o.lead_id LEFT JOIN branches qb ON qb.id = l.qualified_branch_id WHERE o.id = ? "); $st->execute([$id]); $opp = $st->fetch(); } else { $this->log('opportunity', $id, 'VIEW', $u['id'], []); } // branch agents for assignment (only same branch) $agents = []; if (has_role($u, ['ADMIN','BR_MGR'])) { $st = $this->pdo->prepare("SELECT u.id, u.name, u.mobile FROM users u JOIN roles r ON r.id=u.role_id WHERE r.code='BR_AGENT' AND u.is_active=1 AND u.branch_id=? ORDER BY u.name"); $st->execute([$opp['branch_id']]); $agents = $st->fetchAll(); } $st = $this->pdo->prepare("SELECT a.*, u.name actor_name FROM activity_logs a LEFT JOIN users u ON u.id=a.actor_user_id WHERE a.entity_type='opportunity' AND a.entity_id=? ORDER BY a.id DESC LIMIT 100"); $st->execute([$id]); $logs = $st->fetchAll(); $this->render('opps/view.php', [ 'u'=>$u, 'opp'=>$opp, 'agents'=>$agents, 'logs'=>$logs ]); } public function assign(): void { $u = require_login($this->pdo, $this->config); require_post(); csrf_check(); $oppId = (int)($_POST['opp_id'] ?? 0); $agentId = (int)($_POST['agent_id'] ?? 0); $st = $this->pdo->prepare("SELECT o.*, c.country_code, c.mobile, c.full_name, b.location_url, b.name_en AS branch_name_en, b.name_ar AS branch_name_ar FROM opportunities o JOIN contacts c ON c.id=o.contact_id JOIN branches b ON b.id=o.branch_id WHERE o.id=?"); $st->execute([$oppId]); $opp = $st->fetch(); if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; } if (!can_assign_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; } $st = $this->pdo->prepare("UPDATE opportunities SET assigned_to_user_id=?, assigned_by_user_id=?, assigned_at=?, status='assigned', updated_at=? WHERE id=?"); $st->execute([$agentId ?: null, $u['id'], now(), now(), $oppId]); // get agent info $st = $this->pdo->prepare("SELECT id, name, mobile FROM users WHERE id=?"); $st->execute([$agentId]); $agent = $st->fetch(); // queue SMS to contact (MVP) $branchName = is_rtl() ? ($opp['branch_name_ar'] ?? '') : ($opp['branch_name_en'] ?? ''); $msg = "تم تحويل طلبك إلى {$branchName}. ممثل المبيعات: {$agent['name']} - {$agent['mobile']}. رابط الموقع: " . ($opp['location_url'] ?? ''); if (!is_rtl()) { $msg = "Your opportunity has been assigned to {$branchName}. Sales agent: {$agent['name']} - {$agent['mobile']}. Location: " . ($opp['location_url'] ?? ''); } $to = ($opp['country_code'] ?? '+966') . ($opp['mobile'] ?? ''); $st = $this->pdo->prepare("INSERT INTO sms_outbox(to_number, message, status, provider, related_entity_type, related_entity_id, created_by_user_id, created_at) VALUES(?,?,'queued',NULL,'opportunity',?,?,?)"); $st->execute([$to, $msg, $oppId, $u['id'], now()]); $this->log('opportunity', $oppId, 'ASSIGN', $u['id'], ['assigned_to'=>$agentId]); $this->log('opportunity', $oppId, 'SMS_QUEUED', $u['id'], ['to'=>$to]); flash_set('success', t('sms.queued')); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } public function contact(): void { $u = require_login($this->pdo, $this->config); require_post(); csrf_check(); $oppId = (int)($_POST['opp_id'] ?? 0); $note = trim($_POST['note'] ?? ''); $st = $this->pdo->prepare("SELECT * FROM opportunities WHERE id=?"); $st->execute([$oppId]); $opp = $st->fetch(); if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; } if (!can_view_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; } // only assigned agent should mark contact in MVP (admins/managers can too) if (has_role($u, ['BR_AGENT']) && (int)$opp['assigned_to_user_id'] !== (int)$u['id']) { http_response_code(403); echo "Forbidden"; exit; } $st = $this->pdo->prepare("UPDATE opportunities SET first_contact_at=COALESCE(first_contact_at, ?), status=IF(status IN ('new','assigned','in_progress'),'contacted',status), updated_at=? WHERE id=?"); $st->execute([now(), now(), $oppId]); $this->log('opportunity', $oppId, 'CONTACT_SUCCESS', $u['id'], ['note'=>$note]); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } public function update_stage(): void { $u = require_login($this->pdo, $this->config); require_post(); csrf_check(); $oppId = (int)($_POST['opp_id'] ?? 0); $stage = trim((string)($_POST['stage'] ?? '')); $stageStatus = trim((string)($_POST['stage_status'] ?? '')); $status = trim((string)($_POST['status'] ?? '')); $notes = trim((string)($_POST['notes'] ?? '')); $st = $this->pdo->prepare("SELECT * FROM opportunities WHERE id=?"); $st->execute([$oppId]); $opp = $st->fetch(); if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; } if (!can_view_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; } if (empty($opp['first_contact_at'])) { flash_set('danger', 'Locked until contacted'); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } if ($stage === '' || $stageStatus === '' || $status === '' || $notes === '') { flash_set('danger', 'Stage, Stage Status, Overall Status and Notes are mandatory.'); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } $map = $this->stageMap(); $allowedStages = array_keys($map); if (!in_array($stage, $allowedStages, true)) { flash_set('danger', 'Invalid stage selected.'); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } $allowedOverallStatuses = ['open','won','lost','on_hold']; if (!in_array($status, $allowedOverallStatuses, true)) { flash_set('danger', 'Invalid overall status selected.'); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } $allowedStageStatuses = $map[$stage] ?? ['Open']; if (!in_array($stageStatus, $allowedStageStatuses, true)) { flash_set('danger', 'Invalid stage status selected.'); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } $old = [ 'stage' => $opp['stage'], 'stage_status' => $opp['stage_status'], 'status' => $opp['status'] ]; $st = $this->pdo->prepare(" UPDATE opportunities SET stage=?, stage_status=?, status=?, updated_at=? WHERE id=? "); $st->execute([$stage, $stageStatus, $status, now(), $oppId]); $this->log('opportunity', $oppId, 'UPDATE', $u['id'], [ 'old' => $old, 'new' => [ 'stage' => $stage, 'stage_status' => $stageStatus, 'status' => $status, 'notes' => $notes ] ]); flash_set('success', 'Updated'); redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId)); } public function datatables(): void { $u = require_login($this->pdo, $this->config); $draw = (int)($_GET['draw'] ?? 1); $start = max(0, (int)($_GET['start'] ?? 0)); $length = (int)($_GET['length'] ?? 25); if ($length <= 0 || $length > 200) $length = 25; $sf = $_SESSION['opps_filters'] ?? []; $q = trim($_GET['q'] ?? ($sf['q'] ?? '')); $createdRange = trim($_GET['created_range'] ?? ($sf['created_range'] ?? '')); $assignedRange = trim($_GET['assigned_range'] ?? ($sf['assigned_range'] ?? '')); $status = trim($_GET['status'] ?? ($sf['status'] ?? '')); $stage = trim($_GET['stage'] ?? ($sf['stage'] ?? '')); $allowedStatuses = ['open','won','lost','on_hold']; if ($status !== '' && !in_array($status, $allowedStatuses, true)) { $status = ''; } $allowedStages = [ 'open_opportunity', 'test_drive', 'quotation_offer', 'finance_processing', 'negotiation', 'booking', 'delivery_sale', 'closed_lost', 'on_hold' ]; if ($stage !== '' && !in_array($stage, $allowedStages, true)) { $stage = ''; } $branchId = (int)($_GET['branch_id'] ?? ($sf['branch_id'] ?? 0)); // ordering $orderCol = (int)($_GET['order'][0]['column'] ?? 0); $orderDir = strtolower($_GET['order'][0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC'; // Column whitelist map (must match DataTables column order) $colMap = [ 0 => 'o.id', 1 => 'c.mobile', 2 => 'c.full_name', 3 => 'b.name_en', 4 => 'o.stage', 5 => 'o.stage_status', 6 => 'o.status', 7 => 'o.assigned_at', 8 => 'o.created_at', ]; $orderBy = $colMap[$orderCol] ?? 'o.id'; // scope restrictions $where = "1=1"; $params = []; if (has_role($u, ['BR_MGR'])) { $where .= " AND o.branch_id=?"; $params[] = (int)$u['branch_id']; } elseif (has_role($u, ['BR_AGENT'])) { $where .= " AND o.assigned_to_user_id=?"; $params[] = (int)$u['id']; } elseif (!has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) { $where .= " AND 1=0"; } // search if ($q !== '') { $where .= " AND (c.mobile LIKE ? OR c.full_name LIKE ? OR o.stage_status LIKE ?)"; $like = '%' . $q . '%'; $params = array_merge($params, [$like, $like, $like]); } // filters if ($status !== '') { $where .= " AND o.status = ?"; $params[] = $status; } if ($stage !== '') { $where .= " AND o.stage = ?"; $params[] = $stage; } if ($branchId > 0) { // Only allow branch filter for Admin/GM/Sales Mgr. Branch roles already scoped. if (has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) { $where .= " AND o.branch_id = ?"; $params[] = $branchId; } } // Opportunity Creation Date filter (o.created_at) if ($createdRange !== '') { if ($createdRange === 'today') { $where .= " AND DATE(o.created_at) = CURDATE()"; } elseif ($createdRange === 'yesterday') { $where .= " AND DATE(o.created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)"; } elseif ($createdRange === 'this_week') { $where .= " AND YEARWEEK(o.created_at, 1) = YEARWEEK(CURDATE(), 1)"; } elseif ($createdRange === 'this_month') { $where .= " AND YEAR(o.created_at)=YEAR(CURDATE()) AND MONTH(o.created_at)=MONTH(CURDATE())"; } elseif ($createdRange === 'prev_month') { $where .= " AND YEAR(o.created_at)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND MONTH(o.created_at)=MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))"; } } // Assigned Date filter (o.assigned_at) if ($assignedRange !== '') { $where .= " AND o.assigned_at IS NOT NULL"; if ($assignedRange === 'today') { $where .= " AND DATE(o.assigned_at) = CURDATE()"; } elseif ($assignedRange === 'yesterday') { $where .= " AND DATE(o.assigned_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)"; } elseif ($assignedRange === 'this_week') { $where .= " AND YEARWEEK(o.assigned_at, 1) = YEARWEEK(CURDATE(), 1)"; } elseif ($assignedRange === 'this_month') { $where .= " AND YEAR(o.assigned_at)=YEAR(CURDATE()) AND MONTH(o.assigned_at)=MONTH(CURDATE())"; } elseif ($assignedRange === 'prev_month') { $where .= " AND YEAR(o.assigned_at)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND MONTH(o.assigned_at)=MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))"; } } // totals $whereTotal = "1=1"; $paramsTotal = []; if (has_role($u, ['BR_MGR'])) { $whereTotal .= " AND o.branch_id=?"; $paramsTotal[] = (int)$u['branch_id']; } elseif (has_role($u, ['BR_AGENT'])) { $whereTotal .= " AND o.assigned_to_user_id=?"; $paramsTotal[] = (int)$u['id']; } elseif (!has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) { $whereTotal .= " AND 1=0"; } $st = $this->pdo->prepare(" SELECT COUNT(*) cnt FROM opportunities o JOIN contacts c ON c.id = o.contact_id JOIN branches b ON b.id = o.branch_id WHERE $whereTotal "); $st->execute($paramsTotal); $recordsTotal = (int)$st->fetchColumn(); $st = $this->pdo->prepare(" SELECT COUNT(*) cnt FROM opportunities o JOIN contacts c ON c.id = o.contact_id JOIN branches b ON b.id = o.branch_id WHERE $where "); $st->execute($params); $recordsFiltered = (int)$st->fetchColumn(); // data page $sql = " SELECT o.id, c.mobile, c.full_name, o.branch_id, b.name_en AS branch_name_en, b.name_ar AS branch_name_ar, o.stage, o.stage_status, o.status, o.assigned_at, o.created_at FROM opportunities o JOIN contacts c ON c.id = o.contact_id JOIN branches b ON b.id = o.branch_id WHERE $where ORDER BY $orderBy $orderDir LIMIT $length OFFSET $start "; $st = $this->pdo->prepare($sql); $st->execute($params); $data = $st->fetchAll(PDO::FETCH_ASSOC); header('Content-Type: application/json; charset=utf-8'); echo json_encode([ 'draw' => $draw, 'recordsTotal' => $recordsTotal, 'recordsFiltered' => $recordsFiltered, 'data' => $data, ], JSON_UNESCAPED_UNICODE); exit; } public function save_filters(): void { $u = require_login($this->pdo, $this->config); require_post(); csrf_check(); $_SESSION['opps_filters'] = [ 'q' => trim($_POST['q'] ?? ''), 'created_range' => trim($_POST['created_range'] ?? ''), 'assigned_range'=> trim($_POST['assigned_range'] ?? ''), 'status' => trim($_POST['status'] ?? ''), 'stage' => trim($_POST['stage'] ?? ''), 'branch_id' => (int)($_POST['branch_id'] ?? 0), ]; header('Content-Type: application/json; charset=utf-8'); echo json_encode(['ok' => true], JSON_UNESCAPED_UNICODE); exit; } public function reset_filters(): void { $u = require_login($this->pdo, $this->config); require_post(); csrf_check(); unset($_SESSION['opps_filters']); header('Content-Type: application/json; charset=utf-8'); echo json_encode(['ok' => true], JSON_UNESCAPED_UNICODE); exit; } public function bulk_assign(): void { $u = require_login($this->pdo, $this->config); if (!has_role($u, ['ADMIN','BR_MGR'])) { http_response_code(403); echo "Forbidden"; exit; } require_post(); csrf_check(); $agentId = (int)($_POST['agent_id'] ?? 0); $oppIds = $_POST['opp_ids'] ?? []; if ($agentId <= 0) { flash_set('danger', 'Select an agent'); redirect(base_url($this->config, '/index.php?r=opps/index')); } $ids = []; foreach ($oppIds as $id) { $id = (int)$id; if ($id > 0) $ids[] = $id; } $ids = array_values(array_unique($ids)); if (!$ids) { flash_set('danger', 'Select at least one opportunity'); redirect(base_url($this->config, '/index.php?r=opps/index')); } // BR_MGR can only assign within their branch if (has_role($u, ['BR_MGR']) && empty($u['branch_id'])) { flash_set('danger', 'Branch missing for manager'); redirect(base_url($this->config, '/index.php?r=opps/index')); } $placeholders = implode(',', array_fill(0, count($ids), '?')); $this->pdo->beginTransaction(); try { // Load selected opportunities + contact + branch for SMS $sql = " SELECT o.id, o.branch_id, o.contact_id, c.country_code, c.mobile, b.location_url, b.name_en AS branch_name_en, b.name_ar AS branch_name_ar FROM opportunities o JOIN contacts c ON c.id=o.contact_id JOIN branches b ON b.id=o.branch_id WHERE o.id IN ($placeholders) "; $st = $this->pdo->prepare($sql); $st->execute($ids); $opps = $st->fetchAll(PDO::FETCH_ASSOC); // Enforce branch scope for BR_MGR if (has_role($u, ['BR_MGR'])) { foreach ($opps as $o) { if ((int)$o['branch_id'] !== (int)$u['branch_id']) { throw new RuntimeException('You can only assign opportunities within your branch.'); } } } // Update assignments $params = array_merge([$agentId, (int)$u['id'], now(), now()], $ids); $st = $this->pdo->prepare(" UPDATE opportunities SET assigned_to_user_id=?, assigned_by_user_id=?, assigned_at=?, status='assigned', updated_at=? WHERE id IN ($placeholders) AND status IN ('new','assigned','in_progress','contacted') "); $st->execute($params); $affected = $st->rowCount(); // Get agent info $st = $this->pdo->prepare("SELECT id, name, mobile FROM users WHERE id=?"); $st->execute([$agentId]); $agent = $st->fetch(); if (!$agent) throw new RuntimeException('Agent not found'); // Queue SMS + log for each opp foreach ($opps as $o) { $branchName = is_rtl() ? ($o['branch_name_ar'] ?? '') : ($o['branch_name_en'] ?? ''); $msg = "تم تحويل طلبك إلى {$branchName}. ممثل المبيعات: {$agent['name']} - {$agent['mobile']}. رابط الموقع: " . ($o['location_url'] ?? ''); if (!is_rtl()) { $msg = "Your opportunity has been assigned to {$branchName}. Sales agent: {$agent['name']} - {$agent['mobile']}. Location: " . ($o['location_url'] ?? ''); } $to = ($o['country_code'] ?? '+966') . ($o['mobile'] ?? ''); $st = $this->pdo->prepare(" INSERT INTO sms_outbox(to_number, message, status, provider, related_entity_type, related_entity_id, created_by_user_id, created_at) VALUES(?,?,'queued',NULL,'opportunity',?,?,?) "); $st->execute([$to, $msg, (int)$o['id'], (int)$u['id'], now()]); $this->log('opportunity', (int)$o['id'], 'ASSIGN_BULK', (int)$u['id'], ['assigned_to'=>$agentId]); $this->log('opportunity', (int)$o['id'], 'SMS_QUEUED', (int)$u['id'], ['to'=>$to]); } $this->pdo->commit(); flash_set('success', "Bulk assigned: $affected opportunities"); } catch (Throwable $e) { $this->pdo->rollBack(); flash_set('danger', 'Error: ' . $e->getMessage()); } redirect(base_url($this->config, '/index.php?r=opps/index')); } public function bulk_upload(): void { $u = require_login($this->pdo, $this->config); if (!has_role($u, ['ADMIN','MARKETING','CC_SUP','SALES_MGR','GM_SALES'])) { http_response_code(403); echo 'Forbidden'; exit; } $branches = $this->pdo->query("SELECT id, name_en, name_ar, city FROM branches WHERE is_active=1 ORDER BY name_en")->fetchAll(PDO::FETCH_ASSOC); $this->render('opps/bulk_upload.php', ['u'=>$u, 'branches'=>$branches]); } public function bulk_template(): void { require_login($this->pdo, $this->config); header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename="opportunities_bulk_template.csv"'); echo "\xEF\xBB\xBF"; $out = fopen('php://output', 'w'); fputcsv($out, ['mobile','country_code','full_name','email','city','branch_id','source','campaign','vehicle_of_interest','model_year','salary','salary_range','obligation','obligation_amount','pref_time','purchase_time','notes']); fputcsv($out, ['500000000','+966','Customer Name','customer@example.com','Jeddah','1','Excel Upload','May Campaign','Honda Accord','2026','15000','10000-20000','no','','Morning','Within 30 days','Sample row - delete before upload']); fclose($out); exit; } public function bulk_upload_store(): void { $u = require_login($this->pdo, $this->config); if (!has_role($u, ['ADMIN','MARKETING','CC_SUP','SALES_MGR','GM_SALES'])) { http_response_code(403); echo 'Forbidden'; exit; } require_post(); csrf_check(); if (empty($_FILES['opportunities_file']['tmp_name']) || !is_uploaded_file($_FILES['opportunities_file']['tmp_name'])) { flash_set('danger', 'Please upload a CSV file.'); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } $fileName = $_FILES['opportunities_file']['name'] ?? ''; $ext = strtolower(pathinfo($fileName, PATHINFO_EXTENSION)); if ($ext !== 'csv') { flash_set('danger', 'Please save your Excel sheet as CSV and upload it. XLSX can be enabled later by installing PhpSpreadsheet.'); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } $defaultBranchId = (int)($_POST['default_branch_id'] ?? 0); $skipDuplicates = !empty($_POST['skip_duplicates']); $fh = fopen($_FILES['opportunities_file']['tmp_name'], 'r'); if (!$fh) { flash_set('danger', 'Unable to read uploaded file.'); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } $header = fgetcsv($fh); if (!$header) { flash_set('danger', 'CSV file is empty.'); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } if (isset($header[0])) $header[0] = preg_replace('/^\xEF\xBB\xBF/', '', (string)$header[0]); $header = array_map(fn($h) => strtolower(trim((string)$h)), $header); $required = ['mobile','full_name']; foreach ($required as $col) { if (!in_array($col, $header, true)) { flash_set('danger', "Missing required column: {$col}"); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } } if (!in_array('branch_id', $header, true) && $defaultBranchId <= 0) { flash_set('danger', 'Please provide branch_id in CSV or select a default branch.'); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } $created = 0; $skipped = 0; $errors = []; $rowNo = 1; $this->pdo->beginTransaction(); try { while (($row = fgetcsv($fh)) !== false) { $rowNo++; if (count(array_filter($row, fn($v) => trim((string)$v) !== '')) === 0) continue; $data = []; foreach ($header as $i => $key) $data[$key] = trim((string)($row[$i] ?? '')); $mobile = preg_replace('/\D+/', '', $data['mobile'] ?? ''); if (str_starts_with($mobile, '966')) $mobile = substr($mobile, 3); if (str_starts_with($mobile, '0')) $mobile = substr($mobile, 1); $name = $data['full_name'] ?? ''; $country = $data['country_code'] ?: '+966'; $branchId = (int)($data['branch_id'] ?: $defaultBranchId); if ($mobile === '' || $name === '' || $branchId <= 0) { $errors[] = "Row {$rowNo}: mobile, full_name and branch are required."; continue; } $st = $this->pdo->prepare('SELECT id FROM branches WHERE id=? AND is_active=1'); $st->execute([$branchId]); if (!$st->fetchColumn()) { $errors[] = "Row {$rowNo}: invalid branch_id {$branchId}."; continue; } $st = $this->pdo->prepare('SELECT id FROM contacts WHERE country_code=? AND mobile=?'); $st->execute([$country, $mobile]); $contactId = (int)($st->fetchColumn() ?: 0); if (!$contactId) { $st = $this->pdo->prepare('INSERT INTO contacts(country_code, mobile, full_name, email, city, notes, created_at) VALUES(?,?,?,?,?,?,?)'); $st->execute([$country, $mobile, $name, ($data['email'] ?: null), ($data['city'] ?: null), ($data['notes'] ?: null), now()]); $contactId = (int)$this->pdo->lastInsertId(); } else { $st = $this->pdo->prepare('UPDATE contacts SET full_name=COALESCE(NULLIF(?,\'\'), full_name), email=COALESCE(NULLIF(?,\'\'), email), city=COALESCE(NULLIF(?,\'\'), city), updated_at=? WHERE id=?'); $st->execute([$name, ($data['email'] ?? ''), ($data['city'] ?? ''), now(), $contactId]); } if ($skipDuplicates) { $st = $this->pdo->prepare("SELECT o.id FROM opportunities o JOIN contacts c ON c.id=o.contact_id WHERE c.country_code=? AND c.mobile=? AND o.status NOT IN ('lost','closed') LIMIT 1"); $st->execute([$country, $mobile]); if ($st->fetchColumn()) { $skipped++; continue; } } $leadCols = ['contact_id','source','campaign','lead_city','status','qualification','qualified_branch_id','created_by_user_id','created_at']; $leadVals = [$contactId, ($data['source'] ?: 'Bulk Upload'), ($data['campaign'] ?: null), ($data['city'] ?: null), 'qualified', 'qualified', $branchId, (int)$u['id'], now()]; $optionalMap = [ 'vehicle_of_interest'=>'vehicle_of_interest', 'model_year'=>'model_year', 'salary'=>'salary', 'obligation'=>'obligation', 'salary_range'=>'salary_range', 'obligation_amount'=>'obligation_amount', 'pref_time'=>'pref_time', 'purchase_time'=>'purchase_time' ]; foreach ($optionalMap as $col => $key) { if ($this->columnExists('leads', $col)) { $leadCols[] = $col; $leadVals[] = ($data[$key] ?? '') !== '' ? $data[$key] : null; } } foreach (['qualified_at'=>now(), 'qualified_by_user_id'=>(int)$u['id'], 'qualify_note'=>'Bulk opportunity upload'] as $col => $val) { if ($this->columnExists('leads', $col)) { $leadCols[] = $col; $leadVals[] = $val; } } $sql = 'INSERT INTO leads(' . implode(',', $leadCols) . ') VALUES(' . implode(',', array_fill(0, count($leadCols), '?')) . ')'; $st = $this->pdo->prepare($sql); $st->execute($leadVals); $leadId = (int)$this->pdo->lastInsertId(); $st = $this->pdo->prepare("INSERT INTO opportunities(lead_id, contact_id, branch_id, status, stage, created_at) VALUES(?,?,?,'new','open_opportunity',?)"); $st->execute([$leadId, $contactId, $branchId, now()]); $oppId = (int)$this->pdo->lastInsertId(); $st = $this->pdo->prepare('UPDATE leads SET converted_opportunity_id=? WHERE id=?'); $st->execute([$oppId, $leadId]); $this->log('opportunity', $oppId, 'BULK_UPLOAD_CREATED', (int)$u['id'], ['row'=>$rowNo, 'lead_id'=>$leadId]); $created++; } fclose($fh); $this->pdo->commit(); } catch (Throwable $e) { if (is_resource($fh)) fclose($fh); $this->pdo->rollBack(); flash_set('danger', 'Upload failed: ' . $e->getMessage()); redirect(base_url($this->config, '/index.php?r=opps/bulk_upload')); } $msg = "Bulk upload completed. Created: {$created}. Skipped duplicates: {$skipped}."; if ($errors) $msg .= ' Errors: ' . implode(' | ', array_slice($errors, 0, 10)); flash_set($errors ? 'warning' : 'success', $msg); redirect(base_url($this->config, '/index.php?r=opps/index')); } private function columnExists(string $table, string $column): bool { static $cache = []; $key = $table . '.' . $column; if (array_key_exists($key, $cache)) return $cache[$key]; $st = $this->pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?"); $st->execute([$column]); return $cache[$key] = (bool)$st->fetch(); } }