Пример #1
0
  /**
   * 指定した条件のデータをCSVへ出力する
   *
   * @param searchMdl 検索条件
   * @param sessionUserSid セッションユーザSID
   * @param rl リスナークラス
   * @return List in Tcd010Model 結果を格納したListオブジェクト
   * @throws SQLException SQL実行例外
   * @throws CSVException CSV出力時例外
   */
  public int createTimecardForCsv(
      TcdCsvSearchModel searchMdl, int sessionUserSid, TcdCsvRecordListenerImpl rl)
      throws SQLException, CSVException {

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Connection con = null;
    int ret = 0;
    con = getCon();

    try {
      CmnUsrmInfDao usDao = new CmnUsrmInfDao(con);
      CmnUsrmInfModel usMdl = usDao.select(searchMdl.getTcdCsvUserSid());
      String userName = usMdl.getUsiSei() + " " + usMdl.getUsiMei();
      // SQL文
      SqlBuffer sql = new SqlBuffer();
      sql.addSql(" select ");
      sql.addSql("   CMN_USRM_INF.USR_SID,");
      sql.addSql("   CMN_USRM_INF.USI_SEI,");
      sql.addSql("   CMN_USRM_INF.USI_MEI,");
      sql.addSql("   TCD_TCDATA.TCD_DATE,");
      sql.addSql("   TCD_TCDATA.TCD_INTIME,");
      sql.addSql("   TCD_TCDATA.TCD_OUTTIME,");
      sql.addSql("   TCD_TCDATA.TCD_STRIKE_INTIME,");
      sql.addSql("   TCD_TCDATA.TCD_STRIKE_OUTTIME,");
      sql.addSql("   TCD_TCDATA.TCD_BIKO,");
      sql.addSql("   TCD_TCDATA.TCD_STATUS,");
      sql.addSql("   TCD_TCDATA.TCD_HOLKBN,");
      sql.addSql("   TCD_TCDATA.TCD_HOLOTHER,");
      sql.addSql("   TCD_TCDATA.TCD_HOLCNT,");
      sql.addSql("   TCD_TCDATA.TCD_CHKKBN,");
      sql.addSql("   TCD_TCDATA.TCD_SOUKBN,");
      sql.addSql("   TCD_TCDATA.TCD_LOCK_FLG,");
      sql.addSql("   TCD_TCDATA.TCD_AUID,");
      sql.addSql("   TCD_TCDATA.TCD_ADATE,");
      sql.addSql("   TCD_TCDATA.TCD_EUID,");
      sql.addSql("   TCD_TCDATA.TCD_EDATE");
      sql.addSql(" from ");
      sql.addSql("   CMN_USRM_INF,");
      sql.addSql("   TCD_TCDATA");
      sql.addSql(" where ");
      sql.addSql("   CMN_USRM_INF.USR_SID = ?");
      sql.addSql(" and ");
      sql.addSql("   CMN_USRM_INF.USR_SID = TCD_TCDATA.USR_SID");
      sql.addSql(" and ");
      sql.addSql("   TCD_TCDATA.TCD_DATE >= ?");
      sql.addSql(" and");
      sql.addSql("   TCD_TCDATA.TCD_DATE <= ?");
      sql.addSql(" order by");
      sql.addSql("   TCD_TCDATA.TCD_DATE");

      pstmt = con.prepareStatement(sql.toSqlString());
      sql.addIntValue(searchMdl.getTcdCsvUserSid());
      sql.addDateValue(searchMdl.getTcdCsvFrDate());
      sql.addDateValue(searchMdl.getTcdCsvToDate());
      UDate frDate = searchMdl.getTcdCsvFrDate().cloneUDate();
      UDate toDate = searchMdl.getTcdCsvToDate().cloneUDate();
      log__.info(sql.toLogString());
      sql.setParameter(pstmt);
      rs = pstmt.executeQuery();

      TcdCsvModel model = null;
      TcdCsvModel blankModel = null;
      while (rs.next()) {
        model = __getTcdCsvFromRs(rs);
        while (frDate.compareDateYMD(model.getTcdDate()) == UDate.LARGE) {
          blankModel = new TcdCsvModel();
          blankModel.setTcdDate(frDate);
          blankModel.setUserName(userName);
          rl.setRecord(blankModel);
          frDate.addDay(1);
        }
        rl.setRecord(model);
        frDate.addDay(1);
      }
      while (frDate.compareDateYMD(toDate) != UDate.SMALL) {
        blankModel = new TcdCsvModel();
        blankModel.setTcdDate(frDate);
        blankModel.setUserName(userName);
        rl.setRecord(blankModel);
        frDate.addDay(1);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      JDBCUtil.closeResultSet(rs);
      JDBCUtil.closeStatement(pstmt);
    }

    return ret;
  }
Пример #2
0
  /**
   * CSV出力用モデルを生成
   *
   * @param rs ResultSet
   * @return created TcdCsvModel
   * @throws SQLException SQL実行例外
   */
  private TcdCsvModel __getTcdCsvFromRs(ResultSet rs) throws SQLException {
    TcdCsvModel bean = new TcdCsvModel();
    bean.setUsrSid(rs.getInt("USR_SID"));
    bean.setUserName(rs.getString("USI_SEI") + " " + rs.getString("USI_MEI"));

    bean.setTcdDate(UDate.getInstanceTimestamp(rs.getTimestamp("TCD_DATE")));
    bean.setTcdIntime(rs.getTime("TCD_INTIME"));
    bean.setTcdOuttime(rs.getTime("TCD_OUTTIME"));
    bean.setTcdStrikeIntime(rs.getTime("TCD_STRIKE_INTIME"));
    bean.setTcdStrikeOuttime(rs.getTime("TCD_STRIKE_OUTTIME"));
    bean.setTcdBiko(rs.getString("TCD_BIKO"));
    bean.setTcdStatus(rs.getInt("TCD_STATUS"));
    bean.setTcdHolkbn(rs.getInt("TCD_HOLKBN"));
    bean.setTcdHolother(rs.getString("TCD_HOLOTHER"));
    bean.setTcdHolcnt(rs.getBigDecimal("TCD_HOLCNT"));
    bean.setTcdChkkbn(rs.getInt("TCD_CHKKBN"));
    bean.setTcdSoukbn(rs.getInt("TCD_SOUKBN"));
    bean.setTcdLockFlg(rs.getInt("TCD_LOCK_FLG"));
    bean.setTcdAuid(rs.getInt("TCD_AUID"));
    bean.setTcdAdate(UDate.getInstanceTimestamp(rs.getTimestamp("TCD_ADATE")));
    bean.setTcdEuid(rs.getInt("TCD_EUID"));
    bean.setTcdEdate(UDate.getInstanceTimestamp(rs.getTimestamp("TCD_EDATE")));

    return bean;
  }