package com.xdf.supermarket.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.xdf.supermarket.db.DBConnection;
import com.xdf.supermarket.dto.DeptDTO;
import com.xdf.supermarket.util.Constant;
import com.xdf.supermarket.util.Tools;

public class DeptBean extends BaseBean{
	
	/**
	 * 得到所有的部門信息 
	 */
	public ArrayList getAllDept(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList(); 
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "select * from table_dept order by dept_id";
			rs = stmt.executeQuery(sql);
			while (rs.next()){
				DeptDTO v = new DeptDTO(); 
				v.setDept_id(rs.getString("dept_id"));
				v.setDept_name(rs.getString("dept_name"));
				v.setDept_descreption(rs.getString("dept_descreption"));
				list.add(v);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;		
	}
	/**
	 * 增加部門
	 */
	public boolean addDept(DeptDTO dd){
		Connection conn = null;
		Statement stmt = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "insert into table_dept values(seq_dept_id.nextval,'"+
			Tools.dan(dd.getDept_name())+"','"+Tools.dan(dd.getDept_descreption())+"')";
			
			int t = stmt.executeUpdate(sql);
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
		return flag;
	} 
	
	/**
	 * 修改部門
	 */
	public boolean updateDept(DeptDTO dd){
		Connection conn = null;
		Statement stmt = null;
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "update table_dept set dept_name='"+
			Tools.dan(dd.getDept_name())+"',dept_descreption='"+Tools.dan(dd.getDept_descreption())+"' " +
			"where dept_id="+dd.getDept_id();
			
			int t = stmt.executeUpdate(sql);
			if (t==1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
		return flag;
	}
	
	/**
	 * 得到總頁數
	 */
	public int getDeptTotalPage(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		int total = 0;
		try {
			conn = DBConnection.getConnection();
			//設置結果集可以滾動，不受其他更新影響，只讀
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) c from table_dept");
			rs.next();
			int num = rs.getInt("c");
			total = (num+Constant.DEPT_PAGE_SIZE-1)/Constant.DEPT_PAGE_SIZE;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);			
		}
		return total;
	}
	/**
	 * 得到一頁部門
	 */
	public ArrayList getOnePageDept(int pageno){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			int pagesize = Constant.DEPT_PAGE_SIZE;
			int first = (pageno-1)*pagesize+1;
			int last = pageno*pagesize;
			String sql = 
				"select * from " + 
				"(select s.*,rownum r from " +
				"(select * from table_dept order by dept_id) s " +
				"where rownum<="+last+") " +
				"where r>="+first;
			
			rs = stmt.executeQuery(sql);
			while (rs.next()){
				DeptDTO v = new DeptDTO(); 
				v.setDept_id(rs.getString("dept_id"));
				v.setDept_name(rs.getString("dept_name"));
				v.setDept_descreption(rs.getString("dept_descreption"));
				list.add(v);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;		
	}	
	/**
	 * 根據id得到一個部門
	 */
	public DeptDTO getOneDept(String dept_id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		DeptDTO v = new DeptDTO();
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			String sql = "select * from table_dept where dept_id="+dept_id; 
			
			rs = stmt.executeQuery(sql);
			if (rs.next()){
				v.setDept_id(rs.getString("dept_id"));
				v.setDept_name(rs.getString("dept_name"));
				v.setDept_descreption(rs.getString("dept_descreption"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return v;		
	}
}