/// <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 }