I bought the super-cool USB7 7-segment LED kit and soldered it together in about an hour. It's a six-digit display, like you would see on clocks or a calculator. The included driver makes it appear to Windows (or Mac, or Linux) as a serial port. Thus, you write a line to the serial port and it appears on the LCD display. Simple enough.
The trick is getting useful information to the LCD display. How do I get the number of incidents in my queue to the USB7? (A better question might be, how am I supposed to know when there's a new ticket in my queue at all?)
We use Windows at work, so I needed to write this in some kind of dotNet mumbo-jumbo, which is slightly more complicated than PHP, Java ,or VB6, because you need to do things the dotNet way. This uses dotNet 2.0 or 3/3.5 and works in Visual Studio 2005 and Visual Studio 2008 on x86 and x64. It's a simple console application that runs a query and writes it to the com port. Everything is hard coded. Feel free to use it and change it as you wish. Now I need a larger LCD display. SQL query for ServiceCenter 6.1 tables in bold. (the thing with ServiceCenter, at least v6.1, is that incidents are in the probsummary tables. Calls are in the incidents tables.)
'Simple VB.Net 2008/2005 Code to write a single line to the USB7
'available from
'Tested on Win2k3 x86 amd x64 using avrcdc.inf
'11.20.2008 Larry
'release 1.1
'.Net sure is different than good old VB6. Everything's an object.
Imports System
Imports System.Text
Imports System.IO.Ports
Imports System.Data.SqlClient
Module USB7
Dim intTixCount As Int32 = 0
Sub Main()
'this is a standard ServiceCenter 6.1 query to list all the incidents in queue "My Queue"
Dim sql As String = "select count(dbo.probsummarym1.assignment)from dbo.probsummarym1 inner join dbo.probsummarym2 on dbo.probsummarym1.number = dbo.probsummarym2.number and dbo.probsummarym2.close_time is null where probsummarym1.assignment = 'My Queue';"
Using conn As New SqlConnection("Data Source=mydbserver;Initial Catalog=mydatabase;Integrated Security=True")
'you could hard-code a password in that string, but you know better.
Dim cmd As New SqlCommand(sql, conn)
Try
conn.Open()
intTixCount = Convert.ToInt32(cmd.ExecuteScalar())
'ExecuteScalar returns an int -- a regular cmd.execute would result in a recordset
'which would be a problem.
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
conn.Close()
'keep your DBAs happy and close those connections
End Using
Using com1 As IO.Ports.SerialPort = _
My.Computer.Ports.OpenSerialPort("COM2", 9600)
com1.WriteLine(intTixCount)
com1.Close()
'it really is that simple, because we're not reading anything back from the USB7
End Using
End Sub
End Module
