gbs_mmember_db.java

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

/** 
 * method GBS_MMember_DB.java
 * created on 07-29-2004
 * 
 * @author   GXK
 * @version  1.0
 */

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 java.util.HashMap;

import javax.sql.DataSource;

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

public class GBS_MMember_DB implements SystemConstants {

	private DataSource datasource = null;

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

	/**
	 * get GBS_MMember_st by userid
	 * @param		String UserId
	 * @return		ReturnValue
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/29
	 */

	public ReturnValue selectByUserId(String UserId) throws Exception {
		//Add by Gxk 2004/09/10 Start
		UserId = BaseCommonCheck.convertSql(UserId);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();

		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append("SELECT ");
		sql.append(" USER_ID , "); //儐乕僓乕ID
		sql.append(" DISP_ORDER , "); //昞帵弴彉
		sql.append(" PASSWORD , "); //僷僗儚乕僪
		sql.append(" MEMBER_NAME_FIRST , "); //儊儞僶乕柤乮First)
		sql.append(" MEMBER_NAME_LAST , "); //儊儞僶乕柤(Last)
		sql.append(" SUBSIDIARY_CODE , "); //尰朄僐乕僪
		sql.append(" DEPT , "); //晹彁
		sql.append(" E_MAIL , "); //儊乕儖傾僪儗僗
		sql.append(" TELEPHONE , "); //揹榖斣崋
		sql.append(" BOSS_NAME_FIRST , "); //忋巌偺柤慜(First)
		sql.append(" BOSS_NAME_LAST , "); //忋巌偺柤慜(Last)
		sql.append(" BOSS_E_MAIL , "); //忋巌偺儊乕儖傾僪儗僗
		sql.append(" MEMBER_KIND , "); //儊儞僶乕庬椶
		sql.append(" ADMIN_AUTHORITY , "); //俙倓倣倝値尃尷
		sql.append(" DOWNLOAD_OK , "); //僟僂儞儘乕僪壜斲
		sql.append(" DELETE_FLG , "); //嶍彍僼儔僌
		sql.append(" CREATE_USER , "); //嶌惉儐乕僓乕
		sql.append(" CREATE_DATE , "); //嶌惉擔帪
		sql.append(" UPDATE_USER , "); //峏怴儐乕僓乕
		sql.append(" UPDATE_DATE "); //峏怴擔帪
		sql.append("FROM ");
		sql.append("	M_MEMBER ");
		sql.append("WHERE (DELETE_FLG<>'D' OR DELETE_FLG IS NULL) ");
		if (!"".equals(UserId)) {
			sql.append(" AND USER_ID = '" + UserId + "' ");
		}

		//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
			//DEFINE RETURNVALUE
			GBS_MMember_stBean out_st = new GBS_MMember_stBean();
			int hitCount = 0;
			while (rset.next()) {
				out_st.setUserId(rset.getString("USER_ID"));
				out_st.setDispOrder(rset.getString("DISP_ORDER"));
				out_st.setPassword(rset.getString("PASSWORD"));
				out_st.setMemberNameFirst(rset.getString("MEMBER_NAME_FIRST"));
				out_st.setMemberNameLast(rset.getString("MEMBER_NAME_LAST"));
				out_st.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
				out_st.setDept(rset.getString("DEPT"));
				out_st.setEmail(rset.getString("E_MAIL"));
				out_st.setTelephone(rset.getString("TELEPHONE"));
				out_st.setBossNameFirst(rset.getString("BOSS_NAME_FIRST"));
				out_st.setBossNameLast(rset.getString("BOSS_NAME_LAST"));
				out_st.setBossEMail(rset.getString("BOSS_E_MAIL"));
				out_st.setMenberKind(rset.getString("MEMBER_KIND"));
				out_st.setAdminAuthority(rset.getString("ADMIN_AUTHORITY"));
				out_st.setDownloadOk(rset.getString("DOWNLOAD_OK"));
				out_st.setDeleteFlg(rset.getString("DELETE_FLG"));
				out_st.setCreateUser(rset.getString("CREATE_USER"));
				out_st.setCreateDate(rset.getString("CREATE_DATE"));
				out_st.setUpdateUser(rset.getString("UPDATE_USER"));
				out_st.setUpdateDate(rset.getString("UPDATE_DATE"));

				hitCount++;
			}
			if (hitCount == 0) {
				//returnValue.setErrorCode("10000005");
				messageList.setMessage("UserId", UserId, "10000005", Integer.MIN_VALUE);
			} else {
				returnValue.setErrorCode("");
				returnValue.setDataValue(out_st);
			}

			//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) {
			}
			if (messageList.size() > 0) {
				returnValue.setMessageList(messageList);
				returnValue.setBussinessError();
			}
		}
		return returnValue;
	}

	/**
	 * update GBS_MMember_st by userid
	 * @param		GBS_MMember_stBean in_st
	 * @param		String oldUserId
	 * @return		ReturnValue
	 * @exception	Exception    Exception for information of other errors
	 * @since		2004/07/30
	 */

	public ReturnValue updateByUserId(GBS_MMember_stBean in_st, String oldUserId) throws Exception {
		//Add by Gxk 2004/09/10 Start
		oldUserId = BaseCommonCheck.convertSql(oldUserId);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();

		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		//edit sql1:select date from DB by userid
		sql.append("SELECT ");
		sql.append("	USER_ID ");
		sql.append("FROM ");
		sql.append("	M_MEMBER ");
		sql.append("WHERE USER_ID = ");
		sql.append("'" + BaseCommonCheck.convertSql(in_st.getUserId()) + "'");

		sql1.append("SELECT ");
		sql1.append(" USER_ID ");
		sql1.append("FROM ");
		sql1.append(" M_MEMBER ");
		sql1.append("WHERE");
		sql1.append(" USER_ID = '" + oldUserId + "' ");
		sql1.append(" FOR UPDATE ");

		//edit sql2:update date at DB by in_st
		sql2.append("UPDATE M_MEMBER SET ");
		sql2.append("USER_ID = '" + BaseCommonCheck.convertSql(in_st.getUserId()) + "', ");
		sql2.append("DISP_ORDER = '" + BaseCommonCheck.convertSql(in_st.getDispOrder()) + "', ");
		sql2.append("PASSWORD = '" + BaseCommonCheck.convertSql(in_st.getPassword()) + "', ");
		sql2.append("MEMBER_NAME_FIRST = '" + BaseCommonCheck.convertSql(in_st.getMemberNameFirst()) + "', ");
		sql2.append("MEMBER_NAME_LAST = '" + BaseCommonCheck.convertSql(in_st.getMemberNameLast()) + "', ");
		sql2.append("SUBSIDIARY_CODE = '" + BaseCommonCheck.convertSql(in_st.getSubsidiaryCode()) + "', ");
		sql2.append("DEPT = '" + BaseCommonCheck.convertSql(in_st.getDept()) + "', ");
		sql2.append("E_MAIL = '" + BaseCommonCheck.convertSql(in_st.getEmail()) + "', ");
		sql2.append("TELEPHONE = '" + BaseCommonCheck.convertSql(in_st.getTelephone()) + "', ");
		sql2.append("BOSS_NAME_FIRST = '" + BaseCommonCheck.convertSql(in_st.getBossNameFirst()) + "', ");
		sql2.append("BOSS_NAME_LAST = '" + BaseCommonCheck.convertSql(in_st.getBossNameLast()) + "', ");
		sql2.append("BOSS_E_MAIL = '" + BaseCommonCheck.convertSql(in_st.getBossEMail()) + "', ");
		sql2.append("MEMBER_KIND = '" + BaseCommonCheck.convertSql(in_st.getMenberKind()) + "', ");
		sql2.append("ADMIN_AUTHORITY = '" + BaseCommonCheck.convertSql(in_st.getAdminAuthority()) + "', ");
		sql2.append("DOWNLOAD_OK = '" + BaseCommonCheck.convertSql(in_st.getDownloadOk()) + "', ");
		sql2.append("UPDATE_USER = '" + BaseCommonCheck.convertSql(in_st.getUpdateUser()) + "', ");
		sql2.append("VISIBLE = '" + BaseCommonCheck.convertSql(in_st.getVisible()) + "', ");
		sql2.append("DELETE_FLG = '',");
		sql2.append("UPDATE_DATE = SYSDATE ");
		sql2.append("WHERE USER_ID = '" + oldUserId + "' ");
		//		System.out.println(sql2.toString());
		//END EDIT SQL		
		/////////////////////////////////////////////////////////////////////////////

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

		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql1.toString());
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			boolean isExist = false;
			while (rset.next()) {
				if (oldUserId.equals(rset.getString("USER_ID"))) {
					isExist = true;
				}
				if (!isExist) {
					messageList.setMessage("UserId", oldUserId, "10000001", Integer.MIN_VALUE);
				}
			}
			if (isExist && !oldUserId.equals(in_st.getUserId())) {
				rset = st.executeQuery(sql.toString());
				while (rset.next()) {
					isExist = false;
				}
				if (!isExist) {
					messageList.setMessage("UserId", in_st.getUserId(), "10000002", Integer.MIN_VALUE);
				}
			}
			if (isExist) {
				PreparedStatement pstmt = conn.prepareStatement(sql2.toString());
				int countUpdate = pstmt.executeUpdate();
				if (countUpdate > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
			}
			//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) {
			}
			if (messageList.size() > 0) {
				returnValue.setMessageList(messageList);
				returnValue.setBussinessError();
			}
		}