diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/bidding.ts | 1 | ||||
| -rw-r--r-- | db/schema/items.ts | 24 |
2 files changed, 15 insertions, 10 deletions
diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts index 9c623232..40632607 100644 --- a/db/schema/bidding.ts +++ b/db/schema/bidding.ts @@ -172,6 +172,7 @@ export const biddings = pgTable('biddings', { createdAt: timestamp('created_at').defaultNow().notNull(), updatedAt: timestamp('updated_at').defaultNow().notNull(), // 최종수정일 updatedBy: varchar('updated_by', { length: 100 }), // 최종수정자 + ANFNR: varchar({length: 50}), // 원본 ANFNR 추적을 위한 Bidding/RFQ Number (ECC) }) // 9. 사양설명회 정보 테이블 diff --git a/db/schema/items.ts b/db/schema/items.ts index a7eb2a3f..ef19a276 100644 --- a/db/schema/items.ts +++ b/db/schema/items.ts @@ -1,6 +1,6 @@ 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" +import { sql, eq } from "drizzle-orm" +import { MATERIAL_MASTER_PART_MATL, MATERIAL_MASTER_PART_MATL_CHARASGN } from "./MDG/mdg" // 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음) export const items = pgTable("items", { @@ -89,23 +89,27 @@ export type ItemOffshoreHull = typeof itemOffshoreHull.$inferSelect; //각 테이블별 컬럼 변경(itemid -> itemCode) -// 자재그룹 검색용 뷰 - MDG 스키마의 MATERIAL_MASTER_PART_MATL 테이블에서 DISTINCT 조회 +// 자재그룹 검색용 뷰 - MATKL(자재그룹코드)별 자재그룹 설명 조회 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") + 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") }) .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.ZZNAME} IS NOT NULL - AND ${MATERIAL_MASTER_PART_MATL.ZZNAME} != '' + 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.ZZNAME) - .orderBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL.ZZNAME); + .groupBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB) + .orderBy(MATERIAL_MASTER_PART_MATL.MATKL, MATERIAL_MASTER_PART_MATL_CHARASGN.ATWTB); }); export type MaterialSearchView = typeof materialSearchView.$inferSelect; |