travislowdermilk.com

“What were the dates of all the Mondays in the last 5 months?”
“How many Fridays were there between this date range?”
“What were the dates of the paydays in the last 10 months?”

Do any of these types of questions sound familiar? If you’ve ever worked with these types of date time questions in SQL, you would know that it can be VERY challenging.

At the hospital we use Cisco Unified Communications Manager (a.k.a CallManager).

The directors desired a report to break down various call statistics to one of their clinics. They were hoping this information would give them insight into how to handle call load.

One particular question was a tricky one: “What is the average call per days of the week, per hour, for the last three months?”.

For example: “What was the average call count, per hour, for all the Mondays in the last 3 months?”

Thanks to newly discovered dimensional tables, I had the breakdown per hour figured out, however, I was stumped on how to group it for average Mondays, Tuesdays, Wednesdays, etc.

That’s why I was thrilled to discover this solution by Peter Larsson, titled How Many More Mondays Until I Retire?

Essentially, Peter suggests creating a scalar SQL function that returns a table of dates between a specified date range. This opens a range of possibilities as far as dealing with tricky date queries.

Here is Peter’s function:

CREATE FUNCTION dbo.fnSeqDates
(
    @LowDate DATETIME,
    @HighDate DATETIME
)
RETURNS @Dates TABLE
        (
            SeqDate DATETIME
        )
AS

BEGIN
    DECLARE @Temp DATETIME

    IF @LowDate > @HighDate
        SELECT    @Temp = @LowDate,
                  @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),
                  @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
    ELSE
        SELECT    @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),
                  @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

    INSERT    @Dates
              (
                  SeqDate
              )
    VALUES    (
                  @LowDate
              )

    WHILE @@ROWCOUNT > 0
        INSERT     @Dates
                   (
                       SeqDate
                   )
        SELECT     DATEADD(dd, n.Items, d.SeqDate)
        FROM       @Dates d
        CROSS JOIN (
                        SELECT COUNT(SeqDate) Items
                        FROM   @Dates
                   ) n
        WHERE      DATEADD(dd, n.Items, d.SeqDate) <= @HighDate

    RETURN
END

I used the function in my stored procedure like this:

	@StartDate datetime,
	@EndDate datetime,
	@Extensions varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @Time_Value TABLE (
RangeName varchar(18) UNIQUE,
Value int UNIQUE
)
-- This section is for setting up the dimensional table for the hours breakdown
INSERT INTO @Time_Value VALUES ('00',0)
INSERT INTO @Time_Value VALUES ('01',1)
INSERT INTO @Time_Value VALUES ('02',2)
INSERT INTO @Time_Value VALUES ('03',3)
INSERT INTO @Time_Value VALUES ('04',4)
INSERT INTO @Time_Value VALUES ('05',5)
INSERT INTO @Time_Value VALUES ('06',6)
INSERT INTO @Time_Value VALUES ('07',7)
INSERT INTO @Time_Value VALUES ('08',8)
INSERT INTO @Time_Value VALUES ('09',9)
INSERT INTO @Time_Value VALUES ('10',10)
INSERT INTO @Time_Value VALUES ('11',11)
INSERT INTO @Time_Value VALUES ('12',12)
INSERT INTO @Time_Value VALUES ('13',13)
INSERT INTO @Time_Value VALUES ('14',14)
INSERT INTO @Time_Value VALUES ('15',15)
INSERT INTO @Time_Value VALUES ('16',16)
INSERT INTO @Time_Value VALUES ('17',17)
INSERT INTO @Time_Value VALUES ('18',18)
INSERT INTO @Time_Value VALUES ('19',19)
INSERT INTO @Time_Value VALUES ('20',20)
INSERT INTO @Time_Value VALUES ('21',21)
INSERT INTO @Time_Value VALUES ('22',22)
INSERT INTO @Time_Value VALUES ('23',23)

SELECT
CASE
	WHEN DATEPART(weekday,ReportShortDateTime) = 1 THEN 'Sunday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 2 THEN 'Monday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 3 THEN 'Tuesday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 4 THEN 'Wednesday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 5 THEN 'Thursday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 6 THEN 'Friday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 7 THEN 'Saturday'
END AS Day_Name, RangeName AS Hour_Range,
COUNT(ReportShortDateTime) AS Calls,
(ROUND((CAST(COUNT(ReportShortDateTime) AS Float) / CAST((SELECT COUNT(SeqDate) FROM dbo.fnSeqDates(@StartDate, @EndDate) WHERE DATEPART(dw, SeqDate) = DATEPART(weekday,ReportShortDateTime)) AS Float)), 1)) AS Average_Calls
FROM [CALLMANAGER] INNER JOIN
@Time_Value ON DATEPART(hour, ReportDateTimeOrigination) = Value
WHERE ReportShortDateTime BETWEEN @StartDate AND @EndDate AND
originalCalledPartyNumber IN (Select Convert(varchar(50),Value) FROM dbo.fnSplit(@Extensions, ','))
GROUP BY DATEPART(weekday,ReportShortDateTime), RangeName, Value
ORDER BY DATEPART(weekday,ReportShortDateTime), Value
END

As you can see in the query, I use the function like this:

SELECT COUNT(SeqDate) FROM dbo.fnSeqDates(@StartDate, @EndDate) WHERE DATEPART(dw, SeqDate) = DATEPART(weekday,ReportShortDateTime)

In other words, I’m saying: “Give me the count of days, between this specified date range, that are Mondays, Tuesdays, Wednesdays, etc.”

Who cares that I don’t have a face? It’s still cool!

Below is the code I used to tap into the CIPIMAGE API and convert a .jpg image from our PeopleSoft database into a valid .cip image at runtime. Specifically it uses the “ImageProcessor” object to convert the image from a jpeg into XML data to be parsed by the phone.

This code will also require my Cisco Phone Object Class. The cipimage.dll is included in this post below. Be sure to register cipimage.dll on your web server!

Here’s the code:

Imports CiscoIPPhoneServices

Imports System.Data

Imports System.Data.SqlClient

Imports CIPIMAGE

Partial Class physdir_show_details

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Response.ContentType = “text/xml”

Dim ImgProc As New ImageProcessor

‘Establish connection with database

Dim SQLConnect As New SqlConnection(ConfigurationManager.AppSettings(“ConnectionStringName”))

Dim SQLCommand_Info As New SqlCommand(“stored_procedure_to_receive_info_from_db”, SQLConnect)

SQLCommand_Info.CommandType = CommandType.StoredProcedure

SQLCommand_Info.Parameters.AddWithValue(“@empid”, Request.QueryString(“empid”).ToString)

‘Prepare the table

Dim Adapter As New SqlDataAdapter(SQLCommand_Info)

Dim DT As New DataTable

Adapter.Fill(DT)

Dim EmpPhoto As String = “”

Dim EmpName As String = DT.Rows(0).Item(“EmployeeName”)

Dim pWidth As Integer = 52

If DT.Rows(0).IsNull(“EmpPhoto”) = False Then

Dim bEmpPhoto As Byte() = DT.Rows(0).Item(“EmpPhoto”)

ImgProc.LoadJPGFromBuffer(bEmpPhoto)

ImgProc.SetLocation(-1, -1)

ImgProc.Resize(52, 65)

ImgProc.ColorToGray()

ImgProc.ReducePaletteColors(4)

EmpPhoto = ImgProc.SaveCIPDataToBuffer()

EmpPhoto = Left(EmpPhoto, EmpPhoto.Length – 6)

Else

‘No photo Hex ASCII string

EmpPhoto = “<Insert a valid ASCII string for a picture that will display when the employee does not have a photo>”

EmpName = “No Photo Available”

pWidth = 87

End If

Dim CIPPD As New CiscoIPPhoneImage(“”, EmpName, -1, -1, pWidth, 65, 2, EmpPhoto, “Back”, “SoftKey:Back”, 1)

Response.Write(CIPPD)

End Sub

End Class

Download – CipImage.dll

I finally rolled out my solution for a Physician Directory on our Cisco VOIP system. The staff appears to be pleased with this added functionality. This has also opened up more ideas on how we can continue to leverage our knowledge in ASP.NET to provide services on our phone system.

The Cisco phone system operates entirely off of XML. For the most part, it is a glorified XML reader!

Guillaume Gros provided the foundation for a .NET class that I was able to add functionality to.

Now I have my most common functions that I would need (for instance “give me a button”) in managed code. In the code-behind on my aspx page I include the class, call a function, and supply a minimum set of parameters. The class returns the necessary XML to be rendered. Neat!

Let me give you an example (VB):

Dim btnSubmit As New CiscoSoftKey(“Search”, “SoftKey:Submit”, 1)

This declares a new CiscoSoftKey button specifying that the text of the button will be “Search” and “SoftKey:Submit” is a URL parameter that the Cisco phone interprets as “submit the following parameters”. 1 indicates the position of the button (out of 4 available).

The class accepts the parameters and returns the XML required to be display a Cisco Soft Key on the phone.

Simply Response.Write the XML that is returned and the Cisco phone (for the most part) does all the rest. Below is the code I wrote to build the screen shot you see above (search for physician):

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Response.ContentType = “text/xml”

‘Create an Array of CiscoInput items
Dim Inputs As New ArrayList
Dim LName As New CiscoInput(“Last Name”, “lname”, “U”, “”)
Dim FName As New CiscoInput(“First Name”, “fname”, “U”, “”)
Inputs.Add(LName)
Inputs.Add(FName)

‘Create and array of CiscoSoftKeyItems
Dim SoftKeys As New ArrayList
Dim btnSubmit As New CiscoSoftKey(“Search”, “SoftKey:Submit”, 1)
Dim btnBack As New CiscoSoftKey(“<<”, “SoftKey:<<”, 2)
Dim btnEROnCall As New CiscoSoftKey(“EROnCall”, “http://<hidden.com>/physdir/eroncall.aspx”, 3)
Dim btnExit As New CiscoSoftKey(“Exit”, “SoftKey:Exit”, 4)
SoftKeys.Add(btnSubmit)
SoftKeys.Add(btnBack)
SoftKeys.Add(btnEROnCall)
SoftKeys.Add(btnExit)

‘Send the array and other information to the .dll to retrieve xml result

Dim CPI As New CiscoIPPhoneInput(“Physician Directory Search”, “Enter search criteria”, “<hidden.com>.aspx”, Inputs, SoftKeys)

‘Write out the XML Code
Response.Write(CPI)

End Sub

And the two methods handled in the class:

public class CiscoInput

{

public string DisplayName;
public string QueryStringParam;
public string InputFlags;
public string DefaultValue;

public CiscoInput(string DisplayName, string QueryStringParam, string InputFlags, string DefaultValue)

{
this.DisplayName = DisplayName;
this.QueryStringParam = QueryStringParam;
this.InputFlags = InputFlags;
this.DefaultValue = DefaultValue;
}
public override string ToString()
{
return “<InputItem>\r\n<DisplayName>” + this.DisplayName + “</DisplayName>\r\n<QueryStringParam>” + this.QueryStringParam + “</QueryStringParam>\r\n<InputFlags>” + this.InputFlags + “</InputFlags>\r\n<DefaultValue>”+this.DefaultValue+“</DefaultValue>\r\n</InputItem>\r\n”;
}
}

public class CiscoSoftKey
{
public string Name;
public string URL;
public string URLDown;
public int position;
public CiscoSoftKey(string Name, string URL, int position)
{
this.Name = Name;
this.URL = URL;
this.position = position;
}

public override string ToString()
{

return “<SoftKeyItem>\r\n<Name>” + this.Name + “</Name>\r\n<URL>” + this.URL + “</URL>\r\n<Position>” + this.position + “</Position>\r\n</SoftKeyItem>\r\n”;
}
}

Attached is the source code for the class. Also, I would recommend the book Developing Cisco IP Phone Services.

Here’s the code you mooch!

Cisco IP Phone Services Class