/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package club.data;
import club.beans.AdministratorBean;
import club.ulti.FormatConverter;
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;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author sior
*/
public class DataAdmin {
private static Connection conn;
private static Statement stmt;
private static PreparedStatement psmt;
private static ResultSet rs;
public List<AdministratorBean> getAllAdmins() { // get all record in table Member and return in a List
List<AdministratorBean> list = new ArrayList();
String query = "SELECT * FROM Administrator";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
AdministratorBean bean = new AdministratorBean();
bean.setAccount(rs.getString("Account"));
bean.setPassword(rs.getString("Password"));
bean.setFullname(rs.getString("Fullname"));
bean.setAdminID(rs.getInt("AdminID"));
bean.setStatus(rs.getInt("Status"));
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAdmin.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public static AdministratorBean getAuthentication(String account) {
AdministratorBean admin = null;
String query = "SELECT * FROM Administrator WHERE Account='" + account+"'";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
admin = new AdministratorBean();
admin.setAccount(rs.getString("Account"));
admin.setAdminID(rs.getInt("AdminID"));
admin.setFullname(rs.getNString("Fullname"));
admin.setPassword(rs.getString("Password"));
admin.setStatus(rs.getInt("Status"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return admin;
}
public static boolean isValidInfo(String account, String password){
boolean flag = false;
String query = "SELECT COUNT(*) as total FROM Administrator WHERE Account=? AND Password=?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setString(1, account);
psmt.setString(2, password);
rs = psmt.executeQuery();
rs.next();
if (rs.getInt("total") == 1){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEvent.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static boolean isExistID(String acc, int ID){
boolean flag = false;
String query = "SELECT COUNT(*) as total FROM Administrator WHERE Account=? AND AdminID=?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setString(1, acc);
psmt.setInt(2, ID);
rs = psmt.executeQuery();
rs.next();
if (rs.getInt("total") == 1){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEvent.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static AdministratorBean getAdminByID(int id) { // find a member by ID and return as Bean
AdministratorBean bean = null;
String query = "SELECT * FROM Administrator WHERE AdminID=" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bean = new AdministratorBean();
bean.setAccount(rs.getString("Account"));
bean.setPassword(rs.getString("Password"));
bean.setFullname(rs.getString("Fullname"));
bean.setAdminID(rs.getInt("AdminID"));
bean.setStatus(rs.getInt("Status"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAdmin.class.getName()).log(Level.SEVERE, null, ex);
}
}
return bean;
}
public static boolean updateAdminByID(String field, String value, int id) {
boolean flag = false;
String query;
if (field.equalsIgnoreCase("password")){
query = "UPDATE Administrator SET Password = ? WHERE AdminID = ?";
} else if (field.equalsIgnoreCase("Status")) {
query = "UPDATE Administrator SET Status = ? WHERE AdminID = ?";
} else{
query = "UPDATE Administrator SET Fullname = ? WHERE AdminID = ?";
}
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
if (field.equalsIgnoreCase("Status")){
psmt.setInt(1, Integer.parseInt(value));
} else{
psmt.setString(1, value);
}
psmt.setInt(2, id);
int i = psmt.executeUpdate();
if (i == 1) {
flag = true;
}
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAdmin.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static boolean insertAdmin(String Account, String Password, String Fullname, int Status) {
boolean flag = false;
String query = "INSERT INTO Administrator(Account, Password, Fullname, Status) VALUES(?,?,?,?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setString(1, Account);
psmt.setString(2, Password);
psmt.setString(3, Fullname);
psmt.setInt(4, Status);
int i = psmt.executeUpdate();
conn.commit();
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
}