gbs_request_db.java

对日软件外包 为东芝做的一个全球商业管理系统

/** 
 * method GBS_MMember_DB.java
 * created on 07-29-2004
 * 
 * @author   GXK
 * @version  1.0
 */

package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import COMMON.BaseCommonCheck;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_Customer_stBean;
import DataBean.GBS_RFP_stBean;

public class GBS_Request_DB implements SystemConstants {

	private DataSource datasource = null;

	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_Request_DB(DataSource datasource) {
		this.datasource = datasource;
	}

	/**
	 * get GBS_MMember_st by userid
	 * @param		String UserId
	 * @return		GBS_MMember_stBean
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/29
	 */

	public ReturnValue selectByCustomerID(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 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	,	"); //	屭媞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 = '" + RfpID + "' ");
		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"));
				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;
	}
}