/******************************************************************************
 * Product: Compiere ERP & CRM Smart Business Solution                        *
 * Copyright (C) 1999-2007 ComPiere, Inc. All Rights Reserved.                *
 * This program is free software, you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY, without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program, if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 * For the text or an alternative of this public license, you may reach us    *
 * ComPiere, Inc., 3600 Bridge Parkway #102, Redwood City, CA 94065, USA      *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.compiere.udf;

import java.io.*;
import java.math.*;
import java.sql.*;
import java.util.*;
import COM.ibm.db2.app.*;


/**
 *	SQLJ Compiere Control and Utility Class
 *	
 *  @author Jorg Janke
 *  @version $Id: Compiere.java,v 1.1 2008/01/16 08:15:54 jrmt Exp $
 */

public class Compiere extends UDF implements Serializable
{
	private static Compiere  cInst = null;
	/**
	 * 	Get Compiere Instance
	 *	@return Compiere Instance
	 */
	public static Compiere getInstance()
	{
		/*
		if (cInst == null)
		{
			synchronized (ONE)
			{
				if (cInst == null)
					cInst = new Compiere();
			}
		}
		return cInst;
		*/
		
		return new Compiere();
	}	//	version
	
	/**
	 * 	Get Version
	 *	@return version
	 */
	public static String getVersion()
	{
		return "Compiere SQLJ 1.0";
	}	//	version
	
	/**
	 * 	Get Environment Info
	 *	@return properties
	 */
	public static String getProperties()
	{
		StringBuffer sb = new StringBuffer();
		Enumeration en = System.getProperties().keys();
		while (en.hasMoreElements())
		{
			if (sb.length() != 0)
				sb.append(" - ");
			String key = (String)en.nextElement();
			String value = System.getProperty(key);
			sb.append(key).append("=").append(value);
		}
		return sb.toString();
	}	//	environment

	/**
	 * 	Get Environment Info
	 * 	@param key key
	 *	@return property info
	 *	@throws SQLException
	 */
	public static String getProperty (String key) throws SQLException
	{
		if (key == null || key.length() == 0)
			return "null";
		return System.getProperty(key, "NotFound");
	}	//	environment

	/** Oracle Server					*/
	public static final String TYPE_ORACLE = "oracle";
	/** DB2 Server						*/
	public static final String TYPE_DB2 = "db2";
	/** Server Type						*/
	public static String 	s_type = TYPE_DB2;
	
	/**
	 * 	Get Server Type
	 *	@return server type
	 */
	public static String getServerType()
	{
		if (s_type == null)
		{
			String vendor = System.getProperty("java.vendor");
			if (vendor.startsWith("Oracle"))
				s_type = TYPE_ORACLE;
			else if (vendor.startsWith("IBM"))
				s_type = TYPE_DB2;
			else
				s_type = "??";
		}
		return s_type;
	}	//	getServerType
	
	/**
	 * 	Is this Oracle ?
	 *	@return true if Oracle
	 */
	static boolean isOracle()
	{
		if (s_type == null)
			getServerType();
		if (s_type != null)
			return TYPE_ORACLE.equals(s_type);
		return false;
	}	//	isOracle
	
	/**
	 * 	Is this DB2 ?
	 *	@return true if DB2
	 */
	static boolean isDB2()
	{
		if (s_type == null)
			getServerType();
		if (s_type != null)
			return TYPE_DB2.equals(s_type);
		return false;
	}	//	isDB2
	
	
	/**
	 * 	Get Connection URL
	 *	@return connection URL
	 */
	static String getConnectionURL()
	{
		if (s_url != null)
			return s_url;
		
		s_url = "jdbc:default:connection:";
		if (isDB2())
			s_url = "jdbc:default:connection";
		//
		return s_url;
	}	//	getConnectionURL
	
	/**	Connection URL				*/
	protected static String		s_url = null;
	/**	Connection User				*/
	protected static String		s_uid = null;
	/**	Connection Password			*/
	protected static String		s_pwd = null;
	
	/**
	 * 	Get Server side Connection
	 *	@return connection
	 *	@throws SQLException
	 */
	/*
	private static Connection getConnection() throws SQLException
	{
		if (s_uid != null && s_pwd != null)
			return DriverManager.getConnection(getConnectionURL(), s_uid, s_pwd);
		return DriverManager.getConnection(getConnectionURL());
	}	//	getConnection
	*/

	/**
	 * 	Prepare Statement (Forward, ReadOnly)
	 *	@param sql sql
	 *	@return prepared statement
	 *	@throws SQLException
	 */
	PreparedStatement prepareStatement (String sql) throws SQLException
	{
		return prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	}	//	prepareStatement
	
	/**
	 * 	Prepare Statement
	 *	@param sql sql
	 *	@param resultSetType result set type
	 *	@param resultSetCurrency result type currency
	 *	@return prepared statement
	 *	@throws SQLException
	 */
	PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetCurrency)
		throws SQLException
	{
		synchronized (ONE) //jz although diff VM with setConnection
		{
			try
			{

				if (s_conn == null)
					s_conn = getConnection();
			}
			catch (Exception e)	//	connection not good anymore
			{
			}
		}
		try
		{
			if (s_conn != null)
			{
				PreparedStatement ps = s_conn.prepareStatement(sql, resultSetType, resultSetCurrency);
				s_conn1 = null;  //jz no more need it here
				return ps;
			}
		}
		catch (Exception e)	//	connection not good anymore
		{
		}
		//	get new Connection
		synchronized (ONE)
		{
			//s_conn is bad at this time
			if (s_conn1 != null)
			{
				s_conn = s_conn1;
				s_conn1 = null;
			}
			else //try this at last
			try
			{
				s_conn = getConnection();
			}
			catch (Exception e)	//	connection not good anymore
			{
			}
		}
		return s_conn.prepareStatement(sql);
	}	//	

	/**
	 * 	Get SQL int Value with param 
	 *	@param sql sql command
	 *	@param param1 parameter
	 *	@return value or -1 if not found
	 *	@throws SQLException
	 */
	int getSQLValue (String sql, int param1) throws SQLException
	{
		int retValue = -1;
		PreparedStatement pstmt = prepareStatement(sql);
		pstmt.setInt(1, param1);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next())
			retValue = rs.getInt(1);
		rs.close();
		pstmt.close();
		return retValue;
	}	//	getSQLValue
	
	static public void setConnection(Connection conn)
	{
		Compiere c = getInstance();
		c.setConnection(conn, 0);
	}
		
	public void setConnection(Connection conn, int sig)
	{
		if (!(conn instanceof Connection))
			return;
		synchronized (ONE)
		{
			if (s_conn != null)
			{
				if (s_conn == conn) //same reference, no need to set again
					return;
				else if (s_conn1 == null)
					s_conn1= conn; //as 2nd connection in the case the connection from others is bad
			}
			else
				s_conn = conn;
		}
	}
	/** Permanently open Connection		*/
	//jz seems DB2 doesn't like static connection
	//private static Connection s_conn = null;
	//private static Connection s_conn1 = null;
	private  Connection s_conn = null;
	private  Connection s_conn1 = null;
	
	/** Zero 0				*/
	public static final BigDecimal ZERO = new BigDecimal((double)0.0);
	/** One 1				*/
	public static final BigDecimal ONE = new BigDecimal((double)1.0);
	/** Hundred 100				*/
	public static final BigDecimal HUNDRED = new BigDecimal((double)100.0);
	

	/**
	 * 	Truncate Date
	 *	@param p_dateTime date
	 *	@param fmt String, 'D', 'M', 'Y', etc. ignored for now
	 *	@return day
	 */
	/* jz use the one from TimeUtil.java
	public static Timestamp trunc (Timestamp p_dateTime, String fmt)
	{
		Timestamp time = p_dateTime;
		if (time == null)
			time = new Timestamp(System.currentTimeMillis());
		//
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(time);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		//
		java.util.Date temp = cal.getTime();
		return new Timestamp (temp.getTime());
	}	//	trunc
	
	*/
	
	/** Truncate Day - D			*/
	public static final String	TRUNC_DAY = "DD"; //jz user Oracle format 'DD', 'DY'
	/** Truncate Week - W			*/
	public static final String	TRUNC_WEEK = "DY";
	/** Truncate Month - MM			*/
	public static final String	TRUNC_MONTH = "MM";
	/** Truncate Quarter - Q		*/
	public static final String	TRUNC_QUARTER = "Q";
	/** Truncate Year - Y			*/
	public static final String	TRUNC_YEAR = "Y";
	
	/**
	 * 	Get truncated day/time
	 *  @param dayTime day
	 *  @param trunc how to truncate TRUNC_*
	 *  @return next day with 00:00
	 */
	static public Timestamp trunc (Timestamp dayTime, String trunc)
	{
		if (dayTime == null)
			dayTime = new Timestamp(System.currentTimeMillis());
		//jz GregorianCalendar cal = new GregorianCalendar(Language.getLoginLanguage().getLocale());
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTimeInMillis(dayTime.getTime());
		cal.set(Calendar.MILLISECOND, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MINUTE, 0);
		//	D
		cal.set(Calendar.HOUR_OF_DAY, 0);
		if (trunc == null || trunc.equals(TRUNC_DAY))
			return new Timestamp (cal.getTimeInMillis());
		//	W
		if (trunc.equals(TRUNC_WEEK))
		{
			cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek());
			return new Timestamp (cal.getTimeInMillis());
		}
		// MM
		cal.set(Calendar.DAY_OF_MONTH, 1);
		if (trunc.equals(TRUNC_MONTH))
			return new Timestamp (cal.getTimeInMillis());
		//	Q
		if (trunc.equals(TRUNC_QUARTER))
		{
			int mm = cal.get(Calendar.MONTH);
			if (mm < 4)
				mm = 1;
			else if (mm < 7)
				mm = 4;
			else if (mm < 10)
				mm = 7;
			else
				mm = 10;
			cal.set(Calendar.MONTH, mm);
			return new Timestamp (cal.getTimeInMillis());
		}
		cal.set(Calendar.DAY_OF_YEAR, 1);
		return new Timestamp (cal.getTimeInMillis());
	}	//	trunc
	


	//jz to make work for both java in this package and function written in the code
	protected static Timestamp trunc (Timestamp t)
	{
		return trunc(t, "DD");
	}
	/**
	 * 	Truncate Date
	 *	@param p_dateTime date
	 *	@param XX date part - Supported: DD(default),DY,MM,Q 
	 *	@return day (first)
	 */
	public static Timestamp firstOf (Timestamp p_dateTime, String XX)
	{
		Timestamp time = p_dateTime;
		if (time == null)
			time = new Timestamp(System.currentTimeMillis());
		//
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(time);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		//
		if ("MM".equals(XX))					//	Month
			cal.set(Calendar.DAY_OF_MONTH, 1);
		else if ("DY".equals(XX))				//	Week
			cal.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
		else if ("Q".equals(XX))				//	Quarter
		{
			cal.set(Calendar.DAY_OF_MONTH, 1);
			int mm = cal.get(Calendar.MONTH);	//	January = 0
			if (mm < Calendar.APRIL)
				cal.set(Calendar.MONTH, Calendar.JANUARY);
			else if (mm < Calendar.JULY)
				cal.set(Calendar.MONTH, Calendar.APRIL);
			else if (mm < Calendar.OCTOBER)
				cal.set(Calendar.MONTH, Calendar.JULY);
			else
				cal.set(Calendar.MONTH, Calendar.OCTOBER);
		}
		//
		java.util.Date temp = cal.getTime();
		return new Timestamp (temp.getTime());
	}	//	trunc
	
	/**
	 * 	Calculate the number of days between start and end.
	 * 	@param start start date
	 * 	@param end end date
	 * 	@return number of days (0 = same)
	 */
	static public int getDaysBetween (Timestamp start, Timestamp end)
	{
		boolean negative = false;
		
		if (start == null && end == null)
			return 0;
		if (start == null)
			start = new Timestamp(new java.util.Date().getTime());
		if (end == null)
			end = new Timestamp(new java.util.Date().getTime());
		
		if (end.before(start))
		{
			negative = true;
			Timestamp temp = start;
			start = end;
			end = temp;
		}
		//
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(start);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		GregorianCalendar calEnd = new GregorianCalendar();
		calEnd.setTime(end);
		calEnd.set(Calendar.HOUR_OF_DAY, 0);
		calEnd.set(Calendar.MINUTE, 0);
		calEnd.set(Calendar.SECOND, 0);
		calEnd.set(Calendar.MILLISECOND, 0);

	//	System.out.println("Start=" + start + ", End=" + end + ", dayStart=" + cal.get(Calendar.DAY_OF_YEAR) + ", dayEnd=" + calEnd.get(Calendar.DAY_OF_YEAR));

		//	in same year
		if (cal.get(Calendar.YEAR) == calEnd.get(Calendar.YEAR))
		{
			if (negative)
				return (calEnd.get(Calendar.DAY_OF_YEAR) - cal.get(Calendar.DAY_OF_YEAR)) * -1;
			return calEnd.get(Calendar.DAY_OF_YEAR) - cal.get(Calendar.DAY_OF_YEAR);
		}

		//	not very efficient, but correct
		int counter = 0;
		while (calEnd.after(cal))
		{
			cal.add (Calendar.DAY_OF_YEAR, 1);
			counter++;
		}
		if (negative)
			return counter * -1;
		return counter;
	}	//	getDaysBetween

	/**
	 * 	Return Day + offset (truncates)
	 * 	@param day Day
	 * 	@param offset day offset
	 * 	@return Day + offset at 00:00
	 */
	static public Timestamp addDays (Timestamp day, int offset)
	{
		if (day == null)
			day = new Timestamp(System.currentTimeMillis());
		//
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(day);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		if (offset != 0)
			cal.add(Calendar.DAY_OF_YEAR, offset);	//	may have a problem with negative (before 1/1)
		//
		java.util.Date temp = cal.getTime();
		return new Timestamp (temp.getTime());
	}	//	addDays

	/**
	 * 	Next Business Day.
	 * 	(Only Sa/Su -> Mo)
	 *	@param day day
	 *	@return next business dat if day is "off"
	 */
	static public Timestamp nextBusinessDay (Timestamp day)
	{
		if (day == null)
			day = new Timestamp(System.currentTimeMillis());
		//
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(day);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		//
		int dow = cal.get(Calendar.DAY_OF_WEEK);
		if (dow == Calendar.SATURDAY)
			cal.add(Calendar.DAY_OF_YEAR, 2);
		else if (dow == Calendar.SUNDAY)
			cal.add(Calendar.DAY_OF_YEAR, 1);
		//
		java.util.Date temp = cal.getTime();
		return new Timestamp (temp.getTime());
	}	//	nextBusinessDay	
	
	
	/**
	 * 	Character At Position
	 *	@param source source
	 *	@param posIndex position 1 = first
	 *	@return substring or null
	 */
	public static String charAt (String source, int posIndex)
	{
		if (source == null || source.length() == 0 || posIndex < 0 || posIndex >= source.length())
			return null;
		try
		{
			return (source.substring(posIndex+1, posIndex+2));
		}
		catch (Exception e)
		{}
		return null;
	}	//	charAt
	
	/**
	 * 	Mext ID
	 *	@param AD_Sequence_ID sequence
	 *	@param System system
	 *	@return ID or -1
	 *	@throws SQLException
	 */
	public int nextID (int AD_Sequence_ID, String System)
		throws SQLException, Exception
	{
		boolean isSystem = System != null && "Y".equals(System);
		int retValue = -1;
		StringBuffer sql = new StringBuffer ("SELECT CurrentNext");
		if (isSystem)
			sql.append("Sys");
		sql.append(",IncrementNo FROM AD_Sequence WHERE AD_Sequence_ID=? FOR UPDATE");
		PreparedStatement pstmt = prepareStatement(sql.toString(),
			ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next())
		{
			retValue = rs.getInt(1);
			int incrementNo = rs.getInt(2);
			//jz
			retValue += incrementNo;
			rs.updateInt(2, retValue);
			//pstmt.getConnection().commit();
		}
		rs.close();
		pstmt.close();
		//
		return retValue;
	}	//	nextID
	
	/*
	 *  ProcNextID is a procedure version of nextID
	 *  Since Derby could not tell diff of signatures, use ProcNextID
	 */
	public void ProcNextID (int AD_Sequence_ID, String System, int[] retVal)
	throws SQLException, Exception
	{
		retVal[0] = nextID(AD_Sequence_ID, System);
	}
	
	/**
	 * 	get current Date (Timestamp)
	 *	@return Timestamp
	 *	
	 */
	
	//jz not found anywhere, add it
	public static Timestamp getDate()
	{
		return new Timestamp(new java.util.Date().getTime());
	}	//	getDate
	
	/**
	 * 	get chars from a number
	 *  @param n int
	 *	@return String
	 *	
	 */	
	/*jz not found anywhere, add it
	public static String getChars(int n)
	{
		return Integer.toString(n);
	}	//	getDate
	
	/**
	 * 	get chars from a number
	 *  @param d double
	 *	@return String
	 *	
	 */	
	//jz not found anywhere, add it
	public static String getChars(BigDecimal d)
	{
		if (d== null)
			return "0";
		String s = d.toString();
		int i = s.indexOf('.');
		boolean non0 = false;
		if (i!=-1)
			for (int j=i+1; j<s.length(); j++)
				if (s.charAt(j)!= '0')
				{
					non0 = true;
					break;
				}
		if (!non0)
			s=s.substring(0,i);
		
		if (s==null || s.length()==0)
			return "0";
		
		return s;
	}	//	getChars
	
	/**
	 * 	get chars from a number
	 *  @param d double
	 *	@return String
	 *	
	 */	
	//jz not found anywhere, add it
	public static String Time2Chars(Timestamp t )
	{
		if (t== null)
			return "";
		return t.toString();
	}	//	getChars
	
	
}	//	Compiere
