🏨 Advanced Hotel Management System

\n \n

Rooms Management

\n \n \n \n \n \n \n \n \n \n \n \n \n {rooms.map(room => (\n \n \n \n \n \n \n \n ))}\n \n

Room ID	Type	Rate	Max Occupancy	Status
{room.room_id}	{room.room_type}	${room.room_rate}	{room.max_occupancy}	{room.status}

\n\n

Guest Registration

\n \n \n \n \n \n \n \n \n \n \n \n {guests.map(guest => (\n \n \n \n \n \n \n ))}\n \n

Guest ID	Name	Email	Phone
{guest.guest_id}	{guest.first_name} {guest.last_name}	{guest.email}	{guest.phone}

\n\n

Room Booking

\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n {bookings.map((booking, index) => (\n \n \n \n \n \n \n \n \n \n ))}\n \n

Booking ID	Guest	Room	Check-in	Check-out	Total Guests	Total Price
{booking.booking_id}	{booking.guest_id}	{booking.room_id}	{booking.check_in_date}	{booking.check_out_date}	{booking.total_guests}	${booking.total_price}

\n View Source\n

\n );\n}\n\nfunction client() {\n createRoot(document.getElementById(\"root\")).render();\n}\nif (typeof document !== \"undefined\") { client(); }\n\nexport default async function server(request: Request): Promise {\n const { sqlite } = await import(\"https://esm.town/v/stevekrouse/sqlite\");\n const KEY = \"hotelManagementSystem\";\n const SCHEMA_VERSION = 4;\n\n // Create rooms table with more detailed schema\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_rooms_${SCHEMA_VERSION} (\n room_id TEXT PRIMARY KEY,\n room_type TEXT NOT NULL,\n room_rate REAL NOT NULL,\n max_occupancy INTEGER NOT NULL,\n status TEXT DEFAULT 'available',\n CONSTRAINT valid_room_type CHECK (room_type IN ('single', 'double', 'suite', 'family', 'deluxe')),\n CONSTRAINT valid_status CHECK (status IN ('available', 'booked', 'maintenance'))\n )\n `);\n\n // Create guests table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_guests_${SCHEMA_VERSION} (\n guest_id TEXT PRIMARY KEY,\n first_name TEXT NOT NULL,\n last_name TEXT NOT NULL,\n email TEXT UNIQUE NOT NULL,\n phone TEXT,\n CONSTRAINT valid_email CHECK (email LIKE '%_@__%.__%')\n )\n `);\n\n // Create bookings table with complex relationships\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_bookings_${SCHEMA_VERSION} (\n booking_id TEXT PRIMARY KEY,\n guest_id TEXT NOT NULL,\n room_id TEXT NOT NULL,\n check_in_date DATE NOT NULL,\n check_out_date DATE NOT NULL,\n total_guests INTEGER NOT NULL,\n total_price REAL NOT NULL,\n booking_date DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(guest_id) REFERENCES ${KEY}_guests_${SCHEMA_VERSION}(guest_id),\n FOREIGN KEY(room_id) REFERENCES ${KEY}_rooms_${SCHEMA_VERSION}(room_id),\n CONSTRAINT valid_dates CHECK (check_out_date > check_in_date),\n CONSTRAINT valid_guests CHECK (total_guests > 0)\n )\n `);\n\n // Handle different routes\n const url = new URL(request.url);\n \n // GET rooms with complex query\n if (url.pathname === '/rooms' && request.method === 'GET') {\n const rooms = await sqlite.execute(`\n SELECT \n room_id, \n room_type, \n room_rate, \n max_occupancy, \n status,\n (SELECT COUNT(*) FROM ${KEY}_bookings_${SCHEMA_VERSION} b \n WHERE b.room_id = r.room_id AND b.check_out_date > date('now')) as current_bookings\n FROM ${KEY}_rooms_${SCHEMA_VERSION} r\n ORDER BY room_type, room_rate\n `);\n return new Response(JSON.stringify(rooms.rows), {\n headers: { 'Content-Type': 'application/json' }\n });\n }\n\n // POST rooms with validation\n if (url.pathname === '/rooms' && request.method === 'POST') {\n const room = await request.json();\n await sqlite.execute(`\n INSERT OR REPLACE INTO ${KEY}_rooms_${SCHEMA_VERSION} \n (room_id, room_type, room_rate, max_occupancy, status) \n VALUES (?, ?, ?, ?, ?)\n `, [\n room.room_id, \n room.room_type, \n room.room_rate, \n room.max_occupancy, \n room.status\n ]);\n return new Response('Room added', { status: 200 });\n }\n\n // GET guests\n if (url.pathname === '/guests' && request.method === 'GET') {\n const guests = await sqlite.execute(`\n SELECT \n guest_id, \n first_name, \n last_name, \n email, \n phone,\n (SELECT COUNT(*) FROM ${KEY}_bookings_${SCHEMA_VERSION} b \n WHERE b.guest_id = g.guest_id) as total_bookings\n FROM ${KEY}_guests_${SCHEMA_VERSION} g\n ORDER BY total_bookings DESC\n `);\n return new Response(JSON.stringify(guests.rows), {\n headers: { 'Content-Type': 'application/json' }\n });\n }\n\n // POST guests\n if (url.pathname === '/guests' && request.method === 'POST') {\n const guest = await request.json();\n await sqlite.execute(`\n INSERT OR REPLACE INTO ${KEY}_guests_${SCHEMA_VERSION} \n (guest_id, first_name, last_name, email, phone) \n VALUES (?, ?, ?, ?, ?)\n `, [\n guest.guest_id, \n guest.first_name, \n guest.last_name, \n guest.email, \n guest.phone\n ]);\n return new Response('Guest registered', { status: 200 });\n }\n\n // GET bookings with join\n if (url.pathname === '/bookings' && request.method === 'GET') {\n const bookings = await sqlite.execute(`\n SELECT \n b.booking_id, \n b.guest_id, \n g.first_name || ' ' || g.last_name as guest_name,\n b.room_id, \n r.room_type,\n b.check_in_date, \n b.check_out_date,\n b.total_guests,\n b.total_price\n FROM ${KEY}_bookings_${SCHEMA_VERSION} b\n JOIN ${KEY}_guests_${SCHEMA_VERSION} g ON b.guest_id = g.guest_id\n JOIN ${KEY}_rooms_${SCHEMA_VERSION} r ON b.room_id = r.room_id\n ORDER BY b.check_in_date\n `);\n return new Response(JSON.stringify(bookings.rows), {\n headers: { 'Content-Type': 'application/json' }\n });\n }\n\n // POST bookings with complex availability check\n if (url.pathname === '/bookings' && request.method === 'POST') {\n const booking = await request.json();\n \n // Check room availability and capacity\n const availabilityCheck = await sqlite.execute(`\n SELECT \n COUNT(*) as conflicting_bookings,\n max_occupancy\n FROM ${KEY}_rooms_${SCHEMA_VERSION} r\n LEFT JOIN ${KEY}_bookings_${SCHEMA_VERSION} b \n ON r.room_id = b.room_id \n AND (\n (? BETWEEN b.check_in_date AND b.check_out_date) OR\n (? BETWEEN b.check_in_date AND b.check_out_date) OR\n (b.check_in_date BETWEEN ? AND ?)\n )\n WHERE r.room_id = ?\n `, [\n booking.check_in_date, \n booking.check_out_date, \n booking.check_in_date, \n booking.check_out_date, \n booking.room_id\n ]);\n\n const { conflicting_bookings, max_occupancy } = availabilityCheck.rows[0];\n\n if (conflicting_bookings > 0) {\n return new Response('Room not available for selected dates', { status: 400 });\n }\n\n if (parseInt(booking.total_guests) > max_occupancy) {\n return new Response('Exceeds room capacity', { status: 400 });\n }\n\n // Insert booking\n await sqlite.execute(`\n INSERT INTO ${KEY}_bookings_${SCHEMA_VERSION} \n (booking_id, guest_id, room_id, check_in_date, check_out_date, total_guests, total_price) \n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n booking.booking_id,\n booking.guest_id, \n booking.room_id, \n booking.check_in_date, \n booking.check_out_date,\n booking.total_guests,\n booking.total_price\n ]);\n\n return new Response('Booking successful', { status: 200 });\n }\n\n // Serve HTML\n return new Response(`\n \n \n Advanced Hotel Management System\n \n \n \n

\n \n \n \n \n `, {\n headers: { 'Content-Type': 'text/html' }\n });\n}\n\nconst css = `\nbody {\n font-family: Arial, sans-serif;\n max-width: 1000px;\n margin: 0 auto;\n padding: 20px;\n background-color: #f4f4f4;\n}\n\n.hotel-management {\n background-color: white;\n padding: 20px;\n border-radius: 8px;\n box-shadow: 0 2px 4px rgba(0,0,0,0.1);\n}\n\nh1, h2 {\n color: #333;\n text-align: center;\n}\n\nform {\n display: flex;\n flex-wrap: wrap;\n gap: 10px;\n margin-bottom: 20px;\n justify-content: center;\n}\n\ninput {\n flex-grow: 1;\n min-width: 150px;\n padding: 8px;\n border: 1px solid #ddd;\n border-radius: 4px;\n}\n\nbutton {\n background-color: #4CAF50;\n color: white;\n border: none;\n padding: 8px 16px;\n border-radius: 4px;\n cursor: pointer;\n transition: background-color 0.3s;\n}\n\nbutton:hover {\n background-color: #45a049;\n}\n\ntable {\n width: 100%;\n border-collapse: collapse;\n margin-top: 20px;\n}\n\nth, td {\n border: 1px solid #ddd;\n padding: 12px;\n text-align: left;\n}\n\nth {\n background-color: #f2f2f2;\n font-weight: bold;\n}\n\ntr:nth-child(even) {\n background-color: #f9f9f9;\n}\n\ntr:hover {\n background-color: #f5f5f5;\n}\n`;","deleted","label",[98],"permalink","https://www.val.town/x/Adhi_007/hotelManagementSystem/code/main.tsx","extension","actionData","errors"]