--Begin-- 病区各科室物品领用统计 -- 2013-06-27 11:30:43 declare V_TEMPID T_CTLTEMP.F_TEMPID%TYPE; begin SELECT COUNT(*) INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='F' AND F_NAMECN='病区各科室物品领用统计'; IF V_TEMPID =0 THEN SELECT nvl(MAX(F_TEMPID),0)+1 INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='F'; ELSE SELECT F_TEMPID INTO V_TEMPID FROM T_CTLTEMP WHERE F_SYSID='F' AND F_NAMECN='病区各科室物品领用统计'; END IF; DELETE FROM T_TEMPTYPE WHERE F_SYSID='F' AND F_TYPEID='0001'; INSERT INTO T_TEMPTYPE (F_SYSID,F_TYPEID,F_TYPENAME,F_MEMO) VALUES ('F','0001','自定义报表',''); -- T_TEMPTYPE ------------------------------------------ DELETE FROM T_CTLTEMP WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLTEMP1 WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLEDIT WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLABEL WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLLINE WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLDATE WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCMB WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLCURR WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRID WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_CTLGRIDCOL WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLPARAMS WHERE F_SYSID='F' AND F_TEMPID=V_TEMPID; DELETE FROM T_SQLLIST WHERE F_SYSID='F' 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_MENUSHOW,F_ORIENTATION,F_MODELCOUNT) VALUES ('F',V_TEMPID,'0001','病区各科室物品领用统计','2012-11-7','9999','0','0','','0','0','1','1','','A4','2101','2971','16777215','0','0','0','0.1','1','0',''); --T_CTLTEMP------------------------------------------ INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'1000','LABEL','Label0',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'1001','LABEL','label1001',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'1002','LABEL','label1002',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'1003','LABEL','Label3',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'2000','EDIT','Edit0','编辑框0'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'2001','EDIT','Edit1','编辑1'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',V_TEMPID,'2002','EDIT','Edit2','编辑框2'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('F',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 ('F',V_TEMPID,'2000','Edit0','编辑框0','CS_开始日期','0','2','0','0','0','0','0','0','9','0','1','0','0','429','209','508','90','楷体_GB2312','1','20','-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 ('F',V_TEMPID,'2001','Edit1','编辑1','CS_结束日期','0','2','0','0','0','0','0','0','9','0','1','0','0','1032','209','540','90','楷体_GB2312','1','20','-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 ('F',V_TEMPID,'2002','Edit2','编辑框2','XT_YGXM','0','2','0','0','0','0','0','0','9','0','1','0','0','323','2797','519','87','楷体_GB2312','1','20','-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 ('F',V_TEMPID,'1000','Label0','','物品领用统计','2','2','0','0','1','249','61','1442','116','楷体_GB2312','10','26','-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 ('F',V_TEMPID,'1001','label1001','','统计日期：','2','2','0','0','1','48','196','413','116','楷体_GB2312','10','22','-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 ('F',V_TEMPID,'1002','label1002','','--','2','2','0','0','1','942','196','90','116','楷体_GB2312','10','22','-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 ('F',V_TEMPID,'1003','Label3','','统计人：','0','2','0','0','1','53','2794','265','95','楷体_GB2312','10','20','-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,F_SQL1) VALUES ('F',V_TEMPID,'7000','Grid0','网格0','0','SELECT qlksmc, SUM (sfsl00) sfsl00, SUM (bzzje0) bzzje0 FROM (SELECT a.qlksmc, DECODE (a.zt0000, 3, a.sfsl00, 5, -a.sfsl00, a.sfsl00 ) sfsl00, a.wpmc00, a.zt0000, a.wpbzdj, a.bzzje0 FROM (SELECT a.ffrbh0, a.ffczy0, a.qldh00, a.qlrq00, qlsj00, a.qlksmc, SUM (b.sfsl00) sfsl00, wpmc00, wpgg00, wpdw00, wpxzmc, a.ztmc00, a.qlrmc0, a.ffrmc0, a.zt0000, a.ffrq00, a.ffsj00, b.wpbh00, b.wpbzdj, b.bzzje0 FROM vw_gy_kswply a, vw_gy_wplymx b WHERE a.qldh00 = b.qldh00 AND a.ffrq00 >= :开始日期 AND a.ffrq00 <= :结束日期 AND a.ffksbh = ''259'' AND a.zt0000 IN (3, 5) GROUP BY a.ffrbh0, a.ffczy0, a.qldh00, a.qlrq00, a.qlsj00, a.qlksmc, wpmc00, wpgg00, wpdw00, wpxzmc, a.ztmc00, a.qlrmc0, a.ffrmc0, a.zt0000, a.ffrq00, a.ffsj00, b.wpbh00, b.wpbzdj, b.bzzje0) a, gy_wpbm00 b WHERE a.wpbh00 = b.wpbh00 ORDER BY a.qldh00) GROUP BY qlksmc ORDER BY qlksmc ','','1','1','3','-16777201','13428940','536870911','1','101','339','1712','2426','80','70','楷体_GB2312','1','16','-16777208','楷体_GB2312','0','14','-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_COLDATANAME,F_COLPAGETYPE,F_COLDATATYPE) VALUES ('F',V_TEMPID,'7000','0','QLKSMC','部门名称','2','479','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_COLDATANAME,F_COLPAGETYPE,F_COLDATATYPE) VALUES ('F',V_TEMPID,'7000','2','BZZJE0','总金额','2','418','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_COLDATANAME,F_COLPAGETYPE,F_COLDATATYPE) VALUES ('F',V_TEMPID,'7000','1','SFSL00','发放数量','2','466','0','1','1','合计|合计','1','2'); --T_CTLGRIDCOL------------------------------------------ INSERT INTO T_SQLPARAMS (F_SYSID,F_TEMPID,F_SEQUENCE,F_PARAMID,F_PARAMNAME,F_VALUETYPE,F_EDITTYPE,F_COMBOSTR,F_DEFAUL1,F_DEFAULT) VALUES ('F',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_DEFAUL1,F_DEFAULT) VALUES ('F',V_TEMPID,'7000','1','结束日期','0','4','','',''); --T_SQLPARAMS------------------------------------------ --T_SQLLIST------------------------------------------ commit; end; --End---病区各科室物品领用统计