summaryrefslogtreecommitdiff
path: root/lib/techsales-rfq/repository.ts
diff options
context:
space:
mode:
Diffstat (limited to 'lib/techsales-rfq/repository.ts')
-rw-r--r--lib/techsales-rfq/repository.ts325
1 files changed, 255 insertions, 70 deletions
diff --git a/lib/techsales-rfq/repository.ts b/lib/techsales-rfq/repository.ts
index 66c0b345..e9ad3925 100644
--- a/lib/techsales-rfq/repository.ts
+++ b/lib/techsales-rfq/repository.ts
@@ -3,17 +3,18 @@
import {
techSalesRfqs,
techSalesVendorQuotations,
- vendors,
users,
- itemShipbuilding
+ biddingProjects
} from "@/db/schema";
+import { techVendors } from "@/db/schema/techVendors";
import {
asc,
- desc, count, SQL, sql
+ desc, count, SQL, sql, eq
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
+
export type NewTechSalesRfq = typeof techSalesRfqs.$inferInsert;
/**
* 기술영업 RFQ 생성
@@ -61,27 +62,29 @@ export async function countTechSalesRfqs(
return res[0]?.count ?? 0;
}
+
/**
- * RFQ 정보 직접 조인 조회 (뷰 대신 테이블 조인 사용)
+ * 기술영업 RFQ 조회 with 조인 (Repository)
*/
export async function selectTechSalesRfqsWithJoin(
tx: PgTransaction<any, any, any>,
- params: {
- where?: 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 } = params;
+ const { where, orderBy, offset = 0, limit = 10, rfqType } = options;
// 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
- const query = tx.select({
+ let query = tx.select({
// RFQ 기본 정보
id: techSalesRfqs.id,
rfqCode: techSalesRfqs.rfqCode,
- itemShipbuildingId: techSalesRfqs.itemShipbuildingId,
- itemName: itemShipbuilding.itemList,
+ rfqType: techSalesRfqs.rfqType,
+ biddingProjectId: techSalesRfqs.biddingProjectId,
materialCode: techSalesRfqs.materialCode,
// 날짜 및 상태 정보
@@ -93,6 +96,7 @@ export async function selectTechSalesRfqsWithJoin(
picCode: techSalesRfqs.picCode,
remark: techSalesRfqs.remark,
cancelReason: techSalesRfqs.cancelReason,
+ description: techSalesRfqs.description,
// 생성/수정 정보
createdAt: techSalesRfqs.createdAt,
@@ -106,16 +110,12 @@ export async function selectTechSalesRfqsWithJoin(
sentBy: techSalesRfqs.sentBy,
sentByName: sql<string | null>`sent_user.name`,
- // 프로젝트 정보 (스냅샷)
- projectSnapshot: techSalesRfqs.projectSnapshot,
- seriesSnapshot: techSalesRfqs.seriesSnapshot,
-
- // 프로젝트 핵심 정보
- pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
- projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
- sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
- projMsrm: sql<number>`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`,
- ptypeNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`,
+ // 프로젝트 정보 (조인)
+ pspid: biddingProjects.pspid,
+ projNm: biddingProjects.projNm,
+ sector: biddingProjects.sector,
+ projMsrm: biddingProjects.projMsrm,
+ ptypeNm: biddingProjects.ptypeNm,
// 첨부파일 개수
attachmentCount: sql<number>`(
@@ -130,20 +130,43 @@ export async function selectTechSalesRfqsWithJoin(
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(itemShipbuilding, sql`${techSalesRfqs.itemShipbuildingId} = ${itemShipbuilding.id}`)
+
+ // 프로젝트 정보 조인 추가
+ .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`);
+ .leftJoin(sql`${users} AS sent_user`, sql`${techSalesRfqs.sentBy} = sent_user.id`)
+
+;
- // where 조건 적용
- const queryWithWhere = where ? query.where(where) : query;
+ // 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
- ? queryWithWhere.orderBy(...orderBy)
- : queryWithWhere.orderBy(desc(techSalesRfqs.createdAt));
+ ? query.orderBy(...orderBy)
+ : query.orderBy(desc(techSalesRfqs.createdAt));
// offset과 limit 적용 후 실행
return queryWithOrderBy.offset(offset).limit(limit);
@@ -154,13 +177,23 @@ export async function selectTechSalesRfqsWithJoin(
*/
export async function countTechSalesRfqsWithJoin(
tx: PgTransaction<any, any, any>,
- where?: 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)
- .leftJoin(itemShipbuilding, sql`${techSalesRfqs.itemShipbuildingId} = ${itemShipbuilding.id}`)
- .where(where ?? undefined);
+ .where(finalWhere);
return res[0]?.count ?? 0;
}
@@ -171,22 +204,24 @@ export async function selectTechSalesVendorQuotationsWithJoin(
tx: PgTransaction<any, any, any>,
params: {
where?: any;
- orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
+ orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
offset?: number;
limit?: number;
+ rfqType?: "SHIP" | "TOP" | "HULL";
}
) {
- const { where, orderBy, offset = 0, limit = 10 } = params;
+ const { where, orderBy, offset = 0, limit = 10, rfqType } = params;
// 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
- const query = tx.select({
+ let query = tx.select({
// 견적 기본 정보
id: techSalesVendorQuotations.id,
rfqId: techSalesVendorQuotations.rfqId,
rfqCode: techSalesRfqs.rfqCode,
+ rfqType: techSalesRfqs.rfqType,
vendorId: techSalesVendorQuotations.vendorId,
- vendorName: vendors.vendorName,
- vendorCode: vendors.vendorCode,
+ vendorName: techVendors.vendorName,
+ vendorCode: techVendors.vendorCode,
// 견적 상세 정보
totalPrice: techSalesVendorQuotations.totalPrice,
@@ -210,13 +245,11 @@ export async function selectTechSalesVendorQuotationsWithJoin(
// 프로젝트 정보
materialCode: techSalesRfqs.materialCode,
- itemShipbuildingId: techSalesRfqs.itemShipbuildingId,
- itemName: itemShipbuilding.itemList,
- // 프로젝트 핵심 정보
- pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
- projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
- sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
+ // 프로젝트 핵심 정보 - null 체크 추가
+ pspid: techSalesRfqs.biddingProjectId,
+ projNm: biddingProjects.projNm,
+ sector: biddingProjects.sector,
// 첨부파일 개수
attachmentCount: sql<number>`(
@@ -224,21 +257,33 @@ export async function selectTechSalesVendorQuotationsWithJoin(
FROM tech_sales_attachments
WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
)`,
+
})
.from(techSalesVendorQuotations)
.leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
- .leftJoin(vendors, sql`${techSalesVendorQuotations.vendorId} = ${vendors.id}`)
- .leftJoin(itemShipbuilding, sql`${techSalesRfqs.itemShipbuildingId} = ${itemShipbuilding.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`);
- // where 조건 적용
- const queryWithWhere = where ? query.where(where) : query;
+ // 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
- ? queryWithWhere.orderBy(...orderBy)
- : queryWithWhere.orderBy(desc(techSalesVendorQuotations.createdAt));
+ ? query.orderBy(...orderBy)
+ : query.orderBy(desc(techSalesVendorQuotations.createdAt));
// offset과 limit 적용 후 실행
return queryWithOrderBy.offset(offset).limit(limit);
@@ -249,15 +294,25 @@ export async function selectTechSalesVendorQuotationsWithJoin(
*/
export async function countTechSalesVendorQuotationsWithJoin(
tx: PgTransaction<any, any, any>,
- where?: 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(vendors, sql`${techSalesVendorQuotations.vendorId} = ${vendors.id}`)
- .leftJoin(itemShipbuilding, sql`${techSalesRfqs.itemShipbuildingId} = ${itemShipbuilding.id}`)
- .where(where ?? undefined);
+ .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`)
+ .where(finalWhere);
return res[0]?.count ?? 0;
}
@@ -271,30 +326,28 @@ export async function selectTechSalesDashboardWithJoin(
orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
offset?: number;
limit?: number;
+ rfqType?: "SHIP" | "TOP" | "HULL";
}
) {
- const { where, orderBy, offset = 0, limit = 10 } = params;
+ const { where, orderBy, offset = 0, limit = 10, rfqType } = params;
// 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
- const query = tx.select({
+ 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,
-
- // 아이템 정보
- itemShipbuildingId: techSalesRfqs.itemShipbuildingId,
- itemName: itemShipbuilding.itemList,
-
- // 프로젝트 정보
- pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
- projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
- sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
- projMsrm: sql<number>`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`,
- ptypeNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`,
+
+ // 프로젝트 정보 - null 체크 추가
+ pspid: techSalesRfqs.biddingProjectId,
+ projNm: biddingProjects.projNm,
+ sector: biddingProjects.sector,
+ projMsrm: biddingProjects.projMsrm,
+ ptypeNm: biddingProjects.ptypeNm,
// 벤더 견적 통계
vendorCount: sql<number>`(
@@ -362,20 +415,152 @@ export async function selectTechSalesDashboardWithJoin(
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(itemShipbuilding, sql`${techSalesRfqs.itemShipbuildingId} = ${itemShipbuilding.id}`)
- .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`);
-
- // where 조건 적용
- const queryWithWhere = where ? query.where(where) : query;
+ .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
- ? queryWithWhere.orderBy(...orderBy)
- : queryWithWhere.orderBy(desc(techSalesRfqs.updatedAt));
+ ? 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;
+}
+
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 05:49:34 +0000