| -- 1. Create Agency Table
|
| CREATE TABLE Agency (
|
| agency_id INT AUTO_INCREMENT PRIMARY KEY,
|
| agency_name VARCHAR(100) NOT NULL,
|
| city VARCHAR(100)
|
| );
|
|
|
| -- 2. Create Development Table
|
| CREATE TABLE Development (
|
| dev_id INT AUTO_INCREMENT PRIMARY KEY,
|
| agency_id INT,
|
| dev_name VARCHAR(100) NOT NULL,
|
| location VARCHAR(100),
|
| year_opened INT,
|
| height_stories INT,
|
| FOREIGN KEY (agency_id) REFERENCES Agency(agency_id) ON DELETE CASCADE
|
| );
|
|
|
| -- 3. Create Household Table
|
| CREATE TABLE Household (
|
| household_id INT AUTO_INCREMENT PRIMARY KEY,
|
| head_member_id INT NULL -- Will be linked after member insertion
|
| );
|
|
|
| -- 4. Create HousingUnit Table
|
| CREATE TABLE HousingUnit (
|
| unit_id INT AUTO_INCREMENT PRIMARY KEY,
|
| dev_id INT,
|
| household_id INT NULL,
|
| unit_number VARCHAR(20) NOT NULL,
|
| num_bedrooms INT,
|
| num_bathrooms INT,
|
| has_kitchen ENUM('Y', 'N'),
|
| has_living_room ENUM('Y', 'N'),
|
| sq_footage INT,
|
| FOREIGN KEY (dev_id) REFERENCES Development(dev_id) ON DELETE CASCADE,
|
| FOREIGN KEY (household_id) REFERENCES Household(household_id) ON DELETE SET NULL
|
| );
|
|
|
| -- 5. Create Members Table
|
| CREATE TABLE Members (
|
| member_id INT AUTO_INCREMENT PRIMARY KEY,
|
| household_id INT,
|
| member_name VARCHAR(100) NOT NULL,
|
| dob DATE,
|
| sex ENUM('M', 'F', 'O'),
|
| is_head ENUM('Y', 'N'),
|
| FOREIGN KEY (household_id) REFERENCES Household(household_id) ON DELETE CASCADE
|
| );
|
|
|
| -- 6. Create UnitTransferHistory Table
|
| CREATE TABLE UnitTransferHistory (
|
| transfer_id INT AUTO_INCREMENT PRIMARY KEY,
|
| household_id INT,
|
| from_unit_id INT,
|
| to_unit_id INT,
|
| transfer_date DATE NOT NULL,
|
| FOREIGN KEY (household_id) REFERENCES Household(household_id) ON DELETE CASCADE,
|
| FOREIGN KEY (from_unit_id) REFERENCES HousingUnit(unit_id) ON DELETE CASCADE,
|
| FOREIGN KEY (to_unit_id) REFERENCES HousingUnit(unit_id) ON DELETE CASCADE
|
| );
|
|
|
| -- 7. Insert Dummy Seed Data
|
| INSERT INTO Agency (agency_name, city) VALUES ('Kolkata Housing Board', 'Kolkata');
|
|
|
| INSERT INTO Development (agency_id, dev_name, location, year_opened, height_stories)
|
| VALUES (1, 'Greenwood Acres', 'North District', 2010, 5),
|
| (1, 'Riverside Towers', 'South Waterfront', 2018, 12);
|
|
|
| INSERT INTO Household (head_member_id) VALUES (NULL), (NULL);
|
|
|
| INSERT INTO HousingUnit (dev_id, household_id, unit_number, num_bedrooms, num_bathrooms, has_kitchen, has_living_room, sq_footage)
|
| VALUES (1, 1, 'A-101', 2, 1, 'Y', 'Y', 750),
|
| (1, NULL, 'B-202', 3, 2, 'Y', 'Y', 1100),
|
| (2, 2, 'T1-404', 1, 1, 'Y', 'N', 500),
|
| (2, NULL, 'T2-501', 2, 2, 'Y', 'Y', 900);
|
|
|
| INSERT INTO Members (household_id, member_name, dob, sex, is_head)
|
| VALUES (1, 'Amit Mukherjee', '1984-04-12', 'M', 'Y'),
|
| (2, 'Priya Sharma', '1991-08-23', 'F', 'Y');
|
|
|
| -- Complete the circular reference loop for Heads
|
| UPDATE Household SET head_member_id = 1 WHERE household_id = 1;
|
| UPDATE Household SET head_member_id = 2 WHERE household_id = 2;
|
| const express = require('express');
|
| const mysql = require('mysql2/promise');
|
| const cors = require('cors');
|
|
|
| const app = express();
|
| app.use(cors());
|
| app.use(express.json());
|
|
|
| // XAMPP Default MySQL Connection Config
|
| const dbConfig = {
|
| host: 'localhost',
|
| user: 'root',
|
| password: '',
|
| database: 'housing_agency'
|
| };
|
|
|
| // 1. Dropdown Lookup: Get all developments
|
| app.get('/api/developments', async (req, res) => {
|
| try {
|
| const connection = await mysql.createConnection(dbConfig);
|
| const [rows] = await connection.execute('SELECT dev_id, dev_name FROM Development ORDER BY dev_name');
|
| await connection.end();
|
| res.json(rows);
|
| } catch (err) {
|
| res.status(500).send(err.message);
|
| }
|
| });
|
|
|
| // 2. Dropdown Lookup: Get all housing units
|
| app.get('/api/units', async (req, res) => {
|
| try {
|
| const connection = await mysql.createConnection(dbConfig);
|
| const [rows] = await connection.execute('SELECT unit_id, dev_id, unit_number FROM HousingUnit');
|
| await connection.end();
|
| res.json(rows);
|
| } catch (err) {
|
| res.status(500).send(err.message);
|
| }
|
| });
|
|
|
| // 3. Dropdown Lookup: Get all household head members
|
| app.get('/api/heads', async (req, res) => {
|
| try {
|
| const connection = await mysql.createConnection(dbConfig);
|
| const [rows] = await connection.execute("SELECT h.household_id, m.member_name FROM Household h JOIN Members m ON h.head_member_id = m.member_id WHERE m.is_head = 'Y' ORDER BY m.member_name");
|
| await connection.end();
|
| res.json(rows);
|
| } catch (err) {
|
| res.status(500).send(err.message);
|
| }
|
| });
|
|
|
| // 4. POST Endpoint to log a fresh unit transfer tracking entry
|
| app.post('/api/transfer', async (req, res) => {
|
| const { household_id, from_unit_id, to_unit_id, transfer_date } = req.body;
|
| try {
|
| const connection = await mysql.createConnection(dbConfig);
|
|
|
| // Transaction to add history log and update current active unit assignment
|
| await connection.beginTransaction();
|
|
|
| const insertLogSql = `INSERT INTO UnitTransferHistory (household_id, from_unit_id, to_unit_id, transfer_date) VALUES (?, ?, ?, ?)`;
|
| await connection.execute(insertLogSql, [household_id, from_unit_id, to_unit_id, transfer_date]);
|
|
|
| const clearOldUnitSql = `UPDATE HousingUnit SET household_id = NULL WHERE unit_id = ?`;
|
| await connection.execute(clearOldUnitSql, [from_unit_id]);
|
|
|
| const assignNewUnitSql = `UPDATE HousingUnit SET household_id = ? WHERE unit_id = ?`;
|
| await connection.execute(assignNewUnitSql, [household_id, to_unit_id]);
|
|
|
| await connection.commit();
|
| await connection.end();
|
| res.json({ success: true });
|
| } catch (err) {
|
| res.status(500).send(err.message);
|
| }
|
| });
|
|
|
| // 5. GET Endpoint to fetch a date-wise report of head of household movements
|
| app.get('/api/report', async (req, res) => {
|
| try {
|
| const connection = await mysql.createConnection(dbConfig);
|
| const sql = `
|
| SELECT m.member_name, u1.unit_number AS from_unit, u2.unit_number AS to_unit, t.transfer_date
|
| FROM UnitTransferHistory t
|
| JOIN Household h ON t.household_id = h.household_id
|
| JOIN Members m ON h.head_member_id = m.member_id
|
| JOIN HousingUnit u1 ON t.from_unit_id = u1.unit_id
|
| JOIN HousingUnit u2 ON t.to_unit_id = u2.unit_id
|
| ORDER BY t.transfer_date ASC
|
| `;
|
| const [rows] = await connection.execute(sql);
|
| await connection.end();
|
| res.json(rows);
|
| } catch (err) {
|
| res.status(500).send(err.message);
|
| }
|
| });
|
|
|
| app.listen(3000, () => console.log('XAMPP Backend microservice running on port 3000'));
|
| <!DOCTYPE html>
|
| <html lang="en">
|
| <head>
|
| <meta charset="UTF-8">
|
| <meta name="viewport" content="width=device-width, initial-scale=1.0">
|
| <title>Public Housing Management Portal (XAMPP)</title>
|
| <style>
|
| :root {
|
| --primary: #0f766e;
|
| --primary-hover: #115e59;
|
| --bg: #f4fbfb;
|
| --card-bg: #ffffff;
|
| --text: #334155;
|
| --border: #cbd5e1;
|
| }
|
| body {
|
| font-family: system-ui, -apple-system, sans-serif;
|
| background-color: var(--bg);
|
| color: var(--text);
|
| max-width: 850px;
|
| margin: 40px auto;
|
| padding: 0 20px;
|
| }
|
| .card {
|
| background: var(--card-bg);
|
| padding: 30px;
|
| border-radius: 12px;
|
| box-shadow: 0 4px 6px -1px rgb(0 0 0 / 0.05);
|
| margin-bottom: 30px;
|
| }
|
| h2, h3 {
|
| margin-top: 0;
|
| color: #111827;
|
| border-bottom: 2px solid #ccfbf1;
|
| padding-bottom: 10px;
|
| }
|
| .form-row {
|
| display: flex;
|
| gap: 20px;
|
| margin-bottom: 15px;
|
| }
|
| .form-group {
|
| flex: 1;
|
| margin-bottom: 15px;
|
| }
|
| label {
|
| display: block;
|
| margin-bottom: 6px;
|
| font-weight: 600;
|
| font-size: 0.9rem;
|
| }
|
| select, input, button {
|
| width: 100%;
|
| padding: 10px 14px;
|
| border-radius: 6px;
|
| border: 1px solid var(--border);
|
| font-size: 1rem;
|
| box-sizing: border-box;
|
| }
|
| button {
|
| background-color: var(--primary);
|
| color: white;
|
| border: none;
|
| font-weight: 600;
|
| cursor: pointer;
|
| margin-top: 10px;
|
| }
|
| button:hover { background-color: var(--primary-hover); }
|
| .alert {
|
| background-color: #ccfbf1;
|
| color: #115e59;
|
| padding: 12px;
|
| border-radius: 6px;
|
| font-weight: 600;
|
| margin-bottom: 20px;
|
| text-align: center;
|
| }
|
| table {
|
| width: 100%;
|
| border-collapse: collapse;
|
| margin-top: 15px;
|
| }
|
| th, td {
|
| padding: 12px;
|
| text-align: left;
|
| border-bottom: 1px solid #e2e8f0;
|
| }
|
| th { background-color: #f1f5f9; font-weight: 600; }
|
| </style>
|
| </head>
|
| <body>
|
|
|
| <div class="card">
|
| <h2>Household Unit Transfer Form</h2>
|
| <div id="successMessage" class="alert" style="display:none;">
|
| Transfer records successfully logged in XAMPP MySQL database!
|
| </div>
|
|
|
| <form id="transferForm">
|
| <div class="form-row">
|
| <!-- Step 1: Select Development -->
|
| <div class="form-group">
|
| <label for="development">1. Select Housing Development:</label>
|
| <select id="development" required>
|
| <option value="">Loading Developments...</option>
|
| </select>
|
| </div>
|
| <!-- Step 2: Select Head of Household -->
|
| <div class="form-group">
|
| <label for="headMember">2. Head of Household Involved:</label>
|
| <select id="headMember" required disabled>
|
| <option value="">-- Select Development First --</option>
|
| </select>
|
| </div>
|
| </div>
|
|
|
| <div class="form-row">
|
| <!-- Step 3: Select From Unit -->
|
| <div class="form-group">
|
| <label for="fromUnit">3. From Unit:</label>
|
| <select id="fromUnit" required disabled>
|
| <option value="">-- Select Household First --</option>
|
| </select>
|
| </div>
|
| <!-- Step 4: Select To Unit -->
|
| <div class="form-group">
|
| <label for="toUnit">4. To Unit:</label>
|
| <select id="toUnit" required disabled>
|
| <option value="">-- Select Household First --</option>
|
| </select>
|
| </div>
|
| </div>
|
|
|
| <div class="form-row">
|
| <!-- Step 5: Transfer Date -->
|
| <div class="form-group">
|
| <label for="transferDate">5. Transfer Effective Date:</label>
|
| <input type="date" id="transferDate" required disabled>
|
| </div>
|
| </div>
|
|
|
| <button type="submit">Execute Unit Transfer Log</button>
|
| </form>
|
| </div>
|
|
|
| <div class="card">
|
| <h3>Household Relocation & Transfer Report</h3>
|
| <table id="reportTable">
|
| <thead>
|
| <tr>
|
| <th>Head of Household</th>
|
| <th>From Unit</th>
|
| <th>To Unit</th>
|
| <th>Transfer Date</th>
|
| </tr>
|
| </thead>
|
| <tbody></tbody>
|
| </table>
|
| </div>
|
|
|
| <script>
|
| const API_URL = 'http://localhost:3000/api';
|
| document.getElementById('transferDate').valueAsDate = new Date();
|
|
|
| const devEl = document.getElementById('development');
|
| const fromUnitEl = document.getElementById('fromUnit');
|
| const toUnitEl = document.getElementById('toUnit');
|
| const headEl = document.getElementById('headMember');
|
| const dateEl = document.getElementById('transferDate');
|
|
|
| let cachedUnits = [];
|
|
|
| async function initLookupData() {
|
| try {
|
| const resDevs = await fetch(`${API_URL}/developments`);
|
| const developments = await resDevs.json();
|
| devEl.innerHTML = '<option value="">-- Select Development --</option>';
|
| developments.forEach(row => {
|
| devEl.innerHTML += `<option value="${row.dev_id}">${row.dev_name}</option>`;
|
| });
|
|
|
| const resUnits = await fetch(`${API_URL}/units`);
|
| cachedUnits = await resUnits.json();
|
| } catch (err) {
|
| console.error("Initialization failure:", err);
|
| }
|
| }
|
|
|
| devEl.addEventListener('change', async function() {
|
| const devId = parseInt(this.value);
|
| if(devId) {
|
| try {
|
| const resHeads = await fetch(`${API_URL}/heads`);
|
| const heads = await resHeads.json();
|
| headEl.innerHTML = '<option value="">-- Choose Head of Household --</option>';
|
| heads.forEach(row => {
|
| headEl.innerHTML += `<option value="${row.household_id}">${row.member_name}</option>`;
|
| });
|
| headEl.disabled = false;
|
| } catch (err) { console.error(err); }
|
| } else {
|
| headEl.disabled = true;
|
| fromUnitEl.disabled = true;
|
| toUnitEl.disabled = true;
|
| dateEl.disabled = true;
|
| }
|
| });
|
|
|
| headEl.addEventListener('change', function() {
|
| const devId = parseInt(devEl.value);
|
| if(this.value && devId) {
|
| const optionsStr = cachedUnits
|
| .filter(row => row.dev_id === devId)
|
| .map(row => `<option value="${row.unit_id}">${row.unit_number}</option>`)
|
| .join('');
|
|
|
| fromUnitEl.innerHTML = '<option value="">-- Select Current Unit --</option>' + optionsStr;
|
| toUnitEl.innerHTML = '<option value="">-- Select New Unit --</option>' + optionsStr;
|
|
|
| fromUnitEl.disabled = false;
|
| toUnitEl.disabled = false;
|
| dateEl.disabled = false;
|
| } else {
|
| fromUnitEl.disabled = true;
|
| toUnitEl.disabled = true;
|
| dateEl.disabled = true;
|
| }
|
| });
|
|
|
| document.getElementById('transferForm').addEventListener('submit', async (e) => {
|
| e.preventDefault();
|
| if(fromUnitEl.value === toUnitEl.value) {
|
| return alert("Source and destination units cannot be identical.");
|
| }
|
|
|
| const payload = {
|
| household_id: headEl.value,
|
| from_unit_id: fromUnitEl.value,
|
| to_unit_id: toUnitEl.value,
|
| transfer_date: dateEl.value
|
| };
|
|
|
| const res = await fetch(`${API_URL}/transfer`, {
|
| method: 'POST',
|
| headers: { 'Content-Type': 'application/json' },
|
| body: JSON.stringify(payload)
|
| });
|
|
|
| if (res.ok) {
|
| document.getElementById('transferForm').reset();
|
| fromUnitEl.disabled = true;
|
| toUnitEl.disabled = true;
|
| headEl.disabled = true;
|
| dateEl.disabled = true;
|
| dateEl.valueAsDate = new Date();
|
|
|
| const msg = document.getElementById('successMessage');
|
| msg.style.display = 'block';
|
| setTimeout(() => msg.style.display = 'none', 3000);
|
|
|
| loadReportTable();
|
| } else {
|
| alert('Database transaction rejected.');
|
| }
|
| });
|
|
|
| async function loadReportTable() {
|
| try {
|
| const res = await fetch(`${API_URL}/report`);
|
| const rows = await res.json();
|
| const tbody = document.querySelector('#reportTable tbody');
|
| tbody.innerHTML = '';
|
|
|
| rows.forEach(row => {
|
| // Formatting clean JavaScript ISO dates properly
|
| const cleanDate = new Date(row.transfer_date).toISOString().split('T')[0];
|
| tbody.innerHTML += `
|
| <tr>
|
| <td><strong>${row.member_name}</strong></td>
|
| <td>${row.from_unit}</td>
|
| <td>${row.to_unit}</td>
|
| <td>${cleanDate}</td>
|
| </tr>
|
| `;
|
| });
|
| } catch (err) {
|
| console.error("Failed to load historical transfer metrics:", err);
|
| }
|
| }
|
|
|
| initLookupData();
|
| loadReportTable();
|
| </script>
|
| </body>
|
| </html>
|