모두의교육그룹 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