专家坐堂-代码效率优化Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim gs As String ,dw As String , bb As String ,dfje As Decimal,je As Decimal,mxzje As Decimal

\'\'\'\'\'\'\'\'\'查询需要计算请款总金额的清单

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.C

cmd.CommandText = "SELEC T 公司,单位,币别,sum(请款金额) as 请款总金额 from 请款单 group by 公司,单位,币别 order by 公司"

dt = cmd.ExecuteReader()

\'\'\'\'\'\'\'\'\'查询需要计算请款总金额的清单

If dt.DataRows.Count > 0 Then

For Each drb As DataRow In dt.DataRows

bb = drb("币别")

gs = drb("公司")

dw = drb("单位")

je = drb("请款总金额")

Dim cmd1 As New SQLCommand

Dim dt1 As DataTable

cmd1.C

cmd1.CommandText = "Selec t _identify as 序号, 公司,单位,本期贷方发生额,发生期间 ,应支付期间,付款总金额,请款总金额,币别 FROM 科目余额表 where 公司 = \'" & gs & "\' " & _

" And 单位 =\'" & dw & "\' and 币别=\'" & bb & "\'order by 应支付期间 "

dt1 = cmd1.ExecuteReader()

If dt1.DataRows.Count > 0 Then

mxzje = dt1.Compute("sum(本期贷方发生额)","")

dfje=je - mxzje

For Each dra As DataRow In dt1.DataRows

If dra("公司")=gs And dra("单位")=dw And dra("币别")=bb Then

If je>= dra("本期贷方发生额") Then

dra("请款总金额") = dra("本期贷方发生额")

je = je - dra("本期贷方发生额")

Else

dra("请款总金额") = je

je = 0

End If

\'\'\'\'\'\'\'\'反写请款总金额

Dim cmd2 As New SQLCommand

Dim dt2 As DataTable

cmd2.C

cmd2.CommandText = "updat e 科目余额表 set 请款总金额 = \'" & dra("请款总金额") & "\' where _identify = \'" & dra("序号") & "\'"

dt2 = cmd2.ExecuteReader()

\'\'\'\'\'\'\'\'反写请款总金额

End If