江湖数字化平台
登录

数据结构

12114

表总览

普通表

_cache

  • 缓存表
  1. CREATE TABLE `_cache` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `userId` varchar(255)  NOT NULL COMMENT '用户Id',
  4.   `content` longtext  COMMENT '缓存数据',
  5.   `recordStatus` varchar(255)  DEFAULT 'active',
  6.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  7.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  8.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  9.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  10.   PRIMARY KEY (`id`) USING BTREE
  11. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '缓存表'

_constant

  • 常量表; 软删除未启用;
  1. CREATE TABLE `_constant` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `constantKey` varchar(255)  DEFAULT NULL,
  4.   `constantType` varchar(255)  DEFAULT NULL COMMENT '常量类型; object, array',
  5.   `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  6.   `constantValue` text  COMMENT '常量内容; object, array',
  7.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  8.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  9.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  10.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  11.   PRIMARY KEY (`id`) USING BTREE
  12. ) ENGINE = InnoDB AUTO_INCREMENT = 30 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '常量表; 软删除未启用;'

_page

  • 页面表; 软删除未启用;
  1. CREATE TABLE `_page` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `pageId` varchar(255)  DEFAULT NULL COMMENT 'pageId',
  4.   `pageFile` varchar(255)  DEFAULT NULL COMMENT 'page文件指定; 默认使用pageId.html',
  5.   `pageName` varchar(255)  DEFAULT NULL COMMENT 'page name',
  6.   `pageType` varchar(255)  DEFAULT NULL COMMENT '页面类型; showInMenu, dynamicInMenu',
  7.   `sort` varchar(255)  DEFAULT NULL,
  8.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  9.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  10.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  11.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  12.   PRIMARY KEY (`id`) USING BTREE
  13. ) ENGINE = InnoDB AUTO_INCREMENT = 52 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '页面表; 软删除未启用;'

_record_history

  • 数据历史表
  1. CREATE TABLE `_record_history` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `table` varchar(255)  DEFAULT NULL COMMENT '表',
  4.   `recordId` int(11) DEFAULT NULL COMMENT '数据在table中的主键id; recordContent.id',
  5.   `recordContent` text  NOT NULL COMMENT '数据',
  6.   `packageContent` longtext  NOT NULL COMMENT '当时请求的 package JSON',
  7.   `operation` varchar(255)  DEFAULT NULL COMMENT '操作; jhInsert, jhUpdate, jhDelete jhRestore',
  8.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId; recordContent.operationByUserId',
  9.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名; recordContent.operationByUser',
  10.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; recordContent.operationAt; E.g: 2021-05-28T10:24:54+08:00 ',
  11.   PRIMARY KEY (`id`) USING BTREE,
  12.   KEY `index_record_id` (`recordId`) USING BTREE,
  13.   KEY `index_table_action` (`table`, `operation`) USING BTREE
  14. ) ENGINE = InnoDB AUTO_INCREMENT = 40438 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '数据历史表'

_resource

  • 请求资源表; 软删除未启用; resourceId=${appId}.${pageId}.${actionId}
  1. CREATE TABLE `_resource` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `accessControlTable` varchar(255)  DEFAULT NULL COMMENT '数据规则控制表',
  4.   `resourceHook` text  COMMENT '[ "before": {"service": "xx", "serviceFunction": "xxx"}, "after": [] }',
  5.   `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  6.   `actionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  7.   `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  8.   `resourceType` varchar(255)  DEFAULT NULL COMMENT 'resource 类型; E.g: auth service sql',
  9.   `appDataSchema` text  COMMENT 'appData 参数校验',
  10.   `resourceData` text  COMMENT 'resource 数据; { "service": "auth", "serviceFunction": "passwordLogin" } or  { "table": "${tableName}", "action": "select", "whereCondition": ".where(function() {this.whereNot( { recordStatus: \\"active\\" })})" }',
  11.   `requestDemo` text  COMMENT '请求Demo',
  12.   `responseDemo` text  COMMENT '响应Demo',
  13.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  14.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  15.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  16.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  17.   PRIMARY KEY (`id`) USING BTREE
  18. ) ENGINE = InnoDB AUTO_INCREMENT = 460 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '请求资源表; 软删除未启用; resourceId=`${appId}.${pageId}.${actionId}`'

_test_case

  • 测试用例表
  1. CREATE TABLE `_test_case` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `pageId` varchar(255)  DEFAULT NULL COMMENT '页面Id',
  4.   `testId` varchar(255)  DEFAULT NULL COMMENT '测试用例Id; 10000 ++',
  5.   `testName` varchar(255)  DEFAULT NULL COMMENT '测试用例名',
  6.   `uiActionIdList` varchar(255)  DEFAULT NULL COMMENT 'uiAction列表; 一个测试用例对应多个uiActionId',
  7.   `testOpeartion` text  COMMENT '测试用例步骤;',
  8.   `operation` varchar(255)  DEFAULT NULL COMMENT '操作; jhInsert, jhUpdate, jhDelete jhRestore',
  9.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId; recordContent.operationByUserId',
  10.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名; recordContent.operationByUser',
  11.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; recordContent.operationAt; E.g: 2021-05-28T10:24:54+08:00 ',
  12.   PRIMARY KEY (`id`) USING BTREE
  13. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '测试用例表'

_ui

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

_user_session

  • 用户session表; deviceId 维度;软删除未启用;
  1. CREATE TABLE `_user_session` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `userId` varchar(255)  DEFAULT NULL COMMENT '用户id',
  4.   `userIp` varchar(255)  DEFAULT NULL COMMENT '用户ip',
  5.   `userIpRegion` varchar(255)  DEFAULT NULL COMMENT '用户Ip区域',
  6.   `userAgent` text  COMMENT '请求的 agent',
  7.   `deviceId` varchar(255)  DEFAULT NULL COMMENT '设备id',
  8.   `deviceType` varchar(255)  DEFAULT 'web' COMMENT '设备类型; flutter, web, bot_databot, bot_chatbot, bot_xiaochengxu',
  9.   `socketStatus` varchar(255)  DEFAULT 'offline' COMMENT 'socket状态',
  10.   `authToken` varchar(255)  DEFAULT NULL COMMENT 'auth token',
  11.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  12.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  13.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  14.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  15.   PRIMARY KEY (`id`) USING BTREE,
  16.   KEY `userId_index` (`userId`) USING BTREE,
  17.   KEY `userId_deviceId_index` (`userId`, `deviceId`) USING BTREE,
  18.   KEY `authToken_index` (`authToken`) USING BTREE
  19. ) ENGINE = InnoDB AUTO_INCREMENT = 10 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '用户session表; deviceId 维度;软删除未启用;'

bill

  1. CREATE TABLE `bill` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `billId` varchar(255)  DEFAULT NULL COMMENT '账单ID',
  4.   `billBatchId` varchar(255)  DEFAULT NULL COMMENT '批次ID',
  5.   `studentId` varchar(255)  DEFAULT NULL COMMENT '学生ID',
  6.   `paymentType` varchar(255)  DEFAULT NULL COMMENT '缴费类型',
  7.   `paymentConfig` longtext  COMMENT '缴费项目(项目/金额)',
  8.   `reductionConfig` longtext  COMMENT '减免项目(项目/金额)',
  9.   `paymentItemActual` varchar(255)  DEFAULT NULL COMMENT '实缴费项目(项目/金额)',
  10.   `totalPayment` decimal(11, 2) DEFAULT NULL COMMENT '账单总额',
  11.   `reductionTotal` decimal(11, 2) DEFAULT NULL COMMENT '减免总额',
  12.   `scholarshipReductionTotal` decimal(11, 2) DEFAULT NULL COMMENT '奖学金抵扣总额',
  13.   `totalPaymentDue` decimal(11, 2) DEFAULT NULL COMMENT '应缴费总额',
  14.   `totalPaymentActual` decimal(11, 2) DEFAULT NULL COMMENT '实缴费总额',
  15.   `totalPaymentOutstanding` decimal(11, 2) DEFAULT NULL COMMENT '待缴费总额',
  16.   `paymentStatus` varchar(255)  DEFAULT NULL COMMENT '缴费状态',
  17.   `confirmationStatus` varchar(255)  DEFAULT NULL COMMENT '确认状态',
  18.   `billCompletedStatus` varchar(255)  DEFAULT NULL COMMENT '缴费完成状态',
  19.   `remarks` varchar(1023)  DEFAULT NULL,
  20.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  21.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  22.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  23.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  24.   PRIMARY KEY (`id`) USING BTREE,
  25.   UNIQUE KEY `billId` (`billId`) USING BTREE,
  26.   KEY `studentId` (`studentId`) USING BTREE,
  27.   KEY `paymentType` (`paymentType`) USING BTREE
  28. ) ENGINE = InnoDB AUTO_INCREMENT = 10180 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

bill_batch

  1. CREATE TABLE `bill_batch` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `idSequence` varchar(255)  NOT NULL DEFAULT '' COMMENT '自增id',
  4.   `billBatchId` varchar(255)  DEFAULT NULL COMMENT '批次ID',
  5.   `billBatchName` varchar(255)  DEFAULT NULL COMMENT '批次名称',
  6.   `paymentType` varchar(255)  DEFAULT NULL COMMENT '缴费类型',
  7.   `paymentConfig` longtext  COMMENT '缴费项目配置(项目/金额)',
  8.   `reductionConfig` longtext  COMMENT '减免项目配置(项目/所需减免的人员)',
  9.   `paymentPersonConfig` longtext  COMMENT '缴费人员配置',
  10.   `totalPayment` decimal(11, 2) DEFAULT NULL COMMENT '缴费总额',
  11.   `remarks` varchar(1023)  DEFAULT NULL,
  12.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  13.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  14.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  15.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  16.   `billBatchStatus` varchar(255)  DEFAULT NULL COMMENT '批次状态; 结束生成, 正常',
  17.   PRIMARY KEY (`id`) USING BTREE
  18. ) ENGINE = InnoDB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

bill_history

  1. CREATE TABLE `bill_history` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `billId` varchar(255)  DEFAULT NULL COMMENT '账单ID',
  4.   `billBatchId` varchar(255)  DEFAULT NULL COMMENT '批次ID',
  5.   `billBatchName` varchar(255)  DEFAULT NULL COMMENT '批次名称',
  6.   `studentId` varchar(255)  DEFAULT NULL COMMENT '学生ID',
  7.   `name` varchar(255)  DEFAULT NULL COMMENT '姓名',
  8.   `gender` varchar(255)  DEFAULT NULL COMMENT '性别',
  9.   `segment` varchar(255)  DEFAULT NULL COMMENT '学部',
  10.   `className` varchar(255)  DEFAULT NULL COMMENT '班级号',
  11.   `classId` varchar(255)  DEFAULT NULL COMMENT '班级Id',
  12.   `year` varchar(255)  DEFAULT NULL COMMENT '学年',
  13.   `semester` varchar(255)  DEFAULT NULL COMMENT '学期',
  14.   `paymentType` varchar(255)  DEFAULT NULL COMMENT '缴费类型',
  15.   `paymentConfig` longtext  COMMENT '缴费项目(项目/金额)',
  16.   `reductionConfig` longtext  COMMENT '减免项目(项目/金额)',
  17.   `paymentItemActual` varchar(255)  DEFAULT NULL COMMENT '实缴费项目(项目/金额)',
  18.   `totalPayment` decimal(11, 2) DEFAULT NULL COMMENT '账单总额',
  19.   `reductionTotal` decimal(11, 2) DEFAULT NULL COMMENT '减免总额',
  20.   `scholarshipReductionTotal` decimal(11, 2) DEFAULT NULL COMMENT '奖学金抵扣总额',
  21.   `totalPaymentDue` decimal(11, 2) DEFAULT NULL COMMENT '应缴费总额',
  22.   `totalPaymentActual` decimal(11, 2) DEFAULT NULL COMMENT '实缴费总额',
  23.   `totalPaymentOutstanding` decimal(11, 2) DEFAULT NULL COMMENT '待缴费总额',
  24.   `paymentStatus` varchar(255)  DEFAULT NULL COMMENT '缴费状态',
  25.   `confirmationStatus` varchar(255)  DEFAULT NULL COMMENT '确认状态',
  26.   `billCompletedStatus` varchar(255)  DEFAULT NULL COMMENT '学生缴费完成状态',
  27.   `remarks` varchar(1023)  DEFAULT NULL,
  28.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  29.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  30.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  31.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  32.   `level` varchar(255)  DEFAULT NULL COMMENT '年级',
  33.   `number` varchar(255)  DEFAULT NULL COMMENT '班级编号',
  34.   `afterSchoolCareType` varchar(255)  DEFAULT NULL COMMENT '托管模式',
  35.   `freshmanOrReturning` varchar(255)  DEFAULT NULL COMMENT '新生/老生',
  36.   PRIMARY KEY (`id`) USING BTREE
  37. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

payment_record

  1. CREATE TABLE `payment_record` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `idSequence` int(11) DEFAULT NULL COMMENT '自增id',
  4.   `paymentRecordId` varchar(255)  DEFAULT NULL COMMENT '缴费ID',
  5.   `billId` varchar(255)  DEFAULT NULL COMMENT '账单ID',
  6.   `paymentAmount` decimal(11, 2) DEFAULT NULL COMMENT '缴费金额',
  7.   `paymentAt` varchar(255)  DEFAULT NULL COMMENT '缴费时间',
  8.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  9.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  10.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  11.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  12.   PRIMARY KEY (`id`) USING BTREE
  13. ) ENGINE = InnoDB AUTO_INCREMENT = 2982 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

student_scholarship

  1. CREATE TABLE `student_scholarship` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `studentId` varchar(255)  DEFAULT NULL COMMENT '学生ID',
  4.   `scholarshipTotal` decimal(11, 2) DEFAULT '0.00' COMMENT '奖学金总额度',
  5.   `scholarshipUnused` decimal(11, 2) DEFAULT '0.00' COMMENT '剩余额度',
  6.   `scholarshipUsed` decimal(11, 2) DEFAULT '0.00' COMMENT '已使用额度',
  7.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  8.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  9.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  10.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  11.   PRIMARY KEY (`id`) USING BTREE,
  12.   UNIQUE KEY `uniq` (`studentId`) USING BTREE
  13. ) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

student_scholarship_record

  1. CREATE TABLE `student_scholarship_record` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `idSequence` varchar(255)  DEFAULT NULL COMMENT '自增id',
  4.   `scholarshipRecordId` varchar(255)  DEFAULT NULL COMMENT '奖学金明细ID',
  5.   `year` varchar(255)  DEFAULT NULL COMMENT '自然年',
  6.   `semester` varchar(255)  DEFAULT NULL COMMENT '学期',
  7.   `studentId` varchar(255)  DEFAULT NULL COMMENT '学生ID',
  8.   `transactionType` varchar(255)  DEFAULT NULL COMMENT '交易类型;学费抵扣,奖学金发放,转出',
  9.   `transactionAmount` decimal(11, 2) DEFAULT NULL COMMENT '交易金额',
  10.   `transactionAt` varchar(255)  DEFAULT NULL COMMENT '交易时间',
  11.   `remarks` varchar(1023)  DEFAULT NULL,
  12.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  13.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  14.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  15.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  16.   PRIMARY KEY (`id`) USING BTREE
  17. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

student_semester_scholarship

  1. CREATE TABLE `student_semester_scholarship` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `year` varchar(255)  DEFAULT NULL COMMENT '自然年',
  4.   `semester` varchar(255)  DEFAULT NULL COMMENT '学期',
  5.   `studentId` varchar(255)  DEFAULT NULL COMMENT '学生ID',
  6.   `semesterScholarship` decimal(10, 2) DEFAULT NULL COMMENT '本学期奖学金额度',
  7.   `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  8.   `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  9.   `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  10.   `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  11.   `semesterScholarshipUsed` decimal(10, 2) DEFAULT '0.00' COMMENT '本学期奖学金已使用额度',
  12.   `semesterScholarshipUnused` decimal(10, 2) DEFAULT '0.00' COMMENT '本学期奖学金剩余额度',
  13.   PRIMARY KEY (`id`) USING BTREE,
  14.   UNIQUE KEY `uniq` (`year`, `semester`, `studentId`) USING BTREE,
  15.   KEY `indexStudent` (`studentId`) USING BTREE
  16. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

视图

_directory_user_session

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_directory_user_session` AS
  2. select
  3.   `lms_data_repository`.`enterprise_directory_user_session`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_directory_user_session`.`userId` AS `userId`,
  5.   `lms_data_repository`.`enterprise_directory_user_session`.`userIp` AS `userIp`,
  6.   `lms_data_repository`.`enterprise_directory_user_session`.`userIpRegion` AS `userIpRegion`,
  7.   `lms_data_repository`.`enterprise_directory_user_session`.`userAgent` AS `userAgent`,
  8.   `lms_data_repository`.`enterprise_directory_user_session`.`deviceId` AS `deviceId`,
  9.   `lms_data_repository`.`enterprise_directory_user_session`.`deviceType` AS `deviceType`,
  10.   `lms_data_repository`.`enterprise_directory_user_session`.`socketStatus` AS `socketStatus`,
  11.   `lms_data_repository`.`enterprise_directory_user_session`.`authToken` AS `authToken`,
  12.   `lms_data_repository`.`enterprise_directory_user_session`.`operation` AS `operation`,
  13.   `lms_data_repository`.`enterprise_directory_user_session`.`operationByUserId` AS `operationByUserId`,
  14.   `lms_data_repository`.`enterprise_directory_user_session`.`operationByUser` AS `operationByUser`,
  15.   `lms_data_repository`.`enterprise_directory_user_session`.`operationAt` AS `operationAt`
  16. from
  17.   `lms_data_repository`.`enterprise_directory_user_session`

_group

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_group` AS
  2. select
  3.   `lms_data_repository`.`enterprise_group`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_group`.`groupId` AS `groupId`,
  5.   `lms_data_repository`.`enterprise_group`.`groupLastId` AS `groupLastId`,
  6.   `lms_data_repository`.`enterprise_group`.`groupPath` AS `groupPath`,
  7.   `lms_data_repository`.`enterprise_group`.`groupName` AS `groupName`,
  8.   `lms_data_repository`.`enterprise_group`.`groupDeptName` AS `groupDeptName`,
  9.   `lms_data_repository`.`enterprise_group`.`groupAllName` AS `groupAllName`,
  10.   `lms_data_repository`.`enterprise_group`.`principalId` AS `principalId`,
  11.   `lms_data_repository`.`enterprise_group`.`headId` AS `headId`,
  12.   `lms_data_repository`.`enterprise_group`.`leadId` AS `leadId`,
  13.   `lms_data_repository`.`enterprise_group`.`groupDesc` AS `groupDesc`,
  14.   `lms_data_repository`.`enterprise_group`.`operation` AS `operation`,
  15.   `lms_data_repository`.`enterprise_group`.`operationByUserId` AS `operationByUserId`,
  16.   `lms_data_repository`.`enterprise_group`.`operationByUser` AS `operationByUser`,
  17.   `lms_data_repository`.`enterprise_group`.`operationAt` AS `operationAt`
  18. from
  19.   `lms_data_repository`.`enterprise_group`

_role

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_role` AS
  2. select
  3.   `lms_data_repository`.`enterprise_role`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_role`.`roleId` AS `roleId`,
  5.   `lms_data_repository`.`enterprise_role`.`roleName` AS `roleName`,
  6.   `lms_data_repository`.`enterprise_role`.`roleType` AS `roleType`,
  7.   `lms_data_repository`.`enterprise_role`.`roleDesc` AS `roleDesc`,
  8.   `lms_data_repository`.`enterprise_role`.`operation` AS `operation`,
  9.   `lms_data_repository`.`enterprise_role`.`operationByUserId` AS `operationByUserId`,
  10.   `lms_data_repository`.`enterprise_role`.`operationByUser` AS `operationByUser`,
  11.   `lms_data_repository`.`enterprise_role`.`operationAt` AS `operationAt`
  12. from
  13.   `lms_data_repository`.`enterprise_role`

_user_group_role

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_user_group_role` AS
  2. select
  3.   `lms_data_repository`.`enterprise_user_group_role`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_user_group_role`.`userId` AS `userId`,
  5.   `lms_data_repository`.`enterprise_user_group_role`.`groupId` AS `groupId`,
  6.   `lms_data_repository`.`enterprise_user_group_role`.`roleId` AS `roleId`,
  7.   `lms_data_repository`.`enterprise_user_group_role`.`roleDeadline` AS `roleDeadline`,
  8.   `lms_data_repository`.`enterprise_user_group_role`.`operation` AS `operation`,
  9.   `lms_data_repository`.`enterprise_user_group_role`.`operationByUserId` AS `operationByUserId`,
  10.   `lms_data_repository`.`enterprise_user_group_role`.`operationByUser` AS `operationByUser`,
  11.   `lms_data_repository`.`enterprise_user_group_role`.`operationAt` AS `operationAt`
  12. from
  13.   `lms_data_repository`.`enterprise_user_group_role`

_user_group_role_page

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_user_group_role_page` AS
  2. select
  3.   `lms_data_repository`.`enterprise_user_group_role_page`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_user_group_role_page`.`appId` AS `appId`,
  5.   `lms_data_repository`.`enterprise_user_group_role_page`.`user` AS `user`,
  6.   `lms_data_repository`.`enterprise_user_group_role_page`.`group` AS `group`,
  7.   `lms_data_repository`.`enterprise_user_group_role_page`.`role` AS `role`,
  8.   `lms_data_repository`.`enterprise_user_group_role_page`.`page` AS `page`,
  9.   `lms_data_repository`.`enterprise_user_group_role_page`.`allowOrDeny` AS `allowOrDeny`,
  10.   `lms_data_repository`.`enterprise_user_group_role_page`.`desc` AS `desc`,
  11.   `lms_data_repository`.`enterprise_user_group_role_page`.`source` AS `source`,
  12.   `lms_data_repository`.`enterprise_user_group_role_page`.`operation` AS `operation`,
  13.   `lms_data_repository`.`enterprise_user_group_role_page`.`operationByUserId` AS `operationByUserId`,
  14.   `lms_data_repository`.`enterprise_user_group_role_page`.`operationByUser` AS `operationByUser`,
  15.   `lms_data_repository`.`enterprise_user_group_role_page`.`operationAt` AS `operationAt`
  16. from
  17.   `lms_data_repository`.`enterprise_user_group_role_page`
  18. where
  19.   (
  20.     (
  21.       `lms_data_repository`.`enterprise_user_group_role_page`.`appId` = 'lms-finance'
  22.     )
  23.     or (
  24.       `lms_data_repository`.`enterprise_user_group_role_page`.`appId` = '*'
  25.     )
  26.   )

_user_group_role_resource

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_user_group_role_resource` AS
  2. select
  3.   `lms_data_repository`.`enterprise_user_group_role_resource`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_user_group_role_resource`.`appId` AS `appId`,
  5.   `lms_data_repository`.`enterprise_user_group_role_resource`.`user` AS `user`,
  6.   `lms_data_repository`.`enterprise_user_group_role_resource`.`group` AS `group`,
  7.   `lms_data_repository`.`enterprise_user_group_role_resource`.`role` AS `role`,
  8.   `lms_data_repository`.`enterprise_user_group_role_resource`.`resource` AS `resource`,
  9.   `lms_data_repository`.`enterprise_user_group_role_resource`.`allowOrDeny` AS `allowOrDeny`,
  10.   `lms_data_repository`.`enterprise_user_group_role_resource`.`desc` AS `desc`,
  11.   `lms_data_repository`.`enterprise_user_group_role_resource`.`source` AS `source`,
  12.   `lms_data_repository`.`enterprise_user_group_role_resource`.`operation` AS `operation`,
  13.   `lms_data_repository`.`enterprise_user_group_role_resource`.`operationByUserId` AS `operationByUserId`,
  14.   `lms_data_repository`.`enterprise_user_group_role_resource`.`operationByUser` AS `operationByUser`,
  15.   `lms_data_repository`.`enterprise_user_group_role_resource`.`operationAt` AS `operationAt`
  16. from
  17.   `lms_data_repository`.`enterprise_user_group_role_resource`
  18. where
  19.   (
  20.     (
  21.       `lms_data_repository`.`enterprise_user_group_role_resource`.`appId` = 'lms-finance'
  22.     )
  23.     or (
  24.       `lms_data_repository`.`enterprise_user_group_role_resource`.`appId` = '*'
  25.     )
  26.   )

_view01_user

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view01_user` AS
  2. select
  3.   `lms_data_repository`.`enterprise_view01_user`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_view01_user`.`idSequence` AS `idSequence`,
  5.   `lms_data_repository`.`enterprise_view01_user`.`userId` AS `userId`,
  6.   `lms_data_repository`.`enterprise_view01_user`.`username` AS `username`,
  7.   `lms_data_repository`.`enterprise_view01_user`.`phoneNumber` AS `phoneNumber`,
  8.   `lms_data_repository`.`enterprise_view01_user`.`email` AS `email`,
  9.   `lms_data_repository`.`enterprise_view01_user`.`userStatus` AS `userStatus`,
  10.   `lms_data_repository`.`enterprise_view01_user`.`qiweiId` AS `qiweiId`,
  11.   `lms_data_repository`.`enterprise_view01_user`.`userConfig` AS `userConfig`,
  12.   `lms_data_repository`.`enterprise_view01_user`.`password` AS `password`,
  13.   `lms_data_repository`.`enterprise_view01_user`.`md5Salt` AS `md5Salt`,
  14.   `lms_data_repository`.`enterprise_view01_user`.`clearTextPassword` AS `clearTextPassword`,
  15.   `lms_data_repository`.`enterprise_view01_user`.`operation` AS `operation`,
  16.   `lms_data_repository`.`enterprise_view01_user`.`operationByUserId` AS `operationByUserId`,
  17.   `lms_data_repository`.`enterprise_view01_user`.`operationByUser` AS `operationByUser`,
  18.   `lms_data_repository`.`enterprise_view01_user`.`operationAt` AS `operationAt`
  19. from
  20.   `lms_data_repository`.`enterprise_view01_user`

_view02_user_app

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view02_user_app` AS
  2. select
  3.   `lms_data_repository`.`enterprise_view02_user_app`.`id` AS `id`,
  4.   `lms_data_repository`.`enterprise_view02_user_app`.`userId` AS `userId`,
  5.   `lms_data_repository`.`enterprise_view02_user_app`.`appId` AS `appId`
  6. from
  7.   `lms_data_repository`.`enterprise_view02_user_app`
  8. where
  9.   (
  10.     `lms_data_repository`.`enterprise_view02_user_app`.`appId` = 'lms-finance'
  11.   )

tuition_bill_order

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `tuition_bill_order` AS
  2. select
  3.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`id` AS `id`,
  4.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`idSequence` AS `idSequence`,
  5.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`studentId` AS `studentId`,
  6.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`classId` AS `classId`,
  7.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`billId` AS `billId`,
  8.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`billInfo` AS `billInfo`,
  9.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`tuitionOrderId` AS `tuitionOrderId`,
  10.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`amount` AS `amount`,
  11.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`traceNo` AS `traceNo`,
  12.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`bocPayLs` AS `bocPayLs`,
  13.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`bocQrCode` AS `bocQrCode`,
  14.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`bocResponse` AS `bocResponse`,
  15.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`bocNotify` AS `bocNotify`,
  16.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`bocNotifyAt` AS `bocNotifyAt`,
  17.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`paymentStatus` AS `paymentStatus`,
  18.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`retryCount` AS `retryCount`,
  19.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`createAt` AS `createAt`,
  20.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`createUser` AS `createUser`,
  21.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`operation` AS `operation`,
  22.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`operationByUserId` AS `operationByUserId`,
  23.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`operationByUser` AS `operationByUser`,
  24.   `lms_data_repository`.`lms_notice__tuition_bill_order`.`operationAt` AS `operationAt`
  25. from
  26.   `lms_data_repository`.`lms_notice__tuition_bill_order`

view00_student_basic_class

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view00_student_basic_class` AS
  2. select
  3.   `view01_student_basic_class`.`studentId` AS `studentId`,
  4.   `view01_student_basic_class`.`name` AS `name`,
  5.   `view01_student_basic_class`.`academicYear` AS `academicYear`,
  6.   `view01_student_basic_class`.`classNum` AS `classNum`,
  7.   `view01_student_basic_class`.`studentStatus` AS `studentStatus`,
  8.   `view01_student_basic_class`.`remarks` AS `remarks`,
  9.   `view01_student_basic_class`.`newRemarks` AS `newRemarks`,
  10.   `view01_student_basic_class`.`gender` AS `gender`,
  11.   `view01_student_basic_class`.`guardian2Contact` AS `guardian2Contact`,
  12.   `view01_student_basic_class`.`guardian2Relationship` AS `guardian2Relationship`,
  13.   `view01_student_basic_class`.`guardian2Name` AS `guardian2Name`,
  14.   `view01_student_basic_class`.`guardian1Contact` AS `guardian1Contact`,
  15.   `view01_student_basic_class`.`guardian1Relationship` AS `guardian1Relationship`,
  16.   `view01_student_basic_class`.`guardian1Name` AS `guardian1Name`,
  17.   `view01_student_basic_class`.`classId` AS `classId`,
  18.   `view01_student_basic_class`.`className` AS `className`,
  19.   `view01_student_basic_class`.`requestType` AS `requestType`,
  20.   `view01_student_basic_class`.`completedClassAssignedYesOrNo` AS `completedClassAssignedYesOrNo`,
  21.   `view01_student_basic_class`.`completedEnrollmentRecordYesOrNo` AS `completedEnrollmentRecordYesOrNo`,
  22.   `view01_student_basic_class`.`completedLogisticsAssignedYesOrNo` AS `completedLogisticsAssignedYesOrNo`,
  23.   `view01_student_basic_class`.`completedPaymentYesOrNo` AS `completedPaymentYesOrNo`,
  24.   `view01_student_basic_class`.`completedEnrollmentYesOrNo` AS `completedEnrollmentYesOrNo`,
  25.   `view01_student_basic_class`.`completedQuitYesOrNo` AS `completedQuitYesOrNo`,
  26.   `view01_student_basic_class`.`id` AS `id`,
  27.   `view01_student_basic_class`.`compeletedUpgradeYesOrNo` AS `compeletedUpgradeYesOrNo`,
  28.   `view01_student_basic_class`.`enrollmentYear` AS `enrollmentYear`,
  29.   `view01_student_basic_class`.`enrollmentSemester` AS `enrollmentSemester`,
  30.   `view01_student_basic_class`.`enrollmentSegment` AS `enrollmentSegment`,
  31.   `view01_student_basic_class`.`enrollmentLevel` AS `enrollmentLevel`,
  32.   `view01_student_basic_class`.`middleSchoolExamScore` AS `middleSchoolExamScore`,
  33.   `view01_student_basic_class`.`operationAt` AS `operationAt`,
  34.   `view01_student_basic_class`.`number` AS `number`,
  35.   `view01_student_basic_class`.`icNumber` AS `icNumber`,
  36.   `view01_student_basic_class`.`afterSchoolCareType` AS `afterSchoolCareType`,
  37.   `view01_student_basic_class`.`freshmanOrReturning` AS `freshmanOrReturning`,
  38.   `lms_finance`.`student_scholarship`.`scholarshipTotal` AS `scholarshipTotal`,
  39.   `lms_finance`.`student_scholarship`.`scholarshipUsed` AS `scholarshipUsed`,
  40.   `lms_finance`.`student_scholarship`.`scholarshipUnused` AS `scholarshipUnused`,
  41.   `lms_finance`.`student_semester_scholarship`.`semesterScholarship` AS `semesterScholarship`,
  42.   `lms_finance`.`student_semester_scholarship`.`semesterScholarshipUsed` AS `semesterScholarshipUsed`,
  43.   `lms_finance`.`student_semester_scholarship`.`semesterScholarshipUnused` AS `semesterScholarshipUnused`,
  44.   (
  45.     case
  46.       when (`view01_student_basic_class`.`year` is not null) then `view01_student_basic_class`.`year`
  47.       else (
  48.         case
  49.           when (
  50.             `view01_student_basic_class`.`enrollmentSemester` = '秋季'
  51.           ) then substring_index(
  52.             `view01_student_basic_class`.`enrollmentYear`,
  53.             '-',
  54.             1
  55.           )
  56.           else substring_index(
  57.             `view01_student_basic_class`.`enrollmentYear`,
  58.             '-',
  59.             - (1)
  60.           )
  61.         end
  62.       )
  63.     end
  64.   ) AS `year`,
  65.   (
  66.     case
  67.       when (
  68.         `view01_student_basic_class`.`semester` is not null
  69.       ) then `view01_student_basic_class`.`semester`
  70.       else `view01_student_basic_class`.`enrollmentSemester`
  71.     end
  72.   ) AS `semester`,
  73.   (
  74.     case
  75.       when (
  76.         `view01_student_basic_class`.`segment` is not null
  77.       ) then convert(
  78.         `view01_student_basic_class`.`segment` using utf8mb4
  79.       )
  80.       else `view01_student_basic_class`.`enrollmentSegment`
  81.     end
  82.   ) AS `segment`,
  83.   (
  84.     case
  85.       when (`view01_student_basic_class`.`level` is not null) then `view01_student_basic_class`.`level`
  86.       else `view01_student_basic_class`.`enrollmentLevel`
  87.     end
  88.   ) AS `level`
  89. from
  90.   (
  91.     (
  92.       `lms_student_admin`.`view01_student_basic_class`
  93.       left join `lms_finance`.`student_scholarship` on (
  94.         (
  95.           `lms_finance`.`student_scholarship`.`studentId` = `view01_student_basic_class`.`studentId`
  96.         )
  97.       )
  98.     )
  99.     left join `lms_finance`.`student_semester_scholarship` on (
  100.       (
  101.         (
  102.           `lms_finance`.`student_semester_scholarship`.`studentId` = `view01_student_basic_class`.`studentId`
  103.         )
  104.         and (
  105.           `lms_finance`.`student_semester_scholarship`.`year` = (
  106.             case
  107.               when (`view01_student_basic_class`.`year` is not null) then `view01_student_basic_class`.`year`
  108.               else (
  109.                 case
  110.                   when (
  111.                     `view01_student_basic_class`.`enrollmentSemester` = '秋季'
  112.                   ) then substring_index(
  113.                     `view01_student_basic_class`.`enrollmentYear`,
  114.                     '-',
  115.                     1
  116.                   )
  117.                   else substring_index(
  118.                     `view01_student_basic_class`.`enrollmentYear`,
  119.                     '-',
  120.                     - (1)
  121.                   )
  122.                 end
  123.               )
  124.             end
  125.           )
  126.         )
  127.         and (
  128.           `lms_finance`.`student_semester_scholarship`.`semester` = (
  129.             case
  130.               when (
  131.                 `view01_student_basic_class`.`semester` is not null
  132.               ) then `view01_student_basic_class`.`semester`
  133.               else `view01_student_basic_class`.`enrollmentSemester`
  134.             end
  135.           )
  136.         )
  137.       )
  138.     )
  139.   )

view01_bill

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_bill` AS
  2. select
  3.   `lms_finance`.`bill`.`id` AS `id`,
  4.   `lms_finance`.`bill`.`billId` AS `billId`,
  5.   `lms_finance`.`bill`.`billBatchId` AS `billBatchId`,
  6.   `lms_finance`.`bill`.`studentId` AS `studentId`,
  7.   `lms_finance`.`bill`.`paymentType` AS `paymentType`,
  8.   `lms_finance`.`bill`.`paymentConfig` AS `paymentConfig`,
  9.   `lms_finance`.`bill`.`reductionConfig` AS `reductionConfig`,
  10.   `lms_finance`.`bill`.`paymentItemActual` AS `paymentItemActual`,
  11.   `lms_finance`.`bill`.`totalPayment` AS `totalPayment`,
  12.   `lms_finance`.`bill`.`reductionTotal` AS `reductionTotal`,
  13.   `lms_finance`.`bill`.`scholarshipReductionTotal` AS `scholarshipReductionTotal`,
  14.   `lms_finance`.`bill`.`totalPaymentDue` AS `totalPaymentDue`,
  15.   `lms_finance`.`bill`.`totalPaymentActual` AS `totalPaymentActual`,
  16.   `lms_finance`.`bill`.`totalPaymentOutstanding` AS `totalPaymentOutstanding`,
  17.   `lms_finance`.`bill`.`paymentStatus` AS `paymentStatus`,
  18.   `lms_finance`.`bill`.`confirmationStatus` AS `confirmationStatus`,
  19.   `lms_finance`.`bill`.`remarks` AS `remarks`,
  20.   `lms_finance`.`bill`.`operation` AS `operation`,
  21.   `lms_finance`.`bill`.`operationByUserId` AS `operationByUserId`,
  22.   `lms_finance`.`bill`.`operationByUser` AS `operationByUser`,
  23.   `lms_finance`.`bill`.`operationAt` AS `operationAt`,
  24.   `lms_finance`.`bill_batch`.`billBatchName` AS `billBatchName`,
  25.   `view00_student_basic_class`.`name` AS `name`,
  26.   `view00_student_basic_class`.`segment` AS `segment`,
  27.   `view00_student_basic_class`.`gender` AS `gender`,
  28.   `view00_student_basic_class`.`className` AS `className`,
  29.   `view00_student_basic_class`.`level` AS `level`,
  30.   `view00_student_basic_class`.`number` AS `number`,
  31.   `view00_student_basic_class`.`classId` AS `classId`,
  32.   `view00_student_basic_class`.`year` AS `year`,
  33.   `view00_student_basic_class`.`semester` AS `semester`,
  34.   `view00_student_basic_class`.`scholarshipTotal` AS `scholarshipTotal`,
  35.   `view00_student_basic_class`.`scholarshipUsed` AS `scholarshipUsed`,
  36.   `view00_student_basic_class`.`scholarshipUnused` AS `scholarshipUnused`,
  37.   `view00_student_basic_class`.`semesterScholarship` AS `semesterScholarship`,
  38.   `view00_student_basic_class`.`semesterScholarshipUsed` AS `semesterScholarshipUsed`,
  39.   `view00_student_basic_class`.`semesterScholarshipUnused` AS `semesterScholarshipUnused`,
  40.   `view00_student_basic_class`.`afterSchoolCareType` AS `afterSchoolCareType`,
  41.   `view00_student_basic_class`.`freshmanOrReturning` AS `freshmanOrReturning`,
  42.   `lms_finance`.`bill`.`billCompletedStatus` AS `billCompletedStatus`
  43. from
  44.   (
  45.     (
  46.       `lms_finance`.`bill`
  47.       left join `lms_finance`.`bill_batch` on (
  48.         (
  49.           `lms_finance`.`bill_batch`.`billBatchId` = `lms_finance`.`bill`.`billBatchId`
  50.         )
  51.       )
  52.     )
  53.     left join `lms_finance`.`view00_student_basic_class` on (
  54.       (
  55.         `view00_student_basic_class`.`studentId` = `lms_finance`.`bill`.`studentId`
  56.       )
  57.     )
  58.   )

view01_bill_batch

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_bill_batch` AS
  2. select
  3.   `bill_batch`.`id` AS `id`,
  4.   `bill_batch`.`idSequence` AS `idSequence`,
  5.   `bill_batch`.`billBatchId` AS `billBatchId`,
  6.   `bill_batch`.`billBatchName` AS `billBatchName`,
  7.   `bill_batch`.`paymentType` AS `paymentType`,
  8.   `bill_batch`.`paymentConfig` AS `paymentConfig`,
  9.   `bill_batch`.`reductionConfig` AS `reductionConfig`,
  10.   `bill_batch`.`paymentPersonConfig` AS `paymentPersonConfig`,
  11.   `bill_batch`.`totalPayment` AS `totalPayment`,
  12.   `bill_batch`.`remarks` AS `remarks`,
  13.   `bill_batch`.`operation` AS `operation`,
  14.   `bill_batch`.`operationByUserId` AS `operationByUserId`,
  15.   `bill_batch`.`operationByUser` AS `operationByUser`,
  16.   `bill_batch`.`operationAt` AS `operationAt`,
  17.   `bill_batch`.`billBatchStatus` AS `billBatchStatus`,
  18.   count(`bill`.`studentId`) AS `billNumber`
  19. from
  20.   (
  21.     `bill_batch`
  22.     left join `bill` on (
  23.       (`bill_batch`.`billBatchId` = `bill`.`billBatchId`)
  24.     )
  25.   )
  26. group by
  27.   `bill_batch`.`billBatchId`

view01_student_basic

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_student_basic` AS
  2. select
  3.   `lms_student_admin`.`student_basic`.`id` AS `id`,
  4.   `lms_student_admin`.`student_basic`.`enrollmentYear` AS `enrollmentYear`,
  5.   `lms_student_admin`.`student_basic`.`enrollmentSemester` AS `enrollmentSemester`,
  6.   `lms_student_admin`.`student_basic`.`enrollmentDate` AS `enrollmentDate`,
  7.   `lms_student_admin`.`student_basic`.`studentId` AS `studentId`,
  8.   `lms_student_admin`.`student_basic`.`classId` AS `classId`,
  9.   `lms_student_admin`.`student_basic`.`idSequence` AS `idSequence`,
  10.   `lms_student_admin`.`student_basic`.`enrollmentSegment` AS `enrollmentSegment`,
  11.   `lms_student_admin`.`student_basic`.`enrollmentLevel` AS `enrollmentLevel`,
  12.   `lms_student_admin`.`student_basic`.`enrollmentClassId` AS `enrollmentClassId`,
  13.   `lms_student_admin`.`student_basic`.`name` AS `name`,
  14.   `lms_student_admin`.`student_basic`.`gender` AS `gender`,
  15.   `lms_student_admin`.`student_basic`.`studentFeeStatus` AS `studentFeeStatus`,
  16.   `lms_student_admin`.`student_basic`.`studentStatus` AS `studentStatus`,
  17.   `lms_student_admin`.`student_basic`.`icNumber` AS `icNumber`,
  18.   `lms_student_admin`.`student_basic`.`scholarshipLevel` AS `scholarshipLevel`,
  19.   `lms_student_admin`.`student_basic`.`requestType` AS `requestType`,
  20.   `lms_student_admin`.`student_basic`.`afterSchoolCareType` AS `afterSchoolCareType`,
  21.   `lms_student_admin`.`student_basic`.`residentialAddress` AS `residentialAddress`,
  22.   `lms_student_admin`.`student_basic`.`guardian1Name` AS `guardian1Name`,
  23.   `lms_student_admin`.`student_basic`.`guardian1Relationship` AS `guardian1Relationship`,
  24.   `lms_student_admin`.`student_basic`.`guardian1Contact` AS `guardian1Contact`,
  25.   `lms_student_admin`.`student_basic`.`guardian1ResidentialAddress` AS `guardian1ResidentialAddress`,
  26.   `lms_student_admin`.`student_basic`.`guardian2Name` AS `guardian2Name`,
  27.   `lms_student_admin`.`student_basic`.`guardian2Relationship` AS `guardian2Relationship`,
  28.   `lms_student_admin`.`student_basic`.`guardian2Contact` AS `guardian2Contact`,
  29.   `lms_student_admin`.`student_basic`.`guardian2ResidentialAddress` AS `guardian2ResidentialAddress`,
  30.   `lms_student_admin`.`student_basic`.`remarks` AS `remarks`,
  31.   `lms_student_admin`.`student_basic`.`newRemarks` AS `newRemarks`,
  32.   `lms_student_admin`.`student_basic`.`hostelName` AS `hostelName`,
  33.   `lms_student_admin`.`student_basic`.`hostelUnitNumber` AS `hostelUnitNumber`,
  34.   `lms_student_admin`.`student_basic`.`hostelBedId` AS `hostelBedId`,
  35.   `lms_student_admin`.`student_basic`.`dining_canteen_table` AS `dining_canteen_table`,
  36.   `lms_student_admin`.`student_basic`.`completedEnrollmentRecordYesOrNo` AS `completedEnrollmentRecordYesOrNo`,
  37.   `lms_student_admin`.`student_basic`.`completedClassAssignedYesOrNo` AS `completedClassAssignedYesOrNo`,
  38.   `lms_student_admin`.`student_basic`.`completedLogisticsAssignedYesOrNo` AS `completedLogisticsAssignedYesOrNo`,
  39.   `lms_student_admin`.`student_basic`.`completedPaymentYesOrNo` AS `completedPaymentYesOrNo`,
  40.   `lms_student_admin`.`student_basic`.`completedEnrollmentYesOrNo` AS `completedEnrollmentYesOrNo`,
  41.   `lms_student_admin`.`student_basic`.`completedQuitYesOrNo` AS `completedQuitYesOrNo`,
  42.   `lms_student_admin`.`student_basic`.`compeletedUpgradeYesOrNo` AS `compeletedUpgradeYesOrNo`,
  43.   `lms_student_admin`.`student_basic`.`enrolledBy` AS `enrolledBy`,
  44.   `lms_student_admin`.`student_basic`.`classAssignedBy` AS `classAssignedBy`,
  45.   `lms_student_admin`.`student_basic`.`paymentReceivedBy` AS `paymentReceivedBy`,
  46.   `lms_student_admin`.`student_basic`.`checkPaymentStatusBy` AS `checkPaymentStatusBy`,
  47.   `lms_student_admin`.`student_basic`.`hostelAssignedBy` AS `hostelAssignedBy`,
  48.   `lms_student_admin`.`student_basic`.`enrollmentApprovedBy` AS `enrollmentApprovedBy`,
  49.   `lms_student_admin`.`student_basic`.`quitApprovedBy` AS `quitApprovedBy`,
  50.   `lms_student_admin`.`student_basic`.`operation` AS `operation`,
  51.   `lms_student_admin`.`student_basic`.`operationAt` AS `operationAt`,
  52.   `lms_student_admin`.`student_basic`.`operationByUserId` AS `operationByUserId`,
  53.   `lms_student_admin`.`student_basic`.`operationByUser` AS `operationByUser`,
  54.   `lms_student_admin`.`student_basic`.`completedQuitAssignedYesOrNo` AS `completedQuitAssignedYesOrNo`,
  55.   `lms_student_admin`.`student_basic`.`quitHostelAssignedBy` AS `quitHostelAssignedBy`,
  56.   `lms_student_admin`.`student_basic`.`completedQuitPaymentYesOrNo` AS `completedQuitPaymentYesOrNo`,
  57.   `lms_student_admin`.`student_basic`.`quitPaymentBy` AS `quitPaymentBy`,
  58.   `lms_student_admin`.`student_basic`.`middleSchoolExamScore` AS `middleSchoolExamScore`,
  59.   `lms_student_admin`.`student_basic`.`middleSchoolExamSequence` AS `middleSchoolExamSequence`
  60. from
  61.   `lms_student_admin`.`student_basic`

view01_student_scholarship

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_student_scholarship` AS
  2. select
  3.   `lms_finance`.`student_scholarship`.`id` AS `id`,
  4.   `lms_finance`.`student_scholarship`.`studentId` AS `studentId`,
  5.   `lms_finance`.`student_scholarship`.`scholarshipTotal` AS `scholarshipTotal`,
  6.   `lms_finance`.`student_scholarship`.`scholarshipUnused` AS `scholarshipUnused`,
  7.   `lms_finance`.`student_scholarship`.`scholarshipUsed` AS `scholarshipUsed`,
  8.   `lms_finance`.`student_scholarship`.`operation` AS `operation`,
  9.   `lms_finance`.`student_scholarship`.`operationByUserId` AS `operationByUserId`,
  10.   `lms_finance`.`student_scholarship`.`operationByUser` AS `operationByUser`,
  11.   `lms_finance`.`student_scholarship`.`operationAt` AS `operationAt`,
  12.   `view00_student_basic_class`.`name` AS `name`,
  13.   `view00_student_basic_class`.`segment` AS `segment`,
  14.   `view00_student_basic_class`.`gender` AS `gender`,
  15.   `view00_student_basic_class`.`className` AS `className`,
  16.   `view00_student_basic_class`.`level` AS `level`,
  17.   `view00_student_basic_class`.`year` AS `year`,
  18.   `view00_student_basic_class`.`semester` AS `semester`,
  19.   `view00_student_basic_class`.`classId` AS `classId`,
  20.   `view00_student_basic_class`.`studentStatus` AS `studentStatus`,
  21.   `lms_finance`.`student_semester_scholarship`.`semesterScholarship` AS `semesterScholarship`,
  22.   `lms_finance`.`student_semester_scholarship`.`semesterScholarshipUsed` AS `semesterScholarshipUsed`,
  23.   `lms_finance`.`student_semester_scholarship`.`semesterScholarshipUnused` AS `semesterScholarshipUnused`
  24. from
  25.   (
  26.     (
  27.       `lms_finance`.`student_scholarship`
  28.       left join `lms_finance`.`view00_student_basic_class` on (
  29.         (
  30.           `view00_student_basic_class`.`studentId` = `lms_finance`.`student_scholarship`.`studentId`
  31.         )
  32.       )
  33.     )
  34.     left join `lms_finance`.`student_semester_scholarship` on (
  35.       (
  36.         (
  37.           `view00_student_basic_class`.`year` = `lms_finance`.`student_semester_scholarship`.`year`
  38.         )
  39.         and (
  40.           `view00_student_basic_class`.`semester` = `lms_finance`.`student_semester_scholarship`.`semester`
  41.         )
  42.         and (
  43.           `lms_finance`.`student_scholarship`.`studentId` = `lms_finance`.`student_semester_scholarship`.`studentId`
  44.         )
  45.       )
  46.     )
  47.   )
  48. group by
  49.   `lms_finance`.`student_scholarship`.`id`

view01_student_scholarship_record

  1. CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_student_scholarship_record` AS
  2. select
  3.   `lms_finance`.`student_scholarship_record`.`id` AS `id`,
  4.   `lms_finance`.`student_scholarship_record`.`idSequence` AS `idSequence`,
  5.   `lms_finance`.`student_scholarship_record`.`scholarshipRecordId` AS `scholarshipRecordId`,
  6.   `lms_finance`.`student_scholarship_record`.`year` AS `year`,
  7.   `lms_finance`.`student_scholarship_record`.`semester` AS `semester`,
  8.   `lms_finance`.`student_scholarship_record`.`studentId` AS `studentId`,
  9.   `lms_finance`.`student_scholarship_record`.`transactionType` AS `transactionType`,
  10.   `lms_finance`.`student_scholarship_record`.`transactionAmount` AS `transactionAmount`,
  11.   `lms_finance`.`student_scholarship_record`.`transactionAt` AS `transactionAt`,
  12.   `lms_finance`.`student_scholarship_record`.`remarks` AS `remarks`,
  13.   `lms_finance`.`student_scholarship_record`.`operation` AS `operation`,
  14.   `lms_finance`.`student_scholarship_record`.`operationByUserId` AS `operationByUserId`,
  15.   `lms_finance`.`student_scholarship_record`.`operationByUser` AS `operationByUser`,
  16.   `lms_finance`.`student_scholarship_record`.`operationAt` AS `operationAt`,
  17.   `view00_student_basic_class`.`name` AS `name`,
  18.   `view00_student_basic_class`.`segment` AS `segment`,
  19.   `view00_student_basic_class`.`gender` AS `gender`,
  20.   `view00_student_basic_class`.`className` AS `className`,
  21.   `view00_student_basic_class`.`level` AS `level`
  22. from
  23.   (
  24.     `lms_finance`.`student_scholarship_record`
  25.     left join `lms_finance`.`view00_student_basic_class` on (
  26.       (
  27.         `view00_student_basic_class`.`studentId` = `lms_finance`.`student_scholarship_record`.`studentId`
  28.       )
  29.     )
  30.   )