If e.Form.Controls("StartDate").Value = Nothing OrElse e.Form.Controls("EndDate").Value = Nothing Then '当日期为空取消保存操作
messagebox.show("请选择起止日期")
Return
End If
If e.Form.Controls("ComboBox1").Value = e.Form.Controls("ComboBox1").Items(0) Then
Dim cmd As New SQLCommand
cmd.CommandText = "Select * Fr om {缴费情况} Where [_Identify] Is null"
Dim dt As DataTable = cmd.ExecuteReader()
If dt.DataCols.Contains("查询_月租_所属日期") = False Then
dt.DataCols.add("查询_月租_所属日期", Gettype(Date))
End If
If dt.DataCols.Contains("查询_月租_缴费金额") = False Then
dt.DataCols.add("查询_月租_缴费金额", Gettype(Double))
End If
Dim Filter As String
Dim f2 As String
Filter = "分类 = '" & e.Form.Controls("ComboBox1").Items(1) & "'"
With e.Form.Controls("StartDate")
If .Value IsNot Nothing Then
f2 = "[缴费_月租_起计日期] >= #" & .Value & "#"
End If
End With
With e.Form.Controls("EndDate")
If .Value IsNot Nothing Then
If f2 > "" Then
f2 = Filter & " And "
End If
f2 = f2 & "[缴费_月租_起计日期] <= #" & .Value & "#"
End If
End With
If F2 > "" Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & " (" & F2 & ") "
End If
With e.Form.Controls("DropBox1")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "车牌号码 = '" & .Value & "'"
End If
End With
If Filter > "" Then
Dim drs As List(Of DataRow) = DataTables("缴费情况").SQLSelect(Filter)
Dim a As Integer
For a = 0 To drs.count - 1
Dim b As Integer
For b = 0 To drs(a)("缴费_月租_缴费月数") - 1
Dim da As DataRow = dt.AddNew()
da("分类") = e.Form.Controls("ComboBox1").Items(1)
da("住址_巷") = drs(a)("住址_巷")
da("住址_号") = drs(a)("住址_号")
da("住址_房") = drs(a)("住址_房")
da("姓名") = drs(a)("姓名")
da("手机号码") = drs(a)("手机号码")
da("车牌号码") = drs(a)("车牌号码")
da("缴费_月租_起计日期") = "#" & drs(a)("缴费_月租_起计日期") & "#"
da("缴费_月租_止计日期") = "#" & drs(a)("缴费_月租_止计日期") & "#"
da("查询_月租_所属日期") = drs(a)("缴费_月租_起计日期").AddMonths(b)
da("缴费_月租_月租单价") = drs(a)("缴费_月租_月租单价")
da("查询_月租_缴费金额") = drs(a)("缴费_月租_月租单价")
Next
Next
End If
With Tables("安费用所属期查询统计_Table1")
.SubtotalGroups.Clear()
LockBaseMainForm() '锁定主界面
.StopRedraw()
Dim bc As New GroupTableBuilder("统计表2",dt)
Dim dt1 As fxDataSource
bc.Filter = "[查询_月租_所属日期] >= #" & e.Form.Controls("StartDate").Value & "# And [查询_月租_所属日期] <= #" & e.Form.Controls("EndDate").Value & "#"
bc.Groups.AddDef("查询_月租_所属日期",DateGroupEnum.Year,"年")
bc.Groups.AddDef("查询_月租_所属日期",DateGroupEnum.Month,"月")
bc.Totals.AddDef("查询_月租_缴费金额")
dt1 = bc.BuildDataSource()
Dim bc1 As New SQLGroupTableBuilder("统计表3","缴费情况")
Dim dt2 As fxDataSource
bc1.Filter = "[分类] = '" & e.Form.Controls("ComboBox1").Items(2) & "'And [日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "#"
bc1.Groups.AddDef("日期",DateGroupEnum.Year,"年")
bc1.Groups.AddDef("日期",DateGroupEnum.Month,"月")
bc1.Totals.AddDef("缴费_临时停放_缴费金额")
bc1.Totals.AddDef("缴费_其他收入_金额")
dt2 = bc1.BuildDataSource()
Dim nms As String() = {"年","月"} '指定连接列
dt1.Combine(nms,dt2,nms) '将销售统计数据组合到进货统计数据
.DataSource = dt1
DataTables("安费用所属期查询统计_Table1").DataCols.Add("当月总收入",Gettype(Double),"IsNull([查询_月租_缴费金额],0) + ISNULL([缴费_临时停放_缴费金额],0) - ISNULL([缴费_其他收入_金额],0)")
Dim g1 As New Subtotalgroup
g1.GroupOn = "年" '分组列为客户列
g1.TotalOn = "查询_月租_缴费金额,缴费_临时停放_缴费金额,缴费_其他收入_金额,当月总收入" '对数量和金额进行统计
g1.Caption = "本年累计"
.SubtotalGroups.Add(g1)
'定义总计分组
Dim g2 As New Subtotalgroup
g2.GroupOn = "*"
g2.TotalOn = "查询_月租_缴费金额,缴费_临时停放_缴费金额,缴费_其他收入_金额,当月总收入"
.SubtotalGroups.Add(g2)
.Subtotal() '生成汇总模式
.DataTable.DataCols("查询_月租_缴费金额").SetFormat("#,###.##")
.DataTable.DataCols("缴费_临时停放_缴费金额").SetFormat("#,###.##")
.DataTable.DataCols("缴费_其他收入_金额").SetFormat("#,###.##")
.DataTable.DataCols("当月总收入").SetFormat("#,###.##")
.SetHeaderRowHeight(0,25,25)
.ResumeRedraw()
UnLockBaseMainForm() '解锁主界面
End With
End If