tmrgo/update.sql

ALTER TABLE `tmrwatch2`.`tmr` 
ADD COLUMN `gps` varchar(50) NULL AFTER `lastinorout`;


ALTER TABLE `tmrwatch2`.`downloadedplan` 
ADD COLUMN `driverId` bigint NOT NULL DEFAULT 0 COMMENT '驾驶员' ;

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












  ALTER TABLE `tmrwatch2`.`pasture` 
  ADD COLUMN `werks` varchar(30) NULL AFTER `isgroups`;

  ALTER TABLE `tmrwatch2`.`bar` 
  ADD COLUMN `AUFNR` varchar(50) NULL COMMENT 'sap牛群订单行号' AFTER `backup2`;

  ALTER TABLE `tmrwatch2`.`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 `tmrwatch2`.`bar` 
  ADD COLUMN `sapCode` varchar(50) NULL AFTER `cattlecode`;



  
  ALTER TABLE `tmrwatch2`.`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 `tmrwatch2`.`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 `tmrwatch2`.`feedtemplet` 
  ADD COLUMN `cattle` varchar(30) NULL COMMENT '牛群类别' AFTER `backup2`;


  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;
	
	
	
	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 `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;













	
	  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) NULL COMMENT '修改用户' AFTER `volume`;

ALTER TABLE `fpdetailhistory` 
ADD COLUMN `userId` varchar(30) NULL COMMENT '修改用户' AFTER `createdate`;

ALTER TABLE `feedphistory` 
ADD COLUMN `userId` varchar(30) NULL COMMENT '修改用户' AFTER `createdate`;

ALTER TABLE `lpplandtl1history` 
ADD COLUMN `userId` varchar(30) NULL COMMENT '修改用户' AFTER `createdate`;

ALTER TABLE `fit_history` 
ADD COLUMN `userId` varchar(30) NULL AFTER `feedgroup`;




ALTER TABLE `fpdetail` 
MODIFY COLUMN `ptsid` bigint NOT NULL DEFAULT -1 COMMENT '补料配方' AFTER `ptid`;

ALTER TABLE `fpdetail` 
MODIFY COLUMN `ptid` bigint NOT NULL DEFAULT 0 COMMENT '配方模板' AFTER `ccountradio`;



INSERT INTO `cowclass` (`id`, `pastureid`, `classcode`, `classname`, `sort`,  `parentid`, `parentname`) 
select ccid,pastureId,ccname,ccname,ccid,ccid,ccname from feedtemplet   group by ccid,pastureId



ALTER TABLE `ftdry` 
ADD COLUMN `excess` tinyint(1) NULL DEFAULT 0 AFTER `ftid`


ALTER TABLE `tmrwatch2`.`feed` 
ADD COLUMN `udname` varchar(50) NULL AFTER `sapGoods`

















delete from `cowclass` ;




INSERT INTO `cowclass` (`id`, `pastureid`, `classcode`, `classname`, `sort`,  `parentid`, `parentname`) 
select ccid,pastureId,ccname,ccname,ccid,ccid,ccname from feedtemplet   group by ccid,pastureId;





ALTER TABLE `ftdry` 
ADD COLUMN `excess` tinyint(1) NULL DEFAULT 0 AFTER `ftid`;


ALTER TABLE `feed` 
ADD COLUMN `udname` varchar(50) NULL AFTER `sapGoods`;
	
	INSERT INTO `sysopt` (`pastureid`, `inforname`, `inforvalue`) VALUES ( (select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'gmUdSync', '0');
	
	
	
		INSERT INTO `sysopt` (`pastureid`, `inforname`, `inforvalue`) VALUES ( (select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'udFeedpSync', '1');
	
	
	
RENAME TABLE apisql TO apisql231228;


INSERT INTO `sysopt` (`pastureid`, `inforname`, `inforvalue`) VALUES ( (select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'domain', 'http://122.114.177.195:8082');

	
INSERT INTO `sysopt` (`pastureid`, `inforname`, `inforvalue`) VALUES ( (select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'sprinkleFeedTimeAllow', '10');
	



ALTER TABLE downloadplandtl1_exec ADD 	COLUMN    `xxwFeedCode` varchar(100) DEFAULT NULL;

ALTER TABLE downloadplandtl2 ADD 	COLUMN  `xxwTempletCode` varchar(100) DEFAULT NULL,  ADD 	COLUMN `xxwBarCode` varchar(100) DEFAULT NULL;


ALTER TABLE downloadplandtl1 ADD 	COLUMN    `xxwFeedCode` varchar(100) DEFAULT NULL;


ALTER TABLE feedtemplet  ADD 	COLUMN   `xxwTempletCode` varchar(100) DEFAULT NULL;

ALTER TABLE bar  ADD 	COLUMN   `xxwBarCode`varchar(50) DEFAULT NULL;

ALTER TABLE feed ADD 	COLUMN  `xxwFeedCode` varchar(100) DEFAULT NULL;

ALTER TABLE `barfeedremain` 
ADD COLUMN `dry` decimal(12, 2) NULL COMMENT '干物质' ;


ALTER TABLE `feedtemplethistory` 
ADD COLUMN `volume` decimal(12, 2) NULL AFTER `userId`;


ALTER TABLE `feedtemplet`
    ADD COLUMN `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否',
    ADD COLUMN `group_data_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团端数据ID',
    ADD COLUMN `group_version` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团端版本号';



ALTER TABLE .`bar` 
ADD COLUMN `udcode` varchar(50) NULL AFTER `sapCode`,
ADD COLUMN `udname` varchar(50) NULL AFTER `udcode`;




ALTER TABLE `feedtemplet` 
ADD COLUMN `autozone` int NOT NULL DEFAULT 0 AFTER `group_version`;




ALTER TABLE `downloadedplan` 
ADD COLUMN `artificial` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 为手动完成' AFTER `driverId`;



CREATE TABLE `tmr_gear` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tmrId` int(11) NOT NULL,
  `gear` varchar(30) DEFAULT NULL COMMENT '档位',
  `rev` int(11) DEFAULT NULL COMMENT '转速/分钟',
  `volume` decimal(12,5) DEFAULT NULL COMMENT '每转体积',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `tmr` 
ADD COLUMN `speed` decimal(12, 0) NOT NULL DEFAULT 0 COMMENT '速度' AFTER `volume`;

ALTER TABLE bar
ADD COLUMN `length` decimal(12, 0) NOT NULL DEFAULT 0 COMMENT '栏舍长度' AFTER `udname`;


ALTER TABLE `bar`
ADD COLUMN `pen` varchar(30) NULL COMMENT '圈舍' AFTER `length`;


INSERT INTO `sysopt` (`id`, `pastureid`, `inforname`, `inforvalue`) VALUES (3029240269385600828, (select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'yhfeedtemplet', '1')

INSERT INTO sysopt (`id`, `pastureid`, `inforname`, `inforvalue`)
 VALUES (3029240269385600829,(select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'yhautosecond', '0')



	ALTER TABLE `ftdetail`  ADD COLUMN 
	`is_show` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT '是否显示 0 无效 1 显示 2 隐藏';

	
	CREATE TABLE `resetplan` (
  `id` bigint(20) NOT NULL,
  `sort` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 0,
  `mydate` datetime NOT NULL,
  `plansort` int(11) DEFAULT 0,
  `lastsort` int(11) DEFAULT 0,
  `lastweight` decimal(12,4) DEFAULT NULL,
  `weight` decimal(12,4) DEFAULT 0.0000,
  `datacaptureno` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


ALTER TABLE `ftdetail` 
ADD COLUMN `shutdown` tinyint(1) NOT NULL DEFAULT 0 COMMENT '停机时间  秒' AFTER `is_show`;


ALTER TABLE `tmr` 
ADD COLUMN `shutdown` tinyint(1) NULL DEFAULT 0 COMMENT '停机时间  秒' AFTER `speed`;


ALTER TABLE `tmr` 
ADD COLUMN `appoint` int NULL COMMENT '撒料车指定搅拌车' AFTER `shutdown`;




ALTER TABLE `tmrwatch2`.`downloadplandtl1` 
DROP INDEX `downloadplandtl1_uni`,
ADD UNIQUE INDEX `downloadplandtl1_uni`(`pastureid`, `flpid`, `fid`, `date`, `pid`, `type`) USING BTREE;



CREATE TABLE `surplus` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pastureId` int(11) DEFAULT NULL,
  `surplus` varchar(50) DEFAULT NULL,
  `feedId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `surplus` (`surplus`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8mb3;


ALTER TABLE `feedtemplet` 
ADD COLUMN `surplus` int NULL COMMENT '剩料配置' AFTER `autozone`;

ALTER TABLE `ftdetail` 
ADD COLUMN `is_surplus` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 不是剩料' AFTER `shutdown`;


ALTER TABLE `downloadplandtl1_exec` 
ADD COLUMN `surplus` decimal(10, 2) NULL COMMENT '剩料重量' AFTER `xxwFeedCode`;


INSERT INTO `dictlist` ( `pid`, `pastureId`, `label`, `value`, `orderby`, `enable`) VALUES ( 19, NULL, '剩料', '5', NULL, 1);



INSERT INTO sysopt (`id`, `pastureid`, `inforname`, `inforvalue`)
 VALUES (3029240269385600831,(select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'language', '0');

INSERT INTO sysopt (`id`, `pastureid`, `inforname`, `inforvalue`)
 VALUES (3029240269385600832,(select column_default  as pastureid from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid'), 'YynSync', '1');