# E28
###### tags: `已處理`
## 問題敘述
N110-26契約第4批已完成驗收，請款單資料產生(MR30409)出現「該標項、批次
尚未完成驗收手續」，無法辦理請款手續，敬請查修。(三區物料課反映)
[E28](https://docs.google.com/document/d/1hdbGk5BuwvChER8lDuw7r4mRqFvrgzdt/edit?usp=sharing&ouid=107825791787894292764&rtpof=true&sd=true)
:::spoiler
## 解析
https://10.100.50.20:9080/mr/mr30302_lis.jsp?s_BNOH_CSEQ=26&e_BNOH_CHSEQ=&s_BNOH_SNO=&s_BNOH_CHYY=&s_BNOH_SNAME=&s_BNOH_MKYY=&s_BNOH_ANO=03&s_BNOH_CHSEQ=&s_BNOH_STATUS=&s_BNOH_CYY=110&e_BNOH_MKSEQ=&e_BNOH_CSEQ=26&s_BNOH_MKSEQ=
https://10.100.50.20:9080/mr/mr30409_man.jsp?PNOH_ANO=03&PNOH_CSEQ1=03&PNOH_ANO1=03&PNOH_CYY=110&s_PNOH_DEL=&s_PNOH_PMYY=111&PNOH_CSEQ=26&PNOH_PMYY=111&PNOH_PMSEQ=108&s_PNOH_PMDATE=&s_PNOH_ANO=03&PNOH_CCODE=N&s_PNOH_PMSEQ=108&PNOH_PMKIND=P&PNOH_SEQ=1&PNOH_DEL=0
https://10.100.50.20:9080/mr/mr30409_man.jsp?ccsForm=MAUR_PNOM&PNOH_PMSEQ=108&s_PNOH_PMYY=111&PNOH_CSEQ=26&PNOH_PMYY=111&PNOH_ANO1=03&PNOH_DEL=0&PNOH_SEQ=1&PNOH_ANO=03&s_PNOH_DEL=&PNOH_CCODE=N&PNOH_CSEQ1=03&PNOH_CYY=110&s_PNOH_ANO=03&s_PNOH_PMDATE=&PNOH_PMKIND=P&s_PNOH_PMSEQ=108
https://10.100.50.20:9080/mr/mr30302_man.jsp?s_BNOH_MKYY=&s_BNOH_CYY=110&s_BNOH_MKSEQ=&s_BNOH_STATUS=&s_BNOH_CHSEQ=&s_BNOH_SNAME=&BNOH_CHDATE=1110704&e_BNOH_MKSEQ=&BNOH_CHSEQ=7899&BNOH_CHYY=111&e_BNOH_CSEQ=26&s_BNOH_CHYY=&e_BNOH_CHSEQ=&s_BNOH_CSEQ=26&BNOH_CHKIND=B&BNOH_ANO=03&s_BNOH_ANO=03&s_BNOH_SNO=
https://10.100.50.20:9080/mr/mr30409_lis.jsp?s_PNOH_PMYY=111&s_PNOH_DEL=&s_PNOH_ANO=03&s_PNOH_PMDATE=&s_PNOH_PMSEQ=108
:::
## 問題單內容
* 原始程式碼位置
* 目的程式碼位置
* 問題解析
* 處理方法
* 變更後測試執行結果
## 討論
先定位該錯誤訊息拋出的位置再開始解析原因
mr30409_manHandlers.jsp
先把那一段計算數量的 sql 兜出來
理論上要什麼數字、實際上是什麼數字
分析造成這兩個落差的原因
修正
提示：可以直接打開 CCLogger 去查看 DBTools.dLookUp 這個動作留下的 sql 語法
你會需要這樣推導
```java
// line 952 //項次起訖為空
pnom_item_count = Utils.convertToLong(DBTools.dLookUp("NVL(COUNT(*), 0)", tableName, whereCondition2, connectionName)).intValue();
// line 948 //項次起訖不為空
pnom_item_count = Integer.parseInt(pnom_item_e) - Integer.parseInt(pnom_item_s) + 1;
// line 865-871
//項次_起[PNOM_ITEM_S]
Control cntrl_pnom_item_s = (Control)rowData.get("PNOM_ITEM_S");
String pnom_item_s = Utils.convertToString(cntrl_pnom_item_s.getValue());
//項次_迄[PNOM_ITEM_E]
Control cntrl_pnom_item_e = (Control)rowData.get("PNOM_ITEM_E");
String pnom_item_e = Utils.convertToString(cntrl_pnom_item_e.getValue());
```
我有去撈該筆資料的table & sql
## Mr30409
```sql
SELECT * FROM MAUR_PNOH
WHERE
PNOH_PMYY=111
AND PNOH_DEL= 0
AND PNOH_ANO=03
--AND PNOH_PMDATE=
AND PNOH_PMSEQ=108
--CDVD_ANO='0D' AND CDVD_CCODE='N' AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D'
;
```
## Mr30409
```sql
SELECT * FROM MAUR_BNOH
WHERE
BNOH_CYY=110
AND BNOH_CHDATE=1110704
AND BNOH_CHSEQ=7899
AND BNOH_CHYY=111
AND BNOH_CSEQ=26
AND BNOH_CHKIND = 'B'
AND BNOH_ANO = '03'
```
> 你先跟著程式邏輯走，把完整的 sql 組合出來
> 例如說我需要請款種類 PNOM_TYPE 去判斷sql 是要從 line 907-914 或是 line 916-921 來，條件要加 line 936 或是 938
好~
```sql
-- 已完成交貨手續的數量
-- 查詢一次，然後再把 AND BNOH_CHDATE IS NOT NULL 註解掉看看有什麼差別
SELECT NVL(COUNT(*), 0)
FROM MAUR_BNOH, MAUR_BNOB
WHERE
(BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE) AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ) AND (BNOH_CSEQ1 = BNOB_CSEQ1)
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ
AND BNOH_DEL = '0' AND BNOH_STATUS = '1'
AND BNOB_ANO1 = :PNOH_ANO1 AND BNOB_CCODE = :PNOH_CCODE AND BNOB_CYY = :PNOH_CYY AND BNOB_CSEQ = :PNOH_CSEQ AND BNOB_CSEQ1 = :PNOH_CSEQ1
AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM =:PNOM_PITEM
AND BNOH_CHDATE IS NOT NULL
-- pnom_item_count =
-- 查這個的值
SELECT NVL(COUNT(*), 0)
FROM MAUR_BNOH, MAUR_BNOB
WHERE (BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE) AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ) AND (BNOH_CSEQ1 = BNOB_CSEQ1) AND BNOH_DEL = '0'
AND BNOB_ANO1 = :PNOH_ANO1 AND BNOB_CCODE = :PNOH_CCODE AND BNOB_CYY = :PNOH_CYY AND BNOB_CSEQ = :PNOH_CSEQ AND BNOB_CSEQ1 = :PNOH_CSEQ1 AND BNOB_DEL = '0' AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM = :PNOM_PITEM
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ
```
我剛有拿掉過 但結果蠻怪的
阿不是拉 PNOM那些我要帶多少XDD
那要去CCloger撈 這筆資料的參數 ㄇ?
好 我去撈一下
辛苦ㄌ
> 多一個; 修正再丟
> 你把參數值都要填上
> 你要從 30409 查 你看 xml 下面的 select 語法
最快的就是從 logger 直接拿他查的語法
不用 logger 就是要照條件推導
我不在現場只能做苦工
mr30409_manHandlers.jsp上 900行那邊 放ccLoger 紀錄嗎?
> 有些 sql 他原本就會 log 出來，沒有的話你也可以放
> 我記得 DBLookup 的都會 log
有 我找到ㄌ
```sql
SELECT NVL(COUNT(*), 0)
FROM MAUR_BNOH, MAUR_BNOB
WHERE (BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE)
AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ)
AND (BNOH_CSEQ1 = BNOB_CSEQ1) AND BNOH_DEL = '0'
AND BNOH_DEL = '0' AND BNOH_STATUS = '1'
AND BNOB_ANO1 = '03' AND BNOB_CCODE = 'N'
AND BNOB_CYY = 110 AND BNOB_CSEQ = 26 AND BNOB_CSEQ1 = 03
AND BNOB_DEL = '0'
--AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM = :PNOM_PITEM
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ
```
(兩段結果 都是 33)
(select* 時有找到 7899的資料)
table MAUR_PNOM 沒資料
```sql
SELECT *
FROM MAUR_PNOM
WHERE PNOM_ANO = '03'
AND PNOM_KIND = 'P'
AND PNOM_PMYY = 111 ㄎ
--AND PNOM_PMSEQ = 108
--AND PNOM_DEL
```
(沒有108)
但table MAUR_PNOH 有資料
```sql
SELECT *
FROM MAUR_PNOH
WHERE PNOH_ANO like '03%' AND PNOH_ANO = '03'
AND PNOH_PMKIND = 'P' AND PNOH_PMYY = 111
AND PNOH_PMSEQ = 108
```
> AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM = :PNOM_PITEM
> 不能註解，你看 jsp 可以知道分別代表標項、批次
> 要帶入 1 , 4 查
這個數字是12
```sql
SELECT*
--NVL(COUNT(*), 0)
FROM MAUR_BNOH, MAUR_BNOB
WHERE (BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE)
AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ)
AND (BNOH_CSEQ1 = BNOB_CSEQ1) AND BNOH_DEL = '0'
AND BNOH_DEL = '0' AND BNOH_STATUS = '1'
AND BNOB_ANO1 = '03' AND BNOB_CCODE = 'N'
AND BNOB_CYY = 110 AND BNOB_CSEQ = 26 AND BNOB_CSEQ1 = 03
AND BNOB_DEL = '0'
AND BNOB_BITEM = 1 AND BNOB_PITEM = 4
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ
```
```sql
DISABLE TRIGGER ALL ON MAUR_PNOM;
UPDATE MAUR_PNOM SET PNOM_PITEM= 4
WHERE PNOM_ANO = '03' AND PNOM_ANO = '03'
--AND PNOM_PMKIND = 'P'
AND PNOM_PMYY = 111
AND PNOM_PMSEQ = 108 ;
ENABLE TRIGGER ALL ON MAUR_PNOM;
SELECT * FROM MAUR_PNOM
WHERE PNOM_ANO = '03' AND PNOM_ANO = '03'
--AND PNOM_PMKIND = 'P' AND PNOM_PMYY = 111
--WHERE PNOM_ANO like '03'
----AND PNOM_KIND = 'P'
AND PNOM_PMYY = 111 AND PNOM_PMSEQ = 108
----AND PNOM_DEL = '0'
ORDER BY PNOM_SEQ;
```
7/21
已修正
Sign in with Wallet
Connect another wallet