using C# to run Avaya CMS reports

[madhombre]

I have been going round and around on this, hopefully someone can help me.

I have this VB code...

Dim cvsApp = CreateObject("cvsup.cvsApplication")
Dim cvsConn = CreateObject("CVSCN.cvsConnection")
Dim cvsSrv = CreateObject("CVSUPSRV.cvsServer")
Dim Rep = CreateObject("CVSREP.cvsReport")
Dim Info As Object, b As Object

If cvsApp.CreateServer("******", "", "", "******", False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login("******", "******", "******", "ENU") Then
'On Error Resume Next
cvsSrv.Reports.ACD = 1
Info = cvsSrv.Reports.Reports(reportName)
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Rep.SetProperty(prop1, prop2)
b = Rep.ExportData(destFile, 9, 0, True, False, True)
Rep.Quit()
Rep = Nothing
End If
End If
End If
Info = Nothing

runs perfectly, no issues, all good.

Problem is I would like to port this over to c#. it seems this is a tougher prospect and from searching online for several days I came up with nothing.

I have started ok...

CVSUP.cvsApplication cvsApp = new CVSUP.cvsApplication();
CVSCN.cvsConnection cvsConn = new CVSCN.cvsConnection();
CVSUPSRV.cvsServer cvsSrv = new CVSUPSRV.cvsServer();
CVSREP.cvsReport cvsRep = new CVSREP.cvsReport();

Object info, b = new Object();

object srv = (object)cvsSrv;
object conn = (object)cvsConn;
object rep = (object)cvsRep;

if (cvsApp.CreateServer("******", "", "", "******", false, "ENU", ref srv, ref conn))
{
if (cvsConn.Login("******", "******", "******", "ENU", "", cvsConn.bAutoRetry))
{
Console.WriteLine(cvsConn.ConnectionStatus.ToStrin g());
}
}

I can get logged in and as the status is up.

However I can't seem to proceed. While debugging I come up with nothing. The vb code indicates something is there and I can hook in.

I am worried about the casting of the special objects to just object, but I have to do it or the createserver fails. The parameters have to be objects.

Anyone help or done this before and could send me an example, I would be very greatful.

Thanks

[corcoranp]

madhombre,

There are some basic issues I want to mention before answering this question.

First, what you are wanting to do is use native COM objects, which is unmanaged within the .Net world which is managed code. That being said, .Net usually does a good job handling COM Interop, but in this case I think it’s bombing. Short of writing our own COM Interop for these set of dlls (and exes), I think I have a resolution to your problem.

Before we get into to it, I’d like to take a look at the difference between what’s happening when you call the CreateServer method in VB and in .Net.

The first think to remember is that C# is VERY type specific (or “strongly typed” for all the vocabulary police out there). VB on the other hand is NOT. What does that mean? Well in this example it means that although you have declared the following objects:
1. application (cvsup.cvsApplication or AVSUP.cvsApplicationClass),
2. connection (CVSCN.cvsConnection or AVSCN.cvsConnectionClass),
3. server (CVSSUPSRV.cvsServer or AVSSUPSRV.cvsServer), and
4. report (CVSREP.cvsReport or AVSREP.cvsReportClasss) objects

…there are still 2 more you are missing.

The funny thing about VB is that it doesn’t care about declaring these classes (or using them to cast objects), unfortunately C# cares a great deal.

The other thing and I can only assume you are doing this, you are adding the dlls (or exes) as a reference in .Net, which should be creating a set of Interop dlls. That being the case, when you call the CreateServer method of the cvsApplication objects you are probably seeing 2 processes appear in task manager for cvsSrv.exe…correct?

I think I have answer but it will probably take someone a little more technical than I to fully explain this, but here goes…

The first instance of cvsSrv.exe (or avsSrv.exe in my case (different CMS version but same issues)) is from this line of code:
CVSUPSRV.cvsServer cvsSrv = new CVSUPSRV.cvsServer();

The second instance is from the CreateServer method itself.

For some reason, even though you are passing in a reference object (ref srv) the cvsApplication method does not use the reference but creates a new object in memory (I think it’s Interop related).

I know what you are asking: How does all this relate to your problem and how do we fix it??

Well, if the CreateServer method is creating a NEW object in memory and NOT using the REFERENCE you passed to it, then you don’t have a variable within your scope that points to the created server in memory.

I’ll say that again…

Well, if the CreateServer method is creating a NEW object in memory and NOT using the REFERENCE you passed to it, then you don’t have a variable within your scope that points to the created server in memory.

So that is that, if someone has a better explanations please feel free.



But wait there’s more! The How do we fix it part?

Well here is what I did, which I think is the easy way and avoids having to write your own COM Interop or mixing managed and unmanaged code within C#.

First, because of the issues with C# which creates multiple servers in memory I needed a way to call the cvsApplication object’s CreateServer method and still have a reference to it. Since, the CVS dlls are COM I figured I could write an ActiveX Dll in VB6 and import it into my c# references. Now granted this is still a band-aid approach but it still works!

Here is the VB6 Class I wrote (no error detection/correction, or tracing included):
NOTE: On the project’s properties page, on the “Make” tab, I added “cms” as the application’s title, and on the “General” tab “cms” as the project name.


Dim cmsApplication As AVSUP.cvsApplication
Dim cmsConnection As AVSCN.cvsConnection
Dim cmsServer As AVSUPSRV.cvsServer

Private Sub Class_Initialize()
'
Set cmsApplication = New AVSUP.cvsApplication
Set cmsConnection = New AVSCN.cvsConnection
Set cmsServer = New AVSUPSRV.cvsServer

End Sub

Property Get cmsSrv() As Object
Set cmsSrv = cmsServer
End Property

Public Sub CreateServer(UserName As String, Password As String, Server As String)

If cmsApplication.CreateServer(UserName, "", "", Server, False, "ENU", cmsServer, cmsConnection) Then
If cmsConnection.Login(UserName, Password, Server, "ENU", "", False) Then
End If
End If
End Sub


Of course the with the Dll version you are using, the code would look like this:

Dim cmsApplication As CVSUP.cvsApplication
Dim cmsConnection As CVSCN.cvsConnection
Dim cmsServer As CVSUPSRV.cvsServer

Private Sub Class_Initialize()
'
Set cmsApplication = New CVSUP.cvsApplication
Set cmsConnection = New CVSCN.cvsConnection
Set cmsServer = New CVSUPSRV.cvsServer

End Sub

Property Get cmsSrv() As Object
Set cmsSrv = cmsServer
End Property

Public Sub CreateServer(UserName As String, Password As String, Server As String)

If cmsApplication.CreateServer(UserName, "", "", Server, False, "ENU", cmsServer, cmsConnection) Then
If cmsConnection.Login(UserName, Password, Server, "ENU", "", False) Then
End If
End If
End Sub


Once I created the dll, I added as a reference my c# project. At that point it was as easy as calling my new class and creating a server instance:


(In C#)

cms.ConnectorClass cc = new cms.ConnectorClass();
cc.CreateServer(“userid”, “pass”, “servername”);


This creates a CVS Server object in memory and we can now call it using the property in the VB class:

…cc.cmsSrv.ToString()..

(above code should return type info)


Well, I assume your next set is to populate the server with info so you can run some reports against it. This is where those 2 other objects come into play.

In Visual Studio you need to add a reference to the cvsCTLG.dll (something like: C:\Program Files\Avaya\CMS Supervisor V11\cvsCTLG.dll). And if you haven’t already added avsSrv.exe add it too.

Now to populate the Server so you can run reports you have to populate the server’s Catalog (1 of the new objects) based on your call management system’s ACD, AND depending on the report type you are interested in…

If you need Dictionary reports which are more like system reports, like agent extensions, system definitions, etc.; you will need to populate the Dictionary catalog on the server object

If you need access to your custom Reports or generic reports you will need to populate the Report Catalog on the server object

EXAMPLES:

//My Dictionary Example
((AVSCTLG.cvsCatalogClass)((AVSUPSRV.cvsServerClas s)cc.cmsServer).Dictionary).ACD = 2;

//Your Dictionary Example
((CVSCTLG.cvsCatalogClass)((CVSUPSRV.cvsServerClas s)cc.cmsServer).Dictionary).ACD = 2;


//My Report Example:
((AVSCTLG.cvsCatalogClass)((AVSUPSRV.cvsServerClas s)cc.cmsServer).Report).ACD = 2;

//Your Report Example:
((CVSCTLG.cvsCatalogClass)((CVSUPSRV.cvsServerClas s)cc.cmsServer).Report).ACD = 2;



So what does setting this property do??? It populates the corresponding catalog.

You might be a little confused right now but let me attempt to clear it up…

The Dictionary and Report catalog is a VBA.Collection. This collection houses the 2nd of the new objects I need to introduce the ReportInfo class. The ReportInfo class is used with a CVSREP.cvsReport (AVSREP.cvsReport) class to create a report object.

To see what reports you have access to you can do the following:

//My Example:
foreach(object o in ((AVSCTLG.cvsCatalogClass)((AVSUPSRV.cvsServerClas s)cc.cmsServer).Dictionary).Reports)
{
Console.WriteLine(((AVSCTLG.cvsReportInfoClass)o). Name.ToString());
}

//Your Example:
foreach(object o in ((CVSCTLG.cvsCatalogClass)((CVSUPSRV.cvsServerClas s)cc.cmsServer).Dictionary).Reports)
{
Console.WriteLine(((AVSCTLG.cvsReportInfoClass)o). Name.ToString());
}


These loops will give you the name of every report listed in the Dictionary Catalog.


The remainder of the C# code (creating the report) should be a piece of cake after this. Just remember to cast your objects to the appropriate type and you should be good.

Let me know if you have any questions and I’ll see if I can work them out.

I also rolled the reporting object up into a class if anyone is interested let me know and I’ll post it.


Thanks.

[corcoranp]

OK forget everything I said about the VB COM stuff and just try the below code:



CVSUP.cvsApplicationClass oApp = new CVSUP.cvsApplicationClass();
System.Object oCon = new object();
System.Object oSrv = new object();


if(((CVSUP.cvsApplicationClass)oApp).CreateServer( "userid","","","server",false,"", ref oSrv, ref oCon))
{
if(((CVSCN.cvsConnectionClass)oCon).Login("userid","password","server","ENU","",false))
{
}
}
((CVSCTLG.cvsCatalogClass)((CVSUPSRV.cvsServerClas s)oSrv).Dictionary).ACD = //put in ACD HERE;

foreach(object o in ((CVSCTLG.cvsCatalogClass)((CVSUPSRV.cvsServerClas s)oSrv).Dictionary).Reports)
{
Console.WriteLine(((CVSCTLG.cvsReportInfoClass)o). Key.ToString());
}


[muppetK]

Hi All,

I work with C# too and the access to the CMS works fine, but I don't want to exprort the data of a report to a file or print it out.
Is there a possibility to copy all data in an array or another structur?

Thanks for your help

[corcoranp]

I've been trying to figure it out myself...

There are some methods and properties that seem promising when you look at the COMInterop cvsReport interface. For example:

Methods:
Edit()
Preview()
Run()

Properties:
ReportView

If you look at the locals while running an app, you will see that the ReportView is a VBA.Collection, however, I've not been able to anything back from it while experimenting. (I've tried the Run() method then enumerating through ReportView and got nada.)

Have you tried anything along these lines???

[muppetK]

I've only tried to get data using the property ReportView with the same results, NULL.

I think the method Preview() looks good, I will make some tests and let you know, if I have any results.

[corcoranp]

Well, I've done some investigating and I'm not sure that we are going to be able to get the data directly using the CMS Scripting dlls.

I did a WinGrep of the dll in my CMS directory for the ReportView method and found an example of it in the cvsSCALL.dll (VERY interesting btw). The dll has some good examples of code and how to use the properties and methods. In fact, I THINK (not sure) that this dll is what generates the cvs scripts. If you look at a script and then look at the dll it will start to make sense. When it comes the ReportView VBA.Collection, the dll shows it being used, and it's NOT for retreiving the data from the report.

I will tell you what I've implemented to get the data and how I'm applying it.

I do the reporting for the Help Desk of my organization, and this started out as a way to persist data for a longer period of time (specifically interval data for trending purposes). My system only holds Agent data for about a month before dropping it. We have multiple call centers, so we have to control the size of the database.

Well, to pull daily interval data for about 30 agents is a pain and I needed a way to push it to a SqlServer database. So this is what I've done:

I've wrapped the connection to CMS into a c# class. This allows me to share a single connection with all the reports I'm needing to generate on the fly (about 60-70 interval reports). After about 2 weeks of figuring it out I've finally have it in c# (you can see the method I was attempting from my post above)

After that, I created a report class that would allow me to generate reports as text files. It was a pain at times trying to figure out the right types in c# but everything is working now. One big issue that I had to resolve was the fact that the report objects wanted to stay open as a process even after I was done with them, and .NET wouldn’t take care of them automatically. So I had to figure out a way to release them after I was done with them. It was very simple after I did my research: System.Runtime.InteropServices.Marshal.ReleaseComO bject(object);

I said all that to say this:

I’ve created objects that correspond to specific data that is in the reports I generate. 1. "STAT" object that holds a single line (or period) from an interval report. (things like ACD, ACW, RING, AUX, times etc.); & 2. a "STATS" object that inherits from CollectionBase, and implements the IEnumerable interface. The stats object holds all the stat objects I’m creating. It also has a Populate() method that pulls the reports and parses them into stat objects then adds them to the collection.

In my SqlServer database I have a table that corresponds to the properites of the "STAT" object, and I have a data class that accepts a "STATS" object then adds the "STAT"s to the database.

I know it’s a round-a-bout way to doing this, but it’s the best method I’ve come up with. Besides, most Business Intelligence groups out there have a methodology for an ETL (extract, transform, load) process and that’s basically what I’m doing.

[SRGR]

Corcoranp,
Thats exactly I am trying to do. Currently I have automatic scripts scheduled they create flat files and I have VB program which extracts the data from flat files and send to SQL Server. Now I am trying to automate in visual Basic, I tried your code but didn't work. Would you please explain what exactly you did?

Thanks ,
SRGR.

[RickoT]

Hey all,

I am trying to do the same thing, except I am using visual basic. I have gotten everything working except for one thing. Currently I use the export to clipboard option instead of the file option because I am trying to get live data to post on call boards. unfortunately if the reports refresh at the right (or wrong) time, the clipboard doesnt get cleared properly and I am stuck with two reports holding the same data. On the flipside of that, if I use the export to files, I have to suffer the delay of writing to text files and opening them and all that fun stuff. Has anyone figured out how to export the data to a variable instead of to either the clipboard or the file options?

If anyone wants a sample of what I've done, let me know

~Rick

[SRGR]

Hi,
Please post the sample code.

[RickoT]

In review.. the code below does the following
BuildAgentReport creates the report on the server to grab from, and the DisplayAgentReport grabs it from the server and posts it into the clipboard (then into a flexgrid) - I tried inserting a variable where the "" is, and it still puts it into the clipboard... if I output to a file it takes WAY too long... any suggestions would be great! Thanks :)

I used the PHP code because there wasnt one for VB, I really wanted just proper spacing and everything, ignore the red :)

PHP Code:

Public Function BuildAgentReport() As Boolean 'Builds agent report on server
    On Error Resume Next
    
    cvsSrvAgent.Reports.ACD = 1
    Set RepInfoAgent = cvsSrvAgent.Reports.Reports("Real-Time\Agent\Agent Group Report")
    
    If RepInfoAgent Is Nothing Then
        If cvsSrvAgent.Interactive Then
            MsgBox "The report Real-Time\Agent\Agent Group Report was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
        Else
            Set acsLog = CreateObject("ACSERR.cvsLog")
            acsLog.AutoLogWrite "The report Real-Time\Agent\Agent Group Report was not found on ACD 1."
            Set acsLog = Nothing
        End If
    Else
        RepInfoAgent = cvsSrvAgent.Reports.CreateReport(RepInfoAgent, RepAgent)
        If RepInfoAgent Then
            RepAgent.Window.Top = 6075
            RepAgent.Window.Left = 3285
            RepAgent.Window.Width = 9675
            RepAgent.Window.Height = 4815
            
            RepAgent.SetProperty "Agent Group", "ISD Service Desk"
            RepAgent.ReportView.Add "G2,0,0;-1,2,2", "data"
            BuildAgentReport = True
            If Not cvsSrvAgent.Interactive Then cvsSrvAgent.ActiveTasks.Remove RepAgent.TaskID
        End If
    
    End If
    
    Set RepInfoAgent = Nothing
End Function

Public Function DisplayAgentReport() As Boolean 'Grabs agent report data from server
    Dim strdata As String
    Dim i As Variant
    If cvsConnAgent.LoginState = 7145 Then GoTo ConnLost
TryAgain:
    With frmMain.flxAgentGrid
        .Clear
        .Row = 0
        .Col = 0
        .RowSel = 49
        .ColSel = 9
        RepAgent.RefreshInterval = 5
        
        strdata = cvsSrvAgent.Reports.View(1)
        
        'DisplayAgentReport = RepAgent.ExportData("", 9, 0, False, False, False) ' SEE NOTE ABOVE
        .Clear
        .Clip = Clipboard.GetText
        If frmAgentDataDisplay.Visible = True Then DoAgentReportDisplay
    End With
Exit Function
ConnLost:
'    DoDisconnect
'    DoConnection
'    DisplaySkillReport
'    DisplayAgentReport
GoTo TryAgain
End Function 


[SRGR]

Thanks RickoT, When I use this script in VB what are referencies should I make? (Project -> references).

[RickoT]

The code below is what creates all of my references... after that establishes the server criteria as well as creates the connection to the server itself

PHP Code:


Option Explicit
Public cvsApp As New cvsApplication
Public cvsConnAgent As New cvsConnection
Public cvsConnSkill As New cvsConnection
Public acsLog As Object
Public RepSkill As cvsReport
Public RepAgent As cvsReport
Public RepInfoSkill
Public RepInfoAgent
Public cvsCatalogSkill As New cvsCatalog
Public cvsCatalogagent As New cvsCatalog
Public cvsSrvSkill As cvsServer
Public cvsSrvAgent As cvsServer

Private Sub Form_Load()
    cvsApp.CreateServer "****", "****", "", "157.209.119.34", False, "ENU", cvsSrvAgent, cvsConnAgent
    cvsApp.CreateServer "****", "****", "", "157.209.119.34", False, "ENU", cvsSrvSkill, cvsConnSkill
End Sub

Public Sub DoConnection()
   
    cvsConnAgent.Login "****", "****", "157.209.119.34", "ENU"
    cvsConnSkill.Login "****", "****", "157.209.119.34", "ENU"
    
    If BuildSkillReport = True Then frmMain.chkSkillReportDone.Value = 1 Else frmMain.chkSkillReportDone = 0
    If BuildAgentReport = True Then frmMain.chkAgentReportDone.Value = 1 Else frmMain.chkAgentReportDone = 0
    
End Sub 


[Hwkranger]

Should this work with CMS 11.0?

I'm guessing that it doesn't, my libraries seem different.... any help would be apprecaited.

[Hwkranger]

Should this work with CMS 11.0?

I'm guessing that it doesn't, my libraries seem different.... any help would be apprecaited.