locked
How can show employee Min clock time and Max clock out time RRS feed

  • Question

  • User-1355965324 posted

    I have  the following model in my program. Also I have the Id sequence column  in EmpClocking model .  Some time the employee will be clock in not clocked out . 

    EmployeeModel
    EmpCode string
    EmpName string
    
    
    EmpClocking Model
    [key]
    Id int EmpCode string ClockDate datetime Employee Record Insert into Employee('100', 'aaaa') Insert into Employee('101', 'xxxx') Insert into Employee('102', 'yyyy') Insert into Employee('103', 'zzzz') Insert into Employee('104', 'ssss') Insert into Employee('105', 'bbbb') AttendanceView Model EmpCode string AttendanceDate datetime ClockInTime string ClockOutTIme string Attendance Record Insert into EmpClocking ('101', '2019-05-10 08:31:03.000') Insert into EmpClocking ('101', '2019-05-10 14:38:17.000') Insert into EmpClocking ('102', '2019-05-10 08:31:48.000') Insert into EmpClocking ('102', '2019-05-10 17:08:06.000') Insert into EmpClocking ('103', '2019-05-10 08:34:30.000') Insert into EmpClocking ('103', '2019-05-10 11:04:25.000') Insert into EmpClocking ('103', '2019-05-10 12:27:52.000') Insert into EmpClocking ('104', '2019-05-10 08:34:30.000') Insert into EmpClocking ('104', '2019-05-10 11:04:25.000') Insert into EmpClocking ('104', '2019-05-10 12:27:52.000') Insert into EmpClocking ('104', '2019-05-10 17:27:52.000')

    Insert into EmpClocking ('105', '2019-05-10 08:27:52.000')

    I want to list employees all the clock in and clock out time for each date 

    public List<AttendanceView> GetEmpAttendance('100,101,102,103,104,105', fromdate datetime, todate datetime)
    {
        what is the linq expression to get the result as given below
    }
    AttendanceView
    Code         Date    		Clockin     Clockout
    100          
    101          10/05/2019         08:31        14:38    
    102          10/05/2019         08:31        17:08 
    103          10/05/2019         08:34        12:27 
    104          10/05/2019         08:34        17:27 
    105          10/05/2019         08:27

    Please can you help it would be very appreciated

    With Thanks

    Pol

    Saturday, January 9, 2021 9:08 AM

Answers

  • User1686398519 posted

    Hi polachan, 

    You have submitted a similar post before, I gave a complete example, you can refer to it.

    According to your requirements, you only need to modify the query and Model.

    Model

        public class EmployeeViewModel
        {
            public string EmpCode { get; set; }
            public string EmpName { get; set; }
            public string Date { get; set; }
            public string Clockin { get; set; }
            public string Clockout { get; set; }
        }

    Controller

                var result = db.Employees.ToList().GroupJoin(
                    db.EmpClockings.ToList(),
                    em => em.EmpCode,
                    ec => ec.EmpCode,
                    (emp, empclock) => new
                    {
                        employee = emp,
                        dateAndTime = empclock.Where(i=>i.ClockDate.Date.ToString().Equals(clockdate.Date.ToString())).Select(m => m.ClockDate).ToList()
                    })
                    .Where(m => m.employee.EmpCode== empcode)
                    .Select(m => new EmployeeViewModel
                    {
                        EmpCode = m.employee.EmpCode,
                        EmpName = m.employee.EmpName,
                        Date = DateTime.Parse(m.dateAndTime.FirstOrDefault().ToString()).ToString("MM/dd/yyyy"),
                        Clockin = DateTime.Parse(m.dateAndTime.Min().ToString()).ToString("HH:mm"),
                        Clockout = m.dateAndTime.Count() <= 1 ? "" : DateTime.Parse(m.dateAndTime.Max().ToString()).ToString("HH:mm")
                    });

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 13, 2021 6:36 AM

All replies

  • User475983607 posted

    The EmpClocking table needs at least one more column to log if the DateTime record is a "Clock In" or "Clock Out".  Your table design is dependent on order which is not a good choice which leads to complex code.

    Consider doing a Google search for timesheet designs.  Your question has been asked and answered many many times.

    https://www.google.com/search?q=timesheet+mvc

    Saturday, January 9, 2021 12:49 PM
  • User-1355965324 posted

    Sorry I forgot to give Id column for clocking model .  Clocking model have another one sequence column Id.

    Thanks mgebhard

    Saturday, January 9, 2021 6:48 PM
  • User475983607 posted

    Sorry I forgot to give Id column for clocking model .  Clocking model have another one sequence column Id.

    You have not explained how a sequence is used in the general design.   Typically, timesheets designs have a column that identifies the time record type when the record is created.   For example, the start of the work day, breaks, and end of the work day.  Again this problem has been solved and there are plenty examples online.

    Lastly, the members can only see the the code you share on the forum.  Neglecting to tell us about the sequence or how it is intended to be used does not help us to help you.

    Saturday, January 9, 2021 11:57 PM
  • User-1355965324 posted

    hi mgebhard

    the Id  is auto increment column.

    Sunday, January 10, 2021 8:11 AM
  • User475983607 posted

    the Id  is auto increment column.

    Seems to me an auto increment column would cause more complexity.  What if a user forgets to clock out and clocks in again?  For the second time, can you explain your design intent rather than making the community guess what you are thinking.

    Also, can you explain why my design advice does not meet your requirements?

    Sunday, January 10, 2021 12:56 PM
  • User1686398519 posted

    Hi polachan, 

    According to your needs, I wrote an example, you can refer to it.

    Model

        public class EmployeeViewModel
        {
            public string EmpCode { get; set; }
            public string EmpName { get; set; }
            public List<ClockViewModel> ClockViewModel { get; set; }
        }
        public class ClockViewModel
        {
            public string Date { get; set; }
            public string Clockin { get; set; }
            public string Clockout { get; set; }
        }
        public class Employee
        {
            [Key]
            public int Id { get; set; }
            public string EmpCode { get; set; }
            public string EmpName { get; set; }
        }
        public class EmpClocking
        {
            [Key]
            public int Id { get; set; }
            public DateTime ClockDate { get; set; }
            public string EmpCode { get; set; }
        }

    Controller

        public class EmployeeController : Controller
        {
            private DailyCoreMVCDemoContext db;
            public EmployeeController(DailyCoreMVCDemoContext _db)
            {
                db = _db;
            }
            public IActionResult Index()
            {
                var result = db.Employees.ToList().GroupJoin(
                    db.EmpClockings.ToList(),
                    em => em.EmpCode,
                    ec => ec.EmpCode,
                    (emp, empclock) => new
                    {
                        employee = emp,
                        dateAndTime = empclock.Select(m=>m.ClockDate).ToList()
                    }).Select(m => new EmployeeViewModel
                    {
                     EmpCode = m.employee.EmpCode,
                     EmpName = m.employee.EmpName,
                     ClockViewModel = m.dateAndTime.ToList().GroupBy(g => g.Date)
                                        .Select(i => new ClockViewModel
                                        {
                                            Date = DateTime.Parse(i.Key.ToString()).ToString("MM/dd/yyyy"),
                                            Clockin = DateTime.Parse(i.Min().ToString()).ToString("HH:mm"),
                                            Clockout = i.Count()<=1?"":DateTime.Parse(i.Max().ToString()).ToString("HH:mm")
                                        }).ToList()
                 }).ToList();
                return View(result);
            }
        }

    View

    @model IEnumerable<DailyCoreMVCDemo.Models.EmployeeViewModel>
    <table class="table table-bordered table-light table-striped">
        <thead>
            <tr><th>Index</th><th>EmpCode</th><th>EmpName</th></tr>
        </thead>
        <tbody>
            @for (var i = 0; i < Model.Count(); i++)
            {
                <tr>
    
                    <td>@i</td>
                    <td>@Html.DisplayTextFor(m => Model.ToList()[i].EmpCode)</td>
                    <td>@Html.DisplayTextFor(m => Model.ToList()[i].EmpName)</td>
                </tr>
                if (Model.ToList()[i].ClockViewModel.Count() != 0)
                {
                    <tr>
                        <td colspan="3">
                            <table class="table table-success table-borderless table-striped">
                                <thead>
                                    <tr>
                                        <th>Date</th>
                                        <th>Clockin</th>
                                        <th>Clockout</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    @for (var j = 0; j < Model.ToList()[i].ClockViewModel.Count(); j++)
                                    {
                                        <tr>
                                            <td>@Html.DisplayTextFor(m => Model.ToList()[i].ClockViewModel[j].Date)</td>
                                            <td>@Html.DisplayTextFor(m => Model.ToList()[i].ClockViewModel[j].Clockin)</td>
                                            <td>@Html.DisplayTextFor(m => Model.ToList()[i].ClockViewModel[j].Clockout)</td>
                                        </tr>
                                    }
                                </tbody>
                            </table>
                        </td>
                    </tr>
                }
                else
                {
                    <tr>
                        <td colspan="3">
                            <table class="table table-success table-borderless table-striped">
                                <tbody>
                                        <tr>
                                            <td>No Data!</td>
                                        </tr>
                                </tbody>
                            </table>
                        </td>
                    </tr>
                }
            }
        </tbody>
    </table>

    Here is the result. 

    Best Regards,

    YihuiSun

    Tuesday, January 12, 2021 9:51 AM
  • User-1355965324 posted

    Hi

    here I have two model class

    EmpClockModel
    (
        Id int,
        EmpCode int,
       ClockedTime DateTime
       ClockedDatewithoutTime DateTime
    )
    
    EmpClockViewModel
    (
        EmpCode int,
       ClockIn DateTime
       ClockOut DateTime
       ClockedDatewithoutTime DateTime
    )
    

    The record  as given below

     EmpClockModel
    1   100,  '2021-01-12 07:26:06.000', '2021-01-12'
    2   100,  '2021-01-12 12:26:06.000', '2021-01-12'
    3   100,  '2021-01-12 17:26:06.000', '2021-01-12'
    
    4   101,  '2021-01-12 07:56:06.000', '2021-01-12'
    5   101,  '2021-01-12 12:26:06.000', '2021-01-12'
    6   101,  '2021-01-12 17:26:06.000', '2021-01-12'
    7   101,  '2021-01-12 17:30:06.000', '2021-01-12'
    
    
    8   102,  '2021-01-12 07:56:06.000' , '2021-01-12'
    

    I want to get Min  and Max  clockeddate  for the employee and for the date

     public EmpClockViewModel GetInOutClockedHrsForDate(string empcode, DateTime clockdate)
     {
    	// How can I get the Min and Max value from EmpClockModel  into the column of  EmpClockViewModel[ClockIn , ClockOut ]  where EmpClockModel.empcode = empcode && EmpClockModel.ClockedDatewithout==clockdate
     }

    Regards

    Pol

    Tuesday, January 12, 2021 12:42 PM
  • User475983607 posted

    The where condition is trivial; where the employee equals the employee parameter and the month, day, and year are equal to the input parameter values.  From there you can simple get the max and min values using the Max() and Min() methods.

    Looking forward to seeing your code... 

    Tuesday, January 12, 2021 1:16 PM
  • User1686398519 posted

    Hi polachan, 

    You have submitted a similar post before, I gave a complete example, you can refer to it.

    According to your requirements, you only need to modify the query and Model.

    Model

        public class EmployeeViewModel
        {
            public string EmpCode { get; set; }
            public string EmpName { get; set; }
            public string Date { get; set; }
            public string Clockin { get; set; }
            public string Clockout { get; set; }
        }

    Controller

                var result = db.Employees.ToList().GroupJoin(
                    db.EmpClockings.ToList(),
                    em => em.EmpCode,
                    ec => ec.EmpCode,
                    (emp, empclock) => new
                    {
                        employee = emp,
                        dateAndTime = empclock.Where(i=>i.ClockDate.Date.ToString().Equals(clockdate.Date.ToString())).Select(m => m.ClockDate).ToList()
                    })
                    .Where(m => m.employee.EmpCode== empcode)
                    .Select(m => new EmployeeViewModel
                    {
                        EmpCode = m.employee.EmpCode,
                        EmpName = m.employee.EmpName,
                        Date = DateTime.Parse(m.dateAndTime.FirstOrDefault().ToString()).ToString("MM/dd/yyyy"),
                        Clockin = DateTime.Parse(m.dateAndTime.Min().ToString()).ToString("HH:mm"),
                        Clockout = m.dateAndTime.Count() <= 1 ? "" : DateTime.Parse(m.dateAndTime.Max().ToString()).ToString("HH:mm")
                    });

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 13, 2021 6:36 AM