/******************************************************************************
 * 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.util.Ctx;
import org.compiere.util.KeyNamePair;
import org.compiere.util.Msg;

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

/**
 *	Search Business Partner and return selection
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: InfoBPartner.java,v 1.1 2010/01/13 11:52:01 clmg Exp $
 */
public class InfoBPartner extends Info
{
	private Info_Column[] s_partnerLayout = null;

	/**
	 *	Standard Constructor
	 *  @param WindowNo WindowNo
	 *  @param  value   Query value Name or Value if contains numbers
	 *  @param isSOTrx  if false, query vendors only
	 *  @param multiSelection multiple selection
	 *  @param whereClause where clause
	 */
	public InfoBPartner(int windowNo, String value, boolean isSOTrx,
		boolean multiSelection, String whereClause, Ctx ctx)
	{
		super(windowNo, "C_BPartner", "C_BPartner_ID", multiSelection, whereClause, ctx);

		/**  Array of Column Info    */
		s_partnerLayout = new Info_Column [11];
		int i = 0;
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Select"), "C_BPartner.C_BPartner_ID", IDColumn.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Value"), "C_BPartner.Value", String.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Name"), "C_BPartner.Name", String.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Contact"), "c.Name AS Contact", KeyNamePair.class, "c.AD_User_ID");
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "SO_CreditAvailable"), "C_BPartner.SO_CreditLimit-C_BPartner.SO_CreditUsed AS SO_CreditAvailable", BigDecimal.class, true, true, null);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "SO_CreditUsed"), "C_BPartner.SO_CreditUsed", BigDecimal.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Phone"), "c.Phone", String.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Postal"), "a.Postal", KeyNamePair.class, "l.C_BPartner_Location_ID");
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "City"), "a.City", String.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "TotalOpenBalance"), "C_BPartner.TotalOpenBalance", BigDecimal.class);
		s_partnerLayout[i++] = new Info_Column(Msg.translate(ctx, "Revenue"), "C_BPartner.ActualLifetimeValue", BigDecimal.class);

		m_keyColumnIndex = 0;

		m_isSOTrx = isSOTrx;

		//
		initInfo (value, whereClause);

		p_loadedOK = true;
	}	//	InfoBPartner

	/** SalesOrder Trx          */
	private boolean 		m_isSOTrx = false;

	/** From Clause             */
	private static String s_partnerFROM = "C_BPartner"
		+ " LEFT OUTER JOIN C_BPartner_Location l ON (C_BPartner.C_BPartner_ID=l.C_BPartner_ID AND l.IsActive='Y')"
		+ " LEFT OUTER JOIN AD_User c ON (C_BPartner.C_BPartner_ID=c.C_BPartner_ID AND (c.C_BPartner_Location_ID IS NULL OR c.C_BPartner_Location_ID=l.C_BPartner_Location_ID) AND c.IsActive='Y')" 
		+ " LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID)";
	
	//
	private String fieldValue = "";
	private String fieldName = "";
	private String fieldContact = "";
	private String fieldEMail = "";
	private String fieldPostal = "";
	private String fieldPhone = "";
	private String checkAND = "";
	private String checkCustomer = "";

	/**
	 *	Dynamic Init
	 *  @param value value
	 *  @param whereClause where clause
	 */
	private void initInfo(String value, String whereClause)
	{
		/**	From
			C_BPartner
			 LEFT OUTER JOIN C_BPartner_Location l ON (C_BPartner.C_BPartner_ID=l.C_BPartner_ID AND l.IsActive='Y') 
			 LEFT OUTER JOIN AD_User c ON (C_BPartner.C_BPartner_ID=c.C_BPartner_ID AND (c.C_BPartner_Location_ID IS NULL OR c.C_BPartner_Location_ID=l.C_BPartner_Location_ID) AND c.IsActive='Y') 
			 LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID)
		**/

		//	Create Grid
		StringBuffer where = new StringBuffer();
		where.append("C_BPartner.IsSummary='N' AND C_BPartner.IsActive='Y'");
		if (whereClause != null && whereClause.length() > 0)
			where.append(" AND ").append(whereClause);
		//
		prepareTable(s_partnerLayout, s_partnerFROM, where.toString(), "C_BPartner.Value");

		//  Set Value
		if (value == null)
			value = "%";
		if (!value.endsWith("%"))
			value += "%";

		//	Put query string in Name if not numeric
		if (value.equals("%"))
			fieldName = value;
		//	No Numbers entered
		else if ((value.indexOf("0")+value.indexOf("1")+value.indexOf("2")+value.indexOf("3")+value.indexOf("4") +value.indexOf("5")
			+value.indexOf("6")+value.indexOf("7")+value.indexOf("8")+value.indexOf("9")) == -10)
		{
			if (value.startsWith("%"))
				fieldName = value;
			else
				fieldName = "%";
		}
		//	Number entered
		else {
			fieldValue = value;
		}
	}	//	initInfo

	/*************************************************************************/

	/**
	 *	Construct SQL Where Clause and define parameters.
	 *  (setParameters needs to set parameters)
	 *  Includes first AND
	 *  @return WHERE clause
	 */
	String getSQLWhere()
	{
		ArrayList<String> list = new ArrayList<String>();
		//	=> Value
		String value = fieldValue.toUpperCase();
		if (!(value.equals("") || value.equals("%")))
			list.add ("UPPER(C_BPartner.Value) LIKE ?");
		//	=> Name
		String name = fieldName.toUpperCase();
		if (!(name.equals("") || name.equals("%")))
			list.add ("UPPER(C_BPartner.Name) LIKE ?");
		//	=> Contact
		String contact = fieldContact.toUpperCase();
		if (!(contact.equals("") || contact.equals("%")))
			list.add ("UPPER(c.Name) LIKE ?");
		//	=> EMail
		String email = fieldEMail.toUpperCase();
		if (!(email.equals("") || email.equals("%")))
			list.add ("UPPER(c.EMail) LIKE ?");
		//	=> Phone
		String phone = fieldPhone.toUpperCase();
		if (!(phone.equals("") || phone.equals("%")))
			list.add ("UPPER(c.Phone) LIKE ?");
		//	=> Postal
		String postal = fieldPostal.toUpperCase();
		if (!(postal.equals("") || postal.equals("%")))
			list.add ("UPPER(a.Postal) LIKE ?");

		StringBuffer sql = new StringBuffer();
		int size = list.size();
		//	Just one
		if (size == 1)
			sql.append(" AND ").append(list.get(0));
		else if (size > 1)
		{
			boolean AND = ("true".equalsIgnoreCase(checkAND));
			sql.append(" AND ");
			if (!AND)
				sql.append("(");
			for (int i = 0; i < size; i++)
			{
				if (i > 0)
					sql.append(AND ? " AND " : " OR ");
				sql.append(list.get(i));
			}
			if (!AND)
				sql.append(")");
		}

		//	Static SQL
		if ("true".equalsIgnoreCase(checkCustomer))
		{
			sql.append(" AND ");
			if (m_isSOTrx)
				sql.append ("C_BPartner.IsCustomer='Y'");
			else
				sql.append ("C_BPartner.IsVendor='Y'");
		}
		return sql.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;
		//	=> 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);
		}
		//	=> Contact
		String contact = fieldContact.toUpperCase();
		if (!(contact.equals("") || contact.equals("%")))
		{
			if (!contact.endsWith("%"))
				contact += "%";
			pstmt.setString(index++, contact);
			log.fine("Contact: " + contact);
		}
		//	=> EMail
		String email = fieldEMail.toUpperCase();
		if (!(email.equals("") || email.equals("%")))
		{
			if (!email.endsWith("%"))
				email += "%";
			pstmt.setString(index++, email);
			log.fine("EMail: " + email);
		}
		//	=> Phone
		String phone = fieldPhone.toUpperCase();
		if (!(phone.equals("") || phone.equals("%")))
		{
			if (!phone.endsWith("%"))
				phone += "%";
			pstmt.setString(index++, phone);
			log.fine("Phone: " + phone);
		}
		//	=> Postal
		String postal = fieldPostal.toUpperCase();
		if (!(postal.equals("") || postal.equals("%")))
		{
			if (!postal.endsWith("%"))
				postal += "%";
			pstmt.setString(index++, postal);
			log.fine("Postal: " + postal);
		}
	}   //  setParameters

	/*************************************************************************/

	/**
	 * Set query parameters
	 */
	public void setQueryParas(InfoBPartnerForm searchInfoForm) {
		fieldValue = (searchInfoForm.getValue() == null ? "" : searchInfoForm.getValue());
		fieldName = (searchInfoForm.getName() == null ? "" : searchInfoForm.getName());
		fieldContact = (searchInfoForm.getContact() == null ? "" : searchInfoForm.getContact());
		fieldEMail = (searchInfoForm.getEmail() == null ? "" : searchInfoForm.getEmail());
	    fieldPhone = (searchInfoForm.getPhone() == null ? "" : searchInfoForm.getPhone());
		fieldPostal = (searchInfoForm.getPostal() == null ? "" : searchInfoForm.getPostal());
		checkAND = (searchInfoForm.isCheckANDStatus() ? "true" : "false");
		checkCustomer = (searchInfoForm.isCheckCustomerStatus() ? "true" : "false");
	}	//	setQueryParas
}
