/******************************************************************************
 * 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 Smart Business Solution. The Initial
 * Developer of the Original Code is Jorg Janke. Portions created by Jorg Janke
 * are Copyright (C) 1999-2005 Jorg Janke.
 * All parts are Copyright (C) 1999-2005 ComPiere, Inc.  All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package com.ampiere.search;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Properties;
import java.util.logging.Level;

import org.apache.ecs.AlignType;
import org.compiere.util.Ctx;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.Env;
import org.compiere.util.Msg;

/**
 *	Generic Table Search
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: InfoGeneral.java,v 1.1 2010/01/13 11:52:01 clmg Exp $
 */
public class InfoGeneral extends Info
{
	/**
	 *	Detail Protected Constructor.
	 *
	 * 	@param WindowNo window no
	 * 	@param value QueryValue
	 * 	@param tableName table name
	 * 	@param keyColumn key column (ignored)
	 * 	@param multiSelection multiple selections
	 * 	@param whereClause where clause
	 */
	protected InfoGeneral (int WindowNo, String value, String tableName, String keyColumn, boolean multiSelection, String whereClause, Ctx ctx)
	{
		super(WindowNo, tableName, keyColumn, multiSelection, whereClause, ctx);
		log.info(tableName + " - " + keyColumn + " - " + whereClause);

		title = Msg.translate(ctx, "Info");

		//
		statInit(ctx);
		String s = "";
		if (value != null && value.length() > 0) {
			s = value;
		}
		p_loadedOK = initInfo(ctx, s);
	}	//	InfoGeneral

	/**  String Array of Column Info    */
	private Info_Column[] m_generalLayout;
	/** list of query columns           */
	private ArrayList<String> 	m_queryColumns = new ArrayList<String>();

	/**
	 *	Static Setup - add fields to parameterPanel (GridLayout)
	 */
	private void statInit(Ctx ctx)
	{
	}	//	statInit

	/**
	 *	General Init
	 *	@return true, if success
	 */
	private boolean initInfo(Ctx ctx, String value)
	{
		if (!initInfoTable(ctx))
			return false;

		//  prepare table
		StringBuffer where = new StringBuffer("IsActive='Y'");
		if (p_whereClause.length() > 0)
			where.append(" AND ").append(p_whereClause);
		prepareTable(m_generalLayout,
			p_tableName,
			where.toString(),
			"2");

		// Set & enable Fields
		// First Row
		componentList.add(new ComponentBean(Msg.translate(ctx, m_queryColumns.get(0).toString()), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put(m_queryColumns.get(0).toString(), new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, value, AlignType.LEFT, AlignType.MIDDLE, "", 220));

		if (m_queryColumns.size() > 1) {
			componentList.add(new ComponentBean(Msg.translate(ctx, m_queryColumns.get(1).toString()), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
			componentMapping.put(m_queryColumns.get(1).toString(), new Integer(componentList.size()));
			componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
		}

		componentList.add(new ComponentBean(ComponentBean.TYPE_BR));

		// 2nd Row
		if (m_queryColumns.size() > 2) {
			componentList.add(new ComponentBean(Msg.translate(ctx, m_queryColumns.get(2).toString()), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
			componentMapping.put(m_queryColumns.get(2).toString(), new Integer(componentList.size()));
			componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
		}
		if (m_queryColumns.size() > 3) {
			componentList.add(new ComponentBean(Msg.translate(ctx, m_queryColumns.get(3).toString()), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
			componentMapping.put(m_queryColumns.get(3).toString(), new Integer(componentList.size()));
			componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
		}

		return true;
	}	//	initInfo

	/**
	 *	Init info with Table.
	 *	- find QueryColumns (Value, Name, ..)
	 *	- build gridController & columsn
	 *  @return true if success
	 */
	private boolean initInfoTable(Ctx ctx)
	{
		//	Get Query Columns -------------------------------------------------
		String sql = "SELECT c.ColumnName, t.AD_Table_ID, t.TableName "
			+ "FROM AD_Table t"
			+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
			+ "WHERE c.AD_Reference_ID=10"
			+ " AND t.TableName=?"	//	#1
			//	Displayed in Window
			+ " AND EXISTS (SELECT * FROM AD_Field f "
				+ "WHERE f.AD_Column_ID=c.AD_Column_ID"
				+ " AND f.IsDisplayed='Y' AND f.IsEncrypted='N' AND f.ObscureType IS NULL) "
			//
			+ "ORDER BY c.IsIdentifier DESC, c.SeqNo";
		int AD_Table_ID = 0;
		String tableName = null;
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql, null);
			pstmt.setString(1, p_tableName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				m_queryColumns.add(rs.getString(1));
				if (AD_Table_ID == 0)
				{
					AD_Table_ID = rs.getInt(2);
					tableName = rs.getString(3);
				}
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql, e);
			return false;
		}
		//	Miminum check
		if (m_queryColumns.size() == 0)
		{
			log.log(Level.SEVERE, "No query columns found");
			return false;
		}
		log.finest("Table " + tableName + ", ID=" + AD_Table_ID 
			+ ", QueryColumns #" + m_queryColumns.size());
		//	Only 4 Query Columns
		while (m_queryColumns.size() > 4)
			m_queryColumns.remove(m_queryColumns.size()-1);
		//  Set Title
		String title = Msg.translate(ctx, tableName + "_ID");  //  best bet
		if (title.endsWith("_ID"))
			title = Msg.translate(ctx, tableName);             //  second best bet
		this.title = this.title + " " + title;

		//	Get Display Columns -----------------------------------------------
		ArrayList<Info_Column> list = new ArrayList<Info_Column>();
		sql = "SELECT c.ColumnName, c.AD_Reference_ID, c.IsKey, f.IsDisplayed, c.AD_Reference_Value_ID "
			+ "FROM AD_Column c"
			+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID)"
			+ " INNER JOIN AD_Tab tab ON (t.AD_Window_ID=tab.AD_Window_ID)"
			+ " INNER JOIN AD_Field f ON (tab.AD_Tab_ID=f.AD_Tab_ID AND f.AD_Column_ID=c.AD_Column_ID) "
			+ "WHERE t.AD_Table_ID=? "
			+ " AND (c.IsKey='Y' OR "
			//	+ " (f.IsDisplayed='Y' AND f.IsEncrypted='N' AND f.ObscureType IS NULL)) "
				+ " (f.IsEncrypted='N' AND f.ObscureType IS NULL)) "
			+ "ORDER BY c.IsKey DESC, f.SeqNo";

		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql, null);
			pstmt.setInt(1, AD_Table_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				String columnName = rs.getString(1);
				int displayType = rs.getInt(2);
				boolean isKey = rs.getString(3).equals("Y");
				boolean isDisplayed = rs.getString(4).equals("Y");
				int AD_Reference_Value_ID = rs.getInt(5);
				//  Default
				StringBuffer colSql = new StringBuffer(columnName);
				Class colClass = null;
				//
				if (isKey)
					colClass = IDColumn.class;
				else if (!isDisplayed)
					;
				else if (displayType == DisplayType.YesNo)
					colClass = Boolean.class;
				else if (displayType == DisplayType.Amount)
					colClass = BigDecimal.class;
				else if (displayType == DisplayType.Number || displayType == DisplayType.Quantity)
					colClass = Double.class;
				else if (displayType == DisplayType.Integer)
					colClass = Integer.class;
				else if (displayType == DisplayType.String || displayType == DisplayType.Text || displayType == DisplayType.Memo)
					colClass = String.class;
				else if (DisplayType.isDate(displayType))
					colClass = Timestamp.class;
				else if (displayType == DisplayType.List)
				{
					if (Env.isBaseLanguage(ctx, "AD_Ref_List"))
						colSql = new StringBuffer("(SELECT l.Name FROM AD_Ref_List l WHERE l.AD_Reference_ID=")
							.append(AD_Reference_Value_ID).append(" AND l.Value='").append(columnName)
							.append("') AS ").append(columnName);
					else
						colSql = new StringBuffer("(SELECT t.Name FROM AD_Ref_List l, AD_Ref_List_Trl t "
							+ "WHERE l.AD_Ref_List_ID=t.AD_Ref_List_ID AND l.AD_Reference_ID=")
							.append(AD_Reference_Value_ID).append(" AND l.Value='").append(columnName)
							.append("' AND t.AD_Language='").append(Env.getAD_Language(ctx))
							.append("') AS ").append(columnName);
					colClass = String.class;
				}

				if (colClass != null)
				{
					if (isKey) {
						list.add(new Info_Column(Msg.translate(ctx, "Select"), colSql.toString(), colClass));
					} else {
						list.add(new Info_Column(Msg.translate(ctx, columnName), colSql.toString(), colClass));
					}
					log.finest("Added Column=" + columnName);
				}
				else
					log.finest("Not Added Column=" + columnName);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql, e);
			return false;
		}
		if (list.size() == 0)
		{
			log.log(Level.SEVERE, "No Info for AD_Table_ID=" + AD_Table_ID + " - " + sql);
			return false;
		}
		log.finest("InfoColumns #" + list.size()); 

		//  Convert ArrayList to Array
		m_generalLayout = new Info_Column[list.size()];
		list.toArray(m_generalLayout);
		columnAttrList = new ArrayList<Integer>();
		columnAttrList.add(1);
		columnAttrList.add(1);
		for (int ii = 2; ii < m_generalLayout.length; ii++) {
			columnAttrList.add(0);
		}
		return true;
	}	//	initInfoTable

	/**
	 *	Construct SQL Where Clause and define parameters.
	 *  (setParameters needs to set parameters)
	 *  Includes first AND
	 *  @return sql
	 */
	protected String createQuery(Hashtable valueMapping, SimpleDateFormat dateFormat) {
		StringBuffer sql = new StringBuffer();

		// Field 1
		String s = getComponetValue(valueMapping, m_queryColumns.get(0).toString());
		if (s != null)
		{
			addSQLWhere(sql, 0, s.toUpperCase());
		}

		if (m_queryColumns.size() > 1) {
			s = getComponetValue(valueMapping, m_queryColumns.get(1).toString());
			if (s != null)
			{
				addSQLWhere(sql, 1, s.toUpperCase());
			}
		}

		if (m_queryColumns.size() > 2) {
			s = getComponetValue(valueMapping, m_queryColumns.get(2).toString());
			if (s != null)
			{
				addSQLWhere(sql, 2, s.toUpperCase());
			}
		}

		if (m_queryColumns.size() > 3) {
			s = getComponetValue(valueMapping, m_queryColumns.get(3).toString());
			if (s != null)
			{
				addSQLWhere(sql, 3, s.toUpperCase());
			}
		}

		return sql.toString();
	}	// getSQLWhere

	/**
	 *	Add directly Query as Strings
	 * 	@param sql sql buffer
	 * 	@param index index
	 * 	@param value value
	 */
	private void addSQLWhere(StringBuffer sql, int index, String value)
	{
		if (!(value.equals("") || value.equals("%")) && index < m_queryColumns.size())
		{
			sql.append(" AND UPPER(").append(m_queryColumns.get(index).toString()).append(") LIKE '");
			sql.append(value);
			if (value.endsWith("%"))
				sql.append("'");
			else
				sql.append("%'");
		}
	}	// addSQLWhere

	/**
	 *  Set Parameters for Query.
	 *  (as defined in getSQLWhere)
	 * 	@param pstmt statement
	 *  @param forCount for counting records
	 *  @throws SQLException
	 */
	void setParameters(PreparedStatement pstmt, boolean forCount) throws SQLException
	{
	}	// setParameters

	/**************************************************************************
	 *  Get dynamic WHERE part of SQL
	 *	To be overwritten by concrete classes
	 *  @return WHERE clause
	 */
	protected String getSQLWhere() {
		if (p_sqlWhere != null && p_sqlWhere.length() > 0) {
			return p_sqlWhere;
		}
		return "";
	}
}	//	InfoGeneral
