Bdtjtk

I'm fetching rows from excel sheet in my application that holds attendance records from the bio metric machine. In order to get the best result i have to remove the redundant data. For that I have to manage check in and checkout timings at regular intervals. For instance, First check in time for entering, and then checkout time for lunch, then again check in for returning back, and last check out for going home. Meanwhile the rows in excel contains multiple check ins and check outs as the employee tends to do more that once for both.

I have managed to get records from excel and added to data table. Now for the sequence and sorting part I'm struggling to achieve my desired result. Below is my code.

  protected void btnSaveAttendance_Click(object sender, EventArgs e)

    {

        try

        {

            if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == ".xls")

            {

                using (var excel = new OfficeOpenXml.ExcelPackage(FileUpload1.PostedFile.InputStream))

                {

                    var tbl = new DataTable();

                    var ws = excel.Workbook.Worksheets.First();

                    var hasHeader = true;  // adjust accordingly

                                           // add DataColumns to DataTable

                    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])

                        tbl.Columns.Add(hasHeader ? firstRowCell.Text

                            : String.Format("Column {0}", firstRowCell.Start.Column));



                    // add DataRows to DataTable

                    int startRow = hasHeader ? 2 : 1;

                    for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)

                    {

                        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];

                        DataRow row = tbl.NewRow();

                        foreach (var cell in wsRow)

                            row[cell.Start.Column - 1] = cell.Text;

                        tbl.Rows.Add(row);

                    }



                    var distinctNames = (from row in tbl.AsEnumerable()

                                         select row.Field<string>("Employee Code")).Distinct();



                    DataRow dataRows = tbl.Select().OrderBy(u => u["Employee Code"]).ToArray();



                    var ss = dataRows.Where(p => p.Field<string>("Employee Code") == "55").ToArray();





                }

            }

        }

        catch (Exception ex) { }

    }

The result i'm getting is:

Employee Code   Employee Name      Date Time       In / Out

 55                   Alex         12/27/2018 8:59    IN

 55                   Alex         12/27/2018 8:59    IN

 55                   Alex         12/27/2018 13:00   OUT

 55                   Alex         12/27/2018 13:00   OUT

 55                   Alex         12/27/2018 13:48   IN

 55                   Alex         12/27/2018 13:49   IN

 55                   Alex         12/27/2018 18:08   OUT

And I want to have first In and then out and then in and then out. This would iterate four times to generate the result.

Expected result is:

Employee Code   Employee Name       Date Time      In / Out

 55                   Alex         12/27/2018 8:59    IN

 55                   Alex         12/27/2018 13:00   OUT

 55                   Alex         12/27/2018 13:48   IN

 55                   Alex         12/27/2018 18:08   OUT

Can you try to do groupby in the result like below

ss=ss.GroupBy(x=>x.DateTime).ToArray();

Build a logic, if your result have 2 successive In/Out as a sample like below.
Here In I considered as field name

var tt;

for(int i=0;i<ss.Count();i++)

{

if(ss[i].In=="In" && (tt!=null || tt.LastOrDefault().In!="In"))

tt=ss[i];

else if(ss[i].In=="Out" && (tt!=null || tt.LastOrDefault().In!="Out"))

tt=ss[i];



}