diff options
| author | joonhoekim <26rote@gmail.com> | 2025-09-01 10:22:55 +0000 |
|---|---|---|
| committer | joonhoekim <26rote@gmail.com> | 2025-09-01 10:22:55 +0000 |
| commit | f72142f6cc46c7be5bf90803d365c2ecd144c53d (patch) | |
| tree | 6cfefba8edc8573bc99bee14ae7ed95914692430 /db/schema/items.ts | |
| parent | 20ba04d8588a7dfa6f65b1c080d6373631449f59 (diff) | |
(김준회) MDG 자재마스터 정보 조회 기능 및 메뉴 추가, 회원가입시 공급품목 선택 기능 추가
Diffstat (limited to 'db/schema/items.ts')
| -rw-r--r-- | db/schema/items.ts | 29 |
1 files changed, 27 insertions, 2 deletions
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; + |