gt: 		if (retValue != null)
context: /******************************************************************************
   * Product: Adempiere ERP & CRM Smart Business Solution                       *
   * Copyright (C) 1999-2006 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., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
   * or via info@compiere.org or http://www.compiere.org/license.html           *
   *****************************************************************************/
  package org.compiere.model;
  
  import java.math.BigDecimal;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Timestamp;
  import java.util.ArrayList;
  import java.util.Properties;
  import java.util.logging.Level;
  
  import org.compiere.util.CLogMgt;
  import org.compiere.util.CLogger;
  import org.compiere.util.DB;
  import org.compiere.util.Env;
  
  /**
   * 	Inventory Storage Model
   *
   *	@author Jorg Janke
   *	@version $Id: MStorage.java,v 1.3 2006/07/30 00:51:05 jjanke Exp $
   */
  public class MStorage extends X_M_Storage
  {
  	/**
  	 * generated serialVersionUID
  	 */
  	private static final long serialVersionUID = 9086223702645715061L;
  
  	/**
  	 * 	Get Storage Info
  	 *	@param ctx context
  	 *	@param M_Locator_ID locator
  	 *	@param M_Product_ID product
  	 *	@param M_AttributeSetInstance_ID instance
  	 *	@param trxName transaction
  	 *	@return existing or null
  	 */
  	public static MStorage get (Properties ctx, int M_Locator_ID, 
  		int M_Product_ID, int M_AttributeSetInstance_ID, String trxName)
  	{
  		MStorage retValue = null;
  		String sql = "SELECT * FROM M_Storage "
  			+ "WHERE M_Locator_ID=? AND M_Product_ID=? AND ";
  		if (M_AttributeSetInstance_ID == 0)
  			sql += "(M_AttributeSetInstance_ID=? OR M_AttributeSetInstance_ID IS NULL)";
  		else
  			sql += "M_AttributeSetInstance_ID=?";
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement (sql, trxName);
  			pstmt.setInt (1, M_Locator_ID);
  			pstmt.setInt (2, M_Product_ID);
  			pstmt.setInt (3, M_AttributeSetInstance_ID);
  			rs = pstmt.executeQuery ();
  			if (rs.next ())
  				retValue = new MStorage (ctx, rs, trxName);
  		}
  		catch (SQLException ex)
  		{
  			s_log.log(Level.SEVERE, sql, ex);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		if (retValue == null)
  			s_log.fine("Not Found - M_Locator_ID=" + M_Locator_ID 
  				+ ", M_Product_ID=" + M_Product_ID + ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID);
  		else
  			s_log.fine("M_Locator_ID=" + M_Locator_ID 
  				+ ", M_Product_ID=" + M_Product_ID + ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID);
  		return retValue;
  	}	//	get
  
  	/**
  	 * 	Get all Storages for Product with ASI and QtyOnHand <> 0
  	 *	@param ctx context
  	 *	@param M_Product_ID product
  	 *	@param M_Locator_ID locator
  	 *	@param FiFo first in-first-out
  	 *	@param trxName transaction
  	 *	@return existing or null
  	 */
  	public static MStorage[] getAllWithASI (Properties ctx, int M_Product_ID, int M_Locator_ID, 
  		boolean FiFo, String trxName)
  	{
  		ArrayList<MStorage> list = new ArrayList<MStorage>();
  		String sql = "SELECT * FROM M_Storage "
  			+ "WHERE M_Product_ID=? AND M_Locator_ID=?"
  			+ " AND M_AttributeSetInstance_ID > 0 "
  			+ " AND QtyOnHand <> 0 "			
  			+ "ORDER BY M_AttributeSetInstance_ID";
  		if (!FiFo)
  			sql += " DESC";
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement (sql, trxName);
  			pstmt.setInt (1, M_Product_ID);
  			pstmt.setInt (2, M_Locator_ID);
  			rs = pstmt.executeQuery ();
  			while (rs.next ())
  				list.add(new MStorage (ctx, rs, trxName));
  		}
  		catch (SQLException ex)
  		{
  			s_log.log(Level.SEVERE, sql, ex);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		MStorage[] retValue = new MStorage[list.size()];
  		list.toArray(retValue);
  		return retValue;
  	}	//	getAllWithASI
  
  	/**
  	 * 	Get all Storages for Product where QtyOnHand <> 0
  	 *	@param ctx context
  	 *	@param M_Product_ID product
  	 *	@param M_Locator_ID locator
  	 *	@param trxName transaction
  	 *	@return existing or null
  	 */
  	public static MStorage[] getAll (Properties ctx, 
  		int M_Product_ID, int M_Locator_ID, String trxName)
  	{
  		ArrayList<MStorage> list = new ArrayList<MStorage>();
  		String sql = "SELECT * FROM M_Storage "
  			+ "WHERE M_Product_ID=? AND M_Locator_ID=?"
  			+ " AND QtyOnHand <> 0 "
  			+ "ORDER BY M_AttributeSetInstance_ID";
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement (sql, trxName);
  			pstmt.setInt (1, M_Product_ID);
  			pstmt.setInt (2, M_Locator_ID);
  			rs = pstmt.executeQuery ();
  			while (rs.next ())
  				list.add(new MStorage (ctx, rs, trxName));
  		}
  		catch (SQLException ex)
  		{
  			s_log.log(Level.SEVERE, sql, ex);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		MStorage[] retValue = new MStorage[list.size()];
  		list.toArray(retValue);
  		return retValue;
  	}	//	getAll
  
  	
  	/**
  	 * 	Get Storage Info for Product across warehouses
  	 *	@param ctx context
  	 *	@param M_Product_ID product
  	 *	@param trxName transaction
  	 *	@return existing or null
  	 */
  	public static MStorage[] getOfProduct (Properties ctx, int M_Product_ID, String trxName)
  	{
  		ArrayList<MStorage> list = new ArrayList<MStorage>();
  		String sql = "SELECT * FROM M_Storage "
  			+ "WHERE M_Product_ID=?";
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement (sql, trxName);
  			pstmt.setInt (1, M_Product_ID);
  			rs = pstmt.executeQuery ();
  			while (rs.next ())
  				list.add(new MStorage (ctx, rs, trxName));
  		}
  		catch (SQLException ex)
  		{
  			s_log.log(Level.SEVERE, sql, ex);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		MStorage[] retValue = new MStorage[list.size()];
  		list.toArray(retValue);
  		return retValue;
  	}	//	getOfProduct
  	
  	/**
  	 * 	Get Storage Info for Warehouse
  	 *	@param ctx context
  	 *	@param M_Warehouse_ID 
  	 *	@param M_Product_ID product
  	 *	@param M_AttributeSetInstance_ID instance
  	 *	@param M_AttributeSet_ID attribute set
  	 *	@param allAttributeInstances if true, all attribute set instances
  	 *	@param minGuaranteeDate optional minimum guarantee date if all attribute instances
  	 *	@param FiFo first in-first-out
  	 *	@param trxName transaction
  	 *	@return existing - ordered by location priority (desc) and/or guarantee date
  	 *
  	 *  @deprecated
  	 */
  	public static MStorage[] getWarehouse (Properties ctx, int M_Warehouse_ID, 
  		int M_Product_ID, int M_AttributeSetInstance_ID, int M_AttributeSet_ID,
  		boolean allAttributeInstances, Timestamp minGuaranteeDate,
  		boolean FiFo, String trxName)
  	{
  		return getWarehouse(ctx, M_Warehouse_ID, M_Product_ID, M_AttributeSetInstance_ID, 
  				minGuaranteeDate, FiFo, false, 0, trxName);
  	}
  	
  	/**
  	 * 	Get Storage Info for Warehouse or locator
  	 *	@param ctx context
  	 *	@param M_Warehouse_ID ignore if M_Locator_ID > 0
  	 *	@param M_Product_ID product
  	 *	@param M_AttributeSetInstance_ID instance id, 0 to retrieve all instance
  	 *	@param minGuaranteeDate optional minimum guarantee date if all attribute instances
  	 *	@param FiFo first in-first-out
  	 *  @param positiveOnly if true, only return storage records with qtyOnHand > 0
  	 *  @param M_Locator_ID optional locator id
  	 *	@param trxName transaction
  	 *	@return existing - ordered by location priority (desc) and/or guarantee date
  	 */
  	public static MStorage[] getWarehouse (Properties ctx, int M_Warehouse_ID, 
  		int M_Product_ID, int M_AttributeSetInstance_ID, Timestamp minGuaranteeDate,
  		boolean FiFo, boolean positiveOnly, int M_Locator_ID, String trxName)
  	{
  		if ((M_Warehouse_ID == 0 && M_Locator_ID == 0) || M_Product_ID == 0)
  			return new MStorage[0];
  		
  		boolean allAttributeInstances = false;
  		if (M_AttributeSetInstance_ID == 0)
  			allAttributeInstances = true;		
  		
  		ArrayList<MStorage> list = new ArrayList<MStorage>();
  		//	Specific Attribute Set Instance
  		String sql = "SELECT s.M_Product_ID,s.M_Locator_ID,s.M_AttributeSetInstance_ID,"
  			+ "s.AD_Client_ID,s.AD_Org_ID,s.IsActive,s.Created,s.CreatedBy,s.Updated,s.UpdatedBy,"
  			+ "s.QtyOnHand,s.QtyReserved,s.QtyOrdered,s.DateLastInventory "
  			+ "FROM M_Storage s"
  			+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID) ";
  		if (M_Locator_ID > 0)
  			sql += "WHERE l.M_Locator_ID = ?";
  		else
  			sql += "WHERE l.M_Warehouse_ID=?";
  		sql += " AND s.M_Product_ID=?"
  			 + " AND COALESCE(s.M_AttributeSetInstance_ID,0)=? ";
  		if (positiveOnly)
  		{
  			sql += " AND s.QtyOnHand > 0 ";
  		}
  		else
  		{
  			sql += " AND s.QtyOnHand <> 0 ";
  		}
  		sql += "ORDER BY l.PriorityNo DESC, M_AttributeSetInstance_ID";
  		if (!FiFo)
  			sql += " DESC";
  		//	All Attribute Set Instances
  		if (allAttributeInstances)
  		{
  			sql = "SELECT s.M_Product_ID,s.M_Locator_ID,s.M_AttributeSetInstance_ID,"
  				+ "s.AD_Client_ID,s.AD_Org_ID,s.IsActive,s.Created,s.CreatedBy,s.Updated,s.UpdatedBy,"
  				+ "s.QtyOnHand,s.QtyReserved,s.QtyOrdered,s.DateLastInventory "
  				+ "FROM M_Storage s"
  				+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID)"
  				+ " LEFT OUTER JOIN M_AttributeSetInstance asi ON (s.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID) ";
  			if (M_Locator_ID > 0)
  				sql += "WHERE l.M_Locator_ID = ?";
  			else
  				sql += "WHERE l.M_Warehouse_ID=?";
  			sql += " AND s.M_Product_ID=? ";
  			if (positiveOnly)
  			{
  				sql += " AND s.QtyOnHand > 0 ";
  			}
  			else
  			{
  				sql += " AND s.QtyOnHand <> 0 ";
  			}
  			if (minGuaranteeDate != null)
  			{
  				sql += "AND (asi.GuaranteeDate IS NULL OR asi.GuaranteeDate>?) ";
  				sql += "ORDER BY l.PriorityNo DESC, " +
  					   "asi.GuaranteeDate, M_AttributeSetInstance_ID";
  				if (!FiFo)
  					sql += " DESC";
  				sql += ", s.QtyOnHand DESC";
  			}
  			else
  			{
  				sql += "ORDER BY l.PriorityNo DESC, l.M_Locator_ID, s.M_AttributeSetInstance_ID";
  				if (!FiFo)
  					sql += " DESC";
  				sql += ", s.QtyOnHand DESC";
  			}
  		} 
  		PreparedStatement pstmt = null;
  		ResultSet rs = null;
  		try
  		{
  			pstmt = DB.prepareStatement(sql, trxName);
  			pstmt.setInt(1, M_Locator_ID > 0 ? M_Locator_ID : M_Warehouse_ID);
  			pstmt.setInt(2, M_Product_ID);
  			if (!allAttributeInstances)
  			{
  				pstmt.setInt(3, M_AttributeSetInstance_ID);
  			}
  			else if (minGuaranteeDate != null)
  			{
  				pstmt.setTimestamp(3, minGuaranteeDate);
  			}
  			rs = pstmt.executeQuery();
  			while (rs.next())
  			{	
  				if(rs.getBigDecimal(11).signum() != 0)
  				list.add (new MStorage (ctx, rs, trxName));
  			}	
  		}
  		catch (Exception e)
  		{
  			s_log.log(Level.SEVERE, sql, e);
  		}
  		finally
  		{
  			DB.close(rs, pstmt);
  			rs = null; pstmt = null;
  		}
  		MStorage[] retValue = new MStorage[list.size()];
  		list.toArray(retValue);
  		return retValue;
  	}	//	getWarehouse
  
  	
  	/**
  	 * 	Create or Get Storage Info
  	 *	@param ctx context
  	 *	@param M_Locator_ID locator
  	 *	@param M_Product_ID product
  	 *	@param M_AttributeSetInstance_ID instance
  	 *	@param trxName transaction
  	 *	@return existing/new or null
  	 */
  	public static MStorage getCreate (Properties ctx, int M_Locator_ID, 
  		int M_Product_ID, int M_AttributeSetInstance_ID, String trxName)
  	{
  		if (M_Locator_ID == 0)
  			throw new IllegalArgumentException("M_Locator_ID=0");
  		if (M_Product_ID == 0)
  			throw new IllegalArgumentException("M_Product_ID=0");
  		MStorage retValue = get(ctx, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, trxName);
  