tmrgo1/update.220802.sql

UPDATE `tmrwatch2`.`apisql` SET `sqlstr` = 'SELECT\r\n        (\r\n        SELECT\r\n                SUM( weight )\r\n        FROM\r\n           fpdetaildate fpdv\r\n        WHERE\r\n                fpdv.pastureid = fpd.pastureid\r\n                AND fpdv.barid = fpd.barid\r\n                AND fpdv.date = fpd.date\r\n        ) sumweight,\r\n        TRIM( fd.id ) fpid,\r\n        TRIM( fpd.`id` ) id,\r\n        TRIM( fpd.`pastureid` ) pastureid,\r\n        TRIM( fpd.`barid` ) barid,\r\n        fpd.`barname`,\r\n        fpd.`times` timesin,\r\nCASE\r\n                fpd.`times`\r\n                WHEN 1 THEN\r\n                \'第一班\'\r\n                WHEN 2 THEN\r\n                \'第二班\'\r\n                WHEN 3 THEN\r\n                \'第三班\'\r\n                WHEN 4 THEN\r\n                \'第四班\'\r\n        END times,\r\n        fpd.`tratio`,\r\n        fpd.weight,\r\n        @unlockwe := ROUND(\r\n                IFNULL((\r\n                        SELECT\r\n                                SUM( IFNULL(ftd.fweight,0))\r\n                        FROM\r\n                                `ftdetail` ftd\r\n                        WHERE\r\n                                ftd.pastureid = fpd.`pastureid`\r\n                                AND ftd.ftid = fpd.`ptid`\r\n                                AND ftd.islockcount = 0\r\n                                ),\r\n                        0\r\n                ),\r\n                2\r\n        ) unlockweight,\r\n        @lockwe := ROUND(\r\n                IFNULL((\r\n                        SELECT\r\n                                SUM( ftd.`fweight` )\r\n                        FROM\r\n                                `ftdetail` ftd\r\n                        WHERE\r\n                                ftd.pastureid = fpd.`pastureid`\r\n                                AND ftd.ftid = fpd.`ptid`\r\n                                AND ftd.islockcount = 1\r\n                                ),\r\n                        0\r\n                ),\r\n                2\r\n        ) lockweight,\r\n        @pwe := ROUND( @unlockwe * fpd.ccountradio * fpd.cowcount / 100 + @lockwe * fpd.cowcount, 2 ) pweight,\r\n        @punlockwe := ROUND(\r\n                IFNULL((\r\n                        SELECT\r\n                                SUM( ftd.`fweight` )\r\n                        FROM\r\n                                `ftdetail` ftd\r\n                        WHERE\r\n                                ftd.pastureid = fpd.`pastureid`\r\n                                AND ftd.ftid = fpd.`ptsid`\r\n                                AND ftd.islockcount = 0\r\n                                ),\r\n                        0\r\n                ),\r\n                2\r\n        ) punlockweight,\r\n        @plockwe := ROUND(\r\n                IFNULL((\r\n                        SELECT\r\n                                SUM( ftd.`fweight` )\r\n                        FROM\r\n                                `ftdetail` ftd\r\n                        WHERE\r\n                                ftd.pastureid = fpd.`pastureid`\r\n                                AND ftd.ftid = fpd.`ptsid`\r\n                                AND ftd.islockcount = 1\r\n                                ),\r\n                        0\r\n                ),\r\n                2\r\n        ) plockweight,\r\n        ROUND( @punlockwe * fpd.ccountradio * fpd.cowcount / 100 + @plockwe * fpd.cowcount, 2 ) ptweight,\r\n        fpd.`ptsrate`,\r\n        ( SELECT tname FROM `feedtemplet` ft1 WHERE ft1.pastureid = fpd.pastureid AND ft1.id = fpd.`ptid` ) ptname,\r\n        ( SELECT tname FROM `feedtemplet` ft1 WHERE ft1.pastureid = fpd.pastureid AND ft1.id = fpd.`ptsid` ) ptsname,\r\n        TRIM( fpd.`ptid` ) ptid,\r\n        TRIM( fpd.`ptsid` ) ptsid,\r\n        fpd.`ptuse`,\r\n        fpd.`ptsuse`,\r\n        fpd.cowcount ccount,\r\n        fpd.`ccountradio` ratio,\r\n        ROUND( fpd.`ccountradio` * fpd.`cowcount` / 100, 2 ) ccountratio,\r\n        fd.`softccount`,\r\n        @dryw := ROUND(\r\n                IFNULL((\r\n                        SELECT\r\n                                SUM(\r\n                                IF\r\n                                        (\r\n                                               ftd.preftid <> 0,\r\n                                                ifnull(ftd.`fweight`,0) * IFNULL((\r\n                                                        SELECT\r\n                                                                SUM( ifnull(ftd.`fweight`,0) * ifnull(dry / 100,0) )/\r\n																																SUM( ifnull(ftd.`fweight`,0) )\r\n                                                        FROM\r\n                                                                `ftdetail` ftd\r\n                                                                JOIN feednur f ON f.fid = ftd.fid\r\n                                                                AND f.pastureid = ftd.pastureid\r\n                                                        WHERE\r\n                                                                ftd.pastureid = fpd.`pastureid`\r\n                                                                AND ftd.ftid = fpd.`ptid`\r\n                                                                ),\r\n                                                        0\r\n                                                ),\r\n                                                ftd.`fweight` * dry / 100\r\n                                        )\r\n                                )/ SUM( ftd.`fweight` )\r\n                        FROM\r\n                                `ftdetail` ftd\r\n                                LEFT JOIN feednur f ON f.fid = ftd.fid\r\n                                AND f.pastureid = ftd.pastureid\r\n                        WHERE\r\n                                ftd.pastureid = fpd.`pastureid`\r\n                                AND ftd.ftid = fpd.`ptid`\r\n                                ),\r\n                        0\r\n                ),\r\n                2\r\n        ) dryweight1,\r\n        @acrate := IFNULL((\r\n                SELECT\r\n                        actweight\r\n                FROM\r\n                        `ftdry`\r\n                WHERE\r\n                        ftdry.`pastureid` = fpd.`pastureid`\r\n                        AND ftdry.`barid` = fpd.`barid`\r\n                        AND ftdry.`times` = fpd.`times`\r\n                        AND ftdry.`operatetime` = DATE_ADD( fpd.`date`, INTERVAL - 1 DAY )),\r\n                0\r\n        ) actweightrate,\r\n        @acrate1 := IFNULL((\r\n                SELECT\r\n                        actweight\r\n                FROM\r\n                        `ftdry`\r\n                WHERE\r\n                        ftdry.`pastureid` = fpd.`pastureid`\r\n                        AND ftdry.`barid` = fpd.`barid`\r\n                        AND ftdry.`times` = fpd.`times`\r\n                        AND ftdry.`operatetime` = DATE_ADD( fpd.`date`, INTERVAL - 2 DAY )),\r\n                0\r\n        ) actweightrate1,\r\n        @swyes := ROUND(\r\n                IFNULL((\r\n                        SELECT\r\n                                ROUND( fpd1.weight *( 1-fpd1.ptsrate )* @acrate1 / fpd1.cowcount / fpd1.ccountradio * 100, 2 )\r\n                        FROM\r\n                                `fpdetaildate` fpd1\r\n                        WHERE\r\n                                fpd1.pastureid = fpd.pastureid\r\n                                AND fpd1.barid = fpd.barid\r\n                                AND fpd1.times = fpd.times\r\n                                AND fpd1.date = DATE_ADD( fpd.`date`, INTERVAL - 1 DAY )),\r\n                        0\r\n                ),\r\n                2\r\n        ) swdryw1,\r\n        ifnull( @sw := ROUND( weight *( 1-ptsrate )* @acrate / fpd.cowcount / fpd.ccountradio * 100, 2 ), 0 ) swdryw2,\r\n		\r\n        ROUND( @pwe * @acrate / fpd.cowcount / fpd.ccountradio * 100, 2 ) cydryw,\r\n        ROUND( @dryw * @pwe / fpd.cowcount / fpd.ccountradio * 100, 2 ) dryweight,\r\n				  CONCAT(\r\n               CAST( @sw AS CHAR CHARACTER SET utf8),\r\n                CAST( \'( 昨:\' AS CHAR CHARACTER SET utf8),\r\n                 CAST( @swyes AS CHAR CHARACTER SET utf8),\r\n        IF\r\n                ( @sw > @swyes, CAST( \' + \' AS CHAR CHARACTER SET utf8),  CAST( \' - \' AS CHAR CHARACTER SET utf8) ),\r\n                 CAST( ABS( @sw - @swyes ) AS CHAR CHARACTER SET utf8),\r\n                 CAST( \')\'  AS CHAR CHARACTER SET utf8)\r\n        )  as swdryw,\r\n				if(lpplandtl1.id  != 0, (select  havebutton from  downloadedplan dpl where  dpl.pid = lpplandtl1.lppid  and DATE_FORMAT(dpl.mydate,\'%Y-%m-%e\')  =fpd.`date` and dpl.times = (select times  from lpplan  where id = lpplandtl1.lppid ) order by havebutton limit 1) ,0) as havebutton \r\nFROM\r\n        `fpdetaildate` fpd\r\n        JOIN feedpdate fd ON fd.`pastureid` = fpd.`pastureid`\r\n        AND fd.`barid` = fpd.`barid`\r\n        AND fd.`date` = fpd.`date`\r\n        JOIN bar ON bar.`pastureid` = fpd.`pastureid`\r\n        AND bar.`id` = fpd.barid\r\n				left join \r\n `lpplandtl1date`  lpplandtl1 on lpplandtl1.fpdid = fpd.id and lpplandtl1.date  = fpd.`date`\r\nWHERE\r\n        fpd.`pastureid` = ?\r\n        AND fpd.`date` =  ?\r\n        AND bar.`enable` = 1\r\n        AND fpd.times <= ( SELECT sysopt.inforvalue FROM sysopt WHERE sysopt.`pastureid` = ? AND sysopt.`inforname` = \'times\' )\r\n\r\n\r\n' WHERE `id` = 1708;




-- 新生成  任意车索引修改
ALTER TABLE `downloadedplan` 
DROP INDEX `downloadedplan_uni`,
ADD UNIQUE INDEX `downloadedplan_uni`(`pastureid` ASC, `mydate` ASC, `lpplantype` ASC, `pid` ASC, `tmrid` ASC, `id`) USING BTREE;
-- 新增字段
ALTER TABLE `downloadedplan` 
ADD COLUMN `oldtmrid` bigint NULL AFTER `isUse`;






栏舍配方 ifnull ptsrate
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-ifnull(ptsrate,0))), 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-ifnull(ptsrate,0))),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 ' WHERE `id` = 1327;


新增允许延时偏差
ALTER TABLE `ftdetail` 
ADD COLUMN `deviation` int NULL COMMENT '搅拌延时值允许延时偏差' AFTER `splitftpreid`;

ALTER TABLE `ftdetaildate` 
ADD COLUMN `deviation` int NULL COMMENT '搅拌延时值允许延时偏差' AFTER `version`;

ALTER TABLE `downloadplandtl1` 
MODIFY COLUMN `deviation` int NULL DEFAULT NULL COMMENT '搅拌延时值允许延时偏差' AFTER `type`;

ALTER TABLE `downloadplandtl1_exec` 
ADD COLUMN `deviation` int NULL COMMENT '搅拌延时值允许延时偏差' AFTER `type`;


-- 2022-08-16
CREATE TABLE `remainplanrecord` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pastureid` bigint(20) DEFAULT NULL,
  `eqname` varchar(50) DEFAULT NULL COMMENT '设备名称',
  `eqcode` varchar(50) DEFAULT NULL COMMENT '设备编码',
  `tclassid` int(11) DEFAULT NULL COMMENT '设备类型',
  `tclassname` varchar(50) DEFAULT NULL,
  `barid` bigint(20) DEFAULT NULL COMMENT '栏舍',
  `barname` varchar(50) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL COMMENT '收集时间',
  `weight` decimal(38,2) DEFAULT NULL COMMENT '重量',
  `beginweight` decimal(38,2) DEFAULT NULL COMMENT '开始重量',
  `endweight` decimal(38,2) DEFAULT NULL COMMENT '结束重量',
  `driver` varchar(30) DEFAULT NULL COMMENT '司机',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;



ALTER TABLE `tmrwatch2`.`downloadplandtl1_log` 
ADD COLUMN `InventoryWarning` tinyint(1) NULL AFTER `abnormalMixingDelay`;