/// <summary>
/// Get Price from Price List
/// </summary>
/// <param name="cost">cost record</param>
/// <returns>price or null</returns>
private Decimal?GetPrice(VAdvantage.Model.MCost cost)
{
Decimal?retValue = null;
String sql = "SELECT PriceLimit "
+ "FROM M_ProductPrice "
+ "WHERE M_Product_ID=" + cost.GetM_Product_ID() + " AND M_PriceList_Version_ID=" + _M_PriceList_Version_ID;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
//pstmt.setInt (1, cost.GetM_Product_ID());
//pstmt.setInt (2, _M_PriceList_Version_ID);
//ResultSet dr = pstmt.executeQuery ();
if (idr.Read())
{
retValue = Util.GetValueOfDecimal(idr[0]);//.getBigDecimal(1);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
return(retValue);
}
/// <summary>
/// Create New Client level Costing Record
/// </summary>
/// <param name="product">product</param>
/// <param name="?">acct schema</param>
/// <returns>true if created</returns>
private bool CreateNew(MProduct product, MAcctSchema as1)
{
VAdvantage.Model.MCost cost = VAdvantage.Model.MCost.Get(product, 0, as1, 0, _ce.GetM_CostElement_ID());
if (cost.Is_New())
{
return(cost.Save());
}
return(false);
}
/// <summary>
/// Update Cost Records
/// </summary>
/// <param name="cost">cost</param>
/// <returns>true if updated</returns>
private bool Update(VAdvantage.Model.MCost cost)
{
bool updated = false;
if (_SetFutureCostTo.Equals(_SetStandardCostTo))
{
Decimal costs = Util.GetValueOfDecimal(GetCosts(cost, _SetFutureCostTo));
if (costs != null && Env.Signum(costs) != 0)
{
cost.SetFutureCostPrice(costs);
cost.SetCurrentCostPrice(costs);
updated = true;
}
}
else
{
if (_SetStandardCostTo.Length > 0)
{
Decimal costs = Util.GetValueOfDecimal(GetCosts(cost, _SetStandardCostTo));
if (costs != null && Env.Signum(costs) != 0)
{
cost.SetCurrentCostPrice(costs);
updated = true;
}
}
if (_SetFutureCostTo.Length > 0)
{
Decimal costs = Util.GetValueOfDecimal(GetCosts(cost, _SetFutureCostTo));
if (costs != null && Env.Signum(costs) != 0)
{
cost.SetFutureCostPrice(costs);
updated = true;
}
}
}
if (updated)
{
updated = cost.Save();
}
return(updated);
}
/// <summary>
/// Get Old Current Cost Price
/// </summary>
/// <param name="cost">costs</param>
/// <returns>price if found</returns>
private Decimal?GetOldCurrentCostPrice(VAdvantage.Model.MCost cost)
{
Decimal?retValue = null;
String sql = "SELECT CostStandard, CurrentCostPrice "
+ "FROM M_Product_Costing "
+ "WHERE M_Product_ID=" + cost.GetM_Product_ID() + " AND C_AcctSchema_ID=" + cost.GetC_AcctSchema_ID();
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
//pstmt.setInt (1, cost.GetM_Product_ID());
//pstmt.setInt (2, cost.GetC_AcctSchema_ID());
//ResultSet dr = pstmt.executeQuery ();
if (idr.Read())
{
retValue = Util.GetValueOfDecimal(idr[0]);//.getBigDecimal(1);
if (retValue == null || Env.Signum(Util.GetValueOfDecimal(retValue)) == 0)
{
retValue = Util.GetValueOfDecimal(idr[1]);
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
return(retValue);
}
/// <summary>
/// Get Costs
/// </summary>
/// <param name="cost">Cost</param>
/// <param name="to">where to get costs from </param>
/// <returns>costs (could be 0) or null if not found</returns>
private Decimal?GetCosts(VAdvantage.Model.MCost cost, String to)
{
Decimal?retValue = null;
// Average Invoice
if (to.Equals(TO_AverageInvoice))
{
MCostElement ce = GetCostElement(TO_AverageInvoice);
if (ce == null)
{
throw new Exception("CostElement not found: " + TO_AverageInvoice);
}
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetCurrentCostPrice();
}
}
// Average Invoice History
else if (to.Equals(TO_AverageInvoiceHistory))
{
MCostElement ce = GetCostElement(TO_AverageInvoice);
if (ce == null)
{
throw new Exception("CostElement not found: " + TO_AverageInvoice);
}
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetHistoryAverage();
}
}
// Average PO
else if (to.Equals(TO_AveragePO))
{
MCostElement ce = GetCostElement(TO_AveragePO);
if (ce == null)
{
throw new Exception("CostElement not found: " + TO_AveragePO);
}
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetCurrentCostPrice();
}
}
// Average PO History
else if (to.Equals(TO_AveragePOHistory))
{
MCostElement ce = GetCostElement(TO_AveragePO);
if (ce == null)
{
throw new Exception("CostElement not found: " + TO_AveragePO);
}
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetHistoryAverage();
}
}
// FiFo
else if (to.Equals(TO_FiFo))
{
MCostElement ce = GetCostElement(TO_FiFo);
if (ce == null)
{
throw new Exception("CostElement not found: " + TO_FiFo);
}
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetCurrentCostPrice();
}
}
// Future Std Costs
else if (to.Equals(TO_FutureStandardCost))
{
retValue = cost.GetFutureCostPrice();
}
// Last Inv Price
else if (to.Equals(TO_LastInvoicePrice))
{
MCostElement ce = GetCostElement(TO_LastInvoicePrice);
if (ce != null)
{
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetCurrentCostPrice();
}
}
if (retValue == null)
{
MProduct product = MProduct.Get(GetCtx(), cost.GetM_Product_ID());
MAcctSchema as1 = MAcctSchema.Get(GetCtx(), cost.GetC_AcctSchema_ID());
retValue = VAdvantage.Model.MCost.GetLastInvoicePrice(product,
cost.GetM_AttributeSetInstance_ID(), cost.GetAD_Org_ID(), as1.GetC_Currency_ID());
}
}
// Last PO Price
else if (to.Equals(TO_LastPOPrice))
{
MCostElement ce = GetCostElement(TO_LastPOPrice);
if (ce != null)
{
if (BTR002_IsPickLastPO == "N")
{
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetCurrentCostPrice();
}
}
}
if (retValue == null)
{
MProduct product = MProduct.Get(GetCtx(), cost.GetM_Product_ID());
MAcctSchema as1 = MAcctSchema.Get(GetCtx(), cost.GetC_AcctSchema_ID());
retValue = VAdvantage.Model.MCost.GetLastPOPrice(product,
cost.GetM_AttributeSetInstance_ID(), cost.GetAD_Org_ID(), as1.GetC_Currency_ID());
}
}
// FiFo
else if (to.Equals(TO_LiFo))
{
MCostElement ce = GetCostElement(TO_LiFo);
if (ce == null)
{
throw new Exception("CostElement not found: " + TO_LiFo);
}
VAdvantage.Model.MCost xCost = VAdvantage.Model.MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID());
if (xCost != null)
{
retValue = xCost.GetCurrentCostPrice();
}
}
// Old Std Costs
else if (to.Equals(TO_OldStandardCost))
{
retValue = GetOldCurrentCostPrice(cost);
}
// Price List
else if (to.Equals(TO_PriceListLimit))
{
retValue = GetPrice(cost);
}
// Standard Costs
else if (to.Equals(TO_StandardCost))
{
retValue = cost.GetCurrentCostPrice();
}
return(retValue);
}
/// <summary>
/// Update Cost Records
/// </summary>
/// <returns>no updated</returns>
private int Update()
{
int counter = 0;
String sql = "SELECT * FROM M_Cost c WHERE M_CostElement_ID=" + _ce.GetM_CostElement_ID();
if (_M_Product_Category_ID != 0)
{
sql += " AND EXISTS (SELECT * FROM M_Product p "
+ "WHERE c.M_Product_ID=p.M_Product_ID AND p.M_Product_Category_ID=" + _M_Product_Category_ID + ")";
}
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
//pstmt.setInt (1, _ce.GetM_CostElement_ID());
//if (_M_Product_Category_ID != 0)
// pstmt.setInt (2, _M_Product_Category_ID);
//ResultSet dr = pstmt.executeQuery ();
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
VAdvantage.Model.MCost cost = new VAdvantage.Model.MCost(GetCtx(), dr, Get_Trx());
for (int i = 0; i < _ass.Length; i++)
{
// Update Costs only for default Cost Type
if (_ass[i].GetC_AcctSchema_ID() == cost.GetC_AcctSchema_ID() &&
_ass[i].GetM_CostType_ID() == cost.GetM_CostType_ID())
{
if (Update(cost))
{
counter++;
}
}
}
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
finally
{
dt = null;
if (idr != null)
{
idr.Close();
}
}
log.Info("#" + counter);
AddLog(0, null, new Decimal(counter), "@Updated@");
return(counter);
}