diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/basicContractDocumnet.ts | 142 | ||||
| -rw-r--r-- | db/schema/bidding.ts | 8 | ||||
| -rw-r--r-- | db/schema/legal.ts | 3 |
3 files changed, 145 insertions, 8 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 diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts index 02e869b4..723bee32 100644 --- a/db/schema/bidding.ts +++ b/db/schema/bidding.ts @@ -150,6 +150,7 @@ export const biddings = pgTable('biddings', { currency: varchar('currency', { length: 3 }).default('KRW'), // 통화 budget: decimal('budget', { precision: 15, scale: 2 }), // 예산 targetPrice: decimal('target_price', { precision: 15, scale: 2 }), // 내정가 + targetPriceCalculationCriteria: text('target_price_calculation_criteria'), // 내정가 산정 기준 finalBidPrice: decimal('final_bid_price', { precision: 15, scale: 2 }), // 최종입찰가 // PR 정보 @@ -252,10 +253,10 @@ export const prItemsForBidding = pgTable('pr_items_for_bidding', { updatedAt: timestamp('updated_at').defaultNow().notNull(), }) -// 12. 입찰 조건 테이블 (구매자가 제시하는 조건들) +// 12. 입찰 조건 테이블 (SHI 구매자가 제시하는 조건들) export const biddingConditions = pgTable('bidding_conditions', { id: serial('id').primaryKey(), - biddingId: integer('bidding_id').references(() => biddings.id).notNull(), + biddingCompanyId: integer('bidding_company_id').references(() => biddingCompanies.id).notNull(), // 지급조건 paymentTerms: text('payment_terms'), // 지급조건 옵션들 (JSON 배열) @@ -299,7 +300,8 @@ export const biddingCompanies = pgTable('bidding_companies', { finalQuoteAmount: decimal('final_quote_amount', { precision: 15, scale: 2 }), finalQuoteSubmittedAt: timestamp('final_quote_submitted_at'), isWinner: boolean('is_winner').default(false), // 낙찰 여부 - + isAttendingMeeting: boolean('is_attending_meeting'), // 사양설명회 참석 여부 + // 기타 notes: text('notes'), // 특이사항 contactPerson: varchar('contact_person', { length: 100 }), // 업체 담당자 diff --git a/db/schema/legal.ts b/db/schema/legal.ts index e4880517..3047bc2a 100644 --- a/db/schema/legal.ts +++ b/db/schema/legal.ts @@ -1,6 +1,7 @@ import { pgView,pgTable, serial, varchar, boolean, date, text, timestamp, integer, decimal } from 'drizzle-orm/pg-core'; import { vendors } from './vendors'; import { eq , sql, relations} from "drizzle-orm"; +import { basicContract } from './basicContractDocumnet'; // 법무 업무 테이블 export const legalWorks = pgTable('legal_works', { @@ -38,6 +39,8 @@ export const legalWorks = pgTable('legal_works', { // 메타데이터 createdAt: timestamp('created_at').defaultNow().notNull(), updatedAt: timestamp('updated_at').defaultNow().notNull(), + basicContractId: integer("basic_contract_id") + .references(() => basicContract.id, { onDelete: "set null" }), }); // 타입 정의 |