package common;

import cmis.common.Describe;
import database.DBPoolManager;
import java.sql.*;
import java.util.Hashtable;
import java.util.ArrayList;
// Download by http://www.codefans.net
public class DepartmentsBean {

	public DepartmentsBean() {
	}

        public int getUserDepartment(String user) {
		int dept = 0;
		if (user == null) {
			return 0;
		}
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		try {
			String sql = "select a.OrganID from organ a, Staff b where "
					+ "b.OrganID = a.OrganID and b.UserCode = '" + user + "'";
			Statement selectStatement = dbpool.conn.createStatement();
			ResultSet rest = selectStatement.executeQuery(sql);
			if (rest.next()) {
				dept = rest.getInt(1);
			}
			rest.close();
			selectStatement.close();
		} catch (SQLException ex) {
			System.err.println("Get User Department info SQLException : "
					+ ex.getMessage());
		} finally {
			dbpool.freeConnection();
		}
		return dept;
	}

	public String getUserName(String userId) {
		String res = userId;
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		try {
			String sql = "select UserName from Staff where UserCode = '"
					+ userId + "'";
			Statement selectStatement = dbpool.conn.createStatement();
			ResultSet rest = selectStatement.executeQuery(sql);
			if (rest.next()) {
				res = rest.getString(1);
			}
			rest.close();
			selectStatement.close();
		} catch (SQLException ex) {
			System.err.println("Get User Name SQLException : "
					+ ex.getMessage());
		} finally {
			dbpool.freeConnection();
		}
		return res;
	}
	
	
	public boolean isAdmin(String userId) {
		boolean  reflag = false;
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		try {
			String sql = "select UserName from Staff a, Staff_role b where a.UserId=b.UserId and a.UserCode = '"
					+ userId + "' and b.RoleId= 0 ";
			Statement selectStatement = dbpool.conn.createStatement();
			ResultSet rest = selectStatement.executeQuery(sql);
			if (rest.next()) {
				reflag = true ;
			}
			rest.close();
			selectStatement.close();
		} catch (SQLException ex) {
			System.err.println("IsAdmin SQLException : "
					+ ex.getMessage());
		} finally {
			dbpool.freeConnection();
		}
		return reflag;
	}
	
	public String getDepartment(int dept) {
		String res = "";
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		try {
			String sql = "select OrganName from organ where OrganID = " + dept;
			Statement selectStatement = dbpool.conn.createStatement();
			ResultSet rest = selectStatement.executeQuery(sql);
			if (rest.next()) {
				res = rest.getString(1);
			}
			rest.close();
			selectStatement.close();
		} catch (SQLException ex) {
			System.err.println("Get Department info SQLException : "
					+ ex.getMessage());
		} finally {
			dbpool.freeConnection();
		}
		return res;
	}

	public ArrayList getDepts() {
		ArrayList depts = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		try {
			String sql = "select OrganID, OrganName from organ where Organflag = 1  order by organId";
			Statement stmt = dbpool.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			while (rest.next()) {
				Hashtable ht = new Hashtable();
				ht.put("deptId", rest.getInt(1));
				ht.put("deptName", rest.getString(2));
				depts.add(ht);
			}
			rest.close();
			stmt.close();
		} catch (SQLException ex) {
			System.err.println("Get Departments SQLException: " + ex.toString());
		} finally {
			dbpool.freeConnection();
		}
		return depts;
	}
	
	public ArrayList getDepts(String organidof) {
		ArrayList depts = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		try {
			String sql = "select OrganID, OrganName from organ where OrganId in (  Select organid from organ Connect by prior organid=suporganid Start with organid = "  + organidof + " )  order by organId";
			Statement stmt = dbpool.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			while (rest.next()) {
				Hashtable ht = new Hashtable();
				ht.put("deptId", rest.getInt(1));
				ht.put("deptName", rest.getString(2));
				depts.add(ht);
			}
			rest.close();
			stmt.close();
		} catch (SQLException ex) {
			System.err.println("Get Departments SQLException: " + ex.toString());
		} finally {
			dbpool.freeConnection();
		}
		return depts;
	}	
	
}