gbs_mcountryproductr_db.java

对日软件外包 为东芝做的一个全球商业管理系统

/** 
 * method GBS_MCountryProductR_DB.java
 * @author gxk
 * @version  1.0
 * @since 2004-08-03
 */

package DB;

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 javax.sql.DataSource;

import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_CodeName_stBean;
import DataBean.GBS_CountryProductHandling_stBean;

public class GBS_MCountryProductR_DB implements SystemConstants {
	private DataSource datasource = null;

	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_MCountryProductR_DB(DataSource datasource) {
		this.datasource = datasource;
	}

	/**
	 * select the country and producd info from M_COUNTRY_PRODUCT_R
	 * @param String[] subsidiary
	 * @param String[] country
	 * @param String[] product
	 * @return		ArrayList
	 * @exception	Exception    Exception for information of other errors
	 * @author gxk
	 * @since		2004/08/03
	 */
	public ReturnValue selectCountryProductInfo(
		String colorCpm,
		String bwCpm,
		String fax,
		String printer,
		String cpmFrom,
		String cpmTo,
		String subsidiary[],
		String country[],
		String product[],
		String loginAdmin,
		ArrayList controlCountryList)
		throws Exception {
		//Edit by Gxk 2004/09/10
		colorCpm = BaseCommonCheck.convertSql(colorCpm);
		bwCpm = BaseCommonCheck.convertSql(bwCpm);
		fax = BaseCommonCheck.convertSql(fax);
		printer = BaseCommonCheck.convertSql(printer);
		cpmFrom = BaseCommonCheck.convertSql(cpmFrom);
		cpmTo = BaseCommonCheck.convertSql(cpmTo);

		String strSubs = "";
		if (subsidiary == null) {
			strSubs = "NULL";
		} else {
			strSubs = "(";
			for (int i = 0; i < subsidiary.length; i++) {
				strSubs += "'" + BaseCommonCheck.convertSql(subsidiary[i]) + "'";
				if (i < subsidiary.length - 1) {
					strSubs += ",";
				}
			}
			strSubs += ")";
		}

		String strCountries = "";
		if (country == null) {
			strCountries = "NULL";
		} else {
			strCountries = "(";
			for (int i = 0; i < country.length; i++) {
				strCountries += "'" + BaseCommonCheck.convertSql(country[i]) + "'";
				if (i < country.length - 1) {
					strCountries += ",";
				}
			}
			strCountries += ")";
		}

		String strProducts = "";
		if (product == null) {
			strProducts = "NULL";
		} else {
			strProducts = "(";
			for (int i = 0; i < product.length; i++) {
				strProducts += "'" + BaseCommonCheck.convertSql(product[i]) + "'";
				if (i < product.length - 1) {
					strProducts += ",";
				}
			}
			strProducts += ")";
		}

		loginAdmin = BaseCommonCheck.convertSql(loginAdmin);

		StringBuffer selCountrySql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		//Get the country List
		selCountrySql.append("SELECT ");
		selCountrySql.append(" COUNTRY_CODE,COUNTRY_NAME ");
		selCountrySql.append("FROM ");
		selCountrySql.append(" M_COUNTRY ");
		if (!strCountries.equals("NULL") || !strSubs.equals("NULL")) {
			selCountrySql.append("WHERE ");
			selCountrySql.append(" COUNTRY_CODE IN " + strCountries);
			selCountrySql.append(" OR SUBSIDIARY_CODE IN " + strSubs);
		}
		selCountrySql.append(" ORDER BY ");
		selCountrySql.append(" DISP_ORDER, COUNTRY_CODE ");
		System.out.println("SQL= " + selCountrySql);

		//Get the product List
		StringBuffer selProductSql = new StringBuffer();
		selProductSql.append("SELECT ");
		selProductSql.append(" PRODUCT_ID ");
		selProductSql.append("FROM ");
		selProductSql.append(" M_PRODUCT ");
		selProductSql.append("WHERE ");
		selProductSql.append(" (DELETE_FLG<>'D' OR DELETE_FLG IS NULL) ");
		if (!strProducts.equals("NULL")) {
			selProductSql.append(" AND PRODUCT_ID IN " + strProducts);
		}
		if (!(BaseCommonCheck.isEmpty(fax)
			&& BaseCommonCheck.isEmpty(printer)
			&& BaseCommonCheck.isEmpty(colorCpm)
			&& BaseCommonCheck.isEmpty(bwCpm))) {
			selProductSql.append(" AND ( ");
			boolean isOr = false;
			if (fax.equals("on") && printer.equals("on")) {
				selProductSql.append("PRODUCT_CATEGORY IN ('FAX','PRINTER') ");
				isOr = true;
			} else if (fax.equals("on")) {
				selProductSql.append("PRODUCT_CATEGORY IN ('FAX') ");
				isOr = true;
			} else if (printer.equals("on")) {
				selProductSql.append("PRODUCT_CATEGORY IN ('PRINTER') ");
				isOr = true;
			}
//			if (colorCpm.equals("on") || bwCpm.equals("on")) {
//				if (isOr) {
//					selProductSql.append("OR (");
//				}
//				selProductSql.append("PRODUCT_CATEGORY = 'MFP' AND ");
//				if (colorCpm.equals("on") && bwCpm.equals("on")) {
//					if (BaseCommonCheck.isEmpty(cpmTo)) {
//						selProductSql.append("(( COLOR_CPM>='" + cpmFrom + "') OR (BW_CPM>='" + cpmFrom + "'))");
//					} else {
//						selProductSql.append(
//							"(( COLOR_CPM>='"
//								+ cpmFrom
//								+ "' AND COLOR_CPM<='"
//								+ cpmTo
//								+ "') "
//								+ "OR ( BW_CPM>='"
//								+ cpmFrom
//								+ "' AND BW_CPM<='"
//								+ cpmTo
//								+ "'))");
//					}
//				} else if (colorCpm.equals("on")) {
//					if (BaseCommonCheck.isEmpty(cpmTo)) {
//						selProductSql.append("COLOR_CPM>='" + cpmFrom + "' ");
//					} else {
//						selProductSql.append("COLOR_CPM>='" + cpmFrom + "' AND COLOR_CPM<='" + cpmTo + "'");
//					}
//				} else if (bwCpm.equals("on")) {
//					if (BaseCommonCheck.isEmpty(cpmTo)) {
//						selProductSql.append("BW_CPM>='" + cpmFrom + "' ");
//					} else {
//						selProductSql.append("BW_CPM>='" + cpmFrom + "' AND BW_CPM<='" + cpmTo + "'");
//					}
//				}
//				if (isOr) {
//					selProductSql.append(")");
//				}
//			}
//			selProductSql.append(")");
			if (colorCpm.equals("on") || bwCpm.equals("on")) {
				if (isOr) {
					selProductSql.append("OR (");
				}
				selProductSql.append("PRODUCT_CATEGORY = 'MFP' AND ");
				if (colorCpm.equals("on") && bwCpm.equals("on")) {
						selProductSql.append("(( COLOR_CPM>='" + 1 + "') OR (BW_CPM>='" + 1 + "'))");
				} else if (colorCpm.equals("on")) {
						selProductSql.append("COLOR_CPM>='" + 1 + "' ");
				} else if (bwCpm.equals("on")) {
						selProductSql.append("BW_CPM>='" + 1 + "' ");
				}
				if (isOr) {
					selProductSql.append(")");
				}
			}
			selProductSql.append(")");
		}
		if(!(BaseCommonCheck.isEmpty(cpmFrom)
			&& BaseCommonCheck.isEmpty(cpmTo))){
			selProductSql.append(" AND ( ");
			if (BaseCommonCheck.isEmpty(cpmFrom)) {
				selProductSql.append("BW_CPM<='" + cpmTo + "'");
				selProductSql.append("OR COLOR_CPM<='" + cpmTo + "'");
				selProductSql.append("OR MONOCOLOR_CPM<='" + cpmTo + "'");
				selProductSql.append("OR OTHER_CPM<='" + cpmTo + "'");
			}else if(BaseCommonCheck.isEmpty(cpmTo)){
				selProductSql.append("COLOR_CPM>='" + cpmFrom + "' ");
				selProductSql.append("OR BW_CPM>='" + cpmFrom + "' ");
				selProductSql.append("OR MONOCOLOR_CPM>='" + cpmFrom + "' ");
				selProductSql.append("OR OTHER_CPM>='" + cpmFrom + "' ");
			}else{
				selProductSql.append("(BW_CPM>='" + cpmFrom + "' AND BW_CPM<='" + cpmTo + "')");
				selProductSql.append(" OR (COLOR_CPM>='" + cpmFrom + "' AND COLOR_CPM<='" + cpmTo + "')");
				selProductSql.append(" OR (MONOCOLOR_CPM>='" + cpmFrom + "' AND MONOCOLOR_CPM<='" + cpmTo + "')");
				selProductSql.append(" OR (OTHER_CPM>='" + cpmFrom + "' AND OTHER_CPM<='" + cpmTo + "')");
			}
			selProductSql.append(")");
		}
		selProductSql.append(" ORDER BY DISP_ORDER, PRODUCT_ID");
		System.out.println("SQL= " + selProductSql);

		ArrayList returnList = new ArrayList();

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;

		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(selCountrySql.toString());

			//GET COUNTRY DATA FROM DB RESULTSET
			ArrayList countryList = new ArrayList();
			int hitCount = 0;
			boolean isError = false;
			while (rset.next()) {
				GBS_CodeName_stBean bean = new GBS_CodeName_stBean();
				bean.setCode(rset.getString("COUNTRY_CODE"));
				bean.setName(rset.getString("COUNTRY_NAME"));
				countryList.add(bean);
				hitCount++;
			}
			if (hitCount == 0) {
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValue.setBussinessError();
				isError = true;
			}

			//GET PRODUCT DATA FROM DB RESULTSET
			ArrayList productList = new ArrayList();
			if (!isError) {
				rset = st.executeQuery(selProductSql.toString());
				hitCount = 0;
				while (rset.next()) {
					productList.add(rset.getString("PRODUCT_ID"));
					hitCount++;
				}
				if (hitCount == 0) {
					messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
					returnValue.setBussinessError();
					isError = true;
				}
			}
			if (!isError) {
				//Get the first country's product list(with product info)
				String sqlProduct = "(";
				for (int i = 0; i < productList.size(); i++) {
					sqlProduct += "'"
						+ BaseCommonCheck.convertSql(productList.get(i) == null ? "" : productList.get(i).toString())
						+ "'";
					if (i < productList.size() - 1) {
						sqlProduct += ",";
					}
				}
				sqlProduct += ")";

				String countryCode1 = BaseCommonCheck.convertSql(((GBS_CodeName_stBean) countryList.get(0)).getCode());
				String countryName1 = BaseCommonCheck.convertSql(((GBS_CodeName_stBean) countryList.get(0)).getName());
				StringBuffer sql = new StringBuffer();
				//GET cancontrol1
				boolean canControl1 = false;
				for (int i = 0; controlCountryList != null && i < controlCountryList.size(); i++) {
					if (countryCode1.equals(controlCountryList.get(i)==null?"":controlCountryList.get(i).toString())) {
						canControl1 = true;
						break;
					}
				}

				sql.append("SELECT ");
				sql.append(" T.PRODUCT_ID,T.PRODUCT_ABBR_NAME,T.COLOR_CPM,T.BW_CPM,T.VERSION_NAME,MP.HANDLING ");
				sql.append("FROM ");
				sql.append("	(SELECT ");
				sql.append("	M.DISP_ORDER AS DISP_ORDER_1, ");
				sql.append("	P.DISP_ORDER AS DISP_ORDER_2, ");
				sql.append("	M.COUNTRY_CODE, ");
				sql.append("	P.PRODUCT_ID,P.PRODUCT_ABBR_NAME, ");
				sql.append("	P.COLOR_CPM, P.BW_CPM , ");
				sql.append("	P.VERSION_NAME ");
				sql.append("	FROM ");
				sql.append("	M_COUNTRY M,M_PRODUCT P ");
				sql.append("	WHERE ");
				sql.append("	M.COUNTRY_CODE ='" + countryCode1 + "' ");
				sql.append("	AND  P.PRODUCT_ID IN " + sqlProduct);
				sql.append("	) T,M_COUNTRY_PRODUCT_R MP ");
				sql.append("WHERE ");
				sql.append(" T.COUNTRY_CODE = MP.COUNTRY_CODE(+) ");
				sql.append(" AND T.PRODUCT_ID = MP.PRODUCT_ID(+) ");
				sql.append("ORDER BY ");
				sql.append(" T.DISP_ORDER_2,T.PRODUCT_ID ");
				System.out.println("SQL= " + sql);

				rset = st.executeQuery(sql.toString());
				while (rset.next()) {
					GBS_CountryProductHandling_stBean stBean = new GBS_CountryProductHandling_stBean();
					stBean.setCountryCode(countryCode1);
					stBean.setCountryName(countryName1);
					stBean.setProductCode(rset.getString("PRODUCT_ID"));
					stBean.setProductName(rset.getString("PRODUCT_ABBR_NAME"));
					stBean.setBwCpm(BaseCommonCheck.convertNullToZero(rset.getString("BW_CPM")));
					stBean.setColorCpm(BaseCommonCheck.convertNullToZero(rset.getString("COLOR_CPM")));
					stBean.setHandling(rset.getString("HANDLING"));
					if (BaseCommonCheck.isEmpty(rset.getString("VERSION_NAME"))) {
						stBean.setCanDownload(false);
					} else {
						stBean.setCanDownload(true);
					}
					if (loginAdmin.equals("1")) {
						stBean.setCanControl(true);
					} else {
						stBean.setCanControl(canControl1);
					}
					returnList.add(stBean);
				}
				//add by YM 20040929
				String countryCode = "";
				GBS_CodeName_stBean bean = new GBS_CodeName_stBean();
				for(int i=1;i<countryList.size();i++){
					bean = (GBS_CodeName_stBean)countryList.get(i);
					if(i != countryList.size()-1){
						countryCode = countryCode  + "'" + bean.getCode() + "'" + ",";
					}else{
						countryCode = countryCode  + "'" + bean.getCode() + "'" ;
					}
				}
				sql = new StringBuffer();
				sql.append("SELECT ");
				sql.append(" C.COUNTRY_CODE,C.COUNTRY_NAME,P.PRODUCT_ID,R.HANDLING ");
				sql.append("FROM ");
				sql.append(" M_COUNTRY_PRODUCT_R R,M_PRODUCT P,M_COUNTRY C ");
				sql.append("WHERE ");
				sql.append(" R.PRODUCT_ID=P.PRODUCT_ID ");
				if(!countryCode.equals("")){
					sql.append(" AND R.COUNTRY_CODE IN (" + countryCode + ")");
				}
				sql.append(" AND R.COUNTRY_CODE=C.COUNTRY_CODE ");
				//sql.append(" AND P.PRODUCT_ID IN " + sqlProduct);
				sql.append("ORDER BY ");
				sql.append(" C.DISP_ORDER,P.DISP_ORDER,P.PRODUCT_ID ");
				System.out.println("SQL= " + sql);
				rset = st.executeQuery(sql.toString());
				ArrayList dbList = new ArrayList();
				while (rset.next()) {
					GBS_CountryProductHandling_stBean stBean = new GBS_CountryProductHandling_stBean();
					for(int i=0;i<productList.size();i++){