productcost.java

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

			log.fine("No Product");
			return null;
		}
		//
		BigDecimal cost = MCost.getCurrentCost (m_product, m_M_AttributeSetInstance_ID, 
			as, AD_Org_ID, costingMethod, m_qty, C_OrderLine_ID, zeroCostsOK, m_trxName);
		if (cost == null)
		{
			log.fine("No Costs");
			return null;
		}
		return cost;
	}   //  getProductCosts


	/**
	 *  Get Product Costs per UOM for Accounting Schema in Accounting Schema Currency.
	 *  - if costType defined - cost
	 *  - else CurrentCosts
	 *  @param as accounting schema
	 *  @param costType - if null uses Accounting Schema Costs - see AcctSchema.COSTING_*
	 *  @return product costs
	 */
	private BigDecimal getProductItemCostOld (MAcctSchema as, String costType)
	{
		BigDecimal current = null;
		BigDecimal cost = null;
		String cm = as.getCostingMethod();
		StringBuffer sql = new StringBuffer("SELECT CurrentCostPrice,");	//	1
		//
		if ((costType == null && MAcctSchema.COSTINGMETHOD_AveragePO.equals(cm))
				|| MAcctSchema.COSTINGMETHOD_AveragePO.equals(costType))
			sql.append("COSTAVERAGE");										//	2
	//	else if (AcctSchema.COSTING_FIFO.equals(cm))
	//		sql.append("COSTFIFO");
	//	else if (AcctSchema.COSTING_LIFO.equals(cm))
	//		sql.append("COSTLIFO");
		else if ((costType == null && MAcctSchema.COSTINGMETHOD_LastPOPrice.equals(cm))
				|| MAcctSchema.COSTINGMETHOD_LastPOPrice.equals(costType))
			sql.append("PRICELASTPO");
		else    //  AcctSchema.COSTING_STANDARD
			sql.append("COSTSTANDARD");
		sql.append(" FROM M_Product_Costing WHERE M_Product_ID=? AND C_AcctSchema_ID=?");

		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
			pstmt.setInt(1, m_M_Product_ID);
			pstmt.setInt(2, as.getC_AcctSchema_ID());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				current = rs.getBigDecimal(1);
				cost = rs.getBigDecimal(2);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}

		//  Return Costs
		if (costType != null && cost != null && !cost.equals(Env.ZERO))
		{
			log.fine("Costs=" + cost);
			return cost;
		}
		else if (current != null && !current.equals(Env.ZERO))
		{
			log.fine("Current=" + current);
			return current;
		}

		//  Create/Update Cost Record
		boolean create = (cost == null && current == null);
		return updateCostsOld (as, create);
	}   //  getProductCostOld

	/**
	 *  Update/Create initial Cost Record.
	 *  Check first for     Purchase Price List,
	 *      then Product    Purchase Costs
	 *      and then        Price List
	 *  @param as accounting schema
	 *  @param create create record
	 *  @return costs
	 */
	private BigDecimal updateCostsOld (MAcctSchema as, boolean create)
	{
		//  Create Zero Record
		if (create)
		{
			StringBuffer sql = new StringBuffer ("INSERT INTO M_Product_Costing "
				+ "(M_Product_ID,C_AcctSchema_ID,"
				+ " AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ " CurrentCostPrice,CostStandard,FutureCostPrice,"
				+ " CostStandardPOQty,CostStandardPOAmt,CostStandardCumQty,CostStandardCumAmt,"
				+ " CostAverage,CostAverageCumQty,CostAverageCumAmt,"
				+ " PriceLastPO,PriceLastInv, TotalInvQty,TotalInvAmt) "
				+ "VALUES (");
			sql.append(m_M_Product_ID).append(",").append(as.getC_AcctSchema_ID()).append(",")
				.append(as.getAD_Client_ID()).append(",").append(as.getAD_Org_ID()).append(",")
				.append("'Y',SysDate,0,SysDate,0, 0,0,0,  0,0,0,0,  0,0,0,  0,0,  0,0)");
			int no = DB.executeUpdate(sql.toString(), m_trxName);
			if (no == 1)
				log.fine("CostingCreated");
		}

		//  Try to find non ZERO Price
		String costSource = "PriceList-PO";
		BigDecimal costs = getPriceList (as, true);
		if (costs == null || costs.equals(Env.ZERO))
		{
			costSource = "PO Cost";
			costs = getPOCost(as);
		}
		if (costs == null || costs.equals(Env.ZERO))
		{
			costSource = "PriceList";
			costs = getPriceList (as, false);
		}

		//  if not found use $1 (to be able to do material transactions)
		if (costs == null || costs.equals(Env.ZERO))
		{
			costSource = "Not Found";
			costs = new BigDecimal("1");
		}

		//  update current costs
		StringBuffer sql = new StringBuffer ("UPDATE M_Product_Costing ");
		sql.append("SET CurrentCostPrice=").append(costs)
			.append(" WHERE M_Product_ID=").append(m_M_Product_ID)
			.append(" AND C_AcctSchema_ID=").append(as.getC_AcctSchema_ID());
		int no = DB.executeUpdate(sql.toString(), m_trxName);
		if (no == 1)
			log.fine(costSource + " - " + costs);
		return costs;
	}   //  createCosts

	/**
	 *  Get PO Price from PriceList - and convert it to AcctSchema Currency
	 *  @param as accounting schema
	 *  @param onlyPOPriceList use only PO price list
	 *  @return po price
	 */
	private BigDecimal getPriceList (MAcctSchema as, boolean onlyPOPriceList)
	{
		StringBuffer sql = new StringBuffer (
			"SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit "
			+ "FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp "
			+ "WHERE pl.M_PriceList_ID = plv.M_PriceList_ID"
			+ " AND plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID"
			+ " AND pp.M_Product_ID=?");
		if (onlyPOPriceList)
			sql.append(" AND pl.IsSOPriceList='N'");
		sql.append(" ORDER BY pl.IsSOPriceList ASC, plv.ValidFrom DESC");
		int C_Currency_ID = 0;
		BigDecimal PriceList = null;
		BigDecimal PriceStd = null;
		BigDecimal PriceLimit = null;
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
			pstmt.setInt(1, m_M_Product_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				C_Currency_ID = rs.getInt(1);
				PriceList = rs.getBigDecimal(2);
				PriceStd = rs.getBigDecimal(3);
				PriceLimit = rs.getBigDecimal(4);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		//  nothing found
		if (C_Currency_ID == 0)
			return null;

		BigDecimal price = PriceLimit;  //  best bet
		if (price == null || price.equals(Env.ZERO))
			price = PriceStd;
		if (price == null || price.equals(Env.ZERO))
			price = PriceList;
		//  Convert
		if (price != null && !price.equals(Env.ZERO))
			price = MConversionRate.convert (as.getCtx(),
				price, C_Currency_ID, as.getC_Currency_ID(), 
				as.getAD_Client_ID(), 0);
		return price;
	}   //  getPOPrice

	/**
	 *  Get PO Cost from Purchase Info - and convert it to AcctSchema Currency
	 *  @param as accounting schema
	 *  @return po cost
	 */
	private BigDecimal getPOCost (MAcctSchema as)
	{
		String sql = "SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO "
			+ "FROM M_Product_PO WHERE M_Product_ID=? "
			+ "ORDER BY IsCurrentVendor DESC";

		int C_Currency_ID = 0;
		BigDecimal PriceList = null;
		BigDecimal PricePO = null;
		BigDecimal PriceLastPO = null;
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql, null);
			pstmt.setInt(1, m_M_Product_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				C_Currency_ID = rs.getInt(1);
				PriceList = rs.getBigDecimal(2);
				PricePO = rs.getBigDecimal(3);
				PriceLastPO = rs.getBigDecimal(4);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql, e);
		}
		//  nothing found
		if (C_Currency_ID == 0)
			return null;

		BigDecimal cost = PriceLastPO;  //  best bet
		if (cost == null || cost.equals(Env.ZERO))
			cost = PricePO;
		if (cost == null || cost.equals(Env.ZERO))
			cost = PriceList;
		//  Convert - standard precision!! - should be costing precision
		if (cost != null && !cost.equals(Env.ZERO))
			cost = MConversionRate.convert (as.getCtx(),
				cost, C_Currency_ID, as.getC_Currency_ID(), as.getAD_Client_ID(), as.getAD_Org_ID());
		return cost;
	}   //  getPOCost

}	//	ProductCost