(대표님, 최겸) 기본계약, 입찰, 파일라우트, 계약서명라우트, 인포메이션, 메뉴설정, PQ(메일템플릿 관련)

1 files changed, 137 insertions, 5 deletions

diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts
index 869ce538..7a9c972d 100644
--- a/db/schema/basicContractDocumnet.ts
+++ b/db/schema/basicContractDocumnet.ts
@@ -1,4 +1,4 @@
-import { pgTable, pgView, text, timestamp, integer, varchar, boolean , unique} from 'drizzle-orm/pg-core';
+import { pgTable, pgView, text, timestamp, integer, varchar, boolean , unique, date} from 'drizzle-orm/pg-core';
 import { vendors } from './vendors';
 import { eq, sql } from "drizzle-orm";
 import { users } from './users';
@@ -34,16 +34,34 @@ export const basicContract = pgTable('basic_contract', {
   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
+  
+  // 상태값을 더 세분화
+  status: text('status').notNull().default('PENDING'), 
+  // PENDING: 초기 발송 상태
+  // VENDOR_SIGNED: 협력업체 서명 완료
+  // BUYER_SIGNED: 구매팀 서명 완료 (협력업체 서명 후)
+  // LEGAL_REVIEW_REQUESTED: 법무검토 요청
+  // LEGAL_REVIEW_COMPLETED: 법무검토 완료
+  // COMPLETED: 모든 과정 완료
+  // REJECTED: 거절됨
+  
   fileName: varchar("file_name", { length: 255 }).notNull(),
   filePath: varchar("file_path", { length: 1024 }).notNull(),
+  
+  // 새로 추가되는 필드들
+  deadline: date('deadline').notNull(), // 마감일 (요청일로부터 10일 후)
+  vendorSignedAt: timestamp('vendor_signed_at'), // 협력업체 서명일
+  buyerSignedAt: timestamp('buyer_signed_at'), // 구매팀 서명일
+  legalReviewRequestedAt: timestamp('legal_review_requested_at'), // 법무검토 요청일
+  legalReviewCompletedAt: timestamp('legal_review_completed_at'), // 법무검토 완료일
+  
   createdAt: timestamp('created_at').defaultNow(),
   updatedAt: timestamp('updated_at').defaultNow(),
-  completedAt: timestamp('completed_at'), // 계약 체결 완료 날짜 - 새로 추가할 필드
+  completedAt: timestamp('completed_at'), // 계약 체결 완료 날짜
 
+  
 });
 
-
 // 기본 계약 요청 뷰
 export const basicContractView = pgView('basic_contract_view').as((qb) => {
   return qb
@@ -54,6 +72,14 @@ export const basicContractView = pgView('basic_contract_view').as((qb) => {
       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'),
+      
+      // 새로 추가된 필드들
+      deadline: sql<string | null>`${basicContract.deadline}`.as('deadline'),
+      vendorSignedAt: sql<Date | null>`${basicContract.vendorSignedAt}`.as('vendor_signed_at'),
+      buyerSignedAt: sql<Date | null>`${basicContract.buyerSignedAt}`.as('buyer_signed_at'),
+      legalReviewRequestedAt: sql<Date | null>`${basicContract.legalReviewRequestedAt}`.as('legal_review_requested_at'),
+      legalReviewCompletedAt: sql<Date | null>`${basicContract.legalReviewCompletedAt}`.as('legal_review_completed_at'),
+      
       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'),
@@ -94,9 +120,115 @@ export const basicContractView = pgView('basic_contract_view').as((qb) => {
     .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;
 
+export const basicContractTemplateStatsView = pgView('basic_contract_template_stats_view').as((qb) => {
+  return qb
+    .select({
+      // 템플릿 기본 정보
+      templateId: sql<number>`${basicContractTemplates.id}`.as('template_id'),
+      templateName: sql<string>`${basicContractTemplates.templateName}`.as('template_name'),
+      revision: sql<number>`${basicContractTemplates.revision}`.as('revision'),
+      legalReviewRequired: sql<boolean>`${basicContractTemplates.legalReviewRequired}`.as('legal_review_required'),
+      validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validity_period'),
+      templateCreatedAt: sql<Date>`${basicContractTemplates.createdAt}`.as('template_created_at'),
+      
+      // 통계 정보
+      // 발송건수 (전체 basicContract 수)
+      totalSentCount: sql<number>`COUNT(${basicContract.id})`.as('total_sent_count'),
+      
+      // 지연건수 (데드라인이 지났는데 완료되지 않은 것)
+      overdueCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.deadline} < CURRENT_DATE 
+               AND ${basicContract.status} NOT IN ('COMPLETED', 'REJECTED') 
+          THEN 1 
+        END)
+      `.as('overdue_count'),
+      
+      // 미서명건수 (PENDING 상태)
+      unsignedCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} = 'PENDING' 
+          THEN 1 
+        END)
+      `.as('unsigned_count'),
+      
+      // 서명완료건수 (협력업체만 - VENDOR_SIGNED 이상)
+      vendorSignedCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} IN ('VENDOR_SIGNED', 'BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED') 
+          THEN 1 
+        END)
+      `.as('vendor_signed_count'),
+      
+      // 법무요청건수
+      legalRequestCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} IN ('LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED') 
+          THEN 1 
+        END)
+      `.as('legal_request_count'),
+      
+      // 법무완료건수
+      legalCompletedCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} = 'LEGAL_REVIEW_COMPLETED' 
+               OR (${basicContract.status} = 'COMPLETED' AND ${basicContract.legalReviewCompletedAt} IS NOT NULL)
+          THEN 1 
+        END)
+      `.as('legal_completed_count'),
+      
+      // 계약완료건수
+      contractCompletedCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} = 'COMPLETED' 
+          THEN 1 
+        END)
+      `.as('contract_completed_count'),
+      
+      // 추가 유용한 통계
+      // 구매팀 서명완료건수
+      buyerSignedCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} IN ('BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED') 
+          THEN 1 
+        END)
+      `.as('buyer_signed_count'),
+      
+      // 거절건수
+      rejectedCount: sql<number>`
+        COUNT(CASE 
+          WHEN ${basicContract.status} = 'REJECTED' 
+          THEN 1 
+        END)
+      `.as('rejected_count'),
+      
+      // 평균 처리시간 (완료된 건만)
+      avgProcessingDays: sql<number>`
+        AVG(CASE 
+          WHEN ${basicContract.status} = 'COMPLETED' AND ${basicContract.completedAt} IS NOT NULL
+          THEN EXTRACT(EPOCH FROM (${basicContract.completedAt} - ${basicContract.createdAt})) / 86400
+        END)
+      `.as('avg_processing_days'),
+      
+      // 최근 활동일
+      lastActivityDate: sql<Date | null>`MAX(${basicContract.updatedAt})`.as('last_activity_date'),
+    })
+    .from(basicContractTemplates)
+    .leftJoin(basicContract, eq(basicContractTemplates.id, basicContract.templateId))
+    .where(eq(basicContractTemplates.status, 'ACTIVE'))
+    .groupBy(
+      basicContractTemplates.id,
+      basicContractTemplates.templateName,
+      basicContractTemplates.revision,
+      basicContractTemplates.legalReviewRequired,
+      basicContractTemplates.validityPeriod,
+      basicContractTemplates.createdAt
+    );
+});
+
+export type BasicContractTemplateStatsView = typeof basicContractTemplateStatsView.$inferSelect;
\ No newline at end of file