"""Repeatable imports of OGI Page JSON produced by the RIS scraper."""
import base64
import hashlib
import json
import re
from collections import Counter
from datetime import date, datetime, timedelta
from io import BytesIO
from pathlib import Path
from typing import Any, TYPE_CHECKING
from zoneinfo import ZoneInfo
from markupsafe import escape
from onegov.core.html import sanitize_html
from onegov.core.orm.mixins import ContentMixin
from onegov.org.models import (
Meeting,
MeetingItem,
PoliticalBusiness,
PoliticalBusinessParticipation,
RISCommission,
RISCommissionMembership,
RISParliamentarian,
RISParliamentarianRole,
RISParliamentaryGroup,
)
from onegov.org.models.file import GeneralFileCollection
from onegov.org.models.political_business import (
POLITICAL_BUSINESS_STATUS,
PoliticalBusinessType,
)
if TYPE_CHECKING:
from collections.abc import Iterator
from onegov.parliament.models.parliamentarian_role import Role
from sqlalchemy.orm import Session
[docs]
SECTIONS: tuple[str, ...] = (
'parliamentarians',
'commissions',
'parliamentary_groups',
'political-business',
'meetings',
)
[docs]
KEYS: dict[str, str] = {
'parliamentarians': 'parliamentarian_id',
'commissions': 'commission_id',
'parliamentary_groups': 'parliamentary_group_id',
'political-business': 'self_id',
'meetings': 'meeting_id',
}
[docs]
BUSINESS_TYPES: dict[str, PoliticalBusinessType] = {
'Anfrage': 'inquiry',
'Bericht und Antrag': 'report and proposal',
'Beschluss': 'decision',
'Dringliche Interpellation': 'urgent interpellation',
'Dringliche Motion': 'urgent motion',
'Dringliches Postulat': 'urgent postulate',
'Einladung': 'invitation',
'Interpellation': 'interpellation',
'Kommissionsbericht': 'commission report',
'Motion': 'motion',
'Parlamentarische Erklärung': 'parliamentary statement',
'Petition': 'petition',
'Postulat': 'postulate',
'Protokoll': 'protocol',
'Resolution': 'resolution',
'Verschiedenes': 'miscellaneous',
'Vorlage': 'draft',
'Wahlen': 'election',
}
[docs]
COUNCIL_ROLES: dict[str, Role] = {
'Präsident': 'president',
'Präsidentin': 'president',
'Vizepräsident': 'vice_president',
'Vizepräsidentin': 'vice_president',
'Stimmenzähler': 'vote_counter',
'Stimmenzählerin': 'vote_counter',
'Stimmenzähler-Stellvertreter': 'vote_counter',
'Stimmenzähler-Stellvertreterin': 'vote_counter',
'Sekretär': 'none',
'Sekretär-Stellvertreterin': 'none',
'Protokollführerin': 'none',
'Gemeindeschreiber': 'none',
}
[docs]
MONTHS: dict[str, int] = {
name: index
for index, names in enumerate(
(
('Januar', 'Jan'),
('Februar', 'Feb'),
('März', 'Mär'),
('April', 'Apr'),
('Mai',),
('Juni', 'Jun'),
('Juli', 'Jul'),
('August', 'Aug'),
('September', 'Sept', 'Sep'),
('Oktober', 'Okt'),
('November', 'Nov'),
('Dezember', 'Dez'),
),
1,
)
for name in names
}
[docs]
REFERENCE = re.compile(
r'/(?:_rte/|_rtr/)(information|person|behoerde|anlass)[/_](\d+)'
)
[docs]
FIELD_NAMES: set[str] = {
'Nummer',
'Geschäftsart',
'Status',
'Datum',
'Beschreibung',
'Verfasser/Beteiligte',
'Beteiligte',
'Fraktion',
'Fraktionen',
'Funktion',
'Partei',
'Beruf',
'Ort',
'Kontakt',
}
[docs]
def walk(node: Any) -> Iterator[dict[str, Any]]:
if isinstance(node, dict):
yield node
for value in node.values():
yield from walk(value)
elif isinstance(node, list):
for value in node:
yield from walk(value)
[docs]
def text(node: Any) -> str:
if isinstance(node, list):
return ' '.join(filter(None, (text(child) for child in node)))
if not isinstance(node, dict):
return ''
return ' '.join(
(node.get('text', '') or text(node.get('children', []))).split()
)
[docs]
def references(node: Any, kind: str) -> set[str]:
return {
match[2]
for child in walk(node)
if (match := REFERENCE.search(child.get('url', '')))
and match[1] == kind
}
[docs]
def fields(page: dict[str, Any]) -> dict[str, Any]:
elements = page['elements']
return {
text(element): elements[index + 1]
for index, element in enumerate(elements[:-1])
if text(element) in FIELD_NAMES
and (
element['type'] == 'Heading'
or (
element['type'] == 'Paragraph'
and len(element.get('children', [])) == 1
and element['children'][0]['type'] == 'Text'
)
)
}
[docs]
def tables(page: dict[str, Any]) -> Iterator[tuple[str, dict[str, Any]]]:
heading = ''
for element in page['elements']:
if element['type'] == 'Heading':
heading = text(element)
if element['type'] == 'Table':
headers = [text(header) for header in element['headers']]
for row in element['rows']:
yield heading, dict(zip(headers, row['cells']))
[docs]
def parse_date(value: str) -> date | None:
if not value.strip():
return None
if match := re.search(r'(\d{1,2})\.\s*(\d{1,2})\.\s*(\d{4})', value):
day, month, year = map(int, match.groups())
return date(year, month, day)
match = re.search(r'(\d{1,2})\.\s*(\w+)\.?\s+(\d{4})', value)
if not match or match[2] not in MONTHS:
raise ValueError(f'Unrecognised date: {value!r}')
return date(int(match[3]), MONTHS[match[2]], int(match[1]))
[docs]
def normalize_name(value: str) -> str:
return ' '.join(value.replace('\u200b', '').replace('\ufeff', '').split())
[docs]
def role_key(value: str | None) -> str:
key = re.sub(r'[\s/-]', '', (value or '').casefold())
if key.startswith('erstunterzeich') or key == 'firstsignatory':
return 'first'
if key.startswith('mitunterzeich') or key == 'cosignatory':
return 'co'
if key.startswith('verfasser') or key == 'author':
return 'author'
return key
[docs]
def markup(node: Any) -> str:
if isinstance(node, list):
return ' '.join(markup(child) for child in node)
if not isinstance(node, dict):
return ''
kind = node['type']
value = str(escape(node.get('text', '')))
children = markup(node.get('children', []))
if kind == 'Text':
if node.get('bold'):
value = f'<strong>{value}</strong>'
if node.get('italic'):
value = f'<em>{value}</em>'
return value
if kind == 'Link':
return str(
sanitize_html(f'<a href="{escape(node["url"])}">{value}</a>')
)
if kind == 'Table':
headers = ''.join(
f'<th>{markup(header)}</th>' for header in node['headers']
)
rows = ''.join(
'<tr>'
+ ''.join(f'<td>{markup(cell)}</td>' for cell in row['cells'])
+ '</tr>'
for row in node['rows']
)
return f'<table><thead>{headers}</thead><tbody>{rows}</tbody></table>'
if kind == 'Break':
return '<br>'
if kind == 'Heading':
level = min(6, max(1, int(node.get('level', 2))))
return f'<h{level}>{value}</h{level}>'
tag = {
'Paragraph': 'div',
'ListItem': 'li',
'UnorderedList': 'ul',
'OrderedList': 'ol',
}.get(kind)
return f'<{tag}>{children}</{tag}>' if tag else ''
[docs]
def safe_path(root: Path, name: str) -> Path:
path = (root / name).resolve()
if not path.is_relative_to(root.resolve()) or not path.is_file():
raise ValueError(f'Invalid OGI archive path: {name}')
return path
[docs]
def digest(path: Path) -> str:
with path.open('rb') as stream:
return hashlib.file_digest(stream, 'sha256').hexdigest()
[docs]
def source_id(path: Path) -> str:
return path.stem.rsplit('_', 1)[-1]
[docs]
class OGIArchive:
def __init__(self, path: Path, section: str | None = None) -> None:
[docs]
self.root = path.parent if section else path
[docs]
self.pages: dict[str, dict[str, dict[str, Any]]] = {
name: {} for name in (*SECTIONS, 'lists')
}
[docs]
self.assets: dict[str, dict[str, str]] = {}
[docs]
self.manifest: dict[str, Any] = {}
[docs]
self.unavailable: dict[str, int] = {}
manifest_path = self.root / 'manifest.json'
if manifest_path.is_file():
self.manifest = json.loads(manifest_path.read_text())
if self.manifest.get('format') != 'ogi-ris-1':
raise ValueError('Expected an OGI RIS acquisition manifest')
self.assets = self.manifest['assets']
self.unavailable = self.manifest.get('unavailable_documents', {})
if any(
code not in (404, 410) for code in self.unavailable.values()
):
raise ValueError('Unexpected document download failure')
for name, checksum in self.manifest['files'].items():
file = safe_path(self.root, name)
if digest(file) != checksum:
raise ValueError(f'OGI page checksum mismatch: {name}')
directory = Path(name).parent.name
if directory not in self.pages:
raise ValueError(f'Unknown OGI section: {directory}')
key = source_id(file) if directory != 'lists' else file.stem
if key in self.pages[directory]:
raise ValueError(f'Duplicate OGI source ID: {name}')
self.pages[directory][key] = json.loads(file.read_text())
checked: set[str] = set()
for asset in self.assets.values():
name = asset['path']
if name not in checked:
if digest(safe_path(self.root, name)) != asset['sha256']:
raise ValueError(f'Document checksum mismatch: {name}')
checked.add(name)
elif section:
for file in sorted(path.glob('*.json')):
key = source_id(file)
if key in self.pages[section]:
raise ValueError(f'Duplicate OGI source ID: {file}')
self.pages[section][key] = json.loads(file.read_text())
else:
raise ValueError('Missing complete OGI acquisition manifest')
self.validate()
[docs]
def validate(self) -> None:
for pages in self.pages.values():
for page in pages.values():
if not page['metadata']['title'] or 'elements' not in page:
raise ValueError('Invalid OGI Page')
businesses = self.pages['political-business']
if self.manifest:
listed = references(
self.pages['lists']['businesses'], 'information'
)
if (
listed != businesses.keys()
or len(listed) != self.manifest['business_count']
):
raise ValueError('Incomplete business acquisition')
for page in businesses.values():
data = fields(page)
kind = text(data.get('Geschäftsart'))
if kind not in BUSINESS_TYPES:
raise ValueError(f'Unknown business type: {kind}')
status = text(data.get('Status'))
if status and status not in POLITICAL_BUSINESS_STATUS.values():
raise ValueError(f'Unknown business status: {status}')
entry = parse_date(text(data.get('Datum')))
if self.manifest and (
entry is None
or entry < date.fromisoformat(self.manifest['since'])
):
raise ValueError(f'Business outside cutoff: {entry}')
if self.manifest:
for kind, section in (
('person', 'parliamentarians'),
('anlass', 'meetings'),
):
missing = (
references(page, kind) - self.pages[section].keys()
)
if missing:
raise ValueError(f'Missing {kind} pages: {missing}')
if self.manifest:
for pages in self.pages.values():
for page in pages.values():
for node in walk(page['elements']):
url = node.get('url', '')
if re.search(r'/_(?:doc|docn)/', url):
if (
url not in self.assets
and url not in self.unavailable
):
raise ValueError(f'Missing document: {url}')
for page in self.pages['meetings'].values():
meeting_times(page)
[docs]
def importer(self, session: Session) -> OGIImporter:
return OGIImporter(self, session)
[docs]
def meeting_times(
page: dict[str, Any],
) -> tuple[datetime, datetime | None]:
timestamp = next(
(
text(el)
for el in page['elements']
if el['type'] == 'Paragraph' and re.match(r'^\d{1,2}\.', text(el))
),
'',
)
day = parse_date(timestamp)
if day is None:
raise ValueError(f'Missing meeting date: {page["metadata"]["title"]}')
times = re.findall(r'(\d{1,2})[.:](\d{2})\s*Uhr', timestamp)
zone = ZoneInfo('Europe/Zurich')
hour, minute = map(int, times[0]) if times else (0, 0)
start = datetime(day.year, day.month, day.day, hour, minute, tzinfo=zone)
end = None
if len(times) > 1:
hour, minute = map(int, times[1])
end = datetime(day.year, day.month, day.day, hour, minute, tzinfo=zone)
if end < start:
end += timedelta(days=1)
return start, end
[docs]
class OGIImporter:
def __init__(self, archive: OGIArchive, session: Session) -> None:
[docs]
self.counts: Counter[str] = Counter()
[docs]
self.people: dict[str, RISParliamentarian] = {}
[docs]
self.businesses: dict[str, PoliticalBusiness] = {}
[docs]
self.groups: dict[str, RISParliamentaryGroup] = {}
[docs]
self.commissions: dict[str, RISCommission] = {}
[docs]
self.active: set[str] = set()
[docs]
self.council: set[str] = set()
[docs]
self.council_functions: dict[str, str] = {}
[docs]
self.names: dict[str, str] = {}
for name in ('people', 'council'):
page = archive.pages['lists'].get(name, {'elements': []})
ids = references(page, 'person')
self.active.update(ids)
if name == 'council':
self.council.update(ids)
for _heading, row in tables(page):
for source in references(row, 'person'):
self.council_functions[source] = text(
row.get('Funktion')
)
for section in ('lists', 'commissions', 'parliamentary_groups'):
for page in archive.pages[section].values():
for node in walk(page['elements']):
match = REFERENCE.search(node.get('url', ''))
if match and match[1] == 'person':
self.names[match[2]] = text(node)
if section != 'lists':
self.active.add(match[2])
eligible = set(self.active)
businesses = archive.pages['political-business']
for page in businesses.values():
eligible.update(references(page, 'person'))
for source, page in archive.pages['parliamentarians'].items():
if references(page, 'information') & businesses.keys():
eligible.add(source)
for (meta,) in session.query(PoliticalBusiness.meta).filter(
PoliticalBusiness.meta['self_id'].astext.in_(businesses)
):
eligible.update(
source for source, _role in meta.get('people_ids', [])
)
for person in session.query(RISParliamentarian):
existing_source = person.meta.get('parliamentarian_id')
if existing_source and (
not archive.manifest or existing_source in eligible
):
self.people[existing_source] = person
[docs]
def upsert[T: ContentMixin](
self,
model: type[T],
source: str,
key: str,
identity: dict[str, Any] | None = None,
) -> T:
query = self.session.query(model)
obj = query.filter(
model.meta[key].astext.in_((source, source + '.json'))
).one_or_none()
if obj is None and identity:
obj = (
query.filter_by(**identity)
.filter(model.meta[key].astext.is_(None))
.one_or_none()
)
if obj is None:
obj = model()
self.session.add(obj)
self.counts['created'] += 1
else:
self.counts['updated'] += 1
obj.meta = {**(obj.meta or {}), key: source}
return obj
[docs]
def attach(
self, obj: PoliticalBusiness | Meeting, page: dict[str, Any]
) -> None:
collection = GeneralFileCollection(self.session)
urls = {
node['url']
for node in walk(page['elements'])
if node.get('url') in self.archive.assets
}
imported = dict(obj.meta.get('ogi_documents', {}))
for url in sorted(urls):
asset = self.archive.assets[url]
if imported.get(url) == asset['sha256']:
continue
path = safe_path(self.archive.root, asset['path'])
content = BytesIO(path.read_bytes())
file = collection.by_content(content).first()
if file is None:
content.seek(0)
title = asset['title'] or url.rsplit('/', 1)[-1]
if content.getvalue().startswith(
b'%PDF-'
) and not title.endswith('.pdf'):
title += '.pdf'
file = collection.add(filename=title, content=content)
if file not in obj.files:
obj.files.append(file)
self.counts['attachments'] += 1
imported[url] = asset['sha256']
obj.meta = {**obj.meta, 'ogi_documents': imported}
[docs]
def attach_images(
self,
business: PoliticalBusiness,
page: dict[str, Any],
) -> None:
images: dict[str, dict[str, str]] = {}
collection = GeneralFileCollection(self.session)
for node in walk(page['elements']):
if node.get('type') != 'EmbeddedImage':
continue
encoded = node['content']
data = base64.b64decode(encoded.split(',', 1)[1])
checksum = hashlib.sha256(data).hexdigest()
if checksum in images:
continue
content = BytesIO(data)
file = collection.by_content(content).first()
if file is None:
content.seek(0)
extension = (
'png' if encoded.startswith('data:image/png') else ('jpg')
)
file = collection.add(
filename=f'illustration.{extension}', content=content
)
if file not in business.files:
business.files.append(file)
self.counts['illustrations'] += 1
images[checksum] = {
'file_id': file.id,
'alt': node.get('alt') or '',
}
business.meta = {**business.meta, 'ogi_images': list(images.values())}
[docs]
def import_people(self) -> None:
linked: set[str] = set()
businesses = self.archive.pages['political-business']
for page in businesses.values():
linked.update(references(page, 'person'))
for source, page in self.archive.pages['parliamentarians'].items():
if references(page, 'information') & businesses.keys():
linked.add(source)
if self.archive.manifest and source not in self.active | linked:
self.counts['skipped_inactive'] += 1
continue
title = page['metadata']['title']
first, _, last = title.partition(' ')
parts = title.split()
if len(parts) == 1:
first, last = '', title
listed = self.names.get(source, '').split()
for split in range(1, len(parts)):
if parts[split:] + parts[:split] == listed:
first, last = ' '.join(parts[:split]), ' '.join(
parts[split:]
)
break
person = self.upsert(
RISParliamentarian,
source,
'parliamentarian_id',
{'first_name': first, 'last_name': last or first},
)
person.first_name, person.last_name = first, last or first
data = fields(page)
person.party = text(data.get('Partei'))
person.occupation = text(data.get('Beruf'))
contact: list[dict[str, Any]] = []
for el in page['elements']:
if el['type'] == 'Table':
break
contact.append(el)
for node in walk(contact):
url = node.get('url', '')
if url.startswith('mailto:'):
person.email_primary = url[7:].split('?', 1)[0]
elif url.startswith('tel:'):
person.phone_business = text(node)
elif '/ortsplan/' in url:
address = text(node)
match = re.search(r'(\d{4})\s+(.+)$', address)
if match:
person.private_address = address[
: match.start()
].strip()
person.private_address_zip_code = match[1]
person.private_address_city = match[2]
if not person.occupation:
person.occupation = next(
(
text(el).split(', im Einwohnerrat')[0]
for el in contact
if ', im Einwohnerrat' in text(el)
),
'',
)
person.content = {
**(person.content or {}),
'info': str(sanitize_html(markup(contact))),
}
interest_rows = [
row
for heading, row in tables(page)
if heading == 'Interessenbindungen'
]
if interest_rows:
person.interests = {
'headers': list(interest_rows[0]),
'rows': [
{key: text(value) for key, value in row.items()}
for row in interest_rows
],
}
for node in walk(page['elements']):
if node['type'] == 'EmbeddedImage':
image = base64.b64decode(node['content'].split(',', 1)[1])
checksum = hashlib.sha256(image).hexdigest()
if person.meta.get('ogi_portrait') != checksum:
person.picture = (image, 'portrait.jpg')
person.meta = {**person.meta, 'ogi_portrait': checksum}
break
marker = next(
(role for role in person.roles if role.meta.get('ogi_active')),
None,
)
if source in self.active:
if marker is None:
marker = RISParliamentarianRole(meta={'ogi_active': True})
person.roles.append(marker)
function = self.council_functions.get(source, '')
marker.role = (
COUNCIL_ROLES.get(function, 'member')
if source in self.council
else 'none'
)
marker.additional_information = function or None
marker.end = None
elif marker is not None:
person.roles.remove(marker)
self.session.delete(marker)
person.meta = {
**person.meta,
'ogi_active': source in self.active,
'ogi_source_name': self.names.get(source, title),
}
self.people[source] = person
self.counts['parliamentarians'] += 1
self.session.flush()
[docs]
def import_bodies(self, section: str) -> None:
for source, page in self.archive.pages[section].items():
title = page['metadata']['title']
if section == 'parliamentary_groups':
group = self.upsert(
RISParliamentaryGroup,
source,
KEYS[section],
{'name': title},
)
self.groups[source] = group
body: RISParliamentaryGroup | RISCommission = group
else:
body = self.upsert(
RISCommission, source, KEYS[section], {'name': title}
)
self.commissions[source] = body
body.name = title
body.description = sanitize_html(
markup(
[el for el in page['elements'] if el['type'] != 'Table']
)
)
members: list[tuple[str, str]] = []
for _heading, row in tables(page):
name = row.get('Name Vorname', row.get('Name', {}))
function = text(row.get('Funktion'))
members.extend(
(person_id, function)
for person_id in sorted(references(name, 'person'))
)
body.meta = {**body.meta, 'people_ids': members}
self.session.flush()
self.counts[section] = len(self.archive.pages[section])
[docs]
def plain_historical(self, person: RISParliamentarian) -> bool:
identity = {
'id',
'type',
'external_kub_id',
'first_name',
'last_name',
'created',
'modified',
'meta',
'content',
'gender',
'shipping_method',
}
return (
set(person.meta) <= {'parliamentarian_id', 'ogi_unresolved_name'}
and not person.content
and not person.roles
and not person.commission_memberships
and not person.picture
and person.gender == 'male'
and person.shipping_method == 'a'
and all(
getattr(person, column.key) in (None, '')
for column in RISParliamentarian.__mapper__.column_attrs
if column.key not in identity
)
)
[docs]
def merge_person(
self,
target: RISParliamentarian,
duplicate: RISParliamentarian,
) -> None:
old = duplicate.meta['parliamentarian_id']
source = target.meta['parliamentarian_id']
for participation in list(duplicate.political_businesses):
business = participation.political_business
participation.parliamentarian = target
business.meta = {
**business.meta,
'people_ids': [
(source if key == old else key, role)
for key, role in business.meta.get('people_ids', [])
],
}
self.people.pop(old, None)
self.session.delete(duplicate)
self.session.flush()
self.counts['merged_parliamentarians'] += 1
[docs]
def historical_person(self, name: str) -> str:
name = normalize_name(name.strip(' ,'))
# Only exact, unique source names establish identity.
matches = [
person
for person in self.people.values()
if name.casefold()
in (
normalize_name(
f'{person.first_name} {person.last_name}'
).casefold(),
normalize_name(
f'{person.last_name} {person.first_name}'
).casefold(),
)
]
if len(matches) > 1:
clean = [
person
for person in matches
if not any(
character in person.meta.get('ogi_unresolved_name', '')
for character in ('\u200b', '\ufeff')
)
]
target = (
clean[0]
if len(clean) == 1
else (
min(
matches,
key=lambda person: person.meta['parliamentarian_id'],
)
if not clean
else None
)
)
if target is not None:
for duplicate in list(matches):
if duplicate == target:
continue
if (
duplicate.meta.get('ogi_unresolved_name')
and any(
c in duplicate.meta['ogi_unresolved_name']
for c in ('\u200b', '\ufeff')
)
and self.plain_historical(duplicate)
):
self.merge_person(target, duplicate)
matches.remove(duplicate)
if len(matches) == 1:
person = matches[0]
if person.meta.get('ogi_unresolved_name'):
person.first_name = normalize_name(person.first_name)
person.last_name = normalize_name(person.last_name)
person.meta = {
**person.meta,
'ogi_unresolved_name': normalize_name(
person.meta['ogi_unresolved_name']
),
}
return person.meta['parliamentarian_id']
source = (
'unlinked-'
+ hashlib.sha256(name.casefold().encode()).hexdigest()[:20]
)
if source not in self.people:
first, _, last = name.partition(' ')
person = self.upsert(
RISParliamentarian, source, 'parliamentarian_id'
)
person.first_name, person.last_name = first, last or first
person.meta = {**person.meta, 'ogi_unresolved_name': name}
self.people[source] = person
return source
[docs]
def participants(self, node: dict[str, Any]) -> list[tuple[str, str]]:
pieces: list[str] = []
for child in walk(node):
if child['type'] == 'Link':
link_ids = references(child, 'person')
if link_ids:
pieces.append(f'@@{next(iter(link_ids))}@@')
else:
pieces.append(text(child))
elif child['type'] == 'Text':
pieces.append(text(child))
elif child['type'] == 'Break':
pieces.append('\n')
value = ' '.join(pieces)
records: list[tuple[str, str]] = []
for segment in re.split(r'\n|(?<=\))\s*,?\s*', value):
segment = segment.strip(' ,')
if not segment:
continue
role_match = re.search(
r'\(?((?:Erst|Mit)unterzeich[^)]*|Verfasser[^)]*|'
r'keine Funktion)\)?$',
segment,
)
role = role_match[1] if role_match else ''
name = segment[: role_match.start()] if role_match else segment
name = name.strip(' (,')
ids = re.findall(r'@@(\d+)@@', name)
if ids:
records.extend((source, role) for source in ids)
elif name:
parts = [part.strip() for part in name.split(',')]
if len(parts) > 2:
name = f'{parts[1]} {parts[0]}'
else:
name = parts[0]
records.append((self.historical_person(name), role))
return records
[docs]
def merge_business(
self,
target: PoliticalBusiness,
duplicate: PoliticalBusiness,
) -> None:
for file in duplicate.files:
if file not in target.files:
target.files.append(file)
for group in duplicate.parliamentary_groups:
if group not in target.parliamentary_groups:
target.parliamentary_groups.append(group)
for participant in list(duplicate.participants):
participant.political_business = target
for item in list(duplicate.meeting_items):
item.political_business = target
for item in list(duplicate.agenda_items):
item.political_businesses = list(
dict.fromkeys(
target if business == duplicate else business
for business in item.political_businesses
)
)
target.meta = {**duplicate.meta, **target.meta}
target.content = {**duplicate.content, **target.content}
self.session.delete(duplicate)
self.session.flush()
self.counts['merged'] += 1
[docs]
def import_businesses(self, source_ids: set[str] | None = None) -> None:
statuses = {
value: key for key, value in POLITICAL_BUSINESS_STATUS.items()
}
untagged = (
self.session.query(PoliticalBusiness)
.filter(PoliticalBusiness.meta['self_id'].astext.is_(None))
.all()
)
for source, page in self.archive.pages['political-business'].items():
if source_ids is not None and source not in source_ids:
continue
data = fields(page)
number = text(data.get('Nummer')) or None
entry = parse_date(text(data.get('Datum')))
kind = BUSINESS_TYPES[text(data.get('Geschäftsart'))]
candidates = [
business
for business in untagged
if not business.meta.get('self_id')
and number
and business.entry_date == entry
and re.sub(
r'^(?:Nr\.?|[A-Z])\s*', '', (business.number or '').strip()
)
== number
and business.political_business_type.removeprefix('urgent ')
== kind.removeprefix('urgent ')
]
if len(candidates) > 1:
raise ValueError(f'Ambiguous existing business: {number}')
if candidates:
existing = (
self.session.query(PoliticalBusiness)
.filter(PoliticalBusiness.meta['self_id'].astext == source)
.one_or_none()
)
if existing is not None and existing != candidates[0]:
self.merge_business(candidates[0], existing)
candidates[0].meta = {**candidates[0].meta, 'self_id': source}
business = self.upsert(PoliticalBusiness, source, 'self_id')
business.title = page['metadata']['title']
business.number = text(data.get('Nummer')) or None
business.entry_date = parse_date(text(data.get('Datum')))
business.political_business_type = BUSINESS_TYPES[
text(data.get('Geschäftsart'))
]
business.status = statuses.get(text(data.get('Status')))
business.chronology = str(
sanitize_html(markup(data.get('Beschreibung', [])))
)
participants = data.get(
'Verfasser/Beteiligte', data.get('Beteiligte', {})
)
records = self.participants(participants) if participants else []
business.meta = {
**business.meta,
'people_ids': records,
'parliamentary_group_ids': sorted(
references(
[data.get('Fraktion'), data.get('Fraktionen')],
'behoerde',
)
),
'ogi_meeting_ids': sorted(references(page, 'anlass')),
'ogi_source_fields': {
key: text(value) for key, value in data.items()
},
}
self.attach(business, page)
self.attach_images(business, page)
self.businesses[source] = business
# Participation is sometimes exposed only on the person's page.
for person_id, page in self.archive.pages['parliamentarians'].items():
if person_id not in self.people:
continue
for _heading, row in tables(page):
for source in references(row, 'information'):
linked_business = self.businesses.get(source)
if linked_business is None:
continue
records = list(linked_business.meta['people_ids'])
if not any(key == person_id for key, _role in records):
records.append((person_id, text(row.get('Rolle'))))
linked_business.meta = {
**linked_business.meta,
'people_ids': records,
}
self.session.flush()
self.counts['political-business'] = len(self.businesses)
[docs]
def import_meetings(self) -> None:
for source, page in self.archive.pages['meetings'].items():
meeting = self.upsert(Meeting, source, 'meeting_id')
meeting.title = page['metadata']['title']
meeting.start_datetime, meeting.end_datetime = meeting_times(page)
data = fields(page)
meeting.address = sanitize_html(markup(data.get('Ort', [])))
meeting.description = sanitize_html(
markup(data.get('Beschreibung', []))
)
meeting.meta = {
**meeting.meta,
'ogi_business_ids': sorted(references(page, 'information')),
}
self.attach(meeting, page)
agenda_business_ids: dict[str, list[str]] = {}
for heading, row in tables(page):
if heading != 'Traktanden':
continue
number = text(row.get('Nr.', row.get('Nr', {}))) or None
title = text(row.get('Bezeichnung'))
item = next(
(
item
for item in meeting.meeting_items
if item.number == number
and (number is not None or item.title == title)
),
None,
)
if item is None:
item = MeetingItem(number=number)
meeting.meeting_items.append(item)
item.title = title
ids = list(
dict.fromkeys(
match[2]
for node in walk(row.get('Geschäft', {}))
if (match := REFERENCE.search(node.get('url', '')))
and match[1] == 'information'
)
)
included = [
source
for source in ids
if source in self.archive.pages['political-business']
]
item.political_business_link_id = next(
iter(included or ids), None
)
agenda_business_ids[number or title] = ids
self.counts['agenda_items'] += 1
meeting.meta = {
**meeting.meta,
'ogi_agenda_business_ids': agenda_business_ids,
}
self.session.flush()
self.counts['meetings'] = len(self.archive.pages['meetings'])
[docs]
def run(
self,
section: str | None = None,
business_ids: set[str] | None = None,
) -> dict[str, int]:
if section in (None, 'parliamentarians'):
self.import_people()
if section in (None, 'commissions'):
self.import_bodies('commissions')
if section in (None, 'parliamentary_groups'):
self.import_bodies('parliamentary_groups')
if section in (None, 'political-business'):
self.import_businesses(business_ids)
if section in (None, 'meetings'):
self.import_meetings()
connect_relationships(
self.session, set(self.people) if self.archive.manifest else None
)
self.session.flush()
self.counts['unavailable_documents'] = len(self.archive.unavailable)
return dict(self.counts)
[docs]
def connect_relationships(
session: Session,
allowed_people: set[str] | None = None,
) -> None:
people = {
person.meta['parliamentarian_id']: person
for person in session.query(RISParliamentarian)
if person.meta.get('parliamentarian_id')
and (
allowed_people is None
or person.meta['parliamentarian_id'] in allowed_people
)
}
groups = {
group.meta['parliamentary_group_id']: group
for group in session.query(RISParliamentaryGroup)
if group.meta.get('parliamentary_group_id')
}
businesses = {
business.meta['self_id']: business
for business in session.query(PoliticalBusiness)
if business.meta.get('self_id')
}
for commission in session.query(RISCommission):
for source, function in commission.meta.get('people_ids', []):
person = people.get(source)
if person is None:
continue
existing = next(
(
membership
for membership in person.commission_memberships
if membership.commission_id == commission.id
),
None,
)
if existing is None:
person.commission_memberships.append(
RISCommissionMembership(
commission_id=commission.id,
function=function,
role=(
'president'
if function in ('Präsident', 'Präsidentin')
else 'member'
),
)
)
else:
existing.function = function
for group in groups.values():
for member in group.meta.get('people_ids', []):
source, function = (
member if isinstance(member, (list, tuple)) else (member, '')
)
person = people.get(source)
if person is None:
continue
role = next(
(
role
for role in person.roles
if role.parliamentary_group_id == group.id
),
None,
)
if role is None:
role = RISParliamentarianRole(parliamentary_group_id=group.id)
person.roles.append(role)
role.parliamentary_group_role = (
'president'
if function in ('Präsident', 'Präsidentin')
else 'member'
)
for business in businesses.values():
for source, function in business.meta.get('people_ids', []):
person = people.get(source)
if person is None:
if allowed_people is not None:
raise ValueError(f'Missing participant: {source}')
continue
matches = [
participation
for participation in business.participants
if participation.parliamentarian_id == person.id
and role_key(participation.participant_type)
== role_key(function)
]
if matches:
existing_participant = next(
(
p
for p in matches
if p.participant_type
in ('First signatory', 'Co-signatory', 'Author')
),
matches[0],
)
existing_participant.participant_type = function
for duplicate in matches:
if duplicate != existing_participant:
existing_participant.meta = {
**duplicate.meta,
**existing_participant.meta,
}
business.participants.remove(duplicate)
session.delete(duplicate)
else:
business.participants.append(
PoliticalBusinessParticipation(
parliamentarian_id=person.id,
participant_type=function,
)
)
for source in business.meta.get('parliamentary_group_ids', []):
linked_group = groups.get(source)
if (
linked_group is not None
and linked_group not in business.parliamentary_groups
):
business.parliamentary_groups.append(linked_group)
for item in session.query(MeetingItem):
ids = item.meeting.meta.get('ogi_agenda_business_ids', {}).get(
item.number or item.title
)
if ids is not None:
item.political_businesses = [
businesses[source] for source in ids if source in businesses
]
linked_business = businesses.get(item.political_business_link_id or '')
if linked_business is not None:
item.political_business = linked_business