???前言：
??????這段時間還一直在努力之中，這一次在SQL的構(gòu)建上遇到一個挑戰(zhàn)，不過這一次的挑戰(zhàn)是自找的
。
??????開發(fā)環(huán)境是VB6的DLL＋ASP，分到的活是一個報表模塊。
??????需求
?????????我這一塊的報表，現(xiàn)階段是要實(shí)現(xiàn)兩個功能(就我看應(yīng)該只是一個，需求理解錯誤可不是我的事兒)，為了與其它的模塊保持一致，工作流程如下：
?????????1.輸入查詢的年 / 月;
?????????2.查詢結(jié)果並以網(wǎng)頁表格的方式提供預(yù)覽;
?????????3.選擇發(fā)送郵件功能;
?????????4.選取需要發(fā)送的郵件收件人列表;
?????????5.查詢結(jié)果生成Excel文件並以郵件附件的方式發(fā)送。
??????
輸入
?????????參數(shù)是一個 年份值＋月份值：
?????????1. lngYear (0001-9999)，年份值;
?????????2. lngMonth (1-12)，月份值.
?????????由於Input參數(shù)少，JS檢驗比較簡單，因此會在客戶端JS提供第一層的校驗，而模塊裡面就不再做檢驗，捕獲異常即可。
??????輸出
?????????根據(jù)輸入的參數(shù)，搜尋記錄，提供瀏覽以及發(fā)送Excel報表的能力(以郵件附件的方式發(fā)送，在此不做論術(shù))。
?????????再來說說環(huán)境的事情，開發(fā)環(huán)境是用VB的DLL實(shí)現(xiàn)功能封裝，因此我的報表會是一個cls，ASP頁面只要實(shí)現(xiàn)View即可。數(shù)據(jù)環(huán)境是用MSSQL2000，第1個難點(diǎn)出現(xiàn)在這裡：數(shù)據(jù)環(huán)境雖說是用的MSSQL，但是卻不能使用Procedure;DB的設(shè)計也增加了難度：整個DB實(shí)現(xiàn)上是包含了兩個系統(tǒng)的數(shù)據(jù)庫，不同的結(jié)構(gòu)，不同的命名規(guī)則，卻有相同含義的字段
。為了便於說明，拉一個示例數(shù)據(jù)庫出來先：
?????????
見 PDM 圖
(去掉全部無關(guān)字段)
?????????可以看到，DB中以 tbXXX 方式命名的表與 XXX(AS400) 的表有著很大的差異，但是它們之間又存在著相同之處。
??????再來看看表間的隱式關(guān)聯(lián)關(guān)係 (沒有以FK的方式加以約束)
-
合約租金(tbContractRent)? 與 職場(tbPlace)的關(guān)聯(lián)：職場代碼相同 ，合約是職場在某一段時間的表現(xiàn)，即相同職場在某一時刻只會存在一份合約
-
合約租金(tbContractRent)? 與 職場單位(tbUnit)的關(guān)聯(lián)：單位的坪數(shù)生效日/坪數(shù)失效日的區(qū)間 落在一份合約租金生效/租金失效區(qū)間之內(nèi)
-
職場單位(tbUnit) 與 單位資料AS400(AAABREP)的關(guān)聯(lián)：單位代碼 + 單位序號 相同
-
職場單位(tbUnit) 與 單位資料2AS400(DAD6CPP)的關(guān)聯(lián)：(單位代碼 + 單位序號 相同) 並且 (原職場代碼 = 職場代碼 或 新職場代碼 = 職場代碼)
-
合約租金(tbContractRent)? 與 職場資料AS400(DAC9CPP)的關(guān)聯(lián)：職場資料到期日?落在 一份合約租金生效/租金失效日區(qū)間之內(nèi)
???????功能描述：
-
“閒置租約”：
職場單位與租約相關(guān)，這裡的“閒置”狀態(tài)由單位引發(fā)，描述是指在一份合約內(nèi)，單位發(fā)生過“撤銷”，然後就會從職場中“遷出”，那麼遷出日期－－合約租金失效日的區(qū)間內(nèi)即是“閒置”。
因此該功能的條件入口表是 合約租金(tbContractRent)，該功能的業(yè)務(wù)邏輯比較複雜，流程如下：
a. 在考察期內(nèi)有效的合約;
b. 在合約中的單位發(fā)生過“撤銷”-- 通過(tbUnit)連接到(AAABREP)檢查是否存在有撤銷日期(合法日期 並且 發(fā)生在考察期之前)，另外由於存在業(yè)務(wù)邏輯，這裡的撤銷日期在使用時需要向後加一個月;
c. 如果該單位符合撤銷條件則通過(tbUnit)連接到(DAD6CPP)獲取搬遷日期，同樣的，這裡所得到的搬遷日期由於一定的業(yè)務(wù)邏輯，還需要檢查它的有效性，只有大於撤銷日期的才會認(rèn)定為是合法的搬遷日期，否則將使用(tbUnit)表中的坪數(shù)失效日來做為該單位的搬遷日期;
d. 搬遷日期必須早於考察的結(jié)束時間;
-
“待退職場”：
是指一個職場的最後一份合約的失效日落在考察期內(nèi)，由於相同資料的以 XXXAS400表為主，因此條件入口表是職場資料AS400(DAC9CPP)
-
最終的表現(xiàn)形式是一張2維表格的方式，即功能1 與 功能2的結(jié)果要放在一起
??????約束：
?????????不允許使用存儲過程，只能在VB的DLL中實(shí)現(xiàn)。
??????思考及方案選擇：
?????????1. 功能1是這次任務(wù)的難點(diǎn)，實(shí)現(xiàn)邏輯需要跑的表比較多，而且邏輯間也比較複雜，看上去並不能簡單的用 Inner/Left Join + Where就可以搞定那幾表。
?????????2. 最終的結(jié)果集會建立在指定的幾張表上，與實(shí)現(xiàn) 功能 所聯(lián)接的表並不相同，無法在結(jié)果表上進(jìn)行直接篩選，還需要將上面所拿到的條件結(jié)果集與最終的表現(xiàn)表進(jìn)行一次關(guān)聯(lián)。
?????????3. 實(shí)現(xiàn)這次功能的方法有兩種：
?????????3.1. 用ADO對象的嵌套來實(shí)現(xiàn)，配合上帶層次的 Function ，這是比較傳統(tǒng)的解決方案，完全可以解決這類問題。
????????????優(yōu)點(diǎn)
????????????a. 適用面廣，複雜度比較低;
????????????b. 函數(shù)封裝，功能邏輯清晰;
????????????缺點(diǎn)
????????????a.?很明顯的，在這次的任務(wù)中要實(shí)現(xiàn)功能，需要使用3層以上的循環(huán)，這種邊循環(huán)邊查詢的方式的效率是極低的;
?????????3.2. 嚐試使用複雜SQL來構(gòu)造邏輯實(shí)現(xiàn)。
????????????使用這種解決方法，在該問題上算是一種比較酷的解決。使用它的好處就是能提高查詢效率，當(dāng)然對於我來說，這算是一種嚐試和創(chuàng)新，嘻嘻。
????????????優(yōu)點(diǎn)
????????????a. 查詢效率高，因為它只會發(fā)出一條SQL;
????????????缺點(diǎn)
????????????a. 難於構(gòu)建，複雜度高;
????????????b. 如果結(jié)構(gòu)不好則很難調(diào)試及更改，發(fā)生需求變更時改動難度大;
??????細(xì)節(jié)分析：
?????????最終俺決定使用複雜SQL來構(gòu)建邏輯，雖說實(shí)現(xiàn)起來會很困難，時間也蠻緊巴的，不過這是一個挑戰(zhàn)，我之前也對自己的能力抱有信心。好了，切入正題，談?wù)勎业膶?shí)現(xiàn)細(xì)節(jié)。
?????????對於之前所考慮到的問題，我針對性的設(shè)計一種結(jié)構(gòu)來應(yīng)付它。
?????????a. 由於這次的查詢條件表比較多，各表間無法直連，所以我在表這一層上使用封裝，以降低它們之間的瓜葛，並試圖將表這一層的變動壓抑在封裝之內(nèi)，這一點(diǎn)至關(guān)重要，因為SQL就象是一座塔，底部會由幾個基礎(chǔ)表支撐，這就好比萬噸壓力全都壓在幾個支撐柱上，一旦柱子垮了塔也將不復(fù)存在;
?????????b. 在 a 的表封裝基礎(chǔ)上使用模塊化的構(gòu)建，SQL語句說到底都是一句句拼湊起來的，再複雜的語句也不過如此。因此按照邏輯一步步的走下來，逐步的封成單一的模塊，最後再象積木一樣搭建起來是很重要的;
?????????c. 這次頁面的功能是有分成 2 個部分的，而這2功能的查詢?nèi)肟诒砑斑B接順序又不相同(不要跟我說表 A 連接到 表 B 與表 B 連接到表 A 的邏輯是相同的)，不過邏輯的核心表都落在合約租金(tbContractRent)?上，因此采取將產(chǎn)出表與條件表切割開來，而2功能使用聯(lián)合的辦法來完成產(chǎn)出與邏輯的分離，SQL的結(jié)構(gòu)示例成為這樣： Select * From 產(chǎn)出表 Join (功能1 Union 功能2) As 功能表?On 產(chǎn)出與邏輯間的關(guān)聯(lián)。
?????????d. 對於SQL的調(diào)試問題，由於成品SQL的體積會使得調(diào)試起來頗為複雜，因此我在功能模塊上增加調(diào)試用的接口，這樣就能夠?qū)⒅鸩叫纬傻陌氤善方o輸出來，有利於外步調(diào)試時的分步分析;
?????????e. 為便於SQL調(diào)試，在代碼的書寫上使用了一點(diǎn)小技巧。通常在合成SQL時語句的寫法會是這樣:
?????????strSQL="Select * From Table Where col1='" & p1 & "' And col2='" & p2 & "'"
?????????這樣的寫法會增加源代碼的檢查難度，大量的字符串連接符和變量充斥其中，現(xiàn)在改成這樣寫:
?????????strSQL = "Select * From Table Where col1=@p1 And col2=@p2"
?????????在 SQL 語句搭建完成後再使用 strSQL = Repalce(strSQL,"@p1","'xxx'") 的方式來代入?yún)?shù)，即不會影響使用，又降低了源碼的檢查難度，而且配合調(diào)試時輸出:
?????????declare @p1 char(8),@p2 char(8)
?????????select @p1='xxx',@p2='yyy'
?????????這樣一來，就可以很方便地將調(diào)試 SQL 語句直接 Copy 進(jìn)查詢分析器，直接修改輸入?yún)?shù)進(jìn)行調(diào)試分析了。
??????實(shí)現(xiàn)代碼：
?????????第一次嚐試使用這種方法來實(shí)現(xiàn)，花了很長時間(約2天時間)才完成，還好調(diào)試時只遇到了一個很小的失誤，以後模塊又經(jīng)歷了多次變更，後續(xù)文章中會加以討論在這種實(shí)現(xiàn)方式下我是如何跟隨需求變更的，當(dāng)然，經(jīng)過了N次的變更之後，還是....
CODE
'
'**************************************************************************************************
'
*程式功能??:???????獲取"待退（閒置）租約資料報表"所需記錄
'
*開發(fā)人員??: ?????ddm?2006/12/17
'
*異動人員??:
'
*傳出值????:???????rsPlace?????????--查出的主記錄集(包含職場/單位)
'
*傳出值????:???????rsOwner?????????--查出的次記錄集(包含房東資料)
'
*傳入值????:???????lngYear?????????--考察年份
'
*傳入值????:???????lngMonth????????--考察月份
'
*回傳值????:???????boolean?成功=true;失敗=false
'
'**************************************************************************************************
Public
?
Function
?fGetFreePlace(ByRef?rsPlace?
As
?Variant,?ByRef?rsOwner?
As
?Variant,?ByVal?lngYear?
As
?Variant,?ByVal?lngMonth?
As
?Variant)?
As
?
Boolean
On
?
Error
?
GoTo
?ErrHandler
????
Dim
?strYear?
As
?
String
,?strMonth?
As
?
String
????strYear?
=
?
Trim
(
""
?
&
?lngYear)
????strMonth?
=
?
Trim
(
""
?
&
?lngMonth)
????
If
?
IsDate
(strYear?
&
?
"
-
"
?
&
?strMonth)?
=
?
False
?
Then
????????
'
輸入?yún)?shù)檢查
????????fGetFreePlace?
=
?
False
????????
GoTo
?ErrHandler
????????
Exit
?
Function
????
End
?
If
????
????
Dim
?strSql?
As
?
String
,?strWhere?
As
?
String
????
????
'
***************閒置開始***************
????strSql?
=
?
""
????
????
'
符合條件的?"閒置"?租約--(有效租約的相關(guān)單位在考察期間發(fā)生了?"整編")?&&?("迕出日期")?不能在考察期間以後(參照?clsMonthRent.fFreeRent?計算閑置租金)
????
'
準(zhǔn)備SQL的連接表
????
Dim
?strContract_TB?
As
?
String
,?strUnit_TB?
As
?
String
,?strAAABREP_TB?
As
?
String
,?strDAD6CPP_TB?
As
?
String
????
'
???tbContractRent合約租金表
????
'
???????邏輯1:未刪除的
????
'
???????邏輯2:租金生效日--租金失效日?的日期范圍(合約生效范圍)大於 考察期的范圍
????strContract_TB?
=
?
"
(?SELECT?*?from?tbContractRent?where?tbContractRent.CDelFlag='N'?and?(tbContractRent.CStartDate?<?'@StartDate8'?and?'@EndDate8'?<?tbContractRent.CEndDate)?)?tbContractRent
"
????
'
???tbUnit職場單位表
????strUnit_TB?
=
?
"
(Select?*?From?tbUnit?Where?CDelFlag='N')?tbUnit
"
????
'
???AAABREP單位資料(AS400)表
????
'
???????邏輯1:撤銷日期?早於?考察期
????
'
???????注意:撤銷日期(整編日期)會是實(shí)際整編日期的前一個月，因此必須先加1月以得到實(shí)際的整編日期
????
'
???????????取出表中的?PK:(ABABCD,ABI2CD)
????
'
???????????合成實(shí)際整編日期列
????strAAABREP_TB?
=
?
"
Select?ABABCD,ABI2CD,Case?right(ABACDT,2)?when?'12'?then?CAST((CAST(LEFT(ABACDT,4)?as?int)+1)?as?char(4))?+?'01'?else?LEFT(ABACDT,4)?+?RIGHT('0'+CAST((CAST(right(ABACDT,2)?as?int)+1)?as?varchar(2)),2)?End?As?ABACDT?From?AAABREP
"
????
'
???????????整編月份?<?考察月份
????strAAABREP_TB?
=
?
"
Select?*?from?(
"
?
&
?strAAABREP_TB?
&
?
"
)?AAABREP?Where?ABACDT?<?'@Date6'
"
????
'
???????????包裝
????strAAABREP_TB?
=
?
"
(
"
?
&
?strAAABREP_TB?
&
?
"
)?AAABREP
"
????
'
???DAD6CPP單位資料檔2(AS400)表
????
'
???????邏輯1:由於在該表?中?當(dāng)單位發(fā)生"整編時"?並不一定?會記錄下?"搬遷日期(參看clsMonthRent.fFreeRent中的邏輯)"
????
'
???????????判斷邏輯1是否成立的條件:?DAD6CPP單位資料檔2(AS400)表中的"搬遷日期"?必須晚於?AAABREP單位資料(AS400)表中的"整編日期"
????
'
???????????處理:分步連接,使用?Left/Right?Join,?與前3表的結(jié)果表進(jìn)行連接
????strDAD6CPP_TB?
=
?
"
DAD6CPP
"
????
????
'
準(zhǔn)備表間連接條件
????strWhere?
=
?
"
?Where
"
????
'
???tbContractRent合約租金表?與?tbUnit職場單位表
????
'
???????聯(lián)合邏輯:(職場ID相同)?&&?(職場單位"坪數(shù)生效日","坪數(shù)失效日"區(qū)間要在?合約租金"租金生效","租金失效"區(qū)間之內(nèi))
????strWhere?
=
?strWhere?
&
?
"
?(tbContractRent.CPlaceCode?=?tbUnit.CPlaceCode)?and?((tbContractRent.CStartDate?>=?tbUnit.CAreaBegin)?and?(tbUnit.CAreaEnd?<=?tbContractRent.CEndDate))
"
????
'
???tbUnit職場單位表?與?AAABREP單位資料(AS400)表
????
'
???????聯(lián)合邏輯:(單位代碼相同)?&&?(單位序號相同)
????strWhere?
=
?strWhere?
&
?
"
?and?(?(tbUnit.CUnitID?=?AAABREP.ABABCD)?and?(tbUnit.CUnitSeq?=?AAABREP.ABI2CD)?)
"
????
????
'
連接
????
'
???步驟一:連接1.合約租金表;2.職場單位表;3.單位資料(AS400)表,並應(yīng)用3表的篩選條件
????
'
???????取出合約租金表中的?PK:(CContractid);
????
'
???????取出單位表中的?PK:(CPlaceCode,CUnitCode,CUnitName,CAreaBegin);
????
'
???????取出單位表中的?(CAreaEnd);
????
'
???????取出單位表中的?FK(CUnitID,CUnitSeq);
????
'
???????AAABREP單位資料(AS400)表中的?(ABACDT).
????strSql?
=
?
"
Select?tbContractRent.CContractid?as?CContractid,tbUnit.CPlaceCode?as?CPlaceCode,tbUnit.CUnitCode?as?CUnitCode,tbUnit.CUnitName?as?CUnitName,tbUnit.CAreaBegin?as?CAreaBegin,tbUnit.CAreaEnd?as?CAreaEnd,tbUnit.CUnitID?as?CUnitID,tbUnit.CUnitSeq?as?CUnitSeq,AAABREP.ABACDT?as?ABACDT?from?
"
????strSql?
=
?strSql?
&
?strContract_TB?
&
?
"
,
"
?
&
?strUnit_TB?
&
?
"
,
"
?
&
?strAAABREP_TB
????strSql?
=
?strSql?
&
?strWhere
????
'
???步驟二:連接1.步驟一的結(jié)果集
????
'
???????聯(lián)合邏輯:(原職場代號?||?新職場代號?==?單位職場代號)?&&?(單位代碼相同)?&&?(單位序號相同)?&&?(搬遷聯(lián)合日期?晚於?單位撤銷日期)
????
'
???????取出結(jié)果集1中的?全部字段(*);
????
'
???????取出單位資料檔2(AS400)表中的?(D6AOD8).
????
'
???????????如果單位資料檔2(AS400)表中無合法的?"遷出日期"?則使用?"單位坪數(shù)失效日期"
????strSql?
=
?
"
Select?tbResult1.*,ISNULL(DAD6CPP.D6AOD8,tbResult1.CAreaEnd)?as?D6AOD8?From?(
"
?
&
?strSql?
&
?
"
)?tbResult1?Left?Join?
"
?
&
?strDAD6CPP_TB
????strSql?
=
?strSql?
&
?
"
?On?(tbResult1.CPlaceCode?=?DAD6CPP.D6LQCD?Or?tbResult1.CPlaceCode?=?DAD6CPP.D6LSCD)
"
????strSql?
=
?strSql?
&
?
"
?and?(tbResult1.CUnitID?=?DAD6CPP.D6FHCD)
"
????strSql?
=
?strSql?
&
?
"
?and?(tbResult1.CUnitSeq?=?DAD6CPP.D6FICD)
"
????
'
???????撤銷日期是一個6位日期只會考察到月,搬遷日期是一個8位日期,因此必須按6位的月份來計算
????strSql?
=
?strSql?
&
?
"
?and?(tbResult1.ABACDT?<=?LEFT(DAD6CPP.D6AOD8,6))
"
????
'
???步驟三:對步驟二的結(jié)果集進(jìn)行篩選
????
'
???????邏輯:單位的搬遷日期必須早於?考察期的結(jié)止日期
????
'
???????這裡合成?"閒置"?的最後記錄集
????
'
???????取1.合約租金表中的?PK:(CContractid)
????
'
???????取2.用於連接職場的?PK:(CPlaceCode)
????
'
???????取3.單位表?PK:(CPlaceCode,CUnitCode,CUnitName,CAreaBegin)
????strSql?
=
?
"
Select?CContractid,CPlaceCode,CUnitCode,CUnitName,CAreaBegin?From?(
"
?
&
?strSql?
&
?
"
)?tbResult2
"
????strSql?
=
?strSql?
&
?
"
?Where?D6AOD8?<?'@EndDate8'
"
????
'
???閒置邏輯完成:
????
'
???????記錄條件SQL,用於外部調(diào)試
????m_strFreeSql?
=
?strSql
????
'
***************閒置結(jié)束***************
????
????
'
***************待退開始***************
????
Dim
?strDAC9CPP_TB?
As
?
String
,?strSql1?
As
?
String
,?strWhere1?
As
?
String
????
'
???待退邏輯:最新一筆?"職場(AS400)"的到期日落在考察期內(nèi);例如考察期為95年12月,職場A有兩條:a.95.1-95.12;b.96.1-96.6;這不算待退.
????
'
???步驟一:
????
'
???????準(zhǔn)備職場資料記錄檔(AS400)表,每條職場只取最新的記錄
????strDAC9CPP_TB?
=
?
"
Select?DAC9CPP2.*?From
"
????strDAC9CPP_TB?
=
?strDAC9CPP_TB?
&
?
"
?(select?C9K3CD,max(C9JZNB)?C9JZNB?from?DAC9CPP?group?by?C9K3CD)?DAC9CPP1?join?DAC9CPP?DAC9CPP2
"
????strDAC9CPP_TB?
=
?strDAC9CPP_TB?
&
?
"
?on?DAC9CPP1.C9K3CD?=?DAC9CPP2.C9K3CD?and?DAC9CPP1.C9JZNB?=?DAC9CPP2.C9JZNB
"
????strDAC9CPP_TB?
=
?
"
(
"
?
&
?strDAC9CPP_TB?
&
?
"
)?DAC9CPP
"
????
'
???????篩選職場資料記錄檔(AS400)表,到期日落在考察期內(nèi)
????
'
???????取出表中的PK:(C9K3CD,C9JZNB)
????
'
???????取出表中的退租日
????strSql1?
=
?
"
Select?C9K3CD,C9JZNB,C9AGD8
"
????strSql1?
=
?strSql1?
&
?
"
?From?
"
?
&
?strDAC9CPP_TB
????strSql1?
=
?strSql1?
&
?
"
?Where?C9AGD8?Between?@StartDate8?And?@EndDate8
"
????
'
???步驟二:
????
'
???????通過符合條件的職場資料(AS400),與合約租金表關(guān)聯(lián)
????
'
???????包裝
????strSql1?
=
?
"
(
"
?
&
?strSql1?
&
?
"
)?DAC9CPP
"
????
'
???????連接合約租金表
????
'
???????????關(guān)聯(lián)邏輯:(職場ID相同)?&&?(職場的到期日落在合約租金表的"租金生效日","租金失效日"區(qū)間內(nèi)(如果能夠確認(rèn),應(yīng)該職場的到期日=合約的租金失效日))
????strSql1?
=
?strSql1?
&
?
"
,
"
?
&
?
"
(Select?*?From?tbContractRent?Where?CDelFlag='N')?tbContractRent
"
????strWhere1?
=
?
"
?Where?(DAC9CPP.C9K3CD?=?tbContractRent.CPlaceCode)?And?(DAC9CPP.C9AGD8?Between?tbContractRent.CStartDate?And?tbContractRent.CEndDate)
"
????
????
'
???????連接單位表
????
'
???????????關(guān)聯(lián)邏輯:(職場ID相同)?&&?(職場單位"坪數(shù)生效日","坪數(shù)失效日"區(qū)間要在?合約租金"租金生效","租金失效"區(qū)間之內(nèi))
????strUnit_TB?
=
?
"
(Select?*?From?tbUnit?Where?CDelFlag='N')?tbUnit
"
????strSql1?
=
?strSql1?
&
?
"
,
"
?
&
?strUnit_TB
????strWhere1?
=
?strWhere1?
&
?
"
?And?(tbContractRent.CPlaceCode?=?tbUnit.CPlaceCode)?And?((tbContractRent.CStartDate?>=?tbUnit.CAreaBegin)?and?(tbUnit.CAreaEnd?<=?tbContractRent.CEndDate))
"
????
'
???步驟三:
????
'
???????將連接的3表封裝,合成?"待退"?的最後記錄集
????
'
???????取1.合約租金表中的?PK:(CContractid)
????
'
???????取2.用於連接職場的?PK:(CPlaceCode)
????
'
???????取3.單位表?PK:(CPlaceCode,CUnitCode,CUnitName,CAreaBegin)
????strSql1?
=
?
"
Select?tbContractRent.CContractid?as?CContractid,tbUnit.CPlaceCode?as?CPlaceCode,tbUnit.CUnitCode?as?CUnitCode,tbUnit.CUnitName,tbUnit.CAreaBegin?From?
"
?
&
?strSql1
????strSql1?
=
?strSql1?
&
?strWhere1
????
'
???待退邏輯完成:
????
'
???????記錄條件SQL,用於外部調(diào)試
????m_strEndSql?
=
?strSql1
????
'
***************待退結(jié)束***************
????
????
'
***************閒置?UNION?待退***************
????
Dim
?strUnionSql?
As
?
String
,?strUnionSql1?
As
?
String
,?strPlace_TB?
As
?
String
,?strFinalUnion_TB?
As
?
String
????
'
???準(zhǔn)備最終結(jié)果集表(閒置?UNION?待退)
????strFinalUnion_TB?
=
?
"
(
"
?
&
?strSql?
&
?
"
?Union?
"
?
&
?strSql1?
&
?
"
)?tbFinalUnion
"
????
'
???準(zhǔn)備主記錄集表頭所要的表
????strDAC9CPP_TB?
=
?strDAC9CPP_TB???
'
使用上面的邏輯,取AS400相同職場資料"DAC9CPP"中最新的一條
????strContract_TB?
=
?
"
(Select?*?From?tbContractRent?Where?CDelFlag='N')?tbContractRent
"
????strPlace_TB?
=
?
"
(Select?*?From?tbPlace?Where?CDelFlag='N')?tbPlace
"
????strUnit_TB?
=
?
"
(Select?*?From?tbUnit?Where?CDelFlag='N')?tbUnit
"
????
????
'
報表需求:
????
'
???????根據(jù):"租賃系統(tǒng)補(bǔ)充需求_20061130.doc"中?租賃系統(tǒng)補(bǔ)充需求/第5點(diǎn)?:
????
'
???單位中文名稱．職場代碼．地址．虛坪．每坪租金單價．租金．租金福利預(yù)算．當(dāng)月應(yīng)代扣租額．停車位租金．停車位數(shù)量．
????
'
???押金．起租日．續(xù)租日．到期日．搬遷訊息．提前解約規(guī)定及罰則．復(fù)原條件．空調(diào)維護(hù)保養(yǎng)責(zé)任．租約附註事項說明．房東．
????
'
???聯(lián)絡(luò)人．聯(lián)絡(luò)人電話．租金給付方式．大樓名稱．管委會聯(lián)絡(luò)人．管委會聯(lián)人電話
????
'
???退租日
????
'
???????列特殊邏輯:
????
'
???????????1.退租日為合約終止日期或提前退租日
????
'
???????????2.當(dāng)月應(yīng)代扣租額：若租金＜租金福利預(yù)算?則為"0"，反之，則為:租金－租金福利預(yù)算
????
????
'
拼裝返回結(jié)果記錄集SQL:
????
'
???由於房東的資料只能關(guān)聯(lián)到?"房東資料上"?,?采取雙結(jié)果記錄集的方式返回
????
'
???步驟一:
????
'
???????拼主記錄集表頭
????
'
單位中文名稱(單位序號可能為空,IF?==?空?Then?get單位中文名稱)
????strUnionSql?
=
?
"
Select?Case?LEN(LTRIM(RTRIM(ISNULL(tbUnit.CUnitCode,''))))?When?0?Then?tbUnit.CUnitName?Else?tbUnit.CUnitCode?End?As?CUnitName
"
????
'
職場代碼
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9K3CD?As?CPlaceCode
"
????
'
地址
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9BAIG?AS?CAddress
"
????
'
虛坪
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9JCNB?AS?CTotalArea
"
????
'
每坪租金單價
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9AVVA?AS?CPrice
"
????
'
租金
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9AUVA?AS?CRent
"
????
'
租金福利預(yù)算
????strUnionSql?
=
?strUnionSql?
&
?
"
,tbPlace.CRentBudget?AS?CRentBudget
"
????
'
當(dāng)月應(yīng)代扣租額
????strUnionSql?
=
?strUnionSql?
&
?
"
,Case?When?Cast(IsNull(DAC9CPP.C9AUVA,'0.00')?As?Money)?<?Cast(IsNull(tbPlace.CRentBudget,'0.00')?As?Money)?Then?'0.00'
"
????strUnionSql?
=
?strUnionSql?
&
?
"
???Else?Cast(Cast(IsNull(DAC9CPP.C9AUVA,'0.00')?As?Money)?-?Cast(IsNull(tbPlace.CRentBudget,'0.00')?As?Money)?As?char(8))
"
????strUnionSql?
=
?strUnionSql?
&
?
"
?End?AS?CDkRent
"
????
'
停車位租金
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9AXVA?AS?CPartRent
"
????
'
停車位數(shù)量
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9JENB?AS?CPartCount
"
????
'
押金
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9BAVA?AS?CForegift
"
????
'
起租日
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9AED8?AS?CStartDate
"
????
'
續(xù)租日
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9AFD8?AS?CRestartDate
"
????
'
到期日
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9AGD8?AS?CEndDate
"
????
'
搬遷訊息
????strUnionSql?
=
?strUnionSql?
&
?
"
,tbPlace.CMoveInfo?AS?CMoveInfo
"
????
'
提前解約規(guī)定及罰則
????strUnionSql?
=
?strUnionSql?
&
?
"
,tbPlace.CRules?AS?CRules
"
????
'
復(fù)原條件
????strUnionSql?
=
?strUnionSql?
&
?
"
,tbPlace.CRecover?AS?CRecover
"
????
'
空調(diào)維護(hù)保養(yǎng)責(zé)任
????strUnionSql?
=
?strUnionSql?
&
?
"
,tbPlace.CAirCondition?AS?CAirCondition
"
????
'
租約附註事項說明
????strUnionSql?
=
?strUnionSql?
&
?
"
,tbPlace.CAppendRule?AS?CAppendRule
"
????
'
大樓名稱
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9DCIG?AS?CBuildingName
"
????
'
管委會聯(lián)絡(luò)人
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9DDIG?AS?CManager
"
????
'
管委會聯(lián)人電話
????strUnionSql?
=
?strUnionSql?
&
?
"
,DAC9CPP.C9LXCD?AS?CManagerTel
"
????
'
退租日tbContractRent
????strUnionSql?
=
?strUnionSql?
&
?
"
,Case?LEN(LTRIM(RTRIM(IsNull(tbContractRent.CAdvanceBackRentDate,''))))
"
????strUnionSql?
=
?strUnionSql?
&
?
"
???When?8?Then?tbContractRent.CAdvanceBackRentDate
"
????strUnionSql?
=
?strUnionSql?
&
?
"
???Else?tbContractRent.CEndDate
"
????strUnionSql?
=
?strUnionSql?
&
?
"
?End?As?CBackRent
"
????
????
'
???步驟二:
????
'
???????表頭掛接?From,主記錄集需要?From?的表:DAC9CPP/tbContractRent/tbPlace/tbUnit/tbFinalUnion最終結(jié)果集表(閒置?Union?待退)
????strUnionSql?
=
?strUnionSql?
&
?
"
?From?(((
"
?
&
?strFinalUnion_TB
????strUnionSql?
=
?strUnionSql?
&
?
"
?Join?
"
?
&
?strDAC9CPP_TB?
&
?
"
?On?tbFinalUnion.CPlaceCode=DAC9CPP.C9K3CD)
"
????strUnionSql?
=
?strUnionSql?
&
?
"
?Join?
"
?
&
?strPlace_TB?
&
?
"
?On?tbFinalUnion.CPlaceCode=tbPlace.CPlaceCode)
"
????strUnionSql?
=
?strUnionSql?
&
?
"
?Join?
"
?
&
?strContract_TB?
&
?
"
?On?tbFinalUnion.CContractid=tbContractRent.CContractid)
"
????strUnionSql?
=
?strUnionSql?
&
?
"
?Join?
"
?
&
?strUnit_TB?
&
?
"
?On?tbFinalUnion.CPlaceCode=tbUnit.CPlaceCode
"
????strUnionSql?
=
?strUnionSql?
&
?
"
???And?tbFinalUnion.CUnitCode=tbUnit.CUnitCode
"
????strUnionSql?
=
?strUnionSql?
&
?
"
???And?tbFinalUnion.CUnitName=tbUnit.CUnitName
"
????strUnionSql?
=
?strUnionSql?
&
?
"
???And?tbFinalUnion.CAreaBegin=tbUnit.CAreaBegin
"
????
????
'
???步驟三:
????
'
???????按?CPlaceCode?Asc,CUnitName?排序
????strUnionSql?
=
?strUnionSql?
&
?
"
?Order?By?CPlaceCode?Asc,CUnitName
"
????
????
????
'
房東資料需要關(guān)聯(lián)的表:合約租金表/合約租金房東資料/房東資料(tbOwner)/付款方式(tbPayMode)
????
Dim
?strContractOwner_TB?
As
?
String
,?strOwner_TB?
As
?
String
,?strPayMode_TB?
As
?
String
????strContractOwner_TB?
=
?
"
(Select?*?From?tbContractOwner?Where?CDelFlag='N')?tbContractOwner
"
????strOwner_TB?
=
?
"
tbOwner
"
?
'
(Select?*?From?tbOwner?Where?CDelFlag='N')
????strPayMode_TB?
=
?
"
tbPayMode
"
?
'
(Select?*?From?tbPayMode?Where?CDelFlag='N')
????
????
'
???步驟一:
????
'
???????拼房東資料表頭:房東．聯(lián)絡(luò)人．聯(lián)絡(luò)人電話．租金給付方式
????
'
用於外部連接的職場ID
????strUnionSql1?
=
?
"
Select?tbFinalUnion.CPlaceCode?As?CPlaceCode
"
????
'
房東
????strUnionSql1?
=
?strUnionSql1?
&
?
"
,tbOwner.COwner?AS?COwner
"
????
'
聯(lián)絡(luò)人
????strUnionSql1?
=
?strUnionSql1?
&
?
"
,tbOwner.CLinkman?AS?CLinkman
"
????
'
聯(lián)絡(luò)人電話
????strUnionSql1?
=
?strUnionSql1?
&
?
"
,tbOwner.CLinkTel?AS?CLinkmanTel
"
????
'
租金給付方式
????strUnionSql1?
=
?strUnionSql1?
&
?
"
,tbPayMode.CModeName?AS?CRentPayType
"
????
????
'
???步驟二:
????
'
???????表頭掛接?From,房東記錄集需要?From?的表:tbContractOwner/tbOwner/tbPayMode/tbFinalUnion最終結(jié)果集表(閒置?Union?待退)
????strUnionSql1?
=
?strUnionSql1?
&
?
"
?From?((
"
?
&
?strFinalUnion_TB
????strUnionSql1?
=
?strUnionSql1?
&
?
"
?Join?
"
?
&
?strContractOwner_TB?
&
?
"
?On?tbFinalUnion.CContractid=tbContractOwner.CContractid)
"
????strUnionSql1?
=
?strUnionSql1?
&
?
"
?Join?
"
?
&
?strOwner_TB?
&
?
"
?On?tbContractOwner.COwnerid=tbOwner.COwnerid)
"
????strUnionSql1?
=
?strUnionSql1?
&
?
"
?Join?
"
?
&
?strPayMode_TB?
&
?
"
?On?tbOwner.CModeCode=tbPayMode.CModeCode
"
????
????
'
???步驟三:
????
'
???????按?CPlaceCode?Asc,COwner?排序
????strUnionSql1?
=
?strUnionSql1?
&
?
"
?Order?By?CPlaceCode?Asc,COwner
"
????
????
'
???SQL拼裝邏輯全部完成:
????
'
???????記錄條件SQL,用於外部調(diào)試
????m_strFinalUnionSql?
=
?strFinalUnion_TB
????m_strResult1Sql?
=
?strUnionSql
????m_strResult2Sql?
=
?strUnionSql1
????
????
'
*********************************************
????
'
代入考察期參數(shù)
????
'
???處理輸入?yún)?shù)
????
Dim
?strDate?
As
?
String
,?strStartDate?
As
?
String
,?strEndDate?
As
?
String
,?datDate
????strDate?
=
?strYear?
&
?
"
-
"
?
&
?strMonth?
&
?
"
-
"
?
&
?
"
01
"
?????
'
2006-01-01
????datDate?
=
?
CDate
(strDate)
????
????strDate?
=
?Format(datDate,?
"
yyyymm
"
)
????strStartDate?
=
?strDate?
&
?
"
01
"
????strEndDate?
=
?Format(
DateAdd
(
"
d
"
,?
-
1
,?
DateAdd
(
"
m
"
,?
1
,?datDate)),?
"
yyyymmdd
"
)
????
'
???代入輸入?yún)?shù)
????strUnionSql?
=
?
Replace
(strUnionSql,?
"
@Date6
"
,?
"
'
"
?
&
?strDate?
&
?
"
'
"
)
????strUnionSql?
=
?
Replace
(strUnionSql,?
"
@StartDate8
"
,?
"
'
"
?
&
?strStartDate?
&
?
"
'
"
)
????strUnionSql?
=
?
Replace
(strUnionSql,?
"
@EndDate8
"
,?
"
'
"
?
&
?strEndDate?
&
?
"
'
"
)
????strUnionSql1?
=
?
Replace
(strUnionSql1,?
"
@Date6
"
,?
"
'
"
?
&
?strDate?
&
?
"
'
"
)
????strUnionSql1?
=
?
Replace
(strUnionSql1,?
"
@StartDate8
"
,?
"
'
"
?
&
?strStartDate?
&
?
"
'
"
)
????strUnionSql1?
=
?
Replace
(strUnionSql1,?
"
@EndDate8
"
,?
"
'
"
?
&
?strEndDate?
&
?
"
'
"
)
????
????
????
'
查詢並設(shè)置傳出值
????
'
rsPlace?=?strUnionSql
????
'
rsOwner?=?strUnionSql1
????
????
If
?objADO.QueryData(strUnionSql,?rsPlace)?
=
?
False
?
Then
?
GoTo
?ErrHandler
????
If
?objADO.QueryData(strUnionSql1,?rsOwner)?
=
?
False
?
Then
?
GoTo
?ErrHandler
????
????fGetFreePlace?
=
?
True
????
Exit
?
Function
ErrHandler:
????fGetFreePlace?
=
?
False
????objCommon.WriteErrLog?TheMdlName,?
"
fGetFreePlace
"
,?fGetFreePlace,?Err.Number,?Err.Description
End?Function
'
用於調(diào)試用的SQL
Public
?
Function
?getFreeSql(ByRef?strFreeSql?
As
?Variant)?
As
?
Boolean
????strFreeSql?
=
?m_strFreeSql
????getFreeSql?
=
?
True
End?Function
Public
?
Function
?getEndSql(ByRef?strEndSql?
As
?Variant)?
As
?
Boolean
????strEndSql?
=
?m_strEndSql
????getEndSql?
=
?
True
End?Function
Public
?
Function
?getFinalUnionSql(ByRef?strFinalUnionSql?
As
?Variant)?
As
?
Boolean
????strFinalUnionSql?
=
?m_strFinalUnionSql
????getFinalUnionSql?
=
?
True
End?Function
Public
?
Function
?getResult1Sql(ByRef?strResult1Sql?
As
?Variant)?
As
?
Boolean
????strResult1Sql?
=
?m_strResult1Sql
????getResult1Sql?
=
?
True
End?Function
Public
?
Function
?getResult2Sql(ByRef?strResult2Sql?
As
?Variant)?
As
?
Boolean
????strResult2Sql?
=
?m_strResult2Sql
????getResult2Sql?
=
?
True
End?Function
??????看這段代碼需要的是耐心，順著邏輯一步步地往下走才行。
??????代碼中對基礎(chǔ)表的封裝，可以看 str[表名]_TB 這樣命名的變量，它將一張表封裝起來，然後重要的一點(diǎn)就是逐層的表命名，這裡使用了原表的名稱，雖然看起來有些混亂，但是這是調(diào)試所必需的。?
??????後記：
?????????這一次的嚐試，在時間上消耗比較大，開始時對於結(jié)果的正確性還有著一絲懷疑，不過在完工後也就釋然了，只有一處比較小的筆誤進(jìn)行過調(diào)試，算是對這次的結(jié)構(gòu)上的一點(diǎn)肯定吧，嘻嘻。
?????????哎，不過測試時還是遇到了令人沮喪的事情，這次測試MM最終對我說任務(wù)很忙，俺的測試報告自己寫就好，咳咳。
?????????如果有仔細(xì)看完上面的SQL源碼，不難看出除了在結(jié)構(gòu)上完成了表及表間邏輯的封裝外，而且將最終形成的視圖邏輯也進(jìn)行了封裝，因此在ASP視圖中僅僅需要完成循環(huán)顯示即可，這除了簡化ASP視圖外，還對後面幾次的需求變動造成了很深的影響，決定了日後變動的走向。