hust-epi/whepi-doc/login.sql

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
drop table if exists sys_user;
create table sys_user
(
  user_id             BIGINT          not null             comment '用户编号',
  login_name          VARCHAR(50)     not null default ''  comment '登录账户',
  login_pwd           VARCHAR(50)     not null default '123456'  comment '登录密码',
  user_type           VARCHAR(1)      not null             comment '角色ID:1管理员,2业委会,3居民,4巡逻检查人员',
  staff_name          VARCHAR(20)     not null             comment '用户姓名',

  be_active           CHAR(1)         not null default 'Y' comment '是否活动 Y=活动/N=禁用/D=删除',
  create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP   COMMENT '建立时间',
  update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
  primary key ( user_id ),
  UNIQUE INDEX `login_name`(`login_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '用户资料';

-- insert into sys_user
-- select ur.user_id,'账户','密码',ur.role_id as user_type,uo.nick_name,'Y',now(),now() from sys_uptown_home uh
-- left join sys_user_role ur on ur.property_id = uh.house_id
-- left join sys_user_open uo on uo.user_id = ur.user_id
-- where uh.phone = "电话号码"

drop table if exists sys_user_open;
create table sys_user_open
(
  user_id             BIGINT          not null             comment '用户编号',
  open_id             VARCHAR(100)    not null             comment 'wx编号',
  nick_name           VARCHAR(100)    null                 comment '用户昵称',
  sex_desc            VARCHAR(10)     null                 comment '用户性别',
  sex                 int             null                 comment '用户性别',
  city                VARCHAR(50)     null                 comment '用户城市',
  province            VARCHAR(50)     null                 comment '用户省份',
  country             VARCHAR(50)     null                 comment '用户国家',
  head_img_url        VARCHAR(200)    null                 comment '用户头像地址',

  be_active           CHAR(1)         not null default 'Y' comment '是否活动 Y=活动/N=禁用/D=删除',
  create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
  update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
  primary key ( user_id, open_id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '用户open';

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role  (
  ur_id 		bigint(20) NOT NULL COMMENT '用户角色关系ID',
  user_id 		bigint(20) NOT NULL COMMENT '用户ID',
  role_id 		int(11) NOT NULL COMMENT '角色ID:1居民,2业委会,3商家',
  property_id   bigint(20) NOT NULL DEFAULT 0 COMMENT '属性ID,居民门牌ID,业委会ID',
  user_create 	bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create 	datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update 	bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update 	datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (ur_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '用户角色关系表';

-- ----------------------------
-- Table structure for sys_owner
-- ----------------------------
DROP TABLE IF EXISTS sys_owner;
CREATE TABLE sys_owner  (
  owner_id          bigint(20) NOT NULL DEFAULT 0 COMMENT '业委会ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID,下拉选ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  user_create       bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update       bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (owner_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '业委会表';

-- ----------------------------
-- Table structure for sys_owner_linkman
-- ----------------------------
DROP TABLE IF EXISTS sys_owner_linkman;
CREATE TABLE sys_owner_linkman  (
  linkman_id        bigint(20) NOT NULL DEFAULT 0 COMMENT '业委会联系人ID',
  owner_id          bigint(20) NOT NULL DEFAULT 0 COMMENT '业委会ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  user_create       bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update       bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (owner_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '业委会联系人表';

-- ----------------------------
-- Table structure for sys_uptown_home
-- ----------------------------
DROP TABLE IF EXISTS sys_uptown_home;
CREATE TABLE sys_uptown_home  (
  house_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '门牌ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  young             int(11) NOT NULL DEFAULT 0 COMMENT '家庭年前人数',
  middle            int(11) NOT NULL DEFAULT 0 COMMENT '家庭中年人数',
  old               int(11) NOT NULL DEFAULT 0 COMMENT '家庭老年人数',
  remark            varchar(100)  NOT NULL DEFAULT '' COMMENT '备注',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (house_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '小区家庭表';

-- ----------------------------
-- Table structure for sys_area
-- ----------------------------
DROP TABLE IF EXISTS sys_area;
CREATE TABLE sys_area  (
  area_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '地区ID',
  province          varchar(50)  NOT NULL DEFAULT '' COMMENT '省',
  city              varchar(50)  NOT NULL DEFAULT '' COMMENT '市',
  area              varchar(50)  NOT NULL DEFAULT '' COMMENT '区',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (area_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '省市区表';

-- ----------------------------
-- Table structure for sys_area_street
-- ----------------------------
DROP TABLE IF EXISTS sys_area_street;
CREATE TABLE sys_area_street  (
  street_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '街道ID',
  area_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '地区ID',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (street_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '区街道表';

-- ----------------------------
-- Table structure for sys_area_com
-- ----------------------------
DROP TABLE IF EXISTS sys_area_com;
CREATE TABLE sys_area_com  (
  com_id            bigint(20) NOT NULL DEFAULT 0 COMMENT '社区ID',
  street_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '街道ID',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (com_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '街道社区表';

-- ----------------------------
-- Table structure for sys_uptown
-- ----------------------------
DROP TABLE IF EXISTS sys_uptown;
CREATE TABLE sys_uptown  (
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID',
  uptown_name       varchar(20)  NOT NULL DEFAULT '' COMMENT '小区名称',
  uptown_addr       varchar(100)  NOT NULL DEFAULT '' COMMENT '小区地址',
  longitude         varchar(20)  NOT NULL DEFAULT '' COMMENT '经度',
  latitude          varchar(20)  NOT NULL DEFAULT '' COMMENT '纬度',
  area              int(11)  NOT NULL DEFAULT '0' COMMENT '区',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (uptown_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '小区表';

DROP TABLE IF EXISTS sys_uptown_unit;
CREATE TABLE sys_uptown_unit  (
  unit_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '单元ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID',
  ridgepole         varchar(20) NOT NULL DEFAULT '' COMMENT '栋',
  unit              varchar(20) NOT NULL DEFAULT '' COMMENT '单元',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (unit_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '栋,单元信息表';

DROP TABLE IF EXISTS sys_uptown_house;
CREATE TABLE sys_uptown_house  (
  house_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '门牌ID',
  unit_id          bigint(20) NOT NULL DEFAULT 0 COMMENT '单元ID',
  doorplate         varchar(20)  NOT NULL DEFAULT '' COMMENT '门牌',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (house_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '门牌信息表';

DROP TABLE IF EXISTS out_scan;
CREATE TABLE out_scan  (
  scan_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '扫码ID',
  user_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '人员ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID,下拉选ID',
  door_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '小区大门ID',
  in_type           int(11) NOT NULL DEFAULT 0 COMMENT '进出状态:1进,2出',
  day_date          date NOT NULL DEFAULT '0000-01-01' COMMENT '当天时间',
  date              datetime(0) NOT NULL DEFAULT '0000-01-01 00:00:00' COMMENT '进出时间',
  go_status         int(11) NOT NULL DEFAULT 0 COMMENT '通行状态:1允许,-1禁止',
  error_info        varchar(100)  NOT NULL DEFAULT '' COMMENT '错误信息',
  user_create       bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update       bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (scan_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '用户进出扫码信息';

DROP TABLE IF EXISTS out_user;
CREATE TABLE out_user  (
  tu_id             bigint(20) NOT NULL DEFAULT 0 COMMENT '特殊ID',
  name              varchar(100)  NOT NULL DEFAULT '' COMMENT '特殊人员姓名',
  phone             bigint(20) NOT NULL DEFAULT 0 COMMENT '电话号码',
  start_date        date NOT NULL DEFAULT '0000-01-01' COMMENT '开始时间',
  end_date          date NOT NULL DEFAULT '0000-01-01' COMMENT '结束时间',
  time              int(11) NOT NULL DEFAULT 0 COMMENT '总共时间(天)',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (tu_id) USING BTREE,
  UNIQUE INDEX phone(phone) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '进出扫码特殊人员';

DROP TABLE IF EXISTS out_scan_estate;
CREATE TABLE out_scan_estate  (
  se_id             bigint(20) NOT NULL DEFAULT 0 COMMENT '扫码ID',
  user_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '人员ID',
  day_date          date NOT NULL DEFAULT '0000-01-01' COMMENT '当天时间',
  scan_date         datetime(0) NOT NULL DEFAULT '0000-01-01 00:00:00' COMMENT '扫码时间',
  go_status         int(11) NOT NULL DEFAULT 0 COMMENT '通行状态:1允许,-1禁止',
  error_info        varchar(100)  NOT NULL DEFAULT '' COMMENT '错误信息',
  user_create       bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update       bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (se_id) USING BTREE,
  INDEX user_id(user_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '校园公共区域巡检扫码信息';

DROP TABLE IF EXISTS out_scan_conf;
CREATE TABLE out_scan_conf  (
  sc_id        		bigint(20) NOT NULL DEFAULT 0 COMMENT '扫码配置ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID,下拉选ID',
  go_out_fre        int(11) NOT NULL DEFAULT 0 COMMENT '出门频率',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (sc_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '用户外出扫码配置';

DROP TABLE IF EXISTS sys_uptown_door;
CREATE TABLE sys_uptown_door  (
  door_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '小区大门ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID',
  door_name         varchar(20)  NOT NULL DEFAULT '' COMMENT '大门名称',
  in_img            varchar(100)  NOT NULL DEFAULT '' COMMENT '入口二维码',
  out_img           varchar(100)  NOT NULL DEFAULT '' COMMENT '出口二维码',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (door_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '小区大门表';