数据结构

12132

表总览

通用表

• _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

业务表

_app_account

• 帐套表

CREATE TABLE `_app_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appaId` varchar(255)  DEFAULT NULL COMMENT '帐套ID;  可以用缩写 或者 中文名; 不可修改',
  `appaName` varchar(255)  DEFAULT NULL COMMENT '帐套名称;',
  `appaStatus` varchar(255)  DEFAULT NULL COMMENT '账套状态;  账套启用，账套停用',
  `appaType` varchar(255)  DEFAULT '普通账套' COMMENT '账套类型; 模版账套、null',
  `periodIdStart` varchar(255)  DEFAULT NULL COMMENT '账套启用年月;',
  `periodIdEnd` varchar(255)  DEFAULT NULL COMMENT '账套结束年月; 审计专用',
  `appaStandard` varchar(255)  DEFAULT NULL COMMENT '会计准则;',
  `appaIndustry` varchar(255)  DEFAULT NULL COMMENT '所属行业;',
  `appaVATType` varchar(255)  DEFAULT NULL COMMENT '增值税种类; 备用',
  `appaManagerId` varchar(255)  DEFAULT NULL COMMENT '账套负责人ID',
  `appaManagerName` varchar(255)  DEFAULT NULL COMMENT '账套负责人名称',
  `appaMemberIdList` varchar(255)  DEFAULT NULL COMMENT '账套成员IDs',
  `appaMemberList` 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 `appaId_unique` (`appaId`) USING BTREE,
  UNIQUE KEY `appaName_unique` (`appaName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '帐套表'

_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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'ui 施工方案'

audit_project

• 审计-项目列表

CREATE TABLE `audit_project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `isTemplate` varchar(255)  DEFAULT '否' COMMENT '是否是审计模板',
  `auditProjectId` varchar(255)  DEFAULT NULL COMMENT '审计项目; 公司名-202306',
  `auditProjectIdOfTemplate` varchar(255)  DEFAULT NULL COMMENT '使用的审计模板; auditProjectId',
  `auditProjectType` varchar(255)  DEFAULT NULL COMMENT '审计项目类型; 财务审计、税务审计、资产评估',
  `auditStatus` varchar(255)  DEFAULT '审计中' COMMENT '审计状态; 审计中、审定完',
  `auditManagerId` varchar(255)  DEFAULT NULL COMMENT '项目负责人, 审计经理 userId',
  `auditManagerName` varchar(255)  DEFAULT NULL COMMENT '项目负责人, 审计经理',
  `auditMemberIdList` varchar(512)  DEFAULT NULL COMMENT '项目成员Id列表; 用“,”拼接 userId',
  `auditMemberList` text  COMMENT '项目成员信息列表; [{ auditMemberId, auditMemberName }, {}]',
  `auditContent` text  COMMENT '审计分工内容; {}  ',
  `customer` varchar(255)  DEFAULT NULL COMMENT '客户',
  `projectDescription` varchar(255)  DEFAULT NULL COMMENT '项目描述',
  `auditPeriodStartDate` varchar(255)  DEFAULT NULL COMMENT '审计期间-开始日期',
  `auditPeriodEndDate` varchar(255)  DEFAULT NULL COMMENT '审计期间-结束日期',
  `reviewStep` varchar(255)  DEFAULT NULL COMMENT '复合阶段; null 复合1、复合2、复合3',
  `reviewUserId1` varchar(255)  DEFAULT NULL COMMENT '项目审核人 userId 1',
  `reviewUserId2` varchar(255)  DEFAULT NULL COMMENT '项目审核人 userId 2',
  `reviewUserId3` varchar(255)  DEFAULT NULL COMMENT '项目审核人 userId 3',
  `reviewUsername1` varchar(255)  DEFAULT NULL COMMENT '项目审核人 username1 ',
  `reviewUsername2` varchar(255)  DEFAULT NULL COMMENT '项目审核人 username2',
  `reviewUsername3` varchar(255)  DEFAULT NULL COMMENT '项目审核人 username3',
  `auditSymbol` varchar(255)  DEFAULT NULL COMMENT '审计文号',
  `remark` 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 `auditProjectId_unique` (`auditProjectId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 632 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '审计-项目列表'

audit_review

• 审计-项目复核

CREATE TABLE `audit_review` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditProjectId` varchar(255)  DEFAULT NULL COMMENT '审计项目',
  `fileName` varchar(255)  DEFAULT NULL COMMENT '文件名',
  `relativePath` varchar(512)  DEFAULT NULL COMMENT '相对路径',
  `reviewContent1` text  COMMENT '复核意见1',
  `reviewContent2` text  COMMENT '复核意见2',
  `reviewContent3` text  COMMENT '复核意见3',
  `reviewComment1` text  COMMENT '复核意见1',
  `reviewComment2` text  COMMENT '复核意见2',
  `reviewComment3` text  COMMENT '复核意见3',
  `reviewResponse1` text  COMMENT '复核内容1',
  `reviewResponse2` text  COMMENT '复核内容2',
  `reviewResponse3` text  COMMENT '复核内容3',
  `reviewResult1` varchar(255)  DEFAULT NULL COMMENT '复核结果1;复核中、复核完成',
  `reviewResult2` varchar(255)  DEFAULT NULL COMMENT '复核结果2;复核中、复核完成',
  `reviewResult3` varchar(255)  DEFAULT NULL COMMENT '复核结果3;复核中、复核完成',
  `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 `auditProjectId_unique` (`auditProjectId`, `relativePath`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '审计-项目复核'

业务视图

_view02_user_app

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `_view02_user_app` AS
select
  `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` = 'jianghu-audit'
  )

view01_audit_project

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_audit_project` AS
select
  `ap`.`id` AS `id`,
  `ap`.`isTemplate` AS `isTemplate`,
  `appa`.`appaId` AS `financeAppaId`,
  `ap`.`auditProjectId` AS `auditProjectId`,
  `ap`.`auditProjectType` AS `auditProjectType`,
  `ap`.`auditStatus` AS `auditStatus`,
  `ap`.`auditManagerId` AS `auditManagerId`,
  `ap`.`auditManagerName` AS `auditManagerName`,
  `ap`.`auditMemberIdList` AS `auditMemberIdList`,
  `ap`.`auditMemberList` AS `auditMemberList`,
  `ap`.`auditContent` AS `auditContent`,
  `ap`.`remark` AS `remark`,
  `ap`.`operation` AS `operation`,
  `ap`.`operationByUserId` AS `operationByUserId`,
  `ap`.`operationByUser` AS `operationByUser`,
  `ap`.`operationAt` AS `operationAt`,
  `ap`.`customer` AS `customer`,
  `ap`.`projectDescription` AS `projectDescription`,
  `ap`.`auditPeriodStartDate` AS `auditPeriodStartDate`,
  `ap`.`auditPeriodEndDate` AS `auditPeriodEndDate`,
  `ap`.`reviewStep` AS `reviewStep`,
  `ap`.`auditSymbol` AS `auditSymbol`,
  `ap`.`reviewUserId1` AS `reviewUserId1`,
  `ap`.`reviewUserId2` AS `reviewUserId2`,
  `ap`.`reviewUserId3` AS `reviewUserId3`,
  `ap`.`reviewUsername1` AS `reviewUsername1`,
  `ap`.`reviewUsername2` AS `reviewUsername2`,
  `ap`.`reviewUsername3` AS `reviewUsername3`,
  `ap`.`auditProjectIdOfTemplate` AS `auditProjectIdOfTemplate`
from
  (
    `audit_project` `ap`
    left join `_app_account` `appa` on ((`appa`.`appaId` = `ap`.`auditProjectId`))
  )

view01_user_select

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_user_select` AS
select
  `_user`.`id` AS `id`,
  `_user`.`userId` AS `userId`,
  `_user`.`username` AS `username`,
  `_user`.`userStatus` AS `userStatus`,
  `_user`.`userType` AS `userType`
from
  `_user`
where
  (`_user`.`userStatus` = 'active')