📄 gbs_trfp_db.java
字号:
/**
* method GBS_MCustomer_DB.java
* created on 08-11-2004
*
* @author GXK
* @version 1.0
*/
package DB;
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.Locale;
import javax.sql.DataSource;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_RFP_stBean;
import DataBean.GBS_SelectCustomer_stBean;
public class GBS_TRfp_DB implements SystemConstants {
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_TRfp_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* get rfp info by customerid
* @param customerId String
* @return ReturnValue
* @throws Exception
*/
public ReturnValue getRfpInfo(String customerId, int plusHour) throws Exception {
//Add by Gxk 2004/09/10 Start
customerId = BaseCommonCheck.convertSql(customerId);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
ReturnValue returnValue = new ReturnValue();
MessageList messageList = new MessageList();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append(" SELECT");
sql.append(" T_RFP.RFP_NO,");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME,");
sql.append(" M_MEMBER.MEMBER_NAME_FIRST,");
sql.append(" M_MEMBER.MEMBER_NAME_LAST,");
sql.append(" T_RFP.RESULT,");
sql.append(" WON.STRING1 AS WON_REASON,");
sql.append(" LOST.STRING1 AS LOST_REASON,");
sql.append(" TO_CHAR(T_RFP.REP_RCV_DATE,'DD-Mon-YYYY') REP_RCV_DATE,");
sql.append(" TO_CHAR(T_RFP.DUE_DATE,'DD-Mon-YYYY') DUE_DATE,");
sql.append(" TO_CHAR(T_RFP.NDA_DATE,'DD-Mon-YYYY') NDA_DATE,");
sql.append(" T_RFP.REP_UPDATE_DATE,");
sql.append(" T_RFP.RES_UPDATE_DATE");
sql.append(" FROM");
sql.append(" T_RFP, M_SUBSIDIARY, M_MEMBER,M_WIDE_USE WON,M_WIDE_USE LOST");
sql.append(" WHERE");
sql.append(" T_RFP.CUSTOMER_ID='" + customerId + "' AND");
sql.append(" T_RFP.RFP_USER_ID = M_MEMBER.USER_ID (+) AND");
sql.append(" T_RFP.RFP_SUBSIDIARY_CODE=M_SUBSIDIARY.SUBSIDIARY_CODE(+)");
sql.append(" AND T_RFP.WON_REASON = WON.MAIN_KEY(+)");
sql.append(" AND T_RFP.LOST_REASON = LOST.MAIN_KEY(+)");
sql.append(" ORDER BY");
sql.append(" RFP_UPDATE_DATE DESC,");
sql.append(" DUE_DATE DESC");
System.out.println(sql.toString());
//END EDIT SQL
/////////////////////////////////////////////////////////////////////////////
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
ArrayList returnList = new ArrayList();
while (rset.next()) {
GBS_SelectCustomer_stBean stBean = new GBS_SelectCustomer_stBean();
stBean.setRfpNo(rset.getString("RFP_NO"));
stBean.setSubsidiaryName(BaseCommonCheck.convertNullToSpace(rset.getString("SUBSIDIARY_NAME")));
stBean.setCustomerName(
BaseCommonCheck.convertNullToSpace(rset.getString("MEMBER_NAME_FIRST"))
+ " "
+ BaseCommonCheck.convertNullToSpace(rset.getString("MEMBER_NAME_LAST")));
if ("W".equals(rset.getString("RESULT"))) {
stBean.setResult("Won");
stBean.setWonReason(BaseCommonCheck.convertNullToSpace(rset.getString("WON_REASON")));
} else if ("L".equals(rset.getString("RESULT"))) {
stBean.setResult("Lost");
stBean.setWonReason(BaseCommonCheck.convertNullToSpace(rset.getString("LOST_REASON")));
}else{
stBean.setResult("Pending");
}
Calendar date = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
if (!BaseCommonCheck.isEmpty(rset.getString("REP_RCV_DATE"))) {
//rset.getTimestamp("REP_RCV_DATE", date);
//date.add(Calendar.HOUR, plusHour);
stBean.setRfpUpdateDate(rset.getString("REP_RCV_DATE"));
}
if (!BaseCommonCheck.isEmpty(rset.getString("NDA_DATE"))) {
//rset.getTimestamp("NDA_UPDATE_DATE", date);
//date.add(Calendar.HOUR, plusHour);
stBean.setNdaUpdateDate(rset.getString("NDA_DATE"));
}
if (!BaseCommonCheck.isEmpty(rset.getString("REP_UPDATE_DATE"))) {
rset.getTimestamp("REP_UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
stBean.setRepUpdateDate(format.format(date.getTime()));
}
if (!BaseCommonCheck.isEmpty(rset.getString("RES_UPDATE_DATE"))) {
rset.getTimestamp("RES_UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
stBean.setResUpdateDate(format.format(date.getTime()));
}
if (!BaseCommonCheck.isEmpty(rset.getString("DUE_DATE"))) {
stBean.setDueDate(rset.getString("DUE_DATE"));
}
returnList.add(stBean);
}
returnValue.setDataValue(returnList);
//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) {
}
if (messageList.size() > 0) {
returnValue.setMessageList(messageList);
returnValue.setBussinessError();
}
}
return returnValue;
}
public ReturnValue selectByCustomerIDRfpID(String CustomerID, String RfpID) throws Exception {
//Add by Gxk 2004/09/10 Start
CustomerID = BaseCommonCheck.convertSql(CustomerID);
RfpID = BaseCommonCheck.convertSql(RfpID);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT ");
sql.append(" CUSTOMER_ID, ");
sql.append(" RFP_NO,");
sql.append(" TO_CHAR(REP_RCV_DATE,'DD-Mon-YYYY') REP_RCV_DATE,");
sql.append(" TO_CHAR(DUE_DATE,'DD-Mon-YYYY') DUE_DATE,");
sql.append(" TO_CHAR(INFO_DUE_DATE,'DD-Mon-YYYY') INFO_DUE_DATE,");
sql.append(" INFO_COUNTRY,");
sql.append(" INFO_HARD_CATEGORY,");
sql.append(" INFO_LS_RT_OP,");
sql.append(" QUANTITY,");
sql.append(" REMARKS,");
sql.append(" COMMENT1,");
sql.append(" TO_CHAR(NDA_DATE,'DD-Mon-YYYY') NDA_DATE,");
sql.append(" RESULT,");
sql.append(" WON_REASON,");
sql.append(" LOST_REASON,");
sql.append(" LOST_COMMENT,");
sql.append(" LOST_WHOM,");
sql.append(" RFP_STATUS,");
sql.append(" RFP_USER_ID,");
sql.append(" TO_CHAR(RFP_UPDATE_DATE,'DD-Mon-YYYY') RFP_UPDATE_DATE,");
sql.append(" RFP_SUBSIDIARY_CODE,");
sql.append(" PAR_STATUS,");
sql.append(" TO_CHAR(PAR_UPDATE_DATE,'DD-Mon-YYYY') PAR_UPDATE_DATE,");
sql.append(" PAR_USER_ID,");
sql.append(" PAR_SUBSIDIARY_CODE,");
sql.append(" NDA_STATUS,");
sql.append(" NDA_USER_ID,");
sql.append(" TO_CHAR(NDA_UPDATE_DATE,'DD-Mon-YYYY') NDA_UPDATE_DATE,");
sql.append(" NDA_SUBSIDIARY_CODE,");
sql.append(" REP_STATUS,");
sql.append(" TO_CHAR(REP_UPDATE_DATE,'DD-Mon-YYYY') REP_UPDATE_DATE,");
sql.append(" REP_USER_ID,");
sql.append(" REP_SUBSIDIARY_CODE,");
sql.append(" RES_STATUS,");
sql.append(" RES_USER_ID,");
sql.append(" TO_CHAR(RES_UPDATE_DATE,'DD-Mon-YYYY') RES_UPDATE_DATE,");
sql.append(" RES_SUBSIDIARY_CODE,");
sql.append(" TO_CHAR(CREATE_DATE,'DD-Mon-YYYY') CREATE_DATE,");
sql.append(" CREATE_USER,");
sql.append(" TO_CHAR(UPDATE_DATE,'DD-Mon-YYYY') UPDATE_DATE,");
sql.append(" UPDATE_USER");
sql.append(" FROM ");
sql.append(" T_RFP ");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = '" + CustomerID + "' ");
sql.append(" AND RFP_NO = '" + RfpID + "' ");
System.out.println("[INFO] sql = " + sql.toString());
// 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"));
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
// //////////////////////////////////////////////////////////////////////////
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -