(대표님, 최겸) 설계메뉴추가, 작업사항 업데이트

설계메뉴 - 문서관리
설계메뉴 - 벤더 데이터
gtc 메뉴 업데이트
정보시스템 - 메뉴리스트 및 정보 업데이트
파일 라우트 업데이트
엑셀임포트 개선
기본계약 개선
벤더 가입과정 변경 및 개선
벤더 기본정보 - pq
돌체 오류 수정 및 개선
벤더 로그인 과정 이메일 오류 수정

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"),