summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--app/[lng]/evcp/(evcp)/admin/if/items/page.tsx371
-rw-r--r--config/itemsColumnsConfig.ts61
-rw-r--r--db/schema/items.ts21
-rw-r--r--lib/items-tech/table/hull/import-item-handler.tsx4
-rw-r--r--lib/items-tech/table/import-excel-button.tsx2
-rw-r--r--lib/items-tech/table/ship/import-item-handler.tsx6
-rw-r--r--lib/items-tech/table/top/import-item-handler.tsx4
-rw-r--r--lib/items/service.ts55
-rw-r--r--lib/items/table/add-items-dialog.tsx203
-rw-r--r--lib/items/table/import-excel-button.tsx28
-rw-r--r--lib/items/table/import-item-handler.tsx44
-rw-r--r--lib/items/table/item-excel-template.tsx42
-rw-r--r--lib/items/table/items-table.tsx49
-rw-r--r--lib/items/table/update-item-sheet.tsx199
-rw-r--r--lib/items/validations.ts30
-rw-r--r--lib/oracle/db.ts148
16 files changed, 1183 insertions, 84 deletions
diff --git a/app/[lng]/evcp/(evcp)/admin/if/items/page.tsx b/app/[lng]/evcp/(evcp)/admin/if/items/page.tsx
new file mode 100644
index 00000000..5fa788bd
--- /dev/null
+++ b/app/[lng]/evcp/(evcp)/admin/if/items/page.tsx
@@ -0,0 +1,371 @@
+import { getOracleConnection } from "@/lib/oracle-db/db";
+import db from "@/db/db";
+import { items } from "@/db/schema/items";
+import { cache } from "react";
+import { Button } from "@/components/ui/button";
+import {
+ Table,
+ TableBody,
+ TableCell,
+ TableHead,
+ TableHeader,
+ TableRow,
+} from "@/components/ui/table";
+
+// Oracle 메타데이터와 행 타입 정의
+type OracleColumn = {
+ name: string;
+};
+
+type OracleRow = (string | number | null)[];
+
+// PLM(?)의 Oracle DB 에 직접 연결해 데이터를 가져오는 페이지
+// 모든 데이터를 전부 가져오기는 힘들 수 있으므로 샘플 수준으로 가져오는 것을 우선 node로 처리한다.
+
+// Oracle에서 데이터를 가져오는 함수를 캐싱
+const fetchOracleData = cache(async (limit = 100, offset = 0) => {
+ const connection = await getOracleConnection();
+
+ try {
+ // 자재마스터 클래스 정보 테이블 조회 (CMCTB_MAT_CLAS)
+ const result = await connection.execute(
+ `SELECT
+ CLAS_CD,
+ CLAS_NM,
+ CLAS_DTL,
+ PRNT_CLAS_CD,
+ CLAS_LVL,
+ DEL_ORDR,
+ UOM,
+ STYPE,
+ GRD_MATL,
+ CHG_DT,
+ BSE_UOM
+ FROM SHI1.CMCTB_MAT_CLAS
+ WHERE ROWNUM <= :limit + :offset
+ OFFSET :offset ROWS`,
+ { limit, offset }
+ );
+
+ // 총 레코드 수 조회
+ const countResult = await connection.execute(
+ `SELECT COUNT(*) AS TOTAL FROM SHI1.CMCTB_MAT_CLAS`
+ );
+
+ const totalCount = countResult.rows?.[0]?.[0] || 0;
+
+ return {
+ rows: result.rows as OracleRow[] || [],
+ metadata: result.metaData as OracleColumn[] || [],
+ totalCount
+ };
+ } catch (error) {
+ console.error("Oracle 데이터 조회 오류:", error);
+ return { rows: [], metadata: [], totalCount: 0 };
+ } finally {
+ // 연결 종료
+ if (connection) {
+ try {
+ await connection.close();
+ } catch (err) {
+ console.error("Oracle 연결 종료 오류:", err);
+ }
+ }
+ }
+});
+
+// 전체 데이터를 가져와 Postgres에 삽입하는 함수
+const syncAllDataToPostgres = cache(async () => {
+ const BATCH_SIZE = 1000; // 한 번에 처리할 레코드 수
+ const MAX_RECORDS = 50000; // 최대 처리할 레코드 수
+
+ try {
+ // 총 레코드 수 확인
+ const { totalCount } = await fetchOracleData(1, 0);
+ const recordsToProcess = Math.min(totalCount, MAX_RECORDS);
+
+ let processedCount = 0;
+ let currentOffset = 0;
+
+ // 배치 단위로 처리
+ while (processedCount < recordsToProcess) {
+ // 오라클에서 데이터 가져오기
+ const { rows, metadata } = await fetchOracleData(BATCH_SIZE, currentOffset);
+
+ if (!rows.length) break;
+
+ // Postgres DB 트랜잭션 시작
+ await db.transaction(async (tx) => {
+ // Oracle 데이터를 Postgres 스키마에 맞게 변환하여 삽입 (UPSERT)
+ for (const row of rows) {
+ // 배열 형태의 데이터를 객체로 변환
+ const rowObj: Record<string, string | number | null> = {};
+ metadata.forEach((col: OracleColumn, index: number) => {
+ rowObj[col.name] = row[index];
+ });
+
+ await tx
+ .insert(items)
+ .values({
+ itemCode: String(rowObj.CLAS_CD || ''),
+ itemName: String(rowObj.CLAS_NM || ''),
+ description: rowObj.CLAS_DTL ? String(rowObj.CLAS_DTL) : null,
+ parentItemCode: rowObj.PRNT_CLAS_CD ? String(rowObj.PRNT_CLAS_CD) : null,
+ itemLevel: typeof rowObj.CLAS_LVL === 'number' ? rowObj.CLAS_LVL : null,
+ deleteFlag: rowObj.DEL_ORDR ? String(rowObj.DEL_ORDR) : null,
+ unitOfMeasure: rowObj.UOM ? String(rowObj.UOM) : null,
+ steelType: rowObj.STYPE ? String(rowObj.STYPE) : null,
+ gradeMaterial: rowObj.GRD_MATL ? String(rowObj.GRD_MATL) : null,
+ changeDate: rowObj.CHG_DT ? String(rowObj.CHG_DT) : null,
+ baseUnitOfMeasure: rowObj.BSE_UOM ? String(rowObj.BSE_UOM) : null
+ })
+ .onConflictDoUpdate({
+ target: items.itemCode,
+ set: {
+ itemName: String(rowObj.CLAS_NM || ''),
+ description: rowObj.CLAS_DTL ? String(rowObj.CLAS_DTL) : null,
+ parentItemCode: rowObj.PRNT_CLAS_CD ? String(rowObj.PRNT_CLAS_CD) : null,
+ itemLevel: typeof rowObj.CLAS_LVL === 'number' ? rowObj.CLAS_LVL : null,
+ deleteFlag: rowObj.DEL_ORDR ? String(rowObj.DEL_ORDR) : null,
+ unitOfMeasure: rowObj.UOM ? String(rowObj.UOM) : null,
+ steelType: rowObj.STYPE ? String(rowObj.STYPE) : null,
+ gradeMaterial: rowObj.GRD_MATL ? String(rowObj.GRD_MATL) : null,
+ changeDate: rowObj.CHG_DT ? String(rowObj.CHG_DT) : null,
+ baseUnitOfMeasure: rowObj.BSE_UOM ? String(rowObj.BSE_UOM) : null,
+ updatedAt: new Date()
+ }
+ });
+ }
+ });
+
+ processedCount += rows.length;
+ currentOffset += BATCH_SIZE;
+
+ // 진행 상황 업데이트
+ const progress = Math.min(100, Math.round((processedCount / recordsToProcess) * 100));
+
+ // 임시 상태 저장 (실제 구현에서는 저장소나 상태 관리 도구를 사용)
+ console.log(`진행 상황: ${progress}% (${processedCount}/${recordsToProcess})`);
+ }
+
+ return {
+ success: true,
+ message: `${processedCount}개의 자재마스터 클래스 정보가 items 테이블로 성공적으로 이관되었습니다.`,
+ count: processedCount
+ };
+ } catch (error) {
+ console.error("Postgres 데이터 이관 오류:", error);
+ return {
+ success: false,
+ message: `데이터 이관 중 오류가 발생했습니다: ${error instanceof Error ? error.message : '알 수 없는 오류'}`,
+ count: 0
+ };
+ }
+});
+
+// 샘플 데이터만 Postgres에 삽입하는 함수
+const syncSampleDataToPostgres = cache(async () => {
+ const { rows, metadata } = await fetchOracleData(100, 0);
+
+ if (!rows.length) {
+ return {
+ success: false,
+ message: "Oracle에서 가져올 데이터가 없습니다.",
+ count: 0
+ };
+ }
+
+ try {
+ // Postgres DB 트랜잭션 시작
+ await db.transaction(async (tx) => {
+ // Oracle 데이터를 Postgres 스키마에 맞게 변환하여 삽입 (UPSERT)
+ for (const row of rows) {
+ // 배열 형태의 데이터를 객체로 변환
+ const rowObj: Record<string, string | number | null> = {};
+ metadata.forEach((col: OracleColumn, index: number) => {
+ rowObj[col.name] = row[index];
+ });
+
+ await tx
+ .insert(items)
+ .values({
+ itemCode: String(rowObj.CLAS_CD || ''),
+ itemName: String(rowObj.CLAS_NM || ''),
+ description: rowObj.CLAS_DTL ? String(rowObj.CLAS_DTL) : null,
+ parentItemCode: rowObj.PRNT_CLAS_CD ? String(rowObj.PRNT_CLAS_CD) : null,
+ itemLevel: typeof rowObj.CLAS_LVL === 'number' ? rowObj.CLAS_LVL : null,
+ deleteFlag: rowObj.DEL_ORDR ? String(rowObj.DEL_ORDR) : null,
+ unitOfMeasure: rowObj.UOM ? String(rowObj.UOM) : null,
+ steelType: rowObj.STYPE ? String(rowObj.STYPE) : null,
+ gradeMaterial: rowObj.GRD_MATL ? String(rowObj.GRD_MATL) : null,
+ changeDate: rowObj.CHG_DT ? String(rowObj.CHG_DT) : null,
+ baseUnitOfMeasure: rowObj.BSE_UOM ? String(rowObj.BSE_UOM) : null
+ })
+ .onConflictDoUpdate({
+ target: items.itemCode,
+ set: {
+ itemName: String(rowObj.CLAS_NM || ''),
+ description: rowObj.CLAS_DTL ? String(rowObj.CLAS_DTL) : null,
+ parentItemCode: rowObj.PRNT_CLAS_CD ? String(rowObj.PRNT_CLAS_CD) : null,
+ itemLevel: typeof rowObj.CLAS_LVL === 'number' ? rowObj.CLAS_LVL : null,
+ deleteFlag: rowObj.DEL_ORDR ? String(rowObj.DEL_ORDR) : null,
+ unitOfMeasure: rowObj.UOM ? String(rowObj.UOM) : null,
+ steelType: rowObj.STYPE ? String(rowObj.STYPE) : null,
+ gradeMaterial: rowObj.GRD_MATL ? String(rowObj.GRD_MATL) : null,
+ changeDate: rowObj.CHG_DT ? String(rowObj.CHG_DT) : null,
+ baseUnitOfMeasure: rowObj.BSE_UOM ? String(rowObj.BSE_UOM) : null,
+ updatedAt: new Date()
+ }
+ });
+ }
+ });
+
+ return {
+ success: true,
+ message: `${rows.length}개의 자재마스터 클래스 정보가 items 테이블로 성공적으로 이관되었습니다.`,
+ count: rows.length
+ };
+ } catch (error) {
+ console.error("Postgres 데이터 삽입 오류:", error);
+ return {
+ success: false,
+ message: `데이터 이관 중 오류가 발생했습니다: ${error instanceof Error ? error.message : '알 수 없는 오류'}`,
+ count: 0
+ };
+ }
+});
+
+// 현재 PostgreSQL DB에 저장된 데이터를 조회하는 함수
+const fetchCurrentPgData = cache(async () => {
+ try {
+ return await db.select().from(items).limit(100);
+ } catch (error) {
+ console.error("Postgres 데이터 조회 오류:", error);
+ return [];
+ }
+});
+
+export default async function ItemsAdminPage() {
+ // 데이터 초기 로드
+ const { rows: oracleData, metadata, totalCount } = await fetchOracleData(100, 0);
+ const pgData = await fetchCurrentPgData();
+
+ // 서버 액션으로 샘플 데이터 동기화 수행
+ async function handleSyncSample() {
+ "use server";
+ await syncSampleDataToPostgres();
+ // 반환 없이 void로 처리
+ }
+
+ // 서버 액션으로 전체 데이터 동기화 수행
+ async function handleSyncAll() {
+ "use server";
+ await syncAllDataToPostgres();
+ // 반환 없이 void로 처리
+ }
+
+ return (
+ <div className="p-8 space-y-6">
+ <h1 className="text-2xl font-bold">Items 테이블 데이터 관리</h1>
+ <p className="text-muted-foreground">PLM의 Oracle DB에서 자재마스터 클래스 정보를 Items 테이블로 이관하는 페이지입니다.</p>
+
+ <div className="grid grid-cols-1 md:grid-cols-2 gap-8">
+ <div className="border rounded-lg">
+ <div className="p-4 border-b bg-muted/50">
+ <h2 className="text-xl font-semibold">Oracle DB 데이터</h2>
+ <p className="text-sm text-muted-foreground">총 {totalCount}개 중 {oracleData.length}개의 레코드</p>
+ </div>
+
+ <div className="overflow-auto max-h-80 p-4">
+ <Table>
+ <TableHeader>
+ <TableRow>
+ <TableHead>클래스코드</TableHead>
+ <TableHead>클래스명</TableHead>
+ <TableHead>클래스내역</TableHead>
+ <TableHead>레벨</TableHead>
+ </TableRow>
+ </TableHeader>
+ <TableBody>
+ {oracleData.map((row: OracleRow, idx: number) => {
+ // 컬럼 인덱스 찾기
+ const getColIndex = (name: string) => metadata.findIndex((col: OracleColumn) => col.name === name);
+ const classCdIdx = getColIndex('CLAS_CD');
+ const clasNmIdx = getColIndex('CLAS_NM');
+ const clasDtlIdx = getColIndex('CLAS_DTL');
+ const clasLvlIdx = getColIndex('CLAS_LVL');
+
+ return (
+ <TableRow key={idx}>
+ <TableCell>{row[classCdIdx]}</TableCell>
+ <TableCell>{row[clasNmIdx]}</TableCell>
+ <TableCell>{row[clasDtlIdx]}</TableCell>
+ <TableCell>{row[clasLvlIdx]}</TableCell>
+ </TableRow>
+ );
+ })}
+ </TableBody>
+ </Table>
+ </div>
+ </div>
+
+ <div className="border rounded-lg">
+ <div className="p-4 border-b bg-muted/50">
+ <h2 className="text-xl font-semibold">Items 테이블 데이터</h2>
+ <p className="text-sm text-muted-foreground">총 {pgData.length}개의 레코드</p>
+ </div>
+
+ <div className="overflow-auto max-h-80 p-4">
+ <Table>
+ <TableHeader>
+ <TableRow>
+ <TableHead>ID</TableHead>
+ <TableHead>아이템코드</TableHead>
+ <TableHead>아이템명</TableHead>
+ <TableHead>레벨</TableHead>
+ </TableRow>
+ </TableHeader>
+ <TableBody>
+ {pgData.map((row) => (
+ <TableRow key={row.id}>
+ <TableCell>{row.id}</TableCell>
+ <TableCell>{row.itemCode}</TableCell>
+ <TableCell>{row.itemName}</TableCell>
+ <TableCell>{row.itemLevel}</TableCell>
+ </TableRow>
+ ))}
+ </TableBody>
+ </Table>
+ </div>
+ </div>
+ </div>
+
+ <div className="flex flex-col gap-6 mt-8">
+ <div className="flex flex-wrap gap-4">
+ <form action={handleSyncSample}>
+ <Button type="submit" variant="default">
+ 샘플 데이터 이관 (100건)
+ </Button>
+ </form>
+
+ <form action={handleSyncAll}>
+ <Button type="submit" variant="secondary">
+ 전체 데이터 이관 (최대 50,000건)
+ </Button>
+ </form>
+ </div>
+
+ <div className="border rounded-lg p-4">
+ <h3 className="text-lg font-semibold mb-2">데이터 이관 시 참고사항</h3>
+ <ul className="list-disc list-inside space-y-1 text-sm text-muted-foreground">
+ <li>샘플 데이터 이관은 100건의 데이터만 Items 테이블에 저장합니다.</li>
+ <li>전체 데이터 이관은 1,000건씩 나누어 최대 50,000건까지 처리합니다.</li>
+ <li>데이터 양이 많을 경우 이관 작업에 시간이 소요될 수 있습니다.</li>
+ <li>기존 데이터는 유지하고 아이템코드가 같은 경우 업데이트합니다 (UPSERT).</li>
+ <li>Oracle의 CMCTB_MAT_CLAS 테이블 데이터를 Items 테이블로 매핑합니다.</li>
+ </ul>
+ </div>
+ </div>
+ </div>
+ );
+} \ No newline at end of file
diff --git a/config/itemsColumnsConfig.ts b/config/itemsColumnsConfig.ts
index 8a161a37..8540d08c 100644
--- a/config/itemsColumnsConfig.ts
+++ b/config/itemsColumnsConfig.ts
@@ -6,37 +6,64 @@ export interface ItemColumnConfig {
group?: string
excelHeader?: string
type?: string
+ sortable?: boolean
+ filterable?: boolean
+ width?: number
}
export const itemsColumnsConfig: ItemColumnConfig[] = [
{
+ id: "itemLevel",
+ label: "레벨",
+ excelHeader: "레벨",
+ type: "number",
+ sortable: true,
+ filterable: true,
+ width: 80,
+ },
+ {
id: "itemCode",
- label: "Material Group",
- excelHeader: "Material Group",
- // group: "Basic Info",
+ label: "자재그룹코드",
+ excelHeader: "자재그룹코드",
+ type: "text",
+ sortable: true,
+ filterable: true,
+ width: 150,
},
{
id: "itemName",
- label: "Description",
- excelHeader: "Description",
- // group: "Basic Info",
+ label: "자재그룹명",
+ excelHeader: "자재그룹명",
+ type: "text",
+ sortable: true,
+ filterable: true,
+ width: 250,
},
{
id: "description",
- label: "Size/Dimension",
- excelHeader: "Size/Dimension",
- // group: "Basic Info",
+ label: "상세",
+ excelHeader: "상세",
+ type: "text",
+ sortable: true,
+ filterable: true,
+ width: 300,
},
{
- id: "createdAt",
- label: "Created At",
- excelHeader: "Created At",
- // group: "Metadata",a
+ id: "baseUnitOfMeasure",
+ label: "기본단위",
+ excelHeader: "기본단위",
+ type: "text",
+ sortable: true,
+ filterable: true,
+ width: 100,
},
{
- id: "updatedAt",
- label: "Updated At",
- excelHeader: "Updated At",
- // group: "Metadata",
+ id: "changeDate",
+ label: "마지막 변경일",
+ excelHeader: "마지막 변경일",
+ type: "date",
+ sortable: true,
+ filterable: true,
+ width: 130,
},
] \ No newline at end of file
diff --git a/db/schema/items.ts b/db/schema/items.ts
index bf58915b..4fd34506 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -1,13 +1,22 @@
import { relations } from "drizzle-orm";
import { pgTable, varchar, text, timestamp ,serial, integer, pgEnum} from "drizzle-orm/pg-core"
+// 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
export const items = pgTable("items", {
- id: serial("id").primaryKey(),
- itemCode: varchar("item_code", { length: 100 }).unique(),
- itemName: varchar("item_name", { length: 255 }).notNull(),
- description: text("description"),
- createdAt: timestamp("created_at").defaultNow().notNull(),
- updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ id: serial("id").primaryKey(), // 고유 식별자 (신규 추가)
+ itemCode: varchar("item_code", { length: 100 }).unique(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
+ itemName: varchar("item_name", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
+ description: text("description"), // CLAS_DTL - 아이템 상세 설명 (클래스 내역)
+ parentItemCode: varchar("parent_item_code", { length: 18 }), // PRNT_CLAS_CD - 부모 아이템 코드 (부모 클래스 코드)
+ itemLevel: integer("item_level"), // CLAS_LVL - 아이템 레벨 (클래스 레벨)
+ deleteFlag: varchar("delete_flag", { length: 1 }), // DEL_ORDR - 삭제 지시자 (Y/N)
+ unitOfMeasure: varchar("unit_of_measure", { length: 3 }), // UOM - 단위 (UOM)
+ steelType: varchar("steel_type", { length: 2 }), // STYPE - 강종 (Steel Type)
+ gradeMaterial: varchar("grade_material", { length: 50 }), // GRD_MATL - 등급 재질 (Grade Material)
+ changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 수정일자 (YYYYMMDD)
+ baseUnitOfMeasure: varchar("base_unit_of_measure", { length: 3 }), // BSE_UOM - 기준 단위 (Base UOM)
+ createdAt: timestamp("created_at").defaultNow().notNull(), // 생성일시 (신규 추가)
+ updatedAt: timestamp("updated_at").defaultNow().notNull(), // 수정일시 (신규 추가)
});
export type Item = typeof items.$inferSelect
diff --git a/lib/items-tech/table/hull/import-item-handler.tsx b/lib/items-tech/table/hull/import-item-handler.tsx
index 38a176cb..8c0e1cfa 100644
--- a/lib/items-tech/table/hull/import-item-handler.tsx
+++ b/lib/items-tech/table/hull/import-item-handler.tsx
@@ -61,6 +61,7 @@ export async function processHullFileImport(
try {
// 필드 매핑 (한글/영문 필드명 모두 지원)
const itemCode = row["아이템 코드"] || row["itemCode"] || "";
+ const itemName = row["아이템 명"] || row["itemName"] || "기술영업"; // 기본값 설정
const workType = row["기능(공종)"] || row["workType"] || "";
const itemList = row["아이템 리스트"] || row["itemList"] || null;
const subItemList = row["서브 아이템 리스트"] || row["subItemList"] || null;
@@ -68,6 +69,7 @@ export async function processHullFileImport(
// 데이터 정제
const cleanedRow = {
itemCode: typeof itemCode === 'string' ? itemCode.trim() : String(itemCode).trim(),
+ itemName: typeof itemName === 'string' ? itemName.trim() : String(itemName).trim(),
workType: typeof workType === 'string' ? workType.trim() : String(workType).trim(),
itemList: itemList ? (typeof itemList === 'string' ? itemList : String(itemList)) : null,
subItemList: subItemList ? (typeof subItemList === 'string' ? subItemList : String(subItemList)) : null,
@@ -89,7 +91,7 @@ export async function processHullFileImport(
// 해양 HULL 아이템 생성
const result = await createOffshoreHullItem({
itemCode: cleanedRow.itemCode,
- itemName: "기술영업", // 기본값 사용
+ itemName: cleanedRow.itemName, // Excel에서 가져온 값 또는 기본값 사용
workType: cleanedRow.workType as "HA" | "HE" | "HH" | "HM" | "NC",
description: null,
itemList: cleanedRow.itemList,
diff --git a/lib/items-tech/table/import-excel-button.tsx b/lib/items-tech/table/import-excel-button.tsx
index a5f63ff7..9bf4578c 100644
--- a/lib/items-tech/table/import-excel-button.tsx
+++ b/lib/items-tech/table/import-excel-button.tsx
@@ -123,7 +123,7 @@ export function ImportItemButton({ itemType, onSuccess }: ImportItemButtonProps)
});
// 필수 헤더 확인 (타입별 구분)
- let requiredHeaders: string[] = ["아이템 코드", "아이템 명", "기능(공종)"];
+ let requiredHeaders: string[] = ["아이템 코드", "기능(공종)"];
// 해양 TOP 및 HULL의 경우 선종 헤더는 필요 없음
if (itemType === "ship") {
diff --git a/lib/items-tech/table/ship/import-item-handler.tsx b/lib/items-tech/table/ship/import-item-handler.tsx
index 2a23d9d6..77bed4f0 100644
--- a/lib/items-tech/table/ship/import-item-handler.tsx
+++ b/lib/items-tech/table/ship/import-item-handler.tsx
@@ -59,12 +59,14 @@ export async function processFileImport(
try {
// 필드 매핑 (한글/영문 필드명 모두 지원)
const itemCode = row["아이템 코드"] || row["itemCode"] || "";
+ const itemName = row["아이템 명"] || row["itemName"] || "기술영업"; // 기본값 설정
const workType = row["기능(공종)"] || row["workType"] || "";
const itemList = row["아이템 리스트"] || row["itemList"] || null;
// 데이터 정제
const cleanedRow = {
itemCode: typeof itemCode === 'string' ? itemCode.trim() : String(itemCode).trim(),
+ itemName: typeof itemName === 'string' ? itemName.trim() : String(itemName).trim(),
workType: typeof workType === 'string' ? workType.trim() : String(workType).trim(),
itemList: itemList ? (typeof itemList === 'string' ? itemList : String(itemList)) : null,
};
@@ -94,7 +96,7 @@ export async function processFileImport(
// "option" 값으로 아이템 생성
const result = await createShipbuildingImportItem({
itemCode: cleanedRow.itemCode,
- itemName: "기술영업", // 기본값 사용
+ itemName: cleanedRow.itemName, // Excel에서 가져온 값 또는 기본값 사용
workType: cleanedRow.workType as "기장" | "전장" | "선실" | "배관" | "철의",
shipTypes: { "OPTION": true },
description: null,
@@ -115,7 +117,7 @@ export async function processFileImport(
for (const { type } of shipTypeEntries) {
const result = await createShipbuildingImportItem({
itemCode: cleanedRow.itemCode,
- itemName: "기술영업", // 기본값 사용
+ itemName: cleanedRow.itemName, // Excel에서 가져온 값 또는 기본값 사용
workType: cleanedRow.workType as "기장" | "전장" | "선실" | "배관" | "철의",
shipTypes: { [type]: true },
description: null,
diff --git a/lib/items-tech/table/top/import-item-handler.tsx b/lib/items-tech/table/top/import-item-handler.tsx
index d6b81964..e2564a91 100644
--- a/lib/items-tech/table/top/import-item-handler.tsx
+++ b/lib/items-tech/table/top/import-item-handler.tsx
@@ -61,6 +61,7 @@ export async function processTopFileImport(
try {
// 필드 매핑 (한글/영문 필드명 모두 지원)
const itemCode = row["아이템 코드"] || row["itemCode"] || "";
+ const itemName = row["아이템 명"] || row["itemName"] || "기술영업"; // 기본값 설정
const workType = row["기능(공종)"] || row["workType"] || "";
const itemList = row["아이템 리스트"] || row["itemList"] || null;
const subItemList = row["서브 아이템 리스트"] || row["subItemList"] || null;
@@ -68,6 +69,7 @@ export async function processTopFileImport(
// 데이터 정제
const cleanedRow = {
itemCode: typeof itemCode === 'string' ? itemCode.trim() : String(itemCode).trim(),
+ itemName: typeof itemName === 'string' ? itemName.trim() : String(itemName).trim(),
workType: typeof workType === 'string' ? workType.trim() : String(workType).trim(),
itemList: itemList ? (typeof itemList === 'string' ? itemList : String(itemList)) : null,
subItemList: subItemList ? (typeof subItemList === 'string' ? subItemList : String(subItemList)) : null,
@@ -89,7 +91,7 @@ export async function processTopFileImport(
// 해양 TOP 아이템 생성
const result = await createOffshoreTopItem({
itemCode: cleanedRow.itemCode,
- itemName: "기술영업", // 기본값 사용
+ itemName: cleanedRow.itemName, // Excel에서 가져온 값 또는 기본값 사용
workType: cleanedRow.workType as "TM" | "TS" | "TE" | "TP",
description: null,
itemList: cleanedRow.itemList,
diff --git a/lib/items/service.ts b/lib/items/service.ts
index 226742ca..99ef79ef 100644
--- a/lib/items/service.ts
+++ b/lib/items/service.ts
@@ -45,9 +45,18 @@ export async function getItems(input: GetItemsSchema) {
let globalWhere
if (input.search) {
const s = `%${input.search}%`
- globalWhere = or(ilike(items.itemCode, s), ilike(items.itemName, s)
- , ilike(items.description, s)
- )
+ globalWhere = or(
+ ilike(items.itemLevel, s),
+ ilike(items.itemCode, s),
+ ilike(items.itemName, s),
+ ilike(items.description, s),
+ ilike(items.parentItemCode, s),
+ ilike(items.unitOfMeasure, s),
+ ilike(items.steelType, s),
+ ilike(items.gradeMaterial, s),
+ ilike(items.baseUnitOfMeasure, s),
+ ilike(items.changeDate, s)
+ )
// 필요시 여러 칼럼 OR조건 (status, priority, etc)
}
@@ -87,6 +96,7 @@ export async function getItems(input: GetItemsSchema) {
return { data, pageCount };
} catch (err) {
// 에러 발생 시 디폴트
+ console.error(err)
return { data: [], pageCount: 0 };
}
},
@@ -106,6 +116,14 @@ export interface ItemCreateData {
itemCode: string
itemName: string
description: string | null
+ parentItemCode?: string | null
+ itemLevel?: number | null
+ deleteFlag?: string | null
+ unitOfMeasure?: string | null
+ steelType?: string | null
+ gradeMaterial?: string | null
+ changeDate?: string | null
+ baseUnitOfMeasure?: string | null
}
@@ -142,6 +160,14 @@ export async function createItem(input: ItemCreateData) {
txResult = await updateItem(tx, existingItem.id, {
itemName: input.itemName,
description: input.description,
+ parentItemCode: input.parentItemCode,
+ itemLevel: input.itemLevel,
+ deleteFlag: input.deleteFlag,
+ unitOfMeasure: input.unitOfMeasure,
+ steelType: input.steelType,
+ gradeMaterial: input.gradeMaterial,
+ changeDate: input.changeDate,
+ baseUnitOfMeasure: input.baseUnitOfMeasure,
})
} else {
// 새 아이템 생성
@@ -149,6 +175,14 @@ export async function createItem(input: ItemCreateData) {
itemCode: input.itemCode,
itemName: input.itemName,
description: input.description,
+ parentItemCode: input.parentItemCode,
+ itemLevel: input.itemLevel,
+ deleteFlag: input.deleteFlag,
+ unitOfMeasure: input.unitOfMeasure,
+ steelType: input.steelType,
+ gradeMaterial: input.gradeMaterial,
+ changeDate: input.changeDate,
+ baseUnitOfMeasure: input.baseUnitOfMeasure,
})
}
@@ -193,13 +227,20 @@ export async function createItem(input: ItemCreateData) {
export async function modifyItem(input: UpdateItemSchema & { id: number }) {
unstable_noStore();
try {
- const data = await db.transaction(async (tx) => {
- const [res] = await updateItem(tx, input.id, {
+ await db.transaction(async (tx) => {
+ await updateItem(tx, input.id, {
itemCode: input.itemCode,
itemName: input.itemName,
description: input.description,
+ parentItemCode: input.parentItemCode,
+ itemLevel: input.itemLevel,
+ deleteFlag: input.deleteFlag,
+ unitOfMeasure: input.unitOfMeasure,
+ steelType: input.steelType,
+ gradeMaterial: input.gradeMaterial,
+ changeDate: input.changeDate,
+ baseUnitOfMeasure: input.baseUnitOfMeasure,
});
- return res;
});
revalidateTag("items");
@@ -250,6 +291,6 @@ export async function getAllItems(): Promise<Item[]> {
try {
return await findAllItems();
} catch (err) {
- throw new Error("Failed to get roles");
+ throw new Error("Failed to get items");
}
}
diff --git a/lib/items/table/add-items-dialog.tsx b/lib/items/table/add-items-dialog.tsx
index 2224444c..3f71b437 100644
--- a/lib/items/table/add-items-dialog.tsx
+++ b/lib/items/table/add-items-dialog.tsx
@@ -20,7 +20,6 @@ import {
import {
Select,
SelectContent,
- SelectGroup,
SelectItem,
SelectTrigger,
SelectValue,
@@ -41,11 +40,31 @@ export function AddItemDialog() {
itemCode: "",
itemName: "",
description: "",
+ parentItemCode: "",
+ itemLevel: 5, // 기본값 5
+ deleteFlag: "N", // 기본값 N
+ unitOfMeasure: "",
+ steelType: "",
+ gradeMaterial: "",
+ changeDate: "",
+ baseUnitOfMeasure: "",
},
})
async function onSubmit(data: CreateItemSchema) {
- const result = await createItem(data)
+ const result = await createItem({
+ itemCode: data.itemCode,
+ itemName: data.itemName,
+ description: data.description ?? null,
+ parentItemCode: data.parentItemCode ?? null,
+ itemLevel: data.itemLevel ?? null,
+ deleteFlag: data.deleteFlag ?? null,
+ unitOfMeasure: data.unitOfMeasure ?? null,
+ steelType: data.steelType ?? null,
+ gradeMaterial: data.gradeMaterial ?? null,
+ changeDate: data.changeDate ?? null,
+ baseUnitOfMeasure: data.baseUnitOfMeasure ?? null,
+ })
if (result.error) {
alert(`에러: ${result.error}`)
return
@@ -83,16 +102,16 @@ export function AddItemDialog() {
<Form {...form}>
<form onSubmit={form.handleSubmit(onSubmit)}>
<div className="space-y-4 py-4">
-
+ <p className="text-red-500 font-bold text-center">※ MDG에서 수신만 하기에 비활성화할 기능입니다. <br />직접 업로드는 기술영업만 가능</p>
<FormField
control={form.control}
name="itemCode"
render={({ field }) => (
<FormItem>
- <FormLabel>Item Code</FormLabel>
+ <FormLabel>자재그룹코드 *</FormLabel>
<FormControl>
<Input
- placeholder="e.g."
+ placeholder="예: BOLT001"
{...field}
/>
</FormControl>
@@ -105,10 +124,10 @@ export function AddItemDialog() {
name="itemName"
render={({ field }) => (
<FormItem>
- <FormLabel>Item Name</FormLabel>
+ <FormLabel>자재그룹명 *</FormLabel>
<FormControl>
<Input
- placeholder="e.g."
+ placeholder="예: 육각볼트 M8x20"
{...field}
/>
</FormControl>
@@ -122,11 +141,12 @@ export function AddItemDialog() {
name="description"
render={({ field }) => (
<FormItem>
- <FormLabel>Description</FormLabel>
+ <FormLabel>상세</FormLabel>
<FormControl>
<Textarea
- placeholder="e.g."
+ placeholder="예: 표준 육각 볼트 스테인리스 스틸"
{...field}
+ value={field.value || ""}
/>
</FormControl>
<FormMessage />
@@ -134,6 +154,171 @@ export function AddItemDialog() {
)}
/>
+ {/* 2열 레이아웃 */}
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="itemLevel"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>레벨</FormLabel>
+ <FormControl>
+ <Input
+ type="number"
+ min="1"
+ max="5"
+ placeholder="1-5"
+ {...field}
+ value={field.value || ""}
+ onChange={(e) => field.onChange(e.target.value ? parseInt(e.target.value) : null)}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="deleteFlag"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>삭제 플래그</FormLabel>
+ <FormControl>
+ <Select onValueChange={field.onChange} defaultValue={field.value || "N"}>
+ <SelectTrigger>
+ <SelectValue placeholder="선택" />
+ </SelectTrigger>
+ <SelectContent>
+ <SelectItem value="N">N (사용)</SelectItem>
+ <SelectItem value="Y">Y (삭제)</SelectItem>
+ </SelectContent>
+ </Select>
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="parentItemCode"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>부모 아이템 코드</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: BOLT001"
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="steelType"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>강종</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: SS, CS, AL"
+ maxLength={2}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="unitOfMeasure"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>단위</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: EA, SET, KG"
+ maxLength={3}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="baseUnitOfMeasure"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>기본단위</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: EA, SET, KG"
+ maxLength={3}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="gradeMaterial"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>등급 재질</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: Marine Grade A"
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="changeDate"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>변경일자</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="YYYYMMDD"
+ maxLength={8}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
</div>
<DialogFooter>
diff --git a/lib/items/table/import-excel-button.tsx b/lib/items/table/import-excel-button.tsx
index 845f5fe2..7509143b 100644
--- a/lib/items/table/import-excel-button.tsx
+++ b/lib/items/table/import-excel-button.tsx
@@ -87,7 +87,7 @@ export function ImportItemButton({ onSuccess }: ImportItemButtonProps) {
worksheet.eachRow((row, rowNumber) => {
const values = row.values as (string | null)[];
- if (!headerRow && values.some(v => v === "아이템 코드" || v === "itemCode" || v === "item_code")) {
+ if (!headerRow && values.some(v => v === "자재그룹코드" || v === "itemCode" || v === "item_code")) {
headerRowIndex = rowNumber;
headerRow = row;
headerValues = [...values];
@@ -106,17 +106,25 @@ export function ImportItemButton({ onSuccess }: ImportItemButtonProps) {
}
});
- // 필수 헤더 확인
- const requiredHeaders = ["아이템 코드", "아이템 명", "설명"];
- const alternativeHeaders = {
- "아이템 코드": ["itemCode", "item_code"],
- "아이템 명": ["itemName", "item_name"],
- "설명": ["description"]
+ // 필수 헤더 확인 (새로운 컬럼 구조에 맞게 수정)
+ const requiredHeaders = ["자재그룹코드", "자재그룹명"];
+ const alternativeHeaders: Record<string, string[]> = {
+ "자재그룹코드": ["itemCode", "item_code"],
+ "자재그룹명": ["itemName", "item_name"],
+ "상세": ["description"],
+ "부모 아이템 코드": ["parentItemCode", "parent_item_code"],
+ "레벨": ["itemLevel", "item_level"],
+ "삭제 플래그": ["deleteFlag", "delete_flag"],
+ "단위": ["unitOfMeasure", "unit_of_measure"],
+ "강종": ["steelType", "steel_type"],
+ "등급 재질": ["gradeMaterial", "grade_material"],
+ "변경일자": ["changeDate", "change_date"],
+ "기본단위": ["baseUnitOfMeasure", "base_unit_of_measure"]
};
// 헤더 매핑 확인 (대체 이름 포함)
const missingHeaders = requiredHeaders.filter(header => {
- const alternatives = alternativeHeaders[header as keyof typeof alternativeHeaders] || [];
+ const alternatives = alternativeHeaders[header] || [];
return !(header in headerMapping) &&
!alternatives.some(alt => alt in headerMapping);
});
@@ -126,11 +134,11 @@ export function ImportItemButton({ onSuccess }: ImportItemButtonProps) {
}
// 데이터 행 추출 (헤더 이후 행부터)
- const dataRows: Record<string, any>[] = [];
+ const dataRows: Record<string, unknown>[] = [];
worksheet.eachRow((row, rowNumber) => {
if (rowNumber > headerRowIndex) {
- const rowData: Record<string, any> = {};
+ const rowData: Record<string, unknown> = {};
const values = row.values as (string | null | undefined)[];
// 헤더 매핑에 따라 데이터 추출
diff --git a/lib/items/table/import-item-handler.tsx b/lib/items/table/import-item-handler.tsx
index 541d6fe1..170ba24c 100644
--- a/lib/items/table/import-item-handler.tsx
+++ b/lib/items/table/import-item-handler.tsx
@@ -5,9 +5,17 @@ import { createItem } from "../service" // 아이템 생성 서버 액션
// 아이템 데이터 검증을 위한 Zod 스키마
const itemSchema = z.object({
- itemCode: z.string().min(1, "아이템 코드는 필수입니다"),
- itemName: z.string().min(1, "아이템 명은 필수입니다"),
+ itemCode: z.string().min(1, "자재그룹코드는 필수입니다"),
+ itemName: z.string().min(1, "자재그룹명은 필수입니다"),
description: z.string().nullable().optional(),
+ parentItemCode: z.string().max(18).nullable().optional(),
+ itemLevel: z.number().int().min(1).max(5).nullable().optional(),
+ deleteFlag: z.string().max(1).nullable().optional(),
+ unitOfMeasure: z.string().max(3).nullable().optional(),
+ steelType: z.string().max(2).nullable().optional(),
+ gradeMaterial: z.string().max(50).nullable().optional(),
+ changeDate: z.string().max(8).nullable().optional(),
+ baseUnitOfMeasure: z.string().max(3).nullable().optional(),
});
interface ProcessResult {
@@ -20,7 +28,7 @@ interface ProcessResult {
* Excel 파일에서 가져온 아이템 데이터 처리하는 함수
*/
export async function processFileImport(
- jsonData: any[],
+ jsonData: Record<string, unknown>[],
progressCallback?: (current: number, total: number) => void
): Promise<ProcessResult> {
// 결과 카운터 초기화
@@ -54,15 +62,31 @@ export async function processFileImport(
try {
// 필드 매핑 (한글/영문 필드명 모두 지원)
- const itemCode = row["아이템 코드"] || row["itemCode"] || row["item_code"] || "";
- const itemName = row["아이템 명"] || row["itemName"] || row["item_name"] || "";
- const description = row["설명"] || row["description"] || null;
+ const itemCode = row["자재그룹코드"] || row["itemCode"] || row["item_code"] || "";
+ const itemName = row["자재그룹명"] || row["itemName"] || row["item_name"] || "";
+ const description = row["상세"] || row["description"] || null;
+ const parentItemCode = row["부모 아이템 코드"] || row["parentItemCode"] || row["parent_item_code"] || null;
+ const itemLevel = row["레벨"] || row["itemLevel"] || row["item_level"] || null;
+ const deleteFlag = row["삭제 플래그"] || row["deleteFlag"] || row["delete_flag"] || null;
+ const unitOfMeasure = row["단위"] || row["unitOfMeasure"] || row["unit_of_measure"] || null;
+ const steelType = row["강종"] || row["steelType"] || row["steel_type"] || null;
+ const gradeMaterial = row["등급 재질"] || row["gradeMaterial"] || row["grade_material"] || null;
+ const changeDate = row["변경일자"] || row["changeDate"] || row["change_date"] || null;
+ const baseUnitOfMeasure = row["기본단위"] || row["baseUnitOfMeasure"] || row["base_unit_of_measure"] || null;
// 데이터 정제
const cleanedRow = {
itemCode: typeof itemCode === 'string' ? itemCode.trim() : String(itemCode).trim(),
itemName: typeof itemName === 'string' ? itemName.trim() : String(itemName).trim(),
description: description ? (typeof description === 'string' ? description : String(description)) : null,
+ parentItemCode: parentItemCode ? (typeof parentItemCode === 'string' ? parentItemCode.trim() : String(parentItemCode).trim()) : null,
+ itemLevel: itemLevel ? (typeof itemLevel === 'number' ? itemLevel : parseInt(String(itemLevel))) : null,
+ deleteFlag: deleteFlag ? (typeof deleteFlag === 'string' ? deleteFlag.trim() : String(deleteFlag).trim()) : null,
+ unitOfMeasure: unitOfMeasure ? (typeof unitOfMeasure === 'string' ? unitOfMeasure.trim() : String(unitOfMeasure).trim()) : null,
+ steelType: steelType ? (typeof steelType === 'string' ? steelType.trim() : String(steelType).trim()) : null,
+ gradeMaterial: gradeMaterial ? (typeof gradeMaterial === 'string' ? gradeMaterial.trim() : String(gradeMaterial).trim()) : null,
+ changeDate: changeDate ? (typeof changeDate === 'string' ? changeDate.trim() : String(changeDate).trim()) : null,
+ baseUnitOfMeasure: baseUnitOfMeasure ? (typeof baseUnitOfMeasure === 'string' ? baseUnitOfMeasure.trim() : String(baseUnitOfMeasure).trim()) : null,
};
// 데이터 유효성 검사
@@ -83,6 +107,14 @@ export async function processFileImport(
itemCode: cleanedRow.itemCode,
itemName: cleanedRow.itemName,
description: cleanedRow.description,
+ parentItemCode: cleanedRow.parentItemCode,
+ itemLevel: cleanedRow.itemLevel,
+ deleteFlag: cleanedRow.deleteFlag,
+ unitOfMeasure: cleanedRow.unitOfMeasure,
+ steelType: cleanedRow.steelType,
+ gradeMaterial: cleanedRow.gradeMaterial,
+ changeDate: cleanedRow.changeDate,
+ baseUnitOfMeasure: cleanedRow.baseUnitOfMeasure,
});
if (result.success || !result.error) {
diff --git a/lib/items/table/item-excel-template.tsx b/lib/items/table/item-excel-template.tsx
index 75338168..7d18bf03 100644
--- a/lib/items/table/item-excel-template.tsx
+++ b/lib/items/table/item-excel-template.tsx
@@ -15,9 +15,17 @@ export async function exportItemTemplate() {
// 컬럼 헤더 정의 및 스타일 적용
worksheet.columns = [
- { header: '아이템 코드', key: 'itemCode', width: 15 },
- { header: '아이템 명', key: 'itemName', width: 30 },
- { header: '설명', key: 'description', width: 50 }
+ { header: '자재그룹코드', key: 'itemCode', width: 15 },
+ { header: '자재그룹명', key: 'itemName', width: 30 },
+ { header: '상세', key: 'description', width: 50 },
+ { header: '부모 아이템 코드', key: 'parentItemCode', width: 18 },
+ { header: '레벨', key: 'itemLevel', width: 10 },
+ { header: '삭제 플래그', key: 'deleteFlag', width: 12 },
+ { header: '단위', key: 'unitOfMeasure', width: 10 },
+ { header: '강종', key: 'steelType', width: 10 },
+ { header: '등급 재질', key: 'gradeMaterial', width: 20 },
+ { header: '변경일자', key: 'changeDate', width: 12 },
+ { header: '기본단위', key: 'baseUnitOfMeasure', width: 12 }
];
// 헤더 스타일 적용
@@ -42,8 +50,32 @@ export async function exportItemTemplate() {
// 샘플 데이터 추가
const sampleData = [
- { itemCode: 'ITEM001', itemName: '샘플 아이템 1', description: '이것은 샘플 아이템 1의 설명입니다.' },
- { itemCode: 'ITEM002', itemName: '샘플 아이템 2', description: '이것은 샘플 아이템 2의 설명입니다.' }
+ {
+ itemCode: 'SAMPLE001',
+ itemName: '샘플1',
+ description: '샘플1',
+ parentItemCode: null,
+ itemLevel: 1,
+ deleteFlag: 'N',
+ unitOfMeasure: 'EA',
+ steelType: 'SS',
+ gradeMaterial: 'gradeMaterial',
+ changeDate: '20241201',
+ baseUnitOfMeasure: 'EA'
+ },
+ {
+ itemCode: 'SAMPLE002',
+ itemName: '샘플2',
+ description: '샘플2',
+ parentItemCode: 'SAMPLE001',
+ itemLevel: 2,
+ deleteFlag: 'N',
+ unitOfMeasure: 'EA',
+ steelType: 'SS',
+ gradeMaterial: 'gradeMaterial',
+ changeDate: '20241201',
+ baseUnitOfMeasure: 'EA'
+ }
];
// 데이터 행 추가
diff --git a/lib/items/table/items-table.tsx b/lib/items/table/items-table.tsx
index bbbafc2f..2bc1c913 100644
--- a/lib/items/table/items-table.tsx
+++ b/lib/items/table/items-table.tsx
@@ -74,17 +74,58 @@ export function ItemsTable({ promises }: ItemsTableProps) {
*/
const advancedFilterFields: DataTableAdvancedFilterField<Item>[] = [
{
+ id: "itemLevel",
+ label: "레벨",
+ type: "number",
+ },
+ {
id: "itemCode",
- label: "Item Code",
+ label: "자재그룹코드",
type: "text",
},
{
id: "itemName",
- label: "Item Name",
+ label: "자재그룹이름",
type: "text",
- }, {
+ },
+ {
id: "description",
- label: "Description",
+ label: "상세",
+ type: "text",
+ },
+ {
+ id: "parentItemCode",
+ label: "부모 아이템 코드",
+ type: "text",
+ },
+ {
+ id: "deleteFlag",
+ label: "삭제 플래그",
+ type: "text",
+ },
+ {
+ id: "unitOfMeasure",
+ label: "단위",
+ type: "text",
+ },
+ {
+ id: "steelType",
+ label: "강종",
+ type: "text",
+ },
+ {
+ id: "gradeMaterial",
+ label: "등급 재질",
+ type: "text",
+ },
+ {
+ id: "changeDate",
+ label: "변경일자",
+ type: "text",
+ },
+ {
+ id: "baseUnitOfMeasure",
+ label: "기본단위",
type: "text",
},
]
diff --git a/lib/items/table/update-item-sheet.tsx b/lib/items/table/update-item-sheet.tsx
index 4bcdbfcb..fcdc0a70 100644
--- a/lib/items/table/update-item-sheet.tsx
+++ b/lib/items/table/update-item-sheet.tsx
@@ -1,7 +1,6 @@
"use client"
import * as React from "react"
-import { tasks, type Task } from "@/db/schema/tasks"
import { zodResolver } from "@hookform/resolvers/zod"
import { Loader } from "lucide-react"
import { useForm } from "react-hook-form"
@@ -19,7 +18,6 @@ import {
import {
Select,
SelectContent,
- SelectGroup,
SelectItem,
SelectTrigger,
SelectValue,
@@ -35,8 +33,6 @@ import {
} from "@/components/ui/sheet"
import { Textarea } from "@/components/ui/textarea"
-import { modifiTask } from "@/lib//tasks/service"
-import { updateTaskSchema, type UpdateTaskSchema } from "@/lib/tasks/validations"
import { Item } from "@/db/schema/items"
import { updateItemSchema, UpdateItemSchema } from "../validations"
import { modifyItem } from "../service"
@@ -57,7 +53,14 @@ export function UpdateItemSheet({ item, ...props }: UpdateItemSheetProps) {
itemCode: item?.itemCode ?? "",
itemName: item?.itemName ?? "",
description: item?.description ?? "",
-
+ parentItemCode: item?.parentItemCode ?? "",
+ itemLevel: item?.itemLevel ?? null,
+ deleteFlag: item?.deleteFlag ?? "",
+ unitOfMeasure: item?.unitOfMeasure ?? "",
+ steelType: item?.steelType ?? "",
+ gradeMaterial: item?.gradeMaterial ?? "",
+ changeDate: item?.changeDate ?? "",
+ baseUnitOfMeasure: item?.baseUnitOfMeasure ?? "",
},
})
@@ -68,6 +71,14 @@ export function UpdateItemSheet({ item, ...props }: UpdateItemSheetProps) {
itemCode: item.itemCode ?? "",
itemName: item.itemName ?? "",
description: item.description ?? "",
+ parentItemCode: item.parentItemCode ?? "",
+ itemLevel: item.itemLevel ?? null,
+ deleteFlag: item.deleteFlag ?? "",
+ unitOfMeasure: item.unitOfMeasure ?? "",
+ steelType: item.steelType ?? "",
+ gradeMaterial: item.gradeMaterial ?? "",
+ changeDate: item.changeDate ?? "",
+ baseUnitOfMeasure: item.baseUnitOfMeasure ?? "",
});
}
}, [item, form]);
@@ -111,10 +122,9 @@ export function UpdateItemSheet({ item, ...props }: UpdateItemSheetProps) {
name="itemCode"
render={({ field }) => (
<FormItem>
- <FormLabel>Item Code</FormLabel>
+ <FormLabel>자재그룹코드</FormLabel>
<FormControl>
- <Input placeholder="e.g." {...field} />
-
+ <Input placeholder="예: BOLT001" {...field} />
</FormControl>
<FormMessage />
</FormItem>
@@ -125,10 +135,10 @@ export function UpdateItemSheet({ item, ...props }: UpdateItemSheetProps) {
name="itemName"
render={({ field }) => (
<FormItem>
- <FormLabel>Item Name</FormLabel>
+ <FormLabel>자재그룹명</FormLabel>
<FormControl>
<Input
- placeholder="e.g."
+ placeholder="예: 육각볼트 M8x20"
{...field}
/>
</FormControl>
@@ -142,10 +152,10 @@ export function UpdateItemSheet({ item, ...props }: UpdateItemSheetProps) {
name="description"
render={({ field }) => (
<FormItem>
- <FormLabel>Description</FormLabel>
+ <FormLabel>상세</FormLabel>
<FormControl>
<Textarea
- placeholder="e.g."
+ placeholder="예: 표준 육각 볼트 스테인리스 스틸"
{...field}
/>
</FormControl>
@@ -154,6 +164,171 @@ export function UpdateItemSheet({ item, ...props }: UpdateItemSheetProps) {
)}
/>
+ {/* 2열 레이아웃 */}
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="itemLevel"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>레벨</FormLabel>
+ <FormControl>
+ <Input
+ type="number"
+ min="1"
+ max="5"
+ placeholder="1-5"
+ {...field}
+ value={field.value ?? ""}
+ onChange={(e) => field.onChange(e.target.value ? parseInt(e.target.value) : null)}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="deleteFlag"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>삭제 플래그</FormLabel>
+ <FormControl>
+ <Select onValueChange={field.onChange} value={field.value || ""}>
+ <SelectTrigger>
+ <SelectValue placeholder="선택" />
+ </SelectTrigger>
+ <SelectContent>
+ <SelectItem value="N">N (사용)</SelectItem>
+ <SelectItem value="Y">Y (삭제)</SelectItem>
+ </SelectContent>
+ </Select>
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="parentItemCode"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>부모 아이템 코드</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: BOLT001"
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="steelType"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>강종</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: SS, CS, AL"
+ maxLength={2}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="unitOfMeasure"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>단위</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: EA, SET, KG"
+ maxLength={3}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="baseUnitOfMeasure"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>기본단위</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: EA, SET, KG"
+ maxLength={3}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
+ <div className="grid grid-cols-2 gap-4">
+ <FormField
+ control={form.control}
+ name="gradeMaterial"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>등급 재질</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="예: Marine Grade A"
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+
+ <FormField
+ control={form.control}
+ name="changeDate"
+ render={({ field }) => (
+ <FormItem>
+ <FormLabel>변경일자</FormLabel>
+ <FormControl>
+ <Input
+ placeholder="YYYYMMDD"
+ maxLength={8}
+ {...field}
+ value={field.value || ""}
+ />
+ </FormControl>
+ <FormMessage />
+ </FormItem>
+ )}
+ />
+ </div>
+
<SheetFooter className="gap-2 pt-2 sm:space-x-0">
<SheetClose asChild>
<Button type="button" variant="outline">
diff --git a/lib/items/validations.ts b/lib/items/validations.ts
index d299959c..14fc27b1 100644
--- a/lib/items/validations.ts
+++ b/lib/items/validations.ts
@@ -22,6 +22,14 @@ export const searchParamsCache = createSearchParamsCache({
itemCode: parseAsString.withDefault(""),
itemName: parseAsString.withDefault(""),
description: parseAsString.withDefault(""),
+ parentItemCode: parseAsString.withDefault(""),
+ itemLevel: parseAsInteger.withDefault(5),
+ deleteFlag: parseAsString.withDefault(""),
+ unitOfMeasure: parseAsString.withDefault(""),
+ steelType: parseAsString.withDefault(""),
+ gradeMaterial: parseAsString.withDefault(""),
+ changeDate: parseAsString.withDefault(""),
+ baseUnitOfMeasure: parseAsString.withDefault(""),
// advanced filter
filters: getFiltersStateParser().withDefault([]),
@@ -31,15 +39,31 @@ export const searchParamsCache = createSearchParamsCache({
})
export const createItemSchema = z.object({
- itemCode: z.string(),
- itemName: z.string(),
- description: z.string(),
+ itemCode: z.string().min(1, "아이템 코드는 필수입니다"),
+ itemName: z.string().min(1, "아이템명은 필수입니다"),
+ description: z.string().nullable().optional(),
+ parentItemCode: z.string().max(18).nullable().optional(),
+ itemLevel: z.number().int().min(1).max(5).nullable().optional(),
+ deleteFlag: z.string().max(1).nullable().optional(),
+ unitOfMeasure: z.string().max(3).nullable().optional(),
+ steelType: z.string().max(2).nullable().optional(),
+ gradeMaterial: z.string().max(50).nullable().optional(),
+ changeDate: z.string().max(8).nullable().optional(),
+ baseUnitOfMeasure: z.string().max(3).nullable().optional(),
})
export const updateItemSchema = z.object({
itemCode: z.string().optional(),
itemName: z.string().optional(),
description: z.string().optional(),
+ parentItemCode: z.string().max(18).nullable().optional(),
+ itemLevel: z.number().int().min(1).max(5).nullable().optional(),
+ deleteFlag: z.string().max(1).nullable().optional(),
+ unitOfMeasure: z.string().max(3).nullable().optional(),
+ steelType: z.string().max(2).nullable().optional(),
+ gradeMaterial: z.string().max(50).nullable().optional(),
+ changeDate: z.string().max(8).nullable().optional(),
+ baseUnitOfMeasure: z.string().max(3).nullable().optional(),
})
export type GetItemsSchema = Awaited<ReturnType<typeof searchParamsCache.parse>>
diff --git a/lib/oracle/db.ts b/lib/oracle/db.ts
new file mode 100644
index 00000000..57a5cd49
--- /dev/null
+++ b/lib/oracle/db.ts
@@ -0,0 +1,148 @@
+import oracledb, { Connection, PoolAttributes, Result } from 'oracledb';
+
+// Oracle 자동 커밋 활성화
+oracledb.autoCommit = true;
+
+// 연결 구성 타입
+interface DbConfig extends PoolAttributes {
+ user: string;
+ password: string;
+ connectString: string;
+}
+
+// 환경 변수에서 DB 설정 가져오기
+const dbConfig: DbConfig = {
+ user: process.env.ORACLE_USER || '',
+ password: process.env.ORACLE_PASSWORD || '',
+ connectString: process.env.ORACLE_CONNECTION_STRING || ''
+};
+
+// DB 연결 풀
+let pool: oracledb.Pool | null = null;
+
+/**
+ * DB 연결 풀 초기화
+ */
+async function initialize(): Promise<void> {
+ try {
+ pool = await oracledb.createPool(dbConfig);
+ console.log('Oracle DB 연결 풀이 초기화되었습니다.');
+ } catch (err) {
+ console.error('Oracle DB 풀 초기화 오류:', err);
+ throw err;
+ }
+}
+
+/**
+ * DB 연결 가져오기
+ */
+export async function getConnection(): Promise<Connection> {
+ if (!pool) {
+ await initialize();
+ }
+
+ if (!pool) {
+ throw new Error('DB 풀이 초기화되지 않았습니다.');
+ }
+
+ return pool.getConnection();
+}
+
+/**
+ * 쿼리 실행 함수
+ */
+export async function executeQuery<T = any>(
+ query: string,
+ params: any[] = [],
+ options: oracledb.ExecuteOptions = {}
+): Promise<Result<T>> {
+ let connection: Connection | undefined;
+
+ try {
+ connection = await getConnection();
+
+ const result = await connection.execute<T>(
+ query,
+ params,
+ {
+ outFormat: oracledb.OUT_FORMAT_OBJECT,
+ ...options
+ }
+ );
+
+ return result;
+ } catch (err) {
+ console.error('쿼리 실행 오류:', err);
+ throw err;
+ } finally {
+ if (connection) {
+ try {
+ await connection.close();
+ } catch (err) {
+ console.error('연결 종료 오류:', err);
+ }
+ }
+ }
+}
+
+/**
+ * 테이블 데이터 가져오기 함수
+ */
+export async function getTableData<T = any>(tableName: string): Promise<T[]> {
+ // SQL 인젝션 방지를 위한 테이블 이름 검증
+ // 알파벳, 숫자, 밑줄(_), 달러 기호($), 해시(#) 만 허용
+ const tableNameRegex = /^[a-zA-Z0-9_$#]+$/;
+
+ if (!tableNameRegex.test(tableName)) {
+ throw new Error('유효하지 않은 테이블 이름입니다.');
+ }
+
+ const query = `SELECT * FROM ${tableName}`;
+ const result = await executeQuery<T>(query);
+
+ return result.rows as T[] || [];
+}
+
+/**
+ * 테이블 특정 컬럼 데이터 가져오기 함수
+ */
+export async function getTableColumns<T = any>(
+ tableName: string,
+ columns: string[]
+): Promise<T[]> {
+ // SQL 인젝션 방지
+ const tableNameRegex = /^[a-zA-Z0-9_$#]+$/;
+ if (!tableNameRegex.test(tableName)) {
+ throw new Error('유효하지 않은 테이블 이름입니다.');
+ }
+
+ // 컬럼명 검증
+ const columnNameRegex = /^[a-zA-Z0-9_$#]+$/;
+ const validColumns = columns.filter(col => columnNameRegex.test(col));
+
+ if (validColumns.length === 0) {
+ throw new Error('유효하지 않은 컬럼 이름입니다.');
+ }
+
+ const columnsStr = validColumns.join(', ');
+ const query = `SELECT ${columnsStr} FROM ${tableName}`;
+
+ const result = await executeQuery<T>(query);
+ return result.rows as T[] || [];
+}
+
+/**
+ * 풀 종료 함수
+ */
+export async function closePool(): Promise<void> {
+ if (pool) {
+ try {
+ await pool.close(0);
+ pool = null;
+ console.log('Oracle DB 연결 풀이 종료되었습니다.');
+ } catch (err) {
+ console.error('풀 종료 오류:', err);
+ throw err;
+ }
+ }
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 20:41:51 +0000