/// <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);
                }
            }
        }
Example #2
0
        /// <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);
                }
            }
        }