/// <summary>
///
/// </summary>
/// <param name="mf"></param>
/// <param name="opp"></param>
private void CreateMasterForecast(bool opp)
{
mFor = new X_C_MasterForecast(GetCtx(), 0, null);
mFor.SetAD_Client_ID(mf.GetAD_Client_ID());
mFor.SetAD_Org_ID(mf.GetAD_Org_ID());
mFor.SetC_Period_ID(C_Period_ID);
mFor.SetCurrentVersion(true);
if (opp)
{
mFor.SetIsIncludeOpp(true);
}
else
{
mFor.SetIsIncludeOpp(false);
}
string name = mf.GetName().Substring(0, mf.GetName().IndexOf('_') + 1);
string date = System.DateTime.Now.ToString("dd-MMM-yyyy");
if (name == "")
{
mFor.SetName(mf.GetName() + "_" + date);
}
else
{
mFor.SetName(name + date);
}
if (!mFor.Save())
{
log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved");
}
}
protected override string DoIt()
{
mf = new X_C_MasterForecast(GetCtx(), GetRecord_ID(), null);
C_Period_ID = mf.GetC_Period_ID();
// sql = "delete from c_masterforecastline where c_masterforecast_id = " + mf.GetC_MasterForecast_ID();
// int count = DB.ExecuteQuery(sql, null, null);
if (C_Period_ID != 0)
{
sql = "select count(*) from c_masterforecastline where c_masterforecast_id = " + GetRecord_ID();
int count = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
if (count > 0)
{
sql = "update c_masterforecastline set Processed = 'Y' where c_masterforecast_id = " + GetRecord_ID();
int res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
sql = "update c_masterforecast set Processed = 'Y' where c_masterforecast_id = " + GetRecord_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
msg = Msg.GetMsg(GetCtx(), "RecordsProcessed");
return(msg);
}
sql = "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 f.isactive = 'Y' and f.processed = 'Y'";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
while (idr.Read())
{
Decimal?totalQtyTeam = 0;
Decimal?totalPriceTeam = 0;
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "select SUM(nvl(qtyentered,0)) from c_forecastline where m_product_id = " + Util.GetValueOfInt(idr[0]) + " and Processed = 'Y' and isactive = 'Y' and ad_client_id = " + mf.GetAD_Client_ID();
//sql = "select SUM(nvl(fl.qtyentered,0)) from c_forecastline fl inner join c_forecast f on (f.c_forecast_id = fl.c_forecast_id) where f.ad_client_id = " + mf.GetAD_Client_ID() + " and fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and fl.Processed = 'Y' and fl.isactive = 'Y' and f.c_period_id = " + C_Period_ID;
totalQtyTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
sql = "select SUM(nvl(pricestd,0)) from c_forecastline where m_product_id = " + Util.GetValueOfInt(idr[0]) + " and Processed = 'Y' and isactive = 'Y' and ad_client_id = " + mf.GetAD_Client_ID();
// sql = "select SUM(nvl(fl.pricestd,0)) from c_forecastline fl inner join c_forecast f on (f.c_forecast_id = fl.c_forecast_id) where f.ad_client_id = " + mf.GetAD_Client_ID() + " and fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " and fl.Processed = 'Y' and fl.isactive = 'Y' and f.c_period_id = " + C_Period_ID;
totalPriceTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
if (mf.IsIncludeOpp())
{
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' and p.ad_client_id = " + mf.GetAD_Client_ID();
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' and p.ad_client_id = " + mf.GetAD_Client_ID();
totalPriceOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
}
Decimal?totalPrice = Decimal.Add(totalPriceTeam.Value, totalPriceOpp.Value);
Decimal?totalQty = Decimal.Add(totalQtyTeam.Value, totalQtyOpp.Value);
if (totalQty.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPrice.Value, totalQty.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
GenerateMasterForecast(Util.GetValueOfInt(idr[0]), totalQtyTeam, totalQtyOpp, avgPrice);
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
if (mf.IsIncludeOpp())
{
OnlyOpportunityProducts();
}
mf.SetCurrentVersion(true);
mf.SetProcessed(true);
if (!mf.Save())
{
log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved");
}
msg = Msg.GetMsg(GetCtx(), "ProcessCompleted");
}
return(msg);
}
/// <summary>
///
/// </summary>
public void GenerateLines()
{
if (C_Period_ID != 0)
{
sql = "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 f.isactive = 'Y' and f.processed = 'Y'";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
while (idr.Read())
{
Decimal?totalQtyTeam = 0;
Decimal?totalPriceTeam = 0;
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "select SUM(nvl(qtyentered,0)) from c_forecastline where isactive = 'Y' and m_product_id = " + Util.GetValueOfInt(idr[0]) + " and processed = 'Y'";
totalQtyTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
// sql = "select SUM(nvl(qtyentered,0) * nvl(pricestd,0)) from c_forecastline where m_product_id = " + Util.GetValueOfInt(idr[0]) + " and processed = 'Y'";
sql = "select SUM(nvl(pricestd,0)) from c_forecastline where isactive = 'Y' and m_product_id = " + Util.GetValueOfInt(idr[0]) + " and processed = 'Y'";
totalPriceTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
if (mFor.IsIncludeOpp())
{
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));
}
Decimal?totalPrice = Decimal.Add(totalPriceTeam.Value, totalPriceOpp.Value);
Decimal?totalQty = Decimal.Add(totalQtyTeam.Value, totalQtyOpp.Value);
if (totalQty.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPrice.Value, totalQty.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
GenerateMasterForecast(Util.GetValueOfInt(idr[0]), totalQtyTeam, totalQtyOpp, avgPrice);
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
if (mFor.IsIncludeOpp())
{
OnlyOpportunityProducts();
}
mFor.SetCurrentVersion(true);
// mFor.SetProcessed(true);
if (!mFor.Save())
{
log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved");
}
}
}
/// <SUMmary>
/// Consolidate Data FROM sales order , Team Forecast, Opportunity
/// </SUMmary>
/// <returns>Info</returns>
protected override string DoIt()
{
mf = new X_C_MasterForecast(GetCtx(), GetRecord_ID(), Get_Trx());
if (Util.GetValueOfInt(mf.Get_Value("M_PriceList_ID")) == 0)
{
return(Msg.GetMsg(mf.GetCtx(), "CreatelinesManually"));
}
C_Period_ID = mf.GetC_Period_ID();
Currency = Util.GetValueOfInt(mf.Get_Value("C_Currency_ID"));
StdPrecision = Util.GetValueOfInt(DB.ExecuteScalar("SELECT StdPrecision FROM C_Currency WHERE C_Currency_ID=" + Currency, null, null));
//Get Table_Id to create PO Object
// sql = @"SELECT AD_TABLE_ID FROM AD_TABLE WHERE tablename LIKE 'VA073_MasterForecastLineDetail' AND IsActive = 'Y'";
// tableId = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
// tbl = new MTable(GetCtx(), tableId, null);
tbl = MTable.Get(GetCtx(), "VA073_MasterForecastLineDetail");
// sql = "delete FROM c_masterforecastline WHERE c_masterforecast_id = " + mf.GetC_MasterForecast_ID();
// int count = DB.ExecuteQuery(sql, null, null);
if (C_Period_ID != 0)
{
sql = "SELECT COUNT(C_MasterForecastLine_ID) FROM c_masterforecastline WHERE c_masterforecast_id = " + GetRecord_ID();
int count = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
if (count > 0)
{
sql = "UPDATE c_masterforecastline set Processed = 'Y' WHERE c_masterforecast_id = " + GetRecord_ID();
int res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
sql = "UPDATE c_masterforecast set Processed = 'Y' WHERE c_masterforecast_id = " + GetRecord_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
msg = Msg.GetMsg(GetCtx(), "RecordsProcessed");
return(msg);
}
if (!Env.IsModuleInstalled("VA073_"))
{
sql = "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 f.isactive = 'Y' AND f.processed = 'Y'";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, mf.Get_Trx());
while (idr.Read())
{
Decimal?totalQtyTeam = 0;
Decimal?totalPriceTeam = 0;
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "SELECT SUM(nvl(qtyentered,0)) AS Quantity,SUM(nvl(pricestd,0)) AS Price,f.C_Currency_ID FROM c_forecastline fl" +
" INNER JOIN C_Forecast f ON f.C_Forecast_ID = fl.C_Forecast_ID " +
" WHERE fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND f.Processed = 'Y' AND f.isactive = 'Y'" +
" GROUP BY f.C_Currency_ID";
//totalQtyTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
//// sql = "SELECT SUM(nvl(qtyentered,0) * nvl(pricestd,0)) FROM c_forecastline WHERE m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND Processed = 'Y'";
//sql = "SELECT SUM(nvl(pricestd,0)) FROM c_forecastline WHERE m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND Processed = 'Y' AND isactive = 'Y'";
//totalPriceTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx()));
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
totalPriceTeam = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
totalQtyTeam = Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[0]["Quantity"]);
}
if (mf.IsIncludeOpp())
{
sql = "SELECT SUM(NVL(pl.plannedqty,0)) AS Quantity ,SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price, p.C_Currency_ID,pl.C_UOM_ID" +
" 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'"
+ " GROUP BY C_Currency_ID,pl.C_UOM_ID";
//totalQtyOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx()));
//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' AND p.ad_client_id = " + mf.GetAD_Client_ID();
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Conversion from BaseUOM to UOM on Project Line
totalQtyOpp = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(idr[0]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Quantity"]));
}
}
Decimal?totalPrice = Decimal.Add(totalPriceTeam.Value, totalPriceOpp.Value);
Decimal?totalQty = Decimal.Add(totalQtyTeam.Value, totalQtyOpp.Value);
if (totalQty.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPrice.Value, totalQty.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(idr[0]), 0, totalQtyTeam, totalQtyOpp, avgPrice);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
if (mf.IsIncludeOpp())
{
OnlyOpportunityProducts();
}
mf.SetCurrentVersion(true);
mf.SetProcessed(true);
if (!mf.Save())
{
log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved");
return(GetRetrievedError(mf, "MasterForecastNotSaved"));
}
msg = Msg.GetMsg(GetCtx(), "ProcessCompleted");
}
else
{
//VA073 module installed -- Consolidate FROM Sales order , opportunity , Team Forecast
TeamForecastProduct();
if (mf.IsIncludeOpp())
{
OnlyOpportunityProducts();
}
if (Util.GetValueOfBool(mf.Get_Value("VA073_IsIncludeOpenSO")))
{
SalesOrderProducts();
}
if (Count == 0)
{
mf.Get_Trx().Rollback();
}
else
{
//UPDATE Master forecast Line Set processed to true
sql = "UPDATE C_MasterForecastLine SET Processed='Y' WHERE C_MasterForecast_ID=" + GetRecord_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
//UPDATE Master forecast Set processed to true
sql = "UPDATE C_MasterForecast SET Processed='Y' WHERE C_MasterForecast_ID=" + GetRecord_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
msg = Msg.GetMsg(mf.GetCtx(), "ProductLinesDetailCreated") + Count;
}
}
return(msg);
}