1 files changed, 127 insertions, 1 deletions

diff --git a/lib/vendors/service.ts b/lib/vendors/service.ts
index 9a37f5d7..9362a88c 100644
--- a/lib/vendors/service.ts
+++ b/lib/vendors/service.ts
@@ -45,6 +45,7 @@ import type {
   CreateVendorItemSchema,
   GetRfqHistorySchema,
   GetVendorMaterialsSchema,
+  GetBidHistorySchema,
 } from "./validations";
 
 import { asc, desc, ilike, inArray, and, or, eq, isNull, sql } from "drizzle-orm";
@@ -55,7 +56,7 @@ import { items, materials } from "@/db/schema/items";
 import { mfaTokens, roles, userRoles, users } from "@/db/schema/users";
 import { getServerSession } from "next-auth";
 import { authOptions } from "@/app/api/auth/[...nextauth]/route";
-import { contractsDetailView, projects, vendorPQSubmissions, vendorsLogs } from "@/db/schema";
+import { contractsDetailView, projects, vendorPQSubmissions, vendorsLogs, biddingCompanies, biddings } from "@/db/schema";
 import { deleteFile, saveFile, saveBuffer } from "../file-stroage";
 import { basicContractTemplates } from "@/db/schema/basicContractDocumnet";
 import { basicContract } from "@/db/schema/basicContractDocumnet";
@@ -3213,3 +3214,128 @@ export async function getVendorByTaxId(taxId: string) {
     };
   }
 }
+export async function getBidHistory(input: GetBidHistorySchema, vendorId: number) {
+  try {
+    const offset = (input.page - 1) * input.perPage;
+
+    // 기본 where 조건 (vendorId)
+    const vendorWhere = eq(biddingCompanies.companyId, vendorId);
+
+    // 고급 필터링
+    const advancedWhere = filterColumns({
+      table: biddings,
+      filters: input.filters,
+      joinOperator: input.joinOperator,
+      joinedTables: {
+        biddingCompanies,
+        projects,
+      },
+      customColumnMapping: {
+        biddingManager: biddingCompanies.contactPerson,
+        projectCode: projects.code,
+      },
+    });
+
+    // 글로벌 검색
+    let globalWhere;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      globalWhere = or(
+        ilike(biddings.biddingNumber, s),
+        ilike(biddings.projectName, s),
+        ilike(biddings.itemName, s),
+        ilike(biddings.title, s)
+      );
+    }
+
+    const finalWhere = and(
+      advancedWhere,
+      globalWhere,
+      vendorWhere
+    );
+
+    // 정렬 조건 - 동적 매핑 (하드코딩 최소화)
+    // biddings, biddingCompanies, projects 등에서 정렬 가능한 컬럼을 동적으로 매핑
+    const sortFieldMap: Record<string, any> = {
+      biddingNumber: biddings.biddingNumber,
+      revision: biddings.revision,
+      contractType: biddings.contractType,
+      biddingType: biddings.biddingType,
+      biddingStatus: biddings.status,
+      projectName: biddings.projectName,
+      itemName: biddings.itemName,
+      biddingTitle: biddings.title,
+      biddingRequestDate: biddings.submissionStartDate,
+      biddingDeadline: biddings.submissionEndDate,
+      biddingManager: biddingCompanies.contactPerson,
+      projectCode: projects.code,
+      createdAt: biddings.createdAt,
+    };
+
+    const orderBy =
+      input.sort.length > 0
+        ? input.sort.map((item) => {
+            const field = sortFieldMap[item.id] ?? biddings.createdAt;
+            return item.desc ? desc(field) : asc(field);
+          })
+        : [desc(biddings.createdAt)];
+
+    // 트랜잭션으로 데이터 조회
+    const { data, total } = await db.transaction(async (tx) => {
+
+      // 데이터 조회 (biddingCompanies와 biddings 조인)
+      const bidHistoryData = await tx
+        .select({
+          id: biddingCompanies.id,
+          biddingId: biddings.id,
+          biddingNumber: biddings.biddingNumber,
+          revision: biddings.revision,
+          contractType: biddings.contractType,
+          biddingType: biddings.biddingType,
+          biddingStatus: biddings.status,
+          projectCode: projects.code,
+          projectName: biddings.projectName,
+          itemName: biddings.itemName,
+          // materialGroup: sql<string>`null`,
+          // materialGroupName: sql<string>`null`,
+          biddingTitle: biddings.title,
+          poNumber: sql<string>`null`,
+          contractNumber: sql<string>`null`,
+          biddingRequestDate: biddings.submissionStartDate,
+          biddingDeadline: biddings.submissionEndDate,
+          biddingManager: biddingCompanies.contactPerson,
+          currency: biddings.currency,
+          finalBidPrice: biddingCompanies.finalQuoteAmount,
+          expectedAmount: biddings.targetPrice,
+          awardRatio: biddingCompanies.awardRatio,
+          preQuotePrice: biddingCompanies.preQuoteAmount,
+          createdAt: biddings.createdAt,
+          updatedAt: biddings.updatedAt,
+        })
+        .from(biddingCompanies)
+        .innerJoin(biddings, eq(biddingCompanies.biddingId, biddings.id))
+        .leftJoin(projects, eq(biddings.projectId, projects.id))
+        .where(finalWhere)
+        .orderBy(...orderBy)
+        .limit(input.perPage)
+        .offset(offset);
+
+
+      const total = await tx
+        .select({ count: sql<number>`count(*)` })
+        .from(biddingCompanies)
+        .innerJoin(biddings, eq(biddingCompanies.biddingId, biddings.id))
+        .leftJoin(projects, eq(biddings.projectId, projects.id))
+        .where(finalWhere);
+
+      const totalCount = total[0]?.count ?? 0;
+      return { data: bidHistoryData, total: totalCount };
+    });
+
+    const pageCount = Math.ceil(total / input.perPage);
+    return { data, pageCount };
+  } catch (err) {
+    console.error("Error fetching bid history:", err);
+    return { data: [], pageCount: 0 };
+  }
+}
\ No newline at end of file