gbs_nda_db.java

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

package DB;

/** 
 * method GBS_NDA_DB.java
 * created on 08-09-2004
 * @author   xusheng
 * @version  1.0
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_Nda_stBean;

public class GBS_Nda_DB implements SystemConstants {
	private DataSource datasource = null;
	private ReturnValue returnValue = new ReturnValue();

	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_Nda_DB(DataSource datasource) {
	  this.datasource = datasource;
	}
	
	/**
	 * get RFP customer data form T_RFP,M_CUSTOMER
	 * @param		customerID,rfpNo
	 * @return		ReturnValue
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/08/09
	 */
	public ReturnValue selectByRFPNo(String customerId, int rfpNo) throws Exception {
		//Add by Gxk 2004/09/10 Start
		customerId = BaseCommonCheck.convertSql(customerId);
		//Add by Gxk 2004/09/10 End
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		//Edit SQL ===============(start)========================	 
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T_RFP.CUSTOMER_ID, "); //屭媞ID 
		sql.append("        M_CUSTOMER.CUSTOMER_NAME, "); //屭媞柤
		sql.append("        T_RFP.RFP_NO, "); //RFP斣崋
		sql.append("        TO_CHAR(T_RFP.NDA_DATE, 'DD-Mon-YYYY') NDA_DATE, "); //婡枾曐帩宊栺擔
		sql.append("        T_RFP.INFO_COUNTRY, "); //崙
		sql.append("        T_RFP.INFO_HARD_CATEGORY, "); //僴乕僪僂僃傾暘椶
		sql.append("        T_RFP.INFO_LS_RT_OP, "); //儕乕僗丒儗儞僞儖丒尰嬥
		sql.append("        T_RFP.QUANTITY, "); //悢検
		sql.append("        T_RFP.REMARKS, "); //儕儅乕僋僗
		sql.append("        T_RFP.COMMENT1, "); //僐儊儞
		sql.append("        T_RFP.RFP_SUBSIDIARY_CODE "); //
		sql.append("   FROM T_RFP, M_CUSTOMER ");
		sql.append("  WHERE T_RFP.CUSTOMER_ID = '" + customerId + "'  ");
		//Add by Gxk 2004/08/23 add deleteFlg
		sql.append(" AND (M_CUSTOMER.DELETE_FLG<>'D' OR M_CUSTOMER.DELETE_FLG IS NULL)");
		sql.append("    AND T_RFP.RFP_NO = '" + rfpNo + "'  ");
		sql.append("    AND T_RFP.CUSTOMER_ID = M_CUSTOMER.CUSTOMER_ID(+) ");
		sql.append("  ORDER BY CUSTOMER_ID ");
		//edit SQL
		System.out.println("[INFO] sql = " + sql.toString());
		//Edit SQL ===============(end)========================	 

		//define rerurnvalue 
		GBS_Nda_stBean out_st = new GBS_Nda_stBean();
		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 T_RFP,M_CUSTOMER
			int hitCount = 0;
			while (rset.next()) {
				out_st.setCustomerId(rset.getString("CUSTOMER_ID"));
				out_st.setCustomerName(rset.getString("CUSTOMER_NAME"));
				out_st.setRfpNo(rset.getInt("RFP_NO"));
				out_st.setNdaDate(rset.getString("NDA_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.setRfpSubsidiaryCode(rset.getString("RFP_SUBSIDIARY_CODE"));
				hitCount++;
			}
			if (hitCount == 0) {
				//set error information.
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValue.setBussinessError();
			} else {
				returnValue.setDataValue(out_st);
			}
		} 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) {
				throw se;
			}
		}
		return returnValue;
	}

	/**
	 * update T_RFP by CUSTOMER_ID
	 * @param		GBS_NDA_stBean in_st,
	 * @param       String  customerID,
	 * @param  	int rfpNo
	 * @return		ReturnValue
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/08/09
	 */
	public ReturnValue updateByCustomerId(GBS_Nda_stBean in_st) throws Exception {
		//Add by Gxk 2004/09/10 Start
		in_st.setCustomerId(BaseCommonCheck.convertSql(in_st.getCustomerId()));
		in_st.setCustomerName(BaseCommonCheck.convertSql(in_st.getCustomerName()));
		in_st.setNdaDate(BaseCommonCheck.convertSql(in_st.getNdaDate()));
		in_st.setInfoCountry(BaseCommonCheck.convertSql(in_st.getInfoCountry()));
		in_st.setInfoHardCategory(BaseCommonCheck.convertSql(in_st.getInfoHardCategory()));
		in_st.setQuantity(BaseCommonCheck.convertSql(in_st.getQuantity()));
		in_st.setInfoDelivery(BaseCommonCheck.convertSql(in_st.getInfoDelivery()));
		in_st.setRemarks(BaseCommonCheck.convertSql(in_st.getRemarks()));
		in_st.setComment(BaseCommonCheck.convertSql(in_st.getComment()));
		in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
		in_st.setRepUpdateDate(BaseCommonCheck.convertSql(in_st.getRepUpdateDate()));
		in_st.setNdaStatus(BaseCommonCheck.convertSql(in_st.getNdaStatus()));
		in_st.setNdaSubsidiaryCode(BaseCommonCheck.convertSql(in_st.getNdaSubsidiaryCode()));
		in_st.setNdaUserId(BaseCommonCheck.convertSql(in_st.getNdaUserId()));
		in_st.setNdaUpdateDate(BaseCommonCheck.convertSql(in_st.getNdaUpdateDate()));
		//Add by Gxk 2004/09/10 End
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		StringBuffer sql1 = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();

		//start edit sql 1 ==========(start)================
		sql1.append(" SELECT ");
		sql1.append("        CUSTOMER_ID, ");
		sql1.append("        RFP_NO ");
		sql1.append("   FROM T_RFP ");
		sql1.append("  WHERE  ");
		sql1.append("        CUSTOMER_ID = '" + in_st.getCustomerId() + "' ");
		sql1.append("    AND RFP_NO = '" + in_st.getRfpNo() + "' ");
		sql1.append("    FOR UPDATE ");
		System.out.println("[INFO] sql = " + sql1.toString());
		//end edit sql 1 =============(end)==============

		//start edit sql 2 ============(start)=============
		sql2.append(" UPDATE T_RFP SET ");
		sql2.append("        NDA_DATE = to_date('" + in_st.getNdaDate() + "','yyyy/MM/dd'), "); //夋柺丏婡枾曐帩宊栺擔
		sql2.append("        UPDATE_USER = '" + in_st.getUpdateUser() + "', "); //Login儐乕僓乕
		sql2.append("        UPDATE_DATE = SYSDATE, "); //DB僒乕僶乕偺SYSDATE
		sql2.append("        NDA_STATUS = '" + in_st.getNdaStatus() + "', "); //"1"
		sql2.append("        NDA_SUBSIDIARY_CODE = '" + in_st.getNdaSubsidiaryCode() + "', "); //儘僌僀儞儐乕僓乕偺尰朄僐乕僪
		sql2.append("        NDA_USER_ID= '" + in_st.getNdaUserId() + "', "); //Login儐乕僓乕
		sql2.append("        NDA_UPDATE_DATE = SYSDATE "); //DB僒乕僶乕偺SYSDATE
		sql2.append("  WHERE  ");
		sql2.append("        CUSTOMER_ID = '" + in_st.getCustomerId() + "' ");
		sql2.append("    AND RFP_NO = '" + in_st.getRfpNo() + "' ");
		System.out.println("[INFO] sql = " + sql2.toString());
		//end edit sql 2 =============(end)===============

		//define returnValue
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql1.toString());
			boolean isExist = false;
			while (rset.next()) {
				isExist = true;
			}
			if (isExist) {
				pstmt = conn.prepareStatement(sql2.toString());
				int countUpdate = pstmt.executeUpdate();
				if (countUpdate > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
			} else {
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValue.setBussinessError();
			}
			//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 {
			//clost DB conn
			try {
				if (rset != null) {
					rset.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				throw se;
			}
		}
		return returnValue;
	}
}