📄 gbs_tproductprice_db.java
字号:
}
/**
* select edit CPC price 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 selectEditCPCPrice(String countryCode) throws Exception {
//add by YM 2004/09/10 start
countryCode = BaseCommonCheck.convertSql(countryCode);
//add by YM 2004/09/10 end
String sql = new String();
ReturnValue ret = new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " SELECT ";
sql += " M_PRODUCT.PRODUCT_ID, "; //惢昳儅僗僞.惢昳ID
sql += " M_PRODUCT.PRODUCT_NAME, "; //惢昳儅僗僞.惢昳柤
sql += " TO_CHAR(T_PRODUCT_PRICE.RATE_US,'999,999,999.999999') AS RATE_US, "; //惢昳扨壙僩儔儞僓僋僔儑儞.愝掕帪偺儗乕僩乮懳US)
sql += " TO_CHAR(T_PRODUCT_PRICE.RATE_EURO,'999,999,999.999999') AS RATE_EURO, ";
//惢昳扨壙僩儔儞僓僋僔儑儞.愝掕帪偺儗乕僩乮懳EURO)
sql += " T_PRODUCT_PRICE.PRICE_CATEGORY, "; //惢昳扨壙僩儔儞僓僋僔儑儞.扨壙暘椶
sql += " T_PRODUCT_PRICE.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 += " ("; // 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 = 'C1' "; //惢昳扨壙傢CPC偱偡
sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY = 'C2' "; //惢昳扨壙傢CPC偱偡
sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY = 'C3' "; //惢昳扨壙傢CPC偱偡
sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY = 'C4' "; //惢昳扨壙傢CPC偱偡
sql += " AND T_PRODUCT_PRICE.COUNTRY_CODE = '" + countryCode + "' "; //惢昳扨壙僩儔儞僓僋僔儑儞.崙僐乕僪 = 崙僐乕僪
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 += " M_COUNTRY_PRODUCT_R, "; //崙惢昳娭學儅僗僞
sql += " T_PRODUCT_PRICE, "; //惢昳扨壙僩儔儞僓僋僔儑儞
sql += " M_PRODUCT, "; //惢昳儅僗僞
sql += " M_COUNTRY "; //崙儅僗僞
sql += " WHERE ";
sql += " M_COUNTRY.COUNTRY_CODE = '" + 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)
//惢昳儅僗僞.惢昳ID = 崙惢昳娭學儅僗僞.惢昳ID
sql += " AND M_PRODUCT.PRODUCT_ID = M_COUNTRY_PRODUCT_R.PRODUCT_ID ";
//崙惢昳娭學儅僗僞.崙僐乕僪= 崙儅僗僞.崙僐乕僪
sql += " AND M_COUNTRY_PRODUCT_R.COUNTRY_CODE(+) = M_COUNTRY.COUNTRY_CODE ";
//崙惢昳娭學儅僗僞.惢昳ID = MAX 擔帪.惢昳ID
sql += " AND M_COUNTRY_PRODUCT_R.PRODUCT_ID = MAXDATE.PRODUCT_ID(+) ";
sql += " AND M_COUNTRY_PRODUCT_R.Country_Code = MAXDATE.Country_Code(+) ";
sql += " AND MAXDATE.PRODUCT_ID = T_PRODUCT_PRICE.PRODUCT_ID (+) ";
//MAX 擔帪.惢昳ID = 惢昳扨壙僩儔儞僓僋僔儑儞.惢昳ID
sql += " AND MAXDATE.COUNTRY_CODE = T_PRODUCT_PRICE.COUNTRY_CODE (+)";
//MAX 擔帪.崙僐乕僪 = 惢昳扨壙僩儔儞僓僋僔儑儞.崙僐乕僪
sql += " AND MAXDATE.PRICE_CATEGORY = T_PRODUCT_PRICE.PRICE_CATEGORY(+)";
//MAX 擔帪.扨壙暘椶 = 惢昳扨壙僩儔儞僓僋僔儑儞.扨壙暘椶
sql += " AND MAXDATE.DATE_TIME = T_PRODUCT_PRICE.DATE_TIME (+) ";
//MAX 擔帪.擔帪 = 惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
//sql += " AND ( T_PRODUCT_PRICE.PRICE_CATEGORY = 'C1' "; //惢昳扨壙傢CPC偱偡
//sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY = 'C2' "; //惢昳扨壙傢CPC偱偡
//sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY = 'C3' "; //惢昳扨壙傢CPC偱偡
//sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY = 'C4' "; //惢昳扨壙傢CPC偱偡
//sql += " OR T_PRODUCT_PRICE.PRICE_CATEGORY IS NULL )"; //惢昳扨壙傢CPC偱偡
sql += " ORDER BY ";
sql += " M_PRODUCT.PRODUCT_ID, "; //惢昳儅僗僞.惢昳ID
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"));
//Edit by Gxk 2004/09/09 Start
//CPCPrice_st.setRateUS(rset.getString("RATE_US")==null?"":rset.getString("RATE_US"));
if (rset.getString("RATE_US") != null) {
if (rset.getString("RATE_US").trim().startsWith(".")) {
CPCPrice_st.setRateUS("0" + rset.getString("RATE_US").trim());
} else {
CPCPrice_st.setRateUS(rset.getString("RATE_US").trim());
}
} else {
CPCPrice_st.setRateUS("");
}
//CPCPrice_st.setRateEuro(rset.getString("RATE_EURO")==null?"":rset.getString("RATE_EURO"));
if (rset.getString("RATE_EURO") != null) {
if (rset.getString("RATE_EURO").trim().startsWith(".")) {
CPCPrice_st.setRateEuro("0" + rset.getString("RATE_EURO").trim());
} else {
CPCPrice_st.setRateEuro(rset.getString("RATE_EURO").trim());
}
} else {
CPCPrice_st.setRateEuro("");
}
//Edit by Gxk 2004/09/09 End
CPCPrice_st.setPriceCategory(
rset.getString("PRICE_CATEGORY") == null ? "" : rset.getString("PRICE_CATEGORY"));
CPCPrice_st.setLocalCurrencye(
rset.getString("LOCAL_CURRENCY") == null ? "" : rset.getString("LOCAL_CURRENCY"));
//update by YM 20040823 start
//CPCPrice_st.setLocalPrice(rset.getString("LOCAL_PRICE")==null?"":rset.getString("LOCAL_PRICE"));
//CPCPrice_st.setUSPrice(rset.getString("US_PRICE")==null?"":rset.getString("US_PRICE"));
//CPCPrice_st.setEUROPrice(rset.getString("EURO_PRICE")==null?"":rset.getString("EURO_PRICE"));
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").trim());
}
} 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").trim());
}
} 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").trim());
}
} else {
CPCPrice_st.setEUROPrice("");
}
//update by YM 20040823 end
outList.add(CPCPrice_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) {
}
}
ret.setDataValue(outList);
return ret;
}
/**
* update or 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 ReturnValue insert(String userID,ArrayList inList)throws Exception{
//add by YM 2004/09/10 start
userID = BaseCommonCheck.convertSql(userID);
for(int i=0;i<inList.size();i++){
GBS_TProductPrice_stBean in =(GBS_TProductPrice_stBean) inList.get(i);
in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
in.setCreateDate(BaseCommonCheck.convertSql(in.getCreateDate()));
in.setCreateUser(BaseCommonCheck.convertSql(in.getCreateUser()));
in.setDateTime(BaseCommonCheck.convertSql(in.getDateTime()));
in.setEUROPrice(BaseCommonCheck.convertSql(in.getEUROPrice()));
in.setLocalCurregcy(BaseCommonCheck.convertSql(in.getLocalCurregcy()));
in.setLocalPrice(BaseCommonCheck.convertSql(in.getLocalPrice()));
in.setRateEURO(BaseCommonCheck.convertSql(in.getRateEURO()));
in.setRateUS(BaseCommonCheck.convertSql(in.getRateUS()));
in.setUpdateDate(BaseCommonCheck.convertSql(in.getUpdateDate()));
in.setUpdateUser(BaseCommonCheck.convertSql(in.getUpdateUser()));
in.setUSPrice(BaseCommonCheck.convertSql(in.getUSPrice()));
in.setUserID(BaseCommonCheck.convertSql(in.getUserID()));
userID = BaseCommonCheck.convertSql(userID);
inList.set(i,in);
}
//add by YM 2004/09/10 end
ReturnValue returnValue =new ReturnValue();
MessageList messageList = new MessageList();
returnValue.setMessageList( messageList );
ResultSet rset = null;
Connection conn = null;
Statement st = null;
boolean bRet = false;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
for(int i=0; i < inList.size(); i++ ){
GBS_TProductPrice_stBean in =(GBS_TProductPrice_stBean) inList.get(i);
String sql = new String();
/////////////////////////////////////////////////////////////////////////////
//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 += "SYSDATE,'";
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() );
bRet = st.execute( sql );
if ( bRet ){
messageList.setMessage( "", "", "90000013",Integer.MIN_VALUE);
returnValue.setBussinessError();
break;
}
}
////////////////////////////////////////////////////////////////////////////
//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 {
//Commit data
if ( !bRet ){
conn.commit() ;
}else{
conn.rollback() ;
}
//CLOSE DB CONN
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValue;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -