From 33dccde38b365206cb14e1a0cb134e07dd8fe939 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Thu, 22 May 2025 09:52:21 +0000 Subject: (최겸) 기술영업 아이템 수정(itemCode reference) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- config/menuConfig.ts | 18 +++++++------- db/schema/items.ts | 24 +++++++++---------- lib/items-tech/service.ts | 60 +++++++++++++++++------------------------------ 3 files changed, 41 insertions(+), 61 deletions(-) diff --git a/config/menuConfig.ts b/config/menuConfig.ts index fe7d61a1..e182e134 100644 --- a/config/menuConfig.ts +++ b/config/menuConfig.ts @@ -40,13 +40,13 @@ export const mainNav: MenuSection[] = [ // icon: "ListTodo", group: "기본 정보" }, - // { - // title: "아이템 리스트(기술영업)", - // href: "/evcp/items-tech", - // description: "기술영업 조선, 해양 Top, 해양 Hull 아이템 리스트", - // // icon: "ListTodo", - // group: "기본 정보" - // }, + { + title: "아이템 리스트(기술영업)", + href: "/evcp/items-tech", + description: "기술영업 조선, 해양 Top, 해양 Hull 아이템 리스트", + // icon: "ListTodo", + group: "기본 정보" + }, { title: "업체 유형", href: "/evcp/vendor-type", @@ -105,7 +105,7 @@ export const mainNav: MenuSection[] = [ description: "협력업체에 대한 요약 정보를 출력", }, { - title: "협력업체 PQ/실사", + title: "협력업체 실사", href: "/evcp/vendor-investigation", description: "실사가 필요한 협력업체에 대한 일정 및 실사 내용 관리", }, @@ -121,7 +121,7 @@ export const mainNav: MenuSection[] = [ }, { title: "협력업체 Risk 관리", - href: "/evcp/pq", + href: "/evcp/pq_new", description: "협력업체의 제출 PQ를 확인하고 통과 여부를 결정", }, { diff --git a/db/schema/items.ts b/db/schema/items.ts index a032de04..bf58915b 100644 --- a/db/schema/items.ts +++ b/db/schema/items.ts @@ -29,7 +29,7 @@ export const offshoreHullWorkTypeEnum = pgEnum('offshore_hull_work_type', ['HA', //아이템코드(:자재그룹코드), 아이템이름(:아이템리스트), 공종(:workType), 선종, createdAt(:생성일), updatedAt(:수정일) export const itemShipbuilding = pgTable("item_shipbuilding", { id: serial("id").primaryKey(), - itemId: integer("item_id").notNull().references(() => items.id, { onDelete: 'cascade' }), + itemCode: varchar("item_code", { length: 100 }).notNull().references(() => items.itemCode, { onDelete: 'cascade' }), workType: workTypeEnum("work_type").notNull(), itemList: text("item_list"), shipTypes: text("ship_types").notNull().default('OPTION'), @@ -37,24 +37,21 @@ export const itemShipbuilding = pgTable("item_shipbuilding", { updatedAt: timestamp("updated_at").defaultNow().notNull(), }); - //조선 아이템 관계 정의 export const itemShipbuildingRelations = relations(itemShipbuilding, ({ one }) => ({ item: one(items, { - fields: [itemShipbuilding.itemId], - references: [items.id], + fields: [itemShipbuilding.itemCode], + references: [items.itemCode], }), })); - - export type ItemShipbuilding = typeof itemShipbuilding.$inferSelect; export type ItemWithShipbuilding = Item & ItemShipbuilding; //해양 TOP 아이템 테이블 export const itemOffshoreTop = pgTable("item_offshore_top", { id: serial("id").primaryKey(), - itemId: integer("item_id").notNull().references(() => items.id, { onDelete: 'cascade' }), + itemCode: varchar("item_code", { length: 100 }).notNull().references(() => items.itemCode, { onDelete: 'cascade' }), workType: offshoreTopWorkTypeEnum("work_type").notNull(), itemList: text("item_list"), subItemList: text("sub_item_list"), @@ -65,7 +62,7 @@ export const itemOffshoreTop = pgTable("item_offshore_top", { //해양 HULL 아이템 테이블 export const itemOffshoreHull = pgTable("item_offshore_hull", { id: serial("id").primaryKey(), - itemId: integer("item_id").notNull().references(() => items.id, { onDelete: 'cascade' }), + itemCode: varchar("item_code", { length: 100 }).notNull().references(() => items.itemCode, { onDelete: 'cascade' }), workType: offshoreHullWorkTypeEnum("work_type").notNull(), itemList: text("item_list"), subItemList: text("sub_item_list"), @@ -76,20 +73,21 @@ export const itemOffshoreHull = pgTable("item_offshore_hull", { //해양 TOP 아이템 관계 정의 export const itemOffshoreTopRelations = relations(itemOffshoreTop, ({ one }) => ({ item: one(items, { - fields: [itemOffshoreTop.itemId], - references: [items.id], + fields: [itemOffshoreTop.itemCode], + references: [items.itemCode], }), })); //해양 HULL 아이템 관계 정의 export const itemOffshoreHullRelations = relations(itemOffshoreHull, ({ one }) => ({ item: one(items, { - fields: [itemOffshoreHull.itemId], - references: [items.id], + fields: [itemOffshoreHull.itemCode], + references: [items.itemCode], }), })); export type ItemOffshoreTop = typeof itemOffshoreTop.$inferSelect; export type ItemOffshoreHull = typeof itemOffshoreHull.$inferSelect; export type ItemWithOffshoreTop = Item & ItemOffshoreTop; -export type ItemWithOffshoreHull = Item & ItemOffshoreHull; \ No newline at end of file +export type ItemWithOffshoreHull = Item & ItemOffshoreHull; +//각 테이블별 컬럼 변경(itemid -> itemCode) \ No newline at end of file diff --git a/lib/items-tech/service.ts b/lib/items-tech/service.ts index 02a5a901..62a66aaa 100644 --- a/lib/items-tech/service.ts +++ b/lib/items-tech/service.ts @@ -62,18 +62,17 @@ export async function getShipbuildingItems(input: GetItemsSchema) { // 조선 아이템 테이블과 기본 아이템 테이블 조인하여 조회 const result = await db.select({ id: itemShipbuilding.id, - itemId: itemShipbuilding.itemId, + itemCode: items.itemCode, workType: itemShipbuilding.workType, shipTypes: itemShipbuilding.shipTypes, itemList: itemShipbuilding.itemList, - itemCode: items.itemCode, itemName: items.itemName, description: items.description, createdAt: itemShipbuilding.createdAt, updatedAt: itemShipbuilding.updatedAt, }) .from(itemShipbuilding) - .innerJoin(items, eq(itemShipbuilding.itemId, items.id)) + .innerJoin(items, eq(itemShipbuilding.itemCode, items.itemCode)) .where(where) .orderBy(...orderBy) .offset(offset) @@ -84,7 +83,7 @@ export async function getShipbuildingItems(input: GetItemsSchema) { count: count() }) .from(itemShipbuilding) - .innerJoin(items, eq(itemShipbuilding.itemId, items.id)) + .innerJoin(items, eq(itemShipbuilding.itemCode, items.itemCode)) .where(where); const pageCount = Math.ceil(Number(total) / input.perPage); @@ -143,18 +142,17 @@ export async function getOffshoreTopItems(input: GetItemsSchema) { // 해양 TOP 아이템 테이블과 기본 아이템 테이블 조인하여 조회 const result = await db.select({ id: itemOffshoreTop.id, - itemId: itemOffshoreTop.itemId, + itemCode: items.itemCode, workType: itemOffshoreTop.workType, itemList: itemOffshoreTop.itemList, subItemList: itemOffshoreTop.subItemList, - itemCode: items.itemCode, itemName: items.itemName, description: items.description, createdAt: itemOffshoreTop.createdAt, updatedAt: itemOffshoreTop.updatedAt, }) .from(itemOffshoreTop) - .innerJoin(items, eq(itemOffshoreTop.itemId, items.id)) + .innerJoin(items, eq(itemOffshoreTop.itemCode, items.itemCode)) .where(where) .orderBy(...orderBy) .offset(offset) @@ -165,7 +163,7 @@ export async function getOffshoreTopItems(input: GetItemsSchema) { count: count() }) .from(itemOffshoreTop) - .innerJoin(items, eq(itemOffshoreTop.itemId, items.id)) + .innerJoin(items, eq(itemOffshoreTop.itemCode, items.itemCode)) .where(where); const pageCount = Math.ceil(Number(total) / input.perPage); @@ -224,18 +222,17 @@ export async function getOffshoreHullItems(input: GetItemsSchema) { // 해양 HULL 아이템 테이블과 기본 아이템 테이블 조인하여 조회 const result = await db.select({ id: itemOffshoreHull.id, - itemId: itemOffshoreHull.itemId, + itemCode: items.itemCode, workType: itemOffshoreHull.workType, itemList: itemOffshoreHull.itemList, subItemList: itemOffshoreHull.subItemList, - itemCode: items.itemCode, itemName: items.itemName, description: items.description, createdAt: itemOffshoreHull.createdAt, updatedAt: itemOffshoreHull.updatedAt, }) .from(itemOffshoreHull) - .innerJoin(items, eq(itemOffshoreHull.itemId, items.id)) + .innerJoin(items, eq(itemOffshoreHull.itemCode, items.itemCode)) .where(where) .orderBy(...orderBy) .offset(offset) @@ -246,7 +243,7 @@ export async function getOffshoreHullItems(input: GetItemsSchema) { count: count() }) .from(itemOffshoreHull) - .innerJoin(items, eq(itemOffshoreHull.itemId, items.id)) + .innerJoin(items, eq(itemOffshoreHull.itemCode, items.itemCode)) .where(where); const pageCount = Math.ceil(Number(total) / input.perPage); @@ -291,17 +288,15 @@ export async function createShipbuildingItem(input: TypedItemCreateData) { } const result = await db.transaction(async (tx) => { - // 1. itemCode 정규화해서 직접 쿼리 + // 1. itemCode로 직접 쿼리 const existRows = await tx.select().from(items) .where(eq(items.itemCode, input.itemCode)); const existingItem = existRows[0]; - let itemId: number; let itemResult: any; if (existingItem) { // 기존 아이템이 있으면 업데이트하지 않고 그대로 사용 - itemId = existingItem.id; itemResult = [existingItem]; // 배열 형태로 반환 } else { // 없으면 새로 생성 @@ -317,13 +312,11 @@ export async function createShipbuildingItem(input: TypedItemCreateData) { itemName: input.itemName, description: input.description, }).returning(); - - itemId = itemResult[0].id; } const shipData = input as ShipbuildingItemCreateData; const typeResult = await tx.insert(itemShipbuilding).values({ - itemId: itemId, + itemCode: input.itemCode, workType: shipData.workType ? (shipData.workType as '기장' | '전장' | '선실' | '배관' | '철의') : '기장', shipTypes: shipData.shipTypes || '', itemList: shipData.itemList || null, @@ -393,19 +386,17 @@ export async function createShipbuildingImportItem(input: { } const results = await db.transaction(async (tx) => { - // 1. itemCode 정규화해서 직접 쿼리 + // 1. itemCode로 직접 쿼리 const existRows = await tx.select().from(items) .where(eq(items.itemCode, input.itemCode)); const existingItem = existRows[0]; console.log('DB에서 직접 조회한 기존 아이템:', existingItem); - let itemId: number; + let itemResult: any; if (existingItem) { - // 기존 아이템이 있으면 업데이트하지 않고 그대로 사용 - itemId = existingItem.id; - console.log('기존 아이템 사용, id:', itemId); + console.log('기존 아이템 사용, itemCode:', input.itemCode); } else { // 없으면 새로 생성 // 현재 가장 큰 ID 값 가져오기 @@ -422,8 +413,7 @@ export async function createShipbuildingImportItem(input: { description: input.description, }).returning(); - itemId = insertResult[0].id; - console.log('새 아이템 생성 완료, id:', itemId); + console.log('새 아이템 생성 완료, itemCode:', input.itemCode); } const createdItems = []; @@ -432,13 +422,13 @@ export async function createShipbuildingImportItem(input: { const existShip = await tx.select().from(itemShipbuilding) .where( and( - eq(itemShipbuilding.itemId, itemId), + eq(itemShipbuilding.itemCode, input.itemCode), eq(itemShipbuilding.shipTypes, shipType) ) ); if (!existShip[0]) { const shipbuildingResult = await tx.insert(itemShipbuilding).values({ - itemId: itemId, + itemCode: input.itemCode, workType: input.workType, shipTypes: shipType, itemList: input.itemList || null, @@ -494,17 +484,15 @@ export async function createOffshoreTopItem(data: OffshoreTopItemCreateData) { // 트랜잭션 내에서 처리 const result = await db.transaction(async (tx) => { - // 1. itemCode 정규화해서 직접 쿼리 + // 1. itemCode로 직접 쿼리 const existRows = await tx.select().from(items) .where(eq(items.itemCode, data.itemCode)); const existingItem = existRows[0]; - let itemId: number; let itemResult: any; if (existingItem) { // 기존 아이템이 있으면 업데이트하지 않고 그대로 사용 - itemId = existingItem.id; itemResult = [existingItem]; // 배열 형태로 반환 } else { // 없으면 새로 생성 @@ -520,14 +508,12 @@ export async function createOffshoreTopItem(data: OffshoreTopItemCreateData) { itemName: data.itemName, description: data.description, }).returning(); - - itemId = itemResult[0].id; } const [offshoreTop] = await tx .insert(itemOffshoreTop) .values({ - itemId: itemId, + itemCode: data.itemCode, workType: data.workType, itemList: data.itemList, subItemList: data.subItemList, @@ -587,17 +573,15 @@ export async function createOffshoreHullItem(data: OffshoreHullItemCreateData) { // 트랜잭션 내에서 처리 const result = await db.transaction(async (tx) => { - // 1. itemCode 정규화해서 직접 쿼리 + // 1. itemCode로 직접 쿼리 const existRows = await tx.select().from(items) .where(eq(items.itemCode, data.itemCode)); const existingItem = existRows[0]; - let itemId: number; let itemResult: any; if (existingItem) { // 기존 아이템이 있으면 업데이트하지 않고 그대로 사용 - itemId = existingItem.id; itemResult = [existingItem]; // 배열 형태로 반환 } else { // 없으면 새로 생성 @@ -613,14 +597,12 @@ export async function createOffshoreHullItem(data: OffshoreHullItemCreateData) { itemName: data.itemName, description: data.description, }).returning(); - - itemId = itemResult[0].id; } const [offshoreHull] = await tx .insert(itemOffshoreHull) .values({ - itemId: itemId, + itemCode: data.itemCode, workType: data.workType, itemList: data.itemList, subItemList: data.subItemList, -- cgit v1.2.3