from __future__ import annotations
from datetime import date
from uuid import uuid4, UUID
from onegov.core.orm.mixins import TimestampMixin
from sqlalchemy import Enum, Float
from sqlalchemy.orm import backref, mapped_column, relationship, Mapped
from onegov.core.orm import Base
from onegov.core.orm.mixins import (ContentMixin, dict_property,
meta_property, content_property)
from onegov.file import AssociatedFiles
from onegov.gis import CoordinatesMixin
from onegov.search import ORMSearchable
from onegov.translator_directory.constants import ADMISSIONS, GENDERS
from onegov.translator_directory.i18n import _
from onegov.translator_directory.models.certificate import (
certificate_association_table
)
from onegov.translator_directory.models.language import (
mother_tongue_association_table, spoken_association_table,
written_association_table, monitoring_association_table
)
from ..utils import country_code_to_name
from typing import Literal, TYPE_CHECKING
if TYPE_CHECKING:
from collections.abc import Sequence
from onegov.user import User
from typing import TypeAlias
from .certificate import LanguageCertificate
from .language import Language
from .time_report import TranslatorTimeReport
[docs]
TranslatorState: TypeAlias = Literal['proposed', 'published']
[docs]
AdmissionState: TypeAlias = Literal[
'uncertified', 'in_progress', 'certified'
]
[docs]
Gender: TypeAlias = Literal['M', 'F', 'N']
[docs]
InterpretingType: TypeAlias = Literal[
'simultaneous', 'consecutive', 'negotiation', 'whisper'
]
[docs]
class Translator(Base, TimestampMixin, AssociatedFiles, ContentMixin,
CoordinatesMixin, ORMSearchable):
[docs]
__tablename__ = 'translators'
[docs]
fts_type_title = _('Translators')
[docs]
fts_title_property = '_title'
[docs]
fts_properties = {
# TODO: We may get better results if we use the fullname
# although we may have to supply the fullname without
# capitalization of the last name.
'last_name': {'type': 'text', 'weight': 'A'},
'first_name': {'type': 'text', 'weight': 'A'},
'email': {'type': 'text', 'weight': 'A'}
}
[docs]
id: Mapped[UUID] = mapped_column(
primary_key=True,
default=uuid4
)
[docs]
state: Mapped[TranslatorState] = mapped_column(
Enum(
'proposed',
'published',
name='translator_state'
),
default='published'
)
[docs]
first_name: Mapped[str]
# Personal-Nr.
[docs]
pers_id: Mapped[int | None]
# Vertragsnummer / AnsV-Nr. (Anstellungsverhältnis-Nr.)
[docs]
contract_number: Mapped[str | None]
# Zulassung / admission
[docs]
admission: Mapped[AdmissionState] = mapped_column(
Enum(*ADMISSIONS, name='admission_state'),
default='uncertified'
)
# Quellensteuer
# FIXME: These probably should've not been nullable
[docs]
withholding_tax: Mapped[bool] = mapped_column(nullable=True, default=False)
# Selbständig
# FIXME: These probably should've not been nullable
[docs]
self_employed: Mapped[bool] = mapped_column(nullable=True, default=False)
[docs]
gender: Mapped[Gender | None] = mapped_column(
Enum(*GENDERS, name='gender')
)
[docs]
date_of_birth: Mapped[date | None]
# Nationalitäten
[docs]
nationalities: dict_property[list[str] | None] = content_property()
# Fields concerning address
[docs]
address: Mapped[str | None]
[docs]
zip_code: Mapped[str | None]
[docs]
city: Mapped[str | None]
# Heimatort
[docs]
hometown: Mapped[str | None]
# distance calculated from address to a fixed point via api, im km
[docs]
drive_distance: Mapped[float | None] = mapped_column(
Float(precision=2)
)
# AHV-Nr.
[docs]
social_sec_number: Mapped[str | None]
# Bank information
[docs]
bank_name: Mapped[str | None]
[docs]
bank_address: Mapped[str | None]
[docs]
account_owner: Mapped[str | None]
[docs]
iban: Mapped[str | None]
[docs]
email: Mapped[str | None] = mapped_column(unique=True)
# the user account related to this translator
[docs]
user: Mapped[User] = relationship(
primaryjoin='foreign(Translator.email) == User.username',
uselist=False,
backref=backref('translator', uselist=False, passive_deletes='all')
)
[docs]
tel_mobile: Mapped[str | None]
[docs]
tel_private: Mapped[str | None]
[docs]
tel_office: Mapped[str | None]
[docs]
availability: Mapped[str | None]
[docs]
confirm_name_reveal: Mapped[bool | None] = mapped_column(default=False)
# The translator applies to be in the directory and gets a decision
[docs]
date_of_application: Mapped[date | None]
[docs]
date_of_decision: Mapped[date | None]
# Language Information
[docs]
mother_tongues: Mapped[list[Language]] = relationship(
secondary=mother_tongue_association_table,
back_populates='mother_tongues'
)
# Arbeitssprache - Wort
[docs]
spoken_languages: Mapped[list[Language]] = relationship(
secondary=spoken_association_table,
back_populates='speakers'
)
# Arbeitssprache - Schrift
[docs]
written_languages: Mapped[list[Language]] = relationship(
secondary=written_association_table,
back_populates='writers'
)
# Arbeitssprache - Kommunikationsüberwachung
[docs]
monitoring_languages: Mapped[list[Language]] = relationship(
secondary=monitoring_association_table,
back_populates='monitors'
)
# Nachweis der Voraussetzungen
[docs]
proof_of_preconditions: Mapped[str | None]
# Referenzen Behörden
[docs]
agency_references: Mapped[str | None]
# Ausbildung Dolmetscher
[docs]
education_as_interpreter: Mapped[bool] = mapped_column(default=False)
[docs]
certificates: Mapped[list[LanguageCertificate]] = relationship(
secondary=certificate_association_table,
back_populates='owners'
)
# Time reports for this translator
[docs]
time_reports: Mapped[list[TranslatorTimeReport]] = relationship(
back_populates='translator',
order_by='TranslatorTimeReport.assignment_date.desc()',
cascade='all, delete-orphan',
)
# Bemerkungen
# field for hiding to users except admins
[docs]
for_admins_only: Mapped[bool] = mapped_column(default=False)
# the below might never be used, but we import it if customer wants them
[docs]
profession: Mapped[str | None]
[docs]
occupation: Mapped[str | None]
[docs]
other_certificates: Mapped[str | None]
# Besondere Hinweise Einsatzmöglichkeiten
# List of types of interpreting the interpreter can do
[docs]
expertise_interpreting_types: dict_property[Sequence[InterpretingType]] = (
meta_property(default=tuple)
)
# List of types of professional guilds
[docs]
expertise_professional_guilds: dict_property[Sequence[str]] = (
meta_property(default=tuple)
)
[docs]
expertise_professional_guilds_other: dict_property[Sequence[str]] = (
meta_property(default=tuple)
)
@property
[docs]
def expertise_professional_guilds_all(self) -> Sequence[str]:
return (
tuple(self.expertise_professional_guilds or ())
+ tuple(self.expertise_professional_guilds_other or ())
)
# If entry was imported, for the form and the expertise fields
[docs]
imported: Mapped[bool] = mapped_column(default=False)
@property
[docs]
def _title(self) -> str:
""" The title used for searching. """
return f'{self.first_name} {self.last_name}'
@property
[docs]
def title(self) -> str:
""" Returns title with lastname in uppercase. """
return f'{self.last_name.upper()}, {self.first_name}'
@property
[docs]
def lead(self) -> str:
return ', '.join({
*(la.name for la in self.written_languages or []),
*(la.name for la in self.spoken_languages or [])
})
@property
[docs]
def full_name(self) -> str:
""" Returns the full name with lastname in uppercase. """
return f'{self.first_name} {self.last_name.upper()}'
@property
[docs]
def unique_categories(self) -> list[str]:
return sorted({f.note for f in self.files if f.note is not None})
[docs]
def nationalities_as_text(
self, locale: str,
country_codes: list[str] | None = None
) -> str:
"""
Returns the translators nationalities as text, translated to the given
locale.
If `country_codes` e.g. ['CH'] is given, the given codes are
translated to country names instead.
"""
mapping = country_code_to_name(locale)
if country_codes:
return ', '.join(mapping.get(code, code) for code in country_codes)
return ', '.join(
mapping.get(nat, nat)
for nat in self.nationalities) if self.nationalities else '-'