fix(web-admin): 采集大表导出自存退款与科研/预签约对齐

自存表写入当月核量号与负值退款金额；科研废弃行第10列复用储存状态；预签约到账金额带定金后缀并填充退款。

backend/app/crud/collection_protocol.py

@@ -1291,10 +1291,23 @@ def list_collection_protocols(
     )
 
     from app.services.qc_work_order_fee_summary import build_protocol_payment_summaries
+    from app.crud import sample_freight as sample_freight_svc
 
     payment_summary_map = build_protocol_payment_summaries(
         db, protocol_ids, tenant_no, include_sample_payment_modes=True
     )
+    all_list_sample_ids = [
+        s.id
+        for samples in sample_map.values()
+        for s in samples
+        if getattr(s, "id", None)
+    ]
+    freight_by_sample = sample_freight_svc.freight_map_for_samples(
+        db,
+        tenant_no=tenant_no,
+        sample_source=sample_freight_svc.SAMPLE_SOURCE_CELL,
+        sample_ids=all_list_sample_ids,
+    )
 
     # 合并主表 + 子表数据
     result = []
@@ -1376,11 +1389,13 @@ def list_collection_protocols(
                 }
             sample_items.append(
                 {
+                    "id": s.id,
                     "sampleTypeName": type_name,
                     "sampleCode": code,
                     "sampleStatus": (s.sample_status or "").strip(),
                     "labStorageCode": lab_code_map.get(code, "") if code else "",
                     "transport": transport_dict,
+                    "freight": freight_by_sample.get(s.id),
                     **sample_special_to_dict(s),
                     **build_sample_item_reception_qc_fields(
                         reception_summary_map.get(code) if code else None,

backend/app/services/qc_work_order_fee_summary.py

@@ -127,6 +127,7 @@ def _payment_detail_row(d: BizProtocolPaymentDetail, pay_mode: str, idx: int) ->
         "installmentDueDate": _fmt_ymd(d.installment_due_date),
         "dueAmount": _dec(d.due_amount),
         "transferAmount": _dec(d.transfer_amount),
+        "refundAmount": _dec(getattr(d, "refund_amount", None)),
         "payStatus": st,
         "payStatusLabel": _PAY_STATUS_LABELS.get(st, st or "—"),
         "transferTime": _fmt_dt(getattr(d, "transfer_time", None)),
@@ -156,6 +157,14 @@ def build_protocol_payment_summary(
     detail_rows = [_payment_detail_row(d, pay_mode, i) for i, d in enumerate(sorted_details)]
     received = _received_amount_from_details(sorted_details)
     pending = max(contract - received, 0) if contract is not None else None
+    refund_total = 0.0
+    has_refund = False
+    for d in sorted_details:
+        amt = _dec(getattr(d, "refund_amount", None))
+        if amt is None:
+            continue
+        has_refund = True
+        refund_total += float(amt)
     statuses = [(d.pay_status or "").strip() for d in sorted_details]
     overall = _protocol_overall_status(statuses, contract, pending)
     hint = _protocol_status_hint(pay_mode, statuses) or overall["overallPayStatusLabel"]
@@ -165,6 +174,7 @@ def build_protocol_payment_summary(
         "contractAmount": contract,
         "receivedAmount": received,
         "pendingAmount": pending,
+        "refundAmount": refund_total if has_refund else None,
         "statusHint": hint,
         "installmentCount": len(detail_rows) if pay_mode in ("fq", "djfq") else None,
         "paymentDetails": detail_rows,

web-admin/src/utils/collection-ledger-export.test.ts

@@ -45,10 +45,25 @@ describe('buildPresignLedgerRows', () => {
     assert.equal(row.length, PRESIGN_HEADERS.length)
     assert.deepEqual(row.slice(0, 6), ['1', '2026', '4', '2', 'ZKZX1', '杨育蕾'])
     assert.equal(row[9], '趸交')
-    assert.equal(row[12], '1000元')
+    assert.equal(row[12], '1000元（定金）')
     assert.equal(row[13], '2026年4月2日16时49分42秒')
     assert.equal(row[18], '')
   })
+
+  it('fills refund amount for voided deposit orders', () => {
+    const [row] = buildPresignLedgerRows([
+      {
+        orderDate: '20250919',
+        motherName: '陈影',
+        confirmedDepositAmount: 1000,
+        transferTimes: ['2025-09-19 17:26:13'],
+        orderStatus: 'void',
+        remark: '悔存-申请退定金'
+      }
+    ])
+    assert.equal(row[12], '1000元（定金）')
+    assert.equal(row[18], '1000元')
+  })
 })
 
 describe('buildCellStorageLedgerRows', () => {
@@ -65,7 +80,8 @@ describe('buildCellStorageLedgerRows', () => {
       isCoStored: false,
       sampleCollectTime: '2025-09-15 08:51:00',
       motherName: '郭梦朵',
-      protocolNo: 'ZKZX34000849',
+      protocolNo: 'PROTO-SHOULD-NOT-EXPORT',
+      sampleCode: 'ZKZX34000849',
       sampleTypeName: '围产期造血干细胞',
       regionName: '亳州区域',
       hospitalName: '涡阳县人民医院',
@@ -73,6 +89,10 @@ describe('buildCellStorageLedgerRows', () => {
       salesUserName: '马丽',
       regionManagerUserName: '张颖',
       transportType: 'kdys',
+      freightId: 'fr-cell-1',
+      freightTransportLabel: '专线车',
+      freightAmountLabel: '120',
+      monthlyVerifyNo: '202509012',
       paymentSummary: {
         payMode: 'fq',
         contractAmount: 22880,
@@ -97,22 +117,82 @@ describe('buildCellStorageLedgerRows', () => {
         ]
       }
     } as unknown as ProtocolAuditFlatRow
-    const [row] = buildCellStorageLedgerRows([flat])
+    const { rows } = buildCellStorageLedgerRows([flat])
+    const row = rows[0]
     assert.equal(row.length, CELL_STORAGE_HEADERS.length)
     assert.equal(CELL_STORAGE_HEADERS[13], '涡阳县人民医院')
+    assert.equal(row[4], '202509012')
     assert.equal(row[5], 'B0000004')
     assert.equal(row[6], '生效中')
+    assert.equal(row[9], 'ZKZX34000849')
     assert.equal(row[11], '否')
     assert.equal(row[13], '涡阳县人民医院')
     assert.equal(row[14], '分期')
     assert.equal(row[16], '22880')
     assert.equal(row[17], '6480')
+    assert.equal(row[20], '')
     assert.equal(row[22], '2025年9月15日11时13分01秒')
     assert.equal(row[24], '16400')
     assert.equal(row[25], '16400')
     assert.equal(row[26], '2025年12月15日')
     assert.equal(row[50], '马丽')
-    assert.equal(row[52], '快递运输')
+    assert.equal(row[52], '专线车')
+    assert.equal(row[53], '120')
+  })
+
+  it('writes negative refund amount for voided protocols', () => {
+    const { rows } = buildCellStorageLedgerRows([
+      {
+        protocol: { protocolStatus: 'yzf', remark: '', voidType: 'khhc', voidDesc: '悔存' },
+        sampleCollectTime: '2025-09-07 13:45:00',
+        motherName: '王晴晴',
+        sampleCode: 'S-VOID',
+        sampleTypeName: '围产期造血干细胞',
+        paymentSummary: {
+          payMode: 'dj',
+          contractAmount: 5000,
+          receivedAmount: 5000,
+          refundAmount: 5000,
+          paymentDetails: []
+        }
+      } as unknown as ProtocolAuditFlatRow
+    ])
+    assert.equal(rows[0][19], '5000')
+    assert.equal(rows[0][20], '-5000')
+  })
+
+  it('merges transport and freight columns for the same freight batch', () => {
+    const base = {
+      protocol: { protocolStatus: 'sxz', remark: '', voidType: '', voidDesc: '' },
+      sampleCollectTime: '2025-09-11 18:13:00',
+      motherName: '甲',
+      sampleCode: 'S1',
+      sampleTypeName: 'NK',
+      regionName: '常州',
+      hospitalName: '溧阳市人民医院',
+      labStorageCode: 'UBMC1',
+      freightId: 'batch-cell',
+      freightTransportLabel: '专线车',
+      freightAmountLabel: '100'
+    }
+    const { rows, merges } = buildCellStorageLedgerRows([
+      { ...base, sampleIndex: 0 } as unknown as ProtocolAuditFlatRow,
+      {
+        ...base,
+        sampleIndex: 1,
+        motherName: '乙',
+        sampleCode: 'S2',
+        labStorageCode: 'UBMC2'
+      } as unknown as ProtocolAuditFlatRow
+    ])
+    assert.equal(rows[0][52], '专线车')
+    assert.equal(rows[0][53], '100')
+    assert.equal(rows[1][52], '')
+    assert.equal(rows[1][53], '')
+    assert.deepEqual(merges, [
+      { startRow: 2, endRow: 3, startCol: 53, endCol: 53 },
+      { startRow: 2, endRow: 3, startCol: 54, endCol: 54 }
+    ])
   })
 })
 
@@ -154,6 +234,7 @@ describe('buildResearchLedgerRows', () => {
     assert.equal(row[4], '202508001')
     assert.equal(row[6], '实验室废弃')
     assert.equal(row[7], '母血乙肝阳性')
+    assert.equal(row[9], '实验室废弃')
     assert.equal(row[16], '专线车')
     assert.equal(row[17], '100')
     assert.equal(row[18], '女')
@@ -162,6 +243,24 @@ describe('buildResearchLedgerRows', () => {
     assert.equal(row[23], '1')
   })
 
+  it('keeps sample type in column 10 for active research rows', () => {
+    const { rows } = buildResearchLedgerRows([
+      {
+        protocol: { donationStatus: 'sxz', voidType: '', voidDesc: '', monthlyVerifyNo: '202508010' },
+        sampleCollectTime: '2025-08-30 10:00:00',
+        motherName: '正常',
+        sampleTypeName: '围产期造血干细胞',
+        regionName: '亳州',
+        hospitalName: '涡阳县人民医院',
+        freightId: '',
+        freightTransportLabel: '',
+        freightAmountLabel: ''
+      } as unknown as ProtocolAuditFlatRow
+    ])
+    assert.equal(rows[0][6], '')
+    assert.equal(rows[0][9], '围产期造血干细胞')
+  })
+
   it('maps sdfq void type code to 属地废弃 labels', () => {
     const flat = {
       protocol: {
@@ -196,6 +295,7 @@ describe('buildResearchLedgerRows', () => {
     const row = rows[0]
     assert.equal(row[6], '属地废弃')
     assert.equal(row[7], '属地废弃')
+    assert.equal(row[9], '属地废弃')
     assert.equal(row[20], '剖腹产')
     assert.equal(row[22], '无母血')
   })

web-admin/src/utils/collection-ledger-export.ts

@@ -338,8 +338,16 @@ function researchMaternalBloodText(value: unknown): string {
   return text(value) ? text(value) : '无母血'
 }
 
-/** 相邻且 freightId 相同的导出行，合并运输方式/运费列（Excel 行号含表头）。 */
-export function buildFreightMergeRanges(freightIds: string[]): LedgerMergeRange[] {
+/**
+ * 相邻且 freightId 相同的导出行，合并运输方式/运费列（Excel 行号含表头）。
+ * @param transportCol 运输方式列（1-based）
+ * @param freightCol 运费列（1-based）
+ */
+export function buildFreightMergeRanges(
+  freightIds: string[],
+  transportCol = 17,
+  freightCol = 18
+): LedgerMergeRange[] {
   const merges: LedgerMergeRange[] = []
   let index = 0
   while (index < freightIds.length) {
@@ -353,14 +361,31 @@ export function buildFreightMergeRanges(freightIds: string[]): LedgerMergeRange[
     if (end - index >= 2) {
       const startRow = index + 2
       const endRow = end + 1
-      merges.push({ startRow, endRow, startCol: 17, endCol: 17 })
-      merges.push({ startRow, endRow, startCol: 18, endCol: 18 })
+      merges.push({ startRow, endRow, startCol: transportCol, endCol: transportCol })
+      merges.push({ startRow, endRow, startCol: freightCol, endCol: freightCol })
     }
     index = end
   }
   return merges
 }
 
+function applyFreightMerges(
+  built: string[][],
+  freightIds: string[],
+  transportCol: number,
+  freightCol: number
+): LedgerMergeRange[] {
+  const merges = buildFreightMergeRanges(freightIds, transportCol, freightCol)
+  for (const merge of merges) {
+    const colIndex = merge.startCol - 1
+    for (let excelRow = merge.startRow + 1; excelRow <= merge.endRow; excelRow += 1) {
+      const dataIndex = excelRow - 2
+      if (dataIndex >= 0 && dataIndex < built.length) built[dataIndex][colIndex] = ''
+    }
+  }
+  return merges
+}
+
 /** 导出前加载作废类型字典，避免列表外的编码原样写入 Excel。 */
 export async function prepareCollectionLedgerExport(): Promise<void> {
   const { loadVoidTypeDict, getVoidTypeOptions } = await import('./void-type-dict')
@@ -445,11 +470,26 @@ function transportText(row: ProtocolAuditFlatRow): string {
   return label(TRANSPORT, row.transportType || transport.transportType)
 }
 
+function presignDepositText(amount: unknown): string {
+  const money = formatLedgerMoney(amount)
+  return money ? `${money}元（定金）` : ''
+}
+
+function presignRefundText(row: Row): string {
+  const explicit = formatLedgerMoney(row.refundAmount)
+  if (explicit) return `${explicit}元`
+  const status = text(row.orderStatus)
+  const remark = text(row.remark)
+  const voided = status === 'void' || remark.includes('悔存') || remark.includes('退定金')
+  if (!voided) return ''
+  const deposit = formatLedgerMoney(row.confirmedDepositAmount)
+  return deposit ? `${deposit}元` : ''
+}
+
 export function buildPresignLedgerRows(rows: Row[]): string[][] {
   return rows.map((row, index) => {
     const orderDate = row.orderDate
     const times = Array.isArray(row.transferTimes) ? row.transferTimes : []
-    const received = formatLedgerMoney(row.confirmedDepositAmount)
     return [
       String(index + 1),
       yearText(orderDate, ''),
@@ -463,17 +503,31 @@ export function buildPresignLedgerRows(rows: Row[]): string[][] {
       payModeText(row.payMode),
       formatLedgerDateTime(row.sampleCollectTime),
       formatLedgerMoney(row.contractAmount),
-      received ? `${received}元` : '',
+      presignDepositText(row.confirmedDepositAmount),
       times.map((item) => formatLedgerDateTime(item)).filter(Boolean).join('，'),
       formatLedgerDate(row.expectedDeliveryDate),
       text(row.salesUserName),
       text(row.regionManagerUserName),
       text(row.remark),
-      ''
+      presignRefundText(row)
     ]
   })
 }
 
+/** 自存表退款金额：有退费记录写负值；无退费但已作废时写 -已缴金额。 */
+function cellStorageRefundText(summary: Row | null, voided: boolean, received: string): string {
+  const explicit = summary?.refundAmount
+  if (explicit != null && explicit !== '') {
+    const amount = Number(explicit)
+    if (Number.isFinite(amount) && amount !== 0) {
+      const abs = formatLedgerMoney(Math.abs(amount))
+      return abs ? `-${abs}` : ''
+    }
+  }
+  if (!voided || !received) return ''
+  return `-${received}`
+}
+
 function storageLedgerRow(row: ProtocolAuditFlatRow, index: number, research: boolean): string[] {
   const protocol = row.protocol
   const summary = (row.paymentSummary || protocol.paymentSummary) as Row | null
@@ -500,7 +554,8 @@ function storageLedgerRow(row: ProtocolAuditFlatRow, index: number, research: bo
     cells[6] = researchStorageStatus(status, protocol.voidType)
     cells[7] = voidReason(protocol)
     cells[8] = text(row.motherName)
-    cells[9] = text(row.sampleTypeName)
+    // 标准大表废弃行第 10 列复用储存状态文案；正常行写样本类型
+    cells[9] = cells[6] || text(row.sampleTypeName)
     cells[10] = text(row.regionName)
     cells[11] = text(row.hospitalName)
     cells[12] = formatLedgerDateTime(row.newbornDeliveryTime)
@@ -522,11 +577,12 @@ function storageLedgerRow(row: ProtocolAuditFlatRow, index: number, research: bo
   cells[1] = yearText(collectTime, '')
   cells[2] = monthText(collectTime)
   cells[3] = dayText(collectTime)
+  cells[4] = text(row.monthlyVerifyNo) || text(protocol.monthlyVerifyNo)
   cells[5] = text(row.labStorageCode)
   cells[6] = statusText(status, protocol.voidType)
   cells[7] = voidReason(protocol)
   cells[8] = text(row.motherName)
-  cells[9] = text(row.protocolNo)
+  cells[9] = text(row.sampleCode)
   cells[10] = text(row.sampleTypeName)
   cells[11] = row.isCoStored ? '是' : '否'
   cells[12] = text(row.regionName)
@@ -537,6 +593,7 @@ function storageLedgerRow(row: ProtocolAuditFlatRow, index: number, research: bo
   cells[17] = formatLedgerMoney(first.due)
   cells[18] = received
   cells[19] = voided ? received : ''
+  cells[20] = cellStorageRefundText(summary, voided, received)
   cells[21] = text(protocol.remark)
   cells[22] = formatLedgerDateTime(firstPaidTime)
   cells[23] = gapText(first.due, first.paid)
@@ -557,12 +614,19 @@ function storageLedgerRow(row: ProtocolAuditFlatRow, index: number, research: bo
   }
   cells[50] = text(row.salesUserName)
   cells[51] = text(row.regionManagerUserName)
-  cells[52] = transportText(row)
+  cells[52] = researchTransportText(row)
+  cells[53] = researchFreightText(row)
   return cells
 }
 
-export function buildCellStorageLedgerRows(rows: ProtocolAuditFlatRow[]): string[][] {
-  return rows.map((row, index) => storageLedgerRow(row, index, false))
+export function buildCellStorageLedgerRows(rows: ProtocolAuditFlatRow[]): {
+  rows: string[][]
+  merges: LedgerMergeRange[]
+} {
+  const freightIds = rows.map((row) => text(row.freightId))
+  const built = rows.map((row, index) => storageLedgerRow(row, index, false))
+  // 自存表：运输方式=53，运费=54
+  return { rows: built, merges: applyFreightMerges(built, freightIds, 53, 54) }
 }
 
 export function buildResearchLedgerRows(rows: ProtocolAuditFlatRow[]): {
@@ -571,17 +635,8 @@ export function buildResearchLedgerRows(rows: ProtocolAuditFlatRow[]): {
 } {
   const freightIds = rows.map((row) => text(row.freightId))
   const built = rows.map((row, index) => storageLedgerRow(row, index, true))
-  const merges = buildFreightMergeRanges(freightIds)
-  // 合并区域内仅保留首行运输/运费，其余清空，避免合并后重复显示
-  for (const merge of merges) {
-    if (merge.startCol !== 17 && merge.startCol !== 18) continue
-    const colIndex = merge.startCol - 1
-    for (let excelRow = merge.startRow + 1; excelRow <= merge.endRow; excelRow += 1) {
-      const dataIndex = excelRow - 2
-      if (dataIndex >= 0 && dataIndex < built.length) built[dataIndex][colIndex] = ''
-    }
-  }
-  return { rows: built, merges }
+  // 科研表：运输方式=17，运费=18
+  return { rows: built, merges: applyFreightMerges(built, freightIds, 17, 18) }
 }
 
 export function buildCustomerServiceLedgerRows(rows: Row[]): string[][] {

web-admin/src/views/cell-storage/protocol-audit/ProtocolAuditTab.vue

@@ -534,11 +534,13 @@
         ElMessage.warning('没有可导出的数据')
         return
       }
+      const ledger = buildCellStorageLedgerRows(rows)
       await saveCollectionLedgerWorkbook(
         '自存表',
         CELL_STORAGE_HEADERS,
-        buildCellStorageLedgerRows(rows),
-        '自存表'
+        ledger.rows,
+        '自存表',
+        ledger.merges
       )
       ElMessage.success('导出成功')
     } catch {