📄 gbs_mmifdetail_db.java
字号:
/**
* Method GBS_MMifDetail_DB.java
* Created on 08-09-2004
*
* @author XJL
* @version 1.0
*/
package DB;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import oracle.sql.BLOB;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_MMifDetail_stBean;
import DataBean.GBS_MMif_stBean;
import DataBean.GBS_MifEntryDetailList_stBean;
import DataBean.GBS_MifEntryList_stBean;
public class GBS_MMifDetail_DB implements SystemConstants {
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_MMifDetail_DB ( DataSource datasource ) {
this.datasource = datasource;
}
/**
* get MifList by CustomerId
* @param String CustomerId
* @return ReturnValue
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public ReturnValue getMifList(String customerId) 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);
Map returnMap = new HashMap();
StringBuffer sql = new StringBuffer();
ArrayList lstMif = new ArrayList();
//儗僐乕僪僼儔僌
String recordCount = "";
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append(" SELECT ");
sql.append(" M_MIF.PRODUCT_CATEGORY,"); //MIF忣曬丏惢昳暘椶
sql.append(" M_CATEGORY.CATEGORY_NAME,"); //斈梡儅僗僞丏撪梕暥帤侾
sql.append(" M_MIF.COUNTRY_CODE,"); //MIF忣曬丏崙僐乕僪
sql.append(" M_COUNTRY.COUNTRY_NAME,"); //崙儅僗僞丏崙柤
sql.append(" M_MIF.SUBSIDIARY_CODE,"); //MIF忣曬丏尰朄僐乕僪
sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME,"); //尰朄儅僗僞丏尰朄柤
sql.append(" M_MIF_DETAIL.MODEL,"); //MIF徻嵶忣曬丏儌僨儖
sql.append(" TO_CHAR( M_MIF_DETAIL.QTY, '999,999' ) QTY,"); //MIF徻嵶忣曬丏悢検
sql.append(" M_CURRENCY.CURRENCY_NAME,"); //斈梡儅僗僞丏撪梕暥帤侾
//MIF徻嵶忣曬丏僴乕僪僂僃傾壙奿
//Edit by Gxk 2004/08/25 Start
sql.append(" TO_CHAR( M_MIF_DETAIL.HARD_PRICE, '999,999,999,999.00' ) HARD_PRICE,");
//sql.append( " TO_CHAR( M_MIF_DETAIL.HARD_PRICE, '999,999,999.00' ) HARD_PRICE," );
//Edit by Gxk 2004/08/25 End
//MIF徻嵶忣曬丏CPC壙奿
sql.append(" TO_CHAR( M_MIF_DETAIL.CPC_PRICE, '999,999.999999' ) CPC_PRICE,");
//MIF徻嵶忣曬丏MCV
sql.append(" TO_CHAR( M_MIF_DETAIL.MCV, '9,999,999,999' ) MCV, ");
//MIF徻嵶忣曬丏摫擖擔
sql.append(" TO_CHAR( M_MIF_DETAIL.INSTALLATION_DATE, 'DD-Mon-YYYY') IN_DATE,");
//MIF徻嵶忣曬丏RFP庴庢擔
sql.append(" TO_CHAR( M_MIF_DETAIL.RFP_RCV_DATE, 'DD-Mon-YYYY' ) RFP_DATE,");
//儊儞僶乕儅僗僞. (儊儞僶乕柤(Last)+儊儞僶乕柤乮First))
sql.append(" ( M_MEMBER.MEMBER_NAME_FIRST || ' ' || M_MEMBER.MEMBER_NAME_LAST) MEMBER_NAME, ");
sql.append(" M_MIF.ATCH_FILE_NAME,"); //MIF忣曬丏揧晅僼傽僀儖柤
sql.append(" M_MIF.REMARKS"); //MIF忣曬丏儕儅乕僋僗
sql.append(" FROM ");
sql.append(" M_MIF,"); //MIF忣曬
sql.append(" M_MEMBER,"); //儊儞僶乕儅僗僞
sql.append(" M_SUBSIDIARY,"); //尰朄儅僗僞
sql.append(" M_COUNTRY,"); //崙儅僗僞
sql.append(" M_MIF_DETAIL,"); //MIF徻嵶忣曬
sql.append(" ( SELECT ");
sql.append(" (STRING1) CURRENCY_NAME,"); //撪梕暥帤侾
sql.append(" (MAIN_KEY) CURRENCY_CODE"); //僉乕撪梕
sql.append(" FROM ");
sql.append(" M_WIDE_USE"); //斈梡儅僗僞
sql.append(" WHERE");
sql.append(" TARGET_USE = 'CURRENCY'");
sql.append(" ) M_CURRENCY,");
sql.append(" ( SELECT ");
sql.append(" (STRING1) CATEGORY_NAME,"); //撪梕暥帤侾
sql.append(" (MAIN_KEY) CATEGORY_CODE"); //僉乕撪梕
sql.append(" FROM ");
sql.append(" M_WIDE_USE"); //斈梡儅僗僞
sql.append(" WHERE");
sql.append(" TARGET_USE = 'PRODUCT_CATEGORY'");
sql.append(" ) M_CATEGORY");
sql.append(" WHERE ");
sql.append(" M_MIF.CUSTOMER_ID = '" + customerId + "'");
sql.append(" AND M_MIF.PRODUCT_CATEGORY = M_CATEGORY.CATEGORY_CODE(+)");
sql.append(" AND M_MIF.SUBSIDIARY_CODE = M_SUBSIDIARY.SUBSIDIARY_CODE(+)");
sql.append(" AND M_MIF.COUNTRY_CODE = M_COUNTRY.COUNTRY_CODE(+)");
sql.append(" AND M_MIF_DETAIL.USER_ID = M_MEMBER.USER_ID(+)");
sql.append(" AND M_MIF_DETAIL.CURRENCY = M_CURRENCY.CURRENCY_CODE(+)");
sql.append(" AND M_MIF.CUSTOMER_ID = M_MIF_DETAIL.CUSTOMER_ID(+)");
sql.append(" AND M_MIF.PRODUCT_CATEGORY = M_MIF_DETAIL.PRODUCT_CATEGORY(+)");
sql.append(" AND M_MIF.SUBSIDIARY_CODE = M_MIF_DETAIL.SUBSIDIARY_CODE(+)");
sql.append(" AND M_MIF.COUNTRY_CODE = M_MIF_DETAIL.COUNTRY_CODE(+)");
sql.append(" ORDER BY ");
//MIF忣曬丏惢昳暘椶
sql.append(" M_MIF.PRODUCT_CATEGORY,");
//崙儅僗僞丏昞帵弴彉偲崙僐乕僪
sql.append(" M_COUNTRY.DISP_ORDER, M_COUNTRY.COUNTRY_CODE,");
//尰朄儅僗僞丏昞帵弴彉偲尰朄僐乕僪
sql.append(" M_SUBSIDIARY.DISP_ORDER, M_SUBSIDIARY.SUBSIDIARY_CODE,");
//MIF徻嵶忣曬丏儌僨儖
sql.append(" M_MIF_DETAIL.MODEL ");
//END EDIT SQL
/////////////////////////////////////////////////////////////////////////////
//print sql
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
//Create connection
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
while (rset.next()) {
String membername = "";
String firstname = "";
String lastname = "";
GBS_MifEntryDetailList_stBean out_st = new GBS_MifEntryDetailList_stBean();
out_st.setProductCategory(
rset.getString("PRODUCT_CATEGORY") == null ? "" : rset.getString("PRODUCT_CATEGORY").trim());
out_st.setCountryCode(
rset.getString("COUNTRY_CODE") == null ? "" : rset.getString("COUNTRY_CODE").trim());
out_st.setCountryName(
rset.getString("COUNTRY_NAME") == null ? "" : rset.getString("COUNTRY_NAME").trim());
out_st.setSubsidiaryCode(
rset.getString("SUBSIDIARY_CODE") == null ? "" : rset.getString("SUBSIDIARY_CODE").trim());
out_st.setSubsidiaryName(
rset.getString("SUBSIDIARY_NAME") == null ? "" : rset.getString("SUBSIDIARY_NAME").trim());
out_st.setModel(rset.getString("MODEL") == null ? "" : rset.getString("MODEL").trim());
out_st.setQty(rset.getString("QTY") == null ? "" : rset.getString("QTY").trim());
out_st.setCurrency(
rset.getString("CURRENCY_NAME") == null ? "" : rset.getString("CURRENCY_NAME").trim());
if (BaseCommonCheck.isEmpty(rset.getString("HARD_PRICE"))) {
out_st.setHardPrice("");
} else {
if (rset.getString("HARD_PRICE").trim().startsWith(".")) {
out_st.setHardPrice("0" + rset.getString("HARD_PRICE").trim());
} else {
out_st.setHardPrice(rset.getString("HARD_PRICE").trim());
}
}
if (BaseCommonCheck.isEmpty(rset.getString("CPC_PRICE"))) {
out_st.setCpcPrice("");
} else {
if (rset.getString("CPC_PRICE").trim().startsWith(".")) {
out_st.setCpcPrice("0" + rset.getString("CPC_PRICE").trim());
} else {
out_st.setCpcPrice(rset.getString("CPC_PRICE").trim());
}
}
//out_st.setHardPrice( rset.getString("HARD_PRICE") ==null?"":rset.getString("HARD_PRICE").trim() );
//out_st.setCpcPrice( rset.getString("CPC_PRICE") ==null?"":rset.getString("CPC_PRICE").trim() );
out_st.setMcv(rset.getString("MCV") == null ? "" : rset.getString("MCV").trim());
out_st.setInstallDate(rset.getString("IN_DATE") == null ? "" : rset.getString("IN_DATE").trim());
out_st.setRfpRcvDate(rset.getString("RFP_DATE") == null ? "" : rset.getString("RFP_DATE").trim());
out_st.setMemberName(rset.getString("MEMBER_NAME"));
out_st.setString1(rset.getString("CATEGORY_NAME") == null ? "" : rset.getString("CATEGORY_NAME"));
String filename =
(rset.getString("ATCH_FILE_NAME") == null ? "" : rset.getString("ATCH_FILE_NAME").trim());
String filetype = BaseCommonCheck.getFileType(filename);
out_st.setAtchFiletype(filetype);
out_st.setRemarks(rset.getString("REMARKS") == null ? "" : rset.getString("REMARKS").trim());
recordCount = recordCount + 1;
lstMif.add(out_st);
}
//儗僐乕僪悢丂>丂0帪
if (lstMif.size() > 0) {
recordCount = "1";
}
returnMap.put(RECORDCOUNT, recordCount);
returnMap.put(DETAIL, lstMif);
returnValue.setDataValue(returnMap);
//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) {
throw se;
}
}
return returnValue;
}
/**
* get MifDetailList by GBS_MMif_stBean
* @param GBS_MMif_stBean in
* @return ReturnValue
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public ReturnValue getMifDetailList( GBS_MMif_stBean in ) throws Exception {
//Add by Gxk 2004/09/10 Start
in.setAtchFileName(BaseCommonCheck.convertSql(in.getAtchFileName()));
in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
in.setCreateUser(BaseCommonCheck.convertSql(in.getCreateUser()));
in.setCustomerId(BaseCommonCheck.convertSql(in.getCustomerId()));
in.setCustomerName(BaseCommonCheck.convertSql(in.getCustomerName()));
in.setProductCategory(BaseCommonCheck.convertSql(in.getProductCategory()));
in.setRemarks(BaseCommonCheck.convertSql(in.getRemarks()));
in.setSubsidiaryCode(BaseCommonCheck.convertSql(in.getSubsidiaryCode()));
in.setUpdateUser(BaseCommonCheck.convertSql(in.getUpdateUser()));
//Add by Gxk 2004/09/10 End
ReturnValue returnValue = new ReturnValue();
MessageList errorMsg = new MessageList();
returnValue.setMessageList( errorMsg );
Map MifDetail = new HashMap();
ArrayList lstMifDetail = new ArrayList();
GBS_MMif_stBean Mif_st = new GBS_MMif_stBean();
StringBuffer sql = new StringBuffer();
int recordCount = 0;
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append( " SELECT" );
//屭媞儅僗僞.屭媞ID
sql.append( " M_CUSTOMER.CUSTOMER_NAME," );
//MIF忣曬. 惢昳暘椶
sql.append( " M_MIF.PRODUCT_CATEGORY," );
//MIF忣曬. 崙僐乕僪
sql.append( " M_MIF.COUNTRY_CODE," );
//MIF忣曬. 尰朄僐乕僪
sql.append( " M_MIF.SUBSIDIARY_CODE," );
//MIF徻嵶忣曬. 儌僨儖
sql.append( " M_MIF_DETAIL.MODEL," );
//MIF徻嵶忣曬. 悢検
sql.append( " TO_CHAR( M_MIF_DETAIL.QTY, '999,999' ) QTY," );
//MIF徻嵶忣曬. 捠壿
sql.append( " M_MIF_DETAIL.CURRENCY," );
//MIF徻嵶忣曬. 僴乕僪僂僃傾壙奿
sql.append( " TO_CHAR( M_MIF_DETAIL.HARD_PRICE, '999,999,999.00' ) HARD_PRICE," );
//MIF徻嵶忣曬. CPC壙奿
sql.append( " TO_CHAR( M_MIF_DETAIL.CPC_PRICE, '999,999.999999' ) CPC_PRICE," );
//MIF徻嵶忣曬. MCV
sql.append( " TO_CHAR( M_MIF_DETAIL.MCV, '9,999,999,999' ) MCV," );
//MIF徻嵶忣曬. 摫擖擔
sql.append( " TO_CHAR( M_MIF_DETAIL.INSTALLATION_DATE, 'DD-Mon-YYYY') IN_DATE," );
//MIF徻嵶忣曬. RFP庴庢擔
sql.append( " TO_CHAR( M_MIF_DETAIL.RFP_RCV_DATE, 'DD-Mon-YYYY' ) RFP_DATE," );
//儊儞僶乕儅僗僞. (儊儞僶乕柤(Last)+儊儞僶乕柤乮First))
sql.append( " (M_MEMBER.MEMBER_NAME_FIRST || ' ' || M_MEMBER.MEMBER_NAME_LAST) MEMBER_NAME," );
//MIF徻嵶忣曬. 搊榐幰
sql.append( " M_MIF_DETAIL.USER_ID," );
//MIF忣曬. 揧晅僼傽僀儖柤
sql.append( " M_MIF.ATCH_FILE_NAME," );
//MIF忣曬. 儕儅乕僋僗
sql.append( " M_MIF.REMARKS" );
sql.append( " FROM " );
sql.append( " M_MEMBER," ); //儊儞僶乕儅僗僞
sql.append( " M_CUSTOMER," ); //屭媞儅僗僞
sql.append( " M_MIF," ); //MIF忣曬
sql.append( " M_MIF_DETAIL" ); //MIF徻嵶忣曬
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( " M_MIF.CUSTOMER_ID = '" + in.getCustomerId() + "'" );
sql.append( " AND M_MIF.PRODUCT_CATEGORY = '" + in.getProductCategory() + "'" );
sql.append( " AND M_MIF.SUBSIDIARY_CODE = '" + in.getSubsidiaryCode() + "'" );
sql.append( " AND M_MIF.COUNTRY_CODE = '" + in.getCountryCode() + "'" );
sql.append( " AND M_MIF.CUSTOMER_ID = M_CUSTOMER.CUSTOMER_ID(+)" );
sql.append( " AND M_MIF_DETAIL.USER_ID = M_MEMBER.USER_ID(+)" );
sql.append( " AND M_MIF.CUSTOMER_ID = M_MIF_DETAIL.CUSTOMER_ID(+)" );
sql.append( " AND M_MIF.PRODUCT_CATEGORY = M_MIF_DETAIL.PRODUCT_CATEGORY(+)" );
sql.append( " AND M_MIF.SUBSIDIARY_CODE = M_MIF_DETAIL.SUBSIDIARY_CODE(+)" );
sql.append( " AND M_MIF.COUNTRY_CODE = M_MIF_DETAIL.COUNTRY_CODE(+)" );
sql.append( " ORDER BY " );
sql.append( " M_MIF_DETAIL.MODEL " );
//print sql
System.out.println( "[INFO] sql = " + sql.toString() );
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -