<?php
class Commande {
    public static function getCommandeByNoCommande($noCommande) {
        $query = "SELECT * FROM commande".ID_GARAGE." WHERE NoCommande=:nocommande";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":nocommande", $noCommande);
        $statement->execute();
        return $statement->fetch();
    }

    public static function getCommandeByNoClient($noClient) {
        $query = "SELECT * FROM commande".ID_GARAGE." WHERE NoClient=:noclient";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":noclient", $noClient);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getCommandeByNoCommandeAndDatePayement($noCommande, $datePayement) {
        $query = "SELECT * FROM commande".ID_GARAGE." WHERE NoCommande=:commande AND DatePayement=:date";;
        $statement = Connection::getStatement($query);
        $statement->bindParam(":commande", $noCommande);
        $statement->bindParam(":date", $datePayement);
        $statement->execute();
        return $statement->fetchAll();
    }           

    public static function getCommandeByDescriptionAndPosition($description, $position = 1) {
        $query = "SELECT * FROM commandearticles".ID_GARAGE." WHERE Description = :description AND Position = :position";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":description", $description);
        $statement->bindParam(":position", $position);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getVersionByNoCommande($noCommande) {
        $query = "SELECT Version FROM commande".ID_GARAGE." WHERE NoCommande=:nocommande";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":nocommande", $noCommande);
        $statement->execute();
        return $statement->fetch()["Version"];
    }

    public static function getCommandeOuverte() {
        $query = "SELECT * FROM commande".ID_GARAGE." WHERE Type='CP' AND NoFourniseur != '' AND Recu != 'O' ORDER BY DateCommande ASC";
        $statement = Connection::getStatement($query);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function updateRecuToOByNoCommande($noCommande) {
        $query = "UPDATE commande".ID_GARAGE." SET Recu='O' WHERE NoCommande=:nocommande";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":nocommande", $noCommande);
        return $statement->execute();
    }

    public static function updateRecuToNByNoCommande($noCommande) {
        $query = "UPDATE commande".ID_GARAGE." SET Recu='N' WHERE NoCommande=:nocommande";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":nocommande", $noCommande);
        return $statement->execute();
    }

    public static function getRappel($degreSommation, $date, $typeFact) {
        $query = "SELECT * FROM commande".ID_GARAGE." WHERE DegreSommation=:degre AND APayer!=0 AND Paye!='O' AND DateDelaisPayement<:date AND ModeDelaisPayement=0 AND Type=:type ORDER BY NoClient";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":degre", $degreSommation);
        $statement->bindParam(":date", $date);
        $statement->bindParam(":type", $typeFact);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getCommandeByDestinataire($destinataire) {
        $query = "SELECT DISTINCT c.NoCommande FROM commande".ID_GARAGE." c, commandearticles".ID_GARAGE." ca WHERE c.Type = 'CP' AND c.NoCommande = ca.NoCommande AND (ca.Dest LIKE :destinatairedest OR ca.NoArticle LIKE :destinatairearticle ) ORDER BY c.NoCommande";
        $statement = Connection::getStatement($query);
        $statement->bindValue(":destinatairedest", '%'.$destinataire.'%');
        $statement->bindValue(":destinatairearticle", '%'.$destinataire.'%');
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getCommandeByDateCommande($DateDebut, $DateFin) {
        $query = "SELECT ca.NoArticle, ca.Description, ca.Quantite, ca.Dest, ca.PrixBase, c.NoCommande, c.DateCommande FROM `commande1` AS c, `commandearticles1` AS ca WHERE c.NoCommande = ca.NoCommande AND c.Type = 'CP' AND c.Recu = 'O' AND c.DateCommande >= :start AND c.DateCommande < :end ORDER BY ca.PrixBase DESC";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":start", $DateDebut);
        $statement->bindParam(":end", $DateFin);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getCommandeByDateCommandeAndNoArticle($DateDebut, $DateFin, $noArticle){
        $query = "SELECT ca.NoArticle, c.NoCommande, c.DateCommande FROM `commande1` AS c, `commandearticles1` AS ca WHERE c.NoCommande = ca.NoCommande AND (c.Genre = 'F' OR c.Genre = 'G') AND c.DateCommande >= :start AND c.DateCommande < :end AND ca.NoArticle = :article";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":start", $DateDebut);
        $statement->bindParam(":end", $DateFin);
        $statement->bindParam(":article", $noArticle);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getMaxCommandByChassis($chassis){
        $query = "SELECT MAX(DateCommande) AS MaxDateCommande FROM commande".ID_GARAGE." WHERE Vehicule = :chassis";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":chassis", $chassis);
        $statement->execute();
        if($row = $statement->fetch()) {
            return $row["MaxDateCommande"];
        }
        return null;
    }

    public static function getMaxCommandByNoClient($noClient){
        $query = "SELECT MAX(DateCommande) AS MaxDateCommande FROM commande".ID_GARAGE." WHERE NoClient = :client";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":client", $noClient);
        $statement->execute();
        if($row = $statement->fetch()) {
            return $row["MaxDateCommande"];
        }
        return null;
    }

    public static function getSumCommandByNoClient($noClient){
        $query = "SELECT SUM(MontantTotal) AS Total FROM commande".ID_GARAGE." WHERE NoClient = :client";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":client", $noClient);
        $statement->execute();
        if($row = $statement->fetch()) {
            return $row["Total"];
        }
        return "0";
    }
    
}