買賣記錄檔內的訂單合計數量加總錯誤-急

買賣記錄檔內的訂單合計數量加總錯誤
1. 請修改訂單數量顯示方式
2. 修正訂單數量合計
買賣記錄

查到此版面為 bigbeardwo.pbl 的 d_que_sc_item_all2

如果是單純的買賣價紀錄,其實只需秀出訂單資料即可,因為此版面有船務人員也要查看,所以此版面還納入船務責任區的INVOICE出貨資料,而訂單有分批導入INVOICE的作業,為了讓操作者可看到分批出貨資料,必須考慮訂單與INVOICE的連結所產生的以下情況

1.有訂單但尚未有INVOICE
2.有訂單只有一筆INVOICE且訂單數量已出完
3.有訂單且只有一筆INVOICE但訂單數量仍尚未出完,有Balance Nunber
4.有訂單且不只有一筆INVOICE但訂單數量仍尚未出完,有Balance Nunber
5.有訂單只不只有一筆INVOICE但訂單數量已出完,有Balance Nunber
6.有訂單只不只有一筆INVOICE但訂單數量尚未出完但訂單已結案,沒有Balance Nunber,倒是有訂單殘量

問題出在當單一筆訂單對映多筆Invoice出貨時,版面資料量會與Invoice同步,但訂單數量還是只能秀一次,但因為Invoice為多筆,即連帶會秀出多筆訂單資料,且訂單數量都是一樣的情況,會讓人誤以為多筆訂單明細,且版面自己的訂單數量統計數據也一樣產生重複計算的問題了.

不能用PB的Rows->Suppress repeating Values來隱藏重複[訂單數量]資料的顯示,因為是可以達到數量重複不要秀出的問題,因為看不到不代表沒資料,底下的合計數字一樣是錯的,而且游標點到此隱藏欄位,該欄位值也會隨即出現.

版面欄位的底色
由Property->Font->Background Color 統一為以下設定
if( not isnull(ordertop_closedate),12639424, if (num=invoice_num2,29753063,if(currentrow()=getrow(),23325158,16777215)))
底色運算式解釋為
如果 訂單已結案 則
　綠色
否則
　如果 訂單數量=訂單檔記錄的已入Invoice 數量 則
　　　薄綠色
　否則
　　　　如果游標所在　則
　　　　　　淺黃色
　　　　否則
　　　　　　白色
End IF
說明:訂單已結案是由訂單結案日期是否被填入來判斷的,只要訂單結案日期被填入就視同該訂單已結案.
如果有殘量,則設計 Balance Nunber欄位以紅字秀出以提醒操作者.

版面的效能
此版面用訂單與Invoice 採 Left Join 的方式,效能不是很好,後來針對Invoice_item加了Index後效率整個改觀
加Index指令如下,填在database_correct.ini傳送到客戶端由客戶端MIS執行
CREATE INDEX orderseq ON invoice_item (orderno, scseq);

此索引主要是將Invoice_item與Order_item作連結,當初規劃應該是Invoice_item建 Foreign key 連向Order_item,讓Invoice_item的 orderno,scseq 與Order_item的Orderno,seq 相連結.也可以確保Invoice_item一定會來自Order_item,保持資料的一致性Integity.
只是資料量一大,要回頭建Foreign Key有時就會面對原資料已經不一致的問題.

下圖為買賣價瀏覽畫面.

單一筆訂單明細被多筆 Invoice 辦出貨,這些 Invoice item 並不會是放在同一張Invoice單據中,也就是說訂單對映的是多張Invoice,版面需要判斷單一訂單明細蒐集了多張
Invoice item以後,只選擇其中一張秀出 [原訂單數量],避免每一筆Invoice item都秀出 [原訂單數量].

問題就出在在多筆對映的Invoice item中,怎找出其中一筆來秀 [原訂單數量],而該筆被認是適當的.
1.依日期來比,最早的一筆秀[原訂單數量]就好.但日期需要做索引,不然搜尋會慢.
2.依單號順序來比,最小的一筆秀[原訂單數量]就好.但單號如果是人工可編的,是否會有與日期先後不一致的問題發生?


採用上述第二種做法的前提,是同一個訂單產品不會出現在同一張Invoice單上兩次(含)以上.
在版面的SQL中加compute column欄位,取得每筆訂單明細最小的Invoice單號.
(select min(invoice_no) from invoice_item where orderno=order_item.orderno and scseq=order_item.seq) as min_invoiceno

針對版面增加一個compute field,當有訂單沒有Invoice時,或有Invoice但該Invoice單號為最小的單號時,=訂單數量,否則訂單數量填0.
if(min_invoiceno = invoice_no or isnull( min_invoiceno ),num,0)

這樣版面查詢同一單筆訂單明細碰到重複對映的多筆Invoice時,只會秀訂單數量 一次.

排版後查詢畫面如下,但發現單筆訂單明細碰到分散重複的Invoice,游標剛好停在看不到訂單數量的那一筆上面時,會讓操作者感到不妥,因為他可能又想看到原屬訂單數量,雖然他知道這是分批中的第二批以上.所以還需再想辦法改版面以符合操作者期待.

此版面用到好幾個Table,而且有用到Left Join的關聯設定,需注意效率問題,其中的排序經實驗讀取數萬筆資料列後,發現在[資料庫後端排列好再送到前台顯示]所花的時間,是[先送到前台再排序]的近百倍.


有關版面排序的設定方法請參考.
http://idon.666forum.net/forum-f12/topic-t105.htm

與 [原訂單數量] 相同要處理的是 [Balance number]=尚未出貨完畢的量,單筆訂單一樣不能隨多筆Invoice重複秀出,只能秀一次.

在SQL端的Balancenum為下列compute運算式
(order_item.num - order_item.invoice_num) as balancenum

但到前台要顯示的Balance number卻需要判讀min_invoiceno最小Invoice單號,其運算式compute Expression為
if(min_invoiceno = invoice_no or isnull( min_invoiceno ), balancenum ,0)

Balance num來自訂單明細檔的計算式,取訂單明細檔的訂單數量 減去 訂單明細檔的已入Invoice數量,已入Invoice數量並不是訂單拋給Invoice後就回寫回來的,而是由輸入Invoice主檔的結案日期並存檔所觸發Trigger回寫的.

更新後的程式已修改了訂單數量合計錯誤問題
但顯示方式需要修改
買賣記錄

就像第四篇預期的一樣,操作者有所疑慮

重複的數量是不見了,但操作者希望重複的不見沒錯,但游標過去要能看到該數量,對於系統版面的框架而言,看得到數量就代表該筆資料真的有數量,這樣又會造成數量統計上的錯誤.

針對重複數量問題,仍需解決
1.游標要出現就不能在Datawindow編排階段用Compute欄位,因為針對這種欄位,Powerbuilder是固定不讓游標進去的.
2.游標不在該欄位,金額數字不見且金額=0,游標點下去,金額數字出現了,但實際該欄位金額還是0.

首先解決游標出不來的問題

原先以前台處理 datawindow版面編排的訂單數量 compute欄位形式,改以後台 SQL端compute欄位形式處理

針對訂單數量部份
版面compute欄位計算式原為
if(min_invoiceno = invoice_no or isnull( min_invoiceno ),num,0),刪除之

改在SQL端加列不會重複的訂單數量 compute column欄位
(case when min_invoiceno = invoice.invoice_no or min_invoiceno is null then order_item.num else 0 end) as ordernum,
此compute欄位只用來提供合計訂單數量用,並不秀出於版面,真正秀出於版面的訂單數量為 order_item.num,欄位名稱 num.

---

針對balance number 部分
版面compute欄位計算式原為,直接刪除不用
if(min_invoiceno = invoice_no or isnull( min_invoiceno ), balancenum ,0)

原SQL端為 (order_item.num - order_item.invoice_num) as balancenum
改成有條件判斷式
(case when min_invoiceno = invoice.invoice_no or min_invoiceno is null then order_item.num - order_item.invoice_num else 0 end) as balancenum
Balance沒有重複問題,所以合計也是直接採用,計算式=sun(balancenum for all)

---

下圖說明,兩個 訂單數量 num 與 ordernum 欄位都是來自SQL compute 真實欄位,所以游標進的去,但ordernum 碰到重複的會出現0,操作上會有疑慮,所以將之由版面移除,但實際上還在,只是隱藏起來.
而num欄位因為被設定在datawindow 功能表 Rows->Supress repeating values中,所以碰到重複的數字會隱藏,但游標點進去卻有可出現



下圖訂單數量欄位剩 num 這組,但其合計數卻來自 sum(ordernum for all),此圖示範兩組數字,其中一組游標有進去,另一組沒有,可看出系統對重複資料隱藏與顯示的動作.

買賣記錄版面已更改ok
但excel report 出現奇怪欄位且資料顯示為 ! & 錯誤訊息(有幾筆資料就會出現幾次)

多欄位
錯誤訊息

一產品若只有單一交運日，但分次出貨 及 有二個以上交運日出貨，的顯示方應該要不一樣

畫面顯示