PROCEDURE T_ADD_CARE_TASK
IS
v_time VARCHAR(12);
v_careId NUMBER(16);
v_doctorId NUMBER(16);
v_patientId NUMBER(16);
v_paperId NUMBER(16);
v_userName VARCHAR2(50);
v_paperName VARCHAR2(128);
v_taskDesc VARCHAR2(256);
v_msgText VARCHAR2(256);
v_RetCount NUMBER(2);
v_Cursor MYCURSOR;
v_crId NUMBER(16);
BEGIN
OPEN v_Cursor FOR
SELECT TO_CHAR(A.TIME,'YYYYMMDDHH24MI'),A.CARE_ID,B.DOCTOR_ID,B.PATIENT_ID,B.PAPER_ID
INTO v_time,v_careId,v_doctorId,v_patientId,v_paperId
FROM T_CARE_TIME_INFO A
INNER JOIN T_DOCTOR_CARE_INFO B
ON A.CARE_ID = B.CARE_ID;
FETCH v_Cursor INTO v_time,v_careId,v_doctorId,v_patientId,v_paperId;
WHILE v_Cursor%FOUND LOOP
IF v_time = TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') THEN
v_crId:=SEQ_CARE_RECORD.nextval; -- T_CARE_RECORD ID
SELECT USER_NAME INTO v_userName FROM T_DOCTOR_INFO WHERE DOCTOR_ID = v_doctorId; ---获取医生名称
SELECT PAPER_TITLE INTO v_paperName FROM T_PAPER WHERE PAPER_ID = v_paperId; ---获取问卷名称
v_taskDesc:=v_userName||'医生要求您进行'||v_paperName||'随访';
v_msgText:='{"doctorId":'||v_doctorId||',"crId":'||v_crId||',"paperId":"'||v_paperId||'","desc":"'||v_taskDesc||'"}';
INSERT INTO T_TASK_INFO (TASK_ID, TASK_TYPE, FROM_TYPE, FROM_ID, TO_ID, TASK_STATUS, CREATE_TIME, TO_TYPE, TASK_CONTENT)
VALUES (SEQ_TASK_INFO_ID.NEXTVAL, 1, 1, v_doctorId, v_patientId, 1, sysdate, 2, v_msgText);
---插入随访历史记录表
INSERT INTO T_CARE_RECORD (ID, CARE_ID, PATIENT_ID, PAPER_ID, STATUS, CREATE_TIME)
VALUES (v_crId, v_careId, v_patientId, v_paperId, 0, sysdate);
v_RetCount:=v_RetCount+1;
END IF;
FETCH v_Cursor INTO v_time,v_careId,v_doctorId,v_patientId,v_paperId;
END LOOP;
--关闭游标
CLOSE v_Cursor;
IF v_RetCount>0 THEN
COMMIT;
END IF;
END;
end SY_TASK_PKG;
IS
v_time VARCHAR(12);
v_careId NUMBER(16);
v_doctorId NUMBER(16);
v_patientId NUMBER(16);
v_paperId NUMBER(16);
v_userName VARCHAR2(50);
v_paperName VARCHAR2(128);
v_taskDesc VARCHAR2(256);
v_msgText VARCHAR2(256);
v_RetCount NUMBER(2);
v_Cursor MYCURSOR;
v_crId NUMBER(16);
BEGIN
OPEN v_Cursor FOR
SELECT TO_CHAR(A.TIME,'YYYYMMDDHH24MI'),A.CARE_ID,B.DOCTOR_ID,B.PATIENT_ID,B.PAPER_ID
INTO v_time,v_careId,v_doctorId,v_patientId,v_paperId
FROM T_CARE_TIME_INFO A
INNER JOIN T_DOCTOR_CARE_INFO B
ON A.CARE_ID = B.CARE_ID;
FETCH v_Cursor INTO v_time,v_careId,v_doctorId,v_patientId,v_paperId;
WHILE v_Cursor%FOUND LOOP
IF v_time = TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') THEN
v_crId:=SEQ_CARE_RECORD.nextval; -- T_CARE_RECORD ID
SELECT USER_NAME INTO v_userName FROM T_DOCTOR_INFO WHERE DOCTOR_ID = v_doctorId; ---获取医生名称
SELECT PAPER_TITLE INTO v_paperName FROM T_PAPER WHERE PAPER_ID = v_paperId; ---获取问卷名称
v_taskDesc:=v_userName||'医生要求您进行'||v_paperName||'随访';
v_msgText:='{"doctorId":'||v_doctorId||',"crId":'||v_crId||',"paperId":"'||v_paperId||'","desc":"'||v_taskDesc||'"}';
INSERT INTO T_TASK_INFO (TASK_ID, TASK_TYPE, FROM_TYPE, FROM_ID, TO_ID, TASK_STATUS, CREATE_TIME, TO_TYPE, TASK_CONTENT)
VALUES (SEQ_TASK_INFO_ID.NEXTVAL, 1, 1, v_doctorId, v_patientId, 1, sysdate, 2, v_msgText);
---插入随访历史记录表
INSERT INTO T_CARE_RECORD (ID, CARE_ID, PATIENT_ID, PAPER_ID, STATUS, CREATE_TIME)
VALUES (v_crId, v_careId, v_patientId, v_paperId, 0, sysdate);
v_RetCount:=v_RetCount+1;
END IF;
FETCH v_Cursor INTO v_time,v_careId,v_doctorId,v_patientId,v_paperId;
END LOOP;
--关闭游标
CLOSE v_Cursor;
IF v_RetCount>0 THEN
COMMIT;
END IF;
END;
end SY_TASK_PKG;