📄 gbs_tproductprice_db.java
字号:
bresult = pstmt.executeUpdate();
}
if ( bresult > 0 ){
conn.commit();
}else{
conn.rollback();
}
////////////////////////////////////////////////////////////////////////////
//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 (pstmt != null){
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
}
/**
* delete data from T_PRODUCT_PRICE
* @param GBS_TProductPrice_stBean in
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public void deleteByPK(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()));
//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.toString() );
sql1 = " DELETE FROM ";
sql1 += " T_PRODUCT_PRICE " ;
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.toString() );
//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 );
bresult = pstmt.executeUpdate();
}
if ( bresult > 0 ){
conn.commit();
}else{
conn.rollback();
}
////////////////////////////////////////////////////////////////////////////
//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 (pstmt != null){
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
}
/**
* select CPCPrice from T_PRODUCT_PRICE,M_PRODUCT,M_COUNTRY
* @param String productCategoryCode
* @param String countryCode
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public ReturnValue selectCPCPricelist(String productCategoryCode, String countryCode, int plusHour)
throws Exception {
String sql = new String();
ReturnValue ret = new ReturnValue();
MessageList messageList = new MessageList();
ret.setMessageList(messageList);
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " SELECT ";
sql += " M_PRODUCT.PRODUCT_ID, "; //惢昳儅僗僞.惢昳ID
sql += " M_PRODUCT.PRODUCT_NAME, "; //惢昳儅僗僞.惢昳柤
sql += " M_COUNTRY.COUNTRY_CODE, "; //崙儅僗僞.崙僐乕僪
sql += " M_COUNTRY.COUNTRY_NAME, "; //崙儅僗僞.崙柤
sql += " T_PRODUCT_PRICE.DATE_TIME, "; //惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
sql += " T_PRODUCT_PRICE.DATE_TIME AS DTIME, "; //惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
sql += " T_PRODUCT_PRICE.PRICE_CATEGORY, "; //惢昳扨壙僩儔儞僓僋僔儑儞.扨壙暘椶
sql += " T_PRODUCT_PRICE.LOCAL_CURRENCY AS LOCAL_CURRENCY_KEY,"; //惢昳扨壙僩儔儞僓僋僔儑儞.尰抧捠壿
sql += " M_WIDE_USE.STRING1 AS LOCAL_CURRENCY,"; //斈梡儅僗僞.撪梕暥帤侾
sql += " TO_CHAR(T_PRODUCT_PRICE.LOCAL_PRICE,'999,999,999.999999') AS LOCAL_PRICE, "; //CPC尰抧扨壙
sql += " TO_CHAR(T_PRODUCT_PRICE.US_PRICE,'999,999,999.999999') AS US_PRICE, "; //CPCUS$扨壙
sql += " TO_CHAR(T_PRODUCT_PRICE.EURO_PRICE,'999,999,999.999999') AS EURO_PRICE "; //CPCEuro扨壙
sql += " FROM ";
sql += " (SELECT T_PRODUCT_PRICE.* FROM ";
sql += " ("; // MAX 擔帪
sql += " SELECT ";
sql += " T_PRODUCT_PRICE.PRODUCT_ID ,"; // 惢昳扨壙僩儔儞僓僋僔儑儞.惢昳ID
sql += " T_PRODUCT_PRICE.COUNTRY_CODE ,"; // 惢昳扨壙僩儔儞僓僋僔儑儞.崙僐乕僪
sql += " T_PRODUCT_PRICE.PRICE_CATEGORY ,"; // 惢昳扨壙僩儔儞僓僋僔儑儞.扨壙暘椶
sql += " MAX(T_PRODUCT_PRICE.DATE_TIME) AS DATE_TIME"; // 惢昳扨壙僩儔儞僓僋僔儑儞.MAX 擔帪
sql += " FROM ";
sql += " T_PRODUCT_PRICE"; // 惢昳扨壙僩儔儞僓僋僔儑儞
sql += " WHERE ";
sql += " T_PRODUCT_PRICE.PRICE_CATEGORY IN ( 'C1' ,'C2' ,'C3','C4' ) "; //惢昳扨壙傢俠俹俠偱偡
sql += " AND T_PRODUCT_PRICE.COUNTRY_CODE IN (" + countryCode + ") ";
//惢昳扨壙僩儔儞僓僋僔儑儞.崙僐乕僪 IN 崙僐乕僪
sql += " GROUP BY "; // MAX 擔帪
sql += " T_PRODUCT_PRICE.PRODUCT_ID ,"; //惢昳扨壙僩儔儞僓僋僔儑儞.惢昳ID
sql += " T_PRODUCT_PRICE.COUNTRY_CODE ,"; // 惢昳扨壙僩儔儞僓僋僔儑儞.崙僐乕僪
sql += " T_PRODUCT_PRICE.PRICE_CATEGORY"; //惢昳扨壙僩儔儞僓僋僔儑儞.扨壙暘椶
sql += " ) MAXDATE , "; // MAX 擔帪
sql += " T_PRODUCT_PRICE "; //惢昳扨壙僩儔儞僓僋僔儑儞
sql += " WHERE ";
sql += " T_PRODUCT_PRICE.PRODUCT_ID = MAXDATE.PRODUCT_ID ";
sql += " AND T_PRODUCT_PRICE.COUNTRY_CODE = MAXDATE.COUNTRY_CODE ";
sql += " AND T_PRODUCT_PRICE.PRICE_CATEGORY = MAXDATE.PRICE_CATEGORY ";
sql += " AND T_PRODUCT_PRICE.DATE_TIME = MAXDATE.DATE_TIME ) ";
sql += " T_PRODUCT_PRICE, "; //惢昳扨壙僩儔儞僓僋僔儑儞
sql += " M_COUNTRY_PRODUCT_R, "; //崙惢昳娭學儅僗僞
sql += " M_PRODUCT, "; //惢昳儅僗僞
sql += " M_WIDE_USE, "; //斈梡儅僗僞
sql += " M_COUNTRY "; //崙儅僗僞
sql += " WHERE ";
sql += " M_PRODUCT.PRODUCT_CATEGORY IN ( " + productCategoryCode + " ) "; //惢昳儅僗僞.惢昳暘椶 IN 旐??揑惢昳暘椶
sql += " AND M_COUNTRY_PRODUCT_R.COUNTRY_CODE IN ( " + countryCode + " ) "; //崙儅僗僞.崙僐乕僪 IN 崙僐乕僪
sql += " AND ( M_PRODUCT.DELETE_FLG <> 'D' "; //惢昳儅僗僞.DELETE僼儔僌 亙亜丂'D'
sql += " OR M_PRODUCT.DELETE_FLG IS NULL ) "; //惢昳儅僗僞.DELETE僼儔僌 亙亜丂'D'
sql += " AND M_COUNTRY_PRODUCT_R.HANDLING = 'Y' "; //崙惢昳娭學儅僗僞(庢埖偄嬫暘=Y)
//sql += " AND M_PRODUCT.PRODUCT_ID = M_COUNTRY_PRODUCT_R.PRODUCT_ID "; //惢昳儅僗僞.惢昳ID = 崙惢昳娭學儅僗僞.惢昳ID
//sql += " AND M_COUNTRY_PRODUCT_R.COUNTRY_CODE = M_COUNTRY.COUNTRY_CODE(+) "; //崙惢昳娭學儅僗僞.崙僐乕僪 = 崙儅僗僞.崙僐乕僪
//sql += " AND M_PRODUCT.PRODUCT_ID = T_PRODUCT_PRICE.PRODUCT_ID (+) "; //惢昳儅僗僞.惢昳ID = 惢昳扨壙僩儔儞僓僋僔儑儞.惢昳ID
sql += " AND M_PRODUCT.PRODUCT_ID = M_COUNTRY_PRODUCT_R.PRODUCT_ID "; //惢昳儅僗僞.惢昳ID = 崙惢昳娭學儅僗僞.惢昳ID
sql += " AND M_COUNTRY_PRODUCT_R.COUNTRY_CODE(+) = M_COUNTRY.COUNTRY_CODE ";
//崙惢昳娭學儅僗僞.崙僐乕僪 = 崙儅僗僞.崙僐乕僪
sql += " AND M_COUNTRY_PRODUCT_R.PRODUCT_ID = T_PRODUCT_PRICE.PRODUCT_ID(+) ";
sql += " AND M_COUNTRY_PRODUCT_R.Country_Code = T_PRODUCT_PRICE.Country_Code(+) ";
sql += " AND T_PRODUCT_PRICE.LOCAL_CURRENCY = M_WIDE_USE.MAIN_KEY(+) ";
//惢昳扨壙僩儔儞僓僋僔儑儞.尰抧捠壿=斈梡儅僗僞.僉乕撪梕
sql += " AND M_WIDE_USE.TARGET_USE(+) = 'CURRENCY' "; //斈梡儅僗僞.巊梡栚揑='CURRENCY'
sql += " ORDER BY ";
sql += " M_PRODUCT.DISP_ORDER, M_PRODUCT.PRODUCT_ID, "; //惢昳儅僗僞.昞帵弴彉丄惢昳ID
sql += " M_COUNTRY.DISP_ORDER, M_COUNTRY.COUNTRY_CODE, "; //崙儅僗僞.昞帵弴彉丄崙僐乕僪
sql += " T_PRODUCT_PRICE.PRICE_CATEGORY "; //惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
//END EDIT SQL
//DEFINE RETURNVALUE
ArrayList outList = new ArrayList();
//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()) {
GBS_CPCPrice_stBean CPCPrice_st = new GBS_CPCPrice_stBean();
CPCPrice_st.setProductID(rset.getString("PRODUCT_ID") == null ? "" : rset.getString("PRODUCT_ID"));
CPCPrice_st.setProductName(
rset.getString("PRODUCT_NAME") == null ? "" : rset.getString("PRODUCT_NAME"));
CPCPrice_st.setCountryCode(
rset.getString("COUNTRY_CODE") == null ? "" : rset.getString("COUNTRY_CODE"));
CPCPrice_st.setCountryName(
rset.getString("COUNTRY_NAME") == null ? "" : rset.getString("COUNTRY_NAME"));
CPCPrice_st.setDateTime(rset.getString("DATE_TIME") == null ? "" : rset.getString("DATE_TIME"));
Calendar date = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
rset.getTimestamp("DTIME", date);
date.add(Calendar.HOUR, plusHour);
CPCPrice_st.setDate(format.format(date.getTime()));
CPCPrice_st.setPriceCategory(
rset.getString("PRICE_CATEGORY") == null ? "" : rset.getString("PRICE_CATEGORY"));
CPCPrice_st.setLocalCurrencyeKey(
rset.getString("LOCAL_CURRENCY_KEY") == null ? "" : rset.getString("LOCAL_CURRENCY_KEY"));
CPCPrice_st.setLocalCurrencye(
rset.getString("LOCAL_CURRENCY") == null ? "" : rset.getString("LOCAL_CURRENCY"));
if (rset.getString("LOCAL_PRICE") != null) {
if (rset.getString("LOCAL_PRICE").trim().startsWith(".")) {
CPCPrice_st.setLocalPrice("0" + rset.getString("LOCAL_PRICE").trim());
} else {
CPCPrice_st.setLocalPrice(rset.getString("LOCAL_PRICE"));
}
} else {
CPCPrice_st.setLocalPrice("");
}
if (rset.getString("US_PRICE") != null) {
if (rset.getString("US_PRICE").trim().startsWith(".")) {
CPCPrice_st.setUSPrice("0" + rset.getString("US_PRICE").trim());
} else {
CPCPrice_st.setUSPrice(rset.getString("US_PRICE"));
}
} else {
CPCPrice_st.setUSPrice("");
}
if (rset.getString("EURO_PRICE") != null) {
if (rset.getString("EURO_PRICE").trim().startsWith(".")) {
CPCPrice_st.setEUROPrice("0" + rset.getString("EURO_PRICE").trim());
} else {
CPCPrice_st.setEUROPrice(rset.getString("EURO_PRICE"));
}
} else {
CPCPrice_st.setEUROPrice("");
}
//update by YM 20040823 end
outList.add(CPCPrice_st);
}
if (outList.size() == 0) {
messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
ret.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 {
//CLOSE DB CONN
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
ret.setDataValue(outList);
return ret;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -