diff options
Diffstat (limited to 'lib/evaluation-target-list/service.ts')
| -rw-r--r-- | lib/evaluation-target-list/service.ts | 395 |
1 files changed, 395 insertions, 0 deletions
diff --git a/lib/evaluation-target-list/service.ts b/lib/evaluation-target-list/service.ts new file mode 100644 index 00000000..62f0f0ef --- /dev/null +++ b/lib/evaluation-target-list/service.ts @@ -0,0 +1,395 @@ +'use server' + +import { and, or, desc, asc, ilike, eq, isNull, sql, count } from "drizzle-orm"; + +import { getFiltersStateParser, getSortingStateParser } from "@/lib/parsers"; +import { filterColumns } from "@/lib/filter-columns"; +import db from "@/db/db"; +import { + evaluationTargets, + evaluationTargetReviewers, + evaluationTargetReviews, + users, + vendors, + type EvaluationTargetStatus, + type Division, + type MaterialType, + type DomesticForeign, + EVALUATION_DEPARTMENT_CODES, + EvaluationTargetWithDepartments, + evaluationTargetsWithDepartments +} from "@/db/schema"; +import { GetEvaluationTargetsSchema } from "./validation"; +import { PgTransaction } from "drizzle-orm/pg-core"; + + +export async function selectEvaluationTargetsFromView( + tx: PgTransaction<any, any, any>, + params: { + where?: any; + orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]; + offset?: number; + limit?: number; + } +) { + const { where, orderBy, offset = 0, limit = 10 } = params; + + return tx + .select() + .from(evaluationTargetsWithDepartments) + .where(where) + .orderBy(...(orderBy ?? [])) + .offset(offset) + .limit(limit); +} + +/** 총 개수 count */ +export async function countEvaluationTargetsFromView( + tx: PgTransaction<any, any, any>, + where?: any +) { + const res = await tx + .select({ count: count() }) + .from(evaluationTargetsWithDepartments) + .where(where); + + return res[0]?.count ?? 0; +} + +// ============= 메인 서버 액션도 함께 수정 ============= + +export async function getEvaluationTargets(input: GetEvaluationTargetsSchema) { + try { + const offset = (input.page - 1) * input.perPage; + + // 고급 필터링 (View 테이블 기준) + const advancedWhere = filterColumns({ + table: evaluationTargetsWithDepartments, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // 베이직 필터링 (커스텀 필터) + let basicWhere; + if (input.basicFilters && input.basicFilters.length > 0) { + basicWhere = filterColumns({ + table: evaluationTargetsWithDepartments, + filters: input.basicFilters, + joinOperator: input.basicJoinOperator || "and", + }); + } + + // 전역 검색 (View 테이블 기준) + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(evaluationTargetsWithDepartments.vendorCode, s), + ilike(evaluationTargetsWithDepartments.vendorName, s), + ilike(evaluationTargetsWithDepartments.adminComment, s), + ilike(evaluationTargetsWithDepartments.consolidatedComment, s), + // 담당자 이름으로도 검색 가능 + ilike(evaluationTargetsWithDepartments.orderReviewerName, s), + ilike(evaluationTargetsWithDepartments.procurementReviewerName, s), + ilike(evaluationTargetsWithDepartments.qualityReviewerName, s), + ilike(evaluationTargetsWithDepartments.designReviewerName, s), + ilike(evaluationTargetsWithDepartments.csReviewerName, s) + ); + } + + const finalWhere = and(advancedWhere, basicWhere, globalWhere); + + // 정렬 (View 테이블 기준) + const orderBy = input.sort.length > 0 + ? input.sort.map((item) => { + const column = evaluationTargetsWithDepartments[item.id as keyof typeof evaluationTargetsWithDepartments]; + return item.desc ? desc(column) : asc(column); + }) + : [desc(evaluationTargetsWithDepartments.createdAt)]; + + // 데이터 조회 - View 테이블 사용 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectEvaluationTargetsFromView(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }); + + const total = await countEvaluationTargetsFromView(tx, finalWhere); + return { data, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + return { data, pageCount, total }; + } catch (err) { + console.error("Error in getEvaluationTargets:", err); + return { data: [], pageCount: 0 }; + } +} + +// ============= 개별 조회 함수도 업데이트 ============= + +export async function getEvaluationTargetById(id: number): Promise<EvaluationTargetWithDepartments | null> { + try { + const results = await db.transaction(async (tx) => { + return await selectEvaluationTargetsFromView(tx, { + where: eq(evaluationTargetsWithDepartments.id, id), + limit: 1, + }); + }); + + return results[0] || null; + } catch (err) { + console.error("Error in getEvaluationTargetById:", err); + return null; + } +} + +// 통계 조회도 View 기반으로 변경 +export async function getEvaluationTargetsStats(evaluationYear: number) { + try { + const stats = await db.transaction(async (tx) => { + const result = await tx + .select({ + total: count(), + pending: sql<number>`sum(case when status = 'PENDING' then 1 else 0 end)`, + confirmed: sql<number>`sum(case when status = 'CONFIRMED' then 1 else 0 end)`, + excluded: sql<number>`sum(case when status = 'EXCLUDED' then 1 else 0 end)`, + consensusTrue: sql<number>`sum(case when consensus_status = true then 1 else 0 end)`, + consensusFalse: sql<number>`sum(case when consensus_status = false then 1 else 0 end)`, + consensusNull: sql<number>`sum(case when consensus_status is null then 1 else 0 end)`, + oceanDivision: sql<number>`sum(case when division = 'OCEAN' then 1 else 0 end)`, + shipyardDivision: sql<number>`sum(case when division = 'SHIPYARD' then 1 else 0 end)`, + }) + .from(evaluationTargetsWithDepartments) + .where(eq(evaluationTargetsWithDepartments.evaluationYear, evaluationYear)); + + return result[0]; + }); + + return stats; + } catch (err) { + console.error("Error in getEvaluationTargetsStats:", err); + return null; + } +} + + +// ============= 수동 생성 관련 서버 액션 ============= + +// 평가 대상 수동 생성 인터페이스 +export interface CreateEvaluationTargetInput { + evaluationYear: number; + division: Division; + vendorId: number; + materialType: MaterialType; + adminComment?: string; + // 각 부서별 담당자 지정 + reviewers: { + departmentCode: keyof typeof EVALUATION_DEPARTMENT_CODES; + reviewerUserId: number; + }[]; +} + +// 평가 대상 수동 생성 +// service.ts 파일의 CreateEvaluationTargetInput 타입 수정 +export interface CreateEvaluationTargetInput { + evaluationYear: number + division: "OCEAN" | "SHIPYARD" + vendorId: number + materialType: "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK" + adminComment?: string + // ✅ 추가된 L/D 클레임 필드들 + ldClaimCount?: number + ldClaimAmount?: number + ldClaimCurrency?: "KRW" | "USD" | "EUR" | "JPY" + reviewers: Array<{ + departmentCode: string + reviewerUserId: number + }> +} + +// createEvaluationTarget 함수 수정 +// service.ts 수정 +export async function createEvaluationTarget( + input: CreateEvaluationTargetInput, + createdBy: number +) { + + console.log(input,"input") + try { + return await db.transaction(async (tx) => { + // 벤더 정보 조회 (기존과 동일) + const vendor = await tx + .select({ + id: vendors.id, + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName, + country: vendors.country, + }) + .from(vendors) + .where(eq(vendors.id, input.vendorId)) + .limit(1); + + if (!vendor.length) { + throw new Error("벤더를 찾을 수 없습니다."); + } + + const vendorInfo = vendor[0]; + + // 중복 체크 (기존과 동일) + const existing = await tx + .select({ id: evaluationTargets.id }) + .from(evaluationTargets) + .where( + and( + eq(evaluationTargets.evaluationYear, input.evaluationYear), + eq(evaluationTargets.vendorId, input.vendorId), + eq(evaluationTargets.materialType, input.materialType) + ) + ) + .limit(1); + + if (existing.length > 0) { + throw new Error("이미 동일한 평가 대상이 존재합니다."); + } + + // 평가 대상 생성 (기존과 동일) + const newEvaluationTarget = await tx + .insert(evaluationTargets) + .values({ + evaluationYear: input.evaluationYear, + division: input.division, + vendorId: input.vendorId, + vendorCode: vendorInfo.vendorCode || "", + vendorName: vendorInfo.vendorName, + domesticForeign: vendorInfo.country === "KR" ? "DOMESTIC" : "FOREIGN", + materialType: input.materialType, + status: "PENDING", + adminComment: input.adminComment, + adminUserId: createdBy, + ldClaimCount: input.ldClaimCount || 0, + ldClaimAmount: input.ldClaimAmount?.toString() || "0", + ldClaimCurrency: input.ldClaimCurrency || "KRW", + }) + .returning({ id: evaluationTargets.id }); + + const evaluationTargetId = newEvaluationTarget[0].id; + + // ✅ 담당자들 지정 (departmentNameFrom 추가) + if (input.reviewers && input.reviewers.length > 0) { + // 담당자들의 부서 정보 조회 + const reviewerIds = input.reviewers.map(r => r.reviewerUserId); + const reviewerInfos = await tx + .select({ + id: users.id, + departmentName: users.departmentName, // users 테이블에 부서명 필드가 있다고 가정 + }) + .from(users) + .where(sql`${users.id} = ANY(${reviewerIds})`); + + const reviewerAssignments = input.reviewers.map((reviewer) => { + const reviewerInfo = reviewerInfos.find(info => info.id === reviewer.reviewerUserId); + + return { + evaluationTargetId, + departmentCode: reviewer.departmentCode, + departmentNameFrom: reviewerInfo?.departmentName || null, // ✅ 실제 부서명 저장 + reviewerUserId: reviewer.reviewerUserId, + assignedBy: createdBy, + }; + }); + + await tx.insert(evaluationTargetReviewers).values(reviewerAssignments); + } + + return { + success: true, + evaluationTargetId, + message: "평가 대상이 성공적으로 생성되었습니다.", + }; + }); + } catch (error) { + console.error("Error creating evaluation target:", error); + return { + success: false, + error: error instanceof Error ? error.message : "평가 대상 생성 중 오류가 발생했습니다.", + }; + } +} + +// 담당자 목록 조회 시 부서 정보도 함께 반환 +export async function getAvailableReviewers(departmentCode?: string) { + try { + const reviewers = await db + .select({ + id: users.id, + name: users.name, + email: users.email, + // departmentName: "API로 추후", // ✅ 부서명도 반환 + }) + .from(users) + .orderBy(users.name) + .limit(100); + + return reviewers; + } catch (error) { + console.error("Error fetching available reviewers:", error); + return []; + } +} + +// 사용 가능한 벤더 목록 조회 +export async function getAvailableVendors(search?: string) { + try { + let query = db + .select({ + id: vendors.id, + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName, + status: vendors.status, + }) + .from(vendors) + .where( + and( + // 활성 상태인 벤더만 + // eq(vendors.status, "ACTIVE"), + // 검색어가 있으면 적용 + search + ? or( + ilike(vendors.vendorCode, `%${search}%`), + ilike(vendors.vendorName, `%${search}%`) + ) + : undefined + ) + ) + .orderBy(vendors.vendorName) + .limit(100); + + return await query; + } catch (error) { + console.error("Error fetching available vendors:", error); + return []; + } +} + + +// 부서 정보 조회 (상수에서) +export async function getDepartmentInfo() { + return Object.entries(EVALUATION_DEPARTMENT_CODES).map(([key, value]) => { + const departmentNames = { + ORDER_EVAL: "발주 평가 담당", + PROCUREMENT_EVAL: "조달 평가 담당", + QUALITY_EVAL: "품질 평가 담당", + DESIGN_EVAL: "설계 평가 담당", + CS_EVAL: "CS 평가 담당", + }; + + return { + code: value, + name: departmentNames[key as keyof typeof departmentNames], + key, + }; + }); +}
\ No newline at end of file |