/******************************************************************************
 * 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.SQLException;
import java.util.ArrayList;
import java.util.Properties;

import org.compiere.model.MClient;
import org.compiere.util.Ctx;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.compiere.util.Util;

import com.ampiere.web.struts.search.InfoProductForm;

/**
 *	Search Product and return selection
 *
 *  @author     Jorg Janke
 *  @version    $Id: InfoProduct.java,v 1.1 2010/01/13 11:52:01 clmg Exp $
 */
public final class InfoProduct extends Info
{
	/**
	 *	Standard Constructor
	 * 	@param WindowNo window no
	 * 	@param M_Warehouse_ID warehouse
	 * 	@param M_PriceList_ID price list
	 * 	@param value    Query Value or Name if enclosed in @
	 * 	@param multiSelection multiple selections
	 * 	@param whereClause where clause
	 */
	public InfoProduct(int WindowNo, int M_Warehouse_ID, int M_PriceList_ID, String value,
		boolean multiSelection, String whereClause, Ctx ctx)
	{
		super (WindowNo, "p", "M_Product_ID", multiSelection, whereClause, ctx);
		log.info(value + ", Wh=" + M_Warehouse_ID + ", PL=" + M_PriceList_ID + ", WHERE=" + whereClause);

		initInfo(value, M_Warehouse_ID, M_PriceList_ID, ctx);

		p_loadedOK = true;
	}	//	InfoProduct

	/** SQL From				*/
	private static final String s_productFrom =
		"M_Product p"
		+ " LEFT OUTER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID AND pr.IsActive='Y')"
		+ " LEFT OUTER JOIN M_AttributeSet pa ON (p.M_AttributeSet_ID=pa.M_AttributeSet_ID)";

	/**  Array of Column Info    */
	private Info_Column[] s_productLayout = null;

	//
	private String fieldValue = "";
	private String fieldName = "";
	private String fieldUPC = "";
	private String fieldSKU = "";
	private String pickWarehouse = "";
	private String pickPriceList = "";
	private String m_pAttributeWhere = null;

	/**
	 *	Dynamic Init
	 *
	 * @param value value
	 * @param M_Warehouse_ID warehouse
	 * @param M_PriceList_ID price list
	 */
	private void initInfo (String value, int M_Warehouse_ID, int M_PriceList_ID, Ctx ctx)
	{
		//	Set Value or Name
		if (value.startsWith("@") && value.endsWith("@"))
			fieldName = value.substring(1,value.length()-1);
		else
			fieldValue = value;

		//	Create Grid
		StringBuffer where = new StringBuffer();
		where.append("p.IsActive='Y'");
		if (M_Warehouse_ID != 0)
			where.append(" AND p.IsSummary='N'");
		//  dynamic Where Clause
		if (p_whereClause != null && p_whereClause.length() > 0)
			where.append(" AND ")   //  replace fully qalified name with alias
				.append(Util.replace(p_whereClause, "M_Product.", "p."));
		//
		prepareTable(getProductLayout(ctx), s_productFrom, where.toString(), "QtyAvailable DESC, Margin DESC");
	}	//	initInfo

	/**************************************************************************
	 *	Construct SQL Where Clause and define parameters
	 *  (setParameters needs to set parameters)
	 *  Includes first AND
	 *  @return SQL WHERE clause
	 */
	String getSQLWhere()
	{
		StringBuffer where = new StringBuffer();
		
		//	Optional PLV
		int M_PriceList_Version_ID = 0;
		if (pickPriceList != null && ! "".equals(pickPriceList)) {
			M_PriceList_Version_ID = Integer.parseInt(pickPriceList);
		}
		if (M_PriceList_Version_ID != 0)
			where.append(" AND pr.M_PriceList_Version_ID=?");

		//	Product Attribute Search
		if (m_pAttributeWhere != null)
		{
			where.append(m_pAttributeWhere);
			return where.toString();
		}

		//  => Value
		String value = fieldValue.toUpperCase();
		if (!(value.equals("") || value.equals("%")))
			where.append(" AND UPPER(p.Value) LIKE ?");

		//  => Name
		String name = fieldName.toUpperCase();
		if (!(name.equals("") || name.equals("%")))
			where.append(" AND UPPER(p.Name) LIKE ?");

		//  => UPC
		String upc = fieldUPC.toUpperCase();
		if (!(upc.equals("") || upc.equals("%")))
			where.append(" AND UPPER(p.UPC) LIKE ?");

		//  => SKU
		String sku = fieldSKU.toUpperCase();
		if (!(sku.equals("") || sku.equals("%")))
			where.append(" AND UPPER(p.SKU) LIKE ?");

		return where.toString();
	}	//	getSQLWhere

	/**
	 *  Set Parameters for Query
	 *  (as defined in getSQLWhere)
	 *
	 * @param pstmt pstmt
	 *  @param forCount for counting records
	 * @throws SQLException
	 */
	void setParameters(PreparedStatement pstmt, boolean forCount) throws SQLException
	{
		int index = 1;

		if (!forCount)	//	parameters in select
		{
			//  => Warehouse
			int M_Warehouse_ID = 0;
			if (pickWarehouse != null && ! "".equals(pickWarehouse)) {
				M_Warehouse_ID = Integer.parseInt(pickWarehouse);
			}
			for (int i = 0; i < p_layout.length; i++)
			{
				if (p_layout[i].getColSQL().indexOf("?") != -1)
					pstmt.setInt(index++, M_Warehouse_ID);
			}
			log.fine("M_Warehouse_ID=" + M_Warehouse_ID + " (" + (index-1) + "*)");
		}

		//  => PriceList
		int M_PriceList_Version_ID = 0;
		if (pickPriceList != null && ! "".equals(pickPriceList)) {
			M_PriceList_Version_ID = Integer.parseInt(pickPriceList);
		}
		if (M_PriceList_Version_ID != 0)
		{
			pstmt.setInt(index++, M_PriceList_Version_ID);
			log.fine("M_PriceList_Version_ID=" + M_PriceList_Version_ID);
		}

		//  => Value
		String value = fieldValue.toUpperCase();
		if (!(value.equals("") || value.equals("%")))
		{
			if (!value.endsWith("%"))
				value += "%";
			pstmt.setString(index++, value);
			log.fine("Value: " + value);
		}

		//  => Name
		String name = fieldName.toUpperCase();
		if (!(name.equals("") || name.equals("%")))
		{
			if (!name.endsWith("%"))
				name += "%";
			pstmt.setString(index++, name);
			log.fine("Name: " + name);
		}

		//  => UPC
		String upc = fieldUPC.toUpperCase();
		if (!(upc.equals("") || upc.equals("%")))
		{
			if (!upc.endsWith("%"))
				upc += "%";
			pstmt.setString(index++, upc);
			log.fine("UPC: " + upc);
		}

		//  => SKU
		String sku = fieldSKU.toUpperCase();
		if (!(sku.equals("") || sku.equals("%")))
		{
			if (!sku.endsWith("%"))
				sku += "%";
			pstmt.setString(index++, sku);
			log.fine("SKU: " + sku);
		}

	}   //  setParameters

	/**
	 *  Get Product Layout
	 *
	 * @return array of Column_Info
	 */
	private Info_Column[] getProductLayout(Ctx ctx)
	{
		if (s_productLayout != null)
			return s_productLayout;

		//  Euro 13
		MClient client = MClient.get(ctx);
		if ("FRIE".equals(client.getValue()))
		{
			final Info_Column[] frieLayout = {
				new Info_Column(Msg.translate(ctx, "Select"), "p.M_Product_ID", IDColumn.class),
				new Info_Column(Msg.translate(ctx, "Name"), "p.Name", String.class),
				new Info_Column(Msg.translate(ctx, "QtyAvailable"), "bomQtyAvailable(p.M_Product_ID,?,0) AS QtyAvailable", Double.class, true, true, null),
				new Info_Column(Msg.translate(ctx, "PriceList"), "bomPriceList(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceList",  BigDecimal.class),
				new Info_Column(Msg.translate(ctx, "PriceStd"), "bomPriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceStd", BigDecimal.class),
				new Info_Column("Einzel MWSt", "pr.PriceStd * 1.16", BigDecimal.class),
				new Info_Column("Einzel kompl", "(pr.PriceStd+13) * 1.16", BigDecimal.class),
				new Info_Column("Satz kompl", "((pr.PriceStd+13) * 1.16) * 4", BigDecimal.class),
				new Info_Column(Msg.translate(ctx, "QtyOnHand"), "bomQtyOnHand(p.M_Product_ID,?,0) AS QtyOnHand", Double.class),
				new Info_Column(Msg.translate(ctx, "QtyReserved"), "bomQtyReserved(p.M_Product_ID,?,0) AS QtyReserved", Double.class),
				new Info_Column(Msg.translate(ctx, "QtyOrdered"), "bomQtyOrdered(p.M_Product_ID,?,0) AS QtyOrdered", Double.class),
				new Info_Column(Msg.translate(ctx, "Discontinued").substring(0, 1), "p.Discontinued", Boolean.class),
				new Info_Column(Msg.translate(ctx, "Margin"), "bomPriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID)-bomPriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS Margin", BigDecimal.class),
				new Info_Column(Msg.translate(ctx, "PriceLimit"), "bomPriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceLimit", BigDecimal.class),
				new Info_Column(Msg.translate(ctx, "IsInstanceAttribute"), "pa.IsInstanceAttribute", Boolean.class)
			};
			s_productLayout = frieLayout; 
			m_keyColumnIndex = 0;
			return s_productLayout;
		}

		//
		if (s_productLayout == null)
		{
			ArrayList<Info_Column> list = new ArrayList<Info_Column>();
			list.add(new Info_Column(Msg.translate(ctx, "Select"), "p.M_Product_ID", IDColumn.class));
			list.add(new Info_Column(Msg.translate(ctx, "Discontinued").substring(0, 1), "p.Discontinued", Boolean.class));
			list.add(new Info_Column(Msg.translate(ctx, "Value"), "p.Value", String.class));
			list.add(new Info_Column(Msg.translate(ctx, "Name"), "p.Name", String.class));
			list.add(new Info_Column(Msg.translate(ctx, "QtyAvailable"), "bomQtyAvailable(p.M_Product_ID,?,0) AS QtyAvailable", Double.class, true, true, null));
			list.add(new Info_Column(Msg.translate(ctx, "PriceList"), "bomPriceList(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceList",  BigDecimal.class));
			list.add(new Info_Column(Msg.translate(ctx, "PriceStd"), "bomPriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceStd", BigDecimal.class));
			list.add(new Info_Column(Msg.translate(ctx, "QtyOnHand"), "bomQtyOnHand(p.M_Product_ID,?,0) AS QtyOnHand", Double.class));
			list.add(new Info_Column(Msg.translate(ctx, "QtyReserved"), "bomQtyReserved(p.M_Product_ID,?,0) AS QtyReserved", Double.class));
			list.add(new Info_Column(Msg.translate(ctx, "QtyOrdered"), "bomQtyOrdered(p.M_Product_ID,?,0) AS QtyOrdered", Double.class));
			if (isUnconfirmed(ctx))
			{
				list.add(new Info_Column(Msg.translate(ctx, "QtyUnconfirmed"), "(SELECT SUM(c.TargetQty) FROM M_InOutLineConfirm c INNER JOIN M_InOutLine il ON (c.M_InOutLine_ID=il.M_InOutLine_ID) INNER JOIN M_InOut i ON (il.M_InOut_ID=i.M_InOut_ID) WHERE c.Processed='N' AND i.M_Warehouse_ID=? AND il.M_Product_ID=p.M_Product_ID) AS QtyUnconfirmed", Double.class));
				list.add(new Info_Column(Msg.translate(ctx, "QtyUnconfirmedMove"), "(SELECT SUM(c.TargetQty) FROM M_MovementLineConfirm c INNER JOIN M_MovementLine ml ON (c.M_MovementLine_ID=ml.M_MovementLine_ID) INNER JOIN M_Locator l ON (ml.M_LocatorTo_ID=l.M_Locator_ID) WHERE c.Processed='N' AND l.M_Warehouse_ID=? AND ml.M_Product_ID=p.M_Product_ID) AS QtyUnconfirmedMove", Double.class));
			}
			list.add(new Info_Column(Msg.translate(ctx, "Margin"), "bomPriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID)-bomPriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS Margin", BigDecimal.class));
			list.add(new Info_Column(Msg.translate(ctx, "PriceLimit"), "bomPriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceLimit", BigDecimal.class));
			list.add(new Info_Column(Msg.translate(ctx, "IsInstanceAttribute"), "pa.IsInstanceAttribute", Boolean.class));
			s_productLayout = new Info_Column[list.size()];
			list.toArray(s_productLayout);
			m_keyColumnIndex = 0;
		}
		return s_productLayout;
	}   //  getProductLayout
	
	/**
	 * 	System has Unforfirmed records
	 *	@return true if unconfirmed
	 */
	private boolean isUnconfirmed(Ctx ctx)
	{
		int no = DB.getSQLValue(null, 
//			"SELECT COUNT(*) FROM M_InOutLineConfirm WHERE AD_Client_ID=?",  Env.getAD_Client_ID(ctx));
				"SELECT COUNT(*) FROM M_InOutLineConfirm WHERE AD_Client_ID=?",  ctx.getAD_Client_ID());
		if (no > 0)
			return true;

		no = DB.getSQLValue(null, 
//			"SELECT COUNT(*) FROM M_MovementLineConfirm WHERE AD_Client_ID=?", Env.getAD_Client_ID(ctx));
				"SELECT COUNT(*) FROM M_MovementLineConfirm WHERE AD_Client_ID=?", ctx.getAD_Client_ID());
		return (no > 0);
	}	//	isUnconfirmed

	/**
	 * Set query parameters
	 */
	public void setQueryParas(InfoProductForm searchInfoForm, String attributeWhere) {
		pickWarehouse = (searchInfoForm.getWarehouse() == null ? "" : searchInfoForm.getWarehouse());
		pickPriceList = (searchInfoForm.getPriceListVersion() == null ? "" : searchInfoForm.getPriceListVersion());
		if (attributeWhere != null && attributeWhere.length() > 0) {
			m_pAttributeWhere = attributeWhere;
		} else {
			m_pAttributeWhere = null;
			fieldValue = (searchInfoForm.getValue() == null ? "" : searchInfoForm.getValue());
			fieldName = (searchInfoForm.getName() == null ? "" : searchInfoForm.getName());
			fieldSKU = (searchInfoForm.getSku() == null ? "" : searchInfoForm.getSku());
			fieldUPC = (searchInfoForm.getUpc() == null ? "" : searchInfoForm.getUpc());
		}
	}	//	setQueryParas
}	//	InfoProduct
