모두의교육그룹 CRM
테이블 (12)
crm_activity
- activity_id — INT
- related_type — ENUM('LEAD', 'ACCOUNT', 'OPP') — NOT NULL — 지급액
- related_id — INT — NOT NULL — 상태(산정/확정/지급)
- activity_type — VARCHAR(20) — NOT NULL
- content — TEXT
- activity_date — DATETIME
- user_id — VARCHAR(30) — NOT NULL
crm_code_detail
- group_code — VARCHAR(20) — 기본 키 — NOT NULL — 코드명
- code_id — VARCHAR(20) — 기본 키 — NOT NULL — 그룹코드 (FK)
- code_name — VARCHAR(50) — NOT NULL — 정렬순서
- sort_order — INT — 상위메뉴 ID
crm_commission_history
- comm_id — INT
- user_id — VARCHAR(30) — NOT NULL
- target_month — CHAR(7) — NOT NULL
- 2) — NOT — NOT NULL
crm_contact
- contact_id — INT
- account_id — INT — NOT NULL
- contact_name — VARCHAR(50) — NOT NULL
- department — VARCHAR(50)
- position — VARCHAR(50)
- mobile — VARCHAR(20)
- email — VARCHAR(100)
- is_keyman — CHAR(1) — 대상 ID
- lms_user_id — VARCHAR(30) — 활동유형(CALL, MEETING)
crm_daily_report
- report_id — INT
- user_id — VARCHAR(30) — NOT NULL
- report_date — DATE — NOT NULL
- today_work — TEXT
- next_plan — TEXT
- issue — TEXT
- approval_status — VARCHAR(20)
- approver_id — VARCHAR(30)
crm_lead_assign_log
- log_id — INT
- lead_id — INT — NOT NULL
- from_user_id — VARCHAR(30)
- to_user_id — VARCHAR(30) — NOT NULL
- assign_date — DATETIME
crm_msg_log
- log_id — INT
- send_user_id — VARCHAR(30) — NOT NULL
- recipient_phone — VARCHAR(20) — NOT NULL
- msg_type — ENUM('SMS','LMS','KAKAO') — NOT NULL
- content — TEXT — NOT NULL
- send_result — CHAR(1)
- send_date — DATETIME
crm_partner
- partner_id — INT
- partner_name — VARCHAR(50) — NOT NULL
- manager_name — VARCHAR(50)
- commission_rate — DECIMAL(5,2)
crm_role
- role_id — INT
- role_name — VARCHAR(50) — NOT NULL — 직속 상사 ID (무한 계층 구조 핵심)
- description — VARCHAR(200)
crm_role_permission
- role_id — INT — 기본 키 — NOT NULL
- menu_id — VARCHAR(20) — 기본 키 — NOT NULL
- can_read — CHAR(1)
- can_write — CHAR(1)
- data_scope — ENUM('OWN', 'TEAM', 'ALL') — 영업담당자
crm_user
- user_id — VARCHAR(30) — NOT NULL — 직급
- user_pw — VARCHAR(255) — NOT NULL — LMS nynMember.seq 연결용
- user_name — VARCHAR(50) — NOT NULL
- manager_id — VARCHAR(30) — 데이터 조회 범위(본인/하위조직/전체)
- dept_name — VARCHAR(50) — 유입경로
- position — VARCHAR(20) — 상태 (접수/배정/진행/종료)
- mobile — VARCHAR(20)
- email — VARCHAR(100)
- is_active — CHAR(1)
- lms_mapping_key — INT — 배정 담당자
crm_user_role
- user_id — VARCHAR(30) — 기본 키 — NOT NULL
- role_id — INT — 기본 키 — NOT NULL