package com.netflox.dao;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.netflox.model.Bon;
import com.netflox.model.Utilisateur;
import com.netflox.model.Vente;
public class MysqlGestionVente implements GestionVente {
private Connection getConnection(){
/* Connexion à la base de données */
String url = "jdbc:mysql://localhost:3306/netflox";
String utilisateur = "root";
String motDePasse = "";
Connection connexion = null;
/* Chargement du driver JDBC pour MySQL */
try {
Class.forName( "com.mysql.jdbc.Driver" );
} catch ( ClassNotFoundException e ) {
/* Gérer les éventuelles erreurs ici. */
}
try {
connexion = (Connection) DriverManager.getConnection( url, utilisateur, motDePasse );
} catch ( SQLException e ) {
/* Gérer les éventuelles erreurs ici */
}
return connexion;
}
private void closeConnection(Connection connexion){
if ( connexion != null )
try {
/* Fermeture de la connexion */
connexion.close();
} catch ( SQLException ignore ) {
/* Si une erreur survient lors de la fermeture, il suffit de l'ignorer. */
}
}
@Override
public int ajouterVente(Vente v) {
int id = -1;
Connection connex;
connex = getConnection();
String insert = "INSERT INTO `vente`(`idFilm`, `date`, `prix`, `idClient`, `type`) VALUES (?,?,?,?,?)";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(insert);
preStat.setString(1, v.getIdFilm());
preStat.setLong(2, v.getDate());
preStat.setInt(3, v.getPrix());
preStat.setInt(4, v.getIdClient());
preStat.setString(5, v.getType());
preStat.executeUpdate(preStat.asSql(),preStat.RETURN_GENERATED_KEYS);
//Les id auto-générées sont retournées sous forme de ResultSet
ResultSet ids = preStat.getGeneratedKeys();
if(ids.next()){
id = (int)((long)ids.getObject(1));
}
} catch (SQLException ignore) {
System.out.println("erreur ajouterVente : " + ignore);
}
closeConnection(connex);
return id;
}
@Override
public void ajouterBon(Bon b) {
Connection connex;
connex = getConnection();
String insert = "INSERT INTO `bon`(`id`) VALUES (?)";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(insert);
preStat.setInt(1, b.getId());
preStat.executeUpdate();
}catch (SQLException ignore) {
System.out.println("erreur ajouterBon : " + ignore);
}
closeConnection(connex);
}
@Override
/*
* retourne une arraylist composée des id des achat de l'utilisateur passé en paramètre
* @see com.netflox.dao.GestionVente#venteUtilisateur(com.netflox.model.Utilisateur)
*/
public ArrayList<Vente> venteUtilisateur(Utilisateur c) {
ArrayList<Vente> l = new ArrayList<Vente>();
Connection connex;
connex = getConnection();
String select = "SELECT * FROM `vente` WHERE `idClient` = ?";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(select);
preStat.setInt(1, c.getId());
ResultSet rs = preStat.executeQuery();
while(rs.next()){
Vente v = new Vente();
v.setId(rs.getInt(1));
v.setIdFilm(rs.getString(2));
v.setDate(rs.getLong(3));
v.setPrix(rs.getInt(4));
v.setIdClient(rs.getInt(5));
v.setType(rs.getString(6));
l.add(v);
}
} catch (SQLException ignore) {
System.out.println("erreur venteUtilisateur : " + ignore);
}
closeConnection(connex);
return l;
}
@Override
public ArrayList<String[]> filmLoueUtilisateur(Utilisateur c) {
ArrayList<String[]> l = new ArrayList<String[]>();
Connection connex;
connex = getConnection();
String select = "SELECT `idFilm`,`date` FROM `vente` WHERE `idClient` = ? AND `type` = ?";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(select);
preStat.setInt(1, c.getId());
preStat.setString(2, "l");
ResultSet rs = preStat.executeQuery();
while(rs.next()){
String loc[] = {rs.getString(1) , String.valueOf(rs.getLong(2))};
l.add(loc);
}
} catch (SQLException ignore) {
System.out.println("erreur filmLoueUtilisateur : " + ignore);
}
closeConnection(connex);
return l;
}
@Override
/*
* retourne la liste des films vendu
* @see com.netflox.dao.GestionVente#filmVendu()
*/
public ArrayList<String> filmsInfosVenteLoueBon(String type) {
ArrayList<String> l = new ArrayList<String>();
Connection connex;
connex = getConnection();
String select = "SELECT `idFilm` FROM `vente` WHERE `type` = ?";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(select);
preStat.setString(1, type);
ResultSet rs = preStat.executeQuery();
while(rs.next()){
l.add(rs.getString(1));
}
} catch (SQLException ignore) {
System.out.println("erreur filmVendu : " + ignore);
}
closeConnection(connex);
return l;
}
@Override
public int bonValide(int id) {
Connection connex;
connex = getConnection();
String select = "SELECT `utilise` FROM `bon` WHERE `id`= ?";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(select);
preStat.setInt(1, id);
ResultSet rs = preStat.executeQuery();
while(rs.next()){
if(rs.getInt(1) == 0){
int montant = -1;
String select2 = "SELECT `prix` FROM `vente` WHERE `id` = ?";
PreparedStatement preStat2 = (PreparedStatement) connex.prepareStatement(select2);
preStat2.setInt(1, id);
ResultSet rs2 = preStat2.executeQuery();
while(rs2.next()){
montant = rs2.getInt(1);
}
return montant;
}
}
} catch (SQLException ignore) {
System.out.println("erreur bonValide : " + ignore);
}
closeConnection(connex);
return -1;
}
@Override
public void updateBon(Bon b) {
Connection connex;
connex = getConnection();
String select = "UPDATE `bon` SET `idFilm`=?,`dateU`=?,`type`=?,`idClientU`=?,`utilise`=? WHERE `id` = ?";
try {
PreparedStatement preStat = (PreparedStatement) connex.prepareStatement(select);
preStat.setString(1, b.getIdFilm());
preStat.setLong(2, b.getDateU());
preStat.setString(3, b.getType());
preStat.setInt(4, b.getIdClientU());
preStat.setInt(5, b.getUtilise());
preStat.setInt(6, b.getId());
preStat.executeUpdate();
} catch (SQLException ignore) {
System.out.println("erreur updateBon : " + ignore);
}
closeConnection(connex);
}
}