/** * 指定した条件のデータを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; }
/** * 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; }