📋 Customer Complaint Management System

College Project — Full Stack (HTML + Node.js + MySQL)

---

📁 Folder Structure

complaint-system/
│
├── frontend/
│   ├── index.html      ← Main webpage (complaint form)
│   ├── style.css       ← Styling
│   └── script.js       ← Form logic + API calls
│
├── backend/
│   ├── server.js       ← Express server + MySQL connection
│   └── package.json    ← Node.js dependencies
│
└── database/
    └── setup.sql       ← SQL to create DB and table

---

🛠️ Prerequisites (install these first)

Tool	Download Link
Node.js (v18+)	https://nodejs.org
MySQL Server	https://dev.mysql.com/downloads/mysql/
MySQL Workbench (optional)	https://dev.mysql.com/downloads/workbench/

---

🚀 Steps to Run Locally

Step 1 — Set up MySQL Database

Option A: Using MySQL Workbench

1. Open MySQL Workbench
2. Click the + to open a new SQL tab
3. Open and run the file: database/setup.sql
4. You should see the complaint_db database created ✅

Option B: Using Terminal/Command Prompt

mysql -u root -p
# Enter your MySQL password when prompted

# Then paste and run:
source /path/to/complaint-system/database/setup.sql

---

Step 2 — Configure Database Password in Backend

Open backend/server.js and update these lines:

const db = mysql.createConnection({
  host:     'localhost',
  user:     'root',          // ← your MySQL username
  password: 'your_password', // ← your MySQL password (change this!)
  database: 'complaint_db'
});

---

Step 3 — Install Node.js Dependencies

Open a terminal and navigate to the backend folder:

cd complaint-system/backend
npm install

This installs: express, mysql2, cors, nodemon

---

Step 4 — Start the Backend Server

# Inside complaint-system/backend/
node server.js

You should see:

✅  Connected to MySQL database: complaint_db
🚀  Server running at http://localhost:3000

Keep this terminal window open while using the app.

---

Step 5 — Open the Frontend

Simply open frontend/index.html in your browser:

• Double-click the file, OR
• Right-click → Open with → Chrome / Firefox

⚠️ Make sure the backend server is running before submitting a form!

---

✅ Testing the System

1. Open frontend/index.html in browser
2. Fill in the complaint form
3. Click Submit Complaint
4. You should see a green success message
5. Verify in MySQL: SELECT * FROM complaints;

---

🔌 Connecting MySQL to Tableau

1. Open Tableau Desktop
2. Click Connect → To a Server → MySQL
3. Enter:
   • Server: localhost
   • Port: 3306
   • Database: complaint_db
   • Username: root
   • Password: your password
4. Select the complaints table
5. Start building your dashboard! 🎉

---

🌐 API Reference

Method	Endpoint	Description
POST	http://localhost:3000/add-complaint	Submit a new complaint
GET	http://localhost:3000/complaints	View all complaints (bonus)

POST /add-complaint — Request Body

{
  "customer_name":  "Ravi Kumar",
  "complaint_type": "Credit Card",
  "description":    "Unauthorized transaction...",
  "date":           "2025-04-15",
  "status":         "Pending"
}

Response

{
  "message": "Complaint submitted successfully!",
  "id": 6
}

---

❗ Common Issues & Fixes

Problem	Fix
Cannot connect to server	Make sure node server.js is running
MySQL connection failed	Check username/password in server.js
Access denied for user 'root'	Run MySQL as admin or use correct credentials
Form submits but nothing saved	Check browser console (F12) for errors

---

📊 MySQL Queries for Tableau

-- All data
SELECT * FROM complaints;

-- Complaints by month and year (Trend chart)
SELECT YEAR(date) AS Year, MONTH(date) AS Month,
       complaint_type, COUNT(*) AS Total
FROM complaints
GROUP BY Year, Month, complaint_type
ORDER BY Year, Month;

-- Status breakdown
SELECT complaint_type, status, COUNT(*) AS Count
FROM complaints
GROUP BY complaint_type, status;