ALTER TABLE prod07 ADD INDEX billNoVarBatch(pr07_bill_no,pr07_var_batch);
DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_w01`;//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `proc_deal_w01`(IN id varchar(60) character set utf8  COLLATE utf8_general_ci,IN var_task VARCHAR(60) character set utf8  COLLATE utf8_general_ci)
BEGIN
	#Routine body goes here...
	
	DECLARE w_id_creater VARCHAR(60);
	declare id_item varchar(60);
	
		declare  batchcomplete varchar(60);
		
	declare id_task varchar(60);
	declare id_pr021 varchar(60);

	declare id_source varchar(60);
	declare dec_w decimal(20,7);
	declare dec_p DECIMAL(20,7);
	
	DECLARE dec_out DECIMAL(20,7);
	DECLARE w01_sum DECIMAL(20,7);
	DECLARE pickweight DECIMAL(20,7);
	
	#原任务单号
	DECLARE temp_bill_no VARCHAR(60);
	
	declare var_item_serial VARCHAR(60);
	DECLARE var_scale VARCHAR(60);
	DECLARE id_terminal VARCHAR(60);
	DECLARE var_batch_prod VARCHAR(60);
	DECLARE cfg VARCHAR(60);

	declare dec_weight DECIMAL(20,7);
	declare countrec INTEGER default 0;
	declare int_result integer default 0;
	
	#补料标记
	DECLARE flag_supple VARCHAR(10);
	DECLARE countsw01 INTEGER DEFAULT 0;
	DECLARE countspr022 INTEGER DEFAULT 0;
	#批次收料状态
	DECLARE flag_check VARCHAR(10) DEFAULT 'N';
	#生产批记录id
	DECLARE id_pr07 VARCHAR(60);
	DECLARE validdays INTEGER;
  DECLARE msg TEXT;	
  DECLARE result_code INTEGER DEFAULT 0; 
     ## DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code = 1;
			
			
			 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    begin
    
     -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
      msg = MESSAGE_TEXT;

            SET result_code = 1;
    end ;
		
      START TRANSACTION; 
			
	SELECT w01_flag_supple
	INTO flag_supple
	FROM weight01
	WHERE w01_id = id;

	IF flag_supple = 'Y' THEN
			#补料任务单
			SELECT pr023_flag_comp INTO flag_check
			FROM prod023,weight01,prod06
			WHERE w01_id = id
			AND w01_var_task = pr06_bill_no
			AND pr06_var_batch_ori = pr023_var_batch;

			update weight01 , prod06 
			set w01_id_task = pr06_id,
			w01_bill_no_ori = pr06_var_pr02_bill,
			w01_var_batch_ori = pr06_var_batch_ori,
			w01_flag_check = flag_check
			where w01_id = id
			and w01_var_task = pr06_bill_no;
			
			UPDATE weight01,prod061
			SET w01_id_pr011 = pr061_id_pr011
			WHERE w01_id = id
			AND w01_id_pr21 = pr061_id;
	ELSE
			#正常任务单
			update weight01 ,prod02
			set weight01.w01_id_task=prod02.pr02_id,
			pr02_flag_step = '02' 
			where weight01.w01_id=id
			and weight01.w01_var_task=prod02.pr02_bill_no;
			
	END IF;
	
	SELECT w01_id_creater,w01_id_item,w01_id_task,w01_id_pr21,w01_id_source,w01_dec_weight,
	w01_var_item_serial,w01_var_scale,w01_id_terminal,CASE WHEN w01_flag_supple = 'Y' THEN w01_var_batch_ori ELSE w01_var_batch END,w01_id_com,CASE WHEN w01_flag_supple = 'Y' THEN w01_bill_no_ori ELSE w01_var_task END
	INTO w_id_creater,id_item,id_task ,id_pr021,id_source,dec_weight,
	var_item_serial,var_scale,id_terminal,var_batch_prod,batchcomplete,temp_bill_no
	FROM weight01
	WHERE w01_id = id;
	
	#检查配料记录中 终端批次称量完成的数量是否达到 该批次分配的终端数量
	IF batchcomplete='Y' THEN
	
		SELECT COUNT(1) INTO countsw01 
		FROM weight01 
		WHERE w01_id_task = id_task 
		AND w01_var_batch = var_batch_prod 
		AND w01_id_com = 'Y'
		AND w01_flag_state = '正常'
		AND w01_flag_valid = '正常';
		
		SELECT COUNT(DISTINCT pr022_id_terminal) INTO countspr022 
		FROM prod022 
		WHERE pr022_id_main = id_task;
		
		#所有终端都称完后 将对应的批次记录置为称量完成
		IF countsw01 >= countspr022 THEN
			UPDATE prod023 SET pr023_id_com ='Y'
			WHERE pr023_id_main = id_task
			AND pr023_var_batch = var_batch_prod;
			
			#T25070005 判断下这边批次称量完成时 该任务批次是否已经收料完成了 若收料完成 批记录里的称量记录要重新生成
			SELECT pr023_flag_comp INTO flag_check
			FROM prod023 
			WHERE pr023_var_batch = var_batch_prod;
			
			IF flag_check = 'Y' THEN
				SELECT pr07_id INTO id_pr07 
				FROM prod07 
				WHERE pr07_bill_no = temp_bill_no 
				AND pr07_var_batch = var_batch_prod
				ORDER BY pr07_date_create DESC LIMIT 1;
				
				select s302_var_cfg into validdays from sys302 where s302_id_cfg="Validdays_unstock";
				
				DELETE FROM prod071 WHERE pr071_id_main = id_pr07;
				
				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(),
				NOW(),
				'',
				'正常',
				0, 
				
				NOW(),
				w01_id_creater,
				'w01',
				'',
				w01_id_terminal,
				
				'pr071',
				0,
				'',
				NOW(),
				0,
				
				'',
				'',
				id_pr07,
				'',
				NOW(),
				
				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 CASE WHEN w01_flag_supple = 'Y' THEN w01_bill_no_ori ELSE w01_var_task END = temp_bill_no  
				AND CASE WHEN w01_flag_supple = 'Y' THEN w01_var_batch_ori ELSE w01_var_batch END = var_batch_prod
				AND w01_flag_valid = '正常' 
				AND w01_flag_state = '正常' 
				GROUP BY w01_id_creater,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;
				
				UPDATE prod071 SET pr071_dec_stand = convertUnit('kg', pr071_id_unit) * (SELECT sum(pr022_dec_qty) AS qty FROM prod022 WHERE pr022_id_main = id_task AND pr022_id_item = pr071_id_item)
				WHERE pr071_id_main = id_pr07;
			END IF;
		END IF;
	
	END IF;

	select i31_dec_out 
	into dec_out 
	from inv31 
	where i31_id = var_item_serial;
	

	select sum(w01_dec_weight) into dec_w
	from weight01
	where weight01.w01_id_item=id_item
	and weight01.w01_id_task=id_task;

	select sum(w01_dec_weight) into dec_p
	from weight01
	where weight01.w01_id_pr21=id_pr021;

	update out112,out111 set out112.o112_dec_weight_use=dec_w
	where out111.o111_id_pr02_id=id_task
	and out112.o112_id_item=id_item
	and out111.o111_id_main=out112.o112_id_main;
	
	#释放 占用表的占用量 
-- 	update weight01,occupy01 
-- 	set oc01_dec_release = oc01_dec_release + w01_dec_weight,
-- 	oc01_dec_occu = IF(oc01_dec_occu - w01_dec_weight  < 0,0,oc01_dec_occu - w01_dec_weight ) 
-- 	where w01_id_task = oc01_id_pr02 
-- 	and w01_id_item = oc01_id_item 
-- 	and w01_id = id;

	update prod021 set prod021.pr021_dec_act=dec_p
	where prod021.pr021_id=id_pr021; 

	
-- 	select i22_dec_weight into pickweight from weight01,inv22 where weight01.w01_id=id and w01_var_item_serial=i22_var_serial and i22_var_source="自动领料" ORDER BY i22_date_create desc limit 1;
	select i31_dec_end into pickweight from weight01,inv31 where weight01.w01_id=id and w01_var_item_serial=i31_var_serial;
		
	update inv31 
	set i31_dec_end = i31_dec_end - dec_weight,
	i31_dec_out = dec_out + dec_weight,
	i31_dec_loss = i31_dec_in - dec_out - dec_weight
	where i31_id = var_item_serial;

		#####称量数据同步到库存
	INSERT INTO inv22(i22_id, i22_date_create, i22_id_creater, i22_flag_state, i22_int_line, 
							i22_date_bill, i22_id_clerk, i22_id_source_table, i22_id_source, i22_bill_no, 
							i22_id_table, i22_int_year, i22_id_audit, i22_date_audit, i22_int_month, 
							i22_id_dept, i22_id_com, i22_id_main, i22_id_flow, i22_date_audit_act, 
							i22_var_bill_ori, i22_var_busi_no, i22_id_item,   i22_var_item, i22_code_item, 
							i22_var_serial, i22_var_batch, i22_dec_tag, i22_dec_tare, i22_id_unit, 						
							i22_date_prod, i22_date_expire, i22_id_sup, i22_id_wh, i22_id_loc, 
							i22_id_container, i22_int_bag_count, i22_flag_half, i22_flag_checked, i22_flag_occu, 
							i22_flag_closed, i22_id_terminal, i22_flag_io, i22_var_serial_ori, i22_id_scale, 
							i22_flag_tag, i22_flag_bag, i22_dec_weight, i22_dec_qty, i22_var_source, 
							i22_id_pack, i22_id_source_ori,i22_id_supp,i22_var_supp)

	select uuid_short(), w01_date_create, w01_id_creater, w01_flag_state, w01_int_line, 
							w01_date_bill, w01_id_clerk, 'w01', id, id, 
							'i22',	w01_int_year, w01_id_audit, w01_date_audit, w01_int_month,
							w01_id_dept, w01_id_com, '', '', w01_date_audit_act,
							w01_var_item_serial, '', w01_id_item, c102_var_item, w01_id_item, 
							w01_var_serial, w01_var_item_batch, w01_dec_weight, w01_dec_tare, w01_id_unit, 						
							w01_date_create,i31_date_expire,'',c301_id_terminal, '', 
							w01_id_container, 1, 'N', 'Y', 'N', 
							'N', w01_id_terminal, '0', '', w01_var_scale,
							'1', '1', w01_dec_weight, 1, '称量入库',
							'', '','',''
	from weight01,ctlm102,inv31,ctlm301
	where w01_id = id 
	AND w01_id_item = c102_id
	AND w01_var_item_serial = i31_id
	and w01_id_terminal=c301_var_ip;
	
	insert into inv21(i21_id,i21_flag_state,i21_date_create,i21_id_source,i21_var_bill_ori,
			i21_var_busi_no,i21_id_item,i21_var_item,i21_code_item,i21_var_serial,
			i21_var_batch,i21_dec_tag,i21_dec_tare,i21_dec_in,i21_dec_end,
			i21_dec_move,i21_dec_out,	i21_dec_in_qty,i21_dec_end_qty,i21_dec_move_qty,
			i21_dec_out_qty,i21_id_unit,	i21_date_prod,i21_date_expire,i21_id_sup,
			i21_id_wh,i21_id_loc,i21_id_container,i21_int_bag_count,i21_flag_half,
			i21_flag_checked,i21_flag_occu,i21_flag_closed,i21_id_terminal,i21_flag_tag,
			i21_id_pack,i21_var_batch_prod,i21_var_cata,i21_id_supp,i21_var_supp)			
	select uuid_short(),'正常',w01_date_create,id,'',
			'',w01_id_item,c102_var_item,w01_id_item,w01_var_serial,
			w01_var_item_batch,w01_dec_weight,w01_dec_tare,w01_dec_weight ,w01_dec_weight ,
			0,0,1,1,0,
			0,w01_id_unit,w01_date_create,i31_date_expire,'',
			c301_id_terminal,'',w01_id_container,1,'N',
			'Y','N','N',w01_id_terminal,'1',
			w01_id_pack,'','','',''	
			from weight01,ctlm102,inv31,ctlm301
			where w01_id = id
			AND w01_id_item = c102_id
			AND w01_var_item_serial = i31_id
			and w01_id_terminal=c301_var_ip;

	insert into inv31(i31_id,i31_flag_state,i31_date_create,i31_id_source,i31_var_bill_ori,i31_var_busi_no,
			i31_id_item,i31_var_item,i31_code_item,i31_var_serial,i31_var_batch,i31_dec_tag,i31_dec_tare,
			i31_dec_in,i31_dec_end,i31_dec_move,i31_dec_out,	i31_dec_in_qty,i31_dec_end_qty,i31_dec_move_qty,i31_dec_out_qty,
			i31_id_unit,	i31_date_prod,i31_date_expire,i31_id_sup,i31_id_wh,i31_id_loc,i31_id_container,i31_int_bag_count,
			i31_flag_half,i31_flag_checked,i31_flag_occu,i31_flag_closed,i31_id_terminal,i31_flag_tag,i31_id_pack,i31_var_batch_prod,
			i31_var_cata,
			i31_id_supp,i31_var_supp,
			i31_flag_inv,i31_dec_loss,i31_id_process,i31_var_process,i31_var_task,
			i31_var_batch_process,i31_flag_child,i31_var_serial_label)			
	select w01_var_serial,'正常',w01_date_create,id,'','',
			w01_id_item,c102_var_item,w01_id_item,w01_var_serial,w01_var_item_batch,w01_dec_weight,w01_dec_tare,
			w01_dec_weight,
			w01_dec_weight,
			0,0,1,1,0,0,
			w01_id_unit,w01_date_create,i31_date_expire,'',c301_id_terminal,'',w01_id_container,1,
			'N','Y','N','N',w01_id_terminal,1,w01_id_pack,'',
			'',
			'','',
			'in',0,w01_process_code,w01_process_name,IF(w01_flag_supple = 'Y',w01_bill_no_ori,w01_var_task),
			IF(w01_flag_supple = 'Y',w01_var_batch_ori,w01_var_batch),'Y',w01_id_flow 
			from weight01,ctlm102,inv31,ctlm301
			where w01_id = id
			AND w01_id_item = c102_id
			AND w01_var_item_serial = i31_id
			and w01_id_terminal=c301_var_ip;	
			
		
	########################

	#自动领料
	if LOCATE('autoout',id_source) > 0 THEN 
			IF LOCATE('autoback',id_source) = 0 THEN
			
				#没有自动退库的标志时才把在库状态改为out
				update inv31 set i31_flag_inv = 'out' where i31_id = var_item_serial;
				
			END IF;
				
			INSERT INTO inv22(i22_id, i22_date_create, i22_id_creater, i22_flag_state, i22_int_line, 
			i22_date_bill, i22_id_clerk, i22_id_source_table, i22_id_source, i22_bill_no, 
			i22_id_table, i22_int_year, i22_id_audit, i22_date_audit, i22_int_month, 
			i22_id_dept, i22_id_com, i22_id_main, i22_id_flow, i22_date_audit_act, 
			i22_var_bill_ori, i22_var_busi_no, i22_id_item,   i22_var_item, i22_code_item, 
			i22_var_serial, i22_var_batch, i22_dec_tag, i22_dec_tare, i22_id_unit, 
			i22_date_prod, i22_date_expire, i22_id_sup, i22_id_wh, i22_id_loc, 
			i22_id_container, i22_int_bag_count, i22_flag_half, i22_flag_checked, i22_flag_occu, 
			i22_flag_closed, i22_id_terminal, i22_flag_io, i22_var_serial_ori, i22_id_scale, 
			i22_flag_tag, i22_flag_bag, i22_dec_weight, i22_dec_qty, i22_var_source, 
			i22_id_pack, i22_id_source_ori,i22_var_remark,i22_var_batch_prod,i22_var_cata,
			i22_flag_erp,i22_date_erp,i22_id_supp,i22_var_supp)
			select id, NOW(), w_id_creater, i21_flag_state, i21_int_line, 
			NOW(), i21_id_clerk, 'pr02', temp_bill_no, id, 
			'i22', YEAR(NOW()), i21_id_audit, NOW(), MONTH(NOW()), 
			i21_id_dept, i21_id_com, i21_id_main,var_batch_prod, i21_date_audit_act, 
			i21_var_bill_ori, i21_var_busi_no, i21_id_item, i21_var_item, i21_code_item, 
			i21_var_serial, i21_var_batch, i21_dec_tag, i21_dec_tare, i21_id_unit, 
			i21_date_prod, i21_date_expire, i21_id_sup, i21_id_wh, i21_id_loc, 
			i21_id_container, i21_int_bag_count, i21_flag_half, i21_flag_checked, i21_flag_occu, 
			i21_flag_closed, id_terminal, '1', i21_var_serial, var_scale, 
			i21_flag_tag, '' as i22_flag_bag, i21_dec_end, i21_dec_end_qty, '自动领料', 
			i21_id_pack, i21_id ,'',i21_var_batch_prod,i21_var_cata,
			'',NULL,i21_id_supp,i21_var_supp 
			from inv21
			where i21_var_serial = var_item_serial ;
			
			delete from inv21 where i21_var_serial = var_item_serial;
	end IF;

	#自动退库
	if (LOCATE('autoback',id_source) > 0) then
			update inv31 
			set i31_flag_inv = 'in',
			i31_dec_loss = i31_dec_in - i31_dec_end - i31_dec_out 
			where i31_id = var_item_serial;
				
			## 先写流水表
			INSERT INTO inv22(i22_id, i22_date_create, i22_id_creater, i22_flag_state, i22_int_line, 
				i22_date_bill, i22_id_clerk, i22_id_source_table, i22_id_source, i22_bill_no, 
				i22_id_table, i22_int_year, i22_id_audit, i22_date_audit, i22_int_month, 
				i22_id_dept, i22_id_com, i22_id_main, i22_id_flow, i22_date_audit_act, 
				i22_var_bill_ori, i22_var_busi_no, i22_id_item,   i22_var_item, i22_code_item, 
				i22_var_serial, i22_var_batch, i22_dec_tag, i22_dec_tare, i22_id_unit, 
				i22_date_prod, i22_date_expire, i22_id_sup, i22_id_wh, i22_id_loc, 
				i22_id_container, i22_int_bag_count, i22_flag_half, i22_flag_checked, i22_flag_occu, 
				i22_flag_closed, i22_id_terminal, i22_flag_io, i22_var_serial_ori, i22_id_scale, 
				i22_flag_tag, i22_flag_bag, i22_dec_weight, i22_dec_qty, i22_var_source, 
				i22_id_pack, i22_id_source_ori,i22_var_remark,i22_var_batch_prod,i22_var_cata,
				i22_flag_erp,i22_date_erp,i22_id_supp,i22_var_supp)
			select UUID_SHORT(), NOW(), w_id_creater, i31_flag_state, i31_int_line, 
				NOW(), i31_id_clerk, 'pr02', temp_bill_no, id, 
				'i22', YEAR(NOW()), i31_id_audit, NOW(), MONTH(NOW()), 
				i31_id_dept, i31_id_com, i31_id_main, var_batch_prod, NOW(), 
				i31_var_bill_ori, i31_var_busi_no, i31_id_item, i31_var_item, i31_code_item, 
				i31_var_serial, i31_var_batch, i31_dec_tag, i31_dec_tare, i31_id_unit, 
				i31_date_prod, i31_date_expire, i31_id_sup, i31_id_wh, i31_id_loc, 
				i31_id_container, i31_int_bag_count, 'Y', i31_flag_checked, i31_flag_occu, 
				i31_flag_closed, id_terminal, '0', i31_var_serial, var_scale, 
				i31_flag_tag, '' as i22_flag_tag, i31_dec_end, i31_dec_end_qty, '自动退库', 
				i31_id_pack, i31_id ,'',i31_var_batch_prod,i31_var_cata,
				'',NULL,i31_id_supp,i31_var_supp 
			from inv31
			where i31_id = var_item_serial
			and i31_dec_end > 0;
				
			##	插入到i21
			insert into inv21(i21_id,i21_flag_state,i21_date_create,i21_id_source,i21_var_bill_ori,
				i21_var_busi_no,i21_id_item,i21_var_item,i21_code_item,i21_var_serial,
				i21_var_batch,i21_dec_tag,i21_dec_tare,i21_dec_in,i21_dec_end,
				i21_dec_move,i21_dec_out,	i21_dec_in_qty,i21_dec_end_qty,i21_dec_move_qty,
				i21_dec_out_qty,i21_id_unit,	i21_date_prod,i21_date_expire,i21_id_sup,
				i21_id_wh,i21_id_loc,i21_id_container,i21_int_bag_count,i21_flag_half,
				i21_flag_checked,i21_flag_occu,i21_flag_closed,i21_id_terminal,i21_flag_tag,
				i21_id_pack,i21_var_batch_prod,i21_var_cata,i21_id_supp,i21_var_supp,
				i21_id_customer)			
			select uuid_short(),'正常',NOW(),id,'',
				'',i31_id_item,i31_var_item,i31_code_item,i31_var_serial,
				i31_var_batch,i31_dec_tag,i31_dec_tare,i31_dec_end ,i31_dec_end ,
				0,0,1,1,0,
				0,i31_id_unit,i31_date_prod,i31_date_expire,IFNULL(i31_id_sup,''),
				i31_id_wh,i31_id_loc,i31_id_container,i31_int_bag_count,'Y',
				i31_flag_checked,'N','N',id_terminal,i31_flag_tag,
				i31_id_pack,i31_var_batch_prod,ifnull(i31_var_cata,''),IFNULL(i31_id_supp,''),i31_var_supp,
				i31_id_customer		
			from inv31
			where i31_id = var_item_serial
			and i31_dec_end > 0;
			
	END if;
	
	#任务单损耗采集
	select ifnull(s302_var_cfg,"1") into cfg from sys302 where s302_id_cfg="cfg_collectloss";
	if cfg="0" then 
			select count(1) into countrec from record01 where rec01_id_pr02=id_task and rec01_id_main=var_item_serial and rec01_var_batch_prod=var_batch_prod;
			
			if countrec>0 then
			
				
				select sum(b.w01_dec_weight) into w01_sum from weight01 a,weight01 b where a.w01_id=id and a.w01_var_item_serial=b.w01_var_item_serial and a.w01_id_task=b.w01_id_task and a.w01_var_batch=b.w01_var_batch;
				update record01,weight01 set rec01_dec_formula=w01_sum,rec01_dec_loss=rec01_dec_pick-w01_sum-rec01_dec_return where rec01_id_pr02=w01_id_task and rec01_id_main=w01_var_item_serial and rec01_var_batch_prod=w01_var_batch and w01_id=id;
			
			else

					insert into record01(rec01_id,rec01_date_create,rec01_id_creater,rec01_flag_state,rec01_int_line,rec01_date_bill,
					rec01_id_clerk,rec01_id_source_table,rec01_id_source,rec01_bill_no,rec01_id_table,rec01_int_year,rec01_id_audit,
					rec01_date_audit,rec01_int_month,rec01_id_dept,rec01_id_com,rec01_id_main,rec01_id_flow,rec01_date_audit_act,
					rec01_id_pr02,rec01_var_task,rec01_var_batch_prod,rec01_id_item_prod,rec01_var_item_prod,rec01_id_item,
					rec01_var_item,rec01_var_batch,rec01_dec_pick,rec01_dec_formula,rec01_dec_return,rec01_dec_loss)
					select UUID(),w01_date_create,w01_id_creater,'正常',1,w01_date_bill,
					w01_id_clerk,'','','','',w01_int_year,w01_id_audit,
					w01_date_audit,w01_int_month,w01_id_dept,w01_id_com,w01_var_item_serial,'',w01_date_audit_act,
					w01_id_task,w01_var_task,w01_var_batch,w01_process_code,w01_process_name,w01_id_item,
					c102_var_item,w01_var_item_batch,pickweight,w01_dec_weight,0,pickweight-w01_dec_weight
					from weight01,ctlm102
					where weight01.w01_id=id and w01_id_item=c102_id;
					
					
			end if;
	end if;

IF result_code = 1  or int_result = 1  THEN 
				ROLLBACK;
				select '1|重复扫码' as result_code ;
    ELSE
        COMMIT;
				select '0|更新成功' as result_code ;
      END IF;
END//
DELIMITER ;

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 count2 INTEGER DEFAULT 0;
	DECLARE var_batch VARCHAR(60);
	DECLARE flag_supple VARCHAR(10);
	DECLARE bill_no VARCHAR(60);
	DECLARE id VARCHAR(60);
	DECLARE id_main VARCHAR(60);
	DECLARE validdays INTEGER;
	#任务称量完成标记
	DECLARE flag_weigh_comp VARCHAR(60);
	
	#将对应的称量记录中的收料状态变为Y
	UPDATE weight01 SET w01_flag_check = 'Y' WHERE w01_id_flow = serial AND w01_flag_state = '正常' AND w01_flag_valid = '正常';
	
	SELECT IF(w01_flag_supple = 'Y',w01_bill_no_ori,w01_var_task),IF(w01_flag_supple = 'Y',w01_var_batch_ori,w01_var_batch),IF(w01_flag_supple = 'Y','Y','N') INTO bill_no,var_batch,flag_supple FROM weight01 WHERE w01_var_serial = serial;
	
	select pr02_id,IFNULL(pr02_flag_comp_formula,'') into id,flag_weigh_comp from prod02 where pr02_bill_no=bill_no;
	
	select s302_var_cfg into validdays from sys302 where s302_id_cfg="Validdays_semiproduct";
	SELECT count(1) INTO count1 
	FROM weight01 LEFT JOIN prod021 ON w01_id_pr21 = pr021_id
	WHERE w01_flag_check <> 'Y' 
	AND IFNULL(pr021_flag_need_check,'N') = 'Y'
	AND w01_var_batch_ori = var_batch 
	AND w01_flag_state = '正常' 
	AND w01_flag_valid = '正常'
	AND w01_flag_supple = 'Y'
	AND ifnull(w01_id_dept,"")<>'Y';
	
	SELECT count(1) INTO count2 
	FROM weight01 LEFT JOIN prod021 ON w01_id_pr21 = pr021_id 
	WHERE w01_flag_check <> 'Y' 
	AND IFNULL(pr021_flag_need_check,'N') = 'Y'
	AND w01_var_batch = var_batch 
	AND w01_flag_state = '正常' 
	AND w01_flag_valid = '正常'
	AND IFNULL(w01_flag_supple,'') <> 'Y'
	AND ifnull(w01_id_dept,"")<>'Y';
	#插入收料记录
	IF flag_supple = 'Y' THEN
		
		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,
		w01_bill_no_ori 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,
		w01_var_batch_ori 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,w01_id_unit_w,w01_id_task,w01_bill_no_ori,w01_var_item_serial,w01_var_item_batch,w01_process_code,w01_process_name,w01_var_batch_ori;
	ELSE 
		
		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,
		w01_var_task 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,
		w01_var_batch 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,w01_id_unit_w,w01_id_task,w01_var_task,w01_var_item_serial,w01_var_item_batch,w01_process_code,w01_process_name,w01_var_batch;
	END IF;
	
	IF count1 = 0 AND count2 = 0 THEN 
		DELETE FROM inv21 
		WHERE EXISTS(SELECT 1 FROM (SELECT a.w01_var_serial FROM weight01 a WHERE IF(a.w01_flag_supple = 'Y',a.w01_var_batch_ori,a.w01_var_batch) = var_batch AND a.w01_flag_state = '正常' AND a.w01_flag_valid = '正常') aa WHERE aa.w01_var_serial = i21_var_serial);
			
		UPDATE inv31,weight01 a
		SET i31_flag_inv = 'out',
		i31_id_loc = ''
		WHERE i31_id = a.w01_var_serial
		AND IF(a.w01_flag_supple = 'Y',a.w01_var_batch_ori,a.w01_var_batch) = var_batch
		AND a.w01_flag_state = '正常' AND a.w01_flag_valid = '正常';

		UPDATE prod023,weight01
		SET pr023_flag_comp = 'Y'
		WHERE w01_var_serial = serial
		AND IF(w01_flag_supple = 'Y',w01_var_batch_ori,w01_var_batch) = pr023_var_batch;
			
			SET id_main = UUID();
			
		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_var_version,pr07_var_remark)
			select id_main,
			NOW(),
			id_user,
			'正常',
			a.pr02_int_line,
			
			a.pr02_date_create,
			a.pr02_id_creater,
			'正常完成',
			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,
			"",
			"",
			NOW(),
			
			a.pr02_id_unit,
			pr01_var_code,
			a.pr02_id_item,
			c102_var_item,
			var_batch,
			
			0 + CAST(a.pr02_dec_batch_w as CHAR) as pr07_dec_qty,
			DATE_FORMAT(a.pr02_date_plan,'%Y-%m-%d'),
			pr01_var_ver,
			pr01_var_remark
			
			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, bill_no, var_batch,id_main);

		#T25070005 此时收料完成时可能存在 不需要收料的物料还没称完 因此在这做个判断 (防止终端下载不到任务)
		IF flag_weigh_comp = 'Y' THEN
		
			update prod02 set pr02_flag_comp = 'Y' 
			where pr02_id = id
			and not EXISTS(select 1 from prod023 a where a.pr023_id_main= prod02.pr02_id and (IFNULL(pr023_flag_comp,'N')='N' or pr023_flag_comp = '' )) ;
		
		END IF;

	END IF;
END//
DELIMITER ;

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

DELIMITER //
CREATE PROCEDURE `proc_update_comp_weight`(in ids varchar(60),in var_mac varchar(60),IN type VARCHAR(60))
begin
	declare task_type  VARCHAR(1);
	DECLARE count1 INTEGER DEFAULT 0;
	DECLARE count2 INTEGER DEFAULT 0;
	DECLARE id_pr02 VARCHAR(60);
	DECLARE var_batch VARCHAR(60);
	DECLARE id_main VARCHAR(60);
  DECLARE result_code varchar(1) DEFAULT '0'; 
	DECLARE msg TEXT;
	declare task_complete_flag varchar(2) default 't';
	DECLARE checkContainer varchar(2) DEFAULT '0';
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
			BEGIN
				GET DIAGNOSTICS CONDITION 1
				msg = MESSAGE_TEXT;
				SET result_code = '1';
			END;
      START TRANSACTION; 
			
		select s316_var_cfg into task_complete_flag from sys316 where s316_id_cfg='task_complete_flag';
		
		select s316_var_cfg into checkContainer from sys316 where s316_id_cfg = 'checkContainer';
		
		select LEFT(ids,1) INTO task_type;  #S开头的单据号是补料任务单 
			 
		IF task_type = 'S' THEN

			update prod061,prod06 set pr061_flag_comp = 'Y'
			where pr06_bill_no = ids and prod061.pr061_id_main = prod06.pr06_id;
			
			update prod06 set  pr06_flag_comp_formula = 'Y'
			where prod06.pr06_bill_no = ids
			and not EXISTS(select 1 from prod061 a where a.pr061_id_main= prod06.pr06_id and IFNULL(pr061_flag_comp,'N') <> 'Y') ;
			
			if task_complete_flag='1' then
				update prod06 set pr06_flag_comp = 'Y' 	
				where pr06_bill_no = ids and pr06_flag_comp_formula = 'Y';
			end if; 
			
		ELSE
		
			update prod021,ctlm301,prod02 set pr021_flag_comp='Y' 
			where pr02_bill_no=ids 
			and prod021.pr021_id_main=prod02.pr02_id 
			and pr021_id_terminal=ctlm301.c301_id 
			and (ctlm301.c301_var_ip =var_mac);

			SELECT pr02_id,pr02_var_batchs INTO id_pr02,var_batch FROM prod02 WHERE pr02_bill_no = ids;
			
			SELECT count(1) INTO count1 
			FROM prod021
			WHERE pr021_id_main = id_pr02
			AND pr021_flag_comp = 'N';
			
			IF count1 = 0 THEN
				UPDATE prod02 SET pr02_flag_comp_formula = 'Y',pr02_flag_step = '03' 
				WHERE pr02_id = id_pr02;
				
				UPDATE prod02 SET pr02_flag_comp = 'Y' 
				where pr02_id = id_pr02
				and not EXISTS(select 1 from prod023 a where a.pr023_id_main= prod02.pr02_id and (IFNULL(pr023_flag_comp,'N')='N' or pr023_flag_comp = '' )) ;
				
				DELETE FROM occupy05 WHERE oc05_id_pr02 = id_pr02;
			END IF;
			
			#T25070005 此任务单可能不存在需要收料的物料 此处生成批记录并完成
			SELECT count(1) INTO count2
			FROM prod021
			WHERE pr021_id_main = id_pr02
			AND pr021_flag_need_check = 'Y';
			
			IF count2 = 0 THEN

				SET id_main = UUID();
			
				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_var_version,pr07_var_remark)
					select id_main,
					NOW(),
					'',
					'正常',
					pr02_int_line,
					
					pr02_date_create,
					pr02_id_creater,
					'正常完成',
					id_pr02,
					pr02_bill_no,
					
					'pr07',
					pr02_int_year,
					pr02_id_audit,
					pr02_date_audit,
					pr02_int_month,
					
					pr02_id_dept,
					pr02_id_com,
					"",
					"",
					NOW(),
					
					pr02_id_unit,
					pr01_var_code,
					pr02_id_item,
					c102_var_item,
					pr02_var_batchs,
					
					0 + CAST(pr02_dec_batch_w as CHAR) as pr07_dec_qty,
					DATE_FORMAT(pr02_date_plan,'%Y-%m-%d'),
					pr01_var_ver,
					pr01_var_remark
					
					from prod02,prod01,ctlm102 
					where pr02_id = id_pr02
					and pr01_id = pr02_id_formula 	
					and pr02_id_item = c102_id_item;
					
				CALL proc_deal_pr02_record(id_pr02, ids, var_batch,id_main);
				
				UPDATE prod02 SET pr02_flag_comp_formula = 'Y',pr02_flag_comp = 'Y',pr02_flag_step = '03' 
				WHERE pr02_id = id_pr02;
				
				DELETE FROM occupy05 WHERE oc05_id_pr02 = id_pr02;

			END IF;
			
			#type='4' => 单桶打印模式
			IF type = '4' THEN
				update weight01,(select max(w01_var_serial) as serials,w01_id_terminal,w01_id_container,w01_var_batch from weight01
				where w01_var_task = ids 
				and w01_flag_valid = '正常' group by w01_id_terminal,w01_id_container,w01_var_batch ) b 
				set w01_id_prop = '1' 
				where w01_var_task = ids 
				and w01_flag_valid = '正常' 
				and w01_var_serial = b.serials;
			END IF;
			
			IF checkContainer = '1' THEN
				update weight01 a,(select SUM(w01_dec_weight) as qty,w01_id_container from weight01 where w01_var_task = ids and w01_flag_state = '正常' and w01_flag_valid = '正常' GROUP BY w01_id_container) b
				set a.w01_dec_qty = b.qty 
				where a.w01_var_task = ids 
				and a.w01_id_container = b.w01_id_container
				and a.w01_flag_state = '正常' 
				and a.w01_flag_valid = '正常';
			END IF;

			if task_complete_flag='1' then

				update prod02 set pr02_flag_comp='Y' 	
				where prod02.pr02_bill_no=ids and  pr02_flag_comp_formula = 'Y';
			end if; 
			
			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) ;
			
		END IF;
		
		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 ;
