Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Call Handler changes from external app or SQL query.

Hi there;

Just wondering if anyone has been able to make Call

Handler changes from an external app or SQL query.

My aim is to change the value of a Call Handlers transfer options. I use a caller input value on the subscriber to go to a call handler which has a transfer to an external number, i would like to be able to change this from a web page.

Has any one done this?

Regards

Andrew

2 REPLIES
Cisco Employee

Re: Call Handler changes from external app or SQL query.

In this case you’ll need to edit values on the ContactRule table which at this time doesn’t have any stored procedures wrapping it – you’ll have to edit the table directly. Most of the stored procedures we’ve been working on up front since 4.0(1)’s release have been related to subscriber creation/deletion/editing since that’s what most sites doing programmatic administration are interested in. We’ll get stored procs in front of all objects in the database eventually, but for now you’ll have to do this one “in the buff” as it were.

In the “Data Object Model” document on the Documents page of www.CiscoUnityTools.com I talk about the contact rules and how they apply to call handlers – the short story is every call handler is associated with 3 contact rules: an alternate, off hours and standard.

In your case I’m assuming that the call handlers are already configured and the transfer rule you want to edit is the alternate. You can substitute “standard” or “off hours” in the SQL strings below if not. Also, if you don’t have the latest CUDLE installed off www.CiscoUnityTools.com, it’d probably be a good idea to get that – it’s helpful to see the brief explanations for what all the columns mean in each of the tables as you go with such projects.

First, you want to find the call handler in question and then go fetch the contact rules for that guy. I’m assuming you’re probably finding call handlers by alias here. To get the alternate contact rule for the call handler with an alias of “OperatorCH” (the operator call handler) you’d use a query like this:

SELECT ContactRule.* FROM ContactRule INNER JOIN vw_CallHandler

ON ContactRule.ParentObjectID=vw_CallHandler.CallHandlerObjectID

WHERE ContactRule.Alias='Alternate' AND vw_CallHandler.Alias='OperatorCH'

If you’re using extensions to find handlers instead of aliases that’d work as well, you’d just substitute “AND vw_CallHandler.DTMFAccessID=’1234’” into the query above.

Notice that we’re not using the view for the contact rule here since we’re going to be editing it directly.

The examples out on the code samples page cover how to attach to SQL either on box or on a remote server and how to use queries like the above, that should get you from A to B for that. Just be sure to open the recordset as a keyset and allow optimistic updates if you’re using ADO (as I do in all my examples).

If the call handler is already setup to do a transfer using this contact rule the only value you need to change is the “Extension” column – this is the actual dial string that the transfer rule will use (be sure to include the CO prefix if necessary). Save it and you’re done.

If the contact rule is not configured to transfer or you’re not sure one way or the other you’ll want to check a few fields (explanations for these can all be found in CUDLE, of course). Make sure the “Action” field is set to 1 – this means to attempt the transfer with this rule. Make sure the “TimeExpires” field is set to NULL – this means the rule is active – a time in the past means it’s disabled. Make sure the TransferType value is either 0 for release or 1 for supervised – if it’s supervised make sure the TransferRings is set to the number of rings you want.

That’s about it. Not overly complicated, you just need to be careful. The one thing that’ll bite you here is making sure the transfer string entered is allowed by the transfer restriction table of the person making the change – I’m not sure how you’re setting up this web page or how users will authenticate or whatever, but one way or another you’ll want to grab the Class of Service of the person requesting the transfer string change and make sure the string they enter is allowed by their restriction tables. I’ll post a brief VB source clip in the next post that shows how to do that so I don’t run over my max allowed characters in a post here…

Cisco Employee

Re: Call Handler changes from external app or SQL query.

Here’s some VB code I pulled out of dbWalker that shows how to grab a COS entry and check a transfer string against it’s transfer restriction table rules. Unfortunately when I paste it in here it loses all it's indentation and color so it's not as fabulously readable as it is in my dev environment. Also, just in case one of my former Computer Science professors is reading this, I've yanked out the error handling code here to make it shorter - honest.

Just for review here – restriction tables are a collection of pattern matching strings made up of numbers and wildcard characters and an “allow” or “don’t allow” flag – they are processed in order from top to bottom until the dial string matches and the allow/don’t is evaluated. The last rule is always “*” which will match anything so you’re guaranteed a rule will match.

For this routine, you pass in the Class of Service ObjectID of the subscriber and the transfer string you want to check and it returns True or False depending on if the string is allowed by that COS transfer restriction table or not.

This code uses a nifty little VBScript object called RegExp which is a RegularExpression object. To use it in VB you need to include the “Microsoft VBScript Regular Expressions 5.5” object in your project references.

Private Function IsTransferStringLegal(strStringToCheck as String, strCOSObjectID as string) as Boolean

Dim rsRT As ADODB.Recordset

Dim rsPatterns As ADODB.Recordset

Dim rsCOS as ADODB.Recordset

Dim strPattern As String

Dim myRegExp As New RegExp

If Len(strStringToCheck) = 0 Then

'We assume that an empty string is legal

IsTransferStringLegal=True

Exit

End If

‘Assume false here

IsTransferStringLegal=false

‘Setup the regular expression object here

myRegExp.IgnoreCase = True

myRegExp.Global = False

Set rsCOS=New ADODB.Recordset

rsCOS.Open "SELECT * FROM vw_COS WHERE COSObjectID='" + strCOSObjectID + "'",strConnectionString, adOpenKeySet, adLockReadOnly

If rsCOS.RecordCount=0 Then

Msgbox ”(error) could not find COS reference”

Exit

End If

Set rsRT = New ADODB.Recordset

rsRT.Open "SELECT * FROM vw_RestrictionTable WHERE RestrictionObjectID="+ rsCOS("XferRestrictionObjectID") + "'", strConnectionString, adOpenKeyset, adLockReadOnly

If rsRT.Recordcount=0 Then

MsgBox”(error) could not find restriction table for transfer”

Exit

End If

‘Pull the patterns associated with the restriction table out. Be sure to preserve the “index” order since the order in which they’re evaluated is important.

Set rsPatterns = New ADODB.Recordset

rsPatterns.CursorLocation = adUseClient

rsPatterns.Open "Select * from VW_RestrictionPattern WHERE ParentObjectID='" + rsRT("RestrictionObjectID") + "' ORDER BY [Index]", strConnectionString, adOpenStatic, adLockReadOnly

If rsPatterns.RecordCount = 0 Then

MsgBox ”(error) no restriction table patterns found for restriction table”

Exit

End If

'do that regular expression thing, baby!

'First, strip out all commas

strStringToCheck = Replace(strStringToCheck, ",", "", , , vbTextCompare)

‘Now run through each pattern and see if it matches the dial string we’re looking at.

rsPatterns.MoveFirst

Do While rsPatterns.EOF = False

strPattern = rsPatterns("NumberPattern")

'replace ?s with .s to match the regular expression format.

strPattern = Replace(strPattern, "?", ".", , , vbTextCompare)

'replace * with "[0-9]*" to match the regular expression format.

strPattern = Replace(strPattern, "*", "[0-9]*", , , vbTextCompare)

myRegExp.Pattern = strPattern

If myRegExp.Test(strStringToCheck) = True Then

‘OK, the transfer string matches this pattern

If rsPatterns("Blocked") = True Then

‘the transfer string is not allowed – just exit since the function value is false

Else

‘the transfer string is allowed

IsTransferStringLegal=true

End If

Exit Do

End If

rsPatterns.MoveNext

Loop

'clean up and pull the rip cord.

On Error Resume Next

rsRT.Close

Set rsRT = Nothing

rsPatterns.Close

Set rsPatterns = Nothing

rsCOS.Close

Set rsCOS=Nothing

End Sub

117
Views
5
Helpful
2
Replies
CreatePlease to create content