안녕하세요. 셀렉트어드민에 템플릿이 추가되어 안내드립니다.
주문 조회와 상황 파악, 환불 등을 운영 업무가 가능한 주문 대시보드 예제입니다.
아래와 같은 주문 대시보드를 통해
- 조건에 따라 상태를 분류해 우선순위와 상황을 빠르게 파악할 수 있습니다.
- 타임라인 뷰를 통해 히스토리를 쉽게 파악할 수 있습니다.
- 지난주와 현재 상태를 쉽게 비교할 수 있습니다.
해당 템플릿에 활용한 주요 기능
- pages layout
- tableOptions
- sortOptions
- formOptions
- display: chartjs + displayFn
- updateParams
- timelineOptions
menus:
- path: order-refund-center
name: 주문 환불 센터
pages:
- path: order-refund-center
class: container
containerStyle: >
padding: 40px
layout:
div:
- name: top-container
style:
display: flex
marginBottom: 20px
div:
- name: left-container
style:
width: calc(50% - 10px)
padding: 10px
- name: right-container
style:
width: calc(50% - 10px)
padding: 10px
- name: bottom-container
blocks:
- name: 처리 필요
layout: left-container
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
COUNT(CASE WHEN re.status = 'REQUESTED' THEN 1 END) as refund_pending,
COUNT(CASE WHEN o.order_status = 'SHIPPING' AND o.updated_at < DATE_SUB(NOW(), INTERVAL 3 DAY) THEN 1 END) as shipping_delayed,
COUNT(CASE WHEN re.status IN ('REQUESTED', 'PROCESSING') AND re.created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) as urgent_refund_exchange
FROM orders0821 o
LEFT JOIN refund_exchange0821 re ON o.order_id = re.order_id AND re.status = 'REQUESTED'
WHERE o.ordered_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
showDownload: false
tableOptions:
sortable: false
columns:
refund_pending:
label: 🟡 환불승인 대기
style: { color: '#faad14', fontWeight: 'bold', fontSize: '18px' }
shipping_delayed:
label: ⚠️ 배송지연 의심
style: { color: '#fa8c16', fontWeight: 'bold', fontSize: '18px' }
urgent_refund_exchange:
label: 🔴 24시간 초과
style: { color: '#fa8c16', fontWeight: 'bold', fontSize: '18px' }
style:
backgroundColor: '#f6ffed'
border: '2px solid #b7eb8f'
borderRadius: '8px'
padding: '8px'
- name: 주문내역 조회
layout: bottom-container
type: query
resource: mysql.qa
sqlType: select
columns:
' ':
prepend: true
buttons:
- label: 보기
openModal: order-:order_id
priority_badge:
label: 우선순위
order_id:
label: 주문ID
order_number:
label: 주문번호
ordered_at:
label: 주문일
customer_name:
label: 고객명
customer_phone:
label: 고객전화번호
products_detail:
label: 주문상품상세
final_payment_amount:
label: 최종결제금액
order_status:
label: 주문상태
valueAs:
ORDER_RECEIVED: '📋 주문접수'
PREPARING: '📦 준비중'
SHIPPING: '🚛 배송중'
DELIVERED: '✅ 배송완료'
CANCELLED: '❌ 취소완료'
REFUNDED: '💰 환불완료'
auto_process_status:
label: 자동화상태
updated_at:
label: 수정일시
showDownload: false
resetButton: true
sql: |
SELECT
CASE
WHEN re.status = 'REQUESTED' THEN '🟡 환불대기'
WHEN o.order_status = 'SHIPPING' AND o.updated_at < DATE_SUB(NOW(), INTERVAL 3 DAY) THEN '⚠️ 지연의심'
ELSE '✅ 정상'
END as priority_badge,
o.order_id,
o.order_number,
DATE_FORMAT(o.ordered_at, '%Y-%m-%d %H:%i') as ordered_at,
c.customer_name,
c.customer_phone,
(SELECT GROUP_CONCAT(
CONCAT(oi.product_name, ' (', oi.quantity, '개)')
SEPARATOR ', '
) FROM order_items0821 oi WHERE oi.order_id = o.order_id) as products_detail,
FORMAT(o.final_payment_amount, 0) as final_payment_amount,
o.order_status,
CASE
WHEN o.order_status IN ('ORDER_RECEIVED', 'PREPARING', 'SHIPPING', 'DELIVERED') THEN '🤖 자동처리'
WHEN o.payment_status IN ('CANCELLED', 'REFUNDED') THEN '🔄 셀프처리'
ELSE '⏸️ 대기중'
END as auto_process_status,
DATE_FORMAT(o.updated_at, '%Y-%m-%d %H:%i') as updated_at
FROM orders0821 o
JOIN customers0821 c ON o.customer_id = c.customer_id
LEFT JOIN stores0821 s ON o.store_id = s.store_id
LEFT JOIN refund_exchange0821 re ON o.order_id = re.order_id AND re.status = 'REQUESTED'
WHERE 1=1
AND o.ordered_at > COALESCE(NULLIF(:date1, ''), DATE_SUB(NOW(), INTERVAL 8 DAY))
AND o.ordered_at < COALESCE(NULLIF(:date2, ''), DATE_ADD(NOW(), INTERVAL 1 DAY))
AND (COALESCE(NULLIF(:order_status, ''), '') = '' OR o.order_status = :order_status)
AND (COALESCE(NULLIF(:order_number, ''), '') = '' OR o.order_number LIKE CONCAT('%', :order_number, '%'))
AND (COALESCE(NULLIF(:customer_name, ''), '') = '' OR c.customer_name LIKE CONCAT('%', :customer_name, '%'))
AND (COALESCE(NULLIF(:customer_phone, ''), '') = '' OR c.customer_phone LIKE CONCAT('%', :customer_phone, '%'))
requestFn: |
const order_status = params.find(e => e.key == 'order_status')
console.log(order_status)
multipleColumns: true
sortOptions:
enabled: true
initialSortBy:
- field: priority_badge
type: desc
formOptions:
display: col
firstLabelWidth: 180px
labelWidth: 180px
params:
- key: date
label: 주문일자
style:
width: 578px
format: date
range: true
group: 1
showButton: true
shortcuts:
- label: 최근 일주일
from:
offset: -7
period: day
to:
offset: 0
period: day
- label: 이번달
from:
offset: 0
startOf: month
to:
offset: 0
endOf: month
- key: order_status
label: 주문상태
group: 1
radioButtonGroup: true
radio:
- value: ''
label: 전체
- value: ORDER_RECEIVED
label: 주문접수
- value: PREPARING
label: 준비중
- value: SHIPPING
label: 배송중
- value: DELIVERED
label: 배송완료
- value: CANCELLED
label: 취소
- value: REFUNDED
label: 환불
- key: order_number
label: 주문번호
group: 2
- key: customer_name
label: 고객명
group: 3
- key: customer_phone
label: 고객전화번호
group: 3
searchOptions:
enabled: true
placeholder: "주문번호, 고객명, 전화번호로 검색"
paginationOptions:
enabled: true
perPage: 10
modals:
- path: order-:order_id
mode: side
header: false
name: 주문 상세 정보
blocks:
- name: 📋 주문 기본 정보
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT
o.order_number,
o.order_status,
o.final_payment_amount,
o.payment_method,
o.payment_status,
c.customer_name,
c.customer_phone,
o.receiver_name,
o.shipping_address,
o.ordered_at,
o.admin_memo
FROM orders0821 o
JOIN customers0821 c ON o.customer_id = c.customer_id
WHERE o.order_id = :order_id;
columns:
order_number:
label: 주문번호
order_status:
label: 주문상태
valueAs:
ORDER_RECEIVED: '📋 주문접수'
PREPARING: '📦 준비중'
SHIPPING: '🚛 배송중'
DELIVERED: '✅ 배송완료'
CANCELLED: '❌ 취소완료'
REFUNDED: '💰 환불완료'
final_payment_amount:
label: 최종결제금액
formatFn: number0
payment_method:
label: 결제방법
payment_status:
label: 결제상태
customer_name:
label: 고객명
customer_phone:
label: 고객전화번호
receiver_name:
label: 수취인명
shipping_address:
label: 배송주소
ordered_at:
label: 주문일시
admin_memo:
label: 관리자메모
params:
- key: order_id
valueFromRow: order_id
display: col-1
- name: 📦 주문 상품 상세
type: query
resource: mysql.qa
sqlType: select
showDownload: false
tableOptions:
sortable: false
sql: >
SELECT
oi.product_name,
oi.product_option,
oi.quantity,
oi.unit_price,
oi.total_amount,
p.brand_name
FROM order_items0821 oi
JOIN products0821 p ON oi.product_id = p.product_id
WHERE oi.order_id = :order_id
ORDER BY oi.order_item_id;
columns:
product_name:
label: 상품명
product_option:
label: 상품옵션
quantity:
label: 수량
unit_price:
label: 단가
formatFn: number0
total_amount:
label: 총금액
formatFn: number0
brand_name:
label: 브랜드명
params:
- key: order_id
valueFromRow: order_id
- name: 💰 환불/교환 정보
type: query
resource: mysql.qa
sqlType: select
showDownload: false
tableOptions:
sortable: false
sql: >
SELECT
refund_exchange_id,
CASE re.type
WHEN 'REFUND' THEN '💰 환불'
WHEN 'EXCHANGE' THEN '🔄 교환'
END as type_display,
CASE re.status
WHEN 'REQUESTED' THEN '📝 신청됨'
WHEN 'APPROVED' THEN '✅ 승인됨'
WHEN 'REJECTED' THEN '❌ 거부됨'
WHEN 'COMPLETED' THEN '✅ 완료'
END as status_display,
re.reason_detail,
re.refund_amount,
re.created_at
FROM refund_exchange0821 re
WHERE re.order_id = :order_id
ORDER BY re.created_at DESC;
columns:
refund_exchange_id:
hidden: true
type_display:
label: 처리구분
status_display:
label: 처리상태
reason_detail:
label: 상세사유
refund_amount:
label: 환불금액
formatFn: number0
created_at:
label: 신청일시
params:
- key: order_id
valueFromRow: order_id
selectOptions:
enabled: true
actions:
- label: 환불승인
placement: right bottom
type: query
resource: mysql.qa
sqlType: select 1
params:
- key: refund_amount
label: 환불금액
- key: refund_exchange_id
valueFromSelectedRows: refund_exchange_id
confirm: |
환불을 진행하시겠습니까?
- 환불/교환ID: {{refund_exchange_id}}
- 환불금액: {{refund_amount}}
- name: 📈 처리 히스토리
style: >
padding-top: 100px;
margin-top: 100px;
type: query
resource: mysql.qa
sqlType: select
showDownload: false
tableOptions:
sortable: false
sql: >
SELECT
osh.created_at,
CASE osh.previous_status
WHEN 'ORDER_RECEIVED' THEN '주문접수'
WHEN 'PREPARING' THEN '준비중'
WHEN 'SHIPPING' THEN '배송중'
WHEN 'DELIVERED' THEN '배송완료'
WHEN 'CANCELLED' THEN '취소완료'
WHEN 'REFUNDED' THEN '환불완료'
ELSE osh.previous_status
END as previous_status,
CASE osh.new_status
WHEN 'ORDER_RECEIVED' THEN '주문접수'
WHEN 'PREPARING' THEN '준비중'
WHEN 'SHIPPING' THEN '배송중'
WHEN 'DELIVERED' THEN '배송완료'
WHEN 'CANCELLED' THEN '취소완료'
WHEN 'REFUNDED' THEN '환불완료'
ELSE osh.new_status
END as new_status,
CASE
WHEN osh.changed_by_admin_id IS NULL THEN '시스템'
WHEN osh.changed_by_admin_id = 0 THEN '고객'
ELSE CONCAT('관리자 ', osh.changed_by_admin_id)
END as changed_by_display,
osh.change_reason
FROM order_status_history0821 osh
WHERE osh.order_id = :order_id
ORDER BY osh.created_at DESC;
display: timeline
timelineOptions:
useColumn: created_at
template: |
<b>{{changed_by_display}}</b>가 주문 상태를 변경했습니다.<br>
{{previous_status}} → <b>{{new_status}}</b><br>
<small>사유: {{change_reason}}</small>
columns:
created_at:
label: 처리일시
previous_status:
label: 이전상태
new_status:
label: 변경상태
changed_by_display:
label: 처리자
change_reason:
label: 변경사유
params:
- key: order_id
valueFromRow: order_id
- name: 🚨 처리 대기중인 환불/교환 목록
layout: bottom-container
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT
CASE re.type
WHEN 'REFUND' THEN '💰'
WHEN 'EXCHANGE' THEN '🔄'
END as type_icon,
re.refund_exchange_id,
o.order_number,
c.customer_name,
c.customer_phone,
FORMAT(o.final_payment_amount, 0) as final_payment_amount,
FORMAT(re.refund_amount, 0) as refund_amount,
re.reason_detail,
CASE
WHEN re.created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN '🔴 24시간 초과'
WHEN re.created_at < DATE_SUB(NOW(), INTERVAL 12 HOUR) THEN '🟡 12시간 초과'
ELSE '🟢 정상'
END as urgency_status,
DATE_FORMAT(re.created_at, '%Y-%m-%d %H:%i') as created_at,
DATE_FORMAT(o.ordered_at, '%Y-%m-%d') as ordered_at
FROM refund_exchange0821 re
JOIN orders0821 o ON re.order_id = o.order_id
JOIN customers0821 c ON o.customer_id = c.customer_id
WHERE re.status IN ('REQUESTED', 'PROCESSING')
AND (LENGTH(:order_number)=0 OR order_number = :order_number)
ORDER BY
CASE
WHEN re.created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1
WHEN re.created_at < DATE_SUB(NOW(), INTERVAL 12 HOUR) THEN 2
ELSE 3
END ASC,
re.created_at ASC;
columns:
type_icon:
label: 구분
refund_exchange_id:
label: 환불교환ID
urgency_status:
label: 긴급도
order_number:
label: 주문번호
updateParams:
order_number: "{{order_number}}"
customer_name:
label: 고객명
customer_phone:
label: 연락처
final_payment_amount:
label: 주문금액
refund_amount:
label: 환불요청금액
reason_detail:
label: 사유
created_at:
label: 신청일시
ordered_at:
label: 주문일
paginationOptions:
enabled: true
perPage: 10
- name: 오늘 지난주 주문 현황
layout: right-container
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT
CASE o.order_status
WHEN 'ORDER_RECEIVED' THEN '📋 주문접수'
WHEN 'PREPARING' THEN '📦 준비중'
WHEN 'SHIPPING' THEN '🚛 배송중'
WHEN 'DELIVERED' THEN '✅ 배송완료'
WHEN 'CANCELLED' THEN '❌ 취소'
WHEN 'REFUNDED' THEN '💰 환불'
ELSE o.order_status
END as order_status_display,
SUM(CASE WHEN DATE(o.ordered_at) = CURDATE() AND TIME(o.ordered_at) <= CURTIME() THEN 1 ELSE 0 END) as today_count,
SUM(CASE WHEN DATE(o.ordered_at) = CURDATE() AND TIME(o.ordered_at) <= CURTIME() THEN o.final_payment_amount ELSE 0 END) as today_amount,
SUM(CASE WHEN DATE(o.ordered_at) = DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND TIME(o.ordered_at) <= CURTIME() THEN 1 ELSE 0 END) as last_week_count,
SUM(CASE WHEN DATE(o.ordered_at) = DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND TIME(o.ordered_at) <= CURTIME() THEN o.final_payment_amount ELSE 0 END) as last_week_amount,
CASE
WHEN SUM(CASE WHEN DATE(o.ordered_at) = DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND TIME(o.ordered_at) <= CURTIME() THEN 1 ELSE 0 END) > 0
THEN ROUND((SUM(CASE WHEN DATE(o.ordered_at) = CURDATE() AND TIME(o.ordered_at) <= CURTIME() THEN 1 ELSE 0 END) - SUM(CASE WHEN DATE(o.ordered_at) = DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND TIME(o.ordered_at) <= CURTIME() THEN 1 ELSE 0 END)) / SUM(CASE WHEN DATE(o.ordered_at) = DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND TIME(o.ordered_at) <= CURTIME() THEN 1 ELSE 0 END) * 100, 1)
ELSE NULL
END as count_change_percent
FROM orders0821 o
WHERE (DATE(o.ordered_at) = CURDATE() AND TIME(o.ordered_at) <= CURTIME())
OR (DATE(o.ordered_at) = DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND TIME(o.ordered_at) <= CURTIME())
GROUP BY o.order_status
HAVING today_count > 0 OR last_week_count > 0
ORDER BY today_count DESC
display: chartjs
displayFn: |
const labels = rows.map(row => row.order_status_display);
const todayData = rows.map(row => row.today_count);
const lastWeekData = rows.map(row => row.last_week_count);
const changePercents = rows.map(row => row.count_change_percent);
// 상태별 색상 정의
const getStatusColor = (status, alpha = 0.8) => {
const colorMap = {
'📋 주문접수': `rgba(24, 144, 255, ${alpha})`,
'📦 준비중': `rgba(255, 193, 7, ${alpha})`,
'🚛 배송중': `rgba(82, 196, 26, ${alpha})`,
'✅ 배송완료': `rgba(52, 196, 26, ${alpha})`,
'❌ 취소': `rgba(245, 34, 45, ${alpha})`,
'💰 환불': `rgba(245, 34, 45, ${alpha})`
};
return colorMap[status] || `rgba(217, 217, 217, ${alpha})`;
};
const todayColors = labels.map(label => getStatusColor(label, 0.8));
const lastWeekColors = labels.map(label => getStatusColor(label, 0.4));
const data = {
labels: labels,
datasets: [{
label: '오늘 (현재시각까지)',
data: todayData,
backgroundColor: todayColors,
borderColor: todayColors.map(color => color.replace('0.8', '1')),
borderWidth: 1
}, {
label: '지난주 같은 요일 (같은 시각까지)',
data: lastWeekData,
backgroundColor: lastWeekColors,
borderColor: lastWeekColors.map(color => color.replace('0.4', '1')),
borderWidth: 1
}]
};
const config = {
type: 'bar',
data: data,
options: {
indexAxis: 'y',
responsive: true,
maintainAspectRatio: false,
plugins: {
title: {
display: true,
text: '🕐 오늘 vs 지난주 주문 현황',
font: { size: 16 }
},
tooltip: {
callbacks: {
afterLabel: function(context) {
const index = context.dataIndex;
const change = changePercents[index];
const todayAmount = rows[index].today_amount;
const lastWeekAmount = rows[index].last_week_amount;
return [
`변화율: ${change ? (change > 0 ? '+' : '') + change + '%' : 'N/A'}`,
`오늘 금액: ${todayAmount.toLocaleString()}원`,
`지난주 금액: ${lastWeekAmount.toLocaleString()}원`
];
}
}
},
legend: {
position: 'bottom'
}
},
scales: {
x: {
title: {
display: true,
text: '주문 건수'
},
beginAtZero: true
},
y: {
title: {
display: true,
text: '주문 상태'
}
}
}
}
};
const myChart = new Chart(ctx, config);
해당 템플릿에 대해 궁금한점이 있으시다면 문의주세요.
아울러 다른 해결하고 싶은 문제나 효율적인 업무를 위해 필요한 템플릿 주제가 있다면 편하게 말씀해주시기 바랍니다.
감사합니다.
