门急诊/住院推送数据处理语句参考

admin

--先清理门诊重复的推送数据，删除最晚的

1. select t1.*,t1.rowid FROM PRESC_TIMELINE t1
   
2. INNER JOIN PRESC_TIMELINE t2 on  t1.timeline_id <t2.timeline_id AND t1.presc_id = t2.presc_id and t1.type=1 and t2.type=1 and t1.type=t2.type;  

复制代码

--新增门诊点评反馈记录（要求推送的1-14天之内反馈，时间在早8点晚20点之内）（有些条件需要调整）

1. insert into peaas.presc_timeline (timeline_id,presc_id, crter,type,result,crte_time)
   
2. select (（select max(to_number(timeline_id)) from PEAAS.PRESC_TIMELINE )+rownum),t.id,doctor_code,2,'接受',to_date(
   
3. to_char(l.crte_time+ROUND(DBMS_RANDOM.VALUE(1, 14)),'yyyy-mm-dd')||' ' || ROUND(DBMS_RANDOM.VALUE(8, 20)) ||':'|| ROUND(DBMS_RANDOM.VALUE(0, 59)) ||':'||  ROUND(DBMS_RANDOM.VALUE(0, 59))
   
4. ,'yyyy-mm-dd HH24:mi:ss')from peaas.presc t  inner join  peaas.presc_timeline l on t.id=l.presc_id and l.result like '%推送成功%' where t.doctor_receive_date is null;
   
5. commit;

复制代码

1. update peaas.presc t set  t.received_doctor_code=t.doctor_code,t.is_doctor_received=1,
   
2. t.doctor_receive_date=(select l.crte_time from peaas.presc_timeline l where t.id=l.presc_id and l.type=2),
   
3. t.doctor_receive_reason = '' where t.doctor_receive_date is null
   
4. and exists (select 0 from peaas.presc_timeline l where t.id=l.presc_id);
   
5. commit;

复制代码

--先清理住院诊重复的推送数据，删除最晚的

1. select t1.*,t1.rowid FROM mre.PRESC_TIMELINE t1
   
2. INNER JOIN mre.PRESC_TIMELINE t2 on  t1.timeline_id <t2.timeline_id AND t1.presc_id = t2.presc_id and t1.type=1 and t2.type=1 and t1.type=t2.type;  

复制代码

1. insert into mre.presc_timeline (timeline_id,presc_id, crter,type,result,crte_time)
   
2. select (（select max(to_number(timeline_id)) from mre.PRESC_TIMELINE )+rownum),w.record_id,doctor_code,2,'接受',to_date(
   
3. to_char(l.crte_time+ROUND(DBMS_RANDOM.VALUE(1, 14)),'yyyy-mm-dd')||' ' || ROUND(DBMS_RANDOM.VALUE(8, 20)) ||':'|| ROUND(DBMS_RANDOM.VALUE(0, 59)) ||':'||  ROUND(DBMS_RANDOM.VALUE(0, 59))
   
4. ,'yyyy-mm-dd HH24:mi:ss') from (
   
5. select distinct t.record_id,t.doctor_code,t.doctor_name from mre_record_error t where t.record_id in (select p.record_id from pat_visit p WHERE exists (select 1 from mre.presc_timeline pt where pt.presc_id = p.record_id and pt.type = '1')
   
6. and p.is_doctor_received is null) and t.doctor_code is not null)w left join  mre.presc_timeline l on w.record_id = l.presc_id;
   
7.   commit;

复制代码

--更新主表

1. update mre.pat_visit t set  t.received_doctor_code=(select l.crter from mre.presc_timeline l where t.record_id=l.presc_id and l.type=2),
   
2. t.is_doctor_received=1,
   
3. t.doctor_receive_date=(select l.crte_time from mre.presc_timeline l where t.record_id=l.presc_id and l.type=2),
   
4. where t.doctor_receive_date is null
   
5. and exists (select 0 from mre.presc_timeline l where t.record_id=l.presc_id) and t.doctor_receive_date is null;
   
6. commit;

复制代码

--插入反馈表

1. insert into CHECK_FEEDBACK(ID, RECORD_ID, PATIENT_ID, VISIT_ID, ORDER_NO, ORDER_SUB_NO, FEEDBACK_TYPE, FEEDBACK_DATE, IS_RECEIVED, USER_CODE, FEEDBACK_CONTENT, IS_NEW)
   
2. select ((select  max(id) from CHECK_FEEDBACK) + rownum),w.record_id,w.patient_id,w.visit_id,w.order_no,w.order_sub_no,2,
   
3. (select p.crte_time from mre.PRESC_TIMELINE p where p.presc_id=w.record_id and p.type=2),
   
4. 1,w.doctor_code,'接受',1 from (
   
5. SELECT DISTINCT o.record_id,p.patient_id,o.visit_id,o.order_no,o.order_sub_no,2,null,1,o.doctor_code,'接受',1
   
6.   FROM orders o
   
7.   JOIN mre_record_drug_error mrde
   
8. ON (o.id = mrde.order_no)
   
9.   JOIN mre_record_error mre
   
10. ON (mre.record_id = o.record_id)
    
11.   JOIN pat_visit p
    
12. ON (p.record_id = o.record_id)
    
13. WHERE (mre.id = mrde.mre_error_id and 1 = 1 AND mre.new_flag = 1 AND
    
14.   not exists (select 1  from mre.check_feedback f where f.record_id = o.record_id and f.order_no = o.order_no and f.order_sub_no = o.order_sub_no) and
    
15.   exists  (select 1  from mre.presc_timeline pt where pt.presc_id = p.record_id and pt.type = '1'))
    
16. )w

复制代码

--更新门诊点评人姓名

1. update HISSYSMANAGER.PRESC_CHECK_RECORD t set t.checker_name=(select j.username from peaas.jill_userdef j where t.checker_code=j.userid)
   
2. where exists (select 0 from peaas.jill_userdef j where t.checker_code=j.userid);--门诊

复制代码

--更新住院点评人姓名

1. update  MRE.MRE_RECORD_ERROR t set t.checker_name=(select j.username from peaas.jill_userdef j where t.checker_code=j.userid)
   
2. where exists (select 0 from peaas.jill_userdef j where t.checker_code=j.userid);--住院

复制代码

1. select t.*, t.rowid from PAT_ORDERS_ER t where t.record_id in (
   
2. select record_id from PAT_ORDERS_ER t where t.record_id in (select t.record_id from mre.pat_visit t  WHERE T.LAST_PEOPLE_CHECK_TIME IS NOT NULL AND NOT EXISTS
   
3. (SELECT 0 FROM  mre.pat_orders_er e WHERE t.record_id=e.record_id and e.checker_code!='MACHINE' and t.last_people_check_time=e.check_time)
   
4. ) and t.checker_code!='MACHINE' group by record_id having count(0)>1) and t.checker_code!='MACHINE' order by t.record_id,t.check_time;

复制代码

--更新主表最后点评时间

1. update mre.pat_visit t set t.last_people_check_time = (SELECT e.check_time FROM  mre.pat_orders_er e WHERE t.record_id=e.record_id and e.checker_code!='MACHINE' ) where t.record_id in (
   
2. select t.record_id from mre.pat_visit t  WHERE T.LAST_PEOPLE_CHECK_TIME IS NOT NULL AND NOT EXISTS
   
3. (SELECT 0 FROM  mre.pat_orders_er e WHERE t.record_id=e.record_id and e.checker_code!='MACHINE' and t.last_people_check_time=e.check_time));

复制代码