package dao.implementation.mysql.course;
import modele.chauffeur.ReferenceChauffeur;
import modele.course.Tournee;
import modele.voiture.Voiture;
import dao.ITourneeDao;
import dao.implementation.mysql.AccessMySql;
import dao.implementation.mysql.chauffeur.MySqlReferenceChauffeurDao;
import dao.implementation.mysql.voiture.MySqlVoitureDao;
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.Date;
/**
* <p> MySqlTourneeDao est une classe Dao qui implemente les m�thodes de l'interface ITourneeDao :</>
* <ul>
* <li> public void delete(final Tournee tournee) : supprime la tourn�e</li>
* <li> public Tournee find(final int id) cherche la tourn�e par son id</li>
* <li> public ArrayList<Tournee> findAll() : charge depuis la base de donn�es et renvoie la liste de toutes les tourn�e;</li>
* <li> public Tournee insert(final Tournee tournee) : insere la tourn�e </li>
* </ul>
* <p> Elle �tablit la connexion avec la base de donn�es et la synchronise</p>
* <p> Les m�thodes de cette classe permettent soit ajouter, modifier, supprimer ou chercher le(s) enregistrement(s)...</p>
*
* @author Kasandra
*
*/
public class MySqlTourneeDao implements ITourneeDao {
private static final String INSERT_TOURNEE = "insert into ttourneechauf (idChauf, idVoit, dateDebServiceTrnee, dateFinServiceTrnee, dateInterrupServTrnee, kmTabBordDebTrnee, kmTabBordFinTrnee, kmTxmtDebTrnee, kmTxmtFinTrnee, kmEnChargDebTrnee, kmEnChrgFinTrnee, nbrPriseDebTrnee, nbrPriseFinTrnee, chuteDebTrnee, chuteFinTrnee, montRecetteJrnlierTrnee) values (?,?, ?,?,?,?, ?,?,?,?,?,?,?,?,?,?)";
private static final String UPDATE_TOURNEE = "update ttourneechauf set idChauf = ?, idVoit = ?, dateDebServiceTrnee = ?, dateFinServiceTrnee = ?, dateInterrupServTrnee = ?, kmTabBordDebTrnee = ?, kmTabBordFinTrnee = ?, kmTxmtDebTrnee = ?, kmTxmtFinTrnee = ?, kmEnChargDebTrnee = ?, kmEnChrgFinTrnee = ?, nbrPriseDebTrnee = ?, nbrPriseFinTrnee = ?, chuteDebTrnee = ?, chuteFinTrnee = ?, montRecetteJrnlierTrnee = ? where idTrnee = ?";
private static final String DELETE_TOURNEE = "delete from ttourneechauf where idTrnee = ?";
private static final String GET_ALL_TOURNEE = "select idTrnee, idChauf, idVoit, dateDebServiceTrnee, dateFinServiceTrnee, dateInterrupServTrnee, kmTabBordDebTrnee, kmTabBordFinTrnee, kmTxmtDebTrnee, kmTxmtFinTrnee, kmEnChargDebTrnee, kmEnChrgFinTrnee, nbrPriseDebTrnee, nbrPriseFinTrnee, chuteDebTrnee, chuteFinTrnee, montRecetteJrnlierTrnee from ttourneechauf";
private static final String GET_TOURNEE_BY_ID = GET_ALL_TOURNEE + " where idTrnee = ?";
public void delete(final Tournee tournee) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_TOURNEE);
smt.setInt(1, tournee.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Tournee find(final int id) {
final Tournee[] result = new Tournee[1];
final MySqlReferenceChauffeurDao referenceChauffeurDao = getReferenceChauffeurDao();
final MySqlVoitureDao voitureDao = getVoitureDao();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_TOURNEE_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
int tourneeId = res.getInt(1);
ReferenceChauffeur chauffeur = referenceChauffeurDao.find(res.getInt(2));
Voiture voiture = voitureDao.find(res.getInt(3));
Date dateDebutService = new java.util.Date(res.getTimestamp(4).getTime());
Date dateFinService = new java.util.Date(res.getTimestamp(5).getTime());
Date dateInterruptionService = new java.util.Date(res.getTimestamp(6).getTime());
long kilometrageDebutTournee = res.getLong(7);
long kilometrageVoitureFinTournee = res.getLong(8);
long kmTaximetreDebut = res.getLong(9);
long kmTaximetreFin = res.getLong(10);
long kmEnChargeDebut = res.getLong(11);
long kmEnChargeFin = res.getLong(12);
int nombrePriseDebut = res.getInt(13);
int nombrePriseFin = res.getInt(14);
BigDecimal chuteDebut = res.getBigDecimal(15);
BigDecimal chuteFin = res.getBigDecimal(16);
BigDecimal recetteJournali�re = res.getBigDecimal(17);
Tournee tournee = new Tournee(tourneeId);
tournee.setChauffeur(chauffeur);
tournee.setVoiture(voiture);
tournee.setDateDebutService(dateDebutService);
tournee.setKilometrageVoitureDebutTournee(kilometrageDebutTournee);
tournee.setKmTaximetreDebut(kmTaximetreDebut);
tournee.setKmEnChargeDebut(kmEnChargeDebut);
tournee.setNombrePriseDebut(nombrePriseDebut);
tournee.setChuteDebut(chuteDebut);
tournee.setChuteFin(chuteFin);
tournee.setDateFinService(dateFinService);
tournee.setDateInterruptionService(dateInterruptionService);
tournee.setKilometrageVoitureFinTournee(kilometrageVoitureFinTournee);
tournee.setKmEnChargeFin(kmEnChargeFin);
tournee.setKmTaximetreFin(kmTaximetreFin);
tournee.setNombrePriseFin(nombrePriseFin);
tournee.setRecetteJournali�re(recetteJournali�re);
result[0] = tournee;
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public ArrayList<Tournee> findAll() {
final ArrayList<Tournee> listeTournee = new ArrayList<Tournee>();
final MySqlReferenceChauffeurDao referenceChauffeurDao = getReferenceChauffeurDao();
final MySqlVoitureDao voitureDao = getVoitureDao();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_TOURNEE);
res = smt.executeQuery();
while (res.next()) {
int tourneeId = res.getInt(1);
ReferenceChauffeur chauffeur = referenceChauffeurDao.find(res.getInt(2));
Voiture voiture = voitureDao.find(res.getInt(3));
Date dateDebutService = new java.util.Date(res.getTimestamp(4).getTime());
Date dateFinService = new java.util.Date(res.getTimestamp(5).getTime());
Date dateInterruptionService = new java.util.Date(res.getTimestamp(6).getTime());
long kilometrageDebutTournee = res.getLong(7);
long kilometrageVoitureFinTournee = res.getLong(8);
long kmTaximetreDebut = res.getLong(9);
long kmTaximetreFin = res.getLong(10);
long kmEnChargeDebut = res.getLong(11);
long kmEnChargeFin = res.getLong(12);
int nombrePriseDebut = res.getInt(13);
int nombrePriseFin = res.getInt(14);
BigDecimal chuteDebut = res.getBigDecimal(15);
BigDecimal chuteFin = res.getBigDecimal(16);
BigDecimal recetteJournali�re = res.getBigDecimal(17);
Tournee tournee = new Tournee(tourneeId);
tournee.setChauffeur(chauffeur);
tournee.setVoiture(voiture);
tournee.setDateDebutService(dateDebutService);
tournee.setKilometrageVoitureDebutTournee(kilometrageDebutTournee);
tournee.setKmTaximetreDebut(kmTaximetreDebut);
tournee.setKmEnChargeDebut(kmEnChargeDebut);
tournee.setNombrePriseDebut(nombrePriseDebut);
tournee.setChuteDebut(chuteDebut);
tournee.setChuteFin(chuteFin);
tournee.setDateFinService(dateFinService);
tournee.setDateInterruptionService(dateInterruptionService);
tournee.setKilometrageVoitureFinTournee(kilometrageVoitureFinTournee);
tournee.setKmEnChargeFin(kmEnChargeFin);
tournee.setKmTaximetreFin(kmTaximetreFin);
tournee.setNombrePriseFin(nombrePriseFin);
tournee.setRecetteJournali�re(recetteJournali�re);
tournee.setTrajets(new MySqlTrajetDao().findByTournee(tournee));
listeTournee.add(tournee);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeTournee;
}
public Tournee insert(final Tournee tournee) {
final Tournee[] result = new Tournee[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_TOURNEE);
psmt.setInt(1,tournee.getChauffeur().getId());
psmt.setInt(2,tournee.getVoiture().getId());
psmt.setTimestamp(3, new java.sql.Timestamp(tournee.getDateDebutService().getTime()));
psmt.setTimestamp(4, new java.sql.Timestamp(tournee.getDateFinService().getTime()));
psmt.setTimestamp(5, new java.sql.Timestamp(tournee.getDateInterruptionService().getTime()));
psmt.setLong(6, tournee.getKilometrageVoitureDebutTournee());
psmt.setLong(7, tournee.getKilometrageVoitureFinTournee());
psmt.setLong(8, tournee.getKmTaximetreDebut());
psmt.setLong(9, tournee.getKmTaximetreFin());
psmt.setLong(10, tournee.getKmEnChargeDebut());
psmt.setLong(11, tournee.getKmEnChargeFin());
psmt.setInt(12, tournee.getNombrePriseDebut());
psmt.setInt(13, tournee.getNombrePriseFin());
psmt.setBigDecimal(14, tournee.getChuteDebut());
psmt.setBigDecimal(15, tournee.getChuteFin());
psmt.setBigDecimal(16, tournee.getRecetteJournali�re());
psmt.executeUpdate();
}
finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select LAST_INSERT_ID() from ttourneechauf");
if (res.next()) {
result[0] = find(res.getInt(1));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
MySqlVoitureDao voitureDao = new MySqlVoitureDao();
voitureDao.update(tournee.getVoiture());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Tournee update(final Tournee tournee) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_TOURNEE);
smt.setInt(1, tournee.getChauffeur().getId());
smt.setInt(2, tournee.getVoiture().getId());
smt.setDate(3, new java.sql.Date(tournee.getDateDebutService().getTime()));
smt.setDate(4, new java.sql.Date(tournee.getDateFinService().getTime()));
smt.setDate(5, new java.sql.Date(tournee.getDateInterruptionService().getTime()));
smt.setLong(6, tournee.getKilometrageVoitureDebutTournee());
smt.setLong(7, tournee.getKilometrageVoitureFinTournee());
smt.setLong(8, tournee.getKmTaximetreDebut());
smt.setLong(9, tournee.getKmTaximetreFin());
smt.setLong(10, tournee.getKmEnChargeDebut());
smt.setLong(11, tournee.getKmEnChargeFin());
smt.setInt(12, tournee.getNombrePriseDebut());
smt.setInt(13, tournee.getNombrePriseFin());
smt.setBigDecimal(14, tournee.getChuteDebut());
smt.setBigDecimal(15, tournee.getChuteFin());
smt.setBigDecimal(16, tournee.getRecetteJournali�re());
smt.setInt(17, tournee.getId());
smt.executeUpdate();
MySqlVoitureDao voitureDao = new MySqlVoitureDao();
voitureDao.update(tournee.getVoiture());
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return tournee;
}
private MySqlReferenceChauffeurDao getReferenceChauffeurDao() {
return new MySqlReferenceChauffeurDao();
}
private MySqlVoitureDao getVoitureDao() {
return new MySqlVoitureDao();
}
@Override
public Tournee findByTournee(int idTournee) {
// TODO Auto-generated method stub
return null;
}
}