GarbageCrane/一车两车整合版/Sql/database.sql


CREATE DATABASE IF NOT EXISTS database
       DEFAULT CHARACTER SET utf8
       DEFAULT COLLATE  utf8_general_ci;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for censor
-- ----------------------------
DROP TABLE IF EXISTS `censor`;
CREATE TABLE `censor` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of censor
-- ----------------------------
INSERT INTO `censor` VALUES ('1', '河南储备物资管理局三三九处', '2019-08-29 08:18:38');

-- ----------------------------
-- Table structure for client
-- ----------------------------
DROP TABLE IF EXISTS `client`;
CREATE TABLE `client` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of client
-- ----------------------------
INSERT INTO `client` VALUES ('1', '河南晨铭', '2019-08-29 08:29:27');

-- ----------------------------
-- Table structure for config
-- ----------------------------
DROP TABLE IF EXISTS `config`;
CREATE TABLE `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` varchar(50) NOT NULL,
  `value` varchar(100) NOT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of config
-- ----------------------------
INSERT INTO `config` VALUES ('1', 'FeetWeight20', '2300', '20尺集装箱毛重');
INSERT INTO `config` VALUES ('2', 'FeetWeight40', '3800', '40尺集装箱尺寸');
INSERT INTO `config` VALUES ('3', 'DriverSetCaseNumber', '0', '驾驶员输入箱号');
INSERT INTO `config` VALUES ('4', 'PaperKind', '9', '纸张大小');
INSERT INTO `config` VALUES ('5', 'Orientation', '1', '纸张方向');
INSERT INTO `config` VALUES ('6', 'Top', '0.6', '上边距');
INSERT INTO `config` VALUES ('7', 'Left', '1.5', '下边距');
INSERT INTO `config` VALUES ('8', 'Right', '1.5', '右边距');
INSERT INTO `config` VALUES ('9', 'Bottom', '0.6', '下边距');

-- ----------------------------
-- Table structure for device
-- ----------------------------
DROP TABLE IF EXISTS `device`;
CREATE TABLE `device` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ip` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `port` int(10) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  `code` varchar(5) DEFAULT NULL COMMENT 'IP地址最后一组',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of device
-- ----------------------------
INSERT INTO `device` VALUES ('1', '192.168.1.10', '1号起重机', '502', '2019-08-13 15:20:33', '10');

-- ----------------------------
-- Table structure for driver
-- ----------------------------
DROP TABLE IF EXISTS `driver`;
CREATE TABLE `driver` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(10) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of driver
-- ----------------------------
INSERT INTO `driver` VALUES ('1', '1', '张三', '2019-08-22 16:20:24');
INSERT INTO `driver` VALUES ('2', '2', '李四', '2019-08-22 16:20:26');

-- ----------------------------
-- Table structure for operator
-- ----------------------------
DROP TABLE IF EXISTS `operator`;
CREATE TABLE `operator` (
  `id` smallint(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `truename` varchar(20) DEFAULT NULL,
  `pwd` varchar(50) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  `role` smallint(1) DEFAULT '1' COMMENT ' 1 普通用户 2 管理员',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of operator
-- ----------------------------
INSERT INTO `operator` VALUES ('1', 'admin', '管理员', '46f94c8de14fb36680850768ff1b7f2a', '2019-08-16 15:46:45', '2');

-- ----------------------------
-- Table structure for printset
-- ----------------------------
DROP TABLE IF EXISTS `printset`;
CREATE TABLE `printset` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fieldname` varchar(20) DEFAULT NULL,
  `cellcoord` varchar(20) DEFAULT NULL,
  `displayname` varchar(20) DEFAULT NULL,
  `Inuse` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of printset
-- ----------------------------
INSERT INTO `printset` VALUES ('1', 'Number', 'B4,G4', '序号', 1);
INSERT INTO `printset` VALUES ('2', 'NormType', 'B5,G5', '规格', 1);
INSERT INTO `printset` VALUES ('3', 'Operator', 'D5,I5', '司磅员', 1);
INSERT INTO `printset` VALUES ('4', 'CaseNumber', 'B6,G6', '箱号', 1);
INSERT INTO `printset` VALUES ('5', 'Time', 'B7,G7', '日期', '1');
INSERT INTO `printset` VALUES ('6', 'Censor', 'B8,G8', '检测单位', 1);
INSERT INTO `printset` VALUES ('7', 'Client', 'B9,G9', '客户', 1);
INSERT INTO `printset` VALUES ('8', 'GrossWeight', 'B10,G10', '毛重',1);
INSERT INTO `printset` VALUES ('9', 'TareWeight', 'B11,G11', '皮重', 1);
INSERT INTO `printset` VALUES ('10', 'NetWeight', 'D11,I11', '净重', 1);
INSERT INTO `printset` VALUES ('11', 'State', 'A12,F12', '状态', 1);
INSERT INTO `printset` VALUES ('12', 'PrintTime', 'C12,H12', '打印时间', 1);
INSERT INTO `printset` VALUES ('13', 'Device', 'A3,F3', '设备名称', 1);

-- ----------------------------
-- Table structure for record
-- ----------------------------
DROP TABLE IF EXISTS `record`;
CREATE TABLE `record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(20) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  `casenumber` varchar(50) DEFAULT NULL,
  `censor` varchar(200) DEFAULT NULL,
  `client` varchar(200) DEFAULT NULL,
  `operator` varchar(50) DEFAULT NULL,
  `driver` varchar(50) DEFAULT NULL,
  `grossweight` int(10) unsigned DEFAULT NULL,
  `tareweight` int(10) unsigned DEFAULT NULL,
  `netweight` int(10) unsigned DEFAULT NULL,
  `normtype` varchar(10) DEFAULT NULL,
  `deviationX` int(10) DEFAULT NULL,
  `deviationY` int(10) NOT NULL,
  `deviationweight` int(10) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `type` smallint(1) DEFAULT NULL COMMENT '插入方式 1 自动 2手动',
  `lamp` smallint(1) DEFAULT '1' COMMENT '指示灯',
  `device` varchar(50) DEFAULT NULL COMMENT '设备名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for srcdat
-- ----------------------------
DROP TABLE IF EXISTS `srcdat`;
CREATE TABLE `srcdat` (
  `time` timestamp NULL DEFAULT NULL,
  `ch0` varchar(50) DEFAULT NULL COMMENT '箱号',
  `ch1` smallint(5) DEFAULT NULL COMMENT '操作员',
  `ch2` int(10) DEFAULT NULL COMMENT '总重',
  `ch3` smallint(5) DEFAULT NULL COMMENT '偏心X',
  `ch4` smallint(5) DEFAULT NULL COMMENT '偏心Y',
  `ch5` smallint(5) DEFAULT NULL COMMENT '偏重',
  `ch6` int(10) DEFAULT NULL COMMENT '角重1',
  `ch7` int(10) DEFAULT NULL COMMENT '角重2',
  `ch8` int(11) DEFAULT NULL COMMENT '角重3',
  `ch9` int(10) DEFAULT NULL COMMENT '角重4',
  `ch10` smallint(5) DEFAULT NULL COMMENT '规格 0-20尺 1-40尺',
  `ch11` smallint(5) DEFAULT NULL COMMENT '0不合格 1 合格 2 无效',
  `ch12` smallint(5) DEFAULT NULL COMMENT '设备IP最后一组数字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TRIGGER IF EXISTS `tri_insert_srcdat`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_srcdat` AFTER INSERT ON `srcdat` FOR EACH ROW BEGIN
	DECLARE v_tare,v_net INT DEFAULT 0;DECLARE v_number,v_driver,v_state VARCHAR(20);DECLARE v_isopen SMALLINT;DECLARE v_device VARCHAR(50);
	SET v_isopen=(SELECT config.value from config where config.key='DriverSetCaseNumber');
	IF v_isopen=1	THEN
		SET v_tare= (SELECT CASE WHEN new.ch10=20 THEN (select config.value from config where config.key='FeetWeight20') ELSE (select config.value from config where config.key='FeetWeight40') END);
		SET v_net= new.ch2-v_tare;
		SET v_number=(SELECT CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),LPAD((COUNT(1)+1),5,0))  from record where time>DATE(NOW()));
		SET v_driver=(SELECT driver.name from driver where driver.id=new.ch1);
		SET v_state=(SELECT CASE WHEN new.ch11=0 THEN '不合格' WHEN new.ch11=1 THEN '合格' ELSE '无效' END);
   		SET v_device=(SELECT device.name from device WHERE device.code=new.ch12);
		INSERT INTO record (number,time,casenumber,driver,grossweight,tareweight,netweight,normtype,deviationX,deviationY,deviationweight,state,type,device)VALUES(v_number,new.time,new.ch0,v_driver,new.ch2,v_tare,v_net,CONCAT(new.ch10,'英尺'),new.ch3,new.ch4,new.ch5,v_state,1,v_device);
	END IF;
END
;;
DELIMITER ;