- Add role detection

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

.gitea/workflows/deploy.yml

@@ -1,27 +0,0 @@
-name: "Build and push image"
-
-on:
-  push:
-    branches:
-      - main
-
-jobs:
-  deploy:
-    name: "Build Docker image"
-    runs-on: ubuntu-latest
-    steps:
-      - name: Login to Docker Hub
-        uses: docker/login-action@v3
-        with:
-          registry: registry.docker.polytech.djalim.fr
-          username: ${{ secrets.registry_login }}
-          password: ${{ secrets.registry_pass }}
-
-      - name: Set up Docker Buildx
-        uses: docker/setup-buildx-action@v3
-
-      - name: Build and push
-        uses: docker/build-push-action@v6
-        with:
-          push: true
-          tags: registry.docker.polytech.djalim.fr/polympr:latest

Dockerfile

@@ -3,11 +3,10 @@ FROM denoland/deno:alpine
 WORKDIR /app
 
 COPY . .
-
-RUN deno cache main.ts --allow-import
+RUN deno cache --allow-import main.ts
 RUN deno task build
 
-
+USER deno
 EXPOSE 80
 EXPOSE 443
 

contributing.md

@@ -1,20 +0,0 @@
-# Contributing
-
-Thank you for your interest in contributing to our project! We appreciate your
-help in making this project better. To get started with contributing, please
-refer to our
-[Contributing Guide](https://github.com/fedyna-k/PolyMPR/wiki/Contributing) on
-the project's wiki.
-
-The Contributing Guide provides detailed information on how to:
-
-- Set up your development environment
-- Submit issues and feature requests
-- Fork the repository and create pull requests
-- Follow our coding standards and guidelines
-- Report bugs and suggest improvements
-
-If you have any questions or need further assistance, feel free to reach out to
-us by opening an issue or contacting the maintainers directly.
-
-Happy coding! 💻✨

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
 );

databases/init/students.sql

@@ -1,14 +1,15 @@
 create table promotions (
-  id        integer primary key autoincrement,
-  endyear   integer,
-  current   integer
+  id      integer primary key autoincrement,
+  name    text,
+  endyear integer,
+  current integer
 );
 
 create table students (
-  userId      text  primary key,
-  firstName   text,
-  lastName    text,
-  mail        text,
-  promotionId integer,
+  userId    text  primary key,
+  firstName text,
+  lastName  text,
+  mail      text,
+  promotionId     integer,
   foreign key(promotionId) references promotions(id)
 );

defaults/interfaces.ts

@@ -1,14 +1,16 @@
 import { type RegularTagNode, type TextNode } from "@melvdouc/xml-parser";
 import { AsyncRoute } from "$fresh/src/server/types.ts";
 
-export interface AuthenticatedState {
+interface AuthenticatedState {
   isAuthenticated: true;
+  isFromPolytech: boolean;
+  role: "etudiants" | "personnels" | "autres";
   session: CasContent;
-  availablePages: Record<string, string>;
 }
 
 interface UnauthenticatedState {
   isAuthenticated: false;
+  isFromPolytech: false;
   session: undefined;
 }
 

deno.json

@@ -29,14 +29,12 @@
     "@popov/jwt": "jsr:@popov/jwt@^1.0.1",
     "@psych/sheet": "jsr:@psych/sheet@^1.0.6",
     "@std/cli": "jsr:@std/cli@^1.0.10",
-    "@std/dotenv": "jsr:@std/dotenv@^0.225.3",
     "preact": "https://esm.sh/preact@10.22.0",
     "preact/": "https://esm.sh/preact@10.22.0/",
     "@preact/signals": "https://esm.sh/*@preact/signals@1.2.2",
     "@preact/signals-core": "https://esm.sh/*@preact/signals-core@1.5.1",
     "$std/": "https://deno.land/std@0.216.0/",
-    "$root/": "./",
-    "$apps/": "./routes/(apps)/"
+    "$root/": "./"
   },
   "compilerOptions": {
     "jsx": "react-jsx",

env.template

@@ -1,2 +0,0 @@
-#Local mode, set to true to access admin pages with any users
-LOCAL=false

fresh.config.ts

@@ -1,8 +1,6 @@
 import { defineConfig } from "$fresh/server.ts";
 import ensureDatabases from "$root/databases/ensure.ts";
-import { load } from "@std/dotenv";
 
-await load({ envPath: "./.env", export: true });
 await ensureDatabases();
 export default defineConfig({
   server: {

fresh.gen.ts

@@ -4,22 +4,23 @@
 
 import * as $_apps_layout from "./routes/(apps)/_layout.tsx";
 import * as $_apps_mobility_api_download from "./routes/(apps)/mobility/api/download.ts";
-import * as $_apps_mobility_api_download_id_ from "./routes/(apps)/mobility/api/download/[id].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";
 import * as $_apps_notes_partials_notes from "./routes/(apps)/notes/partials/notes.tsx";
-import * as $_apps_students_api_students from "./routes/(apps)/students/api/students.ts";
+import * as $_apps_students_api_insert_students from "./routes/(apps)/students/api/insert_students.ts";
 import * as $_apps_students_index from "./routes/(apps)/students/index.tsx";
 import * as $_apps_students_partials_admin_consult from "./routes/(apps)/students/partials/(admin)/consult.tsx";
 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_types_d from "./routes/(apps)/students/types.d.ts";
+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";
@@ -32,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";
@@ -41,15 +41,8 @@ import type { Manifest } from "$fresh/server.ts";
 const manifest = {
   routes: {
     "./routes/(apps)/_layout.tsx": $_apps_layout,
-<<<<<<< HEAD
-    "./routes/(apps)/_middleware.ts": $_apps_middleware,
-    "./routes/(apps)/mobility/api/insert_mobility.ts":
-=======
     "./routes/(apps)/mobility/api/download.ts": $_apps_mobility_api_download,
-    "./routes/(apps)/mobility/api/download/[id].ts":
-      $_apps_mobility_api_download_id_,
     "./routes/(apps)/mobility/api/insert-mobility.ts":
->>>>>>> 4f1011d (Ultimate fix and tested ! You can download contract now.)
       $_apps_mobility_api_insert_mobility,
     "./routes/(apps)/mobility/index.tsx": $_apps_mobility_index,
     "./routes/(apps)/mobility/partials/(admin)/edit_mobility.tsx":
@@ -58,12 +51,14 @@ 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,
     "./routes/(apps)/notes/partials/index.tsx": $_apps_notes_partials_index,
     "./routes/(apps)/notes/partials/notes.tsx": $_apps_notes_partials_notes,
-    "./routes/(apps)/students/api/students.ts": $_apps_students_api_students,
+    "./routes/(apps)/students/api/insert_students.ts":
+      $_apps_students_api_insert_students,
     "./routes/(apps)/students/index.tsx": $_apps_students_index,
     "./routes/(apps)/students/partials/(admin)/consult.tsx":
       $_apps_students_partials_admin_consult,
@@ -71,7 +66,9 @@ const manifest = {
       $_apps_students_partials_admin_upload,
     "./routes/(apps)/students/partials/index.tsx":
       $_apps_students_partials_index,
-    "./routes/(apps)/students/types.d.ts": $_apps_students_types_d,
+    "./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,
@@ -88,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":

license

@@ -1,18 +0,0 @@
-Copyright 2025 - PolyMPR team @ Polytech Marseille
-
-Permission is hereby granted, free of charge, to any person obtaining a copy of
-this software and associated documentation files (the “Software”), to deal in
-the Software without restriction, including without limitation the rights to
-use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of
-the Software, and to permit persons to whom the Software is furnished to do so,
-subject to the following conditions:
-
-The above copyright notice and this permission notice shall be included in all
-copies or substantial portions of the Software.
-
-THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
-IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS
-FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR
-COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
-IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
-CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

readme.md

@@ -1,89 +1,4 @@
 # ✨ PolyMPR ✨
 
-**PolyMPR** (Poly Management Platform for Resources) is a modern, modular
-framework built on **Deno** and **Fresh**, designed to help organizations
-transition their HR systems to the cloud. With its **modulith architecture**,
-PolyMPR simplifies the development, deployment, and maintenance of HR
-applications, making it the perfect choice for teams looking to modernize their
-workflows. 🌐
-
-## Features ✨
-
-- **Modular Design**: Easily add, remove, or update features without disrupting
-  the entire system. 🧩
-- **Cloud-Native**: Built for the cloud, enabling seamless integration with
-  cloud services (amU DataCenter). ☁️
-- **Deno-Powered**: Utilizes Deno's secure runtime for TypeScript. 🦕
-- **Fresh Framework**: Delivers fast, edge-ready web applications with minimal
-  overhead. ⚡
-- **HR-Focused**: Tailored to meet the unique needs of INFO's HR. 👩‍💼👨‍💼
-
-## Getting Started 🛠️
-
-### Prerequisites
-
-- **Deno**: Install Deno by following the
-  [official guide](https://deno.land/#installation).
-- **Docker** (optional): Install Docker for containerized deployments. Follow
-  the [Docker installation guide](https://docs.docker.com/get-docker/).
-
-### Installation
-
-1. Clone the PolyMPR repository:
-   ```bash
-   git clone https://github.com/fedyna-k/PolyMPR.git
-   cd PolyMPR
-   ```
-2. Start the application:
-   ```bash
-   deno task start
-   ```
-3. Access the application at `https://localhost`.
-
-For detailed installation instructions, check out the
-[Installation Guide](./wiki/installation).
-
-## Modules Overview 🧩
-
-PolyMPR comes with a variety of modules to streamline HR processes.
-
-To learn how to create a module, visit the [Module Overview](./wiki/modules).
-
-## CLI Documentation 📄
-
-The **PolyMPR CLI** simplifies development tasks. Here are some common commands:
-
-- Create a new module:
-  ```bash
-  pmpr module create <module-name-kebab-case>
-  ```
-
-For detailed CLI usage, check out the [CLI Documentation](./wiki/cli).
-
-## Contributing 🤝
-
-We welcome contributions from the community! Whether you're fixing bugs, adding
-features, or improving documentation, your help is appreciated. Here’s how to
-get started:
-
-1. Create a new issue.
-2. Create a new branch for your changes:
-   ```bash
-   git checkout -b PMPR-:ISSUE_ID:
-   ```
-3. Commit your changes and push them to your branch.
-4. Submit a pull request.
-
-For more details, read the [Contributing Guide](./contributing).
-
-## Community and Support 🌟
-
-Join the PolyMPR community to connect with other users and developers:
-
-- **GitHub Discussions**: Ask questions and share ideas. 💬
-- **Issue Tracker**: Report bugs or request features. 🐛
-
-## License 📜
-
-PolyMPR is open-source and released under the **MIT License**. Feel free to use,
-modify, and distribute it as per the license terms.
+The ✨ Poly Module de Pilotage des Ressources ✨ is the ultimate tool to handle
+various HR task in the INFO department.

routes/(apps)/_layout.tsx

@@ -1,19 +1,22 @@
 import { FreshContext } from "$fresh/server.ts";
 import { Partial } from "$fresh/runtime.ts";
-import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { State } from "$root/defaults/interfaces.ts";
+import { AppProperties } from "$root/defaults/interfaces.ts";
 import Navbar from "$root/routes/(_islands)/Navbar.tsx";
 
-// deno-lint-ignore require-await
 export default async function AppLayout(
   request: Request,
-  context: FreshContext<AuthenticatedState>,
+  context: FreshContext<State>,
 ) {
   const pathname = new URL(request.url).pathname;
   const currentApp = pathname.split("/")[1];
+  const properties: AppProperties = (await import(
+    `./${currentApp}/(_props)/props.ts`
+  )).default;
 
   return (
     <section id="app">
-      <Navbar currentApp={currentApp} pages={context.state.availablePages} />
+      <Navbar currentApp={currentApp} pages={properties.pages} />
       <section id="app-body">
         <Partial name="body">
           <context.Component />

routes/(apps)/_middleware.ts

@@ -1,36 +0,0 @@
-import { FreshContext, MiddlewareHandler } from "$fresh/server.ts";
-import {
-  AppProperties,
-  AuthenticatedState,
-} from "$root/defaults/interfaces.ts";
-
-export const handler: MiddlewareHandler<AuthenticatedState>[] = [
-  /**
-   * Get all available pages for current user.
-   * @param request The HTTP incomming request.
-   * @param context The Fresh context object with custom `AuthenticatedState`.
-   * @returns The response from the next middleware.
-   */
-  async function getAllAvailablePages(
-    request: Request,
-    context: FreshContext<AuthenticatedState>,
-  ): Promise<Response> {
-    const pathname = new URL(request.url).pathname;
-    const currentApp = pathname.split("/")[1];
-    const properties: AppProperties = (await import(
-      `./${currentApp}/(_props)/props.ts`
-    )).default;
-
-    context.state.availablePages = properties.pages;
-    if (
-      context.state.session.eduPersonPrimaryAffiliation == "student" &&
-      Deno.env.get("LOCAL") != "true"
-    ) {
-      properties.adminOnly.forEach((page) =>
-        delete context.state.availablePages[page]
-      );
-    }
-
-    return await context.next();
-  },
-];

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<
-    | {
-      promotions?: Promotion[];
-      students?: Student[];
-      mobilities?: Mobility[];
-    }
-    | null
-  >(null);
+  const [mobilityData, setMobilityData] = useState<MobilityData[]>([]);
+  const [promotions, setPromotions] = useState<Promotion[]>([]);
+  const [selectedPromotion, setSelectedPromotion] = useState<number | "all">("all");
   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: API response status:", response.status);
-
+        console.log("ConsultMobility: Fetching data from API...");
+        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) {
-    return <p className="error">{error}</p>;
-  }
+  const filteredData =
+    selectedPromotion === "all"
+      ? mobilityData
+      : mobilityData.filter((entry) => entry.promotionId === selectedPromotion);
 
-  if (!data?.promotions) {
-    return <p>No promotions found.</p>;
-  }
+  const downloadFile = (id: number | null) => {
+    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>
-          <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
-                  );
-                  return (
-                    <tr key={student.id}>
-                      <td>{student.id}</td>
-                      <td>{student.firstName}</td>
-                      <td>{student.lastName}</td>
-                      <td>{mobility?.startDate || "N/A"}</td>
-                      <td>{mobility?.endDate || "N/A"}</td>
-                      <td>{mobility?.weeksCount ?? "N/A"}</td>
-                      <td>{mobility?.destinationCountry || "N/A"}</td>
-                      <td>{mobility?.destinationName || "N/A"}</td>
-                      <td>{mobility?.mobilityStatus || "N/A"}</td>
-                    </tr>
-                  );
-                })}
-            </tbody>
-          </table>
+          {selectedPromotion === "all" || selectedPromotion === 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>
+                    <th>Attestation File</th>
+                  </tr>
+                </thead>
+                <tbody>
+                  {filteredData
+                    .filter((entry) => entry.promotionId === promo.id)
+                    .map((entry) => (
+                      <tr key={entry.studentId}>
+                        <td>{entry.studentId}</td>
+                        <td>{entry.firstName}</td>
+                        <td>{entry.lastName}</td>
+                        <td>{entry.startDate || "N/A"}</td>
+                        <td>{entry.endDate || "N/A"}</td>
+                        <td>{entry.weeksCount || "0"}</td>
+                        <td>{entry.destinationCountry || "N/A"}</td>
+                        <td>{entry.destinationName || "N/A"}</td>
+                        <td>{entry.mobilityStatus}</td>
+                        <td>
+                          {entry.attestationFile ? (
+                            <button
+                              onClick={() => downloadFile(entry.id)}
+                            >
+                              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<
-    | {
-      promotions?: Promotion[];
-      students?: Student[];
-      mobilities?: Mobility[];
-    }
-    | null
-  >(null);
-  const [error, setError] = useState<string | null>(null);
+  const [mobilityData, setMobilityData] = useState<MobilityData[]>([]);
+  const [promotions, setPromotions] = useState<Promotion[]>([]);
+  const [selectedPromotion, setSelectedPromotion] = useState<number | "all">("all");
   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");
-        console.log("EditMobility: API response status:", response.status);
+      const response = await fetch("/mobility/api/insert-mobility");
+      const data = await response.json();
+      console.log("EditMobility: Data fetched successfully:", data);
 
-        if (!response.ok) {
-          throw new Error(`Error fetching data: ${response.statusText}`);
-        }
+      setPromotions(data.promotions);
 
-        const result = await response.json();
-        console.log("EditMobility: Data fetched successfully:", result);
-        setData(result);
-      } catch (err) {
-        console.error("EditMobility: Error fetching data:", err);
-        setError("Failed to load mobility data. Please try again later.");
-      }
-    };
+      const initializedData = data.students.map((student: any) => {
+        const existingMobility = data.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",
+          attestationFile: existingMobility?.attestationFile || null,
+          promotionId: student.promotionId,
+          promotionName: student.promotionName,
+        };
+      });
+      setMobilityData(initializedData);
+    }
 
-    fetchData();
+    fetchMobilityData();
   }, []);
 
-  const handleChange = (
-    studentId: string,
-    field: keyof Mobility,
-    value: string | number | null,
-  ) => {
-    if (!data) return;
+  const handleFileChange = (studentId: string, file: File | null) => {
+    if (file && file.type !== "application/pdf") {
+      alert("Only PDF files are allowed.");
+      return;
+    }
 
-    setData((prevData) => {
-      if (!prevData) return null;
-
-      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 };
-    });
+    setMobilityData((prev) =>
+      prev.map((entry) =>
+        entry.studentId === studentId ? { ...entry, attestationFile: file } : entry
+      )
+    );
   };
 
   const handleSave = async () => {
     setIsSaving(true);
 
     try {
-      const response = await fetch("/mobility/api/insert_mobility", {
-        method: "POST",
-        headers: { "Content-Type": "application/json" },
-        body: JSON.stringify({ data: data?.mobilities }),
+      console.log("EditMobility: Sending data to API...");
+
+      const formData = new FormData();
+
+      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) {
-    return <p className="error">{error}</p>;
-  }
+  const filteredData =
+    selectedPromotion === "all"
+      ? mobilityData
+      : mobilityData.filter((entry) => entry.promotionId === selectedPromotion);
 
-  if (!data?.promotions) {
-    return <p>Loading data...</p>;
-  }
+  const groupedData = promotions.map((promo) => ({
+    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 (
-                    <tr key={student.id}>
-                      <td>{student.id}</td>
-                      <td>{student.firstName}</td>
-                      <td>{student.lastName}</td>
+      <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>
+
+      {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(
-                              student.id,
-                              "startDate",
-                              e.target.value,
-                            )}
+                            setMobilityData((prev) =>
+                              prev.map((data) =>
+                                data.studentId === entry.studentId
+                                  ? { ...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(
-                              student.id,
-                              "destinationCountry",
-                              e.target.value,
-                            )}
+                            setMobilityData((prev) =>
+                              prev.map((data) =>
+                                data.studentId === entry.studentId
+                                  ? { ...data, destinationCountry: e.target.value }
+                                  : data
+                              )
+                            )
+                          }
                         />
                       </td>
                       <td>
                         <input
                           type="text"
-                          value={mobility.destinationName || ""}
+                          value={entry.destinationName || ""}
                           onChange={(e) =>
-                            handleChange(
-                              student.id,
-                              "destinationName",
-                              e.target.value,
-                            )}
+                            setMobilityData((prev) =>
+                              prev.map((data) =>
+                                data.studentId === entry.studentId
+                                  ? { ...data, destinationName: e.target.value }
+                                  : data
+                              )
+                            )
+                          }
                         />
                       </td>
                       <td>
                         <select
-                          value={mobility.mobilityStatus}
+                          value={entry.mobilityStatus}
                           onChange={(e) =>
-                            handleChange(
-                              student.id,
-                              "mobilityStatus",
-                              e.target.value,
-                            )}
+                            setMobilityData((prev) =>
+                              prev.map((data) =>
+                                data.studentId === entry.studentId
+                                  ? { ...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>
-          </table>
+                  ))}
+                </tbody>
+              </table>
+            </>
+          )}
         </div>
       ))}
-      <button type="button" onClick={handleSave} disabled={isSaving}>
+
+      <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

@@ -2,18 +2,23 @@ import { Handlers } from "$fresh/server.ts";
 import connect from "$root/databases/connect.ts";
 
 export const handler: Handlers = {
-  async GET(_request, ctx) {
+  async GET(request) {
     try {
-      const { id } = ctx.params;
+      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 = ?",
+        `SELECT attestationFile FROM mobility WHERE id = ?`
       );
       const result = query.get(id);
 
@@ -21,7 +26,9 @@ export const handler: Handlers = {
         return new Response("No file found for the given ID", { status: 404 });
       }
 
-      return new Response(result.attestationFile, {
+      const fileBuffer = result.attestationFile;
+
+      return new Response(fileBuffer, {
         status: 200,
         headers: {
           "Content-Type": "application/pdf",

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

@@ -80,7 +80,7 @@ export const handler: Handlers = {
           destinationCountry = excluded.destinationCountry,
           destinationName = excluded.destinationName,
           mobilityStatus = excluded.mobilityStatus,
-          attestationFile = COALESCE(excluded.attestationFile, mobility.attestationFile)`
+          attestationFile = excluded.attestationFile`
       );
 
       for (const mobility of dataEntries) {
@@ -105,7 +105,7 @@ export const handler: Handlers = {
             calculatedWeeksCount = Math.floor(differenceInDays / 7);
           }
         }
-        const attestationFile = fileMap[studentId] ?? null;
+        const attestationFile = fileMap[studentId] || null;
 
         console.log(`Inserting/Updating mobility for studentId: ${studentId}`);
         insertQuery.run(

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/(_components)/Promotion.tsx

@@ -1,30 +0,0 @@
-import Student from "$root/routes/(apps)/students/(_components)/Student.tsx";
-
-type PromotionProps = { students: Student[]; promo: Promotion };
-
-export default function Promotion(props: PromotionProps) {
-  if (!props.promo) {
-    return <p>Unable to find user in database.</p>;
-  }
-
-  return (
-    <div key={props.promo.id}>
-      <h3>Promotion {props.promo.endyear}</h3>
-      <table>
-        <thead>
-          <tr>
-            <th>ID</th>
-            <th>First Name</th>
-            <th>Last Name</th>
-            <th>Email</th>
-          </tr>
-        </thead>
-        <tbody>
-          {props.students
-            .filter((student) => student.promotionId === props.promo.id)
-            .map((student) => <Student key={student.id} student={student} />)}
-        </tbody>
-      </table>
-    </div>
-  );
-}

routes/(apps)/students/(_components)/SelfPortrait.tsx

@@ -1,31 +0,0 @@
-import { CasContent } from "$root/defaults/interfaces.ts";
-
-type SelfPortraitProps = { self: CasContent };
-
-const regex =
-  /^(?<year>\d{4})(?<month>\d{2})(?<date>\d{2})(?<hours>\d{2})(?<minutes>\d{2})(?<seconds>\d{2})Z$/;
-
-export default function SelfPortrait(props: SelfPortraitProps) {
-  const { year, month, date, hours, minutes, seconds } = props.self
-    .amuDateValidation.match(regex)!.groups!;
-
-  const validationIsoDate =
-    `${year}-${month}-${date}T${hours}:${minutes}:${seconds}Z`;
-
-  const validationDate = new Date(validationIsoDate);
-
-  return (
-    <div id="self-portrait">
-      <div>Identity</div>
-      <div>{props.self.supannCivilite} {props.self.displayName}</div>
-      <div>Student number</div>
-      <div>{props.self.uid}</div>
-      <div>amU mail</div>
-      <div>{props.self.mail}</div>
-      <div>First amU registration</div>
-      <div>{validationDate.toLocaleString()}</div>
-      <div>amU class code</div>
-      <div>{props.self.supannEtuEtape}</div>
-    </div>
-  );
-}

routes/(apps)/students/(_components)/Student.tsx

@@ -1,13 +0,0 @@
-type StudentProps = { student: Student; promo?: number };
-
-export default function Student(props: StudentProps) {
-  return (
-    <tr key={props.student.userId}>
-      <td>{props.student.userId}</td>
-      <td>{props.student.firstName}</td>
-      <td>{props.student.lastName}</td>
-      <td>{props.student.mail}</td>
-      {props.promo && <td>{props.promo}</td>}
-    </tr>
-  );
-}

routes/(apps)/students/(_islands)/ConsultStudents.tsx

@@ -1,45 +1,75 @@
 import { useEffect, useState } from "preact/hooks";
-import Promotion from "$root/routes/(apps)/students/(_components)/Promotion.tsx";
 
-type SingleUserResponse = { promo: Promotion; student: Student };
-type ManyUsersResponse = { promos: Promotion[]; students: Student[] };
+interface Promotion {
+  id: number;
+  name: string;
+}
 
-type APIResponse = SingleUserResponse | ManyUsersResponse;
+interface Student {
+  userId: string;
+  firstName: string;
+  lastName: string;
+  mail: string;
+  promotionId: number;
+}
 
 export default function ConsultStudents() {
-  const [data, setData] = useState<APIResponse | null>(null);
+  const [data, setData] = useState<
+    { promotions: Promotion[]; students: Student[] } | null
+  >(null);
   const [error, setError] = useState<string | null>(null);
 
   useEffect(() => {
     const fetchData = async () => {
-      const response = await fetch("/students/api/students");
-      if (!response.ok) {
+      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();
+        console.log("Fetched data:", result);
+        setData(result);
+      } catch (err) {
+        console.error("Error fetching data:", err);
         setError("Failed to load data. Please try again later.");
       }
-
-      const result: APIResponse = await response.json();
-      setData(result);
     };
 
     fetchData();
   }, []);
 
   return (
-    <>
+    <section>
+      <h2>Consult Students</h2>
       {error && <p className="error">{error}</p>}
-      {data && ((Object.hasOwn(data, "student"))
-        ? (
-          <Promotion
-            students={[(data as SingleUserResponse).student]}
-            promo={(data as SingleUserResponse).promo}
-          />
-        )
-        : (data as ManyUsersResponse).promos.map((promo) => (
-          <Promotion
-            students={(data as ManyUsersResponse).students}
-            promo={promo}
-          />
-        )))}
-    </>
+      {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>Email</th>
+              </tr>
+            </thead>
+            <tbody>
+              {data.students
+                .filter((student) => student.promotionId === promo.id)
+                .map((student) => (
+                  <tr key={student.userId}>
+                    <td>{student.userId}</td>
+                    <td>{student.firstName}</td>
+                    <td>{student.lastName}</td>
+                    <td>{student.mail}</td>
+                  </tr>
+                ))}
+            </tbody>
+          </table>
+        </div>
+      ))}
+    </section>
   );
 }

routes/(apps)/students/(_islands)/UploadStudents.tsx

@@ -1,111 +1,75 @@
 // @deno-types="https://cdn.sheetjs.com/xlsx-0.20.3/package/types/index.d.ts"
 import * as XLSX from "https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs";
-import { Signal, useSignal } from "@preact/signals";
-
-/**
- * Create a new handler for file change that displays
- * messages in statusMessage and gets file data in fileData.
- * @param statusMessage The status message signal.
- * @param fileData The file data signal.
- * @returns The file change handler.
- */
-function getFileChangeHandler(
-  statusMessage: Signal<string>,
-  fileData: Signal<File | null>,
-): (event: Event) => void {
-  /**
-   * Handle file change.
-   * @param event The file change event.
-   */
-  return (event: Event) => {
-    const input = event.target as HTMLInputElement;
-    if (input.files && input.files.length > 0) {
-      fileData.value = input.files[0];
-      statusMessage.value = `File selected: ${input.files[0].name}`;
-    } else {
-      fileData.value = null;
-      statusMessage.value = "No file selected";
-    }
-  };
-}
-
-/**
- * Create a new handler that sends data file to server.
- * @param statusMessage The status message signal.
- * @param fileData The file data signal.
- * @returns The file confirmation handler.
- */
-function getUploadConfirmationFunction(
-  statusMessage: Signal<string>,
-  fileData: Signal<File | null>,
-): () => void {
-  /**
-   * Add students to database.
-   * @returns Confirm upload of students.
-   */
-  return () => {
-    if (!fileData.value) {
-      statusMessage.value = "Please select a file before confirming upload.";
-      return;
-    }
-
-    const reader = new FileReader();
-
-    /**
-     * Send all data to the server.
-     * @param event The finished progress event.
-     */
-    reader.onload = async (event: ProgressEvent<FileReader>) => {
-      const arrayBuffer = event.target!.result as ArrayBuffer;
-      const workbook = XLSX.read(arrayBuffer, { type: "array" });
-      let allOK = true;
-
-      for (const sheetName of workbook.SheetNames) {
-        const sheet = workbook.Sheets[sheetName];
-        const data = XLSX.utils.sheet_to_json(sheet, {
-          header: ["userId", "lastName", "firstName", "mail"],
-          range: 1,
-        });
-
-        const response = await fetch("/students/api/students", {
-          method: "POST",
-          headers: { "Content-Type": "application/json" },
-          body: JSON.stringify({ promoName: sheetName, data }),
-        });
-
-        if (!response.ok) {
-          allOK = false;
-        }
-      }
-
-      statusMessage.value = allOK
-        ? "Failed to insert all data."
-        : "Data uploaded and inserted successfully!";
-    };
-
-    /**
-     * Display error message if any.
-     */
-    reader.onerror = () => {
-      statusMessage.value = "Error reading the file.";
-    };
-
-    reader.readAsArrayBuffer(fileData.value);
-  };
-}
+import { useSignal } from "@preact/signals";
 
 export default function UploadStudents() {
   const statusMessage = useSignal<string>("");
   const fileData = useSignal<File | null>(null);
 
-  const handleFileChange = getFileChangeHandler(statusMessage, fileData);
-  const confirmUpload = getUploadConfirmationFunction(statusMessage, fileData);
+  const handleFileChange = (event: Event) => {
+    const input = event.target as HTMLInputElement;
+    if (input.files && input.files.length > 0) {
+      fileData.value = input.files[0];
+      statusMessage.value = "File selected: " + input.files[0].name;
+    } else {
+      fileData.value = null;
+      statusMessage.value = "No file selected";
+    }
+  };
+
+  const confirmUpload = () => {
+    if (!fileData.value) {
+      statusMessage.value = "Please select a file before confirming upload.";
+      return;
+    }
+
+    try {
+      const reader = new FileReader();
+
+      reader.onload = async (e) => {
+        const arrayBuffer = e.target?.result as ArrayBuffer;
+        const workbook = XLSX.read(arrayBuffer, { type: "array" });
+
+        for (const sheetName of workbook.SheetNames) {
+          const sheet = workbook.Sheets[sheetName];
+          const data = XLSX.utils.sheet_to_json(sheet, {
+            header: ["Identifiant", "Nom", "Prénom", "Mail"],
+            range: 1, // Ignorer les en-têtes
+          });
+
+          console.log(`Data from sheet ${sheetName}:`, data);
+
+          const response = await fetch("/students/api/insert_students", {
+            method: "POST",
+            headers: { "Content-Type": "application/json" },
+            body: JSON.stringify({ promoName: sheetName, data }),
+          });
+
+          if (!response.ok) {
+            throw new Error(`Failed to insert data for promotion ${sheetName}`);
+          }
+        }
+
+        statusMessage.value = "Data uploaded and inserted successfully!";
+      };
+
+      reader.onerror = () => {
+        statusMessage.value = "Error reading the file.";
+      };
+
+      reader.readAsArrayBuffer(fileData.value);
+    } catch (error) {
+      console.error("Error uploading file:", error);
+      statusMessage.value = "An unexpected error occurred during upload.";
+    }
+  };
 
   return (
-    <>
+    <div>
+      <h2>Upload Students</h2>
       <input type="file" accept=".xlsx, .xls" onChange={handleFileChange} />
-      <button type="button" onClick={confirmUpload}>Confirm Upload</button>
+      <button onClick={confirmUpload}>Confirm Upload</button>
       <p>{statusMessage.value}</p>
-    </>
+    </div>
   );
 }

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

@@ -5,6 +5,7 @@ const properties: AppProperties = {
   icon: "badge",
   pages: {
     index: "Homepage",
+    overview: "Students overview",
     upload: "Upload students",
     consult: "Consult students",
   },

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

@@ -1,7 +1,6 @@
 import { Handlers } from "$fresh/server.ts";
 import connect from "$root/databases/connect.ts";
 
-// Rendre l'API plus simple car xlsx pour l'import c'est nul :/
 export const handler: Handlers = {
   // deno-lint-ignore require-await
   async GET() {

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

@@ -1,151 +0,0 @@
-import { FreshContext, Handlers } from "$fresh/server.ts";
-import connect from "$root/databases/connect.ts";
-import { AuthenticatedState } from "$root/defaults/interfaces.ts";
-import { Database } from "@db/sqlite";
-
-/**
- * Gets itself from the database.
- * @param database The database connection
- * @param userId The user ID.
- * @returns Itself from the database.
- */
-function getItself(
-  database: Database,
-  userId: string,
-): { student: Student | null; promo: Promotion | null } {
-  const studentQuery = "select * from students where userId = ?";
-  const student: Student | undefined = database.prepare(studentQuery).get(
-    userId,
-  );
-
-  if (!student) {
-    return { student: null, promo: null };
-  }
-
-  const promoQuery = "select * from promotions where id = ?";
-  const promo: Promotion | undefined = database.prepare(promoQuery).get(
-    student.promotionId,
-  );
-
-  return { student, promo: promo ?? null };
-}
-
-/**
- * Gets itself from the database.
- * @param database The database connexion
- * @param userId The user ID.
- * @returns Itself from the database.
- */
-function getAll(
-  database: Database,
-): { students: Student[]; promos: Promotion[] } {
-  const studentsQuery = `
-    select userId, firstName, lastName, mail, promotionId
-    from students inner join promotions
-    on students.promotionId = promotions.id
-    where promotions.current < 6`;
-  const students: Student[] = database.prepare(studentsQuery).all();
-
-  const promosQuery = "select * from promotions where promotions.current < 6";
-  const promos: Promotion[] | undefined = database.prepare(promosQuery).all();
-
-  return { students, promos };
-}
-
-/**
- * Add users to the database.
- * @param database The database connexion
- * @param students The students to add
- * @param promoId The promotion id.
- */
-function addStudents(database: Database, students: Student[], promoId: string) {
-  const query = `
-    INSERT INTO students 
-    (userId, firstName, lastName, mail, promotionId) 
-    VALUES (?, ?, ?, ?, ?)`;
-
-  const statement = database.prepare(query);
-
-  for (const student of students) {
-    statement.run(
-      student.userId,
-      student.firstName,
-      student.lastName,
-      student.mail,
-      promoId,
-    );
-  }
-}
-
-export const handler: Handlers<null, AuthenticatedState> = {
-  /**
-   * The students the user can see.
-   * @param _request The HTTP request.
-   * @param _context The context with authenticated state.
-   * @returns All students our user can see.
-   */
-  // deno-lint-ignore require-await
-  async GET(
-    _request: Request,
-    context: FreshContext<AuthenticatedState>,
-  ): Promise<Response> {
-    using connection = connect("students");
-    const database = connection.database;
-
-    if (context.state.session.eduPersonPrimaryAffiliation == "student") {
-      return new Response(
-        JSON.stringify(getItself(database, context.state.session.uid)),
-        {
-          headers: {
-            "content-type": "application/json",
-          },
-        },
-      );
-    }
-
-    return new Response(
-      JSON.stringify(getAll(database)),
-      {
-        headers: {
-          "content-type": "application/json",
-        },
-      },
-    );
-  },
-  /**
-   * Add students in the database.
-   * @param request The HTTP request.
-   * @param _context The Fresh context.
-   * @returns HTTP 201 on successful insert.
-   */
-  async POST(
-    request: Request,
-    _context: FreshContext<AuthenticatedState>,
-  ): Promise<Response> {
-    const { students, promo }: { students: Student[]; promo: string } =
-      await request.json();
-
-    if (!promo || !promo.match(/^\d{4}-\dA$/) || !Array.isArray(students)) {
-      return new Response(null, { status: 400 });
-    }
-
-    using connection = connect("students");
-    const database = connection.database;
-
-    const { endyear, current } = promo.match(
-      /^(?<endyear>\d{4})-(?<current>\d)A$/,
-    )?.groups!;
-
-    database.prepare(
-      "insert or ignore into promotions (endyear, current) values (?, ?)",
-    ).run(endyear, current);
-
-    const { id: promoId }: { id: string } = database
-      .prepare("select id from promotions where endyear = ? and current = ?")
-      .get(endyear, current)!;
-
-    addStudents(database, students, promoId);
-
-    return new Response(null, { status: 201 });
-  },
-};

routes/(apps)/students/partials/(admin)/consult.tsx

@@ -1,16 +1,17 @@
-import ConsultStudents from "../../(_islands)/ConsultStudents.tsx";
+import ConsultStudents from "$root/routes/(apps)/students/(_islands)/ConsultStudents.tsx";
 import {
   getPartialsConfig,
   makePartials,
 } from "$root/defaults/makePartials.tsx";
 import { FreshContext } from "$fresh/server.ts";
-import { State } from "$root/defaults/interfaces.ts";
+import { State } from "$root/routes/_middleware.ts";
+//import EditStudents from "../(_islands)/EditStudents.tsx";
 
 // deno-lint-ignore require-await
 async function Students(_request: Request, _context: FreshContext<State>) {
   return (
     <>
-      <h2>Consult students</h2>
+      <h1>Manage Promotions</h1>
       <ConsultStudents />
     </>
   );

routes/(apps)/students/partials/(admin)/upload.tsx

@@ -1,16 +1,17 @@
-import UploadStudents from "../../(_islands)/UploadStudents.tsx";
+import UploadStudents from "$root/routes/(apps)/students/(_islands)/UploadStudents.tsx";
 import {
   getPartialsConfig,
   makePartials,
 } from "$root/defaults/makePartials.tsx";
 import { FreshContext } from "$fresh/server.ts";
-import { State } from "$root/defaults/interfaces.ts";
+import { State } from "$root/routes/_middleware.ts";
+//import EditStudents from "../(_islands)/EditStudents.tsx";
 
 // deno-lint-ignore require-await
 async function Students(_request: Request, _context: FreshContext<State>) {
   return (
     <>
-      <h2>Upload Students</h2>
+      <h1>Manage Promotions</h1>
       <UploadStudents />
     </>
   );

routes/(apps)/students/partials/index.tsx

@@ -3,18 +3,11 @@ import {
   makePartials,
 } from "$root/defaults/makePartials.tsx";
 import { FreshContext } from "$fresh/server.ts";
-import { State } from "$root/defaults/interfaces.ts";
-import SelfPortrait from "$root/routes/(apps)/students/(_components)/SelfPortrait.tsx";
+import { State } from "$root/routes/_middleware.ts";
 
 // deno-lint-ignore require-await
 export async function Index(_request: Request, context: FreshContext<State>) {
-  return (
-    <>
-      <h2>Welcome {context.state.session?.givenName}!</h2>
-      <h3>Your amU identity</h3>
-      <SelfPortrait self={context.state.session!} />
-    </>
-  );
+  return <h2>Welcome to {context.state.session?.displayName}.</h2>;
 }
 
 export const config = getPartialsConfig();

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

@@ -0,0 +1,17 @@
+import { Partial } from "$fresh/runtime.ts";
+import { RouteConfig } from "$fresh/server.ts";
+
+type ModulesProps = Record<string | number | symbol, never>;
+
+export const config: RouteConfig = {
+  skipAppWrapper: true,
+  skipInheritedLayouts: true,
+};
+
+export default function Modules(_props: ModulesProps) {
+  return (
+    <Partial name="body">
+      <a href="students" f-partial={"notes/partials"}>students</a>
+    </Partial>
+  );
+}

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

@@ -1,13 +0,0 @@
-interface Student {
-  userId: string;
-  firstName: string;
-  lastName: string;
-  mail: string;
-  promotionId: number;
-}
-
-interface Promotion {
-  id: number;
-  endyear: number;
-  current: number;
-}

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/_app.tsx

@@ -27,7 +27,6 @@ export default async function App(
         <link rel="stylesheet" href="/styles/main.css" />
         <link rel="stylesheet" href="/styles/app.css" />
         <link rel="stylesheet" href="styles/app-cards.css" />
-        <link rel="stylesheet" href="styles/students.css" />
       </head>
       <body f-client-nav>
         <Header link={link} />

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) ||
-    !!(route.match(/\..+$/)?.[0] ?? false);
+  return (
+    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 =
-        (getJwtPayload(cookies["sessionToken"]) as LoginJWT).user;
-      context.state.session = session;
+      const session: CasContent = (
+        getJwtPayload(cookies["sessionToken"]) as LoginJWT
+      ).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) {
-      return new Response(null, {
-        status: 302,
-        headers: {
-          Location: "/login",
-        },
-      });
+    if (!isRoutePublic(url.pathname)) {
+      if (!context.state.isAuthenticated) {
+        return new Response(null, {
+          status: 302,
+          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();

static/styles/app.css

@@ -3,7 +3,7 @@
   padding: 1em 0;
   display: grid;
   grid-template-columns: auto 1fr;
-  gap: 4em;
+  gap: 1em;
 }
 
 #app > #app-body {
@@ -16,7 +16,7 @@
 }
 
 #app > nav > a {
-  padding: 0.5em 4em 0.5em 1em;
+  padding: 0.25em 0.5em;
   color: light-dark(var(--light-foreground), var(--dark-foreground));
 }
 
@@ -57,10 +57,5 @@
   #app {
     grid-template-rows: auto 1fr;
     grid-template-columns: none;
-    gap: 1em;
-  }
-
-  #app > nav > a {
-    padding: 0.5em 1em;
   }
 }

static/styles/students.css

@@ -1,15 +0,0 @@
-#self-portrait {
-  display: grid;
-  gap: 1em;
-  grid-template-columns: auto 1fr;
-}
-
-#self-portrait > div:nth-child(2n+1) {
-  font-weight: var(--font-weight-bold);
-}
-
-@media screen and (max-width: 1024px) {
-  #self-portrait {
-    grid-template-columns: 1fr;
-  }
-}