If e.Form.Controls("ComboBox1").Value = "" Then'当日期为空取消保存操作
messagebox.show("请选择分类")
Return
End If
If e.Form.Controls("ComboBox1").Value = e.Form.Controls("ComboBox1").Items(1) Then
If e.Form.Controls("StartDate").Value = Nothing OrElse e.Form.Controls("EndDate").Value = Nothing Then '当日期为空取消保存操作
messagebox.show("请选择起止日期")
Return
Else
Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.CommandText = "Select [_Identify],分类,住址_巷,住址_号,住址_房,姓名,手机号码,车牌号码,缴费_月租_起计日期,缴费_月租_止计日期,缴费_月租_月租单价,备注 fro m {缴费情况} Where [缴费_月租_起计日期] >= #" & e.Form.Controls("StartDate").Value & "# And [缴费_月租_止计日期] <= #" & e.Form.Controls("EndDate").Value & "#"
dt = cmd.ExecuteReader()
If dt.DataCols.Contains("查询_月租_有效起计日期") = False And dt.DataCols.Contains("查询_月租_有效止计日期") = False And dt.DataCols.Contains("查询_月租_缴费月数") = False And dt.DataCols.Contains("查询_月租_缴费金额") = False Then
dt.DataCols.add("查询_月租_有效起计日期", Gettype(Date))
dt.DataCols.add("查询_月租_有效止计日期", Gettype(Date))
dt.DataCols.add("查询_月租_缴费月数", Gettype(Double))
dt.DataCols.add("查询_月租_缴费金额", Gettype(Double))
End If
If dt.DataRows.Count > 0 Then
For Each dr As DataRow In dt.DataRows
If dr("缴费_月租_起计日期") <= "#" & e.Form.Controls("StartDate").Value & "#" Then
dr("查询_月租_有效起计日期") = "#" & e.Form.Controls("StartDate").Value & "#"
ElseIf dr("缴费_月租_起计日期") > "#" & e.Form.Controls("StartDate").Value & "#" Then
dr("查询_月租_有效起计日期") = dr("缴费_月租_起计日期")
End If
If dr("缴费_月租_止计日期") >= "#" & e.Form.Controls("EndDate").Value Then
dr("查询_月租_有效止计日期") = "#" & e.Form.Controls("EndDate").Value & "#"
ElseIf dr("缴费_月租_止计日期") < "#" & e.Form.Controls("EndDate").Value & "#" Then
dr("查询_月租_有效止计日期") = dr("缴费_月租_止计日期")
End If
dr("查询_月租_缴费月数") = DateDiff("m",dr("查询_月租_有效起计日期"),dr("查询_月租_有效止计日期")) + 1
dr("查询_月租_缴费金额") = (DateDiff("m",dr("查询_月租_有效起计日期"),dr("查询_月租_有效止计日期")) + 1) * dr("缴费_月租_月租单价")
Next
End If
Dim Filter As String
Dim f2 As String
Filter = "分类 = '" & e.Form.Controls("ComboBox1").Value & "'"
'With e.Form.Controls("StartDate")
'If .Value IsNot Nothing Then
'f2 = "[缴费_月租_起计日期] <= #" & .Value & "# And [缴费_月租_止计日期] >= #" & .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 & "# And [缴费_月租_止计日期] >= #" & .Value & "#"
'End If
'End With
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
With Tables("安费用所属期查询统计_Table1")
For i As Integer = .Rows.count - 1 To 0 Step -1
.Rows(i).Delete
Next
LockBaseMainForm() '锁定主界面
.StopRedraw()
For i As Integer = DataTables("缴费情况").DataRows.count - 1 To 0 Step -1
Dim tr As DataRow = DataTables("缴费情况").DataRows(i)
If tr.Isnull("车牌号码") Then
tr.delete
End If
Next
.Fill("Select [_Identify],分类,住址_巷,住址_号,住址_房,姓名,手机号码,车牌号码,查询_月租_有效起计日期,查询_月租_有效止计日期,缴费_月租_月租单价,查询_月租_缴费月数,查询_月租_缴费金额,备注 fro m {dt} Where " & Filter,False)
Dim t As DataTable = e.Form.Controls("Table1").Table.DataTable
.SetColVisibleWidth("分类|60|住址_巷|60|住址_号|30|住址_房|30|姓名|50|手机号码|80|车牌号码|70|查询_月租_有效起计日期|70|查询_月租_有效止计日期|70|查询_月租_缴费月数|60|缴费_月租_月租单价|60|查询_月租_缴费金额|60|备注|127")
.SetHeaderRowHeight(0,0,25)
If .Rows.Count < 15 Then
.AddNew(15 - .Rows.Count)
End If
.ResumeRedraw()
UnLockBaseMainForm() '解锁主界面
.Cols("查询_月租_缴费金额").GrandTotal = True
.GrandTotal = True
End With
End If
End If
DataTables("安费用所属期查询统计_Table1").DataCols("查询_月租_缴费金额").SetFormat("#,###.##")