手术相关表和手术状态变化值

手术状态查询sql：

SELECT vaa.OPERATION_TIME,
       vaa.Patient_Area,
       vaa.PATIENT_NAME,
       vaa.SEX,
       vaa.PATIENT_AGE,
       vaa.OPERATION_NAME,
       t.ROOM_NAME, 
      (CASE 
       WHEN (select dial_plate from AIS_FORM_DATA afd where afd.dial_plate='監控結束時間' and afd.form_ruid=vaa.OPERATION_APPLY_ID)is  not null
       THEN '復甦結束'
       WHEN (select dial_plate from AIS_FORM_DATA afd where afd.dial_plate='入PACU時間' and afd.form_ruid=vaa.OPERATION_APPLY_ID)is  not null
       THEN '復甦中'
       WHEN (select dial_plate from AIS_FORM_DATA afd where afd.dial_plate='病人離室去向' and afd.value='PACU'and afd.form_ruid=vaa.OPERATION_APPLY_ID)is  not null
       THEN 'PACU'
       WHEN (select dial_plate from AIS_FORM_DATA afd where afd.dial_plate='病人離室去向' and afd.value='病房'and afd.form_ruid=vaa.OPERATION_APPLY_ID)is  not null
       THEN '病房'
       WHEN (select dial_plate from AIS_FORM_DATA afd where afd.dial_plate='病人離室去向' and afd.value='ICU'and afd.form_ruid=vaa.OPERATION_APPLY_ID)is  not null
       THEN 'ICU' 
       WHEN t.OPER_STATE is  not null
       THEN t.OPER_STATE 
       ELSE '無記錄' END)as OPER_STATE
  FROM jhoris.AIS_OPS_ROOM t ,
       jhoris.view_ais_arrangement vaa 
 WHERE  vaa.OPERATION_APPLY_ID=ruid(+)and vaa.APPLY_STATE!='3' and vaa.Patient_Area_Code='11S' order by vaa.OPERATION_TIME desc

  已经做手术sql

 select c.bed_name       as BedName,
        c.patient_no     as PatientNO,
        c.patient_name   as PatientName,
        c.OPERATION_NAME as OperationName,
        c.OPERATION_TIME as OperationTime,
        c.OperationState as OperationState,
        c.sort_no        as sort_no
   from (select bed.bed_name,
                i.patient_no,
                i.patient_name,
                v.OPERATION_NAME,
                v.OPERATION_TIME,
                (CASE
                  WHEN (r.OUT_ROOM_TIME) is not null THEN
                   decode((select dd.value
                            from AIS_FORM_DATA dd
                           where dd.form_ruid = v.OPERATION_APPLY_ID
                             and dd.dial_plate = '病人離室去向' and  rownum=1),
                          'ICU',
                          'ICU',
                          '病房',
                          '病房',
                          'PACU',
                          'PACU',
                          '',
                          '离开手术室',
                          (select d.value
                             from AIS_FORM_DATA d
                            where d.form_ruid = v.OPERATION_APPLY_ID
                              and d.dial_plate = '病人離室去向'  and rownum=1))
                
                  WHEN (r.ANESTHESIA_END_TIME) is not null THEN
                   '麻醉结束'
                  WHEN (r.OPER_END_TIME) is not null THEN
                   '手术结束'
                  WHEN (r.OPER_BEGIN_TIME) is not null THEN
                   '手术开始'
                  when (r.ANESTHESIA_BEGIN_TIME) is not null THEN
                   '麻醉开始'
                  when (r.ENTER_ROOM_TIME) is not null then
                   '进入手术室'
                  else
                   ''
                END) as OperationState,
                bed.sort_no as sort_no
           from ptm_ipr_inmaininfo   i,
                met_res_bed          bed,
                AIS_OPS_REG          r,
                VIEW_AIS_ARRANGEMENT v
          where i.nurse_cell_code = :NurseCellCode
            and v.OPERATION_TIME > :OperationTime
            and i.bed_no = bed.bed_no
            and r.ruid = v.OPERATION_APPLY_ID
            and i.inpatient_no = v.Patient_Id
          order by bed.sort_no) c
  where c.OperationState is not null

一、状态值为空的时已经预约但没开始手术的数据

二、手术状态变化是根据对于的进入手术室时间、麻醉开始时间、手术开始时间、手术结束时间、麻醉结束时间、离开手术室时间是否有值来做判断的

对于的状态变化值有： 进入手术室-》麻醉开始-》手术开始-》手术结束-》麻醉结束-》离开手术室，可能去向：1、PACU  2、 病房  3、ICU 、4、无记录

 【说明】 1、离开手术室后患者会在PACU中呆，然后看病情情况决定是回病区，还是进ICU

              2、对于局部麻醉的手术，有离开手术室时间，但在记录表AIS_FORM_DATA中没有数据，因为局部麻醉的小手术，做完直接走了

 

 

   问题2：已经申请的手术在哪里能看到？
   答2： 医生账号下有，“手术申请信息查询”菜单，未审核前的手术申请可以修改，审核后不能修改。如果开错，手术室可以作废，医生可以重新开.
   对应表数据如下：
  select t.*,t.rowid from   RSV_OPS_APPLY t  where  t.patient_id='0000633585'