CREATE DEFINER=`mysqladmin`@`%` PROCEDURE `pr_wf_excel_init`(
in pa_cnc varchar(100),
OUT OSA_RETURN_CODE VARCHAR(5),
OUT OSA_SQLERRM VARCHAR(500)
)
__END: BEGIN
declare ls_process_ins_id varchar(64);
declare ls_process_defind_id varchar(64);
declare ls_process_initfile_id varchar(64);
declare ls_process_initfile_path varchar(64);
declare ls_process_initfile_type varchar(128);
declare ls_process_initfile_type_copy varchar(128);
declare ls_process_initnode varchar(128);
declare ls_count_pqct int;
declare ls_uuid varchar(64);
DECLARE done INT DEFAULT FALSE; -- 游标循环不能缺少的语句
DECLARE Cursor_File CURSOR FOR
(
-- t_ebom_cncroot-- t_ebom_cncfiles-- t_ebom_outfile
-- t_mbom_cnctec-- t_mbom_resfile-- 查询 填充文件id 文件路径
SELECT DISTINCT fileid,ftype,typeid FROM
(
select a.fileid,'规格更改书' as ftype,'GGGG' as typeid
from t_ebom_cncroot a where a.eecnc=pa_cnc and cncbiltype='GGGG'
union all
select a.fileid,typedesc,tab.typeid from t_ebom_outfile a
join t_sys_classdital tab on a.fitype=tab.typeid
where a.eecnc=pa_cnc
union all
select a.fileid,typedesc,tab.typeid from t_mbom_resfile a
join t_sys_classdital tab on a.filetype=tab.typeid
where a.bgncnc=pa_cnc
)t
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 绑定控制变量到游标,游标循环结束自动转true
/*---------- 异常处理机制 模板 ----------*/
-- DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION 如果有了游标，就不能有 NOT FOUND
DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
OSA_RETURN_CODE = RETURNED_SQLSTATE, OSA_SQLERRM = MESSAGE_TEXT;
END;
set OSA_RETURN_CODE ="OK";
set OSA_SQLERRM="noErr";
-- 设置同一批次的uuid
set ls_uuid=REPLACE(UUID(),'-','');
-- 获取最新+有效的 工序
set done = false;
open Cursor_File;
FileLoop: loop fetch Cursor_File into ls_process_initfile_id,ls_process_initfile_path,ls_process_initfile_type;
if done then
leave FileLoop;
end if;
if instr(ls_process_initfile_type,'GGGG')>0 then set ls_process_initfile_type_copy = 'GGGG'; -- if 条件 =
elseif instr(ls_process_initfile_type,'PQCT')>0 then set ls_process_initfile_type_copy = ls_process_initfile_type; -- elseif关键字
-- elseif instr(ls_process_initfile_type,'PQCT')>0 then set ls_process_initfile_type = ls_process_initfile_type; -- elseif关键字
else set ls_process_initfile_type_copy = 'EBOM_OUTFILE';
end if;
-- 审批人信息
insert into t_approve_init_temp
with tab_act_info as(
SELECT
a.activity_id_,a.START_TIME_ as start_date,a.END_TIME_ as end_date,u.user_name,u.user_id,
ls_process_initfile_id as file_id,f_getfilepath(ls_process_initfile_id) as file_path
FROM act_ext_hi_taskinst a,t_sys_user u
WHERE a.ASSIGNEE_=u.user_id
and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
and a.END_TIME_ in
(
SELECT max(a.END_TIME_)
FROM act_ext_hi_taskinst a ,t_sys_user u
WHERE a.ASSIGNEE_=u.user_id
and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
and a.EXEC_RESULT_='OK'
and a.ACTIVITY_ID_ <> 'startEvent'
GROUP BY a.activity_id_
ORDER BY a.seq_
)
and a.ACTIVITY_ID_ <> 'startEvent'
ORDER BY a.seq_
)
select null,ls_uuid,s.activity_id_,s.start_date as start_date,s.end_date as end_date,
s.user_name,s.user_id,s.file_id,
s.file_path,k.app_row,k.app_cell,
f_getusersigpath(s.user_id) as sig_path,
-- case f_getusersigpath(s.user_id) when INSERT(f_getusersigpath(s.user_id),'/') then 'Y' end as ib_sign,
'sign_value' as insert_flag
from tab_act_info s join t_approve_init k on s.activity_id_ = k.app_wfnode
WHERE INSTR (ls_process_initfile_type_copy,k.app_type)>0
union all
-- 审批时间信息
select null,ls_uuid,l.app_wfnode,
(select k.start_date from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as start_date,
(select k.end_date from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as end_date,
(select k.user_name from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as user_name,
(select k.user_id from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as user_id,
(select k.file_id from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as file_id,
(select k.file_path from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as file_path,
l.app_row,l.app_cell,
'time' as sig_path,
'insert_time' as insert_flag
from t_approve_init l
where l.app_wfnode not in (
select s.activity_id_
from tab_act_info s
join t_approve_init k
on s.activity_id_ = k.app_wfnode
)
and instr(l.app_wfnode,
(SELECT left(a.activity_id_,10)
FROM act_ext_hi_taskinst a,t_sys_user u
WHERE a.ASSIGNEE_=u.user_id
and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
and a.END_TIME_ in
(
SELECT min(a.END_TIME_)
FROM act_ext_hi_taskinst a ,t_sys_user u
WHERE a.ASSIGNEE_=u.user_id
and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
and a.EXEC_RESULT_='OK'
and a.ACTIVITY_ID_ <> 'startEvent'
)
and a.ACTIVITY_ID_ <> 'startEvent'
ORDER BY a.seq_
)
)>0;
end loop FileLoop;
close Cursor_File;
select * from t_approve_init_temp l where l.uuid = ls_uuid;
END