367 lines
13 KiB
Python
367 lines
13 KiB
Python
"""
|
|
Streets Service - Geschäftslogik für Straßen
|
|
"""
|
|
import json
|
|
import math
|
|
import re
|
|
|
|
from cmbnet.models.streets import StreetModel
|
|
from cmbnet.db import get_db
|
|
from cmbnet.utils.geo_utils import bbox_center, bbox_from_lonlat
|
|
from cmbnet.utils.helpers import log_transaction
|
|
|
|
|
|
_HOUSE_NUMBER_RE = re.compile(r'^\s*(\d+)')
|
|
|
|
|
|
def _house_number_value(value):
|
|
"""Liefert den führenden numerischen Teil einer Hausnummer oder ``None``."""
|
|
match = _HOUSE_NUMBER_RE.match(str(value or ''))
|
|
if not match:
|
|
return None
|
|
try:
|
|
return int(match.group(1))
|
|
except ValueError:
|
|
return None
|
|
|
|
|
|
def _normalize_name(value):
|
|
"""Vereinheitlicht einen Straßennamen für Gruppierung und Vergleiche."""
|
|
return ' '.join(str(value or '').split())
|
|
|
|
|
|
def _combine_zusatz(existing, direction):
|
|
"""Ergänzt einen bestehenden Zusatz um die neue Richtung."""
|
|
existing = _normalize_name(existing)
|
|
if not existing:
|
|
return direction
|
|
return f'{existing}-{direction}'
|
|
|
|
|
|
def _direction_suffixes(lower_bbox, upper_bbox):
|
|
"""Bestimmt die Richtungszusätze anhand der beiden Bounding-Box-Mittelpunkte.
|
|
|
|
Rückgabe: ``(zusatz_unten, zusatz_oben)``.
|
|
"""
|
|
lower_center = bbox_center(lower_bbox)
|
|
upper_center = bbox_center(upper_bbox)
|
|
if lower_center is None or upper_center is None:
|
|
return 'Nord', 'Süd'
|
|
|
|
delta_lat = lower_center[0] - upper_center[0]
|
|
delta_lon = lower_center[1] - upper_center[1]
|
|
mean_lat = math.radians((lower_center[0] + upper_center[0]) / 2)
|
|
east_west_distance = abs(delta_lon) * math.cos(mean_lat)
|
|
|
|
if abs(delta_lat) >= east_west_distance:
|
|
if delta_lat >= 0:
|
|
return 'Nord', 'Süd'
|
|
return 'Süd', 'Nord'
|
|
|
|
if delta_lon >= 0:
|
|
return 'Ost', 'West'
|
|
return 'West', 'Ost'
|
|
|
|
|
|
class StreetsService:
|
|
@staticmethod
|
|
def get_all_for_init(tenant_id):
|
|
"""Gibt alle Straßen für den AJAX-Initialload zurück"""
|
|
streets = StreetModel.get_all_by_tenant(tenant_id)
|
|
result = []
|
|
for street in streets:
|
|
result.append({
|
|
'id': street['id'],
|
|
'name': street['name'],
|
|
'zusatz': street['zusatz'] or '',
|
|
'state': street['state'],
|
|
'group_id': street['group_id'],
|
|
'hn_min': street['hn_min'],
|
|
'hn_max': street['hn_max'],
|
|
'bbox': json.loads(street['bbox']) if street['bbox'] else None
|
|
})
|
|
return result
|
|
|
|
@staticmethod
|
|
def import_from_house_numbers(tenant_id, entries, actor_account_id=None):
|
|
"""Ersetzt Straßen und Hausnummern eines Mandanten durch einen Import.
|
|
|
|
Die Hausnummern-Einträge werden nach Straßenname gruppiert. Pro Straße
|
|
werden minimale/maximale Hausnummer und die Bounding Box aus allen
|
|
Koordinaten berechnet. Der Zusatz startet nach dem Import immer leer.
|
|
"""
|
|
grouped = {}
|
|
for entry in entries or []:
|
|
name = _normalize_name(entry.get('street_name'))
|
|
if not name:
|
|
continue
|
|
key = name.casefold()
|
|
group = grouped.setdefault(key, {'name': name, 'entries': []})
|
|
group['entries'].append(entry)
|
|
|
|
stats = {
|
|
'streets': len(grouped),
|
|
'house_numbers': len(entries or []),
|
|
}
|
|
if not grouped:
|
|
return stats
|
|
db = get_db()
|
|
try:
|
|
# Der Import ist die alleinige Quelle für Straßen und Hausnummern.
|
|
# Bestehende Einträge (inkl. früherer Teilungen) werden ersetzt.
|
|
db.execute(
|
|
'DELETE FROM street_state_history WHERE tenant_id = ?',
|
|
(tenant_id,)
|
|
)
|
|
db.execute(
|
|
'DELETE FROM street_house_numbers WHERE tenant_id = ?',
|
|
(tenant_id,)
|
|
)
|
|
db.execute(
|
|
'DELETE FROM streets WHERE tenant_id = ?',
|
|
(tenant_id,)
|
|
)
|
|
|
|
for group in grouped.values():
|
|
name = group['name']
|
|
numbers = []
|
|
points = []
|
|
for entry in group['entries']:
|
|
number = _house_number_value(entry.get('house_number'))
|
|
if number is not None:
|
|
numbers.append(number)
|
|
try:
|
|
longitude = float(entry.get('longitude'))
|
|
latitude = float(entry.get('latitude'))
|
|
except (TypeError, ValueError):
|
|
continue
|
|
points.append((longitude, latitude))
|
|
|
|
hn_min = min(numbers) if numbers else None
|
|
hn_max = max(numbers) if numbers else None
|
|
bbox = bbox_from_lonlat(points)
|
|
|
|
cursor = db.execute(
|
|
'''INSERT INTO streets
|
|
(tenant_id, name, state, group_id, hn_min, hn_max, bbox, zusatz)
|
|
VALUES (?, ?, 'unbearbeitet', NULL, ?, ?, ?, '')''',
|
|
(tenant_id, name, hn_min, hn_max, bbox)
|
|
)
|
|
street_id = cursor.lastrowid
|
|
db.execute(
|
|
'''INSERT INTO street_state_history
|
|
(tenant_id, street_id, from_state, to_state, group_id,
|
|
actor_account_id, reason)
|
|
VALUES (?, ?, NULL, 'unbearbeitet', NULL, ?, 'Import')''',
|
|
(tenant_id, street_id, actor_account_id)
|
|
)
|
|
|
|
for entry in group['entries']:
|
|
db.execute(
|
|
'''INSERT INTO street_house_numbers
|
|
(tenant_id, street_name, house_number, longitude, latitude)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
ON CONFLICT(tenant_id, street_name, house_number)
|
|
DO UPDATE SET longitude = excluded.longitude,
|
|
latitude = excluded.latitude''',
|
|
(
|
|
tenant_id,
|
|
name,
|
|
entry.get('house_number'),
|
|
entry.get('longitude'),
|
|
entry.get('latitude')
|
|
)
|
|
)
|
|
|
|
db.commit()
|
|
stats['house_numbers'] = db.execute(
|
|
'SELECT COUNT(*) FROM street_house_numbers WHERE tenant_id = ?',
|
|
(tenant_id,)
|
|
).fetchone()[0]
|
|
except Exception:
|
|
db.rollback()
|
|
raise
|
|
|
|
return stats
|
|
|
|
@staticmethod
|
|
def get_split_options(street_id, tenant_id):
|
|
"""Liefert die im gültigen Bereich liegenden Hausnummern einer Straße."""
|
|
street = StreetModel.get_by_id(street_id, tenant_id)
|
|
if not street:
|
|
return None
|
|
|
|
hn_min = street['hn_min']
|
|
hn_max = street['hn_max']
|
|
numbers = set()
|
|
|
|
if hn_min is not None and hn_max is not None:
|
|
rows = get_db().execute(
|
|
'''SELECT house_number FROM street_house_numbers
|
|
WHERE tenant_id = ? AND street_name = ?''',
|
|
(tenant_id, street['name'])
|
|
).fetchall()
|
|
for row in rows:
|
|
number = _house_number_value(row['house_number'])
|
|
if number is not None and hn_min <= number <= hn_max:
|
|
numbers.add(number)
|
|
|
|
return {
|
|
'id': street['id'],
|
|
'name': street['name'],
|
|
'zusatz': street['zusatz'] or '',
|
|
'hn_min': hn_min,
|
|
'hn_max': hn_max,
|
|
'house_numbers': sorted(numbers),
|
|
}
|
|
|
|
@staticmethod
|
|
def split_street(street_id, tenant_id, house_number, actor_account_id=None):
|
|
"""Teilt eine Straße an einer Hausnummer in zwei Abschnitte.
|
|
|
|
Rückgabe: ``(True, result)`` oder ``(False, fehlermeldung)``.
|
|
"""
|
|
street = StreetModel.get_by_id(street_id, tenant_id)
|
|
if not street:
|
|
return False, 'Straße nicht gefunden'
|
|
|
|
try:
|
|
split_number = int(str(house_number).strip())
|
|
except (TypeError, ValueError):
|
|
return False, 'Ungültige Hausnummer'
|
|
|
|
hn_min = street['hn_min']
|
|
hn_max = street['hn_max']
|
|
if hn_min is None or hn_max is None:
|
|
return False, 'Für diese Straße ist kein Hausnummernbereich hinterlegt'
|
|
if split_number < hn_min or split_number > hn_max:
|
|
return False, (
|
|
f'Die Hausnummer muss zwischen {hn_min} und {hn_max} liegen'
|
|
)
|
|
|
|
rows = get_db().execute(
|
|
'''SELECT house_number, longitude, latitude
|
|
FROM street_house_numbers
|
|
WHERE tenant_id = ? AND street_name = ?''',
|
|
(tenant_id, street['name'])
|
|
).fetchall()
|
|
|
|
lower_points = []
|
|
upper_points = []
|
|
for row in rows:
|
|
number = _house_number_value(row['house_number'])
|
|
if number is None or number < hn_min or number > hn_max:
|
|
continue
|
|
try:
|
|
point = (float(row['longitude']), float(row['latitude']))
|
|
except (TypeError, ValueError):
|
|
continue
|
|
if number <= split_number:
|
|
lower_points.append(point)
|
|
if number >= split_number:
|
|
upper_points.append(point)
|
|
|
|
if not lower_points or not upper_points:
|
|
return False, (
|
|
'Für beide Abschnitte werden Hausnummern-Koordinaten benötigt'
|
|
)
|
|
|
|
lower_bbox = bbox_from_lonlat(lower_points)
|
|
upper_bbox = bbox_from_lonlat(upper_points)
|
|
if lower_bbox is None or upper_bbox is None:
|
|
return False, 'Bounding Boxes konnten nicht berechnet werden'
|
|
|
|
lower_direction, upper_direction = _direction_suffixes(
|
|
lower_bbox, upper_bbox
|
|
)
|
|
lower_zusatz = _combine_zusatz(street['zusatz'], lower_direction)
|
|
upper_zusatz = _combine_zusatz(street['zusatz'], upper_direction)
|
|
|
|
db = get_db()
|
|
existing_zusatz = {
|
|
(row['zusatz'] or '').casefold()
|
|
for row in db.execute(
|
|
'''SELECT zusatz FROM streets
|
|
WHERE tenant_id = ? AND name = ? AND id <> ?''',
|
|
(tenant_id, street['name'], street_id)
|
|
).fetchall()
|
|
}
|
|
for zusatz in (lower_zusatz, upper_zusatz):
|
|
if zusatz.casefold() in existing_zusatz:
|
|
return False, f'Der Zusatz „{zusatz}“ ist bereits vergeben'
|
|
|
|
try:
|
|
db.execute(
|
|
'''UPDATE streets
|
|
SET hn_min = ?, hn_max = ?, bbox = ?, zusatz = ?,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = ? AND tenant_id = ?''',
|
|
(hn_min, split_number, lower_bbox, lower_zusatz,
|
|
street_id, tenant_id)
|
|
)
|
|
cursor = db.execute(
|
|
'''INSERT INTO streets
|
|
(tenant_id, name, state, group_id, hn_min, hn_max, bbox, zusatz)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)''',
|
|
(tenant_id, street['name'], street['state'], street['group_id'],
|
|
split_number, hn_max, upper_bbox, upper_zusatz)
|
|
)
|
|
new_street_id = cursor.lastrowid
|
|
db.execute(
|
|
'''INSERT INTO street_state_history
|
|
(tenant_id, street_id, from_state, to_state, group_id,
|
|
actor_account_id, reason)
|
|
VALUES (?, ?, NULL, ?, ?, ?, 'Straße geteilt')''',
|
|
(tenant_id, new_street_id, street['state'],
|
|
street['group_id'], actor_account_id)
|
|
)
|
|
db.commit()
|
|
except Exception:
|
|
db.rollback()
|
|
raise
|
|
|
|
return True, {
|
|
'street_id': street_id,
|
|
'new_street_id': new_street_id,
|
|
'split_house_number': split_number,
|
|
'lower': {
|
|
'hn_min': hn_min,
|
|
'hn_max': split_number,
|
|
'zusatz': lower_zusatz,
|
|
'bbox': lower_bbox,
|
|
},
|
|
'upper': {
|
|
'hn_min': split_number,
|
|
'hn_max': hn_max,
|
|
'zusatz': upper_zusatz,
|
|
'bbox': upper_bbox,
|
|
},
|
|
}
|
|
|
|
@staticmethod
|
|
def update_street(street_id, tenant_id, changes, actor_account_id=None):
|
|
"""Aktualisiert eine Straße und schreibt History/Log"""
|
|
db = get_db()
|
|
old_street = StreetModel.get_by_id(street_id, tenant_id)
|
|
if not old_street:
|
|
return False
|
|
|
|
StreetModel.update(street_id, tenant_id, **changes)
|
|
|
|
# History bei State-Wechsel
|
|
if 'state' in changes and changes['state'] != old_street['state']:
|
|
db.execute(
|
|
'''INSERT INTO street_state_history
|
|
(tenant_id, street_id, from_state, to_state, group_id, actor_account_id)
|
|
VALUES (?, ?, ?, ?, ?, ?)''',
|
|
(tenant_id, street_id, old_street['state'], changes['state'],
|
|
changes.get('group_id', old_street['group_id']), actor_account_id)
|
|
)
|
|
db.commit()
|
|
log_transaction('street.state_change', 'street', street_id,
|
|
{'from': old_street['state'], 'to': changes['state']})
|
|
else:
|
|
log_transaction('street.update', 'street', street_id, changes)
|
|
|
|
return True
|