productinfo.java

Java写的ERP系统

			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, as.getC_AcctSchema_ID());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				validCombination_ID = rs.getInt(AcctType);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error ("getAccountDefault", e);
		}
		if (validCombination_ID == 0)
			return null;
		return Account.getAccount(validCombination_ID);
	}   //  getAccountDefault

	/*************************************************************************/

	/**
	 *  Get Total Costs in Accounting Schema Currency
	 *  @param as accounting schema
	 *  @return     cost or null, if qty or costs cannot be determined
	 */
	public BigDecimal getProductCosts (AcctSchema as)
	{
		if (m_qty == null)
		{
			log.debug("getProductCosts - No Qty");
			return null;
		}
		BigDecimal cost = getProductItemCost(as, null);
		if (cost == null)
		{
			log.debug("getProductCosts - No Costs");
			return null;
		}
		log.debug("getProductCosts - Qty(" + m_qty + ") * Cost(" + cost + ") = " + m_qty.multiply(cost));
		return m_qty.multiply(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
	 */
	public BigDecimal getProductItemCost(AcctSchema as, String costType)
	{
		BigDecimal current = null;
		BigDecimal cost = null;
		String cm = as.getCostingMethod();
		StringBuffer sql = new StringBuffer("SELECT CurrentCostPrice,");	//	1
		//
		if ((costType == null && AcctSchema.COSTING_AVERAGE.equals(cm))
				|| AcctSchema.COSTING_AVERAGE.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 && AcctSchema.COSTING_LASTPO.equals(cm))
				|| AcctSchema.COSTING_LASTPO.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());
			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.error("getProductItemCost", e);
		}

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

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

	/**
	 *  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 updateCosts (AcctSchema 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(m_AD_Client_ID).append(",").append(m_AD_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());
			if (no == 1)
				log.debug("updateCosts - 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());
		if (no == 1)
			log.debug("updateCosts - " + 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 (AcctSchema 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());
			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.error("getPOPrice", 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 = DB.getConvertedAmt(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 (AcctSchema 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);
			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.error("getPOCost", 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 = DB.getConvertedAmt(cost, C_Currency_ID, as.getC_Currency_ID(), m_AD_Client_ID, m_AD_Org_ID);
		return cost;
	}   //  getPOCost

}   //  ProductInfo