How to create monthly attendance sheet with MVC?


I want to display a monthly attendance sheet using the below SQL tables:

 Screenshot_1-min.png

this is the attendance table.

img2

this is the employee table.

Here is the image of the output, which I need in the view

img4

And here is the current C# Code for the action is:

public ActionResult MonthlyAttendance(DateTime? month)
{
var attendance = db.tbl_emp_attendance
.Where(a => a.date_and_time.Value.Month == month.Value.Month || month.Value.Month == null ||
month.Value.Month.ToString() == "").OrderBy(a=>a.date_and_time).ToList();
var count = attendance.Count;

for (var i = 0; i < count; i = i + 2)
{
if (attendance[i].date_and_time.Value.Date == attendance[i + 1].date_and_time.Value.Date &&
attendance[i].employee_id == attendance[i + 1].employee_id)
{

if (attendance[i].date_and_time.Value.TimeOfDay <
attendance[i + 1].date_and_time.Value.TimeOfDay)
{
var outTime = attendance[i + 1].date_and_time.Value.TimeOfDay;
var inTime = attendance[i].date_and_time.Value.TimeOfDay;
attendance[i].emp_inTime = inTime;
attendance[i].emp_outTime = outTime;
}
}

}
return View(attendance);
}

How can I do it?


Asked by:- SalmanZahir
0
: 664 At:- 2/13/2018 9:26:13 AM
asp.net mvc






6 Answers
profileImage Answered by:- Sam

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

emp-table-2-min.png

Attendance Table

attendance-table-min.png

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:

output-view-main-answer-min.png

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

3
At:- 2/14/2018 8:31:36 AM Updated at:- 6/21/2018 6:51:52 PM
That's a brilliant solution. But there is a problem. It is done for only the month of february. What about the other 12 months? 0
By : SalmanZahir - at :- 2/14/2018 10:28:35 AM
I have already explained, please read answer carefully also mentioned especially for you, month dates are count by taking month number value in View, so it will work 0
By : Sam - at :- 2/14/2018 3:26:33 PM


profileImage Answered by:- vikas_jk

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>

3
At:- 2/20/2018 8:33:32 AM
I think you can write Absent/Present instead of Time,Which is being printed right now, looks like you need to clear some basics 0
By : bhanu - at :- 2/21/2018 8:09:18 AM
@Salman, Did you even tried this solution??????? Looks working fine as expected, and please remember, we are not here to complete your project, use some of your technical skills and change your code accordingly,I tired Vikas's code in View and it works 0
By : Sam - at :- 2/21/2018 8:18:58 AM


profileImage Answered by:- Sam

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

new-output-date-min.png

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:

new-output-inout-time-min.png

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

2
At:- 2/15/2018 12:44:15 PM Updated at:- 2/15/2018 12:44:58 PM
I guess you didn't understand my question. You can see, the current code is creating rows for the same employee at different dates. Like for fatema khatun, for the column '1', there is a date, but for the column '2', there is a different row. column 2 of row number 1 is empty. 0
By : SalmanZahir - at :- 2/18/2018 6:13:46 AM


profileImage Answered by:- Sam

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:

monthly-attendance-system-final-output-min.png

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

2
At:- 2/21/2018 8:24:03 AM
Thank you so much. At last it worked. Followed your procedure. I am sorry that I bothered you so much. Thank you everyone. :) 0
By : SalmanZahir - at :- 2/25/2018 6:43:15 AM


profileImage Answered by:- SalmanZahir

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. 

img1

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);
}

 

-2
At:- 2/14/2018 11:18:21 AM Updated at:- 2/15/2018 3:19:16 PM
what do you mean by repetition? you don't want IN/Out time of a day for all employees just Absent/Present is enough? if "yes" Select Distincy by something like this List.Select(m => new {m.empName, m.empDate}).Distinct() , this will check both name and date and will give you distinct value, not test but should work 0
By : Sam - at :- 2/14/2018 3:33:10 PM
I tried it but it doesn't work. Everything comes blank. 0
By : SalmanZahir - at :- 2/15/2018 4:59:47 AM
I also want them in the same row, not different rows. 0
By : SalmanZahir - at :- 2/15/2018 6:11:56 AM


profileImage Answered by:- SalmanZahir

img1

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. 

-2
At:- 2/18/2018 6:31:23 AM
yes, you need to again change logic according to your need, As you know outer foreach loop in view printing it, remove it, get some other way to print Name OR try to print username at first loop only 0
By : Sam - at :- 2/19/2018 4:08:34 PM
Should I take a list of employee with viewbag and loop it? 0
By : SalmanZahir - at :- 2/20/2018 5:57:42 AM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use