📄 gbs_tproductprice_db.java
字号:
}
/**
* select HardwarePrice 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 selectHardwarePricelist(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_PRODUCT.KIND, ";//惢昳儅僗僞.庬椶
sql += " M_PRODUCT.PRODUCT_CATEGORY, ";//惢昳儅僗僞.惢昳暘椶
sql += " M_COUNTRY_PRODUCT_R.COUNTRY_CODE,";//崙惢昳娭學儅僗僞.崙僐乕僪
sql += " M_COUNTRY.COUNTRY_NAME, ";//崙儅僗僞.崙柤
sql += " T_PRODUCT_PRICE.DATE_TIME, ";//惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
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.99') AS LOCAL_PRICE, ";//CPC尰抧扨壙
sql += " TO_CHAR(T_PRODUCT_PRICE.US_PRICE,'999,999,999.99') AS US_PRICE, ";//CPCUS$扨壙
sql += " TO_CHAR(T_PRODUCT_PRICE.EURO_PRICE,'999,999,999.99') AS EURO_PRICE ";//CPCEuro扨壙
sql += " FROM ";
sql += " (";// MAX 擔帪
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 = 'M' ";//惢昳扨壙傢product偱偡
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 ,T_PRODUCT_PRICE ";// MAX 擔帪
sql += " WHERE ";
sql += " MAXDATE.PRODUCT_ID = T_PRODUCT_PRICE.PRODUCT_ID(+) ";
sql += " AND MAXDATE.COUNTRY_CODE = T_PRODUCT_PRICE.COUNTRY_CODE(+) ";
sql += " AND MAXDATE.PRICE_CATEGORY = T_PRODUCT_PRICE.PRICE_CATEGORY(+) ";
sql += " AND MAXDATE.DATE_TIME = T_PRODUCT_PRICE.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.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_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.KIND DESC, ";//惢昳儅僗僞.庬椶
sql += " M_PRODUCT.PRODUCT_CATEGORY , ";//惢昳儅僗僞.惢昳暘椶
sql += " M_PRODUCT.DISP_ORDER, M_PRODUCT.PRODUCT_ID,"; //惢昳儅僗僞.昞帵弴彉丄惢昳ID
sql += " M_COUNTRY.DISP_ORDER, M_COUNTRY.COUNTRY_CODE,"; //崙儅僗僞.昞帵弴彉丄崙僐乕僪
sql += " T_PRODUCT_PRICE.DATE_TIME DESC";//惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
//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_HardwarePrice_stBean hardwarePrice_st = new GBS_HardwarePrice_stBean();
hardwarePrice_st.setProductID(rset.getString("PRODUCT_ID")==null?"":rset.getString("PRODUCT_ID"));
hardwarePrice_st.setProductName(rset.getString("PRODUCT_NAME")==null?"":rset.getString("PRODUCT_NAME"));
hardwarePrice_st.setKind(rset.getString("KIND")==null?"":rset.getString("KIND"));
hardwarePrice_st.setProductCategory(rset.getString("PRODUCT_CATEGORY")==null?"":rset.getString("PRODUCT_CATEGORY"));
hardwarePrice_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("DATE_TIME",date);
date.add(Calendar.HOUR,plusHour);
hardwarePrice_st.setDate(format.format(date.getTime()));
//hardwarePrice_st.setDate(rset.getString("DTIME")==null?"":rset.getString("DTIME"));
hardwarePrice_st.setCountryCode(rset.getString("COUNTRY_CODE")==null?"":rset.getString("COUNTRY_CODE"));
hardwarePrice_st.setCountryName(rset.getString("COUNTRY_NAME")==null?"":rset.getString("COUNTRY_NAME"));
hardwarePrice_st.setLocalCurrencyeKey(rset.getString("LOCAL_CURRENCY_KEY")==null?"":rset.getString("LOCAL_CURRENCY_KEY"));
hardwarePrice_st.setLocalCurrencye(rset.getString("LOCAL_CURRENCY")==null?"":rset.getString("LOCAL_CURRENCY"));
if( rset.getString("LOCAL_PRICE")!=null ){
if(rset.getString("LOCAL_PRICE").trim().startsWith(".")){
hardwarePrice_st.setLocalPrice("0" + rset.getString("LOCAL_PRICE").trim());
}else{
hardwarePrice_st.setLocalPrice(rset.getString("LOCAL_PRICE"));
}
}else{
hardwarePrice_st.setLocalPrice("");
}
if( rset.getString("US_PRICE")!=null ){
if(rset.getString("US_PRICE").trim().startsWith(".")){
hardwarePrice_st.setUSPrice("0" + rset.getString("US_PRICE").trim());
}else{
hardwarePrice_st.setUSPrice(rset.getString("US_PRICE"));
}
}else{
hardwarePrice_st.setUSPrice("");
}
if( rset.getString("EURO_PRICE")!=null ){
if(rset.getString("EURO_PRICE").trim().startsWith(".")){
hardwarePrice_st.setEUROPrice("0" + rset.getString("EURO_PRICE").trim());
}else{
hardwarePrice_st.setEUROPrice(rset.getString("EURO_PRICE"));
}
}else{
hardwarePrice_st.setEUROPrice("");
}
outList.add(hardwarePrice_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;
}
/**
* select edit hardware 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 selectEditHardwarePrice(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 += " M_PRODUCT.KIND, "; //惢昳儅僗僞.庬椶
sql += " M_PRODUCT.PRODUCT_CATEGORY, "; //惢昳儅僗僞.惢昳暘椶
sql += " TO_CHAR(T_PRODUCT_PRICE.DATE_TIME,'YYYYMMDD HH24MMSS') AS DATE_TIME, ";//惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
sql += " T_PRODUCT_PRICE.DATE_TIME AS DTIME, "; //惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
sql += " TO_CHAR(T_PRODUCT_PRICE.RATE_US,'999,999,999.99') AS RATE_US, "; //惢昳扨壙僩儔儞僓僋僔儑儞.愝掕帪偺儗乕僩乮懳US)
sql += " TO_CHAR(T_PRODUCT_PRICE.RATE_EURO,'999,999,999.99') AS RATE_EURO, ";//惢昳扨壙僩儔儞僓僋僔儑儞.愝掕帪偺儗乕僩乮懳EURO)
sql += " T_PRODUCT_PRICE.LOCAL_CURRENCY, "; //惢昳扨壙僩儔儞僓僋僔儑儞.尰抧捠壿
sql += " TO_CHAR(T_PRODUCT_PRICE.LOCAL_PRICE,'999,999,999.99') AS LOCAL_PRICE, ";//CPC尰抧扨壙
sql += " TO_CHAR(T_PRODUCT_PRICE.US_PRICE,'999,999,999.99') AS US_PRICE, ";//CPCUS$扨壙
sql += " TO_CHAR(T_PRODUCT_PRICE.EURO_PRICE,'999,999,999.99') 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 = 'M' "; //惢昳扨壙傢Product偱偡
sql += " AND T_PRODUCT_PRICE.COUNTRY_CODE = '"+ countryCode +"' ";//惢昳扨壙僩儔儞僓僋僔儑儞.崙僐乕僪 = 崙僐乕僪
sql += " GROUP BY ";
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 +"' ";//崙儅僗僞.崙僐乕僪 = 崙僐乕僪
sql += " AND M_PRODUCT.PRODUCT_ID = M_COUNTRY_PRODUCT_R.PRODUCT_ID ";//惢昳儅僗僞.惢昳ID = 崙惢昳娭學儅僗僞.惢昳ID
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_COUNTRY_PRODUCT_R.COUNTRY_CODE = M_COUNTRY.COUNTRY_CODE ";//崙惢昳娭學儅僗僞.崙僐乕僪 = 崙儅僗僞.崙僐乕僪
sql += " AND M_PRODUCT.PRODUCT_ID = MAXDATE.PRODUCT_ID (+) ";//惢昳儅僗僞.惢昳ID = MAX 擔帪.惢昳ID
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 (+)= 'M' ";//惢昳扨壙傢HardWare偱偡
sql += " ORDER BY ";
sql += " M_PRODUCT.KIND DESC, ";//惢昳儅僗僞.庬椶
sql += " M_PRODUCT.PRODUCT_CATEGORY, ";//惢昳儅僗僞.惢昳暘椶
sql += " M_PRODUCT.PRODUCT_ID, ";//惢昳儅僗僞.惢昳ID
sql += " T_PRODUCT_PRICE.DATE_TIME DESC ";//惢昳扨壙僩儔儞僓僋僔儑儞.擔帪
//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_HardwarePrice_stBean hardwarePrice_st = new GBS_HardwarePrice_stBean();
hardwarePrice_st.setProductID(rset.getString("PRODUCT_ID")==null?"":rset.getString("PRODUCT_ID"));
hardwarePrice_st.setProductName(rset.getString("PRODUCT_NAME")==null?"":rset.getString("PRODUCT_NAME"));
hardwarePrice_st.setKind(rset.getString("KIND")==null?"":rset.getString("KIND"));
hardwarePrice_st.setProductCategory(rset.getString("PRODUCT_CATEGORY")==null?"":rset.getString("PRODUCT_CATEGORY"));
hardwarePrice_st.setDateTime(rset.getString("DATE_TIME")==null?"":rset.getString("DATE_TIME"));
hardwarePrice_st.setDate(rset.getString("DTIME")==null?"":rset.getString("DTIME"));
hardwarePrice_st.setRateUS(rset.getString("RATE_US")==null?"":rset.getString("RATE_US"));
hardwarePrice_st.setRateEuro(rset.getString("RATE_EURO")==null?"":rset.getString("RATE_EURO"));
hardwarePrice_st.setLocalCurrencye(rset.getString("LOCAL_CURRENCY")==null?"":rset.getString("LOCAL_CURRENCY"));
if( rset.getString("LOCAL_PRICE")!=null ){
if(rset.getString("LOCAL_PRICE").trim().startsWith(".")){
hardwarePrice_st.setLocalPrice("0" + rset.getString("LOCAL_PRICE").trim());
}else{
hardwarePrice_st.setLocalPrice(rset.getString("LOCAL_PRICE"));
}
}else{
hardwarePrice_st.setLocalPrice("");
}
if( rset.getString("US_PRICE")!=null ){
if(rset.getString("US_PRICE").trim().startsWith(".")){
hardwarePrice_st.setUSPrice("0" + rset.getString("US_PRICE").trim());
}else{
hardwarePrice_st.setUSPrice(rset.getString("US_PRICE"));
}
}else{
hardwarePrice_st.setUSPrice("");
}
if( rset.getString("EURO_PRICE")!=null ){
if(rset.getString("EURO_PRICE").trim().startsWith(".")){
hardwarePrice_st.setEUROPrice("0" + rset.getString("EURO_PRICE").trim());
}else{
hardwarePrice_st.setEUROPrice(rset.getString("EURO_PRICE"));
}
}else{
hardwarePrice_st.setEUROPrice("");
}
outList.add(hardwarePrice_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;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -