gbs_mcountryproductr_db.java

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

						if(productList.get(i).equals(rset.getString("PRODUCT_ID"))){
							stBean.setCountryCode(rset.getString("COUNTRY_CODE"));
							stBean.setCountryName(rset.getString("COUNTRY_NAME"));
							stBean.setProductCode(rset.getString("PRODUCT_ID"));
							stBean.setHandling(rset.getString("HANDLING"));
							dbList.add(stBean);
							break;
						}
					}
				}
				GBS_CountryProductHandling_stBean stBean = null;
				
				int memory = 0;
				for (int i = 1; i < countryList.size(); i++) {
					bean = (GBS_CodeName_stBean)countryList.get(i);
					countryCode1 = BaseCommonCheck.convertSql(bean.getCode());
					GBS_CountryProductHandling_stBean nullBean = new GBS_CountryProductHandling_stBean();
					nullBean.setCountryCode(bean.getCode());
					nullBean.setCountryName(bean.getName());
					boolean canControl = false;
					for (int j = 0; controlCountryList != null && j < controlCountryList.size(); j++) {
						if (countryCode1.equals(controlCountryList.get(j)==null?"":controlCountryList.get(j).toString())) {
							canControl = true;
							break;
						}
					}
					if (loginAdmin.equals("1")) {
						nullBean.setCanControl(true);
					} else {
						nullBean.setCanControl(canControl);
					}
					//nullBean.setCanControl()
					int count=0;
					for(int j=0;j<productList.size();j++){
						
						if(dbList.size() != 0){
							for(int k=memory;k<dbList.size();k++){
								stBean = (GBS_CountryProductHandling_stBean)dbList.get(k);
								bean = (GBS_CodeName_stBean)countryList.get(i);
								if (loginAdmin.equals("1")) {
									stBean.setCanControl(true);
								} else {
									stBean.setCanControl(canControl);
								}
								if(bean.getCode().equals(stBean.getCountryCode())){
									if(productList.get(j).equals(stBean.getProductCode())){
										returnList.add(dbList.get(k));
										memory++;
										count++;
									}else{
										returnList.add(nullBean);
										k--;
										count++;
									}
									break;
								}else{
									returnList.add(nullBean);
									count++;
									break;
								}
							}
						}else{
							returnList.add(nullBean);
						}
						
						if(memory != 0 && memory == dbList.size() && count != productList.size()){
							returnList.add(nullBean);
						}
						
						if(count>j && memory == dbList.size()){
							j=count;
							//returnList.add(nullBean);
						}
					}
				}
			}
				/*
				//get the other country's product info (only with handling)
				for (int i = 1; i < countryList.size(); i++) {
					GBS_CodeName_stBean bean = (GBS_CodeName_stBean) countryList.get(i);
					String countryCode = BaseCommonCheck.convertSql(bean.getCode());
					String countryName = BaseCommonCheck.convertSql(bean.getName());

					sql = new StringBuffer();
					sql.append("SELECT ");
					sql.append(" P.PRODUCT_ID,R.HANDLING ");
					sql.append("FROM ");
					sql.append(" M_COUNTRY_PRODUCT_R R,M_PRODUCT P ");
					sql.append("WHERE ");
					sql.append(" R.COUNTRY_CODE ='" + countryCode + "' ");
					sql.append(" AND R.PRODUCT_ID=P.PRODUCT_ID ");
					//sql.append(" AND P.PRODUCT_ID IN " + sqlProduct);
					sql.append("ORDER BY ");
					sql.append(" P.DISP_ORDER,P.PRODUCT_ID ");
					System.out.println(sql);

					rset = st.executeQuery(sql.toString());
					boolean canControl = false;
					for (int j = 0; controlCountryList != null && j < controlCountryList.size(); j++) {
						if (countryCode.equals(controlCountryList.get(j)==null?"":controlCountryList.get(j).toString())) {
							canControl = true;
							break;
						}
					}

					int intProduct = 0;
					ArrayList dbList = new ArrayList();
					while (rset.next()) {
						GBS_CountryProductHandling_stBean stBean = new GBS_CountryProductHandling_stBean();
						stBean.setCountryCode(countryCode);
						stBean.setCountryName(countryName);
						stBean.setProductCode(rset.getString("PRODUCT_ID"));
						//stBean.setProductName(rset.getString("PRODUCT_ABBR_NAME"));
						stBean.setHandling(rset.getString("HANDLING"));
						if (loginAdmin.equals("1")) {
							stBean.setCanControl(true);
						} else {
							stBean.setCanControl(canControl);
						}
						//End add
						dbList.add(stBean);
					}
					GBS_CountryProductHandling_stBean nullBean = new GBS_CountryProductHandling_stBean();
					nullBean.setCountryCode(countryCode);
					nullBean.setCountryName(countryName);
					if (loginAdmin.equals("1")) {
						nullBean.setCanControl(true);
					} else {
						nullBean.setCanControl(canControl);
					}
					int intDB = 0;
					for (int j = 0; j < productList.size(); j++) {
						if (intDB >= dbList.size()) {
							returnList.add(nullBean);
						} else {
							for (int k = intDB; k < dbList.size(); k++) {
								GBS_CountryProductHandling_stBean stBean = (GBS_CountryProductHandling_stBean) dbList.get(k);
								if (stBean.getProductCode().equals(productList.get(j)==null?"":productList.get(j).toString())) {
									returnList.add(stBean);
									intDB = k + 1;
									break;
								} else if (k == dbList.size() - 1) {
									returnList.add(nullBean);
								}
							}
						}
					}
				}*/
				
			if (returnList.size() > 0 && !isError) {
				System.out.println("hitCount= " + returnList.size());
				returnValue.setDataValue(returnList);
			} else if (!isError) {
				messageList.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) {
			}
		}
		return returnValue;
	}

	/**
	 * select name  from M_COUNTRY by the subsidiary
	 * @param subsidiary String
	 * @return		Arraylist 
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public ReturnValue seletCountryCodeBySubsidiary(String subsidiary) throws Exception {
		subsidiary = BaseCommonCheck.convertSql(subsidiary);
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append("SELECT ");
		sql.append(" COUNTRY_CODE ");
		sql.append("FROM ");
		sql.append(" M_COUNTRY ");
		sql.append("WHERE SUBSIDIARY_CODE = '" + subsidiary + "'");
		sql.append("ORDER BY DISP_ORDER, COUNTRY_CODE ");
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		ArrayList returnList = new ArrayList();
		//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
			while (rset.next()) {
				returnList.add(rset.getString("COUNTRY_CODE"));
			}
			if (returnList.size() > 0) {
				returnValue.setDataValue(returnList);
			} else {
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValue.setBussinessError();
				returnValue.setMessageList(messageList);
			}
			//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) {
			}
		}
		return returnValue;
	}

	/**
	 * update handling at DB
	 * @param country String[]
	 * @param product String[]
	 * @param handling String[]
	 * @param user String
	 * @return		boolean 
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public ReturnValue updateHandling(String[] country, String[] product, String[] handling, String user) throws Exception {
		if (handling != null) {
			for (int i = 0; i < handling.length; i++) {
				handling[i] = BaseCommonCheck.convertSql(handling[i]);
			}
		}
		if (country != null) {
			for (int i = 0; i < country.length; i++) {
				country[i] = BaseCommonCheck.convertSql(country[i]);
			}
		}
		if (product != null) {
			for (int i = 0; product == null || i < product.length; i++) {
				product[i] = BaseCommonCheck.convertSql(product[i]);
			}
		}
		user = BaseCommonCheck.convertSql(user);
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		boolean returnBool = true;
		ReturnValue returnValue = new ReturnValue();
		//END
		/////////////////////////////////////////////////////////////////////////////

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		int result = 0;
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			//rset = st.executeQuery(sql.toString());
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			if (handling.length != product.length * country.length) {
				returnBool = false;
			} else {
				int k = 0; //mark the pistion of handling[]
				for (int i = 0; i < country.length; i++) {
					for (int j = 0; j < product.length; j++) {
						StringBuffer sqlSel = new StringBuffer();
						StringBuffer sql = new StringBuffer();
						sqlSel.append("SELECT HANDLING FROM M_COUNTRY_PRODUCT_R WHERE ");
						sqlSel.append("COUNTRY_CODE='" + country[i] + "' AND PRODUCT_ID='" + product[j] + "'");
						sqlSel.append(" FOR UPDATE ");
						//					System.out.println(sqlSel.toString());
						rset = st.executeQuery(sqlSel.toString());
						if (rset.next()) {
							if ((rset.getString("HANDLING") == null && handling[k].equals(""))
								|| (rset.getString("HANDLING") != null && rset.getString("HANDLING").equals(handling[k]))) {
								k++;
								continue;
							}
							sql.append("UPDATE M_COUNTRY_PRODUCT_R SET HANDLING = '" + handling[k] + "',");
							sql.append("UPDATE_USER = '" + user + "', UPDATE_DATE = SYSDATE WHERE ");
							sql.append("COUNTRY_CODE='" + country[i] + "' AND PRODUCT_ID='" + product[j] + "'");
						} else {
							if (handling[k].equals("")) {
								k++;
								continue;
							}
							sql.append(
								"INSERT INTO M_COUNTRY_PRODUCT_R (COUNTRY_CODE,PRODUCT_ID,HANDLING,CREATE_USER,CREATE_DATE) ");
							sql.append(
								"VALUES ('"
									+ country[i]
									+ "','"
									+ product[j]
									+ "','"
									+ handling[k]
									+ "','"
									+ user
									+ "',SYSDATE)");
						}
						System.out.println(sql.toString());
						PreparedStatement pstmt = conn.prepareStatement(sql.toString());
						result = pstmt.executeUpdate();
						if (result <= 0) {
							break;
						}
						k++;
					}
				}
			}
			//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 (result > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
				if (rset != null) {
					rset.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
			}
		}
		returnValue.setDataValue(new Boolean(returnBool));
		return returnValue;
	}
}