- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助]关于某日期与不同时段日期对比的判断 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=120433)
-- 发布时间：2018/6/15 11:43:00
-- [求助]关于某日期与不同时段日期对比的判断
求助用代码计算出利息计算明细
-- 发布时间：2018/6/15 11:46:00
--
相关表截图
-- 发布时间：2018/6/15 11:48:00
--
此主题相关图片如下：三表.png
-- 发布时间：2018/6/15 11:50:00
--
此主题相关图片如下：qq图片20180615114623.png
-- 发布时间：2018/6/15 11:55:00
--
思路：从开始日期一年一年累加生成每一行数据。累加一年之后，查找当年是否有利率修改（有多少次修改就增加多少行），然后开始日期、结束日期改变一下即可。
不会做请上传具体foxtable项目。
-- 发布时间：2018/6/15 11:55:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:管理项目3_201806151151.zip |
-- 发布时间：2018/6/15 12:46:00
--
Dim dt1 As DataTable = DataTables("个人借款")
Dim dt2 As DataTable = DataTables("利率表")
Dim dt3 As DataTable = DataTables("利息计算")
For Each dr As DataRow In dt1.Select("")
dt3.DeleteFor("单据编号=\'" & dr("单据编号") & "\'")
Dim d1 As Date = dr("存款日期")
Dim d2 As Date = dr("归还日期")
Dim d = d1
Dim bj As Double= dr("借入金额")
Dim kssj As Date = dr("存款日期")
Dim jssj As Date = kssj.AddYears(1).AddDays(-1)
Dim dr_nll As DataRow = dt2.Find("利率更改日期<#" & d & "#", "利率更改日期 desc")
Do While d <= d2
Dim nd = new Date(d.year, 1, 1)
Dim drs = dt2.Select("利率更改日期>=#" & d & "# and 利率更改日期<#" & d.addyears(1) & "#")
Dim nll As Double = dr_nll("年利率")
Dim ndr As DataRow
For Each cdr As DataRow In drs
ndr = dt3.addnew
ndr("单据编号") = dr("单据编号")
ndr("本金") = bj
ndr("开始时间") = kssj
jssj = cdate(cdr("利率更改日期")).adddays(-1)
ndr("结束时间") = jssj
ndr("天数") = (jssj-kssj).TotalDays+1
ndr("年利率") = nll
ndr("利息金额") = bj*nll/100*ndr("天数")/365
kssj=jssj.adddays(1)
jssj=jssj.addyears(1)
bj = bj+ndr("利息金额")
dr_nll = cdr
nll = cdr("年利率")
Next
ndr = dt3.addnew
ndr("单据编号") = dr("单据编号")
ndr("本金") = bj
ndr("开始时间") = kssj
ndr("结束时间") = jssj
ndr("天数") = (jssj-kssj).TotalDays+1
ndr("年利率") = nll
ndr("利息金额") = bj*nll/100
kssj=kssj.addyears(1)
jssj=jssj.addyears(1)
bj = bj+bj*nll/100
d = kssj
Loop
Next
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:管理项目3.table |
-- 发布时间：2018/6/15 17:37:00
--
非常非常感谢版主
此主题相关图片如下：qq图片20180615172938.png
-- 发布时间：2018/6/15 17:51:00
--
Dim dt1 As DataTable = DataTables("个人借款")
Dim dt2 As DataTable = DataTables("利率表")
Dim dt3 As DataTable = DataTables("利息计算")
For Each dr As DataRow In dt1.Select("")
dt3.DeleteFor("单据编号=\'" & dr("单据编号") & "\'")
Dim d1 As Date = dr("存款日期")
Dim d2 As Date = dr("归还日期")
Dim d = d1
Dim bj As Double= dr("借入金额")
Dim kssj As Date = dr("存款日期")
Dim jssj As Date = kssj.AddYears(1).AddDays(-1)
Dim dr_nll As DataRow = dt2.Find("利率更改日期<#" & d & "#", "利率更改日期 desc")
Do While d <= d2
Dim nd = new Date(d.year, 1, 1)
Dim drs = dt2.Select("利率更改日期>=#" & d & "# and 利率更改日期<#" & d.addyears(1) & "#")
Dim nll As Double = 0
If dr_nll IsNot Nothing Then nll=dr_nll("年利率")
Dim ndr As DataRow
For Each cdr As DataRow In drs
ndr = dt3.addnew
ndr("单据编号") = dr("单据编号")
ndr("本金") = bj
ndr("开始时间") = kssj
jssj = cdate(cdr("利率更改日期")).adddays(-1)
jssj = iif(jssj > dr("归还日期"), dr("归还日期"), jssj)
ndr("结束时间") = jssj
ndr("天数") = (jssj-kssj).TotalDays+1
ndr("年利率") = nll
ndr("利息金额") = bj*nll/100*ndr("天数")/365
kssj=jssj.adddays(1)
jssj=jssj.addyears(1)
jssj = iif(jssj > dr("归还日期"), dr("归还日期"), jssj)
bj = bj+ndr("利息金额")
dr_nll = cdr
nll = cdr("年利率")
Next
ndr = dt3.addnew
ndr("单据编号") = dr("单据编号")
ndr("本金") = bj
ndr("开始时间") = kssj
ndr("结束时间") = jssj
ndr("天数") = (jssj-kssj).TotalDays+1
ndr("年利率") = nll
ndr("利息金额") = bj*nll/100
kssj=kssj.addyears(1)
jssj=jssj.addyears(1)
jssj = iif(jssj > dr("归还日期"), dr("归还日期"), jssj)
bj = bj+bj*nll/100
d = kssj
Loop
Next
-- 发布时间：2018/6/19 10:10:00
--
给我感觉是炉火纯青，行云流水
