New Topic/Question
Reply
I am trying to recreate this code from a VB web client to a C# web form.
So far I was able to solve with help on connecting to DSN, and pulling data from a datagridview.
Now my next challenge is pulling two select statements from two sources.
Here is what I have so far:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace RSQ_App_V1
{
public partial class Queue : Form
{
public Queue()
{
InitializeComponent();
try
{
//Initialize variables
int contactsWaiting = 0;
//rtcsqssummary = callswaiting
int oldestContact = 0;
//rtcsqssummary = oldestcontact
int totalContacts = 0;
//rtcsqssummary = totalcalls
int contactsHandled = 0;
//rtcsqssummary = callshandled
int contactsAbandoned = 0;
//rtcsqssummary = callsabandoned
double percentageHandled = 0;
// Establishes Connection to UCCX
OdbcConnection connection = new OdbcConnection();
connection.ConnectionString = "DSN=UCCX";
// Selects Data
string queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname='RSQ-Spanish' OR csqname='RSQ-English'";
// Query Command
OdbcCommand command = new OdbcCommand(queryString, connection);
//Open Connection
connection.Open();
//Executes Data Reader
OdbcDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable("UCCXData");
dt.Load(reader);
reader.Close();
connection.Close();
//Fill the dataview
dvData.DataSource = dt;
totalCalls.Text = Convert.ToString(dvData.Rows[0].Cells[0].Value).ToString();
callsWaiting.Text = Convert.ToString(dvData.Rows[0].Cells[1].Value).ToString();
oldestCall.Text = Convert.ToString(dvData.Rows[0].Cells[2].Value).ToString();
callsHandled.Text = Convert.ToString(dvData.Rows[0].Cells[3].Value).ToString();
callsAbandoned.Text = Convert.ToString(dvData.Rows[0].Cells[4].Value).ToString();
longestCallDuration.Text = Convert.ToString(dvData.Rows[0].Cells[5].Value).ToString();
}
catch (Exception ex)
{
//Error Textbox
MessageBox.Show("there was an error - " + ex);
textBox1.Text = " " + ex;
}
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'dataSet1.rtcsqssummary' table. You can move, or remove it, as needed.
this.rtcsqssummaryTableAdapter.Fill(this.dataSet1.rtcsqssummary);
}
}
}
The VB code had the following:
Imports System.Data.Odbc
Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Get Castle Group data - queue=1
GetUCCXData(1)
'Get Connections data - queue=2
GetUCCXData(2)
End Sub
Private Sub GetUCCXData(ByVal queue As Integer)
'Initialize variables
Dim contactsWaiting As Integer = 0 'rtcsqssummary = callswaiting
Dim oldestContact As Integer = 0 'rtcsqssummary = oldestcontact
Dim totalContacts As Integer = 0 'rtcsqssummary = totalcalls
Dim contactsHandled As Integer = 0 'rtcsqssummary = callshandled
Dim contactsAbandoned As Integer = 0 'rtcsqssummary = callsabandoned
Dim percentageHandled As Double = 0 'Calculated field
Try
'ODBC Connection
Dim connection As New OdbcConnection
connection.ConnectionString = "Dsn=UCCX"
'Query - using SQL SUM function with Informix DB
Dim queryString As String = ""
If queue = 1 Then
queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname='RSQ-Spanish' OR csqname='RSQ-English'"
ElseIf queue = 2 Then
queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname='RSQ-Elevator'"
End If
'Set command
Dim command As New OdbcCommand(queryString, connection)
'Open connection
connection.Open()
'Execute reader
Dim reader As OdbcDataReader = command.ExecuteReader
'Check if reader has any rows
If reader.HasRows Then
'Check that first column is not Null
If reader.IsDBNull(0) = False Then
Dim dt As New DataTable("CSQ")
dt.Load(reader)
'Set data values
contactsWaiting = dt.Rows(0).Item("scallswaiting")
totalContacts = dt.Rows(0).Item("stotalcalls")
oldestContact = dt.Rows(0).Item("soldestcontact")
contactsHandled = dt.Rows(0).Item("scallshandled")
contactsAbandoned = dt.Rows(0).Item("scallsabandoned")
If queue = 1 Then
lblCGStatus.Text = ""
ElseIf queue = 2 Then
lblCEStatus.Text = ""
End If
Else
If queue = 1 Then
lblCGStatus.Text = "Restart - NULL"
ElseIf queue = 2 Then
lblCEStatus.Text = "Restart - NULL"
End If
End If
Else
If queue = 1 Then
lblCGStatus.Text = "Restart - NO ROWS"
ElseIf queue = 2 Then
lblCEStatus.Text = "Restart - NO ROWS"
End If
End If
reader.Close()
connection.Close()
'Render contacts waiting display - red / blink
RenderContactsWaiting(oldestContact, contactsWaiting, queue)
'Set web form values to data values
If queue = 1 Then
valCGWaiting.Text = contactsWaiting
valCGTotal.Text = totalContacts
valCGOldest.Text = CalculateTime(oldestContact)
valCGHandled.Text = contactsHandled
valCGAbandoned.Text = contactsAbandoned
valCGPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%"
ElseIf queue = 2 Then
valCEWaiting.Text = contactsWaiting
valCETotal.Text = totalContacts
valCEOldest.Text = CalculateTime(oldestContact)
valCEHandled.Text = contactsHandled
valCEAbandoned.Text = contactsAbandoned
valCEPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%"
End If
'Reset div views if needed
If exceptionMessage.Attributes("class").ToString = "showDiv" Then
lblException.Text = ""
exceptionMessage.Attributes.Add("class", "hideDiv")
wallboardContainer.Attributes.Add("class", "showDiv")
UpdatePanelMain.Update()
End If
Catch ex As Exception
lblException.Text = "Queue: " + queue.ToString + " - Error: " + ex.ToString
exceptionMessage.Attributes.Add("class", "showDiv")
wallboardContainer.Attributes.Add("class", "hideDiv")
UpdatePanelMain.Update()
End Try
End Sub
Private Sub RenderContactsWaiting(ByVal oldest As Integer, ByVal waiting As Integer, ByVal queue As Integer)
If queue = 1 Then
If oldest > 60000 Then
divCGWaiting.Attributes.Add("class", "waitingDivRedBlink")
ElseIf oldest > 40000 Or waiting > 2 Then
divCGWaiting.Attributes.Add("class", "waitingDivRed")
Else
divCGWaiting.Attributes.Add("class", "waitingDivDefault")
End If
ElseIf queue = 2 Then
If oldest > 60000 Then
divCEWaiting.Attributes.Add("class", "waitingDivRedBlink")
ElseIf oldest > 40000 Or waiting > 2 Then
divCEWaiting.Attributes.Add("class", "waitingDivRed")
Else
divCEWaiting.Attributes.Add("class", "waitingDivDefault")
End If
End If
End Sub
Private Function CalculateTime(ByVal millis As Integer) As String
Dim Hours As Integer = TimeSpan.FromMilliseconds(millis).Hours
Dim Minutes As Integer = TimeSpan.FromMilliseconds(millis).Minutes
Dim Seconds As Integer = TimeSpan.FromMilliseconds(millis).Seconds
Return Hours.ToString("D2") + ":" + Minutes.ToString("D2") + ":" + Seconds.ToString("D2")
End Function
Private Function CalculatePercentHandled(ByVal handled As Integer, ByVal total As Integer) As String
If total > 0 Then
Return Math.Round(((handled / total) * 100)).ToString
Else
Return "0"
End If
End Function
End Class
I am trying to grab the second select statement which would be: "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname='RSQ-Elevator'"
I tried putting it in an if else statement and got an error that "Queue" was invalid.
I am trying to pull both statements so they can fill the labels accordingly.
I know I am going to have to add another if else statement or something similar later in order to display the two "Queue's".
Aside from that I have been able to run it successfully until I try to place it in an if else statement.
Any help will be greatly appreciated as always..
Thank you.
MultiQuote
| 