package com.bluedot.bank.framework.dao;

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

import com.bluedot.bank.framework.sql.MySqlInfo;
import com.bluedot.bank.framework.web.actionform.RolesBean;

public class RolesDao {
	public List<RolesBean> select(String select,String info){
		String sql="select role_id,role_name,role_description from roles where "+select+" like ? escape '!'";
		if(select.equals("all")){
			sql="select role_id,role_name,role_description from roles";
		}
		String b;
		b="%"+info.replaceAll("!","!!").replaceAll("%","!%")+"%";
		List<RolesBean> rolesBeanlist = new ArrayList<RolesBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement(sql);
			if(!select.equals("all")){
				statement.setString(1,b);
			}
			
			ResultSet rs = statement.executeQuery();
			
			while(rs.next()){
				RolesBean rolesBean = new RolesBean();
				rolesBean.setRole_id(rs.getString("role_id"));
				rolesBean.setRole_name(rs.getString("role_name"));
				rolesBean.setRole_description(rs.getString("role_description"));
				
				rolesBeanlist.add(rolesBean);
			}
			
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return rolesBeanlist;
	}
	
	public List<RolesBean> seleName(String roleName) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		List<RolesBean> list = new ArrayList<RolesBean>();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_name,role_id,role_description from roles where role_name=?");
			
			statement.setInt(1,Integer.valueOf(roleName));
			ResultSet rs = statement.executeQuery();
			
			while(rs.next()){
				RolesBean rolesBean = new RolesBean();
				rolesBean.setRole_id(rs.getString("role_id"));
				rolesBean.setRole_name(rs.getString("role_name"));
				rolesBean.setRole_description(rs.getString("role_description"));
				
				list.add(rolesBean);
			}
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}
	
	public List<RolesBean> select(String roleId) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		List<RolesBean> list = new ArrayList<RolesBean>();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_name,role_id,role_description from roles where role_id=?");
			
			statement.setInt(1,Integer.valueOf(roleId));
			ResultSet rs = statement.executeQuery();
			
			while(rs.next()){
				RolesBean rolesBean = new RolesBean();
				rolesBean.setRole_id(rs.getString("role_id"));
				rolesBean.setRole_name(rs.getString("role_name"));
				rolesBean.setRole_description(rs.getString("role_description"));
				
				list.add(rolesBean);
			}
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public void del(String roleId) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("delete from roles where role_id=?");
			
			statement.setInt(1,Integer.valueOf(roleId));
			
			
			PreparedStatement statement1 = connection
				.prepareStatement("delete from popedom where role_name=(select role_name from roles where role_id =?)");
			statement1.setInt(1,Integer.valueOf(roleId));
			statement1.execute();
			statement.execute();			
			
			statement1.close();
			//我靠，打死也不能在干這事了，忘了執行了
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}
}