From f72142f6cc46c7be5bf90803d365c2ecd144c53d Mon Sep 17 00:00:00 2001
From: joonhoekim <26rote@gmail.com>
Date: Mon, 1 Sep 2025 10:22:55 +0000
Subject: (김준회) MDG 자재마스터 정보 조회 기능 및 메뉴 추가, 회원가입시
 공급품목 선택 기능 추가
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/schema/items.ts | 29 +++++++++++++++++++++++++++--
 1 file changed, 27 insertions(+), 2 deletions(-)

(limited to 'db/schema')

diff --git a/db/schema/items.ts b/db/schema/items.ts
index 102f897f..16338671 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -1,4 +1,6 @@
-import { pgTable, varchar, text, timestamp ,serial, integer, unique} from "drizzle-orm/pg-core"
+import { pgTable, varchar, text, timestamp, serial, integer, unique, pgView } from "drizzle-orm/pg-core"
+import { sql } from "drizzle-orm"
+import { MATERIAL_MASTER_PART_MATL } from "./MDG/mdg"
 
 // 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
 export const items = pgTable("items", {
@@ -84,4 +86,27 @@ export const itemOffshoreHull = pgTable("item_offshore_hull", {
 
 export type ItemOffshoreTop = typeof itemOffshoreTop.$inferSelect;
 export type ItemOffshoreHull = typeof itemOffshoreHull.$inferSelect;
-//각 테이블별 컬럼 변경(itemid -> itemCode)
\ No newline at end of file
+//각 테이블별 컬럼 변경(itemid -> itemCode)
+
+
+// 자재 검색용 뷰 - MDG 스키마의 MATERIAL_MASTER_PART_MATL 테이블에서 DISTINCT 조회
+export const materialSearchView = pgView("material_search_view").as((qb) => {
+  return qb
+    .select({
+      materialGroupCode: MATERIAL_MASTER_PART_MATL.MATKL,
+      materialName: MATERIAL_MASTER_PART_MATL.ZZNAME,
+      displayText: sql<string>`COALESCE(${MATERIAL_MASTER_PART_MATL.MATKL}, '') || ' - ' || COALESCE(${MATERIAL_MASTER_PART_MATL.ZZNAME}, '')`.as("display_text")
+    })
+    .from(MATERIAL_MASTER_PART_MATL)
+    .where(sql`
+      ${MATERIAL_MASTER_PART_MATL.MATKL} IS NOT NULL 
+      AND ${MATERIAL_MASTER_PART_MATL.MATKL} != ''
+      AND ${MATERIAL_MASTER_PART_MATL.ZZNAME} IS NOT NULL 
+      AND ${MATERIAL_MASTER_PART_MATL.ZZNAME} != ''
+    `)
+    .groupBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL.ZZNAME)
+    .orderBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL.ZZNAME);
+});
+
+export type MaterialSearchView = typeof materialSearchView.$inferSelect;
+
-- 
cgit v1.2.3