diff options
Diffstat (limited to 'lib/pq/service.ts')
| -rw-r--r-- | lib/pq/service.ts | 583 |
1 files changed, 347 insertions, 236 deletions
diff --git a/lib/pq/service.ts b/lib/pq/service.ts index 7296b836..54459a6c 100644 --- a/lib/pq/service.ts +++ b/lib/pq/service.ts @@ -5,7 +5,7 @@ import { CopyPqListInput, CreatePqListInput, UpdatePqValidToInput, copyPqListSch import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import { getErrorMessage } from "@/lib/handle-error";
-import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, ne, count,isNull,SQL, sql, lt, isNotNull} from "drizzle-orm";
+import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, ne, count,isNull,SQL, sql, lt, gt, isNotNull} from "drizzle-orm";
import { z } from "zod"
import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache";
import { format } from "date-fns"
@@ -91,13 +91,25 @@ export async function getPQProjectsByVendorId(vendorId: number): Promise<Project export async function getPQDataByVendorId(
vendorId: number,
- projectId?: number
+ projectId?: number,
+ pqType?: "GENERAL" | "PROJECT" | "NON_INSPECTION"
): Promise<PQGroupData[]> {
try {
// 파라미터 유효성 검증
if (isNaN(vendorId)) {
throw new Error("Invalid vendorId parameter");
}
+
+ // 타입 결정 로직
+ let finalPqType: "GENERAL" | "PROJECT" | "NON_INSPECTION";
+ if (pqType) {
+ finalPqType = pqType;
+ } else if (projectId) {
+ finalPqType = "PROJECT";
+ } else {
+ finalPqType = "GENERAL";
+ }
+
// 기본 쿼리 구성
const selectObj = {
criteriaId: pqCriterias.id,
@@ -127,65 +139,45 @@ export async function getPQDataByVendorId( fileSize: vendorCriteriaAttachments.fileSize,
};
- // Create separate queries for each case instead of modifying the same query variable
- if (projectId) {
- // 프로젝트별 PQ 쿼리 - PQ 리스트 기반으로 변경
- const rows = await db
- .select(selectObj)
- .from(pqCriterias)
- .innerJoin(
- pqLists,
- and(
- eq(pqCriterias.pqListId, pqLists.id),
- eq(pqLists.projectId, projectId),
- eq(pqLists.type, "PROJECT"),
- eq(pqLists.isDeleted, false)
- )
- )
- .leftJoin(
- vendorPqCriteriaAnswers,
- and(
- eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
- eq(vendorPqCriteriaAnswers.vendorId, vendorId),
- eq(vendorPqCriteriaAnswers.projectId, projectId)
- )
- )
- .leftJoin(
- vendorCriteriaAttachments,
- eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
- )
- .orderBy(pqCriterias.groupName, pqCriterias.code);
-
- return processQueryResults(rows);
- } else {
- // 일반 PQ 쿼리 - PQ 리스트 기반으로 변경
- const rows = await db
- .select(selectObj)
- .from(pqCriterias)
- .innerJoin(
- pqLists,
- and(
- eq(pqCriterias.pqListId, pqLists.id),
- eq(pqLists.type, "GENERAL"),
- eq(pqLists.isDeleted, false)
- )
- )
- .leftJoin(
- vendorPqCriteriaAnswers,
- and(
- eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
- eq(vendorPqCriteriaAnswers.vendorId, vendorId),
- isNull(vendorPqCriteriaAnswers.projectId)
- )
- )
- .leftJoin(
- vendorCriteriaAttachments,
- eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
- )
- .orderBy(pqCriterias.groupName, pqCriterias.code);
-
- return processQueryResults(rows);
+ // 타입별 쿼리 조건 구성
+ const pqListConditions = [
+ eq(pqCriterias.pqListId, pqLists.id),
+ eq(pqLists.type, finalPqType),
+ eq(pqLists.isDeleted, false)
+ ];
+
+ const answerConditions = [
+ eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
+ eq(vendorPqCriteriaAnswers.vendorId, vendorId)
+ ];
+
+ // 프로젝트별 조건 추가
+ if (finalPqType === "PROJECT" && projectId) {
+ pqListConditions.push(eq(pqLists.projectId, projectId));
+ answerConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId));
+ } else if (finalPqType === "GENERAL" || finalPqType === "NON_INSPECTION") {
+ pqListConditions.push(isNull(pqLists.projectId));
+ answerConditions.push(isNull(vendorPqCriteriaAnswers.projectId));
}
+
+ const rows = await db
+ .select(selectObj)
+ .from(pqCriterias)
+ .innerJoin(
+ pqLists,
+ and(...pqListConditions)
+ )
+ .leftJoin(
+ vendorPqCriteriaAnswers,
+ and(...answerConditions)
+ )
+ .leftJoin(
+ vendorCriteriaAttachments,
+ eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
+ )
+ .orderBy(pqCriterias.groupName, pqCriterias.code);
+
+ return processQueryResults(rows);
} catch (error) {
console.error("Error fetching PQ data:", error);
return [];
@@ -790,199 +782,199 @@ export async function uploadSHIMultipleFilesAction(files: File[], userId?: strin }
}
-export async function getVendorsInPQ(input: GetVendorsSchema) {
- return unstable_cache(
- async () => {
- try {
- const offset = (input.page - 1) * input.perPage;
-
- // 1) 고급 필터
- const advancedWhere = filterColumns({
- table: vendors,
- filters: input.filters,
- joinOperator: input.joinOperator,
- });
-
- // 2) 글로벌 검색
- let globalWhere: SQL<unknown> | undefined = undefined;
- if (input.search) {
- const s = `%${input.search}%`;
- globalWhere = or(
- ilike(vendors.vendorName, s),
- ilike(vendors.vendorCode, s),
- ilike(vendors.email, s),
- ilike(vendors.status, s)
- );
- }
-
- // 트랜잭션 내에서 데이터 조회
- const { data, total } = await db.transaction(async (tx) => {
- // 협력업체 ID 모음 (중복 제거용)
- const vendorIds = new Set<number>();
+// export async function getVendorsInPQ(input: GetVendorsSchema) {
+// return unstable_cache(
+// async () => {
+// try {
+// const offset = (input.page - 1) * input.perPage;
+
+// // 1) 고급 필터
+// const advancedWhere = filterColumns({
+// table: vendors,
+// filters: input.filters,
+// joinOperator: input.joinOperator,
+// });
+
+// // 2) 글로벌 검색
+// let globalWhere: SQL<unknown> | undefined = undefined;
+// if (input.search) {
+// const s = `%${input.search}%`;
+// globalWhere = or(
+// ilike(vendors.vendorName, s),
+// ilike(vendors.vendorCode, s),
+// ilike(vendors.email, s),
+// ilike(vendors.status, s)
+// );
+// }
+
+// // 트랜잭션 내에서 데이터 조회
+// const { data, total } = await db.transaction(async (tx) => {
+// // 협력업체 ID 모음 (중복 제거용)
+// const vendorIds = new Set<number>();
- // 1-A) 일반 PQ 답변이 있는 협력업체 찾기 (status와 상관없이)
- const generalPqVendors = await tx
- .select({
- vendorId: vendorPqCriteriaAnswers.vendorId
- })
- .from(vendorPqCriteriaAnswers)
- .innerJoin(
- vendors,
- eq(vendorPqCriteriaAnswers.vendorId, vendors.id)
- )
- .where(
- and(
- isNull(vendorPqCriteriaAnswers.projectId), // 일반 PQ만 (프로젝트 PQ 아님)
- advancedWhere,
- globalWhere
- )
- )
- .groupBy(vendorPqCriteriaAnswers.vendorId); // 각 벤더당 한 번만 카운트
+// // 1-A) 일반 PQ 답변이 있는 협력업체 찾기 (status와 상관없이)
+// const generalPqVendors = await tx
+// .select({
+// vendorId: vendorPqCriteriaAnswers.vendorId
+// })
+// .from(vendorPqCriteriaAnswers)
+// .innerJoin(
+// vendors,
+// eq(vendorPqCriteriaAnswers.vendorId, vendors.id)
+// )
+// .where(
+// and(
+// isNull(vendorPqCriteriaAnswers.projectId), // 일반 PQ만 (프로젝트 PQ 아님)
+// advancedWhere,
+// globalWhere
+// )
+// )
+// .groupBy(vendorPqCriteriaAnswers.vendorId); // 각 벤더당 한 번만 카운트
- generalPqVendors.forEach(v => vendorIds.add(v.vendorId));
+// generalPqVendors.forEach(v => vendorIds.add(v.vendorId));
- // 1-B) 프로젝트 PQ 답변이 있는 협력업체 ID 조회 (status와 상관없이)
- const projectPqVendors = await tx
- .select({
- vendorId: vendorPQSubmissions.vendorId
- })
- .from(vendorPQSubmissions)
- .innerJoin(
- vendors,
- eq(vendorPQSubmissions.vendorId, vendors.id)
- )
- .where(
- and(
- eq(vendorPQSubmissions.type, "PROJECT"),
- // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함
- not(eq(vendorPQSubmissions.status, "REQUESTED")), // REQUESTED 상태는 제외
- advancedWhere,
- globalWhere
- )
- );
+// // 1-B) 프로젝트 PQ 답변이 있는 협력업체 ID 조회 (status와 상관없이)
+// const projectPqVendors = await tx
+// .select({
+// vendorId: vendorPQSubmissions.vendorId
+// })
+// .from(vendorPQSubmissions)
+// .innerJoin(
+// vendors,
+// eq(vendorPQSubmissions.vendorId, vendors.id)
+// )
+// .where(
+// and(
+// eq(vendorPQSubmissions.type, "PROJECT"),
+// // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함
+// not(eq(vendorPQSubmissions.status, "REQUESTED")), // REQUESTED 상태는 제외
+// advancedWhere,
+// globalWhere
+// )
+// );
- projectPqVendors.forEach(v => vendorIds.add(v.vendorId));
+// projectPqVendors.forEach(v => vendorIds.add(v.vendorId));
- // 중복 제거된 협력업체 ID 배열
- const uniqueVendorIds = Array.from(vendorIds);
+// // 중복 제거된 협력업체 ID 배열
+// const uniqueVendorIds = Array.from(vendorIds);
- // 총 개수 (중복 제거 후)
- const total = uniqueVendorIds.length;
+// // 총 개수 (중복 제거 후)
+// const total = uniqueVendorIds.length;
- if (total === 0) {
- return { data: [], total: 0 };
- }
+// if (total === 0) {
+// return { data: [], total: 0 };
+// }
- // 페이징 처리 (정렬 후 limit/offset 적용)
- const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage);
+// // 페이징 처리 (정렬 후 limit/offset 적용)
+// const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage);
- // 2) 페이징된 협력업체 상세 정보 조회
- const vendorsData = await selectVendors(tx, {
- where: inArray(vendors.id, paginatedIds),
- orderBy: input.sort.length > 0
- ? input.sort.map((item) =>
- item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName)
- )
- : [asc(vendors.createdAt)],
- });
+// // 2) 페이징된 협력업체 상세 정보 조회
+// const vendorsData = await selectVendors(tx, {
+// where: inArray(vendors.id, paginatedIds),
+// orderBy: input.sort.length > 0
+// ? input.sort.map((item) =>
+// item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName)
+// )
+// : [asc(vendors.createdAt)],
+// });
- // 3) 각 벤더별 PQ 상태 정보 추가
- const vendorsWithPqInfo = await Promise.all(
- vendorsData.map(async (vendor) => {
- // 3-A) 첨부 파일 조회
- const attachments = await tx
- .select({
- id: vendorAttachments.id,
- fileName: vendorAttachments.fileName,
- filePath: vendorAttachments.filePath,
- })
- .from(vendorAttachments)
- .where(eq(vendorAttachments.vendorId, vendor.id));
+// // 3) 각 벤더별 PQ 상태 정보 추가
+// const vendorsWithPqInfo = await Promise.all(
+// vendorsData.map(async (vendor) => {
+// // 3-A) 첨부 파일 조회
+// const attachments = await tx
+// .select({
+// id: vendorAttachments.id,
+// fileName: vendorAttachments.fileName,
+// filePath: vendorAttachments.filePath,
+// })
+// .from(vendorAttachments)
+// .where(eq(vendorAttachments.vendorId, vendor.id));
- // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지)
- const generalPqAnswers = await tx
- .select({ count: count() })
- .from(vendorPqCriteriaAnswers)
- .where(
- and(
- eq(vendorPqCriteriaAnswers.vendorId, vendor.id),
- isNull(vendorPqCriteriaAnswers.projectId)
- )
- );
+// // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지)
+// const generalPqAnswers = await tx
+// .select({ count: count() })
+// .from(vendorPqCriteriaAnswers)
+// .where(
+// and(
+// eq(vendorPqCriteriaAnswers.vendorId, vendor.id),
+// isNull(vendorPqCriteriaAnswers.projectId)
+// )
+// );
- const hasGeneralPq = generalPqAnswers[0]?.count > 0;
+// const hasGeneralPq = generalPqAnswers[0]?.count > 0;
- // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함)
- const projectPqs = await tx
- .select({
- projectId: vendorPQSubmissions.projectId,
- projectName: projects.name,
- status: vendorPQSubmissions.status,
- submittedAt: vendorPQSubmissions.submittedAt,
- approvedAt: vendorPQSubmissions.approvedAt,
- rejectedAt: vendorPQSubmissions.rejectedAt
- })
- .from(vendorPQSubmissions)
- .innerJoin(
- projects,
- eq(vendorPQSubmissions.projectId, projects.id)
- )
- .where(
- and(
- eq(vendorPQSubmissions.vendorId, vendor.id),
- eq(vendorPQSubmissions.type, "PROJECT"),
- not(eq(vendorPQSubmissions.status, "REQUESTED")) // REQUESTED 상태는 제외
- )
- );
+// // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함)
+// const projectPqs = await tx
+// .select({
+// projectId: vendorPQSubmissions.projectId,
+// projectName: projects.name,
+// status: vendorPQSubmissions.status,
+// submittedAt: vendorPQSubmissions.submittedAt,
+// approvedAt: vendorPQSubmissions.approvedAt,
+// rejectedAt: vendorPQSubmissions.rejectedAt
+// })
+// .from(vendorPQSubmissions)
+// .innerJoin(
+// projects,
+// eq(vendorPQSubmissions.projectId, projects.id)
+// )
+// .where(
+// and(
+// eq(vendorPQSubmissions.vendorId, vendor.id),
+// eq(vendorPQSubmissions.type, "PROJECT"),
+// not(eq(vendorPQSubmissions.status, "REQUESTED")) // REQUESTED 상태는 제외
+// )
+// );
- const hasProjectPq = projectPqs.length > 0;
+// const hasProjectPq = projectPqs.length > 0;
- // 프로젝트 PQ 상태별 카운트
- const projectPqStatusCounts = {
- inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length,
- submitted: projectPqs.filter(p => p.status === "SUBMITTED").length,
- approved: projectPqs.filter(p => p.status === "APPROVED").length,
- rejected: projectPqs.filter(p => p.status === "REJECTED").length,
- total: projectPqs.length
- };
+// // 프로젝트 PQ 상태별 카운트
+// const projectPqStatusCounts = {
+// inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length,
+// submitted: projectPqs.filter(p => p.status === "SUBMITTED").length,
+// approved: projectPqs.filter(p => p.status === "APPROVED").length,
+// rejected: projectPqs.filter(p => p.status === "REJECTED").length,
+// total: projectPqs.length
+// };
- // 3-D) PQ 상태 정보 추가
- return {
- ...vendor,
- hasAttachments: attachments.length > 0,
- attachmentsList: attachments,
- pqInfo: {
- hasGeneralPq,
- hasProjectPq,
- projectPqs,
- projectPqStatusCounts,
- // 현재 PQ 상태 (UI에 표시 용도)
- pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts)
- }
- };
- })
- );
+// // 3-D) PQ 상태 정보 추가
+// return {
+// ...vendor,
+// hasAttachments: attachments.length > 0,
+// attachmentsList: attachments,
+// pqInfo: {
+// hasGeneralPq,
+// hasProjectPq,
+// projectPqs,
+// projectPqStatusCounts,
+// // 현재 PQ 상태 (UI에 표시 용도)
+// pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts)
+// }
+// };
+// })
+// );
- return { data: vendorsWithPqInfo, total };
- });
-
- // 페이지 수
- const pageCount = Math.ceil(total / input.perPage);
-
- return { data, pageCount };
- } catch (err) {
- console.error("Error in getVendorsInPQ:", err);
- // 에러 발생 시
- return { data: [], pageCount: 0 };
- }
- },
- [JSON.stringify(input)], // 캐싱 키
- {
- revalidate: 3600,
- tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화
- }
- )();
-}
+// return { data: vendorsWithPqInfo, total };
+// });
+
+// // 페이지 수
+// const pageCount = Math.ceil(total / input.perPage);
+
+// return { data, pageCount };
+// } catch (err) {
+// console.error("Error in getVendorsInPQ:", err);
+// // 에러 발생 시
+// return { data: [], pageCount: 0 };
+// }
+// },
+// [JSON.stringify(input)], // 캐싱 키
+// {
+// revalidate: 3600,
+// tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화
+// }
+// )();
+// }
// PQ 상태 표시 함수
function getPqStatusDisplay(
@@ -3105,6 +3097,66 @@ export async function togglePQListsAction(ids: number[], newIsDeleted: boolean) const session = await getServerSession(authOptions);
const userId = session?.user?.id ? Number(session.user.id) : null;
const now = new Date();
+
+ // 활성화하려는 경우 중복 활성화 체크
+ if (!newIsDeleted) {
+ // 선택된 PQ 리스트들의 정보를 먼저 가져옴
+ const selectedPqLists = await db
+ .select({
+ id: pqLists.id,
+ name: pqLists.name,
+ type: pqLists.type,
+ projectId: pqLists.projectId,
+ })
+ .from(pqLists)
+ .where(inArray(pqLists.id, ids));
+
+ // 현재 활성화된 PQ 리스트 확인
+ const activePqLists = await db
+ .select({
+ id: pqLists.id,
+ name: pqLists.name,
+ type: pqLists.type,
+ projectId: pqLists.projectId,
+ })
+ .from(pqLists)
+ .where(and(
+ eq(pqLists.isDeleted, false),
+ not(inArray(pqLists.id, ids))
+ ));
+
+ // 각 선택된 PQ 리스트에 대해 중복 체크
+ for (const selectedPq of selectedPqLists) {
+ // 일반 PQ 또는 미실사 PQ인 경우
+ if (selectedPq.type === "GENERAL" || selectedPq.type === "NON_INSPECTION") {
+ const activeSameType = activePqLists.filter(pq => pq.type === selectedPq.type);
+
+ if (activeSameType.length > 0) {
+ const activeNames = activeSameType.map(pq => pq.name).join(", ");
+ return {
+ success: false,
+ error: `${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ는 하나만 활성화할 수 있습니다.먼저 활성화된 ${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ를 비활성화한 후 활성화해주세요.`
+ };
+ }
+ }
+
+ // 프로젝트 PQ인 경우
+ if (selectedPq.type === "PROJECT" && selectedPq.projectId) {
+ const activeSameProject = activePqLists.filter(pq =>
+ pq.type === "PROJECT" && pq.projectId === selectedPq.projectId
+ );
+
+ if (activeSameProject.length > 0) {
+ const activeNames = activeSameProject.map(pq => pq.name).join(", ");
+ return {
+ success: false,
+ error: `프로젝트 PQ는 프로젝트별로 하나만 활성화할 수 있습니다. 먼저 활성화된 프로젝트 PQ를 비활성화한 후 활성화해주세요.`
+ };
+ }
+ }
+ }
+ }
+
const updated = await db
.update(pqLists)
.set({ isDeleted: newIsDeleted, updatedAt: now, updatedBy: userId })
@@ -3726,6 +3778,65 @@ export async function deletePQSubmissionAction(pqSubmissionId: number) { }
// PQ 목록별 항목 조회 (특정 pqListId에 속한 PQ 항목들)
+// PQ 리스트 정보 조회 (상태 포함)
+export async function getPQListInfo(pqListId: number) {
+ return unstable_cache(
+ async () => {
+ try {
+ const pqList = await db
+ .select({
+ id: pqLists.id,
+ name: pqLists.name,
+ type: pqLists.type,
+ projectId: pqLists.projectId,
+ validTo: pqLists.validTo,
+ isDeleted: pqLists.isDeleted,
+ createdAt: pqLists.createdAt,
+ updatedAt: pqLists.updatedAt,
+ })
+ .from(pqLists)
+ .where(and(
+ eq(pqLists.id, pqListId),
+ eq(pqLists.isDeleted, false)
+ ))
+ .limit(1)
+ .then(rows => rows[0]);
+
+ if (!pqList) {
+ return {
+ success: false,
+ error: "PQ 목록을 찾을 수 없습니다"
+ };
+ }
+
+ // 현재 시간과 비교하여 상태 결정
+ const now = new Date();
+ const isValid = !pqList.validTo || pqList.validTo > now;
+ const status = isValid ? "ACTIVE" : "INACTIVE";
+
+ return {
+ success: true,
+ data: {
+ ...pqList,
+ status
+ }
+ };
+ } catch (error) {
+ console.error("Error in getPQListInfo:", error);
+ return {
+ success: false,
+ error: "PQ 목록 정보를 가져오는 중 오류가 발생했습니다"
+ };
+ }
+ },
+ [`pq-list-info-${pqListId}`],
+ {
+ tags: ["pq-lists"],
+ revalidate: 3600, // 1시간
+ }
+ )();
+}
+
export async function getPQsByListId(pqListId: number, input: GetPQSchema) {
return unstable_cache(
async () => {
|