Attendance form

[ChinnuBlr]

Hi,

How to create a Attendance form in windows form.

The form should lookk like this:
---------------------------------------------------------------------
1 2 3 4 5 6 7 ..........till 31 or 30 based on month selected
1. emp1

2. emp2

-----------------------------------------------------------------------

I shud be able to enter attendance for employees.....

 

[demausdauth]

You'll need to be a little more clear about what you want. Perhaps a few things to think about would be : What are you going to do with the collected data (is it going to be stored and where)? How do you want the form to display/input the data? What rules are you using to determine what gets input and how? ie: Do you want to input attendance of employees for a day (23 out of 34 employees for 2009-02-02) or input attendance for an employee based on the date ( employee #2 for 2009-02-02 was here, employee #4 for 2009-02-02 was not here)? Or is it somethign all together different. Got to be a little bit more specific.

 

[ChinnuBlr]

Hi,

I have two dropdowns Department and Month.

When i select a specific Department(HR) and Month(March)....

All the employee in that specific department should display at left side line by line and 31 columns shud be add to enter attendance for each employee....

This form is mainly to enter attendance for each employee based on selected Month....

And finally saved in Attendance table as shown below...

Employeeid Attendance Date Attendancetype

13009 01/03/2009 Present

13009 02/03/2009 Absent

13008 01/03/2009 Present

etc

ect

etc

....


Pls let me know how to do this in windows form....

Thanks in advance

 

[demausdauth]

Sorry it's been so long as I have been pretty busy -- for all I know you have moved on but I do have a solution. This is probably not the best or most optimized.

I used the 2 drop down: cboMonth, cboDepartment
and a datagridview to display the list: dgvAttendance


When the screen loads the month combo is filled and then I call out to a function that returns a datatable of departments

 Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Fill month drop down
        cboMonth.Items.Clear()
        For i As Integer = 1 To 12
            Dim dteDate As New Date(Now.Year, i, 1)
            cboMonth.Items.Add(dteDate.ToString("MMMM"))
        Next

        'Fill Dept drop down

        cboDepartment.DataSource = GetDepartments()
        cboDepartment.DisplayMember = "Department"
        cboDepartment.ValueMember = "Department"
    End Sub

The 2 combo boxes determine when the data grid is filled. After a department and month have been selected then the grid is filled with FillDataGrid()

 Private Sub cboMonth_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboMonth.SelectedIndexChanged

        'when selected check to make sure that department is selected
        If cboDepartment IsNot Nothing AndAlso cboDepartment.SelectedIndex >= 0 Then
            ' item was selected in department
            If cboMonth IsNot Nothing AndAlso cboMonth.SelectedIndex >= 0 Then
                'now fill the table with employees for department and for this month
                FillDataGrid()

            End If
        End If
    End Sub

    Private Sub cboDepartment_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDepartment.SelectedIndexChanged
        'when selected check to make sure that department is selected
        If cboDepartment IsNot Nothing AndAlso cboDepartment.SelectedIndex >= 0 Then
            ' item was selected in department
            If cboMonth IsNot Nothing AndAlso cboMonth.SelectedIndex >= 0 Then
                'now fill the table with employees for department and for this month
                FillDataGrid()

            End If
        End If
      
    End Sub

The FillDataGrid() method does several things:

• Gets a list of employees
• Gets a list of employee attendance dates based on the month selected
• Fills the datagrid with list of days and employee attendance

Now these first 2 items you should be able to replace with a fairly simple SQL statement if your data is stored in a database.

    Private Sub FillDataGrid()

        Dim dtData As New DataTable()


        '#### Columns ####
        'make new columns
        Dim DaysInMonth As Integer = DateTime.DaysInMonth(Now.Year, (cboMonth.SelectedIndex + 1))


        'get employees for department and month
        Dim dtEmployees As DataTable = GetEmployeeTable(cboDepartment.SelectedValue.ToString, R.Next(12, 25))

        'get existing employee attendance for the current month
        Dim dtAttendance As DataTable = GetEmployeeAttendance(dtEmployees, (cboMonth.SelectedIndex + 1))

        'add employeeid column

        dtData.Columns.Add("EmployeeID", Type.GetType("System.Int32"))

        'add day columns to table
        Dim dc As DataColumn
        For ColCnt As Integer = 1 To DaysInMonth
            dc = New DataColumn("Day" & ColCnt.ToString(), Type.GetType("System.Boolean"))
            dc.AllowDBNull = False
            dc.DefaultValue = False
            dtData.Columns.Add(dc)
        Next
        dtData.AcceptChanges()
        '#### Columns ####
        '#### Fill Table Data ####
        Dim drNew As DataRow

        'Adds the employeeIds to the grid
        For Each dr As DataRow In dtEmployees.Rows
            drNew = dtData.NewRow()
            drNew("EmployeeID") = DirectCast(dr("EmployeeID"), Int32)

            Dim drAttend() As DataRow = dtAttendance.Select("EmployeeID = " & DirectCast(dr("EmployeeID"), Int32))

            If drAttend.Length > 0 Then
                'loop and add the employee attendance
                For AttCnt As Integer = 0 To drAttend.Length - 1
                    drNew("Day" & DirectCast(drAttend(AttCnt)("AttendanceDate"), DateTime).Day.ToString()) = True
                Next
            End If
            dtData.Rows.Add(drNew)
        Next

        dtData.AcceptChanges()
        '#### Fill Table Data ####

        '#### Fill Grid ####

        dgvAttendance.DataSource = dtData
        '#### Fill Grid ####


    End Sub

 

[demausdauth]

In the Columns section we are creating a new data table that will have an EmployeeID column, and then this creates columns for each of the days based on the month.... thus if it is Feb then we get 28 days and if its Jan we get 31 days.

 'add day columns to table
        Dim dc As DataColumn
        For ColCnt As Integer = 1 To DaysInMonth
            dc = New DataColumn("Day" & ColCnt.ToString(), Type.GetType("System.Boolean"))
            dc.AllowDBNull = False
            dc.DefaultValue = False
            dtData.Columns.Add(dc)
        Next

In the Fill Table Data section we filling the datatable that we just created Columns section. In this section dtEmployees represents a datatable that lists the employees for a department (2345,3456,4567,etc....) and dtAttendance represents all of the dates for all of the employees for a dept, which closely resemebles the example of the Attendance table you gave.

We loop through each of the employees in dtEmployees. This allows us to create a single row for each employee in dtData (which will be the source for the datagrid) and then we get an array of datarows from dtAttendance so that we have an attendance date. I should not that in my example the only dates that show up are ones that are AttendanceType = Present.

 If drAttend.Length > 0 Then
                'loop and add the employee attendance
                For AttCnt As Integer = 0 To drAttend.Length - 1
                    drNew("Day" & DirectCast(drAttend(AttCnt)("AttendanceDate"), DateTime).Day.ToString()) = True
                Next
            End If

This is the loop through the attendance dates of an employee. Here we are extracting the Day value from the stored Date and using that to find the correct Day## column in dtData to assign the True value to.

After dtData has been filled then set the datagridview source to dtData and it shows us the employeeids down the left side and gives us checkboxes to show Present (checked) and Absent (unchecked).

 

[ChinnuBlr]

Hi,

Really thanks a lot for the help....

But i have a prblm in filldataGrid().

what is this in filldatagrid()


----------------------------------------

Dim dtEmployees As DataTable = GetEmployeeTable(cboDepartment.SelectedValue.ToString, R.Next(12, 25))

'get existing employee attendance for the current month
Dim dtAttendance As DataTable = GetEmployeeAttendance(dtEmployees, (cboMonth.SelectedIndex + 1))


----------------------------------------
GetEmployeeTable and GetEmployeeAttendance giving error....

Can u pls help me in this as well....

Thanks in advance

 

[demausdauth]

I did explain what these were but again.....

dtEmployees represents a listing of the employees for a department as such
(23456, 23478, 23499, etc....)

I did not create a database, xml file, etc... to store the employee information. So, I created a function [GetEmployeeTable()] that returns a random list of numbers to represent a list of employees it passes the selected value from the department drop down and for my case it passes a random number, you would not use the random number part.

Instead you might have a function more like this one:

 Private Function GetEmployeeAttendance(ByVal PassDepartment As String, ByVal PassMonth As Integer, ByVal PassYear As Integer) As DataTable

        'get data from data base concerning the currently selected month
        Dim dtData As New DataTable

        Dim SqlSelect As String = "SELECT E.EmployeeID, A.AttendanceDate, A.AttendanceType FROM EmployeeTable AS E LEFT JOIN Attendance AS A ON E.EmployeeID = A.EmployeeID WHERE E.EmployeeDepartment='" & PassDepartment & "' AND DATEPART(month,A.AtendanceDate) = " & PassMonth & " AND DATEPART(year, A.AttendanceDate) = " & PassYear

        Using sqlCon As New SqlClient.SqlConnection("YourConnectionString")
            If sqlCon.State <> ConnectionState.Open Then sqlCon.Open()

            Using sqlAdapter As New SqlClient.SqlDataAdapter(SqlSelect, sqlCon)

                sqlAdapter.Fill(dtData)
            End Using 'end dataadapter
        End Using 'end sqlcon

        Return dtData

    End Function

This assumes that you are using a SQL database to store information. The select statement selects from an Employee table and joins that against the Attendance table -- this is done to get currently marked employees for this month. The SQL dataadapter fills the DataTable object and then we return that datatable to FillDataGrid(). This function would replace the 2 functions that you had questions on.

 

[demausdauth]

So I have updated the FillDataGrid() to use the latest GetEmployeeAttendance()

 Private Sub FillDataGrid(ByVal PassTest As String)

        Dim dtData As New DataTable()


        '#### Columns ####
        'make new columns
        Dim DaysInMonth As Integer = DateTime.DaysInMonth(Now.Year, (cboMonth.SelectedIndex + 1))

        Dim dtAttendance As DataTable = GetEmployeeAttendance(cboDepartment.SelectedValue.ToString(), (cboMonth.SelectedIndex + 1), Now.Year)

        Dim dv As New DataView(dtAttendance)

        Dim dtEmployees As DataTable = dv.ToTable(True, New String() {"EmployeeID"})

        'add employeeid column
        dtData.Columns.Add("EmployeeID", Type.GetType("System.Int32"))

        'add day columns to table
        Dim dc As DataColumn
        For ColCnt As Integer = 1 To DaysInMonth
            dc = New DataColumn("Day" & ColCnt.ToString(), Type.GetType("System.Boolean"))
            dc.AllowDBNull = False
            dc.DefaultValue = False
            dtData.Columns.Add(dc)
        Next
        dtData.AcceptChanges()

        '#### Columns ####

        '#### Fill Table Data ####

        Dim drNew As DataRow

        'Adds the employeeIds to the grid
        For Each dr As DataRow In dtEmployees.Rows
            drNew = dtData.NewRow()
            drNew("EmployeeID") = DirectCast(dr("EmployeeID"), Int32)

            Dim drAttend() As DataRow = dtAttendance.Select("EmployeeID = " & DirectCast(dr("EmployeeID"), Int32))

            If drAttend.Length > 0 Then
                'loop and add the employee attendance
                For AttCnt As Integer = 0 To drAttend.Length - 1
                    drNew("Day" & DirectCast(drAttend(AttCnt)("AttendanceDate"), DateTime).Day.ToString()) = True
                Next
            End If
            dtData.Rows.Add(drNew)
        Next

        dtData.AcceptChanges()
        '#### Fill Table Data ####

        '#### Fill Grid ####

        dgvAttendance.DataSource = dtData



        '#### Fill Grid ####


    End Sub

I'll go through what I have changed and why.

   Dim dtAttendance As DataTable = GetEmployeeAttendance(cboDepartment.SelectedValue.ToString(), (cboMonth.SelectedIndex + 1), Now.Year)

This is the new GetEmployeeAttendance function. It returns a datatable with schema of EmployeeID (int), AttendanceDate (datetime), AttendanceType (bool). This is great but it shows multiple rows for each employee:

EmployeeID AttendanceDate AttendanceType
34567 02/14/2009 true
34567 02/17/2009 false
34567 02/25/2009 true
98760 02/04/2009 false
98760 02/27/2009 true

   Dim dv As New DataView(dtAttendance)
        '                                     Distinct, Names Column(s) to be include in the new table and also which columns to be distinct on
        Dim dtEmployees As DataTable = dv.ToTable(True, New String() {"EmployeeID"})

So we want a list of just each distinct employeeId so I chose to utilize the .ToTable method of the DataView object. We create the dataview object utilizing dtAttendance as the source, then we use the .ToTable method specifying the parameters to be distinct on the EmployeeID column and to only return the EmployeeId column.