- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助]SQL 与字典代码更改求助 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=83319)
-- 发布时间：2016/4/6 14:12:00
-- [求助]SQL 与字典代码更改求助
代码比较长，请老师见谅！
此主题相关图片如下：qq截图20160406141120.jpg
以下代码用于某一个母卷号的投料量与成材率的DataColChanged运算，进行了修改，使并卷转入后的新钢卷投料量继承了转出的哪些卷的投料量合计数：
If e.DataCol.Name = "审核" AndAlso e.DataRow.RowState <> DataRowState.Detached Then
_dt工序合并表.LoadFilter = "母卷号 = \'" & e.DataRow("母卷号") & "\' Or 记录标记2 = \'" & e.DataRow("记录标记2") & "\'"
_dt工序合并表.Load
\'投料量和成材率计算
Dim drs投料量 As List(Of DataRow) = _dt工序合并表.S elect("母卷号 = \'" & e.DataRow("母卷号") & "\'", "卷号,起始时间")
If drs投料量.Count = 0 Then
ElseIf drs投料量.Count=1 Then
If drs投料量(0)("工序") = "并卷转入" Then \'+++
drs投料量(0)("投料量") = _dt工序合并表.SQLCompute("Sum(投料量)", "工序 = \'并卷转出\' And 并卷对应卷号 = \'" & drs投料量(0)("卷号") & "\'") \'+++
Else \'+++
drs投料量(0)("投料量") = drs投料量(0)("重量")
End If \'+++
drs投料量(0)("成材率") = drs投料量(0)("重量") / drs投料量(0)("投料量")
Else
If drs投料量(0)("工序") = "并卷转入" Then \'+++
drs投料量(0)("投料量") = _dt工序合并表.SQLCompute("Sum(投料量)", "工序 = \'并卷转出\' And 并卷对应卷号 = \'" & drs投料量(0)("卷号") & "\'") \'+++
Else \'+++
drs投料量(0)("投料量")=drs投料量(0)("重量")
End If
Dim tll As Double = drs投料量(0)("投料量") \'+++
Dim dic As New Dictionary(Of String, Double())
Dim Dic_dr As new Dictionary(of DataRow, Double)
For i As Integer = 1 To drs投料量.Count - 1
If drs投料量(i)("卷号") = drs投料量(i-1)("卷号")
Dic_dr.Add(drs投料量(i), tll)
Else
Dim str As String = drs投料量(i)("卷号").Substring(0,drs投料量(i)("卷号").length - 1)
If dic.ContainsKey(str) = False Then
Dim fdr As DataRow = _dt工序合并表.Find("卷号 = \'" & str & "\'", "起始时间")
If fdr IsNot Nothing Then
Dim drs投料量_temp As List(of DataRow) = _dt工序合并表.S elect("substring(卷号,1," & drs投料量(i)("卷号").Length - 1 & ") = \'" & fdr("卷号") & "\' and len(卷号) = " & drs投料量(i)("卷号").Length,"卷号")
If drs投料量_temp.Count > 0 Then
Dim zl As Double = drs投料量_temp(0)("重量")
For j As Integer = 1 To drs投料量_temp.Count - 1
If drs投料量_temp(j)("卷号") <> drs投料量_temp(j-1)("卷号") Then
zl += drs投料量_temp(j)("重量")
End If
Next
dic.Add(str, new Double(){zl, tll})
Else
MessageBox.Show("发生代码运算错误,此错误的相关代码位于全局表事件的DataColChanged事件中,看到此条消息请与管理员联系!","警告",MessageBoxButtons.OK,MessageBoxIcon.Warning)
End If
Else
tll = drs投料量(i)("重量")
Dic_dr.Add(drs投料量(i), tll)
Continue For
End If
End If
tll = drs投料量(i)("重量") / dic(str)(0) * dic(str)(1)
Dic_dr.Add(drs投料量(i), tll)
End If
Next
For Each dr As DataRow In dic_dr.Keys
dr("投料量") = dic_dr(dr)
Next
For i As Integer = 0 To drs投料量.Count - 1
drs投料量(i)("成材率") = drs投料量(i)("重量") / drs投料量(i)("投料量")
Next
End If
_dt工序合并表.Save
以下代码是用于全表投料量与成材率计算，主代码也是之前老师帮我写的，但在这里我不会修改了，不知如何修改能实现并卷转入后的新钢卷投料量继承转出的哪些卷的投料量合计数。
\'投料量计算模块
Dim Result As DialogResult
Result = MessageBox.Show("即将进行全表投料量计算,是否执行?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If Result = DialogResult.Yes Then
Forms("进度条独立窗口").Show \'打开进度条独立窗口
Dim p As WinForm.ProgressBar \'定义进度条
Dim Lab1 As WinForm.Label
p = Forms("进度条独立窗口").Controls("ProgressBar")
Lab1 = Forms("进度条独立窗口").Controls("Label1")
Lab1.Text = "正在加载工序合并表全部数据..."
Application.DoEvents() \'即刻暂停代码的执行,重新绘制控件后,继续执行代码
_dt工序合并表.LoadFilter = ""
_dt工序合并表.Load
Dim t1 As Date = Date.Now
Dim drs As List(Of DataRow) = _dt工序合并表.S elect("", "卷号,起始时间")
drs(0)("投料量")=drs(0)("重量")
Dim tll As Double = drs(0)("重量")
Dim dic As New Dictionary(Of String, Double())
Dim Dic_dr As new Dictionary(of DataRow, Double)
For i As Integer = 1 To drs.Count - 1
p.Maximum = drs.Count \'设置最大值
p.Minimum = 0 \'设置最小值
If i Mod 100 = 0 Then \'每运算100条更新一次进度条
p.Value = i \'当前值为已经完成的行数
Lab1.Text = "工序合并表加载完成,开始计算..." & i + 1 & " / " & drs.Count
Application.DoEvents() \'即刻暂停代码的执行,重新绘制控件后,继续执行代码
ElseIf i=drs.Count-1 \'当i为最大值减1时
p.Value = drs.Count \'当前值直接等于最大值
Lab1.Text = "完成全表投料量与成材率计算,正在保存数据..." & i + 1 & " / " & drs.Count
Application.DoEvents() \'即刻暂停代码的执行,重新绘制控件后,继续执行代码
End If
If drs(i)("卷号") = drs(i-1)("卷号")
Dic_dr.Add(drs(i), tll)
Else
Dim str As String = drs(i)("卷号").Substring(0,drs(i)("卷号").length - 1)
If dic.ContainsKey(str) = False Then
Dim fdr As DataRow = _dt工序合并表.Find("卷号 = \'" & str & "\'", "起始时间")
If fdr IsNot Nothing Then
Dim drs_temp As List(of DataRow) = _dt工序合并表.S elect("substring(卷号,1," & drs(i)("卷号").Length - 1 & ") = \'" & fdr("卷号") & "\' and len(卷号) = " & drs(i)("卷号").Length, "卷号")
Dim zl As Double = drs_temp(0)("重量")
For j As Integer = 1 To drs_temp.Count - 1
If drs_temp(j)("卷号") <> drs_temp(j-1)("卷号") Then
zl += drs_temp(j)("重量")
End If
Next
dic.Add(str, new Double(){zl, tll})
Else
tll = drs(i)("重量")
Dic_dr.Add(drs(i), tll)
Continue For
End If
End If
tll = drs(i)("重量") / dic(str)(0) * dic(str)(1)
Dic_dr.Add(drs(i), tll)
End If
Next
For Each dr As DataRow In dic_dr.Keys
dr("投料量") = dic_dr(dr)
Next
For i As Integer = 0 To drs.Count - 1
drs(i)("成材率") = drs(i)("重量") / drs(i)("投料量")
Next
_dt工序合并表.Save
Forms("进度条独立窗口").Close
MessageBox.Show("完成全表投料量计算!耗时: " & (Date.Now - t1).TotalSeconds & "秒,请不要频繁操作.", "提示",MessageBoxButtons.ok, MessageBoxIcon.Information)
End If
[此贴子已经被作者于2016/4/6 14:13:05编辑过]
-- 发布时间：2016/4/6 15:10:00
--
没看懂你什么意思什么逻辑。
1、上传具体foxtable例子；
2、说明预算逻辑和最后的运算结果。