Login Using Qr Code With Mysql Database in Node js, Ajax, HTML

const express = require('express');
const session = require('express-session');
const bodyParser = require('body-parser');
const app = express();
const axios = require('axios');
const qr = require('qrcode');
const crypto = require('crypto');
const mysql = require('mysql');
const path = require('path');

app.use(express.json());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, 'views'));

// MySQL database connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root@123',
  database: 'abc'
});

db.connect((err) => {
  if (err) {
    throw err;
  }
  console.log('Connected to the database');
});

app.get('/pkLogin', (req, res) => {
  res.render('login');
});

const pkGenerateToken = () => {
  const token = crypto.randomBytes(16).toString('hex');
  console.log("Generated token =", token);
  return token;
};

app.use(
  session({
    secret: 'your_secret_key',
    resave: true,
    saveUninitialized: true
  })
);

let globalUserId = '';

app.get('/', (req, res) => {
  const token = pkGenerateToken();

  const qrCodeOptions = {
    width: 300,
    height: 300
  };

  const qrCodeData = `http://192.168.0.198:7005/pkQR_Login?token=${encodeURIComponent(token)}`;

  qr.toDataURL(qrCodeData, qrCodeOptions, (err, qrCode) => {
    if (err) {
      console.error(err);
      return res.sendStatus(500);
    }

    const sql = 'INSERT INTO tokens SET ?';
    const tokenData = { token: token };

    db.query(sql, tokenData, (err, result) => {
      if (err) {
        console.error(err);
        return res.sendStatus(500);
      }

      res.render('index', { qrCode: qrCode });
    });
  });
});

app.get('/pkQR_Login', (req, res) => {
  const token = req.query.token;
  console.log("Query token =", token);

  const sql = 'SELECT * FROM tokens WHERE token = ?';

  db.query(sql, [token], (err, result) => {
    if (err) {
      console.error(err);
      return res.sendStatus(500);
    }

    if (result && result.length > 0 && result[0].token === token) {
      res.redirect('/pkUsers');
      const deleteSql = 'DELETE FROM tokens WHERE token = ?';
      db.query(deleteSql, [token], (err, deleteResult) => {
        if (err) {
          console.error(err);
        }
        console.log('Token Deleted');
      });
    }
  });
});

app.get('/pkUsers', (req, res) => {
  db.query('SELECT * FROM users', (err, results) => {
    if (err) {
      console.error('Error fetching users:', err);
      res.status(500).send('Internal Server Error');
      return;
    }
    res.render('users', { users: results });
  });
});

app.get('/pkUser/status', async (req, res) => {
  const { userId } = req.query;
  try {
    await axios.get('http://192.168.0.198:7005/pkUsers', { timeout: 1000 });
    db.query(
      'UPDATE users SET online_status = ? WHERE id = ?',
      ['online', userId],
      (err, results) => {
        if (err) {
          console.error('Error updating user status:', err);
          res.status(500).send('Internal Server Error');
          return;
        }
        res.json({ isOnline: true });
      }
    );
  } catch (error) {
    db.query(
      'UPDATE users SET online_status = ? WHERE id = ?',
      ['offline', userId],
      (err, results) => {
        if (err) {
          console.error('Error updating user status:', err);
          res.status(500).send('Internal Server Error');
          return;
        }
        res.json({ isOnline: false });
      }
    );
  }
});

// Blank token
db.query('DELETE FROM tokens WHERE status="1"', (err, results) => {
  console.log('API executed successfully');
});

app.post('/pkLogin_api', (req, res) => {
  const { username, password } = req.body;
  globalUserId = username;
  console.log("Username is", globalUserId);

  const sql = `SELECT username, password FROM users WHERE username = ? AND password = ?`;

  db.query(sql, [username, password], (err, result) => {
    if (err) {
      console.error('Error performing authentication:', err);
      return res.status(500).send('Internal Server Error');
    }

    if (result.length > 0) {
      const updateSql = `UPDATE users SET status = 1 WHERE username = ?`;
      db.query(updateSql, [username], (err) => {
        if (err) {
          console.error('Error updating login status:', err);
          return res.status(500).send('Internal Server Error');
        }
        res.redirect('/pkCall');
      });
    } else {
      const checkSql = `INSERT INTO users (status) value(?)`;
      db.query(checkSql, [0], (err, result) => {
        console.log("Result", result);
        if (err) {
          console.error('Error checking username:', err);
          return res.status(500).send('Internal Server Error');
        }
        if (result.length > 0) {
          return res.status(400).send('User is already logged in');
        } else {
          return res.status(401).send('Invalid username or password');
        }
      });
    }
  });
});

app.get('/pkLogout', (req, res) => {
  const updateSql = 'UPDATE users SET status = 0 WHERE username = ?';
  db.query(updateSql, [globalUserId], (err, result) => {
    console.log("Update logout success", result);
    if (err) {
      console.error('Error updating login status:', err);
      res.status(500).send('Internal Server Error');
      return;
    }
    res.redirect('/pkLogin');
  });
});

app.get('/pkCall', (req, res) => {
  db.query('SELECT * FROM users', (err, results) => {
    if (err) {
      console.error('Error fetching users:', err);
      res.status(500).send('Internal Server Error');
      return;
    }
    res.render('users', { users: results });
  });
});

app.listen(7005, () => {
  console.log('Server started on port 7000');
});

<!DOCTYPE html>

<html lang="en">

<head>

  <meta charset="UTF-8">

  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <title>Login with QR Code</title>

</head>

<body>

  <h1>Login with QR Code</h1>

  <img src="<%= qrCode %>" alt="QR Code">

</body>

</html>

////////////////////// Login ////////////////////////

<!DOCTYPE html>
<html>

<head>
    <title>PK Login</title>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>

<body>
    <h2>PK Login</h2>
    <form id="pkLoginForm">
        <label for="pkUsername">Username:</label>
        <input type="text" id="pkUsername" name="pkUsername">
        <br>
        <label for="pkPassword">Password:</label>
        <input type="password" id="pkPassword" name="pkPassword">
        <br>
        <input type="submit" value="Login">
    </form>

    <script>
        $(document).ready(function () {
            $('#pkLoginForm').submit(function (event) {
                event.preventDefault();
                var username = $('#pkUsername').val();
                var password = $('#pkPassword').val();
                const data = {
                    username: username,
                    password: password
                };
                $.ajax({
                    url: '/pkLogin_api',
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json'
                    },
                    data: JSON.stringify(data),
                    success: function (response) {
                        console.log(response);
                        window.location.href = '/pkCall';
                    },
                    error: function (xhr, status, error) {
                        console.error(xhr.responseText);
                        alert(xhr.responseText); // Display the error message to the user
                    }
                });
            });
        });
    </script>
</body>

</html>

/////////////// user.ejs   /////////////////

<!DOCTYPE html>
<html>

<head>
    <title>PK User List</title>
</head>

<body>
    <h1>PK User List</h1>
    <button onclick="pkLogout()">Logout</button>
    <table>
        <tr>
            <th>ID</th>
            <th>Username</th>
            <th>Status</th>
        </tr>
        <% users.forEach(user => { %>
        <tr>
            <td>
                <%= user.id %>
            </td>
            <td>
                <%= user.username %>
            </td>
            <td id="pkStatus-<%= user.id %>">Loading...</td>
        </tr>
        <% }); %>
    </table>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <script>
        $(document).ready(function () {
            // Function to update user status
            function pkUpdateUserStatus(userId, status) {
                const statusElement = $('#pkStatus-' + userId);
                if (status === 0) {
                    statusElement.text('Offline');
                    statusElement.removeClass('pkOnline');
                    statusElement.addClass('pkOffline');
                } else {
                    $.ajax({
                        url: '/pkUser/status',
                        type: 'GET',
                        data: { userId: userId },
                        success: function (data) {
                            if (data.isOnline) {
                                statusElement.text('Online');
                                statusElement.removeClass('pkOffline');
                                statusElement.addClass('pkOnline');
                            } else {
                                statusElement.text('Offline');
                                statusElement.removeClass('pkOnline');
                                statusElement.addClass('pkOffline');
                            }
                        },
                        error: function (err) {
                            // Handle network error
                            statusElement.text('Offline');
                            statusElement.removeClass('pkOnline');
                            statusElement.addClass('pkOffline');
                            console.error('Error updating user status:', err);
                        }
                    });
                }
            }

            // Initial user status update
            <% users.forEach(user => { %>
            pkUpdateUserStatus(<%= user.id %>, <%= user.status %>);
            <% }); %>

            // Periodically update user statuses every 5 seconds
            setInterval(function () {
                <% users.forEach(user => { %>
                pkUpdateUserStatus(<%= user.id %>, <%= user.status %>);
                <% }); %>
            }, 5000);
        });
    </script>

    <script>
        function pkLogout() {
            window.location.href = "/pkLogout";
        }
    </script>

    <style>
        table {
            border-collapse: collapse;
            width: 100%;
        }

        th,
        td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }

        th {
            background-color: #f2f2f2;
        }

        .pkOnline {
            color: green;
        }

        .pkOffline {
            color: rgb(233, 64, 12);
        }
    </style>
</body>

</html>