Here is the page code for my solution. I hope someone can make use of this, or adapt it to something you can use.
import { getBookingsPage } from 'backend/customBackend.jsw';
// ****************************************************
// Tuning parameters
// ****************************************************
// Bookings data are retrieved one page at a time from the Wix back end.
// bookingsPageSize is the number of bookings records to retrieve at one.
// Because the bookings APIs are quite slow, we need to make sure we don't bog down.
// If this parameter to too large, it will take longer to retrieve each page,
// and hence increase the likelihood of an error 504 (gateway timeout).
// If you get too many of those errors, try reducing the page size.
const bookingsPageSize = 20;
// Loop delay (in milliseconds) is inserted after each page.
// If you get too many error 429 (too many requests), you can try increasing this number.
// Maybe it will also help give the garbage collector time to run if this page runs out of memory.
// All other things being equal, leave this as zero to allow this page to run as fast as possible.
const loopDelay = 0;
// ****************************************************
// Customization parameters
// ****************************************************
// These parameters are specific to each website and its partners
// This page will only process bookings that were purchased using a pricing plan
// that starts with the pricingPlanName string. For example, it will match:
// Partner Memebership
// Partnership Pass
// Partner XYZ
// Everything else will be ignored.
const pricingPlanName = "Partner";
// Each contact who is a partner member must have a custom label set on the contact in Wix.
// The label must start with one of the label strings in this table.
// The org field identifies the organization with which the partnership exists.
// The plan field identifies the specific plan that the specific member participates in.
const partnerLabels = [
{ label: "plan-one-label", org: "Org1", plan: "Plan One Name" },
{ label: "plan-two-label", org: "Org1", plan: "Plan Two Name" },
{ label: "plan-three-label", org: "Org2", plan: "Org2 Plan Name" }
];
// Each partner organization may have a specific code to identify each possible booking location.
// The partnerLocations table defines that mapping.
// There must be one enter per location per partner organization.
const partnerLocations = [
{ partner: "Org1", location: "East Center", partnerLocation: "East" },
{ partner: "Org1", location: "North Center", partnerLocation: "North" },
{ partner: "Org1", location: "South Center", partnerLocation: "South" },
{ partner: "Org1", location: "Public Pool", partnerLocation: "Pool" },
{ partner: "Org2", location: "East Center", partnerLocation: "ORG2LOC1234" },
{ partner: "Org2", location: "North Center", partnerLocation: "ORG2LOC1243" },
{ partner: "Org2", location: "South Center", partnerLocation: "ORG2LOC1257" },
{ partner: "Org2", location: "Public Pool", partnerLocation: "ORG2LOC1275" }
];
// Specific information about each partner
// siteId is a partner-specific string used for all bookings data reported to that organization
// detailBookings: if true, report all bookings data, session-by-session,
// if false, report a count of total bookings by each member.
// dedup: if true, combine multiple bookings on the same day into a single booking,
// if false, report multiple bookings on a single day as separate visits.
// Note: The total count of bookings by member will always be deduped (only count days on which visits occurred).
// tableId: This is the ID of the table element on this page in which bookings data for this partner
// will be consolidated.
// fileName: The name of the file into which bookings data for this partner will be exported.
// Note that the start date of the bookings data will be appended to the filename,
// and an extension of ".xls" will be added
const partnerConfig = [
{ partner: "Org1", siteId: "SITEXX9876", detailBookings: false, dedup: true, tableId: "#org1UsageData", filename: "Org1 Usage Report" },
{ partner: "Org2", siteId: "", detailBookings: true, dedup: true, tableId: "#org2UsageData", filename: "Org2 Usage" }
];
// ****************************************************
// This code runs when this page is first loaded
// ****************************************************
$w.onReady(function () {
// Set the action to be taken when the button is clicked
$w('#partnerPassReportButton').onClick(() => loadBookings());
// Initialize the form with the default start and end date.
// Note that by default (if the user doesn't everride these)
// this page will report on all booking data for the previous month.
// Note that the reporting is NOT inclusive of the end date.
let yr = new Date().getUTCFullYear();
let mon = new Date().getMonth();
$w('#dateStart').value = new Date(yr, mon - 1, 1);
$w('#dateEnd').value = new Date(yr, mon, 1);
// The status text on this page is used to keep the user informed about progress
$w('#statusText').text = "Ready!";
$w('#statusText').show();
// The error text is used, of course, to report errors to the user.
$w('#errorText').hide();
})
// ****************************************************
// This function will wait for the specified number of milliseconds.
// This only exists to possibly work around Wix/HTTP problems related to execing the capacity of the server.
// Logically should not be necessary.
// In other words, a kludge that can be used if things get weird.
// ****************************************************
async function wait(ms) {
return new Promise(resolve => {
setTimeout(resolve, ms);
});
}
// ****************************************************
// This is the main function for this page.
// It does all the work of retrieving the specified bookings data, processing it as required,
// and outputting it in the format requried by each of the partner organizations.
// ****************************************************
async function loadBookings() {
$w('#errorText').hide(); /* hidden until needed */
$w('#statusText').text = "Loading first bookings page ..."; /* Yeah, it's slow. Keep user entertained */
if (!($w('#dateStart').valid && $w('#dateEnd').valid)) {
$w('#errorText').show();
$w('#errorText').text = "Error in form fields";
return;
}
/* Turn off the button for the duration to avoid confusion */
$w('#partnerPassReportButton').disable();
try {
// This function uses the bookingsList table on this page to store the intermediate results
// and to keep the user entertained by showing those intermediate results
$w('#bookingsList').rows = [];
/* Main loop. Get pages of bookings data from the backend and process each page */
for (let index = 0, moreBookings = true; moreBookings; index += bookingsPageSize) {
/* Get a page of bookings data */
let results = await getBookingsPage($w('#dateStart').value, $w('#dateEnd').value, index, bookingsPageSize, $w('#password').value);
/* handle the page of results, if any */
if (results && results.length > 0) {
/* Keep user entertained by updating count of bookings and data of latest */
$w('#statusText').text = "Loading bookings for " +
results[0].sessionTime.toLocaleDateString() + ", " + index.toString() +
" complete ...";
/* loop through each of the individual booking results */
for (let result of results) {
/* Ignore any booking in which the pricing plan isn't a partnr plan,
or in which the booking was marked as a no-show */
if (result.pricingPlan &&
(result.pricingPlan.substr(0, pricingPlanName.length) == pricingPlanName) &&
(result.attendanceStatus != "No-Show")) {
let thisBooking = result;
/* Our Member ID is set to the same as the Partner's Member ID,
because Wix doesn't have a contact ID that is visible. */
thisBooking.memberId = thisBooking.partnerId;
/* The date of the booking is simple the date portion of the session time */
thisBooking.sessionDate = new Date(
thisBooking.sessionTime.getFullYear(),
thisBooking.sessionTime.getMonth(),
thisBooking.sessionTime.getDate()
);
/* Contact custom labels are passed in as a string, separated by semicolons */
/* Go through each one and check to see if it is one of the labels indicating a parter membr */
let bookingLabelArray = thisBooking.labels.split(";");
for (let thisBookingLabel of bookingLabelArray) {
for (let thisPartnerLabel of partnerLabels) {
if (thisBookingLabel.substr(0, thisPartnerLabel.label.length) == thisPartnerLabel.label) {
/* If we found a matching label, set the partner organization and plan */
thisBooking.partnerOrg = thisPartnerLabel.org;
thisBooking.partnerPlan = thisPartnerLabel.plan;
}
}
}
/* Each partner may have its own identifier for each location */
/* Use the mapping table to set the partner location based on the booking's session location */
for (let thisPartnerLocation of partnerLocations) {
if (thisBooking.sessionLocation.substr(0, thisPartnerLocation.location.length) == thisPartnerLocation.location &&
thisBooking.partnerOrg == thisPartnerLocation.partner) {
thisBooking.partnerLocation = thisPartnerLocation.partnerLocation;
}
}
/* Add this booking to the list of partner bookings */
let rows = $w('#bookingsList').rows;
rows.push(thisBooking);
$w('#bookingsList').rows = rows;
}
}
} else moreBookings = false;
/* This delay shouldn't be necessary, but added here in case there are problems with the server getting overwhelmed */
await wait(loopDelay);
}
/* Bookings are already sorted by session time */
/* Sort them additionally by member, so that all of a member's bookings are together */
/* Order will be Lasst Name, then First Name, then Session Time */
let rows = $w('#bookingsList').rows;
$w('#statusText').text = "Sorting " + rows.length.toString() + " bookings ...";
rows.sort(function (a, b) {
let aLast = a.lastName.toLowerCase();
let aFirst = a.firstName.toLowerCase();
let bLast = b.lastName.toLowerCase();
let bFirst = b.firstName.toLowerCase();
if (aLast > bLast) { return 1; } else if (aLast < bLast) { return -1; } else if (aFirst > bFirst) { return 1; } else if (aFirst < bFirst) { return -1; } else { return 0; }
});
/* This next section of code will dedup and count the bookings for each member */
let firstMemberResult = -1; /* index of first booking for this member */
let firstMemberDateResult = -1; /* index of first booking for this member on this date */
let currentMemberId = ""; /* Member ID is used to differentiate members */
let currentBookingDate = null;
for (let i = 0; i < rows.length; i++) {
/* Check to see if same member as previous row */
if (rows[i].memberId == currentMemberId) {
/* Check to see if same date as previous row */
if (+rows[i].sessionDate == +currentBookingDate) {
/* Same member and date as previous row, so incremement count of first booking on this date
and set the count for this row to zero. It will be ignored as a duplicate */
rows[firstMemberDateResult].memberDateBookingCount++;
rows[i].memberDateBookingCount = 0;
} else {
/* Same member, different date. Increment running count for the first booking for this member */
firstMemberDateResult = i;
currentBookingDate = rows[i].sessionDate;
rows[i].memberDateBookingCount = 1;
rows[firstMemberResult].memberBookingCount++;
}
/* Same member as previous row, so set booking count to zero */
rows[i].memberBookingCount = 0;
} else {
/* First booking for a member */
firstMemberResult = i;
firstMemberDateResult = i;
currentMemberId = rows[i].memberId;
currentBookingDate = rows[i].sessionDate;
rows[i].memberBookingCount = 1;
rows[i].memberDateBookingCount = 1;
}
}
$w('#bookingsList').rows = rows;
/* This section of code does all the partner-specific processing */
for (let part of partnerConfig) {
$w('#statusText').text = "Exporting " + part.partner + " bookings ...";
/* Create the output table using those bookings that match this partner */
let partnerRows = [];
for (let row of rows) {
if (row.partnerOrg == part.partner) {
/* Set the Site ID field, specific to this partner */
/* Use of this field is specific to the partner, and may not be relevant for all */
row.siteId = part.siteId;
/* Include the row depending on the configuration parameters for this partner */
if ((row.memberBookingCount > 0) ||
(part.detailBookings && row.memberDateBookingCount > 0) ||
(!part.dedup)) {
partnerRows.push(row);
}
}
}
/* Store the results in the table configured for this partner */
$w(part.tableId).rows = partnerRows;
/* Now we are going to output the table to Excel */
/* First create the filename. Use the filename from the configuratuion table */
/* Then append the date of the first booking and the extension */
/* filenameYYMMDD.xls */
let yr1 = $w('#dateStart').value.getFullYear().toString().substr(2);
let mon1 = ($w('#dateStart').value.getMonth() + 1).toString();
if (mon1.length < 2) { mon1 = "0" + mon1; }
let dt1 = $w('#dateStart').value.getDate().toString();
if (dt1.length < 2) { dt1 = "0" + dt1; }
let exportTable = part.filename + yr1 + mon1 + dt1 + ".xls";
/* The Excel export function takes as its parameter a single string */
/* filename followed by the html for the table to be exported */
exportTable += "<table>";
exportTable += "<tr>";
/* Add the column headings */
for (let col of $w(part.tableId).columns) {
exportTable += "<th>";
exportTable += col.label;
exportTable += "</th>";
}
exportTable += "</tr>";
/* Now add the table data */
for (let row of partnerRows) {
exportTable += "<tr>";
for (let col of $w(part.tableId).columns) {
exportTable += "<td>";
let cellData = "";
if (row[col.dataPath]) {
cellData = row[col.dataPath];
/* If the cell data is a date, convert it to a string */
if (row[col.dataPath] instanceof Date) {
cellData = row[col.dataPath].toLocaleDateString();
} else {
cellData = row[col.dataPath];
}
}
exportTable += cellData;
exportTable += "</td>";
}
exportTable += "</tr>";
}
exportTable += "</table>";
/* Send the download informatino (filename+table) to the HTML element to download */
$w('#downloadLink').postMessage(exportTable);
}
} catch (error) {
console.error('loadBookings error - ' + error.message);
} finally {
/* All done, let the user know, and let them do it again */
$w('#partnerPassReportButton').enable();
$w('#statusText').text = "Processing complete!";
};
}