tmrgo1/migration/haiyuan.sql

CREATE TABLE `dist` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `distType` VARCHAR(30) DEFAULT NULL,
  `distName` VARCHAR(50) DEFAULT NULL,
  `distCode` VARCHAR(50) DEFAULT NULL,
  `remark` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;


CREATE TABLE `bar_sap` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '栏舍id',
  `pastureid` BIGINT(20) NOT NULL COMMENT '牧场id',
  `bcode` VARCHAR(50) DEFAULT NULL COMMENT '栏舍编号',
  `bname` VARCHAR(200) NOT NULL COMMENT '栏舍名称',
  `allowratio` INT(11) DEFAULT 0 COMMENT '允许误差数',
  `autozone` INT(11) DEFAULT 0 COMMENT '跳转重量域（kg）',
  `autosecond` INT(4) DEFAULT 0 COMMENT '跳转延时值',
  `autosecondname` VARCHAR(50) DEFAULT NULL COMMENT '跳转延时名称',
  `isstart` TINYINT(1) DEFAULT 1 COMMENT '确认开始',
  `begintag` INT(11) DEFAULT NULL,
  `endtag` INT(11) DEFAULT NULL,
  `sort` INT(11) DEFAULT 0 COMMENT '排序',
  `enable` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `isdelete` TINYINT(1) DEFAULT 0 COMMENT '是否删除（0为未删除，1为伪删除）',
  `backup1` VARCHAR(255) DEFAULT NULL COMMENT '备用1',
  `backup2` VARCHAR(255) DEFAULT NULL COMMENT '备用2',
  `AUFNR` VARCHAR(50) DEFAULT NULL COMMENT 'sap牛群订单行号',
  `class` VARCHAR(50) DEFAULT NULL COMMENT '牛舍类型',
  `classcode` VARCHAR(50) DEFAULT NULL COMMENT '牛舍类型编码',
  `cattle` VARCHAR(50) DEFAULT NULL COMMENT '牛群',
  `cattlecode` VARCHAR(50) DEFAULT NULL COMMENT '牛群编码',
  `sapCode` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`,`pastureid`) USING BTREE,
  UNIQUE KEY `bar_bname` (`bname`,`pastureid`) USING BTREE,
  UNIQUE KEY `bar_bcode` (`bcode`,`pastureid`) USING BTREE,
  UNIQUE KEY `pastureid` (`pastureid`,`sapCode`)
) ENGINE=INNODB AUTO_INCREMENT=4354454795596596571 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='栏舍';

CREATE TABLE `cattleorder` (
  `AUFNR` VARCHAR(20) NOT NULL COMMENT '订单号',
  `KTEXT` VARCHAR(50) DEFAULT NULL COMMENT '订单描述',
  `ZNZLX` VARCHAR(50) DEFAULT NULL COMMENT '牛只类型 A-母犊牛 B-母育成牛 C-母青年牛 D-母泌乳牛 E-母干奶牛 F-公牛',
  `AUART` VARCHAR(50) DEFAULT NULL COMMENT '订单类型编码',
  `AUTXT` VARCHAR(50) DEFAULT NULL COMMENT '订单类型名称',
  `BUKRS` VARCHAR(50) DEFAULT NULL COMMENT '公司代码',
  `BUTXT` VARCHAR(50) DEFAULT NULL COMMENT '公司代码名称',
  PRIMARY KEY (`AUFNR`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;


CREATE TABLE `feed_sap` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `MATNR` VARCHAR(50) DEFAULT NULL COMMENT '物料号',
  `MTART` VARCHAR(50) DEFAULT NULL COMMENT '物料类型',
  `MATKL` VARCHAR(50) DEFAULT NULL COMMENT '物料组',
  `MAKTX` VARCHAR(50) DEFAULT NULL COMMENT '物料描述',
  `MEINS` VARCHAR(50) DEFAULT NULL COMMENT '基本计量单位',
  `UMREZ` VARCHAR(50) DEFAULT NULL COMMENT '基本计量单位数量',
  `MEINH` VARCHAR(50) DEFAULT NULL COMMENT '转换单位',
  `UMREN` VARCHAR(50) DEFAULT NULL COMMENT '转换单位数量',
  `ZMINC` VARCHAR(50) DEFAULT NULL COMMENT '物料名称',
  `ZGUIG` VARCHAR(50) DEFAULT NULL COMMENT '规格型号',
  `LVORM` VARCHAR(50) DEFAULT NULL COMMENT '物料删除标记',
  `LAEDA` VARCHAR(50) DEFAULT NULL COMMENT '修改日期',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1156 DEFAULT CHARSET=utf8mb3;



  CREATE TABLE `saplog` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `pastureId` VARCHAR(20) DEFAULT NULL,
    `request` LONGTEXT DEFAULT NULL,
    `response` LONGTEXT DEFAULT NULL,
    `status` VARCHAR(20) DEFAULT NULL,
    `msgtext` VARCHAR(255) DEFAULT NULL,
    `url` VARCHAR(255) DEFAULT NULL,
    `createTime` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(),
    `msgtype` INT(1) DEFAULT NULL COMMENT '0饲喂  1剩料  2原料  3栏舍  4配方 5栏舍牛头数 6库存',
    `dataDate` VARCHAR(20) DEFAULT NULL COMMENT '数据日期',
    PRIMARY KEY (`id`)
  ) ENGINE=INNODB AUTO_INCREMENT=2039 DEFAULT CHARSET=utf8mb3;
	
	
	
	
	ALTER TABLE `tmr` 
ADD COLUMN `gps` VARCHAR(50) NULL;



ALTER TABLE `tmr` 
ADD INDEX(`pastureid`, `gps`);












  ALTER TABLE `pasture` 
  ADD COLUMN `werks` VARCHAR(30) NULL  ;

  ALTER TABLE `bar` 
  ADD COLUMN `AUFNR` VARCHAR(50) NULL COMMENT 'sap牛群订单行号';

  ALTER TABLE `bar` 
  ADD COLUMN `class` VARCHAR(50) NULL COMMENT '牛舍类型' AFTER `AUFNR`,
  ADD COLUMN `classcode` VARCHAR(50) NULL COMMENT '牛舍类型编码' AFTER `class`,
  ADD COLUMN `cattle` VARCHAR(50) NULL COMMENT '牛群' AFTER `classcode`,
  ADD COLUMN `cattlecode` VARCHAR(50) NULL COMMENT '牛群编码' AFTER `cattle`;


  ALTER TABLE `bar` 
  ADD COLUMN `sapCode` VARCHAR(50) NULL AFTER `cattlecode`;



  
  ALTER TABLE `feed` 
  ADD COLUMN 	`sapMtart` BIGINT(20) DEFAULT NULL COMMENT 'sap编码',
  ADD COLUMN  `sapGewei` VARCHAR(20) DEFAULT NULL COMMENT 'sap单位',
  ADD COLUMN   `sapLGPRO` VARCHAR(20) DEFAULT NULL COMMENT 'sap牧场编码',
  ADD COLUMN  `sapCHARG` VARCHAR(20) DEFAULT NULL,
  ADD COLUMN  `sapCode` VARCHAR(20) DEFAULT NULL;


  ALTER TABLE `feed` 
  MODIFY COLUMN `sapCode` VARCHAR(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT 'sap编码' AFTER `sapCHARG`,
  ADD COLUMN `sapMaterialtype` VARCHAR(50) NULL COMMENT 'sap物料类型' AFTER `sapCode`,
  ADD COLUMN `sapGoods` VARCHAR(50) NULL COMMENT 'sap货位' AFTER `sapMaterialtype`;


  ALTER TABLE `feedtemplet` 
  ADD COLUMN `cattle` VARCHAR(30) NULL COMMENT '牛群类别' AFTER `backup2`;
	
	  ALTER TABLE `feedtemplet` 
  ADD COLUMN 
	`volume` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '容积';
	
		  ALTER TABLE `tmr` 
  ADD COLUMN 
	`volume` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '容积';
	
	
			  ALTER TABLE `feedtemplethistory` 
  ADD COLUMN 
	`volume` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '容积';
	
	
		
			  ALTER TABLE `feedtempletdate` 
  ADD COLUMN 
	`volume` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '容积';





			  ALTER TABLE `feedtemplethistory` 
  ADD COLUMN 
`userId` VARCHAR(30) DEFAULT NULL COMMENT '修改用户';
		
			  ALTER TABLE `feedtempletdate` 
  ADD COLUMN 
`userId` VARCHAR(30) DEFAULT NULL COMMENT '修改用户';



ALTER TABLE `feedphistory` 
  ADD COLUMN 
`userId` VARCHAR(30) DEFAULT NULL COMMENT '修改用户';



ALTER TABLE `fpdetailhistory` 
  ADD COLUMN 
`userId` VARCHAR(30) DEFAULT NULL COMMENT '修改用户';



ALTER TABLE `lpplandtl1history` 
  ADD COLUMN 
`userId` VARCHAR(30) DEFAULT NULL COMMENT '修改用户';


ALTER TABLE `fit_history` 
  ADD COLUMN 
`userId` VARCHAR(30) DEFAULT NULL COMMENT '修改用户';



ALTER TABLE `downloadedplan` 
  ADD COLUMN 
`driverId` BIGINT(20) DEFAULT NULL COMMENT '驾驶员';


CREATE TABLE `remind` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `message` VARCHAR(100) DEFAULT NULL,
  `trigger` VARCHAR(100) DEFAULT NULL COMMENT '触发',
  `pastureid` INT(11) NOT NULL,
  `user` LONGTEXT NOT NULL,
  `function` VARCHAR(100) DEFAULT NULL,
  `service_id` INT(3) DEFAULT NULL COMMENT '返回得类型id',
  PRIMARY KEY (`id`,`pastureid`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;