msequence.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

/******************************************************************************
 * 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 org.compiere.model;

import java.sql.*;
import java.util.*;
import java.util.logging.*;
import org.compiere.util.*;

/**
 *	Sequence Model.
 *	@see org.compiere.process.SequenceCheck
 *  @author Jorg Janke
 *  @version $Id: MSequence.java,v 1.39 2005/10/26 00:38:16 jjanke Exp $
 */
public class MSequence extends X_AD_Sequence
{
	/** Use SQL procedure to get next id			*/
	private static final boolean USE_PROCEDURE = true;
	/** Log Level for Next ID Call					*/
	private static final Level LOGLEVEL = Level.ALL;
	
	/**
	 *	Get next number for Key column = 0 is Error.
	 *  @param AD_Client_ID client
	 *  @param TableName table name
	 * 	@param trxName optional Transaction Name
	 *  @return next no or (-1=not found, -2=error)
	 */
	public static int getNextID (int AD_Client_ID, String TableName, String trxName)
	{
		if (TableName == null || TableName.length() == 0)
			throw new IllegalArgumentException("TableName missing");
		int retValue = -1;

		//	Check CompiereSys
		boolean compiereSys = Ini.isPropertyBool(Ini.P_COMPIERESYS);
		if (compiereSys && AD_Client_ID > 11)
			compiereSys = false;
		//
		if (CLogMgt.isLevel(LOGLEVEL))
			s_log.log(LOGLEVEL, TableName + " - CompiereSys=" + compiereSys  + " [" + trxName + "]");
		String selectSQL = "SELECT CurrentNext, CurrentNextSys, IncrementNo, AD_Sequence_ID "
			+ "FROM AD_Sequence "
			+ "WHERE Name=?"
			+ " AND IsActive='Y' AND IsTableID='Y' AND IsAutoSequence='Y' ";
		//	+ "FOR UPDATE";	// OF CurrentNext, CurrentNextSys";
		Trx trx = trxName == null ? null : Trx.get(trxName, true);
		Connection conn = null;
		PreparedStatement pstmt = null;
		for (int i = 0; i < 3; i++)
		{
			try
			{
				if (trx != null)
					conn = trx.getConnection();
				else
					conn = DB.getConnectionID();
				//	Error
				if (conn == null)
					return -1;
				//
				pstmt = conn.prepareStatement(selectSQL,
					ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				pstmt.setString(1, TableName);
				//
				ResultSet rs = pstmt.executeQuery();
				if (CLogMgt.isLevelFinest())
					s_log.finest("AC=" + conn.getAutoCommit() + ", RO=" + conn.isReadOnly()
						+ " - Isolation=" + conn.getTransactionIsolation() + "(" + Connection.TRANSACTION_READ_COMMITTED
						+ ") - RSType=" + pstmt.getResultSetType() + "(" + ResultSet.TYPE_SCROLL_SENSITIVE  
						+ "), RSConcur=" + pstmt.getResultSetConcurrency() + "(" + ResultSet.CONCUR_UPDATABLE
						+ ")");
				if (rs.next())
				{
					int AD_Sequence_ID = rs.getInt(4);
					//
					if (USE_PROCEDURE)
					{
						retValue = nextID(conn, AD_Sequence_ID, compiereSys);
					}
					else
					{
						int incrementNo = rs.getInt(3);
						if (compiereSys)
						{
							retValue = rs.getInt(2);
							rs.updateInt(2, retValue + incrementNo);
						}
						else
						{
							retValue = rs.getInt(1);
							rs.updateInt(1, retValue + incrementNo);
						}
						rs.updateRow();
					}
					if (trx == null)
						conn.commit();
				}
				else
					s_log.severe ("No record found - " + TableName);
				rs.close();
				pstmt.close();
				pstmt = null;
				//
			//	conn.close();
				conn = null;
				//
				break;		//	EXIT
			}
			catch (Exception e)
			{
				s_log.log(Level.SEVERE, TableName + " - " + e.getMessage(), e);
				try 
				{
					conn.rollback();
					if (pstmt != null)
						pstmt.close();
				}
				catch (SQLException e1) 
				{
				}
			}
			Thread.yield();		//	give it time
		}
		//	Finish
		try
		{
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		//	if (conn != null)
		//		conn.close();
			conn = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, "Finish", e);
			pstmt = null;
		}
		s_log.finest (retValue + " - Table=" + TableName + " [" + trx + "]");
		return retValue;
	}	//	getNextID

	/**
	 * 	Get Next ID
	 *	@param conn connection
	 *	@param AD_Sequence_ID sequence
	 *	@param compiereSys sys
	 *	@return next id or -1 (error) or -3 (parameter)
	 */
	private static int nextID (Connection conn, int AD_Sequence_ID, boolean compiereSys)
	{
		if (conn == null || AD_Sequence_ID == 0)
			return -3;
		//
		int retValue = -1;
		String sqlUpdate = "{call nextID(?,?,?)}";
		CallableStatement cstmt = null;
		try
		{
			cstmt = conn.prepareCall (sqlUpdate,
				ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			cstmt.setInt(1, AD_Sequence_ID);
			cstmt.setString(2, compiereSys ? "Y" : "N");
			cstmt.registerOutParameter(3, Types.INTEGER);
			cstmt.execute();
			retValue = cstmt.getInt(3);
			cstmt.close();
			cstmt = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, e.toString());
		}
		//	Finish
		try
		{
			if (cstmt != null)
				cstmt.close();
		}
		catch (Exception e)
		{
		}
		return retValue;
	}	//	nextID
	
	/**************************************************************************
	 * 	Get Document No from table
	 *	@param AD_Client_ID client
	 *	@param TableName table name
	 * 	@param trxName optional Transaction Name
	 *	@return document no or null
	 */
	public static synchronized String getDocumentNo (int AD_Client_ID, String TableName, String trxName)
	{
		if (TableName == null || TableName.length() == 0)
			throw new IllegalArgumentException("TableName missing");

		//	Check CompiereSys
		boolean compiereSys = Ini.isPropertyBool(Ini.P_COMPIERESYS);
		if (compiereSys && AD_Client_ID > 11)
			compiereSys = false;
		//
		if (CLogMgt.isLevel(LOGLEVEL))
			s_log.log(LOGLEVEL, TableName + " - CompiereSys=" + compiereSys  + " [" + trxName + "]");
		String selectSQL = "SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, AD_Sequence_ID "
			+ "FROM AD_Sequence "
			+ "WHERE Name=?"
			+ " AND AD_Client_ID IN (0,?)"
			+ " AND IsActive='Y' AND IsTableID='N' AND IsAutoSequence='Y' "
			+ "ORDER BY AD_Client_ID DESC ";
		//	+ "FOR UPDATE";
		Connection conn = null;
		PreparedStatement pstmt = null;
		Trx trx = trxName == null ? null : Trx.get(trxName, true);
		//
		int AD_Sequence_ID = 0;
		int incrementNo = 0;
		int next = -1;
		String prefix = "";
		String suffix = "";
		try
		{
			if (trx != null)
				conn = trx.getConnection();
			else
				conn = DB.getConnectionID();
			//	Error
			if (conn == null)
				return null;
			//
			pstmt = conn.prepareStatement(selectSQL,
				ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			pstmt.setString(1, PREFIX_DOCSEQ + TableName);
			pstmt.setInt(2, AD_Client_ID);
			//
			ResultSet rs = pstmt.executeQuery();
		//	s_log.fine("AC=" + conn.getAutoCommit() + " -Iso=" + conn.getTransactionIsolation() 
		//		+ " - Type=" + pstmt.getResultSetType() + " - Concur=" + pstmt.getResultSetConcurrency());
			if (rs.next())
			{
				AD_Sequence_ID = rs.getInt(6);
				prefix = rs.getString(4);
				suffix = rs.getString(5);
				incrementNo = rs.getInt(3);
				
				if (USE_PROCEDURE)
				{
					next = nextID(conn, AD_Sequence_ID, compiereSys);
				}
				else
				{
					if (compiereSys)
					{
						next = rs.getInt(2);
						rs.updateInt(2, next + incrementNo);
					}
					else
					{
						next = rs.getInt(1);
						rs.updateInt(1, next + incrementNo);
					}
					rs.updateRow();
				}
			}
			else
			{
				s_log.warning ("(Table) - no record found - " + TableName);
				MSequence seq = new MSequence (Env.getCtx(), AD_Client_ID, TableName, null);
				next = seq.getNextID();
				seq.save();
			}
			rs.close();
			pstmt.close();
			pstmt = null;
			//	Commit
			if (trx == null)
			{
				conn.commit();
			//	conn.close();
			}
			conn = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, "(Table) [" + trxName + "]", e);
			next = -2;
		}
		//	Finish
		try
		{
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		//	if (conn != null && trx == null)
		//		conn.close();
			conn = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, "(Table) - finish", e);
			pstmt = null;
		}
		//	Error
		if (next < 0)
			return null;
		
		//	create DocumentNo
		StringBuffer doc = new StringBuffer();
		if (prefix != null && prefix.length() > 0)
			doc.append(prefix);
		doc.append(next);
		if (suffix != null && suffix.length() > 0)
			doc.append(suffix);
		String documentNo = doc.toString();
		s_log.finer (documentNo + " (" + incrementNo + ")" 
				+ " - Table=" + TableName + " [" + trx + "]");
		return documentNo;
	}	//	getDocumentNo
	
	/**
	 * 	Get Document No based on Document Type
	 *	@param C_DocType_ID document type
	 * 	@param trxName optional Transaction Name
	 *	@return document no or null
	 */
	public static synchronized String getDocumentNo (int C_DocType_ID, String trxName)
	{
		if (C_DocType_ID == 0)
		{
			s_log.severe ("C_DocType_ID=0");
			return null;
		}
		MDocType dt = MDocType.get (Env.getCtx(), C_DocType_ID);	//	wrong for SERVER, but r/o
		if (dt != null && !dt.isDocNoControlled())
		{
			s_log.finer("DocType_ID=" + C_DocType_ID + " Not DocNo controlled");
			return null;
		}
		if (dt == null || dt.getDocNoSequence_ID() == 0)
		{
			s_log.warning ("No Sequence for DocType - " + dt);
			return null;
		}
			
		//	Check CompiereSys
		boolean compiereSys = Ini.isPropertyBool(Ini.P_COMPIERESYS);
		if (CLogMgt.isLevel(LOGLEVEL))
			s_log.log(LOGLEVEL, "DocType_ID=" + C_DocType_ID + " [" + trxName + "]");
		String selectSQL = "SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, AD_Client_ID, AD_Sequence_ID "
			+ "FROM AD_Sequence "
			+ "WHERE AD_Sequence_ID=?"
			+ " AND IsActive='Y' AND IsTableID='N' AND IsAutoSequence='Y' ";
		//	+ " FOR UPDATE";
		Connection conn = null;
		PreparedStatement pstmt = null;
		Trx trx = trxName == null ? null : Trx.get(trxName, true);
		//
		int AD_Sequence_ID = 0;
		int incrementNo = 0;
		int next = -1;
		String prefix = "";
		String suffix = "";
		try
		{
			if (trx != null)
				conn = trx.getConnection();
			else
				conn = DB.getConnectionID();
			//	Error
			if (conn == null)
				return null;
			//
			pstmt = conn.prepareStatement(selectSQL,
				ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			pstmt.setInt(1, dt.getDocNoSequence_ID());
			//
			ResultSet rs = pstmt.executeQuery();
		//	s_log.fine("AC=" + conn.getAutoCommit() + " -Iso=" + conn.getTransactionIsolation() 
		//		+ " - Type=" + pstmt.getResultSetType() + " - Concur=" + pstmt.getResultSetConcurrency());
			if (rs.next())
			{
				incrementNo = rs.getInt(3);
				prefix = rs.getString(4);
				suffix = rs.getString(5);
				int AD_Client_ID = rs.getInt(6);
				if (compiereSys && AD_Client_ID > 11)
					compiereSys = false;
				AD_Sequence_ID = rs.getInt(7);
				
				if (USE_PROCEDURE)
				{
					next = nextID(conn, AD_Sequence_ID, compiereSys);
				}
				else
				{
					if (compiereSys)
					{
						next = rs.getInt(2);
						rs.updateInt(2, next + incrementNo);
					}
					else
					{
						next = rs.getInt(1);
						rs.updateInt(1, next + incrementNo);
					}
					rs.updateRow();
				}
			}
			else
			{
				s_log.warning ("(DocType)- no record found - " + dt);
				next = -2;
			}
			rs.close();
			pstmt.close();
			pstmt = null;
			//	Commit
			if (trx == null)
			{
				conn.commit();
			//	conn.close();
			}
			conn = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, "(DocType) [" + trxName + "]", e);
			next = -2;
		}
		//	Finish
		try
		{
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		//	if (conn != null && trx == null)
		//		conn.close();
			conn = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, "(DocType) - finish", e);
			pstmt = null;
		}
		//	Error
		if (next < 0)
			return null;

		//	create DocumentNo
		StringBuffer doc = new StringBuffer();
		if (prefix != null && prefix.length() > 0)
			doc.append(prefix);
		doc.append(next);
		if (suffix != null && suffix.length() > 0)
			doc.append(suffix);
		String documentNo = doc.toString();
		s_log.finer (documentNo + " (" + incrementNo + ")" 
				+ " - C_DocType_ID=" + C_DocType_ID + " [" + trx + "]");
		return documentNo;
	}	//	getDocumentNo

	
	/**************************************************************************
	 *	Check/Initialize Client DocumentNo/Value Sequences 	
	 *	@param ctx context
	 *	@param AD_Client_ID client
	 *	@return true if no error
	 */
	public static boolean checkClientSequences (Properties ctx, int AD_Client_ID, String trxName)
	{
		String sql = "SELECT TableName "
			+ "FROM AD_Table t "
			+ "WHERE IsActive='Y' AND IsView='N'"
			//	Get all Tables with DocumentNo or Value
			+ " AND AD_Table_ID IN "
				+ "(SELECT AD_Table_ID FROM AD_Column "
				+ "WHERE ColumnName = 'DocumentNo' OR ColumnName = 'Value')"
			//	Ability to run multiple times
			+ " AND 'DocumentNo_' || TableName NOT IN "
				+ "(SELECT Name FROM AD_Sequence s "