📄 sqlclass.java~119~
字号:
}
//更新员工信息
public boolean updateEmployee(Vector infor)
{
boolean flag = false;
Connection con = this.getConnection();
String employeeIdAtFirst = infor.elementAt(6).toString().trim();
String empIdNow = infor.elementAt(0).toString().trim();
String sql = "{call updateEmployeeInfor(?,?,?,?,?,?,?,?)}";
//System.out.println(infor);
try
{
CallableStatement cs = con.prepareCall(sql);
for (int i = 0; i < 4; i++)
{
cs.setString(i + 1, infor.elementAt(i).toString().trim());
}
int m = 0;
m = Integer.parseInt(infor.elementAt(4).toString().trim());
cs.setInt(5, m);
for (int i = 5; i < 8; i++)
{
cs.setString(i + 1, infor.elementAt(i).toString().trim());
}
int temp = cs.executeUpdate();
if (temp > 0)
{
flag = true;
}
cs.close();
}
catch (SQLException se)
{
se.printStackTrace();
System.out.println("数据库更新失败！");
}
if (employeeIdAtFirst.equals(empIdNow) == false)
{
//收集ID更新后的所有此员工相关信息，并存入Vector
String loginPassword = this.selectLogin(employeeIdAtFirst);
Vector checkInfor = this.selectEmployeeCheck(employeeIdAtFirst);
Vector salaryInfor = this.selectMonthSalary(employeeIdAtFirst);
Vector bonusInfor = this.selectEmployeeBonus(employeeIdAtFirst);
String[] check = new String[4];
String[] salary = new String[2];
String[] bonus = new String[2];
//System.out.println(employeeIdAtFirst);
//System.out.println(empIdNow);
//System.out.println(checkInfor);
//System.out.println(salaryInfor);
//System.out.println(bonusInfor);
//System.out.println(loginPassword);
//删除此信息
this.deleteOthers(employeeIdAtFirst);
//更新员工表中的相关信息
this.updateIds(employeeIdAtFirst, empIdNow);
//向相应表中插入Vector所存的信息
this.insertLogin(loginPassword, empIdNow);
for (int i = 0; i < checkInfor.size(); i += 4)
{
for (int j = 0; j < 4; j++)
{
check[j] = checkInfor.elementAt(i + j).toString().trim();
}
this.insertCheck(check, empIdNow);
}
for (int i = 0; i < salaryInfor.size(); i += 2)
{
for (int j = 0; j < 2; j++)
{
salary[j] = salaryInfor.elementAt(i + j).toString().trim();
}
this.insertSalary(salary, empIdNow);
}
for (int i = 0; i < bonusInfor.size(); i += 2)
{
for (int j = 0; j < 2; j++)
{
bonus[j] = bonusInfor.elementAt(i + j).toString().trim();
}
this.insertBonus(bonus, empIdNow);
}
}
this.closeConnection(con);
return flag;
}
//返回员工信息,用以初始化管理员管理员工部分信息
public Vector getEmployeeInfor(String empId)
{
String sql = "{call selectInfor(?)}";
Vector infor = new Vector();
Connection con = this.getConnection();
try
{
CallableStatement cs = con.prepareCall(sql);
cs.setString(1, empId);
ResultSet rs = cs.executeQuery();
if (rs.next())
{
infor.add(rs.getString(1).toString().trim());
infor.add(rs.getString(2).toString().trim());
infor.add(rs.getString(3).toString().trim());
infor.add(rs.getString(4).toString().trim());
infor.add(rs.getInt(5));
}
rs.close();
cs.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
this.closeConnection(con);
return infor;
}
//删除员工
public boolean deleteEmployee(String empId)
{
boolean flag = false;
String sql = "{call deleteEmployee(?)}";
Connection con = this.getConnection();
try
{
CallableStatement cs = con.prepareCall(sql);
cs.setString(1, empId);
int i = cs.executeUpdate();
if (i > 0)
{
flag = true;
}
cs.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
this.closeConnection(con);
return flag;
}
//返回一个员工的具体信息
public void selectDetailInformation(String empId,
EmployeeDetailInformation edi)
{
Connection con = this.getConnection();
try
{
Statement s = con.createStatement();
ResultSet rs = s.executeQuery("select dep_name,job_name,emp_name,emp_degree,emp_marryStatue,emp_tel,emp_address,emp_birth,emp_IDCard,emp_contractTime,emp_contractBeginTime,emp_exhibition,password,employee.dep_id,employee.job_id from employee,login,Department,JobType where Employee.dep_id=Department.dep_id and Employee.job_id=JobType.job_id and Employee.emp_id='" +
empId + "' and log_id='" + empId + "'");
if (rs.next())
{
edi.empId = empId;
edi.depName = rs.getString(1).toString().trim();
edi.jobName = rs.getString(2).toString().trim();
edi.name = rs.getString(3).toString().trim();
edi.degree = rs.getString(4).toString().trim();
edi.isMarry = rs.getInt(5);
edi.tel = rs.getString(6).toString().trim();
edi.address = rs.getString(7).toString().trim();
edi.birth = rs.getString(8).toString().trim();
edi.idCard = rs.getString(9).toString().trim();
edi.contractTime = rs.getInt(10);
edi.beginTime = rs.getString(11).toString().trim();
edi.exhibition = rs.getInt(12);
edi.password = rs.getString(13).toString().trim();
edi.depId = rs.getString(14).toString().trim();
edi.jobId = rs.getString(15).toString().trim();
}
rs.close();
s.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
this.closeConnection(con);
}
//更新个人具体信息
public boolean updateDetailInformation(EmployeeDetailInformation edi,
String employeeIdAtFirst)
{
boolean flag = false;
Connection con = this.getConnection();
String updateSql = "{call updateDetailInfor(?,?,?,?,?,?,?,?,?,?,?)}";
System.out.println(edi.depId);
try
{
CallableStatement cs = con.prepareCall(updateSql);
cs.setString(1, employeeIdAtFirst);
cs.setString(2, edi.depId);
cs.setString(3, edi.jobId);
cs.setString(4, edi.name);
cs.setString(5, edi.degree);
cs.setInt(6, edi.isMarry);
cs.setString(7, edi.tel);
cs.setString(8, edi.address);
cs.setString(9, edi.birth);
cs.setString(10, edi.idCard);
cs.setString(11, edi.password);
int i = cs.executeUpdate();
if (i > 0)
{
flag = true;
}
cs.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
if (employeeIdAtFirst.trim().equals(edi.empId.trim()) == false)
{
String loginPassword = this.selectLogin(employeeIdAtFirst);
Vector checkInfor = this.selectEmployeeCheck(employeeIdAtFirst);
Vector salaryInfor = this.selectMonthSalary(employeeIdAtFirst);
Vector bonusInfor = this.selectEmployeeBonus(employeeIdAtFirst);
String[] check = new String[4];
String[] salary = new String[2];
String[] bonus = new String[2];
//System.out.println(employeeIdAtFirst);
//System.out.println(empIdNow);
//System.out.println(checkInfor);
//System.out.println(salaryInfor);
//System.out.println(bonusInfor);
//System.out.println(loginPassword);
this.deleteOthers(employeeIdAtFirst);
this.updateIds(employeeIdAtFirst, edi.empId);
this.insertLogin(loginPassword, edi.empId);
for (int i = 0; i < checkInfor.size(); i += 4)
{
for (int j = 0; j < 4; j++)
{
check[j] = checkInfor.elementAt(i + j).toString().trim();
}
this.insertCheck(check, edi.empId);
}
for (int i = 0; i < salaryInfor.size(); i += 2)
{
for (int j = 0; j < 2; j++)
{
salary[j] = salaryInfor.elementAt(i + j).toString().trim();
}
this.insertSalary(salary, edi.empId);
}
for (int i = 0; i < bonusInfor.size(); i += 2)
{
for (int j = 0; j < 2; j++)
{
bonus[j] = bonusInfor.elementAt(i + j).toString().trim();
}
this.insertBonus(bonus, edi.empId);
}
}
this.closeConnection(con);
return flag;
}
//用于返回登录者的姓名。
public String getEmployeeNameByEmpId(String empId)
{
String empName = "";
Connection con = this.getConnection();
try
{
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(
"select emp_name from employee where emp_id='" + empId + "'");
if (rs.next())
{
empName = rs.getString(1).toString().trim();
}
rs.close();
s.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
this.closeConnection(con);
return empName;
}
//统计考勤系统中的记录
public void insertCheckItems()
{
VectorVariable vv = new VectorVariable();
Vector empIds = vv.employeeIds;
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
String yearStr = sdf.format(date).substring(0, 4);
String monthStr = sdf.format(date).substring(5, 7);
int month = Integer.parseInt(monthStr);
//System.out.println(sdf.format(date));
String dateStr[] = new String[31];
//System.out.println(yearStr);
// System.out.println(monthStr);
//System.out.println(month);
switch (month)
{
case 1:
case 3:
case 5:
case 7:
case 8:
case 10:
case 12:
dateStr = new String[31];
for (int i = 0; i < 31; i++)
{
dateStr[i] = yearStr + "-" + monthStr + "-" + (i + 1);
}
break;
case 4:
case 6:
case 9:
case 11:
dateStr = new String[30];
for (int i = 1; i <= 30; i++)
{
dateStr[i - 1] = yearStr + "-" + monthStr + "-" + i;
}
break;
case 2:
dateStr = new String[29];
for (int i = 0; i < 29; i++)
{
dateStr[i] = yearStr + "-" + monthStr + "-" + (i + 1);
}
break;
}
//for (int i = 0; i < dateStr.length; i++)
//{
// System.out.println(dateStr[i]);
// }
//System.out.println(empIds);
String empId = "";
for (int i = 0; i < dateStr.length; i++)
{
for (int j = 0; j < empIds.size(); j++)
{
//System.out.println(dateStr[i]);
//System.out.println(empIds.elementAt(j).toString().trim());
empId = empIds.elementAt(j).toString().trim();
//System.out.println(empId);
this.insertIntoCheckEachMonth(dateStr[i], empId);
}
}
}
private int getCheckInformationForExhibition(String empId, Date dt)
{
Connection con = this.getConnection();
int exhibition = 0;
try
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
String dateFirst = sdf.format(dt) + "-1";
String dateLast = sdf.format(dt) + "-31";
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(
"select count(*) from employeeCheck where emp_Id='" + empId +
"' and isLeave=1 and checkDate between '" + dateFirst + "' and '" +
dateLast + "'");
int leaveDay = 0;
int lateDay = 0;
int overDay = 0;
if (rs.next())
{
leaveDay = rs.getInt(1);
}
rs = s.executeQuery("select count(*) from employeeCheck where emp_Id='" +
empId +
"' and isLate=1 and checkDate between '" + dateFirst +
"' and '" +
dateLast + "'");
if (rs.next())
{
lateDay = rs.getInt(1);
}
rs = s.executeQuery("select count(*) from employeeCheck where emp_Id='" +
empId +
"' and isOvertime=1 and checkDate between '" +
dateFirst +
"' and '" +
dateLast + "'");
if (rs.next())
{
overDay = rs.getInt(1);
}
if (leaveDay - overDay > 3 && lateDay > 3)
{
exhibition = -1;
}
if (overDay > 3 && leaveDay == 0 && lateDay == 0)
{
exhibition = 1;
}
rs.close();
s.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
this.closeConnection(con);
return exhibition;
}
private double getCheckInformationForSalary(String empId, Date dt)
{
Connection con = this.getConnection();
double salary = 0.0;
try
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
String dateFirst = sdf.format(dt) + "-1";
String dateLast = sdf.format(dt) + "-31";
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(
"select count(*) from employeeCheck where emp_Id='" + empId +
"' and isLeave=1 and checkDate between '" + dateFirst + "' and '" +
dateLast + "'");
int leaveDay = 0;
int lateDay = 0;
int overDay = 0;
if (rs.next())
{
leaveDay = rs.getInt(1);
}
rs = s.executeQuery("select count(*) from employeeCheck where emp_Id='" +
empId +
"' and isLate=1 and checkDate between '" + dateFirst +
"' and '" +
dateLast + "'");
if (rs.next())
{
lateDay = rs.getInt(1);
}
rs = s.executeQuery("select count(*) from employeeCheck where emp_Id='" +
empId +
"' and isOvertime=1 and checkDate between '" +
dateFirst +
"' and '" +
dateLast + "'");
if (rs.next())
{
overDay = rs.getInt(1);
}
rs = s.executeQuery("select job_id from employee where emp_id='" + empId +
"'");
String jobId = "";
if (rs.next())
{
jobId = rs.getString(1);
}
double basicSalary = 0.0;
rs = s.executeQuery("select basicSalary from JobType where job_id='" +
jobId + "'");
if (rs.next())
{
basicSalary = rs.getDouble(1);
}
//System.out.println(leaveDay);
//System.out.println(lateDay);
//System.out.println(overDay);
//System.out.println(jobId);
//System.out.println(basicSalary);
salary = basicSalary + (basicSalary / 31 + 50) * overDay -
50 * (leaveDay + lateDay);
rs.close();
s.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
this.closeConnection(con);
return salary;
}
private double getCheckInformationForBonus(String empId, Date dt)
{
Connection con = this.getConnection();
double bonus = 0.0;
int leaveDay = 0;
int lateDay = 0;
int overDay = 0;
try
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
String dateFirst = sdf.format(dt) + "-1-1";
String dateLast = sdf.format(dt) + "-12-31";
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(
"select count(*) from employeeCheck where emp_Id='" + empId +
"' and isLeave=1 and checkDate between '" + dateFirst + "' and '" +
dateLast + "'");
if (rs.next())
{
leaveDay = rs.getInt(1);
}
rs = s.executeQuery("select count(*) from employeeCheck where emp_Id='" +
empId +
"' and isLate=1 and checkDate between '" + dateFirst +
"' and '" +
dateLast + "'");
if (rs.next())
{
lateDay = rs.getInt(1);
}
rs = s.executeQuery("select count(*) from employeeCheck where emp_Id='" +
empId +
"' and isOvertime=1 and checkDate between '" +
dateFirst +
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -