手术状态查询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'