sqlclass.java~117~

本员工管理系统使公司员工能通过网络来查询信息、请假

                          "' 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 basicBonus = 0.0;
      rs = s.executeQuery("select basicBonus from JobType where job_id='" +
                          jobId + "'");
      if (rs.next())
      {
        basicBonus = rs.getDouble(1);
      }
      //System.out.println(leaveDay);
      //System.out.println(lateDay);
      //System.out.println(overDay);
      //System.out.println(jobId);
      //System.out.println(basicBonus);
      bonus = basicBonus + 150 * (overDay - leaveDay - lateDay);

      rs.close();
      s.close();

    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return bonus;
  }

  //分发员工工资
  public void insertSalaryItems(Date dt)
  {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
    String date = sdf.format(dt);
    VectorVariable vv = new VectorVariable();
    Vector empIds = vv.employeeIds;
    String empId = "";
    double salary = 0.0;
    int exhibition = 0;
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = null;
      for (int i = 0; i < empIds.size(); i++)
      {
        cs = con.prepareCall("{call insertSalary(?,?,?)}");
        empId = empIds.elementAt(i).toString().trim();
        //System.out.println(empId);
        salary = this.getCheckInformationForSalary(empId, dt);
        exhibition = this.getCheckInformationForExhibition(empId, dt);
        //System.out.println(salary);
        cs.setString(1, date);
        cs.setString(2, empId);
        cs.setDouble(3, salary);
        cs.executeUpdate();
        cs = con.prepareCall("{call updateExhibition(?,?)}");
        cs.setString(1, empId);
        cs.setInt(2, exhibition);
        cs.executeUpdate();
        cs.close();
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
  }

  //分发员工奖金
  public void insertBonusItems(Date dt)
  {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
    String date = sdf.format(dt);
    VectorVariable vv = new VectorVariable();
    Vector empIds = vv.employeeIds;
    String empId = "";
    double bonus = 0.0;
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = null;
      for (int i = 0; i < empIds.size(); i++)
      {
        cs = con.prepareCall("{call insertBonus(?,?,?)}");
        empId = empIds.elementAt(i).toString().trim();
        //System.out.println(empId);
        bonus = this.getCheckInformationForBonus(empId, dt);
        //System.out.println(salary);
        cs.setString(1, date);
        cs.setString(2, empId);
        cs.setDouble(3, bonus);
        cs.executeUpdate();

        cs.close();
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);

  }

  //请假
  public boolean askForLeave(String empId, String date)
  {
    Connection con = this.getConnection();
    boolean flag = false;
    try
    {
      CallableStatement cs = con.prepareCall("{call updateCheck(?,?)}");
      cs.setString(1, empId);
      cs.setString(2, date);
      int i = cs.executeUpdate();
      if (i > 0)
      {
        flag = true;
      }
      cs.close();
      this.closeConnection(con);
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    return flag;
  }

  //初始化个人薪水、表现表模式
  public void initDefaultTableModel(DefaultTableModel dtm, String empId)
  {
    Connection con = this.getConnection();
    int exhibition = 0;
    try
    {
      PreparedStatement prst = con.prepareStatement("select month_id,MonthSalary.emp_id,emp_name,salary,emp_exhibition from MonthSalary,employee where MonthSalary.emp_id=? and employee.emp_id=?");
      prst.setString(1, empId);
      prst.setString(2, empId);
      ResultSet rs = prst.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 1; i <= colCount; i++)
      {
        dtm.addColumn(rsmt.getColumnName(i));
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        exhibition = rs.getInt(5);
        if (exhibition == 1)
        {
          cellValue[4] = "优秀";
        }
        else if (exhibition == -1)
        {
          cellValue[4] = "差劲";
        }
        else
        {
          cellValue[4] = "一般";
        }
        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

  //初始化员工薪水表模式(按部门查询)
  public void initEmployeeDefaultTableModel(DefaultTableModel dtm, String depId)
  {
    Connection con = this.getConnection();
    int exhibition = 0;
    try
    {
      CallableStatement cs = con.prepareCall("{call selectSalaryByDep(?)}");
      cs.setString(1, depId);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 1; i <= colCount; i++)
      {
        dtm.addColumn(rsmt.getColumnName(i));
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        exhibition = rs.getInt(5);
        if (exhibition == 1)
        {
          cellValue[4] = "优秀";
        }
        else if (exhibition == -1)
        {
          cellValue[4] = "差劲";
        }
        else
        {
          cellValue[4] = "一般";
        }

        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

//初始化员工薪水表模式(按部门,日期查询)
  public void initEmployeeDefaultTableModel(DefaultTableModel dtm, String depId,
                                            String dt)
  {
    Connection con = this.getConnection();
    int exhibition = 0;
    try
    {
      CallableStatement cs = con.prepareCall(
          "{call selectSalaryByDepAndMonth(?,?)}");
      cs.setString(1, depId);
      cs.setString(2, dt);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 1; i <= colCount; i++)
      {
        dtm.addColumn(rsmt.getColumnName(i));
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        exhibition = rs.getInt(5);
        if (exhibition == 1)
        {
          cellValue[4] = "优秀";
        }
        else if (exhibition == -1)
        {
          cellValue[4] = "差劲";
        }
        else
        {
          cellValue[4] = "一般";
        }

        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

//返回个人年终奖金
  public void initSelfBonusTableModel(DefaultTableModel dtm, String empId)
  {
    Connection con = this.getConnection();
    try
    {
      PreparedStatement prst = con.prepareStatement(
          "select year_id,employeeBonus.emp_id,emp_name,bonus from EmployeeBonus,employee where employeeBonus.emp_id=? and employeeBonus.emp_id=employee.emp_id");
      prst.setString(1, empId);
      ResultSet rs = prst.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 1; i <= colCount; i++)
      {
        dtm.addColumn(rsmt.getColumnName(i));
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2] = rs.getDouble(3);
        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

  //初始化员工奖金表模式(按部门查询)
  public void initEmployeeBonusTableModel(DefaultTableModel dtm, String depId)
  {
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall("{call selectBonusByDep(?)}");
      cs.setString(1, depId);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 1; i <= colCount; i++)
      {
        dtm.addColumn(rsmt.getColumnName(i));
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        dtm.addRow(cellValue);
      }
      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

//初始化员工奖金表模式(按部门,日期查询)
  public void initEmployeeBonusTableModel(DefaultTableModel dtm, String depId,
                                          String dt)
  {
    Connection con = this.getConnection();

    try
    {
      CallableStatement cs = con.prepareCall(
          "{call selectBonusByDepAndYear(?,?)}");
      cs.setString(1, depId);
      cs.setString(2, dt);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 1; i <= colCount; i++)
      {
        dtm.addColumn(rsmt.getColumnName(i));
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        dtm.addRow(cellValue);
      }
      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
  }

  //返回员工所有信息并用以初始化表模式(变量type用以表示查询的类型)1:EmpId; 2:EmpName; 3:DepId; 4:JobId; 5:查询所有;
  public Vector initEmployeeInforByEmpIdTableModel(
                                                 String value, int type)
  {
    Connection con = this.getConnection();
    Vector infor=new Vector();
    int marryStatue = 0;
    int exhibition = 0;
    String sqlExecute = "";
    String sqlSelectByEmpId = "{call selectEmployeeDetailInforByEmpId(?)}";
    String sqlSelectByEmpName = "{call selectEmployeeDetailInforByEmpName(?)}";
    String sqlSelectByDepId = "{call selectEmployeeDetailInforByDepId(?)}";
    String sqlSelectByJobId = "{call selectEmployeeDetailInforByJobId(?)}";
    String sqlSelectAll = "{call selectAllEmployeeDetailInfor}";
    if (type == 1)
    {
      sqlExecute = sqlSelectByEmpId;
    }
    else if (type == 2)
    {
      sqlExecute = sqlSelectByEmpName;
    }
    else if (type == 3)
    {
      sqlExecute = sqlSelectByDepId;
    }
    else if (type == 4)
    {
      sqlExecute = sqlSelectByJobId;
    }
    else if (type == 5)
    {
      sqlExecute = sqlSelectAll;
    }
    try
    {
      CallableStatement cs = con.prepareCall(sqlExecute);
      if (type != 5)
      {
        cs.setString(1, value);
      }
      ResultSet rs = cs.executeQuery();


      while (rs.next())
      {

        EmployeeDetailInformation empInfor = new  EmployeeDetailInformation();
        empInfor.setEmpId(rs.getString(1).trim());
        empInfor.setDepName(rs.getString(2).trim());
        empInfor.setJobName(rs.getString(3).trim());
        empInfor.setName(rs.getString(4).trim());
        empInfor.setDegree(rs.getString(5).trim());

        marryStatue = rs.getInt(6);
        if (marryStatue == 1)
        {
          empInfor.setMarryString("已婚");
        }
        else
        {
          empInfor.setMarryString("未婚");
        }

        empInfor.setTel(rs.getString(7).trim());
        empInfor.setAddress(rs.getString(8).trim());
        empInfor.setBirth(rs.getString(9).trim());
        empInfor.setIdCard(rs.getString(10).trim());

        empInfor.setContractTime( rs.getInt(11));
        empInfor.setBeginTime( rs.getString(12).trim());
        exhibition = rs.getInt(13);
        if (exhibition == 1)
        {
          empInfor.setExhibitionStr("优秀");
        }
        else if (exhibition == -1)
        {
          empInfor.setExhibitionStr( "差劲");
        }
        else
        {
          empInfor.setExhibitionStr( "一般");
        }

        infor.add(empInfor);

      }

      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return infor;
  }
  //更新表中的信息
  public boolean updateValuesInTableModel(String []updateValues)
  {
    boolean flag=false;
    int isMarry=0;
    Connection con=this.getConnection();
    try
    {
      CallableStatement cs=con.prepareCall("{call updateValuesInTableModel(?,?,?,?,?,?,?)}");

      for(int i=0;i<3;i++)
      {
        cs.setString(i+1,updateValues[i]);
      }
      if(updateValues[3].equals("已婚"))
        isMarry=1;
      cs.setInt(4,isMarry);
      for(int i=4;i<7;i++)
      {
        cs.setString(i+1,updateValues[i]);
      }

      int i=cs.executeUpdate();
      if(i>0)
        flag=true;
      cs.close();
    }
    catch(SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return flag;
  }
  //删除列表中的一行???????????????
  public boolean deleteEmployeeInTableModel(String empId)
  {
    boolean flag=false;
    Connection con=this.getConnection();
    try
    {
      CallableStatement cs=con.prepareCall("call deleteEmployee(?)");
      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;
  }
}