device.sql

设备管理系统包含设备管理

CREATE TABLE tbl_user(
  user_id int NOT NULL PRIMARY KEY DEFAULT 0,
  username varchar(20) NULL DEFAULT '',
  password varchar(20) NULL DEFAULT '',
  truename varchar(20) NULL DEFAULT '',
  priv_yhgl bit NOT NULL DEFAULT '0',
  priv_jbzlgl bit NOT NULL DEFAULT '0',
  priv_sbck bit NOT NULL DEFAULT '0',
  priv_sbzlxg bit NOT NULL DEFAULT '0',
  priv_sbdj bit NOT NULL DEFAULT '0',
  priv_sbcz bit NOT NULL DEFAULT '0',
  priv_sbsx bit NOT NULL DEFAULT '0',
  priv_sbtj bit NOT NULL DEFAULT '0'
 )


CREATE TABLE tbl_device (
  device_id int NOT NULL PRIMARY KEY DEFAULT 0,
  netcenter_id varchar(40) NOT NULL default '',
  property_id varchar(40) NOT NULL default '',
  model_id char(2) NOT NULL DEFAULT '00',
  model_name varchar(200) NOT NULL DEFAULT '',
  manufacturer_id char(2) NOT NULL DEFAULT '00',
  manufacturer_name varchar(200) NOT NULL DEFAULT '',
  type_id char(1) NOT NULL DEFAULT '0',
  type_name varchar(100) NOT NULL DEFAULT '',
  sn varchar(255) NOT NULL default '',
  area_id int NOT NULL DEFAULT 0,
  area_name varchar(200) NOT NULL DEFAULT '',
  building_id int NOT NULL DEFAULT 0,
  building_name varchar(200) NOT NULL DEFAULT '',
  room_id int NOT NULL DEFAULT 0,
  room_name varchar(200) NOT NULL DEFAULT '',
  state char(20) NOT NULL default '',
  ip varchar(50) NOT NULL DEFAULT '',
  buyrq varchar(50) NOT NULL DEFAULT '',
  memo varchar(255) NOT NULL DEFAULT ''
) 

CREATE TABLE tbl_device_log (
  log_id int NOT NULL PRIMARY KEY DEFAULT 0,
  device_id int NOT NULL DEFAULT 0,
  log_text varchar(255) NOT NULL default '',
  applicant varchar(20) NOT NULL default '',
  transactor varchar(20) NOT NULL default '',
  logrq varchar(50)  NOT NULL DEFAULT ''
) 

CREATE TABLE tbl_device_register (
  register_id char(8) NOT NULL PRIMARY KEY DEFAULT '',
  desc varchar(255) NOT NULL default '',
  applicant varchar(20) NOT NULL default '',
  transactor varchar(20) NOT NULL default '',
  djrq varchar(50)  NOT NULL DEFAULT ''
) 

CREATE TABLE tbl_device_register_items (
  register_id char(8) NOT NULL DEFAULT '',
  item_id int NOT NULL DEFAULT 0,
  device_id int NOT NULL DEFAULT 0,
  netcenter_id varchar(40) NOT NULL default '',
  property_id varchar(40) NOT NULL default '',
  model_id char(2) NOT NULL DEFAULT '00',
  model_name varchar(200) NOT NULL DEFAULT '',
  manufacturer_id char(2) NOT NULL DEFAULT '00',
  manufacturer_name varchar(200) NOT NULL DEFAULT '',
  type_id char(1) NOT NULL DEFAULT '0',
  type_name varchar(100) NOT NULL DEFAULT '',
  sn varchar(255) NOT NULL default ''
) 

CREATE TABLE tbl_device_oper (
  oper_id char(8) NOT NULL PRIMARY KEY DEFAULT '',
  oper_desc varchar(255) NOT NULL default '',
  operrq varchar(50)  NOT NULL DEFAULT '',
  oper_type varchar(255) NOT NULL default '',
  applicant varchar(20) NOT NULL default '',
  transactor varchar(20) NOT NULL default '',
  memo varchar(255) NOT NULL default ''
) 

CREATE TABLE tbl_device_oper_items (
  oper_id char(8) NOT NULL DEFAULT '',
  item_id int NOT NULL DEFAULT 0,
  device_id int NOT NULL DEFAULT 0,
  netcenter_id varchar(40) NOT NULL default '',
  property_id varchar(40) NOT NULL default '',
  model_id char(2) NOT NULL DEFAULT '00',
  model_name varchar(200) NOT NULL DEFAULT '',
  manufacturer_id char(2) NOT NULL DEFAULT '00',
  manufacturer_name varchar(200) NOT NULL DEFAULT '',
  type_id char(1) NOT NULL DEFAULT '0',
  type_name varchar(100) NOT NULL DEFAULT '',
  sn varchar(255) NOT NULL default ''
) 

CREATE TABLE tbl_device_setup (
  setup_id char(8) NOT NULL PRIMARY KEY DEFAULT '',
  setup_desc varchar(255) NOT NULL default '',
  setuprq varchar(50)  NOT NULL DEFAULT '',
  applicant varchar(20) NOT NULL default '',
  transactor varchar(20) NOT NULL default ''
) 

CREATE TABLE tbl_device_setup_items (
  setup_id char(8) NOT NULL DEFAULT '',
  item_id int NOT NULL DEFAULT 0,
  device_id int NOT NULL DEFAULT 0,
  netcenter_id varchar(40) NOT NULL default '',
  model_id char(2) NOT NULL DEFAULT '00',
  model_name varchar(200) NOT NULL DEFAULT '',
  manufacturer_id char(2) NOT NULL DEFAULT '00',
  manufacturer_name varchar(200) NOT NULL DEFAULT '',
  type_id char(1) NOT NULL DEFAULT '0',
  type_name varchar(100) NOT NULL DEFAULT '',
  area_id int NOT NULL DEFAULT 0,
  area_name varchar(200) NOT NULL DEFAULT '',
  building_id int NOT NULL DEFAULT 0,
  building_name varchar(200) NOT NULL DEFAULT '',
  room_id int NOT NULL DEFAULT 0,
  room_name varchar(200) NOT NULL DEFAULT '',  
  ip varchar(50) NOT NULL default ''
) 

CREATE TABLE tbl_area(
  area_id int NOT NULL PRIMARY KEY DEFAULT 0,
  area_name varchar(200) NOT NULL DEFAULT ''
 )

CREATE TABLE tbl_building(
  building_id int NOT NULL PRIMARY KEY DEFAULT 0,
  building_name varchar(200) NOT NULL DEFAULT '',
  area_id int NOT NULL DEFAULT 0
 )
 
CREATE TABLE tbl_room(
  room_id int NOT NULL PRIMARY KEY DEFAULT 0,
  room_name varchar(200) NOT NULL DEFAULT '',
  area_id int NOT NULL DEFAULT 0,
  building_id int NOT NULL DEFAULT 0
 )


CREATE TABLE tbl_devicetype(
  type_id char(1) NOT NULL PRIMARY KEY DEFAULT '0',
  type_name varchar(100) NOT NULL DEFAULT ''
 )


CREATE TABLE tbl_manufacturer(
  manufacturer_id char(2) NOT NULL PRIMARY KEY DEFAULT '00',
  manufacturer_name varchar(200) NOT NULL DEFAULT ''
 )


CREATE TABLE tbl_model(
  model_id char(2) NOT NULL PRIMARY KEY DEFAULT '00',
  model_name varchar(200) NOT NULL DEFAULT '',
  manufacturer_id char(2) NOT NULL DEFAULT '00',
  manufacturer_name varchar(200) NOT NULL DEFAULT '',
  type_id char(1) NOT NULL DEFAULT '0',
  type_name varchar(100) NOT NULL DEFAULT ''
 )

----------

CREATE VIEW vw_model
AS 
select tbl_model.*,tbl_devicetype.type_name,tbl_manufacturer.manufacturer_name
   from tbl_model,tbl_manufacturer,tbl_devicetype 
   where tbl_model.manufacturer_id = tbl_manufacturer.manufacturer_id 
   and tbl_model.devicetype_id = tbl_devicetype.type_id

CREATE VIEW vw_device
AS 
select tbl_device.*,
   tbl_model.model_name,
   tbl_devicetype.type_name,
   tbl_manufacturer.manufacturer_name,
   tbl_area.area_name,
   tbl_building.building_name,
   tbl_room.room_name
   from tbl_device
   left join tbl_model on tbl_device.model_id = tbl_model.model_id 
   left join tbl_manufacturer on tbl_device.manufacturer_id = tbl_manufacturer.manufacturer_id 
   left join tbl_devicetype on tbl_device.type_id = tbl_devicetype.type_id
   left join tbl_area on tbl_device.area_id = tbl_area.area_id
   left join tbl_building on tbl_device.building_id = tbl_building.building_id
   left join tbl_room on tbl_device.room_id = tbl_room.room_id


---------------
INSERT INTO tbl_model  VALUES ('1', '00', '其它', '00');
INSERT INTO tbl_model  VALUES ('2', '01', '6509', '01');
INSERT INTO tbl_model  VALUES ('3', '02', '4006', '01');
INSERT INTO tbl_model  VALUES ('4', '03', '3512G', '01');
INSERT INTO tbl_model  VALUES ('5', '04', '3524', '01');
INSERT INTO tbl_model  VALUES ('6', '05', '3548', '01');
INSERT INTO tbl_model  VALUES ('7', '06', '3550', '01');
INSERT INTO tbl_model  VALUES ('8', '07', '2950G', '01');
INSERT INTO tbl_model  VALUES ('9', '08', '2950E', '01');
INSERT INTO tbl_model  VALUES ('10', '09', '2924C', '01');
INSERT INTO tbl_model  VALUES ('11', '10', '2924E', '01');
INSERT INTO tbl_model  VALUES ('12', '11', '2912MF', '01');
INSERT INTO tbl_model  VALUES ('13', '12', '2511', '01');
INSERT INTO tbl_model  VALUES ('14', '13', '1924', '01');
INSERT INTO tbl_model  VALUES ('15', '14', '6808', '02');
INSERT INTO tbl_model  VALUES ('16', '15', '6802', '02');
INSERT INTO tbl_model  VALUES ('17', '16', 'FlexHammer24', '02');
INSERT INTO tbl_model  VALUES ('18', '17', 'U2', '02');
INSERT INTO tbl_model  VALUES ('19', '18', 'U2E', '02');
INSERT INTO tbl_model  VALUES ('20', '19', 'UH24', '02');
INSERT INTO tbl_model  VALUES ('21', '20', 'UH24E', '02');
INSERT INTO tbl_model  VALUES ('22', '21', 'U2024E', '02');
INSERT INTO tbl_model  VALUES ('23', '22', '1824', '03');