Compare commits

...
Author SHA1 Message Date
zlwuandCursor 0346bc0b1d fix(qc): 样本接收废弃同步该样本状态为已作废
单条接收结论为废弃时仅将该样本置 yzf;改回接收且协议仍有效时清空作废状态;全部废弃仍自动作废协议。补充历史回刷 SQL(#129)。

Co-authored-by: Cursor <cursoragent@cursor.com>
2026-11-19 13:14:10 +08:00
cykj 76ddb81818 Merge pull request 'fix(protocol): 协议作废/属地废弃同步样本状态为已作废 (#127)' (#128)
fix(protocol): 协议作废/属地废弃同步样本状态为已作废 (#127)
2026-11-19 04:33:48 +00:00
zlwuandCursor 3f16a8ab3e fix(protocol): 协议作废/属地废弃同步样本状态为已作废
协议变为 yzf 或 sdfq 时,除样本接收废弃联动外,将下属有效样本 sample_status 置为 yzf 并写入 void_date;补充历史回刷 SQL 与单测(#127)。

Co-authored-by: Cursor <cursoragent@cursor.com>
2026-11-19 12:31:57 +08:00
cykj 14e733faf0 Merge pull request 'feat(oa): 收款账户组件支持图标编辑与删除 (#110)' (#126) from codex/issue-110-receive-account-edit-07c5b0874c18 into V2.0
feat(oa): 收款账户组件支持图标编辑与删除 (#110)

Merge pull request #126
2026-11-19 04:05:04 +00:00
7 changed files with 634 additions and 32 deletions

No files matched your search

+1 -1
View File
@@ -2677,7 +2677,7 @@ def update_collection_protocol(
next_status = protocol_status.strip()
assert_protocol_status_transition(obj.protocol_status or "", next_status, kind="protocol")
obj.protocol_status = next_status
if next_status == "yzf" and from_status != "yzf":
if next_status in ("yzf", "sdfq") and from_status not in ("yzf", "sdfq"):
voided_now = True
if next_status == "yzf" and void_time is not None:
if void_type is None or not str(void_type).strip():
+2 -1
View File
@@ -1702,7 +1702,8 @@ def change_status(
)
db.add(obj)
if ns == "yzf" and (current or "").strip() != "yzf":
from_status = (current or "").strip()
if ns in ("yzf", "sdfq") and from_status not in ("yzf", "sdfq"):
from app.services.protocol_reception_discard_sync import after_protocol_voided
after_protocol_voided(
+8 -10
View File
@@ -1045,12 +1045,11 @@ def create_sample_reception(
user_id=user_id,
create_dept_id=create_dept_id or "",
)
if conclusion == "bj":
from app.services.protocol_reception_discard_sync import after_sample_reception_saved
from app.services.protocol_reception_discard_sync import after_sample_reception_saved
after_sample_reception_saved(
db, reception=obj, tenant_no=tenant_no, user_id=user_id
)
after_sample_reception_saved(
db, reception=obj, tenant_no=tenant_no, user_id=user_id
)
db.commit()
db.refresh(obj)
return obj
@@ -1169,12 +1168,11 @@ def update_sample_reception(
)
check_result_svc.sync_legacy_columns_from_results(db, rec, saved_rows)
db.add(rec)
if conclusion == "bj":
from app.services.protocol_reception_discard_sync import after_sample_reception_saved
from app.services.protocol_reception_discard_sync import after_sample_reception_saved
after_sample_reception_saved(
db, reception=rec, tenant_no=tenant_no, user_id=user_id
)
after_sample_reception_saved(
db, reception=rec, tenant_no=tenant_no, user_id=user_id
)
db.commit()
db.refresh(rec)
return rec
@@ -1,16 +1,21 @@
"""市场协议作废 ↔ 质检样本接收废弃双向联动。
"""市场协议无效态 ↔ 样本接收废弃 / 样本状态作废联动。
规则(细胞存储 / 科研捐赠):
1. 协议变为已作废(yzf)时,协议下全部样本接收结论自动为废弃(bj)。
2. 协议下全部有效样本均已废弃/拒收(bj)时,协议自动已作废,作废类型为「实验室废弃」。
1. 单条样本接收结论为废弃(bj)时:仅该样本 `sample_status` 置为已作废(yzf)并写 `void_date`;
协议状态保持不变。接收结论改回接收(js)且协议仍有效时:若该样本为 yzf,则清空状态与 void_date。
2. 协议变为已作废(yzf)或属地废弃(sdfq)时:
- 协议下全部样本接收结论自动为废弃(bj);
- 协议下全部有效样本 `sample_status` 置为已作废(yzf),并写入 `void_date`。
3. 协议下全部有效样本均已废弃/拒收(bj)时,协议自动已作废,作废类型为「实验室废弃」。
"""
from __future__ import annotations
from datetime import datetime
from typing import List, Optional
from typing import List, Optional, Union
from sqlalchemy.orm import Session
from app.core.protocol_status_display import INACTIVE_PROTOCOL_STATUSES
from app.core.utils import generate_uuid
from app.models.models import (
BizCollectionProtocol,
@@ -23,11 +28,24 @@ from app.models.research_donation import BizResearchDonation, BizResearchDonatio
LAB_VOID_TYPE_CODE = "sysfq"
LAB_VOID_TYPE_LABEL = "实验室废弃"
VOID_TYPE_DICT = "xyzflx"
SAMPLE_VOID_STATUS = "yzf"
_AUTO_DISCARD_REMARK = "协议作废自动废弃"
_AUTO_VOID_DESC = "全部样本接收废弃/拒收,系统自动作废"
def _void_date_yyyymmdd(
*,
void_time: Optional[datetime] = None,
void_date: Optional[str] = None,
) -> str:
raw = (void_date or "").strip()
if len(raw) == 8 and raw.isdigit():
return raw
ts = void_time or datetime.now()
return ts.strftime("%Y%m%d")
def ensure_lab_void_type(db: Session, *, tenant_no: str) -> str:
"""确保租户字典 xyzflx 存在「实验室废弃」,返回 dict_value。"""
tenant = (tenant_no or "").strip()
@@ -204,7 +222,7 @@ def discard_receptions_for_voided_protocol(
tenant_no: str,
user_id: Optional[str] = None,
) -> int:
"""协议已作废后,将其下样本接收全部置为废弃。返回处理样本数。"""
"""协议无效后,将其下样本接收全部置为废弃。返回处理样本数。"""
src = (source or "").strip()
pid = (protocol_id or "").strip()
tenant = (tenant_no or "").strip()
@@ -242,6 +260,75 @@ def discard_receptions_for_voided_protocol(
return len(samples)
def void_samples_for_inactive_protocol(
db: Session,
*,
source: str,
protocol_id: str,
tenant_no: str,
user_id: Optional[str] = None,
void_time: Optional[datetime] = None,
void_date: Optional[str] = None,
) -> int:
"""协议无效后,将其下有效样本状态一律置为已作废并写入 void_date。返回处理样本数。"""
src = (source or "").strip()
pid = (protocol_id or "").strip()
tenant = (tenant_no or "").strip()
if src not in ("cell_storage", "research_donation") or not pid or not tenant:
return 0
date_s = _void_date_yyyymmdd(void_time=void_time, void_date=void_date)
now = datetime.now()
if src == "cell_storage":
samples: List[Union[BizProtocolSample, BizResearchDonationSample]] = list(
_list_cell_samples(db, protocol_id=pid, tenant_no=tenant)
)
else:
samples = list(_list_donation_samples(db, donation_id=pid, tenant_no=tenant))
for sample in samples:
sample.sample_status = SAMPLE_VOID_STATUS
sample.void_date = date_s
sample.update_by = user_id
sample.update_time = now
db.add(sample)
db.flush()
return len(samples)
def _resolve_protocol_void_time(
db: Session,
*,
source: str,
protocol_id: str,
tenant_no: str,
) -> Optional[datetime]:
src = (source or "").strip()
pid = (protocol_id or "").strip()
tenant = (tenant_no or "").strip()
if src == "cell_storage":
obj = (
db.query(BizCollectionProtocol)
.filter(
BizCollectionProtocol.id == pid,
BizCollectionProtocol.tenant_no == tenant,
BizCollectionProtocol.del_flag == 0,
)
.first()
)
return obj.void_time if obj else None
obj = (
db.query(BizResearchDonation)
.filter(
BizResearchDonation.id == pid,
BizResearchDonation.tenant_no == tenant,
BizResearchDonation.del_flag == 0,
)
.first()
)
return obj.void_time if obj else None
def _all_samples_discarded(
db: Session,
*,
@@ -299,7 +386,7 @@ def maybe_void_protocol_when_all_samples_discarded(
if not obj:
return False
status = (obj.protocol_status or "").strip()
if status in ("yzf", "sdfq"):
if status in INACTIVE_PROTOCOL_STATUSES:
return False
from app.core.protocol_status_display import assert_protocol_status_transition
from app.crud.collection_protocol import update_collection_protocol
@@ -319,7 +406,8 @@ def maybe_void_protocol_when_all_samples_discarded(
void_desc=_AUTO_VOID_DESC,
void_type=void_type,
commit=False,
sync_reception_discard=False,
# 接收已全部废弃;仍需同步样本状态为已作废
sync_reception_discard=True,
)
return True
@@ -335,7 +423,7 @@ def maybe_void_protocol_when_all_samples_discarded(
if not obj:
return False
status = (obj.donation_status or "").strip()
if status in ("yzf", "sdfq"):
if status in INACTIVE_PROTOCOL_STATUSES:
return False
# 捐赠 change_status 默认 commit;此处内联等价逻辑避免提前提交。
@@ -374,6 +462,168 @@ def maybe_void_protocol_when_all_samples_discarded(
)
db.add(obj)
db.flush()
# 接收已全部废弃;仍需同步样本状态为已作废
after_protocol_voided(
db,
source="research_donation",
protocol_id=pid,
tenant_no=tenant,
user_id=user_id,
)
return True
def _find_sample_for_reception(
db: Session,
*,
reception: BizSampleReception,
tenant_no: str,
) -> tuple[str, Optional[Union[BizProtocolSample, BizResearchDonationSample]]]:
source = (reception.reception_source or "cell_storage").strip() or "cell_storage"
tenant = (tenant_no or "").strip()
code = (reception.sample_code or "").strip()
if source == "cell_storage":
sid = (reception.protocol_sample_id or "").strip()
if sid:
obj = (
db.query(BizProtocolSample)
.filter(
BizProtocolSample.id == sid,
BizProtocolSample.tenant_no == tenant,
BizProtocolSample.del_flag == 0,
)
.first()
)
if obj:
return source, obj
pid = (reception.collection_protocol_id or "").strip()
if code and pid:
return source, (
db.query(BizProtocolSample)
.filter(
BizProtocolSample.collection_protocol_id == pid,
BizProtocolSample.sample_code == code,
BizProtocolSample.tenant_no == tenant,
BizProtocolSample.del_flag == 0,
)
.first()
)
return source, None
sid = (reception.donation_sample_id or "").strip()
if sid:
obj = (
db.query(BizResearchDonationSample)
.filter(
BizResearchDonationSample.id == sid,
BizResearchDonationSample.tenant_no == tenant,
BizResearchDonationSample.del_flag == 0,
)
.first()
)
if obj:
return source, obj
pid = (reception.research_donation_id or "").strip()
if code and pid:
return source, (
db.query(BizResearchDonationSample)
.filter(
BizResearchDonationSample.research_donation_id == pid,
BizResearchDonationSample.sample_code == code,
BizResearchDonationSample.tenant_no == tenant,
BizResearchDonationSample.del_flag == 0,
)
.first()
)
return source, None
def _protocol_is_inactive(
db: Session,
*,
source: str,
protocol_id: str,
tenant_no: str,
) -> bool:
src = (source or "").strip()
pid = (protocol_id or "").strip()
tenant = (tenant_no or "").strip()
if not pid or not tenant:
return False
if src == "cell_storage":
obj = (
db.query(BizCollectionProtocol)
.filter(
BizCollectionProtocol.id == pid,
BizCollectionProtocol.tenant_no == tenant,
BizCollectionProtocol.del_flag == 0,
)
.first()
)
status = (obj.protocol_status or "").strip() if obj else ""
else:
obj = (
db.query(BizResearchDonation)
.filter(
BizResearchDonation.id == pid,
BizResearchDonation.tenant_no == tenant,
BizResearchDonation.del_flag == 0,
)
.first()
)
status = (obj.donation_status or "").strip() if obj else ""
return status in INACTIVE_PROTOCOL_STATUSES
def void_sample_for_discarded_reception(
db: Session,
*,
reception: BizSampleReception,
tenant_no: str,
user_id: Optional[str] = None,
) -> bool:
"""单条接收废弃:仅将该样本置为已作废。"""
_source, sample = _find_sample_for_reception(db, reception=reception, tenant_no=tenant_no)
if not sample:
return False
now = datetime.now()
sample.sample_status = SAMPLE_VOID_STATUS
sample.void_date = _void_date_yyyymmdd(void_time=reception.reception_time or now)
sample.update_by = user_id
sample.update_time = now
db.add(sample)
db.flush()
return True
def clear_sample_void_for_accepted_reception(
db: Session,
*,
reception: BizSampleReception,
tenant_no: str,
user_id: Optional[str] = None,
) -> bool:
"""接收改回接收:协议仍有效且样本为 yzf 时,清空样本作废状态。"""
source, sample = _find_sample_for_reception(db, reception=reception, tenant_no=tenant_no)
if not sample:
return False
if (sample.sample_status or "").strip() != SAMPLE_VOID_STATUS:
return False
if source == "cell_storage":
protocol_id = (reception.collection_protocol_id or "").strip()
else:
protocol_id = (reception.research_donation_id or "").strip()
if _protocol_is_inactive(
db, source=source, protocol_id=protocol_id, tenant_no=tenant_no
):
return False
now = datetime.now()
sample.sample_status = None
sample.void_date = None
sample.update_by = user_id
sample.update_time = now
db.add(sample)
db.flush()
return True
@@ -384,23 +634,32 @@ def after_sample_reception_saved(
tenant_no: str,
user_id: Optional[str] = None,
) -> None:
"""样本接收保存后:若结论为废弃,尝试自动作废所属协议。"""
if (reception.accept_conclusion or "").strip() != "bj":
return
"""样本接收保存后:同步该样本状态;若全部废弃则尝试自动作废协议。"""
conclusion = (reception.accept_conclusion or "").strip()
source = (reception.reception_source or "cell_storage").strip() or "cell_storage"
if source == "cell_storage":
protocol_id = (reception.collection_protocol_id or "").strip()
else:
protocol_id = (reception.research_donation_id or "").strip()
if not protocol_id:
if conclusion == "bj":
void_sample_for_discarded_reception(
db, reception=reception, tenant_no=tenant_no, user_id=user_id
)
if protocol_id:
maybe_void_protocol_when_all_samples_discarded(
db,
source=source,
protocol_id=protocol_id,
tenant_no=tenant_no,
user_id=user_id,
)
return
maybe_void_protocol_when_all_samples_discarded(
db,
source=source,
protocol_id=protocol_id,
tenant_no=tenant_no,
user_id=user_id,
)
if conclusion == "js":
clear_sample_void_for_accepted_reception(
db, reception=reception, tenant_no=tenant_no, user_id=user_id
)
def after_protocol_voided(
@@ -411,7 +670,7 @@ def after_protocol_voided(
tenant_no: str,
user_id: Optional[str] = None,
) -> None:
"""协议作废后:同步废弃其下全部样本接收。"""
"""协议作废/属地废弃后:同步废弃样本接收,并将样本状态置为已作废。"""
discard_receptions_for_voided_protocol(
db,
source=source,
@@ -419,3 +678,13 @@ def after_protocol_voided(
tenant_no=tenant_no,
user_id=user_id,
)
void_samples_for_inactive_protocol(
db,
source=source,
protocol_id=protocol_id,
tenant_no=tenant_no,
user_id=user_id,
void_time=_resolve_protocol_void_time(
db, source=source, protocol_id=protocol_id, tenant_no=tenant_no
),
)
@@ -23,6 +23,7 @@ from app.models.research_donation import (
)
from app.services.protocol_reception_discard_sync import (
LAB_VOID_TYPE_CODE,
SAMPLE_VOID_STATUS,
after_protocol_voided,
after_sample_reception_saved,
ensure_lab_void_type,
@@ -77,6 +78,7 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
id="p1",
protocol_no="CP-1",
protocol_status="sxz",
void_time=now,
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
@@ -89,6 +91,7 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
id="s1",
collection_protocol_id="p1",
sample_code="ZK001",
sample_status="yrk",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
@@ -101,6 +104,7 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
id="s2",
collection_protocol_id="p1",
sample_code="ZK002",
sample_status="jcz",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
@@ -152,6 +156,16 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
self.assertEqual(by_code["ZK001"], "bj")
self.assertEqual(by_code["ZK002"], "bj")
samples = (
db.query(BizProtocolSample)
.filter(BizProtocolSample.collection_protocol_id == "p1")
.all()
)
self.assertEqual(len(samples), 2)
for s in samples:
self.assertEqual(s.sample_status, SAMPLE_VOID_STATUS)
self.assertEqual(s.void_date, "20261105")
def test_all_samples_discarded_auto_voids_protocol_with_lab_type(self):
db = _session()
now = _now()
@@ -172,6 +186,7 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
id="s3",
collection_protocol_id="p2",
sample_code="ZK003",
sample_status="ycj",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
@@ -212,6 +227,9 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
self.assertEqual(proto.protocol_status, "yzf")
self.assertEqual(proto.void_type, LAB_VOID_TYPE_CODE)
self.assertIn("全部样本接收", proto.void_desc or "")
sample = db.query(BizProtocolSample).filter(BizProtocolSample.id == "s3").one()
self.assertEqual(sample.sample_status, SAMPLE_VOID_STATUS)
self.assertTrue((sample.void_date or "").strip())
def test_partial_discard_does_not_void_protocol(self):
db = _session()
@@ -282,6 +300,7 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
id="d1",
donation_no="RD-1",
donation_status="sxz",
void_time=now,
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
@@ -294,6 +313,7 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
id="ds1",
research_donation_id="d1",
sample_code="RD001",
sample_status="yrk",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
@@ -322,6 +342,199 @@ class TestProtocolReceptionDiscardSync(unittest.TestCase):
)
self.assertEqual(rec.accept_conclusion, "bj")
self.assertEqual(rec.research_donation_id, "d1")
sample = (
db.query(BizResearchDonationSample)
.filter(BizResearchDonationSample.id == "ds1")
.one()
)
self.assertEqual(sample.sample_status, SAMPLE_VOID_STATUS)
self.assertEqual(sample.void_date, "20261105")
def test_sdfq_also_voids_sample_status(self):
db = _session()
now = _now()
db.add(
BizCollectionProtocol(
id="p-sdfq",
protocol_no="CP-SDFQ",
protocol_status="sdfq",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
)
)
db.add(
BizProtocolSample(
id="s-sdfq",
collection_protocol_id="p-sdfq",
sample_code="ZKSDFQ",
sample_status="ycj",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
)
)
db.commit()
after_protocol_voided(
db,
source="cell_storage",
protocol_id="p-sdfq",
tenant_no="tenant-a",
user_id="u1",
)
db.commit()
sample = db.query(BizProtocolSample).filter(BizProtocolSample.id == "s-sdfq").one()
self.assertEqual(sample.sample_status, SAMPLE_VOID_STATUS)
self.assertTrue((sample.void_date or "").strip())
rec = (
db.query(BizSampleReception)
.filter(BizSampleReception.sample_code == "ZKSDFQ")
.one()
)
self.assertEqual(rec.accept_conclusion, "bj")
def test_single_reception_discard_voids_only_that_sample(self):
db = _session()
now = _now()
db.add(
BizCollectionProtocol(
id="p-multi",
protocol_no="CP-MULTI",
protocol_status="sxz",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
)
)
for sid, code, status in (
("sa", "ZKA", "ycj"),
("sb", "ZKB", "ycj"),
("sc", "ZKC", "ycj"),
):
db.add(
BizProtocolSample(
id=sid,
collection_protocol_id="p-multi",
sample_code=code,
sample_status=status,
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
)
)
rec_a = BizSampleReception(
id="ra",
protocol_sample_id="sa",
collection_protocol_id="p-multi",
reception_time=now,
accept_conclusion="bj",
reception_source="cell_storage",
sample_code="ZKA",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
check_packaging=0,
check_sealing=0,
check_no_damage=0,
check_no_leakage=0,
check_color=0,
check_tying_rope=0,
check_no_stratification=0,
)
db.add(rec_a)
db.commit()
after_sample_reception_saved(
db, reception=rec_a, tenant_no="tenant-a", user_id="u1"
)
db.commit()
proto = db.query(BizCollectionProtocol).filter(BizCollectionProtocol.id == "p-multi").one()
self.assertEqual(proto.protocol_status, "sxz")
by_id = {
s.id: s
for s in db.query(BizProtocolSample)
.filter(BizProtocolSample.collection_protocol_id == "p-multi")
.all()
}
self.assertEqual(by_id["sa"].sample_status, SAMPLE_VOID_STATUS)
self.assertEqual(by_id["sa"].void_date, "20261105")
self.assertEqual(by_id["sb"].sample_status, "ycj")
self.assertEqual(by_id["sc"].sample_status, "ycj")
def test_reception_accept_clears_sample_void_when_protocol_active(self):
db = _session()
now = _now()
db.add(
BizCollectionProtocol(
id="p-clear",
protocol_no="CP-CLEAR",
protocol_status="sxz",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
)
)
db.add(
BizProtocolSample(
id="s-clear",
collection_protocol_id="p-clear",
sample_code="ZKCLR",
sample_status=SAMPLE_VOID_STATUS,
void_date="20261101",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
)
)
rec = BizSampleReception(
id="r-clear",
protocol_sample_id="s-clear",
collection_protocol_id="p-clear",
reception_time=now,
accept_conclusion="js",
reception_source="cell_storage",
sample_code="ZKCLR",
tenant_no="tenant-a",
create_dept_id="",
create_time=now,
update_time=now,
del_flag=0,
check_packaging=0,
check_sealing=0,
check_no_damage=0,
check_no_leakage=0,
check_color=0,
check_tying_rope=0,
check_no_stratification=0,
)
db.add(rec)
db.commit()
after_sample_reception_saved(
db, reception=rec, tenant_no="tenant-a", user_id="u1"
)
db.commit()
sample = db.query(BizProtocolSample).filter(BizProtocolSample.id == "s-clear").one()
self.assertFalse((sample.sample_status or "").strip())
self.assertFalse((sample.void_date or "").strip())
if __name__ == "__main__":
@@ -0,0 +1,54 @@
-- Issue #127:已作废 / 属地废弃协议下样本状态回刷为已作废
-- 细胞存储 + 科研捐赠;可重复执行。
-- 规则:协议 status 为 yzf/sdfq 且样本未删除时,sample_status 置 yzf,
-- void_date 为空时按协议 void_time / update_time 补齐 YYYYMMDD。
-- 细胞存储协议样本
UPDATE biz_protocol_sample s
INNER JOIN biz_collection_protocol p
ON p.id = s.collection_protocol_id
AND p.tenant_no = s.tenant_no
AND p.del_flag = 0
SET
s.sample_status = 'yzf',
s.void_date = CASE
WHEN s.void_date IS NOT NULL AND TRIM(s.void_date) <> '' THEN s.void_date
WHEN p.void_time IS NOT NULL THEN DATE_FORMAT(p.void_time, '%Y%m%d')
WHEN p.update_time IS NOT NULL THEN DATE_FORMAT(p.update_time, '%Y%m%d')
ELSE DATE_FORMAT(NOW(), '%Y%m%d')
END,
s.update_time = NOW()
WHERE s.del_flag = 0
AND p.protocol_status IN ('yzf', 'sdfq')
AND (
s.sample_status IS NULL
OR TRIM(s.sample_status) = ''
OR s.sample_status <> 'yzf'
OR s.void_date IS NULL
OR TRIM(s.void_date) = ''
);
-- 科研捐赠样本
UPDATE biz_research_donation_sample s
INNER JOIN biz_research_donation d
ON d.id = s.research_donation_id
AND d.tenant_no = s.tenant_no
AND d.del_flag = 0
SET
s.sample_status = 'yzf',
s.void_date = CASE
WHEN s.void_date IS NOT NULL AND TRIM(s.void_date) <> '' THEN s.void_date
WHEN d.void_time IS NOT NULL THEN DATE_FORMAT(d.void_time, '%Y%m%d')
WHEN d.update_time IS NOT NULL THEN DATE_FORMAT(d.update_time, '%Y%m%d')
ELSE DATE_FORMAT(NOW(), '%Y%m%d')
END,
s.update_time = NOW()
WHERE s.del_flag = 0
AND d.donation_status IN ('yzf', 'sdfq')
AND (
s.sample_status IS NULL
OR TRIM(s.sample_status) = ''
OR s.sample_status <> 'yzf'
OR s.void_date IS NULL
OR TRIM(s.void_date) = ''
);
@@ -0,0 +1,67 @@
-- Issue #129:样本接收结论已为废弃(bj)、但样本状态尚未作废的历史回刷
-- 细胞存储 / 科研捐赠;可重复执行。
-- 仅处理协议仍有效(非 yzf/sdfq)的样本行;已作废协议样本由 #127 回刷脚本覆盖。
-- 细胞存储
UPDATE biz_protocol_sample s
INNER JOIN biz_sample_reception r
ON r.tenant_no = s.tenant_no
AND r.del_flag = 0
AND r.reception_source = 'cell_storage'
AND r.accept_conclusion = 'bj'
AND r.sample_code = s.sample_code
AND r.collection_protocol_id = s.collection_protocol_id
INNER JOIN biz_collection_protocol p
ON p.id = s.collection_protocol_id
AND p.tenant_no = s.tenant_no
AND p.del_flag = 0
SET
s.sample_status = 'yzf',
s.void_date = CASE
WHEN s.void_date IS NOT NULL AND TRIM(s.void_date) <> '' THEN s.void_date
WHEN r.reception_time IS NOT NULL THEN DATE_FORMAT(r.reception_time, '%Y%m%d')
WHEN s.update_time IS NOT NULL THEN DATE_FORMAT(s.update_time, '%Y%m%d')
ELSE DATE_FORMAT(NOW(), '%Y%m%d')
END,
s.update_time = NOW()
WHERE s.del_flag = 0
AND p.protocol_status NOT IN ('yzf', 'sdfq')
AND (
s.sample_status IS NULL
OR TRIM(s.sample_status) = ''
OR s.sample_status <> 'yzf'
OR s.void_date IS NULL
OR TRIM(s.void_date) = ''
);
-- 科研捐赠
UPDATE biz_research_donation_sample s
INNER JOIN biz_sample_reception r
ON r.tenant_no = s.tenant_no
AND r.del_flag = 0
AND r.reception_source = 'research_donation'
AND r.accept_conclusion = 'bj'
AND r.sample_code = s.sample_code
AND r.research_donation_id = s.research_donation_id
INNER JOIN biz_research_donation d
ON d.id = s.research_donation_id
AND d.tenant_no = s.tenant_no
AND d.del_flag = 0
SET
s.sample_status = 'yzf',
s.void_date = CASE
WHEN s.void_date IS NOT NULL AND TRIM(s.void_date) <> '' THEN s.void_date
WHEN r.reception_time IS NOT NULL THEN DATE_FORMAT(r.reception_time, '%Y%m%d')
WHEN s.update_time IS NOT NULL THEN DATE_FORMAT(s.update_time, '%Y%m%d')
ELSE DATE_FORMAT(NOW(), '%Y%m%d')
END,
s.update_time = NOW()
WHERE s.del_flag = 0
AND d.donation_status NOT IN ('yzf', 'sdfq')
AND (
s.sample_status IS NULL
OR TRIM(s.sample_status) = ''
OR s.sample_status <> 'yzf'
OR s.void_date IS NULL
OR TRIM(s.void_date) = ''
);