|
前面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 |
|
|
