gbs_msubsidiary_db.java

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

		if (on.equals(committe) || on.equals(support) || on.equals(operation) || on.equals(localMember)) {
			sql.append(" AND M_MEMBER.MEMBER_KIND in('-1' ");
			if (on.equals(committe)) {
				sql.append(" ,'1' ");
			}
			if (on.equals(support)) {
				sql.append(" ,'2' ");
			}
			if (on.equals(operation)) {
				sql.append(" ,'3' ");
			}
			if (on.equals(localMember)) {
				sql.append(" ,'0' ");
			}
			sql.append(" ) ");
		}
		sql.append(" ORDER BY ");
		sql.append(" M_MEMBER.DISP_ORDER, M_MEMBER.USER_ID "); //END EDIT SQL		
		//	System.out.print(sql.toString());
		//DEFINE RETURNVALUE
		ArrayList returnList = new ArrayList();
		//END
		/////////////////////////////////////////////////////////////////////////////

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		try {
			conn = this.datasource.getConnection();
			System.out.println(sql.toString());
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			int hitCount = 0;
			while (rset.next()) {
				//update by YM 20040909 start
				if ((admins.equals("1")
					|| (BaseCommonCheck.convertNullToSpace(rset.getString("VISIBLE")).equals("Y")
						&& !BaseCommonCheck.convertNullToSpace(rset.getString("DELETE_FLG")).equals("D")))
					|| (loginId.equals(rset.getString("USER_ID")))) {
					//update by YM 20040909 end
					GBS_MMember_stBean mMember_st = new GBS_MMember_stBean();
					mMember_st.setUserId(rset.getString("USER_ID"));
					mMember_st.setMemberNameFirst(
						rset.getString("MEMBER_NAME_FIRST") + " " + rset.getString("MEMBER_NAME_LAST"));
					mMember_st.setMemberNameLast(rset.getString("MEMBER_NAME_LAST"));
					mMember_st.setDept(rset.getString("DEPT"));
					mMember_st.setSubsidiaryCode(rset.getString("SUBSIDIARY_NAME")); //SUBSIDIARY_NAME
					mMember_st.setEmail(rset.getString("E_MAIL"));
					mMember_st.setTelephone(rset.getString("TELEPHONE"));
					//update by YM 20040909 start
					if (BaseCommonCheck.convertNullToSpace(rset.getString("DELETE_FLG")).equals("D")) {
						mMember_st.setDeleteFlg("仜");
					}
					mMember_st.setVisible(rset.getString("VISIBLE"));
					//update by YM 20040909 end
					String tmpStrKind = rset.getString("MEMBER_KIND");
					if (tmpStrKind == null) {
						mMember_st.setMenberKind("");
					} else if (tmpStrKind.equals("1")) {
						mMember_st.setMenberKind("Committee");
					} else if (tmpStrKind.equals("2")) {
						mMember_st.setMenberKind("Support");
					} else if (tmpStrKind.equals("3")) {
						mMember_st.setMenberKind("Operation");
					} else if (tmpStrKind.equals("0")) {
						mMember_st.setMenberKind("Local Member");
					}
					returnList.add(mMember_st);
					hitCount++;
				}
			}
			if (hitCount == 0) {
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValue.setBussinessError();
			} else {
				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;
	}

	/**
	 * get SUBSIDIARY code and name list by sql
	 * @return ReturnValue
	 * @exception ExceptionException for information of other errors
	 * @since 2004/07/26
	 * @author Gxk
	 */
	public ReturnValue getSubsidiaryCodeNameList() throws Exception {
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		/////////////////////////////////////////////////////////////////////////////
		//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		
		//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("SUBSIDIARY_CODE")); //SUBSIDIARY_CODE
				codeName_st.setName(rset.getString("SUBSIDIARY_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;
	}

	/**
	 * get PlusHour by sql
	 * @param String subsidiaryCode
	 * @return ReturnValue
	 * @exception ExceptionException for information of other errors
	 * @since 2004/08/31
	 */
	public ReturnValue getPlusHour(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("	PLUS_HOUR "); //SUBSIDIARY_NAME
		sql.append("FROM ");
		sql.append("	M_SUBSIDIARY ");
		sql.append("WHERE ");
		sql.append("	SUBSIDIARY_CODE = '" + subsidiaryCode + "'");
		//END EDIT SQL		
		//DEFINE RETURNVALUE
		String returnStr = "0";
		//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()) {
				if (!BaseCommonCheck.isEmpty(rset.getString("PLUS_HOUR"))) {
					returnStr = rset.getString("PLUS_HOUR");
				}
				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;
	}

}