package dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import util.Dbutil;
import util.PasswordUtil;
import modele.ActionStatus;
import modele.Historique;
import modele.PasswordSetting;
import modele.Ressource;
import modele.RessourceUser;
import modele.Role;
import modele.User;
public class Dao {
static String currentUser = "";
public static ResultSet execute(String sql) {
return Dbutil.getQueryResult(sql, false);
}
private static ActionStatus getAction(ResultSet rs) {
ActionStatus action = null;
if (rs != null) {
try {
while (rs.next()) {
action = new ActionStatus(rs.getBoolean(1), rs.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return action;
}
public static User getUser(String userCode) {
String sql = "CALL getUser('" + userCode + "')";
ResultSet rs = execute(sql);
User u = null;
if (rs != null) {
try {
while (rs.next()) {
u = new User();
u.setUserCode(rs.getString(1));
u.setPassWord(rs.getString(2));
u.setUserDescription(rs.getString(3));
u.setUserEmail(rs.getString(4));
u.setActive(rs.getInt(5));
u.setRole(getRole(rs.getInt(6)));
setUserRessources(u);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return u;
}
public static ArrayList<User> allUsers() {
String sql = "CALL userList()";
ResultSet rs = execute(sql);
ArrayList<User> users = new ArrayList<User>();
if (rs != null) {
try {
while (rs.next()) {
users.add(getUser(rs.getString(1)));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return users;
}
public static Ressource getRessource(int ressourceId) {
String sql = "CALL getRessource('" + ressourceId + "')";
ResultSet rs = execute(sql);
Ressource r = null;
if (rs != null) {
try {
while (rs.next()) {
r = new Ressource();
r.setRessourceId(rs.getInt(1));
r.setRessourceDescription(rs.getString(2));
r.setRessourcePage(rs.getString(3));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return r;
}
public static ArrayList<Ressource> allRessources() {
String sql = "CALL ressourceList()";
ResultSet rs = execute(sql);
ArrayList<Ressource> ressources = new ArrayList<Ressource>();
if (rs != null) {
try {
while (rs.next()) {
ressources.add(getRessource(rs.getInt(1)));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return ressources;
}
public static void setUserRessources(User user) {
String sql = "CALL userressourcelist('" + user.getUserCode() + "')";
ResultSet rs = execute(sql);
Ressource r = null;
if (rs != null) {
try {
while (rs.next()) {
r = new Ressource();
r.setRessourceId(rs.getInt(1));
r.setRessourceDescription(rs.getString(2));
r.setRessourcePage(rs.getString(3));
user.addRessources(r);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static Role getRole(int roleId) {
String sql = "CALL getRole('" + roleId + "')";
ResultSet rs = execute(sql);
Role r = null;
if (rs != null) {
try {
while (rs.next()) {
r = new Role();
r.setRoleId(rs.getInt(1));
r.setRoleDescription(rs.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return r;
}
public static ArrayList<Role> allRoles() {
String sql = "CALL roleList()";
ResultSet rs = execute(sql);
ArrayList<Role> roles = new ArrayList<Role>();
if (rs != null) {
try {
while (rs.next()) {
roles.add(getRole(rs.getInt(1)));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return roles;
}
public static ArrayList<Historique> getUsePasswordHistory(String userCode) {
String sql = "CALL lastuserpasswords('" + userCode + "')";
ResultSet rs = execute(sql);
Historique h = null;
ArrayList<Historique> hpass = new ArrayList<Historique>();
if (rs != null) {
try {
while (rs.next()) {
h = new Historique();
h.setUser(getUser(rs.getString(2)));
h.setPassWord(rs.getString(3));
h.setCreateDate(rs.getDate(4));
hpass.add(h);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return hpass;
}
public static PasswordSetting getpasswordSetting() {
String sql = "CALL passwordSettings()";
ResultSet rs = execute(sql);
PasswordSetting p = null;
if (rs != null) {
try {
while (rs.next()) {
p = new PasswordSetting();
p.setPasswordLenght(rs.getInt(2));
p.setUppercaseContain(rs.getBoolean(3));
p.setLowercaseContain(rs.getBoolean(4));
p.setNumberContain(rs.getBoolean(5));
p.setSpecialCharContain(rs.getBoolean(6));
p.setMaxPassStored(rs.getInt(7));
p.setMaxTry(rs.getInt(8));
p.setSpecialChars(rs.getString(9));
p.setDureeInactivite(rs.getInt(10));
p.setValiditeMotdepasse(rs.getInt(11));
p.setSalt(rs.getInt(12));
p.setAlgorithm(rs.getString(13));
p.setTimeOut(rs.getInt(14));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return p;
}
public static ActionStatus addUser(User user) {
// Hash du mot de passe avec l'algorithme MD5
String password = PasswordUtil.hasher(user.getPassWord(),
getpasswordSetting().getSalt(), getpasswordSetting().getAlgorithm());
String sql = "CALL adduser('" + user.getUserCode() + "','" + password
+ "','" + user.getUserDescription() + "','"
+ user.getUserEmail() + "'," + user.isActive() + ","
+ user.getRole().getRoleId() + ")";
return getAction(execute(sql));
}
public static ActionStatus updateUser(User user) {
String sql = "CALL updateuser('" + user.getUserCode() + "','"
+ user.getPassWord() + "','" + user.getUserDescription()
+ "','" + user.getUserEmail() + "'," + user.isActive() + ","
+ user.getRole().getRoleId() + ")";
return getAction(execute(sql));
}
public static ActionStatus addRole(Role role) {
String sql = "CALL addrole('" + role.getRoleDescription() + "')";
return getAction(execute(sql));
}
public static ActionStatus addRessouce(Ressource ressource) {
String sql = "CALL addressource('"
+ ressource.getRessourceDescription() + "','"
+ ressource.getRessourcePage() + "')";
return getAction(execute(sql));
}
public static ActionStatus updateRessource(Ressource ressource) {
String sql = "CALL updateresssource('" + ressource.getRessourceId()
+ "','" + ressource.getRessourceDescription() + "','"
+ ressource.getRessourcePage() + "')";
return getAction(execute(sql));
}
public static ActionStatus addRessourceUser(RessourceUser rs) {
String sql = "CALL addressourceuser('" + rs.getUserCode() + "',"
+ rs.getRessourceId() + ")";
return getAction(execute(sql));
}
public static ActionStatus validLogin(String userCode, String password) {
// Hashage du mot de passe avant d'effectuer la validation.
String pass = PasswordUtil.hasher(password, getpasswordSetting()
.getSalt(), getpasswordSetting().getAlgorithm());
String sql = "CALL validLogin('" + userCode + "','" + pass + "')";
return getAction(execute(sql));
}
public static ActionStatus updateRole(Role role) {
String sql = "CALL updaterole(" + role.getRoleId() + ",'"
+ role.getRoleDescription() + "')";
return getAction(execute(sql));
}
public static ActionStatus updateRessourceUser(RessourceUser rs) {
String sql = "CALL updateesssourceuser('" + rs.getUserCode() + "',"
+ rs.getRessourceId() + ")";
return getAction(execute(sql));
}
public static ActionStatus removeUser(String userCode) {
String sql = "CALL deleteUser('" + userCode + "')";
return getAction(execute(sql));
}
public static ActionStatus removeRessource(int ressourceId) {
String sql = "CALL deleteRessource(" + ressourceId + ")";
return getAction(execute(sql));
}
public static ActionStatus removeRole(int roleId) {
String sql = "CALL deleteRole(" + roleId + ")";
return getAction(execute(sql));
}
public static ActionStatus removeRessourceUser(String userCode,
int ressorceId) {
String sql = "CALL deleteRessourceuser(" + ressorceId + ",'" + userCode
+ "')";
return getAction(execute(sql));
}
public static boolean userExist(String userCode) {
String sql = "SELECT userExist('" + userCode + "')";
ResultSet rs = execute(sql);
boolean b = false;
if (rs != null) {
try {
while (rs.next()) {
b = rs.getBoolean(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return b;
}
public static boolean userStatus(String userCode) {
String sql = "SELECT userActive('" + userCode + "')";
ResultSet rs = execute(sql);
boolean b = false;
if (rs != null) {
try {
while (rs.next()) {
b = rs.getBoolean(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return b;
}
public static ActionStatus updateSettings(PasswordSetting p) {
String sql = "CALL updatepasswordsetting(" + p.getPasswordLenght()
+ "," + p.isUppercaseContain() + "," + p.isLowercaseContain()
+ "," + p.isNumberContain() + "," + p.isSpecialCharContain()
+ "," + p.getMaxPassStored() + "," + p.getMaxTry() + ",'"
+ p.getSpecialChars() + "'," + p.getDureeInactivite() + ","
+ p.getValiditeMotdepasse() + "," + p.getSalt() +",'"+p.getAlgorithm()+"',"+p.getTimeOut()+")";
return getAction(execute(sql));
}
public static int userCount() {
String sql = "SELECT DISTINCT userCode FROM USER";
ResultSet rs = execute(sql);
int count = 0;
if (rs != null) {
try {
rs.last();
count = rs.getRow();
} catch (SQLException e) {
e.printStackTrace();
}
}
return count;
}
public static ActionStatus changePassword(String userCode,
String oldpassword, String newPassword, String confirmPassword) {
// hash the old pass for verification
String op = PasswordUtil.hasher(oldpassword, getpasswordSetting()
.getSalt(), getpasswordSetting().getAlgorithm());
String sql = "SELECT passordVerification('" + userCode + "','" + op
+ "')";
ResultSet rs = execute(sql);
boolean b = false;
if (rs != null) {
try {
while (rs.next()) {
b = rs.getBoolean(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if (b) { // old pass is correct.
// Check the new password policy.
ActionStatus a = PasswordUtil.complexityCheck(getpasswordSetting(),
newPassword);
if (!a.isStatusValue())
return a;
if (newPassword.equals(confirmPassword)) {
// hash the new pass before store
String npass = PasswordUtil.hasher(newPassword,
getpasswordSetting().getSalt(), getpasswordSetting().getAlgorithm());
if (motDePasseDisponible(userCode, npass)) {
String stmt = "CALL userPasswordChange('" + userCode
+ "','" + npass + "')";
return getAction(execute(stmt));
} else {
return new ActionStatus(false,
"Le nouveau mot de passe ne doit pas appartenir aux "
+ getpasswordSetting().getMaxPassStored()
+ " derniers mot de passe utilis�s");
}
} else {
return (new ActionStatus(false,
"Le nouveau mot de passe et la confirmation ne sont pas identiques."));
}
} else {
return (new ActionStatus(false,
"L'ancien mot de passe n'est pas correct."));
}
}
public static boolean motDePasseDisponible(String userCode, String password) {
String sql = "SELECT motDePasseDisponible('" + userCode + "','"
+ password + "')";
ResultSet rs = execute(sql);
boolean b = false;
if (rs != null) {
try {
while (rs.next()) {
b = rs.getBoolean(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return b;
}
public static ArrayList<User> lockedUsers() {
ArrayList<User> users = new ArrayList<>();
String sql = "CALL lockUsers()";
ResultSet rs = execute(sql);
if (rs != null) {
try {
while (rs.next()) {
users.add(getUser(rs.getString(1)));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return users;
}
public static ActionStatus unlockUser(String userCode) {
String sql = "CALL alterUserStatus('" + userCode + "', 1)";
return getAction(execute(sql));
}
public static void setCurrentUser(String userCode) {
Dao.currentUser = userCode;
}
public static String getCurrentUser() {
return Dao.currentUser;
}
public static ArrayList<RessourceUser> allRessourceUser() {
ArrayList<RessourceUser> a = new ArrayList<>();
String sql = "CALL allRessourceuser()";
ResultSet rs = execute(sql);
if (rs != null) {
try {
while (rs.next()) {
a.add(new RessourceUser(rs.getString(1), rs.getInt(2)));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return a;
}
public static String emailDisponible(String userEmail) {
String sql = "SELECT distinct userCode from user where userEmail='"
+ userEmail + "'";
String user=null;
ResultSet rs = execute(sql);
if (rs != null) {
try {
while (rs.next()) {
user=rs.getString(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return (user);
}
}