tmrgo/shengjiinsertFitHistory.sql

CREATE DEFINER=`root`@`%` PROCEDURE `updatedownloadedplanDone`(dateIN  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;
	
	
	
	
	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);
	CALL creatFTdate(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';
	
	### 混料与撒料是相同车	
	
	UPDATE lpplandtl1date SET tmrid = -2 WHERE tmrid = -1 AND pastureid = pastureidIN;
	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 lpplandtl1date lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  
		AND lppid= lpplan.`id` AND lpplandtl1.`tmrid`=lpplan.`tmrid` and lpplan.date = lpplandtl1.date),
		(SELECT SUM(cowcount) FROM lpplandtl1date lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id` AND lpplan.date = lpplandtl1.date),
		(select feedtemplet.`version` from `feedtemplet` where feedtemplet.pastureid = lpplan.`pastureid` and  feedtemplet.`id` =lpplan.`ftid` )
		FROM `lpplandate` 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.date = dateIN
		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 `lpplandtl1date` lpplandtl1 WHERE lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.`id` and lpplandtl1.date = lpplan.date ) >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'),'3'
-- 		),
	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 lpplandtl1date lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  
		AND lppid= lpplan.`id` AND lpplandtl1.`tmrid`=lpplan.`tmrid` AND lpplan.date = lpplandtl1.date),
			(SELECT SUM(cowcount) FROM lpplandtl1date lpplandtl1 WHERE lpplandtl1.pastureid = pastureidIN  AND lppid= lpplan.`id` AND lpplan.date = lpplandtl1.date),
			(SELECT feedtemplet.`version` FROM `feedtemplet` WHERE feedtemplet.pastureid = lpplan.`pastureid` AND  feedtemplet.`id` =lpplan.`ftid` ),lpplandtl1.tmrid
		FROM `lpplandate` lpplan
		JOIN `lpplandtl1date`lpplandtl1 ON lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.`id` AND lpplandtl1.`tmrid` <> lpplan.`tmrid` 
		and lpplan.date = lpplandtl1.date
		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 lpplan.date = dateIN
		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 dv.tmrid = lpplandtl1.`tmrid`) <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 `lpplandtl1date` lpplandtl1
		INNER JOIN lpplandate lpplan
		ON lpplan.`id` = lpplandtl1.`lppid` and lpplan.`pastureid` = lpplandtl1.`pastureid` and lpplan.date = lpplandtl1.date
		LEFT JOIN `fpdetaildate` fd
		ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN  AND fd.date = lpplandtl1.date 
		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 lpplan.date = dateIN 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) <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)  < 1
				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 
				
		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 `lpplandtl1date` lpplandtl1
		INNER JOIN lpplandate lpplan
		ON lpplan.`id` = lpplandtl1.`lppid` AND lpplan.`pastureid` = lpplandtl1.`pastureid` AND lpplan.date = lpplandtl1.date
		WHERE lpplandtl1.pastureid = pastureidIN  AND lpplan.times <= timesOs  AND lpplandtl1.`lweight` > 0 	  
		AND lpplan.sel=1 AND lpplandtl1.`tmrid` <> lpplan.`tmrid`  AND lpplan.date = dateIN
		
-- 		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)  < 1
			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
		
		GROUP BY lpplan.id,lpplandtl1.`tmrid`; 
		
	
	# 日执行子表2
	
	SELECT COUNT(lpplan.id)INTO amount1 FROM `lpplandate` lpplan WHERE pastureid=pastureidIN AND lpplan.times <= timesOs AND lpplan.sel=1 and lpplan.date = dateIN
-- 		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 `lpplandate` lpplan WHERE pastureid=pastureidIN 
			AND lpplan.times <= timesOs AND lpplan.sel=1 AND lpplan.date = dateIN
-- 		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 `lpplandtl1date` 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.date = dateIN
	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 `lpplandtl1date` 	lpplandtl1
		WHERE lpplandtl1.pastureid  = pastureidIN AND lpplandtl1.lppid=lpplanids AND lpplandtl1.date = dateIN
		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 `lpplandtl1date`  lpplandtl1
			INNER JOIN lpplandate lpplan ON
			lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.id and lpplandtl1.date = lpplan.date
			INNER JOIN `fpdetaildate` fd
				ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN and fd.date = lpplandtl1.date
			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 lpplan.date = dateIN 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 `lpplandtl1date`  lpplandtl1
			INNER JOIN lpplandate lpplan ON
			lpplandtl1.`pastureid` = lpplan.`pastureid` AND lpplandtl1.`lppid` = lpplan.id   AND lpplandtl1.date = lpplan.date
			INNER JOIN `fpdetaildate` fd
				ON fd.`id` = lpplandtl1.fpdid AND fd.pastureid = pastureidIN AND fd.date = lpplandtl1.date
			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 lpplan.date = dateIN 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 createsmallmaterialplanHis(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 `premixplandate`  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.date = dateIN
	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 `premixplandate`  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 premixplan.date = dateIN
		
		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 ) < 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'),'11',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 `remainplandate` 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.date = dateIN
			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);
			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 `remainplandate` remainplan
			INNER JOIN rpdetaildate  rpd
			ON rpd.pastureid =remainplan.pastureid  AND rpd.bigid = remainplan.id and rpd.date = remainplan.date
			WHERE remainplan.pastureid  = pastureidIN   
			AND remainplan.times <= timesOs AND remainplan.enable=1 AND remainplan.date = dateIN
			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;
	
	
	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 and 
	downloadplandtl1.`flpid` 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)
		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