DROP PROCEDURE IF EXISTS createopinion33;
# 创建存储过程
CREATE PROCEDURE createopinion33()
BEGIN
DECLARE s1 int;
DECLARE _count1 int default 0;
DECLARE n1 int;
DECLARE _count_n1 int default 0;
DECLARE S_ID bigint(20);
DECLARE F_ID bigint(20);
DECLARE F_RID bigint(20);
DECLARE O_ID bigint(20);
DECLARE E_TYPE int;
DECLARE TABLE_NAME varchar(30);
# opinion意见需要的字段
DECLARE _content longtext;
DECLARE _member_id decimal(19);
DECLARE _member_name varchar(255);
DECLARE _create_time varchar(255);
DECLARE niban_oppion_send longtext;
DECLARE hegao_oppion longtext;
DECLARE niban_oppion_recevie longtext;
# 游标循环控制变量
DECLARE done INT DEFAULT 0;
# 定义游标结果集
DECLARE edocSummaryIds CURSOR FOR SELECT id,form_app_id,form_recordid,SUBSTRING(ID,1,LENGTH(ID)-5) oldId,edoc_type FROM edoc_summary;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SELECT count(*) INTO _count1 FROM edoc_summary;
SET s1=1;
# 打开游标
OPEN edocSummaryIds;
WHILE s1<_count1+1 DO
FETCH edocSummaryIds INTO S_ID,F_ID,F_RID,O_ID,E_TYPE;
SELECT tablename INTO TABLE_NAME FROM form_definition_temp WHERE DI = F_ID;
IF E_TYPE = 0 THEN
SET niban_oppion_send = '';
BEGIN
DECLARE niban CURSOR FOR SELECT content,create_user_id,create_time from edoc_opinion_sj WHERE type='documentSendAudit' and edoc_type=E_TYPE AND edoc_id=O_ID;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE niban;
SELECT count(*) INTO _count_n1 FROM edoc_opinion_sj WHERE type='documentSendAudit' and edoc_type=E_TYPE AND edoc_id=O_ID;
SET n1=1;
OPEN niban;
WHILE n1<_count_n1+1 DO
FETCH niban INTO _content,_member_id,_create_time;
SELECT NAME INTO _member_name FROM org_member WHERE id = _member_id;
IF niban_oppion_send='' THEN
SET niban_oppion_send =CONCAT( _content,'(',_member_name,_create_time,')');
ELSE
SET niban_oppion_send = CONCAT(niban_oppion_send,'\r\n', _content,'(',_member_name,_create_time,')');
END IF;
SET n1=n1+1;
END WHILE;
CLOSE niban;
SET _count_n1 = 0;
END;
SELECT niban_oppion_send;
-- 核稿意见
SET hegao_oppion = '';
BEGIN
DECLARE hegao CURSOR FOR SELECT content,create_user_id,create_time from edoc_opinion_sj WHERE type='AUDIT' and edoc_type=E_TYPE AND edoc_id=O_ID;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE hegao;
SELECT count(*) INTO _count_n1 FROM edoc_opinion_sj WHERE type='AUDIT' and edoc_type=E_TYPE AND edoc_id=O_ID;
SET n1=1;
OPEN hegao;
WHILE n1<_count_n1+1 DO
FETCH hegao INTO _content,_member_id,_create_time;
SELECT NAME INTO _member_name FROM org_member WHERE id = _member_id;
IF hegao_oppion='' THEN
SET hegao_oppion =CONCAT( _content,'(',_member_name,_create_time,')');
ELSE
SET hegao_oppion = CONCAT(hegao_oppion,'\r\n', _content,'(',_member_name,_create_time,')');
END IF;
SET n1=n1+1;
END WHILE;
SET n1=1;
close hegao;
SET _count_n1=0;
END;
SELECT hegao_oppion;
#执行更新程序
SET @sqlStr =CONCAT( 'UPDATE ',TABLE_NAME,' SET field0015="',niban_oppion_send,'",field0016="',hegao_oppion,'" WHERE id =',F_RID);
PREPARE stmt from @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSEIF E_TYPE = 1 THEN
SET niban_oppion_recevie = '';
BEGIN
DECLARE nibanRE CURSOR FOR SELECT content,create_user_id,create_time from edoc_opinion_sj WHERE type='receiveExecutionAudit' and edoc_type=E_TYPE AND edoc_id=O_ID;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE nibanRE;
SELECT count(*) INTO _count_n1 FROM edoc_opinion_sj WHERE type='receiveExecutionAudit' and edoc_type=E_TYPE AND edoc_id=O_ID;
SET n1=1;
OPEN nibanRE;
WHILE n1<_count_n1+1 DO
FETCH nibanRE INTO _content,_member_id,_create_time;
SELECT NAME INTO _member_name FROM org_member WHERE id = _member_id;
IF niban_oppion_recevie='' THEN
SET niban_oppion_recevie =CONCAT( _content,'(',_member_name,_create_time,')');
ELSE
SET niban_oppion_recevie = CONCAT(niban_oppion_recevie,'\r\n', _content,'(',_member_name,_create_time,')');
END IF;
SET n1=n1+1;
END WHILE;
SET n1=1;
CLOSE nibanRE;
SET _count_n1=0;
END;
SELECT niban_oppion_recevie;
SET @sqlStr =CONCAT( 'UPDATE ',TABLE_NAME,' SET field0017="',niban_oppion_recevie,'" WHERE id =',F_RID);
PREPARE stmt from @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
SET s1=s1+1;
END WHILE;
SET s1=1;
CLOSE edocSummaryIds;
END;
call createopinion33();