--Begin-- 门诊医生下午工作量 -- 2011-08-15 08:53:04 declare V_TEMPID T_CTLTEMP.F_TEMPID%TYPE; begin SELECT COUNT(*) INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='4' AND F_NAMECN='门诊医生下午工作量'; IF V_TEMPID =0 THEN SELECT nvl(MAX(F_TEMPID),0)+1 INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='4'; ELSE SELECT F_TEMPID INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='4' AND F_NAMECN='门诊医生下午工作量'; END IF; DELETE FROM T_TEMPTYPE WHERE F_SYSID='4' AND F_TYPEID='00001'; INSERT INTO T_TEMPTYPE (F_SYSID,F_TYPEID,F_TYPENAME,F_MEMO) VALUES ('4','00001','自定义报表',''); -- T_TEMPTYPE ------------------------------------------ DELETE FROM T_CTLTEMP WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLTEMP1 WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLEDIT WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLABEL WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLINE WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLDATE WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCMB WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCURR WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRID WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRIDCOL WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLPARAMS WHERE F_SYSID='4' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLLIST WHERE F_SYSID='4' 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 ('4',V_TEMPID,'00001','门诊医生下午工作量','2010-04-15','陈珑','0','0','','0','0','1','1','','','1799','1500','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 ('4',V_TEMPID,'1000','LABEL','Label0',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'1001','LABEL','Label1',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'1002','LABEL','Label2',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'1003','LABEL','Label3',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'2000','EDIT','Edit0','编辑框0'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'2001','EDIT','Edit1','编辑框1'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'2002','EDIT','Edit2','编辑框2'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'2003','EDIT','Edit3','编辑框3'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',V_TEMPID,'3001','LINE','Line1',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4',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 ('4',V_TEMPID,'2000','Edit0','编辑框0','CS_开始日期','0','2','0','0','0','0','0','0','9','0','1','0','0','270','138','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 ('4',V_TEMPID,'2001','Edit1','编辑框1','CS_截至日期','0','2','0','0','0','0','0','0','9','0','1','0','0','574','138','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 ('4',V_TEMPID,'2002','Edit2','编辑框2','XT_DAY','0','2','0','0','0','0','0','0','9','0','1','0','0','1077','1336','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 ('4',V_TEMPID,'2003','Edit3','编辑框3','XT_YGXM','0','2','0','0','0','0','0','0','9','0','1','0','0','328','1331','265','56','宋体','0','10','-2147483640','','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 ('4',V_TEMPID,'1000','Label0','','门诊医生下午工作量统计','0','2','0','0','1','553','42','545','56','宋体','10','12','-2147483640','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 ('4',V_TEMPID,'1001','Label1','','日期：','0','2','0','0','1','172','138','95','56','宋体','00','10','-2147483640','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 ('4',V_TEMPID,'1002','Label2','','制表人：','0','2','0','0','1','209','1331','122','56','宋体','00','10','-2147483640','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 ('4',V_TEMPID,'1003','Label3','','制表日期：','0','2','0','0','1','921','1336','159','56','宋体','00','10','-2147483640','1'); --T_CTLLABEL------------------------------------------ INSERT INTO T_CTLLINE (F_SYSID,F_TEMPID,F_SEQUENCE,F_NAMEEG,F_FIELD,F_COLOR,F_PRINT,F_LEFT,F_TOP,F_DIRECTION,F_LENGTH,F_THICKNESS,F_VISUAL) VALUES ('4',V_TEMPID,'3001','Line1','','0','1','534','164','1','32','1','1'); --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 ('4',V_TEMPID,'7000','Grid0','网格0','0','select m.zwxm00,m.xkh000,m.bmmc00,m.mzcs,m.ghrc00,m.hjje00,m.bmbh00 from ( select h.zwxm00,h.xkh000,g.mzcs,e.ghrc00,f.hjje00,k.bmmc00,k.bmbh00 from (select a.jzys00,count(*) ghrc00,a.jzks00 from sf_brxxb0 a where a.ghrq00>=:开始日期 and a.ghrq00<=:截至日期 and to_char(to_date(a.GHRQ00,''YYYY.MM.DD'')-1,''D'') in (''1'',''2'',''3'',''5'') and a.GHSJ00>=''14:30:00''and a.GHSJ00<=''17:30:00'' and a.THRQ00 is null and a.GHLB00<>''-99999'' and a.JZYS00>0 group by a.jzys00,a.jzks00) e, (select a.jzys00,sum(b.hjje00) hjje00,a.jzks00 from sf_brxxb0 a,sf_brfy00 b where a.ghrq00>=:开始日期 and a.ghrq00<=:截至日期 and to_char(to_date(a.GHRQ00,''YYYY.MM.DD'')-1,''D'') in (''1'',''2'',''3'',''5'') and a.GHSJ00>=''14:30:00'' and a.GHSJ00<=''17:30:00'' and a.THRQ00 is null and a.GHLB00<>''-99999'' and a.JZYS00>0 and a.ghid00=b.mzid00 group by a.jzys00,a.jzks00) f, (select a.jzys00,count(distinct a.ghrq00) mzcs,a.jzks00 from sf_brxxb0 a where a.ghrq00>=:开始日期 and a.ghrq00<=:截至日期 and to_char(to_date(a.GHRQ00,''YYYY.MM.DD'')-1,''D'') in (''1'',''2'',''3'',''5'') and a.GHSJ00>=''14:30:00'' and a.GHSJ00<=''17:30:00'' and a.THRQ00 is null and a.GHLB00<>''-99999'' and a.JZYS00>0 group by a.jzys00,a.jzks00) g,BM_YGBM00 h,bm_bmbm00 k where e.jzys00(+)=h.ygbh00 and f.jzys00(+)=h.ygbh00 and g.jzys00(+)=h.ygbh00 and k.bmbh00=e.jzks00 and k.bmbh00=f.jzks00 and k.bmbh00=g.jzks00 )m where (mzcs is not null or ghrc00 is not null or hjje00 is not null) and m.bmbh00 in (select bmbh00 from bm_bmbm00 where yyid00 =:分院) order by m.bmmc00,m.bmbh00,m.zwxm00,m.xkh000','','1','1','6','16777215','-2147483643','536870911','1','164','204','1323','1111','80','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 ('4',V_TEMPID,'7000','0','ZWXM00','姓名','0','201','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 ('4',V_TEMPID,'7000','1','XKH000','胸卡号','0','201','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 ('4',V_TEMPID,'7000','5','HJJE00','金额','0','201','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 ('4',V_TEMPID,'7000','3','MZCS','出诊次数','0','201','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 ('4',V_TEMPID,'7000','4','GHRC00','挂号次数','0','201','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 ('4',V_TEMPID,'7000','2','BMMC00','科室','0','307','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 ('4',V_TEMPID,'7000','0','开始日期','2','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 ('4',V_TEMPID,'7000','1','截至日期','2','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 ('4',V_TEMPID,'7000','2','分院','0','3','A|B|C|D||院本部|二化分院|康复分院|体检中心','',''); --T_SQLPARAMS------------------------------------------ --T_SQLLIST------------------------------------------ commit; end; --End---门诊医生下午工作量