(최겸)기술영업 RFQ 담당자 초대, 요구사항 반영

1 files changed, 116 insertions, 7 deletions

diff --git a/lib/tech-vendor-possible-items/repository.ts b/lib/tech-vendor-possible-items/repository.ts
index b2588395..5c1487b5 100644
--- a/lib/tech-vendor-possible-items/repository.ts
+++ b/lib/tech-vendor-possible-items/repository.ts
@@ -1,16 +1,17 @@
-import { eq, desc, count } from "drizzle-orm";

+import { eq, desc, count, SQL, sql, and, or, ilike } from "drizzle-orm";

 import { 

   techVendors, 

   techVendorPossibleItems

 } from "@/db/schema/techVendors";

+import type { PgTransaction } from "drizzle-orm/pg-core";

 

 /**

  * 기술영업 벤더 가능 아이템 목록 조회 (조인 포함)

  */

 export async function selectTechVendorPossibleItemsWithJoin(

-  tx: any,

-  where: any,

-  orderBy: any[],

+  tx: PgTransaction<any, any, any>,

+  where: SQL | undefined,

+  orderBy: SQL[],

   offset: number,

   limit: number

 ) {

@@ -18,10 +19,17 @@ export async function selectTechVendorPossibleItemsWithJoin(
     .select({

       id: techVendorPossibleItems.id,

       vendorId: techVendorPossibleItems.vendorId,

-      vendorCode: techVendors.vendorCode,

+      vendorCode: techVendorPossibleItems.vendorCode, // 테이블에서 직접 조회

       vendorName: techVendors.vendorName,

+      vendorEmail: techVendorPossibleItems.vendorEmail, // 테이블에서 직접 조회

       techVendorType: techVendors.techVendorType,

+      vendorStatus: techVendors.status,

       itemCode: techVendorPossibleItems.itemCode,

+      // 새로운 스키마: 테이블에서 직접 조회

+      workType: techVendorPossibleItems.workType,

+      shipTypes: techVendorPossibleItems.shipTypes,

+      itemList: techVendorPossibleItems.itemList,

+      subItemList: techVendorPossibleItems.subItemList,

       createdAt: techVendorPossibleItems.createdAt,

       updatedAt: techVendorPossibleItems.updatedAt,

     })

@@ -36,7 +44,10 @@ export async function selectTechVendorPossibleItemsWithJoin(
 /**

  * 기술영업 벤더 가능 아이템 총 개수 조회 (조인 포함)

  */

-export async function countTechVendorPossibleItemsWithJoin(tx: any, where?: any) {

+export async function countTechVendorPossibleItemsWithJoin(

+  tx: PgTransaction<any, any, any>, 

+  where?: SQL | undefined

+) {

   const [result] = await tx

     .select({ count: count() })

     .from(techVendorPossibleItems)

@@ -44,4 +55,102 @@ export async function countTechVendorPossibleItemsWithJoin(tx: any, where?: any)
     .where(where);

   

   return result.count;

-} 
\ No newline at end of file
+}

+

+/**

+ * 새로운 필드들을 위한 그룹별 통계 조회

+ */

+export async function getTechVendorPossibleItemsGroupStats(

+  tx: PgTransaction<any, any, any>,

+  groupBy: 'workType' | 'shipTypes' | 'vendorCode' | 'vendorEmail',

+  where?: SQL | undefined

+) {

+  const groupField = techVendorPossibleItems[groupBy];

+  

+  return await tx

+    .select({

+      groupValue: groupField,

+      count: count(),

+      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),

+      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),

+    })

+    .from(techVendorPossibleItems)

+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))

+    .where(where)

+    .groupBy(groupField)

+    .orderBy(desc(count()));

+}

+

+/**

+ * 공종별 통계 조회

+ */

+export async function getWorkTypeStats(

+  tx: PgTransaction<any, any, any>,

+  where?: SQL | undefined

+) {

+  return await tx

+    .select({

+      workType: techVendorPossibleItems.workType,

+      count: count(),

+      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),

+      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),

+    })

+    .from(techVendorPossibleItems)

+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))

+    .where(where)

+    .groupBy(techVendorPossibleItems.workType)

+    .orderBy(desc(count()));

+}

+

+/**

+ * 선종별 통계 조회

+ */

+export async function getShipTypeStats(

+  tx: PgTransaction<any, any, any>,

+  where?: SQL | undefined

+) {

+  return await tx

+    .select({

+      shipTypes: techVendorPossibleItems.shipTypes,

+      count: count(),

+      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),

+      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),

+    })

+    .from(techVendorPossibleItems)

+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))

+    .where(where)

+    .groupBy(techVendorPossibleItems.shipTypes)

+    .orderBy(desc(count()));

+}

+

+/**

+ * 벤더별 통계 조회

+ */

+export async function getVendorStats(

+  tx: PgTransaction<any, any, any>,

+  where?: SQL | undefined

+) {

+  return await tx

+    .select({

+      vendorId: techVendorPossibleItems.vendorId,

+      vendorCode: techVendorPossibleItems.vendorCode,

+      vendorName: techVendors.vendorName,

+      vendorEmail: techVendorPossibleItems.vendorEmail,

+      itemCount: count(),

+      distinctItemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('distinctItemCount'),

+      workTypeCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.workType})`.as('workTypeCount'),

+      shipTypeCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.shipTypes})`.as('shipTypeCount'),

+      latestUpdate: sql<Date>`MAX(${techVendorPossibleItems.updatedAt})`.as('latestUpdate'),

+    })

+    .from(techVendorPossibleItems)

+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))

+    .where(where)

+    .groupBy(

+      techVendorPossibleItems.vendorId,

+      techVendorPossibleItems.vendorCode,

+      techVendors.vendorName,

+      techVendorPossibleItems.vendorEmail

+    )

+    .orderBy(desc(count()));

+}

+