Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to vote.
See more:
My problem:

I need to add 'overtime' for all previous months from the beginning of the year to the month selected.

My table at sql server:

Overtime : varchar(20)
Id | Login | Year | Month | Day | Overtime
---+-------+------+-------+-----+----------
 1 | MIPA  | 2020 |   1   |  1  |  08:00
 2 | MIPA  | 2020 |   1   |  2  |  08:00
 3 | MIPA  | 2020 |   1   |  3  |  07:30
 4 | MIPA  | 2020 |   1   |  4  |  12:00
...
30 | MIPA  | 2020 |   1   |  30 |  04:00
...
41 | MIPA  | 2020 |   2   |  1  |  08:00
42 | MIPA  | 2020 |   2   |  2  |  08:00
43 | MIPA  | 2020 |   2   |  3  |  07:30
44 | MIPA  | 2020 |   2   |  4  |  12:00
...
52 | MIPA  | 2020 |   2   |  25 |  04:00

//so if the user chose March (3) and year: 2020(in dropdownlist). This function must extract the aggregated information from the overtime column of the previous months of the selected year. (that is, in the example for January (1) and February (2) and for 2020)

Controller:
[HttpPost]
public async Task<ActionResult> PartialTableEcp()  //change nothing
{
// Data is already extracted from other operations
// So e.g. user enters chooses year: 2020 month: March (3) 
int numberMonth= 3;
int numberYear= 2020;
int numberOfDays= 31;

// person identification by login
var userName = _httpContextAccessor.HttpContext.User.Identity.Name;

// retrieve all month records from the table, (i dont know it works)
var existingRecords = _tableEcpContext.Karta
                                      .Where(x => x.Login == userName && 
                                                  x.Year == numberYear && 
                                                  x.Month == numberMonth-1) ////will mathematical calculation work here?
                                      .Select(i => new{
                                                       i.Overtime
                                                       }).ToList(); 


and now there is a problem how to sum all these values ​​.. preferably in HHH: mm format


var thisValue = that this value would be written to the variable var;

var viewModel = new ParentView { Model1 = karta };
return PartialView("_TableViewEcp", viewModel); //change nothing

}

Model:

public partial class Karta_Model
    {
        [Key]
        public int Id { get; set; }
        public string? Login { get; set; }
        public int Year { get; set; }
        public int Month{ get; set; }
        public int? DayOfMonth { get; set; }
        public string? Overtime { get; set; }
     }

public partial class ParentView
    {
        public List<Karta_Model> Model1 { get; set; }
    }


What I have tried:

anyone have an idea?


what I wrote is at the top
Posted
Updated 10-Feb-20 10:46am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Your LINQ query as written will only return records for the month prior to what is selected
x.Month == numberMonth-1
What you would need to do is alter this to be less than the selected month
x.Month < numberMonth
Now adding this information up is may be a little tricky. It appears that the Overtime column in your table is of the Time data-type, and the Model you are using has this as a nullable string.
Now I understand that Overtime really isn't a time, it is a period of time; which .NET has natively. It is called a TimeSpan, and TimeSpans can added together.

Now you are going to need to review some documentation (see links below) and figure out how you are going to implement this.
If it was me; I would be changing the Model to the proper type and then just summing the values.

References:
TimeSpan Struct (System) | Microsoft Docs[^]
TimeSpan.Add(TimeSpan) Method (System) | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100