ALTER TABLE `tmrwatch2`.`feedlaid` 
ADD COLUMN `status` int(1) NOT NULL DEFAULT 0 COMMENT '0 已出库 \r\n1 未出库 \r\n2 已撤销\r\n' AFTER `pricenew`;

alter table feedtemplet
    add updatetime datetime default CURRENT_TIMESTAMP null;

ALTER TABLE `tmrwatch`.`tmr` 
ADD COLUMN `lastinorout` int(1) NULL DEFAULT 0 AFTER `autosecondname`


alter table fpdetail
    alter column ptuse set default 0;

alter table fpdetail
    alter column ptsuse set default 0;

    alter table fpdetail
    alter column supplement set default 0;

alter table fpdetail
    alter column ptsrate set default 0;


alter table fpdetail
    alter column weight set default 0;


update fpdetail  set ptsrate = 0  where ptsrate is null ;

update fpdetail  set supplement = 0  where supplement is null ;
  

alter table feedp
    alter column supplyweight set default 0;

-- 修改饲料表顺序
-- update
--  (SELECT @rownum:=0)r ,feed fn
--  set fn.sort =  @rownum := @rownum+1 
-- WHERE fn.pastureid = 1647917519 
-- ORDER BY fn.sort DESC


        update
        (SELECT @rownum:=0)r ,bar fn
        set fn.sort =  @rownum := @rownum+1 
        WHERE fn.pastureid = 1648637190 
        ORDER BY fn.id DESC


-- 宾州筛bug
-- `barid`, `times`, `operatetime` 唯一索引
-- 宾州筛历史记录表
-- CREATE TABLE `pennsieve_history` (
--   `id` bigint(20) NOT NULL AUTO_INCREMENT,
--   `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
--   `times` varchar(50) DEFAULT NULL COMMENT '班次',
--   `onerate` decimal(20,2) DEFAULT NULL COMMENT '第一层比例',
--   `oneweight` decimal(20,2) DEFAULT NULL COMMENT '第一层重量',
--   `tworate` decimal(20,2) DEFAULT NULL COMMENT '第二层比列',
--   `twoweight` decimal(20,2) DEFAULT NULL COMMENT '第二层重量',
--   `threerate` decimal(20,2) DEFAULT NULL COMMENT '第三层比列',
--   `threeweight` decimal(20,2) DEFAULT NULL COMMENT '第三层重量',
--   `fourrate` decimal(20,2) DEFAULT NULL COMMENT '第四层比列',
--   `fourweight` decimal(20,2) DEFAULT NULL COMMENT '第四层重量',
--   `operatetime` datetime DEFAULT NULL COMMENT '操作时间',
--   `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
--   `pastureid` bigint(20) DEFAULT NULL COMMENT '牧场id',
--   `species` varchar(50) DEFAULT NULL COMMENT '种类',
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;


-- 配方干物质
-- 到体况评分bug
CREATE TABLE `ftdry_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
  `times` varchar(50) DEFAULT NULL COMMENT '班次',
  `freshweight` decimal(20,2) DEFAULT NULL COMMENT '新鲜重量',
  `dryweight` decimal(20,2) DEFAULT NULL COMMENT '烘干重量',
  `thoweight` decimal(20,0) DEFAULT NULL COMMENT '理论干物质',
  `actweight` decimal(20,0) DEFAULT NULL COMMENT '实际干物质',
  `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
  `changeTime` datetime DEFAULT NULL COMMENT '操作时间',
  `pastureid` bigint(20) DEFAULT NULL COMMENT '牧场id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB



CREATE TABLE `dungandbodyhistory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
  `standardscore` varchar(50) DEFAULT NULL COMMENT '标准分数',
  `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
  `cowsum` varchar(50) DEFAULT NULL COMMENT '牛头数',
  `onescore` varchar(50) DEFAULT NULL COMMENT '1',
  `twoscore` varchar(50) DEFAULT NULL COMMENT '2',
  `threescore` varchar(50) DEFAULT NULL COMMENT '3',
  `fourscore` varchar(50) DEFAULT NULL COMMENT '4',
  `fivescore` varchar(50) DEFAULT NULL COMMENT '5',
  `pastureid` bigint(20) DEFAULT NULL COMMENT '牧场id',
  `species` varchar(50) DEFAULT NULL COMMENT '种类',
  `changetime` datetime DEFAULT NULL COMMENT '操作时间',
  `sixscore` varchar(50) DEFAULT NULL COMMENT '6',
  `sevenscore` varchar(50) DEFAULT NULL COMMENT '7',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

-- 增加牲畜类别其他
INSERT INTO `tmrwatch2`.`dictlist`(`pid`, `label`, `value`, `orderby`) VALUES (17, '其他', '6', 6)





downloadplandtl2   `pastureid`, `pid`   索引


feedpdate  调整索引顺序 `pastureid`, `date`, `barid`


feedpdate   索引  	pastureid, date, ftid	  

fpdetaildate  调整索引顺序  `date`, `barid`, `pastureid`, `times`





pennsieve,dungsieve,dungscore,bodyscore







CREATE TABLE `log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `method` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `data` longtext DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;











ALTER TABLE `fpdetail` 
MODIFY COLUMN `tratio` decimal(38, 3) NULL DEFAULT NULL COMMENT '班次比例' AFTER `times`;




UPDATE `apisql` SET `sqlstr` = 'SELECT IFNULL(GROUP_CONCAT(fpd.`times`,\':\',round(fpd.tratio,( SELECT inforvalue FROM sysopt WHERE sysopt.`pastureid`=? AND sysopt.`inforname`= \'decimalRate\' )),\':\',\r\nROUND(IF((SELECT inforvalue FROM sysopt WHERE sysopt.`pastureid`=bar.pastureid AND sysopt.`inforname`= \'isEnableSupplyFeed\')=1,fpd.weight,fpd.weight*(1-ptsrate)), 2)),\r\n \r\n CASE (SELECT inforvalue FROM sysopt \r\nWHERE sysopt.`pastureid`=bar.pastureid  AND sysopt.`inforname`= \'times\')\r\nWHEN 1 THEN\r\n\'1:100:0\'\r\nWHEN 2 THEN\r\n\'1:50:0,2:50:0\'\r\nWHEN 3 THEN\r\n\'1:30:0,2:30:0,3:40:0\'\r\nWHEN 4 THEN\r\n\'1:25:0,2:25:0,3:25:0,4:25:0\'\r\nEND \r\n) timesopt,\r\nIFNULL(GROUP_CONCAT(fpd.`times`,\':\',fpd.tratio,\':\',\r\nROUND(IF((SELECT inforvalue FROM sysopt WHERE sysopt.`pastureid`=bar.pastureid AND sysopt.`inforname`= \'isEnableSupplyFeed\')=1,fpd.supplement,fpd.supplement*(1-ptsrate)),2)),\r\n \r\n if(fp.ptsfid !=-1,\"\",\r\n CASE (SELECT inforvalue FROM sysopt \r\nWHERE sysopt.`pastureid`=bar.pastureid  AND sysopt.`inforname`= \'times\')\r\n            WHEN 1 THEN\r\n\'1:100:0\'\r\nWHEN 2 THEN\r\n\'1:50:0,2:50:0\'\r\nWHEN 3 THEN\r\n\'1:30:0,2:30:0,3:40:0\'\r\nWHEN 4 THEN\r\n\'1:25:0,2:25:0,3:25:0,4:25:0\'\r\nEND \r\n)) supplement,\r\nsum(fpd.weight) as todayweight,\r\n\r\nsum(supplement) as todaysupplement,\r\n\r\n(sum(fpd.weight)+sum(supplement)) as todayfeed, \r\nbar.`bname` barname,TRIM(bar.`id`)  barid,\r\n(SELECT bm.cowsum FROM barmilk bm WHERE bm.`barid` =bar.`id` AND bm.pastureid=bar.pastureid ORDER BY bm.productdate DESC LIMIT 1) softccount,\r\n(SELECT bigcowclass FROM barmilk bm WHERE bm.`barid` =bar.`id` AND bm.pastureid=bar.pastureid ORDER BY bm.productdate DESC LIMIT 1) bigcowclass,\r\nccount,ROUND(ratio,2)ratio,ROUND(ccountratio,2)ccountratio,TRIM(ftid) ftid,TRIM(ptsfid)ptsfid,\r\n  if((SELECT inforvalue FROM sysopt WHERE sysopt.`pastureid`=bar.pastureid AND sysopt.`inforname`= \'isEnableSupplyFeed\') = 1,round(ftweight,2),ROUND(ftweight,2))  feedweight,\r\n\r\nROUND(ftweight,2)ftweight,ROUND(supplyweight,2)supplyweight,ft.`tname` ftname,ft1.`tname` ptsfname,\r\nTRIM(fp.id) id,\r\nTRIM(bar.pastureid)pastureid\r\nFROM\r\nbar \r\nLEFT JOIN feedp fp\r\nON bar.`id` = fp.`barid`  AND fp.pastureid=?\r\nLEFT JOIN fpdetail fpd\r\nON fp.`barid` = fpd.`barid`  AND fpd.pastureid=? AND fpd.`times`<=(SELECT inforvalue FROM sysopt \r\nWHERE sysopt.`pastureid`= ? AND sysopt.`inforname`= \'times\')\r\nLEFT JOIN `feedtemplet` ft\r\nON ft.id = fp.`ftid`  and  ft.pastureid=bar.pastureid\r\nLEFT JOIN `feedtemplet` ft1\r\nON ft1.id = fp.`ptsfid` and  ft1.pastureid=bar.pastureid\r\nWHERE bar.pastureid=?  AND bar.enable=1\r\nAND (bar.id = ? or ? = \'\')\r\nAND (ft.`tname` like concat(\"%\",?,\"%\") or ? = \'\')\r\nGROUP BY bar.`id`\r\nORDER BY bar.sort,bar.id', `params` = 'pastureid,pastureid,pastureid,pastureid,pastureid,barid,barid,tname,tname' WHERE `id` = 1327 ;



UPDATE `apisql` SET `sqlstr` = 'SELECT \r\n\'牲畜类别\' typestr,tem.times,\r\nIFNULL(tem.fname,\'无类别\')fname ,\r\nifnull(ROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureId = ? )),0)lweight,\r\nifnull(ROUND(sum(if(tem.actualweightminus<0,0,tem.actualweightminus)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\"  and pastureId = ? )),0)actualweightminus\r\n FROM (\r\n \r\n \r\nSELECT \r\nd2.`type`,\r\n IFNULL((SELECT c.classname from  cowclass c   where c.id = (select f.ccid from feedtemplet f   where f.id = d.tempid and f.pastureid = d.pastureid ) and c.pastureid = d.pastureid  ),\'无类别\') fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n )lweight,\r\n ft.sort,\r\n  SUM(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n )actualweightminus\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id` \r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date and d2.`type` = 0 \r\nleft JOIN `feedtemplet` ft\r\nON d.`tempid` = ft.id\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ?\r\nGROUP BY IFNULL((SELECT c.classname from  cowclass c   where c.id = (select f.ccid from feedtemplet f   where f.id = d.tempid and f.pastureid = d.pastureid ) and c.pastureid = d.pastureid  ),\'无类别\'),IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.fname\r\nORDER BY tem.fname,tem.feedname', `params` = 'pastureid,pastureid,pastureid,startTime,stopTime,fname,fname' WHERE `id` = 1918;


UPDATE `sysopt` SET `inforvalue` = '0' WHERE inforname = 'decimalPlaces';
UPDATE `sysopt` SET `inforvalue` = '0' WHERE inforname = 'decimalRate';









UPDATE `apisql` SET `sqlstr` = 'SELECT \r\ntrim(tem.feedname) feedname\r\n FROM (\r\n \r\nSELECT \r\nd2.`type`,\r\n IFNULL((SELECT cowclass FROM `barmilk` bm WHERE bm.`pastureid` = d2.`pastureid` AND bm.`barid` = d2.`fbarid` AND bm.`productdate` <= d2.`date` ORDER BY bm.`productdate` LIMIT 1),\'无类别\') fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif((\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)<0,0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\n feed.sort sort,\r\n  SUM(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n )actualweightminus\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id` \r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date  AND d2.type = 0\r\n\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nLEFT JOIN feed\r\nON feed.id=d1.fid\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY IFNULL((SELECT cowclass FROM `barmilk` bm WHERE bm.`pastureid` = d2.`pastureid` AND bm.`barid` = d2.`fbarid` AND bm.`productdate` <= d2.`date` ORDER BY bm.`productdate` LIMIT 1),\'无类别\'),IFNULL(ftd.`fname`,d1.`fname` )\r\nHAVING d2.`type` = 0\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY tem.sort' WHERE `id` = 1936;


UPDATE `apisql` SET `sqlstr` = ' \r\n(\r\n\r\n \r\nSELECT \r\n\'牲畜类别\' typestr,tem.times,\r\nIFNULL(tem.fname,\'无类别\')fname , trim(tem.feedname) as feedname,\r\nROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))lweight,\r\nROUND(if(tem.actualweightminus<0,0,tem.actualweightminus),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\nROUND(if(ABS(tem.actualweightminus-tem.lweight)<0,0,ABS(tem.actualweightminus-tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?)) diff,\r\nIFNULL(CONCAT(ROUND(ABS(tem.actualweightminus-tem.lweight)/tem.lweight*100,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? )),\'%\'),\'0.00%\') diffrate\r\n FROM (\r\n \r\nSELECT \r\nd2.`type`,\r\n IFNULL((SELECT c.classname from  cowclass c   where c.id = (select f.ccid from feedtemplet f   where f.id = d.tempid and f.pastureid = d.pastureid ) and c.pastureid = d.pastureid  ),\'无类别\') fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif((\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)<0,0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\n IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY IFNULL((SELECT c.classname from  cowclass c   where c.id = (select f.ccid from feedtemplet f   where f.id = d.tempid and f.pastureid = d.pastureid ) and c.pastureid = d.pastureid  ),\'无类别\'),IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nORDER BY tem.fname,tem.feedname\r\n\r\n)\r\n\r\n\r\n\r\n\r\n\r\n\r\nUNION ALL\r\n\r\n\r\n\r\n(\r\n\r\nSELECT \r\n\'牲畜类别\' typestr,tem.times,\r\n\"合计\" fname ,trim(tem.feedname) as feedname,\r\nROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?))lweight,\r\nROUND(sum(if(tem.actualweightminus<0,0,tem.actualweightminus)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\n0 diff,\r\n0 diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\r\n IFNULL((SELECT c.classname from  cowclass c   where c.id = (select f.ccid from feedtemplet f   where f.id = d.tempid and f.pastureid = d.pastureid ) and c.pastureid = d.pastureid  ),\'无类别\') fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif(\r\n(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	) < 0,\r\n	0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\nIFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus ,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY IFNULL((SELECT c.classname from  cowclass c   where c.id = (select f.ccid from feedtemplet f   where f.id = d.tempid and f.pastureid = d.pastureid ) and c.pastureid = d.pastureid  ),\'无类别\'),IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY  tem.fname,tem.feedname\r\n) ' WHERE `id` = 1585;



UPDATE `apisql` SET `sqlstr` = '\r\n(\r\n\r\n \r\nSELECT \r\n\'日期\' typestr,tem.times,\r\nIFNULL(tem.fname,\'\')fname ,trim(tem.feedname) feedname,\r\nROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))lweight,\r\nROUND(if(tem.actualweightminus<0,0,tem.actualweightminus),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\nROUND(if(ABS(tem.actualweightminus-tem.lweight)<0,0,ABS(tem.actualweightminus-tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?)) diff,\r\nIFNULL(CONCAT(ROUND(ABS(tem.actualweightminus-tem.lweight)/tem.lweight*100,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? )),\'%\'),\'0.00%\') diffrate\r\n FROM (\r\n \r\nSELECT \r\nd2.`type`,\r\n     DATE_FORMAT(d.`mydate`, \'%Y-%m-%d\')  fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif((\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)<0,0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\n IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY d.`mydate`,IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nORDER BY tem.fname,tem.feedname\r\n\r\n)\r\n\r\n\r\n\r\n\r\n\r\n\r\nUNION ALL\r\n\r\n\r\n\r\n(\r\n\r\nSELECT \r\n\'日期\' typestr,tem.times,\r\n\"合计\" fname ,trim(tem.feedname) feedname,\r\nROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?))lweight,\r\nROUND(sum(if(tem.actualweightminus<0,0,tem.actualweightminus)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\n0 diff,\r\n0 diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\r\n DATE_FORMAT(d.`mydate`, \'%Y-%m-%d\')  fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif(\r\n(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	) < 0,\r\n	0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\nIFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus ,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY d.`mydate`,IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY  tem.fname,tem.feedname\r\n)' WHERE `id` = 1587;




UPDATE `apisql` SET `sqlstr` = '\r\n(\r\n\r\n \r\nSELECT \r\n\'TMR名称\' typestr,tem.times,\r\nIFNULL(tem.fname,\'\')fname ,trim(tem.feedname) feedname,\r\nROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))lweight,\r\nROUND(if(tem.actualweightminus<0,0,tem.actualweightminus),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\nROUND(if(ABS(tem.actualweightminus-tem.lweight)<0,0,ABS(tem.actualweightminus-tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?)) diff,\r\nIFNULL(CONCAT(ROUND(ABS(tem.actualweightminus-tem.lweight)/tem.lweight*100,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? )),\'%\'),\'0.00%\') diffrate\r\n FROM (\r\n \r\nSELECT \r\nd2.`type`,\r\nd.`tmrtname`  fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif((\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)<0,0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\n IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY  d.`tmrid`,IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nORDER BY tem.fname,tem.feedname\r\n\r\n)\r\n\r\n\r\n\r\n\r\n\r\n\r\nUNION ALL\r\n\r\n\r\n\r\n(\r\n\r\nSELECT \r\n\'TMR名称\' typestr,tem.times,\r\n\"合计\" fname ,trim(tem.feedname) feedname,\r\nROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?))lweight,\r\nROUND(sum(if(tem.actualweightminus<0,0,tem.actualweightminus)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\n0 diff,\r\n0 diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\r\nd.`tmrtname`  fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif(\r\n(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	) < 0,\r\n	0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\nIFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus \r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\n\r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY d.`tmrid`,IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY  tem.fname,tem.feedname\r\n)' WHERE `id` = 1588;



UPDATE `apisql` SET `sqlstr` = '\r\n(\r\n\r\n \r\nSELECT \r\n\'班次\' typestr,tem.times,\r\nIFNULL(tem.fname,\'\')fname ,trim(tem.feedname) feedname,\r\nROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))lweight,\r\nROUND(if(tem.actualweightminus<0,0,tem.actualweightminus),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\nROUND(if(ABS(tem.actualweightminus-tem.lweight)<0,0,ABS(tem.actualweightminus-tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?)) diff,\r\nIFNULL(CONCAT(ROUND(ABS(tem.actualweightminus-tem.lweight)/tem.lweight*100,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? )),\'%\'),\'0.00%\') diffrate\r\n FROM (\r\n \r\nSELECT \r\nd2.`type`,\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   fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif((\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)<0,0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\n IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY  d.times,IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nORDER BY tem.fname,tem.feedname\r\n\r\n)\r\n\r\n\r\n\r\n\r\n\r\n\r\nUNION ALL\r\n\r\n\r\n\r\n(\r\n\r\nSELECT \r\n\'班次\' typestr,tem.times,\r\n\"合计\" fname ,trim(tem.feedname) feedname,\r\nROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?))lweight,\r\nROUND(sum(if(tem.actualweightminus<0,0,tem.actualweightminus)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\n0 diff,\r\n0 diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\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   fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif(\r\n(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	) < 0,\r\n	0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\nIFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus \r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\n\r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY d.times,IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY  tem.fname,tem.feedname\r\n)' WHERE `id` = 1589 ;


UPDATE `apisql` SET `sqlstr` = '\r\n(\r\n\r\n \r\nSELECT \r\n\'车次\' typestr,tem.times,\r\nIFNULL(tem.fname,\'\')fname ,trim(tem.feedname) feedname,\r\nROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))lweight,\r\nROUND(if(tem.actualweightminus<0,0,tem.actualweightminus),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\nROUND(if(ABS(tem.actualweightminus-tem.lweight)<0,0,ABS(tem.actualweightminus-tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?)) diff,\r\nIFNULL(CONCAT(ROUND(ABS(tem.actualweightminus-tem.lweight)/tem.lweight*100,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? )),\'%\'),\'0.00%\') diffrate\r\n FROM (\r\n \r\nSELECT \r\nd2.`type`,\r\nCONCAT(d.`projname`,\'(\',DATE_FORMAT(d.`mydate`, \'%m-%d\'),\')\') fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif((\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)<0,0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\n IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY CONCAT(d.`projname`,\'(\',DATE_FORMAT(d.`mydate`, \'%m-%d\'),\')\'),IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nORDER BY tem.fname,tem.feedname\r\n\r\n)\r\n\r\n\r\n\r\n\r\n\r\n\r\nUNION ALL\r\n\r\n\r\n\r\n(\r\n\r\nSELECT \r\n\'车次\' typestr,tem.times,\r\n\"合计\" fname ,trim(tem.feedname) feedname,\r\nROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =?))lweight,\r\nROUND(sum(if(tem.actualweightminus<0,0,tem.actualweightminus)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid =? ))actualweightminus,\r\n0 diff,\r\n0 diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\r\nCONCAT(d.`projname`,\'(\',DATE_FORMAT(d.`mydate`, \'%m-%d\'),\')\')  fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\nif(\r\n(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	) < 0,\r\n	0,\r\n	(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n	)\r\n )lweight,\r\nIFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n  SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus ,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ? \r\nGROUP BY CONCAT(d.`projname`,\'(\',DATE_FORMAT(d.`mydate`, \'%m-%d\'),\')\'),IFNULL(ftd.`fname`,d1.`fname` )\r\n\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY  tem.fname,tem.feedname\r\n)' WHERE `id` = 1590;


UPDATE `scheduled_up` SET `datasql` = '\r\n	\r\n	\r\n	select  date_format(d.mydate,\'%Y-%m-%d\') loadDate,d.SORT tmrNo,d.Times loadShift,\r\n	IFNULL(ft.id,((SELECT feedtempletid FROM downloadplandtl2 d2 WHERE d2.pid=d.id LIMIT 1))) recipeId, \r\n	IFNULL(d.templetName ,((SELECT feedtempletName FROM downloadplandtl2 d2 WHERE d2.pid=d.id LIMIT 1))) recipeName, \r\n	ifnull(f.feedcode,\"\") as ingId,de.Fname as ingName,ifnull(fc.FCNAME,\"\")  ingType,\r\n	ifnull(f.dry,\"\") dmPct,de.SORT mixNo,de.feedallowratio allowableError, de.LWEIGHT expWeight,\r\n	de.ActualWeightMinus actualWeight,ifnull(date_format(timestamp(de.InTime,CONCAT(\'-\',(select processTime from downloadplandtl1_exec where pid = de.pid and sort = de.sort and pastureid = de.pastureid))),\'%Y-%m-%d %H:%i:%s\'),\"\") startTime,\r\n	date_format(de.InTime,\'%Y-%m-%d %H:%i:%s\') 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),\"\") tmrName ,d.tmrtname equipmentId \r\n	from downloadedplan d\r\n		 left join downloadplandtl1 de on de.pid = d.id  and de.type = 0\r\n	LEFT JOIN feed f on de.Fid=f.id and f.pastureid = de.pastureid LEFT JOIN feedtemplet ft on d.templetName=ft.TNAME \r\n	LEFT JOIN feedclass fc on f.FCLASS=fc.id \r\n	where d.mydate=date_format(?,\'%Y-%m-%d\') and d.IsCompleted=1 and d.lpplanType=0  and d.pastureid = ?	 and d.id in (idlist)\r\n	\r\nunion all\r\n\r\nselect  date_format(d.mydate,\'%Y-%m-%d\') loadDate,d.SORT tmrNo,d.Times loadShift,\r\n	IFNULL(ft.id,((SELECT feedtempletid FROM downloadplandtl2 d2 WHERE d2.flpid=d.id LIMIT 1))) recipeId, \r\n	IFNULL(d.templetName ,((SELECT feedtempletName FROM downloadplandtl2 d2 WHERE d2.flpid=d.id LIMIT 1))) recipeName, \r\n	ifnull(f.feedcode,\"\") as ingId,de.Fname as ingName,ifnull(fc.FCNAME,\"\")  ingType,\r\n	ifnull(f.dry,\"\") dmPct,de.SORT mixNo,de.feedallowratio allowableError, de.LWEIGHT expWeight,\r\n	de.ActualWeightMinus actualWeight,ifnull(date_format(timestamp(de.InTime,CONCAT(\'-\',(select processTime from downloadplandtl1_exec where pid = de.pid and sort = de.sort and pastureid = de.pastureid))),\'%Y-%m-%d %H:%i:%s\'),\"\") startTime,\r\n	date_format(de.InTime,\'%Y-%m-%d %H:%i:%s\') 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),\"\") tmrName ,d.tmrtname equipmentId \r\n	from downloadedplan d \r\n			 left join downloadplandtl1 de on de.pid = d.id  and de.type = 0\r\n	LEFT JOIN feed f on de.Fid=f.id and f.pastureid = de.pastureid LEFT JOIN feedtemplet ft on d.templetName=ft.TNAME \r\n	LEFT JOIN feedclass fc on f.FCLASS=fc.id  \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)\r\nORDER BY tmrno', `targetdata` = 'date,pastureid,idlist,date,pastureid,idlist' WHERE `id` = 1;

UPDATE `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\nROUND(d2.lweight * (dd.actualweightminus/ (select sum(actualweightminus) from downloadplandtl1 where pid = dd.pid and type = 0) ),2) 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', `targetdata` = 'date,date,pastureid,idlist' WHERE `id` = 2;

UPDATE `apisql` SET `sqlstr` = '(SELECT \r\n\'栏舍\' typestr,tem.times,\r\nIFNULL(tem.fname,\'无类别\')fname ,trim(tem.feedname) as feedname,\r\nifnull(ROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid = ? )),0)lweight,\r\n\r\n\r\nifnull(ROUND(tem.actualweightminus,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid = ? )),0) actualweightminus,\r\nifnull(ROUND(if(ABS(tem.actualweightminus-tem.lweight)<0,0,ABS(tem.actualweightminus-tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid = ? )),0) diff,\r\nIFNULL(CONCAT(ROUND(ABS(tem.actualweightminus-tem.lweight)/tem.lweight*100,(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid = ? )),\'%\'),\'0.00%\') diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\r\n d2.fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n )lweight,\r\n IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n      SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`   and d2.`type` = 0\r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ?\r\nGROUP BY d2.`fbarid`,IFNULL(ftd.`fname`,d1.`fname` )\r\nHAVING d2.`type` = 0  order by b.sort\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nORDER BY tem.d2sort,tem.feedname\r\n\r\n)\r\n\r\n\r\n\r\nUNION ALL\r\n\r\n(\r\nSELECT \r\n\'栏舍\' typestr,tem.times,\r\n\"合计\" fname ,trim(tem.feedname) as feedname,\r\nifnull(ROUND(sum(if(tem.lweight<0,0,tem.lweight)),(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid = ? )),0)lweight,\r\nifnull(ROUND(if(sum(tem.actualweightminus)<0,0,sum(ROUND(tem.actualweightminus,0))),\r\n\r\n\r\n(select  inforvalue from sysopt  where inforname=\"decimalPlaces\" and pastureid = ? )),0)actualweightminus,\r\n0 diff,\r\n0 diffrate\r\n FROM (\r\n \r\n \r\n \r\nSELECT \r\nd2.`type`,\r\n d2.fname,IFNULL(ftd.`fname`,d1.`fname` ) feedname,d.`times` ,\r\nSUM(\r\n  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`lweight`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n )lweight,\r\n  IFNULL(ftd.`sort`,d1.`sort` ) sort,\r\n	\r\n   SUM(\r\n 	if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)  > 0 ,\r\n  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` )),\r\n	\r\n	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*\r\n  d1.`actualweightminus`*IF(ftd.id IS NULL,1,ftd.`fweight`/(SELECT SUM(ftd2.fweight) FROM `ftdetail` ftd2 WHERE ftd2.`pastureid` =d1.`pastureid` AND ftd2.`ftid` = d1.`fid` ))\r\n	)\r\n )actualweightminus,b.sort as d2sort\r\n \r\n FROM \r\n`downloadedplan` d\r\n\r\nJOIN `downloadplandtl2` d2\r\nON d.`pastureid` = d2.`pastureid` AND d2.`pid` = d.`id`  and d2.`type` = 0 \r\nJOIN `downloadplandtl1` d1\r\nON d.`pastureid` = d1.`pastureid` AND d1.`flpid` = d2.`flpid` AND d1.type = 0 AND d2.`date` = d1.date\r\nLEFT JOIN `ftdetaildate` ftd ON  ftd.`pastureid` =d1.`pastureid` AND ftd.`ftid` = d1.`fid` AND d1.`feedcode` = \'-1\' AND ftd.`date` = d.`mydate` AND ftd.`version` = d.`tversion`\r\nleft join bar b  on b.`pastureid` =d1.`pastureid` and b.id = d2.fbarid \r\n\r\n\r\nWHERE d.`pastureid` = ? AND  d.`mydate` BETWEEN ? AND ?\r\nGROUP BY d2.`fbarid`,IFNULL(ftd.`fname`,d1.`fname` )\r\nHAVING d2.`type` = 0 order by b.sort\r\n\r\n\r\n) tem\r\nwhere tem.fname = ? or ? =\'\'\r\nGROUP BY tem.feedname\r\nORDER BY tem.d2sort,tem.feedname\r\n#tem.fname,tem.feedname\r\n)' WHERE `id` = 1586;







	
	
	