(김준회) 기술영업 조선 RFQ 오류 수정 및 선종 필터 기능 추가

1 files changed, 144 insertions, 5 deletions

diff --git a/lib/techsales-rfq/service.ts b/lib/techsales-rfq/service.ts
index f3bb2e59..735fcf68 100644
--- a/lib/techsales-rfq/service.ts
+++ b/lib/techsales-rfq/service.ts
@@ -47,6 +47,91 @@ interface SeriesSnapshot {
   post1?: string;
 }
 
+// JSON 필드 식별 함수
+function isJsonField(fieldId: string): boolean {
+  const jsonFields = ['projNm', 'ptypeNm', 'projMsrm', 'sector', 'pspid'];
+  return jsonFields.includes(fieldId);
+}
+
+// JSON 필드 필터링 함수
+function filterJsonFields(filters: Filter<typeof techSalesRfqs>[], joinOperator: "and" | "or") {
+  const joinFn = joinOperator === "and" ? and : or;
+  
+  const conditions = filters.map(filter => {
+    const fieldId = filter.id as string;
+    const value = filter.value;
+    
+    switch (fieldId) {
+      case 'projNm':
+        return createJsonFieldCondition('projNm', filter.operator, value);
+      case 'ptypeNm':
+        return createJsonFieldCondition('ptypeNm', filter.operator, value);
+      case 'sector':
+        return createJsonFieldCondition('sector', filter.operator, value);
+      case 'pspid':
+        return createJsonFieldCondition('pspid', filter.operator, value);
+      case 'projMsrm':
+        // 숫자 필드는 특별 처리
+        return createJsonNumberFieldCondition('projMsrm', filter.operator, value);
+      default:
+        return undefined;
+    }
+  }).filter(Boolean);
+  
+  return conditions.length > 0 ? joinFn(...conditions) : undefined;
+}
+
+// JSON 텍스트 필드 조건 생성
+function createJsonFieldCondition(fieldName: string, operator: string, value: unknown) {
+  const jsonPath = `${techSalesRfqs.projectSnapshot}->>'${fieldName}'`;
+  
+  switch (operator) {
+    case 'eq':
+      return sql`${sql.raw(jsonPath)} = ${value}`;
+    case 'ne':
+      return sql`${sql.raw(jsonPath)} != ${value}`;
+    case 'iLike':
+      return sql`${sql.raw(jsonPath)} ILIKE ${'%' + value + '%'}`;
+    case 'notILike':
+      return sql`${sql.raw(jsonPath)} NOT ILIKE ${'%' + value + '%'}`;
+    case 'isEmpty':
+      return sql`(${sql.raw(jsonPath)} IS NULL OR ${sql.raw(jsonPath)} = '')`;
+    case 'isNotEmpty':
+      return sql`(${sql.raw(jsonPath)} IS NOT NULL AND ${sql.raw(jsonPath)} != '')`;
+    default:
+      return undefined;
+  }
+}
+
+// JSON 숫자 필드 조건 생성
+function createJsonNumberFieldCondition(fieldName: string, operator: string, value: unknown) {
+  const jsonPath = `(${techSalesRfqs.projectSnapshot}->>'${fieldName}')::int`;
+  const numValue = parseInt(value as string, 10);
+  
+  if (isNaN(numValue)) return undefined;
+  
+  switch (operator) {
+    case 'eq':
+      return sql`${sql.raw(jsonPath)} = ${numValue}`;
+    case 'ne':
+      return sql`${sql.raw(jsonPath)} != ${numValue}`;
+    case 'gt':
+      return sql`${sql.raw(jsonPath)} > ${numValue}`;
+    case 'gte':
+      return sql`${sql.raw(jsonPath)} >= ${numValue}`;
+    case 'lt':
+      return sql`${sql.raw(jsonPath)} < ${numValue}`;
+    case 'lte':
+      return sql`${sql.raw(jsonPath)} <= ${numValue}`;
+    case 'isEmpty':
+      return sql`${techSalesRfqs.projectSnapshot}->>'${fieldName}' IS NULL`;
+    case 'isNotEmpty':
+      return sql`${techSalesRfqs.projectSnapshot}->>'${fieldName}' IS NOT NULL`;
+    default:
+      return undefined;
+  }
+}
+
 /**
  * 연도별 순차 RFQ 코드 생성 함수 (다중 생성 지원)
  * 형식: RFQ-YYYY-001, RFQ-YYYY-002, ...
@@ -251,14 +336,26 @@ export async function getTechSalesRfqsWithJoin(input: GetTechSalesRfqsSchema) {
           });
         }
 
-        // 고급 필터 조건 생성
+        // 고급 필터 조건 생성 (JSON 필드 지원)
         let advancedWhere;
         if (advancedFilters.length > 0) {
-          advancedWhere = filterColumns({
+          // 일반 필드와 JSON 필드 분리
+          const normalFilters = advancedFilters.filter(f => !isJsonField(f.id as string));
+          const jsonFilters = advancedFilters.filter(f => isJsonField(f.id as string));
+          
+          const normalWhere = normalFilters.length > 0 ? filterColumns({
             table: techSalesRfqs,
-            filters: advancedFilters,
+            filters: normalFilters,
             joinOperator: advancedJoinOperator,
-          });
+          }) : undefined;
+          
+          const jsonWhere = jsonFilters.length > 0 ? filterJsonFields(jsonFilters, advancedJoinOperator) : undefined;
+          
+          if (normalWhere && jsonWhere) {
+            advancedWhere = advancedJoinOperator === "and" ? and(normalWhere, jsonWhere) : or(normalWhere, jsonWhere);
+          } else {
+            advancedWhere = normalWhere || jsonWhere;
+          }
         }
 
         // 전역 검색 조건
@@ -292,17 +389,59 @@ export async function getTechSalesRfqsWithJoin(input: GetTechSalesRfqsSchema) {
         if (input.sort?.length) {
           // 안전하게 접근하여 정렬 기준 설정
           orderBy = input.sort.map(item => {
-            switch (item.id) {
+            // TypeScript 에러 방지를 위한 타입 단언
+            const sortField = item.id as string;
+            
+            switch (sortField) {
               case 'id':
                 return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id;
               case 'rfqCode':
                 return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode;
               case 'materialCode':
                 return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode;
+              case 'itemName':
+                // itemName은 조인된 itemShipbuilding.itemList 필드
+                return item.desc ? desc(sql`item_shipbuilding.item_list`) : sql`item_shipbuilding.item_list`;
               case 'status':
                 return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status;
               case 'dueDate':
                 return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate;
+              case 'rfqSendDate':
+                return item.desc ? desc(techSalesRfqs.rfqSendDate) : techSalesRfqs.rfqSendDate;
+              case 'projNm':
+                // JSON 필드에서 추출된 프로젝트명
+                return item.desc ? desc(sql`${techSalesRfqs.projectSnapshot}->>'projNm'`) : sql`${techSalesRfqs.projectSnapshot}->>'projNm'`;
+              case 'projMsrm':
+                // JSON 필드에서 추출된 척수 (정수 캐스팅)
+                return item.desc ? desc(sql`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`) : sql`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`;
+              case 'ptypeNm':
+                // JSON 필드에서 추출된 선종명
+                return item.desc ? desc(sql`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`) : sql`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`;
+              case 'quotationCount':
+                // 서브쿼리로 계산된 견적수 - repository의 SELECT에서 정의한 컬럼명 사용
+                return item.desc ? desc(sql`(
+                  SELECT COUNT(*) 
+                  FROM tech_sales_vendor_quotations 
+                  WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+                )`) : sql`(
+                  SELECT COUNT(*) 
+                  FROM tech_sales_vendor_quotations 
+                  WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+                )`;
+              case 'attachmentCount':
+                // 서브쿼리로 계산된 첨부파일수 - repository의 SELECT에서 정의한 컬럼명 사용
+                return item.desc ? desc(sql`(
+                  SELECT COUNT(*) 
+                  FROM tech_sales_attachments 
+                  WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
+                )`) : sql`(
+                  SELECT COUNT(*) 
+                  FROM tech_sales_attachments 
+                  WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
+                )`;
+              case 'createdByName':
+                // 조인된 사용자명
+                return item.desc ? desc(sql`created_user.name`) : sql`created_user.name`;
               case 'createdAt':
                 return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt;
               case 'updatedAt':