From caa01b321311de3983fb8bcf65bb20a6c047cf57 Mon Sep 17 00:00:00 2001
From: joonhoekim <26rote@gmail.com>
Date: Tue, 9 Sep 2025 10:40:11 +0000
Subject: (김준회) 자재그룹코드 및 자재그룹명에 대해 별도 테이블 생성, 동기화
 로직 작성(일회성 사용이며 수신시점에는 자동저장하므로 추후 사용 불필요),
 자재그룹 선택기를 변경사항에 맞춰 업데이트, 자재그룹명은 MAKTX 로 김학의 프로
 답변에 따라 변경
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/schema/items.ts | 25 ++++++-------------------
 1 file changed, 6 insertions(+), 19 deletions(-)

(limited to 'db/schema/items.ts')

diff --git a/db/schema/items.ts b/db/schema/items.ts
index ef19a276..e87f256e 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -1,6 +1,5 @@
 import { pgTable, varchar, text, timestamp, serial, integer, unique, pgView } from "drizzle-orm/pg-core"
-import { sql, eq } from "drizzle-orm"
-import { MATERIAL_MASTER_PART_MATL, MATERIAL_MASTER_PART_MATL_CHARASGN } from "./MDG/mdg"
+import { MATERIAL_GROUP_MASTER } from "./MDG/mdg"
 
 // 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
 export const items = pgTable("items", {
@@ -89,27 +88,15 @@ export type ItemOffshoreHull = typeof itemOffshoreHull.$inferSelect;
 //각 테이블별 컬럼 변경(itemid -> itemCode)
 
 
-// 자재그룹 검색용 뷰 - MATKL(자재그룹코드)별 자재그룹 설명 조회
+// 자재그룹 검색용 뷰 - 새로운 MATERIAL_GROUP_MASTER 테이블 기반
 export const materialSearchView = pgView("material_search_view").as((qb) => {
   return qb
     .select({
-      materialGroupCode: sql<string>`${MATERIAL_MASTER_PART_MATL.MATKL}`.as("material_group_code"),
-      materialGroupDesc: sql<string>`${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB}`.as("material_group_desc"),
-      displayText: sql<string>`COALESCE(${MATERIAL_MASTER_PART_MATL.MATKL}, '') || ' - ' || COALESCE(${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB}, '')`.as("display_text")
+      materialGroupCode: MATERIAL_GROUP_MASTER.MATKL,
+      materialGroupDesc: MATERIAL_GROUP_MASTER.MAKTX
     })
-    .from(MATERIAL_MASTER_PART_MATL)
-    .innerJoin(
-      MATERIAL_MASTER_PART_MATL_CHARASGN,
-      eq(MATERIAL_MASTER_PART_MATL.MATNR, MATERIAL_MASTER_PART_MATL_CHARASGN.MATNR)
-    )
-    .where(sql`
-      ${MATERIAL_MASTER_PART_MATL.MATKL} IS NOT NULL 
-      AND ${MATERIAL_MASTER_PART_MATL.MATKL} != ''
-      AND ${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB} IS NOT NULL 
-      AND ${MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB} != ''
-    `)
-    .groupBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB)
-    .orderBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB);
+    .from(MATERIAL_GROUP_MASTER)
+    .orderBy(MATERIAL_GROUP_MASTER.MATKL);
 });
 
 export type MaterialSearchView = typeof materialSearchView.$inferSelect;
-- 
cgit v1.2.3