diff options
Diffstat (limited to 'lib/techsales-rfq/repository.ts')
| -rw-r--r-- | lib/techsales-rfq/repository.ts | 1204 |
1 files changed, 611 insertions, 593 deletions
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<any, any, any>, - data: NewTechSalesRfq -) { - return tx - .insert(techSalesRfqs) - .values(data) - .returning({ id: techSalesRfqs.id, createdAt: techSalesRfqs.createdAt }); -} - -/** - * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시 - * - 트랜잭션(tx)을 받아서 사용하도록 구현 - */ -export async function selectTechSalesRfqs( - tx: PgTransaction<any, any, any>, - params: { - where?: any; - orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]; - 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<any, any, any>, - 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<any, any, any>, - options: { - where?: SQL; - orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[]; - 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<string>`created_user.name`, - updatedBy: techSalesRfqs.updatedBy, - updatedByName: sql<string>`updated_user.name`, - sentBy: techSalesRfqs.sentBy, - sentByName: sql<string | null>`sent_user.name`, - - // 프로젝트 정보 (조인) - pspid: biddingProjects.pspid, - projNm: biddingProjects.projNm, - sector: biddingProjects.sector, - projMsrm: biddingProjects.projMsrm, - ptypeNm: biddingProjects.ptypeNm, - - // 첨부파일 개수 (타입별로 분리) - attachmentCount: sql<number>`( - 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<boolean>`( - 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<boolean>`( - 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<number>`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - )`, - - // 아이템 개수 - itemCount: sql<number>`( - 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<any, any, any>, - 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<any, any, any>, - params: { - where?: any; - orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]; - 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<string | null>`created_user.name`, - updatedBy: techSalesVendorQuotations.updatedBy, - updatedByName: sql<string | null>`updated_user.name`, - - // 프로젝트 정보 - materialCode: techSalesRfqs.materialCode, - - // 프로젝트 핵심 정보 - null 체크 추가 - pspid: techSalesRfqs.biddingProjectId, - projNm: biddingProjects.projNm, - sector: biddingProjects.sector, - - // 첨부파일 개수 - attachmentCount: sql<number>`( - SELECT COUNT(*) - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - )`, - - // 견적서 첨부파일 개수 - quotationAttachmentCount: sql<number>`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotation_attachments - WHERE tech_sales_vendor_quotation_attachments.quotation_id = ${techSalesVendorQuotations.id} - )`, - - // RFQ 아이템 개수 - itemCount: sql<number>`( - 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<any, any, any>, - 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<any, any, any>, - params: { - where?: any; - orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[]; - 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<number>`( - SELECT COUNT(DISTINCT vendor_id) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - )`, - - quotationCount: sql<number>`( - SELECT COUNT(*) - FROM tech_sales_vendor_quotations - WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id} - )`, - - submittedQuotationCount: sql<number>`( - 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<string | null>`( - 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<string | null>`( - 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<string | null>`( - 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<number>`( - SELECT COUNT(*) - FROM tech_sales_attachments - WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id} - )`, - - // 코멘트 통계 - commentCount: sql<number>`( - SELECT COUNT(*) - FROM tech_sales_rfq_comments - WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id} - )`, - - unreadCommentCount: sql<number>`( - 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<string>`created_user.name`, - - // 아이템 정보 - rfqType에 따라 다른 테이블에서 조회 - itemName: sql<string>` - 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<any, any, any>, - 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<string | null>`rfq_created_user.name`, - rfqCreatedByEmail: sql<string | null>`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<any, any, any>,
+ data: NewTechSalesRfq
+) {
+ return tx
+ .insert(techSalesRfqs)
+ .values(data)
+ .returning({ id: techSalesRfqs.id, createdAt: techSalesRfqs.createdAt });
+}
+
+/**
+ * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
+ * - 트랜잭션(tx)을 받아서 사용하도록 구현
+ */
+export async function selectTechSalesRfqs(
+ tx: PgTransaction<any, any, any>,
+ params: {
+ where?: any;
+ orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
+ 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<any, any, any>,
+ 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<any, any, any>,
+ options: {
+ where?: SQL;
+ orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
+ 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<string>`created_user.name`,
+ updatedBy: techSalesRfqs.updatedBy,
+ updatedByName: sql<string>`updated_user.name`,
+ sentBy: techSalesRfqs.sentBy,
+ sentByName: sql<string | null>`sent_user.name`,
+
+ // 프로젝트 정보 (조인)
+ pspid: biddingProjects.pspid,
+ projNm: biddingProjects.projNm,
+ sector: biddingProjects.sector,
+ projMsrm: biddingProjects.projMsrm,
+ ptypeNm: biddingProjects.ptypeNm,
+
+ // 첨부파일 개수 (타입별로 분리)
+ attachmentCount: sql<number>`(
+ 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<boolean>`(
+ 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<boolean>`(
+ 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<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_vendor_quotations
+ WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ // 아이템 개수
+ itemCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_rfq_items
+ WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ // WorkTypes aggregation - RFQ에 연결된 모든 아이템들의 workType을 콤마로 구분하여 반환
+ workTypes: sql<string>`(
+ 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<any, any, any>,
+ 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<any, any, any>,
+ params: {
+ where?: any;
+ orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
+ 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<string | null>`created_user.name`,
+ updatedBy: techSalesVendorQuotations.updatedBy,
+ updatedByName: sql<string | null>`updated_user.name`,
+
+ // 프로젝트 정보
+ materialCode: techSalesRfqs.materialCode,
+
+ // 프로젝트 핵심 정보 - null 체크 추가
+ pspid: techSalesRfqs.biddingProjectId,
+ projNm: biddingProjects.projNm,
+ sector: biddingProjects.sector,
+
+ // 첨부파일 개수
+ attachmentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_attachments
+ WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ // 견적서 첨부파일 개수
+ quotationAttachmentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_vendor_quotation_attachments
+ WHERE tech_sales_vendor_quotation_attachments.quotation_id = ${techSalesVendorQuotations.id}
+ )`,
+
+ // RFQ 아이템 개수
+ itemCount: sql<number>`(
+ 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<any, any, any>,
+ 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<any, any, any>,
+ params: {
+ where?: any;
+ orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
+ 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<number>`(
+ SELECT COUNT(DISTINCT vendor_id)
+ FROM tech_sales_vendor_quotations
+ WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ quotationCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_vendor_quotations
+ WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ submittedQuotationCount: sql<number>`(
+ 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<string | null>`(
+ 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<string | null>`(
+ 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<string | null>`(
+ 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<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_attachments
+ WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ // 코멘트 통계
+ commentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM tech_sales_rfq_comments
+ WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id}
+ )`,
+
+ unreadCommentCount: sql<number>`(
+ 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<string>`created_user.name`,
+
+ // 아이템 정보 - rfqType에 따라 다른 테이블에서 조회
+ itemName: sql<string>`
+ 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<any, any, any>,
+ 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<string | null>`rfq_created_user.name`,
+ rfqCreatedByEmail: sql<string | null>`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;
+}
+
|