summaryrefslogtreecommitdiff
path: root/lib/rfq-last/service.ts
diff options
context:
space:
mode:
Diffstat (limited to 'lib/rfq-last/service.ts')
-rw-r--r--lib/rfq-last/service.ts625
1 files changed, 616 insertions, 9 deletions
diff --git a/lib/rfq-last/service.ts b/lib/rfq-last/service.ts
index f2710f02..0be8049b 100644
--- a/lib/rfq-last/service.ts
+++ b/lib/rfq-last/service.ts
@@ -1,12 +1,12 @@
// lib/rfq/service.ts
'use server'
-import { unstable_noStore } from "next/cache";
+import { unstable_cache, unstable_noStore } from "next/cache";
import db from "@/db/db";
-import { rfqsLastView } from "@/db/schema";
-import { and, desc, asc, ilike, or, eq, SQL, count, gte, lte,isNotNull,ne } from "drizzle-orm";
+import { RfqsLastView, rfqLastAttachmentRevisions, rfqLastAttachments, rfqsLast, rfqsLastView, users, rfqPrItems, prItemsLastView } from "@/db/schema";
+import {sql, and, desc, asc, like, ilike, or, eq, SQL, count, gte, lte, isNotNull, ne, inArray } from "drizzle-orm";
import { filterColumns } from "@/lib/filter-columns";
-import { GetRfqsSchema } from "./validations";
+import { GetRfqLastAttachmentsSchema, GetRfqsSchema } from "./validations";
export async function getRfqs(input: GetRfqsSchema) {
unstable_noStore();
@@ -44,17 +44,17 @@ export async function getRfqs(input: GetRfqsSchema) {
// 2. 고급 필터 처리
let advancedWhere: SQL<unknown> | undefined = undefined;
-
+
if (input.filters && Array.isArray(input.filters) && input.filters.length > 0) {
console.log("필터 적용:", input.filters.map(f => `${f.id} ${f.operator} ${f.value}`));
-
+
try {
advancedWhere = filterColumns({
table: rfqsLastView,
filters: input.filters,
joinOperator: input.joinOperator || 'and',
});
-
+
console.log("필터 조건 생성 완료");
} catch (error) {
console.error("필터 조건 생성 오류:", error);
@@ -111,8 +111,8 @@ export async function getRfqs(input: GetRfqsSchema) {
// 6. 정렬 및 페이징 처리
const orderByColumns = input.sort.map((sort) => {
const column = sort.id as keyof typeof rfqsLastView.$inferSelect;
- return sort.desc
- ? desc(rfqsLastView[column])
+ return sort.desc
+ ? desc(rfqsLastView[column])
: asc(rfqsLastView[column]);
});
@@ -139,3 +139,610 @@ export async function getRfqs(input: GetRfqsSchema) {
}
}
+const getRfqById = async (id: number): Promise<RfqsLastView | null> => {
+ // 1) RFQ 단건 조회
+ const rfqsRes = await db
+ .select()
+ .from(rfqsLastView)
+ .where(eq(rfqsLastView.id, id))
+ .limit(1);
+
+ if (rfqsRes.length === 0) return null;
+ const rfqRow = rfqsRes[0];
+
+ // 3) RfqWithItems 형태로 반환
+ const result: RfqsLastView = {
+ ...rfqRow,
+
+ };
+
+ return result;
+};
+
+
+export const findRfqLastById = async (id: number): Promise<RfqsLastView | null> => {
+ try {
+
+ const rfq = await getRfqById(id);
+
+ return rfq;
+ } catch (error) {
+ throw new Error('Failed to fetch user');
+ }
+};
+
+
+export async function getRfqLastAttachments(
+ input: GetRfqLastAttachmentsSchema,
+ rfqId: number
+) {
+ try {
+ const offset = (input.page - 1) * input.perPage
+
+ // Advanced Filter 처리 (메인 테이블 기준)
+ const advancedWhere = filterColumns({
+ table: rfqLastAttachments,
+ filters: input.filters,
+ joinOperator: input.joinOperator,
+ })
+
+ // 전역 검색 (첨부파일 + 리비전 파일명 검색)
+ let globalWhere
+ if (input.search) {
+ const s = `%${input.search}%`
+ globalWhere = or(
+ ilike(rfqLastAttachments.serialNo, s),
+ ilike(rfqLastAttachments.description, s),
+ ilike(rfqLastAttachments.currentRevision, s),
+ ilike(rfqLastAttachmentRevisions.fileName, s),
+ ilike(rfqLastAttachmentRevisions.originalFileName, s)
+ )
+ }
+
+ // 기본 필터
+ let basicWhere
+ if (input.attachmentType.length > 0 || input.fileType.length > 0) {
+ basicWhere = and(
+ input.attachmentType.length > 0
+ ? inArray(rfqLastAttachments.attachmentType, input.attachmentType)
+ : undefined,
+ input.fileType.length > 0
+ ? inArray(rfqLastAttachmentRevisions.fileType, input.fileType)
+ : undefined
+ )
+ }
+
+ // 최종 WHERE 절
+ const finalWhere = and(
+ eq(rfqLastAttachments.rfqId, rfqId), // RFQ ID 필수 조건
+ advancedWhere,
+ globalWhere,
+ basicWhere
+ )
+
+ // 정렬 (메인 테이블 기준)
+ const orderBy = input.sort.length > 0
+ ? input.sort.map((item) =>
+ item.desc ? desc(rfqLastAttachments[item.id as keyof typeof rfqLastAttachments]) : asc(rfqLastAttachments[item.id as keyof typeof rfqLastAttachments])
+ )
+ : [desc(rfqLastAttachments.createdAt)]
+
+ // 트랜잭션으로 데이터 조회
+ const { data, total } = await db.transaction(async (tx) => {
+ // 메인 데이터 조회 (첨부파일 + 최신 리비전 조인)
+ const data = await tx
+ .select({
+ // 첨부파일 메인 정보
+ id: rfqLastAttachments.id,
+ attachmentType: rfqLastAttachments.attachmentType,
+ serialNo: rfqLastAttachments.serialNo,
+ rfqId: rfqLastAttachments.rfqId,
+ currentRevision: rfqLastAttachments.currentRevision,
+ latestRevisionId: rfqLastAttachments.latestRevisionId,
+ description: rfqLastAttachments.description,
+ createdBy: rfqLastAttachments.createdBy,
+ createdAt: rfqLastAttachments.createdAt,
+ updatedAt: rfqLastAttachments.updatedAt,
+
+ // 최신 리비전 파일 정보
+ fileName: rfqLastAttachmentRevisions.fileName,
+ originalFileName: rfqLastAttachmentRevisions.originalFileName,
+ filePath: rfqLastAttachmentRevisions.filePath,
+ fileSize: rfqLastAttachmentRevisions.fileSize,
+ fileType: rfqLastAttachmentRevisions.fileType,
+ revisionComment: rfqLastAttachmentRevisions.revisionComment,
+
+ // 생성자 정보
+ createdByName: users.name,
+ })
+ .from(rfqLastAttachments)
+ .leftJoin(
+ rfqLastAttachmentRevisions,
+ and(
+ eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id),
+ eq(rfqLastAttachmentRevisions.isLatest, true)
+ )
+ )
+ .leftJoin(users, eq(rfqLastAttachments.createdBy, users.id))
+ .where(finalWhere)
+ .orderBy(...orderBy)
+ .limit(input.perPage)
+ .offset(offset)
+
+ // 전체 개수 조회
+ const totalResult = await tx
+ .select({ count: count() })
+ .from(rfqLastAttachments)
+ .leftJoin(
+ rfqLastAttachmentRevisions,
+ eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id)
+ )
+ .where(finalWhere)
+
+ const total = totalResult[0]?.count ?? 0
+
+ return { data, total }
+ })
+
+ const pageCount = Math.ceil(total / input.perPage)
+
+ return { data, pageCount }
+ } catch (err) {
+ console.error("getRfqAttachments error:", err)
+ return { data: [], pageCount: 0 }
+ }
+
+}
+// 사용자 목록 조회 (필터용)
+export async function getPUsersForFilter() {
+
+ try {
+ return await db
+ .select({
+ id: users.id,
+ name: users.name,
+ userCode: users.userCode,
+ })
+ .from(users)
+ .where(and(eq(users.isActive, true), isNotNull(users.userCode,)))
+ .orderBy(asc(users.name))
+ } catch (err) {
+ console.error("Error fetching users for filter:", err)
+ return []
+ }
+}
+
+
+
+// 일반견적 RFQ 코드 생성 (F+userCode(3자리)+일련번호5자리 형식)
+async function generateGeneralRfqCode(userCode: string): Promise<string> {
+ try {
+ // 동일한 userCode를 가진 마지막 일반견적 번호 조회
+ const lastRfq = await db
+ .select({ rfqCode: rfqsLast.rfqCode })
+ .from(rfqsLast)
+ .where(
+ and(
+ eq(rfqsLast.rfqType, "일반견적"),
+ like(rfqsLast.rfqCode, `F${userCode}%`) // 같은 userCode로 시작하는 RFQ만 조회
+ )
+ )
+ .orderBy(desc(rfqsLast.createdAt))
+ .limit(1);
+
+ let nextNumber = 1;
+
+ if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
+ // F+userCode(3자리)+일련번호(5자리) 형식에서 마지막 5자리 숫자 추출
+ const rfqCode = lastRfq[0].rfqCode;
+ const serialNumber = rfqCode.slice(-5); // 마지막 5자리 추출
+
+ // 숫자인지 확인하고 다음 번호 생성
+ if (/^\d{5}$/.test(serialNumber)) {
+ nextNumber = parseInt(serialNumber) + 1;
+ }
+ }
+
+ // 5자리 숫자로 패딩
+ const paddedNumber = String(nextNumber).padStart(5, '0');
+ return `F${userCode}${paddedNumber}`;
+ } catch (error) {
+ console.error("Error generating General RFQ code:", error);
+ // 에러 발생 시 타임스탬프 기반 코드 생성
+ const timestamp = Date.now().toString().slice(-5);
+ return `F${userCode}${timestamp}`;
+ }
+}
+
+// 일반견적 생성 액션
+interface CreateGeneralRfqInput {
+ rfqType: string;
+ rfqTitle: string;
+ dueDate: Date;
+ picUserId: number;
+ remark?: string;
+ items: Array<{
+ itemCode: string;
+ itemName: string;
+ quantity: number;
+ uom: string;
+ remark?: string;
+ }>;
+ createdBy: number;
+ updatedBy: number;
+}
+
+export async function createGeneralRfqAction(input: CreateGeneralRfqInput) {
+ try {
+ // 트랜잭션으로 처리
+ const result = await db.transaction(async (tx) => {
+ // 1. 구매 담당자 정보 조회
+ const picUser = await tx
+ .select({
+ name: users.name,
+ email: users.email,
+ userCode: users.userCode
+ })
+ .from(users)
+ .where(eq(users.id, input.picUserId))
+ .limit(1);
+
+ if (!picUser || picUser.length === 0) {
+ throw new Error("구매 담당자를 찾을 수 없습니다");
+ }
+
+ // 2. userCode 확인 (3자리)
+ const userCode = picUser[0].userCode;
+ if (!userCode || userCode.length !== 3) {
+ throw new Error("구매 담당자의 userCode가 올바르지 않습니다 (3자리 필요)");
+ }
+
+ // 3. RFQ 코드 생성 (userCode 사용)
+ const rfqCode = await generateGeneralRfqCode(userCode);
+
+ // 4. 대표 아이템 정보 추출 (첫 번째 아이템)
+ const representativeItem = input.items[0];
+
+ // 5. rfqsLast 테이블에 기본 정보 삽입
+ const [newRfq] = await tx
+ .insert(rfqsLast)
+ .values({
+ rfqCode,
+ rfqType: input.rfqType,
+ rfqTitle: input.rfqTitle,
+ status: "RFQ 생성",
+ dueDate: input.dueDate,
+
+ // 대표 아이템 정보
+ itemCode: representativeItem.itemCode,
+ itemName: representativeItem.itemName,
+
+ // 담당자 정보
+ pic: input.picUserId,
+ picCode: userCode, // userCode를 picCode로 사용
+ picName: picUser[0].name || '',
+
+ // 기타 정보
+ remark: input.remark || null,
+ createdBy: input.createdBy,
+ updatedBy: input.updatedBy,
+ createdAt: new Date(),
+ updatedAt: new Date(),
+ })
+ .returning();
+
+ // 6. rfqPrItems 테이블에 아이템들 삽입
+ const prItemsData = input.items.map((item, index) => ({
+ rfqsLastId: newRfq.id,
+ rfqItem: `${index + 1}`.padStart(3, '0'), // 001, 002, ...
+ prItem: `${index + 1}`.padStart(3, '0'),
+ prNo: rfqCode, // RFQ 코드를 PR 번호로 사용
+
+ materialCode: item.itemCode,
+ materialDescription: item.itemName,
+ quantity: item.quantity,
+ uom: item.uom,
+
+ majorYn: index === 0, // 첫 번째 아이템을 주요 아이템으로 설정
+ remark: item.remark || null,
+ }));
+
+ await tx.insert(rfqPrItems).values(prItemsData);
+
+ return newRfq;
+ });
+
+ return {
+ success: true,
+ message: "일반견적이 성공적으로 생성되었습니다",
+ data: {
+ id: result.id,
+ rfqCode: result.rfqCode,
+ },
+ };
+
+ } catch (error) {
+ console.error("일반견적 생성 오류:", error);
+
+ if (error instanceof Error) {
+ return {
+ success: false,
+ error: error.message,
+ };
+ }
+
+ return {
+ success: false,
+ error: "일반견적 생성 중 오류가 발생했습니다",
+ };
+ }
+}
+
+// 일반견적 미리보기 (선택적 기능)
+export async function previewGeneralRfqCode(picUserId: number): Promise<string> {
+ try {
+ // 구매 담당자 정보 조회
+ const picUser = await db
+ .select({
+ userCode: users.userCode
+ })
+ .from(users)
+ .where(eq(users.id, picUserId))
+ .limit(1);
+
+ if (!picUser || picUser.length === 0 || !picUser[0].userCode) {
+ return `F???00001`;
+ }
+
+ const userCode = picUser[0].userCode;
+ if (userCode.length !== 3) {
+ return `F???00001`;
+ }
+
+ // 동일한 userCode를 가진 마지막 일반견적 번호 조회
+ const lastRfq = await db
+ .select({ rfqCode: rfqsLast.rfqCode })
+ .from(rfqsLast)
+ .where(
+ and(
+ eq(rfqsLast.rfqType, "일반견적"),
+ like(rfqsLast.rfqCode, `F${userCode}%`)
+ )
+ )
+ .orderBy(desc(rfqsLast.createdAt))
+ .limit(1);
+
+ let nextNumber = 1;
+
+ if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
+ const rfqCode = lastRfq[0].rfqCode;
+ const serialNumber = rfqCode.slice(-5);
+
+ if (/^\d{5}$/.test(serialNumber)) {
+ nextNumber = parseInt(serialNumber) + 1;
+ }
+ }
+
+ const paddedNumber = String(nextNumber).padStart(5, '0');
+ return `F${userCode}${paddedNumber}`;
+ } catch (error) {
+ return `F???XXXXX`;
+ }
+}
+
+
+/**
+ * RFQ 첨부파일 목록 조회
+ */
+export async function getRfqAttachmentsAction(rfqId: number) {
+ try {
+ if (!rfqId || rfqId <= 0) {
+ return {
+ success: false,
+ error: "유효하지 않은 RFQ ID입니다",
+ data: []
+ }
+ }
+
+ // rfpAttachmentsWithLatestRevisionView 뷰 조회
+ const attachments = await db.execute(sql`
+ SELECT
+ attachment_id,
+ attachment_type,
+ serial_no,
+ rfq_id,
+ description,
+ current_revision,
+ revision_id,
+ file_name,
+ original_file_name,
+ file_path,
+ file_size,
+ file_type,
+ revision_comment,
+ created_by,
+ created_by_name,
+ created_at,
+ updated_at
+ FROM rfq_attachments_with_latest_revision
+ WHERE rfq_id = ${rfqId}
+ ORDER BY attachment_type, serial_no, created_at DESC
+ `)
+
+ const formattedAttachments = attachments.rows.map((row: any) => ({
+ attachmentId: row.attachment_id,
+ attachmentType: row.attachment_type,
+ serialNo: row.serial_no,
+ rfqId: row.rfq_id,
+ description: row.description,
+ currentRevision: row.current_revision,
+ revisionId: row.revision_id,
+ fileName: row.file_name,
+ originalFileName: row.original_file_name,
+ filePath: row.file_path,
+ fileSize: row.file_size,
+ fileType: row.file_type,
+ revisionComment: row.revision_comment,
+ createdBy: row.created_by,
+ createdByName: row.created_by_name,
+ createdAt: row.created_at ? new Date(row.created_at) : null,
+ updatedAt: row.updated_at ? new Date(row.updated_at) : null,
+ }))
+
+ return {
+ success: true,
+ data: formattedAttachments,
+ count: formattedAttachments.length
+ }
+
+ } catch (error) {
+ console.error("RFQ 첨부파일 조회 오류:", error)
+ return {
+ success: false,
+ error: "첨부파일 목록을 불러오는데 실패했습니다",
+ data: []
+ }
+ }
+}
+
+/**
+ * RFQ 품목 목록 조회
+ */
+export async function getRfqItemsAction(rfqId: number) {
+ try {
+ if (!rfqId || rfqId <= 0) {
+ return {
+ success: false,
+ error: "유효하지 않은 RFQ ID입니다",
+ data: []
+ }
+ }
+
+ // prItemsLastView 조회
+ const items = await db
+ .select()
+ .from(prItemsLastView)
+ .where(eq(prItemsLastView.rfqsLastId, rfqId))
+ .orderBy(prItemsLastView.majorYn, prItemsLastView.rfqItem, prItemsLastView.materialCode)
+
+ const formattedItems = items.map(item => ({
+ id: item.id,
+ rfqsLastId: item.rfqsLastId,
+ rfqItem: item.rfqItem,
+ prItem: item.prItem,
+ prNo: item.prNo,
+ materialCode: item.materialCode,
+ materialCategory: item.materialCategory,
+ acc: item.acc,
+ materialDescription: item.materialDescription,
+ size: item.size,
+ deliveryDate: item.deliveryDate,
+ quantity: item.quantity,
+ uom: item.uom,
+ grossWeight: item.grossWeight,
+ gwUom: item.gwUom,
+ specNo: item.specNo,
+ specUrl: item.specUrl,
+ trackingNo: item.trackingNo,
+ majorYn: item.majorYn,
+ remark: item.remark,
+ projectDef: item.projectDef,
+ projectSc: item.projectSc,
+ projectKl: item.projectKl,
+ projectLc: item.projectLc,
+ projectDl: item.projectDl,
+ // RFQ 관련 정보
+ rfqCode: item.rfqCode,
+ rfqType: item.rfqType,
+ rfqTitle: item.rfqTitle,
+ itemCode: item.itemCode,
+ itemName: item.itemName,
+ projectCode: item.projectCode,
+ projectName: item.projectName,
+ }))
+
+ // 주요 품목과 일반 품목 분리 및 통계
+ const majorItems = formattedItems.filter(item => item.majorYn)
+ const regularItems = formattedItems.filter(item => !item.majorYn)
+
+ return {
+ success: true,
+ data: formattedItems,
+ statistics: {
+ total: formattedItems.length,
+ major: majorItems.length,
+ regular: regularItems.length,
+ totalQuantity: formattedItems.reduce((sum, item) => sum + (item.quantity || 0), 0),
+ totalWeight: formattedItems.reduce((sum, item) => sum + (item.grossWeight || 0), 0),
+ }
+ }
+
+ } catch (error) {
+ console.error("RFQ 품목 조회 오류:", error)
+ return {
+ success: false,
+ error: "품목 목록을 불러오는데 실패했습니다",
+ data: [],
+ statistics: {
+ total: 0,
+ major: 0,
+ regular: 0,
+ totalQuantity: 0,
+ totalWeight: 0,
+ }
+ }
+ }
+}
+
+/**
+ * RFQ 기본 정보 조회 (첨부파일/품목 다이얼로그용)
+ */
+export async function getRfqBasicInfoAction(rfqId: number) {
+ try {
+ if (!rfqId || rfqId <= 0) {
+ return {
+ success: false,
+ error: "유효하지 않은 RFQ ID입니다",
+ data: null
+ }
+ }
+
+ const rfqInfo = await db
+ .select({
+ id: rfqsLast.id,
+ rfqCode: rfqsLast.rfqCode,
+ rfqType: rfqsLast.rfqType,
+ rfqTitle: rfqsLast.rfqTitle,
+ status: rfqsLast.status,
+ itemCode: rfqsLast.itemCode,
+ itemName: rfqsLast.itemName,
+ dueDate: rfqsLast.dueDate,
+ createdAt: rfqsLast.createdAt,
+ })
+ .from(rfqsLast)
+ .where(eq(rfqsLast.id, rfqId))
+ .limit(1)
+
+ if (!rfqInfo.length) {
+ return {
+ success: false,
+ error: "RFQ를 찾을 수 없습니다",
+ data: null
+ }
+ }
+
+ return {
+ success: true,
+ data: rfqInfo[0]
+ }
+
+ } catch (error) {
+ console.error("RFQ 기본정보 조회 오류:", error)
+ return {
+ success: false,
+ error: "RFQ 정보를 불러오는데 실패했습니다",
+ data: null
+ }
+ }
+}
+
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 10:51:06 +0000