第七章 Server端程式開發第七章 Server端程式開發\7-2 InfoTransation過帳設計

7-2  InfoTransation過帳設計

一般資料庫系統的資料登入,往往不是單純的新增更改刪除,而且會需要與其他資料表發生相關性的異動關係,如輸入出貨單必須對相對的產品庫存扣除,進貨單須對產品庫存增值等等,就是最常見的資料過帳實例。過帳的程式寫法常用的有三種方法,說明如下。

u  第一種方式:

使用後端資料庫的Trigger方式來設計,因為Trigger可以讓你在Table Insert/Delete/Update時搭配InsertedDeleted的新舊資料來自行下達SQL語句達到資料過帳的目的,這種寫法優點是很方便,只要負責下基本Insert/Delete/Update指令給資料庫就可已完成過帳,執行效能高。缺點是維護除錯不易,並當User數量大時,會讓資料庫的負荷不來,另外就是程式與資料庫捆綁的太緊,系統不易與資料庫獨立分開。

u  另一種方式:

是寫在A/P Server上,利用EEPUpdateCompBeforeInsert/AfterInsert/BeforeDelete/AfterDelete/BeforeModify/AfterModify等事件可以用來同步下達SQL命令,與Trigger的方式很像,不同的是將程式寫在A/P Server上,除錯與維護都容易一些,效能也可以分散,缺點還是必需由程式人員來開發,並且開發方式與語法也不標準。

u  EEP內建一個Infotransaction元件,可以直接與UpdateComp來配合,可針對大多數的過帳與異動關係進行標準化與規格化的管理,因此,設計者只要透過視覺化方式定義,就可以達到多數過帳的目的,不必再另外設計。

 

下文中我們將舉例說明InfoTransaction的強大功能。

q  設計一個簡單的主檔過帳

我們將舉上一個採購訂單的例子,來說明過帳的使用。也就是要將採購單Purchase的採購金額TotalAmount加總到供應商Supplier的應付帳總額APAmount,並將Purchase採購日期回寫Suppliers的最後採購日LastPurchaseDate

 

因為Suppliers並沒有這兩個欄位,因此我們必須用SQL Query Analyzer來在SQL命令中下達下列語句:

Alter table suppliers

add LastPurchaseDate datetime null,APAmount money null

 

Step1開啟sPurchases項目,我們貼入一個InfoTransaction元件,命名為tsMaster。先設定UpdateComp屬性,選擇要做過帳的資料,這裏選擇【ucPurchases】。代表當ucPurchases有發生Insert/Delete/Update時,會配合InfoTransaction一起過帳。

Step2Transactions屬性右邊的【…】按鈕,出現Transaction Collection Editor視窗,代表要開始設定過帳的步驟。

Œ   如圖,按左下的【Add】按鈕,新增一個Transaction步驟(Step),系統過帳時是會依次按步驟來過帳,一個步驟通常代表對一個Table資料表的過帳關係,但也可以多個步驟對應同一個Table

  再來,我們先設定『TransTableName』屬性,即要過帳的目的資料表(對方的資料表),我們用下拉選擇的方式選【Suppliers】。

Ž  接著設定『TransMode』屬性,此用來處理過帳之前是否先瞭解對方是否有相對資料。這裏設為【AutoAppend】。代表Suppliers不存在時,會自動新增一筆相對的資料到Suppliers中。上面的模式中,只有AlwaysAppend在過帳前不會去Select對方Table的相對資料是否存在,其餘都會去Select,而且也是必要的。

 

TransMode共有AutoAppend/Exception/AlwaysAppend/Ignore四種模式。分別代表:

F   對方不存在時要AutoAppend自動新增;

F   Exception發出異常錯誤並終止過帳;

F   AlwaysAppend代表無論是Insert/Update/Delete都會讓對方資料表新增;

F   Ignore忽略並繼續過帳。

 

 

 

Step2將『WhenInsert』、『WhenUpdat』、『WhenDelete』三個屬性設為【True】。

 

過帳時機的設定分別為When Insert/When Update/When Delete,分別代表此過帳可配合的時機為InsertUpdateDelete時發生作用。一般預設三個項目都是True,代表UpdateComp不管Insert/Delete/Update都會去執行InfoTransaction的過帳。

 

與對方Table的過帳關係共有兩種設定。一種是鍵值關係(TransKeyFields),代表資料表與資料間表的Where關係,另一種就是過帳關係(TransFields),是指主表欄位要與對方資料表的欄位處理關係。因此我們先設定TransKeyFields屬性。

 

Step2點擊TransKeyFields屬性右邊的【】按鈕,出現TransKeyField Collection Editor視窗。按左下的【Add】按鈕,增加一個TransKeyField,下拉選擇 SrcField 屬性為【SupplierID】,『DesField』屬性也選【SupplierID】。(兩個Table的欄位名稱剛好相同)。



此代表要以Purchase資料表的SupplierID去找SuppliersSupplierID。在過帳時此TransKeyFields就是用來組合Where的語法,因此可以設定多組KeyFields,而且必須一對一對應。設定完畢按下【OK】。

接著設定TransFields屬性來定義主表與過帳目的表的過帳欄位關係。

Step3點擊TransFields屬性右邊的【…】按鈕,出現TransField Collection Editor視窗,點擊左下的【Add】按鈕,增加一個TransField,下拉選擇『DesField』為【ApAmount】,『SrcField』選擇為【TotalAmount】,『UpdateMode』選擇為【inc】。

 

此代表要以PurchaseTotalAmount欄位值去累加到SuppliersApAmount欄位中。

UpdateModeInc/Dec/WriteBack/Replace/Disable5種方式。除了Inc代表累加之外;Dec代表累減;Replace代表更換(覆蓋對方的欄位);WriteBack則是與Replace相反,反而要將對方欄位內容回寫到主表的欄位上,一般用來記錄對方動態的欄位值或AlwaysAppend所產生的單據號碼的回寫。

 

Step4TransFields中繼續新增第二個過帳欄位,這個我們將『DesField』選擇為【LastPurchaseDate】,『SrcField』選擇為【PurchaseDate】,『UpdateMode』為【Replace】。此代表要將PurchasePurchaseDate記錄到SuppliersLastPurchaseDate中。

 

Step4請編譯sPurchases項目,過帳功能基本上已完成。

 

為了方便檢驗過帳的實現,我們將bPurchases打開,並在JQDefault中增加對PurchaseDate的預設值處理。

Step5在先前的JQDefaultColumns屬性按下【…】設定增加一個欄位。『FieldName』選【PurchaseDate】,『DefaultValue』設定為一個系統變數【_today】。

 

 

Step6執行結果,此時我們可以再打開Purchase,並新增一筆資料。如圖,在「廠商編號」與「員工編號」中各輸入1,訂單日期(應該代表採購日期)會自動帶入今天的日期,在「金額」上輸入5000,最後按下「存檔」。

此時也可以利用SQL Profiler看到其過帳的SQL語句。

首先,可以看到整個存檔與過帳被一組BEGIN TRANSACTIONCOMMIT TRANSACTION包起來,也就是過帳會與主檔明細檔的異動在同一個交易處理當中,一同成功或一同退回(這是因為ucPurchases.AutoTranas=True)。

再來,過帳的部份因為TransMode=AutoAppend,所以會有一個取Select Suppliers的語句,再下達一個Update Suppliers的語句,並對APAmount進行增值與LastPurchaseDate進行設值的動作,如果是UpdateDelete時,其SQL語句會自動對應。

 

 

q   設計明細檔的過帳

再來就是要處理採購單明細檔Purchase Detail的過帳處理,讓Purchase DetailQuantity累加到ProductsUnitsInStock的欄位上,原本此欄位應該是進貨單時才累加到UnitsInStock,因為Products中並沒有UnitsOnPurchase的欄位,我們就以UnitsInStock做為實驗對象,其過帳理論是一致的,另外我們也在Products中另外增加一個LastPurchasePrice代表最後採購單價,讓Products能自動記錄下來。

因為Products並沒有這LastPurchasePrice欄位,所以我們用SQL Query Analyzer來在SQL命令中下達下列語句:

Alter table products

add LastPurchasePrice money null

 

Step1Detail的過帳與Master的設定基本相同。打開sPurchases項目,增加一個infoTransaction元件,命名為【tsDetail】。先設定『UpdateComp』屬性,這裏選擇【ucPurchaseDetails】。

 

Step2連點兩下(double clicktsDetail就會出現如下的InfoTransaction Component Editor設定介面。按下Add】按鈕,在新增的TransTableName1的方格中連點兩下(double click)。


Step3在出現的設定介面中,下拉『TransTableName』屬性為【Products】。『TransMode』屬性同樣設為【AutoAppend】(代表如果對方不在會自動新增相對的資料)。

 

Step4按下TransKeyFields屬性右邊的小按鈕【…】,出現新的設定視窗。開始對此Transaction來定義彼此的鍵值關係。按【Add】新增一個TransKeyField。將『DesField』屬性下拉選擇為【ProductID】,『SrcField』屬性為【ProductID】(在此明細檔與Products的欄位名稱剛好一樣),按下【OK】。



 

Step5接著設定與Products關連的過帳欄位,按下TransFields屬性右邊的小按鈕【…】,出現TransField Collection Editor視窗。按【Add】新增一個TransField,將『DesField』屬性下拉選擇為【UnitsInStock】,『SrcField』屬性則選擇為【Quantity】,『UpdateMode』屬性則為【Inc】。按下【OK】。



 

Step6再新增一個過帳欄位,按下Add】按鈕,將『DesField』屬性下拉選擇為【LastPurchasePrice】,『SrcField』屬性則選擇為【UnitPrice】, UpdateMode』屬性則為【Replace】。按下【OK】。





 

Step7完成之後的InfoTransaction Component Editor設計介面如下圖。

 

按下連接線的起點(圓點)可以設定TransKeyfield;按下終點(箭頭),可以設定TransField

 

Step8同樣打開Purchase這個視窗,這次要測試的是更改的動作,選擇一筆有Detail的資料,在明細中將1號產品的單價多加了5元,如圖把20元改成25元,並在2號產品的數量上加10,如將數量40改成50,最後按下Master的「存檔」即可。

 

最後,我們用SQL Profiler來監看此過帳的SQL語句。如圖所示。

可以看到交易時有兩筆Purchase Detail都進行了更改的動作,也各進行了過帳的處理,1號產品的UnitsInStock沒有改變,改變的是LastPurchasePrice成為252號產品的UnitsInStock增加的10LastPurchasePrice則一樣為20



q   AlwaysAppend的應用實例

TransMode(過帳模式)有一種AlwaysAppend用來特殊處理不管主檔是Insert/Delete/Update時,都是以Insert來記錄到對方的目的資料表(Update時會有新舊值各Insert一筆到目的資料表)。此目的就是讓交易的資料表可以用OnLine的方式集中到幾個資料表上統一處理,來讓系統的計算與統計功能變得更容易處理。在這裏我們另外開立一個Detail_Log資料表,來將所有庫存的交易都存到此統一處理。如進貨/出貨/入庫/出庫/借出/借入/調撥出/調撥入等所有庫存交易作業的明細資料表都會統一Log到此Detail_Log表中。

 

Step1新建一個Table,命名為Detail_Log,資料結構與Order Details幾乎完全一樣,只是新增一個Log_IDIndentity欄位,作為主鍵,這樣的資料結構可以記錄Detail的每個欄位的值。當然,也可以只選擇性的按需要設計Log的欄位。

CREATE TABLE [Detail_Log] (

[Log_ID] [int] IDENTITY (1, 1) NOT NULL ,

[PurchaseID] [nchar] (10) NULL ,

[ProductID] [int] NULL ,

[Seq] [nchar] (3 NULL ,

[UnitPrice] [money] NULL ,

[Quantity] [smallint] NULL,

[Discount] [real] NULL,

CONSTRAINT [PK_Detail_Log] PRIMARY KEY CLUSTERED

(

    [Log_ID]

)  ON [PRIMARY]

) ON [PRIMARY]

GO

 

Step2打開sPurchases項目,一樣在之前的tsDetail中新增一個Transaction,設定『TransTableName』為【Detail_Log】, TransMode』為【AlwaysAppend】。

 

Step3接著設定TransKeyFields為【PurchaseID】對應到【PurchaseID】,以及【ProductID】對應到【ProductID】。

 

一般AlwaysAppend模式下設定在TransKeyFieldsTransFields是近似的,但不能重複設定,也就是設定在TransKeyFields中又設定在TransFields,最大的差別是TransKeyFields只有Replace Mode,但TrnasFields則有inc/dec的模式。

 

Step3設定TransFields,表示哪些要記錄的Log欄位。這裏新增Seq,再新增UnitPrice,再新增Quantity,再新增Discount

 

 

因為過帳的方式是AlwaysAppend,所以UpdateMode都設為Replace,只有Quantity是設為inc在原始檔更改時,會Insert兩筆到Detail_Log資料表中,一筆是更改前的舊值,一筆是更改後的新值,因此在UpdateModeinc時,第一筆Insert的舊值會自動為負值(用來扣除先前的交易),第二筆Insert的新值會自動為正值(再加上去)。如果Dec則正負後剛好與inc相反。

 

Step4編譯sPurchases,執行查看結果。

我們試著在某一筆Master所對應的Detail中,去新增一筆Detail。如輸出「產品號碼」為4,「單價」為10,「數量」為40;並在「產品號碼」為3的那筆進行更改,將「數量」原本為10改成30,最後按下Master的「存檔」。

 

最後,我們一樣用Profiler來查看後端對Detail_Log所執行的SQL語法來瞭解AlwaysAppend的作用。

我們可以看會有三筆InsertDetail_Log資料表中,第一二筆為Update所產生的,Quantity10改成30時,可以看到Quantityinc模式,產生一筆 -10的數量,與另一筆 +30的數量;第三筆為Insert的資料,相對也產生另一比「產品編號」為4Detail_Log資料;如果有Delete的話,Detail_Log也會Insert,只是Quantity會自動為負值。


Top of Page