--Begin-- 单位体检核对报表 -- 2011-08-02 09:40:52 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=13; DELETE FROM T_CTLTEMP1 WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLEDIT WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLLABEL WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLLINE WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLDATE WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLCMB WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLCURR WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLGRID WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_CTLGRIDCOL WHERE F_SYSID='4' AND F_TEMPID=13; DELETE FROM T_SQLPARAMS WHERE F_SYSID='4' AND F_TEMPID=13; -- 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','13','00001','单位体检核对报表','2011-8-1 2','网络中心','0','0','','0','0','1','1','','A4','2500','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 ('4','13','1000','LABEL','Label0',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','1001','LABEL','Label1',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','1002','LABEL','Label2',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','1003','LABEL','Label3',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','1004','LABEL','Label4',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','2000','EDIT','Edit0','编辑框0'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','2001','EDIT','Edit1','编辑框1'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','2002','EDIT','Edit2','编辑框2'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','2003','EDIT','Edit3','编辑框3'); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','3000','LINE','Line0',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','3001','LINE','Line1',''); INSERT INTO T_CTLTEMP1 (F_SYSID,F_TEMPID,F_SEQUENCE,F_CONTROLTYPE,F_NAMEEG,F_NAMECN) VALUES ('4','13','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','13','2000','Edit0','编辑框0','CS_开始日期','0','2','0','0','0','0','0','0','9','0','1','0','0','365','138','291','56','宋体','0','11','-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','13','2001','Edit1','编辑框1','CS_结束日期','0','2','0','0','0','0','0','0','9','0','1','0','0','699','138','352','56','宋体','0','11','-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','13','2002','Edit2','编辑框2','XT_YGXM','0','2','0','0','0','0','0','0','9','0','1','0','0','347','2778','339','56','宋体','0','11','-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','13','2003','Edit3','编辑框3','XT_NOW','0','2','0','0','0','0','0','0','9','0','1','0','0','1447','2773','527','56','宋体','0','11','-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','13','1000','Label0','','单位体检核对报表','0','2','0','0','1','855','37','651','82','楷体_GB2312','00','22','-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','13','1001','Label1','','开始日期:','0','2','0','0','1','191','138','172','56','宋体','00','11','-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','13','1002','Label2','','至','0','2','0','0','1','656','138','45','56','宋体','00','11','-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','13','1003','Label3','','制表人:','0','2','0','0','1','196','2778','148','56','宋体','00','11','-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','13','1004','Label4','','打印日期:','0','2','0','0','1','1251','2775','193','56','宋体','00','11','-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','13','3000','Line0','','0','1','191','193','1','1770','1','1'); 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','13','3001','Line1','','0','1','196','2736','1','1773','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','13','7000','Grid0','网格0','0','SELECT i.dwmc00, NVL (i.hjje00, 0) 上期结存, NVL (j.hjje00, 0) 本期增加, NVL (k.hjje00, 0) 本期减少, NVL (l.hjje00, 0) 本期打折, NVL (z.hjje00, 0) 本期结存 FROM (SELECT b.dwid00, b.dwmc00, a.hjje00 FROM (SELECT tydwid, SUM (hjje00) hjje00 FROM (SELECT b.tydwid, SUM (a.hjje00) hjje00 FROM sf_brfy00 a, bm_brxxb0 b WHERE a.brid00 = b.brid00 AND ((a.jzdh00 = 0) OR ((NOT EXISTS ( SELECT 1 FROM sf_jzb000 b WHERE a.jzdh00 = b.jzdh00)))) AND a.czrq00 >= ''20080701'' AND a.czrq00 || a.czsj00 >= ''20080701'' || ''00:00:00'' AND a.czrq00 < :开始日期 AND a.czrq00 || a.czsj00 < (:开始日期) || ''00:00:00'' AND a.brfb00 = 99 GROUP BY b.tydwid, xm0000 UNION ALL SELECT c.tydwid, SUM (d.hjje00) hjje00 FROM sf_brfy00 a, sf_jzb000 b, bm_brxxb0 c,sf_fymx00 d WHERE a.djh000=d.djh000 and a.brid00 = c.brid00 AND a.brid00 = b.brid00 AND a.jzdh00 = b.jzdh00 AND b.fbbh00 = 99 AND b.jzrq00 >= (:开始日期) AND d.czrq00 >= ''20080701'' AND d.czrq00 < (:开始日期) GROUP BY c.tydwid UNION ALL SELECT c.tydwid, SUM (-b.hjje00) hjje00 FROM sf_jzb000 b, bm_brxxb0 c WHERE b.fbbh00 = 99 AND b.brid00 = c.brid00 AND b.jzrq00 = ''20110610'' AND b.brid00 = 1138887 and substr(:开始日期,5,2) = ''07'' GROUP BY c.tydwid) GROUP BY tydwid) a, bm_tydwb0 b WHERE a.tydwid(+) = b.dwid00) i, (SELECT d.dwid00, d.dwmc00, c.hjje00 FROM (SELECT c.tydwid, SUM (b.hjje00) hjje00 FROM sf_brfy00 a, sf_fymx00 b, bm_brxxb0 c WHERE a.brid00 = c.brid00 AND a.djh000 = b.djh000 AND a.brfb00 = 99 AND a.czrq00 >= :开始日期 AND a.czrq00 <= :结束日期 AND b.xmbh00 <> 2917 GROUP BY c.tydwid) c, bm_tydwb0 d WHERE c.tydwid(+) = d.dwid00) j, (SELECT f.dwid00, f.dwmc00, e.hjje00 FROM (SELECT b.dwmc00, SUM (c.hjje00) hjje00 FROM sf_jzb000 c, bm_brxxb0 a, bm_tydwb0 b WHERE a.brid00 = c.brid00 AND a.tydwid = b.dwid00 AND a.fbbh00 = 99 AND c.jzrq00 >= :开始日期 AND c.jzrq00 <= :结束日期 GROUP BY b.dwmc00) e, bm_tydwb0 f WHERE e.dwmc00(+) = f.dwmc00) k, (SELECT h.dwid00, h.dwmc00, g.hjje00 FROM (SELECT c.tydwid, SUM (b.hjje00) hjje00 FROM sf_brfy00 a, sf_fymx00 b, bm_brxxb0 c WHERE a.brid00 = c.brid00 AND a.djh000 = b.djh000 AND a.brfb00 = 99 AND a.czrq00 >= :开始日期 AND a.czrq00 <= :结束日期 AND b.xmbh00 + 0 = 2917 GROUP BY c.tydwid) g, bm_tydwb0 h WHERE g.tydwid(+) = h.dwid00) l, (SELECT y.dwid00, y.dwmc00, w.hjje00 FROM (SELECT tydwid, SUM (hjje00) hjje00 FROM (SELECT v.tydwid, SUM (a.hjje00) hjje00 FROM sf_brfy00 a, bm_brxxb0 v WHERE a.brid00 = v.brid00 AND ( (a.jzdh00 = 0) OR ((NOT EXISTS ( SELECT 1 FROM sf_jzb000 b WHERE a.jzdh00 = b.jzdh00) ) ) ) AND a.czrq00 >= ''20080701'' AND a.czrq00 || a.czsj00 >= ''20080701'' || ''00:00:00'' AND a.czrq00 <= :结束日期 AND a.czrq00 || a.czsj00 <= (:结束日期) || ''23:59:59'' AND a.brfb00 = 99 GROUP BY v.tydwid UNION ALL SELECT c.tydwid, SUM (d.hjje00) hjje00 FROM sf_brfy00 a, sf_jzb000 b, bm_brxxb0 c ,sf_fymx00 d WHERE a.djh000 = d.djh000 and a.brid00 = c.brid00 AND a.brid00 = b.brid00 AND a.jzdh00 = b.jzdh00 AND b.fbbh00 = 99 AND b.jzrq00 > (:结束日期) AND d.czrq00 >= ''20080701'' AND d.czrq00 <= (:结束日期) GROUP BY c.tydwid UNION ALL SELECT c.tydwid, SUM (-b.hjje00) hjje00 FROM sf_jzb000 b, bm_brxxb0 c WHERE 1 = 1 AND b.fbbh00 = 99 AND b.brid00 = c.brid00 AND b.jzrq00 = ''20110610'' AND b.brid00 = 1138887 and substr(:开始日期,5,2) = ''06'' GROUP BY c.tydwid) GROUP BY tydwid) w, bm_tydwb0 y WHERE w.tydwid(+) = y.dwid00) z WHERE i.dwid00 = j.dwid00 AND i.dwid00 = k.dwid00 AND i.dwid00 = l.dwid00 AND i.dwid00 = z.dwid00 AND j.dwid00 = k.dwid00 AND j.dwid00 = l.dwid00 AND j.dwid00 = z.dwid00 AND k.dwid00 = l.dwid00 AND k.dwid00 = z.dwid00 AND l.dwid00 = z.dwid00 AND (i.hjje00 <> 0 OR j.hjje00 <> 0 OR k.hjje00 <> 0 OR l.hjje00 <> 0) ORDER BY i.dwmc00','','1','1','6','-2147483633','-2147483643','536870911','1','193','198','1773','2527','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','13','7000','0','DWMC00','单位名称','0','400','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','13','7000','1','上期结存','上期结存','0','265','0','1','1','2','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 ('4','13','7000','5','本期结存','本期结存','0','299','0','1','1','2','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 ('4','13','7000','3','本期减少','本期减少','0','265','0','1','1','2','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 ('4','13','7000','4','本期打折','本期打折','0','265','0','1','1','2','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 ('4','13','7000','2','本期增加','本期增加','0','265','0','1','1','2','2',''); --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','13','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 ('4','13','7000','1','结束日期','0','4','','',''); --T_SQLPARAMS------------------------------------------ --End---单位体检核对报表