Laravel Employee Attendance Sheet using MYSQL Pivoting Function


    Sometime back I have been working on an Employee Time attendance System which is based on Laravel, php and Mysql Database. There was a requirement for a feature to display the time attendance data identical to the following sheet.
Laravel Employee Attendance Sheet using MYSQL Pivoting FunctionFirst of all I would like to highlight Some of the challenges I have to handle in displaying such a report. We know well one of our requirement to display Dates as column out of how the database is designed and Data is stored. So its not something easy SQL to bring the result from the backend MYSQL Database.

This type of rotation of data from columns to rows is known as PIVOTING in terms of SQL.  MySQL does not have a native pivot function but we can use an aggregate function with a CASE expression as an alternative to PIVOTING to get the result.

My first suggestion would be to determine if we have a calendar table or a table that contains all of the dates that I want to display the sheet. If not, then I would suggest creating one similar to the following:

CREATE TABLE `yearly_date_calendar` (
  `calendarDate` date NOT NULL
);

INSERT INTO `yearly_date_calendar` (`calendarDate`) VALUES
('2016-11-01'),
('2016-11-02'),
('2016-11-03'),
('2016-11-04'),
('2016-11-05'),
('2016-11-06'),
('2016-11-07'),
('2016-11-08'),
('2016-11-09'),
('2016-11-10'),
('2016-11-11'),
('2016-11-12'),
('2016-11-13'),
('2016-11-14'),
('2016-11-15'),
('2016-11-16'),
('2016-11-17'),
('2016-11-18'),
('2016-11-19'),
('2016-11-20'),
('2016-11-21'),
('2016-11-22'),
('2016-11-23'),
('2016-11-24'),
('2016-11-25'),
('2016-11-26'),
('2016-11-27'),
('2016-11-28'),
('2016-11-29'),
('2016-11-30');

You may ask me the purpose of the calendar Table while we already have a Employee Attendance table, Looking at the report , the way Its require to display employee / staff attendance data for a whole month, that mean the dates in which there is no record in attendance table should also be highlighted in the report as absence Days.

And the following is the SQL for Employee table and Attendance Table structure. note that the DDL and DML query is only for demonstration purpose.

Employee Table

CREATE TABLE employee(
  `id` int(10),
  `empId` varchar(255),
  `first_name` varchar(100),
  `last_name` varchar(100),
  `middle_name` varchar(100)
);

Sample Employee Data

INSERT INTO employee (`id`,`empId`, `first_name`, `last_name`, `middle_name`) VALUES
(1,'EPD-111Q2', 'Akram', 'Mohamed', 'Wahid');

Attendance Table

CREATE TABLE `employee_attendance` (
  `attendanceId` int(10),
  `empId` varchar(255),
  `inorout` int(11),
  `remarks` varchar(255),
  `attendance_date` date,
  `attendance_time` time,
  `location` varchar(255),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT '0',
  `updated_by` int(11) DEFAULT '0',
  `deleted_by` int(11) DEFAULT '0'
);

Sample Attendance Data

INSERT INTO `employee_attendance` (`attendanceId`, `empId`, `inorout`, `remarks`, `attendance_date`, `attendance_time`, `location`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`) VALUES
(4907, 'EPD-111Q2', 0, NULL, '2014-11-02', '07:56:43', 'BIOMETRIC', '2014-11-06 08:26:47', '2014-11-06 08:26:47', NULL, 0, 0, 0),
(4908, 'EPD-111Q2', 0, NULL, '2014-11-02', '07:56:44', 'BIOMETRIC', '2014-11-06 08:26:48', '2014-11-06 08:26:48', NULL, 0, 0, 0),
(4953, 'EPD-111Q2', 1, NULL, '2014-11-02', '18:25:40', 'BIOMETRIC', '2014-11-06 08:27:24', '2014-11-06 08:27:24', NULL, 0, 0, 0),
(4954, 'EPD-111Q2', 1, NULL, '2014-11-02', '18:25:41', 'BIOMETRIC', '2014-11-06 08:27:25', '2014-11-06 08:27:25', NULL, 0, 0, 0),
(5012, 'EPD-111Q2', 1, NULL, '2014-11-03', '05:55:39', 'BIOMETRIC', '2014-11-06 08:28:10', '2014-11-06 08:28:10', NULL, 0, 0, 0),
(5063, 'EPD-111Q2', 1, NULL, '2014-11-03', '16:07:16', 'BIOMETRIC', '2014-11-06 08:28:49', '2014-11-06 08:28:49', NULL, 0, 0, 0),
(5117, 'EPD-111Q2', 0, NULL, '2014-11-04', '07:30:14', 'BIOMETRIC', '2014-11-06 08:29:30', '2014-11-06 08:29:30', NULL, 0, 0, 0),
(5165, 'EPD-111Q2', 0, NULL, '2014-11-04', '20:42:43', 'BIOMETRIC', '2014-11-06 08:30:07', '2014-11-06 08:30:07', NULL, 0, 0, 0),
(5166, 'EPD-111Q2', 0, NULL, '2014-11-04', '20:42:45', 'BIOMETRIC', '2014-11-06 08:30:08', '2014-11-06 08:30:08', NULL, 0, 0, 0),
(5292, 'EPD-111Q2', 0, NULL, '2014-11-06', '06:02:18', 'BIOMETRIC', '2014-11-06 08:31:51', '2014-11-06 08:31:51', NULL, 0, 0, 0),
(5293, 'EPD-111Q2', 0, NULL, '2014-11-06', '06:02:20', 'BIOMETRIC', '2014-11-06 08:31:52', '2014-11-06 08:31:52', NULL, 0, 0, 0),
(5334, 'EPD-111Q2', 1, NULL, '2014-11-06', '14:51:17', 'BIOMETRIC', '2014-11-07 08:01:59', '2014-11-07 08:01:59', NULL, 0, 0, 0),
(5397, 'EPD-111Q2', 0, NULL, '2014-11-07', '06:00:10', 'BIOMETRIC', '2014-11-07 08:02:49', '2014-11-07 08:02:49', NULL, 0, 0, 0),
(5440, 'EPD-111Q2', 1, NULL, '2014-11-07', '14:47:25', 'BIOMETRIC', '2014-11-13 10:03:56', '2014-11-13 10:03:56', NULL, 0, 0, 0),
(5441, 'EPD-111Q2', 1, NULL, '2014-11-07', '14:47:27', 'BIOMETRIC', '2014-11-13 10:03:57', '2014-11-13 10:03:57', NULL, 0, 0, 0),
(5501, 'EPD-111Q2', 0, NULL, '2014-11-08', '06:00:13', 'BIOMETRIC', '2014-11-13 10:04:43', '2014-11-13 10:04:43', NULL, 0, 0, 0),
(5543, 'EPD-111Q2', 1, NULL, '2014-11-08', '14:51:59', 'BIOMETRIC', '2014-11-13 10:05:17', '2014-11-13 10:05:17', NULL, 0, 0, 0),
(5606, 'EPD-111Q2', 0, NULL, '2014-11-09', '06:02:02', 'BIOMETRIC', '2014-11-13 10:06:05', '2014-11-13 10:06:05', NULL, 0, 0, 0),
(5649, 'EPD-111Q2', 1, NULL, '2014-11-09', '14:45:07', 'BIOMETRIC', '2014-11-13 10:06:39', '2014-11-13 10:06:39', NULL, 0, 0, 0),
(5712, 'EPD-111Q2', 0, NULL, '2014-11-10', '06:01:46', 'BIOMETRIC', '2014-11-13 10:07:27', '2014-11-13 10:07:27', NULL, 0, 0, 0),
(5759, 'EPD-111Q2', 1, NULL, '2014-11-10', '14:52:23', 'BIOMETRIC', '2014-11-13 10:08:04', '2014-11-13 10:08:04', NULL, 0, 0, 0),
(5760, 'EPD-111Q2', 1, NULL, '2014-11-10', '14:52:25', 'BIOMETRIC', '2014-11-13 10:08:04', '2014-11-13 10:08:04', NULL, 0, 0, 0);

Now back to the report which is displayed in the above figure, if you carefully look at it, you will see how its has been highlighted by different colors for various status of how employee report to work. First of all I will define the Color and meaning of them,

Full Green: Employee Present
Full Red: Employee Absence
Half Green and Half Red: Employee Checked into work but Not Checked Out.
Half Red and Half Green: Employee Didn't Check into work, but Checked Out.
Blue: Employee On Leaves.
Violet : Employee On Day Off
Yellow: Employee in 24 Hour schedule work.

I have completed this with a MySQL stored procedure, Combining Dynamic SQL inside prepared statement. here is the complete Stored Procedure. Due to Busy work schedule , I couldn't explain the following Stored procedure step by step, if you have any question please write it bellow in the comment form.

DROP PROCEDURE IF EXISTS generateAttendanceReport;
DELIMITER $$
CREATE PROCEDURE generateAttendanceReport(IN search_startdate date,IN search_enddate date,IN employeeRoleIds text,IN dayOffIds text,IN vacationIds text)
BEGIN

DROP TEMPORARY TABLE IF EXISTS tableEmployeeAttendance;
CREATE TEMPORARY TABLE tableEmployeeAttendance AS
SELECT emp.empId,emp.first_name,x.attendance_date,
CASE
  WHEN x.CheckIN ='0' and x.Checkout = '1' THEN '1'
  WHEN x.CheckIN ='0' and x.Checkout is null then '2'
  WHEN x.CheckIN is null and x.Checkout = '1' then '3'
  ELSE '0'
END AS Status
FROM employee emp INNER JOIN
(
SELECT staff.empId,staff.first_name,att.attendance_date,
   max(case when att.inorout ='0' then att.inorout end) as 'CheckIN',
  max(case when att.inorout ='1' then att.inorout end) as 'Checkout'
FROM employee staff
LEFT JOIN employee_attendance att
ON staff.empId = att.empId
WHERE att.attendance_date >= DATE_FORMAT(search_startdate, '%Y-%m-%d')
AND  att.attendance_date <= DATE_FORMAT(search_enddate, '%Y-%m-%d')
GROUP BY staff.empId,att.attendance_date
) x  ON emp.empId = x.empId
GROUP BY x.empId,x.attendance_date;

/* Start - track the employee who are on full day work and on leaves  */
BEGIN
DEClARE row_empId int;
DEClARE row_empcode varchar(20);
DEClARE row_name varchar(200);
DEClARE v_finished int DEFAULT 0;
DEClARE dayoff_count int DEFAULT 0;
DEClARE leave_count int DEFAULT 0;
DEClARE schedule_fullday int DEFAULT 0;
DEClARE joined_after int DEFAULT 0;
DEClARE left_before int DEFAULT 0;
DEClARE loop_startDate date;
DEClARE loop_endDate date;

DEClARE formatted_startDate date;
DEClARE formatted_endDate date;

SET formatted_startDate = DATE_FORMAT(search_startdate, '%Y-%m-%d');
SET formatted_endDate = DATE_FORMAT(search_enddate, '%Y-%m-%d');

SET loop_startDate = DATE_FORMAT(search_startdate, '%Y-%m-%d');
SET loop_endDate = DATE_FORMAT(search_enddate, '%Y-%m-%d');

    WHILE loop_startDate <= loop_endDate DO
      SET row_empId = null;
      SET row_empcode = null;
      SET row_name = null;
      SET v_finished = 0;
      SET dayoff_count = 0;
      SET leave_count = 0;
      SET schedule_fullday = 0;
      SET joined_after = 0;

      BEGIN
      DEClARE attendance_cursor CURSOR FOR
          SELECT empcursor.id AS staffId,empcursor.empId,empcursor.first_name
          FROM employee empcursor
          WHERE empcursor.hire_date <= formatted_endDate
          AND empcursor.leaving_date >= formatted_startDate
          AND empcursor.deleted_at IS NULL
          AND
          NOT EXISTS
          (
              SELECT NULL
              FROM tableEmployeeAttendance attcursor
              WHERE attcursor.empId = empcursor.empId
              AND attcursor.attendance_date = loop_startDate
          );

          DEClARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
            OPEN attendance_cursor;
              get_attendance : LOOP
                FETCH attendance_cursor INTO row_empId,row_empcode,row_name;
                IF v_finished = 1 THEN  /* if the result is empty */
                LEAVE get_attendance;
                END IF;

                SELECT COUNT(*) INTO leave_count FROM employee_leave WHERE empId = row_empId AND start_date <= loop_startDate AND end_date >= loop_startDate AND deleted_at IS NULL AND FIND_IN_SET(leavetypeId,vacationIds);
                SELECT COUNT(*) INTO dayoff_count FROM employee_leave WHERE empId = row_empId AND start_date <= loop_startDate AND end_date >= loop_startDate AND deleted_at IS NULL AND FIND_IN_SET(leavetypeId,dayOffIds);
                SELECT COUNT(*) INTO schedule_fullday FROM employee_schedule WHERE empId = row_empId AND start_date <= loop_startDate AND end_date >= loop_startDate AND hours = 24 AND deleted_at IS NULL;
                SELECT COUNT(*) INTO joined_after FROM employee WHERE id = row_empId AND hire_date > loop_startDate AND deleted_at IS NULL;
                SELECT COUNT(*) INTO left_before FROM employee WHERE id = row_empId AND leaving_date < loop_startDate AND deleted_at IS NULL;

                IF(leave_count > 0) THEN
                  INSERT INTO tableEmployeeAttendance VALUES (row_empcode,row_name,loop_startDate,4);
                ELSEIF(dayoff_count > 0) THEN
                  INSERT INTO tableEmployeeAttendance VALUES (row_empcode,row_name,loop_startDate,5);
                ELSEIF(schedule_fullday > 0) THEN
                  INSERT INTO tableEmployeeAttendance VALUES (row_empcode,row_name,loop_startDate,6);
                ELSEIF(joined_after > 0) THEN
                  INSERT INTO tableEmployeeAttendance VALUES (row_empcode,row_name,loop_startDate,7);
                ELSEIF(left_before > 0)  THEN
                  INSERT INTO tableEmployeeAttendance VALUES (row_empcode,row_name,loop_startDate,8);
                END IF;

              END LOOP get_attendance;
            CLOSE attendance_cursor;
        END;
      SET loop_startDate = DATE_ADD(loop_startDate, INTERVAL 1 DAY);
    END WHILE;
END;
/* END - track the staff who are on full day work and on leaves  */


SET @@group_concat_max_len = 75000;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN calendaremployee.calendarDate = ''',
      date_format(calendarDate, '%Y-%m-%d'),
      ''' THEN coalesce(att.Status, 0) END) AS `',
      date_format(calendarDate, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM yearly_date_calendar
where calendarDate >= DATE_FORMAT(search_startdate, '%Y-%m-%d')
  and calendarDate <= DATE_FORMAT(search_enddate, '%Y-%m-%d');

SET @query :=
  CONCAT('SELECT calendaremployee.employeeId,calendaremployee.first_name,calendaremployee.last_name,calendaremployee.empId, ', @sql, '
            FROM
            (
              SELECT emp.id AS employeeId,cal.calendarDate,emp.first_name,emp.last_name,emp.empId
              FROM yearly_date_calendar cal
              CROSS JOIN employee emp
              WHERE emp.hire_date <= ''',DATE_FORMAT(search_enddate, '%Y-%m-%d'),''' AND emp.leaving_date >= ''',DATE_FORMAT(search_startdate, '%Y-%m-%d'),''' AND emp.deleted_at IS NULL AND FIND_IN_SET(emp.roleIds, ''',employeeRoleIds,''' )
             ) calendaremployee
            LEFT JOIN tableEmployeeAttendance att
              on calendaremployee.empId = att.empId
              and calendaremployee.calendarDate = att.attendance_date
              WHERE calendaremployee.calendarDate >= ''',DATE_FORMAT(search_startdate, '%Y-%m-%d'), '''
               AND calendaremployee.calendarDate <= ''',DATE_FORMAT(search_enddate, '%Y-%m-%d'),'''
              group by calendaremployee.first_name, calendaremployee.empId
              order by calendaremployee.empId');


PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;
DROP TABLE tableEmployeeAttendance;
SET @@group_concat_max_len = 1024;
END $$
DELIMITER ;

and you can call the Stored procedure in the following method using Laravel Query Builder, which will return a result set in the format of array, which you can loop through and render on a html table design, if you have lot of employees, this query will be very slow, so I would recommend using Laravel crone Job to cache the query result and then you can render it from cache, instead of calling the query every time.

First import the following namespaces on top of the controller file or in the file where you want to call the Stored Procedure.

use Illuminate\Support\Facades\DB;
use Doctrine\DBAL\Driver\PDOConnection;

and following lines of code show how to call the stored procedure, you should bind parameters required by the stored procedure.

$db = DB::connection()->getPdo();
$db->setAttribute(PDOConnection::ATTR_ERRMODE, PDOConnection::ERRMODE_EXCEPTION);
$db->setAttribute(PDOConnection::ATTR_EMULATE_PREPARES, true);

$queryResult = $db->prepare('call generateAttendanceReport(?,?,?,?,?)');
$queryResult->bindParam(1, $start_date);
$queryResult->bindParam(2, $end_date);
$queryResult->bindParam(3, $employeeRoles,PDOConnection::PARAM_STR);
$queryResult->bindParam(4, $leaveTypesDayOffIds,PDOConnection::PARAM_STR);
$queryResult->bindParam(5, $leaveTypesVactionIds,PDOConnection::PARAM_STR);
$queryResult->execute();
$results = $queryResult->fetchAll(PDOConnection::FETCH_ASSOC);
$queryResult->closeCursor();
return $results;
Written by Akram Wahid 9 years ago

are you looking for a chief cook who can well craft laravel and vuejs, to make some awsome butterscotch,
yes then it is right time for you to look at my profile.

Do you want to write Response or Comment?

You must be a member of techalyst to proceed!

Continue with your Email ? Sign up / log in

Responses

Be the first one to write a response :(

{{ item.member.name }} - {{ item.created_at_human_readable }}

{{ reply.member.name }} - {{ reply.created_at_human_readable }}