How to create monthly attendance sheet with MVC?

If you would have followed @pika answer on your question https://qawithexperts.com/questions/229/create-dynamic-monthyear-calendar-attendance-sheet-in-net-mv, you would have already done it

Suppose there are my tables(both tables are linked where emp_id is PK of table tbl_employee while employee_id of attendance_table is FK)

Emp_table

Attendance Table

Here is my ViewModal

public class EmployeeAtt
    {
        public int Id { get; set; }
        public string Emp_name { get; set; }
        public DateTime? Date { get; set; }
        public TimeSpan InTime { get; set; }
        public TimeSpan OutTime { get; set; }
        
    }

In controller, after connecting it to DB here is my C# code

public ActionResult AttendanceTable()
        {
           //Get current month number, you can pass this value to controller ActionMethod also
            int Month = DateTime.Now.Month;
          
           //Create List
            List <EmployeeAtt> empWithDate = new List<EmployeeAtt>();
            using (var context = new StudentsEntities())
            {
               // get emp Name, Id, Date time and order it in ascending order of date
                empWithDate = context.attendance_table.Where(a=>a.date_and_time.Value.Month == Month)
                    .Select(a => 
                    new EmployeeAtt {
                        Id= a.Id,
                        Date = a.date_and_time,
                        Emp_name = a.tbl_employee.employee_name
                    }).OrderBy(a => a.Date).ToList();

            }
           
            return View(empWithDate);
        }

You can read the comments carefully and understand the code, in View

I will Create loop for number of Days of month(here current month, you can change it's value dynamically)

So, in View, you need to get number of days of month to loop and print it as <th> of table, then you need to loop all the Modal(list of employees with Date/time) & select date according to <th> value and print it using for loop, here is the complete view code with comments as code explanation

@model IEnumerable<ExcelExportMVC.Models.EmployeeAtt>
@{
    ViewBag.Title = "AttendanceTable";
     <!--Get number of days of current month-->
   var DaysInmonth= DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
}

<table class="table table-bordered">
    <thead>
        <tr>
            <th>EmpName</th>
            <!--Loop all the days of month and print it-->
            @for (var numb=1; numb<= DaysInmonth; numb++)
            {
                <th>@numb</th>
            }

        </tr>
    </thead>
    <tbody>
        <!--Loop model-->
        @foreach (var emp  in Model)
        {
            <tr>
                 <!--print employee name one time only at the start of row-->
                <td>@emp.Emp_name</td>
                
                     <!--loop all days of month-->
                    @for (var numb = 1; numb <= DaysInmonth; numb++)
                    {
                        <td>
                       @{
                            <!--print only that date time value which is equal to current date(as it will match column header) and current employee name, else print empty-->
                           var GetThatDayValue = Model.Where(a => a.Date.Value.Day == numb && a.Emp_name== emp.Emp_name && a.Date.Value== emp.Date).FirstOrDefault();
                           var DD = GetThatDayValue != null ? GetThatDayValue.Date.Value.ToString("dd/MM/yyyy hh:mm") : "";
                           <text> @DD </text>
                        }
                      </td>
                    }




               
               
            </tr>
        }
    </tbody>
</table>

Output of the View:

@Salman, please read all the answer code/comments carefully, and change code according to your use(if needed)

You can create a field CurrentName, check if it that name is already looped, if yes, skip that, else continue, I have just changed View code, this can be performed using C# Controller also but to make it easier for you, here is the view code which should work for you

@model IEnumerable<ExcelExportMVC.Models.EmployeeAtt>
@{
    ViewBag.Title = "AttendanceTable";
     <!--Get number of days of current month-->
    var DaysInmonth= DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
    <!--Create a CurrentName field-->
    var CurrentName = "";
}

<table class="table table-bordered">
    <thead>
        <tr>
            <th>EmpName</th>
            <!--Loop all the days of month and print it-->
            @for (var numb=1; numb<= DaysInmonth; numb++)
            {
                <th>@numb</th>
            }

        </tr>
    </thead>
    <tbody>
        <!--Loop model-->
        @foreach (var emp  in Model)
        {
             //if Name is repeated, skip
            if (CurrentName != emp.Emp_name)
            {
                // Set Name
                CurrentName = emp.Emp_name;
            <tr>
                 <!--print employee name one time only at the start of row-->
                <td>@emp.Emp_name</td>
                
                     <!--loop all days of month-->
                    @for (var numb = 1; numb <= DaysInmonth; numb++)
                    {
                        <td>
                       @{
                            <!--print only that date time value which is equal to current date(as it will match column header) and current employee name, else print empty-->
                           var GetThatDayValue = Model.Where(a => a.Date.Value.Day == numb && a.Emp_name == emp.Emp_name).FirstOrDefault();
                           var DD = GetThatDayValue != null ? GetThatDayValue.InoutTime : "";
                           <text> @DD </text>
                        }
                      </td>
                           }




               
               
            </tr>
            }
        }
    </tbody>
</table>

You would always need to change logic according to your output, I don't know what you need in the end or what your client's demand, i am just helping you, looks like I should get paid now for this :)

Here is the new output

I did this change in my C# controller ActionMethod, it looks like this now

 public ActionResult AttendanceTable()
        {
            int Month = DateTime.Now.Month;
           
            List <EmployeeAtt> empWithDate = new List<EmployeeAtt>();
            using (var context = new StudentsEntities())
            {
                empWithDate = context.attendance_table.Where(a=>a.date_and_time.Value.Month == Month).GroupBy(a => 
                new {
                    Date=System.Data.Entity.Core.Objects.EntityFunctions.TruncateTime(a.date_and_time),
                   
                    a.tbl_employee.employee_name }
                )
                    .Select(a => 
                    new EmployeeAtt {
                       
                        Date = a.Key.Date,
                        Emp_name = a.Key.employee_name
                    }).OrderBy(a => a.Date).ToList();

            }
           
            return View(empWithDate);
        }

View is almost same, just one line code is changed

<!--then--> 
var DD = GetThatDayValue != null ? GetThatDayValue.Date.Value.ToString("dd/MM/yyyy hh:mm") : "";

<!--now--> 
var DD = GetThatDayValue != null ? GetThatDayValue.Date.Value.ToString("dd/MM/yyyy") : "";

That's it you are done.

If you need another way, if you need Output like In-Out time, you nee, change logic agai, here is C# ActionMethod

public ActionResult AttendanceTable()
        {
            int Month = DateTime.Now.Month;
           
            List <EmployeeAtt> empWithDate = new List<EmployeeAtt>();
            using (var context = new StudentsEntities())
            {
                var List = context.attendance_table.Where(a => a.date_and_time.Value.Month == Month).OrderBy(a => a.date_and_time).ToList();

                var empatt = new EmployeeAtt();
                foreach (var emp in List)
                {
                    //create new employee object fo each loop

                    //Get Array list for each emp using it's Id and sort list according to datetime
                    var attendnaceList = List.Where(a => a.employee_id == emp.employee_id).OrderBy(a => a.date_and_time).ToArray();

                    //Get Count
                    var Count = attendnaceList.Count();

                    //Loop through all values and increment it by 2 as we will compare two values in/out
                    for (var i = 0; i < Count; i = i + 2)
                    {
                        empatt = new EmployeeAtt();
                        //Although no need to check but confirm if both the values are of same date,which we will compare
                        if (attendnaceList[i].date_and_time.Value.Date == attendnaceList[i + 1].date_and_time.Value.Date)
                        {
                            //Check which value is less using Time now,as it is sorted  first value will be less than second
                            if (attendnaceList[i].date_and_time.Value.TimeOfDay < attendnaceList[i + 1].date_and_time.Value.TimeOfDay)
                            {
                                empatt.Id = Convert.ToInt32(emp.employee_id);
                                empatt.Emp_name = emp.tbl_employee.employee_name;
                                empatt.Date = attendnaceList[i].date_and_time.Value.Date;

                                //inout time
                                empatt.InoutTime = attendnaceList[i].date_and_time.Value.TimeOfDay + "-" + attendnaceList[i + 1].date_and_time.Value.TimeOfDay;

                                //to avoid duplicate check if value already exists or not, if not add one
                                if(empWithDate.Where(a=>a.Emp_name== attendnaceList[i].tbl_employee.employee_name && a.Date.Value.Date == attendnaceList[i].date_and_time.Value.Date).Count() ==0)
                                {
                                  empWithDate.Add(empatt);
                                }

                            }
                        }
                    }
                }
                empWithDate.OrderBy(a => a.Date).ToList();

            }



            return View(empWithDate);
        }

View only one line changed to show Inouttime

 var DD = GetThatDayValue != null ? GetThatDayValue.InoutTime : "";

And Added new string field in ViewModal

public class EmployeeAtt
    {
        public int Id { get; set; }
        public string Emp_name { get; set; }
        public DateTime? Date { get; set; }
        public TimeSpan InTime { get; set; }
        public TimeSpan OutTime { get; set; }

        //New Field
        public string InoutTime { get; set; }


    }

Output:

Always try to change basic logic according to your need, you already had important part code.

According to @Vikas's Answer, copy pasting the code in View Gives's time, I have replaced it with Absent/Present like school attendance, please read answer's carefully always & SIR CLEAR YOUR BASICS  of HTML / C#

Here is View Code, C# is same

@model IEnumerable<ExcelExportMVC.Models.EmployeeAtt>
@{
    ViewBag.Title = "AttendanceTable";
     <!--Get number of days of current month-->
    var DaysInmonth= DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
    <!--Create a CurrentName field-->
    var CurrentName = "";
}

<table class="table table-bordered">
    <thead>
        <tr>
            <th>EmpName</th>
            <!--Loop all the days of month and print it-->
            @for (var numb=1; numb<= DaysInmonth; numb++)
            {
                <th>@numb</th>
            }

        </tr>
    </thead>
    <tbody>
        <!--Loop model-->
        @foreach (var emp  in Model)
        {
             //if Name is repeated, skip
            if (CurrentName != emp.Emp_name)
            {
                // Set Name
                CurrentName = emp.Emp_name;
            <tr>
                 <!--print employee name one time only at the start of row-->
                <td>@emp.Emp_name</td>
                
                     <!--loop all days of month-->
                    @for (var numb = 1; numb <= DaysInmonth; numb++)
                    {
                        <td>
                       @{
                            <!--print only that date time value which is equal to current date(as it will match column header) and current employee name, else print empty-->
                           var GetThatDayValue = Model.Where(a => a.Date.Value.Day == numb && a.Emp_name == emp.Emp_name).FirstOrDefault();
                           var DD = GetThatDayValue != null ? GetThatDayValue.InoutTime +" (Present)" : "A";
                           <text> @DD </text>
                        }
                      </td>
                           }




               
               
            </tr>
            }
        }
    </tbody>
</table>

Here is my final output:

I am using same SQL Table and C# Controller code, if your requirement changes, again, please change code accordingly, thanks

Getting System.IndexOutOfRangeException: 'Index was outside the bounds of the array.' on your code.

Here are my files

Employee table

 public partial class TblEmployee
    {
        public TblEmployee()
        {
            AttendanceTable = new HashSet<AttendanceTable>();
        }

        public int EmpId { get; set; }
        public string EmployeeName { get; set; }

        public virtual ICollection<AttendanceTable> AttendanceTable { get; set; }
    }

Attendance Table

 public partial class AttendanceTable
    {
        public int Id { get; set; }
        public int AttendanceId { get; set; }
        public int EmployeeId { get; set; }
        public string ScanType { get; set; }
        public DateTime DateAndTime { get; set; }

        public virtual TblEmployee Employee { get; set; }
    }

View Model

 public class EmployeeAtt
    {
        public int Id { get; set; }
        public string Emp_name { get; set; }
        public DateTime? Date { get; set; }
        public TimeSpan InTime { get; set; }
        public TimeSpan OutTime { get; set; }

        //New Field
        public string InoutTime { get; set; }
    }

Context Class

public partial class CheckItContext : DbContext
    {
        public CheckItContext()
        {
        }

        public CheckItContext(DbContextOptions<CheckItContext> options)
            : base(options)
        {
        }

        public virtual DbSet<AttendanceTable> AttendanceTable { get; set; }
        public virtual DbSet<TblEmployee> TblEmployee { get; set; }

Controller

        public ActionResult Index() 
        {
            int Month = DateTime.Now.Month;

            List<EmployeeAtt> empWithDate = new List<EmployeeAtt>();
            using (_context)
            {
                var List = _context.AttendanceTable.Where(a => a.DateAndTime.Month == Month).OrderBy(a => a.DateAndTime).ToList();

                var empatt = new EmployeeAtt();
                foreach (var emp in List)
                {
                    //create new employee object fo each loop

                    //Get Array list for each emp using it's Id and sort list according to datetime
                    var attendnaceList = List.Where(a => a.EmployeeId == emp.EmployeeId).OrderBy(a => a.DateAndTime).ToArray();

                    //Get Count
                    var Count = attendnaceList.Count();

                    //Loop through all values and increment it by 2 as we will compare two values in/out
                    for (var i = 0; i < Count; i = i + 2)
                    {
                        empatt = new EmployeeAtt();
                        //Although no need to check but confirm if both the values are of same date,which we will compare
                        if (attendnaceList[i].DateAndTime.Date == attendnaceList[i + 1].DateAndTime.Date)
                        {
                            //Check which value is less using Time now,as it is sorted  first value will be less than second
                            if (attendnaceList[i].DateAndTime.TimeOfDay < attendnaceList[i + 1].DateAndTime.TimeOfDay)
                            {
                                empatt.Id = Convert.ToInt32(emp.EmployeeId);
                               // empatt.Emp_name = emp.Employee.EmployeeName;
                                empatt.Date = attendnaceList[i].DateAndTime.Date;

                                //inout time
                                empatt.InoutTime = attendnaceList[i].DateAndTime + "-" + attendnaceList[i + 1].DateAndTime;

                                //to avoid duplicate check if value already exists or not, if not add one
                                if (empWithDate.Where(a => a.Emp_name == attendnaceList[i].Employee.EmployeeName && a.Date.Value.Date == attendnaceList[i].DateAndTime.Date).Count() == 0)
                                {
                                    empWithDate.Add(empatt);
                                }

                            }
                        }
                    }
                }
                empWithDate.OrderBy(a => a.Date).ToList();

            }

Hey Samir bro, I was partly successful. Now the only problem is repetition. If the date is same, It should show only one row for each employee. I tried return view(attendance.DistinctBy(a=>a.employee_id)) but it didn't work. Nothing shows if I give Distinct. 

This is my table for the month February. 

This is the current code with search functionality included-------

public ActionResult MonthlyAttendance(DateTime? month)
{
var attendance = db.tbl_emp_attendance

.Where(a => a.date_and_time.Value.Month == month.Value.Month &&
a.date_and_time.Value.Year == month.Value.Year).OrderBy(a => a.date_and_time).ToList();
foreach (var att in attendance)
{
if (month != null)
{
att.month = month.Value.Month;
att.year = month.Value.Year;
}
else
{
att.month = DateTime.Now.Month;
att.year = DateTime.Now.Year;
}
}
foreach (var employee in attendance)
{
var emp = db.tbl_emp_attendance.OrderBy(a => a.date_and_time).ToList();
foreach (var emp1 in emp)
{
if (employee.date_and_time.Value.Date == emp1.date_and_time.Value.Date &&
employee.employee_id == emp1.employee_id)
{
var actualInTime = employee.date_and_time.Value.Date + new TimeSpan(10, 00, 00);
var actualOutTime = employee.date_and_time.Value.Date + new TimeSpan(17, 00, 00);
var actualWorkingHour = actualOutTime - actualInTime;
if (employee.date_and_time.Value.TimeOfDay < emp1.date_and_time.Value.TimeOfDay)
{
employee.emp_inTime = employee.date_and_time.Value.TimeOfDay;
employee.emp_outTime = emp1.date_and_time.Value.TimeOfDay;
}
else if (employee.date_and_time.Value.TimeOfDay == emp1.date_and_time.Value.TimeOfDay)
{
employee.emp_inTime = employee.date_and_time.Value.TimeOfDay;
employee.emp_outTime = actualOutTime.TimeOfDay;
}
else
{
employee.emp_inTime = emp1.date_and_time.Value.TimeOfDay;
employee.emp_outTime = employee.date_and_time.Value.TimeOfDay;
}

var inTime = employee.emp_inTime;
var outTime = employee.emp_outTime;
if (inTime < actualInTime.TimeOfDay)
{
inTime = actualInTime.TimeOfDay;
}
var employeeworkingHour = outTime - inTime;

var overTime = outTime - actualOutTime.TimeOfDay;

var lateTime = actualInTime.TimeOfDay - inTime;


var actualMonthlyWorkingMinute = (int)actualWorkingHour.TotalMinutes * 30;
var mainSalary = employee.tbl_employee.emp_gross_amount;
var mainPerminuteSalary = mainSalary / ((int)actualMonthlyWorkingMinute);
var perHoursalary = (int)employeeworkingHour.TotalMinutes * mainPerminuteSalary;
var overtimeHourlySalary = (int)overTime.TotalMinutes * mainPerminuteSalary;
var deductedHourlySalary = (int)lateTime.TotalMinutes * mainPerminuteSalary;
var actualSalary = ((int)employeeworkingHour.TotalMinutes) * mainPerminuteSalary * 30;

employee.emp_workingHour = employeeworkingHour;
employee.emp_calculatedSalary = perHoursalary;
employee.emp_overTime = overTime;
employee.emp_late = lateTime.Duration();
employee.emp_overtime_hourly_salary = overtimeHourlySalary;
employee.emp_late_hourly_salary = deductedHourlySalary;

}
}
}

 


return View(attendance);
}

This is what I was talking about. It is creating different rows for each entry. Imaging there are 6 employees. Then there will be 30 rows for each employee for a month isn't it? Then 180 rows for 6 employees. How big the table will be? I want the 'Present' or 'Absent' in SAME ROW FOR EACH EMPLOYEE.