📄 gbs_mcustomer_db.java
字号:
/**
* method GBS_MCustomer_DB.java
* created on 08-11-2004
*
* @author GXK
* @version 1.0
*/
package DB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Locale;
import javax.sql.DataSource;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_Customer_stBean;
import DataBean.GBS_SelectCustomer_stBean;
public class GBS_MCustomer_DB implements SystemConstants {
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_MCustomer_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* select the info of customer
* @param existingMA String
* @param existingGA String
* @param prospectGA String
* @param customerName String
* @param customerAbbr String
* @param statusGA String
* @param resultStatus String
* @param subsidiary String[]
* @param industry String[]
* @param region String[]
* @param countries String[]
* @return ReturnValue
* @throws Exception
*/
public ReturnValue getCustomer(
String existingMA,
String existingGA,
String prospectGA,
String customerName,
String customerAbbr,
String statusGA,
String resultStatus,
String[] subsidiary,
String[] industry,
String[] region,
String[] countries,
int plusHour,
String admin)
throws Exception {
//Edit by Gxk 2004/09/10
MessageList messageList = new MessageList();
existingMA = BaseCommonCheck.convertSql(existingMA);
existingGA = BaseCommonCheck.convertSql(existingGA);
prospectGA = BaseCommonCheck.convertSql(prospectGA);
customerName = BaseCommonCheck.convertSql(customerName);
customerAbbr = BaseCommonCheck.convertSql(customerAbbr);
statusGA = BaseCommonCheck.convertSql(statusGA);
admin = BaseCommonCheck.convertSql(admin);
resultStatus = BaseCommonCheck.convertSql(resultStatus);
if (subsidiary != null) {
for (int i = 0; i < subsidiary.length; i++) {
subsidiary[i] = BaseCommonCheck.convertSql(subsidiary[i]);
}
}
if (industry != null) {
for (int i = 0; i < industry.length; i++) {
industry[i] = BaseCommonCheck.convertSql(industry[i]);
}
}
if (region != null) {
for (int i = 0; i < region.length; i++) {
region[i] = BaseCommonCheck.convertSql(region[i]);
}
}
if (countries != null) {
for (int i = 0; i < countries.length; i++) {
countries[i] = BaseCommonCheck.convertSql(countries[i]);
}
}
StringBuffer sql = new StringBuffer();
ReturnValue returnValue = new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append(" SELECT ");
sql.append(" M_CUSTOMER.CUSTOMER_ID,");
sql.append(" T_RFP.RFP_NO,");
sql.append(" M_CUSTOMER.CUSTOMER_CATEGORY,");
sql.append(" M_CUSTOMER.CUSTOMER_NAME,");
sql.append(" M_CUSTOMER.CUSTOMER_ABBR,");
//Edit by Gxk 2004/09/09 Start
sql.append(" M_CUSTOMER.DELETE_FLG,");
//Edit by Gxk 2004/09/09 End
sql.append(" T_RFP.RESULT,");
sql.append(" WON.STRING1 AS WON_REASON,");
sql.append(" LOST.STRING1 AS LOST_REASON,");
sql.append(" T_RFP.RFP_UPDATE_DATE,");
sql.append(" TO_CHAR(T_RFP.DUE_DATE,'DD-Mon-YYYY') DUE_DATE,");
sql.append(" T_RFP.NDA_UPDATE_DATE,");
sql.append(" T_RFP.REP_UPDATE_DATE,");
sql.append(" T_RFP.RES_UPDATE_DATE,");
sql.append(" W.STRING1,");
sql.append(" C.SUBSIDIARY_NAME,");
sql.append(" C.REGION_NAME,");
sql.append(" C.COUNTRY_NAME,");
sql.append(" M_CUSTOMER.DELETE_FLG");
sql.append(" FROM ");
sql.append(" M_CUSTOMER, ");
sql.append(" T_RFP, ");
sql.append(" ( SELECT MAIN_KEY, STRING1 ");
sql.append(" FROM M_WIDE_USE ");
sql.append(" WHERE M_WIDE_USE.TARGET_USE = 'INDUSTRY'");
sql.append(" ) W , ");
sql.append(" ( SELECT M_COUNTRY.COUNTRY_CODE, ");
sql.append(" M_COUNTRY.COUNTRY_NAME , ");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_CODE,");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME,");
sql.append(" M_REGION.REGION_CODE,");
sql.append(" M_REGION.REGION_NAME ");
sql.append(" FROM M_SUBSIDIARY, M_REGION, M_COUNTRY ");
sql.append(" WHERE M_COUNTRY.SUBSIDIARY_CODE = M_SUBSIDIARY.SUBSIDIARY_CODE (+) ");
sql.append(" AND M_COUNTRY.REGION_CODE = M_REGION.REGION_CODE (+) ");
sql.append(" ) C , ");
sql.append(" M_WIDE_USE WON, ");
sql.append(" M_WIDE_USE LOST ");
sql.append(" WHERE ");
//屭媞儅僗僞.屭媞暘椶 IN 乮夋柺.Customer Category乯
if (existingMA.equals("") && existingGA.equals("") && prospectGA.equals("")) {
} else {
sql.append(" M_CUSTOMER.CUSTOMER_CATEGORY IN ( ");
if ("on".equals(existingMA)) {
sql.append("'1'");
if ("on".equals(existingGA)) {
sql.append(",'2'");
}
if ("on".equals(prospectGA)) {
sql.append(",'3'");
}
} else {
if ("on".equals(existingGA)) {
sql.append("'2'");
if ("on".equals(prospectGA)) {
sql.append(",'3'");
}
} else {
sql.append("'3'");
}
}
sql.append(" ) AND");
//wxf modified at 2004.08.20 End
}
if (!BaseCommonCheck.isEmpty(customerName)) {
//屭媞儅僗僞.屭媞柤 LIKE %夋柺.Customer Name%
sql.append(" M_CUSTOMER.CUSTOMER_NAME LIKE '%" + customerName + "%' AND");
}
if (!BaseCommonCheck.isEmpty(customerAbbr)) {
//屭媞儅僗僞.屭媞棯柤 LIKE %夋柺.Customer Abbr%
sql.append(" M_CUSTOMER.CUSTOMER_ABBR LIKE '%" + customerAbbr + "%' AND");
}
//RFP忣曬丏僗僥乕僞僗(RFP)='1' 夋柺.G/AStatus亖'RFP'帪
if (statusGA.equals("1")) {
sql.append(" T_RFP.RFP_STATUS = '1' AND");
//RFP忣曬丏僗僥乕僞僗(NDA)='1' 夋柺.G/AStatus亖'NDA'帪
} else if (statusGA.equals("2")) {
sql.append(" T_RFP.NDA_STATUS = '1' AND");
//RFP忣曬丏僗僥乕僞僗(Reply)='1' 夋柺.G/AStatus亖'Reply'帪
} else if (statusGA.equals("3")) {
sql.append(" T_RFP.REP_STATUS = '1' AND");
//RFP忣曬丏僗僥乕僞僗(Result)='1' 夋柺.G/AStatus亖Result帪
} else if (statusGA.equals("4")) {
sql.append(" T_RFP.RES_STATUS = '1' AND");
}
//RFP忣曬丏寢壥=夋柺.resultStatus 夋柺.resultStatus=''偺応崌丄忦審偄傜側偄
if (!resultStatus.equals("0")) {
sql.append(" T_RFP.RESULT = '" + resultStatus + "' AND");
}
//抧堟儅僗僞丏抧堟僐乕僪IN(夋柺.Region) 夋柺.Region傪慖戰偟偨帪
if (region != null) {
sql.append(" C.REGION_CODE IN (");
for (int i = 0; i < region.length; i++) {
if (i == 0) {
sql.append("'" + region[i] + "'");
} else {
sql.append(",'" + region[i] + "'");
}
}
sql.append(") AND");
}
//尰朄儅僗僞丏尰朄僐乕僪IN(夋柺.Subsidiary) 夋柺.Subsidiary俠傪慖戰偟偨帪
if (subsidiary != null) {
sql.append(" C.SUBSIDIARY_CODE IN (");
for (int i = 0; i < subsidiary.length; i++) {
if (i == 0) {
sql.append("'" + subsidiary[i] + "'");
} else {
sql.append(",'" + subsidiary[i] + "'");
}
}
sql.append(") AND");
}
//崙儅僗僞丏崙僐乕僪IN(夋柺.Country) 夋柺.Country傪慖戰偟偨帪
if (countries != null) {
sql.append(" M_CUSTOMER.COUNTRY_CODE IN (");
for (int i = 0; i < countries.length; i++) {
if (i == 0) {
sql.append("'" + countries[i] + "'");
} else {
sql.append(",'" + countries[i] + "'");
}
}
sql.append(") AND");
}
//斈梡儅僗僞.僉乕撪梕IN(夋柺.industry) 夋柺.industry傪慖戰偟偨帪
if (industry != null) {
sql.append(" M_CUSTOMER.INDUSTRY IN (");
for (int i = 0; i < industry.length; i++) {
if (i == 0) {
sql.append("'" + industry[i] + "'");
} else {
sql.append(",'" + industry[i] + "'");
}
}
sql.append(") AND");
}
//屭媞儅僗僞.DELETE僼儔僌<>'D'
//sql.append(" (M_CUSTOMER.DELETE_FLG <> 'D' OR M_CUSTOMER.DELETE_FLG IS NULL) AND");
//屭媞儅僗僞.屭媞ID=RFP忣曬.RFP忣曬.屭媞ID
sql.append(" M_CUSTOMER.CUSTOMER_ID = T_RFP.CUSTOMER_ID(+) AND");
//屭媞儅僗僞.崙僐乕僪=崙儅僗僞.崙僐乕僪
sql.append(" M_CUSTOMER.COUNTRY_CODE = C.COUNTRY_CODE(+) AND");
//屭媞儅僗僞.嬈奅=斈梡儅僗僞.僉乕撪梕
sql.append(" M_CUSTOMER.INDUSTRY = W.MAIN_KEY(+)");
sql.append(" AND T_RFP.WON_REASON = WON.MAIN_KEY(+)");
sql.append(" AND T_RFP.LOST_REASON = LOST.MAIN_KEY(+)");
sql.append(" ORDER BY");
sql.append(" M_CUSTOMER.DISP_ORDER,M_CUSTOMER.CUSTOMER_ID,T_RFP.RFP_NO DESC");
System.out.println(sql.toString());
//END EDIT SQL
//END
/////////////////////////////////////////////////////////////////////////////
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
ArrayList returnList = new ArrayList();
int hitCount=0;
while (rset.next()) {
if (!BaseCommonCheck.convertNullToSpace(rset.getString("DELETE_FLG")).equals("D")
|| admin.equals("1")) {
GBS_SelectCustomer_stBean stBean = new GBS_SelectCustomer_stBean();
stBean.setCustomerId(rset.getString("CUSTOMER_ID"));
stBean.setRfpNo(rset.getString("RFP_NO"));
if (rset.getString("CUSTOMER_CATEGORY").equals("1")) {
stBean.setCustomerCategory("Existing M/A");
} else if (rset.getString("CUSTOMER_CATEGORY").equals("2")) {
stBean.setCustomerCategory("Existing G/A");
} else if (rset.getString("CUSTOMER_CATEGORY").equals("3")) {
stBean.setCustomerCategory("Prospect G/A");
} // stBean.setCustomerCategory(rset.getString("CUSTOMER_CATEGORY"));
if (!BaseCommonCheck.isEmpty(rset.getString("CUSTOMER_NAME"))) {
stBean.setCustomerName(rset.getString("CUSTOMER_NAME"));
}
if (!BaseCommonCheck.isEmpty(rset.getString("CUSTOMER_ABBR"))) {
stBean.setCustomerAbbr(rset.getString("CUSTOMER_ABBR"));
}
if ("W".equals(rset.getString("RESULT"))) {
stBean.setResult("Won");
if (!BaseCommonCheck.isEmpty(rset.getString("WON_REASON"))) {
stBean.setWonReason(rset.getString("WON_REASON"));
}
} else if ("L".equals(rset.getString("RESULT"))) {
stBean.setResult("Lost");
if (!BaseCommonCheck.isEmpty(rset.getString("LOST_REASON"))) {
stBean.setWonReason(rset.getString("LOST_REASON"));
}
}
stBean.setDueDate(rset.getString("DUE_DATE"));
Calendar date = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
if (!BaseCommonCheck.isEmpty(rset.getString("RFP_UPDATE_DATE"))) {
rset.getTimestamp("RFP_UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
stBean.setRfpUpdateDate(format.format(date.getTime()));
}
if (!BaseCommonCheck.isEmpty(rset.getString("NDA_UPDATE_DATE"))) {
rset.getTimestamp("NDA_UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
stBean.setNdaUpdateDate(format.format(date.getTime()));
}
if (!BaseCommonCheck.isEmpty(rset.getString("REP_UPDATE_DATE"))) {
rset.getTimestamp("REP_UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
stBean.setRepUpdateDate(format.format(date.getTime()));
}
if (!BaseCommonCheck.isEmpty(rset.getString("RES_UPDATE_DATE"))) {
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -