/******************************************************************************
 * 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.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.MLookup;
import org.compiere.model.MLookupFactory;
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;
import org.compiere.util.Util;

/**
 *  Info Payment
 *
 *  @author Jorg Janke
 *  @version  $Id: InfoCashLine.java,v 1.1 2010/01/13 11:52:01 clmg Exp $
 */
public class InfoCashLine extends Info
{
	/**  Array of Column Info    */
	private Info_Column[] s_cashLayout = null;

	/**
	 *  Detail Protected Contructor
	 *  @param WindowNo window no
	 *  @param value query value
	 *  @param multiSelection multiple selections
	 *  @param whereClause whwre clause
	 */
	protected InfoCashLine(int WindowNo, String value, boolean multiSelection, String whereClause, Ctx ctx)
	{
		super(WindowNo, "cl", "C_CashLine_ID", multiSelection, whereClause, ctx);

		log.info( "InfoCashLine");

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

		/**  Array of Column Info    */
		int i = 0;
		s_cashLayout = new Info_Column [12];
		columnAttrList = new ArrayList<Integer>();
		s_cashLayout[i++] = new Info_Column(" ", "cl.C_CashLine_ID", IDColumn.class);
		columnAttrList.add(1);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "C_CashBook_ID"), "(SELECT cb.Name FROM C_CashBook cb WHERE cb.C_CashBook_ID=c.C_CashBook_ID)", String.class);
		columnAttrList.add(1);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "Name"), "c.Name", String.class);
		columnAttrList.add(1);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "StatementDate"), "c.StatementDate", Timestamp.class);
		columnAttrList.add(1);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "Line"), "cl.Line", Integer.class);
		columnAttrList.add(1);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "Amount"), "cl.Amount",  BigDecimal.class, true, true, null);
		columnAttrList.add(1);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "C_Invoice_ID"), "(SELECT i.DocumentNo||'_'||" + DB.TO_CHAR("i.DateInvoiced",DisplayType.Date,Env.getAD_Language(ctx))
					+ "||'_'||" + DB.TO_CHAR("i.GrandTotal",DisplayType.Amount,Env.getAD_Language(ctx))
					+ " FROM C_Invoice i WHERE i.C_Invoice_ID=cl.C_Invoice_ID)", String.class);
		columnAttrList.add(0);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "C_BankAccount_ID"),
				"(SELECT b.Name||' '||ba.AccountNo FROM C_Bank b, C_BankAccount ba WHERE b.C_Bank_ID=ba.C_Bank_ID AND ba.C_BankAccount_ID=cl.C_BankAccount_ID)", String.class);
		columnAttrList.add(0);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "C_Charge_ID"),
				"(SELECT ca.Name FROM C_Charge ca WHERE ca.C_Charge_ID=cl.C_Charge_ID)", String.class);
		columnAttrList.add(0);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "DiscountAmt"), "cl.DiscountAmt",  BigDecimal.class);
		columnAttrList.add(0);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "WriteOffAmt"), "cl.WriteOffAmt",  BigDecimal.class);
		columnAttrList.add(0);
		s_cashLayout[i++] = new Info_Column(Msg.translate(ctx, "Description"), "cl.Description", String.class);
		columnAttrList.add(0);

		//
		try
		{
			String s = "";
			if (value != null && value.length() > 0) {
				s = value;
			}
			statInit(ctx, s);
			p_loadedOK = initInfo();
		}
		catch (Exception e)
		{
			return;
		}
	}   //  InfoCashLine

	/**
	 *	Static Setup - add fields to parameterPanel
	 *  @throws Exception if Lookups cannot be created
	 */
	private void statInit(Ctx ctx, String value) throws Exception
	{
		// First Row
		// 5249 - C_Cash.C_CashBook_ID
//		MLookup lookupCashBook = MLookupFactory.get(ctx, p_WindowNo, 0, 5249, DisplayType.TableDir);
		MLookup lookupCashBook = MLookupFactory.get(ctx, p_WindowNo, 5249, DisplayType.TableDir);
		componentList.add(new ComponentBean(Msg.translate(ctx, "C_CashBook_ID"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("C_CashBook_ID", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_SELECT, getTableDirectList(lookupCashBook), AlignType.LEFT, AlignType.MIDDLE, "", 218));

		componentList.add(new ComponentBean(Msg.translate(ctx, "Name"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("Name", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_TEXT, value, AlignType.LEFT, AlignType.MIDDLE, "", 220));

		componentMapping.put("AbsoluteAmt", new Integer(componentList.size()));
		ComponentBean bean = new ComponentBean(Msg.translate(ctx, "AbsoluteAmt"), ComponentBean.TYPE_CHECKBOX, "N", AlignType.LEFT, AlignType.MIDDLE, "", 0);
		bean.setColSpan(2);
		componentList.add(bean);

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

		// 2nd Row
		// 5354 - C_CashLine.C_Invoice_ID
		// fInvoice_ID = new VLookup("C_Invoice_ID", false, false, true, MLookupFactory.get (ctx, p_WindowNo, 0, 5354, DisplayType.Search));
		componentList.add(new ComponentBean(Msg.translate(ctx, "C_Invoice_ID"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("C_Invoice_ID", new Integer(componentList.size()));
		componentList.add(new ComponentBean("C_Invoice_ID", ComponentBean.TYPE_LOOKUP, "", AlignType.LEFT, AlignType.MIDDLE, "", 198));

		componentList.add(new ComponentBean(Msg.translate(ctx, "StatementDate"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("DateFrom", new Integer(componentList.size()));
		componentList.add(new ComponentBean(Msg.translate(ctx, "DateFrom"), ComponentBean.TYPE_DATE, "", AlignType.LEFT, AlignType.MIDDLE, "", 198));
		componentList.add(new ComponentBean("-", ComponentBean.TYPE_LABEL, "", AlignType.CENTER, AlignType.MIDDLE, "", 0));
		componentMapping.put("DateTo", new Integer(componentList.size()));
		componentList.add(new ComponentBean(Msg.translate(ctx, "DateTo"), ComponentBean.TYPE_DATE, "", AlignType.LEFT, AlignType.MIDDLE, "", 198));

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

		// 3rd Row
		// 5295 - C_CashLine.C_BankAccount_ID
//		MLookup lookupBankAccount = MLookupFactory.get (ctx, p_WindowNo, 0, 5295, DisplayType.TableDir);
		MLookup lookupBankAccount = MLookupFactory.get (ctx, p_WindowNo, 5295, DisplayType.TableDir);
		componentList.add(new ComponentBean(Msg.translate(ctx, "C_BankAccount_ID"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("C_BankAccount_ID", new Integer(componentList.size()));
		componentList.add(new ComponentBean("", ComponentBean.TYPE_SELECT, getTableDirectList(lookupBankAccount), AlignType.LEFT, AlignType.MIDDLE, "", 218));

		componentList.add(new ComponentBean(Msg.translate(ctx, "Amount"), ComponentBean.TYPE_LABEL, "", AlignType.RIGHT, AlignType.MIDDLE, "", 0));
		componentMapping.put("AmtFrom", new Integer(componentList.size()));
		componentList.add(new ComponentBean(Msg.translate(ctx, "AmtFrom"), ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
		componentList.add(new ComponentBean("-", ComponentBean.TYPE_LABEL, "", AlignType.CENTER, AlignType.MIDDLE, "", 0));
		componentMapping.put("AmtTo", new Integer(componentList.size()));
		componentList.add(new ComponentBean(Msg.translate(ctx, "AmtTo"), ComponentBean.TYPE_TEXT, "", AlignType.LEFT, AlignType.MIDDLE, "", 220));
	}	// statInit

	/**
	 *	General Init
	 *	@return true, if success
	 */
	private boolean initInfo ()
	{
		//  prepare table
		StringBuffer where = new StringBuffer("cl.IsActive='Y'");
		if (p_whereClause.length() > 0)
			where.append(" AND ").append(Util.replace(p_whereClause, "C_CashLine.", "cl."));
		prepareTable (s_cashLayout,
			"C_CashLine cl INNER JOIN C_Cash c ON (cl.C_Cash_ID=c.C_Cash_ID)",
			where.toString(),
			"2,3,cl.Line");

		return true;
	}	//	initInfo

	/**
	 *	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();

		// Name
		String s = getComponetValue(valueMapping, "Name");
		if (s != null)
		{
			s = s.toUpperCase();
			if (! s.endsWith("%"))
				s += "%";
			sql.append(" AND UPPER(c.Name) LIKE ?");
			sql.append(DB.TO_STRING(s));
		}

		// Cashbook ID
		s = getComponetValue(valueMapping, "C_CashBook_ID");
		if (s != null)
		{
			sql.append(" AND c.C_CashBook_ID=");
			sql.append(Integer.parseInt(s));
		}

		// Invoice ID
		s = getComponetValue(valueMapping, "C_Invoice_ID");
		if (s != null)
		{
			sql.append(" AND cl.C_Invoice_ID=");
			sql.append(Integer.parseInt(s));
		}

		// Transaction date
		String from = getComponetValue(valueMapping, "DateFrom");
		String to = getComponetValue(valueMapping, "DateTo");
		if (from != null || to != null)
		{
			Timestamp fromT = null;
			Timestamp toT = null;
			try {
				if (from != null) {
					Date d = dateFormat.parse(from);
					fromT = new Timestamp(d.getTime());
				}
				if (to != null) {
					Date d = dateFormat.parse(to);
					toT = new Timestamp(d.getTime());
				}
			} catch (Exception e) {
				log.log(Level.SEVERE, "InfoCashLine : createQuery : Date convert error ", e);
			}

			if (fromT == null && toT != null) {
				sql.append(" AND TRUNC(c.StatementDate) <= ");
				sql.append(DB.TO_DATE(toT, true));
			} else if (fromT != null && toT == null) {
				sql.append(" AND TRUNC(c.StatementDate) >= ");
				sql.append(DB.TO_DATE(fromT, true));
			} else if (from != null && to != null) {
				sql.append(" AND TRUNC(c.StatementDate) BETWEEN ");
				sql.append(DB.TO_DATE(fromT, true));
				sql.append(" AND ");
				sql.append(DB.TO_DATE(toT, true));
			}
		}

		// Pay Amount
		from = getComponetValue(valueMapping, "AmtFrom");
		to = getComponetValue(valueMapping, "AmtTo");
		if (from != null || to != null)
		{
			BigDecimal fromB = null;
			BigDecimal toB = null;
			try {
				fromB = new BigDecimal(from);
				toB = new BigDecimal(to);
			} catch (Exception e) {
				log.log(Level.SEVERE, "InfoPayment : createQuery : To BigDecimal ", e);
			}

			// Is absolute amount
			s = getComponetValue(valueMapping, "AbsoluteAmt");
			if (s != null && "Y".equalsIgnoreCase(s)) {
				sql.append(" AND ABS(cl.Amount)");
			} else {
				sql.append(" AND cl.Amount");
			}

			if (fromB == null && toB != null) {
				sql.append(" <= ");
				sql.append(toB);
			} else if (from != null && to == null) {
				sql.append(" >= ");
				sql.append(fromB);
			} else if (from != null && to != null) {
				sql.append(" BETWEEN ");
				sql.append(fromB);
				sql.append(" AND ");
				sql.append(toB);
			}
		}

		log.fine(sql.toString());
		return sql.toString();
	}	//	getSQLWhere

	/**
	 *  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 "";
	}
}   //  InfoCashLine
