/******************************************************************************
 * Product: Compiere ERP & CRM Smart Business Solution                        *
 * Copyright (C) 1999-2007 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., 3600 Bridge Parkway #102, Redwood City, CA 94065, USA      *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.compiere.model;

import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import java.util.logging.*;

import org.compiere.*;
import org.compiere.framework.PO;
import org.compiere.util.*;

/**
 *	Persistent Table Model
 *	
 *  @author Jorg Janke
 *  @version $Id: MTable.java,v 1.1 2008/01/16 07:34:29 jrmt Exp $
 */
public class MTable extends X_AD_Table
{
	/**
	 * 	Get all active Tables
	 *	@param entityType optional entity Type ignored
	 *	@return array of tables
	 */
	public static MTable[] getTables(Ctx ctx, String entityType)
	{
		return getTables(ctx, null, entityType);
	}
	
	/**
	 * 	Get all active Tables
	 *  @param where where clause
	 *	@param entityType optional entity type
	 *	@return array of tables
	 */
	public static MTable[] getTables (Ctx ctx, String where, String entityType)
	{
		PreparedStatement pstmt = null;
		String sql = "SELECT * FROM AD_Table WHERE IsActive='Y' AND IsView='N'";
		ArrayList<MTable> list = new ArrayList<MTable>();
		if (where != null && where.length() > 0)
			sql += "AND " + where;
		if (entityType != null)	
			sql += " AND EntityType in (" + entityType + ")";
		sql += " ORDER BY LoadSeq";
		try
		{
			pstmt = DB.prepareStatement (sql, null);
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next ())
			{
				MTable table = new MTable (ctx, rs, null);
				/**
				String s = table.getSQLCreate();
				HashMap hmt = table.get_HashMap();
				MColumn[] columns = table.getColumns(false);
				HashMap hmc = columns[0].get_HashMap();
				**/
				list.add(table);
			}
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			s_log.log (Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		MTable[] retValue = new MTable[list.size()];
		list.toArray(retValue);
		return retValue;
	}	//	getTables
	
	
	/**
	 * 	Get Table from Cache
	 *	@param ctx context
	 *	@param AD_Table_ID id
	 *	@return MTable
	 */
	public static MTable get (Ctx ctx, int AD_Table_ID)
	{
		Integer key = new Integer(AD_Table_ID);
		MTable retValue = s_cache.get(key);
		if (retValue == null)
			return new MTable (ctx, AD_Table_ID, null);
		if (retValue.get_ID () != 0)
			s_cache.put (key, retValue);
		return retValue;
	}	//	get

	/**
	 * 	Get Table from Cache
	 *	@param ctx context
	 *	@param tableName case insensitive table name
	 *	@return Table or null
	 */
	public static MTable get (Ctx ctx, String tableName)
	{
		if (tableName == null)
			return null;
		//	Check cache
		Iterator<MTable> it = s_cache.values().iterator();
		while (it.hasNext())
		{
			MTable retValue = (MTable)it.next();
			if (tableName.equalsIgnoreCase(retValue.getTableName()))
				return retValue;
		}
		//	Get direct
		MTable retValue = null;
		String sql = "SELECT * FROM AD_Table WHERE UPPER(TableName)=?";
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, null);
			pstmt.setString(1, tableName.toUpperCase());
			ResultSet rs = pstmt.executeQuery ();
			if (rs.next ())
				retValue = new MTable (ctx, rs, null);
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		
		if (retValue != null)
		{
			Integer key = new Integer (retValue.getAD_Table_ID());
			s_cache.put (key, retValue);
		}
		return retValue;
	}	//	get
	
	/**
	 * 	Get Table Name
	 *	@param ctx context
	 *	@param AD_Table_ID table
	 *	@return tavle name
	 */
	public static String getTableName (Ctx ctx, int AD_Table_ID)
	{
		return MTable.get(ctx, AD_Table_ID).getTableName();
	}	//	getTableName
	
	
	/**	Cache						*/
	private static CCache<Integer,MTable> s_cache = new CCache<Integer,MTable>("AD_Table", 20);
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (MTable.class);
	
	/**	Packages for Model Classes			*/
	private static String[]			s_packages = null;
	/**	Default Packages for Model Classes	*/
	private static final String[]	s_packagesDefault = new String[] {
		"compiere.model",			//	Extensions	
		"org.compiere.model", "org.compiere.wf", 
		"org.compiere.print", "org.compiere.impexp"
	};
	
	/**	Special Classes				*/
	private static final String[]	s_special = new String[] {
		"AD_Element", "org.compiere.model.M_Element",
		"AD_Registration", "org.compiere.model.M_Registration",
		"AD_Tree", "org.compiere.model.MTree_Base",
		"R_Category", "org.compiere.model.MRequestCategory",
		"GL_Category", "org.compiere.model.MGLCategory",
		"K_Category", "org.compiere.model.MKCategory",
		"C_ValidCombination", "org.compiere.model.MAccount",
		"C_Phase", "org.compiere.model.MProjectTypePhase",
		"C_Task", "org.compiere.model.MProjectTypeTask",
		"K_Source", "org.compiere.model.X_K_Source"
	//	AD_Attribute_Value, AD_TreeNode
	};

	/**
	 * 	Get Packages
	 *	@return array of packages
	 */
	private static String[] getPackages(Ctx ctx)
	{
		if (s_packages != null)
			return s_packages;
		
		ArrayList<String> list = new ArrayList<String>();
		String[] classpaths = MEntityType.getClasspaths(ctx);	//	clean list
		for (int i = 0; i < classpaths.length; i++)
			list.add(classpaths[i]);

		//	Default Packages
		for (int i = 0; i < s_packagesDefault.length; i++)
		{
			String packageName = s_packagesDefault[i];
			if (!list.contains(packageName))
				list.add(packageName);
		}
		
		s_packages = new String[list.size()];
		s_packages = list.toArray(s_packages);
		s_log.info("#" + s_packages.length);
		return s_packages;
	}
	
	/**
	 * 	Get Persistency Class for Table
	 *	@param tableName table name
	 *	@return class or null
	 */
	public static Class<?> getClass (String tableName)
	{
		//	Not supported
		if (tableName == null || tableName.endsWith("_Trl"))
			return null;
		
		//	Import Tables (Name conflict)
		if (tableName.startsWith("I_"))
		{
			Class<?> clazz = getPOclass("org.compiere.model.X_" + tableName);
			if (clazz != null)
				return clazz;
			s_log.warning("No class for table: " + tableName);
			return null;
		}
			

		//	Special Naming
		for (int i = 0; i < s_special.length; i++)
		{
			if (s_special[i++].equals(tableName))
			{
				Class<?> clazz = getPOclass(s_special[i]);
				if (clazz != null)
					return clazz;
				break;
			}
		}
		
		//	Strip table name prefix (e.g. AD_) Customizations are 3/4
		String className = tableName;
		int index = className.indexOf('_');
		if (index > 0)
		{
			if (index < 3)		//	AD_, A_
				 className = className.substring(index+1);
			else
			{
				String prefix = className.substring(0,index);
				if (prefix.equals("Fact"))		//	keep custom prefix
					className = className.substring(index+1);
			}
		}
		//	Remove underlines
		className = Util.replace(className, "_", "");
	
		//	Search packages
		String[] packages = getPackages(Env.getCtx());
		for (int i = 0; i < packages.length; i++)
		{
			StringBuffer name = new StringBuffer(packages[i]).append(".M").append(className);
			Class<?> clazz = getPOclass(name.toString());
			if (clazz != null)
				return clazz;
		}

		//	Default Extension
		Class<?> clazz = getPOclass("compiere.model.X_" + tableName);
		if (clazz != null)
			return clazz;

		//	Default
		clazz = getPOclass("org.compiere.model.X_" + tableName);
		if (clazz != null)
			return clazz;

		return null;
	}	//	getClass
	
	/**
	 * 	Get PO class
	 *	@param className fully qualified class name
	 *	@return class or null
	 */
	private static Class<?> getPOclass (String className)
	{
		try
		{
			Class<?> clazz = Class.forName(className);
			//	Make sure that it is a PO class
			Class<?> superClazz = clazz.getSuperclass();
			while (superClazz != null)
			{
				if (superClazz == PO.class)
				{
					s_log.fine("Use: " + className);
					return clazz;
				}
				superClazz = superClazz.getSuperclass();
			}
		}
		catch (Exception e)
		{
		}
		s_log.finest("Not found: " + className);
		return null;
	}	//	getPOclass

	
	/**************************************************************************
	 * 	Standard Constructor
	 *	@param ctx context
	 *	@param AD_Table_ID id
	 *	@param trxName transaction
	 */
	public MTable (Ctx ctx, int AD_Table_ID, String trxName)
	{
		super (ctx, AD_Table_ID, trxName);
		if (AD_Table_ID == 0)
		{
		//	setName (null);
		//	setTableName (null);
			setAccessLevel (ACCESSLEVEL_SystemOnly);	// 4
			setEntityType (ENTITYTYPE_UserMaintained);	// U
			setIsChangeLog (false);
			setIsDeleteable (false);
			setIsHighVolume (false);
			setIsSecurityEnabled (false);
			setIsView (false);	// N
			setReplicationType (REPLICATIONTYPE_Local);
		}	
	}	//	MTable

	/**
	 * 	Load Constructor
	 *	@param ctx context
	 *	@param rs result set
	 *	@param trxName transaction
	 */
	public MTable (Ctx ctx, ResultSet rs, String trxName)
	{
		super(ctx, rs, trxName);
	}	//	MTable
	
	/**	Columns					*/
	private MColumn[]			m_columns = null;
	/** View Components			*/
	private MViewComponent[]	m_vcs = null;
	/** Dependents of Table		*/
	private ArrayList<MTable> m_dependents = null;

	/**
	 * 	Get Columns
	 *	@param requery requery
	 *	@return array of columns
	 */
	public MColumn[] getColumns (boolean requery)
	{
		if (m_columns != null && !requery)
			return m_columns;
		String sql = "SELECT * FROM AD_Column WHERE AD_Table_ID=? ORDER BY ColumnName";
		ArrayList<MColumn> list = new ArrayList<MColumn>();
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, get_TrxName());
			pstmt.setInt (1, getAD_Table_ID());
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next ())
				list.add (new MColumn (getCtx(), rs, get_TrxName()));
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		//
		m_columns = new MColumn[list.size ()];
		list.toArray (m_columns);
		return m_columns;
	}	//	getColumns
	
	/**
	 * 	Get Column
	 *	@param columnName (case insensitive)
	 *	@return column if found
	 */
	public MColumn getColumn (String columnName)
	{
		if (columnName == null || columnName.length() == 0)
			return null;
		getColumns(false);
		//
		for (int i = 0; i < m_columns.length; i++)
		{
			if (columnName.equalsIgnoreCase(m_columns[i].getColumnName()))
				return m_columns[i];
		}
		return null;
	}	//	getColumn
	
	/**
	 * 	Table has a single Key
	 *	@return true if table has single key column
	 */
	public boolean isSingleKey()
	{
		String[] keys = getKeyColumns();
		return keys.length == 1;
	}	//	isSingleKey
	
	/**
	 * 	Get Key Columns of Table
	 *	@return key columns
	 */
	public String[] getKeyColumns()
	{
		getColumns(false);
		ArrayList<String> list = new ArrayList<String>();
		//
		for (int i = 0; i < m_columns.length; i++)
		{
			MColumn column = m_columns[i];
			if (column.isKey())
				return new String[]{column.getColumnName()};
			if (column.isParent())
				list.add(column.getColumnName());
		}
		String[] retValue = new String[list.size()];
		retValue = list.toArray(retValue);
		return retValue;
	}	//	getKeyColumns
	
	/**
	 * 	Get Identifier Columns of Table
	 *	@return key columns
	 */
	@SuppressWarnings("unchecked")
	public String[] getIdentifierColumns()
	{
		getColumns(false);
		ArrayList<KeyNamePair> list = new ArrayList<KeyNamePair>();
		//
		for (int i = 0; i < m_columns.length; i++)
		{
			MColumn column = m_columns[i];
			if (column.isIdentifier())
			{
				int seq = column.getSeqNo();
				String columnName = column.getColumnName();
				KeyNamePair pp = new KeyNamePair(seq, columnName);
				pp.setSortByName(false);
				list.add (pp);
			}
		}
		
		Collections.sort(list);
		String[] retValue = new String[list.size()];
		for (int i = 0; i < list.size(); i++)
			retValue[i] = list.get(i).getName();
		return retValue;
	}	//	getIdentifierColumns

	/**
	 * 	Get list of columns for SELECT statement.
	 * 	Handles virtual columns
	 *	@return select columns
	 */
	public String getSelectColumns()
	{
		getColumns(false);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < m_columns.length; i++)
		{
			MColumn col = m_columns[i];
			if (i > 0)
				sb.append(",");
			if (col.isVirtualColumn())
				sb.append(col.getColumnSQL()).append(" AS ");
			sb.append(col.getColumnName());
		}
		return sb.toString();
	}	//	getSelectColumns
	
	/**
	 * 	Get Columns which are Foreign Keys
	 * 	@param includeList include list entities
	 *	@return array of FKs
	 */
	public ArrayList<MColumn> getFKs(boolean includeList)
	{
		getColumns(false);
		ArrayList<MColumn> retValue = new ArrayList<MColumn>();
		for (int i = 0; i < m_columns.length; i++)
		{
			MColumn col = m_columns[i];
			if (col.isFK())
				retValue.add(col);
			else if (includeList && col.isList())
				retValue.add(col);
		}
		return retValue;
	}	//	getFKs
	
	/**
	 * 	Get Dependent Tables
	 *	@return list of dependent tables
	 */
	public ArrayList<MTable> getDependents()
	{
		if (m_dependents != null)
			return m_dependents;
		//	Get Key Column
		String[] keyColumns = getKeyColumns();
		if (keyColumns.length == 0)
		{
			log.warning("No Key Columns for " + toString());
			return null;
		}
		else if (keyColumns.length > 1)
			log.warning("Multiple Key Columns for " + toString());
		String keyColumn = keyColumns[0];
		//	Get Dependents
		String sql = "SELECT * FROM AD_Table t "
			+ "WHERE IsView='N'"
			+ " AND EXISTS (SELECT * FROM AD_Column c "
				+ "WHERE t.AD_Table_ID=c.AD_Table_ID AND c.ColumnName=?) "
			+ "ORDER BY LoadSeq";
		m_dependents = new ArrayList<MTable>();
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			pstmt.setString(1, keyColumn);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				MTable table = new MTable(getCtx(), rs, null);
				if (!table.getTableName().equals(getTableName()))
					m_dependents.add(table);
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}

		return m_dependents;
	}	//	getDependents
	

	/**************************************************************************
	 * 	Get PO Class Instance
	 * 	@param ctx context for PO
	 *	@param Record_ID record - 0 = new
	 *	@param trxName transaction
	 *	@return PO for Record or null
	 */
	public PO getPO (Ctx ctx, int Record_ID, String trxName)
	{
		return getPO (ctx, Record_ID, trxName, true);
	}	//	getPO

	/**************************************************************************
	 * 	Get PO Class Instance
	 * 	@param ctx context for PO
	 *	@param Record_ID record - loads valid 0 records if newRecord is false
	 *	@param trxName transaction
	 *	@param newRecord new record
	 *	@return PO for Record or null
	 */
	public PO getPO (Ctx ctx, int Record_ID, String trxName, boolean newRecord)
	{
		String tableName = getTableName();
		if (Record_ID != 0 && !isSingleKey())
		{
			log.log(Level.WARNING, "(id) - Multi-Key " + tableName);
			return null;
		}
		Class<?> clazz = getClass(tableName);
		if (clazz == null)
		{
			log.log(Level.WARNING, "(id) - Class not found for " + tableName);
			return null;
		}
		boolean errorLogged = false;
		try
		{
			Constructor<?> constructor = null;
			try
			{
				constructor = clazz.getDeclaredConstructor(new Class[]{Ctx.class, int.class, String.class});
			}
			catch (Exception e)
			{
				String msg = e.getMessage();
				if (msg == null)
					msg = e.toString();
				log.warning("No transaction Constructor for " + clazz + " (" + msg + ")");
			}
			
			if (constructor != null)
			{
				PO po = (PO)constructor.newInstance(new Object[] {ctx, new Integer(Record_ID), trxName});
				//	Load record 0 - valid for System/Client/Org/Role/User
				if (!newRecord && Record_ID == 0)
					po.load(trxName);
				//	Check if loaded correctly
				if (po != null && po.get_ID() != Record_ID && isSingleKey())
				{
					log.warning(po.get_TableName() + "_ID=" + po.get_ID() + " <> requested=" + Record_ID);
					return null;
				}
				return po;
			}
			else
				throw new Exception("No Std Constructor");
		}
		catch (Exception e)
		{
			if (e.getCause() != null)
			{
				Throwable t = e.getCause();
				log.log(Level.SEVERE, "(id) - Table=" + tableName + ",Class=" + clazz, t);
				errorLogged = true;
				if (t instanceof Exception)
					log.saveError("Error", (Exception)e.getCause());
				else
					log.saveError("Error", "Table=" + tableName + ",Class=" + clazz);
			}
			else
			{
				log.log(Level.SEVERE, "(id) - Table=" + tableName + ",Class=" + clazz, e);
				errorLogged = true;
				log.saveError("Error", "Table=" + tableName + ",Class=" + clazz);
			}
		}
		if (!errorLogged)
			log.log(Level.SEVERE, "(id) - Not found - Table=" + tableName 
				+ ", Record_ID=" + Record_ID);
		return null;
	}	//	getPO
	
	/**
	 * 	Get PO Class Instance
	 * 	@param ctx context for PO
	 *	@param rs result set
	 *	@param trxName transaction
	 *	@return PO for Record or null
	 */
	public PO getPO (Ctx ctx, ResultSet rs, String trxName)
	{
		String tableName = getTableName();
		Class<?> clazz = getClass(tableName);
		if (clazz == null)
		{
			log.log(Level.SEVERE, "(rs) - Class not found for " + tableName);
			return null;
		}
		boolean errorLogged = false;
		try
		{
			Constructor<?> constructor = clazz.getDeclaredConstructor(new Class[]{Ctx.class, ResultSet.class, String.class});
			PO po = (PO)constructor.newInstance(new Object[] {ctx, rs, trxName});
			return po;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "(rs) - Table=" + tableName + ",Class=" + clazz, e);
			errorLogged = true;
			log.saveError("Error", "Table=" + tableName + ",Class=" + clazz);
		}
		if (!errorLogged)
			log.log(Level.SEVERE, "(rs) - Not found - Table=" + tableName);
		return null;
	}	//	getPO

	/**
	 * 	Get PO
	 * 	@param ctx general context for PO
	 *	@param context record context
	 *	@return PO
	 */
	public PO getPO (Ctx ctx, Map<String, String> context)
	{
		String tableName = getTableName();
		Class<?> clazz = getClass(tableName);
		if (clazz == null)
		{
			log.log(Level.WARNING, "(id) - Class not found for " + tableName);
			return null;
		}
		boolean errorLogged = false;
		try
		{
			Constructor<?> constructor = null;
			try
			{
				constructor = clazz.getDeclaredConstructor(new Class[]{Ctx.class, int.class, String.class});
			}
			catch (Exception e)
			{
				String msg = e.getMessage();
				if (msg == null)
					msg = e.toString();
				log.warning("No transaction Constructor for " + clazz + " (" + msg + ")");
			}
			if (constructor != null)
			{
				PO po = (PO)constructor.newInstance(new Object[] {ctx, new Integer(0), null});
				if (!po.load(context))
					throw new Exception("Could not load PO");
				return po;
			}
			else
				throw new Exception("No Std Constructor");
		}
		catch (Exception e)
		{
			if (e.getCause() != null)
			{
				Throwable t = e.getCause();
				log.log(Level.SEVERE, "(id) - Table=" + tableName + ",Class=" + clazz, t);
				errorLogged = true;
				if (t instanceof Exception)
					log.saveError("Error", (Exception)e.getCause());
				else
					log.saveError("Error", "Table=" + tableName + ",Class=" + clazz);
			}
			else
			{
				log.log(Level.SEVERE, "(id) - Table=" + tableName + ",Class=" + clazz, e);
				errorLogged = true;
				log.saveError("Error", "Table=" + tableName + ",Class=" + clazz);
			}
		}
		if (!errorLogged)
			log.log(Level.SEVERE, "(id) - Not found - Table=" + tableName);
		return null;
	}	//	getPO

	
	/**
	 * 	Get PO Class Instance
	 * 	@param ctx context for PO
	 *	@param whereClause where clause resulting in single record
	 *	@param trxName transaction
	 *	@return PO for Record or null
	 */
	public PO getPO (Ctx ctx, String whereClause, String trxName)
	{
		if (whereClause == null || whereClause.length() == 0)
			return null;
		//
		PO po = null;
		StringBuffer sql = new StringBuffer("SELECT ")
			.append(getSelectColumns())
			.append(" FROM ").append(getTableName())
			.append(" WHERE ").append(whereClause); 
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql.toString(), trxName);
			ResultSet rs = pstmt.executeQuery ();
			if (rs.next ())
				po = getPO(ctx, rs, trxName);
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
			log.saveError("Error", e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		if (po == null)
			return getPO(ctx, 0, trxName);
		return po;
	}	//	getPO
	
	/**
	 * 	Get POs Class Instance
	 * 	@param ctx context for PO
	 *	@param orderClause optional order by
	 *	@param trxName transaction
	 *	@return PO for Record or null
	 */
	public PO[] getPOs (Ctx ctx, String whereClause, String orderClause, String trxName)
	{
		ArrayList<PO> list = new ArrayList<PO>();
		String sql = "SELECT * FROM " + getTableName();
		if (whereClause != null && whereClause.length() > 0)
			sql += " WHERE " + whereClause;
		if (orderClause != null && orderClause.length() > 0)
			sql += " ORDER BY " + orderClause;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, trxName);
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next ())
			{
				PO po = getPO(ctx, rs, trxName);
				list.add (po);
			}
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		PO[] retValue = new PO[list.size()];
		list.toArray(retValue);
		return retValue;
	}	//	getPO
	
	/**
	 * 	Before Save
	 *	@param newRecord new
	 *	@return true
	 */
	protected boolean beforeSave (boolean newRecord)
	{
		if (isView() && isDeleteable())
			setIsDeleteable(false);
		//
		return true;
	}	//	beforeSave
	
	/**
	 * 	After Save
	 *	@param newRecord new
	 *	@param success success
	 *	@return success
	 */
	protected boolean afterSave (boolean newRecord, boolean success)
	{
		//	Sync Table ID
		if (newRecord)
			MSequence.createTableSequence(getCtx(), getTableName(), get_TrxName());
		else
		{
			MSequence seq = MSequence.get(getCtx(), getTableName(), get_TrxName());
			if (seq == null || seq.get_ID() == 0)
				MSequence.createTableSequence(getCtx(), getTableName(), get_TrxName());
			else if (!seq.getName().equals(getTableName()))
			{
				seq.setName(getTableName());
				seq.save();
			}
		}	
		return success;
	}	//	afterSave
	
	/**
	 * 	After Delete
	 *	@param success success
	 *	@return true if success
	 */
	protected boolean afterDelete(boolean success)
	{
		if (success)
			MSequence.deleteTableSequence(getCtx(), getTableName(), get_TrxName());
		return success;
	}	//	afterDelete
	
	/**
	 * 	Get SQL Create statement
	 *	@return sql statement
	 */
	public String getSQLCreate()
	{
		return getSQLCreate(true);
	}	// getSQLCrete
	
	/**
	 * 	Get SQL Create
	 * 	@param requery refresh columns
	 *	@return create table DDL
	 */
	public String getSQLCreate(boolean requery)
	{
		StringBuffer sb = new StringBuffer("CREATE TABLE ")
			.append(getTableName()).append(" (");
		//
		boolean hasPK = false;
		boolean hasParents = false;
		StringBuffer constraints = new StringBuffer();
		getColumns(requery);
		for (int i = 0; i < m_columns.length; i++)
		{
			MColumn column = m_columns[i];
			if (column.isVirtualColumn())
				continue;
			if (i > 0)
				sb.append(", ");
			sb.append(column.getSQLDDL());
			//
			if (column.isKey())
			{
				constraints.append (", CONSTRAINT PK").append (getAD_Table_ID()) 
					.append (" PRIMARY KEY (").append (column.getColumnName()).append (")");
				hasPK = true;
			}
			if (column.isParent())
				hasParents = true;
		}
		//	Multi Column PK 
		if (!hasPK && hasParents)
		{
			StringBuffer cols = new StringBuffer();
			for (int i = 0; i < m_columns.length; i++)
			{
				MColumn column = m_columns[i];
				if (!column.isParent())
					continue;
				if (cols.length() > 0)
					cols.append(", ");
				cols.append(column.getColumnName());
			}
			sb.append(", CONSTRAINT PK").append (getAD_Table_ID())
				.append(" PRIMARY KEY (").append(cols).append(")");
		}

		sb.append(constraints)
			.append(")");
		return sb.toString();
	}	//	getSQLCreate
	
	
	/**
	 * 	Get SQL Create View
	 * 	@param requery refresh columns
	 *	@return create create view DDL
	 */
	public String getViewDrop()
	{
		if (!isView() || !isActive())
			return null;
		
		return new String("DROP VIEW " + getTableName());
	}
		
	/**
	 * 	Get SQL Create View
	 * 	@param requery refresh columns
	 *	@return create create view DDL
	 */
	public String getViewCreate(boolean requery)
	{
		if (!isView() || !isActive())
			return null;
		
		StringBuffer sb = new StringBuffer("CREATE OR REPLACE VIEW ")
			.append(getTableName());
		//
		this.getViewComponent(requery);
		if (m_vcs == null || m_vcs.length == 0)
			return null;
		
		MViewColumn[] vCols = null;
		for (int i = 0; i < m_vcs.length; i++)
		{
			MViewComponent vc = m_vcs[i];
			if (i>0)
				sb.append(" UNION ");
			else
			{
				vCols = vc.getColumns(requery);
				if (vCols==null || vCols.length==0)
					return null;
				boolean right = false;
				for (int j=0; j<vCols.length; j++)
				{
					if (vCols[j].getColumnName().equals("*"))
						break;
					if (j==0)
					{
						sb.append("(");
						right = true;
					}
					else
						sb.append(", ");
					sb.append(vCols[j].getColumnName());
				}
				
				if (right)
					sb.append(")");
				sb.append(" AS ");
			}
			
			sb.append(vc.getSelect(false, vCols));
		}
		return sb.toString();
	}	//	getViewCreate
	
	
	/**
	 * 	Get MViewComponent Class Instances
	 *  @param reload boolean if it need to reload 
	 *	@return Array of MViewComponent or null
	 */
	public MViewComponent[] getViewComponent (boolean reload)
	{
		if (!isView() || !isActive())
			return null;
		
		if (m_vcs != null && !reload)
			return m_vcs;
		//
		ArrayList<MViewComponent> list = new ArrayList<MViewComponent>();
		String sql = "SELECT * FROM AD_ViewComponent WHERE AD_Table_ID = " + this.getAD_Table_ID() + " AND IsActive = 'Y'"; 
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, get_TrxName ());
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next ())
			{
				list.add (new MViewComponent (getCtx(), rs, get_TrxName ()));
			}
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log (Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		//
		m_vcs = new MViewComponent[list.size()];
		list.toArray (m_vcs);
		return m_vcs;
	}	//	getViewComponent
	
	
	/**
	 * 	String Representation
	 *	@return info
	 */
	public String toString()
	{
		StringBuffer sb = new StringBuffer ("MTable[");
		sb.append (get_ID()).append ("-").append (getTableName()).append ("]");
		return sb.toString ();
	}	//	toString

	
	/**
	 * 	Test
	 *	@param args
	 */
	public static void main(String[] args)
	{
		Compiere.startup (true);
		getTables(Env.getCtx(), null);
	}	//	main
}	//	MTable
