123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219 |
- SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
- SET AUTOCOMMIT = 0;
- START TRANSACTION;
- SET time_zone = "+00:00";
- --
- -- 表的结构 `__PREFIX__csmadmin_admin`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_admin` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `faadmin_id` int(10) unsigned NOT NULL COMMENT '管理员ID',
- -- `mobile` varchar(100) NOT NULL DEFAULT '' COMMENT '手机号',
- -- `mobileactive` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT '手机激活状态:Y=激活,N=未激活',
- `emailactive` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT '邮箱激活状态:Y=激活,N=未激活',
- `wxuserid` varchar(100) DEFAULT NULL COMMENT '微信ID',
- `wxusername` varchar(100) DEFAULT NULL COMMENT '微信姓名',
- `wxsex` varchar(10) DEFAULT NULL COMMENT '微信性别',
- `wxcountry` varchar(10) DEFAULT NULL COMMENT '微信国家',
- `wxprovince` varchar(20) DEFAULT NULL COMMENT '微信省份',
- `wxcity` varchar(10) DEFAULT NULL COMMENT '微信城市',
- `wxheadimgurl` varchar(255) DEFAULT NULL COMMENT '微信头像',
- `dduserid` varchar(100) DEFAULT NULL COMMENT '钉钉ID[备用字段]',
- `ddusername` varchar(100) DEFAULT NULL COMMENT '钉钉姓名[备用字段]',
- `updatepsdtime` int(11) DEFAULT NULL COMMENT '修改密码时间',
- `lastloginip` varchar(20) DEFAULT NULL COMMENT '上次登录的IP',
- `lastlogincountry` varchar(20) DEFAULT NULL COMMENT '上次登录国家',
- `lastloginprovince` varchar(20) DEFAULT NULL COMMENT '上次登录省份',
- `lastlogincity` varchar(20) DEFAULT NULL COMMENT '上次登录城市',
- `status` enum('normal','hidden') NOT NULL DEFAULT 'normal' COMMENT '状态',
- `createtime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
- `updatetime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新时间',
- `b4` varchar(100) DEFAULT NULL COMMENT '备用字段4',
- `b5` varchar(100) DEFAULT NULL COMMENT '备用字段5',
- `b6` varchar(100) DEFAULT NULL COMMENT '备用字段6',
- `b7` varchar(100) DEFAULT NULL COMMENT '备用字段7',
- `b8` varchar(100) DEFAULT NULL COMMENT '备用字段8',
- `b9` varchar(100) DEFAULT NULL COMMENT '备用字段9',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员扩展表'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_adminapply`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_adminapply` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `applytype` enum('email','mobile','wx','dd') DEFAULT NULL COMMENT '申请类型:email=邮箱,mobile=手机,wx=微信,dd=钉钉',
- `username` varchar(100) NOT NULL DEFAULT '' COMMENT '用户名',
- `nickname` varchar(100) NOT NULL DEFAULT '' COMMENT '昵称',
- `password` varchar(32) NOT NULL DEFAULT '' COMMENT '密码',
- `salt` varchar(30) NOT NULL DEFAULT '' COMMENT '密码盐',
- `avatar` varchar(255) DEFAULT '' COMMENT '头像',
- `email` varchar(100) DEFAULT NULL COMMENT '电子邮箱',
- `mobile` varchar(100) DEFAULT NULL COMMENT '手机',
- `wxuserid` varchar(100) DEFAULT NULL COMMENT '微信ID',
- `wxusername` varchar(100) DEFAULT NULL COMMENT '微信姓名',
- `wxsex` varchar(10) DEFAULT NULL COMMENT '微信性别',
- `wxcountry` varchar(10) DEFAULT NULL COMMENT '微信国家',
- `wxprovince` varchar(20) DEFAULT NULL COMMENT '微信省份',
- `wxcity` varchar(10) DEFAULT NULL COMMENT '微信城市',
- `wxheadimgurl` varchar(255) DEFAULT NULL COMMENT '微信头像',
- `dduserid` varchar(100) DEFAULT NULL COMMENT '钉钉ID[备用字段]',
- `ddusername` varchar(100) DEFAULT NULL COMMENT '钉钉姓名[备用字段]',
- `applyip` varchar(20) DEFAULT NULL COMMENT '申请人IP',
- `applycountry` varchar(20) DEFAULT NULL COMMENT '申请人国家',
- `applyprovince` varchar(20) DEFAULT NULL COMMENT '申请人省份',
- `applycity` varchar(20) DEFAULT NULL COMMENT '申请人城市',
- `description` text DEFAULT NULL COMMENT '备注',
- `faadmin_id` int(10) unsigned DEFAULT 0 COMMENT '管理员ID',
- `auth_group_ids` varchar(100) DEFAULT NULL COMMENT '对应管理员角色',
- `applytime` int(10) DEFAULT NULL COMMENT '申请时间',
- `audittime` int(10) DEFAULT NULL COMMENT '审核时间',
- `auditstatus` enum('-1','0','1','-2') NOT NULL DEFAULT '0' COMMENT '审核状态:-2=申请撤销,-1=审核退回,0=待审核,1=审核通过',
- `auditreturn` varchar(255) DEFAULT NULL COMMENT '审核退回原因',
- `audituser_id` varchar(100) DEFAULT NULL COMMENT '审核人',
- `audituser` varchar(100) DEFAULT NULL COMMENT '审核人姓名',
- `createtime` int(10) DEFAULT NULL COMMENT '创建时间',
- `updatetime` int(10) DEFAULT NULL COMMENT '更新时间',
- `b1` varchar(100) DEFAULT NULL COMMENT '备用字段1',
- `b2` varchar(100) DEFAULT NULL COMMENT '备用字段2',
- `b3` varchar(100) DEFAULT NULL COMMENT '备用字段3',
- `b4` varchar(100) DEFAULT NULL COMMENT '备用字段4',
- `b5` varchar(100) DEFAULT NULL COMMENT '备用字段5',
- `b6` varchar(100) DEFAULT NULL COMMENT '备用字段6',
- `b7` varchar(100) DEFAULT NULL COMMENT '备用字段7',
- `b8` varchar(100) DEFAULT NULL COMMENT '备用字段8',
- `b9` varchar(100) DEFAULT NULL COMMENT '备用字段9',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员注册申请表'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_loginlog`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_loginlog` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `logintype` enum('A','M','E','W','D') NOT NULL DEFAULT 'A' COMMENT '登录方式:A=帐号密码,M=手机短信,E=邮件短信,W=微信,D=钉钉',
- `faadmin_id` int(10) unsigned NOT NULL COMMENT '管理员ID',
- `loginip` varchar(20) DEFAULT NULL COMMENT '上次登录的IP',
- `logincountry` varchar(20) DEFAULT NULL COMMENT '上次登录国家',
- `loginprovince` varchar(20) DEFAULT NULL COMMENT '上次登录省份',
- `logincity` varchar(20) DEFAULT NULL COMMENT '上次登录城市',
- `status` enum('normal','hidden') NOT NULL DEFAULT 'normal' COMMENT '状态',
- `createtime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
- `updatetime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新时间',
- `b4` varchar(100) DEFAULT NULL COMMENT '备用字段4',
- `b5` varchar(100) DEFAULT NULL COMMENT '备用字段5',
- `b6` varchar(100) DEFAULT NULL COMMENT '备用字段6',
- `b7` varchar(100) DEFAULT NULL COMMENT '备用字段7',
- `b8` varchar(100) DEFAULT NULL COMMENT '备用字段8',
- `b9` varchar(100) DEFAULT NULL COMMENT '备用字段9',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员扩展表'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_wxscan`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_wxscan` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `scene_id` varchar(20) NOT NULL COMMENT 'SCENE',
- `openid` varchar(100) DEFAULT NULL COMMENT 'OpenID',
- `username` varchar(100) DEFAULT NULL COMMENT '用户名',
- `event` varchar(100) DEFAULT NULL COMMENT '事件:subscribe,scan',
- `createtime` int(10) DEFAULT 0 COMMENT '创建时间',
- `updatetime` int(10) DEFAULT 0 COMMENT '更新时间',
- PRIMARY KEY (`id`),
- KEY `csmadmin_wxscan_1` (`scene_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='微信扫码记录表'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_depart`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_depart` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `departhead_id` int(10) unsigned DEFAULT NULL COMMENT '部门负责人',
- `parent_id` int(10) unsigned DEFAULT NULL COMMENT '父组织',
- `name` varchar(100) NOT NULL COMMENT '部门名称',
- `root_id` int(10) DEFAULT 0 COMMENT '根节点',
- `description` varchar(255) DEFAULT NULL COMMENT '描述',
- `weigh` int(10) NOT NULL DEFAULT 0 COMMENT '部门排序',
- `fromuuid` varchar(100) DEFAULT NULL COMMENT '来源',
- `fromsys` enum('dd','wx','fs') DEFAULT NULL COMMENT '来源系统:dd=钉钉,wx=微信,fs=飞书',
- `status` enum('normal','hidden') NOT NULL DEFAULT 'normal' COMMENT '状态',
- `createtime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
- `updatetime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新时间',
- `b4` varchar(100) DEFAULT NULL COMMENT '备用字段4',
- `b5` varchar(100) DEFAULT NULL COMMENT '备用字段5',
- `b6` varchar(100) DEFAULT NULL COMMENT '备用字段6',
- `b7` varchar(100) DEFAULT NULL COMMENT '备用字段7',
- `b8` varchar(100) DEFAULT NULL COMMENT '备用字段8',
- `b9` varchar(100) DEFAULT NULL COMMENT '备用字段9',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='部门表'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_depart2user`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_depart2user` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `csmadmin_depart_id` int(10) unsigned DEFAULT NULL COMMENT '所属部门',
- `faadmin_id` int(10) unsigned DEFAULT NULL COMMENT '人员',
- `userposition` varchar(100) DEFAULT '' COMMENT '人员角色',
- `status` enum('normal','hidden') NOT NULL DEFAULT 'normal' COMMENT '状态',
- `createtime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
- `updatetime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新时间',
- `b4` varchar(100) DEFAULT NULL COMMENT '备用字段4',
- `b5` varchar(100) DEFAULT NULL COMMENT '备用字段5',
- `b6` varchar(100) DEFAULT NULL COMMENT '备用字段6',
- `b7` varchar(100) DEFAULT NULL COMMENT '备用字段7',
- `b8` varchar(100) DEFAULT NULL COMMENT '备用字段8',
- `b9` varchar(100) DEFAULT NULL COMMENT '备用字段9',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='人员所在部门表'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_group2admin`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_group2admin` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `auth_group_id` int(10) unsigned DEFAULT NULL COMMENT '角色组',
- `faadmin_id` int(10) unsigned DEFAULT NULL COMMENT '人员',
- `createtime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='角色组授权人员'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_group2depart`
- --
- CREATE TABLE IF NOT EXISTS `__PREFIX__csmadmin_group2depart` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `auth_group_id` int(10) unsigned DEFAULT NULL COMMENT '角色组',
- `csmadmin_depart_id` int(10) unsigned DEFAULT NULL COMMENT '组织',
- `createtime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='角色组授权部门'
- ;
- --
- -- 表的结构 `__PREFIX__csmadmin_auth_group_access`
- --
- CREATE OR REPLACE VIEW `__PREFIX__csmadmin_auth_group_access` AS
- select `b`.`uid` AS `uid`,`b`.`group_id` AS `group_id` from `__PREFIX__auth_group_access` `b`
- union select `t`.`faadmin_id` AS `faadmin_id`,`t`.`auth_group_id` AS `auth_group_id` from `__PREFIX__csmadmin_group2admin` `t`
- union SELECT a2.faadmin_id, a3.auth_group_id FROM `__PREFIX__csmadmin_depart` a1, `__PREFIX__csmadmin_depart2user` a2, `__PREFIX__csmadmin_group2depart` a3 WHERE a1.id = a2.csmadmin_depart_id AND a1.id = a3.csmadmin_depart_id AND a1.status = 'normal';
-
-
- COMMIT;
|