gbs_trfp_db.java

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

		} 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;
	}

	public ReturnValue insertRfpInfo(
		String customerID,
		String repRcvDate,
		String dueDate,
		String infoDueDate,
		String Country,
		String hardwareCategory,
		String leaseRentalOutright,
		String quantity,
		String remarks,
		String comment,
		String createUserID,
		String subdiadiaryCode)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		customerID = BaseCommonCheck.convertSql(customerID);
		repRcvDate = BaseCommonCheck.convertSql(repRcvDate);
		dueDate = BaseCommonCheck.convertSql(dueDate);
		infoDueDate = BaseCommonCheck.convertSql(infoDueDate);
		Country = BaseCommonCheck.convertSql(Country);
		hardwareCategory = BaseCommonCheck.convertSql(hardwareCategory);
		leaseRentalOutright = BaseCommonCheck.convertSql(leaseRentalOutright);
		quantity = BaseCommonCheck.convertSql(quantity);
		remarks = BaseCommonCheck.convertSql(remarks);
		comment = BaseCommonCheck.convertSql(comment);
		createUserID = BaseCommonCheck.convertSql(createUserID);
		subdiadiaryCode = BaseCommonCheck.convertSql(subdiadiaryCode);
		//Add by Gxk 2004/09/10 End
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		String sql1 = new String();
		String sql2 = new String();
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		//	  ///////////////////////////////////////////////////////////////////////////
		//	  EDIT SQL sql1 ===========(start)=======================
		int rfpNo = 1;
		sql1 = " SELECT MAX(RFP_NO) AS RFP_NO  FROM T_RFP WHERE CUSTOMER_ID='" + customerID + "'";
		System.out.println("[INFO] sql = " + sql1);
		//sql1==========================(end)==========================
		conn = this.datasource.getConnection();
		st = conn.createStatement();
		rset = st.executeQuery(sql1);
		if (rset.next()) {
			rfpNo = rset.getInt("RFP_NO");
			rfpNo = rfpNo + 1;
		}
		//sql2====================(start)==============================
		sql2 = " INSERT INTO ";
		sql2 += " T_RFP (";
		sql2 += " CUSTOMER_ID,";
		sql2 += " RFP_NO ,";
		sql2 += " REP_RCV_DATE ,";
		sql2 += " DUE_DATE,";
		sql2 += " INFO_DUE_DATE ,";
		sql2 += " INFO_COUNTRY ,";
		sql2 += " INFO_HARD_CATEGORY,";
		sql2 += " INFO_LS_RT_OP,";
		sql2 += " QUANTITY,";
		sql2 += " REMARKS,";
		sql2 += " COMMENT1,";
		sql2 += " RFP_STATUS,";
		sql2 += " RFP_USER_ID,";
		sql2 += " RFP_UPDATE_DATE,";
		sql2 += " RFP_SUBSIDIARY_CODE,";
		sql2 += " CREATE_USER ,";
		sql2 += " CREATE_DATE )";
		sql2 += " VALUES (  '";
		sql2 += customerID + "','";
		sql2 += rfpNo + "',";
		sql2 += "to_date('" + repRcvDate + "','yyyy/MM/dd'),";
		sql2 += "to_date('" + dueDate + "','yyyy/MM/dd'),";
		sql2 += "to_date('" + infoDueDate + "','yyyy/MM/dd'),'";
		sql2 += Country + "','";
		sql2 += hardwareCategory + "','";
		sql2 += leaseRentalOutright + "','";
		sql2 += quantity + "','";
		sql2 += remarks + "','";
		sql2 += comment + "','";
		sql2 += "1','";
		sql2 += createUserID + "',";
		sql2 += "to_date(SYSDATE,'yyyy/MM/dd'),'";
		sql2 += subdiadiaryCode + "','";
		sql2 += createUserID + "',";
		sql2 += "to_date(SYSDATE,'yyyy/MM/dd')";
		sql2 += " )";
		System.out.println("[INFO] INSERT RFP sql = " + sql2);
		//sql2=================(end)===================
		//	  END EDIT SQL		
		//	  DEFINE RETURNVALUE
		//	  END
		//	  ///////////////////////////////////////////////////////////////////////////
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();

			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			pstmt = conn.prepareStatement(sql2);
			int bresult = pstmt.executeUpdate();
			if (bresult > 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) {
			}
		}
		returnValue.setDataValue(new Integer(rfpNo));
		return returnValue;
	}

	/**
	 * update RFP info
	 * @param customerID String
	 * @param rfpNo String
	 * @param repRcvDate String
	 * @param dueDate String
	 * @param infoDueDate String
	 * @param Country String
	 * @param hardwareCategory String
	 * @param leaseRentalOutright String
	 * @param quantity String
	 * @param remarks String
	 * @param comment String
	 * @param updateUser String
	 * @param subsidiaryCode String
	 * @return the number of update record 
	 * @throws Exception
	 */
	public ReturnValue updateRfpInfo(
		String customerID,
		String rfpNo,
		String repRcvDate,
		String dueDate,
		String infoDueDate,
		String Country,
		String hardwareCategory,
		String leaseRentalOutright,
		String quantity,
		String remarks,
		String comment,
		String updateUser,
		String subsidiaryCode)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		customerID = BaseCommonCheck.convertSql(customerID);
		rfpNo = BaseCommonCheck.convertSql(rfpNo);
		repRcvDate = BaseCommonCheck.convertSql(repRcvDate);
		dueDate = BaseCommonCheck.convertSql(dueDate);
		infoDueDate = BaseCommonCheck.convertSql(infoDueDate);
		Country = BaseCommonCheck.convertSql(Country);
		hardwareCategory = BaseCommonCheck.convertSql(hardwareCategory);
		leaseRentalOutright = BaseCommonCheck.convertSql(leaseRentalOutright);
		quantity = BaseCommonCheck.convertSql(quantity);
		remarks = BaseCommonCheck.convertSql(remarks);
		comment = BaseCommonCheck.convertSql(comment);
		updateUser = BaseCommonCheck.convertSql(updateUser);
		subsidiaryCode = BaseCommonCheck.convertSql(subsidiaryCode);
		//Add by Gxk 2004/09/10 End

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

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

		sql0 = "  SELECT";
		sql0 += " COUNT(*) AS COUNT";
		sql0 += " FROM";
		sql0 += " T_RFP ";
		sql0 += "WHERE CUSTOMER_ID ='" + customerID + "'";
		sql0 += " AND 	RFP_NO  ='" + rfpNo + "'";

		sql = "  UPDATE  ";
		sql += "  T_RFP ";
		sql += " SET ";
		sql += " REP_RCV_DATE = to_date('" + repRcvDate + "','yyyy/MM/dd'),";
		sql += " DUE_DATE = to_date('" + dueDate + "','yyyy/MM/dd'),";
		sql += " INFO_DUE_DATE = to_date('" + infoDueDate + "','yyyy/MM/dd'),";
		sql += " INFO_COUNTRY = '" + Country + "',";
		sql += " INFO_HARD_CATEGORY = '" + hardwareCategory + "',";
		sql += " INFO_LS_RT_OP = '" + leaseRentalOutright + "',";
		sql += " QUANTITY = '" + quantity + "',";
		sql += " REMARKS = '" + remarks + "',";
		sql += " COMMENT1 = '" + comment + "',";
		sql += " RFP_STATUS = '1',";
		sql += " RFP_USER_ID = '" + updateUser + "',";
		sql += " RFP_UPDATE_DATE = to_date(SYSDATE,'yyyy/MM/dd'),";
		sql += " RFP_SUBSIDIARY_CODE = '" + subsidiaryCode + "',";

		sql += " UPDATE_DATE = to_date(SYSDATE,'yyyy/MM/dd'), ";
		sql += " UPDATE_USER = '" + updateUser + "'";
		sql += " WHERE CUSTOMER_ID  ='" + customerID + "'";
		sql += " AND RFP_NO  ='" + rfpNo + "'";
		System.out.println("[INFO] sql = " + sql);
		conn = this.datasource.getConnection();
		st = conn.createStatement();
		rset = st.executeQuery(sql0.toString());
		try {
			boolean isExist = false;
			//check the record to update is exist
			while (rset.next()) {
				if (!BaseCommonCheck.isEmpty(rset.getString("COUNT")) && !rset.getString("COUNT").equals("0")) {
					isExist = true;
				} else {
					messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
					returnValueD.setBussinessError();
				}
			}
			if (isExist) {
				PreparedStatement pstmt = conn.prepareStatement(sql);
				int bresult = 0;
				bresult = pstmt.executeUpdate();
				if (bresult > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
				returnValueD.setDataValue(new Integer(bresult));
			}
		} catch (Exception exception) {
			System.out.println("[Error Happen!]");
			System.out.println("[Start Trace]");
			exception.printStackTrace();
			System.out.println("[End Trace]");
			throw exception;
		} finally {
			try {
				if (rset != null) {
					rset.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				return returnValueD;
			}
		}
		return returnValueD;
	}

}