/** 
 * method GBS_CustomerInquiryForm.java
 * created on 08-11-2004
 * 
 * @author   GXK
 * @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 javax.sql.DataSource;

import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_CodeName_stBean;

public class GBS_MRegion_DB implements SystemConstants {
	
	private DataSource datasource = null;
	
	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_MRegion_DB(DataSource datasource) {
	  this.datasource = datasource;
	}

	/**
	 * get REGION code and name list by sql
	 * @return ReturnValue
	 * @exception ExceptionException for information of other errors
	 * @since 2004/07/26
	 * @author Gxk
	 */
	public ReturnValue getRegionCodeNameList() throws Exception {
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
		sql.append(" SELECT ");
		sql.append(" REGION_CODE,REGION_NAME");
		sql.append(" FROM ");
		sql.append(" M_REGION ");
		sql.append(" ORDER BY ");
		sql.append(" DISP_ORDER,REGION_CODE");
//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();
			while (rset.next()) {
				GBS_CodeName_stBean codeName_st = new GBS_CodeName_stBean();
				codeName_st.setCode(rset.getString("REGION_CODE"));//SUBSIDIARY_CODE
				codeName_st.setName(rset.getString("REGION_NAME"));//SUBSIDIARY_NAME
				returnList.add(codeName_st);
			}
			returnValue.setDataValue(returnList);
//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;
	}
}