summaryrefslogtreecommitdiff
path: root/lib/techsales-rfq/service.ts
diff options
context:
space:
mode:
Diffstat (limited to 'lib/techsales-rfq/service.ts')
-rw-r--r--lib/techsales-rfq/service.ts149
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':
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 09:17:26 +0000