hn-hlw-service/hlw/download-xlsx/index.js

const dayjs = require("dayjs");
const ExcelJS = require('exceljs');
const Sm4Util = require("../../utils/sm4-util");
const { buildHybridSearchQuery } = require("../diagnostic-record/format");

module.exports = async (item, db, res) => {
  switch (item.downloadType) {
    case 'downloadRx':
      return await downloadRx(item, db, res);
    case 'downloadOrder':
      return await downloadOrder(item, db, res);
    case 'downloadLackMedicineReg':
      return await downloadLackMedicineReg(item, db, res);
    default:
      return '不支持的下载类型';
  }
};

const rxStatusMap = {
  INIT: "待审核",
  PASS: "审核通过",
  UNPASS: "审核未通过",
  EXPIRED: "已取消",
  PASS_INVALID: "已作废",
  UNPASS_INVALID: "已作废",
  INVALID: '已作废',
};
const orderStatusMap = {
  pending: "待处理",
  processing: "处理中",
  cancelled: "已取消",
  completed: "已完成",
  finished: "已结束",
};
const orderSourceMap = {
  ALIPAY_MINI: '支付宝小程序',
  MATEPAD: '门店平板'
}

// 流式写入大数据量
async function downloadStream(db, res, options) {
  try {
    // 关闭 useStyles 提升性能
    const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
      stream: res,
      useStyles: false
    });
    const worksheet = workbook.addWorksheet('Sheet1');
    worksheet.columns = options.columns;

    // 提前设置响应头
    const safeFilename = options.filename.replace(/[<>:"/\\|?*]+/g, '_');
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    );
    res.setHeader(
      'Content-Disposition',
      `attachment; filename=${encodeURIComponent(safeFilename)}.xlsx`
    );

    // 加大批次
    const BATCH_SIZE = 2000;
    let skip = 0;
    let hasMore = true;
    while (hasMore) {
      const batch = await db.collection(options.collection).aggregate([
        ...options.aggregateList,
        { $skip: skip },
        { $limit: BATCH_SIZE }
      ]).toArray();

      batch.forEach(i => {
        worksheet.addRow(options.format(i)).commit();
      });

      skip += batch.length;
      hasMore = batch.length === BATCH_SIZE;
    }

    await worksheet.commit();
    await workbook.commit();
    // 不需要 res.end()，ExcelJS 会自动关闭流
  } catch (e) {
    res.status(500).send('生成Excel文件时出错' + e.message);
  }
}

async function downloadRx(ctx, db, res) {
  const { startDate, endDate, startAuditDate, endAuditDate } = ctx;
  const isStoreRxExport = ctx.prescriptionType === "storeMedicinePurchase";
  const hasDates = startDate && dayjs(startDate).isValid() && endDate && dayjs(endDate).isValid();
  const hasAuditDate = startAuditDate && dayjs(startAuditDate).isValid() && endAuditDate && dayjs(endAuditDate).isValid();
  const query = {};
  if (hasDates) {
    query.createTime = { $gte: dayjs(startDate).startOf('day').valueOf(), $lte: dayjs(endDate).endOf('day').valueOf() };
  }
  if (hasAuditDate) {
    query.auditTime = { $gte: dayjs(startAuditDate).startOf('day').valueOf(), $lte: dayjs(endAuditDate).endOf('day').valueOf() };
  }
  if (typeof ctx.name === 'string' && ctx.name.trim() !== "") {
    const nameCondition = buildHybridSearchQuery("name", ctx.name, false);
    if (nameCondition) Object.assign(query, nameCondition);
  }
  if (typeof ctx.doctorCode === 'string') {
    query.doctorCode = ctx.doctorCode.trim();
  }
  if (typeof ctx.pharmacistNo === 'string' && ctx.pharmacistNo.trim() !== "") {
    query.pharmacistNo = ctx.pharmacistNo.trim();
  }
  const storeIds = [ctx.storeId, ctx.storeLoginNo]
    .filter(value => typeof value === "string" && value.trim())
    .map(value => value.trim());
  if (isStoreRxExport && typeof ctx.storeId === "string" && ctx.storeId.trim()) {
    const store = await db.collection("store-list").findOne(
      { store_id: ctx.storeId.trim() },
      { projection: { login_no: 1 } }
    );
    if (store && typeof store.login_no === "string" && store.login_no.trim()) {
      storeIds.push(store.login_no.trim());
    }
  }
  if (storeIds.length) {
    query.drugStoreNo = { $in: [...new Set(storeIds)] };
  }
  if (isStoreRxExport) {
    query.prescriptionType = "storeMedicinePurchase";
  }
  if (typeof ctx.status === "string" && ctx.status.trim()) {
    query.status = ctx.status.trim();
  }
  if (typeof ctx.medOrgOrderNo === "string" && ctx.medOrgOrderNo.trim()) {
    const keyword = ctx.medOrgOrderNo.trim().replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
    query.medOrgOrderNo = { $regex: keyword, $options: "i" };
  }
  if (["YB", "ZF"].includes(ctx.feeType)) {
    query.feeType = ctx.feeType;
  }

  const columns = isStoreRxExport ? [
    { header: '开具时间', key: 'createTime', width: 20 },
    { header: '费别', key: 'feeType', width: 10 },
    { header: '就诊人姓名', key: 'name', width: 18 },
    { header: '诊断', key: 'diagnosisList', width: 24 },
    { header: '开具药品', key: 'drugs', width: 36 },
    { header: '流转状态', key: 'transferStatus', width: 14 },
    { header: '处方编号', key: 'medOrgOrderNo', width: 24 },
    { header: '开方医生', key: 'doctorName', width: 14 },
    { header: '药品类型', key: 'drugType', width: 12 },
    { header: '打印状态', key: 'printStatus', width: 12 },
  ] : [
    { header: '状态', key: 'status', width: 10 },
    { header: '原因', key: 'reasons', width: 10 },
    { header: '提交时间', key: 'createTime', width: 20 },
    { header: '审核时间', key: 'auditTime', width: 20 },
    { header: '医生', key: 'doctorName', width: 10 },
    { header: '药师', key: 'pharmacist', width: 10 },
    { header: '患者', key: 'name', width: 10 },
    { header: '诊断', key: 'diagnosisList', width: 10 },
    { header: '药品', key: 'drugs', width: 10 },
  ];
  let filename = isStoreRxExport ? '门店处方记录' : '处方记录';
  if (hasDates) {
    filename += `_${dayjs(startDate).format('YYYY-MM-DD')}_${dayjs(endDate).format('YYYY-MM-DD')}`;
  } else if (hasAuditDate) {
    filename += `_${dayjs(startAuditDate).format('YYYY-MM-DD')}_${dayjs(endAuditDate).format('YYYY-MM-DD')}`;
  }

  try {
    const total = await db.collection("diagnostic-record").countDocuments(query);
    if (total > 120000) {
      return { success: false, message: `数据量过大(${total}条数据)，请分批次导出` };
    }
    if (total === 0) {
      return { success: false, message: `没有符合条件的数据` };
    }
    const options = {
      query,
      columns,
      filename,
      collection: 'diagnostic-record',
      aggregateList: [
        { $match: query },
        { $project: { _id: 0, status: 1, createTime: 1, reasons: 1, auditTime: 1, name: 1, sex: 1, age: 1, doctorName: 1, pharmacist: 1, diagnosisList: 1, drugs: 1, wines: 1, orderId: 1, medOrgOrderNo: 1, feeType: 1, hisUploadStatus: 1, prescriptionType: 1, printCount: 1 } },
        { $sort: { createTime: -1 } },
      ],
      format: i => isStoreRxExport ? {
        createTime: i.createTime ? dayjs(i.createTime).format("YYYY-MM-DD HH:mm:ss") : "",
        feeType: i.feeType === "YB" ? "医保" : "自费",
        name: [Sm4Util.decryptDataForSm3(i.name) || i.name, i.sex, i.age > 0 ? `${i.age}岁` : ""].filter(Boolean).join(" "),
        diagnosisList: Array.isArray(i.diagnosisList) ? i.diagnosisList.map(d => d.name || '').join("、") : "",
        drugs: Array.isArray(i.drugs) ? i.drugs.map(d => d.drugName || "").filter(Boolean).join("、") : "",
        transferStatus: i.feeType !== "YB" ? "-" : i.hisUploadStatus === "uploaded" ? "流转完成" : i.hisUploadStatus === "error" ? "流转失败" : "流转中",
        medOrgOrderNo: i.medOrgOrderNo || "",
        doctorName: i.doctorName || "",
        drugType: i.prescriptionType === "medicalWinePurchase" ? "中药" : "西药",
        printStatus: Number(i.printCount) > 0 ? "已打印" : "未打印",
      } : ({
        status: rxStatusMap[i.status] || i.status,
        reasons: Array.isArray(i.reasons) ? i.reasons.join(",") : "",
        createTime: i.createTime ? dayjs(i.createTime).format("YYYY-MM-DD HH:mm:ss") : "",
        auditTime: i.auditTime ? dayjs(i.auditTime).format("YYYY-MM-DD HH:mm:ss") : "",
        doctorName: i.doctorName,
        pharmacist: i.pharmacist,
        name: Sm4Util.decryptDataForSm3(i.name)|| i.name,
        diagnosisList: Array.isArray(i.diagnosisList) ? i.diagnosisList.map(d => d.name || '').join(",") : "",
        drugs: Array.isArray(i.drugs) ? i.drugs.map(d => `${d.drugName} ${d.specification || ''} (${d.dosage}${d.dosage_unit}, ${d.frequencyName}, ${d.days}天)`).join(",") : "",
      })
    }
    await downloadStream(db, res, options);
  } catch (e) {
    return { success: false, message: e.message || "查询失败" };
  }
}

async function downloadOrder(ctx, db, res) {
  const { startDate, endDate } = ctx;
  const hasDates = startDate && dayjs(startDate).isValid() && endDate && dayjs(endDate).isValid();
  const query = {};
  if (hasDates) {
    query.createTime = { $gte: dayjs(startDate).startOf('day').valueOf(), $lte: dayjs(endDate).endOf('day').valueOf() };
  }
  if (typeof ctx.name === 'string' && ctx.name.trim() !== "") {
    query.name = new RegExp(ctx.name);
  }
  const doctorCodes = typeof ctx.doctorCodes === 'string' && ctx.doctorCodes.trim() ? ctx.doctorCodes.split(',').map(code => code.trim()).filter(Boolean) : [];
  if (doctorCodes.length) {
    query.doctorCode = { $in: doctorCodes };
  }
  const storeIds = typeof ctx.storeIds === 'string' && ctx.storeIds.trim() ? ctx.storeIds.split(',').map(code => code.trim()).filter(Boolean) : [];
  if (storeIds.length) {
    query.drugStoreId = { $in: storeIds };
  }
  if (typeof ctx.orderSource === 'string' && ctx.orderSource.trim() !== "") {
    query.orderSource = ctx.orderSource.trim()
  }
  if (['true', 'false'].includes(ctx.isPres)) {
    query.preOrderrId = { $exists: ctx.isPres === 'true' }
  }
  if (typeof ctx.orderStatus === 'string' && ctx.orderStatus.trim() !== "") {
    query.orderStatus = ctx.orderStatus
  }

  const columns = [
    { header: '状态', key: 'status', width: 10 },
    { header: '患者', key: 'name', width: 10 },
    { header: '病历号', key: 'blhno', width: 10 },
    { header: '医生', key: 'doctorName', width: 20 },
    { header: '医生工号', key: 'doctorCode', width: 20 },
    { header: '科室', key: 'deptName', width: 10 },
    { header: '下单时间', key: 'orderTime', width: 10 },
    { header: '问诊类型', key: 'orderType', width: 10 },
    { header: '来源', key: 'drugStoreName', width: 10 },
    { header: '终端', key: 'orderSource', width: 10 },
    { header: '订单号', key: 'orderId', width: 10 },

  ];
  let filename = '咨询订单';
  if (hasDates) {
    filename += `_${dayjs(startDate).format('YYYY-MM-DD')}_${dayjs(endDate).format('YYYY-MM-DD')}`;
  }
  try {
    const total = await db.collection("consult-order").countDocuments(query);
    if (total > 10000) {
      return { success: false, message: `数据量过大(${total}条数据)，请分批次导出` };
    }
    if (total === 0) {
      return { success: false, message: `没有符合条件的数据` };
    }
    const options = {
      query,
      columns,
      filename,
      collection: 'consult-order',
      aggregateList: [
        { $match: query },
        { $project: { _id: 0, orderStatus: 1, name: 1, blhno: 1, createTime: 1, doctorName: 1, doctorCode: 1, deptName: 1, preOrderrId: 1, drugStoreName: 1, orderSource: 1, orderId: 1 } },
        {
          $addFields: {
            orderTime: {
              $dateToString: {
                format: "%Y-%m-%d",
                date: { $toDate: "$createTime" },
                timezone: "Asia/Shanghai" // 设置为中国标准时间
              }
            }
          }
        },
        { $sort: { createTime: -1 } },
      ],
      format: i => ({
        status: orderStatusMap[i.orderStatus] || i.orderStatus,
        name: i.name,
        blhno: i.blhno,
        doctorName: i.doctorName,
        doctorCode: i.doctorCode,
        deptName: i.deptName,
        orderTime: i.orderTime,
        orderType: i.preOrderId ? '在线续方' : '在线复诊',
        drugStoreName: i.drugStoreName,
        orderSource: orderSourceMap[i.orderSource] || i.orderSource,
        orderId: i.orderId,
      })
    }
    await downloadStream(db, res, options);
  } catch (e) {
    return { success: false, message: e.message || "查询失败" };
  }
}

// 缺货登记导出
async function downloadLackMedicineReg(ctx, db, res) {
  const { startTime, endTime } = ctx;
  const hasDates = startTime && dayjs(startTime).isValid() && endTime && dayjs(endTime).isValid();
  const query = {};

  if (hasDates) {
    query.createTime = {
      $gte: dayjs(startTime).startOf('day').valueOf(),
      $lte: dayjs(endTime).endOf('day').valueOf(),
    };
  }

  if (typeof ctx.corpId === 'string' && ctx.corpId.trim() !== "") {
    query.corpId = ctx.corpId.trim();
  }

  if (typeof ctx.drugName === 'string' && ctx.drugName.trim() !== "") {
    query.drugName = new RegExp(ctx.drugName.trim(), 'i');
  }

  if (typeof ctx.inventory === 'string') {
    const inventory = ctx.inventory.trim();
    if (['online', 'store'].includes(inventory)) {
      query.inventory = inventory;
    }
  }

  const columns = [
    { header: '登记时间', key: 'createTime', width: 20 },
    { header: '药品名称', key: 'drugName', width: 20 },
    { header: '规格', key: 'spec', width: 15 },
    { header: '厂家', key: 'manufacturer', width: 25 },
    { header: '医保编码', key: 'insuranceCode', width: 20 },
    { header: '附件数量', key: 'attachmentCount', width: 12 },
    { header: '备注说明', key: 'description', width: 30 },
    { header: '缺货药品库', key: 'storeName', width: 15 },
    { header: '患者姓名', key: 'patientName', width: 15 },
    { header: '联系电话', key: 'phone', width: 15 },
  ];

  let filename = '缺货登记';
  if (hasDates) {
    filename += `_${dayjs(startTime).format('YYYY-MM-DD')}_${dayjs(endTime).format('YYYY-MM-DD')}`;
  }

  try {
    const total = await db.collection("lack-medicine-reg").countDocuments(query);
    if (total > 10000) {
      return { success: false, message: `数据量过大(${total}条数据)，请分批次导出` };
    }
    if (total === 0) {
      return { success: false, message: `没有符合条件的数据` };
    }

    const options = {
      query,
      columns,
      filename,
      collection: 'lack-medicine-reg',
      aggregateList: [
        { $match: query },
        {
          $project: {
            _id: 0,
            createTime: 1,
            drugName: 1,
            spec: 1,
            manufacturer: 1,
            insuranceCode: 1,
            images: 1,
            description: 1,
            inventory: 1,
            patientName: 1,
            phone: 1,
          }
        },
        { $sort: { createTime: -1 } },
      ],
      format: i => ({
        createTime: i.createTime ? dayjs(i.createTime).format("YYYY-MM-DD HH:mm:ss") : "",
        drugName: i.drugName || "",
        spec: i.spec || "",
        manufacturer: i.manufacturer || "",
        insuranceCode: i.insuranceCode || "",
        attachmentCount: Array.isArray(i.images) ? i.images.length : 0,
        description: i.description || "",
        storeName: i.inventory === 'store' ? '药店药品库' : '配送药品库',
        patientName: i.patientName || "",
        phone: i.phone || "",
      })
    };

    await downloadStream(db, res, options);
  } catch (e) {
    return { success: false, message: e.message || "查询失败" };
  }
}