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;
  }