Hi Kara

Thank you for reaching out to us.

When you go to this file wp_amelia_customer_bookings click search and there will be a lot of filters

for example i typed 2 into the Value for customerId and it showed me all of that customer's bookings.

Hi Kara

Let me forward this to our Level 2 they might be able to assist you more in this matter.

We appreciate your time and patience.

Hello Kara

If the customer booked all 30 appointments, they will not be able to book them anymore, regardless if they canceled some of them or not. Cancelation is considered as a held appointment, so canceling one will not free the time slot again.

Our developers are currently working on allowing admins to manage packages from the back-end, and I hope modifying the number of appointments a customer has left will be included, but I can't say for sure. At this time, you cannot modify the plugin in order to change the behavior of the packages.

I reached out to one of our developers, to see if there's a way to "reset" the number of appointments in an existing package, but I can't promise anything.

Hi again Kara

To pull the data for a purchased package with the total number of bookings and the booked number of bookings, use this query:

SELECT
    MAX(pcs.id) AS id,
    pc.packageId AS packageId,
    pc.customerId AS customerId,
    pcs.serviceId AS serviceId,
    SUM(pcs.bookingsCount) AS allowedBookings,
    SUM((SELECT COUNT(*) FROM wp_amelia_customer_bookings cb WHERE cb.packageCustomerServiceId = pcs.id)) AS boooked
FROM wp_amelia_packages_customers_to_services pcs
INNER JOIN wp_amelia_packages_to_customers pc ON pcs.packageCustomerId = pc.id
GROUP BY packageId, customerId, serviceId

If you want the ID's of the columns that meet the condition that the number of booked appointments is equal to the number of available appointments, you can use this:

SELECT
    MAX(pcs.id) AS id
FROM wp_amelia_packages_customers_to_services pcs
INNER JOIN wp_amelia_packages_to_customers pc ON pcs.packageCustomerId = pc.id
GROUP BY packageId, customerId, serviceId
HAVING SUM(pcs.bookingsCount) = SUM((SELECT COUNT(*) FROM wp_amelia_customer_bookings cb WHERE cb.packageCustomerServiceId = pcs.id))

One more thing.

Since you want to revert the state, that's a bit problematic. You would need to perform an UPDATE for NULL columns "packageCustomerServiceId" in "wp_amelia_customer_booking" table for the found ID's (unlink them from the package, but their payment is lost when the appointments are listed).

Or

Do an UPDATE of the "bookingsCount" column in "wp_amelia_packages_customers_to_services" to the increased value, so booking will be possible again from the panel, but the new value for the "Total X appointments in this service" will be shown.