tmrgo/shengjiupdatedownloadedplanDone.sql

CREATE DEFINER=`root`@`%` PROCEDURE `createdownloadedplanUndo`(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 FROM downloadedplan WHERE pastureid = pastureidIN AND mydate = dateIN;
  DELETE FROM downloadplandtl1 WHERE pastureid = pastureidIN AND `date` = dateIN;
  DELETE FROM downloadplandtl2 WHERE pastureid = pastureidIN AND `date` = dateIN;
  DELETE FROM downloadplandtl1_exec WHERE pastureid = pastureidIN AND `date` = dateIN;
  
	CALL createLPPdate(dateIN,pastureidIN,'');
	CALL creatFTdate(dateIN,pastureidIN,'');
	CALL creatFPdate(dateIN,pastureidIN,'');
	CALL createPreReHis(dateIN,pastureidIN,'');
	
 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';
 
 ### 混料与撒料是相同车	
 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(cowcount) FROM lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id` ),
	(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 (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(cowcount) FROM lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id` ),
	(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
	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,
	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 ))
	)
	),
	(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 	  
	  ON DUPLICATE KEY UPDATE downloadplandtl1.lweight = downloadplandtl1.lweight+ftd.fweight/(SELECT SUM(fweight) FROM ftdetail WHERE ftid =ft.id AND pastureid=ft.pastureid )*lpplandtl1.`lweight`
	  ;
	  
	  
 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 lpplan.sel=1 AND lpplandtl1.`tmrid` <> lpplan.`tmrid` AND lpplandtl1.`lweight` > 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; 
  
  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
	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 	  
	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 	  
	GROUP BY lpplan.`id`,lpplandtl1.`tmrid`
	ORDER BY lpplandtl1.sort DESC LIMIT 1 OFFSET amount;
	
     
    
	END WHILE;   
   
     
     
    
 END WHILE;  
 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;
	
 
  
 IF issmallmaterial = '1'  # 是否开启小料
 THEN
	CALL createsmallmaterialplan(dateIN,timesOs,pastureidIN);
 END IF ;
 
 
   
   ################################## 预混计划
   
  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;
 
 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;
     
 UPDATE premixplan
 SET `enable` = 0
 WHERE  premixplan.pastureid =pastureidIN  AND premixplan.enable=1;
 
 
 
	
 
   
   #################剩料计划
 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;
	       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;
 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;
 
   
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;   
   
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;
   
  # 复制
 WHILE dateIN<dateStopIN DO   
      SELECT  DATE_ADD(dateIN,INTERVAL 1 DAY) INTO dateIN;
      DELETE FROM downloadedplan WHERE pastureid = pastureidIN AND mydate = dateIN;
      DELETE FROM downloadplandtl1 WHERE pastureid = pastureidIN AND `date` = dateIN;
      DELETE FROM downloadplandtl2 WHERE pastureid = pastureidIN AND `date` = dateIN;
      DELETE FROM downloadplandtl1_exec WHERE pastureid = pastureidIN AND `date` = dateIN;
      
        CALL createLPPdate(dateIN,pastureidIN,'');
	CALL creatFTdate(dateIN,pastureidIN,'');
	CALL creatFPdate(dateIN,pastureidIN,'');
	CALL createPreReHis(dateIN,pastureidIN,'');
	
       ################
        INSERT INTO `downloadedplan`(pastureid,mydate,projname,datacaptureno,tmrid,tmrtname,havebutton,inbuttontype,intime,completedtime,processtime,iscompleted,lpplantype,times,sort,remark,itemnum,outitems,feedpname,feedpcount,pid,tempid,templetname,thoweight,fpweight,lweight,iweight,oweight,plantime)
	SELECT pastureid,dateIN,projname,datacaptureno,tmrid,tmrtname,havebutton,inbuttontype,intime,completedtime,processtime,iscompleted,lpplantype,times,sort,remark,itemnum,outitems,feedpname,feedpcount,pid,tempid,templetname,thoweight,fpweight,lweight,iweight,oweight,plantime
	FROM downloadedplan
	WHERE pastureid= pastureidIN AND mydate = dateTem;
	
	###########################
	INSERT INTO `downloadplandtl1`(pastureid,pid,flpid,fid,lweight,sort,lastactualweight,actualweight,actualweightminus,havebuttom,begintime,intime,buttontype,optdevice,feedcode,fname,feedsort,feedallowratio,stirdelay,fweight,tmrloadname,dryfeed,pricefeed,outweight,`date`,`type`)
	SELECT d1.pastureid,
	(SELECT id FROM downloadedplan d WHERE d.pastureid = d1.`pastureid` AND d.pid = downloadedplan.`pid` AND d.mydate =dateIN AND d.lpplantype = downloadedplan.`lpplantype` AND d.`projname` =downloadedplan.projname),
	
	d1.flpid,d1.fid,d1.lweight,d1.sort,d1.lastactualweight,d1.actualweight,d1.actualweightminus,d1.havebuttom,d1.begintime,d1.intime,d1.buttontype,
	d1.optdevice,d1.feedcode,d1.fname,d1.feedsort,d1.feedallowratio,d1.stirdelay,d1.fweight,d1.tmrloadname,d1.dryfeed,d1.pricefeed,d1.outweight,dateIN ,d1.`type`
	FROM downloadplandtl1 d1
	INNER JOIN downloadedplan 
	ON downloadedplan.`pastureid` = d1.`pastureid` AND downloadedplan.`id` = d1.pid
	WHERE d1.pastureid= pastureidIN AND `date` = dateTem;
	
	#####################
	INSERT INTO `downloadplandtl2`(pastureid,pid,fbarid,fname,lastactualweight,actualweight,lweight,actualweightminus,sort,havebuttom,begintime,begintag,intime,endtag,buttontype,optdevice,flpid,cowcount,cowclassid,cowclassname,bigcowname,pweight,ccountratio,sumrtime,feedtempletid,feedtempletname,dryweight,sumweight,priceweight,processtime,fpid,`date`,allowratio,TYPE,useinbartype)
	SELECT d2.pastureid,
	
	(SELECT id FROM downloadedplan d WHERE d.pastureid = d2.`pastureid` AND d.pid = downloadedplan.`pid` AND d.mydate =dateIN AND d.lpplantype = downloadedplan.`lpplantype` AND d.`projname` =downloadedplan.projname),
	d2.fbarid,d2.fname,d2.lastactualweight,d2.actualweight,d2.lweight,d2.actualweightminus,d2.sort,d2.havebuttom,d2.begintime,d2.begintag,
	d2.intime,d2.endtag,d2.buttontype,d2.optdevice,d2.flpid,d2.cowcount,d2.cowclassid,d2.cowclassname,d2.bigcowname,d2.pweight,d2.ccountratio,d2.sumrtime,d2.feedtempletid,
	d2.feedtempletname,d2.dryweight,d2.sumweight,d2.priceweight,d2.processtime,d2.fpid,dateIN,allowratio,d2.type,d2.useinbartype
	FROM downloadplandtl2 d2
	INNER JOIN downloadedplan 
	ON downloadedplan.`pastureid` = d2.`pastureid` AND downloadedplan.`id` = d2.pid
	WHERE d2.pastureid= pastureidIN AND d2.`date` = dateTem;
	
	
	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; 
	   
/*	INSERT INTO `downloadplandtl1_exec`(pastureid,pid,flpid,lastactualweight,actualweight,lweight,actualweightminus,begintime,intime,havebuttom,buttontype,optdevice,sort,fname,fcount,tmrloadname,feedallowratio,processtime,stirdelay,date)
	SELECT pastureid,pid,flpid,lastactualweight,actualweight,lweight,actualweightminus,begintime,intime,havebuttom,buttontype,optdevice,sort,fname,fcount,tmrloadname,feedallowratio,processtime,stirdelay,dateIN 
	FROM downloadplandtl1_exec
	WHERE pastureid= pastureidIN AND `date` = dateTem;*/
  END WHILE;
  
  IF code1 = '00000' THEN
    COMMIT;
   SELECT
    'success' msg;
  ELSE
     ROLLBACK;
   SELECT
    msg1 msg;
  END IF;  



END