1. Préparer le fichier Excel du lot (1 onglet, en-tête en ligne 1, données dès la ligne 2). Le format de chaque colonne est défini par le Format d'entrée du code prestation concerné — voir plus bas la sous-section dynamique « Détail d'un code prestation ».
2. Glisser-déposer le fichier dans la dropzone de la page « Intégrer un lot ». Possibilité de le télécharger via « Définir le dossier par défaut » pour pointer directement au bon endroit la première fois (Chrome/Edge).
3. Pré-validation automatique : détection fiche CEE (colonne N), déduction code prestation (Site > Contact > CVAD), détection PP/PM (colonne « Bénéficiaire PM »), vérification que les dates d'engagement ne franchissent pas de frontière P4/P5/P6, contrôle des colonnes marquées obligatoires dans le Format d'entrée (blocage si une cellule est vide), calcul du taux réglementaire applicable. Si le type de contrôle déduit ne convient pas (ex. un lot de CPC reconnu par défaut comme contrôle sur site, qui bloque sur une colonne obligatoire), l'encart d'erreur propose des boutons « Relancer en … » pour réintégrer en imposant un autre type de contrôle disponible pour la fiche (Sur site / Contrôle par contact CPC / CVAD).
4. Modale principale : choisir le client (filtre par tapping), le mode Zen/Premium, le taux retenu (Réglementaire ou 100%), confirmer Demande / Consignes / CPC / Commentaire. Les dates butoir / clôture terrain / clôture client se recalculent en direct.
   • Lot « Contrôle par contact » (CONTACTCEE) : Taux réglementaires forcé (grisé), Taux retenu = 0 non modifiable, CPC réalisés en interne = Oui forcé (grisé). Demande client et Consignes préremplies « CPC uniquement : X% » (taux CPC réglementaire), modifiables.
   • Lot sur site avec CPC interne = Oui : la Demande client ajoute automatiquement « + CPC : X% » (taux CPC réglementaire) au texte des satisfaisants.
   • Fiche site + contact avec taux site réglementaire = 0 : le contrôle par contact devient le mode réglementaire (sélectionné par défaut) ; « Contrôles sur site (volontaire) » reste cliquable. Basculer entre les deux re-prévalide le fichier avec le code prestation correspondant (format d'entrée/sortie + ref de lot adaptés).
5. Tableur d'édition : modification colonne par colonne (✎ dans l'en-tête de chaque colonne modifiable_preview). En mode édition, un bouton 📋⬇ permet un coller-en-masse : copier une colonne depuis Excel (même nombre de lignes), la coller dans la modale → toutes les cellules de la colonne sont remplacées d'un coup. Bandeau récap en haut. Affichage des warnings/blocages (jaune/rouge) et des doublons potentiels (violet, avec icônes 🗑 et ℹ par ligne).
6. Bouton « Valider et intégrer » : déclenche la génération en parallèle des 4 sorties (cf. ci-dessous) + ouvre la modale d'envoi du mail terrain (validation manuelle).

Le code prestation détermine tout le traitement du lot : format d'entrée (colonnes attendues + colonnes obligatoires), format de sortie (mappings SharePoint), taux réglementaire et référence de lot. Il est choisi en deux temps.

1. Déduction automatique (par défaut)

À l'upload, l'outil déduit le code prestation à partir des modes de contrôle cochés sur la fiche CEE, par ordre de priorité :

1. Contrôle sur site → le code prestation « CS » configuré sur la fiche (ex. DIVERS-INS, CEE-COF-…). Prioritaire dès que « Contrôle sur site » est coché et qu'un code presta CS est renseigné.
2. sinon Contrôle par contact → CONTACTCEE (les CPC).
3. sinon CVAD (contrôle à distance) → CTRL-VISIO.

Si aucun mode n'est actif sur la fiche, l'intégration est refusée (« aucun mode de contrôle activé »).

2. Exception « site volontaire » (bascule auto)

Cas particulier d'une fiche qui a à la fois Sur site et Contact, mais dont le taux site réglementaire = 0 : le mode réglementaire est en réalité le contrôle par contact. L'outil bascule donc automatiquement sur CONTACTCEE par défaut, et la modale propose « Contrôles sur site (volontaire) » si on veut quand même forcer le site. (Voir aussi le comportement de la modale dans la section précédente.)

3. Forçage manuel du code prestation

La déduction auto peut se tromper quand une fiche est cochée sur plusieurs modes. Exemple typique : on intègre des CPC (contrôle par contact) sur une fiche aussi cochée « Sur site » pour des contrôles volontaires occasionnels → l'outil déduit le code presta site (ex. DIVERS-INS) et bloque sur une colonne obligatoire (« Caractéristiques de l'opération » par ex.) qui n'existe pas pour CONTACTCEE.

Deux moyens de forcer le bon code prestation :

• Sur erreur de pré-validation : l'encart rouge affiche des boutons « Relancer en … » (un par mode disponible pour la fiche : Sur site / Contrôle par contact CPC / CVAD). Le clic ré-uploade le fichier en imposant ce mode et relance l'intégration avec le code prestation correspondant.
• Dans la modale principale (cas « site volontaire ») : le lien « Contrôles sur site (volontaire) » re-prévalide avec le code presta site.

Le forçage ne désactive aucun contrôle métier (colonnes obligatoires, périodes, etc.) : il rejoue simplement la pré-validation avec le format d'entrée/sortie du code prestation imposé. Si ce code n'est pas autorisé sur la fiche (mode non coché), le forçage est refusé.

• JSON de commande compressé ({ref_lot}.json.gz) — uploadé sur SharePoint dans Inspections travaux CEE - Documents/Synthèses/Fichiers commandes pour synthèses/. Contient toutes les colonnes non-intermédiaires.
• Excel Kizeo ({ref_lot}.xlsx) — uploadé sur SharePoint dans Inspections travaux CEE - Documents/Fichiers Kizeo/. Contient les colonnes dans_fichier_kizeo=true non-intermédiaires, avec types préservés (number, date au format DD/MM/YYYY).
• Ligne « Liste des commandes » SharePoint — 1 ligne créée via le mapping lots du format de sortie.
• N lignes « Liste des controles » SharePoint — 1 par opération via le mapping opérations. Création en parallèle (max 10 threads).
• Mail à terrain@spekty.fr — éditable dans une modale rich-text avant envoi. Pièce jointe = Excel Kizeo. Expéditeur = pole.email_emetteur de la fiche, Reply-To = user connecté.

• Numéro du jour : compteur incrémental atomique (1..99, A0..Z9 max 359 / jour). Endpoint public /public-api/numero-jour/today utilisé par la macro VBA historique (auth par token Bearer).
• Taux réglementaires — source de vérité partagée : les taux réglementaires ne se saisissent que dans cet outil. Ils sont exposés en lecture seule via l'endpoint public GET /public-api/taux (auth par token API — Authorization: Bearer integ_… ou ?api_token=integ_…), afin que d'autres applications (ex. syntheses-pncee) les récupèrent sans double saisie. La réponse contient generated_at (horodatage UTC), count et taux (même contrat que l'onglet Taux réglementaires). Les applications consommatrices maintiennent un miroir local en lecture seule.
• Pôles — source de vérité partagée : la table des pôles est également exposée en lecture seule via GET /public-api/poles (même auth par token API). Réponse : generated_at (UTC), count et poles (chaque ligne : id, libelle, sort_order, email_emetteur — même contrat que GET /api/poles).
• Fiches CEE — liste simplifiée partagée : exposée en lecture seule via GET /public-api/fiches (même auth par token API), limitée aux colonnes Numéro / Libellé / Pôle. Réponse : generated_at (UTC), count et fiches (chaque ligne : numero, libelle, pole = libellé du pôle ou null). Triée par numero.
• Détection de doublons (asynchrone) : lance en arrière-plan une requête SharePoint après ouverture du tableur. Match exact sur Fiche_CEE + Tel_1 + statut éligible (En cours / Échantillonné / Planifié, ou Terminé créé < 60 j). Affiché en panneau violet, avec corbeille pour retirer la ligne + modale ℹ détaillée.
• Regroupement de lignes (RES-EC-104 uniquement) : bouton ⇧ par ligne pour fusionner avec la ligne au-dessus. Checks d'équalité sur Client/Adresse/CP/Ville/Prénom/Nom/Tel1/Artisan. Confirme le nombre de luminaires de chaque ligne, somme les cadres A, concatène Ref client / Référence Emmy / Référence interne / Volume HP / Volume P / Montant RAI. Le bouton « Valider et intégrer » est verrouillé par une case « J'ai vérifié les regroupements » spécifique à cette fiche.
• RES-EC-104 — split 01/01/2025 : à l'upload, si le lot contient des dates d'engagement strictement antérieures à 01/01/2025 ET ≥ 01/01/2025, l'intégration est bloquée avec le message « Le lot comporte des opérations engagées AVANT et APRES le 01/01/2025. Découper le lot en 2. » (changement de règles d'éligibilité à cette date).
• Découpe automatique par période CEE : si l'upload bloque parce que les dates d'engagement franchissent une frontière P4/P5/P6, un bouton « ✂ Découper automatiquement le fichier » apparaît dans l'encart d'erreur. Il génère 1 sous-fichier par période (en-tête + formatage préservés, lignes filtrées par période), nommés <original>-P5.xlsx, <original>-P6.xlsx, etc. Les fichiers sont enregistrés dans le dossier par défaut configuré (ou téléchargés en fallback sur Firefox/Safari). Le user peut ensuite ré-uploader chaque sous-fichier indépendamment. À côté du bouton « Découper », un bouton « Poursuivre quand même » permet, après confirmation, d'intégrer le lot tel quel sans le découper (le contrôle de frontière de période est alors ignoré pour ce lot uniquement).
• Découpe automatique PP / PM : si l'upload bloque sur un lot mixte (PP + PM dans le même fichier — détecté via la colonne « Bénéficiaire PM » du Format d'entrée : vide=PP, rempli=PM), un bouton « ✂ Découper en PP / PM » génère 2 sous-fichiers <original>-PP.xlsx et <original>-PM.xlsx. Même mécanique d'enregistrement que la découpe par période.
• Vérification contrat client : à l'ouverture de la modale, appel à fiches-contrats-cee.spekty.fr pour confirmer que le client a la fiche dans son contrat cadre. Warning jaune si non couverte.
• Mauvais payeur : si le client a Mauvais_payeur=Oui sur la liste SP des clients, bandeau rouge bloquant et bouton Valider caché. Contacter la direction commerciale.
• Caractéristiques d'opération : si la fiche en a (HTML rich), confirmation au moment de l'upload pour rappeler à l'utilisateur de compléter ces précisions dans le fichier importé. Dans le tableur, si une colonne « Caractéristiques de l'opération » est définie dans le mapping et que la fiche a un modèle, la colonne est mise en évidence en vert WhatsApp (en-tête + cellules), un bouton ☰ dans l'en-tête ouvre une modale affichant le modèle + bouton « Copier le texte » (collable dans une cellule en mode édition, avec textarea pour multi-lignes). Une checkbox verte « J'ai vérifié le champ Caractéristiques de l'opération » verrouille « Valider et intégrer » jusqu'à validation manuelle (similaire à la checkbox rose pour les regroupements RES-EC-104).
• Tableur — passages à la ligne & resize colonne : les cellules respectent les \n explicites du contenu (CHAR(10)) sans wrap automatique sur les longues lignes. Chaque en-tête de colonne a une poignée de redimensionnement à droite (drag) permettant d'élargir une colonne pour des contenus longs (Caractéristiques en particulier).
• Dates affichées en dd/MM/yyyy dans le preview ET dans le fichier Excel Kizeo (détection automatique des valeurs ISO 8601, plus besoin de configurer format_type=date sur la colonne).

La page « Intégrer un lot » affiche en bas un tableau d'historique des lots réellement intégrés (génération SharePoint aboutie), tous utilisateurs confondus, sur les 7 derniers jours (les lignes plus anciennes sont purgées automatiquement). Un lot qui a été annulé avant la fin (blocage, annulation) n'y figure pas.

• Colonnes : Réf. Spekty (référence du lot), Client, Fiche, Réf. client, PP/PM, Délai (Zen/Premium + date de clôture), Lignes (nombre d'opérations), Utilisateur (celui qui a intégré), Date (format relatif « Il y a X min / h / j », date exacte au survol).
• Picto 📧 : ✓ vert si le mail terrain a été envoyé par l'outil pour ce lot (survol « Mail envoyé par l'outil à l'équipe planification »), — sinon.
• Filtre : le champ de recherche filtre en direct sur Réf. Spekty, Client, Fiche, Réf. client et Utilisateur.
• Bouton 📋 par ligne (à côté de la case à cocher) : copie un vrai tableau (Réf. client, Fiche, Réf. Spekty, Date de clôture prévue) collable directement dans un mail Outlook (en-têtes bleus) ou Excel.
• Sélection multiple : cocher plusieurs lignes fait apparaître « 📋 Copier la sélection » — actif uniquement si toutes les lignes cochées concernent le même client.
• Pagination : 25 lignes par page.
• Actualisation : rechargé à chaque arrivée sur la page et après chaque intégration ; bouton ↻ pour forcer.
• « 📄 Visualiser un fichier de commande » (bouton en bas) : saisir un numéro de lot ouvre le JSON de commande généré à l'intégration, affiché en tableau (une ligne par opération, colonnes redimensionnables, dates en jj/mm/aaaa). Le fichier est lu localement s'il est encore présent, sinon téléchargé depuis SharePoint — la visualisation reste donc possible au-delà des 7 jours de l'historique.

Onglet « Commandes R2 » (accessible à tous les utilisateurs) : liste les commandes R2 nouvelles à préparer — celles dont, sur SharePoint, Statut = « Nouveau » et Date de prise en compte est vide.

• Une pastille rouge clignotante apparaît sur l'onglet dès qu'il y a au moins une commande à préparer.
• Colonnes : Client, Nom, Prénom, Adresse, CP, Ville, Type d'opération. Un clic sur une ligne ouvre la modale « Préparation avant contrôle ».
• En-tête (lecture seule) : Nom, Prénom, Client, Adresse, CP/Ville, Fiche (dérivée du type d'opération).
• Encart orange (modifiable) : Société ayant réalisé l'audit, Scénario sélectionné, Réf/URL contrôle R1, case « PM - Personne morale », Montant facturé (entier, à vider en cas de doute).
• Boutons : « Télécharger l'audit », « Télécharger la facture » (ouvrent les PDF SharePoint), « Retrouver rapport R1 » (ouvre welcome.spekty.fr filtré sur le type d'opération).
• Travaux : cases pré-cochées d'après la commande. Certains travaux affichent des champs de détail obligatoires une fois cochés (type de PAC, nb de splits, type de VMC, nb de fenêtres…). Décocher un travail vide ses champs de détail.
• Intégrer automatiquement : met à jour la ligne SharePoint (audit, travaux en toutes lettres dans Travaux, codes dans Travaux_prevus_kizeo, champs de détail), sans toucher au statut ni à la date de prise en compte. Puis déclenche le flux Power Automate d'intégration (webhook) et attend sa fin (réponse 200) avant de rafraîchir la liste. La commande disparaît de la liste une fois que le flux a renseigné la date de prise en compte. Si le flux échoue, l'enregistrement SharePoint reste fait et un avertissement s'affiche.

Détail d'un code prestation (dynamique)

Sélectionne un code prestation pour voir ses fiches rattachées, ses champs d'entrée obligatoires, et l'ensemble de ses conditions de contrôle warning/bloquantes paramétrées.

Liste statique des messages d'erreur

Toutes les erreurs susceptibles d'apparaître à l'utilisateur, extraites automatiquement du code source par le hook pre-commit.

Paramétrage de base

La page Paramétrage affiche en haut un bandeau lecture seule avec le prochain numéro du jour disponible, le dernier réservé et la date courante (bouton ↻ pour rafraîchir). Ce numéro vient de GET /api/numero-jour/today en peek (sans réservation).

• Codes prestation (10 par défaut) : CEE-COF-CP/MU/PA/CB/R1/R2/RG, DIVERS-INS, CONTACTCEE, CTRL-VISIO.
• Pôles (5) : Résidentiel, Industrie & Tertiaire, Mobilité électrique, Rénovation Globale, Excellence opérationnelle. Chaque pôle a un email_emetteur utilisé pour le mail terrain.
• Qualifications (8) : CEE-1ENV → CEE-8TRA + CEE-SPEC.
• Tbase : 6121 codes postaux (températures Tmin/Tmax) seedés depuis docs/seed_excel/ZipCodes.xlsx. Plus 95 départements (zone climatique, info pare-vapeur) seedés depuis docs/seed_excel/Dpt.xlsx.
• Taux réglementaires : 192 lignes seedées depuis docs/seed_excel/Taux.xlsx.

Import Excel des taux réglementaires

Onglet « Taux régl. » → bouton Importer Excel : ouvre une modale avec un bouton « ⬇ Télécharger un modèle Excel » (en-têtes attendus : Fiche, Date début, Date fin, Libellé du taux, Taux site, Taux CPC ; les taux en décimal, ex. 0.125 = 12,5 %) et un sélecteur de fichier.

L'import fonctionne en AJOUT (upsert), pas en remplacement : chaque ligne du fichier est rapprochée de l'existant par la clé (Fiche + Libellé) — si elle existe, elle est mise à jour ; sinon elle est ajoutée. Tous les autres taux déjà en base sont conservés. Le résultat indique « X ajouté(s), Y mis à jour ». (L'import crée des taux en mode standard ; le mode distinct PP/PM se règle à la main via « Modifier ».)

Fiches CEE

Onglet « Fiches CEE » : ajout/édition/suppression des fiches. Chaque fiche définit :

• Toggles Contrôle sur site / Contrôle par contact / CVAD ;
• Sous-section Divers-INS conditionnelle (apparaît si fiche détectée comme telle) avec code presta CS, qualification CS, temps mission, accréditation, points de contrôle JSON ;
• Pôle, libellé, caractéristiques d'opération (éditeur HTML rich) ;
• Toggle pm_majoritaire : déclenche un warning si un lot PP arrive sur une fiche PM-majoritaire.

Format d'entrée

Onglet « Format d'entrée » : 1 format par code prestation (peut couvrir plusieurs codes via groupe d'onglets de l'Excel modèle). Définit :

• Les colonnes attendues (par lettre A, B, …) avec libellé, couleur d'en-tête, type date (oui/non), obligatoire (oui/non) ;
• Le rôle Date d'engagement sur 1 colonne (sert au calcul des taux) ;
• Le rôle Bénéficiaire PM sur 1 colonne (détection PP/PM : vide=PP, rempli=PM, mix bloque).

⚠️ La lecture du fichier d'entrée est positionnelle (par lettre de colonne, jamais par libellé d'en-tête). Les libellés peuvent différer du modèle sans impact.

Format de sortie

Onglet « Format de sortie » : 1 format par code prestation, avec 3 sections :

• Mapping Kizeo : ordre des colonnes du fichier Kizeo + JSON. Chaque colonne a un libellé, un type (text/number/date), une couleur d'en-tête, un flag intermédiaire (gris dans le tableur, non exportée), un flag « modifiable preview » (✎ dans le tableur), une source (champ d'intégration, client SP, fiche, colonne d'entrée par lettre, formule, …).
• Mapping Liste opérations : 1 ligne SP par opération du lot. Source = libellé d'une colonne du mapping Kizeo, ou formule.
• Mapping Liste lots : 1 ligne SP par lot. Idem.

Une 4^e section Conditions de contrôle contient les règles évaluées par opération : sévérité (warning/blocking), formule, message à afficher. Cf. la sous-section dynamique plus haut.

Moteur de formules : $INTEG.x (data intégration — dont $INTEG.integre_par = nom de l'utilisateur qui réalise l'intégration, à mapper sur une colonne SharePoint dédiée si besoin), $CLIENT.x (client SP), $FICHE.x (fiche), $KIZ.x (colonne calculée Kizeo), #A (colonne entrée par lettre), @{libellé} (autre colonne mapping), opérateur & (concat). Fonctions : IF / IFS / CONCAT / COALESCE / UPPER / LOWER / TRIM / LEFT / RIGHT / MID / LEN / SEARCH / REPLACE / NOMPROPRE / TEL / CP / NOMBRE / ROUND / ROUNDDOWN / ROUNDUP / ABS / INT / MOD / MIN / MAX / TEXT / CHAR / NUM_OPERATION / TBASE_MIN / TBASE_MAX / TBASE_MIN_DPT / TBASE_MAX_DPT / ZONE_CLIMATIQUE / PAREVAPEUR / DATE_EST_POSTERIEURE_A / VERIF_BONIF.

VERIF_BONIF(tvv; date_achèvement; type_bonif) : appelle l'API verifbonif.spekty.fr (token VERIF_BONIF_API_TOKEN dans le .env) et renvoie le texte de réponse. La date est convertie en YYYY-MM-DD. Résultats mis en cache par (tvv, date, bonif). Renvoie vide si le token n'est pas configuré ou en cas d'erreur réseau.

Utilisateurs & Tokens API

• Onglet Utilisateurs : CRUD + toggle admin/user. La création se fait par INITIAL_ADMIN_EMAIL au 1^er démarrage si la table est vide. Allowlist stricte : seuls les utilisateurs déclarés ici peuvent se connecter — un compte Azure AD non déclaré reçoit une page « Accès non autorisé » (pas d'auto-création). Pour donner accès à quelqu'un, l'ajouter d'abord dans cet écran.
• Onglet Tokens API : create / revoke / delete. Les tokens sont hashés avec API_TOKEN_HASH_SECRET (distinct de JWT_SECRET_KEY). Utilisés par la macro VBA + scripts tiers via Authorization: Bearer integ_… ou ?api_token=… sur les endpoints /public-api/*.

Sécurité (audit ASVS v4.0.3, F-01 à F-23)

Garanties activées en production :

• JWT_SECRET_KEY et API_TOKEN_HASH_SECRET ≥ 32 chars, distincts ; rejet du démarrage si insecure.
• AUTH_DISABLED refusé hors ENVIRONMENT=development. PG_SSL_MODE=disable refusé hors dev.
• Cookies session SameSite=Strict + HttpOnly + Secure ; CSRF middleware (X-Requested-With requis).
• Rôle re-vérifié depuis la DB à chaque requête (rétrogradation immédiate).
• Uploads cappés à MAX_UPLOAD_BYTES (25 Mo) + openpyxl en read_only avec garde 500 000 cellules.
• Conditions évaluées en mode fail-closed (formule qui plante = traitée comme déclenchée).
• /docs Swagger UI : accessible UNIQUEMENT aux admins (DocsAdminGuardMiddleware vérifie le rôle en base à chaque requête, 404 sinon). Le schéma OpenAPI exposé est filtré aux endpoints /public-api/*.
• Headers : Permissions-Policy strict, X-Frame-Options DENY, CSP default-src 'self', HSTS preload en prod.
• Mail : whitelist domaine to, from_email vérifié contre pole.email_emetteur, attachment_path confiné sous OUTPUT_DIR.
• Logs : PII (Tel_1, Email, Nom_prenom, Adresse) redactée.
• Jobs (génération + doublons) taggés par owner_email : 404 si requester ≠ owner.
• OUTPUT_DIR purgé au boot (rétention 30 j par défaut).