gbs_tproductprice_db.java

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

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_CPCPrice_stBean;
import DataBean.GBS_HardwarePrice_stBean;
import DataBean.GBS_TProductPrice_stBean;
/**
 * public class GBS_TProductPrice_DB implements SystemConstants
 * created on 07-30-2004
 * @author   wxf
 * @version  1.0
 */

public class GBS_TProductPrice_DB implements SystemConstants{
	private DataSource datasource = null;
	

	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_TProductPrice_DB(DataSource datasource) {
	  this.datasource = datasource;
	}
	/**
	 * selectOneByMaxDate  
	 * @param GBS_TProductPrice_stBean in
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public ReturnValue selectOneByMaxDate(GBS_TProductPrice_stBean in)throws Exception{
		//Add by YM 2004/09/10 Start
		in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
		in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
		in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
		//Add by YM 2004/09/10 End
		String sql = new String();
		ReturnValue ret =new ReturnValue();
		
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
		sql  = " SELECT  ";  
		sql += "    PRODUCT_ID ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. 惢昳ID
		sql += "    COUNTRY_CODE ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 崙僐乕僪
		sql += "    PRICE_CATEGORY ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 扨壙暘椶
		sql += "    DATE_TIME ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. 擔帪
		sql += "    LOCAL_CURRENCY ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 尰抧捠壿
		sql += "    LOCAL_PRICE ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 尰抧扨壙
		sql += "    US_PRICE ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. US$扨壙
		sql += "    EURO_PRICE ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. Euro扨壙
		sql += "    RATE_US ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. 愝掕帪偺儗乕僩乮懳US)
		sql += "    RATE_EURO ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. 愝掕帪偺儗乕僩乮懳EURO)
		sql += "    USER_ID ,"; 		//惢昳扨壙僩儔儞僓僋僔儑儞. 儐乕僓乕ID
		sql += "    CREATE_USER ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 嶌惉儐乕僓乕
		sql += "    CREATE_DATE ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 嶌惉擔帪
		sql += "    UPDATE_USER ,"; 	//惢昳扨壙僩儔儞僓僋僔儑儞. 峏怴儐乕僓乕
		sql += "    UPDATE_DATE "; 		//惢昳扨壙僩儔儞僓僋僔儑儞. 峏怴擔帪
		sql += " FROM ";        
		sql += "      T_PRODUCT_PRICE ";//惢昳扨壙僩儔儞僓僋僔儑儞
		sql += " WHERE ";       
		sql += "       T_PRODUCT_PRICE.PRODUCT_ID     = '"+ in.getProductID()+"'    ";//惢昳ID   = in.惢昳ID
		sql += "  AND  T_PRODUCT_PRICE.COUNTRY_CODE   = '"+ in.getCountryCode()+"'  ";//崙僐乕僪 = in.ID崙僐乕僪 
		sql += "  AND  T_PRODUCT_PRICE.PRICE_CATEGORY = '"+ in.getPriceCategory()+"'";//扨壙暘椶 = in.扨壙暘椶
		sql += "  ORDER BY  ";
		sql += "       T_PRODUCT_PRICE.DATE_TIME  DESC ";//擔帪     
//END EDIT SQL		
//DEFINE RETURNVALUE
		GBS_TProductPrice_stBean 	tProductPrice_st = new GBS_TProductPrice_stBean();
//END
/////////////////////////////////////////////////////////////////////////////
  		System.out.println( "[INFO] sql = " + sql.toString() );
		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()) {
			tProductPrice_st.setProductID(rset.getString("PRODUCT_ID")==null?"":rset.getString("PRODUCT_ID"));	
			tProductPrice_st.setCountryCode(rset.getString("COUNTRY_CODE")==null?"":rset.getString("COUNTRY_CODE"));
			tProductPrice_st.setPriceCategory(rset.getString("PRICE_CATEGORY")==null?"":rset.getString("PRICE_CATEGORY"));
			tProductPrice_st.setDateTime(rset.getString("DATE_TIME")==null?"":rset.getString("DATE_TIME"));
			tProductPrice_st.setLocalCurregcy(rset.getString("LOCAL_CURRENCY")==null?"":rset.getString("LOCAL_CURRENCY"));
			tProductPrice_st.setLocalPrice(rset.getString("LOCAL_PRICE")==null?"":rset.getString("LOCAL_PRICE"));	
			tProductPrice_st.setUSPrice(rset.getString("US_PRICE")==null?"":rset.getString("US_PRICE"));	
			tProductPrice_st.setEUROPrice(rset.getString("EURO_PRICE")==null?"":rset.getString("EURO_PRICE"));	
			tProductPrice_st.setRateUS(rset.getString("RATE_US")==null?"":rset.getString("RATE_US"));	
			tProductPrice_st.setRateEURO(rset.getString("RATE_EURO")==null?"":rset.getString("RATE_EURO"));
			tProductPrice_st.setUserID(rset.getString("USER_ID")==null?"":rset.getString("USER_ID"));
			tProductPrice_st.setCreateUser(rset.getString("CREATE_USER")==null?"":rset.getString("CREATE_USER"));
			tProductPrice_st.setCreateDate(rset.getString("CREATE_DATE")==null?"":rset.getString("CREATE_DATE"));
			tProductPrice_st.setUpdateUser(rset.getString("UPDATE_USER")==null?"":rset.getString("UPDATE_USER"));
			tProductPrice_st.setUpdateDate(rset.getString("UPDATE_DATE")==null?"":rset.getString("UPDATE_DATE"));	
			break;
		  }
//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) {
			  }
		}
		ret.setDataValue(tProductPrice_st);
		return ret;
	 }	 
	/**
	 * insert data to T_PRODUCT_PRICE
	 * @param      String userID
	 * @param		GBS_TProductPrice_stBean in
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public void insertByPK(String userID,GBS_TProductPrice_stBean in)throws Exception{
		//Add by YM 2004/09/10 Start
		in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
		in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
		in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
		in.setDateTime(BaseCommonCheck.convertSql(in.getDateTime()));
		in.setLocalCurregcy(BaseCommonCheck.convertSql(in.getPriceCategory()));
		in.setLocalPrice(BaseCommonCheck.convertSql(in.getLocalPrice()));
		in.setUSPrice(BaseCommonCheck.convertSql(in.getUSPrice()));
		in.setEUROPrice(BaseCommonCheck.convertSql(in.getEUROPrice()));
		in.setRateEURO(BaseCommonCheck.convertSql(in.getRateEURO()));
		in.setRateUS(BaseCommonCheck.convertSql(in.getRateUS()));
		in.setUserID(BaseCommonCheck.convertSql(in.getUserID()));
		userID = BaseCommonCheck.convertSql(userID);
		//Add by YM 2004/09/10 End
		String sql = new String();
		ReturnValue ret =new ReturnValue();
		
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
		  sql  = " INSERT INTO ";
		  sql += "   T_PRODUCT_PRICE (" ;
		  sql += "   PRODUCT_ID  ,";
		  sql += "   COUNTRY_CODE ,";
		  sql += "   PRICE_CATEGORY ,";
		  sql += "   DATE_TIME  ,";
		  sql += "   LOCAL_CURRENCY ,";
		  sql += "   LOCAL_PRICE ,";
		  sql += "   US_PRICE  ,";    
		  sql += "   EURO_PRICE  ,";
		  sql += "   RATE_US  ,";
		  sql += "   RATE_EURO  ,";
		  sql += "   USER_ID  ,";
		  sql += "   CREATE_USER ,";
		  sql += "   CREATE_DATE )";
		  sql += "   VALUES (  '";
		  sql += in.getProductID() +"','";
		  sql += in.getCountryCode() +"','";
		  sql += in.getPriceCategory()+"','";
		  sql += in.getDateTime()  +"','";
		  sql += in.getLocalCurregcy()+"','";
		  sql += in.getLocalPrice() +"','";
		  sql += in.getUSPrice()  +"','";
		  sql += in.getEUROPrice() +"','";
		  sql += in.getRateUS()  +"','";
		  sql += in.getRateEURO()  +"','";
		  sql += in.getUserID()  +"','";
		  sql += userID    +"',";
		  sql += "SYSDATE";
		  sql += " )";
	

//END EDIT SQL		
//DEFINE RETURNVALUE
		
//END
/////////////////////////////////////////////////////////////////////////////
  		System.out.println( "[INFO] sql = " + sql.toString() );
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			conn.commit();
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET

//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) {
			  }
		}
	 }
	/**
	 * update data to T_PRODUCT_PRICE
	 * @param      String userID
	 * @param		GBS_TProductPrice_stBean in
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public void updateByPK(String userID,GBS_TProductPrice_stBean in)throws Exception{
		//Add by YM 2004/09/10 Start
		in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
		in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
		in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
		in.setDateTime(BaseCommonCheck.convertSql(in.getDateTime()));
		in.setLocalCurregcy(BaseCommonCheck.convertSql(in.getPriceCategory()));
		in.setLocalPrice(BaseCommonCheck.convertSql(in.getLocalPrice()));
		in.setUSPrice(BaseCommonCheck.convertSql(in.getUSPrice()));
		in.setEUROPrice(BaseCommonCheck.convertSql(in.getEUROPrice()));
		in.setRateEURO(BaseCommonCheck.convertSql(in.getRateEURO()));
		in.setRateUS(BaseCommonCheck.convertSql(in.getRateUS()));
		in.setUserID(BaseCommonCheck.convertSql(in.getUserID()));
		userID = BaseCommonCheck.convertSql(userID);
		//Add by YM 2004/09/10 End
		String sql = new String();
		String sql1 = new String();
		ReturnValue ret =new ReturnValue();
		
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
  		sql  = " SELECT ";
		sql += " 		PRODUCT_ID";
		sql += " FROM ";
		sql += " 	   	T_PRODUCT_PRICE ";
		sql += " WHERE " ;
		sql += " 	   	PRODUCT_ID  ='"+in.getProductID() +"'";
		sql += " AND 	COUNTRY_CODE  ='"+in.getCountryCode() +"'";
		sql += " AND 	PRICE_CATEGORY ='"+in.getPriceCategory()+"'";
		sql += " AND 	DATE_TIME   ='"+in.getDateTime() +"'";	
		sql += " FOR 	UPDATE ";
		  
		System.out.println( "[INFO] sql = " + sql ); 
		  
		sql1  = " UPDATE  ";
		sql1 += "     	T_PRODUCT_PRICE " ;
		sql1 += " SET " ;
		sql1 += "     	LOCAL_CURRENCY  = '"+in.getLocalCurregcy() +"',";
		sql1 += "     	LOCAL_PRICE  = '"+in.getLocalPrice() +"',";
		sql1 += "     	US_PRICE   = '"+in.getUSPrice()  +"',";   
		sql1 += "     	EURO_PRICE   = '"+in.getEUROPrice()  +"',";
		sql1 += "     	RATE_US   = '"+in.getRateUS()  +"',";
		sql1 += "     	RATE_EURO   = '"+in.getRateEURO()  +"',";
		sql1 += "     	USER_ID   = '"+in.getUserID()  +"',";
		sql1 += "     	UPDATE_USER  = '"+userID    +"',";
		sql1 += "     	UPDATE_DATE  = SYSDATE ";
		sql1 += " WHERE " ;
		sql1 += " 	   	PRODUCT_ID  ='"+in.getProductID() +"'";
		sql1 += " AND 	COUNTRY_CODE  ='"+in.getCountryCode() +"'";
		sql1 += " AND 	PRICE_CATEGORY ='"+in.getPriceCategory()+"'";
		sql1 += " AND 	DATE_TIME   ='"+in.getDateTime() +"'";

		System.out.println( "[INFO] sql = " + sql1 ); 

//END EDIT SQL		
//DEFINE RETURNVALUE
		
//END
/////////////////////////////////////////////////////////////////////////////
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		int bresult = 0;
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			if ( rset.next() ){
				bresult = 1;
			}
			if ( bresult > 0 ){
				pstmt = conn.prepareStatement( sql1 );