查询未处理的检验和检查结果数量

select count(0) from (
select CR.OUTPAT_NUM as 'outpatNum',PIH.PAT_NAME as 'patName','检验' as 'recordType',
        to_char(PLR.CRITICAL_DATE,'yyyy-mm-dd hh24:mi:ss') as 'reportDate',
        nvl(PLR.LAB_REPORT_ITEM_NAME,(select m.data_value_remark from sysmn.mav_emr_core_dict m where m.basicdatavalue_id = plr.lab_report_item_code)) as 'reportItemName',
        plr.report_result ||plr.unit as 'result',
        (select esp.person_name from sysmn.ehr_service_personal esp where cr.DISPOSE_USER_ID= esp.person_id) as 'disposeDoctor',
        to_char(CR.LAST_TIME,'yyyy-mm-dd hh24:mi:ss') as 'disposeDate',
        cr.CRITICAL_RECORD_ID as 'recordId',
        cr.FINISHED as 'finished',
        cr.INPAT_NUM as 'inpatNum',
        PIH.DEPT_ID as 'deptId',
        nvl((select esp.person_code from sysmn.ehr_service_personal esp where PLR.CREATE_USER_ID= esp.person_id),
            (select esp.person_code from sysmn.ehr_service_personal esp where esp.person_name = PLR.CREATE_USER_ID and rownum=1)) as 'reportDoctorNum',
        nvl((select esp.person_name from sysmn.ehr_service_personal esp where PLR.CREATE_USER_ID= esp.person_id),PLR.CREATE_USER_ID) as 'reportDoctoerName',
        (select esp.person_code from sysmn.ehr_service_personal esp where cr.DISPOSE_USER_ID= esp.person_id) as 'disposeDoctorNum',
        (select esp.person_code from sysmn.ehr_service_personal esp where PL.REQ_DOCTOR_ID = esp.person_id) as 'reqDoctorNum',
        (select esp.person_name from sysmn.ehr_service_personal esp where PL.REQ_DOCTOR_ID = esp.person_id) as 'reqDoctorName',
        (select sdd.dept_name from sysmn.sys_dept_dict sdd where PIH.DEPT_ID = sdd.DEPT_ID) as 'deptName',
        -- (select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PL2.REQ_DOCTOR_ID = esp.PERSON_ID)as 'reqDoctorIdName',
        PIH.RESPONSIBLE_DOCTOR_PERSON_ID as 'responsibleDoctorPersonId',
        (select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PIH.RESPONSIBLE_DOCTOR_PERSON_ID = esp.PERSON_ID)as 'responsibleDoctorPersonName',
         -- PL2.REQ_DOCTOR_ID as 'reqDoctorId',
        to_char(pa.CREATE_DATETIME,'yyyy-mm-dd hh24:mi:ss') as 'reqDate',
           to_char(nvl(cr.NURSE_NOTICE_TIME,CR.NURSE_LAST_TIME),'yyyy-mm-dd hh24:mi:ss') as 'nurseLastTime',
        CR.NURSE_FINISHED AS 'nurseFinished',
        nvl((select user_name from sysmn.SYS_USER su where su.USER_ID = cr.NURSE_NOTICE_USERID),SU4.USER_NAME)AS 'disposeNurse',
        ward.DEPT_ALIAS as 'wardDeptName',
        pih.WARD_DEPT_ID as 'wardDeptId',
        PIH.BED_NO AS 'bedNo',
        bd.BED_LABEL as 'bedLabel',
        (case when ward.HOSPITAL_AREA_NO = '01' then '城中' when ward.HOSPITAL_AREA_NO = '02' then '阳湖'else ''  end) as 'hospitalAreaNo'
        from emr.CRITICAL_RECORD CR
        left join emr.LAB_CRITICAL_RECORD PLR on PLR.LAB_CRITICAL_ID = cr.ITEM_ID
        left join emr.PATIENT_LAB PL  on PL.REQ_NUM = plr.REQ_NUM
        left join emr.PATIENT_IN_HOSPITAL PIH on cr.INPAT_NUM = PIH.INPAT_NUM
        left join emr.patient_orders pa on plr.REQ_NUM = pa.REQ_NUM and plr.serial_num = pa.serial_number
        left join SYSMN.SYS_USER SU4 on SU4.USER_ID = CR.NURSE_USER_ID
        left join sysmn.sys_dept_dict ward on ward.dept_id = pih.WARD_DEPT_ID
        left join sysmn.SYS_BED_DICT bd on bd.WARD_DEPT_ID = pih.WARD_DEPT_ID and bd.BED_NO = pih.BED_NO

         WHERE (pih.RESPONSIBLE_DOCTOR_PERSON_ID =  ?
                     or  (PL.REQ_DOCTOR_ID = ?  or cr.DISPOSE_USER_ID  = ?)
                
                    or pih.dept_id in
                    
                )
            
            
            
            
            
            
                and PLR.CRITICAL_DATE >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
            
            
                and to_date(?,'yyyy-mm-dd hh24:mi:ss') >= PLR.CRITICAL_DATE
            
            
            
                and PIH.DEPT_ID not in
                (
                    ?
                )
            

            and cr.INPAT_NUM is not null
            and cr.OUTPAT_NUM is null
            and CR.RECOED_TYPE = '1'
            and (PLR.notice_status = '0' or PLR.notice_status is null) 

        union

        select  cr2.OUTPAT_NUM as 'outpatNum' , PIH.PAT_NAME as 'patName', '检查' as 'recordType',
        to_char(per2.DT_ALERT,'yyyy-mm-dd hh24:mi:ss') as 'reportDate',
        nvl(PLI.EXAM_ITEM_NAME,(select m.data_value_remark from sysmn.mav_emr_core_dict m where m.basicdatavalue_id = pli.exam_item_id)) as 'reportItemName',
        PER2.VAL_RSTRPTLAB as 'result',
        (select esp.person_name from sysmn.ehr_service_personal esp where cr2.DISPOSE_USER_ID= esp.person_id) as 'disposeDoctor',
        to_char(CR2.LAST_TIME,'yyyy-mm-dd hh24:mi:ss') as 'disposeDate',
        cr2.CRITICAL_RECORD_ID as 'recordId',
        cr2.FINISHED as 'finished',
        cr2.INPAT_NUM as 'inpatNum',
        PER2.CODE_EMP_ALERT as 'reportDoctorNum',
        PER2.NAME_EMP_ALERT as 'reportDoctoerName',
        PIH.DEPT_ID as 'deptId',
        (select esp.person_code from sysmn.ehr_service_personal esp where cr2.DISPOSE_USER_ID= esp.person_id) as 'disposeDoctorNum',
        nvl((select esp.person_code from sysmn.ehr_service_personal esp where pe.REQ_DOCTOR_ID = esp.person_id),(select  esp.person_code from sysmn.ehr_service_personal esp where po.CREATE_PERSON_ID = esp.person_id)) as 'reqDoctorNum',
        nvl((select esp.person_name from sysmn.ehr_service_personal esp where pe.REQ_DOCTOR_ID = esp.person_id),(select  esp.person_name from sysmn.ehr_service_personal esp where po.CREATE_PERSON_ID = esp.person_id)) as 'reqDoctorName',
        (select sdd.dept_name from sysmn.sys_dept_dict sdd where PIH.DEPT_ID = sdd.DEPT_ID) as 'deptName',
       --(select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PE2.REQ_DOCTOR_ID = esp.PERSON_ID)as 'reqDoctorIdName',
        PIH.RESPONSIBLE_DOCTOR_PERSON_ID as 'responsibleDoctorPersonId',
        (select esp.PERSON_NAME from sysmn.EHR_SERVICE_PERSONAL esp where PIH.RESPONSIBLE_DOCTOR_PERSON_ID = esp.PERSON_ID)as 'responsibleDoctorPersonName',
        -- PE2.REQ_DOCTOR_ID as 'reqDoctorId',
        to_char(po.CREATE_DATETIME,'yyyy-mm-dd hh24:mi:ss') as 'reqDate',
        to_char(CR2.NURSE_NOTICE_TIME,'yyyy-mm-dd hh24:mi:ss') as 'nurseLastTime',
        CR2.NURSE_FINISHED AS 'nurseFinished',
        nvl((select user_name from sysmn.SYS_USER su where su.USER_ID = CR2.NURSE_NOTICE_USERID),SU4.USER_NAME)AS 'disposeNurse',
        ward.DEPT_ALIAS as 'wardDeptName',
        pih.WARD_DEPT_ID as 'wardDeptId',
        PIH.BED_NO AS 'bedNo',
        bd.BED_LABEL as 'bedLabel',
        (case when ward.HOSPITAL_AREA_NO = '01' then '城中' when ward.HOSPITAL_AREA_NO = '02' then '阳湖' else '' end) as 'hospitalAreaNo'
        from emr.CRITICAL_RECORD CR2
        left join EMR.WJZ_MAG PER2 on PER2.ID_ALERT = cr2.ITEM_ID
        left join emr.PATIENT_EXAM_ITEM PLI  on PLI.REQ_NUM = per2.NO_APPLYFORM and PLI.SERIAL_NUMBER = PER2.SUBNO_APPLYFORM
        left join emr.PATIENT_EXAM pe on pe.REQ_NUM = per2.NO_APPLYFORM
        left join emr.PATIENT_IN_HOSPITAL PIH on cr2.INPAT_NUM = PIH.INPAT_NUM
        left join emr.patient_orders po on per2.NO_APPLYFORM = po.REQ_NUM  and po.serial_number = per2.subno_applyform
        left join SYSMN.SYS_USER SU4 on SU4.USER_ID = CR2.NURSE_USER_ID
        left join sysmn.sys_dept_dict ward on ward.dept_id = pih.WARD_DEPT_ID
        left join sysmn.SYS_BED_DICT bd on bd.WARD_DEPT_ID = pih.WARD_DEPT_ID and bd.BED_NO = pih.BED_NO
         WHERE (pih.RESPONSIBLE_DOCTOR_PERSON_ID =  ?
                
                    or pih.dept_id in
                    
                )
            
            
            
            
            
            
                and per2.DT_ALERT >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
            
            
                and to_date(?,'yyyy-mm-dd hh24:mi:ss') >= per2.DT_ALERT
            
            
            
                and PIH.DEPT_ID not in
                (
                    ?
                )
            
            and CR2.OUTPAT_NUM is  null
            and CR2.RECOED_TYPE = '4') tmp_count