ALTER TABLE ctlm701 ADD INDEX c701_id_task(c701_id_task,c701_var_batch,c701_flag_leaf);
UPDATE `sys006` SET `s006_date_create` = '2019-07-22 11:11:52', `s006_id_creater` = 'c186b3da-1f9e-11e9-a0dc-02004c4f4f50', `s006_flag_state` = '正常', `s006_int_line` = 1, `s006_id_table` = NULL, `s006_id_audit` = NULL, `s006_date_audit` = '2019-07-22 11:11:52', `s006_id_dept` = '186db301-7002-11e9-aa44-00163e13f083', `s006_id_com` = '0e4ca95c-8c60-46f5-8c94-46ab44d9da28', `s006_id_main` = NULL, `s006_id_qry` = 'weight_info_qry', `s006_var_qry` = '根据流水号下载数据', `s006_var_sql` = 'SELECT w01_id,w01_date_create,w01_id_creater,w01_flag_state,w01_int_line,\nw01_date_bill,w01_id_clerk,w01_id_source_table,w01_id_source,w01_bill_no,\nw01_id_table,w01_int_year,w01_id_audit,w01_date_audit,w01_int_month,\nw01_id_dept,w01_id_com,w01_id_main,w01_id_flow,w01_date_audit_act,\nw01_id_item,c102_id_item as ext_c102_id_item__w01_id_item,c102_var_item as ext_c102_var_item__w01_id_item,w01_dec_weight,w01_id_terminal,\nw01_id_unit,w01_id_supp,w01_var_scale,w01_flag_scale,w01_id_pr011,\nw01_dec_batch,w01_dec_min_w,w01_dec_max_w,w01_var_phase,w01_id_prop,\nw01_id_pr21,w01_var_task,w01_id_task,w01_var_batch,w01_var_serial,\nw01_var_item_batch,w01_dec_tare,w01_id_container,w01_var_item_serial,w01_TBTotalRecord,\nw01_dec_bag_qty,w01_id_pack,w01_id_process,w01_dec_stand,w01_process_name,\nw01_process_code,w01_dec_w,w01_id_unit_w,i31_id_supp,i31_var_supp\nFROM weight01\nLEFT JOIN ctlm102 ON w01_id_item = c102_id\nLEFT JOIN inv31 ON i31_id = w01_var_item_serial\nWHERE var_task_supple = (SELECT var_task_supple FROM weight01 WHERE \nw01_var_serial = :var_Serial)\nAND w01_flag_valid <> \'作废\'\nAND w01_id_prop <> \'\'', `s006_var_param` = NULL, `s006_flag_ret` = 'json' WHERE `s006_id` = '736f9858-ac2e-11e9-aa0b-00163e13f083';
DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_r01`;//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `proc_deal_r01`(IN serial VARCHAR(60),IN id_user VARCHAR(60))
BEGIN
	DECLARE count1 INTEGER DEFAULT 0;
	DECLARE var_task VARCHAR(60);
	DECLARE var_batch VARCHAR(60);
	
	#将对应的称量记录中的收料状态变为Y
	UPDATE weight01 SET w01_flag_check = 'Y' WHERE w01_id_flow = serial AND w01_flag_state = '正常' AND w01_flag_valid = '正常';
	
	SELECT var_task_supple,var_batch_supple INTO var_task,var_batch FROM weight01 WHERE w01_id = serial;
	
	SELECT count(1) INTO count1 
	FROM weight01 
	WHERE var_task_supple = var_task
	AND var_batch_supple = var_batch
	AND w01_flag_check <> 'Y' 
	AND w01_flag_state = '正常' 
	AND w01_flag_valid = '正常';
	
	IF count1 = 0 THEN 
		/*
		DELETE FROM inv21 
		WHERE EXISTS(SELECT 1 FROM (SELECT a.w01_var_serial FROM weight01 a WHERE a.var_task_supple = var_task AND a.var_batch_supple = var_batch AND a.w01_flag_state = '正常' AND a.w01_flag_valid = '正常') aa WHERE aa.w01_var_serial = i21_var_serial);
			
		UPDATE inv31,weight01
		SET i31_flag_inv = 'out',
		i31_id_loc = ''
		WHERE var_task_supple = var_task
		AND var_batch_supple = var_batch
		AND w01_flag_state = '正常' 
		AND w01_flag_valid = '正常' 
		AND i31_id = w01_var_serial;
		*/
		UPDATE prod023
		SET pr023_flag_comp = 'Y'
		WHERE pr023_var_batch = var_batch;
	END IF;
	
	
	#插入收料记录
		
	INSERT INTO receive01(r01_id,r01_date_create,r01_id_creater,r01_flag_state,r01_int_line,
	r01_date_bill,r01_id_clerk,r01_id_source_table,r01_id_source,r01_bill_no,
	r01_id_table,r01_int_year,r01_id_audit,r01_date_audit,r01_int_month,
	r01_id_dept,r01_id_com,r01_id_main,r01_id_flow,r01_date_audit_act,
	r01_id_item,r01_var_item,r01_dec_weight,r01_id_terminal,r01_id_unit,
	r01_id_vendor,r01_var_vendor,r01_id_task,r01_var_task,r01_var_item_serial,
	r01_var_item_batch,r01_id_product,r01_var_product,r01_var_batch)
	SELECT 
	UUID() AS r01_id,
	NOW() AS r01_date_create,
	id_user AS r01_id_creater,
	'正常' AS r01_flag_state,
	0 AS r01_int_line,
	
	NULL AS r01_date_bill,
	id_user AS r01_id_clerk,
	'w01' AS r01_id_source_table,
	serial AS r01_id_source,
	'' AS r01_bill_no,
	
	'r01' AS r01_id_table,
	0 AS r01_int_year,
	'' AS r01_id_audit,
	NULL AS r01_date_audit,
	0 AS r01_int_month,
	
	'' AS r01_id_dept,
	'' AS r01_id_com,
	'' AS r01_id_main,
	'' AS r01_id_flow,
	NULL AS r01_date_audit_act,
	
	w01_id_item AS r01_id_item,
	c102_var_item AS r01_var_item,
	SUM(w01_dec_w) AS r01_dec_weight,
	'' AS r01_id_terminal,
	w01_id_unit_w	 AS r01_id_unit,
	
	'' AS r01_id_vendor,
	'' AS r01_var_vendor,
	w01_id_task AS r01_id_task,
	var_task_supple AS r01_var_task,
	w01_var_item_serial AS r01_var_item_serial,
	
	w01_var_item_batch AS r01_var_item_batch,
	w01_process_code AS r01_id_product,
	w01_process_name AS r01_var_product,
	var_batch_supple AS r01_var_batch
	FROM weight01,ctlm102 
	WHERE w01_id_flow = serial
	AND w01_id_item = c102_id
	AND w01_flag_state = '正常'
	AND w01_flag_valid = '正常'
	GROUP BY w01_id_item,c102_var_item,w01_id_unit_w,w01_id_task,var_task_supple,w01_var_item_serial,w01_var_item_batch,w01_process_code,w01_process_name,var_batch_supple;
	
END//
DELIMITER ;

DELIMITER //
DROP PROCEDURE if EXISTs `proc_update_comp_mix`;//
DELIMITER;

DELIMITER //
CREATE PROCEDURE `proc_update_comp_mix`(
	IN `ids` varchar(60),
	IN `batch` VARCHAR(60),
	IN `var_state` VARCHAR(50),
	IN `var_remark` VARCHAR(50),
	IN `operator` VARCHAR(60),
	IN `flag_continue` VARCHAR(10),
	IN dischargetime VARCHAR(60),
	IN dischargeTemp VARCHAR(60),
	IN dischargePH VARCHAR(60),
	IN dischargeWeight VARCHAR(60),
	IN viscosity VARCHAR(60),
	IN strainer VARCHAR(60),
	IN flagQualified VARCHAR(10),
	IN user_confirm VARCHAR(60),
	IN user_principal VARCHAR(60),
	IN mix_auditor VARCHAR(60),
	IN ratio_usage VARCHAR(60),
	IN ratio_produce VARCHAR(60),
	IN var_batch_prod VARCHAR(60),
	In var_prod_act VARCHAR(60),
	IN var_clean_type VARCHAR(60),
	IN `date_start` VARCHAR(60),
	IN `date_end` VARCHAR(60),
	IN `reason_out` VARCHAR(60),
	IN var_group VARCHAR(60)
)
begin
 DECLARE id VARCHAR(60);
 DECLARE cfg_var VARCHAR(60);
 DECLARE result_code varchar(1) DEFAULT '0'; 
 DECLARE id_main VARCHAR(60);
 declare msg TEXT;
 DECLARE validdays INTEGER;
  DECLARE var_custom VARCHAR(60);
	 DECLARE cfg_record_code VARCHAR(60);
	  DECLARE cfg_record_ver VARCHAR(60);
	DECLARE pr02_or01 VARCHAR(60);
	DECLARE pr02_order VARCHAR(60);
	DECLARE or01id VARCHAR(60);
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
 
 BEGIN
 
 GET DIAGNOSTICS CONDITION 1
 msg = MESSAGE_TEXT;
 set result_code = 1;
 END;
 
 START TRANSACTION; 

		select pr02_id into id from prod02 where pr02_bill_no = ids and pr02_flag_state = '正常';
		select s302_var_cfg into cfg_var from sys302 WHERE s302_id_cfg = 'Cfg_finished_product' and s302_flag_state = '正常';
		
		#更新任务批次状态及信息
		update prod023,prod02
		set pr023_flag_mix = 'Y'
		where prod023.pr023_id_main=prod02.pr02_id
		and prod023.pr023_var_batch=batch
		and prod02.pr02_bill_no=ids;

		#所有批次都完成的情况 将任务单状态置为完成
		update prod02 set pr02_flag_comp = 'Y' 
		where prod02.pr02_bill_no=ids 
		and not EXISTS(select 1 from prod023 a where a.pr023_id_main= prod02.pr02_id and (IFNULL(pr023_flag_mix,'N')='N' or pr023_flag_mix = '' )) ;
		
		IF var_state <> '中止任务' THEN
		
			SET id_main = UUID();
			
			UPDATE prod076 SET pr076_id_main = id_main WHERE pr076_id_com = id AND pr076_id_flow = batch;
			
			select s302_var_cfg into validdays from sys302 where s302_id_cfg="Validdays_semiproduct";
			select s302_var_cfg into cfg_record_code from sys302 where s302_id_cfg="Cfg_record_code";
			select s302_var_cfg into cfg_record_ver from sys302 where s302_id_cfg="Cfg_record_ver";
			
			select ifnull(pr02_id_or01,'') into pr02_or01 from prod02 where pr02_id = id;
			
			if pr02_or01!='' then 
				select ifnull(c201_var_corr,'') into var_custom from prod02,order01  
				left join ctlm201 as ctlm201_or01_id_customer on order01.or01_id_customer=ctlm201_or01_id_customer.c201_id 
				where pr02_id_or01=or01_id and pr02_id = id;
			else
				select pr02_var_order into pr02_order from prod02 where pr02_id=id;
				select or01_id into or01id from order01 where or01_var_order=pr02_order order by or01_date_create desc limit 1;
				
				select ifnull(c201_var_corr,'') into var_custom from order01  
				left join ctlm201 as ctlm201_or01_id_customer on order01.or01_id_customer=ctlm201_or01_id_customer.c201_id 
				where or01_id=or01id;
			end if;
			#批记录头信息
			#pr07_id_clerk 取值 pr02_id_creater 记录任务单创建人员
			#pr07_date_bill 取值 pr02_date_create 记录任务单创建时间
			#pr07_id_audit 取值 pr02_id_audit 记录任务单审核人员
			#pr07_date_audit 取值 pr02_date_audit 记录任务单审核时间
			INSERT INTO prod07 (pr07_id,pr07_date_create,pr07_id_creater,pr07_flag_state,pr07_int_line,
			pr07_date_bill,pr07_id_clerk,pr07_id_source_table,pr07_id_source,pr07_bill_no,
			pr07_id_table,pr07_int_year,pr07_id_audit,pr07_date_audit,pr07_int_month,
			pr07_id_dept,pr07_id_com,pr07_id_main,pr07_id_flow,pr07_date_audit_act,
			pr07_id_unit,pr07_var_formula_code,pr07_id_item,pr07_var_item,pr07_var_batch, 
			pr07_dec_qty,pr07_date_prod,pr07_date_expire,pr07_int_valid,pr07_flag_continuous_production,
			pr07_date_equipment_cleaning_start,pr07_date_equipment_cleaning_end,pr07_flag_equipment_cleaning_type,pr07_var_version,pr07_var_remark,
			pr07_date_discharge_time,pr07_var_discharge_temp,pr07_var_discharge_PH,pr07_var_discharge_weight,pr07_var_viscosity,
			pr07_var_strainer,pr07_flag_qualified,pr07_var_user_confirm,pr07_var_user_principal,pr07_var_group,pr07_var_ratio_usage,pr07_var_ratio_produce,pr07_var_batch_prod,pr07_var_custom,pr07_var_record_code,pr07_var_record_ver,pr07_var_prod_act)
			select id_main,
			NOW(),
			operator,
			'正常',
			a.pr02_int_line,
			
			a.pr02_date_create,
			a.pr02_id_creater,
			var_state,
			id,
			a.pr02_bill_no,
			
			'pr07',
			a.pr02_int_year,
			a.pr02_id_audit,
			a.pr02_date_audit,
			a.pr02_int_month,
			
			a.pr02_id_dept,
			a.pr02_id_com,
			var_remark,
			reason_out,
			NOW(),
			
			a.pr02_id_unit,
			pr01_var_code,
			a.pr02_id_item,
			c102_var_item,
			batch,
			
			0 + CAST(a.pr02_dec_batch_w as CHAR) as pr07_dec_qty,
			DATE_FORMAT(a.pr02_date_plan,'%Y-%m-%d'),
			DATE_FORMAT(DATE_ADD(DATE_ADD(DATE(a.pr02_date_plan),INTERVAL c102_int_expire/30 MONTH),INTERVAL -1 DAY),'%Y-%m-%d'),
			c102_int_expire,
			flag_continue,
			
			date_start,
			date_end,
			var_clean_type,
			pr01_var_ver,
			'',
			
			dischargetime,
			dischargeTemp,
			dischargePH,
			dischargeWeight,
			viscosity,
			
			strainer,
			flagQualified,
			user_confirm,
			user_principal,
			var_group,
			
			ratio_usage,
			ratio_produce,
			var_batch_prod,
			var_custom,
			cfg_record_code,
			cfg_record_ver,
			var_prod_act
			from prod02 a,prod01,ctlm102 
			where a.pr02_id = id 
			and pr01_id = a.pr02_id_formula 	
			and a.pr02_id_item = c102_id_item;
		
			#生成批记录
			CALL proc_deal_pr02_record(id, ids, batch,id_main,mix_auditor);
			
			#将当前批次的有效数据都置为投料完成状态
			UPDATE weight01 
			set w01_flag_mix = 'Y' 
			where var_task_supple = ids 
			AND var_batch_supple = batch
			AND w01_flag_state = '正常'
			AND w01_flag_valid = '正常';
			
			update inv31,weight01 
			set i31_flag_inv = "out" 
			where i31_id = w01_id
			and var_task_supple = ids 
			AND var_batch_supple = batch
			AND w01_flag_state = '正常'
			AND w01_flag_valid = '正常';
			
			delete from inv21 
			where i21_var_serial 
			in (select w01_id from weight01 where var_task_supple = ids 
			AND var_batch_supple = batch
			AND w01_flag_state = '正常'
			AND w01_flag_valid = '正常');
			#生成 生产入库单
			IF cfg_var = '1' THEN 
				CALL proc_deal_pr02_inv(id,batch);
			END IF;
		END IF;
		
		/*
		update prod02 set  pr02_flag_comp = 'Y',
		pr02_flag_state_comp = var_state,
		pr02_var_forceend = var_remark 
		where prod02.pr02_bill_no=ids
		and var_state = '终止完成';
		*/
		
		#将对应的混料罐组 设置为待清洗状态
		UPDATE ctlm407 SET c407_flag_container = '3',c407_var_device_no = '',c407_var_cache = '' WHERE c407_var_group = var_group;
		
		#记录在消息中
		INSERT INTO sys318(s318_id,s318_date_create,s318_id_creater,s318_flag_state,s318_int_line,
		s318_id_table,s318_id_audit,s318_date_audit,s318_id_dept,s318_id_com,
		s318_id_main,s318_var_title,s318_var_content,s318_var_dept_pub,s318_date_valid,
		s318_id_post,s318_id_dept_obj,s318_id_post_obj)
		select
		UUID(),NOW(),operator,'正常',1,
		's318','',NOW(),'','',
		'','任务批次完成','',IF(var_state = '正常完成',CONCAT('单据号：',ids,',批次号：',batch,var_state),CONCAT('单据号：',ids,',批次号：',batch,var_state,',原因：',var_remark)),DATE_ADD(now(),INTERVAL 7 DAY),
		'','','';
		
		#记录在日志中
		INSERT INTO log01(l01_id,l01_date_create,l01_id_creater,l01_flag_state,l01_id_audit,
		l01_flag_upload,l01_var_info,l01_var_type)
		SELECT UUID(),NOW(),operator,'正常',operator,
		'0',IF(var_state = '正常完成',CONCAT('单据号：',ids,',批次号：',batch,var_state),CONCAT('单据号：',ids,',批次号：',batch,var_state,',原因：',var_remark)) ,'投料管理';
		
		#将旗下任务单都是完成状态的领料单置为完成
		update out11 set o11_flag_comp='Y'
		where o11_flag_comp='N'
		and not EXISTS(select 1 from prod02 a,out111 where a.pr02_id= out111.o111_id_pr02_id and a.pr02_flag_comp='N' and o111_id_main = o11_id) ;
		
		#SELECT msg;
		IF result_code = '1' THEN 
			ROLLBACK;
	    select CONCAT(result_code,'|更新失败') as result_code ;
    ELSE
			COMMIT;
			select CONCAT(result_code,'|更新成功') as result_code;
    END IF;	
end//
DELIMITER ;

DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_pr02_record`;//
DELIMITER;

DELIMITER //
CREATE PROCEDURE `proc_deal_pr02_record`(
	IN `ids` VARCHAR(60),
	IN `bill_no` VARCHAR(60),
	IN `var_batch` VARCHAR(60),
	IN id_main VARCHAR(60),
	IN mix_auditor VARCHAR(60)
)
BEGIN

	#Routine body goes here...
	DECLARE cur_time datetime;
	DECLARE validdays INTEGER;
	DECLARE Cfg_record_sender VARCHAR(60);
	DECLARE Cfg_record_picker VARCHAR(60);

	select s302_var_cfg into validdays from sys302 where s302_id_cfg="Validdays_unstock";
	
	select s302_var_cfg into Cfg_record_sender from sys302 where s302_id_cfg="Cfg_record_sender";
	select s302_var_cfg into Cfg_record_picker from sys302 where s302_id_cfg="Cfg_record_picker";
	
	SET cur_time = NOW();
	
	#批记录——领料记录
	INSERT INTO prod075(pr075_id,pr075_date_create,pr075_id_creater,pr075_flag_state,pr075_int_line,
	pr075_date_bill,pr075_id_clerk,pr075_id_source_table,pr075_id_source,pr075_bill_no,
	pr075_id_table,pr075_int_year,pr075_id_audit,pr075_date_audit,pr075_int_month,
	pr075_id_dept,pr075_id_com,pr075_id_main,pr075_id_flow,pr075_date_audit_act,
	pr075_date_operate,pr075_id_item,pr075_var_item,pr075_var_batch,pr075_var_serial,
	pr075_dec_weight,pr075_id_unit,pr075_id_operator,pr075_id_checker,pr075_id_picker)
	SELECT UUID(),
	cur_time ,
	'' ,
	'正常' ,
	0 , 
	
	cur_time,
	'' ,
	'i22',
	b.i22_id ,
	'' ,
	
	'pr075' ,
	0 ,
	'' ,
	cur_time,
	0 ,
	
	'' ,
	'' ,
	id_main ,
	'' ,
	cur_time,
	
	b.i22_date_create ,
	b.i22_id_item ,
	b.i22_var_item ,
	b.i22_var_batch ,
	b.i22_var_serial ,
	
	0 + CAST(b.i22_dec_weight as CHAR) AS pr075_dec_weight,
	b.i22_id_unit ,
	Cfg_record_sender,
	'' ,
	Cfg_record_picker 
	FROM inv22 b,(SELECT MAX(i22_date_create) i22_date_create,i22_var_serial FROM inv22,weight01 WHERE var_task_supple = bill_no AND var_batch_supple = var_batch AND w01_var_item_serial = i22_var_serial AND i22_var_source = '领料单' AND i22_flag_state = '正常' GROUP BY i22_var_serial) a 
	WHERE b.i22_date_create = a.i22_date_create 
	AND b.i22_var_source = '领料单'
	ORDER BY b.i22_date_create;
	
	#自动领料
	INSERT INTO prod075(pr075_id,pr075_date_create,pr075_id_creater,pr075_flag_state,pr075_int_line,
	pr075_date_bill,pr075_id_clerk,pr075_id_source_table,pr075_id_source,pr075_bill_no,
	pr075_id_table,pr075_int_year,pr075_id_audit,pr075_date_audit,pr075_int_month,
	pr075_id_dept,pr075_id_com,pr075_id_main,pr075_id_flow,pr075_date_audit_act,
	pr075_date_operate,pr075_id_item,pr075_var_item,pr075_var_batch,pr075_var_serial,
	pr075_dec_weight,pr075_id_unit,pr075_id_operator,pr075_id_checker,pr075_id_picker)
	SELECT UUID(),
	cur_time,
	'' AS pr075_id_creater,
	'正常',
	0 AS pr075_int_line, 
	
	cur_time,
	'' AS pr075_id_clerk,
	'i22',
	b.i22_id AS pr075_id_source,
	'' AS pr075_bill_no,
	
	'pr075' AS pr075_id_table,
	0 AS pr075_int_year,
	'' AS pr075_id_audit,
	cur_time AS pr075_date_audit,
	0 AS pr075_int_month,
	
	'' AS pr075_id_dept,
	'' AS pr075_id_com,
	id_main AS pr075_id_main,
	'' AS pr075_id_flow,
	cur_time AS pr075_date_audit_act,
	
	b.i22_date_create AS pr075_date_operate,
	b.i22_id_item AS pr075_id_item,
	b.i22_var_item AS pr075_var_item,
	b.i22_var_batch AS pr075_var_batch,
	b.i22_var_serial AS pr075_var_serial,
	
	0 + CAST(b.i22_dec_weight as CHAR) AS pr075_dec_weight,
	b.i22_id_unit AS pr075_id_unit,
	Cfg_record_sender,
	'' AS pr075_id_checker,
	Cfg_record_picker
	FROM inv22 b
	WHERE b.i22_id_source = bill_no
	AND b.i22_id_flow = var_batch
	AND b.i22_var_source = '自动领料'
	ORDER BY b.i22_date_create;

	#批记录——称量记录
	INSERT INTO prod071(pr071_id,pr071_date_create,pr071_id_creater,pr071_flag_state,pr071_int_line,
	pr071_date_bill,pr071_id_clerk,pr071_id_source_table,pr071_id_source,pr071_bill_no,
	pr071_id_table,pr071_int_year,pr071_id_audit,pr071_date_audit,pr071_int_month,
	pr071_id_dept,pr071_id_com,pr071_id_main,pr071_id_flow,pr071_date_audit_act,
	pr071_id_item,pr071_var_item,pr071_id_manufacturer,pr071_var_manufacturer,pr071_date_expire,
	pr071_dec_stand,pr071_dec_weight,pr071_id_scale,pr071_var_scale,pr071_date_expire_scale,
	pr071_id_unit,pr071_var_batch)
	SELECT UUID(),
	cur_time,
	'',
	'正常',
	0, 
	
	cur_time,
	w01_id_creater,
	'w01',
	'',
	w01_id_terminal,
	
	'pr071',
	0,
	w01_id_audit,
	cur_time,
	0,
	
	'',
	'',
	id_main,
	'',
	cur_time,
	
	w01_id_item,
	c102_var_item,
	a.i31_id_sup,
	a.c202_var_supp,
	if(c102_flag_stock_un="Y",DATE_ADD(DATE(w01_date_create),INTERVAL validdays DAY),DATE_FORMAT(a.i31_date_expire,'%Y-%m-%d')),
	
	0 ,
	SUM(0 + CAST(w01_dec_w AS CHAR)) AS pr071_dec_weight,
	w01_var_scale,
	'',
	NULL,
	
	w01_id_unit_w,
	w01_var_item_batch 
	FROM weight01 
	LEFT JOIN ctlm102 ON w01_id_item = c102_id
	LEFT JOIN (SELECT i31_id,i31_id_item,i31_var_item,i31_id_sup,c202_var_supp,i31_date_expire,i31_id_supp,i31_var_supp FROM inv31 LEFT JOIN ctlm202 on i31_id_sup = c202_id) a ON w01_var_item_serial = a.i31_id 
	WHERE var_task_supple = bill_no  
	AND var_batch_supple = var_batch
	AND w01_flag_valid = '正常' 
	AND w01_flag_state = '正常' 
	GROUP BY w01_id_creater,w01_id_audit,w01_id_item,w01_var_item_batch,w01_id_terminal,w01_var_scale,w01_id_unit_w,c102_var_item,a.i31_id_sup,a.c202_var_supp,a.i31_date_expire,w01_date_create
	ORDER BY w01_id_item;
	
	UPDATE prod071 SET pr071_dec_stand = convertUnit('kg', pr071_id_unit) * (SELECT sum(pr022_dec_qty) AS qty FROM prod022 WHERE pr022_id_main = ids AND pr022_id_item = pr071_id_item)
	WHERE pr071_id_main = id_main;
	
	#批记录——收料记录
	INSERT INTO prod074(pr074_id,pr074_date_create,pr074_id_creater,pr074_flag_state,pr074_int_line,
	pr074_date_bill,pr074_id_clerk,pr074_id_source_table,pr074_id_source,pr074_bill_no,
	pr074_id_table,pr074_int_year,pr074_id_audit,pr074_date_audit,pr074_int_month,
	pr074_id_dept,pr074_id_com,pr074_id_main,pr074_id_flow,pr074_date_audit_act,
	pr074_date_operate,pr074_id_item,pr074_var_item,pr074_var_batch,pr074_var_serial,
	pr074_dec_weight,pr074_id_unit,pr074_id_operator)
	SELECT 
	UUID(),
	cur_time,
	'' AS pr074_id_creater,
	'正常',
	0,
	
	cur_time,
	'' AS pr074_id_clerk,
	'r01',
	r01_id,
	'' AS pr074_bill_no,
	
	'pr074',
	0,
	'' AS pr074_id_audit,
	NULL,
	0,
	
	'' AS pr074_id_dept,
	'' AS pr074_id_com,
	id_main,
	'' AS pr074_id_flow,
	NULL,
	
	r01_date_create,
	r01_id_item,
	r01_var_item,
	r01_var_item_batch,
	r01_var_item_serial,
	
	r01_dec_weight,
	r01_id_unit,
	r01_id_creater 
	FROM receive01 
	WHERE r01_var_task = bill_no
	AND r01_var_batch = var_batch
	AND r01_flag_state = '正常'
	ORDER BY r01_id_item,r01_date_create;
	
	#批记录——配制过程
	INSERT INTO prod072(pr072_id,pr072_date_create,pr072_id_creater,pr072_flag_state,pr072_int_line,
	pr072_date_bill,pr072_id_clerk,pr072_id_source_table,pr072_id_source,pr072_bill_no,
	pr072_id_table,pr072_int_year,pr072_id_audit,pr072_date_audit,pr072_int_month,
	pr072_id_dept,pr072_id_com,pr072_id_main,pr072_id_flow,pr072_date_audit_act,
	pr072_var_title,pr072_date_begin,pr072_date_end,pr072_var_custom,pr072_var_record
	)
	select UUID(),
	cur_time,
	pr012_id_creater,
	'正常',
	pr012_int_line,
	
	cur_time,
	bbb.c701_id_creater,
	'pr012',
	pr012_id,
	pr012_bill_no,
	
	'pr072',
	pr012_int_year,
	pr012_id_audit,
	cur_time,
	pr012_int_month,
	
	pr012_id_dept,
	bbb.c701_id_com,
	id_main,
	bbb.c701_flag_leaf,
	cur_time,
	
	pr012_var_title,
	bbb.c701_date_begin,
	bbb.c701_date_end,
	bbb.c701_var_custom,
	IFNULL(bbb.record,'')
	from prod02,prod012 LEFT JOIN (select GROUP_CONCAT(a.aaa) as record ,a.c701_id_flow as line1 ,a.c701_date_begin,a.c701_date_end,'' AS c701_var_custom,a.c701_flag_leaf,a.c701_id_creater,a.c701_id_com from (select CONCAT(REVERSE(SUBSTRING_INDEX(REVERSE(c701_var_spell),'_',1)),':',c701_var_desc,c701_var_spell_short) as aaa,c701_id_creater,c701_id_com,c701_id_flow,c701_var_source,c701_date_begin,c701_date_end,c701_flag_leaf
from ctlm701 where c701_id_task = ids AND c701_var_batch = var_batch and c701_flag_leaf = 'record') a GROUP BY c701_id_flow,c701_date_begin,c701_date_end,c701_flag_leaf,c701_id_creater,c701_id_com
UNION 
SELECT '' AS record,c701_id_flow AS line1,c701_date_begin,c701_date_end,c701_var_custom,c701_flag_leaf,c701_id_creater,c701_id_com FROM ctlm701 WHERE c701_id_task = ids AND c701_var_batch = var_batch AND c701_flag_leaf = 'custom') bbb ON pr012_int_line =
bbb.line1 
	where pr012_id_main = pr02_id_formula
	and pr02_id = ids;
	
	#批记录——投料记录
	INSERT INTO prod073(pr073_id,pr073_date_create,pr073_id_creater,pr073_flag_state,pr073_int_line,
	pr073_date_bill,pr073_id_clerk,pr073_id_source_table,pr073_id_source,pr073_bill_no,
	pr073_id_table,pr073_int_year,pr073_id_audit,pr073_date_audit,pr073_int_month,
	pr073_id_dept,pr073_id_com,pr073_id_main,pr073_id_flow,pr073_id_item,
	pr073_var_item,pr073_var_item_batch,pr073_var_phase,pr073_date_mix,pr073_id_container,
	pr073_dec_weight,pr073_id_unit)
	SELECT UUID(),cur_time,c01_id_creater,'正常',c01_int_line,
	cur_time,c01_id_clerk,'c01',c01_id,c01_bill_no,
	'pr073',YEAR(cur_time),mix_auditor,cur_time,MONTH(cur_time),
	c01_id_dept,c01_id_com,id_main,'',c01_id_item,
	c102_var_item,c01_var_item_batch,c01_var_phase,c01_date_create,c01_id_container,
	c01_dec_weight,c01_id_unit
	FROM check01 
	LEFT JOIN ctlm102 ON c01_id_item = c102_id_item 
	WHERE var_task_supple = bill_no
	AND c01_var_batch = var_batch
	AND c01_flag_state = '正常'
	AND c01_flag_valid <> 'N'
	ORDER BY c01_id_item,c01_date_create;
	
	#退库记录 只记录截止批记录生成时的退库记录
	INSERT INTO prod077(pr077_id,pr077_date_create,pr077_id_creater,pr077_flag_state,pr077_int_line,
	pr077_id_source_table,pr077_id_source,pr077_id_main,pr077_date_operate,pr077_id_item,
	pr077_var_item,pr077_var_batch,pr077_var_serial,pr077_dec_weight,pr077_id_unit,
	pr077_id_operator)
	SELECT UUID(),cur_time,'','正常',0,
	'i22',b.i22_id,id_main,b.i22_date_create,b.i22_id_item,
	b.i22_var_item,b.i22_var_batch,b.i22_var_serial,b.i22_dec_weight,b.i22_id_unit,
	b.i22_id_creater
	FROM inv22 b,(SELECT MAX(i22_date_create) i22_date_create,i22_var_serial FROM inv22,weight01 WHERE var_task_supple = bill_no AND var_batch_supple = var_batch AND w01_var_item_serial = i22_var_serial AND i22_date_create >= w01_date_create AND i22_var_source = '退库' AND i22_flag_state = '正常' GROUP BY i22_var_serial) a
	WHERE b.i22_date_create = a.i22_date_create 
	AND i22_var_source = '退库'
	ORDER BY b.i22_date_create;
	
	#自动退库记录
	/*
	INSERT INTO prod077(pr077_id,pr077_date_create,pr077_id_creater,pr077_flag_state,pr077_int_line,
	pr077_id_source_table,pr077_id_source,pr077_id_main,pr077_date_operate,pr077_id_item,
	pr077_var_item,pr077_var_batch,pr077_var_serial,pr077_dec_weight,pr077_id_unit,
	pr077_id_operator)
	SELECT UUID(),cur_time,'','正常',0,
	'i22',b.i22_id,id_main,b.i22_date_create,b.i22_id_item,
	b.i22_var_item,b.i22_var_batch,b.i22_var_serial,b.i22_dec_weight,b.i22_id_unit,
	b.i22_id_creater
	FROM inv22 b
	WHERE b.i22_id_source = bill_no
	AND b.i22_id_flow = var_batch 
	AND b.i22_var_source = '自动退库'
	ORDER BY b.i22_date_create;
	*/
	update prod071,(select c301_var_ip,c302_id_scale,c302_code_scale,c302_date_check from ctlm302,ctlm301 where c302_id_main = c301_id) a 
	set pr071_var_scale = a.c302_code_scale,pr071_date_expire_scale = DATE_FORMAT(a.c302_date_check,'%Y-%m-%d')
	where pr071_id_main = id_main 
	and pr071_bill_no = a.c301_var_ip 
	and pr071_id_scale = a.c302_id_scale;	
END//
DELIMITER ;