利用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
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
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_home_name"="orahome92"
"nls_lang"="american_america.us7ascii"
"inst_loc"="c： files\\oracle\\inventory"
"api"="c："
"oracle_group_name"="oracle
- orahome92"
"oledb"="c："
"vobhome2.0"="c："
"oo4o"="c："
[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："
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 =
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（）
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（j - 1）。value = worksheets（"datasheet"）。cells（i，
j）。value
next j
empdynaset.dbupdate
empdynaset.dbmovenext
i
= i + 1
wend
orasession.committrans
擷取數組（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
orasession as object
dim oradynaset as
dim oraplsqlstmt as object
dim oraparray1 as object
oraparray2 as object
dim plsqlstmt1 as string
dim plsqlstmt2 as
string
‘ 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 " &
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； " & _
（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 =
oraparray2.get_value（j）
msgbox parm_value
cursorreturn：示範如何傳回pl/sql的遊标（cursors）。
該特性提供隻讀通路遊标（cursors）。 但是要求已經裝載pl/sql的存儲過程，如\oo4o2\oraexamp.sql
定義并設定對象
2. 從excel資料表（datasheet）j2單元格擷取範例資料表"dept" .
建立dept參數。
4. 通過存儲過程的事件建立記錄集
5. 拷貝記錄集到資料表頁（datasheet）
cursor_return（）
const oraparm_input =
dim orasession as object
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
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
range（"a2：h50"）。select
selection.clear
‘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 = 20 and issue
‘ a
oradynaset.refresh command
七、開放式通用平台應用案例
為了更直覺的示範oo4o技術和vba技術如何将ctais分發伺服器資料庫内容延伸到excel中，下面将引出一個基層管理者的實際應用範例——增值稅一般納稅人征管台賬。
1. 首先将制作好的免安裝用戶端拷貝到c盤根目錄。
2. 修改c：\oracle\network\admin\tnsnames.ora
的參數（對事先設定好統一發放的tnsnames可以忽略此步驟）
3. 運作批處理進行dll和ocx的注冊
4. 将設計好的pl/sql語句填入開放平台預先設計好的模式化接口程式中代碼引入部分，如下代碼斜體部分：
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 =
""
with activesheet.querytables.add（connection：=mycon，
destination：=range（"a2"））
。commandtext = mysql
。refresh
with
sheets（"納稅人資訊"）。select
步驟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設計參考手冊
（作者簡介：于非 泸州市國家稅務局資訊中心主任
易飛 泸州市國家稅務局資訊中心）