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, 1 insertions, 307 deletions
diff --git a/db/migrations/0097_poa_initial_setup.sql b/db/migrations/0097_poa_initial_setup.sql
deleted file mode 100644
index fae3f4d1..00000000
--- a/db/migrations/0097_poa_initial_setup.sql
+++ /dev/null
@@ -1,95 +0,0 @@
--- 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 c14921bb..10721b4d 100644
--- a/db/schema/contract.ts
+++ b/db/schema/contract.ts
@@ -257,106 +257,4 @@ export const contractsDetailView = pgView("contracts_detail_view").as((qb) => {
});
// Type inference for the view
-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
+export type ContractDetail = typeof contractsDetailView.$inferSelect; \ No newline at end of file
diff --git a/db/seeds_2/poaSeed.ts b/db/seeds_2/poaSeed.ts
deleted file mode 100644
index d93cde14..00000000
--- a/db/seeds_2/poaSeed.ts
+++ /dev/null
@@ -1,109 +0,0 @@
-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-09 01:46:53 +0000