Time Off Management System

High-Level Goals

• Combine the separate application instances and databases into one, while still providing “walls” between departments.
• Allow for department-specific configuration, so that each department or office could configure their own benefit types, limitations, and categories for their employees. 
• Upgrade and replace the technology: eliminate Java and Apache Tomcat in the web stack, implement a current PHP version, implement the latest Drupal version.
• Migrate the existing data from many individual SQL Server databases into one MySQL database

Requirements

• Launch on internally-hosted Red Hat 7 servers with current versions of PHP, MySQL, and Drupal
• Fix or eliminate the automation of time off approvals
• Improve and standardize the date range selector
• Give managers full control over time off requests, allowing them to edit any request at any time. 
• Improve/complete the Administrative UI
• Make department-specific configuration more visible to managers, with clear documentation on what options mean; eliminate unnecessary or unused configuration
• Eliminate the ability to create multiple user accounts for the same employee.  One employee = one account.
• Create a department transfer process, so that when an employee moves to a different department, their time off follows them. 
• Provide employees a way to delete their own time off requests, if a request has not yet been approved/denied.
• Display approved time-off to both managers and employees in a flexible calendar format that provides multiple displays and can be printed, if necessary.
• Modernize the look and feel. 
• Accommodate complex Union Contract requirements for bargaining employees, without impacting non-bargaining staff.
• Bootstrap-based theme
• Integration to HR system for regular feed of seniority data
• Easily-visible Logging data
• AD integration

Migration Approach

Migration had to be handled in a 3-step approach, taking data from individual SQL Server databases into one single Drupal instance and MySQL database:

1. Migrate the department-specific configuration
2. Migrate the department’s user accounts
3. Migrate the time off data for those users

As much as possible, the Feeds and Feeds Tamper modules were used to automate this process, but some manual manipulation of the data was necessary due to the drastically-altered data structure between the new and legacy system.  When necessary, MS Excel functions massaged legacy data into the correct format, and reusable Feeds then imported delimited data files to the new application. 

During the migration process, Department “walls” had to be established, so that users and managers only had access to their own data.  This was accomplished by assigning each department a unique ID, and using Views (Contextual Filters) to filter the data, depending on the logged-in user.  Business Rules (Dependent Fields) created the necessary parent-child relationships between departments and department-specific configuration.

Theme

Since all functions of the application are performed by logged in users, an administrative theme, Color Admin, was chosen.  Color Admin is a Bootstrap Dashboard/Administrative theme often used with Angular and Laravel applications.  It was not Drupal-ready, so the standard templates had to be created, and a notable amount of CSS was required for layout corrections.  However, Color Admin offered extensive third-party plugins and UI elements.  It was well-documented, provided a pleasing color scheme, and was easy to use.

Development

The Time Off Management system was built using as many contributed modules as possible, but the central functionality comes from custom modules and entities.  The solution is pure Drupal and does not leverage other technologies for front or back end or interfaces.

Result

The project spanned over 14 months, from research and planning, through development, testing, migration, and training. 

As of April 2019, all departments are using the new Time Off Management system.  The transition was very smooth, with little to no issues following each migration. 

The application is much more portable and easier to administer. 

For the regular user (employee) it features:

• Home page summary of recent activity and the status of each time off request
• Personal calendar and a department calendar showing approved time off
• Form to request time off, and a confirmation screen
• Form to communicate schedule deviations to the manager, such as ‘came in late’

User Home Screen Add Time Off Request Add Schedule Deviation

For the manager:

• Menu of common tasks and links to department-specific configuration
• Configuration screens to
  • Manage items like User Types and Leave Types
  • Create ‘Black Out’ ranges to prevent requests during certain time frames
  • Establish Limits like the number of people who can be off in a given day, or the number of hours per day or week that leave/benefit time can be used.
• User management area to add, edit and maintain accounts for staff
• Department calendar showing approved time off, with filtering options
• Time Off List to view, approve, deny, delete, and search requests
• Time Authorization List to review and disposition schedule deviations
• Alternative Grid Navigation for managers who oversee many different departments
• Customized, searchable logging for various actions within the system

Manager Home Screen Manager Approval Screen Manager Review Schedule Deviation