import { flatten } from '@api/helpers/flatten'; import { ForbiddenException, Injectable, NotFoundException, } from '@nestjs/common'; import dayjs from 'dayjs'; import { eq } from 'drizzle-orm'; import { tnAnalysisBasic, tnB2cAnalysisBasic, tnB2cAnalysisCarDetail, } from 'drizzle/schema'; import { stream } from 'exceljs'; import { Response } from 'express'; import { ClinicalResultService } from '../clinical-result/clinical-result.service'; import { DiseaseService } from '../disease/disease.service'; import { MedicationStandardService } from '../medication-standard/medication-standard.service'; import { OrderDetailService } from '../order/order-detail/order-detail.service'; import { AnalyzeWhereBuilder, B2cAnalyzeWhereBuilder, CarAnalyzeWhereBuilder, } from './analyze-where.builder'; import { GetAnalysisAggregateListDto, GetAnalysisBasicListDto, GetAnalysisCarBasicListDto, GetAnalysisCarDetailListDto, GetAnalysisDetailListDto, GetAnalysisPrescribeListDto, ansAggregateType, ansBasicType, } from './analyze.dto'; import { AnalyzeRepository } from './analyze.repository'; const CAR_RESULTS_VISIBLE_FROM = '2026-07-22 16:00:00'; @Injectable() export class AnalyzeService { constructor( private analyzeRepository: AnalyzeRepository, private orderDetailService: OrderDetailService, private diseaseService: DiseaseService, private medicationStandardService: MedicationStandardService, private clinicalResultService: ClinicalResultService, ) {} //* 기업관리자(corp) 접근제어 — 분석 상세가 자기 기업/조직 소속인지 검증. 아니면 403. //* scope = { agcId, orgPath? }. by 로 asbId/acbId 를 oddId 로 해석 후 검증. async assertCorpScope( id: number, scope: { agcId: number; orgPath?: string }, by: 'odd' | 'asb' | 'acb' = 'odd', ) { let oddId: number | undefined = id; if (by === 'asb') oddId = await this.analyzeRepository.getOddIdByAsbId(id); if (by === 'acb') oddId = await this.analyzeRepository.getOddIdByAcbId(id); const ok = !!oddId && (await this.analyzeRepository.isOddInCorpScope( oddId, scope.agcId, scope.orgPath, )); if (!ok) throw new ForbiddenException('접근 권한이 없습니다'); } //*분석결과기본 async getBasicList( oddId: number, dto: GetAnalysisBasicListDto, isB2c: boolean = false, ) { const { page } = dto; // B2C인 경우 if (isB2c) { const bodId = oddId; // B2C는 bodId를 oddId로 받음 const orderDetail = await this.analyzeRepository.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { throw new Error('해당 주문 정보가 존재하지 않습니다.'); } const user = await this.analyzeRepository.getB2cUserById( orderDetail.bcuId!, ); if (!user) { throw new Error('B2C 사용자 정보를 찾을 수 없습니다.'); } // 정회원과 동일하게 무조건 getBasicList 사용 const whereCondition = this.buildB2cWhereCondition( dto.ansType, dto.searchYear, orderDetail.bodCreatedAt, dto.asbDiseaseCode, ); const detailFilterField = this.getDetailFilterField(dto.ansType); const list = await this.analyzeRepository.getB2cBasicList( bodId, whereCondition, detailFilterField, page, dto.ansType, ); const count = await this.analyzeRepository.getB2cBasicCount( bodId, whereCondition, detailFilterField, dto.ansType, ); //* ANS001(3개월 내) — 심평원 '내가 먹는약 한눈에' 신규 데이터 머지 const medicineSummaryList = dto.ansType === ansBasicType.ANS001 ? await this.getMedicineSummaryDiff( bodId, orderDetail.bodCreatedAt, true, ) : []; return { order: { member: { memLoginId: 'B2C' }, user: { usrName: user.bcuName, usrPhone: user.bcuPhone, usrBirth: user.bcuBirth, }, orderDetail: { oddId: bodId, oddTransactionId: orderDetail.bodTransactionId || '', oddState: orderDetail.bodState || '', bodCreatedAt: orderDetail.bodCreatedAt || '', }, }, count, list, medicineSummaryList, }; } // 정규 회원인 경우 const orderDetail = await this.orderDetailService.getOrderDetail(oddId); const where = this.basicWhere( oddId, dto, orderDetail.orderDetail.oddCreatedAt, ); const list = await this.analyzeRepository.getBasicList(where, page); const count = await this.analyzeRepository.basicCount(where); //* ANS001(3개월 내) — 심평원 '내가 먹는약 한눈에' 신규 데이터 머지 const medicineSummaryList = dto.ansType === ansBasicType.ANS001 ? await this.getMedicineSummaryDiff( oddId, orderDetail.orderDetail.oddCreatedAt, false, ) : []; return { order: { ...orderDetail }, count, list, medicineSummaryList, }; } //* 심평원 '내가 먹는약 한눈에' 신규 데이터 조회 (기존 기본진료정보에 없는 최신 건만) private async getMedicineSummaryDiff( id: number, baseDate: string | undefined, isB2c: boolean, ) { //* ANS001 기준 [baseDate-3개월, baseDate] 윈도우. 동기화 시차로 baseDate 이후 일자가 섞이는 케이스를 방지하기 위해 상한도 적용. const oddDate = baseDate ? dayjs(baseDate) : dayjs(); const startDate = oddDate.subtract(3, 'month').format('YYYY-MM-DD'); const endDate = oddDate.format('YYYY-MM-DD'); const rows = isB2c ? await this.analyzeRepository.getB2cMedicineSummaryNewItems( id, startDate, endDate, ) : await this.analyzeRepository.getMedicineSummaryNewItems( id, startDate, endDate, ); return rows.map((row) => ({ //* 조제일 dispenseDate: row.tmsDispenseDate, //* 처방기관 prescribingHospital: row.tmsPrescribingHospital, //* 조제기관 dispensingPharmacy: row.tmsDispensingPharmacy, prescribingHospitalCode: row.tmsPrescribingHospitalCode, dispensingPharmacyCode: row.tmsDispensingPharmacyCode, drugName: row.tmsDrugName, totalDosingDays: row.tmsTotalDosingDays, })); } //*분석결과상세 async getDetailList(asbId: number, dto: GetAnalysisDetailListDto) { //* 분석기본정보 const basicWhere = this.detailBasicWhere(asbId); const basic = await this.analyzeRepository.getBasicList(basicWhere, 1); if (!basic || basic.length === 0) { throw new NotFoundException( `분석 기본 데이터를 찾을 수 없습니다. asbId: ${asbId}`, ); } const listWhere = this.detailWhere(asbId, dto); const list = await this.analyzeRepository.getDetailList(listWhere); return { basic: { ...basic[0].basic }, list, }; } //* 5년내 7일이상 진료/30일이상 투약 기본 //* 11(6)대 질병진단/수술 기본 async getAggregateList( oddId: number, dto: GetAnalysisAggregateListDto, isB2c: boolean = false, ) { if (isB2c) { const bodId = oddId; const orderDetail = await this.analyzeRepository.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { throw new Error('해당 주문 정보가 존재하지 않습니다.'); } const user = await this.analyzeRepository.getB2cUserById( orderDetail.bcuId!, ); if (!user) { throw new Error('B2C 사용자 정보를 찾을 수 없습니다.'); } const { list, count } = await this.analyzeRepository.getB2cAggregateList( bodId, dto, ); return { order: { member: { memLoginId: 'B2C' }, user: { usrName: user.bcuName, usrPhone: user.bcuPhone, usrBirth: user.bcuBirth, }, orderDetail: { oddId: bodId, oddTransactionId: orderDetail.bodTransactionId || '', oddState: orderDetail.bodState || '', bodCreatedAt: orderDetail.bodCreatedAt || '', }, }, count, list, }; } const order = await this.orderDetailService.getOrderDetail(oddId); const { list, count } = await this.analyzeRepository.getAggregateList( oddId, dto, ); return { order: { ...order }, count, list, }; } //* 처방조제정보 async getPrescribeList( oddId: number, dto: GetAnalysisPrescribeListDto, isB2c: boolean = false, ) { if (isB2c) { const bodId = oddId; const list = await this.analyzeRepository.getB2cPrescribeList(bodId, dto); // B2C basicList 조회 - 해당 날짜의 진료 기본 정보 const basicList = await this.analyzeRepository.getB2cPrescribeBasicList( bodId, dto, ); return { basicList, list, }; } const where = this.prescribeBasicWhere(oddId, dto); const basicList = await this.analyzeRepository.getBasicList(where, 0); const list = await this.analyzeRepository.getPrescribeList(oddId, dto); return { basicList, list, }; } //* 원본데이터 다운로드 async downloadOriginalList(oddId: number, res: Response, includeCar = false) { const orderDetail = await this.orderDetailService.getOrderDetail(oddId); const { basic, detail, prescibe, carBasic, carDetail } = await this.analyzeRepository.getOriginalTreatList(oddId); const basicColumns = this.buildExcelBasicColumns(); const detailColumns = this.buildExcelDetailColumns(); const prescibeColumns = this.buildExcelPrescibeColumns(); //TODO ExcelService로 추후 빼기 const filename = encodeURIComponent( orderDetail.user?.usrName + '님의_원본진료정보.xlsx', ); res.setHeader( 'Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', ); res.setHeader('Content-Disposition', `attachment; filename=${filename}`); const workbook = new stream.xlsx.WorkbookWriter({ stream: res }); //* 기본 const basicWorksheet = workbook.addWorksheet('기본진료정보'); const detailWorkSheet = workbook.addWorksheet('세부진료정보'); const prescibeWorkSheet = workbook.addWorksheet('처방조제정보'); const carBasicWorksheet = includeCar ? workbook.addWorksheet('기본진료정보(자동차)') : null; const carDetailWorksheet = includeCar ? workbook.addWorksheet('세부진료정보(자동차)') : null; basicWorksheet.columns = basicColumns; detailWorkSheet.columns = detailColumns; prescibeWorkSheet.columns = prescibeColumns; if (carBasicWorksheet) carBasicWorksheet.columns = this.buildExcelCarBasicColumns(); if (carDetailWorksheet) carDetailWorksheet.columns = this.buildExcelCarDetailColumns(); for await (const data of basic) basicWorksheet.addRow(flatten(data)).commit(); for await (const data of detail) detailWorkSheet.addRow(flatten(data)).commit(); for await (const data of prescibe) prescibeWorkSheet.addRow(flatten(data)).commit(); if (carBasicWorksheet) { for await (const data of carBasic) carBasicWorksheet.addRow(flatten(data)).commit(); } if (carDetailWorksheet) { for await (const data of carDetail) carDetailWorksheet.addRow(flatten(data)).commit(); } basicWorksheet.commit(); detailWorkSheet.commit(); prescibeWorkSheet.commit(); carBasicWorksheet?.commit(); carDetailWorksheet?.commit(); await workbook.commit(); } //* 분석결과기본(자동차보험) async getCarBasicList( oddId: number, dto: GetAnalysisCarBasicListDto, viewerMemId?: number, ) { const { page } = dto; const orderDetail = await this.orderDetailService.getOrderDetail(oddId); if ( viewerMemId !== undefined && orderDetail.member?.memId !== viewerMemId ) { throw new ForbiddenException('접근 권한이 없습니다'); } if ( viewerMemId !== undefined && orderDetail.orderDetail.oddCreatedAt < CAR_RESULTS_VISIBLE_FROM ) { return { order: { ...orderDetail }, count: 0, list: [] }; } const where = this.carBasicWhere( oddId, dto, orderDetail.orderDetail.oddCreatedAt, ); const list = await this.analyzeRepository.getCarBasicList(where, page); const count = await this.analyzeRepository.getCarBasicCount(where); return { order: { ...orderDetail }, count, list, }; } //* 분석결과상세(자동차보험) async getCarDetailList( acbId: number, dto: GetAnalysisCarDetailListDto, viewerMemId?: number, ) { if (viewerMemId !== undefined) { const oddId = await this.analyzeRepository.getOddIdByAcbId(acbId); if (!oddId) throw new ForbiddenException('접근 권한이 없습니다'); const orderDetail = await this.orderDetailService.getOrderDetail(oddId); if (orderDetail.member?.memId !== viewerMemId) { throw new ForbiddenException('접근 권한이 없습니다'); } if (orderDetail.orderDetail.oddCreatedAt < CAR_RESULTS_VISIBLE_FROM) { return { count: 0, list: [] }; } } const where = this.carDetailWhere(acbId, dto); const list = await this.analyzeRepository.getCarDetailList(where); const count = await this.analyzeRepository.getCarDetailCount(where); return { count, list, }; } //* 분석결과 숨은 보험금 전체 목록(PDF 다운로드용) async getHiddenInsuranceList(oddId: number, isB2c: boolean = false) { if (isB2c) { const bodId = oddId; const page = 0; // B2C 주문 정보 조회 const orderDetail = await this.analyzeRepository.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { throw new Error('해당 주문 정보가 존재하지 않습니다.'); } //* 숨은보험금 진단 정보 const { list } = await this.analyzeRepository.getB2cAggregateList(bodId, { asbSicked: 0, ansType: ansAggregateType.ANS006, page: 0, }); //* 숨은보험금 수술 정보 const whereCondition = this.buildB2cWhereCondition( ansBasicType.ANS008, 5, orderDetail.bodCreatedAt, ); const surgeryList = await this.analyzeRepository.getB2cBasicList( bodId, whereCondition, this.getDetailFilterField(ansBasicType.ANS008), page, ansBasicType.ANS008, ); //* 숨은보험금 치아 const toothWhereCondition = this.buildB2cWhereCondition( ansBasicType.ANS007, 5, orderDetail.bodCreatedAt, ); const toothList = await this.analyzeRepository.getB2cBasicList( bodId, toothWhereCondition, this.getDetailFilterField(ansBasicType.ANS007), page, ansBasicType.ANS007, ); //* 숨은보험금 기타 const etcWhereCondition = this.buildB2cWhereCondition( ansBasicType.ANS009, 5, orderDetail.bodCreatedAt, ); const etcList = await this.analyzeRepository.getB2cBasicList( bodId, etcWhereCondition, this.getDetailFilterField(ansBasicType.ANS009), page, ansBasicType.ANS009, ); //* legacy 정책(2026-08): B2C PDF 묶음에도 자동차(자부상) 진료내역을 포함한다. //* (이전에는 내부 카나리 목적으로 의도적 제외였음 — 제품 결정으로 반전) //* 노출 범위는 일반회원과 동일하게 CAR_RESULTS_VISIBLE_FROM 게이트를 따른다. const carBasicList = orderDetail.bodCreatedAt < CAR_RESULTS_VISIBLE_FROM ? [] : await this.analyzeRepository.getB2cCarBasicList(bodId, page); //* SECURITY: b2cCarDetailWhere는 bacbId로만 필터한다(bodId join 없음). //* bacbId는 반드시 위 carBasicList(이미 bodId로 필터됨) 행에서만 가져올 것. //* getB2cCarBasicList가 bacbId를 acbId로 alias 하므로 basic.acbId가 곧 bacbId다. const carList = await Promise.all( carBasicList.map(async (basic) => ({ ...basic, detailList: await this.analyzeRepository.getB2cCarDetailList( this.b2cCarDetailWhere(basic.acbId), ), })), ); return { diagnosticsList: list.map((item) => { return { babDiseaseName: item.basic.asbDiseaseName, babDiseaseCode: item.basic.asbDiseaseCode, babTreatStartDate: item.basic.asbTreatStartDate, babTreatEndDate: item.basic.asbTreatEndDate, inDiseaseCount: item.basic.inDiseaseCount, babInDisease: item.basic.asbInDisease, }; }), surgeryList: surgeryList.map((item) => { return { babHospitalName: item.basic.asbHospitalName, babDiseaseName: item.basic.asbDiseaseName, babDiseaseCode: item.basic.asbDiseaseCode, babTreatStartDate: item.basic.asbTreatStartDate, badInOperation: item.detail.asdInOperation, inOperationCount: item.detail.inOperationCount, }; }), toothList: toothList.map((item) => { return { babHospitalName: item.basic.asbHospitalName, babDiseaseName: item.basic.asbDiseaseName, babDiseaseCode: item.basic.asbDiseaseCode, babTreatStartDate: item.basic.asbTreatStartDate, badInTooth: item.detail.asdInTooth, inToothCount: item.detail.inToothCount, }; }), etcList: etcList.map((item) => { return { babHospitalName: item.basic.asbHospitalName, babDiseaseName: item.basic.asbDiseaseName, babDiseaseCode: item.basic.asbDiseaseCode, babTreatStartDate: item.basic.asbTreatStartDate, badInEtc: item.detail.asdInEtc, inEtcCount: item.detail.inEtcCount, }; }), carList, }; } const orderDetail = await this.orderDetailService.getOrderDetail(oddId); const page = 0; //* 숨은보험금 진단 정보 const { list } = await this.analyzeRepository.getAggregateList(oddId, { asbSicked: 0, ansType: ansAggregateType.ANS006, page: 0, }); //* 숨은보험금 수술 정보 let where = this.basicWhere( oddId, { page: page, ansType: ansBasicType.ANS008, searchYear: 5, }, orderDetail.orderDetail.oddCreatedAt, ); const surgeryList = await this.analyzeRepository.getBasicList(where, page); //* 숨은보험금 치아 where = this.basicWhere( oddId, { page: page, ansType: ansBasicType.ANS007, searchYear: 5, }, orderDetail.orderDetail.oddCreatedAt, ); const toothList = await this.analyzeRepository.getBasicList(where, page); //* 숨은보험금 기타 where = this.basicWhere( oddId, { page: page, ansType: ansBasicType.ANS009, searchYear: 5, }, orderDetail.orderDetail.oddCreatedAt, ); const etcList = await this.analyzeRepository.getBasicList(where, page); //* legacy 정책(2026-08): PDF 묶음에도 자동차(자부상) 진료내역을 포함한다. //* (이전에는 내부 카나리 목적으로 의도적 제외였음 — 제품 결정으로 반전) //* 노출 범위는 화면(getCarBasicList)과 동일하게 CAR_RESULTS_VISIBLE_FROM 게이트를 따른다. const carBasicList = orderDetail.orderDetail.oddCreatedAt < CAR_RESULTS_VISIBLE_FROM ? [] : await this.analyzeRepository.getCarBasicList( this.carBasicWhere( oddId, { page }, orderDetail.orderDetail.oddCreatedAt, ), page, ); //* SECURITY: carDetailWhere는 acbId로만 필터한다(oddId join 없음). //* acbId는 반드시 위 carBasicList(이미 oddId로 필터됨) 행에서만 가져올 것. //* client 입력에서 받으면 그대로 cross-tenant IDOR이 된다. //* ponytail: 사고 그룹 수는 보통 1~3건이라 그룹별 상세 조회로 충분하다. //* 그룹이 수십 건으로 늘면 oddId 기준 일괄 조회 후 acbId로 그룹핑하도록 바꾼다. const carList = await Promise.all( carBasicList.map(async (basic) => ({ ...basic, detailList: await this.analyzeRepository.getCarDetailList( this.carDetailWhere(basic.acbId, { page }), ), })), ); return { diagnosticsList: list.map((item) => { return { asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatEndDate: item.basic.asbTreatEndDate, inDiseaseCount: item.basic.inDiseaseCount, asbInDisease: item.basic.asbInDisease, }; }), surgeryList: surgeryList.map((item) => { return { asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asdInOperation: item.detail.asdInOperation, inOperationCount: item.detail.inOperationCount, }; }), toothList: toothList.map((item) => { return { asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asdInTooth: item.detail.asdInTooth, }; }), etcList: etcList.map((item) => { return { asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asdInEtc: item.detail.asdInEtc, inEtcCount: item.detail.inEtcCount, }; }), carList, }; } //* 분석결과 주의사항 전체 목록(PDF 다운로드용) async getCautionPdfList(oddId: number, isB2c: boolean = false) { if (isB2c) { const bodId = oddId; const orderDetail = await this.analyzeRepository.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { throw new Error('해당 주문 정보가 존재하지 않습니다.'); } const { list } = await this.analyzeRepository.getB2cAggregateList(bodId, { asbSicked: 0, ansType: ansAggregateType.ANS013, page: 0, }); return { cautionList: list.map((item) => ({ asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatEndDate: item.basic.asbTreatEndDate, asbNotice: item.basic.asbNotice, noticeCount: item.basic.noticeCount, })), }; } const { list } = await this.analyzeRepository.getAggregateList(oddId, { asbSicked: 0, ansType: ansAggregateType.ANS013, page: 0, }); return { cautionList: list.map((item) => ({ asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatEndDate: item.basic.asbTreatEndDate, asbNotice: item.basic.asbNotice, noticeCount: item.basic.noticeCount, })), }; } //* 분석결과 건강체 전체 목록(PDF 다운로드용) async getHealthyPdfList(oddId: number, isB2c: boolean = false) { if (isB2c) { const bodId = oddId; const orderDetail = await this.analyzeRepository.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { throw new Error('해당 주문 정보가 존재하지 않습니다.'); } const page = 0; // ANS001: 3개월 내 const ans001Where = this.buildB2cWhereCondition( 'ANS001', undefined, orderDetail.bodCreatedAt, ); const ans001List = await this.analyzeRepository.getB2cBasicList( bodId, ans001Where, this.getDetailFilterField('ANS001'), page, 'ANS001', ); // ANS002: 1년 내 검사 const ans002Where = this.buildB2cWhereCondition( 'ANS002', undefined, orderDetail.bodCreatedAt, ); const ans002List = await this.analyzeRepository.getB2cBasicList( bodId, ans002Where, this.getDetailFilterField('ANS002'), page, 'ANS002', ); // ANS003: 5년 내 7일/30일 투약 (aggregate) const { list: ans003List } = await this.analyzeRepository.getB2cAggregateList(bodId, { asbSicked: 0, ansType: ansAggregateType.ANS003, page: 0, }); // ANS004: 5년 내 입원/수술 const ans004Where = this.buildB2cWhereCondition( 'ANS004', 5, orderDetail.bodCreatedAt, ); const ans004List = await this.analyzeRepository.getB2cBasicList( bodId, ans004Where, this.getDetailFilterField('ANS004'), page, 'ANS004', ); // ANS005: 5년 내 11대 중대질병 (aggregate, asbSicked=0) const { list: ans005List } = await this.analyzeRepository.getB2cAggregateList(bodId, { asbSicked: 0, ansType: ansAggregateType.ANS005, page: 0, }); return this.mapHealthyPdfData( ans001List, ans002List, ans003List, ans004List, ans005List, ); } const orderDetail = await this.orderDetailService.getOrderDetail(oddId); const page = 0; // ANS001: 3개월 내 let where = this.basicWhere( oddId, { page, ansType: ansBasicType.ANS001, asbDiseaseCode: '', searchYear: 5 }, orderDetail.orderDetail.oddCreatedAt, ); const ans001List = await this.analyzeRepository.getBasicList(where, page); // ANS002: 1년 내 검사 where = this.basicWhere( oddId, { page, ansType: ansBasicType.ANS002, asbDiseaseCode: '', searchYear: 5 }, orderDetail.orderDetail.oddCreatedAt, ); const ans002List = await this.analyzeRepository.getBasicList(where, page); // ANS003: 5년 내 7일/30일 투약 (aggregate) const { list: ans003List } = await this.analyzeRepository.getAggregateList( oddId, { asbSicked: 0, ansType: ansAggregateType.ANS003, page: 0, }, ); // ANS004: 5년 내 입원/수술 where = this.basicWhere( oddId, { page, ansType: ansBasicType.ANS004, asbDiseaseCode: '', searchYear: 5 }, orderDetail.orderDetail.oddCreatedAt, ); const ans004List = await this.analyzeRepository.getBasicList(where, page); // ANS005: 5년 내 11대 중대질병 (aggregate, asbSicked=0) const { list: ans005List } = await this.analyzeRepository.getAggregateList( oddId, { asbSicked: 0, ansType: ansAggregateType.ANS005, page: 0, }, ); return this.mapHealthyPdfData( ans001List, ans002List, ans003List, ans004List, ans005List, ); } //* 분석결과 유병자 전체 목록(PDF 다운로드용) async getSickPdfList(oddId: number, isB2c: boolean = false) { if (isB2c) { const bodId = oddId; const orderDetail = await this.analyzeRepository.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { throw new Error('해당 주문 정보가 존재하지 않습니다.'); } const page = 0; // ANS001: 3개월 내 const ans001Where = this.buildB2cWhereCondition( 'ANS001', undefined, orderDetail.bodCreatedAt, ); const ans001List = await this.analyzeRepository.getB2cBasicList( bodId, ans001Where, this.getDetailFilterField('ANS001'), page, 'ANS001', ); // ANS004_2: 2년 내 입원/수술 const ans004_2Where = this.buildB2cWhereCondition( 'ANS004', 2, orderDetail.bodCreatedAt, ); const ans004_2List = await this.analyzeRepository.getB2cBasicList( bodId, ans004_2Where, this.getDetailFilterField('ANS004'), page, 'ANS004', ); // ANS004_5: 5년 내 입원/수술 const ans004_5Where = this.buildB2cWhereCondition( 'ANS004', 5, orderDetail.bodCreatedAt, ); const ans004_5List = await this.analyzeRepository.getB2cBasicList( bodId, ans004_5Where, this.getDetailFilterField('ANS004'), page, 'ANS004', ); // ANS005: 5년 내 6대 중대질병 (aggregate, asbSicked=1) const { list: ans005List } = await this.analyzeRepository.getB2cAggregateList(bodId, { asbSicked: 1, ansType: ansAggregateType.ANS005, page: 0, }); return this.mapSickPdfData( ans001List, ans004_2List, ans004_5List, ans005List, ); } const orderDetail = await this.orderDetailService.getOrderDetail(oddId); const page = 0; // ANS001: 3개월 내 let where = this.basicWhere( oddId, { page, ansType: ansBasicType.ANS001, asbDiseaseCode: '', searchYear: 5 }, orderDetail.orderDetail.oddCreatedAt, ); const ans001List = await this.analyzeRepository.getBasicList(where, page); // ANS004_2: 2년 내 입원/수술 where = this.basicWhere( oddId, { page, ansType: ansBasicType.ANS004, asbDiseaseCode: '', searchYear: 2 }, orderDetail.orderDetail.oddCreatedAt, ); const ans004_2List = await this.analyzeRepository.getBasicList(where, page); // ANS004_5: 5년 내 입원/수술 where = this.basicWhere( oddId, { page, ansType: ansBasicType.ANS004, asbDiseaseCode: '', searchYear: 5 }, orderDetail.orderDetail.oddCreatedAt, ); const ans004_5List = await this.analyzeRepository.getBasicList(where, page); // ANS005: 5년 내 6대 중대질병 (aggregate, asbSicked=1) const { list: ans005List } = await this.analyzeRepository.getAggregateList( oddId, { asbSicked: 1, ansType: ansAggregateType.ANS005, page: 0, }, ); return this.mapSickPdfData( ans001List, ans004_2List, ans004_5List, ans005List, ); } private mapHealthyPdfData( ans001List: any[], ans002List: any[], ans003List: any[], ans004List: any[], ans005List: any[], ) { return { ans001: ans001List.map((item) => ({ asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatType: item.basic.asbTreatType, asbVisitDays: item.basic.asbVisitDays, asdOperation: item.detail?.asdOperation, operationCount: item.detail?.operationCount, asdExamination: item.detail?.asdExamination, examinationCount: item.detail?.examinationCount, asbDosingDays: item.basic.asbDosingDays, asdDosingDays: item.detail?.asdDosingDays, asbEtc: item.basic.asbEtc, asbDuplicated: item.basic.asbDuplicated, asbDrugNotice: item.basic.asbDrugNotice, })), ans002: ans002List.map((item) => ({ asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatType: item.basic.asbTreatType, asbVisitDays: item.basic.asbVisitDays, asdExamination: item.detail?.asdExamination, examinationCount: item.detail?.examinationCount, asbEtc: item.basic.asbEtc, })), ans003: ans003List.map((item) => ({ asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatEndDate: item.basic.asbTreatEndDate, asbVisitDays: item.basic.asbVisitDays, asbDosingDays: item.basic.asbDosingDays, })), ans004: ans004List.map((item) => ({ asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatType: item.basic.asbTreatType, asbVisitDays: item.basic.asbVisitDays, asdOperation: item.detail?.asdOperation, operationCount: item.detail?.operationCount, asbDrugNotice: item.basic.asbDrugNotice, })), ans005: ans005List.map((item) => ({ asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatEndDate: item.basic.asbTreatEndDate, asbVisitDays: item.basic.asbVisitDays, asbDosingDays: item.basic.asbDosingDays, asdOperation: item.detail?.asdOperation, })), }; } private mapSickPdfData( ans001List: any[], ans004_2List: any[], ans004_5List: any[], ans005List: any[], ) { return { ans001: ans001List.map((item) => ({ asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatType: item.basic.asbTreatType, asbVisitDays: item.basic.asbVisitDays, asdOperation: item.detail?.asdOperation, operationCount: item.detail?.operationCount, asdExamination: item.detail?.asdExamination, examinationCount: item.detail?.examinationCount, asbDosingDays: item.basic.asbDosingDays, asdDosingDays: item.detail?.asdDosingDays, asbEtc: item.basic.asbEtc, asbDuplicated: item.basic.asbDuplicated, asbDrugNotice: item.basic.asbDrugNotice, })), ans004_2: ans004_2List.map((item) => ({ asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatType: item.basic.asbTreatType, asbVisitDays: item.basic.asbVisitDays, asdOperation: item.detail?.asdOperation, operationCount: item.detail?.operationCount, })), ans004_5: ans004_5List.map((item) => ({ asbHospitalName: item.basic.asbHospitalName, asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatType: item.basic.asbTreatType, asbVisitDays: item.basic.asbVisitDays, asdOperation: item.detail?.asdOperation, operationCount: item.detail?.operationCount, })), ans005: ans005List.map((item) => ({ asbDiseaseName: item.basic.asbDiseaseName, asbDiseaseCode: item.basic.asbDiseaseCode, asbTreatStartDate: item.basic.asbTreatStartDate, asbTreatEndDate: item.basic.asbTreatEndDate, asbVisitDays: item.basic.asbVisitDays, asbDosingDays: item.basic.asbDosingDays, asdOperation: item.detail?.asdOperation, })), }; } //* 5년 내 7일이상 진료 그룹 조회 async getTreatment7DayGroups(id: number, isB2c: boolean = false) { if (isB2c) { return await this.diseaseService.getTreatment7DayGroups( undefined, id, undefined, ); } return await this.diseaseService.getTreatment7DayGroups( id, undefined, undefined, ); } //* 5년 내 30일이상 투약 그룹 조회 async getDosing30DayGroups(id: number, isB2c: boolean = false) { if (isB2c) { return await this.medicationStandardService.getDosing30DayGroups( undefined, id, undefined, ); } return await this.medicationStandardService.getDosing30DayGroups( id, undefined, undefined, ); } //* 기타 숨은보험금 그룹 조회 (ANS009) async getEtcInsuranceGroups(oddId?: number, bodId?: number, codId?: number) { return await this.diseaseService.getEtcInsuranceGroups(oddId, bodId, codId); } //* 동일 진단코드 내원 그룹 조회 async getDiseaseCodeVisitGroups(id: number, isB2c: boolean = false) { if (isB2c) { return await this.diseaseService.getDiseaseCodeVisitGroups( undefined, id, undefined, ); } return await this.diseaseService.getDiseaseCodeVisitGroups( id, undefined, undefined, ); } //* 치아 숨은보험금 그룹 조회 (ANS007) async getToothInsuranceGroups( oddId?: number, bodId?: number, codId?: number, ) { return await this.diseaseService.getToothInsuranceGroups( oddId, bodId, codId, ); } //*--------------------------------------------- excel ---------------------------------------------// /** 엑셀 다운로드 컬럼 */ buildExcelBasicColumns(): { header: string; key: string }[] { return [ { header: '진료일자', key: 'trbTreatStartDate' }, { header: '병/의원&약국', key: 'trbHospitalName' }, { header: '진단과', key: 'trbDepartment' }, { header: '타입', key: 'trbTreatType' }, { header: '주상병코드', key: 'trbDiseaseCode' }, { header: '주상병명', key: 'trbDiseaseName' }, { header: '내원일', key: 'trbVisitDays' }, { header: '총진료비', key: 'trbTotalAmount' }, { header: '혜택받은금액', key: 'trbPublicCharge' }, { header: '내가낸의료비', key: 'trbDeductibleAmt' }, ]; } buildExcelDetailColumns(): { header: string; key: string }[] { return [ { header: '진료일자', key: 'trdTreatStartDate' }, { header: '병/의원&약국', key: 'trdHospitalName' }, { header: '진료형태', key: 'trdTreatType' }, { header: '코드명', key: 'trdCodeName' }, { header: '1회투약량', key: 'trdOneDose' }, { header: '1회투여횟수', key: 'trdDailyDosesNumber' }, { header: '총 투약일수', key: 'trdTotalDosingDays' }, ]; } buildExcelPrescibeColumns(): { header: string; key: string }[] { return [ { header: '진료일자', key: 'trpTreatStartDate' }, { header: '병/의원&약국', key: 'trpHospitalName' }, { header: '진료형태', key: 'trpTreatType' }, { header: '약품명', key: 'trpDrugName' }, { header: '성분명', key: 'trpIngredients' }, { header: '1회투약량', key: 'trpOneDose' }, { header: '1회투여횟수', key: 'trpDailyDosesNumber' }, { header: '총 투약일수', key: 'trpTotalDosingDays' }, ]; } //* 자동차 보험 추가 buildExcelCarBasicColumns(): { header: string; key: string }[] { return [ { header: '진료일자', key: 'tcbTreatStartDate' }, { header: '병/의원&약국', key: 'tcbHospitalName' }, { header: '진단과', key: 'tcbDepartment' }, { header: '타입', key: 'tcbTreatType' }, { header: '주상병코드', key: 'tcbDiseaseCode' }, { header: '주상병명', key: 'tcbDiseaseName' }, { header: '진료일수', key: 'tcbTreatDate' }, { header: '총진료비', key: 'tcbTotalAmount' }, { header: '자동차보험 총진료비', key: 'tcbMedicalFee' }, ]; } //* 자동차 보험(상세) 추가 buildExcelCarDetailColumns(): { header: string; key: string }[] { return [ { header: '진료일자', key: 'tcdTreatStartDate' }, { header: '병/의원&약국', key: 'tcdHospitalName' }, { header: '진료형태', key: 'tcdTreatType' }, { header: '코드명', key: 'tcdCodeName' }, { header: '1회투약량', key: 'tcdOneDose' }, { header: '1회투여횟수', key: 'tcdDailyDosesNumber' }, { header: '총 투약일수', key: 'tcdTotalDosingDays' }, ]; } //*--------------------------------------------- where ---------------------------------------------// private basicWhere( oddId: number, dto: GetAnalysisBasicListDto, oddDate: string, ) { return new AnalyzeWhereBuilder({ oddId, dto, oddDate }) .searchId() .ansType('basic') .build(); } private detailBasicWhere(asbId: number) { //* 기본정보는 basic 테이블 PK 로 조회한다. //* (searchId() 는 basic↔detail 좌조인의 detail 컬럼을 필터하므로 //* detail 이 없는 basic 이 통째로 누락되어 500 이 발생했다.) return eq(tnAnalysisBasic.asbId, asbId); } private detailWhere(asbId: number, dto: GetAnalysisDetailListDto) { return new AnalyzeWhereBuilder({ asbId, dto }) .searchId() .ansType('detail') .build(); } private prescribeBasicWhere(oddId: number, dto: GetAnalysisPrescribeListDto) { const { searchDate } = dto; return new AnalyzeWhereBuilder({ oddId, asbTreatStartDate: searchDate }) .searchId() .searchDate() .build(); } private carBasicWhere( oddId: number, dto: GetAnalysisCarBasicListDto, oddDate: string, ) { return new CarAnalyzeWhereBuilder({ oddId, dto, oddDate }) .searchId() .build(); } private carDetailWhere(acbId: number, dto: GetAnalysisCarDetailListDto) { return new CarAnalyzeWhereBuilder({ acbId, dto }).searchId().build(); } //*------------------------ Schedule ------------------------*// async deleteAnalyze() { return await this.analyzeRepository.delete(); } async deleteB2cAnalyze() { return await this.analyzeRepository.deleteB2c(); } //*------------------------ 병명예측 PDF 데이터 ------------------------*// async getDiseasePredictionPdf(oddId: number, isB2c: boolean = false) { //* 기업코드는 변경될 수 있으므로 환경별 불변 agcId로만 임시 마스킹한다. const hideNameAgencyId = Number( process.env.DISEASE_PDF_HIDE_NAME_AGENCY_ID, ); const [ recommendedInsuranceData, insuranceReferenceData, medicationData, examinationData, treatmentLogData, hidePatientName, ] = await Promise.all([ this.diseaseService.getRecommendedInsuranceByDiseaseForPdf(oddId, isB2c), this.diseaseService.getInsuranceReferenceTreeForPdf(oddId, isB2c), isB2c ? this.medicationStandardService.getRecommendedMedicationForPdf( undefined, oddId, ) // B2C는 bodId로 전달 : this.medicationStandardService.getRecommendedMedicationForPdf(oddId), isB2c ? this.clinicalResultService.getRecommendedExaminationForPdf( undefined, oddId, ) // B2C는 bodId로 전달 : this.clinicalResultService.getRecommendedExaminationForPdf(oddId), this.diseaseService.getTreatmentLogForPdf(oddId, isB2c), isB2c || !Number.isInteger(hideNameAgencyId) || hideNameAgencyId <= 0 ? false : this.analyzeRepository.isOddInCorpScope(oddId, hideNameAgencyId), ]); return { hidePatientName, recommendedInsurance: recommendedInsuranceData, insuranceReference: insuranceReferenceData, medication: medicationData, examination: examinationData, treatmentLog: treatmentLogData, }; } // B2C WHERE 조건 생성 헬퍼 메서드 (정회원 AnalyzeWhereBuilder와 동일한 로직) private buildB2cWhereCondition( ansType: string, searchYear?: number, bodCreatedAt?: string, asbDiseaseCode?: string, asbSicked?: number, ): string { const oddDate = bodCreatedAt ? dayjs(bodCreatedAt) : dayjs(); let startDate: string; let baseCondition: string; //* ★보안(C3 SQLi): asbDiseaseCode가 baseCondition에 문자열 보간되고 하류에서 sql.raw로 실행되므로 //* 인젝션 표면(특히 비인증 admin-view 경로로 도달). 상병코드(KCD)는 영숫자·점·하이픈만 가지므로 //* 그 외 문자가 있으면 거부 → 따옴표/세미콜론/공백 주입 불가. (정상 코드는 전부 통과) if (asbDiseaseCode && !/^[A-Za-z0-9.\-]+$/.test(asbDiseaseCode)) { throw new Error('잘못된 상병코드 형식'); } switch (ansType) { case 'ANS001': // 3개월 이내 (정회원과 동일: 약국제외 없음, sicked 조건 없음) startDate = oddDate.subtract(3, 'month').format('YYYY-MM-DD'); baseCondition = `basb_treat_start_date >= '${startDate}'`; break; case 'ANS002': // 1년 이내 검사 + 약국제외 (정회원과 동일: sicked 조건 없음) startDate = oddDate.subtract(365, 'day').format('YYYY-MM-DD'); baseCondition = `basb_treat_start_date >= '${startDate}' AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS003': // diseaseCode 필수 + 외래 + 약국제외 if (!asbDiseaseCode) { throw new Error('ANS003 requires asbDiseaseCode'); } baseCondition = `basb_disease_code = '${asbDiseaseCode}' AND basb_treat_type = '외래' AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS004': // searchYear 필수 + 약국제외 (입원/수술 조건은 repository에서 처리) // 정회원: searchYear가 2면 730일, 아니면 날짜 조건 없음 if (searchYear === 2) { startDate = oddDate.subtract(730, 'day').format('YYYY-MM-DD'); baseCondition = `basb_treat_start_date >= '${startDate}' AND basb_hospital_name NOT LIKE '%약국%'`; } else { baseCondition = `basb_hospital_name NOT LIKE '%약국%'`; } break; case 'ANS005': // diseaseCode 필수 + 약국제외 (정회원과 동일: 날짜 조건 없음) if (!asbDiseaseCode) { throw new Error('ANS005 requires asbDiseaseCode'); } baseCondition = `basb_disease_code = '${asbDiseaseCode}' AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS006': // diseaseCode 필수 + inDisease (약국 포함) if (!asbDiseaseCode) { throw new Error('ANS006 requires asbDiseaseCode'); } baseCondition = `basb_disease_code = '${asbDiseaseCode}' AND basb_in_disease IS NOT NULL`; break; case 'ANS007': // inTooth=1 + 약국제외 (정회원과 동일: 날짜 조건 없음) baseCondition = `basb_in_tooth = 1 AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS008': // 약국제외 (정회원과 동일: 날짜 조건 없음, detail 필터는 repository에서) baseCondition = `basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS009': // 약국제외 (정회원과 동일: 날짜 조건 없음, detail 필터는 repository에서) baseCondition = asbDiseaseCode ? `basb_disease_code = '${asbDiseaseCode}' AND basb_hospital_name NOT LIKE '%약국%'` : `basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS010': // dentures + 약국제외 (정회원과 동일: 날짜 조건 없음) baseCondition = `basb_dentures IS NOT NULL AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS011': // 1년 이내 + toothOne + 약국제외 startDate = oddDate.subtract(365, 'day').format('YYYY-MM-DD'); baseCondition = `basb_tooth_one IS NOT NULL AND basb_treat_start_date >= '${startDate}' AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS012': // toothFive + 약국제외 (정회원과 동일: 날짜 조건 없음) baseCondition = `basb_tooth_five IS NOT NULL AND basb_hospital_name NOT LIKE '%약국%'`; break; case 'ANS013': // diseaseCode 필수 + notice + 약국제외 if (!asbDiseaseCode) { throw new Error('ANS013 requires asbDiseaseCode'); } baseCondition = `basb_disease_code = '${asbDiseaseCode}' AND basb_notice IS NOT NULL AND basb_hospital_name NOT LIKE '%약국%'`; break; default: // ANS014 등: 조건 없음 (정회원과 동일: switch에 case 없으면 조건 추가 안 함) baseCondition = '1=1'; break; } return baseCondition; } private getDetailFilterField(ansType: string): string | undefined { switch (ansType) { case 'ANS001': return '(basd_operation IS NOT NULL OR basd_examination IS NOT NULL)'; // 3개월내 수술/검사 case 'ANS002': return 'basd_examination'; // 1년내 검사 case 'ANS007': return 'basd_in_tooth'; // 숨은보험-치아 case 'ANS008': return 'basd_in_operation'; // 숨은보험-수술 case 'ANS009': return 'basd_in_etc'; // 숨은보험-기타 case 'ANS010': return undefined; // Basic 테이블만 체크 case 'ANS011': return 'basd_tooth_one'; case 'ANS012': return 'basd_tooth_five'; default: return undefined; } } //* B2C 자동차보험 상세 (정회원과 동일하게 bcbId로만 조회) async getB2cCarDetailList(bcbId: number, page: number) { const where = this.b2cCarDetailWhere(bcbId); const list = await this.analyzeRepository.getB2cCarDetailList(where); const count = await this.analyzeRepository.getB2cCarDetailCount(where); return { count, list, }; } private b2cCarDetailWhere(bacbId: number) { return eq(tnB2cAnalysisCarDetail.bacbId, bacbId); } //* B2C 진단 상세 (정회원과 동일한 로직으로 처리) async getB2cDetailList(basbId: number, dto: GetAnalysisDetailListDto) { //* 분석기본정보 const basicWhere = this.b2cDetailBasicWhere(basbId); const basic = await this.analyzeRepository.getB2cBasicSingle(basicWhere); //* 상세정보 (ansType에 따른 필터링 적용) const listWhere = this.b2cDetailWhere(basbId, dto); const list = await this.analyzeRepository.getB2cDetailList(listWhere); return { basic: { ...basic[0].basic }, list, }; } private b2cDetailBasicWhere(basbId: number) { return eq(tnB2cAnalysisBasic.basbId, basbId); } private b2cDetailWhere(basbId: number, dto: GetAnalysisDetailListDto) { return new B2cAnalyzeWhereBuilder({ basbId, dto }) .searchId() .ansType('detail') .build(); } /** * B2C 상담신청 상태 업데이트 */ async updateB2cConsultRequest(bodId: number) { const result = await this.analyzeRepository.updateB2cConsultRequest(bodId); if (!result) { throw new Error('상담신청 업데이트에 실패했습니다.'); } return result; } }