/******************************************************************************
 * 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.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Properties;
import java.util.logging.Level;

import org.apache.ecs.AlignType;
import org.compiere.model.MAttribute;
import org.compiere.model.MRole;
import org.compiere.util.CLogger;
import org.compiere.util.Ctx;
import org.compiere.util.DB;
import org.compiere.util.Msg;

import com.ampiere.dto.CodeValue;

/**
 *	Search by Product Attribute.
 *
 *  @author     Jorg Janke
 *  @version    $Id: InfoPAttribute.java,v 1.1 2010/01/13 11:52:01 clmg Exp $
 */
public class InfoPAttribute
{
	/**
	 * 	Constructor.
	 * 	Called from InfoProduct,cmd_InfoPAttribute
	 *	@param parent
	 */
	public InfoPAttribute (Ctx ctx)
	{
		try
		{
			dynInit(ctx);
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "InfoPAttribute", e);
		}
	}	//	InfoPAttribute

	/**	Logger			*/
	private static CLogger log = CLogger.getCLogger(InfoPAttribute.class);

	/**	Product Attribure Editors	*/
	private ArrayList<Hashtable> m_productEditors = new ArrayList<Hashtable>();
	private ArrayList<Hashtable> m_productEditorsTo = new ArrayList<Hashtable>();
	/**	Instance Attribute Editors	*/
	private ArrayList<Hashtable> m_instanceEditors = new ArrayList<Hashtable>();
	private ArrayList<Hashtable> m_instanceEditorsTo = new ArrayList<Hashtable>();
	private ArrayList<ComponentBean> componentList = new ArrayList<ComponentBean>();
	private Hashtable<String, Integer> componentMapping = new Hashtable<String, Integer>();

	/**
	 * 	Dynamic Init of the Center Panel
	 */
	private void dynInit(Ctx ctx)
	{
		addAttributes(ctx);

		componentList.add(new ComponentBean(Msg.translate(ctx, "SerNo"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("SerNo", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
		componentList.add(new ComponentBean(ComponentBean.TYPE_BR));
		componentMapping.put("Lot", new Integer(componentList.size()));
		componentList.add(new ComponentBean(Msg.translate(ctx, "Lot"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
		componentList.add(new ComponentBean(ComponentBean.TYPE_BR));
		componentList.add(new ComponentBean(Msg.translate(ctx, "M_Lot_ID"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("M_Lot_ID", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_SELECT, initLotSelection(), AlignType.LEFT, AlignType.MIDDLE, "", 220));
		componentList.add(new ComponentBean(ComponentBean.TYPE_BR));

		//
		String s = Msg.translate(ctx, "GuaranteeDate");
		ArrayList<CodeValue> lst = new ArrayList<CodeValue>();
		lst.add(new CodeValue("<", s + " <"));
		lst.add(new CodeValue("=", s + " ="));
		lst.add(new CodeValue(">", s + " >"));
		componentMapping.put("GuaranteeDateSelection", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_SELECT, lst, AlignType.RIGHT, AlignType.MIDDLE, "", 220));
		componentMapping.put("GuaranteeDate", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_DATE, "", AlignType.LEFT, AlignType.MIDDLE, "", 196));
		componentList.add(new ComponentBean(ComponentBean.TYPE_BR));
	}	//	dynInit

	/**
	 * 	Add Attributes
	 *	@return rows
	 */
	private int addAttributes(Ctx ctx)
	{
		int row = 0;
		PreparedStatement pstmt = null;
		String sql = MRole.getDefault().addAccessSQL(
			"SELECT M_Attribute_ID, Name, Description, AttributeValueType, IsInstanceAttribute "
			+ "FROM M_Attribute "
			+ "WHERE IsActive='Y' "
			+ "ORDER BY IsInstanceAttribute, Name", 
			"M_Attribute", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			ResultSet rs = pstmt.executeQuery();
			boolean instanceLine = false;
			while (rs.next())
			{
				int attribute_ID = rs.getInt(1);
				String name = rs.getString(2);
				String description = rs.getString(3);
				String attributeValueType = rs.getString(4);
				boolean isInstanceAttribute = "Y".equals(rs.getString(5)); 
				//	Instance switch
				if (!instanceLine && isInstanceAttribute)
				{
					componentList.add(new ComponentBean(Msg.translate(ctx, "IsInstanceAttribute"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.RIGHT, "", 0));
					componentList.add(new ComponentBean(ComponentBean.TYPE_BR));
					row++;
					instanceLine = true;
				}

				//
				ComponentBean cb = new ComponentBean();
				if (description != null && description.length() > 0)
					cb.setToolTipText(description);
				if (MAttribute.ATTRIBUTEVALUETYPE_List.equals(attributeValueType)) {
					cb.setType(ComponentBean.TYPE_SELECT);
					cb.setContent(getAttributeList(attribute_ID));
					cb.setWidth(220);
				} else if (MAttribute.ATTRIBUTEVALUETYPE_Number.equals(attributeValueType)) {
					cb.setType(ComponentBean.TYPE_NUMBER);
					cb.setWidth(220);
				} else {
					cb.setType(ComponentBean.TYPE_TEXT);
					cb.setWidth(220);
				}
				componentList.add(new ComponentBean(name, ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
				int idx = componentList.size();
				componentList.add(cb);
				componentList.add(new ComponentBean(ComponentBean.TYPE_BR));
				row++;

				Hashtable<String, Integer> attr = new Hashtable<String, Integer>();
				attr.put("idx", idx);
				attr.put("attribute_ID", attribute_ID);
				if (isInstanceAttribute)
					m_instanceEditors.add(attr);
				else
					m_productEditors.add(attr);

				Hashtable<String, Integer> attrTo = new Hashtable<String, Integer>();
				attrTo.put("attribute_ID", attribute_ID);
				//	To (numbers)
				if (MAttribute.ATTRIBUTEVALUETYPE_Number.equals(attributeValueType))
				{
					componentList.add(new ComponentBean("-" + name, ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
					idx = componentList.size();
					componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
					componentList.add(new ComponentBean(ComponentBean.TYPE_BR));
					attrTo.put("idx", idx);
				} else {
					attrTo.put("idx", -1);
				}

				if (isInstanceAttribute)
					m_instanceEditorsTo.add(attrTo);
				else
					m_productEditorsTo.add(attrTo);
			}
			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 row;
	}	//	addProductAttributes

	/**
	 *	Get Attribute List
	 *	@param M_Attribute_ID attribure
	 *	@return array
	 */
	private ArrayList<CodeValue> getAttributeList(int M_Attribute_ID)
	{
		ArrayList<CodeValue> list = new ArrayList<CodeValue>();
		list.add(new CodeValue("", ""));

		PreparedStatement pstmt = null;
		String sql = MRole.getDefault().addAccessSQL( 
			"SELECT M_AttributeValue_ID, Value, Name "
			+ "FROM M_AttributeValue "
			+ "WHERE M_Attribute_ID=? "
			+ "ORDER BY 2",
			"M_AttributeValue", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			pstmt.setInt(1, M_Attribute_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
				list.add(new CodeValue(rs.getString(1), rs.getString(3)));
			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 list;
	}	//	getAttributeList


	/**
	 * 	Initialize Lot Selection
	 */
	private ArrayList<CodeValue> initLotSelection()
	{
		ArrayList<CodeValue> list = new ArrayList<CodeValue>();
		list.add(new CodeValue("", ""));

		String sql = MRole.getDefault().addAccessSQL(
			"SELECT M_Lot_ID, Name FROM M_Lot WHERE IsActive='Y' ORDER BY 2",
			"M_Lot", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
				list.add(new CodeValue(rs.getString(1), rs.getString(2)));
			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;
		}
		//	Create List
		return list;
	}	//	initLotSelection

	/**
	 * Get search value
	 * @param valueMapping
	 * @param idx
	 * @return
	 */
	private String getValue(Hashtable valueMapping, int idx) {
		String s = (String)valueMapping.get(idx);
		if (s != null && s.length() > 0) {
			return s;
		}
		return null;
	}

	/**
	 * Get component search attribute value
	 * @param valueMapping
	 * @param key
	 * @return
	 */
	private String getComponetValue(Hashtable valueMapping, String key) {
		Integer idx = (Integer)componentMapping.get(key);
		if (idx != null && valueMapping.get(idx) != null) {
			return getValue(valueMapping, idx.intValue());
		}
		return null;
	}

	/**
	 * 	Create Query
	 *  <code>
	 * 	Available synonyms:
	 *		M_Product p
	 *		M_ProductPrice pr
	 *		M_AttributeSet pa
	 *	</code>
	 *	@return query
	 */
	private String createQuery(Hashtable valueMapping, SimpleDateFormat dateFormat)
	{
		/** Base Query
		SELECT *
		FROM M_Product p
 		 INNER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID)
 		 LEFT OUTER JOIN M_AttributeSet pa ON (p.M_AttributeSet_ID=pa.M_AttributeSet_ID)
		WHERE
		**/
		
		/***	Instance Attributes		*/	
		StringBuffer sb = new StringBuffer();

		//	Serial No
		String s = getComponetValue(valueMapping, "SerNo");
		if (s != null)
		{
			sb.append(" AND asi.SerNo");
			if (s.indexOf('%') == -1 && s.indexOf('_') == 1)
				sb.append("=");
			else
				sb.append(" LIKE ");
			sb.append(DB.TO_STRING(s));
		}

		//	Lot Number
		s = getComponetValue(valueMapping, "Lot");
		if (s != null) {
			sb.append(" AND asi.Lot");
			if (s.indexOf('%') == -1 && s.indexOf('_') == 1)
				sb.append("=");
			else
				sb.append(" LIKE ");
			sb.append(DB.TO_STRING(s));
		}

		//	Lot ID
		s = getComponetValue(valueMapping, "M_Lot_ID");
		if (s != null) {
			int ID = Integer.parseInt(s);
			sb.append(" AND asi.M_Lot_ID=").append(ID);
		}

		//	Guarantee Date
		s = getComponetValue(valueMapping, "GuaranteeDate");
		if (s != null) {
			try {
				Date d = dateFormat.parse(s);
				Timestamp ts = new Timestamp(d.getTime());
				sb.append(" AND TRUNC(asi.GuaranteeDate)");

				String ss = "<";
				s = getComponetValue(valueMapping, "GuaranteeDateSelection");
				if (s != null) {
					if (s.indexOf("=") >= 0) {
						ss = "=";
					} else if (ss.indexOf(">") >= 0) {
						ss = ">";
					}
				}
				sb.append(ss);
				sb.append(DB.TO_DATE(ts,true));
			} catch (Exception e) {
				log.log(Level.SEVERE, "InfoPAttribute : createQuery : GuaranteeDate", e);
			}
		}

		//	Instance Editors
		for (int i = 0; i < m_instanceEditors.size(); i++)
		{
			StringBuffer iAttr = new StringBuffer();
			Hashtable attr = m_instanceEditors.get(i);
			Hashtable attrTo = m_instanceEditorsTo.get(i);
			int idx = (Integer)attr.get("idx");
			int attribute_ID = (Integer)attr.get("attribute_ID");
			int idxTo = (Integer)attrTo.get("idx");
			ComponentBean cb = (ComponentBean)componentList.get(idx);
			if (cb == null) {
				continue;
			}

			String val = getValue(valueMapping, idx);
			String valTo = getValue(valueMapping, idxTo);

			int M_Attribute_ID = attribute_ID;
			if (cb.getType() == ComponentBean.TYPE_SELECT)
			{
				if (val != null) {
					iAttr.append("M_Attribute_ID=").append(M_Attribute_ID)
						.append(" AND M_AttributeValue_ID=").append(val);
				}
			}
			else if (cb.getType() == ComponentBean.TYPE_NUMBER)
			{
				BigDecimal value = null;
				BigDecimal valueTo = null;
				try {
					value = new BigDecimal(val);
					valueTo = new BigDecimal(valTo);
				} catch (Exception e) {
					log.log(Level.SEVERE, "InfoPAttribute : createQuery : To BigDecimal", e);
				}
				if (value != null || valueTo != null)
				{
					iAttr.append("M_Attribute_ID=").append(M_Attribute_ID)
						.append(" AND ValueNumber");
					if (value != null && valueTo == null)
						iAttr.append("=").append(value);
					else if (value == null && valueTo != null)
						iAttr.append("<=").append(valueTo);
					else if (value != null && valueTo != null)
						iAttr.append(" BETWEEN ").append(value)
							.append(" AND ").append(valueTo);
				} 
			}
			else
			{
				String value = val;
				if (value != null && value.length() > 0)
				{
					iAttr.append("M_Attribute_ID=").append(M_Attribute_ID)
						.append(" AND Value");
					if (value.indexOf('%') == -1 && value.indexOf('_') == -1)
						iAttr.append("=");
					else
						iAttr.append(" LIKE ");
					iAttr.append(DB.TO_STRING(value));
				}
			}

			//	Add to where
			if (iAttr.length() > 0)
				sb.append(" AND asi.M_AttributeSetInstance_ID IN "
					+ "(SELECT M_AttributeSetInstance_ID FROM M_AttributeInstance "
					+ "WHERE ")
					.append(iAttr).append(")");
		}

		//	finish Instance Attributes
		if (sb.length() > 0)
		{
			sb.insert(0, " AND EXISTS (SELECT * FROM M_Storage s"
				+ " INNER JOIN M_AttributeSetInstance asi ON (s.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID) "
				+ "WHERE s.M_Product_ID=p.M_Product_ID");
			sb.append(")");
		}

		//	Product Attributes 
		for (int i = 0; i < m_productEditors.size(); i++)
		{
			StringBuffer pAttr = new StringBuffer();
			Hashtable attr = m_productEditors.get(i);
			Hashtable attrTo = m_productEditorsTo.get(i);
			int idx = (Integer)attr.get("idx");
			int attribute_ID = (Integer)attr.get("attribute_ID");
			int idxTo = (Integer)attrTo.get("idx");
			ComponentBean cb = (ComponentBean)componentList.get(idx);
			if (cb == null) {
				continue;
			}

			String val = getValue(valueMapping, idx);
			String valTo = getValue(valueMapping, idxTo);
			int M_Attribute_ID = attribute_ID;
			if (cb.getType() == ComponentBean.TYPE_SELECT)
			{
				if (val != null) {
					pAttr.append("M_Attribute_ID=").append(M_Attribute_ID)
						.append(" AND M_AttributeValue_ID=").append(val);
				} 
			}
			else if (cb.getType() == ComponentBean.TYPE_NUMBER)
			{
				BigDecimal value = new BigDecimal(val);
				BigDecimal valueTo = new BigDecimal(valTo);
				try {
					value = new BigDecimal(val);
					valueTo = new BigDecimal(valTo);
				} catch (Exception e) {
					log.log(Level.SEVERE, "InfoPAttribute : createQuery : To BigDecimal", e);
				}
				if (value != null || valueTo != null)
				{
					pAttr.append("M_Attribute_ID=").append(M_Attribute_ID)
						.append(" AND ValueNumber");
					if (value != null && valueTo == null)
						pAttr.append("=").append(value);
					else if (value == null && valueTo != null)
						pAttr.append("<=").append(valueTo);
					else if (value != null && valueTo != null)
						pAttr.append(" BETWEEN ").append(value)
							.append(" AND ").append(valueTo);
				} 
			}
			else
			{
				String value = val;
				if (value != null && value.length() > 0)
				{
					pAttr.append("M_Attribute_ID=").append(M_Attribute_ID)
						.append(" AND Value");
					if (value.indexOf('%') == -1 && value.indexOf('_') == -1)
						pAttr.append("=");
					else
						pAttr.append(" LIKE ");
					pAttr.append(DB.TO_STRING(value));
				}
			}

			//	Add to Where
			if (pAttr.length() > 0)
				sb.append(" AND p.M_AttributeSetInstance_ID IN "
					+ "(SELECT M_AttributeSetInstance_ID "
					+ "FROM M_AttributeInstance WHERE ")
					.append(pAttr).append(")");
		}

		//
		if (sb.length() > 0) {
			return sb.toString();
		} else {
			return null;
		}
	}	//	createQuery

	/**
	 * 	Get resulting Query WHERE
	 *	@return query or null
	 */
	public String getWhereClause(String where, SimpleDateFormat dateFormat)
	{
		if (where == null || where.length() == 0) {
			return null;
		}

		String p[] = where.split(";");
		if (p == null || p.length == 0) {
			return null;
		}

		Hashtable<Integer, String> valueMapping = new Hashtable<Integer, String>();
		for (int i = 0; i < p.length; i++) {
			String pp[] = p[i].split(",");
			if (pp == null || pp.length != 2) {
				continue;
			}

			try {
				valueMapping.put(new Integer(Integer.parseInt(pp[0])), pp[1]);
			} catch (Exception e) {
				log.log(Level.SEVERE, "InfoPAttribute : getWhereClause", e);
			}
		}

		String query = createQuery(valueMapping, dateFormat);
		log.config(query);
		return query;
	}	//	getQuery

	/**
	 * @return
	 */
	public ArrayList<ComponentBean> getComponentList() {
		return componentList;
	}
}	//	InfoPAttribute
