diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/basicContractDocumnet.ts | 25 | ||||
| -rw-r--r-- | db/schema/compliance.ts | 173 | ||||
| -rw-r--r-- | db/schema/index.ts | 1 | ||||
| -rw-r--r-- | db/schema/information.ts | 41 | ||||
| -rw-r--r-- | db/schema/vendorDocu.ts | 6 | ||||
| -rw-r--r-- | db/schema/vendors.ts | 6 |
6 files changed, 221 insertions, 31 deletions
diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts index 25069942..869ce538 100644 --- a/db/schema/basicContractDocumnet.ts +++ b/db/schema/basicContractDocumnet.ts @@ -5,7 +5,6 @@ import { users } from './users'; export const basicContractTemplates = pgTable('basic_contract_templates', { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), - // templateCode: varchar("template_code", { length: 50 }).notNull().unique(), // 템플릿 코드 - 제거됨 templateName: text('template_name').notNull(), revision: integer('revision').notNull().default(1), // 리비전 status: text('status').notNull().default('ACTIVE'), // ACTIVE, DISPOSED @@ -16,16 +15,6 @@ export const basicContractTemplates = pgTable('basic_contract_templates', { // 법무검토 관련 legalReviewRequired: boolean('legal_review_required').notNull().default(false), // 법무검토 필요 여부 - // 적용 범위 (각 사업부별) - shipBuildingApplicable: boolean('shipbuilding_applicable').notNull().default(false), // 조선해양 - windApplicable: boolean('wind_applicable').notNull().default(false), // 풍력 - pcApplicable: boolean('pc_applicable').notNull().default(false), // PC - nbApplicable: boolean('nb_applicable').notNull().default(false), // NB - rcApplicable: boolean('rc_applicable').notNull().default(false), // RC - gyApplicable: boolean('gy_applicable').notNull().default(false), // GY - sysApplicable: boolean('sys_applicable').notNull().default(false), // S&Sys - infraApplicable: boolean('infra_applicable').notNull().default(false), // Infra - // 감사 정보 createdAt: timestamp('created_at').defaultNow(), createdBy: integer('created_by').references(() => users.id), // 생성자 @@ -39,6 +28,7 @@ export const basicContractTemplates = pgTable('basic_contract_templates', { })); + export const basicContract = pgTable('basic_contract', { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), templateId: integer('template_id').references(() => basicContractTemplates.id), @@ -76,24 +66,13 @@ export const basicContractView = pgView('basic_contract_view').as((qb) => { // 요청자 정보 requestedByName: sql<string | null>`${users.name}`.as('requested_by_name'), - // 템플릿 정보 (확장된 필드들) - // templateCode: sql<string | null>`${basicContractTemplates.templateCode}`.as('template_code'), + // 템플릿 정보 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'), - // 적용 범위 정보 - shipBuildingApplicable: sql<boolean | null>`${basicContractTemplates.shipBuildingApplicable}`.as('shipbuilding_applicable'), - windApplicable: sql<boolean | null>`${basicContractTemplates.windApplicable}`.as('wind_applicable'), - pcApplicable: sql<boolean | null>`${basicContractTemplates.pcApplicable}`.as('pc_applicable'), - nbApplicable: sql<boolean | null>`${basicContractTemplates.nbApplicable}`.as('nb_applicable'), - rcApplicable: sql<boolean | null>`${basicContractTemplates.rcApplicable}`.as('rc_applicable'), - gyApplicable: sql<boolean | null>`${basicContractTemplates.gyApplicable}`.as('gy_applicable'), - sysApplicable: sql<boolean | null>`${basicContractTemplates.sysApplicable}`.as('sys_applicable'), - infraApplicable: sql<boolean | null>`${basicContractTemplates.infraApplicable}`.as('infra_applicable'), - // 템플릿 파일 정보 templateFilePath: sql<string | null>`${basicContractTemplates.filePath}`.as('template_file_path'), templateFileName: sql<string | null>`${basicContractTemplates.fileName}`.as('template_file_name'), diff --git a/db/schema/compliance.ts b/db/schema/compliance.ts new file mode 100644 index 00000000..b49650b1 --- /dev/null +++ b/db/schema/compliance.ts @@ -0,0 +1,173 @@ +import { pgTable, integer, varchar, text, timestamp, boolean, decimal, serial } from 'drizzle-orm/pg-core'; +import { relations } from 'drizzle-orm'; +import { basicContract } from './basicContractDocumnet'; +import { users } from './users'; + +// 1. 설문조사 템플릿 (어떤 계약 타입에 어떤 설문을 적용할지) +export const complianceSurveyTemplates = pgTable('compliance_survey_templates', { + id: serial('id').primaryKey(), + name: varchar('name', { length: 255 }).notNull(), // '기본 준법 설문', '금융업 준법 설문' 등 + description: text('description'), + version: varchar('version', { length: 50 }).notNull().default('1.0'), + isActive: boolean('is_active').notNull().default(true), + createdAt: timestamp('created_at').defaultNow(), + updatedAt: timestamp('updated_at').defaultNow(), +}); + +// 2. 설문 질문들 +export const complianceQuestions = pgTable('compliance_questions', { + id: serial('id').primaryKey(), + templateId: integer('template_id').references(() => complianceSurveyTemplates.id).notNull(), + questionNumber: varchar('question_number', { length: 10 }).notNull(), // '4', '10-1' 등 + questionText: text('question_text').notNull(), + questionType: varchar('question_type', { length: 20 }).notNull(), + // 'RADIO', 'CHECKBOX', 'TEXT', 'TEXTAREA', 'DROPDOWN', 'FILE', 'PERCENTAGE', 'CONDITIONAL' + isRequired: boolean('is_required').notNull().default(true), + hasDetailText: boolean('has_detail_text').notNull().default(false), // 상세 기술 필요 여부 + hasFileUpload: boolean('has_file_upload').notNull().default(false), // 첨부파일 필요 여부 + parentQuestionId: integer('parent_question_id'), // 조건부 질문의 부모 + conditionalValue: varchar('conditional_value', { length: 100 }), // 부모 질문의 어떤 답변에서 나타날지 + displayOrder: integer('display_order').notNull(), + createdAt: timestamp('created_at').defaultNow(), +}); + +// 3. 선택형 질문의 옵션들 +export const complianceQuestionOptions = pgTable('compliance_question_options', { + id: serial('id').primaryKey(), + questionId: integer('question_id').references(() => complianceQuestions.id).notNull(), + optionValue: varchar('option_value', { length: 100 }).notNull(), // 'YES', 'NO', 'COMPANY_CORP' 등 + optionText: varchar('option_text', { length: 255 }).notNull(), // '네', '아니오', '주식회사/유한회사' 등 + allowsOtherInput: boolean('allows_other_input').notNull().default(false), // '기타' 선택 시 수기입력 가능 + displayOrder: integer('display_order').notNull(), +}); + +// 4. 설문 응답 (기본계약과 연결) +export const complianceResponses = pgTable('compliance_responses', { + id: serial('id').primaryKey(), + basicContractId: integer('basic_contract_id').references(() => basicContract.id).notNull(), + templateId: integer('template_id').references(() => complianceSurveyTemplates.id).notNull(), + status: varchar('status', { length: 20 }).notNull().default('IN_PROGRESS'), // 'IN_PROGRESS', 'COMPLETED', 'REVIEWED' + completedAt: timestamp('completed_at'), + reviewedBy: integer('reviewed_by').references(() => users.id), // 검토자 + reviewedAt: timestamp('reviewed_at'), + reviewNotes: text('review_notes'), // 검토 의견 + createdAt: timestamp('created_at').defaultNow(), + updatedAt: timestamp('updated_at').defaultNow(), +}); + +// 5. 각 질문에 대한 답변 +export const complianceResponseAnswers = pgTable('compliance_response_answers', { + id: serial('id').primaryKey(), + responseId: integer('response_id').references(() => complianceResponses.id).notNull(), + questionId: integer('question_id').references(() => complianceQuestions.id).notNull(), + answerValue: text('answer_value'), // 선택값 또는 입력값 + detailText: text('detail_text'), // 상세 기술 내용 + otherText: varchar('other_text', { length: 500 }), // '기타' 선택 시 수기입력 내용 + percentageValue: decimal('percentage_value', { precision: 5, scale: 2 }), // 지분율 등 + createdAt: timestamp('created_at').defaultNow(), + updatedAt: timestamp('updated_at').defaultNow(), +}); + +// 6. 응답에 첨부된 파일들 +export const complianceResponseFiles = pgTable('compliance_response_files', { + id: serial('id').primaryKey(), + answerId: integer('answer_id').references(() => complianceResponseAnswers.id).notNull(), + fileName: varchar('file_name', { length: 255 }).notNull(), + filePath: varchar('file_path', { length: 1024 }).notNull(), + fileSize: integer('file_size'), + mimeType: varchar('mime_type', { length: 100 }), + uploadedAt: timestamp('uploaded_at').defaultNow(), +}); + +// Relations 정의 +export const complianceSurveyTemplatesRelations = relations(complianceSurveyTemplates, ({ many }) => ({ + questions: many(complianceQuestions), + responses: many(complianceResponses), +})); + +export const complianceQuestionsRelations = relations(complianceQuestions, ({ one, many }) => ({ + template: one(complianceSurveyTemplates, { + fields: [complianceQuestions.templateId], + references: [complianceSurveyTemplates.id], + }), + parentQuestion: one(complianceQuestions, { + fields: [complianceQuestions.parentQuestionId], + references: [complianceQuestions.id], + }), + childQuestions: many(complianceQuestions), + options: many(complianceQuestionOptions), + answers: many(complianceResponseAnswers), +})); + +export const complianceQuestionOptionsRelations = relations(complianceQuestionOptions, ({ one }) => ({ + question: one(complianceQuestions, { + fields: [complianceQuestionOptions.questionId], + references: [complianceQuestions.id], + }), +})); + +export const complianceResponsesRelations = relations(complianceResponses, ({ one, many }) => ({ + basicContract: one(basicContract, { + fields: [complianceResponses.basicContractId], + references: [basicContract.id], + }), + template: one(complianceSurveyTemplates, { + fields: [complianceResponses.templateId], + references: [complianceSurveyTemplates.id], + }), + answers: many(complianceResponseAnswers), +})); + +export const complianceResponseAnswersRelations = relations(complianceResponseAnswers, ({ one, many }) => ({ + response: one(complianceResponses, { + fields: [complianceResponseAnswers.responseId], + references: [complianceResponses.id], + }), + question: one(complianceQuestions, { + fields: [complianceResponseAnswers.questionId], + references: [complianceQuestions.id], + }), + files: many(complianceResponseFiles), +})); + +export const complianceResponseFilesRelations = relations(complianceResponseFiles, ({ one }) => ({ + answer: one(complianceResponseAnswers, { + fields: [complianceResponseFiles.answerId], + references: [complianceResponseAnswers.id], + }), +})); + +// 타입 정의 +export type ComplianceSurveyTemplate = typeof complianceSurveyTemplates.$inferSelect; +export type ComplianceQuestion = typeof complianceQuestions.$inferSelect; +export type ComplianceQuestionOption = typeof complianceQuestionOptions.$inferSelect; +export type ComplianceResponse = typeof complianceResponses.$inferSelect; +export type ComplianceResponseAnswer = typeof complianceResponseAnswers.$inferSelect; +export type ComplianceResponseFile = typeof complianceResponseFiles.$inferSelect; + +// Insert 타입 +export type NewComplianceSurveyTemplate = typeof complianceSurveyTemplates.$inferInsert; +export type NewComplianceQuestion = typeof complianceQuestions.$inferInsert; +export type NewComplianceQuestionOption = typeof complianceQuestionOptions.$inferInsert; +export type NewComplianceResponse = typeof complianceResponses.$inferInsert; +export type NewComplianceResponseAnswer = typeof complianceResponseAnswers.$inferInsert; +export type NewComplianceResponseFile = typeof complianceResponseFiles.$inferInsert; + +// 질문 타입 상수 +export const QUESTION_TYPES = { + RADIO: 'RADIO', // 라디오 버튼 (단일 선택) + CHECKBOX: 'CHECKBOX', // 체크박스 (다중 선택) + TEXT: 'TEXT', // 단일 라인 텍스트 + TEXTAREA: 'TEXTAREA', // 다중 라인 텍스트 + DROPDOWN: 'DROPDOWN', // 드롭다운 선택 + FILE: 'FILE', // 파일 업로드 + PERCENTAGE: 'PERCENTAGE', // 지분율 등 퍼센트 입력 + CONDITIONAL: 'CONDITIONAL', // 조건부 질문 +} as const; + +// 응답 상태 상수 +export const RESPONSE_STATUS = { + IN_PROGRESS: 'IN_PROGRESS', + COMPLETED: 'COMPLETED', + REVIEWED: 'REVIEWED', +} as const;
\ No newline at end of file diff --git a/db/schema/index.ts b/db/schema/index.ts index 9cd71197..7637d247 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -35,6 +35,7 @@ export * from './legal'; export * from './consent'; export * from './bidding'; export * from './vendorRegistrations'; +export * from './compliance'; // 부서별 도메인 할당 관리 export * from './departmentDomainAssignments'; diff --git a/db/schema/information.ts b/db/schema/information.ts index 43d0d0c7..f20723d5 100644 --- a/db/schema/information.ts +++ b/db/schema/information.ts @@ -1,4 +1,5 @@ -import { pgTable, varchar, timestamp, serial, boolean, text as textArea } from "drizzle-orm/pg-core";
+import { pgTable, varchar, timestamp, serial, boolean, text as textArea, integer } from "drizzle-orm/pg-core";
+import { relations } from "drizzle-orm";
// 페이지별 인포메이션 관리 테이블
export const pageInformation = pgTable("page_information", {
@@ -10,11 +11,6 @@ export const pageInformation = pgTable("page_information", { informationContent: textArea("information_content").notNull(), // 설명 내용
- // 첨부파일 정보
- attachmentFileName: varchar("attachment_file_name", { length: 255 }), // 첨부파일 원본명
- attachmentFilePath: varchar("attachment_file_path", { length: 1024 }), // 첨부파일 저장 경로
- attachmentFileSize: varchar("attachment_file_size", { length: 50 }), // 첨부파일 크기
-
// 활성화 여부
isActive: boolean("is_active").default(true).notNull(), // 활성화 여부
@@ -23,5 +19,36 @@ export const pageInformation = pgTable("page_information", { updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
+// 첨부파일 테이블
+export const informationAttachments = pgTable("information_attachments", {
+ id: serial("id").primaryKey(),
+
+ // 인포메이션 ID (외래키)
+ informationId: integer("information_id").notNull().references(() => pageInformation.id, { onDelete: "cascade" }),
+
+ // 파일 정보
+ fileName: varchar("file_name", { length: 255 }).notNull(), // 원본 파일명
+ filePath: varchar("file_path", { length: 1024 }).notNull(), // 저장된 파일 경로
+ fileSize: varchar("file_size", { length: 50 }).notNull(), // 파일 크기
+
+ // 메타데이터
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 관계 설정
+export const pageInformationRelations = relations(pageInformation, ({ many }) => ({
+ attachments: many(informationAttachments),
+}));
+
+export const informationAttachmentsRelations = relations(informationAttachments, ({ one }) => ({
+ information: one(pageInformation, {
+ fields: [informationAttachments.informationId],
+ references: [pageInformation.id],
+ }),
+}));
+
export type PageInformation = typeof pageInformation.$inferSelect;
-export type NewPageInformation = typeof pageInformation.$inferInsert;
\ No newline at end of file +export type NewPageInformation = typeof pageInformation.$inferInsert;
+export type InformationAttachment = typeof informationAttachments.$inferSelect;
+export type NewInformationAttachment = typeof informationAttachments.$inferInsert;
\ No newline at end of file diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index 789f2cd7..b7136a5c 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -194,7 +194,9 @@ export const revisions = pgTable( createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), - registerId: varchar("register_id", { length: 50 }), // 상대 시스템에서 생성한 ID + registerId: varchar("register_id", { length: 50 }), + + serialNo: varchar("serial_no", { length: 50 }), // 상대 시스템에서 생성한 ID }, (table) => { @@ -888,6 +890,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { reviewerId: number | null; reviewerName: string | null; reviewComments: string | null; + serialNo: string | null; createdAt: Date; updatedAt: Date; attachments: Array<{ @@ -959,6 +962,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { 'rejectedDate', r.rejected_date, 'reviewerId', r.reviewer_id, 'reviewerName', r.reviewer_name, + 'serialNo', r.serial_no, 'reviewComments', r.review_comments, 'createdAt', r.created_at, 'updatedAt', r.updated_at, diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts index e5773fc1..d53fb674 100644 --- a/db/schema/vendors.ts +++ b/db/schema/vendors.ts @@ -24,6 +24,8 @@ export const vendors = pgTable("vendors", { //사업자번호이고, 법인등록번호는 corporateRegistrationNumber taxId: varchar("tax_id", { length: 100 }).notNull(), address: text("address"), + addressDetail: text("address_detail"), + postalCode: varchar("postal_code", { length: 20 }), country: varchar("country", { length: 100 }), phone: varchar("phone", { length: 50 }), email: varchar("email", { length: 255 }), @@ -242,6 +244,8 @@ export const vendorDetailView = pgView("vendor_detail_view").as((qb) => { vendorCode: vendors.vendorCode, taxId: vendors.taxId, address: vendors.address, + addressDetail: vendors.addressDetail, + postalCode: vendors.postalCode, businessSize:vendors.businessSize, country: vendors.country, phone: vendors.phone, @@ -421,6 +425,8 @@ export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => { vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"), taxId: sql<string>`${vendors.taxId}`.as("tax_id"), address: sql<string>`${vendors.address}`.as("address"), + addressDetail: sql<string>`${vendors.addressDetail}`.as("address_detail"), + postalCode: sql<string>`${vendors.postalCode}`.as("postal_code"), country: sql<string>`${vendors.country}`.as("country"), phone: sql<string>`${vendors.phone}`.as("phone"), email: sql<string>`${vendors.email}`.as("email"), |