thetool/application/IpNetwork/IpNetworkModel.php

<?php
// IpNetworkModel.php

class IpNetworkModel {

    public $id;
    public $network_address;
    public $cidr;
    public $parent_network_id;
    public $status;
    public $children;
    public $network_address_str;
    public $name;
    public $description;
    public $create;
    public $edit;
    public $location;

    public function __construct($data = []) {
        foreach ($data as $field => $value) {
            if (property_exists(get_called_class(), $field)) {
                $this->$field = $value;
            }
        }
    }

    private static function getSqlFilter(array $filters): string {
        $sqlConditions = [];
        $db = FronkDB::singleton()->link;

        if (!empty($filters['globalSearch'])) {
            $searchTerm = $db->real_escape_string($filters['globalSearch']);
            $sqlConditions[] = " (CONCAT(INET_NTOA(network_address), '/', cidr) LIKE '%{$searchTerm}%' OR `name` LIKE '%{$searchTerm}%' OR `description` LIKE '%{$searchTerm}%') ";
        }

        if (!empty($filters['name'])) $sqlConditions[] = Helper::generateFilterCondition($filters['name'], 'name');
        if (!empty($filters['description'])) $sqlConditions[] = Helper::generateFilterCondition($filters['description'], 'description');
        if (!empty($filters['location'])) $sqlConditions[] = Helper::generateFilterCondition($filters['location'], 'location');
        if (!empty($filters['status'])) $sqlConditions[] = Helper::generateFilterCondition($filters['status'], 'status');
        if (isset($filters['children']) && is_array($filters['children'])) {
            if (isset($filters['children']['from'])) {
                $sqlConditions[] = " (SELECT COUNT(*) FROM `IpNetwork` WHERE `parent_network_id` = main.id) >= " . intval($filters['children']['from']);
            }
            if (isset($filters['children']['to'])) {
                $sqlConditions[] = " (SELECT COUNT(*) FROM `IpNetwork` WHERE `parent_network_id` = main.id) <= " . intval($filters['children']['to']);
            }
        }

        if (empty($filters['parent_network_id'])) {
            $sqlConditions[] = " `parent_network_id` IS NULL ";
        } else {
            $sqlConditions[] = " `parent_network_id` = " . intval($filters['parent_network_id']) . " ";
        }

        foreach ($sqlConditions as $key => $condition) {
            if (strpos($condition, ' AND ') === 0 || strpos($condition, 'AND ') === 0) {
                $sqlConditions[$key] = substr($condition, 4);
            }
        }

        return empty($sqlConditions) ? "" : " WHERE " . implode(" AND ", $sqlConditions);
    }

    public static function getIpNetworks($filters, $limit = null, $offset = 0, $order = null): array {
        $db = FronkDB::singleton()->link;

        $orderClause = "ORDER BY `network_address` ASC";
        if ($order && !empty($order['key'])) {
            $orderKey = $db->real_escape_string($order['key']);
            $orderDir = (isset($order['order']) && strtolower($order['order']) === 'desc') ? 'DESC' : 'ASC';
            if ($orderKey === 'network_address_str') $orderKey = 'network_address';
            $orderClause = "ORDER BY `{$orderKey}` {$orderDir}";
        }

        $limitClause = is_null($limit) ? "" : " LIMIT " . intval($limit) . " OFFSET " . intval($offset);

        $sql = "
            SELECT 
                main.*, 
                CONCAT(INET_NTOA(main.network_address), '/', main.cidr) AS network_address_str,
                (SELECT COUNT(*) FROM `IpNetwork` WHERE `parent_network_id` = main.id) as children
            FROM `IpNetwork` main
        " . self::getSqlFilter($filters) . " 
        " . $orderClause . "
        " . $limitClause;

        $result = $db->query($sql);
        $rows = [];
        while ($row = $result->fetch_assoc()) {
            $rows[] = new IpNetworkModel($row);
        }

        return $rows;
    }

    public static function findByNetworkString(string $networkString): ?IpNetworkModel {
        $db = FronkDB::singleton()->link;
        if (!str_contains($networkString, '/')) {
            return null;
        }
        // Extract IP and CIDR, allowing for extra text like "(My Network)"
        preg_match('/^([0-9\.]+)\/(\d+)/', $networkString, $matches);
        if (count($matches) < 3) {
            return null;
        }
        $ip = $db->real_escape_string($matches[1]);
        $cidr = (int)$matches[2];

        $sql = "SELECT * FROM `IpNetwork` WHERE `network_address` = INET_ATON('$ip') AND `cidr` = $cidr";
        $result = $db->query($sql);
        $row = $result->fetch_assoc();
        return $row ? new IpNetworkModel($row) : null;
    }

    public static function countIpNetworks($filters): int {
        $db = FronkDB::singleton()->link;
        $sql = "SELECT COUNT(*) as `total_rows` FROM `IpNetwork` main" . self::getSqlFilter($filters);
        $result = $db->query($sql);
        return (int)$result->fetch_assoc()['total_rows'];
    }


    public static function findSuggestions(string $query, int $limit = 10): array {
        $db = FronkDB::singleton()->link;
        $query = $db->real_escape_string($query);
        $sql = "
            SELECT 
                CONCAT(INET_NTOA(network_address), '/', cidr) as network_address_str, 
                name
            FROM `IpNetwork` 
            WHERE 
                CONCAT(INET_NTOA(network_address), '/', cidr) LIKE '%{$query}%' OR 
                `name` LIKE '%{$query}%' OR 
                `description` LIKE '%{$query}%'
            LIMIT " . $limit;

        $result = $db->query($sql);
        $suggestions = [];
        while ($row = $result->fetch_assoc()) {
            $text = $row['network_address_str'];
            if ($row['name']) {
                $text .= " ({$row['name']})";
            }
            $suggestions[] = [
                'value' => $row['network_address_str'],
                'text' => $text
            ];
        }
        return $suggestions;
    }

    /**
     * @throws Exception
     */
    public static function createIpNetwork($data): void {
        $db = FronkDB::singleton()->link;

        $network_address = $data['network_address'];
        if (!filter_var($network_address, FILTER_VALIDATE_IP)) {
            throw new Exception("Ungültige IP-Adresse angegeben.");
        }

        $cidr = (int)$data['cidr'];
        $parent_network_id = !empty($data['parent_network_id']) ? (int)$data['parent_network_id'] : 'NULL';
        $status = $db->real_escape_string($data['status']);
        $name = $db->real_escape_string($data['name']);
        $description = $db->real_escape_string($data['description']);
        $location = $db->real_escape_string($data['location']);

        $check_sql = "SELECT id FROM `IpNetwork` WHERE `network_address` = INET_ATON('$network_address') AND `cidr` = $cidr";
        $result = $db->query($check_sql);
        if($result->num_rows > 0) {
            throw new Exception("Ein identisches Netzwerk existiert bereits.");
        }

        if ($parent_network_id === 'NULL' && $cidr >= 32) {
            throw new Exception("Stamm-Netzwerke können keine einzelnen IPs sein.");
        }

        $sql = "INSERT INTO `IpNetwork` (`network_address`, `cidr`, `parent_network_id`, `status`, `name`, `description`, `location`, `create`, `edit`) 
                VALUES (INET_ATON('$network_address'), $cidr, $parent_network_id, '$status', '$name', '$description', '$location', UNIX_TIMESTAMP(), UNIX_TIMESTAMP())";

        if (!$db->query($sql)) {
            throw new Exception("Fehler beim Einfügen des Netzwerks: " . $db->error);
        }
    }

    public static function updateIpNetwork($data): void {
        $db = FronkDB::singleton()->link;
        $id = (int)$data['id'];

        $sqlSetStr = [];
        if (isset($data['status'])) $sqlSetStr[] = "`status` = '" . $db->real_escape_string($data['status']) . "'";
        if (isset($data['name'])) $sqlSetStr[] = "`name` = '" . $db->real_escape_string($data['name']) . "'";
        if (isset($data['description'])) $sqlSetStr[] = "`description` = '" . $db->real_escape_string($data['description']) . "'";
        if (isset($data['location'])) $sqlSetStr[] = "`location` = '" . $db->real_escape_string($data['location']) . "'";

        if(empty($sqlSetStr)) return;

        $sqlSetStr[] = "`edit` = UNIX_TIMESTAMP()";

        $sql = "UPDATE `IpNetwork` SET " . implode(', ', $sqlSetStr) . " WHERE `id` = $id";

        if (!$db->query($sql)) {
            throw new Exception("Fehler beim Aktualisieren des Netzwerks: " . $db->error);
        }
    }

    public static function getById($id) {
        $db = FronkDB::singleton()->link;
        $id = (int)$id;
        $sql = "SELECT *, CONCAT(INET_NTOA(network_address), '/', cidr) as network_address_str FROM `IpNetwork` WHERE `id` = $id";
        $result = $db->query($sql);
        $row = $result->fetch_assoc();
        return $row ? new IpNetworkModel($row) : null;
    }

    public static function deleteIpNetwork($id) {
        $db = FronkDB::singleton()->link;
        $id = (int)$id;

        $child_sql = "SELECT `id` FROM `IpNetwork` WHERE `parent_network_id` = $id";
        $result = $db->query($child_sql);
        while ($row = $result->fetch_assoc()) {
            self::deleteIpNetwork($row['id']);
        }

        $delete_sql = "DELETE FROM `IpNetwork` WHERE `id` = $id";
        if (!$db->query($delete_sql)) {
            throw new Exception("Fehler beim Löschen des Netzwerks: " . $db->error);
        }
    }
}