- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助]修改窗口[应付款及欠piao按供应商汇总]按钮代码 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=79227)
-- 发布时间：2015/12/27 10:39:00
-- [求助]修改窗口[应付款及欠piao按供应商汇总]按钮代码
红袍老师你好,麻烦你修改一下窗口[应付款及欠票按供应商汇总]按钮代码,谢谢
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:应付统计表.zip |
-- 发布时间：2015/12/27 11:36:00
--
什么意思啊，没问题啊
-- 发布时间：2015/12/27 11:44:00
--
Dim st As Date = Date.Now \'用于计算时间
Dim s As String = e.Form.Controls("财务月份").Value
Dim gys As String = e.Form.Controls("供应商").Value
Dim flt As String = "1=1"
If gys > "" Then
flt &= " and 供应商名称 = \'" & gys & "\'"
End If
If s > "" AndAlso s <> "选择月份" Then
Tables("应付款按供应商汇总").StopRedraw() \'停止重绘
DataTables("应付款按供应商汇总").DataRows.Clear
Dim dt_in As List(Of DataRow) = DataTables("应付款采购明细表").Select("供应商编号 is not null and " & flt, "供应商编号,供应商名称,月份") \'排序取出 应付采购 全部数据
Dim drs_out As List(Of DataRow) = DataTables("应付款采购收票明细表").Select("供应商编号 is not null and " & flt, "供应商编号,供应商名称,月份") \'排序取出 收票 全部数据
Dim drs_init As List(Of DataRow) = DataTables("应付款初始欠票").Select("月份 <= " & s)
Dim drs_init1 As List(Of DataRow) = DataTables("应付款初始账面应付款").Select("月份 <= " & s)
Dim drs_in1 As List(Of DataRow) = DataTables("应付款付款明细表").Select("供应商编号 is not null and " & flt, "供应商编号,供应商名称,月份")\'排序取出 付款 全部数据
Dim r As Row
Dim sum_curr_money, sum_prev_money,sum_curr_money1 , sum_prev_money1 As Double
Dim sum_curr_count, sum_prev_count As Single
r = Tables("应付款按供应商汇总").AddNew
r("供应商编号") = dt_in(0)("供应商编号")
r("供应商名称") = dt_in(0)("供应商名称")
If dt_in(0)("月份") < s Then \'之前月
sum_prev_count = sum_prev_count + dt_in(0)("数量")
sum_prev_money = sum_prev_money + dt_in(0)("含税金额")
Else If dt_in(0)("月份") = s Then \'本月份
sum_curr_money = sum_curr_money + dt_in(0)("含税金额")
sum_curr_count = sum_curr_count + dt_in(0)("数量")
End If
For i As Single = 1 To dt_in.Count - 1 \'写入供应商,同时统计采购数量和金额
If dt_in(i)("供应商编号") <> dt_in(i-1)("供应商编号") OrElse dt_in(i)("供应商名称") <> dt_in(i-1)("供应商名称") Then \'不同供应商
r("本期购货_数量") = sum_curr_count
r("本期购货_金额") = sum_curr_money
r("期初应付_尚未开票_数量") = sum_prev_count \'暂存
r("期初应付_尚未开票_金额") = sum_prev_money \'暂存
r = Tables("应付款按供应商汇总").AddNew \'插入新数据
r("供应商编号") = dt_in(i)("供应商编号")
r("供应商名称") = dt_in(i)("供应商名称")
sum_curr_money = 0
sum_curr_count = 0
sum_prev_count = 0
sum_prev_money = 0
End If
If dt_in(i)("月份") < s Then \'之前月
sum_prev_count = sum_prev_count + dt_in(i)("数量")
sum_prev_money = sum_prev_money + dt_in(i)("含税金额")
Else If dt_in(i)("月份") = s Then \'本月份
sum_curr_money = sum_curr_money + dt_in(i)("含税金额")
sum_curr_count = sum_curr_count + dt_in(i)("数量")
End If
Next
For Each dr As DataRow In drs_init
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'初始欠票\'
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
For Each dr As DataRow In drs_init1
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'初始账面应付款\'
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
For Each dr As DataRow In drs_in1
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'从付款明细中
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
r("本期支付_金额") = sum_curr_money1
r("期初应付_账面") = sum_prev_money1 \'暂存
For Each dr As DataRow In DataTables("应付款按供应商汇总").DataRows \'填充统计数据
sum_curr_money = 0 \'累计本月付款金额
sum_prev_money = 0 \'累计之前付款金额
Dim filter As String = "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'"
drs_in1 = DataTables("应付款付款明细表").Select(filter, "月份") \'排序取出 付款 全部数据
For Each dr_in1 As DataRow In drs_in1
If dr_in1("月份") < s Then
sum_prev_money1 = sum_prev_money1 + dr_in1("付款金额")
Else If dr_in1("月份") = s Then
sum_curr_money1 = sum_curr_money1 + dr_in1("付款金额")
Else
Exit For
End If
Next
Next
For Each dr As DataRow In drs_out
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'采购收票
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
r("本期购货_数量") = sum_curr_count
r("本期购货_金额") = sum_curr_money
r("期初应付_尚未开票_数量") = sum_prev_count \'暂存
r("期初应付_尚未开票_金额") = sum_prev_money \'暂存
MessageBox.Show("供应商耗时: " & (Date.Now - st).TotalSeconds & "秒")
st = Date.Now \'用于计算时间
For Each dr As DataRow In DataTables("应付款按供应商汇总").DataRows \'填充统计数据
sum_curr_count = 0 \'累计本月开票数量
sum_prev_count = 0 \'累计之前开票数量
sum_curr_money = 0 \'累计本月开票金额
sum_prev_money = 0 \'累计之前开票金额
Dim filter As String = "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'"
drs_out = DataTables("应付款采购收票明细表").Select(filter, "月份") \'排序取出 开票 全部数据
For Each dr_out As DataRow In drs_out
If dr_out("月份") < s Then
sum_prev_count = sum_prev_count + dr_out("开票_数量")
sum_prev_money = sum_prev_money + dr_out("开票_含税金额")
Else If dr_out("月份") = s Then
sum_curr_count = sum_curr_count + dr_out("开票_数量")
sum_curr_money = sum_curr_money + dr_out("开票_含税金额")
Else
Exit For
End If
Next
Dim qcsl As Single = DataTables("应付款初始欠票").compute("sum(入库_数量)", "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\' and 月份 <= " & s)
Dim qcsl1 As Single = DataTables("应付款初始欠票").compute("sum(入库_含税金额)", "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\' and 月份 <= " & s)
Dim qcsl2 As Single = DataTables("应付款初始账面应付款").compute("sum(账面应付金额)", "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\' and 月份 <= " & s)
Dim sum_in_prev_count As Single = dr("期初应付_尚未开票_数量") \'从表中取出暂存数据
Dim sum_in_prev_money As Double = dr("期初应付_尚未开票_金额") \'从表中取出暂存数据
Dim sum_in_prev_money1 As Double = dr("期初应付_账面")
dr("本期收票_数量") = sum_curr_count
dr("本期收票_金额") = sum_curr_money
dr("本期支付_金额") = sum_curr_money1
dr("期初应付_账面") = sum_in_prev_money1 - sum_prev_money1 + qcsl2
dr("期初应付_尚未开票_数量") = sum_in_prev_count - sum_prev_count + qcsl
dr("期初应付_尚未开票_金额") = sum_in_prev_money - sum_prev_money + qcsl1
dr("期末应付_账面") = dr("期初应付_账面") + dr("本期收票_金额") - dr("本期支付_金额")
dr("期末应付_尚未开票_数量") = dr("期初应付_尚未开票_数量") + dr("本期购货_数量") - dr("本期收票_数量")
dr("期末应付_尚未开票_金额") = dr("期初应付_尚未开票_金额") + dr("本期购货_金额") - dr("本期收票_金额")
Next
Tables("应付款按供应商汇总").ResumeRedraw()
End If
MessageBox.Show("统计和写入耗时: " & (Date.Now - st).TotalSeconds & "秒")
-- 发布时间：2015/12/27 12:17:00
--
此主题相关图片如下：应付采购发票付款汇总表.png
-- 发布时间：2015/12/27 12:17:00
--
此主题相关图片如下：应付款付款明细表.png
-- 发布时间：2015/12/27 13:40:00
--
Dim st As Date = Date.Now \'用于计算时间
Dim s As String = e.Form.Controls("财务月份").Value
Dim gys As String = e.Form.Controls("供应商").Value
Dim flt As String = "1=1"
If gys > "" Then
flt &= " and 供应商名称 = \'" & gys & "\'"
End If
If s > "" AndAlso s <> "选择月份" Then
Tables("应付款按供应商汇总").StopRedraw() \'停止重绘
DataTables("应付款按供应商汇总").DataRows.Clear
Dim dt_in As List(Of DataRow) = DataTables("应付款采购明细表").Select("供应商编号 is not null and " & flt, "供应商编号,供应商名称,月份") \'排序取出 应付采购 全部数据
Dim drs_out As List(Of DataRow) = DataTables("应付款采购收票明细表").Select("供应商编号 is not null and " & flt, "供应商编号,供应商名称,月份") \'排序取出 收票 全部数据
Dim drs_init As List(Of DataRow) = DataTables("应付款初始欠票").Select("月份 <= " & s)
Dim drs_init1 As List(Of DataRow) = DataTables("应付款初始账面应付款").Select("月份 <= " & s)
Dim drs_in1 As List(Of DataRow) = DataTables("应付款付款明细表").Select("供应商编号 is not null and " & flt, "供应商编号,供应商名称,月份")\'排序取出 付款 全部数据
Dim r As Row
Dim sum_curr_money, sum_prev_money As Double
Dim sum_curr_count, sum_prev_count As Single
r = Tables("应付款按供应商汇总").AddNew
r("供应商编号") = dt_in(0)("供应商编号")
r("供应商名称") = dt_in(0)("供应商名称")
If dt_in(0)("月份") < s Then \'之前月
sum_prev_count = sum_prev_count + dt_in(0)("数量")
sum_prev_money = sum_prev_money + dt_in(0)("含税金额")
Else If dt_in(0)("月份") = s Then \'本月份
sum_curr_money = sum_curr_money + dt_in(0)("含税金额")
sum_curr_count = sum_curr_count + dt_in(0)("数量")
End If
For i As Single = 1 To dt_in.Count - 1 \'写入供应商,同时统计采购数量和金额
If dt_in(i)("供应商编号") <> dt_in(i-1)("供应商编号") OrElse dt_in(i)("供应商名称") <> dt_in(i-1)("供应商名称") Then \'不同供应商
r("本期购货_数量") = sum_curr_count
r("本期购货_金额") = sum_curr_money
r("期初应付_尚未开票_数量") = sum_prev_count \'暂存
r("期初应付_尚未开票_金额") = sum_prev_money \'暂存
r = Tables("应付款按供应商汇总").AddNew \'插入新数据
r("供应商编号") = dt_in(i)("供应商编号")
r("供应商名称") = dt_in(i)("供应商名称")
sum_curr_money = 0
sum_curr_count = 0
sum_prev_count = 0
sum_prev_money = 0
End If
If dt_in(i)("月份") < s Then \'之前月
sum_prev_count = sum_prev_count + dt_in(i)("数量")
sum_prev_money = sum_prev_money + dt_in(i)("含税金额")
Else If dt_in(i)("月份") = s Then \'本月份
sum_curr_money = sum_curr_money + dt_in(i)("含税金额")
sum_curr_count = sum_curr_count + dt_in(i)("数量")
End If
Next
r("本期购货_数量") = sum_curr_count
r("本期购货_金额") = sum_curr_money
r("期初应付_尚未开票_数量") = sum_prev_count \'暂存
r("期初应付_尚未开票_金额") = sum_prev_money \'暂存
For Each dr As DataRow In drs_init
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'初始欠票\'
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
For Each dr As DataRow In drs_init1
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'初始账面应付款\'
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
For Each dr As DataRow In drs_in1
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'从付款明细中
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
For Each dr As DataRow In DataTables("应付款按供应商汇总").DataRows \'填充统计数据
sum_curr_money = 0 \'累计本月付款金额
sum_prev_money = 0 \'累计之前付款金额
Dim filter As String = "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'"
drs_in1 = DataTables("应付款付款明细表").Select(filter, "月份") \'排序取出 付款 全部数据
For Each dr_in1 As DataRow In drs_in1
If dr_in1("月份") < s Then
sum_prev_money = sum_prev_money + dr_in1("付款金额")
Else If dr_in1("月份") = s Then
sum_curr_money = sum_curr_money + dr_in1("付款金额")
End If
Next
dr("期初应付_账面") = sum_prev_money
dr("本期支付_金额") = sum_curr_money
Next
For Each dr As DataRow In drs_out
If DataTables("应付款按供应商汇总").find("供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'") Is Nothing Then
Dim nr = Tables("应付款按供应商汇总").AddNew \'采购收票
nr("供应商编号") = dr("供应商编号")
nr("供应商名称") = dr("供应商名称")
End If
Next
MessageBox.Show("供应商耗时: " & (Date.Now - st).TotalSeconds & "秒")
st = Date.Now \'用于计算时间
For Each dr As DataRow In DataTables("应付款按供应商汇总").DataRows \'填充统计数据
sum_curr_count = 0 \'累计本月开票数量
sum_prev_count = 0 \'累计之前开票数量
sum_curr_money = 0 \'累计本月开票金额
sum_prev_money = 0 \'累计之前开票金额
Dim filter As String = "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\'"
drs_out = DataTables("应付款采购收票明细表").Select(filter, "月份") \'排序取出 开票 全部数据
For Each dr_out As DataRow In drs_out
If dr_out("月份") < s Then
sum_prev_count = sum_prev_count + dr_out("开票_数量")
sum_prev_money = sum_prev_money + dr_out("开票_含税金额")
Else If dr_out("月份") = s Then
sum_curr_count = sum_curr_count + dr_out("开票_数量")
sum_curr_money = sum_curr_money + dr_out("开票_含税金额")
Else
Exit For
End If
Next
Dim qcsl As Single = DataTables("应付款初始欠票").compute("sum(入库_数量)", "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\' and 月份 <= " & s)
Dim qcsl1 As Single = DataTables("应付款初始欠票").compute("sum(入库_含税金额)", "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\' and 月份 <= " & s)
Dim qcsl2 As Single = DataTables("应付款初始账面应付款").compute("sum(账面应付金额)", "供应商编号 = \'" & dr("供应商编号") & "\' And 供应商名称 = \'" & dr("供应商名称") & "\' and 月份 <= " & s)
Dim sum_in_prev_count As Single = dr("期初应付_尚未开票_数量") \'从表中取出暂存数据
Dim sum_in_prev_money As Double = dr("期初应付_尚未开票_金额") \'从表中取出暂存数据
Dim sum_in_prev_money1 As Double = dr("期初应付_账面")
dr("本期收票_数量") = sum_curr_count
dr("本期收票_金额") = sum_curr_money
dr("期初应付_账面") = sum_in_prev_money1 + qcsl2
dr("期初应付_尚未开票_数量") = sum_in_prev_count - sum_prev_count + qcsl
dr("期初应付_尚未开票_金额") = sum_in_prev_money - sum_prev_money + qcsl1
dr("期末应付_账面") = dr("期初应付_账面") + dr("本期收票_金额") - dr("本期支付_金额")
dr("期末应付_尚未开票_数量") = dr("期初应付_尚未开票_数量") + dr("本期购货_数量") - dr("本期收票_数量")
dr("期末应付_尚未开票_金额") = dr("期初应付_尚未开票_金额") + dr("本期购货_金额") - dr("本期收票_金额")
Next
Tables("应付款按供应商汇总").ResumeRedraw()
End If
MessageBox.Show("统计和写入耗时: " & (Date.Now - st).TotalSeconds & "秒")
-- 发布时间：2015/12/27 14:30:00
--
红袍老师你好,选择相应的月份后,当月的付款金额已对应到相应的供应商,但期初与期末的账面金额还不对,麻烦老师修改一下.
-- 发布时间：2015/12/27 14:31:00
--
自己看到代码来改。
-- 发布时间：2015/12/27 14:31:00
--
麻烦你好好看懂一下代码。
不会就重新学习一下foxtable基础。
-- 发布时间：2015/12/27 15:08:00
--
谢谢老师,加上红色部份就对了, dr("期初应付_账面") = sum_in_prev_money1 - sum_prev_money + qcsl2