--  删除数据库

DROP database xinyuanDB;

--  温控系统数据库;
CREATE DATABASE xinyuanDB ; 

--  USE温控系统;
use xinyuanDB;

--  1 控制器定义(表)（Excel读入或手动修改）
drop table TemConSet;

create table TemConSet(
TemConAddr1 varchar(16),	-- 小区号
TemConAddr2 varchar(10), 	-- 楼号
TemConAddr3 varchar(8), 	-- 房间号
SetAddr varchar (10),         	-- 安装位置 
TemConAddrCode int(2),      -- 温控器地址 ??????????
TemConName int(1),      	-- 名称 
Powers int,                    	-- 功率 
primary key(TemConAddrCode,TemConName)
);

--  2 数据采集时间点(表)，最多48个点
drop table DataGatherTime;

create table DataGatherTime(
-- Num int primary key,                      -- 序号 int,
-- ConcreteTime time                         -- 具体时间点 datetime
	hour tinyint,
	min tinyint,
	primary key(hour,min)
);

--  3 温控器设置日志（表） ：记录设置的所有指令和查询结果
drop table TemConLog;

create table TemConLog(
no int AUTO_INCREMENT primary key,
TemConAddrCode int(2) references TemConSet(TenConAddrCode),      -- 温控器地址 ??????????
TemConName int(1) REFERENCES TemConSet(TemConName),      	-- 温控器名称 
-- Directive int(3),		-- 指令名称
Directive char(10) not null,
-- DirectiveData char(64),		-- 指令数据
DirectiveResult tinyint,	-- 指令结果
DirectiveTime 	datetime,	-- 记录时间
userName varchar(16)
);


--  4 用户操作日志查询(表）
drop table UserOperateLog;

CREATE TABLE UserOperateLog(
uolNo int AUTO_INCREMENT primary key,
uolNotesTime datetime ,          	-- 记录时间 datetime ,
uolIncident varchar(50),         	-- 事件 varchar (10),
uolUserName varchar(10),      	-- 操作用户 varchar(10)
uolResult varchar(10),
uolComment varchar(50)
-- primary key(uolNotesTime,uolUserName) 
);

--  5 计划任务日志（表）
drop table PlanTaskLog;

CREATE TABLE PlanTaskLog(
ptlNo int AUTO_INCREMENT primary key,
ptlTemConAddrCode int,
ptlTemConName int,
ptlType tinyint,          	-- 事件 varchar (10)
ptlResult tinyint,  	        -- 计划任务失败对象 varchar (20),
ptlUserName varchar(16),
ptlTime datetime               		-- 记录时间 datetime,
);

--  6计划任务(表）  ??????
drop table plantask;

CREATE TABLE PlanTask(
ptNo int AUTO_INCREMENT primary key,   	-- 序号
ptTemConAddrCode int(2),
ptTemConName int(1),
ptType tinyint default 2,				-- 启动　半启动　停止
ptMode tinyint default 0, 			-- 0:一次执行 1：每天执行　2:停止
ptUserName varchar(16), 			-- 任务对象 int
ptTime varchar(20)              		-- 执行时间 time,
);

--  7分段计费设置(表）
drop table pricedefinition;

CREATE TABLE PriceDefinition( 
Num int primary key,            -- 序号 
Type int,                    -- 类型  0:谷　　１：平　２：峰
fromHour tinyint,       -- 起始日期 
fromMinute tinyint,
toHour tinyint,
toMinute tinyint,
Day int                          
);

--  8 计费统计(表）
drop table moneystatistics;

create table MoneyStatistics(
-- No int AUTO_INCREMENT primary key,
-- TemConName int(1) REFERENCES TemConSet(TemConName),         -- 名称 
TemConAddr int(2) REFERENCES TemConSet(TemConAddr),       	-- 温度器地址
Date date,                     -- 时间 ????????
-- UnitPrice int,                  -- 单价1
Money0 float,                     -- 费用金额
TimeLong0 int,
Money1 float,                     -- 费用金额
TimeLong1 int,
Money2 float,                     -- 费用金额
TimeLong2 int,
total float,
primary key(temConAddr,date)
);

-- 9 温控器温度记录(表）
drop table temtable;

create table TemTable(
TemConAddr int REFERENCES TemConSet(TemConAddr),             -- 温控器地址 
TemConName int ,            -- 名称 
Temperature0 float,                -- 设置温度 ,
Temperature1 float,                -- 室温 ,
Temperature2 float,                -- 地温 ,
data4 int,            	   -- B4第4数据：工作模式,
data5 int,                	   -- B4第5数据：
data6 int,            	   -- B4第6数据：
data7 int,                	   -- B4第7数据：
B4Time  datetime,		   -- 查询时间
primary key(TemConAddr,TemConName,B4Time)
);


--  11用户表
drop table user;

create table user(
username varchar(16) primary key,  -- 用户名
password varchar(16),  -- 密码
id char(18), -- 身份证号码
tel char(15), -- 电话号码
mobile char(12), -- 手机号码
province char(20),
city char(10),
addr varchar(40), -- 住址
postCode char(30), -- 用为单位
state tinyint   --  0:禁止 1:可用
);


--  12用户组
drop table grouptable;

create table grouptable(
groupname varchar(30) primary key,  -- 组名
groupdesc varchar(20)
); 

--  13用户 与 组的关系
drop table usergroup;

CREATE table usergroup(
username varchar(16) REFERENCES user(username),
groupname varchar(30) references grouptable(groupname),
primary key(username,groupname)
);

--   14权限表
drop table privilege;

create table privilege(
	privilegename varchar(30) primary key,  -- 权限名
	privilegedesc varchar(50)  -- 权限描述
);

insert into privilege values('TCSetPrivilege','设置温控器权限');
insert into privilege values('TCInquiryPrivilege','查询温控器权限');
insert into privilege values('SetParamPrivilege','设置参数权限');
insert into privilege values('DBBackupPrivilege','备份权限');
insert into privilege values('DBRestorePrivilege','恢复权限');
insert into privilege values('UserMgrPrivilege','用户管理权限');





--  15组与权限之间的关系
drop table groupprivilege;

create TABLE groupprivilege(
groupname varchar(30) references grouptable(groupname),
privilegename varchar(30)references privilege(privilegename),
primary key(groupname,privilegename)
);

delete from groupprivilege where groupname not in (select groupname from grouptable);


-- 16 加热器启动状态表
drop table heaterState;

create TABLE heaterState(
no int AUTO_INCREMENT primary key,
TemConAddrCode int(2) ,      -- 温控器地址 ??????????
TemConName int(1) ,      	-- 温控器名称 
state tinyint,			-- 1:启动　0:未启动
DirectiveTime 	datetime,	-- 记录时间
userName varchar(16)
);

-- 17 工作站表
drop table workstation;

create TABLE workstation(
wkCode char(10) primary key,
wkHostId char(12),
wkProvince char(20),
wkCity char(10),
wkAddr char(40),
wkState tinyint
);
-- 缴费状态
-- drop table payment;
create table payment(
TemConAddr int(2) REFERENCES TemConSet(TemConAddr),       	-- 温度器地址
Date date,                     -- 时间 ????????
payedDue float,
payed float,
notpayed float,
state tinyint, -- 0 未缴，1 已缴  2 部分
primary key(TemConAddr)
);
-- 10 参考温度表
drop table workstation;

create TABLE refTemp(
no int,
temfloorL float,
temfloorH float,
temRoomL float,
temRoomH float
);
-- 表1输入
delete from TemConSet;
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','301','主卧',513,1,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','301','厕所',514,1,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','主卧',514,2,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',257,1,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',257,2,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',257,3,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',257,4,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',257,5,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,1,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,2,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,3,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,4,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,5,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,6,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,7,3000);
insert into TemConSet(TemConAddr1,TemConAddr2,TemConAddr3,setaddr,TemConAddrCode,temconname,powers) values('鑫源小区','三单元','302','书房',258,8,3000);


-- 表2输入
insert into  DataGatherTime(num,ConcreteTime) VALUES (1,'1:00:00');
insert into  DataGatherTime(num,ConcreteTime) VALUES (2,'1:30:00');
insert into  DataGatherTime(num,ConcreteTime) VALUES (3,'2:00:00');
insert into  DataGatherTime(num,ConcreteTime) VALUES (4,'2:30:00');

-- 表3输入
insert into TemConLog(no,TemConAddrCode,temconname,Directive,Directivedata,Directiveresult) VALUES(1,2,2,'1','a1','a');
insert into TemConLog(no,TemConAddrCode,temconname,Directive,Directivedata,Directiveresult) VALUES(2,3,2,'2','a2','2');

-- 表12输入




delete from user;
insert into user(username,password,state,province,city,addr,postCode) values("liming","0000",1,'辽宁省','沈阳','鑫源小区','110130');
insert into user(username,password,state,province,city,addr,postCode) values("wangming","0000",1,'辽宁省','沈阳','鑫源小区','110130');
insert into user(username,password,state,province,city,addr,postCode) values("zhangming","0000",1,'辽宁省','沈阳','鑫源小区','110130');

insert into grouptable() VALUES('ServerOp','监控中心操作组');
insert into grouptable() VALUES('ClientOp','客户端操作组');
insert into grouptable() VALUES('GeneralMan','总经理');


INSERT into privilege(privilegename,privilegedesc) values('ServerOp','服务器端操作');
INSERT into privilege(privilegename,privilegedesc) values('ClientOp','客户端操作');
INSERT into privilege(privilegename,privilegedesc) values('ServerView','服务器端查询');
INSERT into privilege(privilegename,privilegedesc) values('ClientView','客户端查询');


INSERT into groupprivilege() values('ServerOp','ServerOp');
INSERT into groupprivilege() values('ClientOP','ClientOp');
INSERT into groupprivilege() values('GeneralMan','ServerView');
INSERT into groupprivilege() values('GeneralMan','ClientView');

INSERT into usergroup() values('liming','ClientOp');
INSERT into usergroup() values('Wangming','GeneralMan');
INSERT into usergroup() values('zhangming','ServerOp');


