public static void format(Connection con, String kq_date, String branch_id) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    StuKqFormatNew stuKqFormatNewObj = null;

    JLog.getLogger().debug("branch_id=" + branch_id);

    java.sql.Timestamp dKqDate = new java.sql.Timestamp(JUtil.str2SQLDate(kq_date).getTime());
    java.sql.Timestamp nextDate = new java.sql.Timestamp(JUtil.relativeDate(dKqDate, 1).getTime());

    sql =
        " select a.* from t_fl_class_schedule a, shinyway.t_fl_class b "
            + " where a.class_id=b.class_id and b.branch_id=?"
            + " and a.begin_time >=? and a.begin_time <?";
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, branch_id);
    pstmt.setTimestamp(2, dKqDate);
    pstmt.setTimestamp(3, nextDate);
    JLog.getLogger()
        .debug(
            "sql="
                + sql
                + " branch_id="
                + branch_id
                + " dKqDate="
                + dKqDate
                + " nextDate="
                + nextDate);
    rs = pstmt.executeQuery();
    while (rs.next()) {
      ScheduleDate scheduleObj = new ScheduleDate();
      scheduleObj.setLine_id(rs.getString("line_id"));
      scheduleObj.setClass_id(rs.getString("class_id"));
      scheduleObj.setBegin_time(rs.getTimestamp("begin_time"));
      scheduleObj.setEnd_time(rs.getTimestamp("end_time"));
      JLog.getLogger().debug("class_id=" + scheduleObj.getClass_id());
      formatClassSchedule(con, scheduleObj);
    }
    rs.close();
    pstmt.close();
  }
  /**
   * @param biz_type 留学、移民、培训、游学。如果为null则全部
   * @return
   */
  public Map<String, List<JCompany>> getCompanysWithBiz(String biz_type) {
    List<JCompany> lsCompany = null;
    biz_type = JUtil.convertNull(biz_type);
    Map<String, List<JCompany>> districtCompany = new LinkedHashMap<String, List<JCompany>>();
    String[] districts = JCompany.getDistricts();
    for (String district : districts) districtCompany.put(district, new ArrayList<JCompany>());
    if (biz_type.equals("lx")) lsCompany = getCompanyListByBiz("留学");
    else if (biz_type.equals("fl")) lsCompany = getCompanyListByBiz("外语");
    else if (biz_type.equals("ym")) lsCompany = getCompanyListByBiz("移民");
    else if (biz_type.equals("yx")) lsCompany = getCompanyListByBiz("游学");
    else lsCompany = getCompanyList();

    for (JCompany company : lsCompany) {
      String district = JUtil.convertNull(company.getCompany_district());
      if (district.length() == 0) district = "总部";
      List<JCompany> companys = districtCompany.get(district);
      companys.add(company);
      districtCompany.put(district, companys);
    }

    return districtCompany;
  }
  private void loadCompany(Connection con, String unit_code) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String qry = null;

    JCompany obj = null;

    qry =
        " select unit_id, unit_code, unit_name, biz_lx, biz_ym, biz_fl, biz_yx, biz_ch, "
            + "        company_fullName, company_addr,company_district,unit_created "
            + "   from t_unit "
            + "  where unit_code=? ";
    pstmt = con.prepareStatement(qry);
    pstmt.setString(1, unit_code.toUpperCase());
    rs = pstmt.executeQuery();
    if (rs.next()) {
      Set<String> bizSet = null;
      String[] bizTypes = null;
      String szId, szCode, szName;

      bizSet = new HashSet<String>();
      szId = rs.getString("unit_id");
      szCode = rs.getString("unit_code");
      szName = JUtil.convertNull(rs.getString("unit_name"));

      {
        if (rs.getInt("biz_lx") == 1) bizSet.add("留学");
        if (rs.getInt("biz_ym") == 1) bizSet.add("移民");
        if (rs.getInt("biz_yx") == 1) bizSet.add("团组");
        if (rs.getInt("biz_fl") == 1) bizSet.add("外语");
        if (rs.getInt("biz_ch") == 1) bizSet.add("华文");
        bizTypes = new String[bizSet.size()];
        int i = 0;
        for (String biz : bizSet) {
          bizTypes[i++] = biz;
        }
      }

      obj = new JCompany(szId, szCode, szName, bizTypes);

      obj.setCompany_fullName(rs.getString("company_fullName"));
      obj.setCompany_address(rs.getString("company_addr"));
      obj.setCompany_district(rs.getString("company_district"));
      obj.setUnit_created(rs.getDate("unit_created"));
      obj.setBranch(getChildBranch(con, szCode));
    }
    rs.close();
    pstmt.close();

    if (obj == null) return;

    /** ******** 放到 列表 中 ****************** */
    String szId = obj.getCompany_id();
    String szCode = obj.getCompany_code();

    lsBizCompany.remove(szId);
    if (!szCode.startsWith("XT_NB.")) lsBizCompany.add(szId);

    lsOACompany.contains(szId);
    lsOACompany.add(szId);

    mapCompany.put(obj.getCompany_code(), obj);
    int idx = lsCompany.size();
    for (int i = 0; i < lsCompany.size(); i++) {
      JCompany com = lsCompany.get(i);
      if (com.getCompany_id().equals(szId)) {
        idx = i;
        lsCompany.remove(i);
        break;
      }
    }
    lsCompany.add(idx, obj);
  }
  /**
   * 格式化某位员工 某一天的 考勤信息
   *
   * @param con
   * @param schedule_id 某节课ID t_fl_class_schedule.line_id
   * @throws Exception
   */
  private static void formatClassSchedule(Connection con, ScheduleDate scheduleObj)
      throws Exception {
    Connection con_igo = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    StuKqFormatNew stuKqFormatNewObj = null;
    String class_id = null;
    java.sql.Timestamp begin_time = null;

    class_id = scheduleObj.getClass_id();
    begin_time = scheduleObj.getBegin_time();

    String szClassDate = JUtil.formatDate(begin_time, "YYYY-MM-DD");

    java.sql.Timestamp dKqDate = new java.sql.Timestamp(JUtil.str2SQLDate(szClassDate).getTime());
    java.sql.Timestamp nextDate = new java.sql.Timestamp(JUtil.relativeDate(dKqDate, 1).getTime());

    java.util.List<StuKqFormatNew> lsStuKqFormatNew = new java.util.ArrayList<StuKqFormatNew>();
    try {
      // 附加学生基本信息
      con_igo = com.gemway.igo.JDatabase.getJDatabase().getConnection();

      sql =
          "	select a.stu_id, b.stu_no, b.company_id, b.father_phone, c.cstm_name, c.cstm_phone, c.cstm_handset  "
              + " from t_fl_course_stu a, t_fl_student b, t_custom c "
              + " where a.cstm_id=b.cstm_id and b.cstm_id = c.cstm_id and a.course_id=?";
      pstmt = con_igo.prepareStatement(sql);
      pstmt.setString(1, class_id);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        stuKqFormatNewObj = new StuKqFormatNew();
        stuKqFormatNewObj.setKq_id(com.gemway.util.ID.getIDObject("DEFAULT").create());
        stuKqFormatNewObj.setKq_date(JUtil.str2SQLDate(szClassDate));
        stuKqFormatNewObj.setClass_id(scheduleObj.getClass_id());
        stuKqFormatNewObj.setLine_id(scheduleObj.getLine_id());
        stuKqFormatNewObj.setStu_name(rs.getString("cstm_name"));
        stuKqFormatNewObj.setStu_no(rs.getString("stu_no"));
        stuKqFormatNewObj.setIs_late(0);
        stuKqFormatNewObj.setEarlygo(0);
        stuKqFormatNewObj.setAbsenteeism(0);
        stuKqFormatNewObj.setLeave_out(0);
        stuKqFormatNewObj.setFather_phone(rs.getString("father_phone"));
        lsStuKqFormatNew.add(stuKqFormatNewObj);
      }
      rs.close();
      pstmt.close();
    } catch (Exception e) {
      throw e;
    } finally {
      if (con_igo != null) con_igo.close();
    }

    // 格式化前删除原来信息
    sql = "delete from t_fl_stu_kq_format_new where line_id=?";
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, scheduleObj.getLine_id());
    pstmt.executeUpdate();
    pstmt.close();

    for (int i = 0; i < lsStuKqFormatNew.size(); i++) {
      stuKqFormatNewObj = lsStuKqFormatNew.get(i);
      sql =
          " insert into t_fl_stu_kq_format_new "
              + " (kq_id, kq_date, class_id, line_id, stu_name, stu_no, is_late, "
              + "  earlygo, absenteeism, leave_out, father_phone, created) "
              + " values(?,?,?,?,?,?,?,?,?,?,?,?)";
      pstmt = con.prepareStatement(sql);
      int rowIndex = 1;
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getKq_id());
      pstmt.setDate(rowIndex++, stuKqFormatNewObj.getKq_date());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getClass_id());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getLine_id());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getStu_name());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getStu_no());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getIs_late());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getEarlygo());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getAbsenteeism());
      pstmt.setInt(rowIndex++, stuKqFormatNewObj.getLeave_out());
      pstmt.setString(rowIndex++, stuKqFormatNewObj.getFather_phone());
      pstmt.setTimestamp(rowIndex++, new java.sql.Timestamp(System.currentTimeMillis()));
      pstmt.executeUpdate();
      pstmt.close();
    }
  }