📄 manager.frm
字号:
Strikethrough = 0 'False
EndProperty
CustomFormat = "yyyy - MM - dd"
Format = 16449539
CurrentDate = 38791
End
Begin MSComCtl2.DTPicker adcEnd
Height = 375
Left = 2880
TabIndex = 17
Top = 7200
Width = 2415
_ExtentX = 4260
_ExtentY = 661
_Version = 393216
BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
CustomFormat = "yyyy - MM - dd"
Format = 16449539
CurrentDate = 38791
End
Begin VB.Label Label5
Caption = "Start From:"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 255
Left = 240
TabIndex = 20
Top = 6960
Width = 2295
End
Begin VB.Label Label4
Caption = "End To:"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = 2880
TabIndex = 19
Top = 6960
Width = 855
End
Begin VB.Label adcLabel
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = 240
TabIndex = 18
Top = 6480
Width = 10815
End
Begin VB.Label pcuLabel
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = -74760
TabIndex = 13
Top = 6480
Width = 10815
End
Begin VB.Label Label2
Caption = "End To:"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = -72120
TabIndex = 12
Top = 6960
Width = 855
End
Begin VB.Label Label1
Caption = "Start From:"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 255
Left = -74760
TabIndex = 11
Top = 6960
Width = 2295
End
Begin VB.Label Label15
Caption = "Start From:"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 255
Left = -74760
TabIndex = 6
Top = 6960
Width = 2295
End
Begin VB.Label Label14
Caption = "End To:"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = -72120
TabIndex = 5
Top = 6960
Width = 855
End
End
End
Attribute VB_Name = "Display"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private xlExcelBook As Excel.Workbook
Private xlExcelApp As Excel.Application
Private Sub adcDB_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
adcDB.SortKey = ColumnHeader.Index - 1
' Set Sorted to True to sort the list.
If adcDB.SortOrder = lvwAscending Then
adcDB.SortOrder = lvwDescending
Else
adcDB.SortOrder = lvwAscending
End If
adcDB.Sorted = True
End Sub
Private Sub adcDB_DblClick()
Dim xlSheet As Object
Dim i As Integer, j As Integer
Dim Filename As String
Filename = InputBox("Enter file to save:", "File Save", "C:\Abandoned Calls.xls")
If Filename = "" Then
Exit Sub
End If
On Error GoTo DatabaseErr
Set xlExcelApp = CreateObject("Excel.Application")
'create the excel workbook object for resultant file
Set xlExcelBook = xlExcelApp.Workbooks.Add
Set xlSheet = xlExcelBook.Worksheets(1)
For i = 1 To adcDB.ColumnHeaders.Count
xlSheet.Cells(1, i).Value = adcDB.ColumnHeaders(i).Text
Next i
For i = 1 To adcDB.ListItems.Count
xlSheet.Cells(i + 1, 1) = "'" & adcDB.ListItems(i).Text
For j = 1 To adcDB.ColumnHeaders.Count - 1
xlSheet.Cells(i + 1, j + 1).Value = "'" & adcDB.ListItems(i).ListSubItems(j).Text
Next j
Next i
xlExcelBook.SaveAs Filename
MsgBox "Report export to " & Filename
xlExcelBook.Save
xlExcelBook.Close
xlExcelApp.Quit
Set xlSheet = Nothing
Set xlExcelBook = Nothing
Set xlExcelApp = Nothing
Exit Sub
DatabaseErr:
MsgBox Err.Description
End Sub
Private Sub adcDisplay_Click()
Dim strMessage As String
Dim cnn As ADODB.Connection
Dim cmdQuery As ADODB.Command
Dim rstRows As ADODB.Recordset
Dim controlid As String, start_dttm As String, end_dttm As String
Dim leavevm_dttm As String, leavevm_dttms As String
Dim cso_dttm As String, cso_dttms As String
Dim calls As Long, abandonedcalls As Long
Dim hours As Integer, cli As String
Dim startdttm As String, enddttm As String
adcDisplay.Enabled = False
startdttm = Format(adcStart.Value, "YYYY-MM-DD") & " 00:00:00"
enddttm = Format(adcEnd.Value, "YYYY-MM-DD") & " 00:00:00"
adcDB.Sorted = False
adcDB.ListItems.Clear
adcDB.View = lvwReport
adcDB.ColumnHeaders.Clear
' Add four ColumnHeaders.
adcDB.ColumnHeaders.Add , , "Control ID", 1700
adcDB.ColumnHeaders.Add , , "CLI", 1000
adcDB.ColumnHeaders.Add , , "Hour", 600
adcDB.ColumnHeaders.Add , , "Call starttime", 1500
adcDB.ColumnHeaders.Add , , "Queue starttime", 1500
adcDB.ColumnHeaders.Add , , "CSO answertime", 1500
adcDB.ColumnHeaders.Add , , "Call endtime", 1500
adcDB.ColumnHeaders.Add , , "Leave voicemail", 700
adcDB.ColumnHeaders.Add , , "Abondanded call", 700
calls = 0
abandonedcalls = 0
' Userinfo
Set cnn = Nothing
strMessage = "provider=MSDASQL;driver={IBM DB2 ODBC Driver};uid=db2admin;pwd=07646513;dbalias=crms;"
'strMessage = "provider=MSDASQL;driver={IBM DB2 ODBC Driver};uid=db2admin;pwd=db2admin;dbalias=crms;"
Set cnn = New Connection
Call cnn.Open(strMessage)
Set cmdQuery = New ADODB.Command
cmdQuery.ActiveConnection = cnn
strMessage = "select controlid, cli, HOUR(start_dttm), start_dttm, end_dttm, cso_dttm, cso_dttms, leavevm_dttm, leavevm_dttms from crms.phonenet_usage where start_dttm between '" & _
startdttm & "' and '" & enddttm & "' order by start_dttm asc"
cmdQuery.CommandText = strMessage
Set rstRows = cmdQuery.Execute
Do While Not rstRows.EOF
controlid = IIf(IsNull(rstRows.Fields(0).Value), "", Trim(rstRows.Fields(0).Value))
cli = IIf(IsNull(rstRows.Fields(1).Value), "", Trim(rstRows.Fields(1).Value))
hours = IIf(IsNull(rstRows.Fields(2).Value), 0, rstRows.Fields(2).Value)
start_dttm = IIf(IsNull(rstRows.Fields(3).Value), "", Format(rstRows.Fields(3).Value, "DD/MM/YYYY hh:mm"))
end_dttm = IIf(IsNull(rstRows.Fields(4).Value), "", Format(rstRows.Fields(4).Value, "DD/MM/YYYY hh:mm"))
cso_dttm = IIf(IsNull(rstRows.Fields(5).Value), "", Format(rstRows.Fields(5).Value, "DD/MM/YYYY hh:mm"))
cso_dttms = IIf(IsNull(rstRows.Fields(6).Value), "", Format(rstRows.Fields(6).Value, "DD/MM/YYYY hh:mm"))
leavevm_dttm = IIf(IsNull(rstRows.Fields(7).Value), "", Format(rstRows.Fields(7).Value, "DD/MM/YYYY hh:mm"))
leavevm_dttms = IIf(IsNull(rstRows.Fields(8).Value), "", Format(rstRows.Fields(8).Value, "DD/MM/YYYY hh:mm"))
calls = calls + 1
Set xItem = adcDB.ListItems.Add(Key:="A" & adcDB.ListItems.Count, Text:=controlid)
xItem.ListSubItems.Add Key:="CLI", Text:=cli
xItem.ListSubItems.Add Key:="Hour", Text:=hours
xItem.ListSubItems.Add Key:="Call starttime", Text:=start_dttm
xItem.ListSubItems.Add Key:="Queue starttime", Text:=cso_dttm
xItem.ListSubItems.Add Key:="CSO answertime", Text:=cso_dttms
If IsNull(rstRows.Fields(8).Value) Then
xItem.ListSubItems.Add Key:="Call endtime", Text:=end_dttm
Else
xItem.ListSubItems.Add Key:="Call endtime", Text:=leavevm_dttms
End If
If IsNull(rstRows.Fields(7).Value) Then
xItem.ListSubItems.Add Key:="Leave voicemail", Text:="-"
Else
xItem.ListSubItems.Add Key:="Leave voicemail", Text:="Y"
End If
If IsNull(rstRows.Fields(5).Value) Then
xItem.ListSubItems.Add Key:="Abondanded call", Text:="-"
Else
If IsNull(rstRows.Fields(6).Value) Then
abandonedcalls = abandonedcalls + 1
xItem.ListSubItems.Add Key:="Abondanded call", Text:="Y"
Else
xItem.ListSubItems.Add Key:="Abondanded call", Text:="-"
End If
End If
rstRows.MoveNext
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -