private void GenerateMasterForecast(int M_Product_ID, decimal?totalQtyTeam, Decimal?totalQtyOpp, decimal?avgPrice) { sql = "select c_uom_id from m_product where m_product_id = " + M_Product_ID; MMasterForecastLine mfLine = new MMasterForecastLine(GetCtx(), 0, null); mfLine.SetAD_Client_ID(mf.GetAD_Client_ID()); mfLine.SetAD_Org_ID(mf.GetAD_Org_ID()); mfLine.SetM_Product_ID(M_Product_ID); mfLine.SetC_MasterForecast_ID(mf.GetC_MasterForecast_ID()); mfLine.SetForcastQty(totalQtyTeam); mfLine.SetOppQty(totalQtyOpp); Decimal?total = Decimal.Add(totalQtyOpp.Value, totalQtyTeam.Value); mfLine.SetTotalQty(total); mfLine.SetPrice(avgPrice); mfLine.SetC_UOM_ID(Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null))); mfLine.SetProcessed(true); Decimal?planRevenue = Decimal.Round(Decimal.Multiply(avgPrice.Value, total.Value), 2, MidpointRounding.AwayFromZero); mfLine.SetPlannedRevenue(planRevenue); if (!mfLine.Save()) { log.SaveError("MasterForecastLineNotSaved", "MasterForecastLineNotSaved"); } }
private void OnlyOpportunityProducts() { //sql = " SELECT distinct(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL" // + " AND p.ref_order_id IS NULL AND pl.m_product_id NOT IN (SELECT DISTINCT(M_Product_ID) FROM c_forecastline fl " // + " INNER JOIN c_forecast f ON (fl.c_forecast_id = f.c_forecast_id) WHERE f.c_period_id = " + C_Period_ID // + " AND f.ad_client_id = " + GetCtx().GetAD_Client_ID() + " AND fl.isactive = 'Y')"; sql = " SELECT distinct(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL" + " AND p.ref_order_id IS NULL AND pl.m_product_id NOT IN (select m_product_id from c_masterforecastline where isactive = 'Y' and c_masterforecast_id = " + mFor.GetC_MasterForecast_ID() + ")"; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, null); while (idr.Read()) { Decimal?totalQtyOpp = 0; Decimal?totalPriceOpp = 0; sql = "select sum(nvl(pl.plannedqty,0)) FROM c_projectline pl inner join c_project p on (p.c_project_id = pl.c_project_id) " + " WHERE pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " + " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and p.c_order_id is null and p.ref_order_id is null and pl.isactive = 'Y'"; totalQtyOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); sql = " SELECT SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) FROM c_projectline pl inner join c_project p on (p.c_project_id = pl.c_project_id) " + " WHERE pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " + " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and p.c_order_id is null and p.ref_order_id is null and pl.isactive = 'Y'"; totalPriceOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); if (totalQtyOpp.Value > 0) { Decimal?avgPrice = Decimal.Divide(totalPriceOpp.Value, totalQtyOpp.Value); avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero); GenerateMasterForecast(Util.GetValueOfInt(idr[0]), Util.GetValueOfDecimal(Decimal.Zero), totalQtyOpp, avgPrice); } } if (idr != null) { idr.Close(); idr = null; } } catch { if (idr != null) { idr.Close(); idr = null; } } }
/// <SUMmary> /// Create Master Forecast /// </SUMmary> /// <param name="M_Product_ID">Product </param> /// <param name="totalQtyTeam">Forecast Qty</param> /// <param name="totalQtyOpp">Opportunity Qty</param> /// <param name="avgPrice">Price</param> /// <param name="totalQtySO">Total </param> /// <returns>odject</returns> private MMasterForecastLine GenerateMasterForecast(int M_Product_ID, int M_AttributeSetInstance, decimal?totalQtyTeam, Decimal?totalQtyOpp, decimal?avgPrice) { MMasterForecastLine mfLine = MMasterForecastLine.GetOrCreate(mf, M_Product_ID, M_AttributeSetInstance); mfLine.SetC_MasterForecast_ID(mf.GetC_MasterForecast_ID()); mfLine.SetForcastQty(totalQtyTeam); mfLine.SetOppQty(totalQtyOpp + mfLine.GetOppQty()); Decimal?total = Decimal.Add(totalQtyOpp.Value, totalQtyTeam.Value); mfLine.SetTotalQty(total + mfLine.GetTotalQty()); mfLine.SetPrice(avgPrice + mfLine.GetPrice()); Decimal?planRevenue = Decimal.Round(Decimal.Multiply(mfLine.GetPrice(), mfLine.GetTotalQty()), StdPrecision, MidpointRounding.AwayFromZero); mfLine.SetPlannedRevenue(planRevenue); return(mfLine); }
/// <SUMmary> /// Create Master Forecast /// </SUMmary> /// <param name="M_Product_ID">Product </param> /// <param name="totalQtyTeam">Forecast Qty</param> /// <param name="totalQtyOpp">Opportunity Qty</param> /// <param name="avgPrice">Price</param> /// <param name="totalQtySO">Total </param> /// <returns>object</returns> private MMasterForecastLine GenerateMasterForecast(int M_Product_ID, int M_AttributeSetInstance, decimal?totalQtyTeam, Decimal?totalQtyOpp, decimal?avgPrice) { MMasterForecastLine mfLine = MMasterForecastLine.GetOrCreate(mf, M_Product_ID, M_AttributeSetInstance); Decimal? qty = mfLine.GetOppQty(); mfLine.SetC_MasterForecast_ID(mf.GetC_MasterForecast_ID()); if (totalQtyOpp > 0 && totalQtyTeam > 0) { mfLine.SetForcastQty(totalQtyTeam + mfLine.GetForcastQty()); mfLine.SetOppQty(totalQtyOpp); } else { mfLine.SetForcastQty(totalQtyTeam); mfLine.SetOppQty(totalQtyOpp + mfLine.GetOppQty()); } //Decimal? totalqty = Decimal.Add(mfLine.GetForcastQty(), mfLine.GetOppQty()); mfLine.SetTotalQty(mfLine.GetForcastQty() + mfLine.GetOppQty()); //calculate average price for opportunit case only if (totalQtyOpp > 0 && totalQtyTeam == 0) { avgPrice = ((mfLine.GetPrice() * qty) + (avgPrice * totalQtyOpp)) / mfLine.GetTotalQty(); avgPrice = Decimal.Round(avgPrice.Value, StdPrecision, MidpointRounding.AwayFromZero); mfLine.SetPrice(avgPrice); } //Team Forecast case else { mfLine.SetPrice(avgPrice); } Decimal?planRevenue = Decimal.Round(Decimal.Multiply(mfLine.GetPrice(), mfLine.GetTotalQty()), StdPrecision, MidpointRounding.AwayFromZero); mfLine.SetPlannedRevenue(planRevenue); return(mfLine); }