public List<CustomerEntity> retrieveAllCustomer() {
// declare local variables
ArrayList<CustomerEntity> list = new ArrayList<CustomerEntity>();
ResultSet rs = null;
DBController db = new DBController();
String dbQuery;
// Step 1 - connect to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "SELECT * FROM customer where enabled = 1";
// step 3 - using DBController readRequest method
rs = db.readRequest(dbQuery);
try {
while (rs.next()) {
CustomerEntity customer = convertToCustomer(rs);
list.add(customer);
}
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return list;
}
public boolean deleteTeller(String uid) {
// declare local variables
boolean success = false;
DBController db = new DBController();
String dbQuery;
PreparedStatement pstmt;
db.getConnection();
dbQuery = "UPDATE customer SET banker = '0' WHERE uid = ? and enabled = 1";
pstmt = db.getPreparedStatement(dbQuery);
try {
pstmt.setString(1, uid);
if (pstmt.executeUpdate() == 1) success = true;
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return success;
}
public CustomerEntity getCustomerByUid(String uid) {
// declare local variables
CustomerEntity customer = new CustomerEntity();
ResultSet rs = null;
PreparedStatement pstmt;
DBController db = new DBController();
String dbQuery;
// Step 1 - connect to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "SELECT * FROM customer where uid = ?";
pstmt = db.getPreparedStatement(dbQuery);
try {
pstmt.setString(1, uid);
rs = pstmt.executeQuery();
if (rs.next()) { // first record found
customer = convertToCustomer(rs);
}
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return customer;
}
// -------------------------TELLER--------------------------------
public boolean createTellerWithUid(String uid) {
boolean success = false;
DBController db = new DBController();
String dbQuery;
PreparedStatement pstmt;
// get connection
db.getConnection();
// update customer 'banker' attribute with specified uid
// helps with login
dbQuery = "UPDATE customer SET banker = '1' WHERE uid = ? and enabled = 1";
pstmt = db.getPreparedStatement(dbQuery);
try {
pstmt.setString(1, uid);
if (pstmt.executeUpdate() == 1) {
success = true;
}
System.out.println(pstmt.executeUpdate());
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return success;
}
public CustomerEntity getTellerById(String id) {
// declare local variables
CustomerEntity teller = null;
ResultSet rs = null;
DBController db = new DBController();
String dbQuery;
PreparedStatement pstmt;
// step 1 - connect to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "SELECT * FROM customer WHERE id = ? and enabled = 1";
pstmt = db.getPreparedStatement(dbQuery);
// step 3 - execute query
try {
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if (rs.next()) { // first record found
teller = convertToCustomer(rs);
}
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return teller;
}
public List<CustomerEntity> getTopFiveCustomerWithdraw() {
// declare local variables
ArrayList<CustomerEntity> list = new ArrayList<CustomerEntity>();
ResultSet rs = null;
DBController db = new DBController();
String dbQuery;
// Step 1 - connect to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "select * from customer group by withdraw DESC limit 5;";
// step 3 - using DBController readRequest method
rs = db.readRequest(dbQuery);
try {
while (rs.next()) {
CustomerEntity customer = convertToCustomer(rs);
list.add(customer);
}
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return list;
}
public boolean deleteCustomerByUid(String uid) {
// declare local variables
boolean success = false;
DBController db = new DBController();
String dbQuery;
PreparedStatement pstmt;
// step 1 - establish connection to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "DELETE FROM customer WHERE uid = ?";
pstmt = db.getPreparedStatement(dbQuery);
// step 3 - to delete record using executeUpdate method
try {
pstmt.setString(1, uid);
if (pstmt.executeUpdate() == 1) success = true;
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return success;
}
public boolean updateCustomerClassByUid(String classs, String uid) {
boolean success = false;
DBController db = new DBController();
String dbQuery;
PreparedStatement pstmt;
// step 1 - establish connection to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "UPDATE customer SET classs = ? WHERE uid = ?";
pstmt = db.getPreparedStatement(dbQuery);
try {
pstmt.setString(1, classs);
pstmt.setString(2, uid);
// step 3 - to update record using executeUpdate method
if (pstmt.executeUpdate() == 1) success = true;
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(success);
// step 4 - close connection
db.terminate();
return success;
}
public List<CustomerEntity> retrieveAllCustomerByName(String name) {
// declare local variables
ArrayList<CustomerEntity> list = new ArrayList<CustomerEntity>();
ResultSet rs = null;
PreparedStatement pstmt = null;
DBController db = new DBController();
String dbQuery;
// Step 1 - connect to database
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "SELECT * FROM customer where name LIKE ? and enabled = 1";
pstmt = db.getPreparedStatement(dbQuery);
try {
pstmt.setString(1, "%" + name + "%");
System.out.println(name);
rs = pstmt.executeQuery();
while (rs.next()) {
CustomerEntity customer = convertToCustomer(rs);
list.add(customer);
}
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return list;
}
public static boolean updateCreditCard(CreditCard cc, int ID) throws SQLException {
String cardNumber = cc.getCardNumber();
int expiryMonth = cc.getExpiryMonth();
int expiryYear = cc.getExpiryYear();
String expiryDate = cc.getExpiryDate();
String cardHolderName = cc.getCardHolderName();
String country = cc.getCountry();
int CVC = cc.getCVC();
String streetAddress = cc.getStreetAddress();
String cardType = cc.getCardType();
boolean success = false;
DBController db = new DBController();
String dbQuery =
"UPDATE creditcard set CardNumber = '"
+ cardNumber
+ "',"
+ "ExpiryMonth='"
+ expiryMonth
+ "',ExpiryYear='"
+ expiryYear
+ "',"
+ "ExpiryDate='"
+ expiryDate
+ "',CardHolderName='"
+ cardHolderName
+ "',"
+ "Country='"
+ country
+ "',CVC='"
+ CVC
+ "',StreetAddress='"
+ streetAddress
+ "',"
+ "cardType='"
+ cardType
+ "'WHERE ID = '"
+ ID
+ "'";
db.getConnection();
if (db.updateRequest(dbQuery) == 1) {
success = true;
}
db.terminate();
return success;
}
public boolean toggleAccount(String nric) {
DBController db = new DBController();
boolean success = false;
String dbQuery;
PreparedStatement pstmt;
db.getConnection();
// step 2 - declare the SQL statement
dbQuery = "UPDATE customer SET enabled = !enabled WHERE nric = ?";
pstmt = db.getPreparedStatement(dbQuery);
// step 3 - to delete record using executeUpdate method
try {
pstmt.setString(1, nric);
if (pstmt.executeUpdate() == 1) success = true;
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
// step 4 - close connection
db.terminate();
return success;
}
