- Add role detection

- Restrict APIs to personnels
- Show 403 for unauthorized access"

Dockerfile

@@ -3,7 +3,7 @@ FROM denoland/deno:alpine
 WORKDIR /app
 
 COPY . .
-RUN deno cache main.ts --allow-import flag
+RUN deno cache --allow-import main.ts
 RUN deno task build
 
 USER deno

databases/init/mobility.sql

@@ -7,5 +7,5 @@ CREATE TABLE mobility (
     destinationCountry  text,
     destinationName     text,
     mobilityStatus      text default 'N/A',
-    foreign key (studentId) references students(userId)
+    attestationFile     blob
 );

defaults/interfaces.ts

@@ -3,11 +3,14 @@ import { AsyncRoute } from "$fresh/src/server/types.ts";
 
 interface AuthenticatedState {
   isAuthenticated: true;
+  isFromPolytech: boolean;
+  role: "etudiants" | "personnels" | "autres";
   session: CasContent;
 }
 
 interface UnauthenticatedState {
   isAuthenticated: false;
+  isFromPolytech: false;
   session: undefined;
 }
 

fresh.gen.ts

@@ -3,11 +3,13 @@
 // This file is automatically updated during development when running `dev.ts`.
 
 import * as $_apps_layout from "./routes/(apps)/_layout.tsx";
-import * as $_apps_mobility_api_insert_mobility from "./routes/(apps)/mobility/api/insert_mobility.ts";
+import * as $_apps_mobility_api_download from "./routes/(apps)/mobility/api/download.ts";
+import * as $_apps_mobility_api_insert_mobility from "./routes/(apps)/mobility/api/insert-mobility.ts";
 import * as $_apps_mobility_index from "./routes/(apps)/mobility/index.tsx";
 import * as $_apps_mobility_partials_admin_edit_mobility from "./routes/(apps)/mobility/partials/(admin)/edit_mobility.tsx";
 import * as $_apps_mobility_partials_index from "./routes/(apps)/mobility/partials/index.tsx";
 import * as $_apps_mobility_partials_overview from "./routes/(apps)/mobility/partials/overview.tsx";
+import * as $_apps_mobility_types_d from "./routes/(apps)/mobility/types.d.ts";
 import * as $_apps_notes_index from "./routes/(apps)/notes/index.tsx";
 import * as $_apps_notes_partials_admin_courses from "./routes/(apps)/notes/partials/(admin)/courses.tsx";
 import * as $_apps_notes_partials_index from "./routes/(apps)/notes/partials/index.tsx";
@@ -18,6 +20,7 @@ import * as $_apps_students_partials_admin_consult from "./routes/(apps)/student
 import * as $_apps_students_partials_admin_upload from "./routes/(apps)/students/partials/(admin)/upload.tsx";
 import * as $_apps_students_partials_index from "./routes/(apps)/students/partials/index.tsx";
 import * as $_apps_students_partials_overview from "./routes/(apps)/students/partials/overview.tsx";
+import * as $_403 from "./routes/_403.tsx";
 import * as $_404 from "./routes/_404.tsx";
 import * as $_app from "./routes/_app.tsx";
 import * as $_middleware from "./routes/_middleware.ts";
@@ -30,7 +33,6 @@ import * as $_islands_AppNavigator from "./routes/(_islands)/AppNavigator.tsx";
 import * as $_islands_Navbar from "./routes/(_islands)/Navbar.tsx";
 import * as $_apps_mobility_islands_ConsultMobility from "./routes/(apps)/mobility/(_islands)/ConsultMobility.tsx";
 import * as $_apps_mobility_islands_EditMobility from "./routes/(apps)/mobility/(_islands)/EditMobility.tsx";
-import * as $_apps_mobility_islands_ImportFile from "./routes/(apps)/mobility/(_islands)/ImportFile.tsx";
 import * as $_apps_students_islands_ConsultStudents from "./routes/(apps)/students/(_islands)/ConsultStudents.tsx";
 import * as $_apps_students_islands_EditStudents from "./routes/(apps)/students/(_islands)/EditStudents.tsx";
 import * as $_apps_students_islands_UploadStudents from "./routes/(apps)/students/(_islands)/UploadStudents.tsx";
@@ -39,7 +41,8 @@ import type { Manifest } from "$fresh/server.ts";
 const manifest = {
   routes: {
     "./routes/(apps)/_layout.tsx": $_apps_layout,
-    "./routes/(apps)/mobility/api/insert_mobility.ts":
+    "./routes/(apps)/mobility/api/download.ts": $_apps_mobility_api_download,
+    "./routes/(apps)/mobility/api/insert-mobility.ts":
       $_apps_mobility_api_insert_mobility,
     "./routes/(apps)/mobility/index.tsx": $_apps_mobility_index,
     "./routes/(apps)/mobility/partials/(admin)/edit_mobility.tsx":
@@ -48,6 +51,7 @@ const manifest = {
       $_apps_mobility_partials_index,
     "./routes/(apps)/mobility/partials/overview.tsx":
       $_apps_mobility_partials_overview,
+    "./routes/(apps)/mobility/types.d.ts": $_apps_mobility_types_d,
     "./routes/(apps)/notes/index.tsx": $_apps_notes_index,
     "./routes/(apps)/notes/partials/(admin)/courses.tsx":
       $_apps_notes_partials_admin_courses,
@@ -64,6 +68,7 @@ const manifest = {
       $_apps_students_partials_index,
     "./routes/(apps)/students/partials/overview.tsx":
       $_apps_students_partials_overview,
+    "./routes/_403.tsx": $_403,
     "./routes/_404.tsx": $_404,
     "./routes/_app.tsx": $_app,
     "./routes/_middleware.ts": $_middleware,
@@ -80,8 +85,6 @@ const manifest = {
       $_apps_mobility_islands_ConsultMobility,
     "./routes/(apps)/mobility/(_islands)/EditMobility.tsx":
       $_apps_mobility_islands_EditMobility,
-    "./routes/(apps)/mobility/(_islands)/ImportFile.tsx":
-      $_apps_mobility_islands_ImportFile,
     "./routes/(apps)/students/(_islands)/ConsultStudents.tsx":
       $_apps_students_islands_ConsultStudents,
     "./routes/(apps)/students/(_islands)/EditStudents.tsx":

routes/(apps)/mobility/(_islands)/ConsultMobility.tsx

@@ -1,113 +1,147 @@
 import { useEffect, useState } from "preact/hooks";
 
-interface Promotion {
-  id: number;
-  name: string;
-}
-
-interface Student {
-  id: string;
-  firstName: string;
-  lastName: string;
-  promotionId: number;
-}
-
-interface Mobility {
-  id: number;
-  studentId: string;
-  startDate: string | null;
-  endDate: string | null;
-  weeksCount: number | null;
-  destinationCountry: string | null;
-  destinationName: string | null;
-  mobilityStatus: string;
-}
-
 export default function ConsultMobility() {
-  const [data, setData] = useState<
+  const [mobilityData, setMobilityData] = useState<MobilityData[]>([]);
-    | {
+  const [promotions, setPromotions] = useState<Promotion[]>([]);
-      promotions?: Promotion[];
+  const [selectedPromotion, setSelectedPromotion] = useState<number | "all">("all");
-      students?: Student[];
-      mobilities?: Mobility[];
-    }
-    | null
-  >(null);
   const [error, setError] = useState<string | null>(null);
 
   useEffect(() => {
     const fetchData = async () => {
-      console.log("ConsultMobility: Fetching data from API...");
       try {
-        const response = await fetch("/mobility/api/insert_mobility");
+        console.log("ConsultMobility: Fetching data from API...");
-        console.log("ConsultMobility: API response status:", response.status);
+        const response = await fetch("/mobility/api/insert-mobility");
-
         if (!response.ok) {
           throw new Error(`Error fetching data: ${response.statusText}`);
         }
 
         const result = await response.json();
         console.log("ConsultMobility: Data fetched successfully:", result);
-        setData(result);
+
+        setPromotions(result.promotions);
+
+        const mergedData = result.students.map((student: any) => {
+          const existingMobility = result.mobilities.find(
+            (mobility: any) => mobility.studentId === student.id
+          );
+          return {
+            id: existingMobility ? existingMobility.id : null, 
+            studentId: student.id,
+            firstName: student.firstName,
+            lastName: student.lastName,
+            startDate: existingMobility?.startDate || null,
+            endDate: existingMobility?.endDate || null,
+            weeksCount: existingMobility?.weeksCount || null,
+            destinationCountry: existingMobility?.destinationCountry || null,
+            destinationName: existingMobility?.destinationName || null,
+            mobilityStatus: existingMobility?.mobilityStatus || "N/A",
+            promotionId: student.promotionId,
+            promotionName: student.promotionName,
+            attestationFile: existingMobility?.attestationFile || null,
+          };
+        });
+
+        setMobilityData(mergedData);
       } catch (err) {
         console.error("ConsultMobility: Error fetching data:", err);
-        setError("Failed to load mobility data. Please try again later.");
+        setError("Failed to load data. Please try again later.");
       }
     };
 
     fetchData();
   }, []);
 
-  if (error) {
+  const filteredData =
-    return <p className="error">{error}</p>;
+    selectedPromotion === "all"
-  }
+      ? mobilityData
+      : mobilityData.filter((entry) => entry.promotionId === selectedPromotion);
 
-  if (!data?.promotions) {
+  const downloadFile = (id: number | null) => {
-    return <p>No promotions found.</p>;
+    if (!id) {
-  }
+      alert("No file available for download.");
+      return;
+    }
+
+    const downloadUrl = `/mobility/api/download/${id}`;
+    window.open(downloadUrl, "_blank");
+  };
 
   return (
     <section>
       <h2>Consult Mobility</h2>
-      {data.promotions.map((promo) => (
+      {error && <p className="error">{error}</p>}
+
+      <div>
+        <label htmlFor="promotionSelect">Select Promotion: </label>
+        <select
+          id="promotionSelect"
+          value={selectedPromotion}
+          onChange={(e) =>
+            setSelectedPromotion(
+              e.target.value === "all" ? "all" : Number(e.target.value)
+            )
+          }
+        >
+          <option value="all">All Promotions</option>
+          {promotions.map((promo) => (
+            <option key={promo.id} value={promo.id}>
+              {promo.name}
+            </option>
+          ))}
+        </select>
+      </div>
+
+      {promotions.map((promo) => (
         <div key={promo.id}>
-          <h3>Promotion: {promo.name}</h3>
+          {selectedPromotion === "all" || selectedPromotion === promo.id ? (
-          <table>
+            <>
-            <thead>
+              <h3>Promotion: {promo.name}</h3>
-              <tr>
+              <table>
-                <th>ID</th>
+                <thead>
-                <th>First Name</th>
+                  <tr>
-                <th>Last Name</th>
+                    <th>ID</th>
-                <th>Start Date</th>
+                    <th>First Name</th>
-                <th>End Date</th>
+                    <th>Last Name</th>
-                <th>Weeks Count</th>
+                    <th>Start Date</th>
-                <th>Destination Country</th>
+                    <th>End Date</th>
-                <th>Destination Name</th>
+                    <th>Weeks Count</th>
-                <th>Status</th>
+                    <th>Destination Country</th>
-              </tr>
+                    <th>Destination Name</th>
-            </thead>
+                    <th>Status</th>
-            <tbody>
+                    <th>Attestation File</th>
-              {data.students
+                  </tr>
-                ?.filter((student) => student.promotionId === promo.id)
+                </thead>
-                .map((student) => {
+                <tbody>
-                  const mobility = data.mobilities?.find((mob) =>
+                  {filteredData
-                    mob.studentId === student.id
+                    .filter((entry) => entry.promotionId === promo.id)
-                  );
+                    .map((entry) => (
-                  return (
+                      <tr key={entry.studentId}>
-                    <tr key={student.id}>
+                        <td>{entry.studentId}</td>
-                      <td>{student.id}</td>
+                        <td>{entry.firstName}</td>
-                      <td>{student.firstName}</td>
+                        <td>{entry.lastName}</td>
-                      <td>{student.lastName}</td>
+                        <td>{entry.startDate || "N/A"}</td>
-                      <td>{mobility?.startDate || "N/A"}</td>
+                        <td>{entry.endDate || "N/A"}</td>
-                      <td>{mobility?.endDate || "N/A"}</td>
+                        <td>{entry.weeksCount || "0"}</td>
-                      <td>{mobility?.weeksCount ?? "N/A"}</td>
+                        <td>{entry.destinationCountry || "N/A"}</td>
-                      <td>{mobility?.destinationCountry || "N/A"}</td>
+                        <td>{entry.destinationName || "N/A"}</td>
-                      <td>{mobility?.destinationName || "N/A"}</td>
+                        <td>{entry.mobilityStatus}</td>
-                      <td>{mobility?.mobilityStatus || "N/A"}</td>
+                        <td>
-                    </tr>
+                          {entry.attestationFile ? (
-                  );
+                            <button
-                })}
+                              onClick={() => downloadFile(entry.id)}
-            </tbody>
+                            >
-          </table>
+                              Download
+                            </button>
+                          ) : (
+                            "No file"
+                          )}
+                        </td>
+                      </tr>
+                    ))}
+                </tbody>
+              </table>
+            </>
+          ) : null}
         </div>
       ))}
     </section>

routes/(apps)/mobility/(_islands)/ConsultStudents_test.tsx

@@ -1,75 +0,0 @@
-import { useEffect, useState } from "preact/hooks";
-
-interface Promotion {
-  id: number;
-  name: string;
-}
-
-interface Student {
-  id: number;
-  firstName: string;
-  lastName: string;
-  mail: string;
-  promotionId: number;
-  promotionName: string;
-}
-
-export default function ConsultStudents_test() {
-  const [data, setData] = useState<
-    { promotions: Promotion[]; students: Student[] } | null
-  >(null);
-  const [error, setError] = useState<string | null>(null);
-
-  useEffect(() => {
-    const fetchData = async () => {
-      try {
-        const response = await fetch("/students/api/insert_students");
-        if (!response.ok) {
-          throw new Error(`Error fetching data: ${response.statusText}`);
-        }
-
-        const result = await response.json();
-        setData(result);
-      } catch (err) {
-        console.error("Error fetching data:", err);
-        setError("Failed to load data. Please try again later.");
-      }
-    };
-
-    fetchData();
-  }, []);
-
-  return (
-    <section>
-      <h2>Consult Students</h2>
-      {error && <p className="error">{error}</p>}
-      {data?.promotions.map((promo) => (
-        <div key={promo.id}>
-          <h3>Promotion: {promo.id}</h3>
-          <table>
-            <thead>
-              <tr>
-                <th>ID</th>
-                <th>First Name</th>
-                <th>Last Name</th>
-                <th>Email</th>
-              </tr>
-            </thead>
-            <tbody>
-              {data.students
-                .filter((student) => student.promotionId === promo.id)
-                .map((student) => (
-                  <tr key={student.id}>
-                    <td>{student.id}</td>
-                    <td>{student.firstName}</td>
-                    <td>{student.lastName}</td>
-                    <td>{student.mail}</td>
-                  </tr>
-                ))}
-            </tbody>
-          </table>
-        </div>
-      ))}
-    </section>
-  );
-}

routes/(apps)/mobility/(_islands)/EditMobility.tsx

@@ -1,117 +1,97 @@
 import { useEffect, useState } from "preact/hooks";
 
-interface Student {
-  id: string;
-  firstName: string;
-  lastName: string;
-  promotionId: number;
-}
-
-interface Promotion {
-  id: number;
-  name: string;
-}
-
-interface Mobility {
-  id: number | null;
-  studentId: string;
-  startDate: string | null;
-  endDate: string | null;
-  weeksCount: number | null;
-  destinationCountry: string | null;
-  destinationName: string | null;
-  mobilityStatus: string;
-}
-
 export default function EditMobility() {
-  const [data, setData] = useState<
+  const [mobilityData, setMobilityData] = useState<MobilityData[]>([]);
-    | {
+  const [promotions, setPromotions] = useState<Promotion[]>([]);
-      promotions?: Promotion[];
+  const [selectedPromotion, setSelectedPromotion] = useState<number | "all">("all");
-      students?: Student[];
-      mobilities?: Mobility[];
-    }
-    | null
-  >(null);
-  const [error, setError] = useState<string | null>(null);
   const [isSaving, setIsSaving] = useState(false);
 
   useEffect(() => {
-    const fetchData = async () => {
+    async function fetchMobilityData() {
       console.log("EditMobility: Fetching data from API...");
-      try {
+      const response = await fetch("/mobility/api/insert-mobility");
-        const response = await fetch("/mobility/api/insert_mobility");
+      const data = await response.json();
-        console.log("EditMobility: API response status:", response.status);
+      console.log("EditMobility: Data fetched successfully:", data);
 
-        if (!response.ok) {
+      setPromotions(data.promotions);
-          throw new Error(`Error fetching data: ${response.statusText}`);
-        }
 
-        const result = await response.json();
+      const initializedData = data.students.map((student: any) => {
-        console.log("EditMobility: Data fetched successfully:", result);
+        const existingMobility = data.mobilities.find(
-        setData(result);
+          (mobility: any) => mobility.studentId === student.id
-      } catch (err) {
+        );
-        console.error("EditMobility: Error fetching data:", err);
+        return {
-        setError("Failed to load mobility data. Please try again later.");
+          id: existingMobility ? existingMobility.id : null,
-      }
+          studentId: student.id,
-    };
+          firstName: student.firstName,
+          lastName: student.lastName,
+          startDate: existingMobility?.startDate || null,
+          endDate: existingMobility?.endDate || null,
+          weeksCount: existingMobility?.weeksCount || null,
+          destinationCountry: existingMobility?.destinationCountry || null,
+          destinationName: existingMobility?.destinationName || null,
+          mobilityStatus: existingMobility?.mobilityStatus || "N/A",
+          attestationFile: existingMobility?.attestationFile || null,
+          promotionId: student.promotionId,
+          promotionName: student.promotionName,
+        };
+      });
+      setMobilityData(initializedData);
+    }
 
-    fetchData();
+    fetchMobilityData();
   }, []);
 
-  const handleChange = (
+  const handleFileChange = (studentId: string, file: File | null) => {
-    studentId: string,
+    if (file && file.type !== "application/pdf") {
-    field: keyof Mobility,
+      alert("Only PDF files are allowed.");
-    value: string | number | null,
+      return;
-  ) => {
+    }
-    if (!data) return;
 
-    setData((prevData) => {
+    setMobilityData((prev) =>
-      if (!prevData) return null;
+      prev.map((entry) =>
-
+        entry.studentId === studentId ? { ...entry, attestationFile: file } : entry
-      const updatedMobilities = prevData.mobilities?.map((mobility) => {
+      )
-        if (mobility.studentId === studentId) {
+    );
-          const updatedMobility = { ...mobility, [field]: value };
-
-          if (field === "startDate" || field === "endDate") {
-            const startDate = new Date(updatedMobility.startDate || "");
-            const endDate = new Date(updatedMobility.endDate || "");
-            if (startDate && endDate && startDate <= endDate) {
-              const weeks = Math.ceil(
-                (endDate.getTime() - startDate.getTime()) /
-                  (7 * 24 * 60 * 60 * 1000),
-              );
-              updatedMobility.weeksCount = weeks;
-            } else {
-              updatedMobility.weeksCount = null;
-            }
-          }
-
-          return updatedMobility;
-        }
-        return mobility;
-      }) || [];
-
-      return { ...prevData, mobilities: updatedMobilities };
-    });
   };
 
   const handleSave = async () => {
     setIsSaving(true);
 
     try {
-      const response = await fetch("/mobility/api/insert_mobility", {
+      console.log("EditMobility: Sending data to API...");
-        method: "POST",
+
-        headers: { "Content-Type": "application/json" },
+      const formData = new FormData();
-        body: JSON.stringify({ data: data?.mobilities }),
+
+      mobilityData.forEach((entry) => {
+        formData.append(
+          "data",
+          JSON.stringify({
+            id: entry.id,
+            studentId: entry.studentId,
+            startDate: entry.startDate,
+            endDate: entry.endDate,
+            destinationCountry: entry.destinationCountry,
+            destinationName: entry.destinationName,
+            mobilityStatus: entry.mobilityStatus,
+          })
+        );
+
+        if (entry.attestationFile instanceof File) {
+          formData.append(`file_${entry.studentId}`, entry.attestationFile);
+        }
       });
 
-      console.log("EditMobility: Save response status:", response.status);
+      const response = await fetch("/mobility/api/insert-mobility", {
+        method: "POST",
+        body: formData,
+      });
 
       if (response.ok) {
         alert("Data saved successfully!");
-        globalThis.location.reload();
+        console.log("EditMobility: Save response status:", response.status);
       } else {
-        throw new Error(`Failed to save data: ${response.statusText}`);
+        alert("Failed to save data.");
+        console.error("EditMobility: Save response status:", response.status);
       }
     } catch (error) {
       console.error("EditMobility: Error saving data:", error);
@@ -121,110 +101,143 @@ export default function EditMobility() {
     }
   };
 
-  if (error) {
+  const filteredData =
-    return <p className="error">{error}</p>;
+    selectedPromotion === "all"
-  }
+      ? mobilityData
+      : mobilityData.filter((entry) => entry.promotionId === selectedPromotion);
 
-  if (!data?.promotions) {
+  const groupedData = promotions.map((promo) => ({
-    return <p>Loading data...</p>;
+    promotion: promo.name,
-  }
+    students: filteredData.filter((entry) => entry.promotionId === promo.id),
+  }));
+
+  const handleDownload = (id: number) => {
+    window.open(`/mobility/api/download/${id}`, "_blank");
+  };
 
   return (
-    <section>
+    <div>
       <h2>Edit Mobility</h2>
-      {data.promotions.map((promo) => (
-        <div key={promo.id}>
-          <h3>Promotion: {promo.name}</h3>
-          <table>
-            <thead>
-              <tr>
-                <th>ID</th>
-                <th>First Name</th>
-                <th>Last Name</th>
-                <th>Start Date</th>
-                <th>End Date</th>
-                <th>Weeks Count</th>
-                <th>Destination Country</th>
-                <th>Destination Name</th>
-                <th>Status</th>
-              </tr>
-            </thead>
-            <tbody>
-              {data.students
-                ?.filter((student) => student.promotionId === promo.id)
-                .map((student) => {
-                  const mobility = data.mobilities?.find((mob) =>
-                    mob.studentId === student.id
-                  ) || {
-                    id: null,
-                    studentId: student.id,
-                    startDate: null,
-                    endDate: null,
-                    weeksCount: null,
-                    destinationCountry: null,
-                    destinationName: null,
-                    mobilityStatus: "N/A",
-                  };
 
-                  return (
+      <div>
-                    <tr key={student.id}>
+        <label htmlFor="promotionSelect">Select Promotion: </label>
-                      <td>{student.id}</td>
+        <select
-                      <td>{student.firstName}</td>
+          id="promotionSelect"
-                      <td>{student.lastName}</td>
+          value={selectedPromotion}
+          onChange={(e) =>
+            setSelectedPromotion(
+              e.target.value === "all" ? "all" : Number(e.target.value)
+            )
+          }
+        >
+          <option value="all">All Promotions</option>
+          {promotions.map((promo) => (
+            <option key={promo.id} value={promo.id}>
+              {promo.name}
+            </option>
+          ))}
+        </select>
+      </div>
+
+      {groupedData.map((group) => (
+        <div key={group.promotion}>
+          {group.students.length > 0 && (
+            <>
+              <h3>Promotion: {group.promotion}</h3>
+              <table>
+                <thead>
+                  <tr>
+                    <th>ID</th>
+                    <th>First Name</th>
+                    <th>Last Name</th>
+                    <th>Start Date</th>
+                    <th>End Date</th>
+                    <th>Weeks Count</th>
+                    <th>Destination Country</th>
+                    <th>Destination Name</th>
+                    <th>Status</th>
+                    <th>Attestation File</th>
+                  </tr>
+                </thead>
+                <tbody>
+                  {group.students.map((entry) => (
+                    <tr key={entry.studentId}>
+                      <td>{entry.studentId}</td>
+                      <td>{entry.firstName}</td>
+                      <td>{entry.lastName}</td>
                       <td>
                         <input
                           type="date"
-                          value={mobility.startDate || ""}
+                          value={entry.startDate || ""}
                           onChange={(e) =>
-                            handleChange(
+                            setMobilityData((prev) =>
-                              student.id,
+                              prev.map((data) =>
-                              "startDate",
+                                data.studentId === entry.studentId
-                              e.target.value,
+                                  ? { ...data, startDate: e.target.value }
-                            )}
+                                  : data
+                              )
+                            )
+                          }
                         />
                       </td>
                       <td>
                         <input
                           type="date"
-                          value={mobility.endDate || ""}
+                          value={entry.endDate || ""}
                           onChange={(e) =>
-                            handleChange(student.id, "endDate", e.target.value)}
+                            setMobilityData((prev) =>
+                              prev.map((data) =>
+                                data.studentId === entry.studentId
+                                  ? { ...data, endDate: e.target.value }
+                                  : data
+                              )
+                            )
+                          }
                         />
                       </td>
-                      <td>{mobility.weeksCount ?? "N/A"}</td>
+                      <td>{entry.weeksCount || "0"}</td>
                       <td>
                         <input
                           type="text"
-                          value={mobility.destinationCountry || ""}
+                          value={entry.destinationCountry || ""}
                           onChange={(e) =>
-                            handleChange(
+                            setMobilityData((prev) =>
-                              student.id,
+                              prev.map((data) =>
-                              "destinationCountry",
+                                data.studentId === entry.studentId
-                              e.target.value,
+                                  ? { ...data, destinationCountry: e.target.value }
-                            )}
+                                  : data
+                              )
+                            )
+                          }
                         />
                       </td>
                       <td>
                         <input
                           type="text"
-                          value={mobility.destinationName || ""}
+                          value={entry.destinationName || ""}
                           onChange={(e) =>
-                            handleChange(
+                            setMobilityData((prev) =>
-                              student.id,
+                              prev.map((data) =>
-                              "destinationName",
+                                data.studentId === entry.studentId
-                              e.target.value,
+                                  ? { ...data, destinationName: e.target.value }
-                            )}
+                                  : data
+                              )
+                            )
+                          }
                         />
                       </td>
                       <td>
                         <select
-                          value={mobility.mobilityStatus}
+                          value={entry.mobilityStatus}
                           onChange={(e) =>
-                            handleChange(
+                            setMobilityData((prev) =>
-                              student.id,
+                              prev.map((data) =>
-                              "mobilityStatus",
+                                data.studentId === entry.studentId
-                              e.target.value,
+                                  ? { ...data, mobilityStatus: e.target.value }
-                            )}
+                                  : data
+                              )
+                            )
+                          }
                         >
                           <option value="N/A">N/A</option>
                           <option value="Planned">Planned</option>
@@ -233,16 +246,51 @@ export default function EditMobility() {
                           <option value="Validated">Validated</option>
                         </select>
                       </td>
+                      <td>
+                        {entry.attestationFile ? (
+                          <>
+                            <button onClick={() => handleDownload(entry.id!)}>
+                              Download
+                            </button>
+                            <button
+                              onClick={() =>
+                                setMobilityData((prev) =>
+                                  prev.map((data) =>
+                                    data.studentId === entry.studentId
+                                      ? { ...data, attestationFile: null }
+                                      : data
+                                  )
+                                )
+                              }
+                            >
+                              Delete
+                            </button>
+                          </>
+                        ) : (
+                          <input
+                            type="file"
+                            accept=".pdf"
+                            onChange={(e) =>
+                              handleFileChange(
+                                entry.studentId,
+                                e.target.files?.[0] || null
+                              )
+                            }
+                          />
+                        )}
+                      </td>
                     </tr>
-                  );
+                  ))}
-                })}
+                </tbody>
-            </tbody>
+              </table>
-          </table>
+            </>
+          )}
         </div>
       ))}
+
       <button onClick={handleSave} disabled={isSaving}>
         {isSaving ? "Saving..." : "Confirm"}
       </button>
-    </section>
+    </div>
   );
 }

routes/(apps)/mobility/(_props)/props.ts

@@ -8,9 +8,8 @@ const properties: AppProperties = {
     index: "Homepage",
     overview: "Mobility overview",
     edit_mobility: "Mobility management",
-    consult_students_test: "Test consult students",
   },
-  adminOnly: ["edit_mobility", "consult_students_test"],
+  adminOnly: ["edit_mobility"],
 };
 
 export default properties;

routes/(apps)/mobility/api/download.ts

@@ -0,0 +1,43 @@
+import { Handlers } from "$fresh/server.ts";
+import connect from "$root/databases/connect.ts";
+
+export const handler: Handlers = {
+  async GET(request) {
+    try {
+      console.log("API /mobility/api/download/:id GET called");
+
+      const url = new URL(request.url);
+      const id = url.pathname.split("/").pop();
+
+      if (!id) {
+        return new Response("Invalid request: Missing ID", { status: 400 });
+      }
+
+      console.log("Connecting to mobility database...");
+      using connection = connect("mobility");
+      console.log("Connected to databases.");
+
+      const query = connection.database.prepare(
+        `SELECT attestationFile FROM mobility WHERE id = ?`
+      );
+      const result = query.get(id);
+
+      if (!result || !result.attestationFile) {
+        return new Response("No file found for the given ID", { status: 404 });
+      }
+
+      const fileBuffer = result.attestationFile;
+
+      return new Response(fileBuffer, {
+        status: 200,
+        headers: {
+          "Content-Type": "application/pdf",
+          "Content-Disposition": `attachment; filename="attestation_${id}.pdf"`,
+        },
+      });
+    } catch (error) {
+      console.error("Error fetching file:", error);
+      return new Response("Failed to fetch file", { status: 500 });
+    }
+  },
+};

routes/(apps)/mobility/api/insert-mobility.ts

@@ -0,0 +1,131 @@
+import { Handlers } from "$fresh/server.ts";
+import connect from "$root/databases/connect.ts";
+
+export const handler: Handlers = {
+  // deno-lint-ignore require-await
+  async GET() {
+    try {
+      using connection = connect("mobility");
+
+      const mobilities = connection.database.prepare(
+        `SELECT 
+          mobility.id, 
+          mobility.studentId, 
+          mobility.startDate, 
+          mobility.endDate, 
+          mobility.weeksCount, 
+          mobility.destinationCountry, 
+          mobility.destinationName, 
+          mobility.mobilityStatus, 
+          mobility.attestationFile -- Inclure le fichier
+        FROM mobility`
+      ).all();
+
+      const students = connection.database.prepare(
+        `SELECT 
+          students.userId AS id, 
+          students.firstName, 
+          students.lastName, 
+          students.promotionId AS promotionId, 
+          promotions.name AS promotionName
+        FROM students.students
+        LEFT JOIN students.promotions ON students.promotionId = promotions.id`
+      ).all();
+
+      const promotions = connection.database.prepare(
+        `SELECT id, name FROM students.promotions`
+      ).all();
+
+      return new Response(
+        JSON.stringify({ mobilities, students, promotions }),
+        {
+          status: 200,
+          headers: { "Content-Type": "application/json" },
+        },
+      );
+    } catch (error) {
+      console.error("Error fetching mobility data:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+
+  async POST(request) {
+    console.log("API /mobility/api/insert-mobility POST called");
+
+    try {
+      const formData = await request.formData();
+      const dataEntries = formData.getAll("data").map((item) => JSON.parse(item as string));
+      console.log("Parsed data entries:", dataEntries);
+
+      const fileMap: Record<string, Uint8Array> = {};
+      for (const [key, value] of formData.entries()) {
+        if (key.startsWith("file_") && value instanceof File) {
+          const studentId = key.split("_")[1]; 
+          const file = value as File;
+          fileMap[studentId] = new Uint8Array(await file.arrayBuffer());
+          console.log(`File processed for studentId ${studentId}`);
+        }
+      }
+
+      using connection = connect("mobility");
+      const insertQuery = connection.database.prepare(
+        `INSERT INTO mobility (
+          id, studentId, startDate, endDate, weeksCount, destinationCountry, destinationName, mobilityStatus, attestationFile
+        )
+        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
+        ON CONFLICT(id) DO UPDATE SET
+          startDate = excluded.startDate,
+          endDate = excluded.endDate,
+          weeksCount = excluded.weeksCount,
+          destinationCountry = excluded.destinationCountry,
+          destinationName = excluded.destinationName,
+          mobilityStatus = excluded.mobilityStatus,
+          attestationFile = excluded.attestationFile`
+      );
+
+      for (const mobility of dataEntries) {
+        const {
+          id = null,
+          studentId,
+          startDate,
+          endDate,
+          destinationCountry,
+          destinationName,
+          mobilityStatus = "N/A",
+        } = mobility;
+
+        let calculatedWeeksCount = null;
+        if (startDate && endDate) {
+          const start = new Date(startDate);
+          const end = new Date(endDate);
+          if (start <= end) {
+            const differenceInDays = Math.ceil(
+              (end.getTime() - start.getTime()) / (24 * 60 * 60 * 1000)
+            );
+            calculatedWeeksCount = Math.floor(differenceInDays / 7);
+          }
+        }
+        const attestationFile = fileMap[studentId] || null;
+
+        console.log(`Inserting/Updating mobility for studentId: ${studentId}`);
+        insertQuery.run(
+          id,
+          studentId,
+          startDate,
+          endDate,
+          calculatedWeeksCount,
+          destinationCountry,
+          destinationName,
+          mobilityStatus,
+          attestationFile
+        );
+      }
+
+      console.log("Mobility data inserted/updated successfully.");
+      return new Response("Data inserted/updated successfully", { status: 200 });
+    } catch (error) {
+      console.error("Error inserting mobility data:", error);
+      return new Response("Failed to insert/update data", { status: 500 });
+    }
+  },
+};

routes/(apps)/mobility/api/insert_mobility.ts

@@ -1,168 +0,0 @@
-import { Handlers } from "$fresh/server.ts";
-import { Database } from "@db/sqlite";
-
-export const handler: Handlers = {
-  // deno-lint-ignore require-await
-  async GET() {
-    try {
-      console.log("Connecting to mobility database...");
-      const connection = new Database("databases/data/mobility.db", {
-        create: false,
-      });
-      connection.run(
-        "ATTACH DATABASE 'databases/data/students.db' AS students",
-      );
-      console.log("Connected to databases.");
-
-      const students = connection.prepare(
-        `SELECT 
-          students.userId AS id, 
-          students.firstName, 
-          students.lastName, 
-          students.promotionId AS promotionId, 
-          promotions.name AS promotionName
-         FROM students.students
-         LEFT JOIN students.promotions ON students.promotionId = promotions.id`,
-      ).all();
-
-      const mobilities = connection.prepare(
-        `SELECT 
-          mobility.id, 
-          mobility.studentId, 
-          mobility.startDate, 
-          mobility.endDate, 
-          mobility.weeksCount, 
-          mobility.destinationCountry, 
-          mobility.destinationName, 
-          mobility.mobilityStatus 
-        FROM mobility`,
-      ).all();
-
-      const promotions = connection.prepare(
-        `SELECT id, name FROM students.promotions`,
-      ).all();
-
-      connection.close();
-
-      return new Response(
-        JSON.stringify({ mobilities, students, promotions }),
-        {
-          status: 200,
-          headers: { "Content-Type": "application/json" },
-        },
-      );
-    } catch (error) {
-      console.error("Error fetching mobility data:", error);
-      return new Response("Failed to fetch data", { status: 500 });
-    }
-  },
-
-  async POST(request) {
-    console.log("API /mobility/api/insert_mobility POST called");
-
-    try {
-      const body = await request.json();
-      const { data } = body;
-
-      if (!Array.isArray(data)) {
-        throw new Error("Invalid request body");
-      }
-      console.log("Connecting to mobility database...");
-      const connection = new Database("databases/data/mobility.db", {
-        create: false,
-      });
-
-      console.log("Attaching students database...");
-      connection.run(
-        "ATTACH DATABASE 'databases/data/students.db' AS students",
-      );
-      console.log("Students database attached successfully.");
-
-      const insertQuery = connection.prepare(
-        `INSERT INTO mobility (
-          id, studentId, startDate, endDate, weeksCount, destinationCountry, destinationName, mobilityStatus
-        )
-        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
-        ON CONFLICT(id) DO UPDATE SET
-          startDate = excluded.startDate,
-          endDate = excluded.endDate,
-          weeksCount = excluded.weeksCount,
-          destinationCountry = excluded.destinationCountry,
-          destinationName = excluded.destinationName,
-          mobilityStatus = excluded.mobilityStatus`,
-      );
-
-      for (const mobility of data) {
-        const {
-          id,
-          studentId,
-          startDate,
-          endDate,
-          weeksCount,
-          destinationCountry,
-          destinationName,
-          mobilityStatus = "N/A",
-        } = mobility;
-
-        console.log("Processing mobility data:", mobility);
-
-        const studentExists = connection
-          .prepare(
-            `SELECT COUNT(*) AS count FROM students.students WHERE userId = ?`,
-          )
-          .get(studentId);
-
-        console.log(`Student ${studentId} exists:`, studentExists.count > 0);
-
-        if (studentExists.count === 0) {
-          console.warn(`Skipping mobility for unknown studentId: ${studentId}`);
-          continue;
-        }
-
-        let calculatedWeeksCount = weeksCount;
-        if (startDate && endDate) {
-          const start = new Date(startDate);
-          const end = new Date(endDate);
-          if (start <= end) {
-            calculatedWeeksCount = Math.ceil(
-              (end.getTime() - start.getTime()) / (7 * 24 * 60 * 60 * 1000),
-            );
-          } else {
-            calculatedWeeksCount = null;
-          }
-        }
-
-        console.log("Executing SQL insert/update query for:", {
-          id,
-          studentId,
-          startDate,
-          endDate,
-          calculatedWeeksCount,
-          destinationCountry,
-          destinationName,
-          mobilityStatus,
-        });
-
-        insertQuery.run(
-          id,
-          studentId,
-          startDate,
-          endDate,
-          calculatedWeeksCount,
-          destinationCountry,
-          destinationName,
-          mobilityStatus,
-        );
-      }
-
-      connection.close();
-      console.log("Mobility data inserted/updated successfully.");
-      return new Response("Data inserted/updated successfully", {
-        status: 200,
-      });
-    } catch (error) {
-      console.error("Error inserting mobility data:", error);
-      return new Response("Failed to insert/update data", { status: 500 });
-    }
-  },
-};

routes/(apps)/mobility/partials/(admin)/consult_students_test.tsx

@@ -1,21 +0,0 @@
-import ConsultStudents_test from "$root/routes/(apps)/mobility/(_islands)/ConsultStudents_test.tsx";
-import {
-  getPartialsConfig,
-  makePartials,
-} from "$root/defaults/makePartials.tsx";
-import { FreshContext } from "$fresh/server.ts";
-import { State } from "$root/routes/_middleware.ts";
-//import EditStudents from "../(_islands)/EditStudents.tsx";
-
-// deno-lint-ignore require-await
-async function Mobility(_request: Request, _context: FreshContext<State>) {
-  return (
-    <>
-      <h1>Test consult students</h1>
-      <ConsultStudents_test />
-    </>
-  );
-}
-
-export const config = getPartialsConfig();
-export default makePartials(Mobility);

routes/(apps)/mobility/partials/overview.tsx

@@ -10,7 +10,7 @@ import { State } from "$root/routes/_middleware.ts";
 async function Mobility(_request: Request, _context: FreshContext<State>) {
   return (
     <>
-      <h1>Edit mobility</h1>
+      <h1>Mobility overview</h1>
       <ConsultMobility />
     </>
   );

routes/(apps)/mobility/types.d.ts

@@ -0,0 +1,21 @@
+interface Promotion {
+    id: number;
+    name: string;
+}
+
+interface MobilityData {
+    id: number | null;
+    studentId: string;
+    firstName: string;
+    lastName: string;
+    startDate: string | null;
+    endDate: string | null;
+    weeksCount: number | null;
+    destinationCountry: string | null;
+    destinationName: string | null;
+    mobilityStatus: string;
+    promotionId: number;
+    promotionName: string;
+    //attestationFile: File | null;
+  }
+  

routes/(apps)/students/api/insert_students.ts

@@ -1,5 +1,4 @@
 import { Handlers } from "$fresh/server.ts";
-// import { Database } from "@db/sqlite";
 import connect from "$root/databases/connect.ts";
 
 export const handler: Handlers = {

routes/_403.tsx

@@ -0,0 +1,12 @@
+import { Head } from "$fresh/runtime.ts";
+
+export default function Error403() {
+  return (
+    <>
+      <Head>
+        <title>403 - Forbidden</title>
+      </Head>
+      <p>403</p>
+    </>
+  );
+}

routes/_middleware.ts

@@ -21,10 +21,19 @@ const deleteKey = (user: string) => delete jwtKeyCache[user];
  * @returns `true` if the route is public, `false` otherwise.
  */
 function isRoutePublic(route: string): boolean {
-  return PUBLIC_ROUTES.includes(route) ||
+  return (
-    !!(route.match(/\..+$/)?.[0] ?? false);
+    PUBLIC_ROUTES.includes(route) || !!(route.match(/\..+$/)?.[0] ?? false)
+  );
 }
 
+/**
+ * Checks if the given route is an API route.
+ * @param route The route to check.
+ * @returns `true` if the route is an API route, `false` otherwise.
+ */
+function isRouteAnAPI(route: string): boolean {
+  return route.includes("/api/");
+}
 /**
  * Get the given user's key, creating it if not already existing.
  * @param user The key's user.
@@ -44,6 +53,7 @@ export function getKey(user: string): string {
 export const handler: MiddlewareHandler<State>[] = [
   /**
    * Check if user is authenticated and add session to context accordingly.
+   * Only authenticated users who are members of Polytech are allowed.
    * @param request The HTTP incomming request.
    * @param context The Fresh context object with custom `State`.
    * @returns The response from the next middleware.
@@ -55,6 +65,7 @@ export const handler: MiddlewareHandler<State>[] = [
     const cookies = getCookies(request.headers);
     if (!cookies["sessionToken"]) {
       context.state.isAuthenticated = false;
+      context.state.isFromPolytech = false;
       return await context.next();
     }
 
@@ -67,9 +78,32 @@ export const handler: MiddlewareHandler<State>[] = [
     );
 
     if (context.state.isAuthenticated) {
-      const session: CasContent =
+      const session: CasContent = (
-        (getJwtPayload(cookies["sessionToken"]) as LoginJWT).user;
+        getJwtPayload(cookies["sessionToken"]) as LoginJWT
-      context.state.session = session;
+      ).user;
+
+      const isFromPolytech = Object.values(session.memberOf).some(
+        (value) =>
+          typeof value === "string" && value.includes("cn=amu:ufr:polytech"),
+      );
+
+      context.state.isFromPolytech = isFromPolytech;
+
+      if (isFromPolytech) {
+        context.state.session = session;
+
+        if (Object.values(session.memberOf).some(
+          (value) => typeof value === "string" && value.includes("cn=amu:ufr:polytech:personnels")
+        )) {
+          context.state.role = "personnels";
+        } else if (Object.values(session.memberOf).some(
+          (value) => typeof value === "string" && value.includes("cn=amu:ufr:polytech:etudiants")
+        )) {
+          context.state.role = "etudiants";
+        } else {
+          context.state.role = "autres";
+        }
+      }
     }
 
     return await context.next();
@@ -87,13 +121,30 @@ export const handler: MiddlewareHandler<State>[] = [
   ): Promise<Response> {
     const url = new URL(request.url);
 
-    if (!isRoutePublic(url.pathname) && !context.state.isAuthenticated) {
+    if (!isRoutePublic(url.pathname)) {
-      return new Response(null, {
+      if (!context.state.isAuthenticated) {
-        status: 302,
+        return new Response(null, {
-        headers: {
+          status: 302,
-          Location: "/login",
+          headers: {
-        },
+            Location: "/login",
-      });
+          },
+        });
+      }
+
+      if (!context.state.isFromPolytech) {
+        return new Response(null, {
+          status: 403,
+          headers: {
+            Location: "/403",
+          },
+        });
+      }
+
+      if (isRouteAnAPI(url.pathname) && !(context.state.role == "personnels")) {
+        return new Response(null, {
+          status: 403,
+        });
+      }
     }
 
     return await context.next();