/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.17 : Database - face_discern
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`face_discern` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `face_discern`;

/*Table structure for table `t_calling` */

DROP TABLE IF EXISTS `t_calling`;

CREATE TABLE `t_calling` (
  `ID` int(32) NOT NULL COMMENT '签到事件主键',
  `callDT` datetime DEFAULT NULL COMMENT '呼叫时间',
  `callerPersonID` int(32) DEFAULT NULL COMMENT '呼叫人员主键',
  `calleePersonID` int(32) DEFAULT NULL COMMENT '被呼叫人员主键',
  PRIMARY KEY (`ID`),
  KEY `con_calling_callerPersonID` (`callerPersonID`),
  KEY `con_calling_calleePersonID` (`calleePersonID`),
  CONSTRAINT `con_calling_calleePersonID` FOREIGN KEY (`calleePersonID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_calling_callerPersonID` FOREIGN KEY (`callerPersonID`) REFERENCES `t_person` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_calling` */

/*Table structure for table `t_checking` */

DROP TABLE IF EXISTS `t_checking`;

CREATE TABLE `t_checking` (
  `ID` int(32) NOT NULL COMMENT '签到事件主键',
  `statusID` tinyint(2) DEFAULT NULL COMMENT '签到状态',
  `statusDT` datetime DEFAULT NULL COMMENT '签到时间',
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  `placeID` int(32) DEFAULT NULL COMMENT '签到位置',
  PRIMARY KEY (`ID`),
  KEY `con_checking_personID` (`personID`),
  KEY `con_checking_placeID` (`placeID`),
  CONSTRAINT `con_checking_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_checking_placeID` FOREIGN KEY (`placeID`) REFERENCES `t_place` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_checking` */

/*Table structure for table `t_checkingcompany` */

DROP TABLE IF EXISTS `t_checkingcompany`;

CREATE TABLE `t_checkingcompany` (
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  `companyID` int(32) DEFAULT NULL COMMENT '公司主键',
  KEY `con_checkingCompany_personID` (`personID`),
  KEY `con_checkingCompany_registerCompanyID` (`companyID`),
  CONSTRAINT `con_checkingCompany_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_checkingCompany_registerCompanyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_checkingcompany` */

/*Table structure for table `t_company` */

DROP TABLE IF EXISTS `t_company`;

CREATE TABLE `t_company` (
  `ID` int(10) NOT NULL COMMENT '公司主键',
  `NAME` varchar(255) DEFAULT NULL COMMENT '公司名称',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_company` */

/*Table structure for table `t_department` */

DROP TABLE IF EXISTS `t_department`;

CREATE TABLE `t_department` (
  `ID` int(10) NOT NULL COMMENT '部门主键',
  `label` varchar(40) DEFAULT NULL COMMENT '部门名称',
  `labelSpell` char(2) DEFAULT NULL COMMENT '部门首字母',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_department` */

/*Table structure for table `t_employeeface` */

DROP TABLE IF EXISTS `t_employeeface`;

CREATE TABLE `t_employeeface` (
  `ID` int(32) NOT NULL COMMENT '员工图片主键',
  `registerCompanyID` int(32) DEFAULT NULL COMMENT '注册公司主键',
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  `imagePath` varchar(255) DEFAULT NULL COMMENT '图片地址',
  PRIMARY KEY (`ID`),
  KEY `con_employeeFace_personID` (`personID`),
  KEY `con_employeeFace_registerCompanyID` (`registerCompanyID`),
  CONSTRAINT `con_employeeFace_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_employeeFace_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_employeeface` */

/*Table structure for table `t_image` */

DROP TABLE IF EXISTS `t_image`;

CREATE TABLE `t_image` (
  `personID` int(10) DEFAULT NULL COMMENT '人员主键',
  `imageTye` varchar(32) DEFAULT NULL COMMENT '图片类型',
  `imagePath` varchar(255) DEFAULT NULL COMMENT '图片地址',
  KEY `con_image_personID` (`personID`),
  CONSTRAINT `con_image_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_image` */

/*Table structure for table `t_person` */

DROP TABLE IF EXISTS `t_person`;

CREATE TABLE `t_person` (
  `ID` int(10) NOT NULL COMMENT '人员主键',
  `NAME` varchar(40) DEFAULT NULL COMMENT '人员名字',
  `nameSpell` char(2) DEFAULT NULL COMMENT '人员名首字母',
  `gender` tinyint(1) DEFAULT NULL COMMENT '性别',
  `country` char(20) DEFAULT NULL COMMENT '国籍',
  `province` char(20) DEFAULT NULL COMMENT '省',
  `city` char(20) DEFAULT NULL COMMENT '市',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `phone` char(12) DEFAULT NULL COMMENT '座机电话',
  `mobilePhone` char(15) DEFAULT NULL COMMENT '移动电话',
  `identityType` char(40) DEFAULT NULL COMMENT '证件类型',
  `identify` varchar(255) DEFAULT NULL COMMENT '证件编号',
  `companyID` int(10) DEFAULT NULL COMMENT '公司主键',
  `departmentID` int(10) DEFAULT NULL COMMENT '部门主键',
  `postID` int(10) DEFAULT NULL COMMENT '职务主键',
  `remark` varchar(255) DEFAULT NULL COMMENT '人员备注消息',
  `warning` varchar(255) DEFAULT NULL COMMENT '危险人员警告消息',
  PRIMARY KEY (`ID`),
  KEY `con_person_companyID` (`companyID`),
  KEY `con_person_departmentID` (`departmentID`),
  KEY `con_person_postID` (`postID`),
  CONSTRAINT `con_person_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`),
  CONSTRAINT `con_person_departmentID` FOREIGN KEY (`departmentID`) REFERENCES `t_department` (`ID`),
  CONSTRAINT `con_person_postID` FOREIGN KEY (`postID`) REFERENCES `t_post` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_person` */

/*Table structure for table `t_personext` */

DROP TABLE IF EXISTS `t_personext`;

CREATE TABLE `t_personext` (
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  KEY `con_personExt_personID` (`personID`),
  CONSTRAINT `con_personExt_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_personext` */

/*Table structure for table `t_place` */

DROP TABLE IF EXISTS `t_place`;

CREATE TABLE `t_place` (
  `ID` int(10) NOT NULL COMMENT '设备主键',
  `companyID` int(10) DEFAULT NULL COMMENT '公司主键',
  `label` varchar(40) DEFAULT NULL COMMENT '设备地点',
  `authorizationID` varchar(40) DEFAULT NULL COMMENT '设备授权码',
  PRIMARY KEY (`ID`),
  KEY `con_place_companyID` (`companyID`),
  CONSTRAINT `con_place_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_place` */

/*Table structure for table `t_post` */

DROP TABLE IF EXISTS `t_post`;

CREATE TABLE `t_post` (
  `ID` int(10) NOT NULL COMMENT '职位主键',
  `label` varchar(40) DEFAULT NULL COMMENT '职位名称',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_post` */

/*Table structure for table `t_visit` */

DROP TABLE IF EXISTS `t_visit`;

CREATE TABLE `t_visit` (
  `ID` int(32) NOT NULL COMMENT '访问事件主键',
  `visitorPersonID` int(32) DEFAULT NULL COMMENT '访问人员主键',
  `visiteePersonID` int(32) DEFAULT NULL COMMENT '被访问人员主键',
  `matterID` int(32) DEFAULT NULL COMMENT '访问事由主键',
  `remark` varchar(255) DEFAULT NULL COMMENT '访问备注',
  `lastStatus` int(40) DEFAULT NULL COMMENT '最新的访问状态',
  `startTime` datetime DEFAULT NULL COMMENT '来访时间',
  `lastTime` datetime DEFAULT NULL COMMENT '签离时间',
  `visitorTypeLabel` varchar(32) DEFAULT NULL COMMENT '访客类型',
  `warning` varchar(255) DEFAULT NULL COMMENT '当前访问的警告状态',
  PRIMARY KEY (`ID`),
  KEY `con_visit_visitorPersonID` (`visitorPersonID`),
  KEY `con_visit_visiteePersonID` (`visiteePersonID`),
  KEY `con_visit_matterID` (`matterID`),
  CONSTRAINT `con_visit_matterID` FOREIGN KEY (`matterID`) REFERENCES `t_visitmatter` (`ID`),
  CONSTRAINT `con_visit_visiteePersonID` FOREIGN KEY (`visiteePersonID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_visit_visitorPersonID` FOREIGN KEY (`visitorPersonID`) REFERENCES `t_person` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_visit` */

/*Table structure for table `t_visitface` */

DROP TABLE IF EXISTS `t_visitface`;

CREATE TABLE `t_visitface` (
  `ID` int(10) NOT NULL COMMENT '访客图片主键',
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  `imagePath` varchar(255) DEFAULT NULL COMMENT '图片地址',
  PRIMARY KEY (`ID`),
  KEY `con_visitFace_personID` (`personID`),
  CONSTRAINT `con_visitFace_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_visitface` */

/*Table structure for table `t_visiting` */

DROP TABLE IF EXISTS `t_visiting`;

CREATE TABLE `t_visiting` (
  `ID` int(32) NOT NULL COMMENT '访问事件流水ID',
  `visitID` int(32) DEFAULT NULL COMMENT '访问事件ID',
  `statusID` datetime DEFAULT NULL COMMENT '当前访问状态',
  `statusDT` datetime DEFAULT NULL COMMENT '更新状态时间',
  `placeID` int(32) DEFAULT NULL COMMENT '设备位置主键',
  PRIMARY KEY (`ID`),
  KEY `con_visiting_placeID` (`placeID`),
  CONSTRAINT `con_visiting_placeID` FOREIGN KEY (`placeID`) REFERENCES `t_place` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_visiting` */

/*Table structure for table `t_visitmatter` */

DROP TABLE IF EXISTS `t_visitmatter`;

CREATE TABLE `t_visitmatter` (
  `ID` int(32) NOT NULL COMMENT '访问事由主键',
  `companyID` int(32) DEFAULT NULL COMMENT '公司主键',
  `label` varchar(40) DEFAULT NULL COMMENT '访问事由',
  `available` tinyint(1) DEFAULT NULL COMMENT '是否是通用事由',
  PRIMARY KEY (`ID`),
  KEY `con_visitMatter_companyID` (`companyID`),
  CONSTRAINT `con_visitMatter_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_visitmatter` */

/*Table structure for table `t_visitortype` */

DROP TABLE IF EXISTS `t_visitortype`;

CREATE TABLE `t_visitortype` (
  `ID` int(32) NOT NULL COMMENT '访客类型主键',
  `label` varchar(32) DEFAULT NULL COMMENT '访客类型',
  `registerCompanyID` int(32) DEFAULT NULL COMMENT '注册公司主键',
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  PRIMARY KEY (`ID`),
  KEY `con_visitorType_personID` (`personID`),
  KEY `con_visitorType_registerCompanyID` (`registerCompanyID`),
  CONSTRAINT `con_visitorType_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_visitorType_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_visitortype` */

/*Table structure for table `t_visitstat` */

DROP TABLE IF EXISTS `t_visitstat`;

CREATE TABLE `t_visitstat` (
  `personID` int(32) DEFAULT NULL COMMENT '人员主键',
  `registerCompanyID` int(32) DEFAULT NULL COMMENT '注册公司主键',
  `totalCount` int(20) DEFAULT NULL COMMENT '来访公司次数',
  KEY `con_visitStat_personID` (`personID`),
  KEY `con_visitStat_registerCompanyID` (`registerCompanyID`),
  CONSTRAINT `con_visitStat_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`),
  CONSTRAINT `con_visitStat_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_visitstat` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;