sqlclass.java

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

        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="";
      String dateLast="";

      SimpleDateFormat sdf1=new SimpleDateFormat("MM");
       SimpleDateFormat sdf2=new SimpleDateFormat("MM");
      int month=Integer.parseInt(sdf1.format(dt));
      int year=Integer.parseInt(sdf2.format(dt));

      if((year%4==0&&year/100!=0)||year%400==0)
      {
        switch (month)
        {
          case 1:
          case 3:
          case 5:
          case 7:
          case 8:
          case 10:
          case 12:
            dateLast = sdf.format(dt) + "-31";
            break;
          case 4:
          case 6:
          case 9:
          case 11:
            dateLast = sdf.format(dt) + "-30";
            break;
          case 2:
            dateLast = sdf.format(dt) + "-29";
            break;
        }
      }
      else
      {
        switch (month)
        {
          case 1:
          case 3:
          case 5:
          case 7:
          case 8:
          case 10:
          case 12:
            dateLast = sdf.format(dt) + "-31";
            break;
          case 4:
          case 6:
          case 9:
          case 11:
            dateLast = sdf.format(dt) + "-30";
            break;
          case 2:
            dateLast = sdf.format(dt) + "-28";
            break;
        }
      }
      dateFirst = sdf.format(dt) + "-1";


      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");
      SimpleDateFormat sdf1=new SimpleDateFormat("MM");
       SimpleDateFormat sdf2=new SimpleDateFormat("MM");
      int month=Integer.parseInt(sdf1.format(dt));
      int year=Integer.parseInt(sdf2.format(dt));
      String dateFirst="";
      String dateLast="";
      if((year%4==0&&year/100!=0)||year%400==0)
      {
        switch (month)
        {
          case 1:
          case 3:
          case 5:
          case 7:
          case 8:
          case 10:
          case 12:
            dateLast = sdf.format(dt) + "-31";
            break;
          case 4:
          case 6:
          case 9:
          case 11:
            dateLast = sdf.format(dt) + "-30";
            break;
          case 2:
            dateLast = sdf.format(dt) + "-29";
            break;
        }
      }
      else
      {
        switch (month)
        {
          case 1:
          case 3:
          case 5:
          case 7:
          case 8:
          case 10:
          case 12:
            dateLast = sdf.format(dt) + "-31";
            break;
          case 4:
          case 6:
          case 9:
          case 11:
            dateLast = sdf.format(dt) + "-30";
            break;
          case 2:
            dateLast = sdf.format(dt) + "-28";
            break;
        }

      }
      dateFirst = sdf.format(dt) + "-1";

      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 +
                          "' 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.setInt(4,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)