@Override
public List<Paiement> listerPaiements(Adherent adherent) {
List<Paiement> listePaiements = new ArrayList<Paiement>();
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement("SELECT * FROM `paiement` WHERE `numLicence`=?");
stmt.setString(1, adherent.getLicence());
ResultSet results = stmt.executeQuery();
while (results.next()) {
Paiement paiement =
new Paiement(
results.getInt("idPaiement"),
results.getBoolean("payer"),
results.getString("banque"),
results.getString("numCheque"),
results.getDate("echeance"),
results.getFloat("montant"));
listePaiements.add(paiement);
}
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listePaiements;
}
public int CompterCommentaire(Integer idArticle) {
int compt = 0;
try {
/** ** Creation de la connexion *** */
Connection connection = DataSourceProvider.getDataSource().getConnection();
/** ** Utilisation de la connexion *** */
PreparedStatement stmt =
connection.prepareStatement(
"" + "SELECT count(*) FROM t_comment WHERE T_ARTICLE_ART_ID=?");
stmt.setInt(1, idArticle);
ResultSet results = stmt.executeQuery();
while (results.next()) {
compt = results.getInt("count(*)");
}
/** ** Fermer la connexion *** */
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return compt;
}
public List<Paiement> listerPaiementsAEncaisser(java.util.Date date) {
List<Paiement> listePaiements = new ArrayList<Paiement>();
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"SELECT * FROM `paiement` WHERE YEAR(`echeance`)=? AND MONTH(`echeance`)=?");
stmt.setInt(1, date.getYear() + 1900);
stmt.setInt(2, date.getMonth() + 1);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Paiement paiement =
new Paiement(
results.getInt("idPaiement"),
results.getBoolean("payer"),
results.getString("banque"),
results.getString("numCheque"),
results.getDate("echeance"),
results.getFloat("montant"));
listePaiements.add(paiement);
}
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listePaiements;
}
public List<Commentaire> listerCommentaire(Integer idArticle) {
List<Commentaire> listeCommentaires = new ArrayList<Commentaire>();
try {
/** ** Creation de la connexion *** */
Connection connection = DataSourceProvider.getDataSource().getConnection();
/** ** Utilisation de la connexion *** */
PreparedStatement stmt =
connection.prepareStatement("" + "SELECT * FROM t_comment WHERE T_ARTICLE_ART_ID=?");
stmt.setInt(1, idArticle);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Commentaire commentaire =
new Commentaire(
results.getInt("COM_ID"),
results.getString("COM_TEXT"),
results.getString("COM_NAME"),
results.getString("COM_EMAIL"),
results.getInt("T_ARTICLE_ART_ID"));
listeCommentaires.add(commentaire);
}
/** ** Fermer la connexion *** */
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listeCommentaires;
}
public List<Participer> listerParticipationforEtudiant(String identifiant) {
List<Participer> listeParticipationforEtudiant = new ArrayList<Participer>();
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"SELECT participer.*, challenge.*, etudiant.*, adresse.* FROM adresse INNER JOIN (challenge INNER JOIN (participer INNER JOIN etudiant ON participer.id_etudiant = etudiant.id_etudiant)ON participer.id_challenge = challenge.id_challenge) ON adresse.id_adr = challenge.id_adresse WHERE participer.id_etudiant = ? ORDER BY challenge.date_challenge DESC");
stmt.setString(1, identifiant);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Participer participer =
new Participer(
results.getString("participer.id_etudiant"),
results.getString("participer.id_challenge"),
results.getString("participer.presence"));
participer.setEtudiant(
new Etudiant(
results.getString("etudiant.id_etudiant"),
results.getString("etudiant.nom_etudiant"),
results.getString("etudiant.prenom_etudiant"),
results.getString("etudiant.classe_etudiant"),
results.getString("etudiant.tel_etudiant"),
results.getString("etudiant.mail_etudiant"),
results.getString("etudiant.photo_etudiant"),
results.getBoolean("etudiant.cotisation_etudiant"),
results.getBoolean("etudiant.certificat_etudiant"),
results.getString("licence_etudiant")));
participer.setChallenge(
new Challenge(
results.getString("challenge.id_challenge"),
results.getString("challenge.nom_challenge"),
results.getDate("challenge.date_challenge"),
results.getTime("challenge.heure_challenge"),
results.getString("challenge.description_challenge"),
results.getString("challenge.id_adresse")));
participer
.getChallenge()
.setAdresse(
new Adresse(
results.getString("adresse.id_adr"),
results.getString("adresse.site_adr"),
results.getString("adresse.num_adr"),
results.getString("adresse.rue_adr"),
results.getString("adresse.cp_adr"),
results.getString("adresse.ville_adr"),
results.getString("adresse.pays_adr")));
listeParticipationforEtudiant.add(participer);
}
// Fermer la connexion
results.close();
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listeParticipationforEtudiant;
}
public List<Participer> listerParticipationByChallenge(String id_challenge) {
List<Participer> listeParticipationByChallenge = new ArrayList<Participer>();
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"SELECT participer.*, challenge.*, adresse.* FROM participer INNER JOIN (challenge INNER JOIN adresse ON challenge.id_adresse = adresse.id_adr) ON participer.id_challenge = challenge.id_challenge WHERE participer.id_challenge = ?");
stmt.setString(1, id_challenge);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Participer participer =
new Participer(
results.getString("participer.id_etudiant"),
results.getString("participer.id_challenge"),
results.getString("participer.presence"));
participer.setChallenge(
new Challenge(
results.getString("challenge.id_challenge"),
results.getString("challenge.nom_challenge"),
results.getDate("challenge.date_challenge"),
results.getTime("challenge.heure_challenge"),
results.getString("challenge.description_challenge"),
results.getString("challenge.id_adresse")));
participer
.getChallenge()
.setAdresse(
new Adresse(
results.getString("adresse.id_adr"),
results.getString("adresse.site_adr"),
results.getString("adresse.num_adr"),
results.getString("adresse.rue_adr"),
results.getString("adresse.cp_adr"),
results.getString("adresse.ville_adr"),
results.getString("adresse.pays_adr")));
listeParticipationByChallenge.add(participer);
}
// Fermer la connexion
results.close();
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listeParticipationByChallenge;
}
public void afficherNotification() {
// TODO Auto-generated method stub
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt = connection.prepareStatement("UPDATE flag SET notification = ?");
stmt.setBoolean(1, true);
stmt.executeUpdate();
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void nePasEncaisserPaiement(Integer idPaiement) {
// TODO Auto-generated method stub
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement("UPDATE `paiement` SET `payer`='0' WHERE `idPaiement`=?");
stmt.setInt(1, idPaiement);
stmt.executeUpdate();
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void ModifierPresence(String id_etudiant, String id_challenge, String presence) {
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"UPDATE participer SET presence = ? WHERE id_etudiant = ? AND id_challenge = ?");
stmt.setString(1, presence);
stmt.setString(2, id_etudiant);
stmt.setString(3, id_challenge);
stmt.executeUpdate();
// Fermer la connexion
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void AjouterParticipationToChallenge(Participer participer) {
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"INSERT INTO participer (id_etudiant, id_challenge, presence) VALUES (?, ?, ?)");
stmt.setString(1, participer.getId_etudiant());
stmt.setString(2, participer.getId_challenge());
stmt.setString(3, participer.getPresence());
stmt.executeUpdate();
// Fermer la connexion
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public boolean etatNotification() {
// TODO Auto-generated method stub
boolean res = false;
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
Statement stmt = connection.createStatement();
ResultSet results = stmt.executeQuery("SELECT * FROM `flag`");
results.next();
res = results.getBoolean("notification");
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
@Override
public void ajouterPaiement(Adherent adherent, Paiement paiement) {
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"INSERT INTO `paiement`(`payer`, `banque`, `numCheque`, `echeance`, `montant`,`numLicence`) VALUES(?,?,?,?,?,?)");
stmt.setBoolean(1, paiement.isPayer());
stmt.setString(2, paiement.getBanque());
stmt.setString(3, paiement.getNumCheque());
stmt.setDate(4, new Date(paiement.getEcheance().getTime()));
stmt.setFloat(5, paiement.getMontant());
stmt.setString(6, adherent.getLicence());
stmt.executeUpdate();
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void ajouterCommentaire(Commentaire commentaire) {
/** ** Creation de la connexion *** */
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
/** ** Utilisation de la connexion *** */
PreparedStatement stmt =
connection.prepareStatement(
""
+ "INSERT INTO t_comment(COM_TEXT,COM_NAME,COM_EMAIL,T_ARTICLE_ART_ID) VALUES (?,?,?,?)");
stmt.setString(1, commentaire.getText());
stmt.setString(2, commentaire.getName());
stmt.setString(3, commentaire.getEmail());
stmt.setInt(4, commentaire.getIdArticle());
stmt.executeUpdate();
/** ** Fermer la connexion *** */
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public DataSource getDataSource(String name) {
DataSourceProvider prov = getDataSourceProvider(name);
if (prov != null) return prov.getDataSource();
return null;
}
