GBS.rar_GBS_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(); //ƒŒƒR[ƒhƒtƒ‰ƒO String recordCount = ""; ///////////////////////////////////////////////////////////////////////////// //EDIT SQL sql.append(" SELECT "); sql.append(" M_MIF.PRODUCT_CATEGORY,"); //MIFî•ñD»•i•ª—Þ sql.append(" M_CATEGORY.CATEGORY_NAME,"); //”Ä—pƒ}ƒXƒ^D“à—e•¶Žš‚P sql.append(" M_MIF.COUNTRY_CODE,"); //MIFî•ñD‘ƒR[ƒh sql.append(" M_COUNTRY.COUNTRY_NAME,"); //‘ƒ}ƒXƒ^D‘–¼ sql.append(" M_MIF.SUBSIDIARY_CODE,"); //MIFî•ñDŒ»–@ƒR[ƒh sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME,"); //Œ»–@ƒ}ƒXƒ^DŒ»–@–¼ sql.append(" M_MIF_DETAIL.MODEL,"); //MIFÚ×î•ñDƒ‚ƒfƒ‹ sql.append(" TO_CHAR( M_MIF_DETAIL.QTY, '999,999' ) QTY,"); //MIFÚ×î•ñD”—Ê sql.append(" M_CURRENCY.CURRENCY_NAME,"); //”Ä—pƒ}ƒXƒ^D“à—e•¶Žš‚P //MIFÚ×î•ñDƒn[ƒhƒEƒFƒA‰¿Ši //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Ú×î•ñDCPC‰¿Ši sql.append(" TO_CHAR( M_MIF_DETAIL.CPC_PRICE, '999,999.999999' ) CPC_PRICE,"); //MIFÚ×î•ñDMCV sql.append(" TO_CHAR( M_MIF_DETAIL.MCV, '9,999,999,999' ) MCV, "); //MIFÚ×î•ñD“±“ü“ú sql.append(" TO_CHAR( M_MIF_DETAIL.INSTALLATION_DATE, 'DD-Mon-YYYY') IN_DATE,"); //MIFÚ×î•ñDRFPŽóŽæ“ú sql.append(" TO_CHAR( M_MIF_DETAIL.RFP_RCV_DATE, 'DD-Mon-YYYY' ) RFP_DATE,"); //ƒƒ“ƒo[ƒ}ƒXƒ^. (ƒƒ“ƒo[–¼(Last)+ƒƒ“ƒo[–¼iFirst)) sql.append(" ( M_MEMBER.MEMBER_NAME_FIRST || ' ' || M_MEMBER.MEMBER_NAME_LAST) MEMBER_NAME, "); sql.append(" M_MIF.ATCH_FILE_NAME,"); //MIFî•ñD“Y•tƒtƒ@ƒCƒ‹–¼ sql.append(" M_MIF.REMARKS"); //MIFî•ñDƒŠƒ}[ƒNƒX sql.append(" FROM "); sql.append(" M_MIF,"); //MIFî•ñ sql.append(" M_MEMBER,"); //ƒƒ“ƒo[ƒ}ƒXƒ^ sql.append(" M_SUBSIDIARY,"); //Œ»–@ƒ}ƒXƒ^ sql.append(" M_COUNTRY,"); //‘ƒ}ƒXƒ^ sql.append(" M_MIF_DETAIL,"); //MIFÚ×î•ñ sql.append(" ( SELECT "); sql.append(" (STRING1) CURRENCY_NAME,"); //“à—e•¶Žš‚P sql.append(" (MAIN_KEY) CURRENCY_CODE"); //ƒL[“à—e sql.append(" FROM "); sql.append(" M_WIDE_USE"); //”Ä—pƒ}ƒXƒ^ sql.append(" WHERE"); sql.append(" TARGET_USE = 'CURRENCY'"); sql.append(" ) M_CURRENCY,"); sql.append(" ( SELECT "); sql.append(" (STRING1) CATEGORY_NAME,"); //“à—e•¶Žš‚P sql.append(" (MAIN_KEY) CATEGORY_CODE"); //ƒL[“à—e sql.append(" FROM "); sql.append(" M_WIDE_USE"); //”Ä—pƒ}ƒXƒ^ 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î•ñD»•i•ª—Þ sql.append(" M_MIF.PRODUCT_CATEGORY,"); //‘ƒ}ƒXƒ^D•\Ž¦‡˜‚ƍ‘ƒR[ƒh sql.append(" M_COUNTRY.DISP_ORDER, M_COUNTRY.COUNTRY_CODE,"); //Œ»–@ƒ}ƒXƒ^D•\Ž¦‡˜‚ÆŒ»–@ƒR[ƒh sql.append(" M_SUBSIDIARY.DISP_ORDER, M_SUBSIDIARY.SUBSIDIARY_CODE,"); //MIFÚ×î•ñDƒ‚ƒfƒ‹ 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