CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL UNIQUE, `password` varchar(255) NOT NULL, `role` enum('admin','user') DEFAULT 'user', `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `description` text, `color` varchar(50) DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `letters` ( `id` int(11) NOT NULL AUTO_INCREMENT, `reference_no` varchar(100) NOT NULL, `subject` varchar(255) NOT NULL, `date_received` date NOT NULL, `type` enum('incoming','outgoing','internal') NOT NULL, `category_id` int(11) DEFAULT NULL, `priority` enum('low','medium','high','urgent') DEFAULT 'medium', `status` enum('pending','processed','archived') DEFAULT 'pending', `from_person` varchar(255) DEFAULT NULL, `to_person` varchar(255) DEFAULT NULL, `description` text, `created_by` int(11) NOT NULL, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`), FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `notes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `letter_id` int(11) NOT NULL, `content` text NOT NULL, `created_by` int(11) NOT NULL, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), FOREIGN KEY (`letter_id`) REFERENCES `letters` (`id`), FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; $_GET['type'] ?? '', 'status' => $_GET['status'] ?? 'pending', 'category' => $_GET['category'] ?? '' ]; $letters = getLetters($conn, $filters); ?> Dashboard | LetterTrack Pro

Letters Dashboard

Ref No.	Subject	Type	Category	Status	Date	Actions
						View

Made with DeepSite - 🧬 Remix

0) { $sql .= " WHERE " . implode(' AND ', $where); } $sql .= " ORDER BY date_received DESC"; $stmt = $conn->prepare($sql); $stmt->execute($params); return $stmt->fetchAll(); } function addLetter($conn, $data) { $sql = "INSERT INTO letters (reference_no, subject, date_received, type, category_id, priority, status, from_person, to_person, created_by, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $stmt = $conn->prepare($sql); return $stmt->execute([ $data['reference_no'], $data['subject'], $data['date_received'], $data['type'], $data['category_id'], $data['priority'], 'pending', $data['from_person'], $data['to_person'], $_SESSION['user_id'], $data['description'] ]); } ?> prepare("SELECT * FROM users WHERE email = ?"); $stmt->execute([$email]); $user = $stmt->fetch(); if ($user && password_verify($password, $user['password'])) { $_SESSION['user_id'] = $user['id']; $_SESSION['user_name'] = $user['name']; $_SESSION['user_role'] = $user['role']; header("Location: dashboard.php"); exit(); } else { $error = "Invalid email or password"; } } ?> Login | LetterTrack Pro setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { die("Connection failed: " . $e->getMessage()); } ?> DB_HOST=localhost DB_USER=root DB_PASSWORD= DB_NAME=letter_management JWT_SECRET=your_jwt_secret_key_here PORT=5000 const jwt = require('jsonwebtoken'); const User = require('../models/user.model'); const auth = async (req, res, next) => { try { const token = req.header('Authorization').replace('Bearer ', ''); const decoded = jwt.verify(token, process.env.JWT_SECRET); // Check token in database const [rows] = await connection.promise().query( 'SELECT * FROM users WHERE id = ? AND token = ?', [decoded.id, token] ); if (rows.length === 0) { throw new Error(); } req.token = token; req.user = rows[0]; next(); } catch (err) { res.status(401).send({ error: 'Please authenticate.' }); } }; module.exports = auth; const express = require('express'); const router = express.Router(); const User = require('../models/user.model'); const auth = require('../middleware/auth'); // User signup router.post('/signup', async (req, res) => { try { const userId = await User.create(req.body); const token = jwt.sign({ id: userId }, process.env.JWT_SECRET); // Update user with token await connection.promise().query( 'UPDATE users SET token = ? WHERE id = ?', [token, userId] ); const user = await User.findById(userId); res.status(201).send({ user, token }); } catch (err) { res.status(400).send(err.message); } }); // User login router.post('/login', async (req, res) => { try { const { email, password } = req.body; const user = await User.findOne({ email }); if (!user) { return res.status(401).send('Invalid login credentials'); } const isMatch = await user.comparePassword(password); if (!isMatch) { return res.status(401).send('Invalid login credentials'); } const token = await user.generateAuthToken(); res.send({ user, token }); } catch (err) { res.status(400).send(err.message); } }); // Get current user router.get('/me', auth, async (req, res) => { res.send(req.user); }); // Update user profile router.patch('/me', auth, async (req, res) => { try { const user = req.user; const updates = Object.keys(req.body); updates.forEach(update => user[update] = req.body[update]); await user.save(); res.send(user); } catch (err) { res.status(400).send(err.message); } }); module.exports = router; const express = require('express'); const router = express.Router(); const Letter = require('../models/letter.model'); const auth = require('../middleware/auth'); // Create a new letter router.post('/', auth, async (req, res) => { try { const letter = new Letter({ ...req.body, createdBy: req.user._id }); await letter.save(); res.status(201).send(letter); } catch (err) { res.status(400).send(err.message); } }); // Get all letters with filters router.get('/', auth, async (req, res) => { try { const { type, status, category } = req.query; const filter = {}; if (type) filter.type = type; if (status) filter.status = status; if (category) filter.category = category; const letters = await Letter.find(filter) .populate('category', 'name color') .populate('createdBy', 'name avatar') .sort({ createdAt: -1 }); res.send(letters); } catch (err) { res.status(500).send(err.message); } }); // Get letter by ID router.get('/:id', auth, async (req, res) => { try { const letter = await Letter.findById(req.params.id) .populate('category') .populate('createdBy', 'name avatar') .populate('assignedTo', 'name avatar'); if (!letter) { return res.status(404).send('Letter not found'); } res.send(letter); } catch (err) { res.status(500).send(err.message); } }); // Update letter router.patch('/:id', auth, async (req, res) => { const updates = Object.keys(req.body); try { const letter = await Letter.findById(req.params.id); if (!letter) { return res.status(404).send('Letter not found'); } updates.forEach(update => letter[update] = req.body[update]); await letter.save(); res.send(letter); } catch (err) { res.status(400).send(err.message); } }); // Add note to letter router.post('/:id/notes', auth, async (req, res) => { try { const letter = await Letter.findById(req.params.id); if (!letter) { return res.status(404).send('Letter not found'); } letter.notes.push({ content: req.body.content, createdBy: req.user._id }); await letter.save(); res.send(letter); } catch (err) { res.status(400).send(err.message); } }); module.exports = router; const mongoose = require('mongoose'); const categorySchema = new mongoose.Schema({ name: { type: String, required: true, unique: true }, description: String, color: String, // For UI display purposes createdBy: { type: mongoose.Schema.Types.ObjectId, ref: 'User' } }, { timestamps: true }); module.exports = mongoose.model('Category', categorySchema); const bcrypt = require('bcryptjs'); const connection = require('../db'); // Assuming you have a db.js with MySQL connection class User { static async create(userData) { const hashedPassword = await bcrypt.hash(userData.password, 10); const [result] = await connection.promise().query( 'INSERT INTO users SET ?', {...userData, password: hashedPassword} ); return result.insertId; } static async findByEmail(email) { const [rows] = await connection.promise().query( 'SELECT * FROM users WHERE email = ?', [email] ); return rows[0]; } static async comparePassword(candidatePassword, hashedPassword) { return await bcrypt.compare(candidatePassword, hashedPassword); } } module.exports = User; const mongoose = require('mongoose'); const letterSchema = new mongoose.Schema({ referenceNumber: { type: String, required: true, unique: true }, type: { type: String, enum: ['incoming', 'outgoing', 'internal'], required: true }, subject: { type: String, required: true }, date: { type: Date, required: true }, description: String, priority: { type: String, enum: ['low', 'medium', 'high', 'urgent'], default: 'medium' }, status: { type: String, enum: ['pending', 'processed', 'archived'], default: 'pending' }, category: { type: mongoose.Schema.Types.ObjectId, ref: 'Category', required: true }, from: String, to: String, createdBy: { type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true }, assignedTo: { type: mongoose.Schema.Types.ObjectId, ref: 'User' }, attachments: [{ fileName: String, filePath: String, fileType: String, fileSize: Number }], notes: [{ content: String, createdBy: { type: mongoose.Schema.Types.ObjectId, ref: 'User' }, createdAt: { type: Date, default: Date.now } }] }, { timestamps: true }); module.exports = mongoose.model('Letter', letterSchema); const express = require('express'); const mongoose = require('mongoose'); const cors = require('cors'); const dotenv = require('dotenv'); // Load environment variables dotenv.config(); // Create Express app const app = express(); // Middleware app.use(cors()); app.use(express.json()); // Database connection const mysql = require('mysql2'); const connection = mysql.createConnection({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME }); connection.connect(err => { if (err) { console.error('MySQL connection error:', err); return; } console.log('Connected to MySQL database'); }); // Routes const letterRoutes = require('./routes/letters'); const userRoutes = require('./routes/users'); const categoryRoutes = require('./routes/categories'); app.use('/api/letters', letterRoutes); app.use('/api/users', userRoutes); app.use('/api/categories', categoryRoutes); // Error handling middleware app.use((err, req, res, next) => { console.error(err.stack); res.status(500).send('Something broke!'); }); // Start server const PORT = process.env.PORT || 5000; app.listen(PORT, () => { console.log(`Server running on port ${PORT}`); }); LetterTrack Pro - Letter Management System

LetterTrack Pro

Tools

Settings

Sarah Johnson

Administrator

LetterTrack Pro

Tools

Settings

Sarah Johnson

Administrator

Dashboard

Incoming Today

12% increase from yesterday

Outgoing Today

5% decrease from yesterday

Pending Approval

3 awaiting your review

Total Letters

1,248

22% increase from last month

Recent Letters

Board Meeting Minutes - June 2023

From: Executive Committee • Category: Minutes

High 10:30 AM

Annual Budget Approval

To: Finance Department • Category: Financial

Medium Yesterday

Project Proposal: New Website Design

From: Marketing Team • Category: Project

Approved Jun 12

Contract Renewal: Office Lease

From: Landlord • Category: Legal

Urgent Jun 10

Employee Policy Updates

To: All Staff • Category: HR

Info Jun 8

Quick Actions

Letter Status

Total Letters

1,248

Processed

756 (60.5%)

Pending

264 (21.2%)

Archived

512 (41.0%)

Letters Dashboard

Login to LetterTrack Pro

Tools

Settings

Tools

Settings

Dashboard

Recent Letters

Quick Actions

Letter Status

Create New Letter

Success