/******************************************************************************
 * Product: Adempiere ERP & CRM Smart Business Solution                       *
 * Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved.                *
 * This program is free software; you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program; if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 * For the text or an alternative of this public license, you may reach us    *
 * ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.compiere.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CCache;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.Env;
import org.compiere.util.Language;

/**
 * Factory to create MLookup instance.
 *
 * @author Jorg Janke
 * @version  $Id: MLookupFactory.java,v 1.3 2006/07/30 00:58:04 jjanke Exp $
 *
 * @author Teo Sarca, SC ARHIPAC SERVICE SRL
 *		<li>BF [ 1734394 ] MLookupFactory.getLookup_TableDirEmbed is not translated
 *		<li>BF [ 1714261 ] MLookupFactory: TableDirEmbed -&gt; TableEmbed not supported
 *		<li>BF [ 1672820 ] Sorting should be language-sensitive
 *		<li>BF [ 1739530 ] getLookup_TableDirEmbed error when BaseColumn is sql query
 *		<li>BF [ 1739544 ] getLookup_TableEmbed error for self referencing references
 *		<li>BF [ 1817768 ] Isolate hardcoded table direct columns
 * @author Teo Sarca
 * 		<li>BF [ 2933367 ] Virtual Column Identifiers are not working
 * 			https://sourceforge.net/p/adempiere/bugs/2291/
 * @author Carlos Ruiz, GlobalQSS
 *		<li>BF [ 2561593 ] Multi-tenant problem with webui
 */
public class MLookupFactory
{
	/**	Logging								*/
	private static CLogger		s_log = CLogger.getCLogger(MLookupFactory.class);
	/** Table Reference Cache				*/
	private static CCache<String,MLookupInfo> s_cacheRefTable = new CCache<String,MLookupInfo>(I_AD_Ref_Table.Table_Name, 30, CCache.DEFAULT_EXPIRE_MINUTE);	//	1h
	/** List Reference Cache				*/
	private static CCache<String,MLookupInfo> s_cacheRefList = new CCache<String,MLookupInfo>(I_AD_Ref_List.Table_Name, 30, CCache.DEFAULT_EXPIRE_MINUTE);	//	1h

	/**
	 *  Create MLookup
	 *
	 *  @param ctx context for access
	 *  @param WindowNo window no
	 * 	@param AD_Reference_ID lookup display type
	 *  @param Column_ID AD_Column_ID or AD_Process_Para_ID
	 *  @param language language
	 * 	@param ColumnName key column name
	 * 	@param AD_Reference_Value_ID AD_Reference (List - AD_Ref_List, Table - AD_Ref_Table)
	 * 	@param IsParent parent (prevents query to directly access value)
	 * 	@param ValidationCode optional SQL validation/filter
	 *  @throws Exception if Lookup could not be created
	 *  @return MLookup
	 */
	public static MLookup get (Properties ctx, int WindowNo, int Column_ID, int AD_Reference_ID,
			Language language, String ColumnName, int AD_Reference_Value_ID,
			boolean IsParent, String ValidationCode)
		throws Exception
	{
		MLookupInfo info = getLookupInfo (ctx, WindowNo, Column_ID, AD_Reference_ID,
			language, ColumnName, AD_Reference_Value_ID, IsParent, ValidationCode);
		if (info == null)
			throw new Exception ("MLookup.create - no LookupInfo");
		return new MLookup(info, 0);
	}   //  create

	/**
	 * @param ctx
	 * @param WindowNo
	 * @param Column_ID
	 * @param AD_Reference_ID
	 * @return MLookupInfo
	 */
	public static MLookupInfo getLookupInfo(Properties ctx, int WindowNo, int Column_ID, int AD_Reference_ID)
	{
		return 	getLookupInfo(ctx, WindowNo, 0, Column_ID, AD_Reference_ID);
	}
	
	/**
	 * @param ctx
	 * @param WindowNo
	 * @param TabNo
	 * @param Column_ID
	 * @param AD_Reference_ID
	 * @return MLookupInfo
	 */
	public static MLookupInfo getLookupInfo(Properties ctx, int WindowNo, int TabNo, int Column_ID, int AD_Reference_ID)
	{
		MColumn column = MColumn.get(ctx, Column_ID);
		if (column.get_ID() == 0)
			s_log.log(Level.SEVERE, "Column Not Found - AD_Column_ID=" + Column_ID);

		String ColumnName = column.getColumnName();
		int AD_Reference_Value_ID = column.getAD_Reference_Value_ID();
		boolean IsParent = column.isParent();
		String ValidationCode = "";

		if (column.getAD_Val_Rule_ID() > 0) {
			MValRule valRule = MValRule.get(ctx, column.getAD_Val_Rule_ID());
			ValidationCode = valRule.getCode();
		}
		//
		MLookupInfo info = getLookupInfo (ctx, WindowNo, TabNo, Column_ID, AD_Reference_ID,
			Env.getLanguage(ctx), ColumnName, AD_Reference_Value_ID, IsParent, ValidationCode);

		return info;
	}

	/**
	 *  Create MLookup
	 *
	 *  @param ctx context for access
	 *  @param WindowNo window no
	 * 	@param TabNo TabNo
	 *  @param Column_ID AD_Column_ID or AD_Process_Para_ID
	 * 	@param AD_Reference_ID display type
	 *  @return MLookup
	 */
	public static MLookup get (Properties ctx, int WindowNo, int TabNo, int Column_ID, int AD_Reference_ID)
	{
		//
		MLookupInfo info = getLookupInfo (ctx, WindowNo, TabNo, Column_ID, AD_Reference_ID);
		return new MLookup(info, TabNo);
	}   //  get

	/**
	 *  Get Information for Lookups based on Column_ID for Table Columns or Process Parameters.
	 *  @param ctx context for access
	 *  @param language report language
	 *  @param WindowNo window no
	 *  @param Column_ID AD_Column_ID or AD_Process_Para_ID
	 * 	@param ColumnName key column name
	 * 	@param AD_Reference_ID display type
	 * 	@param AD_Reference_Value_ID AD_Reference (List, Table)
	 * 	@param IsParent parent (prevents query to directly access value)
	 * 	@param ValidationCode optional SQL validation
	 *  @return lookup info structure
	 */
	static public MLookupInfo getLookupInfo (Properties ctx, int WindowNo,
		int Column_ID, int AD_Reference_ID,
		Language language, String ColumnName, int AD_Reference_Value_ID,
		boolean IsParent, String ValidationCode)
	{
		return getLookupInfo(ctx, WindowNo, 0,
				          Column_ID, AD_Reference_ID,
				          language, ColumnName, AD_Reference_Value_ID,
				          IsParent, ValidationCode);
	}	//	getLookupInfo
	
	/**
	 *  Get Information for Lookups based on Column_ID for Table Columns or Process Parameters.
	 *  @param ctx context for access
	 *  @param language report language
	 *  @param WindowNo window no
	 *  @param tabNo    tab no
	 *  @param Column_ID AD_Column_ID or AD_Process_Para_ID
	 * 	@param ColumnName key column name
	 * 	@param AD_Reference_ID display type
	 * 	@param AD_Reference_Value_ID AD_Reference (List, Table)
	 * 	@param IsParent parent (prevents query to directly access value)
	 * 	@param ValidationCode optional SQL validation
	 *  @return lookup info structure
	 */
	static public MLookupInfo getLookupInfo (Properties ctx, int WindowNo, int tabNo,
		int Column_ID, int AD_Reference_ID,
		Language language, String ColumnName, int AD_Reference_Value_ID,
		boolean IsParent, String ValidationCode)
	{
		MLookupInfo info = null;
		boolean needToAddSecurity = true;
		//	List
		if (DisplayType.isList(AD_Reference_ID))	//	17
		{
			info = getLookup_List(language, AD_Reference_Value_ID);
			needToAddSecurity = false;
		}
		//	Table or Search with Reference_Value
		else if ((AD_Reference_ID == DisplayType.Table || AD_Reference_ID == DisplayType.TableUU
			|| AD_Reference_ID == DisplayType.Search || AD_Reference_ID == DisplayType.SearchUU
			|| AD_Reference_ID == DisplayType.ChosenMultipleSelectionTable || AD_Reference_ID == DisplayType.ChosenMultipleSelectionSearch)
			&& AD_Reference_Value_ID != 0)
		{
			info = getLookup_Table (ctx, language, WindowNo, AD_Reference_Value_ID);
		}
		//	TableDir, Search, ID, ...
		else
		{
			info = getLookup_TableDir (ctx, language, WindowNo, ColumnName);
		}
		//  do we have basic info?
		if (info == null)
		{
			s_log.severe ("No SQL - " + ColumnName);
			return null;
		}
		//	remaining values
		info.ctx = ctx;
		info.WindowNo = WindowNo;
		info.tabNo = tabNo;
		info.Column_ID = Column_ID;
		info.DisplayType = AD_Reference_ID;
		info.AD_Reference_Value_ID = AD_Reference_Value_ID;
		info.IsParent = IsParent;
		info.ValidationCode = ValidationCode;
		if (info.ValidationCode == null)
			info.ValidationCode = "";

		//	Variables in SQL WHERE
		if (info.Query.indexOf('@') != -1)
		{
			String newSQL = Env.parseContext(ctx, 0, info.Query, false);	//	only global
			if (newSQL.length() == 0)
			{
				s_log.severe ("SQL parse error: " + info.Query);
				return null;
			}
			info.Query = newSQL;
			if (s_log.isLoggable(Level.FINE)) s_log.fine("getLookupInfo, newSQL ="+newSQL); //jz
		}

		//	Direct Query - NO Validation/Security
		if (info.QueryDirect.indexOf('@') != -1)
		{
			String newSQL = Env.parseContext(ctx, 0, info.QueryDirect, false);	//	only global
			if (newSQL.length() == 0)
			{
				s_log.severe ("SQL parse error: " + info.QueryDirect);
				return null;
			}
			info.QueryDirect = newSQL;
			if (s_log.isLoggable(Level.FINE)) s_log.fine("getLookupInfo, newSQL ="+newSQL); //jz
		}

		//	Validation
		//String local_validationCode = "";
		if (info.ValidationCode.length() == 0)
			info.IsValidated = true;

		else
		{
			info.IsValidated = false;
		}

		//	Add Security
		if (needToAddSecurity)
			info.Query = MRole.getDefault(ctx, false).addAccessSQL(info.Query,
				info.TableName, MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);

		return info;
	}	//	getLookupInfo

	/**
	 *	Get Lookup Info for List display type
	 *  @param language language
	 *  @param AD_Reference_Value_ID list reference id (AD_Ref_List)
	 *	@return MLookupInfo
	 */
	static public MLookupInfo getLookup_List(Language language, int AD_Reference_Value_ID)
	{
		String lang;
		if (language == null) {
			lang = Env.getAD_Language(Env.getCtx());
		} else {
			lang = language.getAD_Language();
		}
		StringBuilder key = new StringBuilder()
				.append(Env.getAD_Client_ID(Env.getCtx())).append("|")
				.append(lang).append("|")
				.append(String.valueOf(AD_Reference_Value_ID));
		MLookupInfo retValue = (MLookupInfo)s_cacheRefList.get(key.toString());
		if (retValue != null)
		{
			if (s_log.isLoggable(Level.FINEST)) s_log.finest("Cache: " + retValue);
			return retValue.cloneIt();
		}

		boolean orderByValue = MReference.get(AD_Reference_Value_ID).isOrderByValue();
		
		StringBuilder realSQL = new StringBuilder ("SELECT NULL, AD_Ref_List.Value,");
		MClient client = MClient.get(Env.getCtx());
		StringBuilder AspFilter = new StringBuilder();
        if ( client.isUseASP() ) {
        	AspFilter.append(" AND AD_Ref_List.AD_Ref_List_ID NOT IN ( ")
        			 .append(" SELECT li.AD_Ref_List_ID")
        			 .append(" FROM ASP_Ref_List li")
        			 .append(" INNER JOIN ASP_Level l ON ( li.ASP_Level_ID = l.ASP_Level_ID)")
        			 .append(" INNER JOIN ASP_ClientLevel cl on (l.ASP_Level_ID = cl.ASP_Level_ID)")
        			 .append(" INNER JOIN AD_Client c on (cl.AD_Client_ID = c.AD_Client_ID)")
        			 .append(" WHERE li.AD_Reference_ID=").append(AD_Reference_Value_ID)
        			 .append(" AND li.IsActive='Y'")
        			 .append(" AND c.AD_Client_ID=").append(client.getAD_Client_ID())
        			 .append(" AND li.ASP_Status='H')");
			
		}
		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 ")
				.append("FROM AD_Ref_List  INNER JOIN AD_Ref_List_Trl trl ")
				.append(" 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);
		
		String directSql = realSQL.toString() + " AND AD_Ref_List.Value=?";
				
		realSQL.append(AspFilter.toString());
		if (orderByValue)
			realSQL.append(" ORDER BY 2");
		else
			realSQL.append(" ORDER BY 3"); // sort by name/translated name - teo_sarca, [ 1672820 ]
		//
		MLookupInfo info = new MLookupInfo(realSQL.toString(), "AD_Ref_List", "AD_Ref_List.Value",
			101,101, MQuery.getEqualQuery("AD_Reference_ID", AD_Reference_Value_ID));	//	Zoom Window+Query
		info.QueryDirect = directSql;
		
		s_cacheRefList.put(key.toString(), info.cloneIt());
		
		return info;
	}	//	getLookup_List

	/**
	 * Get Lookup SQL for List (for use as embedded query in SELECT)
	 * @param language Language
	 * @param AD_Reference_Value_ID list reference id (AD_Ref_List)
	 * @param linkColumnName link column name
	 * @return SELECT Name FROM AD_Ref_List WHERE AD_Reference_ID=x AND Value=linkColumn
	 */
	static public String getLookup_ListEmbed(Language language,
		int AD_Reference_Value_ID, String linkColumnName)
	{
		StringBuilder realSQL = new StringBuilder ("SELECT ");
		if (Env.isBaseLanguage(language, "AD_Ref_List"))
			realSQL.append("AD_Ref_List.Name FROM AD_Ref_List ");
		else
			realSQL.append("trl.Name ")
				.append("FROM AD_Ref_List  INNER JOIN AD_Ref_List_Trl trl ")
				.append(" 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)
			.append(" AND AD_Ref_List.Value=").append(linkColumnName);

		//
		return realSQL.toString();
	}	//	getLookup_ListEmbed

	/**
	 *	Get Lookup Info for Table Lookup display type
	 *  @param ctx context for access and dynamic access
	 *  @param language report language
	 *  @param WindowNo window no
	 *  @param AD_Reference_Value_ID table reference id (AD_Ref_Table)
	 *	@return	MLookupInfo
	 */
	static private MLookupInfo getLookup_Table (Properties ctx, Language language,
		int WindowNo, int AD_Reference_Value_ID)
	{
		String lang;
		if (language == null) {
			lang = Env.getAD_Language(Env.getCtx());
		} else {
			lang = language.getAD_Language();
		}
		StringBuilder key = new StringBuilder()
				.append(Env.getAD_Client_ID(ctx)).append("|")
				.append(Env.getAD_Role_ID(ctx)).append("|")
				.append(Env.getAD_User_ID(ctx)).append("|")
				.append(lang).append("|")
				.append(String.valueOf(AD_Reference_Value_ID));
		MLookupInfo retValue = (MLookupInfo)s_cacheRefTable.get(key.toString());
		if (retValue != null)
		{
			if (s_log.isLoggable(Level.FINEST)) s_log.finest("Cache: " + retValue);
			return retValue.cloneIt();
		}
		//
		String sql0 = "SELECT t.TableName,ck.ColumnName AS KeyColumn,"				//	1..2
			+ "cd.ColumnName AS DisplayColumn,rt.IsValueDisplayed,cd.IsTranslated,"	//	3..5
			+ "rt.WhereClause,rt.OrderByClause,t.AD_Window_ID,t.PO_Window_ID, "		//	6..9
			+ "t.AD_Table_ID, cd.ColumnSQL as DisplayColumnSQL, "					//	10..11
			+ "rt.AD_Window_ID as RT_AD_Window_ID, rt.AD_InfoWindow_ID as AD_InfoWindow_ID " // 12..13
			+ "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;
		String displayColumnSQL = null;
		boolean IsTranslated = false, isValueDisplayed = false;

		int ZoomWindow = 0;
		int ZoomWindowPO = 0;
		int overrideZoomWindow = 0;
		int infoWindowId = 0;
		boolean loaded = false;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sql0, null);
			pstmt.setInt(1, AD_Reference_Value_ID);
			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);
				ZoomWindowPO = rs.getInt(9);
				displayColumnSQL = rs.getString(11);
				if (displayColumnSQL != null && displayColumnSQL.length() > 0 && (displayColumnSQL.startsWith(MColumn.VIRTUAL_UI_COLUMN_PREFIX) || displayColumnSQL.startsWith(MColumn.VIRTUAL_SEARCH_COLUMN_PREFIX)))
					displayColumnSQL = "NULL";
				if (displayColumnSQL != null && displayColumnSQL.contains("@"))
					displayColumnSQL = Env.parseContext(Env.getCtx(), -1, displayColumnSQL, false, true);
				overrideZoomWindow = rs.getInt(12);
				infoWindowId = rs.getInt(13);
				loaded = true;
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, sql0, e);
			return null;
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		if (!loaded)
		{
			s_log.log(Level.SEVERE, "No Table Reference Table ID=" + AD_Reference_Value_ID);
			return null;
		}

		StringBuilder realSQL = new StringBuilder("SELECT ");
		if (!KeyColumn.endsWith("_ID") && !KeyColumn.endsWith("_UU"))
			realSQL.append("NULL,");
		
		boolean showID = DisplayColumn.equals(TableName+"_ID") || DisplayColumn.equals(PO.getUUIDColumnName(TableName));
		
		ArrayList<LookupDisplayColumn> list = null;
		if (showID) {
			list = getListIdentifiers(TableName);
			//  Do we have columns ?
			if (list == null || list.size() == 0)
			{
				if (s_log.isLoggable(Level.INFO))
				{
					s_log.log(Level.INFO, "No Identifier records found: " + KeyColumn);
				}
				if (list == null)
					list = new ArrayList<LookupDisplayColumn>();
				list.add(new LookupDisplayColumn(KeyColumn, null, false, DisplayType.ID, 0));
			}
			// set isTranslated
			IsTranslated = false;
			for (LookupDisplayColumn ldc : list) {
				if (!IsTranslated && ldc.IsTranslated) {
					IsTranslated = true;
					break;
				}
			}
		}

		String separator = MSysConfig.getValue(MSysConfig.IDENTIFIER_SEPARATOR, "_", Env.getAD_Client_ID(Env.getCtx()));
		String lookupDisplayColumn = null;
		//	Translated
		if (IsTranslated && !Env.isBaseLanguage(language, TableName))
		{
			realSQL.append(TableName).append(".").append(KeyColumn).append(",");
			if (KeyColumn.endsWith("_ID") || KeyColumn.endsWith("_UU"))
				realSQL.append("NULL,");
			if (isValueDisplayed)
				realSQL.append("NVL(").append(TableName).append(".Value,'-1') || '").append(separator).append("' || ");
			if (displayColumnSQL != null && displayColumnSQL.trim().length() > 0)
				realSQL.append("NVL(").append(displayColumnSQL).append(",'-1')");
			else {
				if (showID) {
					StringBuilder displayColumn = getDisplayColumn(language, TableName, list);
					lookupDisplayColumn = displayColumn.toString();
					realSQL.append(displayColumn);
				} else {
					lookupDisplayColumn = DisplayColumn;
					realSQL.append("NVL(").append(TableName).append("_Trl.").append(DisplayColumn).append(",'-1')");
				}
			}
			realSQL.append(",").append(TableName).append(".IsActive");

			String realKeyColumn = KeyColumn.endsWith("_ID") || KeyColumn.endsWith("_UU") ? KeyColumn : (MTable.get(ctx, TableName).isUUIDKeyTable() ? PO.getUUIDColumnName(TableName) : TableName + "_ID");

			realSQL.append(" FROM ").append(TableName)
				.append(" INNER JOIN ").append(TableName).append("_TRL ON (")
				.append(TableName).append(".").append(realKeyColumn)
				.append("=").append(TableName).append("_Trl.").append(realKeyColumn)
				.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") || KeyColumn.endsWith("_UU"))
				realSQL.append("NULL,");
			if (isValueDisplayed)
				realSQL.append("NVL(").append(TableName).append(".Value,'-1') || '").append(separator).append("' || ");
			if (displayColumnSQL != null && displayColumnSQL.trim().length() > 0)
				realSQL.append("NVL(").append(displayColumnSQL).append(",'-1')");
			else {
				if (showID) {
					StringBuilder displayColumn = getDisplayColumn(language, TableName, list);
					lookupDisplayColumn = displayColumn.toString();
					realSQL.append(displayColumn);
				} else {
					lookupDisplayColumn = DisplayColumn;
					realSQL.append("NVL(").append(TableName).append(".").append(DisplayColumn).append(",'-1')");
				}
			}
			realSQL.append(",").append(TableName).append(".IsActive");
			realSQL.append(" FROM ").append(TableName);
		}

		String directQuery = realSQL.toString() + " WHERE " + TableName + "." + KeyColumn + "=?";
		
		//	add WHERE clause
		MQuery zoomQuery = null;
		if (WhereClause != null && WhereClause.length() > 0)
		{
			String where = WhereClause;
			if (where.indexOf('@') != -1)
				where = Env.parseContext(ctx, WindowNo, where, false);
			if (where.length() == 0 && WhereClause.length() != 0)
				s_log.severe ("Could not resolve: " + WhereClause);

			//	We have no context
			if (where.length() != 0)
			{
				realSQL.append(" WHERE ").append(where);
				if (where.indexOf('.') == -1)
					s_log.log(Level.SEVERE, "getLookup_Table - " + TableName
						+ ": WHERE should be fully qualified: " + WhereClause);
				zoomQuery = new MQuery (TableName);
				zoomQuery.addRestriction(where);
			}
		}

		//	Order By qualified term or by Name
		if (OrderByClause != null && OrderByClause.length() > 0  )
		{
			realSQL.append(" ORDER BY ").append(OrderByClause);
			if (OrderByClause.indexOf('.') == -1)
				s_log.log(Level.SEVERE, "getLookup_Table - " + TableName
					+ ": ORDER BY must fully qualified: " + OrderByClause);
		}
		else
			realSQL.append(" ORDER BY 3");

		if (s_log.isLoggable(Level.FINEST)) s_log.finest("AD_Reference_Value_ID=" + AD_Reference_Value_ID + " - " + realSQL);

		int zoomWinID = Env.getZoomWindowID(MTable.get(ctx, TableName).getAD_Table_ID(), 0, WindowNo);
		if (zoomWinID > 0)
			ZoomWindow = zoomWinID;

		if (overrideZoomWindow > 0)
		{
			ZoomWindow = overrideZoomWindow;
			ZoomWindowPO = 0;
		}
		StringBuilder msginf = new StringBuilder().append(TableName).append(".").append(KeyColumn);
		retValue = new MLookupInfo (realSQL.toString(), TableName,
			msginf.toString(), ZoomWindow, ZoomWindowPO, zoomQuery);
		retValue.DisplayColumn = lookupDisplayColumn;		
		retValue.InfoWindowId = infoWindowId;
		retValue.QueryDirect = MRole.getDefault().addAccessSQL(directQuery, TableName, true, false);
		List<String> lookupDisplayColumns = new ArrayList<String>();
		if (isValueDisplayed)
			lookupDisplayColumns.add("Value");
		lookupDisplayColumns.add(lookupDisplayColumn != null ? lookupDisplayColumn : DisplayColumn);
		retValue.lookupDisplayColumns = lookupDisplayColumns;
		if(list != null) {
			retValue.lookupDisplayColumnNames = new ArrayList<>();
			if (isValueDisplayed)
				retValue.lookupDisplayColumnNames.add("Value");
			for (LookupDisplayColumn ldc : list) {
				retValue.lookupDisplayColumnNames.add(ldc.ColumnName);
			}
		} else {
			retValue.lookupDisplayColumnNames = new ArrayList<>(retValue.lookupDisplayColumns); 
		}
		s_cacheRefTable.put(key.toString(), retValue.cloneIt());
		return retValue;
	}	//	getLookup_Table

	/**
	 *	Get Embedded Lookup SQL for Table Lookup display type
	 *  @param language language
	 * 	@param BaseColumn base column name
	 * 	@param BaseTable base table name
	 *  @param AD_Reference_Value_ID table reference id (AD_Ref_Table)
	 *	@return	SELECT Name FROM Table
	 */
	public static String getLookup_TableEmbed (Language language,
		String BaseColumn, String BaseTable, int AD_Reference_Value_ID)
	{
		String sql = "SELECT t.TableName,ck.ColumnName AS KeyColumn,"
			+ "cd.ColumnName AS DisplayColumn,rt.isValueDisplayed,cd.IsTranslated, cd.AD_Column_ID AS columnDisplay_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, DisplayColumn, TableName, TableNameAlias;
		boolean IsTranslated, isValueDisplayed;
		Integer columnDisplay_ID = 0;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			pstmt.setInt(1, AD_Reference_Value_ID);
			rs = pstmt.executeQuery();
			if (!rs.next())
			{
				s_log.log(Level.SEVERE, "Cannot find Reference Table, ID=" + AD_Reference_Value_ID
					+ ", Base=" + BaseTable + "." + BaseColumn);
				return null;
			}

			TableName = rs.getString(1);
			KeyColumn = rs.getString(2);
			DisplayColumn = rs.getString(3);
			isValueDisplayed = rs.getString(4).equals("Y");
			IsTranslated = rs.getString(5).equals("Y");
			columnDisplay_ID = rs.getInt(6);

		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, sql, e);
			return null;
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		int Column_ID = MColumn.getColumn_ID(BaseTable, BaseColumn);
		MColumn column = MColumn.get(Env.getCtx(), Column_ID);
		boolean showID = DisplayColumn.equals(TableName+"_ID") || DisplayColumn.equals(PO.getUUIDColumnName(TableName));
		if (showID) {
			if (column.isVirtualColumn())
				return getLookup_TableDirEmbed(language, DisplayColumn, BaseTable, column.getColumnSQL());
			else
				return getLookup_TableDirEmbed(language, DisplayColumn, BaseTable, BaseColumn);
		}

		// If it's self referencing then use other alias - teo_sarca [ 1739544 ]
		if (TableName.equals(BaseTable)) {
			TableNameAlias = TableName + "1";
		}
		else {
			TableNameAlias = TableName;
		}

		StringBuilder embedSQL = new StringBuilder("SELECT ");

		if (isValueDisplayed) {
			String separator = MSysConfig.getValue(MSysConfig.IDENTIFIER_SEPARATOR, "_", Env.getAD_Client_ID(Env.getCtx()));
			embedSQL.append(TableNameAlias).append(".Value||'").append(separator).append("'||");
		}

		MColumn columnDisplay = MColumn.get(Env.getCtx(), columnDisplay_ID);
		if (columnDisplay.isVirtualUIColumn() || columnDisplay.isVirtualSearchColumn())
		{
			s_log.warning("Virtual UI Column must not be used as display");
			return null;
		}

		boolean translated = false; 
		//	Translated
		if (IsTranslated && !Env.isBaseLanguage(language, TableName))
		{
			translated = true;

			if (columnDisplay.isVirtualColumn())
			{
				s_log.warning("Column SQL should not be Translated");
				return null;
			}
			else
				embedSQL.append(TableName).append("_Trl.").append(DisplayColumn);

			embedSQL.append(" FROM ").append(TableName).append(" ").append(TableNameAlias)
				.append(" INNER JOIN ").append(TableName).append("_TRL ON (")
				.append(TableNameAlias).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
		{
			if (columnDisplay.isVirtualColumn())
				embedSQL.append(columnDisplay.getColumnSQL(true)).append(" AS ").append(KeyColumn);
			else
				embedSQL.append(TableNameAlias).append(".").append(DisplayColumn);

			embedSQL.append(" FROM ").append(TableName).append(" ").append(TableNameAlias);
		}

		embedSQL.append(" WHERE ");
		
		// If is not virtual column - teo_sarca [ 1739530 ]
		if (!column.isVirtualColumn())
		{
			embedSQL.append(BaseTable).append(".").append(BaseColumn);
			embedSQL.append("=").append(TableNameAlias).append(".").append(KeyColumn);
		} else if (translated) {
			embedSQL.append(TableNameAlias).append(".").append(KeyColumn).append("=").append(column.getColumnSQL(true));
		} else {
			embedSQL.append(TableNameAlias).append(".").append(KeyColumn).append("=").append(column.getColumnSQL(true));
		}

		return embedSQL.toString();
	}	//	getLookup_TableEmbed

	/**
	 * Get Lookup Info for Table Direct lookup display type
	 * @param ctx context for access
	 * @param language language
	 * @param ColumnName column name
	 * @param WindowNo Window number
	 * @return MLookupInfo
	 */
	static private MLookupInfo getLookup_TableDir (Properties ctx, Language language,
		int WindowNo, String ColumnName)
	{
		if (!ColumnName.endsWith("_ID") && !ColumnName.endsWith("_UU"))
		{
			String error = "Key does not end with '_ID' or '_UU': " + ColumnName;
			s_log.log(Level.SEVERE, error, new Exception(error));
			return null;
		}

		String KeyColumn = MQuery.getZoomColumnName(ColumnName);
		String TableName = MQuery.getZoomTableName(ColumnName);
		int ZoomWindow = 0;
		int ZoomWindowPO = 0;

		//try cache
		StringBuilder cacheKey = new StringBuilder()
				.append(Env.getAD_Client_ID(ctx)).append("|")
				.append(Env.getAD_Role_ID(ctx)).append("|")
				.append(Env.getAD_User_ID(ctx)).append("|")
				.append(language.getAD_Language()).append("|")
				.append(TableName).append(".")
				.append(KeyColumn);
		if (s_cacheRefTable.containsKey(cacheKey.toString()))
			return s_cacheRefTable.get(cacheKey.toString()).cloneIt();

		ArrayList<LookupDisplayColumn> list = getListIdentifiers(TableName);
		
		//  Do we have columns ?
		if (list == null || list.size() == 0)
		{
			if (s_log.isLoggable(Level.INFO))
			{
				s_log.log(Level.INFO, "No Identifier records found: " + ColumnName);
			}
			if (list == null)
				list = new ArrayList<LookupDisplayColumn>();
			list.add(new LookupDisplayColumn(KeyColumn, null, false, DisplayType.ID, 0));
		}

		// set isTranslated
		boolean isTranslated = false;
		for (LookupDisplayColumn ldc : list) {
			if (!isTranslated && ldc.IsTranslated) {
				isTranslated = true;
				break;
			}
		}
		
		MTable table = MTable.get(ctx, TableName);
		ZoomWindow = table.getAD_Window_ID();
		ZoomWindowPO = table.getPO_Window_ID();

		int zoomWinID = Env.getZoomWindowID(table.getAD_Table_ID(), 0, WindowNo);
		if (zoomWinID > 0)
			ZoomWindow = zoomWinID;

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

		StringBuilder displayColumn = getDisplayColumn(language, TableName, list);
		realSQL.append(displayColumn.toString());
		realSQL.append(",").append(TableName).append(".IsActive");

		//  Translation
		if (isTranslated && !Env.isBaseLanguage(language, TableName))
		{
			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("')");
		}
		else	//	no translation
		{
			realSQL.append(" FROM ").append(TableName);
		}
		
		String directQuery = realSQL.toString() + " WHERE " + TableName + "." + KeyColumn + "=?";

		//	Order by Display
		realSQL.append(" ORDER BY 3");
		MQuery zoomQuery = null;	//	corrected in VLookup

		if (s_log.isLoggable(Level.FINE)) s_log.fine("ColumnName=" + ColumnName + " - " + realSQL);
		StringBuilder msginf = new StringBuilder().append(TableName).append(".").append(KeyColumn);
		MLookupInfo lInfo = new MLookupInfo(realSQL.toString(), TableName,
			msginf.toString(), ZoomWindow, ZoomWindowPO, zoomQuery);
		lInfo.DisplayColumn = displayColumn.toString();
		lInfo.QueryDirect = MRole.getDefault().addAccessSQL(directQuery, TableName, true, false);
		List<String> lookupDisplayColumns = new ArrayList<String>();
		for (LookupDisplayColumn ldc : list) {
			lookupDisplayColumns.add(ldc.ColumnName);
		}
		lInfo.lookupDisplayColumns = lookupDisplayColumns;
		if(list != null) {
			lInfo.lookupDisplayColumnNames = new ArrayList<>();
			for (LookupDisplayColumn ldc : list) {
				lInfo.lookupDisplayColumnNames.add(ldc.ColumnName);
			}
		}
		s_cacheRefTable.put(cacheKey.toString(), lInfo.cloneIt());
		return lInfo;
	}	//	getLookup_TableDir

	/**
	 * @param language
	 * @param tableName
	 * @param list
	 * @return display columns
	 */
	private static StringBuilder getDisplayColumn(Language language,
			String tableName, ArrayList<LookupDisplayColumn> list) {
		return getDisplayColumn(language, tableName, list, tableName);
	}

	/**
	 * @param language
	 * @param TableName
	 * @param list
	 * @param baseTable
	 * @return display columns
	 */
	private static StringBuilder getDisplayColumn(Language language,
			String TableName, ArrayList<LookupDisplayColumn> list,
			String baseTable) {
		StringBuilder displayColumn = new StringBuilder();
		int size = list.size();
		//  Get Display Column
		for (int i = 0; i < size; i++)
		{
			if (i > 0)
			{
				displayColumn.append(" ||'")
					.append(MSysConfig.getValue(MSysConfig.IDENTIFIER_SEPARATOR, "_", Env.getAD_Client_ID(Env.getCtx())))
					.append("'|| " );
			}
			LookupDisplayColumn ldc = (LookupDisplayColumn)list.get(i);
			StringBuilder msg = new StringBuilder().append(TableName).append(".").append(ldc.ColumnName);
			String columnSQL = ldc.IsVirtual ? ldc.ColumnSQL : msg.toString();

			displayColumn.append("NVL(");

			//  translated
			if (ldc.IsTranslated && !Env.isBaseLanguage(language, TableName) && !ldc.IsVirtual
				&& baseTable != null && !(TableName+"_Trl").equalsIgnoreCase(baseTable))
			{
				displayColumn.append(TableName).append("_Trl.").append(ldc.ColumnName);
			}
			//  date, number
			else if (DisplayType.isDate(ldc.DisplayType) || DisplayType.isNumeric(ldc.DisplayType))
			{
				displayColumn.append(DB.TO_CHAR(columnSQL, ldc.DisplayType, language.getAD_Language()));
			}
			//	Table
			else if ((ldc.DisplayType == DisplayType.Table || ldc.DisplayType == DisplayType.TableUU
					|| ldc.DisplayType == DisplayType.Search || ldc.DisplayType == DisplayType.SearchUU) && ldc.AD_Reference_ID != 0)
			{
				String embeddedSQL;
				if (ldc.IsVirtual)
					embeddedSQL = getLookup_TableEmbed (language, ldc.ColumnSQL, TableName, ldc.AD_Reference_ID);
				else
					embeddedSQL = getLookup_TableEmbed (language, ldc.ColumnName, TableName, ldc.AD_Reference_ID);
				if (embeddedSQL != null)
					displayColumn.append("(").append(embeddedSQL).append(")");
			}
			//  TableDir
			else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search) && ldc.ColumnName.endsWith("_ID")
				  || (ldc.DisplayType == DisplayType.TableDirUU || ldc.DisplayType == DisplayType.SearchUU) && ldc.ColumnName.endsWith("_UU"))
			{
				String embeddedSQL;
				if (ldc.IsVirtual)
					embeddedSQL = getLookup_TableDirEmbed(language, ldc.ColumnName, TableName, ldc.ColumnSQL);
				else
					embeddedSQL = getLookup_TableDirEmbed(language, ldc.ColumnName, TableName);
				if (embeddedSQL != null)
					displayColumn.append("(").append(embeddedSQL).append(")");
			}
			//  List
			else if (DisplayType.isList(ldc.DisplayType))
			{
				if (ldc.DisplayType == DisplayType.ChosenMultipleSelectionList)
				{
					displayColumn.append(columnSQL);
				}
				else
				{
					String embeddedSQL = getLookup_ListEmbed(language, ldc.AD_Reference_ID, ldc.ColumnName);
					if (embeddedSQL != null)
						displayColumn.append("(").append(embeddedSQL).append(")");
				}
			}
			//	ID
			else if (DisplayType.isID(ldc.DisplayType))
			{
				displayColumn.append(DB.TO_CHAR(columnSQL, ldc.DisplayType, language.getAD_Language()));
			}
			//  String
			else
			{
				displayColumn.append(columnSQL);
			}

			displayColumn.append(",'-1')");

		}
		return displayColumn;
	}

	/**
	 *  Get embedded SQL for TableDir Lookup display type
	 *
	 *  @param language language
	 *  @param ColumnName column name
	 *  @param BaseTable base table
	 *  @return SELECT Column FROM TableName WHERE BaseTable.ColumnName=TableName.ColumnName
	 *  @see #getLookup_TableDirEmbed(Language, String, String, String)
	 */
	static public String getLookup_TableDirEmbed (Language language, String ColumnName, String BaseTable)
	{
		return getLookup_TableDirEmbed (language, ColumnName, BaseTable, ColumnName);
	}   //  getLookup_TableDirEmbed

	/**
	 *  Get embedded SQL for TableDir Lookup display type
	 *
	 *  @param language language
	 *  @param ColumnName column name
	 *  @param BaseTable base table
	 *  @param BaseColumn base column
	 *  @return SELECT Column FROM TableName WHERE BaseTable.BaseColumn=TableName.ColumnName
	 */
	static public String getLookup_TableDirEmbed (Language language,
		String ColumnName, String BaseTable, String BaseColumn)
	{
		String KeyColumn = MQuery.getZoomColumnName(ColumnName);
		String TableName = MQuery.getZoomTableName(ColumnName);

		ArrayList<LookupDisplayColumn> list = getListIdentifiers(TableName);

		//  Do we have columns ?
		if (list == null || list.size() == 0)
		{
			s_log.log(Level.SEVERE, "No Identifier records found: " + ColumnName);
			return "";
		}

		// set isTranslated
		boolean isTranslated = false;
		for (LookupDisplayColumn ldc : list) {
			if (!isTranslated && ldc.IsTranslated) {
				isTranslated = true;
				break;
			}
		}
		
		//
		StringBuilder embedSQL = new StringBuilder("SELECT ");

		StringBuilder displayColumn = getDisplayColumn(language, TableName, list, BaseTable);
		embedSQL.append(displayColumn.toString());
		embedSQL.append(" FROM ").append(TableName);
		//  Translation
		if (   isTranslated && !Env.isBaseLanguage(language, TableName)
			&& !(TableName+"_Trl").equalsIgnoreCase(BaseTable))  // IDEMPIERE-1070
		{
			embedSQL.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(DB.TO_STRING(language.getAD_Language())).append(")");
		}
		embedSQL.append(" WHERE ");
		// If is not virtual column - teo_sarca [ 1739530 ]
		if (! BaseColumn.trim().startsWith("(")) {
			embedSQL.append(BaseTable).append(".").append(BaseColumn);
		} else {
			embedSQL.append(BaseColumn);
		}
		embedSQL.append("=").append(TableName).append(".").append(ColumnName);
		//
		return embedSQL.toString();
	}	//  getLookup_TableDirEmbed

	/**
	 * @param TableName
	 * @return identifier list for a table
	 */
	private static ArrayList<LookupDisplayColumn> getListIdentifiers(String TableName) {
		ArrayList<LookupDisplayColumn> list = new ArrayList<LookupDisplayColumn>();
		MTable table = MTable.get(Env.getCtx(), TableName);
		if (table == null)
			return null;
		for (String idColumnName : table.getIdentifierColumns()) {
			MColumn column = table.getColumn(idColumnName);
			LookupDisplayColumn ldc = new LookupDisplayColumn(column.getColumnName(), column.getColumnSQL(true), column.isTranslated(), column.getAD_Reference_ID(), column.getAD_Reference_Value_ID());
			list.add (ldc);
		}
		return list;
	}

}   //  MLookupFactory