1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
import { pgTable, pgView, text, timestamp, integer, varchar, boolean , unique} from 'drizzle-orm/pg-core';
import { vendors } from './vendors';
import { eq, sql } from "drizzle-orm";
import { users } from './users';
export const basicContractTemplates = pgTable('basic_contract_templates', {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
templateName: text('template_name').notNull(),
revision: integer('revision').notNull().default(1), // 리비전
status: text('status').notNull().default('ACTIVE'), // ACTIVE, DISPOSED
fileName: varchar("file_name", { length: 255 }),
filePath: varchar("file_path", { length: 1024 }),
validityPeriod: integer('validity_period'), // 계약 유효기간(개월)
// 법무검토 관련
legalReviewRequired: boolean('legal_review_required').notNull().default(false), // 법무검토 필요 여부
// 감사 정보
createdAt: timestamp('created_at').defaultNow(),
createdBy: integer('created_by').references(() => users.id), // 생성자
updatedAt: timestamp('updated_at').defaultNow(),
updatedBy: integer('updated_by').references(() => users.id), // 수정자
disposedAt: timestamp('disposed_at'), // 폐기일자
restoredAt: timestamp('restored_at'), // 복구일자
}, (table) => ({
// templateName과 revision 조합이 유니크해야 함
templateNameRevisionUnique: unique("template_name_revision_unique").on(table.templateName, table.revision),
}));
export const basicContract = pgTable('basic_contract', {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
templateId: integer('template_id').references(() => basicContractTemplates.id),
vendorId: integer('vendor_id').references(() => vendors.id),
requestedBy: integer('requested_by').references(() => users.id),
status: text('status').notNull().default('PENDING'), // PENDING, COMPLETED, REJECTED
fileName: varchar("file_name", { length: 255 }).notNull(),
filePath: varchar("file_path", { length: 1024 }).notNull(),
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').defaultNow(),
completedAt: timestamp('completed_at'), // 계약 체결 완료 날짜 - 새로 추가할 필드
});
// 기본 계약 요청 뷰
export const basicContractView = pgView('basic_contract_view').as((qb) => {
return qb
.select({
// 기본 계약 정보
id: sql<number>`${basicContract.id}`.as('id'),
templateId: sql<number | null>`${basicContract.templateId}`.as('template_id'),
vendorId: sql<number | null>`${basicContract.vendorId}`.as('vendor_id'),
requestedBy: sql<number | null>`${basicContract.requestedBy}`.as('requested_by'),
status: sql<string>`${basicContract.status}`.as('basic_contract_status'),
createdAt: sql<Date>`${basicContract.createdAt}`.as('created_at'),
updatedAt: sql<Date>`${basicContract.updatedAt}`.as('updated_at'),
completedAt: sql<Date | null>`${basicContract.completedAt}`.as('completed_at'),
// 벤더 정보
vendorCode: sql<string | null>`${vendors.vendorCode}`.as('vendor_code'),
vendorEmail: sql<string | null>`${vendors.email}`.as('vendor_email'),
vendorName: sql<string | null>`${vendors.vendorName}`.as('vendor_name'),
// 요청자 정보
requestedByName: sql<string | null>`${users.name}`.as('requested_by_name'),
// 템플릿 정보
templateName: sql<string | null>`${basicContractTemplates.templateName}`.as('template_name'),
templateRevision: sql<number | null>`${basicContractTemplates.revision}`.as('template_revision'),
templateStatus: sql<string | null>`${basicContractTemplates.status}`.as('template_status'),
validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validity_period'),
legalReviewRequired: sql<boolean | null>`${basicContractTemplates.legalReviewRequired}`.as('legal_review_required'),
// 템플릿 파일 정보
templateFilePath: sql<string | null>`${basicContractTemplates.filePath}`.as('template_file_path'),
templateFileName: sql<string | null>`${basicContractTemplates.fileName}`.as('template_file_name'),
// 서명된 계약서 파일 정보
signedFilePath: sql<string | null>`${basicContract.filePath}`.as('signed_file_path'),
signedFileName: sql<string | null>`${basicContract.fileName}`.as('signed_file_name'),
// 템플릿 감사 정보
templateCreatedAt: sql<Date | null>`${basicContractTemplates.createdAt}`.as('template_created_at'),
templateCreatedBy: sql<number | null>`${basicContractTemplates.createdBy}`.as('template_created_by'),
templateUpdatedAt: sql<Date | null>`${basicContractTemplates.updatedAt}`.as('template_updated_at'),
templateUpdatedBy: sql<number | null>`${basicContractTemplates.updatedBy}`.as('template_updated_by'),
templateDisposedAt: sql<Date | null>`${basicContractTemplates.disposedAt}`.as('template_disposed_at'),
templateRestoredAt: sql<Date | null>`${basicContractTemplates.restoredAt}`.as('template_restored_at'),
})
.from(basicContract)
.leftJoin(vendors, eq(basicContract.vendorId, vendors.id))
.leftJoin(users, eq(basicContract.requestedBy, users.id))
.leftJoin(basicContractTemplates, eq(basicContract.templateId, basicContractTemplates.id));
});
// 타입 정의
export type BasicContractTemplate = typeof basicContractTemplates.$inferSelect;
export type BasicContract = typeof basicContract.$inferSelect;
export type BasicContractView = typeof basicContractView.$inferSelect;
|
