📄 frmtj.frm
字号:
VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Begin VB.Form frmTJ
Caption = "操作票统计"
ClientHeight = 3195
ClientLeft = 60
ClientTop = 345
ClientWidth = 3630
LinkTopic = "Form1"
MaxButton = 0 'False
MDIChild = -1 'True
MinButton = 0 'False
ScaleHeight = 3195
ScaleWidth = 3630
Begin VB.CommandButton Command2
Caption = "详单打印"
Height = 375
Left = 2160
TabIndex = 8
Top = 2520
Width = 1095
End
Begin MSComCtl2.DTPicker DTPicker1
Height = 375
Left = 1080
TabIndex = 5
Top = 1680
Width = 1335
_ExtentX = 2355
_ExtentY = 661
_Version = 393216
CustomFormat = "yyyy-MM"
Format = 61210627
UpDown = -1 'True
CurrentDate = 37915
End
Begin VB.Frame Frame2
Height = 1335
Left = 1920
TabIndex = 4
Top = 120
Width = 1575
Begin VB.OptionButton Option2
Caption = "月统计"
Height = 375
Left = 240
TabIndex = 7
Top = 840
Value = -1 'True
Width = 1215
End
Begin VB.OptionButton Option1
Caption = "年统计"
Height = 495
Left = 240
TabIndex = 6
Top = 240
Width = 1095
End
End
Begin VB.Frame Frame1
Height = 1335
Left = 120
TabIndex = 1
Top = 120
Width = 1455
Begin VB.OptionButton Option4
Caption = "逐项操作票"
Height = 255
Left = 120
TabIndex = 3
Top = 840
Width = 1215
End
Begin VB.OptionButton Option3
Caption = "综合操作票"
Height = 375
Left = 120
TabIndex = 2
Top = 240
Value = -1 'True
Width = 1215
End
End
Begin VB.CommandButton Command1
Caption = "统 计"
Height = 375
Left = 360
TabIndex = 0
Top = 2520
Width = 1095
End
End
Attribute VB_Name = "frmTJ"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
Dim sendexcel As Excel.Application
If Option2.Value Then
If Option3.Value Then
sql1 = "select count(zlph) from xdgl_zhczpzb where rq between '" & Format(DTPicker1.Value, "yyyy-mm-01") & "' and '" & DateAdd("d", -1, DateAdd("m", 1, Format(DTPicker1.Value, "yyyy-mm-01"))) & "' "
sql2 = "select count(zlph) from xdgl_zhczpzb where rq between '" & Format(DTPicker1.Value, "yyyy-mm-01") & "' and '" & DateAdd("d", -1, DateAdd("m", 1, Format(DTPicker1.Value, "yyyy-mm-01"))) & "' and zxjg='已执行'"
sql3 = "select count(zlph) from xdgl_zhczpzb where rq between '" & Format(DTPicker1.Value, "yyyy-mm-01") & "' and '" & DateAdd("d", -1, DateAdd("m", 1, Format(DTPicker1.Value, "yyyy-mm-01"))) & "' and zxjg='已执行' and pjjg='合格'"
Else
sql1 = "select count(zxlph) from xdgl_zxczpzb where zxsj between '" & Format(DTPicker1.Value, "yyyy-mm-01") & "' and '" & DateAdd("d", -1, DateAdd("m", 1, Format(DTPicker1.Value, "yyyy-mm-01"))) & "' "
sql2 = "select count(zxlph) from xdgl_zxczpzb where zxsj between '" & Format(DTPicker1.Value, "yyyy-mm-01") & "' and '" & DateAdd("d", -1, DateAdd("m", 1, Format(DTPicker1.Value, "yyyy-mm-01"))) & "' and zxqk='已执行'"
sql3 = "select count(zxlph) from xdgl_zxczpzb where zxsj between '" & Format(DTPicker1.Value, "yyyy-mm-01") & "' and '" & DateAdd("d", -1, DateAdd("m", 1, Format(DTPicker1.Value, "yyyy-mm-01"))) & "' and zxqk='已执行' and pjqk='合格'"
End If
Else
If Option3.Value Then
sql1 = "select count(zlph) from xdgl_zhczpzb where rq between '" & Format(DTPicker1.Value, "yyyy-01") & "' and '" & Format(DTPicker1.Value, "yyyy-12") & "' "
sql2 = "select count(zlph) from xdgl_zhczpzb where rq between '" & Format(DTPicker1.Value, "yyyy-01") & "' and '" & Format(DTPicker1.Value, "yyyy-12") & "' and zxjg='已执行'"
sql3 = "select count(zlph) from xdgl_zhczpzb where rq between '" & Format(DTPicker1.Value, "yyyy-01") & "' and '" & Format(DTPicker1.Value, "yyyy-12") & "' and zxjg='已执行' and pjjg='合格'"
Else
sql1 = "select count(zxlph) from xdgl_zxczpzb where zxsj between '" & Format(DTPicker1.Value, "yyyy-01") & "' and '" & Format(DTPicker1.Value, "yyyy-12") & "' "
sql2 = "select count(zxlph) from xdgl_zxczpzb where zxsj between '" & Format(DTPicker1.Value, "yyyy-01") & "' and '" & Format(DTPicker1.Value, "yyyy-12") & "' and zxqk='已执行'"
sql3 = "select count(zxlph) from xdgl_zxczpzb where zxsj between '" & Format(DTPicker1.Value, "yyyy-01") & "' and '" & Format(DTPicker1.Value, "yyyy-12") & "' and zxqk='已执行' and pjqk='合格'"
End If
End If
Call Open_link
Set RS = ZHCX.Execute(sql1, 1)
Debug.Print sql1
If Not RS.EOF Then
temp1 = RS(0)
End If
If RS.State Then
RS.Close
End If
Set RS = ZHCX.Execute(sql2, 0)
If Not RS.EOF Then
temp2 = RS(0)
End If
If RS.State Then
RS.Close
End If
Set RS = ZHCX.Execute(sql3, 0)
If Not RS.EOF Then
temp3 = RS(0)
End If
If RS.State Then
RS.Close
End If
Call Close_link
Set sendexcel = CreateObject("Excel.Application")
sendexcel.Visible = True
sendexcel.Workbooks.Add
sendexcel.Range("A1:k1").Select
With sendexcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
If Option3.Value Then
sendexcel.Cells(1, 1).Value = Format(DTPicker1.Value, "yyyy年mm月") + "综合操作指令票统计评价情况"
Else
sendexcel.Cells(1, 1).Value = Format(DTPicker1.Value, "yyyy年mm月") + "逐项操作指令票统计评价情况"
End If
With sendexcel.Selection.Font
.Name = "隶书"
.Size = 24
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
sendexcel.Selection.Font.Bold = True
sendexcel.Selection.Merge
sendexcel.Range("e3:h3").Select
With sendexcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
sendexcel.Selection.Merge
sendexcel.Cells(3, 5).Value = "拟票 " + CStr(temp1) + " 张"
With sendexcel.Selection.Font
.Name = "宋体"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
sendexcel.Selection.Font.Bold = True
sendexcel.Range("e6:h6").Select
With sendexcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
sendexcel.Selection.Merge
sendexcel.Cells(6, 5).Value = "执行 " + CStr(temp2) + " 张"
With sendexcel.Selection.Font
.Name = "宋体"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
sendexcel.Selection.Font.Bold = True
sendexcel.Range("e9:H9").Select
With sendexcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
sendexcel.Selection.Merge
If temp3 = 0 Then
Else
sendexcel.Cells(9, 5).Value = "合格率： " + CStr(CDbl(Format(temp3 / temp2, "0.0000")) * 100) + "%"
End If
With sendexcel.Selection.Font
.Name = "宋体"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
sendexcel.Selection.Font.Bold = True
sendexcel.Range("E12:H12").Select
With sendexcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
sendexcel.Selection.Merge
With sendexcel.Selection.Font
.Name = "宋体"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
sendexcel.Cells(12, 5).Value = "评价审核人签字："
sendexcel.Selection.Font.Bold = True
sendexcel.Rows("1:1").Select
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -