「实用场景教程」如何用日程控件DHTMLX Scheduler制作酒店预订日历？（三）

dhtmlxScheduler是一个类似于Google日历的JavaScript日程安排控件日历事件通过Ajax动态加载支持通过拖放功能调整事件日期和时间事件可以按天周月三个种视图显示。在本教程中我们将使用两个强大的工具DHTMLX Scheduler库和Angular框架来创建一个全面的酒店客房预订应用程序。在上文中我们为大家介绍了提供保存数据中的数据加载、CRUD操作实现等本文将继续介绍服务器配置。Step 5 – 服务器配置现在让我们继续为应用程序设置Node.js服务器本教程使用Express框架和MySQL作为数据存储。添加依赖项和安装模块您应该设置MySQL服务器或者可以使用其他服务例如免费MySQL托管。添加express、mysql和date-format-lite模块$ npm install express mysql date-format-liteserver.js被指定为上面的输入点现在让我们在项目的根目录下创建server文件夹并添加server.js文件代码如下const express require(express); // use Express const app express(); // create application const port 3000; // port for listening const cors require(cors); app.use(cors()); // enable CORS for all routes // MySQL will be used for db access and util to promisify queries const util require(util); const mysql require(mysql); // use your own parameters for database const mysqlConfig { connectionLimit: 10, host: localhost, user: root, password: , database: room_reservation_node }; app.use(express.json()); // Enable JSON body parsing // return static pages from the ./public directory app.use(express.static(__dirname /public)); // start server app.listen(port, () { console.log(Server is running on port port ...); }); const router require(./router); // open connection to mysql const connectionPool mysql.createPool(mysqlConfig); connectionPool.query util.promisify(connectionPool.query); // add listeners to basic CRUD requests const DatabaseHandler require(./databaseHandler); const databaseHandler new DatabaseHandler(connectionPool); router.setRoutes(app, /data, databaseHandler);然后打开package.json文件夹将start语句替换为scripts: { ng: ng, start: concurrently \node server/server.js\ \ng serve\, …我们将使用concurrent包来同时启动服务器和客户端应用程序因此添加concurrent模块$ npm install concurrently准备数据库让我们将Scheduler连接到数据库并定义在其中读取和写入项的方法。创建数据库首先我们需要一个数据库来工作您可以使用自己喜欢的mysql-client或通过控制台创建数据库。要使用mysql-client创建数据库打开它并执行下面的代码创建预订表CREATE TABLE reservations ( id bigint(20) unsigned AUTO_INCREMENT, start_date datetime NOT NULL, end_date datetime NOT NULL, text varchar(255) DEFAULT NULL, room varchar(255) DEFAULT NULL, booking_status varchar(255) DEFAULT NULL, is_paid BOOLEAN DEFAULT NULL CHECK (is_paid IN (0, 1)), PRIMARY KEY (id) ) DEFAULT CHARSETutf8;让我们添加一些测试数据INSERT INTO reservations VALUES (2, 2023-08-01, 2023-08-11, RSV2023-08-01ABC124, 3, 4, true); INSERT INTO reservations VALUES (3, 2023-08-07, 2023-08-17, RSV2023-08-07ABC126, 5, 3, true); INSERT INTO reservations VALUES (4, 2023-08-04, 2023-08-16, RSV2023-08-04ABC125, 7, 4, false); INSERT INTO reservations VALUES (13, 2023-07-28, 2023-08-14, RSV2023-07-28ABC123, 1, 4, true); INSERT INTO reservations VALUES (14, 2023-08-14, 2023-08-27, RSV2023-08-14ABC129, 1, 3, false); INSERT INTO reservations VALUES (15, 2023-08-19, 2023-08-29, new booking, 4, 1, false); INSERT INTO reservations VALUES (16, 2023-08-24, 2023-08-31, new booking, 11, 1, false); INSERT INTO reservations VALUES (17, 2023-08-17, 2023-08-26, RSV2023-08-17ABC135, 6, 2, false); INSERT INTO reservations VALUES (18, 2023-08-18, 2023-08-31, RSV2023-08-18ABC139, 9, 2, false); INSERT INTO reservations VALUES (19, 2023-08-02, 2023-08-12, RSV2023-08-02ABC127, 10, 4, true); INSERT INTO reservations VALUES (20, 2023-08-12, 2023-08-21, RSV2023-08-12ABC130, 10, 3, false);创建房间表CREATE TABLE rooms ( id bigint(20) unsigned AUTO_INCREMENT, value varchar(255) DEFAULT NULL, label varchar(255) DEFAULT NULL, type varchar(255) DEFAULT NULL, cleaning_status varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSETutf8;添加一些测试数据INSERT INTO rooms VALUES (1, 1, 101, 1, 1); INSERT INTO rooms VALUES (2, 2, 102, 1, 3); INSERT INTO rooms VALUES (3, 3, 103, 1, 2); INSERT INTO rooms VALUES (4, 4, 104, 1, 1); INSERT INTO rooms VALUES (5, 5, 105, 2, 1); INSERT INTO rooms VALUES (6, 6, 201, 2, 2); INSERT INTO rooms VALUES (7, 7, 202, 2, 1); INSERT INTO rooms VALUES (8, 8, 203, 3, 3); INSERT INTO rooms VALUES (9, 9, 204, 3, 3); INSERT INTO rooms VALUES (10, 10, 301, 4, 2); INSERT INTO rooms VALUES (11, 11, 302, 4, 2); INSERT INTO rooms VALUES (12, 12, 303, 1, 2);创建roomTypes表CREATE TABLE roomTypes ( id bigint(20) unsigned AUTO_INCREMENT, value varchar(255) DEFAULT NULL, label varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSETutf8;添加一些测试数据INSERT INTO roomTypes VALUES (1, 1, 1 bed); INSERT INTO roomTypes VALUES (2, 2, 2 bed); INSERT INTO roomTypes VALUES (3, 3, 3 bed); INSERT INTO roomTypes VALUES (4, 4, 4 bed);创建cleaningStatuses表CREATE TABLE cleaningStatuses ( id bigint(20) unsigned AUTO_INCREMENT, value varchar(255) DEFAULT NULL, label varchar(255) DEFAULT NULL, color varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSETutf8;添加一些测试数据INSERT INTO cleaningStatuses VALUES (1, 1, Ready, #43a047); INSERT INTO cleaningStatuses VALUES (2, 2, Dirty, #e53935); INSERT INTO cleaningStatuses VALUES (3, 3, Clean up, #ffb300);创建bookingStatuses表CREATE TABLE bookingStatuses ( id bigint(20) unsigned AUTO_INCREMENT, value varchar(255) DEFAULT NULL, label varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) DEFAULT CHARSETutf8;添加一些测试数据INSERT INTO bookingStatuses VALUES (1, 1, New); INSERT INTO bookingStatuses VALUES (2, 2, Confirmed); INSERT INTO bookingStatuses VALUES (3, 3, Arrived); INSERT INTO bookingStatuses VALUES (4, 4, Checked Out);实现数据访问所有的读/写逻辑都将在一个名为DatabaseHandler的单独模块中定义它将使用mysql连接并在指定的表中执行简单的CRUD操作读取所有项插入新项更新或删除现有项。为此创建databaseHandler.js文件并将以下代码添加到其中require(date-format-lite); // add date format class DatabaseHandler { constructor(connection, table) { this._db connection; this.table reservations; } /// ↓↓↓ reservations handler ↓↓↓ // get reservations, use dynamic loading if parameters sent async getAllReservations(params) { let query SELECT * FROM ??; let queryParams [ this.table ]; let result await this._db.query(query, queryParams); result.forEach((entry) { // format date and time entry.start_date entry.start_date.format(YYYY-MM-DD hh:mm); entry.end_date entry.end_date.format(YYYY-MM-DD hh:mm); }); return result; } // create new reservation async insert(data) { let result await this._db.query( INSERT INTO ?? (start_date, end_date, text, room, booking_status, is_paid) VALUES (?,?,?,?,?,?), [this.table, data.start_date, data.end_date, data.text, data.room, data.booking_status, data.is_paid]); return { action: inserted, tid: result.insertId } } // update reservation async update(id, data) { await this._db.query( UPDATE ?? SET start_date ?, end_date ?, text ?, room ?, booking_status ?, is_paid ? WHERE id ?, [this.table, data.start_date, data.end_date, data.text, data.room, data.booking_status, data.is_paid, id]); return { action: updated } } // delete reservation async delete(id) { await this._db.query( DELETE FROM ?? WHERE id? ;, [this.table, id]); return { action: deleted } } /// ↑↑↑ reservations handler ↑↑↑ /// ↓↓↓ room cleanup status handler ↓↓↓ // get rooms async getAllRooms(params) { let query SELECT * FROM ??; let queryParams [ rooms ]; let result await this._db.query(query, queryParams); return result; } // update room cleanup status async updateRoomCleaningStatus(id, data) { await this._db.query( UPDATE ?? SET value ?, label ?, type ?, cleaning_status ? WHERE id ?, [rooms, data.key, data.label, data.type, data.cleaning_status, id]); return { action: updated } } /// ↑↑↑ room cleanup status handler ↑↑↑ /// ↓↓↓ get room types ↓↓↓ async getRoomTypes(params) { let query SELECT * FROM ??; let queryParams [ roomTypes ]; let result await this._db.query(query, queryParams); return result; } /// ↑↑↑ get room types ↑↑↑ /// ↓↓↓ get cleaning statuses ↓↓↓ async getCleaningStatuses(params) { let query SELECT * FROM ??; let queryParams [ cleaningStatuses ]; let result await this._db.query(query, queryParams); return result; } /// ↑↑↑ get cleaning statuses ↑↑↑ /// ↓↓↓ get booking statuses ↓↓↓ async getBookingStatuses(params) { let query SELECT * FROM ??; let queryParams [ bookingStatuses ]; let result await this._db.query(query, queryParams); return result; } /// ↑↑↑ get booking statuses ↑↑↑ } module.exports DatabaseHandler;路由然后需要设置路由以便放置在页面上的调度器可以访问存储。为此创建另一个helper模块并将其命名为router.jsfunction callMethod (method) { return async (req, res) { let result; try { result await method(req, res); } catch (e) { result { action: error, message: e.message } } res.send(result); } }; module.exports { setRoutes (app, prefix, databaseHandler) { /// ↓↓↓ reservations router ↓↓↓ app.get(${prefix}/reservations, callMethod((req) { return databaseHandler.getAllReservations(req.query); })); app.post(${prefix}/reservations, callMethod((req) { return databaseHandler.insert(req.body); })); app.put(${prefix}/reservations/:id, callMethod((req) { return databaseHandler.update(req.params.id, req.body); })); app.delete(${prefix}/reservations/:id, callMethod((req) { return databaseHandler.delete(req.params.id); })); /// ↑↑↑ reservations router ↑↑↑ /// ↓↓↓ rooms router ↓↓↓ app.get(${prefix}/collections/rooms, callMethod((req) { return databaseHandler.getAllRooms(req.query); })); app.put(${prefix}/collections/rooms/:id, callMethod((req) { return databaseHandler.updateRoomCleaningStatus(req.params.id, req.body); })); /// ↑↑↑ rooms router ↑↑↑ /// ↓↓↓ room types router ↓↓↓ app.get(${prefix}/collections/roomTypes, callMethod((req) { return databaseHandler.getRoomTypes(req.query); })); /// ↑↑↑ room types router ↑↑↑ /// ↓↓↓ cleaning statuses router ↓↓↓ app.get(${prefix}/collections/cleaningStatuses, callMethod((req) { return databaseHandler.getCleaningStatuses(req.query); })); /// ↑↑↑ cleaning statuses router ↑↑↑ /// ↓↓↓ booking statuses router ↓↓↓ app.get(${prefix}/collections/bookingStatuses, callMethod((req) { return databaseHandler.getBookingStatuses(req.query); })); /// ↑↑↑ booking statuses router ↑↑↑ } };它所做的就是设置应用程序来侦听调度器可以发送的请求url并调用存储的适当方法。请注意所有方法都包装在try-catch块中以便能够捕获任何错误并向客户机返回适当的错误响应。还要注意异常消息是直接写入API响应的。这在开发过程中非常方便但在生产环境中对客户端隐藏这些消息可能是一个好主意因为到达那里的原始mysql异常可能包含敏感数据。现在如果您打开应用程序页面可以看到一个带有预订的调度程序。可以在调度程序中创建、删除和修改项即使重新加载页面您所做的任何更改也将保留。