以文本方式查看主题 - Foxtable(狐表) (http://foxtable.com/bbs/index.asp) -- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2) ---- 现有编码简化问题 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=95172) |
-- 作者:feixianzhi -- 发布时间:2017/1/12 9:59:00 -- 现有编码简化问题 有一段代码,太麻烦了,哪位大师帮忙简化一下。 Dim dr1 As DataRow = e.DataRow Select Case e.DataCol.Name Case "返利单价","全年应返数量","全年已返数量","全年未返数量","全年未返金额","第一季度实际数量","第一季度调整数量","第一季度计提数量","第一季度计提金额","第二季度实际数量","第二季度调整数量","第二季度计提数量","第二季度计提金额","第三季度实际数量","第三季度调整数量","第三季度计提数量","第三季度计提金额","第四季度实际数量","第四季度调整数量","第四季度计提数量","第四季度计提金额" If dr1.IsNull("全年应返数量") OrElse dr1.IsNull("全年已返数量") Then dr1("全年未返数量") = Nothing Else dr1("全年未返数量") = round2(dr1("全年应返数量") - dr1("全年已返数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull("全年未返数量") Then dr1("全年未返金额") = Nothing Else dr1("全年未返金额") = round2(dr1("全年未返数量") * dr1("返利单价"),2) End If If dr1.IsNull("第一季度实际数量") Then dr1("第一季度计提数量") = Nothing Else dr1("第一季度计提数量") = round2(dr1("第一季度实际数量") + dr1("第一季度调整数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull("第一季度计提数量") Then dr1("第一季度计提金额") = Nothing Else dr1("第一季度计提金额") = round2(dr1("第一季度计提数量") * dr1("返利单价"),2) End If If dr1.IsNull("第二季度实际数量") Then dr1("第二季度计提数量") = Nothing Else dr1("第二季度计提数量") = round2(dr1("第二季度实际数量") + dr1("第二季度调整数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull("第二季度计提数量") Then dr1("第二季度计提金额") = Nothing Else dr1("第二季度计提金额") = round2(dr1("第二季度计提数量") * dr1("返利单价"),2) End If If dr1.IsNull("第三季度实际数量")Then dr1("第三季度计提数量") = Nothing Else dr1("第三季度计提数量") = round2(dr1("第三季度实际数量") + dr1("第三季度调整数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull("第三季度计提数量") Then dr1("第三季度计提金额") = Nothing Else dr1("第三季度计提金额") = round2(dr1("第三季度计提数量") * dr1("返利单价"),2) End If If dr1.IsNull("第四季度实际数量") Then dr1("第四季度计提数量") = Nothing Else dr1("第四季度计提数量") = round2(dr1("第四季度实际数量") + dr1("第四季度调整数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull("第四季度计提数量") Then dr1("第四季度计提金额") = Nothing Else dr1("第四季度计提金额") = round2(dr1("第四季度计提数量") * dr1("返利单价"),2) End If End Select |
-- 作者:有点色 -- 发布时间:2017/1/12 10:34:00 -- Dim dr1 As DataRow = e.DataRow Select Case e.DataCol.Name Case "全年应返数量","全年已返数量","返利单价","全年未返数量" If dr1.IsNull("全年应返数量") OrElse dr1.IsNull("全年已返数量") Then dr1("全年未返数量") = Nothing Else dr1("全年未返数量") = round2(dr1("全年应返数量") - dr1("全年已返数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull("全年未返数量") Then dr1("全年未返金额") = Nothing Else dr1("全年未返金额") = round2(dr1("全年未返数量") * dr1("返利单价"),2) End If Case "第一季度实际数量","第一季度调整数量","第一季度计提数量","第二季度实际数量","第二季度调整数量","第二季度计提数量","第三季度实际数量","第三季度调整数量","第三季度计提数量","第四季度实际数量","第四季度调整数量","第四季度计提数量" Dim jd As String = e.DataCol.name.substring(0, 4) If dr1.IsNull(jd & "实际数量") Then dr1(jd & "计提数量") = Nothing Else dr1(jd & "计提数量") = round2(dr1(jd & "实际数量") + dr1(jd & "调整数量"),2) End If If dr1.IsNull("返利单价") OrElse dr1.IsNull(jd & "计提数量") Then dr1(jd & "计提金额") = Nothing Else dr1(jd & "计提金额") = round2(dr1(jd & "计提数量") * dr1("返利单价"),2) End If Case "返利单价" Dim ary() As String = {"第一季度实际数量", "第二季度实际数量", "第三季度实际数量", "第四季度实际数量", "全年应返数量"} For Each s As String In ary e.DataTable.DataCols(s).RaiseDataColChanged(dr1) next End Select |
-- 作者:feixianzhi -- 发布时间:2017/1/12 13:14:00 -- 非常感谢有点色老师的帮忙。 |
-- 作者:feixianzhi -- 发布时间:2017/1/12 20:50:00 -- 有点色老师又有一个问题,请您帮忙。 以下这段代码在“合同”表的DatacolChanged事件中,现不想实时统计,只是想需要的时候统计。做了个按钮,但下面的编码不能用,需要怎么改。 If e.DataCol.name = "财务编号" OrElse e.DataCol.name = "返利类型" Then Dim dr As DataRow = e.DataRow Dim dt As DataTable If dr("返利类型") = "按入库" Then dt = DataTables("入库结构报表") ElseIf dr("返利类型") = "按销售" Then dt = DataTables("批发及退货报表") End If Dim yearin1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\'") Dim yearin2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\'") Dim yearout1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\' and 高开结清 = true") Dim yearout2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\' and 返利结清 = true") dr("全年应返数量") = yearin1 + yearin2 dr("全年已返数量") = yearout1 + yearout2 Dim cs() As String = {"第一季度统计数量","第二季度统计数量","第三季度统计数量","第四季度统计数量"} For i As Integer = 0 To cs.Length - 1 Dim filter As String = "" Dim d As Date d = new Date(2016, 3*i+1, 1) \'这是提取日期的年,这是2016年的,如果是2017年,则2016改成2017,以此类推. filter = " and 单据日期 >= #" & d & "# and 单据日期 < #" & d.AddMonths(3) & "#" Dim quarin1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\'" & filter) Dim quarin2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\'" & filter) Dim quarout1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\' and 高开结清 = true" & filter) Dim quarout2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\' and 返利结清 = true" & filter) dr(cs(i)) = quarin1 + quarin2 - quarout1 - quarout2 Next End If |
-- 作者:feixianzhi -- 发布时间:2017/1/12 20:55:00 -- 请有点色老师帮忙。 |
-- 作者:有点色 -- 发布时间:2017/1/12 22:04:00 -- For Each dr As DataRow In DataTables("表A").Select("") Dim dt As DataTable If dr("返利类型") = "按入库" Then dt = DataTables("入库结构报表") ElseIf dr("返利类型") = "按销售" Then dt = DataTables("批发及退货报表") End If Dim yearin1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\'") Dim yearin2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\'") Dim yearout1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\' and 高开结清 = true") Dim yearout2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\' and 返利结清 = true") dr("全年应返数量") = yearin1 + yearin2 dr("全年已返数量") = yearout1 + yearout2 Dim cs() As String = {"第一季度统计数量","第二季度统计数量","第三季度统计数量","第四季度统计数量"} For i As Integer = 0 To cs.Length - 1 Dim filter As String = "" Dim d As Date d = new Date(2016, 3*i+1, 1) \'这是提取日期的年,这是2016年的,如果是2017年,则2016改成2017,以此类推. filter = " and 单据日期 >= #" & d & "# and 单据日期 < #" & d.AddMonths(3) & "#" Dim quarin1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\'" & filter) Dim quarin2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\'" & filter) Dim quarout1 As Double = dt.Compute("sum(数量)", "高开财务编号 = \'" & dr("财务编号") & "\' and 高开结清 = true" & filter) Dim quarout2 As Double = dt.Compute("sum(数量)", "返利财务编号 = \'" & dr("财务编号") & "\' and 返利结清 = true" & filter) dr(cs(i)) = quarin1 + quarin2 - quarout1 - quarout2 Next Next |
-- 作者:feixianzhi -- 发布时间:2017/1/12 22:26:00 -- 非常感谢有点色老师,这么晚了还帮忙,辛苦了。 |