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商家',
  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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 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=utf8 comment = '门牌信息表';