接在上面代码的后面 For Each pricenode In priceroot.childNodes If pricenode.baseName <> "record" Then MsgBox "价格数据错误" GoTo ClearAndExit Else '一条价格记录 S1 = "" S2 = "" For Each Node In pricenode.childNodes If Node.baseName = "ID" Then S1 = Node.Text ElseIf Node.baseName = "生效日期" Then S2 = Node.Text edate = Format(S2, "yyyy-mm-dd") ElseIf Node.baseName = "失效日期" Then S3 = Node.Text ElseIf Node.baseName = "承运人" Then S4 = Node.Text ElseIf Node.baseName = "开船日期" Then S5 = Node.Text ' TTin = s5 ' Call EtdChangeIn ElseIf Node.baseName = "目的港" Then S6 = Node.Text ElseIf Node.baseName = "起运港" Then s14 = Node.Text ElseIf Node.baseName = "GP20价格" Then S7 = Node.Text ElseIf Node.baseName = "GP40价格" Then S8 = Node.Text ElseIf Node.baseName = "GP45价格" Then S9 = Node.Text ElseIf Node.baseName = "中转港" Then S10 = Node.Text ElseIf Node.baseName = "航程" Then S11 = Node.Text ElseIf Node.baseName = "报价协议号" Then S12 = Node.Text ElseIf Node.baseName = "备注" Then s13 = Node.Text Else MsgBox "价格记录字段错误" GoTo ClearAndExit End If Next Count = Count + 1 rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0 'and carrier=('" & S4 & "')and end_date> ('" & tdate & "') and ('" & edate & "')>('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic If Not rs.EOF Then rs.Fields("end_date") = Format(S2, "yyyy-mm-dd") rs.Fields("status") = -1 rs.UPdate End If rs.Close
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0 'and carrier=('" & S4 & "')and end_date> ('" & tdate & "') and ('" & edate & "')<=('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic If Not rs.EOF Then rs.Fields("status") = -1 rs.UPdate End If rs.Close rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0' and carrier=('" & S4 & "')and end_date> ('" & tdate & "') and ('" & edate & "')>=('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic If Not rs.EOF Then rs.Fields("status") = -1 rs.UPdate End If rs.Close
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0' and carrier=('" & S4 & "')and end_date< ('" & tdate & "') and ('" & edate & "')>('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic If Not rs.EOF Then rs.Fields("status") = -1 rs.UPdate End If rs.Close
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0' and carrier=('" & S4 & "')and end_date< ('" & edate & "') and ('" & edate & "')<('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic If Not rs.EOF Then rs.Fields("status") = -1 rs.UPdate End If rs.Close
For Each pricenode In priceroot.childNodes
If pricenode.baseName <> "record" Then
MsgBox "价格数据错误"
GoTo ClearAndExit
Else '一条价格记录
S1 = ""
S2 = ""
For Each Node In pricenode.childNodes
If Node.baseName = "ID" Then
S1 = Node.Text
ElseIf Node.baseName = "生效日期" Then
S2 = Node.Text
edate = Format(S2, "yyyy-mm-dd")
ElseIf Node.baseName = "失效日期" Then
S3 = Node.Text
ElseIf Node.baseName = "承运人" Then
S4 = Node.Text
ElseIf Node.baseName = "开船日期" Then
S5 = Node.Text
' TTin = s5
' Call EtdChangeIn
ElseIf Node.baseName = "目的港" Then
S6 = Node.Text
ElseIf Node.baseName = "起运港" Then
s14 = Node.Text
ElseIf Node.baseName = "GP20价格" Then
S7 = Node.Text
ElseIf Node.baseName = "GP40价格" Then
S8 = Node.Text
ElseIf Node.baseName = "GP45价格" Then
S9 = Node.Text
ElseIf Node.baseName = "中转港" Then
S10 = Node.Text
ElseIf Node.baseName = "航程" Then
S11 = Node.Text
ElseIf Node.baseName = "报价协议号" Then
S12 = Node.Text
ElseIf Node.baseName = "备注" Then
s13 = Node.Text
Else
MsgBox "价格记录字段错误"
GoTo ClearAndExit
End If
Next
Count = Count + 1
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0 'and carrier=('" & S4 & "')and end_date> ('" & tdate & "') and ('" & edate & "')>('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
rs.Fields("end_date") = Format(S2, "yyyy-mm-dd")
rs.Fields("status") = -1
rs.UPdate
End If
rs.Close
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0 'and carrier=('" & S4 & "')and end_date> ('" & tdate & "') and ('" & edate & "')<=('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
rs.Fields("status") = -1
rs.UPdate
End If
rs.Close rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0' and carrier=('" & S4 & "')and end_date> ('" & tdate & "') and ('" & edate & "')>=('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
rs.Fields("status") = -1
rs.UPdate End If
rs.Close
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0' and carrier=('" & S4 & "')and end_date< ('" & tdate & "') and ('" & edate & "')>('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
rs.Fields("status") = -1
rs.UPdate End If
rs.Close
rs.Open "select * from freight_in_price where booking_co=('" & SS & "') and etd=('" & S5 & "') and sp_price='0' and carrier=('" & S4 & "')and end_date< ('" & edate & "') and ('" & edate & "')<('" & tdate & "') and port_loading=('" & s14 & "') and port_discharge=('" & S6 & "') and status=0 ", ADO_Cnn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
rs.Fields("status") = -1
rs.UPdate End If
rs.Close
rs.Open "select * from freight_in_price where 1=0", ADO_Cnn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs.Fields("booking_co") = SS
rs.Fields("interior_code") = S1
rs.Fields("carrier") = S4
rs.Fields("port_discharge") = S6
rs.Fields("in_date") = Format(s, "yyyy-mm-dd")
rs.Fields("container_type") = "GP"
rs.Fields("price_20") = S7
rs.Fields("price_40") = S8
rs.Fields("price_45") = S9
rs.Fields("commission") = "4.25%"
rs.Fields("etd") = S5
rs.Fields("sail_day") = S11
rs.Fields("vis") = S10
rs.Fields("notes") = s13
rs.Fields("begin_date") = Format(S2, "yyyy-mm-dd")
rs.Fields("end_date") = Format(S3, "yyyy-mm-dd")
rs.Fields("status") = 0
rs.Fields("cargo_type") = "普通货物"
rs.Fields("transit_type") = "集装箱"
rs.Fields("sales") = g_sUserID
rs.Fields("freight_type") = "FREIGHT PREPAID"
rs.Fields("contract_no_sales") = S12
rs.Fields("sp_price") = 0
rs.Fields("port_loading") = s14
rs.UPdate
rs.Close End If
Next