<commit_before><?php

/*
  Oressource
  Copyright (C) 2014-2017  <NAME> and Oressource devellopers

  This program is free software: you can redistribute it and/or modify
  it under the terms of the GNU Affero General Public License as
  published by the Free Software Foundation, either version 3 of the
  License, or (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU Affero General Public License for more details.

  You should have received a copy of the GNU Affero General Public License
  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

function filter_visibles(array $a): array {
  return array_map(function (array $e): array {
    $e['visible'] = $e['visible'] === 'oui';
    return $e;
  }, $a);
}

function objet_id_dechet(PDO $bdd, $id_dechet) {
  $stmt = $bdd->prepare("SELECT * FROM grille_objets WHERE id_type_dechet = :id_type_dechet");
  $stmt->bindValue(':id_type_dechet', $id_dechet, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function objet_id(PDO $bdd, $id_obj) {
  $req = $bdd->prepare("SELECT * FROM grille_objets WHERE id = :id_obj");
  $req->bindValue(':id_obj', $id_obj, PDO::PARAM_INT);
  $req->execute();
  $result = $req->fetch(PDO::FETCH_ASSOC);
  $req->closeCursor();
  return $result;
}

function objet_update_nom(PDO $bdd, $id, $nom) {
  $req = $bdd->prepare('update grille_objets set nom = :nom where id = :id');
  $req->bindValue(':id', $id, PDO::PARAM_INT);
  $req->bindValue(':nom', $nom, PDO::PARAM_STR);
  $req->execute();
  $req->closeCursor();
}

function objet_update(PDO $bdd, int $id, $prix, $nom, $description) {
  $req = $bdd->prepare('
      update grille_objets
      set nom = :nom1,
          description = :description,
          prix = :prix
      where BINARY nom <> :nom2
      and id = :id');
  $req->bindValue(':id', $id, PDO::PARAM_INT);
  $req->bindValue(':prix', $prix);
  $req->bindParam(':nom1', $nom, PDO::PARAM_STR);
  $req->bindParam(':nom2', $nom, PDO::PARAM_STR);
  $req->bindParam(':description', $description, PDO::PARAM_STR);
  $req->execute();
  if ($req->rowCount() === 0) {
    $req->closeCursor();
    throw new UnexpectedValueException('Un objet avec le meme nom existe deja.');
  }
  $req->closeCursor();
}

function objets(PDO $bdd): array {
  $sql = 'SELECT * FROM grille_objets';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function utilisateurs_id(PDO $bdd, int $id) {
  $sql = 'SELECT
    id,
    prenom,
    mail,
    nom,
    niveau
    FROM utilisateurs
    WHERE utilisateurs.id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function utilisateurs(PDO $bdd): array {
  $sql = 'SELECT
    id,
    prenom,
    mail,
    nom,
    niveau
    FROM utilisateurs';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  return $result;
}

function utilisateur_insert(PDO $bdd, array $utilisateur): int {
  $sql = 'INSERT INTO utilisateurs (
      nom,
      prenom,
      mail,
      pass,
      niveau
      ) VALUES (
        :nom,
        :prenom,
        :mail,
        :pass,
        :niveau)';
  $stmt = $bdd->prepare($sql);
  $stmt->bindParam(':nom', $utilisateur['nom'], PDO::PARAM_STR);
  $stmt->bindParam(':prenom', $utilisateur['prenom'], PDO::PARAM_STR);
  $stmt->bindParam(':mail', $utilisateur['mail'], PDO::PARAM_STR);
  $stmt->bindParam(':pass', $utilisateur['pass'], PDO::PARAM_STR);
  $stmt->bindParam(':niveau', $utilisateur['niveau'], PDO::PARAM_STR);
  $stmt->execute();
  $id = $bdd->lastInsertId();
  $stmt->closeCursor();
  return $id;
}

function utilisateur_update(PDO $bdd, array $utilisateur) {
  $sql = 'UPDATE utilisateurs SET
      nom = :nom,
      prenom = :prenom,
      mail = :mail,
      niveau = :niveau
      WHERE
      id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $utilisateur['id'], PDO::PARAM_INT);
  $stmt->bindParam(':nom', $utilisateur['nom'], PDO::PARAM_STR);
  $stmt->bindParam(':prenom', $utilisateur['prenom'], PDO::PARAM_STR);
  $stmt->bindParam(':mail', $utilisateur['mail'], PDO::PARAM_STR);
  $stmt->bindParam(':niveau', $utilisateur['niveau'], PDO::PARAM_STR);
  $stmt->execute();
  $stmt->closeCursor();
}

function convention_sortie(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, description, timestamp, visible FROM conventions_sorties';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function convention_sortie_by_id(PDO $bdd, int $id): array {
  $sql = 'SELECT id, nom, couleur, description, timestamp, visible FROM conventions_sorties
  AND id = :i';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function points_collecte_id(PDO $bdd, int $id): array {
  $sql = 'SELECT pesee_max, nom
          FROM points_collecte
          WHERE id = :id
          LIMIT 1';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $point_collecte = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $point_collecte;
}

function points_sorties_id(PDO $bdd, int $id): array {
  $sql = 'SELECT id, pesee_max, nom, adresse, visible FROM points_sortie WHERE id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $point_sortie = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $point_sortie;
}

function points_collectes(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom, adresse, pesee_max FROM points_collecte WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function points_sorties(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom, adresse FROM points_sortie WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function points_ventes(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom, adresse FROM points_vente WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function points_ventes_id(PDO $bdd, int $id_point_vente): array {
  $stmt = $bdd->prepare('SELECT id, nom, adresse FROM points_vente WHERE id = :id');
  $stmt->bindValue(':id', $id_point_vente, PDO::PARAM_INT);
  $stmt->execute();
  $point_sortie = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $point_sortie;
}

function types_contenants(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT masse, nom FROM type_contenants WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_contenants_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT masse, nom FROM type_contenants WHERE id = :id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function localites(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom FROM localites WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_poubelles(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom, masse_bac, couleur FROM types_poubelles WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_poubelles_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, nom, masse_bac, couleur, ultime FROM types_poubelles WHERE id = :id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function types_conteneurs(PDO $bdd): array {
  $sql = 'SELECT masse, nom FROM type_contenants WHERE visible = "oui"';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_dechets_evac(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom, couleur FROM type_dechets_evac WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_dechets_evac_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, nom, couleur, description FROM type_dechets_evac WHERE :id = id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function types_dechets(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom, couleur, description FROM type_dechets WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_dechets_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, nom, couleur, description FROM type_dechets WHERE :id = id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function types_sorties(PDO $bdd): array {
  $stmt = $bdd->prepare('SELECT id, nom FROM type_sortie WHERE visible = "oui"');
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_collectes(PDO $bdd): array {
  $sql = 'SELECT id, nom FROM type_collecte WHERE visible = "oui"';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function types_collectes_id(PDO $bdd, int $id): array {
  $sql = 'SELECT id, nom, description, couleur FROM type_collecte WHERE :id = id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function filieres_sorties(PDO $bdd): array {
  $sql = 'SELECT filieres_sortie.id, filieres_sortie.nom, filieres_sortie.id_type_dechet_evac AS type_dechet
          FROM filieres_sortie
          WHERE filieres_sortie.visible = "oui"';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  $filieres_sorties = $stmt->fetchAll(PDO::FETCH_ASSOC);
  return array_map(function($filiere) {
    $filiere['accepte_type_dechet'] = explode('a', $filiere['type_dechet']);
    return $filiere;
  }, $filieres_sorties);
}

function nb_categories_dechets_evac(PDO $bdd): int {
  $stmt = $bdd->prepare('SELECT MAX(id) AS nombrecat FROM type_dechets_evac LIMIT 1');
  $stmt->execute();
  $nombre_categories = (int) $stmt->fetch(PDO::FETCH_ASSOC)['nombrecat'];
  $stmt->closeCursor();
  return $nombre_categories;
}

function nb_categories_dechets_item(PDO $bdd): int {
  $stmt = $bdd->prepare('SELECT MAX(id) AS nombrecat FROM type_dechets');
  $stmt->execute();
  $nombre_categories = (int) $stmt->fetch(PDO::FETCH_ASSOC)['nombrecat'];
  $stmt->closeCursor();
  return (int) $nombre_categories;
}

function nb_categories_poubelles(PDO $bdd): int {
  $stmt = $bdd->prepare('SELECT MAX(id) AS nombrecat FROM types_poubelles LIMIT 1');
  $stmt->execute();
  $nombre_categories = (int) $stmt->fetch(PDO::FETCH_ASSOC)['nombrecat'];
  $stmt->closeCursor();
  return (int) $nombre_categories;
}

// Insere une collecte dans la base.
// On ecrit le nombre de categories maxi dans la varialble $nombreCategories
// HACK C'est pas parfait il faudrait comparer aux differents ID de la base.
// Si des utilisateurs suppriment des objet sa la main c'est la galere...
// Mais d'un cote niveau tracabilite ils devraient pas supprimer de categories...
// genere une exception si les masses sont inferieurs a 0.

function insert_items_collecte(PDO $bdd, int $id_collecte, $collecte, $items) {
  $nombreCategories = nb_categories_dechets_item($bdd);
  $req = $bdd->prepare('INSERT INTO pesees_collectes
                            (timestamp, masse, id_collecte, id_type_dechet, id_createur)
                        VALUES (:timestamp, :masse, :id_collecte, :id_type_dechet, :id_createur)');
  $req->bindValue(':timestamp', $collecte['timestamp']->format('Y-m-d H:i:s'), PDO::PARAM_STR);
  $req->bindValue(':id_collecte', $id_collecte, PDO::PARAM_INT);
  $req->bindValue(':id_createur', $collecte['id_user'], PDO::PARAM_INT);
  foreach ($items as $item) {
    $masse = (float) parseFloat($item['masse']);
    $type_dechet = (int) parseInt($item['type']);
    if ($masse > 0.00 && $type_dechet <= $nombreCategories) {
      $req->bindValue(':masse', $masse);
      $req->bindValue(':id_type_dechet', $type_dechet, PDO::PARAM_INT);
      $req->execute();
    } else {
      $req->closeCursor();
      throw new UnexpectedValueException('masse <= 0.0 ou type item inconnu');
    }
  }
}

// TODO: Refactorer ça trop d'info
function pesee_collectes_id(PDO $bdd, int $id_collecte): int {
  $sql = 'SELECT
    pesees_collectes.id, pesees_collectes.timestamp,
    type_dechets.nom, pesees_collectes.masse, type_dechets.couleur,
    utilisateurs.mail mail, pesees_collectes.last_hero_timestamp edit_date,
    pesees_collectes.id_last_hero id_editeur
    FROM pesees_collectes, type_dechets, utilisateurs, collectes
    WHERE type_dechets.id = pesees_collectes.id_type_dechet
    AND utilisateurs.id = pesees_collectes.id_createur
    AND pesees_collectes.id_collecte = :id_collecte
    GROUP BY id';
  $stmt = $bdd->prepare($sql);
  $stmt->prepare(['id_collecte' => $id_collecte]);
  return $stmt - fetchAll(PDO::FETCH_ASSOC);
}

function collecte_id(PDO $bdd, int $id): array {
  $sql = 'Select id,
    timestamp, id_type_collecte,
    localisation, id_point_collecte,
    commentaire, id_createur,
    id_editeur, edit_date
    From collecte Where id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $collecte = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $collecte;
}

function moyens_paiements(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM moyens_paiement';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function moyens_paiements_visibles(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM moyens_paiement WHERE visible = "oui"';
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function insert_collecte(PDO $bdd, array $collecte): int {
  $req = $bdd->prepare('INSERT INTO collectes
                            (timestamp, id_type_collecte,
                            localisation, id_point_collecte,
                            commentaire, id_createur)
                          VALUES (:timestamp, :id_type_action,
                                  :localite, :id_point,
                                  :commentaire, :id_createur)');
  $req->bindValue(':timestamp', $collecte['timestamp']->format('Y-m-d H:i:s'), PDO::PARAM_STR);
  $req->bindValue(':id_type_action', $collecte['id_type_action'], PDO::PARAM_INT);
  // HACK: virer les adherants de la base ou juste changer le type en booleen serieusement...
  $req->bindValue(':localite', $collecte['localite'], PDO::PARAM_INT);
  $req->bindValue(':id_point', $collecte['id_point'], PDO::PARAM_INT);
  $req->bindValue(':commentaire', $bdd->quote($collecte['commentaire']), PDO::PARAM_STR);
  $req->bindValue(':id_createur', $collecte['id_user'], PDO::PARAM_INT);
  $req->execute();
  return (int) $bdd->lastInsertId();
}

function insert_items_sorties(PDO $bdd, int $id_sorties, $sortie, $items) {
  $nombreCategories = nb_categories_dechets_item($bdd);
  $req = $bdd->prepare('INSERT INTO pesees_sorties (timestamp, masse,  id_sortie, id_type_dechet, id_createur)
                            VALUES(:timestamp, :masse, :id_sortie, :id_type_dechet, :id_createur)');
  $req->bindValue(':timestamp', $sortie['timestamp']->format('Y-m-d H:i:s'), PDO::PARAM_STR);
  $req->bindValue(':id_sortie', $id_sorties, PDO::PARAM_INT);<commit_msg>Factorisation du code des requêtes via une fonction auxilière.<commit_after><?php

/*
  Oressource
  Copyright (C) 2014-2017  <NAME> and Oressource devellopers

  This program is free software: you can redistribute it and/or modify
  it under the terms of the GNU Affero General Public License as
  published by the Free Software Foundation, either version 3 of the
  License, or (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU Affero General Public License for more details.

  You should have received a copy of the GNU Affero General Public License
  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

function filter_visibles(array $a): array {
  return array_map(function (array $e): array {
    $e['visible'] = $e['visible'] === 'oui';
    return $e;
  }, $a);
}

function fetch_all(string $sql, PDO $bdd): array {
  $stmt = $bdd->prepare($sql);
  $stmt->execute();
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function objet_id_dechet(PDO $bdd, $id_dechet) {
  $stmt = $bdd->prepare("SELECT * FROM grille_objets WHERE id_type_dechet = :id_type_dechet");
  $stmt->bindValue(':id_type_dechet', $id_dechet, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function objet_id(PDO $bdd, $id_obj) {
  $req = $bdd->prepare("SELECT * FROM grille_objets WHERE id = :id_obj");
  $req->bindValue(':id_obj', $id_obj, PDO::PARAM_INT);
  $req->execute();
  $result = $req->fetch(PDO::FETCH_ASSOC);
  $req->closeCursor();
  return $result;
}

function objet_update_nom(PDO $bdd, $id, $nom) {
  $req = $bdd->prepare('update grille_objets set nom = :nom where id = :id');
  $req->bindValue(':id', $id, PDO::PARAM_INT);
  $req->bindValue(':nom', $nom, PDO::PARAM_STR);
  $req->execute();
  $req->closeCursor();
}

function objet_update(PDO $bdd, int $id, $prix, $nom, $description) {
  $req = $bdd->prepare('
      update grille_objets
      set nom = :nom1,
          description = :description,
          prix = :prix
      where BINARY nom <> :nom2
      and id = :id');
  $req->bindValue(':id', $id, PDO::PARAM_INT);
  $req->bindValue(':prix', $prix);
  $req->bindParam(':nom1', $nom, PDO::PARAM_STR);
  $req->bindParam(':nom2', $nom, PDO::PARAM_STR);
  $req->bindParam(':description', $description, PDO::PARAM_STR);
  $req->execute();
  if ($req->rowCount() === 0) {
    $req->closeCursor();
    throw new UnexpectedValueException('Un objet avec le meme nom existe deja.');
  }
  $req->closeCursor();
}

function objets(PDO $bdd): array {
  $sql = 'SELECT * FROM grille_objets';
  return fetch_all($sql, $bdd);
}

function utilisateurs_id(PDO $bdd, int $id) {
  $sql = 'SELECT
    id,
    prenom,
    mail,
    nom,
    niveau
    FROM utilisateurs
    WHERE utilisateurs.id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function utilisateurs(PDO $bdd): array {
  $sql = 'SELECT
    id,
    prenom,
    mail,
    nom,
    niveau
    FROM utilisateurs';
  return fetch_all($sql, $bdd);
}

function utilisateur_insert(PDO $bdd, array $utilisateur): int {
  $sql = 'INSERT INTO utilisateurs (
      nom,
      prenom,
      mail,
      pass,
      niveau
      ) VALUES (
        :nom,
        :prenom,
        :mail,
        :pass,
        :niveau)';
  $stmt = $bdd->prepare($sql);
  $stmt->bindParam(':nom', $utilisateur['nom'], PDO::PARAM_STR);
  $stmt->bindParam(':prenom', $utilisateur['prenom'], PDO::PARAM_STR);
  $stmt->bindParam(':mail', $utilisateur['mail'], PDO::PARAM_STR);
  $stmt->bindParam(':pass', $utilisateur['pass'], PDO::PARAM_STR);
  $stmt->bindParam(':niveau', $utilisateur['niveau'], PDO::PARAM_STR);
  $stmt->execute();
  $id = $bdd->lastInsertId();
  $stmt->closeCursor();
  return $id;
}

function utilisateur_update(PDO $bdd, array $utilisateur) {
  $sql = 'UPDATE utilisateurs SET
      nom = :nom,
      prenom = :prenom,
      mail = :mail,
      niveau = :niveau
      WHERE
      id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $utilisateur['id'], PDO::PARAM_INT);
  $stmt->bindParam(':nom', $utilisateur['nom'], PDO::PARAM_STR);
  $stmt->bindParam(':prenom', $utilisateur['prenom'], PDO::PARAM_STR);
  $stmt->bindParam(':mail', $utilisateur['mail'], PDO::PARAM_STR);
  $stmt->bindParam(':niveau', $utilisateur['niveau'], PDO::PARAM_STR);
  $stmt->execute();
  $stmt->closeCursor();
}

function convention_sortie(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, description, timestamp, visible FROM conventions_sorties';
  return fetch_all($sql, $bdd);
}

function convention_sortie_by_id(PDO $bdd, int $id): array {
  $sql = 'SELECT id, nom, couleur, description, timestamp, visible FROM conventions_sortie AND id = :i';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function points_collecte_id(PDO $bdd, int $id): array {
  $sql = 'SELECT pesee_max, nom
          FROM points_collecte
          WHERE id = :id
          LIMIT 1';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $point_collecte = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $point_collecte;
}

function points_sorties_id(PDO $bdd, int $id): array {
  $sql = 'SELECT id, pesee_max, nom, adresse, visible FROM points_sortie WHERE id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $point_sortie = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $point_sortie;
}

function points_collectes(PDO $bdd): array {
  $sql = 'SELECT id, nom, adresse, couleur, pesee_max, visible FROM points_collecte';
  return fetch_all($sql, $bdd);
}

function points_sorties(PDO $bdd): array {
  $sql = 'SELECT id, nom, adresse, couleur, visible FROM points_sortie';
  return fetch_all($sql, $bdd);
}

function points_ventes(PDO $bdd): array {
  $sql = 'SELECT id, nom, adresse, couleur, visible FROM points_vente';
  return fetch_all($sql, $bdd);
}

function points_ventes_id(PDO $bdd, int $id_point_vente): array {
  $stmt = $bdd->prepare('SELECT id, nom, adresse FROM points_vente WHERE id = :id');
  $stmt->bindValue(':id', $id_point_vente, PDO::PARAM_INT);
  $stmt->execute();
  $point_sortie = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $point_sortie;
}

function types_contenants(PDO $bdd): array {
  $sql = 'SELECT masse, nom, couleur, visible FROM type_contenants';
  return fetch_all($sql, $bdd);
}

function types_contenants_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, masse, nom, couleur, visible FROM type_contenants WHERE id = :id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function localites(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM localites';
  return fetch_all($sql, $bdd);
}

function types_poubelles(PDO $bdd): array {
  $sql = 'SELECT id, nom, masse_bac, couleur, visible FROM types_poubelles';
  return fetch_all($sql, $bdd);
}

function types_poubelles_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, nom, masse_bac, couleur, ultime FROM types_poubelles WHERE id = :id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function types_conteneurs(PDO $bdd): array {
  $sql = 'SELECT id, nom, visible, masse FROM type_contenants';
  return fetch_all($sql, $bdd);
}

function types_dechets_evac(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM type_dechets_evac';
  return fetch_all($sql, $bdd);
}

function types_dechets_evac_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, nom, couleur, description, visible FROM type_dechets_evac WHERE :id = id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function types_dechets(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, description, visible FROM type_dechets';
  return fetch_all($sql, $bdd);
}

function types_dechets_id(PDO $bdd, int $id): array {
  $stmt = $bdd->prepare('SELECT id, nom, couleur, description, visible FROM type_dechets WHERE :id = id');
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function types_sorties(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM type_sortie';
  return fetch_all($sql, $bdd);
}

function types_collectes(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM type_collecte WHERE visible = "oui"';
  return fetch_all($sql, $bdd);
}

function types_collectes_id(PDO $bdd, int $id): array {
  $sql = 'SELECT id, nom, description, couleur, visible FROM type_collecte WHERE :id = id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $result;
}

function filieres_sorties(PDO $bdd): array {
  $sql = 'SELECT id, nom, visible, id_type_dechet_evac type_dechet FROM filieres_sortie';
  return array_map(function($filiere) {
    $filiere['accepte_type_dechet'] = explode('a', $filiere['type_dechet']);
    return $filiere;
  }, fetch_all($sql, $bdd));
}

function nb_categories_dechets_evac(PDO $bdd): int {
  $stmt = $bdd->prepare('SELECT MAX(id) AS nombrecat FROM type_dechets_evac LIMIT 1');
  $stmt->execute();
  $nombre_categories = (int) $stmt->fetch(PDO::FETCH_ASSOC)['nombrecat'];
  $stmt->closeCursor();
  return $nombre_categories;
}

function nb_categories_dechets_item(PDO $bdd): int {
  $stmt = $bdd->prepare('SELECT MAX(id) AS nombrecat FROM type_dechets');
  $stmt->execute();
  $nombre_categories = (int) $stmt->fetch(PDO::FETCH_ASSOC)['nombrecat'];
  $stmt->closeCursor();
  return (int) $nombre_categories;
}

function nb_categories_poubelles(PDO $bdd): int {
  $stmt = $bdd->prepare('SELECT MAX(id) AS nombrecat FROM types_poubelles LIMIT 1');
  $stmt->execute();
  $nombre_categories = (int) $stmt->fetch(PDO::FETCH_ASSOC)['nombrecat'];
  $stmt->closeCursor();
  return (int) $nombre_categories;
}

// Insere une collecte dans la base.
// On ecrit le nombre de categories maxi dans la varialble $nombreCategories
// HACK C'est pas parfait il faudrait comparer aux differents ID de la base.
// Si des utilisateurs suppriment des objet sa la main c'est la galere...
// Mais d'un cote niveau tracabilite ils devraient pas supprimer de categories...
// genere une exception si les masses sont inferieurs a 0.

function insert_items_collecte(PDO $bdd, int $id_collecte, $collecte, $items) {
  $nombreCategories = nb_categories_dechets_item($bdd);
  $req = $bdd->prepare('INSERT INTO pesees_collectes
                            (timestamp, masse, id_collecte, id_type_dechet, id_createur)
                        VALUES (:timestamp, :masse, :id_collecte, :id_type_dechet, :id_createur)');
  $req->bindValue(':timestamp', $collecte['timestamp']->format('Y-m-d H:i:s'), PDO::PARAM_STR);
  $req->bindValue(':id_collecte', $id_collecte, PDO::PARAM_INT);
  $req->bindValue(':id_createur', $collecte['id_user'], PDO::PARAM_INT);
  foreach ($items as $item) {
    $masse = (float) parseFloat($item['masse']);
    $type_dechet = (int) parseInt($item['type']);
    if ($masse > 0.00 && $type_dechet <= $nombreCategories) {
      $req->bindValue(':masse', $masse);
      $req->bindValue(':id_type_dechet', $type_dechet, PDO::PARAM_INT);
      $req->execute();
    } else {
      $req->closeCursor();
      throw new UnexpectedValueException('masse <= 0.0 ou type item inconnu');
    }
  }
}

// TODO: Refactorer ça trop d'info
function pesee_collectes_id(PDO $bdd, int $id_collecte): int {
  $sql = 'SELECT
    pesees_collectes.id, pesees_collectes.timestamp,
    type_dechets.nom, pesees_collectes.masse, type_dechets.couleur,
    utilisateurs.mail mail, pesees_collectes.last_hero_timestamp edit_date,
    pesees_collectes.id_last_hero id_editeur
    FROM pesees_collectes, type_dechets, utilisateurs, collectes
    WHERE type_dechets.id = pesees_collectes.id_type_dechet
    AND utilisateurs.id = pesees_collectes.id_createur
    AND pesees_collectes.id_collecte = :id_collecte
    GROUP BY id';
  $stmt = $bdd->prepare($sql);
  $stmt->prepare(['id_collecte' => $id_collecte]);
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function collecte_id(PDO $bdd, int $id): array {
  $sql = 'Select id,
    timestamp, id_type_collecte,
    localisation, id_point_collecte,
    commentaire, id_createur,
    id_editeur, edit_date
    From collecte Where id = :id';
  $stmt = $bdd->prepare($sql);
  $stmt->bindValue(':id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $collecte = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();
  return $collecte;
}

function moyens_paiements(PDO $bdd): array {
  $sql = 'SELECT id, nom, couleur, visible FROM moyens_paiement';
  return fetch_all($sql, $bdd);
}

function insert_collecte(PDO $bdd, array $collecte): int {
  $req = $bdd->prepare('INSERT INTO collectes
                            (timestamp, id_type_collecte,
                            localisation, id_point_collecte,
                            commentaire, id_createur)
                          VALUES (:timestamp, :id_type_action,
                                  :localite, :id_point,
                                  :commentaire, :id_createur)');
  $req->bindValue(':timestamp', $collecte['timestamp']->format('Y-m-d H:i:s'), PDO::PARAM_STR);
  $req->bindValue(':id_type_action', $collecte['id_type_action'], PDO::PARAM_INT);
  // HACK: virer les adherants de la base ou juste changer le type en booleen serieusement...
  $req->bindValue(':localite', $collecte['localite'], PDO::PARAM_INT);
  $req->bindValue(':id_point', $collecte['id_point'], PDO::PARAM_INT);
  $req->bindValue(':commentaire', $bdd->quote($collecte['commentaire']), PDO::PARAM_STR);
  $req->bindValue(':id_createur', $collecte['id_user'], PDO::PARAM_INT);
  $req->execute();
  return (int) $bdd->lastInsertId();
}

function insert_items_sorties(PDO $bdd, int $id_sorties, $sortie, $items) {
  $nombreCategories = nb_categories_dechets_item($bdd);
  $req = $bdd->prepare('INSERT INTO pesees_sorties (timestamp, masse,  id_sortie, id_type_dechet, id_createur)
                            VALUES(:timestamp, :masse, :id_sortie, :id_type_dechet, :id_createur)');
  $req->bindValue(':timestamp', $sortie['timestamp']->format('Y-m-d H:i:s'), PDO::PARAM_STR);
  $req->bindValue(':id_sortie', $id_sorties, PDO::PARAM_INT);
{'fraction_non_alphanumeric': 0.16739047132456253, 'fraction_numerical': 0.0014688976880827692, 'mean_word_length': 4.483628086149536, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 340, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2593457', 'n_tokens_mistral': 11579, 'n_tokens_neox': 11532, 'n_words': 2507}