/**
 * 
 */
package com.ampiere.web.struts.search;

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

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.compiere.framework.Lookup;
import org.compiere.model.MColumn;
import org.compiere.model.MLookup;
import org.compiere.model.MLookupFactory;
import org.compiere.model.MLookupInfo;
import org.compiere.model.MRole;
import org.compiere.util.CLogger;
import org.compiere.util.Ctx;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.Env;
import org.compiere.util.WebEnv;

import com.ampiere.util.AmpiereUtil;
import com.ampiere.web.struts.common.PopupBaseAction;

/**
 * @author Shinjirito
 *
 */
public class InfoBaseAction extends PopupBaseAction {
	/**	Logger			*/
	protected CLogger	log = CLogger.getCLogger(this.getClass());

	protected String m_tableName = null;
	protected String m_keyColumnName = null;

	/* (non-Javadoc)
	 * @see com.jware.base.web.struts.jWareBaseAction#doExecute(org.apache.struts.action.ActionMapping, org.apache.struts.action.ActionForm, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse, org.apache.struts.action.ActionErrors, boolean)
	 */
	public ActionForward doExecute(ActionMapping mapping, ActionForm baseForm,
			HttpServletRequest request, HttpServletResponse response,
			ActionErrors errors, boolean mochaFlag) throws Exception {
		try
		{
			String enc = request.getCharacterEncoding();
			if (enc == null)
				request.setCharacterEncoding(WebEnv.ENCODING);
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "Set CharacterEndocung=" + WebEnv.ENCODING, e);
		}

	    return null;
	}

	/**
	 * 	Generate Access SQL for Search.
	 * 	The SQL returns the ID of the value entered
	 * 	Also sets m_tableName and m_keyColumnName
	 *	@param text uppercase text for LIKE comparison
	 *	@return sql or ""
	 *  Example
	 *	SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE x OR ...
	 */
	protected String getDirectAccessSQL(Ctx ctx, String text, String columnName, Lookup lookup)
	{
		StringBuffer sql = new StringBuffer();
		m_tableName = columnName.substring(0, columnName.length()-3);
		m_keyColumnName = columnName;
		//
		if (columnName.equals("M_Product_ID"))
		{
			//	Reset
//			Env.setContext(ctx, Env.WINDOW_INFO, Env.TAB_INFO, "M_Product_ID", "0");
//			Env.setContext(ctx, Env.WINDOW_INFO, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
			ctx.setContext(Env.WINDOW_INFO, Env.TAB_INFO, "M_Product_ID", "0");
			ctx.setContext(Env.WINDOW_INFO, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
			//
			sql.append("SELECT M_Product_ID FROM M_Product WHERE (UPPER(Value) LIKE ")
				.append(DB.TO_STRING(text))
				.append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text))
				.append(" OR UPC LIKE ").append(DB.TO_STRING(text)).append(")");
		}
		else if (columnName.equals("C_BPartner_ID"))
		{
			sql.append("SELECT C_BPartner_ID FROM C_BPartner WHERE (UPPER(Value) LIKE ")
				.append(DB.TO_STRING(text))
				.append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text)).append(")");
		}
		else if (columnName.equals("C_Order_ID"))
		{
			sql.append("SELECT C_Order_ID FROM C_Order WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("C_Invoice_ID"))
		{
			sql.append("SELECT C_Invoice_ID FROM C_Invoice WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("M_InOut_ID"))
		{
			sql.append("SELECT M_InOut_ID FROM M_InOut WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("C_Payment_ID"))
		{
			sql.append("SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("GL_JournalBatch_ID"))
		{
			sql.append("SELECT GL_JournalBatch_ID FROM GL_JournalBatch WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("SalesRep_ID"))
		{
			sql.append("SELECT AD_User_ID FROM AD_User WHERE UPPER(Name) LIKE ")
				.append(DB.TO_STRING(text));
			m_tableName = "AD_User";
			m_keyColumnName = "AD_User_ID";
		}
		//	Predefined
		if (sql.length() > 0)
		{
			String wc = getWhereClause(ctx, lookup, columnName);
			if (wc != null && wc.length() > 0)
				sql.append(" AND ").append(wc);
			sql.append(" AND IsActive='Y'");
			//	***
			log.finest(columnName + " (predefined) " + sql.toString());
			return MRole.getDefault().addAccessSQL(sql.toString(),
				m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
		}
		
		//	Check if it is a Table Reference
		if (lookup != null && lookup instanceof MLookup)
		{
			int AD_Reference_ID = ((MLookup)lookup).getAD_Reference_Value_ID();
			if (AD_Reference_ID != 0)
			{
				String query = "SELECT kc.ColumnName, dc.ColumnName, t.TableName "
					+ "FROM AD_Ref_Table rt"
					+ " INNER JOIN AD_Column kc ON (rt.AD_Key=kc.AD_Column_ID)"
					+ " INNER JOIN AD_Column dc ON (rt.AD_Display=dc.AD_Column_ID)"
					+ " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID) "
					+ "WHERE rt.AD_Reference_ID=?";
				String displayColumnName = null;
				PreparedStatement pstmt = null;
				try
				{
					pstmt = DB.prepareStatement(query, null);
					pstmt.setInt(1, AD_Reference_ID);
					ResultSet rs = pstmt.executeQuery();
					if (rs.next())
					{
						m_keyColumnName = rs.getString(1);
						displayColumnName = rs.getString(2);
						m_tableName = rs.getString(3);
					}
					rs.close();
					pstmt.close();
					pstmt = null;
				}
				catch (Exception e)
				{
					log.log(Level.SEVERE, query, e);
				}
				try
				{
					if (pstmt != null)
						pstmt.close();
					pstmt = null;
				}
				catch (Exception e)
				{
					pstmt = null;
				}
				if (displayColumnName != null)
				{
					sql = new StringBuffer();
					sql.append("SELECT ").append(m_keyColumnName)
						.append(" FROM ").append(m_tableName)
						.append(" WHERE UPPER(").append(displayColumnName)
						.append(") LIKE ").append(DB.TO_STRING(text))
						.append(" AND IsActive='Y'");
					String wc = getWhereClause(ctx, lookup, columnName);
					if (wc != null && wc.length() > 0)
						sql.append(" AND ").append(wc);
					//	***
					log.finest(columnName + " (Table) " + sql.toString());
					return MRole.getDefault().addAccessSQL(sql.toString(),
								m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
				}
			}	//	Table Reference
		}	//	MLookup
		
		/** Check Well Known Columns of Table - assumes TableDir	**/
		String query = "SELECT t.TableName, c.ColumnName "
			+ "FROM AD_Column c "
			+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID AND t.IsView='N') "
			+ "WHERE (c.ColumnName IN ('DocumentNo', 'Value', 'Name') OR c.IsIdentifier='Y')"
			+ " AND c.AD_Reference_ID IN (10,14)"
			+ " AND EXISTS (SELECT * FROM AD_Column cc WHERE cc.AD_Table_ID=t.AD_Table_ID"
				+ " AND cc.IsKey='Y' AND cc.ColumnName=?)";
		m_keyColumnName = columnName;
		sql = new StringBuffer();
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(query, null);
			pstmt.setString(1, m_keyColumnName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				if (sql.length() != 0)
					sql.append(" OR ");
				m_tableName = rs.getString(1);
				sql.append("UPPER(").append(rs.getString(2)).append(") LIKE ").append(DB.TO_STRING(text));
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (SQLException ex)
		{
			log.log(Level.SEVERE, query, ex);
		}
		try
		{
			if (pstmt != null)
				pstmt.close();
		}
		catch (SQLException ex1)
		{
		}
		pstmt = null;
		//
		if (sql.length() == 0)
		{
			log.log(Level.SEVERE, columnName + " (TableDir) - no standard/identifier columns");
			return "";
		}
		//
		StringBuffer retValue = new StringBuffer ("SELECT ")
			.append(columnName).append(" FROM ").append(m_tableName)
			.append(" WHERE ").append(sql)
			.append(" AND IsActive='Y'");
		String wc = getWhereClause(ctx, lookup, columnName);
		if (wc != null && wc.length() > 0)
			retValue.append(" AND ").append(wc);
		//	***
		log.finest(columnName + " (TableDir) " + sql.toString());
		return MRole.getDefault().addAccessSQL(retValue.toString(),
					m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
	}	//	getDirectAccessSQL

	/**
	 * 	Get Where Clause
	 *	@return where clause or ""
	 */
	protected String getWhereClause(Ctx ctx, Lookup lookup, String columnName)
	{
		String whereClause = "";
		if (lookup == null)
			return "";
		if (lookup.getZoomQuery() != null)
			whereClause = lookup.getZoomQuery().getWhereClause();
		String validation = lookup.getValidation();
		if (validation == null)
			validation = "";
		if (whereClause.length() == 0)
			whereClause = validation;
		else if (validation.length() > 0)
			whereClause += " AND " + validation;
		if (whereClause.indexOf('@') != -1)
		{
			String validated = Env.parseContext(ctx, lookup.getWindowNo(), whereClause, false);
			if (validated.length() == 0)
				log.severe(columnName + " - Cannot Parse=" + whereClause);
			else
			{
				log.fine(columnName + " - Parsed: " + validated);
				return validated;
			}
		}
		return whereClause;
	}	//	getWhereClause

	/**
	 * Create lookup from column ID / column name
	 * @param ctx
	 * @param windowNo
	 * @param AD_Column_ID
	 * @param columnName
	 * @return lookup
	 */
	protected Lookup createLookup(Ctx ctx, int windowNo, int AD_Column_ID, String columnName) {
		Lookup lookup = null;
		try {
//			lookup = MLookupFactory.get (ctx, windowNo, 0, AD_Column_ID, DisplayType.Search);
			lookup = MLookupFactory.get (ctx, windowNo, AD_Column_ID, DisplayType.Search);
		} catch (Exception e) {
			AD_Column_ID = AmpiereUtil.getFixedColumnID(columnName);
			try {
//				lookup = MLookupFactory.get (ctx, windowNo, 0, AD_Column_ID, DisplayType.Search);
				lookup = MLookupFactory.get (ctx, windowNo, AD_Column_ID, DisplayType.Search);
			} catch (Exception ex) {
				log.log(Level.SEVERE, "MLookupFactory.get AD_Column_ID=" + AD_Column_ID, e);
			}
		}

		if (lookup != null) {
			if (columnName != null && lookup.getColumnName() != null &&
					columnName.indexOf(lookup.getColumnName()) < 0 && lookup.getColumnName().indexOf(columnName) < 0) {
				MColumn column = new MColumn(ctx, AD_Column_ID, null);
				int AD_Reference_Value_ID=column.getAD_Reference_Value_ID();
				MLookupInfo info = MLookupFactory.getLookupInfo(ctx, windowNo, DisplayType.Search, AD_Column_ID,
						 Env.getLanguage(ctx), columnName, AD_Reference_Value_ID, false, "");
				if (info != null) {
//					lookup = new MLookup(info, 0);
					lookup = MLookupFactory.get(ctx, windowNo, AD_Column_ID, DisplayType.Search,
							Env.getLanguage(ctx), columnName, AD_Reference_Value_ID, false, "");
				}
			}
		}

		return lookup;
	}
}
