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

DELIMITER //
CREATE PROCEDURE `proc_deal_pr021`(IN `id` varchar(60))
BEGIN

  DECLARE result_code varchar(1) DEFAULT '0'; 
	
			DECLARE sign varchar(50);
			DECLARE var_batch varchar(60);
			DECLARE id_terminal varchar(60);
			declare var_code varchar(60);
			declare id_flow varchar(60);
			declare num int default 0;
		  declare id_pr01 varchar(60);
			declare date_opr varchar(60) default '';
			declare i int default 1; 
			declare int_batch varchar(60);
			declare date_plan VARCHAR(60);
			declare date_prod VARCHAR(60);
			declare int_p int default 4;
			declare str varchar(1000) default '';
			declare rownum int default 0;
		  declare id_source VARCHAR(60);
			declare var_batch_old varchar(1000) default '';
			declare var_prop varchar(60);
				
			declare var_desc varchar(1024);
			declare counts int;
			declare date_work datetime;
			declare task_name varchar(60);
			declare bill_no varchar(60) default '';
			declare flag_type varchar(60);
			DECLARE msg TEXT;
			declare flag_append varchar(60) default '';
			#产品属性
			DECLARE prod_prop VARCHAR(60) DEFAULT '';
			
			DECLARE rand_i INTEGER;
			DECLARE rand_s INTEGER;

      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    begin
    
     -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
      msg = MESSAGE_TEXT;

      SET result_code = 1;
    end ;
		
    START TRANSACTION; 
			
		SELECT pr02_flag_append,pr02_var_task,pr02_var_batch,pr02_id_terminal,pr02_flag_type,pr02_bill_no,IFNULL(c102_id_prop,'')
		INTO flag_append,task_name,int_batch,id_terminal,flag_type,bill_no,prod_prop
		FROM prod02
		LEFT JOIN ctlm102 ON c102_id = pr02_id_item
		WHERE pr02_id = id;
		
		if(flag_append ='N') then 
			
					delete from prod021 where pr021_id_main =id;
					
					if(flag_type='M') then
					-- 主计划单独处理，获取其他计划的项目
					
							insert into  prod021(pr021_id,pr021_id_item,pr021_dec_qty,pr021_id_unit,pr021_id_main,
							pr021_flag_state,pr021_id_terminal,pr021_flag_scale,
							pr021_dec_batch,pr021_id_pr011,pr021_id_prop,pr021_var_phase,pr021_flag_comp,
							pr021_id_unit_w,pr021_id_supp,pr021_var_cata,pr021_int_line)
							select a.*,@rownum := @rownum + 1 as line from (	
								select UUID_SHORT() ,pr021_id_item,sum(pr021_dec_qty),pr021_id_unit,id,
								'正常',b.pr02_id_terminal,pr021_flag_scale,
								sum(pr021_dec_qty)/b.pr02_int_batch,'' as t1,pr021_id_prop,pr021_var_phase,pr021_flag_comp,
								pr021_id_unit_w,pr021_id_supp,pr021_var_cata 
								from prod021,prod02 a,prod02 b
								where a.pr02_var_main=bill_no
								and a.pr02_flag_state ='正常'
								and a.pr02_id=pr021_id_main
								and b.pr02_id=id 
								group by pr021_id_item,b.pr02_id_terminal,pr021_flag_scale,
									pr021_id_prop,pr021_var_phase,pr021_flag_comp,pr021_id_unit_w,pr021_id_supp,pr021_var_cata,pr021_dec_qty/a.pr02_dec_plan
									)a ,(SELECT @rownum := 0) c
							order by pr021_var_phase;
										
							update prod021 ,prod02 a,prod02 b,prod021 c
							set c.pr021_id_source=a.pr02_id
							where a.pr02_var_main=b.pr02_bill_no
							and a.pr02_flag_state ='正常'
							and a.pr02_id=prod021.pr021_id_main
							and b.pr02_id=id
							and  c.pr021_id_main =b.pr02_id
							and c.pr021_dec_qty = prod021.pr021_dec_qty
							and c.pr021_id_item=prod021.pr021_id_item;
			
							select  group_concat(c102_id_item,' ',c102_var_item,' ',FORMAT(pr02_dec_plan,2) SEPARATOR '\r\n' ) into var_desc
							from prod02,ctlm102 
							where pr02_var_main=bill_no and c102_id=pr02_id_item;
							
							update prod02 set pr02_var_desc= CONCAT('合并详情:','\r\n',var_desc) where pr02_id=id;
								
							set var_desc='';
							select group_concat(a.c102_var_item,' -->',b.c102_var_item,'(',FORMAT(pr02_dec_plan,2),')' SEPARATOR '\r\n') into var_desc
							from prod021,ctlm102 a,prod02,ctlm102 b
							where a.c102_id=pr021_id_item
							and prod021.pr021_id_source<>''
							and pr021_id_main =id
							and pr02_id=pr021_id_source
							and prod02.pr02_id_item = b.c102_id_item;
			
							update prod02 set pr02_var_desc= CONCAT(pr02_var_desc,'\r\n\r\n分料后:','\r\n',var_desc) 	where pr02_id=id;

					else
																															 
							#Routine body goes here...
							insert into  prod021(pr021_id,pr021_id_item,pr021_dec_qty,pr021_id_unit,pr021_id_main,
							pr021_flag_state,pr021_id_terminal,pr021_flag_scale,pr021_dec_batch,pr021_id_pr011,
							pr021_id_prop,pr021_var_phase,pr021_flag_comp,pr021_id_unit_w,pr021_int_line,
							pr021_id_supp,pr021_var_cata)
							select UUID_SHORT(),prod011.pr011_id_item,prod011.pr011_dec_qty*(prod02.pr02_dec_plan/prod01.pr01_dec_sum),if(pr011_id_unit='g'or pr011_id_unit='kg','kg',pr011_id_unit),prod02.pr02_id,
							'正常','',pr011_flag_scale,prod011.pr011_dec_qty*(prod02.pr02_dec_plan/prod01.pr01_dec_sum)/pr02_int_batch,pr011_id,
							ctlm102.c102_id_prop,pr011_var_prop,'N',pr011_id_unit,pr011_int_step*100+pr011_int_line,
							prod011.pr011_id_manufacturer ,pr011_var_cata 
							from prod01,prod011,prod02,ctlm102
							where prod011.pr011_id_main= prod01.pr01_id
							and prod02.pr02_id=id
							and prod01.pr01_id=prod02.pr02_id_formula
							and prod01.pr01_flag_state='正常'
							and prod011.pr011_id_item=ctlm102.c102_id
							order by prod011.pr011_int_step,prod011.pr011_int_line,pr011_var_cata	;	
									
					end if;			
								
					#按任务单生成批次数  生成批次 
					select prod02.pr02_id_formula,prod02.pr02_int_batch ,pr02_var_batchs into id_pr01,num,var_batch_old from prod02 where prod02.pr02_id=id;
					
					if var_batch_old='' or isnull(var_batch_old) then 
					
							select prod01.pr01_var_code into var_code from prod01 where prod01.pr01_id=id_pr01;		
							set sign = "-";
							set date_opr = DATE_FORMAT(now(),'%y%m%d');
							
							while i <= num 	do 
								##	set var_batch =CONCAT(var_code,sign,date_opr,sign,LPAD(i,int_p,'0'));
									set var_batch =func_get_batch(CONCAT(var_code,sign,date_opr,sign),2);
									if (i=num)THEN 
										 set str =concat(str,var_batch);
										 else

									set str =concat(str,var_batch,',');
									end if;
									set i = i + 1; 
							
						end while; 
						
						update prod02 set pr02_var_batchs = str where prod02.pr02_id=id;
					end if;
			
			
			else
			##增补计划
							
					select prod02.pr02_id,prod02.pr02_date_plan,pr023_var_batch,prod02.pr02_date_plan,prod02.pr02_date_prod,pr02_id_terminal 
					into id_source,date_work ,var_batch_old,date_plan,date_prod ,id_terminal 
					from prod02,prod023 
					where prod02.pr02_bill_no = task_name 
					and prod023.pr023_id_main= prod02.pr02_id 
					and prod023.pr023_int_line=int_batch; 
					
					select count(1) into counts
					from prod02
					where  pr02_id_source=id_source and pr02_id<> id;
					
					update prod02 set prod02.pr02_id_source = id_source,prod02.pr02_bill_no=CONCAT(task_name,'-',counts+1),
					prod02.pr02_date_plan = date_plan,
					prod02.pr02_date_prod = date_prod,
					pr02_var_batchs=CONCAT(var_batch_old,'-',counts+1),
					pr02_date_plan=date_work,pr02_int_year=YEAR(NOW()),pr02_int_month=MONTH(NOW()),pr02_id_table='pr02',pr02_id_unit='kg',
					pr02_var_remark=CONCAT(pr02_var_reason,'-----',task_name,'(',var_batch_old,')增补计划'),
					pr02_date_audit_act=now(),
					pr02_flag_comp='N',
					pr02_id_terminal=id_terminal
					where prod02.pr02_id = id
					and prod02.pr02_flag_append = 'Y';
					
					delete from prod021 where pr021_id_main=id;
						
					insert into prod021(pr021_id,pr021_flag_state,pr021_id_main,pr021_id_item,pr021_dec_qty,
					pr021_id_unit,pr021_dec_batch,pr021_id_pr011,pr021_flag_comp,pr021_int_year,
					pr021_int_month,pr021_flag_scale,pr021_id_unit_w,pr021_id_terminal)	
					
					select uuid_short(),'正常',id,pr02_id_item,pr02_dec_plan,
					'kg',pr02_dec_plan,id,'N',YEAR(NOW()),
					MONTH(NOW()),'0','kg',id_terminal 
					from prod02,ctlm102
					where prod02.pr02_id=id and
					prod02.pr02_id_item=ctlm102.c102_id;
							
			end if;
			
			##更改制定终端，过敏、非过敏等等；
			
			select c301_id_prop into var_prop from ctlm301 where c301_id=id_terminal;
			
			#T23030104
			#工作站自动分配定制
			/**
			#1.小于等于3公斤的用05编号工作站
			UPDATE prod021,ctlm301 
			SET pr021_id_terminal = c301_id
			WHERE pr021_id_main = id
			AND pr021_dec_batch <= 3
			AND c301_id_terminal = '05'
			AND c301_flag_state = '正常';
			
			#2.大于3公斤并且小于6公斤的用04编号工作站
			UPDATE prod021,ctlm301
			SET pr021_id_terminal = c301_id
			WHERE pr021_id_main = id
			AND pr021_dec_batch > 3
			AND pr021_dec_batch < 6
			AND c301_id_terminal = '04'
			AND c301_flag_state = '正常';
			
			#3.大于等于6公斤的：
			#3.1 粉末属性的分配02编号工作站，液体属性的分配03编号工作站
			UPDATE prod021,ctlm301
			SET pr021_id_terminal = c301_id
			WHERE pr021_id_main = id
			AND pr021_dec_batch >= 6
			AND pr021_id_prop = '粉末'
			AND c301_id_terminal = '02'
			AND c301_flag_state = '正常';
			
			UPDATE prod021,ctlm301
			SET pr021_id_terminal = c301_id
			WHERE pr021_id_main = id
			AND pr021_dec_batch >= 6
			AND pr021_id_prop = '液体'
			AND c301_id_terminal = '03'
			AND c301_flag_state = '正常';
			
			#3.2 非粉末、液体的随机分配
			SET rand_s = CEILING(RAND() * 9 +1);
			SET rand_i = rand_s % 2;
			IF rand_i = 0 THEN
				UPDATE prod021,ctlm301
				SET pr021_id_terminal = c301_id
				WHERE pr021_id_main = id
				AND pr021_dec_batch >= 6
				AND IFNULL(pr021_id_prop,'') <> '粉末'
				AND IFNULL(pr021_id_prop,'') <> '液体'
				AND c301_id_terminal = '02'
				AND c301_flag_state = '正常';
	
			ELSE
				UPDATE prod021,ctlm301
				SET pr021_id_terminal = c301_id
				WHERE pr021_id_main = id
				AND pr021_dec_batch >= 6
				AND IFNULL(pr021_id_prop,'') <> '粉末'
				AND IFNULL(pr021_id_prop,'') <> '液体'
				AND c301_id_terminal = '03'
				AND c301_flag_state = '正常';
				
			END IF;

			IF prod_prop = '' THEN
			  #产品属性为空时和之前一样分配
				update prod021 set pr021_id_terminal =(select c301_id from ctlm301 where IFNULL(c301_id_prop,'') = IFNULL(pr021_id_prop,'') and c301_flag_reg='Y' 
				and c301_flag_formula='Y' and c301_flag_state = '正常' limit 1 )
				where prod021.pr021_id_main=id
				and IFNULL(pr021_id_prop,'') <>''
				and IFNULL(pr021_id_prop,'') <> IFNULL(var_prop,'');
				
				update prod021 set pr021_id_terminal =(select c301_id from ctlm301 where IFNULL(c301_id_prop,'') = '' and c301_flag_reg='Y' 
				and c301_flag_formula='Y' and c301_flag_state = '正常' limit 1 )
				where prod021.pr021_id_main = id
				and IFNULL(pr021_id_terminal,'') = ''
				and IFNULL(pr021_id_prop,'') = '';
				
				UPDATE prod021 
				SET pr021_id_terminal = id_terminal
				WHERE IFNULL(pr021_id_terminal,'') = ''
				AND pr021_id_main = id;
				
			ELSE
				#产品属性不为空时 只能从无属性或一样属性的工作站中获取
				SELECT c301_id INTO id_terminal 
				FROM ctlm301
				WHERE c301_id_prop = prod_prop
				AND c301_flag_reg = 'Y'
				AND c301_flag_formula = 'Y'
				AND c301_flag_state = '正常'
				LIMIT 1;
				
				UPDATE prod021 
				SET pr021_id_terminal = id_terminal
				WHERE IFNULL(pr021_id_terminal,'') = ''
				AND pr021_id_main = id;
				
				SELECT c301_id INTO id_terminal 
				FROM ctlm301
				WHERE IFNULL(c301_id_prop,'') = ''
				AND c301_flag_reg = 'Y'
				AND c301_flag_formula = 'Y'
				AND c301_flag_state = '正常'
				LIMIT 1;
				
				UPDATE prod021 
				SET pr021_id_terminal = id_terminal
				WHERE IFNULL(pr021_id_terminal,'') = ''
				AND pr021_id_main = id;
				
			END IF;
			**/
			# 先根据物料属性分配
			UPDATE prod021 SET pr021_id_terminal = (SELECT c301_id FROM ctlm301 WHERE IFNULL(c301_id_prop,'') = IFNULL(pr021_id_prop,'') AND c301_flag_reg = 'Y' 
				AND c301_flag_formula = 'Y' AND c301_flag_state = '正常' LIMIT 1)
			WHERE pr021_id_main = id
			AND IFNULL(pr021_id_prop,'') <> '';
			
			UPDATE prod021 
			SET pr021_id_terminal = id_terminal
			WHERE IFNULL(pr021_id_terminal,'') = ''
			AND pr021_id_main = 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 ;