-- Start of DDL Script for Function SD_HOSPITAL.SF_JY_GET_MZZYTJ_QSFLAG -- Generated 12-一月-2017 11:22:52 from SD_HOSPITAL@HOSPITAL CREATE OR REPLACE FUNCTION sf_jy_get_mzzytj_qsflag (P_MZZYBZ in char, P_SFTJ00 in char ) return varchar2 is V_RESULT varchar2(10); V_MZSFQS XT_XTCS00.Value0%type; V_TJSFQS XT_XTCS00.Value0%type; V_ZYSFQS XT_XTCS00.Value0%type; begin begin select trim(value0) into V_ZYSFQS from xt_xtcs00 where name00='JY_ZYSFQS'; exception when others then V_ZYSFQS:='Y'; end; begin select trim(value0) into V_MZSFQS from xt_xtcs00 where name00='JY_MZSFQS'; exception when others then V_MZSFQS:='N'; end; begin select trim(value0) into V_TJSFQS from xt_xtcs00 where name00='JY_TJSFQS'; exception when others then V_TJSFQS:='Y'; end; if ((P_MZZYBZ='1') and (V_ZYSFQS='Y')) or ((P_MZZYBZ='0') and (P_SFTJ00='N') and (V_MZSFQS='Y')) or ((P_MZZYBZ='0') and (P_SFTJ00='Y') and (V_MZSFQS='Y')) then V_RESULT:='1'; elsif ((P_MZZYBZ='1') and (V_ZYSFQS='N')) or ((P_MZZYBZ='0') and (P_SFTJ00='N') and (V_MZSFQS='N')) or ((P_MZZYBZ='0') and (P_SFTJ00='Y') and (V_MZSFQS='N')) then V_RESULT:='0'; end if; return V_RESULT; exception when others then raise_application_error(-20001,'获取签收标志失败，请联系管理员！*') ; end; / -- End of DDL Script for Function SD_HOSPITAL.SF_JY_GET_MZZYTJ_QSFLAG -- Start of DDL Script for Procedure SD_HOSPITAL.SP_JY_ZZSJTJ -- Generated 12-一月-2017 11:23:18 from SD_HOSPITAL@HOSPITAL CREATE OR REPLACE procedure sp_jy_zzsjtj ( PKSRQ00 in char, --开始日期 PJSRQ00 in char, --结束日期 PSTRSQL in varchar2 --其他条件 ) as strSQL1 varchar2(4000); --查询语句 v_sqlerror varchar2(4000); V_JYYWID JY_ZZSJTJ_TEMP.JYYWID%TYPE; V_BRXM00 JY_ZZSJTJ_TEMP.BRXM00%TYPE; V_ZYHGHH JY_ZZSJTJ_TEMP.ZYHGHH%TYPE; V_TXM000 JY_ZZSJTJ_TEMP.TXM000%TYPE; V_YBH000 JY_ZZSJTJ_TEMP.YBH000%TYPE; V_ZLXMJC JY_ZZSJTJ_TEMP.ZLXMJC%TYPE; V_MZZYBZ JY_ZZSJTJ_TEMP.MZZYBZ%TYPE; V_SFTJ00 JY_ZZSJTJ_TEMP.SFTJ00%TYPE; V_YXBZ00 JY_ZZSJTJ_TEMP.YXBZ00%TYPE; V_SQRQ00 JY_ZZSJTJ_TEMP.SQRQ00%TYPE; V_SQSJ00 JY_ZZSJTJ_TEMP.SQSJ00%TYPE; V_BBSDRQ JY_ZZSJTJ_TEMP.BBSDRQ%TYPE; V_BBSDSJ JY_ZZSJTJ_TEMP.BBSDSJ%TYPE; V_BBCYRQ JY_ZZSJTJ_TEMP.BBCYRQ%TYPE; V_BBCYSJ JY_ZZSJTJ_TEMP.BBCYSJ%TYPE; V_PYRQ00 JY_ZZSJTJ_TEMP.PYRQ00%TYPE; V_PYSJ00 JY_ZZSJTJ_TEMP.PYSJ00%TYPE; V_CBGRQ0 JY_ZZSJTJ_TEMP.CBGRQ0%TYPE; V_CBGSJ0 JY_ZZSJTJ_TEMP.CBGSJ0%TYPE; V_DYRQSJ JY_ZZSJTJ_TEMP.DYRQSJ%TYPE; V_TJSFQS xt_xtcs00.VALUE0%TYPE; V_MZSFQS xt_xtcs00.VALUE0%TYPE; V_ZYSFQS xt_xtcs00.VALUE0%TYPE; --检验前周转时间 V_JQZZSJ number; --室内周转时间 V_SNZZSJ number; --检验后周转时间 V_JHZZSJ number; --检验前周转时间中位数（常规） V_CGJQZZZWS number; --检验前周转时间中位数（急诊） V_JZJQZZZWS number; --室内周转时间中位数（常规） V_CGSNZZZWS number; --室内周转时间中位数 (急诊) V_JZSNZZZWS number; --检验前周转时间90百分位数（常规） V_CGJQZZBFWS number; --检验前周转时间90百分位数（急诊） V_JZJQZZBFWS number; --室内周转时间90百分位数（常规） V_CGSNZZBFWS number; --室内周转时间90百分位数 (急诊) V_JZSNZZBFWS number; V_COUNT0 number(10); V_COUNT1 number(10); V_ROWNUM0 number(10); V_ROWNUM1 number(10); V_ROWNUM2 number(10); V_ROWNUM3 number(10); type cursor_type is ref cursor; CUR_JY_ZZSJTJ cursor_type; -- MODIFICATION HISTORY -- Person Date Comments -- yushenjun 2016.09.22 新增周转时间统计for LIS-20160914-003 -- daihq 2016.10.17 增加写入PYRQ00,PYSJ00,CBGRQ0,CBGSJ0,DYRQSJ for LIS-20160812-005 -- daihq 2016.12.19 优化语句解决统计速度慢的问题 for LIS-20161215-001 begin delete from JY_ZZSJTJ; delete from JY_ZZSJTJ_TEMP; begin SELECT trim(VALUE0) INTO V_TJSFQS FROM xt_xtcs00 WHERE NAME00 ='JY_TJSFQS'; exception when others then V_TJSFQS:='N'; end; begin SELECT trim(VALUE0) INTO V_MZSFQS FROM xt_xtcs00 WHERE NAME00 ='JY_MZSFQS'; exception when others then V_MZSFQS:='N'; end; begin SELECT trim(VALUE0) INTO V_MZSFQS FROM xt_xtcs00 WHERE NAME00 ='JY_ZYSFQS'; exception when others then V_ZYSFQS:='Y'; end; sp_execute_sql('insert into JY_ZZSJTJ_TEMP(JYYWID,BRXM00,ZYHGHH,MZZYBZ,TXM000,YBH000,ZLXMJC,SFTJ00,YXBZ00,SQRQ00,'|| 'SQSJ00,BBSDRQ,BBSDSJ,BBCYRQ,BBCYSJ,PYRQ00,PYSJ00,CBGRQ0,CBGSJ0,DYRQSJ)'|| 'select a.JYYWID,a.BRXM00,a.ZYHGHH,a.MZZYBZ,a.TXM000,a.YBH000,a.ZLXMJC,a.SFTJ00,a.YXBZ00,a.SQRQ00,a.SQSJ00,a.BBSDRQ,a.BBSDSJ,a.BBCYRQ,a.BBCYSJ,'|| 'a.PYRQ00,a.PYSJ00,a.CBGRQ0,a.CBGSJ0,a.DYRQSJ from VW_JY_YW0000 a '|| ' where a.JGZT00 in (''6'',''8'',''9'') '||PSTRSQL|| ' and a.BBCYRQ between'''||PKSRQ00||''' AND '''||PJSRQ00||''' '); --'order by jyywid '; update JY_ZZSJTJ_TEMP a set JQZZSJ=ROUND(TO_NUMBER(decode(SF_JY_GET_MZZYTJ_QSFLAG(a.mzzybz,a.sftj00),'0',TO_DATE(BBSDRQ||' '||BBSDSJ,'YYYYMMDD HH24:MI:SS'),TO_DATE(PYRQ00||' '||PYSJ00,'YYYYMMDD HH24:MI:SS')) - TO_DATE(BBCYRQ||' '||BBCYSJ,'YYYYMMDD HH24:MI:SS')) * 24 * 60 ), SNZZSJ=ROUND(TO_NUMBER(TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS') - decode(SF_JY_GET_MZZYTJ_QSFLAG(a.mzzybz,a.sftj00),'0',TO_DATE(BBSDRQ||' '||BBSDSJ,'YYYYMMDD HH24:MI:SS'),TO_DATE(PYRQ00||' '||PYSJ00,'YYYYMMDD HH24:MI:SS'))) * 24 * 60 ), JYHSJ0=ROUND(TO_NUMBER(TO_DATE(DYRQSJ,'YYYYMMDD HH24:MI:SS') - TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS')) * 24 * 60 ); -- where (SFTJ00 = 'Y' AND V_TJSFQS = 'Y') or (MZZYBZ='0' and SFTJ00 = 'N' and V_MZSFQS='Y') or -- (MZZYBZ = '1' and V_ZYSFQS='Y'); /* update JY_ZZSJTJ_TEMP set JQZZSJ=ROUND(TO_NUMBER(TO_DATE(PYRQ00||' '||PYSJ00,'YYYYMMDD HH24:MI:SS') - TO_DATE(BBCYRQ||' '||BBCYSJ,'YYYYMMDD HH24:MI:SS')) * 24 * 60 ), SNZZSJ=ROUND(TO_NUMBER(TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS') - TO_DATE(PYRQ00||' '||PYSJ00,'YYYYMMDD HH24:MI:SS')) * 24 * 60 ), JYHSJ0=ROUND(TO_NUMBER(TO_DATE(DYRQSJ,'YYYYMMDD HH24:MI:SS') - TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS')) * 24 * 60 ) where not ((SFTJ00 = 'Y' AND V_TJSFQS = 'Y') or (MZZYBZ='0' and SFTJ00 = 'N' and V_MZSFQS='Y') or (MZZYBZ = '1' and V_ZYSFQS='Y')); */ -- open CUR_JY_ZZSJTJ for strSQL1; -- loop -- fetch CUR_JY_ZZSJTJ into V_JYYWID,V_BRXM00,V_ZYHGHH,V_MZZYBZ,V_TXM000,V_YBH000,V_ZLXMJC,V_SFTJ00,V_YXBZ00,V_SQRQ00,V_SQSJ00, -- V_BBSDRQ,V_BBSDSJ,V_BBCYRQ,V_BBCYSJ,V_PYRQ00,V_PYSJ00,V_CBGRQ0,V_CBGSJ0,V_DYRQSJ; -- exit when CUR_JY_ZZSJTJ%notfound; -- begin -- insert into JY_ZZSJTJ_TEMP(JYYWID,BRXM00,ZYHGHH,MZZYBZ,TXM000,YBH000,ZLXMJC,SFTJ00,YXBZ00,SQRQ00, -- SQSJ00,BBSDRQ,BBSDSJ,BBCYRQ,BBCYSJ,PYRQ00,PYSJ00,CBGRQ0,CBGSJ0,DYRQSJ) -- select V_JYYWID,V_BRXM00,V_ZYHGHH,V_MZZYBZ,V_TXM000,V_YBH000,V_ZLXMJC,V_SFTJ00,V_YXBZ00,V_SQRQ00,V_SQSJ00, -- V_BBSDRQ,V_BBSDSJ,V_BBCYRQ,V_BBCYSJ,V_PYRQ00,V_PYSJ00,V_CBGRQ0,V_CBGSJ0,V_DYRQSJ -- from dual; -- --where not exists (select 1 from JY_ZZSJTJ_TEMP where jyywid=V_JYYWID); -- if (trim(V_SFTJ00) = 'Y' AND V_TJSFQS = 'Y') or (V_MZZYBZ='0' and trim(V_SFTJ00) = 'N' and V_MZSFQS='Y') or -- (V_MZZYBZ = '1' and V_ZYSFQS='Y') then -- SELECT ROUND(TO_NUMBER(TO_DATE(BBSDRQ||' '||BBSDSJ,'YYYYMMDD HH24:MI:SS') - TO_DATE(BBCYRQ||' '||BBCYSJ,'YYYYMMDD HH24:MI:SS')) * 24 * 60 )JQZZSJ, -- ROUND(TO_NUMBER(TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS') - TO_DATE(BBSDRQ||' '||BBSDSJ,'YYYYMMDD HH24:MI:SS')) * 24 * 60 )SNZZSJ, -- ROUND(TO_NUMBER(TO_DATE(DYRQSJ,'YYYYMMDD HH24:MI:SS') - TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS')) * 24 * 60 )JHZZSJ into V_JQZZSJ,V_SNZZSJ,V_JHZZSJ -- from JY_ZZSJTJ_TEMP where jyywid=V_JYYWID; -- update JY_ZZSJTJ_TEMP set JQZZSJ=V_JQZZSJ,SNZZSJ=V_SNZZSJ,JYHSJ0=V_JHZZSJ -- where jyywid=V_JYYWID; -- else -- SELECT ROUND(TO_NUMBER(TO_DATE(PYRQ00||' '||PYSJ00,'YYYYMMDD HH24:MI:SS') - TO_DATE(BBCYRQ||' '||BBCYSJ,'YYYYMMDD HH24:MI:SS')) * 24 * 60 )JQZZSJ, -- ROUND(TO_NUMBER(TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS') - TO_DATE(PYRQ00||' '||PYSJ00,'YYYYMMDD HH24:MI:SS')) * 24 * 60 )SNZZSJ, -- ROUND(TO_NUMBER(TO_DATE(DYRQSJ,'YYYYMMDD HH24:MI:SS') - TO_DATE(CBGRQ0||' '||CBGSJ0,'YYYYMMDD HH24:MI:SS')) * 24 * 60 )JHZZSJ into V_JQZZSJ,V_SNZZSJ,V_JHZZSJ -- from JY_ZZSJTJ_TEMP where jyywid=V_JYYWID; -- -- update JY_ZZSJTJ_TEMP set JQZZSJ=V_JQZZSJ,SNZZSJ=V_SNZZSJ,JYHSJ0=V_JHZZSJ -- where jyywid=V_JYYWID; -- end if; -- exception -- when others then -- v_sqlerror:=SUBSTR(SQLERRM, 1, 2000); -- RAISE_APPLICATION_ERROR(-20001, v_sqlerror); -- end; -- end loop; -- close CUR_JY_ZZSJTJ; --中位数 select nvl(median(a.JQZZSJ),0) into V_CGJQZZZWS from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by JQZZSJ;--检验前周转时间中位数（常规） select nvl(median(a.JQZZSJ),0) into V_JZJQZZZWS from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by JQZZSJ;--检验前周转时间中位数（急诊） select nvl(median(a.SNZZSJ),0) into V_CGSNZZZWS from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by SNZZSJ;--室内周转时间中位数（常规） select nvl(median(a.SNZZSJ),0) into V_JZSNZZZWS from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by SNZZSJ;--室内周转时间中位数 (急诊) --90百分位数 select (0.9*count(1)-trunc(0.9*count(1))) into V_COUNT0 from JY_ZZSJTJ_TEMP a where a.yxbz00='N';--常规 select (0.9*count(1)-trunc(0.9*count(1))) into V_COUNT1 from JY_ZZSJTJ_TEMP a where a.yxbz00='Y';--急诊 if V_COUNT0<>0 then --判断90%n是否为小数 select (trunc(0.9*count(1))+1) into V_ROWNUM0 from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by JQZZSJ); select nvl(n.JQZZSJ,0) into V_CGJQZZBFWS from (select rownum no,JQZZSJ from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by JQZZSJ))n where no=V_ROWNUM0; select nvl(n.SNZZSJ,0) into V_CGSNZZBFWS from (select rownum no,SNZZSJ from (select a.SNZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by SNZZSJ))n where no=V_ROWNUM0; else select (0.9*count(1)),(0.9*count(1)+1) into V_ROWNUM0,V_ROWNUM1 from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by JQZZSJ); select (0.9*count(1)),(0.9*count(1)+1) into V_ROWNUM2,V_ROWNUM3 from (select a.SNZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by SNZZSJ); select nvl(trunc(sum(n.JQZZSJ)*0.5),0) into V_CGJQZZBFWS from (select rownum no,JQZZSJ from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by JQZZSJ))n where no in (V_ROWNUM0,V_ROWNUM1); select nvl(trunc(sum(n.SNZZSJ)*0.5),0) into V_CGSNZZBFWS from (select rownum no,SNZZSJ from (select a.SNZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='N' order by SNZZSJ))n where no in (V_ROWNUM2,V_ROWNUM3); end if; if V_COUNT1<>0 then select (trunc(0.9*count(1))+1) into V_ROWNUM0 from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by JQZZSJ); select n.JQZZSJ into V_JZJQZZBFWS from (select rownum no,JQZZSJ from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by JQZZSJ))n where no=V_ROWNUM0; select n.SNZZSJ into V_JZSNZZBFWS from (select rownum no,SNZZSJ from (select a.SNZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by SNZZSJ))n where no=V_ROWNUM0; else select (0.9*count(1)),(0.9*count(1)+1) into V_ROWNUM0,V_ROWNUM1 from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by JQZZSJ); select (0.9*count(1)),(0.9*count(1)+1) into V_ROWNUM2,V_ROWNUM3 from (select a.SNZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by SNZZSJ); select nvl(trunc(sum(n.JQZZSJ)*0.5),0) into V_JZJQZZBFWS from (select rownum no,JQZZSJ from (select a.JQZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by JQZZSJ))n where no in (V_ROWNUM0,V_ROWNUM1); select nvl(trunc(sum(n.SNZZSJ)*0.5),0) into V_JZSNZZBFWS from (select rownum no,SNZZSJ from (select a.SNZZSJ from JY_ZZSJTJ_TEMP a where a.yxbz00='Y' order by SNZZSJ))n where no in (V_ROWNUM2,V_ROWNUM3); end if; insert into JY_ZZSJTJ (ZZSJLB,JQZZZS,SNZZZS,JQZZBF,SNZZBF) select '常规',V_CGJQZZZWS,V_CGSNZZZWS,V_CGJQZZBFWS,V_CGSNZZBFWS from dual; insert into JY_ZZSJTJ (ZZSJLB,JQZZZS,SNZZZS,JQZZBF,SNZZBF) select '急诊',V_JZJQZZZWS,V_JZSNZZZWS,V_JZJQZZBFWS,V_JZSNZZBFWS from dual; commit; exception when others then rollback; RAISE_APPLICATION_ERROR(-20003, NVL(SQLERRM, '原因不明出错！*')); end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_JY_ZZSJTJ -- Start of DDL Script for Procedure SD_HOSPITAL.SP_JY_YQXMTJ -- Generated 12-一月-2017 11:25:40 from SD_HOSPITAL@HOSPITAL CREATE OR REPLACE PROCEDURE sp_jy_yqxmtj (PKSRQ00 in char, --开始日期 PJSRQ00 in char, --结束日期 PSTRSQL in varchar2, --其他条件，申请医生、申请科室、报告医生、仪器代号 PSTRSQL1 in varchar2 ) as --Person Date Comments --xutz 20160107 修改统计方式 for LIS-20151207-009; begin sp_execute_sql('delete from JY_YQXMTJ_temp'); sp_execute_sql('delete from JY_YQXMTJ'); sp_execute_sql('insert into JY_YQXMTJ_temp(je0000,yqdh00) '||PSTRSQL1); sp_execute_sql('insert into JY_YQXMTJ(yqcs00,ybcs00,yqdh00,yqjc00) '|| 'SELECT SUM(yqcs00) yqcs00,SUM(ybcs00) ybcs00,t.yqdh00,yqjc00 FROM ( '|| 'SELECT 1 yqcs00,0 ybcs00,B.YQDH00 from jy_jyjg00 a,vw_jy_yw0000 b where a.ybh000=b.ybh000 and a.pyrq00=b.pyrq00 '||PSTRSQL || 'UNION ALL '|| 'SELECT 1 yqcs00,0 ybcs00,B.YQDH00 from jy_XJBGJG a,vw_jy_yw0000 b where a.ybh000=b.ybh000 and a.pyrq00=b.pyrq00 '||PSTRSQL || 'UNION ALL '|| 'select 0 yqcs00,1 ybcs00,b.yqdh00 from jy_XJBGJG a,vw_jy_yw0000 b where a.ybh000=b.ybh000 and a.pyrq00=b.pyrq00 '||PSTRSQL || 'GROUP BY a.pyrq00,a.ybh000,b.yqdh00 '|| 'UNION ALL '|| 'select 0 yqcs00,1 ybcs00,b.yqdh00 from jy_jyjg00 a,vw_jy_yw0000 b where a.ybh000=b.ybh000 and a.pyrq00=b.pyrq00 '||PSTRSQL || 'GROUP BY a.pyrq00,a.ybh000,b.yqdh00) t,jy_jyyqzd y where t.yqdh00=y.yqdh00 '|| 'GROUP BY t.yqdh00 ,yqjc00'); commit; end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_JY_YQXMTJ