-- 作者：朱女士
-- 发布时间：2021/6/1 16:37:00
-- 数据回填
老师您好！
我想把物流表的数据回填到费用表中，我的代码只能回填客户不为空的数据，客户为空的不回填，我不知道问题出在哪，请指教！谢谢！
Dim dtp1 As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker1")
Dim dtp2 As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker2")
Dim y1 As Integer=year(dtp1.value)
Dim m1 As Integer = month(dtp1.value)
Dim d1 As Integer = day(dtp1.value)
Dim y2 As Integer=year(dtp2.value)
Dim m2 As Integer = month(dtp2.value)
Dim d2 As Integer = day(dtp2.value)
Dim dt1 As New Date(y1, m1, d1)
Dim dt2 As New Date(y2, m2, d2)
Dim dt3 As New Date(y1, m1, 1)
Dim dt4 As New Date(y1, m1, Date.DaysInMonth(y1,m1))
Dim tje,tje1,tje2,tgh1,tgh2,tgh3,tgh4,tgh,tzb1,tzb2 As Double
For Each r As Row In Tables("物流表").rows
Dim xm As String=r("xmdm")
Dim kh As String=r("客户")
Dim dl As String=r("大类代码")
Dim edm As String=r("二级代码")
Dim dr As DataRow = DataTables("物流表").find("发货日期>=# " & dt1 & "# and 发货日期<= #" & dt2 & "# And xmdm=\'" & xm & "\'and 客户=\'" & kh & "\'")
If dr IsNot Nothing Then
If dr("客户") IsNot Nothing
Dim fr As DataRow = DataTables("fyb").find("日期>=# " & dt1 & "# and 日期<= #" & dt2 & "# And xmdm=\'" & xm & "\'and 客户=\'" & kh & "\'")
If fr Is Nothing Then
fr=DataTables("fyb").AddNew()
If edm="a041" Then
tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm=\'" & xm & "\'and 客户=\'" & kh & "\'and [二级代码]=\'a041\'")
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="f"
fr("金额") =tje
End If
If edm="a042" Then
tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm=\'" & xm & "\' and 客户=\'" & kh & "\'and [二级代码]=\'a042\'")
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="w"
fr("金额") =tje
End If
If edm<>"a041" And edm<>"a042" Then
tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm=\'" & xm & "\' and 客户=\'" & kh & "\'and [二级代码]<>\'a041\' and [二级代码]<>\'a042\'")
tgh1=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)=\'c\'" & " and 客户=\'" & kh & "\' And 发货日期 Is not null") \'合计层压木产量
tgh2=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)=\'z\'" & " and 客户=\'" & kh & "\' and 发货日期 Is not null") \'合计纸板产量
tgh3=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)=\'f\'" & " and 客户=\'" & kh & "\' And 发货日期 Is not null") \'合计辅料产量
tgh4=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)=\'w\'" & " and 客户=\'" & kh & "\' and 发货日期 Is not null") \'合计外协产量
tgh=tgh1+tgh2
tzb1=tgh1/tgh
tzb2=tgh2/tgh
tje1=tje*tzb1
tje2=tje*tzb2
messagebox.show("客户:" & kh & "层压木本月产量:" & tgh1 & "纸板产量:" & tgh2 & "总金额:" & tje)
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
If tje1<>0 And tje2<>0 Then
fr("lb")="c"
fr("金额") =tje1
Dim wr As DataRow=DataTables("fyb").AddNew
wr("日期")=dr("发货日期")
wr("xmdm") =xm
wr("客户") =dr("客户")
wr("大类代码")=dr("大类代码")
wr("大类名称") =dr("大类名称")
wr("二级名称")=dr("二级名称")
wr("二级代码") =dr("二级代码")
wr("项目名称") =dr("项目名称")
wr("lb")="z"
wr("金额") =tje2
End If
If tje1=0 And tje2<>0
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="z"
fr("金额") =tje2
End If
If tje1<>0 And tje2=0
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="c"
fr("金额") =tje1
End If
If tgh1=0 And tgh2=0 And tgh3<>0
tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm=\'" & xm & "\' and 客户=\'" & kh & "\'and [二级代码]<>\'a041\' and [二级代码]<>\'a042\'")
tgh3=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)=\'f\'" & " and 客户=\'" & kh & "\' And 发货日期 Is not null") \'合计辅料产量
tgh4=DataTables("ddpsb").Compute("Sum(自产重量)","[发货日期]>= #" & dt3 & "# and [发货日期]<= #" & dt4 & "# and substring([ddlb],1,1)=\'w\'" & " and 客户=\'" & kh & "\' and 发货日期 Is not null") \'合计外协
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="f"
fr("金额") =tje
End If
If tgh1=0 And tgh2=0 And tgh4<>0
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户") =dr("客户")
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="w"
fr("金额") =tje
End If
End If
End If
Else If dr.IsNull("客户")
Dim fr As DataRow = DataTables("fyb").find("日期>=# " & dt1 & "# and 日期<= #" & dt2 & "# And xmdm=\'" & xm & "\'and 客户 isnull")
If fr Is Nothing Then
fr=DataTables("fyb").AddNew()
If dr("大类代码")="a" And edm<>"a041" And edm<>"a042" Then
tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm=\'" & xm & "\'[大类代码]=\'a\' and [二级代码]<>\'a041\' and [二级代码]<>\'042\'and 客户 isnull")
fr("日期")=dr("发货日期")
fr("客户")=Nothing
fr("xmdm") =xm
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="t"
fr("金额") =tje
End If
If dr("大类代码")="b" Then
tje=DataTables("物流表").Compute("Sum(金额)", "[发货日期]>= #" & dt1 & "# and [发货日期]<= #" & dt2 & "# And xmdm=\'" & xm & "\'[大类代码]=\'b\'and 客户 isnull")
fr("日期")=dr("发货日期")
fr("xmdm") =xm
fr("客户")=Nothing
fr("大类代码")=dr("大类代码")
fr("大类名称") =dr("大类名称")
fr("二级名称")=dr("二级名称")
fr("二级代码") =dr("二级代码")
fr("项目名称") =dr("项目名称")
fr("lb")="t"
fr("金额") =tje
End If
End If
End If
End If
Next
messagebox.show("回填完毕,请认真核对数据!","提示:")
MainTable=Tables("fyb")
|