import { DrizzleService } from '@lib/drizzle/src/drizzle.service'; import { BadRequestException, Injectable } from '@nestjs/common'; import { ConfigService } from '@nestjs/config'; import { SQL, and, asc, count, desc, eq, gt, gte, isNotNull, isNull, like, lte, or, sql, sum, } from 'drizzle-orm'; import { thMoneyCorp, thMoneyPaymentCancel, thMoneyUsage, thMoneyWallet, tnAdmin, tnAgency, tnAgencyOrganization, tnMember, tnMoneyChargeRequest, tnMoneyGrant, tnMoneyGrantTarget, tnMoneyIssue, tnMoneyIssueTarget, tnMoneyPayment, tnMoneyRefund, tnMoneySource, tnSalesAgency, tnSalesAgencyChannel, } from 'drizzle/schema'; import { MAX_MONEY_BALANCE_AMOUNT, MoneyPaymentStatus, MoneyRefundStatus, } from './money.dto'; type MoneyDb = any; /** * 연결된 결제가 취소 예약 중이 아닌 원천. 예약 중에는 PG 가 이미 환불했을 수 있어 사용 복구 · 환불 반려로 잔여가 생겨도 쓰지 못한다. * 바깥 FOR UPDATE 는 이 안쪽 결제 행을 잠그지 않고 일반 읽기로 본다. 호출자가 회원 잠금 뒤에 부르므로 예약 커밋 뒤의 값을 읽는다. */ const NOT_RESERVED_FOR_CANCEL = sql`NOT EXISTS (SELECT 1 FROM ${tnMoneyPayment} WHERE ${tnMoneyPayment.mpyId} = ${tnMoneySource.mpyId} AND ${tnMoneyPayment.mpyStatus} = ${MoneyPaymentStatus.CANCELLING})`; /** * 목록·상세의 **처리 계정 칸**(M9 D4). 조립은 이 함수 하나이고 자격 판정은 그 앞의 * `MoneyAdminController.auditViewer`(최고관리자 판정) 가 진다. * * **왜 한 자리인가.** 처리 계정을 채우는 쿼리는 여섯이다. 라우트마다 `@Roles` 를 고치면 신규 * 라우트가 같은 실수를 반복하고, 계수(조립 수 ≤ 판정 수)로 세면 쓰이지 않는 판정 함수 하나와 * 호출 여섯 개로도 수가 맞는다(계획 게이트 R1 Codex). 그래서 게이트 `#4` 는 **구조를 강제한다** — * 이 파일의 `processedBy:` 는 이 함수 호출만 값으로 가질 수 있다. * * 자격이 없으면 세 칸 전부 비운다. `admId` 만 남겨도 계정을 특정하는 식별자이고, `deleted` 는 * '처리자가 있었는데 삭제됐다' 를 말해 계정의 존재 자체를 흘린다. * * **이 덩어리를 비우는 것만으로는 부족하다** — 같은 `select` 의 형제 키가 같은 사실을 다른 이름으로 * 실으면 가림이 그 자리에서 우회된다(로컬 코드리뷰 CRITICAL). 그 축은 `processedByOnly` 가 진다. */ export function processedByCell(admIdCol: unknown, canSee: boolean) { if (!canSee) { return { loginId: sql`NULL`, admId: sql`NULL`, deleted: sql`0`, }; } return { loginId: tnAdmin.admLoginId, admId: admIdCol as never, deleted: sql`CASE WHEN ${admIdCol as never} IS NOT NULL AND ${tnAdmin.admId} IS NULL THEN 1 ELSE 0 END`, }; } /** * 처리 계정을 **다른 이름으로** 싣는 형제 키(로컬 코드리뷰 CRITICAL). 자격이 없으면 `NULL` 이다. * * **왜 별도 함수인가.** `processedByCell` 이 세 칸을 비워도 같은 `select` 가 `approvedAdmLoginId` * (평문 `adm_login_id`) · `approvedAdmName`(`FN_DECRYPT` 한 실명) 을 형제 키로 내리고 있었다 — * 가리려던 값이 옆 칸으로 그대로 나갔고, 게이트 `#4` 는 `processedBy:` 만 보므로 그 자리가 사각이었다. * * 중복 키는 이 함수로 감싸지 않고 **지웠다** — 최상위 `admId` 는 `processedBy.admId` 와 같은 값이고 * 서버·프런트 어디서도 읽지 않는다(실측). 남긴 것은 프런트가 실제로 그리는 `approvedAdmName` * 한 자리뿐이고(`shared/lib/processedBy.ts` 의 `formatProcessedByWithName`), 그래서 그것만 건다. */ export function processedByOnly(col: T, canSee: boolean) { return canSee ? col : (sql`NULL` as never); } @Injectable() export class MoneyRepository { private key: string; constructor( private drizzle: DrizzleService, private configService: ConfigService, ) { this.key = this.configService.get('aes.secret') ?? ''; } // ============================================================================ // 회원머니 적립 (tn_money_issue) // ============================================================================ async getIssueList( where: SQL | undefined, page: number, limit: number = 10, //* 처리 계정 열람 자격(M9 D4a). 기본값은 `false` 다 — 빠뜨린 호출은 노출되는 쪽이 아니라 //* 가려지는 쪽으로 떨어진다. 자격을 기본값으로 주면 그 반대가 된다. canSeeProcessedBy: boolean = false, ) { const offset = (+page - 1) * limit; const query = this.drizzle .select({ mniId: tnMoneyIssue.mniId, agcId: tnMoneyIssue.agcId, mniType: tnMoneyIssue.mniType, mniTargetType: tnMoneyIssue.mniTargetType, mniMemberType: tnMoneyIssue.mniMemberType, mniTitle: tnMoneyIssue.mniTitle, mniReason: tnMoneyIssue.mniReason, mniAmount: tnMoneyIssue.mniAmount, mniMemberCount: tnMoneyIssue.mniMemberCount, mniTotalAmount: tnMoneyIssue.mniTotalAmount, mniHasExpiry: tnMoneyIssue.mniHasExpiry, mniExpiryDays: tnMoneyIssue.mniExpiryDays, mniCreatedAt: tnMoneyIssue.mniCreatedAt, agcCorporateName: tnAgency.agcCorporateName, agcCorporateCode: tnAgency.agcCorporateCode, //* 이 목록은 `tnAdmin` 을 조인하지 않고 있었다 — 화면의 처리자 칸이 늘 비어 있었다는 뜻이다 //* (`MoneyGeneralPage` 가 읽던 `admin.admName` 은 이 select 에 없는 키였다). processedBy: processedByCell(tnMoneyIssue.admId, canSeeProcessedBy), }) .from(tnMoneyIssue) .leftJoin(tnAgency, eq(tnMoneyIssue.agcId, tnAgency.agcId)) .leftJoin(tnAdmin, eq(tnMoneyIssue.admId, tnAdmin.admId)) .where(where) .orderBy(desc(tnMoneyIssue.mniCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getIssueCount(where: SQL | undefined) { const result = await this.drizzle .select({ count: count() }) .from(tnMoneyIssue) .leftJoin(tnAgency, eq(tnMoneyIssue.agcId, tnAgency.agcId)) .where(where); return result[0]?.count || 0; } async getIssueById(mniId: number) { const result = await this.drizzle .select({ mniId: tnMoneyIssue.mniId, agcId: tnMoneyIssue.agcId, mniType: tnMoneyIssue.mniType, mniTargetType: tnMoneyIssue.mniTargetType, mniMemberType: tnMoneyIssue.mniMemberType, mniTitle: tnMoneyIssue.mniTitle, mniReason: tnMoneyIssue.mniReason, mniAmount: tnMoneyIssue.mniAmount, mniMemberCount: tnMoneyIssue.mniMemberCount, mniTotalAmount: tnMoneyIssue.mniTotalAmount, mniHasExpiry: tnMoneyIssue.mniHasExpiry, mniExpiryDays: tnMoneyIssue.mniExpiryDays, mniCreatedAt: tnMoneyIssue.mniCreatedAt, agcCorporateName: tnAgency.agcCorporateName, agcCorporateCode: tnAgency.agcCorporateCode, }) .from(tnMoneyIssue) .leftJoin(tnAgency, eq(tnMoneyIssue.agcId, tnAgency.agcId)) .where(eq(tnMoneyIssue.mniId, mniId)) .limit(1); return result[0] || null; } async createIssue( data: typeof tnMoneyIssue.$inferInsert, db: MoneyDb = this.drizzle, ) { const result = await db.insert(tnMoneyIssue).values(data).$returningId(); return result[0]?.mniId; } async updateIssueCounts( mniId: number, memberCount: number, totalAmount: number, db: MoneyDb = this.drizzle, ) { await db .update(tnMoneyIssue) .set({ mniMemberCount: memberCount, mniTotalAmount: totalAmount, }) .where(eq(tnMoneyIssue.mniId, mniId)); } async getIssueTargetList(mniId: number, page: number, limit: number = 10) { const offset = (+page - 1) * limit; // 서브쿼리: 회수(EARN_RECOVERY)의 경우 tnMoneySource가 없으므로 // th_money_wallet에서 직접 조회 (mitCreatedAt 기준으로 가장 가까운 이력) const walletBalanceSubquery = sql`( SELECT mwl.mwl_balance FROM th_money_wallet mwl WHERE mwl.mem_id = ${tnMoneyIssueTarget.memId} AND mwl.mwl_created_at <= ${tnMoneyIssueTarget.mitCreatedAt} ORDER BY mwl.mwl_created_at DESC LIMIT 1 )`; const query = this.drizzle .select({ mitId: tnMoneyIssueTarget.mitId, mniId: tnMoneyIssueTarget.mniId, memId: tnMoneyIssueTarget.memId, mitAmount: tnMoneyIssueTarget.mitAmount, mitCreatedAt: tnMoneyIssueTarget.mitCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, agcCorporateCode: tnAgency.agcCorporateCode, // 잔액머니: 적립(source 경유) 또는 회수(서브쿼리) mwlBalance: sql`COALESCE(${thMoneyWallet.mwlBalance}, ${walletBalanceSubquery})`, msrExpiredAt: tnMoneySource.msrExpiredAt, }) .from(tnMoneyIssueTarget) .leftJoin(tnMember, eq(tnMoneyIssueTarget.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) //* 영업대리점: 회원 자체 sag_code 또는 소속기업(agency)의 sag_id로 매칭. //* 기업회원은 회원 sag_code가 NULL이라 기업 sag로 잡아야 영업대리점이 뜬다(미소속 오표시 방지). .leftJoin( tnSalesAgency, or( eq(tnMember.sagCode, tnSalesAgency.sagCode), eq(tnAgency.sagId, tnSalesAgency.sagId), ), ) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .leftJoin( tnMoneySource, and( eq(tnMoneySource.mniId, tnMoneyIssueTarget.mniId), eq(tnMoneySource.memId, tnMoneyIssueTarget.memId), ), ) // th_money_wallet 조인: source의 mwlId로 적립 후 전체 잔액 조회 .leftJoin(thMoneyWallet, eq(tnMoneySource.mwlId, thMoneyWallet.mwlId)) .where(eq(tnMoneyIssueTarget.mniId, mniId)) .orderBy(desc(tnMoneyIssueTarget.mitCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getIssueTargetCount(mniId: number) { const result = await this.drizzle .select({ count: count() }) .from(tnMoneyIssueTarget) .where(eq(tnMoneyIssueTarget.mniId, mniId)); return result[0]?.count || 0; } async createIssueTarget( data: typeof tnMoneyIssueTarget.$inferInsert, db: MoneyDb = this.drizzle, ) { await db.insert(tnMoneyIssueTarget).values(data); } async createIssueTargetBatch( targets: (typeof tnMoneyIssueTarget.$inferInsert)[], ) { if (targets.length === 0) return; await this.drizzle.insert(tnMoneyIssueTarget).values(targets); } // ============================================================================ // 기업머니 이력 (th_money_corp) // ============================================================================ async getCorpHistoryList( agcId: number, where: SQL | undefined, page: number, limit: number = 10, canSeeProcessedBy: boolean = false, ) { const offset = (+page - 1) * limit; const baseWhere = eq(thMoneyCorp.agcId, agcId); const finalWhere = where ? and(baseWhere, where) : baseWhere; const query = this.drizzle .select({ mcpId: thMoneyCorp.mcpId, agcId: thMoneyCorp.agcId, mngId: thMoneyCorp.mngId, mcpActorType: thMoneyCorp.mcpActorType, mcpActorMemId: thMoneyCorp.mcpActorMemId, mcpType: thMoneyCorp.mcpType, mcpTitle: thMoneyCorp.mcpTitle, mcpReason: thMoneyCorp.mcpReason, mcpAmount: thMoneyCorp.mcpAmount, mcpBalance: thMoneyCorp.mcpBalance, mcpCreatedAt: thMoneyCorp.mcpCreatedAt, //* `adm_name` 은 `FN_ENCRYPT` 로 저장된 칸이다 — 복호화 없이 내리면 화면에 암호문이 간다. //* 이름을 새로 복호화하지도 않는다: 계정 ID 는 평문·UNIQUE 이고 이름보다 담긴 개인정보가 //* 적다(계획 M7 D1). 처리자 표기는 아래 `processedBy` 한 덩어리가 진다. processedBy: processedByCell(thMoneyCorp.admId, canSeeProcessedBy), actorMemLoginId: tnMember.memLoginId, actorMemName: sql< string | null >`FN_DECRYPT(${tnMember.memName}, ${this.key})`, mngTargetType: tnMoneyGrant.mngTargetType, }) .from(thMoneyCorp) .leftJoin(tnAdmin, eq(thMoneyCorp.admId, tnAdmin.admId)) .leftJoin(tnMoneyGrant, eq(thMoneyCorp.mngId, tnMoneyGrant.mngId)) .leftJoin(tnMember, eq(thMoneyCorp.mcpActorMemId, tnMember.memId)) .where(finalWhere) .orderBy(desc(thMoneyCorp.mcpCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getCorpHistoryCount(agcId: number, where: SQL | undefined) { const baseWhere = eq(thMoneyCorp.agcId, agcId); const finalWhere = where ? and(baseWhere, where) : baseWhere; const result = await this.drizzle .select({ count: count() }) .from(thMoneyCorp) .leftJoin(tnMoneyGrant, eq(thMoneyCorp.mngId, tnMoneyGrant.mngId)) .where(finalWhere); return result[0]?.count || 0; } async getCorpSummary(agcId: number) { const result = await this.drizzle .select({ mcpType: thMoneyCorp.mcpType, total: sum(thMoneyCorp.mcpAmount), }) .from(thMoneyCorp) .where(eq(thMoneyCorp.agcId, agcId)) .groupBy(thMoneyCorp.mcpType); const summary = { totalEarn: 0, totalEarnRecovery: 0, totalGrant: 0, totalGrantRecovery: 0, currentBalance: 0, }; for (const row of result) { const amount = Number(row.total) || 0; switch (row.mcpType) { case 'EARN': summary.totalEarn = amount; break; case 'EARN_RECOVERY': summary.totalEarnRecovery = Math.abs(amount); break; case 'GRANT': summary.totalGrant = Math.abs(amount); break; case 'GRANT_RECOVERY': summary.totalGrantRecovery = amount; break; } } // 현재 잔액은 agency 테이블에서 조회 const agency = await this.drizzle .select({ agcMoney: tnAgency.agcMoney }) .from(tnAgency) .where(eq(tnAgency.agcId, agcId)) .limit(1); summary.currentBalance = agency[0]?.agcMoney || 0; return summary; } async createCorpHistory( data: typeof thMoneyCorp.$inferInsert, db: MoneyDb = this.drizzle, ) { await db.insert(thMoneyCorp).values(data); } // ============================================================================ // 기업머니 충전요청 (tn_money_charge_request) // ============================================================================ async getChargeRequestList( where: SQL | undefined, page: number, limit: number = 10, canSeeProcessedBy: boolean = false, ) { const offset = (+page - 1) * limit; const query = this.drizzle .select({ mcrId: tnMoneyChargeRequest.mcrId, agcId: tnMoneyChargeRequest.agcId, requestMemId: tnMoneyChargeRequest.requestMemId, mcrStatus: tnMoneyChargeRequest.mcrStatus, mcrPaymentMethod: tnMoneyChargeRequest.mcrPaymentMethod, mcrTitle: tnMoneyChargeRequest.mcrTitle, mcrReason: tnMoneyChargeRequest.mcrReason, mcrAmount: tnMoneyChargeRequest.mcrAmount, mcrAdminMemo: tnMoneyChargeRequest.mcrAdminMemo, mcrRejectedReason: tnMoneyChargeRequest.mcrRejectedReason, mcrApprovedAt: tnMoneyChargeRequest.mcrApprovedAt, mcrRejectedAt: tnMoneyChargeRequest.mcrRejectedAt, mcrCanceledAt: tnMoneyChargeRequest.mcrCanceledAt, mcrCreatedAt: tnMoneyChargeRequest.mcrCreatedAt, mcrUpdatedAt: tnMoneyChargeRequest.mcrUpdatedAt, agcCorporateName: tnAgency.agcCorporateName, agcCorporateCode: tnAgency.agcCorporateCode, sagName: tnSalesAgency.sagName, requestMemLoginId: tnMember.memLoginId, requestMemName: sql< string | null >`FN_DECRYPT(${tnMember.memName}, ${this.key})`, //* 승인자 **이름**은 이 화면이 M7 이전부터 보이던 값이라 지우지 않는다(D1). 다만 자격에 //* 건다 — 이 라우트는 `@Roles('admin','corp')` 라서 기업담당자도 부르고, 복호화한 실명은 //* `processedBy` 보다 **더 많은** 개인정보를 싣는다(로컬 코드리뷰 CRITICAL). //* 형제 키 `approvedAdmId`·`approvedAdmLoginId` 는 `processedBy` 와 값이 같고 읽는 곳이 //* 없어 지웠다 — 가림을 두 곳에 두면 한 곳만 고쳐진다. approvedAdmName: processedByOnly( sql`FN_DECRYPT(${tnAdmin.admName}, ${this.key})`, canSeeProcessedBy, ), processedBy: processedByCell( tnMoneyChargeRequest.approvedAdmId, canSeeProcessedBy, ), }) .from(tnMoneyChargeRequest) .leftJoin(tnAgency, eq(tnMoneyChargeRequest.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnAgency.sagId, tnSalesAgency.sagId)) .leftJoin(tnMember, eq(tnMoneyChargeRequest.requestMemId, tnMember.memId)) .leftJoin(tnAdmin, eq(tnMoneyChargeRequest.approvedAdmId, tnAdmin.admId)) .where(where) .orderBy(desc(tnMoneyChargeRequest.mcrCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getChargeRequestCount(where: SQL | undefined) { const result = await this.drizzle .select({ count: count() }) .from(tnMoneyChargeRequest) .leftJoin(tnAgency, eq(tnMoneyChargeRequest.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnAgency.sagId, tnSalesAgency.sagId)) .where(where); return result[0]?.count || 0; } async createChargeRequest(data: typeof tnMoneyChargeRequest.$inferInsert) { const result = await this.drizzle .insert(tnMoneyChargeRequest) .values(data) .$returningId(); return result[0]?.mcrId; } async getChargeRequestById(mcrId: number) { const result = await this.drizzle .select() .from(tnMoneyChargeRequest) .where(eq(tnMoneyChargeRequest.mcrId, mcrId)) .limit(1); return result[0] || null; } async updateChargeRequest( mcrId: number, data: Partial, ) { await this.drizzle .update(tnMoneyChargeRequest) .set({ ...data, mcrUpdatedAt: sql`CURRENT_TIMESTAMP` as any, }) .where(eq(tnMoneyChargeRequest.mcrId, mcrId)); } /** * 충전요청 승인. **트랜잭션을 열지 않는다** — 서비스의 `runInTransaction` 콜백이 받은 `tx` 로 * 쓴다. 감사 행이 같은 콜백에서 같은 `tx` 로 적재되어야 승인과 원자적이다(계획 M8 D5). * 잔액 상한·기업 없음은 여기서 던지고, 그 예외가 콜백을 거쳐 선점 UPDATE 까지 되돌린다. */ async approveChargeRequest( request: typeof tnMoneyChargeRequest.$inferSelect, admId: number, adminMemo: string | null, approvedAt: string, tx: MoneyDb, ): Promise< | { processed: false } | { processed: true; balanceBefore: number; newBalance: number } > { const claim = await tx .update(tnMoneyChargeRequest) .set({ approvedAdmId: admId, mcrStatus: 'APPROVED', mcrAdminMemo: adminMemo, mcrApprovedAt: approvedAt, mcrUpdatedAt: sql`CURRENT_TIMESTAMP` as any, }) .where( and( eq(tnMoneyChargeRequest.mcrId, request.mcrId), eq(tnMoneyChargeRequest.mcrStatus, 'REQUESTED'), ), ); const affectedRows = (claim as unknown as Array<{ affectedRows?: number }>)[0]?.affectedRows ?? 0; if (affectedRows === 0) { return { processed: false }; } const agency = await this.getAgencyByIdForUpdate(request.agcId, tx); if (!agency) { throw new Error('AGENCY_NOT_FOUND'); } const newBalance = agency.agcMoney + request.mcrAmount; if (newBalance > MAX_MONEY_BALANCE_AMOUNT) { throw new BadRequestException( '기업머니 잔액은 4,294,967,295원을 초과할 수 없습니다.', ); } await tx.insert(thMoneyCorp).values({ agcId: request.agcId, admId, mngId: null, mcpActorType: 'ADMIN', mcpActorMemId: null, mcpType: 'EARN', mcpTitle: request.mcrTitle, mcpReason: adminMemo || request.mcrReason || `충전요청 승인 #${request.mcrId}`, mcpAmount: request.mcrAmount, mcpBalance: newBalance, }); await tx .update(tnAgency) .set({ agcMoney: newBalance }) .where(eq(tnAgency.agcId, request.agcId)); return { processed: true, balanceBefore: agency.agcMoney, newBalance }; } async rejectChargeRequest( mcrId: number, admId: number, adminMemo: string | null, rejectedReason: string, rejectedAt: string, ): Promise { const result = await this.drizzle .update(tnMoneyChargeRequest) .set({ approvedAdmId: admId, mcrStatus: 'REJECTED', mcrAdminMemo: adminMemo, mcrRejectedReason: rejectedReason, mcrRejectedAt: rejectedAt, mcrUpdatedAt: sql`CURRENT_TIMESTAMP` as any, }) .where( and( eq(tnMoneyChargeRequest.mcrId, mcrId), eq(tnMoneyChargeRequest.mcrStatus, 'REQUESTED'), ), ); return ( ((result as unknown as Array<{ affectedRows?: number }>)[0] ?.affectedRows ?? 0) > 0 ); } async cancelChargeRequest( mcrId: number, agcId: number, canceledAt: string, ): Promise { const result = await this.drizzle .update(tnMoneyChargeRequest) .set({ mcrStatus: 'CANCELED', mcrCanceledAt: canceledAt, mcrUpdatedAt: sql`CURRENT_TIMESTAMP` as any, }) .where( and( eq(tnMoneyChargeRequest.mcrId, mcrId), eq(tnMoneyChargeRequest.agcId, agcId), eq(tnMoneyChargeRequest.mcrStatus, 'REQUESTED'), ), ); return ( ((result as unknown as Array<{ affectedRows?: number }>)[0] ?.affectedRows ?? 0) > 0 ); } // ============================================================================ // 기업회원 지급 (tn_money_grant) // ============================================================================ async createGrant( data: typeof tnMoneyGrant.$inferInsert, db: MoneyDb = this.drizzle, ) { const result = await db.insert(tnMoneyGrant).values(data).$returningId(); return result[0]?.mngId; } async updateGrantCounts( mngId: number, memberCount: number, totalAmount: number, db: MoneyDb = this.drizzle, ) { await db .update(tnMoneyGrant) .set({ mngMemberCount: memberCount, mngTotalAmount: totalAmount, }) .where(eq(tnMoneyGrant.mngId, mngId)); } async createGrantTarget( data: typeof tnMoneyGrantTarget.$inferInsert, db: MoneyDb = this.drizzle, ) { await db.insert(tnMoneyGrantTarget).values(data); } async createGrantTargetBatch( targets: (typeof tnMoneyGrantTarget.$inferInsert)[], ) { if (targets.length === 0) return; await this.drizzle.insert(tnMoneyGrantTarget).values(targets); } // ============================================================================ // 결제 (tn_money_payment) // ============================================================================ async getPaymentList( where: SQL | undefined, page: number, limit: number = 10, ) { const offset = (+page - 1) * limit; const query = this.drizzle .select({ mpyId: tnMoneyPayment.mpyId, memId: tnMoneyPayment.memId, mpyOrderNumber: tnMoneyPayment.mpyOrderNumber, mpyTid: tnMoneyPayment.mpyTid, mpyAmount: tnMoneyPayment.mpyAmount, mpyMoney: tnMoneyPayment.mpyMoney, mpyStatus: tnMoneyPayment.mpyStatus, mpyCardType: tnMoneyPayment.mpyCardType, mpyCardName: tnMoneyPayment.mpyCardName, mpyCardNumber: tnMoneyPayment.mpyCardNumber, mpyCanceledAmount: tnMoneyPayment.mpyCanceledAmount, mpyCancelCount: tnMoneyPayment.mpyCancelCount, mpyApprovedAt: tnMoneyPayment.mpyApprovedAt, mpyCanceledAt: tnMoneyPayment.mpyCanceledAt, mpyCreatedAt: tnMoneyPayment.mpyCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, }) .from(tnMoneyPayment) .leftJoin(tnMember, eq(tnMoneyPayment.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .where(where) .orderBy(desc(tnMoneyPayment.mpyCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getPaymentCount(where: SQL | undefined) { const result = await this.drizzle .select({ count: count() }) .from(tnMoneyPayment) .leftJoin(tnMember, eq(tnMoneyPayment.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .where(where); return result[0]?.count || 0; } /** * 결제관리 목록 조회 (searchKey/searchText 패턴) */ async getPaymentListWithSearch(dto: any, page: number, limit: number = 10) { const offset = (+page - 1) * limit; const conditions: SQL[] = []; // 날짜 조건 if (dto.startDate && dto.endDate) { conditions.push( and( gte(tnMoneyPayment.mpyApprovedAt, `${dto.startDate} 00:00:00`), lte(tnMoneyPayment.mpyApprovedAt, `${dto.endDate} 23:59:59`), )!, ); } // 결제상태 조건 if (dto.mpyStatus) { conditions.push(eq(tnMoneyPayment.mpyStatus, dto.mpyStatus)); } // 영업대리점명 조건 if (dto.sagName) { if (dto.sagName === '-') { conditions.push(isNull(tnSalesAgency.sagName)); } else { conditions.push(eq(tnSalesAgency.sagName, dto.sagName)); } } // 회원타입 조건 if (dto.memType) { const memTypeValue = dto.memType === 'GENERAL' ? 'MEM001' : dto.memType === 'CORP' ? 'MEM002' : dto.memType; conditions.push(eq(tnMember.memType, memTypeValue)); } // 기업명 조건 if (dto.agcCorporateName) { conditions.push( like(tnAgency.agcCorporateName, `%${dto.agcCorporateName}%`), ); } // searchKey + searchText 패턴 (CODE_STYLE_GUIDE 준수) if (dto.searchKey && dto.searchText) { const searchText = `%${dto.searchText}%`; switch (dto.searchKey) { case 'memLoginId': conditions.push(like(tnMember.memLoginId, searchText)); break; case 'memName': conditions.push( sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${searchText} COLLATE utf8mb4_general_ci`, ); break; case 'orderNumber': conditions.push(like(tnMoneyPayment.mpyOrderNumber, searchText)); break; } } const where = conditions.length > 0 ? and(...conditions) : undefined; const query = this.drizzle .select({ mpyId: tnMoneyPayment.mpyId, memId: tnMoneyPayment.memId, mpyOrderNumber: tnMoneyPayment.mpyOrderNumber, mpyTid: tnMoneyPayment.mpyTid, mpyAmount: tnMoneyPayment.mpyAmount, mpyMoney: tnMoneyPayment.mpyMoney, mpyStatus: tnMoneyPayment.mpyStatus, mpyCardType: tnMoneyPayment.mpyCardType, mpyCardName: tnMoneyPayment.mpyCardName, mpyCardNumber: tnMoneyPayment.mpyCardNumber, mpyCanceledAmount: tnMoneyPayment.mpyCanceledAmount, mpyCancelCount: tnMoneyPayment.mpyCancelCount, mpyApprovedAt: tnMoneyPayment.mpyApprovedAt, mpyCanceledAt: tnMoneyPayment.mpyCanceledAt, mpyCreatedAt: tnMoneyPayment.mpyCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, }) .from(tnMoneyPayment) .leftJoin(tnMember, eq(tnMoneyPayment.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .where(where) .orderBy(desc(tnMoneyPayment.mpyCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } /** * 결제관리 카운트 (searchKey/searchText 패턴) */ async getPaymentCountWithSearch(dto: any) { const conditions: SQL[] = []; // 날짜 조건 if (dto.startDate && dto.endDate) { conditions.push( and( gte(tnMoneyPayment.mpyApprovedAt, `${dto.startDate} 00:00:00`), lte(tnMoneyPayment.mpyApprovedAt, `${dto.endDate} 23:59:59`), )!, ); } // 결제상태 조건 if (dto.mpyStatus) { conditions.push(eq(tnMoneyPayment.mpyStatus, dto.mpyStatus)); } // 영업대리점명 조건 if (dto.sagName) { if (dto.sagName === '-') { conditions.push(isNull(tnSalesAgency.sagName)); } else { conditions.push(eq(tnSalesAgency.sagName, dto.sagName)); } } // 회원타입 조건 if (dto.memType) { const memTypeValue = dto.memType === 'GENERAL' ? 'MEM001' : dto.memType === 'CORP' ? 'MEM002' : dto.memType; conditions.push(eq(tnMember.memType, memTypeValue)); } // 기업명 조건 if (dto.agcCorporateName) { conditions.push( like(tnAgency.agcCorporateName, `%${dto.agcCorporateName}%`), ); } // searchKey + searchText 패턴 (CODE_STYLE_GUIDE 준수) if (dto.searchKey && dto.searchText) { const searchText = `%${dto.searchText}%`; switch (dto.searchKey) { case 'memLoginId': conditions.push(like(tnMember.memLoginId, searchText)); break; case 'memName': conditions.push( sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${searchText} COLLATE utf8mb4_general_ci`, ); break; case 'orderNumber': conditions.push(like(tnMoneyPayment.mpyOrderNumber, searchText)); break; } } const where = conditions.length > 0 ? and(...conditions) : undefined; const result = await this.drizzle .select({ count: count() }) .from(tnMoneyPayment) .leftJoin(tnMember, eq(tnMoneyPayment.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .where(where); return result[0]?.count || 0; } async getPaymentById(mpyId: number) { const result = await this.drizzle .select({ mpyId: tnMoneyPayment.mpyId, memId: tnMoneyPayment.memId, mpyOrderNumber: tnMoneyPayment.mpyOrderNumber, mpyTid: tnMoneyPayment.mpyTid, mpyAmount: tnMoneyPayment.mpyAmount, mpyMoney: tnMoneyPayment.mpyMoney, mpyStatus: tnMoneyPayment.mpyStatus, mpyCardType: tnMoneyPayment.mpyCardType, mpyCardName: tnMoneyPayment.mpyCardName, mpyCardNumber: tnMoneyPayment.mpyCardNumber, mpyCanceledAmount: tnMoneyPayment.mpyCanceledAmount, mpyCancelCount: tnMoneyPayment.mpyCancelCount, mpyApprovedAt: tnMoneyPayment.mpyApprovedAt, mpyCanceledAt: tnMoneyPayment.mpyCanceledAt, mpyCreatedAt: tnMoneyPayment.mpyCreatedAt, mpyUpdatedAt: tnMoneyPayment.mpyUpdatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, }) .from(tnMoneyPayment) .leftJoin(tnMember, eq(tnMoneyPayment.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .where(eq(tnMoneyPayment.mpyId, mpyId)) .limit(1); return result[0] || null; } /** * 결제 행 잠금 조회 (`FOR UPDATE`). 취소의 예약 · 반영이 회원 잠금 뒤에 상태를 이 잠금 아래에서 다시 본다. */ async getPaymentByIdForUpdate(mpyId: number, db: MoneyDb = this.drizzle) { const [result] = await db .select({ mpyId: tnMoneyPayment.mpyId, mpyStatus: tnMoneyPayment.mpyStatus, mpyMoney: tnMoneyPayment.mpyMoney, mpyCanceledAmount: tnMoneyPayment.mpyCanceledAmount, mpyUpdatedAt: tnMoneyPayment.mpyUpdatedAt, //* 예약 시각과 같은 DB 시계로 잰다 //* shortcut: 옛 예약은 수정일이 비면 결제 시각부터 재 실제보다 길게 읽힌다, 옛 예약이 실제로 나오면 첫 확인 시각을 적는다 secondsSinceUpdate: sql`TIMESTAMPDIFF(SECOND, COALESCE(${tnMoneyPayment.mpyUpdatedAt}, ${tnMoneyPayment.mpyCreatedAt}), NOW())`, }) .from(tnMoneyPayment) .where(eq(tnMoneyPayment.mpyId, mpyId)) .limit(1) .for('update'); return result; } /** 회원에게 취소 처리 중인 결제가 있는가. 차감 실패의 사유를 가른다. */ async hasCancellingPayment(memId: number, db: MoneyDb = this.drizzle) { const [row] = await db .select({ mpyId: tnMoneyPayment.mpyId }) .from(tnMoneyPayment) .where( and( eq(tnMoneyPayment.memId, memId), eq(tnMoneyPayment.mpyStatus, MoneyPaymentStatus.CANCELLING), ), ) .limit(1); return !!row; } /** 이 원천에서 차감된 환불 신청이 대기 중인가. 환불 이력은 신청 wallet 의 usage 로 원천과 이어진다. */ async hasPendingRefundOnSource(msrId: number, db: MoneyDb = this.drizzle) { const [row] = await db .select({ mrfId: tnMoneyRefund.mrfId }) .from(tnMoneyRefund) .innerJoin(thMoneyUsage, eq(thMoneyUsage.mwlId, tnMoneyRefund.mwlId)) .where( and( eq(thMoneyUsage.msrId, msrId), eq(tnMoneyRefund.mrfStatus, MoneyRefundStatus.PENDING), ), ) .limit(1); return !!row; } async getPaymentByTid(mpyTid: string) { const result = await this.drizzle .select() .from(tnMoneyPayment) .where(eq(tnMoneyPayment.mpyTid, mpyTid)) .limit(1); return result[0] || null; } async getPaymentByMemIdAndTid(memId: number, mpyTid: string) { const result = await this.drizzle .select() .from(tnMoneyPayment) .where( and(eq(tnMoneyPayment.memId, memId), eq(tnMoneyPayment.mpyTid, mpyTid)), ) .limit(1); return result[0] || null; } async createPayment(data: any) { const result = await this.drizzle .insert(tnMoneyPayment) .values(data) .$returningId(); return result[0]?.mpyId; } async updatePaymentStatus( mpyId: number, status: string, canceledAmount?: number, db: MoneyDb = this.drizzle, ) { const updateData: any = { mpyStatus: status, }; if (canceledAmount !== undefined) { updateData.mpyCanceledAmount = canceledAmount; updateData.mpyCanceledAt = sql`CURRENT_TIMESTAMP`; updateData.mpyCancelCount = sql`${tnMoneyPayment.mpyCancelCount} + 1`; } await db .update(tnMoneyPayment) .set(updateData) .where(eq(tnMoneyPayment.mpyId, mpyId)); } /** * 취소 예약. `updatePaymentStatus` 와 달리 취소 횟수 · 취소일을 건드리지 않는다. 예약 동안 취소액 칸은 예약액, * 수정일 칸은 예약 시각이다 (결과 확인이 같은 예약인지 가르고 나이를 잰다). */ async reservePaymentCancel( mpyId: number, reservedAmount: number, db: MoneyDb = this.drizzle, ) { await db .update(tnMoneyPayment) .set({ mpyStatus: MoneyPaymentStatus.CANCELLING, mpyCanceledAmount: reservedAmount, mpyUpdatedAt: sql`CURRENT_TIMESTAMP` as any, }) .where(eq(tnMoneyPayment.mpyId, mpyId)); } /** * 원장으로 구한 예약액. 원천을 움직이는 쓰기는 예약 · 해제 말고 모두 사용 내역을 남기므로 원금 − 차감 + 복구 − 지금 잔여가 예약액이다. * 한 문장이라 사용 복구와 겹쳐도 앞뒤가 맞는 값을 읽는다. */ async getCancelReservedFromUsage(msrId: number) { const [row] = await this.drizzle .select({ reserved: sql`${tnMoneySource.msrOriginalAmount} - COALESCE(SUM(CASE WHEN ${thMoneyUsage.musType} = 'DEDUCT' THEN ${thMoneyUsage.musAmount} END), 0) + COALESCE(SUM(CASE WHEN ${thMoneyUsage.musType} = 'RESTORE' THEN ${thMoneyUsage.musAmount} END), 0) - ${tnMoneySource.msrRemainingAmount}`, }) .from(tnMoneySource) .leftJoin(thMoneyUsage, eq(thMoneyUsage.msrId, tnMoneySource.msrId)) .where(eq(tnMoneySource.msrId, msrId)) .groupBy(tnMoneySource.msrId); return Number(row?.reserved); } /** 취소 예약을 푼다 (환불 안 됨). 결제완료로 되돌리고 취소액 칸을 비운다. */ async releasePaymentCancel(mpyId: number, db: MoneyDb = this.drizzle) { await db .update(tnMoneyPayment) .set({ mpyStatus: MoneyPaymentStatus.COMPLETED, mpyCanceledAmount: 0, }) .where(eq(tnMoneyPayment.mpyId, mpyId)); } async getPaymentCancels(mpyId: number, canSeeProcessedBy: boolean = false) { return await this.drizzle .select({ mpcId: thMoneyPaymentCancel.mpcId, mpyId: thMoneyPaymentCancel.mpyId, mpcType: thMoneyPaymentCancel.mpcType, mpcAmount: thMoneyPaymentCancel.mpcAmount, mpcReason: thMoneyPaymentCancel.mpcReason, mpcSequence: thMoneyPaymentCancel.mpcSequence, mpcCreatedAt: thMoneyPaymentCancel.mpcCreatedAt, //* 기업머니 이력과 같은 자리 — 복호화 없는 `adm_name` 을 지우고 계정으로 바꾼다(D1). processedBy: processedByCell( thMoneyPaymentCancel.admId, canSeeProcessedBy, ), }) .from(thMoneyPaymentCancel) .leftJoin(tnAdmin, eq(thMoneyPaymentCancel.admId, tnAdmin.admId)) .where(eq(thMoneyPaymentCancel.mpyId, mpyId)) .orderBy(desc(thMoneyPaymentCancel.mpcCreatedAt)); } async createPaymentCancel( data: typeof thMoneyPaymentCancel.$inferInsert, db: MoneyDb = this.drizzle, ) { await db.insert(thMoneyPaymentCancel).values(data); } // ============================================================================ // 환불 (tn_money_refund) // ============================================================================ async getRefundList( where: SQL | undefined, page: number, limit: number = 10, canSeeProcessedBy: boolean = false, ) { const offset = (+page - 1) * limit; const query = this.drizzle .select({ mrfId: tnMoneyRefund.mrfId, memId: tnMoneyRefund.memId, mrfStatus: tnMoneyRefund.mrfStatus, mrfAmount: tnMoneyRefund.mrfAmount, mrfFee: tnMoneyRefund.mrfFee, mrfActualAmount: tnMoneyRefund.mrfActualAmount, mrfBankName: tnMoneyRefund.mrfBankName, mrfAccountNumber: tnMoneyRefund.mrfAccountNumber, mrfAccountHolder: tnMoneyRefund.mrfAccountHolder, mrfReason: tnMoneyRefund.mrfReason, mrfAdminMemo: tnMoneyRefund.mrfAdminMemo, mrfProcessedAt: tnMoneyRefund.mrfProcessedAt, mrfCreatedAt: tnMoneyRefund.mrfCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, //* 환불 목록에는 처리자 칸 자체가 없었다 — 조인부터 새로 낸다(계획 Task 7). processedBy: processedByCell(tnMoneyRefund.admId, canSeeProcessedBy), }) .from(tnMoneyRefund) .leftJoin(tnMember, eq(tnMoneyRefund.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .leftJoin(tnAdmin, eq(tnMoneyRefund.admId, tnAdmin.admId)) .where(where) .orderBy(desc(tnMoneyRefund.mrfCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getRefundCount(where: SQL | undefined) { const result = await this.drizzle .select({ count: count() }) .from(tnMoneyRefund) .leftJoin(tnMember, eq(tnMoneyRefund.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .where(where); return result[0]?.count || 0; } /** * 환불관리 목록 조회 (searchKey/searchText 패턴) */ async getRefundListWithSearch( dto: any, page: number, limit: number = 10, canSeeProcessedBy: boolean = false, ) { const offset = (+page - 1) * limit; const conditions: SQL[] = []; // 날짜 조건 if (dto.startDate && dto.endDate) { conditions.push( and( gte(tnMoneyRefund.mrfCreatedAt, `${dto.startDate} 00:00:00`), lte(tnMoneyRefund.mrfCreatedAt, `${dto.endDate} 23:59:59`), )!, ); } // 환불상태 조건 if (dto.mrfStatus) { conditions.push(eq(tnMoneyRefund.mrfStatus, dto.mrfStatus)); } // 영업대리점명 조건 if (dto.sagName) { if (dto.sagName === '-') { conditions.push(isNull(tnSalesAgency.sagName)); } else { conditions.push(eq(tnSalesAgency.sagName, dto.sagName)); } } // 회원타입 조건 if (dto.memType) { const memTypeValue = dto.memType === 'GENERAL' ? 'MEM001' : dto.memType === 'CORP' ? 'MEM002' : dto.memType; conditions.push(eq(tnMember.memType, memTypeValue)); } // 기업명 조건 if (dto.agcCorporateName) { conditions.push( like(tnAgency.agcCorporateName, `%${dto.agcCorporateName}%`), ); } // searchKey + searchText 패턴 (CODE_STYLE_GUIDE 준수) if (dto.searchKey && dto.searchText) { const searchText = `%${dto.searchText}%`; switch (dto.searchKey) { case 'memLoginId': conditions.push(like(tnMember.memLoginId, searchText)); break; case 'memName': conditions.push( sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${searchText} COLLATE utf8mb4_general_ci`, ); break; } } const where = conditions.length > 0 ? and(...conditions) : undefined; const query = this.drizzle .select({ mrfId: tnMoneyRefund.mrfId, memId: tnMoneyRefund.memId, mrfStatus: tnMoneyRefund.mrfStatus, mrfAmount: tnMoneyRefund.mrfAmount, mrfFee: tnMoneyRefund.mrfFee, mrfActualAmount: tnMoneyRefund.mrfActualAmount, mrfBankName: tnMoneyRefund.mrfBankName, mrfAccountNumber: tnMoneyRefund.mrfAccountNumber, mrfAccountHolder: tnMoneyRefund.mrfAccountHolder, mrfReason: tnMoneyRefund.mrfReason, mrfAdminMemo: tnMoneyRefund.mrfAdminMemo, mrfProcessedAt: tnMoneyRefund.mrfProcessedAt, mrfCreatedAt: tnMoneyRefund.mrfCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, //* 환불 목록에는 처리자 칸 자체가 없었다 — 조인부터 새로 낸다(계획 Task 7). processedBy: processedByCell(tnMoneyRefund.admId, canSeeProcessedBy), }) .from(tnMoneyRefund) .leftJoin(tnMember, eq(tnMoneyRefund.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .leftJoin(tnAdmin, eq(tnMoneyRefund.admId, tnAdmin.admId)) .where(where) .orderBy(desc(tnMoneyRefund.mrfCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } /** * 환불관리 카운트 (searchKey/searchText 패턴) */ async getRefundCountWithSearch(dto: any) { const conditions: SQL[] = []; // 날짜 조건 if (dto.startDate && dto.endDate) { conditions.push( and( gte(tnMoneyRefund.mrfCreatedAt, `${dto.startDate} 00:00:00`), lte(tnMoneyRefund.mrfCreatedAt, `${dto.endDate} 23:59:59`), )!, ); } // 환불상태 조건 if (dto.mrfStatus) { conditions.push(eq(tnMoneyRefund.mrfStatus, dto.mrfStatus)); } // 영업대리점명 조건 if (dto.sagName) { if (dto.sagName === '-') { conditions.push(isNull(tnSalesAgency.sagName)); } else { conditions.push(eq(tnSalesAgency.sagName, dto.sagName)); } } // 회원타입 조건 if (dto.memType) { const memTypeValue = dto.memType === 'GENERAL' ? 'MEM001' : dto.memType === 'CORP' ? 'MEM002' : dto.memType; conditions.push(eq(tnMember.memType, memTypeValue)); } // 기업명 조건 if (dto.agcCorporateName) { conditions.push( like(tnAgency.agcCorporateName, `%${dto.agcCorporateName}%`), ); } // searchKey + searchText 패턴 (CODE_STYLE_GUIDE 준수) if (dto.searchKey && dto.searchText) { const searchText = `%${dto.searchText}%`; switch (dto.searchKey) { case 'memLoginId': conditions.push(like(tnMember.memLoginId, searchText)); break; case 'memName': conditions.push( sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${searchText} COLLATE utf8mb4_general_ci`, ); break; } } const where = conditions.length > 0 ? and(...conditions) : undefined; const result = await this.drizzle .select({ count: count() }) .from(tnMoneyRefund) .leftJoin(tnMember, eq(tnMoneyRefund.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .where(where); return result[0]?.count || 0; } /** * 환불 행 잠금 조회 (`FOR UPDATE`). 승인 · 반려는 상태를 이 잠금 아래에서 다시 본다. */ async getRefundByIdForUpdate(mrfId: number, db: MoneyDb = this.drizzle) { const [result] = await db .select({ mrfId: tnMoneyRefund.mrfId, memId: tnMoneyRefund.memId, mwlId: tnMoneyRefund.mwlId, mrfStatus: tnMoneyRefund.mrfStatus, mrfAmount: tnMoneyRefund.mrfAmount, }) .from(tnMoneyRefund) .where(eq(tnMoneyRefund.mrfId, mrfId)) .limit(1) .for('update'); return result; } async getRefundById(mrfId: number) { const result = await this.drizzle .select({ mrfId: tnMoneyRefund.mrfId, memId: tnMoneyRefund.memId, mwlId: tnMoneyRefund.mwlId, mrfStatus: tnMoneyRefund.mrfStatus, mrfAmount: tnMoneyRefund.mrfAmount, mrfFee: tnMoneyRefund.mrfFee, mrfActualAmount: tnMoneyRefund.mrfActualAmount, mrfBankName: tnMoneyRefund.mrfBankName, mrfAccountNumber: tnMoneyRefund.mrfAccountNumber, mrfAccountHolder: tnMoneyRefund.mrfAccountHolder, mrfReason: tnMoneyRefund.mrfReason, mrfAdminMemo: tnMoneyRefund.mrfAdminMemo, mrfProcessedAt: tnMoneyRefund.mrfProcessedAt, mrfCreatedAt: tnMoneyRefund.mrfCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, memMoney: tnMember.memMoney, memPurchaseMoney: tnMember.memPurchaseMoney, memAgencyMoney: tnMember.memAgencyMoney, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, }) .from(tnMoneyRefund) .leftJoin(tnMember, eq(tnMoneyRefund.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .where(eq(tnMoneyRefund.mrfId, mrfId)) .limit(1); return result[0] || null; } async getRefundsByMemId(memId: number) { return await this.drizzle .select({ mrfId: tnMoneyRefund.mrfId, mrfAmount: tnMoneyRefund.mrfAmount, mrfStatus: tnMoneyRefund.mrfStatus, mrfReason: tnMoneyRefund.mrfReason, mrfAdminMemo: tnMoneyRefund.mrfAdminMemo, mrfProcessedAt: tnMoneyRefund.mrfProcessedAt, mrfCreatedAt: tnMoneyRefund.mrfCreatedAt, }) .from(tnMoneyRefund) .where(eq(tnMoneyRefund.memId, memId)) .orderBy(desc(tnMoneyRefund.mrfCreatedAt)); } /** * 특정 결제건(mpyId)과 연관된 환불 내역 조회 * th_money_usage를 통해 해당 결제의 source에서 차감된 환불 내역 조회 * mwlId를 사용하여 정확하게 매칭 */ async getRefundsByPaymentId(mpyId: number) { // 1. 해당 결제의 source 조회 const source = await this.getSourceByPaymentId(mpyId); if (!source) return []; // 2. 해당 source에서 차감된 usage 조회 const usages = await this.drizzle .select({ mwlId: thMoneyUsage.mwlId, musAmount: thMoneyUsage.musAmount, }) .from(thMoneyUsage) .where(eq(thMoneyUsage.msrId, source.msrId)); if (usages.length === 0) return []; const mwlIds = usages.map((u) => u.mwlId); // 3. mwlId로 직접 환불 조회 (정확한 매칭) // tn_money_refund.mwlId는 환불신청 시 생성된 wallet ID const refunds = await this.drizzle .select({ mrfId: tnMoneyRefund.mrfId, mrfAmount: tnMoneyRefund.mrfAmount, mrfStatus: tnMoneyRefund.mrfStatus, mrfReason: tnMoneyRefund.mrfReason, mrfAdminMemo: tnMoneyRefund.mrfAdminMemo, mrfProcessedAt: tnMoneyRefund.mrfProcessedAt, mrfCreatedAt: tnMoneyRefund.mrfCreatedAt, mwlId: tnMoneyRefund.mwlId, }) .from(tnMoneyRefund) .where( sql`${tnMoneyRefund.mwlId} IN (${sql.join( mwlIds.map((id) => sql`${id}`), sql`, `, )})`, ); // 4. usage에서 차감 금액 매칭 return refunds.map((refund) => { const usage = usages.find((u) => u.mwlId === refund.mwlId); return { ...refund, mrfAmountFromThis: usage?.musAmount || 0, mrfTotalAmount: refund.mrfAmount, }; }); } async createRefund( data: typeof tnMoneyRefund.$inferInsert, db: MoneyDb = this.drizzle, ) { const result = await db.insert(tnMoneyRefund).values(data).$returningId(); return result[0]?.mrfId; } async updateRefundStatus( mrfId: number, //* 영업대리점 토큰에는 `adm_id` 가 없다 — `null` 이 '처리 계정 없음' 이라는 관측이고, //* `undefined` 로 두면 drizzle 이 칸을 빼 직전 값이 남는다. admId: number | null, status: string, adminMemo?: string, db: MoneyDb = this.drizzle, ) { await db .update(tnMoneyRefund) .set({ admId, mrfStatus: status, mrfAdminMemo: adminMemo, mrfProcessedAt: sql`CURRENT_TIMESTAMP`, }) .where(eq(tnMoneyRefund.mrfId, mrfId)); } async deleteRefund(mrfId: number) { await this.drizzle .delete(tnMoneyRefund) .where(eq(tnMoneyRefund.mrfId, mrfId)); } // ============================================================================ // 지갑 이력 (th_money_wallet) // ============================================================================ async getWalletList( where: SQL | undefined, page: number, limit: number = 10, ) { const offset = (+page - 1) * limit; const query = this.drizzle .select({ mwlId: thMoneyWallet.mwlId, memId: thMoneyWallet.memId, mwlType: thMoneyWallet.mwlType, mwlAmount: thMoneyWallet.mwlAmount, mwlBalance: thMoneyWallet.mwlBalance, mwlDescription: thMoneyWallet.mwlDescription, mwlCreatedAt: thMoneyWallet.mwlCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, }) .from(thMoneyWallet) .leftJoin(tnMember, eq(thMoneyWallet.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .where(where) .orderBy(desc(thMoneyWallet.mwlCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getWalletCount(where: SQL | undefined) { const result = await this.drizzle .select({ count: count() }) .from(thMoneyWallet) .leftJoin(tnMember, eq(thMoneyWallet.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .where(where); return result[0]?.count || 0; } /** * 내역관리 목록 조회 (searchKey/searchText 패턴) */ async getWalletListWithSearch(dto: any, page: number, limit: number = 10) { const offset = (+page - 1) * limit; const conditions: SQL[] = []; // 날짜 조건 if (dto.startDate && dto.endDate) { conditions.push( and( gte(thMoneyWallet.mwlCreatedAt, `${dto.startDate} 00:00:00`), lte(thMoneyWallet.mwlCreatedAt, `${dto.endDate} 23:59:59`), )!, ); } // 구분 조건 if (dto.mwlType) { conditions.push(eq(thMoneyWallet.mwlType, dto.mwlType)); } // 영업대리점명 조건 if (dto.sagName) { if (dto.sagName === '-') { conditions.push(isNull(tnSalesAgency.sagName)); } else { conditions.push(eq(tnSalesAgency.sagName, dto.sagName)); } } // 회원타입 조건 if (dto.memType) { const memTypeValue = dto.memType === 'GENERAL' ? 'MEM001' : dto.memType === 'CORP' ? 'MEM002' : dto.memType; conditions.push(eq(tnMember.memType, memTypeValue)); } // 기업명 조건 if (dto.agcCorporateName) { conditions.push( like(tnAgency.agcCorporateName, `%${dto.agcCorporateName}%`), ); } //* 기업관리자(corp) 스코프 — 자기 기업 회원머니만 if (dto.agcId) { conditions.push(eq(tnMember.agcId, dto.agcId)); } //* 조직관리자 부분트리 스코프 — 회원 소속 org가 관리 org_path 하위인 것만. if (dto.orgPath) { conditions.push( sql`EXISTS (SELECT 1 FROM tn_agency_organization o WHERE o.org_id = ${tnMember.orgId} AND o.org_path LIKE ${dto.orgPath + '%'})`, ); } // searchKey + searchText 패턴 (CODE_STYLE_GUIDE 준수) if (dto.searchKey && dto.searchText) { const searchText = `%${dto.searchText}%`; switch (dto.searchKey) { case 'memLoginId': conditions.push(like(tnMember.memLoginId, searchText)); break; case 'memName': conditions.push( sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${searchText} COLLATE utf8mb4_general_ci`, ); break; case 'description': conditions.push(like(thMoneyWallet.mwlDescription, searchText)); break; case 'reason': // 적립(mniReason) 또는 지급(mngReason) 사유 검색 conditions.push( or( like(tnMoneyGrant.mngReason, searchText), like(tnMoneyIssue.mniReason, searchText), )!, ); break; } } const where = conditions.length > 0 ? and(...conditions) : undefined; const query = this.drizzle .select({ mwlId: thMoneyWallet.mwlId, memId: thMoneyWallet.memId, mwlType: thMoneyWallet.mwlType, mwlAmount: thMoneyWallet.mwlAmount, mwlBalance: thMoneyWallet.mwlBalance, mwlDescription: thMoneyWallet.mwlDescription, mwlCreatedAt: thMoneyWallet.mwlCreatedAt, memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, memType: tnMember.memType, sagName: tnSalesAgency.sagName, sacName: tnSalesAgencyChannel.sacName, agcCorporateName: tnAgency.agcCorporateName, msrExpiredAt: tnMoneySource.msrExpiredAt, // 적립(mniReason) 또는 지급(mngReason) 사유 - COALESCE로 둘 중 하나 선택 mwlReason: sql`COALESCE(${tnMoneyGrant.mngReason}, ${tnMoneyIssue.mniReason})`, }) .from(thMoneyWallet) .leftJoin(tnMember, eq(thMoneyWallet.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .leftJoin(tnMoneySource, eq(thMoneyWallet.mwlId, tnMoneySource.mwlId)) .leftJoin(tnMoneyGrant, eq(tnMoneySource.mngId, tnMoneyGrant.mngId)) .leftJoin(tnMoneyIssue, eq(tnMoneySource.mniId, tnMoneyIssue.mniId)) .where(where) .orderBy(desc(thMoneyWallet.mwlCreatedAt)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } /** * 내역관리 카운트 (searchKey/searchText 패턴) */ async getWalletCountWithSearch(dto: any) { const conditions: SQL[] = []; // 날짜 조건 if (dto.startDate && dto.endDate) { conditions.push( and( gte(thMoneyWallet.mwlCreatedAt, `${dto.startDate} 00:00:00`), lte(thMoneyWallet.mwlCreatedAt, `${dto.endDate} 23:59:59`), )!, ); } // 구분 조건 if (dto.mwlType) { conditions.push(eq(thMoneyWallet.mwlType, dto.mwlType)); } // 영업대리점명 조건 if (dto.sagName) { if (dto.sagName === '-') { conditions.push(isNull(tnSalesAgency.sagName)); } else { conditions.push(eq(tnSalesAgency.sagName, dto.sagName)); } } // 회원타입 조건 if (dto.memType) { const memTypeValue = dto.memType === 'GENERAL' ? 'MEM001' : dto.memType === 'CORP' ? 'MEM002' : dto.memType; conditions.push(eq(tnMember.memType, memTypeValue)); } // 기업명 조건 if (dto.agcCorporateName) { conditions.push( like(tnAgency.agcCorporateName, `%${dto.agcCorporateName}%`), ); } //* 기업관리자(corp) 스코프 — 자기 기업 회원머니만 if (dto.agcId) { conditions.push(eq(tnMember.agcId, dto.agcId)); } //* 조직관리자 부분트리 스코프 — 회원 소속 org가 관리 org_path 하위인 것만. if (dto.orgPath) { conditions.push( sql`EXISTS (SELECT 1 FROM tn_agency_organization o WHERE o.org_id = ${tnMember.orgId} AND o.org_path LIKE ${dto.orgPath + '%'})`, ); } // searchKey + searchText 패턴 (CODE_STYLE_GUIDE 준수) if (dto.searchKey && dto.searchText) { const searchText = `%${dto.searchText}%`; switch (dto.searchKey) { case 'memLoginId': conditions.push(like(tnMember.memLoginId, searchText)); break; case 'memName': conditions.push( sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${searchText} COLLATE utf8mb4_general_ci`, ); break; case 'description': conditions.push(like(thMoneyWallet.mwlDescription, searchText)); break; case 'reason': // 적립(mniReason) 또는 지급(mngReason) 사유 검색 conditions.push( or( like(tnMoneyGrant.mngReason, searchText), like(tnMoneyIssue.mniReason, searchText), )!, ); break; } } const where = conditions.length > 0 ? and(...conditions) : undefined; const result = await this.drizzle .select({ count: count() }) .from(thMoneyWallet) .leftJoin(tnMember, eq(thMoneyWallet.memId, tnMember.memId)) .leftJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId)) .leftJoin(tnSalesAgency, eq(tnMember.sagCode, tnSalesAgency.sagCode)) .leftJoin( tnSalesAgencyChannel, eq(tnMember.sacCode, tnSalesAgencyChannel.sacCode), ) .leftJoin(tnMoneySource, eq(thMoneyWallet.mwlId, tnMoneySource.mwlId)) .leftJoin(tnMoneyGrant, eq(tnMoneySource.mngId, tnMoneyGrant.mngId)) .leftJoin(tnMoneyIssue, eq(tnMoneySource.mniId, tnMoneyIssue.mniId)) .where(where); return result[0]?.count || 0; } async getWalletListByMemId( memId: number, where: SQL | undefined, page: number, limit: number = 10, ) { const offset = (+page - 1) * limit; const baseWhere = eq(thMoneyWallet.memId, memId); const finalWhere = where ? and(baseWhere, where) : baseWhere; const query = this.drizzle .select({ mwlId: thMoneyWallet.mwlId, memId: thMoneyWallet.memId, mwlType: thMoneyWallet.mwlType, mwlAmount: thMoneyWallet.mwlAmount, mwlBalance: thMoneyWallet.mwlBalance, mwlDescription: thMoneyWallet.mwlDescription, mwlCreatedAt: thMoneyWallet.mwlCreatedAt, msrExpiredAt: tnMoneySource.msrExpiredAt, mwlReason: sql`COALESCE(${tnMoneyGrant.mngReason}, ${tnMoneyIssue.mniReason})`, // 결제 정보 (충전건) mpyOrderNumber: tnMoneyPayment.mpyOrderNumber, mpyTid: tnMoneyPayment.mpyTid, mpyApprovedAt: tnMoneyPayment.mpyApprovedAt, }) .from(thMoneyWallet) .leftJoin(tnMoneySource, eq(thMoneyWallet.mwlId, tnMoneySource.mwlId)) .leftJoin(tnMoneyGrant, eq(tnMoneySource.mngId, tnMoneyGrant.mngId)) .leftJoin(tnMoneyIssue, eq(tnMoneySource.mniId, tnMoneyIssue.mniId)) .leftJoin(tnMoneyPayment, eq(tnMoneySource.mpyId, tnMoneyPayment.mpyId)) .where(finalWhere) .orderBy(desc(thMoneyWallet.mwlId)) .$dynamic(); if (page === 0) { return await query; } return await query.limit(limit).offset(offset); } async getWalletCountByMemId(memId: number, where: SQL | undefined) { const baseWhere = eq(thMoneyWallet.memId, memId); const finalWhere = where ? and(baseWhere, where) : baseWhere; const result = await this.drizzle .select({ count: count() }) .from(thMoneyWallet) .leftJoin(tnMoneySource, eq(thMoneyWallet.mwlId, tnMoneySource.mwlId)) .leftJoin(tnMoneyGrant, eq(tnMoneySource.mngId, tnMoneyGrant.mngId)) .leftJoin(tnMoneyIssue, eq(tnMoneySource.mniId, tnMoneyIssue.mniId)) .where(finalWhere); return result[0]?.count || 0; } async getWalletById(mwlId: number, db: MoneyDb = this.drizzle) { const [result] = await db .select() .from(thMoneyWallet) .where(eq(thMoneyWallet.mwlId, mwlId)); return result; } async createWallet( data: typeof thMoneyWallet.$inferInsert, db: MoneyDb = this.drizzle, ) { const result = await db.insert(thMoneyWallet).values(data).$returningId(); return result[0]?.mwlId; } /** * wallet 타입 및 설명 업데이트 (환불승인 시 REFUND_REQ → REFUND_OK) */ async updateWalletType( mwlId: number, mwlType: string, mwlDescription?: string, db: MoneyDb = this.drizzle, ) { const updateData: any = { mwlType }; if (mwlDescription !== undefined) { updateData.mwlDescription = mwlDescription; } await db .update(thMoneyWallet) .set(updateData) .where(eq(thMoneyWallet.mwlId, mwlId)); } // ============================================================================ // FIFO 원천 (tn_money_source) // ============================================================================ /** * FIFO 차감 순서로 source 목록 조회 * 1. 유효기간 있는 EARN/GRANT (만료 임박 순) * 2. 유효기간 없는 EARN/GRANT (생성일 순) * 3. CHARGE (생성일 순) */ async getSourcesForDeduct( memId: number, db: MoneyDb = this.drizzle, excludeExpired: boolean = false, ) { return await db .select() .from(tnMoneySource) .where( and( eq(tnMoneySource.memId, memId), gt(tnMoneySource.msrRemainingAmount, 0), NOT_RESERVED_FOR_CANCEL, //* 사용 차감만 만료 원천을 뺀다. 소멸 대상 조회 (`getExpiredSources`) 의 `<= NOW()` 의 여집합이다. excludeExpired ? or( isNull(tnMoneySource.msrExpiredAt), sql`${tnMoneySource.msrExpiredAt} > NOW()`, ) : undefined, ), ) .orderBy( // 1순위: EARN/GRANT 우선 (CHARGE는 나중) sql`CASE WHEN ${tnMoneySource.msrType} IN ('EARN', 'GRANT') THEN 0 ELSE 1 END`, // 2순위: 유효기간 임박 순 (NULL은 맨 뒤) sql`CASE WHEN ${tnMoneySource.msrExpiredAt} IS NULL THEN '9999-12-31' ELSE ${tnMoneySource.msrExpiredAt} END`, // 3순위: 생성일 순 asc(tnMoneySource.msrCreatedAt), ) .for('update'); } /** * 환불 대상 source 조회 (PURCHASE만, FIFO 순). 잠금 읽기다 (호출자는 회원 잠금 뒤의 트랜잭션). */ async getSourcesForRefund(memId: number, db: MoneyDb = this.drizzle) { return await db .select() .from(tnMoneySource) .where( and( eq(tnMoneySource.memId, memId), eq(tnMoneySource.msrMoneyType, 'PURCHASE'), gt(tnMoneySource.msrRemainingAmount, 0), NOT_RESERVED_FOR_CANCEL, ), ) .orderBy(asc(tnMoneySource.msrCreatedAt)) .for('update'); } /** * 결제 ID로 source 조회 */ async getSourceByPaymentId(mpyId: number) { const result = await this.drizzle .select() .from(tnMoneySource) .where(eq(tnMoneySource.mpyId, mpyId)) .limit(1); return result[0] || null; } /** * source ID로 조회 */ async getSourceById(msrId: number) { const result = await this.drizzle .select() .from(tnMoneySource) .where(eq(tnMoneySource.msrId, msrId)) .limit(1); return result[0] || null; } /** * source ID로 잠금 조회 (`FOR UPDATE`). 환불 · 복구 트랜잭션이 회원 잠금 뒤에 쓴다. */ async getSourceByIdForUpdate(msrId: number, db: MoneyDb = this.drizzle) { const result = await db .select() .from(tnMoneySource) .where(eq(tnMoneySource.msrId, msrId)) .limit(1) .for('update'); return result[0] || null; } /** * 회원의 만료 원천 잠금 조회 (소멸 트랜잭션용). 대상은 `getExpiredSources` 와 같다. */ async getExpiredSourcesByMemberForUpdate( memId: number, db: MoneyDb = this.drizzle, ) { return await db .select() .from(tnMoneySource) .where( and( eq(tnMoneySource.memId, memId), sql`${tnMoneySource.msrType} IN ('EARN', 'GRANT')`, isNotNull(tnMoneySource.msrExpiredAt), lte(tnMoneySource.msrExpiredAt, sql`NOW()`), gt(tnMoneySource.msrRemainingAmount, 0), ), ) .for('update'); } /** * 회원의 잔여 원천 전부 잠금 조회 (만료 여부와 무관). 칸 일치 검사의 입력이다. */ async getSourcesByMemberForUpdate(memId: number, db: MoneyDb = this.drizzle) { return await db .select() .from(tnMoneySource) .where( and( eq(tnMoneySource.memId, memId), gt(tnMoneySource.msrRemainingAmount, 0), ), ) .for('update'); } async createSource( data: typeof tnMoneySource.$inferInsert, db: MoneyDb = this.drizzle, ) { const result = await db.insert(tnMoneySource).values(data).$returningId(); return result[0]?.msrId; } async updateSourceRemaining( msrId: number, remainingAmount: number, db: MoneyDb = this.drizzle, ) { await db .update(tnMoneySource) .set({ msrRemainingAmount: remainingAmount }) .where(eq(tnMoneySource.msrId, msrId)); } /** * 만료된 source 목록 조회 (스케줄러용) * - 성능 최적화: EARN/GRANT 타입만 조회 (CHARGE는 유효기간 없음) */ async getExpiredSources() { return await this.drizzle .select() .from(tnMoneySource) .where( and( // EARN, GRANT만 조회 (CHARGE는 msrExpiredAt이 NULL이므로 제외) sql`${tnMoneySource.msrType} IN ('EARN', 'GRANT')`, isNotNull(tnMoneySource.msrExpiredAt), lte(tnMoneySource.msrExpiredAt, sql`NOW()`), gt(tnMoneySource.msrRemainingAmount, 0), ), ); } // ============================================================================ // 사용 상세 (th_money_usage) // ============================================================================ async createUsage( data: typeof thMoneyUsage.$inferInsert, db: MoneyDb = this.drizzle, ) { await db.insert(thMoneyUsage).values(data); } async createUsageBatch(usages: (typeof thMoneyUsage.$inferInsert)[]) { if (usages.length === 0) return; await this.drizzle.insert(thMoneyUsage).values(usages); } /** * 환불신청 wallet의 usage 목록 조회 (복구용) */ async getUsagesByWalletId(mwlId: number, db: MoneyDb = this.drizzle) { return await db .select() .from(thMoneyUsage) .where(eq(thMoneyUsage.mwlId, mwlId)); } /** * 구독 포인트 적립 건 조회 (결제취소 시 회수용) * - 결제 승인 시간 전후 5분 이내 * - 해당 회원의 EARN 타입 * - "구독 포인트 적립" 포함하는 description */ async findSubscriptionEarnWallet( memId: number, approvedAt: string, db: MoneyDb = this.drizzle, ) { return await db .select({ mwlId: thMoneyWallet.mwlId, mwlAmount: thMoneyWallet.mwlAmount, mwlDescription: thMoneyWallet.mwlDescription, msrId: tnMoneySource.msrId, msrRemainingAmount: tnMoneySource.msrRemainingAmount, }) .from(thMoneyWallet) .innerJoin(tnMoneySource, eq(tnMoneySource.mwlId, thMoneyWallet.mwlId)) .where( and( eq(thMoneyWallet.memId, memId), eq(thMoneyWallet.mwlType, 'EARN'), like(thMoneyWallet.mwlDescription, '%구독 포인트 적립%'), gte( thMoneyWallet.mwlCreatedAt, sql`DATE_SUB(${approvedAt}, INTERVAL 5 MINUTE)`, ), lte( thMoneyWallet.mwlCreatedAt, sql`DATE_ADD(${approvedAt}, INTERVAL 5 MINUTE)`, ), gt(tnMoneySource.msrRemainingAmount, 0), // 아직 사용되지 않은 잔액만 ), ) .limit(1) .for('update'); } /** * 결제 이용내역 조회 (source → usage → wallet) */ async getPaymentUsageHistory(mpyId: number) { const source = await this.getSourceByPaymentId(mpyId); if (!source) return []; return await this.drizzle .select({ musId: thMoneyUsage.musId, mwlType: thMoneyWallet.mwlType, mwlDescription: thMoneyWallet.mwlDescription, musAmount: thMoneyUsage.musAmount, mwlCreatedAt: thMoneyWallet.mwlCreatedAt, }) .from(thMoneyUsage) .leftJoin(thMoneyWallet, eq(thMoneyUsage.mwlId, thMoneyWallet.mwlId)) .where(eq(thMoneyUsage.msrId, source.msrId)) .orderBy(desc(thMoneyWallet.mwlCreatedAt)); } // ============================================================================ // 회원 관련 // ============================================================================ async getMemberById(memId: number, db: MoneyDb = this.drizzle) { const [result] = await db .select({ memId: tnMember.memId, memMoney: tnMember.memMoney, memPurchaseMoney: tnMember.memPurchaseMoney, memAgencyMoney: tnMember.memAgencyMoney, agcId: tnMember.agcId, memName: sql`CONVERT(AES_DECRYPT(FROM_BASE64(${tnMember.memName}), ${this.key}) USING UTF8)`, memPhone: sql`CONVERT(AES_DECRYPT(FROM_BASE64(${tnMember.memPhone}), ${this.key}) USING UTF8)`, memEmail: sql`CONVERT(AES_DECRYPT(FROM_BASE64(${tnMember.memEmail}), ${this.key}) USING UTF8)`, }) .from(tnMember) .where(eq(tnMember.memId, memId)) .limit(1); return result; } /** * 회원 행 잠금 읽기. 칸 세 개와 `agcId` 만 `FOR UPDATE` 로 읽는다. * * 복호화 열 (이름 · 전화 · 이메일) 을 넣지 않는다. 키가 든 식은 질의 문장에 그대로 실리고, 잠금 * 대기 초과 (1205) · 교착 (1213) 의 오류 객체 `sql` 속성으로 로그에 남기 때문이다 (M12 보안 점검 HIGH). * 잠금 경로의 호출자는 이름 · 전화 · 이메일을 쓰지 않는다. */ async getMemberByIdForUpdate(memId: number, db: MoneyDb = this.drizzle) { const [result] = await db .select({ memId: tnMember.memId, memMoney: tnMember.memMoney, memPurchaseMoney: tnMember.memPurchaseMoney, memAgencyMoney: tnMember.memAgencyMoney, agcId: tnMember.agcId, }) .from(tnMember) .where(eq(tnMember.memId, memId)) .limit(1) .for('update'); return result; } async getMemberByLoginId(memLoginId: string) { const result = await this.drizzle .select({ memId: tnMember.memId, memMoney: tnMember.memMoney, memPurchaseMoney: tnMember.memPurchaseMoney, memAgencyMoney: tnMember.memAgencyMoney, agcId: tnMember.agcId, //* 업로드 미리보기 표시용 memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, agcName: tnAgency.agcCorporateName, }) .from(tnMember) .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId)) .where(eq(tnMember.memLoginId, memLoginId)) .limit(1); return result[0] || null; } async getMemberByLoginIdAndAgcId(memLoginId: string, agcId: number) { const result = await this.drizzle .select({ memId: tnMember.memId, memMoney: tnMember.memMoney, memPurchaseMoney: tnMember.memPurchaseMoney, memAgencyMoney: tnMember.memAgencyMoney, agcId: tnMember.agcId, }) .from(tnMember) .where( and(eq(tnMember.memLoginId, memLoginId), eq(tnMember.agcId, agcId)), ) .limit(1); return result[0] || null; } /** * 회원아이디 + 기업코드로 회원 조회. * - 동일 회원아이디가 여러 기업에 존재할 수 있어, 기업코드(agc_corporate_code, unique)로 해당 기업의 회원만 특정. * - 회원머니 엑셀업로드에서 오배정(다른 기업 회원에게 적립) 방지용. */ async getMemberByLoginIdAndAgcCode(memLoginId: string, agcCode: string) { const result = await this.drizzle .select({ memId: tnMember.memId, memMoney: tnMember.memMoney, memPurchaseMoney: tnMember.memPurchaseMoney, memAgencyMoney: tnMember.memAgencyMoney, agcId: tnMember.agcId, //* 업로드 미리보기 표시용 memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, agcName: tnAgency.agcCorporateName, }) .from(tnMember) .innerJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId)) .where( and( eq(tnMember.memLoginId, memLoginId), eq(tnAgency.agcCorporateCode, agcCode), ), ) .limit(1); return result[0] || null; } async updateMemberMoney( memId: number, memMoney: number, memPurchaseMoney: number, memAgencyMoney: number, db: MoneyDb = this.drizzle, ) { await db .update(tnMember) .set({ memMoney, memPurchaseMoney, memAgencyMoney, }) .where(eq(tnMember.memId, memId)); } async isMemberInAgencyScope( memId: number, agcId: number, orgPath?: string, ): Promise { const conditions: SQL[] = [ eq(tnMember.memId, memId), 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({ memId: tnMember.memId }) .from(tnMember) .where(and(...conditions)) .limit(1); return !!row; } async getMembersByAgcId( agcId: number, isSubscribed?: boolean, orgPath?: string, ) { const conditions: SQL[] = [eq(tnMember.agcId, agcId)]; if (isSubscribed) { conditions.push( sql`EXISTS ( SELECT 1 FROM tn_subscribe s WHERE s.mem_id = ${tnMember.memId} AND s.sub_id = ( SELECT MAX(s2.sub_id) FROM tn_subscribe s2 WHERE s2.mem_id = ${tnMember.memId} ) AND s.sub_state = 'SUB001' )`, ); } 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 where = and(...conditions); return await this.drizzle .select({ memId: tnMember.memId }) .from(tnMember) .where(where) .orderBy(asc(tnMember.memId)); } async getAllMembers(isSubscribed?: boolean) { const conditions: SQL[] = []; if (isSubscribed) { conditions.push( sql`EXISTS ( SELECT 1 FROM tn_subscribe s WHERE s.mem_id = ${tnMember.memId} AND s.sub_id = ( SELECT MAX(s2.sub_id) FROM tn_subscribe s2 WHERE s2.mem_id = ${tnMember.memId} ) AND s.sub_state = 'SUB001' )`, ); } return await this.drizzle .select({ memId: tnMember.memId }) .from(tnMember) .where(conditions.length > 0 ? and(...conditions) : undefined) .orderBy(asc(tnMember.memId)); } async getAgencyMemberReferenceRows(agcId: number, orgPath?: string) { const conditions: SQL[] = [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 + '%'})`, ); } return await this.drizzle .select({ memLoginId: tnMember.memLoginId, memName: sql`FN_DECRYPT(${tnMember.memName}, ${this.key})`, orgCode: tnAgencyOrganization.orgCode, orgName: tnAgencyOrganization.orgName, memAgencyMoney: tnMember.memAgencyMoney, }) .from(tnMember) .leftJoin( tnAgencyOrganization, eq(tnAgencyOrganization.orgId, tnMember.orgId), ) .where(and(...conditions)) .orderBy(asc(tnMember.memLoginId)); } // ============================================================================ // 기업 관련 // ============================================================================ async getAgencyById(agcId: number, db: MoneyDb = this.drizzle) { const result = await db .select() .from(tnAgency) .where(eq(tnAgency.agcId, agcId)) .limit(1); return result[0] || null; } async getAgencyByIdForUpdate(agcId: number, db: MoneyDb = this.drizzle) { await db.execute( sql`SELECT agc_id FROM tn_agency WHERE agc_id = ${agcId} FOR UPDATE`, ); return await this.getAgencyById(agcId, db); } async updateAgencyMoney( agcId: number, agcMoney: number, db: MoneyDb = this.drizzle, ) { await db .update(tnAgency) .set({ agcMoney }) .where(eq(tnAgency.agcId, agcId)); } // ============================================================================ // 트랜잭션 지원 // ============================================================================ /** * 트랜잭션 실행 */ async runInTransaction( callback: (tx: typeof this.drizzle) => Promise, ): Promise { return await this.drizzle.transaction(async (tx) => { return await callback(tx as typeof this.drizzle); }); } }