Brand-Brothers
Documentatie web site Brand-Brothers
Forms
Handleiding
Deze handleiding beschrijft de verschillende parameters die je kunt gebruiken bij het configureren van een bier selectie formulier.
Het form wordt aangeroepen door middel van een URL en alle parameters worden in de URL meegegeven.
Voorbeeld:
https://....../.../brand-brothers/forms/?eigenaar=naam&merk=Texels&set=Texels-vuurtorenbar&minbeers=3&beerfilter=amstel&options=1,2,3,4,25,26&t=1&maxbeers=3
Spaties in URL
In de gehele URL moeten spaties worden gecodeerd als %20
.
Parameters
De parameters zoals in het voorbeeld getoond, worden hieronder beschreven. Elke parameter is rood gemarkeerd.
minbeers
- Beschrijving: Het minimaal aantal bieren dat geselecteerd moet worden.
- Werking: Als er minder bieren zijn geselecteerd dan het minimum, krijg je een waarschuwing wanneer je probeert het formulier te versturen.
maxbeers
- Beschrijving: Het maximaal aantal bieren dat geselecteerd kan worden.
- Werking: Als je meer bieren selecteert dan het maximum, verschijnt er onmiddellijk een waarschuwing.
Verborgen Velden
- Beschrijving: De volgende velden zijn verborgen en kunnen via de URL worden meegegeven. De verborgen velden worden dor het formulier meegestuurd en verschijnen in de email.
- eigenaar: De eigenaar van het biermerk.
- merk: De naam van het biermerk.
- set: De naam van de set die in de titel van het formulier verschijnt.
t (Testmodus)
- Beschrijving: Hiermee kun je de testmodus van het formulier instellen. aan t kunnen de volgende waarden worden gegeven:
- 0: Normale werking (dezelfde als geen waarde opgegeven).
- 1: Alle verplichte velden zijn niet meer verplicht en het formulier wordt niet per e-mail verzonden.
- 2: Alle verplichte velden zijn niet meer verplicht en het formulier wordt wel per e-mail verzonden
options
- Beschrijving: Een lijst van opties die als een komma-gescheiden lijst moeten worden opgegeven. Deze opties worden getoond op het formulier.
beerfilter
- Beschrijving: Een filter om bieren op naam te selecteren.
- Werking: Als de waarde 'none' is, wordt de gehele bierselectie niet getoond. Je kunt meerdere filters specificeren, bijvoorbeeld:
beerfilter=amstel,brand
.
Options aanpassen
Om options aan te passen kan deze URL worden gebruikt:
https://....../.../brand-brothers/forms/options
of nieuwe interface
https://....../.../brand-brothers/forms/options/index2.php
Optienummer
Elke optie heeft een nummer. Aan de hand van dit nummer wordt de optie op het formulieer geplaatst. De nummers hoeven niet uniek te zijn. Wanneer meerdere opties hetzelfde nummer hebben, worden alle opties op het fomrulier getoond wanneer deze optie wordt gekozen.
Optienaam
De naam van de optie is de naam die op de aanvraag verschijnt (email).
Optielabel
Het label is de de naam zoals die op het formulier verschijnt.
Extra naam
De extra naam is de (open) vraag die wordt gesteld indien de optie wordt geslecteerd. De naam is de naam zoals die op het aanvraag (email) verschijnt.
Extra label
Het extra label is de (open) vraag die wordt gesteld indien de optie wordt geslecteerd. Het label is de naam zoals die op het formulier verschijnt.
URL naar options (back-up)
De 'database' waarin de options worden opgeslagen kun je via de volgende URLs controleren:
https://.../.../brand-brothers/forms/options/options.js
https://.../.../brand-brothers/forms/options/options.json
Deze bestanden zou je ook als (extra) back-up kunnen gebruiken.
De twee bestanden bevatten dezelfde informatie, maar zijn om technische redenen op een andere wijze vormgegeven.
Options kunnen niet worden aangepast?
Als de opties niet kunnen worden aangepast, controleer dan de twee bestanden. Zijn de wijzigingen in deze bestanden wel aangepast dan zorgt de cache van de browser ervoor dat de wijzigingen niet (direct) worden getoond. Laadt de pagina dan opnieuw en leeg de browser cache (dit werkt op elke browser anders).
Beers aanpassen
(dit is nog nooit in productie getest)
https://............/brand-brothers/forms/beers
of nieuwe interface
https://............/brand-brothers/forms/beers/index2.php
Een beer heeft een ID en een naam zoals dat op het form te zien is. Zorg ervoor dat de ID's uniek blijven.
--
Equipment
Equipmentlist
(in ontwikkeling)
https://..../brand-brothers/equipment-list/folders.php
--
Technical (Equipment )
Equipment
folder -> equipment ->
Performance
Plaatjes
Plaatjes staan in Amazon Cloud en zijn 200kb tot 500kb.
No Cache, no optimazation
Het laden van 5 items duurt 1 tot 2.5 seconden afhankelijk van de grootte van de plaatjes.
Async laden plaatjes
Laadtijden per 5 items tussen de 0.6 en 1.2 seconden zijn.
Plaatjes kleiner maken (optimized for web)
Het laden van 5 items duurt 2 ms.. 200 to 500 maal sneller.
API Call optimization
API calls naar rentman kosten gemiddeld 120 ms per call (dat is snel); voor elk item heb je wel 3 call nodig (item data, plaatje data, calender data).
Dat is dus 360 ms per item.
Via cache is dat < 1 ms.
Mogleijk optimalisatie is parrallel async verwerking.
Implementation
all getc api calls are cached. The cache has a predefiend TTL $cache_ttl = 3600
the raw return value of the API is json encoded written to file.
There is code to delete all the cache (not tested yet)
Rerender plaatje sop server
Let op dat de GD library op de server actief moet zijn.
Amazon picture load
TLS Handshake and Connection Setup: 100 ms
s3-eu-west-1.amazonaws.com - ping time 27 ms
server processing - 10 ms
Network tranfer speed 0.8 sec/KB
Cache of rentman API
all getc api calls are cached. The cache has a predefiend TTL $cache_ttl = 3600
the raw return value of the API is json encoded written to file.
There is code to delete all the cache (not tested yet)
Images cache
Images are cached as images (binaries) and are re-rendered at a lower resolution.
Images are cached under the name [equipment-id]-[image-modified].jpg
When equipment ids are not valid any more, there could be orphan images in the cache.
When the image is updated for an exisitng equipment id, there will be two files with the same equipment-id,
the one with the lowest image-modified timestamp can be deleted.
There is no code for this yet.
Performance test
-
- Orginele laadtijd: 5,4 seconden (1.8 MB plaatjes)
- Img cache v no cache 2600/3500 ms - 2 ms
- API call cache v no cache: 1700 ms - 2 ms
- na cache plaatjes/ api calls 2 ms
Rechten op directory (Ubuntu)
// create new group
sudo groupadd shared-www
// add users to group
sudo usermod -aG shared-www www-data
sudo usermod -aG shared-www max
// set shared group as owner
sudo chown -R :shared-www /path/to/shared-directory
sudo chmod -R 770 /path/to/shared-directory
// Ensure that any new files or directories created inside the shared directory inherit the group ownership of shared-group.
sudo chmod g+s /path/to/shared-directory
Fotobooth
Code voor Arduino (1ste versie)
#include <Keyboard.h>
const int inputPin = 12; // Pin connected to Nayax output
int lastState = LOW; // Previous state of the input pin
int count;
void setup() {
pinMode(inputPin, INPUT); // Set the pin as input
digitalWrite(LED_BUILTIN, HIGH); // initialize digital pin LED_BUILTIN as an output.
pinMode(LED_BUILTIN, OUTPUT); // turn the LED on (HIGH is the voltage level)
Keyboard.begin(); // Initialize the keyboard emulation
Serial.begin(9600);
count = 0;
delay(5000); // Delay to give time to connect the Arduino
digitalWrite(LED_BUILTIN, LOW); // turn the LED off by making the voltage LOW
}
void loop() {
int currentState = digitalRead(inputPin); // Read the Nayax signal
// Check if the state has changed from LOW to HIGH
if (currentState == HIGH && lastState == LOW) {
count++;
Serial.print("Pulse: ");
Serial.println(count);
digitalWrite(LED_BUILTIN, HIGH); // turn the LED on (HIGH is the voltage level)
Keyboard.press(0x20); // press space
delay(100);
Keyboard.releaseAll(); // Release the key
delay(1000); // Debounce delay to prevent multiple triggers
digitalWrite(LED_BUILTIN, LOW); // turn the LED off by making the voltage LOW
}
lastState = currentState; // Update the last state
}
Aansluitschema
Arduino 5v - Optocoupler +
Arduino GRND - weerstand 10K - pin12
Arduino Pin 12 - GRND Optocoupler
Optocoupler primair 12 volt via 10K weerstand
Pin 12 wordt met een pull down weerstand laag (0) gehouden. De (1) is 5V wordt via de optocoupler verbonden aan pin12 als er een signaal via de optocoupler binnenkomt. De stroom die door de 10K weerstand loopt als de optocoupler 'aan' staat, is 0.5 mA (2.5 mW).
De 10K weerstand voor de 12v primaire kant v/d optocoupler is proefondervindelijk vastgesteld. Bij 12v en 10K weerstand staat er 5v op de primare ingang en werkt de optocoupler goed. Meer spanning (lager weerstand kan) volgens de specs maar is onnodig.
Explanation primary side optocoupler
Primary (Input) Side: Connecting to Nayax
- Calculate the Current-Limiting Resistor:
- LED forward voltage (V_F): ~1.4V.
- Desired LED current (I_F): 10mA (0.01A).
- Voltage drop across the resistor: 24V - 1.4V = 22.6V.
- Resistor value: R = 22.6V / 0.01A = 2260Ω → Use a 2.2kΩ resistor.
- Add a Pull-Up Resistor:
- When the pulse wire floats (inactive), we need the LED off. A pull-up resistor on the cathode (Pin 2) to +24V ensures no current flows when the wire is floating.
- Use a 10kΩ pull-up resistor. This keeps the cathode at 24V when floating, preventing current through the LED.
- Wiring the Primary Side:
- +24V Supply: Connect to one end of the 2.2kΩ resistor.
- 2.2kΩ Resistor to Pin 1 (Anode): Connect the other end to Pin 1 of the PC817.
- Pin 2 (Cathode): Connect to the green Pulse 1 wire and one end of the 10kΩ pull-up resistor.
- 10kΩ Pull-Up Resistor: Connect the other end to +24V.
- Nayax Ground: Ensure the 24V supply ground is the same as the Nayax ground.
- Behavior:
- Inactive (Floating): The 10kΩ pull-up keeps Pin 2 at 24V, same as Pin 1’s effective voltage (24V - 1.4V drop across resistor), so no current flows (LED off).
- Active (Low, 0V): The pulse wire pulls Pin 2 to ground, creating a 22.6V drop across the resistor and LED, driving ~10mA through the LED (on).
Explanation of Behavior
- Inactive (Floating): The pull-up resistor ties Pin 2 to 24V. Pin 1 is at ~22.6V (24V minus the resistor drop if any current flowed), but since Pin 2 is also at 24V, there’s no voltage difference across the LED—no current, no light.
- Active (Low, 0V): The Nayax pulls the green wire (Pin 2) to ground. Now, Pin 1 is at 24V through the resistor, and Pin 2 is at 0V. The LED sees ~1.4V, the resistor drops the remaining 22.6V, and ~10mA flows—LED turns on.
New code (v2) to ignore small pulses
#include <Keyboard.h>
const int inputPin = 12; // Pin connected to Nayax output
int lastState = LOW;
int count;
unsigned long lastPulseTime = 0;
const unsigned long debounceDelay = 1000; // Minimum time between pulses (ms)
const unsigned long pulseValidationTime = 10; // Must stay HIGH at least this long (ms)
void setup() {
pinMode(inputPin, INPUT);
pinMode(LED_BUILTIN, OUTPUT);
digitalWrite(LED_BUILTIN, HIGH);
Keyboard.begin();
Serial.begin(9600);
count = 0;
delay(5000);
digitalWrite(LED_BUILTIN, LOW);
}
void loop() {
int currentState = digitalRead(inputPin);
if (currentState == HIGH && lastState == LOW) {
// Wait a bit to check if the signal is stable
delay(pulseValidationTime);
if (digitalRead(inputPin) == HIGH) {
unsigned long now = millis();
if (now - lastPulseTime > debounceDelay) {
count++;
Serial.print("Pulse: ");
Serial.println(count);
digitalWrite(LED_BUILTIN, HIGH);
Keyboard.press(0x20);
delay(100);
Keyboard.releaseAll();
digitalWrite(LED_BUILTIN, LOW);
lastPulseTime = now;
}
}
}
lastState = currentState;
}
Sets and availabilty
"Database prompt"
The following data is to be used as input.
- Table equipment with columns id and current_quantity
This data is equipment that is available for renting. Per equipment id (item) the current _quantity is the stock available. - Table equipmentsetscontent with columns id, quantity and parent_equipment.
This defines a relation between equipment. Some equipment is part of a parent equipment. Therefor the availablity of the parent depends on the quantity needed of the childs. The parent-child relation is an hierachical tree. - Table plannedequipment with columns id, quantity, planperiod_start and planperiod_end.
This defines the number of equipment items, rented and not availbale during the period planperiod_start unitl planperiod_end (bothe dates are inclusive).
Example
equipment
id | quantity |
1 | - |
2 | 14 |
3 | 6 |
4 | 40 |
equipmentsetscontent
id | quantity | parent_equipment |
3 | 2 | 1 |
4 | 4 | 1 |
2 | 6 | 1 |
Because item 1 consists of 2 x item3 + 4 x item 4 + 6 x item 2. Based on the quatity available (from the equipment table), we can build 3 sets with item 3, 10 sets with item 4, and 2 sets with item 2. The total sets (item with id 1) availbale for renting out will be 2.
plannedequipment
id | quantity | planperiode_start | planperiod_end |
2 | 2 | 2026-05-01 | 2026-05-10 |
2 | 1 | 2026-05-09 | 2026-05-12 |
We have two rentals for item 2. Th efirst rental decreases the quantity from item 2 from 14 to 12. Still 2 sets with id can be construncted so the avialability of the set wiht item id 1 will not be affected and stay 2.
From the 9th till the 12th we have one additional rental. This results in an decrease of stock of 3 in the period from the 9th till the 10th. When 3 items with id 2 are rented out, the remaining stock will become 11. This decreases the availabilyt of set (item) 1 to 1.
Output
I need to determine the availability of all ultimate parents per year and month.
I have a table reservations with the columns equipment_id, current_quantity, year, month, availability.
I want to upsert a row for any ultimate parent: the current_quatity is the default quantity when nothng is rentend out. And the availablity is an array of numbers. Each number represents the availablity of the set (ultimate parent) on that day.
[19, 19, 23, 21, 11, 10, 11, 11, 11, 13, 15, 15, 11, 11, 11, 11, 23, 23, 22, 8, 9, 9, 9, 13, 13, 13, 13, 23, 23, 11]
This means that on the first 2 days of the monthe we have 19 items (sets) and the 3rd 23, etc. etc.
What I need
Create a python script that creates the upserts for the table reservations for the next N months (default N = 12).
Create a method or more if needed in a class.
In order to acces the database you can use:
self.cursor.execute(...), self.conn.commit(), self.cursor.fetchall(), etc.
For logging use:
log(self, message: str, progress: bool = False, warn: bool = False) -> None:
Do you have enough infomration or do you need additional exmples or clarification?
Please ask clarification when needed, otherwise give me a strategy on how to proceed.
--
✅ I have enough information to outline a strategy to proceed.
🧠 Summary of What We Need to Do
-
Identify all ultimate parents from the
equipmentsetscontent
table (i.e., parents that are not used as a child themselves). -
Build a dependency tree: For each ultimate parent, find all the components and their required quantities (recursively).
-
Track dynamic availability per day:
-
Start with
current_quantity
fromequipment
for each item. -
Apply reservations from
plannedequipment
to reduce availability on affected days.
-
-
For each ultimate parent and for each day, calculate:
-
The number of complete sets that can be built based on the most limiting child.
-
-
Summarize per month:
-
Create an array for each month with the daily availability (e.g., 30-element array for June).
-
-
Upsert into the
reservations
table:-
If a row for the
(equipment_id, year, month)
exists, update it; otherwise, insert a new one.
-
🔧 Classes & Methods Proposal
We will define a class, e.g., AvailabilityCalculator
, with the following responsibilities:
1. get_ultimate_parents()
Returns a list of ultimate parent equipment IDs.
2. build_dependency_tree(parent_id)
Returns a Dict[item_id -> required_quantity]
for a given parent (resolved recursively).
3. get_daily_availability_for_item(item_id, year, month)
Returns a list of integers: available quantity per day of the given item after applying reservations.
4. calculate_set_availability(dependency_dict, daily_availabilities)
Takes the dependency definition and child item availabilities and calculates how many sets can be constructed per day.
5. upsert_reservation(equipment_id, year, month, current_quantity, availability_array)
Performs an insert or update into the reservations
table.
6. generate_reservations(months_ahead: int = 12)
Main method to loop through upcoming months and ultimate parents, compute availability, and update the database.
⚠️ Edge Cases to Handle
-
Dates in
plannedequipment
are inclusive. -
Leap years and month-end dates.
-
Recursive child resolution (tree structure).
-
Cycles in the hierarchy (validate and skip with warning if detected).
-
Gaps in days with no reservations should default to
current_quantity
.
✅ Next Step
Would you like me to now:
-
Implement the skeleton for this class and the
generate_reservations()
method? -
Or give you the full implementation?
If you’re ready, I’ll start with the full implementation in parts to keep it manageable and clear.
Code
Here is the full Python implementation of the AvailabilityCalculator
from datetime import datetime, timedelta
import calendar
import json
from collections import defaultdict
class AvailabilityCalculator:
def __init__(self, conn):
self.conn = conn
self.cursor = conn.cursor()
def log(self, message: str, progress: bool = False, warn: bool = False) -> None:
prefix = "[WARN]" if warn else "[INFO]" if progress else "[LOG]"
print(f"{prefix} {message}")
def get_ultimate_parents(self):
self.cursor.execute("SELECT DISTINCT parent_equipment FROM equipmentsetscontent")
parents = set(row[0] for row in self.cursor.fetchall())
self.cursor.execute("SELECT DISTINCT id FROM equipmentsetscontent")
children = set(row[0] for row in self.cursor.fetchall())
# Parents that are not children themselves
ultimate_parents = parents - children
return list(ultimate_parents)
def build_dependency_tree(self, parent_id):
stack = [(parent_id, 1)]
dependencies = defaultdict(int)
visited = set()
while stack:
current, factor = stack.pop()
self.cursor.execute(
"SELECT id, quantity FROM equipmentsetscontent WHERE parent_equipment = ?",
(current,))
rows = self.cursor.fetchall()
if not rows:
dependencies[current] += factor
else:
for child_id, qty in rows:
if (current, child_id) in visited:
self.log(f"Cycle detected: {current} -> {child_id}", warn=True)
continue
visited.add((current, child_id))
stack.append((child_id, factor * qty))
return dict(dependencies)
def get_reserved_quantities(self, item_id, year, month):
_, days_in_month = calendar.monthrange(year, month)
daily_reserved = [0] * days_in_month
start_of_month = datetime(year, month, 1)
end_of_month = start_of_month + timedelta(days=days_in_month - 1)
self.cursor.execute("""
SELECT quantity, planperiod_start, planperiod_end FROM plannedequipment
WHERE id = ?
""", (item_id,))
for quantity, start_str, end_str in self.cursor.fetchall():
start = datetime.strptime(start_str, "%Y-%m-%d")
end = datetime.strptime(end_str, "%Y-%m-%d")
for i in range(days_in_month):
day = start_of_month + timedelta(days=i)
if start <= day <= end:
daily_reserved[i] += quantity
return daily_reserved
def get_daily_availability_for_item(self, item_id, year, month):
self.cursor.execute("SELECT current_quantity FROM equipment WHERE id = ?", (item_id,))
result = self.cursor.fetchone()
if not result:
return []
base_quantity = result[0]
reserved = self.get_reserved_quantities(item_id, year, month)
return [max(0, base_quantity - reserved[i]) for i in range(len(reserved))]
def calculate_set_availability(self, dependency_dict, year, month):
_, days_in_month = calendar.monthrange(year, month)
availabilities = {}
for item_id in dependency_dict:
availabilities[item_id] = self.get_daily_availability_for_item(item_id, year, month)
result = []
for day in range(days_in_month):
set_count_per_day = [
availabilities[item_id][day] // dependency_dict[item_id]
for item_id in dependency_dict
]
result.append(min(set_count_per_day) if set_count_per_day else 0)
return result
def upsert_reservation(self, equipment_id, year, month, current_quantity, availability_array):
self.cursor.execute("""
SELECT COUNT(*) FROM reservations WHERE equipment_id = ? AND year = ? AND month = ?
""", (equipment_id, year, month))
exists = self.cursor.fetchone()[0] > 0
availability_json = json.dumps(availability_array)
if exists:
self.cursor.execute("""
UPDATE reservations
SET current_quantity = ?, availability = ?
WHERE equipment_id = ? AND year = ? AND month = ?
""", (current_quantity, availability_json, equipment_id, year, month))
else:
self.cursor.execute("""
INSERT INTO reservations (equipment_id, current_quantity, year, month, availability)
VALUES (?, ?, ?, ?, ?)
""", (equipment_id, current_quantity, year, month, availability_json))
self.conn.commit()
def generate_reservations(self, months_ahead: int = 12):
today = datetime.today().replace(day=1)
self.cursor.execute("""
CREATE TABLE IF NOT EXISTS reservations (
equipment_id INT NOT NULL,
current_quantity INT NOT NULL,
year INT NOT NULL,
month INT NOT NULL,
availability JSON NOT NULL,
PRIMARY KEY (equipment_id, year, month)
)
""")
ultimate_parents = self.get_ultimate_parents()
for month_offset in range(months_ahead + 1):
date = today + timedelta(days=32 * month_offset)
year, month = date.year, date.month
for parent_id in ultimate_parents:
dependencies = self.build_dependency_tree(parent_id)
availability_array = self.calculate_set_availability(dependencies, year, month)
default_quantity = min(
self.get_daily_availability_for_item(parent_id, year, month) or [0]
)
self.upsert_reservation(parent_id, year, month, default_quantity, availability_array)
self.log(f"Upserted reservation for {parent_id} in {year}-{month:02}", progress=True)
self.log("✅ All reservations generated.", progress=True)
----
POC PHP Implementation Sets Availablity
Step 1
Reformat plannedequipment into reservations per date
Processes equipment reservation data.
- Reads reservation records from JSON files in the "plannedequipment" directory.
- Reads equipment properties from JSON files in the "equipment" directory.
Calculates daily reservation totals for each equipment item.
- Covers a date range: current month plus a configurable number of months (default 9).
- Organizes data into a nested structure: year → month → day.
Computes daily available quantity: current quantity minus reservations.
Outputs results to JSON files in the "reservationperdate" directory.
Offers optional features:
- Suppression of days with zero reservations.
- Prevention of file creation when no reservation data exists.
Script
# createReservationperdatev2.py
import os
import json
from datetime import datetime, date, timedelta
import calendar
# Parameters
SUPPRESS_ZERO = True # If True, skip storing day nodes with zero reservations.
SUPPRESS_EMPTY_FILE = True # If True, do not create an output file if the "dates" part is empty.
ADDITIONAL_MONTHS = 9 # Number of additional months to process (beyond the current month)
# Define the directories
plannedequipment_dir = 'plannedequipment'
equipment_dir = 'equipment'
output_dir = 'reservationperdate'
os.makedirs(output_dir, exist_ok=True)
# Define the date range: from the first day of this month to the last day of the month ADDITIONAL_MONTHS later.
today = date.today()
start_date = date(today.year, today.month, 1)
end_month = today.month + ADDITIONAL_MONTHS
end_year = today.year + (end_month - 1) // 12
end_month = ((end_month - 1) % 12) + 1
last_day = calendar.monthrange(end_year, end_month)[1]
end_date = date(end_year, end_month, last_day)
# Define the list of equipment property keys to copy from the equipment JSON files.
equipment_keys = ["is_combination", "is_physical", "current_quantity"]
def add_day(nested_dates, current_date):
"""Ensure that nested_dates[year][month][day] exists and initialize it."""
year = str(current_date.year)
month = f"{current_date.month:02d}"
day = f"{current_date.day:02d}"
if year not in nested_dates:
nested_dates[year] = {}
if month not in nested_dates[year]:
nested_dates[year][month] = {}
if day not in nested_dates[year][month]:
nested_dates[year][month][day] = {"reservations": 0, "available": None}
def remove_empty_containers(nested_dates):
"""Remove empty month and year nodes if they have no day entries."""
years_to_remove = []
for year, months in nested_dates.items():
months_to_remove = []
for month, days in months.items():
if not days: # If month dictionary is empty
months_to_remove.append(month)
for month in months_to_remove:
del nested_dates[year][month]
if not nested_dates[year]:
years_to_remove.append(year)
for year in years_to_remove:
del nested_dates[year]
# Process each JSON file in the plannedequipment directory.
for filename in os.listdir(plannedequipment_dir):
if not filename.endswith('.json'):
continue
# Build full paths for plannedequipment, equipment and output files.
plan_file_path = os.path.join(plannedequipment_dir, filename)
equipment_file_path = os.path.join(equipment_dir, filename)
output_path = os.path.join(output_dir, filename)
# Load the reservation data from the plannedequipment file.
with open(plan_file_path, 'r', encoding='utf-8') as infile:
plan_data = json.load(infile)
# Initialize the nested dates dictionary.
nested_dates = {}
current_date = start_date
while current_date <= end_date:
add_day(nested_dates, current_date)
current_date += timedelta(days=1)
# Process each reservation record.
for record in plan_data.get('data', []):
start_str = record.get('planperiod_start')
end_str = record.get('planperiod_end')
if not start_str or not end_str:
continue
record_start = datetime.fromisoformat(start_str).date()
record_end = datetime.fromisoformat(end_str).date()
# Clamp the reservation period to our target range.
period_start = max(record_start, start_date)
period_end = min(record_end, end_date)
if period_start > period_end:
continue
quantity = record.get('quantity', 0)
current = period_start
while current <= period_end:
year = str(current.year)
month = f"{current.month:02d}"
day = f"{current.day:02d}"
if year in nested_dates and month in nested_dates[year] and day in nested_dates[year][month]:
nested_dates[year][month][day]["reservations"] += quantity
current += timedelta(days=1)
# Initialize equipment properties.
item_properties = {}
current_quantity = 0
# Read the corresponding equipment file.
if os.path.exists(equipment_file_path):
with open(equipment_file_path, 'r', encoding='utf-8') as eq_file:
equipment_data = json.load(eq_file)
equipment_item = equipment_data.get('data', {})
# Copy the desired properties.
for key in equipment_keys:
item_properties[key] = equipment_item.get(key, None)
current_quantity = equipment_item.get('current_quantity', 0)
else:
print(f"Error: Equipment file '{filename}' not found in '{equipment_dir}'. Assuming current_quantity = 0 and default item properties.")
for key in equipment_keys:
item_properties[key] = None
# Update each day with the available quantity and optionally suppress days with zero reservations.
for year in list(nested_dates.keys()):
for month in list(nested_dates[year].keys()):
for day in list(nested_dates[year][month].keys()):
day_data = nested_dates[year][month][day]
reservations = day_data["reservations"]
day_data["available"] = current_quantity - reservations
if SUPPRESS_ZERO and reservations == 0:
del nested_dates[year][month][day]
# If a month becomes empty after removing days, remove the month.
if not nested_dates[year][month]:
del nested_dates[year][month]
# If a year becomes empty after removing months, remove the year.
if not nested_dates[year]:
del nested_dates[year]
# If SUPPRESS_EMPTY_FILE is True and the nested dates dictionary is empty,
# remove the output file if it exists and skip writing.
if SUPPRESS_EMPTY_FILE and not nested_dates:
if os.path.exists(output_path):
os.remove(output_path)
print(f"Removed existing file {output_path} because there are no reservations.")
continue
# Combine the item properties and the nested date reservation data.
output_data = {
"item": item_properties,
"dates": nested_dates
}
# Write the result to the output directory.
with open(output_path, 'w', encoding='utf-8') as outfile:
json.dump(output_data, outfile, indent=2)
print("Reservation, availability, and item data have been processed and saved.")
Step 2
Download the set hiarchy from the API
(takes long, build in delays to prevent throttling)
# getSets.py
import json
import time
import requests
import sys
# Configuration variables (adjust these as needed)
BASE_URL = "https://api.rentman.net/" # Replace with your actual base URL
FILTER_ON_TAGS = False # Set to True if you want to apply tag filtering
API_TOKEN = "abc" # Replace with your actual API token
def log_message(message, force=False):
"""Logs a message. You can modify this to log to a file if needed."""
sys.stderr.write(message + "\n")
if force:
print(message)
def count_api_usage():
"""Stub function for counting API usage."""
# Extend this function to track API usage if needed.
pass
def filter_array_by_selections(items, selections):
"""
Filters an array of dictionaries based on the presence of certain keys with truthy values.
:param items: List of dictionaries to filter.
:param selections: List of keys to check.
:return: Filtered list of dictionaries.
"""
filtered = []
for item in items:
for sel in selections:
if item.get(sel):
filtered.append(item)
break
return filtered
def make_request(method, endpoint, data=None):
"""
Makes an HTTP request using the requests library.
:param method: HTTP method ('GET' or 'POST').
:param endpoint: API endpoint (appended to BASE_URL).
:param data: Dictionary of data or query parameters.
:return: Parsed JSON response or None if error occurs.
"""
if data is None:
data = {}
url = BASE_URL + endpoint
headers = {
"Authorization": f"Bearer {API_TOKEN}",
"Content-Type": "application/json"
}
try:
if method.upper() == 'POST':
response = requests.post(url, headers=headers, json=data)
elif method.upper() == 'GET':
response = requests.get(url, headers=headers, params=data)
else:
log_message(f"Unsupported HTTP method: {method}")
return None
except requests.RequestException as e:
log_message(f"Request exception: {e}")
return None
if not response.ok:
log_message(f"HTTP error: {response.status_code} | Response Body: {response.text}")
try:
return response.json()
except json.JSONDecodeError:
log_message("Error decoding JSON response.")
return None
def get_request(endpoint, params=None):
"""
Performs a GET request and counts API usage.
:param endpoint: API endpoint.
:param params: Query parameters as a dictionary.
:return: Parsed JSON response.
"""
count_api_usage()
return make_request('GET', endpoint, params)
def equipmentsetscontent(filter_on_tags=FILTER_ON_TAGS):
"""
Retrieves equipment sets content by fetching data in batches.
:param filter_on_tags: If True, applies tag filtering.
:return: Complete list of items.
"""
all_items = []
offset = 0
batch_size = 300
total_fetched = 0
while True:
params = {'offset': offset, 'limit': batch_size}
response = get_request('equipmentsetscontent', params)
if not response or 'data' not in response:
print("<pre><h1>Error in API Request</h1>")
print(response)
sys.exit(1)
items = response['data']
all_items.extend(items)
total_fetched += len(items)
# If fewer than batch_size items are returned, no more records are available.
if len(items) < batch_size:
break
offset += batch_size
# If 3000 records have been fetched, pause for 1 second to avoid rate limits.
if total_fetched >= 3000:
log_message("Pausing for 1 second to avoid rate limits...", True)
time.sleep(1)
total_fetched = 0 # Reset the counter after the pause
if filter_on_tags:
all_items = filter_array_by_selections(all_items, ['hoofditem', 'set'])
return all_items
# Main execution
if __name__ == "__main__":
complete_data = equipmentsetscontent()
print(json.dumps(complete_data, indent=4))
Step 3,
Convert the output from step 2 into sets-childs format (parrent with list of childs).
For each entry, the script removes the "/equipment/" prefix from both the parent and child equipment values using the helper function.
For every entry, after stripping the prefixes, a new dictionary is created for the child item (containing the stripped child equipment and its quantity). This child dictionary is then appended to the list associated with the corresponding parent key in the dictionary.
Once all entries are processed, the dictionary keys (i.e., the parent equipment numbers) are sorted in ascending order to organize the final output.
Prompt
Write a Python script that performs the following tasks:
- Define variables at the top of the script for an input file and an output file. The input file is "sets/sets.json" and the output file is "sets/sets-childs.json".
- Read the JSON data from the input file. The file contains a list of objects, each with fields such as "parent_equipment", "equipment", and "quantity". The "parent_equipment" and "equipment" fields are strings that start with the prefix "/equipment/".
- Create a helper function to remove the "/equipment/" prefix from these strings.
- Group the objects by the numeric part of the parent equipment (after stripping the prefix). For each group, store a list of child items, where each child is represented by a dictionary containing the stripped "equipment" number and its "quantity".
- Sort the groups by their parent equipment keys in ascending numerical order.
- Write the resulting grouped and sorted data to the output file in JSON format with proper indentation.
- Please generate the complete Python code to achieve this.
# set-combined.py
import json
from collections import defaultdict
# Define file paths
input_file = "sets/sets.json"
output_file = "sets/sets-childs.json"
def strip_prefix(equipment_str):
prefix = "/equipment/"
return equipment_str[len(prefix):] if equipment_str.startswith(prefix) else equipment_str
# Load the input JSON file
with open(input_file, "r") as infile:
data = json.load(infile)
# Group child items by parent equipment (after stripping the prefix)
sets_dict = defaultdict(list)
for entry in data:
parent = strip_prefix(entry["parent_equipment"])
child_item = {
"equipment": strip_prefix(entry["equipment"]),
"quantity": entry["quantity"]
}
sets_dict[parent].append(child_item)
# Sort the dictionary by parent keys in ascending numerical order
sorted_sets_dict = {k: sets_dict[k] for k in sorted(sets_dict.keys(), key=lambda x: int(x))}
# Write the output JSON file with sorted parent keys
with open(output_file, "w") as outfile:
json.dump(sorted_sets_dict, outfile, indent=4)
print("The new JSON file has been created successfully.")
Step 4
Calculate set availability into reservationperdate-sets folder.
Prompt: prompt to create set reservations.docx
#!/usr/bin/env python3
# setReservationperdate.py
import os
import json
import math
from collections import defaultdict
# Directories for input and output files
SETS_FILE = os.path.join("sets", "sets-childs.json")
RESERVATION_DIR = "reservationperdate"
OUTPUT_DIR = "reservationperdate-sets"
# Ensure output directory exists
os.makedirs(OUTPUT_DIR, exist_ok=True)
def load_json(filepath):
with open(filepath, "r") as f:
return json.load(f)
def get_child_reservation(child_id):
"""
Given a child equipment id, load its reservation file.
Assumes filename is child_id + ".json" in the RESERVATION_DIR.
Returns a dict with keys "item" and optionally "dates".
"""
filepath = os.path.join(RESERVATION_DIR, f"{child_id}.json")
if os.path.exists(filepath):
return load_json(filepath)
else:
raise FileNotFoundError(f"Reservation file for equipment {child_id} not found at {filepath}")
def extract_date_availability(reservation):
"""
Extract a mapping from (year, month, day) tuple to available quantity.
If a reservation file has no "dates" node, returns an empty dict.
"""
date_avail = {}
dates = reservation.get("dates", {})
for year, months in dates.items():
for month, days in months.items():
for day, data in days.items():
date_avail[(year, month, day)] = data.get("available", 0)
return date_avail
def nested_date_structure(date_dict):
"""
Given a mapping with keys (year, month, day) and value (availability),
build a nested dictionary: year -> month -> day -> {reservations: 0, available: value}
"""
nested = defaultdict(lambda: defaultdict(dict))
for (year, month, day), avail in date_dict.items():
nested[year][month][day] = {"reservations": 0, "available": avail}
return {year: dict(months) for year, months in nested.items()}
def main():
# Load the sets definitions
sets_def = load_json(SETS_FILE)
# Process each set defined in sets-childs.json
for set_id, children in sets_def.items():
child_infos = [] # List of tuples: (child_id, required_quantity, full_stock, date_availability mapping)
for child in children:
child_id = child["equipment"]
req_qty = child["quantity"]
try:
reservation = get_child_reservation(child_id)
except FileNotFoundError as e:
print(e)
continue # Skip this child if its reservation file is missing
full_stock = reservation.get("item", {}).get("current_quantity", 0)
date_avail = extract_date_availability(reservation)
child_infos.append((child_id, req_qty, full_stock, date_avail))
# Calculate the full availability (without date restrictions)
available_sets_full = math.inf
for (_, req_qty, full_stock, _) in child_infos:
child_full_sets = full_stock // req_qty
available_sets_full = min(available_sets_full, child_full_sets)
if available_sets_full == math.inf:
available_sets_full = 0
# Gather the union of all dates from the children
union_dates = set()
for (_, _, full_stock, date_avail) in child_infos:
union_dates.update(date_avail.keys())
# Compute availability per date from the union of dates
date_results = {}
for date in union_dates:
avail_for_date = math.inf
for (_, req_qty, full_stock, date_avail) in child_infos:
# Use date-specific available if provided, otherwise use full stock
child_avail = date_avail.get(date, full_stock)
child_sets = child_avail // req_qty
avail_for_date = min(avail_for_date, child_sets)
date_results[date] = avail_for_date
# Only include dates where the available set count is lower than full availability
filtered_dates = {date: avail for date, avail in date_results.items() if avail < available_sets_full}
# Build output JSON structure following the reservation file structure
output = {}
output["item"] = {
"is_combination": True,
"current_quantity": available_sets_full
}
if filtered_dates:
output["dates"] = nested_date_structure(filtered_dates)
# Write the output JSON file for the set to the OUTPUT_DIR
out_filepath = os.path.join(OUTPUT_DIR, f"{set_id}.json")
with open(out_filepath, "w") as outfile:
json.dump(output, outfile, indent=4)
print(f"Set {set_id}: full availability {available_sets_full} written to {out_filepath}")
if __name__ == "__main__":
main()
Add available to parents
Prompt: Calculate Parent Availability from Equipment JSON
Context
You are given a JSON file representing a list of equipment items. Each item has:
-
A
displayname
andparent_equipment_number
-
A list of
children
, and each child contains:-
current_quantity
: how many are in stock -
quantity
: how many are needed to build 1 parent -
usage_by_day
: reservations per day (structured asYYYY-MM
→ day → reserved quantity)
-
Task
Write a PHP function that:
-
Input: Takes an array decoded from this JSON structure.
-
Output: Adds two new keys to each parent item, inserted directly after the
displayname
key:-
'available'
: the maximum number of parents that can be assembled based on the current stock and child requirements.-
For each child, calculate:
floor(current_quantity / quantity)
-
The minimum across all children determines the parent’s availability.
-
-
'available_by_day'
: a structure similar tousage_by_day
, but showing the availability of the parent on specific days, accounting for child reservations.-
For each day, subtract reserved quantity from current stock per child.
-
Then divide by the number of that child required per parent.
-
Again, take the minimum across children for that day.
-
If that value equals the overall
'available'
, omit the day. -
If it drops below
0
, set it to0
(administrative error handling). -
Output structure:
-
-
Ensure the final order of keys for each parent is:
-
parent_equipment_number
-
displayname
-
available
-
available_by_day
-
children
-
-
Function name: addParentAvailability
Code generated
/**
* Enhances parent equipment data with:
* 1. An overall 'available' count based on current stock of children.
* 2. A per-day 'available_by_day' structure that shows date-based limitations.
*
* 'available' shows the max number of parents that can be built given stock.
* 'available_by_day' shows reductions on specific dates due to reservations
* on child equipment. Dates with full availability are omitted. Negative availability
* (due to administrative error) is corrected to 0.
*
* @param array $equipmentSet The array of parent equipment items with their children.
* @return array The modified array with the new availability keys added.
*/
function addParentAvailability(array $equipmentSet): array {
foreach ($equipmentSet as &$parent) {
$minAvailable = PHP_INT_MAX;
$dailyAvailability = [];
foreach ($parent['children'] as $child) {
$neededPerParent = $child['quantity'];
$childAvailable = floor($child['current_quantity'] / $neededPerParent);
$minAvailable = min($minAvailable, $childAvailable);
if (!isset($child['usage_by_day'])) continue;
foreach ($child['usage_by_day'] as $ym => $days) {
foreach ($days as $day => $usedQty) {
$available = floor(($child['current_quantity'] - $usedQty) / $neededPerParent);
if ($available < 0) $available = 0;
// If not yet set, initialize with high value
if (!isset($dailyAvailability[$ym][$day])) {
$dailyAvailability[$ym][$day] = $available;
} else {
$dailyAvailability[$ym][$day] = min($dailyAvailability[$ym][$day], $available);
}
}
}
}
// Clean up: remove days that match the full availability
foreach ($dailyAvailability as $ym => &$days) {
foreach ($days as $day => $value) {
if ($value >= $minAvailable) {
unset($days[$day]);
}
}
if (empty($days)) {
unset($dailyAvailability[$ym]);
}
}
// Rebuild parent structure with displayname -> available -> available_by_day -> rest
$newParent = [];
foreach ($parent as $key => $value) {
$newParent[$key] = $value;
if ($key === 'displayname') {
$newParent['available'] = $minAvailable;
$newParent['available_by_day'] = $dailyAvailability;
}
}
$parent = $newParent;
}
return $equipmentSet;
}
Correction (last step)
📦 Prompt: Calculate Parent Availability from Equipment JSON (Full Version with Bug Fix)
## 🧾 Context
You are given a JSON file representing a list of equipment items. Each item represents a **parent item**, and contains:
- A `displayname` and `parent_equipment_number`.
- A list of `children`, each with:
- `current_quantity`: number in stock.
- `quantity`: number required to build **1 parent**.
- `usage_by_day`: reservations per date in the structure:
```json
{
"2025-06": {
"13": 2,
"14": 3
}
}
```
---
## 🎯 Goal
Write a PHP function that does the following:
---
### ✅ 1. Input
The function receives a decoded JSON structure (i.e., a PHP array), containing an array of parent items, each with `children`.
---
### ✅ 2. Calculates `available`
For each parent item:
- Determine how many complete parents can be built from the current stock of all required children.
- For each child:
```php
floor(current_quantity / quantity_required_per_parent)
```
- The **minimum** result across all children is the number of complete parent items that can be built.
- Add this number as a new key:
```php
"available": <int>
```
- Insert it **immediately after `displayname`** in the parent’s object.
---
### ✅ 3. Calculates `available_by_day`
Also add a new key **after** `available`, named:
```json
"available_by_day": {
"YYYY-MM": {
"DD": <int>
}
}
```
This shows for which dates the availability of parents is **lower than** the full availability due to **child reservations**.
#### Logic:
- Loop through **all dates** that appear in any child’s `usage_by_day`.
- For each date:
- For each child:
- Subtract reserved amount on that date from current_quantity.
- Compute:
```php
floor((current_quantity - reserved) / quantity_required)
```
- If the result < 0, treat it as 0.
- The **minimum** across all children determines the **parent’s availability for that date**.
- If that value is **less than** the overall `available`, include it in `available_by_day`.
- If the value is **equal to or greater than** `available`, skip it (don't output it).
- If no days are limited, the key `available_by_day` may be an empty object.
---
### ✅ 4. Output Structure
Each parent item will be rewritten to insert:
- `available`
- `available_by_day`
…right after `displayname`.
#### Example:
```json
{
"parent_equipment_number": 1234,
"displayname": "Cool Parent",
"available": 2,
"available_by_day": {
"2025-06": {
"13": 1,
"14": 0
}
},
"children": [ ... ]
}
```
---
### ✅ 5. Edge Cases
- If no usage data is available for any child, the daily availability is not included.
- If calculated availability is negative on any date, set it to `0` (administrative error).
- Never allow availability on any date to exceed the total `available`.
---
This prompt includes all requirements, handles bugs related to overestimation, and ensures robust availability calculations per parent.
Code:
/**
* Enhances each parent item with:
* - 'available': max number of parents that can be built from current stock.
* - 'available_by_day': date-specific limits due to reservations in children.
*
* For 'available_by_day', only include dates where the availability is lower than 'available'.
* Values below 0 are treated as 0.
* Dates with full availability are skipped.
*
* @param array $equipmentSet Decoded JSON with parent items and their children.
* @return array Modified data with availability info inserted.
*/
function addParentAvailability(array $equipmentSet): array {
foreach ($equipmentSet as &$parent) {
$minAvailable = PHP_INT_MAX;
$dailyAvailability = [];
$allDates = [];
// Step 1: Compute total availability and gather all relevant dates
foreach ($parent['children'] as $child) {
$neededPerParent = $child['quantity'];
$childAvailable = floor($child['current_quantity'] / $neededPerParent);
$minAvailable = min($minAvailable, $childAvailable);
if (!isset($child['usage_by_day'])) continue;
foreach ($child['usage_by_day'] as $ym => $days) {
foreach ($days as $day => $_) {
$allDates[$ym][$day] = true;
}
}
}
// Step 2: For every known date, compute availability from all children
foreach ($allDates as $ym => $days) {
foreach ($days as $day => $_) {
$minForDay = PHP_INT_MAX;
foreach ($parent['children'] as $child) {
$needed = $child['quantity'];
$stock = $child['current_quantity'];
$used = $child['usage_by_day'][$ym][$day] ?? 0;
$available = floor(($stock - $used) / $needed);
$available = max(0, $available); // prevent negative availability
$minForDay = min($minForDay, $available);
}
if ($minForDay < $minAvailable) {
$dailyAvailability[$ym][$day] = $minForDay;
}
}
}
// Step 3: Rebuild parent array, insert new keys after 'displayname'
$newParent = [];
foreach ($parent as $key => $value) {
$newParent[$key] = $value;
if ($key === 'displayname') {
$newParent['available'] = $minAvailable;
$newParent['available_by_day'] = $dailyAvailability;
}
}
$parent = $newParent;
}
return $equipmentSet;
}
Open issues
- At the moment we only read and cache equipment belonging to a client. Set can consist out of items not belonging to any client directly, so there is no data available (in equipment). This means that there's no data for the availabilty in the cache. This would mean that we need to load and cach more equipment and more plannedequipment.
These are appr. 1000 equipments (current number is 1200). - The calendar format for each month is:
Array ( [1] => 1 [2] => 1 [3] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 [10] => 1 [11] => 1 [12] => 1 [13] => 1 [14] => 1 [15] => 1 [16] => 1 [17] => 1 [18] => 1 [19] => 1 [20] => 1 [21] => 1 [22] => 1 [23] => 1 [24] => 1 [25] => 1 [26] => 1 [27] => 1 [28] => 1 [29] => 1 [30] => 1 )
---
Prompt
Database
-- Database: `rentman`
CREATE TABLE `equipment` (
`id` int(11) NOT NULL,
`modified` datetime DEFAULT NULL,
`modified_db` timestamp NOT NULL DEFAULT current_timestamp(),
`displayname` varchar(128) DEFAULT NULL,
`folder` int(11) DEFAULT NULL,
`code` varchar(8) DEFAULT NULL,
`in_shop` int(11) DEFAULT NULL,
`surface_article` int(11) DEFAULT NULL,
`shop_description_short` varchar(128) DEFAULT NULL,
`shop_description_long` text DEFAULT NULL,
`shop_seo_title` varchar(80) DEFAULT NULL,
`shop_seo_keyword` varchar(80) DEFAULT NULL,
`shop_seo_description` varchar(80) DEFAULT NULL,
`shop_featured` int(11) DEFAULT NULL,
`price` float DEFAULT NULL,
`subrental_costs` int(11) DEFAULT NULL,
`critical_stock_level` int(11) DEFAULT NULL,
`type` varchar(80) DEFAULT NULL,
`rental_sales` varchar(80) DEFAULT NULL,
`volume` int(11) DEFAULT NULL,
`packed_per` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`length` int(11) DEFAULT NULL,
`weight` int(11) DEFAULT NULL,
`empty_weight` int(11) DEFAULT NULL,
`power` int(11) DEFAULT NULL,
`current` int(11) DEFAULT NULL,
`image` varchar(40) DEFAULT NULL,
`image_path` varchar(255) DEFAULT NULL,
`is_combination` int(11) DEFAULT NULL,
`is_physical` varchar(80) DEFAULT NULL,
`tags` varchar(128) DEFAULT NULL,
`tag_hoofditem` tinyint(1) NOT NULL DEFAULT 0,
`tag_set` tinyint(1) NOT NULL DEFAULT 0,
`location_in_warehouse` varchar(80) DEFAULT NULL,
`current_quantity` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `equipmentsetscontent` (
`id` int(11) NOT NULL,
`modified` text DEFAULT NULL,
`displayname` text DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`parent_equipment` text DEFAULT NULL,
`order` int(11) DEFAULT NULL,
`equipment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `plannedequipment` (
`id` int(11) NOT NULL,
`modified` datetime DEFAULT NULL,
`displayname` varchar(128) DEFAULT NULL,
`equipment` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`quantity_total` int(11) DEFAULT NULL,
`equipment_group` int(11) DEFAULT NULL,
`planperiod_start` varchar(26) DEFAULT NULL,
`planperiod_end` varchar(26) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `reservations` (
`equipment_id` int(11) NOT NULL,
`current_quantity` int(11) DEFAULT NULL,
`year` int(11) NOT NULL,
`month` int(11) NOT NULL,
`availability` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`availability`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
Class
class AvailabilityCalculator(DatabaseBaseClass):
def __init__(self, config_path="../config/database.txt"):
super().__init__()
def get_ultimate_parents(self):
self.cursor.execute("SELECT DISTINCT parent_equipment FROM equipmentsetscontent")
parents = set(row[0] for row in self.cursor.fetchall())
self.cursor.execute("SELECT DISTINCT equipment FROM equipmentsetscontent")
children = set(row[0] for row in self.cursor.fetchall())
# Parents that are not children themselves
ultimate_parents = parents - children
return list(ultimate_parents)
Reservations look like this:
3047, 3, 2025, 5, [3, 1, 0, 0, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3..., 2]
3048, 3, 2025, 5, [3, 3, 3, 3, 3, 0, 0, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3..., 3]
3043, 4, 2025, 5, [4, 4, 4, 4, 4, 4, 4, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4..., 4]
3031, 1, 2025, 5, [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1..., 1]
....
The reservations are a list of numbers. Each number represents a day for that month (in the example May) and the number tells how many of this equipment is available.
Question
I want add a method: get_set_parts(equipment_id)
This method will select all children of the parent with equipment_id. Use table equipmentsetscontent to get this. Per child we need to store the quantity.quantity
Then if there's a reservation record for this child-equipment_id, add all these reservations to this child.
The output of the function needs to be something like this (please create a proper data structure).
parent: 3029
child: 1086
quantity: 12
2025-06: 1,1,1,2,3,4,4,4,0,......
2025-07: 1,1,1,2,3,4,4,4,0,......
etc, etc.
parent: 2029
child: 1087
quantity: 10
2025-06: 1,1,1,2,3,4,4,4,0,......
2025-07: 1,1,1,2,3,4,4,4,0,......
etc, etc.
child: 1091
quantity: 10
2025-06: 1,1,1,2,1,1,1,1,0,......
2025-08: 1,1,1,2,3,4,4,0,0,......
etc, etc.
etc, etc
Do you need additional information?
import mysql.connector
import json
class DatabaseBaseClass:
"""A base class to handle database connections."""
def __init__(self, host="localhost", user="root", password="", database="rentman"):
try:
self.db = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
self.cursor = self.db.cursor()
except mysql.connector.Error as err:
print(f"Error connecting to database: {err}")
# In a real application, you might want to handle this more gracefully
self.db = None
self.cursor = None
def __del__(self):
if self.db and self.db.is_connected():
self.cursor.close()
self.db.close()
class AvailabilityCalculator(DatabaseBaseClass):
def __init__(self, host="localhost", user="root", password="", database="rentman"):
super().__init__(host, user, password, database)
def get_ultimate_parents(self):
"""
Gets a list of all parent equipment that are not themselves children of another set.
"""
if not self.cursor:
return []
self.cursor.execute("SELECT DISTINCT parent_equipment FROM equipmentsetscontent")
parents = set(row[0] for row in self.cursor.fetchall())
self.cursor.execute("SELECT DISTINCT equipment FROM equipmentsetscontent")
children = set(row[0] for row in self.cursor.fetchall())
# Parents that are not children themselves
ultimate_parents = parents - children
return list(ultimate_parents)
def get_set_parts(self, equipment_id: int):
"""
Retrieves all child equipment for a given parent equipment_id,
including their quantities and monthly reservation data.
Args:
equipment_id: The ID of the parent equipment.
Returns:
A dictionary containing the parent's children and their details,
structured for clarity and easy access. Returns an empty dictionary
if the parent ID is not found or has no children.
"""
if not self.cursor:
return {}
# The main dictionary to hold the final structured data.
result = {}
# Step 1: Find all children of the given parent_equipment and their quantities.
# The 'equipment' and 'parent_equipment' columns are TEXT, so we search for the ID as a string.
query_children = """
SELECT equipment, quantity
FROM equipmentsetscontent
WHERE parent_equipment = %s
"""
self.cursor.execute(query_children, (str(equipment_id),))
children = self.cursor.fetchall()
if not children:
return {} # Return empty if no children are found
# Initialize the parent key in the result dictionary.
result[equipment_id] = {'children': {}}
# Step 2: For each child, fetch its reservation data.
for child_id_str, quantity in children:
try:
# The child_id from `equipmentsetscontent` is TEXT, needs to be INT for the next query.
child_id = int(child_id_str)
except (ValueError, TypeError):
# Skip if the child ID is not a valid integer.
continue
child_data = {
'quantity': quantity,
'reservations': {}
}
# Query to get all reservations for the current child.
query_reservations = """
SELECT year, month, availability
FROM reservations
WHERE equipment_id = %s
ORDER BY year, month
"""
self.cursor.execute(query_reservations, (child_id,))
reservations = self.cursor.fetchall()
for year, month, availability_json in reservations:
# Format the year and month into a 'YYYY-MM' key.
date_key = f"{year}-{str(month).zfill(2)}"
# The 'availability' is stored as a JSON string (e.g., '[3, 1, 0, ...]').
# We load it, convert each number to a string, and join with commas.
try:
availability_list = json.loads(availability_json)
availability_str = ",".join(map(str, availability_list))
child_data['reservations'][date_key] = availability_str
except json.JSONDecodeError:
# Handle cases where the JSON is invalid.
child_data['reservations'][date_key] = "Error: Invalid availability format"
# Add the complete child data to the result.
result[equipment_id]['children'][child_id] = child_data
return result
On the top level, the level of the parent I want to add an extra field.`
perform
select current_quantity from equipment where id = equipment_id (the function parameter).
and store the current_quantity on the level of the parent.
Change code to add this to the data structure.
The output of this method is
{3024: {'children': {
3025: {'quantity': 1,
'available': { '2025-05': '1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0',
'2025-06': '0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0',
'2025-07': '1,1,1,1,0,0,0,1,1,0,0,0,0,0,1,1,1,0,0,0,0,1,1,1,1,1,1,1,0,0,0',
'2025-08': '0,0,0,0,1,0,0,0,0,0,0,0,0,1,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0',
'2025-09': '0,1,1,0,0,0,0,0,1,0,0,0,0,0,0,1,1,1,0,1,1,1,1,0,0,0,0,0,0,1',
'2025-10': '1,1,0,1,1,1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1'}},
3026: {'quantity': 4,
'available': { '2025-05': '4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,0,0,0,0,0',
'2025-06': '0,0,0,0,0,0,0,0,0,0,4,0,0,0,4,4,4,0,0,0,0,0,0,0,0,0,0,0,0,0',
'2025-07': '4,4,4,4,0,0,0,4,4,0,0,0,0,0,4,4,4,0,0,0,0,4,4,4,4,4,4,4,0,0,0',
'2025-08': '0,0,0,0,4,0,0,0,0,0,0,0,0,4,0,0,0,0,4,4,0,0,0,0,0,0,0,0,0,0,0',
'2025-09': '0,4,4,0,0,0,0,0,4,0,0,0,0,0,0,4,4,4,0,4,4,4,4,0,0,0,0,0,0,4',
'2025-10': '4,4,0,4,4,4,4,4,4,0,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4'}},
3027: {'quantity': 1,
'available': {'2025-05': '2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,0,0,0,0,0',
'2025-06': '0,0,1,0,0,0,1,1,1,1,1,0,0,0,1,2,2,1,0,0,0,0,1,0,0,0,0,0,0,0',
'2025-07': '2,2,0,0,0,0,0,2,2,1,1,1,1,1,2,2,2,1,1,1,1,2,2,2,2,2,2,2,1,1,1',
'2025-08': '1,1,1,1,2,1,1,1,1,1,1,1,1,2,1,1,1,1,2,2,1,0,0,0,0,1,1,0,0,0,0',
'2025-09': '0,2,2,1,1,1,1,1,2,1,1,0,0,0,0,2,2,2,1,2,2,2,2,0,0,0,0,0,0,2',
'2025-10': '2,2,1,2,2,2,2,2,2,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2'}},
5641: {'quantity': 3,
'available': { '2025-05': '50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,47,47,47,46,46',
'2025-06': '46,46,47,47,47,47,47,47,47,47,50,47,47,47,50,50,50,47,47,47,47,47,47,47,47,47,47,47,47,47',
'2025-07': '50,50,50,50,47,47,47,50,50,47,47,47,47,47,50,50,50,47,47,47,47,50,50,50,50,50,50,50,47,47,47',
'2025-08': '47,47,47,47,50,47,47,47,47,47,47,47,47,50,47,47,47,47,50,50,47,47,47,47,47,47,47,47,47,47,47',
'2025-09': '47,50,50,47,47,47,47,47,50,47,47,47,47,47,47,50,50,50,47,50,50,50,50,47,47,47,47,47,47,50',
'2025-10': '50,50,47,50,50,50,50,50,50,47,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50'}},
10857: {'quantity': 5,
'available': {' 2025-06': '10,5,5,10,7,7,7,5,5,8,4,4,4,4,4,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10',
'2025-07': '10,10,7,7,2,2,2,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10',
'2025-08': '10,10,10,10,10,10,10,10,10,10,10,10,10,10,5,5,5,5,10,10,10,10,10,10,10,10,10,10,10,10,10',
'2025-09': '10,10,10,5,5,5,5,5,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10'}}},
'current_quantity': 2}}
We changed 'reservations' into 'available', because this reflects the values better.
The interpretation of the data is as follows.
We have equipment with id 3024, the current_quantity is 2.
We see that we need 1 times child 3025 in order to complete the set 3024. In may we have 1 available and the last 5 days of the month we have 0 available. This means that we can create only 1 set 3024 in may and the last 5 days of May we have 0 availability of 3024.
We also need 4 times 3026 and we have 4 available in the first part of May. Meaning we can create one set 3024.
Do you understand the interpretation?
Result
{3024: {'available': {'2025-05': '1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0',
'2025-06': '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0',
'2025-07': '1,1,0,0,0,0,0,1,1,0,0,0,0,0,1,1,1,0,0,0,0,1,1,1,1,1,1,1,0,0,0',
'2025-08': '0,0,0,0,1,0,0,0,0,0,0,0,0,1,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0',
'2025-09': '0,1,1,0,0,0,0,0,1,0,0,0,0,0,0,1,1,1,0,1,1,1,1,0,0,0,0,0,0,1',
'2025-10': '1,1,0,1,1,1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1'},