diff options
Diffstat (limited to 'lib/vendor-evaluation-submit/service.ts')
| -rw-r--r-- | lib/vendor-evaluation-submit/service.ts | 885 |
1 files changed, 885 insertions, 0 deletions
diff --git a/lib/vendor-evaluation-submit/service.ts b/lib/vendor-evaluation-submit/service.ts new file mode 100644 index 00000000..5ab1206e --- /dev/null +++ b/lib/vendor-evaluation-submit/service.ts @@ -0,0 +1,885 @@ +'use server' + +import db from "@/db/db"; +import { + evaluationSubmissions, + vendors, + generalEvaluationResponses, + esgEvaluationResponses, + vendorEvaluationAttachments, + EvaluationSubmission, + GeneralEvaluationResponse, + NewGeneralEvaluationResponse, + generalEvaluations, + GeneralEvaluation, + EsgEvaluationItem, + EsgAnswerOption, + EsgEvaluationResponse, + esgEvaluations, + esgAnswerOptions, + esgEvaluationItems +} from "@/db/schema"; +import { and, asc, desc, eq, ilike, or, SQL, count , sql, avg} from "drizzle-orm"; +import { filterColumns } from "@/lib/filter-columns"; +import { GetEvaluationsSubmitSchema } from "./validation"; + +// 평가 제출 목록 조회용 뷰 타입 +export type EvaluationSubmissionWithVendor = EvaluationSubmission & { + vendor: { + id: number; + vendorCode: string; + vendorName: string; + countryCode: string; + contactEmail: string; + }; + _count: { + generalResponses: number; + esgResponses: number; + attachments: number; + }; +}; + +/** + * 평가 제출 목록을 조회합니다 + */ +export async function getEvaluationSubmissions(input: GetEvaluationsSubmitSchema) { + try { + const offset = (input.page - 1) * input.perPage; + + // 고급 필터링 + const advancedWhere = filterColumns({ + table: evaluationSubmissions, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // 전역 검색 + let globalWhere: SQL<unknown> | undefined; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(evaluationSubmissions.submissionId, s), + ilike(vendors.vendorName, s), + ilike(vendors.vendorCode, s), + ilike(evaluationSubmissions.submissionStatus, s), + ilike(evaluationSubmissions.evaluationRound, s) + ); + } + + const finalWhere = and( + advancedWhere, + globalWhere, + eq(evaluationSubmissions.isActive, true) + ); + + // 정렬 + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + return item.desc + ? desc(evaluationSubmissions[item.id]) + : asc(evaluationSubmissions[item.id]); + }) + : [desc(evaluationSubmissions.createdAt)]; + + // 데이터 조회 + const { data, total } = await db.transaction(async (tx) => { + // 메인 데이터 조회 + const data = await tx + .select({ + id: evaluationSubmissions.id, + submissionId: evaluationSubmissions.submissionId, + companyId: evaluationSubmissions.companyId, + evaluationYear: evaluationSubmissions.evaluationYear, + evaluationRound: evaluationSubmissions.evaluationRound, + submissionStatus: evaluationSubmissions.submissionStatus, + submittedAt: evaluationSubmissions.submittedAt, + reviewedAt: evaluationSubmissions.reviewedAt, + reviewedBy: evaluationSubmissions.reviewedBy, + reviewComments: evaluationSubmissions.reviewComments, + averageEsgScore: evaluationSubmissions.averageEsgScore, + totalGeneralItems: evaluationSubmissions.totalGeneralItems, + completedGeneralItems: evaluationSubmissions.completedGeneralItems, + totalEsgItems: evaluationSubmissions.totalEsgItems, + completedEsgItems: evaluationSubmissions.completedEsgItems, + isActive: evaluationSubmissions.isActive, + createdAt: evaluationSubmissions.createdAt, + updatedAt: evaluationSubmissions.updatedAt, + // Vendor 정보 + vendor: { + id: vendors.id, + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName, + countryCode: vendors.country, + contactEmail: vendors.email, + }, + }) + .from(evaluationSubmissions) + .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id)) + .where(finalWhere) + .orderBy(...orderBy) + .limit(input.perPage) + .offset(offset); + + // 각 제출에 대한 응답/첨부파일 수 조회 + const dataWithCounts = await Promise.all( + data.map(async (submission) => { + const [generalCount, esgCount, attachmentCount] = await Promise.all([ + tx + .select({ count: count() }) + .from(generalEvaluationResponses) + .where( + and( + eq(generalEvaluationResponses.submissionId, submission.id), + eq(generalEvaluationResponses.isActive, true) + ) + ) + .then(result => result[0]?.count || 0), + + tx + .select({ count: count() }) + .from(esgEvaluationResponses) + .where( + and( + eq(esgEvaluationResponses.submissionId, submission.id), + eq(esgEvaluationResponses.isActive, true) + ) + ) + .then(result => result[0]?.count || 0), + + tx + .select({ count: count() }) + .from(vendorEvaluationAttachments) + .where( + and( + eq(vendorEvaluationAttachments.submissionId, submission.id), + eq(vendorEvaluationAttachments.isActive, true) + ) + ) + .then(result => result[0]?.count || 0), + ]); + + return { + ...submission, + _count: { + generalResponses: generalCount, + esgResponses: esgCount, + attachments: attachmentCount, + }, + }; + }) + ); + + // 총 개수 조회 + const totalResult = await tx + .select({ count: count() }) + .from(evaluationSubmissions) + .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id)) + .where(finalWhere); + + const total = totalResult[0]?.count || 0; + + return { data: dataWithCounts, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + return { data, pageCount }; + } catch (err) { + console.error('Error in getEvaluationSubmissions:', err); + return { data: [], pageCount: 0 }; + } +} + +/** + * 특정 평가 제출의 상세 정보를 조회합니다 + */ +export async function getEvaluationSubmissionById(id: number) { + try { + const result = await db + .select() + .from(evaluationSubmissions) + .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id)) + .where( + and( + eq(evaluationSubmissions.id, id), + eq(evaluationSubmissions.isActive, true) + ) + ) + .limit(1); + + if (result.length === 0) { + return null; + } + + const submission = result[0]; + + // 응답 데이터도 함께 조회 + const [generalResponses, esgResponses, attachments] = await Promise.all([ + db + .select() + .from(generalEvaluationResponses) + .where( + and( + eq(generalEvaluationResponses.submissionId, id), + eq(generalEvaluationResponses.isActive, true) + ) + ), + + db + .select() + .from(esgEvaluationResponses) + .where( + and( + eq(esgEvaluationResponses.submissionId, id), + eq(esgEvaluationResponses.isActive, true) + ) + ), + + db + .select() + .from(vendorEvaluationAttachments) + .where( + and( + eq(vendorEvaluationAttachments.submissionId, id), + eq(vendorEvaluationAttachments.isActive, true) + ) + ), + ]); + + return { + ...submission, + generalResponses, + esgResponses, + attachments, + }; + } catch (err) { + console.error('Error in getEvaluationSubmissionById:', err); + return null; + } +} + + +/** + * 평가 제출의 완성도를 확인합니다 (간단 버전) + */ +export async function getEvaluationSubmissionCompleteness(submissionId: number) { + const result = await db.transaction(async (tx) => { + // 제출 정보 조회 + const submissionInfo = await tx + .select({ + submissionId: evaluationSubmissions.id, + countryCode: vendors.country, + averageEsgScore: evaluationSubmissions.averageEsgScore, + totalGeneralItems: evaluationSubmissions.totalGeneralItems, + completedGeneralItems: evaluationSubmissions.completedGeneralItems, + totalEsgItems: evaluationSubmissions.totalEsgItems, + completedEsgItems: evaluationSubmissions.completedEsgItems, + }) + .from(evaluationSubmissions) + .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id)) + .where(eq(evaluationSubmissions.id, submissionId)) + .limit(1); + + if (submissionInfo.length === 0) { + throw new Error("Submission not found"); + } + + const info = submissionInfo[0]; + const isKorean = info.countryCode === 'KR'; + + // 🔄 실제 평가 항목 수 조회 (전체 시스템에 등록된 평가 항목 수) + const [actualGeneralTotal, actualEsgTotal] = await Promise.all([ + // 활성화된 일반평가 항목 수 + tx + .select({ count: count() }) + .from(generalEvaluations) + .where(eq(generalEvaluations.isActive, true)) + .then(result => result[0]?.count || 0), + + // 활성화된 ESG 평가 항목 수 (한국 업체인 경우에만) + isKorean ? tx + .select({ count: count() }) + .from(esgEvaluationItems) + .innerJoin(esgEvaluations, eq(esgEvaluationItems.esgEvaluationId, esgEvaluations.id)) + .where( + and( + eq(esgEvaluationItems.isActive, true), + eq(esgEvaluations.isActive, true) + ) + ) + .then(result => result[0]?.count || 0) : Promise.resolve(0) + ]); + + // 실시간 완성도 계산 (실제 응답된 것만) + const [generalStats, esgStats] = await Promise.all([ + tx + .select({ + total: count(), + completed: sql<number>`COUNT(CASE WHEN ${generalEvaluationResponses.responseText} IS NOT NULL AND ${generalEvaluationResponses.responseText} != '' THEN 1 END)`, + }) + .from(generalEvaluationResponses) + .where( + and( + eq(generalEvaluationResponses.submissionId, submissionId), + eq(generalEvaluationResponses.isActive, true) + ) + ), + + isKorean ? tx + .select({ + total: count(), + completed: count(esgEvaluationResponses.selectedScore), + averageScore: avg(esgEvaluationResponses.selectedScore), + }) + .from(esgEvaluationResponses) + .where( + and( + eq(esgEvaluationResponses.submissionId, submissionId), + eq(esgEvaluationResponses.isActive, true) + ) + ) : Promise.resolve([{ total: 0, completed: 0, averageScore: null }]) + ]); + + // 실제 완료된 항목 수 + const generalCompleted = generalStats[0]?.completed || 0; + const esgCompleted = esgStats[0]?.completed || 0; + const esgAverage = parseFloat(esgStats[0]?.averageScore?.toString() || '0'); + + // 🎯 실제 평가 항목 수를 기준으로 완성도 계산 + return { + general: { + total: actualGeneralTotal, + completed: generalCompleted, + percentage: actualGeneralTotal > 0 ? (generalCompleted / actualGeneralTotal) * 100 : 0, + isComplete: actualGeneralTotal > 0 && generalCompleted === actualGeneralTotal, + }, + esg: { + total: actualEsgTotal, + completed: esgCompleted, + percentage: actualEsgTotal > 0 ? (esgCompleted / actualEsgTotal) * 100 : 0, + averageScore: esgAverage, + isComplete: actualEsgTotal === 0 || esgCompleted === actualEsgTotal, + }, + overall: { + isComplete: + (actualGeneralTotal > 0 && generalCompleted === actualGeneralTotal) && + (actualEsgTotal === 0 || esgCompleted === actualEsgTotal), + totalItems: actualGeneralTotal + actualEsgTotal, + completedItems: generalCompleted + esgCompleted, + }, + }; + }); + + return result; +} + +/** + * 평가 제출 상태를 업데이트합니다 (완성도 검증 포함) + */ +export async function updateEvaluationSubmissionStatus( + submissionId: number, + newStatus: string, + reviewData?: { + reviewedBy: string; + reviewComments?: string; + } +) { + return await db.transaction(async (tx) => { + // 제출 시에는 완성도 검증 + if (newStatus === 'submitted') { + const completeness = await getEvaluationSubmissionCompleteness(submissionId); + + if (!completeness.overall.isComplete) { + throw new Error( + `평가가 완료되지 않았습니다. ` + + `일반평가: ${completeness.general.completed}/${completeness.general.total}, ` + + `ESG평가: ${completeness.esg.completed}/${completeness.esg.total}` + ); + } + } + + // 상태 업데이트 + const updateData: any = { + submissionStatus: newStatus, + updatedAt: new Date(), + }; + + if (newStatus === 'submitted') { + updateData.submittedAt = new Date(); + } + + if (reviewData) { + updateData.reviewedAt = new Date(); + updateData.reviewedBy = reviewData.reviewedBy; + updateData.reviewComments = reviewData.reviewComments; + } + + const [updatedSubmission] = await tx + .update(evaluationSubmissions) + .set(updateData) + .where(eq(evaluationSubmissions.id, submissionId)) + .returning(); + + return updatedSubmission; + }); +} + +export type GeneralEvaluationFormData = { + submission: { + id: number; + submissionId: string; + vendorName: string; + submissionStatus: string; + }; + evaluations: Array<{ + evaluation: GeneralEvaluation; + response: GeneralEvaluationResponse | null; + attachments: Array<{ + id: number; + fileId: string; + originalFileName: string; + fileSize: number; + mimeType: string | null; + createdAt: Date; + }>; + }>; +}; + +/** + * 일반평가 폼 데이터를 조회하고, 응답 레코드가 없으면 생성합니다 + */ +export async function getGeneralEvaluationFormData(submissionId: number): Promise<GeneralEvaluationFormData> { + return await db.transaction(async (tx) => { + // 1. 제출 정보 조회 + const submissionResult = await tx + .select({ + id: evaluationSubmissions.id, + submissionId: evaluationSubmissions.submissionId, + vendorName: vendors.vendorName, + submissionStatus: evaluationSubmissions.submissionStatus, + }) + .from(evaluationSubmissions) + .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id)) + .where(eq(evaluationSubmissions.id, submissionId)) + .limit(1); + + if (submissionResult.length === 0) { + throw new Error("제출 정보를 찾을 수 없습니다."); + } + + const submission = submissionResult[0]; + + // 2. 활성화된 일반평가 항목들 조회 + const activeEvaluations = await tx + .select() + .from(generalEvaluations) + .where(eq(generalEvaluations.isActive, true)) + .orderBy(asc(generalEvaluations.serialNumber)); + + // 3. 기존 응답들 조회 + const existingResponses = await tx + .select() + .from(generalEvaluationResponses) + .where( + and( + eq(generalEvaluationResponses.submissionId, submissionId), + eq(generalEvaluationResponses.isActive, true) + ) + ); + + // 4. 응답이 없는 평가 항목들에 대해 빈 응답 레코드 생성 + const responseMap = new Map(existingResponses.map(r => [r.generalEvaluationId, r])); + const missingResponses: NewGeneralEvaluationResponse[] = []; + + for (const evaluation of activeEvaluations) { + if (!responseMap.has(evaluation.id)) { + missingResponses.push({ + submissionId, + generalEvaluationId: evaluation.id, + responseText: '', + hasAttachments: false, + }); + } + } + + // 5. 누락된 응답 레코드들 생성 + let newResponses: GeneralEvaluationResponse[] = []; + if (missingResponses.length > 0) { + newResponses = await tx + .insert(generalEvaluationResponses) + .values(missingResponses) + .returning(); + } + + // 6. 응답 맵 업데이트 + newResponses.forEach(response => { + responseMap.set(response.generalEvaluationId, response); + }); + + // 7. 각 응답의 첨부파일들 조회 + const evaluationData = await Promise.all( + activeEvaluations.map(async (evaluation) => { + const response = responseMap.get(evaluation.id) || null; + + let attachments: any[] = []; + if (response) { + attachments = await tx + .select({ + id: vendorEvaluationAttachments.id, + fileId: vendorEvaluationAttachments.fileId, + originalFileName: vendorEvaluationAttachments.originalFileName, + fileSize: vendorEvaluationAttachments.fileSize, + mimeType: vendorEvaluationAttachments.mimeType, + createdAt: vendorEvaluationAttachments.createdAt, + }) + .from(vendorEvaluationAttachments) + .where( + and( + eq(vendorEvaluationAttachments.generalEvaluationResponseId, response.id), + eq(vendorEvaluationAttachments.isActive, true) + ) + ) + .orderBy(desc(vendorEvaluationAttachments.createdAt)); + } + + return { + evaluation, + response, + attachments, + }; + }) + ); + + return { + submission, + evaluations: evaluationData, + }; + }); +} + +/** + * 일반평가 응답을 저장합니다 + */ +export async function saveGeneralEvaluationResponse(data: { + responseId: number; + responseText: string; + hasAttachments?: boolean; +}) { + try { + const [updatedResponse] = await db + .update(generalEvaluationResponses) + .set({ + responseText: data.responseText, + hasAttachments: data.hasAttachments || false, + updatedAt: new Date(), + }) + .where(eq(generalEvaluationResponses.id, data.responseId)) + .returning(); + + return updatedResponse; + } catch (error) { + console.error('Error saving general evaluation response:', error); + throw error; + } +} + +/** + * 평가 제출의 진행률과 ESG 평균점수를 계산합니다 + */ +export async function recalculateEvaluationProgress(submissionId: number) { + try { + return await db.transaction(async (tx) => { + // 1. 일반평가 진행률 계산 (점수 계산 제거) + const generalProgressResult = await tx + .select({ + totalItems: count(), + completedItems: sql<number>`COUNT(CASE WHEN ${generalEvaluationResponses.responseText} IS NOT NULL AND ${generalEvaluationResponses.responseText} != '' THEN 1 END)`, + }) + .from(generalEvaluationResponses) + .where( + and( + eq(generalEvaluationResponses.submissionId, submissionId), + eq(generalEvaluationResponses.isActive, true) + ) + ); + + const generalStats = generalProgressResult[0]; + const totalGeneralItems = generalStats.totalItems || 0; + const completedGeneralItems = generalStats.completedItems || 0; + + // 2. ESG 평가 평균 점수 계산 + const esgScoreResult = await tx + .select({ + averageScore: avg(esgEvaluationResponses.selectedScore), + totalItems: count(), + completedItems: count(esgEvaluationResponses.selectedScore), + }) + .from(esgEvaluationResponses) + .where( + and( + eq(esgEvaluationResponses.submissionId, submissionId), + eq(esgEvaluationResponses.isActive, true) + ) + ); + + const esgStats = esgScoreResult[0]; + const averageEsgScore = parseFloat(esgStats.averageScore?.toString() || '0'); + const totalEsgItems = esgStats.totalItems || 0; + const completedEsgItems = esgStats.completedItems || 0; + + // 3. submission 테이블 업데이트 + const [updatedSubmission] = await tx + .update(evaluationSubmissions) + .set({ + // ❌ averageGeneralScore 제거 + averageEsgScore: averageEsgScore > 0 ? averageEsgScore.toString() : null, + totalGeneralItems, + completedGeneralItems, + totalEsgItems, + completedEsgItems, + updatedAt: new Date(), + }) + .where(eq(evaluationSubmissions.id, submissionId)) + .returning(); + + return { + submission: updatedSubmission, + stats: { + general: { + total: totalGeneralItems, + completed: completedGeneralItems, + percentage: totalGeneralItems > 0 ? (completedGeneralItems / totalGeneralItems) * 100 : 0, + }, + esg: { + average: averageEsgScore, + total: totalEsgItems, + completed: completedEsgItems, + percentage: totalEsgItems > 0 ? (completedEsgItems / totalEsgItems) * 100 : 0, + }, + }, + }; + }); + } catch (error) { + console.error('Error recalculating evaluation progress:', error); + throw error; + } +} + + +// ================================================================ +// ESG평가 관련 서버 액션들 +// ================================================================ + +export type EsgEvaluationFormData = { + submission: { + id: number; + submissionId: string; + vendorName: string; + submissionStatus: string; + }; + evaluations: Array<{ + evaluation: { + id: number; + serialNumber: string; + category: string; + inspectionItem: string; + }; + items: Array<{ + item: EsgEvaluationItem; + answerOptions: EsgAnswerOption[]; + response: EsgEvaluationResponse | null; + }>; + }>; +}; + +/** + * ESG평가 폼 데이터를 조회합니다 (응답은 실시간 생성) + */ +export async function getEsgEvaluationFormData(submissionId: number): Promise<EsgEvaluationFormData> { + return await db.transaction(async (tx) => { + // 1. 제출 정보 조회 + const submissionResult = await tx + .select({ + id: evaluationSubmissions.id, + submissionId: evaluationSubmissions.submissionId, + vendorName: vendors.vendorName, + submissionStatus: evaluationSubmissions.submissionStatus, + }) + .from(evaluationSubmissions) + .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id)) + .where(eq(evaluationSubmissions.id, submissionId)) + .limit(1); + + if (submissionResult.length === 0) { + throw new Error("제출 정보를 찾을 수 없습니다."); + } + + const submission = submissionResult[0]; + + // 2. 활성화된 ESG 평가표들 조회 + const activeEsgEvaluations = await tx + .select({ + id: esgEvaluations.id, + serialNumber: esgEvaluations.serialNumber, + category: esgEvaluations.category, + inspectionItem: esgEvaluations.inspectionItem, + }) + .from(esgEvaluations) + .where(eq(esgEvaluations.isActive, true)) + .orderBy(asc(esgEvaluations.serialNumber)); + + // 3. 각 ESG 평가표의 항목들과 답변 옵션들 조회 + const evaluationData = await Promise.all( + activeEsgEvaluations.map(async (evaluation) => { + // 평가 항목들 조회 + const items = await tx + .select() + .from(esgEvaluationItems) + .where( + and( + eq(esgEvaluationItems.esgEvaluationId, evaluation.id), + eq(esgEvaluationItems.isActive, true) + ) + ) + .orderBy(asc(esgEvaluationItems.orderIndex)); + + // 각 항목의 답변 옵션들과 기존 응답 조회 + const itemsWithOptions = await Promise.all( + items.map(async (item) => { + // 답변 옵션들 조회 + const answerOptions = await tx + .select() + .from(esgAnswerOptions) + .where( + and( + eq(esgAnswerOptions.esgEvaluationItemId, item.id), + eq(esgAnswerOptions.isActive, true) + ) + ) + .orderBy(asc(esgAnswerOptions.orderIndex)); + + // 기존 응답 조회 + const existingResponse = await tx + .select() + .from(esgEvaluationResponses) + .where( + and( + eq(esgEvaluationResponses.submissionId, submissionId), + eq(esgEvaluationResponses.esgEvaluationItemId, item.id), + eq(esgEvaluationResponses.isActive, true) + ) + ) + .limit(1); + + return { + item, + answerOptions, + response: existingResponse[0] || null, + }; + }) + ); + + return { + evaluation, + items: itemsWithOptions, + }; + }) + ); + + return { + submission, + evaluations: evaluationData, + }; + }); +} + +/** + * ESG평가 응답을 저장합니다 + */ +export async function saveEsgEvaluationResponse(data: { + submissionId: number; + esgEvaluationItemId: number; + esgAnswerOptionId: number; + selectedScore: number; + additionalComments?: string; +}) { + try { + return await db.transaction(async (tx) => { + // 기존 응답이 있는지 확인 + const existingResponse = await tx + .select() + .from(esgEvaluationResponses) + .where( + and( + eq(esgEvaluationResponses.submissionId, data.submissionId), + eq(esgEvaluationResponses.esgEvaluationItemId, data.esgEvaluationItemId), + eq(esgEvaluationResponses.isActive, true) + ) + ) + .limit(1); + + if (existingResponse.length > 0) { + // 기존 응답 업데이트 + const [updatedResponse] = await tx + .update(esgEvaluationResponses) + .set({ + esgAnswerOptionId: data.esgAnswerOptionId, + selectedScore: data.selectedScore.toString(), + additionalComments: data.additionalComments || null, + updatedAt: new Date(), + }) + .where(eq(esgEvaluationResponses.id, existingResponse[0].id)) + .returning(); + + return updatedResponse; + } else { + // 새 응답 생성 + const [newResponse] = await tx + .insert(esgEvaluationResponses) + .values({ + submissionId: data.submissionId, + esgEvaluationItemId: data.esgEvaluationItemId, + esgAnswerOptionId: data.esgAnswerOptionId, + selectedScore: data.selectedScore.toString(), + additionalComments: data.additionalComments || null, + }) + .returning(); + + return newResponse; + } + }); + } catch (error) { + console.error('Error saving ESG evaluation response:', error); + throw error; + } +} + +export async function updateAttachmentStatus(responseId: number) { + try { + // 활성 첨부파일 개수 확인 + const attachmentCount = await db + .select({ count: vendorEvaluationAttachments.id }) + .from(vendorEvaluationAttachments) + .where( + and( + eq(vendorEvaluationAttachments.generalEvaluationResponseId, responseId), + eq(vendorEvaluationAttachments.isActive, true) + ) + ) + + const hasAttachments = attachmentCount.length > 0 + + // 응답 테이블의 hasAttachments 필드 업데이트 + await db + .update(generalEvaluationResponses) + .set({ + hasAttachments, + updatedAt: new Date() + }) + .where(eq(generalEvaluationResponses.id, responseId)) + + return { hasAttachments, count: attachmentCount.length } + } catch (error) { + console.error('Error updating attachment status:', error) + throw error + } +} |