--Begin-- 住院科室出院收入统计 -- 2011-08-31 00:26:15 declare V_TEMPID T_CTLTEMP.F_TEMPID%TYPE; begin SELECT COUNT(*) INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='Z' AND F_NAMECN='住院科室出院收入统计'; IF V_TEMPID =0 THEN SELECT nvl(MAX(F_TEMPID),0)+1 INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='Z'; ELSE SELECT F_TEMPID INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='Z' AND F_NAMECN='住院科室出院收入统计'; END IF; DELETE FROM T_TEMPTYPE WHERE F_SYSID='Z' AND F_TYPEID='00001'; INSERT INTO T_TEMPTYPE (F_SYSID,F_TYPEID,F_TYPENAME,F_MEMO) VALUES ('Z','00001','自定义报表',''); -- T_TEMPTYPE ------------------------------------------ DELETE FROM T_CTLTEMP WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLTEMP1 WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLEDIT WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLABEL WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLINE WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLDATE WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCMB WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCURR WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRID WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRIDCOL WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLPARAMS WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLLIST WHERE F_SYSID='Z' AND F_TEMPID=V_TEMPID; -- DELETE ------------------------------------------ INSERT INTO T_CTLTEMP (F_SYSID,F_TEMPID,F_TEMPTYPE,F_NAMECN,F_CREATEDATE,F_CREATEPERSON,F_DATASRC,F_PACK,F_SQL,F_MODEL,F_MODELDIRECT,F_MODELCOLCOUNT,F_MODELROWCOUNT,F_PRINTER,F_PAPER,F_WIDTH,F_HEIGHT,F_COLOR,F_BORDERCOLOR,F_LOCK,F_ISSYS,F_ZOOM,F_ORIENTATION,F_MENUSHOW,F_MODELCOUNT) VALUES ('Z',V_TEMPID,'00001','住院科室出院收入统计','2011-08-30','管理员','0','0','','0','0','1','1','','A4','2101','2971','16777215','0','0','0','0.1','0','1',''); --T_CTLTEMP------------------------------------------ INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'1000','LABEL','Label0',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'1001','LABEL','Label1',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'1002','LABEL','label1002',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'1003','LABEL','label1003',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'2000','EDIT','Edit0','编辑框0'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'2001','EDIT','Edit1','编辑框1'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'2002','EDIT','Edit2','编辑框2'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('Z',V_TEMPID,'7000','GRID','Grid0','网格0'); --T_CTLTEMP1------------------------------------------ INSERT INTO T_CTLEDIT (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_NAMECN,F_FIELD,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_DATA,F_DECIMAL,F_FORMATFLOAT,F_CAPITAL,F_DIGITAL,F_READONLY,F_PRINT,F_BORDERSTYLE,F_TAB,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_DEFAULT,F_CURRFORMAT,F_FULLPRINT,F_SHOWBTN,F_VISUAL) VALUES ('Z',V_TEMPID,'2000','Edit0','编辑框0','CS_起始日期','0','2','0','0','0','0','0','0','9','0','1','0','0','373','185','288','56','宋体','0','12','-16777208','','0','0','0','1'); INSERT INTO T_CTLEDIT (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_NAMECN,F_FIELD,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_DATA,F_DECIMAL,F_FORMATFLOAT,F_CAPITAL,F_DIGITAL,F_READONLY,F_PRINT,F_BORDERSTYLE,F_TAB,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_DEFAULT,F_CURRFORMAT,F_FULLPRINT,F_SHOWBTN,F_VISUAL) VALUES ('Z',V_TEMPID,'2001','Edit1','编辑框1','CS_截至日期','0','2','0','0','0','0','0','0','9','0','1','0','0','706','185','296','58','宋体','0','12','-16777208','','0','0','0','1'); INSERT INTO T_CTLEDIT (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_NAMECN,F_FIELD,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_DATA,F_DECIMAL,F_FORMATFLOAT,F_CAPITAL,F_DIGITAL,F_READONLY,F_PRINT,F_BORDERSTYLE,F_TAB,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_DEFAULT,F_CURRFORMAT,F_FULLPRINT,F_SHOWBTN,F_VISUAL) VALUES ('Z',V_TEMPID,'2002','Edit2','编辑框2','XT_BMMC','0','2','0','0','0','0','0','0','9','0','1','0','0','1201','185','336','56','宋体','0','12','-16777208','','0','0','0','1'); --T_CTLEDIT------------------------------------------ INSERT INTO T_CTLLABEL (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_FIELD,F_CAPTION,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_PRINT,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_VISUAL) VALUES ('Z',V_TEMPID,'1000','Label0','','住院科室出院收入统计','0','2','0','0','1','410','45','812','95','宋体','10','20','-16777208','1'); INSERT INTO T_CTLLABEL (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_FIELD,F_CAPTION,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_PRINT,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_VISUAL) VALUES ('Z',V_TEMPID,'1001','Label1','','统计日期：','0','2','0','0','1','180','185','265','56','宋体','00','12','-16777208','1'); INSERT INTO T_CTLLABEL (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_FIELD,F_CAPTION,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_PRINT,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_VISUAL) VALUES ('Z',V_TEMPID,'1002','label1002','','－','0','2','0','0','1','661','185','48','56','宋体','00','11','-16777208','1'); INSERT INTO T_CTLLABEL (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_FIELD,F_CAPTION,F_HALIGN,F_VALIGN,F_DISPERSE,F_MULLINE,F_PRINT,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FONTNAME,F_FONTSTYLE,F_FONTSIZE,F_FONTCOLOR,F_VISUAL) VALUES ('Z',V_TEMPID,'1003','label1003','','住院科室：','0','2','0','0','1','1011','185','188','56','宋体','00','12','-16777208','1'); --T_CTLLABEL------------------------------------------ --T_CTLLINE------------------------------------------ --T_CTLDATE------------------------------------------ --T_CTLCMB------------------------------------------ --T_CTLCURR------------------------------------------ INSERT INTO T_CTLGRID (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_NAMECN,F_PACK,F_SQL,F_MASTERFIELD,F_FIXROWS,F_ROWS,F_COLS,F_FIXCOLOR,F_DETAILCOLOR,F_LINECOLOR,F_VISUAL,F_LEFT,F_TOP,F_WIDTH,F_HEIGHT,F_FIXHEIGHT,F_DETAILHEIGHT,F_FONTNAME1,F_FONTSTYLE1,F_FONTSIZE1,F_FONTCOLOR1,F_FONTNAME2,F_FONTSTYLE2,F_FONTSIZE2,F_FONTCOLOR2,F_PRINTHEADER,F_PRINTDETAIL,F_PRINTCOLOR,F_PRINTVLINE,F_PRINTHLINE,F_INTERVAL,F_BORDERSTYLE,F_BORDERCOLOR,F_FILLALL,F_CURRENCY,F_CURRSYMBOL,F_CURRDIGITAL,F_CURRDECIMAL) VALUES ('Z',V_TEMPID,'7000','Grid0','网格0','0','select * from ( select ''01'' bh0000,''入院人数''xmmc00,to_char(count(distinct a.ZYID00))SZ0000 from BQ_BRLDXX a where QSZT00=''新入'' AND YXBZ00=''1'' and QSRQ00>=:起始日期 and QSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) union all select ''03'',''转入人数'',to_char(count(a.ZYID00)) from BQ_BRLDXX a where QSZT00=''转入'' and YXBZ00=''1'' and QSRQ00>=:起始日期 and QSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) union all select ''04'',''转出人数'',to_char(count( a.ZYID00)) from BQ_BRLDXX a where JSZT00=''转出'' and YXBZ00=''1'' and JSRQ00>=:起始日期 and JSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) union all SELECT ''05'',''在院病人占用床日数'',to_char(sum(least(to_date(BQJSRQ,''YYYYMMDD''),to_date(:截至日期,''YYYYMMDD'')+1)-to_date(greatest(QSRq00,:起始日期),''YYYYMMDD''))) FROM bq_brldxx b where QSZT00 in (''新入'',''转入'') and YXBZ00=''1'' and QSRQ00<=:截至日期 and BQJSRQ > :起始日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) union all SELECT DECODE (row000, 1, ''02'', 2, ''06'', 3,''07'', 4, ''08'', 5, ''09'', 6,''10'',7,''11'', 8,''12'',9,''13'') bh0000, DECODE (row000, 1, ''出院人数'',2, ''出院患者住院总天数'',3,''出院患者平均住院天数'',4,''出院患者总费用'',5,''出院患者医疗费'', 6,''出院患者药品费'',7,''出院患者药品比率'',8,''出院患者人均费用'',9,''出院患者人天费用'') xmmc00, DECODE (row000, 1, to_char(cyrs00),2,to_char(cyts00),3,to_char(ROUND(cyts00/cyrs00,2)),4,to_char(zfy000),5,to_char(zfy000-ypf000),6,to_char(ypf000), 7,to_char(ROUND(ypf000/zfy000,4)*100||''%''),8,to_char(ROUND(zfy000/cyrs00,2)),9,to_char(ROUND(zfy000/cyts00,2)) ) SZ0000 FROM (SELECT ROWNUM row000, (select count(distinct a.ZYID00) from BQ_BRLDXX a where JSZT00=''出院'' AND YXBZ00=''1'' and JSRQ00>=:起始日期 and JSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) ) cyrs00, (SELECT sum(greatest(to_date(decode(c.sjcyrq,''20991231'',c.cyrq00,c.sjcyrq),''YYYYMMDD'')-to_date(c.RYRQ00,''YYYYMMDD''),1)) FROM ZY_BRXXB0 c where ZYID00 in (select ZYID00 from bq_brldxx b where JSZT00=''出院'' AND YXBZ00=''1'' and JSRQ00>=:起始日期 and JSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) )) cyts00, (SELECT sum(ypf000) FROM ZH_CYBRBB c where ZYID00 in (select ZYID00 from bq_brldxx b where JSZT00=''出院'' AND YXBZ00=''1'' and JSRQ00>=:起始日期 and JSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) )) ypf000, (SELECT sum(zfy000) FROM ZH_CYBRBB c where ZYID00 in (select ZYID00 from bq_brldxx b where JSZT00=''出院'' AND YXBZ00=''1'' and JSRQ00>=:起始日期 and JSRQ00<=:截至日期 and BQH000=(SELECT bqh000 FROM bm_bqks00 WHERE ksh000 = :XT_BMBM) )) zfy000 FROM bm_bmbm00 a WHERE ROWNUM <= 9)) order by bh0000 ','','1','1','3','16777215','13428940','536870911','1','188','265','1265','1770','80','60','宋体','0','12','-16777208','宋体','0','12','-16777208','1','1','0','1','1','1','0','0','0','0','','9','2'); --T_CTLGRID------------------------------------------ INSERT INTO T_CTLGRIDCOL (F_SYSID,F_TEMPID,F_SEQUENCE,F_COLID,F_COLFIELD,F_COLCAPTION,F_COLALIGN,F_COLWIDTH,F_COLFIXED,F_COLVISUAL,F_COLPRINT,F_COLPAGETYPE,F_COLDATATYPE,F_COLDATANAME) VALUES ('Z',V_TEMPID,'7000','0','BH0000','序号','0','188','0','1','1','0','0',''); INSERT INTO T_CTLGRIDCOL (F_SYSID,F_TEMPID,F_SEQUENCE,F_COLID,F_COLFIELD,F_COLCAPTION,F_COLALIGN,F_COLWIDTH,F_COLFIXED,F_COLVISUAL,F_COLPRINT,F_COLPAGETYPE,F_COLDATATYPE,F_COLDATANAME) VALUES ('Z',V_TEMPID,'7000','2','SZ0000','值','0','450','0','1','1','0','0',''); INSERT INTO T_CTLGRIDCOL (F_SYSID,F_TEMPID,F_SEQUENCE,F_COLID,F_COLFIELD,F_COLCAPTION,F_COLALIGN,F_COLWIDTH,F_COLFIXED,F_COLVISUAL,F_COLPRINT,F_COLPAGETYPE,F_COLDATATYPE,F_COLDATANAME) VALUES ('Z',V_TEMPID,'7000','1','XMMC00','项目名称','0','606','0','1','1','0','0',''); --T_CTLGRIDCOL------------------------------------------ INSERT INTO T_SQLPARAMS (F_SYSID,F_TEMPID,F_SEQUENCE,F_PARAMID,F_PARAMNAME,F_VALUETYPE,F_EDITTYPE,F_COMBOSTR,F_DEFAULT,F_DEFAUL1) VALUES ('Z',V_TEMPID,'7000','0','起始日期','0','4','','',''); INSERT INTO T_SQLPARAMS (F_SYSID,F_TEMPID,F_SEQUENCE,F_PARAMID,F_PARAMNAME,F_VALUETYPE,F_EDITTYPE,F_COMBOSTR,F_DEFAULT,F_DEFAUL1) VALUES ('Z',V_TEMPID,'7000','1','截至日期','0','4','','',''); INSERT INTO T_SQLPARAMS (F_SYSID,F_TEMPID,F_SEQUENCE,F_PARAMID,F_PARAMNAME,F_VALUETYPE,F_EDITTYPE,F_COMBOSTR,F_DEFAULT,F_DEFAUL1) VALUES ('Z',V_TEMPID,'7000','2','XT_BMBM','0','0','','',''); --T_SQLPARAMS------------------------------------------ --T_SQLLIST------------------------------------------ commit; end; --End---住院科室出院收入统计