0d30c58bf0
Snapshot of the working tree on srv1071960 (branch main), taken during server cleanup. .env files, node_modules, builds, logs and media were excluded.
246 lines
11 KiB
Python
246 lines
11 KiB
Python
"""The Daily Call Report and its call rows — the core artefact of the MVP.
|
|
|
|
Each working day an MR files two reports (see ``ReportType``): a morning PLAN of the
|
|
doctors and chemists they intend to visit, and an end-of-day ACTUAL report of the visits
|
|
that happened. Both share this table and its call rows; the ACTUAL report points at its
|
|
plan, and records why any planned visit was missed in ``missed_visit``.
|
|
|
|
An Area Business Manager files a SELF report of their own visits in the same table; ``mr_user_id``
|
|
is then the Area Business Manager (the report's owner), and submitting it makes it final."""
|
|
|
|
import uuid
|
|
from datetime import date, datetime, time
|
|
|
|
from sqlalchemy import (
|
|
CheckConstraint,
|
|
Date,
|
|
DateTime,
|
|
Enum,
|
|
ForeignKey,
|
|
String,
|
|
Text,
|
|
Time,
|
|
UniqueConstraint,
|
|
)
|
|
from sqlalchemy.dialects.postgresql import ARRAY
|
|
from sqlalchemy.dialects.postgresql import UUID as PGUUID
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
|
|
|
from app.common.enums import CallOutcome, ConversionStatus, DCRStatus, ReportType, Role
|
|
from app.database.base import Base, TimestampMixin, UUIDMixin
|
|
|
|
|
|
class DailyCallReport(Base, UUIDMixin, TimestampMixin):
|
|
__tablename__ = "daily_call_report"
|
|
__table_args__ = (
|
|
# V1 / A9 — ONE report of each type per MR per calendar date. Enforced in the
|
|
# database and not only in the service, so a double-submit race cannot create two.
|
|
UniqueConstraint("mr_user_id", "report_date", "report_type", name="uq_dcr_mr_date_type"),
|
|
# A plan is followed by at most one end-of-day report.
|
|
UniqueConstraint("plan_id", name="uq_dcr_plan_id"),
|
|
# Only an end-of-day report points at a plan.
|
|
CheckConstraint("report_type = 'ACTUAL' OR plan_id IS NULL", name="ck_dcr_plan_link"),
|
|
# An approved report must carry its approval timestamp, and nothing else may.
|
|
CheckConstraint(
|
|
"(status = 'APPROVED' AND approved_at IS NOT NULL) "
|
|
"OR (status <> 'APPROVED' AND approved_at IS NULL)",
|
|
name="ck_dcr_approved_at",
|
|
),
|
|
)
|
|
|
|
mr_user_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("user.id", ondelete="RESTRICT"), nullable=False
|
|
)
|
|
#: SNAPSHOT of the MR's headquarters at creation time. If the MR is later
|
|
#: transferred, historical reports keep the HQ they were filed under (A1).
|
|
headquarters_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("headquarters.id", ondelete="RESTRICT"), nullable=False
|
|
)
|
|
report_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
report_type: Mapped[ReportType] = mapped_column(
|
|
Enum(ReportType, name="report_type_enum", native_enum=False, length=10),
|
|
default=ReportType.ACTUAL,
|
|
nullable=False,
|
|
)
|
|
#: The approved morning plan an end-of-day report is filed against. NULL for plans,
|
|
#: and for end-of-day reports filed before plans existed.
|
|
plan_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("daily_call_report.id", ondelete="RESTRICT")
|
|
)
|
|
status: Mapped[DCRStatus] = mapped_column(
|
|
Enum(DCRStatus, name="dcr_status_enum", native_enum=False, length=20),
|
|
default=DCRStatus.DRAFT,
|
|
nullable=False,
|
|
)
|
|
|
|
#: SNAPSHOT of the reviewing ABM / overseeing AGM, so an HQ handover never rewrites
|
|
#: history (report §5.5).
|
|
current_am_user_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("user.id", ondelete="RESTRICT")
|
|
)
|
|
current_gm_user_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("user.id", ondelete="RESTRICT")
|
|
)
|
|
|
|
mr_remarks: Mapped[str | None] = mapped_column(Text)
|
|
#: The printed form's "STOCKIST VISIT" and "ORDER" lines.
|
|
stockist_visit: Mapped[str | None] = mapped_column(String(200))
|
|
order_value: Mapped[str | None] = mapped_column(String(100))
|
|
return_reason: Mapped[str | None] = mapped_column(Text)
|
|
#: Which role sent it back — data, not a separate status (report §7.2).
|
|
returned_by_role: Mapped[Role | None] = mapped_column(
|
|
Enum(Role, name="role_enum", native_enum=False, length=10)
|
|
)
|
|
|
|
submitted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
pdf_path: Mapped[str | None] = mapped_column(String(500))
|
|
|
|
mr: Mapped["User"] = relationship(foreign_keys=[mr_user_id]) # noqa: F821
|
|
headquarters: Mapped["Headquarters"] = relationship() # noqa: F821
|
|
doctor_calls: Mapped[list["DoctorCall"]] = relationship(
|
|
back_populates="dcr", cascade="all, delete-orphan", lazy="selectin"
|
|
)
|
|
chemist_calls: Mapped[list["ChemistCall"]] = relationship(
|
|
back_populates="dcr", cascade="all, delete-orphan", lazy="selectin"
|
|
)
|
|
stockist_calls: Mapped[list["StockistCall"]] = relationship(
|
|
back_populates="dcr", cascade="all, delete-orphan", lazy="selectin"
|
|
)
|
|
missed_visits: Mapped[list["MissedVisit"]] = relationship(
|
|
back_populates="dcr", cascade="all, delete-orphan", lazy="selectin"
|
|
)
|
|
#: Loaded explicitly by the repository (self-referential, so never eagerly by default).
|
|
plan: Mapped["DailyCallReport | None"] = relationship(
|
|
remote_side="DailyCallReport.id", foreign_keys=[plan_id], back_populates="actual"
|
|
)
|
|
actual: Mapped["DailyCallReport | None"] = relationship(
|
|
foreign_keys=[plan_id], back_populates="plan", uselist=False
|
|
)
|
|
|
|
@property
|
|
def is_locked(self) -> bool:
|
|
"""APPROVED is the lock. There is no separate LOCKED status (report §7.2)."""
|
|
return self.status == DCRStatus.APPROVED
|
|
|
|
|
|
class DoctorCall(Base, UUIDMixin, TimestampMixin):
|
|
__tablename__ = "doctor_call"
|
|
__table_args__ = (
|
|
# V5 / A11 — a doctor may appear only once per report.
|
|
UniqueConstraint("dcr_id", "doctor_id", name="uq_call_dcr_doctor"),
|
|
)
|
|
|
|
dcr_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True),
|
|
ForeignKey("daily_call_report.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
)
|
|
doctor_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("doctor.id", ondelete="RESTRICT"), nullable=False
|
|
)
|
|
#: On a plan this is the planned time; on an end-of-day report, when the visit happened.
|
|
visit_time: Mapped[time] = mapped_column(Time, nullable=False)
|
|
#: Required on an end-of-day report; a planned visit has no outcome yet (NULL).
|
|
call_outcome: Mapped[CallOutcome | None] = mapped_column(
|
|
Enum(CallOutcome, name="call_outcome_enum", native_enum=False, length=20)
|
|
)
|
|
#: V7 — only permitted when the doctor is a core doctor.
|
|
conversion_status: Mapped[ConversionStatus | None] = mapped_column(
|
|
Enum(ConversionStatus, name="conversion_status_enum", native_enum=False, length=20)
|
|
)
|
|
#: The printed form's "PRODUCT PROMOTE" and "PRODUCT SAMPLE" columns; several per visit.
|
|
product_promote: Mapped[list[str]] = mapped_column(
|
|
ARRAY(String(200)), default=list, server_default="{}", nullable=False
|
|
)
|
|
product_sample: Mapped[list[str]] = mapped_column(
|
|
ARRAY(String(200)), default=list, server_default="{}", nullable=False
|
|
)
|
|
#: A gift given to the doctor on this visit, typed in by the MR.
|
|
gift: Mapped[str | None] = mapped_column(String(200))
|
|
remarks: Mapped[str | None] = mapped_column(Text)
|
|
|
|
dcr: Mapped["DailyCallReport"] = relationship(back_populates="doctor_calls")
|
|
doctor: Mapped["Doctor"] = relationship(lazy="selectin") # noqa: F821
|
|
|
|
|
|
class ChemistCall(Base, UUIDMixin, TimestampMixin):
|
|
__tablename__ = "chemist_call"
|
|
__table_args__ = (
|
|
UniqueConstraint("dcr_id", "chemist_id", name="uq_call_dcr_chemist"),
|
|
)
|
|
|
|
dcr_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True),
|
|
ForeignKey("daily_call_report.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
)
|
|
chemist_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("chemist.id", ondelete="RESTRICT"), nullable=False
|
|
)
|
|
visit_time: Mapped[time] = mapped_column(Time, nullable=False)
|
|
#: The printed form's "POB" column.
|
|
pob: Mapped[str | None] = mapped_column(String(100))
|
|
remarks: Mapped[str | None] = mapped_column(Text)
|
|
|
|
dcr: Mapped["DailyCallReport"] = relationship(back_populates="chemist_calls")
|
|
chemist: Mapped["Chemist"] = relationship(lazy="selectin") # noqa: F821
|
|
|
|
|
|
class StockistCall(Base, UUIDMixin, TimestampMixin):
|
|
__tablename__ = "stockist_call"
|
|
__table_args__ = (
|
|
UniqueConstraint("dcr_id", "stockist_id", name="uq_call_dcr_stockist"),
|
|
)
|
|
|
|
dcr_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True),
|
|
ForeignKey("daily_call_report.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
)
|
|
stockist_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("stockist.id", ondelete="RESTRICT"), nullable=False
|
|
)
|
|
visit_time: Mapped[time] = mapped_column(Time, nullable=False)
|
|
#: Order booked with the stockist on this visit.
|
|
pob: Mapped[str | None] = mapped_column(String(100))
|
|
remarks: Mapped[str | None] = mapped_column(Text)
|
|
|
|
dcr: Mapped["DailyCallReport"] = relationship(back_populates="stockist_calls")
|
|
stockist: Mapped["Stockist"] = relationship(lazy="selectin") # noqa: F821
|
|
|
|
|
|
class MissedVisit(Base, UUIDMixin, TimestampMixin):
|
|
"""Why a doctor, chemist or stockist on the approved plan was not visited that day.
|
|
|
|
Belongs to the end-of-day report; exactly one of doctor/chemist/stockist is set.
|
|
"""
|
|
|
|
__tablename__ = "missed_visit"
|
|
__table_args__ = (
|
|
UniqueConstraint("dcr_id", "doctor_id", name="uq_missed_dcr_doctor"),
|
|
UniqueConstraint("dcr_id", "chemist_id", name="uq_missed_dcr_chemist"),
|
|
UniqueConstraint("dcr_id", "stockist_id", name="uq_missed_dcr_stockist"),
|
|
CheckConstraint(
|
|
"num_nonnulls(doctor_id, chemist_id, stockist_id) = 1", name="ck_missed_one_target"
|
|
),
|
|
)
|
|
|
|
dcr_id: Mapped[uuid.UUID] = mapped_column(
|
|
PGUUID(as_uuid=True),
|
|
ForeignKey("daily_call_report.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
)
|
|
doctor_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("doctor.id", ondelete="RESTRICT")
|
|
)
|
|
chemist_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("chemist.id", ondelete="RESTRICT")
|
|
)
|
|
stockist_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
PGUUID(as_uuid=True), ForeignKey("stockist.id", ondelete="RESTRICT")
|
|
)
|
|
reason: Mapped[str] = mapped_column(Text, nullable=False)
|
|
|
|
dcr: Mapped["DailyCallReport"] = relationship(back_populates="missed_visits")
|