view.setNumber(rs.getString("number"));

        //ls.add(view);
      }
    });

    String sql2 = "SELECT count(person_id) FROM pr_person WHERE order_id=" +
        order_id;
    DBConnection dbconn1 = new DBConnection(2);
    DBUtil.ProccessSql(dbconn, sql2, new ResultHandler() {
      /**
       * proccess
       *
       * @param rs ResultSet
       */
      public void proccess(ResultSet rs) throws SQLException {
        view.setCollected(rs.getInt(1));
        ls.add(view);
      }

    });
    return ls;
  }

  public List listByOrder(String orderid, int number) throws Exception {
    DBConnection dbconn = new DBConnection(2);
    String sql = " SELECT DISTINCT p.person_id,p.person_fname,p.person_sname," +
        " p.apply_for,s.personstatus_id,s.personstatus_name FROM i_jobtype j,pr_person p,pr_personstatus s " +
        " WHERE p.personstatus_id=s.personstatus_id AND p.order_id = '" +
        orderid + "'";

    final List list1 = new ArrayList();
    String order_id = orderid;
    final int num = number;

    DBUtil.ProccessSql(dbconn, sql, new ResultHandler() {
      int fin = 0;

      public void proccess(ResultSet rs) throws Exception {
        searchByResult sbr = new searchByResult();
        sbr.setPerson_id(rs.getLong("person_id"));
        sbr.setPerson_fname(rs.getString("person_fname"));
        sbr.setPerson_sname(rs.getString("person_sname"));
        sbr.setApply_for(rs.getString("apply_for"));
        sbr.setPersonstatus_name(rs.getString("personstatus_name"));

        if (rs.getLong("personstatus_id") == 10008) {
          fin++;
          System.out.println(fin);
        }
        sbr.setFinish(fin);

        list1.add(sbr);
      }
    }); // dbconn is not  close; by zeb;
    //DBUtil.close();
    //dbconn.close();
    final List list2 = new ArrayList();
    for (int i = 0; i < list1.size(); i++) {
      String flag = "0";
      final searchByResult sbr = (searchByResult) list1.get(i);
      String sql2 = "SELECT check_id FROM pr_innercheck WHERE order_id = '" +
          orderid + "' AND person_id='" + sbr.getPerson_id() + "'";
      System.out.println(sql2);
      //假設內部面試id不存在
      sbr.setInnercheck_id(0);

      dbconn = new DBConnection(2);
      //查找內部面試id,如果有，假設不成立，否則，假設的"0"成立
      DBUtil.ProccessSql(dbconn, sql2, new ResultHandler() {
        public void proccess(ResultSet rs) throws SQLException {
            sbr.setInnercheck_id(rs.getLong("check_id"));
        }
      });
      //假設客戶面試id不存在
      sbr.setCustomercheck_id(0);
      String sql3 =
         "SELECT checksheet_id FROM pr_customercheck WHERE order_id = '" +
         orderid + "' AND person_id='" + sbr.getPerson_id() + "'";

      //查找客戶面試id,如果有，假設不成立，否則，假設的"0"成立
      DBUtil.ProccessSql(dbconn, sql3, new ResultHandler() {
        public void proccess(ResultSet rs) throws SQLException {
        sbr.setCustomercheck_id(rs.getLong("checksheet_id"));
        }
      });
      //假設歷史成績?yōu)榭?      sbr.setHistoryRecords(0);
      String sql4 =
          "SELECT check_id FROM pr_innercheck WHERE person_id='"+sbr.getPerson_id()+"'";
      //查找check_id,如果有，假設不成立，歷史成績存在，賦值為1
      DBUtil.ProccessSql(dbconn,sql4,new ResultHandler(){
        public void proccess(ResultSet rs) {
          sbr.setHistoryRecords(1);
        }
      });
      list2.add(sbr);
    }
    return list2;
  }

  public boolean updatePersonstatus(String personstatus_id, String whereSql) throws
      Exception {

    if (conn == null) {
      conn = new DBConnection(2);
    }
    final String personstatus = personstatus_id;

    String updateSql =
        "update pr_person set personstatus_id = ?  where " + whereSql;

    try {
      DBUtil.ProccessPreparedStatement(conn, updateSql,
                                       new PreparedStmtementHandler() {
        public void proccess(PreparedStatement pstmt) throws SQLException {
          pstmt.setString(1, personstatus);
        }
      });

    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return true;

  }

  // add by zeb 04.05   ------------------------------------
  public boolean updateStataddOrder(String personstatus_id, String sql,
                                    String order_id,int checknum) throws Exception {
    final String personstatus = personstatus_id;
    final String orderid = order_id;
    String updateSql =
        "update pr_person set personstatus_id = ?,order_id = ?  where " + sql;
    String sql2 =
        "UPDATE o_order set finish_number = finish_number +'"+checknum+"' WHERE order_id ="+orderid;

    if (conn == null) {
      conn = new DBConnection(2);
    }
    try {
      //conn.getConn().setAutoCommit(false);
      //conn.getConn().setSavepoint("DBOP");
      DBUtil.ProccessPreparedStatement(conn, updateSql,
                                       new PreparedStmtementHandler() {
        public void proccess(PreparedStatement pstmt) throws SQLException {
          pstmt.setString(1, personstatus);
          pstmt.setString(2, orderid);
        }
      });
     DBUtil.ProccessPreparedStatement(conn,sql2,new PreparedStmtementHandler(){

        public void proccess(PreparedStatement pstmt) {

        }

      });
    }
    catch (Exception ex) {
      //conn.getConn().rollback();
      //conn.getConn().setAutoCommit(true);
      return false;
    }
    return true;
  }

  public boolean FinishOrder(String orderId, String status) throws Exception {
    String updateSql =
        "update o_order set orderstatus_id = ?  where order_id=" + orderId;
    final String orderstatus = status;
    if (conn == null) {
      conn = new DBConnection(2);
    }
    try {
      DBUtil.ProccessPreparedStatement(conn, updateSql,
                                       new PreparedStmtementHandler() {
        public void proccess(PreparedStatement pstmt) throws SQLException {
          pstmt.setString(1, orderstatus);
        }
      });

    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return true;

  }

  // add by zeb 04.05 ------------------------------------------
  public List listByOrder(searchForm sform) throws Exception {
    final String order_id = sform.getOrder_id();
    DBConnection dbconn = new DBConnection(2);
    String header = " SELECT ";
    String sfield =
        "DISTINCT p.person_id,p.person_fname,p.person_sname,p.apply_for,s.personstatus_id," +
        "s.personstatus_name FROM i_jobtype j,pr_person p,pr_personstatus s ";
    //人員狀態(tài)為待職
    long personstatus_id = 1002;
    //查詢條件
    String condition =
        " WHERE  s.personstatus_id=p.personstatus_id  AND " +
        " p.personstatus_id = " + personstatus_id + " AND ";

    List temp = new ArrayList();

    String sql;
    // 條件組裝
    String jobtype = sform.getJobtype_id(); //
    sform.setJobtype_id("");
    String age_from = sform.getAge_from();
    sform.setAge_from("");
    String age_to = sform.getAge_to();
    sform.setAge_to("");
    String sex = sform.getSex();
    sform.setSex("");
    String major = sform.getMajor_id(); //
    sform.setMajor_id("");
    String degree = sform.getDegree_id(); //
    sform.setDegree_id("");
    String japanese = sform.getLanguage_japanese_level();
    sform.setLanguage_japanese_level("");
    String english = sform.getLanguage_english_level();
    sform.setLanguage_english_level("");
    String salary_from = sform.getSalary_from();
    sform.setSalary_from("");
    String salary_to = sform.getSalary_to();
    sform.setSalary_to("");

    String sft = "";
    if (age_from != null && !age_from.equals("") ||
        (age_to != null && !age_to.equals(""))) {
      String af = "(age BETWEEN " + Integer.parseInt(age_from) + " AND " +
          Integer.parseInt(age_to) + ")";

      temp.add(af);
      af = "";
    }

    if (jobtype != null && !jobtype.equals("")) {
      String jt = "apply_for LIKE '" + jobtype.trim() + "%'";
      temp.add(jt);
    }
    if (sex != null && !sex.equals("")) {
      String s = "sex='" + sex.trim() + "'";
      temp.add(s);
    }
    if (major != null && !major.equals("")) {
      String m = "major='" + major.trim() + "'";
      temp.add(m);
    }
    if (degree != null && !degree.equals("")) {
      String d = "degree='" + degree.trim() + "'";
      temp.add(d);
    }
    if (japanese != null && !japanese.equals("")) {
      String j = "language_jp='" + japanese.trim() + "'";
      temp.add(j);
    }
    if (english != null && !english.equals("")) {
      String e = "language_en='" + english.trim() + "'";
      temp.add(e);
    }
    if (salary_from != null && !salary_from.equals("") ||
        (salary_to != null && !salary_to.equals(""))) {
      String sf = "(salary_requirnet_net BETWEEN '" + salary_from.trim() +
          "' AND '" + salary_to.trim() + "')";

      temp.add(sf);
      sf = "";
    }
    for (int i = 0; i < temp.size(); i++) {

      condition += temp.get(i) + " AND ";
    }
    condition = condition.substring(0, condition.length() - 4);
    sql = header + sfield + condition;

    System.out.println(sql);

    final List list1 = new ArrayList();

    DBUtil.ProccessSql(dbconn, sql, new ResultHandler() {
      int fin = 0;

      public void proccess(ResultSet rs) throws Exception {
        searchByResult sbr = new searchByResult();
        sbr.setPerson_id(rs.getLong("person_id"));
        sbr.setPerson_fname(rs.getString("person_fname"));
        sbr.setPerson_sname(rs.getString("person_sname"));
        sbr.setApply_for(rs.getString("apply_for"));
        sbr.setPersonstatus_name(rs.getString("personstatus_name"));

        sbr.setFinish(fin);
        sbr.setOrder_id(order_id);
        list1.add(sbr);
      }
    }); // dbconn is not  close; by zeb;
    return list1;
  }

  // add by zeb 04.06
  public List listPersonRecords(String person_id) throws Exception {
    DBConnection dbconn = new DBConnection(2);
    final List list = new ArrayList();
    String sql = "SELECT o.order_id,m.customer_name,o.belongto,i.check_id " +
        " FROM o_order o,o_customer m,pr_innercheck i,pr_person p WHERE p.person_id=i.person_id" +
        " AND o.customer_id=m.customer_id AND o.order_id=i.order_id AND p.person_id='" +
        person_id + "'";

    System.out.println(sql);

    DBUtil.ProccessSql(dbconn, sql, new ResultHandler() {

      public void proccess(ResultSet rs) throws Exception {

        searchByRecords sbr = new searchByRecords();
        sbr.setOrder_id(rs.getLong("order_id"));
        sbr.setCustomer_name(rs.getString("customer_name"));
        //sbr.setPerson_id(rs.getLong("person_id"));
        //sbr.setPerson_name(rs.getString("name_person"));
        sbr.setBelongto(rs.getString("belongto"));
        sbr.setCheck_id(rs.getLong("check_id"));

        list.add(sbr);
      }
    });

    String sql2 = "";
    List list2 = new ArrayList();
    for (int i = 0; i < list.size(); i++) {
      final searchByRecords sbr = (searchByRecords) list.get(i);
      sql2 = "SELECT checksheet_id FROM pr_customercheck WHERE order_id = '" +
          sbr.getOrder_id() + "' AND person_id='" + person_id + "'";

      sbr.setCuscheck_id(0);
      dbconn = new DBConnection(2);
      DBUtil.ProccessSql(dbconn, sql2, new ResultHandler() {
        public void proccess(ResultSet rs) throws SQLException {

            sbr.setCuscheck_id(rs.getLong("checksheet_id"));

        }

      });
      list2.add(sbr);
    }
    return list2;
  }

  //add by zeb 04.07
  public boolean delPersonFromOrder(String personstatus_id,String orderid,String whereSql,int checkNum) throws
      Exception {
    String updateSql =
        "update pr_person set personstatus_id = ?,order_id = ?  where " +
        whereSql;
    String sql2 =
        "UPDATE o_order set finish_number = finish_number -'"+checkNum+"' WHERE order_id ="+orderid;
    final String personstatus = personstatus_id;
    final String orderid1 = "";
    if (conn == null) {
      conn = new DBConnection(2);
    }
    try {
      //conn.getConn().setSavepoint("DBOP");
      DBUtil.ProccessPreparedStatement(conn, updateSql,
                                       new PreparedStmtementHandler() {
        public void proccess(PreparedStatement pstmt) throws SQLException {
          pstmt.setString(1, personstatus);
          pstmt.setString(2, orderid1);
        }
      });
      DBUtil.ProccessPreparedStatement(conn,sql2,new PreparedStmtementHandler(){

       public void proccess(PreparedStatement pstmt) {
       }
     });

    }
    catch (Exception ex) {
      //ex.printStackTrace();
      //conn.getConn().rollback();
      return false;
    }
    return true;
  }

}