<?php
// models/Projet.php
require_once __DIR__ . '/Database.php';
class Projet {
private $db;
public function __construct() {
$this->db = Database::getInstance()->getConnection();
$this->ensureSchema();
}
private function ensureSchema(): void {
$this->db->exec("CREATE TABLE IF NOT EXISTS projets (
id INT AUTO_INCREMENT PRIMARY KEY,
devis_id INT DEFAULT NULL,
client_id INT DEFAULT NULL,
titre VARCHAR(255) NOT NULL,
description TEXT NULL,
date_projet DATE DEFAULT NULL,
montant DECIMAL(10,2) NOT NULL DEFAULT 0,
statut VARCHAR(50) NOT NULL DEFAULT 'Proposition'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}
public function getAll() {
$sql = 'SELECT p.*, c.nom AS client_nom, c.prenom AS client_prenom FROM projets p LEFT JOIN clients c ON p.client_id = c.id ORDER BY date_projet DESC, id DESC';
$stmt = $this->db->query($sql);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public function getById($id) {
$sql = 'SELECT p.*, c.nom AS client_nom, c.prenom AS client_prenom FROM projets p LEFT JOIN clients c ON p.client_id = c.id WHERE p.id = ?';
$stmt = $this->db->prepare($sql);
$stmt->execute([$id]);
return $stmt->fetch(PDO::FETCH_ASSOC);
}
public function getByDevisId($devisId) {
$sql = 'SELECT p.*, c.nom AS client_nom, c.prenom AS client_prenom FROM projets p LEFT JOIN clients c ON p.client_id = c.id WHERE p.devis_id = ? LIMIT 1';
$stmt = $this->db->prepare($sql);
$stmt->execute([$devisId]);
return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}
public function getPendingRevenue(): float {
$sql = "SELECT COALESCE(SUM(montant), 0) FROM projets WHERE LOWER(statut) NOT IN ('terminé', 'termine')";
$stmt = $this->db->query($sql);
return (float) $stmt->fetchColumn();
}
public function getCompletedRevenue(): float {
$sql = "SELECT COALESCE(SUM(montant), 0) FROM projets WHERE LOWER(statut) IN ('terminé', 'termine')";
$stmt = $this->db->query($sql);
return (float) $stmt->fetchColumn();
}
public function getActiveForDashboard(int $limit = 5): array {
$limit = max(1, $limit);
$sql = "SELECT p.*, c.nom AS client_nom, c.prenom AS client_prenom, c.entreprise AS client_entreprise
FROM projets p
LEFT JOIN clients c ON p.client_id = c.id
WHERE LOWER(p.statut) IN ('proposition', 'en cours')
ORDER BY CASE WHEN LOWER(p.statut) = 'en cours' THEN 0 ELSE 1 END, p.date_projet DESC, p.id DESC
LIMIT " . $limit;
$stmt = $this->db->query($sql);
return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
public function getPipelineSummaryForDashboard(): array {
$sql = "SELECT
SUM(CASE WHEN LOWER(statut) = 'proposition' THEN 1 ELSE 0 END) AS proposal_count,
SUM(CASE WHEN LOWER(statut) = 'en cours' THEN 1 ELSE 0 END) AS in_progress_count,
SUM(CASE WHEN LOWER(statut) IN ('proposition', 'en cours') THEN 1 ELSE 0 END) AS active_count
FROM projets";
$row = $this->db->query($sql)->fetch(PDO::FETCH_ASSOC) ?: [];
return [
'proposal_count' => (int) ($row['proposal_count'] ?? 0),
'in_progress_count' => (int) ($row['in_progress_count'] ?? 0),
'active_count' => (int) ($row['active_count'] ?? 0),
];
}
public function getPipelineBreakdownForDashboard(): array {
$sql = "SELECT LOWER(TRIM(COALESCE(statut, ''))) AS normalized_status, COUNT(*) AS total_count
FROM projets
GROUP BY LOWER(TRIM(COALESCE(statut, '')))";
$rows = $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
$breakdown = [
'Proposition' => 0,
'En cours' => 0,
'Terminé' => 0,
'Autres' => 0,
];
foreach ($rows as $row) {
$status = (string) ($row['normalized_status'] ?? '');
$count = (int) ($row['total_count'] ?? 0);
if ($status === 'proposition') {
$breakdown['Proposition'] += $count;
} elseif ($status === 'en cours') {
$breakdown['En cours'] += $count;
} elseif ($status === 'termine' || $status === 'terminé') {
$breakdown['Terminé'] += $count;
} else {
$breakdown['Autres'] += $count;
}
}
$result = [];
foreach ($breakdown as $label => $value) {
if ($value > 0) {
$result[] = [
'label' => $label,
'value' => $value,
];
}
}
return $result;
}
public function create(array $data): ?int {
// Mise en majuscules de la ville si présente dans $data
if (isset($data['ville'])) {
$data['ville'] = mb_strtoupper($data['ville']);
}
$sql = 'INSERT INTO projets (devis_id, client_id, titre, description, date_projet, montant, statut) VALUES (?, ?, ?, ?, ?, ?, ?)';
$stmt = $this->db->prepare($sql);
$ok = $stmt->execute([
$data['devis_id'] ?? null,
$data['client_id'] ?? null,
$data['titre'],
$data['description'] ?? '',
$data['date_projet'],
$data['montant'] ?? 0,
$data['statut'] ?? 'Proposition',
]);
return $ok ? (int) $this->db->lastInsertId() : null;
}
public function update($id, array $data) {
// Mise en majuscules de la ville si présente dans $data
if (isset($data['ville'])) {
$data['ville'] = mb_strtoupper($data['ville']);
}
$sql = 'UPDATE projets SET devis_id = ?, client_id = ?, titre = ?, description = ?, date_projet = ?, montant = ?, statut = ? WHERE id = ?';
$stmt = $this->db->prepare($sql);
return $stmt->execute([
$data['devis_id'] ?? null,
$data['client_id'] ?? null,
$data['titre'],
$data['description'] ?? '',
$data['date_projet'],
$data['montant'] ?? 0,
$data['statut'] ?? 'Proposition',
$id,
]);
}
public function delete($id) {
$sql = 'DELETE FROM projets WHERE id = ?';
$stmt = $this->db->prepare($sql);
return $stmt->execute([$id]);
}
}