QR Code Reading Application using SQL Server and Web-Based Application

Problem

Effective employee attendance management is vital for operational efficiency and security in today's dynamic workplace. This final installment of our series focuses on expanding our web application, built initially to track employee movements through QR Code generation and data storage in SQL Server (QR Code Generator in Web-Based App from data stored in SQL Server).

This article will address the need for additional functionality, including displaying employee attendance records, individual employee profiles, and consolidated attendance data.

Solution

Our previous articles laid the groundwork for our attendance tracking system. To address the challenges at hand, we have:

Developed a QR Code Generator integrated into a web-based app for employee check-in/check-out
Expanded the web application to facilitate the scanning of QR codes to record arrivals and departures
Prepared the database to support further development, particularly the creation of comprehensive reports on employee movements within and outside the office

We will utilize the existing framework from the previous articles to expand our web application. Here's what we plan to achieve:

List of All Employees' Attendance Records: We will provide a comprehensive list of all employees who have clocked in and out at the office, along with their associated QR codes.
Individual Employee Attendance Records: Each employee will have access to their attendance records.
Consolidated Attendance Records: We will introduce a consolidated view of an employee's attendance data, offering a holistic perspective of their office activity.

Database Preparation

To get started, we need to ensure our database is appropriately configured. As outlined in our previous articles, the necessary SQL scripts include:

Remove Old HRDatabase: [SQL Script Name: 01 - Remove Old HRDatabase.sql]
Create New HRDatabase: [SQL Script Name: 02 - Create New HRDatabase.sql]
Create Tables: [SQL Script Name: 03 - Create Tables.sql]
Insert Dummy Data: [SQL Script Name: 04 - Insert dummy Data.sql]
Create Stored Procedures: [SQL Script Name: 05 - Create Stored Procedures.sql]
Insert Dummy Data for the Attendance Table: [SQL Script Name: 06 - Insert dummy Data for Attendance.sql]

This article will work with the final SQL script to prepare our database for the upcoming web application expansion in this script: Stored Procedures that get used in the web application to show the data: [08 – Create Final Stored Procedures.sql]

Stored Procedures for Enhanced Functionality

We have developed a set of essential stored procedures to empower our web application.

Stored Procedure for Listing Employee Attendance

-- Stored Procedure to list the Attendance of Employees
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeesAttendance
AS
BEGIN
 
   SELECT  e.id
         , e.EmployeeName
         , e.ContactNo
         , e.Email
         , a.ArrDepWork
         , a.CreatedDate
   FROM  dbo.Employees e
   JOIN  dbo.Attendance a on a.EmpID = e.id
 
END
GO;

Stored Procedure for Listing Employees

-- Stored Procedure to list the Employees
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployees
AS
BEGIN
 
   SELECT e.ID, e.EmployeeName
   FROM dbo.Employees e
   ORDER BY e.ID
 
END
GO;

Stored Procedure for Listing Individual Employee Records

-- Stored Procedure to list per Employee
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeAttendance
   @EmpID   INT
AS
BEGIN
 
   SELECT  e.id
         , e.EmployeeName
         , e.ContactNo
         , e.Email
         , a.ArrDepWork
         , a.CreatedDate
   FROM  dbo.Employees e
   JOIN  dbo.Attendance a on a.EmpID = e.id
   WHERE e.id = @EmpID
   ORDER BY a.id
 
END
GO;

Stored Procedure for Extracting a Single Employee's Records:

-- Stored Procedure to extract a single Employee's records
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeAttendance1
   @EmpID   int
AS
BEGIN
DECLARE @TableVariable TABLE (
    EmpID INT,
    EmployeeName NVARCHAR(255),
    AttendanceDate DATE,
    Arrive DATETIME,
    Depart DATETIME,
    TotalTime INT
)
   ;WITH PairedRecords AS (
      SELECT
         a1.EmpID,
         e.EmployeeName,
         CONVERT(DATE, a1.CreatedDate) AS AttendanceDate,
         a1.ArrDepWork,
         a1.CreatedDate
      FROM
         dbo.Attendance a1
      JOIN dbo.Employees e ON e.id = a1.EmpID
      WHERE 
         a1.ArrDepWork = 'Arrive'
   )
   INSERT INTO @TableVariable (EmpID, EmployeeName, AttendanceDate, Arrive, Depart, TotalTime)
   SELECT EmpID, EmployeeName, AttendanceDate, CreatedDate [Arrive],
      (SELECT top 1 a2.CreatedDate [Depart]
      FROM dbo.Attendance a2 
      WHERE a2.CreatedDate > PairedRecords.CreatedDate 
      AND a2.EmpID = PairedRecords.EmpID
      AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate) [Depart]
      , 
      DATEDIFF(MINUTE, CreatedDate, (
         SELECT top 1 a2.CreatedDate [Depart]
         FROM dbo.Attendance a2 
         WHERE a2.CreatedDate > PairedRecords.CreatedDate 
         AND a2.EmpID = PairedRecords.EmpID
         AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate
      )) TotalTime
   FROM PairedRecords 
   WHERE EmpID = @EmpID
   ORDER BY 1,4;
 
   SELECT * FROM @TableVariable;
 
END
GO;

Stored Procedure for Extracting Consolidated Records

-- Stored Procedure to extract a single Employee's consolidated records
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeAttendance2
   @EmpID    int
AS
BEGIN
DECLARE @TableVariable TABLE (
    EmpID INT,
    EmployeeName NVARCHAR(255),
    AttendanceDate DATE,
    Arrive DATETIME,
    Depart DATETIME,
    TotalTime INT
)
   ;WITH PairedRecords AS (
      SELECT
         a1.EmpID,
         e.EmployeeName,
         CONVERT(DATE, a1.CreatedDate) AS AttendanceDate,
         a1.ArrDepWork,
         a1.CreatedDate
      FROM
         dbo.Attendance a1
      JOIN dbo.Employees e ON e.id = a1.EmpID
      WHERE 
         a1.ArrDepWork = 'Arrive'
   )
   INSERT INTO @TableVariable (EmpID, EmployeeName, AttendanceDate, Arrive, Depart, TotalTime)
   SELECT EmpID, EmployeeName, AttendanceDate, CreatedDate [Arrive],
      (SELECT top 1 a2.CreatedDate [Depart]
      FROM dbo.Attendance a2 
      WHERE a2.CreatedDate > PairedRecords.CreatedDate 
      AND a2.EmpID = PairedRecords.EmpID
      AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate) [Depart]
      , 
      DATEDIFF(MINUTE, CreatedDate, (
         SELECT top 1 a2.CreatedDate [Depart]
         FROM dbo.Attendance a2 
         WHERE a2.CreatedDate > PairedRecords.CreatedDate 
         AND a2.EmpID = PairedRecords.EmpID
         AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate
      )) TotalTime
   FROM PairedRecords 
   WHERE EmpID = @EmpID
   ORDER BY 1,4;
 
   ;WITH CombinedRecords AS (
      SELECT
         EmpID,
         EmployeeName,
         AttendanceDate,
         MIN(Arrive) AS Arrive,
         MAX(Depart) AS Depart,
         CONVERT(VARCHAR(5), DATEADD(MINUTE, SUM(TotalTime), 0), 108) AS TotalTime
      FROM
         @TableVariable
   WHERE EmpID = @EmpID
      GROUP BY
         EmpID,
         EmployeeName,
         AttendanceDate
   )
   SELECT
      EmpID,
      EmployeeName,
      AttendanceDate,
      Arrive,
      Depart,
      TotalTime
   FROM
      CombinedRecords;
 
END
GO;

Integrating Stored Procedures in the Web Application

Next, let's incorporate these stored procedures into our web application. Here's a step-by-step guide:

Step 1

Open your web solution in Visual Studio.

Step 2

Create four new pages, naming them according to their intended functionality (e.g., Attendance1 to 4).

Step 3

Add a dropdown menu next to the Employees menu item to facilitate easy employee selection.

Web Application Pages

Attendance1.aspx: This page will utilize the first stored procedure to display a comprehensive list of all employees' attendance records, including their QR codes.

Attendance2.aspx: Similar to the previous page, this page will allow the selection of a specific employee to view their attendance records.

Attendance3.aspx: On this page, select an individual to review their arrival and departure times and total time spent in the office. Multiple records per day will be shown for employees with multiple arrivals and departures as you can see on 2023-10-02.

Attendance4.aspx: This page will display the same information as in the previous one, but it will consolidate data to provide a summary of the employee's time spent in the office for each day.

You have the option to download the finalized Visual Studio solution. Download the solution in a zip format here: [HRSolution-CheckScan.zip]

Next Steps

Following the completion of this series, you are now equipped to create a database to store essential company and employee information, scan employee ID cards, record data in the database, and generate basic reports on employee office movements.
This serves as a foundational stepping stone for more advanced applications and reporting capabilities.
We trust that you have found this series informative and practical. Stay tuned for forthcoming series that promises to be equally engaging and insightful.

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-01-30