TMRWatchComm/db.sql

/*
SQLyog Ultimate v12.3.1 (64 bit)
MySQL - 10.3.12-MariaDB : Database - heatwatch
*********************************************************************
*/


/*!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 */;


/*Table structure for table `apisql` */

DROP TABLE IF EXISTS `apisql`;

CREATE TABLE `apisql` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sqlstr` text DEFAULT NULL,
  `sqlname` varchar(50) DEFAULT NULL,
  `returetype` varchar(50) DEFAULT 'map',
  `method` varchar(50) DEFAULT 'query',
  `created_on` int(11) DEFAULT 0,
  `modified_on` int(11) DEFAULT 0,
  `deleted_on` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

/*Data for the table `apisql` */

insert  into `apisql`(`id`,`sqlstr`,`sqlname`,`returetype`,`method`,`created_on`,`modified_on`,`deleted_on`) values 

(1,'SELECT `temp`,`humi`,`thi` ,CASE WHEN thi<68 THEN \'无应激\' \r\n WHEN thi<72 THEN \'轻度应激\' WHEN thi<80 THEN \'中度应激\' \r\n WHEN thi<90 THEN \'重度应激\' ELSE \'严重应激\' END   AS title \r\nFROM `temphumi` \r\nWHERE id = (SELECT MAX(id) FROM `temphumi` ) ','GetTempHumi','map','query',0,0,0),

(2,'SELECT MAX(`temp`) max_temp,MIN(`temp`) min_temp,round(AVG(`temp`),2) avg_temp, \r\n		MAX(`humi`) max_humi,MIN(`humi`) min_humi,round(AVG(`humi`),2) avg_humi, \r\n		 	MAX(`thi`) max_thi,MIN(`thi`) min_thi,round(AVG(`thi`),2) avg_thi, \r\n		 	(SELECT MAX(t1.`receivetime`) FROM `temphumi` t1 \r\n		 WHERE thi=(SELECT MAX(`thi`) FROM `temphumi` \r\n		 WHERE `receivetime` > DATE_ADD(NOW(), INTERVAL -24 HOUR))) AS maxtime, \r\n		 	(SELECT MAX(t2.`receivetime`) FROM `temphumi` t2 \r\n		 WHERE thi= (SELECT MIN(`thi`) FROM `temphumi` \r\n		 WHERE `receivetime` > DATE_ADD(NOW(), INTERVAL -324 HOUR))) AS mintime \r\n		 FROM `temphumi`  WHERE `receivetime` > DATE_ADD(NOW(), INTERVAL -324 HOUR)','GetTempHumi24MinMax','map','query',0,0,0),

(3,'SELECT no, MAX(`temp`) max_temp, MAX(`humi`) max_humi, 	MAX(`thi`) max_thi, \r\n			(SELECT DATE_FORMAT(MAX(t1.`receivetime`),\'%H:%i:%s\') FROM `temphumi` t1 \r\n		 WHERE thi= (SELECT MAX(`thi`) FROM `temphumi` \r\n		     WHERE `receivetime` > DATE_ADD(NOW(), INTERVAL -324 HOUR))) AS maxtime \r\n		     FROM `temphumi` WHERE `receivetime` > DATE_ADD(NOW(), INTERVAL -324 HOUR) group by no ','GetTempHumi24MinMaxByNo','map','query',0,0,0),

(4,'SELECT DATE_FORMAT(`receivetime`,\'%m-%d %H:%i:%s\') AS `receivetime`,`temp`,`humi`,`thi`\r\n	            FROM `temphumi` WHERE `receivetime` > DATE_ADD(NOW(), INTERVAL -30 DAY) ORDER BY id','GetTempHumi30dayByNo','map','query',0,0,0),

(5,'SELECT (h_time+1)*2 hours,ROUND(SUM(`opens`)/6) openmins FROM KPTAdmin \r\nWHERE `createtime` > DATE_ADD(NOW(), INTERVAL -324 HOUR) \r\nGROUP BY `h_time`','GetPriByHour','map','query',0,0,0),

(6,'SELECT `eqcode`,ROUND(SUM(`opens`)/6) openmins FROM KPTAdmin \r\nWHERE `createtime` > DATE_ADD(NOW(), INTERVAL -224 HOUR) \r\nGROUP BY `eqcode`','GetPriByEQ','map','query',0,0,0),

(7,'SELECT `eqcode`,ROUND(SUM(`opens`)/6) openmins FROM KPTAdmin \r\nWHERE `createtime` > DATE_ADD(NOW(), INTERVAL -324 HOUR) \r\nGROUP BY `eqcode` ','GetFutureMonthTemp','map','query',0,0,0),

(8,'select intbarid,varbarname as \'爱妃\' from bar','getbar','map','query',0,0,0),

(9,'select intcowid,varcowcode from cow','getcow','map','query',0,0,0),

(10,'insert into applog(varcowcode,objcode,createtime) values(?,?,?)','setapplog','map','insert',0,0,0),

(11,'call aaa','callaaa','map','query',0,0,0),

(12,'select id,deptname,parentid,remark from dept','getdeptall','map','query',0,0,0),

(13,'insert into dept(parentid, deptname, remark) values(?,?,?)','createdept','map','query',0,0,0),

(14,'update dept set parentid= ? , deptname = ? , remark= ? where id = ?','updatedept','map','query',0,0,0),

(15,'delete from dept where id = ?','deletedept','map','query',0,0,0),

(16,'SELECT menu.id, menu.`path`, menu.`redirect`, menu.`component`, \nmenu.`title`,menu.`icon`,menu.`parentId`,menu.`name` FROM `user`,`user_role`,`role_menu`,`menu` \nWHERE user.`username`=? AND user_role.`user_id`=user.`id` AND role_menu.`role_id`=user_role.`role_id` \nAND menu.`id`=role_menu.`menu_id` AND menu.`menutype`=\'menu\' AND menu.`parentId`=? \norder by sort','getmenu','map','query',0,0,0),

(17,'select id value,deptname label,parentid,remark from dept where parentid=?','getdeptrecu','map','query',0,0,0),

(18,'select id,deptname,parentid,remark from dept  where parentid=?','getdeptlistrecu','map','query',0,0,0),

(19,'select id,sqlstr,sqlname from apisql','getapisqlall','map','query',0,0,0),

(20,'update apisql set sqlstr= ? , sqlname = ? where id = ?','updateapisql','map','query',0,0,0),

(21,'delete from apisql where id = ?','deleteapisql','map','query',0,0,0),

(22,'insert into apisql(sqlstr,sqlname) values(?,?)','createapisql','map','query',0,0,0),

(23,'select id,title,parentid from menu where parentid=? and redirect =\'noredirect\'','getMenuListRecu','map','query',0,0,0),

(24,'SELECT id,name,menutype,path,method,icon,redirect,title,parentid,component,sort FROM menu WHERE menutype=\'menu\' AND parentid=? order by sort','getMenuRecu','map','query',0,0,0),

(25,'insert into menu(parentid,name,title,path,component,icon,redirect,sort,menutype,method) values(?,?,?,?,?,?,?,?,\'menu\',\'post\')','createMenu','map','query',0,0,0),

(26,'delete from menu where id=?','deleteMenu','map','query',0,0,0),

(27,'update menu\nset parentid=?,name=?,title=?,\npath=?,component=?,icon=?,redirect=?,sort=? \nwhere id =?','updateMenu','map','query',0,0,0),

(28,'SELECT user.id ,user.username,user.fullname,dept.deptname FROM USER LEFT JOIN dept ON dept.id=user.deptid','getUserAll','map','query',0,0,0),

(29,'insert into user(username, fullname, deptid) values(?,?,?)','createUser','map','query',0,0,0),

(30,'update user set username=? ,fullname=? ,deptid=? where id=?','updateUser','map','query',0,0,0),

(31,'delete from user where id = ?','deleteUser','map','query',0,0,0),

(32,'select id,name from role','getRoleAll','map','query',0,0,0),

(33,'insert into role(name) values(?)','createRole','map','query',0,0,0),

(34,'update role set `name`=? where `id` =?','updateRole','map','query',0,0,0),

(35,'delete from role where id = ?','deleteRole','map','query',0,0,0),

(36,'select role_id from user_role where user_id = ?','getRoleByUser','map','query',0,0,0),

(37,'select menu_id from role_menu where role_id = ?','getMenuByRole','map','query',0,0,0);

/*Table structure for table `dept` */

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptname` varchar(50) DEFAULT NULL,
  `parentid` int(11) DEFAULT -1,
  `remark` varchar(50) DEFAULT NULL,
  `createon` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

insert  into `dept`(`id`,`deptname`,`parentid`,`remark`,`createon`) values 

(39,'总公司',-1,'',NULL),

(40,'销售部',39,'',NULL),

(41,'行政部',39,'',NULL),

(63,'士大夫',39,'',NULL),

(66,'爱上',39,'是',NULL),

(67,'是第三个',39,' ',NULL);

/*Table structure for table `menu` */

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT '名字',
  `menutype` varchar(50) DEFAULT 'api',
  `path` varchar(50) DEFAULT '' COMMENT '访问路径',
  `method` varchar(50) DEFAULT 'post' COMMENT '资源请求方式',
  `icon` varchar(50) DEFAULT '',
  `redirect` varchar(50) DEFAULT 'noredirect',
  `title` varchar(50) DEFAULT '',
  `parentId` int(11) DEFAULT -1,
  `component` varchar(50) DEFAULT 'Layout',
  `sort` int(11) DEFAULT 0,
  `created_on` int(11) unsigned DEFAULT 0 COMMENT '创建时间',
  `modified_on` int(11) unsigned DEFAULT 0 COMMENT '更新时间',
  `deleted_on` int(11) unsigned DEFAULT 0 COMMENT '删除时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

/*Data for the table `menu` */

insert  into `menu`(`id`,`name`,`menutype`,`path`,`method`,`icon`,`redirect`,`title`,`parentId`,`component`,`sort`,`created_on`,`modified_on`,`deleted_on`) values 

(1,'查询所有菜单','api','/api/v1/menus','GET','','noredirect','查询所有菜单',25,'Layout',0,0,0,0),

(2,'查询单个菜单','api','/api/v1/menus/:id','GET','','noredirect','查询单个菜单',25,'Layout',0,0,0,0),

(3,'创建单个菜单','api','/api/v1/menus','POST','','noredirect','创建单个菜单',25,'Layout',0,0,0,0),

(4,'更新单个菜单','api','/api/v1/menus/:id','PUT','','noredirect','更新单个菜单',25,'Layout',0,0,0,0),

(5,'删除单个菜单','api','/api/v1/menus/:id','DELETE','','noredirect','删除单个菜单',25,'Layout',0,0,0,0),

(6,'查询所有用户','api','/api/v1/users','GET','','noredirect','查询所有用户',25,'Layout',0,0,0,0),

(7,'查询单个用户','api','/api/v1/users/:id','GET','','noredirect','查询单个用户',25,'Layout',0,0,0,0),

(8,'创建单个用户','api','/api/v1/users','POST','','noredirect','创建单个用户',25,'Layout',0,0,0,0),

(9,'更新单个用户','api','/api/v1/users/:id','PUT','','noredirect','更新单个用户',25,'Layout',0,0,0,0),

(10,'删除单个用户','api','/api/v1/users/:id','DELETE','','noredirect','删除单个用户',25,'Layout',0,0,0,0),

(11,'查询所有角色','api','/api/v1/roles','GET','','noredirect','查询所有角色',25,'Layout',0,0,0,0),

(12,'查询单个角色','api','/api/v1/roles/:id','GET','','noredirect','查询单个角色',25,'Layout',0,0,0,0),

(13,'创建单个角色','api','/api/v1/roles','POST','','noredirect','创建单个角色',25,'Layout',0,0,0,0),

(14,'更新单个角色','api','/api/v1/roles/:id','PUT','','noredirect','更新单个角色',25,'Layout',0,0,0,0),

(15,'删除单个角色','api','/api/v1/roles/:id','DELETE','','noredirect','删除单个角色',25,'Layout',0,0,0,0),

(16,'登录','api','/auth','GET','','noredirect','登录',25,'Layout',0,0,0,0),

(19,'console','menu','/console','POST','form','noredirect','系统管理',-1,'Layout',0,0,0,0),

(21,'user','menu','user','POST','user','','用户管理',19,'console/user/index',3,0,0,0),

(22,'menu','menu','menu','POST','table','','菜单管理',19,'console/menu/index',1,0,0,0),

(23,'role','menu','role','POST','table','','角色管理',19,'console/role/index',2,0,0,0),

(24,'dict','menu','dict','POST','table','','字典管理',19,'console/dict/index',3,0,0,0),

(26,'dept','menu','dept','POST','user','','部门管理',19,'console/dept/index',4,0,0,0),

(27,'apisql','menu','apisql','post','table','','SQL管理',19,'console/apisql/index',5,0,0,0),

(30,'adf','menu','ad','post','','noredirect','adf',-1,'Layout',0,0,0,0),

(31,'测试','menu','simple','post','form','','测试',19,'customviews/TableView',0,0,0,0);

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT '名字',
  `created_on` int(11) unsigned DEFAULT NULL COMMENT '创建时间',
  `modified_on` int(11) unsigned DEFAULT NULL COMMENT '更新时间',
  `deleted_on` int(11) unsigned DEFAULT 0 COMMENT '删除时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

/*Data for the table `role` */

insert  into `role`(`id`,`name`,`created_on`,`modified_on`,`deleted_on`) values 

(1,'朱红宾',NULL,NULL,0),

(11,'管理员',NULL,NULL,0),

(12,'测试',NULL,NULL,0),

(13,'adsf',NULL,NULL,0),

(18,'舒服的',NULL,NULL,0);

/*Table structure for table `role_menu` */

DROP TABLE IF EXISTS `role_menu`;

CREATE TABLE `role_menu` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) unsigned DEFAULT NULL COMMENT '角色ID',
  `menu_id` int(11) unsigned DEFAULT NULL COMMENT '菜单ID',
  `deleted_on` int(11) unsigned DEFAULT 0 COMMENT '删除时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COMMENT='用户_角色ID_管理';

/*Data for the table `role_menu` */

insert  into `role_menu`(`id`,`role_id`,`menu_id`,`deleted_on`) values 

(1,1,1,0),

(2,1,2,0),

(3,1,3,0),

(4,1,4,0),

(5,1,5,0),

(6,1,6,0),

(7,1,7,0),

(8,1,8,0),

(9,1,9,0),

(10,1,10,0),

(11,1,11,0),

(12,1,12,0),

(13,1,13,0),

(14,1,14,0),

(15,1,15,0),

(17,1,19,0),

(18,1,21,0),

(19,1,22,0),

(20,1,23,0),

(21,1,24,0),

(22,1,25,0),

(23,1,26,0),

(24,1,27,0),

(25,1,31,0);

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT '' COMMENT '账号',
  `fullname` varchar(50) DEFAULT '',
  `deptid` int(11) DEFAULT -1,
  `password` varchar(50) DEFAULT '' COMMENT '密码',
  `created_on` int(11) unsigned DEFAULT NULL COMMENT '创建时间',
  `modified_on` int(11) unsigned DEFAULT NULL COMMENT '更新时间',
  `deleted_on` int(11) unsigned DEFAULT 0 COMMENT '删除时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='用户管理';

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`fullname`,`deptid`,`password`,`created_on`,`modified_on`,`deleted_on`) values 

(1,'admin','管理员',39,'e10adc3949ba59abbe56e057f20f883e',NULL,NULL,0),

(2,'zhuhongbin','',39,'e10adc3949ba59abbe56e057f20f883e',1550642309,1550642309,0);

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL COMMENT '用户ID',
  `role_id` int(11) unsigned DEFAULT NULL COMMENT '角色ID',
  `deleted_on` int(11) unsigned DEFAULT 0 COMMENT '删除时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='用户_角色ID_管理';

/*Data for the table `user_role` */

insert  into `user_role`(`id`,`user_id`,`role_id`,`deleted_on`) values 

(1,1,1,0),

(8,2,1,0),

(26,2,11,0);

/*!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 */;