959
機房收費係統之周結賬單
在做係統的過程中，應該說報表這塊兒，尤其是周結賬和日結賬這塊是最繁瑣的了，我用了3、4天吧，拿下了這塊兒。
首先看一下這個周結賬單
這個窗體的控件我也找了很長時間才找到，顯示時間的是DTpicker，日曆是monthview。
首先實例化報表
Dim WithEvents report As grproLibCtl.GridppReport '實例化報表
在報表中初始化日期、用戶名
Private Sub report_initialize() '初始化數據
report.ParameterByName("username").AsString = username
report.ParameterByName("datestart").AsString = Format(startDate.Value, "yyyy-mm-dd")
report.ParameterByName("dateend").AsString = Format(endDate.Value, "yyyy-mm-dd")
End Sub
下麵看一下我的周結賬單是如何計算的
Private Sub cmdUpdate_Click()
Dim strMsg As String
Dim strSqlRe As String
Dim mrcRe As ADODB.Recordset
Dim strSqlCan As String
Dim mrcCan As ADODB.Recordset
Dim strSqlWeek As String
Dim mrcWeek As ADODB.Recordset
Dim strSQLL As String
Dim mrcL As ADODB.Recordset
Dim Toconsume As Long
Dim consume As Long
Dim dateIndex As Date
Dim datestart As Date
Dim dateend As Date
Dim Toremain As Integer
Dim Torecharge As Integer
Dim Tocancel As Integer
Dim remain As Integer
Dim charge As Integer
Dim cancel As Integer
Dim all As Long
'GRDisplayViewer1.Refresh
datestart = Format(startDate.Value, "yyyy-mm-dd")
dateend = Format(endDate.Value, "yyyy-mm-dd")
'刪除周表中的數據
strSqlWeek = "delete from checkweek_info "
Set mrcWeek = executeSQL(strSqlWeek, strMsg)
For dateIndex = datestart To dateend
remain = 0
Recharge = 0
consume = 0
cancel = 0
all = 0
'上期充值卡餘額計算
If dateIndex = datestart Then
remain = 0
Call Form_Load
Else
'先算總的recharge
strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date<='" & Format(dateIndex - 1, "yyyy-mm-dd") & "'"
Set mrcRe = executeSQL(strSqlRe, strMsg)
If Not IsNull(mrcRe!rechargecash) Then
Recharge = mrcRe!rechargecash
mrcRe.Close
End If
'總的cancelcard
strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date<='" & Format(dateIndex - 1, "yyyy-mm-dd") & "'"
Set mrcCan = executeSQL(strSqlCan, strMsg)
If Not IsNull(mrcCan!cancel) Then
cancel = mrcCan!cancel
mrcCan.Close
End If
'總的consume
strSQLL = "select sum(consume) as consumecash from line_info where offdate<= '" & Format(dateIndex - 1, "yyyy-mm-dd") & "'"
Set mrcL = executeSQL(strSQLL, strMsg)
If Not IsNull(mrcL!consumeCash) Then
consume = mrcL!consumeCash
mrcL.Close
End If
'上期充值卡餘額
remain = Recharge - consume - cancel
'本期充值金額
strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date='" & Format(dateIndex, "yyyy-mm-dd") & "'"
Set mrcRe = executeSQL(strSqlRe, strMsg)
If Not IsNull(mrcRe!rechargecash) Then
Torecharge = mrcRe!rechargecash
mrcRe.Close
End If
'本期退卡
strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date='" & Format(dateIndex, "yyyy-mm-dd") & "'"
Set mrcCan = executeSQL(strSqlCan, strMsg)
If Not IsNull(mrcCan!cancel) Then
Tocancel = mrcCan!cancel
mrcCan.Close
End If
'本期消費
strSQLL = "select sum(consume) as consumecash from line_info where offdate= '" & Format(dateIndex, "yyyy-mm-dd") & "'"
Set mrcL = executeSQL(strSQLL, strMsg)
If Not IsNull(mrcL!consumeCash) Then
Toconsume = mrcL!consumeCash
mrcL.Close
End If
'本期充值卡餘額
all = remain + Torecharge - Toconsume - Tocancel
strSqlWeek = "select * from checkweek_info "
Set mrcWeek = executeSQL(strSqlWeek, strMsg)
mrcWeek.AddNew
mrcWeek.Fields("remaincash") = remain
mrcWeek.Fields("rechargecash") = Torecharge
mrcWeek.Fields("consumecash") = Toconsume
mrcWeek.Fields("cancelcash") = Tocancel
mrcWeek.Fields("allcash") = all
mrcWeek.Fields("date") = Format(dateIndex, "yyyy-mm-dd")
mrcWeek.Update
mrcWeek.Close
End If
Next
Call Form_Load
End Sub
Private Sub Form_Load()
Dim strSQL As String
Dim strMsg As String
Dim mrc As ADODB.Recordset
Dim strSqlRe As String
Dim mrcRe As ADODB.Recordset
Dim strSqlCan As String
Dim mrcCan As ADODB.Recordset
Dim strSqlWeek As String
Dim mrcWeek As ADODB.Recordset
Dim strSQLL As String
Dim mrcL As ADODB.Recordset
Dim Toconsume As Long
Dim consume As Long
Dim remain As Integer
Dim Recharge As Integer
Dim cancel As Integer
Dim all As Integer
Dim Toremain As Integer
Dim Torecharge As Integer
Dim Tocancel As Integer
MonthViewStart.Visible = False
MonthViewEnd.Visible = False
strSQL = "select * from checkweek_info where date between '" & Format(startDate.Value, "yyyy-mm-dd") & "' and '" & Format(endDate.Value, "yyyy-mm-dd") & "'"
Set mrc = executeSQL(strSQL, strMsg)
strSQL = strSQL & "order by date"
Set report = New grproLibCtl.GridppReport
GRDisplayViewer1.Stop
report.LoadFromFile (App.Path & "\周結賬單.grf")
report.DetailGrid.Recordset.ConnectionString = connectstring()
report.DetailGrid.Recordset.QuerySQL = strSQL
GRDisplayViewer1.report = report
GRDisplayViewer1.Start
strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date<='" & Format(startDate.Value - 1, "yyyy-mm-dd") & "'"
Set mrcRe = executeSQL(strSqlRe, strMsg)
If Not IsNull(mrcRe!rechargecash) Then
Recharge = mrcRe!rechargecash
mrcRe.Close
End If
'總的cancelcard
strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date<='" & Format(startDate.Value - 1, "yyyy-mm-dd") & "'"
Set mrcCan = executeSQL(strSqlCan, strMsg)
If Not IsNull(mrcCan!cancel) Then
cancel = mrcCan!cancel
mrcCan.Close
End If
'總的consume
strSQLL = "select sum(consume) as consumecash from line_info where offdate<= '" & Format(startDate.Value - 1, "yyyy-mm-dd") & "'"
Set mrcL = executeSQL(strSQLL, strMsg)
If Not IsNull(mrcL!consumeCash) Then
consume = mrcL!consumeCash
mrcL.Close
End If
'上期充值卡餘額
remain = Recharge - consume - cancel
'本期充值金額
strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date='" & Format(startDate.Value, "yyyy-mm-dd") & "'"
Set mrcRe = executeSQL(strSqlRe, strMsg)
If Not IsNull(mrcRe!rechargecash) Then
Torecharge = mrcRe!rechargecash
mrcRe.Close
End If
'本期退卡
strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date='" & Format(startDate.Value, "yyyy-mm-dd") & "'"
Set mrcCan = executeSQL(strSqlCan, strMsg)
If Not IsNull(mrcCan!cancel) Then
Tocancel = mrcCan!cancel
mrcCan.Close
End If
'本期消費
strSQLL = "select sum(consume) as consumecash from line_info where offdate= '" & Format(startDate.Value, "yyyy-mm-dd") & "'"
Set mrcL = executeSQL(strSQLL, strMsg)
If Not IsNull(mrcL!consumeCash) Then
Toconsume = mrcL!consumeCash
mrcL.Close
End If
'本期充值卡餘額
all = remain + Torecharge - Toconsume - Tocancel
strSqlWeek = "select * from checkweek_info "
Set mrcWeek = executeSQL(strSqlWeek, strMsg)
mrcWeek.AddNew
mrcWeek.Fields("remaincash") = remain
mrcWeek.Fields("rechargecash") = Torecharge
mrcWeek.Fields("consumecash") = Toconsume
mrcWeek.Fields("cancelcash") = Tocancel
mrcWeek.Fields("allcash") = all
mrcWeek.Fields("date") = Format(startDate.Value, "yyyy-mm-dd")
mrcWeek.Update
End Sub
我的方法很麻煩，這是我使勁想出來的，大家有好的方法可以給我一個連接哦，讓我借鑒一下。
最後更新：2017-04-03 15:22:13
上一篇：
URAL 1204 中國剩餘定理
下一篇： java學習路線圖------（java1234）
- Linux Debugging（三）: C++函數調用的參數傳遞方法總結（通過gdb+反匯編）
- XP用戶噩耗來襲：微軟將於2014年停止其技術支持
- 前端開發中的柯裏化
- Linux平台使用二進製包將MySQL 5.1安裝到個人目錄簡易指南
- 雲服務器 ECS 實例自定義數據：自定義 yum 源、NTP 服務和 DNS 服務
- 員工(人才),留得住是因為本身就留得住；留不住的,你永遠留不住 .
- 《Spring 5 官方文檔》1. Spring入門指南（二）
- shell 循環刷新輸出方法
- 手把手 | 30行JavaScript代碼，教你分分鍾創建神經網絡
- 《HttpClient官方文檔》2.6 連接維持存活策略