BOM表查詢的VB實現(xiàn)方法

2024-07-21 02:20:29

字體：大中小

來源：轉載

供稿：網(wǎng)友

相關需求及信息請點擊這里查看。

用vb代碼實現(xiàn)方法

引用：無，部件：無

設計：在form1中右下角加入一個commandbutton，名稱默認為command1，窗體的autoredraw屬性設為true

窗體文件一：form1

option explicit

private mbom as collection              '這是入口的集合
private mbomreturn as collection        '這是出口的集合，未經(jīng)處理
private mbomreturnlast as collection    '這是出口的集合，經(jīng)過處理

private sub addbomrecord()
'在這里往mbom加入數(shù)據(jù)庫里面的原內容，為求簡便，我不想連接數(shù)據(jù)庫
'直接往里面寫入記錄了，如果需要，你就直接連接數(shù)據(jù)庫，分析一下里面的
'代碼，然后再往mbom里面寫入記錄
'fg      sa1     2.0000
'fg      sa2     3.0000
'sa1     pt1     4.0000
'sa1     pt2     5.0000
'sa2     pt1     6.0000
'sa2     pt3     7.0000

dim mbomvalue as cbomvalue

set mbomvalue = new cbomvalue

mbomvalue.assbom = "fg"
mbomvalue.bompoint = "sa1"
mbomvalue.quantity = 2

mbom.add mbomvalue

set mbomvalue = new cbomvalue

mbomvalue.assbom = "fg"
mbomvalue.bompoint = "sa2"
mbomvalue.quantity = 3

mbom.add mbomvalue

set mbomvalue = new cbomvalue

mbomvalue.assbom = "sa1"
mbomvalue.bompoint = "pt1"
mbomvalue.quantity = 4

mbom.add mbomvalue

set mbomvalue = new cbomvalue

mbomvalue.assbom = "sa1"
mbomvalue.bompoint = "pt2"
mbomvalue.quantity = 5

mbom.add mbomvalue

set mbomvalue = new cbomvalue

mbomvalue.assbom = "sa2"
mbomvalue.bompoint = "pt1"
mbomvalue.quantity = 6

mbom.add mbomvalue

set mbomvalue = new cbomvalue

mbomvalue.assbom = "sa2"
mbomvalue.bompoint = "pt3"
mbomvalue.quantity = 7

mbom.add mbomvalue

end sub

private sub command1_click()

dim i as integer
dim m as cbomvalue

'進行計算

'注意以下兩個新建實例，必須放置于getbomlist前，該操作也有清空現(xiàn)有數(shù)據(jù)的作用，否則會造成錯誤
'即第一次運行后保存了數(shù)據(jù)于該兩個變量中，并未清除相關記錄，而下一次運行則在現(xiàn)有的基礎上再進行加操作，因此數(shù)據(jù)錯誤了。

set mbomreturn = new collection
set mbomreturnlast = new collection

call getbomlist

'計算后，mbomreturnlast返回的就是最終結果
if mbomreturnlast.count < 0 then
    msgbox "沒有記錄！", vbinformation + vbokonly, "bom表計算"
    exit sub
else

    '在窗體中打印出列表的內容
    me.cls

    print "assbom" & vbtab & "point" & vbtab & "quantity"

    for i = 1 to mbomreturnlast.count
        set m = mbomreturnlast.item(i)

        print m.assbom & vbtab & m.bompoint & vbtab & m.quantity
    next i
end if

end sub

private sub form_load()

'窗體調用處新建實例，然后再裝入數(shù)據(jù)

set mbom = new collection

addbomrecord

end sub

'***************************************************************
'*
'* 以下為進行計算部分的代碼，注意collection里面的處理
'*
'***************************************************************

private sub getbomlist()
    dim mbomtop as collection       '這里保存了頂級產(chǎn)成品
    dim i as integer
    dim j as integer
    dim m as cbomreturnvalue
    dim mlast as cbomvalue
    dim bfind as boolean



    set mbomtop = new collection


    '裝入頂級產(chǎn)成品

    loadbomtop mbomtop

    '對頂級產(chǎn)品進行下級的判斷

    for i = 1 to mbomtop.count
        '最后一個參數(shù)為1，表示一個單位的產(chǎn)成品
        call calcnextbom(mbomtop.item(i), mbomtop.item(i), "1")
    next i


    '最終得以mbomreturn，這里面已初步形成了結果了

    '再進行表達式計算，得到的值返回到mbomreturnlast中，注：mbomreturnlast這個集合加入cbomvalue內容


    for i = 1 to mbomreturn.count
        '處理一下最終結果，如果沒有在collection里面發(fā)現(xiàn)相同的assbom及bompoint，則新增加一個，如果已發(fā)現(xiàn)，僅只是數(shù)量相加
        set m = mbomreturn(i)

        '查找是否已加入
        bfind = false
        for j = 1 to mbomreturnlast.count
            set mlast = mbomreturnlast(j)

            if trim(mlast.assbom) = trim(m.assbom) and trim(mlast.bompoint) = trim(m.bompoint) then
                '如果發(fā)現(xiàn)有相同的，則加入相關數(shù)字
                mlast.quantity = mlast.quantity + calcexpression(m.expression)
                bfind = true
            end if

        next j

        if bfind = false then
            '如果沒有找到
            set mlast = new cbomvalue
            mlast.assbom = trim(m.assbom)
            mlast.bompoint = trim(m.bompoint)
            mlast.quantity = calcexpression(trim(m.expression))

            mbomreturnlast.add mlast

        end if
    next i

    '所有操作完畢
end sub

private sub loadbomtop(byref bomtop as collection)
    '裝入頂級產(chǎn)成品，并返回到bomtop中
    '即存儲過程中getbomlist中的第一個游標的創(chuàng)建@bomtop

    dim i as integer
    dim j as integer
    dim n as integer


    dim bmark as boolean    '這只是一個標識符，表明是否發(fā)現(xiàn)非頂級
    dim bmarkadd as boolean '用于判斷是否已加入到bomtop中的標識


    '判斷方法，如果assbom不在bompoint中，那就是頂級了
    dim sbomassbom as string

    for i = 1 to mbom.count
        sbomassbom = trim(mbom.item(i).assbom)

        '再進行循環(huán)
        bmark = false

        for j = 1 to mbom.count
            if sbomassbom = trim(mbom.item(j).bompoint) then
                bmark = true
            end if
        next j

        if bmark = false then
            '如果沒有發(fā)現(xiàn)有相同的，則bomtop加入

            '加入前需要進行判斷是否已加入

            for n = 1 to bomtop.count
                if bomtop.item(n) = sbomassbom then
                    bmarkadd = true
                end if
            next n

            if bmarkadd = false then
                '如果沒有加入過，則加入
                bomtop.add sbomassbom
            end if
        end if
    next i


end sub

'getbomtruelist的存儲過程用vb來描述
private sub calcnextbom(sassbom as string, sasspoint as string, sexp as string)
    dim dquan as double
    dim sexpression as string
    dim spoint as string

    dim bomtop as string

    '創(chuàng)建point_cursor處的游標
    dim mbompoint as collection

    set mbompoint = new collection


    '裝入相關的集合
    call loadnextpoint(mbompoint, sasspoint)

    '裝入完畢后，再進行判斷是否為明細級半成品，如果不是，遞歸一次本函數(shù)，如果是，加入到mbomreturn里面去

    dim i as integer
    dim mbomreturnvalue as cbomreturnvalue

    for i = 1 to mbompoint.count
        '判斷是否為明細級
        if isdetailpoint(trim(mbompoint.item(i).bompoint)) = true then
            '如果是明細級，則加入到cbomreturnvalue
            set mbomreturnvalue = new cbomreturnvalue
            mbomreturnvalue.assbom = trim(sassbom)
            mbomreturnvalue.bompoint = trim(mbompoint.item(i).bompoint)
            '構建表達式
            mbomreturnvalue.expression = sexp & "*" & trim(cstr(mbompoint.item(i).quantity))

            mbomreturnvalue.quantity = mbompoint.item(i).quantity


            '加入
            mbomreturn.add mbomreturnvalue

        else

            '如果不是明細項，則再次遞歸，注意傳入的第一個參數(shù)，總是頂級bom，僅作標識符用
            call calcnextbom(sassbom, trim(mbompoint.item(i).bompoint), sexp & "*" & trim(cstr(mbompoint.item(i).quantity)))
        end if

    next i



end sub

private sub loadnextpoint(byref bompoint as collection, byval pointname as string)
'相當于getbomtruelist中的游標中的select distinct point,sl from te where assbom = @pointname

    dim i as integer
    dim j as integer

    dim bmark as boolean
    dim mpointvalue as cpointvalue

    for i = 1 to mbom.count
        bmark = false
        if trim(mbom.item(i).assbom) = trim(pointname) then
            '判斷是否已加入
            for j = 1 to bompoint.count
                if trim(bompoint.item(j).bompoint) = trim(mbom.item(i).bompoint) and bompoint.item(j).quantity = mbom.item(i).quantity then
                    bmark = true
                end if
            next j
            if bmark = false then
                '表示沒有加入
                set mpointvalue = new cpointvalue
                mpointvalue.bompoint = trim(mbom.item(i).bompoint)
                mpointvalue.quantity = mbom.item(i).quantity
                bompoint.add mpointvalue
            end if

        end if
    next i



end sub

private function isdetailpoint(byval pointname as string) as boolean
'判斷是否為底級半成品

    '只需要判斷pointname不在mbom的assbom項中即可

    dim i as integer

    for i = 1 to mbom.count
        if trim(mbom.item(i).assbom) = trim(pointname) then
            '如果找到了，直接返回false，并退出函數(shù)
            isdetailpoint = false
            exit function
        end if
    next i

    '如果到了這里還沒有找到，那么就肯定是底級了
    isdetailpoint = true
end function

public function calcexpression(strexp as string) as double
'計算處理中的表達式，注意，只有乘法

dim sitemexp() as string

dim dreturnvalue as double
dim iindex as integer

sitemexp = split(trim(strexp), "*")

if ubound(sitemexp) < 0 then
    calcexpression = 0
else

    dreturnvalue = 1
    for iindex = 0 to ubound(sitemexp)
        if trim(sitemexp(iindex)) = "" then
            sitemexp(iindex) = 0
        end if


        dreturnvalue = dreturnvalue * cdbl(sitemexp(iindex))
    next iindex

    calcexpression = dreturnvalue


end if

end function

類模塊一：類名：cbomreturnvalue

option explicit

'保持屬性值的局部變量
private mvarassbom as string '局部復制
private mvarbompoint as string '局部復制
private mvarquantity as double '局部復制
private mvarexpression as string '局部復制
public property let expression(byval vdata as string)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.expression = 5
    mvarexpression = vdata
end property

public property get expression() as string
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.expression
    expression = mvarexpression
end property

public property let quantity(byval vdata as double)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.quantity = 5
    mvarquantity = vdata
end property

public property get quantity() as double
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.quantity
    quantity = mvarquantity
end property

public property let bompoint(byval vdata as string)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.bompoint = 5
    mvarbompoint = vdata
end property

public property get bompoint() as string
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.bompoint
    bompoint = mvarbompoint
end property

public property let assbom(byval vdata as string)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.assbom = 5
    mvarassbom = vdata
end property

public property get assbom() as string
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.assbom
    assbom = mvarassbom
end property

類模塊二：類名：cbomvalue

option explicit

'保持屬性值的局部變量
private mvarassbom as string '局部復制
private mvarbompoint as string '局部復制
private mvarquantity as double '局部復制
public property let quantity(byval vdata as double)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.quantity = 5
    mvarquantity = vdata
end property

public property get quantity() as double
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.quantity
    quantity = mvarquantity
end property

public property let bompoint(byval vdata as string)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.bompoint = 5
    mvarbompoint = vdata
end property

public property get bompoint() as string
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.bompoint
    bompoint = mvarbompoint
end property

public property let assbom(byval vdata as string)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.assbom = 5
    mvarassbom = vdata
end property

public property get assbom() as string
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.assbom
    assbom = mvarassbom
end property

類模塊三：類名：cpointvalue

option explicit

'保持屬性值的局部變量
private mvarbompoint as string '局部復制
private mvarquantity as double '局部復制
public property let quantity(byval vdata as double)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.quantity = 5
    mvarquantity = vdata
end property

public property get quantity() as double
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.quantity
    quantity = mvarquantity
end property

public property let bompoint(byval vdata as string)
'向屬性指派值時使用，位于賦值語句的左邊。
'syntax: x.bompoint = 5
    mvarbompoint = vdata
end property

public property get bompoint() as string
'檢索屬性值時使用，位于賦值語句的右邊。
'syntax: debug.print x.bompoint
    bompoint = mvarbompoint
end property

加入后可直接在窗體中print出列表。

收集最實用的網(wǎng)頁特效代碼！

上一篇：用VB函數(shù)Dir實現(xiàn)遞歸搜索目錄

下一篇：優(yōu)化代碼時要選用正確的測試方法