diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema/MDG/mdg.ts | 9 | ||||
| -rw-r--r-- | db/schema/items.ts | 25 |
2 files changed, 15 insertions, 19 deletions
diff --git a/db/schema/MDG/mdg.ts b/db/schema/MDG/mdg.ts index 60f94793..7dd2db88 100644 --- a/db/schema/MDG/mdg.ts +++ b/db/schema/MDG/mdg.ts @@ -1037,6 +1037,15 @@ export const MATERIAL_MASTER_PART_MATL_UNIT = mdgSchema.table('MATERIAL_MASTER_P updatedAt: timestamp('updated_at').defaultNow().notNull(), }); +// [별도 테이블] 자재그룹코드 마스터 - MATKL, MAKTX 중복 최소화 +export const MATERIAL_GROUP_MASTER = mdgSchema.table('MATERIAL_GROUP_MASTER', { + MATKL: varchar('MATKL', { length: 1000 }).primaryKey(), // Material Group Code (자재그룹코드) - 기본 키 + MAKTX: varchar('MAKTX', { length: 1000 }), // Material Group Description (자재그룹명) - 영어(SPRAS='E') 기준 + + createdAt: timestamp('created_at').defaultNow().notNull(), + updatedAt: timestamp('updated_at').defaultNow().notNull(), +}); + // [root] export const MATERIAL_MASTER_PART_RETURN_CMCTB_MAT_BSE = mdgSchema.table('MATERIAL_MASTER_PART_RETURN_CMCTB_MAT_BSE', { id: integer('id').primaryKey().generatedByDefaultAsIdentity(), 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; |