commit - 77f906b3cf1c549a28c0cd767e2244e470f598bf
commit + 22f3a7ce67aefccfc7d2c6871c9168ec89319e29
blob - 8652d6fb89573981c8b033c155824147cea4c5f8
blob + 1603bc07aeb8c87cea5da5a9983e6e2c55737b5e
--- bookingform.c
+++ bookingform.c
void
handle_bookingform(const char *day_param)
{
- struct booking_list bl;
- struct desk_list dl;
+ struct booking_list all;
struct tm tm;
char display[16], datestr[16];
- int i;
+ int i, has_booked, has_available;
const char *user = getenv("REMOTE_USER");
if (user == NULL || *user == '\0') {
return;
}
- if (db_query_day_bookings(db, datestr, &bl) != 0) {
- fprintf(stderr, "Error listing booked desks for day "
+ if (db_query_desks_with_bookings(db, datestr, &all) != 0) {
+ fprintf(stderr, "Error listing desks for day "
"\"%s\"\n", datestr);
db_close(db);
cgi_error(500);
return;
}
-
- if (db_query_available_desks(db, datestr, &dl) != 0) {
- fprintf(stderr, "Error listing available desks for day "
- "\"%s\"\n", datestr);
- booking_list_free(&bl);
- db_close(db);
- cgi_error(500);
- return;
- }
db_close(db);
char *csrf = cgi_csrf_generate();
if (csrf == NULL) {
- booking_list_free(&bl);
- desk_list_free(&dl);
+ booking_list_free(&all);
cgi_error(500);
return;
}
printf(" <img src=\"/static/floorplan.png\" "
"alt=\"Floor plan\">\n");
- if (bl.count > 0) {
+ /* Check which sections are needed. */
+ has_booked = 0;
+ has_available = 0;
+ for (i = 0; i < all.count; i++) {
+ if (*all.items[i].user != '\0')
+ has_booked = 1;
+ else
+ has_available = 1;
+ }
+
+ if (has_booked) {
printf(" <h2>Current bookings for ");
cgi_html_escape(display);
printf("</h2>\n");
printf(" </tr>\n");
printf(" </thead>\n");
printf(" <tbody>\n");
- for (i = 0; i < bl.count; i++) {
+ for (i = 0; i < all.count; i++) {
+ if (*all.items[i].user == '\0')
+ continue;
printf(" <tr>\n");
printf(" <td>");
- cgi_html_escape(bl.items[i].desk);
+ cgi_html_escape(all.items[i].desk);
printf("</td>\n");
printf(" <td>");
- cgi_html_escape(bl.items[i].user);
+ cgi_html_escape(all.items[i].user);
printf("</td>\n");
printf(" </tr>\n");
}
printf(" </table>\n");
}
- if (dl.count > 0) {
+ if (has_available) {
printf(" <h2>Book a desk for ");
cgi_html_escape(display);
printf("</h2>\n");
printf(" <form method=\"POST\">\n");
printf(" <input type=\"hidden\" name=\"%s\" "
"value=\"%s\">\n", CSRF_KEY, csrf);
- for (i = 0; i < dl.count; i++) {
+ for (i = 0; i < all.count; i++) {
+ if (*all.items[i].user != '\0')
+ continue;
printf(" <input type=\"radio\" id=\"");
- cgi_html_escape(dl.items[i]);
+ cgi_html_escape(all.items[i].desk);
printf("\" name=\"%s\" value=\"", DESK_KEY);
- cgi_html_escape(dl.items[i]);
+ cgi_html_escape(all.items[i].desk);
printf("\">\n");
printf(" <label for=\"");
- cgi_html_escape(dl.items[i]);
+ cgi_html_escape(all.items[i].desk);
printf("\">");
- cgi_html_escape(dl.items[i]);
+ cgi_html_escape(all.items[i].desk);
printf("</label>\n");
printf(" <br/>\n");
}
printf("</html>\n");
free(csrf);
- booking_list_free(&bl);
- desk_list_free(&dl);
+ booking_list_free(&all);
}
blob - 6965fc60f248b87956a53fc2097f3c0a477cb72d
blob + 1e5f76636cf06ad3a70eead626f995e49af9bbde
--- db.c
+++ db.c
, '\0'
};
+static const char sql_select_desks_with_bookings[] = {
+#embed "sql/select_desks_with_bookings.sql"
+ , '\0'
+};
+
static const char sql_insert_booking[] = {
#embed "sql/insert_booking.sql"
, '\0'
}
/*
+ * Query all desks with their booking status for a given day.
+ * Each desk appears exactly once: booked desks have a non-empty
+ * user field, available desks have an empty user string. Results
+ * are ordered by desk name using natural sort collation. Replaces
+ * the need for separate day_bookings and available_desks queries.
+ * The caller must call booking_list_free when done. Returns 0 on
+ * success, -1 on failure.
+ */
+int
+db_query_desks_with_bookings(sqlite3 *db, const char *day,
+ struct booking_list *bl)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+
+ memset(bl, 0, sizeof(*bl));
+
+ if (sqlite3_prepare_v2(db, sql_select_desks_with_bookings, -1,
+ &stmt, NULL) != SQLITE_OK) {
+ fprintf(stderr, "prepare select desks with bookings: %s\n",
+ sqlite3_errmsg(db));
+ return -1;
+ }
+
+ sqlite3_bind_text(stmt, 1, day, -1, SQLITE_STATIC);
+
+ while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
+ const char *desk = (const char *)sqlite3_column_text(stmt, 0);
+ const char *user = "";
+ if (sqlite3_column_type(stmt, 1) != SQLITE_NULL)
+ user = (const char *)sqlite3_column_text(stmt, 1);
+ if (booking_list_add(bl, user, desk, day) != 0) {
+ sqlite3_finalize(stmt);
+ booking_list_free(bl);
+ return -1;
+ }
+ }
+
+ sqlite3_finalize(stmt);
+ if (rc != SQLITE_DONE) {
+ fprintf(stderr, "step select desks with bookings: %s\n",
+ sqlite3_errmsg(db));
+ booking_list_free(bl);
+ return -1;
+ }
+
+ return 0;
+}
+
+/*
* Insert a booking for the given user, desk, and day. Foreign key
* constraints (enabled at connection open) reject unknown desks.
* Returns 0 on success, or the SQLite extended error code on
blob - e5fb88c036b13412cac5b0b9cc66a11b4fbae1f4
blob + 64e97bac5c093cc1a50f1b48b114f6b4267d24d3
--- db.h
+++ db.h
struct booking_list *);
int db_query_available_desks(sqlite3 *, const char *,
struct desk_list *);
+int db_query_desks_with_bookings(sqlite3 *, const char *,
+ struct booking_list *);
int db_insert_booking(sqlite3 *, const char *, const char *,
const char *);
int db_delete_booking(sqlite3 *, const char *, const char *,
blob - /dev/null
blob + fe2bd69dabc977c43cf0bd7436b46a5ba565e3d8 (mode 644)
--- /dev/null
+++ sql/select_desks_with_bookings.sql
+SELECT d.desk, b.user FROM desks d LEFT JOIN bookings b ON d.desk = b.desk AND b.day = ? ORDER BY d.desk COLLATE "natural"
\ No newline at end of file
