From 14f61e24947fb92dd71ec0a7196a6e815f8e66da Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 21 Jul 2025 07:54:26 +0000 Subject: (최겸)기술영업 RFQ 담당자 초대, 요구사항 반영 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/techsales-rfq/repository.ts | 1204 ++++++++++++++++++++------------------- 1 file changed, 611 insertions(+), 593 deletions(-) (limited to 'lib/techsales-rfq/repository.ts') diff --git a/lib/techsales-rfq/repository.ts b/lib/techsales-rfq/repository.ts index 1aaf4b3d..07c9ddf8 100644 --- a/lib/techsales-rfq/repository.ts +++ b/lib/techsales-rfq/repository.ts @@ -1,593 +1,611 @@ -/* eslint-disable @typescript-eslint/no-explicit-any */ - -import { - techSalesRfqs, - techSalesVendorQuotations, - users, - biddingProjects -} from "@/db/schema"; -import { techVendors } from "@/db/schema/techVendors"; -import { - asc, - desc, count, SQL, sql, eq -} from "drizzle-orm"; -import { PgTransaction } from "drizzle-orm/pg-core"; - - - -export type NewTechSalesRfq = typeof techSalesRfqs.$inferInsert; -/** - * 기술영업 RFQ 생성 - * ID 및 생성일 리턴 - */ -export async function insertTechSalesRfq( - tx: PgTransaction, - data: NewTechSalesRfq -) { - return tx - .insert(techSalesRfqs) - .values(data) - .returning({ id: techSalesRfqs.id, createdAt: techSalesRfqs.createdAt }); -} - -/** - * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시 - * - 트랜잭션(tx)을 받아서 사용하도록 구현 - */ -export async function selectTechSalesRfqs( - tx: PgTransaction, - params: { - where?: any; - orderBy?: (ReturnType | ReturnType)[]; - offset?: number; - limit?: number; - } -) { - const { where, orderBy, offset = 0, limit = 10 } = params; - - return tx - .select() - .from(techSalesRfqs) - .where(where ?? undefined) - .orderBy(...(orderBy ?? [])) - .offset(offset) - .limit(limit); -} -/** 총 개수 count */ -export async function countTechSalesRfqs( - tx: PgTransaction, - where?: any -) { - const res = await tx.select({ count: count() }).from(techSalesRfqs).where(where); - return res[0]?.count ?? 0; -} - - -/** - * 기술영업 RFQ 조회 with 조인 (Repository) - */ -export async function selectTechSalesRfqsWithJoin( - tx: PgTransaction, - options: { - where?: SQL; - orderBy?: (ReturnType | ReturnType | SQL)[]; - offset?: number; - limit?: number; - rfqType?: "SHIP" | "TOP" | "HULL"; - } -) { - const { where, orderBy, offset = 0, limit = 10, rfqType } = options; - - // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행 - let query = tx.select({ - // RFQ 기본 정보 - id: techSalesRfqs.id, - rfqCode: techSalesRfqs.rfqCode, - rfqType: techSalesRfqs.rfqType, - biddingProjectId: techSalesRfqs.biddingProjectId, - materialCode: techSalesRfqs.materialCode, - - // 날짜 및 상태 정보 - dueDate: techSalesRfqs.dueDate, - rfqSendDate: techSalesRfqs.rfqSendDate, - status: techSalesRfqs.status, - - // 담당자 및 비고 - picCode: techSalesRfqs.picCode, - remark: techSalesRfqs.remark, - cancelReason: techSalesRfqs.cancelReason, - description: techSalesRfqs.description, - - // 생성/수정 정보 - createdAt: techSalesRfqs.createdAt, - updatedAt: techSalesRfqs.updatedAt, - - // 사용자 정보 - createdBy: techSalesRfqs.createdBy, - createdByName: sql`created_user.name`, - updatedBy: techSalesRfqs.updatedBy, - updatedByName: sql`updated_user.name`, - sentBy: techSalesRfqs.sentBy, - sentByName: sql`sent_user.name`, - - // 프로젝트 정보 (조인) - pspid: biddingProjects.pspid, - projNm: biddingProjects.projNm, - sector: biddingProjects.sector, - projMsrm: biddingProjects.projMsrm, - ptypeNm: biddingProjects.ptypeNm, - - // 첨부파일 개수 (타입별로 분리) - attachmentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - AND tech_sales_attachments.attachment_type = 'RFQ_COMMON' - )`, - hasTbeAttachments: sql`( - SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - AND tech_sales_attachments.attachment_type = 'TBE_RESULT' - )`, - hasCbeAttachments: sql`( - SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - AND tech_sales_attachments.attachment_type = 'CBE_RESULT' - )`, - - // 벤더 견적 개수 - quotationCount: sql`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - )`, - - // 아이템 개수 - itemCount: sql`( - SELECT COUNT(*) - FROM tech_sales_rfq_items - WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id} - )`, - }) - .from(techSalesRfqs) - - // 프로젝트 정보 조인 추가 - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - - // 사용자 정보 조인 - .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`) - .leftJoin(sql`${users} AS updated_user`, sql`${techSalesRfqs.updatedBy} = updated_user.id`) - .leftJoin(sql`${users} AS sent_user`, sql`${techSalesRfqs.sentBy} = sent_user.id`) - -; - - // rfqType 필터링 - const conditions = []; - if (rfqType) { - conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); - } - if (where) { - conditions.push(where); - } - - if (conditions.length > 0) { - query = query.where(sql`${sql.join(conditions, sql` AND `)}`); - } - - // orderBy 적용 - const queryWithOrderBy = orderBy?.length - ? query.orderBy(...orderBy) - : query.orderBy(desc(techSalesRfqs.createdAt)); - - // offset과 limit 적용 후 실행 - return queryWithOrderBy.offset(offset).limit(limit); -} - -/** - * RFQ 개수 직접 조회 (뷰 대신 테이블 조인 사용) - */ -export async function countTechSalesRfqsWithJoin( - tx: PgTransaction, - where?: any, - rfqType?: "SHIP" | "TOP" | "HULL" -) { - const conditions = []; - if (rfqType) { - conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); - } - if (where) { - conditions.push(where); - } - - const finalWhere = conditions.length > 0 ? sql`${sql.join(conditions, sql` AND `)}` : undefined; - - const res = await tx - .select({ count: count() }) - .from(techSalesRfqs) - .where(finalWhere); - return res[0]?.count ?? 0; -} - -/** - * 벤더 견적서 직접 조인 조회 (뷰 대신 테이블 조인 사용) - */ -export async function selectTechSalesVendorQuotationsWithJoin( - tx: PgTransaction, - params: { - where?: any; - orderBy?: (ReturnType | ReturnType)[]; - offset?: number; - limit?: number; - rfqType?: "SHIP" | "TOP" | "HULL"; - } -) { - const { where, orderBy, offset = 0, limit = 10, rfqType } = params; - - // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행 - let query = tx.select({ - // 견적 기본 정보 - id: techSalesVendorQuotations.id, - rfqId: techSalesVendorQuotations.rfqId, - rfqCode: techSalesRfqs.rfqCode, - rfqType: techSalesRfqs.rfqType, - vendorId: techSalesVendorQuotations.vendorId, - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - - // 견적 상세 정보 - totalPrice: techSalesVendorQuotations.totalPrice, - currency: techSalesVendorQuotations.currency, - validUntil: techSalesVendorQuotations.validUntil, - status: techSalesVendorQuotations.status, - remark: techSalesVendorQuotations.remark, - rejectionReason: techSalesVendorQuotations.rejectionReason, - - // 날짜 정보 - submittedAt: techSalesVendorQuotations.submittedAt, - acceptedAt: techSalesVendorQuotations.acceptedAt, - createdAt: techSalesVendorQuotations.createdAt, - updatedAt: techSalesVendorQuotations.updatedAt, - - // 생성/수정 사용자 - createdBy: techSalesVendorQuotations.createdBy, - createdByName: sql`created_user.name`, - updatedBy: techSalesVendorQuotations.updatedBy, - updatedByName: sql`updated_user.name`, - - // 프로젝트 정보 - materialCode: techSalesRfqs.materialCode, - - // 프로젝트 핵심 정보 - null 체크 추가 - pspid: techSalesRfqs.biddingProjectId, - projNm: biddingProjects.projNm, - sector: biddingProjects.sector, - - // 첨부파일 개수 - attachmentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - )`, - - // 견적서 첨부파일 개수 - quotationAttachmentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotation_attachments - WHERE tech_sales_vendor_quotation_attachments.quotation_id = ${techSalesVendorQuotations.id} - )`, - - // RFQ 아이템 개수 - itemCount: sql`( - SELECT COUNT(*) - FROM tech_sales_rfq_items - WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id} - )`, - - }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`) - .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`) - // 프로젝트 정보 조인 추가 - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .leftJoin(sql`${users} AS created_user`, sql`${techSalesVendorQuotations.createdBy} = created_user.id`) - .leftJoin(sql`${users} AS updated_user`, sql`${techSalesVendorQuotations.updatedBy} = updated_user.id`); - - // rfqType 필터링 - const conditions = []; - if (rfqType) { - conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); - } - if (where) { - conditions.push(where); - } - - if (conditions.length > 0) { - query = query.where(sql`${sql.join(conditions, sql` AND `)}`); - } - - // orderBy 적용 - const queryWithOrderBy = orderBy?.length - ? query.orderBy(...orderBy) - : query.orderBy(desc(techSalesVendorQuotations.createdAt)); - - // offset과 limit 적용 후 실행 - return queryWithOrderBy.offset(offset).limit(limit); -} - -/** - * 벤더 견적서 개수 직접 조회 (뷰 대신 테이블 조인 사용) - */ -export async function countTechSalesVendorQuotationsWithJoin( - tx: PgTransaction, - where?: any, - rfqType?: "SHIP" | "TOP" | "HULL" -) { - const conditions = []; - if (rfqType) { - conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); - } - if (where) { - conditions.push(where); - } - - const finalWhere = conditions.length > 0 ? sql`${sql.join(conditions, sql` AND `)}` : undefined; - - const res = await tx - .select({ count: count() }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`) - .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`) - .where(finalWhere); - return res[0]?.count ?? 0; -} - -/** - * RFQ 대시보드 데이터 직접 조인 조회 (뷰 대신 테이블 조인 사용) - */ -export async function selectTechSalesDashboardWithJoin( - tx: PgTransaction, - params: { - where?: any; - orderBy?: (ReturnType | ReturnType | SQL)[]; - offset?: number; - limit?: number; - rfqType?: "SHIP" | "TOP" | "HULL"; - } -) { - const { where, orderBy, offset = 0, limit = 10, rfqType } = params; - - // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행 - let query = tx.select({ - // RFQ 기본 정보 - id: techSalesRfqs.id, - rfqCode: techSalesRfqs.rfqCode, - rfqType: techSalesRfqs.rfqType, - status: techSalesRfqs.status, - dueDate: techSalesRfqs.dueDate, - rfqSendDate: techSalesRfqs.rfqSendDate, - materialCode: techSalesRfqs.materialCode, - - // 프로젝트 정보 - null 체크 추가 - pspid: techSalesRfqs.biddingProjectId, - projNm: biddingProjects.projNm, - sector: biddingProjects.sector, - projMsrm: biddingProjects.projMsrm, - ptypeNm: biddingProjects.ptypeNm, - - // 벤더 견적 통계 - vendorCount: sql`( - SELECT COUNT(DISTINCT vendor_id) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - )`, - - quotationCount: sql`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - )`, - - submittedQuotationCount: sql`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - AND tech_sales_vendor_quotations.status = 'Submitted' - )`, - - minPrice: sql`( - SELECT MIN(total_price) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - AND tech_sales_vendor_quotations.status = 'Submitted' - )`, - - maxPrice: sql`( - SELECT MAX(total_price) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - AND tech_sales_vendor_quotations.status = 'Submitted' - )`, - - avgPrice: sql`( - SELECT AVG(total_price) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - AND tech_sales_vendor_quotations.status = 'Submitted' - )`, - - // 첨부파일 통계 - attachmentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - )`, - - // 코멘트 통계 - commentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_rfq_comments - WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id} - )`, - - unreadCommentCount: sql`( - SELECT COUNT(*) - FROM tech_sales_rfq_comments - WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id} - AND tech_sales_rfq_comments.is_read = false - )`, - - // 생성/수정 정보 - createdAt: techSalesRfqs.createdAt, - updatedAt: techSalesRfqs.updatedAt, - createdByName: sql`created_user.name`, - - // 아이템 정보 - rfqType에 따라 다른 테이블에서 조회 - itemName: sql` - CASE - WHEN ${techSalesRfqs.rfqType} = 'SHIP' THEN ship_items.item_list - WHEN ${techSalesRfqs.rfqType} = 'TOP' THEN top_items.item_list - WHEN ${techSalesRfqs.rfqType} = 'HULL' THEN hull_items.item_list - ELSE NULL - END - `, - }) - .from(techSalesRfqs) - .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`) - - // 아이템 정보 조인 - .leftJoin( - sql`( - SELECT DISTINCT ON (rfq_id) - tri.rfq_id, - ship.item_list - FROM tech_sales_rfq_items tri - LEFT JOIN item_shipbuilding ship ON tri.item_shipbuilding_id = ship.id - WHERE tri.item_type = 'SHIP' - ORDER BY rfq_id, tri.id - ) AS ship_items`, - sql`ship_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'SHIP'` - ) - .leftJoin( - sql`( - SELECT DISTINCT ON (rfq_id) - tri.rfq_id, - top.item_list - FROM tech_sales_rfq_items tri - LEFT JOIN item_offshore_top top ON tri.item_offshore_top_id = top.id - WHERE tri.item_type = 'TOP' - ORDER BY rfq_id, tri.id - ) AS top_items`, - sql`top_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'TOP'` - ) - .leftJoin( - sql`( - SELECT DISTINCT ON (rfq_id) - tri.rfq_id, - hull.item_list - FROM tech_sales_rfq_items tri - LEFT JOIN item_offshore_hull hull ON tri.item_offshore_hull_id = hull.id - WHERE tri.item_type = 'HULL' - ORDER BY rfq_id, tri.id - ) AS hull_items`, - sql`hull_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'HULL'` - ); - - // rfqType 필터링 - const conditions = []; - if (rfqType) { - conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); - } - if (where) { - conditions.push(where); - } - - if (conditions.length > 0) { - query = query.where(sql`${sql.join(conditions, sql` AND `)}`); - } - - // orderBy 적용 - const queryWithOrderBy = orderBy?.length - ? query.orderBy(...orderBy) - : query.orderBy(desc(techSalesRfqs.updatedAt)); - - // offset과 limit 적용 후 실행 - return queryWithOrderBy.offset(offset).limit(limit); -} - -/** - * 단일 벤더 견적서 직접 조인 조회 (단일 견적서 상세용) - */ -export async function selectSingleTechSalesVendorQuotationWithJoin( - tx: PgTransaction, - quotationId: number -) { - const result = await tx.select({ - // 견적 기본 정보 - id: techSalesVendorQuotations.id, - rfqId: techSalesVendorQuotations.rfqId, - vendorId: techSalesVendorQuotations.vendorId, - - // 견적 상세 정보 - quotationCode: techSalesVendorQuotations.quotationCode, - quotationVersion: techSalesVendorQuotations.quotationVersion, - totalPrice: techSalesVendorQuotations.totalPrice, - currency: techSalesVendorQuotations.currency, - validUntil: techSalesVendorQuotations.validUntil, - status: techSalesVendorQuotations.status, - remark: techSalesVendorQuotations.remark, - rejectionReason: techSalesVendorQuotations.rejectionReason, - - // 날짜 정보 - submittedAt: techSalesVendorQuotations.submittedAt, - acceptedAt: techSalesVendorQuotations.acceptedAt, - createdAt: techSalesVendorQuotations.createdAt, - updatedAt: techSalesVendorQuotations.updatedAt, - - // 생성/수정 사용자 - createdBy: techSalesVendorQuotations.createdBy, - updatedBy: techSalesVendorQuotations.updatedBy, - - // RFQ 정보 - rfqCode: techSalesRfqs.rfqCode, - rfqType: techSalesRfqs.rfqType, - rfqStatus: techSalesRfqs.status, - dueDate: techSalesRfqs.dueDate, - rfqSendDate: techSalesRfqs.rfqSendDate, - materialCode: techSalesRfqs.materialCode, - description: techSalesRfqs.description, - rfqRemark: techSalesRfqs.remark, - picCode: techSalesRfqs.picCode, - - // RFQ 생성자 정보 - rfqCreatedBy: techSalesRfqs.createdBy, - rfqCreatedByName: sql`rfq_created_user.name`, - rfqCreatedByEmail: sql`rfq_created_user.email`, - - // 벤더 정보 - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - vendorCountry: techVendors.country, - vendorEmail: techVendors.email, - vendorPhone: techVendors.phone, - - // 프로젝트 정보 - biddingProjectId: techSalesRfqs.biddingProjectId, - pspid: biddingProjects.pspid, - projNm: biddingProjects.projNm, - sector: biddingProjects.sector, - projMsrm: biddingProjects.projMsrm, - ptypeNm: biddingProjects.ptypeNm, - - }) - .from(techSalesVendorQuotations) - .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(techVendors, eq(techSalesVendorQuotations.vendorId, techVendors.id)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .leftJoin(sql`${users} AS rfq_created_user`, sql`${techSalesRfqs.createdBy} = rfq_created_user.id`) - .where(eq(techSalesVendorQuotations.id, quotationId)); - - return result[0] || null; -} - +/* eslint-disable @typescript-eslint/no-explicit-any */ + +import { + techSalesRfqs, + techSalesVendorQuotations, + users, + biddingProjects +} from "@/db/schema"; +import { techVendors } from "@/db/schema/techVendors"; +import { + asc, + desc, count, SQL, sql, eq +} from "drizzle-orm"; +import { PgTransaction } from "drizzle-orm/pg-core"; + + + +export type NewTechSalesRfq = typeof techSalesRfqs.$inferInsert; +/** + * 기술영업 RFQ 생성 + * ID 및 생성일 리턴 + */ +export async function insertTechSalesRfq( + tx: PgTransaction, + data: NewTechSalesRfq +) { + return tx + .insert(techSalesRfqs) + .values(data) + .returning({ id: techSalesRfqs.id, createdAt: techSalesRfqs.createdAt }); +} + +/** + * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시 + * - 트랜잭션(tx)을 받아서 사용하도록 구현 + */ +export async function selectTechSalesRfqs( + tx: PgTransaction, + params: { + where?: any; + orderBy?: (ReturnType | ReturnType)[]; + offset?: number; + limit?: number; + } +) { + const { where, orderBy, offset = 0, limit = 10 } = params; + + return tx + .select() + .from(techSalesRfqs) + .where(where ?? undefined) + .orderBy(...(orderBy ?? [])) + .offset(offset) + .limit(limit); +} +/** 총 개수 count */ +export async function countTechSalesRfqs( + tx: PgTransaction, + where?: any +) { + const res = await tx.select({ count: count() }).from(techSalesRfqs).where(where); + return res[0]?.count ?? 0; +} + + +/** + * 기술영업 RFQ 조회 with 조인 (Repository) + */ +export async function selectTechSalesRfqsWithJoin( + tx: PgTransaction, + options: { + where?: SQL; + orderBy?: (ReturnType | ReturnType | SQL)[]; + offset?: number; + limit?: number; + rfqType?: "SHIP" | "TOP" | "HULL"; + } +) { + const { where, orderBy, offset = 0, limit = 10, rfqType } = options; + + // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행 + let query = tx.select({ + // RFQ 기본 정보 + id: techSalesRfqs.id, + rfqCode: techSalesRfqs.rfqCode, + rfqType: techSalesRfqs.rfqType, + biddingProjectId: techSalesRfqs.biddingProjectId, + materialCode: techSalesRfqs.materialCode, + + // 날짜 및 상태 정보 + dueDate: techSalesRfqs.dueDate, + rfqSendDate: techSalesRfqs.rfqSendDate, + status: techSalesRfqs.status, + + // 담당자 및 비고 + picCode: techSalesRfqs.picCode, + remark: techSalesRfqs.remark, + cancelReason: techSalesRfqs.cancelReason, + description: techSalesRfqs.description, + + // 생성/수정 정보 + createdAt: techSalesRfqs.createdAt, + updatedAt: techSalesRfqs.updatedAt, + + // 사용자 정보 + createdBy: techSalesRfqs.createdBy, + createdByName: sql`created_user.name`, + updatedBy: techSalesRfqs.updatedBy, + updatedByName: sql`updated_user.name`, + sentBy: techSalesRfqs.sentBy, + sentByName: sql`sent_user.name`, + + // 프로젝트 정보 (조인) + pspid: biddingProjects.pspid, + projNm: biddingProjects.projNm, + sector: biddingProjects.sector, + projMsrm: biddingProjects.projMsrm, + ptypeNm: biddingProjects.ptypeNm, + + // 첨부파일 개수 (타입별로 분리) + attachmentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_attachments + WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} + AND tech_sales_attachments.attachment_type = 'RFQ_COMMON' + )`, + hasTbeAttachments: sql`( + SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END + FROM tech_sales_attachments + WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} + AND tech_sales_attachments.attachment_type = 'TBE_RESULT' + )`, + hasCbeAttachments: sql`( + SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END + FROM tech_sales_attachments + WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} + AND tech_sales_attachments.attachment_type = 'CBE_RESULT' + )`, + + // 벤더 견적 개수 + quotationCount: sql`( + SELECT COUNT(*) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + )`, + + // 아이템 개수 + itemCount: sql`( + SELECT COUNT(*) + FROM tech_sales_rfq_items + WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id} + )`, + + // WorkTypes aggregation - RFQ에 연결된 모든 아이템들의 workType을 콤마로 구분하여 반환 + workTypes: sql`( + SELECT STRING_AGG(DISTINCT + CASE + WHEN tri.item_type = 'SHIP' THEN ship.work_type + WHEN tri.item_type = 'TOP' THEN top.work_type + WHEN tri.item_type = 'HULL' THEN hull.work_type + ELSE NULL + END, ', ' + ) + FROM tech_sales_rfq_items tri + LEFT JOIN item_shipbuilding ship ON tri.item_shipbuilding_id = ship.id AND tri.item_type = 'SHIP' + LEFT JOIN item_offshore_top top ON tri.item_offshore_top_id = top.id AND tri.item_type = 'TOP' + LEFT JOIN item_offshore_hull hull ON tri.item_offshore_hull_id = hull.id AND tri.item_type = 'HULL' + WHERE tri.rfq_id = ${techSalesRfqs.id} + AND (ship.work_type IS NOT NULL OR top.work_type IS NOT NULL OR hull.work_type IS NOT NULL) + )`, + }) + .from(techSalesRfqs) + + // 프로젝트 정보 조인 추가 + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + + // 사용자 정보 조인 + .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`) + .leftJoin(sql`${users} AS updated_user`, sql`${techSalesRfqs.updatedBy} = updated_user.id`) + .leftJoin(sql`${users} AS sent_user`, sql`${techSalesRfqs.sentBy} = sent_user.id`) + +; + + // rfqType 필터링 + const conditions = []; + if (rfqType) { + conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); + } + if (where) { + conditions.push(where); + } + + if (conditions.length > 0) { + query = query.where(sql`${sql.join(conditions, sql` AND `)}`); + } + + // orderBy 적용 + const queryWithOrderBy = orderBy?.length + ? query.orderBy(...orderBy) + : query.orderBy(desc(techSalesRfqs.createdAt)); + + // offset과 limit 적용 후 실행 + return queryWithOrderBy.offset(offset).limit(limit); +} + +/** + * RFQ 개수 직접 조회 (뷰 대신 테이블 조인 사용) + */ +export async function countTechSalesRfqsWithJoin( + tx: PgTransaction, + where?: any, + rfqType?: "SHIP" | "TOP" | "HULL" +) { + const conditions = []; + if (rfqType) { + conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); + } + if (where) { + conditions.push(where); + } + + const finalWhere = conditions.length > 0 ? sql`${sql.join(conditions, sql` AND `)}` : undefined; + + const res = await tx + .select({ count: count() }) + .from(techSalesRfqs) + .where(finalWhere); + return res[0]?.count ?? 0; +} + +/** + * 벤더 견적서 직접 조인 조회 (뷰 대신 테이블 조인 사용) + */ +export async function selectTechSalesVendorQuotationsWithJoin( + tx: PgTransaction, + params: { + where?: any; + orderBy?: (ReturnType | ReturnType)[]; + offset?: number; + limit?: number; + rfqType?: "SHIP" | "TOP" | "HULL"; + } +) { + const { where, orderBy, offset = 0, limit = 10, rfqType } = params; + + // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행 + let query = tx.select({ + // 견적 기본 정보 + id: techSalesVendorQuotations.id, + rfqId: techSalesVendorQuotations.rfqId, + rfqCode: techSalesRfqs.rfqCode, + rfqType: techSalesRfqs.rfqType, + vendorId: techSalesVendorQuotations.vendorId, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + + // 견적 상세 정보 + totalPrice: techSalesVendorQuotations.totalPrice, + currency: techSalesVendorQuotations.currency, + validUntil: techSalesVendorQuotations.validUntil, + status: techSalesVendorQuotations.status, + remark: techSalesVendorQuotations.remark, + rejectionReason: techSalesVendorQuotations.rejectionReason, + + // 날짜 정보 + submittedAt: techSalesVendorQuotations.submittedAt, + acceptedAt: techSalesVendorQuotations.acceptedAt, + createdAt: techSalesVendorQuotations.createdAt, + updatedAt: techSalesVendorQuotations.updatedAt, + + // 생성/수정 사용자 + createdBy: techSalesVendorQuotations.createdBy, + createdByName: sql`created_user.name`, + updatedBy: techSalesVendorQuotations.updatedBy, + updatedByName: sql`updated_user.name`, + + // 프로젝트 정보 + materialCode: techSalesRfqs.materialCode, + + // 프로젝트 핵심 정보 - null 체크 추가 + pspid: techSalesRfqs.biddingProjectId, + projNm: biddingProjects.projNm, + sector: biddingProjects.sector, + + // 첨부파일 개수 + attachmentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_attachments + WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} + )`, + + // 견적서 첨부파일 개수 + quotationAttachmentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_vendor_quotation_attachments + WHERE tech_sales_vendor_quotation_attachments.quotation_id = ${techSalesVendorQuotations.id} + )`, + + // RFQ 아이템 개수 + itemCount: sql`( + SELECT COUNT(*) + FROM tech_sales_rfq_items + WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id} + )`, + + }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`) + .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`) + // 프로젝트 정보 조인 추가 + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .leftJoin(sql`${users} AS created_user`, sql`${techSalesVendorQuotations.createdBy} = created_user.id`) + .leftJoin(sql`${users} AS updated_user`, sql`${techSalesVendorQuotations.updatedBy} = updated_user.id`); + + // rfqType 필터링 + const conditions = []; + if (rfqType) { + conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); + } + if (where) { + conditions.push(where); + } + + if (conditions.length > 0) { + query = query.where(sql`${sql.join(conditions, sql` AND `)}`); + } + + // orderBy 적용 + const queryWithOrderBy = orderBy?.length + ? query.orderBy(...orderBy) + : query.orderBy(desc(techSalesVendorQuotations.createdAt)); + + // offset과 limit 적용 후 실행 + return queryWithOrderBy.offset(offset).limit(limit); +} + +/** + * 벤더 견적서 개수 직접 조회 (뷰 대신 테이블 조인 사용) + */ +export async function countTechSalesVendorQuotationsWithJoin( + tx: PgTransaction, + where?: any, + rfqType?: "SHIP" | "TOP" | "HULL" +) { + const conditions = []; + if (rfqType) { + conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); + } + if (where) { + conditions.push(where); + } + + const finalWhere = conditions.length > 0 ? sql`${sql.join(conditions, sql` AND `)}` : undefined; + + const res = await tx + .select({ count: count() }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`) + .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`) + .where(finalWhere); + return res[0]?.count ?? 0; +} + +/** + * RFQ 대시보드 데이터 직접 조인 조회 (뷰 대신 테이블 조인 사용) + */ +export async function selectTechSalesDashboardWithJoin( + tx: PgTransaction, + params: { + where?: any; + orderBy?: (ReturnType | ReturnType | SQL)[]; + offset?: number; + limit?: number; + rfqType?: "SHIP" | "TOP" | "HULL"; + } +) { + const { where, orderBy, offset = 0, limit = 10, rfqType } = params; + + // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행 + let query = tx.select({ + // RFQ 기본 정보 + id: techSalesRfqs.id, + rfqCode: techSalesRfqs.rfqCode, + rfqType: techSalesRfqs.rfqType, + status: techSalesRfqs.status, + dueDate: techSalesRfqs.dueDate, + rfqSendDate: techSalesRfqs.rfqSendDate, + materialCode: techSalesRfqs.materialCode, + + // 프로젝트 정보 - null 체크 추가 + pspid: techSalesRfqs.biddingProjectId, + projNm: biddingProjects.projNm, + sector: biddingProjects.sector, + projMsrm: biddingProjects.projMsrm, + ptypeNm: biddingProjects.ptypeNm, + + // 벤더 견적 통계 + vendorCount: sql`( + SELECT COUNT(DISTINCT vendor_id) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + )`, + + quotationCount: sql`( + SELECT COUNT(*) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + )`, + + submittedQuotationCount: sql`( + SELECT COUNT(*) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + AND tech_sales_vendor_quotations.status = 'Submitted' + )`, + + minPrice: sql`( + SELECT MIN(total_price) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + AND tech_sales_vendor_quotations.status = 'Submitted' + )`, + + maxPrice: sql`( + SELECT MAX(total_price) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + AND tech_sales_vendor_quotations.status = 'Submitted' + )`, + + avgPrice: sql`( + SELECT AVG(total_price) + FROM tech_sales_vendor_quotations + WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} + AND tech_sales_vendor_quotations.status = 'Submitted' + )`, + + // 첨부파일 통계 + attachmentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_attachments + WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} + )`, + + // 코멘트 통계 + commentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_rfq_comments + WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id} + )`, + + unreadCommentCount: sql`( + SELECT COUNT(*) + FROM tech_sales_rfq_comments + WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id} + AND tech_sales_rfq_comments.is_read = false + )`, + + // 생성/수정 정보 + createdAt: techSalesRfqs.createdAt, + updatedAt: techSalesRfqs.updatedAt, + createdByName: sql`created_user.name`, + + // 아이템 정보 - rfqType에 따라 다른 테이블에서 조회 + itemName: sql` + CASE + WHEN ${techSalesRfqs.rfqType} = 'SHIP' THEN ship_items.item_list + WHEN ${techSalesRfqs.rfqType} = 'TOP' THEN top_items.item_list + WHEN ${techSalesRfqs.rfqType} = 'HULL' THEN hull_items.item_list + ELSE NULL + END + `, + }) + .from(techSalesRfqs) + .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`) + + // 아이템 정보 조인 + .leftJoin( + sql`( + SELECT DISTINCT ON (rfq_id) + tri.rfq_id, + ship.item_list + FROM tech_sales_rfq_items tri + LEFT JOIN item_shipbuilding ship ON tri.item_shipbuilding_id = ship.id + WHERE tri.item_type = 'SHIP' + ORDER BY rfq_id, tri.id + ) AS ship_items`, + sql`ship_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'SHIP'` + ) + .leftJoin( + sql`( + SELECT DISTINCT ON (rfq_id) + tri.rfq_id, + top.item_list + FROM tech_sales_rfq_items tri + LEFT JOIN item_offshore_top top ON tri.item_offshore_top_id = top.id + WHERE tri.item_type = 'TOP' + ORDER BY rfq_id, tri.id + ) AS top_items`, + sql`top_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'TOP'` + ) + .leftJoin( + sql`( + SELECT DISTINCT ON (rfq_id) + tri.rfq_id, + hull.item_list + FROM tech_sales_rfq_items tri + LEFT JOIN item_offshore_hull hull ON tri.item_offshore_hull_id = hull.id + WHERE tri.item_type = 'HULL' + ORDER BY rfq_id, tri.id + ) AS hull_items`, + sql`hull_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'HULL'` + ); + + // rfqType 필터링 + const conditions = []; + if (rfqType) { + conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`); + } + if (where) { + conditions.push(where); + } + + if (conditions.length > 0) { + query = query.where(sql`${sql.join(conditions, sql` AND `)}`); + } + + // orderBy 적용 + const queryWithOrderBy = orderBy?.length + ? query.orderBy(...orderBy) + : query.orderBy(desc(techSalesRfqs.updatedAt)); + + // offset과 limit 적용 후 실행 + return queryWithOrderBy.offset(offset).limit(limit); +} + +/** + * 단일 벤더 견적서 직접 조인 조회 (단일 견적서 상세용) + */ +export async function selectSingleTechSalesVendorQuotationWithJoin( + tx: PgTransaction, + quotationId: number +) { + const result = await tx.select({ + // 견적 기본 정보 + id: techSalesVendorQuotations.id, + rfqId: techSalesVendorQuotations.rfqId, + vendorId: techSalesVendorQuotations.vendorId, + + // 견적 상세 정보 + quotationCode: techSalesVendorQuotations.quotationCode, + quotationVersion: techSalesVendorQuotations.quotationVersion, + totalPrice: techSalesVendorQuotations.totalPrice, + currency: techSalesVendorQuotations.currency, + validUntil: techSalesVendorQuotations.validUntil, + status: techSalesVendorQuotations.status, + remark: techSalesVendorQuotations.remark, + rejectionReason: techSalesVendorQuotations.rejectionReason, + + // 날짜 정보 + submittedAt: techSalesVendorQuotations.submittedAt, + acceptedAt: techSalesVendorQuotations.acceptedAt, + createdAt: techSalesVendorQuotations.createdAt, + updatedAt: techSalesVendorQuotations.updatedAt, + + // 생성/수정 사용자 + createdBy: techSalesVendorQuotations.createdBy, + updatedBy: techSalesVendorQuotations.updatedBy, + + // RFQ 정보 + rfqCode: techSalesRfqs.rfqCode, + rfqType: techSalesRfqs.rfqType, + rfqStatus: techSalesRfqs.status, + dueDate: techSalesRfqs.dueDate, + rfqSendDate: techSalesRfqs.rfqSendDate, + materialCode: techSalesRfqs.materialCode, + description: techSalesRfqs.description, + rfqRemark: techSalesRfqs.remark, + picCode: techSalesRfqs.picCode, + + // RFQ 생성자 정보 + rfqCreatedBy: techSalesRfqs.createdBy, + rfqCreatedByName: sql`rfq_created_user.name`, + rfqCreatedByEmail: sql`rfq_created_user.email`, + + // 벤더 정보 + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + vendorCountry: techVendors.country, + vendorEmail: techVendors.email, + vendorPhone: techVendors.phone, + + // 프로젝트 정보 + biddingProjectId: techSalesRfqs.biddingProjectId, + pspid: biddingProjects.pspid, + projNm: biddingProjects.projNm, + sector: biddingProjects.sector, + projMsrm: biddingProjects.projMsrm, + ptypeNm: biddingProjects.ptypeNm, + + }) + .from(techSalesVendorQuotations) + .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(techVendors, eq(techSalesVendorQuotations.vendorId, techVendors.id)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .leftJoin(sql`${users} AS rfq_created_user`, sql`${techSalesRfqs.createdBy} = rfq_created_user.id`) + .where(eq(techSalesVendorQuotations.id, quotationId)); + + return result[0] || null; +} + -- cgit v1.2.3