📄 gbs_mmifdetail_db.java
字号:
//END EDIT SQL
/////////////////////////////////////////////////////////////////////////////
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_MMifDetail_stBean out_st = new GBS_MMifDetail_stBean();
out_st.setModel( (rset.getString("MODEL") ==null?"":rset.getString( "MODEL" ).trim()) );
out_st.setModelOld((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") ==null?"":rset.getString("CURRENCY").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.setRfpDate( rset.getString( "RFP_DATE" ) ==null?"":rset.getString("RFP_DATE").trim() );
out_st.setUserName(rset.getString("MEMBER_NAME") ==null?"":rset.getString("MEMBER_NAME").trim() );
out_st.setPerson(rset.getString("MEMBER_NAME") ==null?"":rset.getString("MEMBER_NAME").trim() );
out_st.setUserId( rset.getString( "USER_ID" ) ==null?"":rset.getString("USER_ID").trim() );
if (recordCount == 0){
Mif_st.setCustomerName( rset.getString("CUSTOMER_NAME") ==null?"":rset.getString("CUSTOMER_NAME").trim() );
Mif_st.setCountryCode( rset.getString("COUNTRY_CODE") ==null?"":rset.getString("COUNTRY_CODE").trim() );
Mif_st.setSubsidiaryCode( rset.getString("SUBSIDIARY_CODE") ==null?"":rset.getString("SUBSIDIARY_CODE").trim() );
Mif_st.setProductCategory( rset.getString("PRODUCT_CATEGORY")==null?"":rset.getString("PRODUCT_CATEGORY").trim() );
Mif_st.setAtchFileName( rset.getString("ATCH_FILE_NAME") ==null?"":rset.getString("ATCH_FILE_NAME").trim() );
Mif_st.setRemarks( rset.getString("REMARKS") ==null?"":rset.getString("REMARKS").trim() );
}
recordCount = recordCount + 1;
lstMifDetail.add(out_st);
}
MifDetail.put(MIF, Mif_st);
MifDetail.put(MIF_DETAIL, lstMifDetail);
returnValue.setDataValue( MifDetail );
if ( recordCount ==0 ){
//僄儔乕張棟
errorMsg.setMessage("", "", "10000004", Integer.MIN_VALUE);
returnValue.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) {
throw se;
}
}
return returnValue;
}
/**
* update MIF and MIF_DETAIL
* @return int
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public ReturnValue updateMifDetail(GBS_MifEntryList_stBean MifIn, String seqNo, String fileStatus)
throws Exception {
//Add by Gxk 2004/09/10 Start
MifIn.setCountryCode(BaseCommonCheck.convertSql(MifIn.getCountryCode()));
MifIn.setCustomerId(BaseCommonCheck.convertSql(MifIn.getCustomerId()));
MifIn.setProductCategory(BaseCommonCheck.convertSql(MifIn.getProductCategory()));
MifIn.setRemarks(BaseCommonCheck.convertSql(MifIn.getRemarks()));
MifIn.setSubsidiaryCode(BaseCommonCheck.convertSql(MifIn.getSubsidiaryCode()));
MifIn.setUser(BaseCommonCheck.convertSql(MifIn.getUser()));
//Add by Gxk 2004/09/10 End
ReturnValue returnValue = new ReturnValue();
MessageList messageList = new MessageList();
returnValue.setMessageList(messageList);
StringBuffer sql = new StringBuffer();
//start sql edit ================================
sql.append(" SELECT ");
sql.append(" CUSTOMER_ID ");
sql.append(" FROM ");
sql.append(" M_MIF ");
sql.append(" WHERE ");
sql.append(" M_MIF.CUSTOMER_ID = '" + MifIn.getCustomerId() + "'");
sql.append(" AND M_MIF.PRODUCT_CATEGORY = '" + MifIn.getProductCategory() + "'");
sql.append(" AND M_MIF.SUBSIDIARY_CODE = '" + MifIn.getSubsidiaryCode() + "'");
sql.append(" AND M_MIF.COUNTRY_CODE = '" + MifIn.getCountryCode() + "'");
sql.append(" FOR UPDATE ");
//print sql
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
Connection conn = null;
int bresult = 0;
try {
String strSql = sql.toString();
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(strSql);
if (!rset.next()) {
bresult = -1;
}
if (bresult == 0) {
sql = new StringBuffer();
strSql = "";
sql.append(" UPDATE ");
sql.append(" M_MIF ");
sql.append(" SET ");
sql.append(" REMARKS = '" + MifIn.getRemarks() + "',"); //儕儅乕僋僗
if (fileStatus.equals("delete")) { //deleteFile = true;
sql.append(" ATCH_FILE_NAME = '', "); //揧晅僼傽僀儖柤
sql.append(" ATCH_FILE = null, "); //揧晅僼傽僀儖
}
sql.append(" UPDATE_USER = '" + MifIn.getUser() + "',"); //峏怴儐乕僓乕
sql.append(" UPDATE_DATE = SYSDATE"); //峏怴擔帪
sql.append(" WHERE ");
sql.append(" M_MIF.CUSTOMER_ID = '" + MifIn.getCustomerId() + "'");
sql.append(" AND M_MIF.PRODUCT_CATEGORY = '" + MifIn.getProductCategory() + "'");
sql.append(" AND M_MIF.SUBSIDIARY_CODE = '" + MifIn.getSubsidiaryCode() + "'");
sql.append(" AND M_MIF.COUNTRY_CODE = '" + MifIn.getCountryCode() + "'");
//print sql
System.out.println("[INFO] sql = " + sql.toString());
strSql = sql.toString();
//st = conn.createStatement();
pstmt = conn.prepareStatement(strSql);
//bresult= st.execute(strSql);
bresult = pstmt.executeUpdate();
if (bresult <= 0) {
conn.rollback();
messageList.setMessage("", "", "10000002", Integer.MIN_VALUE);
returnValue.setMessageList(messageList);
returnValue.setBussinessError();
} else {
ArrayList lstDetail = new ArrayList();
lstDetail = MifIn.getGBSMMifDetail_st();
for (int i = 0; lstDetail != null && i < lstDetail.size(); i++) {
GBS_MMifDetail_stBean detail_st = (GBS_MMifDetail_stBean) lstDetail.get(i);
if ((!detail_st.getModel().trim().equals("")) && detail_st.getModelOld().trim().equals("")) {
//insert MIF徻嵶忣曬
returnValue = insertMifDetail(MifIn, detail_st, conn);
} else if (
!detail_st.getModel().trim().equals("") && !detail_st.getModelOld().trim().equals("")) {
//update MIF徻嵶忣曬
bresult = updateMifEntryDetail(MifIn, detail_st, conn);
} else if (
(detail_st.getModel().trim().equals("")) && (!detail_st.getModelOld().trim().equals(""))) {
//delete from MIF徻嵶忣曬
bresult = deleteEntryDetail(MifIn, detail_st, conn);
} else if (
(detail_st.getModel().trim().equals("")) && (detail_st.getModelOld().trim().equals(""))) {
//do nothing
continue;
}
if (returnValue.isError()) {
if (returnValue.isBussinessError()) {
messageList.addAll(returnValue.getMessageList());
returnValue.setBussinessError();
break;
}
} else {
if (bresult <= 0) {
break;
}
}
}
if (bresult > 0 && fileStatus.equals("update") && !seqNo.equals("")) {
//udpate attachmentFile
returnValue = this.updateAttachmentFile(MifIn, seqNo, conn);
bresult = ((Integer) returnValue.getDataValue()).intValue();
}
}
if (bresult > 0) {
conn.commit();
} else {
conn.rollback();
}
}
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//release db
try {
if (rset != null) {
rset.close();
}
if (pstmt != null) {
pstmt.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
se.printStackTrace();
System.out.println("[End Trace]");
throw se;
}
}
return returnValue;
}
/**
* update MIF_DETAIL
* @return int
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public int updateMifEntryDetail(GBS_MifEntryList_stBean MifIn, GBS_MMifDetail_stBean detail_st, Connection conn)
throws Exception {
//Add by Gxk 2004/09/10 Start
MifIn.setCountryCode(BaseCommonCheck.convertSql(MifIn.getCountryCode()));
MifIn.setCustomerId(BaseCommonCheck.convertSql(MifIn.getCustomerId()));
MifIn.setProductCategory(BaseCommonCheck.convertSql(MifIn.getProductCategory()));
MifIn.setRemarks(BaseCommonCheck.convertSql(MifIn.getRemarks()));
MifIn.setSubsidiaryCode(BaseCommonCheck.convertSql(MifIn.getSubsidiaryCode()));
MifIn.setUser(BaseCommonCheck.convertSql(MifIn.getUser()));
//Add by Gxk 2004/09/10 End
ArrayList lstDetail = new ArrayList();
StringBuffer sql = new StringBuffer();
// start sql edit ================================
sql.append(" SELECT ");
sql.append(" CUSTOMER_ID ");
sql.append(" FROM ");
sql.append(" M_MIF_DETAIL ");
sql.append(" WHERE ");
sql.append(" M_MIF_DETAIL.CUSTOMER_ID = '" + MifIn.getCustomerId() + "'");
sql.append(" AND M_MIF_DETAIL.PRODUCT_CATEGORY = '" + MifIn.getProductCategory() + "'");
sql.append(" AND M_MIF_DETAIL.SUBSIDIARY_CODE = '" + MifIn.getSubsidiaryCode() + "'");
sql.append(" AND M_MIF_DETAIL.COUNTRY_CODE = '" + MifIn.getCountryCode() + "'");
sql.append(" AND M_MIF_DETAIL.MODEL = '" + detail_st.getModelOld() + "'");
sql.append(" FOR UPDATE ");
//print sql
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
String strSql = "";
int bresult = 0;
try {
st = conn.createStatement();
strSql = sql.toString();
rset = st.executeQuery(strSql);
if (!rset.next()) {
bresult = -1;
}
if (bresult == 0) {
sql = new StringBuffer();
strSql = "";
sql.append(" UPDATE ");
sql.append(" M_MIF_DETAIL ");
sql.append(" SET ");
sql.append(" MODEL = '" + detail_st.getModel() + "',"); //儌僨儖
sql.append(" QTY = '" + detail_st.getQty() + "',"); //悢検
sql.append(" CURRENCY = '" + detail_st.getCurrency() + "',"); //捠壿
sql.append(" HARD_PRICE = '" + detail_st.getHardPrice() + "',"); //僴乕僪僂僃傾壙奿
sql.append(" CPC_PRICE = '" + detail_st.getCpcPrice() + "',"); //CPC壙奿
sql.append(" MCV = '" + detail_st.getMcv() + "',"); //MCV
sql.append(" INSTALLATION_DATE = " + "to_date('" + detail_st.getInstallDate() + "','yyyy/MM/dd'),"); //摫擖擔
//sql.append("to_date( '" + detail_st.getInstallDate() + "','yyyy/MM/dd'),");
sql.append(" RFP_RCV_DATE = " + "to_date('" + detail_st.getRfpDate() + "','yyyy/MM/dd'),"); //RFP庴庢擔
//sql.append(" RFP_RCV_DATE = '" + detail_st.getRfpDate() + "',"); //RFP庴庢擔
sql.append(" USER_ID = '" + detail_st.getUserId() + "',"); //搊榐幰
sql.append(" UPDATE_USER = '" + detail_st.getUserId() + "',"); //峏怴儐乕僓乕
sql.append(" UPDATE_DATE = SYSDATE"); //峏怴擔帪
sql.append(" WHERE ");
sql.append(" M_MIF_DETAIL.CUSTOMER_ID = '" + MifIn.getCustomerId() + "'");
sql.append(" AND M_MIF_DETAIL.PRODUCT_CATEGORY = '" + MifIn.getProductCategory() + "'");
sql.append(" AND M_MIF_DETAIL.SUBSIDIARY_CODE = '" + MifIn.getSubsidiaryCode() + "'");
sql.append(" AND M_MIF_DETAIL.COUNTRY_CODE = '" + MifIn.getCountryCode() + "'");
sql.append(" AND M_MIF_DETAIL.MODEL = '" + detail_st.getModelOld() + "'");
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -