/// <summary>
/// Return individual fields as per query
/// </summary>
/// <param name="ApplicationID"></param>
/// <param name="Query"></param>
/// <returns> array of string delimited by '|'</returns>
private string[] GetRNData(string ApplicationID, string Query)
{
string[] rn_data = null;
IOTClouddAutoClientAddIn.GlobalContext.PrepareConnectSession(_rightNowClient.ChannelFactory);
ClientInfoHeader hdr = new ClientInfoHeader()
{
AppID = ApplicationID
};
byte[] output = null;
CSVTableSet data = null;
try
{
data = _rightNowClient.QueryCSV(hdr, Query, 50, "|", false, false, out output);
string data_row = String.Empty;
if (data != null && data.CSVTables.Length > 0 && data.CSVTables[0].Rows.Length > 0)
{
return(data.CSVTables[0].Rows);
}
}
catch (Exception ex)
{
throw ex;
}
return(rn_data);
}
/// <summary>
/// Return individual fields as per query
/// </summary>
/// <param name="ApplicationID"></param>
/// <param name="Query"></param>
/// <returns> array of string delimited by '~'</returns>
private string[] GetRNData(string ApplicationID, string Query)
{
string[] rnData = null;
ClientInfoHeader hdr = new ClientInfoHeader()
{
AppID = ApplicationID
};
byte[] output = null;
CSVTableSet data = null;
try
{
data = _rightNowClient.QueryCSV(hdr, Query, 1000, "~", false, false, out output);
string dataRow = String.Empty;
if (data != null && data.CSVTables.Length > 0 && data.CSVTables[0].Rows.Length > 0)
{
return(data.CSVTables[0].Rows);
}
}
catch (Exception ex)
{
WorkspaceAddIn.InfoLog(ex.Message);
}
return(rnData);
}
/// <summary>
/// Get Result of Report based on Filter's passed
/// </summary>
/// <param name="filters">List of filter name and Values</param>
/// <returns>CSVTable</returns>
public CSVTable GetReportDetails(List <KeyValuePair <string, string> > filters)
{
try
{
AnalyticsReport analyticsreport = new AnalyticsReport {
ID = new ID {
id = WorkspaceAddIn._reportID, idSpecified = true
}
};
List <AnalyticsReportFilter> analyticsfilter = new List <AnalyticsReportFilter>();
foreach (KeyValuePair <string, string> filter in filters)
{
string filterName = filter.Key;
if (filterName == "Customer Name")
{
analyticsfilter.Add(new AnalyticsReportFilter
{
Name = filter.Key,
Operator = new NamedID
{
ID = new ID
{
id = 7,
idSpecified = true
}
},
Values = new string[] { filter.Value }
});
}
else
{
analyticsfilter.Add(new AnalyticsReportFilter
{
Name = filter.Key,
Operator = new NamedID
{
ID = new ID
{
id = 1,
idSpecified = true
}
},
Values = new string[] { filter.Value }
});
}
}
analyticsreport.Filters = analyticsfilter.ToArray();
CSVTableSet tableset = RunReport(analyticsreport);
if (tableset.CSVTables.Length > 0)
{
return(tableset.CSVTables[0]);
}
}
catch (Exception ex)
{
WorkspaceAddIn.InfoLog("Exception in GetReportDetails: " + ex.Message);
}
return(null);
}
public string[] getReportResult(int reportId, List <AnalyticsReportFilter> filterList)
{
//Use RightNow SOAP API to run report and get RNow Contact search results
//Set up Analytics Report
//Create new AnalyticsReport Object
AnalyticsReport analyticsReport = new AnalyticsReport();
//create limit and start parameters. Specifies the max number of rows to return (10,000 is the overall maximum)
//start specifies the starting row
int limit = 10;
int start = 0;
//Specify a report ID
ID reportID = new ID();
//contact search report
reportID.id = reportId;
reportID.idSpecified = true;
analyticsReport.ID = reportID;
analyticsReport.Filters = filterList.ToArray();
CSVTableSet thisset = new CSVTableSet();
byte[] fd;
// Run AnalyticsReport via SOAP API
thisset = _rnowClient.RunAnalyticsReport(_rnowClientInfoHeader, analyticsReport, limit, start, ",", false, true, out fd);
// Get Report Search Result
CSVTable[] tableResults = thisset.CSVTables;
String[] searchResults = tableResults[0].Rows;
return(searchResults);
}
public string[] queryData(string queryString)
{
byte[] outByte = new byte[1000];
CSVTableSet tableSet = _rnowClient.QueryCSV(_rnowClientInfoHeader, queryString, 100, ",", false, false, out outByte);
CSVTable[] csvTables = tableSet.CSVTables;
CSVTable table = csvTables[0];
string[] rowData = table.Rows;
return(rowData);
}
internal CSVQueryResponse QueryCSV(string queryString, string delimiter, int pageSize)
{
bool mustRetry = false; // used to determine if we should retry a failed job
int retryTimes = 0; // we don't want to endlessly retry, so we only will retry 3 times
do
{
try
{
CSVTableSet result = null;
byte[] byteArray;
result = _client.QueryCSV(_clientInfoHeader, queryString, pageSize, delimiter, false, true, out byteArray);
return(new CSVQueryResponse
{
TableSet = result,
Successful = true,
SuccessfulSet = true
});
}
catch (Exception ex)
{
GlobalContext.Log(string.Format("Failed QueryCSV: Retry {0}: {1}", retryTimes, ex.Message), true);
GlobalContext.Log(string.Format("Failed QueryCSV: Retry {0}: {1}{2}{3}", retryTimes, ex.Message, Environment.NewLine, ex.StackTrace), false);
if (retryTimes < 3)
{
// if we haven't retried 3 times then we retry the load again
mustRetry = true;
retryTimes++;
}
else
{
// don't retry for 3rd retry
return(new CSVQueryResponse
{
TableSet = null,
Successful = false,
SuccessfulSet = true,
Details = ex.Message
});
}
}
GlobalContext.Log(string.Format("QueryCSV Must Retry {0}", mustRetry), false);
} while (mustRetry);
GlobalContext.Log("QueryCSV End: This code should never be hit.", false);
return(null); // this code should never be hit
}
/// <summary>
/// Get config verb value
/// </summary>
/// <param name="configVerbName"></param>
/// <returns></returns>
public string GetRightNowConfigVerbValue(string configVerbName)
{
//Prepare session
IOTClouddAutoClientAddIn.GlobalContext.PrepareConnectSession(_rightNowClient.ChannelFactory);
Debug("RightNowConnectService - GetRightNowConfigVerbValue() - Enter");
string configVerbValue = String.Empty;
try
{
// Set up query and set request
ClientInfoHeader cih = new ClientInfoHeader();
cih.AppID = OracleRightNowIOTAddInNames.ORACLE_RIGHTNOW_IOT_CLIENT;
byte[] outByte = new byte[1000];
string query = RightNowQueries.GET_CONFIG_VERB_QUERY + "'" + configVerbName + "'";
Notice("Sending query to fetch " + configVerbName + " Config verb value");
CSVTableSet tableSet = _rightNowClient.QueryCSV(cih, query, 100, ",", false, false, out outByte);
CSVTable[] csvTables = tableSet.CSVTables;
CSVTable table = csvTables[0];
string[] rowData = table.Rows;
// Check whether configuration is set
if (rowData.Length == 0)
{
return(String.Empty);
}
// Get configuration value
Notice("Returning Config verb '" + configVerbName + "' value: " + rowData[0]);
configVerbValue = rowData[0];
}
catch (Exception e)
{
Debug("RightNowConnectService - GetRightNowConfigVerbValue() - Error while fetching config verb" + e.Message);
Error("RightNowConnectService - GetRightNowConfigVerbValue() - Error while fetching config verb", e.StackTrace);
MessageBox.Show("RightNowConnectService - Error while fetching config verb", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Debug("RightNowConnectService - GetRightNowConfigVerbValue() - Exit");
return(configVerbValue);
}
/// <summary>
/// Funtion to Run the report
/// </summary>
/// <param name="report">AnalyticsReport info like report ID and Filter detail</param>
/// <returns>CSVTableSet</returns>
public CSVTableSet RunReport(AnalyticsReport report)
{
CSVTableSet reportdata = null;
byte[] bytearray = null;
try
{
ClientInfoHeader hdr = new ClientInfoHeader()
{
AppID = "Get Report Data"
};
reportdata = _rightNowClient.RunAnalyticsReport(hdr, report, 10000, 0, "~", false, true, out bytearray);
if (reportdata != null && reportdata.CSVTables.Length > 0)
{
return(reportdata);
}
}
catch (Exception ex)
{
WorkspaceAddIn.InfoLog("Exception in RunReport: " + ex.Message);
}
return(null);
}
/// <summary>
/// Return individual fields as per query
/// </summary>
/// <param name="ApplicationID"></param>
/// <param name="Query"></param>
/// <returns> array of string delimited by '|'</returns>
private string[] GetRNData(string ApplicationID, string Query)
{
Debug("RightNowConnectService - GetRNData() - Entry");
string[] rn_data = null;
OutOfOfficeClientAddIn.GlobalContext.PrepareConnectSession(_rightNowClient.ChannelFactory);
ClientInfoHeader hdr = new ClientInfoHeader()
{
AppID = ApplicationID
};
byte[] output = null;
CSVTableSet data = null;
try
{
data = _rightNowClient.QueryCSV(hdr, Query, 50, "|", false, false, out output);
string data_row = String.Empty;
if (data != null && data.CSVTables.Length > 0 && data.CSVTables[0].Rows.Length > 0)
{
return(data.CSVTables[0].Rows);
}
}
catch (Exception ex)
{
Error(ex.Message, ex.StackTrace);
throw ex;
}
Debug("RightNowConnectService - GetRNData() - Exit");
return(rn_data);
}
public string[] getReportResult(int reportId, List<AnalyticsReportFilter> filterList)
{
//Use RightNow SOAP API to run report and get RNow Contact search results
//Set up Analytics Report
//Create new AnalyticsReport Object
AnalyticsReport analyticsReport = new AnalyticsReport();
//create limit and start parameters. Specifies the max number of rows to return (10,000 is the overall maximum)
//start specifies the starting row
int limit = 10;
int start = 0;
//Specify a report ID
ID reportID = new ID();
//contact search report
reportID.id = reportId;
reportID.idSpecified = true;
analyticsReport.ID = reportID;
analyticsReport.Filters = filterList.ToArray();
CSVTableSet thisset = new CSVTableSet();
byte[] fd;
// Run AnalyticsReport via SOAP API
thisset = _rnowClient.RunAnalyticsReport(_rnowClientInfoHeader, analyticsReport, limit, start, ",", false, true, out fd);
// Get Report Search Result
CSVTable[] tableResults = thisset.CSVTables;
String[] searchResults = tableResults[0].Rows;
return searchResults;
}
/* this method is called from framework to show the report row (data)
* It combines the incident report (ConfigurationSetting.incidentsByContactReportID)
* and the ServiceRequest.LookupSRbyContactPartyID(contactPartyID)
* Currently this list is only showing certain fields (because of combining 2 lists with common fields)
* The Right Now incidents by a contact report is hidden, meaning the Report control of a contact
* workspace tab is based on the EBS Service Request List Table report definition
* Also, do not change the default column heading of Right Now incidents by a contact report
* (they are hard coded to uppercase). Because they are hidden anyway.
* The EBS Service Request List Table report definition column headings can be changed and those are
* the ones being displayed.
*/
public override IList <IReportRow> GetRows(IList <string> columns, IReportFilterNode filterNode)
{
IList <IReportRow> reportRows = new List <IReportRow>();
IRecordContext _context = ((EBSVirtualReportTablesPackage)this.Parent)._globalContext.AutomationContext.CurrentWorkspace;
if (_context == null)
{
return(reportRows);
}
IContact contactRecord = _context.GetWorkspaceRecord(RightNow.AddIns.Common.WorkspaceRecordType.Contact) as IContact;
/* report framework refresh every 30 sec (default) even though the tab is not active
* so need to check contactRecord for null (when the editor is different)
*/
if (contactRecord == null)
{
return(reportRows);
}
int contactPartyID = 0;
// get the ebs contact party custom attribute on the contact workspace
contactPartyID = getContactPartyIdCustomAttr(contactRecord);
// following to get the rNow incidents report and filter is the rNow contactID
AnalyticsReport reportIncident = new AnalyticsReport();
ID rId = new ID();
rId.id = ConfigurationSetting.incidentsByContactReportID;
rId.idSpecified = true;
reportIncident.ID = rId;
byte[] outByte = new byte[1000];
AnalyticsReportFilter[] filter = new AnalyticsReportFilter[3];
filter[0] = new AnalyticsReportFilter();
String[] filterString = new String[1];
filterString[0] = "" + contactRecord.ID;
filter[0].Values = filterString;
filter[0].Name = "Contact"; // incidents by a contact, thus Contact filter
NamedID datatype = new NamedID();
datatype.Name = "Integer";
filter[0].DataType = datatype;
reportIncident.Filters = filter;
ClientInfoHeader _cih = new ClientInfoHeader();
_cih.AppID = "Accelerator Report Add-In";
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
CSVTableSet tableSet = ConfigurationSetting.client.RunAnalyticsReport(
_cih, reportIncident, 100, 0, "\t", false, false, out outByte
);
stopwatch.Stop();
string logMessage = "Called RightNowSyncPortClient.RunAnalyticsReport." +
"reportID: " + ConfigurationSetting.incidentsByContactReportID;
ConfigurationSetting.logWrap.DebugLog(0, contactRecord.ID, logMessage: logMessage, timeElapsed: (int)stopwatch.ElapsedMilliseconds);
CSVTable[] csvTables = tableSet.CSVTables;
CSVTable table = csvTables[0];
string[] rowData = table.Rows;
int rNowIncidentCount = table.Rows.Length;
int srVirtualTableCount = this.Columns.Count;
string[] colHeadingIncidentReport = table.Columns.Split('\t');
foreach (String commaRow in table.Rows)
{
ReportDataRow reportDataRow = new ReportDataRow(srVirtualTableCount);
string[] colValue = commaRow.Split('\t');
// the report output is stored as <columnHeading, value>
Dictionary <string, string> dictRow = new Dictionary <string, string>();
int i = 0;
foreach (string val in colValue)
{ /* make the column heading upper case (because the custom attribute heading
* in the report designer sometime all in lower case, sometime the reverse)
*/
dictRow.Add(colHeadingIncidentReport[i].ToUpper(), val);
i++;
}
addRnowIncidentRow(ref columns, ref reportDataRow, ref reportRows, dictRow);
}
if (contactPartyID > 0)
{
ServiceRequest[] sRs = ServiceRequest.LookupSRbyContactPartyID(contactPartyID, 0, contactRecord.ID);
foreach (ServiceRequest req in sRs)
{
ReportDataRow reportDataRow = new ReportDataRow(this.Columns.Count);
if (req != null) // live ebs row 316 of 319 of contact 4431 return null
{
addEBSsrRow(ref columns, ref reportDataRow, ref reportRows, req);
}
}
}
return(reportRows);
}
/// <summary>
/// Generic helper method to create a lookup table
/// This is used for performance reasons, so that main lookups all occur in memory instead of database lookups
/// </summary>
/// <param name="query"></param>
/// <param name="limit"></param>
/// <param name="useCache"></param>
/// <returns></returns>
public Dictionary <long, long> GetLookupTable2(string baseQueryString)
{
bool mustRetry = false; // used to determine if we should retry a failed job
int retryTimes = 0; // we don't want to endlessly retry, so we only will retry 3 times
do
{
try
{
Dictionary <long, long> lookupTable = new Dictionary <long, long>();
int limit = 10000;
long rowsReturned = Int64.MaxValue;
long totalRowsReturned = 0;
int offset = 0;
while (rowsReturned >= limit)
{
string queryString = baseQueryString + " LIMIT " + limit + " OFFSET " + offset;
var queryResult = QueryCSV(queryString, "|", limit);
if (!queryResult.Successful)
{
throw new Exception(queryResult.Details);
}
CSVTableSet thisset = queryResult.TableSet;
foreach (CSVTable table in thisset.CSVTables)
{
rowsReturned = table.Rows.Length;
totalRowsReturned += rowsReturned;
foreach (string row in table.Rows)
{
String[] columns = row.Split(new string[] { "|" }, StringSplitOptions.None);
if (!String.IsNullOrEmpty(columns[0]) && !String.IsNullOrEmpty(columns[1]) && (columns[0] != "0"))
{
long key = Convert.ToInt64(columns[0]);
if (!lookupTable.ContainsKey(key))
{
lookupTable.Add(key, Convert.ToInt64(columns[1]));
}
else
{
GlobalContext.Log(string.Format("GetLookupTable2: Ignoring Duplicate ID Found: {0} : {1} : Query String: {2}",
columns[0], columns[1], queryString), true);
}
}
}
break;
}
offset = offset + limit;
}
GlobalContext.Log("GetLookupTable2 Added " + lookupTable.Count + " Query Results: " + baseQueryString, false);
return(lookupTable);
}
catch (Exception ex)
{
GlobalContext.Log(string.Format("GetLookupTable2 Error: Retry {0}: {1}\n{2}", retryTimes, ex.Message, ex.StackTrace), false);
if (retryTimes < 4)
{
// if we haven't retried 4 times then we retry the load again
mustRetry = true;
retryTimes++;
}
else
{
throw ex; // don't retry for 4th retry
}
}
GlobalContext.Log(string.Format("GetLookupTable2 Must Retry {0}", mustRetry), false);
} while (mustRetry);
GlobalContext.Log("GetLookupTable2 End: This code should never be hit.", false);
return(null); // this code should never be hit
}
/// <summary>
/// Generic helper method to create a lookup table
/// This is used for performance reasons, so that main lookups all occur in memory instead of database lookups
/// </summary>
/// <param name="query"></param>
/// <param name="limit"></param>
/// <param name="useCache"></param>
/// <returns></returns>
public List <CSVTable> GetAnalyticsReport(long reportId, AnalyticsReportFilter[] filters, string delimiter = ",")
{
bool mustRetry = false; // used to determine if we should retry a failed job
int retryTimes = 0; // we don't want to endlessly retry, so we only will retry 3 times
if (filters == null)
{
filters = new AnalyticsReportFilter[] { };
}
do
{
try
{
List <CSVTable> resultTableList = new List <CSVTable>();
int limit = 10000;
long rowsReturned = Int64.MaxValue;
long totalRowsReturned = 0;
int offset = 0;
while (rowsReturned >= limit)
{
var queryResult = GetAnalyticsReportResults(reportId, filters.ToArray(), limit, offset, delimiter); // "|");
if (!queryResult.Successful)
{
throw new Exception(queryResult.Details);
}
CSVTableSet thisset = queryResult.RunAnalyticsTableSet;
foreach (CSVTable table in thisset.CSVTables)
{
resultTableList.Add(table);
rowsReturned = table.Rows.Length;
totalRowsReturned += rowsReturned;
//break;
}
offset = offset + limit;
}
GlobalContext.Log(string.Format("GetAnalyticsReport Added {0} tables with a total of {1} records for ReportID {2}",
resultTableList.Count, totalRowsReturned, reportId), false);
return(resultTableList);
}
catch (Exception ex)
{
GlobalContext.Log(string.Format("GetAnalyticsReport Error calling ReportID {0}: Retry {1}: {2}\n{3}",
reportId, retryTimes, ex.Message, ex.StackTrace), false);
if (retryTimes < 4)
{
// if we haven't retried 4 times then we retry the load again
mustRetry = true;
retryTimes++;
}
else
{
throw ex; // don't retry for 4th retry
}
}
GlobalContext.Log(string.Format("GetAnalyticsReport Must Retry {0}", mustRetry), false);
} while (mustRetry);
GlobalContext.Log("GetAnalyticsReport End: This code should never be hit.", false);
return(null); // this code should never be hit
}
internal RunAnalyticsResponse GetAnalyticsReportResults(long reportId, AnalyticsReportFilter[] filters,
int limit = 100, int start = 0, string delimiter = ",")
{
//Create new AnalyticsReport Object
AnalyticsReport analyticsReport = new AnalyticsReport();
//Specify a report ID of Public Reports>Common>Data integration>Opportunities
ID reportID = new ID();
reportID.id = reportId;
reportID.idSpecified = true;
analyticsReport.ID = reportID;
analyticsReport.Filters = filters;
GetProcessingOptions processingOptions = new GetProcessingOptions();
processingOptions.FetchAllNames = true;
RNObject[] getAnalyticsObjects = new RNObject[] { analyticsReport };
CSVTableSet thisSet = new CSVTableSet();
byte[] ignore;
bool mustRetry = false; // used to determine if we should retry a failed job
int retryTimes = 0; // we don't want to endlessly retry, so we only will retry 3 times
do
{
try
{
thisSet = _client.RunAnalyticsReport(_clientInfoHeader, analyticsReport, limit, start, delimiter, false, true, out ignore);
return(new RunAnalyticsResponse
{
RunAnalyticsTableSet = thisSet,
Successful = true,
SuccessfulSet = true
});
}
catch (Exception ex)
{
GlobalContext.Log(string.Format("Failed RunAnalyticsReport: Retry {0}: {1}", retryTimes, ex.Message), true);
GlobalContext.Log(string.Format("Failed RunAnalyticsReport: Retry {0}: {1}{2}{3}", retryTimes, ex.Message, Environment.NewLine, ex.StackTrace), false);
if (retryTimes < 3)
{
// if we haven't retried 3 times then we retry the load again
mustRetry = true;
retryTimes++;
}
else
{
// don't retry for 3rd retry
return(new RunAnalyticsResponse
{
Successful = false,
SuccessfulSet = false,
Details = ex.Message
});
}
}
GlobalContext.Log(string.Format("RunAnalyticsReport Must Retry {0}", mustRetry), false);
} while (mustRetry);
GlobalContext.Log("RunAnalyticsReport End: This code should never be hit.", false);
return(null); // this code should never be hit
}
