package com.bluedot.bank.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bluedot.bank.framework.sql.MySqlInfo;
import com.bluedot.bank.framework.web.actionform.UsersBean;
import com.bluedot.bank.framework.web.actionform.UsersLoadBean;

public class UsersDao {
	public UsersLoadBean select(String name, String password) {
		List<String> list = new ArrayList<String>();
		UsersLoadBean usersLoadBean = new UsersLoadBean();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_id,user_status from users where username=? and password=?");
			statement.setString(1,name);
			statement.setString(2,password);
			
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				
				list=select(rs.getString("role_id"));
				usersLoadBean.setUserName(list);
				usersLoadBean.setUserStatus(rs.getString("user_status"));
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return usersLoadBean;
	}
	
	public List<UsersBean> seleId(String role_id) {
		List<UsersBean> list = new ArrayList<UsersBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql="select * from users where role_id=?";
		try {
			PreparedStatement statement = connection
					.prepareStatement(sql);
				statement.setString(1,role_id);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				UsersBean usersBean = new UsersBean();
				usersBean.setUser_id(rs.getString("user_id"));
				usersBean.setUserName(rs.getString("username"));
				usersBean.setChck_time(rs.getTimestamp("chck_time"));
				usersBean.setCheck_manager(rs.getString("check_manager"));
				usersBean.setInit_time(rs.getTimestamp("init_time"));
				usersBean.setPassword(rs.getString("password"));
				usersBean.setRole_id(rs.getString("role_id"));
				usersBean.setUser_describe(rs.getString("user_describe"));
				usersBean.setUser_status(rs.getString("user_status"));
				list.add(usersBean);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}
	
	public List<UsersBean> seleName(String name) {
		List<UsersBean> list = new ArrayList<UsersBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql="select * from users where username=?";
		try {
			PreparedStatement statement = connection
					.prepareStatement(sql);
				statement.setString(1,name);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				UsersBean usersBean = new UsersBean();
				usersBean.setUser_id(rs.getString("user_id"));
				usersBean.setUserName(rs.getString("username"));
				usersBean.setChck_time(rs.getTimestamp("chck_time"));
				usersBean.setCheck_manager(rs.getString("check_manager"));
				usersBean.setInit_time(rs.getTimestamp("init_time"));
				usersBean.setPassword(rs.getString("password"));
				usersBean.setRole_id(rs.getString("role_id"));
				usersBean.setUser_describe(rs.getString("user_describe"));
				usersBean.setUser_status(rs.getString("user_status"));
				list.add(usersBean);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}
	
	public List<UsersBean> select(int a,String id) {
		List<UsersBean> list = new ArrayList<UsersBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql="select * from users where user_id=?";
		if(id.equals("")){
			sql="select * from users";
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement(sql);
			if(id.equals("")){
			}else{
				statement.setString(1,id);
			}
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				UsersBean usersBean = new UsersBean();
				usersBean.setUser_id(rs.getString("user_id"));
				usersBean.setUserName(rs.getString("username"));
				usersBean.setChck_time(rs.getTimestamp("chck_time"));
				usersBean.setCheck_manager(rs.getString("check_manager"));
				usersBean.setInit_time(rs.getTimestamp("init_time"));
				usersBean.setPassword(rs.getString("password"));
				usersBean.setRole_id(rs.getString("role_id"));
				usersBean.setUser_describe(rs.getString("user_describe"));
				usersBean.setUser_status(rs.getString("user_status"));
				list.add(usersBean);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public List<String> select(String id) {
		List<String> list = new ArrayList<String>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_range from popedom where role_name=(select role_name from roles where role_id=?)");
			statement.setString(1, id);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("role_range"));
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}
	
	public void update(UsersBean usersBean){
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("update users set username=?,user_status=?,password=?,user_describe=? where user_id=?");
			int index=1;
			statement.setString(index++,usersBean.getUserName());
			statement.setString(index++,usersBean.getUser_status());
			statement.setString(index++,usersBean.getPassword());
			statement.setString(index++,usersBean.getUser_describe());
			statement.setString(index++,usersBean.getUser_id());
			statement.execute();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}
	
	
	
	
	public void delete(String id){
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("delete from users where user_id=?");
			statement.setString(1, id);
			
			statement.execute();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}
	
	
	

}