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

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開啟S004項目,我們貼入一個InfoTransaction元件,命名為tsMaster先設定UpdateComp屬性,選擇要做過帳的資料,這裏選擇ucMaster代表當ucMaster有發生Insert/Delete/Update時,會配合InfoTransaction一起過帳。

5-4-1 貼入InfoTransaction元件

5-4-2 設定UpdateComp屬性

 

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忽略並繼續過帳。

 

5-4-3 設定過帳

 

 

Step2WhenInsertWhenUpdatWhenDelete三個屬性設為True

 

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

5-4-3 開啟過帳時機

 

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

 

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



5-4-4 開啟過帳時機

 

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

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

Step3點擊TransFields屬性右邊的按鈕,出現TransField Collection Editor視窗,點擊左下的Add按鈕,增加一個TransField,下拉選擇DesFieldApAmountSrcField選擇為TotalAmountUpdateMode選擇為inc

5-4-5 設定主表與目的表的欄位元關係1

 

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

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

 

Step4TransFields中繼續新增第二個過帳欄位,這個我們將DesField選擇為LastPurchaseDateSrcField選擇為PurchaseDateUpdateModeReplace。此代表要將PurchasePurchaseDate記錄到SuppliersLastPurchaseDate中。

5-4-6 設定主表與目的表的欄位元關係2

 

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

 

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

Step5在先前的DefaultValidate1FieldItems屬性按下設定增加一個欄位。FieldNamePurchaseDateDefaultValue設定為一個函數傳回值GetToday()

5-4-7 增加一個PurchaseDate欄位

 

Step5並在From上增加一個GetToday()的程式,其中為了配合PurchaseDateDataTime的日期格式,在此使用了標準的ToString()格式。程式碼如下:

public string GetToday()
{
return System.DateTime.Today.ToString
();

}

插入後如下圖所示:

5-4-8 增加一個GetToday()的程式

 

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

5-4-9 打開C004項目

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

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

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

5-4-10 查看過帳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的設定基本相同。打開S004項目,增加一個infoTransaction元件,命名為tsDetail。先設定UpdateComp屬性,這裏選擇ucDetail

5-4-11 增加一個infoTransaction元件

 

 

 

 

 

 

 

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


5-4-12 增加一個TransTableName1

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

5-4-13 設定TransTableName1

 

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

5-4-14 新增一個TransKeyField


5-4-15 設定關聯關係

 

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


5-4-16 新增一個TransField

 

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




5-4-17 再新增一個TransField

 

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

5-4-17 設置完成後介面

 

按下連接線的起點(圓點)可以設定TransKeyfield;按下終點(箭頭)可以設定TransField如圖5-4-18/19

5-4-18 TransKeyfield設定介面

5-4-19 TransField設定介面

 

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

5-4-20 資料異動

 

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

5-4-21 查看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打開S004項目,一樣在之前的tsDetail中新增一個Transaction,設定TransTableNameDetail_Log TransModeAlwaysAppend

5-4-22 tsDetail中新增一個Transaction

 

Step3接著設定TransKeyFieldsPurchaseID對應到PurchaseID,以及ProductID對應到ProductID

5-4-23 欄位對應

 

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

 

Step3設定TransFields,表示哪些要記錄的Log欄位。這裏新增Seq如圖5-4-24,再新增UnitPrice如圖5-4-25,再新增Quantity如圖5-4-26,再新增Discount如圖5-4-27

5-4-24 新增Seq欄位

5-4-25 新增UnitPrice欄位


5-4-26 新增Quantity欄位


5-4-27 新增Discount欄位

 

 

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

 

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

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

5-4-28 查看結果

 

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

5-4-28 查看後端SQL語句

 

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

 

===============EEP2012基礎設計(中)完==============


訊光科技系統股份有限公司

Converted from CHM to HTML with chm2web Pro 2.85 (unicode)