package org.pan.web;

import java.sql.*;
import java.util.Vector;
import java.util.Calendar;
import org.pan.util.*;
import javax.servlet.http.HttpServletRequest;
import org.pan.web.leave;

/**
 * Title:       假條信息管理類
 * Description:
 * Copyright:    Copyright (c) 2008
 * @author:		 雙魚林
 * @version 1.0
 */

public class leaveManage extends DataBase {
	private leave oneLeaveRecord = new leave();	//新的一個請假記錄
	private javax.servlet.http.HttpServletRequest request; //建立頁面請求
	private boolean sqlflag = true ;		//對接收到的數據是否正確
	private Vector leavelist;				//顯示員工列表向量數組
	private int page = 1;					//顯示的頁碼
	private int pageSize=5;				//每頁顯示的假條數
	private int pageCount =0;				//頁面總數
	private long recordCount =0;			//查詢的記錄總數
  public String errorMessage = "";           //保存出錯信息
  public String sqlStr = "";

	
	public leaveManage() throws Exception{
		super();
	}

	public Vector getLeavelist() {
		return leavelist;
	}

	public boolean getSqlflag() {
		return sqlflag;
	}

	public String getGbk( String str) {
		try
		{
			return new String(str.getBytes("ISO8859-1"));
		}
		catch (Exception e)
		{
			return str;
		}
	}
	
	//將頁面表單傳來的資料分解并保存到員工類的各個字段中
	public boolean getRequest(javax.servlet.http.HttpServletRequest newrequest) {
		boolean flag = false;
		try
		{	
			request = newrequest;
			
			String employeeId = request.getParameter("employeeId");
			if(null == employeeId || employeeId.equals("")) {
				employeeId = "";
				sqlflag = false;
			}
			oneLeaveRecord.setEmployeeId(getGbk(employeeId));
			
			String employeeName = request.getParameter("employeeName");
			if(null == employeeName || employeeName.equals("")) {
				employeeName = "";
				sqlflag = false;
			}
			oneLeaveRecord.setEmployeeName(getGbk(employeeName));
			
			String startTime = request.getParameter("startTime");
			if(null == startTime || startTime.equals("")) {
				startTime = "";
				sqlflag = false;
			}
			oneLeaveRecord.setStartTime(getGbk(startTime));
			
			String day = request.getParameter("day");
			if(null == day || day.equals("")) {
				day = "";
				sqlflag = false;
			}
			oneLeaveRecord.setDay(getGbk(day));
			
			String reason = request.getParameter("reason");
			if(null == reason || reason.equals("")) reason = "";
			oneLeaveRecord.setReason(getGbk(reason));
			
			String approvePerson = request.getParameter("approvePerson");
			if(null == approvePerson || approvePerson.equals("")) approvePerson = "";
			oneLeaveRecord.setApprovePerson(getGbk(approvePerson));
	
		  String  editTime = request.getParameter("editTime");
		  if(null == editTime || editTime.equals("")) editTime = "";
		  oneLeaveRecord.setEditTime(getGbk(editTime));
		  
		  String memo = request.getParameter("memo");
		  if(null == memo || memo.equals("")) {
		  	memo = "";
		  }
		  oneLeaveRecord.setMemo(getGbk(memo));
		  
			if (sqlflag){
				flag = true;
			}
			return flag;					
		}
		catch (Exception e)
		{
			return flag;
		}
	}


  //按條件執行請假信息的查找并將結果保存到Leavelist向量數組中
	public boolean execute(HttpServletRequest res) throws Exception {  //用于執行按條件查詢
		request = res;
		String PAGE = request.getParameter("page");   //頁碼
		try {
			page = Integer.parseInt(PAGE);
		}
		catch (NumberFormatException e)
		{
			page = 1;
		}
		 
		//取出記錄數
		sqlStr = "select count(*) from leave";
		String searchZiduan = request.getParameter("searchZiduan");
		if(searchZiduan == null) searchZiduan = "";
		String keyword = request.getParameter("keyword");
		if(keyword == null) keyword = "";
		keyword = getGbk(keyword);
		
		if(!searchZiduan.equals("")) {
			sqlStr = sqlStr + " where " + searchZiduan + " like '%" + keyword + "%'";
		}
		
		int rscount = pageSize;
		try
		{
			ResultSet rs1 = stmt.executeQuery(sqlStr);
			if (rs1.next()) recordCount = rs1.getInt(1);				
			rs1.close();
		}
		catch (SQLException e)
		{
			errorMessage = "查詢記錄總數錯誤:" + sqlStr + e.getMessage();
			return false;
		}
		//計算查詢結果總頁數
		if (recordCount < 1)
      pageCount = 0;
    else
      pageCount = (int)(recordCount - 1) / pageSize + 1;
		//檢查查看的頁面數是否在范圍內
		if (page < 1)  
       page = 1;
    else if (page >= pageCount) //如果請求最后一頁的記錄
    {
       page = pageCount;
		   rscount = (int) recordCount % pageSize;
		  }
		
		sqlStr = "select * from leave";  //根據不同條件構造不同的sql查詢字符串
		if(!searchZiduan.equals("")) {
			sqlStr = sqlStr + " where " + searchZiduan + " like '%" + keyword + "%'";
		}
		try {
			rs = stmt.executeQuery(sqlStr);
			leavelist = new Vector(rscount);
			for(int i=0;i<pageSize*(page-1);i++)rs.next();//跳過前面頁的記錄
			while (rs.next())
			{
				leave aLeaveRecord = new leave();
				aLeaveRecord.setLeaveId(rs.getLong("leaveId"));
				aLeaveRecord.setEmployeeId(rs.getString("employeeId"));
				aLeaveRecord.setEmployeeName(rs.getString("employeeName"));
				aLeaveRecord.setStartTime(rs.getString("startTime"));
				aLeaveRecord.setDay(rs.getString("day"));
				aLeaveRecord.setReason(rs.getString("reason"));
				aLeaveRecord.setApprovePerson(rs.getString("approvePerson"));
				aLeaveRecord.setEditTime(rs.getString("editTime"));
				aLeaveRecord.setMemo(rs.getString("memo"));
				leavelist.addElement(aLeaveRecord);
			}
			rs.close();
			return true;
		}
		catch (SQLException e)
		{
			errorMessage = errorMessage + sqlStr + e.getMessage();
			System.out.println(e);
			return false;
		}
	}

  //向數據庫中假條信息表中插入一條新的記錄
	public boolean insert() throws Exception {
		String editTime;
    Calendar cal=Calendar.getInstance(); 
    Integer y,m,d,h,mi,s;
    y=cal.get(Calendar.YEAR); 
    m=cal.get(Calendar.MONTH) + 1; 
    d=cal.get(Calendar.DATE); 
    h=cal.get(Calendar.HOUR_OF_DAY); 
    mi=cal.get(Calendar.MINUTE); 
    s=cal.get(Calendar.SECOND); 
    editTime = y+"年"+m+"月"+d+"日"+h+"時"+mi+"分"+s+"秒"; 
		sqlStr = "insert into leave(employeeId,employeeName,startTime,day,reason,approvePerson,editTime,memo) values ('";
		sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getEmployeeId()) + "','";
		sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getEmployeeName()) + "','";
		sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getStartTime()) + "','";
	  sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getDay()) + "','";
	  sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getReason()) + "','";
	  sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getApprovePerson()) + "','";
	  sqlStr = sqlStr + strFormat.toSql(editTime) + "','";
	  sqlStr = sqlStr + strFormat.toSql(oneLeaveRecord.getMemo()) + "')";
		try
		{
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException sqle)
		{	
			errorMessage = errorMessage + sqlStr + sqle.getMessage();
			return false;
		}
	}
	
	//更新假條表中的一條記錄
	public boolean update(long leaveId) throws Exception {
		String editTime;
    Calendar cal=Calendar.getInstance(); 
    Integer y,m,d,h,mi,s;
    y=cal.get(Calendar.YEAR); 
    m=cal.get(Calendar.MONTH) + 1; 
    d=cal.get(Calendar.DATE); 
    h=cal.get(Calendar.HOUR_OF_DAY); 
    mi=cal.get(Calendar.MINUTE); 
    s=cal.get(Calendar.SECOND); 
    editTime = y+"年"+m+"月"+d+"日"+h+"時"+mi+"分"+s+"秒"; 
		sqlStr = "update leave set ";
		sqlStr = sqlStr + "employeeId = '" + strFormat.toSql(oneLeaveRecord.getEmployeeId()) + "',";
		sqlStr = sqlStr + "employeeName = '" + strFormat.toSql(oneLeaveRecord.getEmployeeName()) + "',";
		sqlStr = sqlStr + "startTime = '" + strFormat.toSql(oneLeaveRecord.getStartTime()) + "',";
		sqlStr = sqlStr + "day = '" + strFormat.toSql(oneLeaveRecord.getDay()) + "',";
		sqlStr = sqlStr + "reason = '" + strFormat.toSql(oneLeaveRecord.getReason()) + "',";
		sqlStr = sqlStr + "approvePerson = '" + strFormat.toSql(oneLeaveRecord.getApprovePerson()) + "',";
		sqlStr = sqlStr + "editTime = '" + editTime + "',";
		sqlStr = sqlStr + "memo = '" + strFormat.toSql(oneLeaveRecord.getMemo()) + "' ";
		sqlStr = sqlStr + "where leaveId =" + leaveId;
		System.out.println(sqlStr);
		try
		{		
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException e)
		{
			errorMessage = sqlStr + e.getMessage();
			return false;
		}
	}

  //刪除編號為leaveId的假條
	public boolean delete(long leaveId) throws Exception {
		sqlStr = "delete from leave where leaveId = "  + leaveId;
		try
		{
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException e)
		{
			System.out.println(e);
			return false;
		}
	}

  //得到編號為leaveId的假條信息并保存的leavelist向量數組中
	public boolean getOneLeave(long leaveId) throws Exception {
		try
		{
			sqlStr="select * from leave where leaveId =" + leaveId;
			rs = stmt.executeQuery(sqlStr);
			if (rs.next())
			{	
				leavelist = new Vector(1);
				leave aLeaveRecord = new leave();
				aLeaveRecord.setLeaveId(rs.getLong("leaveId"));
				aLeaveRecord.setEmployeeId(rs.getString("employeeId"));
				aLeaveRecord.setEmployeeName(rs.getString("employeeName"));
				aLeaveRecord.setStartTime(rs.getString("startTime"));
				aLeaveRecord.setDay(rs.getString("day"));
				aLeaveRecord.setReason(rs.getString("reason"));
				aLeaveRecord.setApprovePerson(rs.getString("approvePerson"));
				aLeaveRecord.setEditTime(rs.getString("editTime"));
				aLeaveRecord.setMemo(rs.getString("memo"));
				leavelist.addElement(aLeaveRecord);
			} else {
				rs.close();
				errorMessage = sqlStr;
				return false;
			}
			rs.close();
			return true;
		}
		catch (SQLException e)
		{
			errorMessage = sqlStr + e.getMessage();
			return false;
		}
		
	}

	public int getPage() {				//顯示的頁碼
		return page;
	}
	public void setPage(int newpage) {
		page = newpage;
	}

	public int getPageSize(){			//每頁顯示的圖書數
		return pageSize;
	}
	public void setPageSize(int newpsize) {
		pageSize = newpsize;
	}

	public int getPageCount() {				//頁面總數
		return pageCount;
	}
	public void setPageCount(int newpcount) {
		pageCount = newpcount;
	}

	public long getRecordCount() {
		return recordCount;
	}
	public void setRecordCount(long newrcount) {
		recordCount= newrcount;
	}
};