gbs_msubsidiary_db.java

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

/** 
 * method GBS_MSubsidiary_DB.java
 * created on 07-29-2004
 * 
 * @author GXK
 * @version1.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 javax.sql.DataSource;

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

public class GBS_MSubsidiary_DB implements SystemConstants {

	private DataSource datasource = null;

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

	/**
	 * get SUBSIDIARY by sql
	 * @return HashMap
	 * @exception ExceptionException for information of other errors
	 * @since 2004/07/26
	 */
	public ReturnValue getSubsidiaryList() throws Exception {
		StringBuffer sql = new StringBuffer();

		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append("SELECT ");
		sql.append("	SUBSIDIARY_CODE, "); //SUBSIDIARY_CODE
		sql.append("	SUBSIDIARY_NAME "); //SUBSIDIARY_NAME
		sql.append("FROM ");
		sql.append("	M_SUBSIDIARY ");
		sql.append("ORDER BY ");
		sql.append("	DISP_ORDER, SUBSIDIARY_CODE");
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		HashMap returnMap = new HashMap();
		//END
		/////////////////////////////////////////////////////////////////////////////

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		ReturnValue returnValue = new ReturnValue();
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			ArrayList subsidiaryCodeList = new ArrayList();
			ArrayList subsidiaryNameList = new ArrayList();
			subsidiaryCodeList.add("-1"); //SUBSIDIARY_CODE
			subsidiaryNameList.add(""); //SUBSIDIARY_NAME
			while (rset.next()) {
				subsidiaryCodeList.add(rset.getString("SUBSIDIARY_CODE")); //SUBSIDIARY_CODE
				subsidiaryNameList.add(rset.getString("SUBSIDIARY_NAME")); //SUBSIDIARY_NAME
			}
			returnMap.put(SUBSIDIARY_NAME, subsidiaryNameList);
			returnMap.put(SUBSIDIARY_CODE, subsidiaryCodeList);
			//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 SUBSIDIARYName by sql
	 * @param String subsidiaryCode
	 * @return ReturnValue
	 * @exception ExceptionException for information of other errors
	 * @since 2004/07/26
	 */
	public ReturnValue getSubsidiaryName(String subsidiaryCode) throws Exception {
		//Add by Gxk 2004/09/10 Start
		subsidiaryCode = BaseCommonCheck.convertSql(subsidiaryCode);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append("SELECT ");
		sql.append("	SUBSIDIARY_CODE, "); //SUBSIDIARY_CODE
		sql.append("	SUBSIDIARY_NAME "); //SUBSIDIARY_NAME
		sql.append("FROM ");
		sql.append("	M_SUBSIDIARY ");
		sql.append("WHERE ");
		sql.append("	SUBSIDIARY_CODE = '" + subsidiaryCode + "'");
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		String returnStr = "";
		//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
			int hitCount = 0;
			while (rset.next()) {
				returnStr = rset.getString("SUBSIDIARY_NAME"); //SUBSIDIARY_CODE
				hitCount++;
			}
			if (hitCount == 0) {
				messageList.setMessage("", "", "10000005", Integer.MIN_VALUE);
				returnValue.setBussinessError();
			} else {
				returnValue.setDataValue(returnStr);
			}
			//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;
	}

	/**
	 * get usermaster info from db
	 * @param UserName
	 * @param Subsidiary
	 * @param Committe
	 * @param Support
	 * @param Operation
	 * @param LocalMember
	 * @return
	 * @throws Exception
	 */
	public ReturnValue getUserMasterList(
		String userFirstName,
		String userLastName,
		String subsidiary,
		String committe,
		String support,
		String operation,
		String localMember,
		String admins,
		String loginId)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		userFirstName = BaseCommonCheck.convertSql(userFirstName);
		userLastName = BaseCommonCheck.convertSql(userLastName);
		subsidiary = BaseCommonCheck.convertSql(subsidiary);
		committe = BaseCommonCheck.convertSql(committe);
		support = BaseCommonCheck.convertSql(support);
		operation = BaseCommonCheck.convertSql(operation);
		localMember = BaseCommonCheck.convertSql(localMember);
		admins = BaseCommonCheck.convertSql(admins);
		loginId = BaseCommonCheck.convertSql(loginId);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append("SELECT ");
		sql.append("	M_MEMBER.USER_ID, "); //
		sql.append("	MEMBER_NAME_FIRST, "); //SUBSIDIARY_NAME_FIRST
		sql.append("	MEMBER_NAME_LAST, "); //SUBSIDIARY_NAME_FIRST
		sql.append("	M_SUBSIDIARY.SUBSIDIARY_NAME, "); //SUBSIDIARY_NAME
		sql.append("	M_MEMBER.E_MAIL, "); //
		sql.append("	M_MEMBER.TELEPHONE, "); //
		sql.append("	M_MEMBER.DEPT, "); //
		sql.append("	M_MEMBER.MEMBER_KIND, "); //
		sql.append("	M_MEMBER.DELETE_FLG, "); //
		sql.append("	M_MEMBER.VISIBLE "); //
		sql.append("FROM ");
		sql.append(" 	M_MEMBER, ");
		//sql.append(" left outer join ");
		sql.append("	M_SUBSIDIARY ");
		//sql.append(" on ");
		sql.append("WHERE M_SUBSIDIARY.SUBSIDIARY_CODE (+)= M_MEMBER.SUBSIDIARY_CODE ");
		//sql.append(" AND (M_MEMBER.DELETE_FLG<>'D' OR M_MEMBER.DELETE_FLG IS NULL) ");
		if (!"".equals(userFirstName)) {
			sql.append(" AND UPPER(M_MEMBER.MEMBER_NAME_FIRST) like '%" + userFirstName.toUpperCase() + "%' ");
		}
		if (!"".equals(userLastName)) {
			sql.append(" AND UPPER(M_MEMBER.MEMBER_NAME_LAST) like '%" + userLastName.toUpperCase() + "%' ");
		}
		if (!"-1".equals(subsidiary)) {
			sql.append(" AND M_MEMBER.SUBSIDIARY_CODE = '" + subsidiary + "' ");
		}