三個表的合並
| 日期 | 客戶 | 投入數 | 不良數 | 不良率 | 制損 | 良品數 | 誤判率 | 毛屑 | 碰刮擦划傷 | 顆粒 | 異色 | 色差 | 按鍵不良 | 漏件 | 卡勾不良 | 棉絮 | 脫漆 | 溢漆 | 積漆 |
這是一個正在使用的報表，但是在做業務系統時，需要分成三個表，fail .check_info ,fail_info,然後在合並成這樣的報表出來。
Dim myda As SqlDataAdapter
Dim ds As DataSet = New DataSet
Dim t3 As DataTable = New DataTable("t3")
Dim tr As DataRow
Dim i, j As Int16
'fali ds
mysql = "SELECT * FROM Fail ORDER BY Fail_ID"
ds.Tables.Clear()
conn.sqlconn_Open()
sqlcomm(mysql.Trim)
myda = New SqlDataAdapter(mycomm)
myda.Fill(ds, "Fail")
'check info
' mysql = " "
mysql = " SELECT * FROM dbo.Check_Info
' ds.Tables.Clear()
sqlcomm(mysql.Trim)
myda = New SqlDataAdapter(mycomm)
myda.Fill(ds, "check")
'fail info
mysql = "SELECT * FROM dbo.Fail_Info ORDER BY "
' ds.Tables.Clear()
sqlcomm(mysql.Trim)
myda = New SqlDataAdapter(mycomm)
myda.Fill(ds, "Fail_info")
conn.sqlconn_Close()
'確定t3table
'把check填入t3
' Dim tlength As Int16 = ds.Tables("fail").Columns.Count + ds.Tables("check").Columns.Count
For i = 0 To 14 '
t3.Columns.Add(New DataColumn("c" + i.ToString))
Next
For i = 0 To ds.Tables("check").Rows.Count - 1
tr = t3.NewRow
tr(0) = ds.Tables("check").Rows(i)(0).ToString '日期
tr(1) = ds.Tables("check").Rows(i)(1) '客戶
..............................
Dim getqty As String = ds.Tables("check").Rows(i)(8).ToString
Dim failqty As String = ds.Tables("check").Rows(i)(9).ToString
tr(8) = getqty.Trim '投入數
tr(9) = failqty.Trim '不良數
If Val(getqty) = 0 Then
tr(10) = "#DIV/0!" '不良率
Else
If Val(failqty) = 0 Then
tr(10) = "0.00%" '不良率
Else
Dim failper As String = (Val(failqty) / Val(getqty)).ToString
tr(10) = failper.Trim '不良率
End If
End If
Dim makeqty As String = ds.Tables("check").Rows(i)(10).ToString
Dim fineqty As String = ds.Tables("check").Rows(i)(11).ToString
tr(11) = makeqty.Trim '制損數
tr(12) = fineqty.Trim '良品數
If Val(makeqty) + Val(fineqty) + Val(failqty) = 0 Then
tr(13) = "0.00%" '誤判率
Else
If Val(makeqty) + Val(fineqty) = 0 Then
tr(13) = "0.00%" '誤判率
Else
Dim makeper As String = (Val(makeqty) + Val(fineqty) + Val(failqty)) / (Val(makeqty) + Val(fineqty)).ToString
tr(13) = makeper.Trim '誤判率
End If
End If
tr(14) = ds.Tables("check").Rows(i)(12).ToString 'check_id
t3.Rows.Add(tr)
Next
'check info已經錄入完畢
Dim strtemp As String
For i = 0 To ds.Tables("fail").Rows.Count - 1
strtemp = "D" + ds.Tables("fail").Rows(i)(0).ToString
t3.Columns.Add(New DataColumn(strtemp.Trim))
Next i
For i = 0 To t3.Rows.Count - 1
For j = 0 To ds.Tables("fail_info").Rows.Count - 1
'如果checkid一樣
If t3.Rows(i).Item(14).ToString = ds.Tables("fail_info").Rows(j)(0).ToString Then
Dim stemp As String = "D" + ds.Tables("fail_info").Rows(j)(2).ToString 'fail_id
t3.Rows(i)(stemp) = ds.Tables("fail_info").Rows(j)(1)
End If
Next j
Next i
Me.GridView1.DataSource = t3
Me.GridView1.DataBind()
''加上標題
''加上check 信息
'sstable = "<table width='100%' cellSpacing='1' cellPadding='1' border='1' bordercolor ='#006666'>"
'sstable &= "<tr><td>日期</td><td>客戶</td><td>料號</td><td>材料類</td><td>廠商</td>"
'sstable &= "<td>品名</td><td>Model</td><td>機種</td><td>投入數</td><td>不良數</td><td>不良率</td>"
'sstable &= "<td>制損數</td><td>良品數</td><td>誤判率</td>"
' ''加入不良描述
''For i = 0 To ds.Tables("Fail").Rows.Count - 1
'' sstable &= "<td>" + ds.Tables("Fail").Rows(i)("Fail_Name") + "</td>"
''Next
'sstable &= "</tr>"
''加入內容
''加上check 信息
'For i = 0 To ds.Tables("check").Rows.Count - 1
' sstable &= "<tr>"
' For j = 0 To ds.Tables("check").Columns.Count - 1
' sstable &= "<td>" + ds.Tables("check").Rows(i)(0).ToString + "</td>" '日期
' sstable &= "<td>" + ds.Tables("check").Rows(i)(1) + "</td>" '客戶
' sstable &= "<td>" + ds.Tables("check").Rows(i)(2) + "</td>" '料號
' sstable &= "<td>" + ds.Tables("check").Rows(i)(3) + "</td>" '材料類
' sstable &= "<td>" + ds.Tables("check").Rows(i)(4) + "</td>" '廠商
' sstable &= "<td>" + ds.Tables("check").Rows(i)(5) + "</td>" '品名
' sstable &= "<td>" + ds.Tables("check").Rows(i)(6) + "</td>" 'Model
' sstable &= "<td>" + ds.Tables("check").Rows(i)(7) + "</td>" '機種
' Dim getqty As String = ds.Tables("check").Rows(i)(8).ToString
' Dim failqty As String = ds.Tables("check").Rows(i)(9).ToString
' sstable &= "<td>" + getqty.Trim + "</td>" '投入數
' sstable &= "<td>" + failqty.Trim + "</td>" '不良數
' If Val(getqty) = 0 Then
' sstable &= "<td>#DIV/0!</td>" '不良率
' Else
' If Val(failqty) = 0 Then
' sstable &= "<td>0.00%</td>" '不良率
' Else
' Dim failper As String = (Val(failqty) / Val(getqty)).ToString
' sstable &= "<td>" + failper.Trim + "</td>" '不良率
' End If
' End If
' Dim makeqty As String = ds.Tables("check").Rows(i)(10).ToString
' Dim fineqty As String = ds.Tables("check").Rows(i)(11).ToString
' sstable &= "<td>" + makeqty.Trim + "</td>" '制損數
' sstable &= "<td>" + fineqty.Trim + "</td>" '良品數
' If Val(makeqty) + Val(fineqty) + Val(failqty) = 0 Then
' sstable &= "<td>0.00%</td>" '誤判率
' Else
' If Val(makeqty) + Val(fineqty) = 0 Then
' sstable &= "<td>0.00%</td>" '誤判率
' Else
' Dim makeper As String = (Val(makeqty) + Val(fineqty) + Val(failqty)) / (Val(makeqty) + Val(fineqty)).ToString
' sstable &= "<td>" + makeper.Trim + "</td>" '誤判率
' End If
' End If
' Next
' sstable &= "</tr>"
'Next
'sstable &= "</table>"
' '加上不良描述
' ' sstable &= ""
' '加入復判信息和不良
' Dim code = ds.Tables("check").Rows(i)(12).ToString 'check id
' For j = 0 To ds.Tables("Fail_info").Rows.Count - 1
' '檢查checkid是否一樣
' Dim check_ID As String = ds.Tables("check").Rows(j)(0).ToString
' If check_ID = code Then
' 'fail_info ID
' Dim fail_ID As String = ds.Tables("check").Rows(j)(1).ToString
' For k = 0 To ds.Tables("Fail").Rows.Count - 1
' 'fail ID
' If fail_ID = ds.Tables("fail").Rows(k)(1).ToString Then
' End If
' Next
' End If
' Next
' sstable &= "</tr>"
'Next
'sstable &= "</table>"
