INSERT INTO `ver01`(`id`, `version`, `apk_name`, `generate_date`, `note`) VALUES (11, 'ifs.pda.6.4.x.zeson.260212.1', 'ZESON26021201.apk', '2026-02-12 11:23:02', NULL);
DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_inv22_in`;//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `proc_deal_inv22_in`(IN id_source VARCHAR(60), IN ids varchar(60), IN var_source VARCHAR(60), OUT int_result int)
begin
 
	#入库单  
	declare counts integer default 0;
	declare counts1 integer default 0;
	DECLARE count2 INTEGER DEFAULT 0;
	DECLARE id_pr02 VARCHAR(60);
	
	DECLARE check_result VARCHAR(10);	#检验状态
	DECLARE q_flag_result VARCHAR(10); #质检单结果
	DECLARE id_source_table VARCHAR(10); #p11_id_source_table 

	declare var_serial VARCHAR(60);
	#储罐号 生产入库时用
	DECLARE id_tank VARCHAR(60);
	
	set int_result = 0;
		
		IF var_source = '采购入库' THEN
			#修改原单据的收货数量
			update pur111 set p111_dec_deal=(select sum(i22_dec_qty) from  inv22 where i22_id_source =id_source and i22_flag_state='正常' )	where pur111.p111_id=id_source;
			update pur111 set p111_dec_deal_w=(select sum(i22_dec_weight) from  inv22 where i22_id_source =id_source and i22_flag_state='正常' )	where pur111.p111_id=id_source;
		ELSE
			update inv111 set i111_dec_deal = (select sum(i22_dec_qty) from inv22 where i22_id_source = id_source and i22_flag_state = '正常') where inv111.i111_id = id_source;
			update inv111 set i111_dec_deal_w=(select sum(i22_dec_weight) from  inv22 where i22_id_source =id_source and i22_flag_state='正常' )	where inv111.i111_id=id_source;
		END IF;

		#生产入库情况  将该任务单批号 入库状态改成Y
		IF var_source = '生产入库' THEN
			###修改储罐状态为占用
			SELECT i22_id_container INTO id_tank
			FROM inv22 
			WHERE i22_id = ids;
			
			UPDATE ctlm305
			SET c305_flag_status = '1'
			WHERE c305_id_tank = id_tank
			AND c305_flag_state = '正常';
		
			UPDATE prod023,inv111 
			SET pr023_flag_inv = 'Y' 
			WHERE pr023_var_batch = i111_var_batch 
			AND i111_id = id_source;
			
			UPDATE prod02 
			set pr02_flag_step = '04',
			pr02_flag_prod_in = 'Y'
			WHERE NOT EXISTS (SELECT 1 from prod023 where pr023_id_main = pr02_id and pr023_flag_inv <> 'Y')
			AND pr02_flag_state = '正常';

			select pr023_id_main INTO id_pr02 FROM inv22,prod023 
			where i22_id = ids
			and i22_var_batch = pr023_var_batch;
			
			select count(1) into count2 from inv111,prod023  
			where pr023_id_main = id_pr02 
			and pr023_var_batch = i111_var_batch 
			and i111_dec_deal_w = 0;
			
			IF count2 = 0 THEN
				UPDATE prod02 set pr02_flag_prod_in = 'Y' where pr02_id = id_pr02;
			END IF;
		END IF;
		
			
	#本单据的仓库信息	
		/*
		SELECT IF(c102_flag_check = 'Y' || c102_flag_check = 'A','W','Y')  
		INTO check_result 
		FROM ctlm102,inv22 
		WHERE i22_id = ids 
		and i22_id_item = c102_id;
		
		IF check_result = 'W' THEN
			#根据i22_flag_checked(若有外观检验项 就是外观检验结果 根据该结果查找质检单结果 q01_id_main记录了质检单的外观检验结果)
			SELECT IFNULL((SELECT q01_flag_result AS result 
			FROM qa01,inv22
			WHERE q01_id_item = i22_id_item
			AND q01_var_batch = i22_var_batch
			AND q01_flag_state = '正常'
			AND q01_bill_source = i22_var_busi_no
			AND q01_id_main = i22_flag_checked 
			AND i22_id = ids
			ORDER BY q01_date_create DESC 
			LIMIT 1),'') INTO q_flag_result;
			
			#只有在q_flag_result是有效数据时 才给check_result赋值
			IF q_flag_result = 'Y' OR q_flag_result = 'N' THEN
				SET check_result = q_flag_result;
			END IF;
		END IF;
		*/
		#插入库存表
		select i22_var_serial into var_serial from inv22 where i22_id = ids;
		
		select count(i21_id) into counts from inv21 where i21_id_source= ids;
		select count(i31_id) into counts1 from inv31 where i31_id= var_serial;
		select counts;
		if counts=0 then

			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,i21_var_remark,i21_id_creater,i21_date_recheck,i21_var_source_item,
			i21_dec_pack_gross,i21_var_batch_semi,i21_var_box_pattern,i21_dec_box_qty,i21_date_check,
			i21_date_check_expire,i21_var_checker,i21_var_maker,i21_var_gravity)
			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,i22_date_expire,IFNULL(i22_id_sup,''),
			i22_id_wh,i22_id_loc,i22_id_container,i22_int_bag_count,i22_flag_half,
			i22_flag_checked,'N','N',i22_id_terminal,i22_flag_tag,
			i22_id_pack,i22_var_batch_prod,ifnull(i22_var_cata,''),IFNULL(i22_id_supp,''),i22_var_supp,
			i22_id_customer,i22_var_remark,i22_id_creater,if(i22_flag_checked='Y',if(ifnull(c102_int_recheck,0)>0,DATE(DATE_ADD(i22_date_create,INTERVAL c102_int_recheck day)),""),""),i22_var_source_item,
			i22_dec_pack_gross,i22_var_batch_semi,i22_var_box_pattern,i22_dec_box_qty,i22_date_check,
			i22_date_check_expire,i22_var_checker,i22_var_maker,i22_var_gravity
			from inv22,ctlm102
			where inv22.i22_id=ids and ctlm102.c102_id_item=inv22.i22_code_item;		
			

		else
			set int_result = 1; #重复扫码
							
		end if;
		
		if counts1=0 then

			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,i31_id_customer,i31_var_remark,
			i31_date_bill,i31_id_creater,i31_date_recheck, i31_var_source_item,i31_dec_pack_gross,
			i31_var_batch_semi,i31_var_box_pattern,i31_dec_box_qty,i31_date_check,i31_date_check_expire,
			i31_var_checker,i31_var_maker,i31_var_gravity)
			select var_serial,'正常',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,i22_date_expire,IFNULL(i22_id_sup,''),
			i22_id_wh,i22_id_loc,i22_id_container,i22_int_bag_count,i22_flag_half,
			i22_flag_checked,'N','N',i22_id_terminal,i22_flag_tag,
			i22_id_pack,i22_var_batch_prod,ifnull(i22_var_cata,''),IFNULL(i22_id_supp,''),i22_var_supp,
			'in',0,'','','',
			'','N','',i22_id_customer,i22_var_remark,
			i22_date_bill,i22_id_creater,if(i22_flag_checked='Y',if(ifnull(c102_int_recheck,0)>0,DATE(DATE_ADD(i22_date_create,INTERVAL c102_int_recheck day)),""),""), i22_var_source_item,i22_dec_pack_gross,
			i22_var_batch_semi,i22_var_box_pattern,i22_dec_box_qty,i22_date_check,i22_date_check_expire,
			i22_var_checker,i22_var_maker,i22_var_gravity
			from inv22,ctlm102
			where inv22.i22_id=ids and ctlm102.c102_id_item=inv22.i22_code_item;		

		end if;
END//
DELIMITER ;

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

DELIMITER //
CREATE PROCEDURE `proc_deal_q01_result`(IN ids VARCHAR(60),IN flag_result VARCHAR(60))
BEGIN
	#Routine body goes here...
	DECLARE var_batch VARCHAR(60);
	DECLARE id_item VARCHAR(60);
	DECLARE var_serial VARCHAR(60);
	DECLARE var_desc VARCHAR(60);

	Declare cur_date datetime;
	
	select q01_var_batch,q01_id_item,IFNULL(q01_var_serial,''),s201_var_value
	INTO var_batch,id_item,var_serial,var_desc
	from qa01
	LEFT JOIN sys201 ON s201_id_column = 'q01_var_desc' AND s201_var_key = q01_var_desc
	where q01_id = ids;

	set cur_date=NOW();
	if ifnull(var_serial,'') = '' THEN 
		update inv21 
		set i21_flag_checked = flag_result,
		i21_var_quality_desc = var_desc
		where i21_id_item = id_item
		and i21_var_batch = var_batch;
		
		update inv31 
		set i31_flag_checked = flag_result,
		i31_var_quality_desc = var_desc
		where i31_var_batch = var_batch
		AND i31_id_item = id_item;
		
		update inv21,ctlm102 
		set i21_date_recheck=DATE(DATE_ADD(cur_date,INTERVAL c102_int_recheck day)) 
		where i21_id_item = id_item 
		and i21_var_batch = var_batch 
		and c102_id_item=id_item
		and IFNULL(c102_int_recheck,0)>0
		and flag_result='Y';
		
		update inv31,ctlm102 
		set i31_date_recheck=DATE(DATE_ADD(cur_date,INTERVAL c102_int_recheck day))
		where i31_var_batch = var_batch
		and i31_id_item = id_item
		and c102_id_item=id_item
		and IFNULL(c102_int_recheck,0)>0
		and flag_result='Y';
	ELSE
		update inv21 
		set i21_flag_checked = flag_result,
		i21_var_quality_desc = var_desc
		where i21_var_serial = var_serial;
		
		update inv31 
		set i31_flag_checked = flag_result,
		i31_var_quality_desc = var_desc
		where i31_id = var_serial;
		
		update inv21,ctlm102 
		set i21_date_recheck=DATE(DATE_ADD(cur_date,INTERVAL c102_int_recheck day)) 
		where  i21_var_serial = var_serial  
		and c102_id_item=i21_id_item
		and IFNULL(c102_int_recheck,0)>0
		and flag_result='Y';
		
		update inv31,ctlm102 
		set i31_date_recheck=DATE(DATE_ADD(cur_date,INTERVAL c102_int_recheck day)) 
		where i31_id = var_serial  
		and i31_id_item = c102_id_item
		and IFNULL(c102_int_recheck,0)>0
		and flag_result='Y';

	END IF;
END//
DELIMITER ;

ALTER TABLE qa01 ADD INDEX idx_idItem_varBatch(q01_id_item,q01_var_batch);