gbs_mcountry_db.java

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

/** 
 * method GBS_MCountry_DB.java
 * created on 07-30-2004
 * 
 * @author   wxf
 * @version  1.0
 */

package DB;

import java.sql.Connection;
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.sql.DataSource;

import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_CodeName_stBean;
import DataBean.GBS_LittleCountry_stBean;
import DataBean.GBS_MCountry_stBean;
import DataBean.GBS_RfpCountry_stBean;

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

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

	/**
	 * select all data from M_COUNTRY
	 * @return		Arraylist of GBS_MCountry_stBean
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public ReturnValue seletAllCountry() throws Exception {
		String sql = new String();

		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql = " SELECT ";
		sql += "  		COUNTRY_CODE, "; //崙僐乕僪
		sql += "  		DISP_ORDER, "; //昞帵弴彉
		sql += "  		COUNTRY_NAME, "; //崙柤
		sql += "  		REGION_CODE, "; //抧堟僐乕僪
		sql += "  		SUBSIDIARY_CODE, "; //尰朄僐乕僪
		//sql  += "  		PLUS_HOUR, ";				//擔杮帪娫亄帪娫
		sql += "  		CREATE_USER, "; //嶌惉儐乕僓乕
		sql += "  		CREATE_DATE, "; //嶌惉擔帪
		sql += "  		UPDATE_USER, "; //峏怴儐乕僓乕
		sql += "  		UPDATE_DATE "; //峏怴擔帪		
		sql += " FROM ";
		sql += "  		M_COUNTRY";
		sql += " ORDER BY 	";
		sql += "  		DISP_ORDER, COUNTRY_CODE ";

		//END EDIT SQL		
		//DEFINE RETURNVALUE
		ArrayList countryList = new ArrayList();
		//END
		/////////////////////////////////////////////////////////////////////////////

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		ReturnValue returnValueD = new ReturnValue();
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());

			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			while (rset.next()) {
				GBS_MCountry_stBean mCountry_st = new GBS_MCountry_stBean();
				mCountry_st.setCountryCode(rset.getString("COUNTRY_CODE"));
				mCountry_st.setDispOrder(rset.getString("DISP_ORDER"));
				mCountry_st.setCountryName(rset.getString("COUNTRY_NAME"));
				mCountry_st.setRegionCode(rset.getString("REGION_CODE"));
				mCountry_st.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
				//mCountry_st.setPlusHour(rset.getString("PLUS_HOUR"));
				mCountry_st.setCreateUser(rset.getString("CREATE_USER"));
				mCountry_st.setCreateDate(rset.getString("CREATE_DATE"));
				mCountry_st.setUpdateUser(rset.getString("UPDATE_USER"));
				mCountry_st.setUpdateDate(rset.getString("UPDATE_DATE"));
				countryList.add(mCountry_st);
			}
			returnValueD.setDataValue(countryList);
			//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 returnValueD;
	}

	/**
	 * select name  from M_COUNTRY
	 * @return		Arraylist of GBS_MCountry_stBean
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */
	public ReturnValue seletCountryName(String countryCode) throws Exception {
		//countryCode = BaseCommonCheck.convertSql(countryCode);
		String sql = new String();

		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql = " SELECT ";
		sql += " 		COUNTRY_CODE,";
		sql += " 		COUNTRY_NAME ";
		sql += " FROM ";
		sql += " 		M_COUNTRY";
		sql += " WHERE 	";
		sql += " 	  	M_COUNTRY.COUNTRY_CODE IN (" + countryCode + " )";
		sql += " ORDER BY 	";
		sql += "        DISP_ORDER, COUNTRY_CODE";
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		ArrayList countryList = new ArrayList();
		//END
		/////////////////////////////////////////////////////////////////////////////

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		ReturnValue returnValueD = new ReturnValue();
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			while (rset.next()) {
				GBS_LittleCountry_stBean littleCountry_st = new GBS_LittleCountry_stBean();
				littleCountry_st.setCountryCode(rset.getString("COUNTRY_CODE"));
				littleCountry_st.setCountryName(rset.getString("COUNTRY_NAME"));
				countryList.add(littleCountry_st);
			}
			returnValueD.setDataValue(countryList);
			//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 returnValueD;
	}

	/**
	 * get Country by sql
	 * @return ReturnValue
	 * @exception ExceptionException for information of other errors
	 * @since 2004/08/10
	 */
	public ReturnValue getCountry() throws Exception {
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append(" SELECT");
		sql.append(" COUNTRY_NAME,");
		sql.append(" COUNTRY_CODE ");
		sql.append(" FROM ");
		sql.append(" M_COUNTRY ");
		sql.append(" ORDER BY");
		sql.append(" DISP_ORDER,COUNTRY_CODE");

		//END EDIT SQL		
		//DEFINE RETURNVALUE
		Map returnMap = new HashMap();
		//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 lstCountryCode = new ArrayList();
			ArrayList lstCountryName = new ArrayList();
			lstCountryCode.add("-1");
			lstCountryName.add("");
			while (rset.next()) {
				lstCountryCode.add(rset.getString("COUNTRY_CODE"));
				lstCountryName.add(rset.getString("COUNTRY_NAME"));
			}
			returnMap.put(COUNTRY_CODE, lstCountryCode);
			returnMap.put(COUNTRY_NAME, lstCountryName);
			//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) {
			}
		}
		returnValue.setDataValue(returnMap);
		return returnValue;
	}

	/**
	 * get CountryCode and SubsCode by sql
	 * @return ReturnValue
	 * @exception ExceptionException for information of other errors
	 * @since 2004/08/10
	 */
	public ReturnValue getCountryCodeAndSubsCode() throws Exception {
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();

		//--------------------------------------------------------------------------
		//EDIT SQL
		sql.append(" SELECT");
		sql.append(" SUBSIDIARY_CODE,");
		sql.append(" COUNTRY_CODE ");
		sql.append(" FROM ");
		sql.append(" M_COUNTRY ");
		sql.append(" ORDER BY");
		sql.append(" 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()) {
				GBS_CodeName_stBean bean = new GBS_CodeName_stBean();
				bean.setCode("country" + rset.getString("COUNTRY_CODE"));
				bean.setName(rset.getString("SUBSIDIARY_CODE"));
				returnList.add(bean);
			}
			//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) {
			}
		}
		returnValue.setDataValue(returnList);
		return returnValue;
	}

	public ReturnValue seletAllCountryNameByCountrySubsidiary() throws Exception {
		StringBuffer sql = new StringBuffer();

		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append(" SELECT");
		sql.append(" M_COUNTRY.COUNTRY_CODE, ");
		sql.append(" M_COUNTRY.COUNTRY_NAME,");
		sql.append(" M_SUBSIDIARY.SUBSIDIARY_CODE, ");
		sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME");
		sql.append(" FROM ");
		sql.append(" M_COUNTRY, ");
		sql.append(" M_SUBSIDIARY ");
		sql.append(" WHERE ");
		sql.append(" M_COUNTRY.SUBSIDIARY_CODE = M_SUBSIDIARY.SUBSIDIARY_CODE(+) ");
		sql.append(" ORDER BY ");
		sql.append(" M_COUNTRY.DISP_ORDER,M_COUNTRY.COUNTRY_CODE");
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		ArrayList countryList = new ArrayList();
		//END
		/////////////////////////////////////////////////////////////////////////////

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		ReturnValue returnValueD = new ReturnValue();
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			while (rset.next()) {
				GBS_RfpCountry_stBean RfpCountry_stBean = new GBS_RfpCountry_stBean();
				RfpCountry_stBean.setCountryCode(rset.getString("COUNTRY_CODE"));
				RfpCountry_stBean.setCountryName(rset.getString("COUNTRY_NAME"));
				RfpCountry_stBean.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
				RfpCountry_stBean.setSubsidiaryName(rset.getString("SUBSIDIARY_NAME"));
				countryList.add(RfpCountry_stBean);
			}
			returnValueD.setDataValue(countryList);
			//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 returnValueD;
	}
}