import { DrizzleService } from '@lib/drizzle/src/drizzle.service'; import { Injectable } from '@nestjs/common'; import { ConfigService } from '@nestjs/config'; import dayjs from 'dayjs'; import { and, asc, count, desc, eq, gte, inArray, isNotNull, lte, max, min, notLike, or, sql, SQL, } from 'drizzle-orm'; import { tcMedicationStandard, tnAgency, tnAnalysisBasic, tnAnalysisCarBasic, tnAnalysisCarDetail, tnAnalysisDetail, tnAnalysisPrescibe, tnB2cAnalysisBasic, tnB2cAnalysisCarBasic, tnB2cAnalysisCarDetail, tnB2cAnalysisDetail, tnB2cAnalysisPrescribe, tnB2cOrderDetail, tnB2cUser, tnClientAnalysisBasic, tnClientAnalysisCarBasic, tnMember, tnOrder, tnOrderDetail, tnPayment, tnSubscribe, ttB2cTreatBasic, ttB2cTreatCarBasic, ttB2cTreatCarDetail, ttB2cTreatDetail, ttB2cTreatMedicineSummary, ttB2cTreatPrescribe, ttTreatBasic, ttTreatCarBasic, ttTreatCarDetail, ttTreatDetail, ttTreatMedicineSummary, ttTreatPrescribe, } from 'drizzle/schema'; import { GetAnalysisAggregateListDto, GetAnalysisPrescribeListDto, } from './analyze.dto'; @Injectable() export class AnalyzeRepository { private key: string; constructor( private drizzle: DrizzleService, private configService: ConfigService, ) { this.key = this.configService.get('aes.secret') ?? ''; } //------------------------- basic -------------------------// async getBasicList(where: SQL | undefined, page: number) { const limit = 10; const offset = (+page - 1) * limit; let list: any = []; if (page === 0) { list = await this.buildDynamicBasicQuery(where); } else { list = await this.buildDynamicBasicQuery(where) .limit(limit) .offset(offset); } return list; } buildDynamicBasicQuery(where: SQL | undefined) { //* 약국의 경우 투약일수를 정확하게 매칭이 가능하므로, 약국일 경우엔 asdDosingDays로 병원은 asbDosingDays return this.drizzle .select({ basic: { oddId: tnAnalysisBasic.oddId, asbId: tnAnalysisBasic.asbId, asbTreatStartDate: tnAnalysisBasic.asbTreatStartDate, asbHospitalName: tnAnalysisBasic.asbHospitalName, asbDepartment: tnAnalysisBasic.asbDepartment, asbTreatType: tnAnalysisBasic.asbTreatType, asbDiseaseCode: tnAnalysisBasic.asbDiseaseCode, asbDiseaseName: tnAnalysisBasic.asbDiseaseName, asbVisitDays: tnAnalysisBasic.asbVisitDays, asbDisease: tnAnalysisBasic.asbDisease, asbInDisease: tnAnalysisBasic.asbInDisease, asbNotice: tnAnalysisBasic.asbNotice, asbDentures: tnAnalysisBasic.asbDentures, asbToothOne: tnAnalysisBasic.asbToothOne, asbToothFive: tnAnalysisBasic.asbToothFive, asbEtc: tnAnalysisBasic.asbEtc, asbSicked: tnAnalysisBasic.asbSicked, asbDrugNotice: tnAnalysisBasic.asbDrugNotice, asbDosingDays: tnAnalysisBasic.asbDosingDays, asbDuplicated: tnAnalysisBasic.asbDuplicated, }, detail: { //* 분석상세의 각 항목에 대한 1건 도출 asdCodeName: max(tnAnalysisDetail.asdCodeName).as('asdCodeName'), asdOperation: max(tnAnalysisDetail.asdOperation).as('asdOperation'), asdExamination: min(tnAnalysisDetail.asdExamination).as( 'asdExamination', ), asdInTooth: max(tnAnalysisDetail.asdInTooth).as('asdInTooth'), asdInOperation: max(tnAnalysisDetail.asdInOperation).as( 'asdInOperation', ), asdInEtc: max(tnAnalysisDetail.asdInEtc).as('asdInEtc'), asdDentures: max(tnAnalysisDetail.asdDentures).as('asdDentures'), asdToothOne: max(tnAnalysisDetail.asdToothOne).as('asdToothOne'), asdToothFive: max(tnAnalysisDetail.asdToothFive).as('asdToothFive'), asdDosingDays: max(tnAnalysisDetail.asdDosingDays).as( 'asdDosingDays', ), //* 분석상세의 각 항목에 대한 상세 수량 operationCount: count(tnAnalysisDetail.asdOperation), examinationCount: count(tnAnalysisDetail.asdExamination), inToothCount: count(tnAnalysisDetail.asdInTooth), inOperationCount: count(tnAnalysisDetail.asdInOperation), inEtcCount: count(tnAnalysisDetail.asdInEtc), denturesCount: count(tnAnalysisDetail.asdDentures), toothOneCount: count(tnAnalysisDetail.asdToothOne), toothFiveCount: count(tnAnalysisDetail.asdToothFive), }, }) .from(tnAnalysisBasic) .leftJoin( tnAnalysisDetail, eq(tnAnalysisDetail.asbId, tnAnalysisBasic.asbId), ) .where(where) .groupBy( tnAnalysisBasic.asbId, tnAnalysisBasic.asbTreatStartDate, tnAnalysisBasic.asbHospitalName, tnAnalysisBasic.asbDepartment, tnAnalysisBasic.asbTreatType, tnAnalysisBasic.asbDiseaseCode, tnAnalysisBasic.asbDiseaseName, tnAnalysisBasic.asbVisitDays, tnAnalysisBasic.asbDisease, tnAnalysisBasic.asbInDisease, tnAnalysisBasic.asbNotice, tnAnalysisBasic.asbDentures, tnAnalysisBasic.asbToothOne, tnAnalysisBasic.asbToothFive, tnAnalysisBasic.asbEtc, tnAnalysisBasic.asbSicked, tnAnalysisBasic.asbDrugNotice, tnAnalysisBasic.asbDosingDays, tnAnalysisBasic.asbDuplicated, ) .orderBy(asc(tnAnalysisBasic.asbId)) .$dynamic(); } async basicCount(where: SQL | undefined) { //* count가 groupBy로 묶이므로 groupBy가 있는 곳은 length로 가져올 것 const totalCount = await this.drizzle .select({ count: sql`count(*)`.mapWith(Number) }) .from(tnAnalysisBasic) .leftJoin( tnAnalysisDetail, eq(tnAnalysisDetail.asbId, tnAnalysisBasic.asbId), ) .where(where) .groupBy( tnAnalysisBasic.asbId, tnAnalysisBasic.asbTreatStartDate, tnAnalysisBasic.asbHospitalName, tnAnalysisBasic.asbTreatType, tnAnalysisBasic.asbDiseaseCode, tnAnalysisBasic.asbDiseaseName, tnAnalysisBasic.asbVisitDays, tnAnalysisBasic.asbDisease, tnAnalysisBasic.asbInDisease, tnAnalysisBasic.asbDentures, tnAnalysisBasic.asbToothOne, tnAnalysisBasic.asbToothFive, tnAnalysisBasic.asbEtc, tnAnalysisBasic.asbSicked, tnAnalysisBasic.asbDrugNotice, tnAnalysisBasic.asbDosingDays, tnAnalysisBasic.asbDuplicated, ); return totalCount.length; } //------------------------- detail -------------------------// async getDetailList(where: SQL | undefined) { return await this.drizzle .select({ asdId: tnAnalysisDetail.asdId, asdCodeName: tnAnalysisDetail.asdCodeName, asdOperation: tnAnalysisDetail.asdOperation, asdExamination: tnAnalysisDetail.asdExamination, asdInTooth: tnAnalysisDetail.asdInTooth, asdInOperation: tnAnalysisDetail.asdInOperation, asdInEtc: tnAnalysisDetail.asdInEtc, asdDentures: tnAnalysisDetail.asdDentures, asdToothOne: tnAnalysisDetail.asdToothOne, asdToothFive: tnAnalysisDetail.asdToothFive, asdDosingDays: tnAnalysisDetail.asdDosingDays, }) .from(tnAnalysisDetail) .where(where) .orderBy( asc(tnAnalysisDetail.asdExamination), asc(tnAnalysisDetail.asdOperation), asc(tnAnalysisDetail.asdInTooth), asc(tnAnalysisDetail.asdInOperation), asc(tnAnalysisDetail.asdDentures), asc(tnAnalysisDetail.asdToothOne), asc(tnAnalysisDetail.asdToothFive), asc(tnAnalysisDetail.asdId), ); } //------------------------- aggregate -------------------------// /** * (ANS003) 5년내 7일이상 진료, 30일이상 투약 * (ANS005) 5년내 질병, 진단 * (ANS006) 숨은보험 진단 * @param id * @param dto * @returns */ async getAggregateList(id: number, dto: GetAnalysisAggregateListDto) { const { page, ansType, asbSicked } = dto; const limit = 10; const offset = (+page - 1) * limit; //* 숨은보험진단(ANS006), 보상찾아줘-기타(ANS009)의 경우만 약국도 포함 const sq1Where = ansType === 'ANS006' || ansType === 'ANS009' ? and(eq(tnAnalysisBasic.oddId, id)) : and( eq(tnAnalysisBasic.oddId, id), notLike(tnAnalysisBasic.asbHospitalName, '%약국%'), ); //* ANS009(보상찾아줘-기타)일 경우 asdInEtc별로 행을 분리해야 하므로 GROUP BY에 포함 const sq1 = this.drizzle .select({ asbId: tnAnalysisBasic.asbId, asbTreatStartDate: tnAnalysisBasic.asbTreatStartDate, asbHospitalName: tnAnalysisBasic.asbHospitalName, asbDiseaseCode: tnAnalysisBasic.asbDiseaseCode, asbDiseaseName: sql`REPLACE(REPLACE(${tnAnalysisBasic.asbDiseaseName}, '(양방)', ''), '(한방)', '') `.as( 'asbDiseaseName', ), asbTreatType: tnAnalysisBasic.asbTreatType, asbVisitDays: tnAnalysisBasic.asbVisitDays, asbDosingDays: tnAnalysisBasic.asbDosingDays, asbDisease: tnAnalysisBasic.asbDisease, asbInDisease: tnAnalysisBasic.asbInDisease, asbNotice: tnAnalysisBasic.asbNotice, asbSicked: tnAnalysisBasic.asbSicked, asbDuplicated: tnAnalysisBasic.asbDuplicated, //투약중복여부 asdOperation: tnAnalysisDetail.asdOperation, //상세의 요약 1건 asdInEtc: ansType === 'ANS009' ? tnAnalysisDetail.asdInEtc //ANS009: GROUP BY에 포함하므로 MAX 불필요 : max(tnAnalysisDetail.asdInEtc).as('asdInEtc'), //기타: 숨은보험 기타 }) .from(tnAnalysisBasic) .where(sq1Where) .leftJoin( tnAnalysisDetail, eq(tnAnalysisDetail.asbId, tnAnalysisBasic.asbId), ) .groupBy( tnAnalysisBasic.asbId, tnAnalysisBasic.asbTreatStartDate, tnAnalysisBasic.asbHospitalName, ...(ansType === 'ANS009' ? [tnAnalysisDetail.asdInEtc] : []), ) .as('sq1'); /** * 2차쿼리 조건절 * ANS003일 경우 외래만 가져오는 것으로 조건 추가됨 */ const sq2 = this.drizzle .select({ asbTreatStartDate: min(sq1.asbTreatStartDate).as('asbTreatStartDate'), asbTreatEndDate: max(sq1.asbTreatStartDate).as('asbTreatEndDate'), asbDiseaseCode: sq1.asbDiseaseCode, asbDiseaseName: sq1.asbDiseaseName, asbInDisease: max(sq1.asbInDisease).as('asbInDisease'), asbNotice: max(sq1.asbNotice).as('asbNotice'), inDiseaseCount: count(sq1.asbInDisease).as('asbInDiseaseCount'), noticeCount: count(sq1.asbNotice).as('asbNoticeCount'), asbInEtc: max(sq1.asdInEtc).as('asbInEtc'), inEtcCount: count(sq1.asdInEtc).as('asbInEtcCount'), visitDaysCount: count(sq1.asbVisitDays).as('visitDaysCount'), asbTreatType: max(sq1.asbTreatType).as('asbTreatType'), asbVisitDays: sql`sum(${sq1.asbVisitDays})`.as('asbVisitDays'), asbDosingDays: sql`sum(${sq1.asbDosingDays})`.as( 'asbDosingDays', ), asdOperation: max(sq1.asdOperation).as('asdOperation'), asbDisease: max(sq1.asbDisease).as('asbDisease'), asbSicked: max(sq1.asbSicked).as('asbSicked'), asbDuplicated: max(sq1.asbDuplicated).as('asbDuplicated'), }) .from(sq1) .where(ansType === 'ANS003' ? eq(sq1.asbTreatType, '외래') : undefined) .groupBy( sq1.asbDiseaseCode, sq1.asbDiseaseName, ...(ansType === 'ANS009' ? [sq1.asdInEtc] : []), ) .as('sq2'); /** * 최종 쿼리의 조건절 * ANS003일 경우 7일이상 치료 또는 30일이상 투약의 건 * ANS006(숨은보험진단)의 건강체일 경우, 질병에 해당하는 건 * ANS013(주의사항)일 경우 주의사항 해당하는 건 * ANS005의 유병자일 경우, 유병자질병에 해당하는 건 */ const where = ansType === 'ANS003' ? or(gte(sq2.asbVisitDays, 7), gte(sq2.asbDosingDays, 30)) : ansType === 'ANS006' ? isNotNull(sq2.asbInDisease) : ansType === 'ANS009' ? isNotNull(sq2.asbInEtc) : ansType === 'ANS013' ? isNotNull(sq2.asbNotice) : ansType === 'ANS005' && asbSicked === 1 ? and(isNotNull(sq2.asbDisease), eq(sq2.asbSicked, 1)) : isNotNull(sq2.asbDisease); let list: any = []; if (page === 0) { list = await this.drizzle .select({ basic: { asbTreatStartDate: sq2.asbTreatStartDate, asbTreatEndDate: sq2.asbTreatEndDate, asbDiseaseCode: sq2.asbDiseaseCode, asbDiseaseName: sq2.asbDiseaseName, asbInDisease: sq2.asbInDisease, asbNotice: sq2.asbNotice, asbVisitDays: sql`${sq2.asbVisitDays}`.mapWith(Number), asbDosingDays: sql`${sq2.asbDosingDays}`.mapWith(Number), asbDuplicated: sql`${sq2.asbDuplicated}`.mapWith(Number), inDiseaseCount: sql`${sq2.inDiseaseCount}`.mapWith(Number), noticeCount: sql`${sq2.noticeCount}`.mapWith(Number), asbInEtc: sq2.asbInEtc, inEtcCount: sql`${sq2.inEtcCount}`.mapWith(Number), visitDaysCount: sql`${sq2.visitDaysCount}`.mapWith(Number), }, detail: { asdOperation: sq2.asdOperation, }, }) .from(sq2) .where(where) .orderBy(asc(sq2.asbTreatStartDate)); } else { list = await this.drizzle .select({ basic: { asbTreatStartDate: sq2.asbTreatStartDate, asbTreatEndDate: sq2.asbTreatEndDate, asbDiseaseCode: sq2.asbDiseaseCode, asbDiseaseName: sq2.asbDiseaseName, asbInDisease: sq2.asbInDisease, asbNotice: sq2.asbNotice, asbVisitDays: sql`${sq2.asbVisitDays}`.mapWith(Number), asbDosingDays: sql`${sq2.asbDosingDays}`.mapWith(Number), asbDuplicated: sql`${sq2.asbDuplicated}`.mapWith(Number), inDiseaseCount: sql`${sq2.inDiseaseCount}`.mapWith(Number), noticeCount: sql`${sq2.noticeCount}`.mapWith(Number), asbInEtc: sq2.asbInEtc, inEtcCount: sql`${sq2.inEtcCount}`.mapWith(Number), visitDaysCount: sql`${sq2.visitDaysCount}`.mapWith(Number), }, detail: { asdOperation: sq2.asdOperation, }, }) .from(sq2) .where(where) .orderBy(asc(sq2.asbTreatStartDate)) .limit(limit) .offset(offset); } const [totalCount] = await this.drizzle .select({ count: count() }) .from(sq2) .where(where); return { list, count: totalCount?.count ?? 0 }; } //------------------------- prescribe -------------------------// async getPrescribeList(id: number, dto: GetAnalysisPrescribeListDto) { return await this.drizzle .select({ aspTreatStartDate: tnAnalysisPrescibe.aspTreatStartDate, aspHospitalName: tnAnalysisPrescibe.aspHospitalName, aspTreatType: tnAnalysisPrescibe.aspTreatType, aspDrugName: tnAnalysisPrescibe.aspDrugName, aspIngredients: tnAnalysisPrescibe.aspIngredients, aspOneDose: tnAnalysisPrescibe.aspOneDose, aspDailyDosesNumber: tnAnalysisPrescibe.aspDailyDosesNumber, aspTotalDosingDays: tnAnalysisPrescibe.aspTotalDosingDays, // 약품명 ↔ 투약기준(병명예측관리 투약로그) 대조로 효능효과 조회 // mscCode 중복 등록 시 행 증식을 막기 위해 상관 서브쿼리로 단일 값만 반환 aspEfficacy: sql`( SELECT ${tcMedicationStandard.mscName} FROM ${tcMedicationStandard} WHERE REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tnAnalysisPrescibe.aspDrugName}, ' ', '') COLLATE utf8mb4_general_ci LIMIT 1 )`, }) .from(tnAnalysisPrescibe) .where( and( eq(tnAnalysisPrescibe.oddId, id), eq(tnAnalysisPrescibe.aspTreatStartDate, dto.searchDate), // notLike(tnAnalysisPrescibe.aspHospitalName, '%약국%'), ), ) .orderBy(asc(tnAnalysisPrescibe.aspId)); } //------------------------- original data -------------------------// async getOriginalTreatList(id: number) { const basic = await this.drizzle .select() .from(ttTreatBasic) .where(eq(ttTreatBasic.oddId, id)); const detail = await this.drizzle .select() .from(ttTreatDetail) .where(eq(ttTreatDetail.oddId, id)); const prescibe = await this.drizzle .select() .from(ttTreatPrescribe) .where(eq(ttTreatPrescribe.oddId, id)); //* 자동차 보험 추가 const carBasic = await this.drizzle .select() .from(ttTreatCarBasic) .where(eq(ttTreatCarBasic.oddId, id)); const carDetail = await this.drizzle .select() .from(ttTreatCarDetail) .where(eq(ttTreatCarDetail.oddId, id)); return { basic, detail, prescibe, carBasic, carDetail }; } async getOriginalTreatBasicList(id: number) { return await this.drizzle .select() .from(ttTreatBasic) .where(eq(ttTreatBasic.oddId, id)); } // async getOriginalTreatDetailList(id: number) { // return await this.drizzle // .select() // .from(ttTreatDetail) // .where(eq(ttTreatDetail.oddId, id)); // } // async getOriginalTreatPrescibeList(id: number) { // return await this.drizzle // .select() // .from(ttTreatPrescribe) // .where(eq(ttTreatPrescribe.oddId, id)); // } //------------------------- car basic -------------------------// async getCarBasicList(where: SQL | undefined, page: number) { const limit = 10; const offset = (+page - 1) * limit; let list: any = []; if (page === 0) { list = await this.buildDynamicCarBasicQuery(where); } else { list = await this.buildDynamicCarBasicQuery(where) .limit(limit) .offset(offset); } return list; } buildDynamicCarBasicQuery(where: SQL | undefined) { return this.drizzle .select({ acbId: tnAnalysisCarBasic.acbId, oddId: tnAnalysisCarBasic.oddId, acbTreatStartDate: tnAnalysisCarBasic.acbTreatStartDate, acbTreatEndDate: tnAnalysisCarBasic.acbTreatEndDate, acbTotalTreatDate: tnAnalysisCarBasic.acbTotalTreatDate, acbTotalAmount: tnAnalysisCarBasic.acbTotalAmount, acbOutpatientTotalAmount: tnAnalysisCarBasic.acbOutpatientTotalAmount, acbInpatientTotalAmount: tnAnalysisCarBasic.acbInpatientTotalAmount, }) .from(tnAnalysisCarBasic) .where(where) .orderBy(asc(tnAnalysisCarBasic.acbId)) .$dynamic(); } async getCarBasicCount(where: SQL | undefined) { const [totalCount] = await this.drizzle .select({ count: count() }) .from(tnAnalysisCarBasic) .where(where); return totalCount.count ?? 0; } //------------------------- car detail -------------------------// async getCarDetailList(where: SQL | undefined) { return this.drizzle .select({ acdId: tnAnalysisCarDetail.acdId, acdTreatStartDate: tnAnalysisCarDetail.acdTreatStartDate, acdHospitalName: tnAnalysisCarDetail.acdHospitalName, acdDepartment: tnAnalysisCarDetail.acdDepartment, acdTreatType: tnAnalysisCarDetail.acdTreatType, acdDiseaseCode: tnAnalysisCarDetail.acdDiseaseCode, acdDiseaseName: tnAnalysisCarDetail.acdDiseaseName, acdTreatDate: tnAnalysisCarDetail.acdTreatDate, acdTotalAmount: tnAnalysisCarDetail.acdTotalAmount, acdMedicalFee: tnAnalysisCarDetail.acdMedicalFee, }) .from(tnAnalysisCarDetail) .where(where); } async getCarDetailCount(where: SQL | undefined) { const [totalCount] = await this.drizzle .select({ count: count() }) .from(tnAnalysisCarDetail) .where(where); return totalCount.count ?? 0; } //------------------------- schedule -------------------------// //* 스케줄러를 통한 30일 경과시 분석결과 데이터 삭제(기본, 상세, 자동차진료기본, 자동차진료상세) async delete() { const nowDate = dayjs(); const subDate = nowDate.subtract(30, 'day').format('YYYY-MM-DD HH:mm:ss'); //* 생성된지 30일이 지난 주문상세건 조회 const orderDetail = await this.drizzle .select({ oddId: tnOrderDetail.oddId, }) .from(tnOrderDetail) .where(lte(tnOrderDetail.oddCreatedAt, subDate)); //* id값만 추출해 number 배열 생성 const ids = orderDetail.map((item) => item.oddId); //* 주문 id 기준으로 분석결과 정보 삭제 //* 삭제된 주문 상세건의 연결된 user_id 정보도 null 처리 await this.drizzle.transaction(async (tx) => { await Promise.all([ tx.delete(tnAnalysisDetail).where(inArray(tnAnalysisDetail.oddId, ids)), tx .delete(tnAnalysisCarDetail) .where(inArray(tnAnalysisCarDetail.oddId, ids)), tx .delete(tnAnalysisPrescibe) .where(inArray(tnAnalysisPrescibe.oddId, ids)), ]); await Promise.all([ tx.delete(tnAnalysisBasic).where(inArray(tnAnalysisBasic.oddId, ids)), tx .delete(tnAnalysisCarBasic) .where(inArray(tnAnalysisCarBasic.oddId, ids)), ]); await tx .update(tnOrderDetail) .set({ usrId: null, }) .where(inArray(tnOrderDetail.oddId, ids)); }); } async deleteB2c() { const subDate = dayjs().subtract(30, 'day').format('YYYY-MM-DD HH:mm:ss'); //* 생성된지 30일이 지난 B2C 주문상세건 조회 const orderDetail = await this.drizzle .select({ bodId: tnB2cOrderDetail.bodId, }) .from(tnB2cOrderDetail) .where(lte(tnB2cOrderDetail.bodCreatedAt, subDate)); const ids = orderDetail.map((item) => item.bodId); if (ids.length === 0) return; //* 주문 id 기준으로 B2C 분석결과 정보 삭제 await this.drizzle.transaction(async (tx) => { await Promise.all([ tx .delete(tnB2cAnalysisDetail) .where(inArray(tnB2cAnalysisDetail.bodId, ids)), tx .delete(tnB2cAnalysisCarDetail) .where(inArray(tnB2cAnalysisCarDetail.bodId, ids)), tx .delete(tnB2cAnalysisPrescribe) .where(inArray(tnB2cAnalysisPrescribe.bodId, ids)), ]); await Promise.all([ tx .delete(tnB2cAnalysisBasic) .where(inArray(tnB2cAnalysisBasic.bodId, ids)), tx .delete(tnB2cAnalysisCarBasic) .where(inArray(tnB2cAnalysisCarBasic.bodId, ids)), ]); await tx .update(tnB2cOrderDetail) .set({ bcuId: null, }) .where(inArray(tnB2cOrderDetail.bodId, ids)); }); } //------------------------- B2C basic -------------------------// async getB2cUserWithOrderDetail(bcuId: number) { const result = await this.drizzle .select({ bcuId: tnB2cUser.bcuId, bcuName: sql`FN_DECRYPT(${tnB2cUser.bcuName}, ${this.key})`, bcuPhone: sql`FN_DECRYPT(${tnB2cUser.bcuPhone}, ${this.key})`, bcuBirth: sql`FN_DECRYPT(${tnB2cUser.bcuBirth}, ${this.key})`, bodId: tnB2cOrderDetail.bodId, bodTransactionId: tnB2cOrderDetail.bodTransactionId, bodState: tnB2cOrderDetail.bodState, bodCreatedAt: tnB2cOrderDetail.bodCreatedAt, }) .from(tnB2cUser) .leftJoin( tnB2cOrderDetail, eq(tnB2cUser.bcuId, tnB2cOrderDetail.bcuId), ) .where(eq(tnB2cUser.bcuId, bcuId)) .limit(1); return result[0] || null; } async getB2cBasicList(bodId: number, whereCondition?: string, detailFilterField?: string, page: number = 1, ansType?: string) { const limit = 10; const offset = (page - 1) * limit; // WHERE 조건 생성 let whereClause = eq(tnB2cAnalysisBasic.bodId, bodId); if (whereCondition) { whereClause = and(whereClause, sql.raw(whereCondition)) as any; } // ANS004는 특별 처리: 입원 OR 수술 if (ansType === 'ANS004') { whereClause = and( whereClause, sql.raw(`( basb_treat_type = '입원' OR EXISTS ( SELECT 1 FROM tn_b2c_analysis_detail d WHERE d.basb_id = tn_b2c_analysis_basic.basb_id AND basd_operation IS NOT NULL ) )`) ) as any; } // detail 필터 조건을 서브쿼리로 추가 (페이지네이션 전에 필터링) else if (detailFilterField) { const filterCondition = detailFilterField.includes('IS NOT NULL') ? detailFilterField : `${detailFilterField} IS NOT NULL`; whereClause = and( whereClause, sql.raw(`EXISTS ( SELECT 1 FROM tn_b2c_analysis_detail d WHERE d.basb_id = tn_b2c_analysis_basic.basb_id AND ${filterCondition} )`) ) as any; } // 정회원과 동일한 방식으로 LEFT JOIN + GROUP BY로 한 번에 조회 const groupByFields = [ tnB2cAnalysisBasic.basbId, tnB2cAnalysisBasic.basbTreatStartDate, tnB2cAnalysisBasic.basbHospitalName, tnB2cAnalysisBasic.basbDepartment, tnB2cAnalysisBasic.basbTreatType, tnB2cAnalysisBasic.basbDiseaseCode, tnB2cAnalysisBasic.basbDiseaseName, tnB2cAnalysisBasic.basbVisitDays, tnB2cAnalysisBasic.basbDisease, tnB2cAnalysisBasic.basbInDisease, tnB2cAnalysisBasic.basbNotice, tnB2cAnalysisBasic.basbDentures, tnB2cAnalysisBasic.basbToothOne, tnB2cAnalysisBasic.basbToothFive, tnB2cAnalysisBasic.basbEtc, tnB2cAnalysisBasic.basbSicked, tnB2cAnalysisBasic.basbDrugNotice, tnB2cAnalysisBasic.basbDosingDays, tnB2cAnalysisBasic.basbDuplicated, ]; const query = this.drizzle .select({ basic: { oddId: tnB2cAnalysisBasic.bodId, asbId: tnB2cAnalysisBasic.basbId, asbTreatStartDate: tnB2cAnalysisBasic.basbTreatStartDate, asbHospitalName: tnB2cAnalysisBasic.basbHospitalName, asbDepartment: tnB2cAnalysisBasic.basbDepartment, asbTreatType: tnB2cAnalysisBasic.basbTreatType, asbDiseaseCode: tnB2cAnalysisBasic.basbDiseaseCode, asbDiseaseName: tnB2cAnalysisBasic.basbDiseaseName, asbVisitDays: tnB2cAnalysisBasic.basbVisitDays, asbDisease: tnB2cAnalysisBasic.basbDisease, asbInDisease: tnB2cAnalysisBasic.basbInDisease, asbNotice: tnB2cAnalysisBasic.basbNotice, asbDentures: tnB2cAnalysisBasic.basbDentures, asbToothOne: tnB2cAnalysisBasic.basbToothOne, asbToothFive: tnB2cAnalysisBasic.basbToothFive, asbEtc: tnB2cAnalysisBasic.basbEtc, asbSicked: tnB2cAnalysisBasic.basbSicked, asbDrugNotice: tnB2cAnalysisBasic.basbDrugNotice, asbDosingDays: tnB2cAnalysisBasic.basbDosingDays, asbDuplicated: tnB2cAnalysisBasic.basbDuplicated, }, detail: { asdCodeName: max(tnB2cAnalysisDetail.basdCodeName).as('asdCodeName'), asdOperation: max(tnB2cAnalysisDetail.basdOperation).as('asdOperation'), asdExamination: min(tnB2cAnalysisDetail.basdExamination).as('asdExamination'), asdDosingDays: max(tnB2cAnalysisDetail.basdDosingDays).as('asdDosingDays'), asdInTooth: max(tnB2cAnalysisDetail.basdInTooth).as('asdInTooth'), asdInOperation: max(tnB2cAnalysisDetail.basdInOperation).as('asdInOperation'), asdInEtc: max(tnB2cAnalysisDetail.basdInEtc).as('asdInEtc'), asdDentures: max(tnB2cAnalysisDetail.basdDentures).as('asdDentures'), asdToothOne: max(tnB2cAnalysisDetail.basdToothOne).as('asdToothOne'), asdToothFive: max(tnB2cAnalysisDetail.basdToothFive).as('asdToothFive'), operationCount: count(tnB2cAnalysisDetail.basdOperation), examinationCount: count(tnB2cAnalysisDetail.basdExamination), inToothCount: count(tnB2cAnalysisDetail.basdInTooth), inOperationCount: count(tnB2cAnalysisDetail.basdInOperation), inEtcCount: count(tnB2cAnalysisDetail.basdInEtc), denturesCount: count(tnB2cAnalysisDetail.basdDentures), toothOneCount: count(tnB2cAnalysisDetail.basdToothOne), toothFiveCount: count(tnB2cAnalysisDetail.basdToothFive), }, }) .from(tnB2cAnalysisBasic) .leftJoin( tnB2cAnalysisDetail, eq(tnB2cAnalysisDetail.basbId, tnB2cAnalysisBasic.basbId), ) .where(whereClause) .groupBy(...groupByFields) .orderBy(asc(tnB2cAnalysisBasic.basbId)); if (page === 0) { return await query; } else { return await query.limit(limit).offset(offset); } } async getB2cBasicById(basbId: number) { const [result] = await this.drizzle .select({ basbId: tnB2cAnalysisBasic.basbId, bodId: tnB2cAnalysisBasic.bodId, }) .from(tnB2cAnalysisBasic) .where(eq(tnB2cAnalysisBasic.basbId, basbId)); return result; } async getB2cBasicCount(bodId: number, whereCondition?: string, detailFilterField?: string, ansType?: string) { // WHERE 조건 생성 let whereClause = eq(tnB2cAnalysisBasic.bodId, bodId); if (whereCondition) { whereClause = and(whereClause, sql.raw(whereCondition)) as any; } // ANS004는 특별 처리: 입원 OR 수술 if (ansType === 'ANS004') { whereClause = and( whereClause, sql.raw(`( basb_treat_type = '입원' OR EXISTS ( SELECT 1 FROM tn_b2c_analysis_detail d WHERE d.basb_id = tn_b2c_analysis_basic.basb_id AND basd_operation IS NOT NULL ) )`) ) as any; } // detail 필터 조건 추가 else if (detailFilterField) { // detailFilterField가 이미 완전한 조건인 경우(ANS001 등)와 단일 필드인 경우 모두 처리 const filterCondition = detailFilterField.includes('IS NOT NULL') ? detailFilterField : `${detailFilterField} IS NOT NULL`; whereClause = and( whereClause, sql.raw(`EXISTS ( SELECT 1 FROM tn_b2c_analysis_detail d WHERE d.basb_id = tn_b2c_analysis_basic.basb_id AND ${filterCondition} )`) ) as any; } // groupBy 결과의 개수 반환 const countResult = await this.drizzle .select({ count: sql`count(*)` }) .from(tnB2cAnalysisBasic) .where(whereClause) .groupBy( tnB2cAnalysisBasic.basbId, tnB2cAnalysisBasic.basbTreatStartDate, tnB2cAnalysisBasic.basbHospitalName, tnB2cAnalysisBasic.basbDepartment, tnB2cAnalysisBasic.basbTreatType, tnB2cAnalysisBasic.basbDiseaseCode, tnB2cAnalysisBasic.basbDiseaseName, tnB2cAnalysisBasic.basbVisitDays, tnB2cAnalysisBasic.basbDisease, tnB2cAnalysisBasic.basbInDisease, tnB2cAnalysisBasic.basbNotice, tnB2cAnalysisBasic.basbDentures, tnB2cAnalysisBasic.basbToothOne, tnB2cAnalysisBasic.basbToothFive, tnB2cAnalysisBasic.basbEtc, tnB2cAnalysisBasic.basbSicked, tnB2cAnalysisBasic.basbDrugNotice, tnB2cAnalysisBasic.basbDosingDays, tnB2cAnalysisBasic.basbDuplicated, ); return countResult.length; } async checkB2cOrderDetailByBodId(bodId: number) { const [orderDetail] = await this.drizzle .select({ bodId: tnB2cOrderDetail.bodId, bcuId: tnB2cOrderDetail.bcuId, bodAgcCode: tnB2cOrderDetail.bodAgcCode, bodTransactionId: tnB2cOrderDetail.bodTransactionId, bodState: tnB2cOrderDetail.bodState, bodConsultRequested: tnB2cOrderDetail.bodConsultRequested, bodCreatedAt: tnB2cOrderDetail.bodCreatedAt, }) .from(tnB2cOrderDetail) .where(eq(tnB2cOrderDetail.bodId, bodId)); return orderDetail; } async createB2cOrderDetail(bcuId: number, agcCode: string, transactionId: string, marketingAgree: number = 0) { const [result] = await this.drizzle .insert(tnB2cOrderDetail) .values({ bcuId: bcuId, bodAgcCode: agcCode, bodTransactionId: transactionId, bodState: 'BOD001', // 인증요청중 bodMarketingAgree: marketingAgree, }) .$returningId(); return result.bodId; } async updateB2cOrderDetail(bodId: number, updates: { bodState?: string; bodTransactionId?: string; bodCompletedAt?: string; bodFailedAt?: string; bodUpdatedAt?: string; bodExposed?: number; bodAssignedStatus?: number; bodAssignedMemberId?: string; bodAssignedDate?: Date; }) { // 분석 완료 시 bod_exposed를 1로 설정 const updateData: any = { ...updates, bodUpdatedAt: updates.bodUpdatedAt || sql`CURRENT_TIMESTAMP`, }; if (updates.bodState && updates.bodState === 'BOD004') { updateData.bodExposed = 1; } return await this.drizzle .update(tnB2cOrderDetail) .set(updateData) .where(eq(tnB2cOrderDetail.bodId, bodId)); } async getB2cOrderDetailByAgcCode(agcCode: string) { const [result] = await this.drizzle .select({ bodId: tnB2cOrderDetail.bodId, bcuId: tnB2cOrderDetail.bcuId, bodTransactionId: tnB2cOrderDetail.bodTransactionId, bodAgcCode: tnB2cOrderDetail.bodAgcCode, bodState: tnB2cOrderDetail.bodState, bodAgcId: tnAgency.agcId, bodAgcCorporateName: tnAgency.agcCorporateName, }) .from(tnB2cOrderDetail) .leftJoin(tnAgency, eq(tnAgency.agcCorporateCode, tnB2cOrderDetail.bodAgcCode)) .where(eq(tnB2cOrderDetail.bodAgcCode, agcCode)) .orderBy(desc(tnB2cOrderDetail.bodCreatedAt)) .limit(1); return result; } async getB2cUserIdByPhone(phone: string) { const [result] = await this.drizzle .select({ bcuId: tnB2cUser.bcuId, }) .from(tnB2cUser) .where(sql`${tnB2cUser.bcuPhone} = FN_ENCRYPT(${phone}, ${this.key})`) .limit(1); return result?.bcuId; } async createB2cUser(data: { name: string; phone: string; birth: string; }) { const [result] = await this.drizzle .insert(tnB2cUser) .values({ bcuName: sql`FN_ENCRYPT(${data.name}, ${this.key})`, bcuPhone: sql`FN_ENCRYPT(${data.phone}, ${this.key})`, bcuBirth: sql`FN_ENCRYPT(${data.birth}, ${this.key})`, }) .$returningId(); return result.bcuId; } async getB2cUserByTransactionId(transactionId: string) { const [result] = await this.drizzle .select({ bodId: tnB2cOrderDetail.bodId, bcuId: tnB2cOrderDetail.bcuId, bodTransactionId: tnB2cOrderDetail.bodTransactionId, bodState: tnB2cOrderDetail.bodState, }) .from(tnB2cOrderDetail) .where(eq(tnB2cOrderDetail.bodTransactionId, transactionId)) .orderBy(desc(tnB2cOrderDetail.bodCreatedAt)) .limit(1); return result; } async getB2cUserById(bcuId: number): Promise<{ bcuId: number; bcuName: string; bcuPhone: string; bcuBirth: string } | undefined> { const [user] = await this.drizzle .select({ bcuId: tnB2cUser.bcuId, bcuName: sql`FN_DECRYPT(${tnB2cUser.bcuName}, ${this.key})`, bcuPhone: sql`FN_DECRYPT(${tnB2cUser.bcuPhone}, ${this.key})`, bcuBirth: sql`FN_DECRYPT(${tnB2cUser.bcuBirth}, ${this.key})`, }) .from(tnB2cUser) .where(eq(tnB2cUser.bcuId, bcuId)); if (!user) { return undefined; } return { bcuId: user.bcuId, bcuName: user.bcuName || '', bcuPhone: user.bcuPhone || '', bcuBirth: user.bcuBirth || '', }; } async findB2cUserByInfo(phone: string, name: string, birth: string) { const [user] = await this.drizzle .select({ bcuId: tnB2cUser.bcuId, bcuName: sql`FN_DECRYPT(${tnB2cUser.bcuName}, ${this.key})`, bcuPhone: sql`FN_DECRYPT(${tnB2cUser.bcuPhone}, ${this.key})`, bcuBirth: sql`FN_DECRYPT(${tnB2cUser.bcuBirth}, ${this.key})`, }) .from(tnB2cUser) .where( and( sql`FN_DECRYPT(${tnB2cUser.bcuPhone}, ${this.key}) = ${phone}`, sql`FN_DECRYPT(${tnB2cUser.bcuName}, ${this.key}) = ${name}`, sql`FN_DECRYPT(${tnB2cUser.bcuBirth}, ${this.key}) = ${birth}`, ) ); return user; } // B2C 생년월일 검증 async verifyB2cBirthDate(bodId: number, birthDate: string): Promise { const orderDetail = await this.checkB2cOrderDetailByBodId(bodId); if (!orderDetail) { return false; } const user = await this.getB2cUserById(orderDetail.bcuId!); if (!user) { return false; } // 생년월일 비교 (암호화된 데이터를 복호화한 값과 비교) return user.bcuBirth === birthDate; } async getB2cAggregateList(bodId: number, dto: GetAnalysisAggregateListDto) { const { page, ansType, asbSicked, asbDiseaseCode } = dto; const limit = 10; const offset = (+page - 1) * limit; //* 숨은보험진단(ANS006), 보상찾아줘-기타(ANS009)의 경우만 약국도 포함 const sq1Where = ansType === 'ANS006' || ansType === 'ANS009' ? and(eq(tnB2cAnalysisBasic.bodId, bodId)) : and( eq(tnB2cAnalysisBasic.bodId, bodId), notLike(tnB2cAnalysisBasic.basbHospitalName, '%약국%'), ); //* ANS009(보상찾아줘-기타)일 경우 basdInEtc별로 행을 분리해야 하므로 GROUP BY에 포함 const sq1 = this.drizzle .select({ basbId: tnB2cAnalysisBasic.basbId, basbTreatStartDate: tnB2cAnalysisBasic.basbTreatStartDate, basbHospitalName: tnB2cAnalysisBasic.basbHospitalName, basbDiseaseCode: tnB2cAnalysisBasic.basbDiseaseCode, basbDiseaseName: sql`REPLACE(REPLACE(${tnB2cAnalysisBasic.basbDiseaseName}, '(양방)', ''), '(한방)', '') `.as( 'basbDiseaseName', ), basbTreatType: tnB2cAnalysisBasic.basbTreatType, basbVisitDays: tnB2cAnalysisBasic.basbVisitDays, basbDosingDays: tnB2cAnalysisBasic.basbDosingDays, basbDisease: tnB2cAnalysisBasic.basbDisease, basbInDisease: tnB2cAnalysisBasic.basbInDisease, basbNotice: tnB2cAnalysisBasic.basbNotice, basbSicked: tnB2cAnalysisBasic.basbSicked, basbDuplicated: tnB2cAnalysisBasic.basbDuplicated, basdOperation: tnB2cAnalysisDetail.basdOperation, basdInEtc: ansType === 'ANS009' ? tnB2cAnalysisDetail.basdInEtc : max(tnB2cAnalysisDetail.basdInEtc).as('basdInEtc'), }) .from(tnB2cAnalysisBasic) .where(sq1Where) .leftJoin( tnB2cAnalysisDetail, eq(tnB2cAnalysisDetail.basbId, tnB2cAnalysisBasic.basbId), ) .groupBy( tnB2cAnalysisBasic.basbId, tnB2cAnalysisBasic.basbTreatStartDate, tnB2cAnalysisBasic.basbHospitalName, ...(ansType === 'ANS009' ? [tnB2cAnalysisDetail.basdInEtc] : []), ) .as('sq1'); const sq2 = this.drizzle .select({ asbTreatStartDate: min(sq1.basbTreatStartDate).as('asbTreatStartDate'), asbTreatEndDate: max(sq1.basbTreatStartDate).as('asbTreatEndDate'), asbHospitalName: sql`GROUP_CONCAT(DISTINCT ${sq1.basbHospitalName} SEPARATOR ' | ')`.as('asbHospitalName'), asbDiseaseCode: sq1.basbDiseaseCode, asbDiseaseName: sq1.basbDiseaseName, asbInDisease: max(sq1.basbInDisease).as('asbInDisease'), asbNotice: max(sq1.basbNotice).as('asbNotice'), inDiseaseCount: count(sq1.basbInDisease).as('asbInDiseaseCount'), noticeCount: count(sq1.basbNotice).as('asbNoticeCount'), asbInEtc: max(sq1.basdInEtc).as('asbInEtc'), inEtcCount: count(sq1.basdInEtc).as('asbInEtcCount'), visitDaysCount: count(sq1.basbVisitDays).as('visitDaysCount'), asbTreatType: max(sq1.basbTreatType).as('asbTreatType'), asbVisitDays: sql`sum(${sq1.basbVisitDays})`.as('asbVisitDays'), asbDosingDays: sql`sum(${sq1.basbDosingDays})`.as('asbDosingDays'), asdOperation: max(sq1.basdOperation).as('asdOperation'), asbDisease: max(sq1.basbDisease).as('asbDisease'), asbSicked: max(sq1.basbSicked).as('asbSicked'), asbDuplicated: max(sq1.basbDuplicated).as('asbDuplicated'), }) .from(sq1) .where(ansType === 'ANS003' ? eq(sq1.basbTreatType, '외래') : undefined) .groupBy( sq1.basbDiseaseCode, sq1.basbDiseaseName, ...(ansType === 'ANS009' ? [sq1.basdInEtc] : []), ) .as('sq2'); const baseWhere = ansType === 'ANS003' ? or(gte(sq2.asbVisitDays, 7), gte(sq2.asbDosingDays, 30)) : ansType === 'ANS006' ? isNotNull(sq2.asbInDisease) : ansType === 'ANS009' ? isNotNull(sq2.asbInEtc) : ansType === 'ANS013' ? isNotNull(sq2.asbNotice) : ansType === 'ANS005' && asbSicked === 1 ? and(isNotNull(sq2.asbDisease), eq(sq2.asbSicked, 1)) : isNotNull(sq2.asbDisease); // 질병코드 필터링 추가 const where = asbDiseaseCode ? and(baseWhere, eq(sq2.asbDiseaseCode, asbDiseaseCode)) : baseWhere; let list: any = []; if (page === 0) { list = await this.drizzle .select({ basic: { asbTreatStartDate: sq2.asbTreatStartDate, asbTreatEndDate: sq2.asbTreatEndDate, asbHospitalName: sq2.asbHospitalName, asbDiseaseCode: sq2.asbDiseaseCode, asbDiseaseName: sq2.asbDiseaseName, asbInDisease: sq2.asbInDisease, asbNotice: sq2.asbNotice, asbVisitDays: sql`${sq2.asbVisitDays}`.mapWith(Number), asbDosingDays: sql`${sq2.asbDosingDays}`.mapWith(Number), asbDuplicated: sql`${sq2.asbDuplicated}`.mapWith(Number), inDiseaseCount: sql`${sq2.inDiseaseCount}`.mapWith(Number), noticeCount: sql`${sq2.noticeCount}`.mapWith(Number), asbInEtc: sq2.asbInEtc, inEtcCount: sql`${sq2.inEtcCount}`.mapWith(Number), visitDaysCount: sql`${sq2.visitDaysCount}`.mapWith(Number), }, detail: { asdOperation: sq2.asdOperation, }, }) .from(sq2) .where(where) .orderBy(asc(sq2.asbTreatStartDate)); } else { list = await this.drizzle .select({ basic: { asbTreatStartDate: sq2.asbTreatStartDate, asbTreatEndDate: sq2.asbTreatEndDate, asbHospitalName: sq2.asbHospitalName, asbDiseaseCode: sq2.asbDiseaseCode, asbDiseaseName: sq2.asbDiseaseName, asbInDisease: sq2.asbInDisease, asbNotice: sq2.asbNotice, asbVisitDays: sql`${sq2.asbVisitDays}`.mapWith(Number), asbDosingDays: sql`${sq2.asbDosingDays}`.mapWith(Number), asbDuplicated: sql`${sq2.asbDuplicated}`.mapWith(Number), inDiseaseCount: sql`${sq2.inDiseaseCount}`.mapWith(Number), noticeCount: sql`${sq2.noticeCount}`.mapWith(Number), asbInEtc: sq2.asbInEtc, inEtcCount: sql`${sq2.inEtcCount}`.mapWith(Number), visitDaysCount: sql`${sq2.visitDaysCount}`.mapWith(Number), }, detail: { asdOperation: sq2.asdOperation, }, }) .from(sq2) .where(where) .orderBy(asc(sq2.asbTreatStartDate)) .limit(limit) .offset(offset); } const [totalCount] = await this.drizzle .select({ count: count() }) .from(sq2) .where(where); return { list, count: totalCount?.count ?? 0 }; } async getB2cPrescribeList(bodId: number, dto: GetAnalysisPrescribeListDto) { return await this.drizzle .select({ aspTreatStartDate: tnB2cAnalysisPrescribe.baspTreatStartDate, aspHospitalName: tnB2cAnalysisPrescribe.baspHospitalName, aspTreatType: tnB2cAnalysisPrescribe.baspTreatType, aspDrugName: tnB2cAnalysisPrescribe.baspDrugName, aspIngredients: tnB2cAnalysisPrescribe.baspIngredients, aspOneDose: tnB2cAnalysisPrescribe.baspOneDose, aspDailyDosesNumber: tnB2cAnalysisPrescribe.baspDailyDosesNumber, aspTotalDosingDays: tnB2cAnalysisPrescribe.baspTotalDosingDays, // 약품명 ↔ 투약기준(병명예측관리 투약로그) 대조로 효능효과 조회 // mscCode 중복 등록 시 행 증식을 막기 위해 상관 서브쿼리로 단일 값만 반환 aspEfficacy: sql`( SELECT ${tcMedicationStandard.mscName} FROM ${tcMedicationStandard} WHERE REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tnB2cAnalysisPrescribe.baspDrugName}, ' ', '') COLLATE utf8mb4_general_ci LIMIT 1 )`, }) .from(tnB2cAnalysisPrescribe) .where( and( eq(tnB2cAnalysisPrescribe.bodId, bodId), eq(tnB2cAnalysisPrescribe.baspTreatStartDate, dto.searchDate), ), ) .orderBy(asc(tnB2cAnalysisPrescribe.baspId)); } async getB2cPrescribeBasicList(bodId: number, dto: GetAnalysisPrescribeListDto) { return await this.drizzle .select({ basic: { asbSicked: tnB2cAnalysisBasic.basbSicked, oddId: sql`${tnB2cAnalysisBasic.bodId}`.as('oddId'), asbId: sql`${tnB2cAnalysisBasic.basbId}`.as('asbId'), asbTreatStartDate: tnB2cAnalysisBasic.basbTreatStartDate, asbHospitalName: tnB2cAnalysisBasic.basbHospitalName, asbDepartment: tnB2cAnalysisBasic.basbDepartment, asbTreatType: tnB2cAnalysisBasic.basbTreatType, asbDiseaseCode: tnB2cAnalysisBasic.basbDiseaseCode, asbDiseaseName: tnB2cAnalysisBasic.basbDiseaseName, asbVisitDays: tnB2cAnalysisBasic.basbVisitDays, asbDisease: tnB2cAnalysisBasic.basbDisease, asbInDisease: tnB2cAnalysisBasic.basbInDisease, asbNotice: tnB2cAnalysisBasic.basbNotice, asbDentures: tnB2cAnalysisBasic.basbDentures, asbToothOne: tnB2cAnalysisBasic.basbToothOne, asbToothFive: tnB2cAnalysisBasic.basbToothFive, asbEtc: tnB2cAnalysisBasic.basbEtc, asbDrugNotice: tnB2cAnalysisBasic.basbDrugNotice, asbDosingDays: tnB2cAnalysisBasic.basbDosingDays, asbDuplicated: tnB2cAnalysisBasic.basbDuplicated, }, detail: { asdOperation: tnB2cAnalysisDetail.basdOperation, asdExamination: tnB2cAnalysisDetail.basdExamination, asdDosingDays: tnB2cAnalysisDetail.basdDosingDays, asdInTooth: tnB2cAnalysisDetail.basdInTooth, asdInOperation: tnB2cAnalysisDetail.basdInOperation, asdInEtc: tnB2cAnalysisDetail.basdInEtc, asdDentures: tnB2cAnalysisDetail.basdDentures, asdToothOne: tnB2cAnalysisDetail.basdToothOne, asdToothFive: tnB2cAnalysisDetail.basdToothFive, operationCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdOperation} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('operationCount'), examinationCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdExamination} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('examinationCount'), inToothCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdInTooth} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('inToothCount'), inOperationCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdInOperation} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('inOperationCount'), inEtcCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdInEtc} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('inEtcCount'), denturesCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdDentures} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('denturesCount'), toothOneCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdToothOne} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('toothOneCount'), toothFiveCount: sql`COUNT(DISTINCT CASE WHEN ${tnB2cAnalysisDetail.basdToothFive} IS NOT NULL THEN ${tnB2cAnalysisDetail.basdId} END)`.as('toothFiveCount'), }, }) .from(tnB2cAnalysisBasic) .leftJoin( tnB2cAnalysisDetail, eq(tnB2cAnalysisBasic.basbId, tnB2cAnalysisDetail.basbId), ) .where( and( eq(tnB2cAnalysisBasic.bodId, bodId), eq(tnB2cAnalysisBasic.basbTreatStartDate, dto.searchDate), ), ) .groupBy(tnB2cAnalysisBasic.basbId) .orderBy(asc(tnB2cAnalysisBasic.basbId)); } async getB2cCarBasicById(bacbId: number) { const [result] = await this.drizzle .select({ bacbId: tnB2cAnalysisCarBasic.bacbId, bodId: tnB2cAnalysisCarBasic.bodId, bacbTreatStartDate: tnB2cAnalysisCarBasic.bacbTreatStartDate, bacbTreatEndDate: tnB2cAnalysisCarBasic.bacbTreatEndDate, }) .from(tnB2cAnalysisCarBasic) .where(eq(tnB2cAnalysisCarBasic.bacbId, bacbId)); return result; } async getB2cCarBasicList(bodId: number, page: number) { const limit = 10; const offset = (+page - 1) * limit; const query = this.drizzle .select({ acbId: sql`${tnB2cAnalysisCarBasic.bacbId}`.as('acbId'), oddId: sql`${tnB2cAnalysisCarBasic.bodId}`.as('oddId'), acbTreatStartDate: tnB2cAnalysisCarBasic.bacbTreatStartDate, acbTreatEndDate: tnB2cAnalysisCarBasic.bacbTreatEndDate, acbTotalTreatDate: tnB2cAnalysisCarBasic.bacbTotalTreatDate, acbTotalAmount: tnB2cAnalysisCarBasic.bacbTotalAmount, acbInpatientTotalAmount: tnB2cAnalysisCarBasic.bacbInpatientTotalAmount, acbOutpatientTotalAmount: tnB2cAnalysisCarBasic.bacbOutpatientTotalAmount, }) .from(tnB2cAnalysisCarBasic) .where(eq(tnB2cAnalysisCarBasic.bodId, bodId)); if (page === 0) { return await query; } else { return await query.limit(limit).offset(offset); } } async getB2cCarDetailList(where: SQL | undefined) { return this.drizzle .select({ acdId: sql`${tnB2cAnalysisCarDetail.bacdId}`.as('acdId'), acdTreatStartDate: tnB2cAnalysisCarDetail.bacdTreatStartDate, acdHospitalName: tnB2cAnalysisCarDetail.bacdHospitalName, acdDepartment: tnB2cAnalysisCarDetail.bacdDepartment, acdTreatType: tnB2cAnalysisCarDetail.bacdTreatType, acdDiseaseCode: tnB2cAnalysisCarDetail.bacdDiseaseCode, acdDiseaseName: tnB2cAnalysisCarDetail.bacdDiseaseName, acdTreatDate: tnB2cAnalysisCarDetail.bacdTreatDate, acdTotalAmount: tnB2cAnalysisCarDetail.bacdTotalAmount, acdMedicalFee: tnB2cAnalysisCarDetail.bacdMedicalFee, }) .from(tnB2cAnalysisCarDetail) .where(where); } async getB2cCarDetailCount(where: SQL | undefined) { const [totalCount] = await this.drizzle .select({ count: count() }) .from(tnB2cAnalysisCarDetail) .where(where); return totalCount.count ?? 0; } //------------------------- B2C detail -------------------------// async getB2cBasicSingle(where: SQL | undefined) { const list = await this.drizzle .select({ basic: { bodId: tnB2cAnalysisBasic.bodId, asbId: sql`${tnB2cAnalysisBasic.basbId}`.as('asbId'), asbTreatStartDate: tnB2cAnalysisBasic.basbTreatStartDate, asbHospitalName: tnB2cAnalysisBasic.basbHospitalName, asbDepartment: tnB2cAnalysisBasic.basbDepartment, asbTreatType: tnB2cAnalysisBasic.basbTreatType, asbDiseaseCode: tnB2cAnalysisBasic.basbDiseaseCode, asbDiseaseName: tnB2cAnalysisBasic.basbDiseaseName, asbVisitDays: tnB2cAnalysisBasic.basbVisitDays, asbDisease: tnB2cAnalysisBasic.basbDisease, asbInDisease: tnB2cAnalysisBasic.basbInDisease, asbNotice: tnB2cAnalysisBasic.basbNotice, asbDentures: tnB2cAnalysisBasic.basbDentures, asbToothOne: tnB2cAnalysisBasic.basbToothOne, asbToothFive: tnB2cAnalysisBasic.basbToothFive, asbEtc: tnB2cAnalysisBasic.basbEtc, asbSicked: tnB2cAnalysisBasic.basbSicked, asbDrugNotice: tnB2cAnalysisBasic.basbDrugNotice, asbDosingDays: tnB2cAnalysisBasic.basbDosingDays, asbDuplicated: tnB2cAnalysisBasic.basbDuplicated, asbInTooth: tnB2cAnalysisBasic.basbInTooth, }, }) .from(tnB2cAnalysisBasic) .where(where) .limit(1); return list; } async getB2cDetailList(where: SQL | undefined) { return await this.drizzle .select({ asdId: sql`${tnB2cAnalysisDetail.basdId}`.as('asdId'), asdCodeName: tnB2cAnalysisDetail.basdCodeName, asdOperation: tnB2cAnalysisDetail.basdOperation, asdExamination: tnB2cAnalysisDetail.basdExamination, asdInTooth: tnB2cAnalysisDetail.basdInTooth, asdInOperation: tnB2cAnalysisDetail.basdInOperation, asdInEtc: tnB2cAnalysisDetail.basdInEtc, asdDentures: tnB2cAnalysisDetail.basdDentures, asdToothOne: tnB2cAnalysisDetail.basdToothOne, asdToothFive: tnB2cAnalysisDetail.basdToothFive, asdDosingDays: tnB2cAnalysisDetail.basdDosingDays, }) .from(tnB2cAnalysisDetail) .where(where) .orderBy( asc(tnB2cAnalysisDetail.basdExamination), asc(tnB2cAnalysisDetail.basdOperation), asc(tnB2cAnalysisDetail.basdInTooth), asc(tnB2cAnalysisDetail.basdInOperation), asc(tnB2cAnalysisDetail.basdDentures), asc(tnB2cAnalysisDetail.basdToothOne), asc(tnB2cAnalysisDetail.basdToothFive), asc(tnB2cAnalysisDetail.basdId), ); } /** * B2C 상담신청 상태 업데이트 */ async updateB2cConsultRequest(bodId: number) { const result = await this.drizzle .update(tnB2cOrderDetail) .set({ bodConsultRequested: 1 }) .where(eq(tnB2cOrderDetail.bodId, bodId)); return result; } //------------------------- B2C Order Detail Admin -------------------------// async getB2cOrderDetailList( where: SQL | undefined, orderBy: SQL, page: number, isDownload: boolean, ) { const limit = 10; const offset = (page - 1) * limit; if (isDownload) { return await this.buildB2cOrderDetailQuery(where, orderBy); } else { return await this.buildB2cOrderDetailQuery(where, orderBy) .limit(limit) .offset(offset); } } buildB2cOrderDetailQuery(where: SQL | undefined, orderBy: SQL) { return this.drizzle .select({ user: { bcuId: tnB2cUser.bcuId, bcuName: sql`FN_DECRYPT(${tnB2cUser.bcuName}, ${this.key})`, bcuPhone: sql`FN_DECRYPT(${tnB2cUser.bcuPhone}, ${this.key})`, bcuBirth: sql`FN_DECRYPT(${tnB2cUser.bcuBirth}, ${this.key})`, }, agency: { agcId: tnAgency.agcId, agcCode: tnB2cOrderDetail.bodAgcCode, agcName: tnAgency.agcCorporateName, }, orderDetail: { bodId: tnB2cOrderDetail.bodId, bodTransactionId: tnB2cOrderDetail.bodTransactionId, bodState: tnB2cOrderDetail.bodState, bodMarketingAgree: tnB2cOrderDetail.bodMarketingAgree, bodConsultRequested: tnB2cOrderDetail.bodConsultRequested, bodExposed: tnB2cOrderDetail.bodExposed, bodAssignedStatus: tnB2cOrderDetail.bodAssignedStatus, bodAssignedMemberId: tnB2cOrderDetail.bodAssignedMemberId, bodAssignedDate: tnB2cOrderDetail.bodAssignedDate, bodCompletedAt: tnB2cOrderDetail.bodCompletedAt, bodFailedAt: tnB2cOrderDetail.bodFailedAt, bodCreatedAt: tnB2cOrderDetail.bodCreatedAt, bodUpdatedAt: tnB2cOrderDetail.bodUpdatedAt, }, }) .from(tnB2cOrderDetail) .leftJoin(tnB2cUser, eq(tnB2cUser.bcuId, tnB2cOrderDetail.bcuId)) .leftJoin(tnAgency, eq(tnAgency.agcCorporateCode, tnB2cOrderDetail.bodAgcCode)) .where(where) .orderBy(orderBy) .$dynamic(); } async countB2cOrderDetail(where: SQL | undefined) { const [totalCount] = await this.drizzle .select({ count: count() }) .from(tnB2cOrderDetail) .leftJoin(tnB2cUser, eq(tnB2cUser.bcuId, tnB2cOrderDetail.bcuId)) .where(where); return totalCount.count; } async whereClauseB2cOrderDetail(dto: any) { const { searchKey, searchText, bodState, dateType, startDate, endDate, agcCode, bodExposed, bodMarketingAgree, bodConsultRequested, bodAssigned, } = dto; const where: SQL[] = []; if (!!startDate && !!endDate) { const dateWhere = this.makeDateWhereB2c(dateType, startDate, endDate); where.push(...dateWhere); } if (!!searchText) { const searchKeyWhere = this.makeSearchKeyWhereB2c(searchKey, searchText); where.push(searchKeyWhere); } if (!!bodState) { const stateWhere = eq(tnB2cOrderDetail.bodState, bodState); where.push(stateWhere); } if (!!agcCode) { where.push(eq(tnB2cOrderDetail.bodAgcCode, agcCode)); } if (bodExposed !== undefined && bodExposed !== null) { where.push(eq(tnB2cOrderDetail.bodExposed, bodExposed)); } if (bodMarketingAgree !== undefined && bodMarketingAgree !== null) { where.push(eq(tnB2cOrderDetail.bodMarketingAgree, bodMarketingAgree)); } if (bodConsultRequested !== undefined && bodConsultRequested !== null) { where.push(eq(tnB2cOrderDetail.bodConsultRequested, bodConsultRequested)); } // bodAssigned는 아직 컬럼이 없으므로 TODO // if (bodAssigned !== undefined && bodAssigned !== null) { // where.push(eq(tnB2cOrderDetail.bodAssigned, bodAssigned)); // } return and(...where); } async orderByClauseB2cOrderDetail(dto: any) { const orderField = this.makeOrderFieldB2c(dto.sortKey); switch (dto.sortValue) { case 'desc': { return desc(orderField); } default: { return asc(orderField); } } } makeDateWhereB2c(dateType: string, startDate: string, endDate: string): SQL[] { const where: SQL[] = []; switch (dateType) { case 'bodCreatedAt': { where.push(gte(tnB2cOrderDetail.bodCreatedAt, startDate)); where.push(lte(tnB2cOrderDetail.bodCreatedAt, endDate)); return where; } default: { where.push(gte(tnB2cOrderDetail.bodCreatedAt, startDate)); where.push(lte(tnB2cOrderDetail.bodCreatedAt, endDate)); return where; } } } makeSearchKeyWhereB2c(searchKey: string, searchText: string) { switch (searchKey) { case 'bodTransactionId': { return sql`${tnB2cOrderDetail.bodTransactionId} LIKE ${`%${searchText}%`}`; } case 'bcuName': { return sql`CONVERT(AES_DECRYPT(FROM_BASE64(${tnB2cUser.bcuName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}`; } case 'bcuPhone': { return sql`CONVERT(AES_DECRYPT(FROM_BASE64(${tnB2cUser.bcuPhone}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}`; } case 'assignedMemberId': { // TODO: 담당회원아이디 검색 (컬럼 추가 후) return sql`1=1`; } default: { return sql`${tnB2cOrderDetail.bodTransactionId} LIKE ${`%${searchText}%`}`; } } } makeOrderFieldB2c(sortKey: string) { switch (sortKey) { case 'bodCreatedAt': { return tnB2cOrderDetail.bodCreatedAt; } case 'bodUpdatedAt': { return tnB2cOrderDetail.bodUpdatedAt; } default: { return tnB2cOrderDetail.bodCreatedAt; } } } async getB2cOriginalTreatList(bodId: number) { const basic = await this.drizzle .select() .from(ttB2cTreatBasic) .where(eq(ttB2cTreatBasic.bodId, bodId)); const detail = await this.drizzle .select() .from(ttB2cTreatDetail) .where(eq(ttB2cTreatDetail.bodId, bodId)); const prescibe = await this.drizzle .select() .from(ttB2cTreatPrescribe) .where(eq(ttB2cTreatPrescribe.bodId, bodId)); const carBasic = await this.drizzle .select() .from(ttB2cTreatCarBasic) .where(eq(ttB2cTreatCarBasic.bodId, bodId)); const carDetail = await this.drizzle .select() .from(ttB2cTreatCarDetail) .where(eq(ttB2cTreatCarDetail.bodId, bodId)); return { basic, detail, prescibe, carBasic, carDetail }; } async getB2cOrderDetailById(bodId: number) { const [result] = await this.drizzle .select({ user: { bcuId: tnB2cUser.bcuId, bcuName: sql`FN_DECRYPT(${tnB2cUser.bcuName}, ${this.key})`, bcuPhone: sql`FN_DECRYPT(${tnB2cUser.bcuPhone}, ${this.key})`, bcuBirth: sql`FN_DECRYPT(${tnB2cUser.bcuBirth}, ${this.key})`, }, agency: { agcId: tnAgency.agcId, agcCode: tnB2cOrderDetail.bodAgcCode, agcName: tnAgency.agcCorporateName, }, orderDetail: { bodId: tnB2cOrderDetail.bodId, bodTransactionId: tnB2cOrderDetail.bodTransactionId, bodState: tnB2cOrderDetail.bodState, bodMarketingAgree: tnB2cOrderDetail.bodMarketingAgree, bodConsultRequested: tnB2cOrderDetail.bodConsultRequested, bodExposed: tnB2cOrderDetail.bodExposed, bodAssignedStatus: tnB2cOrderDetail.bodAssignedStatus, bodAssignedMemberId: tnB2cOrderDetail.bodAssignedMemberId, bodAssignedDate: tnB2cOrderDetail.bodAssignedDate, bodCompletedAt: tnB2cOrderDetail.bodCompletedAt, bodFailedAt: tnB2cOrderDetail.bodFailedAt, bodCreatedAt: tnB2cOrderDetail.bodCreatedAt, bodUpdatedAt: tnB2cOrderDetail.bodUpdatedAt, }, }) .from(tnB2cOrderDetail) .leftJoin(tnB2cUser, eq(tnB2cUser.bcuId, tnB2cOrderDetail.bcuId)) .leftJoin(tnAgency, eq(tnAgency.agcCorporateCode, tnB2cOrderDetail.bodAgcCode)) .where(eq(tnB2cOrderDetail.bodId, bodId)); return result; } //* 정회원 — 심평원 '내가 먹는약 한눈에' 신규 데이터 중 기존 기본진료정보(ttTreatBasic)에 없는 건만 반환 //* ANS001([baseDate-3개월, baseDate]) 영역에서 사용 async getMedicineSummaryNewItems( oddId: number, startDate: string, endDate: string, ) { return await this.drizzle .select({ tmsDispenseDate: ttTreatMedicineSummary.tmsDispenseDate, tmsPrescribingHospital: ttTreatMedicineSummary.tmsPrescribingHospital, tmsDispensingPharmacy: ttTreatMedicineSummary.tmsDispensingPharmacy, tmsPrescribingHospitalCode: ttTreatMedicineSummary.tmsPrescribingHospitalCode, tmsDispensingPharmacyCode: ttTreatMedicineSummary.tmsDispensingPharmacyCode, tmsDrugName: ttTreatMedicineSummary.tmsDrugName, tmsTotalDosingDays: ttTreatMedicineSummary.tmsTotalDosingDays, }) .from(ttTreatMedicineSummary) .where( and( eq(ttTreatMedicineSummary.oddId, oddId), gte(ttTreatMedicineSummary.tmsDispenseDate, startDate), lte(ttTreatMedicineSummary.tmsDispenseDate, endDate), //* 기존 기본진료정보(ttTreatBasic)에 동일 진료일+병원명 조합이 없는 건만 선택 //* 병원/약국명 양쪽이 모두 NULL인 행은 SQL 3-value logic상 매칭이 항상 UNKNOWN이 되므로, //* basic 측이 NULL이고 medicine 측도 NULL이면 동일 진료일 기준으로 매칭된 것으로 간주 sql`NOT EXISTS ( SELECT 1 FROM tt_treat_basic b WHERE b.odd_id = ${ttTreatMedicineSummary.oddId} AND b.trb_treat_start_date = ${ttTreatMedicineSummary.tmsDispenseDate} AND ( b.trb_hospital_name = ${ttTreatMedicineSummary.tmsPrescribingHospital} OR b.trb_hospital_name = ${ttTreatMedicineSummary.tmsDispensingPharmacy} OR ( b.trb_hospital_name IS NULL AND ${ttTreatMedicineSummary.tmsPrescribingHospital} IS NULL AND ${ttTreatMedicineSummary.tmsDispensingPharmacy} IS NULL ) ) )`, ), ) .orderBy(desc(ttTreatMedicineSummary.tmsDispenseDate)); } //* B2C — 심평원 '내가 먹는약 한눈에' 신규 데이터 중 기존 기본진료정보에 없는 건만 반환 async getB2cMedicineSummaryNewItems( bodId: number, startDate: string, endDate: string, ) { return await this.drizzle .select({ tmsDispenseDate: ttB2cTreatMedicineSummary.btmsDispenseDate, tmsPrescribingHospital: ttB2cTreatMedicineSummary.btmsPrescribingHospital, tmsDispensingPharmacy: ttB2cTreatMedicineSummary.btmsDispensingPharmacy, tmsPrescribingHospitalCode: ttB2cTreatMedicineSummary.btmsPrescribingHospitalCode, tmsDispensingPharmacyCode: ttB2cTreatMedicineSummary.btmsDispensingPharmacyCode, tmsDrugName: ttB2cTreatMedicineSummary.btmsDrugName, tmsTotalDosingDays: ttB2cTreatMedicineSummary.btmsTotalDosingDays, }) .from(ttB2cTreatMedicineSummary) .where( and( eq(ttB2cTreatMedicineSummary.bodId, bodId), gte(ttB2cTreatMedicineSummary.btmsDispenseDate, startDate), lte(ttB2cTreatMedicineSummary.btmsDispenseDate, endDate), //* 병원/약국명 양쪽이 모두 NULL이면 동일 진료일 기준으로 매칭된 것으로 간주 (NULL-safe) sql`NOT EXISTS ( SELECT 1 FROM tt_b2c_treat_basic b WHERE b.bod_id = ${ttB2cTreatMedicineSummary.bodId} AND b.btrb_treat_start_date = ${ttB2cTreatMedicineSummary.btmsDispenseDate} AND ( b.btrb_hospital_name = ${ttB2cTreatMedicineSummary.btmsPrescribingHospital} OR b.btrb_hospital_name = ${ttB2cTreatMedicineSummary.btmsDispensingPharmacy} OR ( b.btrb_hospital_name IS NULL AND ${ttB2cTreatMedicineSummary.btmsPrescribingHospital} IS NULL AND ${ttB2cTreatMedicineSummary.btmsDispensingPharmacy} IS NULL ) ) )`, ), ) .orderBy(desc(ttB2cTreatMedicineSummary.btmsDispenseDate)); } // ============================================================================ // 기업관리자(corp) 접근제어 — 분석 상세(oddId/asbId/acbId)의 소속 기업/조직 검증 // ============================================================================ //* oddId 가 corp 스코프(자기 기업 agcId + ORG는 org_path 부분트리) 안에 있는지 여부. //* join 경로: order_detail → order → payment → subscribe → member → org async isOddInCorpScope( oddId: number, agcId: number, orgPath?: string, ): Promise { const conditions: SQL[] = [ eq(tnOrderDetail.oddId, oddId), eq(tnMember.agcId, agcId), ]; if (orgPath) { conditions.push( sql`EXISTS (SELECT 1 FROM tn_agency_organization o WHERE o.org_id = ${tnMember.orgId} AND o.org_path LIKE ${orgPath + '%'})`, ); } const [row] = await this.drizzle .select({ oddId: tnOrderDetail.oddId }) .from(tnOrderDetail) .leftJoin(tnOrder, eq(tnOrder.ordId, tnOrderDetail.ordId)) .leftJoin(tnPayment, eq(tnPayment.pmtId, tnOrder.pmtId)) .leftJoin(tnSubscribe, eq(tnSubscribe.subId, tnPayment.subId)) .leftJoin(tnMember, eq(tnMember.memId, tnSubscribe.memId)) .where(and(...conditions)) .limit(1); return !!row; } //* asbId(분석기본) → oddId 해석 async getOddIdByAsbId(asbId: number): Promise { const [row] = await this.drizzle .select({ oddId: tnAnalysisBasic.oddId }) .from(tnAnalysisBasic) .where(eq(tnAnalysisBasic.asbId, asbId)) .limit(1); return row?.oddId; } //* acbId(자동차보험 분석기본) → oddId 해석 async getOddIdByAcbId(acbId: number): Promise { const [row] = await this.drizzle .select({ oddId: tnAnalysisCarBasic.oddId }) .from(tnAnalysisCarBasic) .where(eq(tnAnalysisCarBasic.acbId, acbId)) .limit(1); return row?.oddId; } //* basbId(B2C 분석기본) → bodId 해석 async getBodIdByBasbId(basbId: number): Promise { const [row] = await this.drizzle .select({ bodId: tnB2cAnalysisBasic.bodId }) .from(tnB2cAnalysisBasic) .where(eq(tnB2cAnalysisBasic.basbId, basbId)) .limit(1); return row?.bodId; } //* bcbId(B2C 자동차보험 분석기본; 실제 테이블 키 bacbId) → bodId 해석 async getBodIdByBcbId(bcbId: number): Promise { const [row] = await this.drizzle .select({ bodId: tnB2cAnalysisCarBasic.bodId }) .from(tnB2cAnalysisCarBasic) .where(eq(tnB2cAnalysisCarBasic.bacbId, bcbId)) .limit(1); return row?.bodId; } //* casbId(고객사 분석기본) → codId 해석 async getCodIdByCasbId(casbId: number): Promise { const [row] = await this.drizzle .select({ codId: tnClientAnalysisBasic.codId }) .from(tnClientAnalysisBasic) .where(eq(tnClientAnalysisBasic.casbId, casbId)) .limit(1); return row?.codId; } //* cacbId(고객사 자동차보험 분석기본) → codId 해석 async getCodIdByCacbId(cacbId: number): Promise { const [row] = await this.drizzle .select({ codId: tnClientAnalysisCarBasic.codId }) .from(tnClientAnalysisCarBasic) .where(eq(tnClientAnalysisCarBasic.cacbId, cacbId)) .limit(1); return row?.codId; } }