summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/migrations/0097_poa_initial_setup.sql95
-rw-r--r--db/schema/contract.ts104
-rw-r--r--db/seeds_2/poaSeed.ts109
3 files changed, 307 insertions, 1 deletions
diff --git a/db/migrations/0097_poa_initial_setup.sql b/db/migrations/0097_poa_initial_setup.sql
new file mode 100644
index 00000000..fae3f4d1
--- /dev/null
+++ b/db/migrations/0097_poa_initial_setup.sql
@@ -0,0 +1,95 @@
+-- Drop existing tables and views
+DROP VIEW IF EXISTS change_orders_detail_view;
+DROP TABLE IF EXISTS change_order_items CASCADE;
+DROP TABLE IF EXISTS change_orders CASCADE;
+DROP VIEW IF EXISTS poa_detail_view;
+DROP TABLE IF EXISTS poa CASCADE;
+
+-- Create POA table
+CREATE TABLE poa (
+ id SERIAL PRIMARY KEY,
+ contract_no VARCHAR(100) NOT NULL,
+ original_contract_no VARCHAR(100) NOT NULL,
+ project_id INTEGER NOT NULL,
+ vendor_id INTEGER NOT NULL,
+ original_contract_name VARCHAR(255) NOT NULL,
+ original_status VARCHAR(50) NOT NULL,
+ delivery_terms TEXT,
+ delivery_date DATE,
+ delivery_location VARCHAR(255),
+ currency VARCHAR(10),
+ total_amount NUMERIC(12,2),
+ discount NUMERIC(12,2),
+ tax NUMERIC(12,2),
+ shipping_fee NUMERIC(12,2),
+ net_total NUMERIC(12,2),
+ change_reason TEXT,
+ approval_status VARCHAR(50) DEFAULT 'PENDING',
+ created_at TIMESTAMP NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
+ CONSTRAINT poa_original_contract_no_contracts_contract_no_fk
+ FOREIGN KEY (original_contract_no)
+ REFERENCES contracts(contract_no)
+ ON DELETE CASCADE,
+ CONSTRAINT poa_project_id_projects_id_fk
+ FOREIGN KEY (project_id)
+ REFERENCES projects(id)
+ ON DELETE CASCADE,
+ CONSTRAINT poa_vendor_id_vendors_id_fk
+ FOREIGN KEY (vendor_id)
+ REFERENCES vendors(id)
+ ON DELETE CASCADE
+);
+
+-- Create POA detail view
+CREATE VIEW poa_detail_view AS
+SELECT
+ -- POA primary information
+ poa.id,
+ poa.contract_no,
+ poa.change_reason,
+ poa.approval_status,
+
+ -- Original PO information
+ poa.original_contract_no,
+ poa.original_contract_name,
+ poa.original_status,
+ c.start_date as original_start_date,
+ c.end_date as original_end_date,
+
+ -- Project information
+ poa.project_id,
+ p.code as project_code,
+ p.name as project_name,
+
+ -- Vendor information
+ poa.vendor_id,
+ v.vendor_name,
+
+ -- Changed delivery details
+ poa.delivery_terms,
+ poa.delivery_date,
+ poa.delivery_location,
+
+ -- Changed financial information
+ poa.currency,
+ poa.total_amount,
+ poa.discount,
+ poa.tax,
+ poa.shipping_fee,
+ poa.net_total,
+
+ -- Timestamps
+ poa.created_at,
+ poa.updated_at,
+
+ -- Electronic signature status
+ EXISTS (
+ SELECT 1
+ FROM contract_envelopes
+ WHERE contract_envelopes.contract_id = poa.id
+ ) as has_signature
+FROM poa
+LEFT JOIN contracts c ON poa.original_contract_no = c.contract_no
+LEFT JOIN projects p ON poa.project_id = p.id
+LEFT JOIN vendors v ON poa.vendor_id = v.id; \ No newline at end of file
diff --git a/db/schema/contract.ts b/db/schema/contract.ts
index 10721b4d..c14921bb 100644
--- a/db/schema/contract.ts
+++ b/db/schema/contract.ts
@@ -257,4 +257,106 @@ export const contractsDetailView = pgView("contracts_detail_view").as((qb) => {
});
// Type inference for the view
-export type ContractDetail = typeof contractsDetailView.$inferSelect; \ No newline at end of file
+export type ContractDetail = typeof contractsDetailView.$inferSelect;
+
+
+
+
+// ============ poa (Purchase Order Amendment) ============
+export const poa = pgTable("poa", {
+ // 주 키
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+ // Form code는 원본과 동일하게 유지
+ contractNo: varchar("contract_no", { length: 100 }).notNull(),
+
+ // 원본 PO 참조
+ originalContractNo: varchar("original_contract_no", { length: 100 })
+ .notNull()
+ .references(() => contracts.contractNo, { onDelete: "cascade" }),
+
+ // 원본 계약 정보
+ projectId: integer("project_id")
+ .notNull()
+ .references(() => projects.id, { onDelete: "cascade" }),
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id, { onDelete: "cascade" }),
+ originalContractName: varchar("original_contract_name", { length: 255 }).notNull(),
+ originalStatus: varchar("original_status", { length: 50 }).notNull(),
+
+ // 변경된 납품 조건
+ deliveryTerms: text("delivery_terms"), // 변경된 납품 조건
+ deliveryDate: date("delivery_date"), // 변경된 납품 기한
+ deliveryLocation: varchar("delivery_location", { length: 255 }), // 변경된 납품 장소
+
+ // 변경된 가격/금액 관련
+ currency: varchar("currency", { length: 10 }), // 변경된 통화
+ 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 }), // 변경된 순 총액
+
+ // 변경 사유
+ changeReason: text("change_reason"),
+
+ // 승인 상태
+ approvalStatus: varchar("approval_status", { length: 50 }).default("PENDING"),
+
+ // 생성/수정 시각
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+})
+
+// 타입 추론
+export type POA = typeof poa.$inferSelect
+
+// ============ poa_detail_view ============
+export const poaDetailView = pgView("poa_detail_view").as((qb) => {
+ return qb
+ .select({
+ // POA primary information
+ id: poa.id,
+ contractNo: poa.contractNo,
+ projectId: contracts.projectId,
+ vendorId: contracts.vendorId,
+ changeReason: poa.changeReason,
+ approvalStatus: poa.approvalStatus,
+
+ // Original PO information
+ originalContractName: sql<string>`${contracts.contractName}`.as('original_contract_name'),
+ originalStatus: sql<string>`${contracts.status}`.as('original_status'),
+ originalStartDate: sql<Date>`${contracts.startDate}`.as('original_start_date'),
+ originalEndDate: sql<Date>`${contracts.endDate}`.as('original_end_date'),
+
+ // Changed delivery details
+ deliveryTerms: poa.deliveryTerms,
+ deliveryDate: poa.deliveryDate,
+ deliveryLocation: poa.deliveryLocation,
+
+ // Changed financial information
+ currency: poa.currency,
+ totalAmount: poa.totalAmount,
+ discount: poa.discount,
+ tax: poa.tax,
+ shippingFee: poa.shippingFee,
+ netTotal: poa.netTotal,
+
+ // Timestamps
+ createdAt: poa.createdAt,
+ updatedAt: poa.updatedAt,
+
+ // Electronic signature status
+ hasSignature: sql<boolean>`EXISTS (
+ SELECT 1
+ FROM ${contractEnvelopes}
+ WHERE ${contractEnvelopes.contractId} = ${poa.id}
+ )`.as('has_signature'),
+ })
+ .from(poa)
+ .leftJoin(contracts, eq(poa.contractNo, contracts.contractNo))
+});
+
+// Type inference for the view
+export type POADetail = typeof poaDetailView.$inferSelect; \ No newline at end of file
diff --git a/db/seeds_2/poaSeed.ts b/db/seeds_2/poaSeed.ts
new file mode 100644
index 00000000..d93cde14
--- /dev/null
+++ b/db/seeds_2/poaSeed.ts
@@ -0,0 +1,109 @@
+import { faker } from "@faker-js/faker"
+import db from "../db"
+import { contracts, poa } from "../schema/contract"
+import { sql } from "drizzle-orm"
+
+export async function seedPOA({ count = 10 } = {}) {
+ try {
+ console.log(`📝 Inserting POA ${count}`)
+
+ // 기존 POA 데이터 삭제 및 ID 시퀀스 초기화
+ await db.delete(poa)
+ await db.execute(sql`ALTER SEQUENCE poa_id_seq RESTART WITH 1;`)
+ console.log("✅ 기존 POA 데이터 삭제 및 ID 초기화 완료")
+
+ // 조선업 맥락에 맞는 예시 문구들
+ const deliveryTermsExamples = [
+ "FOB 부산항",
+ "CIF 상하이항",
+ "DAP 울산조선소",
+ "DDP 거제 옥포조선소",
+ ]
+ const deliveryLocations = [
+ "부산 영도조선소",
+ "울산 본사 도크 #3",
+ "거제 옥포조선소 해양공장",
+ "목포신항 부두",
+ ]
+ const changeReasonExamples = [
+ "납품 일정 조정 필요",
+ "자재 사양 변경",
+ "선박 설계 변경에 따른 수정",
+ "추가 부품 요청",
+ "납품 장소 변경",
+ "계약 조건 재협상"
+ ]
+
+ // 1. 기존 계약(PO) 목록 가져오기
+ const existingContracts = await db.select().from(contracts)
+ console.log(`Found ${existingContracts.length} existing contracts`)
+
+ if (existingContracts.length === 0) {
+ throw new Error("계약(PO) 데이터가 없습니다. 먼저 계약 데이터를 생성해주세요.")
+ }
+
+ // 2. POA 생성
+ for (let i = 0; i < count; i++) {
+ try {
+ // 랜덤으로 원본 계약 선택
+ const originalContract = faker.helpers.arrayElement(existingContracts)
+ console.log(`Selected original contract: ${originalContract.contractNo}`)
+
+ // POA 생성
+ const totalAmount = faker.number.float({ min: 5000000, max: 500000000 })
+ const discount = faker.helpers.maybe(() => faker.number.float({ min: 0, max: 500000 }), { probability: 0.3 })
+ const tax = faker.helpers.maybe(() => faker.number.float({ min: 0, max: 1000000 }), { probability: 0.8 })
+ const shippingFee = faker.helpers.maybe(() => faker.number.float({ min: 0, max: 300000 }), { probability: 0.5 })
+ const netTotal = totalAmount - (discount || 0) + (tax || 0) + (shippingFee || 0)
+
+ const poaData = {
+ // Form code는 원본과 동일하게 유지
+ contractNo: originalContract.contractNo,
+ originalContractNo: originalContract.contractNo,
+ projectId: originalContract.projectId,
+ vendorId: originalContract.vendorId,
+ originalContractName: originalContract.contractName,
+ originalStatus: originalContract.status,
+
+ // 변경 가능한 정보들
+ deliveryTerms: faker.helpers.arrayElement(deliveryTermsExamples),
+ deliveryDate: faker.helpers.maybe(() => faker.date.future().toISOString(), { probability: 0.7 }),
+ deliveryLocation: faker.helpers.arrayElement(deliveryLocations),
+ currency: "KRW",
+ totalAmount: totalAmount.toString(),
+ discount: discount?.toString(),
+ tax: tax?.toString(),
+ shippingFee: shippingFee?.toString(),
+ netTotal: netTotal.toString(),
+ changeReason: faker.helpers.arrayElement(changeReasonExamples),
+ approvalStatus: faker.helpers.arrayElement(["PENDING", "APPROVED", "REJECTED"]),
+ createdAt: new Date(),
+ updatedAt: new Date(),
+ }
+
+ console.log("POA data:", poaData)
+
+ await db.insert(poa).values(poaData)
+ console.log(`Created POA for contract: ${originalContract.contractNo}`)
+ } catch (error) {
+ console.error(`Error creating POA ${i + 1}:`, error)
+ throw error
+ }
+ }
+
+ console.log(`✅ Successfully added ${count} new POAs`)
+ } catch (error) {
+ console.error("Error in seedPOA:", error)
+ throw error
+ }
+}
+
+// 실행
+if (require.main === module) {
+ seedPOA({ count: 5 })
+ .then(() => process.exit(0))
+ .catch((error) => {
+ console.error(error)
+ process.exit(1)
+ })
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 10:19:44 +0000