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/crud/research_donation.py

@@ -879,6 +879,20 @@ def list_donations(
             if t.express_company_id
         ],
     )
+    from app.crud import sample_freight as sample_freight_svc
+
+    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_DONATION,
+        sample_ids=all_list_sample_ids,
+    )
 
     result = []
     for r in rows:
@@ -957,11 +971,13 @@ def list_donations(
                 }
             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

@@ -7,6 +7,7 @@ import {
   RESEARCH_HEADERS,
   buildCellStorageLedgerRows,
   buildCustomerServiceLedgerRows,
+  buildFreightMergeRanges,
   buildPresignLedgerRows,
   buildResearchLedgerRows,
   formatLedgerDateTime
@@ -44,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', () => {
@@ -64,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: '涡阳县人民医院',
@@ -72,6 +89,10 @@ describe('buildCellStorageLedgerRows', () => {
       salesUserName: '马丽',
       regionManagerUserName: '张颖',
       transportType: 'kdys',
+      freightId: 'fr-cell-1',
+      freightTransportLabel: '专线车',
+      freightAmountLabel: '120',
+      monthlyVerifyNo: '202509012',
       paymentSummary: {
         payMode: 'fq',
         contractAmount: 22880,
@@ -96,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 }
+    ])
   })
 })
 
@@ -141,19 +222,119 @@ describe('buildResearchLedgerRows', () => {
       birthOrder: 1,
       maternalBloodCollectTime: '2025-08-29 13:30:00',
       consentFormUrls: 'http://example/consent',
-      remark: ''
+      remark: '',
+      freightId: 'fr1',
+      freightTransportLabel: '专线车',
+      freightAmountLabel: '100'
     } as unknown as ProtocolAuditFlatRow
-    const [row] = buildResearchLedgerRows([flat])
+    const { rows } = buildResearchLedgerRows([flat])
+    const row = rows[0]
     assert.equal(row.length, RESEARCH_HEADERS.length)
     assert.equal(row[1], '2025年')
     assert.equal(row[4], '202508001')
-    assert.equal(row[6], '已作废(实验室废弃)')
+    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], '女')
     assert.equal(row[19], '3000g')
     assert.equal(row[20], '顺产')
     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: {
+        donationStatus: 'yzf',
+        voidType: 'sdfq',
+        voidDesc: '',
+        monthlyVerifyNo: ''
+      },
+      sampleIndex: 0,
+      sampleCollectTime: '2025-08-29 12:27:00',
+      motherName: '测试',
+      sampleTypeName: '围产期造血干细胞',
+      regionName: '亳州',
+      hospitalName: '涡阳县人民医院',
+      labStorageCode: 'A0000002',
+      salesUserName: '',
+      regionManagerUserName: '',
+      transportType: '',
+      newbornDeliveryTime: '',
+      newbornSex: '',
+      newbornWeightGram: '',
+      deliveryMode: 'pgc',
+      birthOrder: '',
+      maternalBloodCollectTime: '',
+      consentFormUrls: '',
+      remark: '',
+      freightId: '',
+      freightTransportLabel: '',
+      freightAmountLabel: ''
+    } as unknown as ProtocolAuditFlatRow
+    const { rows } = buildResearchLedgerRows([flat])
+    const row = rows[0]
+    assert.equal(row[6], '属地废弃')
+    assert.equal(row[7], '属地废弃')
+    assert.equal(row[9], '属地废弃')
+    assert.equal(row[20], '剖腹产')
+    assert.equal(row[22], '无母血')
+  })
+
+  it('merges transport and freight columns for the same freight batch', () => {
+    const base = {
+      protocol: { donationStatus: 'sxz', voidType: '', voidDesc: '', monthlyVerifyNo: '' },
+      sampleCollectTime: '2025-09-11 18:13:00',
+      motherName: '甲',
+      sampleTypeName: 'NK',
+      regionName: '常州',
+      hospitalName: '溧阳市人民医院',
+      labStorageCode: 'UBMC1',
+      freightId: 'batch-a',
+      freightTransportLabel: '专线车',
+      freightAmountLabel: '100'
+    }
+    const { rows, merges } = buildResearchLedgerRows([
+      { ...base, sampleIndex: 0 } as unknown as ProtocolAuditFlatRow,
+      {
+        ...base,
+        sampleIndex: 1,
+        motherName: '乙',
+        labStorageCode: 'UBMC2'
+      } as unknown as ProtocolAuditFlatRow
+    ])
+    assert.equal(rows[0][16], '专线车')
+    assert.equal(rows[0][17], '100')
+    assert.equal(rows[1][16], '')
+    assert.equal(rows[1][17], '')
+    assert.deepEqual(merges, [
+      { startRow: 2, endRow: 3, startCol: 17, endCol: 17 },
+      { startRow: 2, endRow: 3, startCol: 18, endCol: 18 }
+    ])
+    assert.deepEqual(buildFreightMergeRanges(['', 'x', 'x', 'y']), [
+      { startRow: 3, endRow: 4, startCol: 17, endCol: 17 },
+      { startRow: 3, endRow: 4, startCol: 18, endCol: 18 }
+    ])
+  })
 })
 
 describe('buildCustomerServiceLedgerRows', () => {

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

@@ -166,12 +166,16 @@ const PROTOCOL_STATUS: Record<string, string> = {
   zckc: '转细胞存储'
 }
 
+/** 作废类型兜底；与 void-type-dict 默认项一致，并覆盖误写入作废类型的状态码。 */
 const VOID_TYPE: Record<string, string> = {
   khhc: '客户毁存',
   jcbhg: '检测不合格',
   sysfq: '实验室废弃'
 }
 
+/** 导出前由字典填充；单元测试环境可为空。 */
+let runtimeVoidLabels: Record<string, string> = {}
+
 const PAY_MODE: Record<string, string> = {
   dj: '趸交',
   fq: '分期',
@@ -194,6 +198,19 @@ const DELIVERY: Record<string, string> = {
   pgc: '剖宫产'
 }
 
+/** 科研表模板多用「剖腹产」。 */
+const RESEARCH_DELIVERY: Record<string, string> = {
+  sc: '顺产',
+  pgc: '剖腹产'
+}
+
+export type LedgerMergeRange = {
+  startRow: number
+  endRow: number
+  startCol: number
+  endCol: number
+}
+
 function text(value: unknown): string {
   return value == null ? '' : String(value).trim()
 }
@@ -275,16 +292,107 @@ function dayText(value: unknown): string {
   return parts ? String(parts.day) : ''
 }
 
+/** 作废类型：字典 → 本地兜底 → 协议状态码文案（如 sdfq→属地废弃）。 */
+function voidTypeText(value: unknown): string {
+  const code = text(value)
+  if (!code) return ''
+  return (
+    runtimeVoidLabels[code] ||
+    VOID_TYPE[code] ||
+    PROTOCOL_STATUS[code] ||
+    code
+  )
+}
+
 function statusText(status: unknown, voidType: unknown): string {
   const code = text(status)
   const base = PROTOCOL_STATUS[code] || code
   if (code !== 'yzf') return base
-  const reason = label(VOID_TYPE, voidType)
+  const reason = voidTypeText(voidType)
   return reason ? `${base}(${reason})` : base
 }
 
+/** 科研表储存状态：废弃写类别文案，正常样本留空，对齐线下大表。 */
+function researchStorageStatus(status: unknown, voidType: unknown): string {
+  const code = text(status)
+  if (code === 'yzf') return voidTypeText(voidType)
+  if (code === 'sdfq') return PROTOCOL_STATUS.sdfq
+  return ''
+}
+
 function voidReason(row: Row): string {
-  return text(row.voidDesc) || label(VOID_TYPE, row.voidType)
+  return text(row.voidDesc) || voidTypeText(row.voidType)
+}
+
+function researchTransportText(row: ProtocolAuditFlatRow): string {
+  return text(row.freightTransportLabel) || transportText(row)
+}
+
+function researchFreightText(row: ProtocolAuditFlatRow): string {
+  return text(row.freightAmountLabel)
+}
+
+function researchMaternalBloodText(value: unknown): string {
+  const formatted = formatLedgerDateTime(value)
+  if (formatted) return formatted
+  return text(value) ? text(value) : '无母血'
+}
+
+/**
+ * 相邻且 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) {
+    const freightId = text(freightIds[index])
+    if (!freightId) {
+      index += 1
+      continue
+    }
+    let end = index + 1
+    while (end < freightIds.length && text(freightIds[end]) === freightId) end += 1
+    if (end - index >= 2) {
+      const startRow = index + 2
+      const endRow = end + 1
+      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')
+  await loadVoidTypeDict()
+  runtimeVoidLabels = Object.fromEntries(
+    getVoidTypeOptions().map((item) => [String(item.value), String(item.label)])
+  )
 }
 
 function payModeText(value: unknown, fallback = ''): string {
@@ -362,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, ''),
@@ -380,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
@@ -414,22 +551,24 @@ function storageLedgerRow(row: ProtocolAuditFlatRow, index: number, research: bo
     cells[3] = dayText(collectTime)
     cells[4] = text(protocol.monthlyVerifyNo)
     cells[5] = text(row.labStorageCode)
-    cells[6] = statusText(status, protocol.voidType)
+    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)
     cells[13] = formatLedgerDateTime(collectTime)
     cells[14] = text(row.salesUserName)
     cells[15] = text(row.regionManagerUserName)
-    cells[16] = transportText(row)
+    cells[16] = researchTransportText(row)
+    cells[17] = researchFreightText(row)
     cells[18] = label(SEX, row.newbornSex)
     cells[19] = weightText(row.newbornWeightGram)
-    cells[20] = label(DELIVERY, row.deliveryMode)
+    cells[20] = label(RESEARCH_DELIVERY, row.deliveryMode) || label(DELIVERY, row.deliveryMode)
     cells[21] = text(row.birthOrder)
-    cells[22] = formatLedgerDateTime(row.maternalBloodCollectTime)
+    cells[22] = researchMaternalBloodText(row.maternalBloodCollectTime)
     cells[23] = text(row.consentFormUrls) ? '1' : ''
     cells[24] = text(row.remark)
     return cells
@@ -438,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)
@@ -453,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)
@@ -473,16 +614,29 @@ 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[][] {
+export function buildCellStorageLedgerRows(rows: ProtocolAuditFlatRow[]): {
-  return rows.map((row, index) => storageLedgerRow(row, index, false))
+  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[]): string[][] {
+export function buildResearchLedgerRows(rows: ProtocolAuditFlatRow[]): {
-  return rows.map((row, index) => storageLedgerRow(row, index, true))
+  rows: string[][]
+  merges: LedgerMergeRange[]
+} {
+  const freightIds = rows.map((row) => text(row.freightId))
+  const built = rows.map((row, index) => storageLedgerRow(row, index, true))
+  // 科研表：运输方式=17，运费=18
+  return { rows: built, merges: applyFreightMerges(built, freightIds, 17, 18) }
 }
 
 export function buildCustomerServiceLedgerRows(rows: Row[]): string[][] {
@@ -551,7 +705,8 @@ export async function saveCollectionLedgerWorkbook(
   sheetName: string,
   headers: string[],
   rows: string[][],
-  filenamePrefix: string
+  filenamePrefix: string,
+  merges: LedgerMergeRange[] = []
 ) {
   const ExcelJS = (await import('exceljs')).default
   const FileSaver = (await import('file-saver')).default
@@ -568,8 +723,15 @@ export async function saveCollectionLedgerWorkbook(
       const cell = added.getCell(index + 1)
       cell.value = value
       cell.numFmt = '@'
+      cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }
     })
   })
+  merges.forEach((merge) => {
+    if (merge.endRow <= merge.startRow) return
+    sheet.mergeCells(merge.startRow, merge.startCol, merge.endRow, merge.endCol)
+    const cell = sheet.getCell(merge.startRow, merge.startCol)
+    cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }
+  })
   headers.forEach((header, index) => {
     sheet.getColumn(index + 1).width = Math.min(28, Math.max(12, header.replace(/\n/g, '').length + 2))
   })

web-admin/src/utils/protocol-audit-flatten.test.ts

@@ -110,7 +110,18 @@ describe('flattenProtocolAuditRows', () => {
         infectiousDiseaseReportUrls: '',
         attachmentUrls: 'https://x/b.pdf',
         remark: '备注一',
-        sampleItems: [{ sampleTypeName: 'NK', sampleCode: 'D2', labStorageCode: 'A1' }]
+        sampleItems: [
+          {
+            sampleTypeName: 'NK',
+            sampleCode: 'D2',
+            labStorageCode: 'A1',
+            freight: {
+              id: 'fr-1',
+              transportModeLabel: '专线车',
+              freightAmountLabel: '100'
+            }
+          }
+        ]
       }
     ])
     assert.equal(rows[0].monthlyVerifyNo, '202508001')
@@ -122,6 +133,9 @@ describe('flattenProtocolAuditRows', () => {
     assert.equal(rows[0].infectiousDiseaseReportUrls, '')
     assert.equal(rows[0].attachmentUrls, 'https://x/b.pdf')
     assert.equal(rows[0].remark, '备注一')
+    assert.equal(rows[0].freightId, 'fr-1')
+    assert.equal(rows[0].freightTransportLabel, '专线车')
+    assert.equal(rows[0].freightAmountLabel, '100')
   })
 })
 

web-admin/src/utils/protocol-audit-flatten.ts

@@ -37,6 +37,10 @@ export type ProtocolAuditFlatRow = {
   labStorageCode: string
   transportType: string
   transportLabel: string
+  /** 样本运费批次；多样本共用时 id 相同 */
+  freightId: string
+  freightTransportLabel: string
+  freightAmountLabel: string
   paymentSummary: QcProtocolPaymentSummary | null
   monthlyVerifyNo: string
   donationStatus: string
@@ -90,7 +94,13 @@ export function flattenProtocolAuditRows(
     slots.forEach((item, sampleIndex) => {
       const rec = item || {}
       const transport = asRecord(rec.transport)
+      const freight = asRecord(rec.freight)
       const transportType = str(transport?.transportType)
+      const freightAmountLabel = str(freight?.freightAmountLabel)
+      const freightAmount =
+        freight?.freightAmount == null || freight?.freightAmount === ''
+          ? ''
+          : String(freight.freightAmount)
       out.push({
         rowKey: `${protocolId}|${str(rec.sampleCode) || sampleIndex}`,
         protocolId,
@@ -113,6 +123,9 @@ export function flattenProtocolAuditRows(
         labStorageCode: str(rec.labStorageCode),
         transportType,
         transportLabel: '',
+        freightId: str(freight?.id),
+        freightTransportLabel: str(freight?.transportModeLabel) || str(freight?.transportMode),
+        freightAmountLabel: freightAmountLabel || freightAmount,
         paymentSummary:
           protocol.paymentSummary && typeof protocol.paymentSummary === 'object'
             ? (protocol.paymentSummary as QcProtocolPaymentSummary)

web-admin/src/views/cell-storage/presign-order/index.vue

@@ -206,7 +206,8 @@
     buildPresignLedgerRows,
     fetchAllPages,
     PRESIGN_HEADERS,
-    saveCollectionLedgerWorkbook
+    saveCollectionLedgerWorkbook,
+    prepareCollectionLedgerExport
   } from '@/utils/collection-ledger-export'
   import { formatDisplayOrDash } from '@/utils/datetime-display'
   import { formatMoneyDisplay } from '@/utils/money-format'
@@ -262,6 +263,7 @@
     if (exporting.value) return
     exporting.value = true
     try {
+      await prepareCollectionLedgerExport()
       const rows = selectedRows.value.length
         ? selectedRows.value
         : await fetchAllPages((page, pageSize) =>

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

@@ -465,7 +465,8 @@
     RESEARCH_HEADERS,
     buildResearchLedgerRows,
     fetchAllPages,
-    saveCollectionLedgerWorkbook
+    saveCollectionLedgerWorkbook,
+    prepareCollectionLedgerExport
   } from '@/utils/collection-ledger-export'
   import {
     flattenProtocolAuditRows,
@@ -545,6 +546,7 @@
     if (exporting.value) return
     exporting.value = true
     try {
+      await prepareCollectionLedgerExport()
       const rows = selectedFlatRows.value.length
         ? selectedFlatRows.value
         : flattenProtocolAuditRows(
@@ -567,11 +569,13 @@
         ElMessage.warning('没有可导出的数据')
         return
       }
+      const ledger = buildResearchLedgerRows(rows)
       await saveCollectionLedgerWorkbook(
         '科研表',
         RESEARCH_HEADERS,
-        buildResearchLedgerRows(rows),
+        ledger.rows,
-        '科研表'
+        '科研表',
+        ledger.merges
       )
       ElMessage.success('导出成功')
     } catch {

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

@@ -433,7 +433,8 @@
     CELL_STORAGE_HEADERS,
     buildCellStorageLedgerRows,
     fetchAllPages,
-    saveCollectionLedgerWorkbook
+    saveCollectionLedgerWorkbook,
+    prepareCollectionLedgerExport
   } from '@/utils/collection-ledger-export'
   import {
     flattenProtocolAuditRows,
@@ -510,6 +511,7 @@
     if (exporting.value) return
     exporting.value = true
     try {
+      await prepareCollectionLedgerExport()
       const rows = selectedFlatRows.value.length
         ? selectedFlatRows.value
         : flattenProtocolAuditRows(
@@ -532,11 +534,13 @@
         ElMessage.warning('没有可导出的数据')
         return
       }
+      const ledger = buildCellStorageLedgerRows(rows)
       await saveCollectionLedgerWorkbook(
         '自存表',
         CELL_STORAGE_HEADERS,
-        buildCellStorageLedgerRows(rows),
+        ledger.rows,
-        '自存表'
+        '自存表',
+        ledger.merges
       )
       ElMessage.success('导出成功')
     } catch {

web-admin/src/views/cell-storage/sample-info/UnifiedSampleInfoPage.vue

@@ -525,7 +525,8 @@
     CUSTOMER_SERVICE_HEADERS,
     buildCustomerServiceLedgerRows,
     fetchAllPages,
-    saveCollectionLedgerWorkbook
+    saveCollectionLedgerWorkbook,
+    prepareCollectionLedgerExport
   } from '@/utils/collection-ledger-export'
 
   defineOptions({ name: 'UnifiedSampleInfoPage' })
@@ -653,6 +654,7 @@
     if (exporting.value || !isCellStorage.value) return
     exporting.value = true
     try {
+      await prepareCollectionLedgerExport()
       const rows = selectedRows.value.length
         ? selectedRows.value
         : await fetchAllPages((page, pageSize) =>