public override async Task RefreshDataSetAsync(ConnectedSystemDataSet dataSet, CancellationToken cancellationToken)
{
Logger.LogDebug($"Refreshing DataSet {dataSet.Name}");
var inputText = dataSet.QueryConfig.Query ?? throw new ConfigurationException($"Missing Query in QueryConfig for dataSet '{dataSet.Name}'");
var query = new SubstitutionString(inputText);
var substitutedQuery = query.ToString();
// Send the query off to ServiceNow
var connectedSystemItems = await _serviceNowClient
.GetAllByQueryAsync(
dataSet.QueryConfig.Type,
substitutedQuery,
extraQueryString : dataSet.QueryConfig.Options,
cancellationToken : cancellationToken)
.ConfigureAwait(false);
Logger.LogDebug($"Got {connectedSystemItems.Count} results for {dataSet.Name}.");
await ProcessConnectedSystemItemsAsync(
dataSet,
connectedSystemItems,
ConnectedSystem,
cancellationToken
).ConfigureAwait(false);
}
public override async Task <object?> QueryLookupAsync(
QueryConfig queryConfig,
string field,
bool valueIfZeroMatchesFoundSets,
object?valueIfZeroMatchesFound,
bool valueIfMultipleMatchesFoundSets,
object?valueIfMultipleMatchesFound,
CancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(queryConfig.Query))
{
throw new ConfigurationException($"{nameof(queryConfig.Query)} must be set");
}
var substitutedQuery = new SubstitutionString(queryConfig.Query).ToString();
var connectedSystemItems = await _salesforceClient
.GetAllJObjectsAsync(substitutedQuery)
.ConfigureAwait(false);
Logger.LogDebug($"Got {connectedSystemItems.Count} results for query '{queryConfig.Query}'.");
return(connectedSystemItems.Count switch
{
1 => connectedSystemItems[0][field],
_ => throw new ConfigurationException($"Lookup found {connectedSystemItems.Count} records using query '{queryConfig.Query}'. Expected 1.")
});
public override async Task RefreshDataSetAsync(ConnectedSystemDataSet dataSet, CancellationToken cancellationToken)
{
using var connection = new SqlConnection(_connectedSystem.Credentials.ConnectionString);
Logger.LogDebug($"Opening MS SQL connection for {_connectedSystem.Name}...");
await connection
.OpenAsync(cancellationToken)
.ConfigureAwait(false);
Logger.LogDebug($"Refreshing DataSet {dataSet.Name}");
// Process any ncalc in the query
var inputText = dataSet.QueryConfig.Query ?? throw new ConfigurationException($"Missing Query in QueryConfig for dataSet '{dataSet.Name}'");
var query = new SubstitutionString(inputText);
var substitutedQuery = query.ToString();
// Send the query off to MS SQL Server
var results = (await connection.QueryAsync <object>(substitutedQuery).ConfigureAwait(false)).ToList();
Logger.LogDebug($"Got {results.Count} results for {dataSet.Name}.");
// Convert to JObjects for easier generic manipulation
var connectedSystemItems = results
.ConvertAll(entity => JObject.FromObject(entity))
;
await ProcessConnectedSystemItemsAsync(
dataSet,
connectedSystemItems,
ConnectedSystem,
cancellationToken)
.ConfigureAwait(false);
}
public override async Task RefreshDataSetAsync(ConnectedSystemDataSet dataSet, CancellationToken cancellationToken)
{
Logger.LogDebug($"Refreshing Jira DataSet {dataSet.Name}");
var inputText = dataSet.QueryConfig.Query ?? throw new ConfigurationException($"Missing Query in QueryConfig for dataSet '{dataSet.Name}'");
var query = new SubstitutionString(inputText);
var substitutedQuery = query.ToString();
List <JObject> connectedSystemItems;
switch (dataSet.QueryConfig.Type)
{
case "Issue":
var issues = await _jiraClient
.Issues
.GetIssuesFromJqlAsync(dataSet.QueryConfig.Query, token : cancellationToken)
.ConfigureAwait(false);
connectedSystemItems = issues
.Select(issue => JObject.FromObject(issue))
.ToList();
break;
default:
throw new NotSupportedException($"Jira type '{dataSet.QueryConfig.Type}' not supported.");
}
Logger.LogDebug($"Got {connectedSystemItems.Count} results for Jira dataset {dataSet.Name}.");
await ProcessConnectedSystemItemsAsync(
dataSet,
connectedSystemItems,
ConnectedSystem,
cancellationToken
).ConfigureAwait(false);
}
public override async System.Threading.Tasks.Task RefreshDataSetAsync(ConnectedSystemDataSet dataSet, CancellationToken cancellationToken)
{
Logger.LogDebug($"Refreshing DataSet {dataSet.Name}");
var inputText = dataSet.QueryConfig.Query ?? throw new ConfigurationException($"Missing Query in QueryConfig for dataSet '{dataSet.Name}'");
var query = new SubstitutionString(inputText);
var substitutedQuery = query.ToString();
// Send the query off to AutoTask
var autoTaskResult = await _autoTaskClient
.GetAllAsync(substitutedQuery, cancellationToken)
.ConfigureAwait(false);
Logger.LogDebug($"Got {autoTaskResult.Count():N0} results for {dataSet.Name}.");
// Convert to JObjects for easier generic manipulation
var connectedSystemItems = autoTaskResult
.Select(entity => JObject.FromObject(entity))
.ToList();
await ProcessConnectedSystemItemsAsync(
dataSet,
connectedSystemItems,
ConnectedSystem,
cancellationToken
).ConfigureAwait(false);
}
public override async Task RefreshDataSetAsync(ConnectedSystemDataSet dataSet, CancellationToken cancellationToken)
{
Logger.LogDebug($"Refreshing DataSet {dataSet.Name}");
var inputText = dataSet.QueryConfig.Query ?? throw new ConfigurationException($"Missing Query in QueryConfig for dataSet '{dataSet.Name}'");
var query = new SubstitutionString(inputText);
var substitutedQuery = query.ToString();
var connectedSystemItems = await _salesforceClient.GetAllJObjectsAsync(substitutedQuery).ConfigureAwait(false);
Logger.LogDebug($"Got {connectedSystemItems.Count} results for {dataSet.Name}.");
await ProcessConnectedSystemItemsAsync(
dataSet,
connectedSystemItems,
ConnectedSystem,
cancellationToken)
.ConfigureAwait(false);
}
public override async Task RefreshDataSetAsync(ConnectedSystemDataSet dataSet, CancellationToken cancellationToken)
{
List <JObject> connectedSystemItems;
Logger.LogDebug($"Refreshing DataSet {dataSet.Name}");
if (string.IsNullOrWhiteSpace(dataSet.QueryConfig.Type))
{
throw new ConfigurationException($"In {dataSet.Name}, {nameof(dataSet.QueryConfig)} {nameof(dataSet.QueryConfig.Type)} must be set.");
}
if (string.IsNullOrWhiteSpace(dataSet.QueryConfig.Query))
{
throw new ConfigurationException($"In {dataSet.Name}, {nameof(dataSet.QueryConfig)} {nameof(dataSet.QueryConfig.Query)} must be set.");
}
var type = dataSet.QueryConfig.Type;
var query = new SubstitutionString(dataSet.QueryConfig.Query).ToString();
var configItems = query.Split('|');
try
{
var configItemsExceptFirst = configItems
.ToList();
switch (type.ToLowerInvariant())
{
case "exprptglds":
var exprptgldsConfig = new ExprptgldsConfig(configItemsExceptFirst);
// We have the index
var expenseReportGlds = await _certifyClient
.ExpenseReportGlds
.GetAllAsync(exprptgldsConfig.Index, active : 1)
.ConfigureAwait(false);
connectedSystemItems = expenseReportGlds
.ConvertAll(entity => JObject.FromObject(entity))
;
break;
case "expensereports":
var expenseReports = await _certifyClient
.ExpenseReports
.GetAllAsync()
.ConfigureAwait(false);
connectedSystemItems = expenseReports
.ConvertAll(entity => JObject.FromObject(entity))
;
break;
case "expenses":
var propertyFilters = new List <Filter>();
string?startDate = null;
string?endDate = null;
string?batchId = null;
uint? processed = null;
uint? includeDisapproved = null;
var allFilters = configItemsExceptFirst.Select(ci => new Filter(ci));
foreach (var filter in allFilters)
{
var name = filter.Name;
var value = filter.Value;
var isProperty = false;
switch (name.ToLowerInvariant())
{
case "startdate":
startDate = value;
break;
case "enddate":
endDate = value;
break;
case "batchid":
batchId = value;
break;
case "processed":
processed = GetBoolUint(name, value);
break;
case "includedisapproved":
includeDisapproved = GetBoolUint(name, value);
break;
default:
// Perhaps we will filter on this later?
propertyFilters.Add(filter);
isProperty = true;
break;
}
if (!isProperty && filter.Operator != Operator.Equals)
{
throw new ConfigurationException($"Expense configItem {filter.Name} must in the form 'a==b'");
}
}
// Fetch using the query filters
var expenses = (await _certifyClient
.Expenses
.GetAllAsync(
startDate,
endDate,
batchId,
processed,
includeDisapproved
)
.ConfigureAwait(false))
.AsQueryable();
// Apply property filters
foreach (var propertyFilter in propertyFilters)
{
// Does this refer to a valid property?
var matchingPropertyInfo = ExpensePropertyInfos.SingleOrDefault(pi => string.Equals(pi.Name, propertyFilter.Name, StringComparison.InvariantCultureIgnoreCase));
if (matchingPropertyInfo == null)
{
// No
throw new ConfigurationException($"Expenses do not have property '{propertyFilter.Name}'");
}
// Yes
// Filter on this criteria
expenses = propertyFilter.Operator switch
{
Operator.Equals => expenses.Where(e => matchingPropertyInfo.GetValue(e).EmptyStringIfNull() == propertyFilter.Value),
Operator.NotEquals => expenses.Where(e => matchingPropertyInfo.GetValue(e).EmptyStringIfNull() != propertyFilter.Value),
Operator.LessThanOrEquals => expenses.Where(e => string.Compare(matchingPropertyInfo.GetValue(e).EmptyStringIfNull(), propertyFilter.Value) <= 0),
Operator.LessThan => expenses.Where(e => string.Compare(matchingPropertyInfo.GetValue(e).EmptyStringIfNull(), propertyFilter.Value) < 0),
Operator.GreaterThanOrEquals => expenses.Where(e => string.Compare(matchingPropertyInfo.GetValue(e).EmptyStringIfNull(), propertyFilter.Value) >= 0),
Operator.GreaterThan => expenses.Where(e => string.Compare(matchingPropertyInfo.GetValue(e).EmptyStringIfNull(), propertyFilter.Value) > 0),
_ => throw new NotSupportedException($"Operator '{propertyFilter.Operator}' not supported.")
};
}
var expensesList = expenses.ToList();
// Only one currency supported - convert all to GBP using Fixer
var badExpenses = expensesList
.Where(e => e.Currency != "GBP")
.ToList();
foreach (var badExpense in badExpenses)
{
if (!DateTime.TryParseExact(badExpense.ExpenseDate, "yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AssumeUniversal, out var date))
{
throw new FormatException($"Certify date not in expected format: {badExpense.ExpenseDate}");
}
var currency = badExpense.Currency;
var exchangeRateOnDate = await GetExchangeRateOnDateAsync(currency, date).ConfigureAwait(false);
badExpense.Amount *= (float)exchangeRateOnDate;
badExpense.Currency = "GBP";
}
connectedSystemItems = expensesList
.ConvertAll(entity => JObject.FromObject(entity))
;
break;
default:
throw new NotSupportedException();
}
}
catch (Exception e)
{
Logger.LogError($"Could not fetch {type} due to {e.Message}");
throw;
}
await ProcessConnectedSystemItemsAsync(
dataSet,
connectedSystemItems,
ConnectedSystem,
cancellationToken)
.ConfigureAwait(false);
}
