package model;


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.List;

import javax.servlet.http.HttpServlet;



/**
 * Servlet implementation class UserMod
 */
public class UserMod {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UserMod() {
    	
    }
    	
    
    public List<User> getAll(int limit, int offset) throws SQLException {
    	PreparedStatement getal = null;
//        int offset = (pageNumber - 1) * 9;

       List<User> result = new ArrayList<User>();
       Connection con = new ConnectionPool().getConnection();
       getal = con.prepareStatement("select * from customer limit ? offset ?");
       getal.setInt(1, limit);
       getal.setInt(2, offset);
        ResultSet rs = getal.executeQuery();
        
        while (rs.next()) {
        	
        	String user = rs.getString(2);
            String pass = rs.getString(3);
            String email = rs.getString(4);
            String address = rs.getString(5);
            String phone = rs.getString(6);
            String realname = rs.getString(7);
            String cmnd = rs.getString(8);

            User userx = new User(user, pass, email, address, phone, realname, cmnd);
            result.add(userx);
        }

        con.close();
        return result;
    }
    
    
    public int recordsnumberUser() throws SQLException {
    	PreparedStatement recornumb = null;
    	int record = 0;
		Connection con = new ConnectionPool().getConnection();
		
			recornumb = con.prepareStatement("select count(id) from customer");
	        ResultSet rs2 = recornumb.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
    
 	public boolean DeleteaCustomer(String username) {
 		PreparedStatement delpd = null;
		boolean result = false;

		Connection con = new ConnectionPool().getConnection();
		try {
			
			delpd = con.prepareStatement("DELETE FROM customer WHERE user = ?");
			delpd.setString(1, username);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}

			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}
    
    
    public boolean DeleteAllCustomer() {
 		PreparedStatement delallpd = null;
		boolean result = false;

		Connection con = new ConnectionPool().getConnection();
		try {
			
			delallpd = con.prepareStatement("DELETE FROM customer");

			int number = delallpd.executeUpdate();
			if (number > 0) {
				result = true;
			}

			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}
    
    
    	public boolean Register(User user) {
    		PreparedStatement sqlr = null;
    		boolean result = false;

    		Connection con = new ConnectionPool().getConnection();
    		try {
    			sqlr = con.prepareStatement("insert into customer(user, pass, email, address, phone, realname, cmnd) values(?, ?, ?, ?, ?, ?, ?)");

    			sqlr.setString(1, user.getUserName());
    			sqlr.setString(2, user.getPassword());
    			sqlr.setString(3, user.getEmail());
    			sqlr.setString(4, user.getAddress());
    			sqlr.setString(5, user.getPhone());
    			sqlr.setString(6, user.getRealname());
    			sqlr.setString(7, user.getCmnd());
    			
    			int number = sqlr.executeUpdate();  
    			if (number > 0) {
    				result = true;
    			}

    			con.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}

    		return result;
    	}

    	public boolean checkRegister(String userName) {
    		boolean result = false;
    		PreparedStatement sqlu = null;

    		Connection con = new ConnectionPool().getConnection();
    		try {
    			sqlu = con.prepareStatement("select * from customer where user = ? ");
    			sqlu.setString(1, userName);
    			ResultSet rs = sqlu.executeQuery();
    			while (rs.next()) {
    				result = true;
    			}

    			con.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}

    		return result;
    	}
    	
    	public boolean checkEmail(String Email) {
    		boolean result = false;
    		PreparedStatement sqlu = null;

    		Connection con = new ConnectionPool().getConnection();
    		try {
    			sqlu = con.prepareStatement("select * from customer where email = ? ");
    			sqlu.setString(1, Email);
    			ResultSet rs = sqlu.executeQuery();
    			while (rs.next()) {
    				result = true;
    			}

    			con.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}

    		return result;
    	}

    	public boolean checkLogin(User user) {
    		PreparedStatement sqll = null;
    		boolean result = false;

    		Connection c = new ConnectionPool().getConnection();
    		try {
    			sqll = c.prepareStatement("select * from customer where user = ? AND pass = ?");
    			sqll.setString(1, user.getUserName());
    			sqll.setString(2, user.getPassword());   			
    			ResultSet rs =  sqll.executeQuery();
    			
    			while(rs.next() == true) {
    				result = true;
    				
    				break;
    			}
    			
    			c.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}

    		return result;
    	}
    	
    	public boolean changepass(String user, String pass) {
    		PreparedStatement sqlcp = null;
    		boolean result = false;

    		Connection con = new ConnectionPool().getConnection();
    		try {
    			sqlcp = con.prepareStatement("update customer set pass = ? where user = ?");
    			sqlcp.setString(1, pass); 
    			sqlcp.setString(2, user); 
    			int number = sqlcp.executeUpdate();
    			if (number > 0) {
    				result = true;
    			}

    			con.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}

    		return result;
    	}
    	
    	public boolean checkpass(String user, String pass) {
    		PreparedStatement sqlcpa = null;
    		boolean result = false;

    		Connection con = new ConnectionPool().getConnection();
    		try {
    			sqlcpa = con.prepareStatement("select * from customer where user = ? and pass = ?");
    			sqlcpa.setString(1, user);
    			sqlcpa.setString(2, pass);
    			ResultSet rs = sqlcpa.executeQuery();
    			while (rs.next()) {
    				result = true;
    			}

    			con.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}

    		return result;
    	}
    	
    	public boolean checkforgotpass(String user, String mail) {
    		PreparedStatement sqlf = null;
    		boolean result = false;

    		Connection c = new ConnectionPool().getConnection();
    		try {
    			sqlf = c.prepareStatement("select * from customer where user = ? AND email = ?");
    			sqlf.setString(1, user);
    			sqlf.setString(2, mail);
    			ResultSet rs =  sqlf.executeQuery();
    			
    			while(rs.next() == true) {
    				result = true;
    				
    				break;
    			}
    			
    			c.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}

    		return result;
    	}	
    	public String getpassforgot(String user, String mail) throws SQLException {
        	String password = "";
        	PreparedStatement sqlgp = null;
    		Connection con = new ConnectionPool().getConnection();
    		
    			sqlgp = con.prepareStatement("select pass from customer where user = ? AND email = ?");
    			sqlgp.setString(1, user);
    			sqlgp.setString(2, mail);
    			ResultSet rs = sqlgp.executeQuery();
    	        while (rs.next()) { 	        	
    	        	password = rs.getString(1);
    	        }
    			con.close();
    		return password;
    	}
    	
    	
    	public User getUserInfo(String user) {   
    		PreparedStatement sqlui = null;
    		User us = new User();
    		Connection c = new ConnectionPool().getConnection();
    		try {
    			
    			sqlui = c.prepareStatement("select * from customer where user = ?"); 
    			sqlui.setString(1, user);
    			ResultSet rs =  sqlui.executeQuery();   			
    			if (rs.next()) {
    				String userx = rs.getString("user");
    				String email = rs.getString("email");
    				String address = rs.getString("address");
    				String phone = rs.getString("phone");
    				String realname = rs.getString("realname");
    				String cmnd = rs.getString("cmnd");
    				us = new User(userx,"",email,address,phone,realname,cmnd);
    			}
    			
    			c.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return us;
    	}
    	
    	public int countercustomer() throws SQLException {
    		PreparedStatement ct = null;
        	int countercustomer = 1;
    		Connection con = new ConnectionPool().getConnection();
    		
    			ct = con.prepareStatement("select count(id) from customer");
    	        ResultSet rs = ct.executeQuery();
    	        while (rs.next()) { 	        	
    	        	countercustomer = rs.getInt(1);
    	        }
    			con.close();
    		return countercustomer;
    	}
  
    	public static void main(String[] args) throws SQLException {
    		System.out.println(new UserMod().getUserInfo("luanxt"));
    	}
 
}
