目前在設計一支産品BOM水晶報表,用PULL模式,在MYSQL中
BOM資料表結構:
cprdt prdt whouse dept qty flag
其中cprdt,prdt為資料表主KEY,flag為0表示無下階,為1表示有下階
想要實現選擇不同的CPRDT産品,出現其所有相關的PRDT資料
由於不能確定一個CPRDT産品到底會有多少階,目前是用迴圈僅找出第一階
如果出現多階要怎樣快速實現呢?(記得之前用POWERBUILDER+MSSQL設計時用游標,ASP.NET+MYSQL中要怎樣寫呢?也用游標嗎？)
5/17嘗試用存儲過程+游標,可好像存儲過程中不能再調用它本身
5/18用代碼及函數,可實現函數的反復調用,暫且所要的結果都跑出來了^__^
代碼及函數再經修剪,終於可以秀出理想的結果啦^__^
......
str = "select a.cprdt ,b.pname,b.unit,b.spec,a.prdt,c.pname as pname1,c.unit as unit1,c.spec as spec1,a.qty,a.flag,0 as seq,a.dept ,d.name as dp,a.flag as flag1,'' as prdt0 "
str += " from sto_boms as a ,sto_stocks as b,sto_stocks as c,bas_refers as d "
str += " where a.cprdt=b.prdt and a.prdt=c.prdt and d.kind='2102' and a.dept=d.no limit 0"
dt = fc.datas(Session("mysqlcns"), str)
str = "select distinct a.cprdt,b.pname,b.unit,b.spec from sto_boms as a,sto_stocks as b where a.cprdt=b.prdt and (a.cprdt between '" & strcode1 & "' and '" & strcode2 & "' ) "
Dim dt0 As DataTable = fc.datas(Session("mysqlcns"), str)
Dim sql, cprdt, pname, unit, spec As String
sql = "select a.cprdt ,b.pname,b.unit,b.spec,a.prdt,c.pname as pname1,c.unit as unit1,c.spec as spec1,a.qty,a.flag,0 as seq,a.dept ,d.name as dp,a.flag as flag1,'' as prdt0 "
sql += " from sto_boms as a ,sto_stocks as b,sto_stocks as c,bas_refers as d "
sql += " where a.cprdt=b.prdt and a.prdt=c.prdt and d.kind='2102' and a.dept=d.no "
For inta = 0 To dt0.Rows.Count - 1
cprdt = dt0.Rows(inta)(0)
pname = dt0.Rows(inta)(1)
unit = dt0.Rows(inta)(2)
spec = dt0.Rows(inta)(3)
bom7206(dt, 0, 1, sql, cprdt, pname, unit, spec, cprdt)
Next
......
Sub bom7206(ByVal dt As DataTable, ByVal seq As Int16, ByVal qty As Decimal, ByVal sql As String, ByVal cprdt As String, ByVal pname As String, ByVal unit As String, ByVal spec As String, ByVal prdt As String)
Dim prdt1, flag, sql1 As String
Dim row As DataRow
Dim intb As Integer
Dim dt0 As DataTable
sql1 = sql + " and a.cprdt='" & prdt & "' "
dt0 = fc.datas(Session("mysqlcns"), sql1)
If dt0.Rows.Count > 0 Then
seq += 1
For intb = 0 To dt0.Rows.Count - 1
row = dt.NewRow
row(0) = cprdt
row(1) = pname
row(2) = unit
row(3) = spec
row(4) = dt0.Rows(intb)(4)
row(5) = dt0.Rows(intb)(5)
row(6) = dt0.Rows(intb)(6)
row(7) = dt0.Rows(intb)(7)
row(8) = dt0.Rows(intb)(8) * qty
row(9) = dt0.Rows(intb)(9)
row(10) = seq
row(11) = dt0.Rows(intb)(11)
row(12) = dt0.Rows(intb)(12)
row(13) = dt0.Rows(intb)(13)
row(14) = prdt
dt.Rows.Add(row)
prdt1 = dt0.Rows(intb)(4)
flag = dt0.Rows(intb)(9)
If flag = "1" Then
bom7206(dt, seq, dt0.Rows(intb)(8), sql, cprdt, pname, unit, spec, prdt1)
End If
Next
seq -= 1
End If
End Sub
P.S.最後記得將結果保存起來,以便快速查詢^__^
