数据结构

12123

表总览

通用表

• _user
• _user_session
• _role
• _page
• _resource
• _group
• _user_group_role
• _user_group_role_page
• _user_group_role_resource
• _constant
• _constant_ui
• _record
• _record_history
• _cache
• _file
• _view01_user

业务表

_ui

• ui 施工方案

CREATE TABLE `_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  `uiActionType` varchar(255)  DEFAULT NULL COMMENT 'ui 动作类型，如：fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  `uiActionConfig` text  COMMENT 'ui 动作数据',
  `appDataSchema` text  COMMENT 'ui 校验数据',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'ui 施工方案'

access_control_album_or_article

• 学生表的 accessControl 表

CREATE TABLE `access_control_album_or_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255)  DEFAULT NULL COMMENT '主键id',
  `username` varchar(255)  DEFAULT NULL COMMENT '用户名(登陆)',
  `resourceData` text  COMMENT '明文密码',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `username_index` (`username`) USING BTREE,
  UNIQUE KEY `userId_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '学生表的 accessControl 表'

access_control_course

• 学生表的 accessControl 表

CREATE TABLE `access_control_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255)  DEFAULT NULL COMMENT '主键id',
  `username` varchar(255)  DEFAULT NULL COMMENT '用户名(登陆)',
  `resourceData` text  COMMENT '明文密码',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `username_index` (`username`) USING BTREE,
  UNIQUE KEY `userId_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '学生表的 accessControl 表'

article

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `articleId` bigint(20) DEFAULT NULL COMMENT '文章id, 10000 ++',
  `categoryId` varchar(255) DEFAULT NULL,
  `categoryName` varchar(255) DEFAULT NULL,
  `articleCategoryId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '分类id',
  `courseId` varchar(255) DEFAULT NULL COMMENT '课程id',
  `courseBatchId` varchar(255) DEFAULT NULL,
  `articleGroupName` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '文章所属分组名',
  `articleTagList` text CHARACTER SET utf8mb4  COMMENT '标签; 用, 拼接',
  `articlePublishStatus` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '文章类型(状态)：public, login, draft, deleted',
  `articlePublishTime` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '文章发布时间',
  `articleTitle` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '标题',
  `articleShortName` varchar(255) DEFAULT NULL COMMENT '标题简称',
  `articleConfig` text COMMENT '课件配置：是否考试、时长等',
  `articleCoverImage` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '封面',
  `articleContent` longtext CHARACTER SET utf8mb4  COMMENT '编辑的内容',
  `articleContentForSeo` longtext CHARACTER SET utf8mb4  COMMENT 'HTML 用于渲染',
  `articleAssignmentPublishStatus` varchar(255) DEFAULT NULL COMMENT '作业发布状态',
  `articleAssignment` text COMMENT '文章作业 [{ }]',
  `articleAssignmentWithAnswer` text COMMENT '文章作业答案 [{ }]',
  `articleAudioUrl` varchar(1023) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '默认音频URL唯一的',
  `articleVideoUrl` varchar(1023) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '默认视频URL',
  `articleCreateTime` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '创建时间',
  `articleCreateUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '创建者用户ID',
  `articleCreateUsername` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '创建者用户名',
  `articleUpdateTime` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '更新时间',
  `articleUpdateUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '更新者用户ID',
  `articleUpdateUsername` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '更新者用户名',
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '操作者userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '操作者用户名',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `article_unique` (`articleId`) USING BTREE,
  KEY `categoryId_index` (`articleCategoryId`) USING BTREE,
  KEY `articlePublishTime` (`articlePublishTime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4760 DEFAULT CHARSET = utf8

article_history

CREATE TABLE `article_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `articleId` bigint(20) DEFAULT NULL COMMENT '文章id, 10000 ++',
  `categoryId` varchar(255) DEFAULT NULL COMMENT '分类ID',
  `categoryName` varchar(255) DEFAULT NULL COMMENT '分类名称',
  `articleCategoryId` varchar(255) DEFAULT NULL,
  `courseId` varchar(255) DEFAULT NULL COMMENT '课程id',
  `courseBatchId` varchar(255) DEFAULT NULL COMMENT '课程id',
  `articleGroupName` varchar(255) DEFAULT NULL COMMENT '文章所属分组名',
  `articleTagList` varchar(255) DEFAULT NULL COMMENT '标签; 用, 拼接',
  `articlePublishStatus` varchar(255) DEFAULT 'login' COMMENT '文章类型(状态)：public, login, draft, deleted',
  `articlePublishTime` varchar(255) DEFAULT NULL COMMENT '文章发布时间',
  `articleApproveStatus` varchar(255) DEFAULT NULL,
  `articleTitle` varchar(255) DEFAULT NULL COMMENT '标题',
  `articleShortName` varchar(255) DEFAULT NULL COMMENT '标题简称',
  `articleConfig` text,
  `articleCoverImage` varchar(255) DEFAULT NULL COMMENT '封面',
  `articleContent` longtext COMMENT '编辑的内容',
  `articleContentForSeo` longtext COMMENT 'HTML 用于渲染',
  `articleAssignment` text COMMENT '文章作业 [{ }]',
  `articleAssignmentWithAnswer` text COMMENT '文章作业答案 [{ }]',
  `articleAudioUrl` varchar(255) DEFAULT NULL COMMENT '默认音频URL唯一的',
  `articleVideoUrl` varchar(255) DEFAULT NULL COMMENT '默认视频URL',
  `articleCreateTime` varchar(255) DEFAULT NULL COMMENT '创建时间',
  `articleCreateUserId` varchar(255) DEFAULT NULL COMMENT '创建者用户ID',
  `articleCreateUsername` varchar(255) DEFAULT NULL COMMENT '创建者用户名',
  `articleUpdateTime` varchar(255) DEFAULT NULL COMMENT '更新时间',
  `articleUpdateUserId` varchar(255) DEFAULT NULL COMMENT '更新者用户ID',
  `articleUpdateUsername` varchar(255) DEFAULT NULL COMMENT '更新者用户名',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  `articleAssignmentPublishStatus` varchar(255) DEFAULT NULL COMMENT '文章作业发布状态; publish;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 DEFAULT CHARSET = utf8mb4

assignment

CREATE TABLE `assignment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignmentId` varchar(150) DEFAULT NULL COMMENT '学生作业ID; articleId_userId_123456',
  `albumId` varchar(30) DEFAULT NULL COMMENT '课程ID',
  `classId` varchar(50) DEFAULT NULL COMMENT '班级ID',
  `courseId` varchar(255) DEFAULT NULL,
  `courseBatchId` varchar(255) DEFAULT NULL,
  `articleId` varchar(30) DEFAULT NULL COMMENT '文章ID',
  `userId` varchar(50) DEFAULT NULL COMMENT '用户ID',
  `articleTitle` varchar(255) DEFAULT NULL COMMENT '文章标题',
  `assignmentRetryNumber` int(11) DEFAULT NULL COMMENT '作业重做次数',
  `assignmentScore` int(11) DEFAULT NULL COMMENT '作业分数',
  `assignmentFullMarks` int(11) DEFAULT NULL COMMENT '作业总分',
  `assignmentSubmitStatus` varchar(255) DEFAULT 'save' COMMENT '作业提交状态; publish',
  `assignmentSubmitAt` varchar(255) DEFAULT NULL COMMENT '作业提交时间',
  `assignmentUserAnswer` text COMMENT '用户作业内容',
  `assignmentStandardAnswer` text COMMENT '作业标准答案',
  `assignmentAnswerHistory` longtext COMMENT '作业历史记录',
  `assignmentReview` text,
  `assignmentReviewUserId` varchar(255) DEFAULT NULL COMMENT '批改人UserId',
  `assignmentReviewUser` varchar(255) DEFAULT NULL,
  `assignmentReviewStatus` varchar(255) DEFAULT '' COMMENT '批改状态; publish',
  `assignmentReviewAt` varchar(255) DEFAULT NULL COMMENT '批改时间',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者用户ID; userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者;',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_assignmentId` (`assignmentId`) USING BTREE,
  KEY `group` (`courseBatchId`, `articleId`, `userId`) USING BTREE,
  KEY `id` (`id`) USING BTREE,
  KEY `albumId` (`albumId`) USING BTREE,
  KEY `courseBatchId` (`courseBatchId`) USING BTREE,
  KEY `classId` (`classId`) USING BTREE,
  KEY `articleId` (`articleId`) USING BTREE,
  KEY `userId` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = utf8mb4

attendance

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classId` varchar(255)  DEFAULT NULL,
  `courseId` varchar(255)  DEFAULT NULL,
  `courseBatchId` varchar(255)  DEFAULT NULL,
  `userId` varchar(255)  DEFAULT NULL COMMENT '学生用户id;',
  `lessonNumber` int(11) DEFAULT NULL COMMENT '第几课;',
  `attendanceDate` varchar(255)  DEFAULT NULL COMMENT '出勤时间;',
  `attendance` varchar(255)  DEFAULT NULL COMMENT '出勤类型;出勤,缺勤,迟到,早退',
  `articleId` varchar(255)  DEFAULT NULL COMMENT '文章ID',
  `articleTitle` varchar(255)  DEFAULT NULL COMMENT '文章题目',
  `markedAt` varchar(255)  DEFAULT NULL COMMENT '创建时间',
  `markedByUser` varchar(255)  DEFAULT NULL COMMENT '创建人',
  `markedByUserId` varchar(255)  DEFAULT NULL COMMENT '创建人Id;',
  `remarks` varchar(255)  DEFAULT NULL COMMENT '备注；缺勤情况说明',
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `id` (`id`) USING BTREE,
  KEY `classId` (`classId`) USING BTREE,
  KEY `courseBatchId` (`courseBatchId`) USING BTREE,
  KEY `studentId` (`userId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

behavior

CREATE TABLE `behavior` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255)  DEFAULT NULL,
  `createTime` varchar(255)  DEFAULT NULL,
  `courseBatchId` varchar(255)  DEFAULT NULL,
  `albumId` varchar(255)  DEFAULT NULL,
  `classId` varchar(255)  DEFAULT NULL,
  `articleId` varchar(255)  DEFAULT NULL,
  `userActionType` varchar(255)  DEFAULT NULL,
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT 'insert' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `user_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

bot_report

CREATE TABLE `bot_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reportId` varchar(255)  DEFAULT NULL,
  `reportName` varchar(255)  DEFAULT NULL,
  `reportType` varchar(255)  DEFAULT NULL,
  `reportContent` mediumtext ,
  `reportReceipient` varchar(255)  DEFAULT NULL,
  `reportTime` varchar(255)  DEFAULT NULL,
  `reportStatus` varchar(255)  DEFAULT NULL,
  `reportRawData` mediumtext ,
  `processBatchId` varchar(255)  DEFAULT NULL,
  `operation` varchar(255)  DEFAULT NULL,
  `operationAt` varchar(255)  DEFAULT NULL,
  `operationByUserId` varchar(255)  DEFAULT NULL,
  `operationByUser` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2977 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

category

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoryId` bigint(255) DEFAULT NULL COMMENT '分类id',
  `categoryIntro` text CHARACTER SET utf8mb4 ,
  `categoryName` varchar(255) CHARACTER SET utf8mb4  DEFAULT '',
  `prefix` varchar(255) DEFAULT NULL,
  `sortNumber` varchar(255) DEFAULT NULL,
  `categoryGroup` varchar(255) DEFAULT NULL,
  `categoryGroupSort` varchar(255) DEFAULT NULL,
  `categoryMark` varchar(255) DEFAULT NULL COMMENT '备注',
  `categoryPublishStatus` varchar(255) DEFAULT '',
  `categoryCreateTime` varchar(255) DEFAULT '',
  `categoryCreateUserId` varchar(255) DEFAULT '',
  `categoryCreateUsername` varchar(255) DEFAULT '',
  `categoryUpdateTime` varchar(255) DEFAULT '',
  `categoryUpdateUserId` varchar(255) DEFAULT '',
  `categoryUpdateUsername` varchar(255) DEFAULT '',
  `operation` varchar(255) DEFAULT '',
  `operationByUserId` varchar(255) DEFAULT '',
  `operationByUser` varchar(255) DEFAULT '',
  `operationAt` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `categoryId_unique` (`categoryId`) USING BTREE,
  KEY `categoryName_unique` (`categoryName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 176 DEFAULT CHARSET = utf8mb4

cgg_course

• 课程

CREATE TABLE `cgg_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `courseId` varchar(255)  DEFAULT NULL COMMENT '所属课程系列',
  `courseBatch` varchar(255)  DEFAULT NULL COMMENT '课程期号',
  `courseBatchId` varchar(255)  DEFAULT NULL COMMENT '课程本期ID',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称',
  `courseOrProject` varchar(255)  DEFAULT NULL COMMENT '课程类型',
  `studentOrTeacher` varchar(255)  DEFAULT NULL COMMENT '学员类型',
  `applicationType` varchar(255)  DEFAULT NULL COMMENT '报名类型',
  `workload` text  COMMENT '每周工作量',
  `prerequisites` text  COMMENT '预备课程',
  `nextCourseBatchId` varchar(255)  DEFAULT NULL COMMENT '下一课程',
  `courseDescription` longtext  COMMENT '课程简介',
  `courseLeader` varchar(255)  DEFAULT NULL COMMENT '负责人',
  `courseUrl` varchar(255)  DEFAULT NULL COMMENT '课程主页',
  `teacherCourseBatchId` varchar(255)  DEFAULT NULL COMMENT '班长课程ID',
  `managerCourseBatchId` varchar(255)  DEFAULT NULL COMMENT '课程负责人课程ID',
  `startDate` varchar(255)  DEFAULT NULL COMMENT '开始日期',
  `endDate` varchar(255)  DEFAULT NULL COMMENT '结束日期',
  `totalClassHour` varchar(255)  DEFAULT NULL COMMENT '总课时',
  `courseCredit` varchar(255)  DEFAULT NULL COMMENT '课程学分',
  `courseStatus` varchar(255)  DEFAULT NULL COMMENT '课程状态',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名(展示)',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `course_batch_id` (`courseBatchId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 143 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '课程'

cgg_course_series

• 课程系列

CREATE TABLE `cgg_course_series` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `courseId` varchar(255)  DEFAULT NULL COMMENT '所属课程系列',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称',
  `courseLeader` varchar(255)  DEFAULT NULL COMMENT '负责人',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名(展示)',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `courseId` (`courseId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 134 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '课程系列'

cgg_member_v3

CREATE TABLE `cgg_member_v3` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `memberId` varchar(255)  DEFAULT NULL COMMENT '成员编号',
  `midNumber` int(20) DEFAULT NULL COMMENT '成员编号数字',
  `loginId` varchar(255)  DEFAULT NULL COMMENT '用户账号',
  `sortId` varchar(255)  DEFAULT NULL COMMENT '排序顺序',
  `netName` varchar(255)  DEFAULT NULL COMMENT '名字',
  `district` varchar(255)  DEFAULT NULL COMMENT '组织分区',
  `orgId` varchar(255)  DEFAULT NULL COMMENT '组织编号',
  `orgName` varchar(255)  DEFAULT NULL COMMENT '组织名称',
  `_orgName` varchar(255)  DEFAULT NULL COMMENT '旧组织名称',
  `orgPath` varchar(255)  DEFAULT NULL COMMENT '组织路径',
  `roleId` varchar(255)  DEFAULT NULL COMMENT '组织角色',
  `joinState` varchar(255)  DEFAULT NULL COMMENT '在校状态',
  `joinAt` varchar(255)  DEFAULT NULL COMMENT '加入时间',
  `stage` varchar(255)  DEFAULT NULL COMMENT '成长阶段',
  `position` varchar(255)  DEFAULT NULL COMMENT '成长层次',
  `target` longtext  COMMENT '成长目标',
  `note` longtext  COMMENT '备注',
  `followUpBy` varchar(255)  DEFAULT NULL COMMENT '跟进者',
  `pendingAction` varchar(255)  DEFAULT NULL COMMENT '跟进状态',
  `referer` varchar(255)  DEFAULT NULL COMMENT '访客来源',
  `_referer` varchar(255)  DEFAULT NULL COMMENT '旧访客来源',
  `wechatId` varchar(255)  DEFAULT NULL COMMENT '微信号',
  `qqNumber` varchar(255)  DEFAULT NULL COMMENT 'QQ',
  `alipayId` varchar(255)  DEFAULT NULL COMMENT '支付宝',
  `lineId` varchar(255)  DEFAULT NULL COMMENT 'LINE',
  `whatsappId` varchar(255)  DEFAULT NULL COMMENT 'WhatsApp',
  `country` varchar(255)  DEFAULT NULL COMMENT '所在地区',
  `countryCode` varchar(255)  DEFAULT NULL COMMENT '国家代码',
  `phoneNumber` varchar(255)  DEFAULT NULL COMMENT '电话号码',
  `email` varchar(255)  DEFAULT NULL COMMENT '邮箱',
  `address` varchar(255)  DEFAULT NULL COMMENT '地址',
  `idName` varchar(255)  DEFAULT NULL COMMENT '姓名',
  `isChild` varchar(255)  DEFAULT NULL COMMENT '是否未成年',
  `guardianId` varchar(255)  DEFAULT NULL COMMENT '监护人ID',
  `guardianName` varchar(255)  DEFAULT NULL COMMENT '监护人名字',
  `leaveAt` varchar(255)  DEFAULT NULL COMMENT '离校时间',
  `leaveNote` longtext  COMMENT '离校备注',
  `createAt` varchar(255)  DEFAULT NULL COMMENT '创建时间',
  `updateAt` varchar(255)  DEFAULT NULL COMMENT '更新时间',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者用户名(展示)',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1002 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

class_member

CREATE TABLE `class_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(255)  DEFAULT NULL COMMENT '所属事工系列',
  `courseMemberId` varchar(255)  DEFAULT NULL COMMENT '学习记录编号',
  `courseBatchId` varchar(255)  DEFAULT NULL COMMENT '课程本期ID',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称',
  `classId` varchar(255)  DEFAULT NULL COMMENT '班级ID',
  `className` varchar(255)  DEFAULT NULL COMMENT '班级名称',
  `memberId` varchar(255)  DEFAULT NULL COMMENT '学员会友编号',
  `netName` varchar(255)  DEFAULT NULL COMMENT '学员名称',
  `loginId` varchar(255)  DEFAULT NULL COMMENT '学员用户ID',
  `district` varchar(255)  DEFAULT NULL COMMENT '学员牧区',
  `classMemberRole` varchar(255)  DEFAULT NULL COMMENT '班级学员角色',
  `classMemberStatus` varchar(255)  DEFAULT NULL COMMENT '班级学员状态',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者ID',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `classId_memberId` (`classId`, `memberId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5870 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

course_article

• 班级课程表; 软删除未启用;

CREATE TABLE `course_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(255) DEFAULT NULL COMMENT '课程ID',
  `courseBatchId` varchar(255) DEFAULT NULL COMMENT '课程本期ID',
  `courseName` varchar(255) DEFAULT NULL COMMENT '课程题目',
  `albumId` varchar(255) DEFAULT NULL COMMENT '教材ID',
  `albumTitle` varchar(255) DEFAULT NULL COMMENT '教材题目',
  `articleId` bigint(20) DEFAULT NULL COMMENT '文章Id',
  `articleTitle` varchar(255) DEFAULT NULL COMMENT '文章标题',
  `articleShortName` varchar(255) DEFAULT NULL,
  `categoryName` varchar(255) DEFAULT NULL,
  `lessonNumber` int(11) DEFAULT NULL COMMENT '第几课',
  `publishTime` varchar(255) DEFAULT NULL COMMENT '文章发布时间',
  `passcode` varchar(255) DEFAULT NULL COMMENT '课程密码',
  `isExam` varchar(255) DEFAULT NULL COMMENT '是否是考试',
  `examDuration` varchar(255) DEFAULT NULL COMMENT '考试限制时间',
  `examExpiryTime` varchar(255) DEFAULT NULL COMMENT '考试到期时间',
  `examMaxRetryNumber` varchar(255) DEFAULT NULL COMMENT '允许重考次数',
  `examPassingScore` varchar(255) DEFAULT NULL COMMENT '合格分数线',
  `isShowAnswer` varchar(255) DEFAULT NULL COMMENT '考完是否自动显示答案',
  `displayStatus` varchar(255) DEFAULT NULL COMMENT '显示状态：显示, 隐藏',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `groupId_index` (`articleId`) USING BTREE,
  KEY `albumId_index` (`albumId`) USING BTREE,
  KEY `group_albumId_unique` (`articleId`, `albumId`) USING BTREE,
  KEY `courseBatchId` (`courseBatchId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1095 DEFAULT CHARSET = utf8mb4 COMMENT = '班级课程表; 软删除未启用;'

course_class

CREATE TABLE `course_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(255)  DEFAULT NULL COMMENT '所属课程系列',
  `courseBatchId` varchar(255)  DEFAULT NULL COMMENT '课程本期ID',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称',
  `courseBatch` varchar(255)  DEFAULT NULL COMMENT '课程期号',
  `classId` varchar(255)  DEFAULT NULL COMMENT '班级ID',
  `className` varchar(255)  DEFAULT NULL COMMENT '班级名称',
  `classNumber` varchar(255)  DEFAULT NULL COMMENT '班号',
  `classLeaderMemberId` varchar(255)  DEFAULT NULL COMMENT '班长会友编号',
  `classLeaderUserId` varchar(255)  DEFAULT NULL COMMENT '班长用户ID',
  `classLeaderName` varchar(255)  DEFAULT NULL COMMENT '班长名字',
  `classLeaderDistrict` varchar(255)  DEFAULT NULL COMMENT '班长的牧区',
  `maxNumberOfMember` varchar(255)  DEFAULT NULL COMMENT '班级最大学员数量',
  `isJianghuGroup` varchar(255)  DEFAULT NULL COMMENT '是否创建江湖群',
  `isJianghuAllAdmin` varchar(255)  DEFAULT NULL COMMENT '江湖中是否将全体成员设为为管理员',
  `isDuoxingGroup` varchar(255)  DEFAULT NULL COMMENT '是否创建多星群',
  `isDuoxingAllAdmin` varchar(255)  DEFAULT NULL COMMENT '多星中是否将全体成员设为管理员',
  `isBaofengGroup` varchar(255)  DEFAULT NULL COMMENT '是否创建暴风群',
  `isBaofengAllAdmin` varchar(255)  DEFAULT NULL COMMENT '暴风中是否将全体成员设为管理员',
  `groupCloudDriveStatus` varchar(255)  DEFAULT NULL COMMENT '是否开启云盘;on开启,off关闭',
  `classStatus` varchar(255)  DEFAULT 'active' COMMENT '班级状态',
  `operation` varchar(255)  DEFAULT NULL,
  `operationByUserId` varchar(255)  DEFAULT NULL,
  `operationByUser` varchar(255)  DEFAULT NULL,
  `operationAt` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `classId` (`classId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 415 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

examination

CREATE TABLE `examination` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignmentId` varchar(150) DEFAULT NULL COMMENT '学生作业ID; articleId_userId_123456',
  `albumId` varchar(30) DEFAULT NULL COMMENT '课程ID',
  `classId` varchar(50) DEFAULT NULL COMMENT '班级ID',
  `courseBatchId` varchar(50) DEFAULT NULL,
  `articleId` varchar(30) DEFAULT NULL COMMENT '文章ID',
  `userId` varchar(50) DEFAULT NULL COMMENT '用户ID',
  `examRetryNumber` varchar(30) DEFAULT NULL,
  `assignmentExamSeconds` int(11) DEFAULT NULL COMMENT '剩余答题秒数',
  `assignmentScore` int(11) DEFAULT NULL COMMENT '作业分数',
  `assignmentFullMarks` int(11) DEFAULT NULL,
  `assignmentSubmitStatus` varchar(255) DEFAULT 'save' COMMENT '作业提交状态; publish',
  `assignmentSubmitAt` varchar(255) DEFAULT NULL COMMENT '作业提交时间',
  `assignmentUserAnswer` text COMMENT '用户作业内容',
  `assignmentStandardAnswer` text COMMENT '作业标准答案',
  `assignmentAnswerHistory` longtext,
  `assignmentReview` text,
  `assignmentReviewUserId` varchar(255) DEFAULT NULL COMMENT '批改人UserId',
  `assignmentReviewStatus` varchar(255) DEFAULT '' COMMENT '批改状态; publish',
  `assignmentReviewUser` varchar(255) DEFAULT NULL,
  `assignmentReviewAt` varchar(255) DEFAULT NULL COMMENT '批改时间',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者用户ID; userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者;',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_assignmentId` (`assignmentId`) USING BTREE,
  UNIQUE KEY `uniq` (`articleId`, `courseBatchId`, `userId`) USING BTREE,
  KEY `albumId` (`albumId`) USING BTREE,
  KEY `classId` (`classId`) USING BTREE,
  KEY `courseBatchId` (`courseBatchId`) USING BTREE,
  KEY `articleId` (`articleId`) USING BTREE,
  KEY `userId` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = utf8mb4

note

CREATE TABLE `note` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `noteId` bigint(20) DEFAULT NULL COMMENT '笔记ID',
  `articleId` varchar(50) DEFAULT NULL COMMENT '文章ID',
  `classId` varchar(50) DEFAULT NULL COMMENT '班级Id',
  `courseBatchId` varchar(255) DEFAULT NULL,
  `albumId` varchar(50) DEFAULT NULL COMMENT '课程Id',
  `userId` varchar(50) DEFAULT NULL COMMENT '用户id',
  `noteContent` longtext COMMENT '笔记内容',
  `noteContentForSeo` longtext COMMENT '笔记渲染内容',
  `noteRemark` text COMMENT '学生补充',
  `noteReview` longtext COMMENT '笔记批改',
  `noteRemarkReview` text COMMENT '批改学生补充',
  `noteReviewStatus` varchar(255) DEFAULT NULL COMMENT '批改状态',
  `noteReviewAt` varchar(255) DEFAULT NULL,
  `noteSubmitStatus` varchar(255) DEFAULT 'save' COMMENT '笔记提交状态',
  `noteSubmitAt` varchar(255) DEFAULT NULL COMMENT '笔记提交时间',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group` (`courseBatchId`, `articleId`, `userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8mb4

question

CREATE TABLE `question` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `questionId` varchar(255) DEFAULT NULL COMMENT '题目id, 10000 ++',
  `questionDesc` varchar(255) DEFAULT NULL COMMENT '题目说明',
  `questionType` varchar(255) DEFAULT NULL COMMENT '题目类型',
  `articleId` varchar(255) DEFAULT NULL COMMENT '文章id, 10000 ++',
  `categoryId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '分类id',
  `categoryName` varchar(255) DEFAULT NULL COMMENT '分类名称',
  `courseId` varchar(255) DEFAULT NULL COMMENT '课程id',
  `courseBatchId` varchar(255) DEFAULT NULL,
  `articleGroupName` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '文章所属分组名',
  `articleTagList` text CHARACTER SET utf8mb4  COMMENT '标签; 用, 拼接',
  `articlePublishStatus` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '文章类型(状态)：public, login, draft, deleted',
  `articlePublishTime` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '文章发布时间',
  `articleTitle` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '标题',
  `articleShortName` varchar(255) DEFAULT NULL COMMENT '标题简称',
  `articleConfig` text COMMENT '课件配置：是否考试、时长等',
  `articleAssignmentPublishStatus` varchar(255) DEFAULT NULL COMMENT '作业发布状态',
  `questionTag` varchar(255) DEFAULT NULL COMMENT '题目标签;',
  `questionStem` text COMMENT '题干;',
  `questionContent` text COMMENT '题目body { }',
  `questionContentWithAnswer` text COMMENT '题目答案body { }',
  `articleAudioUrl` varchar(1023) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '默认音频URL唯一的',
  `articleVideoUrl` varchar(1023) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '默认视频URL',
  `articleCreateTime` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '创建时间',
  `articleCreateUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '创建者用户ID',
  `articleCreateUsername` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '创建者用户名',
  `articleUpdateTime` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '更新时间',
  `articleUpdateUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '更新者用户ID',
  `articleUpdateUsername` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '更新者用户名',
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '操作者userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '操作者用户名',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `question_unique` (`questionId`) USING BTREE,
  KEY `articleId_index` (`articleId`) USING BTREE,
  KEY `courseBatchId` (`courseBatchId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 49 DEFAULT CHARSET = utf8

student_complete_course_record

CREATE TABLE `student_complete_course_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `courseMemberId` varchar(255)  DEFAULT NULL COMMENT '学习记录编号',
  `courseBatchId` varchar(255)  DEFAULT NULL COMMENT '课程本期ID',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称',
  `courseId` varchar(255)  DEFAULT NULL COMMENT '课程系列编号',
  `classId` varchar(255)  DEFAULT NULL COMMENT '班级编号',
  `memberId` varchar(255)  DEFAULT NULL COMMENT '员工编号',
  `netName` varchar(255)  DEFAULT NULL COMMENT '名字',
  `loginId` varchar(255)  DEFAULT NULL COMMENT '用户ID',
  `district` varchar(255)  DEFAULT NULL COMMENT '牧区',
  `result` varchar(50)  DEFAULT NULL COMMENT '毕业成绩',
  `teacherRecommendation` varchar(255)  DEFAULT NULL COMMENT '建议：毕业、退课',
  `memberCourseStatus` varchar(255)  DEFAULT NULL COMMENT '课程学习状态',
  `learningRecordDetail` mediumtext  COMMENT '学习记录详情',
  `createByUserId` varchar(255)  DEFAULT NULL COMMENT '提交毕业决定的教师用户ID',
  `createByUser` varchar(255)  DEFAULT NULL COMMENT '提交毕业决定的教师',
  `createAt` varchar(255)  DEFAULT NULL COMMENT '提交毕业决定的创建时间',
  `nextCourseBatchId` varchar(255)  DEFAULT NULL COMMENT '自动升班课程ID',
  `nextCourseClassId` varchar(255)  DEFAULT NULL COMMENT '自动升班班级ID',
  `processStatus` varchar(255)  DEFAULT NULL COMMENT '处理状态：new-新建, processed-已处理, error-错误',
  `processBatchId` varchar(255)  DEFAULT NULL COMMENT '处理BatchId',
  `processResult` varchar(255)  DEFAULT NULL COMMENT '处理结果: 批准，拒绝',
  `processDetails` text  COMMENT '处理详情',
  `processByUserId` varchar(255)  DEFAULT NULL COMMENT '处理人',
  `processByUser` varchar(255)  DEFAULT NULL COMMENT '处理人',
  `deleteErrorRemarks` varchar(255)  DEFAULT NULL COMMENT '删除时出错的备注',
  `syncErrorRemarks` varchar(255)  DEFAULT NULL COMMENT '同步出错的备注',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  `operationByUserId` varchar(50)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(50)  DEFAULT NULL COMMENT '操作者用户名(展示)',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `courseMemberId` (`courseMemberId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

student_learning_record

CREATE TABLE `student_learning_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `courseMemberId` varchar(255)  DEFAULT NULL COMMENT '学习记录编号',
  `courseBatchId` varchar(255)  DEFAULT NULL COMMENT '课程本期ID',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称',
  `courseId` varchar(255)  DEFAULT NULL COMMENT '所属课程系列',
  `classId` varchar(255)  DEFAULT NULL COMMENT '班级编号',
  `memberId` varchar(255)  DEFAULT NULL COMMENT '员工编号',
  `netName` varchar(255)  DEFAULT NULL COMMENT '名字',
  `loginId` varchar(255)  DEFAULT NULL COMMENT '用户ID',
  `district` varchar(255)  DEFAULT NULL COMMENT '分公司ID',
  `orgId` varchar(255)  DEFAULT NULL COMMENT '行政组织ID',
  `orgName` varchar(255)  DEFAULT NULL COMMENT '行政组织名称',
  `orgFullName` varchar(255)  DEFAULT NULL COMMENT '行政组织全称',
  `roleId` varchar(255)  DEFAULT NULL COMMENT '组织角色ID',
  `stage` varchar(50)  DEFAULT NULL COMMENT '职务阶段',
  `title` varchar(50)  DEFAULT NULL COMMENT '职务',
  `position` varchar(50)  DEFAULT NULL COMMENT '职务',
  `memberRole` varchar(50)  DEFAULT NULL COMMENT '学员在课程的职分',
  `result` varchar(50)  DEFAULT NULL COMMENT '毕业成绩',
  `note` text  COMMENT '备注',
  `comment` text  COMMENT '阶段评语',
  `teacherRecommendation` varchar(255)  DEFAULT NULL COMMENT '建议：毕业、不及格',
  `courseCreditEarned` varchar(50)  DEFAULT NULL COMMENT '所得学分',
  `examScoreStat` varchar(255)  DEFAULT NULL COMMENT '平时成绩统计',
  `examScoreDetail` text  COMMENT '每课成绩详情',
  `overallComment` varchar(1023)  DEFAULT NULL COMMENT '整体评语',
  `commentDetail` text  COMMENT '评语详情',
  `attendanceStat` varchar(1023)  DEFAULT NULL COMMENT '出勤统计',
  `attendanceDetail` text  COMMENT '出勤详情',
  `assignmentStat` varchar(1023)  DEFAULT NULL COMMENT '作业统计',
  `assignmentDetail` text  COMMENT '作业详情汇报',
  `noteStat` varchar(1023)  DEFAULT NULL COMMENT '笔记统计',
  `noteDetail` text  COMMENT '笔记详情汇报',
  `totalLearningHour` varchar(255)  DEFAULT NULL COMMENT '总学习时长',
  `memberCourseStatus` varchar(255)  DEFAULT NULL COMMENT '课程学习状态',
  `nominatedByMemberId` varchar(255)  DEFAULT NULL COMMENT '推荐人员工编号',
  `nominatedByUserId` varchar(255)  DEFAULT NULL COMMENT '推荐人用户名',
  `nominatedByUser` varchar(255)  DEFAULT NULL COMMENT '推荐人',
  `dateStarted` varchar(255)  DEFAULT NULL COMMENT '学习开始时间',
  `dateEnded` varchar(255)  DEFAULT NULL COMMENT '学习结束时间',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  `operationByUserId` varchar(50)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(50)  DEFAULT NULL COMMENT '操作者用户名(展示)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

student_video_playtime

• 播放时长记录表

CREATE TABLE `student_video_playtime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playId` varchar(255) NOT NULL COMMENT '播放记录id; 由用户id+课程id+时间戳组成，参考assignment表',
  `albumId` varchar(255) DEFAULT NULL COMMENT '课程ID',
  `classId` varchar(255) DEFAULT NULL COMMENT '班级ID',
  `courseId` varchar(255) DEFAULT NULL,
  `courseBatchId` varchar(255) DEFAULT NULL,
  `articleId` varchar(255) DEFAULT NULL COMMENT '文章ID',
  `userId` varchar(255) DEFAULT NULL COMMENT '用户ID',
  `articleTitle` varchar(255) DEFAULT NULL COMMENT '文章标题',
  `playTime` int(11) DEFAULT NULL COMMENT '播放时长;单位秒',
  `totalTime` int(11) DEFAULT NULL COMMENT '视频总时长;单位秒',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者用户ID; userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者;',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '播放时长记录表'

业务视图

_view02_user_app

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `_view02_user_app` AS
select
  (
    case `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId`
      when 'baofeng-course-mgmt' then 'course_mgmt'
      when 'baofeng-student' then 'student'
      when 'baofeng-sys-admin' then 'sys_admin'
      when 'baofeng-teacher' then 'teacher'
      else NULL
    end
  ) AS `jhId`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`id` AS `id`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`userId` AS `userId`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId` AS `appId`
from
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`
where
  (
    `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId` in (
      'baofeng-course-mgmt',
      'baofeng-student',
      'baofeng-sys-admin',
      'baofeng-teacher'
    )
  )

course

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `course` AS
select
  `cgg_course`.`id` AS `id`,
  `cgg_course`.`courseId` AS `courseId`,
  `cgg_course`.`courseBatch` AS `courseBatch`,
  `cgg_course`.`courseBatchId` AS `courseBatchId`,
  `cgg_course`.`courseName` AS `courseName`,
  `cgg_course`.`courseOrProject` AS `courseOrProject`,
  `cgg_course`.`studentOrTeacher` AS `studentOrTeacher`,
  `cgg_course`.`applicationType` AS `applicationType`,
  `cgg_course`.`workload` AS `workload`,
  `cgg_course`.`prerequisites` AS `prerequisites`,
  `cgg_course`.`nextCourseBatchId` AS `nextCourseBatchId`,
  `cgg_course`.`courseDescription` AS `courseDescription`,
  `cgg_course`.`courseLeader` AS `courseLeader`,
  `cgg_course`.`courseUrl` AS `courseUrl`,
  `cgg_course`.`teacherCourseBatchId` AS `teacherCourseBatchId`,
  `cgg_course`.`managerCourseBatchId` AS `managerCourseBatchId`,
  `cgg_course`.`startDate` AS `startDate`,
  `cgg_course`.`endDate` AS `endDate`,
  `cgg_course`.`totalClassHour` AS `totalClassHour`,
  `cgg_course`.`courseCredit` AS `courseCredit`,
  `cgg_course`.`courseStatus` AS `courseStatus`,
  `cgg_course`.`operation` AS `operation`,
  `cgg_course`.`operationAt` AS `operationAt`,
  `cgg_course`.`operationByUserId` AS `operationByUserId`,
  `cgg_course`.`operationByUser` AS `operationByUser`
from
  `cgg_course`

course_series

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `course_series` AS
select
  `cgg_course_series`.`id` AS `id`,
  `cgg_course_series`.`courseId` AS `courseId`,
  `cgg_course_series`.`courseName` AS `courseName`,
  `cgg_course_series`.`courseLeader` AS `courseLeader`,
  `cgg_course_series`.`operation` AS `operation`,
  `cgg_course_series`.`operationAt` AS `operationAt`,
  `cgg_course_series`.`operationByUserId` AS `operationByUserId`,
  `cgg_course_series`.`operationByUser` AS `operationByUser`
from
  `cgg_course_series`

view01_article

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_article` AS
select
  `article`.`id` AS `id`,
  `article`.`articleId` AS `articleId`,
  `article`.`categoryId` AS `categoryId`,
  `article`.`articleGroupName` AS `articleGroupName`,
  `article`.`articleTagList` AS `articleTagList`,
  `article`.`articlePublishStatus` AS `articlePublishStatus`,
  `article`.`articlePublishTime` AS `articlePublishTime`,
  `article`.`articleTitle` AS `articleTitle`,
  `article`.`articleCoverImage` AS `articleCoverImage`,
  `article`.`articleAudioUrl` AS `articleAudioUrl`,
  `article`.`articleVideoUrl` AS `articleVideoUrl`,
  `article`.`articleCreateTime` AS `articleCreateTime`,
  `article`.`articleCreateUserId` AS `articleCreateUserId`,
  `article`.`articleCreateUsername` AS `articleCreateUsername`,
  `article`.`articleUpdateTime` AS `articleUpdateTime`,
  `article`.`articleUpdateUserId` AS `articleUpdateUserId`,
  `article`.`articleUpdateUsername` AS `articleUpdateUsername`,
  `category`.`categoryIntro` AS `categoryIntro`,
  `category`.`categoryGroup` AS `categoryGroup`,
  `category`.`categoryGroupSort` AS `categoryGroupSort`,
  `category`.`categoryName` AS `categoryName`,
  `category`.`categoryPublishStatus` AS `categoryPublishStatus`,
  `article`.`articleAssignment` AS `articleAssignment`,
  `article`.`articleAssignmentWithAnswer` AS `articleAssignmentWithAnswer`,
  `article`.`articleConfig` AS `articleConfig`,
  `article`.`operation` AS `operation`,
  `article`.`operationByUserId` AS `operationByUserId`,
  `article`.`operationByUser` AS `operationByUser`,
  `article`.`operationAt` AS `operationAt`,
  `article`.`courseId` AS `courseId`,
  `article`.`courseBatchId` AS `courseBatchId`,
  `article`.`articleShortName` AS `articleShortName`,
  `article`.`articleContent` AS `articleContent`,
  `article`.`articleAssignmentPublishStatus` AS `articleAssignmentPublishStatus`
from
  (
    `article`
    left join `category` on (
      (`article`.`categoryId` = `category`.`categoryId`)
    )
  )

view01_article_list

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_article_list` AS
select
  `article`.`id` AS `id`,
  `article`.`articleId` AS `articleId`,
  `article`.`categoryId` AS `categoryId`,
  `article`.`categoryName` AS `categoryName`,
  `article`.`courseId` AS `courseId`,
  `article`.`courseBatchId` AS `courseBatchId`,
  `article`.`articlePublishStatus` AS `articlePublishStatus`,
  `article`.`articlePublishTime` AS `articlePublishTime`,
  `article`.`articleTitle` AS `articleTitle`,
  `article`.`articleShortName` AS `articleShortName`,
  `article`.`articleCreateUserId` AS `createByUserId`,
  `article`.`operation` AS `operation`,
  `article`.`operationByUserId` AS `operationByUserId`,
  `article`.`operationByUser` AS `operationByUser`,
  `article`.`operationAt` AS `operationAt`
from
  `article`

view01_assignment

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_assignment` AS
select
  `assignment`.`id` AS `id`,
  `assignment`.`assignmentId` AS `assignmentId`,
  `assignment`.`albumId` AS `albumId`,
  `assignment`.`classId` AS `classId`,
  `assignment`.`courseBatchId` AS `courseBatchId`,
  `assignment`.`articleId` AS `articleId`,
  `article`.`articleTitle` AS `articleTitle`,
  `assignment`.`userId` AS `userId`,
  `assignment`.`assignmentScore` AS `assignmentScore`,
  `assignment`.`assignmentFullMarks` AS `assignmentFullMarks`,
  `assignment`.`assignmentSubmitStatus` AS `assignmentSubmitStatus`,
  `assignment`.`assignmentSubmitAt` AS `assignmentSubmitAt`,
  `assignment`.`assignmentUserAnswer` AS `assignmentUserAnswer`,
  `assignment`.`assignmentStandardAnswer` AS `assignmentStandardAnswer`,
  `assignment`.`assignmentReview` AS `assignmentReview`,
  `assignment`.`assignmentReviewStatus` AS `assignmentReviewStatus`,
  `assignment`.`assignmentReviewUserId` AS `assignmentReviewUserId`,
  `assignment`.`assignmentReviewUser` AS `assignmentReviewUser`,
  `assignment`.`assignmentReviewAt` AS `assignmentReviewAt`,
  `assignment`.`operation` AS `operation`,
  `assignment`.`operationByUserId` AS `operationByUserId`,
  `assignment`.`operationByUser` AS `operationByUser`,
  `assignment`.`operationAt` AS `operationAt`
from
  (
    `assignment`
    left join `article` on (
      (`assignment`.`articleId` = `article`.`articleId`)
    )
  )

view01_assignment_list

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_assignment_list` AS
select
  `assignment`.`id` AS `id`,
  `assignment`.`assignmentId` AS `assignmentId`,
  `assignment`.`albumId` AS `albumId`,
  `assignment`.`classId` AS `classId`,
  `assignment`.`courseId` AS `courseId`,
  `assignment`.`courseBatchId` AS `courseBatchId`,
  `assignment`.`articleId` AS `articleId`,
  `assignment`.`userId` AS `userId`,
  `assignment`.`assignmentScore` AS `assignmentScore`,
  `assignment`.`assignmentFullMarks` AS `assignmentFullMarks`,
  `assignment`.`assignmentSubmitStatus` AS `assignmentSubmitStatus`,
  `assignment`.`assignmentSubmitAt` AS `assignmentSubmitAt`,
  `assignment`.`assignmentReviewStatus` AS `assignmentReviewStatus`,
  `assignment`.`assignmentReviewAt` AS `assignmentReviewAt`
from
  `assignment`

view01_category

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_category` AS
select
  count(
    (
      (`article`.`articlePublishStatus` = 'login')
      or NULL
    )
  ) AS `count`,
  `category`.`id` AS `id`,
  `category`.`categoryId` AS `categoryId`,
  `category`.`categoryName` AS `categoryName`,
  `category`.`prefix` AS `prefix`,
  `category`.`sortNumber` AS `sortNumber`,
  `category`.`categoryGroup` AS `categoryGroup`,
  `category`.`categoryGroupSort` AS `categoryGroupSort`,
  concat(
    '<',
    `category`.`categoryGroup`,
    `category`.`categoryGroupSort`,
    '>'
  ) AS `categoryGroupConcat`,
  `category`.`categoryIntro` AS `categoryIntro`,
  `category`.`categoryPublishStatus` AS `categoryPublishStatus`,
  `category`.`categoryCreateTime` AS `categoryCreateTime`,
  `category`.`categoryCreateUserId` AS `categoryCreateUserId`,
  `category`.`categoryCreateUsername` AS `categoryCreateUsername`,
  `category`.`categoryUpdateTime` AS `categoryUpdateTime`,
  `category`.`categoryUpdateUserId` AS `categoryUpdateUserId`,
  `category`.`categoryUpdateUsername` AS `categoryUpdateUsername`,
  `category`.`operation` AS `operation`,
  `category`.`operationByUserId` AS `operationByUserId`,
  `category`.`operationByUser` AS `operationByUser`,
  `category`.`operationAt` AS `operationAt`
from
  (
    `category`
    left join `article` on (
      (`category`.`categoryId` = `article`.`categoryId`)
    )
  )
group by
  `category`.`id`

view01_class_attendance

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_class_attendance` AS
select
  `course_class`.`id` AS `id`,
  `course_class`.`courseId` AS `courseId`,
  `course_class`.`courseBatchId` AS `courseBatchId`,
  `course_class`.`courseName` AS `courseName`,
  `course_class`.`courseBatch` AS `courseBatch`,
  `course_class`.`classId` AS `classId`,
  `course_class`.`className` AS `className`,
  `course_class`.`classNumber` AS `classNumber`,
  `course_class`.`classLeaderMemberId` AS `classLeaderMemberId`,
  `course_class`.`classLeaderUserId` AS `classLeaderUserId`,
  `course_class`.`classLeaderName` AS `classLeaderName`,
  `course_class`.`classLeaderDistrict` AS `classLeaderDistrict`,
  `course_class`.`maxNumberOfMember` AS `maxNumberOfMember`,
  '' AS `orgId`,
  '' AS `orgFullName`,
  `course_class`.`isJianghuGroup` AS `isJianghuGroup`,
  `course_class`.`isJianghuAllAdmin` AS `isJianghuAllAdmin`,
  `course_class`.`isDuoxingGroup` AS `isDuoxingGroup`,
  `course_class`.`isDuoxingAllAdmin` AS `isDuoxingAllAdmin`,
  `course_class`.`isBaofengGroup` AS `isBaofengGroup`,
  `course_class`.`isBaofengAllAdmin` AS `isBaofengAllAdmin`,
  `course_class`.`classStatus` AS `classStatus`,
  `course_class`.`operation` AS `operation`,
  `course_class`.`operationByUserId` AS `operationByUserId`,
  `course_class`.`operationByUser` AS `operationByUser`,
  `course_class`.`operationAt` AS `operationAt`,
  count(distinct `course_article`.`articleId`) AS `articleCount`
from
  (
    `course_class`
    left join `course_article` on (
      (
        `course_class`.`courseBatchId` = `course_article`.`courseBatchId`
      )
    )
  )
where
  (
    (`course_article`.`isExam` = '否')
    and (`course_class`.`classStatus` = 'active')
  )
group by
  `course_class`.`id`

view01_class_member

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_class_member` AS
select
  `cm`.`id` AS `id`,
  `cm`.`courseId` AS `courseId`,
  `cm`.`courseMemberId` AS `courseMemberId`,
  `cm`.`courseBatchId` AS `courseBatchId`,
  `cm`.`courseName` AS `courseName`,
  `cm`.`classId` AS `classId`,
  `cm`.`className` AS `className`,
  `cm`.`memberId` AS `memberId`,
  `cm`.`netName` AS `netName`,
  `cm`.`loginId` AS `loginId`,
  `cm`.`loginId` AS `userId`,
  `cm`.`district` AS `district`,
  `cm`.`classMemberRole` AS `classMemberRole`,
  if(
    (`cm`.`classMemberRole` = '老师'),
    'teacher',
    'student'
  ) AS `roleId`,
  `cm`.`classMemberStatus` AS `classMemberStatus`,
  `cm`.`operation` AS `operation`,
  `cm`.`operationByUserId` AS `operationByUserId`,
  `cm`.`operationByUser` AS `operationByUser`,
  `cm`.`operationAt` AS `operationAt`
from
  `class_member` `cm`

view01_course_article

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_course_article` AS
select
  `ca`.`id` AS `id`,
  `ca`.`courseId` AS `courseId`,
  `ca`.`courseBatchId` AS `courseBatchId`,
  `c`.`courseName` AS `courseName`,
  `c`.`courseBatch` AS `courseBatch`,
  `c`.`courseStatus` AS `courseStatus`,
  `ca`.`articleId` AS `articleId`,
  `a`.`articleTitle` AS `articleTitle`,
  if(
    (length(`a`.`articleShortName`) > 0),
    convert(`a`.`articleShortName` using utf8mb4),
    `ca`.`articleShortName`
  ) AS `articleShortName`,
  `ca`.`lessonNumber` AS `lessonNumber`,
  `ca`.`publishTime` AS `publishTime`,
  `ca`.`isExam` AS `isExam`,
  `ca`.`passcode` AS `passcode`,
  `ca`.`examDuration` AS `examDuration`,
  `ca`.`examExpiryTime` AS `examExpiryTime`,
  `ca`.`examMaxRetryNumber` AS `examMaxRetryNumber`,
  `ca`.`examPassingScore` AS `examPassingScore`,
  `ca`.`isShowAnswer` AS `isShowAnswer`,
  `a`.`categoryId` AS `categoryId`,
  `a`.`categoryName` AS `categoryName`,
  `a`.`articlePublishStatus` AS `articlePublishStatus`,
  `a`.`articlePublishTime` AS `articlePublishTime`,
  `a`.`articleCreateUserId` AS `createByUserId`,
  `ca`.`displayStatus` AS `displayStatus`,
  `ca`.`operation` AS `operation`,
  `ca`.`operationByUserId` AS `operationByUserId`,
  `ca`.`operationByUser` AS `operationByUser`,
  `ca`.`operationAt` AS `operationAt`,
  `ca`.`albumId` AS `albumId`
from
  (
    (
      `course_article` `ca`
      left join `course` `c` on ((`ca`.`courseBatchId` = `c`.`courseBatch`))
    )
    left join `article` `a` on ((`ca`.`articleId` = `a`.`articleId`))
  )

view01_course_article_detail

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_course_article_detail` AS
select
  `course_article`.`id` AS `id`,
  `course_article`.`courseId` AS `courseId`,
  `course_article`.`courseBatchId` AS `courseBatchId`,
  `course_article`.`courseName` AS `courseName`,
  `course_article`.`albumId` AS `albumId`,
  `course_article`.`albumTitle` AS `albumTitle`,
  `course_article`.`articleId` AS `articleId`,
  `course_article`.`articleTitle` AS `articleTitle`,
  `course_article`.`lessonNumber` AS `lessonNumber`,
  `course_article`.`publishTime` AS `publishTime`,
  `course_article`.`passcode` AS `passcode`,
  `course_article`.`isExam` AS `isExam`,
  `course_article`.`examDuration` AS `examDuration`,
  `course_article`.`examExpiryTime` AS `examExpiryTime`,
  `course_article`.`examMaxRetryNumber` AS `examMaxRetryNumber`,
  `course_article`.`examPassingScore` AS `examPassingScore`,
  `course_article`.`isShowAnswer` AS `isShowAnswer`,
  `course_article`.`operation` AS `operation`,
  `course_article`.`operationByUserId` AS `operationByUserId`,
  `course_article`.`operationByUser` AS `operationByUser`,
  `course_article`.`operationAt` AS `operationAt`,
  `article`.`articleAssignment` AS `articleAssignment`,
  `article`.`articleAssignmentWithAnswer` AS `articleAssignmentWithAnswer`,
  `article`.`articlePublishStatus` AS `articlePublishStatus`,
  `article`.`articlePublishTime` AS `articlePublishTime`
from
  (
    `course_article`
    left join `article` on (
      (
        `course_article`.`articleId` = `article`.`articleId`
      )
    )
  )

view01_course_with_article_count

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_course_with_article_count` AS
select
  `c`.`id` AS `id`,
  `c`.`courseId` AS `courseId`,
  `c`.`courseBatch` AS `courseBatch`,
  `c`.`courseBatchId` AS `courseBatchId`,
  `c`.`courseName` AS `courseName`,
  `c`.`courseOrProject` AS `courseOrProject`,
  `c`.`studentOrTeacher` AS `studentOrTeacher`,
  `c`.`applicationType` AS `applicationType`,
  `c`.`workload` AS `workload`,
  `c`.`prerequisites` AS `prerequisites`,
  `c`.`courseDescription` AS `courseDescription`,
  `c`.`courseLeader` AS `courseLeader`,
  `c`.`courseUrl` AS `courseUrl`,
  `c`.`teacherCourseBatchId` AS `teacherCourseBatchId`,
  `c`.`managerCourseBatchId` AS `managerCourseBatchId`,
  `c`.`startDate` AS `startDate`,
  `c`.`endDate` AS `endDate`,
  `c`.`totalClassHour` AS `totalClassHour`,
  `c`.`courseCredit` AS `courseCredit`,
  `c`.`courseStatus` AS `courseStatus`,
  `c`.`operation` AS `operation`,
  `c`.`operationAt` AS `operationAt`,
  `c`.`operationByUserId` AS `operationByUserId`,
  `c`.`operationByUser` AS `operationByUser`,
  sum(
    if(
      (
        (`c_art`.`isExam` = '否')
        and (`a`.`articlePublishStatus` = 'login')
      ),
      1,
      0
    )
  ) AS `articleCount`,
  sum(
    if(
      (
        (`c_art`.`isExam` = '是')
        and (`a`.`articlePublishStatus` = 'login')
      ),
      1,
      0
    )
  ) AS `examCount`
from
  (
    (
      `course` `c`
      left join `course_article` `c_art` on ((`c`.`courseBatchId` = `c_art`.`courseBatchId`))
    )
    left join `article` `a` on ((`c_art`.`articleId` = `a`.`articleId`))
  )
group by
  `c`.`id`

view01_exam

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_exam` AS
select
  `article`.`articleTitle` AS `articleTitle`,
  `examination`.`id` AS `id`,
  `examination`.`assignmentId` AS `assignmentId`,
  `examination`.`albumId` AS `albumId`,
  `examination`.`classId` AS `classId`,
  `examination`.`courseBatchId` AS `courseBatchId`,
  `examination`.`articleId` AS `articleId`,
  `examination`.`userId` AS `userId`,
  `examination`.`examRetryNumber` AS `examRetryNumber`,
  `examination`.`assignmentExamSeconds` AS `assignmentExamSeconds`,
  `examination`.`assignmentScore` AS `assignmentScore`,
  `examination`.`assignmentFullMarks` AS `assignmentFullMarks`,
  `examination`.`assignmentSubmitStatus` AS `assignmentSubmitStatus`,
  `examination`.`assignmentSubmitAt` AS `assignmentSubmitAt`,
  `examination`.`assignmentUserAnswer` AS `assignmentUserAnswer`,
  `examination`.`assignmentStandardAnswer` AS `assignmentStandardAnswer`,
  `examination`.`assignmentReview` AS `assignmentReview`,
  `examination`.`assignmentReviewStatus` AS `assignmentReviewStatus`,
  `examination`.`assignmentReviewUserId` AS `assignmentReviewUserId`,
  `examination`.`assignmentReviewUser` AS `assignmentReviewUser`,
  `examination`.`assignmentReviewAt` AS `assignmentReviewAt`,
  `examination`.`operation` AS `operation`,
  `examination`.`operationByUserId` AS `operationByUserId`,
  `examination`.`operationByUser` AS `operationByUser`,
  `examination`.`operationAt` AS `operationAt`
from
  (
    `examination`
    left join `article` on (
      (`examination`.`articleId` = `article`.`articleId`)
    )
  )

view01_exam_list

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_exam_list` AS
select
  `examination`.`id` AS `id`,
  `examination`.`assignmentId` AS `assignmentId`,
  `examination`.`albumId` AS `albumId`,
  `examination`.`classId` AS `classId`,
  `examination`.`courseBatchId` AS `courseBatchId`,
  `examination`.`articleId` AS `articleId`,
  `examination`.`userId` AS `userId`,
  `examination`.`assignmentScore` AS `assignmentScore`,
  `examination`.`assignmentFullMarks` AS `assignmentFullMarks`,
  `examination`.`assignmentSubmitStatus` AS `assignmentSubmitStatus`,
  `examination`.`assignmentSubmitAt` AS `assignmentSubmitAt`,
  `examination`.`assignmentReviewAt` AS `assignmentReviewAt`,
  `examination`.`assignmentReviewStatus` AS `assignmentReviewStatus`
from
  `examination`

view01_member

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_member` AS
select
  `m`.`id` AS `id`,
  `m`.`memberId` AS `memberId`,
  `m`.`midNumber` AS `midNumber`,
  '' AS `icName`,
  `m`.`loginId` AS `loginId`,
  '' AS `createLoginId`,
  `m`.`orgId` AS `orgId`,
  `m`.`orgName` AS `orgName`,
  '' AS `orgFullName`,
  `m`.`orgPath` AS `orgPath`,
  `m`.`roleId` AS `roleId`,
  `m`.`netName` AS `netName`,
  '' AS `title`,
  '' AS `companyName`,
  '' AS `department`,
  `m`.`phoneNumber` AS `phoneNumber`,
  `m`.`wechatId` AS `wechatId`,
  `m`.`qqNumber` AS `qqNumber`,
  `m`.`email` AS `email`,
  `m`.`address` AS `address`,
  '' AS `officeNumber`,
  '' AS `icNumber`,
  `m`.`sortId` AS `sortId`,
  `m`.`district` AS `district`,
  '' AS `districtName`,
  `m`.`joinState` AS `joinState`,
  `m`.`stage` AS `stage`,
  `m`.`position` AS `position`,
  `m`.`referer` AS `referer`,
  `m`.`pendingAction` AS `pendingAction`,
  `m`.`followUpBy` AS `followUpBy`,
  `m`.`alipayId` AS `alipayId`,
  `m`.`lineId` AS `lineId`,
  `m`.`whatsappId` AS `whatsappId`,
  `m`.`country` AS `country`,
  `m`.`countryCode` AS `countryCode`,
  `m`.`target` AS `target`,
  `m`.`guardianId` AS `guardianId`,
  `m`.`guardianName` AS `guardianName`,
  '' AS `memberTag`,
  `m`.`note` AS `note`,
  '' AS `courseComment`,
  '' AS `completedCourse`,
  '' AS `currentCourse`,
  '' AS `failedCourse`,
  `m`.`joinAt` AS `joinAt`,
  `m`.`leaveAt` AS `leaveAt`,
  `m`.`leaveNote` AS `leaveNote`,
  '' AS `adminOrgIdList`,
  '' AS `followMemberIdList`,
  `m`.`createAt` AS `createAt`,
  `m`.`updateAt` AS `updateAt`,
  '' AS `recordStatus`,
  `m`.`operation` AS `operation`,
  `m`.`operationAt` AS `operationAt`,
  `m`.`operationByUserId` AS `operationByUserId`,
  `m`.`operationByUser` AS `operationByUser`
from
  `cgg_member_v3` `m`

view01_note_list

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_note_list` AS
select
  `note`.`id` AS `id`,
  `note`.`noteId` AS `noteId`,
  `note`.`articleId` AS `articleId`,
  `note`.`classId` AS `classId`,
  `note`.`courseBatchId` AS `courseBatchId`,
  `note`.`albumId` AS `albumId`,
  `note`.`userId` AS `userId`,
  `note`.`noteReviewStatus` AS `noteReviewStatus`,
  `note`.`noteReviewAt` AS `noteReviewAt`,
  `note`.`noteSubmitStatus` AS `noteSubmitStatus`,
  `note`.`noteSubmitAt` AS `noteSubmitAt`
from
  `note`

view01_student_behavior

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_student_behavior` AS
select
  `student`.`netName` AS `username`,
  `student`.`loginId` AS `userId`,
  `student`.`classId` AS `classId`,
  `behavior`.`userActionType` AS `userActionType`,
  if(
    (`behavior`.`userActionType` = '提交作业'),
    max(`behavior`.`createTime`),
    NULL
  ) AS `lastAssignmentSubmitAt`,
  if(
    (`behavior`.`userActionType` = '提交笔记'),
    max(`behavior`.`createTime`),
    NULL
  ) AS `lastNoteSubmitAt`,
  `course_class`.`className` AS `groupName`
from
  (
    (
      `view01_class_member` `student`
      left join `behavior` on ((`student`.`loginId` = `behavior`.`userId`))
    )
    left join `course_class` on ((`student`.`classId` = `course_class`.`classId`))
  )
where
  (`student`.`roleId` = 'student')
group by
  `username`,
  `userId`,
  `student`.`classId`,
  `behavior`.`userActionType`,
  `groupName`

view01_student_learning_record

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_student_learning_record` AS
select
  `slr`.`courseMemberId` AS `courseMemberId`,
  `slr`.`courseBatchId` AS `courseBatchId`,
  `slr`.`courseName` AS `courseName`,
  `slr`.`courseId` AS `courseId`,
  `slr`.`classId` AS `classId`,
  `slr`.`memberId` AS `memberId`,
  `slr`.`netName` AS `netName`,
  `slr`.`loginId` AS `loginId`,
  `slr`.`district` AS `district`,
  `slr`.`orgFullName` AS `orgFullName`,
  `slr`.`result` AS `result`,
  `slr`.`teacherRecommendation` AS `teacherRecommendation`,
  `slr`.`courseCreditEarned` AS `courseCreditEarned`,
  `slr`.`examScoreStat` AS `examScoreStat`,
  `slr`.`attendanceStat` AS `attendanceStat`,
  `slr`.`assignmentStat` AS `assignmentStat`,
  `slr`.`noteStat` AS `noteStat`,
  `slr`.`totalLearningHour` AS `totalLearningHour`,
  `slr`.`memberCourseStatus` AS `memberCourseStatus`,
  if(isnull(`ccr`.`courseMemberId`), '', '已发布') AS `hasCompleteCourseRecord`,
  ifnull(`ccr`.`createAt`, 0) AS `completeCourseRecordCreateAt`,
  `course_class`.`className` AS `className`
from
  (
    (
      `student_learning_record` `slr`
      left join `student_complete_course_record` `ccr` on ((`slr`.`courseMemberId` = `ccr`.`courseMemberId`))
    )
    left join `course_class` on ((`slr`.`classId` = `course_class`.`classId`))
  )
where
  (`slr`.`memberCourseStatus` = '接收')

view01_teacher_behavior

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_teacher_behavior` AS
select
  `behavior`.`userId` AS `userId`,
  group_concat(distinct `c`.`classId` separator ',') AS `classId`,
  max(`behavior`.`createTime`) AS `lastTime`,
  `m`.`netName` AS `username`,
  `behavior`.`operationByUser` AS `operationByUser`,
  `behavior`.`operationAt` AS `operationAt`
from
  (
    (
      `behavior`
      left join `view01_class_member` `c` on ((`behavior`.`userId` = `c`.`loginId`))
    )
    left join `view01_member` `m` on ((`behavior`.`userId` = `m`.`loginId`))
  )
where
  (`c`.`roleId` = 'teacher')
group by
  `behavior`.`userId`,
  `username`

view01_user_course

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_user_course` AS
select
  `cm`.`id` AS `id`,
  `cm`.`classId` AS `classId`,
  `cm`.`className` AS `className`,
  `cm`.`memberId` AS `memberId`,
  `cm`.`loginId` AS `loginId`,
  `cm`.`loginId` AS `userId`,
  `cm`.`classMemberRole` AS `classMemberRole`,
  `cm`.`roleId` AS `roleId`,
  `cm`.`courseBatchId` AS `courseBatchId`,
  `c`.`courseId` AS `courseId`,
  `c`.`courseBatch` AS `courseBatch`,
  `c`.`courseName` AS `courseName`,
  `c`.`courseOrProject` AS `courseOrProject`,
  `c`.`studentOrTeacher` AS `studentOrTeacher`,
  `c`.`applicationType` AS `applicationType`,
  `c`.`workload` AS `workload`,
  `c`.`prerequisites` AS `prerequisites`,
  `c`.`courseDescription` AS `courseDescription`,
  `c`.`courseLeader` AS `courseLeader`,
  `c`.`courseUrl` AS `courseUrl`,
  `c`.`teacherCourseBatchId` AS `teacherCourseBatchId`,
  `c`.`managerCourseBatchId` AS `managerCourseBatchId`,
  `c`.`startDate` AS `startDate`,
  `c`.`endDate` AS `endDate`,
  `c`.`totalClassHour` AS `totalClassHour`,
  `c`.`courseCredit` AS `courseCredit`,
  `c`.`courseStatus` AS `courseStatus`,
  `c`.`operation` AS `operation`,
  `c`.`operationAt` AS `operationAt`,
  `c`.`operationByUserId` AS `operationByUserId`,
  `c`.`operationByUser` AS `operationByUser`
from
  (
    `view01_class_member` `cm`
    join `course` `c` on ((`cm`.`courseBatchId` = `c`.`courseBatchId`))
  )

view01_user_course_article

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_user_course_article` AS
select
  `class_member`.`id` AS `id`,
  `class_member`.`courseId` AS `courseId`,
  `class_member`.`courseMemberId` AS `courseMemberId`,
  `class_member`.`courseBatchId` AS `courseBatchId`,
  `course`.`courseName` AS `courseName`,
  `class_member`.`classId` AS `classId`,
  `class_member`.`className` AS `className`,
  `class_member`.`memberId` AS `memberId`,
  `class_member`.`netName` AS `netName`,
  `class_member`.`loginId` AS `loginId`,
  `class_member`.`loginId` AS `userId`,
  `class_member`.`district` AS `district`,
  `class_member`.`classMemberRole` AS `classMemberRole`,
  `class_member`.`roleId` AS `roleId`,
  `class_member`.`classMemberStatus` AS `classMemberStatus`,
  `class_member`.`operation` AS `operation`,
  `class_member`.`operationByUserId` AS `operationByUserId`,
  `class_member`.`operationByUser` AS `operationByUser`,
  `class_member`.`operationAt` AS `operationAt`,
  `course_article`.`albumId` AS `albumId`,
  `course_article`.`albumTitle` AS `albumTitle`,
  `course_article`.`articleId` AS `articleId`,
  `article`.`articleTitle` AS `articleTitle`,
  `course_article`.`categoryName` AS `categoryName`,
  `course_article`.`lessonNumber` AS `lessonNumber`,
  `course_article`.`publishTime` AS `publishTime`,
  `course_article`.`publishTime` AS `articlePublishTime`,
  `course_article`.`passcode` AS `passcode`,
  `course_article`.`isExam` AS `isExam`,
  `article`.`articleAssignmentPublishStatus` AS `articleAssignmentPublishStatus`
from
  (
    (
      (
        `view01_class_member` `class_member`
        join `course_article` on (
          (
            `course_article`.`courseBatchId` = `class_member`.`courseBatchId`
          )
        )
      )
      join `course` on (
        (
          `class_member`.`courseBatchId` = `course`.`courseBatchId`
        )
      )
    )
    join `article` on (
      (
        `course_article`.`articleId` = `article`.`articleId`
      )
    )
  )
where
  (`course_article`.`displayStatus` = '显示')

view02_assignment_for_review

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view02_assignment_for_review` AS
select
  `assignment`.`id` AS `id`,
  `assignment`.`id` AS `reviewId`,
  `assignment`.`assignmentId` AS `assignmentId`,
  `assignment`.`albumId` AS `albumId`,
  `assignment`.`classId` AS `classId`,
  `assignment`.`courseBatchId` AS `courseBatchId`,
  `assignment`.`articleId` AS `articleId`,
  `assignment`.`userId` AS `userId`,
  `assignment`.`assignmentRetryNumber` AS `assignmentRetryNumber`,
  `assignment`.`assignmentScore` AS `assignmentScore`,
  `assignment`.`assignmentFullMarks` AS `assignmentFullMarks`,
  `assignment`.`assignmentSubmitStatus` AS `assignmentSubmitStatus`,
  `assignment`.`assignmentSubmitAt` AS `assignmentSubmitAt`,
  `assignment`.`assignmentUserAnswer` AS `assignmentUserAnswer`,
  `assignment`.`assignmentStandardAnswer` AS `assignmentStandardAnswer`,
  `assignment`.`assignmentReviewUserId` AS `assignmentReviewUserId`,
  `assignment`.`assignmentReviewStatus` AS `assignmentReviewStatus`,
  `assignment`.`assignmentReviewAt` AS `assignmentReviewAt`,
  `assignment`.`operation` AS `operation`,
  `assignment`.`operationByUserId` AS `operationByUserId`,
  `assignment`.`operationByUser` AS `operationByUser`,
  `assignment`.`operationAt` AS `operationAt`,
  `article`.`articleTitle` AS `articleTitle`,
  `assignment`.`operationAt` AS `operationAtStudent`,
  `m`.`netName` AS `username`,
  `course_class`.`className` AS `groupName`,
  '' AS `groupDesc`,
  `assignment`.`assignmentReview` AS `assignmentReview`,
  `assignment`.`assignmentReviewUser` AS `assignmentReviewUser`
from
  (
    (
      (
        `assignment`
        left join `article` on (
          (`assignment`.`articleId` = `article`.`articleId`)
        )
      )
      left join `view01_member` `m` on ((`assignment`.`userId` = `m`.`loginId`))
    )
    left join `course_class` on (
      (`assignment`.`classId` = `course_class`.`classId`)
    )
  )

view02_attendance

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view02_attendance` AS
select
  `class_member`.`courseId` AS `courseId`,
  `class_member`.`courseBatchId` AS `courseBatchId`,
  `class_member`.`memberId` AS `studentId`,
  `class_member`.`classId` AS `classId`,
  `course_class`.`className` AS `className`,
  `m`.`netName` AS `username`,
  `att`.`id` AS `id`,
  `att`.`lessonNumber` AS `lessonNumber`,
  `att`.`attendanceDate` AS `attendanceDate`,
  `att`.`attendance` AS `attendance`,
  `att`.`markedAt` AS `markedAt`,
  `att`.`markedByUserId` AS `markedByUserId`,
  `att`.`markedByUser` AS `markedByUser`,
  `att`.`remarks` AS `remarks`,
  `att`.`operation` AS `operation`,
  `att`.`operationByUserId` AS `operationByUserId`,
  `att`.`operationByUser` AS `operationByUser`,
  `att`.`operationAt` AS `operationAt`
from
  (
    (
      (
        `view01_class_member` `class_member`
        left join `view01_member` `m` on ((`class_member`.`memberId` = `m`.`memberId`))
      )
      left join `attendance` `att` on (
        (
          (`class_member`.`classId` = `att`.`classId`)
          and (`class_member`.`memberId` = `att`.`userId`)
        )
      )
    )
    left join `course_class` on (
      (
        `class_member`.`classId` = `course_class`.`classId`
      )
    )
  )
where
  (`class_member`.`roleId` = 'student')

view02_examination_for_review

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view02_examination_for_review` AS
select
  `examination`.`id` AS `id`,
  `examination`.`id` AS `reviewId`,
  `examination`.`assignmentId` AS `assignmentId`,
  `examination`.`albumId` AS `albumId`,
  `examination`.`classId` AS `classId`,
  `examination`.`courseBatchId` AS `courseBatchId`,
  `examination`.`articleId` AS `articleId`,
  `examination`.`userId` AS `userId`,
  `examination`.`assignmentScore` AS `assignmentScore`,
  `examination`.`assignmentFullMarks` AS `assignmentFullMarks`,
  `examination`.`assignmentSubmitStatus` AS `assignmentSubmitStatus`,
  `examination`.`assignmentSubmitAt` AS `assignmentSubmitAt`,
  `examination`.`assignmentUserAnswer` AS `assignmentUserAnswer`,
  `examination`.`assignmentStandardAnswer` AS `assignmentStandardAnswer`,
  `examination`.`assignmentReview` AS `assignmentReview`,
  `examination`.`assignmentReviewUserId` AS `assignmentReviewUserId`,
  `examination`.`assignmentReviewUser` AS `assignmentReviewUser`,
  `examination`.`assignmentReviewStatus` AS `assignmentReviewStatus`,
  `examination`.`assignmentReviewAt` AS `assignmentReviewAt`,
  `examination`.`operation` AS `operation`,
  `examination`.`operationByUserId` AS `operationByUserId`,
  `examination`.`operationByUser` AS `operationByUser`,
  `examination`.`operationAt` AS `operationAt`,
  `article`.`articleTitle` AS `articleTitle`,
  `examination`.`operationAt` AS `operationAtStudent`,
  `m`.`netName` AS `username`,
  `course_class`.`className` AS `groupName`,
  '' AS `groupDesc`
from
  (
    (
      (
        `examination`
        left join `article` on (
          (`examination`.`articleId` = `article`.`articleId`)
        )
      )
      left join `view01_member` `m` on ((`examination`.`userId` = `m`.`loginId`))
    )
    left join `course_class` on (
      (
        `examination`.`classId` = `course_class`.`classId`
      )
    )
  )