Project Introduction

The Leave Management System is a web application designed to streamline the process of managing employee leave requests and policies. Built using Node.js, this platform allows employees to submit leave requests, while managers can review and approve or reject these requests. The system supports multiple user roles, including admin, manager, and employee, ensuring a tailored experience for each user type. The underlying MySQL database schema is structured to manage users, leave policies, leave requests, leave balances, leave history, and notifications, providing a robust foundation for effective leave management.

Project Objectives

• To develop a user-friendly interface for employees to submit and track their leave requests.
• To implement a secure user authentication system for managing leave requests and policies.
• To allow managers to review, approve, or reject leave requests efficiently.
• To manage leave policies, including accrual rates and eligibility criteria.
• To maintain accurate leave balances for employees based on their leave policies.
• To provide a history of leave requests and actions taken on them.
• To implement a notification system for important updates regarding leave requests.
• To ensure the application is scalable and maintainable for future enhancements.

Project Modules

1. User Management Module:

   This module handles user registration, authentication, and role management, allowing users to manage their accounts securely.

2. Leave Policy Management Module:

   This module allows administrators to create, update, and manage leave policies, including types of leave and accrual rates.

3. Leave Request Management Module:

   This module facilitates the submission and tracking of leave requests by employees, including reasons and dates.

4. Leave Balance Management Module:

   This module maintains and updates leave balances for employees based on their leave policies and requests.

5. Leave History Management Module:

   This module tracks the history of leave requests and actions taken, providing a record for both employees and managers.

6. Notification Module:

   This module sends notifications to users regarding important updates, such as the status of their leave requests.

Step 1: Set Up the Project

mkdir leave-management-system
cd leave-management-system
npm init -y
npm install express sequelize mysql2 ejs body-parser

Step 2: Configure Sequelize

Create a file named config.js for database configuration.

// config.js
const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('database_name', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql',
});
module.exports = sequelize;

Step 3: Define Models

Create a folder named models and create model files for each table.

models/User.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class User extends Model {}
User .init({
    UserId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    Username: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true,
    },
    PasswordHash: {
        type: DataTypes.STRING(256),
        allowNull: false,
    },
    Email: {
        type: DataTypes.STRING(100),
        allowNull: false,
        unique: true,
    },
    FirstName: {
        type: DataTypes.STRING(50),
        allowNull: false,
    },
    LastName: {
        type: DataTypes.STRING(50),
        allowNull: false,
    },
    RoleId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
}, {
    sequelize,
    modelName: 'User ',
    timestamps: true,
});
module.exports = User;

Repeat similar steps for other models (Roles, LeaveTypes, LeaveRequests, LeaveBalances, Notifications, Reports, CalendarEvents, Feedback, CompanyPolicies).

Step 4: Create Repositories

Create a folder named repositories and create repository files for each model.

repositories/userRepository.js

const User = require('../models/User');
class UserRepository {
    async createUser (data) {
        return await User.create(data);
    }
    async getAllUsers() {
        return await User.findAll();
    }
    async getUser ById(id) {
        return await User.findByPk(id);
    }
    async updateUser (id, data) {
        return await User.update(data, { where: { UserId: id } });
    }
    async deleteUser (id) {
        return await User.destroy({ where: { UserId: id } });
    }
}
module.exports = new UserRepository();

Step 5: Set Up Controllers

Create a folder named controllers and create controller files for each model.

controllers/userController.js

const userRepository = require('../repositories/userRepository');
class UserController {
    async createUser (req, res) {
        const user = await userRepository.createUser (req.body);
        res.redirect('/users');
    }
    async getAllUsers(req, res) {
        const users = await userRepository.getAllUsers();
        res.render('users/index', { users });
    }
    async getUser (req, res) {
        const user = await userRepository.getUser ById(req.params.id);
        res.render('users/edit', { user });
    }
    async updateUser (req, res) {
        await userRepository.updateUser (req.params.id, req.body);
        res.redirect('/users');
    }
    async deleteUser (req, res) {
        await userRepository.deleteUser (req.params.id);
        res.redirect('/users');
    }
}
module.exports = new UserController();

Step 6: Define Routes

Create a folder named routes and create route files for each model.

routes/userRoutes.js

const express = require('express');
const router = express.Router();
const userController = require('../controllers/userController');
router.get('/', userController.getAllUsers);
router.get('/create', (req, res) => res.render('users/create'));
router.post('/', userController.createUser );
router.get('/:id', userController.getUser );
router.post('/:id', userController.updateUser );
router.post('/:id/delete', userController.deleteUser );
module.exports = router;

Step 7: Create Views

Create a folder named views and create subfolders for each model. Use EJS to create views.

views/users/index.ejs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <title>Users</title>
</head>
<body>
    <div class="container">
        <h1>Users</h1>
        <a href="/users/create" class="btn btn-primary">Create User</a>
        <table class="table">
            <thead>
                <tr>
                    < th>UserId</th>
                    <th>Username</th>
                    <th>Email</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <% users.forEach(user => { %>
                    <tr>
                        <td><%= user.UserId %></td>
                        <td><%= user.Username %></td>
                        <td><%= user.Email %></td>
                        <td><%= user.FirstName %></td>
                        <td><%= user.LastName %></td>
                        <td>
                            <a href="/users/<%= user.UserId %>" class="btn btn-warning">Edit</a>
                            <form action="/users/<%= user.UserId %>/delete" method="POST" style="display:inline;">
                                <button type="submit" class="btn btn-danger">Delete</button>
                            </form>
                        </td>
                    </tr>
                <% }) %>
            </tbody>
        </table>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Step 8: Implement Bootstrap 5

Ensure that all views utilize Bootstrap 5 for styling. You can include Bootstrap's CSS and JS files in the head and body sections of your EJS templates as shown in the previous example.

Final Steps

Set Up the Express Server

Create an index.js file to set up the Express server and use the defined routes.

const express = require('express');
const bodyParser = require('body-parser');
const userRoutes = require('./routes/userRoutes');
const sequelize = require('./config');
const app = express();
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: true }));
app.use('/users', userRoutes);
const PORT = process.env.PORT || 3000;
sequelize.sync().then(() => {
    app.listen(PORT, () => {
        console.log(`Server is running on http://localhost:${PORT}`);
    });
});

Run the Application

Start your application by running node index.js in your terminal.

Test CRUD Operations

Open your browser and navigate to http://localhost:3000/users to test the CRUD operations for users.

This structure can be replicated for other models like Roles, LeaveTypes, etc., by following the same pattern for repositories, controllers, routes, and views.

Following are the complete CRUD implementation for the remaining models: Roles, LeaveTypes, LeaveRequests, LeaveBalances, Notifications, Reports, CalendarEvents, Feedback, and CompanyPolicies. This will include models, repositories, controllers, routes, and views for each entity.

Step 1: Define Remaining Models

Create the following model files in the models directory.

models/Role.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class Role extends Model {}
Role.init({
    RoleId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    RoleName: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true,
    },
}, {
    sequelize,
    modelName: 'Role',
    timestamps: true,
});
module.exports = Role;

models/LeaveType.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class LeaveType extends Model {}
LeaveType.init({
    LeaveTypeId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    LeaveTypeName: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true,
    },
    Description: {
        type: DataTypes.STRING(255),
    },
}, {
    sequelize,
    modelName: 'LeaveType',
    timestamps: true,
});
module.exports = LeaveType;

models/LeaveRequest.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class LeaveRequest extends Model {}
LeaveRequest.init({
    LeaveRequestId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    UserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    LeaveTypeId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    StartDate: {
        type: DataTypes.DATE,
        allowNull: false,
    },
    EndDate: {
        type: DataTypes.DATE,
        allowNull: false,
    },
    Status: {
        type: DataTypes.STRING(20),
        allowNull: false,
    },
}, {
    sequelize,
    modelName: 'LeaveRequest',
    timestamps: true,
});
module.exports = LeaveRequest;

models/LeaveBalance.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class LeaveBalance extends Model {}
LeaveBalance.init({
    LeaveBalanceId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    UserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    LeaveTypeId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    TotalLeaves: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    UsedLeaves: {
        type: DataTypes.INTEGER,
        allowNull: false,
        defaultValue: 0,
    },
}, {
    sequelize,
    modelName: 'LeaveBalance',
    timestamps: true,
});
module.exports = LeaveBalance;

models/Notification.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class Notification extends Model {}
Notification.init({
    NotificationId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    UserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    Message: {
        type: DataTypes.STRING(255),
        allowNull: false,
    },
    IsRead: {
        type: DataTypes.BOOLEAN,
        defaultValue: false,
    },
}, {
    sequelize,
    modelName: 'Notification',
    timestamps: true,
});
module.exports = Notification;

models/Report.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class Report extends Model {}
Report.init({
    ReportId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    UserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    ReportDate: {
        type: DataTypes.DATE,
        allowNull: false,
    },
    ReportContent: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
}, {
    sequelize,
    modelName: 'Report',
    timestamps: true,
});
module.exports = Report;

models/CalendarEvent.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class CalendarEvent extends Model {}
CalendarEvent.init({
    CalendarEventId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    UserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    EventTitle: {
        type: DataTypes.STRING(100),
        allowNull: false,
    },
    EventDate: {
        type: DataTypes.DATE,
        allowNull: false,
    },
}, {
    sequelize,
    modelName: 'CalendarEvent',
    timestamps: true,
});
module.exports = CalendarEvent;

models/Feedback.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class Feedback extends Model {}
Feedback.init({
    FeedbackId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    UserId: {
        type: DataTypes.INTEGER,
        allowNull : false,
    },
    FeedbackContent: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
}, {
    sequelize,
    modelName: 'Feedback',
    timestamps: true,
});
module.exports = Feedback;

models/CompanyPolicy.js

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../config');
class CompanyPolicy extends Model {}
CompanyPolicy.init({
    PolicyId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    PolicyTitle: {
        type: DataTypes.STRING(100),
        allowNull: false,
    },
    PolicyContent: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
}, {
    sequelize,
    modelName: 'CompanyPolicy',
    timestamps: true,
});
module.exports = CompanyPolicy;

Step 2: Create Repositories for Remaining Models

Create repository files in the repositories directory.

repositories/roleRepository.js

const Role = require('../models/Role');
class RoleRepository {
    async createRole(data) {
        return await Role.create(data);
    }
    async getAllRoles() {
        return await Role.findAll();
    }
    async getRoleById(id) {
        return await Role.findByPk(id);
    }
    async updateRole(id, data) {
        return await Role.update(data, { where: { RoleId: id } });
    }
    async deleteRole(id) {
        return await Role.destroy({ where: { RoleId: id } });
    }
}
module.exports = new RoleRepository();

repositories/leaveTypeRepository.js

const LeaveType = require('../models/LeaveType');
class LeaveTypeRepository {
    async createLeaveType(data) {
        return await LeaveType.create(data);
    }
    async getAllLeaveTypes() {
        return await LeaveType.findAll();
    }
    async getLeaveTypeById(id) {
        return await LeaveType.findByPk(id);
    }
    async updateLeaveType(id, data) {
        return await LeaveType.update(data, { where: { LeaveTypeId: id } });
    }
    async deleteLeaveType(id) {
        return await LeaveType.destroy({ where: { LeaveTypeId: id } });
    }
}
module.exports = new LeaveTypeRepository();

repositories/leaveRequestRepository.js

const LeaveRequest = require('../models/LeaveRequest');
class LeaveRequestRepository {
    async createLeaveRequest(data) {
        return await LeaveRequest.create(data);
    }
    async getAllLeaveRequests() {
        return await LeaveRequest.findAll();
    }
    async getLeaveRequestById(id) {
        return await LeaveRequest.findByPk(id);
    }
    async updateLeaveRequest(id, data) {
        return await LeaveRequest.update(data, { where: { LeaveRequestId: id } });
    }
    async deleteLeaveRequest(id) {
        return await LeaveRequest.destroy({ where: { LeaveRequestId: id } });
    }
}
module.exports = new LeaveRequestRepository();

repositories/leaveBalanceRepository.js

const LeaveBalance = require('../models/LeaveBalance');
class LeaveBalanceRepository {
    async createLeaveBalance(data) {
        return await LeaveBalance.create(data);
    }
    async getAllLeaveBalances() {
        return await LeaveBalance.findAll();
    }
    async getLeaveBalanceById(id) {
        return await LeaveBalance.findByPk(id);
    }
    async updateLeaveBalance(id, data) {
        return await LeaveBalance.update(data, { where: { LeaveBalanceId: id } });
    }
    async deleteLeaveBalance(id) {
        return await LeaveBalance.destroy({ where: { LeaveBalanceId: id } });
    }
}
module.exports = new LeaveBalanceRepository();

repositories/notificationRepository.js

const Notification = require('../models/Notification');
class NotificationRepository {
    async createNotification(data) {
        return await Notification.create(data);
    }
    async getAllNotifications() {
        return await Notification.findAll();
    }
    async getNotificationById(id) {
        return await Notification.findByPk(id);
    }
    async updateNotification(id, data) {
        return await Notification.update(data, { where: { NotificationId: id } });
    }
    async deleteNotification(id) {
        return await Notification.destroy({ where: { NotificationId: id } });
    }
}
module.exports = new NotificationRepository();

repositories/reportRepository.js

const Report = require('../models/Report');
class ReportRepository {
    async createReport(data) {
        return await Report.create(data);
    }
    async getAllReports() {
        return await Report.findAll();
    }
    async getReportById(id) {
        return await Report.findByPk(id);
    }
    async updateReport(id, data){
        return await Report.update(data, { where: { ReportId: id } });
    }
    async deleteReport(id) {
        return await Report.destroy({ where: { ReportId: id } });
    }
}
module.exports = new ReportRepository();

repositories/calendarEventRepository.js

const CalendarEvent = require('../models/CalendarEvent');
class CalendarEventRepository {
    async createCalendarEvent(data) {
        return await CalendarEvent.create(data);
    }
    async getAllCalendarEvents() {
        return await CalendarEvent.findAll();
    }
    async getCalendarEventById(id) {
        return await CalendarEvent.findByPk(id);
    }
    async updateCalendarEvent(id, data) {
        return await CalendarEvent.update(data, { where: { CalendarEventId: id } });
    }
    async deleteCalendarEvent(id) {
        return await CalendarEvent.destroy({ where: { CalendarEventId: id } });
    }
}
module.exports = new CalendarEventRepository();

repositories/feedbackRepository.js

const Feedback = require('../models/Feedback');
class FeedbackRepository {
    async createFeedback(data) {
        return await Feedback.create(data);
    }
    async getAllFeedbacks() {
        return await Feedback.findAll();
    }
    async getFeedbackById(id) {
        return await Feedback.findByPk(id);
    }
    async updateFeedback(id, data) {
        return await Feedback.update(data, { where: { FeedbackId: id } });
    }
    async deleteFeedback(id) {
        return await Feedback.destroy({ where: { FeedbackId: id } });
    }
}
module.exports = new FeedbackRepository();

repositories/companyPolicyRepository.js

const CompanyPolicy = require('../models/CompanyPolicy');
class CompanyPolicyRepository {
    async createCompanyPolicy(data) {
        return await CompanyPolicy.create(data);
    }
    async getAllCompanyPolicies() {
        return await CompanyPolicy.findAll();
    }
    async getCompanyPolicyById(id) {
        return await CompanyPolicy.findByPk(id);
    }
    async updateCompanyPolicy(id, data) {
        return await CompanyPolicy.update(data, { where: { PolicyId: id } });
    }
    async deleteCompanyPolicy(id) {
        return await CompanyPolicy.destroy({ where: { PolicyId: id } });
    }
}
module.exports = new CompanyPolicyRepository();

Step 3: Set Up Controllers for Remaining Models

Create controller files in the controllers directory.

controllers/roleController.js

const roleRepository = require('../repositories/roleRepository');
class RoleController {
    async createRole(req, res) {
        const role = await roleRepository.createRole(req.body);
        res.redirect('/roles');
    }
    async getAllRoles(req, res) {
        const roles = await roleRepository.getAllRoles();
        res.render('roles/index', { roles });
    }
    async getRole(req, res) {
        const role = await roleRepository.getRoleById(req.params.id);
        res.render('roles/edit', { role });
    }
    async updateRole(req, res) {
        await roleRepository.updateRole(req.params.id, req.body);
        res.redirect('/roles');
    }
    async deleteRole(req, res) {
        await roleRepository.deleteRole(req.params.id);
        res.redirect('/roles');
    }
}
module.exports = new RoleController();

controllers/leaveTypeController.js

const leaveTypeRepository = require('../repositories/leaveTypeRepository');
class LeaveTypeController {
    async createLeaveType(req, res) {
        const leaveType = await leaveTypeRepository.createLeaveType(req.body);
        res.redirect('/leave-types');
    }
    async getAllLeaveTypes(req, res) {
        const leaveTypes = await leaveTypeRepository.getAllLeaveTypes();
        res.render('leaveTypes/index', { leaveTypes });
    }
    async getLeaveType(req, res) {
        const leaveType = await leaveTypeRepository.getLeaveTypeById(req.params.id);
        res.render('leaveTypes/edit', { leaveType });
    }
    async updateLeaveType(req, res) {
        await leaveTypeRepository.updateLeaveType(req.params.id, req.body);
        res.redirect('/leave-types');
    }
    async deleteLeaveType(req, res) {
        await leaveTypeRepository.deleteLeaveType(req.params.id);
        res.redirect('/leave-types');
    }
}
module.exports = new LeaveTypeController();

controllers/leaveRequestController.js

const leaveRequestRepository = require('../repositories/leaveRequestRepository');
class LeaveRequestController {
    async createLeaveRequest(req, res) {
        const leaveRequest = await leaveRequestRepository.createLeaveRequest(req.body);
        res.redirect('/leave-requests');
    }
    async getAllLeaveRequests(req, res) {
        const leaveRequests = await leaveRequestRepository.getAllLeaveRequests();
        res.render ('leaveRequests/index', { leaveRequests });
    }
    async getLeaveRequest(req, res) {
        const leaveRequest = await leaveRequestRepository.getLeaveRequestById(req.params.id);
        res.render('leaveRequests/edit', { leaveRequest });
    }
    async updateLeaveRequest(req, res) {
        await leaveRequestRepository.updateLeaveRequest(req.params.id, req.body);
        res.redirect('/leave-requests');
    }
    async deleteLeaveRequest(req, res) {
        await leaveRequestRepository.deleteLeaveRequest(req.params.id);
        res.redirect('/leave-requests');
    }
}
module.exports = new LeaveRequestController();

controllers/leaveBalanceController.js

const leaveBalanceRepository = require('../repositories/leaveBalanceRepository');
class LeaveBalanceController {
    async createLeaveBalance(req, res) {
        const leaveBalance = await leaveBalanceRepository.createLeaveBalance(req.body);
        res.redirect('/leave-balances');
    }
    async getAllLeaveBalances(req, res) {
        const leaveBalances = await leaveBalanceRepository.getAllLeaveBalances();
        res.render('leaveBalances/index', { leaveBalances });
    }
    async getLeaveBalance(req, res) {
        const leaveBalance = await leaveBalanceRepository.getLeaveBalanceById(req.params.id);
        res.render('leaveBalances/edit', { leaveBalance });
    }
    async updateLeaveBalance(req, res) {
        await leaveBalanceRepository.updateLeaveBalance(req.params.id, req.body);
        res.redirect('/leave-balances');
    }
    async deleteLeaveBalance(req, res) {
        await leaveBalanceRepository.deleteLeaveBalance(req.params.id);
        res.redirect('/leave-balances');
    }
}
module.exports = new LeaveBalanceController();

controllers/notificationController.js

const notificationRepository = require('../repositories/notificationRepository');
class NotificationController {
    async createNotification(req, res) {
        const notification = await notificationRepository.createNotification(req.body);
        res.redirect('/notifications');
    }
    async getAllNotifications(req, res) {
        const notifications = await notificationRepository.getAllNotifications();
        res.render('notifications/index', { notifications });
    }
    async getNotification(req, res) {
        const notification = await notificationRepository.getNotificationById(req.params.id);
        res.render('notifications/edit', { notification });
    }
    async updateNotification(req, res) {
        await notificationRepository.updateNotification(req.params.id, req.body);
        res.redirect('/notifications');
    }
    async deleteNotification(req, res) {
        await notificationRepository.deleteNotification(req.params.id);
        res.redirect('/notifications');
    }
}
module.exports = new NotificationController();

controllers/reportController.js

const reportRepository = require('../repositories/reportRepository');
class ReportController {
    async createReport(req, res) {
        const report = await reportRepository.createReport(req.body);
        res.redirect('/reports');
    }
    async getAllReports(req, res) {
        const reports = await reportRepository.getAllReports();
        res.render('reports/index', { reports });
    }
    async getReport(req, res) {
        const report = await reportRepository.getReportById(req.params.id);
        res.render('reports/edit', { report });
    }
    async updateReport(req, res) {
        await reportRepository.updateReport(req.params.id, req.body);
        res.redirect('/reports');
    }
    async deleteReport(req, res) {
        await reportRepository.deleteReport(req.params.id);
        res.redirect('/reports');
    }
}
module.exports = new ReportController();

controllers/calendarEventController.js

const calendarEventRepository = require('../repositories/calendarEventRepository');
class CalendarEventController {
    async createCalendarEvent(req, res) {
        const calendarEvent = await calendarEventRepository.createCalendarEvent(req.body);
        res.redirect('/calendar-events');
    }
    async getAllCalendarEvents(req, res) {
        const calendarEvents = await calendarEventRepository.getAllCalendarEvents();
        res.render('calendarEvents/index', { calendarEvents });
    }
    async getCalendarEvent(req, res) {
        const calendarEvent = await calendarEventRepository.getCalendarEventById(req.params.id);
        res.render('calendarEvents/edit', { calendarEvent });
    }
    async updateCalendarEvent(req, res) {
        await calendarEventRepository.updateCalendarEvent(req.params.id, req.body);
        res.redirect('/calendar-events');
    }
    async deleteCalendarEvent(req, res) {
        await calendarEventRepository.deleteCalendarEvent(req.params.id);
        res.redirect('/calendar-events');
    }
}
module.exports = new CalendarEventController();

controllers/feedbackController.js

const feedbackRepository = require('../repositories/feedbackRepository');
class FeedbackController {
    async createFeedback(req, res) {
        const feedback = await feedbackRepository.createFeedback(req.body);
        res.redirect('/feedbacks');
    }
    async getAllFeedbacks(req, res) {
        const feedbacks = await feedbackRepository getAllFeedbacks();
        res.render('feedbacks/index', { feedbacks });
    }
    async getFeedback(req, res) {
        const feedback = await feedbackRepository.getFeedbackById(req.params.id);
        res.render('feedbacks/edit', { feedback });
    }
    async updateFeedback(req, res) {
        await feedbackRepository.updateFeedback(req.params.id, req.body);
        res.redirect('/feedbacks');
    }
    async deleteFeedback(req, res) {
        await feedbackRepository.deleteFeedback(req.params.id);
        res.redirect('/feedbacks');
    }
}
module.exports = new FeedbackController();

controllers/companyPolicyController.js

const companyPolicyRepository = require('../repositories/companyPolicyRepository');
class CompanyPolicyController {
    async createCompanyPolicy(req, res) {
        const companyPolicy = await companyPolicyRepository.createCompanyPolicy(req.body);
        res.redirect('/company-policies');
    }
    async getAllCompanyPolicies(req, res) {
        const companyPolicies = await companyPolicyRepository.getAllCompanyPolicies();
        res.render('companyPolicies/index', { companyPolicies });
    }
    async getCompanyPolicy(req, res) {
        const companyPolicy = await companyPolicyRepository.getCompanyPolicyById(req.params.id);
        res.render('companyPolicies/edit', { companyPolicy });
    }
    async updateCompanyPolicy(req, res) {
        await companyPolicyRepository.updateCompanyPolicy(req.params.id, req.body);
        res.redirect('/company-policies');
    }
    async deleteCompanyPolicy(req, res) {
        await companyPolicyRepository.deleteCompanyPolicy(req.params.id);
        res.redirect('/company-policies');
    }
}
module.exports = new CompanyPolicyController();

Step 4: Define Routes for Remaining Models

Create route files in the routes directory.

routes/roleRoutes.js

const express = require('express');
const router = express.Router();
const roleController = require('../controllers/roleController');
router.get('/', roleController.getAllRoles);
router.get('/create', (req, res) => res.render('roles/create'));
router.post('/', roleController.createRole);
router.get('/:id', roleController.getRole);
router.post('/:id', roleController.updateRole);
router.post('/:id/delete', roleController.deleteRole);
module.exports = router;

routes/leaveTypeRoutes.js

const express = require('express');
const router = express.Router();
const leaveTypeController = require('../controllers/leaveTypeController');
router.get('/', leaveTypeController.getAllLeaveTypes);
router.get('/create', (req, res) => res.render('leaveTypes/create'));
router.post('/', leaveTypeController.createLeaveType);
router.get('/:id', leaveTypeController.getLeaveType);
router.post('/:id', leaveTypeController.updateLeaveType);
router.post('/:id/delete', leaveTypeController.deleteLeaveType);
module.exports = router;

routes/leaveRequestRoutes.js

const express = require('express');
const router = express.Router();
const leaveRequestController = require('../controllers/leaveRequestController');
router.get('/', leaveRequestController.getAllLeaveRequests);
router.get('/create', (req, res) => res.render('leaveRequests/create'));
router.post('/', leaveRequestController.createLeaveRequest);
router.get('/:id', leaveRequestController.getLeaveRequest);
router.post('/:id', leaveRequestController.updateLeaveRequest);
router.post('/:id/delete', leaveRequestController.deleteLeaveRequest);
module.exports = router;

routes/leaveBalanceRoutes.js

const express = require('express');
const router = express.Router();
const leaveBalanceController = require('../controllers/leaveBalanceController');
router.get('/', leaveBalanceController.getAllLeaveBalances);
router.get('/create', (req, res) => res.render('leaveBalances/create'));
router.post('/', leaveBalanceController.createLeaveBalance);
router.get('/:id', leaveBalanceController.getLeaveBalance);
router.post('/:id', leaveBalanceController.updateLeaveBalance);
router.post('/:id/delete', leaveBalanceController.deleteLeaveBalance);
module.exports = router;

routes/notificationRoutes.js

const express = require('express');
const router = express.Router();
const notificationController = require('../controllers/notificationController');
router.get('/', notificationController.getAllNotifications);
router.get('/create', (req, res) => res.render('notifications/create'));
router.post('/', notificationController.createNotification);
router.get('/:id', notificationController.getNotification);
router.post('/:id', notificationController.updateNotification);
router.post('/:id/delete', notificationController.deleteNotification);
module.exports = router;

routes/reportRoutes.js

const express = require('express');
const router = express.Router();
const reportController = require('../controllers/reportController');
router.get('/', reportController.getAllReports);
router.get('/create', (req, res) => res.render('reports/create'));
router.post('/', reportController.createReport);
router.get('/:id', reportController.getReport);
router.post('/:id', reportController.updateReport );
router.post('/:id/delete', reportController.deleteReport);
module.exports = router;

routes/calendarEventRoutes.js

const express = require('express');
const router = express.Router();
const calendarEventController = require('../controllers/calendarEventController');
router.get('/', calendarEventController.getAllCalendarEvents);
router.get('/create', (req, res) => res.render('calendarEvents/create'));
router.post('/', calendarEventController.createCalendarEvent);
router.get('/:id', calendarEventController.getCalendarEvent);
router.post('/:id', calendarEventController.updateCalendarEvent);
router.post('/:id/delete', calendarEventController.deleteCalendarEvent);
module.exports = router;

routes/feedbackRoutes.js

const express = require('express');
const router = express.Router();
const feedbackController = require('../controllers/feedbackController');
router.get('/', feedbackController.getAllFeedbacks);
router.get('/create', (req, res) => res.render('feedbacks/create'));
router.post('/', feedbackController.createFeedback);
router.get('/:id', feedbackController.getFeedback);
router.post('/:id', feedbackController.updateFeedback);
router.post('/:id/delete', feedbackController.deleteFeedback);
module.exports = router;

routes/companyPolicyRoutes.js

const express = require('express');
const router = express.Router();
const companyPolicyController = require('../controllers/companyPolicyController');
router.get('/', companyPolicyController.getAllCompanyPolicies);
router.get('/create', (req, res) => res.render('companyPolicies/create'));
router.post('/', companyPolicyController.createCompanyPolicy);
router.get('/:id', companyPolicyController.getCompanyPolicy);
router.post('/:id', companyPolicyController.updateCompanyPolicy);
router.post('/:id/delete', companyPolicyController.deleteCompanyPolicy);
module.exports = router;

Step 5: Create Views for Remaining Models

Create view files in the views directory for each model.

views/roles/index.ejs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <title>Roles</title>
</head>
<body>
    <div class="container">
        <h1>Roles</h1>
        <a href="/roles/create" class="btn btn-primary">Create Role</a>
        <table class="table">
            <thead>
                <tr>
                    <th>RoleId</th>
                    <th>RoleName</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <% roles.forEach(role => { %>
                    <tr>
                        <td><%= role.RoleId %></td>
                        <td><%= role.RoleName %></td>
                        <td>
                            <a href="/roles/<%= role.RoleId %>" class="btn btn-warning">Edit</a>
                            <form action="/roles/<%= role.RoleId %>/delete" method="POST" style="display:inline;">
                                <button type="submit" class="btn btn-danger">Delete</button>
                            </form>
                        </td>
                    </tr>
                <% }) %>
            </tbody>
        </table>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Repeat similar steps for other views (LeaveTypes, LeaveRequests, LeaveBalances, Notifications, Reports, CalendarEvents, Feedback, CompanyPolicies).

Final Steps

Set Up the Express Server

Update your index.js file to include the new routes.

const express = require('express');
const bodyParser = require('body-parser');
const sequelize = require('./config');
const userRoutes = require('./routes/userRoutes');
const roleRoutes = require('./routes/roleRoutes');
const leaveTypeRoutes = require('./routes/leaveTypeRoutes');
const leaveRequestRoutes = require('./routes/leaveRequestRoutes');
const leaveBalanceRoutes = require('./routes/leaveBalanceRoutes');
const notificationRoutes = require('./routes/notificationRoutes');
const reportRoutes = require('./routes/reportRoutes');
const calendarEventRoutes = require('./routes/calendarEventRoutes');
const feedbackRoutes = require('./routes/feedbackRoutes');
const companyPolicyRoutes = require('./routes/companyPolicyRoutes');
const app = express();
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: true }));
app.use('/users', userRoutes);
app.use('/roles', roleRoutes);
app.use('/leave-types', leaveTypeRoutes);
app.use('/leave-requests', leaveRequestRoutes);
app.use('/leave-balances', leaveBalanceRoutes);
app.use('/notifications', notificationRoutes);
app.use('/reports', report Routes);
app.use('/calendar-events', calendarEventRoutes);
app.use('/feedbacks', feedbackRoutes);
app.use('/company-policies', companyPolicyRoutes);
const PORT = process.env.PORT || 3000;
sequelize.sync().then(() => {
    app.listen(PORT, () => {
        console.log(`Server is running on http://localhost:${PORT}`);
    });
});

Run the Application

Start your application by running node index.js in your terminal.

Test CRUD Operations

Open your browser and navigate to the respective routes (e.g., http://localhost:3000/users, http://localhost:3000/roles, etc.) to test the CRUD operations for all models.

This completes the implementation of a complete CRUD application using Node.js, Sequelize, Express, EJS, and Bootstrap 5 for the provided SQL schema. Each model has its own repository, controller, routes, and views, allowing for organized and maintainable code.

To create a dashboard page that consolidates data related to the project, we will follow these steps:

Create a Dashboard Route

Define a route for the dashboard.

Create a Dashboard Controller

Implement a controller to fetch and aggregate data from various models.

Create a Dashboard View

Design a view to display the consolidated data.

Update the Main Application

Integrate the dashboard into the main application.

Step 1: Create a Dashboard Route

Create a new file named dashboardRoutes.js in the routes directory.

routes/dashboardRoutes.js

const express = require('express');
const router = express.Router();
const dashboardController = require('../controllers/dashboardController');
router.get('/', dashboardController.getDashboard);
module.exports = router;

Step 2: Create a Dashboard Controller

Create a new file named dashboardController.js in the controllers directory.

controllers/dashboardController.js

const User = require('../models/User');
const Role = require('../models/Role');
const LeaveType = require('../models/LeaveType');
const LeaveRequest = require('../models/LeaveRequest');
const LeaveBalance = require('../models/LeaveBalance');
const Notification = require('../models/Notification');
const Report = require('../models/Report');
const CalendarEvent = require('../models/CalendarEvent');
const Feedback = require('../models/Feedback');
const CompanyPolicy = require('../models/CompanyPolicy');
class DashboardController {
    async getDashboard(req, res) {
        try {
            const userCount = await User.count();
            const roleCount = await Role.count();
            const leaveTypeCount = await LeaveType.count();
            const leaveRequestCount = await LeaveRequest.count();
            const leaveBalanceCount = await LeaveBalance.count();
            const notificationCount = await Notification.count();
            const reportCount = await Report.count();
            const calendarEventCount = await CalendarEvent.count();
            const feedbackCount = await Feedback.count();
            const companyPolicyCount = await CompanyPolicy.count();
            res.render('dashboard', {
                userCount,
                roleCount,
                leaveTypeCount,
                leaveRequestCount,
                leaveBalanceCount,
                notificationCount,
                reportCount,
                calendarEventCount,
                feedbackCount,
                companyPolicyCount,
            });
        } catch (error) {
            console.error(error);
            res.status(500).send('Internal Server Error');
        }
    }
}
module.exports = new DashboardController();

Step 3: Create a Dashboard View

Create a new file named dashboard.ejs in the views directory.

views/dashboard.ejs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <title>Dashboard</title>
</head>
<body>
    <div class="container">
        <h1 class="mt-4">Dashboard</h1>
        <div class="row mt-4">
            <div class="col-md-3">
                <div class="card text-white bg-primary mb-3">
                    <div class="card-header">Users</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= userCount %></h5>
                        <p class="card-text">Total number of users.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-success mb-3">
                    <div class="card-header">Roles</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= roleCount %></h5>
                        <p class="card-text">Total number of roles.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-info mb-3">
                    <div class="card-header">Leave Types</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= leaveTypeCount %></h5>
                        <p class="card-text">Total number of leave types.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-warning mb-3">
                    <div class="card-header">Leave Requests</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= leaveRequestCount %></h5>
                        <p class="card-text">Total number of leave requests.</p>
                    </div>
                </div>
            </div>
        </div>
        <div class="row mt-4">
            <div class="col-md-3">
                <div class="card text-white bg-danger mb-3">
                    <div class="card-header">Leave Balances</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= leaveBalanceCount %></h5>
                        <p class="card-text">Total leave balances.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-secondary mb-3">
                    <div class="card-header">Notifications</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= notificationCount %></h5>
                        <p class="card-text">Total notifications.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-dark mb-3">
                    <div class="card-header">Reports</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= reportCount %></h5>
                        <p class="card-text">Total reports generated.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-light mb-3">
                    <div class="card-header">Calendar Events</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= calendarEventCount %></h5>
                        <p class="card-text">Total calendar events.</p>
                    </div>
                </div>
            </div>
        </div>
        <div class="row mt-4">
            <div class="col-md-3">
                <div class="card text-white bg-primary mb-3">
                    <div class="card-header">Feedback</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= feedbackCount %></h5>
                        <p class="card-text">Total feedback received.</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-white bg-success mb-3">
                    <div class="card-header">Company Policies</div>
                    <div class="card-body">
                        <h5 class="card-title"><%= companyPolicyCount %></h5>
                        <p class="card-text">Total company policies.</p>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Step 4: Update the Main Application

In your main application file (e.g., index.js), include the dashboard route.

const dashboardRoutes = require('./routes/dashboardRoutes');
app.use('/dashboard', dashboardRoutes);

Final Steps

Run the Application

Start your application by running node index.js in your terminal.

Access the Dashboard

Open your browser and navigate to http://localhost:3000/dashboard to view the consolidated data on the dashboard.

This implementation provides a comprehensive dashboard that displays key metrics related to users, roles, leave types, leave requests, leave balances, notifications, reports, calendar events, feedback, and company policies. Each metric is presented in a visually appealing card format using Bootstrap.