diff options
Diffstat (limited to 'lib/esg-check-list/service.ts')
| -rw-r--r-- | lib/esg-check-list/service.ts | 601 |
1 files changed, 601 insertions, 0 deletions
diff --git a/lib/esg-check-list/service.ts b/lib/esg-check-list/service.ts new file mode 100644 index 00000000..500cd82c --- /dev/null +++ b/lib/esg-check-list/service.ts @@ -0,0 +1,601 @@ +'use server' + +import { and, asc, desc, ilike, or } from 'drizzle-orm'; +import db from '@/db/db'; +import { filterColumns } from "@/lib/filter-columns"; + + +import { + esgEvaluations, + esgEvaluationItems, + esgAnswerOptions, + NewEsgEvaluation, + NewEsgEvaluationItem, + NewEsgAnswerOption, + EsgEvaluationWithItems, + esgEvaluationsView +} from '@/db/schema'; +import { eq } from 'drizzle-orm'; +import { GetEsgEvaluationsSchema } from './validation'; +import { countEsgEvaluations, getEsgEvaluationWithDetails, selectEsgEvaluations } from './repository'; + +// ============ 조회 함수들 ============ + +export async function getEsgEvaluations(input: GetEsgEvaluationsSchema) { + try { + const offset = (input.page - 1) * input.perPage; + + // 고급 필터링 + const advancedWhere = filterColumns({ + table: esgEvaluationsView, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // 전역 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(esgEvaluationsView.serialNumber, s), + ilike(esgEvaluationsView.category, s), + ilike(esgEvaluationsView.inspectionItem, s) + ); + } + + const finalWhere = and(advancedWhere, globalWhere); + + // 정렬 + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + return item.desc + ? desc(esgEvaluationsView[item.id]) + : asc(esgEvaluationsView[item.id]); + }) + : [desc(esgEvaluationsView.createdAt)]; + + // 데이터 조회 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectEsgEvaluations(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }); + + const total = await countEsgEvaluations(tx, finalWhere); + return { data, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + return { data, pageCount }; + } catch (err) { + console.error('Error in getEsgEvaluations:', err); + return { data: [], pageCount: 0 }; + } +} + +// 단일 평가표 상세 조회 (평가항목과 답변 옵션 포함) +export async function getEsgEvaluationDetails(id: number) { + try { + return await db.transaction(async (tx) => { + return await getEsgEvaluationWithDetails(tx, id); + }); + } catch (err) { + console.error('Error in getEsgEvaluationDetails:', err); + return null; + } +} + +// ============ 생성 함수들 ============ + +export async function createEsgEvaluation(data: NewEsgEvaluation) { + try { + return await db.transaction(async (tx) => { + const [result] = await tx + .insert(esgEvaluations) + .values(data) + .returning(); + return result; + }); + } catch (err) { + console.error('Error creating ESG evaluation:', err); + throw new Error('Failed to create ESG evaluation'); + } +} + +export async function createEsgEvaluationWithItems( + evaluationData: NewEsgEvaluation, + items: Array<{ + evaluationItem: string; + orderIndex?: number; + answerOptions: Array<{ + answerText: string; + score: number; + orderIndex?: number; + }>; + }> +) { + try { + return await db.transaction(async (tx) => { + // 1. 평가표 생성 + const [evaluation] = await tx + .insert(esgEvaluations) + .values(evaluationData) + .returning(); + + // 2. 평가항목들 생성 + for (let i = 0; i < items.length; i++) { + const item = items[i]; + const [evaluationItem] = await tx + .insert(esgEvaluationItems) + .values({ + esgEvaluationId: evaluation.id, + evaluationItem: item.evaluationItem, + orderIndex: item.orderIndex ?? i, + }) + .returning(); + + // 3. 답변 옵션들 생성 + if (item.answerOptions.length > 0) { + await tx.insert(esgAnswerOptions).values( + item.answerOptions.map((option, optionIndex) => ({ + esgEvaluationItemId: evaluationItem.id, + answerText: option.answerText, + score: option.score.toString(), + orderIndex: option.orderIndex ?? optionIndex, + })) + ); + } + } + + return evaluation; + }); + } catch (err) { + console.error('Error creating ESG evaluation with items:', err); + throw new Error('Failed to create ESG evaluation with items'); + } +} + +// ============ 수정 함수들 ============ + +export async function updateEsgEvaluation( + id: number, + data: Partial<NewEsgEvaluation> +) { + try { + return await db.transaction(async (tx) => { + const [result] = await tx + .update(esgEvaluations) + .set({ ...data, updatedAt: new Date() }) + .where(eq(esgEvaluations.id, id)) + .returning(); + return result; + }); + } catch (err) { + console.error('Error updating ESG evaluation:', err); + throw new Error('Failed to update ESG evaluation'); + } +} + +export async function updateEsgEvaluationItem( + id: number, + data: Partial<NewEsgEvaluationItem> +) { + try { + return await db.transaction(async (tx) => { + const [result] = await tx + .update(esgEvaluationItems) + .set({ ...data, updatedAt: new Date() }) + .where(eq(esgEvaluationItems.id, id)) + .returning(); + return result; + }); + } catch (err) { + console.error('Error updating ESG evaluation item:', err); + throw new Error('Failed to update ESG evaluation item'); + } +} + +export async function updateEsgAnswerOption( + id: number, + data: Partial<NewEsgAnswerOption> +) { + try { + return await db.transaction(async (tx) => { + const [result] = await tx + .update(esgAnswerOptions) + .set({ ...data, updatedAt: new Date() }) + .where(eq(esgAnswerOptions.id, id)) + .returning(); + return result; + }); + } catch (err) { + console.error('Error updating ESG answer option:', err); + throw new Error('Failed to update ESG answer option'); + } +} + +// ============ 삭제 함수들 ============ + +export async function deleteEsgEvaluation(id: number) { + try { + return await db.transaction(async (tx) => { + // Cascade delete가 설정되어 있어서 평가항목과 답변옵션들도 자동 삭제됨 + const [result] = await tx + .delete(esgEvaluations) + .where(eq(esgEvaluations.id, id)) + .returning(); + return result; + }); + } catch (err) { + console.error('Error deleting ESG evaluation:', err); + throw new Error('Failed to delete ESG evaluation'); + } +} + +export async function deleteEsgEvaluationItem(id: number) { + try { + return await db.transaction(async (tx) => { + // Cascade delete가 설정되어 있어서 답변옵션들도 자동 삭제됨 + const [result] = await tx + .delete(esgEvaluationItems) + .where(eq(esgEvaluationItems.id, id)) + .returning(); + return result; + }); + } catch (err) { + console.error('Error deleting ESG evaluation item:', err); + throw new Error('Failed to delete ESG evaluation item'); + } +} + +export async function deleteEsgAnswerOption(id: number) { + try { + return await db.transaction(async (tx) => { + const [result] = await tx + .delete(esgAnswerOptions) + .where(eq(esgAnswerOptions.id, id)) + .returning(); + return result; + }); + } catch (err) { + console.error('Error deleting ESG answer option:', err); + throw new Error('Failed to delete ESG answer option'); + } +} + +// ============ 소프트 삭제 함수들 ============ + +export async function softDeleteEsgEvaluation(id: number) { + return updateEsgEvaluation(id, { isActive: false }); +} + +export async function softDeleteEsgEvaluationItem(id: number) { + return updateEsgEvaluationItem(id, { isActive: false }); +} + +export async function softDeleteEsgAnswerOption(id: number) { + return updateEsgAnswerOption(id, { isActive: false }); +} + + + +export async function updateEsgEvaluationWithItems( + id: number, + evaluationData: { + serialNumber: string; + category: string; + inspectionItem: string; + }, + items: Array<{ + evaluationItem: string; + evaluationItemDescription: string; + answerOptions: Array<{ + answerText: string; + score: number; + }>; + }> +) { + try { + return await db.transaction(async (tx) => { + // 1. 기본 정보 수정 + const [updatedEvaluation] = await tx + .update(esgEvaluations) + .set({ + ...evaluationData, + updatedAt: new Date(), + }) + .where(eq(esgEvaluations.id, id)) + .returning(); + + // 2. 기존 평가항목들과 답변 옵션들 모두 삭제 (cascade delete로 답변옵션도 함께 삭제됨) + await tx + .delete(esgEvaluationItems) + .where(eq(esgEvaluationItems.esgEvaluationId, id)); + + // 3. 새로운 평가항목들과 답변 옵션들 생성 + for (let i = 0; i < items.length; i++) { + const item = items[i]; + + const [evaluationItem] = await tx + .insert(esgEvaluationItems) + .values({ + esgEvaluationId: id, + evaluationItem: item.evaluationItem, + orderIndex: i, + }) + .returning(); + + // 답변 옵션들 생성 + if (item.answerOptions.length > 0) { + await tx.insert(esgAnswerOptions).values( + item.answerOptions.map((option, optionIndex) => ({ + esgEvaluationItemId: evaluationItem.id, + answerText: option.answerText, + score: option.score.toString(), + orderIndex: optionIndex, + })) + ); + } + } + + return updatedEvaluation; + }); + } catch (err) { + console.error('Error updating ESG evaluation with items:', err); + + // 시리얼 번호 중복 에러 처리 + if (err instanceof Error && err.message.includes('unique')) { + throw new Error('이미 존재하는 시리얼번호입니다.'); + } + + throw new Error('평가표 수정에 실패했습니다.'); + } +} + +// ============ 소프트 삭제 버전 (데이터 보존) ============ + +export async function updateEsgEvaluationWithItemsSoft( + id: number, + evaluationData: { + serialNumber: string; + category: string; + inspectionItem: string; + }, + items: Array<{ + evaluationItem: string; + answerOptions: Array<{ + answerText: string; + score: number; + }>; + }> +) { + try { + return await db.transaction(async (tx) => { + // 1. 기본 정보 수정 + const [updatedEvaluation] = await tx + .update(esgEvaluations) + .set({ + ...evaluationData, + updatedAt: new Date(), + }) + .where(eq(esgEvaluations.id, id)) + .returning(); + + // 2. 기존 평가항목들 소프트 삭제 + await tx + .update(esgEvaluationItems) + .set({ isActive: false, updatedAt: new Date() }) + .where(eq(esgEvaluationItems.esgEvaluationId, id)); + + // 기존 답변 옵션들도 소프트 삭제 + const existingItems = await tx + .select({ id: esgEvaluationItems.id }) + .from(esgEvaluationItems) + .where(eq(esgEvaluationItems.esgEvaluationId, id)); + + for (const item of existingItems) { + await tx + .update(esgAnswerOptions) + .set({ isActive: false, updatedAt: new Date() }) + .where(eq(esgAnswerOptions.esgEvaluationItemId, item.id)); + } + + // 3. 새로운 평가항목들과 답변 옵션들 생성 + for (let i = 0; i < items.length; i++) { + const item = items[i]; + + const [evaluationItem] = await tx + .insert(esgEvaluationItems) + .values({ + esgEvaluationId: id, + evaluationItem: item.evaluationItem, + orderIndex: i, + }) + .returning(); + + // 답변 옵션들 생성 + if (item.answerOptions.length > 0) { + await tx.insert(esgAnswerOptions).values( + item.answerOptions.map((option, optionIndex) => ({ + esgEvaluationItemId: evaluationItem.id, + answerText: option.answerText, + score: option.score.toString(), + orderIndex: optionIndex, + })) + ); + } + } + + return updatedEvaluation; + }); + } catch (err) { + console.error('Error updating ESG evaluation with items (soft):', err); + + if (err instanceof Error && err.message.includes('unique')) { + throw new Error('이미 존재하는 시리얼번호입니다.'); + } + + throw new Error('평가표 수정에 실패했습니다.'); + } +} + +// ============ 생성 함수 개선 (에러 처리 추가) ============ + +export async function createEsgEvaluationWithItemsEnhanced( + evaluationData: { + serialNumber: string; + category: string; + inspectionItem: string; + }, + items: Array<{ + evaluationItem: string; + evaluationItemDescription: string; + answerOptions: Array<{ + answerText: string; + score: number; + }>; + }> +) { + try { + return await db.transaction(async (tx) => { + // 1. 평가표 생성 + const [evaluation] = await tx + .insert(esgEvaluations) + .values(evaluationData) + .returning(); + + // 2. 평가항목들과 답변 옵션들 생성 + for (let i = 0; i < items.length; i++) { + const item = items[i]; + + const [evaluationItem] = await tx + .insert(esgEvaluationItems) + .values({ + esgEvaluationId: evaluation.id, + evaluationItem: item.evaluationItem, + evaluationItemDescription: item.evaluationItemDescription, + orderIndex: i, + }) + .returning(); + + // 답변 옵션들 생성 + if (item.answerOptions.length > 0) { + await tx.insert(esgAnswerOptions).values( + item.answerOptions.map((option, optionIndex) => ({ + esgEvaluationItemId: evaluationItem.id, + answerText: option.answerText, + score: option.score.toString(), + orderIndex: optionIndex, + })) + ); + } + } + + return evaluation; + }); + } catch (err) { + console.error('Error creating ESG evaluation with items:', err); + + // 시리얼 번호 중복 에러 처리 + if (err instanceof Error && err.message.includes('unique')) { + throw new Error('이미 존재하는 시리얼번호입니다.'); + } + + throw new Error('평가표 생성에 실패했습니다.'); + } +} + +export async function deleteEsgEvaluationsBatch(ids: number[]) { + try { + if (ids.length === 0) { + throw new Error('삭제할 평가표가 없습니다.'); + } + + return await db.transaction(async (tx) => { + let deletedCount = 0; + + for (const id of ids) { + try { + // 각 평가표 삭제 (cascade delete로 관련 데이터도 함께 삭제됨) + await tx + .delete(esgEvaluations) + .where(eq(esgEvaluations.id, id)); + + deletedCount++; + } catch (error) { + console.error(`Error deleting evaluation ${id}:`, error); + // 개별 삭제 실패는 로그만 남기고 계속 진행 + } + } + + return { + total: ids.length, + deleted: deletedCount, + failed: ids.length - deletedCount + }; + }); + } catch (err) { + console.error('Error in batch delete ESG evaluations:', err); + throw new Error('평가표 일괄 삭제에 실패했습니다.'); + } +} + +export async function softDeleteEsgEvaluationsBatch(ids: number[]) { + try { + if (ids.length === 0) { + throw new Error('삭제할 평가표가 없습니다.'); + } + + return await db.transaction(async (tx) => { + let deletedCount = 0; + + for (const id of ids) { + try { + // 평가표 소프트 삭제 + await tx + .update(esgEvaluations) + .set({ + isActive: false, + updatedAt: new Date(), + }) + .where(eq(esgEvaluations.id, id)); + + // 관련 평가항목들 소프트 삭제 + await tx + .update(esgEvaluationItems) + .set({ isActive: false, updatedAt: new Date() }) + .where(eq(esgEvaluationItems.esgEvaluationId, id)); + + // 관련 답변 옵션들 소프트 삭제 + const evaluationItems = await tx + .select({ id: esgEvaluationItems.id }) + .from(esgEvaluationItems) + .where(eq(esgEvaluationItems.esgEvaluationId, id)); + + for (const item of evaluationItems) { + await tx + .update(esgAnswerOptions) + .set({ isActive: false, updatedAt: new Date() }) + .where(eq(esgAnswerOptions.esgEvaluationItemId, item.id)); + } + + deletedCount++; + } catch (error) { + console.error(`Error soft deleting evaluation ${id}:`, error); + // 개별 삭제 실패는 로그만 남기고 계속 진행 + } + } + + return { + total: ids.length, + deleted: deletedCount, + failed: ids.length - deletedCount + }; + }); + } catch (err) { + console.error('Error in batch soft delete ESG evaluations:', err); + throw new Error('평가표 일괄 삭제에 실패했습니다.'); + } +}
\ No newline at end of file |