utility.java

Java写的ERP系统

package org.compiere.jsp;

import java.sql.*;
import java.math.*;

import org.compiere.util.DisplayType;

/**
 *	Utility Methods
 * 	<P>
 * 	@author Jorg Janke
 */
public class Utility
{
	/*	Does the out method produce output?	*/
	protected static boolean outEnabled = true;

	/**
	 *	Print message with level 1
	 */
	protected static void out(String message)
	{
		if (outEnabled)
			out(1, message);
	}	//	out

	/**
	 * 	Print message indented by level
	 */
	protected static void out (int level, String message)
	{
		if (outEnabled)
		{
			if (level < 0)
				level = 0;
			else if (level > 10)
				level = 10;
			System.out.print("                        ".substring(0, level*2));
			System.out.println(message);
		}
	}	//	out

	/**************************************************************************
	 *	Connection
	 */
	protected static Connection getConnection()
	{
		return null;
	}	//	getConnection



	/**
	 *	Print SQL Exception
	 */
	protected static void printException(SQLException ex, String comment)
	{
		boolean temp = outEnabled;
		outEnabled = true;
		//	always print exceptions
		out(0, "SQL Exception: " + comment);
		while (ex != null)
		{
			StringBuffer buffer = new StringBuffer(ex.getMessage());
			buffer.append("; State=").append(ex.getSQLState()).append("; ErrorCode=").append(ex.getErrorCode());
			out(1, buffer.toString());
			ex = ex.getNextException();
		}
		outEnabled = temp;
	}	//	printException

	/**
	 *	Print SQL Warnings
	 *		Usage: Utility.printWarning(rs.getWarnings(), "xx");
	 */
	protected static void printWarning(SQLWarning warn, String comment)
	{
		if (warn == null || !outEnabled)
			return;
		//
		out(1, "SQL Warning " + comment);
		while (warn != null)
		{
			StringBuffer buffer = new StringBuffer(warn.getMessage());
			buffer.append("; State=").append(warn.getSQLState()).append("; ErrorCode=").append(warn.getErrorCode());
			out(1, buffer.toString());
			warn = warn.getNextWarning();
		}
	}	//	printWarning



	/**************************************************************************
	 *	Return SQL for Lookup
	 *	@param	AD_Column_ID	column with lookup info
	 *	@param	language		language, e.g. GERGER, default: USAENG
	 *
	 *	The SQL needs to return tree columns:
	 *		Key, Value, Name	(where either key or value is null)
	 */
	public static String getLookupSQL(int AD_Column_ID, String language, Object info)
	{
		//	Get Column Info
		String SQL = "SELECT ColumnName,AD_Reference_ID,AD_Reference_Value_ID "
			+ "FROM AD_Column WHERE AD_Column_ID=?";
		int AD_Reference_ID = 0, AD_Reference_Value_ID = 0;
		String ColumnName;

		try
		{
			PreparedStatement pstmt = getConnection().prepareStatement(SQL);
			pstmt.setInt(1, AD_Column_ID);
			ResultSet rs = pstmt.executeQuery();
			if (!rs.next())
			{
				rs.close();
				pstmt.close();
				out(0, "Utility.getLookupSQL - No data for AD_Column_ID=" + AD_Column_ID);
				return "";
			}

			ColumnName = rs.getString(1);
			AD_Reference_ID = rs.getInt(2);
			AD_Reference_Value_ID = rs.getInt(3);

			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			printException(e, "Utility.getLookupSQL");
			return "";
		}

		//	Default Language
		if (language == null || language.equals(""))
			language = "USAENG";

		//	List
		if (AD_Reference_ID == DisplayType.List)	//	17
			SQL = getLookup_List(AD_Reference_Value_ID, language);
		//	Table
		else if (AD_Reference_ID == DisplayType.Table)
			SQL = getLookup_Table(AD_Reference_Value_ID, language);
		//	TableDir, Search, ID, ...
		else
			SQL = getLookup_TableDir(ColumnName, language);

		return SQL;
	}	//	getLookupSQL

	/**
	 *	Get Lookup SQL for Lists
	 *	@returns SELECT NULL, Value, Name FROM AD_Ref_List
	 */
	private static String getLookup_List(int AD_Reference_Value_ID, String language)
	{
		StringBuffer realSQL = new StringBuffer ("SELECT NULL, l.Value,");
		if (language.equals("USAENG"))
			realSQL.append("l.Name FROM AD_Ref_List l WHERE ");
		else
			realSQL.append("trl.Name FROM AD_Ref_List l, AD_Ref_List_Trl trl"
				+ " WHERE l.AD_Ref_List_ID=trl.AD_Ref_List_ID AND ");
		realSQL.append("l.AD_Reference_ID=").append(AD_Reference_Value_ID);
		realSQL.append(" AND l.IsActive='Y'");
		realSQL.append(" ORDER BY 2");
		//
		return realSQL.toString();
	}	//	getLookup_List


	/**
	 *	Get Lookup SQL for Table Lookup
	 *	@returns	SELECT Key, NULL, Name FROM Table - if KeyColumn end with _ID
	 *	  otherwise	SELECT NULL, Key, Name FROM Table
	 */
	private static String getLookup_Table(int AD_Reference_Value_ID, String language)
	{
		String SQL = "SELECT t.TableName, ck.ColumnName AS KeyColumn,"
			+ " cd.ColumnName AS DisplayColumn, cd.DisplaySQL, cd.IsTranslated,"
			+ " rt.WhereClause, rt.OrderByClause" //	t.AD_Window_ID, t.AD_Table_ID
			+ "FROM AD_Ref_Table rt, AD_Table t, AD_Column ck, AD_Column cd "
			+ "WHERE rt.AD_Reference_ID=?"
			+ " AND rt.AD_Table_ID = t.AD_Table_ID"
			+ " AND rt.AD_Key = ck.AD_Column_ID"
			+ " AND rt.AD_Display = cd.AD_Column_ID"
			+ " AND rt.IsActive = 'Y' AND t.IsActive = 'Y'";
		//
		String	TableName, KeyColumn, DisplayColumn, DisplaySQL, WhereClause, OrderByClause;
		boolean IsTranslated;

		try
		{
			PreparedStatement pstmt = getConnection().prepareStatement(SQL);
			pstmt.setInt(1, AD_Reference_Value_ID);
			ResultSet rs = pstmt.executeQuery();
			if (!rs.next())
			{
				out(0, "Utility.getLookup_Table Cannot find Reference Table, ID=" + AD_Reference_Value_ID);
				rs.close();
				pstmt.close();
				return "";
			}

			TableName = rs.getString(1);
			KeyColumn = rs.getString(2);
			DisplayColumn = rs.getString(3);
			DisplaySQL = rs.getString(4);
			IsTranslated = rs.getString(5).equals("Y");
			WhereClause = rs.getString(6);
			OrderByClause = rs.getString(7);

			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			printException(e, "Utility.getLookup_Table");
			return "";
		}

		StringBuffer realSQL = new StringBuffer("SELECT ");
		if (!KeyColumn.endsWith("_ID"))
			realSQL.append("NULL,");

		//	Translated
		if (IsTranslated && !language.equals("USAENG"))
		{
			realSQL.append(TableName).append(".").append(KeyColumn).append(",");
			if (KeyColumn.endsWith("_ID"))
				realSQL.append("NULL,");
			realSQL.append(TableName).append("_Trl.").append(DisplayColumn);
			realSQL.append(" FROM ").append(TableName).append(",").append(TableName).append("_Trl");
			realSQL.append(" WHERE ").append(TableName).append(".").append(KeyColumn);
			realSQL.append("=").append(TableName).append("_Trl.").append(KeyColumn);
			realSQL.append(" AND ");
		}
		//	Not Translated
		else
		{
			realSQL.append(TableName).append(".").append(KeyColumn).append(",");
			if (KeyColumn.endsWith("_ID"))
				realSQL.append("NULL,");
			if (DisplaySQL != null)
				realSQL.append(DisplaySQL).append(" AS ");
			else
				realSQL.append(TableName).append(".");
			realSQL.append(DisplayColumn);
			realSQL.append(" FROM ").append(TableName).append(" WHERE ");
		}

		//	add WHERE clause
		if (WhereClause != null)
		{
			//	We have no context
			if (WhereClause.indexOf("@") == -1)
			{
				realSQL.append(WhereClause).append(" AND ");
				if (WhereClause.indexOf(".") == -1)
				out(1, "Utility.getLookup_Table - " + TableName
					+ ": WHERE must fully qualified: " + WhereClause);
			}
		}

		//	Always restrict to active elements
		realSQL.append(TableName).append(".IsActive='Y'");

		//	Order By qualified term or by Name
		if (OrderByClause != null)
		{
			realSQL.append(" ORDER BY ").append(OrderByClause);
			if (OrderByClause.indexOf(".") == -1)
				out(1, "Utility.getLookup_Table - " + TableName
					+ ": ORDER BY must fully qualified: " + OrderByClause);
		}
		else
			realSQL.append(" ORDER BY 2");

		return realSQL.toString();
	}	//	getLookup_Table


	/**
	 *	Get Lookup SQL for direct Table Lookup
	 *	@returns SELECT Key, NULL, Name from Table
	 */
	private static String getLookup_TableDir(String ColumnName, String language)
	{
		if (!ColumnName.endsWith("_ID"))
		{
			out(0, "Ultity.getLookup_TableDir - Key does not end with '_ID': " + ColumnName);
			return "";
		}

		String TableName = ColumnName.substring(0,ColumnName.length()-3);

		//	get display column name (first identifier column)
		String SQL = "SELECT c.ColumnName,c.IsTranslated "	//	t.AD_Window_ID,t.AD_Table_ID
			+ "FROM AD_Table t, AD_Column c "
			+ "WHERE TableName=?"
			+ " AND t.AD_Table_ID=c.AD_Table_ID"
			+ " AND c.IsIdentifier='Y' "
			+ "ORDER BY c.SeqNo";
		//
		String KeyColumn = ColumnName;
		String DisplayColumn;
		boolean IsTranslated;
		//
		try
		{
			PreparedStatement pstmt = getConnection().prepareStatement(SQL);
			pstmt.setString(1, TableName);
			ResultSet rs = pstmt.executeQuery();
			if (!rs.next())
			{
				out(0, "Ultity.getLookup_TableDir - No Identifier records found: " + ColumnName);
				return "";
			}

			DisplayColumn = rs.getString(1);
			IsTranslated = rs.getString(2).equals("Y");

			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			printException(e, "Ultity.getLookup_TableDir");
			return "";
		}

		StringBuffer realSQL = new StringBuffer("SELECT ");
		realSQL.append(TableName).append(".").append(KeyColumn).append(",NULL,");

		if (IsTranslated && !language.equals("USAENG"))
		{
			realSQL.append(TableName).append("_Trl.").append(DisplayColumn);
			realSQL.append(" FROM ").append(TableName).append(",").append(TableName).append("_Trl");
			realSQL.append(" WHERE ").append(TableName).append(".").append(KeyColumn);
			realSQL.append("=").append(TableName).append("_Trl.").append(KeyColumn).append(" AND ");
		}
		else	//	no translation
		{
			realSQL.append(TableName).append(".").append(DisplayColumn);
			realSQL.append(" FROM ").append(TableName).append(" WHERE ");
		}

		//	Additional Where Clause
		realSQL.append(TableName).append(".IsActive='Y'");

		//	Order by Display
		realSQL.append(" ORDER BY 2");

		return realSQL.toString();
	}	//	getLookup_TableDir

}	//	Utility