(UPDATE: I’ve recoded this in a much better format, go here to check it out)
I feel bad about having never packaged this program into a more usable state, something along the lines of say PsychoStats, but for phone records. Anyway below is what I have, if someone can make use of it without having to resort to one of those outrageously expensive packages, so much the better.
First, the VB code:
Imports System
Imports System.IO
Imports System.Text
Imports System.Collections
Imports System.Data.OleDb
‘Imports System.Data.Odbc
‘Imports System.Data.SqlClient
‘Imports System.Data
Module Module1
Dim logpath_glbl As String = “<PATH TO BCM LOGFILES>”
Dim logfile_path As String = “c:\phonelog.log”
‘Array of file names brought in from the dataset so that they can be
‘ compared to the file name being processed
Dim ProcFileArray(3000) As String
‘Number of file names in the ‘already processed files’ list
Dim MaxRows As Integer = 0
Dim PhonelogDataSet As New DataSet
Dim OleDbSelectCommand1 As String = _
“Provider=SQLNCLI.1;Data Source=MUSE;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=phonelog”
Dim OleDbConnection1 As New OleDbConnection(OleDbSelectCommand1)
Dim OleDbDataAdapter1 As New OleDbDataAdapter(“SELECT call_log.* FROM call_log”, OleDbConnection1)
Dim OleDbDataAdapter2 As New OleDbDataAdapter(“SELECT proc_logfiles.* FROM proc_logfiles”, OleDbConnection1)Sub Main()
Call CreateLogFile()
OleDbConnection1.Open()
WriteLogFile(“*** Populating Call Log table into Dataset ***”)
OleDbDataAdapter1.FillSchema(PhonelogDataSet, SchemaType.Source, “call_log”)
OleDbDataAdapter1.Fill(PhonelogDataSet)
WriteLogFile(“*** Populating Processed log files table into Dataset ***”)
OleDbDataAdapter2.FillSchema(PhonelogDataSet, SchemaType.Source, “proc_logfiles”)
OleDbDataAdapter2.Fill(PhonelogDataSet)
OleDbConnection1.Close()
Call src_List()
End Sub
Private Sub CreateLogFile()‘logfile_path = (prof_path & “\desktop\ToledoTrans-” & TextBox_datecode.Text & “.log”)
Dim fi As FileInfo = New FileInfo(logfile_path)
‘Command used to flush existing log file if appending is not desired.
‘Try
‘ ‘Delete any existing logfile
‘ fi.Delete()
‘Catch e As Exception
‘ Console.WriteLine(“The process failed: {0}”, e.ToString())
‘End Try
‘Get date and time for the header
Dim nowdate As Date = Now
Dim DateString As String = nowdate.ToString(“d”)
Dim TimeString As String = nowdate.ToString(“T”)
‘Write a header on the log file
Using sw As StreamWriter = File.AppendText(logfile_path)
‘sw.Write is used if a CR/LF is not desired
‘sw.Write(“This is the “)
sw.WriteLine(“—————————————” & _
“—————————–“)
sw.WriteLine(DateString & (Chr(9)) & TimeString)
sw.WriteLine(“—————————————” & _
“—————————–“)
sw.Close()
End UsingEnd Sub
Private Sub WriteLogFile(ByVal LogData As String)
‘writes individual lines to the log file
Using sw As StreamWriter = File.AppendText(logfile_path)
sw.WriteLine(LogData)
sw.Close()
End Using
End Sub
Private Sub AutoTrans(ByVal FileProc() As String, ByVal linecnt As Integer)
‘Method extracts legible records from the Nortel logs and calls
‘ AutowriteDB method to commit the records to the DB line by line
‘Variables which track the number of lines that were successfuly committed to the database
Dim UpdEntryCnt As Integer = 0
Dim UpdLogCnt As Integer = 0
‘Variable used to see if a log file has already been processed
Dim FileResult As Integer = 1
‘Variables used to track the number of fields in a row
Dim field_cnt As Integer = 0
Dim FieldTest As String
‘What row is currently being read
Dim row_cnt As Integer = 0
‘Signals if the program has detected a call
Dim hit As Integer = 0
‘Variable which holds the full line to be written
Dim NumVars As Integer = 7
Dim hitline(NumVars) As String
‘Track number of files processed or not
Dim TotalAlreadyProcessed As Integer = 0
Dim TotalProcessed As Integer = 0
‘Used for ‘totals’ message after processing
Dim ProcessString As StringFor FileIndex As Integer = 0 To linecnt
row_cnt = 0
‘Test to see if file to be accessed has already been processed.
FileResult = GetDoneFiles(FileProc(FileIndex))
If FileResult = 0 Then
Using xlatefileReader As New _
Microsoft.VisualBasic.FileIO.TextFieldParser(logpath_glbl & FileProc(FileIndex))
xlatefileReader.TextFieldType = FileIO.FieldType.Delimited
xlatefileReader.Delimiters = New String() {” “}
Dim currentRow As String()
While Not xlatefileReader.EndOfData
Try
field_cnt = 0
row_cnt = row_cnt + 1
currentRow = xlatefileReader.ReadFields()
If hit = 0 Then
‘”——–” Indicates a new call entry in the log
If currentRow(0) = “——–” Then
‘For any variables not handled by the program, they will persist
‘ as *UNKNOWN*
For i As Integer = 0 To NumVars
hitline(i) = “*UNKNOWN*”
Next i
‘Count the number (field_cnt) of strings (FieldTest)
‘in the current row. Only counts of 5 (or 6) will be processed.
For Each FieldTest In currentRow
field_cnt = field_cnt + 1
Next
‘We need to detect variable field counts since Nortel sucks at writing
‘ out logs in a consistent fashion (some lines have additional white
‘ space at the end, some don’t). Wrong dialed number (i.e. someone
‘called a phone number in the company block that is unassigned) have CNT of 8.
If field_cnt = 1 Or field_cnt = 2 Or field_cnt = 3 Or field_cnt = 4 Or field_cnt = 8 Then
‘These (3, 4 and 8 counts) are status fields and are disregarded
‘ For some reason, an irregular field length wasn’t being caught by
‘ the program by the final Else statement. I added 1 and 2 as well.
‘ The irregular field length was caused by a reboot of the PBX.
ElseIf field_cnt = 5 Or field_cnt = 6 Then
hit = 1
‘Set DATE and TIME
hitline(0) = currentRow(1)
hitline(1) = currentRow(2)
‘Set the SRC number to the STN ID, if the number is INCOMING
‘ it will be changed to the CALLING NUMBER, other wise it will
‘ remain the SRC number for the OUTGOING
hitline(3) = currentRow(4)
‘Set the DST number to the Line=, if the number is Outgoing it
‘ will be changed to DIGITS DIALED, or will be set to the CALLING
‘ NUMBER for proper INCOMING calls. For oddball INCOMING calls,
‘ it will remain this string.
hitline(4) = currentRow(3)
Else
Dim errmsg As String
errmsg = “Abnormal Row Length of ” & field_cnt & ” Found At Line ” & row_cnt
errmsg = errmsg & (Chr(13) & Chr(10)) & ” in file ” & logpath_glbl & FileProc(FileIndex)
errmsg = errmsg & (Chr(13) & Chr(10)) & currentRow(0) & “-” & currentRow(1) & “-” & currentRow(2) & “-” & currentRow(3)
WriteLogFile(errmsg)
End IfEnd If
‘If the new call line was detected, add the detected lines below until a
‘ CALL RELEASED or TRANSFER is detected
ElseIf hit = 1 Then
For Each FieldTest In currentRow
field_cnt = field_cnt + 1
Next
‘Both the CALLING NUMBER and DIGITS DIALED contain
‘ five strings on the line (as detected by VB)
If field_cnt = 5 Then
‘Set the SRC number on INCOMING calls to the
‘ CALLING NUMBER (currentrow(4))
If currentRow(3) = “CALLING NUMBER” Then
hitline(2) = “INCOMING”
hitline(3) = currentRow(4)
Else
‘Set the DST on OUTGOING calls to the DIGITS DIALED
If currentRow(3) = “DIGITS DIALED” Then
hitline(2) = “OUTGOING”
hitline(4) = currentRow(4)
End If
End If
End If‘Required to detect odd lines with no ‘Calling number’
If field_cnt > 2 Then
If currentRow(1) = “INCOMING CALL” Then
hitline(2) = “INCOMING”
End If
End If‘Set the DST number on INCOMING calls to the DNIS NUMBER
If field_cnt = 6 Then
If currentRow(3) = “DNIS NUMBER” Then
hitline(4) = currentRow(5)
End If
End If
‘Drop lines where we don’t want logging
‘ Could probably be better accomplished with an array and Switch, but my needs are limited
If hitline(3) = “15554443333” Or hitline(4) = “15554443333” _
Or hitline(3) = “15554443333*” _
Or hitline(4) = “15554443333*” _
Or hitline(3) = “15554442222” _
Or hitline(4) = “15554442222” _
Or hitline(3) = “15554442222#” _
Or hitline(4) = “15554442222#” _
Or hitline(3) = “15554441111” _
Or hitline(4) = “15554441111” Then
hit = 0
End If
If field_cnt = 2 Then
‘When CALL RELEASED is detected, the line is written out and the ‘hit’ reset to 0
If currentRow(1) = “FROM TRANSFER” Then
hitline(2) = “FROM TRANSFER”
hitline(4) = “XFER NUMBER”
ElseIf currentRow(1) = “CALL RELEASED” Or currentRow(1) = “TRANSFERRED” Then
hitline(5) = currentRow(0)
If currentRow(1) = “CALL RELEASED” Then
hitline(6) = “CALL RELEASED”
Else
hitline(6) = “CALL TRANSFERRED”
End If
Dim i As Integer = AutowriteDB(hitline(0), hitline(1), hitline(2), hitline(3), hitline(4), hitline(5), hitline(6), FileProc(FileIndex))
UpdEntryCnt = UpdEntryCnt + i
hit = 0
End If
End If
End IfCatch ex As _
Microsoft.VisualBasic.FileIO.MalformedLineException
WriteLogFile(“Line ” & ex.Message & _
“is not valid and will be skipped.”)
End Try
End While
End Using
End If
‘If file wasn’t found in the Processed Files index, then add it
If FileResult = 0 Then
Dim i As Integer = NoteFile(FileProc(FileIndex))
UpdLogCnt = UpdLogCnt + i
TotalProcessed = TotalProcessed + 1
Else
TotalAlreadyProcessed = TotalAlreadyProcessed + 1
End If
‘Reset the result
FileResult = 1
Next FileIndex
ProcessString = “Files in directory which have already been processed: ” & TotalAlreadyProcessed
ProcessString = ProcessString & (Chr(13) & Chr(10))
ProcessString = ProcessString & “Files processed in this pass: ” & TotalProcessed
ProcessString = ProcessString & (Chr(13) & Chr(10))
ProcessString = ProcessString & “Entrys successfully added to the database: ” & UpdEntryCnt
ProcessString = ProcessString & (Chr(13) & Chr(10))
ProcessString = ProcessString & “Processed log files added to the database: ” & UpdLogCnt
WriteLogFile(ProcessString)
End Sub
Private Function AutowriteDB(ByVal f1 As String, ByVal f2 As String, ByVal f3 As String, ByVal f4 As String, ByVal f5 As String, ByVal f6 As String, ByVal f7 As String, ByVal ProcdFile As String) As Integer
‘Method commits a phone record to the database‘Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter1)
Dim DsNewRow As DataRow
Dim calldate As DateTime
‘Troubleshooting integer
Dim UpdCnt As Integer = 0
‘Save date and time as a DateTime
calldate = DateTime.Parse(f1 & ” ” & f2)
‘Create a new row in the dataset that holds the ‘call_log’ table
DsNewRow = PhonelogDataSet.Tables(“call_log”).NewRow()
DsNewRow.Item(“call_date”) = f1
DsNewRow.Item(“call_time”) = f2
DsNewRow.Item(“call_direction”) = f3
DsNewRow.Item(“call_src”) = f4
DsNewRow.Item(“call_dest”) = f5
DsNewRow.Item(“call_length”) = f6
DsNewRow.Item(“call_status”) = f7
DsNewRow.Item(“call_datetime”) = calldate
‘Add the new changed row to the dataset and commit the change to the database
PhonelogDataSet.Tables(“call_log”).Rows.Add(DsNewRow)
OleDbConnection1.Open()
Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter1)
UpdCnt = OleDbDataAdapter1.Update(PhonelogDataSet, “call_log”)
OleDbConnection1.Close()
Return (UpdCnt)
End Function
Private Function NoteFile(ByVal lfname As String) As Integer
‘Method adds processed files to the processed files table in the
‘ database to avert repeated processing
Dim DsNewRow As DataRow
‘Troubleshooting integer
Dim UpdCnt As Integer = 0
‘Create a new row in the dataset that holds the ‘proc_logfiles’ table
DsNewRow = PhonelogDataSet.Tables(“proc_logfiles”).NewRow()
DsNewRow.Item(“logfile”) = lfname
‘Add the new changed row to the dataset and commit the change to the database
PhonelogDataSet.Tables(“proc_logfiles”).Rows.Add(DsNewRow)
OleDbConnection1.Open()
Dim cb As New OleDb.OleDbCommandBuilder(OleDbDataAdapter2)
UpdCnt = OleDbDataAdapter2.Update(PhonelogDataSet, “proc_logfiles”)
OleDbConnection1.Close()
Return (UpdCnt)
End Function
Function IsValidFileNameOrPath(ByVal name As String) As Boolean
‘ Determines if the name is Nothing.
If name Is Nothing Then
Return False
End If‘ Determines if there are bad characters in the name.
For Each badChar As Char In System.IO.Path.GetInvalidPathChars
If InStr(name, badChar) > 0 Then
Return False
End If
Next
‘ Does file exist?
If My.Computer.FileSystem.FileExists(name) Then
Return True
Else
Return False
End If‘ If file does not meet any criteria, return false
Return FalseEnd Function
Private Sub src_List()
‘This method will pull in the list of files and find the ones
‘ that have not already been processed (performed in PopulateProcFileArray method)‘Find the files matching the specified pattern
‘ note: not year 3000 compatible since the .2* is the beginning of the year 🙂
Dim pattern As String = “Record.2*”
Dim ToBProcFiles(3000) As String
Dim LineCnt As Integer = 0‘Get the pattern without stuff in parens.
‘ This came with the program, in case the files being searched
‘ for have a description in the program (ex. ‘(*.csv)’)
If pattern.IndexOf(“(“) >= 0 Then
pattern = pattern.Substring(0, pattern.IndexOf(“(“))
End If
‘ Get the files
Dim dir_info As New DirectoryInfo(logpath_glbl)
Dim file_infos() As FileInfo
file_infos = dir_info.GetFiles(pattern)
‘Read in a list of files that match the pattern
For Each file_info As FileInfo In file_infos
ToBProcFiles(LineCnt) = file_info.Name
LineCnt = LineCnt + 1
Next file_info
‘Build the array which will hold all the files which
‘ have already been processed
Call PopulateProcFileArray()
‘Decrement one since last loop will increment one too many
‘ variable used in translation method to order ‘For’ loop
LineCnt = LineCnt – 1
AutoTrans(ToBProcFiles, LineCnt)
End Sub
Private Sub PopulateProcFileArray()
Dim count As Integer
‘Populate the data set for dectecting files which have already been processed
OleDbDataAdapter2.Fill(PhonelogDataSet, “proc_logfiles”)
‘Populate Array to be used for the comparison and remove trailing
‘ spaces that are added to the string when it’s put into the DB
MaxRows = PhonelogDataSet.Tables(“proc_logfiles”).Rows.Count
MaxRows = MaxRows – 1
For count = 0 To MaxRows
‘remove whitespace
ProcFileArray(count) = RTrim(PhonelogDataSet.Tables(“proc_logfiles”).Rows(count).Item(“logfile”))
Next
End Sub
Private Function GetDoneFiles(ByVal filename As String) As Integer
Dim count As Integer
Dim detector As Integer = 0
‘Check to see if the file currently in line to be processed
‘ has already been processed. Set to ‘1’ if detected.
For count = 0 To MaxRows
If filename = ProcFileArray(count) Then
detector = 1
count = MaxRows
End If
Next
Return detector
End FunctionEnd Module
I’ll follow that up with some notes:
- I’m sorry, but I do not entirely recall how I structured the database that I was leading the data into. From what I recall, I can export the SQL commands to create the tables as I’ve had to do that before when purging data (dumping tables and reimporting the desired data is a crude, but effective method for me since I’m not working with that much data), but I don’t have time to play with that at the moment.
- I had originally built an ASP front end page that responsible parties could use to peruse the data. I was going to post that code as well, but I noticed that for some reason it is not working. I can read the data fine in Excel, so I know it’s there but I’d imagine my unworking code won’t be of much help to anyone.
- The original incarnation of this program was a GUI program that turned the log files into CSVs, though I don’t think that I have the code for that anymore. It would be easy enough for someone to recreate if they take the base methods and dump the database stuff and replace it with writing the strings out to a CSV file instead.
More details to follow if I get more time to work on this.