tmrgo/shengjicreatedownloadedplanDone.sql

CREATE DEFINER=`root`@`%` PROCEDURE `createdownloadedplanDone`(dateIN  VARCHAR(50),
  dateStopIN  VARCHAR(50),
  pastureidIN BIGINT (20))
label:BEGIN
  DECLARE positionIdS,
  checkResults,
  amount,amount1,
  providerIdA,
  countId2,fttypes INT (20);
 DECLARE
 positionNameS,examineS,standardS,
   noteS,lpplanids,dateTem,timesOs,issmallmaterial,isEnableRemainFeed VARCHAR (100);
  DECLARE i INT(11) DEFAULT 0;
  DECLARE a INT(11) DEFAULT 0;
  DECLARE b INT(11) DEFAULT 0;
  DECLARE msg1 TEXT;
  DECLARE code1 CHAR(5) DEFAULT '00000';
  DECLARE t_error INTEGER DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
      -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
      code1 = RETURNED_SQLSTATE, msg1 = MESSAGE_TEXT;
   END;
  #开启事务
   START TRANSACTION;
   
 
   -- 刷新数据（主要是各个模块的名称保持和基础数据中一致）
  CALL refreshdata(pastureidIN);
  
  -- 删除未执行的计划，已执行的保留
DELETE d,d1,d2,d1e  FROM downloadedplan d
 LEFT JOIN `downloadplandtl1` d1 ON d1.pastureid = d.pastureid AND d1.pid = d.id
 LEFT JOIN `downloadplandtl2` d2 ON d2.pastureid = d.pastureid AND d2.pid = d.id
 LEFT JOIN `downloadplandtl1_exec` d1e ON d1e.pastureid = d.pastureid AND d1e.pid = d.id
 WHERE d.pastureid = pastureidIN AND d.id IN (SELECT * FROM ( SELECT id FROM downloadedplan dv  WHERE  dv.`pastureid` =pastureidIN 
 AND dv.`havebutton`=0 AND dv.`mydate` = dateIN )tem);
  -- 将配方、栏舍配方、撒料计划、预混计划、剩料计划复制到date历史表中
	CALL createLPPdate(dateIN,pastureidIN,'');
	CALL creatFTdate(dateIN,pastureidIN,'');
	CALL creatFPdate(dateIN,pastureidIN,'');
	CALL createPreReHis(dateIN,pastureidIN,'');
	SELECT 2;
	-- 读取基础数据中默认参数配置
 SELECT sysopt.inforvalue INTO timesOs FROM sysopt 
 WHERE sysopt.`pastureid`= pastureidIN AND sysopt.`inforname`='times';
 
 SELECT sysopt.inforvalue INTO issmallmaterial FROM sysopt 
 WHERE sysopt.`pastureid`= pastureidIN AND sysopt.`inforname`='isSmallMaterial';
 
  SELECT sysopt.inforvalue INTO isEnableRemainFeed FROM sysopt 
 WHERE sysopt.`pastureid`= pastureidIN AND sysopt.`inforname`='isEnableRemainFeed';
 
 ################################## 撒料计划
 ### 混料与撒料是相同的tmr车	
 INSERT INTO downloadedplan(pastureid,mydate,projname,datacaptureno,
	tmrid,tmrtname,times,sort,remark,itemnum,outitems,pid,tempid,
	templetname,lweight,lpplantype,plantime,feedpname,feedpcount,tversion)
 
 SELECT 
	lpplan.pastureid,
	dateIN,
-- 	CONCAT(times,'.',
-- 	LPAD(lpplan.sort ,3,'0'),'2'
-- 	),
	CONCAT(times,'.',
	LPAD(lpplan.sort ,3,'0'),'0'
	),
	tmr.`datacaptureno`,
	tmrid,tmrname,
	lpplan.times,
	lpplan.sort,
	lpplan.display,
	0,
	0,
	lpplan.id,
	lpplan.`ftid`,
	lpplan.`ftname`,
	0,
	0,#  类型
	lpplan.`begintime`,
	(SELECT GROUP_CONCAT(barname) FROM lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id` AND lpplandtl1.`tmrid`=lpplan.`tmrid`),
	(select sum(ccount) from feedp where barid in(SELECT barid FROM lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id`) and feedp.pastureid = pastureidIN ),
	(SELECT feedtemplet.`version` FROM `feedtemplet` WHERE feedtemplet.pastureid = lpplan.`pastureid` AND  feedtemplet.`id` =lpplan.`ftid` )
	FROM `lpplan` 
	LEFT JOIN tmr ON
	tmr.id = lpplan.`tmrid` AND tmr.`pastureid` = lpplan.`pastureid`
	WHERE  lpplan.pastureid =pastureidIN  AND lpplan.times <= timesOs AND lpplan.sel=1
	AND lpplan.id NOT IN (SELECT * FROM 
	(SELECT  pid FROM downloadedplan d
	  WHERE d.pastureid = pastureidIN AND d.mydate = dateIN
	  AND 
	  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = d.pastureid AND dv.`pid` = d.pid AND dv.`havebutton`=1 AND dv.`mydate` = d.`mydate`) > 0 
	)tem 
	GROUP BY pid)
	AND (SELECT SUM(lweight) FROM `lpplandtl1`
INNER JOIN `fpdetail` fd
			ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = lpplandtl1.pastureid
WHERE lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.`id`) >0
	ORDER BY lpplan.`sort`;
	
  
   ### 混料与撒料是不同车(不同的车要生成两个主计划)
  	
  INSERT INTO downloadedplan(pastureid,mydate,projname,datacaptureno,
	tmrid,tmrtname,times,sort,remark,itemnum,outitems,pid,tempid,
	templetname,lweight,lpplantype,plantime,feedpname,feedpcount,tversion,oldtmrid)
  SELECT 
	lpplan.pastureid,
	dateIN,
	CONCAT(times,'.',
	LPAD(lpplan.sort ,3,'0'),"333"
	),
	tmr.`datacaptureno`,
	lpplandtl1.tmrid,
	lpplandtl1.tmrname,
	lpplan.times,
	lpplan.sort,
	lpplan.display,
 ###
	0,
	0,
 ###
	lpplan.id,
	lpplan.`ftid`,
	lpplan.`ftname`,
	0,
	2,#  类型
	lpplan.`begintime`,
	(SELECT GROUP_CONCAT(barname) FROM lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id` AND lpplandtl1.`tmrid`<>lpplan.`tmrid` ),
		(select sum(ccount) from feedp where barid in(SELECT barid FROM lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id`) and feedp.pastureid = pastureidIN ),
	(SELECT feedtemplet.`version` FROM `feedtemplet` WHERE feedtemplet.pastureid = lpplan.`pastureid` AND  feedtemplet.`id` =lpplan.`ftid` ),lpplandtl1.tmrid
	FROM `lpplan` 
	JOIN `lpplandtl1` ON lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.`id` AND lpplandtl1.`tmrid` <> lpplan.`tmrid` 
	LEFT JOIN tmr ON
	tmr.id = lpplandtl1.`tmrid` AND tmr.`pastureid` = lpplandtl1.`pastureid`
	WHERE  lpplan.pastureid =pastureidIN  AND lpplan.times <= timesOs AND lpplan.sel=1
	AND lpplandtl1.`lweight`> 0
	
	AND  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = lpplan.pastureid AND dv.`pid` = lpplan.id AND dv.`havebutton`=1 AND dv.`mydate` = dateIN and lpplandtl1.tmrid = dv.tmrid ) <1
	GROUP BY lpplan.id,tmr.`id`,lpplandtl1.tmrid  ORDER BY lpplan.`sort`,lpplandtl1.`sort`;
	
  
 # 生成日执行子表1
 INSERT INTO downloadplandtl1(pastureid,pid,flpid,fid,lweight,sort,feedcode,fname,
	feedallowratio,stirdelay,fweight,tmrloadname,`date`,dryfeed,pricefeed,optdevice)
 SELECT 
	lpplandtl1.pastureid,
	(SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplan.`id` AND d.mydate =dateIN AND lpplantype = 0 LIMIT 1),
	lpplandtl1.`lppid`,
	ftd.fid,
	ROUND(IF((SELECT sysopt.inforvalue FROM sysopt WHERE sysopt.`pastureid`= pastureidIN AND sysopt.`inforname`='isLockCount')=0,
	ftd.fweight/(SELECT SUM(fweight) FROM ftdetail WHERE ftid =ft.id AND pastureid=ft.pastureid )*lpplandtl1.`lweight`,  ##未启用锁定牛头数
	IF(ftd.islockcount=0,
	((SELECT SUM(fweight) FROM ftdetail WHERE ftdetail.ftid =ft.id AND ftdetail.pastureid=ft.pastureid AND ftdetail.`islockcount`=0 )
	/
	(SELECT SUM(fweight) FROM ftdetail WHERE ftdetail.ftid =ft.id AND ftdetail.pastureid=ft.pastureid )
	*lpplandtl1.`lweight` *
	ftd.fweight/
	(SELECT SUM(fweight) FROM ftdetail WHERE ftdetail.ftid =ft.id AND ftdetail.pastureid=ft.pastureid AND ftdetail.`islockcount`=0 )),
	((SELECT SUM(fweight) FROM ftdetail WHERE ftdetail.ftid =ft.id AND ftdetail.pastureid=ft.pastureid AND ftdetail.`islockcount`=1 )
	/
	(SELECT SUM(fweight) FROM ftdetail WHERE ftdetail.ftid =ft.id AND ftdetail.pastureid=ft.pastureid )
	*lpplandtl1.`lweight` *
	ftd.fweight/
	(SELECT SUM(fweight) FROM ftdetail WHERE ftdetail.ftid =ft.id AND ftdetail.pastureid=ft.pastureid AND ftdetail.`islockcount`=1 ))
	)
	),2), -- 重量通过撒料计划中的重量，然后找到对应的配方，将配方的各个饲料按比例拆分（并且要先考虑是否锁定牛头数）
	(SELECT COUNT(*)+1 FROM ftdetail WHERE ftdetail.`pastureid` = lpplandtl1.`pastureid` AND ftdetail.`ftid` = ft.`id` AND ftdetail.sort <ftd.sort),
	IF(ftd.preftid=0,CONCAT(f.feedcode," "),'-1'),
	IFNULL(f.fname,ftd.fname),
	f.allowratio,
	ftd.autosecond,
	ftd.fweight,
	ftd.feedgroup,
	dateIN,
	fu.dry,
	f.uprice,
	lpplan.`tmrid`
	FROM `lpplandtl1` 
	INNER JOIN lpplan
	ON lpplan.`id` = lpplandtl1.`lppid` AND lpplan.`pastureid` = lpplandtl1.`pastureid`
	LEFT JOIN `fpdetail` fd
	  ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN
	  INNER JOIN feedtemplet ft
	  ON IF (lpplandtl1.`fttype`=1,fd.`ptid` = ft.id,fd.`ptsid` = ft.id )  AND ft.pastureid = pastureidIN
	  LEFT JOIN ftdetail ftd
	  ON ftd.`ftid` = ft.`id`  AND ftd.pastureid = pastureidIN
	   LEFT JOIN feed f
	  ON ftd.`fid` = f.`id` AND f.pastureid = pastureidIN
	  LEFT JOIN feednur fu
	  ON fu.fid=f.id
	  WHERE lpplandtl1.pastureid = pastureidIN  AND lpplan.times <= timesOs AND lpplan.sel=1 AND lpplandtl1.`lweight` > 0 	  AND
		(SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplan.`id` AND d.mydate =dateIN AND lpplantype = 0 LIMIT 1) IS NOT NULL
-- 			AND  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = lpplan.pastureid AND dv.`pid` = lpplan.id AND dv.`havebutton`=1 AND dv.`mydate` = dateIN) <2 
			and 	(SELECT count(1) FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplan.`id` AND d.mydate =dateIN AND lpplantype = 0 and d.havebutton = 1 LIMIT 1)  =0
		  
	  ON DUPLICATE KEY UPDATE downloadplandtl1.lweight = IFNULL(ROUND(downloadplandtl1.lweight+ftd.fweight/(SELECT SUM(fweight) FROM ftdetail WHERE ftid =ft.id AND pastureid=ft.pastureid )*lpplandtl1.`lweight`,2),0)
	  ;
	  
		
		
 INSERT INTO downloadplandtl1(pastureid,pid,flpid,fid,lweight,sort,feedcode,fname,
	feedallowratio,stirdelay,fweight,tmrloadname,`date`,dryfeed,pricefeed,optdevice,TYPE)
 SELECT 
	lpplandtl1.pastureid,
	(SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplan.`id` AND d.mydate =dateIN AND lpplantype = 2 AND d.`tmrid` =lpplandtl1.`tmrid` LIMIT 1),
	lpplandtl1.`lppid`,
	lpplan.`tmrid`,
	SUM(lpplandtl1.`lweight`),
	1,
	'',
	lpplan.`tmrname`,
	0,
	0,
	0,
	lpplandtl1.`tmrname`,
	dateIN,
	0,
	0,
	lpplandtl1.`tmrid`,
	1
	FROM `lpplandtl1` 
	INNER JOIN lpplan
	ON lpplan.`id` = lpplandtl1.`lppid` AND lpplan.`pastureid` = lpplandtl1.`pastureid`
	  WHERE lpplandtl1.pastureid = pastureidIN  AND lpplan.times <= timesOs  AND lpplandtl1.`lweight` > 0 	  
	AND lpplan.sel=1 AND lpplandtl1.`tmrid` <> lpplan.`tmrid` AND (SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplan.`id` AND d.mydate =dateIN AND lpplantype = 2 AND d.`tmrid` =lpplandtl1.`tmrid` LIMIT 1) IS NOT NULL
	
-- 		AND  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = lpplan.pastureid AND dv.`pid` = lpplan.id AND dv.`havebutton`=1 AND dv.`mydate` = dateIN) <2 
		
		and 	(SELECT count(1) FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplan.`id` AND d.mydate =dateIN AND lpplantype = 2 AND d.`tmrid` =lpplandtl1.`tmrid` and d.havebutton = 1 LIMIT 1)  =0
		
	 GROUP BY lpplan.id,lpplandtl1.`tmrid`; 
  	  
 
   # 日执行子表2
   
SELECT COUNT(lpplan.id)INTO amount1 FROM `lpplan` WHERE pastureid=pastureidIN AND lpplan.times <= timesOs AND lpplan.sel=1
-- 	AND  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = lpplan.pastureid AND dv.`pid` = lpplan.id AND dv.`havebutton`=1 AND dv.`mydate` = dateIN) <2   
; 
  
  WHILE amount1>0 DO
     SELECT amount1-1 INTO amount1;
     
     SELECT lpplan.id INTO lpplanids FROM `lpplan` WHERE pastureid=pastureidIN AND lpplan.times <= timesOs AND lpplan.sel=1
-- 		 	AND  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = lpplan.pastureid AND dv.`pid` = lpplan.id AND dv.`havebutton`=1 AND dv.`mydate` = dateIN) <2  
			
	 group by lpplan.id ORDER BY id DESC LIMIT 1 OFFSET amount1;
	
	
  SELECT COUNT(lpplandtl1.id)INTO amount FROM `lpplandtl1` 
  INNER JOIN `fpdetail` fd
  ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN
  INNER JOIN feedtemplet ft
  ON IF(lpplandtl1.`fttype`=1,fd.`ptid` = ft.id,fd.`ptsid` = ft.id )  AND ft.pastureid = pastureidIN
  WHERE lpplandtl1.pastureid  = pastureidIN 
  AND lpplandtl1.lppid=lpplanids ;
  SELECT 0,0 INTO a,b;
  WHILE amount>0 DO
	SELECT amount-1 INTO amount;
	
	SELECT lpplandtl1.`fttype` INTO  fttypes FROM `lpplandtl1` 	
	WHERE lpplandtl1.pastureid  = pastureidIN AND lpplandtl1.lppid=lpplanids
	ORDER BY lpplandtl1.sort DESC LIMIT 1 OFFSET amount;
	IF (fttypes = 0)
	THEN 
	SELECT b+1 INTO b;
	END IF;
	IF (fttypes= 1)
	THEN 
	SELECT a+1 INTO a;
	END IF;
	
-- 	INSERT INTO downloadplandtl2
-- 		(pastureid,pid,fbarid,fname,lweight,sort,flpid,
-- 		feedtempletid,feedtempletname,fpid,`date`,allowratio,optdevice)
-- 	SELECT lpplandtl1.`pastureid`,(SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` 
-- 	AND d.pid = lpplandtl1.`lppid` AND d.mydate =dateIN AND lpplantype = IF(lpplandtl1.`tmrid`=lpplan.`tmrid`,0,2)AND lpplandtl1.`tmrid`=d.`tmrid`),
-- 		lpplandtl1.barid,lpplandtl1.`barname`,lpplandtl1.lweight,
-- 		IF(lpplandtl1.`fttype`=1,a+100,b+150),
-- 		lpplandtl1.`lppid`,ft.id ftid,ft.`tname`,
-- 		lpplandtl1.`fpdid`,dateIN,
-- 		(SELECT allowratio FROM bar WHERE bar.`pastureid` =lpplandtl1.`pastureid` AND bar.id = lpplandtl1.`barid` LIMIT 1),
-- 		lpplandtl1.`tmrid`
-- 		FROM `lpplandtl1` 
-- 		INNER JOIN lpplan ON
-- 		lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.id
-- 		INNER JOIN `fpdetail` fd
-- 			ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN
-- 		INNER JOIN feedtemplet ft
-- 			ON IF(lpplandtl1.`fttype`=1,fd.`ptid` = ft.id,fd.`ptsid` = ft.id )  AND ft.pastureid = pastureidIN
-- 	WHERE lpplandtl1.pastureid  = pastureidIN AND lpplandtl1.lppid=lpplanids AND lpplandtl1.`lweight`>0
-- 			AND (SELECT havebutton FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplandtl1.`lppid` 
-- 			AND d.mydate =dateIN AND lpplantype = IF(lpplandtl1.`tmrid`=lpplan.`tmrid`,0,2)AND lpplandtl1.`tmrid`=d.`tmrid`)  != 1 
-- 			
-- 	ORDER BY lpplandtl1.sort DESC LIMIT 1 OFFSET amount;
	
		
	INSERT INTO downloadplandtl2
		(pastureid,pid,fbarid,fname,lweight,sort,flpid,
		feedtempletid,feedtempletname,fpid,`date`,allowratio,optdevice)
	SELECT lpplandtl1.`pastureid`,(SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplandtl1.`lppid` AND d.mydate =dateIN
	 AND lpplantype = IF(lpplandtl1.`tmrid`=lpplan.`tmrid`,0,2)AND lpplandtl1.`tmrid`=d.`tmrid`),
		lpplandtl1.barid,lpplandtl1.`barname`,lpplandtl1.lweight,
		IF(lpplandtl1.`fttype`=1,a+100,b+150),
		lpplandtl1.`lppid`,ft.id ftid,ft.`tname`,
		lpplandtl1.`fpdid`,dateIN,
		(SELECT allowratio FROM bar WHERE bar.`pastureid` =lpplandtl1.`pastureid` AND bar.id = lpplandtl1.`barid` LIMIT 1),
		lpplandtl1.`tmrid`
		FROM `lpplandtl1` 
		INNER JOIN lpplan ON
		lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.id
		INNER JOIN `fpdetail` fd
			ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN
		INNER JOIN feedtemplet ft
			ON IF(lpplandtl1.`fttype`=1,fd.`ptid` = ft.id,fd.`ptsid` = ft.id )  AND ft.pastureid = pastureidIN
	WHERE lpplandtl1.pastureid  = pastureidIN AND lpplandtl1.lppid=lpplanids AND lpplandtl1.`lweight`>0
			AND (SELECT havebutton FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplandtl1.`lppid` AND d.mydate =dateIN AND lpplantype = IF(lpplandtl1.`tmrid`=lpplan.`tmrid`,0,2)AND lpplandtl1.`tmrid`=d.`tmrid`)  != 1 
			
	ORDER BY lpplandtl1.sort DESC LIMIT 1 OFFSET amount;
	
	INSERT INTO downloadplandtl2
		(pastureid,pid,fbarid,fname,lweight,sort,flpid,
		 feedtempletid,feedtempletname,fpid,`date`,allowratio,optdevice,`type`)
	SELECT  lpplandtl1.`pastureid`,(SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplandtl1.`lppid` AND d.mydate =dateIN AND lpplantype = 0),
		lpplandtl1.`tmrid`,lpplandtl1.`tmrname`,SUM(lpplandtl1.lweight),
		IF(lpplandtl1.`fttype`=1,a+100,b+150),
		lpplandtl1.`lppid`,ft.id ftid,ft.`tname`,
		lpplandtl1.`fpdid`,dateIN,
		(SELECT allowratio FROM bar WHERE bar.`pastureid` =lpplandtl1.`pastureid` AND bar.id = lpplandtl1.`barid` LIMIT 1),
		lpplan.`tmrid`,
		1
		FROM `lpplandtl1` 
		INNER JOIN lpplan ON
		lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.id
		INNER JOIN `fpdetail` fd
			ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN
		INNER JOIN feedtemplet ft
			ON IF(lpplandtl1.`fttype`=1,fd.`ptid` = ft.id,fd.`ptsid` = ft.id )  AND ft.pastureid = pastureidIN
	WHERE lpplandtl1.pastureid  = pastureidIN AND lpplandtl1.lppid=lpplanids AND lpplandtl1.`tmrid` <> lpplan.`tmrid` AND lpplandtl1.`lweight`>0
			AND (SELECT havebutton FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplandtl1.`lppid` AND d.mydate =dateIN AND lpplantype = IF(lpplandtl1.`tmrid`=lpplan.`tmrid`,0,2)AND lpplandtl1.`tmrid`=d.`tmrid`)  != 1 
   and (SELECT id FROM downloadedplan d WHERE d.pastureid = lpplandtl1.`pastureid` AND d.pid = lpplandtl1.`lppid` AND d.mydate =dateIN AND lpplantype = 0) is not null	
	GROUP BY lpplan.`id`,lpplandtl1.`tmrid`
	ORDER BY lpplandtl1.sort DESC LIMIT 1 OFFSET amount;
     
    
	END WHILE;   
   
     
     
    
 END WHILE;  
 
 IF issmallmaterial = '1'  # 是否开启小料
 THEN
 ## 开启小料后，会在原来的基础上再生成一个主计划
	CALL createsmallmaterialplan(dateIN,timesOs,pastureidIN);
 END IF ;
 -- 设置为撒料-混料
 UPDATE `downloadedplan` d
 SET d.`lpplantype` = 1 
 WHERE d.pastureid = pastureidIN AND 
 (SELECT COUNT(*) FROM `downloadplandtl2` d2 WHERE d2.pastureid = d.pastureid AND d2.pid = d.id AND d2.`type` = 1 ) > 0 
  AND d.lpplantype = 0 AND d.mydate = dateIN ;
 
 SELECT dateIN INTO dateTem;
  INSERT INTO downloadedplan(pastureid,mydate,projname,datacaptureno,
	tmrid,tmrtname,times,sort,remark,itemnum,outitems,pid,tempid,
	templetname,lweight,lpplantype,plantime,feedpname,feedpcount,tversion)
  SELECT 
	 premixplan.pastureid,dateIN,
		IF(premixplan.lppcode IS NULL, CONCAT(times,'.0000'),
		CONCAT(times,'.',
		LPAD(SUBSTRING_INDEX( premixplan.lppcode, ' ',1 ),3,'0'),'01',LPAD(premixplan.sort,3,'0')
		)
	  ),
	 tmr.`datacaptureno`,
	 tmrid,tmrcode,
	 premixplan.times,
	 premixplan.sort,
	 '预混计划',
	 ###
	 ( SELECT COUNT(*) FROM
	  ftdetail ftd
	   WHERE ftd.`ftid` = premixplan.`ftid` AND ftd.`pastureid`=premixplan.pastureid),
	 0 ,
	 ###
	 premixplan.id,
	 premixplan.`ftid`,
	 premixplan.`ftname`,
	 premixplan.`operateweight`,
	 4,
	 premixplan.`ptime`,
	 '',
	 0,
	 (SELECT feedtemplet.`version` FROM `feedtemplet` WHERE feedtemplet.pastureid = premixplan.`pastureid` AND  feedtemplet.`id` =premixplan.`ftid` )
 FROM `premixplan` 
 INNER JOIN tmr ON
 tmr.pastureid = premixplan.`pastureid` AND tmr.id = premixplan.`tmrid` 
 WHERE  premixplan.pastureid =pastureidIN  AND premixplan.times <= timesOs AND premixplan.enable=1
 AND premixplan.id NOT IN (SELECT * FROM 
	(SELECT  pid FROM downloadedplan d
	  WHERE d.pastureid = pastureidIN AND d.mydate = dateIN
	  AND 
	  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = d.pastureid AND dv.`pid` = d.pid AND dv.`havebutton`=1 AND dv.`mydate` = d.`mydate`) > 0 	
		
	
	)tem 
	GROUP BY pid)
 ;
 
 INSERT INTO downloadplandtl1(pastureid,pid,flpid,fid,lweight,sort,feedcode,fname,
  feedallowratio,stirdelay,fweight,tmrloadname,`date`,dryfeed,pricefeed,optdevice)
SELECT 
	premixplan.pastureid,
	(SELECT id FROM downloadedplan d WHERE d.pastureid = premixplan.`pastureid` AND d.pid = premixplan.`id` AND d.mydate =dateIN AND lpplantype = 4 ),
	premixplan.`id`,
	ftd.fid,
	ftd.fweight/(SELECT SUM(fweight) FROM ftdetail WHERE ftid =ft.id AND pastureid=ft.pastureid )*premixplan.`operateweight`,
	ftd.sort,
	f.feedcode,
	IFNULL(f.fname,ftd.fname),
	f.allowratio,
	ftd.autosecond,
	ftd.fweight,
	ftd.feedgroup,
	dateIN,
	fu.dry,
	f.uprice,
	premixplan.`tmrid`
FROM `premixplan` 
  INNER JOIN feedtemplet ft
  ON premixplan.`ftid` =ft.id AND ft.pastureid = premixplan.pastureid
  LEFT JOIN ftdetail ftd
  ON ftd.`ftid` = ft.`id`  AND ftd.pastureid = premixplan.pastureid
   LEFT JOIN feed f
  ON ftd.`fid` = f.`id` AND f.pastureid = premixplan.pastureid
  LEFT JOIN feednur fu
  ON fu.fid=f.id AND fu.pastureid= premixplan.pastureid
  WHERE premixplan.pastureid = pastureidIN  AND premixplan.times <= timesOs AND premixplan.enable=1
 and   (SELECT count(1) FROM downloadedplan d WHERE d.pastureid = premixplan.`pastureid` AND d.pid = premixplan.`id` AND d.mydate =dateIN AND lpplantype = 4 and d.havebutton = 1 ) =0;
   -- 预混计划一次生成之后会都禁用掉 
 UPDATE premixplan
 SET `enable` = 0
 WHERE  premixplan.pastureid =pastureidIN  AND premixplan.enable=1
 AND premixplan.id NOT IN (SELECT * FROM 
	(SELECT  pid FROM downloadedplan d
	  WHERE d.pastureid = pastureidIN AND d.mydate = dateIN
	  AND 
	  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = d.pastureid AND dv.`pid` = d.pid AND dv.`havebutton`=1 AND dv.`mydate` = d.`mydate`) > 0 
	)tem 
	GROUP BY pid)
;
 
 
	
 
   
   #################剩料计划
 IF isEnableRemainFeed = '1'  # 是否开启剩料
 THEN
    -- 日执行主表
	  INSERT INTO downloadedplan(pastureid,mydate,projname,datacaptureno,
	 tmrid,tmrtname,times,sort,remark,itemnum,outitems,pid,tempid,
	 templetname,lweight,lpplantype,plantime,feedpname,feedpcount)
	 
	 SELECT 
		remainplan.pastureid,
		dateIN,
		IF(remainplan.lppcode IS NULL, CONCAT(times,'.0001'),
			CONCAT(times,'.',
			LPAD(SUBSTRING_INDEX( remainplan.lppcode, ' ',1 ),3,'0'),'31',LPAD(remainplan.sort,3,'0')
			)
		  ),
		
		 tmr.`datacaptureno`,
		 tmrid,tmrcode,
		 remainplan.times,
		 remainplan.sort,
		 '剩料计划',
		 0,
		 1,
		 remainplan.id,
		 NULL,
		 '',
		 0,
		 3,
		 remainplan.`rtime`,
		 bar,
		 IFNULL((SELECT cowsum FROM barmilk WHERE barmilk.pastureid = remainplan.`pastureid`  AND barmilk.`barid`= remainplan.`barid`
		  ORDER BY barmilk.productdate DESC LIMIT 1
		  ),0)
		  
		FROM `remainplan` 
		INNER JOIN tmr ON
		tmr.pastureid = remainplan.`pastureid` AND tmr.id = remainplan.`tmrid` 
		WHERE  remainplan.pastureid =pastureidIN  AND remainplan.times <= timesOs AND remainplan.enable=1
		AND remainplan.id NOT IN (SELECT * FROM 
		(SELECT  pid FROM downloadedplan d
		  WHERE d.pastureid = pastureidIN AND d.mydate = dateIN
		  AND 
		  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = d.pastureid AND dv.`pid` = d.pid AND dv.`havebutton`=1 AND dv.`mydate` = d.`mydate`) > 0 
			
		)tem 
		GROUP BY pid);
   -- 日执行子表2（剩料只有子表2）
	       INSERT INTO downloadplandtl2(pastureid,pid,fbarid,fname,lweight,sort,flpid,useinbartype,fpid,`date`,allowratio,optdevice)
		SELECT remainplan.`pastureid`,
		(SELECT id FROM downloadedplan d WHERE d.pastureid = remainplan.`pastureid` AND d.pid = remainplan.`id` AND d.mydate =dateIN AND lpplantype = 3),
		 rpd.barid,
		 rpd.`bar`,0,
		 rpd.sort,remainplan.`id`,
		 IF(treatmethod='继续饲喂',2, rpd.type),
		 remainplan.`id`,dateIN,
		 (SELECT allowratio FROM bar WHERE bar.`pastureid` =remainplan.`pastureid` AND bar.id = rpd.`barid` LIMIT 1),
		 remainplan.`tmrid`
		FROM `remainplan`
		INNER JOIN rpdetail  rpd
		ON rpd.pastureid =remainplan.pastureid  AND rpd.bigid = remainplan.id
		WHERE remainplan.pastureid  = pastureidIN  
		AND remainplan.times <= timesOs AND remainplan.enable=1
		AND remainplan.id NOT IN (SELECT * FROM 
		(SELECT  pid FROM downloadedplan d
		  WHERE d.pastureid = pastureidIN AND d.mydate = dateIN
		  AND 
		  (SELECT COUNT(*)FROM downloadedplan dv WHERE  dv.`pastureid` = d.pastureid AND dv.`pid` = d.pid AND dv.`havebutton`=1 AND dv.`mydate` = d.`mydate`) > 0 
		)tem 
		GROUP BY pid);
 
 END IF ;
 
 
 
 ############################################################################################################################################  
   
   
  UPDATE downloadplandtl2 
  SET lweighthis = lweight,
  cowclassname = (SELECT `cowclass` FROM `barmilk` WHERE pastureid = downloadplandtl2.`pastureid` AND `barid` = downloadplandtl2.`fbarid` ORDER BY `productdate` DESC LIMIT 1 ),
  cowclassid = (SELECT `cowclassid` FROM `barmilk` WHERE pastureid = downloadplandtl2.`pastureid` AND `barid` = downloadplandtl2.`fbarid`  ORDER BY `productdate` DESC LIMIT 1)
  WHERE pastureid = pastureidIN AND DATE = dateIN;
	 SELECT 5;
 
   -- 将子表1更新到 1e
INSERT INTO downloadplandtl1_exec (id,pastureid,pid,flpid,lweight,optdevice,sort,fname,fcount,tmrloadname,feedallowratio,stirdelay,DATE,TYPE)
SELECT
  id,pastureid,pid,flpid,SUM(lweight),optdevice,sort,GROUP_CONCAT(fname),COUNT(fname),tmrloadname,feedallowratio,stirdelay,DATE,TYPE
  FROM downloadplandtl1
  WHERE downloadplandtl1.`pastureid`= pastureidIN AND downloadplandtl1.date =dateIN 
	GROUP BY pid,sort
	 ORDER BY sort  ON DUPLICATE KEY UPDATE  sort = sort; 
	   
   -- 重新将计划编号生成
CALL updatedownloadprojname(pastureidIN,dateIN); # 
 	
UPDATE downloadedplan SET 
itemnum = (SELECT COUNT(*) FROM `downloadplandtl1` WHERE pastureid = downloadedplan.`pastureid` AND pid =downloadedplan.`id`),
outitems = (SELECT COUNT(*) FROM `downloadplandtl2` WHERE pastureid = downloadedplan.`pastureid` AND pid =downloadedplan.`id`),
lweight =IFNULL(IFNULL((SELECT SUM(lweight) FROM `downloadplandtl2` WHERE pastureid = downloadedplan.`pastureid` AND pid =downloadedplan.`id`),
(SELECT SUM(lweight) FROM `downloadplandtl1` WHERE pastureid = downloadedplan.`pastureid` AND pid =downloadedplan.`id`)),0)
WHERE pastureid = pastureidIN AND mydate = dateIN;
  
  
  IF code1 = '00000' THEN
    COMMIT;
   SELECT
    'success' msg;
  ELSE
     ROLLBACK;
   SELECT
    msg1 msg;
  END IF;  
END