DELETE FROM sys201 WHERE s201_id_column = 'i11_flag_type' AND s201_var_key = '06';
DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_inv22_back`;//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `proc_deal_inv22_back`(IN ids VARCHAR(60),IN p_var_serial VARCHAR(60),in var_source varchar(60),out int_result int)
begin
 
	#生产退料  
	declare counts integer default 0;
	declare i integer default 0;
	declare ori_id varchar(60) ;
	declare dec_tag decimal(20,5);
	declare flag_check varchar(60) ;
	declare check_result varchar(60);
	declare int_days int;
	declare flag_half varchar(60);
	declare var_source varchar(60);
	declare backdec decimal(20,5);
	
	DECLARE flag_1 VARCHAR(60);
	
	set int_result = 0;
	
	SET flag_1 = LOWER(RIGHT(p_var_serial,4));
	
	IF flag_1 = 'fill' THEN
			
		update inv31,inv22 set 
		i31_flag_inv = 'in',
		i31_id_wh = i22_id_wh 
		where i31_id = i22_var_serial 
		and i22_id = ids;
				
	END IF; 

	#判断是领料退库或者调拨
	select count(a.i22_id),a.i22_id,a.i22_dec_tag,b.i22_dec_weight,b.i22_var_source into i,ori_id,dec_tag,backdec,var_source from inv22 a,inv22 b
	where b.i22_id= ids
	and a.i22_id= b.i22_id_source_ori	
  and a.i22_flag_state = '正常'
	GROUP BY a.i22_id,a.i22_dec_tag,b.i22_dec_weight,b.i22_var_source;
	
	if i > 0 then  #退料
			#修改生产领料单的退货重量
			
			if(var_source='退库') then
			
				update inv31,inv22 set 
				i31_flag_inv = 'in',
				i31_id_wh = i22_id_wh,
				i31_dec_loss = i31_dec_end - i22_dec_weight,
				i31_dec_end = i22_dec_weight,
				i31_dec_end_qty = round(i22_dec_weight/dec_tag,2) ,
				i31_flag_half = 'Y'
				where i31_id = i22_var_serial 
				and i22_id = ids;
			
				update out112,inv22
				set out112.o112_dec_weight_back = 0.0000+out112.o112_dec_weight_back + backdec
				where inv22.i22_id=ori_id
				and out112.o112_id= inv22.i22_id_source;
				
				update inv121,inv22
				set inv121.i121_dec_weight_back =0.0000 + inv121.i121_dec_weight_back + backdec
				where inv22.i22_id=ori_id
				and inv121.i121_id= inv22.i22_id_source;
			ELSEIF var_source = '调拨入库' OR var_source = '研发取样退库' OR var_source = '销售退货入库' THEN
				update inv31,inv22 set 
				i31_flag_inv = 'in',
				i31_id_wh = i22_id_wh,
				i31_dec_loss = i31_dec_end - i22_dec_weight,
				i31_dec_end = i22_dec_weight,
				i31_dec_end_qty = round(i22_dec_weight/dec_tag,2) ,
				i31_flag_half = 'Y'
				where i31_id = i22_var_serial 
				and i22_id = ids;
				
				update inv121,inv22
				set inv121.i121_dec_weight_back =0.0000 + inv121.i121_dec_weight_back + backdec
				where inv22.i22_id=ori_id
				and inv121.i121_id= inv22.i22_id_source;
			end if;
		
			#插入库存表
			select count(i21_id) into counts from inv21 where inv21.i21_var_serial= p_var_serial;
			
			##select counts;
			
			if counts=0 then
				select  ctlm102.c102_flag_check into flag_check			
				from ctlm102,inv22
				where ctlm102.c102_id_item=inv22.i22_code_item
				and inv22.i22_id=ids;

				set check_result='Y';
				
				if(flag_check='Y' || flag_check='A')then
						set check_result='W';
						select qa01.q01_flag_result into check_result from qa01,inv22
						where qa01.q01_id_item=inv22.i22_code_item
								and qa01.q01_var_batch=inv22.i22_var_batch
								and ifnull(qa01.q01_id_sup,'')=ifnull(inv22.i22_id_supp,'') 
						and inv22.i22_id=ids
						and qa01.q01_flag_state='正常'
						order by qa01.q01_date_create desc  limit 1;
						
				end if;
				
				set flag_half='Y';
				if var_source='调拨出库' then
					set flag_half='N';
					update inv31,inv22 
					set i31_flag_inv = 'in',
					i31_id_wh = i22_id_wh 
					where i31_id = i22_var_serial and i22_id = ids;
				end if;
					
				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(),'正常',i22_date_create,ids,'','',
				i22_id_item,i22_var_item,i22_code_item,i22_var_serial,i22_var_batch,dec_tag,i22_dec_tare,
				i22_dec_weight,i22_dec_weight ,0,0,round(i22_dec_weight/dec_tag,2),round(i22_dec_weight/dec_tag,2),0,0,
				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,
				flag_half,check_result,'N','N',i22_id_terminal,i22_flag_tag,i22_id_pack,i22_var_batch_prod,i22_var_cata,
				i22_id_supp,i22_var_supp
				from inv22,ctlm102
				where inv22.i22_id=ids and ctlm102.c102_id_item=inv22.i22_code_item;	
						
				#从检验表获取是否检验合格，
	
			else								
					set int_result = 1; #重复扫码
			end if;
			
		else 
		
					#插入库存表
					select count(i21_id) into counts from inv21 where inv21.i21_var_serial= p_var_serial;
					
					##select counts;
					
					if counts=0 then
					##包装入库	
					select  ctlm102.c102_flag_check,ctlm102.c102_int_expire into flag_check	,int_days		
					from ctlm102,inv22
					where ctlm102.c102_id_item=inv22.i22_code_item
					and inv22.i22_id=ids;
					set check_result='Y';
					if(flag_check='Y')then
							set check_result='W';
							select qa01.q01_flag_result into check_result from qa01,inv22
							where qa01.q01_id_item=inv22.i22_code_item
							and qa01.q01_var_batch=inv22.i22_var_batch
							and ifnull(qa01.q01_id_sup,'')=ifnull(inv22.i22_id_supp,'')
							and inv22.i22_id=ids
							and qa01.q01_flag_state='正常'
							order by qa01.q01_date_create desc  limit 1;
							
											
					end if;
					
						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(),'正常',i22_date_create,ids,'','',
						i22_id_item,i22_var_item,i22_code_item,i22_var_serial,i22_var_batch,i22_dec_tag,i22_dec_tare,
						i22_dec_weight ,
						i22_dec_weight ,0,0,i22_dec_qty,i22_dec_qty,0,0,
				##		i22_id_unit,i22_date_prod,DATE_ADD(i22_date_prod,INTERVAL int_days DAY),i22_id_sup,i22_id_wh,i22_id_loc,i22_id_container,i22_int_bag_count,
						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,check_result,'N','N',i22_id_terminal,i22_flag_tag,i22_id_pack,i22_var_batch_prod,i22_var_cata,
							i22_id_supp,i22_var_supp
						from inv22,ctlm102
						where inv22.i22_id=ids and ctlm102.c102_id_item=inv22.i22_code_item;	
						
						
						else
							set int_result = 1; #重复扫码
						end if;
			
		end if;
		
end//
DELIMITER ;