diff options
Diffstat (limited to 'db/schema/risks')
| -rw-r--r-- | db/schema/risks/risks.ts | 169 |
1 files changed, 165 insertions, 4 deletions
diff --git a/db/schema/risks/risks.ts b/db/schema/risks/risks.ts index 7be18776..0a4366d6 100644 --- a/db/schema/risks/risks.ts +++ b/db/schema/risks/risks.ts @@ -8,7 +8,7 @@ import { timestamp, varchar, } from 'drizzle-orm/pg-core'; -import { eq, relations } from 'drizzle-orm'; +import { eq, relations, sql } from 'drizzle-orm'; import { users } from '../users'; import { vendors } from '../vendors'; @@ -40,8 +40,9 @@ const riskEvents = risksSchema.table('risk_events', { // ---------------------------------------------------------------------------------------------------- /* VIEWS */ -const risksView = risksSchema.view('risks_view').as((qb) => - qb +const risksView = risksSchema.view('risks_view').as((qb) => { + + return qb .select({ id: riskEvents.id, eventType: riskEvents.eventType, @@ -51,6 +52,166 @@ const risksView = risksSchema.view('risks_view').as((qb) => businessNumber: vendors.taxId, provider: riskEvents.provider, content: riskEvents.content, + prevRatingTotal: sql` + CASE + WHEN ${riskEvents.eventType} = '종합등급' THEN + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '종합등급' + AND re2.occurred_at < ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '종합등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('prev_rating_total'), + curRatingTotal: sql` + CASE + WHEN ${riskEvents.eventType} = '종합등급' THEN + ${riskEvents.content} + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '종합등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('cur_rating_total'), + prevRatingCredit: sql` + CASE + WHEN ${riskEvents.eventType} = '신용등급' THEN + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '신용등급' + AND re2.occurred_at < ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '신용등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('prev_rating_credit'), + curRatingCredit: sql` + CASE + WHEN ${riskEvents.eventType} = '신용등급' THEN + ${riskEvents.content} + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '신용등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('cur_rating_credit'), + prevRatingCashflow: sql` + CASE + WHEN ${riskEvents.eventType} = '현금흐름등급' THEN + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '현금흐름등급' + AND re2.occurred_at < ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '현금흐름등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('prev_rating_cashflow'), + curRatingCashflow: sql` + CASE + WHEN ${riskEvents.eventType} = '현금흐름등급' THEN + ${riskEvents.content} + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = '현금흐름등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('cur_rating_cashflow'), + prevRatingWatch: sql` + CASE + WHEN ${riskEvents.eventType} = 'WATCH등급' THEN + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = 'WATCH등급' + AND re2.occurred_at < ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = 'WATCH등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('prev_rating_watch'), + curRatingWatch: sql` + CASE + WHEN ${riskEvents.eventType} = 'WATCH등급' THEN + ${riskEvents.content} + ELSE + COALESCE(( + SELECT content + FROM ${risksSchema}.risk_events re2 + WHERE re2.vendor_id = ${riskEvents.vendorId} + AND re2.event_type = 'WATCH등급' + AND re2.occurred_at <= ${riskEvents.occuredAt} + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + `.as('cur_rating_watch'), eventStatus: riskEvents.eventStatus, managerId: riskEvents.managerId, managerName: users.name, @@ -60,7 +221,7 @@ const risksView = risksSchema.view('risks_view').as((qb) => .from(riskEvents) .leftJoin(vendors, eq(vendors.id, riskEvents.vendorId)) .leftJoin(users, eq(users.id, riskEvents.managerId)) -); +}); // ---------------------------------------------------------------------------------------------------- |