Skip to main content

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?


Back to top