wincc 月报表

最新推荐文章于 2024-07-19 14:51:20 发布

杨小杨先生

最新推荐文章于 2024-07-19 14:51:20 发布

阅读量446

点赞数

文章标签： c语言

本文链接：https://blog.csdn.net/weixin_40704392/article/details/127817056

版权

Sub OnClick(ByVal Item)
Dim i, n, k, m, t, e, f, n1
Dim Sql, oCom, conn, oCom1
Dim j, a, b, c, d, z, sql1, a1, b1, c1
Dim ylp, wdp, llp, ylx, wdx, llx, yld, wdd, lld
Dim zlp, dyp, sdp, zlx, dyx, sdx, zld, dyd, sdd
Dim strcn, cn
Dim is_SQL
Dim oRs, oRs1
Dim By, Bm, Bd
Dim Ny, Nm, Nd
Dim MSFlexGrid2,Text2,Byear,Eyear,Bmon,Emon
Dim BeginDate, EndDate
Set Byear = ScreenItems(“Byear”)
Set Eyear = ScreenItems(“Eyear”)
Set Bmon = ScreenItems(“Bmon”)
Set Emon = ScreenItems(“Emon”)
Set Text2 = ScreenItems(“Text2”)
Set MSFlexGrid2 = ScreenItems(“MSFlexGrid2”)

BeginDate = Byear.Text & “-” & Bmon.Text
EndDate = Eyear.Text & “-” & Emon.Text

If Byear.Text > Eyear.Text Or Byear.Text = Eyear.Text And Bmon.Text > Emon.Text Then
MsgBox “输入的时间不正确”, vbOK, “错误的起始时间”
End If

Sql = “select convert(char(10),[riqi],120),avg(yali),avg(wendu),avg(liuliang), avg(zhongliang),avg(dianya),avg(sudu) from ribao where convert(char(7),[riqi],120) between '” & BeginDate & “’ and’” & EndDate & “’ group by convert(char(10),[riqi],120) order by 1”

sql1 = “select avg(yali)as ylp,avg(wendu)as wdp,avg(liuliang)as llp,avg(zhongliang)as zlp,avg(dianya)as dyp,avg(sudu)as sdp,min(yali)as ylx,min(wendu)as wdx,min(liuliang)As llx,min(zhongliang)As zlx,min(dianya)As dyx,min(sudu)As sdx,max(yali)As yld,max(wendu)As wdd,max(liuliang)As lld,max(zhongliang)As zld,max(dianya)As dyd,max(sudu)As sdd from (select top 100 percent convert(char(10) ,[riqi],120) As riqi,avg(yali) As yali ,avg(wendu) As wendu ,avg(liuliang) As liuliang ,avg(zhongliang) As zhongliang ,avg(dianya) As dianya ,avg(sudu) As sudu from ribao where convert(char(7),[riqi],120) between '” & BeginDate & “’ and’” & EndDate & “'group by convert(char(10),[riqi],120) order by 1) As t”
strcn = “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=baobiao1;Data Source=.\wincc”
Set conn = CreateObject(“ADODB.Connection”)
conn.ConnectionString = strcn
conn.CursorLocation = 3
conn.Open
'使用命令文本查询
Set oRs = CreateObject(“ADODB.Recordset”)

Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = Sql

Set oRs = oCom.Execute
n = oRs.RecordCount
Text2.Text = n
Set oCom1 = CreateObject(“ADODB.Command”)
oCom1.CommandType = 1
Set oCom1.ActiveConnection = conn
oCom1.CommandText = sql1
Set oRs1 = oCom1.Execute
n1 = oRs1.RecordCount
ylp = oRs1(“ylp”): wdp = oRs1(“wdp”): llp = oRs1(“llp”): ylx = oRs1(“ylx”): wdx = oRs1(“wdx”): llx = oRs1(“llx”): yld = oRs1(“yld”): wdd = oRs1(“wdd”): lld = oRs1(“lld”)
zlp = oRs1(“zlp”): dyp = oRs1(“dyp”): sdp = oRs1(“sdp”): zlx = oRs1(“zlx”): dyx = oRs1(“dyx”): sdx = oRs1(“sdx”): zld = oRs1(“zld”): dyd = oRs1(“dyd”): sdd = oRs1(“sdd”)
If n = 0 Then
MsgBox “对不起,没有找到符合条件的数据”, vbOK, “没有相关数据”
End If
oRs.Requery

MSFlexGrid2.Clear
MSFlexGrid2.Rows = oRs.RecordCount + 6
MSFlexGrid2.ColWidth(0) = 900
MSFlexGrid2.ColWidth(1) = 1600
MSFlexGrid2.ColWidth(2) = 1000
MSFlexGrid2.ColWidth(3) = 1000
MSFlexGrid2.ColWidth(4) = 1000
MSFlexGrid2.ColWidth(5) = 1000
MSFlexGrid2.ColWidth(6) = 1000
MSFlexGrid2.ColWidth(7) = 1000

MSFlexGrid2.Row = 0
For z = 0 To 7
MSFlexGrid2.Col = z
MSFlexGrid2.Text = “R980履带式布料机月报表”

Next
MSFlexGrid2.MergeCells = 4
MSFlexGrid2.MergeRow(0) = True

MSFlexGrid2.TextMatrix(1, 0) = “编号”
MSFlexGrid2.TextMatrix(1, 1) = “日期”
MSFlexGrid2.TextMatrix(1, 2) = “压力”
MSFlexGrid2.TextMatrix(1, 3) = “温度”
MSFlexGrid2.TextMatrix(1, 4) = “流量”
MSFlexGrid2.TextMatrix(1, 5) = “重量”
MSFlexGrid2.TextMatrix(1, 6) = “电压”
MSFlexGrid2.TextMatrix(1, 7) = “速度”

  MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 0) = "最大值"
   MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 0) = "最小值"
    MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 0) = "平均值"

MSFlexGrid2.ColAlignment(0) = 4
MSFlexGrid2.ColAlignment(1) = 4
MSFlexGrid2.ColAlignment(2) = 4
MSFlexGrid2.ColAlignment(3) = 4
MSFlexGrid2.ColAlignment(4) = 4
MSFlexGrid2.ColAlignment(5) = 4
MSFlexGrid2.ColAlignment(6) = 4
MSFlexGrid2.ColAlignment(7) = 4
For i = 1 To oRs.RecordCount
MSFlexGrid2.TextMatrix(i + 1, 0) = i
Next

If (n > 0) Then
oRs.MoveFirst

    i = 0

Do While Not oRs.EOF
n = n + 1
yld = Int(yld * 10 ^ 3 + 0.5) / (10 ^ 3)
wdd = Int(wdd * 10 ^ 3 + 0.5) / (10 ^ 3)
lld = Int(lld * 10 ^ 3 + 0.5) / (10 ^ 3)

zld = Int(zld * 10 ^ 3 + 0.5) / (10 ^ 3)
dyd = Int(dyd * 10 ^ 3 + 0.5) / (10 ^ 3)
sdd = Int(sdd * 10 ^ 3 + 0.5) / (10 ^ 3)

zlx = Int(zlx * 10 ^ 3 + 0.5) / (10 ^ 3)
dyx = Int(dyx * 10 ^ 3 + 0.5) / (10 ^ 3)
sdx = Int(sdx * 10 ^ 3 + 0.5) / (10 ^ 3)

ylx = Int(ylx * 10 ^ 3 + 0.5) / (10 ^ 3)
wdx = Int(wdx * 10 ^ 3 + 0.5) / (10 ^ 3)
llx = Int(llx * 10 ^ 3 + 0.5) / (10 ^ 3)
ylp = Int(ylp * 10 ^ 3 + 0.5) / (10 ^ 3)
wdp = Int(wdp * 10 ^ 3 + 0.5) / (10 ^ 3)
llp = Int(llp * 10 ^ 3 + 0.5) / (10 ^ 3)
zlp = Int(zlp * 10 ^ 3 + 0.5) / (10 ^ 3)
dyp = Int(dyp * 10 ^ 3 + 0.5) / (10 ^ 3)
sdp = Int(sdp * 10 ^ 3 + 0.5) / (10 ^ 3)
i = i + 1

a = CStr(oRs.Fields(1).Value)
a = Int(a * 10 ^ 3 + 0.5) / (10 ^ 3)
b = CStr(oRs.Fields(2).Value)
b = Int(b * 10 ^ 3 + 0.5) / (10 ^ 3)
c = CStr(oRs.Fields(3).Value)
c = Int(c * 10 ^ 3 + 0.5) / (10 ^ 3)
'*****************************
a1 = CStr(oRs.Fields(4).Value)
a1 = Int(a1 * 10 ^ 3 + 0.5) / (10 ^ 3)
b1 = CStr(oRs.Fields(5).Value)
b1 = Int(b1 * 10 ^ 3 + 0.5) / (10 ^ 3)
c1 = CStr(oRs.Fields(6).Value)
c1 = Int(c1 * 10 ^ 3 + 0.5) / (10 ^ 3)
'********************************
MSFlexGrid2.TextMatrix(i + 1, 1) = CStr(oRs.Fields(0).Value)
MSFlexGrid2.TextMatrix(i + 1, 2) = a
MSFlexGrid2.TextMatrix(i + 1, 3) = b
MSFlexGrid2.TextMatrix(i + 1, 4) = c
MSFlexGrid2.TextMatrix(i + 1, 5) = a1
MSFlexGrid2.TextMatrix(i + 1, 6) = b1
MSFlexGrid2.TextMatrix(i + 1, 7) = c1

MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 2) = yld
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 2) = ylx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 2) = ylp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 3) = wdd
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 3) = wdx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 3) = wdp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 4) = lld
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 4) = llx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 4) = llp
'*********************************************
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 5) = zld
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 5) = zlx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 5) = zlp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 6) = dyd
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 6) = dyx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 6) = dyp
MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 7) = sdd
MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 7) = sdx
MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 7) = sdp