diff options
| -rw-r--r-- | config/risksConfig.ts | 47 | ||||
| -rw-r--r-- | db/schema/risks/risks.ts | 169 | ||||
| -rw-r--r-- | lib/risk-management/table/risks-columns.tsx | 421 | ||||
| -rw-r--r-- | lib/risk-management/table/risks-table.tsx | 13 |
4 files changed, 633 insertions, 17 deletions
diff --git a/config/risksConfig.ts b/config/risksConfig.ts index ceeb93f4..d0bcb9e9 100644 --- a/config/risksConfig.ts +++ b/config/risksConfig.ts @@ -63,6 +63,49 @@ const RISK_DASHBOARD_MENU_ITEM_LIST = [ '기업회생', '휴/폐업', ]; +const RISK_RATING_TOTAL_LIST = [ + { label: '1', value: 1 }, + { label: '2', value: 2 }, + { label: '3', value: 3 }, + { label: '4', value: 4 }, + { label: '5', value: 5 }, + { label: '6', value: 6 }, + { label: '7', value: 7 }, +]; +const RISK_RATING_CREDIT_LIST = [ + { label: 'AAA', value: 1 }, + { label: 'AA', value: 2 }, + { label: 'A', value: 3 }, + { label: 'BBB', value: 4 }, + { label: 'BB', value: 5 }, + { label: 'B', value: 6 }, + { label: 'CCC', value: 7 }, + { label: 'CC', value: 8 }, + { label: 'C', value: 9 }, + { label: 'D', value: 10 }, + { label: 'R', value: 11 }, +]; +const RISK_RATING_CASHFLOW_LIST = [ + { label: 'CR-1', value: 1 }, + { label: 'CR-2', value: 2 }, + { label: 'CR-3', value: 3 }, + { label: 'CR-4', value: 4 }, + { label: 'CR-5', value: 5 }, + { label: 'CR-6', value: 6 }, + { label: 'NF', value: 7 }, + { label: 'NR', value: 8 }, +]; +const RISK_RATING_WATCH_LIST =[ + { label: '정상', value: 1 }, + { label: '유보', value: 2 }, + { label: '관찰', value: 3 }, + { label: '주의', value: 4 }, + { label: '경보', value: 5 }, + { label: '위험', value: 6 }, + { label: '회수의문', value: 7 }, + { label: '휴폐업', value: 8 }, + { label: '부도', value: 9 }, +]; // ---------------------------------------------------------------------------------------------------- @@ -72,4 +115,8 @@ export { RISK_DASHBOARD_MENU_ITEM_LIST, RISK_EVENT_TYPE_LIST, RISK_PROVIDER_LIST, + RISK_RATING_CASHFLOW_LIST, + RISK_RATING_CREDIT_LIST, + RISK_RATING_TOTAL_LIST, + RISK_RATING_WATCH_LIST, };
\ No newline at end of file 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)) -); +}); // ---------------------------------------------------------------------------------------------------- diff --git a/lib/risk-management/table/risks-columns.tsx b/lib/risk-management/table/risks-columns.tsx index fe98448a..cd1aca39 100644 --- a/lib/risk-management/table/risks-columns.tsx +++ b/lib/risk-management/table/risks-columns.tsx @@ -13,6 +13,13 @@ import { DropdownMenuItem, DropdownMenuTrigger, } from '@/components/ui/dropdown-menu'; +import { + RISK_RATING_CASHFLOW_LIST, + RISK_RATING_CREDIT_LIST, + RISK_RATING_TOTAL_LIST, + RISK_RATING_WATCH_LIST +} from '@/config/risksConfig'; +import { type AppRouterInstance } from 'next/dist/shared/lib/app-router-context.shared-runtime'; import { type ColumnDef } from '@tanstack/react-table'; import { type DataTableRowAction } from '@/types/table'; import { type RisksView } from '@/db/schema'; @@ -21,13 +28,16 @@ import { type RisksView } from '@/db/schema'; /* TYPES */ interface GetColumnsProps { - setRowAction: Dispatch<SetStateAction<DataTableRowAction<RisksView> | null>>, + setRowAction: Dispatch<SetStateAction<DataTableRowAction<RisksView> | null>>; + router: AppRouterInstance; + paramsLanguage: string; }; // ---------------------------------------------------------------------------------------------------- /* FUNCTION FOR GETTING COLUMNS SETTING */ -function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { +function getColumns(props: GetColumnsProps): ColumnDef<RisksView>[] { + const { setRowAction, router, paramsLanguage } = props; // [1] SELECT COLUMN - CHECKBOX const selectColumn: ColumnDef<RisksView> = { @@ -53,7 +63,6 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { ), enableSorting: false, enableHiding: false, - size: 40, }; // [2] SOURCE COLUMNS @@ -173,7 +182,7 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { <DataTableColumnHeaderSimple column={column} title="상세 내용" /> ), cell: ({ row }) => { - const value = row.getValue<string>('content') ?? '-'; + const value = row.getValue<string>('content'); return ( <div className="font-regular max-w-[150px] truncate" title={value}> {value} @@ -187,7 +196,6 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { group: 'Risk Information', type: 'text', }, - size: 100, }, { accessorKey: 'occuredAt', @@ -212,7 +220,379 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { }, ]; - // [3] INPUT COLUMNS + // [3] TOTAL RATING COLUMNS + const ratingTotalColumns: ColumnDef<RisksView>[] = [ + { + accessorKey: 'prevRatingTotal', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="이전" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('prevRatingTotal'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Previous', + group: 'Total Rating', + type: 'text', + }, + }, + { + accessorKey: 'curRatingTotal', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="현재" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('curRatingTotal'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Current', + group: 'Total Rating', + type: 'text', + }, + }, + { + accessorKey: 'changeRatingTotal', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="변동" /> + ), + cell: ({ row }) => { + const prevValue = row.getValue<string>('prevRatingTotal'); + const curValue = row.getValue<string>('curRatingTotal'); + + if (prevValue === '-' || curValue === '-' || !prevValue || !curValue) { + return ( + <div className="font-regular"> + - + </div> + ); + } + const prevRating = RISK_RATING_TOTAL_LIST.find(rating => rating.label === prevValue); + const curRating = RISK_RATING_TOTAL_LIST.find(rating => rating.label === curValue); + + if (!prevRating || !curRating || prevRating === curRating) { + return ( + <div className="font-regular"> + - + </div> + ); + } else if (prevRating.value > curRating.value) { + return ( + <div className="font-regular text-red-500"> + ▲ + </div> + ); + } else { + return ( + <div className="font-regular text-blue-500"> + ▼ + </div> + ); + } + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Change', + group: 'Total Rating', + type: 'text', + }, + }, + ]; + + // [4] CREDIT RATING COLUMNS + const ratingCreditColumns: ColumnDef<RisksView>[] = [ + { + accessorKey: 'prevRatingCredit', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="이전" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('prevRatingCredit'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Previous', + group: 'Credit Rating', + type: 'text', + }, + }, + { + accessorKey: 'curRatingCredit', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="현재" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('curRatingCredit'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Current', + group: 'Credit Rating', + type: 'text', + }, + }, + { + accessorKey: 'changeRatingCredit', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="변동" /> + ), + cell: ({ row }) => { + const prevValue = row.getValue<string>('prevRatingCredit'); + const curValue = row.getValue<string>('curRatingCredit'); + + if (prevValue === '-' || curValue === '-' || !prevValue || !curValue) { + return ( + <div className="font-regular"> + - + </div> + ); + } + const prevRating = RISK_RATING_CREDIT_LIST.find(rating => rating.label === prevValue); + const curRating = RISK_RATING_CREDIT_LIST.find(rating => rating.label === curValue); + + if (!prevRating || !curRating || prevRating === curRating) { + return ( + <div className="font-regular"> + - + </div> + ); + } else if (prevRating.value > curRating.value) { + return ( + <div className="font-regular text-red-500"> + ▲ + </div> + ); + } else { + return ( + <div className="font-regular text-blue-500"> + ▼ + </div> + ); + } + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Change', + group: 'Credit Rating', + type: 'text', + }, + }, + ]; + + // [5] CASHFLOW RATING COLUMNS + const ratingCashflowColumns: ColumnDef<RisksView>[] = [ + { + accessorKey: 'prevRatingCashflow', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="이전" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('prevRatingCashflow'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Previous', + group: 'Cashflow Rating', + type: 'text', + }, + }, + { + accessorKey: 'curRatingCashflow', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="현재" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('curRatingCashflow'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Current', + group: 'Cashflow Rating', + type: 'text', + }, + }, + { + accessorKey: 'changeRatingCashflow', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="변동" /> + ), + cell: ({ row }) => { + const prevValue = row.getValue<string>('prevRatingCashflow'); + const curValue = row.getValue<string>('curRatingCashflow'); + + if (prevValue === '-' || curValue === '-' || !prevValue || !curValue) { + return ( + <div className="font-regular"> + - + </div> + ); + } + const prevRating = RISK_RATING_CASHFLOW_LIST.find(rating => rating.label === prevValue); + const curRating = RISK_RATING_CASHFLOW_LIST.find(rating => rating.label === curValue); + + if (!prevRating || !curRating || prevRating === curRating) { + return ( + <div className="font-regular"> + - + </div> + ); + } else if (prevRating.value > curRating.value) { + return ( + <div className="font-regular text-red-500"> + ▲ + </div> + ); + } else { + return ( + <div className="font-regular text-blue-500"> + ▼ + </div> + ); + } + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Change', + group: 'Cashflow Rating', + type: 'text', + }, + }, + ]; + + // [6] WATCH RATING COLUMNS + const ratingWatchColumns: ColumnDef<RisksView>[] = [ + { + accessorKey: 'prevRatingWatch', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="이전" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('prevRatingWatch'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Previous', + group: 'Watch Rating', + type: 'text', + }, + }, + { + accessorKey: 'curRatingWatch', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="현재" /> + ), + cell: ({ row }) => { + const value = row.getValue<string>('curRatingWatch'); + return ( + <div className="font-regular"> + {value} + </div> + ); + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Current', + group: 'Watch Rating', + type: 'text', + }, + }, + { + accessorKey: 'changeRatingWatch', + header: ({ column }) => ( + <DataTableColumnHeaderSimple column={column} title="변동" /> + ), + cell: ({ row }) => { + const prevValue = row.getValue<string>('prevRatingWatch'); + const curValue = row.getValue<string>('curRatingWatch'); + + if (prevValue === '-' || curValue === '-' || !prevValue || !curValue) { + return ( + <div className="font-regular"> + - + </div> + ); + } + const prevRating = RISK_RATING_WATCH_LIST.find(rating => rating.label === prevValue); + const curRating = RISK_RATING_WATCH_LIST.find(rating => rating.label === curValue); + + if (!prevRating || !curRating || prevRating === curRating) { + return ( + <div className="font-regular"> + - + </div> + ); + } else if (prevRating.value > curRating.value) { + return ( + <div className="font-regular text-red-500"> + ▲ + </div> + ); + } else { + return ( + <div className="font-regular text-blue-500"> + ▼ + </div> + ); + } + }, + enableSorting: true, + enableHiding: false, + meta: { + excelHeader: 'Change', + group: 'Watch Rating', + type: 'text', + }, + }, + ]; + + // [7] INPUT COLUMNS const inputColumns: ColumnDef<RisksView>[] = [ { accessorKey: 'eventStatus', @@ -286,11 +666,10 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { group: 'Risk Management', type: 'text', }, - size: 300, }, ]; - // [4] ACTIONS COLUMN - DROPDOWN MENU WITH VIEW ACTION + // [8] ACTIONS COLUMN - DROPDOWN MENU WITH VIEW ACTION const actionsColumn: ColumnDef<RisksView> = { id: 'actions', enableHiding: false, @@ -315,8 +694,9 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { 리스크 관리 </DropdownMenuItem> <DropdownMenuItem - onSelect={() => { - // 신용정보 관리화면으로 이동 + onClick={() => { + const vendorId = row.original.vendorId; + router.push(`/${paramsLanguage}/evcp/vendors/${vendorId}/info/credit`); }} className="cursor-pointer" > @@ -327,7 +707,6 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { </DropdownMenu> ) }, - size: 80, }; return [ @@ -338,6 +717,26 @@ function getColumns({ setRowAction }: GetColumnsProps): ColumnDef<RisksView>[] { columns: sourceColumns, }, { + id: 'ratingTotal', + header: '종합등급', + columns: ratingTotalColumns, + }, + { + id: 'ratingCredit', + header: '신용등급', + columns: ratingCreditColumns, + }, + { + id: 'ratingCashflow', + header: '현금흐름등급', + columns: ratingCashflowColumns, + }, + { + id: 'ratingWatch', + header: 'WATCH등급', + columns: ratingWatchColumns, + }, + { id: 'riskInput', header: '리스크 관리', columns: inputColumns, diff --git a/lib/risk-management/table/risks-table.tsx b/lib/risk-management/table/risks-table.tsx index d6317c26..2bd328f2 100644 --- a/lib/risk-management/table/risks-table.tsx +++ b/lib/risk-management/table/risks-table.tsx @@ -16,7 +16,7 @@ import { } from '@/types/table'; import { useDataTable } from '@/hooks/use-data-table'; import { use, useCallback, useMemo, useState } from 'react'; -import { useRouter } from 'next/navigation'; +import { useParams, useRouter } from 'next/navigation'; import { RISK_EVENT_TYPE_LIST, RISK_PROVIDER_LIST } from '@/config/risksConfig'; // ---------------------------------------------------------------------------------------------------- @@ -33,12 +33,13 @@ interface RisksTableProps { /* TABLE COMPONENT */ function RisksTable({ promises }: RisksTableProps) { const router = useRouter(); + const params = useParams(); const [rowAction, setRowAction] = useState<DataTableRowAction<RisksView> | null>(null); const [isMailDialogOpen, setIsMailDialogOpen] = useState(false); const [promiseData] = use(promises); const tableData = promiseData; const columns = useMemo( - () => getColumns({ setRowAction }), + () => getColumns({ setRowAction, router, paramsLanguage: params?.lng as string ?? 'ko' }), [setRowAction], ); @@ -127,6 +128,14 @@ function RisksTable({ promises }: RisksTableProps) { managerId: 0, managerName: '', adminComment: '', + prevRatingTotal: '', + curRatingTotal: '', + prevRatingCredit: '', + curRatingCredit: '', + prevRatingCashflow: '', + curRatingCashflow: '', + prevRatingWatch: '', + curRatingWatch: '', occuredAt: new Date(), }; |