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