Domů Procházet Nápověda
Registrovat se Přihlásit se
shawei_zz
/
tmrgo
1
0
Rozštěpit 0
Soubory Úkoly 0 Pull Requesty 0 Wiki
Strom: 1b791eba25
Větve Značky
tmrgo
/
update.sql

update.sql 57 KB
Historie Surový

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210 
  1. ALTER TABLE `tmrwatch2`.`feedlaid` 
    2. 

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

  3. 

  4. alter table feedtemplet
    5. 

  5.     add updatetime datetime default CURRENT_TIMESTAMP null;
    6. 

  6. 

  7. ALTER TABLE `tmrwatch`.`tmr` 
    8. 

  8. ADD COLUMN `lastinorout` int(1) NULL DEFAULT 0 AFTER `autosecondname`
    9. 

  9. 

  10. 

  11. alter table fpdetail
    12. 

  12.     alter column ptuse set default 0;
    13. 

  13. 

  14. alter table fpdetail
    15. 

  15.     alter column ptsuse set default 0;
    16. 

  16. 

  17.     alter table fpdetail
    18. 

  18.     alter column supplement set default 0;
    19. 

  19. 

  20. alter table fpdetail
    21. 

  21.     alter column ptsrate set default 0;
    22. 

  22. 

  23. 

  24. alter table fpdetail
    25. 

  25.     alter column weight set default 0;
    26. 

  26. 

  27. 

  28. update fpdetail  set ptsrate = 0  where ptsrate is null ;
    29. 

  29. 

  30. update fpdetail  set supplement = 0  where supplement is null ;
    31. 

  31.   
    32. 

  32. 

  33. alter table feedp
    34. 

  34.     alter column supplyweight set default 0;
    35. 

  35. 

  36. -- 修改饲料表顺序
    37. 

  37. -- update
    38. 

  38. --  (SELECT @rownum:=0)r ,feed fn
    39. 

  39. --  set fn.sort =  @rownum := @rownum+1 
    40. 

  40. -- WHERE fn.pastureid = 1647917519 
    41. 

  41. -- ORDER BY fn.sort DESC
    42. 

  42. 

  43. 

  44.         update
    45. 

  45.         (SELECT @rownum:=0)r ,bar fn
    46. 

  46.         set fn.sort =  @rownum := @rownum+1 
    47. 

  47.         WHERE fn.pastureid = 1648637190 
    48. 

  48.         ORDER BY fn.id DESC
    49. 

  49. 

  50. 

  51. -- 宾州筛bug
    52. 

  52. -- `barid`, `times`, `operatetime` 唯一索引
    53. 

  53. -- 宾州筛历史记录表
    54. 

  54. -- CREATE TABLE `pennsieve_history` (
    55. 

  55. --   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    56. 

  56. --   `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
    57. 

  57. --   `times` varchar(50) DEFAULT NULL COMMENT '班次',
    58. 

  58. --   `onerate` decimal(20,2) DEFAULT NULL COMMENT '第一层比例',
    59. 

  59. --   `oneweight` decimal(20,2) DEFAULT NULL COMMENT '第一层重量',
    60. 

  60. --   `tworate` decimal(20,2) DEFAULT NULL COMMENT '第二层比列',
    61. 

  61. --   `twoweight` decimal(20,2) DEFAULT NULL COMMENT '第二层重量',
    62. 

  62. --   `threerate` decimal(20,2) DEFAULT NULL COMMENT '第三层比列',
    63. 

  63. --   `threeweight` decimal(20,2) DEFAULT NULL COMMENT '第三层重量',
    64. 

  64. --   `fourrate` decimal(20,2) DEFAULT NULL COMMENT '第四层比列',
    65. 

  65. --   `fourweight` decimal(20,2) DEFAULT NULL COMMENT '第四层重量',
    66. 

  66. --   `operatetime` datetime DEFAULT NULL COMMENT '操作时间',
    67. 

  67. --   `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
    68. 

  68. --   `pastureid` bigint(20) DEFAULT NULL COMMENT '牧场id',
    69. 

  69. --   `species` varchar(50) DEFAULT NULL COMMENT '种类',
    70. 

  70. --   PRIMARY KEY (`id`)
    71. 

  71. -- ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
    72. 

  72. 

  73. 

  74. -- 配方干物质
    75. 

  75. -- 到体况评分bug
    76. 

  76. CREATE TABLE `ftdry_history` (
    77. 

  77.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    78. 

  78.   `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
    79. 

  79.   `times` varchar(50) DEFAULT NULL COMMENT '班次',
    80. 

  80.   `freshweight` decimal(20,2) DEFAULT NULL COMMENT '新鲜重量',
    81. 

  81.   `dryweight` decimal(20,2) DEFAULT NULL COMMENT '烘干重量',
    82. 

  82.   `thoweight` decimal(20,0) DEFAULT NULL COMMENT '理论干物质',
    83. 

  83.   `actweight` decimal(20,0) DEFAULT NULL COMMENT '实际干物质',
    84. 

  84.   `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
    85. 

  85.   `changeTime` datetime DEFAULT NULL COMMENT '操作时间',
    86. 

  86.   `pastureid` bigint(20) DEFAULT NULL COMMENT '牧场id',
    87. 

  87.   PRIMARY KEY (`id`)
    88. 

  88. ) ENGINE=InnoDB
    89. 

  89. 

  90. 

  91. 

  92. CREATE TABLE `dungandbodyhistory` (
    93. 

  93.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    94. 

  94.   `barname` varchar(50) DEFAULT NULL COMMENT '栏舍名称',
    95. 

  95.   `standardscore` varchar(50) DEFAULT NULL COMMENT '标准分数',
    96. 

  96.   `barid` bigint(20) DEFAULT NULL COMMENT '栏舍id',
    97. 

  97.   `cowsum` varchar(50) DEFAULT NULL COMMENT '牛头数',
    98. 

  98.   `onescore` varchar(50) DEFAULT NULL COMMENT '1',
    99. 

  99.   `twoscore` varchar(50) DEFAULT NULL COMMENT '2',
    100. 

  100.   `threescore` varchar(50) DEFAULT NULL COMMENT '3',
    101. 

  101.   `fourscore` varchar(50) DEFAULT NULL COMMENT '4',
    102. 

  102.   `fivescore` varchar(50) DEFAULT NULL COMMENT '5',
    103. 

  103.   `pastureid` bigint(20) DEFAULT NULL COMMENT '牧场id',
    104. 

  104.   `species` varchar(50) DEFAULT NULL COMMENT '种类',
    105. 

  105.   `changetime` datetime DEFAULT NULL COMMENT '操作时间',
    106. 

  106.   `sixscore` varchar(50) DEFAULT NULL COMMENT '6',
    107. 

  107.   `sevenscore` varchar(50) DEFAULT NULL COMMENT '7',
    108. 

  108.   PRIMARY KEY (`id`)
    109. 

  109. ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
    110. 

  110. 

  111. -- 增加牲畜类别其他
    112. 

  112. INSERT INTO `tmrwatch2`.`dictlist`(`pid`, `label`, `value`, `orderby`) VALUES (17, '其他', '6', 6)
    113. 

  113. 

  114. 

  115. 

  116. 

  117. 

  118. downloadplandtl2   `pastureid`, `pid`   索引
    119. 

  119. 

  120. 

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

  122. 

  123. 

  124. feedpdate   索引  	pastureid, date, ftid	  
    125. 

  125. 

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

  127. 

  128. 

  129. 

  130. 

  131. 

  132. pennsieve,dungsieve,dungscore,bodyscore
    133. 

  133. 

  134. 

  135. 

  136. 

  137. 

  138. 

  139. 

  140. CREATE TABLE `log` (
    141. 

  141.   `id` int(11) NOT NULL AUTO_INCREMENT,
    142. 

  142.   `method` varchar(255) DEFAULT NULL,
    143. 

  143.   `status` varchar(255) DEFAULT NULL,
    144. 

  144.   `data` longtext DEFAULT NULL,
    145. 

  145.   `date` datetime DEFAULT NULL,
    146. 

  146.   PRIMARY KEY (`id`)
    147. 

  147. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    148. 

  148. 

  149. 

  150. 

  151. 

  152. 

  153. 

  154. 

  155. 

  156. 

  157. 

  158. 

  159. ALTER TABLE `fpdetail` 
    160. 

  160. MODIFY COLUMN `tratio` decimal(38, 3) NULL DEFAULT NULL COMMENT '班次比例' AFTER `times`;
    161. 

  161. 

  162. 

  163. 

  164. 

  165. 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 ;
    166. 

  166. 

  167. 

  168. 

  169. 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;
    170. 

  170. 

  171. 

  172. UPDATE `sysopt` SET `inforvalue` = '0' WHERE inforname = 'decimalPlaces';
    173. 

  173. UPDATE `sysopt` SET `inforvalue` = '0' WHERE inforname = 'decimalRate';
    174. 

  174. 

  175. 

  176. 

  177. 

  178. 

  179. 

  180. 

  181. 

  182. 

  183. UPDATE `tmrwatch2`.`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;
    184. 

  184. 

  185. 

  186. UPDATE `tmrwatch2`.`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;
    187. 

  187. 

  188. 

  189. UPDATE `tmrwatch2`.`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(tem.actualweightminus)),\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;
    190. 

  190. 

  191. 

  192. 

  193. UPDATE `tmrwatch2`.`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;
    194. 

  194. 

  195. 

  196. 

  197. 

  198. UPDATE `tmrwatch2`.`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;
    199. 

  199. 

  200. 

  201. 

  202. UPDATE `tmrwatch2`.`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 ;
    203. 

  203. 

  204. 

  205. UPDATE `tmrwatch2`.`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;
    206. 

  206. 

  207. 

  208. UPDATE `tmrwatch3`.`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;
    209. 

  209. 

  210. UPDATE `tmrwatch3`.`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;
    211.