--Begin-- 抗菌药物各部门使用情况表 -- 2013-11-12 09:56:40 declare V_TEMPID T_CTLTEMP.F_TEMPID%TYPE; begin SELECT COUNT(*) INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='3' AND F_NAMECN='抗菌药物各部门使用情况表'; IF V_TEMPID =0 THEN SELECT nvl(MAX(F_TEMPID),0)+1 INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='3'; ELSE SELECT F_TEMPID INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='3' AND F_NAMECN='抗菌药物各部门使用情况表'; END IF; DELETE FROM T_TEMPTYPE WHERE F_SYSID='3' AND F_TYPEID='00001'; INSERT INTO T_TEMPTYPE (F_SYSID,F_TYPEID,F_TYPENAME,F_MEMO) VALUES ('3','00001','自定义报表',''); -- T_TEMPTYPE ------------------------------------------ DELETE FROM T_CTLTEMP WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLTEMP1 WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLEDIT WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLABEL WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLINE WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLDATE WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCMB WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCURR WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRID WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRIDCOL WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLPARAMS WHERE F_SYSID='3' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLLIST WHERE F_SYSID='3' 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 ('3',V_TEMPID,'00001','抗菌药物各部门使用情况表','2012-6-1 2','测试员','0','0','select ''抗菌药物各部门使用情况表'' t1,''日期：'' t2,''--'' t3 from dual','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 ('3',V_TEMPID,'2000','EDIT','Edit0','编辑框0'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('3',V_TEMPID,'2001','EDIT','Edit1','编辑框1'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('3',V_TEMPID,'2002','EDIT','Edit2','编辑框2'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('3',V_TEMPID,'2003','EDIT','Edit3','编辑框3'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('3',V_TEMPID,'2004','EDIT','Edit4','编辑框4'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('3',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 ('3',V_TEMPID,'2000','Edit0','编辑框0','T1','2','2','0','0','0','0','0','0','9','0','1','0','0','458','42','1072','61','宋体','1','14','-2147483640','','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 ('3',V_TEMPID,'2001','Edit1','编辑框1','T2','0','2','0','0','0','0','0','0','9','0','1','0','0','29','161','135','56','宋体','0','10','-2147483640','','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 ('3',V_TEMPID,'2002','Edit2','编辑框2','CS_开始日期','0','2','0','0','0','0','0','0','9','0','1','0','0','164','164','265','56','宋体','0','10','-2147483640','','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 ('3',V_TEMPID,'2003','Edit3','编辑框3','T3','2','2','0','0','0','0','0','0','9','0','1','0','0','431','167','66','56','宋体','0','10','-2147483640','','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 ('3',V_TEMPID,'2004','Edit4','编辑框4','CS_结束日期','0','2','0','0','0','0','0','0','9','0','1','0','0','505','167','265','56','宋体','0','10','-2147483640','','0','0','0','1'); --T_CTLEDIT------------------------------------------ --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,F_SQL1) VALUES ('3',V_TEMPID,'7000','Grid0','网格0','0','SELECT * FROM ( select kssjb0,ypbm00,ypmc00,ypgg00,kcdw00,to_char(lsdj00) lsdj00,to_char(kcdwlj) kcdwlj,to_char(sum(kcdwsl)) kcdwsl,to_char(sum(ypzsl0)) ypzsl0,to_char(round(sum (kcdwsl*kcdwlj),2)) ypje00,to_char(sum(round(YPZSL0*ZHL000/ddd000,4))) DDDS00 from vw_yk_kssjb0_qy where fyrq00>=:开始日期 and fyrq00<=:结束日期 and (-1=:部门 or (-2=:部门 and MZZYBZ=0) or (-3=:部门 and MZZYBZ=1) or DQBQ00=:部门) group by kssjb0,ypbm00,ypmc00,ypgg00,kcdw00,lsdj00,kcdwlj union all select 99 kssjb0,'''' ypbm00, ''总品种数'' ypmc00 ,(select to_char (count(distinct ypbm00)) from vw_yk_kssjb0_qy where fyrq00>=:开始日期 and fyrq00<=:结束日期 and (-1=:部门 or (-2=:部门 and MZZYBZ=0) or (-3=:部门 and MZZYBZ=1) or DQBQ00=:部门)) ypgg00,'''' kcdw00,null lsdj00, null kcdwlj , null kcdwsl,null ypzsl0,null ypje00,null ddds00 from dual union all select 99 kssjb0,'''' ypbm00, ''总床日数'' ypmc00 ,(select to_char(sum (ZYTS00)) from ZH_CYBRBB where cyrq00 between :开始日期 and :结束日期 and (-1=:部门 or -3=:部门 or CYBQ00=:部门)) ypgg00 ,'''' kcdw00,null lsdj00, null kcdwlj,null kcdwsl,null ypzsl0,null ypje00,null ddds00 from dual union all select 99 kssjb0,'''' ypbm00, ''总出院人数'' ypmc00 ,(select to_char(count(ZYID00)) from ZY_BRXXB0 where SJCYRQ between :开始日期 and :结束日期 and (-1=:部门 or DQBQ00=:部门 or -3=:部门) and BRZT00 not in (''3'',''9'')) ypgg00 ,'''' kcdw00,null lsdj00, null kcdwlj,null kcdwsl,null ypzsl0,null ypje00,null ddds00 from dual union all select 99 kssjb0,'''' ypbm00, ''住院患者抗菌药物使用强度'' ypmc00 ,( SELECT TO_CHAR(ROUND(DDS000*100/ZYTS00,2)) from ( SELECT (select sum(round(YPZSL0*ZHL000/ddd000,4)) DDS000 from vw_yk_kssjb0_qy where fyrq00>=:开始日期 and fyrq00<=:结束日期 and MZZYBZ=''1'' and (-1=:部门 OR DQBQ00=:部门 or -3=:部门)) DDS000, (select greatest(count(*),1) ZYZTS0 from ZY_ZYBRMX a where ZYRQ00 between :开始日期 and :结束日期 and (-1=:部门 OR BQH000=:部门 or -3=:部门)) ZYTS00 FROM DUAL)) ypgg00 ,'''' kcdw00,null lsdj00, null kcdwlj,null kcdwsl,null ypzsl0,null ypje00,null ddds00 from dual ) order by kssjb0,ypzsl0 desc ','','1','1','9','-2147483633','-2147483643','536870911','1','40','222','1998','2627','79','50','宋体','0','11','-2147483640','宋体','0','10','-2147483640','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 ('3',V_TEMPID,'7000','0','KSSJB0','级','0','53','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 ('3',V_TEMPID,'7000','1','YPBM00','药品编码','1','222','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 ('3',V_TEMPID,'7000','2','YPMC00','药品名称','0','497','0','1','1','1','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 ('3',V_TEMPID,'7000','3','YPGG00','规格','0','265','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 ('3',V_TEMPID,'7000','8','DDDS00','DDD(s)','1','225','0','1','1','1','2',''); 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 ('3',V_TEMPID,'7000','5','KCDWLJ','单价','1','161','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 ('3',V_TEMPID,'7000','6','KCDWSL','使用数量','1','177','0','1','1','1','2',''); 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 ('3',V_TEMPID,'7000','7','YPJE00','使用金额','1','246','0','1','1','1','2',''); 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 ('3',V_TEMPID,'7000','4','KCDW00','单位','0','132','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 ('3',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 ('3',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 ('3',V_TEMPID,'7000','2','部门','1','2','select bmbh00,bmmc00 from VW_YK_BQMZQY','',''); --T_SQLPARAMS------------------------------------------ --T_SQLLIST------------------------------------------ commit; end; --End---抗菌药物各部门使用情况表