上位机外挂报表第3课---完结篇（上位机软件界面用什么做）

前面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课--日报表设计