벤더 코멘트 처리

1 files changed, 229 insertions, 0 deletions

diff --git a/lib/vendor-investigation/service.ts b/lib/vendor-investigation/service.ts
new file mode 100644
index 00000000..b731a95c
--- /dev/null
+++ b/lib/vendor-investigation/service.ts
@@ -0,0 +1,229 @@
+"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
+
+import { vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView } from "@/db/schema/vendors"
+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";
+import { filterColumns } from "@/lib/filter-columns";
+import { unstable_cache } from "@/lib/unstable-cache";
+import { getErrorMessage } from "@/lib/handle-error";
+import db from "@/db/db";
+import { sendEmail } from "../mail/sendEmail";
+import fs from "fs"
+import path from "path"
+import { v4 as uuid } from "uuid"
+
+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.investigationNotes, s),
+            ilike(vendorInvestigationsView.vendorEmail, s)
+            // etc.
+          )
+        }
+
+        // 3) Combine finalWhere
+        // Example: Only show vendorStatus = "PQ_SUBMITTED"
+        const finalWhere = and(
+          advancedWhere,
+          globalWhere,
+          eq(vendorInvestigationsView.vendorStatus, "PQ_SUBMITTED")
+        )
+
+
+
+        // 5) 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
+        const { data, total } = await db.transaction(async (tx) => {
+          // a) Select from the view
+          const investigationsData = await tx
+            .select()
+            .from(vendorInvestigationsView)
+            .where(finalWhere)
+            .orderBy(...orderBy)
+            .offset(offset)
+            .limit(input.perPage)
+
+          // b) Count total
+          const resCount = await tx
+            .select({ count: count() })
+            .from(vendorInvestigationsView)
+            .where(finalWhere)
+
+          return { data: investigationsData, total: resCount[0]?.count }
+        })
+
+        // 7) Calculate pageCount
+        const pageCount = Math.ceil(total / input.perPage)
+
+        // Now 'data' already contains JSON arrays of contacts & items
+        // thanks to the subqueries in the view definition!
+        return { data, pageCount }
+      } catch (err) {
+        console.error(err)
+        return { data: [], pageCount: 0 }
+      }
+    },
+    // Cache key
+    [JSON.stringify(input)],
+    {
+      revalidate: 3600,
+      tags: ["vendors-in-investigation"],
+    }
+  )()
+}
+
+
+interface RequestInvestigateVendorsInput {
+  ids: number[]
+}
+
+export async function requestInvestigateVendors({
+  ids,
+}: RequestInvestigateVendorsInput) {
+  try {
+    if (!ids || ids.length === 0) {
+      return { error: "No vendor IDs provided." }
+    }
+
+    // 1. Create a new investigation row for each vendor
+    // You could also check if an investigation already exists for each vendor
+    // before inserting. For now, we’ll assume we always insert new ones.
+    const newRecords = await db
+      .insert(vendorInvestigations)
+      .values(
+        ids.map((vendorId) => ({
+          vendorId
+        }))
+      )
+      .returning()
+
+    // 2. Optionally, send an email notification
+    //    Adjust recipient, subject, and body as needed.
+    await sendEmail({
+      to: "dujin.kim@dtsolution.io",
+      subject: "New Vendor Investigation(s) Requested",
+      // This template name could match a Handlebars file like: `investigation-request.hbs`
+      template: "investigation-request",
+      context: {
+        // For example, if you're translating in Korean:
+        language: "ko",
+        // Add any data you want to use within the template
+        vendorIds: ids,
+        notes: "Please initiate the planned investigations soon."
+      },
+    })
+
+    // 3. Optionally, revalidate any pages that might show updated data
+    // revalidatePath("/your-vendors-page") // or wherever you list the vendors
+
+    return { data: newRecords, error: null }
+  } catch (err: unknown) {
+    const errorMessage = err instanceof Error ? err.message : String(err)
+    return { error: errorMessage }
+  }
+}
+
+
+export async function updateVendorInvestigationAction(formData: FormData) {
+  try {
+    // 1) Separate text fields from file fields
+    const textEntries: Record<string, string> = {}
+    for (const [key, value] of formData.entries()) {
+      if (typeof value === "string") {
+        textEntries[key] = value
+      }
+    }
+
+    // 2) Convert text-based "investigationId" to a number
+    if (textEntries.investigationId) {
+      textEntries.investigationId = String(Number(textEntries.investigationId))
+    }
+
+    // 3) Parse/validate with Zod
+    const parsed = updateVendorInvestigationSchema.parse(textEntries)
+    // parsed is type UpdateVendorInvestigationSchema
+
+    // 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))
+
+    // 5) Handle file attachments
+    //    formData.getAll("attachments") can contain multiple files
+    const files = formData.getAll("attachments") as File[]
+
+    // Make sure the folder exists
+    const uploadDir = path.join(process.cwd(), "public", "vendor-investigation")
+    if (!fs.existsSync(uploadDir)) {
+      fs.mkdirSync(uploadDir, { recursive: true })
+    }
+
+    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}`
+
+        const filePath = path.join(uploadDir, newFileName)
+
+        // 6) Write file to disk
+        const arrayBuffer = await file.arrayBuffer()
+        const buffer = Buffer.from(arrayBuffer)
+        fs.writeFileSync(filePath, buffer)
+
+        // 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
+        })
+      }
+    }
+
+    // Revalidate anything if needed
+    revalidateTag("vendors-in-investigation")
+
+    return { data: "OK", error: null }
+  } catch (err: unknown) {
+    const message = err instanceof Error ? err.message : String(err)
+    return { error: message }
+  }
+}
\ No newline at end of file