Fandaqah PMS

Details for one hotel: address, city, contact fields.

Room cleaning jobs. open_only keeps those not yet completed. completed_at IS NULL is the open marker. There are 2.6M of these rows estate-wide, so this is always a page of the most recent, never the lot.

Guests currently in the hotel: checked in and not yet checked out. Uses the reservation's own checked_in / checked_out flags rather than comparing today against date_in and date_out, because an overstay or an early departure makes those two answers differ, and the flags are what the front desk actually set.

Recent folios/invoices, most recent first. `from` and `to` are the stay period the invoice covers. Both are reserved words in MySQL and must stay quoted.

Room maintenance jobs. open_only keeps those not yet completed. expected_at is the promised completion date where one was set, so a row whose expected_at has passed while completed_at is still null is overdue.

List every hotel the calling user may query. Returns a JSON array of hotels: hotel_id, name, en_name, city.

Night audit runs: what each one processed, and whether it succeeded. The nightly close is where no-shows are flagged, outstanding checkouts are processed and the occupancy snapshot get_occupancy reads is written. A failed or missing run is why yesterday's numbers can look wrong.

The PMS's own occupancy snapshot. Omit hotel_id for the whole portfolio. Returns what the PMS recorded for its most recent snapshot date, verbatim: units_count, occupied, available, cleaning, maintenance, booked, and the percentage the PMS itself computed. The snapshot date is included, because these are daily rows and the latest one may be yesterday. TWO THINGS TO KNOW BEFORE QUOTING THE PERCENTAGE. It can exceed 100%. Hotel 4188 reports 60 occupied against 12 units -- 500% -- because serviced apartments turn over several times a day and `occupied` counts stays rather than rooms. Hotel 2411 hit 102% on 2026-07-31. That is the PMS's own arithmetic and it is reproduced rather than corrected, so this agrees with what a manager sees in the web app. It is NOT derived from units.status. An earlier version of this tool counted status = 1 as occupied and reported 72.7% for hotel 2411 where the PMS said 56%. status = 1 is 89.9% of every room in the estate: it means the room is in service, not that someone is in it. Only 16 of those 48 "occupied" rooms at 2411 had a live stay. For a figure this server can define exactly rather than inherit, use get_rooms and count the ones whose state is occupied -- but be aware that is a different question from what the PMS reports here.

Daily occupancy snapshots over the last N days, oldest first. Reads the PMS's own `occupieds` table, the same source get_occupancy uses for today, so a trend and a spot reading cannot disagree. Percentages are the PMS's own, not recomputed here. One row per snapshot as the PMS wrote it. A day the night audit did not run is simply absent rather than interpolated -- see get_night_audit when a gap appears.

Money actually taken and paid out at one of the caller's hotels. Cash movement, which is a different question from get_revenue. Revenue is what the bookings were worth; this is what moved. They will not agree and should not: a confirmed booking with no deposit yet contributes to one and not the other. 'deposit' is money in, 'withdraw' is money out. Both are reported, plus the net. ON THE UNITS: transactions.amount is a BIGINT in HALALAS, not SAR. Established by cross-referencing 12 transactions against the reservations they belong to -- median ratio exactly 100.0000 (amount 218,165 against a 2,181.65 reservation), and the off-ratio cases were partial payments. So everything here is divided by 100 and reported in SAR. If that ratio ever changes, every figure this tool returns is wrong by a factor of 100, which is why the derivation is written down rather than assumed.

ADR, RevPAR and room-nights for a month. Omit year/month for this month. ADR = room revenue / room-nights sold RevPAR = room revenue / (rooms available x nights in the month) Room-nights are DATEDIFF(date_out, date_in) summed over confirmed, non-no-show stays, so a booking is counted for the nights it covers rather than once. Same exclusions as get_revenue, deliberately: two revenue figures that disagree because they filter differently are worse than one. Rooms available is the hotel's current live unit count, which is a simplification: a room added or removed mid-month is treated as though it existed all month. Stated because RevPAR is otherwise easy to over-trust.

Guests formally registered against a stay (Shomoos / Ministry of Interior). SPARSE ON REAL DATA, and that is a property of the source rather than a fault here: measured on the replica, only 417 of 1,729 hotels have any rows at all, and 4,243 of 3.79M reservations have a linked guest. This table records formal identity registration for specific cases, not the general guest list. For "who is staying", use get_bookers. id_number is never returned, same as get_bookers.

Everything about one booking: stay, room, booker, invoices, extensions. Four scoped queries rather than one wide join, because a booking with three invoices and two extensions would otherwise return six rows that a reader has to un-multiply. Each is tenant-scoped independently.

Recent reservations for one of the caller's hotels, newest first. Includes cancelled bookings, with their status, so the caller can see them. Revenue figures deliberately do not -- see get_revenue.

Confirmed revenue for a month. Omit hotel_id for the whole portfolio. Reads reservations, deliberately not `invoices` (Spark subscription billing -- what Fandaqah charges the hotel) and not `transactions` (see get_payments). Cancelled and no-show bookings are excluded: that exclusion is the difference between a revenue figure and a sum of rows.

Revenue split by booking channel for a month. Omit year/month for this month. Same exclusions as get_revenue -- confirmed, not no-show, by check-in date -- so the parts sum to the whole. Two figures that disagree because they filter differently are worse than one. Bookings with no source_id appear as a null channel rather than being dropped; silently discarding them would make the split fail to reconcile with get_revenue and give no clue why.

Recent room status changes: what changed, from what, to what, and why. Raw status codes are returned rather than guessed labels. units.status carries at least three distinct values in live data and only 1 (in service) has been confirmed against the PMS; inventing names for 2 and 3 would repeat the mistake that made get_occupancy report rooms-in-service as rooms-occupied.

Room categories and their rack rates. Rates are per day of week, because the PMS prices Friday differently from Tuesday, plus a monthly and an hourly rate where the property sells them.

Rooms/units at one of the caller's hotels, with occupancy status.

Extras sold in a month, totalled by direction. Omit year/month for this month. Amounts are halalas divided by 100, and withdrawals are stored negative -- the same convention as transactions, confirmed against this table rather than carried over on faith. net_sar is the signed sum, not deposits minus withdrawals: subtracting an already-negative figure is the error that once put get_payments out by 7.8M SAR and in the wrong direction.

The hotel's sellable extras and their list prices. price here is plain SAR, unlike the halalas used by transactions and service_logs. Measured against the live table, not assumed.

Stays that were extended: the old departure date and the new one.