前面2课叙述了如何准备数据---将数据写入数据库，如何查询、导出和打印日报表。
对于月报表、年报表的操作其实同日报表一样，只是需要做一个系统后台程序，将数据库的每天/每月的数据进行计算，然后写入数据库的月表单和年表单即可。
对于月报表和日报表的界面可以参考设计如下：
对于后台运行程序设计代码如下：
Const g_conString = " Provider=MSDASQL.1;Persist Security Info=False;Data Source=SQL_KEP;Initial Catalog=SQL_KEP" '链接数据库
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Dim g_where As String
Dim g_where2 As String
Dim g_startDate As String
Dim g_mou As Boolean
Dim zou As Boolean
Dim yue As Boolean
Private Sub dayreport(i As Integer) '月报表数据
Dim strsql
Dim cn As ADODB.Connection
Dim record As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open g_conString
Set record = New ADODB.Recordset
st = Str(Date)
g_where = "tt>='" + Str(DateAdd("d", -1, st)) + " 0:20:0' and tt<='" + st + " 0:30:50'"
strsql = "select avg(t1),avg(t2),avg(t3),avg(t4),avg(t5),avg(t6),avg(t7),avg(t8),avg(t9),avg(t10),avg(t11),avg(t12),avg(t13),avg(t14),avg(t15),avg(t16),avg(t17),avg(t18) from tt1 where " + g_where
'计算本月的平均值插入tt2
record.Open strsql, cn
If record.EOF = False Then
V1 = Format(record.Fields(0).Value, "0.00")
V2 = Format(record.Fields(1).Value, "0.00")
V3 = Format(record.Fields(2).Value, "0.00")
V4 = Format(record.Fields(3).Value, "0.00")
V5 = Format(record.Fields(4).Value, "0.00")
V6 = Format(record.Fields(5).Value, "0.00")
V7 = Format(record.Fields(6).Value, "0.00")
V8 = Format(record.Fields(7).Value, "0.00")
V9 = Format(record.Fields(8).Value, "0.00")
V10 = Format(record.Fields(9).Value, "0.00")
V11 = Format(record.Fields(10).Value, "0.00")
V12 = Format(record.Fields(11).Value, "0.00")
V13 = Format(record.Fields(12).Value, "0.00")
V14 = Format(record.Fields(13).Value, "0.00")
V15 = Format(record.Fields(14).Value, "0.00")
V16 = Format(record.Fields(15).Value, "0.00")
V17 = Format(record.Fields(16).Value, "0.00")
V18 = Format(record.Fields(17).Value, "0.00")
End If
Set record = Nothing
Set record = New ADODB.Recordset
strsql = "insert INTO tt2 (tt,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18 ) VALUES ('" + Str(DateAdd("d", -1, st)) + "','" + V1 + "','" + V2 + "','" + V3 + "','" + V4 + "','" + V5 + "','" + V6 + "','" + V7 + "','" + V8 + "','" + V9 + "','" + V10 + "','" + V11 + "','" + V12 + "','" + V13 + "','" + V14 + "','" + V15 + "','" + V16 + "','" + V17 + "','" + V18 + "')"
'MsgBox strsql
record.Open strsql, cn
'record.Close
Set record = Nothing
cn.Close
Set cn = Nothing
End Sub
Private Sub moureport(i As Integer) '年报表
Dim strsql
Dim cn As ADODB.Connection
Dim record As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open g_conString
Set record = New ADODB.Recordset
st = Str(Date)
g_where = "tt>='" + Str(DateAdd("m", -1, st)) + " 0:0:0' and tt<='" + st + " 0:30:50'"
strsql = "select avg(t1),avg(t2),avg(t3),avg(t4),avg(t5),avg(t6),avg(t7),avg(t8),avg(t9),avg(t10),avg(t11),avg(t12),avg(t13),avg(t14),avg(t15) ,avg(t16),avg(t17),avg(t18)from tt2 where " + g_where
'计算本年月度平均值插入到tt3
record.Open strsql, cn
If record.EOF = False Then
V1 = Format(record.Fields(0).Value, "0.00")
V2 = Format(record.Fields(1).Value, "0.00")
V3 = Format(record.Fields(2).Value, "0.00")
V4 = Format(record.Fields(3).Value, "0.00")
V5 = Format(record.Fields(4).Value, "0.00")
V6 = Format(record.Fields(5).Value, "0.00")
V7 = Format(record.Fields(6).Value, "0.00")
V8 = Format(record.Fields(7).Value, "0.00")
V9 = Format(record.Fields(8).Value, "0.00")
V10 = Format(record.Fields(9).Value, "0.00")
V11 = Format(record.Fields(10).Value, "0.00")
V12 = Format(record.Fields(11).Value, "0.00")
V13 = Format(record.Fields(12).Value, "0.00")
V14 = Format(record.Fields(13).Value, "0.00")
V15 = Format(record.Fields(14).Value, "0.00")
V16 = Format(record.Fields(15).Value, "0.00")
V17 = Format(record.Fields(16).Value, "0.00")
V18 = Format(record.Fields(17).Value, "0.00")
End If
Set record = Nothing
Set record = New ADODB.Recordset
strsql = "insert INTO tt3 (tt,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18 ) VALUES ('" + Str(DateAdd("d", -1, st)) + "','" + V1 + "','" + V2 + "','" + V3 + "','" + V4 + "','" + V5 + "','" + V6 + "','" + V7 + "','" + V8 + "','" + V9 + "','" + V10 + "','" + V11 + "','" + V12 + "','" + V13 + "','" + V14 + "','" + V15 + "','" + V16 + "','" + V17 + "','" + V18 + "')"
'MsgBox strsql
record.Open strsql, cn
'record.Close
Set record = Nothing
cn.Close
Set cn = Nothing
End Sub
Private Sub Timer1_Timer()
If Hour(Now()) = 0 And g_mou = False Then
dayreport (1) '调用月报表插入数据
g_mou = True
End If
If Hour(Now()) = 0 And Day(Now()) = 1 And yue = False Then
moureport (1) '调用年报表插入数据
yue = True
End If
If Hour(Now()) > 0 Then
g_mou = False
yue = False
zou = False
End If
End Sub
收录于合集 #数据库
5个
上一篇基于物联网关OPC软件采集数据并存入数据库下一篇上位机外挂报表第2课--日报表设计