gbs_rfpattach_db.java

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

				System.out.println("[End Trace]");
				throw se;
			}
		}
		return returnValueD;
	}

	/**
	 * delete record by sql
	 * @return    int
	 * @exception Exception    Exception for information of other errors
	 * @since 2004/08/14
	 */

	public ReturnValue deleteRfpAttachment(GBS_RfpAttachList_stBean in_st, Connection conn, String userID)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		in_st.setTitle(BaseCommonCheck.convertSql(in_st.getTitle()));
		in_st.setFiletype(BaseCommonCheck.convertSql(in_st.getFiletype()));
		in_st.setAttachmentFileName(BaseCommonCheck.convertSql(in_st.getAttachmentFileName()));
		in_st.setUpdateDate(BaseCommonCheck.convertSql(in_st.getUpdateDate()));
		in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
		in_st.setCreateDate(BaseCommonCheck.convertSql(in_st.getCreateDate()));
		in_st.setCreateUser(BaseCommonCheck.convertSql(in_st.getCreateUser()));
		in_st.setLineNo(BaseCommonCheck.convertSql(in_st.getLineNo()));
		in_st.setCustomerID(BaseCommonCheck.convertSql(in_st.getCustomerID()));
		in_st.setRfpNo(BaseCommonCheck.convertSql(in_st.getRfpNo()));
		in_st.setAttachDate(BaseCommonCheck.convertSql(in_st.getAttachDate()));
		in_st.setUpdateUserName(BaseCommonCheck.convertSql(in_st.getUpdateUserName()));
		in_st.setCreateUserName(BaseCommonCheck.convertSql(in_st.getCreateUserName()));
		in_st.setSeqNo(BaseCommonCheck.convertSql(in_st.getSeqNo()));
		
		userID = BaseCommonCheck.convertSql(userID);
		//Add by Gxk 2004/09/10 End

		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		StringBuffer sqlLock = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		sqlLock.append(" SELECT ");
		sqlLock.append("        CUSTOMER_ID, RFP_NO, LINE_NO ");
		sqlLock.append("   FROM ");
		sqlLock.append("        T_RFP_ATTACH ");
		sqlLock.append("  WHERE ");
		sqlLock.append("        CUSTOMER_ID ='" + in_st.getCustomerID() + "'");
		sqlLock.append("    AND RFP_NO ='" + in_st.getRfpNo() + "'");
		sqlLock.append("    AND LINE_NO ='" + in_st.getLineNo() + "'");
		sqlLock.append("  FOR UPDATE");

		sql.append(" DELETE ");
		sql.append("   FROM T_RFP_ATTACH ");
		sql.append("  WHERE ");
		sql.append("        CUSTOMER_ID ='" + in_st.getCustomerID() + "'");
		sql.append("    AND RFP_NO ='" + in_st.getRfpNo() + "'");
		sql.append("    AND LINE_NO ='" + in_st.getLineNo() + "'");

		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		int bresult = 0;
		String Sql = sql.toString();
		try {

			st = conn.createStatement();
			rset = st.executeQuery(sqlLock.toString());
			if (rset.next()) {

			} else {
				//僨乕僞偑側偄
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValueD.setBussinessError();
				bresult = -1;
			}
			if (bresult >= 0) {
				pstmt = conn.prepareStatement(Sql);
				bresult = pstmt.executeUpdate();
			}

		} catch (Exception exception) {
			System.out.println("[Error Happen!]");
			System.out.println("[Start Trace]");
			exception.printStackTrace();
			System.out.println("[End Trace]");
			throw exception;
		} finally {
			//release db
			try {
				if (rset != null) {
					rset.close();
				}
				if (st != null) {
					st.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException se) {
			}
		}
		returnValueD.setDataValue(new Integer(bresult));
		return returnValueD;
	}

	public ReturnValue insertRfpAttachment(
		GBS_RfpAttachList_stBean in_st,
		Connection conn,
		String userID)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		in_st.setTitle(BaseCommonCheck.convertSql(in_st.getTitle()));
		in_st.setFiletype(BaseCommonCheck.convertSql(in_st.getFiletype()));
		in_st.setAttachmentFileName(BaseCommonCheck.convertSql(in_st.getAttachmentFileName()));
		in_st.setUpdateDate(BaseCommonCheck.convertSql(in_st.getUpdateDate()));
		in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
		in_st.setCreateDate(BaseCommonCheck.convertSql(in_st.getCreateDate()));
		in_st.setCreateUser(BaseCommonCheck.convertSql(in_st.getCreateUser()));
		in_st.setLineNo(BaseCommonCheck.convertSql(in_st.getLineNo()));
		in_st.setCustomerID(BaseCommonCheck.convertSql(in_st.getCustomerID()));
		in_st.setRfpNo(BaseCommonCheck.convertSql(in_st.getRfpNo()));
		in_st.setAttachDate(BaseCommonCheck.convertSql(in_st.getAttachDate()));
		in_st.setUpdateUserName(BaseCommonCheck.convertSql(in_st.getUpdateUserName()));
		in_st.setCreateUserName(BaseCommonCheck.convertSql(in_st.getCreateUserName()));
		in_st.setSeqNo(BaseCommonCheck.convertSql(in_st.getSeqNo()));
		
		userID = BaseCommonCheck.convertSql(userID);
		//Add by Gxk 2004/09/10 End

		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		StringBuffer sql = new StringBuffer();
		StringBuffer sqlMax = new StringBuffer();

		//SQL DEFINE 
		// start sqlMax ===========================
		sqlMax.append(" SELECT ");
		sqlMax.append("   MAX(LINE_NO) AS SEQ");
		sqlMax.append("   FROM T_RFP_ATTACH");
		sqlMax.append("  WHERE ");
		sqlMax.append("        CUSTOMER_ID ='" + in_st.getCustomerID() + "' ");
		sqlMax.append("    AND RFP_NO      ='" + in_st.getRfpNo() + "' ");
		//sqlMax.append( "  FOR UPDATE" );

		System.out.println("[INFO] sql = " + sqlMax.toString());
		// end sqlMax   ===========================
		// start sql ===========================
		sql.append(" INSERT INTO T_RFP_ATTACH ( ");
		sql.append("             CUSTOMER_ID, ");
		sql.append("             RFP_NO, ");
		sql.append("             LINE_NO, ");
		sql.append("             ATTACH_DATE, ");
		sql.append("             TITLE, ");
		sql.append("             ATTACHMENT_FILE_NAME, ");
		sql.append("             ATTACHMENT_FILE, ");
		sql.append("             CREATE_USER, ");
		sql.append("             CREATE_DATE, ");
		sql.append("             UPDATE_USER, ");
		sql.append("             UPDATE_DATE ");
		sql.append(" ) VALUES ( ");
		sql.append("             '" + in_st.getCustomerID() + "', ");
		sql.append("             '" + in_st.getRfpNo() + "', ");
		sql.append("             ?,");
		sql.append("             SYSDATE,");
		sql.append("             '" + in_st.getTitle() + "', ");
		sql.append("             '', ");
		sql.append("             null, ");
		sql.append("             '" + in_st.getCreateUser() + "', ");
		sql.append("             SYSDATE ,");
		sql.append("             '" + in_st.getCreateUser() + "', ");
		sql.append("             SYSDATE )");
		System.out.println("[INFO] sql = " + sql.toString());
		// end sql ===========================

		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		int ret = 0;
		int lineNo = 1;

		//excution result
		int bresult = 0;

		try {
			st = conn.createStatement();
			rset = st.executeQuery(sqlMax.toString());
			if (rset.next()) {
				lineNo = rset.getInt("SEQ");
				lineNo = lineNo + 1;
			}

			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, lineNo);
			bresult = pstmt.executeUpdate();
			if (bresult == 0) {
				messageList.setMessage("", "", "10000013", Integer.MIN_VALUE);
				returnValueD.setBussinessError();
			}
			if (bresult > 0 && !in_st.getSeqNo().equals("")) {
				in_st.setLineNo(String.valueOf(lineNo));
				returnValueD = updateAttachmentFile(in_st, conn);
			}
		} catch (Exception exception) {
			System.out.println("[Error Happen!]");
			System.out.println("[Start Trace]");
			exception.printStackTrace();
			System.out.println("[End Trace]");
			throw exception;
		} finally {
			//release db
			try {
				if (rset != null) {
					rset.close();
				}
				if (st != null) {
					st.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException se) {
				throw se;
			}
		}
		returnValueD.setDataValue(new Integer(bresult));
		return returnValueD;
	}

	/**
	 * method deleteAttachmentFile  
	 * @param GBS_RfpAttachList_stBean RfpAttachList_stBean	
	 * @param Connection conn 
	 * @return ReturnValue
	 * @throws Exception
	 */

	public ReturnValue deleteAttachmentFile(GBS_RfpAttachList_stBean in_st, Connection conn) throws Exception {
		//Add by Gxk 2004/09/10 Start
		in_st.setTitle(BaseCommonCheck.convertSql(in_st.getTitle()));
		in_st.setFiletype(BaseCommonCheck.convertSql(in_st.getFiletype()));
		in_st.setAttachmentFileName(BaseCommonCheck.convertSql(in_st.getAttachmentFileName()));
		in_st.setUpdateDate(BaseCommonCheck.convertSql(in_st.getUpdateDate()));
		in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
		in_st.setCreateDate(BaseCommonCheck.convertSql(in_st.getCreateDate()));
		in_st.setCreateUser(BaseCommonCheck.convertSql(in_st.getCreateUser()));
		in_st.setLineNo(BaseCommonCheck.convertSql(in_st.getLineNo()));
		in_st.setCustomerID(BaseCommonCheck.convertSql(in_st.getCustomerID()));
		in_st.setRfpNo(BaseCommonCheck.convertSql(in_st.getRfpNo()));
		in_st.setAttachDate(BaseCommonCheck.convertSql(in_st.getAttachDate()));
		in_st.setUpdateUserName(BaseCommonCheck.convertSql(in_st.getUpdateUserName()));
		in_st.setCreateUserName(BaseCommonCheck.convertSql(in_st.getCreateUserName()));
		in_st.setSeqNo(BaseCommonCheck.convertSql(in_st.getSeqNo()));
		//Add by Gxk 2004/09/10 End

		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		sql.append(" SELECT");
		sql.append("  ATTACHMENT_FILE, ATTACHMENT_FILE_NAME"); //
		sql.append(" FROM ");
		sql.append("  T_RFP_ATTACH ");
		sql.append(" WHERE ");
		sql.append("       CUSTOMER_ID = '" + in_st.getCustomerID() + "'");
		sql.append("   AND RFP_NO = '" + in_st.getRfpNo() + "'");
		sql.append("   AND LINE_NO = '" + in_st.getLineNo() + "'");
		sql.append(" FOR UPDATE ");
		System.out.println("[INFO] SQL = " + sql.toString());

		int ret = 0;

		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;

		try {
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			if (rset.next()) {
			} else {
				ret = -1;
			}
			if (ret == 0) {
				sql = new StringBuffer();
				sql.append(" UPDATE T_RFP_ATTACH SET ATTACHMENT_FILE = EMPTY_BLOB() ,");
				sql.append("  ATTACHMENT_FILE_NAME = ''");
				sql.append(" WHERE ");
				sql.append("       CUSTOMER_ID = '" + in_st.getCustomerID() + "'");
				sql.append("   AND RFP_NO = '" + in_st.getRfpNo() + "'");
				sql.append("   AND LINE_NO = '" + in_st.getLineNo() + "'");
				System.out.println("[INFO] SQL = " + sql.toString());
				pstmt = conn.prepareStatement(sql.toString());
				ret = pstmt.executeUpdate();
			}

		} catch (Exception exception) {
			System.out.println("[Error Happen!]");
			System.out.println("[Start Trace]");
			exception.printStackTrace();
			System.out.println("[End Trace]");
			throw exception;
		} finally {
			returnValueD.setDataValue(new Integer(ret));
			//release db
			try {
				if (rset != null) {
					rset.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (st != null) {
					st.close();
				}

			} catch (SQLException se) {
				System.out.println("[Error Happen!]");
				System.out.println("[Start Trace]");
				se.printStackTrace();
				System.out.println("[End Trace]");
				throw se;
			}
		}
		return returnValueD;
	}

}