@ CASE < 採購單尚未驗收項目提醒 > ver.20110919

'需要轉EXCEL

'採購單號,驗收物料,品茗,數量(packqty * acptqty),廠商 (supplierID)

==================================================

'發現說自訂控制項dll
宣告物件以後
基本上他事件依然是跟著 class
而不是跟著物件
變成同一個class定義兩個物件
沒辦法依照物件寫事件

應該可以
只是目前不知道
要再查查看

透過加入辨別控制向名稱解決




'加入廠商ID,日期區間
原本日期 改單一日期

'加入預期收貨日期

'萬用輸入


'需要一隻可以查詢 採購單上 已經開立驗收單(作廢不計)
尚未實際驗收的資料

採購單收貨日已到(consdate),尚未實際驗收開單的資料

'加入單一日期篩選

採購單(確認,核可,最新)找收貨日小於今天的,細項找尚未結案的 (isclosed = 0)
驗收單(最新,確認,0 or 2)
@找採購單不會出現在驗收單上的

所以需要結合兩個單子
透過 right join
找到 不存在 chkacptdet 上面 的 pono

[MySQL]left, right, inner, outer join 使用方法

http://blog.wu-boy.com/2009/01/mysqlleft-right-inner-outer-join-%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95/



select a.chkacptno, b.* from tblchkacptdet a right join tblpodet b
on a.pono = b.pono  and a.batchno = b.batchno and a.partcode = b.partcode
where a.chkacptno is null and consdate <= 20110920


============================================



select ac.chkacptno, po.pono as 採購單號, po.partcode as 物料編號, po.qty as 採購數, po.packqty as 單位量, po.採購總量 ,po.supplierID as 供應商ID
from
(select a.chkacptno, b.pono, b.partcode, b.batchno, max(a.formversion) as formversion
from tblchkacpt a join tblchkacptdet b on a.chkacptno = b.chkacptno
where not a.apvddate is null and ( a.status = 2 or a.status = 0 )
group by a.chkacptno, b.pono, b.partcode, b.batchno, b.packqty, b.acptqty) as ac
right join
(select c.pono, d.partcode, d.packqty, d.qty, d.packqty * d.qty as 採購總量, c.supplierID, d.batchno, max(c.formversion) as formversion
from tblpo c join tblpodet d on c.pono = d.pono
where c.status = 2 and (not c.apvddate is null)
and d.consdate <= 20110920
group by c.pono, c.supplierid, d.partcode, d.batchno, d.packqty, d.qty) as po
on
(ac.pono = po.pono and ac.batchno = po.batchno and ac.partcode = po.partcode)
where ac.chkacptno is null



===========================================
[tblchkacptdet ]  partcode batchno pono [tblpodet]

[tblchkacpt] apvddate not is null and  status <> 9 , max(formversion)

[tblpo] apvddate not is null and  status = 2 , max(formversion)