package bean.syslogs;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
//import javax.servlet.http.HttpServletRequest;
import database.DBPoolManager;

public class SyslogBean {

	private ArrayList syslogs;
	
	public SyslogBean() {
		syslogs = new ArrayList();
	}

	public ArrayList getSyslogs() {
		syslogs.clear();
		String s = null;
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();	
		try {
			String    sql  = "select UserCode, syslog, ModifyDate from syslogs order by ModifyDate";
			Statement stmt = dbpool.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			while (rest.next()) {
				Hashtable ht = new Hashtable();
				if (rest.getString("UserCode") == null){
					ht.put("UserCode", "");
				}
				else{
				        s   =  rest.getString("UserCode");
				        s   =  s.trim();
					ht.put("UserCode", s);
				}
				if (rest.getString("syslog") == null){
					ht.put("syslog", "");
				}
				else{
				        s   =  rest.getString("syslog");
				        s   =  s.trim();				
					ht.put("syslog", s);
				}
				if (rest.getString("ModifyDate") == null){
					ht.put("ModifyDate", "");
				}
				else{
				        s   =  rest.getString("ModifyDate");
				        s   =  s.trim();				
					ht.put("ModifyDate", s);
				}				
				syslogs.add(ht);
			}
			rest.close();
			stmt.close();									
		} catch (SQLException ex) {
			System.err.println("Get SyslogBean SQLException: " + ex.toString());
		} finally {
			dbpool.freeConnection();
		}	
		return this.syslogs;
	}
	public ArrayList getSyslogs(int Page,int Lines,String StartDate,String EndDate) {
		syslogs.clear();		
		String  s	=	null;
        	int 	i	=	0;
        	int 	tp	=	0;
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();	
		try {
			String    sql  = "select UserCode, syslog, ModifyDate from syslogs where ModifyDate between to_date('"+StartDate+" 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('"+EndDate+" 23:59:59','yyyy-mm-dd hh24:mi:ss') order by ModifyDate";
			Statement stmt = dbpool.conn.createStatement();
			ResultSet rest = stmt.executeQuery(sql);
			while (rest.next()) {
				Hashtable ht = new Hashtable();
				tp=	tp	+	1;
				if ((tp>Page*Lines)&&(i<Lines)){
					i	=	i	+	1;
					if (rest.getString("UserCode") == null){
						ht.put("UserCode", "");
					}
					else{
					        s   =  rest.getString("UserCode");
					        s   =  s.trim();
						ht.put("UserCode", s);
					}
					if (rest.getString("syslog") == null){
						ht.put("syslog", "");
					}
					else{
					        s   =  rest.getString("syslog");
					        s   =  s.trim();				
						ht.put("syslog", s);
					}
					if (rest.getString("ModifyDate") == null){
						ht.put("ModifyDate", "");
					}
					else{
					        s   =  rest.getString("ModifyDate");
					        s   =  s.trim();				
						ht.put("ModifyDate", s);
					}				
					syslogs.add(ht);
				}
			}
			rest.close();
			stmt.close();									
		} catch (SQLException ex) {
			System.err.println("Get SyslogBean SQLException: " + ex.toString());
		} finally {
			dbpool.freeConnection();
		}	
		return this.syslogs;
	}
	public  String setSyslogs(String UserCode, String syslog) {	
		String result= new String("執行成功！");
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();	
		try {
			String    sql  = null;
			Statement stmt = dbpool.conn.createStatement();
			sql	  =	 "insert into syslogs(logID, UserCode, syslog, ModifyDate)values(LOGID.NextVal,'"+UserCode
             	             			+"','"+syslog
						+"',sysdate)"; 
			stmt.executeUpdate(sql);			
			stmt.close();									
		} catch (SQLException ex) {
			result = "執行出錯！";
			System.err.println("Get SyslogBean SQLException: " + ex.toString());
		} finally {
			dbpool.freeConnection();
		}	
		return result;
	}		
		
}