diff options
Diffstat (limited to 'lib/vendor-investigation/service.ts')
| -rw-r--r-- | lib/vendor-investigation/service.ts | 523 |
1 files changed, 445 insertions, 78 deletions
diff --git a/lib/vendor-investigation/service.ts b/lib/vendor-investigation/service.ts index e3d03cd4..bcf9efd4 100644 --- a/lib/vendor-investigation/service.ts +++ b/lib/vendor-investigation/service.ts @@ -1,6 +1,6 @@ "use server"; // Next.js 서버 액션에서 직접 import하려면 (선택) -import { vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView } from "@/db/schema/vendors" +import { items, vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView, vendorPossibleItems, vendors } from "@/db/schema/" import { GetVendorsInvestigationSchema, updateVendorInvestigationSchema } from "./validations" import { asc, desc, ilike, inArray, and, or, gte, lte, eq, isNull, count } from "drizzle-orm"; import { revalidateTag, unstable_noStore } from "next/cache"; @@ -13,54 +13,62 @@ import fs from "fs" import path from "path" import { v4 as uuid } from "uuid" import { vendorsLogs } from "@/db/schema"; +import { cache } from "react" export async function getVendorsInvestigation(input: GetVendorsInvestigationSchema) { return unstable_cache( async () => { try { const offset = (input.page - 1) * input.perPage - + // 1) Advanced filters const advancedWhere = filterColumns({ table: vendorInvestigationsView, filters: input.filters, joinOperator: input.joinOperator, }) - + // 2) Global search let globalWhere if (input.search) { const s = `%${input.search}%` globalWhere = or( + // 협력업체 정보 ilike(vendorInvestigationsView.vendorName, s), ilike(vendorInvestigationsView.vendorCode, s), + + // 담당자 정보 (새로 추가) + ilike(vendorInvestigationsView.requesterName, s), + ilike(vendorInvestigationsView.qmManagerName, s), + + // 실사 정보 ilike(vendorInvestigationsView.investigationNotes, s), - ilike(vendorInvestigationsView.vendorEmail, s) - // etc. + ilike(vendorInvestigationsView.investigationStatus, s), + ilike(vendorInvestigationsView.evaluationType, s), + ilike(vendorInvestigationsView.investigationAddress, s), + ilike(vendorInvestigationsView.investigationMethod, s), + + // 평가 결과 + ilike(vendorInvestigationsView.evaluationResult, s) ) } - // 3) Combine finalWhere - // Example: Only show vendorStatus = "PQ_SUBMITTED" const finalWhere = and( advancedWhere, - globalWhere, - // eq(vendorInvestigationsView.vendorStatus, "PQ_APPROVED") + globalWhere ) - - - - // 5) Sorting + + // 4) Sorting const orderBy = input.sort && input.sort.length > 0 ? input.sort.map((item) => - item.desc - ? desc(vendorInvestigationsView[item.id]) - : asc(vendorInvestigationsView[item.id]) - ) - : [desc(vendorInvestigationsView.investigationCreatedAt)] - - // 6) Query & count + item.desc + ? desc(vendorInvestigationsView[item.id]) + : asc(vendorInvestigationsView[item.id]) + ) + : [desc(vendorInvestigationsView.createdAt)] + + // 5) Query & count const { data, total } = await db.transaction(async (tx) => { // a) Select from the view const investigationsData = await tx @@ -70,7 +78,7 @@ export async function getVendorsInvestigation(input: GetVendorsInvestigationSche .orderBy(...orderBy) .offset(offset) .limit(input.perPage) - + // b) Count total const resCount = await tx .select({ count: count() }) @@ -79,14 +87,11 @@ export async function getVendorsInvestigation(input: GetVendorsInvestigationSche return { data: investigationsData, total: resCount[0]?.count } }) - - // 7) Calculate pageCount + + // 6) Calculate pageCount const pageCount = Math.ceil(total / input.perPage) - - console.log(data,"data") - - // Now 'data' already contains JSON arrays of contacts & items - // thanks to the subqueries in the view definition! + + // Data is already in the correct format from the simplified view return { data, pageCount } } catch (err) { console.error(err) @@ -101,8 +106,6 @@ export async function getVendorsInvestigation(input: GetVendorsInvestigationSche } )() } - - /** * Get existing investigations for a list of vendor IDs * @@ -116,7 +119,7 @@ export async function getExistingInvestigationsForVendors(vendorIds: number[]) { // Query the vendorInvestigationsView using the vendorIds const investigations = await db.query.vendorInvestigations.findMany({ where: inArray(vendorInvestigationsView.vendorId, vendorIds), - orderBy: [desc(vendorInvestigationsView.investigationCreatedAt)], + orderBy: [desc(vendorInvestigationsView.createdAt)], }) return investigations @@ -188,9 +191,10 @@ export async function requestInvestigateVendors({ } +// 개선된 서버 액션 - 텍스트 데이터만 처리 export async function updateVendorInvestigationAction(formData: FormData) { try { - // 1) Separate text fields from file fields + // 1) 텍스트 필드만 추출 const textEntries: Record<string, string> = {} for (const [key, value] of formData.entries()) { if (typeof value === "string") { @@ -198,69 +202,432 @@ export async function updateVendorInvestigationAction(formData: FormData) { } } - // 2) Convert text-based "investigationId" to a number + // 2) 적절한 타입으로 변환 + const processedEntries: any = {} + + // 필수 필드 if (textEntries.investigationId) { - textEntries.investigationId = String(Number(textEntries.investigationId)) + processedEntries.investigationId = Number(textEntries.investigationId) + } + if (textEntries.investigationStatus) { + processedEntries.investigationStatus = textEntries.investigationStatus } - // 3) Parse/validate with Zod - const parsed = updateVendorInvestigationSchema.parse(textEntries) - // parsed is type UpdateVendorInvestigationSchema + // 선택적 enum 필드 + if (textEntries.evaluationType) { + processedEntries.evaluationType = textEntries.evaluationType + } - // 4) Update the vendor_investigations table - await db - .update(vendorInvestigations) - .set({ - investigationStatus: parsed.investigationStatus, - scheduledStartAt: parsed.scheduledStartAt - ? new Date(parsed.scheduledStartAt) - : null, - scheduledEndAt: parsed.scheduledEndAt ? new Date(parsed.scheduledEndAt) : null, - completedAt: parsed.completedAt ? new Date(parsed.completedAt) : null, - investigationNotes: parsed.investigationNotes ?? "", - updatedAt: new Date(), - }) - .where(eq(vendorInvestigations.id, parsed.investigationId)) + // 선택적 문자열 필드 + if (textEntries.investigationAddress) { + processedEntries.investigationAddress = textEntries.investigationAddress + } + if (textEntries.investigationMethod) { + processedEntries.investigationMethod = textEntries.investigationMethod + } + if (textEntries.investigationNotes) { + processedEntries.investigationNotes = textEntries.investigationNotes + } - // 5) Handle file attachments - // formData.getAll("attachments") can contain multiple files - const files = formData.getAll("attachments") as File[] + // 선택적 날짜 필드 + if (textEntries.forecastedAt) { + processedEntries.forecastedAt = new Date(textEntries.forecastedAt) + } + if (textEntries.requestedAt) { + processedEntries.requestedAt = new Date(textEntries.requestedAt) + } + if (textEntries.confirmedAt) { + processedEntries.confirmedAt = new Date(textEntries.confirmedAt) + } + if (textEntries.completedAt) { + processedEntries.completedAt = new Date(textEntries.completedAt) + } - // Make sure the folder exists - const uploadDir = path.join(process.cwd(), "public", "vendor-investigation") - if (!fs.existsSync(uploadDir)) { - fs.mkdirSync(uploadDir, { recursive: true }) + // 선택적 숫자 필드 + if (textEntries.evaluationScore) { + processedEntries.evaluationScore = Number(textEntries.evaluationScore) } - for (const file of files) { - if (file && file.size > 0) { - // Create a unique filename - const ext = path.extname(file.name) // e.g. ".pdf" - const newFileName = `${uuid()}${ext}` + // 선택적 평가 결과 + if (textEntries.evaluationResult) { + processedEntries.evaluationResult = textEntries.evaluationResult + } - const filePath = path.join(uploadDir, newFileName) + // 3) Zod로 파싱/검증 + const parsed = updateVendorInvestigationSchema.parse(processedEntries) - // 6) Write file to disk - const arrayBuffer = await file.arrayBuffer() - const buffer = Buffer.from(arrayBuffer) - fs.writeFileSync(filePath, buffer) + // 4) 업데이트 데이터 준비 - 실제로 제공된 필드만 포함 + const updateData: any = { + investigationStatus: parsed.investigationStatus, + updatedAt: new Date(), + } - // 7) Insert a record in vendor_investigation_attachments - await db.insert(vendorInvestigationAttachments).values({ - investigationId: parsed.investigationId, - fileName: file.name, // original name - filePath: `/vendor-investigation/${newFileName}`, // relative path in public/ - attachmentType: "REPORT", // or user-specified - }) - } + // 선택적 필드들은 존재할 때만 추가 + if (parsed.evaluationType !== undefined) { + updateData.evaluationType = parsed.evaluationType + } + if (parsed.investigationAddress !== undefined) { + updateData.investigationAddress = parsed.investigationAddress + } + if (parsed.investigationMethod !== undefined) { + updateData.investigationMethod = parsed.investigationMethod + } + if (parsed.forecastedAt !== undefined) { + updateData.forecastedAt = parsed.forecastedAt + } + if (parsed.requestedAt !== undefined) { + updateData.requestedAt = parsed.requestedAt + } + if (parsed.confirmedAt !== undefined) { + updateData.confirmedAt = parsed.confirmedAt + } + if (parsed.completedAt !== undefined) { + updateData.completedAt = parsed.completedAt + } + if (parsed.evaluationScore !== undefined) { + updateData.evaluationScore = parsed.evaluationScore + } + if (parsed.evaluationResult !== undefined) { + updateData.evaluationResult = parsed.evaluationResult + } + if (parsed.investigationNotes !== undefined) { + updateData.investigationNotes = parsed.investigationNotes } - // Revalidate anything if needed + // 5) vendor_investigations 테이블 업데이트 + await db + .update(vendorInvestigations) + .set(updateData) + .where(eq(vendorInvestigations.id, parsed.investigationId)) + + // 6) 캐시 무효화 revalidateTag("vendors-in-investigation") return { data: "OK", error: null } } catch (err: unknown) { + console.error("Investigation update error:", err) const message = err instanceof Error ? err.message : String(err) return { error: message } } -}
\ No newline at end of file +} +// 실사 첨부파일 조회 함수 +export async function getInvestigationAttachments(investigationId: number) { + try { + const attachments = await db + .select() + .from(vendorInvestigationAttachments) + .where(eq(vendorInvestigationAttachments.investigationId, investigationId)) + .orderBy(vendorInvestigationAttachments.createdAt) + + return { success: true, attachments } + } catch (error) { + console.error("첨부파일 조회 실패:", error) + return { success: false, error: "첨부파일 조회에 실패했습니다.", attachments: [] } + } +} + +// 첨부파일 삭제 함수 +export async function deleteInvestigationAttachment(attachmentId: number) { + try { + // 파일 정보 조회 + const [attachment] = await db + .select() + .from(vendorInvestigationAttachments) + .where(eq(vendorInvestigationAttachments.id, attachmentId)) + .limit(1) + + if (!attachment) { + return { success: false, error: "첨부파일을 찾을 수 없습니다." } + } + + // 실제 파일 삭제 + const fullFilePath = path.join(process.cwd(), "public", attachment.filePath) + if (fs.existsSync(fullFilePath)) { + fs.unlinkSync(fullFilePath) + } + + // 데이터베이스에서 레코드 삭제 + await db + .delete(vendorInvestigationAttachments) + .where(eq(vendorInvestigationAttachments.id, attachmentId)) + + // 캐시 무효화 + revalidateTag("vendors-in-investigation") + + return { success: true } + } catch (error) { + console.error("첨부파일 삭제 실패:", error) + return { success: false, error: "첨부파일 삭제에 실패했습니다." } + } +} + +// 첨부파일 다운로드 정보 조회 +export async function getAttachmentDownloadInfo(attachmentId: number) { + try { + const [attachment] = await db + .select({ + fileName: vendorInvestigationAttachments.fileName, + filePath: vendorInvestigationAttachments.filePath, + mimeType: vendorInvestigationAttachments.mimeType, + fileSize: vendorInvestigationAttachments.fileSize, + }) + .from(vendorInvestigationAttachments) + .where(eq(vendorInvestigationAttachments.id, attachmentId)) + .limit(1) + + if (!attachment) { + return { success: false, error: "첨부파일을 찾을 수 없습니다." } + } + + const fullFilePath = path.join(process.cwd(), "public", attachment.filePath) + if (!fs.existsSync(fullFilePath)) { + return { success: false, error: "파일이 존재하지 않습니다." } + } + + return { + success: true, + downloadInfo: { + fileName: attachment.fileName, + filePath: attachment.filePath, + mimeType: attachment.mimeType, + fileSize: attachment.fileSize, + } + } + } catch (error) { + console.error("첨부파일 정보 조회 실패:", error) + return { success: false, error: "첨부파일 정보 조회에 실패했습니다." } + } +} +/** + * Get vendor details by ID + */ +export const getVendorById = cache(async (vendorId: number) => { + try { + const [vendorData] = await db + .select({ + id: vendors.id, + name: vendors.vendorName, + code: vendors.vendorCode, + taxId: vendors.taxId, + email: vendors.email, + phone: vendors.phone, + website: vendors.website, + address: vendors.address, + country: vendors.country, + status: vendors.status, + description: vendors.items, // Using items field as description for now + vendorTypeId: vendors.vendorTypeId, + representativeName: vendors.representativeName, + representativeBirth: vendors.representativeBirth, + representativeEmail: vendors.representativeEmail, + representativePhone: vendors.representativePhone, + corporateRegistrationNumber: vendors.corporateRegistrationNumber, + creditAgency: vendors.creditAgency, + creditRating: vendors.creditRating, + cashFlowRating: vendors.cashFlowRating, + businessSize: vendors.businessSize, + createdAt: vendors.createdAt, + updatedAt: vendors.updatedAt, + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .limit(1) + + if (!vendorData) { + throw new Error(`Vendor with ID ${vendorId} not found`) + } + + return vendorData + } catch (error) { + console.error("Error fetching vendor:", error) + throw new Error("Failed to fetch vendor details") + } +}) + +/** + * Get vendor items by vendor ID with caching + */ +export async function getVendorItemsByVendorId(vendorId: number) { + return unstable_cache( + async () => { + try { + // Join vendorPossibleItems with items table to get complete item information + const vendorItems = await db + .select({ + id: vendorPossibleItems.id, + vendorId: vendorPossibleItems.vendorId, + itemCode: vendorPossibleItems.itemCode, + itemName: items.itemName, + description: items.description, + createdAt: vendorPossibleItems.createdAt, + updatedAt: vendorPossibleItems.updatedAt, + }) + .from(vendorPossibleItems) + .leftJoin( + items, + eq(vendorPossibleItems.itemCode, items.itemCode) + ) + .where(eq(vendorPossibleItems.vendorId, vendorId)) + .orderBy(vendorPossibleItems.createdAt) + + return vendorItems + } catch (error) { + console.error("Error fetching vendor items:", error) + throw new Error("Failed to fetch vendor items") + } + }, + // Cache key + [`vendor-items-${vendorId}`], + { + revalidate: 3600, // Cache for 1 hour + tags: [`vendor-items-${vendorId}`, "vendor-items"], + } + )() +} + +/** + * Get all items for a vendor (alternative function name for clarity) + */ +export const getVendorPossibleItems = cache(async (vendorId: number) => { + return getVendorItemsByVendorId(vendorId) +}) + +/** + * Get vendor contacts by vendor ID + * This function assumes you have a vendorContacts table + */ +export const getVendorContacts = cache(async (vendorId: number) => { + try { + // Note: This assumes you have a vendorContacts table + // If you don't have this table yet, you can return an empty array + // or implement based on your actual contacts storage structure + + // For now, returning empty array since vendorContacts table wasn't provided + return [] + + /* + // Uncomment and modify when you have vendorContacts table: + const contacts = await db + .select({ + id: vendorContacts.id, + contactName: vendorContacts.name, + contactEmail: vendorContacts.email, + contactPhone: vendorContacts.phone, + contactPosition: vendorContacts.position, + isPrimary: vendorContacts.isPrimary, + isActive: vendorContacts.isActive, + createdAt: vendorContacts.createdAt, + updatedAt: vendorContacts.updatedAt, + }) + .from(vendorContacts) + .where( + and( + eq(vendorContacts.vendorId, vendorId), + eq(vendorContacts.isActive, true) + ) + ) + + return contacts + */ + } catch (error) { + console.error("Error fetching vendor contacts:", error) + return [] + } +}) + +/** + * Add an item to a vendor + */ +export async function addVendorItem(vendorId: number, itemCode: string) { + try { + // Check if the item exists + const [item] = await db + .select() + .from(items) + .where(eq(items.itemCode, itemCode)) + .limit(1) + + if (!item) { + throw new Error(`Item with code ${itemCode} not found`) + } + + // Check if the vendor-item relationship already exists + const [existingRelation] = await db + .select() + .from(vendorPossibleItems) + .where( + eq(vendorPossibleItems.vendorId, vendorId) && + eq(vendorPossibleItems.itemCode, itemCode) + ) + .limit(1) + + if (existingRelation) { + throw new Error("This item is already associated with the vendor") + } + + // Add the item to the vendor + const [newVendorItem] = await db + .insert(vendorPossibleItems) + .values({ + vendorId, + itemCode, + }) + .returning() + + // Revalidate cache + revalidateTag(`vendor-items-${vendorId}`) + revalidateTag("vendor-items") + + return newVendorItem + } catch (error) { + console.error("Error adding vendor item:", error) + throw new Error("Failed to add item to vendor") + } +} + +/** + * Remove an item from a vendor + */ +export async function removeVendorItem(vendorId: number, itemCode: string) { + try { + await db + .delete(vendorPossibleItems) + .where( + eq(vendorPossibleItems.vendorId, vendorId) && + eq(vendorPossibleItems.itemCode, itemCode) + ) + + // Revalidate cache + revalidateTag(`vendor-items-${vendorId}`) + revalidateTag("vendor-items") + + return { success: true } + } catch (error) { + console.error("Error removing vendor item:", error) + throw new Error("Failed to remove item from vendor") + } +} + +/** + * Get all available items (for adding to vendors) + */ +export const getAllItems = cache(async () => { + try { + const allItems = await db + .select({ + id: items.id, + itemCode: items.itemCode, + itemName: items.itemName, + description: items.description, + createdAt: items.createdAt, + updatedAt: items.updatedAt, + }) + .from(items) + .orderBy(items.itemName) + + return allItems + } catch (error) { + console.error("Error fetching all items:", error) + throw new Error("Failed to fetch items") + } +})
\ No newline at end of file |