gbs_result_db.java

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

package DB;

/** 
 * method GBS_Result_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_Result_stBean;

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

	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_Result_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 );
	
		StringBuffer sql = new StringBuffer();
		 
		//edit SQL============(start)================
		sql.append(" SELECT ");
		sql.append("        T_RFP.CUSTOMER_ID, ");				//屭媞ID
		sql.append("        M_CUSTOMER.CUSTOMER_NAME, ");		//屭媞柤
		sql.append("        T_RFP.RFP_NO, ");					//RFP斣崋
		sql.append("        T_RFP.RESULT, ");					//寢壥
		sql.append("        T_RFP.WON_REASON, ");				//Won棟桼
		sql.append("        T_RFP.LOST_REASON, ");				//Lost棟桼
		sql.append("        T_RFP.LOST_COMMENT, ");				//Lost僐儊儞僩
		sql.append("        T_RFP.LOST_WHOM ");					//Lost扤偵
		sql.append("   FROM ");
		sql.append("        T_RFP, ");
		sql.append("        M_CUSTOMER ");
		sql.append("  WHERE ");
		//Add by Gxk 2004/08/23 add deleteFlg
		sql.append(" (M_CUSTOMER.DELETE_FLG<>'D' OR M_CUSTOMER.DELETE_FLG IS NULL) AND");
		sql.append("        T_RFP.CUSTOMER_ID = '" + customerId + "' ");
		sql.append("    AND T_RFP.RFP_NO = '" + rfpNo + "' ");
		sql.append("    AND T_RFP.CUSTOMER_ID = M_CUSTOMER.CUSTOMER_ID(+) ");
	
		System.out.println("[INFO] sql = " + sql.toString() );	
		//end edit SQL===========(end)================
			
		//define rerurnvalue 
		GBS_Result_stBean out_st = new GBS_Result_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.getString("RFP_NO"));
			 	 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.setWonComment( "" );
				 if( out_st.getResult() != null && out_st.getResult().equals( "W" ) ){
				 	out_st.setWonComment( out_st.getLostComment() );
				 	out_st.setLostComment( "" );  
				 }
				 hitCount++;
			}
			 //end get data
			 if ( hitCount==0 ) {
				 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_Result_stBean in_st,
	 * @param	    String  customerID,
	 * @param		int rfpNo
	 * @return		boolean
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/08/09
	 */

	public ReturnValue updateByCustomerId(GBS_Result_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.setResult(BaseCommonCheck.convertSql(in_st.getResult()));
		in_st.setWonReason(BaseCommonCheck.convertSql(in_st.getWonReason()));
		in_st.setWonComment(BaseCommonCheck.convertSql(in_st.getWonComment()));
		in_st.setLostReason(BaseCommonCheck.convertSql(in_st.getLostReason()));
		in_st.setLostComment(BaseCommonCheck.convertSql(in_st.getLostComment()));
		in_st.setLostWhom(BaseCommonCheck.convertSql(in_st.getLostWhom()));
		in_st.setCreateUser(BaseCommonCheck.convertSql(in_st.getCreateUser()));
		in_st.setCreateDate(BaseCommonCheck.convertSql(in_st.getCreateDate()));
		in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
		in_st.setUpdateDate(BaseCommonCheck.convertSql(in_st.getUpdateDate()));
		//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();

		//deit SQL
		//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("        RESULT      = '" + in_st.getResult() + "', "); //夋柺丏寢壥
		sql2.append("        WON_REASON  = '" + in_st.getWonReason() + "', "); //夋柺丏Won棟桼
		sql2.append("        LOST_REASON = '" + in_st.getLostReason() + "', "); //夋柺丏Lost棟桼
		sql2.append("        LOST_COMMENT= '" + in_st.getLostComment() + "', "); //夋柺丏寢壥 = L  ? 夋柺丏Lost僐儊儞僩:夋柺丏Won僐儊儞僩
		sql2.append("        LOST_WHOM   = '" + in_st.getLostWhom() + "', "); //夋柺丏Lost扤偵
		sql2.append("        RES_STATUS  = '1', "); //僗僥乕僞僗(Result) = 1
		sql2.append("        RES_USER_ID = '" + in_st.getUpdateUser() + "', "); //Login儐乕僓乕
		sql2.append("        RES_UPDATE_DATE = SYSDATE, "); //DB僒乕僶乕偺SYSDATE
		sql2.append("        UPDATE_USER = '" + in_st.getUpdateUser() + "', "); //Login儐乕僓乕
		sql2.append("        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;
	}
}