<?php
class Atelier {

    public static function getAll() {
        $query = "SELECT * FROM atelier".ID_GARAGE." ORDER BY NoAtelier DESC LIMIT 0, 1";
        $statement = Connection::getStatement($query);
        $statement->execute();
        return $statement->fetchAll();
    }
  
    public static function getAtelierByNoAtelier($noAtelier) {
        $query = "SELECT * FROM atelier".ID_GARAGE." WHERE NoAtelier=:noatelier";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":noatelier", $noAtelier);
        $statement->execute();
        return $statement->fetch();
    }

    public static function getAtelierByDateNoClientNoChassisNoCommande($date, $noClient, $noChassis, $noCommande) {
        $query = "SELECT * FROM atelier".ID_GARAGE." WHERE Date=:date AND NoClient=:client AND NoChasis=:chassis AND NoCommande=:commande";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        $statement->bindParam(":client", $noClient);
        $statement->bindParam(":chassis", $noChassis);
        $statement->bindParam(":commande", $noCommande);
        $statement->execute();
        return $statement->fetch();
    }

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

    public static function getAtelierByDate($date, $mecano = null) {
        $query = "SELECT * FROM atelier".ID_GARAGE." WHERE Date=:date";
        if(!is_null($mecano)) {
            $query .= " AND Mecano = :mecano ORDER BY Etape, NoAtelier";
        } else {
            $query .= " ORDER BY NoAtelier";
        } 
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        if(!is_null($mecano)) {
            $statement->bindParam(":mecano", $mecano);
        }
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getNoCommandeByDate($date) {
        $query = "SELECT atelier".ID_GARAGE.".NoCommande FROM atelier".ID_GARAGE.", commande".ID_GARAGE." WHERE atelier".ID_GARAGE.".Date=:date AND atelier".ID_GARAGE.".NoCommande>0 AND atelier".ID_GARAGE.".NoCommande = commande".ID_GARAGE.".NoCommande AND (commande".ID_GARAGE.".Genre = 'F' OR commande".ID_GARAGE.".Genre = 'G') AND atelier".ID_GARAGE.".Etape > 1";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        $statement->execute();
        return $statement->fetchAll();
    }

    public static function getNoCommandeByDateAndMecano($date, $mecano) {
        $query = "SELECT atelier".ID_GARAGE.".NoCommande FROM atelier".ID_GARAGE.", commande".ID_GARAGE." WHERE atelier".ID_GARAGE.".Date=:date AND atelier".ID_GARAGE.".NoCommande>0 AND atelier".ID_GARAGE.".Mecano = :mecano AND atelier".ID_GARAGE.".NoCommande = commande".ID_GARAGE.".NoCommande AND (commande".ID_GARAGE.".Genre = 'F' OR commande".ID_GARAGE.".Genre = 'G')";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        $statement->bindParam(":mecano", $mecano);
        $statement->execute();
        return $statement->fetchAll();
    }
    
    public static function getSumTempByDate($date, $etape = null, $mecano = null) : float {
        $query = "SELECT SUM(Temps) FROM atelier".ID_GARAGE." WHERE Date=:date";
        if(!is_null($etape)) {
            $query .= " AND Etape<:etape";
        }
        if(!is_null($mecano)) {
            $query .= " AND Mecano = :mecano";
        }
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        if(!is_null($etape)) {
            $statement->bindParam(":etape", $etape);
        }
        if(!is_null($mecano)) {
            $statement->bindParam(":mecano", $mecano);
        }
        $statement->execute();
        $result = $statement->fetch();
        if($result) {
            if(is_null($result[0])) {
                return 0.0;
            }
            return $result[0];
        }
        return 0.0;
    }

    public static function getSumAsuTempByDate($date, $etape = null, $mecano = null) : float {
        $query = "SELECT SUM(asu.Temps) FROM atelier".ID_GARAGE." AS a, atelier_suite".ID_GARAGE." AS asu WHERE a.Date=:date AND a.NoAtelier = asu.NoAtelier";
        if(!is_null($etape)) {
            $query .= " AND Etape<:etape";
        }
        if(!is_null($mecano)) {
            $query .= " AND a.Mecano = :mecano";
        }
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        if(!is_null($etape)) {
            $statement->bindParam(":etape", $etape);
        }
        if(!is_null($mecano)) {
            $statement->bindParam(":mecano", $mecano);
        }
        $statement->execute();
        $result = $statement->fetch();
        if($result) {
            if(is_null($result[0])) {
                return 0.0;
            }
            return $result[0];
        }
        return 0.0;
    }

    public static function getSumAsuTempByAsuDate($date, $etape = null, $mecano = null) : float {
        $query = "SELECT SUM(asu.Temps) FROM atelier".ID_GARAGE." AS a, atelier_suite".ID_GARAGE." AS asu WHERE asu.Date=:date AND a.NoAtelier = asu.NoAtelier";
        if(!is_null($etape)) {
            $query .= " AND Etape<:etape";
        }
        if(!is_null($mecano)) {
            $query .= " AND asu.Mecano = :mecano";
        }
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        if(!is_null($etape)) {
            $statement->bindParam(":etape", $etape);
        }
        if(!is_null($mecano)) {
            $statement->bindParam(":mecano", $mecano);
        }
        $statement->execute();
        $result = $statement->fetch();
        if($result) {
            if(is_null($result[0])) {
                return 0.0;
            }
            return $result[0];
        }
        return 0.0;
    }

    public static function getNbRdv($date, $etape = null, $mecano = null) : int {
        $query = "SELECT COUNT(*) FROM atelier".ID_GARAGE." WHERE Date>=:date AND Date<=:date";
        if(!is_null($etape)) {
            $query .= " AND Etape<:etape";
        }
        if(!is_null($mecano)) {
            $query .=  " AND Mecano = :mecano";
        }
        $statement = Connection::getStatement($query);
        $statement->bindParam(":date", $date);
        if(!is_null($etape)) {
            $statement->bindParam(":etape", $etape);
        }
        if(!is_null($mecano)) {
            $statement->bindParam(":mecano", $mecano);
        }
        $statement->execute();
        $result = $statement->fetch();
        if($result) {
            if(is_null($result[0])) {
                return 0;
            }
            return $result[0];
        }
        return 0;
    }

    public static function getByNoChassisRecent($noChassis) {
        $query = "SELECT * FROM `atelier1` WHERE NoChasis=:chassis AND ( Date > NOW() - INTERVAL 20 MONTH )";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":chassis", $noChassis);
        $statement->execute();
        return $statement->fetch();
    }

    public static function getByNoChassisNoService($noChassis) {
        $query = "SELECT * FROM atelier1 WHERE NoChasis =:chassis AND Id_Service !='0' ORDER BY `atelier1`.`NoAtelier` DESC LIMIT 1";
        $statement = Connection::getStatement($query);
        $statement->bindParam(":chassis", $noChassis);
        $statement->execute();
        return $statement->fetch();
    }
    
}