requestprocessor.java

Java写的ERP系统

		String sql = "SELECT * FROM W_Request "
			+ "WHERE AD_Client_ID=? AND Processed <> 'Y'";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, vo.AD_Client_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int AD_Client_ID = rs.getInt("AD_Client_ID");
				if (AD_Client_ID == 0 || AD_Client_ID == vo.AD_Client_ID)
				{
					int C_BPartner_ID = rs.getInt("C_BPartner_ID");
					int C_BPartner_Contact_ID = rs.getInt("C_BPartner_Contact_ID");
					if (C_BPartner_ID == 0 && C_BPartner_Contact_ID == 0)
					{
						int[] result = getBPartner (rs);
						C_BPartner_ID = result[0];
						C_BPartner_Contact_ID = result[1];
					}
					processWebRequest (vo, rs, C_BPartner_ID, C_BPartner_Contact_ID);
				}
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error("runWeb", e);
		}
	}   //  runWeb

	/**
	 * 	Find BPartner from WebRequest.
	 * 	@param rs ResultSet
	 * 	@return C_BPartner_ID/C_BPartner_Contact_ID
	 * 	@throws SQLException
	 */
	private int[] getBPartner (ResultSet rs) throws SQLException
	{
		int[] retValue = new int[] {0,0};

		//	Try to find it by email address
		int AD_Client_ID = rs.getInt("AD_Client_ID");
		String EMail = rs.getString("EMail");
		if (EMail != null)
		{
			String sql = "SELECT C_BPartner_ID, C_BPartner_Contact_ID "
				+ "FROM C_BPartner_Contact "
				+ "WHERE AD_Client_ID=? AND EMail=?";
			PreparedStatement pstmt = null;
			try
			{
				pstmt = DB.prepareStatement(sql);
				pstmt.setInt(1, AD_Client_ID);
				pstmt.setString(2, EMail);
				ResultSet rsx = pstmt.executeQuery();
				if (rsx.next())
				{
					retValue[0] = rs.getInt(1);
					retValue[1] = rs.getInt(2);
				}
				rsx.close();
				pstmt.close();
				pstmt = null;
			}
			catch (Exception e)
			{
				log.error("getBPartner", e);
			}
			finally
			{
				try
				{
					if (pstmt != null)
						pstmt.close ();
				}
				catch (Exception e)
				{}
				pstmt = null;
			}
		}
		if (retValue[0] != 0 && retValue[1] != 0)
			return retValue;

		return retValue;
	}	//	createBPartner


	/**
	 *  Process Web Request Record.
	 *  - create record
	 *  - assign based on rule
	 *
	 *  @param vo value object
	 *  @param rs W_Request
	 * 	@param C_BPartner_ID bp
	 * 	@param C_BPartner_Contact_ID contact
	 *  @throws SQLException
	 */
	private void processWebRequest (RequestProcessorVO vo, ResultSet rs,
		int C_BPartner_ID, int C_BPartner_Contact_ID)
		throws SQLException
	{
		int W_Request_ID = rs.getInt("W_Request_ID");

		StringBuffer sql = new StringBuffer ("INSERT INTO R_REQUEST "
			+ "(R_Request_ID,AD_Client_ID,AD_Org_ID, "
			+ "IsActive,Created,CreatedBy,Updated,UpdatedBy, "
			+ "DocumentNo,R_RequestType_ID,RequestAmt, "
			+ "Priority,DueType,Summary, "
			+ "IsEscalated,AD_User_ID,W_Request_ID, "
			+ "C_BPartner_ID,C_BPartner_Contact_ID, "
			+ "Result,Processed) VALUES (");
		//  (R_Request_ID, AD_Client_ID, AD_Org_ID,
		int R_Request_ID = DB.getKeyNextNo(vo.AD_Client_ID, "N", "R_Request");
		sql.append(R_Request_ID).append(",").append(vo.AD_Client_ID).append(",0, ");
		//  IsActive, Created, CreatedBy, Updated, UpdatedBy,
		sql.append("'Y',SysDate,0,SysDate,0, ");
		//  DocumentNo, R_RequestType_ID, RequestAmt,
		String docNo = DB.getDocumentNo(vo.AD_Client_ID, "N", "R_Request");
		sql.append(DB.TO_STRING(docNo)).append(",");
		int R_RequestType_ID = getRequestTypeID(vo, rs.getInt("R_RequestType_ID"));
		if (R_RequestType_ID == 0)
			sql.append("null");
		else
			sql.append(R_RequestType_ID);
		sql.append(",0, ");
		//  Priority, DueType, Summary,
		String Question = rs.getString("Question");
		if (Question == null)
			Question = " ";
		sql.append("'5','5',").append(DB.TO_STRING(Question, 2000)).append(", ");
		//  IsEscalated, AD_User_ID, W_Request_ID,
		int AD_User_ID = findUser (vo, R_RequestType_ID, Question);
		sql.append("'N',").append(AD_User_ID).append(",").append(W_Request_ID).append(", ");
		//  C_BPartner_ID, C_BPartner_Contact_ID,
		if (C_BPartner_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_BPartner_ID).append(",");
		if (C_BPartner_Contact_ID == 0)
			sql.append("NULL, ");
		else
			sql.append(C_BPartner_Contact_ID).append(", ");
		//  Result, Processed)
		sql.append("'Web','N')");

		//  Save
		int no = DB.executeUpdate(sql.toString());
		if (no == 1)
		{
			//  Set to processed and change Client for visibility
			sql = new StringBuffer ("UPDATE W_Request SET Processed='Y',AD_Client_ID=");
			sql.append(vo.AD_Client_ID).append(" WHERE W_Request_ID=").append(W_Request_ID);
			no = DB.executeUpdate(sql.toString());
			if (no != 1)
				log.error("processWebRequests - Web NOT updated #=" + no);
		}
		else
			log.error("processWebRequests - Request NOT created #=" + no);
	}   //  processWebRequesr

	/**
	 *  Find User based on Request Type and Question.
	 *
	 *  @param vo value object
	 *  @param R_RequestType_ID request type
	 *  @param Question info
	 *  @return AD_User_ID user
	 */
	private int findUser (RequestProcessorVO vo, int R_RequestType_ID, String Question)
	{
		String sql = "SELECT  R_RequestType_ID, Keyword, AD_User_ID "
			+ "FROM R_RequestProcessor_Route "
			+ "WHERE R_RequestProcessor_ID=?"
			+ " AND IsActive='Y' "
			+ "ORDER BY SeqNo";

		int AD_User_ID = 0;
		String Q = Question.toUpperCase();
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, vo.R_RequestProcessor_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next() && AD_User_ID == 0)
			{
				if (R_RequestType_ID != 0 && rs.getInt(1) == R_RequestType_ID)
					AD_User_ID = rs.getInt(3);
				else
				{
					String keyword = rs.getString(2);
					if (keyword != null && Q.indexOf(keyword.toUpperCase()) != -1)
						AD_User_ID = rs.getInt(3);
				}
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.error("findUser", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		//	return user
		if (AD_User_ID != 0)
			return AD_User_ID;
		return vo.Supervisor_ID;
	}   //  findUser

	/**
	 *  Get (default) RequestType.
	 *
	 *  @param vo value object
	 *  @param R_RequestType_ID initial value
	 *  @return R_RequestType_ID or default
	 */
	private int getRequestTypeID (RequestProcessorVO vo, int R_RequestType_ID)
	{
		if (R_RequestType_ID > 0)
			return R_RequestType_ID;

		//	Get Default
		String sql = "SELECT R_RequestType_ID "
			+ "FROM R_RequestType "
			+ "WHERE AD_Client_ID=? AND IsDefault='Y'";
		int retValue = 0;
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, vo.AD_Client_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getInt(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error("getRequestTypeID", e);
		}
		return retValue;
	}   //  getRequestTypeID

	/*************************************************************************/

	/**
	 *
	 *  @param vo Value Object
	 */
	private void runEMail (RequestProcessorVO vo)
	{
	}   //  runWeb

	/*************************************************************************/

	/**
	 *  Test Run
	 *  @param args ignored
	 */
	public static void main(String[] args)
	{
		org.compiere.Compiere.startupClient ();
		RequestProcessorVO[] vo = RequestProcessorVO.get();
		if (vo != null && vo.length > 0)
			new RequestProcessor (vo[0]);
	}   //  main

}   //  RequestProcessor