首页 > 代码库 > 利用VBA+OO4O构造CTAIS开放式通用平台

利用VBA+OO4O构造CTAIS开放式通用平台

利用VBA+OO4O构造CTAIS开放式通用平台

2010-06-08 14:59:28 | 来源:税务信息化论文集 | 作者:于非 易飞

  摘  要:文立足于CTAIS系统体系,探讨如何通过OO4O技术和VBA技术搭建ORACLE前端开放式通用平台,同时以通过最低编程技术满足基层信息化建设的要求,从而实现CTAIS功能的扩展。本文提供的通用平台建设通过笔者精简的满足OO4O连接必需的ORACLE免安装客户端,通过EXCEL的VBA接口,采取用户编写的各类查询语句的方式查询综合征管软件分发服务器数据,将复杂的编程技术降低到简单的EXCEL利用来实现基层信息中心对分发环境数据的充分利用,以满足各基层对个性化开发的需求。
  关键词:OO4O;ORACLE;VBA;CTAIS
  一、引  言
  按照总局提出的征管模式和完善征管体制的意见,从分类构建征管工作格局的实际出发,探索建立以计算机网络为依托、省局数据集中的、科学合理的税收征管机制,构建严密的税源管理和监控体系,总局成功上线运行的综合征管软件V2.0大幅提升了国税系统税收征管的精细化、规范化和科学化程度。但是如何强化对税收征管数据的分析利用,力求数据有用、管用、好用,进一步提高征管质量效率以及各级国税机关领导决策能力和组织收入能力成为国税系统信息化建设面临的新课题。
  二、需求分析
  由于征管数据采取的是分散采集、省级集中的模式,各地市、州局仅放置存放镜像数据的分发服务器用于查询和数据分析再利用,针对国税信息化建设还处于开发水平不高的初级阶段的现状,大多数国税机关对分发服务器的二次开发力度不够,部分地市(州)国税机关针对分发服务器的开发大多是利用TOMCAT+JSP进行查询,一旦涉及数据分析的开发,则会大幅提高开发成本和人力资源,笔者在前期开发的几个TOMCAT+JSP系统在实际应用中都面临这样的问题。因此降低开发技术难度,用较小的开发实现较大的数据再利用就必须另辟它径。
  笔者通过对基层信息化建设水平的调研,发现利用EXCEL的应用普及性可以为数据再利用提供良好的平台,一旦将数据延伸至EXCEL,则CTAIS数据的再利用可以延伸到征管一线,成为征收管理数据分析利用、有用、管用、好用的最佳途径。因此本文就如何通过OO4O技术和VBA技术搭建ORACLE前端开放式通用平台做了一定程度的探讨,以图抛砖引玉,实现税收数据最大程度服务于征管的目标。
  三、系统的整体结构
  ORACLE前端开放式通用平台采用A/S结构,所谓A/S结构,是为了减少客户端开发,利用已经存在的应用系统(Application)连接ORACLE数据分发服务器(SERVER),通过必要的接口连接(OO4O),将后台服务器(SERVER)数据导入到前端应用形成二次数据源,再利用前端应用的可设计性和易操作性进行开放式的设计,以完成个性定制和数据分析,完成对征管质量效率的提高和基层国税机关领导的决策。
  系统结构如下:

 

  从该系统的结构可以看出,该系统本身的开发力度并不大,但是有效的延伸了ORACLE数据的位置,同时利用EXCLE的强大数据分析功能,扩展了CTAIS的使用,使CTAIS变的更好用、更易用。
  四、ORACLE的OO4O技术
  OO4O(Oracle Objects for OLE)为 COM组件,但并不属于ORACLE体系的产品,一旦安装后,该组件可以跨越ORACLE版本运行,这也是本文选择OO4O技术的原因。OO4O对系统要求的要求如下:
  (1)OO4O可以运行在: Windows NT 4.0, Windows 2000, Windows 98,或者 Windows XP
  (2)32-bit application capable of OLE脚本具有32位应用系统的应用能力,(例如:Visual Basic, Excel等) 也支持C++编译(Microsoft Visual C++ 6.x)。
  (3)通过本地连接或者网络客户端访问Oracle8, Oracle8i or Oracle9i 数据库。ORACLE技术文本称需安装ORACLE客户端,但经笔者调试,已制作出免安装客户端系统,大大提高了系统的应用普及性。
  (4)查阅Required Support Files (RSF) 9.2.0 for OO4O.Oracle安装器应确保RSFs作为OO4O的一部分进行安装。
  (5)必须注册高版本的mfc42.dll (v. 6.00.x)到oip9.dll.注册应当自动完成。
  (6)如果要使用OO4O代码向导做存储过程,需要安装Microsoft Visual Basic 6.0. 如果VB 6没有被先于OO4O安装,可以简单的运行以下命令行注册相应控件和动态链接库:
  - regsvr32.exe oo4ocodewiz.dll
  - regsvr32.exe odbtreeview.ocx
  - regsvr32.exe oo4oaddin.dll
  由上可知,OO4O对系统要求不高,具有较强的普及性。
  五、ORACLE免安装客户端的制作
  搭建A/S结构的ORACLE前端开放式通用平台,需要安装ORACLE客户端,由于ORACLE原始安装文件较大,安装时间较长,不利于前端开放式通用平台的普及应用,因此对ORACLE的客户端进行改装就变的尤其重要,笔者经过反复调试,将ORACLE客户端精简成24M左右的免安装包,可直接拷贝到C盘,经过必要的DLL和OCX注册,即装即用。
  精简步骤如下:
  1、 提取文件:
  经过反复调试,ORACLECLIENT可以精简为24M,
  2、 经过精简后只有58个文件如下:
  Bin 目录下55个文件:

oran9.dll

orannmp9.dll

oranl9.dll

oranro9.dll

orannts9.dll

Oranhost9.dll

oranoname9.dll

orantns9.dll

oransgr9.dll

orancrypt9.dll

oranbeq9.dll

oranipc9.dll

oranjni9.dll

ORANLS9.DLL

ORAUNLS9.DLL

ORASNLS9.DLL

ORAXML9.DLL

ORAXMLG9.DLL

ORAXSD9.DLL

oranmt.dll

oranmi.dll

oranml.dll

oravpxdba.dll

oranms0.dll

tcl82.dll

oravpnt.dll

oravppdc.dll

oranmd.dll

OsUtils.dll

oranoncj9.dll

owm2.dll

oip9.dll

oraansi.dll

oraclm32.dll

oradc.ocx

ODBTREEVIEW.OCX

OO4OADDIN.DLL

OO4OCODEWIZ.DLL

OO4OCODEWIZ.EXE

oraordim9.dll

orawwg9.dll

oravpsqlsrv.dll

oractxx9.dll

oratracepls9.dll

oravpxoafnd.dll

orajox9.dll

oraioser.dll

ORAPLC9.DLL

oraodm9.dll

oraolapop9.dll

oraevrus8.dll

oraobjop9.dll

oraqsmashr.dll

oraspawn.dll

oip9.tlb

  NETWORK下一个文件:
  network\admin\tnsnames.ora
  这个文件安装好之后要根据实际的工作环境进行重新配置下面是这个文件的例子:

 

  0040下一个文件:
  oo4o\mesg\OIPUS.MSB
  ORACORE下一个文件
  oracore\ zoneinfo\timezone.dat
  步骤2:修改注册表
  按照BIN\目录下面的oracle.key文件中的注册表目录,建立注册表项,然后将建立好的REG文件导入到注册表中,以下给出范例:
  Windows Registry Editor Version 5.00
  [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
  "ORACLE_HOME"="C:\\oracle\\ora92"
  "ORACLE_HOME_NAME"="OraHome92"
  "NLS_LANG"="american_america.us7ascii"
  "inst_loc"="C:\\Program Files\\Oracle\\Inventory"
  "API"="C:\\oracle\\ora92\\dbs"
  "ORACLE_GROUP_NAME"="Oracle - OraHome92"
  "OLEDB"="C:\\oracle\\ora92\\oledb\\mesg"
  "VOBHOME2.0"="C:\\oracle\\ora92"
  "OO4O"="C:\\oracle\\ora92\\oo4o\\mesg"
  [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES]
  "HOME_COUNTER"="1"
  "DEFAULT_HOME"="OraHome92"
  "LAST_HOME"="0"
  [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0]
  "NAME"="OraHome92"
  "PATH"="C:\\oracle\\ora92"
  "NLS_LANG"="american_america.us7ascii"
  3.将做好的安装包拷贝到C盘根目录即可使用。
  以上免安装包除可应用在本文前端开放式通用平台中,也可供CTAIS报表系统的使用。六、VBA接口的制作
  EXCEL利用OO4O和VBA技术可以实现对ORACLE的完全访问,所使用的开发基于以下几个部分进行演示:
  数据表(DataSheets):即利用EXCEL的工作表存放查询后的结果。
  本演示数据表如下图:

 

  获取数据接口(GetData):返回查询结果数据并将数据写如数据表
  Sub Get_Data()
  ‘ Create and initialize the necessary objects
  Dim OraSession As Object
  Dim OraDatabase As Object
  Dim EmpDynaset As Object
  Dim ColNames As Object
  Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  Set OraDatabase = OraSession.OpenDatabase("2:", "scott/tiger", 0&)
  Set EmpDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)
  ‘ Using field array, ie. ColNames("ename")。value, is significantly faster than using
  ‘   field lookup, ie. EmpDynaset.fields("ename")。value
  Set ColNames = EmpDynaset.Fields
  ‘ Place column headings on sheet
  For icols = 1 To ColNames.Count
  Worksheets("DataSheet")。Cells(1, icols)。Value = http://www.mamicode.com/ColNames(icols - 1)。Name Next
  ‘ Place data on sheet using CopyToClipboard
  EmpDynaset.CopyToClipboard -1
  Sheets("DataSheet")。Select
  Range("A2")。Select
  ActiveSheet.Paste
  End Sub
  更新数据(UpdateData):将数据表中编辑的数据更新到ORACLE数据库中
  Sub Update_Data()
  ‘ Create and initialize the necessary objects
  Dim OraSession As Object
  Dim OraDatabase As Object
  Dim EmpDynaset As Object
  Dim ColNames As Object
  Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  Set OraDatabase = OraSession.OpenDatabase("2:", "scott/tiger", 0&)
  Set EmpDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)
  Set ColNames = EmpDynaset.Fields
  i = 2
  ‘ the BeginTrans and CommitTrans are optional
  OraSession.BeginTrans
  While Worksheets("DataSheet")。Cells(i, 1)。Value <> ""
  EmpDynaset.dbedit
  For j = 1 To ColNames.Count
  ColNames(j - 1)。Value = http://www.mamicode.com/Worksheets("DataSheet")。Cells(i, j)。Value
  Next j
  EmpDynaset.dbupdate
  EmpDynaset.dbmovenext
  i = i + 1
  Wend
  OraSession.CommitTrans
  End Sub
  获取数组(GetArray):
  将服务器端返回的大量数据通过一个事务(transaction)返回并显示。
  主要按照以下步骤完成:
  1. 定义并设置对象。
  2. 在服务器上传送PL/SQL过程。((过程并非建自客户端,而是在服务器端创建。)
  3. 向服务端传递参数
  4. 获取输入的参数。
  5. 调用过程以一个事务传递整个数组。
  6. 显示返回的数组。
  Sub Get_Array()
  Const ORAPARM_INPUT = 1
  Const ORAPARM_OUTPUT = 2
  Const ORATYPE_VARCHAR2 = 1
  Const ORATYPE_NUMBER = 2
  Dim OraSession As Object
  Dim OraDatabase As Object
  Dim OraDynaset As Object
  Dim OraPLSQLStmt As Object
  Dim OraPArray1 As Object
  Dim OraPArray2 As Object
  Dim PLSQLStmt1 As String
  Dim PLSQLStmt2 As String
  Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  Set OraDatabase = OraSession.OpenDatabase("2:", "scott/tiger", 0&)
  ‘ PL/SQL procedure
  PLSQLStmt1 = "CREATE OR REPLACE PACKAGE Employee AS " & _
  "TYPE numarray IS TABLE OF NUMBER INDEX by BINARY_INTEGER; " & _
  "TYPE vchar2array IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; " & _
  "PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, inEmpnos IN numarray, outEmpNames OUT vchar2array); " & _
  "PROCEDURE GetEmpName (inEmpno IN NUMBER, outEmpName OUT VARCHAR2); " & _
  "FUNCTION GetEmpSal (inEmpno IN NUMBER) RETURN NUMBER; " & _
  "END Employee;"
  PLSQLStmt2 = "CREATE OR REPLACE PACKAGE BODY Employee AS " & _
  "PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY) IS " & _
  "BEGIN FOR i in 1……ArraySize loop SELECT ename INTO outEmpNames(i) FROM emp WHERE empno = inEmpNos(i); " & _
  "END LOOP; END; " & _
  "PROCEDURE GetEmpName (inEmpno IN NUMBER, outEmpName OUT VARCHAR2) IS BEGIN SELECT ename INTO outEmpName " & _
  "FROM EMP WHERE EMPNO = inEmpNo; END; FUNCTION GetEmpSal (inEmpno IN NUMBER) RETURN NUMBER IS " & _
  "outEmpsal NUMBER(7,2); BEGIN SELECT sal INTO outEmpsal FROM emp WHERE empno = inEmpno;  RETURN (outEmpsal); " & _
  "END; END Employee;"
  ‘ add the above procedure to the Server
  OraDatabase.dbexecutesql (PLSQLStmt1)
  OraDatabase.dbexecutesql (PLSQLStmt2)
  ‘ add the 2 parameters to the Server
  OraDatabase.Parameters.addtable "empno", ORAPARM_INPUT, ORATYPE_NUMBER, 3, 22
  OraDatabase.Parameters.addtable "empname", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, 3, 10
  Set OraPArray1 = OraDatabase.Parameters("empno")
  Set OraPArray2 = OraDatabase.Parameters("empname")
  ‘ assign empno‘s to the array
  OraPArray1.put_Value 7698, 0
  OraPArray1.put_Value 7782, 1
  OraPArray1.put_Value 7654, 2
  ‘ execute the procedure and retrieve the empname array
  Set OraPLSQLStmt = OraDatabase.createsql("Begin Employee.GetEmpNamesInArray(3, :EMPNO, :EmpNAME); End;", &O0)
  ‘ display to screen
  For j = 0 To 2
  parm_value = http://www.mamicode.com/OraPArray2.Get_Value(j)
  msgbox parm_value
  Next j
  End Sub
  CursorReturn:演示如何返回PL/SQL的游标(Cursors)。  该特性提供只读访问游标(cursors)。  但是要求已经装载PL/SQL的存储过程,如\oo4o2\oraexamp.sql
  1. 定义并设置对象
  2. 从EXCEL数据表(DataSheet)J2单元格获取范例数据表"dept" .
  3. 创建dept参数。
  4. 通过存储过程的事件创建记录集
  5. 拷贝记录集到数据表页(DataSheet)
  Sub Cursor_Return()
  Const ORATYPE_NUMBER = 2
  Const ORAPARM_INPUT = 1
  Dim OraSession As Object
  Dim OraDatabase As Object
  Dim OraDynaset As Object
  ‘Get deptno parameter from cell J2.  If blank then use 10 as default
  If Val(Worksheets("DataSheet")。Cells(2, 10)。Value) = 0 Then
  dept = 10
  Else
  dept = Worksheets("DataSheet")。Cells(2, 10)。Value
  End If
  ‘Create the OraSession and connection objects
  Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
  ‘Create the Deptno parameter
  OraDatabase.Parameters.Add "DEPTNO", dept, ORAPARM_INPUT
  OraDatabase.Parameters("DEPTNO")。ServerType = ORATYPE_NUMBER
  ‘Create OraDynaset based on "EmpCursor" created in stored procedure.
  Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)
  ‘Copy cursor to clipboard
  OraDynaset.CopyToClipboard -1
  ‘Erase range and paste clipboard
  Sheets("DataSheet")。Select
  Range("A2:H50")。Select
  Selection.Clear
  Range("A2")。Select
  ActiveSheet.Paste
  ‘NOTE:  To vary deptno values you do not need to run this entire procedure.  Simply assign
  ‘a new value to the parameter: OraDatabase.Parameters("DEPTNO")。Value = http://www.mamicode.com/20 and issue
  ‘  a OraDynaset.Refresh command
  End Sub
  七、开放式通用平台应用案例
  为了更直观的演示OO4O技术和VBA技术如何将CTAIS分发服务器数据库内容延伸到EXCEL中,下面将引出一个基层管理员的实际应用范例——增值税一般纳税人征管台账。
  1. 首先将制作好的免安装客户端拷贝到C盘根目录。
  2. 修改C:\ORACLE\network\admin\tnsnames.ora 的参数(对事先设置好统一发放的TNSNAMES可以忽略此步骤)
  3. 运行批处理进行DLL和OCX的注册
  4. 将设计好的PL/SQL语句填入开放平台预先设计好的模式化接口程序中代码引入部分,如下代码斜体部分:
  Sub ctaisquery()
  Dim myCon, mySql As String
  ‘Worksheets.Add
  myCon = "oledb;provider=oraoledb.oracle.1;persist securith info=false;user id=ctais2;password=oracle;data source=sjff"
  mySql = "select distinct * from dj_nsrxx a,SB_ZZS_2003_YBNSR b,dj_nsrxx_kz c,dm_swjg d,dm_czry e,dm_nsrzt f,DM_DJZCLX g,dm_hy h where  a.nsrdzdah=b.nsrdzdah and a.hy_dm=h.hy_dm and a.djzclx_dm=g.djzclx_dm and a.nsrzt_dm=f.nsrzt_dm and a.zgswry_dm=e.czry_dm and a.nsr_swjg_dm=d.swjg_dm and a.nsrdzdah=c.nsrdzdah and b.sssq_q>=to_date(‘" & Trim(Sheets("基本信息")。Cells(3, 2)) & "-" & Trim(Sheets("基本信息")。Cells(3, 4)) & "-" & Trim(Sheets("基本信息")。Cells(3, 6)) & "‘,‘yyyy-mm-dd‘) and b.sssq_q<=to_date(‘" & Trim(Sheets("基本信息")。Cells(3, 9)) & "-" & Trim(Sheets("基本信息")。Cells(3, 11)) & "-" & Trim(Sheets("基本信息")。Cells(3, 13)) & "‘,‘yyyy-mm-dd‘) and sbbl=‘1‘ and  nsrsbh=‘" & Trim(Sheets("基本信息")。Cells(4, 2)) & "‘"
  Sheets("data1")。Select
  Sheets("data1")。Range("A:IV")。Value = http://www.mamicode.com/""
  With ActiveSheet.QueryTables.Add(Connection:=myCon, Destination:=Range("A2"))
  。CommandText = mySql
  。Refresh
  End With
  Sheets("纳税人信息")。Select
  End Sub
  步骤5给出PL/SQL需要引入的参数页,本例需要填入台账所属年度和纳税人识别号,如下图:

 

  步骤6.设计查询触发,利用EXCEL的VBA宏运行代码,将查询结果存放在EXCEL的结果表页面,如下图:

 

    步骤7.将结果页数据按照要求,利用EXCEL的自身功能进行数据过滤,将有用信息按照需求个性定制到数据查询页,同时可对结果数据进行分析,下图分别演示了纳税人信息、税务登记表以及一般纳税人征管台账。

 

 

  通过以上设计步骤就完成了动态查询一般纳税人登记信息和征管台账的全部信息,该设计开发度极低,却可以完全代替TOMCAT+JSP的全部功能,同时相对ORACLE的查询工具相比,更增加了EXCEL本身具备的丰富的数据操作功能。
  八、结束语
  ORACLE前端开放式通用平台的特点并不在于开发的技术性,而是通过一种途径开放了ORACLE的数据使用,将CTAIS后台数据延伸到EXCEL中供基层征管应用,降低开发的难度即扩大了CTAIS数据应用的范围,真正实现了CTAIS数据有用、管用、好用,从而进一步为征管质量效率以及各级国税机关领导决策能力和组织收入能力服务。
  参考文献:
  [1] 四川省国家税务局综合征管软件V2V. 0推广运行维护实施方案
  [2] Oracle Objects for OLE (OO4O) Release Notes
  [3] VBA设计参考手册
  (作者简介:于非 泸州市国家税务局信息中心主任
              易飞 泸州市国家税务局信息中心)