summaryrefslogtreecommitdiff
path: root/db/schema/bidding.ts
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema/bidding.ts')
-rw-r--r--db/schema/bidding.ts958
1 files changed, 543 insertions, 415 deletions
diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts
index 74603df0..18699633 100644
--- a/db/schema/bidding.ts
+++ b/db/schema/bidding.ts
@@ -10,19 +10,21 @@ import {
decimal,
boolean,
pgEnum,
- uuid,
- date, pgView
+ date,
} from 'drizzle-orm/pg-core'
import { Vendor, vendors } from './vendors'
-import { relations , sql} from 'drizzle-orm';
import { projects } from './projects';
+import { users } from './users';
+
// 입찰공고문 템플릿 (기존)
export const biddingNoticeTemplate = pgTable('bidding_notice_template', {
id: serial('id').primaryKey(),
- type: varchar('type', { length: 50 }).notNull().default('standard'), // 'standard' 고정
- title: varchar('title', { length: 200 }).notNull().default('표준 입찰공고문'),
+ biddingId: integer('bidding_id').references(() => biddings.id), // 연결된 입찰 ID (null이면 템플릿)
+ type: varchar('type', { length: 50 }).notNull().default('standard'), // 입찰공고 타입
+ title: varchar('title', { length: 200 }).notNull().default('입찰공고문'),
content: text('content').notNull().default(''),
+ isTemplate: boolean('is_template').default(false).notNull(), // 템플릿 여부
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
})
@@ -40,7 +42,13 @@ export const biddingStatusEnum = pgEnum('bidding_status', [
'bidding_closed', // 입찰마감
'evaluation_of_bidding', // 입찰평가중
'bidding_disposal', // 유찰
- 'vendor_selected' // 업체선정
+ 'vendor_selected', // 업체선정
+ 'bid_opening', // 개찰
+ 'early_bid_opening', // 조기개찰
+ 'rebidding', // 재입찰
+ 'disposal_cancelled', // 유찰취소
+ 'bid_closure', // 폐찰
+ 'round_increase' // 차수증가
])
// 2. 계약구분 enum
@@ -61,7 +69,6 @@ export const biddingTypeEnum = pgEnum('bidding_type', [
'transport', // 운송
'waste', // 폐기물
'sale', // 매각
- 'steel', // 강재
'other' // 기타(직접입력)
])
@@ -73,15 +80,32 @@ export const awardCountEnum = pgEnum('award_count', [
// 5. 업체 초대/응답 상태 enum
export const invitationStatusEnum = pgEnum('invitation_status', [
- 'pending', // 초대 대기
- 'sent', // 초대 발송
- 'bidding_invited', // 입찰 초대
- 'accepted', // 참여 수락
- 'declined', // 참여 거절
- 'submitted', // 견적 제출 완료
- 'bidding_submitted' // 입찰 제출 완료
+ 'pending', // 초대 대기
+ 'pre_quote_sent', // 사전견적 초대 발송
+ 'pre_quote_accepted', // 사전견적 참여
+ 'pre_quote_declined', // 사전견적 미참여
+ 'pre_quote_submitted', // 사전견적제출완료
+ 'bidding_sent', // 입찰 초대 발송
+ 'bidding_accepted', // 입찰 참여
+ 'bidding_declined', // 입찰 미참여
+ 'bidding_cancelled', // 응찰 취소
+ 'bidding_submitted' // 응찰 완료
])
+// invitationStatus 라벨 맵핑
+export const invitationStatusLabels: Record<string, string> = {
+ pending: '초대 대기',
+ pre_quote_sent: '사전견적 초대 발송',
+ pre_quote_accepted: '사전견적 참여',
+ pre_quote_declined: '사전견적 미참여',
+ pre_quote_submitted: '사전견적제출완료',
+ bidding_sent: '입찰 초대 발송',
+ bidding_accepted: '입찰 참여',
+ bidding_declined: '입찰 미참여',
+ bidding_cancelled: '응찰 취소',
+ bidding_submitted: '응찰 완료'
+}
+
// 6. 문서 타입 enum
export const documentTypeEnum = pgEnum('document_type', [
'notice', // 입찰공고서
@@ -124,8 +148,8 @@ export const weightUnitEnum = pgEnum('weight_unit', [
export const biddings = pgTable('biddings', {
id: serial('id').primaryKey(),
biddingNumber: varchar('bidding_number', { length: 50 }).unique().notNull(), // 입찰 No.
+ originalBiddingNumber: varchar('original_bidding_number', { length: 50 }), // 원입찰번호
revision: integer('revision').default(0), // Rev.
- projectId: integer('project_id').references(() => projects.id),
//견적에서 넘어온 레코드인지, 자체생산인지, 디폴트는 자체생산, notnull
biddingSourceType: varchar('bidding_source_type', { length: 20 }).notNull().default('manual'),
// 기본 정보
@@ -133,16 +157,16 @@ export const biddings = pgTable('biddings', {
itemName: varchar('item_name', { length: 300 }), // 품목명
title: varchar('title', { length: 300 }).notNull(), // 입찰명
description: text('description'),
- content: text('content'), // 입찰공고 내용 (HTML) - 표준 템플릿에서 수정된 내용
+ // 입찰공고 내용은 별도 biddingNotices 테이블에서 관리됨
// 계약 정보
contractType: contractTypeEnum('contract_type').notNull(), // 계약구분
biddingType: biddingTypeEnum('bidding_type').notNull(), // 입찰유형
awardCount: awardCountEnum('award_count').default('single'), // 낙찰수
// contractPeriod: varchar('contract_period', { length: 100 }), // 계약기간
- //시작일
- contractStartDate: date('contract_start_date'),
- //종료일
+ //시작일 (기본값: 현재 날짜)
+ contractStartDate: date('contract_start_date').defaultNow(),
+ //종료일 (기본값: 시작일로부터 1년 후)
contractEndDate: date('contract_end_date'),
// 일정 관리
@@ -170,11 +194,19 @@ export const biddings = pgTable('biddings', {
status: biddingStatusEnum('status').default('bidding_generated').notNull(),
isPublic: boolean('is_public').default(false), // 공개 입찰 여부
isUrgent: boolean('is_urgent').default(false), // 긴급여부
-
- // 담당자 정보
- managerName: varchar('manager_name', { length: 100 }), // 입찰담당자
- managerEmail: varchar('manager_email', { length: 100 }),
- managerPhone: varchar('manager_phone', { length: 20 }),
+
+ // 구매조직 정보
+ purchasingOrganization: varchar('purchasing_organization', { length: 100 }), // 구매조직
+
+ // 담당자 정보 (개선된 구조)
+ bidPicId: integer('bid_pic_id').references(() => users.id), // 입찰담당자 ID
+ bidPicName: varchar('bid_pic_name', { length: 100 }), // 입찰담당자 이름
+ bidPicCode: varchar('bid_pic_code', { length: 50 }), // 입찰담당자 코드
+ supplyPicId: integer('supply_pic_id').references(() => users.id), // 조달담당자 ID
+ supplyPicName: varchar('supply_pic_name', { length: 100 }), // 조달담당자 이름
+ supplyPicCode: varchar('supply_pic_code', { length: 50 }), // 조달담당자 코드
+
+ // 기존 담당자 정보 제거됨 (user FK로 대체)
// 메타 정보
remarks: text('remarks'), // 비고
@@ -236,9 +268,16 @@ export const prItemsForBidding = pgTable('pr_items_for_bidding', {
// 기본 정보
itemNumber: varchar('item_number', { length: 50 }), // 아이템 번호
- projectInfo: varchar('project_info', { length: 300 }), // 프로젝트 정보
+ projectId: integer('project_id').references(() => projects.id), // 프로젝트 ID (새로 추가)
+ projectInfo: varchar('project_info', { length: 300 }), // 프로젝트 정보 (기존, 추후 제거 가능)
itemInfo: varchar('item_info', { length: 300 }), // 품목정보
shi: varchar('shi', { length: 100 }), // SHI
+ // 자재 그룹 정보 (새로 추가)
+ materialGroupNumber: varchar('material_group_number', { length: 100 }), // 자재그룹번호
+ materialGroupInfo: varchar('material_group_info', { length: 300 }), // 자재그룹정보
+ // 자재 정보 (새로 추가)
+ materialNumber: varchar('material_number', { length: 100 }), // 자재번호
+ materialInfo: varchar('material_info', { length: 500 }), // 자재정보
// 납품 일정
requestedDeliveryDate: date('requested_delivery_date'), // 납품요청일
@@ -249,12 +288,41 @@ export const prItemsForBidding = pgTable('pr_items_for_bidding', {
// 수량 및 중량
quantity: decimal('quantity', { precision: 10, scale: 2 }), // 수량
- quantityUnit: varchar('quantity_unit', { length: 50 }), // 수량단위
+ quantityUnit: varchar('quantity_unit', { length: 50 }), // 수량단위 (구매단위)
totalWeight: decimal('total_weight', { precision: 10, scale: 2 }), // 총 중량
- weightUnit: varchar('weight_unit', { length: 50 }), // 중량단위
+ weightUnit: varchar('weight_unit', { length: 50 }), // 중량단위 (자재순중량)
+
+ // 가격 단위 추가
+ priceUnit: varchar('price_unit', { length: 50 }), // 가격단위
+ purchaseUnit: varchar('purchase_unit', { length: 50 }), // 구매단위
+ materialWeight: decimal('material_weight', { precision: 10, scale: 2 }), // 자재순중량
+
+ // WBS 정보
+ wbsCode: varchar('wbs_code', { length: 100 }), // WBS 코드
+ wbsName: varchar('wbs_name', { length: 300 }), // WBS 명칭
+
+ // Cost Center 정보
+ costCenterCode: varchar('cost_center_code', { length: 100 }), // 코스트센터 코드
+ costCenterName: varchar('cost_center_name', { length: 300 }), // 코스트센터 명칭
+
+ // GL Account 정보
+ glAccountCode: varchar('gl_account_code', { length: 100 }), // GL 계정 코드
+ glAccountName: varchar('gl_account_name', { length: 300 }), // GL 계정 명칭
+
+ // 내정 정보 (새로 추가)
+ targetUnitPrice: decimal('target_unit_price', { precision: 15, scale: 2 }), // 내정단가
+ targetAmount: decimal('target_amount', { precision: 15, scale: 2 }), // 내정금액
+ targetCurrency: varchar('target_currency', { length: 3 }).default('KRW'), // 내정통화
+
+ // 예산 정보 (새로 추가)
+ budgetAmount: decimal('budget_amount', { precision: 15, scale: 2 }), // 예산금액
+ budgetCurrency: varchar('budget_currency', { length: 3 }).default('KRW'), // 예산통화
+
+ // 실적 정보 (새로 추가)
+ actualAmount: decimal('actual_amount', { precision: 15, scale: 2 }), // 실적금액
+ actualCurrency: varchar('actual_currency', { length: 3 }).default('KRW'), // 실적통화
- // 상세 정보
- materialDescription: text('material_description'), // 자재내역상세
+
prNumber: varchar('pr_number', { length: 50 }), // PR번호
// SPEC 파일 정보
@@ -280,7 +348,8 @@ export const biddingConditions = pgTable('bidding_conditions', {
isPriceAdjustmentApplicable: boolean('is_price_adjustment_applicable'), // 연동제적용 여부
// 무역조건
- incoterms: text('incoterms'), // Incoterms 옵션들
+ incoterms: text('incoterms'), // Incoterms 옵션들
+ incotermsOption: text('incoterms_option'), // Incoterms 옵션 (추가)
shippingPort: varchar('shipping_port', { length: 200 }), // 선적지
destinationPort: varchar('destination_port', { length: 200 }), // 하역지
@@ -298,7 +367,7 @@ export const biddingCompanies = pgTable('bidding_companies', {
companyId: integer('company_id').references(() => vendors.id).notNull(),
// 초대 및 응답 상태
- invitationStatus: invitationStatusEnum('invitation_status').default('pending').notNull(),
+ invitationStatus: invitationStatusEnum('invitation_status').notNull(),
invitedAt: timestamp('invited_at'),
respondedAt: timestamp('responded_at'),
@@ -314,9 +383,12 @@ export const biddingCompanies = pgTable('bidding_companies', {
isBiddingParticipated: boolean('is_bidding_participated'),//본입찰 참여 여부
finalQuoteAmount: decimal('final_quote_amount', { precision: 15, scale: 2 }),
finalQuoteSubmittedAt: timestamp('final_quote_submitted_at'),
+ isFinalSubmission: boolean('is_final_submission').default(false), // 최종제출 여부
isWinner: boolean('is_winner'), // 낙찰 여부
isAttendingMeeting: boolean('is_attending_meeting'), // 사양설명회 참석 여부
awardRatio: decimal('award_ratio', { precision: 5, scale: 2 }), // 발주비율
+ //연동제 적용요건 문의 여부
+ isPriceAdjustmentApplicableQuestion: boolean('is_price_adjustment_applicable_question').default(false), // 연동제 적용요건 문의 여부
// 기타
notes: text('notes'), // 특이사항
@@ -328,6 +400,18 @@ export const biddingCompanies = pgTable('bidding_companies', {
updatedAt: timestamp('updated_at').defaultNow().notNull(),
})
+// 13-1. 입찰 참여 업체 담당자 테이블
+export const biddingCompaniesContacts = pgTable('bidding_companies_contacts', {
+ id: serial('id').primaryKey(),
+ biddingId: integer('bidding_id').references(() => biddings.id).notNull(),
+ vendorId: integer('vendor_id').references(() => vendors.id).notNull(),
+ contactName: varchar('contact_name', { length: 255 }).notNull(),
+ contactEmail: varchar('contact_email', { length: 255 }).notNull(),
+ contactNumber: varchar('contact_number', { length: 50 }),
+ createdAt: timestamp('created_at').defaultNow().notNull(),
+ updatedAt: timestamp('updated_at').defaultNow().notNull(),
+})
+
// 14. 업체별 PR 아이템 응찰 정보
export const companyPrItemBids = pgTable('company_pr_item_bids', {
id: serial('id').primaryKey(),
@@ -491,7 +575,15 @@ export const priceAdjustmentForms = pgTable('price_adjustment_forms', {
});
// 타입 정의
-export type Bidding = typeof biddings.$inferSelect
+export type Bidding = typeof biddings.$inferSelect & {
+ // 추가 필드들 (쿼리 결과에 포함되므로 타입에도 추가)
+ purchasingOrganization?: string | null
+ bidPicId?: number | null
+ bidPicName?: string | null
+ supplyPicId?: number | null
+ supplyPicName?: string | null
+}
+
export type NewBidding = typeof biddings.$inferInsert
export type SpecificationMeeting = typeof specificationMeetings.$inferSelect
@@ -509,6 +601,9 @@ export type NewBiddingConditions = typeof biddingConditions.$inferInsert
export type BiddingCompany = typeof biddingCompanies.$inferSelect
export type NewBiddingCompany = typeof biddingCompanies.$inferInsert
+export type BiddingCompanyContact = typeof biddingCompaniesContacts.$inferSelect
+export type NewBiddingCompanyContact = typeof biddingCompaniesContacts.$inferInsert
+
export type CompanyPrItemBid = typeof companyPrItemBids.$inferSelect
export type NewCompanyPrItemBid = typeof companyPrItemBids.$inferInsert
@@ -543,6 +638,29 @@ export type BiddingWithDetails = Bidding & {
}
export type BiddingListItem = Bidding & {
+ // 전체 참여 현황
+ participantExpected: number // 초대업체 수
+ participationRate: number // 참여율 (입찰 기준)
+
+ // 사전견적 참여 현황
+ preQuotePending: number // 사전견적 초대 대기/발송
+ preQuoteAccepted: number // 사전견적 참여
+ preQuoteDeclined: number // 사전견적 미참여
+ preQuoteSubmitted: number // 사전견적 제출완료
+
+ // 입찰 참여 현황
+ biddingPending: number // 입찰 초대 발송
+ biddingAccepted: number // 입찰 참여
+ biddingDeclined: number // 입찰 미참여
+ biddingCancelled: number // 응찰 취소
+ biddingSubmitted: number // 응찰 완료
+
+ // 호환성을 위한 기존 필드 (deprecated)
+ participantParticipated: number // 참여
+ participantDeclined: number // 포기
+ participantPending: number // 대기
+ participantAccepted: number // 수락
+
participantStats: {
expected: number // 참여예정
participated: number // 참여
@@ -562,7 +680,13 @@ export const biddingStatusLabels = {
bidding_closed: '입찰마감',
evaluation_of_bidding: '입찰평가중',
bidding_disposal: '유찰',
- vendor_selected: '업체선정'
+ vendor_selected: '업체선정',
+ bid_opening: '개찰',
+ early_bid_opening: '조기개찰',
+ rebidding: '재입찰',
+ disposal_cancelled: '유찰취소',
+ bid_closure: '폐찰',
+ round_increase: '차수증가'
} as const
export const contractTypeLabels = {
@@ -571,6 +695,12 @@ export const contractTypeLabels = {
sale: '매각계약'
} as const
+export const biddingNoticeTypeLabels = {
+ standard: '표준',
+ facility: '시설재',
+ unit_price: '단가계약'
+} as const
+
export const biddingTypeLabels = {
equipment: '기자재',
construction: '공사',
@@ -581,7 +711,6 @@ export const biddingTypeLabels = {
transport: '운송',
waste: '폐기물',
sale: '매각',
- steel: '강재',
other: '기타(직접입력)'
} as const
@@ -610,384 +739,383 @@ export const weightUnitLabels = {
} as const
-export const biddingListView = pgView('bidding_list_view').as((qb) =>
- qb
- .select({
- // ═══════════════════════════════════════════════════════════════
- // 기본 입찰 정보
- // ═══════════════════════════════════════════════════════════════
- id: biddings.id,
- biddingNumber: biddings.biddingNumber,
- revision: biddings.revision,
- projectName: biddings.projectName,
- itemName: biddings.itemName,
- title: biddings.title,
- description: biddings.description,
- content: biddings.content,
- biddingSourceType: biddings.biddingSourceType,
- isUrgent: biddings.isUrgent,
-
- // ═══════════════════════════════════════════════════════════════
- // 계약 정보
- // ═══════════════════════════════════════════════════════════════
- contractType: biddings.contractType,
- biddingType: biddings.biddingType,
- awardCount: biddings.awardCount,
- contractStartDate: biddings.contractStartDate,
- contractEndDate: biddings.contractEndDate,
-
- // ═══════════════════════════════════════════════════════════════
- // 일정 관리
- // ═══════════════════════════════════════════════════════════════
- preQuoteDate: biddings.preQuoteDate,
- biddingRegistrationDate: biddings.biddingRegistrationDate,
- submissionStartDate: biddings.submissionStartDate,
- submissionEndDate: biddings.submissionEndDate,
- evaluationDate: biddings.evaluationDate,
-
- // ═══════════════════════════════════════════════════════════════
- // 회의 및 문서
- // ═══════════════════════════════════════════════════════════════
- hasSpecificationMeeting: biddings.hasSpecificationMeeting,
- hasPrDocument: biddings.hasPrDocument,
- prNumber: biddings.prNumber,
-
- // ═══════════════════════════════════════════════════════════════
- // 가격 정보
- // ═══════════════════════════════════════════════════════════════
- currency: biddings.currency,
- budget: biddings.budget,
- targetPrice: biddings.targetPrice,
- finalBidPrice: biddings.finalBidPrice,
-
- // ═══════════════════════════════════════════════════════════════
- // 상태 및 담당자
- // ═══════════════════════════════════════════════════════════════
- status: biddings.status,
- isPublic: biddings.isPublic,
- managerName: biddings.managerName,
- managerEmail: biddings.managerEmail,
- managerPhone: biddings.managerPhone,
-
- // ═══════════════════════════════════════════════════════════════
- // 메타 정보
- // ═══════════════════════════════════════════════════════════════
- remarks: biddings.remarks,
- createdBy: biddings.createdBy,
- createdAt: biddings.createdAt,
- updatedAt: biddings.updatedAt,
- updatedBy: biddings.updatedBy,
-
- // ═══════════════════════════════════════════════════════════════
- // 사양설명회 상세 정보
- // ═══════════════════════════════════════════════════════════════
- hasSpecificationMeetingDetails: sql<boolean>`${specificationMeetings.id} IS NOT NULL`.as('has_specification_meeting_details'),
- meetingDate: specificationMeetings.meetingDate,
- meetingLocation: specificationMeetings.location,
- meetingContactPerson: specificationMeetings.contactPerson,
- meetingIsRequired: specificationMeetings.isRequired,
-
- // ═══════════════════════════════════════════════════════════════
- // PR 문서 집계
- // ═══════════════════════════════════════════════════════════════
- prDocumentCount: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM pr_documents
- WHERE bidding_id = ${biddings.id}
- ), 0)
- `.as('pr_document_count'),
-
- // PR 문서 목록 (최대 5개 문서명)
- prDocumentNames: sql<string[]>`
- (
- SELECT array_agg(document_name ORDER BY registered_at DESC)
- FROM pr_documents
- WHERE bidding_id = ${biddings.id}
- LIMIT 5
- )
- `.as('pr_document_names'),
-
- // ═══════════════════════════════════════════════════════════════
- // 참여 현황 집계 (핵심)
- // ═══════════════════════════════════════════════════════════════
- participantExpected: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- ), 0)
- `.as('participant_expected'),
-
- participantParticipated: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND invitation_status = 'submitted'
- ), 0)
- `.as('participant_participated'),
-
- participantDeclined: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND invitation_status = 'declined'
- ), 0)
- `.as('participant_declined'),
-
- participantPending: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND invitation_status IN ('pending', 'sent')
- ), 0)
- `.as('participant_pending'),
-
- participantAccepted: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND invitation_status = 'accepted'
- ), 0)
- `.as('participant_accepted'),
-
- // ═══════════════════════════════════════════════════════════════
- // 참여율 계산
- // ═══════════════════════════════════════════════════════════════
- participationRate: sql<number>`
- CASE
- WHEN (
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- ) > 0
- THEN ROUND(
- (
- SELECT count(*)::decimal
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND invitation_status = 'submitted'
- ) / (
- SELECT count(*)::decimal
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- ) * 100, 1
- )
- ELSE 0
- END
- `.as('participation_rate'),
-
- // ═══════════════════════════════════════════════════════════════
- // 견적 금액 통계
- // ═══════════════════════════════════════════════════════════════
- avgPreQuoteAmount: sql<number>`
- (
- SELECT AVG(pre_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND pre_quote_amount IS NOT NULL
- )
- `.as('avg_pre_quote_amount'),
-
- minPreQuoteAmount: sql<number>`
- (
- SELECT MIN(pre_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND pre_quote_amount IS NOT NULL
- )
- `.as('min_pre_quote_amount'),
-
- maxPreQuoteAmount: sql<number>`
- (
- SELECT MAX(pre_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND pre_quote_amount IS NOT NULL
- )
- `.as('max_pre_quote_amount'),
-
- avgFinalQuoteAmount: sql<number>`
- (
- SELECT AVG(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- )
- `.as('avg_final_quote_amount'),
-
- minFinalQuoteAmount: sql<number>`
- (
- SELECT MIN(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- )
- `.as('min_final_quote_amount'),
-
- maxFinalQuoteAmount: sql<number>`
- (
- SELECT MAX(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- )
- `.as('max_final_quote_amount'),
-
- // ═══════════════════════════════════════════════════════════════
- // 선정 및 낙찰 정보
- // ═══════════════════════════════════════════════════════════════
- selectedForFinalBidCount: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND is_pre_quote_selected = true
- ), 0)
- `.as('selected_for_final_bid_count'),
-
- winnerCount: sql<number>`
- COALESCE((
- SELECT count(*)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND is_winner = true
- ), 0)
- `.as('winner_count'),
-
- // 낙찰 업체명 목록
- winnerCompanyNames: sql<string[]>`
- (
- SELECT array_agg(v.vendor_name ORDER BY v.vendor_name)
- FROM bidding_companies bc
- JOIN vendors v ON bc.company_id = v.id
- WHERE bc.bidding_id = ${biddings.id}
- AND bc.is_winner = true
- )
- `.as('winner_company_names'),
-
- // ═══════════════════════════════════════════════════════════════
- // 일정 상태 계산
- // ═══════════════════════════════════════════════════════════════
- submissionStatus: sql<string>`
- CASE
- WHEN ${biddings.submissionStartDate} IS NULL OR ${biddings.submissionEndDate} IS NULL
- THEN 'not_scheduled'
- WHEN NOW() < ${biddings.submissionStartDate}
- THEN 'scheduled'
- WHEN NOW() BETWEEN ${biddings.submissionStartDate} AND ${biddings.submissionEndDate}
- THEN 'active'
- WHEN NOW() > ${biddings.submissionEndDate}
- THEN 'closed'
- ELSE 'unknown'
- END
- `.as('submission_status'),
-
- // 마감까지 남은 일수
- daysUntilDeadline: sql<number>`
- CASE
- WHEN ${biddings.submissionEndDate} IS NOT NULL
- AND NOW() < ${biddings.submissionEndDate}
- THEN EXTRACT(DAYS FROM (${biddings.submissionEndDate} - NOW()))::integer
- ELSE NULL
- END
- `.as('days_until_deadline'),
-
- // 시작까지 남은 일수
- daysUntilStart: sql<number>`
- CASE
- WHEN ${biddings.submissionStartDate} IS NOT NULL
- AND NOW() < ${biddings.submissionStartDate}
- THEN EXTRACT(DAYS FROM (${biddings.submissionStartDate} - NOW()))::integer
- ELSE NULL
- END
- `.as('days_until_start'),
-
- // ═══════════════════════════════════════════════════════════════
- // 추가 유용한 계산 필드들
- // ═══════════════════════════════════════════════════════════════
-
- // 예산 대비 최저 견적 비율
- budgetEfficiencyRate: sql<number>`
- CASE
- WHEN ${biddings.budget} IS NOT NULL AND ${biddings.budget} > 0
- AND (
- SELECT MIN(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- ) IS NOT NULL
- THEN ROUND(
- (
- SELECT MIN(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- ) / ${biddings.budget} * 100, 1
- )
- ELSE NULL
- END
- `.as('budget_efficiency_rate'),
-
- // 내정가 대비 최저 견적 비율
- targetPriceEfficiencyRate: sql<number>`
- CASE
- WHEN ${biddings.targetPrice} IS NOT NULL AND ${biddings.targetPrice} > 0
- AND (
- SELECT MIN(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- ) IS NOT NULL
- THEN ROUND(
- (
- SELECT MIN(final_quote_amount)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- AND final_quote_amount IS NOT NULL
- ) / ${biddings.targetPrice} * 100, 1
- )
- ELSE NULL
- END
- `.as('target_price_efficiency_rate'),
-
- // 입찰 진행 단계 점수 (0-100)
- progressScore: sql<number>`
- CASE ${biddings.status}
- WHEN 'bidding_generated' THEN 10
- WHEN 'request_for_quotation' THEN 20
- WHEN 'received_quotation' THEN 40
- WHEN 'set_target_price' THEN 60
- WHEN 'bidding_opened' THEN 70
- WHEN 'bidding_closed' THEN 80
- WHEN 'evaluation_of_bidding' THEN 90
- WHEN 'vendor_selected' THEN 100
- WHEN 'bidding_disposal' THEN 0
- ELSE 0
- END
- `.as('progress_score'),
-
- // 마지막 활동일 (가장 최근 업체 응답일)
- lastActivityDate: sql<Date>`
- GREATEST(
- ${biddings.updatedAt},
- COALESCE((
- SELECT MAX(updated_at)
- FROM bidding_companies
- WHERE bidding_id = ${biddings.id}
- ), ${biddings.updatedAt})
- )
- `.as('last_activity_date'),
- })
- .from(biddings)
- .leftJoin(
- specificationMeetings,
- sql`${biddings.id} = ${specificationMeetings.biddingId}`
- )
-)
-
-export type BiddingListView = typeof biddingListView.$inferSelect
+// export const biddingListView = pgView('bidding_list_view').as((qb) =>
+// qb
+// .select({
+// // ═══════════════════════════════════════════════════════════════
+// // 기본 입찰 정보
+// // ═══════════════════════════════════════════════════════════════
+// id: biddings.id,
+// biddingNumber: biddings.biddingNumber,
+// revision: biddings.revision,
+// projectName: biddings.projectName,
+// itemName: biddings.itemName,
+// title: biddings.title,
+// description: biddings.description,
+// biddingSourceType: biddings.biddingSourceType,
+// isUrgent: biddings.isUrgent,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 계약 정보
+// // ═══════════════════════════════════════════════════════════════
+// contractType: biddings.contractType,
+// biddingType: biddings.biddingType,
+// awardCount: biddings.awardCount,
+// contractStartDate: biddings.contractStartDate,
+// contractEndDate: biddings.contractEndDate,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 일정 관리
+// // ═══════════════════════════════════════════════════════════════
+// preQuoteDate: biddings.preQuoteDate,
+// biddingRegistrationDate: biddings.biddingRegistrationDate,
+// submissionStartDate: biddings.submissionStartDate,
+// submissionEndDate: biddings.submissionEndDate,
+// evaluationDate: biddings.evaluationDate,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 회의 및 문서
+// // ═══════════════════════════════════════════════════════════════
+// hasSpecificationMeeting: biddings.hasSpecificationMeeting,
+// hasPrDocument: biddings.hasPrDocument,
+// prNumber: biddings.prNumber,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 가격 정보
+// // ═══════════════════════════════════════════════════════════════
+// currency: biddings.currency,
+// budget: biddings.budget,
+// targetPrice: biddings.targetPrice,
+// finalBidPrice: biddings.finalBidPrice,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 상태 및 담당자
+// // ═══════════════════════════════════════════════════════════════
+// status: biddings.status,
+// isPublic: biddings.isPublic,
+// managerName: biddings.managerName,
+// managerEmail: biddings.managerEmail,
+// managerPhone: biddings.managerPhone,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 메타 정보
+// // ═══════════════════════════════════════════════════════════════
+// remarks: biddings.remarks,
+// createdBy: biddings.createdBy,
+// createdAt: biddings.createdAt,
+// updatedAt: biddings.updatedAt,
+// updatedBy: biddings.updatedBy,
+
+// // ═══════════════════════════════════════════════════════════════
+// // 사양설명회 상세 정보
+// // ═══════════════════════════════════════════════════════════════
+// hasSpecificationMeetingDetails: sql<boolean>`${specificationMeetings.id} IS NOT NULL`.as('has_specification_meeting_details'),
+// meetingDate: specificationMeetings.meetingDate,
+// meetingLocation: specificationMeetings.location,
+// meetingContactPerson: specificationMeetings.contactPerson,
+// meetingIsRequired: specificationMeetings.isRequired,
+
+// // ═══════════════════════════════════════════════════════════════
+// // PR 문서 집계
+// // ═══════════════════════════════════════════════════════════════
+// prDocumentCount: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM pr_documents
+// WHERE bidding_id = ${biddings.id}
+// ), 0)
+// `.as('pr_document_count'),
+
+// // PR 문서 목록 (최대 5개 문서명)
+// prDocumentNames: sql<string[]>`
+// (
+// SELECT array_agg(document_name ORDER BY registered_at DESC)
+// FROM pr_documents
+// WHERE bidding_id = ${biddings.id}
+// LIMIT 5
+// )
+// `.as('pr_document_names'),
+
+// // ═══════════════════════════════════════════════════════════════
+// // 참여 현황 집계 (핵심)
+// // ═══════════════════════════════════════════════════════════════
+// participantExpected: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// ), 0)
+// `.as('participant_expected'),
+
+// participantParticipated: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND invitation_status = 'submitted'
+// ), 0)
+// `.as('participant_participated'),
+
+// participantDeclined: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND invitation_status = 'declined'
+// ), 0)
+// `.as('participant_declined'),
+
+// participantPending: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND invitation_status IN ('pending', 'sent')
+// ), 0)
+// `.as('participant_pending'),
+
+// participantAccepted: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND invitation_status = 'accepted'
+// ), 0)
+// `.as('participant_accepted'),
+
+// // ═══════════════════════════════════════════════════════════════
+// // 참여율 계산
+// // ═══════════════════════════════════════════════════════════════
+// participationRate: sql<number>`
+// CASE
+// WHEN (
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// ) > 0
+// THEN ROUND(
+// (
+// SELECT count(*)::decimal
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND invitation_status = 'submitted'
+// ) / (
+// SELECT count(*)::decimal
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// ) * 100, 1
+// )
+// ELSE 0
+// END
+// `.as('participation_rate'),
+
+// // ═══════════════════════════════════════════════════════════════
+// // 견적 금액 통계
+// // ═══════════════════════════════════════════════════════════════
+// avgPreQuoteAmount: sql<number>`
+// (
+// SELECT AVG(pre_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND pre_quote_amount IS NOT NULL
+// )
+// `.as('avg_pre_quote_amount'),
+
+// minPreQuoteAmount: sql<number>`
+// (
+// SELECT MIN(pre_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND pre_quote_amount IS NOT NULL
+// )
+// `.as('min_pre_quote_amount'),
+
+// maxPreQuoteAmount: sql<number>`
+// (
+// SELECT MAX(pre_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND pre_quote_amount IS NOT NULL
+// )
+// `.as('max_pre_quote_amount'),
+
+// avgFinalQuoteAmount: sql<number>`
+// (
+// SELECT AVG(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// )
+// `.as('avg_final_quote_amount'),
+
+// minFinalQuoteAmount: sql<number>`
+// (
+// SELECT MIN(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// )
+// `.as('min_final_quote_amount'),
+
+// maxFinalQuoteAmount: sql<number>`
+// (
+// SELECT MAX(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// )
+// `.as('max_final_quote_amount'),
+
+// // ═══════════════════════════════════════════════════════════════
+// // 선정 및 낙찰 정보
+// // ═══════════════════════════════════════════════════════════════
+// selectedForFinalBidCount: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND is_pre_quote_selected = true
+// ), 0)
+// `.as('selected_for_final_bid_count'),
+
+// winnerCount: sql<number>`
+// COALESCE((
+// SELECT count(*)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND is_winner = true
+// ), 0)
+// `.as('winner_count'),
+
+// // 낙찰 업체명 목록
+// winnerCompanyNames: sql<string[]>`
+// (
+// SELECT array_agg(v.vendor_name ORDER BY v.vendor_name)
+// FROM bidding_companies bc
+// JOIN vendors v ON bc.company_id = v.id
+// WHERE bc.bidding_id = ${biddings.id}
+// AND bc.is_winner = true
+// )
+// `.as('winner_company_names'),
+
+// // ═══════════════════════════════════════════════════════════════
+// // 일정 상태 계산
+// // ═══════════════════════════════════════════════════════════════
+// submissionStatus: sql<string>`
+// CASE
+// WHEN ${biddings.submissionStartDate} IS NULL OR ${biddings.submissionEndDate} IS NULL
+// THEN 'not_scheduled'
+// WHEN NOW() < ${biddings.submissionStartDate}
+// THEN 'scheduled'
+// WHEN NOW() BETWEEN ${biddings.submissionStartDate} AND ${biddings.submissionEndDate}
+// THEN 'active'
+// WHEN NOW() > ${biddings.submissionEndDate}
+// THEN 'closed'
+// ELSE 'unknown'
+// END
+// `.as('submission_status'),
+
+// // 마감까지 남은 일수
+// daysUntilDeadline: sql<number>`
+// CASE
+// WHEN ${biddings.submissionEndDate} IS NOT NULL
+// AND NOW() < ${biddings.submissionEndDate}
+// THEN EXTRACT(DAYS FROM (${biddings.submissionEndDate} - NOW()))::integer
+// ELSE NULL
+// END
+// `.as('days_until_deadline'),
+
+// // 시작까지 남은 일수
+// daysUntilStart: sql<number>`
+// CASE
+// WHEN ${biddings.submissionStartDate} IS NOT NULL
+// AND NOW() < ${biddings.submissionStartDate}
+// THEN EXTRACT(DAYS FROM (${biddings.submissionStartDate} - NOW()))::integer
+// ELSE NULL
+// END
+// `.as('days_until_start'),
+
+// // ═══════════════════════════════════════════════════════════════
+// // 추가 유용한 계산 필드들
+// // ═══════════════════════════════════════════════════════════════
+
+// // 예산 대비 최저 견적 비율
+// budgetEfficiencyRate: sql<number>`
+// CASE
+// WHEN ${biddings.budget} IS NOT NULL AND ${biddings.budget} > 0
+// AND (
+// SELECT MIN(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// ) IS NOT NULL
+// THEN ROUND(
+// (
+// SELECT MIN(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// ) / ${biddings.budget} * 100, 1
+// )
+// ELSE NULL
+// END
+// `.as('budget_efficiency_rate'),
+
+// // 내정가 대비 최저 견적 비율
+// targetPriceEfficiencyRate: sql<number>`
+// CASE
+// WHEN ${biddings.targetPrice} IS NOT NULL AND ${biddings.targetPrice} > 0
+// AND (
+// SELECT MIN(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// ) IS NOT NULL
+// THEN ROUND(
+// (
+// SELECT MIN(final_quote_amount)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// AND final_quote_amount IS NOT NULL
+// ) / ${biddings.targetPrice} * 100, 1
+// )
+// ELSE NULL
+// END
+// `.as('target_price_efficiency_rate'),
+
+// // 입찰 진행 단계 점수 (0-100)
+// progressScore: sql<number>`
+// CASE ${biddings.status}
+// WHEN 'bidding_generated' THEN 10
+// WHEN 'request_for_quotation' THEN 20
+// WHEN 'received_quotation' THEN 40
+// WHEN 'set_target_price' THEN 60
+// WHEN 'bidding_opened' THEN 70
+// WHEN 'bidding_closed' THEN 80
+// WHEN 'evaluation_of_bidding' THEN 90
+// WHEN 'vendor_selected' THEN 100
+// WHEN 'bidding_disposal' THEN 0
+// ELSE 0
+// END
+// `.as('progress_score'),
+
+// // 마지막 활동일 (가장 최근 업체 응답일)
+// lastActivityDate: sql<Date>`
+// GREATEST(
+// ${biddings.updatedAt},
+// COALESCE((
+// SELECT MAX(updated_at)
+// FROM bidding_companies
+// WHERE bidding_id = ${biddings.id}
+// ), ${biddings.updatedAt})
+// )
+// `.as('last_activity_date'),
+// })
+// .from(biddings)
+// .leftJoin(
+// specificationMeetings,
+// sql`${biddings.id} = ${specificationMeetings.biddingId}`
+// )
+// )
+
+// export type BiddingListView = typeof biddingListView.$inferSelect
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-06 23:51:42 +0000