diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema/contract.ts | 70 | ||||
| -rw-r--r-- | db/seeds/sap-ecc-po-seed.ts | 682 |
2 files changed, 735 insertions, 17 deletions
diff --git a/db/schema/contract.ts b/db/schema/contract.ts index f56fc36e..4790d717 100644 --- a/db/schema/contract.ts +++ b/db/schema/contract.ts @@ -8,7 +8,6 @@ import { date, boolean, unique, - jsonb, uniqueIndex, pgView } from "drizzle-orm/pg-core" import { projects } from "./projects" @@ -31,37 +30,74 @@ export const contracts = pgTable("contracts", { .references(() => vendors.id, { onDelete: "cascade" }), // 계약/PO 번호(유니크) - contractNo: varchar("contract_no", { length: 100 }).notNull().unique(), - contractName: varchar("contract_name", { length: 255 }).notNull(), + contractNo: varchar("contract_no", { length: 100 }).notNull().unique(), // EBELN + contractName: varchar("contract_name", { length: 255 }).notNull(), // ZTITLE // 계약/PO 상태나 기간 - status: varchar("status", { length: 50 }).notNull().default("ACTIVE"), + status: varchar("status", { length: 50 }).notNull().default("ACTIVE"), // ? startDate: date("start_date"), // 발주일(혹은 유효 시작일) endDate: date("end_date"), // 계약 종료일/유효 기간 등 - // --- PO에 자주 쓰이는 필드 추가 --- - paymentTerms: text("payment_terms"), // 지급 조건(예: 30일 후 현금, 선금/잔금 등) - deliveryTerms: text("delivery_terms"), // 납품 조건(Incoterms 등) - deliveryDate: date("delivery_date"), // 납품 기한(납기 예정일) - deliveryLocation: varchar("delivery_location", { length: 255 }), // 납품 장소 + // --- SAP ECC 인터페이스 매핑 필드들 --- + // 기본 PO 정보 + paymentTerms: text("payment_terms"), // 지급 조건 (ZTERM - 지급조건코드) + deliveryTerms: text("delivery_terms"), // 납품 조건 (INCO1 - 인도조건코드) + deliveryDate: date("delivery_date"), // 납품 기한 (ZPO_DLV_DT - PO납기일자, 개별 품목별) + shippmentPlace: varchar("shippment_place", { length: 255 }), // 선적지 (ZSHIPMT_PLC_CD - 선적지코드) + deliveryLocation: varchar("delivery_location", { length: 255 }), // 하역지 (ZUNLD_PLC_CD - 하역지코드) + + // SAP ECC 추가 필드들 + poVersion: integer("po_version"), // PO 버전 (ZPO_VER - 발주버전) + purchaseDocType: varchar("purchase_doc_type", { length: 10 }), // 구매문서유형 (BSART) + purchaseOrg: varchar("purchase_org", { length: 10 }), // 구매조직 (EKORG - 구매조직코드) + purchaseGroup: varchar("purchase_group", { length: 10 }), // 구매그룹 (EKGRP - 구매그룹코드) + exchangeRate: numeric("exchange_rate", { precision: 9, scale: 5 }), // 환율 (WKURS) + poConfirmStatus: varchar("po_confirm_status", { length: 10 }), // PO확인상태 (ZPO_CNFM_STAT) + + // 계약/보증 관련 + contractGuaranteeCode: varchar("contract_guarantee_code", { length: 2 }), // 계약보증코드 (ZCNRT_GRNT_CD) + defectGuaranteeCode: varchar("defect_guarantee_code", { length: 2 }), // 하자보증코드 (ZDFCT_GRNT_CD) + guaranteePeriodCode: varchar("guarantee_period_code", { length: 2 }), // 보증기간코드 (ZGRNT_PRD_CD) + advancePaymentYn: varchar("advance_payment_yn", { length: 1 }), // 선급금여부 (ZPAMT_YN) + + // 금액 관련 (KRW 변환) + budgetAmount: numeric("budget_amount", { precision: 17, scale: 2 }), // 예산금액 (ZBGT_AMT) + budgetCurrency: varchar("budget_currency", { length: 5 }), // 예산통화 (ZBGT_CURR) + totalAmountKrw: numeric("total_amount_krw", { precision: 17, scale: 2 }), // 발주금액KRW (ZPO_AMT_KRW) + + // 전자계약/승인 관련 + electronicContractYn: varchar("electronic_contract_yn", { length: 1 }), // 전자계약필요여부 (ZELC_CNRT_ND_YN) + electronicApprovalDate: date("electronic_approval_date"), // 전자승인일자 (ZELC_AGR_DT) + electronicApprovalTime: varchar("electronic_approval_time", { length: 6 }), // 전자승인시간 (ZELC_AGR_TM) + ownerApprovalYn: varchar("owner_approval_yn", { length: 1 }), // 선주승인필요여부 (ZOWN_AGR_IND_YN) + + // 기타 + plannedInOutFlag: varchar("planned_in_out_flag", { length: 1 }), // 계획내외구분 (ZPLN_INO_GB) + settlementStandard: varchar("settlement_standard", { length: 1 }), // 정산기준 (ZECAL_BSE) + weightSettlementFlag: varchar("weight_settlement_flag", { length: 1 }), // 중량정산구분 (ZWGT_ECAL_GB) + + // 연동제 관련 + priceIndexYn: varchar("price_index_yn", { length: 1 }), // 납품대금연동제대상여부 (ZDLV_PRICE_T) + writtenContractNo: varchar("written_contract_no", { length: 20 }), // 서면계약번호 (ZWEBELN) + contractVersion: integer("contract_version"), // 서면계약차수 (ZVER_NO) // 가격/금액 관련 - currency: varchar("currency", { length: 10 }).default("KRW"), // 통화 (KRW, USD 등) - totalAmount: numeric("total_amount", { precision: 12, scale: 2 }), // 총 계약 금액(아이템 합산 등) - discount: numeric("discount", { precision: 12, scale: 2 }), // 전체 할인 - tax: numeric("tax", { precision: 12, scale: 2 }), // 전체 세금 - shippingFee: numeric("shipping_fee", { precision: 12, scale: 2 }), // 배송비 - netTotal: numeric("net_total", { precision: 12, scale: 2 }), // (합계) - (할인) + (세금) + (배송비) + currency: varchar("currency", { length: 10 }).default("KRW"), // 통화 (KRW, USD 등) // ZPO_CURR + totalAmount: numeric("total_amount", { precision: 12, scale: 2 }), // 총 계약 금액(아이템 합산 등) // ZPO_AMT + discount: numeric("discount", { precision: 12, scale: 2 }), // 전체 할인 // 인터페이스에 없음 (개별 품목별로는 있음) + tax: numeric("tax", { precision: 12, scale: 2 }), // 전체 세금 // 인터페이스에 없음 (개별 품목별로는 있음) + shippingFee: numeric("shipping_fee", { precision: 12, scale: 2 }), // 배송비 // 인터페이스에 없음 (개별 품목별로는 있음) + netTotal: numeric("net_total", { precision: 12, scale: 2 }), // (합계) - (할인) + (세금) + (배송비) // 인터페이스에 없음 (개별 품목별로는 있음) // 부분 납품/부분 결제 가능 여부 partialShippingAllowed: boolean("partial_shipping_allowed").default(false), partialPaymentAllowed: boolean("partial_payment_allowed").default(false), // 추가 메모/비고 - remarks: text("remarks"), + remarks: text("remarks"), // 발주노트 1, 2가 있는데 메모용인것으로 추정 // 버전 관리 (PO 재발행 등) - version: integer("version").default(1), + version: integer("version").default(1), // 생성/수정 시각 createdAt: timestamp("created_at").defaultNow().notNull(), diff --git a/db/seeds/sap-ecc-po-seed.ts b/db/seeds/sap-ecc-po-seed.ts new file mode 100644 index 00000000..ce5130a2 --- /dev/null +++ b/db/seeds/sap-ecc-po-seed.ts @@ -0,0 +1,682 @@ +/** + * SAP ECC PO 데이터 종합 시딩 스크립트 + * + * 이 스크립트는 다음 데이터를 생성합니다: + * 1. 프로젝트 (projects) + * 2. 벤더 (vendors + vendor_contacts) + * 3. 아이템 (items) + * 4. 계약 (contracts + contract_items) + * + * 실행: npm run seed:sap-po + */ + +import { faker } from "@faker-js/faker"; +import db from "@/db/db"; +import { projects } from "@/db/schema/projects"; +import { vendors, vendorContacts, vendorPossibleItems } from "@/db/schema/vendors"; +import { items } from "@/db/schema/items"; +import { contracts, contractItems } from "@/db/schema/contract"; + +// SAP ECC 코드값 매핑 테이블 +const SAP_CODES = { + // 구매문서유형 (BSART) + PURCHASE_DOC_TYPES: [ + { code: "NB", name: "표준 구매오더" }, + { code: "UB", name: "재고 전송 오더" }, + { code: "KB", name: "위탁 구매오더" }, + { code: "LP", name: "서비스 구매오더" }, + ], + + // 구매조직 (EKORG) + PURCHASE_ORGS: [ + { code: "1000", name: "SHI 구매조직" }, + { code: "2000", name: "해외 구매조직" }, + { code: "3000", name: "프로젝트 구매조직" }, + ], + + // 구매그룹 (EKGRP) + PURCHASE_GROUPS: [ + { code: "001", name: "철강재 구매그룹" }, + { code: "002", name: "기계장비 구매그룹" }, + { code: "003", name: "전기전자 구매그룹" }, + { code: "004", name: "서비스 구매그룹" }, + ], + + // 지급조건 (ZTERM) + PAYMENT_TERMS: [ + { code: "Z001", name: "월말결제" }, + { code: "Z002", name: "선급금 30%, 잔금 70%" }, + { code: "Z003", name: "현금결제" }, + { code: "Z004", name: "60일 후 결제" }, + ], + + // 인도조건 (INCO1) + DELIVERY_TERMS: [ + { code: "FOB", name: "FOB" }, + { code: "CIF", name: "CIF" }, + { code: "EXW", name: "EXW" }, + { code: "DDP", name: "DDP" }, + ], + + // 통화 (ZPO_CURR) + CURRENCIES: ["KRW", "USD", "EUR", "JPY"], + + // 계약상태 (ZPO_CNFM_STAT) + CONTRACT_STATUS: [ + { code: "01", name: "승인대기" }, + { code: "02", name: "승인완료" }, + { code: "03", name: "계약완료" }, + { code: "04", name: "진행중" }, + { code: "99", name: "취소됨" }, + ], +}; + +// 프로젝트 데이터 생성 +async function seedProjects() { + console.log("🚀 Seeding projects..."); + + await db.delete(projects); + + const projectsData = [ + { + code: "SHI2024001", + name: "해상풍력 프로젝트 A", + type: "ship", + pspid: "WBS-2024-001-OFFSHORE-A", + }, + { + code: "SHI2024002", + name: "해상풍력 프로젝트 B", + type: "ship", + pspid: "WBS-2024-002-OFFSHORE-B", + }, + { + code: "SHI2024003", + name: "조선 프로젝트 C", + type: "ship", + pspid: "WBS-2024-003-SHIPYARD-C", + }, + { + code: "SHI2024004", + name: "해양플랜트 프로젝트 D", + type: "ship", + pspid: "WBS-2024-004-OFFSHORE-D", + }, + { + code: "SHI2024005", + name: "LNG 선박 프로젝트 E", + type: "ship", + pspid: "WBS-2024-005-LNG-SHIP-E", + }, + ]; + + const insertedProjects = []; + for (const projectData of projectsData) { + const [inserted] = await db.insert(projects).values(projectData).returning(); + insertedProjects.push(inserted); + console.log(` ✅ Project: ${projectData.code} - ${projectData.name}`); + } + + return insertedProjects; +} + +// 벤더 데이터 생성 +async function seedVendors() { + console.log("🚀 Seeding vendors..."); + + await db.delete(vendorPossibleItems); + await db.delete(vendorContacts); + await db.delete(vendors); + + const vendorsData = [ + { + vendorName: "현대제철 주식회사", + vendorCode: "V100001", + taxId: "134-81-00364", + address: "서울특별시 강남구 테헤란로 440", + country: "대한민국", + phone: "02-3457-0114", + email: "contact@hyundai-steel.com", + status: "ACTIVE" as const, + }, + { + vendorName: "포스코 주식회사", + vendorCode: "V100002", + taxId: "220-81-00307", + address: "경상북도 포항시 남구 동해안로 6267", + country: "대한민국", + phone: "054-220-0114", + email: "info@posco.com", + status: "ACTIVE" as const, + }, + { + vendorName: "두산중공업 주식회사", + vendorCode: "V100003", + taxId: "135-81-00473", + address: "경상남도 창원시 성산구 두산볼바르 22", + country: "대한민국", + phone: "055-278-9114", + email: "contact@doosan.com", + status: "ACTIVE" as const, + }, + { + vendorName: "Samsung Heavy Industries Co., Ltd.", + vendorCode: "V100004", + taxId: "135-81-00432", + address: "경상남도 거제시 장평3로 1", + country: "대한민국", + phone: "055-630-0114", + email: "info@shi.samsung.com", + status: "ACTIVE" as const, + }, + { + vendorName: "HD한국조선해양 주식회사", + vendorCode: "V100005", + taxId: "135-81-00364", + address: "울산광역시 동구 방어진순환도로 400", + country: "대한민국", + phone: "052-202-2114", + email: "contact@hd-ksoe.com", + status: "ACTIVE" as const, + }, + ]; + + const insertedVendors = []; + for (const vendorData of vendorsData) { + const [insertedVendor] = await db.insert(vendors).values(vendorData).returning(); + insertedVendors.push(insertedVendor); + + // 각 벤더마다 연락처 2-3개 생성 + const contactCount = faker.number.int({ min: 2, max: 3 }); + for (let i = 0; i < contactCount; i++) { + const contactData = { + vendorId: insertedVendor.id, + contactName: faker.person.fullName({ sex: "male" }), + contactPosition: faker.helpers.arrayElement([ + "구매담당자", "영업담당자", "기술담당자", "품질담당자", "대표이사" + ]), + contactEmail: faker.internet.email(), + contactPhone: faker.phone.number(), + isPrimary: i === 0, + }; + await db.insert(vendorContacts).values(contactData); + } + + console.log(` ✅ Vendor: ${vendorData.vendorCode} - ${vendorData.vendorName}`); + } + + return insertedVendors; +} + +// 아이템 데이터 생성 (더 많은 아이템 추가) +async function seedItems() { + console.log("🚀 Seeding items..."); + + await db.delete(items); + + const itemsData = [ + // 철강재 관련 + { + ProjectNo: "SHI2024001", + itemCode: "STL-H-BEAM-300", + itemName: "H형강 300x300", + packageCode: "STEEL-STRUCT", + smCode: "SM001", + description: "구조용 H형강 300x300x10x15", + parentItemCode: "STL-STRUCT", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "SS", + gradeMaterial: "SS400", + }, + { + ProjectNo: "SHI2024001", + itemCode: "STL-PLATE-20T", + itemName: "강판 20T", + packageCode: "STEEL-PLATE", + smCode: "SM002", + description: "일반구조용 강판 20mm 두께", + parentItemCode: "STL-PLATE", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "KG", + steelType: "SS", + gradeMaterial: "SS400", + }, + { + ProjectNo: "SHI2024001", + itemCode: "STL-ANGLE-100X100", + itemName: "앵글강 100x100", + packageCode: "STEEL-ANGLE", + smCode: "SM003", + description: "등변앵글강 100x100x10", + parentItemCode: "STL-STRUCT", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "M", + steelType: "SS", + gradeMaterial: "SS400", + }, + { + ProjectNo: "SHI2024001", + itemCode: "STL-CHANNEL-200", + itemName: "채널강 200", + packageCode: "STEEL-CHANNEL", + smCode: "SM004", + description: "용접구조용 채널강 200x80x7.5", + parentItemCode: "STL-STRUCT", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "M", + steelType: "SS", + gradeMaterial: "SS400", + }, + + // 배관 관련 + { + ProjectNo: "SHI2024002", + itemCode: "PIPE-CARBON-100A", + itemName: "탄소강관 100A", + packageCode: "PIPE-CARBON", + smCode: "SM005", + description: "배관용 탄소강관 100A SCH40", + parentItemCode: "PIPE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "M", + steelType: "CS", + gradeMaterial: "STPG370", + }, + { + ProjectNo: "SHI2024002", + itemCode: "PIPE-STAINLESS-80A", + itemName: "스테인리스강관 80A", + packageCode: "PIPE-STAINLESS", + smCode: "SM006", + description: "스테인리스강관 80A SCH40", + parentItemCode: "PIPE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "M", + steelType: "SS", // SUS는 2글자 제한 초과, SS로 변경 + gradeMaterial: "SUS304", + }, + { + ProjectNo: "SHI2024002", + itemCode: "PIPE-ELBOW-100A", + itemName: "엘보 100A", + packageCode: "PIPE-FITTING", + smCode: "SM007", + description: "탄소강 엘보 100A 90도", + parentItemCode: "PIPE-FITTING", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "CS", + gradeMaterial: "STPG370", + }, + + // 밸브 관련 + { + ProjectNo: "SHI2024002", + itemCode: "VALVE-GATE-100A", + itemName: "게이트밸브 100A", + packageCode: "VALVE-GATE", + smCode: "SM008", + description: "주철제 게이트밸브 100A 16K", + parentItemCode: "VALVE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "CI", + gradeMaterial: "FC200", + }, + { + ProjectNo: "SHI2024002", + itemCode: "VALVE-BALL-80A", + itemName: "볼밸브 80A", + packageCode: "VALVE-BALL", + smCode: "SM009", + description: "스테인리스 볼밸브 80A 16K", + parentItemCode: "VALVE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "SS", // SUS는 2글자 제한 초과, SS로 변경 + gradeMaterial: "SUS316", + }, + { + ProjectNo: "SHI2024002", + itemCode: "VALVE-CHECK-100A", + itemName: "체크밸브 100A", + packageCode: "VALVE-CHECK", + smCode: "SM010", + description: "주철제 체크밸브 100A 16K", + parentItemCode: "VALVE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "CI", + gradeMaterial: "FC200", + }, + + // 전기/전자 관련 + { + ProjectNo: "SHI2024003", + itemCode: "MOTOR-3PH-15KW", + itemName: "3상 유도전동기 15kW", + packageCode: "MOTOR-3PH", + smCode: "SM011", + description: "3상 유도전동기 15kW 380V 60Hz", + parentItemCode: "MOTOR-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: null, + gradeMaterial: null, + }, + { + ProjectNo: "SHI2024003", + itemCode: "MOTOR-3PH-30KW", + itemName: "3상 유도전동기 30kW", + packageCode: "MOTOR-3PH", + smCode: "SM012", + description: "3상 유도전동기 30kW 380V 60Hz", + parentItemCode: "MOTOR-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: null, + gradeMaterial: null, + }, + { + ProjectNo: "SHI2024003", + itemCode: "CABLE-POWER-4SQ", + itemName: "전력케이블 4SQ", + packageCode: "CABLE-POWER", + smCode: "SM013", + description: "CV 전력케이블 4SQ 4C 600V", + parentItemCode: "CABLE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "M", + steelType: null, + gradeMaterial: null, + }, + { + ProjectNo: "SHI2024003", + itemCode: "PANEL-MCC-1000A", + itemName: "MCC 판넬 1000A", + packageCode: "PANEL-MCC", + smCode: "SM014", + description: "모터제어반 1000A 380V", + parentItemCode: "PANEL-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: null, + gradeMaterial: null, + }, + + // 기계장비 관련 + { + ProjectNo: "SHI2024004", + itemCode: "PUMP-CENTRIFUGAL-100", + itemName: "원심펌프 100m3/h", + packageCode: "PUMP-CENTRIFUGAL", + smCode: "SM015", + description: "원심펌프 100m3/h 50m 15kW", + parentItemCode: "PUMP-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "CS", + gradeMaterial: "WCC", + }, + { + ProjectNo: "SHI2024004", + itemCode: "COMPRESSOR-SCREW-100", + itemName: "스크류압축기 100kW", + packageCode: "COMPRESSOR-SCREW", + smCode: "SM016", + description: "스크류 공기압축기 100kW 8kg/cm2", + parentItemCode: "COMPRESSOR-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: null, + gradeMaterial: null, + }, + { + ProjectNo: "SHI2024005", + itemCode: "CRANE-OVERHEAD-10T", + itemName: "천장크레인 10톤", + packageCode: "CRANE-OVERHEAD", + smCode: "SM017", + description: "천장크레인 10톤 20m span", + parentItemCode: "CRANE-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "EA", + steelType: "SS", + gradeMaterial: "SS400", + }, + { + ProjectNo: "SHI2024005", + itemCode: "CONVEYOR-BELT-500", + itemName: "벨트컨베이어 500mm", + packageCode: "CONVEYOR-BELT", + smCode: "SM018", + description: "벨트컨베이어 500mm폭 50m", + parentItemCode: "CONVEYOR-STD", + itemLevel: 2, + deleteFlag: "N", + unitOfMeasure: "M", + steelType: "SS", + gradeMaterial: "SS400", + }, + ]; + + const insertedItems = []; + for (const itemData of itemsData) { + const [inserted] = await db.insert(items).values(itemData).returning(); + insertedItems.push(inserted); + console.log(` ✅ Item: ${itemData.itemCode} - ${itemData.itemName}`); + } + + return insertedItems; +} + +// 계약 데이터 생성 (SAP ECC 필드 포함) +async function seedContracts(insertedProjects: Array<{ id: number; code: string; name: string }>, insertedVendors: Array<{ id: number; vendorName: string; vendorCode: string }>, insertedItems: Array<{ id: number; itemCode: string; itemName: string; description: string; packageCode: string; gradeMaterial: string | null; unitOfMeasure: string }>) { + console.log("🚀 Seeding contracts with SAP ECC fields..."); + + await db.delete(contractItems); + await db.delete(contracts); + + // 계약 데이터 생성 (더 많은 계약 생성) + const contractsData = []; + + for (let i = 0; i < 15; i++) { + const project = faker.helpers.arrayElement(insertedProjects); + const vendor = faker.helpers.arrayElement(insertedVendors); + const purchaseDocType = faker.helpers.arrayElement(SAP_CODES.PURCHASE_DOC_TYPES); + const purchaseOrg = faker.helpers.arrayElement(SAP_CODES.PURCHASE_ORGS); + const purchaseGroup = faker.helpers.arrayElement(SAP_CODES.PURCHASE_GROUPS); + const paymentTerms = faker.helpers.arrayElement(SAP_CODES.PAYMENT_TERMS); + const deliveryTerms = faker.helpers.arrayElement(SAP_CODES.DELIVERY_TERMS); + const currency = faker.helpers.arrayElement(SAP_CODES.CURRENCIES); + const contractStatus = faker.helpers.arrayElement(SAP_CODES.CONTRACT_STATUS); + + const contractNo = `PO${new Date().getFullYear()}${String(i + 1).padStart(6, '0')}`; + const totalAmount = faker.number.float({ min: 100000, max: 10000000, multipleOf: 0.01 }); + const exchangeRate = currency === 'KRW' ? 1 : faker.number.float({ min: 1100, max: 1400, multipleOf: 0.01 }); + const totalAmountKrw = currency === 'KRW' ? totalAmount : totalAmount * exchangeRate; + + const contractData = { + // 기본 필드 + projectId: project.id, + vendorId: vendor.id, + contractNo, + contractName: `${purchaseDocType.name} - ${faker.commerce.productName()}`, + status: contractStatus.name, + startDate: faker.date.recent({ days: 30 }).toISOString().split('T')[0], + endDate: faker.date.future({ years: 1 }).toISOString().split('T')[0], + + // SAP ECC 기본 필드들 + paymentTerms: paymentTerms.name, + deliveryTerms: deliveryTerms.code, + shippmentPlace: faker.helpers.arrayElement(["부산항", "인천항", "울산항", "포항항"]), + deliveryLocation: faker.helpers.arrayElement(["거제조선소", "울산조선소", "부산신항", "인천항"]), + + // SAP ECC 추가 필드들 + poVersion: faker.number.int({ min: 1, max: 5 }), + purchaseDocType: purchaseDocType.code, + purchaseOrg: purchaseOrg.code, + purchaseGroup: purchaseGroup.code, + exchangeRate: exchangeRate.toString(), + poConfirmStatus: contractStatus.code, + + // 계약/보증 관련 + contractGuaranteeCode: faker.helpers.arrayElement(["1", "2", "3"]), + defectGuaranteeCode: faker.helpers.arrayElement(["1", "2"]), + guaranteePeriodCode: faker.helpers.arrayElement(["1", "2", "3"]), + advancePaymentYn: faker.helpers.arrayElement(["Y", "N"]), + + // 금액 관련 + budgetAmount: (totalAmount * 1.1).toString(), + budgetCurrency: currency, + currency, + totalAmount: totalAmount.toString(), + totalAmountKrw: totalAmountKrw.toString(), + + // 전자계약/승인 관련 + electronicContractYn: faker.helpers.arrayElement(["Y", "N"]), + electronicApprovalDate: faker.helpers.maybe(() => + faker.date.recent({ days: 10 }).toISOString().split('T')[0], { probability: 0.7 } + ), + electronicApprovalTime: faker.helpers.maybe(() => + faker.date.recent().toTimeString().slice(0, 8).replace(/:/g, ''), { probability: 0.7 } + ), + ownerApprovalYn: faker.helpers.arrayElement(["Y", "N"]), + + // 기타 + plannedInOutFlag: faker.helpers.arrayElement(["I", "O"]), + settlementStandard: faker.helpers.arrayElement(["1", "2", "3"]), + weightSettlementFlag: faker.helpers.arrayElement(["Y", "N"]), + + // 연동제 관련 + priceIndexYn: faker.helpers.arrayElement(["Y", "N"]), + writtenContractNo: faker.helpers.maybe(() => `WC${contractNo.slice(2)}`, { probability: 0.6 }), + contractVersion: faker.number.int({ min: 1, max: 3 }), + + netTotal: totalAmount.toString(), + remarks: faker.lorem.sentence(), + version: 1, + }; + + contractsData.push(contractData); + } + + const insertedContracts = []; + for (const contractData of contractsData) { + const [insertedContract] = await db.insert(contracts).values(contractData).returning(); + insertedContracts.push(insertedContract); + + // 각 계약마다 계약 아이템 3-8개 생성 (중복 방지) + const itemCount = faker.number.int({ min: 3, max: 8 }); + const shuffledItems = faker.helpers.shuffle([...insertedItems]); + const selectedItems = shuffledItems.slice(0, Math.min(itemCount, shuffledItems.length)); + + for (const item of selectedItems) { + const quantity = faker.number.int({ min: 1, max: 100 }); + const unitPrice = faker.number.float({ min: 1000, max: 100000, multipleOf: 0.01 }); + const taxRate = faker.helpers.arrayElement([0.00, 10.00]); // 면세 또는 10% 세율 + const taxAmount = (unitPrice * quantity * taxRate) / 100; + const totalLineAmount = (unitPrice * quantity) + taxAmount; + + // SAP ECC 관련 상세 설명 생성 + const detailedDescription = `${item.itemName} - ${item.description} +품목코드: ${item.itemCode} +패키지: ${item.packageCode} +규격: ${faker.helpers.arrayElement(['KS규격', 'JIS규격', 'ASTM규격', 'DIN규격'])} +재질: ${item.gradeMaterial || 'N/A'} +단위: ${item.unitOfMeasure}`; + + // 비고에 SAP 관련 정보 추가 + const sapRemarks = [ + `납기: ${faker.date.future({ years: 1 }).toISOString().split('T')[0]}`, + `검사여부: ${faker.helpers.arrayElement(['필요', '불필요'])}`, + `저장위치: ${faker.helpers.arrayElement(['본사창고', '현장창고', '협력업체'])}`, + `운송조건: ${faker.helpers.arrayElement(['FOB', 'CIF', 'EXW', 'DDP'])}`, + `품질등급: ${faker.helpers.arrayElement(['A급', 'B급', '특급'])}` + ]; + + const itemData = { + contractId: insertedContract.id, + itemId: item.id, + description: detailedDescription, + quantity, + unitPrice: unitPrice.toString(), + taxRate: taxRate.toString(), + taxAmount: taxAmount.toString(), + totalLineAmount: totalLineAmount.toString(), + remark: faker.helpers.maybe(() => + `${faker.helpers.arrayElement(sapRemarks)} | ${faker.lorem.sentence()}`, + { probability: 0.7 } + ), + }; + + await db.insert(contractItems).values(itemData); + } + + console.log(` ✅ Contract: ${contractData.contractNo} - ${contractData.contractName}`); + } + + return insertedContracts; +} + +// 메인 시딩 함수 +async function main() { + try { + console.log("🌱 Starting SAP ECC PO data seeding...\n"); + + const insertedProjects = await seedProjects(); + console.log(`✅ Created ${insertedProjects.length} projects\n`); + + const insertedVendors = await seedVendors(); + console.log(`✅ Created ${insertedVendors.length} vendors\n`); + + const insertedItems = await seedItems(); + console.log(`✅ Created ${insertedItems.length} items\n`); + + const insertedContracts = await seedContracts(insertedProjects, insertedVendors, insertedItems); + console.log(`✅ Created ${insertedContracts.length} contracts\n`); + + console.log("🎉 SAP ECC PO data seeding completed successfully!"); + + // 요약 출력 + console.log("\n📊 Seeding Summary:"); + console.log(` - Projects: ${insertedProjects.length}`); + console.log(` - Vendors: ${insertedVendors.length} (각각 2-3명의 연락처)`); + console.log(` - Items: ${insertedItems.length} (철강재, 배관, 밸브, 전기, 기계장비)`); + console.log(` - Contracts: ${insertedContracts.length} (각각 3-8개의 계약품목)`); + + // 예상 contract_items 수 계산 + const estimatedContractItems = insertedContracts.length * 5.5; // 평균 5.5개 + console.log(` - Contract Items: 약 ${Math.round(estimatedContractItems)}개 (상세 품목정보 포함)`); + + process.exit(0); + } catch (error) { + console.error("❌ Seeding failed:", error); + process.exit(1); + } +} + +// 스크립트 실행 +if (require.main === module) { + main(); +} + +export { main as seedSapEccPoData }; |