tmrgo/shengji.sql

CREATE TABLE `feedtemplethistory` (
  `id` bigint(20) NOT NULL COMMENT '配方模板id',
  `pastureid` bigint(20) NOT NULL,
  `tcode` varchar(50) DEFAULT NULL COMMENT '配方编码',
  `tname` varchar(50) NOT NULL COMMENT '配方名称',
  `tcolor` varchar(50) DEFAULT '#ccc' COMMENT '配方模板颜色',
  `ccid` bigint(20) DEFAULT NULL COMMENT '牲畜类别id',
  `ccname` varchar(50) DEFAULT NULL COMMENT '类别名称',
  `fttype` varchar(50) DEFAULT NULL COMMENT '配方类型',
  `fttypeid` tinyint(1) DEFAULT 0 COMMENT '配方类型id(1饲喂，2预混，3补料)',
  `source` varchar(50) DEFAULT NULL COMMENT '来源',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `enable` tinyint(1) DEFAULT 1 COMMENT '启用',
  `sort` int(11) DEFAULT 1 COMMENT '排序',
  `owner` varchar(50) DEFAULT NULL COMMENT '来源',
  `weight` decimal(38,3) DEFAULT NULL COMMENT '总重',
  `dryweight` decimal(38,3) DEFAULT NULL COMMENT '干物质含量',
  `isdelete` tinyint(1) DEFAULT 0 COMMENT '是否删除（0为未删除，1为伪删除）',
  `version` int(11) DEFAULT 1 COMMENT '修改一次增加版本号',
  `savetime` datetime DEFAULT current_timestamp() COMMENT '保存日期',
  `isissue` tinyint(4) DEFAULT 0 COMMENT '下发是否为当前配方（1是，0否）',
  `issueversion` int(11) DEFAULT 0 COMMENT '下发时的版本',
  `issueid` bigint(20) DEFAULT NULL COMMENT '下发时的id',
  `backup1` varchar(255) DEFAULT NULL COMMENT '备用1',
  `backup2` varchar(255) DEFAULT NULL COMMENT '备用2',
  `createdate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;



CREATE TABLE `lpplandtl1history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '发料计划详情',
  `pastureid` bigint(20) NOT NULL,
  `lppid` bigint(20) DEFAULT NULL COMMENT '计划id',
  `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
  `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
  `fpdid` bigint(20) DEFAULT NULL COMMENT '栏舍配方子id',
  `fttype` tinyint(4) DEFAULT 1 COMMENT '配方类型（1饲喂，0补料）',
  `lweight` decimal(10,2) unsigned DEFAULT NULL COMMENT '重量',
  `sort` int(11) DEFAULT 0 COMMENT '顺序',
  `tmrid` bigint(20) DEFAULT NULL COMMENT '撒料车id',
  `tmrname` varchar(50) DEFAULT NULL COMMENT '撒料车名称',
  `background` varchar(50) DEFAULT NULL COMMENT '颜色',
  `cowcount` int(11) DEFAULT NULL COMMENT '牛头数',
  `ccountradio` decimal(38,2) DEFAULT NULL COMMENT '牛头系数',
  `lweighthis` decimal(10,2) DEFAULT NULL,
  `createdate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`pastureid`) USING BTREE,
  KEY `fpdid` (`fpdid`) USING BTREE,
  KEY `lppid` (`lppid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3932719861083937372 DEFAULT CHARSET=utf8mb3;



CREATE TABLE `feedphistory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `pastureid` bigint(20) NOT NULL,
  `barname` char(20) DEFAULT NULL,
  `barid` bigint(20) NOT NULL COMMENT '舍栏id',
  `softccount` int(11) DEFAULT NULL COMMENT '软件牛头数',
  `ccount` int(11) NOT NULL DEFAULT 0 COMMENT '实际头数',
  `ratio` decimal(38,2) DEFAULT NULL COMMENT '系数',
  `ccountratio` decimal(38,2) DEFAULT 100.00 COMMENT '系数牛头数',
  `ftid` bigint(20) DEFAULT NULL COMMENT '配方模版id(不是补料模版的id)',
  `ftname` varchar(50) DEFAULT NULL COMMENT '配方名称',
  `ptsfid` bigint(20) DEFAULT NULL COMMENT '配方模版id(补料模版的id)',
  `ptsfname` varchar(50) DEFAULT NULL COMMENT '补料名称',
  `feedweight` decimal(38,3) DEFAULT NULL COMMENT '投喂量',
  `ftweight` decimal(38,3) DEFAULT NULL COMMENT '配方重量',
  `supplyweight` decimal(38,3) DEFAULT 0.000 COMMENT '补料重量',
  `owner` varchar(20) DEFAULT NULL COMMENT '编制人',
  `createdate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3976043477267383617 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='栏舍';



CREATE TABLE `fpdetailhistory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '配方子表id',
  `pastureid` bigint(20) NOT NULL,
  `barid` bigint(20) NOT NULL COMMENT '栏舍id',
  `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
  `times` tinyint(4) DEFAULT NULL COMMENT '班次',
  `tratio` decimal(38,1) DEFAULT NULL COMMENT '班次比例',
  `weight` decimal(38,2) unsigned DEFAULT 0.00 COMMENT '重量',
  `ptsrate` decimal(38,6) unsigned zerofill DEFAULT 00000000000000000000000000000000.000000 COMMENT '补料比例',
  `cowcount` char(20) DEFAULT NULL COMMENT '牛头数',
  `ccountradio` char(20) DEFAULT NULL COMMENT '牛头系数',
  `ptid` bigint(20) DEFAULT -1 COMMENT '配方模板',
  `ptsid` bigint(20) DEFAULT -1 COMMENT '补料配方',
  `ptuse` decimal(38,2) unsigned NOT NULL DEFAULT 0.00 COMMENT '0 未分配 大于0分配',
  `ptsuse` decimal(38,2) unsigned NOT NULL DEFAULT 0.00 COMMENT '0 未分配 大于0分配',
  `supplement` decimal(38,2) DEFAULT 0.00,
  `createdate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`pastureid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3976043477519043586 DEFAULT CHARSET=utf8mb3 COMMENT='栏舍配方子表历史记录';



alter table fit_history
    add autosecond int(11) DEFAULT NULL;

    alter table fit_history
    add preftid bigint(20) DEFAULT 0;

    alter table fit_history
    add  feedgroup varchar(50) DEFAULT NULL;




UPDATE `tmrwatch2`.`scheduled_up` SET `datasql` = 'SELECT\r\n	d.id,IFNULL(ft.id,d2.feedtempletid ) recipeId, \r\n	IFNULL(d.templetName,d2.feedtempletName ) recipeName, \r\n	date_format(d.mydate,\'%Y-%m-%d\') as dropDate,ifnull((select sort from downloadedplan where pid=d.pid and lpplanType !=d.lpplanType and mydate=d.mydate),d.sort) as tmrNo,\r\n	d.Times as dropShift,b.bcode as penId,d2.Fname as penName,fp.CCOUNT as cowCount, d2.SORT as feedingNo, \r\nifnull(ROUND(d2.lweight * (dd.actualweightminus/ (select sum(actualweightminus) from downloadplandtl1 where pid = dd.pid and type = 0) ),2) ,0)as expWeight,\r\n	d2.ActualWeightMinus as actualWeight, ifnull(date_format(timestamp(d2.InTime,CONCAT(\'-\',d2.processTime)),\'%Y-%m-%d %H:%i:%s\'),\"\") as startTime,\r\n	date_format(d2.InTime,\'%Y-%m-%d %H:%i:%s\') as endTime,ifnull((SELECT dr.driver FROM dutyrecord dr \r\nWHERE dr.pastureid = d.pastureid AND dr.eqid = d.tmrid AND dr.times= d.times  AND dr.operatetime <=d.mydate \r\nORDER BY dr.operatetime DESC LIMIT 1),\"\") as tmrName ,d.tmrtname equipmentId ,\r\ndd.fname,\r\nROUND( d2.actualweightminus/( select sum(dd2.actualweightminus) from downloadplandtl2 dd2 where dd2.pid = d2.pid) * (dd.actualweightminus ),2) feedWeight\r\n	from  downloadedplan d  \r\n	LEFT JOIN downloadplandtl2 d2 on d.id=d2.PID \r\n	LEFT JOIN (select dd.pid as lppid,dd1.* from downloadedplan dd join downloadplandtl1  dd1 on dd1.pid = dd.id and dd1.type = 0  \r\n		where dd.mydate= date_format(?,\'%Y-%m-%d\') and dd.IsCompleted=1 and dd.lpplanType in (0,1) ) dd on dd.lppid = d.pid\r\n	\r\n	LEFT JOIN feedp fp on d2.FBarID=fp.barid \r\n	left join bar b on fp.barid = b.id and d.pastureid = b.pastureid\r\n	left JOIN feedtemplet ft on d.templetName=ft.TNAME \r\n	where d.mydate= date_format(?,\'%Y-%m-%d\') and d.IsCompleted=1 and d.lpplanType!=1 and d.pastureid = ?  and d.id in (idlist) order by tmrno' WHERE `id` = 2;



UPDATE `tmrwatch2`.`apisql` SET `sqlstr` = 'SELECT \r\n(SELECT `pasture_name` FROM `pasture` WHERE pastureid = d.pastureid) 牧场,\r\nDATE_FORMAT(d.`mydate`, \'%Y-%m-%d\') 日期, d.`tmrtname` TMR名称,d.`projname` 车次,\r\nCASE d.`times` \r\nWHEN 1 THEN\r\n\'第一班\'\r\nWHEN 2 THEN\r\n\'第二班\'\r\nWHEN 3 THEN\r\n\'第三班\'\r\nWHEN 4 THEN\r\n\'第四班\'\r\nEND 班次,d.tempid as ftid ,(select tname from feedtemplet where id = d.tempid and pastureid = d.pastureid) 配方名称,d1.`fname` 饲料,d1.`lweight` 理论重量,d1.`actualweightminus` 实际重量,\r\nABS(d1.`lweight`-d1.`actualweightminus`) 误差值,\r\n\r\n\r\nCONCAT(if(round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d1.`pastureid`) = 1,if(abs(d1.actualweightminus - d1.lweight) <=d1.feedallowratio , 100,if(d1.actualweightminus > d1.lweight , 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.actualweightminus, 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.lweight) *100) ,\r\nIF (d1.`actualweightminus`>d1.`lweight`,\r\nROUND((d1.`lweight`/d1.`actualweightminus`*100),2) ,\r\nROUND((d1.`actualweightminus`/d1.`lweight`*100) ,2))),0),2) < 0 ,0,round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d1.`pastureid`) = 1,if(abs(d1.actualweightminus - d1.lweight) <=d1.feedallowratio , 100,if(d1.actualweightminus > d1.lweight , 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.actualweightminus, 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.lweight)*100) ,\r\nIF (d1.`actualweightminus`>d1.`lweight`,\r\nROUND((d1.`lweight`/d1.`actualweightminus`*100),2) ,\r\nROUND((d1.`actualweightminus`/d1.`lweight`*100) ,2))),0),2)),\'%\')  准确率,\r\nd.`plantime` 计划时间,\r\nd1.`begintime` 开始时间1,\r\nDATE_FORMAT(d1.`intime`, \'%H:%i:%s\') 结束时间,\r\nDATE_FORMAT(IFNULL((SELECT MAX(intime) FROM downloadplandtl1 d2 \r\nWHERE  d1.`pid`=d2.`pid` AND d1.pastureid = d2.pastureid AND d2.intime<d1.intime)\r\n, (SELECT MAX(intime) FROM downloadedplan d2 \r\nWHERE  d1.`date`=d2.`mydate` AND d1.pastureid = d2.pastureid AND d2.id=d1.pid)\r\n), \'%H:%i:%s\') 开始时间,\r\ncase d1.buttontype\r\nwhen 1 then \'自动\'\r\nwhen 2 then \'手动\'\r\nwhen 3 then \'手动\'\r\nend  跳转方式,d1.`lastactualweight` 开始重量,d1.`actualweight` 结束重量,\r\n(select de.processtime  from downloadplandtl1_exec de  where de.pid = d.id   AND d.`pastureid`=de.`pastureid`  and de.sort = d1.sort ) 搅拌时间\r\n\r\n\r\nFROM `downloadedplan` d JOIN `downloadplandtl1` d1 ON d.`id`=d1.`pid` AND d.`pastureid`=d1.`pastureid`\r\nWHERE  d.pastureid=? AND d.`mydate`  BETWEEN ? AND ?  AND d.lpplantype  in (0,1,4) and  d1.type = 0 and iscompleted = 1\r\nAND ( d.`tmrtname`=? or ? = \'\')\r\nAND ( d.`projname`=? or ? = \'\')\r\nAND ( d.`times`=? or ? = \'\')\r\nAND ( if(? = 1,d1.`buttontype` = 1,d1.`buttontype` != 1) or ? = \'\')\r\nAND ( d.`templetname`=? or ? = \'\')\r\n	and  if(?  = \'\' and ? = \'\' , 1=1 ,ABS(d1.`lweight`-d1.`actualweightminus`) between ?  and ?) \r\n and  if(?  = \'\' and ? = \'\' , 1=1 ,if(round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d1.`pastureid`) = 1,if(abs(d1.actualweightminus - d1.lweight) <=d1.feedallowratio , 100,if(d1.actualweightminus > d1.lweight , 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.actualweightminus, 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.lweight) *100) ,\r\nIF (d1.`actualweightminus`>d1.`lweight`,\r\nROUND((d1.`lweight`/d1.`actualweightminus`*100),2) ,\r\nROUND((d1.`actualweightminus`/d1.`lweight`*100) ,2))),0),2) < 0 ,0,round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d1.`pastureid`) = 1,if(abs(d1.actualweightminus - d1.lweight) <=d1.feedallowratio , 100,if(d1.actualweightminus > d1.lweight , 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.actualweightminus, 1-(abs(d1.actualweightminus - d1.lweight) - d1.feedallowratio)/d1.lweight)*100) ,\r\nIF (d1.`actualweightminus`>d1.`lweight`,\r\nROUND((d1.`lweight`/d1.`actualweightminus`*100),2) ,\r\nROUND((d1.`actualweightminus`/d1.`lweight`*100) ,2))),0),2))  between ?  and ?) \r\n and  if(?  = \'\' and ? = \'\' , 1=1 ,ROUND(IFNULL(IF(ABS(d1.`actualweightminus`-d1.`lweight`)<=d1.`feedallowratio` AND d1.`actualweightminus`<>0,1,0)/1,0)*100,2) between ?  and ?) \r\n and   if(?= \'\',1, if(d1.`feedallowratio` <=0,0, if(ABS(d1.`lweight`-d1.`actualweightminus`) >d1.`feedallowratio` ,1,0  ))) = 1\r\n \r\nORDER BY d.`mydate`,d.`projname`,d1.`intime` \r\n' WHERE `id` = 1551;




UPDATE `tmrwatch2`.`apisql` SET `sqlstr` = 'SELECT \r\n(SELECT `pasture_name` FROM `pasture` WHERE pastureid = d.pastureid) 牧场,\r\nDATE_FORMAT(d.`mydate`, \'%Y-%m-%d\') 日期, d.`tmrtname` TMR名称,d.`projname` 车次,\r\nCASE d.`times` \r\nWHEN 1 THEN\r\n\'第一班\'\r\nWHEN 2 THEN\r\n\'第二班\'\r\nWHEN 3 THEN\r\n\'第三班\'\r\nWHEN 4 THEN\r\n\'第四班\'\r\nEND 班次,\r\n(select tname from feedtemplet where id = d.tempid and pastureid = d.pastureid) 配方名称,d.tempid ftid,d2.`fname` 栏舍,d2.`lweight` 理论重量,d2.`actualweightminus` 实际重量,\r\nABS(d2.`lweight`-d2.`actualweightminus`) 误差值,\r\nCONCAT(if(round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d2.`pastureid`) = 1,if(abs(d2.actualweightminus - d2.lweight) <=d2.allowratio , 100,if(d2.actualweightminus > d2.lweight , 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.actualweightminus, 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.lweight)*100) ,\r\nIF (d2.`actualweightminus`>d2.`lweight`,\r\nROUND((d2.`lweight`/d2.`actualweightminus`*100),2) ,\r\nROUND((d2.`actualweightminus`/d2.`lweight`*100) ,2))),0),2) < 0,0,round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d2.`pastureid`) = 1,if(abs(d2.actualweightminus - d2.lweight) <=d2.allowratio , 100,if(d2.actualweightminus > d2.lweight , 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.actualweightminus, 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.lweight) * 100) ,\r\nIF (d2.`actualweightminus`>d2.`lweight`,\r\nROUND((d2.`lweight`/d2.`actualweightminus`*100),2) ,\r\nROUND((d2.`actualweightminus`/d2.`lweight`*100) ,2))),0),2)),\'%\')  准确率,\r\n\r\n\r\n\r\nDATE_FORMAT(d.`plantime`, \'%H:%i:%s\')  计划时间,d2.`begintime` 开始时间1,DATE_FORMAT(d2.`intime`, \'%H:%i:%s\') 结束时间,\r\nDATE_FORMAT(IFNULL((SELECT MAX(intime) FROM downloadplandtl2 d1\r\nWHERE  d1.`pid`=d2.`pid` AND d1.pastureid = d2.pastureid AND d1.intime<d2.intime)\r\n, (SELECT MAX(intime) FROM downloadplandtl1_exec de\r\nWHERE  de.`date`=d2.`date` AND de.pastureid = d2.pastureid AND de.pid=d2.pid)\r\n), \'%H:%i:%s\') 开始时间,\r\n\r\ncase d2.buttontype\r\nwhen 1 then \'自动\'\r\nwhen 2 then \'手动\'\r\nwhen 3 then \'手动\'\r\nend  跳转方式,d2.`lastactualweight` 开始重量,d2.`actualweight` 结束重量\r\nFROM `downloadedplan` d JOIN `downloadplandtl2` d2 ON d.`id`=d2.`pid` AND d.`pastureid`=d2.`pastureid` and d.iscompleted = 1\r\nWHERE d.pastureid=? AND d.`mydate`  BETWEEN ? AND ?  AND d.lpplantype  in (0,2) and  d2.type = 0\r\nAND ( d.`tmrtname`=? or ? = \'\')\r\nAND ( d.`projname`=? or ? = \'\')\r\nAND ( d.`times`=? or ? = \'\')\r\nAND ( d.`templetname`=? or ? = \'\')\r\nAND ( d2.`fname`=? or ? = \'\')\r\nAND ( if(? = 1,d2.`buttontype` = 1,d2.`buttontype` != 1) or ? = \'\')\r\n\r\n	and  if(?  = \'\' and ? = \'\' , 1=1 ,ABS(d2.`lweight`-d2.`actualweightminus`) between ?  and ?) \r\n and  if(?  = \'\' and ? = \'\' , 1=1 ,if(round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d2.`pastureid`) = 1,if(abs(d2.actualweightminus - d2.lweight) <=d2.allowratio , 100,if(d2.actualweightminus > d2.lweight , 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.actualweightminus, 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.lweight)*100) ,\r\nIF (d2.`actualweightminus`>d2.`lweight`,\r\nROUND((d2.`lweight`/d2.`actualweightminus`*100),2) ,\r\nROUND((d2.`actualweightminus`/d2.`lweight`*100) ,2))),0),2) < 0,0,round(ifnull(if((select inforvalue  from sysopt where inforname = \'accuracy\'  and pastureid = d2.`pastureid`) = 1,if(abs(d2.actualweightminus - d2.lweight) <=d2.allowratio , 100,if(d2.actualweightminus > d2.lweight , 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.actualweightminus, 1-(abs(d2.actualweightminus - d2.lweight) - d2.allowratio)/d2.lweight) * 100) ,\r\nIF (d2.`actualweightminus`>d2.`lweight`,\r\nROUND((d2.`lweight`/d2.`actualweightminus`*100),2) ,\r\nROUND((d2.`actualweightminus`/d2.`lweight`*100) ,2))),0),2))  between ?  and ?) \r\n and  if(?  = \'\' and ? = \'\' , 1=1 ,ROUND(IFNULL(IF(ABS(d2.`actualweightminus`-d2.`lweight`)<=d2.`allowratio` AND d2.`actualweightminus`<>0,1,0)/1,0)*100,2) between ?  and ?) \r\n and   if(?= \'\',1, if(d2.`allowratio` <=0,0, if(ABS(d2.`lweight`-d2.`actualweightminus`) >d2.`allowratio` ,1,0  ))) = 1\r\n \r\n\r\nORDER BY d.`mydate`,d.`projname`,d2.`intime` ' WHERE `id` = 1552;