mlookupfactory.java

Java写的ERP系统

/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is                  Compiere  ERP & CRM  Business Solution
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
 * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.model;

import java.util.*;
import java.sql.*;

import org.apache.log4j.Logger;

import org.compiere.util.*;

/**
 *  Create MLookups
 *
 *  @author Jorg Janke
 *  @version  $Id: MLookupFactory.java,v 1.4 2003/04/30 06:25:21 jjanke Exp $
 */
public class MLookupFactory
{
	/**	Logging								*/
	private static Logger				s_log = Logger.getLogger(MLookupFactory.class);

	/**
	 *  Create MLookup Info
	 *
	 *  @param ctx context
	 *  @param AD_Column_ID column
	 *  @param WindowNo window no
	 *  @param displayType display type
	 *	@param	isProcess if true, AD_Process_Para is queried not AD_Column
	 *  and AD_Process_Para_ID is used instead of AD_Column_ID
	 *  @throws Exception if Lookup could not be created
	 *  @return MLookupInfo
	 */
	public static MLookupInfo createVO (Properties ctx, int AD_Column_ID, int WindowNo, int displayType, boolean isProcess)
		throws Exception
	{
		Language language = Language.getLanguage(Env.getAD_Language(ctx));
		MLookupInfo info = getLookup_Info(ctx, language, WindowNo, AD_Column_ID, isProcess);
		info.ctx = ctx;
		info.WindowNo = WindowNo;
		info.AD_Column_ID = AD_Column_ID;
		info.IsProcess = isProcess;
		info.DisplayType = displayType;
		return info;
	}   //  createVO

	/**
	 *  Create MLookup
	 *
	 *  @param ctx context
	 *  @param AD_Column_ID column
	 *  @param WindowNo window no
	 *  @param displayType display type
	 *	@param	isProcess if true, AD_Process_Para is queried not AD_Column
	 *  and AD_Process_Para_ID is used instead of AD_Column_ID
	 *  @throws Exception if Lookup could not be created
	 *  @return MLookup
	 */
	public static MLookup create (Properties ctx, int AD_Column_ID, int WindowNo, int displayType, boolean isProcess)
		throws Exception
	{
		MLookupInfo info = createVO (ctx, AD_Column_ID, WindowNo, displayType, isProcess);
		return new MLookup(info, 0);
	}   //  create

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

	/**
	 *  Get Information for Lookups based on Column_ID for Table Columns or Process Parameters.
	 *
	 *	The SQL returns three columns:
	 *  <pre>
	 *		Key, Value, Name	(where either key or value is null)
	 *  </pre>
	 *  @param ctx context for access
	 *  @param language report language
	 *  @param WindowNo window no
	 *  @param Column_ID AD_Column_ID or if isProcess is true AD_Process_Para_ID
	 *  @param isProcess if true, query AD_Process_Para rather than AD_Column
	 *  @return lookup info structure
	 */
	static public MLookupInfo getLookup_Info (Properties ctx, Language language,
		int WindowNo, int Column_ID, boolean isProcess)
	{
		//	We can cache the LookupInfo, if there is no dynamic Validation(!)

		//	Get Column Info
		String sql = "SELECT c.ColumnName,c.AD_Reference_ID,c.AD_Reference_Value_ID,c.IsParent,v.Code "
			+ "FROM AD_Column c,AD_Val_Rule v "
			+ "WHERE c.AD_Val_Rule_ID=v.AD_Val_Rule_ID(+)"
			+ " AND c.AD_Column_ID=?";
		if (isProcess)
			sql = "SELECT p.ColumnName,p.AD_Reference_ID,p.AD_Reference_Value_ID,'N',v.Code "
				+ "FROM AD_Process_Para p,AD_Val_Rule v "
				+ "WHERE p.AD_Val_Rule_ID=v.AD_Val_Rule_ID(+)"
				+ " AND p.AD_Process_Para_ID=?";
		//
		String ColumnName = null;
		int AD_Reference_ID = 0;            //  data type
		int AD_Reference_Value_ID = 0;      //
		boolean isParent = false;
		String validation = "";
		boolean loaded = false;

		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, Column_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				ColumnName = rs.getString(1);
				AD_Reference_ID = rs.getInt(2);
				AD_Reference_Value_ID = rs.getInt(3);
				isParent = rs.getString(4).equals("Y");
				validation = rs.getString(5);
				if (validation == null)
					validation = "";
				loaded = true;
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("getLookup_Info", e);
			return null;
		}
		if (!loaded)
		{
			s_log.error("getLookup_Info - No data for Column_ID=" + Column_ID);
			return null;
		}

		MLookupInfo info = null;
		//	List
		if (AD_Reference_ID == DisplayType.List)	//	17
		{
			info = getLookup_List(language, AD_Reference_Value_ID);
		}
		//	Table or Search with Reference_Value
		else if ((AD_Reference_ID == DisplayType.Table || AD_Reference_ID == DisplayType.Search)
			&& AD_Reference_Value_ID != 0)
		{
			info = getLookup_Table (ctx, language, WindowNo, AD_Reference_Value_ID);
		}
		//	TableDir, Search, ID, ...
		else
		{
			info = getLookup_TableDir(ctx, language, ColumnName);
		}
		//  do we have basic info?
		if (info == null)
		{
			s_log.error ("getLookup_Info - No SQL - " + ColumnName);
			return null;
		}
		info.IsParent = isParent;
		info.Validation = validation;

		//	Variables in SQL WHERE
		if (info.Query.indexOf("@") != -1)
		{
			String newSQL = Env.parseContext(ctx, WindowNo, info.Query, false);
			if (newSQL.length() == 0)
			{
				s_log.error ("getLookup_Info - SQL parse error: " + info.Query);
				return null;
			}
			info.Query = newSQL;
		}

		//	Validation
		validation = "";
		if (info.Validation.length() == 0)
			info.IsValidated = true;
		else
		{
			validation = Env.parseContext (ctx, WindowNo, info.Validation, true);
			if (validation.length() == 0)   //  returns "" if not all variables were parsed
				info.IsValidated = false;
			else
				info.IsValidated = true;
		}
		int posOrder = info.Query.lastIndexOf(" ORDER BY ");

		//	Add Validation
		if (validation.length() != 0)
		{
			info.Query = info.Query.substring(0, posOrder)
				+ " AND " + validation
				+ info.Query.substring(posOrder);
			//	Create m_SQL for direct access
			info.QueryDirect = info.Query.substring(0, posOrder)
				+ " AND " + validation
				+ " AND " + info.KeyColumn + "=?";
		}
		else
			info.QueryDirect = info.Query.substring(0, posOrder)
				+ " AND " + info.KeyColumn + "=?";
		//
		return info;
	}	//	getLookup_Info

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

	/**
	 *	Get Lookup SQL for Lists
	 *  @param language report language
	 *  @param AD_Reference_Value_ID reference value
	 *	@return SELECT NULL, Value, Name, IsActive FROM AD_Ref_List
	 */
	static public MLookupInfo getLookup_List(Language language, int AD_Reference_Value_ID)
	{
		StringBuffer realSQL = new StringBuffer ("SELECT NULL, AD_Ref_List.Value,");
		if (Env.isBaseLanguage(language, "AD_Ref_List"))
			realSQL.append("AD_Ref_List.Name,AD_Ref_List.IsActive FROM AD_Ref_List");
		else
			realSQL.append("trl.Name, AD_Ref_List.IsActive "
				+ "FROM AD_Ref_List INNER JOIN AD_Ref_List_Trl trl "
				+ " ON (AD_Ref_List.AD_Ref_List_ID=trl.AD_Ref_List_ID AND trl.AD_Language='")
					.append(language.getAD_Language()).append("')");
		realSQL.append(" WHERE AD_Ref_List.AD_Reference_ID=").append(AD_Reference_Value_ID);
		realSQL.append(" ORDER BY 2");
		//
		return new MLookupInfo(realSQL.toString(), "AD_Ref_List.Value",
			101, MQuery.getEqualQuery("AD_Reference_ID", AD_Reference_Value_ID));	//	Zoom Window+Query
	}	//	getLookup_List

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

	/**
	 *	Get Lookup SQL for Table Lookup
	 *  @param ctx context for access and dynamic access
	 *  @param language report language
	 *  @param WindowNo window no
	 *  @param AD_Reference_Value_ID reference value
	 *	@return	SELECT Key, NULL, Name, IsActive FROM Table - if KeyColumn end with _ID
	 *	  otherwise	SELECT NULL, Key, Name, IsActive FROM Table
	 */
	static public MLookupInfo getLookup_Table (Properties ctx, Language language,
		int WindowNo, int AD_Reference_Value_ID)
	{
		String sql = "SELECT t.TableName,ck.ColumnName AS KeyColumn,"
			+ "cd.ColumnName AS DisplayColumn,rt.isValueDisplayed,cd.IsTranslated,"
			+ "rt.WhereClause,rt.OrderByClause,t.AD_Window_ID "
			+ "FROM AD_Ref_Table rt"
			+ " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID)"
			+ " INNER JOIN AD_Column ck ON (rt.AD_Key=ck.AD_Column_ID)"
			+ " INNER JOIN AD_Column cd ON (rt.AD_Display=cd.AD_Column_ID) "
			+ "WHERE rt.AD_Reference_ID=?"
			+ " AND rt.IsActive='Y' AND t.IsActive='Y'";
		//
		String	KeyColumn = null, DisplayColumn = null, TableName = null, WhereClause = null, OrderByClause = null;
		boolean IsTranslated = false, isValueDisplayed = false;
		int ZoomWindow = 0;
		boolean loaded = false;

		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, AD_Reference_Value_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				TableName = rs.getString(1);
				KeyColumn = rs.getString(2);
				DisplayColumn = rs.getString(3);
				isValueDisplayed = "Y".equals(rs.getString(4));
				IsTranslated = "Y".equals(rs.getString(5));
				WhereClause = rs.getString(6);
				OrderByClause = rs.getString(7);
				ZoomWindow = rs.getInt(8);
				loaded = true;
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("getLookup_Table", e);
			return null;
		}
		if (!loaded)
		{
			s_log.error("getLookup_Table - No Table Reference Table ID=" + AD_Reference_Value_ID);
			return null;
		}

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

		//	Translated
		if (IsTranslated && !Env.isBaseLanguage(language, TableName))
		{
			realSQL.append(TableName).append(".").append(KeyColumn).append(",");
			if (KeyColumn.endsWith("_ID"))
				realSQL.append("NULL,");
			if (isValueDisplayed)
				realSQL.append(TableName).append(".Value || '-' || ");
			realSQL.append(TableName).append("_Trl.").append(DisplayColumn)
				.append(",").append(TableName).append(".IsActive");
			realSQL.append(" FROM ").append(TableName)
				.append(" INNER JOIN ").append(TableName).append("_TRL ON (")
				.append(TableName).append(".").append(KeyColumn)
				.append("=").append(TableName).append("_Trl.").append(KeyColumn)
				.append(" AND ").append(TableName).append("_Trl.AD_Language='")
				.append(language.getAD_Language()).append("')");
		}
		//	Not Translated
		else
		{
			realSQL.append(TableName).append(".").append(KeyColumn).append(",");
			if (KeyColumn.endsWith("_ID"))
				realSQL.append("NULL,");
			if (isValueDisplayed)
				realSQL.append(TableName).append(".Value || '-' || ");
			realSQL.append(TableName).append(".").append(DisplayColumn);
			realSQL.append(",").append(TableName).append(".IsActive");
			realSQL.append(" FROM ").append(TableName);
		}

		//	add WHERE clause
		if (WhereClause != null)
		{
			String where = WhereClause;
			if (where.indexOf("&") != -1)
				where = Env.parseContext(ctx, WindowNo, where, false);
			if (where.length() == 0 && WhereClause.length() != 0)
				s_log.error ("getLookup_Table - Could not resolve: " + WhereClause);