📄 gbs_reply_db.java
字号:
/**
* method GBS_ReplyFrom_DB.java
* created on 08-11-2004
*
* @author q
* @version 1.0
*/
package DB;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;
import java.util.Vector;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import oracle.sql.BLOB;
import org.apache.struts.upload.FormFile;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_Customer_stBean;
import DataBean.GBS_File_stBean;
import DataBean.GBS_RFP_stBean;
import DataBean.GBS_ReplyList_stBean;
import DataBean.GBS_RfpCountry_stBean;
public class GBS_Reply_DB implements SystemConstants {
private DataSource datasource = null;
public GBS_Reply_DB(DataSource datasource) {
this.datasource = datasource;
}
public ReturnValue selectSubRfpCountry(String customerID, String rfpNo) throws Exception {
//Add by Gxk 2004/09/10 Start
customerID = BaseCommonCheck.convertSql(customerID);
rfpNo = BaseCommonCheck.convertSql(rfpNo);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append("SELECT ");
sql.append("T_RFP_COUNTRY.COUNTRY_CODE, ");
sql.append("T_RFP_COUNTRY.SUBSIDIARY_CODE, ");
sql.append("M_COUNTRY.COUNTRY_NAME, ");
sql.append("M_SUBSIDIARY.SUBSIDIARY_NAME ");
sql.append("FROM ");
sql.append("T_RFP_COUNTRY, ");
sql.append("M_COUNTRY, ");
sql.append("M_SUBSIDIARY ");
sql.append("WHERE T_RFP_COUNTRY.CUSTOMER_ID = '" + customerID + "' ");
sql.append(" AND T_RFP_COUNTRY.RFP_NO = '" + rfpNo + "' ");
sql.append(" AND T_RFP_COUNTRY.SUBSIDIARY_CODE = M_SUBSIDIARY.SUBSIDIARY_CODE ");
sql.append(" AND T_RFP_COUNTRY.COUNTRY_CODE = M_COUNTRY.COUNTRY_CODE(+) ");
sql.append("ORDER BY ");
sql.append(" CUSTOMER_ID, RFP_NO ");
// END EDIT SQL
// DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
GBS_RfpCountry_stBean out_st = null;
ArrayList list = new ArrayList();
// END
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
// //////////////////////////////////////////////////////////////////////////
// GET DATA FROM DB RESULTSET
while (rset.next()) {
out_st = new GBS_RfpCountry_stBean();
out_st.setCountryCode(rset.getString("COUNTRY_CODE"));
out_st.setCountryName(rset.getString("COUNTRY_NAME"));
out_st.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
out_st.setSubsidiaryName(rset.getString("SUBSIDIARY_NAME"));
list.add(out_st);
}
returnValue.setDataValue(list);
// 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;
}
public ReturnValue insertTemp(String strSessionID, String strCreateUser, FormFile attachmentFile)
throws Exception {
//Add by Gxk 2004/09/10 Start
strSessionID = BaseCommonCheck.convertSql(strSessionID);
strCreateUser = BaseCommonCheck.convertSql(strCreateUser);
//Add by Gxk 2004/09/10 End
StringBuffer sqlSel = new StringBuffer();
StringBuffer sqlIn = new StringBuffer();
StringBuffer sqlInStr = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
int lineNo = 1;
int bresult = 0;
sqlSel.append(" SELECT MAX(SEQ_NO) AS LINE_NO FROM T_TEMP ");
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sqlSel.toString());
if (rset.next()) {
lineNo = rset.getInt("LINE_NO");
lineNo = lineNo + 1;
}
sqlInStr.append(" INSERT INTO T_TEMP ");
sqlInStr.append(" VALUES( '");
sqlInStr.append(lineNo + "','");
sqlInStr.append(strSessionID + "','',EMPTY_BLOB(),'");
sqlInStr.append(strCreateUser + "',SYSDATE)");
//output sql
System.out.println("[INFO] sql = " + sqlInStr.toString());
pstmt = conn.prepareStatement(sqlInStr.toString());
bresult = pstmt.executeUpdate();
if (bresult > 0 && attachmentFile != null && attachmentFile.getFileSize() > 0) {
ReturnValue returnValue = updateAttachmentFile(String.valueOf(lineNo), attachmentFile, conn);
}
if (bresult > 0) {
conn.commit();
} else {
conn.rollback();
}
} 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) {
throw se;
}
}
if (bresult > 0) {
returnValueD.setDataValue(new Integer(lineNo));
return returnValueD;
}
returnValueD.setDataValue(new Integer(bresult));
return returnValueD;
}
public ReturnValue selectCustomer(String customerID) throws Exception {
//Add by Gxk 2004/09/10 Start
customerID = BaseCommonCheck.convertSql(customerID);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append("SELECT ");
sql.append(" CUSTOMER_ID , "); //屭媞ID
sql.append(" DISP_ORDER , "); //昞帵弴彉
sql.append(" CUSTOMER_CATEGORY,"); //屭媞暘椶
sql.append(" CUSTOMER_CODE, "); //屭媞僐乕僪
sql.append(" CUSTOMER_NAME, "); //屭媞柤
sql.append(" CUSTOMER_ABBR, "); //屭媞棯柤
sql.append(" INDUSTRY, "); //嬈奅
sql.append(" ADDR1, "); //廧強侾
sql.append(" ADDR2, "); //廧強俀
sql.append(" ZIP_CODE, "); //梄曋斣崋
sql.append(" COUNTRY_CODE, "); //崙僐乕僪
sql.append(" TELEPHONE, "); //揹榖斣崋
sql.append(" FAX, "); //FAX斣崋
sql.append(" E_MAIL, "); //E_MAIL傾僪儗僗
sql.append(" CONTACT_PERSON, "); //愭曽扴摉幰
sql.append(" URL, "); //URL
sql.append(" DELETE_FLG, "); //DELETE僼儔僌
sql.append(" NUMBER_OF_EMPLOYEE, "); //廬嬈堳悢
sql.append(" CREATE_USER, "); //嶌惉儐乕僓乕
sql.append(" CREATE_DATE, "); //嶌惉擔帪
sql.append(" UPDATE_USER, "); //峏怴儐乕僓乕
sql.append(" UPDATE_DATE "); //峏怴擔帪
sql.append("FROM ");
sql.append("M_CUSTOMER ");
sql.append("WHERE (DELETE_FLG<>'D' OR DELETE_FLG IS NULL) ");
if (!"".equals(customerID)) {
sql.append(" AND CUSTOMER_ID = '" + customerID + "' ");
}
sql.append("ORDER BY ");
sql.append(" DISP_ORDER ");
//END EDIT SQL
//DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
GBS_Customer_stBean out_st = new GBS_Customer_stBean();
//END
/////////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
while (rset.next()) {
out_st.setAddr1(rset.getString("ADDR1"));
out_st.setAddr2(rset.getString("ADDR2"));
out_st.setContactPerson(rset.getString("CONTACT_PERSON"));
out_st.setCountryCode(rset.getString("COUNTRY_CODE"));
out_st.setCreateDate(rset.getString("CREATE_DATE"));
out_st.setCreateUser(rset.getString("CREATE_USER"));
out_st.setCustomerAbbr(rset.getString("CUSTOMER_ABBR"));
out_st.setCustomerCode(rset.getString("CUSTOMER_CODE"));
out_st.setCustomerID(rset.getString("CUSTOMER_ID"));
out_st.setCustomerName(rset.getString("CUSTOMER_NAME"));
out_st.setDeleteFlg(rset.getString("DELETE_FLG"));
out_st.setDispOrder(rset.getString("DISP_ORDER"));
out_st.setEmail(rset.getString("E_MAIL"));
out_st.setFax(rset.getString("FAX"));
out_st.setIndustry(rset.getString("INDUSTRY"));
out_st.setNumberOfEmployee(rset.getString("NUMBER_OF_EMPLOYEE"));
out_st.setTelephone(rset.getString("TELEPHONE"));
out_st.setUpdateDate(rset.getString("UPDATE_DATE"));
out_st.setUpdateUser(rset.getString("UPDATE_USER"));
out_st.setUrl(rset.getString("URL"));
out_st.setZipCode(rset.getString("ZIP_CODE"));
out_st.setCustomerCategory(rset.getString("CUSTOMER_CATEGORY"));
}
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;
}
public ReturnValue selectRfp(String customerID, String rfpNo) throws Exception {
//Add by Gxk 2004/09/10 Start
customerID = BaseCommonCheck.convertSql(customerID);
rfpNo = BaseCommonCheck.convertSql(rfpNo);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append("SELECT ");
sql.append(" CUSTOMER_ID , "); //屭媞ID
sql.append(" RFP_NO , "); //RFP斣崋
sql.append(" REP_RCV_DATE , "); //RFP庴庢擔
sql.append(" DUE_DATE , "); //擺婜
sql.append(" INFO_DUE_DATE , "); //忣曬擺婜
sql.append(" INFO_COUNTRY , "); //崙
sql.append(" INFO_HARD_CATEGORY, "); //僴乕僪僂僃傾暘椶
sql.append(" INFO_DELIVERY , "); //僨儕僶儕乕
sql.append(" QUANTITY , "); //悢検
sql.append(" REMARKS , "); //儕儅乕僋僗
sql.append(" COMMENT1 , "); //僐儊儞僩
sql.append(" NDA_DATE , "); //婡枾曐帩宊栺擔
sql.append(" RESULT , "); //寢壥
sql.append(" WON_REASON , "); //Won棟桼
sql.append(" LOST_REASON , "); //Lost棟桼
sql.append(" LOST_COMMENT , "); //Lost僐儊儞僩
sql.append(" LOST_WHOM , "); //Lost扤偵
sql.append(" RFP_STATUS , "); //僗僥乕僞僗(RFP)
sql.append(" RFP_USER_ID , "); //擖椡幰(RFP)
sql.append(" RFP_UPDATE_DATE , "); //峏怴擔帪(RFP)
sql.append(" RFP_SUBSIDIARY_CODE, "); //尰朄恖(RFP)
sql.append(" PAR_STATUS , "); //僗僥乕僞僗(Participate)
sql.append(" PAR_USER_ID , "); //擖椡幰(Participate)
sql.append(" PAR_UPDATE_DATE , "); //峏怴擔帪(Participate)
sql.append(" PAR_SUBSIDIARY_CODE, "); //尰朄恖(Participate)
sql.append(" NDA_STATUS , "); //僗僥乕僞僗(NDA)
sql.append(" NDA_USER_ID , "); //擖椡幰(NDA)
sql.append(" NDA_UPDATE_DATE , "); //峏怴擔帪(NDA)
sql.append(" NDA_SUBSIDIARY_CODE, "); //尰朄恖(NDA)
sql.append(" REP_STATUS , "); //僗僥乕僞僗(Reply)
sql.append(" REP_USER_ID , "); //擖椡幰(Reply)
sql.append(" REP_UPDATE_DATE , "); //峏怴擔帪(Reply)
sql.append(" REP_SUBSIDIARY_CODE, "); //尰朄恖(Reply)
sql.append(" RES_STATUS , "); //僗僥乕僞僗(Result)
sql.append(" RES_USER_ID , "); //擖椡幰(Result)
sql.append(" RES_UPDATE_DATE , "); //峏怴擔帪(Result)
sql.append(" RES_SUBSIDIARY_CODE, "); //尰朄恖(Result)
sql.append(" CREATE_USER , "); //嶌惉儐乕僓乕
sql.append(" CREATE_DATE , "); //嶌惉擔帪
sql.append(" UPDATE_USER , "); //峏怴儐乕僓乕
sql.append(" UPDATE_DATE "); //峏怴擔帪
sql.append("FROM ");
sql.append("T_RFP ");
sql.append("WHERE CUSTOMER_ID = '" + customerID + "' ");
sql.append(" AND RFP_NO = '" + rfpNo + "' ");
sql.append("ORDER BY ");
sql.append(" CUSTOMER_ID, RFP_NO ");
// END EDIT SQL
// DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
GBS_RFP_stBean out_st = new GBS_RFP_stBean();
// END
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
// //////////////////////////////////////////////////////////////////////////
// GET DATA FROM DB RESULTSET
while (rset.next()) {
out_st.setCustomerID(rset.getString("CUSTOMER_ID"));
out_st.setRfpNo(rset.getString("RFP_NO"));
out_st.setRepRcvDate(rset.getString("REP_RCV_DATE"));
out_st.setDueDate(rset.getString("DUE_DATE"));
out_st.setInfoDueDate(rset.getString("INFO_DUE_DATE"));
out_st.setInfoCountry(rset.getString("INFO_COUNTRY"));
out_st.setInfoHardCategory(rset.getString("INFO_HARD_CATEGORY"));
out_st.setInfoDelivery(rset.getString("INFO_LS_RT_OP"));
out_st.setQuantity(rset.getString("QUANTITY"));
out_st.setRemarks(rset.getString("REMARKS"));
out_st.setComment(rset.getString("COMMENT1"));
out_st.setNdaDate(rset.getString("NDA_DATE"));
out_st.setResult(rset.getString("RESULT"));
out_st.setWonReason(rset.getString("WON_REASON"));
out_st.setLostReason(rset.getString("LOST_REASON"));
out_st.setLostComment(rset.getString("LOST_COMMENT"));
out_st.setLostWhom(rset.getString("LOST_WHOM"));
out_st.setRfpStatus(rset.getString("RFP_STATUS"));
out_st.setRfpUserID(rset.getString("RFP_USER_ID"));
out_st.setRfpUpdateDate(rset.getString("RFP_UPDATE_DATE"));
out_st.setRfpSubsidiaryCode(rset.getString("RFP_SUBSIDIARY_CODE"));
out_st.setParStatus(rset.getString("PAR_STATUS"));
out_st.setParUpdateDate(rset.getString("PAR_UPDATE_DATE"));
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -