package DAO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import utility.MD5;
import Interface.UserDAOInterface;
import beans.User;
public class JDBCUserDAO implements UserDAOInterface {
public DataSource getDataSource() throws NamingException{
Context namingContext = new InitialContext();
DataSource dataSource = (DataSource)namingContext.lookup ("java:comp/env/jdbc/testDatasource");
return dataSource;
}
@Override
public void create(String login, String mdp, String email) {
Connection con=null;
try{
con= getDataSource().getConnection();
PreparedStatement pstmt = con.prepareStatement("Insert into UTILISATEUR (login,mdp,compte_valider,email,administrateur,news_letter,date_fin_abonnement) values(?,?,?,?,0,0,now())");
pstmt.setString(1, login);
pstmt.setString(2, MD5.encode(mdp));
pstmt.setInt(3, 0);
pstmt.setString(4, email);
pstmt.executeUpdate();
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void activate(String login) {
Connection con=null;
try{
con= getDataSource().getConnection();
PreparedStatement pstmt = con.prepareStatement("Update UTILISATEUR set compte_valider=1 where login=?");
pstmt.setString(1, login);
pstmt.executeUpdate();
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public boolean checkIfExistAndValidate(String login, String mdp) {
Connection con=null;
try{
con= getDataSource().getConnection();
PreparedStatement pstmt = con.prepareStatement("select mdp from UTILISATEUR where login=? and compte_valider=1");
pstmt.setString(1, login);
ResultSet res =pstmt.executeQuery();
if(res.next()){
if(res.getString(1).equals(MD5.encode(mdp))){
return true;
}
}
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return false;
}
@Override
public User getUser(String login) {
Connection con=null;
User user=null;
try{
con= getDataSource().getConnection();
user=new User();
PreparedStatement pstmt = con.prepareStatement("select date_fin_abonnement, email,administrateur,news_letter from UTILISATEUR where login=?");
pstmt.setString(1, login);
ResultSet res =pstmt.executeQuery();
if(res.next()){
user.setLogin(login);
user.setDateFinAbonnement(res.getDate(1));
user.setEmail(res.getString(2));
user.setAdministrateur(res.getInt(3));
user.setNewsLetter(res.getInt(4));
return user;
}
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return user;
}
@Override
public String createAbonnement(String login,String duree) {
Connection con=null;
String currentTime=null;
try{
con= getDataSource().getConnection();
PreparedStatement pstmt = con.prepareStatement("Insert into ABONNEMENT (login,duree,code_utilise,date_souscription) values(?,?,?,?)");
pstmt.setString(1, login);
pstmt.setInt(2, Integer.parseInt(duree));
pstmt.setInt(3, 0);
java.util.Date dt = new java.util.Date();
java.text.SimpleDateFormat sdf =
new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
currentTime = sdf.format(dt);
pstmt.setString(4,currentTime);
pstmt.executeUpdate();
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return MD5.encode(login+duree+currentTime+".0");
}
@Override
public boolean codeVerification(User user,String code) {
Connection con=null;
String login=user.getLogin();
try{
con= getDataSource().getConnection();
PreparedStatement pstmt = con.prepareStatement("select duree,date_souscription,num_abonnement from ABONNEMENT where login=? and code_utilise=0");
pstmt.setString(1, login);
ResultSet res =pstmt.executeQuery();
while(res.next()){
//Verification du code
if(MD5.encode(login+res.getString(1)+res.getString(2)).equals(code)){
Statement stmt = con.createStatement();
stmt.executeUpdate("update ABONNEMENT set code_utilise=1 where num_abonnement="+res.getInt(3));
java.util.Date dt = new java.util.Date();
long time=dt.getTime();
dt.setTime(0);
dt.setMonth(res.getInt(1));
long timeToAdd=dt.getTime();
dt.setTime(time+timeToAdd);
java.text.SimpleDateFormat sdf =
new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String date = sdf.format(dt);
stmt.executeUpdate("update UTILISATEUR set date_fin_abonnement='"+date+"' where login='"+login+"'");
user.setDateFinAbonnement(dt);
return true;
}
}
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return false;
}
@Override
public void newsLetter(boolean activation,String login) {
Connection con=null;
try{
con= getDataSource().getConnection();
PreparedStatement pstmt;
if(activation){
pstmt = con.prepareStatement("update UTILISATEUR set news_letter=1 where login=?");
}else{
pstmt = con.prepareStatement("update UTILISATEUR set news_letter=0 where login=?");
}
pstmt.setString(1, login);
pstmt.executeUpdate();
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}