/// <summary>
/// Update Header
/// </summary>
private void UpdateHeader()
{
int id = GetC_ProjectTask_ID();
int projID = 0;
if (id == 0)
{
projID = GetC_Project_ID(); // Marketing Campaign Window
}
else
{
//Used transaction because total was not updating on header
string Sql = "SELECT C_Project_ID FROM C_ProjectPhase WHERE C_ProjectPhase_ID in(select C_ProjectPhase_ID FROM" +
" C_ProjectTask WHERE C_ProjectTask_ID =" + id + ")";
projID = Util.GetValueOfInt(DB.ExecuteScalar(Sql, null, Get_TrxName()));
}
//Used transaction because total was not updating on header
string sql = "SELECT IsOpportunity FROM C_Project WHERE C_Project_ID = " + projID;
string isOpp = Util.GetValueOfString(DB.ExecuteScalar(sql, null, Get_TrxName()));
//Amit
string isCam = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsCampaign FROM C_Project WHERE C_Project_ID = " + projID, null, Get_TrxName()));
if (isOpp.Equals("N") && isCam.Equals("N") && id != 0)
{
// set sum of planned Amount from task line to task
MProjectTask tsk = new MProjectTask(GetCtx(), id, Get_Trx());
//Used transaction because total was not updating on header
decimal plannedAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_ProjectTask_ID = " + id, null, Get_TrxName()));
tsk.SetPlannedAmt(plannedAmt);
tsk.Save();
}
//Amit
else if (isOpp.Equals("N") && isCam.Equals("N") && id == 0 && GetC_ProjectPhase_ID() != 0)
{
//Used transaction because total was not updating on header
MProjectPhase projectPhase = new MProjectPhase(GetCtx(), GetC_ProjectPhase_ID(), Get_TrxName());
decimal plnAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_ProjectPhase_ID = " + GetC_ProjectPhase_ID() + " AND pl.C_Project_ID = " + projID, null, Get_TrxName()));
projectPhase.SetPlannedAmt(plnAmt);
projectPhase.Save();
}
else if (isOpp.Equals("Y")) // Opportunity Window
{
//Used transaction because total was not updating on header
MProject prj = new MProject(GetCtx(), projID, Get_TrxName());
decimal plnAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + projID, null, Get_TrxName()));
prj.SetPlannedAmt(plnAmt);
prj.Save();
if (VAdvantage.Utility.Env.IsModuleInstalled("VA077_"))
{
sql = @"UPDATE C_Project SET VA077_TotalMarginAmt=(SELECT ROUND(Sum(VA077_MarginAmt),2) FROM C_ProjectLine
WHERE C_PROJECT_ID=" + projID + @" AND IsActive='Y'),
VA077_TotalPurchaseAmt=(SELECT ROUND(Sum(VA077_PurchaseAmt),2) FROM C_ProjectLine
WHERE C_PROJECT_ID=" + projID + @" AND IsActive='Y'),
VA077_MarginPercent=(SELECT CASE WHEN Sum(PlannedAmt) > 0 Then
ROUND(((Sum(PlannedAmt)- Sum(NVL(VA077_PurchaseAmt,0)))/Sum(PlannedAmt)*100),2) ELSE 0 END
FROM C_ProjectLine WHERE C_PROJECT_ID=" + projID + @" AND IsActive='Y')
WHERE C_Project_ID=" + projID;
int no = DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 1)
{
log.Log(Level.SEVERE, "updateHeader - #" + no);
}
}
}
else if (id != 0)
{
MProjectTask tsk = new MProjectTask(GetCtx(), id, Get_TrxName());
//Used transaction because total was not updating on header
decimal plannedAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_ProjectTask_ID = " + id, null, Get_TrxName()));
tsk.SetPlannedAmt(plannedAmt);
tsk.Save();
}
else
{
sql = "UPDATE C_Project p SET " +
"PlannedAmt=(SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",PlannedQty=(SELECT COALESCE(SUM(pl.PlannedQty),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",PlannedMarginAmt=(SELECT COALESCE(SUM(pl.PlannedMarginAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",CommittedAmt=(SELECT COALESCE(SUM(pl.CommittedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",CommittedQty=(SELECT COALESCE(SUM(pl.CommittedQty),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",InvoicedAmt=(SELECT COALESCE(SUM(pl.InvoicedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
", InvoicedQty =(SELECT COALESCE(SUM(pl.InvoicedQty),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
" WHERE p.C_Project_ID=" + GetC_Project_ID();
int no = DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 1)
{
log.Log(Level.SEVERE, "updateHeader - #" + no);
}
}
}
/// <summary>
/// Update Header
/// </summary>
private void UpdateHeader()
{
int id = GetC_ProjectTask_ID();
int projID = 0;
if (id == 0)
{
projID = GetC_Project_ID(); // Marketing Campaign Window
}
else
{
string Sql = "SELECT C_Project_ID FROM C_ProjectPhase WHERE C_ProjectPhase_ID in(select C_ProjectPhase_ID FROM" +
" C_ProjectTask WHERE C_ProjectTask_ID =" + id + ")";
projID = Util.GetValueOfInt(DB.ExecuteScalar(Sql, null, null));
}
string sql = "SELECT IsOpportunity FROM C_Project WHERE C_Project_ID = " + projID;
string isOpp = Util.GetValueOfString(DB.ExecuteScalar(sql, null, null));
//Amit
string isCam = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsCampaign FROM C_Project WHERE C_Project_ID = " + projID));
if (isOpp.Equals("N") && isCam.Equals("N") && id != 0)
{
// set sum of planned Amount from task line to task
MProjectTask tsk = new MProjectTask(GetCtx(), id, Get_Trx());
decimal plannedAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_ProjectTask_ID = " + id));
tsk.SetPlannedAmt(plannedAmt);
tsk.Save();
}
//Amit
else if (isOpp.Equals("N") && isCam.Equals("N") && id == 0 && GetC_ProjectPhase_ID() != 0)
{
MProjectPhase projectPhase = new MProjectPhase(GetCtx(), GetC_ProjectPhase_ID(), null);
decimal plnAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_ProjectPhase_ID = " + GetC_ProjectPhase_ID() + " AND pl.C_Project_ID = " + projID));
projectPhase.SetPlannedAmt(plnAmt);
projectPhase.Save();
}
else if (isOpp.Equals("Y")) // Opportunity Window
{
MProject prj = new MProject(GetCtx(), projID, Get_TrxName());
decimal plnAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + projID));
prj.SetPlannedAmt(plnAmt);
prj.Save();
}
else if (id != 0)
{
MProjectTask tsk = new MProjectTask(GetCtx(), id, Get_TrxName());
decimal plannedAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_ProjectTask_ID = " + id));
tsk.SetPlannedAmt(plannedAmt);
tsk.Save();
}
else
{
sql = "UPDATE C_Project p SET " +
"PlannedAmt=(SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",PlannedQty=(SELECT COALESCE(SUM(pl.PlannedQty),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",PlannedMarginAmt=(SELECT COALESCE(SUM(pl.PlannedMarginAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",CommittedAmt=(SELECT COALESCE(SUM(pl.CommittedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",CommittedQty=(SELECT COALESCE(SUM(pl.CommittedQty),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
",InvoicedAmt=(SELECT COALESCE(SUM(pl.InvoicedAmt),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
", InvoicedQty =(SELECT COALESCE(SUM(pl.InvoicedQty),0) FROM C_ProjectLine pl WHERE pl.IsActive = 'Y' AND pl.C_Project_ID = " + GetC_Project_ID() + ")" +
" WHERE p.C_Project_ID=" + GetC_Project_ID();
int no = DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 1)
{
log.Log(Level.SEVERE, "updateHeader - #" + no);
}
}
}