package dao.implementation.mysql.course;
import modele.chauffeur.Chauffeur;
import modele.course.Tournee;
import modele.course.Trajet;
import dao.ITourneeDao;
import dao.ITrajetDao;
import dao.implementation.mysql.AccessMySql;
import gui.MainWindow;
import gui.util.TaxiGuiUtil;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Vector;
import util.Periode;
/**
* <p> MySqlTrajetDaoo est une classe Dao qui implemente les m�thodes de l'interface ITrajetDao:</>
* <ul>
* <li> public ReferenceChauffeur find(final int id) : cherche le chauffeur par son id </li>
* <li> public ArrayList<ReferenceChauffeur> findAll() : charge depuis la base de donn�es, renvoie la liste des chauffeurs</li>
* </ul>
* <p>Dans chaque m�thode, on �tablit la connexion avec la base de donn�es et on la synchronise</p>
*
* @author Kasandra
*
*/
public class MySqlTrajetDao implements ITrajetDao {
private static final String INSERT_TRAJET = "insert into ttrajet (idTrnee, adrDepTraj, kmDepTraj, dtDepTraj, adrArrivTraj, kmArrivTraj, dtArrivTraj, prixCourseTraj, prixSupplementTraj) values (?,?, ?,?,?,?, ?,?,?)";
private static final String UPDATE_TRAJET = "update ttrajet set idTrnee = ?, adrDepTraj = ?, kmDepTraj = ?, dtDepTraj = ?, adrArrivTraj = ?, kmArrivTraj = ?, dtArrivTraj = ?, prixCourseTraj = ?, prixSupplementTraj = ?";
private static final String DELETE_TRAJET = "delete from ttrajet where idTraj = ?";
private static final String GET_ALL_TRAJET = "select idTraj,idTrnee, adrDepTraj, kmDepTraj, dtDepTraj, adrArrivTraj, kmArrivTraj, dtArrivTraj, prixCourseTraj, prixSupplementTraj from ttrajet";
private static final String GET_TRAJET_BY_ID = GET_ALL_TRAJET + " where idTraj = ?";
private static final String GET_TRAJETS_BY_CHAUFFEUR_AND_PERIODE = "select idTraj, idTrnee, adrDepTraj, kmDepTraj, dtDepTraj, adrArrivTraj, kmArrivTraj, hrArrivTraj, prixCourseTraj, prixSupplementTraj from ttrajet, ttournees where ttourneechauf.idTrnee = ttrajet.idTrnee and ttourneechauf.idChauff = ? and dtDepTraj between ? and ?";
private static final String GET_TRAJETS_BY_TOURNEE = "select idTraj, adrDepTraj, kmDepTraj, dtDepTraj, adrArrivTraj, kmArrivTraj, dtArrivTraj, prixCourseTraj, prixSupplementTraj from ttrajet where idTrnee = ?";
public Trajet insert(final Trajet trajet) {
final Trajet[] result = new Trajet[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_TRAJET);
Tournee tournee = trajet.getTournee();
Integer tourn�eId = tournee.getId();
psmt.setInt(1, tourn�eId);
psmt.setString(2,trajet.getAddresseDepart());
psmt.setLong(3, trajet.getKilometrageTaximetreDebut());
//psmt.setDate(4,new java.sql.Date(trajet.getHeureDepart().getTime()));
psmt.setTimestamp(4, new java.sql.Timestamp(trajet.getHeureDepart().getTime()));
psmt.setString(5,trajet.getAddresseArrivee());
psmt.setLong(6, trajet.getKilometrageTaximetreFin());
//psmt.setDate(7, new java.sql.Date(trajet.getHeureArrivee().getTime()));
psmt.setTimestamp(7, new java.sql.Timestamp(trajet.getHeureArrivee().getTime()));
psmt.setBigDecimal(8, trajet.getPrixCourse());
psmt.setBigDecimal(9, trajet.getPrixSupplementaire());
psmt.executeUpdate();
} finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select idTraj,ttrajet.idTrnee, adrDepTraj, kmDepTraj, dtDepTraj, adrArrivTraj, kmArrivTraj, dtArrivTraj, prixCourseTraj, prixSupplementTraj from ttrajet, ttourneechauf where ttrajet.idTrnee = ttourneechauf.idTrnee and idTraj = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Trajet(res.getInt(1));
Tournee tournee = new Tournee(res.getInt(2));
result[0].setTournee(tournee);
result[0].setAddresseDepart(res.getString(3));
result[0].setKilometrageTaximetreDepart(Long.parseLong(res.getString(4)));
Date hrDepart = new Date(res.getTimestamp(5).getTime());
result[0].setHeureDepart(hrDepart);
result[0].setAddresseArrivee(res.getString(6));
result[0].setKilometrageTaximetreArrivee(Long.parseLong(res.getString(7)));
Date hrArrivee = new Date(res.getTimestamp(8).getTime());
result[0].setHeureArrivee(hrArrivee);
result[0].setPrixCourse(new BigDecimal(res.getString(9)));
result[0].setPrixSupplementaire(new BigDecimal(res.getString(10)));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Trajet update(final Trajet trajet) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_TRAJET);
smt.setInt(1, trajet.getTournee().getId());
smt.setString(2,trajet.getAddresseDepart());
smt.setLong(3, trajet.getKilometrageTaximetreDebut());
smt.setTimestamp(4, new java.sql.Timestamp(trajet.getHeureDepart().getTime()));
smt.setString(5,trajet.getAddresseArrivee());
smt.setLong(6, trajet.getKilometrageTaximetreFin());
smt.setTimestamp(7, new java.sql.Timestamp(trajet.getHeureArrivee().getTime()));
smt.setBigDecimal(8, trajet.getPrixCourse());
smt.setBigDecimal(9, trajet.getPrixSupplementaire());
smt.setInt(10, trajet.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return trajet;
}
public void delete(final Trajet trajet) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_TRAJET);
smt.setInt(1, trajet.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public ArrayList<Trajet> findAll() {
final ArrayList<Trajet> listeTrajet = new ArrayList<Trajet>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_TRAJET);
res = smt.executeQuery();
while (res.next()) {
Trajet trajet = new Trajet(res.getInt(1));
Tournee tournee = new Tournee(res.getInt(2));
trajet.setTournee(tournee);
trajet.setAddresseDepart(res.getString(3));
trajet.setKilometrageTaximetreDepart(Long.parseLong(res.getString(4)));
Date hrDepart = new java.sql.Timestamp(res.getTimestamp(5).getTime());
//Date hrDepart = new Date(res.getDate(5).getTime());
trajet.setHeureDepart(hrDepart);
trajet.setAddresseArrivee(res.getString(6));
trajet.setKilometrageTaximetreArrivee(Long.parseLong(res.getString(7)));
//Date hrArrivee = new Date(res.getDate(8).getTime());
Date hrArrivee = new java.sql.Timestamp(res.getTimestamp(8).getTime());
trajet.setHeureArrivee(hrArrivee);
trajet.setPrixCourse(new BigDecimal(res.getString(9)));
trajet.setPrixSupplementaire(new BigDecimal(res.getString(10)));
listeTrajet.add(trajet);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeTrajet;
}
public Trajet find( final int id) {
final Trajet[] result = new Trajet[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_TRAJET_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Trajet(res.getInt(1));
Tournee tournee = new Tournee(res.getInt(2));
result[0].setTournee(tournee);
result[0].setAddresseDepart(res.getString(3));
result[0].setKilometrageTaximetreDepart(Long.parseLong(res.getString(4)));
Date hrDepart = new java.sql.Timestamp(res.getTimestamp(5).getTime());
result[0].setHeureDepart(hrDepart);
result[0].setAddresseArrivee(res.getString(6));
result[0].setKilometrageTaximetreArrivee(Long.parseLong(res.getString(7)));
Date hrArrivee = new java.sql.Timestamp(res.getTimestamp(5).getTime());
result[0].setHeureArrivee(hrArrivee);
result[0].setPrixCourse(new BigDecimal(res.getString(9)));
result[0].setPrixSupplementaire(new BigDecimal(res.getString(10)));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Collection<Trajet> findByChauffeurEtPeriode(final Chauffeur chauffeur, final Periode periode, final ITourneeDao tourneeDao) {
final Collection<Trajet> trajets = new Vector<Trajet>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_TRAJETS_BY_CHAUFFEUR_AND_PERIODE);
smt.setInt(1, chauffeur.getId());
smt.setTimestamp(2, new java.sql.Timestamp(periode.dateDebut().getTime()));
smt.setTimestamp(3,new java.sql.Timestamp(periode.dateFin().getTime()));
res = smt.executeQuery();
while (res.next()) {
int id = res.getInt(1);
Trajet trajet = new Trajet(id);
trajet.setTournee(tourneeDao.find(res.getInt(2)));
trajet.setAddresseDepart(res.getString(3));
trajet.setKilometrageTaximetreDepart(res.getLong(4));
trajet.setHeureDepart(new java.sql.Timestamp(res.getTimestamp(5).getTime()));
trajet.setAddresseArrivee(res.getString(6));
trajet.setKilometrageTaximetreArrivee(res.getLong(7));
trajet.setHeureArrivee(new java.sql.Timestamp(res.getTimestamp(8).getTime()));
trajet.setPrixCourse(res.getBigDecimal(9));
trajet.setPrixSupplementaire(res.getBigDecimal(10));
trajets.add(trajet);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return trajets;
}
public Collection<Trajet> findByTournee(final Tournee tournee) {
final Collection<Trajet> listeTrajet = new Vector<Trajet>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_TRAJETS_BY_TOURNEE);
smt.setInt(1, tournee.getId());
res = smt.executeQuery();
while (res.next()) {
Trajet trajet = new Trajet(res.getInt(1));
trajet.setTournee(tournee);
trajet.setAddresseDepart(res.getString(2));
trajet.setKilometrageTaximetreDepart(Long.parseLong(res.getString(3)));
Date hrDepart = new java.sql.Timestamp(res.getTimestamp(4).getTime());
trajet.setHeureDepart(hrDepart);
trajet.setAddresseArrivee(res.getString(5));
trajet.setKilometrageTaximetreArrivee(Long.parseLong(res.getString(6)));
Date hrArrivee = new java.sql.Timestamp(res.getTimestamp(7).getTime());
trajet.setHeureArrivee(hrArrivee);
trajet.setPrixCourse(new BigDecimal(res.getString(8)));
trajet.setPrixSupplementaire(new BigDecimal(res.getString(9)));
listeTrajet.add(trajet);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeTrajet;
}
}