About MIS's Log

import java.util.Calendar;
import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.*;

public class LogBean {

 public ResultSet result = null;

 public Connection conn;

 /**
  * Description:Get connection <br>
  * Input: None <br>
  * Output: None <br>
  * Return: None <br>
  * Remark: <br>
  *
  */
 public void getConnection() throws Exception {

  try {
   this.conn = Conn.getConnection();
  } catch (Exception e1) {
   e1.printStackTrace();
  }
 }

 /**
  * Description:Close database <br>
  * Input: None <br>
  * Output: None <br>
  * Return: None <br>
  * Remark: <br>
  *
  */
 public void closeConnection() {

  try {
   this.conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 /**
  * Description:Get log refer format as "200623"<br>
  * Input: the employee number <br>
  * Output: String logrefer <br>
  * Return: String <br>
  * Remark: <br>
  *
  */
 public String getCreateLogRefer() {

  String logrefer = null;
  if (this.getWeekOfYear() / 10 > 0)
   logrefer = String.valueOf(this.getLogYear())
     + String.valueOf(this.getWeekOfYear());
  else
   logrefer = String.valueOf(this.getLogYear()) + "0"
     + String.valueOf(this.getWeekOfYear());
  return logrefer;
 }

 /**
  * Description:Create new weeklog according to system date<br>
  * Input: the employee number <br>
  * Output: None <br>
  * Return: None <br>
  * Remark: <br>
  *
  */
 public void createWeekLog(String aEmpID) throws SQLException,
   java.io.UnsupportedEncodingException {

  try {
   String logrefer = this.getCreateLogRefer();
   this.getConnection();
   // �����log
   for (int i = 1; i <= 7; i++) {
    PreparedStatement create_stm = conn
      .prepareStatement("Insert into LogDay(Log_Date,log_empid,log_refer,log_weekday) "
        + "values(?,?,?,?)");
    create_stm.setString(1, this.getWeekDate(i));
    create_stm.setString(2, aEmpID);
    create_stm.setString(3, logrefer);
    create_stm.setString(4, this.convertWeek(i));
    create_stm.executeUpdate();
    create_stm.close();
    conn.commit();
   }
   // �����loogweek
   EmployeeBean emp = new EmployeeBean();
   PreparedStatement createLogWeek_stm = conn
     .prepareStatement("Insert into LogWeek"
       + "(LogWeek_EmpID,logweek_year,logweek_dep,logweek_week,"
       + "logweek_empname,logweek_between,logweek_refer)"
       + " values(?,?,?,?,?,?,?)");
   createLogWeek_stm.setString(1, aEmpID);
   createLogWeek_stm.setInt(2, this.getLogYear());
   createLogWeek_stm.setString(3, emp.getDepName(aEmpID));
   createLogWeek_stm.setInt(4, this.getWeekOfYear());
   createLogWeek_stm.setString(5, emp.getEmpName(aEmpID));
   createLogWeek_stm.setString(6, this.getBeginDate() + "��"
     + this.getEndDate());
   createLogWeek_stm.setString(7, logrefer);
   createLogWeek_stm.executeUpdate();
   createLogWeek_stm.close();
   conn.commit();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }
 }

 /**
  * Description:Check wheather the log created <br>
  * Input: the employee number and the date <br>
  * Output: true or false <br>
  * Return: boolean <br>
  * Remark: <br>
  *
  */
 public boolean createEmpLog(String aEmpID, String aLogDate) {

  try {
   this.getConnection();
   PreparedStatement select_stm = conn.prepareStatement(
     "SELECT id FROM LogDay WHERE log_empid=? and log_date=?",
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setString(1, aEmpID);
   select_stm.setString(2, aLogDate);
   result = select_stm.executeQuery();
   boolean bl = result.first();
   result.close();
   this.closeConnection();
   return bl;
  } catch (Exception e) {
   e.printStackTrace();
  }
  return false;
 }

 /**
  * Description:Get all the information of logweek by the employee ID<br>
  * Input: the employee number <br>
  * Output: None<br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public ResultSet getWeekResult(String aEmpID) {

  try {
   PreparedStatement select_stm = conn
     .prepareStatement(
       "SELECT logweek_year, logweek_dep,logweek_week,logweek_empname,logweek_between,logweek_refer "
         + "FROM logweek WHERE logweek_empid =? order by logweek_week desc",
       java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
       java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setString(1, aEmpID);
   result = select_stm.executeQuery();
  } catch (Exception e) {
   System.out.println(e);
  }
  return result;
 }

 /**
  * Description:Get all the information of log with logweek_State='���ϴ�' by
  * the employee ID<br>
  * Input: the employee number <br>
  * Output: None<br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public ResultSet getAllWeekResult() {

  try {
   PreparedStatement select_stm = conn
     .prepareStatement(
       "SELECT logweek_year, logweek_dep,logweek_empID,logweek_week,logweek_empname,logweek_between,logweek_refer "
         + "FROM logweek WHERE logweek_State='���ϴ�' order by logweek_week desc",
       java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
       java.sql.ResultSet.CONCUR_READ_ONLY);
   result = select_stm.executeQuery();
  } catch (Exception e) {
   System.out.println(e);
  }
  return result;
 }

 /**
  * Description:Get all the history of logweek <br>
  * Input: None <br>
  * Output: None<br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public ResultSet getAllHistoryResult() {

  try {
   PreparedStatement select_stm = conn.prepareStatement(
     "SELECT logweek_year, logweek_dep,logweek_empID,logweek_week,"
       + "logweek_empname,logweek_between,logweek_refer "
       + "FROM logweek  order by logweek_week desc",
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);

   result = select_stm.executeQuery();
  } catch (Exception e) {
   System.out.println(e);
  }
  return result;
 }

 /**
  * Description:Get LogDay'ID by LogDay'log_empid and log_date<br>
  * Input:String aLogEmp, String aLogDate <br>
  * Output:int logid <br>
  * Return: int <br>
  * Remark: <br>
  *
  */
 public int getLogDayId(String aLogEmp, String aLogDate) {

  int logid = 0;
  try {
   this.getConnection();
   PreparedStatement select_stm = conn.prepareStatement("SELECT id "
     + "FROM logday WHERE log_empid =? and log_date=?",
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setString(1, aLogEmp);
   select_stm.setString(2, aLogDate);
   result = select_stm.executeQuery();
   while (result.next()) {
    logid = result.getInt("id");
   }
   result.close();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
  }
  return logid;
 }

 /**
  * Description:Get LogDay'id by LogDay'log_empid ,log_refer and log_weekday<br>
  * Input:tring aLogEmp, String aLogRefer, int aNum<br>
  * Output:int logid <br>
  * Return: int <br>
  * Remark: <br>
  *
  */
 public int getLogDayId2(String aLogEmp, String aLogRefer, int aNum) {

  int logid = 0;
  try {
   this.getConnection();
   PreparedStatement select_stm = conn
     .prepareStatement(
       "SELECT id "
         + "FROM logday WHERE log_empid =? and log_refer=? and log_weekday=?",
       java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
       java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setString(1, aLogEmp);
   select_stm.setString(2, aLogRefer);
   select_stm.setString(3, this.convertWeek(aNum));
   result = select_stm.executeQuery();
   while (result.next()) {
    logid = result.getInt("id");
   }
   result.close();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
  }
  return logid;
 }

 /**
  * Description:Get String of date and week by LogDay'lOG_ID as "2006-07-14(������)"<br>
  * Input:int aLogDayID <br>
  * Output:ResultSet result <br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public String getDateAndWeek(int aLogDayID) {

  String date_week = null;
  try {
   this.getConnection();
   PreparedStatement select_stm = conn.prepareStatement(
     "SELECT log_date,log_weekday FROM logday "
       + "WHERE id=(select log_id from log where id=?)",
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setInt(1, aLogDayID);
   result = select_stm.executeQuery();
   while (result.next()) {
    date_week = result.getString("log_date") + " ("
      + result.getString("log_weekday") + ")";
   }
   result.close();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
  }
  return date_week;
 }

 /**
  * Description:Get String of date and week by LogDay'ID as "2006-07-14(������)"<br>
  * Input:int aLogDayID <br>
  * Output:ResultSet result <br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public String getDateAndWeek2(int aLogDayID) {

  String date_week = null;
  try {
   this.getConnection();
   PreparedStatement select_stm = conn.prepareStatement(
     "SELECT log_date,log_weekday FROM logday WHERE id=?",
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setInt(1, aLogDayID);
   result = select_stm.executeQuery();
   while (result.next()) {
    date_week = result.getString("log_date") + " ("
      + result.getString("log_weekday") + ")";
   }
   result.close();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
  }
  return date_week;
 }

 /**
  * Description:Get all the result of logweek filled the request<br>
  * Input: number <br>
  * Output: result = all the result <br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public ResultSet getPrepareResult(int aSelBtn) {

  try {
   String select_sql = null;
   if (aSelBtn == 1)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_year asc";
   else if (aSelBtn == 2)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_year desc";
   else if (aSelBtn == 3)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_dep asc";
   else if (aSelBtn == 4)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_dep desc";
   else if (aSelBtn == 5)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_week asc";
   else if (aSelBtn == 6)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_week desc";
   else if (aSelBtn == 7)
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_empID asc";
   else
    select_sql = "SELECT logweek_year, logweek_dep,logweek_empID,"
      + "logweek_week,logweek_empname,logweek_between,logweek_refer "
      + "FROM logweek  order by logweek_empID desc";
   PreparedStatement select_stm = conn.prepareStatement(select_sql,
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
   result = select_stm.executeQuery();
  } catch (Exception e) {
   System.out.println(e);
  }
  return result;
 }

 /**
  * Description:Get the detail log by Log'Log_ID<br>
  * Input:ResultSetint aID <br>
  * Output:ResultSet result <br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public ResultSet readWeekLog(int aLogID) {

  try {
   PreparedStatement select_stm = conn
     .prepareStatement(
       "SELECT id,log_id,log_other,log_prjsort,log_prjid,log_detail,log_times,log_state "
         + "FROM log WHERE log_id=? order by log_id asc",
       java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
       java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setInt(1, aLogID);
   result = select_stm.executeQuery();
  } catch (Exception e) {
   System.out.println(e);
  }
  return result;
 }

 /**
  * Description:Get the detail log by Log'ID<br>
  * Input:ResultSetint aID <br>
  * Output:ResultSet result <br>
  * Return: ResultSet <br>
  * Remark: <br>
  *
  */
 public ResultSet readDayLog(int aID) {

  try {
   PreparedStatement select_stm = conn.prepareStatement(
     "SELECT log_id,log_other,log_prjsort,log_prjid,log_detail,log_times,log_state "
       + "FROM log WHERE id=?",
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
   select_stm.setInt(1, aID);
   result = select_stm.executeQuery();
  } catch (Exception e) {
   System.out.println(e);
  }
  return result;
 }

 /**
  * Description:upload the week log then refresh of the state by the employee
  * ID<br>
  * Input: the employee Id and the refer <br>
  * Output: None <br>
  * Return: None <br>
  * Remark: <br>
  *
  */
 public void uploadWeekLog(String aEmpID, String aRefer)
   throws SQLException, java.io.UnsupportedEncodingException {

  try {
   // l����ݿ�
   this.getConnection();
   // ����logweek
   PreparedStatement up_logweek_stm = conn
     .prepareStatement("update logweek set logweek_state='���ϴ�' "
       + "where logweek_empid=? and logweek_refer=?");
   up_logweek_stm.setString(1, aEmpID);
   up_logweek_stm.setString(2, aRefer);
   up_logweek_stm.executeUpdate();
   up_logweek_stm.close();
   conn.commit();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }
 }

 /**
  * Description:return the week's index in the month <br>
  * Input: None<br>
  * Output: None <br>
  * Return: int <br>
  * Remark: <br>
  *
  */
 public int getWeekOfYear() {

  Calendar calendar = Calendar.getInstance();
  return calendar.get(Calendar.WEEK_OF_YEAR);
 }

 /**
  * Description:return the day's index in the week <br>
  * Input: None<br>
  * Output: None <br>
  * Return:int <br>
  * Remark: <br>
  *
  */
 public int getWeekDay() {

  Calendar calendar = Calendar.getInstance();
  return calendar.get(Calendar.DAY_OF_WEEK) - 1;
 }

 /**
  * Description:return the year <br>
  * Input: None<br>
  * Output: None <br>
  * Return:int <br>
  * Remark: <br>
  *
  */
 public int getLogYear() {

  Calendar calendar = Calendar.getInstance();
  return calendar.get(Calendar.YEAR);
 }

 /**
  * Description:return the start date of the first day of week <br>
  * Input: None<br>
  * Output: None <br>
  * Return:String <br>
  * Remark: <br>
  *
  */
 public String getBeginDate() {

  Date date = new Date();
  Date dateBegin = new Date();
  SimpleDateFormat shortDateFormat = new SimpleDateFormat("yyyy-MM-dd");
  dateBegin.setTime(date.getTime() - (long) (getWeekDay() - 1) * 24 * 60
    * 60 * 1000);
  return shortDateFormat.format(dateBegin);
 }

 /**
  * Description:return the end date of the first day of week <br>
  * Input: None<br>
  * Output: None <br>
  * Return:String <br>
  * Remark: <br>
  *
  */
 public String getEndDate() {

  Date date = new Date();
  Date dateBegin = new Date();
  SimpleDateFormat shortDateFormat = new SimpleDateFormat("yyyy-MM-dd");
  dateBegin.setTime(date.getTime() - (long) (getWeekDay() - 7) * 24 * 60
    * 60 * 1000);
  return shortDateFormat.format(dateBegin);
 }

 /**
  * Description:return the date of the day in the week <br>
  * Input: int i<br>
  * Output: None <br>
  * Return:String <br>
  * Remark: <br>
  *
  */
 public String getWeekDate(int i) {

  Date date = new Date();
  Date dateBegin = new Date();
  SimpleDateFormat shortDateFormat = new SimpleDateFormat("yyyy-MM-dd");
  dateBegin.setTime(date.getTime() - (long) (getWeekDay() - i) * 24 * 60
    * 60 * 1000);
  return shortDateFormat.format(dateBegin);
 }

 /**
  * Description:Convert number to weekday <br>
  * Input: int i <br>
  * Output: String week <br>
  * Return:String <br>
  * Remark: <br>
  *
  */
 public String convertWeek(int i) {

  String week = null;
  switch (i) {
  case 1:
   week = "����һ";
   break;
  case 2:
   week = "���ڶ�";
   break;
  case 3:
   week = "������";
   break;
  case 4:
   week = "������";
   break;
  case 5:
   week = "������";
   break;
  case 6:
   week = "������";
   break;
  case 7:
   week = "������";
   break;
  }
  return week;
 }

 /**
  * Description:Update Records from the log by Log'ID <br>
  *
  * @param <br>
  *            Input: int aID, String aLogPrjSort, String aLogPrjID, String
  *            aLogDetail, int aLogTimes<br>
  *            Output: None <br>
  * @return: None <br>
  * @Remark: <br>
  *
  */
 public void updateRecord(int aID, String aLogPrjSort, String aLogPrjID,
   String aLogDetail, int aLogTimes) throws SQLException,
   java.io.UnsupportedEncodingException {

  try {
   this.getConnection();
   PreparedStatement update_stm = conn
     .prepareStatement("update Log "
       + "set Log_PrjSort=?,Log_PrjID=?,Log_Detail=?, Log_Times=?  where id=? ");
   update_stm.setString(1, aLogPrjSort);
   update_stm.setString(2, aLogPrjID);
   update_stm.setString(3, aLogDetail);
   update_stm.setInt(4, aLogTimes);
   update_stm.setInt(5, aID);
   update_stm.executeUpdate();
   update_stm.close();
   conn.commit();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }
 }

 /**
  * Description:Add a new record into table log<br>
  *
  * @param <br>
  *            Input: int aLogID, String aLogPrjSort, String aLogPrjID,
  *            String aLogDetail, int aLogTimes<br>
  *            Output: None <br>
  * @return: None <br>
  * @Remark: <br>
  *
  */
 public void addLogDetail(int aLogID, String aLogPrjSort, String aLogPrjID,
   String aLogDetail, int aLogTimes) throws SQLException,
   java.io.UnsupportedEncodingException {

  try {
   this.getConnection();
   PreparedStatement insert_stm = conn
     .prepareStatement("Insert into Log(log_id,log_prjsort,log_prjid,log_detail,log_times)"
       + " values(?,?,?,?,?)");
   insert_stm.setInt(1, aLogID);
   insert_stm.setString(2, aLogPrjSort);
   insert_stm.setString(3, aLogPrjID);
   insert_stm.setString(4, aLogDetail);
   insert_stm.setInt(5, aLogTimes);
   insert_stm.executeUpdate();
   insert_stm.close();
   conn.commit();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }
 }

 /**
  * Description:Update the state to readed after the administrstor readed
  * <br>
  *
  * @param <br>
  *            Input: the employeeId and refer <br>
  *            Output: None <br>
  * @return: None <br>
  * @Remark: <br>
  *
  */
 public void changeLogWeekState(String aEmpID, String aRefer)
   throws SQLException, java.io.UnsupportedEncodingException {

  try {
   this.getConnection();
   // update LogWeek
   PreparedStatement update_stm = conn
     .prepareStatement("update Logweek "
       + "set logweek_state=? where logweek_empid=? and logweek_refer=?");
   update_stm.setString(1, "����");
   update_stm.setString(2, aEmpID);
   update_stm.setString(3, aRefer);
   update_stm.executeUpdate();
   update_stm.close();
   conn.commit();
   update_stm.close();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }
 }

 /**
  * Description:Update log_other by id <br>
  *
  * @param <br>
  *            Input: id,log_other <br>
  *            Output: None <br>
  * @return: None <br>
  * @Remark: <br>
  *
  */
 public void updateLogOther(int aLogID, String aLogOther)
   throws SQLException, java.io.UnsupportedEncodingException {

  try {
   this.getConnection();
   PreparedStatement update_stm = conn.prepareStatement("update Log "
     + "set Log_Other=? where ID=?");

   update_stm.setString(1, aLogOther);
   update_stm.setInt(2, aLogID);

   update_stm.executeUpdate();
   update_stm.close();
   conn.commit();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }

 }

 /**
  * Description:Delete log by id <br>
  *
  * @param <br>
  *            Input:id <br>
  *            Output: None <br>
  * @return: None <br>
  * @Remark: <br>
  *
  */
 public void deleteLogDetail(int aLogID) throws SQLException,
   java.io.UnsupportedEncodingException {

  try {
   this.getConnection();
   PreparedStatement delete_stm = conn
     .prepareStatement("delete from log where id=?");
   delete_stm.setInt(1, aLogID);
   delete_stm.executeUpdate();
   delete_stm.close();
   conn.commit();
   this.closeConnection();
  } catch (Exception e) {
   System.out.println(e);
   conn.rollback();
  }
 }

 public static void main(String args[]) throws Exception {
  LogBean tt = new LogBean();
  System.out.print(tt.getDateAndWeek2(12));
 }
}