locked
Turning Static Number into A variable with conditions RRS feed

  • Question

  • User-1681055142 posted

    Hello Asp.net Community,

    Let me first start off by saying I am a noob when it comes to c# and asp.net so please bare with me as I try to explain.

    I have been tasked with turning a static number into a variable. We are basically using an equation to calculate some data, which is based on a 12 month table (see attached image). Here is the line of code where we would like to change the 37.0 into a variable (full code is listed below).

    this.BudgetedPOs.Add(int.Parse(row["month"].ToString()),(double.Parse(row["value"].ToString()) / 100.0) * 37.0);

    I have been racking my brain as to the best way to accomplish this task. What I plan on doing is creating a separate page where a user would be able to update the variable based on the month. Maybe a table with a submit button; however I am not sure if that is the best option. Any insight would be helpful.

    Project stipulations:

    -The variable can only change for a month’s time, we’d still like to have a default value if the variable isn’t changed (default value = 32.0)

    -Next Month’s data will default from the current month

    -Once the calendar year has passed percentages should not be changed (example if we change the percentage for the month of Jan, when Feb comes around Jan’s percentage should not be able to be changed. Furthermore once the year is over there should be no way to update percentages for the previous year.

    Index Page: (Also what does the "n0" mean)?

    <tr>
        <td class="text-left">Budgeted PO's</td>
        @for (int i = 1; i <= 12; i++)
        	{
             if (i <= DateTime.Now.Month)
                 {
                   <td>@Model.BudgetedPOs[i].ToString("n0")</td>
                 }
             else
                 {
                   <td></td>
                 }
           }
    </tr>
    

    DLL (CS Page)(Here is the entire function)

    Task task8 = Task.Run(delegate {
                    this.BudgetedPOs = new Dictionary<int, double>();
                    if (PurchasingDashboardReports.Oracle.FakeConnection)
                    {
                        this.BudgetedPOs.Add(1, 4633409.2231);
                        this.BudgetedPOs.Add(2, 1937052.4604);
                        this.BudgetedPOs.Add(3, 3490624.3112);
                        this.BudgetedPOs.Add(4, 2676698.1743);
                        this.BudgetedPOs.Add(5, 2306698.1743);
                        this.BudgetedPOs.Add(6, 3416698.1743000005);
                        this.BudgetedPOs.Add(7, 0.0);
                        this.BudgetedPOs.Add(8, 0.0);
                        this.BudgetedPOs.Add(9, 0.0);
                        this.BudgetedPOs.Add(10, 0.0);
                        this.BudgetedPOs.Add(11, 0.0);
                        this.BudgetedPOs.Add(12, 0.0);
                    }
                    else
                    {
                        string queryString = string.Format(PurchasingDashboardReports.Oracle.Query_MonthlyBudgetedPOs, System.DateTime.Now.Year);
                        DataTable table = GiTools.Database.Oracle.SqlToDataTable(PurchasingDashboardReports.Oracle.DataSource, PurchasingDashboardReports.Oracle.UserID, PurchasingDashboardReports.Oracle.Password, queryString, true);
                        foreach (DataRow row in table.Rows)
                        {
                            this.BudgetedPOs.Add(int.Parse(row["month"].ToString()), (double.Parse(row["value"].ToString()) / 100.0) * 37.0);
                        }
                    }
                });
    

    Thanks in advance. Hopefully I am posting in the right section. 

    Thursday, October 22, 2015 11:49 AM

Answers

  • User325035487 posted

    I would set up my table like this

    http://sqlfiddle.com/#!6/f726d/1

    Then in the page where users will enter the value of percentage You can have a simple input field like this by querying  (T-SQL you may need equivalent in Oracle I assume)

    var sql = "SELECT percentage FROM Percentages WHERE Years=DateTime.Now.Year AND Months=DateTime.Now.Month";
    var percen = db.QueryValue(sql);
    <input type="number" name="percentage" id="percentage" value="@percen"  required />

    Then in your code block @{ ...  } after the first query data above

        var sql = "SELECT percentage FROM Percentages WHERE Years=DateTime.Now.Year AND Months=DateTime.Now.Month";
        var percen = db.QueryValue(sql);
        if (IsPost)
        {
            decimal percentage = Request.Form["percentage"].AsDecimal();
            if(percen != null)//Checks if Value inserted before
            {
                var sqlu = "UPDATE Percentages SET percentage=@0 WHERE Years=@1 AND Months=@2";
            }
            else
            {
                var sqlu = "INSERT INTO Percentages (Percentage,Years,Months) VALUES(@0,@1,@2)";            
            }
            db.Execute(sqlu, percen, DateTime.Now.Year, DateTime.Now.Month);
        }

    This way user can update only current month percentage.

    If you want to have the default value from previous month. Change the above code like this

        var sql = "SELECT percentage FROM Percentages WHERE Years=DateTime.Now.Year AND Months=DateTime.Now.Month";
        decimal percen = (decimal)db.QueryValue(sql);
        if (percen == null)
        {
                var pyear = DateTime.Now.Year;
                var pmonth = DateTime.Now.Month - 1;
                if (pmonth == 0) //Check for December and set previous year
                {
                    pmonth = 12;
                    pyear -= 1;
                }
                var sqllm = "SELECT percentage FROM Percentages WHERE Years=@0 AND Months=DateTime.Now.Month";
                decimal percen = (decimal)db.QueryValue(sqllm,pyear,pmonth);
        }
        if (IsPost)
        {
            decimal percentage = Request.Form["percentage"].AsDecimal();
            if(percen != null)//Checks if Value inserted before
            {
                var sqlu = "UPDATE Percentages SET percentage=@0 WHERE Years=@1 AND Months=@2";
            }
            else
            {
                var sqlu = "INSERT INTO Percentages (Percentage,Years,Months) VALUES(@0,@1,@2)";
            }
            db.Execute(sqlu, percen, DateTime.Now.Year, DateTime.Now.Month);
        }

    Haha. Hope I didnt complicate

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 25, 2015 1:56 AM

All replies

  • User379720387 posted

    Are you using Razor C# (WebMatrix)?

    I would create a table that holds the value for the month.

    var desiredmonth = 10 (i.e. October)

    var calcfactor = db.QueryValue("SELECT factor FROM yourtable WHERE MONTH(GETDATE()) = @0", desiredmonth);

    The end of your formula looks like this:  ) * @calcfactor);

    That is a number formatting directive.  You can find more on google looking for number formatting in c#

    Here is the same for date and time formatting: http://www.mikesdotnetting.com/article/23/date-formatting-in-c

    Thursday, October 22, 2015 8:43 PM
  • User-1681055142 posted

    Yes I am using C# I am not 100% on if we are using web matrix as I am new to asp.net c# etc. Thank you for providing insight your answer is much appreciated. Now my next question is how do we go about comparing dates as we want to be sure dates are not retro active. I assume we will need to use some comparison operators such as datetime.now etc. However would we need to store that information inside a database. Any insight would be helpful. 

    Thanks again.

    Friday, October 23, 2015 3:00 PM
  • User325035487 posted

    I would set up my table like this

    http://sqlfiddle.com/#!6/f726d/1

    Then in the page where users will enter the value of percentage You can have a simple input field like this by querying  (T-SQL you may need equivalent in Oracle I assume)

    var sql = "SELECT percentage FROM Percentages WHERE Years=DateTime.Now.Year AND Months=DateTime.Now.Month";
    var percen = db.QueryValue(sql);
    <input type="number" name="percentage" id="percentage" value="@percen"  required />

    Then in your code block @{ ...  } after the first query data above

        var sql = "SELECT percentage FROM Percentages WHERE Years=DateTime.Now.Year AND Months=DateTime.Now.Month";
        var percen = db.QueryValue(sql);
        if (IsPost)
        {
            decimal percentage = Request.Form["percentage"].AsDecimal();
            if(percen != null)//Checks if Value inserted before
            {
                var sqlu = "UPDATE Percentages SET percentage=@0 WHERE Years=@1 AND Months=@2";
            }
            else
            {
                var sqlu = "INSERT INTO Percentages (Percentage,Years,Months) VALUES(@0,@1,@2)";            
            }
            db.Execute(sqlu, percen, DateTime.Now.Year, DateTime.Now.Month);
        }

    This way user can update only current month percentage.

    If you want to have the default value from previous month. Change the above code like this

        var sql = "SELECT percentage FROM Percentages WHERE Years=DateTime.Now.Year AND Months=DateTime.Now.Month";
        decimal percen = (decimal)db.QueryValue(sql);
        if (percen == null)
        {
                var pyear = DateTime.Now.Year;
                var pmonth = DateTime.Now.Month - 1;
                if (pmonth == 0) //Check for December and set previous year
                {
                    pmonth = 12;
                    pyear -= 1;
                }
                var sqllm = "SELECT percentage FROM Percentages WHERE Years=@0 AND Months=DateTime.Now.Month";
                decimal percen = (decimal)db.QueryValue(sqllm,pyear,pmonth);
        }
        if (IsPost)
        {
            decimal percentage = Request.Form["percentage"].AsDecimal();
            if(percen != null)//Checks if Value inserted before
            {
                var sqlu = "UPDATE Percentages SET percentage=@0 WHERE Years=@1 AND Months=@2";
            }
            else
            {
                var sqlu = "INSERT INTO Percentages (Percentage,Years,Months) VALUES(@0,@1,@2)";
            }
            db.Execute(sqlu, percen, DateTime.Now.Year, DateTime.Now.Month);
        }

    Haha. Hope I didnt complicate

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 25, 2015 1:56 AM