📄 gbs_reply_db.java
字号:
out_st.setParUserID(rset.getString("PAR_USER_ID"));
out_st.setParSubsidiaryCode(rset.getString("PAR_SUBSIDIARY_CODE"));
out_st.setNdaStatus(rset.getString("NDA_STATUS"));
out_st.setNdaUserID(rset.getString("NDA_USER_ID"));
out_st.setNdaUpdateDate(rset.getString("NDA_UPDATE_DATE"));
out_st.setNdaSubsidiaryCode(rset.getString("NDA_SUBSIDIARY_CODE"));
out_st.setRepStatus(rset.getString("REP_STATUS"));
out_st.setRepUpdateDate(rset.getString("REP_UPDATE_DATE"));
out_st.setRepUserID(rset.getString("REP_USER_ID"));
out_st.setRepSubsidiaryCode(rset.getString("REP_SUBSIDIARY_CODE"));
out_st.setResStatus(rset.getString("RES_STATUS"));
out_st.setResUserID(rset.getString("RES_USER_ID"));
out_st.setResUpdateDate(rset.getString("RES_UPDATE_DATE"));
out_st.setResSubsidiaryCode(rset.getString("RES_SUBSIDIARY_CODE"));
out_st.setCreateDate(rset.getString("CREATE_DATE"));
out_st.setCreateUser(rset.getString("CREATE_USER"));
out_st.setUpdateDate(rset.getString("UPDATE_DATE"));
out_st.setUpdateUser(rset.getString("UPDATE_USER"));
}
returnValue.setDataValue(out_st);
// END GET DATA
// //////////////////////////////////////////////////////////////////////////
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//CLOSE DB CONN
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValue;
}
/**
* get service info by sql
* @return List
* @exception Exception Exception for information of other errors
* @since 2004/08/12
*/
public ReturnValue getReplyList(String CustomerID, String RfpID, int plusHour) throws Exception {
//Add by Gxk 2004/09/10 Start
CustomerID = BaseCommonCheck.convertSql(CustomerID);
RfpID = BaseCommonCheck.convertSql(RfpID);
//Add by Gxk 2004/09/10 End
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
ArrayList retdata = new ArrayList(); //Vector();
StringBuffer sql = new StringBuffer();
sql.append(" SELECT ");
sql.append(" T_REPLY.CUSTOMER_ID, ");
sql.append(" T_REPLY.RFP_NO, ");
sql.append(" T_REPLY.REPLY_KBN, ");
sql.append(" T_REPLY.SUBSIDIARY_CODE, ");
sql.append(" T_REPLY.LINE_NO, ");
sql.append(" T_REPLY.TITLE, ");
sql.append(" T_REPLY.COMMENT1, ");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME, ");
sql.append(
" ( T_CREATE_USER.MEMBER_NAME_FIRST || ' ' || T_CREATE_USER.MEMBER_NAME_LAST ) CREATE_USER, ");
//
sql.append(" T_REPLY.CREATE_DATE, "); //
sql.append(
" ( T_UPDATE_USER.MEMBER_NAME_FIRST || ' ' || T_UPDATE_USER.MEMBER_NAME_LAST) UPDATE_USER, ");
//
sql.append(" T_REPLY.UPDATE_DATE, "); //
sql.append(" T_REPLY.ATTACHMENT_FILE_NAME "); //
sql.append(" FROM ");
sql.append(" T_REPLY, M_MEMBER T_CREATE_USER, M_MEMBER T_UPDATE_USER , M_SUBSIDIARY");
sql.append(" WHERE ");
sql.append(" T_REPLY.CUSTOMER_ID='" + CustomerID + "'");
sql.append(" AND T_REPLY.RFP_NO='" + RfpID + "'");
sql.append(" AND T_CREATE_USER.USER_ID(+) = T_REPLY.CREATE_USER ");
sql.append(" AND T_UPDATE_USER.USER_ID(+) = T_REPLY.UPDATE_USER ");
sql.append(" AND M_SUBSIDIARY.SUBSIDIARY_CODE(+) = T_REPLY.SUBSIDIARY_CODE ");
sql.append(" ORDER BY ");
sql.append(" T_Reply.REPLY_KBN, T_Reply.ATTACH_DATE DESC");
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
while (rset.next()) {
GBS_ReplyList_stBean line = new GBS_ReplyList_stBean();
line.setCustomerID(rset.getString("CUSTOMER_ID"));
line.setRfpNo(rset.getString("RFP_NO"));
line.setReplyKbn(rset.getString("REPLY_KBN"));
line.setSubCode(rset.getString("SUBSIDIARY_CODE"));
line.setLineNo(rset.getString("LINE_NO"));
line.setTitle(rset.getString("TITLE"));
line.setComment(rset.getString("COMMENT1"));
line.setSubName(rset.getString("SUBSIDIARY_NAME"));
line.setCreateuserName(rset.getString("CREATE_USER"));
line.setUpdateuserName(rset.getString("UPDATE_USER"));
Calendar date = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
rset.getTimestamp("CREATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
line.setCreatedate(format.format(date.getTime()));
rset.getTimestamp("UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
line.setUpdatedate(format.format(date.getTime()));
String filename = rset.getString("ATTACHMENT_FILE_NAME");
String filetype = BaseCommonCheck.getFileType(filename);
line.setFiletype(filetype);
line.setAttachmentfilename(filename);
retdata.add(line);
}
returnValueD.setDataValue(retdata);
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//release db
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValueD;
}
/**
* get info by sql
* @return List
* @exception Exception Exception for information of other errors
* @since 2004/07/29
*/
// public ReturnValue getReply(String customerID,
// String rfpNo,
// String replyKbn,
// String subCode,
// String lineNo) throws Exception {
// GBS_Reply_stBean retdata = new GBS_Reply_stBean();
// StringBuffer sql = new StringBuffer();
// ReturnValue returnValueD = new ReturnValue();
// MessageList messageList = new MessageList();
// returnValueD.setMessageList( messageList );
//
// sql.append(" SELECT");
// sql.append(" CUSTOMER_ID, ");
// sql.append(" RFP_NO, ");
// sql.append(" REPLY_KBN, ");
// sql.append(" SUBSIDIARY_CODE, ");
// sql.append(" LINE_NO, ");
// sql.append(" TITLE, ");
// sql.append(" COMMENT, ");
// sql.append(" CREATE_USER, ");
// sql.append(" TO_CHAR(CREATE_DATE,'DD-Mon-YYYY') CREATE_DATE, ");
// sql.append(" UPDATE_USER, ");
// sql.append(" TO_CHAR(UPDATE_DATE,'DD-Mon-YYYY') UPDATE_DATE, ");
// sql.append(" ATTACHMENT_FILE_NAME ");
// sql.append(" FROM ");
// sql.append(" T_REPLY ");
// sql.append(" WHERE ");
// sql.append(" CUSTOMER_ID ='");
// sql.append( customerID );
// sql.append("' AND RFP_NO =");
// sql.append( rfpNo );
// sql.append(" AND REPLY_KBN ='");
// sql.append( replyKbn );
// sql.append("' AND SUBSIDIARY_CODE ='");
// sql.append( subCode );
// sql.append("' AND LINE_NO =");
// sql.append( lineNo );
// System.out.println( "[INFO] sql = " + sql.toString() );
//
// ResultSet rset = null;
// Connection conn = null;
// Statement st = null;
// try{
// conn = this.datasource.getConnection();
// st = conn.createStatement();
// rset = st.executeQuery(sql.toString());
// if(rset.next()){
// retdata.setReplyKbn(rset.getString("REPLY_KBN"));
// retdata.setSubCode(rset.getString("SUBSIDIARY_CODE"));
// retdata.setLineNo(rset.getString("LINE_NO"));
// retdata.setTitle(rset.getString("TITLE"));
// retdata.setComment(rset.getString("COMMENT"));
// retdata.setCreatedate(rset.getString("CREATE_DATE"));
// retdata.setCreateuser(rset.getString("CREATE_USER"));
// retdata.setUpdatedate(rset.getString("UPDATE_DATE"));
// retdata.setUpdateuser(rset.getString("UPDATE_USER"));
// retdata.setAttachmentfilename(rset.getString("ATTACHMENT_FILE_NAME"));
// }else{
// retdata.setReplyKbn("");
// retdata.setSubCode("");
// retdata.setLineNo("");
// retdata.setTitle("");
// retdata.setComment("");
// retdata.setCreatedate("");
// retdata.setCreateuser("");
// retdata.setUpdatedate("");
// retdata.setUpdateuser("");
// retdata.setAttachmentfilename("");
// //data not found;
// messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
// returnValueD.setBussinessError();
// }
// returnValueD.setDataValue(retdata);
// }catch (Exception exception) {
// System.out.println("[Error Happen!]");
// System.out.println("[Start Trace]");
// exception.printStackTrace();
// System.out.println("[End Trace]");
// throw exception;
// }finally {
// //release db
// try {
// if (rset != null) {
// rset.close();
// }
// if (st != null) {
// st.close();
// }
// if (conn != null) {
// conn.close();
// }
// } catch (SQLException se) {
// }
// }
// return returnValueD;
// }
/**
* update AttachmentFile by sql
* @return boolean
* @exception Exception Exception for information of other errors
* @since 2004/08/13
*/
public ReturnValue updateAttachmentFile(String strSeqNo, FormFile attachmentFile, Connection conn)
throws Exception {
//Add by Gxk 2004/09/10 Start
strSeqNo = BaseCommonCheck.convertSql(strSeqNo);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
int ret = 0;
sql.append(" SELECT");
sql.append(" ATTACHMENT_FILE, ATTACHMENT_FILE_NAME");
sql.append(" FROM ");
sql.append(" T_TEMP ");
sql.append(" WHERE ");
sql.append(" T_TEMP.SEQ_NO =");
sql.append(strSeqNo);
sql.append(" FOR UPDATE ");
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
try {
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
if (rset.next()) {
blob = (BLOB) rset.getBlob("ATTACHMENT_FILE");
} else {
ret = -1;
}
if (ret == 0) {
sql = new StringBuffer();
sql.append(" UPDATE T_TEMP SET ATTACHMENT_FILE = ? ,");
sql.append(" ATTACHMENT_FILE_NAME = ?");
sql.append(" WHERE ");
sql.append(" T_TEMP.SEQ_NO = ?");
sql.append(strSeqNo);
pstmt = conn.prepareStatement(sql.toString());
OutputStream out = blob.getBinaryOutputStream();
out.write(attachmentFile.getFileData());
out.close();
pstmt.setBlob(1, blob);
pstmt.setString(2, attachmentFile.getFileName());
pstmt.setString(3, strSeqNo);
ret = pstmt.executeUpdate();
}
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//release db
try {
if (rset != null) {
rset.close();
}
if (pstmt != null) {
pstmt.close();
}
if (st != null) {
st.close();
}
} catch (SQLException se) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
se.printStackTrace();
System.out.println("[End Trace]");
throw se;
}
}
returnValueD.setDataValue(new Integer(ret));
return returnValueD;
}
public ReturnValue insertReplyAttachment(GBS_ReplyList_stBean replyListBean, Connection conn) throws Exception {
//Add by Gxk 2004/09/10 Start
replyListBean.setReplyKbn(BaseCommonCheck.convertSql(replyListBean.getReplyKbn()));
replyListBean.setSubCode(BaseCommonCheck.convertSql(replyListBean.getSubCode()));
replyListBean.setLineNo(BaseCommonCheck.convertSql(replyListBean.getLineNo()));
replyListBean.setTitle(BaseCommonCheck.convertSql(replyListBean.getTitle()));
replyListBean.setComment(BaseCommonCheck.convertSql(replyListBean.getComment()));
replyListBean.setSubName(BaseCommonCheck.convertSql(replyListBean.getSubName()));
replyListBean.setCreateuser(BaseCommonCheck.convertSql(replyListBean.getCreateuser()));
replyListBean.setCreateuserName(BaseCommonCheck.convertSql(replyListBean.getCreateuserName()));
replyListBean.setUpdateuserName(BaseCommonCheck.convertSql(replyListBean.getUpdateuserName()));
replyListBean.setCreatedate(BaseCommonCheck.convertSql(replyListBean.getCreatedate()));
replyListBean.setUpdateuser(BaseCommonCheck.convertSql(replyListBean.getUpdateuser()));
replyListBean.setUpdatedate(BaseCommonCheck.convertSql(replyListBean.getUpdatedate()));
replyListBean.setAttachmentfilename(BaseCommonCheck.convertSql(replyListBean.getAttachmentfilename()));
replyListBean.setFiletype(BaseCommonCheck.convertSql(replyListBean.getFiletype()));
replyListBean.setSeqNo(BaseCommonCheck.convertSql(replyListBean.getSeqNo()));
replyListBean.setCustomerID(BaseCommonCheck.convertSql(replyListBean.getCustomerID()));
replyListBean.setRfpNo(BaseCommonCheck.convertSql(replyListBean.getRfpNo()));
//Add by Gxk 2004/09/10 End
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
StringBuffer sql = new StringBuffer();
StringBuffer sqlMax = new StringBuffer();
//SQL DEFINE
// start sqlMax ===========================
sqlMax.append(" SELECT ");
sqlMax.append(" MAX(LINE_NO) AS SEQ");
sqlMax.append(" FROM T_REPLY");
sqlMax.append(" WHERE ");
sqlMax.append(" CUSTOMER_ID ='" + replyListBean.getCustomerID() + "' ");
sqlMax.append(" AND RFP_NO ='" + replyListBean.getRfpNo() + "' ");
sqlMax.append(" AND REPLY_KBN ='" + replyListBean.getReplyKbn() + "' ");
sqlMax.append(" AND SUBSIDIARY_CODE ='" + replyListBean.getSubCode() + "' ");
System.out.println("[INFO] sql = " + sqlMax.toString());
// end sqlMax ===========================
// start sql ===========================
sql.append(" INSERT INTO T_REPLY ( ");
sql.append(" CUSTOMER_ID, ");
sql.append(" RFP_NO, ");
sql.append(" REPLY_KBN, ");
sql.append(" SUBSIDIARY_CODE, ");
sql.append(" LINE_NO, ");
sql.append(" ATTACH_DATE, ");
sql.append(" TITLE, ");
sql.append(" COMMENT1, ");
sql.append(" ATTACHMENT_FILE_NAME, ");
sql.append(" ATTACHMENT_FILE, ");
sql.append(" UPDATE_USER, ");
sql.append(" UPDATE_DATE, ");
sql.append(" CREATE_USER, ");
sql.append(" CREATE_DATE) ");
sql.append(" VALUES( ");
sql.append(" '" + replyListBean.getCustomerID() + "', ");
sql.append(" '" + replyListBean.getRfpNo() + "', ");
sql.append(" '" + replyListBean.getReplyKbn() + "', ");
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -