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));
}
}