public String insertCustomerDetails(String memNo) {
CustomerDBConnection c = new CustomerDBConnection();
String res = " ";
ResultSet rs = null;
String status = "pending";
int quantity = 1;
String type = "DVD";
String fine = "0";
try {
String issuedate = c.calculateIssueDate();
String duedate = c.calculateDueDate();
String query1 = "Select movieID from cart where memNo=?";
ps = con.prepareStatement(query1);
ps.setString(1, memNo);
rs = ps.executeQuery();
while (rs.next()) {
String movieid = rs.getString(1);
System.out.println("inside db");
String query =
"Insert into customerdetails(membershipno,movieid,quantity,type,issueddate,duedate,status,fine) values(?,?,?,?,?,?,?,?)";
ps = con.prepareStatement(query);
ps.setString(1, memNo);
ps.setString(2, movieid);
ps.setInt(3, quantity);
ps.setString(4, type);
ps.setString(5, issuedate);
ps.setString(6, duedate);
ps.setString(7, status);
ps.setString(8, fine);
ps.executeUpdate();
}
String query3 = "Delete from cart where memNo=?";
ps = con.prepareStatement(query3);
ps.setString(1, memNo);
ps.executeUpdate();
res = "true";
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return res;
}
public String changePassword(String username, String oldPassword, String newPassword) {
ResultSet rs = null;
PreparedStatement ps = null;
CustomerDBConnection db = new CustomerDBConnection();
db.changePasswordLogin(username, oldPassword, newPassword);
// Connection con = null;
String res = " ";
int rowcount;
try {
String query = "Select * from customer WHERE username = ? and password=?";
ps = con.prepareStatement(query);
ps.setString(1, username);
System.out.println(username);
ps.setString(2, oldPassword);
System.out.println(oldPassword);
rs = ps.executeQuery();
while (rs.next()) {
String query1 = "Update customer SET password= ? where username=? and password=?";
ps = con.prepareStatement(query1);
ps.setString(1, newPassword);
System.out.println(newPassword);
ps.setString(2, username);
System.out.println(username);
ps.setString(3, oldPassword);
System.out.println(oldPassword);
rowcount = ps.executeUpdate();
// String res1 = "updated";
if (rowcount > 0) {
res = "true: updated successfully";
} else {
res = "false : Not updated";
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
// rs.close();
// con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return res;
}
// Checkout
public String checkOut(String memno) {
String res = " ";
int count = 0;
try {
String query = "Select * from customerdetails WHERE membershipno=?";
ps = con.prepareStatement(query);
ps.setString(1, memno);
rs = ps.executeQuery();
while (rs.next()) {
count++;
System.out.println(count);
}
CustomerDBConnection c = new CustomerDBConnection();
// checking if the max allowed count has already been reached in the customerdetails DB
System.out.println("calculating the movies checkedout....");
String result2 = c.checkedOutMovies(memno, count);
// if it hasnt
if (result2.equals("true")) {
System.out.println("checking count in cart...");
// get the count of movies in the cart
int count_cart = c.checkCart(memno);
System.out.println(count_cart);
// compare with the max_allowed
System.out.println("checking validity for checkout....");
res = c.checkValidity(memno, count, count_cart);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
// con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return res;
}
public String removefromCart(String memNo, int movieID) {
String res = " ";
// Connection con = null
ResultSet rs = null;
PreparedStatement ps = null;
try {
String query1 =
"Select c.movieID,moviename,banner,description,releasedate,rent,category,quantity FROM movie m,cart c WHERE m.idMovie = c.movieId and c.memNo=?";
ps = con.prepareStatement(query1);
ps.setString(1, memNo);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println("inside resultset");
int movieId = rs.getInt(1);
String name = rs.getString(2);
String banner = rs.getString(3);
String description = rs.getString(4);
String releaseDate = rs.getString(5);
int rent = rs.getInt(6);
String cat = rs.getString(7);
int quant = rs.getInt(8);
res +=
movieId
+ ";"
+ name
+ ";"
+ banner
+ ";"
+ description
+ ";"
+ releaseDate
+ ";"
+ rent
+ ";"
+ cat
+ ";"
+ quant;
}
String query = "Delete FROM cart Where memNo=? And movieID=?";
ps = con.prepareStatement(query);
ps.setString(1, memNo);
System.out.println(memNo);
ps.setInt(2, movieID);
System.out.println(movieID);
ps.executeUpdate();
CustomerDBConnection c = new CustomerDBConnection();
c.addQuantity(movieID);
} catch (Exception e) {
e.printStackTrace();
res = "false";
} finally {
try {
ps.close();
// con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return res;
}
public String addtoCart(String memNo, int movieId) {
String res = " ";
ResultSet rs = null;
PreparedStatement ps = null;
try {
// con = getConnection();
CustomerDBConnection c = new CustomerDBConnection();
String result = c.checkCart(memNo, movieId);
if (result.equals("true")) {
String result2 = c.decreaseQuantity(movieId);
if (result2.equals("true")) {
String query = "Insert into cart(memNo,movieID) values(?,?)";
ps = con.prepareStatement(query);
ps.setString(1, memNo);
System.out.println(memNo);
ps.setInt(2, movieId);
System.out.println(movieId);
ps.executeUpdate();
String query1 =
"Select c.movieID,moviename,banner,description,releasedate,rent,category,quantity FROM movie m,cart c WHERE m.IdMovie = c.movieID and c.memNo=?";
ps = con.prepareStatement(query1);
ps.setString(1, memNo);
rs = ps.executeQuery();
while (rs.next()) {
int movieID = rs.getInt(1);
String name = rs.getString(2);
String banner = rs.getString(3);
String description = rs.getString(4);
String releaseDate = rs.getString(5);
String rent = rs.getString(6);
String cat = rs.getString(7);
String quant = rs.getString(8);
res +=
movieID
+ ";"
+ name
+ ";"
+ banner
+ ";"
+ description
+ ";"
+ releaseDate
+ ";"
+ cat
+ ";"
+ quant
+ "##";
}
}
} else {
res = "false";
}
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
// Signup for the customer >>>>>>>>>>>---------
public String addCustomer(
String username,
String password,
String firstname,
String lastname,
String address,
String state,
String city,
int zip,
String type) {
String res = " ";
try {
CustomerDBConnection c = new CustomerDBConnection();
String membershipno = "";
String balance = "";
String role = "User";
if (type.equalsIgnoreCase("SimpleCustomer")) {
balance = "2";
membershipno = c.generateMembershipno("SimpleCustomer");
} else if (type.equalsIgnoreCase("Premium")) {
balance = "10";
membershipno = c.generateMembershipno("Premium");
}
System.out.println("inside insert method");
// con = getConnection();
// stmt=con.createStatement();
String query1 = "Select * from customer WHERE username=?";
System.out.println("inside 1st query");
ps = con.prepareStatement(query1);
ps.setString(1, username);
rs = ps.executeQuery();
if (rs.next()) {
res = "false";
} else {
System.out.println("starting insert query");
String query =
"Insert into customer(membershipno,username,password,firstname,lastname,address,state,city,zipcode,accounttype,balance) values(?,?,?,?,?,?,?,?,?,?,?)";
CustomerDBConnection db = new CustomerDBConnection();
db.login(username, password);
System.out.println("before query");
ps = con.prepareStatement(query);
System.out.println("after query");
ps.setString(1, membershipno);
ps.setString(2, username);
ps.setString(3, password);
ps.setString(4, firstname);
ps.setString(5, lastname);
ps.setString(6, address);
ps.setString(7, state);
ps.setString(8, city);
ps.setFloat(9, zip);
ps.setString(10, type);
ps.setString(11, balance);
ps.executeUpdate();
System.out.println("Inserted Successfully");
c.insertIntoLogin(username, password);
res = "true";
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// rs.close();
ps.close();
// con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return res;
}