from uuid import uuid4
from onegov.core.orm.mixins import TimestampMixin
from sqlalchemy import Column, Text, Enum, Date, Integer, Boolean, Float
from sqlalchemy.orm import backref, relationship
from onegov.core.orm import Base
from onegov.core.orm.mixins import (ContentMixin, dict_property,
meta_property, content_property)
from onegov.core.orm.types import UUID
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.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 typing import Literal, TYPE_CHECKING
from ..utils import country_code_to_name
if TYPE_CHECKING:
import uuid
from collections.abc import Sequence
from datetime import date
from onegov.user import User
from typing import TypeAlias
from .certificate import LanguageCertificate
from .language import Language
[docs]
TranslatorState: TypeAlias = Literal['proposed', 'published']
AdmissionState: TypeAlias = Literal[
'uncertified', 'in_progress', 'certified'
]
Gender: TypeAlias = Literal['M', 'F', 'N']
InterpretingType: TypeAlias = Literal[
'simultaneous', 'consecutive', 'negotiation', 'whisper'
]
[docs]
class Translator(Base, TimestampMixin, AssociatedFiles, ContentMixin,
CoordinatesMixin, ORMSearchable):
[docs]
__tablename__ = 'translators'
[docs]
es_properties = {
'last_name': {'type': 'text'},
'first_name': {'type': 'text'},
'email': {'type': 'text'}
}
[docs]
id: 'Column[uuid.UUID]' = Column(
UUID, # type:ignore[arg-type]
primary_key=True,
default=uuid4
)
[docs]
state: 'Column[TranslatorState]' = Column(
Enum( # type:ignore[arg-type]
'proposed',
'published',
name='translator_state'
),
nullable=False,
default='published'
)
[docs]
first_name: 'Column[str]' = Column(Text, nullable=False)
[docs]
last_name: 'Column[str]' = Column(Text, nullable=False)
# Personal-Nr.
[docs]
pers_id: 'Column[int | None]' = Column(Integer)
# Zulassung / admission
[docs]
admission: 'Column[AdmissionState]' = Column(
Enum(*ADMISSIONS, name='admission_state'), # type:ignore[arg-type]
nullable=False,
default='uncertified'
)
# Quellensteuer
[docs]
withholding_tax: 'Column[bool]' = Column(Boolean, default=False)
# Selbständig
[docs]
self_employed: 'Column[bool]' = Column(Boolean, default=False)
[docs]
gender: 'Column[Gender | None]' = Column(
Enum(*GENDERS, name='gender') # type:ignore[arg-type]
)
[docs]
date_of_birth: 'Column[date | None]' = Column(Date)
# Nationalität (old)
[docs]
nationality: 'Column[str | None]' = Column(Text)
# Nationalitäten
[docs]
nationalities: dict_property[list[str] | None] = content_property()
# Fields concerning address
[docs]
address: 'Column[str | None]' = Column(Text)
[docs]
zip_code: 'Column[str | None]' = Column(Text)
[docs]
city: 'Column[str | None]' = Column(Text)
# Heimatort
[docs]
hometown: 'Column[str | None]' = Column(Text)
# distance calculated from address to a fixed point via api, im km
[docs]
drive_distance: 'Column[float | None]' = Column(
Float(precision=2) # type:ignore[arg-type]
)
# AHV-Nr.
[docs]
social_sec_number = Column(Text)
# Bank information
[docs]
bank_name: 'Column[str | None]' = Column(Text)
[docs]
bank_address: 'Column[str | None]' = Column(Text)
[docs]
account_owner: 'Column[str | None]' = Column(Text)
[docs]
iban: 'Column[str | None]' = Column(Text)
[docs]
email: 'Column[str | None]' = Column(Text, unique=True)
# the user account related to this translator
[docs]
user: 'relationship[User]' = relationship(
'User',
primaryjoin='foreign(Translator.email) == User.username',
uselist=False,
backref=backref('translator', uselist=False, passive_deletes='all')
)
[docs]
tel_mobile: 'Column[str | None]' = Column(Text)
[docs]
tel_private: 'Column[str | None]' = Column(Text)
[docs]
tel_office: 'Column[str | None]' = Column(Text)
[docs]
availability: 'Column[str | None]' = Column(Text)
[docs]
confirm_name_reveal: 'Column[bool | None]' = Column(Boolean, default=False)
# The translator applies to be in the directory and gets a decision
[docs]
date_of_application: 'Column[date | None]' = Column(Date)
[docs]
date_of_decision: 'Column[date | None]' = Column(Date)
# Language Information
[docs]
mother_tongues: 'relationship[list[Language]]' = relationship(
'Language',
secondary=mother_tongue_association_table,
back_populates='mother_tongues'
)
# Arbeitssprache - Wort
[docs]
spoken_languages: 'relationship[list[Language]]' = relationship(
'Language',
secondary=spoken_association_table,
back_populates='speakers'
)
# Arbeitssprache - Schrift
[docs]
written_languages: 'relationship[list[Language]]' = relationship(
'Language',
secondary=written_association_table,
back_populates='writers'
)
# Arbeitssprache - Kommunikationsüberwachung
[docs]
monitoring_languages: 'relationship[list[Language]]' = relationship(
'Language',
secondary=monitoring_association_table,
back_populates='monitors'
)
# Nachweis der Voraussetzungen
[docs]
proof_of_preconditions: 'Column[str | None]' = Column(Text)
# Referenzen Behörden
[docs]
agency_references: 'Column[str | None]' = Column(Text)
# Ausbildung Dolmetscher
[docs]
education_as_interpreter: 'Column[bool]' = Column(
Boolean,
default=False,
nullable=False
)
[docs]
certificates: 'relationship[list[LanguageCertificate]]' = relationship(
'LanguageCertificate',
secondary=certificate_association_table,
back_populates='owners')
# Bemerkungen
# field for hiding to users except admins
[docs]
for_admins_only: 'Column[bool]' = Column(
Boolean,
default=False,
nullable=False
)
# the below might never be used, but we import it if customer wants them
[docs]
profession: 'Column[str | None]' = Column(Text)
[docs]
occupation: 'Column[str | None]' = Column(Text)
[docs]
other_certificates: 'Column[str | None]' = Column(Text)
# Besondere Hinweise Einsatzmöglichkeiten
# List of types of interpreting the interpreter can do
[docs]
expertise_interpreting_types: 'dict_property[Sequence[InterpretingType]]'
expertise_interpreting_types = meta_property(default=tuple)
# List of types of professional guilds
[docs]
expertise_professional_guilds: 'dict_property[Sequence[str]]'
expertise_professional_guilds = meta_property(default=tuple)
[docs]
expertise_professional_guilds_other: 'dict_property[Sequence[str]]'
expertise_professional_guilds_other = 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: 'Column[bool]' = Column(Boolean, default=False, nullable=False)
@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.upper()} {self.last_name}'
@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 '-'