안녕하세요.
셀렉트어드민에 템플릿이 추가되었습니다.
이번 예제는 구독 비즈니스 운영 관리 전반(대시보드, 사용자, 결제/구독, 고객지원)을 아우르는 통합 템플릿입니다.
- 운영 데이터를 직관적으로 확인하고 빠르게 상황을 파악할 수 있습니다.
- 다양한 화면과 기능을 하나의 흐름 안에서 연결해 관리할 수 있습니다.
- 고객지원, 결제, 사용자 상태까지 연계된 운영 프로세스를 효율적으로 처리할 수 있습니다.
해당 템플릿에 활용한 주요 기능:
- pages layout
- colorFn
- valueAs
- openPopper
- updateParams
- tabOptions
- formOptions
- type: toggle
대시보드
menus:
- path: pages/5o6GOc
name: 대시보드
group: 5o6GOc
icon: mdi-view-dashboard
pages:
- path: pages/5o6GOc
blocks:
- name: KPI 통합 계산
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
(SELECT COUNT(id) FROM users) AS total_users,
(SELECT COUNT(id) FROM users WHERE status = 'active') AS active_users,
(SELECT
SUM(CASE WHEN p.billing_cycle = 'monthly' THEN p.price ELSE 0 END) +
SUM(CASE WHEN p.billing_cycle = 'yearly' THEN p.price / 12 ELSE 0 END)
FROM subscriptions s
JOIN plans p ON s.plan_id = p.id
WHERE s.status = 'active') AS total_mrr,
(SELECT COUNT(id)
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS new_users_last_30_days,
(SELECT COUNT(id)
FROM mrr_movements
WHERE movement_type = 'churn'
AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS churn_count_last_30_days;
display: metric
showDownload: false
columns:
total_users:
label: 총 사용자
format: number
active_users:
label: 활성 사용자
format: number
total_mrr:
label: 현재 총 MRR
format: number
new_users_last_30_days:
label: 최근 30일간 신규 가입자
format: number
churn_count_last_30_days:
label: 최근 30일간 발생한 이탈
format: number
- name: 최근 가입한 사용자 5명
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT id, name, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 5;
showDownload: false
columns:
id:
label: 사용자ID
name:
label: 이름
email:
label: 이메일
created_at:
label: 생성일
- name: 최근 생성된 고객 지원 티켓 5개
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT id, subject, user_id, status, created_at,
CASE WHEN status = 'closed' THEN '-' ELSE DATEDIFF(NOW(), created_at) END AS gap_today
FROM support_tickets
ORDER BY id DESC
LIMIT 5;
showDownload: false
columns:
id:
label: 티켓ID
subject:
label: 제목
user_id:
label: 사용자ID
status:
label: 상태
color:
open: red
in_progress: green
closed: gray
valueAs:
open: 열림
in_progress: 진행중
closed: 종료
created_at:
label: 생성일
gap_today:
label: 경과일
colorFn: |
if (value === '-') return '';
return value < 5 ? 'green' : 'red';
사용자 관리
menus:
- path: pages/5o6GOc-user
name: 사용자 관리
group: 5o6GOc
icon: mdi-account-group
pages:
- path: pages/5o6GOc-user
blocks:
- name: 사용자 목록 조회 및 검색
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT u.id, u.name, u.email, u.company_name, u.status, u.created_at, GROUP_CONCAT(r.name) AS roles
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE (u.name LIKE CONCAT('%', :search_keyword, '%')
OR u.email LIKE CONCAT('%', :search_keyword, '%'))
GROUP BY u.id
ORDER BY u.id DESC
paginationOptions:
enabled: true
perPage: 20
params:
- key: search_keyword
label: 통합 검색어
columns:
id:
label: ID
width: 80px
style:
fontFamily: "monospace"
color: "#6b7280"
name:
label: 이름
filterOptions:
enabled: true
placeholder: 이름 검색
style:
fontWeight: "500"
color: "#111827"
email:
label: 이메일
filterOptions:
enabled: true
placeholder: 이메일 검색
format: email
style:
color: "#2563eb"
company_name:
label: 회사명
style:
color: "#374151"
status:
style: >
display: flex; gap: 8px; align-items: center; justify-content: flex-end;
label: 상태
valueAs:
active: 활성
inactive: 비활성
pending: 대기
color:
active: green
inactive: red
pending: yellow
buttons:
- label: 변경
openModal: edit-status
- label: 변경2
openPopper: true
popperStyle:
width: 500px
height: 300px
overflow: auto
padding: 24px
backgroundColor: "#ffffff"
borderRadius: "12px"
boxShadow: "0 8px 25px rgba(0,0,0,0.15)"
border: "1px solid #e5e7eb"
blocks:
- name: 사용자 상태 변경
type: query
resource: mysql.qa
sqlType: update
sql: >
UPDATE users SET status = :status WHERE id = :user_id;
display: form
formOptions:
firstLabelWidth: 100px
params:
- key: user_id
label: 사용자ID
valueFromRow: id
hidden: false
disabled: true
- key: status
label: 상태
dropdown:
- pending: 대기
- active: 활성
- inactive: 비활성
created_at:
label: 가입일
format: datetime
style:
color: "#6b7280"
fontSize: "0.875rem"
roles:
label: 역할
formatFn: splitComma
modals:
- path: edit-status
header: false
mode: side
width: 500px
style:
padding: "24px"
backgroundColor: "#ffffff"
borderRadius: "12px"
boxShadow: "0 10px 25px rgba(0,0,0,0.1)"
blocks:
- name: 사용자 상태 변경
type: query
resource: mysql.qa
sqlType: update
sql: >
UPDATE users SET status = :status WHERE id = :user_id;
display: form
formOptions:
firstLabelWidth: 120px
style:
padding: "16px"
backgroundColor: "#f8fafc"
borderRadius: "8px"
params:
- key: user_id
label: 사용자ID
valueFromRow: id
hidden: false
disabled: true
- key: status
label: 상태
dropdown:
- pending: 대기
- active: 활성
- inactive: 비활성
결제 및 구독 관리
menus:
- path: pages/5o6GOc-payment
name: 결제 및 구독 관리
group: 5o6GOc
icon: mdi-credit-card-multiple
pages:
- path: pages/5o6GOc-payment
blocks:
- type: query
name: 전체 구독 내역
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT s.id AS subscription_id, u.name, u.email, p.name AS plan_name, s.status, s.start_date, s.next_billing_date
FROM subscriptions s
JOIN users u ON s.user_id = u.id
JOIN plans p ON s.plan_id = p.id
ORDER BY s.id DESC;
paginationOptions:
enabled: true
perPage: 10
columns:
subscription_id:
label: 구독ID
updateParams:
subscription_id: "{{subscription_id}}"
name:
label: 사용자명
email:
label: 이메일
format: email
plan_name:
label: 플랜명
status:
label: 상태
valueAs:
active: 활성
inactive: 비활성
canceled: 취소됨
trialing: 체험중
color:
active: green
inactive: red
canceled: gray
trialing: yellow
start_date:
label: 시작일
formatFn: date
next_billing_date:
label: 다음 결제일
formatFn: date
tabOptions:
autoload: 1
tabs:
- name: 인보이스
blocks:
- type: query
name: 인보이스 내역 조회
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT id AS invoice_id, subscription_id, amount, status, due_date, paid_at
FROM invoices
WHERE subscription_id = :subscription_id
ORDER BY id DESC;
params:
- key: subscription_id
label: 구독ID
columns:
invoice_id:
label: 인보이스ID
updateParams:
invoice_id: "{{invoice_id}}"
subscription_id:
label: 구독ID
amount:
label: 금액
formatFn: |
return '₩' + Number(value).toLocaleString()
status:
label: 상태
valueAs:
pending: 대기
paid: 결제완료
failed: 결제실패
unpaid: 미결제
color:
pending: yellow
paid: green
failed: red
unpaid: yellow
due_date:
label: 만료일
formatFn: date
paid_at:
label: 결제일
format: datetime
tabOptions:
autoload: 1
tabs:
- name: 트랜잭션
blocks:
- type: query
name: 트랜잭션 로그 조회
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT invoice_id, transaction_id, payment_gateway, amount, status, error_message, created_at
FROM transactions
WHERE invoice_id = :invoice_id
params:
- key: invoice_id
label: 인보이스ID
columns:
invoice_id:
label: 인보이스ID
transaction_id:
label: 트랜잭션ID
payment_gateway:
label: 결제 게이트웨이
amount:
label: 금액
format: number
formatFn: |
return '₩' + Number(value).toLocaleString()
status:
label: 상태
valueAs:
pending: 대기
completed: 완료
failed: 실패
color:
pending: yellow
completed: green
failed: red
error_message:
label: 오류 메시지
created_at:
label: 생성일
format: datetime
고객지원
menus:
- path: pages/5o6GOc-support
name: 고객지원
group: 5o6GOc
icon: mdi-headset
pages:
- path: pages/5o6GOc-support
blocks:
- type: left
style:
width: calc(20% - 1rem)
border: "1px solid #e5e7eb"
borderRadius: "8px"
padding: 16px 0 0 0
backgroundColor: "#f9fafb"
boxShadow: "0 1px 3px rgba(0,0,0,0.1)"
blocks:
- name: 고객 지원 티켓 목록
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT st.id AS ticket_id, st.subject, u.id AS user_id, u.name AS user_name, st.status, st.priority, st.created_at
FROM support_tickets st
JOIN users u ON st.user_id = u.id
WHERE (LENGTH(:user_id) = 0 OR u.id = :user_id)
ORDER BY st.id DESC;
params:
- key: user_id
label: 사용자ID
columns:
' ':
prepend: true
buttons:
- label: 보기
openModal: ticket-:ticket_id
ticket_id:
label: 티켓ID
updateParams:
ticket_id: "{{ticket_id}}"
subject:
label: 제목
user_id:
label: 사용자ID
user_name:
label: 사용자명
status:
label: 상태
valueAs:
open: 열림
in_progress: 진행중
closed: 종료
color:
open: red
in_progress: green
closed: gray
priority:
label: 우선순위
valueAs:
low: 낮음
medium: 보통
high: 높음
color:
low: green
medium: yellow
high: red
created_at:
label: 생성일
format: datetime
modals:
- path: ticket-:ticket_id
mode: bottom
width: 800px
height: 300px
header: false
dismissible: true
style:
padding: "24px"
backgroundColor: "#ffffff"
borderRadius: "12px"
blocks:
- name: 티켓 상태 변경
type: query
resource: mysql.qa
sqlType: update
sql: >
UPDATE support_tickets SET status = :status WHERE id = :ticket_id;
params:
- key: ticket_id
label: 티켓ID
valueFromRow: ticket_id
hidden: false
disabled: true
- key: status
label: 상태
dropdown:
- open: 열림
- in_progress: 진행중
- closed: 종료
- type: center
style:
width: calc(70% - 1rem)
border: "1px solid #e5e7eb"
borderRadius: "8px"
padding: "20px"
backgroundColor: "#ffffff"
boxShadow: "0 1px 3px rgba(0,0,0,0.1)"
marginLeft: "16px"
blocks:
- type: top
blocks:
- name: 티켓 상세 내용
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT st.id AS ticket_id, st.subject, st.description, st.status, st.priority, st.created_at, u.name AS user_name
FROM support_tickets st
JOIN users u ON st.user_id = u.id
WHERE st.id = :ticket_id;
params:
- key: ticket_id
label: 티켓ID
columns:
ticket_id:
label: 티켓ID
updateParams:
ticket_id: "{{ticket_id}}"
subject:
label: 제목
description:
label: 상세내용
status:
label: 상태
valueAs:
open: 열림
in_progress: 진행중
closed: 종료
color:
open: red
in_progress: yellow
closed: green
dropdown:
- open: 열림
- in_progress: 진행중
- closed: 종료
updateOptions:
type: query
resource: mysql.qa
sqlType: update
sql: >
UPDATE support_tickets SET status = :value WHERE id = :ticket_id;
params:
- key: ticket_id
valueFromRow: ticket_id
priority:
label: 우선순위
valueAs:
low: 낮음
medium: 보통
high: 높음
color:
low: green
medium: yellow
high: red
created_at:
label: 생성일
format: datetime
user_name:
label: 사용자명
tabOptions:
autoload: 1
tabs:
- name: 답변
blocks:
- name: 해당 티켓의 답변
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT tr.id, tr.message, tr.created_at, u.name AS author_name, IF(tr.admin_user_id IS NOT NULL, 'Admin', 'User') AS author_type
FROM ticket_replies tr
LEFT JOIN users u ON u.id = IFNULL(tr.user_id, tr.admin_user_id)
WHERE tr.ticket_id = :ticket_id
ORDER BY tr.id ASC;
params:
- key: ticket_id
label: 티켓ID
valueFromRow: ticket_id
columns:
id:
label: 답변ID
message:
label: 답변내용
created_at:
label: 작성일
format: datetime
author_name:
label: 작성자
author_type:
label: 작성자 유형
valueAs:
Admin: 관리자
User: 사용자
color:
Admin: blue
User: green
- name: 티켓에 관리자 답변 추가
type: query
resource: mysql.qa
sqlType: insert
sql: >
INSERT INTO ticket_replies (ticket_id, admin_user_id, message)
VALUES (:ticket_id, :admin_id, :reply_message);
sqlConfirm: true
params:
- key: ticket_id
label: 티켓ID
valueFromRow: id
hidden: false
disabled: true
- key: admin_id
valueFromUserProperty: "{{id}}"
- key: reply_message
label: 답변 내용
format: tiptap
- type: bottom
blocks:
- name: 공지사항/FAQ
type: toggle
class: text-lg p-2 shadow rounded text-green-700
toggledClass: font-medium text-green-700 bg-green-600/10
blocks:
- name: 공지사항
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT id, title, content, published_at
FROM announcements
ORDER BY id DESC
LIMIT 5
columns:
id:
label: 공지ID
title:
label: 제목
content:
label: 내용
format: text
width: 300px
published_at:
label: 발행일
formatFn: date
actions:
- label: 공지사항 추가
openModal: new-notice
placement: right bottom
single: true
modals:
- path: new-notice
mode: bottom
height: 400px
header: false
dismissible: false
style:
padding: "24px"
backgroundColor: "#ffffff"
borderRadius: "12px 12px 0 0"
blocks:
- name: 공지사항 추가
type: query
resource: mysql.qa
sqlType: insert
sql: >
INSERT INTO announcements (title, content, target_audience, published_at)
VALUES (:title, :content, 'all', NOW());
params:
- key: title
label: 제목
- key: content
label: 내용
format: tiptap
display: form
formOptions:
firstLabelWidth: 140px
- name: FAQ 검색
type: query
resource: mysql.qa
sqlType: select
showDownload: false
sql: >
SELECT id, category, question, answer, created_at
FROM faqs
WHERE
(COALESCE(:category, '') = '' OR category = :category)
AND (
COALESCE(:keyword, '') = ''
OR question LIKE CONCAT('%', :keyword, '%')
OR answer LIKE CONCAT('%', :keyword, '%')
);
paginationOptions:
enabled: true
perPage: 5
params:
- key: keyword
label: 검색어
- key: category
label: 카테고리
datalistFromQuery:
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT DISTINCT category
FROM faqs
columns:
id:
label: FAQ ID
category:
label: 카테고리
question:
label: 질문
answer:
label: 답변
format: text
width: 400px
created_at:
label: 생성일
formatFn: date
actions:
- label: FAQ 추가
openModal: new-faq
placement: right bottom
single: true
modals:
- path: new-faq
mode: bottom
height: 500px
style:
padding: "24px"
backgroundColor: "#ffffff"
borderRadius: "12px 12px 0 0"
blocks:
- name: FAQ 추가
type: query
resource: mysql.qa
sqlType: insert
sql: >
INSERT INTO faqs (category, question, answer, created_at)
VALUES (:category, :question, :answer, NOW());
params:
- key: category
label: 카테고리
datalistFromQuery:
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT DISTINCT category
FROM faqs
- key: question
label: 질문
format: textarea
- key: answer
label: 답변
format: textarea
display: form
formOptions:
firstLabelWidth: 140px
해당 템플릿에 대해 궁금한점이 있으시다면 문의주세요.
아울러 해결하고 싶은 문제나 니즈가 있다면 편하게 말씀해주시기 바랍니다.
감사합니다.