zkxw/backend/app/crud/protocol_sample_collection.py

"""采集协议样本采集信息列表 CRUD（样本信息管理 · 样本采集信息 Tab）"""
from __future__ import annotations

import math
from datetime import datetime
from typing import Any, Dict, List, Optional, Tuple

from sqlalchemy import and_, or_
from sqlalchemy.orm import Session

from app.core.utils import format_date
from app.utils.sample_special import sample_special_to_dict
from app.models.models import (
    BizCellSampleType,
    BizCollectionHospital,
    BizCollectionProtocol,
    BizCollectionRegion,
    BizProtocolCollectionInfo,
    BizProtocolPartyCustomer,
    BizProtocolSample,
)


def total_pages(total: int, page_size: int) -> int:
    if total <= 0:
        return 0
    return int(math.ceil(total / float(page_size)))


def _fmt_dt(t: Optional[datetime]) -> str:
    return t.strftime("%Y-%m-%d %H:%M:%S") if t else ""


def _sample_status_label(status: str) -> str:
    return {
        "ycj": "已采集",
        "jcz": "检测中",
        "yrk": "已入库",
        "yzf": "已作废",
    }.get(status, "未采集" if not status else status)


def _row_to_dict(
    *,
    protocol_sample_id: str,
    collection_protocol_id: str,
    protocol_no: str,
    protocol_status: str,
    expected_delivery_date: str,
    mother_name: str,
    newborn_name: str,
    sample_code: str,
    sample_type_name: str,
    sample_status: str,
    sample_status_label: str,
    region_name: str,
    hospital_name: str,
    medical_staff: str,
    sample_collect_time: str,
    maternal_blood_collect_time: str,
) -> Dict[str, Any]:
    return {
        "protocolSampleId": protocol_sample_id,
        "collectionProtocolId": collection_protocol_id,
        "protocolNo": protocol_no,
        "protocolStatus": protocol_status,
        "expectedDeliveryDate": expected_delivery_date,
        "motherName": mother_name,
        "newbornName": newborn_name,
        "sampleCode": sample_code,
        "sampleTypeName": sample_type_name,
        "sampleStatus": sample_status,
        "sampleStatusLabel": sample_status_label,
        "regionName": region_name,
        "hospitalName": hospital_name,
        "medicalStaff": medical_staff,
        "sampleCollectTime": sample_collect_time,
        "maternalBloodCollectTime": maternal_blood_collect_time,
    }


def _base_query(db: Session, tenant_no: str):
    return (
        db.query(
            BizProtocolSample,
            BizCollectionProtocol,
            BizProtocolPartyCustomer,
            BizProtocolCollectionInfo,
        )
        .join(
            BizCollectionProtocol,
            and_(
                BizCollectionProtocol.id == BizProtocolSample.collection_protocol_id,
                BizCollectionProtocol.del_flag == 0,
            ),
        )
        .outerjoin(
            BizProtocolPartyCustomer,
            and_(
                BizProtocolPartyCustomer.collection_protocol_id == BizCollectionProtocol.id,
                BizProtocolPartyCustomer.del_flag == 0,
            ),
        )
        .outerjoin(
            BizProtocolCollectionInfo,
            and_(
                BizProtocolCollectionInfo.collection_protocol_id == BizCollectionProtocol.id,
                BizProtocolCollectionInfo.del_flag == 0,
            ),
        )
        .filter(
            BizProtocolSample.tenant_no == tenant_no,
            BizProtocolSample.del_flag == 0,
        )
    )


def _apply_sample_status_filter(q, sample_status: Optional[str]):
    st = (sample_status or "").strip()
    if not st or st == "all":
        return q
    if st == "uncollected":
        return q.filter(
            or_(
                BizProtocolSample.sample_status.is_(None),
                BizProtocolSample.sample_status == "",
            )
        )
    if st == "collected":
        return q.filter(BizProtocolSample.sample_status.in_(["ycj", "jcz", "yrk"]))
    if st == "voided":
        return q.filter(BizProtocolSample.sample_status == "yzf")
    return q.filter(BizProtocolSample.sample_status == st)


def _apply_reception_flag_filter(q, flag: Optional[str], *, sample_code_col, tenant_no: str, source: str):
    from sqlalchemy import exists

    from app.models.models import BizSampleReception

    f = (flag or "").strip()
    if not f:
        return q
    base = and_(
        BizSampleReception.tenant_no == tenant_no,
        BizSampleReception.del_flag == 0,
        BizSampleReception.reception_source == source,
        BizSampleReception.sample_code == sample_code_col,
    )
    if f == "js":
        return q.filter(exists().where(and_(base, BizSampleReception.accept_conclusion == "js")))
    if f == "bj":
        return q.filter(exists().where(and_(base, BizSampleReception.accept_conclusion == "bj")))
    if f == "none":
        return q.filter(
            ~exists().where(and_(base, BizSampleReception.accept_conclusion.in_(["js", "bj"])))
        )
    return q


def _apply_qc_flag_filter(q, flag: Optional[str], *, sample_code_col, tenant_no: str, source: str):
    from sqlalchemy import exists

    from app.models.models import BizQcWorkOrder

    f = (flag or "").strip()
    if not f:
        return q
    base = and_(
        BizQcWorkOrder.tenant_no == tenant_no,
        BizQcWorkOrder.del_flag == 0,
        BizQcWorkOrder.reception_source == source,
        BizQcWorkOrder.trigger_source == "reception",
        BizQcWorkOrder.sample_code == sample_code_col,
    )
    if f == "complete":
        return q.filter(exists().where(and_(base, BizQcWorkOrder.conclusion.in_(["tg", "btg"]))))
    if f == "voided":
        return q.filter(exists().where(and_(base, BizQcWorkOrder.work_order_status == "yzf")))
    if f == "incomplete":
        return q.filter(
            ~exists().where(
                and_(
                    base,
                    or_(
                        BizQcWorkOrder.conclusion.in_(["tg", "btg"]),
                        BizQcWorkOrder.work_order_status == "yzf",
                    ),
                )
            )
        )
    return q


def _apply_storage_flag_filter(q, flag: Optional[str], *, sample_code_col, tenant_no: str, source: str):
    from sqlalchemy import exists

    from app.models.models import BizStorageReleaseReview

    f = (flag or "").strip()
    if not f:
        return q
    base = and_(
        BizStorageReleaseReview.tenant_no == tenant_no,
        BizStorageReleaseReview.del_flag == 0,
        BizStorageReleaseReview.reception_source == source,
        BizStorageReleaseReview.sample_code == sample_code_col,
    )
    if f == "yrk":
        return q.filter(exists().where(and_(base, BizStorageReleaseReview.storage_status == "yrk")))
    if f == "rejected":
        return q.filter(
            exists().where(
                and_(
                    base,
                    BizStorageReleaseReview.storage_status != "yrk",
                    BizStorageReleaseReview.review_conclusion == "btg",
                )
            )
        )
    if f == "none":
        return q.filter(
            ~exists().where(
                and_(
                    base,
                    or_(
                        BizStorageReleaseReview.storage_status == "yrk",
                        BizStorageReleaseReview.review_conclusion == "btg",
                    ),
                )
            )
        )
    return q


def list_protocol_sample_collections(
    db: Session,
    *,
    page: int = 1,
    page_size: int = 10,
    tenant_no: str,
    keyword: Optional[str] = None,
    sample_status: Optional[str] = None,
    collection_flag: Optional[str] = None,
    reception_flag: Optional[str] = None,
    qc_flag: Optional[str] = None,
    storage_flag: Optional[str] = None,
    storage_cert_handled: Optional[str] = None,
    insurance_handled: Optional[str] = None,
    user_info=None,
) -> Tuple[List[Dict[str, Any]], int]:
    if not tenant_no:
        return [], 0

    q = _base_query(db, tenant_no)
    q = _apply_sample_status_filter(q, collection_flag or sample_status)
    q = _apply_reception_flag_filter(
        q, reception_flag, sample_code_col=BizProtocolSample.sample_code, tenant_no=tenant_no, source="cell_storage"
    )
    q = _apply_qc_flag_filter(
        q, qc_flag, sample_code_col=BizProtocolSample.sample_code, tenant_no=tenant_no, source="cell_storage"
    )
    q = _apply_storage_flag_filter(
        q, storage_flag, sample_code_col=BizProtocolSample.sample_code, tenant_no=tenant_no, source="cell_storage"
    )
    from app.models.models import BizProtocolFullmoonTodo, BizProtocolStorageCert

    sch = (storage_cert_handled or "").strip()
    if sch in ("1", "0", "true", "false"):
        want = sch in ("1", "true")
        handled_pred = or_(
            BizProtocolStorageCert.handle_time.isnot(None),
            BizProtocolStorageCert.make_status == "1",
            and_(BizProtocolStorageCert.make_date.isnot(None), BizProtocolStorageCert.make_date != ""),
        )
        q = q.outerjoin(
            BizProtocolStorageCert,
            and_(
                BizProtocolStorageCert.collection_protocol_id == BizCollectionProtocol.id,
                BizProtocolStorageCert.tenant_no == tenant_no,
                BizProtocolStorageCert.del_flag == 0,
            ),
        )
        q = q.filter(handled_pred) if want else q.filter(~handled_pred)
    ih = (insurance_handled or "").strip()
    if ih in ("1", "0", "true", "false"):
        want = ih in ("1", "true")
        handled_pred = or_(
            BizProtocolFullmoonTodo.handle_time.isnot(None),
            BizProtocolFullmoonTodo.todo_status == "1",
            and_(BizProtocolFullmoonTodo.handle_date.isnot(None), BizProtocolFullmoonTodo.handle_date != ""),
        )
        q = q.outerjoin(
            BizProtocolFullmoonTodo,
            and_(
                BizProtocolFullmoonTodo.collection_protocol_id == BizCollectionProtocol.id,
                BizProtocolFullmoonTodo.tenant_no == tenant_no,
                BizProtocolFullmoonTodo.del_flag == 0,
            ),
        )
        q = q.filter(handled_pred) if want else q.filter(~handled_pred)

    if keyword and keyword.strip():
        kw = f"%{keyword.strip()}%"
        kw_conds = [
            BizProtocolPartyCustomer.mother_name.like(kw),
            BizProtocolSample.sample_code.like(kw),
        ]
        from app.crud import sample_reception as reception_svc

        lab_codes = reception_svc.sample_codes_matching_lab_storage_keyword(
            db, tenant_no, keyword, reception_source="cell_storage"
        )
        if lab_codes:
            kw_conds.append(BizProtocolSample.sample_code.in_(lab_codes))
        q = q.filter(or_(*kw_conds))

    if user_info:
        from app.core.data_scope import apply_data_scope

        q = apply_data_scope(q, BizProtocolSample, user_info, db, tenant_no)

    total = q.count()
    page = max(1, page)
    page_size = min(max(1, page_size), 100)

    rows = (
        q.order_by(
            BizProtocolPartyCustomer.expected_delivery_date.is_(None),
            BizProtocolPartyCustomer.expected_delivery_date.desc(),
            BizCollectionProtocol.create_time.desc(),
            BizProtocolSample.id.desc(),
        )
        .offset((page - 1) * page_size)
        .limit(page_size)
        .all()
    )

    sample_type_ids = list({
        ps.sample_type_id for ps, _, _, _ in rows if ps and ps.sample_type_id
    })
    type_map: Dict[str, str] = {}
    if sample_type_ids:
        for t in (
            db.query(BizCellSampleType)
            .filter(BizCellSampleType.id.in_(sample_type_ids), BizCellSampleType.del_flag == 0)
            .all()
        ):
            type_map[t.id] = t.type_name or ""

    region_ids = list({
        (ci.region_id or "").strip()
        for _, _, _, ci in rows
        if ci and (ci.region_id or "").strip()
    })
    hospital_ids = list({
        (ci.hospital_id or "").strip()
        for _, _, _, ci in rows
        if ci and (ci.hospital_id or "").strip()
    })
    region_name_map: Dict[str, str] = {}
    hospital_name_map: Dict[str, str] = {}
    if region_ids:
        for r in (
            db.query(BizCollectionRegion.id, BizCollectionRegion.region_name)
            .filter(BizCollectionRegion.id.in_(region_ids), BizCollectionRegion.del_flag == 0)
            .all()
        ):
            region_name_map[r.id] = r.region_name or ""
    if hospital_ids:
        for h in (
            db.query(BizCollectionHospital.id, BizCollectionHospital.hospital_name)
            .filter(BizCollectionHospital.id.in_(hospital_ids), BizCollectionHospital.del_flag == 0)
            .all()
        ):
            hospital_name_map[h.id] = h.hospital_name or ""

    items: List[Dict[str, Any]] = []
    for psample, proto, party, ci in rows:
        status = (psample.sample_status or "").strip() if psample else ""
        region_id = (ci.region_id or "").strip() if ci else ""
        hospital_id = (ci.hospital_id or "").strip() if ci else ""
        item = _row_to_dict(
                protocol_sample_id=psample.id,
                collection_protocol_id=proto.id if proto else "",
                protocol_no=(proto.protocol_no or "") if proto else "",
                protocol_status=(proto.protocol_status or "") if proto else "",
                expected_delivery_date=format_date(party.expected_delivery_date) if party else "",
                mother_name=(party.mother_name or "") if party else "",
                newborn_name=(party.newborn_name or "") if party else "",
                sample_code=psample.sample_code or "",
                sample_type_name=type_map.get(psample.sample_type_id or "", ""),
                sample_status=status,
                sample_status_label=_sample_status_label(status),
                region_name=region_name_map.get(region_id, ""),
                hospital_name=hospital_name_map.get(hospital_id, ""),
                medical_staff=(ci.medical_staff or "") if ci else "",
                sample_collect_time=_fmt_dt(ci.sample_collect_time) if ci else "",
                maternal_blood_collect_time=_fmt_dt(ci.maternal_blood_collect_time) if ci else "",
            )
        item.update(sample_special_to_dict(psample))
        # 客户服务宽表：当事人 / 协议附件 / 备注 / 市场专员 id
        item["motherPhone"] = (party.mother_phone or "") if party else ""
        item["motherIdCard"] = (party.mother_id_card or "") if party else ""
        item["fatherName"] = (party.father_name or "") if party else ""
        item["fatherPhone"] = (party.father_phone or "") if party else ""
        item["fatherIdCard"] = (party.father_id_card or "") if party else ""
        item["newbornIdCard"] = (party.newborn_id_card or "") if party else ""
        item["newbornSex"] = (party.newborn_sex or "") if party else ""
        item["newbornDeliveryTime"] = _fmt_dt(party.newborn_delivery_time) if party else ""
        item["protocolRemark"] = (proto.remark or "") if proto else ""
        item["protocolAttachments"] = (proto.protocol_attachments or "") if proto else ""
        item["consentFormUrls"] = (proto.consent_form_urls or "") if proto else ""
        item["healthSurveyFormUrls"] = (proto.health_survey_form_urls or "") if proto else ""
        item["salesUserId"] = (ci.sales_user_id or "") if ci else ""
        items.append(item)
    if items and tenant_no:
        from app.crud import sample_reception as reception_svc
        from app.crud.storage_release_review import _get_user_name
        from app.models.models import BizProtocolSamplePayment

        codes = [i["sampleCode"] for i in items if i.get("sampleCode")]
        code_map = reception_svc.get_lab_storage_code_map_by_sample_codes(
            db, tenant_no, codes, reception_source="cell_storage"
        )
        pay_mode_map: Dict[str, str] = {}
        if codes:
            for sp in (
                db.query(BizProtocolSamplePayment)
                .filter(
                    BizProtocolSamplePayment.tenant_no == tenant_no,
                    BizProtocolSamplePayment.del_flag == 0,
                    BizProtocolSamplePayment.sample_code.in_(codes),
                )
                .all()
            ):
                sc = (sp.sample_code or "").strip()
                if sc and sc not in pay_mode_map:
                    pay_mode_map[sc] = (sp.pay_mode or "").strip()
        sales_ids = list({(i.get("salesUserId") or "").strip() for i in items if i.get("salesUserId")})
        sales_name_map = {uid: _get_user_name(db, uid) for uid in sales_ids if uid}
        pay_mode_label = {"dj": "趸交", "fq": "分期"}
        for item in items:
            sc = (item.get("sampleCode") or "").strip()
            item["labStorageCode"] = code_map.get(sc, "") if sc else ""
            mode = pay_mode_map.get(sc, "")
            item["payMode"] = mode
            item["payModeLabel"] = pay_mode_label.get(mode, mode or "")
            sid = (item.get("salesUserId") or "").strip()
            item["marketSpecialistName"] = sales_name_map.get(sid, "") if sid else ""
        from app.services.sample_info_list_enrich import enrich_sample_info_items

        enrich_sample_info_items(
            db, tenant_no, items, reception_source="cell_storage", include_handle_flags=True
        )
    return items, total