summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/schema/basicContractDocumnet.ts18
-rw-r--r--db/schema/items.ts12
-rw-r--r--db/schema/rfqLast.ts298
3 files changed, 318 insertions, 10 deletions
diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts
index 7fc975a0..0f335e90 100644
--- a/db/schema/basicContractDocumnet.ts
+++ b/db/schema/basicContractDocumnet.ts
@@ -244,4 +244,20 @@ export const basicContractTemplateStatsView = pgView('basic_contract_template_st
);
});
-export type BasicContractTemplateStatsView = typeof basicContractTemplateStatsView.$inferSelect; \ No newline at end of file
+export type BasicContractTemplateStatsView = typeof basicContractTemplateStatsView.$inferSelect;
+
+
+export const buyerSignatures = pgTable('buyer_signatures', {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ name: varchar('name', { length: 255 }).notNull().default('삼성중공업'),
+ imageUrl: text('image_url').notNull(),
+ dataUrl: text('data_url'), // Base64 데이터
+ mimeType: varchar('mime_type', { length: 100 }),
+ fileSize: integer('file_size'),
+ isActive: boolean('is_active').default(true),
+ createdAt: timestamp('created_at').defaultNow().notNull(),
+ updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+export type BuyerSignature = typeof buyerSignatures.$inferSelect;
+export type NewBuyerSignature = typeof buyerSignatures.$inferInsert; \ No newline at end of file
diff --git a/db/schema/items.ts b/db/schema/items.ts
index e50ff491..d7640049 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -4,12 +4,12 @@ import { MATERIAL_GROUP_MASTER } from "./MDG/mdg"
// 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
export const items = pgTable("items", {
id: serial("id").primaryKey(), // 고유 식별자 (신규 추가)
- ProjectNo: varchar("project_no", { length: 100 }).notNull(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
- itemCode: varchar("item_code", { length: 100 }).notNull(), // CLAS_CD - 아이템 코드 (자재 클래스 코드) - unique 제거
- itemName: varchar("item_name", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
- packageCode: varchar("package_code", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
- smCode: varchar("sm_code", { length: 255 }), // CLAS_NM - 아이템명 (자재 클래스명)
- description: text("description"), // CLAS_DTL - 아이템 상세 설명 (클래스 내역)
+ ProjectNo: varchar("project_no", { length: 100 }).notNull(),
+ itemCode: varchar("item_code", { length: 100 }).notNull(),
+ itemName: varchar("item_name", { length: 255 }).notNull(),
+ packageCode: varchar("package_code", { length: 255 }).notNull(),
+ smCode: varchar("sm_code", { length: 255 }),
+ description: text("description"),
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)
diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts
index f643a2fa..b58341c5 100644
--- a/db/schema/rfqLast.ts
+++ b/db/schema/rfqLast.ts
@@ -1,4 +1,4 @@
-import { decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex, unique } from "drizzle-orm/pg-core";
+import {bigint, jsonb, decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex, unique } from "drizzle-orm/pg-core";
import { eq, sql, relations } from "drizzle-orm";
import { projects } from "./projects";
import { users } from "./users";
@@ -159,7 +159,7 @@ export const rfqLastDetails = pgTable(
// 계약 전환 정보 (선택사항)
contractStatus: varchar("contract_status", { length: 30 })
- .$type<"대기" | "진행중" | "완료" | "취소" | null>(),
+ .$type<"대기" | "진행중" | "완료" | "취소" |"입찰진행중"|"일반계약 진행중"| null>(),
contractCreatedAt: timestamp("contract_created_at", { withTimezone: true })
.$type<Date | null>(),
contractNo: varchar("contract_no", { length: 50 }),
@@ -926,4 +926,296 @@ export const vendorSelections = pgTable(
approvalStatusIdx: index("vendor_selections_approval_status_idx").on(table.approvalStatus),
};
}
- ); \ No newline at end of file
+ );
+
+
+
+ export type PurchaseRequestStatus =
+ | "작성중" // 임시저장 상태
+ | "요청완료" // 설계 엔지니어가 최종 제출
+ | "검토중" // 구매팀 검토 중
+ | "승인" // 승인되어 RFQ 생성 가능
+ | "반려" // 반려됨
+ | "RFQ생성완료"; // rfqsLast로 이관 완료
+
+export const purchaseRequests = pgTable(
+ "purchase_requests",
+ {
+ id: serial("id").primaryKey(),
+
+ // 요청 번호 (PR-2025-00001 형식)
+ requestCode: varchar("request_code", { length: 50 }).unique().notNull(),
+
+ // 프로젝트 정보
+ projectId: integer("project_id")
+ .references(() => projects.id, { onDelete: "set null" }),
+ projectCode: varchar("project_code", { length: 100 }),
+ projectName: varchar("project_name", { length: 255 }),
+
+ // ITB 관련 필드
+ projectCompany: varchar("project_company", { length: 255 }),
+ projectSite: varchar("project_site", { length: 255 }),
+ classNo: varchar("class_no", { length: 50 }),
+
+ // 패키지 정보
+ packageNo: varchar("package_no", { length: 50 }),
+ packageName: varchar("package_name", { length: 255 }),
+
+ // 자재 정보
+ majorItemMaterialCategory: varchar("major_item_material_category", { length: 100 }),
+ majorItemMaterialDescription: varchar("major_item_material_description", { length: 255 }),
+ smCode: varchar("sm_code", { length: 255 }),
+
+ // 요청 내용
+ requestTitle: varchar("request_title", { length: 255 }).notNull(),
+ requestDescription: text("request_description"),
+ estimatedBudget: varchar("estimated_budget", { length: 100 }),
+ requestedDeliveryDate: timestamp("requested_delivery_date", { withTimezone: true }),
+
+ // 아이템 목록 (JSON 배열)
+ items: jsonb("items").$type<{
+ id: string;
+ itemCode: string;
+ itemName: string;
+ specification: string;
+ quantity: number;
+ unit: string;
+ estimatedUnitPrice?: number;
+ remarks?: string;
+ }[]>().default([]),
+
+ // 상태 관리
+ status: varchar("status", { length: 30 })
+ .$type<PurchaseRequestStatus>()
+ .default("작성중")
+ .notNull(),
+
+ // 반려 사유
+ rejectReason: text("reject_reason"),
+
+ // 요청 확정 정보
+ confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
+ confirmedBy: integer("confirmed_by")
+ .references(() => users.id, { onDelete: "set null" }),
+
+ // RFQ 생성 정보
+ rfqId: integer("rfq_id"),
+ rfqCode: varchar("rfq_code", { length: 50 }),
+ rfqCreatedAt: timestamp("rfq_created_at", { withTimezone: true }),
+
+ // 담당자 정보
+ engPicId: integer("eng_pic_id")
+ .references(() => users.id, { onDelete: "set null" }),
+ engPicName: varchar("eng_pic_name", { length: 50 }),
+
+ // 구매 담당자
+ purchasePicId: integer("purchase_pic_id")
+ .references(() => users.id, { onDelete: "set null" }),
+ purchasePicName: varchar("purchase_pic_name", { length: 50 }),
+
+ // 메타 정보
+ createdBy: integer("created_by")
+ .notNull()
+ .references(() => users.id, { onDelete: "set null" }),
+ updatedBy: integer("updated_by")
+ .notNull()
+ .references(() => users.id, { onDelete: "set null" }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ }
+);
+
+// 첨부파일 테이블
+export const purchaseRequestAttachments = pgTable(
+ "purchase_request_attachments",
+ {
+ id: serial("id").primaryKey(),
+ requestId: integer("request_id")
+ .notNull()
+ .references(() => purchaseRequests.id, { onDelete: "cascade" }),
+
+ // 파일 정보
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 512 }).notNull(),
+ fileSize: integer("file_size"),
+ fileType: varchar("file_type", { length: 100 }),
+
+ // 첨부파일 분류
+ category: varchar("category", { length: 50 }).notNull().default("설계문서"),
+ description: varchar("description", { length: 500 }),
+
+ // 메타 정보
+ createdBy: integer("created_by")
+ .references(() => users.id, { onDelete: "set null" })
+ .notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ },
+ (table) => ({
+ requestFileIdx: uniqueIndex("request_file_idx").on(table.requestId, table.fileName),
+ })
+);
+
+
+
+export const purchaseRequestsView = pgView("purchase_requests_view", {
+ // 기본 정보
+ id: serial("id"),
+ requestCode: varchar("request_code", { length: 50 }),
+ requestTitle: varchar("request_title", { length: 255 }),
+ requestDescription: text("request_description"),
+
+ // 프로젝트 정보
+ projectId: integer("project_id"),
+ projectCode: varchar("project_code", { length: 100 }),
+ projectName: varchar("project_name", { length: 255 }),
+ projectCompany: varchar("project_company", { length: 255 }),
+ projectSite: varchar("project_site", { length: 255 }),
+ classNo: varchar("class_no", { length: 50 }),
+
+ // 패키지 정보
+ packageNo: varchar("package_no", { length: 50 }),
+ packageName: varchar("package_name", { length: 255 }),
+
+ // 자재 정보
+ majorItemMaterialCategory: varchar("major_item_material_category", { length: 100 }),
+ majorItemMaterialDescription: varchar("major_item_material_description", { length: 255 }),
+ smCode: varchar("sm_code", { length: 255 }),
+
+ // 예산 및 납기
+ estimatedBudget: varchar("estimated_budget", { length: 100 }),
+ requestedDeliveryDate: timestamp("requested_delivery_date", { withTimezone: true }),
+
+ // 아이템 정보
+ items: jsonb("items"),
+ itemCount: integer("item_count"),
+ totalQuantity: numeric("total_quantity"),
+ totalEstimatedAmount: numeric("total_estimated_amount"),
+
+ // 상태 정보
+ status: varchar("status", { length: 30 }),
+ rejectReason: text("reject_reason"),
+
+ // 확정 정보
+ confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
+ confirmedBy: integer("confirmed_by"),
+ confirmedByName: varchar("confirmed_by_name", { length: 100 }),
+
+ // RFQ 정보
+ rfqId: integer("rfq_id"),
+ rfqCode: varchar("rfq_code", { length: 50 }),
+ rfqCreatedAt: timestamp("rfq_created_at", { withTimezone: true }),
+
+ // 담당자 정보
+ engPicId: integer("eng_pic_id"),
+ engPicName: varchar("eng_pic_name", { length: 50 }),
+ engPicEmail: varchar("eng_pic_email", { length: 100 }),
+
+ purchasePicId: integer("purchase_pic_id"),
+ purchasePicName: varchar("purchase_pic_name", { length: 50 }),
+ purchasePicEmail: varchar("purchase_pic_email", { length: 100 }),
+
+ // 첨부파일 정보
+ attachmentCount: bigint("attachment_count", { mode: "number" }),
+
+ // 생성/수정 정보
+ createdBy: integer("created_by"),
+ createdByName: varchar("created_by_name", { length: 100 }),
+ createdByEmail: varchar("created_by_email", { length: 255 }),
+ updatedBy: integer("updated_by"),
+ updatedByName: varchar("updated_by_name", { length: 100 }),
+ updatedByEmail: varchar("updated_by_email", { length: 255 }),
+ createdAt: timestamp("created_at"),
+ updatedAt: timestamp("updated_at"),
+ }).as(sql`
+ SELECT
+ pr.id,
+ pr.request_code,
+ pr.request_title,
+ pr.request_description,
+
+ -- 프로젝트 정보
+ pr.project_id,
+ pr.project_code,
+ pr.project_name,
+ pr.project_company,
+ pr.project_site,
+ pr.class_no,
+
+ -- 패키지 정보
+ pr.package_no,
+ pr.package_name,
+
+ -- 자재 정보
+ pr.major_item_material_category,
+ pr.major_item_material_description,
+ pr.sm_code,
+
+ -- 예산 및 납기
+ pr.estimated_budget,
+ pr.requested_delivery_date,
+
+ -- 아이템 정보
+ pr.items,
+ jsonb_array_length(pr.items) as item_count,
+ (
+ SELECT SUM((item->>'quantity')::numeric)
+ FROM jsonb_array_elements(pr.items) as item
+ ) as total_quantity,
+ (
+ SELECT SUM(
+ (item->>'quantity')::numeric *
+ COALESCE((item->>'estimatedUnitPrice')::numeric, 0)
+ )
+ FROM jsonb_array_elements(pr.items) as item
+ ) as total_estimated_amount,
+
+ -- 상태 정보
+ pr.status,
+ pr.reject_reason,
+
+ -- 확정 정보
+ pr.confirmed_at,
+ pr.confirmed_by,
+ cb.name as confirmed_by_name,
+
+ -- RFQ 정보
+ pr.rfq_id,
+ pr.rfq_code,
+ pr.rfq_created_at,
+
+ -- 담당자 정보
+ pr.eng_pic_id,
+ pr.eng_pic_name,
+ ep.email as eng_pic_email,
+
+ pr.purchase_pic_id,
+ pr.purchase_pic_name,
+ pp.email as purchase_pic_email,
+
+ -- 첨부파일 수
+ (
+ SELECT COUNT(*)
+ FROM purchase_request_attachments pra
+ WHERE pra.request_id = pr.id
+ ) as attachment_count,
+
+ -- 생성/수정 정보
+ pr.created_by,
+ cr.name as created_by_name,
+ cr.email as created_by_email,
+ pr.updated_by,
+ up.name as updated_by_name,
+ up.email as updated_by_email,
+ pr.created_at,
+ pr.updated_at
+
+ FROM purchase_requests pr
+ LEFT JOIN users cb ON cb.id = pr.confirmed_by
+ LEFT JOIN users ep ON ep.id = pr.eng_pic_id
+ LEFT JOIN users pp ON pp.id = pr.purchase_pic_id
+ LEFT JOIN users cr ON cr.id = pr.created_by
+ LEFT JOIN users up ON up.id = pr.updated_by
+ `);
+
+ export type PurchaseRequestView = typeof purchaseRequestsView.$inferSelect; \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 18:47:36 +0000